<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.rongwei.bscommon.sys.dao.ApsProductionOrderDao">

    <resultMap id="BaseResultMap" type="com.rongwei.bsentity.domain.ApsProductionOrderDo">
        <id property="id" column="ID" jdbcType="VARCHAR"/>
        <result property="orderno" column="ORDERNO" jdbcType="VARCHAR"/>
        <result property="customname" column="CUSTOMNAME" jdbcType="VARCHAR"/>
        <result property="orderdate" column="ORDERDATE" jdbcType="TIMESTAMP"/>
        <result property="deliverydate" column="DELIVERYDATE" jdbcType="TIMESTAMP"/>
        <result property="allowday" column="ALLOWDAY" jdbcType="INTEGER"/>
        <result property="orderstatus" column="ORDERSTATUS" jdbcType="VARCHAR"/>
        <result property="auditstatus" column="AUDITSTATUS" jdbcType="VARCHAR"/>
        <result property="auditor" column="AUDITOR" jdbcType="VARCHAR"/>
        <result property="auditorid" column="AUDITORID" jdbcType="VARCHAR"/>
        <result property="auditdate" column="AUDITDATE" jdbcType="TIMESTAMP"/>
        <result property="productionstatus" column="PRODUCTIONSTATUS" jdbcType="VARCHAR"/>
        <result property="promisedeliverydate" column="PROMISEDELIVERYDATE" jdbcType="TIMESTAMP"/>
        <result property="customwarehouse" column="CUSTOMWAREHOUSE" jdbcType="VARCHAR"/>
        <result property="deliverytime" column="DELIVERYTIME" jdbcType="INTEGER"/>
        <result property="belongfactory" column="BELONGFACTORY" jdbcType="VARCHAR"/>
        <result property="ordertype" column="ORDERTYPE" jdbcType="VARCHAR"/>
        <result property="salesman" column="SALESMAN" jdbcType="VARCHAR"/>
        <result property="businessunit" column="BUSINESSUNIT" jdbcType="VARCHAR"/>
        <result property="customorderno" column="CUSTOMORDERNO" jdbcType="VARCHAR"/>
        <result property="isfromcustom" column="ISFROMCUSTOM" jdbcType="VARCHAR"/>
        <result property="customordertype" column="CUSTOMORDERTYPE" jdbcType="VARCHAR"/>
        <result property="customorderstatus" column="CUSTOMORDERSTATUS" jdbcType="VARCHAR"/>
        <result property="tenantid" column="TENANTID" jdbcType="VARCHAR"/>
        <result property="roption" column="ROPTION" jdbcType="VARCHAR"/>
        <result property="deleted" column="DELETED" jdbcType="CHAR"/>
        <result property="remark" column="REMARK" jdbcType="VARCHAR"/>
        <result property="createdate" column="CREATEDATE" jdbcType="TIMESTAMP"/>
        <result property="createuserid" column="CREATEUSERID" jdbcType="VARCHAR"/>
        <result property="modifydate" column="MODIFYDATE" jdbcType="TIMESTAMP"/>
        <result property="modifyuserid" column="MODIFYUSERID" jdbcType="VARCHAR"/>
        <result property="createusername" column="CREATEUSERNAME" jdbcType="VARCHAR"/>
        <result property="modifyusername" column="MODIFYUSERNAME" jdbcType="VARCHAR"/>
        <result property="scheduledeliverydate" column="SCHEDULEDELIVERYDATE" jdbcType="TIMESTAMP"/>
        <result property="actualdeliverydate" column="ACTUALDELIVERYDATE" jdbcType="TIMESTAMP"/>
        <result property="customid" column="CUSTOMID" jdbcType="VARCHAR"/>
        <result property="status" column="STATUS" jdbcType="VARCHAR"/>
        <result property="processinstid" column="PROCESSINSTID" jdbcType="VARCHAR"/>
        <result property="customwarehouseid" column="CUSTOMWAREHOUSEID" jdbcType="VARCHAR"/>
        <result property="blanktype" column="BLANKTYPE" jdbcType="VARCHAR"/>
        <result property="compositionchange" column="COMPOSITIONCHANGE" jdbcType="VARCHAR"/>
        <result property="changeremark" column="CHANGEREMARK" jdbcType="VARCHAR"/>
        <result property="blamkthickness" column="BLAMKTHICKNESS" jdbcType="DECIMAL"/>
        <result property="blankhomogenization" column="BLANKHOMOGENIZATION" jdbcType="VARCHAR"/>
        <result property="finishtemperature" column="FINISHTEMPERATURE" jdbcType="VARCHAR"/>
        <result property="thicknesstolerance" column="THICKNESSTOLERANCE" jdbcType="VARCHAR"/>
        <result property="widthtolerance" column="WIDTHTOLERANCE" jdbcType="VARCHAR"/>
        <result property="lengthtolerance" column="LENGTHTOLERANCE" jdbcType="VARCHAR"/>
        <result property="bendrequirement" column="BENDREQUIREMENT" jdbcType="VARCHAR"/>
        <result property="joint" column="JOINT" jdbcType="VARCHAR"/>
        <result property="swabbing" column="SWABBING" jdbcType="VARCHAR"/>
        <result property="tensilestrength" column="TENSILESTRENGTH" jdbcType="VARCHAR"/>
        <result property="yieldstrength" column="YIELDSTRENGTH" jdbcType="VARCHAR"/>
        <result property="elongationrate" column="ELONGATIONRATE" jdbcType="VARCHAR"/>
        <result property="pinhole" column="PINHOLE" jdbcType="VARCHAR"/>
        <result property="cupping" column="CUPPING" jdbcType="VARCHAR"/>
        <result property="earing" column="EARING" jdbcType="VARCHAR"/>
        <result property="innercorediameter" column="INNERCOREDIAMETER" jdbcType="DECIMAL"/>
        <result property="packagingmethod" column="PACKAGINGMETHOD" jdbcType="VARCHAR"/>
        <result property="tag" column="TAG" jdbcType="VARCHAR"/>
        <result property="surfacerequirement" column="SURFACEREQUIREMENT" jdbcType="VARCHAR"/>
        <result property="istensionlevelling" column="ISTENSIONLEVELLING" jdbcType="VARCHAR"/>
        <result property="packagingrequirement" column="PACKAGINGREQUIREMENT" jdbcType="VARCHAR"/>
        <result property="surfacehardness" column="SURFACEHARDNESS" jdbcType="VARCHAR"/>
        <result property="nomod" column="NOMOD" jdbcType="VARCHAR"/>
        <result property="rollweightrequirement" column="ROLLWEIGHTREQUIREMENT" jdbcType="VARCHAR"/>
        <result property="otherorderstandard" column="OTHERORDERSTANDARD" jdbcType="VARCHAR"/>
        <result property="technicalreviewerid" column="TECHNICALREVIEWERID" jdbcType="VARCHAR"/>
        <result property="technicalreviewername" column="TECHNICALREVIEWERNAME" jdbcType="VARCHAR"/>
        <result property="technicalreviewdate" column="TECHNICALREVIEWDATE" jdbcType="TIMESTAMP"/>
        <result property="qualityreviewerid" column="QUALITYREVIEWERID" jdbcType="VARCHAR"/>
        <result property="qualityreviewername" column="QUALITYREVIEWERNAME" jdbcType="VARCHAR"/>
        <result property="qualityreviewdate" column="QUALITYREVIEWDATE" jdbcType="TIMESTAMP"/>
        <result property="reviewerid" column="REVIEWERID" jdbcType="VARCHAR"/>
        <result property="reviewername" column="REVIEWERNAME" jdbcType="VARCHAR"/>
        <result property="reviewdate" column="REVIEWDATE" jdbcType="TIMESTAMP"/>
        <result property="idmatureprocess" column="IDMATUREPROCESS" jdbcType="VARCHAR"/>
        <result property="otherprecaution" column="OTHERPRECAUTION" jdbcType="VARCHAR"/>
        <result property="enddate" column="ENDDATE" jdbcType="TIMESTAMP"/>
        <result property="plandate" column="PLANDATE" jdbcType="TIMESTAMP"/>
        <result property="reviewtype" column="REVIEWTYPE" jdbcType="VARCHAR"/>
