采购对帐单相关SQL

#采购对帐单pi_purchase_statement
SELECT
    pps.PURCHASE_STATEMENT_ID,
    pps. CODE,
    pps.OWNER_ID,
    pps.SUPPLIER_ID,
    pps.ORDER_TYPE,
    pps.TOTAL_AMOUNT,
    pps.TOTAL_NOW_AMOUNT,
    pps.STATEMENT_DATE_FROM,
    pps.STATEMENT_DATE_TO,
    pps.OP_STAFF_ID,
    pps.DEPARTMENT_ID,
    pps. STATUS,
    pps.PRINT_COUNT,
    pps.PRINT_STAFF_ID,
    pps.PRINT_TIME,
    pps.REMARK,
    pps.VERIFY_STAFF_ID,
    pps.VERIFY_TIME,
    pps.CREATE_STAFF_ID,
    pps.CREATE_TIME,
    pps.UPDATE_TIME,
    pps.UPDATE_STAFF_ID,
    pps.COMPANY_ID,
    pps.SYSTEM_ID,
    pis. NAME SUPPLIER_NAME,
    adp. NAME DEPARTMENT_NAME,
    pio. NAME OWNER_NAME,
    adsa. NAME OP_STAFF_NAME,
    adsb. NAME VERIFY_STAFF_NAME,
    adsc. NAME CREATE_STAFF_NAME
FROM
    pi_purchase_statement pps
LEFT JOIN pi_suppliers pis ON pis.id = pps.supplier_id
LEFT JOIN ad_department adp ON adp.DEPARTMENT_ID = pps.department_id
LEFT JOIN pi_owner pio ON pio.id = pps.owner_id
LEFT JOIN ad_staff adsa ON adsa.staff_id = pps.OP_STAFF_ID
LEFT JOIN ad_staff adsb ON adsb.staff_id = pps.VERIFY_STAFF_ID
LEFT JOIN ad_staff adsc ON adsc.staff_id = pps.CREATE_STAFF_ID
LEFT JOIN pi_purchase_statement_list pssl ON pssl.PURCHASE_STATEMENT_ID = pps.PURCHASE_STATEMENT_ID
LEFT JOIN pi_purchase_balance ppb ON ppb.ORDER_ID = pps.PURCHASE_STATEMENT_ID
WHERE
    1 = 1
#AND pps. STATUS IN (?, ?, ?, ?)
AND pps.COMPANY_ID = '1000001425'
GROUP BY
    pps.PURCHASE_STATEMENT_ID
ORDER BY
    pps.CREATE_TIME DESC
LIMIT 50;

#采购对帐单-对账明细列表
SELECT
    IFNULL(
        IFNULL(
            ppi.ID,
            ppe.PURCHASE_EXPENSE_ID
        ),
        pppb.PRTMANUFACT_PRICE_BILL_ID
    ) AS ORDER_ID,
    IFNULL(
        IFNULL(ppi. CODE, ppe. CODE),
        pppb. CODE
    ) AS ORDER_CODE,
    IFNULL(
        IFNULL(
            ppi.ORDER_TYPE,
            ppe.ORDER_TYPE
        ),
        pppb.ORDER_TYPE
    ) AS ORDER_TYPE,
    get_lookup_code_name (
        IFNULL(
            IFNULL(
                ppi.ORDER_TYPE,
                ppe.ORDER_TYPE
            ),
            pppb.ORDER_TYPE
        )
    ) AS ORDER_TYPE_NAME,
    IFNULL(
        IFNULL(
            ppi.STOCK_INOUT_TIME,
            ppe.STOCK_TIME
        ),
        pppb.STOCK_TIME
    ) AS STATEMENT_DATE_FROM,
    IFNULL(
        IFNULL(
            ppe.ACCOUNT_DATE,
            ppi.ACCOUNT_DATE
        ),
        pppb.ACCOUNT_DATE
    ) AS ACCOUNT_DATE,
    IFNULL(ppi.TOTAL_QTY, 0) AS TOTAL_QTY,
    IFNULL(
        IFNULL(
            ppi.TOTAL_AMOUNT,
            ppe.TOTAL_AMOUNT
        ),
        pppb.TOTAL_MANUFACT_AMOUNT
    ) AS TOTAL_AMOUNT,
    IFNULL(ppb.CHECK_AMOUNT, 0) AS ALREADY_AMOUNT,
    IFNULL(
        IFNULL(adsa. NAME, adsb. NAME),
        adsc. NAME
    ) AS CREATE_STAFF_NAME,
    IFNULL(

        IF (
            pppb.BANYIN_REAL = NULL,
            IFNULL(mmp1. NAME, mmp2. NAME),
            CONCAT(
                IFNULL(mmp1. NAME, mmp2. NAME),
                '(',
                pppb.BANYIN_REAL,
                ')'
            )
        ),

    IF (
        ppe.BANYIN_REAL = NULL,
        IFNULL(mmp1. NAME, mmp2. NAME),
        CONCAT(
            IFNULL(mmp1. NAME, mmp2. NAME),
            '(',
            ppe.BANYIN_REAL,
            ')'
        )
    )
    ) AS PRODUCT_NAME,
    ppsl.REMARK,
    ppsl.NOW_AMOUNT,
    ppsl.PURCHASE_STATEMENT_LIST_ID,
    ppsl.PURCHASE_STATEMENT_ID,
    ppsl.PRIORITY,
    ppsl.ORDER_AMOUNT
