<?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.fjhx.sale.mapper.dept.DeptPerfMapper">
    <select id="getList" resultType="com.fjhx.sale.entity.dept.vo.DeptPerfVo">
        SELECT dep.id,
               co.dept_id                      as companyId,
               co.dept_name                    as companyName,
               de.dept_id,
               de.dept_name,
               gr.dept_id                      AS groupId,
               gr.dept_name                    AS groupName,
               IFNULL(dep.years, #{dto.years}) AS years,
               dep.january,
               dep.february,
               dep.march,
               dep.april,
               dep.may,
               dep.june,
               dep.july,
               dep.august,
               dep.september,
               dep.october,
               dep.november,
               dep.december
        FROM sys_dept gr
                 LEFT JOIN sys_dept de ON gr.parent_id = de.dept_id
                 left join sys_dept co on de.parent_id = co.dept_id
                 LEFT JOIN dept_perf dep ON dep.group_id = gr.dept_id AND dep.years = #{dto.years}
        WHERE gr.type != 0
              AND FIND_IN_SET(100,gr.ancestors)
              AND not gr.jst_distributor is null
              AND gr.jst_distributor !=''
    </select>

    <select id="monthlyReporting" resultType="com.fjhx.sale.entity.DeptPerfReporting">
        SELECT t1.*,
               IFNULL(t2.sumContractAmount, 0)  AS sumContractAmount,
               IFNULL(t2.sumJstAmount, 0)       AS sumJstAmount,
               (SELECT GROUP_CONCAT(nick_name)
                FROM sys_user su
                         JOIN sys_user_role sur ON sur.user_id = su.user_id
                         JOIN sys_role sr ON sr.role_id = sur.role_id
                WHERE su.dept_id = t1.groupId
                  AND sr.role_key = 'salesman') AS saleUserName
        FROM (SELECT de.dept_id,
                     de.dept_name,
                     gr.dept_id   AS groupId,
                     gr.dept_name AS groupName,
                     gr.jst_distributor
              FROM sys_dept gr
                       LEFT JOIN sys_dept de ON gr.parent_id = de.dept_id
              WHERE gr.type != 0
		AND FIND_IN_SET( 100, gr.ancestors )
		AND NOT gr.jst_distributor IS NULL
		AND gr.jst_distributor != ''
              GROUP BY
                  gr.dept_id) t1
                 LEFT JOIN (SELECT t1.dept_id,
                                   IFNULL(sum(t1.sumContractAmount), 0) AS sumContractAmount,
                                   IFNULL(sum(t1.sumJstAmount), 0)      AS sumJstAmount
                            FROM (SELECT c.dept_id,
                                         IFNULL(sum(c.amount), 0) AS sumContractAmount,
                                         NULL                     AS sumJstAmount
                                  FROM contract c
                                  WHERE DATE_FORMAT(c.create_time, '%Y-%m') = DATE_FORMAT(#{dto.beginTime}, '%Y-%m')
                                  GROUP BY c.dept_id
                                  UNION ALL
                                  SELECT de.dept_id,
                                         NULL                       AS sumContractAmount,
                                         IFNULL(sum(joi.amount), 0) AS sumJstAmount
                                  FROM jst_order_info joi
                                           LEFT JOIN sys_dept de ON joi.drp_from = de.jst_distributor
                                  WHERE DATE_FORMAT(joi.created, '%Y-%m') = DATE_FORMAT(#{dto.beginTime}, '%Y-%m')
                                  GROUP BY de.dept_id) t1
                            GROUP BY t1.dept_id) t2 ON t2.dept_id = t1.groupId
    </select>
    <select id="getDayRepo" resultType="com.fjhx.sale.entity.DeptPerfReporting">
        SELECT t1.dept_id,
               t1.dayStr,
               sum(sumAmount)                       AS sumContractAmount,
               sum(sumJstAmount)                    AS sumJstAmount,
               (sum(sumAmount) + sum(sumJstAmount)) AS sumAmount
        FROM (SELECT c.dept_id,
                     DATE_FORMAT(c.create_time, '%Y-%m-%d') AS dayStr,
                     sum(c.amount)                          AS sumAmount,
                     NULL                                   AS sumJstAmount
              FROM contract c
              WHERE DATE_FORMAT(c.create_time, '%Y-%m') = DATE_FORMAT(#{dto.beginTime}, '%Y-%m')
                AND NOT c.dept_id IS NULL
              GROUP BY c.dept_id,
                       DATE_FORMAT(c.create_time, '%Y-%m-%d')
              UNION ALL
              SELECT de.dept_id,
                     DATE_FORMAT(joi.created, '%Y-%m-%d') AS dayStr,
                     NULL                                 AS sumAmount,
                     sum(joi.amount)                      AS sumJstAmount
              FROM jst_order_info joi
                       LEFT JOIN sys_dept de ON joi.drp_from = de.jst_distributor
              WHERE DATE_FORMAT(joi.created, '%Y-%m') = DATE_FORMAT(#{dto.beginTime}, '%Y-%m')
                AND NOT de.dept_id IS NULL
              GROUP BY de.dept_id,
                       DATE_FORMAT(joi.created, '%Y-%m-%d')) t1
        GROUP BY t1.dept_id,
                 t1.dayStr
    </select>

</mapper>
