#生产进度报表
SELECT DISTINCT
ppb.PRTMANUFACT_BILL_ID AS prtmanufactBillId,
ppb.`CODE` AS CODE,
get_lookup_code_name (ppb.PRTTIME_TYPE) AS prttimeType,
mp.ISBN AS isbn,
mp.STANDARD_NAME AS standardName,
mp.CATALOG_B_NAME AS catalogBName,
ppb.BANYIN_CIP AS banyinCip,
mp.PUBLISH_DATE AS publishDate,
mp.PRINT_DATE AS printDate,
mp.IMPRESSIONS AS impressions,
mp.PRODUCT_PRICE AS productPrice,
mp.PUBLISH_NAME AS publishName,
ppb.PRTING_QTY AS printQty,
ppb.RECVGOODS_DATE AS recvgoodsDate,
ppb.CURRENT_PROGRESS AS currentProgres,
ppb.CURRENT_PROGRESS_TIME AS currentProgresTime,
get_staff_name (ppb.CREATE_STAFF_ID) AS createStaffId,
ppb.BILL_DATE AS billDate,
GROUP_CONCAT(ps.`NAME` SEPARATOR ',') AS supplierName,
# ps.`NAME` AS supplierName,
get_lookup_code_name (mp.BINDING) AS bindDing,
ppb.BANYIN_REAL AS banyinReal,
ppb.DIFFICULTY_DEGREE AS difficultyDegree,
wqc.VERIFY_TIME AS qcDate,
get_lookup_code_name(wqc.FINAL_JUDGE) AS qcResult,
ppb.PRT_CLOSED_DATE AS prtClosedDate,
ppb.REASON_AFTER AS reasonAfter,
s1.stockInTime AS stockInTime,
s1.producyQuantity AS producyQuantity,
DATEDIFF(
s1.stockInTime,
ppb.BILL_DATE
) AS productionDays,
DATEDIFF(
s1.stockInTime,
ppb.RECVGOODS_DATE
) AS lateDays
FROM
pi_prtmanufact_bill ppb
LEFT JOIN mm_product mp ON mp.PRODUCT_ID = ppb.PRODUCT_ID
LEFT JOIN pi_prtmanufact_bill_list ppbl ON ppbl.PRTMANUFACT_BILL_ID = ppb.PRTMANUFACT_BILL_ID
LEFT JOIN pi_suppliers ps ON ppbl.SUPPLIER_ID = ps.ID
LEFT JOIN (
SELECT
wstl.PRODUCT_ID AS productId,
wstl.remark AS banyinReal,
wst.stock_in_time AS stockInTime,
wstl.PRODUCT_QUANTITY AS producyQuantity
FROM
wm_stock_transfer_plan AS wst
INNER JOIN wm_stock_transfer_plan_list AS wstl ON wst.STOCK_TRANSFER_PLAN_ID = wstl.STOCK_TRANSFER_PLAN_ID
LEFT JOIN pi_prtmanufact_bill ppb ON ppb.PRODUCT_ID = wstl.PRODUCT_ID
WHERE
wst.COMPANY_ID = '1000001425'
AND wst.STOCK_IN_STATUS = 'XT3702'
AND wst.CANCEL_STATUS = 'XT1101'
AND wst.TO_STORAGE_TYPE_ID = '1008616537'
AND wst.FROM_STORAGE_TYPE_ID = '1000032016'
) s1 ON s1.productId = ppb.PRODUCT_ID
AND s1.banyinReal = ppb.BANYIN_REAL
INNER JOIN (
SELECT
wqc.DEL_FLAG,
wqc.VERIFY_STATUS,
wqc.VERIFY_TIME,
wqcl.FINAL_JUDGE,
wqcl.PRODUCT_ID
FROM
wm_quality_check_list wqcl
RIGHT JOIN wm_quality_check wqc ON wqcl.QUALITY_CHECK_ID = wqc.QUALITY_CHECK_ID
WHERE wqc.DEL_FLAG = 0 AND wqc.VERIFY_STATUS='XT10604' AND wqc.CANCEL_STATUS='XT1101'
) wqc ON wqc.PRODUCT_ID = ppb.PRODUCT_ID
WHERE
1 = 1
# AND ppb.`CODE` in ('MPA00000211','MPA00000215','MPA00000148','MPA00000135')
#AND ppb.RECVGOODS_DATE >= ?
#AND ppb.RECVGOODS_DATE < ?
AND ppb.`STATUS` != 'PI2906'
GROUP BY
ppb.PRTMANUFACT_BILL_ID
ORDER BY
ppb.`CODE` DESC
LIMIT 500;