<?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.business.dao.WorkAttendanceDAO">
    <resultMap id="WorkAttendanceMap" type="com.jpsoft.shinestar.modules.business.entity.WorkAttendance">
        <id property="id" column="id_"/>
        <result property="alarmConfigId" column="alarm_config_id"/>
        <result property="personId" column="person_id"/>
        <result property="result" column="result_"/>
        <result property="temperature" column="temperature_"/>
        <result property="recordTime" column="record_time"/>
        <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="sources" column="sources_"/>
        <result property="type" column="type_"/>
        <result property="workStatus" column="work_status"/>
        <result property="remark" column="remark_"/>
    </resultMap>
    <resultMap id="WorkAttendanceMap2" type="com.jpsoft.shinestar.modules.business.entity.WorkAttendance" extends="WorkAttendanceMap">
        <result property="classifier" column="classifier_"/>
    </resultMap>
    <insert id="insert" parameterType="com.jpsoft.shinestar.modules.business.entity.WorkAttendance">
        <!--
        <selectKey resultType="java.lang.String" order="BEFORE" keyProperty="id">
            select sys_guid() from dual
        </selectKey>
        -->
        <![CDATA[
		insert into business_work_attendance
	    (id_,alarm_config_id,person_id,result_,create_by,create_time,update_by,update_time,
	    del_flag,record_time,temperature_,sources_,type_,work_status,remark_)
		values
		(
			#{id,jdbcType=VARCHAR}
			,#{alarmConfigId,jdbcType=VARCHAR}
			,#{personId,jdbcType=NUMERIC}
			,#{result,jdbcType=VARCHAR}
			,#{createBy,jdbcType=VARCHAR}
			,#{createTime,jdbcType= TIMESTAMP }
			,#{updateBy,jdbcType=VARCHAR}
			,#{updateTime,jdbcType= TIMESTAMP }
			,#{delFlag,jdbcType= NUMERIC }
			,#{recordTime,jdbcType= TIMESTAMP }
			,#{temperature,jdbcType= NUMERIC }
            ,#{sources,jdbcType=VARCHAR}
            ,#{type,jdbcType=VARCHAR}
            ,#{workStatus,jdbcType=VARCHAR}
            ,#{remark,jdbcType=VARCHAR}
		)
	]]>
    </insert>
    <delete id="delete" parameterType="string">
        delete from business_work_attendance where id_=#{id,jdbcType=VARCHAR}
    </delete>
    <update id="update" parameterType="com.jpsoft.shinestar.modules.business.entity.WorkAttendance">
        update business_work_attendance
        <set>
            <if test="alarmConfigId!=null">
                alarm_config_id=#{alarmConfigId,jdbcType=VARCHAR},
            </if>
            <if test="personId!=null">
                person_id=#{personId,jdbcType=NUMERIC},
            </if>
            <if test="result!=null">
                result_=#{result,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="recordTime!=null">
                record_time=#{recordTime,jdbcType= TIMESTAMP },
            </if>
            <if test="temperature!=null">
                temperature_=#{temperature,jdbcType= NUMERIC },
            </if>
            <if test="sources!=null">
                sources_=#{sources,jdbcType=VARCHAR},
            </if>
            <if test="type!=null">
                type_=#{type,jdbcType=VARCHAR},
            </if>
            <if test="workStatus!=null">
                work_status=#{workStatus,jdbcType=VARCHAR},
            </if>
            <if test="remark!=null">
                remark_=#{remark,jdbcType=VARCHAR},
            </if>
        </set>
        where id_=#{id}
    </update>
    <select id="get" parameterType="string" resultMap="WorkAttendanceMap">
        select * from business_work_attendance where id_=#{0}
    </select>
    <select id="exist" parameterType="string" resultType="int">
        select count(*) from business_work_attendance where id_=#{0}
    </select>
    <select id="list" resultMap="WorkAttendanceMap">
        select * from business_work_attendance
    </select>
    <select id="search" parameterType="hashmap" resultMap="WorkAttendanceMap">
        <![CDATA[
			select * from business_work_attendance
		]]>
        <where>
            del_flag=0
            <if test="searchParams.personId != null">
                and person_id = #{searchParams.personId}
            </if>
            <if test="searchParams.alarmConfigId != null">
                and alarm_config_id = #{searchParams.alarmConfigId}
            </if>
            <if test="searchParams.maxRecordTime != null">
                <![CDATA[
                    and record_time >= #{searchParams.maxRecordTime}
                ]]>
            </if>
            <if test="searchParams.result != null">
                and result_ = #{searchParams.result}
            </if>
        </where>
        <foreach item="sort" collection="sortList" open="order by" separator=",">
            ${sort.name} ${sort.order}
        </foreach>
    </select>
    <select id="findByPersonIdAndDate" resultMap="WorkAttendanceMap2">
        <![CDATA[
            select a.*,b.classifier_ from business_work_attendance a,base_alarm_config b
            where a.alarm_config_id = b.id_
            and a.person_id=#{personId}
            and a.record_time>=#{startDate}
            and a.record_time<#{endDate}
            and a.del_flag=0
            order by record_time asc
        ]]>
    </select>
    <select id="findByPeriod" resultMap="WorkAttendanceMap">
        select * from business_work_attendance
        where person_id=#{personId}
        and alarm_config_id=#{alarmConfigId}
        and DATEDIFF(#{recordTime},record_time)=0
        and del_flag=0
        order by record_time asc
    </select>
    <select id="queryUnAttendanceList" resultMap="com.jpsoft.shinestar.modules.base.dao.PersonInfoDAO.PersonInfoMap">
        <![CDATA[
        select a.*,b.name_ as company_name
        from base_person_info a,base_company_info b
        where a.company_id=b.id_ and b.code_ like #{companyCode}
        and a.del_flag=0
        and a.id_ in (
            select person_id from business_work_attendance
            where record_time>=#{startDate}
            and record_time <=#{endDate}
            and result_='0'
            and del_flag=0
        )
    ]]>
    </select>
    <select id="findByPersonIdAndPeriod" resultMap="WorkAttendanceMap2">
        select a.* from business_work_attendance a
        where a.person_id=#{personId}
        and a.alarm_config_id=#{alarmConfigId}
        and DATEDIFF(#{queryDate},a.record_time)=0
        and a.del_flag=0
        order by a.record_time asc
        limit 1
    </select>
    <select id="findByPersonIdAndAttendanceType" resultMap="WorkAttendanceMap">
        select * from business_work_attendance t1,base_alarm_config t2
        where t1.person_id=#{personId}
        and t1.alarm_config_id=t2.id_
        and t2.attendance_type=2
        <![CDATA[
            and record_time>=#{startDate}
            and record_time<=#{endDate}
        ]]>
        and del_flag=0
        order by record_time asc
    </select>
    <select id="findByPersonIdAndWorkStatus" resultMap="WorkAttendanceMap">
        select * from business_work_attendance t1
        where t1.person_id=#{personId}
        and t1.work_status=#{workStatus}
        <![CDATA[
            and record_time>=#{startDate}
            and record_time<=#{endDate}
        ]]>
        and del_flag=0
        order by record_time asc
    </select>
    <delete id="deleteByCompanyCodeAndDate">
        delete from business_work_attendance
        where person_id in (
            select a.person_id from base_person_popedom a,base_company_info b
            where a.company_id=b.id_
            and b.code_ like #{companyCode}
            and a.del_flag=0
            <foreach collection="excludeCompanyList" index="index" item="item" open="and b.id_ not in (" separator="," close=")">
                #{item}
            </foreach>
        )
        <if test="alarmConfigId != null">
            and alarm_config_id = #{alarmConfigId}
        </if>
        <![CDATA[
            and record_time>=#{startDate}
            and record_time<=#{endDate}
            and sources_=#{sources}
        ]]>
    </delete>
    <delete id="deleteByPersonIdAndDate">
        delete from business_work_attendance
        where person_id=#{personId}
        <if test="alarmConfigId != null">
            and alarm_config_id = #{alarmConfigId}
        </if>
        <![CDATA[
            and record_time>=#{startDate}
            and record_time<=#{endDate}
            and sources_=#{sources}
            and ifnull(type_,'')<>'2'
        ]]>
    </delete>
</mapper>