生产进度报表

#生产进度报表
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;

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