采购付款单相关SQL

#采购付款单pi_purchase_pay
SELECT
    ppp.PURCHASE_PAY_ID AS purchasePayId,
    ppp.`CODE` AS CODE,
    po.`NAME` AS ownerName,
    ps.`NAME` AS supplierName,
    IFNULL(SUM(pppl.preAmount), 0) + IFNULL(SUM(pppl.invAmount), 0) AS totalAmount,
    ppp.TOTAL_AMOUNT AS actualPay,
    ppp.ACCOUNT_DATE AS accountDate,
    asop.`NAME` AS opStaffName,
    ad.`NAME` AS departmentName,
    ppp.`STATUS` AS STATUS,
    IFNULL(ppp.TOTAL_PRE_AMOUNT, 0) AS totalPreAmount,
    ppp.REMARK AS remark,
    asstock.`NAME` AS stockStaffName,
    DATE_FORMAT(ppp.STOCK_TIME, '%Y-%m-%d') AS stockTime,
    ascre.`NAME` AS createName,
    ppp.CREATE_TIME AS createTime
FROM
    pi_purchase_pay ppp
LEFT JOIN pi_owner po ON po.ID = ppp.OWNER_ID
LEFT JOIN pi_suppliers ps ON ps.ID = ppp.SUPPLIER_ID
LEFT JOIN ad_department ad ON ad.DEPARTMENT_ID = ppp.DEPARTMENT_ID
LEFT JOIN ad_staff asop ON asop.STAFF_ID = ppp.OP_STAFF_ID
LEFT JOIN ad_staff asstock ON asstock.STAFF_ID = ppp.STOCK_STAFF_ID
LEFT JOIN ad_staff ascre ON ascre.STAFF_ID = ppp.CREATE_STAFF_ID
LEFT JOIN (
    SELECT
        pppl.PURCHASE_PAY_LIST_ID,
        pppl.PURCHASE_PAY_ID,
        pppl.ORDER_CODE,
        CASE
    WHEN pppl.ORDER_TYPE = 'PI2518' THEN
        pppl.ORDER_AMOUNT
    END AS 'preAmount',
    CASE
WHEN pppl.ORDER_TYPE = 'PI2514' THEN
    0 - pppl.ORDER_AMOUNT
END AS 'invAmount'
FROM
    pi_purchase_pay_list pppl
) pppl ON pppl.PURCHASE_PAY_ID = ppp.PURCHASE_PAY_ID
LEFT JOIN pi_purchase_pay_item_list ppil ON ppil.PURCHASE_PAY_ID = ppp.PURCHASE_PAY_ID
LEFT JOIN pi_purchase_pay_inout_list pppil ON pppil.PURCHASE_PAY_ID = ppp.PURCHASE_PAY_ID
WHERE
    1 = 1
#AND ppp. STATUS IN (?, ?, ?, ?, ?)
GROUP BY
    ppp.PURCHASE_PAY_ID
ORDER BY
    ppp.CREATE_TIME DESC
LIMIT 50;

#采购付款单-付款单明细列表
SELECT
    ppl.PURCHASE_PAY_ID AS purchasePayId,
    ppl.ORDER_ID AS orderId,
    ppl.ORDER_CODE AS orderCode,
    get_lookup_code_name (ppl.ORDER_TYPE) AS orderTypes,
    ppl.ORDER_TYPE AS orderType,
    DATE_FORMAT(
        ppl.ACCOUNT_DATE,
        '%Y-%m-%d'
    ) AS accountDate,
    DATE_FORMAT(ppl.STOCK_DATE, '%Y-%m-%d') AS stockDate,
    IFNULL(ppl.ORDER_AMOUNT, 0) AS orderAmount,
    IFNULL(ppl.ALREADY_AMOUNT, 0) AS alreadyAmount,
    IFNULL(ppl.ORDER_AMOUNT, 0) - IFNULL(ppl.ALREADY_AMOUNT, 0) AS notAlreadyAmount,
    IFNULL(ppl.NOW_AMOUNT, 0) AS nowAmount,
    ppl.REMARK AS remark,
    ast.`NAME` AS createStaffName,
    ppl.CREATE_STAFF_ID AS createStaffId,
    DATE_FORMAT(ppl.CREATE_TIME, '%Y-%m-%d') AS createTime
FROM
    pi_purchase_pay_list ppl
LEFT JOIN ad_staff ast ON ast.STAFF_ID = ppl.CREATE_STAFF_ID
WHERE
    1 = 1
