box-cost/boxcost/api/plan-cost-excel.go

package api

import (
	"fmt"

	"github.com/xuri/excelize/v2"
	"go.mongodb.org/mongo-driver/bson/primitive"
)

// 生产成本表
type PlanCostExcel struct {
	Offset int
	Row    int

	Title string //标题

	Excel *excelize.File

	SheetName string

	AlignCenterStyle int

	Content *SupplierPlanCost
}

func (b *PlanCostExcel) drawTitle() error {
	// tileIndex := b.Offset + 1
	b.Row++
	startCell := fmt.Sprintf("A%d", b.Row)
	err := b.Excel.MergeCell(b.SheetName, startCell, fmt.Sprintf("N%d", b.Row))
	if err != nil {
		return err
	}

	style, err := b.Excel.NewStyle(&excelize.Style{
		Alignment: &excelize.Alignment{Horizontal: "center", Vertical: "center"},
		Font:      &excelize.Font{Bold: true, Size: 18}})
	if err != nil {
		return err
	}
	err = b.Excel.SetCellStyle(b.SheetName, startCell, startCell, style)
	if err != nil {
		return err
	}
	b.Excel.SetRowHeight(b.SheetName, b.Row, 23)
	b.Excel.SetCellValue(b.SheetName, startCell, b.Title)
	return nil
}

func (b *PlanCostExcel) drawTableTitle() error {
	// row := b.Offset + 2
	b.Row++

	//A采购项目 B规格(克) 尺寸C-D 数量E 单价F-G 交货时间H 备注I
	// A产品名称
	var drawCol = func(prefix string, value string) error {
		left1Cell := fmt.Sprintf("%s%d", prefix, b.Row)
		left2Cell := fmt.Sprintf("%s%d", prefix, b.Row+1)

		err := b.Excel.MergeCell(b.SheetName, left1Cell, left2Cell)
		if err != nil {
			return err
		}
		err = b.Excel.SetCellStyle(b.SheetName, left1Cell, left2Cell, b.AlignCenterStyle)
		if err != nil {
			return err
		}

		return b.Excel.SetCellValue(b.SheetName, left1Cell, value)
	}

	var drawCol2 = func(prefix1 string, prefix2 string, value1 string, value2 string, value3 string) error {
		left1Cell := fmt.Sprintf("%s%d", prefix1, b.Row)
		left2Cell := fmt.Sprintf("%s%d", prefix2, b.Row)
		err := b.Excel.MergeCell(b.SheetName, left1Cell, left2Cell)
		if err != nil {
			return err
		}
		if err != nil {
			fmt.Println(err)
			return err
		}
		err = b.Excel.SetCellStyle(b.SheetName, left1Cell, left2Cell, b.AlignCenterStyle)
		if err != nil {
			return err
		}
		b.Excel.SetCellValue(b.SheetName, left1Cell, value1)

		val2Cel := fmt.Sprintf("%s%d", prefix1, b.Row+1)
		b.Excel.SetCellStyle(b.SheetName, val2Cel, val2Cel, b.AlignCenterStyle)
		b.Excel.SetCellValue(b.SheetName, val2Cel, value2)

		val3Cel := fmt.Sprintf("%s%d", prefix2, b.Row+1)
		b.Excel.SetCellStyle(b.SheetName, val3Cel, val3Cel, b.AlignCenterStyle)
		b.Excel.SetCellValue(b.SheetName, val3Cel, value3)
		return nil
	}
	var drawCol3 = func(prefix1 string, prefix2 string, prefix3 string, value1 string, value2 string, value3 string, value4 string) error {
		left1Cell := fmt.Sprintf("%s%d", prefix1, b.Row)
		// left2Cell := fmt.Sprintf("%s%d", prefix2, row)
		left3Cell := fmt.Sprintf("%s%d", prefix3, b.Row)
		err := b.Excel.MergeCell(b.SheetName, left1Cell, left3Cell)
		if err != nil {
			return err
		}
		if err != nil {
			fmt.Println(err)
			return err
		}
		err = b.Excel.SetCellStyle(b.SheetName, left1Cell, left3Cell, b.AlignCenterStyle)
		if err != nil {
			return err
		}
		b.Excel.SetCellValue(b.SheetName, left1Cell, value1)

		val2Cel := fmt.Sprintf("%s%d", prefix1, b.Row+1)
		b.Excel.SetCellStyle(b.SheetName, val2Cel, val2Cel, b.AlignCenterStyle)
		b.Excel.SetCellValue(b.SheetName, val2Cel, value2)

		val3Cel := fmt.Sprintf("%s%d", prefix2, b.Row+1)
		b.Excel.SetCellStyle(b.SheetName, val3Cel, val3Cel, b.AlignCenterStyle)
		b.Excel.SetCellValue(b.SheetName, val3Cel, value3)

		val4Cel := fmt.Sprintf("%s%d", prefix3, b.Row+1)
		b.Excel.SetCellStyle(b.SheetName, val4Cel, val4Cel, b.AlignCenterStyle)
		b.Excel.SetCellValue(b.SheetName, val4Cel, value4)
		return nil
	}

	drawCol("A", "产品部件名称")
	drawCol2("B", "C", "类型/项目", "类型", "项目")
	drawCol("D", "供应商名称")
	drawCol("E", "单价1")
	drawCol3("F", "G", "H", "规格", "厚度(纸克)", "长", "宽")
	drawCol("I", "单位")
	drawCol("J", "下单数量")
	drawCol("K", "实际数量")
	drawCol("L", "单价") // ??? 下单单价
	drawCol("M", "预算金额")
	drawCol("N", "实际金额")
	return nil
}

