<?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.jeeplus.modules.sg.managementcenter.project.mapper.PickingRequisitionMapper">
    
	<sql id="pickingColumns">
		a.id AS "id",
		a.create_by AS "createBy.id",
		a.create_date AS "createDate",
		a.update_by AS "updateBy.id",
		a.update_date AS "updateDate",
		a.remarks AS "remarks",
		a.del_flag AS "delFlag",
		a.requisition AS requisition,
		a.projectDefinition AS projectDefinition
	</sql>
    
	<select id="get" resultType="PickingRequisition" >
		SELECT 
			<include refid="pickingColumns"/>
		FROM xm_picking_contrast a
		WHERE a.id = #{id}
	</select>
	
	<select id="findList" resultType="PickingRequisition" >
		SELECT
		<include refid="pickingColumns"/>
		FROM xm_picking_contrast a
		<where>
			a.del_flag = #{DEL_FLAG_NORMAL}
			${dataScope}
		</where>
		<choose>
			<when test="page !=null and page.orderBy != null and page.orderBy != ''">
				ORDER BY ${page.orderBy}
			</when>
			<otherwise>
				ORDER BY a.update_date DESC
			</otherwise>
		</choose>
	</select>
	
	<select id="findAllList" resultType="PickingRequisition" >
		SELECT
		<include refid="pickingColumns"/>
		FROM xm_picking_contrast a
		<where>
			a.del_flag = #{DEL_FLAG_NORMAL}
			${dataScope}
		</where>		
		<choose>
			<when test="page !=null and page.orderBy != null and page.orderBy != ''">
				ORDER BY ${page.orderBy}
			</when>
			<otherwise>
				ORDER BY a.update_date DESC
			</otherwise>
		</choose>
	</select>
	
	<insert id="insert">
		INSERT INTO xm_picking_contrast(
			id,
			create_by,
			create_date,
			update_by,
			update_date,
			remarks,
			del_flag,
			lineItemNumber,
			requisition,
			userName,
			fullName,
			itemDetail,
			theFactory,
			reservedNumberr,
			reserveLineNumber,
			projectDefinition,
			wbsElement,
			materialCode,
			materialDescription,
			measuringUnit,
			quantityDemanded,
			quantityDelivered,
			changeData,
			requisitionCostCenter,
			factoryDescription,
			projectDepartment,
			batch,
			planningDivision,
			theOrder,
			wbsDescription,
			purposeMark,
			specialStock,
			appointmentDate,
			purposeIdentification,
			returnId,
			returnMessage
		) VALUES (
			#{id},
			#{createBy.id},
			#{createDate},
			#{updateBy.id},
			#{updateDate},
			#{remarks},
			#{delFlag},
			#{lineItemNumber},
			#{requisition},
			#{userName},
			#{fullName},
			#{itemDetail},
			#{theFactory},
			#{reservedNumberr},
			#{reserveLineNumber},
			#{projectDefinition},
			#{wbsElement},
			#{materialCode},
			#{materialDescription},
			#{measuringUnit},
			#{quantityDemanded},
			#{quantityDelivered},
			#{changeData},
			#{requisitionCostCenter},
			#{factoryDescription},
			#{projectDepartment},
			#{batch},
			#{planningDivision},
			#{theOrder},
			#{wbsDescription},
			#{purposeMark},
			#{specialStock},
			#{appointmentDate},
			#{purposeIdentification},
			#{returnId},
			#{returnMessage}
		)
	</insert>

	<insert id="insertList">
		replace INTO xm_picking_contrast(
		id,
		create_by,
		create_date,
		update_by,
		update_date,
		remarks,
		del_flag,
		requisition,
		projectDefinition
		) VALUES
		<foreach collection="list" item="item" index="index" separator="," >
			(
			#{item.id},
			#{item.createBy.id},
			#{item.createDate},
			#{item.updateBy.id},
			#{item.updateDate},
			#{item.remarks},
			#{item.delFlag},
			#{item.requisition},
			#{item.projectDefinition}
		)
		</foreach>
	</insert>

	<update id="update">
		UPDATE xm_picking_contrast SET

		WHERE id = #{id}
	</update>
	
	
	<!--物理删除-->
	<update id="delete">
		DELETE FROM xm_picking_contrast
		WHERE id = #{id}
	</update>
	
	<!--逻辑删除-->
	<update id="deleteByLogic">
		UPDATE xm_picking_contrast SET
			del_flag = #{DEL_FLAG_DELETE}
		WHERE id = #{id}
	</update>
	
	
	<!-- 根据实体名称和字段名称和字段值获取唯一记录 -->
	<select id="findUniqueByProperty" resultType="PickingRequisition" statementType="STATEMENT">
		select * FROM xm_picking_contrast  where ${id} = '${value}'
	</select>

	<select id="findListPicking" parameterType="java.util.List" resultType="com.jeeplus.modules.sg.managementcenter.project.entity.PickingRequisition">
		select
		<include refid="pickingColumns"/>
		FROM xm_picking_contrast a
		WHERE a.del_flag = '0'
		and a.projectDefinition in
		<if test="list != null">
			<foreach collection="list" index="index" item="item" open="("
					 separator="," close=")">
				#{item.projectDefinition}
			</foreach>
		</if>
	</select>

	<select id="findUserId" parameterType="java.util.List" resultType="java.lang.String">
		select a.user_id from sys_user_role a
		where  a.role_id in
		(
			select r.id from sys_role r where r.del_flag = '0' and  r.name in
			<foreach collection="list" item="item" index="index" open="(" separator="," close=")">
				#{item}
			</foreach>
		)
	</select>

	<select id="findListAcquisition" parameterType="java.util.List" resultType="com.jeeplus.modules.sg.picking.activiti.entity.Acquisition">
		select
		a.id AS "id",
		a.project_id  AS "projectId",
		a.managerTime AS "managerTime"
		FROM md_acquisition_process a
		WHERE a.del_flag = '0'
		and a.project_id in
		<if test="list != null">
			<foreach collection="list" index="index" item="item" open="("
					 separator="," close=")">
				#{item.id}
			</foreach>
		</if>
	</select>
</mapper>