<?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.ruoyi.excel.wecom.mapper.DepartmentStatisticsDataMapper">

    <!-- 根据日期查询统计数据 -->
    <select id="selectByDate" resultType="com.ruoyi.excel.wecom.domain.DepartmentStatisticsData">
        SELECT *
        FROM department_statistics_data
        WHERE  corp_id = #{corpId} and stat_date = #{date}
        ORDER BY department_path, sort_no
    </select>

    <!-- 根据部门路径和日期范围查询统计数据 -->
    <select id="selectByDepartmentAndDateRange" resultType="com.ruoyi.excel.wecom.domain.DepartmentStatisticsData">
        SELECT *
        FROM department_statistics_data
        WHERE corp_id = #{corpId} and department_path = #{departmentPath}
        <if test="startDate != null">
            AND stat_date &gt;= #{startDate}
        </if>
        <if test="endDate != null">
            AND stat_date &lt;= #{endDate}
        </if>
        ORDER BY stat_date, sort_no
    </select>

    <!-- 查询指定部门的历史累计数据（总承接数） -->
    <select id="selectHistoricalAcceptSum" resultType="map">
        SELECT
            SUM(daily_total_accepted) as total_value,
            COUNT(*) as record_count
        FROM department_statistics_data
        WHERE corp_id = #{corpId} and department_path = #{departmentPath}
    </select>

    <!-- 查询指定部门的历史累计数据（总成单数） -->
    <select id="selectHistoricalOrderSum" resultType="map">
        SELECT
            SUM(daily_total_orders) as total_value,
            COUNT(*) as record_count
        FROM department_statistics_data
        WHERE corp_id = #{corpId} and department_path = #{departmentPath}
    </select>

    <!-- 查询部门统计数据VO列表(用于导出) -->
    <select id="selectDepartmentStatisticsDataVOList" resultType="com.ruoyi.excel.wecom.vo.DepartmentStatisticsDataVO">
        SELECT
        stat_date as statDate,
        department_path as departmentPath,
        daily_total_accepted as dailyTotalAccepted,
        daily_total_orders as dailyTotalOrders,
        daily_conversion_rate as dailyConversionRate,
        daily_timely_order_ratio as dailyTimelyOrderRatio,
        daily_non_timely_order_ratio as dailyNonTimelyOrderRatio,
        CASE 
            WHEN parent_daily_count > 0 
            THEN ROUND(parent_daily_order_count * 100.0 / parent_daily_count, 2)
            ELSE 0 
        END as dailyParentOrderRate,
        CASE 
            WHEN student_daily_count > 0 
            THEN ROUND(student_daily_order_count * 100.0 / student_daily_count, 2)
            ELSE 0 
        END as dailyStudentOrderRate,
        sort_no as sortNo
        FROM department_statistics_data
        <where>
            corp_id = #{corpId}
            <if test="startDate != null">
                AND stat_date &gt;= #{startDate}
            </if>
            <if test="endDate != null">
                AND stat_date &lt;= #{endDate}
            </if>
            <if test="departmentPath != null and departmentPath != ''">
                AND department_path LIKE CONCAT(#{departmentPath}, '%')
            </if>
        </where>
        ORDER BY stat_date DESC, department_path, sort_no
    </select>
    <select id="selectDepartmentStatisticsDataList"
            resultType="com.ruoyi.excel.wecom.domain.DepartmentStatisticsData">
        SELECT
            stat_date as statDate,
            department_path as departmentPath,
            daily_total_accepted as dailyTotalAccepted,
            daily_total_orders as dailyTotalOrders,
            daily_conversion_rate as dailyConversionRate,
            daily_timely_order_ratio as dailyTimelyOrderRatio,
            daily_non_timely_order_ratio as dailyNonTimelyOrderRatio,
            daily_timely_count as dailyTimelyCount,
            daily_non_timely_count as dailyNonTimelyCount,
            parent_daily_order_count as parentDailyOrderCount,
            parent_daily_count as parentDailyCount,
            student_daily_order_count as studentDailyOrderCount,
            student_daily_count as studentDailyCount,
            CASE 
                WHEN parent_daily_count > 0 
                THEN ROUND(parent_daily_order_count * 100.0 / parent_daily_count, 2)
                ELSE 0 
            END as dailyParentOrderRate,
            CASE 
                WHEN student_daily_count > 0 
                THEN ROUND(student_daily_order_count * 100.0 / student_daily_count, 2)
                ELSE 0 
            END as dailyStudentOrderRate,
            person_flag as personFlag,
            corp_name as corpName,
            department_name as departmentName,
            group_name as groupName,
            person_name as personName
        FROM department_statistics_data
        <where>
            corp_id = #{corpId}
            <if test="startDate != null">
                AND stat_date &gt;= #{startDate}
            </if>
            <if test="endDate != null">
                AND stat_date &lt;= #{endDate}
            </if>
            <if test="departmentPath != null and departmentPath != ''">
                AND department_path LIKE CONCAT(#{departmentPath}, '%')
            </if>
        </where>
        ORDER BY stat_date DESC, department_path, sort_no
    </select>

    <select id="getSummary" resultType="java.util.Map">
        select sum(daily_total_orders) as totalOrders,
               sum(ifnull(daily_total_accepted,0)+ifnull(manager_accepted,0)) as totalIns
        from department_statistics_data
        <where>
            corp_id = #{corpId} and person_flag = true
            <if test="startDate != null">
                AND stat_date &gt;= #{startDate}
            </if>
            <if test="endDate != null">
                AND stat_date &lt;= #{endDate}
            </if>
            <if test="departmentPath != null and departmentPath != ''">
                AND department_path = #{departmentPath}
            </if>
        </where>
    </select>

    <select id="selectDepartmentStatisticsDataAggregatedList"
            resultType="com.ruoyi.excel.wecom.domain.DepartmentStatisticsData">
        SELECT
            department_path as departmentPath,
            SUM(daily_total_accepted) as dailyTotalAccepted,
            SUM(daily_total_orders) as dailyTotalOrders,
            CASE 
                WHEN SUM(daily_total_accepted) > 0
                THEN ROUND(SUM(daily_total_orders) * 100.0 / SUM(daily_total_accepted), 2)
                ELSE 0 
            END as dailyConversionRate,
            CASE 
                WHEN SUM(daily_total_orders) > 0 
                THEN ROUND(SUM(IFNULL(daily_timely_count, 0)) * 100.0 / SUM(daily_total_orders), 2)
                ELSE 0 
            END as dailyTimelyOrderRatio,
            CASE 
                WHEN SUM(daily_total_orders) > 0 
                THEN ROUND(SUM(IFNULL(daily_non_timely_count, 0)) * 100.0 / SUM(daily_total_orders), 2)
                ELSE 0 
            END as dailyNonTimelyOrderRatio,
            SUM(IFNULL(daily_timely_count, 0)) as dailyTimelyCount,
            SUM(IFNULL(daily_non_timely_count, 0)) as dailyNonTimelyCount,
            SUM(IFNULL(parent_daily_order_count, 0)) as parentDailyOrderCount,
            SUM(IFNULL(parent_daily_count, 0)) as parentDailyCount,
            SUM(IFNULL(student_daily_order_count, 0)) as studentDailyOrderCount,
            SUM(IFNULL(student_daily_count, 0)) as studentDailyCount,
            CASE 
                WHEN SUM(IFNULL(parent_daily_count, 0)) > 0 
                THEN ROUND(SUM(IFNULL(parent_daily_order_count, 0)) * 100.0 / SUM(IFNULL(parent_daily_count, 0)), 2)
                ELSE 0 
            END as dailyParentOrderRate,
            CASE 
                WHEN SUM(IFNULL(student_daily_count, 0)) > 0 
                THEN ROUND(SUM(IFNULL(student_daily_order_count, 0)) * 100.0 / SUM(IFNULL(student_daily_count, 0)), 2)
                ELSE 0 
            END as dailyStudentOrderRate,
            person_flag as personFlag,
            corp_name as corpName,
            department_name as departmentName,
            group_name as groupName,
            person_name as personName,
            MIN(sort_no) as sortNo
        FROM department_statistics_data
        <where>
            corp_id = #{corpId}
            <if test="startDate != null">
                AND stat_date &gt;= #{startDate}
            </if>
            <if test="endDate != null">
                AND stat_date &lt;= #{endDate}
            </if>
            <if test="departmentPath != null and departmentPath != ''">
                AND department_path LIKE CONCAT(#{departmentPath}, '%')
            </if>
        </where>
        GROUP BY department_path, person_flag, corp_name, department_name, group_name, person_name
        ORDER BY department_path, sortNo
    </select>

    <select id="selectDepartmentStatisticsDataVOAggregatedList" resultType="com.ruoyi.excel.wecom.vo.DepartmentStatisticsDataVO">
        SELECT
            department_path as departmentPath,
            SUM(daily_total_accepted) as dailyTotalAccepted,
            SUM(daily_total_orders) as dailyTotalOrders,
            CASE 
                WHEN SUM(daily_total_accepted) > 0 
                THEN ROUND(SUM(daily_total_orders) * 100.0 / SUM(daily_total_accepted), 2)
                ELSE 0 
            END as dailyConversionRate,
            CASE 
                WHEN SUM(daily_total_orders) > 0 
                THEN ROUND(SUM(IFNULL(daily_timely_count, 0)) * 100.0 / SUM(daily_total_orders), 2)
                ELSE 0 
            END as dailyTimelyOrderRatio,
            CASE 
                WHEN SUM(daily_total_orders) > 0 
                THEN ROUND(SUM(IFNULL(daily_non_timely_count, 0)) * 100.0 / SUM(daily_total_orders), 2)
                ELSE 0 
            END as dailyNonTimelyOrderRatio,
            CASE 
                WHEN SUM(IFNULL(parent_daily_count, 0)) > 0 
                THEN ROUND(SUM(IFNULL(parent_daily_order_count, 0)) * 100.0 / SUM(IFNULL(parent_daily_count, 0)), 2)
                ELSE 0 
            END as dailyParentOrderRate,
            CASE 
                WHEN SUM(IFNULL(student_daily_count, 0)) > 0 
                THEN ROUND(SUM(IFNULL(student_daily_order_count, 0)) * 100.0 / SUM(IFNULL(student_daily_count, 0)), 2)
                ELSE 0 
            END as dailyStudentOrderRate,
            MIN(sort_no) as sortNo
        FROM department_statistics_data
        <where>
            corp_id = #{corpId}
            <if test="startDate != null">
                AND stat_date &gt;= #{startDate}
            </if>
            <if test="endDate != null">
                AND stat_date &lt;= #{endDate}
            </if>
            <if test="departmentPath != null and departmentPath != ''">
                AND department_path LIKE CONCAT(#{departmentPath}, '%')
            </if>
        </where>
        GROUP BY department_path
        ORDER BY department_path, sortNo
    </select>

</mapper>
