<?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">
<!-- namespace必须指向DAO接口 -->
<mapper namespace="com.jpsoft.shinestar.modules.base.dao.WorkPersonSchedulingDAO">
	<resultMap id="WorkPersonSchedulingMap" type="com.jpsoft.shinestar.modules.base.entity.WorkPersonScheduling">
		<id property="id" column="id_" />
			<result property="personId" column="person_id" />
			<result property="year" column="year_" />
			<result property="month" column="month_" />
			<result property="day" column="day_" />
			<result property="schedulingDay" column="scheduling_day" />
			<result property="shiftIds" column="shift_ids" />
			<result property="shiftNumbers" column="shift_numbers" />
			<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="delFlag" column="del_flag" />
			</resultMap>
	<insert id="insert" parameterType="com.jpsoft.shinestar.modules.base.entity.WorkPersonScheduling">
	<!--
	<selectKey resultType="java.lang.String" order="BEFORE" keyProperty="id">
		select sys_guid() from dual
	</selectKey>
	-->
	<![CDATA[
		insert into base_work_person_scheduling
	    (id_,person_id,year_,month_,day_,scheduling_day,shift_ids,shift_numbers,create_by,create_time,update_by,update_time,del_flag)
		values
		(
#{id,jdbcType=VARCHAR}
,#{personId,jdbcType= NUMERIC }
,#{year,jdbcType= NUMERIC }
,#{month,jdbcType= NUMERIC }
,#{day,jdbcType= NUMERIC }
,#{schedulingDay,jdbcType= TIMESTAMP }
,#{shiftIds,jdbcType= NUMERIC }
,#{shiftNumbers,jdbcType=VARCHAR}
,#{createBy,jdbcType=VARCHAR}
,#{createTime,jdbcType= TIMESTAMP }
,#{updateBy,jdbcType=VARCHAR}
,#{updateTime,jdbcType= TIMESTAMP }
,#{delFlag,jdbcType= NUMERIC }
		)
	]]>
	</insert>
	<delete id="delete" parameterType="string">
		delete from base_work_person_scheduling where id_=#{id,jdbcType=VARCHAR}
	</delete>
	<update id="update" parameterType="com.jpsoft.shinestar.modules.base.entity.WorkPersonScheduling">
		update base_work_person_scheduling
		<set>
				<if test="personId!=null">
		person_id=#{personId,jdbcType= NUMERIC },
		</if>
				<if test="year!=null">
		year_=#{year,jdbcType= NUMERIC },
		</if>
				<if test="month!=null">
		month_=#{month,jdbcType= NUMERIC },
		</if>
				<if test="day!=null">
		day_=#{day,jdbcType= NUMERIC },
		</if>
				<if test="schedulingDay!=null">
		scheduling_day=#{schedulingDay,jdbcType= TIMESTAMP },
		</if>
				<if test="shiftIds!=null">
		shift_ids=#{shiftIds,jdbcType= NUMERIC },
		</if>
				<if test="shiftNumbers!=null">
		shift_numbers=#{shiftNumbers,jdbcType=VARCHAR},
		</if>
				<if test="createBy!=null">
		create_by=#{createBy,jdbcType=VARCHAR},
		</if>
				<if test="createTime!=null">
		create_time=#{createTime,jdbcType= TIMESTAMP },
		</if>
				<if test="updateBy!=null">
		update_by=#{updateBy,jdbcType=VARCHAR},
		</if>
				<if test="updateTime!=null">
		update_time=#{updateTime,jdbcType= TIMESTAMP },
		</if>
				<if test="delFlag!=null">
		del_flag=#{delFlag,jdbcType= NUMERIC },
		</if>
		</set>
	where id_=#{id}
	</update>
	<select id="get" parameterType="string" resultMap="WorkPersonSchedulingMap">
		select * from base_work_person_scheduling where id_=#{0}
	</select>
	<select id="exist" parameterType="string" resultType="int">
		select count(*) from base_work_person_scheduling where id_=#{0}
	</select>
	<select id="list" resultMap="WorkPersonSchedulingMap">
		select * from base_work_person_scheduling
	</select>
	<select id="search" parameterType="hashmap" resultMap="WorkPersonSchedulingMap">
		<![CDATA[
			select * from base_work_person_scheduling
		]]>
		<where>
			del_flag = 0
			<if test="searchParams.id != null">
				and ID_ like #{searchParams.id}
			</if>
		</where>
		<foreach item="sort" collection="sortList"  open="order by" separator=",">
	        ${sort.name} ${sort.order}
	 	</foreach>
	</select>

	<select id="findByYearMonthDayPersonId" resultMap="WorkPersonSchedulingMap">
		<![CDATA[
		select * from base_work_person_scheduling
		where del_flag = 0
		and year_ = #{year}
		and month_ = #{month}
		and day_ = #{day}
		and person_id = #{personId}
		limit 1
		]]>
	</select>

	<update id="deleteByPersonIdSchedulingDay">
		update base_work_person_scheduling
		set del_flag = 1
		where
		del_flag = 0
		and year_ = #{year}
		and month_ = #{month}
		and day_ = #{day}
		and person_id = #{personId}
	</update>

	<select id="findByPersonIdSchedulingDay" resultMap="WorkPersonSchedulingMap">
		<![CDATA[
		select * from base_work_person_scheduling
		where del_flag = 0
		and person_id = #{personId}
		and scheduling_day = #{dateTime}
		]]>
	</select>

	<select id="findByPersonIdYearMonth" resultMap="WorkPersonSchedulingMap">
		<![CDATA[
		SELECT
			a.*
		FROM
			base_work_person_scheduling a
		WHERE
			a.del_flag = 0
			AND a.person_id = #{personId}
			AND a.year_ = #{year}
			AND a.month_ = #{month}
		ORDER BY
			a.day_
		]]>
	</select>
	<select id="findByPersonIdYearMonths" resultMap="WorkPersonSchedulingMap">
		<![CDATA[
		SELECT
			a.*
		FROM
			base_work_person_scheduling a
		]]>
		WHERE
			a.del_flag = 0
			AND a.person_id = #{personId}
			AND a.year_ = #{year}
			<foreach collection="months" index="index" item="month" open="and a.month_ in(" separator="," close=")">
				#{month}
			</foreach>
			<if test="isQueryRest == false">
				AND shift_ids NOT LIKE ( SELECT id_ FROM base_work_shift_info WHERE number_ = '0' )
			</if>
		ORDER BY
			a.year_,
			a.month_,
			a.day_
	</select>

	<select id="findByPersonIdSDateAndEDate" resultMap="WorkPersonSchedulingMap">
		<![CDATA[
		SELECT
			a.*
		FROM
			base_work_person_scheduling a
		WHERE
		a.del_flag = 0
		AND a.person_id = #{personId}
		and scheduling_day >= #{startDate}
        and scheduling_day <= #{endDate}
		]]>
		<if test="isQueryRest == false">
			AND shift_ids NOT LIKE ( SELECT id_ FROM base_work_shift_info WHERE number_ = '0' )
		</if>
		ORDER BY
		a.year_,
		a.month_,
		a.day_
	</select>

	<select id="findSchedulingDayByPersonIdSDateAndEDate" resultType="date">
		<![CDATA[
		SELECT DISTINCT
			scheduling_day
		FROM
			base_work_person_scheduling
		WHERE
			del_flag = 0
			AND person_id = #{personId}
			and scheduling_day >= #{startDate}
			and scheduling_day <= #{endDate}
			]]>
			<if test="isQueryRest == false">
                AND shift_ids NOT LIKE ( SELECT id_ FROM base_work_shift_info WHERE number_ = '0' )
            </if>
		ORDER BY
			scheduling_day
	</select>

	<select id="findRepeatSchedulingDay" resultType="date">
		SELECT
			 DISTINCT scheduling_day
		FROM
			base_work_person_scheduling
		WHERE
			person_id = #{personId}
			AND del_flag = 0
			AND scheduling_day IN
			( SELECT scheduling_day FROM base_work_person_scheduling
			WHERE person_id = #{personId}
			AND del_flag = 0
			GROUP BY scheduling_day HAVING count( scheduling_day ) > 1 )
		ORDER BY
			scheduling_day ASC
	</select>
	<select id="findRedundantSchedule" resultType="map">
		<![CDATA[
			select * from (
				select
				person_id as personId,
				scheduling_day as schedulingDay,
				count(*) as total
				from base_work_person_scheduling
				where
				scheduling_day>=#{startDate}
				and
				scheduling_day<=#{endDate}
				and
				del_flag=0
				group by person_id,scheduling_day
			) as a
			where a.total>1
		]]>
	</select>
</mapper>