package com.fs.crm.mapper;

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

import com.alibaba.fastjson.JSONObject;
import com.fs.crm.domain.CrmCustomerVisit;
import com.fs.crm.param.CrmCustomerStatisticsParam;
import com.fs.crm.param.CrmCustomerVisitListParam;
import com.fs.crm.param.CrmCustomerVisitListQueryParam;
import com.fs.crm.vo.CrmCustomerVisitExportVO;
import com.fs.crm.vo.CrmCustomerVisitListVO;
import com.fs.crm.vo.CrmCustomerVisitStatisticsVO;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

/**
 * 跟进Mapper接口
 *
 * @author fs
 * @date 2022-12-21
 */
public interface CrmCustomerVisitMapper
{
    /**
     * 查询跟进
     *
     * @param visitId 跟进ID
     * @return 跟进
     */
    public CrmCustomerVisit selectCrmCustomerVisitById(Long visitId);

    /**
     * 查询跟进列表
     *
     * @param crmCustomerVisit 跟进
     * @return 跟进集合
     */
    public List<CrmCustomerVisit> selectCrmCustomerVisitList(CrmCustomerVisit crmCustomerVisit);

    /**
     * 新增跟进
     *
     * @param crmCustomerVisit 跟进
     * @return 结果
     */
    public int insertCrmCustomerVisit(CrmCustomerVisit crmCustomerVisit);

    /**
     * 修改跟进
     *
     * @param crmCustomerVisit 跟进
     * @return 结果
     */
    public int updateCrmCustomerVisit(CrmCustomerVisit crmCustomerVisit);

    /**
     * 删除跟进
     *
     * @param visitId 跟进ID
     * @return 结果
     */
    public int deleteCrmCustomerVisitById(Long visitId);

    /**
     * 批量删除跟进
     *
     * @param visitIds 需要删除的数据ID
     * @return 结果
     */
    public int deleteCrmCustomerVisitByIds(Long[] visitIds);

    @Select({"<script> " +
            "select v.*,cu.nick_name as company_user_nick_name,com.company_name,c.customer_name,c.customer_code,c.create_time as customerCreateTime from crm_customer_visit v left join crm_customer  c on c.customer_id=v.customer_id left join company com on com.company_id=c.company_id left join company_user cu on cu.user_id=v.company_user_id " +
            "where 1=1 " +
            "<if test = 'maps.companyUserNickName != null and  maps.companyUserNickName !=\"\"    '> " +
            "and cu.nick_name like CONCAT('%',#{maps.companyUserNickName},'%') " +
            "</if>" +
            "<if test = 'maps.customerCode != null and  maps.customerCode !=\"\"    '> " +
            "and c.customer_code like CONCAT('%',#{maps.customerCode},'%') " +
            "</if>" +
            "<if test = 'maps.customerName != null and  maps.customerName !=\"\"    '> " +
            "and c.customer_name like CONCAT('%',#{maps.customerName},'%') " +
            "</if>" +
            "<if test = 'maps.mobile != null and  maps.mobile !=\"\"    '> " +
            "and c.mobile like CONCAT('%',#{maps.mobile},'%') " +
            "</if>" +
            "<if test = 'maps.companyId != null      '> " +
            "and v.company_id =#{maps.companyId} " +
            "</if>" +
            "<if test = 'maps.customerId != null      '> " +
            "and v.customer_id =#{maps.customerId} " +
            "</if>" +
            "<if test = 'maps.visitType != null      '> " +
            "and v.company_id =#{maps.visitType} " +
            "</if>" +
            "<if test = 'maps.deptId != null  and maps.deptId != 0 '> " +
            "AND (c.dept_id = #{maps.deptId} OR c.dept_id IN ( SELECT t.dept_id FROM company_dept t WHERE find_in_set(#{maps.deptId}, ancestors) )) " +
            "</if>" +
            "<if test = 'maps.customerUserStatus != null      '> " +
            "and v.customer_user_status =#{maps.customerUserStatus} " +
            "</if>" +
            "<if test = 'maps.beginTime != null and maps.beginTime != \"\" '> " +
            "AND date_format(v.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(v.create_time,'%y%m%d') &lt;= date_format(#{maps.endTime},'%y%m%d')"+
            "</if>" +
            "<if test = 'maps.customerCreateTime != null    '> " +
            " AND date_format(c.create_time,'%y%m%d') &gt;= date_format(#{maps.customerCreateTime[0]},'%y%m%d') " +
            " AND date_format(c.create_time,'%y%m%d') &lt;= date_format(#{maps.customerCreateTime[1]},'%y%m%d') " +
            "</if>" +
            "<if test = 'maps.receiveTimeList != null    '> " +
            " AND date_format(c.receive_time,'%y%m%d') &gt;= date_format(#{maps.receiveTimeList[0]},'%y%m%d') " +
            " AND date_format(c.receive_time,'%y%m%d') &lt;= date_format(#{maps.receiveTimeList[1]},'%y%m%d') " +
            "</if>" +
            "${maps.params.dataScope}"+
            " order by v.visit_id desc "+
            "</script>"})
    List<CrmCustomerVisitListVO> selectCrmCustomerVisitListVO(@Param("maps") CrmCustomerVisitListParam param);
    @Select({"<script> " +
            "select v.*,cu.nick_name as company_user_nick_name,com.company_name,c.customer_name,c.customer_code from crm_customer_visit v left join crm_customer  c on c.customer_id=v.customer_id left join company com on com.company_id=c.company_id left join company_user cu on cu.user_id=v.company_user_id " +
            "where 1=1 " +
            "<if test = 'maps.companyId != null     '> " +
            "and v.company_id =#{maps.companyId} " +
            "</if>" +
            "<if test = 'maps.customerId != null      '> " +
            "and v.customer_id =#{maps.customerId} " +
            "</if>" +
            "<if test = 'maps.customerUserStatus != null      '> " +
            "and v.customer_user_status =#{maps.customerUserStatus} " +
            "</if>" +
            "${maps.params.dataScope}"+
            " order by v.visit_id desc "+
            "</script>"})
    List<CrmCustomerVisitListVO> selectCrmCustomerVisitListQuery(@Param("maps")CrmCustomerVisitListQueryParam param);


