<?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="cn.com.ctop.oa.modules.mapper.WechatNoListMapper">

    <select id="queryAttendanceData" resultType="cn.com.ctop.oa.modules.entity.WechatAttendance">
        SELECT DISTINCT
        us.user_id AS userId,
        us.user_name AS userName,
        dpt.depart_id AS departId,
        dpt.depart_name AS departName,
        DATE_FORMAT(ck.checkin_time,'%Y-%m-%d') AS checkinDate,
        ck.checkin_type AS checkinType,
        ck.exception_type AS exceptionType,
        nl.status as status,
        DATE_FORMAT(ck.checkin_time, '%Y-%m-%d %H:%i:%s') AS checkinTime,
        case when nl.type !='' THEN nl.type
        when ck.checkin_type ='上班打卡' AND exception_type ='时间异常' then '迟到'
        when ck.checkin_type ='下班打卡' AND exception_type ='时间异常' then '早退'
        end AS type,
        case when nl.type!=''and nl.type !='补卡' then CONCAT(nl.new_duration,' h')
        when ck.checkin_type ='上班打卡'and ck.exception_type ='时间异常' then
        CONCAT(TIMESTAMPDIFF(MINUTE,DATE_FORMAT(CONCAT(DATE_FORMAT(ck.checkin_time,'%Y-%m-%d '),'09:35:00'),'%Y-%m-%d
        %H:%i:%s'),ck.checkin_time),' min')
        when ck.checkin_type ='下班打卡'and ck.exception_type ='时间异常' then
        CONCAT(TIMESTAMPDIFF(MINUTE,ck.checkin_time,DATE_FORMAT(CONCAT(DATE_FORMAT(ck.checkin_time,'%Y-%m-%d
        '),'19:00:00'),'%Y-%m-%d %H:%i:%s')),' min')
        end AS stateDuration
        FROM ctop_wechat_user_list AS us
        LEFT JOIN ctop_wechat_department AS dpt ON us.depart_id = dpt.depart_id
        LEFT JOIN ctop_wechat_checkin_data AS ck ON ck.user_id = us.user_id
        LEFT JOIN ctop_wechat_no_list AS nl ON nl.user_id = us.user_id and
        nl.new_begin Like CONCAT(DATE_FORMAT(ck.checkin_time,'%Y-%m-%d'),'%')
        WHERE ck.exception_type!='' and ISNULL(ck.exception_type)= 0
        and us.data_type = dpt.data_type
        and us.data_type = 0
        <if test="userId != null and userId != '' ">
            and us.user_id = #{userId}
        </if>
        <if test="userName != null and userName != '' ">
            and us.user_Name LIKE CONCAT("%",#{userName},"%")
        </if>
        <if test="departId != null and departId != '' ">
            and dpt.depart_id = #{departId}
        </if>
        <if test="departName != null and departName != '' ">
            and dpt.depart_name LIKE CONCAT("%",#{departName},"%")
        </if>
        <if test="startTime != null">
            and ck.checkin_time &gt;= #{startTime}
        </if>
        <if test="stopTime != null ">
            and ck.checkin_time &lt;= #{stopTime}
        </if>
        order by us.depart_id,us.user_name ,ck.checkin_time
    </select>

    <select id="queryYRAttendanceData" resultType="cn.com.ctop.oa.modules.entity.WechatAttendance">
        SELECT DISTINCT
        us.user_id AS userId,
        us.user_name AS userName,
        dpt.depart_id AS departId,
        dpt.depart_name AS departName,
        DATE_FORMAT(ck.checkin_time,'%Y-%m-%d') AS checkinDate,
        ck.checkin_type AS checkinType,
        ck.exception_type AS exceptionType,
        nl.status as status,
        DATE_FORMAT(ck.checkin_time, '%Y-%m-%d %H:%i:%s') AS checkinTime,
        case when nl.type !='' THEN nl.type
        when ck.checkin_type ='上班打卡' AND exception_type ='时间异常' then '迟到'
        when ck.checkin_type ='下班打卡' AND exception_type ='时间异常' then '早退'
        end AS type,
        case when nl.type!=''and nl.type !='补卡' then CONCAT(nl.new_duration,' h')
        when ck.checkin_type ='上班打卡'and ck.exception_type ='时间异常' then
        CONCAT(TIMESTAMPDIFF(MINUTE,DATE_FORMAT(CONCAT(DATE_FORMAT(ck.checkin_time,'%Y-%m-%d '),'10:00:00'),'%Y-%m-%d
        %H:%i:%s'),ck.checkin_time),' min')
        when ck.checkin_type ='下班打卡'and ck.exception_type ='时间异常' then
        CONCAT(TIMESTAMPDIFF(MINUTE,ck.checkin_time,DATE_FORMAT(CONCAT(DATE_FORMAT(ck.checkin_time,'%Y-%m-%d
        '),'19:00:00'),'%Y-%m-%d %H:%i:%s')),' min')
        end AS stateDuration
        FROM ctop_wechat_user_list AS us
        LEFT JOIN ctop_wechat_department AS dpt ON us.depart_id = dpt.depart_id
        LEFT JOIN ctop_wechat_checkin_data AS ck ON ck.user_id = us.user_id
        LEFT JOIN ctop_wechat_no_list AS nl ON nl.user_id = us.user_id and
        nl.new_begin Like CONCAT(DATE_FORMAT(ck.checkin_time,'%Y-%m-%d'),'%')
        WHERE ck.exception_type!='' and ISNULL(ck.exception_type)= 0
        and us.data_type = dpt.data_type
        and us.data_type = 1
        <if test="userId != null and userId != '' ">
            and us.user_id = #{userId}
        </if>
        <if test="userName != null and userName != '' ">
            and us.user_Name LIKE CONCAT("%",#{userName},"%")
        </if>
        <if test="departId != null and departId != '' ">
            and dpt.depart_id = #{departId}
        </if>
        <if test="departName != null and departName != '' ">
            and dpt.depart_name LIKE CONCAT("%",#{departName},"%")
        </if>
        <if test="startTime != null">
            and ck.checkin_time &gt;= #{startTime}
        </if>
        <if test="stopTime != null ">
            and ck.checkin_time &lt;= #{stopTime}
        </if>
        order by us.depart_id,us.user_name ,ck.checkin_time
    </select>


    <select id="queryOverTimeData" resultType="cn.com.ctop.oa.modules.entity.WechatAttendance">
        SELECT
        us.user_id AS userId,
        us.user_name AS userName,
        dpt.depart_id AS departId,
        dpt.depart_name AS departName,
        DATE_FORMAT(ck.checkin_time,'%Y-%m-%d') AS checkinDate,
        CONCAT(ck.overtime,' h') AS overtime
        FROM ctop_wechat_checkin_data as ck
        LEFT JOIN ctop_wechat_user_list as us ON ck.user_id = us.user_id
        LEFT JOIN ctop_wechat_department as dpt ON dpt.depart_id = us.depart_id
        WHERE ck.overtime_or_not = 1
        and us.data_type = dpt.data_type
        and us.data_type = 0
        <if test="userId != null and userId != '' ">
            and us.user_id = #{userId}
        </if>
        <if test="userName != null and userName != '' ">
            and us.user_Name LIKE CONCAT("%",#{userName},"%")
        </if>
        <if test="departId != null and departId != '' ">
            and dpt.depart_id = #{departId}
        </if>
        <if test="departName != null and departName != '' ">
            and dpt.depart_name LIKE CONCAT("%",#{departName},"%")
        </if>
        <if test="startTime != null">
            and ck.checkin_time &gt;= #{startTime}
        </if>
        <if test="stopTime != null ">
            and ck.checkin_time &lt;= #{stopTime}
        </if>
        order by us.depart_id,us.user_name ,ck.checkin_time
    </select>

    <select id="queryYROverTimeData" resultType="cn.com.ctop.oa.modules.entity.WechatAttendance">
        SELECT
        us.user_id AS userId,
        us.user_name AS userName,
        dpt.depart_id AS departId,
        dpt.depart_name AS departName,
        DATE_FORMAT(ck.checkin_time,'%Y-%m-%d') AS checkinDate,
        CONCAT(ck.overtime,' h') AS overtime
        FROM ctop_wechat_checkin_data as ck
        LEFT JOIN ctop_wechat_user_list as us ON ck.user_id = us.user_id
        LEFT JOIN ctop_wechat_department as dpt ON dpt.depart_id = us.depart_id
        WHERE ck.overtime_or_not = 1
        and us.data_type = dpt.data_type
        and us.data_type = 1
        <if test="userId != null and userId != '' ">
            and us.user_id = #{userId}
        </if>
        <if test="userName != null and userName != '' ">
            and us.user_Name LIKE CONCAT("%",#{userName},"%")
        </if>
        <if test="departId != null and departId != '' ">
            and dpt.depart_id = #{departId}
        </if>
        <if test="departName != null and departName != '' ">
            and dpt.depart_name LIKE CONCAT("%",#{departName},"%")
        </if>
        <if test="startTime != null">
            and ck.checkin_time &gt;= #{startTime}
        </if>
        <if test="stopTime != null ">
            and ck.checkin_time &lt;= #{stopTime}
        </if>
        order by us.depart_id,us.user_name ,ck.checkin_time
    </select>

</mapper>