hz_crm_api/controllers/statistic_company_merge.go

package controllers

import (
	"fmt"
	"github.com/rdlucklib/rdluck_tools/paging"
	"hongze/hz_crm_api/models"
	"hongze/hz_crm_api/models/company"
	"hongze/hz_crm_api/models/response"
	"hongze/hz_crm_api/services"
	"strings"
	//"hongze/hz_crm_api/services"
	"hongze/hz_crm_api/utils"
	"strconv"
	//"strings"
	"time"
)

// 统计报表模块下权益合同合并
// StatisticReportController 统计报告基类
type StatisticCompanyMergerController struct {
	BaseAuthController
}

// IncrementalCompanyList
// @Title 获取增量客户数据列表
// @Description 获取增量客户数据列表接口
// @Param   PageSize   query   int  true       "每页数据条数"
// @Param   CurrentIndex   query   int  true       "当前页页码，从1开始"
// @Param   Keyword   query   string  true       "客户名称"
// @Param   CompanyType   query   string  true       "客户类型：传空字符串或者不传为全部,'ficc','权益'"
// @Param   AdminId   query   string  true       "销售id，多个用英文逗号隔开，空字符串为全部"
// @Param   RegionType   query   string  false       "所属区域：传空字符串或者不传为全部,'国内','海外'"
// @Param   StartDate   query   string  false       "开始日期"
// @Param   EndDate   query   string  false       "结束日期"
// @Param   DataType   query   string  false       "报表类型，枚举值：`新增试用`，`新签客户`，`续约客户`，`未续约客户`"
// @Param   TryOutType   query   string  false       " '试用'， '非试用' 非试用即为冻结/流失"
// @Param   IsExport   query   bool  false       "是否导出excel，默认是false"
// @Param   IsConfirm   query   int  false       "是否确认续约: -1-默认全部; 0-待确认; 1-已确认"
// @Param   CompanyAscribeId   query   int  false       "归因ID"
// @Param   PackageDifference   query   string  false       "和上一份合同的区别，枚举值：`增加套餐`，`减少套餐`，`维持套餐`"
// @Success 200 {object} response.IncrementalCompanyListResp
// @router /merge_company_list [get]
func (this *StatisticCompanyMergerController) IncrementalCompanyList() {
	br := new(models.BaseResponse).Init()
	defer func() {
		this.Data["json"] = br
		this.ServeJSON()
	}()

	sysUser := this.SysUser
	if sysUser == nil {
		br.Msg = "请登录"
		br.ErrMsg = "请登录，SysUser Is Empty"
		br.Ret = 408
		return
	}

	pageSize, _ := this.GetInt("PageSize")
	currentIndex, _ := this.GetInt("CurrentIndex")

	adminId := this.GetString("AdminId")
	regionType := this.GetString("RegionType")
	companyType := this.GetString("CompanyType")
	dataType := this.GetString("DataType")
	tryOutType := this.GetString("TryOutType")
	keyword := this.GetString("Keyword")

	startDate := this.GetString("StartDate")
	endDate := this.GetString("EndDate")

	isConfirm, _ := this.GetInt("IsConfirm", -1)
	companyAscribeId, _ := this.GetInt("CompanyAscribeId", -1)
	//if startDate == "" || endDate == "" {
	//	br.Msg = "获取失败，开始日期或结束日期未传"
	//	br.ErrMsg = "获取失败，开始日期或结束日期未传"
	//	return
	//}
	if startDate == "" {
		startDate = "2015-01-01"
	}
	if endDate == "" {
		endDate = time.Now().Format(utils.FormatDate)
	}

	var startSize int
	if pageSize <= 0 {
		pageSize = utils.PageSize20
	}
	if currentIndex <= 0 {
		currentIndex = 1
	}
	startSize = utils.StartIndex(currentIndex, pageSize)

	//是否导出报表
	isExport, _ := this.GetBool("IsExport")
	if isExport {
		pageSize = 10000
		currentIndex = 1
	}

	var condition string
	var pars []interface{}

	//条件
	if adminId != "" {
		condition += ` AND c.seller_id in  (` + adminId + `) `
		//pars = append(pars, adminId)
	} else {

		//根据当前角色来获取查询条件
		condition, pars = getQueryParams(condition, pars, sysUser, "c.")

	}
	if regionType != "" {
		condition += ` AND b.region_type = ? `
		pars = append(pars, regionType)
	}
	//关键字搜索
	if keyword != "" {
		condition += ` and b.company_name like "%` + keyword + `%" `
	}

	var conditionAscribRai string      // 处理权益未续约客户检索列表SQL查询条件
	var conditionAscribRaiTotal string // 处理权益未续约客户总量查询条件
	var parsAscribeRai []interface{}
	var parsAscribeRaiTotal []interface{} // 处理权益未续约客户总量查询条件
	var conditionConfirm string
	var parsConfirm []interface{}

	companyConfirmList, err := company.GetCompanyNoRenewedAscribeList(conditionConfirm, parsConfirm, 0, 0)
	if err != nil && err.Error() != utils.ErrNoRow() {
		br.Msg = "获取失败"
		br.ErrMsg = "获取失败，GetCompanyNoRenewedAscribeList Err:" + err.Error()
		return
	}
	var noRenewedcompanyIds []int //已经确定未续约的公司ID
	if len(companyConfirmList) == 0 {
		noRenewedcompanyIds = append(noRenewedcompanyIds, 0) // 给一个不存在的ID
	} else {
		for _, v := range companyConfirmList {
			noRenewedcompanyIds = append(noRenewedcompanyIds, v.CompanyId)
		}
	}
	//是否确认续约 CRM 13.9
	conditionAscribRaiTotal += ` AND ( c.company_id IN (` + utils.GetOrmInReplace(len(noRenewedcompanyIds)) + `)   OR c.product_id = 1  OR  a.create_time <  '2023-01-01'  )   ` // 已确认
	parsAscribeRaiTotal = append(parsAscribeRaiTotal, noRenewedcompanyIds)
	if isConfirm != -1 {
		if isConfirm == 0 {
			conditionAscribRai += ` AND ( c.company_id NOT IN (` + utils.GetOrmInReplace(len(noRenewedcompanyIds)) + `)  AND  c.product_id = 2  AND  a.create_time >=  '2023-01-01' )  ` // 待确认
		} else {
			conditionAscribRai += ` AND ( c.company_id IN (` + utils.GetOrmInReplace(len(noRenewedcompanyIds)) + `)   OR c.product_id = 1  OR   a.create_time <  '2023-01-01' )    ` // 已确认
		}
		parsAscribeRai = append(parsAscribeRai, noRenewedcompanyIds)
	}

	//归因ID CRM 13.9
	if companyAscribeId > 0 {
		var conditionAscribe string
		var parsAscribe []interface{}
		conditionAscribe = "  AND  company_ascribe_id = ? "
		parsAscribe = append(parsAscribe, companyAscribeId)
		companyNoRenewedAscribeList, err := company.GetCompanyNoRenewedAscribeList(conditionAscribe, parsAscribe, 0, 0)
		if err != nil && err.Error() != utils.ErrNoRow() {
			br.Msg = "获取失败"
			br.ErrMsg = "获取失败，GetCompanyNoRenewedAscribeList Err:" + err.Error()
			return
		}
		var companyIds []int
		if len(companyNoRenewedAscribeList) == 0 {
			companyIds = append(companyIds, 0) // 给一个不存在的ID
		} else {
			for _, v := range companyNoRenewedAscribeList {
				companyIds = append(companyIds, v.CompanyId)
			}
		}
		conditionAscribRai += ` AND c.company_id IN (` + utils.GetOrmInReplace(len(companyIds)) + `)`
		parsAscribeRai = append(parsAscribeRai, companyIds)
	}

	switch companyType {
	case "ficc":
		condition += ` AND c.product_id = ? `
		pars = append(pars, 1)
	case "权益":
		condition += ` AND c.product_id = ? `
		pars = append(pars, 2)
	case "":
	default:
		br.Msg = "获取失败，客户类型异常"
		br.ErrMsg = "获取失败，客户类型异常"
		return
	}

	var list []*models.IncrementalList
	//moreListMap := make(map[int][]*models.IncrementalList)

	var newCompanyTotal int //新签合同数量
	var dataTotal, trialTotal, renewalCompanyTotal, notRenewalCompanyTotal int
	var notRenewalTryOut, notRenewalNotTryOut int

	//新签客户数
	{
		condition1 := condition
		pars1 := pars
		condition1 += ` AND a.start_date >= ? AND a.start_date <= ? `
		pars1 = append(pars1, startDate, endDate)
		condition1 += ` AND a.contract_type = ? `
		pars1 = append(pars1, "新签合同")
		newCompanyTotal, err = company.GetIncrementalNewCompanyProductMergeCount(condition1, pars1)
		if err != nil && err.Error() != utils.ErrNoRow() {
			br.Msg = "获取失败"
			br.ErrMsg = "获取失败，Err:" + err.Error()
			return
		}

		if dataType == "新签客户" {
			//列表数据数量
			total, err := company.GetIncrementalNewCompanyProductMergeCount(condition1, pars1)
			if err != nil && err.Error() != utils.ErrNoRow() {
				br.Msg = "获取失败"
				br.ErrMsg = "获取失败，Err:" + err.Error()
				return
			}

			//列表页数据
			tmpList, err := models.GetIncrementalCompanyMergeList(condition1, pars1, startSize, pageSize)
			if err != nil {
				br.Msg = "获取失败"
				br.ErrMsg = "获取失败，Err:" + err.Error()
				return
			}
			list = tmpList
			dataTotal = total
		}
	}

	//续约客户数
	{
		condition1 := condition
		pars1 := pars
		condition1 += ` AND a.start_date >= ? AND a.start_date <= ? `
		pars1 = append(pars1, startDate, endDate)
		condition1 += ` AND a.contract_type = ? `
		pars1 = append(pars1, "续约合同")

		////额外条件（续约合同的起始日期包含在所选时间段内且不包含在新签合同存续期内的客户）
		//pars1 = append(pars1, endDate)

		total, err := company.GetIncrementalRenewalCompanyProductMergeCount(condition1, pars1)
		if err != nil && err.Error() != utils.ErrNoRow() {
			br.Msg = "获取失败"
			br.ErrMsg = "获取失败，Err:" + err.Error()
			return
		}
		renewalCompanyTotal = total

		if dataType == "续约客户" {
			//列表数据数量
			total, err := company.GetIncrementalRenewalCompanyProductMergeCount(condition1, pars1)
			if err != nil && err.Error() != utils.ErrNoRow() {
				br.Msg = "获取失败"
				br.ErrMsg = "获取失败，Err:" + err.Error()
				return
			}
			//列表页数据
			tmpList, err := models.GetIncrementalCompanyMergeList(condition1, pars1, startSize, pageSize)
			if err != nil {
				br.Msg = "获取失败"
				br.ErrMsg = "获取失败，Err:" + err.Error()
				return
			}
			list = tmpList
			dataTotal = total

			var ids []string
			companyMap := make(map[int]*models.IncrementalList)
			oldCompanyMap := make(map[int]*models.IncrementalList)
			countMap := make(map[int]int)
			for _, item := range tmpList {
				ids = append(ids, strconv.Itoa(item.CompanyId))
				companyMap[item.CompanyId] = item
			}
			if len(ids) > 0 {
				idStr := strings.Join(ids, ",")
				lists, err := models.GetLastContractMoney(idStr)
				if err != nil {
					br.Msg = "获取失败"
					br.ErrMsg = "获取失败，Err:" + err.Error()
					return
				}

				for _, item := range lists {
					_, countOk := countMap[item.CompanyId]
					_, ok := oldCompanyMap[item.CompanyId]
					if !ok {
						oldCompanyMap[item.CompanyId] = item
					} else if !countOk {
						countMap[item.CompanyId] = 1
						oldCompanyMap[item.CompanyId] = item
					}
				}
				//给list赋值
				for _, item := range list {
					if item.ProductName == "权益" {
						lastContract, _ := oldCompanyMap[item.CompanyId]
						if item.Money > lastContract.Money {
							item.PackageDifference = "增加套餐"
						} else if item.Money < lastContract.Money {
							item.PackageDifference = "减少套餐"
						} else {
							item.PackageDifference = "维持套餐"
						}
					}
				}
			}

		}
	}

	//未续约客户数
	{
		condition1 := condition
		pars1 := pars

		endDateTime, err := time.Parse(utils.FormatDate, endDate)
		if err != nil {
			br.Msg = "结束时间异常"
			br.ErrMsg = "获取失败，Err:" + err.Error()
			return
		}
		//选择的日期加一天的原因是因为：筛选条件是截止到时分秒的，如果要把选择的这一天也统计进去，那么需要在选择的结束日期基础上加上一天
		tryOutEndDate := endDateTime.AddDate(0, 0, 1).Format(utils.FormatDate)

		condition1 += ` AND a.create_time >= ? AND a.create_time < ? `
		pars1 = append(pars1, startDate, tryOutEndDate)
		condition1 += ` AND a.operation = ? `
		pars1 = append(pars1, "try_out")
		condition1 += ` AND c.status not in ("永续","正式","关闭")  `

		//未续约这里只统计已经确定的
		notRenewalCondition := condition1
		notRenewalPars := pars1
		notRenewalCondition += conditionAscribRaiTotal
		notRenewalPars = append(notRenewalPars, parsAscribeRaiTotal)
		total, err := models.GetIncrementalCompanyCountByOperationRecord(notRenewalCondition, notRenewalPars)
		if err != nil && err.Error() != utils.ErrNoRow() {
			br.Msg = "获取失败"
			br.ErrMsg = "获取失败，Err:" + err.Error()
			return
		}
		notRenewalCompanyTotal = total

		condition1 += conditionAscribRai
		pars1 = append(pars1, parsAscribeRai)
		if dataType == "未续约客户" {
			//统计数据
			for _, v := range []string{"试用", "非试用"} {
				totalCondition1 := condition1
				totalPars1 := pars1
				var tmpTotal int
				if v == "试用" {
					totalCondition1 += ` AND c.status = "试用" `
					tmpTotal, err = models.GetIncrementalCompanyProductCountByOperationRecord(totalCondition1, totalPars1)
					if err != nil && err.Error() != utils.ErrNoRow() {
						br.Msg = "获取失败"
						br.ErrMsg = "获取失败，Err:" + err.Error()
						return
					}
					notRenewalTryOut = tmpTotal
				} else if v == "非试用" {
					totalCondition1 += ` AND c.status IN ("冻结","流失") `
					tmpTotal, err = models.GetIncrementalCompanyProductCountByOperationRecord(totalCondition1, totalPars1)
					if err != nil && err.Error() != utils.ErrNoRow() {
						br.Msg = "获取失败"
						br.ErrMsg = "获取失败，Err:" + err.Error()
						return
					}
					notRenewalNotTryOut = tmpTotal
				}

			}
			//列表数据数量
			if tryOutType == "试用" {
				condition1 += ` AND c.status = "试用" `
				total = notRenewalTryOut
			} else if tryOutType == "非试用" {
				condition1 += ` AND c.status IN ("冻结","流失") `
				total = notRenewalNotTryOut
			}

			//total, err := models.GetIncrementalCompanyProductCountByOperationRecord(condition1, pars1)
			//if err != nil && err.Error() != utils.ErrNoRow() {
			//	br.Msg = "获取失败"
			//	br.ErrMsg = "获取失败，Err:" + err.Error()
			//	return
			//}

			//分页total单独计算
			total, err = models.GetIncrementalCompanyProductCountByOperationRecord(condition1, pars1)
			if err != nil && err.Error() != utils.ErrNoRow() {
				br.Msg = "获取失败"
				br.ErrMsg = "获取失败，Err:" + err.Error()
				return
			}
			//列表页数据
			tmpList, err := models.GetIncrementalCompanyListByOperationRecord(condition1, pars1, startSize, pageSize)
			if err != nil {
				br.Msg = "获取失败"
				br.ErrMsg = "获取失败，Err:" + err.Error()
				return
			}

			var ascribecompanyIds []int
			for _, item := range tmpList {
				endDateTime, _ := time.Parse(utils.FormatDateTime, item.CreateTime)
				item.EndDate = endDateTime.Format(utils.FormatDate)
				ascribecompanyIds = append(ascribecompanyIds, item.CompanyId)
			}
			//归因标签
			mapGetCompanyAscribeContent, mapContent := services.GetCompanyAscribeContentMap(ascribecompanyIds)
			mapNoRenewedNote := services.GetCompanyNoRenewedNoteMap(ascribecompanyIds)

			for _, item := range tmpList {
				item.AscribeContent = mapGetCompanyAscribeContent[fmt.Sprint("CID_", item.CompanyId, "PID_", item.ProductId)]
				item.Content = mapContent[fmt.Sprint("CID_", item.CompanyId, "PID_", item.ProductId)]
				item.IsShowNoRenewedNote = mapNoRenewedNote[fmt.Sprint("CID_", item.CompanyId, "PID_", item.ProductId)]
			}

			list = tmpList
			dataTotal = total

		}
	}

	listLen := len(list)

	if listLen == 0 {
		list = make([]*models.IncrementalList, 0)
	}
	for i := 0; i < listLen; i++ {
		item := list[i]

		//新增试用不需要计算剩余日期
		if dataType != "新增试用" {
			//剩余可用天数
			endDateTime, _ := time.Parse(utils.FormatDate, item.EndDate)
			endDateTime = endDateTime.AddDate(0, 0, 1)
			sub := endDateTime.Sub(time.Now())
			expireDay := fmt.Sprintf("%v", int(sub.Hours()/24))
			list[i].ExpireDay = expireDay
		}

	}
	page := paging.GetPaging(currentIndex, pageSize, dataTotal)
	resp := response.IncrementalCompanyListResp{
		Paging:                 page,
		List:                   list,
		TrialTotal:             trialTotal,
		NewCompanyTotal:        newCompanyTotal,
		RenewalCompanyTotal:    renewalCompanyTotal,
		NotRenewalCompanyTotal: notRenewalCompanyTotal,
		NotRenewalTryOut:       notRenewalTryOut,
		NotRenewalNotTryOut:    notRenewalNotTryOut,
	}
	//fmt.Println()
	//导出excel
	if isExport {
		//IncrementalCompanyListExport(this, dataType, resp, br)
		return
	}
	br.Ret = 200
	br.Success = true
	br.Msg = "获取成功"
	br.Data = resp
}