box-cost/boxcost/api/report.go

package api

import (
	"box-cost/db/model"
	"box-cost/db/repo"
	"errors"
	"fmt"

	"github.com/gin-gonic/gin"
	"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/produce/list", ReportProduceList)
	// 采购列表
	r.POST("/report/purchase/list", ReportPurchaseList)
	r.POST("/report/produce/download", ReportProduceDownload)
	r.POST("/report/purchase/download", ReportPurchaseDownload)
}

type ReportListReq struct {
	SupplierId primitive.ObjectID
	TimeRange  []string
	PackIds    []primitive.ObjectID
	PlanIds    []primitive.ObjectID
	Page       int64
	Size       int64
}

// 加工单
func ReportProduceList(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
	}
	if form.Size > 0 {
		size = form.Size
	}

	// 条件处理
	query := make(map[string]interface{}, 0)
	query["status"] = "complete"
	if !form.SupplierId.IsZero() {
		query["supplierId"] = form.SupplierId
	}

	// 时间范围
	if len(form.TimeRange) == 2 {
		start, end := getTimeRange(form.TimeRange[0], form.TimeRange[1])
		query["updateTime"] = 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
	}

	// 获取采购单符合条件的信息
	return repo.RepoPageSearch(apictx.CreateRepoCtx(), &repo.PageSearchOptions{
		CollectName: repo.CollectionBillPurchase,
		Query:       query,
		Page:        page,
		Size:        size,
	})

}

// 采购单
func ReportPurchaseList(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
	}
	if form.Size > 0 {
		size = form.Size
	}

	// 条件处理
	query := make(map[string]interface{}, 0)
	query["status"] = "complete"
	if !form.SupplierId.IsZero() {
		query["supplierId"] = form.SupplierId
	}

	// 时间范围
	if len(form.TimeRange) == 2 {
		start, end := getTimeRange(form.TimeRange[0], form.TimeRange[1])
		query["updateTime"] = 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
	}

	// 获取采购单符合条件的信息
	return repo.RepoPageSearch(apictx.CreateRepoCtx(), &repo.PageSearchOptions{
		CollectName: repo.CollectionBillPurchase,
		Query:       query,
		Page:        page,
		Size:        size,
	})

}

// func ReportProduceList(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)
// 	query["status"] = "complete"
// 	if !form.SupplierId.IsZero() {
// 		query["supplierId"] = form.SupplierId
// 	}

// 	// 时间范围
// 	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

// }

func ReportProduceDownload(c *gin.Context, apictx *ApiSession) (interface{}, error) {
	var form ReportListReq
	err := c.ShouldBindJSON(&form)
	if err != nil {
		return nil, errors.New("参数错误")
	}
	// 条件处理
	query := make(map[string]interface{}, 0)
	query["status"] = "complete"
	if !form.SupplierId.IsZero() {
		query["supplierId"] = form.SupplierId
	}

	// 时间范围
	if len(form.TimeRange) == 2 {
		start, end := getTimeRange(form.TimeRange[0], form.TimeRange[1])
		query["updateTime"] = 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
	}

	// 获取采符合条件的信息
	produces := []model.ProduceBill{}
	err = repo.RepoDocsSearch(apictx.CreateRepoCtx(), &repo.PageSearchOptions{
		CollectName: repo.CollectionBillProduce,
		Query:       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,
	})
	offset := 0
	info := model.Setting{}
	repo.RepoSeachDoc(apictx.CreateRepoCtx(), &repo.DocSearchOptions{
		CollectName: "infos",
	}, &info)

	var budgetCount float64 = 0
	var realCount float64 = 0

	for _, produce := range produces {
		produceExcel := NewReportProduceExcel(f)
		produceExcel.Content = &produce
		budgetCount += produceExcel.BudgetCount
		realCount += produceExcel.RealCount
		produceExcel.Title = fmt.Sprintf("%ss加工单", info.CompanyName)
		//设置对应的数据
		produceExcel.Offset = offset
		produceExcel.Draws()
		offset += 15
	}
	row := offset + 1
	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("%.2f", 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("%.2f", 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="+"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) {
	var form ReportListReq
	err := c.ShouldBindJSON(&form)
	if err != nil {
		return nil, errors.New("参数错误")
	}
	// 条件处理
	query := make(map[string]interface{}, 0)
	query["status"] = "complete"
	if !form.SupplierId.IsZero() {
		query["supplierId"] = form.SupplierId
	}

	// 时间范围
	if len(form.TimeRange) == 2 {
		start, end := getTimeRange(form.TimeRange[0], form.TimeRange[1])
		query["updateTime"] = 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{}
	err = repo.RepoDocsSearch(apictx.CreateRepoCtx(), &repo.PageSearchOptions{
		CollectName: repo.CollectionBillPurchase,
		Query:       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,
	})
	offset := 0
	info := model.Setting{}
	repo.RepoSeachDoc(apictx.CreateRepoCtx(), &repo.DocSearchOptions{
		CollectName: "infos",
	}, &info)

	var budgetCount float64 = 0
	var realCount float64 = 0

	for _, purchase := range purchases {
		purchaseExcel := NewReportPurchaseExcel(f)
		purchaseExcel.Content = &purchase
		budgetCount += purchaseExcel.BudgetCount
		realCount += purchaseExcel.RealCount
		purchaseExcel.Title = fmt.Sprintf("%s原材料采购单", info.CompanyName)
		//设置对应的数据
		purchaseExcel.Offset = offset
		purchaseExcel.Draws()
		offset += 15
	}
	row := offset + 1
	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("%.2f", 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("%.2f", 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
}