<?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.qmth.sop.business.mapper.TBDingMapper">

        <select id="query" resultType="com.qmth.sop.business.bean.result.TBDingSubmitQueryResult">

            SELECT
            d.id,
            bs.NAME service,
            d.sop_no,
            c.NAME custom,
            ua.province,
            ua.city,
            d.scan_start_time scanStartTime,
            d.scan_end_time scanEndTime,
            d.mark_paper_start_time markPaperStartTime,
            d.mark_paper_end_time markPaperEndTime,
            ua.name user_name,
            ua.`code` user_no,
            d.role_name as roleName,
            s.NAME supplier,
            d.actual_days attendance,
            d.weekdays weekdays,
            d.weekends weekends,
            d.legal_holidays holidays,
            d.work_hours work_hours,
            d.violation_days violation_days,
            d.submit_status `status`,
            sub.real_name submitter,
            d.submit_time submission_time

            FROM
            t_b_ding_submit d
            LEFT JOIN t_b_user_archives ua ON d.user_archives_id = ua.id
            LEFT JOIN t_b_user_archives_supplier us ON us.user_archives_id = ua.id
            LEFT JOIN t_b_service bs ON bs.id = d.service_id
            LEFT JOIN t_b_crm crm ON crm.crm_no = d.crm_no
            LEFT JOIN sys_custom c ON c.id = crm.custom_id
            LEFT JOIN sys_user sub ON sub.id = d.submit_user_id
            LEFT JOIN sys_supplier s ON s.id = us.supplier_id

            <where>
                AND bs.status = 'PUBLISH'
                <if test="serviceId != null and serviceId != ''">
                    and d.service_id = #{serviceId}
                </if>
                <if test="status != null and status != ''">
                    and d.submit_status = #{status}
                </if>
                <if test="createId != null and createId != ''">
                    and d.submit_user_id = #{createId}
                </if>
                <if test="userName != null and userName != ''">
                    and ua.name like concat('%', #{userName} , '%')
                </if>
                <if test="supplierId != null and supplierId != ''">
                    and us.supplier_id = #{supplierId}
                </if>
                <if test="custom != null and custom != ''">
                    and c.NAME like concat('%', #{custom} , '%')
                </if>
                <if test="sopNo != null and sopNo != ''">
                    and d.sop_no like concat('%', #{sopNo} , '%')
                </if>
                <if test="days != null and days != '' ">
                    <![CDATA[
                   and d.violation_days > #{days}
                   ]]>
                </if>
                <if test="dpr != null and !dpr.hasAdmin and !dpr.hasPmo">
                    <choose>
                        <when test="dpr.hasBusiness">
                            AND bs.service_lead_id = #{dpr.requestUserId}
                        </when>
                        <otherwise>
                            <choose>
                                <when test="dpr.hasRegionManager">
                                    AND crm.lead_id = #{dpr.requestUserId}
                                </when>
                                <otherwise>
                                    <choose>
                                        <when test="dpr.hasRegionCoordinator">
                                            AND crm.region_coordinator_id = #{dpr.requestUserId}
                                        </when>
                                    </choose>
                                </otherwise>
                            </choose>
                        </otherwise>
                    </choose>
                </if>
            </where>
            order by  d.submit_time desc
        </select>




    <select id="count" resultType="com.qmth.sop.business.bean.result.TBDingCountResult">
        SELECT
        count( d.id ) total,
        SUM( CASE WHEN d.submit_status='ALREADY_SUBMIT' THEN 1 ELSE 0 END ) submitted,
        SUM( CASE WHEN d.submit_status='WILL_SUBMIT' THEN 1 ELSE 0 END ) un_submitted,
        SUM( CASE WHEN d.submit_status='ALREADY_SUBMIT' THEN d.actual_days ELSE 0 END ) all_days,
        SUM( CASE WHEN d.submit_status='ALREADY_SUBMIT'  THEN d.work_hours ELSE 0 END ) all_hours
        FROM
        t_b_ding_submit d
        LEFT JOIN t_b_user_archives ua ON d.user_archives_id = ua.id
        LEFT JOIN t_b_user_archives_supplier us ON us.user_archives_id = ua.id
        LEFT JOIN t_b_crm crm ON crm.crm_no = d.crm_no
        LEFT JOIN sys_custom c ON c.id = crm.custom_id
        LEFT JOIN t_b_service bs ON bs.id = d.service_id
        <where>
            AND bs.status = 'PUBLISH'
            <if test="serviceId != null and serviceId != ''">
                and d.service_id = #{serviceId}
            </if>
            <if test="status != null and status != ''">
                and d.submit_status = #{status}
            </if>
            <if test="createId != null and createId != ''">
                and d.submit_user_id = #{createId}
            </if>
            <if test="userName != null and userName != ''">
                and ua.name like concat('%', #{userName} , '%')
            </if>
            <if test="supplierId != null and supplierId != ''">
                and us.supplier_id = #{supplierId}
            </if>
            <if test="custom != null and custom != ''">
                and c.name like concat('%', #{custom} , '%')
            </if>
            <if test="sopNo != null and sopNo != ''">
                and d.sop_no like concat('%', #{sopNo} , '%')
            </if>
            <if test="dpr != null and !dpr.hasAdmin and !dpr.hasPmo">
                <choose>
                    <when test="dpr.hasBusiness">
                        AND EXISTS(SELECT 1 FROM t_b_service tbs WHERE d.service_id = tbs.id AND tbs.service_lead_id = #{dpr.requestUserId})
                    </when>
                    <otherwise>
                        <choose>
                            <when test="dpr.hasRegionManager">
                                AND crm.lead_id = #{dpr.requestUserId}
                            </when>
                            <otherwise>
                                <choose>
                                    <when test="dpr.hasRegionCoordinator">
                                        AND crm.region_coordinator_id = #{dpr.requestUserId}
                                    </when>
                                </choose>
                            </otherwise>
                        </choose>
                    </otherwise>
                </choose>
            </if>
        </where>
    </select>

    <select id="attendanceQuery" resultType="com.qmth.sop.business.bean.result.TBDingAttendanceResult">
        SELECT * from (
        SELECT
        d.sop_no sop_no,
        bs.NAME service,
        c.NAME custom,
        ua.province,
        ua.city,
        ua.name user_name,
        ua.`code` user_no,
        d.sop_role_type as sopRoleType,
        s.NAME supplier,
        d.sign_date,
        d.type,
        d.sign_time,
        CASE WHEN d.sign_time IS NOT NULL THEN 'NORMAL' ELSE 'ABNORMAL' END STATUS,
        d.address,
        d.axis_x AS axisX,
        d.axis_y AS axisY,
        d.service_id,
        s.id supplier_id ,
        d.user_id,
        bs.service_lead_id AS serviceUnitLeaderId,
        crm.lead_id AS regionManagerId,
        crm.region_coordinator_id AS coordinatorId,
        bs.status serviceStatus
        FROM
        (
        SELECT d.*,d.axis_in_x AS axis_x, d.axis_in_y AS axis_y,d.sign_in_address address, d.sign_in_time sign_time,'IN' AS type FROM t_b_ding d UNION ALL
        SELECT d.*,d.axis_out_x AS axis_x, d.axis_out_y AS axis_y,d.sign_out_address address, d.sign_out_time sign_time,'OUT' AS type FROM t_b_ding d
        where
        d.sign_out_time is not null  or
        (d.sign_out_time is  null and
        CONCAT(d.sign_year,d.sign_date) != date_format(now(),'%Y年%m月%日'))
        ) d
        LEFT JOIN t_b_user_archives ua ON ua.id = d.user_archives_id
        LEFT JOIN t_b_user_archives_supplier us ON us.user_archives_id = ua.id
        LEFT JOIN t_b_service bs ON bs.id = d.service_id
        LEFT JOIN t_b_crm crm ON crm.crm_no = d.crm_no
        LEFT JOIN sys_custom c ON c.id = crm.custom_id
        LEFT JOIN sys_supplier s ON s.id = us.supplier_id
        ) a
        <where>
            and a.serviceStatus = 'PUBLISH'
            <if test="serviceId != null and serviceId != ''">
                and a.service_id = #{serviceId}
            </if>
            <if test="userName != null and userName != ''">
                and a.user_name like concat('%', #{userName} , '%')
            </if>
            <if test="startTime != null and startTime != '' ">
                <![CDATA[
               and a.sign_time >= #{startTime}
               ]]>
            </if>
            <if test="endTime != null and endTime != ''">
                <![CDATA[
                and a.sign_time <= #{endTime}
                ]]>
            </if>
            <if test="supplierId != null and supplierId != ''">
                and a.supplier_id = #{supplierId}
            </if>
            <if test="custom != null and custom != ''">
                and a.custom like concat('%', #{custom} , '%')
            </if>
            <if test="sopNo != null and sopNo != ''">
                and a.sop_no like concat('%', #{sopNo} , '%')
            </if>
            <if test="type != null and type != ''">
                and a.type = #{type}
            </if>
            <if test="status != null and status != ''">
                and a.status = #{status}
            </if>
            <if test="abnormal != null and abnormal != ''">
                and a.user_id in (    SELECT x.user_id from(

                SELECT

                d.user_id,sum(case when d.sign_time is null then 1 else 0 end) count
                FROM
                (
                SELECT d.*,d.axis_in_x AS axis_x, d.axis_in_y AS axis_y,d.sign_in_address address, d.sign_in_time sign_time,'IN' AS type FROM t_b_ding d UNION ALL
                SELECT d.*,d.axis_out_x AS axis_x, d.axis_out_y AS axis_y,d.sign_out_address address, d.sign_out_time sign_time,'OUT' AS type FROM t_b_ding d
                where
                d.sign_out_time is not null  or
                (d.sign_out_time is  null and
                CONCAT(d.sign_year,d.sign_date) != date_format(now(),'%Y年%m月%日'))
                ) d

                GROUP BY d.user_id

                )x where x.count>=#{abnormal})
            </if>
            <if test="dpr != null and !dpr.hasAdmin and !dpr.hasPmo">
                <choose>
                    <when test="dpr.hasBusiness">
                        AND a.serviceUnitLeaderId = #{dpr.requestUserId}
                    </when>
                    <otherwise>
                        <choose>
                            <when test="dpr.hasRegionManager">
                                AND a.regionManagerId = #{dpr.requestUserId}
                            </when>
                            <otherwise>
                                <choose>
                                    <when test="dpr.hasRegionCoordinator">
                                        AND a.coordinatorId = #{dpr.requestUserId}
                                    </when>
                                </choose>
                            </otherwise>
                        </choose>
                    </otherwise>
                </choose>
            </if>
        </where>
        order by a.sign_date desc,a.sign_time desc
    </select>

    <select id="attendanceCount" resultType="com.qmth.sop.business.bean.result.TBDingAttendanceCountResult">
        SELECT
        count(d.id)*2-sum( CASE WHEN d.sign_out_time IS NULL and  CONCAT(d.sign_year,d.sign_date) = date_format(now(),'%Y年%m月%日') THEN 1 ELSE 0 END ) total,
        sum( CASE WHEN d.sign_in_time IS NULL THEN 1 ELSE 0 END )+ sum( CASE WHEN d.sign_out_time IS NULL and  CONCAT(d.sign_year,d.sign_date) != date_format(now(),'%Y年%m月%日') THEN 1 ELSE 0 END ) abnormal,
        SUM( CASE WHEN d.sign_in_time IS NOT NULL AND d.sign_out_time IS NOT NULL THEN 1 ELSE 0 END ) all_days,
        SUM( CASE WHEN d.sign_in_time IS NOT NULL AND d.sign_out_time IS NOT NULL THEN
        (d.sign_out_time-d.sign_in_time)
        ELSE 0
        END
        )/1000/60/60 all_hours
        FROM
        t_b_ding d
        LEFT JOIN sys_user u ON u.id = d.user_id
        LEFT JOIN t_b_user_archives ua ON u.mobile_number = ua.mobile_number
        LEFT JOIN t_b_user_archives_supplier us ON us.user_archives_id = ua.id
        LEFT JOIN t_b_crm crm ON crm.crm_no = d.crm_no
        LEFT JOIN sys_custom c ON c.id = crm.custom_id
        LEFT JOIN t_b_service tbs ON d.service_id = tbs.id
        <where>
            and tbs.status = 'PUBLISH'
            <if test="serviceId != null and serviceId != ''">
                and d.service_id = #{serviceId}
            </if>
            <if test="userName != null and userName != ''">
                and u.real_name like concat('%', #{userName} , '%')
            </if>
            <if test="startTime != null and startTime != '' ">
                <![CDATA[
               and d.sign_date >= #{startTime}
               ]]>
            </if>
            <if test="endTime != null and endTime != ''">
                <![CDATA[
                and d.sign_date <= #{endTime}
                ]]>
            </if>
            <if test="supplierId != null and supplierId != ''">
                and us.supplier_id = #{supplierId}
            </if>
            <if test="custom != null and custom != ''">
                and c.name like concat('%', #{custom} , '%')
            </if>
            <if test="sopNo != null and sopNo != ''">
                and d.sop_no like concat('%', #{sopNo} , '%')
            </if>
            <if test="dpr != null and !dpr.hasAdmin and !dpr.hasPmo">
                <choose>
                    <when test="dpr.hasBusiness">
                        AND tbs.service_lead_id = #{dpr.requestUserId}
                    </when>
                    <otherwise>
                        <choose>
                            <when test="dpr.hasRegionManager">
                                AND crm.lead_id = #{dpr.requestUserId}
                            </when>
                            <otherwise>
                                <choose>
                                    <when test="dpr.hasRegionCoordinator">
                                        AND crm.region_coordinator_id = #{dpr.requestUserId}
                                    </when>
                                </choose>
                            </otherwise>
                        </choose>
                    </otherwise>
                </choose>
            </if>
        </where>
    </select>

    <select id="findDingResultByUserId" resultType="com.qmth.sop.business.bean.result.DingElementResult">
        SELECT
            tbua.id AS userArchivesId,
            tbua.name AS userArchivesName,
            tbua.base_photo_path AS basePhotoPath,
            sdg.sign_in_time AS signInTimeScope,
            sdg.sign_out_time AS signOutTimeScope,
            sdg.reissue_card_count AS reissueCardCount,
            sdg.face_open AS faceOpen,
            sdg.id AS dingGroupId
        FROM
            t_b_user_archives tbua
                LEFT JOIN
            sys_user su ON tbua.mobile_number = su.mobile_number
                LEFT JOIN
            t_b_user_archives_supplier tbuas ON tbuas.user_archives_id = tbua.id
                LEFT JOIN
            sys_supplier ss ON tbuas.supplier_id = ss.id
                LEFT JOIN
            sys_ding_group sdg ON sdg.supplier_id = ss.id
        <where>
            <if test="userId != null">
                AND su.id = #{userId}
            </if>
            <if test="serviceUnitId != null">
                AND sdg.service_id = #{serviceUnitId}
            </if>
        </where>
    </select>
    <select id="findDateAndStatusBySopNo" resultType="com.qmth.sop.business.entity.TFFlowApprove">
        SELECT
            tffa.flow_id AS flowId,
            tffa.status AS status,
            tffa.create_time AS createTime,
            tffa.update_time AS updateTime
        FROM
            t_f_flow_approve tffa
                LEFT JOIN
            t_f_custom_flow_entity tfcfe ON tffa.flow_id = tfcfe.flow_id
        <where>
            <if test="sopNo != null and sopNo != ''">
                AND tfcfe.code = #{sopNo}
            </if>
        </where>
    </select>
    <select id="findDingExceptionApplyCount" resultType="java.lang.Integer">
        SELECT
            COUNT(*)
        FROM
            t_b_ding_apply
        <where>
            AND (approve IS NULL OR approve = 'PASS')
            <if test="userId != null">
                AND create_id = #{userId}
            </if>
            <if test="serviceId != null and serviceId != ''">
                AND service_id = #{serviceId}
            </if>
            <choose>
                <when test="sopNo != null and sopNo != ''">
                    AND sop_no = #{sopNo}
                </when>
                <otherwise>
                    AND sop_no IS NULL
                </otherwise>
            </choose>
        </where>
    </select>
</mapper>