package com.fs.company.mapper;

import com.fs.company.domain.CompanyTcmReport;
import com.fs.company.param.CompanyTcmReportListQueryParam;
import com.fs.company.vo.CompanyReportExportVO;
import com.fs.company.vo.CompanyTcmReportExportVO;
import com.fs.company.vo.CompanyTcmReportListVO;
import com.fs.company.vo.CompanyTcmStatisticsExportVO;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * 中医档期业绩报表Mapper接口
 *
 * @author fs
 * @date 2023-09-05
 */
public interface CompanyTcmReportMapper
{
    /**
     * 查询中医档期业绩报表
     *
     * @param id 中医档期业绩报表ID
     * @return 中医档期业绩报表
     */
    public CompanyTcmReport selectCompanyTcmReportById(Long id);

    /**
     * 查询中医档期业绩报表列表
     *
     * @param companyTcmReport 中医档期业绩报表
     * @return 中医档期业绩报表集合
     */
    public List<CompanyTcmReport> selectCompanyTcmReportList(CompanyTcmReport companyTcmReport);

    /**
     * 新增中医档期业绩报表
     *
     * @param companyTcmReport 中医档期业绩报表
     * @return 结果
     */
    public int insertCompanyTcmReport(CompanyTcmReport companyTcmReport);

    /**
     * 修改中医档期业绩报表
     *
     * @param companyTcmReport 中医档期业绩报表
     * @return 结果
     */
    public int updateCompanyTcmReport(CompanyTcmReport companyTcmReport);

    /**
     * 删除中医档期业绩报表
     *
     * @param id 中医档期业绩报表ID
     * @return 结果
     */
    public int deleteCompanyTcmReportById(Long id);

    /**
     * 批量删除中医档期业绩报表
     *
     * @param ids 需要删除的数据ID
     * @return 结果
     */
    public int deleteCompanyTcmReportByIds(Long[] ids);


    @Select({"<script> " +
            "select r.*,u.nick_name as user_nick_name,c.company_name,t.dept_name as deptName,s.name as scheduleName " +
            "from company_tcm_report r left join company_tcm_schedule s on r.schedule_id=s.id left join company c on c.company_id=r.company_id  left join company_user u on u.user_id=r.company_user_id " +
            " left join company_dept t on u.dept_id=t.dept_id  where 1=1 " +

//            "<if test = 'maps.companyId != null  '> " +
//            "and r.company_id = #{maps.companyId} " +
//            "</if>" +

            "<if test = 'maps.companyId != null   and maps.companyId != \"\"      '> " +
            "and r.company_id IN " +
            "<foreach collection=\"maps.companyId.split(',')\"  item='item' index='index'  open='(' separator=',' close=')'> #{item} </foreach>"+
            "</if>" +

            "<if test = 'maps.userNickName != null and maps.userNickName != \"\" '> " +
            "and u.nick_name like '%${maps.userNickName}%'" +
            "</if>" +
            "<if test = 'maps.companyUserId != null '> " +
            " and r.company_user_id = #{maps.companyUserId}" +
            "</if>" +
            "<if test = 'maps.scheduleId != null   and maps.scheduleId != \"\"      '> " +
            "and r.schedule_id IN " +
            "<foreach collection=\"maps.scheduleId.split(',')\"  item='item' index='index'  open='(' separator=',' close=')'> #{item} </foreach>"+
            "</if>" +
            "<if test = 'maps.beginTime != null and maps.beginTime != \"\" '> " +
            "and date_format(r.create_time,'%y%m%d') &gt;= date_format(#{maps.beginTime},'%y%m%d') " +
            "</if>" +
            "<if test = 'maps.endTime != null and maps.endTime != \"\" '> " +
            "and date_format(r.create_time,'%y%m%d') &lt;= date_format(#{maps.endTime},'%y%m%d') " +
            "</if>" +
            "<if test = 'maps.deptId != null    '> " +
            "  AND (u.dept_id = #{maps.deptId} OR u.dept_id IN ( SELECT t.dept_id FROM company_dept t WHERE find_in_set(#{maps.deptId}, ancestors) )) " +
            "</if>" +
            "${maps.params.dataScope}"+
            "order by r.id desc " +
            "</script>"})
    List<CompanyTcmReportListVO> selectCompanyTcmReportListVOList(@Param("maps") CompanyTcmReportListQueryParam param);


