package census import ( "bytes" "fmt" "github.com/gin-gonic/gin" "github.com/go-playground/validator/v10" "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" fmsService "hongze/fms_api/services/fms" "hongze/fms_api/utils" "net/http" "sort" "strconv" "strings" "sync" "time" ) // InvoicePaymentController 商品到款统计 type InvoicePaymentController struct{} // List2 原版(已废弃) //func (ct *InvoicePaymentController) List2(c *gin.Context) { // var req fms.InvoicePaymentCensusListReq // 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 // } // // cond := `1 = 1` // pars := make([]interface{}, 0) // // 合同编号/客户姓名/销售 // if req.Keyword != "" { // kw := "%" + req.Keyword + "%" // cond += ` AND b.company_name LIKE ? OR a.seller_name LIKE ?` // pars = append(pars, kw, kw) // } // if req.SellGroupId > 0 { // cond += ` AND a.seller_group_id = ?` // pars = append(pars, req.SellGroupId) // } // // 套餐筛选 // if req.ServiceType != 0 { // registerIds, e := fms.GetContractRegisterIdsByTempId(req.ServiceType) // if e != nil { // resp.FailMsg("获取失败", "获取合同登记IDs失败, Err: "+e.Error(), c) // return // } // if len(registerIds) > 0 { // cond += ` AND b.contract_register_id IN ?` // pars = append(pars, registerIds) // } else { // cond += ` AND 1 = 2` // } // } // // 开票到款日期 // if req.TimeType > 0 && req.StartDate != "" && req.EndDate != "" { // st := fmt.Sprint(req.StartDate, " 00:00:00") // ed := fmt.Sprint(req.EndDate, " 23:59:59") // cond += ` AND a.invoice_type = ? AND (a.invoice_time BETWEEN ? AND ?)` // pars = append(pars, req.TimeType, st, ed) // } // // 已开票 // if req.HasInvoice == 1 && req.HasPayment == 0 { // cond += ` AND a.invoice_type = 1` // } // // 已到款 // if req.HasInvoice == 0 && req.HasPayment == 1 { // cond += ` AND a.invoice_type = 2` // } // // page := new(base.Page) // page.SetPageSize(req.PageSize) // page.SetCurrent(req.Current) // page.AddOrderItem(base.OrderItem{Column: "a.create_time", Asc: false}) // page.AddOrderItem(base.OrderItem{Column: "b.contract_register_id", Asc: true}) // if req.IsExport == 1 { // page.SetPageSize(10000) // page.SetCurrent(1) // } // // list, allRegisterIds, e := fms.GetInvoicePaymentCensusPageList(page, cond, pars) // if e != nil { // resp.FailMsg("获取失败", "获取商品到款统计列表失败, Err: "+e.Error(), c) // return // } // total := int64(len(allRegisterIds)) // registerIds := make([]int, 0) // for i := range list { // registerIds = append(registerIds, list[i].ContractRegisterId) // } // // results := new(fms.InvoicePaymentCensusResp) // if len(registerIds) > 0 { // // 获取开票到款列表 // ivCond := `contract_register_id IN ?` // ivPars := make([]interface{}, 0) // ivPars = append(ivPars, registerIds) // iv := new(fms.ContractInvoice) // invoiceList, e := iv.List(ivCond, ivPars, "") // if e != nil { // resp.FailMsg("获取失败", "获取开票到款列表失败, Err: "+e.Error(), c) // return // } // // 取出开票、到款 // invoiceMap := make(map[int][]*fms.ContractInvoice, 0) // paymentMap := make(map[int][]*fms.ContractInvoice, 0) // paymentIds := make([]int, 0) // for i := range invoiceList { // if invoiceMap[invoiceList[i].ContractRegisterId] == nil { // invoiceMap[invoiceList[i].ContractRegisterId] = make([]*fms.ContractInvoice, 0) // } // if paymentMap[invoiceList[i].ContractRegisterId] == nil { // paymentMap[invoiceList[i].ContractRegisterId] = make([]*fms.ContractInvoice, 0) // } // if invoiceList[i].InvoiceType == fms.ContractInvoiceTypeMake { // invoiceMap[invoiceList[i].ContractRegisterId] = append(invoiceMap[invoiceList[i].ContractRegisterId], invoiceList[i]) // } // if invoiceList[i].InvoiceType == fms.ContractInvoiceTypePay { // paymentMap[invoiceList[i].ContractRegisterId] = append(paymentMap[invoiceList[i].ContractRegisterId], invoiceList[i]) // paymentIds = append(paymentIds, invoiceList[i].ContractInvoiceId) // } // } // // // 合同套餐 // contractServiceCond := `contract_register_id IN ?` // contractServicePars := make([]interface{}, 0) // contractServicePars = append(contractServicePars, registerIds) // contractServiceOB := new(fms.ContractService) // contractServiceList, e := contractServiceOB.List(contractServiceCond, contractServicePars) // if e != nil { // resp.FailMsg("获取失败", "获取合同套餐列表失败, Err:"+e.Error(), c) // return // } // contractServiceMap := make(map[int][]*fms.ContractService, 0) // servicesNameMap := make(map[int][]string, 0) // for i := range contractServiceList { // if contractServiceMap[contractServiceList[i].ContractRegisterId] == nil { // contractServiceMap[contractServiceList[i].ContractRegisterId] = make([]*fms.ContractService, 0) // } // contractServiceMap[contractServiceList[i].ContractRegisterId] = append(contractServiceMap[contractServiceList[i].ContractRegisterId], contractServiceList[i]) // servicesNameMap[contractServiceList[i].ContractRegisterId] = append(servicesNameMap[contractServiceList[i].ContractRegisterId], contractServiceList[i].Title) // } // // // 到款套餐分配 // serviceAmountMap := make(map[int][]*fms.ContractPaymentServiceAmount, 0) // if len(paymentIds) > 0 { // serviceAmountCond := `contract_payment_id IN ?` // serviceAmountPars := make([]interface{}, 0) // serviceAmountPars = append(serviceAmountPars, paymentIds) // serviceAmountOB := new(fms.ContractPaymentServiceAmount) // serviceAmountList, e := serviceAmountOB.List(serviceAmountCond, serviceAmountPars) // if e != nil { // resp.FailMsg("获取失败", "获取到款套餐分配列表失败, Err:"+e.Error(), c) // return // } // for i := range serviceAmountList { // if serviceAmountMap[serviceAmountList[i].ContractRegisterId] == nil { // serviceAmountMap[serviceAmountList[i].ContractRegisterId] = make([]*fms.ContractPaymentServiceAmount, 0) // } // serviceAmountMap[serviceAmountList[i].ContractRegisterId] = append(serviceAmountMap[serviceAmountList[i].ContractRegisterId], serviceAmountList[i]) // } // } // // // 整合响应数据 // respList := make([]*fms.InvoicePaymentCensusItem, 0) // for i := range list { // v := new(fms.InvoicePaymentCensusItem) // v.ContractRegisterId = list[i].ContractRegisterId // v.CompanyName = list[i].CompanyName // v.NewCompany = list[i].NewCompany // v.StartDate = list[i].StartDate.Format(utils.FormatDate) // v.EndDate = list[i].EndDate.Format(utils.FormatDate) // svList := servicesNameMap[list[i].ContractRegisterId] // v.ServicesName = strings.Join(svList, ",") // // 格式化(合并)开票到款数据 // v.InvoicePaymentList = fmsService.MergeInvoiceList2InvoicePaymentCensusInfo(invoiceMap[list[i].ContractRegisterId], paymentMap[list[i].ContractRegisterId], // contractServiceMap[list[i].ContractRegisterId], serviceAmountMap[list[i].ContractRegisterId]) // respList = append(respList, v) // } // // // 开票到款金额合计(换算后) // amountTotalCond := `contract_register_id IN ?` // amountTotalPars := make([]interface{}, 0) // amountTotalPars = append(amountTotalPars, allRegisterIds) // amountTotalList, e := fms.GetContractInvoiceAmountTotal(amountTotalCond, amountTotalPars) // if e != nil { // resp.FailMsg("获取失败", "获取开票到款金额合计失败, Err:"+e.Error(), c) // return // } // amountTotalMap := make(map[int]float64) // for i := range amountTotalList { // amountTotalMap[amountTotalList[i].InvoiceType] = amountTotalList[i].TotalAmount // } // invoiceTotal, _ := strconv.ParseFloat(fmt.Sprintf("%.2f", amountTotalMap[fms.ContractInvoiceTypeMake]), 64) // paymentTotal, _ := strconv.ParseFloat(fmt.Sprintf("%.2f", amountTotalMap[fms.ContractInvoiceTypePay]), 64) // // // 分币种金额统计 // currencyOB := new(fms.CurrencyUnit) // currencyCond := `enable = 1` // currencyPars := make([]interface{}, 0) // currencyList, e := currencyOB.List(currencyCond, currencyPars) // if e != nil { // resp.FailMsg("获取失败", "获取货币列表失败, Err: "+e.Error(), c) // return // } // unitMap := make(map[string]string) // invoiceCurrencyTotals := make([]*fms.InvoiceListCurrencyTotal, 0) // paymentCurrencyTotals := make([]*fms.InvoiceListCurrencyTotal, 0) // for i := range currencyList { // unitMap[currencyList[i].Code] = currencyList[i].UnitName // invoiceCurrencyTotals = append(invoiceCurrencyTotals, &fms.InvoiceListCurrencyTotal{ // Name: currencyList[i].Name, // UnitName: currencyList[i].UnitName, // Code: currencyList[i].Code, // FlagImg: currencyList[i].FlagImg, // }) // paymentCurrencyTotals = append(paymentCurrencyTotals, &fms.InvoiceListCurrencyTotal{ // Name: currencyList[i].Name, // UnitName: currencyList[i].UnitName, // Code: currencyList[i].Code, // FlagImg: currencyList[i].FlagImg, // }) // } // sumList, e := fms.GetInvoiceListCurrencySum(amountTotalCond, amountTotalPars, "currency_unit, invoice_type") // if e != nil { // resp.FailMsg("获取失败", "获取商品到款统计货币合计金额失败, Err: "+e.Error(), c) // return // } // invoiceSumMap := make(map[string]float64) // paymentSumMap := make(map[string]float64) // for i := range sumList { // if sumList[i].InvoiceType == fms.ContractInvoiceTypeMake { // invoiceSumMap[sumList[i].CurrencyUnit] = sumList[i].OriginAmountTotal // continue // } // if sumList[i].InvoiceType == fms.ContractInvoiceTypePay { // paymentSumMap[sumList[i].CurrencyUnit] = sumList[i].OriginAmountTotal // } // } // for i := range invoiceCurrencyTotals { // a, _ := strconv.ParseFloat(fmt.Sprintf("%.2f", invoiceSumMap[invoiceCurrencyTotals[i].Code]), 64) // invoiceCurrencyTotals[i].Amount = a // } // for i := range paymentCurrencyTotals { // a, _ := strconv.ParseFloat(fmt.Sprintf("%.2f", paymentSumMap[paymentCurrencyTotals[i].Code]), 64) // paymentCurrencyTotals[i].Amount = a // } // // results.DataList = respList // results.InvoiceTotal = invoiceTotal // results.PaymentTotal = paymentTotal // results.InvoiceCurrencyTotal = invoiceCurrencyTotals // results.PaymentCurrencyTotal = paymentCurrencyTotals // } // // // 是否导出 // if req.IsExport == 1 { // ExportInvoicePaymentCensusList(c, results) // return // } // page.SetTotal(total) // baseData := new(base.BaseData) // baseData.SetPage(page) // baseData.SetList(results) // resp.OkData("获取成功", baseData, c) //} // ExportInvoicePaymentCensusList 导出商品到款统计列表 func ExportInvoicePaymentCensusList(c *gin.Context, results *fms.InvoicePaymentCensusResp) { list := results.DataList if len(list) == 0 { resp.Fail("列表数据为空", c) return } // 生成Excel文件 xlsxFile := xlsx.NewFile() style := xlsx.NewStyle() alignment := xlsx.Alignment{ Horizontal: "center", Vertical: "center", WrapText: true, } style.Alignment = alignment style.ApplyAlignment = true sheet, err := xlsxFile.AddSheet("商品到款统计") if err != nil { resp.FailData("新增Sheet失败", "Err:"+err.Error(), c) return } _ = sheet.SetColWidth(1, 1, 30) _ = sheet.SetColWidth(3, 3, 30) // 前三行-开票金额合计 rowA := sheet.AddRow() cellAA := rowA.AddCell() cellAA.SetString(fmt.Sprintf("已开票合计金额（换算后）：%.2f（元）", results.InvoiceTotal)) rowBData := "已开票金额：" for _, v := range results.InvoiceCurrencyTotal { rowBData += fmt.Sprintf("%s%.2f(%s) ", v.Name, v.Amount, v.UnitName) } rowB := sheet.AddRow() rowB.AddCell().SetString(rowBData) sheet.AddRow() // 四至六-到款金额合计 rowD := sheet.AddRow() cellDA := rowD.AddCell() cellDA.SetString(fmt.Sprintf("已到款合计金额（换算后）：%.2f（元）", results.PaymentTotal)) rowEData := "已到款金额：" for _, v := range results.PaymentCurrencyTotal { rowEData += fmt.Sprintf("%s%.2f(%s) ", v.Name, v.Amount, v.UnitName) } rowE := sheet.AddRow() rowE.AddCell().SetString(rowEData) sheet.AddRow() // 表头, 套餐动态获取 rowTitle := []string{"序号", "客户名称", "是否新客户", "合同有效期", "开票日", "开票金额", "到款日", "到款金额", "付款方式", "销售", "销售组别", "销售类型"} serviceTempCond := `` serviceTempPars := make([]interface{}, 0) serviceTempOB := new(fms.ContractServiceTemplate) serviceTempList, e := serviceTempOB.List(serviceTempCond, serviceTempPars) if e != nil { resp.FailData("获取套餐模板列表失败", "Err:"+e.Error(), c) return } serviceTempListMap := make(map[int]*fms.ContractServiceTemplate) var serviceTempShow []*fms.ContractServiceTemplate for i := range serviceTempList { serviceTempListMap[serviceTempList[i].ServiceTemplateId] = serviceTempList[i] } for i := range serviceTempList { if serviceTempList[i].ProductId == 1 { rowTitle = append(rowTitle, serviceTempList[i].Title) serviceTempShow = append(serviceTempShow, serviceTempList[i]) } else if serviceTempList[i].ProductId == 2 { if serviceTempList[i].Title == "行业套餐" || serviceTempList[i].Title == "权益大套餐" || (serviceTempList[i].Title == "策略" && serviceTempListMap[serviceTempList[i].Pid].Pid == 0) { } else if serviceTempList[i].Title == "医药" || serviceTempList[i].Title == "消费" || serviceTempList[i].Title == "科技" || serviceTempList[i].Title == "智造" { } else { serviceTempShow = append(serviceTempShow, serviceTempList[i]) } if serviceTempList[i].Title == "行业套餐" || serviceTempList[i].Title == "45万" || serviceTempList[i].Title == "70万" { continue } if serviceTempList[i].Pid > 0 && serviceTempListMap[serviceTempList[i].Pid].Pid > 0 { continue } rowTitle = append(rowTitle, serviceTempList[i].Title) } } hi := 0 insertHi := 0 titleRow := sheet.AddRow() for i := range rowTitle { v := titleRow.AddCell() v.SetString(rowTitle[i]) v.SetStyle(style) if rowTitle[i] != "权益大套餐" && rowTitle[i] != "医药" && rowTitle[i] != "消费" && rowTitle[i] != "科技" && rowTitle[i] != "智造" { v.VMerge = 1 hi++ } else { if insertHi == 0 { insertHi = hi } v.HMerge = 1 titleRow.AddCell().SetString("") } } // 新增一行放主观和客观 specialRow := sheet.AddRow() for i := 0; i < hi; i++ { if i == insertHi { v1 := specialRow.AddCell() v1.SetString("45万") v1.SetStyle(style) v1 = specialRow.AddCell() v1.SetString("70万") v1.SetStyle(style) for j := 0; j < 8; j++ { if j%2 == 0 { v1 = specialRow.AddCell() v1.SetString("主观") v1.SetStyle(style) } else { v1 = specialRow.AddCell() v1.SetString("客观") v1.SetStyle(style) } } } else { v := specialRow.AddCell() v.SetString("") } } newCompanyMap := map[int]string{0: "/", 1: "是", 2: "否"} for k, v := range list { dataRow := sheet.AddRow() // 前四个单元格根据每行开票到款条数向下合并 l := len(v.InvoicePaymentList) mergeRowNum := l - 1 // 序号 sortNum := k + 1 colA := dataRow.AddCell() colA.VMerge = mergeRowNum colA.SetString(fmt.Sprint(sortNum)) // 客户名称 colB := dataRow.AddCell() colB.VMerge = mergeRowNum colB.SetString(v.CompanyName) // 是否新客户 colC := dataRow.AddCell() colC.VMerge = mergeRowNum colC.SetString(newCompanyMap[v.ContractType]) // 合同有效期 colD := dataRow.AddCell() colD.VMerge = mergeRowNum colD.SetString(fmt.Sprint(v.StartDate, "至", v.EndDate)) // 开票到款信息 for k2, v2 := range v.InvoicePaymentList { rowData := []string{ v2.InvoiceDate, // 开票日 fmt.Sprint(v2.InvoiceAmount), // 开票金额 v2.PaymentDate, // 到款日 fmt.Sprint(v2.PaymentAmount), // 到款金额 fms.ContractPaymentPayTypeNameMap[v2.PayType], // 付款方式 v2.SellerName, // 销售 v2.SellerGroupName, // 组别 v2.SellerType, // 销售类型 } // 套餐金额信息 serviceTempShowAmount := make(map[int]string) for i := range serviceTempShow { for s2 := range v2.ServiceAmountList { item := v2.ServiceAmountList[s2] if item.ServiceTemplateId == serviceTempShow[i].ServiceTemplateId { serviceTempShowAmount[serviceTempShow[i].ServiceTemplateId] = fmt.Sprint(item.Amount) break } else if serviceTempShow[i].Pid == item.ServiceTemplateId { serviceTempShowAmount[serviceTempShow[i].ServiceTemplateId] = fmt.Sprint(item.Amount / 2) break } } } for i := range serviceTempShow { sa := "" if am, ok := serviceTempShowAmount[serviceTempShow[i].ServiceTemplateId]; ok { sa = am } rowData = append(rowData, sa) } // 首行开票到款 if k2 == 0 { for i := range rowData { dataRow.AddCell().SetString(rowData[i]) } continue } // 其他行开票到款, 加四列空的单元格用于合并 dataRowExtra := sheet.AddRow() for i := 0; i < 4; i++ { dataRowExtra.AddCell() } for i := range rowData { dataRowExtra.AddCell().SetString(rowData[i]) } } } // 输出文件 var buffer bytes.Buffer _ = xlsxFile.Write(&buffer) content := bytes.NewReader(buffer.Bytes()) randStr := time.Now().Format(utils.FormatDateTimeUnSpace) fileName := "商品到款统计_" + 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) } // List // @Title 商品到款统计列表 // @Description 商品到款统计列表 // @Param Keyword query string false "关键词" // @Param SellGroupId query int false "销售组别ID" // @Param ServiceType query int false "套餐类型" // @Param StartDate query string false "合同开始日期" // @Param EndDate query string false "合同结束日期" // @Param TimeType query int false "时间类型: 1-开票时间; 2-到款时间" // @Param HasInvoice query string false "是否已开票: 0-否; 1-是; 空-全部" // @Param HasPayment query string false "是否已到款: 0-否; 1-是; 空-全部" // @Param IsExport query int false "是否导出: 0-否; 1-是" // @Param ListParam query int false "套餐类型: 0-全部; 1-ficc; 2-权益" // @Param SortParam query string false "排序字段参数，用来排序的字段， 枚举值：'invoice_time':开票日 、 'payment_date':到款日" // @Param SortType query string true "如何排序，是正序还是倒序，枚举值：`asc 正序`,`desc 倒叙`" // @Success 200 {object} fms.ContractRegisterItem // @router /census/invoice_payment/list [get] func (ct *InvoicePaymentController) List(c *gin.Context) { var req fms.InvoicePaymentCensusListReq 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 } cond := `1 = 1` pars := make([]interface{}, 0) // 客户姓名/销售 if req.Keyword != "" { kw := "%" + req.Keyword + "%" cond += ` AND b.company_name LIKE ?` pars = append(pars, kw) } if req.SellerIds != "" { sellerIds := strings.Split(req.SellerIds, ",") cond += ` AND (c.seller_id in ? OR d.seller_id in ?)` pars = append(pars, sellerIds, sellerIds) } // 套餐筛选 if req.ServiceTypes != "" { serviceTypes := strings.Split(req.ServiceTypes, ",") tempRegisterIds, e := fms.GetContractRegisterIdsByTempId(serviceTypes) if e != nil { resp.FailMsg("获取失败", "获取合同登记IDs失败, Err: "+e.Error(), c) return } if len(tempRegisterIds) > 0 { cond += ` AND a.register_id IN ?` pars = append(pars, tempRegisterIds) } else { cond += ` AND 1 = 2` } } if req.ListParam == 1 { cond += ` AND a.service_product_id = 1 ` } else if req.ListParam == 2 { cond += ` AND a.service_product_id = 2 ` } // 开票到款日期 if req.TimeType != 0 { if req.TimeType == 1 && 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 ? AND ?) ` pars = append(pars, st, ed) } else if req.TimeType == 2 && req.StartDate != "" && req.EndDate != "" { st := fmt.Sprint(req.StartDate, " 00:00:00") ed := fmt.Sprint(req.EndDate, " 23:59:59") cond += ` AND (d.invoice_time BETWEEN ? AND ?) ` pars = append(pars, st, ed) } else if req.TimeType == 3 && 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 ? AND ?) AND (d.invoice_time BETWEEN ? AND ?) ` pars = append(pars, st, ed, st, ed) } } else if req.StartDate != "" && req.EndDate != "" { st := fmt.Sprint(req.StartDate, " 00:00:00") ed := fmt.Sprint(req.EndDate, " 23:59:59") if req.SellerIds != "" { sellerIds := strings.Split(req.SellerIds, ",") cond += ` AND (c.seller_id in ? OR d.seller_id in ?)` pars = append(pars, sellerIds, sellerIds) cond += ` AND ((c.seller_id in ? AND a.invoice_id <> 0 AND c.invoice_time BETWEEN ? AND ?)` cond += `OR (d.seller_id in ? AND a.payment_id <> 0 AND a.invoice_id = 0 AND d.invoice_time BETWEEN ? AND ?))` pars = append(pars, sellerIds, st, ed, sellerIds, st, ed) } else { cond += ` AND ((a.invoice_id <> 0 AND c.invoice_time BETWEEN ? AND ?)` cond += `OR (a.payment_id <> 0 AND a.invoice_id = 0 AND d.invoice_time BETWEEN ? AND ?))` pars = append(pars, st, ed, st, ed) } } if req.HasInvoice == "1" { cond += ` AND a.invoice_id > 0 ` } else if req.HasInvoice == "0" { cond += ` AND a.invoice_id = 0 ` } if req.HasPayment == "1" { cond += ` AND a.payment_id > 0 ` } else if req.HasPayment == "0" { cond += ` AND a.payment_id = 0 ` } page := new(base.Page) page.SetPageSize(req.PageSize) page.SetCurrent(req.Current) //排序 if req.SortType == "" { req.SortType = "desc" } if req.SortType != "desc" && req.SortType != "asc" { resp.Fail("排序类型不正确", c) return } if req.SortParam == "" { page.AddOrderItem(base.OrderItem{Column: "sort_invoice_id", Asc: true}) page.AddOrderItem(base.OrderItem{Column: "sort_payment_id", Asc: false}) page.AddOrderItem(base.OrderItem{Column: "c.invoice_time", Asc: false}) page.AddOrderItem(base.OrderItem{Column: "c.amount", Asc: false}) page.AddOrderItem(base.OrderItem{Column: "a.create_time", Asc: false}) } else if req.SortParam == "invoice_time" { if req.SortType == "asc" { page.AddOrderItem(base.OrderItem{Column: "sort_invoice_id", Asc: false}) page.AddOrderItem(base.OrderItem{Column: "c.invoice_time", Asc: true}) page.AddOrderItem(base.OrderItem{Column: "c.amount", Asc: true}) page.AddOrderItem(base.OrderItem{Column: "a.create_time", Asc: true}) } else { page.AddOrderItem(base.OrderItem{Column: "sort_invoice_id", Asc: true}) page.AddOrderItem(base.OrderItem{Column: "c.invoice_time", Asc: false}) page.AddOrderItem(base.OrderItem{Column: "c.amount", Asc: false}) page.AddOrderItem(base.OrderItem{Column: "a.create_time", Asc: false}) } } else if req.SortParam == "payment_date" { if req.SortType == "asc" { page.AddOrderItem(base.OrderItem{Column: "sort_payment_id", Asc: false}) page.AddOrderItem(base.OrderItem{Column: "d.invoice_time", Asc: true}) page.AddOrderItem(base.OrderItem{Column: "d.amount", Asc: true}) page.AddOrderItem(base.OrderItem{Column: "a.create_time", Asc: true}) } else { page.AddOrderItem(base.OrderItem{Column: "sort_payment_id", Asc: true}) page.AddOrderItem(base.OrderItem{Column: "d.invoice_time", Asc: false}) page.AddOrderItem(base.OrderItem{Column: "d.amount", Asc: false}) page.AddOrderItem(base.OrderItem{Column: "a.create_time", Asc: false}) } } else { resp.Fail("排序字段不正确", c) return } if req.IsExport == 1 { page.SetPageSize(10000) page.SetCurrent(1) } registerList, total, e := fms.GetInvoicePaymentCensusPageList(page, cond, pars) if e != nil { resp.FailMsg("获取失败", "获取商品到款统计列表总数失败, Err: "+e.Error(), c) return } queryRegisterIds := make([]int, 0) for i := range registerList { queryRegisterIds = append(queryRegisterIds, registerList[i].ContractRegisterId) } //fmt.Println("queryRegisterIds:",queryRegisterIds) results := new(fms.InvoicePaymentCensusResp) if len(queryRegisterIds) > 0 { // 获取汇总数据IDs, 用于查询合计数据 summaryIdsCond := cond summaryIdsPars := pars summaryIds, e := fms.GetInvoicePaymentCensusSummaryDataIds(summaryIdsCond, summaryIdsPars) if e != nil { resp.FailMsg("获取失败", "获取商品到款汇总IDs失败, Err: "+e.Error(), c) return } //fmt.Println("summaryIds:",summaryIds) var listErr, totalErr, totalGroupErr error wg := sync.WaitGroup{} // 响应列表 respList := make([]*fms.InvoicePaymentCensusItem, 0) summaryList := make([]*fms.InvoicePaymentSummaryItem, 0) wg.Add(1) go func() { defer wg.Done() // 获取汇总数据 summaryCond := cond summaryCond += ` AND a.register_id IN ?` summaryPars := pars summaryPars = append(summaryPars, queryRegisterIds) summaryData, e := fms.GetInvoicePaymentCensusSummaryData(summaryCond, summaryPars) if e != nil { resp.FailMsg("获取失败", "获取商品到款汇总列表失败, Err: "+e.Error(), c) return } summaryList = summaryData //summaryIds := make([]int, 0) paymentIds := make([]int, 0) for i := range summaryList { //summaryIds = append(summaryIds, summaryList[i].SummaryId) if summaryList[i].PaymentId > 0 { paymentIds = append(paymentIds, summaryList[i].PaymentId) } } // 合同套餐 /*contractServiceCond := `contract_register_id IN ?` contractServicePars := make([]interface{}, 0) contractServicePars = append(contractServicePars, queryRegisterIds) contractServiceOB := new(fms.ContractService) contractServiceList, e := contractServiceOB.List(contractServiceCond, contractServicePars) if e != nil { listErr = fmt.Errorf("获取合同套餐列表失败, Err: %s", e.Error()) return } contractServiceMap := make(map[int][]*fms.ContractService, 0) servicesNameMap := make(map[int][]string, 0) for i := range contractServiceList { if contractServiceMap[contractServiceList[i].ContractRegisterId] == nil { contractServiceMap[contractServiceList[i].ContractRegisterId] = make([]*fms.ContractService, 0) } contractServiceMap[contractServiceList[i].ContractRegisterId] = append(contractServiceMap[contractServiceList[i].ContractRegisterId], contractServiceList[i]) servicesNameMap[contractServiceList[i].ContractRegisterId] = append(servicesNameMap[contractServiceList[i].ContractRegisterId], contractServiceList[i].Title) } */ servicesNameMap, serviceFormatMap, e := fmsService.GetContractServiceNameFormat(queryRegisterIds) if e != nil { listErr = fmt.Errorf("获取合同套餐列表失败, Err: %s", e.Error()) return } // 到款套餐分配 serviceAmountMap := make(map[int][]*fms.ContractPaymentServiceAmount, 0) if len(paymentIds) > 0 { serviceAmountCond := `contract_payment_id IN ?` serviceAmountPars := make([]interface{}, 0) serviceAmountPars = append(serviceAmountPars, paymentIds) serviceAmountOB := new(fms.ContractPaymentServiceAmount) serviceAmountList, e := serviceAmountOB.List(serviceAmountCond, serviceAmountPars) if e != nil { listErr = fmt.Errorf("获取到款套餐分配列表失败, Err: %s", e.Error()) return } for i := range serviceAmountList { if serviceAmountMap[serviceAmountList[i].ContractPaymentId] == nil { serviceAmountMap[serviceAmountList[i].ContractPaymentId] = make([]*fms.ContractPaymentServiceAmount, 0) } serviceAmountMap[serviceAmountList[i].ContractPaymentId] = append(serviceAmountMap[serviceAmountList[i].ContractPaymentId], serviceAmountList[i]) } } sellerTypeMap := map[int]string{2: "FICC销售", 5: "权益销售"} sellerList, e := crmService.GetSellerDepartmentListWithEnable() if e != nil { resp.FailData("获取销售失败", "Err:"+e.Error(), c) return } sellerMap := make(map[int]*crm.SellerAdminWithGroupTeam) for i := range sellerList { sellerMap[sellerList[i].SellerId] = sellerList[i] } // 重组汇总数据 summaryMap := make(map[int][]*fms.InvoicePaymentCensusInfo) amountMap := make(map[string]*fms.ContractPaymentServiceAmount) for i := range summaryList { v := new(fms.InvoicePaymentCensusInfo) v.InvoiceId = summaryList[i].InvoiceId v.InvoiceDate = utils.TimeTransferString(utils.FormatDate, summaryList[i].InvoiceDate) v.InvoiceAmount = summaryList[i].InvoiceAmount v.SellerId = summaryList[i].SellerId v.SellerName = summaryList[i].SellerName v.SellerGroupId = summaryList[i].SellerGroupId v.SellerGroupName = summaryList[i].SellerGroupName if seller, ok := sellerMap[summaryList[i].SellerId]; ok { v.SellerType = sellerTypeMap[seller.DepartmentId] } v.PaymentId = summaryList[i].PaymentId v.PaymentDate = utils.TimeTransferString(utils.FormatDate, summaryList[i].PaymentDate) v.PaymentAmount = summaryList[i].PaymentAmount v.PayType = summaryList[i].PayType // 套餐到款分配 svaList := make([]*fms.ContractPaymentServiceAmountItem, 0) amountList := serviceAmountMap[summaryList[i].PaymentId] if amountList != nil { for i := range amountList { k := fmt.Sprintf("%d-%d", amountList[i].ContractPaymentId, amountList[i].ServiceTemplateId) amountMap[k] = amountList[i] } } // 合同对应的所有套餐 svList := serviceFormatMap[summaryList[i].RegisterId] if svList != nil { for ii := range svList { vv := new(fms.ContractPaymentServiceAmountItem) vv.ServiceTemplateId = svList[ii].ServiceTemplateId vv.ServiceTemplateName = svList[ii].FormatTitle vv.ServiceTemplatePid = svList[ii].ServiceTemplatePid vv.ServiceProductId = svList[ii].ServiceProductId k2 := fmt.Sprintf("%d-%d", summaryList[i].PaymentId, svList[ii].ServiceTemplateId) a := amountMap[k2] if a != nil { vv.ContractPaymentServiceAmountId = a.ContractPaymentServiceAmountId vv.ContractPaymentId = a.ContractPaymentId vv.Amount = a.Amount } svaList = append(svaList, vv) } } v.ServiceAmountList = svaList summaryMap[summaryList[i].SummaryId] = append(summaryMap[summaryList[i].SummaryId], v) } // 响应列表 for i := range registerList { v := new(fms.InvoicePaymentCensusItem) v.SummaryId = registerList[i].SummaryId v.ContractRegisterId = registerList[i].ContractRegisterId v.CompanyName = registerList[i].CompanyName v.NewCompany = registerList[i].NewCompany v.StartDate = utils.TimeTransferString(utils.FormatDate, registerList[i].StartDate) v.EndDate = utils.TimeTransferString(utils.FormatDate, registerList[i].EndDate) v.ServicesName = servicesNameMap[registerList[i].ContractRegisterId] v.InvoicePaymentList = summaryMap[registerList[i].SummaryId] v.ContractType = registerList[i].ContractType v.ActualPayCompanies = registerList[i].ActualPayCompanies respList = append(respList, v) } }() // 开票到款金额合计(换算后) var invoiceTotal, paymentTotal, amountTotal float64 wg.Add(1) go func() { defer wg.Done() if len(summaryIds) == 0 { return } amountTotalCond := `a.id IN ?` amountTotalPars := make([]interface{}, 0) amountTotalPars = append(amountTotalPars, summaryIds) invoiceSum, e := fms.GetContractSummaryInvoicePaymentAmountTotal(amountTotalCond, amountTotalPars, 1) if e != nil { totalErr = fmt.Errorf("获取汇总开票金额合计失败, Err: %s", e.Error()) return } invoiceTotal, _ = strconv.ParseFloat(fmt.Sprintf("%.2f", invoiceSum), 64) paymentSum, e := fms.GetContractSummaryInvoicePaymentAmountTotal(amountTotalCond, amountTotalPars, 2) if e != nil { totalErr = fmt.Errorf("获取汇总到款金额合计失败, Err: %s", e.Error()) return } paymentTotal, _ = strconv.ParseFloat(fmt.Sprintf("%.2f", paymentSum), 64) amountCond := `a.id IN ? AND (a.invoice_id <> 0 OR (a.payment_id <> 0 AND a.invoice_id =0))` amountPars := make([]interface{}, 0) amountPars = append(amountPars, summaryIds) amountSum, e := fms.GetContractSummaryInvoicePaymentAmount(amountCond, amountPars) if e != nil { totalErr = fmt.Errorf("获取汇总金额合计失败, Err: %s", e.Error()) return } amountTotal, _ = strconv.ParseFloat(fmt.Sprintf("%.2f", amountSum), 64) }() // 分币种金额统计 invoiceCurrencyTotals := make([]*fms.InvoiceListCurrencyTotal, 0) paymentCurrencyTotals := make([]*fms.InvoiceListCurrencyTotal, 0) wg.Add(1) go func() { defer wg.Done() currencyOB := new(fms.CurrencyUnit) currencyCond := `enable = 1` currencyPars := make([]interface{}, 0) currencyList, e := currencyOB.List(currencyCond, currencyPars) if e != nil { totalGroupErr = fmt.Errorf("获取货币列表失败, Err: %s", e.Error()) return } //unitMap := make(map[string]string) for i := range currencyList { //unitMap[currencyList[i].Code] = currencyList[i].UnitName invoiceCurrencyTotals = append(invoiceCurrencyTotals, &fms.InvoiceListCurrencyTotal{ Name: currencyList[i].Name, UnitName: currencyList[i].UnitName, Code: currencyList[i].Code, FlagImg: currencyList[i].FlagImg, }) paymentCurrencyTotals = append(paymentCurrencyTotals, &fms.InvoiceListCurrencyTotal{ Name: currencyList[i].Name, UnitName: currencyList[i].UnitName, Code: currencyList[i].Code, FlagImg: currencyList[i].FlagImg, }) } if len(summaryIds) == 0 { return } totalGroupCond := `a.id IN ?` totalGroupPars := make([]interface{}, 0) totalGroupPars = append(totalGroupPars, summaryIds) invoiceSumGroup, e := fms.GetSummaryListCurrencySum(totalGroupCond, totalGroupPars, 1) if e != nil { totalGroupErr = fmt.Errorf("获取汇总货币合计开票金额失败, Err: %s", e.Error()) return } paymentSumGroup, e := fms.GetSummaryListCurrencySum(totalGroupCond, totalGroupPars, 2) if e != nil { totalGroupErr = fmt.Errorf("获取汇总货币合计到款金额失败, Err: %s", e.Error()) return } invoiceSumMap := make(map[string]float64) paymentSumMap := make(map[string]float64) for i := range invoiceSumGroup { invoiceSumMap[invoiceSumGroup[i].CurrencyUnit] = invoiceSumGroup[i].OriginAmountTotal continue } for i := range paymentSumGroup { paymentSumMap[paymentSumGroup[i].CurrencyUnit] = paymentSumGroup[i].OriginAmountTotal continue } for i := range invoiceCurrencyTotals { a, _ := strconv.ParseFloat(fmt.Sprintf("%.2f", invoiceSumMap[invoiceCurrencyTotals[i].Code]), 64) invoiceCurrencyTotals[i].Amount = a } for i := range paymentCurrencyTotals { a, _ := strconv.ParseFloat(fmt.Sprintf("%.2f", paymentSumMap[paymentCurrencyTotals[i].Code]), 64) paymentCurrencyTotals[i].Amount = a } }() wg.Wait() if listErr != nil { resp.FailMsg("获取失败", listErr.Error(), c) return } if totalErr != nil { resp.FailMsg("获取失败", totalErr.Error(), c) return } if totalGroupErr != nil { resp.FailMsg("获取失败", totalGroupErr.Error(), c) return } results.DataList = respList results.InvoiceTotal = invoiceTotal results.PaymentTotal = paymentTotal results.AmountTotal = amountTotal results.InvoiceCurrencyTotal = invoiceCurrencyTotals results.PaymentCurrencyTotal = paymentCurrencyTotals } // 是否导出 if req.IsExport == 1 { ExportInvoicePaymentCensusList(c, results) return } page.SetTotal(total) baseData := new(base.BaseData) baseData.SetPage(page) baseData.SetList(results) resp.OkData("获取成功", baseData, c) } // IncomeList // @Title 业务收入统计表 // @Description 获取业务收入统计表接口 // @Param ListParam query int false "套餐类型: 0-月度; 1-季度; 2-半年度；3-年度；4-月度累计" // @Success 200 {object} fms.CensusIncomeChartResp // @router /census/income/list [get] func (this *InvoicePaymentController) IncomeList(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 getCensusIncomeListV2(ch, req) for v := range ch { incomeList = v close(ch) } // 是否导出 if req.IsExport == 1 { ExportIncomeList(c, incomeList.DataList) return } resp.OkData("获取成功", incomeList, c) } func getCensusIncomeListV2(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 }() //todayStr := utils.GetToday("20060102") //key := "admin:home:fmsIncomeList:" + todayStr // //redisJsonData, redisErr := global.Redis.Get(context.TODO(), key).Result() //if redisErr != nil { //获取最新的开票到款日期 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", err.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 //共存在多少个月 //if req.ListParam == "1" { // numMonth = numMonth / 3 //} else if req.ListParam == "2" { // numMonth = numMonth / 6 //} else if req.ListParam == "3" { // numMonth = numMonth / 12 //} // 累计值 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) } //fmt.Println("i:",i) //fmt.Println("j:",j) { //本期 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) if e != nil { return } // 开票到款金额合计(换算后) var prevAmountTotal float64 if len(prevSummaryIds) > 0 { amountCond := `a.id IN ? ` amountPars := make([]interface{}, 0) amountPars = append(amountPars, prevSummaryIds) 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, prevSt, prevEd, sellerIds, prevSt, prevEd) } 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 } } 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 } } 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 } } //fmt.Println("partHistoryAccumulate:",partHistoryAccumulate) } 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)) //计算同比值 for i := range prevTotalMoneySlice { var yoy float64 var yoyStr string //fmt.Println("1：", prevTotalMoneySlice[i]) //fmt.Println("2:", totalMoneySlice[i]) //fmt.Println("3:", totalMoneySlice[i]-prevTotalMoneySlice[i]) 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) } incomeChart.Title = "开票到款统计图" incomeChart.Date = dateSlice incomeChart.TotalMoney = totalMoneySlice incomeChart.PrevTotalMoney = prevTotalMoneySlice incomeChart.Yoy = yoySlice //redisJsonData, err := json.Marshal(incomeChart) //if err == nil { // global.Redis.SetEX(context.TODO(), key, string(redisJsonData), time.Minute*30) //} //} else { // err = json.Unmarshal([]byte(redisJsonData), &incomeChart) // if err != nil { // fmt.Println("近两年的收入统计数据，json转换失败") // } //} return } // ExportIncomeList 导出业务收入统计列表 func ExportIncomeList(c *gin.Context, list []*fms.IncomeSummaryItem) { // 生成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 } // 数据表头 rowTitle := []string{"开票日期", "新客户(1)", "客户名称", "金额", "销售"} titleRow := sheet.AddRow() for i := range rowTitle { v := titleRow.AddCell() v.SetString(rowTitle[i]) v.SetStyle(style) } incomeSummaryItemList := make(fms.IncomeSummaryItemList, 0) incomeSummaryItemList = list sort.Sort(incomeSummaryItemList) // 填充数据 for _, v := range incomeSummaryItemList { dataRow := sheet.AddRow() dataRow.AddCell().SetString(v.InvoiceDate.Format(utils.FormatDate)) // 开票日期 newCompany := 0 if v.ContractType == 1 { newCompany = 1 } dataRow.AddCell().SetString(strconv.Itoa(newCompany)) // 新客户 dataRow.AddCell().SetString(v.CompanyName) // 客户名称 dataRow.AddCell().SetString(fmt.Sprint(v.Amount)) // 金额 dataRow.AddCell().SetString(v.SellerName) // 销售员 } // 输出文件 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) } // List // @Title 未开票统计列表 // @Description 未开票统计列表 // @Param Keyword query string false "关键词" // @Param SellGroupId query int false "销售组别ID" // @Param ServiceType query int false "套餐类型" // @Param StartDate query string false "合同开始日期" // @Param EndDate query string false "合同结束日期" // @Param TimeType query int false "时间类型: 1-开票时间; 2-到款时间" // @Param HasInvoice query string false "是否已开票: 0-否; 1-是; 空-全部" // @Param HasPayment query string false "是否已到款: 0-否; 1-是; 空-全部" // @Param IsExport query int false "是否导出: 0-否; 1-是" // @Param ListParam query int false "套餐类型: 0-全部; 1-ficc; 2-权益" // @Param SortParam query string false "排序字段参数，用来排序的字段， 枚举值：'start_date':开票日 、 'end_date':到款日" // @Param SortType query string true "如何排序，是正序还是倒序，枚举值：`asc 正序`,`desc 倒叙`" // @Success 200 {object} fms.ContractRegisterItem // @router /census/invoice_payment/not_invoice/list [get] func (ct *InvoicePaymentController) NotInvoiceList(c *gin.Context) { var req fms.InvoicePaymentCensusListReq 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 } cond := `1 = 1` pars := make([]interface{}, 0) noSummaryCond := `1 = 1` noSummaryPars := make([]interface{}, 0) // 客户姓名/合同编号 if req.Keyword != "" { kw := "%" + req.Keyword + "%" cond += ` AND (b.company_name LIKE ? OR b.contract_code LIKE ?)` pars = append(pars, kw, kw) noSummaryCond += ` AND (b.company_name LIKE ? OR b.contract_code LIKE ?)` noSummaryPars = append(noSummaryPars, kw, kw) } if req.SellerIds != "" { sellerIds := strings.Split(req.SellerIds, ",") cond += ` AND (b.seller_id in ? OR b.rai_seller_id in ?)` pars = append(pars, sellerIds, sellerIds) noSummaryCond += ` AND (b.seller_id in ? OR b.rai_seller_id in ?)` noSummaryPars = append(noSummaryPars, sellerIds, sellerIds) } // 套餐筛选 if req.ServiceTypes != "" { serviceTypes := strings.Split(req.ServiceTypes, ",") tempRegisterIds, e := fms.GetContractRegisterIdsByTempId(serviceTypes) if e != nil { resp.FailMsg("获取失败", "获取合同登记IDs失败, Err: "+e.Error(), c) return } if len(tempRegisterIds) > 0 { cond += ` AND a.register_id IN ?` pars = append(pars, tempRegisterIds) noSummaryCond += ` AND b.register_id IN ? ` noSummaryPars = append(noSummaryPars, tempRegisterIds) } else { cond += ` AND 1 = 2` noSummaryCond += ` AND 1 = 2` } } if req.ListParam == 1 { cond += ` AND a.service_product_id = 1 ` noSummaryCond += ` AND b.product_ids = '1' ` } else if req.ListParam == 2 { cond += ` AND a.service_product_id = 2 ` noSummaryCond += ` AND b.product_ids = '2' ` } page := new(base.Page) page.SetPageSize(req.PageSize) page.SetCurrent(req.Current) //排序 if req.SortType == "" { req.SortType = "asc" } if req.SortType != "desc" && req.SortType != "asc" { resp.Fail("排序类型不正确", c) return } if req.SortParam == "" { page.AddOrderItem(base.OrderItem{Column: "e.start_date", Asc: true}) } else if req.SortParam == "start_date" { if req.SortType == "asc" { page.AddOrderItem(base.OrderItem{Column: "e.start_date", Asc: true}) } else { page.AddOrderItem(base.OrderItem{Column: "e.start_date", Asc: false}) } } else if req.SortParam == "end_date" { if req.SortType == "asc" { page.AddOrderItem(base.OrderItem{Column: "e.end_date", Asc: true}) } else { page.AddOrderItem(base.OrderItem{Column: "e.end_date", Asc: false}) } } else { resp.Fail("排序字段不正确", c) return } if req.IsExport == 1 { page.SetPageSize(10000) page.SetCurrent(1) } cond += ` AND b.contract_amount <> b.invoiced_amount AND b.is_deleted = 0 AND b.contract_amount <> b.payment_amount AND b.contract_status <> 4 ` registerList, total, e := fms.GetInvoiceCensusPageList(page, cond, pars) if e != nil { resp.FailMsg("获取失败", "获取商品到款统计列表总数失败, Err: "+e.Error(), c) return } queryRegisterIds := make([]int, 0) for _, v := range registerList { queryRegisterIds = append(queryRegisterIds, v.ContractRegisterId) } fmt.Println("queryRegisterIds:", queryRegisterIds) results := new(fms.NotInvoicePaymentCensusResp) if len(queryRegisterIds) > 0 { // 获取合同的套餐金额信息 serviceAmountList, e := fms.GetContractServiceAmountByContractRegisterIds(queryRegisterIds) if e != nil { resp.FailData("获取合同的套餐金额信息失败", "Err:"+e.Error(), c) return } serviceAmountMap := make(map[int]map[int]*fms.ContractServiceAmount, 0) for _, v := range serviceAmountList { if _, ok := serviceAmountMap[v.ContractRegisterId]; !ok { serviceAmountMap[v.ContractRegisterId] = make(map[int]*fms.ContractServiceAmount) } serviceAmountMap[v.ContractRegisterId][v.ProductId] = v } var listErr, totalErr, totalGroupErr error wg := sync.WaitGroup{} // 响应列表 respList := make([]*fms.NotPaymentCensusItem, 0) summaryList := make([]*fms.NotInvoicePaymentSummaryItem, 0) wg.Add(1) go func() { defer wg.Done() // 获取汇总数据 summaryCond := cond summaryCond += ` AND a.register_id IN ?` summaryPars := pars summaryPars = append(summaryPars, queryRegisterIds) summaryData, e := fms.GetNotInvoicePaymentCensusSummaryData(summaryCond, summaryPars) if e != nil { resp.FailMsg("获取失败", "获取商品到款汇总列表失败, Err: "+e.Error(), c) return } notQueryRegisterIds := make([]int, 0) for _, v := range summaryData { notQueryRegisterIds = append(notQueryRegisterIds, v.ContractRegisterId) } noSummaryCond += ` AND b.is_deleted = 0 AND b.contract_status <> 4 ` if len(notQueryRegisterIds) >0 { noSummaryCond += ` AND b.contract_register_id IN ? AND b.contract_register_id NOT IN ?` noSummaryPars = append(noSummaryPars, queryRegisterIds, notQueryRegisterIds) } else { noSummaryCond += ` AND b.contract_register_id IN ? ` noSummaryPars = append(noSummaryPars, queryRegisterIds) } noSummaryData, e := fms.GetNoInvoicePaymentCensusData(noSummaryCond, noSummaryPars) if e != nil { resp.FailMsg("获取失败", "GetNoInvoicePaymentCensusData, Err: "+e.Error(), c) return } fmt.Println("len(summaryData)", len(summaryData)) fmt.Println("len(noSummaryData)", len(noSummaryData)) summaryList = summaryData NotInvoiceSummaryMap := make(map[int][]*fms.NotInvoicePaymentSummaryItem, 0) for _, v := range summaryList { fmt.Println("v2.ContractRegisterId", v.ContractRegisterId) NotInvoiceSummaryMap[v.RegisterId] = append(NotInvoiceSummaryMap[v.RegisterId], v) } summaryList = append(summaryList, noSummaryData...) //fmt.Println("len(NotInvoiceSummaryMap):", len(NotInvoiceSummaryMap)) for _, v := range summaryList { //fmt.Println("ProductIds:", v.ProductIds) //fmt.Println("ContractRegisterId:", v.ContractRegisterId) //fmt.Println("ServiceProductId:", v.ServiceProductId) //fmt.Println("RegisterId:", v.RegisterId) //fmt.Println("len(NotInvoiceSummaryMap[v.RegisterId]):", len(NotInvoiceSummaryMap[v.ContractRegisterId])) if (v.ProductIds == "1,2" || v.ProductIds == "2,1") && len(NotInvoiceSummaryMap[v.ContractRegisterId]) == 1 { if v.ServiceProductId == 1 { if serviceAmount, ok := serviceAmountMap[v.ContractRegisterId]; ok { item := fms.NotInvoicePaymentSummaryItem{ NotInvoicedAmountTotal: serviceAmount[2].ServiceAmount, ServiceProductId: 2, } item.SellerId = v.SellerId item.SellerName = v.SellerName item.RaiSellerId = v.RaiSellerId item.RaiSellerName = v.RaiSellerName NotInvoiceSummaryMap[v.ContractRegisterId] = append(NotInvoiceSummaryMap[v.ContractRegisterId], &item) } } else { if serviceAmount, ok := serviceAmountMap[v.ContractRegisterId]; ok { item := fms.NotInvoicePaymentSummaryItem{ NotInvoicedAmountTotal: serviceAmount[1].ServiceAmount, ServiceProductId: 1, } item.SellerId = v.SellerId item.SellerName = v.SellerName item.RaiSellerId = v.RaiSellerId item.RaiSellerName = v.RaiSellerName NotInvoiceSummaryMap[v.ContractRegisterId] = append(NotInvoiceSummaryMap[v.ContractRegisterId], &item) } } } else if (v.ProductIds == "1,2" || v.ProductIds == "2,1") && len(NotInvoiceSummaryMap[v.ContractRegisterId]) == 0 { if serviceAmount, ok := serviceAmountMap[v.ContractRegisterId]; ok { item1 := fms.NotInvoicePaymentSummaryItem{ NotInvoicedAmountTotal: serviceAmount[2].ServiceAmount, ServiceProductId: 2, } item1.SellerId = v.SellerId item1.SellerName = v.SellerName item1.RaiSellerId = v.RaiSellerId item1.RaiSellerName = v.RaiSellerName NotInvoiceSummaryMap[v.ContractRegisterId] = append(NotInvoiceSummaryMap[v.ContractRegisterId], &item1) item2 := fms.NotInvoicePaymentSummaryItem{ NotInvoicedAmountTotal: serviceAmount[1].ServiceAmount, ServiceProductId: 1, } item2.SellerId = v.SellerId item2.SellerName = v.SellerName item2.RaiSellerId = v.RaiSellerId item2.RaiSellerName = v.RaiSellerName NotInvoiceSummaryMap[v.ContractRegisterId] = append(NotInvoiceSummaryMap[v.ContractRegisterId], &item2) } } else if v.ProductIds == "1" && len(NotInvoiceSummaryMap[v.ContractRegisterId]) == 0 { if serviceAmount, ok := serviceAmountMap[v.ContractRegisterId]; ok { item1 := fms.NotInvoicePaymentSummaryItem{ NotInvoicedAmountTotal: serviceAmount[1].ServiceAmount, ServiceProductId: 1, } item1.SellerId = v.SellerId item1.SellerName = v.SellerName item1.RaiSellerId = v.RaiSellerId item1.RaiSellerName = v.RaiSellerName NotInvoiceSummaryMap[v.ContractRegisterId] = append(NotInvoiceSummaryMap[v.ContractRegisterId], &item1) } } else if v.ProductIds == "2" && len(NotInvoiceSummaryMap[v.ContractRegisterId]) == 0 { if serviceAmount, ok := serviceAmountMap[v.ContractRegisterId]; ok { item2 := fms.NotInvoicePaymentSummaryItem{ NotInvoicedAmountTotal: serviceAmount[2].ServiceAmount, ServiceProductId: 2, } item2.SellerId = v.SellerId item2.SellerName = v.SellerName item2.RaiSellerId = v.RaiSellerId item2.RaiSellerName = v.RaiSellerName NotInvoiceSummaryMap[v.ContractRegisterId] = append(NotInvoiceSummaryMap[v.ContractRegisterId], &item2) } } } // 重组汇总数据 sellerList, e := crmService.GetSellerDepartmentListWithEnable() if e != nil { resp.FailData("获取销售失败", "Err:"+e.Error(), c) return } sellerMap := make(map[int]*crm.SellerAdminWithGroupTeam) for i := range sellerList { sellerMap[sellerList[i].SellerId] = sellerList[i] } sellerTypeMap := map[int]string{2: "FICC销售", 5: "权益销售"} // 货币列表 currencyOB := new(fms.CurrencyUnit) currencyCond := `enable = 1` currencyPars := make([]interface{}, 0) currencyList, e := currencyOB.List(currencyCond, currencyPars) if e != nil { resp.FailMsg("获取失败", "获取货币列表失败, Err: "+e.Error(), c) return } unitMap := make(map[string]string) for i := range currencyList { unitMap[currencyList[i].Code] = currencyList[i].UnitName } for registerId, list := range NotInvoiceSummaryMap { for _, v := range list { fmt.Println("v.ContractRegisterId", v.ContractRegisterId) fmt.Println("v.ServiceProductId:", v.ServiceProductId) fmt.Println("v.NotInvoicedAmountTotal:", v.NotInvoicedAmountTotal) if v.NotInvoicedAmountTotal == 0 { if v.InvoiceAmountTotal > v.PaymentAmountTotal { v.NotInvoicedAmountTotal = serviceAmountMap[registerId][v.ServiceProductId].ServiceAmount - v.InvoiceAmountTotal } else { v.NotInvoicedAmountTotal = serviceAmountMap[registerId][v.ServiceProductId].ServiceAmount - v.PaymentAmountTotal } } if v.SellerId > 0 && v.RaiSellerId > 0 { if v.ServiceProductId == 1 { if seller, ok := sellerMap[v.SellerId]; ok { v.SellerType = sellerTypeMap[seller.DepartmentId] v.SellerGroupId = seller.GroupId v.SellerGroupName = seller.GroupName } } else { if seller, ok := sellerMap[v.RaiSellerId]; ok { v.SellerType = sellerTypeMap[seller.DepartmentId] v.RaiSellerGroupId = seller.GroupId v.RaiSellerGroupName = seller.GroupName } } } else if v.SellerId > 0 && v.RaiSellerId == 0 { if seller, ok := sellerMap[v.SellerId]; ok { v.SellerType = sellerTypeMap[seller.DepartmentId] v.SellerGroupId = seller.GroupId v.SellerGroupName = seller.GroupName } } else if v.SellerId == 0 && v.RaiSellerId > 0 { if seller, ok := sellerMap[v.RaiSellerId]; ok { v.SellerType = sellerTypeMap[seller.DepartmentId] v.RaiSellerGroupId = seller.GroupId v.RaiSellerGroupName = seller.GroupName } } } } // 响应列表 for i := range registerList { v := new(fms.NotPaymentCensusItem) v.SummaryId = registerList[i].SummaryId v.ContractRegisterId = registerList[i].ContractRegisterId v.CompanyName = registerList[i].CompanyName v.NewCompany = registerList[i].NewCompany v.StartDate = utils.TimeTransferString(utils.FormatDate, registerList[i].StartDate) v.EndDate = utils.TimeTransferString(utils.FormatDate, registerList[i].EndDate) v.ContractType = registerList[i].ContractType v.ContractCode = registerList[i].ContractCode v.ContractAmount = registerList[i].ContractAmount v.UnitName = unitMap[registerList[i].CurrencyUnit] v.ProductIds = registerList[i].ProductIds //if registerList[i].InvoicedAmount != 0 { // v.NotInvoiceTotal = registerList[i].ContractAmount - registerList[i].InvoicedAmount //} else { // v.NotInvoiceTotal = registerList[i].ContractAmount - registerList[i].PaymentAmount //} v.NotInvoiceList = NotInvoiceSummaryMap[registerList[i].ContractRegisterId] respList = append(respList, v) } }() // 开票到款金额合计(换算后)V2 var invoiceTotal, contractAmount float64 wg.Add(1) go func() { defer wg.Done() registerList, e := fms.GetInvoiceCensusList(cond, pars) if e != nil { resp.FailMsg("获取失败", "获取商品到款统计列表总数失败, Err: "+e.Error(), c) return } contractIdSumMap := make(map[int]int) for _, v := range registerList { if v.InvoicedAmount >= v.PaymentAmount { invoiceTotal += v.InvoicedAmount } else if v.InvoicedAmount == 0 { invoiceTotal += v.PaymentAmount } rate := v.RMBRate if rate == 0.0 { rate = 1.0 } if _, ok := contractIdSumMap[v.ContractRegisterId]; !ok { contractIdSumMap[v.ContractRegisterId] = 1 contractAmount += v.ContractAmount / v.RMBRate } } }() // 分币种金额统计 invoiceCurrencyTotals := make([]*fms.InvoiceListCurrencyTotal, 0) paymentCurrencyTotals := make([]*fms.InvoiceListCurrencyTotal, 0) contractAmountCurrencyTotals := make([]*fms.InvoiceListCurrencyTotal, 0) wg.Add(1) go func() { defer wg.Done() currencyOB := new(fms.CurrencyUnit) currencyCond := `enable = 1` currencyPars := make([]interface{}, 0) currencyList, e := currencyOB.List(currencyCond, currencyPars) if e != nil { totalGroupErr = fmt.Errorf("获取货币列表失败, Err: %s", e.Error()) return } unitMap := make(map[string]string) for i := range currencyList { unitMap[currencyList[i].Code] = currencyList[i].UnitName invoiceCurrencyTotals = append(invoiceCurrencyTotals, &fms.InvoiceListCurrencyTotal{ Name: currencyList[i].Name, UnitName: currencyList[i].UnitName, Code: currencyList[i].Code, FlagImg: currencyList[i].FlagImg, }) paymentCurrencyTotals = append(paymentCurrencyTotals, &fms.InvoiceListCurrencyTotal{ Name: currencyList[i].Name, UnitName: currencyList[i].UnitName, Code: currencyList[i].Code, FlagImg: currencyList[i].FlagImg, }) contractAmountCurrencyTotals = append(contractAmountCurrencyTotals, &fms.InvoiceListCurrencyTotal{ Name: currencyList[i].Name, UnitName: currencyList[i].UnitName, Code: currencyList[i].Code, FlagImg: currencyList[i].FlagImg, }) } registerList, e := fms.GetInvoiceCensusList(cond, pars) if e != nil { resp.FailMsg("获取失败", "获取商品到款统计列表总数失败, Err: "+e.Error(), c) return } invoiceSumMap := make(map[string]float64) contractSumMap := make(map[string]float64) contractIdSumMap := make(map[int]int) for _, v := range registerList { if v.InvoiceOrigin >= v.PaymentOrigin { invoiceSumMap[v.CurrencyUnit] += v.InvoicedAmount } else { invoiceSumMap[v.CurrencyUnit] += v.PaymentAmount } if _, ok := contractIdSumMap[v.ContractRegisterId]; !ok { contractIdSumMap[v.ContractRegisterId] = 1 contractSumMap[v.CurrencyUnit] += v.ContractAmount } } for _, v := range contractAmountCurrencyTotals { v.Amount = contractSumMap[v.Code] - invoiceSumMap[v.Code] } }() wg.Wait() if listErr != nil { resp.FailMsg("获取失败", listErr.Error(), c) return } if totalErr != nil { resp.FailMsg("获取失败", totalErr.Error(), c) return } if totalGroupErr != nil { resp.FailMsg("获取失败", totalGroupErr.Error(), c) return } results.DataList = respList results.InvoiceTotal = invoiceTotal results.NotInvoiceTotal, _ = strconv.ParseFloat(fmt.Sprintf("%.2f", contractAmount-invoiceTotal), 64) results.InvoiceCurrencyTotal = invoiceCurrencyTotals results.PaymentCurrencyTotal = paymentCurrencyTotals results.NotInvoiceCurrencyTotal = contractAmountCurrencyTotals } // 是否导出 if req.IsExport == 1 { ExportNotInvoiceCensusList(c, results) return } page.SetTotal(total) baseData := new(base.BaseData) baseData.SetPage(page) baseData.SetList(results) resp.OkData("获取成功", baseData, c) } // List // @Title 开票未到款统计列表 // @Description 开票未到款统计列表 // @Param Keyword query string false "关键词" // @Param SellGroupId query int false "销售组别ID" // @Param ServiceType query int false "套餐类型" // @Param StartDate query string false "合同开始日期" // @Param EndDate query string false "合同结束日期" // @Param TimeType query int false "时间类型: 1-开票时间; 2-到款时间" // @Param HasInvoice query string false "是否已开票: 0-否; 1-是; 空-全部" // @Param HasPayment query string false "是否已到款: 0-否; 1-是; 空-全部" // @Param IsExport query int false "是否导出: 0-否; 1-是" // @Param ListParam query int false "套餐类型: 0-全部; 1-ficc; 2-权益" // @Param SortParam query string false "排序字段参数，用来排序的字段， 枚举值：'invoice_time':开票日 、 'payment_date':到款日" // @Param SortType query string true "如何排序，是正序还是倒序，枚举值：`asc 正序`,`desc 倒叙`" // @Success 200 {object} fms.ContractRegisterItem // @router /census/invoice_payment/not_payment/list [get] func (ct *InvoicePaymentController) NotPaymentList(c *gin.Context) { var req fms.InvoicePaymentCensusListReq 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 } cond := `1 = 1` pars := make([]interface{}, 0) // 客户姓名/销售 if req.Keyword != "" { kw := "%" + req.Keyword + "%" cond += ` AND (b.company_name LIKE ? OR b.contract_code LIKE ?)` pars = append(pars, kw, kw) } if req.SellerIds != "" { sellerIds := strings.Split(req.SellerIds, ",") cond += ` AND (c.seller_id in ?)` pars = append(pars, sellerIds) } // 套餐筛选 if req.ServiceTypes != "" { serviceTypes := strings.Split(req.ServiceTypes, ",") tempRegisterIds, e := fms.GetContractRegisterIdsByTempId(serviceTypes) if e != nil { resp.FailMsg("获取失败", "获取合同登记IDs失败, Err: "+e.Error(), c) return } if len(tempRegisterIds) > 0 { cond += ` AND a.register_id IN ?` pars = append(pars, tempRegisterIds) } else { cond += ` AND 1 = 2` } } if req.ListParam == 1 { cond += ` AND a.service_product_id = 1 ` } else if req.ListParam == 2 { cond += ` AND a.service_product_id = 2 ` } page := new(base.Page) page.SetPageSize(req.PageSize) page.SetCurrent(req.Current) //排序 if req.SortType == "" { req.SortType = "asc" } if req.SortType != "desc" && req.SortType != "asc" { resp.Fail("排序类型不正确", c) return } if req.SortType == "asc" { page.AddOrderItem(base.OrderItem{Column: "e.invoice_time", Asc: true}) page.AddOrderItem(base.OrderItem{Column: "e.modify_time", Asc: false}) } else { page.AddOrderItem(base.OrderItem{Column: "e.invoice_time", Asc: false}) page.AddOrderItem(base.OrderItem{Column: "e.modify_time", Asc: false}) } if req.IsExport == 1 { page.SetPageSize(10000) page.SetCurrent(1) } cond += ` AND b.payment_amount <> b.invoiced_amount AND b.is_deleted = 0 AND b.contract_status <> 4 ` registerList, total, e := fms.GetNoPaymentCensusPageList(page, cond, pars) if e != nil { resp.FailMsg("获取失败", "获取商品到款统计列表总数失败, Err: "+e.Error(), c) return } queryRegisterIds := make([]int, 0) for i := range registerList { queryRegisterIds = append(queryRegisterIds, registerList[i].ContractRegisterId) } fmt.Println("queryRegisterIds:", queryRegisterIds) //fmt.Println("queryRegisterIds:",queryRegisterIds) results := new(fms.NotInvoicePaymentCensusResp) if len(queryRegisterIds) > 0 { // 获取汇总数据IDs, 用于查询合计数据 summaryIdsCond := cond summaryIdsPars := pars if req.SellerIds != "" { sellerIds := strings.Split(req.SellerIds, ",") summaryIdsCond += ` AND c.seller_id in ? AND a.invoice_id <> 0 AND (c.origin_amount > d.origin_amount OR d.origin_amount IS NULL) ` summaryIdsPars = append(summaryIdsPars, sellerIds) } else { summaryIdsCond += ` AND a.invoice_id <> 0 AND (c.origin_amount > d.origin_amount OR d.origin_amount IS NULL) ` summaryIdsPars = append(summaryIdsPars) } summaryIds, e := fms.GetInvoicePaymentCensusSummaryDataIds(summaryIdsCond, summaryIdsPars) if e != nil { resp.FailMsg("获取失败", "获取商品到款汇总IDs失败, Err: "+e.Error(), c) return } //fmt.Println("summaryIds:",summaryIds) var listErr, totalErr, totalGroupErr error wg := sync.WaitGroup{} // 响应列表 respList := make([]*fms.NotPaymentCensusItem, 0) summaryList := make([]*fms.InvoicePaymentSummaryItem, 0) wg.Add(1) go func() { defer wg.Done() // 获取汇总数据 summaryCond := cond summaryCond += ` AND a.register_id IN ?` summaryPars := pars summaryPars = append(summaryPars, queryRegisterIds) if req.SellerIds != "" { sellerIds := strings.Split(req.SellerIds, ",") summaryCond += ` AND c.seller_id in ? AND a.invoice_id <> 0 AND (c.origin_amount > d.origin_amount OR d.origin_amount IS NULL) ` summaryPars = append(summaryPars, sellerIds) } else { summaryCond += ` AND a.invoice_id <> 0 AND (c.origin_amount > d.origin_amount OR d.origin_amount IS NULL) ` summaryPars = append(summaryPars) } summaryData, e := fms.GetInvoicePaymentCensusSummaryData(summaryCond, summaryPars) if e != nil { resp.FailMsg("获取失败", "获取商品到款汇总列表失败, Err: "+e.Error(), c) return } summaryList = summaryData //summaryIds := make([]int, 0) //for i := range summaryList { // //summaryIds = append(summaryIds, summaryList[i].SummaryId) // //} // 货币列表 currencyOB := new(fms.CurrencyUnit) currencyCond := `enable = 1` currencyPars := make([]interface{}, 0) currencyList, e := currencyOB.List(currencyCond, currencyPars) if e != nil { resp.FailMsg("获取失败", "获取货币列表失败, Err: "+e.Error(), c) return } unitMap := make(map[string]string) for i := range currencyList { unitMap[currencyList[i].Code] = currencyList[i].UnitName } //// 获取合同的套餐金额信息 //serviceAmountList, e := fms.GetContractServiceAmountByContractRegisterIds(queryRegisterIds) //if e != nil { // resp.FailData("获取合同的套餐金额信息失败", "Err:"+e.Error(), c) // return //} //serviceAmountMap := make(map[int]map[int]*fms.ContractServiceAmount, 0) //for _, v := range serviceAmountList { // if _, ok := serviceAmountMap[v.ContractRegisterId]; !ok { // serviceAmountMap[v.ContractRegisterId] = make(map[int]*fms.ContractServiceAmount) // } // serviceAmountMap[v.ContractRegisterId][v.ProductId] = v //} sellerList, e := crmService.GetSellerDepartmentListWithEnable() if e != nil { resp.FailData("获取销售失败", "Err:"+e.Error(), c) return } sellerMap := make(map[int]*crm.SellerAdminWithGroupTeam) for i := range sellerList { sellerMap[sellerList[i].SellerId] = sellerList[i] } sellerTypeMap := map[int]int{crm.SellerDepartmentId: 1, crm.RaiSellerDepartmentId: 2} // 重组汇总数据 summaryMap := make(map[int][]*fms.NotPaymentCensusInfo) for i := range summaryList { v := new(fms.NotPaymentCensusInfo) v.InvoiceId = summaryList[i].InvoiceId v.InvoiceDate = utils.TimeTransferString(utils.FormatDate, summaryList[i].InvoiceDate) v.InvoiceAmount = summaryList[i].InvoiceAmount v.SellerId = summaryList[i].SellerId v.SellerName = summaryList[i].SellerName v.SellerGroupId = summaryList[i].SellerGroupId v.SellerGroupName = summaryList[i].SellerGroupName if seller, ok := sellerMap[summaryList[i].SellerId]; ok { v.SellerType = sellerTypeMap[seller.DepartmentId] } v.PaymentId = summaryList[i].PaymentId v.PaymentDate = utils.TimeTransferString(utils.FormatDate, summaryList[i].PaymentDate) v.PaymentAmount = summaryList[i].PaymentAmount v.PayType = summaryList[i].PayType v.NotPaymentAmount = summaryList[i].InvoiceAmount - summaryList[i].PaymentAmount v.ServiceProductId = summaryList[i].ServiceProductId v.UnitName = unitMap[summaryList[i].CurrencyUnit] summaryMap[summaryList[i].SummaryId] = append(summaryMap[summaryList[i].SummaryId], v) } // 响应列表 for i := range registerList { v := new(fms.NotPaymentCensusItem) v.SummaryId = registerList[i].SummaryId v.ContractRegisterId = registerList[i].ContractRegisterId v.CompanyName = registerList[i].CompanyName v.NewCompany = registerList[i].NewCompany v.StartDate = utils.TimeTransferString(utils.FormatDate, registerList[i].StartDate) v.EndDate = utils.TimeTransferString(utils.FormatDate, registerList[i].EndDate) v.InvoicePaymentList = summaryMap[registerList[i].SummaryId] v.ContractType = registerList[i].ContractType v.ContractCode = registerList[i].ContractCode v.ContractAmount = registerList[i].ContractAmount v.UnitName = unitMap[registerList[i].CurrencyUnit] v.ProductIds = registerList[i].ProductIds respList = append(respList, v) } }() // 开票到款金额合计(换算后) var invoiceTotal, paymentTotal float64 wg.Add(1) go func() { defer wg.Done() if len(summaryIds) == 0 { return } amountTotalCond := `a.id IN ?` amountTotalPars := make([]interface{}, 0) amountTotalPars = append(amountTotalPars, summaryIds) invoiceSum, e := fms.GetContractSummaryInvoicePaymentAmountTotal(amountTotalCond, amountTotalPars, 1) if e != nil { totalErr = fmt.Errorf("获取汇总开票金额合计失败, Err: %s", e.Error()) return } invoiceTotal, _ = strconv.ParseFloat(fmt.Sprintf("%.2f", invoiceSum), 64) paymentSum, e := fms.GetContractSummaryInvoicePaymentAmountTotal(amountTotalCond, amountTotalPars, 2) if e != nil { totalErr = fmt.Errorf("获取汇总到款金额合计失败, Err: %s", e.Error()) return } paymentTotal, _ = strconv.ParseFloat(fmt.Sprintf("%.2f", paymentSum), 64) }() // 分币种金额统计 invoiceCurrencyTotals := make([]*fms.InvoiceListCurrencyTotal, 0) paymentCurrencyTotals := make([]*fms.InvoiceListCurrencyTotal, 0) notpaymentCurrencyTotals := make([]*fms.InvoiceListCurrencyTotal, 0) //contractAmountCurrencyTotals := make([]*fms.InvoiceListCurrencyTotal, 0) wg.Add(1) go func() { defer wg.Done() currencyOB := new(fms.CurrencyUnit) currencyCond := `enable = 1` currencyPars := make([]interface{}, 0) currencyList, e := currencyOB.List(currencyCond, currencyPars) if e != nil { totalGroupErr = fmt.Errorf("获取货币列表失败, Err: %s", e.Error()) return } unitMap := make(map[string]string) for i := range currencyList { unitMap[currencyList[i].Code] = currencyList[i].UnitName invoiceCurrencyTotals = append(invoiceCurrencyTotals, &fms.InvoiceListCurrencyTotal{ Name: currencyList[i].Name, UnitName: currencyList[i].UnitName, Code: currencyList[i].Code, FlagImg: currencyList[i].FlagImg, }) paymentCurrencyTotals = append(paymentCurrencyTotals, &fms.InvoiceListCurrencyTotal{ Name: currencyList[i].Name, UnitName: currencyList[i].UnitName, Code: currencyList[i].Code, FlagImg: currencyList[i].FlagImg, }) notpaymentCurrencyTotals = append(notpaymentCurrencyTotals, &fms.InvoiceListCurrencyTotal{ Name: currencyList[i].Name, UnitName: currencyList[i].UnitName, Code: currencyList[i].Code, FlagImg: currencyList[i].FlagImg, }) //contractAmountCurrencyTotals = append(contractAmountCurrencyTotals, &fms.InvoiceListCurrencyTotal{ // Name: currencyList[i].Name, // UnitName: currencyList[i].UnitName, // Code: currencyList[i].Code, // FlagImg: currencyList[i].FlagImg, //}) } if len(summaryIds) == 0 { return } totalGroupCond := `a.id IN ?` totalGroupPars := make([]interface{}, 0) totalGroupPars = append(totalGroupPars, summaryIds) invoiceSumGroup, e := fms.GetSummaryListCurrencySum(totalGroupCond, totalGroupPars, 1) if e != nil { totalGroupErr = fmt.Errorf("获取汇总货币合计开票金额失败, Err: %s", e.Error()) return } paymentSumGroup, e := fms.GetSummaryListCurrencySum(totalGroupCond, totalGroupPars, 2) if e != nil { totalGroupErr = fmt.Errorf("获取汇总货币合计到款金额失败, Err: %s", e.Error()) return } invoiceSumMap := make(map[string]float64) paymentSumMap := make(map[string]float64) //contractSumMap := make(map[string]float64) for i := range invoiceSumGroup { invoiceSumMap[invoiceSumGroup[i].CurrencyUnit] = invoiceSumGroup[i].OriginAmountTotal continue } for i := range paymentSumGroup { paymentSumMap[paymentSumGroup[i].CurrencyUnit] = paymentSumGroup[i].OriginAmountTotal continue } for i := range invoiceCurrencyTotals { a, _ := strconv.ParseFloat(fmt.Sprintf("%.2f", invoiceSumMap[invoiceCurrencyTotals[i].Code]), 64) invoiceCurrencyTotals[i].Amount = a } for i := range paymentCurrencyTotals { a, _ := strconv.ParseFloat(fmt.Sprintf("%.2f", paymentSumMap[paymentCurrencyTotals[i].Code]), 64) paymentCurrencyTotals[i].Amount = a } for i := range notpaymentCurrencyTotals { a, _ := strconv.ParseFloat(fmt.Sprintf("%.2f", invoiceSumMap[invoiceCurrencyTotals[i].Code]-paymentSumMap[paymentCurrencyTotals[i].Code]), 64) notpaymentCurrencyTotals[i].Amount = a } //contractRegisters, err := fms.GetContractInfoByRegisterIds(queryRegisterIds) //if err != nil { // resp.FailMsg("查询合同信息失败", "查询合同信息失败, Err: "+err.Error(), c) // return //} //for _, v := range contractRegisters { // if v.RMBRate == 0.0 { // v.RMBRate = 1 // } // amount, _ := strconv.ParseFloat(fmt.Sprintf("%.2f", v.ContractAmount/v.RMBRate), 64) // contractAmount += amount // contractSumMap[v.CurrencyUnit] = v.ContractAmount //} //for _, v := range contractAmountCurrencyTotals { // v.Amount = contractSumMap[v.Code] - invoiceSumMap[v.Code] //} }() wg.Wait() if listErr != nil { resp.FailMsg("获取失败", listErr.Error(), c) return } if totalErr != nil { resp.FailMsg("获取失败", totalErr.Error(), c) return } if totalGroupErr != nil { resp.FailMsg("获取失败", totalGroupErr.Error(), c) return } results.DataList = respList results.InvoiceTotal = invoiceTotal results.PaymentTotal = paymentTotal results.NotPaymentTotal = invoiceTotal - paymentTotal results.NotPaymentCurrencyTotal = notpaymentCurrencyTotals } // 是否导出 if req.IsExport == 1 { ExportNotPaymentCensusList(c, results) return } page.SetTotal(total) baseData := new(base.BaseData) baseData.SetPage(page) baseData.SetList(results) resp.OkData("获取成功", baseData, c) } // ExportNotInvoiceCensusList 导出未开票统计列表 func ExportNotInvoiceCensusList(c *gin.Context, results *fms.NotInvoicePaymentCensusResp) { list := results.DataList if len(list) == 0 { resp.Fail("列表数据为空", c) return } // 生成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 } // 数据表头 rowTitle := []string{"序号", "客户名称", "合同编号", "合同开始时间", "合同结束时间", "合同金额", "金额单位", "未开票金额", "套餐类型", "销售", "销售组别", "销售类型"} titleRow := sheet.AddRow() for i := range rowTitle { v := titleRow.AddCell() v.SetString(rowTitle[i]) v.SetStyle(style) } incomeSummaryItemList := make([]*fms.NotPaymentCensusItem, 0) incomeSummaryItemList = list sellerList, e := crmService.GetSellerDepartmentListWithEnable() if e != nil { resp.FailData("获取销售失败", "Err:"+e.Error(), c) return } sellerMap := make(map[int]*crm.SellerAdminWithGroupTeam) for i := range sellerList { sellerMap[sellerList[i].SellerId] = sellerList[i] } sellerTypeMap := map[int]string{2: "FICC销售", 5: "权益销售"} serviceProductIdMap := map[int]string{ 1: "FICC套餐", 2: "权益套餐", } // 填充数据 for i, v := range incomeSummaryItemList { dataRow := sheet.AddRow() dataRow2 := sheet.AddRow() cell := dataRow.AddCell() cell.VMerge = 1 cell.SetString(strconv.Itoa(i + 1)) //序号 dataRow2.AddCell().SetString("") cell2 := dataRow.AddCell() cell2.VMerge = 1 cell2.SetString(v.CompanyName) // 客户名称 dataRow2.AddCell().SetString("") cell3 := dataRow.AddCell() cell3.VMerge = 1 cell3.SetString(v.ContractCode) // 合同编号 dataRow2.AddCell().SetString("") cell4 := dataRow.AddCell() cell4.VMerge = 1 cell4.SetString(v.StartDate) // 合同开始时间 dataRow2.AddCell().SetString("") cell5 := dataRow.AddCell() cell5.VMerge = 1 cell5.SetString(v.EndDate) // 合同结束时间 dataRow2.AddCell().SetString("") cell6 := dataRow.AddCell() cell6.VMerge = 1 cell6.SetString(fmt.Sprintf("%.2f", v.ContractAmount)) // 合同金额 dataRow2.AddCell().SetString("") cell7 := dataRow.AddCell() cell7.VMerge = 1 cell7.SetString(v.UnitName) // 金额单位 dataRow2.AddCell().SetString("") for j, item := range v.NotInvoiceList { if j == 1 { dataRow2.AddCell().SetString(fmt.Sprintf("%.2f", item.NotInvoicedAmountTotal)) // 未开票金额 dataRow2.AddCell().SetString(serviceProductIdMap[item.ServiceProductId]) // 套餐类型 if item.SellerId > 0 && item.RaiSellerId > 0 { if item.ServiceProductId == 1 { item.SellerGroupName = sellerMap[item.SellerId].GroupName if seller, ok := sellerMap[item.SellerId]; ok { item.SellerType = sellerTypeMap[seller.DepartmentId] } dataRow2.AddCell().SetString(item.SellerName) // 销售 dataRow2.AddCell().SetString(item.SellerGroupName) // 销售组别 dataRow2.AddCell().SetString(item.SellerType) // 销售类型 } else { item.RaiSellerGroupName = sellerMap[item.RaiSellerId].GroupName if seller, ok := sellerMap[item.RaiSellerId]; ok { item.SellerType = sellerTypeMap[seller.DepartmentId] } dataRow2.AddCell().SetString(item.RaiSellerName) // 销售 dataRow2.AddCell().SetString(item.RaiSellerGroupName) // 销售组别 dataRow2.AddCell().SetString(item.SellerType) // 销售类型 } } else if item.SellerId > 0 && item.RaiSellerId == 0 { item.SellerGroupName = sellerMap[item.SellerId].GroupName if seller, ok := sellerMap[item.SellerId]; ok { item.SellerType = sellerTypeMap[seller.DepartmentId] } dataRow2.AddCell().SetString(item.SellerName) // 销售 dataRow2.AddCell().SetString(item.SellerGroupName) // 销售组别 dataRow2.AddCell().SetString(item.SellerType) // 销售类型 } else if item.SellerId == 0 && item.RaiSellerId > 0 { item.RaiSellerGroupName = sellerMap[item.RaiSellerId].GroupName if seller, ok := sellerMap[item.RaiSellerId]; ok { item.SellerType = sellerTypeMap[seller.DepartmentId] } dataRow2.AddCell().SetString(item.RaiSellerName) // 销售 dataRow2.AddCell().SetString(item.RaiSellerGroupName) // 销售组别 dataRow2.AddCell().SetString(item.SellerType) // 销售类型 } } else { dataRow.AddCell().SetString(fmt.Sprintf("%.2f", item.NotInvoicedAmountTotal)) // 未开票金额 dataRow.AddCell().SetString(serviceProductIdMap[item.ServiceProductId]) // 套餐类型 if item.SellerId > 0 && item.RaiSellerId > 0 { if item.ServiceProductId == 1 { item.SellerGroupName = sellerMap[item.SellerId].GroupName if seller, ok := sellerMap[item.SellerId]; ok { item.SellerType = sellerTypeMap[seller.DepartmentId] } dataRow.AddCell().SetString(item.SellerName) // 销售 dataRow.AddCell().SetString(item.SellerGroupName) // 销售组别 dataRow.AddCell().SetString(item.SellerType) // 销售类型 } else { item.RaiSellerGroupName = sellerMap[item.RaiSellerId].GroupName if seller, ok := sellerMap[item.RaiSellerId]; ok { item.SellerType = sellerTypeMap[seller.DepartmentId] } dataRow.AddCell().SetString(item.RaiSellerName) // 销售 dataRow.AddCell().SetString(item.RaiSellerGroupName) // 销售组别 dataRow.AddCell().SetString(item.SellerType) // 销售类型 } } else if item.SellerId > 0 && item.RaiSellerId == 0 { item.SellerGroupName = sellerMap[item.SellerId].GroupName if seller, ok := sellerMap[item.SellerId]; ok { item.SellerType = sellerTypeMap[seller.DepartmentId] } dataRow.AddCell().SetString(item.SellerName) // 销售 dataRow.AddCell().SetString(item.SellerGroupName) // 销售组别 dataRow.AddCell().SetString(item.SellerType) // 销售类型 } else if item.SellerId == 0 && item.RaiSellerId > 0 { item.RaiSellerGroupName = sellerMap[item.RaiSellerId].GroupName if seller, ok := sellerMap[item.RaiSellerId]; ok { item.SellerType = sellerTypeMap[seller.DepartmentId] } dataRow.AddCell().SetString(item.RaiSellerName) // 销售 dataRow.AddCell().SetString(item.RaiSellerGroupName) // 销售组别 dataRow.AddCell().SetString(item.SellerType) // 销售类型 } } } } // 输出文件 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) } // ExportNotPaymentCensusList 导出开票未到款统计列表 func ExportNotPaymentCensusList(c *gin.Context, results *fms.NotInvoicePaymentCensusResp) { list := results.DataList if len(list) == 0 { resp.Fail("列表数据为空", c) return } // 生成Excel文件 xlsxFile := xlsx.NewFile() style := xlsx.NewStyle() alignment := xlsx.Alignment{ Horizontal: "center", Vertical: "center", WrapText: true, } style.Alignment = alignment style.ApplyAlignment = true sheet, err := xlsxFile.AddSheet("开票未到款统计") if err != nil { resp.FailData("新增Sheet失败", "Err:"+err.Error(), c) return } _ = sheet.SetColWidth(1, 1, 30) _ = sheet.SetColWidth(3, 3, 30) // 前三行-开票金额合计 rowA := sheet.AddRow() cellAA := rowA.AddCell() cellAA.SetString(fmt.Sprintf("开票未到款合计金额（换算后）：%.2f（元）", results.InvoiceTotal)) rowBData := "开票未到款金额：" for _, v := range results.NotPaymentCurrencyTotal { rowBData += fmt.Sprintf("%s%.2f(%s) ", v.Name, v.Amount, v.UnitName) } rowB := sheet.AddRow() rowB.AddCell().SetString(rowBData) sheet.AddRow() // 表头, 套餐动态获取 rowTitle := []string{"序号", "客户名称", "合同编号", "合同有效期", "套餐类型", "开票日", "开票金额", "金额单位", "开票销售", "销售组别", "销售类型"} titleRow := sheet.AddRow() for i := range rowTitle { v := titleRow.AddCell() v.SetString(rowTitle[i]) v.SetStyle(style) } serviceProductIdMap := map[int]string{ 1: "FICC套餐", 2: "权益套餐", } sellerTypeMap := map[int]string{1: "FICC销售", 2: "权益销售"} for k, v := range list { dataRow := sheet.AddRow() // 前四个单元格根据每行开票到款条数向下合并 l := len(v.InvoicePaymentList) mergeRowNum := l - 1 // 序号 sortNum := k + 1 colA := dataRow.AddCell() colA.VMerge = mergeRowNum colA.SetString(fmt.Sprint(sortNum)) // 客户名称 colB := dataRow.AddCell() colB.VMerge = mergeRowNum colB.SetString(v.CompanyName) // 合同编号 colC := dataRow.AddCell() colC.VMerge = mergeRowNum colC.SetString(v.ContractCode) // 合同有效期 colD := dataRow.AddCell() colD.VMerge = mergeRowNum colD.SetString(fmt.Sprint(v.StartDate, "至", v.EndDate)) // 套餐类型 colE := dataRow.AddCell() colE.VMerge = mergeRowNum colE.SetString(serviceProductIdMap[v.InvoicePaymentList[0].ServiceProductId]) // 开票到款信息 for _, v2 := range v.InvoicePaymentList { rowData := []string{ v2.InvoiceDate, // 开票日 fmt.Sprint(v2.InvoiceAmount), // 开票金额 v2.UnitName, // 开票金额 v2.SellerName, // 销售 v2.SellerGroupName, // 组别 sellerTypeMap[v2.SellerType], // 销售类型 } for i := range rowData { dataRow.AddCell().SetString(rowData[i]) } } } // 输出文件 var buffer bytes.Buffer _ = xlsxFile.Write(&buffer) content := bytes.NewReader(buffer.Bytes()) randStr := time.Now().Format(utils.FormatDateTimeUnSpace) fileName := "商品到款统计_" + 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) }