<?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.BusinessOutDAO">
    <resultMap id="BusinessOutMap" type="com.jpsoft.shinestar.modules.base.entity.BusinessOut">
        <id property="id" column="id_"/>
        <result property="personPopedomId" column="person_popedom_id"/>
        <result property="type" column="type_"/>
        <result property="destination" column="destination_"/>
        <result property="startTime" column="start_time"/>
        <result property="endTime" column="end_time"/>
        <result property="estimatedTime" column="estimated_time"/>
        <result property="estimatedDate" column="estimated_date"/>
        <result property="content" column="content_"/>
        <result property="approvalPersonPopedomId" column="approval_person_popedom_id"/>
        <result property="status" column="status_"/>
        <result property="approvalNo" column="approval_no"/>
        <result property="approvalTime" column="approval_time"/>
        <result property="approvalContent" column="approval_content"/>
        <result property="approvalPersonIdStr" column="approval_person_id_str"/>
        <result property="approvalStatusStr" column="approval_status_str"/>
        <result property="approvalTimeStr" column="approval_time_str"/>
        <result property="approvalContentStr" column="approval_content_str"/>
        <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="remark" column="remark_"/>
        <result property="procinstId" column="procinst_id"/>
        <result property="workReport" column="work_report"/>
        <result property="personId" column="person_id"/>
        <result property="personName" column="person_name"/>
        <result property="personRoleName" column="person_role_name"/>
        <result property="personCompanyId" column="person_company_id"/>
        <result property="jobNumber" column="job_number"/>
    </resultMap>
    <insert id="insert" parameterType="com.jpsoft.shinestar.modules.base.entity.BusinessOut">
        <!--
        <selectKey resultType="java.lang.String" order="BEFORE" keyProperty="id">
            select sys_guid() from dual
        </selectKey>
        -->
        <![CDATA[
		insert into base_business_out
	    (id_,person_popedom_id,type_,destination_,start_time,end_time,estimated_time,estimated_date,content_,approval_person_popedom_id,status_,approval_no,approval_time,approval_content,approval_person_id_str,approval_status_str,approval_time_str,approval_content_str,create_by,create_time,update_by,update_time,del_flag,remark_,procinst_id,work_report)
		values
		(
            #{id,jdbcType=VARCHAR}
            ,#{personPopedomId,jdbcType=VARCHAR}
            ,#{type,jdbcType=VARCHAR}
            ,#{destination,jdbcType=VARCHAR}
            ,#{startTime,jdbcType= TIMESTAMP }
            ,#{endTime,jdbcType=TIMESTAMP}
            ,#{estimatedTime,jdbcType=VARCHAR}
            ,#{estimatedDate,jdbcType=TIMESTAMP}
            ,#{content,jdbcType=VARCHAR}
            ,#{approvalPersonPopedomId,jdbcType=VARCHAR }
            ,#{status,jdbcType=VARCHAR}
            ,#{approvalNo,jdbcType=VARCHAR}
            ,#{approvalTime,jdbcType= TIMESTAMP }
            ,#{approvalContent,jdbcType=VARCHAR}
            ,#{approvalPersonIdStr,jdbcType=VARCHAR}
            ,#{approvalStatusStr,jdbcType=VARCHAR}
            ,#{approvalTimeStr,jdbcType=VARCHAR}
            ,#{approvalContentStr,jdbcType=VARCHAR}
            ,#{createBy,jdbcType=VARCHAR}
            ,#{createTime,jdbcType= TIMESTAMP }
            ,#{updateBy,jdbcType=VARCHAR}
            ,#{updateTime,jdbcType= TIMESTAMP }
            ,#{delFlag,jdbcType= NUMERIC }
            ,#{remark,jdbcType=VARCHAR}
            ,#{procinstId,jdbcType=VARCHAR}
            ,#{workReport,jdbcType=VARCHAR}
		)
	]]>
    </insert>
    <delete id="delete" parameterType="string">
        delete from base_business_out where id_=#{id,jdbcType=VARCHAR}
    </delete>
    <update id="update" parameterType="com.jpsoft.shinestar.modules.base.entity.BusinessOut">
        update base_business_out
        <set>
            <if test="personPopedomId!=null">
                person_popedom_id=#{personPopedomId,jdbcType=VARCHAR},
            </if>
            <if test="type!=null">
                type_=#{type,jdbcType=VARCHAR},
            </if>
            <if test="destination!=null">
                destination_=#{destination,jdbcType=VARCHAR},
            </if>
            <if test="startTime!=null">
                start_time=#{startTime,jdbcType= TIMESTAMP },
            </if>
            <if test="endTime!=null">
                end_time=#{endTime,jdbcType= TIMESTAMP },
            </if>
            <if test="estimatedTime!=null">
                estimated_time=#{estimatedTime,jdbcType=VARCHAR},
            </if>
            <if test="estimatedDate != null">
                estimated_date=#{estimatedDate,jdbcType= TIMESTAMP },
            </if>
            <if test="content!=null">
                content_=#{content,jdbcType=VARCHAR},
            </if>
            <if test="approvalPersonPopedomId!=null">
                approval_person_popedom_id=#{approvalPersonPopedomId,jdbcType=VARCHAR },
            </if>
            <if test="status!=null">
                status_=#{status,jdbcType=VARCHAR},
            </if>
            <if test="approvalNo!=null">
                approval_no=#{approvalNo,jdbcType=VARCHAR},
            </if>
            <if test="approvalTime!=null">
                approval_time=#{approvalTime,jdbcType= TIMESTAMP },
            </if>
            <if test="approvalContent!=null">
                approval_content=#{approvalContent,jdbcType=VARCHAR},
            </if>
            <if test="approvalPersonIdStr!=null">
                approval_person_id_str=#{approvalPersonIdStr,jdbcType=VARCHAR},
            </if>
            <if test="approvalStatusStr!=null">
                approval_status_str=#{approvalStatusStr,jdbcType=VARCHAR},
            </if>
            <if test="approvalTimeStr!=null">
                approval_time_str=#{approvalTimeStr,jdbcType=VARCHAR},
            </if>
            <if test="approvalContentStr!=null">
                approval_content_str=#{approvalContentStr,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="remark!=null">
                remark_=#{remark,jdbcType=VARCHAR },
            </if>
            <if test="procinstId!=null">
                procinst_id=#{procinstId,jdbcType=VARCHAR },
            </if>
            <if test="workReport!=null">
                work_report=#{workReport,jdbcType=VARCHAR },
            </if>
        </set>
        where id_=#{id}
    </update>
    <select id="get" parameterType="string" resultMap="BusinessOutMap">
        select * from base_business_out where id_=#{0}
    </select>
    <select id="exist" parameterType="string" resultType="int">
        select count(*) from base_business_out where id_=#{0}
    </select>
    <select id="list" resultMap="BusinessOutMap">
        select * from base_business_out
    </select>
    <select id="search" parameterType="hashmap" resultMap="BusinessOutMap">
        <![CDATA[
			SELECT
				a.*,
				c.id_ as person_id,
				c.company_id as person_company_id,
				c.name_  as person_name,
				d.person_role_name as person_role_name,
				c.job_number as job_number
			FROM
				base_business_out a
				LEFT JOIN base_person_popedom b ON a.person_popedom_id = b.id_ AND b.del_flag = 0
				LEFT JOIN base_person_info c ON b.person_id = c.id_ AND c.del_flag = 0
				LEFT JOIN base_person_role_info d ON b.person_role_id = d.id_ AND d.del_flag = 0
				LEFT JOIN base_company_info e ON e.id_ = c.company_id AND e.del_flag = 0
		]]>
        <where>
            a.del_flag = 0
            and c.del_flag = 0
            <if test="searchParams.id != null">
                and a.ID_ like #{searchParams.id}
            </if>
            <if test="searchParams.personName != null">
                and c.name_ like #{searchParams.personName}
            </if>
            <if test="searchParams.type != null">
                and a.type_ = #{searchParams.type}
            </if>
            <if test="searchParams.status != null">
                and a.status_ = #{searchParams.status}
            </if>
            <if test="searchParams.approvalNo != null">
                and a.approval_no like #{searchParams.approvalNo}
            </if>
            <if test="searchParams.personPopedomId != null">
                and a.person_popedom_id = #{searchParams.personPopedomId}
            </if>
            <if test="searchParams.approvalPersonPopedomId != null">
                and a.approval_person_popedom_id = #{searchParams.approvalPersonPopedomId}
            </if>
            <if test="searchParams.notStatus != null">
                and a.status_ != #{searchParams.notStatus}
            </if>
            <if test="searchParams.inCompanyId != null">
                <foreach collection="searchParams.inCompanyId" item="companyId" open="and b.company_id in ("
                         separator="," close=")">
                    #{companyId}
                </foreach>
            </if>
            <if test="searchParams.inCompanyIdList != null">
                <foreach collection="searchParams.inCompanyIdList" item="companyInfo" open="and b.company_id in ("
                         separator="," close=")">
                    #{companyInfo.id}
                </foreach>
            </if>
            <if test='searchParams.startTime!=null'>
                <![CDATA[
                    and a.start_time >= #{searchParams.startTime}
                ]]>
            </if>
            <if test='searchParams.endTime!=null'>
                <![CDATA[
                    and a.end_time <= #{searchParams.endTime}
                ]]>
            </if>

            <if test='searchParams.shiftStartTime!=null'>
                <![CDATA[
                    and a.start_time <= #{searchParams.shiftStartTime}
                ]]>
            </if>
            <if test='searchParams.shiftEndTime!=null'>
                <![CDATA[
                    and a.estimated_date >= #{searchParams.shiftEndTime}
                ]]>
            </if>

            <if test='searchParams.requestStartTime!=null'>
                <![CDATA[
                    and a.start_time >= #{searchParams.requestStartTime}
                ]]>
            </if>
            <if test='searchParams.requestEndTime!=null'>
                <![CDATA[
                    and a.start_time <= #{searchParams.requestEndTime}
                ]]>
            </if>
            <if test='searchParams.auditStartTime!=null'>
                <![CDATA[
                    and a.approval_time >= #{searchParams.auditStartTime}
                ]]>
            </if>
            <if test='searchParams.auditEndTime!=null'>
                <![CDATA[
                    and a.approval_time <= #{searchParams.auditEndTime}
                ]]>
            </if>
            <if test="searchParams.personInfoList != null ">
                <foreach collection="searchParams.personInfoList" item="item" index="index" open="and b.person_id in(" separator="," close=")">
                    #{item.id}
                </foreach>
            </if>
            <if test="searchParams.companyCode != null">
                and e.code_ like #{searchParams.companyCode}
            </if>
            <if test="searchParams.hqCompanyCode != null">
                and e.code_ like #{searchParams.hqCompanyCode}
            </if>
            <if test="searchParams.hqStatus != null">
                and c.hq_status = #{searchParams.hqStatus}
            </if>
            <if test="searchParams.hqStatus == null and searchParams.hqPerson != null">
                <![CDATA[
					and ifnull(c.hq_status,0)<>0
				]]>
            </if>
        </where>
        <foreach item="sort" collection="sortList" open="order by" separator=",">
            ${sort.name} ${sort.order}
        </foreach>
    </select>

    <select id="findByPersonPopedomAndEndTimeIsNull" resultMap="BusinessOutMap">
        <![CDATA[
		select * from base_business_out
		where del_flag = 0
		and person_popedom_id = #{personPopedomId}
		and end_time is null
		and status_ <> '2'
		]]>
    </select>

    <select id="findLastActiveByPersonId" resultMap="BusinessOutMap">
        <![CDATA[
			SELECT
				a.*
			FROM
				base_business_out a
				LEFT JOIN base_person_popedom b ON a.person_popedom_id = b.id_ AND b.del_flag = 0
			where
			   b.person_id=#{personId}
			and
			   a.end_time is null
			and
			   a.status_='1'
			order by a.create_time desc limit 1
		]]>
    </select>
    <select id="findByPersonIdAndDate" resultMap="BusinessOutMap">
        <!--考勤时间需要大于出差开始时间，避免从12点请到24点时，把上午的下班卡算进去-->
        <![CDATA[
            SELECT a.* FROM
            base_business_out a
            LEFT JOIN base_person_popedom b ON a.person_popedom_id = b.id_ AND b.del_flag = 0
            where
            b.person_id=#{personId}
            and
            a.start_time<#{attendanceDate}
            and
            (a.end_time is null or a.end_time>=#{attendanceDate})
            and
            a.status_='1'
            and
            a.del_flag=0
            order by a.create_time desc limit 1
        ]]>
    </select>
    <select id="findByGreaterEndTime" resultMap="BusinessOutMap">
        <![CDATA[
        select * from base_business_out
        where del_flag = 0
        and end_time > #{date}
        and status_ = '1'
        and person_popedom_id = #{personPopedomId}
        ]]>
    </select>
    <select id="findByPersonIdAndSEDate" resultMap="BusinessOutMap">
        <![CDATA[
            SELECT a.* FROM
            base_business_out a
            LEFT JOIN base_person_popedom b ON a.person_popedom_id = b.id_ AND b.del_flag = 0
            where
            b.person_id=#{personId}
            and
            a.start_time<=#{endDate}
            and
            a.end_time>=#{startDate}
            and
            a.status_='1'
            and
            a.del_flag=0
            order by a.create_time desc
        ]]>
    </select>
</mapper>