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

    <resultMap type="cn.ezhizao.project.business.domain.BizProductionPlanDetailSubDetail"
               id="BizProductionPlanDetailSubDetailResult">
        <id column="id" property="id"/>
        <result column="technological_process_id" property="technologicalProcessId"/>
        <result column="production_plan_detail_id" property="productionPlanDetailId"/>
        <result column="product_id" property="productId"/>
        <association property="productDescription" javaType="java.lang.String" column="product_id"
                     select="getProductDescription"/>
        <association property="drawingNumber" javaType="java.lang.String" column="production_plan_detail_id"
                     select="getDrawingNumber"/>
        <collection column="id" property="finishProcess"
                    ofType="cn.ezhizao.project.business.domain.BizTechnologicalProcessDetail"
                    select="getFinishProcess"/>
    </resultMap>

    <resultMap type="cn.ezhizao.project.business.domain.BizTechnologicalProcessDetail"
               id="BizTechnologicalProcessResult">
        <id column="id" property="id"/>
    </resultMap>
    <select id="getProductDescription" resultType="java.lang.String">
        select description as productDescription from biz_product where deleted = 0 and id = #{product_id}
    </select>
    <select id="getDrawingNumber" resultType="java.lang.String">
        select drawing_number as drawingNumber from biz_production_plan_detail where deleted = 0 and id =
        #{production_plan_detail_id}
    </select>
    <select id="getFinishProcess" resultMap="BizTechnologicalProcessResult">
        select
        *
        from biz_technological_process_detail
        where deleted = 0 and technological_process_id = #{technologicalProcessId}
        and process_id in
        (select process_id from biz_daywork_item where status >5 and deleted = 0 and daywork_id = (select id from
        biz_daywork where deleted = 0 and production_plan_detail_sub_detail_id = #{id}))
        order by process_step_number
    </select>

    <select id="getList" parameterType="BizProductionPlanDetailSubDetail"
            resultMap="BizProductionPlanDetailSubDetailResult">
        SELECT t.*, t2.product_description, t2.one_lot_quantity FROM biz_production_plan_detail_sub_detail t left join
        biz_production_plan_detail t2 on t2.id = t.production_plan_detail_id
        <trim prefix=" WHERE" suffix="" suffixOverrides="AND">
            t.deleted = 0
            <if test="productionPlanDetailId != null">AND t.production_plan_detail_id = #{productionPlanDetailId}</if>
            <if test="lotNumber != null ">AND t.lot_number = #{lotNumber}</if>
            <if test="productionPlanNo != null ">AND t.production_plan_no like concat('%', #{productionPlanNo}, '%')
            </if>
            <if test="tenantId != null and tenantId != 0">AND t.tenant_id = #{tenantId}</if>
            <if test="keyword != null">AND (t.production_plan_no like #{keyword} or t2.product_description like
                #{keyword})
            </if>
            <!--            <if test="deptId != null"> AND t.dept_id = #{deptId} </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 create_time DESC
    </select>

    <!-- 根据关键字获取列表 -->
    <select id="getListByKeywords" parameterType="String" resultMap="BizProductionPlanDetailSubDetailResult">
        SELECT
        t1.*,
        t2.specification AS product_specification,
        t2.drawing_number,
        t2.company_alias,
        t2.description AS product_description
        FROM biz_production_plan_detail_sub_detail t1
        INNER JOIN biz_product t2 ON t1.product_id=t2.id
        <trim prefix=" WHERE" suffix="" suffixOverrides="AND">
            t1.deleted = 0
            <if test="keywords != null and keywords != ''">AND t2.description LIKE CONCAT('%', #{keywords}, '%')</if>
            <if test="deptId !=null and deptId !=''">AND t1.dept_id = #{deptId}</if>
            <if test="productDescription !=null and productDescription !=''">AND t2.description LIKE CONCAT('%',
                #{productDescription}, '%')
            </if>
            <if test="tenantId != null  and tenantId != 0">AND t1.tenant_id = #{tenantId}</if>
        </trim>
        ORDER BY t1.create_time DESC
    </select>

    <delete id="physicalDelete">
        DELETE FROM biz_production_plan_detail_sub_detail
        <trim prefix=" WHERE" suffix="" suffixOverrides="AND">
            <if test="id != null">
                id = #{id} AND
            </if>
            <!-- 删除条件为其他外键可以在这里加 -->
        </trim>
    </delete>
    <select id="getListForOutsource" parameterType="BizProductionPlanDetailSubDetail"
            resultMap="BizProductionPlanDetailSubDetailResult">
        SELECT t.*, t2.product_description, t2.one_lot_quantity FROM biz_production_plan_detail_sub_detail t left join
        biz_production_plan_detail t2 on t2.id = t.production_plan_detail_id
        <trim prefix=" WHERE" suffix="" suffixOverrides="AND">
            t.deleted = 0
            <if test="productionPlanDetailId != null">AND t.production_plan_detail_id = #{productionPlanDetailId}</if>
            <if test="lotNumber != null ">AND t.lot_number = #{lotNumber}</if>
            <if test="productionPlanNo != null ">AND t.production_plan_no like concat('%', #{productionPlanNo}, '%')
            </if>
            <if test="tenantId != null and tenantId != 0">AND t.tenant_id = #{tenantId}</if>
            <if test="keyword != null">AND (t.production_plan_no like #{keyword} or t2.product_description like
                #{keyword})
            </if>
            <if test="deptId != null">AND t.dept_id = #{deptId}</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 create_time DESC
    </select>
</mapper>
