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" "strconv" "strings" //"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 == "" { 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.GetCompanyContractNoRenewedAscribeList(conditionConfirm, parsConfirm, 0, 0) if err != nil && err.Error() != utils.ErrNoRow() { br.Msg = "获取失败" br.ErrMsg = "获取失败，GetCompanyNoRenewedAscribeList Err:" + err.Error() return } var noRenewedcompanyContractIds []int //已经确定未续约的公司ID if len(companyConfirmList) == 0 { noRenewedcompanyContractIds = append(noRenewedcompanyContractIds, 0) // 给一个不存在的ID } else { for _, v := range companyConfirmList { noRenewedcompanyContractIds = append(noRenewedcompanyContractIds, v.CompanyContractId) } } //是否确认续约 CRM 13.9 conditionAscribRaiTotal += ` AND a.company_contract_id IN (` + utils.GetOrmInReplace(len(noRenewedcompanyContractIds)) + `) ` // 已确认 parsAscribeRaiTotal = append(parsAscribeRaiTotal, noRenewedcompanyContractIds) conditionAscribRaiToBeTotal += ` AND a.company_contract_id NOT IN (` + utils.GetOrmInReplace(len(noRenewedcompanyContractIds)) + `) ` // 待确认 parsAscribeRaiTobeTotal = append(parsAscribeRaiTobeTotal, noRenewedcompanyContractIds) if isConfirm != -1 { if isConfirm == 0 { conditionAscribRai += ` AND a.company_contract_id NOT IN (` + utils.GetOrmInReplace(len(noRenewedcompanyContractIds)) + `) ` // 待确认 } else { conditionAscribRai += ` AND a.company_contract_id IN (` + utils.GetOrmInReplace(len(noRenewedcompanyContractIds)) + `) ` // 已确认 } parsAscribeRai = append(parsAscribeRai, noRenewedcompanyContractIds) } //归因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.GetCompanyContractNoRenewedAscribeList(conditionAscribe, parsAscribe, 0, 0) if err != nil && err.Error() != utils.ErrNoRow() { br.Msg = "获取失败" br.ErrMsg = "获取失败，GetCompanyNoRenewedAscribeList Err:" + err.Error() return } var noRenewedcompanyContractIds []int if len(companyNoRenewedAscribeList) == 0 { noRenewedcompanyContractIds = append(noRenewedcompanyContractIds, 0) // 给一个不存在的ID } else { for _, v := range companyNoRenewedAscribeList { noRenewedcompanyContractIds = append(noRenewedcompanyContractIds, v.CompanyContractId) } } conditionAscribRai += ` AND a.company_contract_id IN (` + utils.GetOrmInReplace(len(noRenewedcompanyContractIds)) + `)` parsAscribeRai = append(parsAscribeRai, noRenewedcompanyContractIds) } 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.rai_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.rai_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 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单独计算 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 var companyContractIds []int for _, item := range tmpList { //endDateTime, _ := time.Parse(utils.FormatDateTime, item.CreateTime) //item.EndDate = endDateTime.Format(utils.FormatDate) ascribecompanyIds = append(ascribecompanyIds, item.CompanyId) companyContractIds = append(companyContractIds, item.CompanyContractId) } //合同归因标签 mapGetCompanyAscribeContent, mapContent := services.GetCompanyContractAscribeContentMap(companyContractIds) mapNoRenewedNote := services.GetCompanyNoRenewedNoteMap(ascribecompanyIds) for _, item := range tmpList { item.AscribeContent = mapGetCompanyAscribeContent[item.CompanyContractId] item.Content = mapContent[item.CompanyContractId] 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 var companyIds []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 } companyIds = append(companyIds, item.CompanyId) } //合并合同所对应的权限 mappermissionName, err := cygxService.GetCompanyContractPermissionNameMapById(companyContractIds) if err != nil { br.Msg = "获取失败" br.ErrMsg = "获取失败，Err:" + err.Error() return } mapIsUserMaker := cygxService.GetCompanyProductIsUserMakerByCompanyIds(companyIds) //根据公司ID获取近四周之内有决策人互动的客户 for _, v := range list { v.PermissionName = mappermissionName[v.CompanyContractId] v.IsUserMaker = mapIsUserMaker[v.CompanyId] } 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("所属销售") cellMoney := titleRow.AddCell() cellMoney.SetStyle(style) cellMoney.SetValue("合同金额") 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 } notRenewalCompanyTotal = total condition1 += conditionAscribRai pars1 = append(pars1, parsAscribeRai) 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单独计算 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{"医药", "消费", "科技", "智造", "策略", utils.CHART_PERMISSION_NAME_MF_YANXUAN, utils.YAN_XUAN_KOU_DIAN_BAO_NAME} 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 = "导出成功" } // @Title 权益客户续约率统计 // @Description 权益客户续约率统计接口 // @Param PageSize query int true "每页数据条数" // @Param CurrentIndex query int true "当前页页码，从1开始" // @Param AdminId query string true "销售id，多个用英文逗号隔开，空字符串为全部" // @Param StartDate query string false "开始日期" // @Param EndDate query string false "结束日期" // @Param ContractDataType query string false "合同类型，枚举值：`续约合同`，`到期合同`" // @Success 200 {object} response.IncrementalCompanyListResp // @router /merge_company/company_contract_percentage/list [get] func (this *StatisticCompanyMergerController) CompanyContractPercentageList() { 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") contractDataType := this.GetString("ContractDataType") startDate := this.GetString("StartDate") endDate := this.GetString("EndDate") initendDate := this.GetString("EndDate") if startDate == "" || endDate == "" { br.Msg = "开始时间或结束时间不能为空" return } //如果传的查询方式是今年，那么就以当前时间作为截止时间 //if endDate == strconv.Itoa(time.Now().Year())+"-12-31" { // endDate = time.Now().Format(utils.FormatDate) //} //判断结束时间是否晚于当前时间，如果晚于当前时间，那么就把当前时间作为截止时间。 endDateTime, _ := time.Parse(utils.FormatDate, endDate) if endDateTime.After(time.Now()) { endDate = time.Now().Format(utils.FormatDate) } //fmt.Println("endDate", endDate) if contractDataType == "" { contractDataType = "续约合同" } var startSize int if pageSize <= 0 { pageSize = utils.PageSize20 } if currentIndex <= 0 { currentIndex = 1 } startSize = utils.StartIndex(currentIndex, pageSize) var condition string var pars []interface{} //条件 if adminId != "" { condition += ` AND c.seller_id in (` + adminId + `) ` } else { //根据当前角色来获取查询条件 condition, pars = getQueryParams(condition, pars, sysUser, "c.") } //权益有效合同 condition += ` AND c.product_id = 2 AND a.status = 1 ` var list []*models.IncrementalList var renewalRateMoney string //合同金额续约率 var renewalRateMoneyContent string //合同金额续约率详情 var renewalRateTotal string //合同数量续约率 var renewalRateTotalContent string //合同数量续约率详情 var renewalContractTotal int //续约合同数量 var renewalContractMoney int //续约合同数量 var expireRenewalContractTotal int //到期合同数量 var expireRenewalContractMoney int //到期合同数量 var dataTotal int //分页数据 conditionRenewal := condition // 续约合同查询条件查询条件 parsRenewal := pars conditionRenewal += ` AND a.start_date >= ? AND a.start_date <= ? ` parsRenewal = append(parsRenewal, startDate, endDate) conditionRenewal += ` AND a.rai_contract_type = ? ` parsRenewal = append(parsRenewal, "续约合同") totalRenewal, err := company.GetIncrementalRenewalCompanyProductMergeCount(conditionRenewal, parsRenewal) if err != nil { br.Msg = "获取失败" br.ErrMsg = "获取失败，Err:" + err.Error() return } totalMoneyRenewal, err := company.GetIncrementalRenewalCompanyProductMergeSumMoney(conditionRenewal, parsRenewal) if err != nil { br.Msg = "获取失败" br.ErrMsg = "获取失败，Err:" + err.Error() return } //续约合同查询条件查询条件 end conditionRenewalEnd := condition // 今年结束的续约合同查询条件 parsRenewalEnd := pars conditionRenewalEnd += ` AND a.end_date >= ? AND a.end_date <= ? ` parsRenewalEnd = append(parsRenewalEnd, startDate, endDate) conditionRenewalEnd += ` AND a.rai_contract_type = ? ` parsRenewalEnd = append(parsRenewalEnd, "续约合同") totalRenewalEnd, err := company.GetIncrementalRenewalCompanyProductMergeCount(conditionRenewalEnd, parsRenewalEnd) if err != nil && err.Error() != utils.ErrNoRow() { br.Msg = "获取失败" br.ErrMsg = "获取失败，Err:" + err.Error() return } totalMoneyRenewalEnd, err := company.GetIncrementalRenewalCompanyProductMergeSumMoney(conditionRenewalEnd, parsRenewalEnd) if err != nil { br.Msg = "获取失败" br.ErrMsg = "获取失败，Err:" + err.Error() return } // 今年结束的续约合同查询条件end conditionNew := condition // 新签合同查询条件 parsNew := pars conditionNew += ` AND a.end_date >= ? AND a.end_date <= ? ` parsNew = append(parsNew, startDate, endDate) conditionNew += ` AND a.rai_contract_type = ? ` parsNew = append(parsNew, "新签合同") totalNew, err := company.GetIncrementalRenewalCompanyProductMergeCount(conditionNew, parsNew) if err != nil && err.Error() != utils.ErrNoRow() { br.Msg = "获取失败" br.ErrMsg = "获取失败，Err:" + err.Error() return } totalMoneyNew, err := company.GetIncrementalRenewalCompanyProductMergeSumMoney(conditionNew, parsNew) if err != nil { br.Msg = "获取失败" br.ErrMsg = "获取失败，Err:" + err.Error() return } // 新签合同查询条件 end //分子或者分母为零的时候，不做计算 if totalRenewal == 0 || totalNew+totalRenewalEnd == 0 { renewalRateMoney = "0%" renewalRateTotal = "0%" } else { renewalRateMoney = fmt.Sprint(utils.SubFloatToString(float64(totalMoneyRenewal)/float64(totalMoneyNew+totalMoneyRenewalEnd)*100, 2), "%") renewalRateTotal = fmt.Sprint(utils.SubFloatToString(float64(totalRenewal)/float64(totalNew+totalRenewalEnd)*100, 2), "%") } renewalRateTotalContent = fmt.Sprint(int(totalMoneyRenewal), "/", int(totalMoneyNew+totalMoneyRenewalEnd)) renewalRateMoneyContent = fmt.Sprint(totalRenewal, "份/", totalNew+totalRenewalEnd, "份") if contractDataType == "续约合同" { condition1 := condition pars1 := pars condition1 += ` AND a.start_date >= ? AND a.start_date <= ? ` pars1 = append(pars1, startDate, endDate) condition1 += ` AND a.rai_contract_type = ? ` pars1 = append(pars1, "续约合同") total, err := company.GetIncrementalRenewalCompanyProductMergeCount(condition1, pars1) if err != nil && err.Error() != utils.ErrNoRow() { br.Msg = "获取失败" br.ErrMsg = "获取失败，Err:" + err.Error() return } totalMoney, err := company.GetIncrementalRenewalCompanyProductMergeSumMoney(condition1, pars1) if err != nil && err.Error() != utils.ErrNoRow() { br.Msg = "获取失败" br.ErrMsg = "获取失败，Err:" + err.Error() return } renewalContractTotal = total renewalContractMoney = int(totalMoney) //列表页数据 tmpList, err := models.GetIncrementalCompanyMergeList(condition1, pars1, startSize, pageSize) if err != nil { br.Msg = "获取失败" br.ErrMsg = "获取失败，Err:" + err.Error() return } list = tmpList dataTotal = total } if contractDataType == "到期合同" { condition1 := condition pars1 := pars //判断结束时间是否晚于当前时间，如果晚于当前时间，那么就把当前时间作为截止时间。并且当前这天的到期的合同，不查询 endDateTime, _ := time.Parse(utils.FormatDate, initendDate) if endDateTime.After(time.Now()) { endDate = time.Now().Format(utils.FormatDate) condition1 += ` AND a.end_date >= ? AND a.end_date < ? ` } else { condition1 += ` AND a.end_date >= ? AND a.end_date <= ? ` } //condition1 += ` AND a.end_date >= ? AND a.end_date <= ? ` pars1 = append(pars1, startDate, endDate) total, err := company.GetIncrementalRenewalCompanyProductMergeCount(condition1, pars1) if err != nil && err.Error() != utils.ErrNoRow() { br.Msg = "获取失败" br.ErrMsg = "获取失败，Err:" + err.Error() return } totalMoney, err := company.GetIncrementalRenewalCompanyProductMergeSumMoney(condition1, pars1) if err != nil && err.Error() != utils.ErrNoRow() { br.Msg = "获取失败" br.ErrMsg = "获取失败，Err:" + err.Error() return } expireRenewalContractTotal = total expireRenewalContractMoney = int(totalMoney) //列表页数据 tmpList, err := models.GetIncrementalCompanyMergeListEnd(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 for i := 0; i < listLen; i++ { item := list[i] companyContractIds = append(companyContractIds, item.CompanyContractId) } //合并合同所对应的权限 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.IncrementalCompanyPercentageListResp{ Paging: page, RenewalContractTotal: renewalContractTotal, RenewalContractMoney: renewalContractMoney, ExpireRenewalContractTotal: expireRenewalContractTotal, ExpireRenewalContractMoney: expireRenewalContractMoney, RenewalRateMoney: renewalRateMoney, RenewalRateMoneyContent: renewalRateMoneyContent, RenewalRateTotal: renewalRateTotal, RenewalRateTotalContent: renewalRateTotalContent, List: list, } br.Ret = 200 br.Success = true br.Msg = "获取成功" br.Data = resp } // @Title 权益客户续约率统计所能查询的年份 // @Description权益客户续约率统计所能查询的年份接口 // @Success 200 {object} response.IncrementalCompanyListResp // @router /merge_company/get_year_list [get] func (this *StatisticCompanyMergerController) GetYearList() { 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 } thisYear := time.Now().Year() startYear := 2022 //初始查询年份 resp := new(company.SearchYearListResp) var yearList []int for i := startYear; i <= thisYear; i++ { yearList = append(yearList, i) } resp.List = yearList 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 AdminId query string true "销售id，多个用英文逗号隔开，空字符串为全部" // @Param StartDate query string false "开始日期" // @Param EndDate query string false "结束日期" // @Param ContractDataType query string false "合同类型，枚举值：`续约合同`，`到期合同`" // @Success 200 {object} response.IncrementalCompanyListResp // @router /merge_company/company_contract_percentage/listV2 [get] func (this *StatisticCompanyMergerController) CompanyContractPercentageListV2() { 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") contractDataType := this.GetString("ContractDataType") startDate := this.GetString("StartDate") endDate := this.GetString("EndDate") //initendDate := this.GetString("EndDate") if startDate == "" || endDate == "" { br.Msg = "开始时间或结束时间不能为空" return } //如果传的查询方式是今年，那么就以当前时间作为截止时间 //if endDate == strconv.Itoa(time.Now().Year())+"-12-31" { // endDate = time.Now().Format(utils.FormatDate) //} //判断结束时间是否晚于当前时间，如果晚于当前时间，那么就把当前时间作为截止时间。 endDateTime, _ := time.Parse(utils.FormatDate, endDate) if endDateTime.After(time.Now()) { endDate = time.Now().Format(utils.FormatDate) } //fmt.Println("endDate", endDate) if contractDataType == "" { contractDataType = "续约合同" } var startSize int if pageSize <= 0 { pageSize = utils.PageSize20 } if currentIndex <= 0 { currentIndex = 1 } startSize = utils.StartIndex(currentIndex, pageSize) var condition string var pars []interface{} //条件 if adminId != "" { condition += ` AND c.seller_id in (` + adminId + `) ` } else { //根据当前角色来获取查询条件 condition, pars = getQueryParams(condition, pars, sysUser, "c.") } //权益有效合同 condition += ` AND c.product_id = 2 AND a.status = 1 ` var list []*models.IncrementalList var renewalRateMoney string //合同金额续约率 var renewalRateMoneyContent string //合同金额续约率详情 var renewalRateTotal string //合同数量续约率 var renewalRateTotalContent string //合同数量续约率详情 var renewalContractTotal int //续约合同数量 var renewalContractMoney int //续约合同数量 var expireRenewalContractTotal int //到期合同数量 var expireRenewalContractMoney int //到期合同数量 var dataTotal int //分页数据 //conditionRenewal := condition // 续约合同查询条件查询条件 //parsRenewal := pars //conditionRenewal += ` AND a.start_date >= ? AND a.start_date <= ? ` //conditionRenewal += ` AND a.end_date >= ? AND a.end_date <= ? ` //parsRenewal = append(parsRenewal, startDate, endDate) ////conditionRenewal += ` AND a.rai_contract_type = ? ` ////parsRenewal = append(parsRenewal, "续约合同") // //conditionRenewal += ` AND a.company_ascribe_id > 0 AND a.company_ascribe_id !=9 ` // 已确认未续约 //totalRenewal, err := company.GetIncrementalRenewalCompanyProductMergeCount(conditionRenewal, parsRenewal) //if err != nil { // br.Msg = "获取失败" // br.ErrMsg = "获取失败，Err:" + err.Error() // return //} // //totalMoneyRenewal, err := company.GetIncrementalRenewalCompanyProductMergeSumMoney(conditionRenewal, parsRenewal) //if err != nil { // br.Msg = "获取失败" // br.ErrMsg = "获取失败，Err:" + err.Error() // return //} //续约合同查询条件查询条件 end //conditionRenewalEnd := condition // 今年结束的续约合同查询条件 //parsRenewalEnd := pars // ////startDateTime, _ := time.Parse(utils.FormatDate, endDate) ////startDateTime = startDateTime.AddDate(-1, 0, 2) //合同开始时间，与查询的结束时间，之间相差的天数大于 365天。 ////fmt.Println("startDateTime", startDateTime) //conditionRenewalEnd += ` AND a.end_date >= ? AND a.end_date <= ? ` //parsRenewalEnd = append(parsRenewalEnd, startDate, endDate) //conditionRenewalEnd += ` AND a.rai_contract_type = ? ` //parsRenewalEnd = append(parsRenewalEnd, "续约合同") // //totalRenewalEnd, err := company.GetIncrementalRenewalCompanyProductMergeCount(conditionRenewalEnd, parsRenewalEnd) //if err != nil && err.Error() != utils.ErrNoRow() { // br.Msg = "获取失败" // br.ErrMsg = "获取失败，Err:" + err.Error() // return //} ////return //totalMoneyRenewalEnd, err := company.GetIncrementalRenewalCompanyProductMergeSumMoney(conditionRenewalEnd, parsRenewalEnd) //if err != nil { // br.Msg = "获取失败" // br.ErrMsg = "获取失败，Err:" + err.Error() // return //} // 今年结束的续约合同查询条件end // //conditionNew := condition // 新签合同查询条件 //parsNew := pars //conditionNew += ` AND a.end_date >= ? AND a.end_date <= ? ` //parsNew = append(parsNew, startDate, endDate) //conditionNew += ` AND a.rai_contract_type = ? ` //parsNew = append(parsNew, "新签合同") //totalNew, err := company.GetIncrementalRenewalCompanyProductMergeCount(conditionNew, parsNew) //if err != nil && err.Error() != utils.ErrNoRow() { // br.Msg = "获取失败" // br.ErrMsg = "获取失败，Err:" + err.Error() // return //} //totalMoneyNew, err := company.GetIncrementalRenewalCompanyProductMergeSumMoney(conditionNew, parsNew) //if err != nil { // br.Msg = "获取失败" // br.ErrMsg = "获取失败，Err:" + err.Error() // return //} // 新签合同查询条件 end //fmt.Println("totalNew", totalNew) //fmt.Println("totalRenewalEnd", totalRenewalEnd) condition1 := condition pars1 := pars //condition1 += ` AND a.start_date >= ? AND a.start_date <= ? ` condition1 += ` AND a.end_date >= ? AND a.end_date <= ? ` pars1 = append(pars1, startDate, endDate) //condition1 += ` AND a.rai_contract_type = ? ` //pars1 = append(pars1, "续约合同") condition1 += ` AND a.company_ascribe_id > 0 AND a.company_ascribe_id !=9 ` // 已确认未续约 totalNo, err := company.GetIncrementalRenewalCompanyProductMergeCount(condition1, pars1) if err != nil && err.Error() != utils.ErrNoRow() { br.Msg = "获取失败" br.ErrMsg = "获取失败，Err:" + err.Error() return } totalMoney, err := company.GetIncrementalRenewalCompanyProductMergeSumMoney(condition1, pars1) if err != nil && err.Error() != utils.ErrNoRow() { br.Msg = "获取失败" br.ErrMsg = "获取失败，Err:" + err.Error() return } renewalContractTotal = totalNo renewalContractMoney = int(totalMoney) if contractDataType == "续约合同" || contractDataType == "确认不续约合同" { //列表页数据 tmpList, err := models.GetIncrementalCompanyMergeListEnd(condition1, pars1, startSize, pageSize) if err != nil { br.Msg = "获取失败" br.ErrMsg = "获取失败，Err:" + err.Error() return } list = tmpList dataTotal = totalNo } conditionEnd1 := condition parsEnd1 := pars //判断结束时间是否晚于当前时间，如果晚于当前时间，那么就把当前时间作为截止时间。并且当前这天的到期的合同，不查询 //endDateTime, _ := time.Parse(utils.FormatDate, initendDate) //if endDateTime.After(time.Now()) { // endDate = time.Now().Format(utils.FormatDate) // condition1 += ` AND a.end_date >= ? AND a.end_date < ? ` //} else { // condition1 += ` AND a.end_date >= ? AND a.end_date <= ? ` //} conditionEnd1 += ` AND a.end_date >= ? AND a.end_date <= ? AND a.company_ascribe_id !=9 ` parsEnd1 = append(parsEnd1, startDate, endDate) //condition1 += ` AND a.start_date < ? ` //pars1 = append(pars1, startDateTime) totalEnd, err := company.GetIncrementalRenewalCompanyProductMergeCount(conditionEnd1, parsEnd1) if err != nil && err.Error() != utils.ErrNoRow() { br.Msg = "获取失败" br.ErrMsg = "获取失败，Err:" + err.Error() return } totalMoneyEnd, err := company.GetIncrementalRenewalCompanyProductMergeSumMoney(conditionEnd1, parsEnd1) if err != nil && err.Error() != utils.ErrNoRow() { br.Msg = "获取失败" br.ErrMsg = "获取失败，Err:" + err.Error() return } expireRenewalContractTotal = totalEnd expireRenewalContractMoney = int(totalMoneyEnd) if contractDataType == "到期合同" { //列表页数据 tmpList, err := models.GetIncrementalCompanyMergeListEnd(conditionEnd1, parsEnd1, startSize, pageSize) if err != nil { br.Msg = "获取失败" br.ErrMsg = "获取失败，Err:" + err.Error() return } list = tmpList dataTotal = totalEnd } //分子或者分母为零的时候，不做计算 if totalNo == 0 || totalEnd == 0 { renewalRateMoney = "0%" renewalRateTotal = "0%" } else { renewalRateMoney = fmt.Sprint(utils.SubFloatToString(float64(totalMoney)/float64(totalMoneyEnd)*100, 2), "%") renewalRateTotal = fmt.Sprint(utils.SubFloatToString(float64(totalNo)/float64(totalEnd)*100, 2), "%") } renewalRateTotalContent = fmt.Sprint(int(totalMoney), "/", int(totalMoneyEnd)) renewalRateMoneyContent = fmt.Sprint(totalNo, "份/", totalEnd, "份") 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) } //合同归因标签 mapGetCompanyAscribeContent, mapContent := services.GetCompanyContractAscribeContentMap(companyContractIds) //合并合同所对应的权限 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] if mapGetCompanyAscribeContent[v.CompanyContractId] != "" { v.Content = mapContent[v.CompanyContractId] v.AscribeContent = mapGetCompanyAscribeContent[v.CompanyContractId] v.IsShowNoRenewedNote = true } } page := paging.GetPaging(currentIndex, pageSize, dataTotal) resp := response.IncrementalCompanyPercentageListResp{ Paging: page, RenewalContractTotal: renewalContractTotal, RenewalContractMoney: renewalContractMoney, ExpireRenewalContractTotal: expireRenewalContractTotal, ExpireRenewalContractMoney: expireRenewalContractMoney, RenewalRateMoney: renewalRateMoney, RenewalRateMoneyContent: renewalRateMoneyContent, RenewalRateTotal: renewalRateTotal, RenewalRateTotalContent: renewalRateTotalContent, List: list, } br.Ret = 200 br.Success = true br.Msg = "获取成功" br.Data = resp } func init213() { var condition string var pars []interface{} condition = " AND rai_contract_type = '续约合同' AND product_id = 2 " var companyContractIds []string companyContractList, e := company.GetCompanyContractList(condition, pars) if e != nil && e.Error() != utils.ErrNoRow() { fmt.Println(e) return } for _, v := range companyContractList { fmt.Println(v.CompanyContractId) startDateTime, _ := time.Parse(utils.FormatDate, v.StartDate) startDateTime = startDateTime.AddDate(-1, 0, 0) totalLastYear, err := company.GetCompanyContractCountRaiByLastYear(v.CompanyId, startDateTime.Format(utils.FormatDate)) if err != nil { fmt.Println(err) return } if totalLastYear > 0 { companyContractIds = append(companyContractIds, strconv.Itoa(v.CompanyContractId)) } } fmt.Println("companyContractIds", len(companyContractIds)) fmt.Println(strings.Join(companyContractIds, ",")) err := company.UpdateCompanyContractRaiContractTypeInit(strings.Join(companyContractIds, ",")) fmt.Println(err) return } func init323() { var conditionConfirm string var parsConfirm []interface{} companyConfirmList, err := company.GetCompanyContractNoRenewedAscribeList(conditionConfirm, parsConfirm, 0, 0) if err != nil && err.Error() != utils.ErrNoRow() { fmt.Println(err) return } for _, v := range companyConfirmList { err = company.UpdateCompanyContractCompanyAscribeId(v.CompanyAscribeId, v.CompanyContractId) if err != nil && err.Error() != utils.ErrNoRow() { fmt.Println(err) return } } }