<?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="org.dromara.zjk.mapper.ZjkLeaveMapper">

    <resultMap id="BaseResultMap" type="org.dromara.zjk.domain.ZjkLeave">
        <id property="gid" column="gid" jdbcType="VARCHAR"/>
        <result property="leaveType" column="leave_type" jdbcType="VARCHAR"/>
        <result property="startDate" column="start_date" jdbcType="TIMESTAMP"/>
        <result property="endDate" column="end_date" jdbcType="TIMESTAMP"/>
        <result property="leaveDays" column="leave_days" jdbcType="OTHER"/>
        <result property="leaveReason" column="leave_reason" jdbcType="VARCHAR"/>
        <result property="status" column="status" jdbcType="OTHER"/>
        <result property="approvalUser" column="approval_user" jdbcType="VARCHAR"/>
        <result property="tenantId" column="tenant_id" jdbcType="VARCHAR"/>
        <result property="approvalMsg" column="approval_msg" jdbcType="VARCHAR"/>
        <result property="createdBy" column="created_by" jdbcType="OTHER"/>
        <result property="createdTime" column="created_time" jdbcType="DATE"/>
        <result property="updatedBy" column="updated_by" jdbcType="VARCHAR"/>
        <result property="updatedTime" column="updated_time" jdbcType="DATE"/>
    </resultMap>

    <sql id="Base_Column_List">
        gid,leave_type,start_date,
        end_date,leave_days,leave_reason,
        status,approval_user,tenant_id,
        approval_msg,created_by,created_time,
        updated_by,updated_time
    </sql>
    <update id="updateExpertLeaveStatus">
        update zjk_expert_item set leave_status=1 where user_id=#{userId} and item_id=#{itemId}
        <if test="stages != null and stages.size() > 0">
            AND review_id IN
            <foreach collection="stages" item="id" open="(" separator="," close=")">
                #{id}
            </foreach>
        </if>
    </update>
    <update id="updateExpertAcceptStatus">
        update zjk_expert_item set accept_status=#{status} where user_id=#{userId} and item_id=#{itemId}
        <if test="stages != null and stages.size() > 0">
            AND review_id IN
            <foreach collection="stages" item="id" open="(" separator="," close=")">
                #{id}
            </foreach>
        </if>
    </update>
    <update id="updateLeaveStatusRangeTime">
        update zjk_expert_item set leave_status=1 where user_id=#{userId} and review_id in
        <if test="ruleIds != null and ruleIds.size() > 0">
            <foreach collection="ruleIds" item="id" open="(" separator="," close=")">
                #{id}
            </foreach>
        </if>
    </update>
    <update id="updateExtraUserNum">
        update zjk_item_extreation set extracted_sum=extracted_sum-1,extraction_status=1 where review_id in
        <if test="reviewIds != null and reviewIds.size() > 0">
            <foreach collection="reviewIds" item="id" open="(" separator="," close=")">
                #{id}
            </foreach>
        </if>
    </update>
    <select id="selectPageSpList" resultType="org.dromara.zjk.domain.vo.ZjkLeaveVo">
        SELECT
        zl.*,
        su.nick_name AS createBy
        FROM
        zjk_leave zl
        LEFT JOIN sys_user su ON zl.create_by = su.user_id
        WHERE
        1=1
        <if test="bo.createBy != null and bo.createBy != ''">
            and zl.create_by = #{bo.createBy}
        </if>
        <if test="bo.status != null and bo.status != ''">
            and zl.status = #{bo.status}
        </if>
        <if test="bo.startDate != null">
            and zl.start_date >= #{bo.startDate}
        </if>
        <if test="bo.endDate != null">
            and zl.end_date <![CDATA[ <= ]]> #{bo.endDate}
        </if>
        ORDER BY
        zl.leave_type,
        zl.status

    </select>
    <select id="selectPageAdminLeaveList" resultType="org.dromara.zjk.domain.vo.ZjkLeaveVo">
        select
        zl.gid,
        zl.start_date,
        zl.end_date,
        zl.create_time,
        zl.leave_reason,
        zl.leave_type,
        zl.status,
        suu.nick_name as approval_user,
        su.nick_name as create_by,
        ze.expert_name as expertName
        from zjk_leave ZL
        LEFT JOIN sys_user su on ZL.create_by = su.user_id
        left join sys_user suu on ZL.approval_user = suu.user_id and zl.approval_user!=null
        LEFT JOIN zjk_expert ze ON ze.user_id = ZL.create_by
        where
        1=1
        <if test="bo.createBy != null and bo.createBy != ''">
            and zl.create_by = #{bo.createBy}
        </if>
        <if test="bo.status != null and bo.status != ''">
            and zl.status = #{bo.status}
        </if>
        <if test="bo.status != null and bo.status != ''">
            and zl.status = #{bo.status}
        </if>
        <if test="bo.status != null and bo.status != ''">
            and zl.status = #{bo.status}
        </if>
        <if test="bo.startDate != null">
            and zl.start_date >= #{bo.startDate}
        </if>
        <if test="bo.endDate != null">
            and zl.end_date <![CDATA[ <= ]]> #{bo.endDate}
        </if>
        ORDER BY
        zl.create_time desc,
        zl.status

    </select>
    <select id="selectLeaveSpDetail" resultType="org.dromara.zjk.domain.vo.ZjkLeaveVo">
        select zl.gid,
               zl.start_date,
               zl.end_date,
               zl.create_time,
               zl.leave_reason,
               zl.leave_type,
               zl.status,
               zl.approval_msg,
               suu.nick_name as approvalUser,
               su.nick_name  as create_by
        from zjk_leave ZL
                 LEFT JOIN sys_user su on ZL.create_by = su.user_id
                 left join sys_user suu on ZL.approval_user = suu.user_id and zl.approval_user is not null
        where zl.gid = #{gid}
    </select>
    <select id="getLeavePros" resultType="org.dromara.zjk.domain.vo.ZjkEvaluateVO">
        select zp.product_sn, zp.product_name, zp.product_unit, zp.unit_by, dd.*
        from (select zrp.item_id, zrp.review_sn as itemStage, zei.user_id as userId, zei.accept_status,
        zei.leave_status,zrp.review_time
        from zjk_review_phase zrp
        left join zjk_expert_item zei on zrp.id = zei.review_id
        where
        zei.is_sure = 1 and zrp.effective = 1 and zei.leave_status = 0
        <if test="userId != null and userId != ''">
            and zei.user_id = #{userId}
        </if>
        <if test="acceptStatus != null">
            and zei.accept_status = #{acceptStatus}
        </if>
        and zrp.notice_time > CURRENT_TIMESTAMP
        group by zrp.item_id, zrp.review_sn, zei.user_id, zei.accept_status, zei.leave_status,zrp.review_time) dd
        left join zjk_product zp on dd.item_id = zp.id
    </select>
    <select id="getRuleIdsByItemId" resultType="java.lang.Long">
        select id
        from zjk_review_phase
        where item_id = #{itemId}
          and review_sn = #{itemStage}
          and effective = 1
    </select>
    <select id="getLeavePerSts" resultType="org.dromara.zjk.domain.dto.LeaveStsDto">
        select dd.mt, count(1) cs
        from (select year(create_time) yr, month(create_time) mt
              from zjk_leave
              where status = 1
                and create_time between #{ago} and #{now}
              group by year(create_time), month(create_time), create_by) dd
        group by dd.yr, dd.mt
    </select>
    <select id="getApprovalStu" resultType="org.dromara.zjk.domain.dto.LeaveStsDto">
        select status mt, count(1) cs
        from zjk_leave
        where create_time between #{ago} and #{now}
        group by status

    </select>
    <select id="getLeaveRanks" resultType="org.dromara.zjk.domain.dto.LeaveRankDto">
        select ze.expert_name, bb.nums
        from (select cc.create_by, sum(ts) nums
              from (SELECT EXTRACT(DAY FROM AGE(end_date::timestamp, start_date::timestamp)) AS ts, create_by
                    FROM zjk_leave
                    where create_time between #{ago} and #{now}
                      and leave_type = 1
                      and status = 1) cc
              group by cc.create_by) bb
                 left join zjk_expert ze
                           on ze.user_id = bb.create_by
        where ze.expert_id is not null
    </select>


</mapper>
