SELECT
ppippe.ORDER_ID,
get_lookup_code_name (ppippe.ORDER_TYPE) AS ORDER_TYPE,
ppippe.ORDER_TYPE AS ORDER_TYPE_CODE,
ppippe. CODE,
ppippe.TOTAL_AMOUNT,
ppippe.ALREADY_AMOUNT,
DATE_FORMAT(
ppippe.STATEMENT_DATE_FROM,
'%Y-%m-%d'
) AS STATEMENT_DATE_FROM,
ppippe.ACCOUNT_DATE,
ppippe.OP_STAFF_NAME,
ppippe.REMARK,
ppippe.QTY,
ppippe.TOTAL_QTY,
ppippe.ROW_AMOUNT,
ppippe.META_ID,
pim. CODE AS META_CODE,
pim.META_NAME,
pim.META_GWIGHT,
pim.META_LENGTH,
pim.META_WIDTH,
ppippe.CREATE_TIME,
ppippe.CREATE_STAFF_NAME,
IF (
ppippe.BANYIN_REAL = NULL,
ppippe.productName,
CONCAT(
ppippe.productName,
'(',
ppippe.BANYIN_REAL,
')'
)
) AS PRODUCT_NAME,
ppippe.CHECK_PERCENT
FROM
(
(
SELECT
ppi.ID AS ORDER_ID,
ppi.ORDER_TYPE,
ppi. CODE,
ppi.TOTAL_AMOUNT,
ppi.ACCOUNT_DATE,
ppi.STOCK_INOUT_TIME AS STATEMENT_DATE_FROM,
get_staff_name (ps.OP_STAFF_ID) AS OP_STAFF_NAME,
ppi.REMARK,
ppil.META_ID,
ppil.LIST_TOTAL_AMOUNT AS ROW_AMOUNT,
ppil.QTY,
ppi.TOTAL_QTY,
ppi.CREATE_TIME,
ads. NAME AS CREATE_STAFF_NAME,
concat(ppb.CHECK_PERCENT * 100, '%') AS CHECK_PERCENT,
NULL AS productName,
NULL AS BANYIN_REAL,
ppb.CHECK_AMOUNT AS ALREADY_AMOUNT
FROM
pi_purchase_inout ppi
LEFT JOIN pi_suppliers ps ON ps.ID = ppi.SUPPLIER_ID
LEFT JOIN pi_purchase_inout_list ppil ON ppil.PURCHASE_INOUT_ID = ppi.id
LEFT JOIN ad_staff ads ON ads.STAFF_ID = ppi.CREATE_STAFF_ID
LEFT JOIN pi_purchase_balance ppb ON ppb.ORDER_ID = ppi.id
WHERE
1 = 1
AND ppi. STATUS = 'PI2605'
AND IFNULL(ppb.CHECK_PERCENT, 0) < 1
AND ppi.OWNER_ID = '1020447100'
AND ppi.SUPPLIER_ID = '1036602203'
)
UNION ALL
(
SELECT
ppe.PURCHASE_EXPENSE_ID AS ORDER_ID,
ppe.ORDER_TYPE,
ppe. CODE,
ppe.TOTAL_AMOUNT,
ppe.ACCOUNT_DATE,
ppe.STOCK_TIME AS STATEMENT_DATE_FROM,
get_staff_name (ps.OP_STAFF_ID) AS OP_STAFF_NAME,
ppe.REMARK,
NULL AS META_ID,
0 AS ROW_AMOUNT,
0 AS QTY,
0 AS TOTAL_QTY,
ppe.CREATE_TIME,
ads. NAME AS CREATE_STAFF_NAME,
concat(ppb.CHECK_PERCENT * 100, '%') AS CHECK_PERCENT,
mmp. NAME AS productName,
ppe.BANYIN_REAL AS BANYIN_REAL,
ppb.CHECK_AMOUNT AS ALREADY_AMOUNT
FROM
pi_purchase_expense ppe
LEFT JOIN pi_suppliers ps ON ps.ID = ppe.SUPPLIER_ID
LEFT JOIN ad_staff ads ON ads.STAFF_ID = ppe.CREATE_STAFF_ID
LEFT JOIN pi_purchase_balance ppb ON ppb.ORDER_ID = ppe.PURCHASE_EXPENSE_ID
LEFT JOIN mm_product mmp ON mmp.PRODUCT_ID = ppe.PRODUCT_ID
WHERE
1 = 1
AND ppe. STATUS = 'PI2904'
AND IFNULL(ppb.CHECK_PERCENT, 0) < 1
AND ppe.OWNER_ID = '1020447100'
AND ppe.SUPPLIER_ID = '1036602203'
)
UNION ALL
(
SELECT
pppb.PRTMANUFACT_PRICE_BILL_ID AS ORDER_ID,
pppb.ORDER_TYPE,
pppb. CODE,
pppb.TOTAL_MANUFACT_AMOUNT AS TOTAL_AMOUNT,
pppb.ACCOUNT_DATE,
pppb.STOCK_TIME AS STATEMENT_DATE_FROM,
ads. NAME AS OP_STAFF_NAME,
pppb.REMARK,
NULL AS META_ID,
0 AS ROW_AMOUNT,
0 AS QTY,
0 AS TOTAL_QTY,
pppb.CREATE_TIME,
adsa. NAME AS CREATE_STAFF_NAME,
concat(ppb.CHECK_PERCENT * 100, '%') AS CHECK_PERCENT,
mmp. NAME AS productName,
pppb.BANYIN_REAL AS BANYIN_REAL,
ppb.CHECK_AMOUNT AS ALREADY_AMOUNT
FROM
pi_prtmanufact_price_bill pppb
LEFT JOIN pi_suppliers ps ON ps.ID = pppb.SUPPLIER_ID
LEFT JOIN ad_staff ads ON ads.STAFF_ID = ps.OP_STAFF_ID
LEFT JOIN ad_staff adsa ON adsa.STAFF_ID = pppb.CREATE_STAFF_ID
LEFT JOIN pi_purchase_balance ppb ON ppb.ORDER_ID = pppb.PRTMANUFACT_PRICE_BILL_ID
LEFT JOIN mm_product mmp ON mmp.PRODUCT_ID = pppb.PRODUCT_ID
WHERE
1 = 1
AND pppb. STATUS = 'PI2904'
AND IFNULL(ppb.CHECK_PERCENT, 0) < 1
AND pppb.OWNER_ID = '1020447100'
AND pppb.SUPPLIER_ID = '1036602203'
)
) AS ppippe
LEFT JOIN pi_metarials pim ON pim.id = ppippe.META_ID
LEFT JOIN pi_purchase_statement_list ppsl ON ppsl.ORDER_ID = ppippe.ORDER_ID
WHERE
1 = 1
GROUP BY
ORDER_ID
ORDER BY
ppippe. CODE DESC
LIMIT 50;