<!--        <result property="schedulstatus" column="SCHEDULSTATUS" jdbcType="VARCHAR"/>-->
    </resultMap>

    <sql id="Base_Column_List">
        ID,ORDERNO,CUSTOMNAME,
        ORDERDATE,DELIVERYDATE,ALLOWDAY,
        ORDERSTATUS,AUDITSTATUS,AUDITOR,
        AUDITORID,AUDITDATE,PRODUCTIONSTATUS,
        PROMISEDELIVERYDATE,CUSTOMWAREHOUSE,DELIVERYTIME,
        BELONGFACTORY,ORDERTYPE,SALESMAN,
        BUSINESSUNIT,CUSTOMORDERNO,ISFROMCUSTOM,
        CUSTOMORDERTYPE,CUSTOMORDERSTATUS,
        TENANTID,ROPTION,DELETED,
        REMARK,CREATEDATE,CREATEUSERID,
        MODIFYDATE,MODIFYUSERID,CREATEUSERNAME,
        MODIFYUSERNAME,SCHEDULEDELIVERYDATE,ACTUALDELIVERYDATE,
        CUSTOMID,STATUS,PROCESSINSTID,
        CUSTOMWAREHOUSEID,BLANKTYPE,COMPOSITIONCHANGE,
        CHANGEREMARK,BLAMKTHICKNESS,BLANKHOMOGENIZATION,
        FINISHTEMPERATURE,THICKNESSTOLERANCE,WIDTHTOLERANCE,
        LENGTHTOLERANCE,BENDREQUIREMENT,JOINT,
        SWABBING,TENSILESTRENGTH,YIELDSTRENGTH,
        ELONGATIONRATE,PINHOLE,CUPPING,
        EARING,INNERCOREDIAMETER,PACKAGINGMETHOD,
        TAG,SURFACEREQUIREMENT,ISTENSIONLEVELLING,
        PACKAGINGREQUIREMENT,SURFACEHARDNESS,NOMOD,
        ROLLWEIGHTREQUIREMENT,OTHERORDERSTANDARD,TECHNICALREVIEWERID,
        TECHNICALREVIEWERNAME,TECHNICALREVIEWDATE,QUALITYREVIEWERID,
        QUALITYREVIEWERNAME,QUALITYREVIEWDATE,REVIEWERID,
        REVIEWERNAME,REVIEWDATE,IDMATUREPROCESS,
        OTHERPRECAUTION,ENDDATE,PLANDATE,
        REVIEWTYPE,SCHEDULSTATUS
    </sql>
    <update id="publish">
        <if test="ids != null and ids.size() >0">
            <foreach collection="ids" item="id" separator=";">
                update aps_production_order b
                <set>
                    PRODUCTIONSTATUS=(
                    SELECT
                    CASE
                    WHEN GROUP_CONCAT(DISTINCT apo.PROCESSSTATUS) = '10' THEN '10'
                    WHEN GROUP_CONCAT(DISTINCT apo.PROCESSSTATUS) = '20' THEN '20'
                    WHEN GROUP_CONCAT(DISTINCT apo.PROCESSSTATUS) = '30' THEN '30'
                    WHEN GROUP_CONCAT(DISTINCT apo.PROCESSSTATUS) = '40' THEN '40'
                    WHEN GROUP_CONCAT(DISTINCT apo.PROCESSSTATUS) = '50' THEN '50'
                    WHEN FIND_IN_SET('40',GROUP_CONCAT(DISTINCT apo.PROCESSSTATUS)) THEN '40'
                    WHEN FIND_IN_SET('30',GROUP_CONCAT(DISTINCT apo.PROCESSSTATUS)) THEN '30'
                    WHEN FIND_IN_SET('20',GROUP_CONCAT(DISTINCT apo.PROCESSSTATUS)) THEN '20'
                    ELSE b.PRODUCTIONSTATUS END
                    FROM
                    aps_process_operation apo
                    LEFT JOIN aps_blank_order abo ON apo.BLANKID = abo.ID AND abo.DELETED='0'
                    where apo.DELETED='0'
                    and abo.PRODUCTIONORDERID =#{id}
                    GROUP BY abo.PRODUCTIONORDERID
                    )
                </set>
                <where>
                    ID = #{id} and DELETED='0'
                </where>
            </foreach>
        </if>
    </update>
    <update id="publishCancel">
        <if test="ids != null and ids.size() >0">
            <foreach collection="ids" item="id" separator=";">
                update aps_production_order b
                <set>
                    PRODUCTIONSTATUS=(
                    SELECT
                    CASE
                    WHEN GROUP_CONCAT(DISTINCT abo.PRODUCTSTATUS) = '10' THEN '10'
                    WHEN GROUP_CONCAT(DISTINCT abo.PRODUCTSTATUS) = '20' THEN '20'
                    WHEN GROUP_CONCAT(DISTINCT abo.PRODUCTSTATUS) = '30' THEN '30'
                    WHEN GROUP_CONCAT(DISTINCT abo.PRODUCTSTATUS) = '40' THEN '40'
                    WHEN GROUP_CONCAT(DISTINCT abo.PRODUCTSTATUS) = '50' THEN '50'
                    WHEN FIND_IN_SET('50',GROUP_CONCAT(DISTINCT abo.PRODUCTSTATUS)) THEN '40'
                    WHEN FIND_IN_SET('40',GROUP_CONCAT(DISTINCT abo.PRODUCTSTATUS)) THEN '40'
                    WHEN FIND_IN_SET('30',GROUP_CONCAT(DISTINCT abo.PRODUCTSTATUS)) THEN '30'
                    WHEN FIND_IN_SET('20',GROUP_CONCAT(DISTINCT abo.PRODUCTSTATUS)) THEN '20'
                    ELSE b.PRODUCTIONSTATUS
                    END
                    FROM aps_blank_order abo
                    where abo.DELETED='0'
                    and abo.PRODUCTIONORDERID =#{id}
                    GROUP BY abo.PRODUCTIONORDERID
                    )
                </set>
                <where>
                    ID = #{id} and DELETED='0'
                </where>
            </foreach>
        </if>
    </update>
    <select id="getStartWorkById" resultType="java.lang.String">
        select DISTINCT CONCAT( apo.CUSTOMNAME,'-',GROUP_CONCAT(DISTINCT apo.ORDERNO )  )
        from aps_production_order apo
        left join aps_blank_order abo on apo.ID=abo.PRODUCTIONORDERID AND abo.DELETED='0'
        left join aps_process_operation apo1 on apo1.BLANKID =abo.ID and apo1.DELETED='0'
        <where>
             apo.DELETED='0'
            <if test="ids != null and ids.size() >0">
                and apo.ID in
                <foreach collection="ids" open="(" close=")" separator="," item="id">
                    #{id}
                </foreach>
            </if>
            and apo1.PROCESSSTATUS in ('40','50')
        </where>
        group by apo.CUSTOMNAME
    </select>
    <select id="getStartWorkByBlankId" resultType="java.lang.String">
        select DISTINCT CONCAT( apo.CUSTOMNAME,'-',GROUP_CONCAT(DISTINCT apo.ORDERNO )  )
        from aps_production_order apo
        left join aps_blank_order abo on apo.ID=abo.PRODUCTIONORDERID AND abo.DELETED='0'
        <where>
            apo.DELETED='0'
            <if test="ids != null and ids.size() >0">
                and abo.ID in
                <foreach collection="ids" open="(" close=")" separator="," item="id">
                    #{id}
                </foreach>
            </if>
            and abo.PRODUCTSTATUS in ('40','50')
        </where>
        group by apo.CUSTOMNAME
    </select>
    <select id="getDataByBlankId" resultType="com.rongwei.bsentity.domain.ApsProductionOrderDo">
        select * from aps_production_order where ID in(
        select PRODUCTIONORDERID from aps_blank_order where DELETED ='0' AND ID IN
        <foreach collection="blankIds" open="(" close=")" separator="," item="id">
                                                              #{id}
        </foreach>
        )
        and SCHEDULEDELIVERYDATE>PROMISEDELIVERYDATE
    </select>
    <select id="getWorkShopByBlankId" resultType="com.rongwei.bsentity.vo.BlankIdAndWorkShopVo">
        SELECT
            apo.BLANKID AS 'blankId',
            GROUP_CONCAT(DISTINCT apope.PROCESSWORKSHOP ) AS 'prcessWorkShop'
        FROM
            aps_process_operation apo
            LEFT JOIN aps_process_operation_process_equ apope ON apope.DELETED = 0
            AND apo.ID = apope.PROCESSID
        WHERE
            apo.DELETED = 0
            AND apo.IFBLANKPROCESS = '是'
            AND apo.BLANKID IN
            <foreach collection="blankIdList" item="item" open="(" close=")" separator=",">
                #{item}
            </foreach>
        GROUP BY
            apo.BLANKID
    </select>
    <select id="selectListAndPlanDate" resultType="com.rongwei.bsentity.domain.ApsProcessOperationDo">
        SELECT a.ID,
            a.TENANTID,
            a.ROPTION,
            a.DELETED,
            a.REMARK,
            a.CREATEDATE,
            a.CREATEUSERID,
            a.MODIFYDATE,
            a.MODIFYUSERID,
            a.CREATEUSERNAME,
            a.MODIFYUSERNAME,
            a.OWNEDFACTORY,
            a.CRAFTSTEPID,
            a.STEPNUM,
            a.PROCESSID,
            a.PROCESSCODE,
            a.PROCESS,
            a.PROCESSNAME,
            a.PROCESSWAY,
            a.NUMUNIT,
            a.ONCEPROCESSVAL,
            a.ONCEPROCESSMIN,
            a.PERTIMECAPACITY,
            a.TIMEUNIT,
            a.PREPRESSWORKMIN,
            a.LOADINGMIN,
            a.CUTTINGMIN,
            a.CUTFINISHMIN,
            a.MINFLOWBATCH,
            a.MINFLOWWAITMIN,
            a.MAXFLOWWAITMIN,
            a.IFBLANKPROCESS,
            a.MATERIALCODE,
            a.MATERIALNAME,
            a.THICKNESS,
            a.THICKSTR,
            a.THICKEND,
            a.PROWIDTH,
            a.WIDTHSTR,
            a.WIDTHEND,
            a.PROLENGTH,
            a.LENGTHSTR,
            a.LENGTHEND,
            a.INMATERCONDITION,
            a.OUTMATERREQUEST,
            a.CANCHOOSEDEVICEID,
            a.CHOOSEEQU,
            a.BLANKID,
            a.PLANINPUTID,
            a.PLANINPUT,
            a.PLANOUTPUT,
            a.PLANINRALL,
            a.PLANBLANKRALL,
            a.PLANPROCESSRALL,
            a.PROCESSWORKSHOPID,
            a.PROCESSWORKSHOP,
            a.PROCESSDEVICEID,
            a.PROCESSDEVICE,
            MIN(b.PLANSTARTDATE) AS 'PLANSTARTDATE',
            a.ACTUALSTARTDATE,
            MAX(b.PLANENDDATE) AS 'PLANENDDATE',
            a.ACTUALFINISHDATE,
            a.ACTUALIMPUTROLL,
            a.INPUTUNQUALIFIEDROLL,
            a.ACTUALCANPROCESSROLL,
            a.CLOSESTATUS,
            a.SECONDROLL,
            a.STARTINGROLL,
            a.REPORTROLL,
            a.CHECKOUTROLL,
            a.UNFINISHROLL,
            a.CANCELROLL,
            a.WORKSTATUS,
            a.PROCESSSTATUS,
            a.BACHMATERIALPROCESS,
            a.PROCESSTOTALTIME,
            a.WAITREPORTID,
            a.PLANPROCESSNUM,
            a.PREVIOUSPROCESSID,
            a.PREVIOUSPROCESS,
            a.NEXTPROCESSID,
            a.NEXTPROCESS,
            a.LEVEL,
            a.PRODUCTTYPEID,
            a.PRODUCTTYPE,
            a.METAL,
            a.METALSTATE,
            a.OUTPUTQUALIFIEDNUM,
            a.OUTPUTUNQUALIFIEDNUM,
            a.CHANGEINMETER,
            a.SINGLEROLLWEIGHT,
            a.LOCKMARK,
            a.TIMEWAY,
            a.WORKSPEED,
            a.LEAVEWAITJOBROLL,
            a.LEAVEWAITWORKROLL,
            a.ISSUBSECTION
             FROM aps_process_operation a LEFT JOIN aps_process_operation_process_equ b ON b.DELETED = 0 AND a.ID = b.PROCESSID WHERE a.DELETED='0' 
             <if test="blankIdList != null and blankIdList.size > 0">
                 AND a.BLANKID IN
                 <foreach collection="blankIdList" separator="," open="(" close=")" item="item">
                     #{item}
                 </foreach>
             </if>
            GROUP BY 
                a.ID
    </select>
    <select id="selectNowYearPlanGetOrderNum" resultType="com.rongwei.bsentity.domain.ApsReportYearAndMonth">
        select
            aop.TENANTID AS 'tenantid',IFNULL(ROUND(sum(PLANACCEPTORDERSUM),2),0) AS 'planordernumyear'
        from
            aps_accept_order_plan aop
                join aps_accept_order_plan_detail aopd on aop.id=aopd.MAINID
        where
            aop.deleted='0' and aopd.deleted='0'
          and aop.PARTYEAR=YEAR(CURRENT_DATE)
        group by aop.TENANTID
    </select>
    <select id="selectNowYearActualGetOrderNum" resultType="com.rongwei.bsentity.domain.ApsReportYearAndMonth">
        select
        apro.TENANTID AS 'tenantid',
        IFNULL(ROUND(sum((apope.PLANPROCESSRALL-apope.CANCELROLL)*apoom.PLANPRODROLLNUM/apo.PLANPROCESSRALL*apoom.SINGLEROLLWEIGHT),2),0) AS 'actualordernumyear' -- 实际接单量
        from
        aps_process_operation_process_equ apope
            join aps_process_operation apo on apo.ID = apope.PROCESSID
            join aps_process_operation_out_mater apoom on apo.id=apoom.MAINID
            join aps_blank_order abo on apo.BLANKID = abo.id
            join aps_production_order apro on apro.ID = abo.PRODUCTIONORDERID
            left join aps_product_detail apd on apoom.ORDERDETAILID = apd.id and apd.DELETED='0'
            left join aps_product_specs aps on aps.TENANTID=apro.TENANTID and aps.DELETED='0'
            left join aps_production_merge_order apmd on apmd.ORDERNO = apd.MERGEORDERNUMBER and apmd.DELETED='0'
        where apope.DELETED='0' and apo.DELETED='0' and abo.DELETED='0' and apro.DELETED='0' and apoom.DELETED='0'
                  and apoom.THICKNESS > IFNULL(aps.THICKSTR,0) and apoom.THICKNESS &lt;= IFNULL(aps.THICKEND,99999)
                  and IFNULL(apmd.ORDERTYPE, apro.ORDERTYPE)='客户订单'
                  and abo.PRODUCTSTATUS in ('30','40','50')
                  and (apope.PLANPROCESSRALL-apope.CANCELROLL)>0
                  and apoom.PLANPRODROLLNUM>0
        and YEAR(apope.PLANENDDATE) = YEAR(CURRENT_DATE)
        GROUP BY apro.TENANTID
    </select>
    <select id="selectNowYearActualProduceNum" resultType="com.rongwei.bsentity.domain.ApsReportYearAndMonth">
        select
            apro.TENANTID AS 'tenantid',
            IFNULL(ROUND(sum(aro.WEIGHT),2),0) AS 'actualfinishnumyear'
        from
            aps_process_operation_process_equ apope
                join aps_process_operation apo on apo.ID = apope.PROCESSID
                join aps_blank_order abo on apo.BLANKID = abo.id
                join aps_production_order apro on apro.ID = abo.PRODUCTIONORDERID
                join aps_report_records arr on apope.id=arr.PROCESSEQUID
                join aps_report_output aro on arr.id=aro.mainid
                join aps_process_operation_out_mater apoom on aro.OUTPUTID=apoom.ID-- 作业输出物料
                left join aps_product_detail apd on apoom.ORDERDETAILID = apd.id and apd.DELETED = '0' -- 订单产品明细
                left join aps_production_merge_order apmd on apmd.ORDERNO = apd.MERGEORDERNUMBER and apmd.DELETED='0'
        where
            apope.deleted='0' and apo.DELETED='0' and abo.DELETED='0' and apro.deleted='0'
          and arr.deleted='0' and aro.DELETED='0' and apoom.DELETED = '0'
          and ifnull(apmd.ORDERTYPE, apro.ORDERTYPE) = '客户订单' -- 订单类型为空默认为客户订单
          and arr.CHECKSTATUS='已检验'
          and aro.QUALIFIED='是'
          and apoom.PLANPRODROLLNUM>0
          and YEAR(arr.REPORTWORKTIME) = YEAR(CURRENT_DATE)
        GROUP BY apro.TENANTID
    </select>
    <select id="selectNowMonthPlanGetOrderNum" resultType="com.rongwei.bsentity.domain.ApsReportYearAndMonth">
        select
            aop.TENANTID AS 'tenantid',
            ifnull(ROUND(sum(
                           CASE MONTH(CURRENT_DATE)
                               WHEN '1' THEN ONEM
                               WHEN '2' THEN TWOM
                               WHEN '3' THEN THREEM
                               WHEN '4' THEN FOURM
                               WHEN '5' THEN FIVEM
                               WHEN '6' THEN SIXM
                               WHEN '7' THEN SEVENM
                               WHEN '8' THEN EIGHTM
                               WHEN '9' THEN NINEM
                               WHEN '10' THEN TENM
                               WHEN '11' THEN ELEVENM
                               WHEN '12' THEN TWELVEM
                               ELSE null
                               END
                   ),2),0) AS 'planordernummonth'
        from
            aps_accept_order_plan aop
                join aps_accept_order_plan_detail aopd on aop.id=aopd.MAINID
        where
            aop.deleted='0' and aopd.deleted='0'
          and aop.PARTYEAR=YEAR(CURRENT_DATE)
        GROUP BY aop.TENANTID
    </select>
    <select id="selectNowMonthActualGetOrderNum"
            resultType="com.rongwei.bsentity.domain.ApsReportYearAndMonth">
        select
        apro.TENANTID AS 'tenantid',
        ifnull(ROUND(sum((apope.PLANPROCESSRALL-apope.CANCELROLL)*apoom.PLANPRODROLLNUM/apo.PLANPROCESSRALL*apoom.SINGLEROLLWEIGHT),2),0) AS 'actualordernummonth' -- 本月实际接单量
        from
        aps_process_operation_process_equ apope
        join aps_process_operation apo on apo.ID = apope.PROCESSID
        join aps_process_operation_out_mater apoom on apo.id=apoom.MAINID
        join aps_blank_order abo on apo.BLANKID = abo.id
        join aps_production_order apro on apro.ID = abo.PRODUCTIONORDERID
        left join aps_product_detail apd on apoom.ORDERDETAILID = apd.id and apd.DELETED='0'
        left join aps_product_specs aps on aps.TENANTID=apro.TENANTID and aps.DELETED='0'
        left join aps_production_merge_order apmd on apmd.ORDERNO = apd.MERGEORDERNUMBER and apmd.DELETED='0'
        where apope.DELETED='0' and apo.DELETED='0' and abo.DELETED='0' and apro.DELETED='0' and apoom.DELETED='0'
        and apoom.THICKNESS > IFNULL(aps.THICKSTR,0) and apoom.THICKNESS &lt;= IFNULL(aps.THICKEND,99999)
        and IFNULL(apmd.ORDERTYPE, apro.ORDERTYPE)='客户订单'
        and abo.PRODUCTSTATUS in ('30','40','50')
        and (apope.PLANPROCESSRALL-apope.CANCELROLL)>0
        and apoom.PLANPRODROLLNUM>0
        and YEAR(apope.PLANENDDATE) = YEAR(CURRENT_DATE)
        and MONTH(apope.PLANENDDATE) = MONTH(CURRENT_DATE)
        GROUP BY apro.TENANTID
    </select>
    <select id="selectNowMonthActualProduceNum" resultType="com.rongwei.bsentity.domain.ApsReportYearAndMonth">
        select
            apro.TENANTID AS 'tenantid',
            ifnull(ROUND(sum(aro.WEIGHT),2),0) AS 'actualfinishnummonth'
        from
            aps_process_operation_process_equ apope
                join aps_process_operation apo on apo.ID = apope.PROCESSID
                join aps_blank_order abo on apo.BLANKID = abo.id
                join aps_production_order apro on apro.ID = abo.PRODUCTIONORDERID
                join aps_report_records arr on apope.id=arr.PROCESSEQUID
                join aps_report_output aro on arr.id=aro.mainid
                join aps_process_operation_out_mater apoom on aro.OUTPUTID=apoom.ID-- 作业输出物料
                left join aps_product_detail apd on apoom.ORDERDETAILID = apd.id and apd.DELETED = '0' -- 订单产品明细
                left join aps_production_merge_order apmd on apmd.ORDERNO = apd.MERGEORDERNUMBER and apmd.DELETED='0'
        where
            apope.deleted='0' and apo.DELETED='0' and abo.DELETED='0' and apro.deleted='0'
          and arr.deleted='0' and aro.DELETED='0' and apoom.DELETED = '0'
          and ifnull(apmd.ORDERTYPE, apro.ORDERTYPE) = '客户订单' -- 订单类型为空默认为客户订单
          and arr.CHECKSTATUS='已检验'
          and aro.QUALIFIED='是'
          and apoom.PLANPRODROLLNUM>0
          and YEAR(arr.REPORTWORKTIME) = YEAR(CURRENT_DATE)
          and MONTH(arr.REPORTWORKTIME) = MONTH(CURRENT_DATE)
        GROUP BY apro.TENANTID
    </select>
    <select id="selectOrderTable" resultType="com.rongwei.bsentity.domain.ApsReportOrderFinishTable">
        with plan as (
            -- 本周计划完成订单量
            select
                apope.PROCESSWORKSHOPID,
                max(apope.PROCESSWORKSHOP) PROCESSWORKSHOP,
                sum((apope.PLANPROCESSRALL-apope.CANCELROLL)*apoom.PLANPRODROLLNUM/apo.PLANPROCESSRALL*apoom.SINGLEROLLWEIGHT) PLANPRODUCEVOLUME -- 本周计划完成订单量
            from
                aps_process_operation_process_equ apope
                    join aps_process_operation apo on apo.ID = apope.PROCESSID
                    join aps_process_operation_out_mater apoom on apo.id=apoom.MAINID-- 作业输出物料
                    join aps_blank_order abo on apo.BLANKID = abo.id
                    join aps_production_order apro on apro.ID = abo.PRODUCTIONORDERID
                    left join aps_product_detail apd on apoom.ORDERDETAILID = apd.id and apd.DELETED='0' -- 订单产品明细
                    left join aps_production_merge_order apmd on apmd.ORDERNO = apd.MERGEORDERNUMBER and apmd.DELETED='0'
            where
                apope.deleted='0' and apo.DELETED='0' and abo.DELETED='0' and apro.deleted='0'
              and apoom.DELETED='0'
              and ifnull(apmd.ORDERTYPE, apro.ORDERTYPE) = '客户订单' -- 订单类型为空默认为客户订单
              and abo.PRODUCTSTATUS in ('30','40','50')
              and (apope.PLANPROCESSRALL-apope.CANCELROLL)>0
              and apoom.PLANPRODROLLNUM>0
              and YEARWEEK(apope.PLANENDDATE) = YEARWEEK(CURRENT_DATE)
              and apro.TENANTID=#{tenantId}
            group by
                apope.PROCESSWORKSHOPID
        ),
             actual as (
                 -- 本周和昨日实际已完成订单
                 select
                     apope.PROCESSWORKSHOPID,
                     max(apope.PROCESSWORKSHOP) PROCESSWORKSHOP,
                     sum(if(YEARWEEK(arr.REPORTWORKTIME) = YEARWEEK(CURRENT_DATE), -- 本周
                            aro.WEIGHT,null)) ACTUALPRODUCEVOLUME, -- 本周实际完成订单量
                     sum(if(DATE(arr.REPORTWORKTIME) = DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY), -- 昨日
                            aro.WEIGHT,null)) YACTUALPRODUCEVOLUME -- 昨日实际完成订单量
                 from
                     aps_process_operation_process_equ apope
                         join aps_process_operation apo on apo.ID = apope.PROCESSID
                         join aps_blank_order abo on apo.BLANKID = abo.id
                         join aps_production_order apro on apro.ID = abo.PRODUCTIONORDERID
                         join aps_report_records arr on apope.id=arr.PROCESSEQUID
                         join aps_report_output aro on arr.id=aro.mainid
                         join aps_process_operation_out_mater apoom on aro.OUTPUTID=apoom.ID-- 作业输出物料
                         left join aps_product_detail apd on apoom.ORDERDETAILID = apd.id and apd.DELETED = '0' -- 订单产品明细
                         left join aps_production_merge_order apmd on apmd.ORDERNO = apd.MERGEORDERNUMBER and apmd.DELETED='0'
                 where
                     apope.deleted='0' and apo.DELETED='0' and abo.DELETED='0' and apro.deleted='0'
                   and arr.deleted='0' and aro.DELETED='0' and apoom.DELETED = '0'
                   and ifnull(apmd.ORDERTYPE, apro.ORDERTYPE) = '客户订单' -- 订单类型为空默认为客户订单
                   and arr.CHECKSTATUS='已检验'
                   and aro.QUALIFIED='是'
                   and apoom.PLANPRODROLLNUM>0
                   and (YEARWEEK(arr.REPORTWORKTIME) = YEARWEEK(CURRENT_DATE)
                     or DATE(arr.REPORTWORKTIME) = DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY))
            and apro.TENANTID=#{tenantId}
        group by
            apope.PROCESSWORKSHOPID
            )
        select
            plan.PROCESSWORKSHOPID
             ,plan.PROCESSWORKSHOP
             ,IFNULL(ROUND(plan.PLANPRODUCEVOLUME,2),0) AS 'PLANPRODUCEVOLUME' -- 本周计划完成（吨）
        ,IFNULL(ROUND(actual.ACTUALPRODUCEVOLUME,2),0) AS 'ACTUALPRODUCEVOLUME' -- 本周实际已完成（吨）
        ,IFNULL(ROUND(actual.YACTUALPRODUCEVOLUME,2),0) AS 'YACTUALPRODUCEVOLUME' -- 昨日实际已完成（吨）
        ,concat(if(plan.PLANPRODUCEVOLUME is null or plan.PLANPRODUCEVOLUME = 0,
                   100, ROUND(actual.ACTUALPRODUCEVOLUME/plan.PLANPRODUCEVOLUME*100,2)),'%') COMPLETIONRATIO -- 本周实际已完成比例
        from
            plan left join actual on plan.PROCESSWORKSHOPID=actual.PROCESSWORKSHOPID
        union -- mysql 该版本不支持full join，通过left join 和right join配合
        select
            actual.PROCESSWORKSHOPID
             ,actual.PROCESSWORKSHOP
             ,IFNULL(ROUND(plan.PLANPRODUCEVOLUME,2),0) AS 'PLANPRODUCEVOLUME' -- 本周计划完成（吨）
        ,IFNULL(ROUND(actual.ACTUALPRODUCEVOLUME,2),0) AS 'ACTUALPRODUCEVOLUME' -- 本周实际已完成（吨）
        ,IFNULL(ROUND(actual.YACTUALPRODUCEVOLUME,2),0) AS 'YACTUALPRODUCEVOLUME' -- 昨日实际已完成（吨）
        ,concat(if(plan.PLANPRODUCEVOLUME is null or plan.PLANPRODUCEVOLUME = 0,
                   100, ROUND(actual.ACTUALPRODUCEVOLUME/plan.PLANPRODUCEVOLUME*100,2)),'%') COMPLETIONRATIO -- 本周实际已完成比例
        from
            plan right join actual on plan.PROCESSWORKSHOPID=actual.PROCESSWORKSHOPID
    </select>
    <select id="selectOrderFinishPhotoAvg" resultType="com.rongwei.bsentity.domain.ApsReportOrderFinishPhoto">
        with months as (
        -- 12个月
        SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11
        ),
        plan as (
        -- 近12个月计划完成订单量
        select
        DATE_FORMAT(apope.PLANENDDATE, '%Y-%m') month_year,
        sum((apope.PLANPROCESSRALL-apope.CANCELROLL)*apoom.PLANPRODROLLNUM/apo.PLANPROCESSRALL*apoom.SINGLEROLLWEIGHT) PLANPRODUCEVOLUME -- 当月计划完成订单量
        from
        aps_process_operation_process_equ apope
            join aps_process_operation apo on apo.ID = apope.PROCESSID
            join aps_process_operation_out_mater apoom on apo.id=apoom.MAINID-- 作业输出物料
            join aps_blank_order abo on apo.BLANKID = abo.id
            join aps_production_order apro on apro.ID = abo.PRODUCTIONORDERID
            join aps_product_detail apd on apoom.ORDERDETAILID = apd.id and apd.DELETED='0' -- 订单产品明细
            left join aps_production_merge_order apmd on apmd.ORDERNO = apd.MERGEORDERNUMBER and apmd.DELETED='0'
        where
            apope.deleted='0' and apo.DELETED='0' and abo.DELETED='0' and apro.deleted='0'
          and apoom.DELETED='0'
        and ifnull(apmd.ORDERTYPE, apro.ORDERTYPE) = '客户订单' -- 订单类型为空默认为客户订单
        and abo.PRODUCTSTATUS in ('30','40','50')
        and (apope.PLANPROCESSRALL-apope.CANCELROLL)>0
        and apoom.PLANPRODROLLNUM>0
        and apope.PLANENDDATE between DATE_FORMAT(DATE_SUB(CURRENT_DATE, INTERVAL 11 MONTH), '%Y-%m-01') and DATE_FORMAT(LAST_DAY(CURRENT_DATE()),'%Y-%m-%d 23:59:59') -- 近12个月计划
        and apro.TENANTID=#{tenantId}
        group by
        month_year
        ),
        actual as (
        -- 近12个月实际已完成订单
        select
        DATE_FORMAT(arr.REPORTWORKTIME, '%Y-%m') month_year,
        sum(aro.WEIGHT) ACTUALPRODUCEVOLUME -- 当月实际完成订单量
        from
        aps_process_operation_process_equ apope
            join aps_process_operation apo on apo.ID = apope.PROCESSID
            join aps_blank_order abo on apo.BLANKID = abo.id
            join aps_production_order apro on apro.ID = abo.PRODUCTIONORDERID
            join aps_report_records arr on apope.id=arr.PROCESSEQUID
            join aps_report_output aro on arr.id=aro.mainid
            join aps_process_operation_out_mater apoom on aro.OUTPUTID=apoom.ID-- 作业输出物料
            left join aps_product_detail apd on apoom.ORDERDETAILID = apd.id and apd.DELETED = '0' -- 订单产品明细
            left join aps_production_merge_order apmd on apmd.ORDERNO = apd.MERGEORDERNUMBER and apmd.DELETED='0'
        where
            apope.deleted='0' and apo.DELETED='0' and abo.DELETED='0' and apro.deleted='0'
          and arr.deleted='0' and aro.DELETED='0' and apoom.DELETED = '0'
        and ifnull(apmd.ORDERTYPE, apro.ORDERTYPE) = '客户订单' -- 订单类型为空默认为客户订单
        and arr.CHECKSTATUS='已检验'
        and aro.QUALIFIED='是'
        and apoom.PLANPRODROLLNUM>0
        and arr.REPORTWORKTIME between DATE_FORMAT(DATE_SUB(CURRENT_DATE, INTERVAL 11 MONTH), '%Y-%m-01') and DATE_FORMAT(LAST_DAY(CURRENT_DATE()),'%Y-%m-%d 23:59:59') -- 近12个月计划
        and apro.TENANTID=#{tenantId}
        group by
        month_year
        )
        -- 计划完成订单量月平均值，实际已完成订单量月平均值
        select
        IFNULL(ROUND(AVG(plan.PLANPRODUCEVOLUME),2),0) AVGPLANPRODUCEVOLUME, -- 计划完成订单量月平均
        IFNULL(ROUND(AVG(actual.ACTUALPRODUCEVOLUME),2),0) AVGACTUALPRODUCEVOLUME -- 实际已完成订单量月平均
        from
        months
        LEFT JOIN plan ON plan.month_year = DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL months.n MONTH), '%Y-%m')
        LEFT JOIN actual ON actual.month_year = DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL months.n MONTH), '%Y-%m') limit 1
    </select>
    <select id="selectOrderFinishPhotoByMonth"
            resultType="com.rongwei.bsentity.domain.ApsReportOrderFinishPhoto">
        with months as (
        -- 12个月
        SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11
        ),
        plan as (
        -- 近12个月计划完成订单量
        select
        DATE_FORMAT(apope.PLANENDDATE, '%Y-%m') month_year,
        sum((apope.PLANPROCESSRALL-apope.CANCELROLL)*apoom.PLANPRODROLLNUM/apo.PLANPROCESSRALL*apoom.SINGLEROLLWEIGHT) PLANPRODUCEVOLUME -- 当月计划完成订单量
        from
        aps_process_operation_process_equ apope
            join aps_process_operation apo on apo.ID = apope.PROCESSID
            join aps_process_operation_out_mater apoom on apo.id=apoom.MAINID-- 作业输出物料
            join aps_blank_order abo on apo.BLANKID = abo.id
            join aps_production_order apro on apro.ID = abo.PRODUCTIONORDERID
            join aps_product_detail apd on apoom.ORDERDETAILID = apd.id and apd.DELETED='0' -- 订单产品明细
            left join aps_production_merge_order apmd on apmd.ORDERNO = apd.MERGEORDERNUMBER and apmd.DELETED='0'
        where
            apope.deleted='0' and apo.DELETED='0' and abo.DELETED='0' and apro.deleted='0'
          and apoom.DELETED='0'
        and ifnull(apmd.ORDERTYPE, apro.ORDERTYPE) = '客户订单' -- 订单类型为空默认为客户订单
        and abo.PRODUCTSTATUS in ('30','40','50')
        and (apope.PLANPROCESSRALL-apope.CANCELROLL)>0
        and apoom.PLANPRODROLLNUM>0
        and apope.PLANENDDATE between DATE_FORMAT(DATE_SUB(CURRENT_DATE, INTERVAL 11 MONTH), '%Y-%m-01') and DATE_FORMAT(LAST_DAY(CURRENT_DATE()),'%Y-%m-%d 23:59:59') -- 近12个月计划
        and apro.TENANTID=#{tenantId}
        group by
        month_year
        ),
        actual as (
        -- 近12个月实际已完成订单
        select
        DATE_FORMAT(arr.REPORTWORKTIME, '%Y-%m') month_year,
        sum(aro.WEIGHT) ACTUALPRODUCEVOLUME -- 当月实际完成订单量
        from
        aps_process_operation_process_equ apope
            join aps_process_operation apo on apo.ID = apope.PROCESSID
            join aps_blank_order abo on apo.BLANKID = abo.id
            join aps_production_order apro on apro.ID = abo.PRODUCTIONORDERID
            join aps_report_records arr on apope.id=arr.PROCESSEQUID
            join aps_report_output aro on arr.id=aro.mainid
            join aps_process_operation_out_mater apoom on aro.OUTPUTID=apoom.ID-- 作业输出物料
            left join aps_product_detail apd on apoom.ORDERDETAILID = apd.id and apd.DELETED = '0' -- 订单产品明细
            left join aps_production_merge_order apmd on apmd.ORDERNO = apd.MERGEORDERNUMBER and apmd.DELETED='0'
        where
            apope.deleted='0' and apo.DELETED='0' and abo.DELETED='0' and apro.deleted='0'
          and arr.deleted='0' and aro.DELETED='0' and apoom.DELETED = '0'
        and ifnull(apmd.ORDERTYPE, apro.ORDERTYPE) = '客户订单' -- 订单类型为空默认为客户订单
        and arr.CHECKSTATUS='已检验'
        and aro.QUALIFIED='是'
        and apoom.PLANPRODROLLNUM>0
        and arr.REPORTWORKTIME between DATE_FORMAT(DATE_SUB(CURRENT_DATE, INTERVAL 11 MONTH), '%Y-%m-01') and DATE_FORMAT(LAST_DAY(CURRENT_DATE()),'%Y-%m-%d 23:59:59') -- 近12个月计划
        and apro.TENANTID=#{tenantId}
        group by
        month_year
        )
        select
            DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL months.n MONTH), '%Y-%m') yearmonth,
            IFNULL(ROUND(plan.PLANPRODUCEVOLUME,2),0) AS 'PLANPRODUCEVOLUME', -- 计划完成订单量
            IFNULL(ROUND(actual.ACTUALPRODUCEVOLUME,2),0) AS 'ACTUALPRODUCEVOLUME' -- 实际已完成订单量
        from
            months
                LEFT JOIN plan ON plan.month_year = DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL months.n MONTH), '%Y-%m')
                LEFT JOIN actual ON actual.month_year = DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL months.n MONTH), '%Y-%m')
        ORDER BY
            yearmonth
    </select>
    <select id="selectCastRollData" resultType="com.rongwei.bsentity.domain.ApsReportCastRollTable">
        with plan as (
            -- 按照车间分组统计本周铸轧工序类别计划加工量
            select
                apo.TENANTID,
                apope.PROCESSWORKSHOPID,
            DATE(apope.PLANENDDATE) PROCESSDAY,
            max(apope.PROCESSWORKSHOP) PROCESSWORKSHOP,
            sum(if(YEARWEEK(apope.PLANENDDATE,1)=YEARWEEK(CURRENT_DATE,1), -- 本周
           (apope.PLANPROCESSRALL-apope.CANCELROLL)*apo.SINGLEROLLWEIGHT,null)) PLANPRODUCEVOLUME, -- 本周计划加工量
            sum(if(DATE(apope.PLANENDDATE) = DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY), -- 昨日
           (apope.PLANPROCESSRALL-apope.CANCELROLL)*apo.SINGLEROLLWEIGHT,null)) YPLANPRODUCEVOLUME -- 昨日计划加工量
        from
            aps_process_operation_process_equ apope
            join aps_process_operation apo on apo.ID = apope.PROCESSID
            join aps_production_processes app on app.PRODUCTPROCESSNAME=apo.PROCESS
        where
            apope.deleted='0' and apo.DELETED='0'
          and app.PROCESSCATEGORY='10' -- 铸轧
          and apo.PROCESSSTATUS in ('30','40','50') -- 已发布
          and (apope.PLANPROCESSRALL-apope.CANCELROLL)>0 -- 计划加工卷数- 已取消卷数合计
          and (YEARWEEK(apope.PLANENDDATE,1)=YEARWEEK(CURRENT_DATE,1) -- 本周
           or DATE(apope.PLANENDDATE) = DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY)) -- 昨日
          and apo.TENANTID=#{tenantId}
        group by
            apope.PROCESSWORKSHOPID,PROCESSDAY
            ),
            actual as (
        -- 按照车间分组统计本周铸轧工序类别实际加工量
        select
            apo.TENANTID,
            apope.PROCESSWORKSHOPID,
            DATE(arr.REPORTWORKTIME) PROCESSDAY,
            max(apope.PROCESSWORKSHOP) PROCESSWORKSHOP,
            sum(if(YEARWEEK(arr.REPORTWORKTIME,1)=YEARWEEK(CURRENT_DATE,1), -- 本周
            apo.SINGLEROLLWEIGHT,null)) ACTUALPRODUCEVOLUME, -- 本周实际已加工量
            sum(if(DATE(arr.REPORTWORKTIME) = DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY), -- 昨日
            apo.SINGLEROLLWEIGHT,null)) YACTUALPRODUCEVOLUME -- 昨日实际已加工量
        from
            aps_report_records arr
            join aps_process_operation_process_equ apope  on apope.id=arr.PROCESSEQUID
            join aps_process_operation apo on apo.ID = apope.PROCESSID
            join aps_production_processes app on app.PRODUCTPROCESSNAME=apo.PROCESS
        where
            apope.deleted='0' and apo.DELETED='0' and arr.deleted='0'
          and app.PROCESSCATEGORY='10' -- 铸轧
          and arr.REPORTWORKSTATUS='已报工'
          and (YEARWEEK(arr.REPORTWORKTIME,1)=YEARWEEK(CURRENT_DATE,1) -- 本周
           or DATE(arr.REPORTWORKTIME) = DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY)) -- 昨日
          and apo.TENANTID=#{tenantId}
        group by
            apope.PROCESSWORKSHOPID,PROCESSDAY
            ),
            total as (
        select plan.TENANTID,
            plan.PROCESSWORKSHOPID,
            plan.PROCESSDAY,
            plan.PROCESSWORKSHOP,
            plan.PLANPRODUCEVOLUME, -- 本周计划加工量
            plan.YPLANPRODUCEVOLUME, -- 昨日计划加工量
            actual.ACTUALPRODUCEVOLUME,
            actual.YACTUALPRODUCEVOLUME
        from plan left join actual on plan.PROCESSWORKSHOPID=actual.PROCESSWORKSHOPID and plan.PROCESSDAY = actual.PROCESSDAY
        union
        select actual.TENANTID,
            actual.PROCESSWORKSHOPID,
            actual.PROCESSDAY,
            actual.PROCESSWORKSHOP,
            plan.PLANPRODUCEVOLUME, -- 本周计划加工量
            plan.YPLANPRODUCEVOLUME, -- 昨日计划加工量
            actual.ACTUALPRODUCEVOLUME,
            actual.YACTUALPRODUCEVOLUME
        from plan right join actual on plan.PROCESSWORKSHOPID=actual.PROCESSWORKSHOPID and plan.PROCESSDAY = actual.PROCESSDAY
            )

        select
            total.PROCESSWORKSHOPID
             ,total.PROCESSWORKSHOP
             ,IFNULL(ROUND(sum(if(YEARWEEK(total.PROCESSDAY,1)=YEARWEEK(CURRENT_DATE,1),total.PLANPRODUCEVOLUME,null)),2),0) AS 'WEEKPLANPRODUCEVOLUME' -- 本周计划加工（吨）
             ,IFNULL(ROUND(sum(if(DATE(total.PROCESSDAY) = DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY),total.YPLANPRODUCEVOLUME,null)),2),0) AS 'YPLANPRODUCEVOLUME' -- 昨日计划加工（吨）
             ,IFNULL(ROUND(sum(if(YEARWEEK(total.PROCESSDAY,1)=YEARWEEK(CURRENT_DATE,1),total.ACTUALPRODUCEVOLUME,null)),2),0) AS 'WEEKACTUALPRODUCEVOLUME' -- 本周实际已加工（吨）
             ,IFNULL(ROUND(sum(if(DATE(total.PROCESSDAY) = DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY),total.YACTUALPRODUCEVOLUME,null)),2),0) AS 'YACTUALPRODUCEVOLUME' -- 昨日实际已加工（吨）
        --              ,ROUND(if(plan.PLANPRODUCEVOLUME is null or plan.PLANPRODUCEVOLUME = 0,
--                  1, actual.ACTUALPRODUCEVOLUME/plan.PLANPRODUCEVOLUME),2) COMPLETIONRATIO -- 本周实际已完成比例
            ,IFNULL(ROUND(AVG(total.ACTUALPRODUCEVOLUME),2),0) COMPLETIONRATIO -- 本周日平均
        from
            total  group by total.PROCESSWORKSHOPID
    </select>
    <select id="selectCastRollPhotoAvg" resultType="com.rongwei.bsentity.domain.ApsReportCastRollPhoto">
        with months as (
            -- 12个月
            SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11
        ),
             plan as (
                 -- 近12个月铸轧计划加工量
                 select
                     DATE_FORMAT(apope.PLANENDDATE, '%Y-%m') month_year,
                     sum((apope.PLANPROCESSRALL-apope.CANCELROLL)*apo.SINGLEROLLWEIGHT) PLANPRODUCEVOLUME -- 当月计划加工量
                 from
                     aps_process_operation_process_equ apope
                         join aps_process_operation apo on apo.ID = apope.PROCESSID
                         join aps_production_processes app on app.PRODUCTPROCESSNAME=apo.PROCESS
                 where
                     apope.deleted='0' and apo.DELETED='0'
                   and app.PROCESSCATEGORY='10' -- 铸轧
                   and apo.PROCESSSTATUS in ('30','40','50') -- 已发布
                   and (apope.PLANPROCESSRALL-apope.CANCELROLL)>0 -- 计划加工卷数- 已取消卷数合计
                   and apope.PLANENDDATE between DATE_FORMAT(DATE_SUB(CURRENT_DATE, INTERVAL 11 MONTH), '%Y-%m-01') and DATE_FORMAT(LAST_DAY(CURRENT_DATE()),'%Y-%m-%d 23:59:59') -- 近12个月计划
                   and apo.TENANTID=#{tenantId}
                 group by
                     month_year
             ),
             actual as (
                 -- 近12个月铸轧实际加工量
                 select
                     DATE_FORMAT(arr.REPORTWORKTIME, '%Y-%m') month_year,
                     sum(apo.SINGLEROLLWEIGHT) ACTUALPRODUCEVOLUME -- 当月实际已加工量
                 from
                     aps_report_records arr
                         join aps_process_operation_process_equ apope  on apope.id=arr.PROCESSEQUID
                         join aps_process_operation apo on apo.ID = apope.PROCESSID
                         join aps_production_processes app on app.PRODUCTPROCESSNAME=apo.PROCESS
                 where
                     apope.deleted='0' and apo.DELETED='0' and arr.deleted='0'
                   and app.PROCESSCATEGORY='10' -- 铸轧
                   and arr.REPORTWORKSTATUS='已报工'
                   and arr.REPORTWORKTIME between DATE_FORMAT(DATE_SUB(CURRENT_DATE, INTERVAL 11 MONTH), '%Y-%m-01') and DATE_FORMAT(LAST_DAY(CURRENT_DATE()),'%Y-%m-%d 23:59:59') -- 近12个月计划
                   and apo.TENANTID=#{tenantId}
                 group by
                     month_year
             )
        -- 计划加工量月平均值，实际已加工量月平均值
        select
            IFNULL(ROUND(AVG(plan.PLANPRODUCEVOLUME),2),0) AVGPLANPRODUCEVOLUME, -- 计划加工量月平均
            IFNULL(ROUND(AVG(actual.ACTUALPRODUCEVOLUME),2),0) AVGACTUALPRODUCEVOLUME -- 实际已加工量月平均
        from
            months
                LEFT JOIN plan ON plan.month_year = DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL months.n MONTH), '%Y-%m')
                LEFT JOIN actual ON actual.month_year = DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL months.n MONTH), '%Y-%m') limit 1
    </select>
    <select id="selectCastRollPhoto" resultType="com.rongwei.bsentity.domain.ApsReportCastRollPhoto">
        with months as (
            -- 12个月
            SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11
        ),
             plan as (
                 -- 近12个月铸轧计划加工量
                 select
                     DATE_FORMAT(apope.PLANENDDATE, '%Y-%m') month_year,
                     sum((apope.PLANPROCESSRALL-apope.CANCELROLL)*apo.SINGLEROLLWEIGHT) PLANPRODUCEVOLUME -- 当月计划加工量
                 from
                     aps_process_operation_process_equ apope
                         join aps_process_operation apo on apo.ID = apope.PROCESSID
                         join aps_production_processes app on app.PRODUCTPROCESSNAME=apo.PROCESS
                 where
                     apope.deleted='0' and apo.DELETED='0'
                   and app.PROCESSCATEGORY='10' -- 铸轧
                   and apo.PROCESSSTATUS in ('30','40','50') -- 已发布
                   and (apope.PLANPROCESSRALL-apope.CANCELROLL)>0 -- 计划加工卷数- 已取消卷数合计
                   and apope.PLANENDDATE between DATE_FORMAT(DATE_SUB(CURRENT_DATE, INTERVAL 11 MONTH), '%Y-%m-01') and DATE_FORMAT(LAST_DAY(CURRENT_DATE()),'%Y-%m-%d 23:59:59') -- 近12个月计划
                   and apo.TENANTID=#{tenantId}
                 group by
                     month_year
             ),
             actual as (
                 -- 近12个月铸轧实际加工量
                 select
                     DATE_FORMAT(arr.REPORTWORKTIME, '%Y-%m') month_year,
                     sum(apo.SINGLEROLLWEIGHT) ACTUALPRODUCEVOLUME -- 当月实际已加工量
                 from
                     aps_report_records arr
                         join aps_process_operation_process_equ apope  on apope.id=arr.PROCESSEQUID
                         join aps_process_operation apo on apo.ID = apope.PROCESSID
                         join aps_production_processes app on app.PRODUCTPROCESSNAME=apo.PROCESS
                 where
                     apope.deleted='0' and apo.DELETED='0' and arr.deleted='0'
                   and app.PROCESSCATEGORY='10' -- 铸轧
                   and arr.REPORTWORKSTATUS='已报工'
                   and arr.REPORTWORKTIME between DATE_FORMAT(DATE_SUB(CURRENT_DATE, INTERVAL 11 MONTH), '%Y-%m-01') and DATE_FORMAT(LAST_DAY(CURRENT_DATE()),'%Y-%m-%d 23:59:59') -- 近12个月计划
                   and apo.TENANTID=#{tenantId}
                 group by
                     month_year
             )
        select
            DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL months.n MONTH), '%Y-%m') yearmonth,
            IFNULL(ROUND(plan.PLANPRODUCEVOLUME,2),0) AS 'PLANPRODUCEVOLUME', -- 计划加工量
            IFNULL(ROUND(actual.ACTUALPRODUCEVOLUME,2),0) AS 'ACTUALPRODUCEVOLUME' -- 实际已加工量
        from
            months
                LEFT JOIN plan ON plan.month_year = DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL months.n MONTH), '%Y-%m')
                LEFT JOIN actual ON actual.month_year = DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL months.n MONTH), '%Y-%m')
        ORDER BY
            yearmonth
    </select>
    <select id="selectProcessTable" resultType="com.rongwei.bsentity.domain.ApsReportJzProcessTable">
        with plan as (
            -- 按照车间分组统计本周精整工序类别计划加工量
            select
                apo.TENANTID,
                apope.PROCESSWORKSHOPID,
            DATE(apope.PLANENDDATE) PROCESSDAY,
            max(apope.PROCESSWORKSHOP) PROCESSWORKSHOP,
            sum(if(YEARWEEK(apope.PLANENDDATE,1)=YEARWEEK(CURRENT_DATE,1), -- 本周
           (apope.PLANPROCESSRALL-apope.CANCELROLL)*apo.SINGLEROLLWEIGHT,null)) PLANPRODUCEVOLUME, -- 本周计划加工量
            sum(if(DATE(apope.PLANENDDATE) = DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY), -- 昨日
           (apope.PLANPROCESSRALL-apope.CANCELROLL)*apo.SINGLEROLLWEIGHT,null)) YPLANPRODUCEVOLUME -- 昨日计划加工量
        from
            aps_process_operation_process_equ apope
            join aps_process_operation apo on apo.ID = apope.PROCESSID
            join aps_production_processes app on app.PRODUCTPROCESSNAME=apo.PROCESS
        where
            apope.deleted='0' and apo.DELETED='0'
          and app.PROCESSCATEGORY='40' -- 精整
          and apo.PROCESSSTATUS in ('30','40','50') -- 已发布
          and (apope.PLANPROCESSRALL-apope.CANCELROLL)>0 -- 计划加工卷数- 已取消卷数合计
          and (YEARWEEK(apope.PLANENDDATE,1)=YEARWEEK(CURRENT_DATE,1) -- 本周
           or DATE(apope.PLANENDDATE) = DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY)) -- 昨日
          and apo.TENANTID=#{tenantId}
        group by
            apope.PROCESSWORKSHOPID,PROCESSDAY
            ),
            actual as (
        -- 按照车间分组统计本周精整工序类别实际加工量
        select
            apo.TENANTID,
            apope.PROCESSWORKSHOPID,
            DATE(arr.REPORTWORKTIME) PROCESSDAY,
            max(apope.PROCESSWORKSHOP) PROCESSWORKSHOP,
            sum(if(YEARWEEK(arr.REPORTWORKTIME,1)=YEARWEEK(CURRENT_DATE,1), -- 本周
            (LENGTH(trim( BOTH ',' FROM arr.BATCHNUMBER))-LENGTH(REPLACE(trim( BOTH ',' FROM arr.BATCHNUMBER), ',', '')) + 1) * IFNULL(arr.ACTUALROLLWEIGHT,apo.SINGLEROLLWEIGHT),null)) ACTUALPRODUCEVOLUME, -- 本周实际已加工量=报工批次号数量*实际输入单卷重
            sum(if(DATE(arr.REPORTWORKTIME) = DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY), -- 昨日
            (LENGTH(trim( BOTH ',' FROM arr.BATCHNUMBER))-LENGTH(REPLACE(trim( BOTH ',' FROM arr.BATCHNUMBER), ',', '')) + 1) * IFNULL(arr.ACTUALROLLWEIGHT,apo.SINGLEROLLWEIGHT),null)) YACTUALPRODUCEVOLUME -- 昨日实际已加工量=报工批次号数量*实际输入单卷重
        from
            aps_report_records arr
            join aps_process_operation_process_equ apope  on apope.id=arr.PROCESSEQUID
            join aps_process_operation apo on apo.ID = apope.PROCESSID
            join aps_production_processes app on app.PRODUCTPROCESSNAME=apo.PROCESS
        where
            apope.deleted='0' and apo.DELETED='0' and arr.deleted='0'
          and app.PROCESSCATEGORY='40' -- 精整
          and arr.REPORTWORKSTATUS='已报工'
          and (YEARWEEK(arr.REPORTWORKTIME,1)=YEARWEEK(CURRENT_DATE,1) -- 本周
           or DATE(arr.REPORTWORKTIME) = DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY)) -- 昨日
          and apo.TENANTID=#{tenantId}
        group by
            apope.PROCESSWORKSHOPID,PROCESSDAY
            ),
            total as (
        select plan.TENANTID,
            plan.PROCESSWORKSHOPID,
            plan.PROCESSDAY,
            plan.PROCESSWORKSHOP,
            plan.PLANPRODUCEVOLUME, -- 本周计划加工量
            plan.YPLANPRODUCEVOLUME, -- 昨日计划加工量
            actual.ACTUALPRODUCEVOLUME,
            actual.YACTUALPRODUCEVOLUME
        from plan left join actual on plan.PROCESSWORKSHOPID=actual.PROCESSWORKSHOPID and plan.PROCESSDAY = actual.PROCESSDAY
        union
        select actual.TENANTID,
            actual.PROCESSWORKSHOPID,
            actual.PROCESSDAY,
            actual.PROCESSWORKSHOP,
            plan.PLANPRODUCEVOLUME, -- 本周计划加工量
            plan.YPLANPRODUCEVOLUME, -- 昨日计划加工量
            actual.ACTUALPRODUCEVOLUME,
            actual.YACTUALPRODUCEVOLUME
        from plan right join actual on plan.PROCESSWORKSHOPID=actual.PROCESSWORKSHOPID and plan.PROCESSDAY = actual.PROCESSDAY
            )

        select
            total.PROCESSWORKSHOPID
             ,total.PROCESSWORKSHOP
             ,IFNULL(ROUND(sum(if(YEARWEEK(total.PROCESSDAY,1)=YEARWEEK(CURRENT_DATE,1),total.PLANPRODUCEVOLUME,null)),2),0) AS 'WEEKPLANPRODUCEVOLUME' -- 本周计划加工（吨）
             ,IFNULL(ROUND(sum(if(DATE(total.PROCESSDAY) = DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY),total.YPLANPRODUCEVOLUME,null)),2),0) AS 'YPLANPRODUCEVOLUME' -- 昨日计划加工（吨）
             ,IFNULL(ROUND(sum(if(YEARWEEK(total.PROCESSDAY,1)=YEARWEEK(CURRENT_DATE,1),total.ACTUALPRODUCEVOLUME,null)),2),0) AS 'WEEKACTUALPRODUCEVOLUME' -- 本周实际已加工（吨）
             ,IFNULL(ROUND(sum(if(DATE(total.PROCESSDAY) = DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY),total.YACTUALPRODUCEVOLUME,null)),2),0) AS 'YACTUALPRODUCEVOLUME' -- 昨日实际已加工（吨）
        --              ,ROUND(if(plan.PLANPRODUCEVOLUME is null or plan.PLANPRODUCEVOLUME = 0,
--                  1, actual.ACTUALPRODUCEVOLUME/plan.PLANPRODUCEVOLUME),2) COMPLETIONRATIO -- 本周实际已完成比例
            ,IFNULL(ROUND(AVG(total.ACTUALPRODUCEVOLUME),2),0) COMPLETIONRATIO -- 本周日平均
        from
            total  group by total.PROCESSWORKSHOPID

    </select>
    <select id="selectProcessAvg" resultType="com.rongwei.bsentity.domain.ApsReportJzProcessPhoto">
        with months as (
            -- 12个月
            SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11
        ),
             plan as (
                 -- 近12个月精整计划加工量
                 select
                     DATE_FORMAT(apope.PLANENDDATE, '%Y-%m') month_year,
                     sum((apope.PLANPROCESSRALL-apope.CANCELROLL)*apo.SINGLEROLLWEIGHT) PLANPRODUCEVOLUME -- 当月计划加工量
                 from
                     aps_process_operation_process_equ apope
                         join aps_process_operation apo on apo.ID = apope.PROCESSID
                         join aps_production_processes app on app.PRODUCTPROCESSNAME=apo.PROCESS
                 where
                     apope.deleted='0' and apo.DELETED='0'
                   and app.PROCESSCATEGORY='40' -- 精整
                   and apo.PROCESSSTATUS in ('30','40','50') -- 已发布
                   and (apope.PLANPROCESSRALL-apope.CANCELROLL)>0 -- 计划加工卷数- 已取消卷数合计
                   and apope.PLANENDDATE between DATE_FORMAT(DATE_SUB(CURRENT_DATE, INTERVAL 11 MONTH), '%Y-%m-01') and DATE_FORMAT(LAST_DAY(CURRENT_DATE()),'%Y-%m-%d 23:59:59') -- 近12个月计划
                   and apo.TENANTID=#{tenantId}
                 group by
                     month_year
             ),
             actual as (
                 -- 近12个月精整实际加工量
                 select
                     DATE_FORMAT(arr.REPORTWORKTIME, '%Y-%m') month_year,
                     sum((LENGTH(trim( BOTH ',' FROM arr.BATCHNUMBER))-LENGTH(REPLACE(trim( BOTH ',' FROM arr.BATCHNUMBER), ',', '')) + 1) * IFNULL(arr.ACTUALROLLWEIGHT,apo.SINGLEROLLWEIGHT)) ACTUALPRODUCEVOLUME -- 当月实际已加工量
                 from
                     aps_report_records arr
                         join aps_process_operation_process_equ apope  on apope.id=arr.PROCESSEQUID
                         join aps_process_operation apo on apo.ID = apope.PROCESSID
                         join aps_production_processes app on app.PRODUCTPROCESSNAME=apo.PROCESS
                 where
                     apope.deleted='0' and apo.DELETED='0' and arr.deleted='0'
                   and app.PROCESSCATEGORY='40' -- 精整
                   and arr.REPORTWORKSTATUS='已报工'
                   and arr.REPORTWORKTIME between DATE_FORMAT(DATE_SUB(CURRENT_DATE, INTERVAL 11 MONTH), '%Y-%m-01') and DATE_FORMAT(LAST_DAY(CURRENT_DATE()),'%Y-%m-%d 23:59:59') -- 近12个月计划
                   and apo.TENANTID=#{tenantId}
                 group by
                     month_year
             )
        -- 计划加工量月平均值，实际已加工量月平均值
        select
            IFNULL(ROUND(AVG(plan.PLANPRODUCEVOLUME),2),0) AVGPLANPRODUCEVOLUME, -- 计划加工量月平均
            IFNULL(ROUND(AVG(actual.ACTUALPRODUCEVOLUME),2),0) AVGACTUALPRODUCEVOLUME -- 实际已加工量月平均
        from
            months
                LEFT JOIN plan ON plan.month_year = DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL months.n MONTH), '%Y-%m')
                LEFT JOIN actual ON actual.month_year = DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL months.n MONTH), '%Y-%m') limit 1
    </select>
    <select id="selectProcessTrend" resultType="com.rongwei.bsentity.domain.ApsReportJzProcessPhoto">
        with months as (
            -- 12个月
            SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11
        ),
             plan as (
                 -- 近12个月精整计划加工量
                 select
                     DATE_FORMAT(apope.PLANENDDATE, '%Y-%m') month_year,
                     sum((apope.PLANPROCESSRALL-apope.CANCELROLL)*apo.SINGLEROLLWEIGHT) PLANPRODUCEVOLUME -- 当月计划加工量
                 from
                     aps_process_operation_process_equ apope
                         join aps_process_operation apo on apo.ID = apope.PROCESSID
                         join aps_production_processes app on app.PRODUCTPROCESSNAME=apo.PROCESS
                 where
                     apope.deleted='0' and apo.DELETED='0'
                   and app.PROCESSCATEGORY='40' -- 精整
                   and apo.PROCESSSTATUS in ('30','40','50') -- 已发布
                   and (apope.PLANPROCESSRALL-apope.CANCELROLL)>0 -- 计划加工卷数- 已取消卷数合计
                   and apope.PLANENDDATE between DATE_FORMAT(DATE_SUB(CURRENT_DATE, INTERVAL 11 MONTH), '%Y-%m-01') and DATE_FORMAT(LAST_DAY(CURRENT_DATE()),'%Y-%m-%d 23:59:59') -- 近12个月计划
                   and apo.TENANTID=#{tenantId}
                 group by
                     month_year
             ),
             actual as (
                 -- 近12个月精整实际加工量
                 select
                     DATE_FORMAT(arr.REPORTWORKTIME, '%Y-%m') month_year,
                     sum((LENGTH(trim( BOTH ',' FROM arr.BATCHNUMBER))-LENGTH(REPLACE(trim( BOTH ',' FROM arr.BATCHNUMBER), ',', '')) + 1) * IFNULL(arr.ACTUALROLLWEIGHT,apo.SINGLEROLLWEIGHT)) ACTUALPRODUCEVOLUME -- 当月实际已加工量
                 from
                     aps_report_records arr
                         join aps_process_operation_process_equ apope  on apope.id=arr.PROCESSEQUID
                         join aps_process_operation apo on apo.ID = apope.PROCESSID
                         join aps_production_processes app on app.PRODUCTPROCESSNAME=apo.PROCESS
                 where
                     apope.deleted='0' and apo.DELETED='0' and arr.deleted='0'
                   and app.PROCESSCATEGORY='40' -- 精整
                   and arr.REPORTWORKSTATUS='已报工'
                   and arr.REPORTWORKTIME between DATE_FORMAT(DATE_SUB(CURRENT_DATE, INTERVAL 11 MONTH), '%Y-%m-01') and DATE_FORMAT(LAST_DAY(CURRENT_DATE()),'%Y-%m-%d 23:59:59') -- 近12个月计划
                   and apo.TENANTID=#{tenantId}
                 group by
                     month_year
             )
        select
            DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL months.n MONTH), '%Y-%m') yearmonth,
            IFNULL(ROUND(plan.PLANPRODUCEVOLUME,2),0) AS 'PLANPRODUCEVOLUME', -- 计划加工量
            IFNULL(ROUND(actual.ACTUALPRODUCEVOLUME,2),0) AS 'ACTUALPRODUCEVOLUME' -- 实际已加工量
        from
            months
                LEFT JOIN plan ON plan.month_year = DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL months.n MONTH), '%Y-%m')
                LEFT JOIN actual ON actual.month_year = DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL months.n MONTH), '%Y-%m')
        ORDER BY
            yearmonth
    </select>
    <select id="selectZjProcessTable" resultType="com.rongwei.bsentity.domain.ApsReportZjProcessTable">
        with plan as (
            -- 按照车间分组统计本周轧机工序类别计划加工量
            select
                apo.TENANTID,
                apope.PROCESSWORKSHOPID,
            DATE(apope.PLANENDDATE) PROCESSDAY,
            max(apope.PROCESSWORKSHOP) PROCESSWORKSHOP,
            sum(if(YEARWEEK(apope.PLANENDDATE,1)=YEARWEEK(CURRENT_DATE,1), -- 本周
            apope.PLANPROCESSRALL-apope.CANCELROLL,null)) PLANPRODUCEVOLUME, -- 本周计划加工量
            sum(if(DATE(apope.PLANENDDATE) = DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY), -- 昨日
            apope.PLANPROCESSRALL-apope.CANCELROLL,null)) YPLANPRODUCEVOLUME -- 昨日计划加工量
        from
            aps_process_operation_process_equ apope
            join aps_process_operation apo on apo.ID = apope.PROCESSID
            join aps_production_processes app on app.PRODUCTPROCESSNAME=apo.PROCESS
        where
            apope.deleted='0' and apo.DELETED='0'
          and app.PROCESSCATEGORY='20' -- 轧机
          and apo.PROCESSSTATUS in ('30','40','50') -- 已发布
          and (apope.PLANPROCESSRALL-apope.CANCELROLL)>0 -- 计划加工卷数- 已取消卷数合计
          and (YEARWEEK(apope.PLANENDDATE,1)=YEARWEEK(CURRENT_DATE,1) -- 本周
           or DATE(apope.PLANENDDATE) = DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY)) -- 昨日
          and apo.TENANTID=#{tenantId}
        group by
            apope.PROCESSWORKSHOPID,PROCESSDAY
            ),
            actual as (
        -- 按照车间分组统计本周轧机工序类别实际加工量
        select
            apo.TENANTID,
            apope.PROCESSWORKSHOPID,
            DATE(arr.REPORTWORKTIME) PROCESSDAY,
            max(apope.PROCESSWORKSHOP) PROCESSWORKSHOP,
            sum(if(YEARWEEK(arr.REPORTWORKTIME,1)=YEARWEEK(CURRENT_DATE,1), -- 本周
            1,null)) ACTUALPRODUCEVOLUME, -- 本周实际已加工量
            sum(if(DATE(arr.REPORTWORKTIME) = DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY), --  昨日
            1,null)) YACTUALPRODUCEVOLUME -- 昨日实际已加工量
        from
            aps_report_records arr
            join aps_process_operation_process_equ apope  on apope.id=arr.PROCESSEQUID
            join aps_process_operation apo on apo.ID = apope.PROCESSID
            join aps_production_processes app on app.PRODUCTPROCESSNAME=apo.PROCESS
        where
            apope.deleted='0' and apo.DELETED='0' and arr.deleted='0'
          and app.PROCESSCATEGORY='20' -- 轧机
          and arr.REPORTWORKSTATUS='已报工'
          and (YEARWEEK(arr.REPORTWORKTIME,1)=YEARWEEK(CURRENT_DATE,1) -- 本周
           or DATE(arr.REPORTWORKTIME) = DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY)) -- 昨日
          and apo.TENANTID=#{tenantId}
        group by
            apope.PROCESSWORKSHOPID,PROCESSDAY
            ),
            total as (
        select plan.TENANTID,
            plan.PROCESSWORKSHOPID,
            plan.PROCESSDAY,
            plan.PROCESSWORKSHOP,
            plan.PLANPRODUCEVOLUME, -- 本周计划加工量
            plan.YPLANPRODUCEVOLUME, -- 昨日计划加工量
            actual.ACTUALPRODUCEVOLUME,
            actual.YACTUALPRODUCEVOLUME
        from plan left join actual on plan.PROCESSWORKSHOPID=actual.PROCESSWORKSHOPID and plan.PROCESSDAY = actual.PROCESSDAY
        union
        select actual.TENANTID,
            actual.PROCESSWORKSHOPID,
            actual.PROCESSDAY,
            actual.PROCESSWORKSHOP,
            plan.PLANPRODUCEVOLUME, -- 本周计划加工量
            plan.YPLANPRODUCEVOLUME, -- 昨日计划加工量
            actual.ACTUALPRODUCEVOLUME,
            actual.YACTUALPRODUCEVOLUME
        from plan right join actual on plan.PROCESSWORKSHOPID=actual.PROCESSWORKSHOPID and plan.PROCESSDAY = actual.PROCESSDAY
            )

        select
            total.PROCESSWORKSHOPID
             ,total.PROCESSWORKSHOP
             ,IFNULL(ROUND(sum(if(YEARWEEK(total.PROCESSDAY,1)=YEARWEEK(CURRENT_DATE,1),total.PLANPRODUCEVOLUME,null)),2),0) AS 'WEEKPLANPRODUCEVOLUME' -- 本周计划加工（吨）
             ,IFNULL(ROUND(sum(if(DATE(total.PROCESSDAY) = DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY),total.YPLANPRODUCEVOLUME,null)),2),0) AS 'YPLANPRODUCEVOLUME' -- 昨日计划加工（吨）
             ,IFNULL(ROUND(sum(if(YEARWEEK(total.PROCESSDAY,1)=YEARWEEK(CURRENT_DATE,1),total.ACTUALPRODUCEVOLUME,null)),2),0) AS 'WEEKACTUALPRODUCEVOLUME' -- 本周实际已加工（吨）
             ,IFNULL(ROUND(sum(if(DATE(total.PROCESSDAY) = DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY),total.YACTUALPRODUCEVOLUME,null)),2),0) AS 'YACTUALPRODUCEVOLUME' -- 昨日实际已加工（吨）
        --              ,ROUND(if(plan.PLANPRODUCEVOLUME is null or plan.PLANPRODUCEVOLUME = 0,
--                  1, actual.ACTUALPRODUCEVOLUME/plan.PLANPRODUCEVOLUME),2) COMPLETIONRATIO -- 本周实际已完成比例
            ,IFNULL(ROUND(AVG(total.ACTUALPRODUCEVOLUME),2),0) COMPLETIONRATIO -- 本周日平均
        from
            total  group by total.PROCESSWORKSHOPID
    </select>
    <select id="selectAvgZjProcessPhoto" resultType="com.rongwei.bsentity.domain.ApsReportZjProcessPhoto">
        with months as (
            -- 12个月
            SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11
        ),
             plan as (
                 -- 近12个月轧机计划加工量
                 select
                     DATE_FORMAT(apope.PLANENDDATE, '%Y-%m') month_year,
                     sum(apope.PLANPROCESSRALL-apope.CANCELROLL) PLANPRODUCEVOLUME -- 当月计划加工量
                 from
                     aps_process_operation_process_equ apope
                         join aps_process_operation apo on apo.ID = apope.PROCESSID
                         join aps_production_processes app on app.PRODUCTPROCESSNAME=apo.PROCESS
                 where
                     apope.deleted='0' and apo.DELETED='0'
                   and app.PROCESSCATEGORY='20' -- 轧机
                   and apo.PROCESSSTATUS in ('30','40','50') -- 已发布
                   and (apope.PLANPROCESSRALL-apope.CANCELROLL)>0 -- 计划加工卷数- 已取消卷数合计
                   and apope.PLANENDDATE between DATE_FORMAT(DATE_SUB(CURRENT_DATE, INTERVAL 11 MONTH), '%Y-%m-01') and DATE_FORMAT(LAST_DAY(CURRENT_DATE()),'%Y-%m-%d 23:59:59') -- 近12个月计划
                   and apo.TENANTID=#{tenantId}
                 group by
                     month_year
             ),
             actual as (
                 -- 近12个月轧机实际加工量
                 select
                     DATE_FORMAT(arr.REPORTWORKTIME, '%Y-%m') month_year,
                     count(arr.id) ACTUALPRODUCEVOLUME -- 当月实际已加工量
                 from
                     aps_report_records arr
                         join aps_process_operation_process_equ apope  on apope.id=arr.PROCESSEQUID
                         join aps_process_operation apo on apo.ID = apope.PROCESSID
                         join aps_production_processes app on app.PRODUCTPROCESSNAME=apo.PROCESS
                 where
                     apope.deleted='0' and apo.DELETED='0' and arr.deleted='0'
                   and app.PROCESSCATEGORY='20' -- 轧机
                   and arr.REPORTWORKSTATUS='已报工'
                   and arr.REPORTWORKTIME between DATE_FORMAT(DATE_SUB(CURRENT_DATE, INTERVAL 11 MONTH), '%Y-%m-01') and DATE_FORMAT(LAST_DAY(CURRENT_DATE()),'%Y-%m-%d 23:59:59') -- 近12个月计划
                   and apo.TENANTID=#{tenantId}
                 group by
                     month_year
             )

        -- 计划加工量月平均值，实际已加工量月平均值
        select
            IFNULL(ROUND(AVG(plan.PLANPRODUCEVOLUME),2),0) AVGPLANPRODUCEVOLUME, -- 计划加工量月平均
            IFNULL(ROUND(AVG(actual.ACTUALPRODUCEVOLUME),2),0) AVGACTUALPRODUCEVOLUME -- 实际已加工量月平均
        from
            months
                LEFT JOIN plan ON plan.month_year = DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL months.n MONTH), '%Y-%m')
                LEFT JOIN actual ON actual.month_year = DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL months.n MONTH), '%Y-%m') limit 1
    </select>
    <select id="selectZjProcessPhoto" resultType="com.rongwei.bsentity.domain.ApsReportZjProcessPhoto">
        with months as (
            -- 12个月
            SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11
        ),
             plan as (
                 -- 近12个月轧机计划加工量
                 select
                     DATE_FORMAT(apope.PLANENDDATE, '%Y-%m') month_year,
                     sum(apope.PLANPROCESSRALL-apope.CANCELROLL) PLANPRODUCEVOLUME -- 当月计划加工量
                 from
                     aps_process_operation_process_equ apope
                         join aps_process_operation apo on apo.ID = apope.PROCESSID
                         join aps_production_processes app on app.PRODUCTPROCESSNAME=apo.PROCESS
                 where
                     apope.deleted='0' and apo.DELETED='0'
                   and app.PROCESSCATEGORY='20' -- 轧机
                   and apo.PROCESSSTATUS in ('30','40','50') -- 已发布
                   and (apope.PLANPROCESSRALL-apope.CANCELROLL)>0 -- 计划加工卷数- 已取消卷数合计
                   and apope.PLANENDDATE between DATE_FORMAT(DATE_SUB(CURRENT_DATE, INTERVAL 11 MONTH), '%Y-%m-01') and DATE_FORMAT(LAST_DAY(CURRENT_DATE()),'%Y-%m-%d 23:59:59') -- 近12个月计划
                   and apo.TENANTID=#{tenantId}
                 group by
                     month_year
             ),
             actual as (
                 -- 近12个月轧机实际加工量
                 select
                     DATE_FORMAT(arr.REPORTWORKTIME, '%Y-%m') month_year,
                     count(arr.id) ACTUALPRODUCEVOLUME -- 当月实际已加工量
                 from
                     aps_report_records arr
                         join aps_process_operation_process_equ apope  on apope.id=arr.PROCESSEQUID
                         join aps_process_operation apo on apo.ID = apope.PROCESSID
                         join aps_production_processes app on app.PRODUCTPROCESSNAME=apo.PROCESS
                 where
                     apope.deleted='0' and apo.DELETED='0' and arr.deleted='0'
                   and app.PROCESSCATEGORY='20' -- 轧机
                   and arr.REPORTWORKSTATUS='已报工'
                   and arr.REPORTWORKTIME between DATE_FORMAT(DATE_SUB(CURRENT_DATE, INTERVAL 11 MONTH), '%Y-%m-01') and DATE_FORMAT(LAST_DAY(CURRENT_DATE()),'%Y-%m-%d 23:59:59') -- 近12个月计划
                   and apo.TENANTID=#{tenantId}
                 group by
                     month_year
             )
        select
            DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL months.n MONTH), '%Y-%m') yearmonth,
            IFNULL(ROUND(plan.PLANPRODUCEVOLUME,2),0) AS 'PLANPRODUCEVOLUME', -- 计划加工量
            IFNULL(ROUND(actual.ACTUALPRODUCEVOLUME,2),0) AS 'ACTUALPRODUCEVOLUME' -- 实际已加工量
        from
            months
                LEFT JOIN plan ON plan.month_year = DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL months.n MONTH), '%Y-%m')
                LEFT JOIN actual ON actual.month_year = DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL months.n MONTH), '%Y-%m')
        ORDER BY
            yearmonth
    </select>
    <select id="selectThProcessTable" resultType="com.rongwei.bsentity.domain.ApsReportThProcessTable">
        -- 退火生产情况：
        with plan as (
            select
                PROCESSWORKSHOPID,
                max(PROCESSWORKSHOP) PROCESSWORKSHOP,
            DATE(PLANENDDATE) PROCESSDAY,
            count(if(YEARWEEK(heat.PROCESSDAY,1)=YEARWEEK(CURRENT_DATE,1), -- 本周
            HEATNUMBER,null)) PLANPRODUCEVOLUME, -- 本周计划加工量=炉次的数量
            count(if(DATE(heat.PROCESSDAY) = DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY), -- 昨日
            HEATNUMBER,null)) YPLANPRODUCEVOLUME -- 昨日计划加工量=炉次的数量
        from (
            -- 按照车间分组统计本周退火工序类别计划加工量
            select
            max(apope.PROCESSWORKSHOPID) PROCESSWORKSHOPID,
            max(apope.PROCESSWORKSHOP) PROCESSWORKSHOP,
            apope.PROCESSDEVICEID+apope.PLANSTARTDATE HEATNUMBER, -- 炉次(设备ID+计划开工时间)
            max(apope.PLANENDDATE) PROCESSDAY,
            apope.PLANENDDATE
            from
            aps_process_operation_process_equ apope
            join aps_process_operation apo on apo.ID = apope.PROCESSID
            join aps_production_processes app on app.PRODUCTPROCESSNAME=apo.PROCESS
            where
            apope.deleted='0' and apo.DELETED='0'
            and app.PROCESSCATEGORY='30' -- 退火
            and apo.PROCESSSTATUS in ('30','40','50') -- 已发布
            and (apope.PLANPROCESSRALL-apope.CANCELROLL)>0 -- 计划加工卷数- 已取消卷数合计
            and (YEARWEEK(apope.PLANENDDATE,1)=YEARWEEK(CURRENT_DATE,1) -- 本周
            or DATE(apope.PLANENDDATE) = DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY)) -- 昨日
            and apo.TENANTID=#{tenantId}
            group by
            HEATNUMBER -- 计划炉次
            ) heat
        group by PROCESSWORKSHOPID
            ),
            actual as (
        -- 按照车间分组统计本周退火工序类别实际加工量
        select
            PROCESSWORKSHOPID,
            max(PROCESSWORKSHOP) PROCESSWORKSHOP,
            DATE(REPORTWORKTIME) PROCESSDAY,
            count(if(YEARWEEK(heat.PROCESSDAY,1)=YEARWEEK(CURRENT_DATE,1), -- 本周
            HEATNUMBER,null)) ACTUALPRODUCEVOLUME, -- 本周实际已加工（炉次）
            count(if(DATE(heat.PROCESSDAY) = DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY), -- 昨日
            HEATNUMBER,null)) YACTUALPRODUCEVOLUME -- 昨日实际已加工（炉次）
        from(
            select
            apope.PROCESSDEVICEID+arr.HEATNUMBER HEATNUMBER, -- 炉次
            max(DATE(arr.REPORTWORKTIME)) PROCESSDAY,
            max(apope.PROCESSWORKSHOPID) PROCESSWORKSHOPID,
            max(apope.PROCESSWORKSHOP) PROCESSWORKSHOP,
            arr.REPORTWORKTIME
            from
            aps_report_records arr
            join aps_process_operation_process_equ apope  on apope.id=arr.PROCESSEQUID
            join aps_process_operation apo on apo.ID = apope.PROCESSID
            join aps_production_processes app on app.PRODUCTPROCESSNAME=apo.PROCESS
            where
            apope.deleted='0' and apo.DELETED='0' and arr.deleted='0'
            and app.PROCESSCATEGORY='30' -- 退火
            and arr.REPORTWORKSTATUS='已报工'
            and (YEARWEEK(arr.REPORTWORKTIME,1)=YEARWEEK(CURRENT_DATE,1) -- 本周
            or DATE(arr.REPORTWORKTIME) = DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY)) -- 昨日
            and apo.TENANTID=#{tenantId}
            group by
            apope.PROCESSDEVICEID,arr.HEATNUMBER
            ) heat
        group by PROCESSWORKSHOPID
            )
        select
            plan.PROCESSWORKSHOPID
             ,plan.PROCESSWORKSHOP
             ,IFNULL(ROUND(sum(if(YEARWEEK(plan.PROCESSDAY,1)=YEARWEEK(CURRENT_DATE,1),plan.PLANPRODUCEVOLUME,null)),2),0) AS 'WEEKPLANPRODUCEVOLUME' -- 本周计划加工（吨）
             ,IFNULL(ROUND(sum(if(DATE(plan.PROCESSDAY) = DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY),plan.YPLANPRODUCEVOLUME,null)),2),0) AS 'YPLANPRODUCEVOLUME' -- 昨日计划加工（吨）
             ,IFNULL(ROUND(sum(if(YEARWEEK(actual.PROCESSDAY,1)=YEARWEEK(CURRENT_DATE,1),actual.ACTUALPRODUCEVOLUME,null)),2),0) AS 'WEEKACTUALPRODUCEVOLUME' -- 本周实际已加工（吨）
             ,IFNULL(ROUND(sum(if(DATE(actual.PROCESSDAY) = DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY),actual.YACTUALPRODUCEVOLUME,null)),2),0) AS 'YACTUALPRODUCEVOLUME' -- 昨日实际已加工（吨）
        --              ,ROUND(if(plan.PLANPRODUCEVOLUME is null or plan.PLANPRODUCEVOLUME = 0,
--                  1, actual.ACTUALPRODUCEVOLUME/plan.PLANPRODUCEVOLUME),2) COMPLETIONRATIO -- 本周实际已完成比例
            ,IFNULL(ROUND(AVG(actual.ACTUALPRODUCEVOLUME),2),0) COMPLETIONRATIO -- 本周日平均
        from
            plan left join actual on plan.PROCESSWORKSHOPID=actual.PROCESSWORKSHOPID group by plan.PROCESSWORKSHOPID
        union -- mysql 该版本不支持full join，通过left join 和right join配合
        select
            actual.PROCESSWORKSHOPID
             ,actual.PROCESSWORKSHOP
             ,IFNULL(ROUND(sum(if(YEARWEEK(plan.PROCESSDAY,1)=YEARWEEK(CURRENT_DATE,1),plan.PLANPRODUCEVOLUME,null)),2),0) AS 'WEEKPLANPRODUCEVOLUME' -- 本周计划加工（吨）
             ,IFNULL(ROUND(sum(if(DATE(plan.PROCESSDAY) = DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY),plan.YPLANPRODUCEVOLUME,null)),2),0) AS 'YPLANPRODUCEVOLUME' -- 昨日计划加工（吨）
             ,IFNULL(ROUND(sum(if(YEARWEEK(actual.PROCESSDAY,1)=YEARWEEK(CURRENT_DATE,1),actual.ACTUALPRODUCEVOLUME,null)),2),0) AS 'WEEKACTUALPRODUCEVOLUME' -- 本周实际已加工（吨）
             ,IFNULL(ROUND(sum(if(DATE(actual.PROCESSDAY) = DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY),actual.YACTUALPRODUCEVOLUME,null)),2),0) AS 'YACTUALPRODUCEVOLUME' -- 昨日实际已加工（吨）
        --              ,ROUND(if(plan.PLANPRODUCEVOLUME is null or plan.PLANPRODUCEVOLUME = 0,
--                  1, actual.ACTUALPRODUCEVOLUME/plan.PLANPRODUCEVOLUME),2) COMPLETIONRATIO -- 本周实际已完成比例
            ,IFNULL(ROUND(AVG(actual.ACTUALPRODUCEVOLUME),2),0) COMPLETIONRATIO -- 本周日平均
        from
            plan right join actual on plan.PROCESSWORKSHOPID=actual.PROCESSWORKSHOPID group by actual.PROCESSWORKSHOPID
    </select>
    <select id="selectAvgThProcessPhoto" resultType="com.rongwei.bsentity.domain.ApsReportThProcessPhoto">
        with months as (
            -- 12个月
            SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11
        ),
             plan as (
                 -- 近12个月退火计划加工量

                 -- 按照车间分组统计本月退火工序类别计划加工量
                 select
                     DATE_FORMAT(heat.PROCESSDAY, '%Y-%m') month_year,
                     count(HEATNUMBER) PLANPRODUCEVOLUME -- 当月计划加工量=炉次的数量
                 from (
                          -- 按照车间分组统计本月退火工序类别计划加工量
                          select
                              apope.PROCESSDEVICEID+apope.PLANSTARTDATE HEATNUMBER, -- 炉次(设备ID+计划开工时间)
                              max(apope.PLANENDDATE) PROCESSDAY
                          from
                              aps_process_operation_process_equ apope
                                  join aps_process_operation apo on apo.ID = apope.PROCESSID
                                  join aps_production_processes app on app.PRODUCTPROCESSNAME=apo.PROCESS
                          where
                              apope.deleted='0' and apo.DELETED='0'
                            and app.PROCESSCATEGORY='30' -- 退火
                            and apo.PROCESSSTATUS in ('30','40','50') -- 已发布
                            and (apope.PLANPROCESSRALL-apope.CANCELROLL)>0 -- 计划加工卷数- 已取消卷数合计
                            and (YEAR(apope.PLANENDDATE) = YEAR(CURRENT_DATE) and MONTH(apope.PLANENDDATE) = MONTH(CURRENT_DATE) -- 本月
                              or DATE(apope.PLANENDDATE) = DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY)) -- 昨日
                            and apo.TENANTID=#{tenantId}
                          group by
                              HEATNUMBER -- 计划炉次
                      ) heat
                 group by month_year
             ),
             actual as (
                 -- 近12个月退火实际加工量
                 select
                     DATE_FORMAT(heat.PROCESSDAY, '%Y-%m') month_year,
                     count(HEATNUMBER) ACTUALPRODUCEVOLUME -- 当月实际已加工量=炉次的数量
                 from(
                         -- 按照车间分组统计本月退火工序类别实际加工量
                         select
                             apope.PROCESSDEVICEID+arr.HEATNUMBER HEATNUMBER, -- 炉次
                             max(DATE(arr.REPORTWORKTIME)) PROCESSDAY
                         from
                             aps_report_records arr
                                 join aps_process_operation_process_equ apope  on apope.id=arr.PROCESSEQUID
                                 join aps_process_operation apo on apo.ID = apope.PROCESSID
                                 join aps_production_processes app on app.PRODUCTPROCESSNAME=apo.PROCESS
                         where
                             apope.deleted='0' and apo.DELETED='0' and arr.deleted='0'
                           and app.PROCESSCATEGORY='30' -- 退火
                           and arr.REPORTWORKSTATUS='已报工'
                           and apo.TENANTID=#{tenantId}
                         group by
                             apope.PROCESSDEVICEID,arr.HEATNUMBER
                     ) heat
                 group by month_year
             )
        -- 计划加工量月平均值，实际已加工量月平均值
        select
            IFNULL(ROUND(AVG(plan.PLANPRODUCEVOLUME),2),0) AVGPLANPRODUCEVOLUME, -- 计划加工量月平均
            IFNULL(ROUND(AVG(actual.ACTUALPRODUCEVOLUME),2),0) AVGACTUALPRODUCEVOLUME -- 实际已加工量月平均
        from
            months
                LEFT JOIN plan ON plan.month_year = DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL months.n MONTH), '%Y-%m')
                LEFT JOIN actual ON actual.month_year = DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL months.n MONTH), '%Y-%m') limit 1
    </select>
    <select id="selectThProcessPhoto" resultType="com.rongwei.bsentity.domain.ApsReportThProcessPhoto">
        with months as (
            -- 12个月
            SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11
        ),
             plan as (
                 -- 近12个月退火计划加工量

                 -- 按照车间分组统计本月退火工序类别计划加工量
                 select
                     DATE_FORMAT(heat.PROCESSDAY, '%Y-%m') month_year,
                     count(HEATNUMBER) PLANPRODUCEVOLUME -- 当月计划加工量=炉次的数量
                 from (
                          -- 按照车间分组统计本月退火工序类别计划加工量
                          select
                              apope.PROCESSDEVICEID+apope.PLANSTARTDATE HEATNUMBER, -- 炉次(设备ID+计划开工时间)
                              max(apope.PLANENDDATE) PROCESSDAY
                          from
                              aps_process_operation_process_equ apope
                                  join aps_process_operation apo on apo.ID = apope.PROCESSID
                                  join aps_production_processes app on app.PRODUCTPROCESSNAME=apo.PROCESS
                          where
                              apope.deleted='0' and apo.DELETED='0'
                            and app.PROCESSCATEGORY='30' -- 退火
                            and apo.PROCESSSTATUS in ('30','40','50') -- 已发布
                            and (apope.PLANPROCESSRALL-apope.CANCELROLL)>0 -- 计划加工卷数- 已取消卷数合计
                            and (YEAR(apope.PLANENDDATE) = YEAR(CURRENT_DATE) and MONTH(apope.PLANENDDATE) = MONTH(CURRENT_DATE) -- 本月
                              or DATE(apope.PLANENDDATE) = DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY)) -- 昨日
                            and apo.TENANTID=#{tenantId}
                          group by
                              HEATNUMBER -- 计划炉次
                      ) heat
                 group by month_year
             ),
             actual as (
                 -- 近12个月退火实际加工量
                 select
                     DATE_FORMAT(heat.PROCESSDAY, '%Y-%m') month_year,
                     count(HEATNUMBER) ACTUALPRODUCEVOLUME -- 当月实际已加工量=炉次的数量
                 from(
                         -- 按照车间分组统计本月退火工序类别实际加工量
                         select
                             apope.PROCESSDEVICEID+arr.HEATNUMBER HEATNUMBER, -- 炉次
                             max(DATE(arr.REPORTWORKTIME)) PROCESSDAY
                         from
                             aps_report_records arr
                                 join aps_process_operation_process_equ apope  on apope.id=arr.PROCESSEQUID
                                 join aps_process_operation apo on apo.ID = apope.PROCESSID
                                 join aps_production_processes app on app.PRODUCTPROCESSNAME=apo.PROCESS
                         where
                             apope.deleted='0' and apo.DELETED='0' and arr.deleted='0'
                           and app.PROCESSCATEGORY='30' -- 退火
                           and arr.REPORTWORKSTATUS='已报工'
                           and apo.TENANTID=#{tenantId}
                         group by
                             apope.PROCESSDEVICEID,arr.HEATNUMBER
                     ) heat
                 group by month_year
             )
        select
            DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL months.n MONTH), '%Y-%m') yearmonth,
            IFNULL(ROUND(plan.PLANPRODUCEVOLUME,2),0) PLANPRODUCEVOLUME, -- 计划加工量
            IFNULL(ROUND(actual.ACTUALPRODUCEVOLUME,2),0) ACTUALPRODUCEVOLUME -- 实际已加工量
        from
            months
                LEFT JOIN plan ON plan.month_year = DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL months.n MONTH), '%Y-%m')
                LEFT JOIN actual ON actual.month_year = DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL months.n MONTH), '%Y-%m')
        ORDER BY
            yearmonth
    </select>
    <select id="selectNowWeekActualGetOrderNum" resultType="com.rongwei.bsentity.domain.ApsReportYearAndMonth">
        select
            apro.TENANTID AS 'tenantid',
                ifnull(ROUND(sum((apope.PLANPROCESSRALL-apope.CANCELROLL)*apoom.PLANPRODROLLNUM/apo.PLANPROCESSRALL*apoom.SINGLEROLLWEIGHT),2),0) AS 'actualordernumweek' -- 本周实际接单量
        from
            aps_process_operation_process_equ apope
                join aps_process_operation apo on apo.ID = apope.PROCESSID
                join aps_process_operation_out_mater apoom on apo.id=apoom.MAINID
                join aps_blank_order abo on apo.BLANKID = abo.id
                join aps_production_order apro on apro.ID = abo.PRODUCTIONORDERID
                left join aps_product_detail apd on apoom.ORDERDETAILID = apd.id and apd.DELETED='0'
                left join aps_product_specs aps on aps.TENANTID=apro.TENANTID and aps.DELETED='0'
                left join aps_production_merge_order apmd on apmd.ORDERNO = apd.MERGEORDERNUMBER and apmd.DELETED='0'
            where apope.DELETED='0' and apo.DELETED='0' and abo.DELETED='0' and apro.DELETED='0' and apoom.DELETED='0'
                and apoom.THICKNESS > IFNULL(aps.THICKSTR,0) and apoom.THICKNESS &lt;= IFNULL(aps.THICKEND,99999)
                and IFNULL(apmd.ORDERTYPE, apro.ORDERTYPE)='客户订单'
                and abo.PRODUCTSTATUS in ('30','40','50')
                and (apope.PLANPROCESSRALL-apope.CANCELROLL)>0
                and apoom.PLANPRODROLLNUM>0
                and YEARWEEK(apope.PLANENDDATE,1)=YEARWEEK(CURRENT_DATE,1)
        GROUP BY apro.TENANTID
    </select>
    <select id="selectNowWeekActualProduceNum" resultType="com.rongwei.bsentity.domain.ApsReportYearAndMonth">
        select
            apro.TENANTID AS 'tenantid',
                ifnull(ROUND(sum(aro.WEIGHT),2),0) AS 'actualfinishnumweek'
        from
            aps_process_operation_process_equ apope
                join aps_process_operation apo on apo.ID = apope.PROCESSID
                join aps_blank_order abo on apo.BLANKID = abo.id
                join aps_production_order apro on apro.ID = abo.PRODUCTIONORDERID
                join aps_report_records arr on apope.id=arr.PROCESSEQUID
                join aps_report_output aro on arr.id=aro.mainid
                join aps_process_operation_out_mater apoom on aro.OUTPUTID=apoom.ID-- 作业输出物料
                left join aps_product_detail apd on apoom.ORDERDETAILID = apd.id and apd.DELETED = '0' -- 订单产品明细
                left join aps_production_merge_order apmd on apmd.ORDERNO = apd.MERGEORDERNUMBER and apmd.DELETED='0'
        where
            apope.deleted='0' and apo.DELETED='0' and abo.DELETED='0' and apro.deleted='0'
                and arr.deleted='0' and aro.DELETED='0' and apoom.DELETED = '0'
                and ifnull(apmd.ORDERTYPE, apro.ORDERTYPE) = '客户订单' -- 订单类型为空默认为客户订单
                and arr.CHECKSTATUS='已检验'
                and aro.QUALIFIED='是'
                and apoom.PLANPRODROLLNUM>0
                and YEARWEEK(arr.REPORTWORKTIME,1)=YEARWEEK(CURRENT_DATE,1)
        GROUP BY apro.TENANTID
    </select>
    <select id="selectPlanMap" resultType="java.util.Map">
        SELECT
            aop.TENANTID AS 'tenantid',
                aop.PARTYEAR AS 'year',
                ifnull( ROUND( sum( ONEM )/DAY(LAST_DAY(CONCAT(aop.PARTYEAR, '-01-01'))), 4 ), 0 ) AS '01',
                ifnull( ROUND( sum( TWOM )/DAY(LAST_DAY(CONCAT(aop.PARTYEAR, '-02-01'))), 4 ), 0 ) AS '02',
                ifnull( ROUND( sum( THREEM )/DAY(LAST_DAY(CONCAT(aop.PARTYEAR, '-03-01'))), 4 ), 0 ) AS '03',
                ifnull( ROUND( sum( FOURM )/DAY(LAST_DAY(CONCAT(aop.PARTYEAR, '-04-01'))), 4 ), 0 ) AS '04',
                ifnull( ROUND( sum( FIVEM )/DAY(LAST_DAY(CONCAT(aop.PARTYEAR, '-05-01'))), 4 ), 0 ) AS '05',
                ifnull( ROUND( sum( SIXM )/DAY(LAST_DAY(CONCAT(aop.PARTYEAR, '-06-01'))), 4 ), 0 ) AS '06',
                ifnull( ROUND( sum( SEVENM )/DAY(LAST_DAY(CONCAT(aop.PARTYEAR, '-07-01'))), 4 ), 0 ) AS '07',
                ifnull( ROUND( sum( EIGHTM )/DAY(LAST_DAY(CONCAT(aop.PARTYEAR, '-08-01'))), 4 ), 0 ) AS '08',
                ifnull( ROUND( sum( NINEM )/DAY(LAST_DAY(CONCAT(aop.PARTYEAR, '-09-01'))), 4 ), 0 ) AS '09',
                ifnull( ROUND( sum( TENM )/DAY(LAST_DAY(CONCAT(aop.PARTYEAR, '-10-01'))), 4 ), 0 ) AS '10',
                ifnull( ROUND( sum( ELEVENM )/DAY(LAST_DAY(CONCAT(aop.PARTYEAR, '-11-01'))), 4 ), 0 ) AS '11',
                ifnull( ROUND( sum( TWELVEM )/DAY(LAST_DAY(CONCAT(aop.PARTYEAR, '-12-01'))), 4 ), 0 ) AS '12'
        FROM
            aps_accept_order_plan aop
                JOIN aps_accept_order_plan_detail aopd ON aop.id = aopd.MAINID
        WHERE
            aop.deleted = '0'
          AND aopd.deleted = '0'
          AND aop.PARTYEAR IN (YEAR(CURRENT_DATE),YEAR(CURRENT_DATE)-1)
        GROUP BY
            aop.TENANTID,aop.PARTYEAR
    </select>
</mapper>
