package census import ( "bytes" "fmt" "github.com/gin-gonic/gin" "github.com/go-playground/validator/v10" "github.com/shopspring/decimal" "github.com/tealeg/xlsx" "hongze/fms_api/controller/resp" "hongze/fms_api/global" "hongze/fms_api/models" "hongze/fms_api/models/base" "hongze/fms_api/models/crm" "hongze/fms_api/models/fms" "hongze/fms_api/services/alarm_msg" crmService "hongze/fms_api/services/crm" "hongze/fms_api/utils" "net/http" "strconv" "strings" "time" ) // SellerController 销售统计 type SellerController struct{} // GroupInvoiceList // @Title 销售组开票统计列表 // @Description 销售组开票统计列表 // @Param StartDate query string false "开始日期" // @Param EndDate query string false "结束日期" // @Param SortField query int false "排序字段: 1-开票金额; 2-组别占比" // @Param SortType query int false "排序方式: 1-正序; 2-倒序" // @Param IsExport query int false "是否导出: 0-否; 1-是" // @Success 200 {object} fms.CensusSellerGroupInvoiceItem // @router /census/seller/group_invoice_list [get] func (ct *SellerController) GroupInvoiceList(c *gin.Context) { var req fms.CensusSellerGroupInvoiceListReq if e := c.BindQuery(&req); e != nil { err, ok := e.(validator.ValidationErrors) if !ok { resp.FailData("参数解析失败", "Err:"+e.Error(), c) return } resp.FailData("参数解析失败", err.Translate(global.Trans), c) return } var departmentId int if req.SellerType == 1 { departmentId = crm.SellerDepartmentId }else if req.SellerType == 2 { departmentId = crm.RaiSellerDepartmentId }else if req.SellerType == 0 { resp.Fail("请选择销售类型", c) return }else { resp.Fail("请选择正确的销售类型", c) return } outCond := ` department_id = %d AND parent_id = 0 ` outCond = fmt.Sprintf(outCond, departmentId) cond := ` (invoice_type = %d OR invoice_type = %d) AND a.is_deleted = 0 AND a.seller_id != 0 ` cond = fmt.Sprintf(cond, fms.ContractInvoiceTypeMake, fms.ContractInvoiceTypePreMake) //adminCond := ` (invoice_type = %d OR invoice_type = %d) AND is_deleted = 0 AND seller_group_id != 0 ` //adminCond = fmt.Sprintf(cond, fms.ContractInvoiceTypeMake, fms.ContractInvoiceTypePreMake) pars := make([]interface{}, 0) // 开票日期 if req.StartDate != "" && req.EndDate != "" { st := fmt.Sprint(req.StartDate, " 00:00:00") ed := fmt.Sprint(req.EndDate, " 23:59:59") cond += ` AND (invoice_time BETWEEN '%s' AND '%s')` cond = fmt.Sprintf(cond, st, ed) //adminCond += ` AND (invoice_time BETWEEN '%s' AND '%s')` //adminCond = fmt.Sprintf(cond, st, ed) } if req.CompanyType == 1 { cond += ` AND b.contract_type = 1 ` //historyCond += ` AND new_company = 1 ` } else if req.CompanyType == 2 { cond += ` AND b.contract_type IN (2,3,4) ` //historyCond += ` AND new_company = 0 ` } page := new(base.Page) page.SetPageSize(req.PageSize) page.SetCurrent(req.Current) // 排序, 默认开票金额倒序 sortFieldMap := map[int]string{0: "invoice_amount", 1: "invoice_amount", 2: "group_rate"} sortTypeMap := map[int]bool{0: false, 1: true, 2: false} page.AddOrderItem(base.OrderItem{Column: sortFieldMap[req.SortField], Asc: sortTypeMap[req.SortType]}) if req.IsExport == 1 { page.SetPageSize(10000) page.SetCurrent(1) } // 查询开票金额总和(减少子查询) invOB := new(fms.ContractInvoice) sumCond := cond sumPars := make([]interface{}, 0) invSum, e := invOB.Sum("amount", sumCond, sumPars) if e != nil { resp.FailMsg("获取失败", "获取开票金额总和失败, Err: "+e.Error(), c) return } // 查询列表 groupOB := new(crm.SysGroup) //totalCond := outCond //totalPars := make([]interface{}, 0) //total, e := groupOB.Count(totalCond, totalPars) //if e != nil { // resp.FailMsg("获取失败", "获取销售组开票统计列表总数失败, Err: "+e.Error(), c) // return //} //list, e := fms.GetCensusSellerGroupInvoicePageList(page, cond, outCond, pars, invSum) //if e != nil { // resp.FailMsg("获取失败", "获取销售组开票统计列表失败, Err: "+e.Error(), c) // return //} groupCond := ` department_id = %d AND parent_id = 0 ` groupCond = fmt.Sprintf(groupCond, departmentId) groupPars := make([]interface{}, 0) groupList, e := groupOB.List(groupCond, groupPars) if e != nil { resp.FailMsg("获取失败", "获取组别列表失败, Err: "+e.Error(), c) return } //total := len(groupList) groupMap := make(map[int]*crm.SysGroup) groupIdSlice := make([]string,0) for i := range groupList { groupMap[groupList[i].GroupId] = groupList[i] groupIdSlice = append(groupIdSlice, strconv.Itoa(groupList[i].GroupId)) } groupStr := strings.Join(groupIdSlice, ",") total, list, e := fms.GetCensusSellerGroupInvoicePageListV2(page, groupStr, cond, pars, invSum) if e != nil { resp.FailMsg("获取失败", "获取销售组开票统计列表失败, Err: "+e.Error(), c) return } for _, v := range list { if group, ok := groupMap[v.GroupId]; ok{ v.GroupName = group.GroupName } } // 处理百分比, 乘100并保留两位小数 mulNum := decimal.NewFromFloat(100) for i := range list { d := decimal.NewFromFloat(list[i].GroupRate) d = d.Mul(mulNum).Round(2) a, _ := d.Float64() list[i].GroupRate = a } // 是否导出 if req.IsExport == 1 { ExportGroupInvoiceList(c, list, req) return } page.SetTotal(int64(total)) baseData := new(base.BaseData) baseData.SetPage(page) baseData.SetList(list) resp.OkData("获取成功", baseData, c) } // ExportGroupInvoiceList 导出销售组开票统计列表 func ExportGroupInvoiceList(c *gin.Context, list []*fms.CensusSellerGroupInvoiceItem, req fms.CensusSellerGroupInvoiceListReq) { // 生成Excel文件 xlsxFile := xlsx.NewFile() style := xlsx.NewStyle() alignment := xlsx.Alignment{ Horizontal: "center", Vertical: "center", WrapText: true, } style.Alignment = alignment style.ApplyAlignment = true sheetName := "销售组开票统计" sheet, err := xlsxFile.AddSheet(sheetName) if err != nil { resp.FailData("新增Sheet失败", "Err:"+err.Error(), c) return } // 存在筛选则前两行显示时间信息 if req.StartDate != "" && req.EndDate != "" { timeData := fmt.Sprintf("时间：%s至%s", req.StartDate, req.EndDate) rowA := sheet.AddRow() cellAA := rowA.AddCell() cellAA.SetString("销售统计表") cellAA.SetStyle(style) rowB := sheet.AddRow() rowB.AddCell().SetString(timeData) // 第三行空出 sheet.AddRow() } // 数据表头 rowTitle := []string{"排名", "销售组别", "收入金额（元）", "组别占比"} titleRow := sheet.AddRow() for i := range rowTitle { v := titleRow.AddCell() v.SetString(rowTitle[i]) v.SetStyle(style) } // 填充数据 for k, v := range list { dataRow := sheet.AddRow() dataRow.AddCell().SetString(fmt.Sprint(k + 1)) // 排名 dataRow.AddCell().SetString(v.GroupName) // 销售组别 dataRow.AddCell().SetString(fmt.Sprint(v.InvoiceAmount)) // 开票金额 dataRow.AddCell().SetString(fmt.Sprint(v.GroupRate, "%")) // 组别占比 } // 输出文件 var buffer bytes.Buffer _ = xlsxFile.Write(&buffer) content := bytes.NewReader(buffer.Bytes()) randStr := time.Now().Format(utils.FormatDateTimeUnSpace) fileName := sheetName + randStr + ".xlsx" c.Writer.Header().Add("Content-Disposition", fmt.Sprintf(`attachment; filename="%s"`, fileName)) c.Writer.Header().Add("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") http.ServeContent(c.Writer, c.Request, fileName, time.Now(), content) } // InvoiceList // @Title 销售开票统计列表 // @Description 销售开票统计列表 // @Param GroupId query int false "组别ID" // @Param StartDate query string false "开始日期" // @Param EndDate query string false "结束日期" // @Param SortField query int false "排序字段: 1-开票金额; 2-组别占比; 3-全员占比" // @Param SortType query int false "排序方式: 1-正序; 2-倒序" // @Param IsExport query int false "是否导出: 0-否; 1-是" // @Success 200 {object} fms.CensusSellerInvoiceListReq // @router /census/seller/invoice_list [get] func (ct *SellerController) InvoiceList(c *gin.Context) { var req fms.CensusSellerInvoiceListReq if e := c.BindQuery(&req); e != nil { err, ok := e.(validator.ValidationErrors) if !ok { resp.FailData("参数解析失败", "Err:"+e.Error(), c) return } resp.FailData("参数解析失败", err.Translate(global.Trans), c) return } var departmentId int if req.SellerType == 1 { departmentId = crm.SellerDepartmentId }else if req.SellerType == 2 { departmentId = crm.RaiSellerDepartmentId }else if req.SellerType == 0 { resp.Fail("请选择销售类型", c) return }else { resp.Fail("请选择正确的销售类型", c) return } pars := make([]interface{}, 0) adminPars := make([]interface{}, 0) outCond := ` a.department_id = %d ` outCond = fmt.Sprintf(outCond, departmentId) adminCond := ` department_id = %d ` adminCond = fmt.Sprintf(adminCond, departmentId) totalCond := ` department_id = %d ` totalCond = fmt.Sprintf(totalCond, departmentId) totalPars := make([]interface{}, 0) if !req.ShowResign { adminCond += ` AND enabled = 1 ` } if req.GroupId > 0 { // 筛选组别时, 查询当前组别的下级组(因为admin表存的group_id, 有三级的存的是子ID, 只有二级的存的才是父ID =_=!) groupCond := `parent_id = ?` groupPars := make([]interface{}, 0) groupPars = append(groupPars, req.GroupId) groupOB := new(crm.SysGroup) groupList, e := groupOB.List(groupCond, groupPars) if e != nil { resp.FailMsg("获取失败", "获取组别下级组列表失败, Err: "+e.Error(), c) return } groupIds := make([]int, 0) groupIds = append(groupIds, req.GroupId) for i := range groupList { groupIds = append(groupIds, groupList[i].GroupId) } outCond += ` AND a.group_id IN (?) ` pars = append(pars, groupIds) adminCond += ` AND group_id IN (?) ` adminPars = append(adminPars, groupIds) totalCond += ` AND group_id IN (?) ` totalPars = append(totalPars, groupIds) } sumCond := ` (invoice_type = ? OR invoice_type = ? ) AND a.is_deleted = 0 AND a.seller_id != 0 ` sumPars := make([]interface{}, 0) sumPars = append(sumPars, fms.ContractInvoiceTypeMake, fms.ContractInvoiceTypePreMake) cond := ` (c.invoice_type = %d OR c.invoice_type = %d) AND c.is_deleted = 0 AND c.seller_id != 0 ` inCond := ` (invoice_type = %d OR invoice_type = %d) AND is_deleted = 0 AND seller_id != 0 ` cond = fmt.Sprintf(cond, fms.ContractInvoiceTypeMake, fms.ContractInvoiceTypePreMake) inCond = fmt.Sprintf(inCond, fms.ContractInvoiceTypeMake, fms.ContractInvoiceTypePreMake) if req.CompanyType == 1 { cond += ` AND b.contract_type = 1 ` //historyCond += ` AND new_company = 1 ` } else if req.CompanyType == 2 { cond += ` AND b.contract_type IN (2,3,4) ` //historyCond += ` AND new_company = 0 ` } // 开票日期 if req.StartDate != "" && req.EndDate != "" { st := fmt.Sprint(req.StartDate, " 00:00:00") ed := fmt.Sprint(req.EndDate, " 23:59:59") cond += ` AND (c.invoice_time BETWEEN '%s' AND '%s')` inCond += ` AND (invoice_time BETWEEN '%s' AND '%s')` cond = fmt.Sprintf(cond, st, ed) inCond = fmt.Sprintf(inCond, st, ed) sumCond += ` AND (invoice_time BETWEEN ? AND ?)` sumPars = append(sumPars, st, ed) } page := new(base.Page) page.SetPageSize(req.PageSize) page.SetCurrent(req.Current) // 排序, 默认开票金额倒序 sortFieldMap := map[int]string{0: "invoice_amount", 1: "invoice_amount", 2: "group_rate", 3: "seller_rate"} sortTypeMap := map[int]bool{0: false, 1: true, 2: false} page.AddOrderItem(base.OrderItem{Column: sortFieldMap[req.SortField], Asc: sortTypeMap[req.SortType]}) if req.IsExport == 1 { page.SetPageSize(10000) page.SetCurrent(1) } // 查询开票金额总和(减少子查询) invOB := new(fms.ContractInvoice) invSum, e := invOB.Sum("amount", sumCond, sumPars) if e != nil { resp.FailMsg("获取失败", "获取开票金额总和失败, Err: "+e.Error(), c) return } // 查询列表 adminOB := new(crm.Admin) //total, e := adminOB.Count(totalCond, totalPars) //if e != nil { // resp.FailMsg("获取失败", "获取销售开票统计列表总数失败, Err: "+e.Error(), c) // return //} //list, e := fms.GetCensusSellerInvoicePageList(page, cond, outCond, pars, invSum) //if e != nil { // resp.FailMsg("获取失败", "获取销售开票统计列表失败, Err: "+e.Error(), c) // return //} adminList, e := adminOB.List(adminCond, adminPars) if e != nil { resp.FailMsg("获取失败", "获取销售列表失败, Err: "+e.Error(), c) return } //total := len(adminList) adminIdSlice := make([]string,0) sellerMap := make(map[int]*crm.Admin) for i := range adminList { sellerMap[adminList[i].AdminId] = adminList[i] adminIdSlice = append(adminIdSlice, strconv.Itoa(adminList[i].AdminId)) } adminStr := strings.Join(adminIdSlice, ",") total, list, e := fms.GetCensusSellerInvoicePageListV2(page, adminStr, inCond, cond, pars, invSum) if e != nil { resp.FailMsg("获取失败", "获取销售开票统计列表失败, Err: "+e.Error(), c) return } for _, v := range list { if admin, ok := sellerMap[v.SellerId]; ok{ v.GroupName = admin.GroupName v.SellerName = admin.RealName v.GroupId = admin.GroupId } } // 分组信息, list的groupId可能是三级的ID, 要转为对应的二级 groupMap, e := crmService.GetSellerTeamGroupMap() if e != nil { resp.FailMsg("获取失败", "获取组别对应信息失败, Err: "+e.Error(), c) return } mulNum := decimal.NewFromFloat(100) for i := range list { g := groupMap[list[i].GroupId] if g != nil { list[i].GroupId = g.GroupId list[i].GroupName = g.GroupName } // 处理百分比, 乘100并保留两位小数 d := decimal.NewFromFloat(list[i].GroupRate) d = d.Mul(mulNum).Round(2) a, _ := d.Float64() list[i].GroupRate = a d2 := decimal.NewFromFloat(list[i].SellerRate) d2 = d2.Mul(mulNum).Round(2) a2, _ := d2.Float64() list[i].SellerRate = a2 } // 是否导出 if req.IsExport == 1 { ExportInvoiceList(c, list, req) return } page.SetTotal(int64(total)) baseData := new(base.BaseData) baseData.SetPage(page) baseData.SetList(list) resp.OkData("获取成功", baseData, c) } // ExportInvoiceList 导出销售开票统计列表 func ExportInvoiceList(c *gin.Context, list []*fms.CensusSellerInvoiceItem, req fms.CensusSellerInvoiceListReq) { // 生成Excel文件 xlsxFile := xlsx.NewFile() style := xlsx.NewStyle() alignment := xlsx.Alignment{ Horizontal: "center", Vertical: "center", WrapText: true, } style.Alignment = alignment style.ApplyAlignment = true sheetName := "销售开票统计" sheet, err := xlsxFile.AddSheet(sheetName) if err != nil { resp.FailData("新增Sheet失败", "Err:"+err.Error(), c) return } // 存在筛选则前两行显示时间信息 if req.StartDate != "" && req.EndDate != "" { timeData := fmt.Sprintf("时间：%s至%s", req.StartDate, req.EndDate) rowA := sheet.AddRow() cellAA := rowA.AddCell() cellAA.SetString("销售统计表") cellAA.SetStyle(style) rowB := sheet.AddRow() rowB.AddCell().SetString(timeData) // 第三行空出 sheet.AddRow() } // 数据表头 rowTitle := []string{"排名", "销售员", "销售组别", "收入金额（元）", "小组占比", "全员占比"} titleRow := sheet.AddRow() for i := range rowTitle { v := titleRow.AddCell() v.SetString(rowTitle[i]) v.SetStyle(style) } // 填充数据 for k, v := range list { dataRow := sheet.AddRow() dataRow.AddCell().SetString(fmt.Sprint(k + 1)) // 排名 dataRow.AddCell().SetString(v.SellerName) // 销售员 dataRow.AddCell().SetString(v.GroupName) // 销售组别 dataRow.AddCell().SetString(fmt.Sprint(v.InvoiceAmount)) // 开票金额 dataRow.AddCell().SetString(fmt.Sprint(v.GroupRate, "%")) // 小组占比 dataRow.AddCell().SetString(fmt.Sprint(v.SellerRate, "%")) // 全员占比 } // 输出文件 var buffer bytes.Buffer _ = xlsxFile.Write(&buffer) content := bytes.NewReader(buffer.Bytes()) randStr := time.Now().Format(utils.FormatDateTimeUnSpace) fileName := sheetName + randStr + ".xlsx" c.Writer.Header().Add("Content-Disposition", fmt.Sprintf(`attachment; filename="%s"`, fileName)) c.Writer.Header().Add("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") http.ServeContent(c.Writer, c.Request, fileName, time.Now(), content) } func (this *SellerController) GroupInvoiceListV2(c *gin.Context) { var req fms.IncomeListReq if e := c.BindQuery(&req); e != nil { err, ok := e.(validator.ValidationErrors) if !ok { resp.FailData("参数解析失败", "Err:"+e.Error(), c) return } resp.FailData("参数解析失败", err.Translate(global.Trans), c) return } //收入统计 var incomeList models.CensusIncomeChartResp ch := make(chan models.CensusIncomeChartResp, 1) go getGroupInvoiceList(ch, req) for v := range ch { incomeList = v close(ch) } // 是否导出 if req.IsExport == 1 { ExportIncomeList(c, incomeList.DataList) return } resp.OkData("获取成功", incomeList, c) } func getGroupInvoiceList(ch chan models.CensusIncomeChartResp, req fms.IncomeListReq) (incomeChart models.CensusIncomeChartResp, err error) { defer func() { if err != nil { global.LOG.Error(err) if err != utils.ErrNoRow { go alarm_msg.SendAlarmMsg("获取业务收入金额统计数据异常，Err："+err.Error(), 3) } } ch <- incomeChart }() //获取最新的开票到款日期 cond := `` historyCond := `` pars := make([]interface{}, 0) historyPars := make([]interface{}, 0) //if req.SellerIds != "" { // sellerIds := strings.Split(req.SellerIds, ",") // cond += ` AND (a.seller_id in ? ) ` // historyCond += ` AND (seller_id in ? ) ` // pars = append(pars, sellerIds) // historyPars = append(historyPars, sellerIds) //} if req.CompanyType == 1 { cond += ` AND b.contract_type = 1 ` historyCond += ` AND new_company = 1 ` } else if req.CompanyType == 2 { cond += ` AND b.contract_type IN (2,3,4) ` historyCond += ` AND new_company = 0 ` } var latestTime time.Time invoiceItem, err := fms.GetLatestIncome(cond, pars) if err != nil && err != utils.ErrNoRow { err = fmt.Errorf("获取最新的开票或到款日期, Err: %s", err.Error()) return } latestTime = invoiceItem.InvoiceDate if err == utils.ErrNoRow { historyItem, e := fms.GetLatestHistoryIncome(historyCond, historyPars) if e != nil && e != utils.ErrNoRow { err = fmt.Errorf("获取最新的历史开票或到款日期, Err: %s", e.Error()) return } latestTime = historyItem.InvoiceDate } if latestTime.IsZero() { latestTime = time.Now() } latestTime = latestTime.AddDate(0, 0, -latestTime.Day()+1) var dateSlice []string var totalMoneySlice, prevTotalMoneySlice []float64 var yoySlice []string var yearNum, monthNum int var reqStartDate, reqEndDate time.Time historyTime, _ := time.Parse(utils.FormatDate, "2023-04-01") if req.StartDate != "" && req.EndDate != "" { st := fmt.Sprint(req.StartDate, "-01 00:00:00") ed := fmt.Sprint(req.EndDate, "-01 23:59:59") reqStartDate, _ = time.Parse(utils.FormatDateTime, st) reqEndDate, _ = time.Parse(utils.FormatDateTime, ed) if reqEndDate.After(latestTime) { yearNum = latestTime.Year() - reqStartDate.Year() monthNum = int(latestTime.Month() - reqStartDate.Month()) } else { yearNum = reqEndDate.Year() - reqStartDate.Year() monthNum = int(reqEndDate.Month() - reqStartDate.Month()) } } else { yearNum = latestTime.Year() - 2020 monthNum = int(latestTime.Month() - 1) } if yearNum < 0 { yearNum = -yearNum } if monthNum < 0 { monthNum = -monthNum } numMonth := yearNum*12 + monthNum //共存在多少个月 // 累计值 var accumulate float64 var partAccumulate float64 var historyAccumulate float64 var partHistoryAccumulate float64 //dataList := make([]*fms.IncomeSummaryItem, 0) //historydataList := make([]*fms.IncomeSummaryItem, 0) fmt.Println("numMonth:", numMonth) fmt.Println("InvoiceDate:", latestTime) var j int for i := 0; i <= numMonth; i++ { //timeNow, _ := time.Parse("2006-01", time.Now().Format("2006-01")) var endDateTime time.Time var prevEndDateTime time.Time var prevStartDate, prevEndDate string var startDate, endDate string //开始日期 if req.StartDate != "" && req.EndDate != "" { startDate = reqStartDate.AddDate(0, i, 0).Format("2006-01") prevStartDate = reqStartDate.AddDate(-1, i, 0).Format("2006-01") } else { startDate = latestTime.AddDate(0, i-numMonth, 0).Format("2006-01") prevStartDate = latestTime.AddDate(-1, i-numMonth, 0).Format("2006-01") } startDate = fmt.Sprint(startDate, "-01") prevStartDate = fmt.Sprint(prevStartDate, "-01") startDateTime, _ := time.Parse(utils.FormatDate, startDate) prevStartDateTime, _ := time.Parse(utils.FormatDate, prevStartDate) //结束日期 if req.StartDate != "" && req.EndDate != "" { endDateTime = reqStartDate.AddDate(0, i+1, -1) prevEndDateTime = reqStartDate.AddDate(-1, i+1, -1) if reqEndDate.After(latestTime) { endDateTime = latestTime.AddDate(0, i-numMonth+1, -1) prevEndDateTime = latestTime.AddDate(-1, i-numMonth+1, -1) } } else { endDateTime = latestTime.AddDate(0, i-numMonth+1, -1) prevEndDateTime = latestTime.AddDate(-1, i-numMonth+1, -1) } endDate = endDateTime.Format(utils.FormatDate) prevEndDate = prevEndDateTime.Format(utils.FormatDate) cond := `1 = 1` histrtyCond := `1 = 1` pars := make([]interface{}, 0) historyPars := make([]interface{}, 0) prevCond := `1 = 1` prevHistoryCond := `1 = 1` prevPars := make([]interface{}, 0) prevHistoryPars := make([]interface{}, 0) if req.CompanyType == 1 { cond += ` AND b.contract_type = 1 ` prevCond += ` AND b.contract_type = 1 ` histrtyCond += ` AND new_company = 1 ` prevHistoryCond += ` AND new_company = 1 ` } else if req.CompanyType == 2 { cond += ` AND b.contract_type IN (2,3,4) ` prevCond += ` AND b.contract_type IN (2,3,4) ` histrtyCond += ` AND new_company = 0 ` prevHistoryCond += ` AND new_company = 0 ` } //if req.SellerIds != "" { // sellerIds := strings.Split(req.SellerIds, ",") // cond += ` AND (c.seller_id in ? OR d.seller_id in ?)` // pars = append(pars, sellerIds, sellerIds) // prevCond += ` AND (c.seller_id in ? OR d.seller_id in ?)` // prevPars = append(prevPars, sellerIds, sellerIds) // histrtyCond += ` AND seller_id in ? ` // prevHistoryCond += ` AND seller_id in ? ` // historyPars = append(historyPars, sellerIds) // prevHistoryPars = append(prevHistoryPars, sellerIds) //} { //本期 st := fmt.Sprint(startDate, " 00:00:00") ed := fmt.Sprint(endDate, " 23:59:59") //校验日期，分段查询 if startDateTime.After(historyTime) || startDateTime.Equal(historyTime) { //全部走新查询 //fmt.Println("新查询") cond += ` AND ((c.invoice_time BETWEEN ? AND ?) or (d.invoice_time BETWEEN ? AND ?))` pars = append(pars, st, ed, st, ed) summaryIds, e := fms.GetInvoicePaymentCensusSummaryDataIds(cond, pars) if e != nil { return } // 开票到款金额合计(换算后) var amountTotal float64 if len(summaryIds) > 0 { amountCond := `a.id IN ? ` amountPars := make([]interface{}, 0) amountPars = append(amountPars, summaryIds) if req.SellerIds != "" { sellerIds := strings.Split(req.SellerIds, ",") amountCond += ` AND (( b.seller_id IN ? AND a.invoice_id <> 0 AND b.invoice_time BETWEEN ? AND ?)` amountCond += `OR ( d.seller_id IN ? AND a.payment_id <> 0 AND a.invoice_id = 0 AND d.invoice_time BETWEEN ? AND ?)) ` amountPars = append(amountPars, sellerIds, st, ed, sellerIds, st, ed) } else { amountCond += ` AND ((a.invoice_id <> 0 AND b.invoice_time BETWEEN ? AND ?)` amountCond += `OR (a.payment_id <> 0 AND a.invoice_id = 0 AND d.invoice_time BETWEEN ? AND ?))` amountPars = append(amountPars, st, ed, st, ed) } results, e := fms.GetContractSummaryIncomeAmount(amountCond, amountPars) if e != nil { err = fmt.Errorf("获取汇总数据失败, Err: %s", e.Error()) return } //dataList = append(dataList, results...) var amountSum float64 for _, result := range results { incomeChart.DataList = append(incomeChart.DataList, result) amountSum += result.Amount fmt.Println("result.Amount:", result.Amount) } amountTotal, _ = strconv.ParseFloat(fmt.Sprintf("%.2f", amountSum), 64) accumulate += amountTotal partAccumulate += amountTotal } if i == j || i == numMonth { if req.ListParam == "4" { totalMoneySlice = append(totalMoneySlice, accumulate) if startDateTime.Month() == 12 { accumulate = 0 } } else if req.ListParam == "0" { totalMoneySlice = append(totalMoneySlice, amountTotal) } else if i > 0 || i == numMonth { totalMoneySlice = append(totalMoneySlice, partAccumulate) fmt.Println("partAccumulate:", partAccumulate) partAccumulate = 0.0 } } } else if endDateTime.Before(historyTime) || endDateTime.Equal(historyTime) { //全部走旧查询 //fmt.Println("旧查询") //fmt.Println("st:",st) //fmt.Println("ed:",ed) histrtyCond += ` AND (invoice_time BETWEEN ? AND ? )` historyPars = append(historyPars, st, ed) //fmt.Println("st:",st) //fmt.Println("ed:",ed) // 开票到款金额合计(换算后) var amountTotal float64 results, e := fms.GetIncomeHistory(histrtyCond, historyPars) if e != nil { err = fmt.Errorf("获取汇总数据失败, Err: %s", e.Error()) return } var amountSum float64 //dataList = append(dataList, results...) for _, result := range results { incomeChart.DataList = append(incomeChart.DataList, result) amountSum += result.Amount } amountTotal, _ = strconv.ParseFloat(fmt.Sprintf("%.2f", amountSum), 64) accumulate += amountTotal partAccumulate += amountTotal if i == j || i == numMonth { if req.ListParam == "4" { totalMoneySlice = append(totalMoneySlice, accumulate) if startDateTime.Month() == 12 { accumulate = 0 } } else if req.ListParam == "0" { totalMoneySlice = append(totalMoneySlice, amountTotal) } else if i > 0 || i == numMonth { totalMoneySlice = append(totalMoneySlice, partAccumulate) partAccumulate = 0.0 } } //fmt.Println("partAccumulate:",partAccumulate) } } { // 去年同期，用于计算同比值 prevSt := fmt.Sprint(prevStartDate, " 00:00:00") // 格式化上一年同一天的开始时间 prevEd := fmt.Sprint(prevEndDate, " 23:59:59") // 格式化上一年同一天的结束时间 // 校验日期，分段查询 if prevStartDateTime.After(historyTime) || prevStartDateTime.Equal(historyTime) { // 全部走新查询 prevCond += ` AND ((b.invoice_time BETWEEN ? AND ?) or (d.invoice_time BETWEEN ? AND ?))` // 查询条件加入新的时间范围 prevPars = append(prevPars, prevSt, prevEd, prevSt, prevEd) // 添加时间参数到参数列表 prevSummaryIds, e := fms.GetInvoicePaymentCensusSummaryDataIds(prevCond, prevPars) // 获取摘要ID列表 if e != nil { return } // 开票到款金额合计(换算后) var prevAmountTotal float64 if len(prevSummaryIds) > 0 { amountCond := `a.id IN ? ` // 查询条件为ID在给定的摘要ID列表中 amountPars := make([]interface{}, 0) amountPars = append(amountPars, prevSummaryIds) // 将摘要ID列表添加到参数列表 if req.SellerIds != "" { sellerIds := strings.Split(req.SellerIds, ",") amountCond += ` AND (( b.seller_id IN ? AND a.invoice_id <> 0 AND b.invoice_time BETWEEN ? AND ?)` amountCond += `OR ( d.seller_id IN ? AND a.payment_id <> 0 AND a.invoice_id = 0 AND d.invoice_time BETWEEN ? AND ?)) ` // 根据卖家ID和发票/支付状态筛选时间范围 amountPars = append(amountPars, sellerIds, prevSt, prevEd, sellerIds, prevSt, prevEd) // 将卖家ID列表添加到参数列表 } else { amountCond += ` AND ((a.invoice_id <> 0 AND b.invoice_time BETWEEN ? AND ?)` amountCond += `OR (a.payment_id <> 0 AND a.invoice_id = 0 AND d.invoice_time BETWEEN ? AND ?))` // 根据发票/支付状态筛选时间范围 amountPars = append(amountPars, prevSt, prevEd) } results, e := fms.GetContractSummaryIncomeAmount(amountCond, amountPars) // 获取合同摘要收入金额数据 if e != nil { err = fmt.Errorf("获取汇总数据失败, Err: %s", e.Error()) return } var amountSum float64 //historydataList = append(historydataList, results...) for _, result := range results { amountSum += result.Amount // 累计收入金额 } prevAmountTotal, _ = strconv.ParseFloat(fmt.Sprintf("%.2f", amountSum), 64) // 将累计金额转换为浮点数 historyAccumulate += prevAmountTotal // 累计历史金额 partHistoryAccumulate += prevAmountTotal // 累计部分历史金额 } if i == j || i == numMonth { if req.ListParam == "4" { prevTotalMoneySlice = append(prevTotalMoneySlice, historyAccumulate) // 将累计金额添加到总金额切片中 if prevStartDateTime.Month() == 12 { historyAccumulate = 0 // 如果是去年的12月份，则将历史金额清零 } } else if req.ListParam == "0" { prevTotalMoneySlice = append(prevTotalMoneySlice, prevAmountTotal) // 将部分历史金额添加到总金额切片中 } else if i > 0 || i == numMonth { prevTotalMoneySlice = append(prevTotalMoneySlice, partHistoryAccumulate) // 将部分历史金额添加到总金额切片中 fmt.Println("partHistoryAccumulate:", partHistoryAccumulate) partHistoryAccumulate = 0.0 // 清零部分历史金额 } } } else if prevEndDateTime.Before(historyTime) || prevEndDateTime.Equal(historyTime) { // 全部走旧查询 fmt.Println("prevSt:", prevSt) fmt.Println("prevEd:", prevEd) prevHistoryCond += ` AND (invoice_time BETWEEN ? AND ?)` // 查询条件加入旧的时间范围 prevHistoryPars = append(prevHistoryPars, prevSt, prevEd) // 添加时间参数到参数列表 // 开票到款金额合计(换算后) var amountTotal float64 results, e := fms.GetIncomeHistory(prevHistoryCond, prevHistoryPars) // 获取历史收入数据 if e != nil { err = fmt.Errorf("获取汇总数据失败, Err: %s", e.Error()) return } //historydataList = append(historydataList, results...) var amountSum float64 for _, result := range results { amountSum += result.Amount // 累计收入金额 } amountTotal, _ = strconv.ParseFloat(fmt.Sprintf("%.2f", amountSum), 64) // 将累计金额转换为浮点数 historyAccumulate += amountTotal // 累计历史金额 partHistoryAccumulate += amountTotal // 累计部分历史金额 if i == j || i == numMonth { if req.ListParam == "4" { prevTotalMoneySlice = append(prevTotalMoneySlice, historyAccumulate) // 将累计金额添加到总金额切片中 if prevStartDateTime.Month() == 12 { historyAccumulate = 0 // 如果是去年的12月份，则将历史金额清零 } } else if req.ListParam == "0" { prevTotalMoneySlice = append(prevTotalMoneySlice, amountTotal) // 将历史金额添加到总金额切片中 } else if i > 0 || i == numMonth { prevTotalMoneySlice = append(prevTotalMoneySlice, partHistoryAccumulate) // 将部分历史金额添加到总金额切片中 fmt.Println("partHistoryAccumulate:", partHistoryAccumulate) partHistoryAccumulate = 0.0 // 清零部分历史金额 } } if req.ListParam == "1" && i == j { if i == 0 { dateSlice = append(dateSlice, startDateTime.AddDate(0, 2, 0).Format("06/01")) // 添加下一个月份的开始日期到日期切片中 j = j + 2 } else { dateSlice = append(dateSlice, startDateTime.AddDate(0, 3, 0).Format("06/01")) // 添加下一季度的开始日期到日期切片中 j = j + 3 } } else if req.ListParam == "2" && i == j { if i == 0 { dateSlice = append(dateSlice, startDateTime.AddDate(0, 5, 0).Format("06/01")) // 添加下五个月份的开始日期到日期切片中 j = j + 5 } else { dateSlice = append(dateSlice, startDateTime.AddDate(0, 6, 0).Format("06/01")) // 添加下半年的开始日期到日期切片中 j = j + 6 } } else if req.ListParam == "3" && i == j { if i == 0 { dateSlice = append(dateSlice, startDateTime.AddDate(0, 11, 0).Format("06/01")) // 添加下十一个月份的开始日期到日期切片中 j = j + 11 } else { dateSlice = append(dateSlice, startDateTime.AddDate(0, 12, 0).Format("06/01")) // 添加下一季度的开始日期到日期切片中 j = j + 12 } } else if i == j { dateSlice = append(dateSlice, startDateTime.Format("06/01")) // 添加当月开始日期到日期切片中 j++ } } if req.ListParam == "1" && i == j { if i == 0 { dateSlice = append(dateSlice, startDateTime.AddDate(0, 2, 0).Format("06/01")) // 添加下一个月份的开始日期到日期切片中 j = j + 2 } else { dateSlice = append(dateSlice, startDateTime.AddDate(0, 3, 0).Format("06/01")) // 添加下一季度的开始日期到日期切片中 j = j + 3 } } else if req.ListParam == "2" && i == j { if i == 0 { dateSlice = append(dateSlice, startDateTime.AddDate(0, 5, 0).Format("06/01")) // 添加下五个月份的开始日期到日期切片中 j = j + 5 } else { dateSlice = append(dateSlice, startDateTime.AddDate(0, 6, 0).Format("06/01")) // 添加下半年的开始日期到日期切片中 j = j + 6 } } else if req.ListParam == "3" && i == j { if i == 0 { dateSlice = append(dateSlice, startDateTime.AddDate(0, 11, 0).Format("06/01")) j = j + 11 } else { dateSlice = append(dateSlice, startDateTime.AddDate(0, 12, 0).Format("06/01")) j = j + 12 } } else if i == j { dateSlice = append(dateSlice, startDateTime.Format("06/01")) j++ } } } fmt.Println("prevTotalMoneySlice:", len(prevTotalMoneySlice)) fmt.Println("totalMoneySlice:", len(totalMoneySlice)) if req.CompanyType != 3 { //计算同比值 for i := range prevTotalMoneySlice { var yoy float64 var yoyStr string totalMoneySlice[i], _ = strconv.ParseFloat(fmt.Sprintf("%.2f", totalMoneySlice[i]), 64) if prevTotalMoneySlice[i] != 0 && totalMoneySlice[i] != 0 { yoy = (totalMoneySlice[i] - prevTotalMoneySlice[i]) / prevTotalMoneySlice[i] yoyStr = fmt.Sprintf("%.4f", yoy) if i == len(prevTotalMoneySlice)-1 && i > 0 && req.ListParam == "3" { fmt.Println("totalMoneySlice[i-1]:", totalMoneySlice[i-1]) yoy = (totalMoneySlice[i] - totalMoneySlice[i-1]) / totalMoneySlice[i-1] yoyStr = fmt.Sprintf("%.4f", yoy) } } yoySlice = append(yoySlice, yoyStr) } } else { // 当筛选条件是”月度“时，并且客户是”未续约“客户，展示柱形图和曲线图 // 当筛选条件是”季度“、”半年度“、”年度“、”月度统计“时，并且客户是”未续约“时，仅展示柱形图，无曲线图 // 未续约收入金额=去年同期总收入金额（新客户+老客户）-当期老客户收入金额 // 移动平均值：若是10月份，计算8月，9月，10月，这三个月的收入金额平均值 for i := range prevTotalMoneySlice { totalMoneySlice[i], _ = strconv.ParseFloat(fmt.Sprintf("%.2f", prevTotalMoneySlice[i]-totalMoneySlice[i]), 64) var yoy float64 var yoyStr string if i > 1 && req.ListParam == "0" { // 前俩月没有 yoy = (totalMoneySlice[i] + totalMoneySlice[i-1] + totalMoneySlice[i-2]) / 3 yoyStr = fmt.Sprintf("%.4f", yoy) yoySlice = append(yoySlice, yoyStr) } else { yoySlice = append(yoySlice, "") } } } incomeChart.Title = "开票到款统计图" incomeChart.Date = dateSlice incomeChart.TotalMoney = totalMoneySlice incomeChart.PrevTotalMoney = prevTotalMoneySlice incomeChart.Yoy = yoySlice return }