FROM
    pi_purchase_statement_list ppsl
LEFT JOIN pi_purchase_balance ppb ON ppsl.order_type = ppb.order_type AND ppsl.order_id = ppb.order_id
LEFT JOIN pi_purchase_inout ppi ON ppsl.order_type = ppi.ORDER_TYPE AND ppsl.order_id = ppi.ID
LEFT JOIN pi_purchase_expense ppe ON ppsl.order_type = ppe.ORDER_TYPE AND ppsl.order_id = ppe.PURCHASE_EXPENSE_ID
LEFT JOIN mm_product mmp1 ON mmp1.PRODUCT_ID = ppe.PRODUCT_ID 
LEFT JOIN pi_prtmanufact_price_bill pppb ON ppsl.order_type = pppb.order_type AND ppsl.order_id = pppb.PRTMANUFACT_PRICE_BILL_ID
LEFT JOIN mm_product mmp2 ON mmp2.PRODUCT_ID = pppb.PRODUCT_ID
LEFT JOIN ad_staff adsa ON adsa.staff_id = ppi.CREATE_STAFF_ID
LEFT JOIN ad_staff adsb ON adsb.staff_id = ppe.CREATE_STAFF_ID
LEFT JOIN ad_staff adsc ON adsc.staff_id = pppb.CREATE_STAFF_ID
WHERE
    1 = 1
AND ppsl.PURCHASE_STATEMENT_ID = '1039264095'
GROUP BY
    IFNULL(
        IFNULL(
            ppi.ID,
            ppe.PURCHASE_EXPENSE_ID
        ),
        pppb.PRTMANUFACT_PRICE_BILL_ID
    )

#采购对帐单-材料明细列表
SELECT
    ppi.ID AS ORDER_ID,
    ppi.`CODE` AS ORDER_CODE,
    get_lookup_code_name (ppi.ORDER_TYPE) AS ORDER_TYPE_NAME,
    ppi.STOCK_INOUT_TIME AS STATEMENT_DATE_FROM,
    ppi.TOTAL_AMOUNT,
    ppil.QTY,
    ppil.LIST_TOTAL_AMOUNT AS ROW_AMOUNT,
    pm.`CODE` AS META_CODE,
    pm.META_NAME,
    pm.META_GWIGHT,
    pm.META_LENGTH,
    pm.META_WIDTH
FROM
    pi_purchase_statement_list ppsl
LEFT JOIN pi_purchase_inout ppi ON ppsl.order_type = ppi.ORDER_TYPE AND ppsl.order_id = ppi.ID
LEFT JOIN pi_purchase_inout_list ppil ON ppi.id = ppil.PURCHASE_INOUT_ID
LEFT JOIN pi_metarials pm ON ppil.meta_id = pm.id
WHERE
    1 = 1
AND ppi.ID IS NOT NULL
AND ppsl.PURCHASE_STATEMENT_ID = '1039264095'
ORDER BY
    ppi. CODE DESC,
    ppi.ORDER_TYPE DESC,
    META_CODE DESC

转载于:https://my.oschina.net/marlon520/blog/833195