package api import ( "box-cost/db/model" "box-cost/db/repo" "box-cost/log" "errors" "fmt" "time" "github.com/gin-gonic/gin" "github.com/go-redis/redis/v8" "github.com/xuri/excelize/v2" "go.mongodb.org/mongo-driver/bson" "go.mongodb.org/mongo-driver/bson/primitive" ) // 统计报表 func Report(r *GinRouter) { r.POST("/report/list", ReportList) r.POST("/report/download", ReportDownload) } type ReportListReq struct { Supplier string TimeRange []time.Time PackIds []primitive.ObjectID PlanIds []primitive.ObjectID Page int64 Size int64 } func ReportList(c *gin.Context, apictx *ApiSession) (interface{}, error) { // 财务管理】 添加统计报表功能。按 时间范围， 供应商(单选) 包装（多选） 计划（多选） 四个维度进行过滤，形成报表。可以下载 var form ReportListReq err := c.ShouldBindJSON(&form) if err != nil { return nil, errors.New("参数错误") } var page int64 = 1 var size int64 = 10 if form.Page > 0 { page = form.Page } else { page = 1 } if form.Size > 0 { size = form.Size } else { size = 10 } start := (page - 1) * size end := page*size - 1 // 条件处理 query := make(map[string]interface{}, 0) if form.Supplier != "" { query["supplier"] = form.Supplier } // 时间范围 if len(form.TimeRange) == 2 { start, end := getTimeRange(form.TimeRange[0], form.TimeRange[1]) query["createTime"] = bson.M{"$gte": start, "$lte": end} } // 包装 if len(form.PackIds) > 0 { packQuery := []bson.M{} for _, packId := range form.PackIds { packQuery = append(packQuery, bson.M{"packId": packId}) } query["$or"] = packQuery } // 计划 if len(form.PlanIds) > 0 { planQuery := bson.A{} for _, planId := range form.PlanIds { planQuery = append(planQuery, bson.M{"planId": planId}) } query["$or"] = planQuery } // 获取采购单符合条件的信息 purchases := []model.PurchaseBill{} repo.RepoDocsSearch(apictx.CreateRepoCtx(), &repo.PageSearchOptions{ CollectName: repo.CollectionBillPurchase, Query: query, }, &purchases) // 获取加工单符合条件的信息 produces := []model.ProduceBill{} repo.RepoDocsSearch(apictx.CreateRepoCtx(), &repo.PageSearchOptions{ CollectName: repo.CollectionBillProduce, Query: query, }, &produces) // 组装数据 cacheKey := "report:list" apictx.Svc.Redis.Del(apictx.CreateRepoCtx().Ctx, cacheKey) // key存在时 // if apictx.Svc.Redis.Exists(apictx.CreateRepoCtx().Ctx, cacheKey).Val() == 1 { // ret := apictx.Svc.Redis.ZRange(apictx.CreateRepoCtx().Ctx, cacheKey, start, end) // retList = ret.Val() // } else { // } mlen := len(purchases) + len(produces) type MapList map[string]interface{} lists := make([]MapList, mlen) if len(purchases) > 0 { for _, purchase := range purchases { apictx.Svc.Redis.ZAdd(apictx.CreateRepoCtx().Ctx, cacheKey, &redis.Z{ Score: float64(purchase.CreateTime.Unix()), Member: purchase.Id.Hex(), }) list := MapList{purchase.Id.Hex(): purchase, "type": "purchase"} lists = append(lists, list) } } if len(produces) > 0 { for _, produce := range produces { apictx.Svc.Redis.ZAdd(apictx.CreateRepoCtx().Ctx, cacheKey, &redis.Z{ Score: float64(produce.CreateTime.Unix()), Member: produce.Id.Hex(), }) list := MapList{produce.Id.Hex(): produce, "type": "produce"} lists = append(lists, list) } } // 当前key存在时 retList := make([]map[string]interface{}, 0) // 所有id ids := make([]map[string]string, 0) if apictx.Svc.Redis.Exists(apictx.CreateRepoCtx().Ctx, cacheKey).Val() == 1 { // ret := apictx.Svc.Redis.ExpireLT(apictx.CreateRepoCtx().Ctx, cacheKey, 20*time.Second) // 按创建时间升序排 result := apictx.Svc.Redis.ZRange(apictx.CreateRepoCtx().Ctx, cacheKey, start, end) if result.Err() != nil { log.Error(err) return nil, errors.New("缓存数据内部错误") } retids := apictx.Svc.Redis.ZRange(apictx.CreateRepoCtx().Ctx, cacheKey, 0, -1) // 所有id if len(retids.Val()) > 0 { for _, id := range retids.Val() { for _, item := range lists { if _, ok := item[id]; ok { ids = append(ids, map[string]string{"id": id, "type": item["type"].(string)}) } } } } // 返回的列表 if len(result.Val()) > 0 { for _, id := range result.Val() { for _, item := range lists { if _, ok := item[id]; ok { retList = append(retList, item) } } } } } type PageResult struct { List []map[string]interface{} `json:"list"` Total int64 `json:"total"` Page int64 `json:"page"` Size int64 `json:"size"` Ids []map[string]string `json:"ids"` } out := &PageResult{ List: retList, Total: int64(mlen), Page: page, Size: size, Ids: ids, } return out, nil } type ReportDownloadReq struct { Id string Type string } func ReportDownload(c *gin.Context, apictx *ApiSession) (interface{}, error) { var form []ReportDownloadReq err := c.ShouldBindJSON(&form) if err != nil { return nil, errors.New("参数错误") } if len(form) > 0 { info := model.Setting{} repo.RepoSeachDoc(apictx.CreateRepoCtx(), &repo.DocSearchOptions{ CollectName: "infos", }, &info) f := excelize.NewFile() // Create a new sheet. index := f.NewSheet("Sheet1") f.SetActiveSheet(index) f.SetDefaultFont("宋体") offset := 0 for _, req := range form { // 是采购单 if req.Type == "purchase" { purchaseId, err := primitive.ObjectIDFromHex(req.Id) if err == nil { purchase := model.PurchaseBill{} found, _ := repo.RepoSeachDoc(apictx.CreateRepoCtx(), &repo.DocSearchOptions{ CollectName: repo.CollectionBillPurchase, Query: repo.Map{"_id": purchaseId}, }, &purchase) if found { purchaseExcel := NewPurchaseBill(f) purchaseExcel.Content = &purchase purchaseExcel.Title = fmt.Sprintf("%s原材料采购单", info.CompanyName) //设置对应的数据 purchaseExcel.Offset = offset purchaseExcel.Draws() offset += 15 } } } // 是加工单 if req.Type == "produce" { produceId, err := primitive.ObjectIDFromHex(req.Id) if err == nil { produce := model.ProduceBill{} found, _ := repo.RepoSeachDoc(apictx.CreateRepoCtx(), &repo.DocSearchOptions{ CollectName: repo.CollectionBillProduce, Query: repo.Map{"_id": produceId}, }, &produce) if found { produceExcel := NewProduceBill(f) produceExcel.Content = &produce produceExcel.Title = fmt.Sprintf("%s加工单", info.CompanyName) //设置对应的数据 produceExcel.Offset = offset produceExcel.Draws() offset += 15 } } } } 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 }