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

    <resultMap id="BaseResultMap" type="com.rongwei.bsentity.domain.ApsProcessOperationProcessEquDo">
        <id property="id" column="ID" jdbcType="VARCHAR"/>
        <result property="tenantid" column="TENANTID" jdbcType="VARCHAR"/>
        <result property="roption" column="ROPTION" jdbcType="VARCHAR"/>
        <result property="deleted" column="DELETED" jdbcType="VARCHAR"/>
        <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="ownedfactory" column="OWNEDFACTORY" jdbcType="VARCHAR"/>
        <result property="processid" column="PROCESSID" jdbcType="VARCHAR"/>
        <result property="processworkshop" column="PROCESSWORKSHOP" jdbcType="VARCHAR"/>
        <result property="processdeviceid" column="PROCESSDEVICEID" jdbcType="VARCHAR"/>
        <result property="processdevice" column="PROCESSDEVICE" jdbcType="VARCHAR"/>
        <result property="actualstartdate" column="ACTUALSTARTDATE" jdbcType="TIMESTAMP"/>
        <result property="actualfinishdate" column="ACTUALFINISHDATE" jdbcType="TIMESTAMP"/>
        <result property="closestatus" column="CLOSESTATUS" jdbcType="VARCHAR"/>
        <result property="planprocessrall" column="PLANPROCESSRALL" jdbcType="INTEGER"/>
        <result property="startingroll" column="STARTINGROLL" jdbcType="INTEGER"/>
        <result property="reportroll" column="REPORTROLL" jdbcType="INTEGER"/>
        <result property="checkoutroll" column="CHECKOUTROLL" jdbcType="INTEGER"/>
        <result property="unfinishroll" column="UNFINISHROLL" jdbcType="INTEGER"/>
        <result property="workstatus" column="WORKSTATUS" jdbcType="VARCHAR"/>
        <result property="bachmaterialprocess" column="BACHMATERIALPROCESS" jdbcType="VARCHAR"/>
        <result property="waitreportid" column="WAITREPORTID" jdbcType="VARCHAR"/>
    </resultMap>

    <sql id="Base_Column_List">
        ID,TENANTID,ROPTION,
        DELETED,REMARK,CREATEDATE,
        CREATEUSERID,MODIFYDATE,MODIFYUSERID,
        CREATEUSERNAME,MODIFYUSERNAME,OWNEDFACTORY,
        PROCESSID,PROCESSWORKSHOP,PROCESSDEVICEID,
        PROCESSDEVICE,ACTUALSTARTDATE,ACTUALFINISHDATE,
        CLOSESTATUS,PLANPROCESSRALL,STARTINGROLL,
        REPORTROLL,CHECKOUTROLL,UNFINISHROLL,
        WORKSTATUS,BACHMATERIALPROCESS,WAITREPORTID
    </sql>
    <update id="updateBachmaterialprocessByIds">
        update aps_process_operation_process_equ set BACHMATERIALPROCESS =
        CONCAT_WS(',',BACHMATERIALPROCESS,#{batchNum})
        <where>
            PROCESSID in
            <foreach collection="ids" separator="," open="(" close=")" item="id">
                #{id}
            </foreach>
        </where>
    </update>
    <update id="updateAboutCheckBatchNum">
        update aps_process_operation_process_equ apope
        <set>
            <!--  OUTPUTQUALIFIEDNUM = IFNULL(OUTPUTQUALIFIEDNUM,0)+ #{qualifiedQuantity},
             OUTPUTUNQUALIFIEDNUM = IFNULL(OUTPUTUNQUALIFIEDNUM,0) +#{unqualifiedQuantity},-->
            apope.CHECKOUTROLL = IFNULL(CHECKOUTROLL,0) +#{currentCheckNum},
            apope.UNFINISHROLL = PLANPROCESSRALL -#{currentCheckNum},
            <choose>
                <when test="previousprocessid == null and previousprocessid == ''">
                    <!-- 不存在上一个工序 未完工卷数等于0时 更新 为已完工  -->
                    <!-- 完工状态-->
#                     apope.CLOSESTATUS= CASE
#                     WHEN (apope.PLANPROCESSRALL - #{currentCheckNum}) = 0 THEN '已完工'
#                     ELSE '未完工'
#                     END,
                    <!--  作业状态-->
                    apope.WORKSTATUS= CASE
                    WHEN (apope.PLANPROCESSRALL -#{currentCheckNum}) = 0 THEN '已完工'
                    ELSE apope.WORKSTATUS
                    END,
                    <!--实际完工时间-->
                    apope.ACTUALFINISHDATE = CASE
                    WHEN (apope.PLANPROCESSRALL - #{currentCheckNum}) = 0 THEN NOW()
                    ELSE apope.ACTUALFINISHDATE
                    END
                </when>
                <otherwise>
                    <!-- 存在上一个工序 上道工序作业已完工 当前工序作业明细的已开工卷数=已检验卷数-->
<!--                    apope.CLOSESTATUS =-->
<!--                    CASE-->
<!--                    &lt;!&ndash;并且当前工序明细的已开工卷数=已检验卷数&ndash;&gt;-->
<!--                        WHEN-->
<!--                        (-->
<!--                        &lt;!&ndash;并且上道工序作业已完工状态=已完工&ndash;&gt;-->
<!--                         select (select apo.CLOSESTATUS='已完工' from aps_process_operation apo where apo.ID =#{previousprocessid} )-->
<!--                        &lt;!&ndash;并且作业明细待加工料卷批次号数量-工序作业已取消卷数<=0&ndash;&gt;-->
<!--                        and (select IFNULL(LENGTH(apope.BACHMATERIALPROCESS) - LENGTH(REPLACE(apope.BACHMATERIALPROCESS, ',',-->
<!--                        '')) + 1 ,0)-IFNULL(apo.CANCELROLL,0)&lt;=0 from aps_process_operation apo where apo.ID =#{processOperationId}-->
<!--                        ) and apope.STARTINGROLL = apope.CHECKOUTROLL+#{currentCheckNum}  from dual )THEN '已完工'-->
<!--                        ELSE '未完工'-->
<!--                    END,-->
                    apope.WORKSTATUS =
                    CASE
                        WHEN  (select (select apo.CLOSESTATUS='已完工' from aps_process_operation apo where apo.ID =#{previousprocessid} )
                    <!--并且作业明细待加工料卷批次号数量-工序作业已取消卷数<=0-->
                    and (select IFNULL(LENGTH(apope.BACHMATERIALPROCESS) - LENGTH(REPLACE(apope.BACHMATERIALPROCESS, ',',
                    '')) + 1 ,0)-IFNULL(apo.CANCELROLL,0)&lt;=0 from aps_process_operation apo where apo.ID =#{processOperationId}
                    ) and apope.STARTINGROLL = apope.CHECKOUTROLL+#{currentCheckNum}  from dual) THEN '已完工'
                        ELSE apope.WORKSTATUS
                    END,
                    apope.ACTUALFINISHDATE =  CASE
                    WHEN  (select (select apo.CLOSESTATUS='已完工' from aps_process_operation apo where apo.ID =#{previousprocessid} )
                    <!--并且作业明细待加工料卷批次号数量-工序作业已取消卷数<=0-->
                    and (select IFNULL(LENGTH(apope.BACHMATERIALPROCESS) - LENGTH(REPLACE(apope.BACHMATERIALPROCESS, ',',
                    '')) + 1 ,0)-IFNULL(apo.CANCELROLL,0)&lt;=0 from aps_process_operation apo where apo.ID =#{processOperationId}
                    ) and apope.STARTINGROLL = apope.CHECKOUTROLL+#{currentCheckNum}  from dual) THEN NOW()
                    ELSE apope.ACTUALFINISHDATE
                    END
                </otherwise>
            </choose>

        </set>
        <where>
            ID =#{id}
        </where>
    </update>

    <!--    <resultMap id="userOrdersResultMap" type="com.rongwei.bsentity.vo.ScheduleGanttVo">-->
    <!--        <id property="processworkshop" column="PROCESSWORKSHOP"/>-->
    <!--        <collection property="processList" ofType="com.rongwei.bsentity.vo.ScheduleGanttVo.ProcessVo" javaType="java.util.List">-->
    <!--            <result property="processdevice" column="PROCESSDEVICE"/>-->
    <!--            <result property="processdeviceid" column="PROCESSDEVICEID"/>-->
    <!--            <result property="planstartdate" column="PLANSTARTDATE"/>-->
    <!--            <result property="planenddate" column="PLANENDDATE"/>-->
    <!--            <result property="processids" column="PROCESSIDS"/>-->
    <!--            <result property="processdetailids" column="PROCESSDETAILIDS"/>-->
    <!--            <result property="itemname" column="ITEMNAME"/>-->
    <!--            <result property="itemdesc" column="ITEMDESC"/>-->
    <!--        </collection>-->
    <!--    </resultMap>-->


    <select id="getGanttDataList" resultType="com.rongwei.bsentity.vo.GanttVos">
        SELECT
        a4.PRODUCTNAME,
        a1.PLANPROCESSRALL,
        a2.PROCESSSTATUS,
        a1.PLANSTARTDATE,
        a1.PLANENDDATE,
        a2.ACTUALSTARTDATE,
        a2.ACTUALFINISHDATE,
        a1.PROCESSWORKSHOPID,
        a1.PROCESSWORKSHOP,
        a1.PROCESSDEVICEID,
        a1.PROCESSDEVICE,
        DATE(a6.DELIVERYDATE) AS DELIVERYDATE,
        a2.PROCESSNAME,
        a1.STARTINGROLL,
        a1.REPORTROLL,
        a1.CHECKOUTROLL,
        a1.UNFINISHROLL,
        a1.WORKSTATUS,
        a1.LOCKMARK,
        a1.HASCONFLICT,
        a1.CONFLICTDES,
        (select CUSTOMERABBREVIATION from aps_customer_management where ID =
        IF(IFNULL(a5.MERGEORDERNUMBER,'')='',a6.CUSTOMID,(select CUSTOMID from aps_production_merge_order where
        ORDERNO=a5.MERGEORDERNUMBER ) )
        and DELETED ='0') AS CUSTOMERABBREVIATION,
        a1.ID,
        a3.ID AS blankOrderId,
        a6.ID AS productionorderid,
        a6.ORDERNO,
        a6.CUSTOMNAME,
        a6.ORDERDATE,
        a7.CUSTOMERUNIT,
        a2.ID as processid,
        Date(a6.PROMISEDELIVERYDATE) as PROMISEDELIVERYDATE,
        Date(a6.SCHEDULEDELIVERYDATE) as SCHEDULEDELIVERYDATE,
        if((a6.PROMISEDELIVERYDATE is not null and DATE(a6.SCHEDULEDELIVERYDATE)> DATE(a6.PROMISEDELIVERYDATE))
        or ((a6.PROMISEDELIVERYDATE is null or DATE(a6.SCHEDULEDELIVERYDATE) &lt;= DATE(a6.PROMISEDELIVERYDATE)) and
        DATE(a6.SCHEDULEDELIVERYDATE)> DATE(a6.DELIVERYDATE)
        ),1,0) as conflict,
        a1.SOFTCONFLICTDES
        FROM
        aps_process_operation_process_equ a1
        LEFT JOIN aps_process_operation a2 ON a1.PROCESSID = a2.ID
        LEFT JOIN aps_blank_order a3 ON a3.ID = a2.BLANKID
        LEFT JOIN aps_process_output_product a4 ON a3.ID = a4.BLANKID
        LEFT JOIN aps_product_detail a5 ON a4.PRODUCTID = a5.ID
        LEFT JOIN aps_production_order a6 on a6.ID= a5.MAINID
        LEFT JOIN aps_customer_management a7 on a6.CUSTOMID= a7.ID
        <if test="productionLineIds != null and productionLineIds.size()>0">
            LEFT JOIN aps_resources_equipment ape on ape.EQUIPMENTID = a1.PROCESSDEVICEID
            LEFT JOIN aps_production_line apl on ape.MAINID =apl.ID
        </if>
        <where>
            a1.DELETED ='0'
            AND a2.DELETED = '0'
            AND a3.DELETED = '0'
            AND a4.DELETED = '0'
            AND a5.DELETED = '0'
            AND a6.DELETED='0'
            <if test="productionLineIds != null and productionLineIds.size()>0">
                AND ape.DELETED = '0'
                AND apl.DELETED = '0'
            </if>
            <if test="workshopIds != null and workshopIds.size()>0">
                and a1.PROCESSWORKSHOPID in
                <foreach collection="workshopIds" item="workshopId" open="(" close=")" separator=",">
                    #{workshopId}
                </foreach>
            </if>
            <if test="orderNos !=null and orderNos.size()>0">
                and a6.ORDERNO in
                <foreach collection="orderNos" item="orderNo" open="(" close=")" separator=",">
                    #{orderNo}
                </foreach>
            </if>
            <if test="equIds != null and equIds.size()>0">
                and a1.PROCESSDEVICEID in
                <foreach collection="equIds" item="equId" open="(" close=")" separator=",">
                    #{equId}
                </foreach>
            </if>
            <if test="productionLineIds != null and productionLineIds.size()>0">
                and apl.ID in
                <foreach collection="productionLineIds" item="productionLineId" open="(" close=")" separator=",">
                    #{productionLineId}
                </foreach>
            </if>
            <if test="factoryId != null and factoryId != '' and factoryId != 'ALL'">and a1.TENANTID = #{factoryId}</if>
            <if test="startTime != null ">and a1.PLANSTARTDATE &gt;= #{startTime}</if>
            <if test="endTime != null ">and a1.PLANENDDATE &lt;= #{endTime}</if>
            <if test="processids != null ">and a1.PROCESSID in (${processids})</if>
        </where>
        order by a1.PLANSTARTDATE asc,a1.PLANENDDATE asc
    </select>
    <select id="selectNeedUpdate" resultType="com.rongwei.bsentity.vo.CommonUpdateProductionStatusReq">
        SELECT
            apd.ID AS 'productDetailId',
            MIN( abo.PRODUCTSTATUS ) AS 'minProductionStatus',
            MAX( abo.PRODUCTSTATUS ) AS 'maxProductionStatus'
        FROM
            aps_product_detail apd
            LEFT JOIN aps_process_output_product apop ON apd.ID = apop.PRODUCTID
            AND apop.DELETED = 0
            LEFT JOIN aps_blank_order abo ON apop.BLANKID = abo.ID
            AND abo.DELETED = 0
        WHERE
            apd.DELETED = 0
            AND apd.ID IN
            <foreach collection="productIdList" open="(" close=")" separator="," item="item">
                #{item}
            </foreach>
        GROUP BY
            apd.ID,
            abo.ID
    </select>


    <sql id="appendSql">
        (SELECT apo.CLOSESTATUS = '已完工'
                   AND  IFNULL(LENGTH(apope.BACHMATERIALPROCESS) - LENGTH(REPLACE(apope.BACHMATERIALPROCESS, ',', '')) + 1 ,0)
                            -IFNULL(apo1.CANCELROLL,0)&lt;=0
                   AND apope.STARTINGROLL = apope.CHECKOUTROLL
        FROM
            aps_process_operation apo INNER JOIN
            aps_process_operation apo1
        WHERE
            apo.ID = #{data.previousprocessid}
          and apo1.ID=#{data.id})
    </sql>
    <update id="updateAboutStateByProcessId">
        <foreach collection="updateIds" item="data" separator=";">
            <if  test="data.processdetailids != null and data.processdetailids.size()>0">
                <foreach collection="data.processdetailids" item="id" >
                    update aps_process_operation_process_equ apope
                    <set>
                        <!--并且当前工序明细的已开工卷数=已检验卷数-->
                        apope.CLOSESTATUS = CASE  WHEN <include refid="appendSql"/> THEN '已完工' ELSE '未完工'  END,
                        apope.WORKSTATUS = CASE WHEN <include refid="appendSql"/> THEN '已完工' ELSE apope.WORKSTATUS  END,
                        apope.ACTUALFINISHDATE=  CASE WHEN <include refid="appendSql"/> THEN NOW() ELSE apope.ACTUALFINISHDATE END
                    </set>
                    <where>
                        apope.ID=#{id}
                        and apope.CLOSESTATUS='未完工';
                    </where>
                </foreach>
            </if>

            update aps_process_operation
            <set>
                <!--实际完工时间-->
                ACTUALFINISHDATE = (select IF(COUNT(ID) !=0 ,NULL,NOW()) from aps_process_operation_process_equ where
                PROCESSID =#{data.id} and CLOSESTATUS !='已完工'),
                <!-- 完工状态-->
                CLOSESTATUS = (select IF(COUNT(ID) !=0 ,'未完工','已完工') from aps_process_operation_process_equ where
                PROCESSID =#{data.id} and CLOSESTATUS !='已完工'),
                <!-- 作业状态-->
                WORKSTATUS = (select IF(COUNT(ID) !=0 ,'加工中','已完工') from aps_process_operation_process_equ where
                PROCESSID =#{data.id} and CLOSESTATUS !='已完工'),
                <!-- 生产状态-->
                PROCESSSTATUS = (select IF(COUNT(ID) !=0 ,'40','50') from aps_process_operation_process_equ where PROCESSID
                =#{data.id} and CLOSESTATUS !='已完工')
            </set>
            <where>
                ID =#{data.id}
                AND CLOSESTATUS !='已完工'
            </where>
        </foreach>

    </update>
    <update id="publish">
        UPDATE aps_process_operation_process_equ
        <set>
            LOCKMARK ='y',
            WORKSTATUS='待开工'
        </set>
            <where>
                BLANKID in (
                select ID from aps_blank_order where PRODUCTIONORDERID IN
                <foreach collection="productIdList" item="id" open="(" close=")" separator=",">
                    #{id}
                </foreach>
                AND DELETED='0'
                )
            </where>
    </update>
    <update id="publishCancel">
        UPDATE aps_process_operation_process_equ
        <set>
            LOCKMARK ='n',
            WORKSTATUS='待开工'
        </set>
        <where>
            BLANKID in (
            select ID from aps_blank_order where PRODUCTIONORDERID IN
            <foreach collection="productIdList" item="id" open="(" close=")" separator=",">
                #{id}
            </foreach>
            AND DELETED='0'
            )
        </where>
    </update>
    <select id="getOptionalEquipmentById" resultType="com.rongwei.bsentity.vo.OptionalEquipmentVo">
        SELECT
            CONCAT(IFNULL( aci.USEDEPTID, arg.RESOURCEWORKSHOPID ),'/',aci.ID) AS value,
            CONCAT(IFNULL( aci.USEDEPTNAME, arg.RESOURCEWORKSHOP ),'/',aci.CHECKITEMNAME) as label
        FROM
            aps_process_operation_process_equ apope
                LEFT JOIN aps_process_operation apo ON apope.PROCESSID = apo.ID
                AND apo.DELETED = '0'
                LEFT JOIN aps_process_operation_equ apoe ON apoe.MAINID = apo.ID
                AND apoe.DELETED = '0'
                LEFT JOIN asp_check_items aci ON apoe.RESOURCEID = aci.ID
                AND aci.DELETED = '0'
                LEFT JOIN aps_resource_group arg ON apoe.RESOURCEID = arg.ID
        <where>
            apope.DELETED = '0'
            <if test="idList !=null and idList.size() >0">
            AND apope.ID IN
                <foreach collection="idList" item="id" open="(" close=")" separator=",">
                    #{id}
                </foreach>
            </if>
            GROUP BY IFNULL( aci.USEDEPTID, arg.RESOURCEWORKSHOPID ),aci.ID
            <if test="idList !=null and idList.size() >1">
                <!-- 存在多选工序作业时 筛选共有的设备 -->
                <bind name="idLength" value="idList.size()" />
                HAVING count(*)>=#{idLength}
            </if>
        </where>
    </select>
    <select id="checkConflict" resultType="java.lang.String">
        select CONCAT("订单",apo1.ORDERNO,"-",
            acm.CUSTOMERABBREVIATION,"的作业-",a.PROCESSNAME,"已发布，换线后存在冲突，不能换线") from
        <foreach collection="checkConflictVos" separator="union all" item="data" open="(" close=")">(
            select
            apope. ID,apo.PROCESSNAME,
            apo.BLANKID from aps_process_operation_process_equ apope
            left join aps_process_operation apo on apope.PROCESSID =apo.ID and apo.DELETED='0'
            <where>
                apope.DELETED='0'
                AND apope.PROCESSDEVICEID =#{data.equId}
                AND (
                (#{data.planStartDate}
                &lt; apope.PLANENDDATE and #{data.planEndDate} > apope.PLANSTARTDATE) or
                (
                apope.PLANSTARTDATE &lt; #{data.planEndDate} and apope.PLANENDDATE > #{data.planStartDate})
                )
                AND apope.ID != #{data.id}
                AND apope.
                WORKSTATUS = '待开工'
                AND apo.PROCESSSTATUS IN ('30','40')
                limit 0,1
            </where>
                    )
        </foreach>
                a left join aps_blank_order abo on a.BLANKID =abo.ID AND abo.DELETED='0'
                  LEFT JOIN aps_production_order apo1 on apo1.ID=abo.PRODUCTIONORDERID and apo1.DELETED='0'
                  LEFT JOIN aps_customer_management acm on acm.ID = apo1.CUSTOMID AND acm.DELETED='0'
        <where>
            a.ID IS NOT NULL
            AND a.BLANKID is not null
            limit 0,1
        </where>
    </select>
    <select id="getConflictId" resultType="com.rongwei.bsentity.domain.ApsProcessOperationProcessEquDo">
        select * from aps_process_operation_process_equ
            <where>
                DELETED='0'
                AND ID != #{id}
                AND PROCESSDEVICEID =#{equId}
                AND (
                (#{startTime}&lt; PLANENDDATE and #{endTime} > PLANSTARTDATE)
                  or
                (PLANSTARTDATE &lt; #{endTime} and PLANENDDATE > #{startTime})
                )
            </where>
    </select>
</mapper>