    @Select({"<script> " +
            "select r.*,u.nick_name as user_nick_name,c.company_name,t.dept_name as deptName,s.name as scheduleName " +
            "from company_tcm_report r left join company_tcm_schedule s on r.schedule_id=s.id left join company c on c.company_id=r.company_id  left join company_user u on u.user_id=r.company_user_id " +
            " left join company_dept t on u.dept_id=t.dept_id  where 1=1 " +
//            "<if test = 'maps.companyId != null  '> " +
//            "and r.company_id = #{maps.companyId} " +
//            "</if>" +

            "<if test = 'maps.companyId != null   and maps.companyId != \"\"      '> " +
            "and r.company_id IN " +
            "<foreach collection=\"maps.companyId.split(',')\"  item='item' index='index'  open='(' separator=',' close=')'> #{item} </foreach>"+
            "</if>" +

            "<if test = 'maps.userNickName != null and maps.userNickName != \"\" '> " +
            "and u.nick_name like '%${maps.userNickName}%'" +
            "</if>" +
            "<if test = 'maps.companyUserId != null '> " +
            " and r.company_user_id = #{maps.companyUserId}" +
            "</if>" +
            "<if test = 'maps.scheduleId != null   and maps.scheduleId != \"\"      '> " +
            "and r.schedule_id IN " +
            "<foreach collection=\"maps.scheduleId.split(',')\"  item='item' index='index'  open='(' separator=',' close=')'> #{item} </foreach>"+
            "</if>" +
            "<if test = 'maps.beginTime != null and maps.beginTime != \"\" '> " +
            "and date_format(r.create_time,'%y%m%d') &gt;= date_format(#{maps.beginTime},'%y%m%d') " +
            "</if>" +
            "<if test = 'maps.endTime != null and maps.endTime != \"\" '> " +
            "and date_format(r.create_time,'%y%m%d') &lt;= date_format(#{maps.endTime},'%y%m%d') " +
            "</if>" +
            "<if test = 'maps.deptId != null    '> " +
            "  AND (u.dept_id = #{maps.deptId} OR u.dept_id IN ( SELECT t.dept_id FROM company_dept t WHERE find_in_set(#{maps.deptId}, ancestors) )) " +
            "</if>" +
            "${maps.params.dataScope}"+
            "order by r.id desc " +
            "</script>"})
    public List<CompanyTcmReportExportVO> selectCompanyTcmReportExportVOList(@Param("maps") CompanyTcmReportListQueryParam param);



