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/base" "hongze/fms_api/models/crm" "hongze/fms_api/models/fms" crmService "hongze/fms_api/services/crm" fmsService "hongze/fms_api/services/fms" "hongze/fms_api/utils" "net/http" "strconv" "strings" "sync" "time" ) // InvoicePaymentController 商品到款统计 type InvoicePaymentController struct{} // 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.OrderList(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: "否", 3: "否", 4: "否"} 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) // 代付方 colE := dataRow.AddCell() colE.VMerge = mergeRowNum colE.SetString(v.ActualPayCompanies) // 是否新客户 colC := dataRow.AddCell() colC.VMerge = mergeRowNum colC.SetString(newCompanyMap[v.ContractType]) // 合同有效期 colD := dataRow.AddCell() colD.VMerge = mergeRowNum colD.SetString(v.StartDate) colF := dataRow.AddCell() colF.VMerge = mergeRowNum colF.SetString(v.EndDate) // 开票到款信息 for k2, v2 := range v.InvoicePaymentList { rowData := []string{ v2.InvoiceDate, // 开票日 fmt.Sprint(v2.InvoiceOriginAmount), // 开票金额 fmt.Sprint(v2.InvoiceAmount), // 开票换算金额 v2.PaymentDate, // 到款日 fmt.Sprint(v2.PaymentOriginAmount), // 到款金额 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 ? OR b.actual_pay_companies LIKE ? )` pars = append(pars, kw, 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.InvoiceOriginAmount = summaryList[i].InvoiceOriginAmount 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.PaymentOriginAmount = summaryList[i].PaymentOriginAmount 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) } // 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 } //fmt.Println("len(summaryData)", len(summaryData)) // 有过开票到款记录的合同ids，在后面的查询中去除 notQueryRegisterIds := make([]int, 0) for _, v := range summaryData { notQueryRegisterIds = append(notQueryRegisterIds, v.ContractRegisterId) } summaryList = summaryData // 有开票到款过的记录map，用于在后面判断是否有过开票到款，然后补充数据 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) } // 获取未进行过开票到款的合同数据，其实只用到了合同id和ProductIds用来补充不同的套餐信息 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(noSummaryData)", len(noSummaryData)) // 补充为完整的list，包含有汇总记录的和没有汇总记录的 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 { // 套餐是ficc权益都有的，但是只有一个套餐有过开票到款，手动补充另一个套餐的信息 if v.ServiceProductId == 1 { if serviceAmount, ok := serviceAmountMap[v.ContractRegisterId]; ok { item := fms.NotInvoicePaymentSummaryItem{ NotInvoicedAmountOriginTotal: serviceAmount[2].ServiceAmount, ServiceProductId: 2, } item.SellerId = v.SellerId item.SellerName = v.SellerName item.RaiSellerId = v.RaiSellerId item.RaiSellerName = v.RaiSellerName item.RMBRate = v.RMBRate NotInvoiceSummaryMap[v.ContractRegisterId] = append(NotInvoiceSummaryMap[v.ContractRegisterId], &item) } } else { if serviceAmount, ok := serviceAmountMap[v.ContractRegisterId]; ok { item := fms.NotInvoicePaymentSummaryItem{ NotInvoicedAmountOriginTotal: serviceAmount[1].ServiceAmount, ServiceProductId: 1, } item.SellerId = v.SellerId item.SellerName = v.SellerName item.RaiSellerId = v.RaiSellerId item.RaiSellerName = v.RaiSellerName item.RMBRate = v.RMBRate NotInvoiceSummaryMap[v.ContractRegisterId] = append(NotInvoiceSummaryMap[v.ContractRegisterId], &item) } } } else if (v.ProductIds == "1,2" || v.ProductIds == "2,1") && len(NotInvoiceSummaryMap[v.ContractRegisterId]) == 0 { // 套餐是ficc权益都有的，但是都没进行过开票到款，补充两个套餐的内容，直接用合同金额当作未开票金额即可 if serviceAmount, ok := serviceAmountMap[v.ContractRegisterId]; ok { item1 := fms.NotInvoicePaymentSummaryItem{ NotInvoicedAmountOriginTotal: serviceAmount[2].ServiceAmount, ServiceProductId: 2, } item1.SellerId = v.SellerId item1.SellerName = v.SellerName item1.RaiSellerId = v.RaiSellerId item1.RaiSellerName = v.RaiSellerName item1.RMBRate = v.RMBRate NotInvoiceSummaryMap[v.ContractRegisterId] = append(NotInvoiceSummaryMap[v.ContractRegisterId], &item1) item2 := fms.NotInvoicePaymentSummaryItem{ NotInvoicedAmountOriginTotal: serviceAmount[1].ServiceAmount, ServiceProductId: 1, } item2.SellerId = v.SellerId item2.SellerName = v.SellerName item2.RaiSellerId = v.RaiSellerId item2.RaiSellerName = v.RaiSellerName item2.RMBRate = v.RMBRate NotInvoiceSummaryMap[v.ContractRegisterId] = append(NotInvoiceSummaryMap[v.ContractRegisterId], &item2) } } else if v.ProductIds == "1" && len(NotInvoiceSummaryMap[v.ContractRegisterId]) == 0 { // 只有ficc套餐，但是未进行过开票到款，手动补充信息 if serviceAmount, ok := serviceAmountMap[v.ContractRegisterId]; ok { item1 := fms.NotInvoicePaymentSummaryItem{ NotInvoicedAmountOriginTotal: serviceAmount[1].ServiceAmount, ServiceProductId: 1, } item1.SellerId = v.SellerId item1.SellerName = v.SellerName item1.RaiSellerId = v.RaiSellerId item1.RaiSellerName = v.RaiSellerName item1.RMBRate = v.RMBRate 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{ NotInvoicedAmountOriginTotal: serviceAmount[2].ServiceAmount, ServiceProductId: 2, } item2.SellerId = v.SellerId item2.SellerName = v.SellerName item2.RaiSellerId = v.RaiSellerId item2.RaiSellerName = v.RaiSellerName item2.RMBRate = v.RMBRate 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 i, v := range list { // fmt.Println("v.ContractRegisterId", v.ContractRegisterId) // fmt.Println("v.ServiceProductId:", v.ServiceProductId) // fmt.Println("v.NotInvoicedAmountTotal:", v.NotInvoicedAmountTotal) // fmt.Println("registerId:",registerId) if v.NotInvoicedAmountOriginTotal == 0 { if v.InvoiceAmountTotal > v.PaymentAmountTotal { // 有合规登记或补录合同过的，有serviceAmountMap信息 if _, ok := serviceAmountMap[registerId]; ok { if _, ok2 := serviceAmountMap[registerId][v.ServiceProductId]; ok2 { list[i].NotInvoicedAmountOriginTotal = serviceAmountMap[registerId][v.ServiceProductId].ServiceAmount - v.InvoiceAmountTotal } } else { // 预登记没有合同套餐金额信息 list[i].NotInvoicedAmountOriginTotal = v.InvoiceAmountTotal - v.PaymentAmountTotal } } else { if _, ok := serviceAmountMap[registerId]; ok { // 有合规登记或补录合同过的，有serviceAmountMap信息 if _, ok2 := serviceAmountMap[registerId][v.ServiceProductId]; ok2 { list[i].NotInvoicedAmountOriginTotal = serviceAmountMap[registerId][v.ServiceProductId].ServiceAmount - v.PaymentAmountTotal } } else { // 预登记没有合同套餐金额信息 list[i].NotInvoicedAmountOriginTotal = v.PaymentAmountTotal - v.InvoiceAmountTotal } } } else { list[i].NotInvoicedAmountOriginTotal, _ = strconv.ParseFloat(fmt.Sprintf("%.2f", list[i].NotInvoicedAmountOriginTotal), 64) } list[i].NotInvoicedAmountOriginTotal, _ = strconv.ParseFloat(fmt.Sprintf("%.2f", list[i].NotInvoicedAmountOriginTotal), 64) list[i].NotInvoicedAmountTotal = v.NotInvoicedAmountOriginTotal / v.RMBRate list[i].NotInvoicedAmountTotal, _ = strconv.ParseFloat(fmt.Sprintf("%.2f", v.NotInvoicedAmountTotal), 64) // 补充销售信息 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 / rate } } }() // 分币种金额统计 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] v.Amount, _ = strconv.ParseFloat(fmt.Sprintf("%.2f", v.Amount), 64) } }() 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 fmt.Println("NotInvoiceTotal:", contractAmount-invoiceTotal) fmt.Println("contractAmount:", contractAmount) fmt.Println("invoiceTotal:", invoiceTotal) } // 是否导出 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 } 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.InvoiceOriginAmount = summaryList[i].InvoiceOriginAmount 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.PaymentOriginAmount = summaryList[i].PaymentOriginAmount v.PayType = summaryList[i].PayType v.NotPaymentOriginAmount = summaryList[i].InvoiceOriginAmount - summaryList[i].PaymentOriginAmount 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 v.NotInvoiceTotal = registerList[i].ContractAmount - registerList[i].InvoicedAmount 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, }) } 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 } }() 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 } // 前三行-开票金额合计 rowA := sheet.AddRow() cellAA := rowA.AddCell() cellAA.SetString(fmt.Sprintf("未开票合计金额（换算后）：%.2f（元）", results.NotInvoiceTotal)) rowBData := "未开票金额：" for _, v := range results.NotInvoiceCurrencyTotal { 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) } 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.NotInvoicedAmountOriginTotal)) // 未开票金额 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 { if seller, ok := sellerMap[item.SellerId]; ok { item.SellerGroupName = seller.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.NotPaymentTotal)) 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(v.StartDate) colF := dataRow.AddCell() colF.VMerge = mergeRowNum colF.SetString(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.NotPaymentOriginAmount), // 开票原始金额 v2.UnitName, // 开票金额 fmt.Sprint(v2.NotPaymentAmount), // 开票换算金额 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) }