    @Select({"<script> " +
            "select u.user_id, u.nick_name,d.dept_name, IFNULL(COUNT(distinct v.customer_id),0)  as customer_count, IFNULL(COUNT(v.visit_id ),0) as visit_count from crm_customer_visit v left join company_user u on u.user_id=v.company_user_id left join company_dept d on d.dept_id=u.dept_id  " +
            "where 1=1 " +
            "<if test = 'maps.type != null and maps.type ==1 '> " +
            "and TO_DAYS(v.create_time) = TO_DAYS(NOW()) " +
            "</if>" +
            "<if test = 'maps.type != null and maps.type ==2 '> " +
            "and TO_DAYS(NOW()) - TO_DAYS(v.create_time) &lt;= 1 " +
            "</if>" +
            "<if test = 'maps.type != null and maps.type ==3 '> " +
            "and YEARWEEK(DATE_FORMAT(v.create_time,'%Y-%m-%d')) = YEARWEEK(NOW()) " +
            "</if>" +
            "<if test = 'maps.type != null and maps.type ==4 '> " +
            "and YEARWEEK(DATE_FORMAT(v.create_time,'%Y-%m-%d')) = YEARWEEK(NOW())-1 " +
            "</if>" +
            "<if test = 'maps.type != null and maps.type ==5 '> " +
            "and DATE_FORMAT(v.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(v.create_time,'%Y%m')) = 1 " +
            "</if>" +
            "<if test = 'maps.type != null and maps.type ==7 '> " +
            "and QUARTER(v.create_time) = QUARTER(NOW()) " +
            "</if>" +
            "<if test = 'maps.type != null and maps.type ==8 '> " +
            "and QUARTER(v.create_time) = QUARTER(DATE_SUB(NOW(),INTERVAL 1 QUARTER)) " +
            "</if>" +
            "<if test = 'maps.type != null and maps.type ==9 '> " +
            "and YEAR(v.create_time)=YEAR(NOW()) " +
            "</if>" +
            "<if test = 'maps.type != null and maps.type ==10 '> " +
            "and  YEAR(v.create_time) = YEAR(DATE_SUB(NOW(),INTERVAL 1 YEAR))" +
            "</if>" +
            "<if test = 'maps.endTime != null and maps.startTime != \"\" '> " +
            "AND date_format(v.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(v.create_time,'%y%m%d') &lt;= date_format(#{maps.endTime},'%y%m%d')"+
            "</if>" +
            "and  v.company_user_id IN " +
            "<foreach  item='item' index='index' collection='maps.users' open='(' separator=',' close=')'> #{item}    </foreach>"+
            " group by v.company_user_id "+
            "</script>"})
    List<CrmCustomerVisitStatisticsVO> selectCrmCustomerVisitStatisticsList(@Param("maps") CrmCustomerStatisticsParam param);