    @Select({"<script> " +
            " SELECT r.company_id,c.company_name, r.schedule_id,s.name as schedule_name,ANY_VALUE(cu.cu_count) as cu_count,ANY_VALUE(cu.money) as money,min(r.create_time) as create_time,max(r.update_time) as update_time,count( DISTINCT r.company_user_id) AS connection_num, " +
            " SUM(total_num) as total_num,SUM(register_num) as register_num,SUM(online_num) as online_num, SUM(finish_num) as finish_num,SUM(total_money) as total_money,SUM(target_money) as target_money,SUM(total_order) as total_order, " +
            " SUM(round1_money) as round1_money,SUM(round1_order) as round1_order,SUM(round2_money) as round2_money,SUM(round2_order) as round2_order,SUM(round3_money) as round3_money,SUM(round3_order) as round3_order,"+
            " SUM(round4_money) as round4_money,SUM(round4_order) as round4_order,SUM(round5_money) as round5_money,SUM(round5_order) as round5_order,SUM(round6_money) as round6_money,SUM(round6_order) as round6_order,"+
            " SUM(round7_money) as round7_money,SUM(round7_order) as round7_order,SUM(round8_money) as round8_money,SUM(round8_order) as round8_order,SUM(round9_money) as round9_money,SUM(round9_order) as round9_order "+
            " from company_tcm_report r LEFT JOIN company_tcm_schedule s on r.schedule_id=s.id  LEFT JOIN company c on r.company_id=c.company_id   "+
            " left join company_tcm_consume cu on r.company_id=cu.company_id and r.schedule_id=cu.schedule_id " +
            " left join company_user comu on r.company_user_id=comu.user_id "+
            //" left join ( SELECT count(*) as cu_count,company_id from company_user  GROUP BY company_id) cu on r.company_id=cu.company_id "+
            " where 1=1 "+
//            "<if test = 'maps.companyId != null  '> " +
//            " and r.company_id = #{maps.companyId} " +
//            "</if>" +
            "<if test = 'maps.companyId != null  and maps.companyId != \"\"    '> " +
            "and r.company_id IN " +
            "<foreach collection=\"maps.companyId.split(',')\"  item='item' index='index'  open='(' separator=',' close=')'> #{item} </foreach>"+
            "</if>" +
            "<if test = 'maps.companyUserId != null '> " +
            " and r.company_user_id = #{maps.companyUserId}" +
            "</if>" +

            "<if test = 'maps.userNickName != null and maps.userNickName != \"\" '> " +
            "and comu.nick_name like '%${maps.userNickName}%'" +
            "</if>" +

            "<if test = 'maps.scheduleId != null   and maps.scheduleId != \"\"     '> " +
            " and r.schedule_id IN " +
            "<foreach collection=\"maps.scheduleId.split(',')\"  item='item' index='index'  open='(' separator=',' close=')'> #{item} </foreach>"+
            "</if>" +
            "<if test = 'maps.beginTime != null and maps.beginTime != \"\" '> " +
            "and date_format(r.create_time,'%y%m%d') &gt;= date_format(#{maps.beginTime},'%y%m%d') " +
            "</if>" +
            "<if test = 'maps.endTime != null and maps.endTime != \"\" '> " +
            "and date_format(r.create_time,'%y%m%d') &lt;= date_format(#{maps.endTime},'%y%m%d') " +
            "</if>" +
            "<if test = 'maps.deptIdStr != null and  maps.deptIdStr !=\"\"    '> " +
            " AND " +
            "<foreach collection=\"maps.deptIdStr.split(',')\" item=\"deptId\"   open=\"(\" close=\")\" separator=\"OR\">" +
            " comu.dept_id = #{deptId} OR comu.dept_id IN ( SELECT t.dept_id FROM company_dept t WHERE find_in_set(#{deptId}, ancestors) )  " +
            "</foreach> " +
            "</if>" +
            "${maps.params.dataScope}"+
            " GROUP BY r.schedule_id,r.company_id "+
            " order by r.company_id desc " +
            "</script>"})
    List<CompanyTcmReportListVO> selectCompanyTcmReportStatisticsVOList(@Param("maps") CompanyTcmReportListQueryParam param);


