<?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.WorkOverPersonDAO">
    <resultMap id="WorkOverPersonMap" type="com.jpsoft.shinestar.modules.base.entity.WorkOverPerson">
        <id property="id" column="id_" />
        <result property="personPopedomId" column="person_popedom_id" />
        <result property="workOverId" column="work_over_id" />
        <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" />
        <result property="duration" column="duration_"/>
        <result property="durationStr" column="duration_str"/>
        <result property="personName" column="person_name" />
        <result property="startTime" column="start_time" />
        <result property="endTime" column="end_time" />
        <result property="hours" column="hours_"/>
        <result property="personId" column="person_id" />
        <result property="approvalPersonId" column="approval_person_id" />
        <result property="approvalPersonName" column="approval_person_name" />
        <result property="beginTime" column="begin_time" />
        <result property="finishTime" column="finish_time"/>
    </resultMap>
    <insert id="insert" parameterType="com.jpsoft.shinestar.modules.base.entity.WorkOverPerson">
        <!--
        <selectKey resultType="java.lang.String" order="BEFORE" keyProperty="id">
            select sys_guid() from dual
        </selectKey>
        -->
        <![CDATA[
		insert into base_work_over_person
	    (id_,person_popedom_id,work_over_id,create_by,create_time,update_by,update_time,del_flag,finish_time,
	    duration_,duration_str,begin_time)
		values
		(
            #{id,jdbcType=VARCHAR}
            ,#{personPopedomId,jdbcType=VARCHAR}
            ,#{workOverId,jdbcType=VARCHAR}
            ,#{createBy,jdbcType=VARCHAR}
            ,#{createTime,jdbcType= TIMESTAMP }
            ,#{updateBy,jdbcType=VARCHAR}
            ,#{updateTime,jdbcType= TIMESTAMP }
            ,#{delFlag,jdbcType= NUMERIC }
            ,#{finishTime,jdbcType=TIMESTAMP}
            ,#{duration,jdbcType=INTEGER}
            ,#{durationStr,jdbcType=VARCHAR}
            ,#{beginTime,jdbcType= TIMESTAMP }
		)
	]]>
    </insert>
    <delete id="delete" parameterType="string">
        delete from base_work_over_person where id_=#{id,jdbcType=VARCHAR}
    </delete>
    <delete id="deletePerson" parameterType="string">
        delete from base_work_over_person where work_over_id =#{id,jdbcType=VARCHAR}
    </delete>
    <update id="update" parameterType="com.jpsoft.shinestar.modules.base.entity.WorkOverPerson">
        update base_work_over_person
        <set>
            <if test="personPopedomId!=null">
                person_popedom_id=#{personPopedomId,jdbcType=VARCHAR},
            </if>
            <if test="workOverId!=null">
                work_over_id = #{workOverId,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>
            <if test="finishTime!=null">
                finish_time=#{finishTime,jdbcType=TIMESTAMP },
            </if>
            <if test="duration!=null">
                duration_=#{duration,jdbcType=INTEGER },
            </if>
            <if test="durationStr!=null">
                duration_str=#{durationStr,jdbcType=VARCHAR },
            </if>
            <if test="beginTime!=null">
                begin_time=#{beginTime,jdbcType= TIMESTAMP },
            </if>
        </set>
        where id_=#{id}
    </update>
    <select id="get" parameterType="string" resultMap="WorkOverPersonMap">
        select * from base_work_over_person where id_=#{0}
    </select>
    <select id="exist" parameterType="string" resultType="int">
        select count(*) from base_work_over_person where id_=#{0}
    </select>
    <select id="list" resultMap="WorkOverPersonMap">
		select * from base_work_over_person
	</select>
    <select id="search" parameterType="hashmap" resultMap="WorkOverPersonMap">
        <![CDATA[
			SELECT
                a.*,
                c.id_ as person_id,
                c.name_ AS person_name,
                g.id_ as approval_person_id,
                g.name_ as approval_person_name
            FROM
                base_work_over_person a
                LEFT JOIN base_person_popedom b ON a.person_popedom_id = b.id_
                LEFT JOIN base_person_info c ON b.person_id = c.id_
                left join base_work_over e on a.work_over_id = e.id_
                LEFT JOIN base_person_popedom f ON e.approval_person_popedom_id = f.id_
                LEFT JOIN base_person_info g ON f.person_id = g.id_
		]]>
        <where>
            a.del_flag = 0
            <if test="searchParams.id != null">
                and a.ID_ like #{searchParams.id}
            </if>
            <if test="searchParams.workOverId != null">
                and a.work_over_id = #{searchParams.workOverId}
            </if>
            <if test="searchParams.startDate != null">
                <![CDATA[
                and e.start_time>=#{searchParams.startDate}
                ]]>
            </if>
            <if test="searchParams.endDate != null">
                <![CDATA[
                and e.start_time<=#{searchParams.endDate}
                ]]>
            </if>
            <if test="searchParams.personId != null">
                and c.id_ = #{searchParams.personId}
            </if>
            <if test="searchParams.status != null">
                and e.status_ = #{searchParams.status}
            </if>
        </where>
        <foreach item="sort" collection="sortList"  open="order by" separator=",">
            ${sort.name} ${sort.order}
        </foreach>
    </select>

    <select id="findByWorkOverId" resultMap="WorkOverPersonMap">
        <![CDATA[
        select * from base_work_over_person
        where del_flag = 0
        and work_over_id = #{workOverId}
        ]]>
    </select>
    <select id="findOneByPersonIdAndDate" resultMap="WorkOverPersonMap">
        <![CDATA[
            SELECT
            a.*
            FROM
            base_work_over_person a
            inner join base_person_popedom b on a.person_popedom_id = b.id_
            inner join base_work_over c on a.work_over_id = c.id_
            where b.person_id = #{personId}
            and c.start_time<=#{attendanceDate}
            and c.end_time>=#{attendanceDate}
            and (c.status_='1' or c.status_ = '4')
            and a.del_flag=0
            limit 1
        ]]>
    </select>

    <select id="findByPersonIdAndSEDate" resultMap="WorkOverPersonMap">
        <![CDATA[
            SELECT
                a.*,
                c.start_time,
                c.end_time,
                COALESCE
		        ( c.hours_ , 0 ) as hours_
            FROM
                base_work_over_person a
                inner join base_person_popedom b on a.person_popedom_id = b.id_
                inner join base_work_over c on a.work_over_id = c.id_
            where
                b.person_id = #{personId}
                and c.start_time>=#{startDate}
                and c.start_time<=#{endDate}
                and (c.status_='1' or c.status_ = '4')
                and a.del_flag=0
                and c.del_flag = 0
        ]]>
    </select>
    <select id="findOneByPersonIdAndOffsetMinute" resultMap="WorkOverPersonMap">
        <![CDATA[
            SELECT
                a.*,
                c.start_time,
                c.end_time
            FROM
                base_work_over_person a
                inner join base_person_popedom b on a.person_popedom_id = b.id_
                inner join base_work_over c on a.work_over_id = c.id_
            where
                b.person_id = #{personId}
                and date_add(c.start_time,interval ${earlyMinutes} minute)<=#{recordTime}
                and date_add(c.end_time,interval ${delayMinutes} minute)>=#{recordTime}
                and (c.status_='1' or c.status_ = '4')
                and a.del_flag=0
                and c.del_flag = 0
            limit 1
        ]]>
    </select>
    <select id="findUnFinishByDate" resultType="java.util.Map">
        <![CDATA[
            select
            t1.id_,
            t4.name_,
            t3.person_id,
            t2.start_time,
            t2.end_time,
            t1.begin_time,
            t1.finish_time,
            t1.duration_,
            t1.duration_str
            from base_work_over_person t1
            inner join base_work_over t2 on t1.work_over_id = t2.id_
            inner join base_person_popedom t3 on t1.person_popedom_id = t3.id_
            inner join base_person_info t4 on t4.id_ = t3.person_id
            where t1.begin_time>#{startDate} and t1.begin_time<#{endDate}
            and t1.finish_time is null
            order by begin_time asc
        ]]>
    </select>
</mapper>