tsl-data/src/main/java/com/nokia/tsl_data/dao/MobileComplaintMapper.java

package com.nokia.tsl_data.dao;

import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

@Mapper
public interface MobileComplaintMapper {
        /**
         * 统计某天入库工单数量
         */
        @Select("select count(1) from tsl_data.mobile_complaint_day " +
                        "where month_id = substring(#{day} from 1 for 6) and day_id = substring(#{day} from 7 for 2)")
        int selectCompCountForDay(String day);

        /**
         * 超时工单统计
         */
        @Select("with t1 as (select compl_area_local, is_timeout from tsl_data.mobile_complaint_day " +
                        " where month_id = substring(#{day} from 1 for 6) and day_id = substring(#{day} from 7 for 2)), "
                        +
                        "t2 as (select '全省' as compl_area_local, count(1) as total_num from t1), " +
                        "t3 as (select compl_area_local, count(1) as total_num from t1 group by compl_area_local), " +
                        "t4 as (select * from t2 union select * from t3), " +
                        "t5 as (select compl_area_local from t1 where is_timeout = '是'), " +
                        "t7 as (select '全省' as compl_area_local, count(1) as timeout_num from t5), " +
                        "t8 as (select compl_area_local, count(1) as timeout_num from t5 group by compl_area_local), " +
                        "t9 as (select * from t7 union select * from t8) " +
                        "select t4.compl_area_local, t4.total_num, t9.timeout_num, t9.timeout_num / t4.total_num::float8 as timeout_ratio "
                        +
                        "from t4, t9 where t4.compl_area_local = t9.compl_area_local")
        List<Map<String, Object>> selectTimeoutTsCountForDay(String day);

        /**
         * 处理时长统计
         */
        @Select("with t1 as (select compl_area_local, " +
                        "case when proce_time != '' then (extract('epoch' from to_timestamp( proce_time, 'YYYY-MM-DD HH24:MI:SS'))"
                        +
                        "          - extract('epoch' from to_timestamp(accept_time, 'YYYY-MM-DD HH24:MI:SS'))) / 3600" +
                        "     when is_online_complete = '是' then 0" +
                        "     else (extract('epoch' from to_timestamp(end_time, 'YYYY-MM-DD HH24:MI:SS'))" +
                        "          - extract('epoch' from to_timestamp(accept_time, 'YYYY-MM-DD HH24:MI:SS'))) / 3600 "
                        +
                        "     end as duration from tsl_data.mobile_complaint_day " +
                        "where month_id = substring(#{day} from 1 for 6) and day_id = substring(#{day} from 7 for 2)) "
                        +
                        "select compl_area_local, avg(duration)::float8 as avg_duration from t1 group by compl_area_local")
        List<Map<String, Object>> selectTsDurationForDay(String day);

        /**
         * 重复工单
         */
        @Select("with t1 as (select compl_area_local, busi_no from tsl_data.mobile_complaint_day " +
                        " where month_id = substring(#{day} from 1 for 6) and day_id = substring(#{day} from 7 for 2)), "
                        +
                        "t2 as (select distinct * from t1), " +
                        "t3 as (select compl_area_local, count(1) as total_num from t1 group by compl_area_local), " +
                        "t4 as (select compl_area_local, count(1) as distinct_num from t2 group by compl_area_local), "
                        +
                        "t5 as (select t3.compl_area_local, t3.total_num, t3.total_num - t4.distinct_num::float8 as repeat_num, "
                        +
                        "       (t3.total_num - t4.distinct_num) / t3.total_num::float8 as repeat_ratio from T3, t4 " +
                        "       where t3.compl_area_local = t4.compl_area_local) " +
                        "select '全省' as compl_area_local, sum(total_num) as total_num, sum(repeat_num) as repeat_num, "
                        +
                        "sum(repeat_num) / sum(total_num)::float8 as repeat_ratio from t5 union select * from t5 ")
        List<Map<String, Object>> selectRepeatTsCountForDay(String day);

        /**
         * 投诉清单按日按地市计数
         */
        @Select("select compl_area_local, substring(acct_date from 7 for 2) as day_id, count(1) as num " +
                        " from tsl_data.mobile_complaint_day where month_id = substring(#{day} from 1 for 6) " +
                        " and day_id = substring(#{day} from 7 for 2) group by compl_area_local, substring(acct_date from 7 for 2) "
                        +
                        " order by compl_area_local, substring(acct_date from 7 for 2)")
        List<Map<String, Object>> selectCityTslForMonth(String day);

        /**
         * 投诉清单全省计数
         */
        @Select("select substring(acct_date from 7 for 2) as day_id, count(1) as num " +
                        "from tsl_data.mobile_complaint_day where month_id = substring(#{day} from 1 for 6) " +
                        "and day_id = substring(#{day} from 7 for 2) group by substring(acct_date from 7 for 2) " +
                        "order by substring(acct_date from 7 for 2)")
        List<Map<String, Object>> selectAllTslForMonth(String day);

