<?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.ximai.mes.pro.mapper.productionSolution.ProProductionSolutionMapper">

    <resultMap type="com.ximai.mes.pro.domain.productionSolution.ProProductionSolution" id="ProProductionSolutionResult">
        <result property="productionSolutionId" column="production_solution_id"/>
        <result property="productionSolutionCode" column="production_solution_code"/>
        <result property="productionSolutionName" column="production_solution_name"/>
        <result property="productionSolutionType" column="production_solution_type"/>
        <result property="itemId" column="item_id"/>
        <result property="itemCode" column="item_code"/>
        <result property="sapItemCode" column="sap_item_code"/>
        <result property="itemName" column="item_name"/>
        <result property="routeId" column="route_id"/>
        <result property="routeName" column="route_name"/>
        <result property="usageId" column="usage_id"/>
        <result property="usageName" column="usage_name"/>
        <result property="remark" column="remark"/>
        <result property="createBy" column="create_by"/>
        <result property="createTime" column="create_time"/>
        <result property="updateBy" column="update_by"/>
        <result property="updateTime" column="update_time"/>
        <result property="sapItemCode" column="sap_item_code"/>
        <result property="factoryCode" column="factory_code"/>
        <result property="groupKey" column="group_key"/>
        <result property="groupCounter" column="group_counter"/>
        <result property="materialsGroupBill" column="materials_group_bill"/>
        <result property="alternativeBill" column="alternative_bill"/>
        <result property="startDate" column="start_date"/>
        <result property="expiryDate" column="expiry_date"/>
        <result property="maxLot" column="max_lot"/>
        <result property="minLot" column="min_lot"/>
        <result property="status" column="status"/>
        <result property="unitOfMeasure" column="unit_of_measure"/>
        <result property="ecnCode" column="ecn_code"/>
        <result property="sapUsageId" column="sap_usage_id"/>
        <result property="sapPrototypeMakeCode" column="sap_prototype_make_code"/>

    </resultMap>

    <sql id="selectProProductionSolutionVo">
        select pps.production_solution_id,
               pps.production_solution_code,
               pps.production_solution_name,
               pps.production_solution_type,
               pps.sap_prototype_make_code,
               pps.sap_item_code,
               pps.item_id,
               mi.item_name,
               mi.item_code,
               pps.route_id,
               pr.route_name,
               pps.usage_id,
               pu.usage_name,
               pps.remark,
               pps.create_by,
               pps.create_time,
               pps.update_by,
               pps.update_time,
               pps.sap_item_code,
               pps.factory_code,
               CONCAT(group_key, group_counter) as group_counter,
               pps.alternative_bill,
               pps.materials_group_bill,
               pps.status,
               pps.start_date,
               pps.expiry_date,
               pps.max_lot,
               pps.min_lot,
               pps.unit_of_measure,
               pps.ecn_code,
               pps.sap_usage_id,
               pps.unit_Price,
               pps.surpass_state,
               pps.url,
               pps.file_path,
               pps.original_filename
        from pro_production_solution pps
                 left join md_item mi on pps.item_id = mi.item_id
                 left join pro_route pr on pr.route_id = pps.route_id
                 left join pro_usage pu on pu.usage_id = pps.usage_id
    </sql>

    <select id="selectProProductionSolutionList" parameterType="ProProductionSolution"
            resultMap="ProProductionSolutionResult">
        <include refid="selectProProductionSolutionVo"/>
        <where>
            <if test="productionSolutionCode != null  and productionSolutionCode != ''">and pps.production_solution_code
                = #{productionSolutionCode}
            </if>
            <if test="productionSolutionName != null  and productionSolutionName != ''">and pps.production_solution_name
                like concat('%', #{productionSolutionName}, '%')
            </if>
            <if test="itemId != null ">and pps.item_id = #{itemId}</if>
            <if test="sapItemCode != null and sapItemCode != ''">and pps.sap_item_code like concat('%', #{sapItemCode}, '%')</if>
            <if test="itemName != null and itemName != ''">and mi.item_name like concat('%', #{itemName}, '%')</if>
            <if test="itemCode != null and itemCode != ''">and mi.item_code like concat('%', #{itemCode}, '%')</if>
            <if test="sapPrototypeMakeCode != null and sapPrototypeMakeCode != ''">and pps.sap_prototype_make_code
                like concat('%', #{sapPrototypeMakeCode}, '%')
            </if>
            <if test="groupCounter != null and groupCounter != ''">and CONCAT(group_key,group_counter) like concat('%',
                #{groupCounter}, '%')
            </if>
            <if test="routeId != null ">and pps.route_id = #{routeId}</if>
            <if test="usageId != null ">and pps.usage_id = #{usageId}</if>
        </where>
    </select>

    <select id="selectProProductionSolutionByProductionSolutionId" parameterType="Long"
            resultMap="ProProductionSolutionResult">
        <include refid="selectProProductionSolutionVo"/>
        where pps.production_solution_id = #{productionSolutionId}
    </select>


    <select id="selectByGroupKeyAndGroupCounterAndSapItemCode"
            resultMap="ProProductionSolutionResult">
        <include refid="selectProProductionSolutionVo"/>
        where pps.group_key = #{groupKey}
        and pps.group_counter = #{groupCounter}
        and pps.sap_item_code = #{sapItemCode}
    </select>

    <select id="selectProProductionSolutionBySapPrototypeMakeCode"
            resultMap="ProProductionSolutionResult">
        <include refid="selectProProductionSolutionVo"/>
        where pps.sap_prototype_make_code = #{sapPrototypeMakeCode}

    </select>
    <select id="selectProductionSolutionCode"
            resultType="com.ximai.mes.pro.domain.productionSolution.ProProductionSolution">
        select * from pro_production_solution
        <where>
            <if test="productionSolutionCode != null">
                production_solution_code = #{productionSolutionCode}
            </if>
            and  item_id = #{itemId}
        </where>
        order by production_solution_code desc

    </select>


    <insert id="insertProProductionSolution" parameterType="ProProductionSolution" useGeneratedKeys="true"
            keyProperty="productionSolutionId">
        insert into pro_production_solution
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="productionSolutionCode != null">production_solution_code,</if>
            <if test="productionSolutionName != null">production_solution_name,</if>
            <if test="productionSolutionType != null">production_solution_type,</if>
            <if test="itemId != null">item_id,</if>
            <if test="routeId != null">route_id,</if>
            <if test="usageId != null">usage_id,</if>
            <if test="remark != null">remark,</if>
            <if test="createBy != null">create_by,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateBy != null">update_by,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="sapItemCode != null">sap_item_code,</if>
            <if test="factoryCode != null">factory_code,</if>
            <if test="groupKey != null">group_key,</if>
            <if test="groupCounter != null">group_counter,</if>
            <if test="materialsGroupBill != null">materials_group_bill,</if>
            <if test="alternativeBill != null">alternative_bill,</if>
            <if test="startDate != null">start_date,</if>
            <if test="expiryDate != null">expiry_date,</if>
            <if test="maxLot != null">max_lot,</if>
            <if test="minLot != null">min_lot,</if>
            <if test="status != null">status,</if>
            <if test="ecnCode != null">ecn_code,</if>
            <if test="unitOfMeasure != null">unit_of_measure,</if>
            <if test="sapUsageId != null">sap_usage_id,</if>
            <if test="sapPrototypeMakeCode != null">sap_prototype_make_code,</if>
            <if test="unitPrice != null">unit_price,</if>
            <if test="surpassState != null">surpass_state,</if>
            <if test="url != null">url,</if>
            <if test="filePath != null">file_path,</if>
            <if test="originalFilename != null">original_filename,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="productionSolutionCode != null">#{productionSolutionCode},</if>
            <if test="productionSolutionName != null">#{productionSolutionName},</if>
            <if test="productionSolutionType != null">#{productionSolutionType},</if>
            <if test="itemId != null">#{itemId},</if>
            <if test="routeId != null">#{routeId},</if>
            <if test="usageId != null">#{usageId},</if>
            <if test="remark != null">#{remark},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateBy != null">#{updateBy},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="sapItemCode != null">#{sapItemCode},</if>
            <if test="factoryCode != null">#{factoryCode},</if>
            <if test="groupKey != null">#{groupKey},</if>
            <if test="groupCounter != null">#{groupCounter},</if>
            <if test="materialsGroupBill != null">#{materialsGroupBill},</if>
            <if test="alternativeBill != null">#{alternativeBill},</if>
            <if test="startDate != null">#{startDate},</if>
            <if test="expiryDate != null">#{expiryDate},</if>
            <if test="maxLot != null">#{maxLot},</if>
            <if test="minLot != null">#{minLot},</if>
            <if test="status != null">#{status},</if>
            <if test="ecnCode != null">#{ecnCode},</if>
            <if test="unitOfMeasure != null">#{unitOfMeasure},</if>
            <if test="sapUsageId != null">#{sapUsageId},</if>
            <if test="sapPrototypeMakeCode != null">#{sapPrototypeMakeCode},</if>
            <if test="unitPrice != null">#{unitPrice},</if>
            <if test="surpassState != null">#{surpassState},</if>
            <if test="url != null">#{url},</if>
            <if test="filePath != null">#{filePath},</if>
            <if test="originalFilename != null">#{originalFilename},</if>
        </trim>
    </insert>

    <update id="updateProProductionSolution" parameterType="ProProductionSolution">
        update pro_production_solution
        <trim prefix="SET" suffixOverrides=",">
            <if test="productionSolutionCode != null">production_solution_code = #{productionSolutionCode},</if>
            <if test="productionSolutionName != null">production_solution_name = #{productionSolutionName},</if>
            <if test="productionSolutionType != null">production_solution_type = #{productionSolutionType},</if>
            <if test="itemId != null">item_id = #{itemId},</if>
            <if test="routeId != null">route_id = #{routeId},</if>
            <if test="usageId != null">usage_id = #{usageId},</if>
            <if test="remark != null">remark = #{remark},</if>
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="sapItemCode != null">sap_item_code = #{sapItemCode},</if>
            <if test="factoryCode != null">factory_code = #{factoryCode},</if>
            <if test="groupKey != null">group_key = #{groupKey},</if>
            <if test="groupCounter != null">group_counter = #{groupCounter},</if>
            <if test="materialsGroupBill != null">materials_group_bill = #{materialsGroupBill},</if>
            <if test="alternativeBill != null">alternative_bill = #{alternativeBill},</if>
            <if test="startDate != null">start_date = #{startDate},</if>
            <if test="expiryDate != null">expiry_date = #{expiryDate},</if>
            <if test="maxLot != null">max_lot = #{maxLot},</if>
            <if test="minLot != null">min_lot = #{minLot},</if>
            <if test="status != null">status = #{status},</if>
            <if test="ecnCode != null">ecn_code = #{ecnCode},</if>
            <if test="unitOfMeasure != null">unit_of_measure = #{unitOfMeasure},</if>
            <if test="sapUsageId != null">sap_usage_id = #{sapUsageId},</if>
            <if test="sapPrototypeMakeCode != null">sap_prototype_make_code = #{sapPrototypeMakeCode},</if>
            <if test="unitPrice != null">unit_price = #{unitPrice},</if>
            <if test="surpassState != null">surpass_state = #{surpassState},</if>
            url = #{url},
            file_path = #{filePath},
            original_filename = #{originalFilename}
        </trim>
        where production_solution_id = #{productionSolutionId}
    </update>

    <delete id="deleteProProductionSolutionByProductionSolutionId" parameterType="Long">
        delete
        from pro_production_solution
        where production_solution_id = #{productionSolutionId}
    </delete>

    <delete id="deleteProProductionSolutionByProductionSolutionIds" parameterType="String">
        delete from pro_production_solution where production_solution_id in
        <foreach item="productionSolutionId" collection="array" open="(" separator="," close=")">
            #{productionSolutionId}
        </foreach>
    </delete>
</mapper>
