验收应急用

liyong

4 天以前 3ce16d71df26c71f3ad74b211c1f94dc76cc4a77

验收应急用

 src/main/resources/mapper/purchase/InvoicePurchaseMapper.xml

@@ -30,91 +30,122 @@
        sl.sales_contract_no
    </select>
    <select id="listVat" resultType="com.ruoyi.purchase.dto.VatDto">
        select *
        from (SELECT
                  COALESCE(a1.month, a2.month) AS month,
                  IFNULL(a1.tax_amount, 0) AS j_tax_amount,
                  IFNULL(a2.x_tax_amount, 0) AS x_tax_amount
              FROM (
                       -- 第一个查询：来自 invoice_ledger 的税额
                       SELECT
                           DATE_FORMAT(il.invoice_date, '%Y-%m') AS month,
                           ROUND(SUM(pr.invoice_amount - pr.invoice_amount / (1 + pr.tax_rate / 100)), 2) AS tax_amount
                       FROM invoice_ledger il
                                LEFT JOIN invoice_registration_product pr ON pr.id = il.invoice_registration_product_id
                       WHERE il.invoice_no IS NOT NULL
                         AND invoice_type = '增专票'
                         AND DATE_FORMAT(il.invoice_date, '%Y-%m') IS NOT NULL  -- 新增：过滤month为NULL的情
                       GROUP BY DATE_FORMAT(il.invoice_date, '%Y-%m')
                   ) a1
                       LEFT JOIN (
                  -- 第二个查询：来自 ticket_registration 的税额
                  SELECT
                      DATE_FORMAT(a.issue_date, '%Y-%m') AS month,
                      SUM(a.invoice_amount) AS x_tax_amount
                  FROM (
                           SELECT DISTINCT pr.id,
                                           tr.issue_date,
                                           ROUND(pr.tickets_amount / (1 + pr.tax_rate / 100), 2) AS un_tickets_price,
                                           ROUND(pr.tickets_amount - pr.tickets_amount / (1 + pr.tax_rate / 100), 2) AS invoice_amount
                           FROM product_record pr
                                    LEFT JOIN purchase_ledger pl ON pl.id = pr.purchase_ledger_id
                                    LEFT JOIN sales_ledger sl ON sl.id = pl.sales_ledger_id
                                    LEFT JOIN ticket_registration tr ON tr.purchase_ledger_id = pl.id
                                    LEFT JOIN product_model pm ON pm.id = pr.product_model_id
                           WHERE type = 2
                             AND tr.invoice_number IS NOT NULL
                       ) a
                  GROUP BY DATE_FORMAT(a.issue_date, '%Y-%m')
              ) a2 ON a1.month = a2.month
        <!--        select *-->
        <!--        from (SELECT-->
        <!--                  COALESCE(a1.month, a2.month) AS month,-->
        <!--                  IFNULL(a1.tax_amount, 0) AS j_tax_amount,-->
        <!--                  IFNULL(a2.x_tax_amount, 0) AS x_tax_amount-->
        <!--              FROM (-->
        <!--                       &#45;&#45; 第一个查询：来自 invoice_ledger 的税额-->
        <!--                       SELECT-->
        <!--                           DATE_FORMAT(il.invoice_date, '%Y-%m') AS month,-->
        <!--                           ROUND(SUM(pr.invoice_amount - pr.invoice_amount / (1 + pr.tax_rate / 100)), 2) AS tax_amount-->
        <!--                       FROM invoice_ledger il-->
        <!--                                LEFT JOIN invoice_registration_product pr ON pr.id = il.invoice_registration_product_id-->
        <!--                       WHERE il.invoice_no IS NOT NULL-->
        <!--                         AND invoice_type = '增专票'-->
        <!--                         AND DATE_FORMAT(il.invoice_date, '%Y-%m') IS NOT NULL  &#45;&#45; 新增：过滤month为NULL的情-->
        <!--                       GROUP BY DATE_FORMAT(il.invoice_date, '%Y-%m')-->
        <!--                   ) a1-->
        <!--                       LEFT JOIN (-->
        <!--                  &#45;&#45; 第二个查询：来自 ticket_registration 的税额-->
        <!--                  SELECT-->
        <!--                      DATE_FORMAT(a.issue_date, '%Y-%m') AS month,-->
        <!--                      SUM(a.invoice_amount) AS x_tax_amount-->
        <!--                  FROM (-->
        <!--                           SELECT DISTINCT pr.id,-->
        <!--                                           tr.issue_date,-->
        <!--                                           ROUND(pr.tickets_amount / (1 + pr.tax_rate / 100), 2) AS un_tickets_price,-->
        <!--                                           ROUND(pr.tickets_amount - pr.tickets_amount / (1 + pr.tax_rate / 100), 2) AS invoice_amount-->
        <!--                           FROM product_record pr-->
        <!--                                    LEFT JOIN purchase_ledger pl ON pl.id = pr.purchase_ledger_id-->
        <!--                                    LEFT JOIN sales_ledger sl ON sl.id = pl.sales_ledger_id-->
        <!--                                    LEFT JOIN ticket_registration tr ON tr.purchase_ledger_id = pl.id-->
        <!--                                    LEFT JOIN product_model pm ON pm.id = pr.product_model_id-->
        <!--                           WHERE type = 2-->
        <!--                             AND tr.invoice_number IS NOT NULL-->
        <!--                       ) a-->
        <!--                  GROUP BY DATE_FORMAT(a.issue_date, '%Y-%m')-->
        <!--              ) a2 ON a1.month = a2.month-->

              UNION ALL
        <!--              UNION ALL-->

              SELECT
                  COALESCE(a1.month, a2.month) AS month,
                  IFNULL(a1.tax_amount, 0) AS tax_amount,
                  IFNULL(a2.x_tax_amount, 0) AS x_tax_amount
              FROM (
                       -- 第二个查询：来自 ticket_registration 的税额（反过来补全没有匹配到的）
                       SELECT
                           DATE_FORMAT(a.issue_date, '%Y-%m') AS month,
                           SUM(a.invoice_amount) AS x_tax_amount
                       FROM (
                                SELECT DISTINCT pr.id,
                                                tr.issue_date,
                                                ROUND(pr.tickets_amount / (1 + pr.tax_rate / 100), 2) AS un_tickets_price,
                                                ROUND(pr.tickets_amount - pr.tickets_amount / (1 + pr.tax_rate / 100), 2) AS invoice_amount
                                FROM product_record pr
                                         LEFT JOIN purchase_ledger pl ON pl.id = pr.purchase_ledger_id
                                         LEFT JOIN sales_ledger sl ON sl.id = pl.sales_ledger_id
                                         LEFT JOIN ticket_registration tr ON tr.purchase_ledger_id = pl.id
                                         LEFT JOIN product_model pm ON pm.id = pr.product_model_id
                                WHERE type = 2
                                  AND tr.invoice_number IS NOT NULL
                            ) a
                       GROUP BY DATE_FORMAT(a.issue_date, '%Y-%m')
                   ) a2
                       LEFT JOIN (
                  -- 第一个查询：来自 invoice_ledger 的税额
                  SELECT
                      DATE_FORMAT(il.invoice_date, '%Y-%m') AS month,
                      ROUND(SUM(pr.invoice_amount - pr.invoice_amount / (1 + pr.tax_rate / 100)), 2) AS tax_amount
                  FROM invoice_ledger il
                           LEFT JOIN invoice_registration_product pr ON pr.id = il.invoice_registration_product_id
                  WHERE il.invoice_no IS NOT NULL
                    AND invoice_type = '增专票'
                    AND DATE_FORMAT(il.invoice_date, '%Y-%m') IS NOT NULL  -- 新增：过滤month为NULL的情
                  GROUP BY DATE_FORMAT(il.invoice_date, '%Y-%m')
              ) a1 ON a1.month = a2.month
              WHERE a1.month IS NULL
              ORDER BY month
             )as a
        <!--              SELECT-->
        <!--                  COALESCE(a1.month, a2.month) AS month,-->
        <!--                  IFNULL(a1.tax_amount, 0) AS tax_amount,-->
        <!--                  IFNULL(a2.x_tax_amount, 0) AS x_tax_amount-->
        <!--              FROM (-->
        <!--                       &#45;&#45; 第二个查询：来自 ticket_registration 的税额（反过来补全没有匹配到的）-->
        <!--                       SELECT-->
        <!--                           DATE_FORMAT(a.issue_date, '%Y-%m') AS month,-->
        <!--                           SUM(a.invoice_amount) AS x_tax_amount-->
        <!--                       FROM (-->
        <!--                                SELECT DISTINCT pr.id,-->
        <!--                                                tr.issue_date,-->
        <!--                                                ROUND(pr.tickets_amount / (1 + pr.tax_rate / 100), 2) AS un_tickets_price,-->
        <!--                                                ROUND(pr.tickets_amount - pr.tickets_amount / (1 + pr.tax_rate / 100), 2) AS invoice_amount-->
        <!--                                FROM product_record pr-->
        <!--                                         LEFT JOIN purchase_ledger pl ON pl.id = pr.purchase_ledger_id-->
        <!--                                         LEFT JOIN sales_ledger sl ON sl.id = pl.sales_ledger_id-->
        <!--                                         LEFT JOIN ticket_registration tr ON tr.purchase_ledger_id = pl.id-->
        <!--                                         LEFT JOIN product_model pm ON pm.id = pr.product_model_id-->
        <!--                                WHERE type = 2-->
        <!--                                  AND tr.invoice_number IS NOT NULL-->
        <!--                            ) a-->
        <!--                       GROUP BY DATE_FORMAT(a.issue_date, '%Y-%m')-->
        <!--                   ) a2-->
        <!--                       LEFT JOIN (-->
        <!--                  &#45;&#45; 第一个查询：来自 invoice_ledger 的税额-->
        <!--                  SELECT-->
        <!--                      DATE_FORMAT(il.invoice_date, '%Y-%m') AS month,-->
        <!--                      ROUND(SUM(pr.invoice_amount - pr.invoice_amount / (1 + pr.tax_rate / 100)), 2) AS tax_amount-->
        <!--                  FROM invoice_ledger il-->
        <!--                           LEFT JOIN invoice_registration_product pr ON pr.id = il.invoice_registration_product_id-->
        <!--                  WHERE il.invoice_no IS NOT NULL-->
        <!--                    AND invoice_type = '增专票'-->
        <!--                    AND DATE_FORMAT(il.invoice_date, '%Y-%m') IS NOT NULL  &#45;&#45; 新增：过滤month为NULL的情-->
        <!--                  GROUP BY DATE_FORMAT(il.invoice_date, '%Y-%m')-->
        <!--              ) a1 ON a1.month = a2.month-->
        <!--              WHERE a1.month IS NULL-->
        <!--              ORDER BY month-->
        <!--             )as a-->
        <!--        <where>-->
        <!--            a.month is not null-->
        <!--            <if test="month != null">-->
        <!--                and a.month = #{month}-->
        <!--            </if>-->
        <!--        </where>-->
        SELECT
        month,
        SUM(sales_tax_amount) AS sales_tax_amount,
        SUM(purchase_tax_amount) AS purchase_tax_amount
        FROM (
        SELECT
        DATE_FORMAT(entry_date, '%Y-%m') AS month,
        ROUND(SUM(contract_amount * 0.13), 2) AS sales_tax_amount,
        0 AS purchase_tax_amount
        FROM sales_ledger
        WHERE entry_date IS NOT NULL
        GROUP BY DATE_FORMAT(entry_date, '%Y-%m')

        UNION ALL

        SELECT
        DATE_FORMAT(entry_date, '%Y-%m') AS month,
        0 AS sales_tax_amount,
        ROUND(SUM(contract_amount * 0.26), 2) AS purchase_tax_amount
        FROM purchase_ledger
        <where>

            a.month is not null
            <if test="month != null">
                and a.month = #{month}
            </if>
        </where>
        GROUP BY DATE_FORMAT(entry_date, '%Y-%m')
        ) t
        GROUP BY month
        ORDER BY month;

    </select>
    <select id="listVat1" resultType="com.ruoyi.purchase.dto.VatDto">