package com.fs.company.mapper;

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

import com.alibaba.fastjson.JSONObject;
import com.fs.company.domain.CompanySmsLogs;
import com.fs.company.param.CompanySmsLogsListParam;
import com.fs.company.param.CompanySmsLogsListQueryParam;
import com.fs.company.param.CompanyStatisticsParam;
import com.fs.company.vo.CompanySmsLogsListQueryVO;
import com.fs.company.vo.CompanySmsLogsListVO;
import com.fs.company.vo.CompanySmsLogsStatisticsVO;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

/**
 * 短信发送记录Mapper接口
 *
 * @author fs
 * @date 2023-01-09
 */
public interface CompanySmsLogsMapper
{
    /**
     * 查询短信发送记录
     *
     * @param logsId 短信发送记录ID
     * @return 短信发送记录
     */
    public CompanySmsLogs selectCompanySmsLogsById(Long logsId);

    /**
     * 查询短信发送记录列表
     *
     * @param companySmsLogs 短信发送记录
     * @return 短信发送记录集合
     */
    public List<CompanySmsLogs> selectCompanySmsLogsList(CompanySmsLogs companySmsLogs);

    /**
     * 新增短信发送记录
     *
     * @param companySmsLogs 短信发送记录
     * @return 结果
     */
    public int insertCompanySmsLogs(CompanySmsLogs companySmsLogs);

    /**
     * 修改短信发送记录
     *
     * @param companySmsLogs 短信发送记录
     * @return 结果
     */
    public int updateCompanySmsLogs(CompanySmsLogs companySmsLogs);

    /**
     * 删除短信发送记录
     *
     * @param logsId 短信发送记录ID
     * @return 结果
     */
    public int deleteCompanySmsLogsById(Long logsId);

    /**
     * 批量删除短信发送记录
     *
     * @param logsIds 需要删除的数据ID
     * @return 结果
     */
    public int deleteCompanySmsLogsByIds(Long[] logsIds);
    @Select({"<script> " +
            "select l.*,u.nick_name as company_user_nick_name,c.customer_code,c.customer_name from company_sms_logs l left join crm_customer c on c.customer_id=l.customer_id left join company_user u on u.user_id=l.company_user_id " +
            "where 1=1 " +
            "<if test = 'maps.companyId != null  '> " +
            "and l.company_id = #{maps.companyId}" +
            "</if>" +
            "<if test = 'maps.customerId != null  '> " +
            "and l.customer_id = #{maps.customerId}" +
            "</if>" +
            "<if test = 'maps.status != null  '> " +
            "and l.status = #{maps.status}" +
            "</if>" +
            "<if test = 'maps.isReply != null and maps.isReply==1  '> " +
            "and l.reply_content is not null " +
            "</if>" +
            "<if test = 'maps.customerCode != null and maps.customerCode!=\"\"  '> " +
            "and c.customer_code = #{maps.customerCode}" +
            "</if>" +
            "<if test = 'maps.customerName != null and maps.customerName!=\"\"  '> " +
            "and c.customer_name = #{maps.customerName}" +
            "</if>" +
            "<if test = 'maps.companyUserNickName != null and maps.companyUserNickName!=\"\"  '> " +
            "and u.nick_name = #{maps.companyUserNickName}" +
            "</if>" +
            "<if test = 'maps.phone != null and maps.phone!=\"\"  '> " +
            "and l.phone = #{maps.phone}" +
            "</if>" +
            "<if test = 'maps.sendTime != null '> " +
            "and date_format(l.send_time,'%y%m%d')  = date_format(#{maps.sendTime},'%y%m%d') " +
            "</if>" +
            "<if test = 'maps.beginTime != null and maps.beginTime != \"\"    '> " +
            " AND date_format(l.create_time,'%y%m%d') &gt;= date_format(#{maps.beginTime},'%y%m%d') " +
            " AND date_format(l.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 l.logs_id desc " +
            "</script>"})
    List<CompanySmsLogsListQueryVO> selectCompanySmsLogsListQuery(@Param("maps")CompanySmsLogsListQueryParam param);
    @Select({"<script> " +
            "select l.*,c.company_name,u.nick_name as user_nick_name,cc.customer_name as customerName from company_sms_logs l left join company c on c.company_id=l.company_id left join crm_customer cc on cc.customer_id=l.customer_id left join company_user u on u.user_id=l.company_user_id " +
            "where 1=1 " +
            "<if test = 'maps.companyId != null  '> " +
            "and l.company_id = #{maps.companyId}" +
            "</if>" +
            "<if test = 'maps.customerId != null  '> " +
            "and l.customer_id = #{maps.customerId}" +
            "</if>" +
            "<if test = 'maps.status != null  '> " +
            "and l.status = #{maps.status}" +
            "</if>" +
            "<if test = 'maps.userNickName != null and maps.userNickName != \"\" '> " +
            "and u.nick_name like '%${maps.userNickName}%' " +
            "</if>" +
            "<if test = 'maps.customerNo != null  and maps.customerNo!=\"\"  '> " +
            "and cc.customer_no = #{maps.customerNo}" +
            "</if>" +
            "<if test = 'maps.phone != null  and maps.phone!=\"\"  '> " +
            "and l.phone = #{maps.phone}" +
            "</if>" +
            "<if test = 'maps.createTime != null '> " +
            "and date_format(l.create_time,'%y%m%d')  = date_format(#{maps.createTime},'%y%m%d') " +
            "</if>" +
            "<if test = 'maps.createTime != null '> " +
            "and date_format(l.create_time,'%y%m%d')  = date_format(#{maps.createTime},'%y%m%d') " +
            "</if>" +
            "<if test = 'maps.beginTime != null and maps.beginTime != \"\"    '> " +
            " AND date_format(l.send_time,'%y%m%d') &gt;= date_format(#{maps.beginTime},'%y%m%d') " +
            " AND date_format(l.send_time,'%y%m%d') &lt;= date_format(#{maps.endTime},'%y%m%d') " +
            "</if>" +
            "order by l.logs_id desc " +
            "</script>"})
    List<CompanySmsLogsListVO> selectCompanySmsLogsList(@Param("maps")CompanySmsLogsListParam param);
    @Select("select * from company_sms_logs where mid=#{mid} ")
    CompanySmsLogs selectCompanySmsLogsByMid(String mid);

