hz_crm_api/controllers/statistic_company_merge.go

package controllers

import (
	"fmt"
	"github.com/rdlucklib/rdluck_tools/paging"
	"github.com/tealeg/xlsx"
	"hongze/hz_crm_api/models"
	"hongze/hz_crm_api/models/company"
	"hongze/hz_crm_api/models/response"
	"hongze/hz_crm_api/services"
	cygxService "hongze/hz_crm_api/services/cygx"
	"os"
	"path/filepath"
	//"hongze/hz_crm_api/services"
	"hongze/hz_crm_api/utils"
	//"strings"
	"time"
)

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

// MergeCompanyList
// @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) MergeCompanyList() {
	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")
	packageDifference := this.GetString("PackageDifference")

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

	isConfirm, _ := this.GetInt("IsConfirm", -1)
	companyAscribeId, _ := this.GetInt("CompanyAscribeId", -1)

	if dataType != "续约客户" {
		packageDifference = "" // 只有续约客户才会有值，过滤前端传过来的脏数据
	}
	//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 conditionAscribRaiToBeTotal string // 处理权益未续约客户待确认总量查询条件
	var parsAscribeRai []interface{}
	var parsAscribeRaiTotal []interface{}     // 处理权益未续约客户总量查询条件
	var parsAscribeRaiTobeTotal []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)) + `)  ` // 已确认
	parsAscribeRaiTotal = append(parsAscribeRaiTotal, noRenewedcompanyIds)

	conditionAscribRaiToBeTotal += ` AND c.company_id NOT IN (` + utils.GetOrmInReplace(len(noRenewedcompanyIds)) + `)   ` // 待确认
	parsAscribeRaiTobeTotal = append(parsAscribeRaiTobeTotal, noRenewedcompanyIds)

	if isConfirm != -1 {
		if isConfirm == 0 {
			conditionAscribRai += ` AND  c.company_id NOT IN (` + utils.GetOrmInReplace(len(noRenewedcompanyIds)) + `)  ` // 待确认
		} else {
			conditionAscribRai += ` AND  c.company_id IN (` + utils.GetOrmInReplace(len(noRenewedcompanyIds)) + `)    ` // 已确认
		}
		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)
	}

	condition += ` AND c.product_id = ?   AND  a.status = 1 `
	pars = append(pars, 2)

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

	var newCompanyTotal int                   //新签合同数量
	var notRenewalCompanyTotal int            //未续约客户数已确认
	var notRenewalCompanyToBeConfirmTotal int //未续约客户数待确认
	var dataTotal, trialTotal, renewalCompanyTotal 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.GetIncrementalNewCompanyProductMergeCount(condition1, pars1)
		if err != nil && err.Error() != utils.ErrNoRow() {
			br.Msg = "获取失败"
			br.ErrMsg = "获取失败，Err:" + err.Error()
			return
		}
		renewalCompanyTotal = total

		if dataType == "续约客户" {
			//续约的客户才会查询
			if packageDifference != "" {
				condition1 += ` AND a.package_difference = ? `
				pars1 = append(pars1, packageDifference)
			}
			//列表数据数量
			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

		}
	}

	//未续约客户数
	{
		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.end_date >= ? AND a.end_date  <= ? `
		pars1 = append(pars1, startDate, endDate)
		//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 := company.GetIncrementalRenewalCompanyProductMergeCount(notRenewalCondition, notRenewalPars)
		if err != nil && err.Error() != utils.ErrNoRow() {
			br.Msg = "获取失败"
			br.ErrMsg = "获取失败，Err:" + err.Error()
			return
		}
		notRenewalCompanyTotal = total

		//未续约待确认数量
		notRenewalToBeCondition := condition1
		notRenewalToBePars := pars1
		notRenewalToBeCondition += conditionAscribRaiToBeTotal
		notRenewalToBePars = append(notRenewalToBePars, parsAscribeRaiTobeTotal)
		notRenewalCompanyToBeConfirmTotal, err = company.GetIncrementalRenewalCompanyProductMergeCount(notRenewalToBeCondition, notRenewalToBePars)
		if err != nil && err.Error() != utils.ErrNoRow() {
			br.Msg = "获取失败"
			br.ErrMsg = "获取失败，Err:" + err.Error()
			return
		}

		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 = company.GetIncrementalRenewalCompanyProductMergeCount(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 = company.GetIncrementalRenewalCompanyProductMergeCount(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 = company.GetIncrementalRenewalCompanyProductMergeCount(condition1, pars1)
			if err != nil && err.Error() != utils.ErrNoRow() {
				br.Msg = "获取失败"
				br.ErrMsg = "获取失败，Err:" + err.Error()
				return
			}
			//return
			//列表页数据
			tmpList, err := models.GetIncrementalCompanyListByOperationRecordMerge(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)
	}
	var companyContractIds []int
	for i := 0; i < listLen; i++ {
		item := list[i]
		companyContractIds = append(companyContractIds, item.CompanyContractId)
		//新增试用不需要计算剩余日期
		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
		}
	}

	//合并合同所对应的权限
	mappermissionName, err := cygxService.GetCompanyContractPermissionNameMapById(companyContractIds)
	if err != nil {
		br.Msg = "获取失败"
		br.ErrMsg = "获取失败，Err:" + err.Error()
		return
	}
	for _, v := range list {
		v.PermissionName = mappermissionName[v.CompanyContractId]
	}

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

// IncrementalCompanyListExport 导出增量客户数据报表excel
func MergeCompanyListListExport(this *StatisticCompanyMergerController, dataType string, resp response.IncrementalCompanyListResp, br *models.BaseResponse) {
	dir, err := os.Executable()
	exPath := filepath.Dir(dir)
	downLoadnFilePath := exPath + "/" + time.Now().Format(utils.FormatDateTimeUnSpace) + ".xlsx"
	xlsxFile := xlsx.NewFile()
	if err != nil {
		br.Msg = "生成文件失败"
		br.ErrMsg = "生成文件失败"
		return
	}
	style := xlsx.NewStyle()
	alignment := xlsx.Alignment{
		Horizontal: "center",
		Vertical:   "center",
		WrapText:   true,
	}

	style.Alignment = alignment
	style.ApplyAlignment = true

	sheel, err := xlsxFile.AddSheet("增量客户数据")
	if err != nil {
		br.Msg = "新增Sheet失败"
		br.ErrMsg = "新增Sheet失败,Err:" + err.Error()
		return
	}
	sheel.SetColWidth(0, 0, 30)
	sheel.SetColWidth(1, 1, 15)
	sheel.SetColWidth(2, 2, 15)
	sheel.SetColWidth(3, 3, 18)
	sheel.SetColWidth(4, 4, 40)
	sheel.SetColWidth(5, 5, 18)

	//统计数据
	statisticRow := sheel.AddRow()

	cell1 := statisticRow.AddCell()
	cell1.SetStyle(style)
	cell1.SetValue(fmt.Sprint("新增试用客户数：", resp.TrialTotal))

	cell2 := statisticRow.AddCell()
	cell2.SetStyle(style)
	cell2.SetValue(fmt.Sprint("新签客户数：", resp.NewCompanyTotal))

	cell3 := statisticRow.AddCell()
	cell3.SetStyle(style)
	cell3.SetValue(fmt.Sprint("续约客户数：", resp.RenewalCompanyTotal))

	cell4 := statisticRow.AddCell()
	cell4.SetStyle(style)
	cell4.SetValue(fmt.Sprint("未续约客户数：", resp.NotRenewalCompanyTotal))
	//表头

	titleRow := sheel.AddRow()

	cellName := titleRow.AddCell()
	cellName.SetStyle(style)
	cellName.SetValue("客户名称")

	cellProName := titleRow.AddCell()
	cellProName.SetStyle(style)
	cellProName.SetValue("客户类型")

	cellSellerName := titleRow.AddCell()
	cellSellerName.SetStyle(style)
	cellSellerName.SetValue("所属销售")

	//permissionMap := make(map[int][]string)
	//tmpPermissionMap := make(map[int]map[string][]string)
	//companyContractIdList := make([]string, 0)
	//for _, v := range resp.List {
	//	companyContractIdList = append(companyContractIdList, fmt.Sprint(v.CompanyContractId))
	//}
	//if dataType == "新签客户" || dataType == "续约客户" {
	cellMoney := titleRow.AddCell()
	cellMoney.SetStyle(style)
	cellMoney.SetValue("合同金额")

	//这么大费周章的目的是为了：权益的品种存在主观、客观的区分，如果一个品种既存在主观，又存在客观，那么就展示品种名称，否则就要列出品种名称+主、客观类型
	//if len(companyContractIdList) > 0 {
	//	list, tmpErr := company.GetCompanyContractPermissionListByContractIds(strings.Join(companyContractIdList, ","))
	//	if tmpErr != nil {
	//		err = tmpErr
	//		return
	//	}
	//	for _, v := range list {
	//		tmpPermissionNameMap, ok := tmpPermissionMap[v.CompanyContractId]
	//		if ok {
	//			tmpPermissionNameList, ok2 := tmpPermissionNameMap[v.ChartPermissionName]
	//			if ok2 {
	//				tmpPermissionNameList = append(tmpPermissionNameList, v.PermissionRemark)
	//			} else {
	//				tmpPermissionNameList = []string{v.PermissionRemark}
	//			}
	//			tmpPermissionNameMap[v.ChartPermissionName] = tmpPermissionNameList
	//		} else {
	//			tmpPermissionNameMap = make(map[string][]string)
	//			tmpPermissionNameMap[v.ChartPermissionName] = []string{v.PermissionRemark}
	//		}
	//		tmpPermissionMap[v.CompanyContractId] = tmpPermissionNameMap
	//	}
	//}
	//}

	//for companyContractId, tmpPermissionNameMap := range tmpPermissionMap {
	//	tmpPermissionName := ``
	//	tmpPermissionList := []string{}
	//	for tmpChartPermissionName, tmpChartPermissionNameList := range tmpPermissionNameMap {
	//		if len(tmpChartPermissionNameList) > 1 {
	//			tmpPermissionName = tmpChartPermissionName
	//		} else {
	//			tmpPermissionName = tmpChartPermissionNameList[0]
	//		}
	//		tmpPermissionList = append(tmpPermissionList, tmpPermissionName)
	//	}
	//
	//	permissionMap[companyContractId] = tmpPermissionList
	//}

	cellTime := titleRow.AddCell()
	cellTime.SetStyle(style)
	switch dataType {
	case "新增试用":
		cellTime.SetValue("新增时间")
	case "新签客户":
		cellTime.SetValue("签约时间")
	case "续约客户":
		cellTime.SetValue("续约时间")
	case "未续约客户":
		cellTime.SetValue("最近合同到期时间")

		cellAscribeContent := titleRow.AddCell()
		cellAscribeContent.SetStyle(style)
		cellAscribeContent.SetValue("不续约归因")

		cellContent := titleRow.AddCell()
		cellContent.SetStyle(style)
		cellContent.SetValue("详细原因")
	}
	cellPermissionName := titleRow.AddCell()
	cellPermissionName.SetStyle(style)
	cellPermissionName.SetValue("签约套餐")
	for _, v := range resp.List {
		dataRow := sheel.AddRow()
		dataRow.SetHeight(20)

		cellDataName := dataRow.AddCell()
		cellDataName.SetStyle(style)
		cellDataName.SetValue(v.CompanyName)

		cellDataProName := dataRow.AddCell()
		cellDataProName.SetStyle(style)
		cellDataProName.SetValue(v.ProductName)

		cellDataSellerName := dataRow.AddCell()
		cellDataSellerName.SetStyle(style)
		cellDataSellerName.SetValue(v.SellerName)

		//if dataType == "新签客户" || dataType == "续约客户" {
		cellDataMoney := dataRow.AddCell()
		cellDataMoney.SetStyle(style)
		cellDataMoney.SetValue(v.Money)
		//}

		cellDataTime := dataRow.AddCell()
		cellDataTime.SetStyle(style)
		switch dataType {
		case "新增试用":
			cellDataTime.SetValue(v.CreateTime)
		case "新签客户":
			cellDataTime.SetValue(v.StartDate)
		case "续约客户":
			cellDataTime.SetValue(v.StartDate)
		case "未续约客户":
			cellDataTime.SetValue(v.EndDate)

			cellAscribeContent := dataRow.AddCell()
			cellAscribeContent.SetStyle(style)
			cellAscribeContent.SetValue(v.AscribeContent)

			cellContent := dataRow.AddCell()
			cellContent.SetStyle(style)
			cellContent.SetValue(v.Content)
		}
		cellDataPermissionName := dataRow.AddCell()
		cellDataPermissionName.SetStyle(style)
		cellDataPermissionName.SetValue(v.PermissionName)
	}
	err = xlsxFile.Save(downLoadnFilePath)
	if err != nil {
		br.Msg = "保存文件失败"
		br.ErrMsg = "保存文件失败"
		return
	}
	randStr := time.Now().Format(utils.FormatDateTimeUnSpace)
	downloadFileName := dataType + "数据_" + randStr + ".xlsx"
	this.Ctx.Output.Download(downLoadnFilePath, downloadFileName)
	defer func() {
		os.Remove(downLoadnFilePath)
	}()
	br.Ret = 200
	br.Success = true
	br.Msg = "导出成功"
}

// @Title 上一份合同详情
// @Description 上一份合同详情接口
// @Param   CompanyContractId   query   int  true       "合同ID"
// @Success Ret=200 {object} company.GetCompanyContractMergeDetailResp
// @router /merge_company_previous/detail [get]
func (this *StatisticCompanyMergerController) CompanyPreviousDetail() {
	br := new(models.BaseResponse).Init()
	defer func() {
		this.Data["json"] = br
		this.ServeJSON()
	}()
	AdminUser := this.SysUser
	if AdminUser == nil {
		br.Msg = "请登录"
		br.ErrMsg = "请登录，用户信息为空"
		br.Ret = 408
		return
	}
	resp := new(company.GetCompanyContractDetailResp)
	companyContractId, _ := this.GetInt("CompanyContractId")
	if companyContractId < 1 {
		br.Msg = "请输入合同ID"
		return
	}
	var condition string
	var pars []interface{}
	//获取最新的一条到期的合同信息

	condition = " AND company_contract_id = ?  "
	pars = append(pars, companyContractId)
	detail, err := company.GetCompanyContracDetail(condition, pars)
	if err != nil {
		br.Msg = "详情不存在"
		br.ErrMsg = "获取失败，Err:" + err.Error()
		return
	}
	//获取前一份合同的信息
	pars = make([]interface{}, 0)
	condition = " AND company_id = ? AND company_contract_id < ?   AND status = 1    AND product_id = 2  ORDER BY company_contract_id DESC   LIMIT  1  "
	pars = append(pars, detail.CompanyId, companyContractId)
	detailPrevious, err := company.GetCompanyContracDetail(condition, pars)
	if err != nil {
		br.Msg = "详情不存在"
		br.ErrMsg = "获取失败，Err:" + err.Error()
		return
	}
	//合并合同所对应的权限
	mappermissionName, err := cygxService.GetCompanyContractPermissionNameMapById([]int{detailPrevious.CompanyContractId})
	if err != nil {
		br.Msg = "获取失败"
		br.ErrMsg = "获取失败，Err:" + err.Error()
		return
	}
	detailPrevious.PermissionName = mappermissionName[detailPrevious.CompanyContractId]
	resp.Detail = detailPrevious
	br.Ret = 200
	br.Success = true
	br.Msg = "获取成功"
	br.Data = resp
}

// @Title 权益行业套餐统计
// @Description 权益客户统计列表接口
// @Param   PageSize   query   int  true       "每页数据条数"
// @Param   CurrentIndex   query   int  true       "当前页页码，从1开始"
// @Param   Keyword   query   string  true       "客户名称"
// @Param   AdminId   query   string  true       "销售id，多个用英文逗号隔开，空字符串为全部"
// @Param   StartDate   query   string  false       "开始日期"
// @Param   EndDate   query   string  false       "结束日期"
// @Param   DataType   query   string  false       "报表类型，枚举值：`行业新签`，`行业续约`，`行业未续约`"
// @Param   PermissionName   query   string  false       "行业权限名称"
// @Param   IsExport   query   bool  false       "是否导出excel，默认是false"
// @Success 200 {object} response.IncrementalCompanyListResp
// @router /merge_company/company_contract_permission/list [get]
func (this *StatisticCompanyMergerController) CompanyContractPermissionList() {
	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")
	keyword := this.GetString("Keyword")

	startDate := this.GetString("StartDate")
	endDate := this.GetString("EndDate")
	permissionName := this.GetString("PermissionName")
	if permissionName == "" { //默认所筛选行业为医药
		permissionName = "医药"
	}

	//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
		permissionName = "" //点击下载的时候不做行业筛选
	}

	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{} // 处理权益未续约客户总量查询条件

	condition += ` AND c.product_id = ?   AND  a.status = 1 `
	pars = append(pars, 2)

	var list []*models.IncrementalList
	var listPessionGroup []*models.CompanyContractPermissionNameGroupCountResp

	var newCompanyTotal int                   //新签合同数量
	var notRenewalCompanyTotal int            //未续约客户数已确认
	var notRenewalCompanyToBeConfirmTotal int //未续约客户数待确认
	var dataTotal, trialTotal, renewalCompanyTotal int
	var notRenewalTryOut, notRenewalNotTryOut int

	//if dataType == "行业未续约" {
	//	condition += ` AND a.end_date >= ? AND a.end_date  <= ? `
	//	pars = append(pars, startDate, endDate)
	//} else {
	//	condition += ` AND a.start_date >= ? AND a.start_date <= ?  `
	//	pars = append(pars, startDate, endDate)
	//}
	//
	//conditionPerssion := condition
	//parsPerssion := pars
	//conditionPerssion += ` AND  d.permission_name IN ('医药','消费','科技','智造','策略','买方研选')`
	//
	//listPessionGroup, err := models.GetCompanyContractPermissionNameGroupCount(conditionPerssion, parsPerssion)
	//if err != nil && err.Error() != utils.ErrNoRow() {
	//	br.Msg = "获取失败"
	//	br.ErrMsg = "获取失败，Err:" + err.Error()
	//	return
	//}

	if permissionName == "" {
		condition += ` AND  d.permission_name IN ('医药','消费','科技','智造','策略','买方研选')`
	}

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

		if dataType == "行业新签" {
			conditionPerssion := condition1
			parsPerssion := pars1
			conditionPerssion += ` AND  d.permission_name IN ('医药','消费','科技','智造','策略','买方研选')`

			listPessionGroup, err = models.GetCompanyContractPermissionNameGroupCount(conditionPerssion, parsPerssion)
			if err != nil && err.Error() != utils.ErrNoRow() {
				br.Msg = "获取失败"
				br.ErrMsg = "获取失败，Err:" + err.Error()
				return
			}

			//行业筛选查询
			if permissionName != "" {
				condition1 += ` AND  d.permission_name = ? `
				pars1 = append(pars1, permissionName)
			}

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

			//列表页数据
			tmpList, err := models.GetIncrementalCompanyPermissionList(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 d.contract_type = ? `
		pars1 = append(pars1, "行业续约")
		////额外条件（续约合同的起始日期包含在所选时间段内且不包含在新签合同存续期内的客户）
		//pars1 = append(pars1, endDate)

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

		renewalCompanyTotal = total

		if dataType == "行业续约" {
			conditionPerssion := condition1
			parsPerssion := pars1
			conditionPerssion += ` AND  d.permission_name IN ('医药','消费','科技','智造','策略','买方研选')`

			listPessionGroup, err = models.GetCompanyContractPermissionNameGroupCount(conditionPerssion, parsPerssion)
			if err != nil && err.Error() != utils.ErrNoRow() {
				br.Msg = "获取失败"
				br.ErrMsg = "获取失败，Err:" + err.Error()
				return
			}
			//行业筛选查询
			if permissionName != "" {
				condition1 += ` AND  d.permission_name = ? `
				pars1 = append(pars1, permissionName)
			}
			//列表数据数量
			total, err := company.GetIncrementalNewCompanyProductPermissionCount(condition1, pars1)
			if err != nil && err.Error() != utils.ErrNoRow() {
				br.Msg = "获取失败"
				br.ErrMsg = "获取失败，Err:" + err.Error()
				return
			}
			//列表页数据
			tmpList, err := models.GetIncrementalCompanyPermissionList(condition1, pars1, startSize, pageSize)
			if err != nil {
				br.Msg = "获取失败"
				br.ErrMsg = "获取失败，Err:" + err.Error()
				return
			}
			list = tmpList
			dataTotal = total
		}
	}

	//return

	//行业未续约
	{
		condition1 := condition
		pars1 := pars
		condition1 += ` AND a.end_date >= ? AND a.end_date  <= ? `
		pars1 = append(pars1, startDate, endDate)
		condition1 += ` AND c.status not in ("永续","正式","关闭")  `

		//未续约数量
		notRenewalCondition := condition1
		notRenewalPars := pars1
		notRenewalCondition += conditionAscribRaiTotal
		notRenewalPars = append(notRenewalPars, parsAscribeRaiTotal)
		total, err := company.GetIncrementalNewCompanyProductPermissionCount(notRenewalCondition, notRenewalPars)
		if err != nil && err.Error() != utils.ErrNoRow() {
			br.Msg = "获取失败"
			br.ErrMsg = "获取失败，Err:" + err.Error()
			return
		}

		condition1 += conditionAscribRai
		pars1 = append(pars1, parsAscribeRai)
		if dataType == "行业未续约" {
			notRenewalCompanyTotal = total
			conditionPerssion := condition1
			parsPerssion := pars1
			conditionPerssion += ` AND  d.permission_name IN ('医药','消费','科技','智造','策略','买方研选')`

			listPessionGroup, err = models.GetCompanyContractPermissionNameGroupCount(conditionPerssion, parsPerssion)
			if err != nil && err.Error() != utils.ErrNoRow() {
				br.Msg = "获取失败"
				br.ErrMsg = "获取失败，Err:" + err.Error()
				return
			}
			//行业筛选查询
			if permissionName != "" {
				condition1 += ` AND  d.permission_name = ? `
				pars1 = append(pars1, permissionName)
			}

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

			list = tmpList
			dataTotal = total

		}
	}

	listLen := len(list)

	if listLen == 0 {
		list = make([]*models.IncrementalList, 0)
	}
	var companyContractIds []int
	var companyIds []int
	for i := 0; i < listLen; i++ {
		item := list[i]
		companyContractIds = append(companyContractIds, item.CompanyContractId)
		companyIds = append(companyIds, item.CompanyId)
		//新增试用不需要计算剩余日期
		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
		}
	}

	//行业未续约 需要处理公司对应行业权限下的权限状态
	if dataType == "行业未续约" {
		mappermissionNameStatus, err := cygxService.GetCompanyPermissionNameStatusMapByCompanyId(companyIds)
		if err != nil {
			br.Msg = "获取失败"
			br.ErrMsg = "获取失败，Err:" + err.Error()
			return
		}
		for _, v := range list {
			v.PermissionNameStatus = mappermissionNameStatus[fmt.Sprint(v.CompanyId, v.PermissionNameExport)]
		}
	}

	//合并合同所对应的权限
	mappermissionName, err := cygxService.GetCompanyContractPermissionNameMapById(companyContractIds)
	if err != nil {
		br.Msg = "获取失败"
		br.ErrMsg = "获取失败，Err:" + err.Error()
		return
	}
	for _, v := range list {
		v.PermissionName = mappermissionName[v.CompanyContractId]
	}

	//处理展示顺序
	permissionNameSlice := []string{"医药", "消费", "科技", "智造", "策略", "买方研选"}
	var itemspermissionNameSlice []*models.CompanyContractPermissionNameGroupCountResp
	for _, vName := range permissionNameSlice {
		item := new(models.CompanyContractPermissionNameGroupCountResp)
		for _, v := range listPessionGroup {
			if v.PermissionName != vName {
				continue
			}
			item.Total = v.Total
		}
		item.PermissionName = vName
		itemspermissionNameSlice = append(itemspermissionNameSlice, item)
	}

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