func (b *PlanCostExcel) drawRow(rowIndex int, values ...string) {
	charas := []string{"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N"}
	for i, c := range charas {
		v := ""
		if i < len(values) {
			v = values[i]
		}
		b.Excel.SetCellValue(b.SheetName, fmt.Sprintf("%s%d", c, rowIndex), v)

		val2Cel := fmt.Sprintf("%s%d", c, rowIndex)
		b.Excel.SetCellStyle(b.SheetName, val2Cel, val2Cel, b.AlignCenterStyle)

		b.Excel.SetRowHeight(b.SheetName, rowIndex, 21)
	}

}

func (b *PlanCostExcel) drawSupplierContent() error {
	b.Row += 2
	supplierId, err := primitive.ObjectIDFromHex(b.Content.SupplierId)
	if err != nil {
		return err
	}
	supplier := ""
	comps := b.Content.Pack.Components
	// 预算金额汇总
	var totalBudgetPrice float64 = 0.00
	// 实际金额汇总
	var totalRealPrice float64 = 0.00
	if len(comps) > 0 {
		for _, comp := range comps {
			var perBudgetPrice float64 = 0.00
			var perRealPrice float64 = 0.00

			if len(comp.Stages) > 0 {
				startRow := 0
				cates := map[string][]int{}
				for _, stage := range comp.Stages {
					if stage.SupplierInfo != nil {
						if supplierId == stage.SupplierInfo.Id {
							supplier = stage.SupplierInfo.Name
							// 材料
							if startRow == 0 {
								startRow = b.Row

							}

							matHeigth := fmt.Sprintf("%d", stage.BatchSizeHeight)
							b.FormatToEmpty(&matHeigth)
							matWidth := fmt.Sprintf("%d", stage.BatchSizeWidth)
							b.FormatToEmpty(&matWidth)
							orderCount := fmt.Sprintf("%d", int(stage.OrderCount))
							b.FormatToEmpty(&orderCount)

							// 实际数量
							realCount := fmt.Sprintf("%d", stage.ConfirmCount)
							b.FormatToEmpty(&realCount)
							// 单价
							price := fmt.Sprintf("%.3f", stage.OrderPrice)
							b.FormatToEmpty(&price)
							// 预算金额
							budgetPrice := fmt.Sprintf("%.3f", stage.OrderPrice*float64(stage.OrderCount))
							perBudgetPrice += stage.OrderPrice * float64(stage.OrderCount)
							b.FormatToEmpty(&budgetPrice)
							// 实际金额
							perRealPrice += stage.OrderPrice * float64(stage.ConfirmCount)
							realPrice := fmt.Sprintf("%.3f", stage.OrderPrice*float64(stage.ConfirmCount))
							b.FormatToEmpty(&realPrice)
							unit := stage.Unit
							if stage.Unit == "吨" || stage.Unit == "平方米" {
								unit = "张"
							}

							// 生成单据时已billType为准
							stageType := ""
							if stage.BillType > 0 {
								stage.Type = stage.BillType
							}
							if stage.Type == 1 {
								stageType = "材料采购"
							}
							if stage.Type == 2 {
								stageType = "工艺"
							}
							if stage.Type == 3 {
								stageType = "成品采购"
							}
							b.drawRow(b.Row, "", stageType, stage.Name, "", fmt.Sprintf("%.3f元/%s", stage.Price, stage.Unit), stage.Norm, matHeigth, matWidth, unit, orderCount, realCount, price, budgetPrice, realPrice)
							cates[stage.SupplierInfo.Name] = append(cates[stage.SupplierInfo.Name], b.Row)
							b.Row++
						}

					}
				}

				// 合并同一供应商名
				for supplierName, cate := range cates {
					mergeStartRow := cate[0]
					mergeEndRow := cate[len(cate)-1]
					b.Excel.MergeCell(b.SheetName, fmt.Sprintf("D%d", mergeStartRow), fmt.Sprintf("D%d", mergeEndRow))
					b.Excel.SetCellValue(b.SheetName, fmt.Sprintf("D%d", mergeEndRow), supplierName)
				}
				if startRow != 0 {
					endRow := b.Row - 1
					// 组件名字
					startACell := fmt.Sprintf("%s%d", "A", startRow)
					endACell := fmt.Sprintf("%s%d", "A", endRow)
					b.Excel.MergeCell(b.SheetName, startACell, endACell)

					err := b.Excel.SetCellStyle(b.SheetName, startACell, endACell, b.AlignCenterStyle)
					if err != nil {
						return err
					}
					b.Excel.SetCellValue(b.SheetName, startACell, comp.Name)

				}

			}
			// 预算
			totalBudgetPrice += perBudgetPrice
			// 预算
			totalRealPrice += perRealPrice
		}

	}

	// 生产汇总金额
	startACell := fmt.Sprintf("%s%d", "A", b.Row)
	endLCell := fmt.Sprintf("%s%d", "L", b.Row)
	MCell := fmt.Sprintf("%s%d", "M", b.Row)
	NCell := fmt.Sprintf("%s%d", "N", b.Row)
	b.Excel.MergeCell(b.SheetName, startACell, endLCell)
	b.Excel.SetCellStyle(b.SheetName, startACell, endLCell, b.AlignCenterStyle)
	b.Excel.SetCellValue(b.SheetName, startACell, fmt.Sprintf("【%s】生产计划汇总金额", supplier))

	// 生产预算汇总
	b.Excel.SetCellStyle(b.SheetName, MCell, MCell, b.AlignCenterStyle)
	planOrderRealPrice := fmt.Sprintf("%.3f", totalBudgetPrice)
	b.FormatToEmpty(&planOrderRealPrice)
	b.Excel.SetCellValue(b.SheetName, MCell, planOrderRealPrice)

	// 生产实际汇总
	b.Excel.SetCellStyle(b.SheetName, NCell, NCell, b.AlignCenterStyle)
	planRealPrice := fmt.Sprintf("%.3f", totalRealPrice)
	b.FormatToEmpty(&planRealPrice)
	b.Excel.SetCellValue(b.SheetName, NCell, planRealPrice)

	return nil
}