        /**
         * 投诉清单地市总数
         */
        @Select("select compl_area_local, count(1) as num from tsl_data.mobile_complaint_day " +
                        "where month_id = substring(#{day} from 1 for 6) and day_id = substring(#{day} from 7 for 2) " +
                        " group by compl_area_local order by compl_area_local")
        List<Map<String, Object>> selectCityAllForMonth(String day);

        /**
         * 投诉清单按月计算总数
         */
        @Select("select count(1) as num from tsl_data.mobile_complaint_day hdmc " +
                        "where month_id = substring(#{day} from 1 for 6) and day_id = substring(#{day} from 7 for 2)")
        int selectAllForMonth(String day);

        @Delete("delete from tsl_data.mobile_complaint_day where month_id = #{month_id}  and day_id = #{day_id} ")
        int deleteMobileComplaintForMonthIdAndDayId(@Param("month_id") String monthId, @Param("day_id") String dayId);

        /**
         * 删除某天的管理端投诉清单
         */
        default int deleteMobileCompForDay(String day) {
                return deleteMobileComplaintForMonthIdAndDayId(day.substring(0, 6), day.substring(6));
        }

        @Select("select count(1) from tsl_data.mobile_complaint_day where month_id = #{month_id}  and day_id = #{day_id} ")
        int countForDay(@Param("month_id") String monthId, @Param("day_id") String dayId);

        default int countForDay(String day) {
                return countForDay(day.substring(0, 6), day.substring(6));
        }

        // 3G简网投诉情况
        // 移网质量>>网络覆盖>>Vo-LTE>>未开通/不支撑Vo-LTE功能
        // 移网质量>>网络覆盖>>2/3G网络精简>>2/3G网络精简
        // 日定责问题分类 DUTY_REASON_ID_DAY
        // 地市按日统计 20240409 新增
        @Select("select compl_area_local, substring(acct_date from 7 for 2) as day_id, count(1) as num " +
                        "from tsl_data.mobile_complaint_day " +
                        "where month_id = #{month_id} and day_id = #{day_id} " +
                        "and duty_reason_id_day in ( " +
                        "'移网质量>>网络覆盖>>Vo-LTE>>未开通/不支撑Vo-LTE功能', " +
                        "'移网质量>>网络覆盖>>2/3G网络精简>>2/3G网络精简') " +
                        "group by compl_area_local, substring(acct_date from 7 for 2) " +
                        "order by compl_area_local, substring(acct_date from 7 for 2)")
        List<Map<String, Object>> selectCity3GjwForMonth(@Param("month_id") String monthId,
                        @Param("day_id") String dayId);

        default List<Map<String, Object>> selectCity3GjwForMonth(String day) {
                return selectCity3GjwForMonth(day.substring(0, 6), day.substring(6));
        }

        // 全省按日统计 20240409 新增
        @Select("select substring(acct_date from 7 for 2) as day_id, count(1) as num " +
                        "from tsl_data.mobile_complaint_day " +
                        "where month_id = #{month_id} and day_id = #{day_id} " +
                        "and duty_reason_id_day in ( " +
                        "'移网质量>>网络覆盖>>Vo-LTE>>未开通/不支撑Vo-LTE功能', " +
                        "'移网质量>>网络覆盖>>2/3G网络精简>>2/3G网络精简') " +
                        "group by substring(acct_date from 7 for 2) " +
                        "order by substring(acct_date from 7 for 2)")
        List<Map<String, Object>> selectAll3GjwGroupByDayForMonth(@Param("month_id") String monthId,
                        @Param("day_id") String dayId);

        default List<Map<String, Object>> selectAll3GjwGroupByDayForMonth(String day) {
                return selectAll3GjwGroupByDayForMonth(day.substring(0, 6), day.substring(6));
        }

        @Select("select compl_area_local, count(1) as num " +
                        "from tsl_data.mobile_complaint_day " +
                        "where month_id = #{month_id} and day_id = #{day_id} " +
                        "and duty_reason_id_day in ( " +
                        "'移网质量>>网络覆盖>>Vo-LTE>>未开通/不支撑Vo-LTE功能', " +
                        "'移网质量>>网络覆盖>>2/3G网络精简>>2/3G网络精简') " +
                        "group by compl_area_local " +
                        "order by compl_area_local ")
        List<Map<String, Object>> selectCityAll3GjwForMonth(@Param("month_id") String monthId,
                        @Param("day_id") String dayId);

        default List<Map<String, Object>> selectCityAll3GjwForMonth(String day) {
                return selectCityAll3GjwForMonth(day.substring(0, 6), day.substring(6));
        }

        @Select("select count(1)  from tsl_data.mobile_complaint_day " +
                        "where month_id = #{month_id} and day_id = #{day_id} " +
                        "and duty_reason_id_day in ( " +
                        "'移网质量>>网络覆盖>>Vo-LTE>>未开通/不支撑Vo-LTE功能', " +
                        "'移网质量>>网络覆盖>>2/3G网络精简>>2/3G网络精简')")
        int selectAll3GjwForMonth(@Param("month_id") String monthId, @Param("day_id") String dayId);

        default int selectAll3GjwForMonth(String day) {
                return selectAll3GjwForMonth(day.substring(0, 6), day.substring(6));
        }
}