    List<JSONObject> selectCrmCustomerVisitCounts(Map<String, Object> toMap);
    @Select({"<script> " +
            "select v.*,cu.nick_name as company_user_nick_name,com.company_name,c.customer_name,c.customer_code,c.create_time as customerCreateTime from crm_customer_visit v left join crm_customer  c on c.customer_id=v.customer_id left join company com on com.company_id=c.company_id left join company_user cu on cu.user_id=v.company_user_id " +
            "where 1=1 " +
            "<if test = 'maps.companyUserNickName != null and  maps.companyUserNickName !=\"\"    '> " +
            "and cu.nick_name like CONCAT('%',#{maps.companyUserNickName},'%') " +
            "</if>" +
            "<if test = 'maps.customerCode != null and  maps.customerCode !=\"\"    '> " +
            "and c.customer_code like CONCAT('%',#{maps.customerCode},'%') " +
            "</if>" +
            "<if test = 'maps.customerName != null and  maps.customerName !=\"\"    '> " +
            "and c.customer_name like CONCAT('%',#{maps.customerName},'%') " +
            "</if>" +
            "<if test = 'maps.mobile != null and  maps.mobile !=\"\"    '> " +
            "and c.mobile like CONCAT('%',#{maps.mobile},'%') " +
            "</if>" +
            "<if test = 'maps.companyId != null      '> " +
            "and v.company_id =#{maps.companyId} " +
            "</if>" +
            "<if test = 'maps.customerId != null      '> " +
            "and v.customer_id =#{maps.customerId} " +
            "</if>" +
            "<if test = 'maps.visitType != null      '> " +
            "and v.company_id =#{maps.visitType} " +
            "</if>" +
            "<if test = 'maps.deptId != null  and maps.deptId != 0 '> " +
            "AND (c.dept_id = #{maps.deptId} OR c.dept_id IN ( SELECT t.dept_id FROM company_dept t WHERE find_in_set(#{maps.deptId}, ancestors) )) " +
            "</if>" +
            "<if test = 'maps.customerUserStatus != null      '> " +
            "and v.customer_user_status =#{maps.customerUserStatus} " +
            "</if>" +
            "<if test = 'maps.beginTime != null and maps.beginTime != \"\" '> " +
            "AND date_format(v.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(v.create_time,'%y%m%d') &lt;= date_format(#{maps.endTime},'%y%m%d')"+
            "</if>" +
            "<if test = 'maps.customerCreateTime != null    '> " +
            " AND date_format(c.create_time,'%y%m%d') &gt;= date_format(#{maps.customerCreateTime[0]},'%y%m%d') " +
            " AND date_format(c.create_time,'%y%m%d') &lt;= date_format(#{maps.customerCreateTime[1]},'%y%m%d') " +
            "</if>" +
            "<if test = 'maps.receiveTimeList != null    '> " +
            " AND date_format(c.receive_time,'%y%m%d') &gt;= date_format(#{maps.receiveTimeList[0]},'%y%m%d') " +
            " AND date_format(c.receive_time,'%y%m%d') &lt;= date_format(#{maps.receiveTimeList[1]},'%y%m%d') " +
            "</if>" +
            "${maps.params.dataScope}"+
            " order by v.visit_id desc "+
            "</script>"})
    List<CrmCustomerVisitExportVO> selectCrmCustomerVisitExportListVO(@Param("maps") CrmCustomerVisitListParam param);
}