    List<JSONObject> selectSmsLogsCounts(Map<String, Object> toMap);
    @Select({"<script> " +
            "select u.nick_name, count(l.company_user_id) as sms_count,count( IF ( l.status =1,1, NULL ) ) as success_count  from  company_sms_logs l left join company_user u on l.company_user_id=u.user_id  " +
            "where 1=1 " +
            "<if test = 'maps.type != null and maps.type ==1 '> " +
            "and TO_DAYS(l.create_time) = TO_DAYS(NOW()) " +
            "</if>" +
            "<if test = 'maps.type != null and maps.type ==2 '> " +
            "and TO_DAYS(NOW()) - TO_DAYS(l.create_time) &lt;= 1 " +
            "</if>" +
            "<if test = 'maps.type != null and maps.type ==3 '> " +
            "and YEARWEEK(DATE_FORMAT(l.create_time,'%Y-%m-%d')) = YEARWEEK(NOW()) " +
            "</if>" +
            "<if test = 'maps.type != null and maps.type ==4 '> " +
            "and YEARWEEK(DATE_FORMAT(l.create_time,'%Y-%m-%d')) = YEARWEEK(NOW())-1 " +
            "</if>" +
            "<if test = 'maps.type != null and maps.type ==5 '> " +
            "and DATE_FORMAT(l.create_time,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') " +
            "</if>" +
            "<if test = 'maps.type != null and maps.type ==6 '> " +
            "and PERIOD_DIFF(DATE_FORMAT(NOW(),'%Y%m'),DATE_FORMAT(l.create_time,'%Y%m')) = 1 " +
            "</if>" +
            "<if test = 'maps.type != null and maps.type ==7 '> " +
            "and QUARTER(l.create_time) = QUARTER(NOW()) " +
            "</if>" +
            "<if test = 'maps.type != null and maps.type ==8 '> " +
            "and QUARTER(l.create_time) = QUARTER(DATE_SUB(NOW(),INTERVAL 1 QUARTER)) " +
            "</if>" +
            "<if test = 'maps.type != null and maps.type ==9 '> " +
            "and YEAR(l.create_time)=YEAR(NOW()) " +
            "</if>" +
            "<if test = 'maps.type != null and maps.type ==10 '> " +
            "and  YEAR(l.create_time) = YEAR(DATE_SUB(NOW(),INTERVAL 1 YEAR))" +
            "</if>" +
            "<if test = 'maps.startTime != null and maps.startTime != \"\" '> " +
            "AND date_format(l.create_time,'%y%m%d') &gt;= date_format(#{maps.startTime},'%y%m%d')"+
            "</if>" +
            "<if test = 'maps.endTime != null and maps.endTime != \"\" '> " +
            "AND date_format(l.create_time,'%y%m%d') &lt;= date_format(#{maps.endTime},'%y%m%d')"+
            "</if>" +
            "and  l.`company_user_id` IN " +
            "<foreach  item='item' index='index' collection='maps.users' open='(' separator=',' close=')'> #{item}    </foreach>"+
            " group by l.company_user_id "+
            "</script>"})
    List<CompanySmsLogsStatisticsVO> selectSmsLogsStatisticsList(@Param("maps") CompanyStatisticsParam param);
    @Select("select * from company_sms_logs where phone=#{mobile} order by logs_id desc limit 1")
    CompanySmsLogs selectCompanySmsLogsByMobile(String mobile);
}
