<?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.bonus.system.holiday.dao.RequestReportDao">
    <insert id="insertHoliday">
        INSERT INTO leave_apply(user_id,user_name,org_id,org_name,leave_type,leave_reason,
        leave_start_date,
        <if test="leaveStartInterval != null">
            leave_start_interval,
        </if>
        leave_end_date,
        <if test="leaveEndInterval != null">
            leave_end_interval,
        </if>
                                leave_duration,examine_status,source,create_user_id,is_agree, location, host_user_id, remark,type)
        VALUES(#{userId},#{userName},#{orgId},#{orgName},#{leaveType},#{leaveReason},
        #{leaveStartDate},
        <if test="leaveStartInterval != null">
            #{leaveStartInterval},
        </if>
        #{leaveEndDate},
        <if test="leaveEndInterval != null">
            #{leaveEndInterval},
        </if>
               #{leaveDuration},#{examineStatus},#{source},#{createUserId}, #{isAgree}, #{location}, #{hostUserId}, #{remark}, #{type})
    </insert>
    <insert id="insertWorkReportData">
        INSERT INTO leave_check(month,
                                org_id,
                                leave_num,
                                out_num,
                                business_trip_num,
                                holiday_num)
        VALUES (#{month},
                #{orgId},
                #{leaveNum},
                #{outNum},
                #{businessTripNum},
                #{holidayNum})
    </insert>
    <update id="updateEvection">
        update leave_check
        set
        <if test="attCheckStatus!=null and attCheckStatus!=''">
            att_check_status =#{attCheckStatus},
        </if>
        <if test="attCheckTime!=null and attCheckTime!=''">
            att_check_time =#{attCheckTime},
        </if>
        <if test="attCheckRemark!=null and attCheckRemark!=''">
            att_check_remark =#{attCheckRemark},
        </if>
        <if test="chargeCheckStatus!=null and chargeCheckStatus!=''">
            charge_check_status =#{chargeCheckStatus},
        </if>
        <if test="chargeCheckTime!=null and chargeCheckTime!=''">
            charge_check_time =#{chargeCheckTime},
        </if>
        <if test="chargeCheckRemark!=null and chargeCheckRemark!=''">
            charge_check_remark =#{chargeCheckRemark},
        </if>
        <if test="wageCheckStatus!=null and wageCheckStatus!=''">
            wage_check_status =#{wageCheckStatus},
        </if>
        <if test="wageCheckTime!=null and wageCheckTime!=''">
            wage_check_time =#{wageCheckTime},
        </if>
        <if test="wageCheckRemark!=null and wageCheckRemark!=''">
            wage_check_remark =#{wageCheckRemark},
        </if>
        status=#{status}
        where id=#{id}
    </update>
    <select id="selectDataByMonth" resultType="com.bonus.system.holiday.entity.WorkReportBean">
        SELECT
            aa.*,
            GROUP_CONCAT(h.user_id) as chargeCheckId,
            (
                SELECT
                    GROUP_CONCAT(sur.user_id) as UserId
                FROM
                    sys_user_role sur
                        LEFT JOIN sys_role sr on sr.role_id=sur.role_id and sr.del_flag=0
                WHERE
                    sur.is_active=1
                  and sr.role_name='人资专员'
            ) as wage_check_id
        FROM
            (
                SELECT
                    a.*,
                    so.attendance_user_id as attCheckId
                FROM
                    (
                        SELECT #{month} as month,
            la.org_id as orgId,
            SUM(CASE WHEN la.leave_type = '轮休' and la.examine_status='1' THEN 1 ELSE 0 END) AS leaveNum,
            SUM(CASE WHEN la.leave_type = '出差' THEN 1 ELSE 0 END) AS businessTripNum,
            SUM(CASE WHEN la.leave_type = '临时外出' and la.examine_status='1' THEN 1 ELSE 0 END) AS outNum,
		    SUM(
			    CASE
	            WHEN la.leave_type='病假' and la.source=2 THEN
		            1
		        WHEN la.leave_type='年休假' and la.source=2 THEN
		            1
		        WHEN la.leave_type='探亲假' and la.source=2 THEN
		            1
		        WHEN la.leave_type='事假' and la.source=2 THEN
		            1
		        WHEN la.leave_type='产假' and la.source=2 THEN
		            1
		        WHEN la.leave_type='婚假' and la.source=2 THEN
		            1
		        WHEN la.leave_type='丧假' and la.source=2 THEN
		            1
                WHEN la.leave_type='育儿假' and la.source=2 THEN
                    1
                WHEN la.leave_type='陪护假' and la.source=2 THEN
                    1
	            ELSE
		            0
                END
		        ) as holidayNum
                        FROM
                            leave_apply la
                        WHERE
                            la.is_active=1
                          and (
                            SUBSTRING(la.leave_start_date
                            , 1
                            , 7)=#{month}
                           OR
                            SUBSTRING(la.leave_end_date
                            , 1
                            , 7)=#{month}
                            )
                        GROUP BY la.org_id
                    ) a
                        LEFT JOIN sys_organization so on so.id=a.orgId and so.is_active=1
            ) aa

                left join sys_organization_head h on h.org_id = aa.orgId and h.is_active = 1
        GROUP BY
            aa.orgId
    </select>

    <select id="getWorkReportList" resultType="com.bonus.system.holiday.entity.WorkReportBean">
        select a.*
        from (
        SELECT la.id,
        la.user_id AS userId,
        la.user_name AS userName,
        la.org_name AS orgName,
        la.create_time AS createTime,
        la.leave_start_date AS startDate,
        la.leave_end_date AS endDate,
        la.leave_duration AS leaveDuration,
        la.leave_form_id AS uuId,
        la.examine_status AS examineStatus,
        la.location,
        case
        when la.leave_type = '病假' then '请假'
        when la.leave_type = '年休假' then '请假'
        when la.leave_type = '事假' then '请假'
        when la.leave_type = '产假' then '请假'
        when la.leave_type = '婚假' then '请假'
        when la.leave_type = '丧假' then '请假'
        when la.leave_type = '探亲假' then '请假'
        when la.leave_type = '请假' then '请假'
        when la.leave_type = '育儿假' then '请假'
        when la.leave_type = '陪护假' then '请假'
        else la.leave_type
        end as leaveType,
        case when la.is_agree = '1' then '是' else '否' end as isAgree,
        la.leave_reason as leaveReason,
        la.remark,
        la.host_user_id as hostUserId
        FROM leave_apply la
        LEFT JOIN sys_user su on su.user_id = la.user_id and su.is_active = 1
        WHERE la.is_active = 1
        AND (
        (la.leave_start_date &lt;= #{endDate} AND la.leave_end_date >= #{startDate}) or
        la.leave_start_date BETWEEN #{startDate} and #{endDate}
        OR
        la.leave_end_date BETWEEN #{startDate} and #{endDate}
        )
        AND (
        (la.leave_type IN ('轮休', '临时外出') AND (la.examine_status = '1' or la.examine_status = '0') )
        OR
        (la.type = '请假' AND la.source = '1')
        OR
        (la.leave_type NOT IN ('轮休', '临时外出', '请假'))
        )
        and su.is_cadre = 1
        <if test="userName!=null and userName!=''">
            and la.user_name like concat('%',#{userName},'%')
        </if>
        <if test="examineStatus!=null and examineStatus!=''">
            and la.examine_status = #{examineStatus}
        </if>
        ) a
        where 1=1
        <if test="leaveTypes!=null and leaveTypes!=''">
            and a.leaveType = #{leaveTypes}
        </if>

    </select>
    <select id="getDetailsList" resultType="com.bonus.system.holiday.entity.WorkReportBean">
        SELECT
        la.id,
        la.user_id AS userId,
        la.user_name AS userName,
        la.org_name AS orgName,
        la.create_time AS createTime,
        la.leave_start_date AS startDate,
        la.leave_end_date AS endDate,
        la.leave_duration AS leaveDuration,
        la.leave_form_id                           as uuId
        FROM
        leave_apply la
        WHERE
        la.is_active = 1
        AND ( SUBSTRING( la.leave_start_date, 1, 7 ) = #{month} OR SUBSTRING( la.leave_end_date, 1, 7 ) = #{month} )
        <if test="holidayType!=null and holidayType.length > 0">
            and la.leave_type in
            <foreach item="item" index="index" collection="holidayType" open="(" separator="," close=")">
                #{item}
            </foreach>
            and la.source=2
        </if>
        <if  test="type!='出差' and holidayType==null">
            AND la.leave_type = #{type}
            and la.examine_status='1'
        </if>
        <if  test="type=='出差' and holidayType==null">
            AND la.leave_type = #{type}
        </if>
          <if test="userName!=null and userName!=''">
            and la.user_name like concat('%',#{userName},'%')
        </if>
        AND la.org_id = #{orgId}
    </select>
    <select id="getRoleListByUserId" resultType="com.bonus.system.api.domain.SysRole">
        SELECT
            sr.role_name as roleName
        FROM
            sys_user_role sur
                LEFT JOIN sys_role sr on sr.role_id=sur.role_id and sr.del_flag=0
        WHERE
            sur.is_active=1
          and sur.user_id=#{userId}
    </select>
    <select id="selectEvectionById" resultType="com.bonus.system.holiday.entity.WorkReportBean">
        SELECT
            lc.id,
            lc.`month`,
            lc.org_id as orgId,
            lc.leave_num as leaveNum,
            lc.out_num as outNum,
            lc.business_trip_num as business_tripNum,
            lc.holiday_num as holidayNum,
            lc.att_check_id as attCheckId,
            lc.charge_check_id as chargeCheckId,
            lc.wage_check_id as wageCheckId,
            lc.status
        FROM
            leave_check lc
        WHERE
            lc.is_active = 1
        AND lc.id = #{id}
    </select>
</mapper>