#采购付款单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'