// type merge

func (b *PlanCostExcel) drawAllContent() error {
	b.Row += 2
	comps := b.Content.Pack.Components
	// 预算金额汇总
	var totalBudgetPrice float64 = 0.00
	// 实际金额汇总
	var totalRealPrice float64 = 0.00
	if len(comps) > 0 {
		for _, comp := range comps {
			var perBudgetPrice float64 = 0.00
			var perRealPrice float64 = 0.00
			if len(comp.Stages) > 0 {
				startRow := b.Row
				cates := map[string][]int{}
				for _, stage := range comp.Stages {
					matHeigth := fmt.Sprintf("%d", stage.BatchSizeHeight)
					b.FormatToEmpty(&matHeigth)
					matWidth := fmt.Sprintf("%d", stage.BatchSizeWidth)
					b.FormatToEmpty(&matWidth)
					orderCount := fmt.Sprintf("%d", int(stage.OrderCount))
					b.FormatToEmpty(&orderCount)

					// 实际数量
					realCount := fmt.Sprintf("%d", stage.ConfirmCount)
					b.FormatToEmpty(&realCount)
					// 单价
					price := fmt.Sprintf("%.3f", stage.OrderPrice)
					b.FormatToEmpty(&price)
					// 预算金额
					budgetPrice := fmt.Sprintf("%.3f", stage.OrderPrice*float64(stage.OrderCount))
					perBudgetPrice += stage.OrderPrice * float64(stage.OrderCount)
					b.FormatToEmpty(&budgetPrice)
					// 实际金额
					perRealPrice += stage.OrderPrice * float64(stage.ConfirmCount)
					realPrice := fmt.Sprintf("%.3f", stage.OrderPrice*float64(stage.ConfirmCount))
					b.FormatToEmpty(&realPrice)
					unit := stage.Unit
					if stage.Unit == "吨" || stage.Unit == "平方米" {
						unit = "张"
					}

					supplierName := ""
					if stage.SupplierInfo != nil {
						supplierName = stage.SupplierInfo.Name
					}
					stageType := ""
					if stage.BillType > 0 {
						stage.Type = stage.BillType
					}
					if stage.Type == 1 {
						stageType = "材料采购"
					}
					if stage.Type == 2 {
						stageType = "工艺"
					}
					if stage.Type == 3 {
						stageType = "成品采购"
					}
					b.drawRow(b.Row, "", stageType, stage.Name, supplierName, fmt.Sprintf("%.3f元/%s", stage.Price, stage.Unit), stage.Norm, matHeigth, matWidth, unit, orderCount, realCount, price, budgetPrice, realPrice)
					if stage.SupplierInfo != nil {
						cates[stage.SupplierInfo.Name] = append(cates[stage.SupplierInfo.Name], b.Row)

					}

					b.Row++

				}

				for supplierName, cate := range cates {
					mergeStartRow := cate[0]
					mergeEndRow := cate[len(cate)-1]
					b.Excel.MergeCell(b.SheetName, fmt.Sprintf("D%d", mergeStartRow), fmt.Sprintf("D%d", mergeEndRow))
					b.Excel.SetCellValue(b.SheetName, fmt.Sprintf("D%d", mergeEndRow), supplierName)
				}

				endRow := b.Row - 1
				// 组件名字
				startACell := fmt.Sprintf("%s%d", "A", startRow)
				endACell := fmt.Sprintf("%s%d", "A", endRow)
				b.Excel.MergeCell(b.SheetName, startACell, endACell)
				err := b.Excel.SetCellStyle(b.SheetName, startACell, endACell, b.AlignCenterStyle)
				if err != nil {
					return err
				}
				b.Excel.SetCellValue(b.SheetName, startACell, comp.Name)
			}

			// 预算
			totalBudgetPrice += perBudgetPrice
			// 实际金额
			totalRealPrice += perRealPrice
		}
	}
	startACell := fmt.Sprintf("%s%d", "A", b.Row)
	endLCell := fmt.Sprintf("%s%d", "L", b.Row)
	MCell := fmt.Sprintf("%s%d", "M", b.Row)
	NCell := fmt.Sprintf("%s%d", "N", b.Row)
	b.Excel.MergeCell(b.SheetName, startACell, endLCell)
	b.Excel.SetCellStyle(b.SheetName, startACell, endLCell, b.AlignCenterStyle)
	b.Excel.SetCellValue(b.SheetName, startACell, "生产计划汇总金额")

	// 生产预算汇总
	b.Excel.SetCellStyle(b.SheetName, MCell, MCell, b.AlignCenterStyle)
	planOrderRealPrice := fmt.Sprintf("%.3f", totalBudgetPrice)
	b.FormatToEmpty(&planOrderRealPrice)
	b.Excel.SetCellValue(b.SheetName, MCell, planOrderRealPrice)

	// 生产实际汇总
	b.Excel.SetCellStyle(b.SheetName, NCell, NCell, b.AlignCenterStyle)
	planRealPrice := fmt.Sprintf("%.3f", totalRealPrice)
	b.FormatToEmpty(&planRealPrice)
	b.Excel.SetCellValue(b.SheetName, NCell, planRealPrice)

	return nil
}

