<?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.WorkReportDao">
    <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,
        <if test="attCheckId!=null and attCheckId!=''">
            att_check_id,
        </if>
        <if test="chargeCheckId!=null and chargeCheckId!=''">
            charge_check_id,
        </if>
        <if test="wageCheckId!=null and wageCheckId!=''">
            wage_check_id,
        </if>
        is_active
        )
        VALUES (#{month},
        #{orgId},
        #{leaveNum},
        #{outNum},
        #{businessTripNum},
        #{holidayNum},
        <if test="attCheckId!=null and attCheckId!=''">
            #{attCheckId},
        </if>
        <if test="chargeCheckId!=null and chargeCheckId!=''">
            #{chargeCheckId},
        </if>
        <if test="wageCheckId!=null and wageCheckId!=''">
            #{wageCheckId},
        </if>
        '1'
        )
    </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 wageCheckId
        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' or la.examine_status='0') 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' or la.examine_status='0') THEN 1 ELSE 0 END) AS outNum,
		    SUM(
			    CASE
		        WHEN la.leave_type='年休假' THEN 1
		        WHEN la.leave_type='探亲假' THEN 1
		        WHEN la.leave_type='事假' THEN 1
                WHEN la.leave_type='病假' THEN 1
		        WHEN la.leave_type='产假' THEN 1
		        WHEN la.leave_type='婚假' THEN 1
		        WHEN la.leave_type='丧假' THEN 1
                WHEN la.leave_type='育儿假' THEN 1
                WHEN la.leave_type='陪护假' THEN 1
                WHEN la.leave_type='流产假' THEN 1
                WHEN la.leave_type='哺乳假' THEN 1
                WHEN la.leave_type='节育假' THEN 1
                WHEN la.leave_type='父母护理假' THEN 1
                WHEN la.leave_type='其他' 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
        lc.id,
        lc.`month`,
        lc.org_id as orgId,
        so.org_name as orgName,
        lc.leave_num as leaveNum,
        lc.out_num as outNum,
        lc.business_trip_num as business_tripNum,
        lc.holiday_num as holidayNum,
        lc.wage_check_status as status,
        lc.att_check_remark as attCheckRemark,
        lc.charge_check_remark as chargeCheckRemark,
        lc.wage_check_remark as wageCheckRemark,
        if(att_check_status is null, att_check_id,if(charge_check_status is null, charge_check_id,if(wage_check_status is null, wage_check_id,'')))  as checkId
        FROM
        leave_check lc
        LEFT JOIN sys_organization so ON so.id = lc.org_id
        AND so.is_active = 1
        WHERE
        lc.is_active =1
        <if test="startDate!=null and startDate!='' and endDate!=null and endDate!=''">
            and lc.`month` BETWEEN #{startDate} AND #{endDate}
        </if>
        <if test="orgIds!=null and orgIds.length > 0">
            and lc.org_id in
            <foreach item="item" index="index" collection="orgIds" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>

    </select>
    <select id="getDetailsList" resultType="com.bonus.system.holiday.entity.WorkReportBean">
        SELECT
        #{type} as type,
        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.examine_status AS examineStatus,
        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>
        </if>
        <if  test="type!='出差' and holidayType==null">
            AND la.leave_type = #{type}
            and (
                la.examine_status='1' or
                la.examine_status='0'
            )
        </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>
          <if test="examineStatus!=null and examineStatus!=''">
            and la.examine_status = #{examineStatus}
        </if>
        AND la.org_id = #{orgId}
    </select>
    <select id="getRoleListByUserId" resultType="com.bonus.system.api.domain.SysRole">
        SELECT
            sr.role_id as roleId,
            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 sr.del_flag=0
          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>
    <select id="selectOrgIdByUserId" resultType="java.lang.Long">
        SELECT
            org_id
        FROM
            sys_user_org
        WHERE
            user_id = #{userId}
    </select>
    <select id="selectOrgIdByRoleAndUserId" resultType="java.lang.Long">
        SELECT so.id AS orgId
        FROM sys_organization so
        WHERE so.is_active = 1
          AND so.attendance_user_id = #{userId}
        UNION
        SELECT soh.org_id AS orgId
        FROM sys_organization_head soh
        WHERE soh.is_active = 1
          AND soh.user_id = #{userId}
    </select>
</mapper>