acr_invoice_statistics.lwm 5.84 KB
Newer Older
Spencer Chang's avatar
Spencer Chang committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124
<?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>