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

	Title string //标题

	Excel *excelize.File

	SheetName string

	AlignCenterStyle int
	Row              int

	// Content *model.ProductPlan
	Content *SupplierPlanCost
}

func (b *PlanCostExcel) drawTitle() error {
	tileIndex := b.Offset + 1
	startCell := fmt.Sprintf("A%d", tileIndex)
	err := b.Excel.MergeCell(b.SheetName, startCell, fmt.Sprintf("M%d", tileIndex))
	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, tileIndex, 23)
	b.Excel.SetCellValue(b.SheetName, startCell, b.Title)
	return nil
}

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

	//A采购项目 B规格(克) 尺寸C-D 数量E 单价F-G 交货时间H 备注I
	// A产品名称
	var drawCol = func(prefix string, value string) error {
		left1Cell := fmt.Sprintf("%s%d", prefix, row)
		left2Cell := fmt.Sprintf("%s%d", prefix, 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 drawCol1 = func(prefix string, value1 string, value2 string) error {
	// 	topCell := fmt.Sprintf("%s%d", prefix, row)
	// 	bottomCell := fmt.Sprintf("%s%d", prefix, row+1)
	// 	b.Excel.SetCellStyle(b.SheetName, topCell, topCell, b.AlignCenterStyle)
	// 	b.Excel.SetCellValue(b.SheetName, topCell, value1)
	// 	b.Excel.SetCellStyle(b.SheetName, bottomCell, bottomCell, b.AlignCenterStyle)
	// 	b.Excel.SetCellValue(b.SheetName, bottomCell, value2)
	// 	return nil
	// }

	var drawCol2 = func(prefix1 string, prefix2 string, value1 string, value2 string, value3 string) error {
		left1Cell := fmt.Sprintf("%s%d", prefix1, row)
		left2Cell := fmt.Sprintf("%s%d", prefix2, 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, row+1)
		b.Excel.SetCellStyle(b.SheetName, val2Cel, val2Cel, b.AlignCenterStyle)
		b.Excel.SetCellValue(b.SheetName, val2Cel, value2)

		val3Cel := fmt.Sprintf("%s%d", prefix2, 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, row)
		// left2Cell := fmt.Sprintf("%s%d", prefix2, row)
		left3Cell := fmt.Sprintf("%s%d", prefix3, 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, row+1)
		b.Excel.SetCellStyle(b.SheetName, val2Cel, val2Cel, b.AlignCenterStyle)
		b.Excel.SetCellValue(b.SheetName, val2Cel, value2)

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

		val4Cel := fmt.Sprintf("%s%d", prefix3, 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", "供应商名称")
	drawCol3("E", "F", "G", "规格", "厚度(纸克)", "长", "宽")
	drawCol("H", "单位")
	drawCol("I", "下单数量")
	drawCol("J", "实际数量")
	drawCol("K", "单价")
	drawCol("L", "预算金额")
	drawCol("M", "实际金额")
	return nil
}

func (b *PlanCostExcel) drawSupplierContent() error {
	supplierId, err := primitive.ObjectIDFromHex(b.Content.SupplierId)
	if err != nil {
		return err
	}
	row := b.Offset + 4
	comps := b.Content.Pack.Components
	var totalPlanPrice float32 = 0.00
	var confirmTotalPlanPrice float64 = 0.00
	if len(comps) > 0 {
		for _, comp := range comps {
			var totalOrderRealPrice float32 = 0.00
			var totalConfirmRealPrice float64 = 0.00
			if len(comp.Mats) > 0 {
				startRow := 0
				for _, mat := range comp.Mats {
					if mat.Supplier.SupplierInfo != nil {
						if supplierId == mat.Supplier.SupplierInfo.Id {
							// 材料
							if startRow == 0 {
								startRow = row

							}

							supplierName := mat.Supplier.SupplierInfo.Name
							matHeigth := fmt.Sprintf("%d", mat.BatchSizeHeight)
							b.FormatToEmpty(&matHeigth)
							matWidth := fmt.Sprintf("%d", mat.BatchSizeWidth)
							b.FormatToEmpty(&matWidth)
							orderCount := fmt.Sprintf("%d", int(mat.Supplier.OrderCount))
							b.FormatToEmpty(&orderCount)

							// 实际数量
							confirmCount := fmt.Sprintf("%d", mat.ConfirmCount)
							b.FormatToEmpty(&confirmCount)
							// 单价
							orderPrice := fmt.Sprintf("%.2f", mat.Supplier.OrderPrice)
							b.FormatToEmpty(&orderPrice)
							totalOrderRealPrice += mat.Supplier.OrderRealPrice
							orderRealPrice := fmt.Sprintf("%.2f", mat.Supplier.OrderRealPrice)
							b.FormatToEmpty(&orderRealPrice)
							// 实际金额
							confirmPrice := mat.Supplier.OrderPrice * float64(mat.ConfirmCount)
							totalConfirmRealPrice += confirmPrice
							confirmRealPrice := fmt.Sprintf("%.2f", confirmPrice)
							b.FormatToEmpty(&confirmRealPrice)
							b.drawRow(row, "", mat.MatInfo.Name, "", supplierName, mat.MatInfo.Norm, matHeigth, matWidth, mat.MatInfo.Unit, orderCount, confirmCount, orderPrice, orderRealPrice, confirmRealPrice)
							row++
						}

					}

					if len(mat.Crafts) > 0 {
						for _, craft := range mat.Crafts {
							if craft.Supplier.SupplierInfo != nil { // 外箱材料报错
								// 工序
								if supplierId == craft.Supplier.SupplierInfo.Id {
									if startRow == 0 {
										startRow = row

									}
									craftSupplierName := craft.Supplier.SupplierInfo.Name
									carftOrderCount := fmt.Sprintf("%d", int(craft.Supplier.OrderCount))
									b.FormatToEmpty(&carftOrderCount)
									carftHeigth := fmt.Sprintf("%d", craft.BatchSizeHeight)
									b.FormatToEmpty(&carftHeigth)
									carftWidth := fmt.Sprintf("%d", craft.BatchSizeWidth)
									b.FormatToEmpty(&carftWidth)

									// 实际数量
									confirmCraftCount := fmt.Sprintf("%d", craft.ConfirmCount)
									b.FormatToEmpty(&confirmCraftCount)

									carftOrderPrice := fmt.Sprintf("%.2f", craft.Supplier.OrderPrice)
									b.FormatToEmpty(&carftOrderPrice)
									totalOrderRealPrice += craft.Supplier.OrderRealPrice
									carftOrderRealPrice := fmt.Sprintf("%.2f", craft.Supplier.OrderRealPrice)
									b.FormatToEmpty(&carftOrderRealPrice)

									// 实际金额
									confirmCraftPrice := craft.Supplier.OrderPrice * float64(craft.ConfirmCount)
									totalConfirmRealPrice += confirmCraftPrice
									confirmCraftRealPrice := fmt.Sprintf("%.2f", confirmCraftPrice)
									b.FormatToEmpty(&confirmCraftRealPrice)

									b.drawRow(row, "", "", craft.CraftInfo.Name, craftSupplierName, craft.CraftInfo.Norm, carftHeigth, carftWidth, craft.CraftInfo.Unit, carftOrderCount, confirmCraftCount, carftOrderPrice, carftOrderRealPrice, confirmCraftRealPrice)
									row++

								}

							}

						}
					}
				}
				if startRow != 0 {
					endRow := 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)

				}

			}
			totalPlanPrice += totalOrderRealPrice
			confirmTotalPlanPrice += totalConfirmRealPrice
		}

		// 生产汇总金额
		startACell := fmt.Sprintf("%s%d", "A", row)
		endKCell := fmt.Sprintf("%s%d", "K", row)
		LCell := fmt.Sprintf("%s%d", "L", row)
		MCell := fmt.Sprintf("%s%d", "M", row)
		b.Excel.MergeCell(b.SheetName, startACell, endKCell)
		b.Excel.SetCellStyle(b.SheetName, startACell, endKCell, b.AlignCenterStyle)
		b.Excel.SetCellValue(b.SheetName, startACell, "生产计划汇总金额")

		// 生产预算汇总
		b.Excel.SetCellStyle(b.SheetName, LCell, LCell, b.AlignCenterStyle)
		planTotalPrice := fmt.Sprintf("%.2f", totalPlanPrice)
		b.FormatToEmpty(&planTotalPrice)
		b.Excel.SetCellValue(b.SheetName, LCell, planTotalPrice)

		// 生产实际汇总
		b.Excel.SetCellStyle(b.SheetName, MCell, MCell, b.AlignCenterStyle)
		planConfirmTotalPrice := fmt.Sprintf("%.2f", confirmTotalPlanPrice)
		b.FormatToEmpty(&planConfirmTotalPrice)
		b.Excel.SetCellValue(b.SheetName, MCell, planConfirmTotalPrice)

	}

	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"}
	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) drawAllContent() error {
	row := b.Offset + 4
	comps := b.Content.Pack.Components
	var totalPlanPrice float32 = 0.00
	var confirmTotalPlanPrice float64 = 0.00
	if len(comps) > 0 {
		for _, comp := range comps {
			var totalConfirmRealPrice float64 = 0.00
			var totalOrderRealPrice float32 = 0.00
			if len(comp.Mats) > 0 {
				startRow := row
				for _, mat := range comp.Mats {
					// 材料
					supplierName := ""
					if mat.Supplier.SupplierInfo != nil {
						supplierName = mat.Supplier.SupplierInfo.Name
					}
					matHeigth := fmt.Sprintf("%d", mat.BatchSizeHeight)
					b.FormatToEmpty(&matHeigth)
					matWidth := fmt.Sprintf("%d", mat.BatchSizeWidth)
					b.FormatToEmpty(&matWidth)
					orderCount := fmt.Sprintf("%d", int(mat.Supplier.OrderCount))
					b.FormatToEmpty(&orderCount)

					// 实际数量
					confirmCount := fmt.Sprintf("%d", mat.ConfirmCount)
					b.FormatToEmpty(&confirmCount)
					// 单价
					orderPrice := fmt.Sprintf("%.2f", mat.Supplier.OrderPrice)
					b.FormatToEmpty(&orderPrice)
					orderRealPrice := fmt.Sprintf("%.2f", mat.Supplier.OrderRealPrice)
					totalOrderRealPrice += mat.Supplier.OrderRealPrice
					b.FormatToEmpty(&orderRealPrice)
					// 实际金额
					confirmPrice := mat.Supplier.OrderPrice * float64(mat.ConfirmCount)
					totalConfirmRealPrice += confirmPrice
					confirmRealPrice := fmt.Sprintf("%.2f", confirmPrice)
					b.FormatToEmpty(&confirmRealPrice)
					b.drawRow(row, "", mat.MatInfo.Name, "", supplierName, mat.MatInfo.Norm, matHeigth, matWidth, mat.MatInfo.Unit, orderCount, confirmCount, orderPrice, orderRealPrice, confirmRealPrice)

					row++
					if len(mat.Crafts) > 0 {
						// 工序
						for _, craft := range mat.Crafts {
							craftSupplierName := ""
							if craft.Supplier.SupplierInfo != nil { // 外箱材料报错
								craftSupplierName = craft.Supplier.SupplierInfo.Name

							}
							carftOrderCount := fmt.Sprintf("%d", int(craft.Supplier.OrderCount))
							b.FormatToEmpty(&carftOrderCount)
							carftHeigth := fmt.Sprintf("%d", craft.BatchSizeHeight)
							b.FormatToEmpty(&carftHeigth)
							carftWidth := fmt.Sprintf("%d", craft.BatchSizeWidth)
							b.FormatToEmpty(&carftWidth)

							// 实际数量
							confirmCraftCount := fmt.Sprintf("%d", craft.ConfirmCount)
							b.FormatToEmpty(&confirmCraftCount)

							carftOrderPrice := fmt.Sprintf("%.2f", craft.Supplier.OrderPrice)
							b.FormatToEmpty(&carftOrderPrice)
							carftOrderRealPrice := fmt.Sprintf("%.2f", craft.Supplier.OrderRealPrice)
							b.FormatToEmpty(&carftOrderRealPrice)

							// 实际金额
							confirmCraftPrice := craft.Supplier.OrderPrice * float64(craft.ConfirmCount)

							totalOrderRealPrice += craft.Supplier.OrderRealPrice
							totalConfirmRealPrice += confirmCraftPrice
							confirmCraftRealPrice := fmt.Sprintf("%.2f", confirmCraftPrice)
							b.FormatToEmpty(&confirmCraftRealPrice)

							b.drawRow(row, "", "", craft.CraftInfo.Name, craftSupplierName, craft.CraftInfo.Norm, carftHeigth, carftWidth, craft.CraftInfo.Unit, carftOrderCount, confirmCraftCount, carftOrderPrice, carftOrderRealPrice, confirmCraftRealPrice)

							row++

						}
					}
				}
				endRow := 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)
			}

			// 实际
			confirmTotalPlanPrice += totalConfirmRealPrice

			// 预算
			totalPlanPrice += totalOrderRealPrice
		}
	}
	if len(b.Content.Process) > 0 {
		// 生产汇总金额
		startACell := fmt.Sprintf("%s%d", "A", row)
		endMCell := fmt.Sprintf("%s%d", "K", row)
		b.Excel.MergeCell(b.SheetName, startACell, endMCell)
		b.Excel.SetCellStyle(b.SheetName, startACell, endMCell, b.AlignCenterStyle)
		b.Excel.SetCellValue(b.SheetName, startACell, "其他")
		row++
		// 工序 外箱 手工费 表头
		var drawCol = func(startCell, endCell string, value string) error {
			left1Cell := fmt.Sprintf("%s%d", startCell, row)
			left2Cell := fmt.Sprintf("%s%d", endCell, 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)
		}

		drawCol("B", "C", "材料/工序")
		drawCol("D", "D", "供应商名称")
		drawCol("E", "G", "规格")
		drawCol("H", "H", "单位")
		drawCol("I", "I", "下单数量")
		drawCol("J", "J", "实际数量")
		drawCol("K", "K", "单价")
		drawCol("L", "L", "预算金额")
		drawCol("M", "M", "实际金额")
		row++

		var DrawRow = func(rowIndex int, values ...string) {
			charas := []string{"B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M"}
			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)
			}
		}

		for _, ps := range b.Content.Process {
			count := fmt.Sprintf("%d", ps.Count)
			confirmCount := fmt.Sprintf("%d", ps.ConfirmCount)
			price := fmt.Sprintf("%.2f", ps.Price)
			b.FormatToEmpty(&price)
			budgetAmountf := ps.Price * float64(ps.Count)
			budgetAmount := fmt.Sprintf("%.2f", budgetAmountf)
			b.FormatToEmpty(&budgetAmount)
			realAmountf := ps.Price * float64(ps.ConfirmCount)
			realAmount := fmt.Sprintf("%.2f", realAmountf)
			b.FormatToEmpty(&realAmount)
			confirmTotalPlanPrice += realAmountf
			totalPlanPrice += float32(budgetAmountf)

			DrawRow(row, ps.Name, ps.Supplier, ps.Norm, ps.Unit, count, confirmCount, price, budgetAmount, realAmount)
			row++
		}

	}
	// 生产汇总金额
	startACell := fmt.Sprintf("%s%d", "A", row)
	endKCell := fmt.Sprintf("%s%d", "K", row)
	LCell := fmt.Sprintf("%s%d", "L", row)
	MCell := fmt.Sprintf("%s%d", "M", row)
	b.Excel.MergeCell(b.SheetName, startACell, endKCell)
	b.Excel.SetCellStyle(b.SheetName, startACell, endKCell, b.AlignCenterStyle)
	b.Excel.SetCellValue(b.SheetName, startACell, "生产计划汇总金额")

	// 生产预算汇总
	b.Excel.SetCellStyle(b.SheetName, LCell, LCell, b.AlignCenterStyle)
	// planTotalPrice := fmt.Sprintf("%.2f", b.Content.TotalPrice) //
	planTotalPrice := fmt.Sprintf("%.2f", totalPlanPrice) //
	b.FormatToEmpty(&planTotalPrice)
	b.Excel.SetCellValue(b.SheetName, LCell, planTotalPrice)

	// 生产实际汇总
	b.Excel.SetCellStyle(b.SheetName, MCell, MCell, b.AlignCenterStyle)
	planConfirmTotalPrice := fmt.Sprintf("%.2f", confirmTotalPlanPrice)
	b.FormatToEmpty(&planConfirmTotalPrice)
	b.Excel.SetCellValue(b.SheetName, MCell, planConfirmTotalPrice)

	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", "D", 12)
	f.SetColWidth(b.SheetName, "E", "M", 10)
	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.00" {
		*str = ""
	}

}