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

// 加工单
func ReportProduceList(c *gin.Context, apictx *ApiSession) (interface{}, error) {
	// 财务管理】 添加统计报表功能。按 时间范围， 供应商(单选)   包装（多选） 计划（多选） 四个维度进行过滤，形成报表。可以下载
	page, size, query := UtilQueryPageSize(c)
	// 条件处理
	query["status"] = "complete"

	if _supplierId, ok := query["supplierId"]; ok {
		delete(query, "supplierId")
		supplierId, _ := primitive.ObjectIDFromHex(_supplierId.(string))
		if !supplierId.IsZero() {
			query["supplierId"] = supplierId

		}
	}

	if _timeRange, ok := query["timeRange"]; ok {
		timeRange, _ := _timeRange.([]interface{})

		if len(timeRange) == 2 {
			start, end := getTimeRange(timeRange[0].(string), timeRange[1].(string))
			query["updateTime"] = bson.M{"$gte": start, "$lte": end}
		}
		delete(query, "timeRange")
	}

	if _planIds, ok := query["planIds"]; ok {
		if len(_planIds.([]interface{})) > 0 {
			planQuery := bson.A{}
			for _, _planId := range _planIds.([]interface{}) {
				planId, _ := primitive.ObjectIDFromHex(_planId.(string))
				planQuery = append(planQuery, bson.M{"planId": planId})
			}
			query["$or"] = planQuery
		}
		delete(query, "planIds")
	}
	fmt.Println(query)

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

}

// 采购单
func ReportPurchaseList(c *gin.Context, apictx *ApiSession) (interface{}, error) {
	// 财务管理】 添加统计报表功能。按 时间范围， 供应商(单选) 计划（多选） 四个维度进行过滤，形成报表。可以下载
	page, size, query := UtilQueryPageSize(c)
	// 条件处理
	query["status"] = "complete"

	if _supplierId, ok := query["supplierId"]; ok {
		delete(query, "supplierId")
		supplierId, _ := primitive.ObjectIDFromHex(_supplierId.(string))
		if !supplierId.IsZero() {
			query["supplierId"] = supplierId

		}
	}

	if _timeRange, ok := query["timeRange"]; ok {
		timeRange, _ := _timeRange.([]interface{})

		if len(timeRange) == 2 {
			start, end := getTimeRange(timeRange[0].(string), timeRange[1].(string))
			query["updateTime"] = bson.M{"$gte": start, "$lte": end}
		}
		delete(query, "timeRange")
	}

	if _planIds, ok := query["planIds"]; ok {
		if len(_planIds.([]interface{})) > 0 {
			planQuery := bson.A{}
			for _, _planId := range _planIds.([]interface{}) {
				planId, _ := primitive.ObjectIDFromHex(_planId.(string))
				planQuery = append(planQuery, bson.M{"planId": planId})
			}
			query["$or"] = planQuery
		}
		delete(query, "planIds")
	}
	// 获取采购单符合条件的信息
	return repo.RepoPageSearch(apictx.CreateRepoCtx(), &repo.PageSearchOptions{
		CollectName: repo.CollectionBillPurchase,
		Query:       query,
		Page:        page,
		Size:        size,
	})

}

func ReportProduceDownload(c *gin.Context, apictx *ApiSession) (interface{}, error) {
	_, _, query := UtilQueryPageSize(c)
	// 条件处理
	query["status"] = "complete"

	if _supplierId, ok := query["supplierId"]; ok {
		delete(query, "supplierId")
		supplierId, _ := primitive.ObjectIDFromHex(_supplierId.(string))
		if !supplierId.IsZero() {
			query["supplierId"] = supplierId

		}
	}

	if _timeRange, ok := query["timeRange"]; ok {
		timeRange, _ := _timeRange.([]interface{})

		if len(timeRange) == 2 {
			start, end := getTimeRange(timeRange[0].(string), timeRange[1].(string))
			query["updateTime"] = bson.M{"$gte": start, "$lte": end}
		}
		delete(query, "timeRange")
	}

	if _planIds, ok := query["planIds"]; ok {
		if len(_planIds.([]interface{})) > 0 {
			planQuery := bson.A{}
			for _, _planId := range _planIds.([]interface{}) {
				planId, _ := primitive.ObjectIDFromHex(_planId.(string))
				planQuery = append(planQuery, bson.M{"planId": planId})
			}
			query["$or"] = planQuery
		}
		delete(query, "planIds")
	}

	// 获取采符合条件的信息
	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
	var row int = 0

	for _, produce := range produces {
		produceExcel := NewReportProduceExcel(f)
		produceExcel.Content = &produce

		produceExcel.Title = fmt.Sprintf("%s加工单", info.CompanyName)
		//设置对应的数据
		produceExcel.Offset = offset
		produceExcel.Draws()
		budgetCount += produceExcel.BudgetCount
		realCount += produceExcel.RealCount
		offset += 15
		row = produceExcel.Row

	}
	row++
	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) {
	_, _, query := UtilQueryPageSize(c)
	// 条件处理
	query["status"] = "complete"

	if _supplierId, ok := query["supplierId"]; ok {
		delete(query, "supplierId")
		supplierId, _ := primitive.ObjectIDFromHex(_supplierId.(string))
		if !supplierId.IsZero() {
			query["supplierId"] = supplierId
		}
	}

	if _timeRange, ok := query["timeRange"]; ok {
		timeRange, _ := _timeRange.([]interface{})

		if len(timeRange) == 2 {
			start, end := getTimeRange(timeRange[0].(string), timeRange[1].(string))
			query["updateTime"] = bson.M{"$gte": start, "$lte": end}
		}
		delete(query, "timeRange")
	}

	if _planIds, ok := query["planIds"]; ok {
		if len(_planIds.([]interface{})) > 0 {
			planQuery := bson.A{}
			for _, _planId := range _planIds.([]interface{}) {
				planId, _ := primitive.ObjectIDFromHex(_planId.(string))
				planQuery = append(planQuery, bson.M{"planId": planId})
			}
			query["$or"] = planQuery
		}
		delete(query, "planIds")
	}
	// 获取符合条件的信息
	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
	var row int = 0

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