    @Select({"<script> " +
            " SELECT s.name as schedule_name,c.company_name,ANY_VALUE(cu.cu_count) as cu_count,ANY_VALUE(cu.money) as money,min(r.create_time) as create_time,max(r.update_time) as update_time,count( DISTINCT r.company_user_id) AS connection_num, " +
            " SUM(total_num) as total_num,SUM(register_num) as register_num,SUM(online_num) as online_num, SUM(finish_num) as finish_num,SUM(total_money) as total_money,SUM(target_money) as target_money,SUM(total_order) as total_order, " +
            " SUM(round1_money) as round1_money,SUM(round1_order) as round1_order,SUM(round2_money) as round2_money,SUM(round2_order) as round2_order,SUM(round3_money) as round3_money,SUM(round3_order) as round3_order,"+
            " SUM(round4_money) as round4_money,SUM(round4_order) as round4_order,SUM(round5_money) as round5_money,SUM(round5_order) as round5_order,SUM(round6_money) as round6_money,SUM(round6_order) as round6_order,"+
            " SUM(round7_money) as round7_money,SUM(round7_order) as round7_order,SUM(round8_money) as round8_money,SUM(round8_order) as round8_order,SUM(round9_money) as round9_money,SUM(round9_order) as round9_order "+
            " from company_tcm_report r LEFT JOIN company_tcm_schedule s on r.schedule_id=s.id  LEFT JOIN company c on r.company_id=c.company_id   "+
            " left join company_tcm_consume cu on r.company_id=cu.company_id and r.schedule_id=cu.schedule_id "+
//            " left join ( SELECT count(*) as cu_count,company_id from company_user  GROUP BY company_id) cu on r.company_id=cu.company_id " +
            " where 1=1 "+
            "<if test = 'maps.companyId != null  and maps.companyId != \"\"     '> " +
            " and r.company_id = #{maps.companyId} " +
            "</if>" +
            "<if test = 'maps.companyUserId != null '> " +
            " and r.company_user_id = #{maps.companyUserId}" +
            "</if>" +
            "<if test = 'maps.scheduleId != null   and maps.scheduleId != \"\"     '> " +
            " and r.schedule_id = #{maps.scheduleId}" +
            "</if>" +
            "<if test = 'maps.beginTime != null and maps.beginTime != \"\" '> " +
            "and date_format(r.create_time,'%y%m%d') &gt;= date_format(#{maps.beginTime},'%y%m%d') " +
            "</if>" +
            "<if test = 'maps.endTime != null and maps.endTime != \"\" '> " +
            "and date_format(r.create_time,'%y%m%d') &lt;= date_format(#{maps.endTime},'%y%m%d') " +
            "</if>" +

            "<if test = 'maps.createTimeList != null    '> " +
            " AND date_format(r.create_time,'%y%m%d') &gt;= date_format(#{maps.createTimeList[0]},'%y%m%d') " +
            " AND date_format(r.create_time,'%y%m%d') &lt;= date_format(#{maps.createTimeList[1]},'%y%m%d') " +
            "</if>" +

            "${maps.params.dataScope}"+
            " GROUP BY r.schedule_id,r.company_id "+
            " order by r.company_id desc " +
            "</script>"})
    List<CompanyTcmStatisticsExportVO> selectCompanyTcmStatisticsExportVOList(@Param("maps") CompanyTcmReportListQueryParam param);


    @Select({"<script> " +
            " SELECT r.*,c.company_name,s.name as schedule_name,cu.user_name as company_user_name,cu.nick_name as user_nick_name,cu.create_time as cu_create_time from company_tcm_report r " +
            " LEFT JOIN company_tcm_schedule s on r.schedule_id=s.id  LEFT JOIN company c on r.company_id=c.company_id " +
            " left join company_user cu on cu.user_id=r.company_user_id  " +
            " where 1=1"+
//            "<if test = 'maps.companyId != null  '> " +
//            "and r.company_id = #{maps.companyId} " +
//            "</if>" +
            "<if test = 'maps.companyId != null    and maps.companyId != \"\"     '> " +
            "and r.company_id IN " +
            "<foreach collection=\"maps.companyId.split(',')\"  item='item' index='index'  open='(' separator=',' close=')'> #{item} </foreach>"+
            "</if>" +
            "<if test = 'maps.userNickName != null and maps.userNickName != \"\" '> " +
            "and cu.nick_name like '%${maps.userNickName}%'" +
            "</if>" +
            "<if test = 'maps.companyUserId != null '> " +
            " and r.company_user_id = #{maps.companyUserId}" +
            "</if>" +
            "<if test = 'maps.scheduleId != null   and maps.scheduleId != \"\"      '> " +
            "and r.schedule_id IN " +
            "<foreach collection=\"maps.scheduleId.split(',')\"  item='item' index='index'  open='(' separator=',' close=')'> #{item} </foreach>"+
            "</if>" +
            "<if test = 'maps.beginTime != null and maps.beginTime != \"\" '> " +
            "and date_format(r.create_time,'%y%m%d') &gt;= date_format(#{maps.beginTime},'%y%m%d') " +
            "</if>" +
            "<if test = 'maps.endTime != null and maps.endTime != \"\" '> " +
            "and date_format(r.create_time,'%y%m%d') &lt;= date_format(#{maps.endTime},'%y%m%d') " +
            "</if>" +
            "<if test = 'maps.deptId != null    '> " +
            "  AND (u.dept_id = #{maps.deptId} OR u.dept_id IN ( SELECT t.dept_id FROM company_dept t WHERE find_in_set(#{maps.deptId}, ancestors) )) " +
            "</if>" +
            "${maps.params.dataScope}"+
            "order by r.id desc " +
            "</script>"})
    List<CompanyReportExportVO> selectCompanyReportExportVOList(@Param("maps") CompanyTcmReportListQueryParam param);