// PermissionCompanyListListExport 导出增量客户数据报表excel
func PermissionCompanyListListExport(this *StatisticCompanyMergerController, dataType string, resp response.IncrementalCompanyListResp, br *models.BaseResponse) {
	dir, err := os.Executable()
	exPath := filepath.Dir(dir)
	downLoadnFilePath := exPath + "/" + time.Now().Format(utils.FormatDateTimeUnSpace) + ".xlsx"
	xlsxFile := xlsx.NewFile()
	if err != nil {
		br.Msg = "生成文件失败"
		br.ErrMsg = "生成文件失败"
		return
	}
	style := xlsx.NewStyle()
	alignment := xlsx.Alignment{
		Horizontal: "center",
		Vertical:   "center",
		WrapText:   true,
	}
	permissionNameSlice := []string{"医药", "消费", "科技", "智造", "策略", "买方研选"}

	mapExport := make(map[string][]*models.IncrementalList)
	type IncrementalCompanyListExport struct {
		PermissionName string `description:"权限名"`
		List           []*models.IncrementalList
	}

	for _, v := range resp.List {
		if v.PermissionNameExport == "" {
			continue
		}
		mapExport[v.PermissionNameExport] = append(mapExport[v.PermissionNameExport], v)
	}

	style.Alignment = alignment
	style.ApplyAlignment = true

	for _, vName := range permissionNameSlice {
		sheel, err := xlsxFile.AddSheet(vName)
		if err != nil {
			br.Msg = "新增Sheet失败"
			br.ErrMsg = "新增Sheet失败,Err:" + err.Error()
			return
		}
		sheel.SetColWidth(0, 0, 30)
		sheel.SetColWidth(1, 1, 15)
		sheel.SetColWidth(2, 2, 15)
		sheel.SetColWidth(3, 3, 18)
		sheel.SetColWidth(4, 4, 40)
		sheel.SetColWidth(5, 5, 18)

		//表头

		titleRow := sheel.AddRow()

		cellName := titleRow.AddCell()
		cellName.SetStyle(style)
		cellName.SetValue("客户名称")

		cellSellerName := titleRow.AddCell()
		cellSellerName.SetStyle(style)
		cellSellerName.SetValue("所属销售")

		cellMoney := titleRow.AddCell()
		cellMoney.SetStyle(style)
		cellMoney.SetValue("合同金额")

		cellTime := titleRow.AddCell()
		cellTime.SetStyle(style)
		cellTime.SetValue("合同期限")

		cellPermissionName := titleRow.AddCell()
		cellPermissionName.SetStyle(style)
		cellPermissionName.SetValue("签约套餐")

		if dataType == "行业未续约" {
			cellPermissionNameStatus := titleRow.AddCell()
			cellPermissionNameStatus.SetStyle(style)
			cellPermissionNameStatus.SetValue("当前行业状态")

			cellCompanyProductStatus := titleRow.AddCell()
			cellCompanyProductStatus.SetStyle(style)
			cellCompanyProductStatus.SetValue("当前客户状态")
		}
		for _, v := range mapExport[vName] {
			dataRow := sheel.AddRow()
			dataRow.SetHeight(20)

			cellDataName := dataRow.AddCell()
			cellDataName.SetStyle(style)
			cellDataName.SetValue(v.CompanyName)

			cellDataSellerName := dataRow.AddCell()
			cellDataSellerName.SetStyle(style)
			cellDataSellerName.SetValue(v.SellerName)

			cellDataMoney := dataRow.AddCell()
			cellDataMoney.SetStyle(style)
			cellDataMoney.SetValue(v.Money)

			cellDataTime := dataRow.AddCell()
			cellDataTime.SetStyle(style)
			cellDataTime.SetValue(v.StartDate + "~" + v.EndDate)

			cellDataPermissionName := dataRow.AddCell()
			cellDataPermissionName.SetStyle(style)
			cellDataPermissionName.SetValue(v.PermissionName)

			if dataType == "行业未续约" {
				cellDataPermissionNameStatus := dataRow.AddCell()
				cellDataPermissionNameStatus.SetStyle(style)
				cellDataPermissionNameStatus.SetValue(v.PermissionNameStatus)

				cellDataCompanyProductStatus := dataRow.AddCell()
				cellDataCompanyProductStatus.SetStyle(style)
				cellDataCompanyProductStatus.SetValue(v.CompanyProductStatus)
			}
		}
	}

	err = xlsxFile.Save(downLoadnFilePath)
	if err != nil {
		br.Msg = "保存文件失败"
		br.ErrMsg = "保存文件失败"
		return
	}
	randStr := time.Now().Format(utils.FormatDateTimeUnSpace)
	downloadFileName := dataType + "数据_" + randStr + ".xlsx"
	this.Ctx.Output.Download(downLoadnFilePath, downloadFileName)
	defer func() {
		os.Remove(downLoadnFilePath)
	}()
	br.Ret = 200
	br.Success = true
	br.Msg = "导出成功"
}