<?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="cn.ezhizao.project.business.mapper.BizProductionPlanDetailMapper">

    <resultMap type="cn.ezhizao.project.business.domain.BizProductionPlanDetail" id="BizProductionPlanDetailResult">
        <id column="id" property="id"/>
        <result column="requisition_department_id" property="requisitionDepartmentId"/>
        <result column="dept_id" property="deptId"/>
        <!--        <association property="inProduction"  column="dept_id" select="getInProduction"/>-->
        <!--        <association property="afterProduction" column="dept_id" select="getAfterProduction"/>-->
        <association property="requisitionDepartmentName" javaType="java.lang.String" column="requisition_department_id"
                     select="getRequisitionDepartmentName"/>
        <association property="pickUpQuantity" javaType="java.lang.String" column="id" select="getPickUpQuantity"/>
        <association property="lotTotalNumber" javaType="java.lang.String" column="id" select="getLotTotalNumber"/>
        <association property="superadditionTotalNumber" javaType="java.lang.Integer" column="id" select="getSuperadditionTotalNumber"/>
        <association property="firstRemain" javaType="java.lang.Integer" column="id" select="getFirstRemian" />
        <association property="issuanceDate" javaType="date" column="id" select="getIssuanceDate"/>
        <association property="regularTotalNumber" javaType="java.lang.Integer" column="id" select="getRegularTotalNumber" />
        <collection property="dayWorkList" ofType="cn.ezhizao.project.business.domain.BizDaywork"
                    select="getDayWorkList" column="id"></collection>
    </resultMap>
    <resultMap type="cn.ezhizao.project.business.domain.BizProductionPlanDetailSubDetail"
               id="BizProductionPlanDetailSubDetailResult">
        <id column="id" property="id"/>
        <collection property="dayWorkList" ofType="cn.ezhizao.project.business.domain.BizDaywork" select="getDayWork"
                    column="production_plan_detail_id"></collection>
    </resultMap>
    <select id="getIssuanceDate" resultType="date">
        select issuance_date from biz_production_plan_detail_sub_detail where production_plan_detail_id = #{id} and
        deleted = 0 order by issuance_date limit 1
    </select>
    <select id="getRequisitionDepartmentName" resultType="java.lang.String">
        select dept_name from sys_dept where dept_id = #{requisitionDepartmentId}
    </select>
    <select id="getDayWorkList" resultType="cn.ezhizao.project.business.domain.BizDaywork">
        select * from biz_daywork where production_plan_detail_id = #{id}
    </select>
    <select id="getDayWork" resultType="cn.ezhizao.project.business.domain.BizDaywork">
        select * from biz_daywork where production_plan_detail_id = #{production_plan_detail_id}
    </select>
    <select id="getRegularTotalNumber" resultType="java.lang.Integer">
        select count(1) from biz_daywork t1 left join biz_lot t2 on t1.lot_id = t2.id where t1.deleted = 0 and
        t2.deleted = 0 and t1.production_plan_detail_id = #{id} and t2.is_waste_recycling = 0 and t2.from_id = 0
        and t2.is_superaddition = 0
    </select>
    <!--<select id="getDayWorkLot" resultType="Integer" >-->
    <!--    select count(1) FROM biz_daywork where production_plan_detail_id = #{id}-->
    <!--</select>-->
    <select id="getList" resultMap="BizProductionPlanDetailResult">
        SELECT t.* FROM biz_production_plan_detail t
        <trim prefix=" WHERE" suffix="" suffixOverrides="AND">
            t.deleted = 0
            <if test="keywords != null and keywords !=''">AND t.`product_description` LIKE CONCAT('%', #{keywords}, '%')
            </if>
            <if test="keyword != null and keyword !=''">AND (t.`product_description` LIKE CONCAT('%', #{keyword}, '%')
                or t.`production_plan_no` LIKE CONCAT('%', #{keyword}, '%'))
            </if>
            <if test="companyAlias != null ">AND t.company_alias LIKE CONCAT('%', #{companyAlias}, '%')
            </if>
            <if test="drawingNumber != null ">AND t.drawing_number LIKE CONCAT('%', #{drawingNumber}, '%')
            </if>
            <if test="specification != null ">AND t.specification LIKE CONCAT('%', #{specification}, '%')
            </if>
            <if test="productDescription != null ">AND t.product_description LIKE CONCAT('%', #{productDescription}, '%')
            </if>
            <if test="companyAlias != null ">AND t.company_alias LIKE CONCAT('%', #{companyAlias}, '%')
            </if>
            <if test="productionPlanNo != null and productionPlanNo !=''">AND t.`production_plan_no` LIKE CONCAT('%',
                #{productionPlanNo}, '%')
            </if>
            <if test="tenantId != null  and tenantId != 0">AND tenant_id = #{tenantId}</if>
            <!--            <if test="deptId != null"> AND exists (select 1 from biz_production_plan_detail_sub_detail t1 where t1.dept_id = #{deptId} and t1.production_plan_detail_id = t.id) </if>-->
            <if test="supplierId != null">
                <!-- 外协商配置的产品 -->
                AND exists (select 1 from biz_supplier_product sp where sp.deleted = 0 and sp.supplier_id =
                #{supplierId} and sp.product_id = t.product_id)
            </if>
        </trim>
        ORDER BY id DESC
    </select>
    <select id="getSuperadditionTotalNumber" resultType="java.lang.Integer">
        select count(1) from biz_lot where deleted = 0 and is_superaddition = 1 and production_plan_detail_id = #{id}
    </select>
    <!--    <select id="getInProduction"  resultType="java.lang.Integer">-->
    <!--        select COUNT(*) inProduction from biz_daywork where deleted = 0 and dept_id = #{dept_id} and id in (select daywork_id from biz_daywork_item where deleted = 0 and status &lt; 3 and production_plan_detail_id = #{id})-->
    <!--    </select>-->
    <!--    <select id="getAfterProduction"  resultType="java.lang.Integer">-->
    <!--        select COUNT(*) afterProduction from biz_daywork where deleted = 0 and dept_id = #{dept_id} and id in (select daywork_id from biz_daywork_item where deleted = 0 and status = 3 and production_plan_detail_id = #{id})-->
    <!--    </select>-->
    <select id="reportList" resultMap="BizProductionPlanDetailResult">
        SELECT
        t1.production_plan_no,
        t1.company_alias,
        t2.dept_name as currentDepartment,
        t2.dept_id as currentDeptId,
        t1.line_number,
        t1.technology_version,
        t1.product_description,
        t1.total_lot_number,
        t1.id
        FROM
        biz_daywork t
        LEFT JOIN biz_production_plan_detail t1 ON t1.id = t.production_plan_detail_id
        LEFT JOIN sys_dept t2 ON t2.dept_id = t.dept_id
        <trim prefix=" WHERE" suffix="" suffixOverrides="AND">
            t.deleted = 0 and t1.deleted = 0 and t2.del_flag = 0
            <if test="keywords != null and keywords !=''">AND t1.product_description LIKE CONCAT('%', #{keywords}, '%')
            </if>
            <if test="productionPlanNo != null and productionPlanNo !=''">AND t1.production_plan_no LIKE CONCAT('%',
                #{productionPlanNo}, '%')
            </if>
            <if test="deptId != null and deptId !=0">AND t2.dept_id = #{deptId}</if>
        </trim>
        GROUP BY
        t.dept_id,
        t1.id
        order by (select biz_daywork_item.create_time from biz_daywork_item left join biz_daywork on biz_daywork.id =
        biz_daywork_item.daywork_id where biz_daywork_item.deleted = 0 and biz_daywork_item.production_plan_detail_id =
        t1.id order by biz_daywork_item.create_time limit 1 ) ASC
    </select>
    <!--    <select id="reportList" resultMap="BizProductionPlanDetailResult">-->
    <!--        SELECT DISTINCT *, (-->
    <!--        select COUNT(*) inProduction from biz_daywork where deleted = 0 AND IF(COALESCE(#{deptId}, 0) > 0, dept_id =-->
    <!--        #{deptId}, TRUE) and production_plan_detail_id = biz_production_plan_detail.id-->
    <!--        <if test="inDayworkIds != null and inDayworkIds.size > 0">-->
    <!--            and id in-->
    <!--            <foreach collection="inDayworkIds" index="index" item="id" separator="," open="(" close=")">-->
    <!--                #{id,jdbcType=BIGINT}-->
    <!--            </foreach>-->
    <!--        </if>-->
    <!--        ) inProduction,-->
    <!--        (-->
    <!--        select COUNT(*) afterProduction from biz_daywork where deleted = 0 AND IF(COALESCE(#{deptId}, 0) > 0, dept_id =-->
    <!--        #{deptId}, TRUE) and production_plan_detail_id = biz_production_plan_detail.id-->
    <!--        <if test="afterDayworkIds != null and afterDayworkIds.size > 0">-->
    <!--            and id in-->
    <!--            <foreach collection="afterDayworkIds" index="index" item="id" separator="," open="(" close=")">-->
    <!--                #{id,jdbcType=BIGINT}-->
    <!--            </foreach>-->
    <!--        </if>-->
    <!--        ) afterProduction,-->
    <!--        (select dept_name from sys_dept where dept_id = (select dept_id from biz_daywork_item where deleted = 0 and-->
    <!--        production_plan_detail_id = biz_production_plan_detail.id AND IF(COALESCE(#{deptId}, 0) > 0, dept_id =-->
    <!--        #{deptId}, TRUE) order by create_time desc limit 0,1 )) currentDepartment-->
    <!--        FROM biz_production_plan_detail-->
    <!--        <trim prefix=" WHERE" suffix="" suffixOverrides="AND">-->
    <!--            deleted = 0-->
    <!--            <if test="keywords != null and keywords !=''">AND `product_description` LIKE CONCAT('%', #{keywords}, '%')-->
    <!--            </if>-->
    <!--            <if test="productionPlanNo != null and productionPlanNo !=''">AND `production_plan_no` LIKE CONCAT('%',-->
    <!--                #{productionPlanNo}, '%')-->
    <!--            </if>-->
    <!--            <if test="tenantId != null  and tenantId != 0">AND tenant_id = #{tenantId}</if>-->
    <!--            <if test="inDayworkIds != null or afterDayworkIds != null">-->
    <!--                AND (-->
    <!--                <if test="inDayworkIds !=null and inDayworkIds.size > 0"> exists (select 1 from biz_daywork where id-->
    <!--                    in-->
    <!--                    <foreach collection="inDayworkIds" index="index" item="id" separator="," open="(" close=")">-->
    <!--                        #{id,jdbcType=BIGINT}-->
    <!--                    </foreach>-->
    <!--                    and biz_production_plan_detail.id = biz_daywork.production_plan_detail_id)-->
    <!--                </if>-->
    <!--                 or-->
    <!--                <if test="afterDayworkIds !=null and afterDayworkIds.size > 0"> exists (select 1 from biz_daywork-->
    <!--                    where id in-->
    <!--                    <foreach collection="afterDayworkIds" index="index" item="id" separator="," open="(" close=")">-->
    <!--                        #{id,jdbcType=BIGINT}-->
    <!--                    </foreach>-->
    <!--                    and biz_production_plan_detail.id = biz_daywork.production_plan_detail_id)-->
    <!--                </if>-->
    <!--                )-->
    <!--            </if>-->
    <!--        </trim>-->
    <!--        ORDER BY id DESC-->
    <!--    </select>-->
    <!--    <select id="getInfo" resultMap="BizProductionPlanDetailSubDetailResult">-->
    <!--        SELECT t1.*,t2.line_number as lineNumber,t2.product_description as productDescription FROM biz_production_plan_detail_sub_detail t1 left join biz_production_plan_detail t2 on t1.production_plan_detail_id = t2.id-->
    <!--        <trim prefix=" WHERE" suffix="" suffixOverrides="AND">-->
    <!--            t1.deleted = 0 and t2.deleted = 0-->
    <!--            <if test="deptId != null  and deptId != 0"> AND (t1.production_plan_detail_id in (SELECT production_plan_detail_id from biz_daywork where deleted = 0 and dept_id = #{deptId}) or requisition_department_id = #{deptId}) </if>-->
    <!--            <if test="keywords != null and keywords !=''"> AND `product_description` LIKE CONCAT('%', #{keywords}, '%')</if>-->
    <!--            <if test="productionPlanNo != null and productionPlanNo !=''"> AND t1.production_plan_no LIKE CONCAT('%', #{productionPlanNo}, '%')</if>-->
    <!--            <if test="productDescription!= null and productDescription !=''"> AND t2.product_description LIKE CONCAT('%', #{productDescription}, '%')</if>-->
    <!--            <if test="tenantId != null  and tenantId != 0"> AND t1.tenant_id = #{tenantId}</if>-->
    <!--        </trim>-->
    <!--        ORDER BY create_time DESC-->
    <!--    </select>-->
    <select id="getInfo" resultMap="BizProductionPlanDetailResult">
        SELECT DISTINCT t1.*
        FROM biz_production_plan_detail t1
        LEFT JOIN biz_production_plan_detail_sub_detail t2 ON t1.id = t2.production_plan_detail_id
        <trim prefix=" WHERE" suffix="" suffixOverrides="AND">
            t1.deleted = 0 and t2.deleted = 0 and t1.id not in (SELECT production_plan_detail_id from
            biz_production_resource_group_sub_plan where deleted = 0 and resource_group_id = #{resourceGroupId})
            <!--            生产计划进行投产，有生产子计划，且领料部门id等于工段id ；4-18修改看当前生产计划的批次里废品回用的批次是否是当前id-->
            <if test="deptId != null  and deptId != 0">AND (t1.id in (SELECT production_plan_detail_id from
                biz_daywork_item
                where deleted = 0 and dept_id = #{deptId}) or requisition_department_id = #{deptId}
                OR (t1.id IN (
                SELECT production_plan_detail_id
                FROM biz_lot
                WHERE production_plan_detail_id = t1.id
                AND is_waste_recycling = 1
                AND requisition_department_id = #{deptId}
                )))
            </if>
            <if test="keywords != null and keywords !=''">AND t1.product_description LIKE CONCAT('%', #{keywords},
                '%')
            </if>
            <if test="productionPlanNo != null and productionPlanNo !=''">AND t1.production_plan_no LIKE CONCAT('%',
                #{productionPlanNo}, '%')
            </if>
            <if test="productDescription!= null and productDescription !=''">AND t1.product_description LIKE CONCAT('%',
                #{productDescription}, '%')
            </if>
            <if test="tenantId != null  and tenantId != 0">AND t1.tenant_id = #{tenantId}</if>
        </trim>
        ORDER BY create_time DESC
    </select>
    <select id="getTechnologicalVersion" resultType="cn.ezhizao.project.business.domain.BizTechnologicalProcess">
        select id as value,technology_version as label from biz_technological_process where deleted = 0 and product_id =
        #{productId}
    </select>
    <select id="getPickUpQuantity" resultType="java.lang.Double">
        SELECT sum(production_quantity) as pickUpQuantity from biz_production_plan_detail_sub_detail where
        production_plan_detail_id = #{id} and deleted = 0
    </select>

    <select id="getLotTotalNumber" resultType="java.lang.Double">
        SELECT sum(lot_number) as lotTotalNumber from biz_production_plan_detail_sub_detail where
        production_plan_detail_id = #{id} and deleted = 0
    </select>
    <select id="getFirstRemian" resultType="java.lang.Integer">
        select ifnull((SELECT sum(lot_number) as lotTotalNumber from biz_production_plan_detail_sub_detail where
        production_plan_detail_id = #{id} and deleted = 0), 0) - ifnull((select count(1) from biz_outsourced_order_detail where deleted = 0 and production_plan_detail_id = #{id}), 0)

    </select>
    <update id="updateLotCodeStatus">
        UPDATE biz_production_plan_detail SET lot_code_status_code = #{lotCodeStatusCode} WHERE id = #{id}
    </update>

    <select id="selectSpecialFirstProductDetail" resultMap="BizProductionPlanDetailResult">
        SELECT * FROM biz_production_plan_detail
        <trim prefix=" WHERE" suffix="" suffixOverrides="AND">
            deleted=0 AND
            id IN (
            SELECT production_plan_detail_id FROM biz_production_plan_detail_sub_detail
            <trim prefix=" WHERE" suffix="" suffixOverrides="AND">
                deleted=0
                <if test="deptId != null">
                    AND dept_id = #{deptId}
                </if>
            </trim>
            )
            <if test="productDescription != null and productDescription !=''">
                AND product_description LIKE CONCAT('%', #{productDescription}, '%')
            </if>
            <if test="productionPlanNo != null and productionPlanNo !=''">
                 AND production_plan_no LIKE CONCAT('%',#{productionPlanNo}, '%')
            </if>
        </trim>
    </select>



    <delete id="physicalDelete">
        DELETE FROM biz_production_plan_detail
        <trim prefix=" WHERE" suffix="" suffixOverrides="AND">
            <if test="id != null">
                id = #{id} AND
            </if>
            <!-- 删除条件为其他外键可以在这里加 -->
        </trim>
    </delete>


    <select id="getListForOutsource" resultMap="BizProductionPlanDetailResult">
        SELECT t.* FROM biz_production_plan_detail t
        <trim prefix=" WHERE" suffix="" suffixOverrides="AND">
            t.deleted = 0
            <if test="keywords != null and keywords !=''">AND t.`product_description` LIKE CONCAT('%', #{keywords}, '%')
            </if>
            <if test="keyword != null and keyword !=''">AND (t.`product_description` LIKE CONCAT('%', #{keyword}, '%')
                or t.`production_plan_no` LIKE CONCAT('%', #{keyword}, '%'))
            </if>
            <if test="productionPlanNo != null and productionPlanNo !=''">AND t.`production_plan_no` LIKE CONCAT('%',
                #{productionPlanNo}, '%')
            </if>
            <if test="tenantId != null  and tenantId != 0">AND tenant_id = #{tenantId}</if>
            <if test="deptId != null">AND exists (select 1 from biz_production_plan_detail_sub_detail t1 where
                t1.dept_id = #{deptId} and t1.production_plan_detail_id = t.id)
            </if>
            <if test="supplierId != null">
                <!-- 外协商配置的产品 -->
                AND exists (select 1 from biz_supplier_product sp where sp.deleted = 0 and sp.supplier_id =
                #{supplierId} and sp.product_id = t.product_id)
            </if>
            <if test="remainLot == true">
                AND (ifnull((SELECT sum(lot_number) as lotTotalNumber from biz_production_plan_detail_sub_detail where
                production_plan_detail_id = t.id and deleted = 0), 0) - ifnull((select count(1) from biz_outsourced_order_detail where deleted = 0 and production_plan_detail_id = t.id), 0)) > 0
            </if>
        </trim>
    </select>
</mapper>
