package api import ( "box-cost/db/model" "box-cost/db/repo" "errors" "fmt" "strings" "time" "github.com/gin-gonic/gin" "github.com/go-redis/redis/v8" "github.com/xuri/excelize/v2" "go.mongodb.org/mongo-driver/bson/primitive" ) // 统计报表 按时间范围,供应商 包装-计划(多选) 维度进行过滤,形成报表。可以下载 func Report(r *GinRouter) { // 加工列表 r.GET("/report/produce/list", ReportProduceList) // 采购列表 r.GET("/report/purchase/list", ReportPurchaseList) r.GET("/report/product/list", ReportProductList) r.GET("/report/produce/download", ReportProduceDownload) r.GET("/report/purchase/download", ReportPurchaseDownload) r.GET("/report/product/download", ReportProductDownload) r.GETJWT("/report/list", ReportList) r.GETJWT("/report/download", ReportListDownload) } // 加工单 func ReportListDownload(c *gin.Context, apictx *ApiSession) (interface{}, error) { _, _, query := UtilQueryPageSize(c) // start, stop := CreatePageRange(page, size) supplierId := primitive.NilObjectID if _supplierId, ok := query["supplierId"]; ok { supplierId, _ = primitive.ObjectIDFromHex(_supplierId.(string)) } timeRange := []interface{}{} if _timeRange, ok := query["timeRange"]; ok { timeRange, _ = _timeRange.([]interface{}) } filtter := handleReportQuery(query) purchases := []*model.PurchaseBill{} produces := []*model.ProduceBill{} products := []*model.ProductBill{} repo.RepoDocsSearch(apictx.CreateRepoCtx(), &repo.PageSearchOptions{ CollectName: repo.CollectionBillPurchase, Query: filtter, Project: []string{"_id", "completeTime"}, }, &purchases) repo.RepoDocsSearch(apictx.CreateRepoCtx(), &repo.PageSearchOptions{ CollectName: repo.CollectionBillProduce, Query: filtter, Project: []string{"_id", "completeTime"}, }, &produces) repo.RepoDocsSearch(apictx.CreateRepoCtx(), &repo.PageSearchOptions{ CollectName: repo.CollectionBillProduct, Query: filtter, Project: []string{"_id", "completeTime"}, }, &products) // 加入redis有序集合中 redisCli := apictx.Svc.Redis reportBillKey := "report-bill-list:" + apictx.User.Parent isExist := redisCli.Exists(apictx.CreateRepoCtx().Ctx, reportBillKey).Val() // 不存在这个key时 if isExist < 1 { if len(purchases) > 0 { for _, purchase := range purchases { member := "purchase_" + purchase.Id.Hex() score := purchase.CompleteTime.Unix() redisCli.ZAdd(apictx.CreateRepoCtx().Ctx, reportBillKey, &redis.Z{Score: float64(score), Member: member}) } } if len(produces) > 0 { for _, produce := range produces { member := "produce_" + produce.Id.Hex() score := produce.CompleteTime.Unix() redisCli.ZAdd(apictx.CreateRepoCtx().Ctx, reportBillKey, &redis.Z{Score: float64(score), Member: member}) } } if len(products) > 0 { for _, product := range products { member := "product_" + product.Id.Hex() score := product.CompleteTime.Unix() redisCli.ZAdd(apictx.CreateRepoCtx().Ctx, reportBillKey, &redis.Z{Score: float64(score), Member: member}) } } // 设置过期时间 redisCli.Expire(apictx.CreateRepoCtx().Ctx, reportBillKey, 1*time.Second) } total, err := redisCli.ZCard(apictx.CreateRepoCtx().Ctx, reportBillKey).Uint64() fmt.Println(total) if err != nil { fmt.Println(err) return nil, err } reports, err := redisCli.ZRevRange(apictx.CreateRepoCtx().Ctx, reportBillKey, 0, -1).Result() if err != nil { return nil, err } if len(reports) < 1 { return nil, errors.New("没有单据信息") } lists := []map[string]interface{}{} for _, report := range reports { billArray := strings.Split(report, "_") billType := billArray[0] _billId := billArray[1] billId, _ := primitive.ObjectIDFromHex(_billId) billData := map[string]interface{}{} found := false if billType == "purchase" { found, billData = repo.RepoSeachDocMap(apictx.CreateRepoCtx(), &repo.DocSearchOptions{ CollectName: repo.CollectionBillPurchase, Query: repo.Map{"_id": billId}, }) } if billType == "produce" { found, billData = repo.RepoSeachDocMap(apictx.CreateRepoCtx(), &repo.DocSearchOptions{ CollectName: repo.CollectionBillProduce, Query: repo.Map{"_id": billId}, }) } if billType == "product" { found, billData = repo.RepoSeachDocMap(apictx.CreateRepoCtx(), &repo.DocSearchOptions{ CollectName: repo.CollectionBillProduct, Query: repo.Map{"_id": billId}, }) } if found { billData["billType"] = billType lists = append(lists, billData) } } f := excelize.NewFile() defer f.Close() index := f.NewSheet("Sheet1") f.SetActiveSheet(index) f.SetDefaultFont("宋体") report := NewReportExcel(f) supplier := model.Supplier{} supplierName := "【所有供应商】" if !supplierId.IsZero() { repo.RepoSeachDoc(apictx.CreateRepoCtx(), &repo.DocSearchOptions{ CollectName: repo.CollectionSupplier, Query: repo.Map{"_id": supplierId}, Project: []string{"name"}, }, &supplier) supplierName = supplier.Name } report.SupplierName = supplierName if len(timeRange) == 2 { report.TimeRange = append(report.TimeRange, timeRange[0].(string), timeRange[1].(string)) } report.Content = lists report.Draws() c.Header("Content-Type", "application/octet-stream") c.Header("Content-Disposition", "attachment; filename="+"report.xlsx") c.Header("Content-Transfer-Encoding", "binary") err = f.Write(c.Writer) if err != nil { return nil, err } return nil, nil } // 加工单 func ReportList(c *gin.Context, apictx *ApiSession) (interface{}, error) { page, size, query := UtilQueryPageSize(c) start, stop := CreatePageRange(page, size) filtter := handleReportQuery(query) purchases := []*model.PurchaseBill{} produces := []*model.ProduceBill{} products := []*model.ProductBill{} repo.RepoDocsSearch(apictx.CreateRepoCtx(), &repo.PageSearchOptions{ CollectName: repo.CollectionBillPurchase, Query: filtter, Project: []string{"_id", "completeTime"}, }, &purchases) repo.RepoDocsSearch(apictx.CreateRepoCtx(), &repo.PageSearchOptions{ CollectName: repo.CollectionBillProduce, Query: filtter, Project: []string{"_id", "completeTime"}, }, &produces) repo.RepoDocsSearch(apictx.CreateRepoCtx(), &repo.PageSearchOptions{ CollectName: repo.CollectionBillProduct, Query: filtter, Project: []string{"_id", "completeTime"}, }, &products) // 加入redis有序集合中 redisCli := apictx.Svc.Redis reportBillKey := "report-bill-list:" + apictx.User.Parent isExist := redisCli.Exists(apictx.CreateRepoCtx().Ctx, reportBillKey).Val() // 不存在这个key时 if isExist < 1 { if len(purchases) > 0 { for _, purchase := range purchases { member := "purchase_" + purchase.Id.Hex() score := purchase.CompleteTime.Unix() redisCli.ZAdd(apictx.CreateRepoCtx().Ctx, reportBillKey, &redis.Z{Score: float64(score), Member: member}) } } if len(produces) > 0 { for _, produce := range produces { member := "produce_" + produce.Id.Hex() score := produce.CompleteTime.Unix() redisCli.ZAdd(apictx.CreateRepoCtx().Ctx, reportBillKey, &redis.Z{Score: float64(score), Member: member}) } } if len(products) > 0 { for _, product := range products { member := "product_" + product.Id.Hex() score := product.CompleteTime.Unix() redisCli.ZAdd(apictx.CreateRepoCtx().Ctx, reportBillKey, &redis.Z{Score: float64(score), Member: member}) } } // 设置过期时间 redisCli.Expire(apictx.CreateRepoCtx().Ctx, reportBillKey, 1*time.Second) } total, err := redisCli.ZCard(apictx.CreateRepoCtx().Ctx, reportBillKey).Uint64() if err != nil { fmt.Println(err) return nil, err } reports, err := redisCli.ZRevRange(apictx.CreateRepoCtx().Ctx, reportBillKey, start, stop).Result() if err != nil { return nil, err } if len(reports) < 1 { return repo.PageResult{ List: []map[string]interface{}{}, Page: page, Size: size, Total: 0, }, nil } lists := []map[string]interface{}{} for _, report := range reports { billArray := strings.Split(report, "_") billType := billArray[0] _billId := billArray[1] billId, _ := primitive.ObjectIDFromHex(_billId) billData := map[string]interface{}{} found := false if billType == "purchase" { found, billData = repo.RepoSeachDocMap(apictx.CreateRepoCtx(), &repo.DocSearchOptions{ CollectName: repo.CollectionBillPurchase, Query: repo.Map{"_id": billId}, }) } if billType == "produce" { found, billData = repo.RepoSeachDocMap(apictx.CreateRepoCtx(), &repo.DocSearchOptions{ CollectName: repo.CollectionBillProduce, Query: repo.Map{"_id": billId}, }) } if billType == "product" { found, billData = repo.RepoSeachDocMap(apictx.CreateRepoCtx(), &repo.DocSearchOptions{ CollectName: repo.CollectionBillProduct, Query: repo.Map{"_id": billId}, }) } if found { billData["billType"] = billType lists = append(lists, billData) } } return repo.PageResult{ List: lists, Total: int64(total), Page: page, Size: size, }, nil } func CreatePageRange(page, size int64) (int64, int64) { if page < 1 { page = 1 } if size < 1 { size = 10 } start := (page - 1) * size stop := page*size - 1 return start, stop } func ReportProduceList(c *gin.Context, apictx *ApiSession) (interface{}, error) { page, size, query := UtilQueryPageSize(c) // 条件处理 // 获取采购单符合条件的信息 return repo.RepoPageSearch(apictx.CreateRepoCtx(), &repo.PageSearchOptions{ CollectName: repo.CollectionBillProduce, Query: handleReportQuery(query), Page: page, Size: size, }) } // 采购单 func ReportPurchaseList(c *gin.Context, apictx *ApiSession) (interface{}, error) { page, size, query := UtilQueryPageSize(c) return repo.RepoPageSearch(apictx.CreateRepoCtx(), &repo.PageSearchOptions{ CollectName: repo.CollectionBillPurchase, Query: handleReportQuery(query), Page: page, Size: size, }) } func ReportProductList(c *gin.Context, apictx *ApiSession) (interface{}, error) { page, size, query := UtilQueryPageSize(c) return repo.RepoPageSearch(apictx.CreateRepoCtx(), &repo.PageSearchOptions{ CollectName: repo.CollectionBillProduct, Query: handleReportQuery(query), Page: page, Size: size, }) } func ReportProduceDownload(c *gin.Context, apictx *ApiSession) (interface{}, error) { _, _, query := UtilQueryPageSize(c) // 获取采符合条件的信息 produces := []*model.ProduceBill{} err := repo.RepoDocsSearch(apictx.CreateRepoCtx(), &repo.PageSearchOptions{ CollectName: repo.CollectionBillProduce, Query: handleReportQuery(query), }, &produces) if err != nil || len(produces) < 1 { return nil, errors.New("数据不存在") } f := excelize.NewFile() index := f.NewSheet("Sheet1") sheetName := f.GetSheetName(index) f.SetActiveSheet(index) f.SetDefaultFont("宋体") border := []excelize.Border{ {Type: "top", Style: 1, Color: "000000"}, {Type: "left", Style: 1, Color: "000000"}, {Type: "right", Style: 1, Color: "000000"}, {Type: "bottom", Style: 1, Color: "000000"}, } alignCenterStyle, _ := f.NewStyle(&excelize.Style{ Alignment: &excelize.Alignment{Horizontal: "center", Vertical: "center"}, Border: border, }) companyName := getCompanyName(apictx) var budgetAmount float64 = 0 var realAmount float64 = 0 var row int = 0 for _, produce := range produces { produceExcel := NewReportProduceExcel(f) produceExcel.Row = row produceExcel.Content = produce produceExcel.Title = fmt.Sprintf("%s加工单", companyName) //设置对应的数据 produceExcel.Draws() budgetAmount += produceExcel.BudgetAmount realAmount += produceExcel.RealAmount row = produceExcel.Row + 3 } row = row - 2 startCell := fmt.Sprintf("%s%d", "A", row) endCell := fmt.Sprintf("%s%d", "H", row) f.MergeCell(sheetName, startCell, endCell) f.SetCellStyle(sheetName, startCell, endCell, alignCenterStyle) f.SetCellValue(sheetName, startCell, "汇总金额") // 预算金额汇总 budgetAmountCell := fmt.Sprintf("%s%d", "I", row) budgetAmountStr := "" if budgetAmount > 0 { budgetAmountStr = fmt.Sprintf("%.3f", budgetAmount) } f.SetCellValue(sheetName, budgetAmountCell, budgetAmountStr) f.SetCellStyle(sheetName, budgetAmountCell, budgetAmountCell, alignCenterStyle) // 实际金额汇总 realAmountCell := fmt.Sprintf("%s%d", "J", row) realAmountStr := "" if realAmount > 0 { realAmountStr = fmt.Sprintf("%.3f", realAmount) } f.SetCellValue(sheetName, realAmountCell, realAmountStr) f.SetCellStyle(sheetName, realAmountCell, realAmountCell, alignCenterStyle) f.SetRowHeight(sheetName, row, 21) c.Header("Content-Type", "application/octet-stream") c.Header("Content-Disposition", "attachment; filename="+"reportProduce.xlsx") c.Header("Content-Transfer-Encoding", "binary") err = f.Write(c.Writer) if err != nil { return nil, err } return nil, nil } func ReportPurchaseDownload(c *gin.Context, apictx *ApiSession) (interface{}, error) { _, _, query := UtilQueryPageSize(c) purchases := []model.PurchaseBill{} err := repo.RepoDocsSearch(apictx.CreateRepoCtx(), &repo.PageSearchOptions{ CollectName: repo.CollectionBillPurchase, Query: handleReportQuery(query), }, &purchases) if err != nil || len(purchases) < 1 { return nil, errors.New("数据不存在") } f := excelize.NewFile() index := f.NewSheet("Sheet1") sheetName := f.GetSheetName(index) f.SetActiveSheet(index) f.SetDefaultFont("宋体") border := []excelize.Border{ {Type: "top", Style: 1, Color: "000000"}, {Type: "left", Style: 1, Color: "000000"}, {Type: "right", Style: 1, Color: "000000"}, {Type: "bottom", Style: 1, Color: "000000"}, } alignCenterStyle, _ := f.NewStyle(&excelize.Style{ Alignment: &excelize.Alignment{Horizontal: "center", Vertical: "center"}, Border: border, }) companyName := getCompanyName(apictx) var budgetCount float64 = 0 var realCount float64 = 0 var row int = 0 for _, purchase := range purchases { var reportBill *ReportPurchaseExcel if len(purchase.Paper) > 0 { reportBill = NewReportPurchaseExcel(f) } if reportBill == nil { return nil, errors.New("数据不存在") } reportBill.Row = row reportBill.Content = &purchase reportBill.Title = fmt.Sprintf("%s原材料采购单", companyName) reportBill.Draws() budgetCount += reportBill.BudgetAmount realCount += reportBill.RealAmount row = reportBill.Row + 3 } row = row - 2 startCell := fmt.Sprintf("%s%d", "A", row) endCell := fmt.Sprintf("%s%d", "H", row) f.MergeCell(sheetName, startCell, endCell) f.SetCellStyle(sheetName, startCell, endCell, alignCenterStyle) f.SetCellValue(sheetName, startCell, "汇总金额") // 预算金额汇总 budgetCountCell := fmt.Sprintf("%s%d", "I", row) budgetCountStr := "" if realCount > 0 { budgetCountStr = fmt.Sprintf("%.3f", budgetCount) } f.SetCellValue(sheetName, budgetCountCell, budgetCountStr) f.SetCellStyle(sheetName, budgetCountCell, budgetCountCell, alignCenterStyle) // 实际金额汇总 RealCountCell := fmt.Sprintf("%s%d", "J", row) realCountStr := "" if realCount > 0 { realCountStr = fmt.Sprintf("%.3f", realCount) } f.SetCellValue(sheetName, RealCountCell, realCountStr) f.SetCellStyle(sheetName, RealCountCell, RealCountCell, alignCenterStyle) f.SetRowHeight(sheetName, row, 21) c.Header("Content-Type", "application/octet-stream") c.Header("Content-Disposition", "attachment; filename="+"reportPurchase.xlsx") c.Header("Content-Transfer-Encoding", "binary") err = f.Write(c.Writer) if err != nil { return nil, err } return nil, nil } func ReportProductDownload(c *gin.Context, apictx *ApiSession) (interface{}, error) { _, _, query := UtilQueryPageSize(c) // 获取采符合条件的信息 products := []*model.ProductBill{} err := repo.RepoDocsSearch(apictx.CreateRepoCtx(), &repo.PageSearchOptions{ CollectName: repo.CollectionBillProduct, Query: handleReportQuery(query), }, &products) if err != nil || len(products) < 1 { return nil, errors.New("数据不存在") } f := excelize.NewFile() index := f.NewSheet("Sheet1") sheetName := f.GetSheetName(index) f.SetActiveSheet(index) f.SetDefaultFont("宋体") border := []excelize.Border{ {Type: "top", Style: 1, Color: "000000"}, {Type: "left", Style: 1, Color: "000000"}, {Type: "right", Style: 1, Color: "000000"}, {Type: "bottom", Style: 1, Color: "000000"}, } alignCenterStyle, _ := f.NewStyle(&excelize.Style{ Alignment: &excelize.Alignment{Horizontal: "center", Vertical: "center"}, Border: border, }) companyName := getCompanyName(apictx) var budgetAmount float64 = 0 var realAmount float64 = 0 var row int = 0 for _, product := range products { productExcel := NewReportProductBill(f) productExcel.Row = row productExcel.Content = product productExcel.Title = companyName productExcel.Draws() budgetAmount += productExcel.BudgetAmount realAmount += productExcel.RealAmount row = productExcel.Row + 3 } row = row - 2 startCell := fmt.Sprintf("%s%d", "A", row) endCell := fmt.Sprintf("%s%d", "H", row) f.MergeCell(sheetName, startCell, endCell) f.SetCellStyle(sheetName, startCell, endCell, alignCenterStyle) f.SetCellValue(sheetName, startCell, "汇总金额") // 预算金额汇总 budgetAmountCell := fmt.Sprintf("%s%d", "I", row) budgetCountStr := "" if realAmount > 0 { budgetCountStr = fmt.Sprintf("%.3f", realAmount) } f.SetCellValue(sheetName, budgetAmountCell, budgetCountStr) f.SetCellStyle(sheetName, budgetAmountCell, budgetAmountCell, alignCenterStyle) // 实际金额汇总 realAmountCell := fmt.Sprintf("%s%d", "J", row) realAmountStr := "" if realAmount > 0 { realAmountStr = fmt.Sprintf("%.3f", realAmount) } f.SetCellValue(sheetName, realAmountCell, realAmountStr) f.SetCellStyle(sheetName, realAmountCell, realAmountCell, alignCenterStyle) f.SetRowHeight(sheetName, row, 21) c.Header("Content-Type", "application/octet-stream") c.Header("Content-Disposition", "attachment; filename="+"reportProduce.xlsx") c.Header("Content-Transfer-Encoding", "binary") err = f.Write(c.Writer) if err != nil { return nil, err } return nil, nil }