func (b *PlanCostExcel) Draws() {
	b.drawTitle()
	b.drawTableTitle()
	if b.Content.SupplierId != "" {
		b.drawSupplierContent()
	} else {
		b.drawAllContent()
	}

}

func NewPlanCostExcel(f *excelize.File) *PlanCostExcel {

	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"},
	}

	styleLeft, _ := f.NewStyle(&excelize.Style{
		Alignment: &excelize.Alignment{Horizontal: "center", Vertical: "center"},
		Border:    border,
		Font:      &excelize.Font{Size: 10},
	})

	b := &PlanCostExcel{
		Title:            "生产成本表",
		SheetName:        "Sheet1",
		Excel:            f,
		Offset:           0,
		AlignCenterStyle: styleLeft,
	}

	f.SetColWidth(b.SheetName, "A", "B", 12)
	f.SetColWidth(b.SheetName, "C", "C", 16)
	f.SetColWidth(b.SheetName, "D", "D", 25)
	f.SetColWidth(b.SheetName, "E", "E", 15)
	f.SetColWidth(b.SheetName, "F", "F", 16)
	f.SetColWidth(b.SheetName, "F", "N", 12)
	f.SetPageMargins(b.SheetName, excelize.PageMarginTop(0), excelize.PageMarginLeft(0), excelize.PageMarginRight(0))
	return b
}

func (b *PlanCostExcel) FormatToEmpty(str *string) {
	if *str == "0" || *str == "0.000" {
		*str = ""
	}

}