#采购收票单列表
SELECT
ppi.PURCHASE_INVOICE_ID,
ppi. CODE,
ppi.OWNER_ID,
ppi.SUPPLIER_ID,
ppi.ORDER_TYPE,
ppi.TOTAL_AMOUNT,
ppi.TOTAL_NOW_AMOUNT,
ppi.ACCOUNT_DATE,
ppi.OP_STAFF_ID,
ppi.DEPARTMENT_ID,
ppi. STATUS,
ppi.INVOICE_NO,
ppi.INVOICE_TYPE,
ppi.INVOICE_TITLE,
ppi.PRINT_COUNT,
ppi.PRINT_STAFF_ID,
ppi.PRINT_TIME,
ppi.REMARK,
ppi.VERIFY_STAFF_ID,
ppi.VERIFY_TIME,
ppi.STOCK_STAFF_ID,
ppi.STOCK_TIME,
ppi.CREATE_STAFF_ID,
ppi.CREATE_TIME,
ppi.UPDATE_TIME,
ppi.UPDATE_STAFF_ID,
ppi.COMPANY_ID,
ppi.SYSTEM_ID,
pis. NAME SUPPLIER_NAME,
adp. NAME DEPARTMENT_NAME,
pio. NAME OWNER_NAME,
adsa. NAME OP_STAFF_NAME,
adsb. NAME STOCK_STAFF_NAME,
adsc. NAME CREATE_STAFF_NAME,
ppi.ALREADY_PAY_PERCENT
FROM
(
SELECT
ppi.PURCHASE_INVOICE_ID,
ppi. CODE,
ppi.OWNER_ID,
ppi.SUPPLIER_ID,
ppi.ORDER_TYPE,
ppi.TOTAL_AMOUNT,
ppi.TOTAL_NOW_AMOUNT,
ppi.ACCOUNT_DATE,
ppi.OP_STAFF_ID,
ppi.DEPARTMENT_ID,
ppi. STATUS,
ppi.INVOICE_NO,
ppi.INVOICE_TYPE,
ppi.INVOICE_TITLE,
ppi.PRINT_COUNT,
ppi.PRINT_STAFF_ID,
ppi.PRINT_TIME,
ppi.REMARK,
ppi.VERIFY_STAFF_ID,
ppi.VERIFY_TIME,
ppi.STOCK_STAFF_ID,
ppi.STOCK_TIME,
ppi.CREATE_STAFF_ID,
ppi.CREATE_TIME,
ppi.UPDATE_TIME,
ppi.UPDATE_STAFF_ID,
ppi.COMPANY_ID,
ppi.SYSTEM_ID,
ppi.ORDER_CODE,
ppi.INOUT_ORDER_CODE,
ppi.ALREADY_PAY_PERCENT
FROM
(
SELECT
ppi.PURCHASE_INVOICE_ID,
ppi. CODE,
ppi.OWNER_ID,
ppi.SUPPLIER_ID,
ppi.ORDER_TYPE,
ppi.TOTAL_AMOUNT,
ppi.TOTAL_NOW_AMOUNT,
ppi.ACCOUNT_DATE,
ppi.OP_STAFF_ID,
ppi.DEPARTMENT_ID,
ppi. STATUS,
ppi.INVOICE_NO,
ppi.INVOICE_TYPE,
ppi.INVOICE_TITLE,
ppi.PRINT_COUNT,
ppi.PRINT_STAFF_ID,
ppi.PRINT_TIME,
ppi.REMARK,
ppi.VERIFY_STAFF_ID,
ppi.VERIFY_TIME,
ppi.STOCK_STAFF_ID,
ppi.STOCK_TIME,
ppi.CREATE_STAFF_ID,
ppi.CREATE_TIME,
ppi.UPDATE_TIME,
ppi.UPDATE_STAFF_ID,
ppi.COMPANY_ID,
ppi.SYSTEM_ID,
ppil.ORDER_CODE,
ppiil.INOUT_ORDER_CODE,
ppb.INVOICE_PERCENT AS ALREADY_PAY_PERCENT
FROM
pi_purchase_invoice ppi
LEFT JOIN pi_purchase_invoice_list ppil ON ppil.PURCHASE_INVOICE_ID = ppi.PURCHASE_INVOICE_ID
LEFT JOIN pi_purchase_invoice_inout_list ppiil ON ppiil.PURCHASE_INVOICE_ID = ppi.PURCHASE_INVOICE_ID
LEFT JOIN pi_purchase_balance ppb ON ppb.ORDER_ID = ppi.PURCHASE_INVOICE_ID
) AS ppi
LEFT JOIN pi_purchase_statement_list ppsl ON ppi.ORDER_CODE = ppsl.ORDER_CODE
) AS ppi
LEFT JOIN pi_suppliers pis ON pis.id = ppi.supplier_id
LEFT JOIN ad_department adp ON adp.DEPARTMENT_ID = ppi.department_id
LEFT JOIN pi_owner pio ON pio.id = ppi.owner_id
LEFT JOIN ad_staff adsa ON adsa.staff_id = ppi.OP_STAFF_ID
LEFT JOIN ad_staff adsb ON adsb.staff_id = ppi.STOCK_STAFF_ID
LEFT JOIN ad_staff adsc ON adsc.staff_id = ppi.CREATE_STAFF_ID
WHERE
1 = 1
#AND ppi. STATUS IN (?, ?, ?, ?, ?)
AND ppi.COMPANY_ID = '1000001425'
GROUP BY
ppi.PURCHASE_INVOICE_ID
ORDER BY
ppi.CREATE_TIME DESC
LIMIT 50;
#采购收票单详情-对账单列表
SELECT
ppil.PURCHASE_INVOICE_ID,
ppil.PURCHASE_INVOICE_LIST_ID,
pps.PURCHASE_STATEMENT_ID,
pps.STATEMENT_DATE_FROM,
pps.STATEMENT_DATE_TO,
pps.VERIFY_TIME AS STOCK_TIME,
get_lookup_code_name (pps.ORDER_TYPE) AS ORDER_TYPE_NAME,
pps.TOTAL_AMOUNT AS ORDER_AMOUNT,
ppsl.ORDER_CODE AS ORDER_CODE_LIST,
ppsl.ORDER_TYPE AS ORDER_TYPE_LIST,
get_lookup_code_name (ppsl.ORDER_TYPE) AS ORDER_TYPE_LIST_NAME,
IFNULL(
ppio.CREATE_TIME,
ppe.CREATE_TIME
) AS CREATE_TIME_LIST,
IFNULL(ppio.REMARK, ppe.REMARK) AS REMARK_LIST,
ppe.ACCOUNT_DATE,
ppil.NOW_AMOUNT,
ppil.ORDER_ID,
ppil.ORDER_CODE,
ppil.ORDER_TYPE,
ppil.ALREADY_AMOUNT_SP,
ppil.REMARK,
ppil.CREATE_STAFF_ID,
ppil.CREATE_TIME,
pps.TOTAL_NOW_AMOUNT AS ALREADY_AMOUNT_DZ,
ppil.ALREADY_AMOUNT_SP
FROM
pi_purchase_invoice_list ppil
LEFT JOIN pi_purchase_statement pps ON pps.PURCHASE_STATEMENT_ID = ppil.ORDER_ID
LEFT JOIN pi_purchase_statement_list ppsl ON ppsl.PURCHASE_STATEMENT_ID = pps.PURCHASE_STATEMENT_ID
LEFT JOIN pi_purchase_balance_log ppbl ON ppbl.RELATED_ORDER_ID = ppsl.ORDER_ID
LEFT JOIN pi_purchase_inout ppio ON ppio.ID = ppsl.ORDER_ID
LEFT JOIN pi_purchase_expense ppe ON ppe.PURCHASE_EXPENSE_ID = ppsl.ORDER_ID
LEFT JOIN pi_purchase_balance ppb ON ppb.order_id = ppsl.ORDER_ID
WHERE
1 = 1
AND ppil.PURCHASE_INVOICE_ID = '1039264099'
GROUP BY
ppil.PURCHASE_INVOICE_LIST_ID
ORDER BY
ORDER_CODE DESC
#采购收票单详情-对账单明细列表
SELECT
ppil.PURCHASE_INVOICE_ID,
ppil.PURCHASE_INVOICE_LIST_ID,
ppiil.STATEMENT_ORDER_ID AS PURCHASE_STATEMENT_ID,
ppil.ORDER_CODE,
ppiil.INOUT_ORDER_ID AS ORDER_ID_LIST,
ppil.ORDER_ID AS ORDER_ID,
ppiil.INOUT_ORDER_CODE AS ORDER_CODE_LIST,
ppiil.INOUT_ORDER_TYPE AS ORDER_TYPE_LIST,
get_lookup_code_name (ppiil.INOUT_ORDER_TYPE) AS ORDER_TYPE_LIST_NAME,
IFNULL(
IFNULL(
ppio.STOCK_INOUT_TIME,
ppe.STOCK_TIME
),
pppb.STOCK_TIME
) AS STOCK_INOUT_TIME,
IFNULL(
IFNULL(ppio.REMARK, ppe.REMARK),
pppb.REMARK
) AS REMARK_LIST,
ppiil.ORDER_AMOUNT,
IFNULL(
IFNULL(
ppe.ACCOUNT_DATE,
ppio.ACCOUNT_DATE
),
pppb.ACCOUNT_DATE
) AS ACCOUNT_DATE,
ppiil.NOW_AMOUNT AS NOW_AMOUNT_LIST,
ppiil.ALREADY_AMOUNT_DZ,
ppiil.ALREADY_AMOUNT_KP
FROM
pi_purchase_invoice_list ppil
LEFT JOIN pi_purchase_invoice_inout_list ppiil ON ppiil.PURCHASE_INVOICE_LIST_ID = ppil.PURCHASE_INVOICE_LIST_ID
LEFT JOIN pi_purchase_inout ppio ON ppio.ID = ppiil.INOUT_ORDER_ID
LEFT JOIN pi_purchase_expense ppe ON ppe.PURCHASE_EXPENSE_ID = ppiil.INOUT_ORDER_ID
LEFT JOIN pi_prtmanufact_price_bill pppb ON pppb.PRTMANUFACT_PRICE_BILL_ID = ppiil.INOUT_ORDER_ID
WHERE
1 = 1
AND ppil.PURCHASE_INVOICE_ID = '1039264099'
GROUP BY
ppiil.PURCHASE_INVOICE_INOUT_LIST_ID
ORDER BY
ORDER_CODE,
ORDER_CODE_LIST,
ORDER_TYPE_LIST_NAME DESC