box-cost/boxcost/api/report.go

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) {
	page, size, 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, 20*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 repo.PageResult{
			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)
		}
	}
	f := excelize.NewFile()
	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{"supplierId": 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, 20*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{
			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
	}
	// var start int64 = 0
	// var stop int64 = 9
	// if size < 1 {
	// 	return start, stop
	// }

	// if page > 0 {
	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
}