AND ppl.PURCHASE_PAY_ID = '1039264149'

#采购付款单-对账单据列表
SELECT
    ppil.PURCHASE_PAY_ITEM_LIST_ID AS purchasePayItemListId,
    ppil.PURCHASE_PAY_LIST_ID AS purchasePayListId,
    ppil.PURCHASE_PAY_ID AS purchasePayId,
    ppil.INVOICE_ORDER_ID AS invoiceId,
    ppil.INVOICE_ORDER_CODE AS invoiceCode,
    ppil.STATEMENT_ORDER_ID statementId,
    ppil.STATEMENT_ORDER_CODE AS statementCode,
    ppil.STATEMENT_DATE_FROM AS statementDateFrom,
    ppil.STATEMENT_DATE_TO AS statementDateTo,
    IFNULL(ppil.TOTAL_AMOUNT, 0) AS totalAmount,
    IFNULL(ppil.ALREADY_AMOUNT_DZ, 0) AS alreadyAmountDZ,
    IFNULL(ppil.ALREADY_AMOUNT_SP, 0) AS alreadyAmountSP,
    IFNULL(ppil.ALREADY_AMOUNT_FK, 0) AS alreadyAmountFK,
    IFNULL(ppil.ALREADY_AMOUNT_SP, 0) - IFNULL(ppil.ALREADY_AMOUNT_FK, 0) AS alreadyAmountWF,
    IFNULL(ppil.NOT_AMOUNT, 0) AS nowAmount,
    ppil.REMARK AS remark,
    ast.`NAME` AS createStaffName,
    ppil.CREATE_STAFF_ID AS createStaffId,
    DATE_FORMAT(
        ppil.CREATE_TIME,
        '%Y-%m-%d'
    ) AS createTime
FROM
    pi_purchase_pay_item_list ppil
LEFT JOIN ad_staff ast ON ast.STAFF_ID = ppil.CREATE_STAFF_ID
WHERE
    1 = 1
AND ppil.PURCHASE_PAY_ID = '1039264149'

#采购付款单-出入库单据列表
SELECT
    ppil.PURCHASE_PAY_INOUT_LIST_ID AS purchasePayInoutListId,
    ppil.PURCHASE_PAY_ITEM_LIST_ID AS purchasePayItemListId,
    ppil.PURCHASE_PAY_LIST_ID AS purchasePayListId,
    ppil.PURCHASE_PAY_ID AS purchasePayId,
    ppil.INVOICE_ORDER_ID AS invoiceId,
    ppil.INVOICE_ORDER_CODE AS invoiceCode,
    ppil.STATEMENT_ORDER_ID AS statementId,
    ppil.STATEMENT_ORDER_CODE AS statementCode,
    ppil.INOUT_ORDER_ID AS inoutId,
    ppil.INOUT_ORDER_CODE AS inoutCode,
    get_lookup_code_name (ppil.INOUT_ORDER_TYPE) AS orderTypes,
    ppil.INOUT_ORDER_TYPE AS orderType,
    ppil.ACCOUNT_DATE AS accountDate,
    ppil.STOCK_DATE AS stockDate,
    IFNULL(ppil.ORDER_AMOUNT, 0) AS orderAmount,
    IFNULL(ppil.ALREADY_AMOUNT_DZ, 0) AS alreadyAmountDZ,
    IFNULL(ppil.ALREADY_AMOUNT_SP, 0) AS alreadyAmountSP,
    IFNULL(ppil.ALREADY_AMOUNT_FK, 0) AS alreadyAmountFK,
    IFNULL(ppil.ALREADY_AMOUNT_SP, 0) - IFNULL(ppil.ALREADY_AMOUNT_FK, 0) AS alreadyAmountWF,
    IFNULL(ppil.NOW_AMOUNT, 0) AS nowAmount,
    ppil.REMARK AS remark,
    ast.`NAME` AS createStaffName,
    ppil.CREATE_STAFF_ID AS createStaffId,
    DATE_FORMAT(
        ppil.CREATE_TIME,
        '%Y-%m-%d'
    ) AS createTime
FROM
    pi_purchase_pay_inout_list ppil
LEFT JOIN ad_staff ast ON ast.STAFF_ID = ppil.CREATE_STAFF_ID
WHERE
    1 = 1
AND ppil.PURCHASE_PAY_ID = '1039264149'

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