    @Select({"<script> " +
            " SELECT r.company_id,c.company_name, r.schedule_id,s.name as schedule_name,ANY_VALUE(cu.cu_count) as cu_count,ANY_VALUE(cu.money) as money,count( DISTINCT r.company_user_id) AS connection_num, " +
            " SUM(total_num) as total_num,SUM(register_num) as register_num,SUM(online_num) as online_num, SUM(finish_num) as finish_num,SUM(total_money) as total_money,SUM(target_money) as target_money,SUM(total_order) as total_order, " +
            " SUM(round1_money) as round1_money,SUM(round1_order) as round1_order,SUM(round2_money) as round2_money,SUM(round2_order) as round2_order,SUM(round3_money) as round3_money,SUM(round3_order) as round3_order,"+
            " SUM(round4_money) as round4_money,SUM(round4_order) as round4_order,SUM(round5_money) as round5_money,SUM(round5_order) as round5_order,SUM(round6_money) as round6_money,SUM(round6_order) as round6_order,"+
            " SUM(round7_money) as round7_money,SUM(round7_order) as round7_order,SUM(round8_money) as round8_money,SUM(round8_order) as round8_order,SUM(round9_money) as round9_money,SUM(round9_order) as round9_order "+
            " from company_tcm_report r LEFT JOIN company_tcm_schedule s on r.schedule_id=s.id  LEFT JOIN company c on r.company_id=c.company_id   "+
            " left join company_tcm_consume cu on r.company_id=cu.company_id and r.schedule_id=cu.schedule_id "+
            " left join company_user comu on r.company_user_id=comu.user_id LEFT JOIN company_dept cd on comu.dept_id=cd.dept_id "+
            " where 1=1 "+
            "<if test = 'maps.companyId != null   and maps.companyId != \"\"     '> " +
            "and r.company_id IN " +
            "<foreach collection=\"maps.companyId.split(',')\"  item='item' index='index'  open='(' separator=',' close=')'> #{item} </foreach>"+
            "</if>" +
            "<if test = 'maps.scheduleId != null  and maps.scheduleId != \"\"      '> " +
            "and r.schedule_id IN " +
            "<foreach collection=\"maps.scheduleId.split(',')\"  item='item' index='index'  open='(' separator=',' close=')'> #{item} </foreach>"+
            "</if>" +
            "<if test = 'maps.beginTime != null and maps.beginTime != \"\" '> " +
            "and date_format(r.create_time,'%y%m%d') &gt;= date_format(#{maps.beginTime},'%y%m%d') " +
            "</if>" +
            "<if test = 'maps.endTime != null and maps.endTime != \"\" '> " +
            "and date_format(r.create_time,'%y%m%d') &lt;= date_format(#{maps.endTime},'%y%m%d') " +
            "</if>" +
            "${maps.params.dataScope}"+
            " GROUP BY r.schedule_id,comu.dept_id "+
            " order by cd.dept_id desc " +
            "</script>"})
    List<CompanyTcmReportListVO> selectReportStatisticsByScheduleId(@Param("maps") CompanyTcmReportListQueryParam param);


}
