<?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.ApsBlankOrderDao">

    <resultMap id="BaseResultMap" type="com.rongwei.bsentity.domain.ApsBlankOrderDo">
            <id property="id" column="ID" jdbcType="VARCHAR"/>
            <result property="productionorderid" column="PRODUCTIONORDERID" jdbcType="VARCHAR"/>
            <result property="materialname" column="MATERIALNAME" jdbcType="VARCHAR"/>
            <result property="materialcode" column="MATERIALCODE" jdbcType="VARCHAR"/>
            <result property="blanktype" column="BLANKTYPE" jdbcType="VARCHAR"/>
            <result property="producttype" column="PRODUCTTYPE" jdbcType="VARCHAR"/>
            <result property="alloy" column="ALLOY" jdbcType="VARCHAR"/>
            <result property="alloystatus" column="ALLOYSTATUS" jdbcType="VARCHAR"/>
            <result property="thickness" column="THICKNESS" jdbcType="DECIMAL"/>
            <result property="prowidth" column="PROWIDTH" jdbcType="DECIMAL"/>
            <result property="prolength" column="PROLENGTH" jdbcType="DECIMAL"/>
            <result property="singlerollweight" column="SINGLEROLLWEIGHT" jdbcType="DECIMAL"/>
            <result property="numberunit" column="NUMBERUNIT" jdbcType="VARCHAR"/>
            <result property="rollnum" column="ROLLNUM" jdbcType="INTEGER"/>
            <result property="totalweight" column="TOTALWEIGHT" jdbcType="DECIMAL"/>
            <result property="preparematerial" column="PREPAREMATERIAL" jdbcType="VARCHAR"/>
            <result property="defectiveproduct" column="DEFECTIVEPRODUCT" jdbcType="VARCHAR"/>
            <result property="preparematerialbatchno" column="PREPAREMATERIALBATCHNO" jdbcType="VARCHAR"/>
            <result property="outputorderproduct" column="OUTPUTORDERPRODUCT" jdbcType="VARCHAR"/>
            <result property="outputfinishproduct" column="OUTPUTFINISHPRODUCT" jdbcType="VARCHAR"/>
            <result property="outputmaterial" column="OUTPUTMATERIAL" jdbcType="VARCHAR"/>
            <result property="productstatus" column="PRODUCTSTATUS" 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="producttypeid" column="PRODUCTTYPEID" jdbcType="VARCHAR"/>
            <result property="inputreportdescribe" column="INPUTREPORTDESCRIBE" jdbcType="VARCHAR"/>
            <result property="craftrouteid" column="CRAFTROUTEID" jdbcType="VARCHAR"/>
            <result property="lockmark" column="LOCKMARK" jdbcType="VARCHAR"/>
            <result property="isstag" column="ISSTAG" jdbcType="VARCHAR"/>
            <result property="schedulingstatus" column="SCHEDULINGSTATUS" jdbcType="VARCHAR"/>
    </resultMap>

    <sql id="Base_Column_List">
        ID,PRODUCTIONORDERID,MATERIALNAME,
        MATERIALCODE,BLANKTYPE,PRODUCTTYPE,
        ALLOY,ALLOYSTATUS,THICKNESS,
        PROWIDTH,PROLENGTH,SINGLEROLLWEIGHT,
        NUMBERUNIT,ROLLNUM,TOTALWEIGHT,
        PREPAREMATERIAL,DEFECTIVEPRODUCT,PREPAREMATERIALBATCHNO,
        OUTPUTORDERPRODUCT,OUTPUTFINISHPRODUCT,OUTPUTMATERIAL,
        PRODUCTSTATUS,TENANTID,ROPTION,
        DELETED,REMARK,CREATEDATE,
        CREATEUSERID,MODIFYDATE,MODIFYUSERID,
        CREATEUSERNAME,MODIFYUSERNAME,PRODUCTTYPEID,
        INPUTREPORTDESCRIBE,CRAFTROUTEID,LOCKMARK,
        ISSTAG,SCHEDULINGSTATUS
    </sql>
    <update id="publish">
        <if test="productionOrderIds != null and productionOrderIds.size() >0">
            UPDATE aps_blank_order b
                <set>
                    PRODUCTSTATUS=(select
                    CASE
                    WHEN GROUP_CONCAT(DISTINCT a.PROCESSSTATUS) = '10' THEN '10'
                    WHEN GROUP_CONCAT(DISTINCT a.PROCESSSTATUS) = '20' THEN '20'
                    WHEN GROUP_CONCAT(DISTINCT a.PROCESSSTATUS) = '30' THEN '30'
                    WHEN GROUP_CONCAT(DISTINCT a.PROCESSSTATUS) = '40' THEN '40'
                    WHEN GROUP_CONCAT(DISTINCT a.PROCESSSTATUS) = '50' THEN '50'
                    WHEN FIND_IN_SET('40',GROUP_CONCAT(DISTINCT a.PROCESSSTATUS)) THEN '40'
                    WHEN FIND_IN_SET('30',GROUP_CONCAT(DISTINCT a.PROCESSSTATUS)) THEN '30'
                    WHEN FIND_IN_SET('20',GROUP_CONCAT(DISTINCT a.PROCESSSTATUS)) THEN '20'
                    ELSE b.PRODUCTSTATUS END
                    END
                    from aps_process_operation a where a.BLANKID = b.ID and deleted='0'
                    ),
                    SCHEDULINGSTATUS="30"
                </set>
            <where>
                AND DELETED='0'
                AND PRODUCTIONORDERID in
                <foreach collection="productionOrderIds" item="id" open="(" close=")" separator=",">
                    #{id}
                </foreach>
            </where>
        </if>
    </update>
    <update id="publishCancel">
        <if test="productionOrderIds != null and productionOrderIds.size() >0">
            UPDATE aps_blank_order b
            <set>
                PRODUCTSTATUS=(select
                CASE
                WHEN GROUP_CONCAT(DISTINCT a.PROCESSSTATUS) = '10' THEN '10'
                WHEN GROUP_CONCAT(DISTINCT a.PROCESSSTATUS) = '20' THEN '20'
                WHEN GROUP_CONCAT(DISTINCT a.PROCESSSTATUS) = '30' THEN '30'
                WHEN GROUP_CONCAT(DISTINCT a.PROCESSSTATUS) = '40' THEN '40'
                WHEN GROUP_CONCAT(DISTINCT a.PROCESSSTATUS) = '50' THEN '50'
                WHEN FIND_IN_SET('50',GROUP_CONCAT(DISTINCT a.PROCESSSTATUS)) THEN '40'
                WHEN FIND_IN_SET('40',GROUP_CONCAT(DISTINCT a.PROCESSSTATUS)) THEN '40'
                WHEN FIND_IN_SET('30',GROUP_CONCAT(DISTINCT a.PROCESSSTATUS)) THEN '30'
                WHEN FIND_IN_SET('20',GROUP_CONCAT(DISTINCT a.PROCESSSTATUS)) THEN '20'
                ELSE b.PRODUCTSTATUS END
                from aps_process_operation a where a.BLANKID = b.ID and deleted='0'
                ),
                SCHEDULINGSTATUS= "20"
            </set>
            <where>
                AND DELETED='0'
                AND PRODUCTIONORDERID in
                <foreach collection="productionOrderIds" item="id" open="(" close=")" separator=",">
                    #{id}
                </foreach>
            </where>
        </if>
    </update>
    <update id="cancelSchedulingUpdate">
        <if test="idList != null and idList.size() >0">
            update aps_blank_order a
            <set>
                LOCKMARK='n',
                PRODUCTSTATUS='10',
                SCHEDULINGSTATUS='10',
                SCHEDULEDATESTART=null,
                SCHEDULEDATEEND=null
            </set>
            <where>
                DELETED='0'
                    AND ID in
                    <foreach collection="idList" item="id" open="(" close=")" separator=",">
                        #{id}
                    </foreach>
                </where>
        </if>
    </update>
    <update id="reportCheckUpdateJobStatus">
        update aps_blank_order
        <set>
            SCHEDULINGSTATUS = if((select GROUP_CONCAT(DISTINCT PROCESSSTATUS)='50' from aps_process_operation where
            DELETED ='0' and BLANKID=#{id}),
            '50',SCHEDULINGSTATUS)
        </set>
        <where>
            DELETED ='0'
            AND ID=#{id}
        </where>
    </update>
    <select id="getProcessInfoByLockTime" resultType="com.rongwei.bsentity.domain.ApsProcessOperationDo">
        select apo.* from aps_process_operation apo
        left join aps_process_operation_process_equ apope on apope.PROCESSID=apo.ID and apope.DELETED='0'
        <where>
            apo.DELETED ='0'
<!--            <if test='lockMark=="n"'>-->
<!--                AND apo.PRODUCTSTATUS !='40'-->
<!--                and apo.PRODUCTSTATUS !='50'-->
<!--            </if>-->
            <choose>
                <when test='lockMark=="y"'> and apope.PLANSTARTDATE &lt; #{lockTime} </when>
                <otherwise>and apope.PLANSTARTDATE &gt; #{lockTime}</otherwise>
            </choose>
        </where>
    </select>
    <select id="getProcessDetailInfoByLockTime" resultType="com.rongwei.bsentity.domain.ApsProcessOperationProcessEquDo">
        select * from aps_process_operation_process_equ
        <where>
            DELETED ='0'
            <choose>
                <when test='lockMark=="y"'> and PLANSTARTDATE &lt; #{lockTime} and  LOCKMARKDETAIL='n'</when>
                <otherwise>and PLANSTARTDATE &gt; #{lockTime} and  LOCKMARKDETAIL='y'</otherwise>
            </choose>
        </where>
    </select>
    <update id="publishUnlock">
        update aps_blank_order
        <set>
            PROCESSSTATUS=if(PROCESSSTATUS='10',,PROCESSSTATUS),
            LOCKMARK='n'
        </set>
        <where>
            DELETED='0'
            AND PRODUCTSTATUS IN ('30','20')
            AND ID in
            <foreach collection="ids" item="id" open="(" close=")" separator=",">
                #{id}
            </foreach>
        </where>
    </update>
    <update id="updateFinishDateAboutProcessEqu">
        update
        aps_process_operation_process_equ apope
        join aps_process_operation apo on apo.id = apope.PROCESSID
        set
        apope.PLANSTARTDATE=IF(apope.ACTUALFINISHDATE IS NOT NULL,DATE_SUB(apope.ACTUALFINISHDATE,INTERVAL TIMESTAMPdiff(SECOND, apope.PLANSTARTDATE, apope.PLANENDDATE) SECOND), apope.PLANSTARTDATE),
        apope.PLANENDDATE=IF(apope.ACTUALFINISHDATE IS NOT NULL, apope.ACTUALFINISHDATE, apope.PLANENDDATE),
        apope.PLANENDDATE=IF(apope.ACTUALFINISHDATE IS NOT NULL, apope.PLANENDDATE, DATE_ADD(apope.ACTUALSTARTDATE,INTERVAL TIMESTAMPdiff(SECOND, apope.PLANSTARTDATE, apope.PLANENDDATE) SECOND)),
        apope.PLANSTARTDATE=IF(apope.ACTUALFINISHDATE IS NOT NULL,apope.PLANSTARTDATE,apope.ACTUALSTARTDATE)
        where
        apo.DELETED='0' and apope.deleted='0'
        AND (apope.ACTUALSTARTDATE IS NOT NULL OR apope.ACTUALFINISHDATE IS NOT NULL)
        and (apope.ACTUALSTARTDATE &lt; apope.PLANSTARTDATE OR apope.ACTUALFINISHDATE &lt; apope.PLANENDDATE)
        and apope.PLANENDDATE > #{needScheduleDate}
        and apo.PROCESS != '铸轧'
    </update>
    <update id="removeLockNextDayProcessEqu">
        with tomorroworder as(
            select DISTINCT apope.blankid
            from aps_process_operation_process_equ apope
            where apope.deleted='0'
              and DATE(apope.PLANSTARTDATE) = #{needScheduleDate}
            )
        update
            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 tomorroworder on apope.blankid=tomorroworder.blankid
            set apope.LOCKMARKDETAIL = 'n',apo.LOCKMARK='n',abo.SCHEDULINGSTATUS = '20'
        where apope.deleted='0' and apo.deleted='0'
          and apo.PROCESS != '铸轧' and apope.WORKSTATUS = '待开工'
          and apope.PLANSTARTDATE > #{needScheduleDate}
    </update>
    <update id="removeLockNextTwoDayProcessEqu">
        update
        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
        set apope.LOCKMARKDETAIL = 'n',apo.LOCKMARK='n',abo.SCHEDULINGSTATUS = '20'
        where apope.deleted='0' and apo.deleted='0'
        and apo.PROCESS != '铸轧' and apope.WORKSTATUS = '待开工'
        and apope.PLANSTARTDATE >= DATE_ADD(#{needScheduleDate},INTERVAL 1 DAY) and apope.PLANSTARTDATE &lt; DATE_ADD(#{needScheduleDate},INTERVAL 2 DAY)
    </update>
    <select id="checkConflictByBlankId" resultType="java.lang.Integer">
        select count(*) from aps_process_operation_process_equ
            <where>
                DELETED='0'
                AND BLANKID in
                <foreach collection="blankIds" item="id" open="(" close=")" separator=",">
                    #{id}
                </foreach>
                AND (CONFLICTDES is not null AND  CONFLICTDES !='' )
            </where>
    </select>
    <select id="getBlankDeliveryDate" resultType="com.rongwei.bsentity.vo.UpdateBlankDeliveryDateVo">
        SELECT
            a.*
        FROM
            (
                SELECT
                    ROW_NUMBER() OVER ( ORDER BY apope.PLANENDDATE ASC ) AS rn_asc,
                    ROW_NUMBER() OVER ( ORDER BY apope.PLANENDDATE DESC ) AS rn_desc,
                    apope.PLANENDDATE,
                    apope.ID,
                    apo1.DELIVERYTIME, abo.PROMISEDATESTART,
                    abo.PROMISEDATEEND,
                    abo.ID AS BLANKID
                FROM
                    aps_blank_order abo
                        LEFT JOIN aps_process_operation apo ON apo.BLANKID = abo.ID
                        AND abo.DELETED = '0'
                        LEFT JOIN aps_process_operation_out_mater apoom ON apoom.MAINID = apo.ID
                        AND apoom.DELETED = '0'
                        LEFT JOIN aps_process_operation_process_equ apope ON apo.ID = apope.PROCESSID
                        AND apope.DELETED = '0'
                        LEFT JOIN aps_production_order apo1 ON abo.PRODUCTIONORDERID = apo1.ID
                        AND apo1.DELETED = '0'
                WHERE
                    apo.DELETED = '0'
                  AND apope.DELETED = '0'
                  AND apoom.PLANPRODROLLNUM > 0
                  AND abo.ID = #{blankId}
            ) a
        where a.rn_asc=1 or a.rn_desc=1 order by a.PLANENDDATE
    </select>
    <select id="selectPageReport" resultType="com.rongwei.bsentity.vo.ProductPlanReportVo">
        SELECT a.* from (SELECT
            apo.*,
            abo.PROMISEDATESTART AS 'promiseDateStart',
            abo.PROMISEDATEEND AS 'promiseDateEnd',
            abo.BLANKNUMBER AS 'blankNumber',
            CONCAT(apop.OUTPUTRESULT,'=',CAST(ROUND(apop.PLANOUTPUTONEROLLWEIGHT,2) AS CHAR) + 0,'吨') AS 'outPutResult',
            CONCAT(abo.BLANKTYPE,IFNULL(abo.BLANKCOMEFROM,''),abo.INPUTREPORTDESCRIBE,' ',CAST(abo.SINGLEROLLWEIGHT AS CHAR) + 0,'吨*',abo.ROLLNUM,'卷=',CAST(ROUND(abo.SINGLEROLLWEIGHT*abo.ROLLNUM,2) AS CHAR) + 0,'吨') AS 'planBlank',
            '' AS 'processWorkShop',
            null AS 'planChangeBlankDate',
            null AS 'actualChangeBlankDate',
            '' AS 'hadStartRoll',
            b.ID AS 'lastProcessId',
            '' AS 'productLoading',
            IF(abo.PRODUCTSTATUS = '30','待开工',IF(b.PROCESSSTATUS = '50','已完工','加工中')) AS 'productionOrderStatus',
            b.PRODOUTROLL AS 'prodOutRoll',
            null AS 'planStartDate',
            null AS 'planEndDate',
            null AS 'actualStartDate',
            null AS 'actualEndDate',
            abo.REMARK AS 'blankRemark',
            abo.ID AS 'blankId',
            apop.ID AS 'processOutputId',
            apop.PRODUCTID AS 'outputOrderDetailId',
            CASE
            WHEN IFNULL( detail.ID, '' )!= ''
            AND IFNULL( detail.MERGEORDERNUMBER, '' )!= '' THEN
            ( SELECT ORDERNO FROM aps_production_merge_order WHERE ORDERNO = detail.MERGEORDERNUMBER ) ELSE (
            SELECT
            apo.ORDERNO
            FROM
            aps_blank_order abo
            LEFT JOIN aps_production_order apo ON apo.ID = abo.PRODUCTIONORDERID
            WHERE
            abo.ID = apop.BLANKID
            )
            END AS newOrderNo,
            IF
            (
            IFNULL( detail.ID, '' )!= '',
            detail.CUSTOMORDERNO,(
            SELECT
            apo.CUSTOMORDERNO
            FROM
            aps_blank_order abo
            LEFT JOIN aps_production_order apo ON apo.ID = abo.PRODUCTIONORDERID
            WHERE
            abo.ID = apop.BLANKID
            )
            ) AS newCustomOrderNo,
            CASE
            WHEN IFNULL( detail.ID, '' )!= ''
            AND IFNULL( detail.MERGEORDERNUMBER, '' )!= '' THEN
            ( SELECT CUSTOMNAME FROM aps_production_merge_order WHERE ORDERNO = detail.MERGEORDERNUMBER ) ELSE (
            SELECT
            apo.CUSTOMNAME
            FROM
            aps_blank_order abo
            LEFT JOIN aps_production_order apo ON apo.ID = abo.PRODUCTIONORDERID
            WHERE
            abo.ID = apop.BLANKID
            )
            END AS newCustomName
        FROM
            aps_production_order apo
            LEFT JOIN aps_blank_order abo ON abo.DELETED = 0 
            AND apo.ID = abo.PRODUCTIONORDERID
            LEFT JOIN aps_process_output_product apop ON apop.DELETED = 0 
            AND abo.ID = apop.BLANKID
            LEFT JOIN aps_product_detail detail ON apop.PRODUCTID = detail.ID  AND detail.DELETED = '0'
            LEFT JOIN (select apo1.ID,apo1.BLANKID,apo1.PROCESSSTATUS,apoom.OUTPUTFINISHPRODUCT,apoom.PRODOUTROLL from aps_process_operation apo1 LEFT JOIN aps_process_operation_out_mater apoom ON apoom.DELETED = 0 AND apoom.OUTPUTFINISHPRODUCT != '' AND apoom.OUTPUTFINISHPRODUCT is not null AND apo1.ID = apoom.MAINID WHERE apo1.DELETED = 0 AND apoom.OUTPUTFINISHPRODUCT != '' AND apoom.OUTPUTFINISHPRODUCT is not null) b
            ON b.OUTPUTFINISHPRODUCT = apop.OUTPUTRESULT AND b.BLANKID = apop.BLANKID
        WHERE 
            apo.DELETED = 0
            AND apop.ID is not null
            AND abo.PRODUCTSTATUS > 20
            <if test="param.startDate != '' and param.endDate != ''">
                AND abo.PROMISEDATESTART >= #{param.startDate} AND abo.PROMISEDATESTART &lt;= #{param.endDate}
            </if>
            <if test="param.blankNumber != ''">
                AND abo.BLANKNUMBER like concat('%',#{param.blankNumber},'%')
            </if>
            <if test="param.tenantId != ''">
                AND apo.TENANTID = #{param.tenantId}
            </if>
            )a
        <where>
            <if test="param.productionOrderStatus != ''">
                AND a.productionOrderStatus = #{param.productionOrderStatus}
            </if>
            <if test="param.customName != ''">
                AND a.newCustomName like concat('%',#{param.customName},'%')
            </if>
            <if test="param.customOrderNo != ''">
                AND a.newCustomOrderNo like concat('%',#{param.customOrderNo},'%')
            </if>
            <if test="param.likeSearchAll != ''">
                AND (a.newCustomName like concat('%',#{param.likeSearchAll},'%')
                OR a.newCustomOrderNo like concat('%',#{param.likeSearchAll},'%')
                OR a.blankNumber like concat('%',#{param.likeSearchAll},'%')
                )
            </if>
        </where>

        ORDER BY
            <if test="param.orders != ''">
                ${param.orders}
            </if>
            a.promiseDateStart, a.newCustomOrderNo, a.outPutResult
    </select>
    <select id="selectIsWaitGetMaterialById" resultType="java.lang.String">
        select
        IF(a.PREPAREMATERIAL = '否'
        and a.PRODUCTSTATUS = '30'
        and b.IFBLANKPROCESS = '否'
        and (IF(a.PREPAREMATERIALBATCHNO = '' || a.PREPAREMATERIALBATCHNO is null,0,(LENGTH( a.PREPAREMATERIALBATCHNO ) - LENGTH( REPLACE ( a.PREPAREMATERIALBATCHNO, ',', '' ) ) + 1)) &lt; (b.PLANPROCESSRALL-b.CANCELROLLSUM) )
        ,'是','否') AS 'ISWAITGETMATERIAL'
        from aps_blank_order a LEFT JOIN (select * from aps_process_operation where DELETED = 0 AND PREVIOUSPROCESSID is null) b ON a.ID = b.BLANKID
        where a.DELETED = 0
        AND a.ID = #{id}
    </select>
    <select id="selectPlanEndProductionWorkshopsById" resultType="java.lang.String">
        SELECT
            GROUP_CONCAT( DISTINCT abo.PLANNEDPRODUCTIONWORKSHOP )
        FROM
            aps_production_order apo
                LEFT JOIN aps_blank_order abo ON apo.ID = abo.PRODUCTIONORDERID
                AND abo.DELETED = 0
        WHERE
            apo.DELETED = 0
          AND apo.ID = #{productionorderid}
    </select>
    <select id="selectNeedRescheduleEqu"
            resultType="com.rongwei.bsentity.domain.ApsProcessOperationProcessEquDo">
        SELECT
            apope.*
        FROM
            aps_process_operation apo
                LEFT JOIN aps_process_operation_process_equ apope ON apo.ID = apope.PROCESSID
                AND apope.DELETED = 0
        WHERE
            apo.DELETED = 0
          AND apo.PROCESS NOT IN ( '铸轧', '成退', '中退', '小卷成退' )
          AND apope.PLANSTARTDATE >= DATE_SUB(#{needScheduleDate}, INTERVAL 1 DAY)
    </select>
</mapper>
