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

package com.nokia.tsl_data.dao;

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

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

@Mapper
public interface ManagementDetailMapper {

        /**
         * 投诉清单分日期分地市计数 按账期查询
         */
        // 20240611 更新，增加类型限制
        @Select("select compl_area_local, substring(acct_date from 7 for 2) as day_id, count(1) as num " + //
                        " from tsl_data.management_detail\r\n" + //
                        " where stat_day_id = #{day}  and origin = 'mobile_complaint_day' " +
                        " group by compl_area_local, substring(acct_date from 7 for 2)\r\n" + //
                        " order by compl_area_local, substring(acct_date from 7 for 2)")
        List<Map<String, Object>> selectCityTslForMonth(@Param("day") String day);

        /**
         * 投诉清单全省分日计数 按账期查询
         */
        @Select("select substring(acct_date from 7 for 2) as day_id, count(1) as num" + //
                        " from tsl_data.management_detail\r\n" + //
                        " where stat_day_id = #{day} and origin = 'mobile_complaint_day' " + //
                        " group by substring(acct_date from 7 for 2)\r\n" + //
                        " order by substring(acct_date from 7 for 2)")
        List<Map<String, Object>> selectAllTslForMonth(@Param("day") String day);

        /**
         * 投诉清单地市总数 按账期查询
         */
        @Select("select compl_area_local, count(1) as num " + //
                        " from tsl_data.management_detail\r\n" + //
                        " where stat_day_id = #{day} and origin = 'mobile_complaint_day' " + //
                        " group by compl_area_local\r\n" + //
                        " order by compl_area_local")
        List<Map<String, Object>> selectCityAllForMonth(@Param("day") String day);

        /**
         * 全省总数 按账期查询
         */
        @Select("select count(1) as num\r\n" + //
                        "from tsl_data.management_detail\r\n" + //
                        "where stat_day_id = #{day} and origin = 'mobile_complaint_day' ")
        int selectAllForMonth(@Param("day") String day);

        /**
         * 从 河北_CEM移网质量投诉明细 mobile_complaint_day 中同步数据
         */
        @Insert("insert into tsl_data.management_detail (origin, stat_day_id, acct_date, sheet_no," +
                        " compl_area_local, compl_city_local, busi_no) " +
                        "select 'mobile_complaint_day' as origin, concat(month_id, day_id) as stat_day_id," +
                        " acct_date, sheet_no, compl_area_local, gis_city as compl_city_local, busi_no " +
                        " from tsl_data.mobile_complaint_day mcd \r\n" +
                        " where month_id = substring(#{day} from 1 for 6) and day_id = substring(#{day} from 7 for 2) ")
        int insertFromMobileComplaint(@Param("day") String day);

        /**
         * 从 河北客户体验管理智能定责投诉明细月累计接口日 complaint_details_fix_ywd_day 同步数据
         */
        @Insert("insert into tsl_data.management_detail (origin, stat_day_id, acct_date, sheet_no," +
                        " compl_area_local, compl_city_local, busi_no) " +
                        "select 'complaint_details_fix_ywd_day' as origin, concat(month_id, day_id) as stat_day_id," +
                        " concat(month_id1, day_id1) as acct_date, sheet_no, compl_area_local, compl_city_local, busi_no "
                        + " from tsl_data.complaint_details_fix_ywd_day cdfyd \r\n" +
                        " where duty_reason_id_day = '业务使用>>基础业务使用>>移网主被叫>>手机无法主被叫'\r\n" +
                        " and month_id = substring(#{day} from 1 for 6) and day_id = substring(#{day} from 7 for 2) ")
        int insertFromComplaintDetailsFixYwd(@Param("day") String day);

        /**
         * 按照账期统计某个账期的数据量
         */
        @Select("select count(1) from tsl_data.management_detail where stat_day_id = #{day} ")
        int selectCountofStatDay(@Param("day") String day);

        @Delete("delete from tsl_data.management_detail where stat_day_id = #{day}")
        int deleteForStatDay(@Param("day") String day);

        @Select("with t1 as (select compl_area_local, busi_no from tsl_data.management_detail md  \n" + //
                        " where stat_day_id = #{day}), \n" + //
                        " t2 as (select distinct * from t1), \n" + //
                        " t3 as (select compl_area_local, count(1) as total_num from t1 group by compl_area_local), \n"
                        + " t4 as (select compl_area_local, count(1) as distinct_num from t2 group by compl_area_local), \n"
                        + " t5 as (select t3.compl_area_local, t3.total_num, t3.total_num - t4.distinct_num::float8 as repeat_num, \n"
                        + " (t3.total_num - t4.distinct_num) / t3.total_num::float8 as repeat_ratio from T3, t4 \n" + //
                        " where t3.compl_area_local = t4.compl_area_local) \n" + //
                        " select '全省' as compl_area_local, sum(total_num) as total_num, sum(repeat_num) as repeat_num, \n"
                        + " sum(repeat_num) / sum(total_num)::float8 as repeat_ratio from t5 union select * from t5 ")
        List<Map<String, Object>> selectRepeatTsCountForDay(String day);

        /**
         * 20241204 修改
         * 使用同一个账期的数据，提取重复工单数据
         */
        @Select("with t1 as (select compl_area_local, busi_no from tsl_data.management_detail md  \n" + //
                        " where stat_day_id = #{statDayId} and acct_date <= #{day} ), \n" + //
                        " t2 as (select distinct * from t1), \n" + //
                        " t3 as (select compl_area_local, count(1) as total_num from t1 group by compl_area_local), \n"
                        + " t4 as (select compl_area_local, count(1) as distinct_num from t2 group by compl_area_local), \n"
                        + " t5 as (select t3.compl_area_local, t3.total_num, t3.total_num - t4.distinct_num::float8 as repeat_num, \n"
                        + " (t3.total_num - t4.distinct_num) / t3.total_num::float8 as repeat_ratio from T3, t4 \n" + //
                        " where t3.compl_area_local = t4.compl_area_local) \n" + //
                        " select '全省' as compl_area_local, sum(total_num) as total_num, sum(repeat_num) as repeat_num, \n"
                        + " sum(repeat_num) / sum(total_num)::float8 as repeat_ratio from t5 union select * from t5 ")
        List<Map<String, Object>> selectRepeatTsCountForDayAndStatDayId(@Param("day") String day, @Param("statDayId") String statDayId);
}