<?xml version="1.0" encoding="UTF-8"?>
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
                SELECT
                    *
                FROM
                    (SELECT
                        tt.invoice_year,
                        tt.invoice_month,
                        SUM(
                            CASE
                                WHEN tt.invoice_kind   = '0' AND
                                    tt.express_status IN ('CONFIRM','SENT_OFF')
                                THEN tt.count
                                ELSE 0
                            END) special_invoice_rec, --增值发票收到
                        SUM(
                            CASE
                                WHEN tt.invoice_kind   = '2' AND
                                    tt.express_status IN ('CONFIRM','SENT_OFF')
                                THEN tt.count
                                ELSE 0
                            END) common_invoice_rec, --普通发票收到
                        SUM(
                            CASE
                                WHEN tt.invoice_kind  IN ('0', '2') AND
                                    tt.express_status IN ('CONFIRM','SENT_OFF')
                                THEN tt.count
                                ELSE 0
                            END) summation_rec, --收到合计
                        SUM(
                            CASE
                                WHEN tt.invoice_kind  = '0' AND
                                    tt.express_status = 'SENT_OFF'
                                THEN tt.count
                                ELSE 0
                            END) special_invoice_sed, --增值发票SENT_OFF
                        SUM(
                            CASE
                                WHEN tt.invoice_kind  = '2' AND
                                    tt.express_status = 'SENT_OFF'
                                THEN tt.count
                                ELSE 0
                            END) common_invoice_sed, --普通发票SENT_OFF
                        SUM(
                            CASE
                                WHEN tt.invoice_kind IN ('0', '2') AND
                                    tt.express_status = 'SENT_OFF'
                                THEN tt.count
                                ELSE 0
                            END) summation_sed, --SENT_OFF合计
                        (SUM(
                            CASE
                                WHEN tt.invoice_kind   = '0' AND
                                    tt.express_status IN ('CONFIRM','SENT_OFF')
                                THEN tt.count
                                ELSE 0
                            END) - SUM(
                            CASE
                                WHEN tt.invoice_kind  = '0' AND
                                    tt.express_status = 'SENT_OFF'
                                THEN tt.count
                                ELSE 0
                            END)) AS special_invoice_sto, --增值发票库存
                        (SUM(
                            CASE
                                WHEN tt.invoice_kind   = '2' AND
                                    tt.express_status IN ('CONFIRM','SENT_OFF')
                                THEN tt.count
                                ELSE 0
                            END) - SUM(
                            CASE
                                WHEN tt.invoice_kind  = '2' AND
                                    tt.express_status = 'SENT_OFF'
                                THEN tt.count
                                ELSE 0
                            END)) AS common_invoice_sto, --普通发票库存
                        (SUM(
                            CASE
                                WHEN tt.invoice_kind  IN ('0', '2') AND
                                    tt.express_status IN ('CONFIRM','SENT_OFF')
                                THEN tt.count
                                ELSE 0
                            END) - SUM(
                            CASE
                                WHEN tt.invoice_kind IN ('0', '2') AND
                                    tt.express_status = 'SENT_OFF'
                                THEN tt.count
                                ELSE 0
                            END)) AS summation_sto --库存合计
                    FROM
                        (SELECT
                            SUBSTR(TO_CHAR(t.fnc_handover_date, 'YYYY-MM-DD'), 1, 4) AS invoice_year,
                            SUBSTR(TO_CHAR(t.fnc_handover_date, 'YYYY-MM-DD'), 6, 2) AS invoice_month,
                            t.invoice_kind,
                            t.express_status,
                            COUNT(*) COUNT
                        FROM
                            acr_invoice_hd t
                        WHERE
                            t.fnc_handover_date IS NOT NULL
                        GROUP BY
                            SUBSTR(TO_CHAR(t.fnc_handover_date, 'YYYY-MM-DD'), 1, 4),
                            SUBSTR(TO_CHAR(t.fnc_handover_date, 'YYYY-MM-DD'), 6, 2),
                            t.invoice_kind,
                            t.express_status
                        ) tt
                    GROUP BY
                        tt.invoice_year,
                        tt.invoice_month
                    ORDER BY
                        tt.invoice_year,
                        tt.invoice_month
                    ) t #WHERE_CLAUSE#
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="invoice_year" queryExpression="t.invoice_year = ${@invoice_year}"/>
        <bm:query-field name="invoice_month" queryExpression="t.invoice_month = ${@invoice_month}"/>
    </bm:query-fields>
</bm:model>
