<?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">

<!-- UIWS 이식: 근무현황 피벗 집계. 원천 = tb_uiws_worklog_dtl + tb_uiws_worklog.
     라벨: 근무자=mes_user.display_name(writer_id=username 조인, 없으면 writer_id),
           근무처=company_id, 근무상태/유형=코드값(공통코드 미이식). -->
<mapper namespace="com.zioinfo.mes.uiws.stats.mapper.StatsMapper">

    <select id="personalWork" resultType="map">
        SELECT COALESCE(u.display_name, w.writer_id) AS worker,
               w.work_status_cd                   AS work_status,
               d.work_type_cd                     AS work_type,
               d.company_id                       AS dyn,
               COUNT(*)                           AS cnt
        FROM tb_uiws_worklog_dtl d
        JOIN tb_uiws_worklog w   ON w.worklog_id = d.worklog_id
        LEFT JOIN mes_user u  ON u.username   = w.writer_id
        WHERE w.work_date BETWEEN #{from} AND #{to}
          <if test="!scopeAll">
              AND w.writer_id IN
              <foreach collection="ownerIds" item="oid" open="(" separator="," close=")">#{oid}</foreach>
          </if>
          <if test="userId != null">AND w.writer_id = #{userId}</if>
        GROUP BY COALESCE(u.display_name, w.writer_id), w.work_status_cd, d.work_type_cd, d.company_id
        ORDER BY worker, work_status, work_type, dyn
    </select>

    <select id="companyWork" resultType="map">
        SELECT d.company_id                       AS company,
               d.work_type_cd                     AS work_type,
               COALESCE(u.display_name, w.writer_id) AS dyn,
               COUNT(*)                           AS cnt
        FROM tb_uiws_worklog_dtl d
        JOIN tb_uiws_worklog w   ON w.worklog_id = d.worklog_id
        LEFT JOIN mes_user u  ON u.username   = w.writer_id
        WHERE w.work_date BETWEEN #{from} AND #{to}
          <if test="!scopeAll">
              AND w.writer_id IN
              <foreach collection="ownerIds" item="oid" open="(" separator="," close=")">#{oid}</foreach>
          </if>
          <if test="companyId != null">AND d.company_id = #{companyId}</if>
          <if test="userId != null">AND w.writer_id = #{userId}</if>
        GROUP BY d.company_id, d.work_type_cd, COALESCE(u.display_name, w.writer_id)
        ORDER BY company, work_type, dyn
    </select>

</mapper>
