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

    <resultMap type="cn.ezhizao.project.business.domain.BizProductionResourceGroupSubPlan" id="BizProductionResourceGroupSubPlanResult">
        <id column="id" property="id"/>
    </resultMap>


    <select id="getList" parameterType="BizProductionResourceGroupSubPlan" resultMap="BizProductionResourceGroupSubPlanResult">
        SELECT
        t1.id,
        t2.description as productDescription,
        t3.production_plan_no as productionPlanNo,
        t3.company_alias as companyAlias,
        t3.id as productionPlanDetailId,
        t3.line_number as lineNumber,
        (SELECT issuance_date FROM biz_production_plan_detail_sub_detail WHERE production_plan_detail_id = t3.id AND deleted = 0 ORDER BY issuance_date LIMIT 1) as issuanceDate,
        (SELECT SUM(lot_number) as lotTotalNumber FROM biz_production_plan_detail_sub_detail WHERE production_plan_detail_id = t3.id AND deleted = 0) as lotTotalNumber
        FROM biz_production_resource_group_sub_plan t1
        LEFT JOIN biz_product t2 ON t1.product_id = t2.id
        LEFT JOIN biz_production_plan_detail t3 ON t3.id = t1.production_plan_detail_id
        <trim prefix=" WHERE" suffix="" suffixOverrides="AND">
            t1.deleted = 0 and t2.deleted = 0 and t3.deleted = 0
            <if test="resourceGroupId != null and resourceGroupId != ''"> AND t1.resource_group_id = #{resourceGroupId}</if>
            <if test="productionPlanNo != null and productionPlanNo != ''"> AND (t3.production_plan_no LIKE CONCAT('%', #{productionPlanNo}, '%') or t2.description LIKE CONCAT('%', #{productionPlanNo}, '%') or t3.company_alias LIKE CONCAT('%', #{productionPlanNo}, '%'))</if>
            <if test="productionPlanDetailId != null and productionPlanDetailId != ''"> AND t1.production_plan_detail_id = #{productionPlanDetailId}</if>
            <if test="deptId != null and deptId != ''"> AND t1.dept_id = #{deptId}</if>
        </trim>
        ORDER BY t1.create_time DESC
    </select>

    <select id="getAotList" parameterType="BizProductionResourceGroupSubPlan" resultMap="BizProductionResourceGroupSubPlanResult">
        SELECT
        t1.id,
        t2.description as productDescription,
        t3.production_plan_no as productionPlanNo,
        t3.company_alias as companyAlias,
        t3.id as productionPlanDetailId,
        t3.line_number as lineNumber,
        (SELECT issuance_date FROM biz_production_plan_detail_sub_detail WHERE production_plan_detail_id = t3.id AND deleted = 0 ORDER BY issuance_date LIMIT 1) as issuanceDate,
        (SELECT SUM(lot_number) as lotTotalNumber FROM biz_production_plan_detail_sub_detail WHERE production_plan_detail_id = t3.id AND deleted = 0) as lotTotalNumber
        FROM biz_production_resource_group_sub_plan t1
        LEFT JOIN biz_product t2 ON t1.product_id = t2.id
        LEFT JOIN biz_production_plan_detail t3 ON t3.id = t1.production_plan_detail_id
        <trim prefix=" WHERE" suffix="" suffixOverrides="AND">
            t1.deleted = 0 and t2.deleted = 0 and t3.deleted = 0
            and t1.resource_group_id IN (SELECT id FROM biz_production_resource_group WHERE deleted = 0 and is_deactivate =0 and id =  t1.resource_group_id)
            <if test="resourceGroupId != null and resourceGroupId != ''"> AND t1.resource_group_id = #{resourceGroupId}</if>
            <if test="productionPlanNo != null and productionPlanNo != ''"> AND (t3.production_plan_no LIKE CONCAT('%', #{productionPlanNo}, '%') or t2.description LIKE CONCAT('%', #{productionPlanNo}, '%') or t3.company_alias LIKE CONCAT('%', #{productionPlanNo}, '%'))</if>
            <if test="productionPlanDetailId != null and productionPlanDetailId != ''"> AND t1.production_plan_detail_id = #{productionPlanDetailId}</if>
            <if test="deptId != null and deptId != ''"> AND t1.dept_id = #{deptId}</if>
        </trim>
    </select>



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