<?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.op.mes.mapper.ProWetMaterialPlanMapper">
	
	<resultMap type="ProWetMaterialPlan" id="ProWetMaterialPlanResult">
		<result property="id"	column="id"	/>
		<result property="factoryCode"	column="factory_code"	/>
		<result property="planTime"	column="plan_time"	/>
		<result property="syncFlag"	column="sync_flag"	/>
		<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="orderType"	column="order_type"	/>
		<result property="prodCode"	column="prod_code"	/>
		<result property="prodDesc"	column="prod_desc"	/>
		<result property="prodSource"	column="prod_source"	/>
		<result property="quantity"	column="quantity"	/>
		<result property="unit"	column="unit"	/>
		<result property="atrr1"	column="atrr1"	/>
		<result property="atrr2"	column="atrr2"	/>
		<result property="atrr3"	column="atrr3"	/>
		<result property="status"	column="status"	/>
		<result property="prodType"	column="prod_type"	/>
		<result property="planCode"	column="plan_code"	/>
		<result property="shiftId"	column="shift_id"	/>
		<result property="shiftDesc"	column="shift_desc"	/>
	</resultMap>

	<sql id="selectProWetMaterialPlanVo">
		select id, factory_code, plan_time, sync_flag, create_by, create_time, update_by, update_time, order_type, prod_code, prod_desc, prod_source, quantity, unit, atrr1, atrr2, atrr3, status, prod_type, plan_code, shift_id, shift_desc from pro_wet_material_plan
	</sql>

	<select id="selectProWetMaterialPlanList" parameterType="ProWetMaterialPlan" resultMap="ProWetMaterialPlanResult">
		<include refid="selectProWetMaterialPlanVo"/>
		<where>
			<if test="syncFlag != null  and syncFlag != ''"> and sync_flag = #{syncFlag}</if>
		</where>
	</select>
	
	<select id="selectProWetMaterialPlanById" parameterType="String" resultMap="ProWetMaterialPlanResult">
		<include refid="selectProWetMaterialPlanVo"/>
		where id = #{id}
	</select>
    <select id="selectProWetMaterialPlanListByTime" parameterType="ProWetMaterialPlan" resultMap="ProWetMaterialPlanResult">
		<include refid="selectProWetMaterialPlanVo"/>
		WHERE CONVERT(date, plan_time) BETWEEN #{startTime} AND #{endTime}
		ORDER BY CONVERT(date, plan_time) DESC , sync_flag
	</select>
    <select id="selectBMSList" resultType="com.op.mes.domain.BMS">
		SELECT bucket_id AS 'bucketId', material_id AS 'materialId',shift_id AS 'shiftId'
		FROM pro_wet_material_plan_detail
		WHERE status = '1' AND CONVERT(date, create_time) = #{planTime}
		GROUP BY bucket_id,material_id,shift_id
	</select>

	<select id="selectShiftById" resultType="java.lang.String">
		SELECT bst.Shift_Desc_Global shiftName
		FROM base_shifts_t bst
		WHERE bst.Shift_Id = #{shiftId}
	</select>

	<select id="selectBucketList" resultType="com.op.mes.domain.Bucket">
		SELECT equipment_id AS 'bucketId',equipment_code AS 'bucketCode',equipment_name AS 'bucketName'
		FROM base_equipment
		WHERE equipment_type_code = 'equ_type_lg'
	</select>

	<select id="selectProductList" resultType="com.op.mes.domain.Product">
		SELECT product_id AS 'productId', product_code AS 'productCode',product_desc_zh AS 'productDesc'
		FROM base_product
		WHERE active_flag = '1'
	</select>

	<select id="selectProWetMaterialPlanByPlanTime" resultType="java.lang.String">
		select id
		from pro_wet_material_plan
		where prod_source = #{bucketCode} and plan_time = #{planTime} and shift_id = #{shiftId}
	</select>

	<insert id="insertProWetMaterialPlan" parameterType="ProWetMaterialPlan">
		insert into pro_wet_material_plan
		<trim prefix="(" suffix=")" suffixOverrides=",">
			<if test="id != null">id,</if>
			<if test="factoryCode != null">factory_code,</if>
			<if test="planTime != null">plan_time,</if>
			<if test="syncFlag != null">sync_flag,</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="orderType != null">order_type,</if>
			<if test="prodCode != null">prod_code,</if>
			<if test="prodDesc != null">prod_desc,</if>
			<if test="prodSource != null">prod_source,</if>
			<if test="quantity != null">quantity,</if>
			<if test="unit != null">unit,</if>
			<if test="atrr1 != null">atrr1,</if>
			<if test="atrr2 != null">atrr2,</if>
			<if test="atrr3 != null">atrr3,</if>
			<if test="status != null">status,</if>
			<if test="prodType != null">prod_type,</if>
			<if test="planCode != null">plan_code,</if>
			<if test="shiftId != null">shift_id,</if>
			<if test="shiftDesc != null">shift_desc,</if>
		 </trim>
		<trim prefix="values (" suffix=")" suffixOverrides=",">
			<if test="id != null">#{id},</if>
			<if test="factoryCode != null">#{factoryCode},</if>
			<if test="planTime != null">#{planTime},</if>
			<if test="syncFlag != null">#{syncFlag},</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="orderType != null">#{orderType},</if>
			<if test="prodCode != null">#{prodCode},</if>
			<if test="prodDesc != null">#{prodDesc},</if>
			<if test="prodSource != null">#{prodSource},</if>
			<if test="quantity != null">#{quantity},</if>
			<if test="unit != null">#{unit},</if>
			<if test="atrr1 != null">#{atrr1},</if>
			<if test="atrr2 != null">#{atrr2},</if>
			<if test="atrr3 != null">#{atrr3},</if>
			<if test="status != null">#{status},</if>
			<if test="prodType != null">#{prodType},</if>
			<if test="planCode != null">#{planCode},</if>
			<if test="shiftId != null">#{shiftId},</if>
			<if test="shiftDesc != null">#{shiftDesc},</if>
		 </trim>
	</insert>

	<update id="updateProWetMaterialPlan" parameterType="ProWetMaterialPlan">
		update pro_wet_material_plan
		<trim prefix="SET" suffixOverrides=",">
			<if test="factoryCode != null">factory_code = #{factoryCode},</if>
			<if test="planTime != null">plan_time = #{planTime},</if>
			<if test="syncFlag != null">sync_flag = #{syncFlag},</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="orderType != null">order_type = #{orderType},</if>
			<if test="prodCode != null">prod_code = #{prodCode},</if>
			<if test="prodDesc != null">prod_desc = #{prodDesc},</if>
			<if test="prodSource != null">prod_source = #{prodSource},</if>
			<if test="quantity != null">quantity = #{quantity},</if>
			<if test="unit != null">unit = #{unit},</if>
			<if test="atrr1 != null">atrr1 = #{atrr1},</if>
			<if test="atrr2 != null">atrr2 = #{atrr2},</if>
			<if test="atrr3 != null">atrr3 = #{atrr3},</if>
			<if test="status != null">status = #{status},</if>
			<if test="prodType != null">prod_type = #{prodType},</if>
			<if test="planCode != null">plan_code = #{planCode},</if>
			<if test="shiftId != null">plan_code = #{shiftId},</if>
			<if test="shiftDesc != null">plan_code = #{shiftDesc},</if>
		</trim>
		where id = #{id}
	</update>

	<delete id="deleteProWetMaterialPlanById" parameterType="String">
		delete from pro_wet_material_plan where id = #{id}
	</delete>

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