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

package api

import (
	"fmt"

	"github.com/xuri/excelize/v2"
)

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

	Title string //标题

	Excel *excelize.File

	SheetName string

	AlignCenterStyle int

	// Content *model.ProductPlan
	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("M%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", "供应商名称")
	drawCol3("E", "F", "G", "规格", "厚度(纸克)", "长", "宽")
	drawCol("H", "单位")
	drawCol("I", "下单数量")
	drawCol("J", "实际数量")
	drawCol("K", "单价")
	drawCol("L", "预算金额")
	drawCol("M", "实际金额")
	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) drawSupplierContent() error {
// 	b.Row += 2
// 	supplierId, err := primitive.ObjectIDFromHex(b.Content.SupplierId)
// 	if err != nil {
// 		return err
// 	}
// 	comps := b.Content.Pack.Components
// 	// 预算金额汇总
// 	var totalOrderRealPrice float64 = 0.00
// 	// 实际金额汇总
// 	var totalRealPrice float64 = 0.00
// 	if len(comps) > 0 {
// 		for _, comp := range comps {
// 			var perOrderRealPrice float64 = 0.00
// 			var perRealPrice float64 = 0.00

// 			if len(comp.Stages) > 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 := ""
// 							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("%.3f", mat.Supplier.OrderPrice)
// 							b.FormatToEmpty(&orderPrice)
// 							// 预算金额
// 							orderRealPrice := fmt.Sprintf("%.3f", mat.Supplier.OrderRealPrice)
// 							perOrderRealPrice += mat.Supplier.OrderRealPrice
// 							b.FormatToEmpty(&orderRealPrice)
// 							// 实际金额
// 							perRealPrice += mat.RealPrice
// 							realPrice := fmt.Sprintf("%.3f", mat.RealPrice)
// 							b.FormatToEmpty(&realPrice)
// 							if mat.MatInfo.Unit == "吨" || mat.MatInfo.Unit == "平方米" {
// 								mat.MatInfo.Unit = "张"
// 							}

// 							b.drawRow(row, "", mat.MatInfo.Name, "", supplierName, mat.MatInfo.Norm, matHeigth, matWidth, mat.MatInfo.Unit, orderCount, confirmCount, orderPrice, orderRealPrice, realPrice)

// 							row++
// 						}

// 					}

// 					if len(mat.Crafts) > 0 {
// 						// 实际数量、预算数量
// 						// mergeStartRow := row
// 						// mergeEndRow := row
// 						cates := map[string]MergeRow{}
// 						for _, craft := range mat.Crafts {
// 							if craft.Supplier.SupplierInfo != nil { // 外箱材料报错
// 								// 工序
// 								if supplierId == craft.Supplier.SupplierInfo.Id {
// 									if startRow == 0 {
// 										startRow = row

// 									}

// 									supplierName := ""
// 									if craft.Supplier.SupplierInfo != nil {
// 										supplierName = craft.Supplier.SupplierInfo.Name
// 									}

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

// 									// 实际数量
// 									confirmCount := fmt.Sprintf("%d", craft.ConfirmCount)
// 									b.FormatToEmpty(&confirmCount)
// 									price := fmt.Sprintf("%.3f", craft.Supplier.OrderPrice)
// 									b.FormatToEmpty(&price)

// 									// 预算金额
// 									budgetAmount := ""
// 									b.FormatToEmpty(&budgetAmount)

// 									// 实际金额 在外层合并单元格
// 									realAmount := ""

// 									if craft.CraftInfo.Unit == "吨" || craft.CraftInfo.Unit == "平方米" {
// 										craft.CraftInfo.Unit = "张"
// 									}

// 									b.drawRow(row, "", "", craft.CraftInfo.Name, supplierName, craft.CraftInfo.Norm, carftHeigth, carftWidth, craft.CraftInfo.Unit, orderCount, confirmCount, price, budgetAmount, realAmount)
// 									// mergeEndRow = row
// 									category := craft.CraftInfo.Category
// 									cates[category] = MergeRow{
// 										Rows:         append(cates[category].Rows, row),
// 										RealAmount:   craft.RealPrice,
// 										BudgetAmount: craft.Supplier.OrderRealPrice,
// 									}

// 									row++
// 								}

// 							}

// 						}

// 						fmt.Println(cates)
// 						for _, cate := range cates {
// 							mergeStartRow := cate.Rows[0]
// 							mergeEndRow := cate.Rows[len(cate.Rows)-1]
// 							orderRealPrice := cate.BudgetAmount
// 							realPrice := cate.RealAmount
// 							b.Excel.MergeCell(b.SheetName, fmt.Sprintf("L%d", mergeStartRow), fmt.Sprintf("L%d", mergeEndRow))
// 							b.Excel.SetCellValue(b.SheetName, fmt.Sprintf("L%d", mergeEndRow), orderRealPrice)
// 							b.Excel.MergeCell(b.SheetName, fmt.Sprintf("M%d", mergeStartRow), fmt.Sprintf("M%d", mergeEndRow))
// 							b.Excel.SetCellValue(b.SheetName, fmt.Sprintf("M%d", mergeEndRow), realPrice)
// 							perRealPrice += realPrice
// 							perOrderRealPrice += orderRealPrice
// 						}
// 						// 多个工序 合并预算价格、实际价格
// 						// b.Excel.MergeCell(b.SheetName, fmt.Sprintf("L%d", mergeStartRow), fmt.Sprintf("L%d", mergeEndRow))
// 						// b.Excel.SetCellValue(b.SheetName, fmt.Sprintf("L%d", mergeEndRow), mat.Crafts[0].Supplier.OrderRealPrice)
// 						// b.Excel.MergeCell(b.SheetName, fmt.Sprintf("M%d", mergeStartRow), fmt.Sprintf("M%d", mergeEndRow))
// 						// b.Excel.SetCellValue(b.SheetName, fmt.Sprintf("M%d", mergeEndRow), mat.Crafts[0].RealPrice)
// 						// perRealPrice += mat.Crafts[0].RealPrice
// 						// perOrderRealPrice += mat.Crafts[0].Supplier.OrderRealPrice

// 					}
// 				}
// 				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)

// 				}

// 			}
// 			// 预算
// 			totalOrderRealPrice += perOrderRealPrice
// 			// 预算
// 			totalRealPrice += perRealPrice
// 		}

// 	}

// 	// 工序数据
// 	if b.Content.Process != nil {
// 		if len(b.Content.Process) > 0 {
// 			isSupplier := false
// 			for _, ps := range b.Content.Process {
// 				if supplierId == ps.Supplier.SupplierInfo.Id {
// 					isSupplier = true
// 					break
// 				}
// 			}

// 			if isSupplier {
// 				// 生产汇总金额
// 				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)
// 					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("A", "A", "")
// 				drawCol("B", "C", "材料/工序")
// 				drawCol("D", "E", "供应商名称")
// 				drawCol("F", "G", "规格")
// 				drawCol("H", "H", "单位")
// 				drawCol("I", "I", "下单数量")
// 				drawCol("J", "J", "实际数量")
// 				drawCol("K", "K", "单价")
// 				drawCol("L", "L", "预算金额")
// 				drawCol("M", "M", "实际金额")
// 				row++
// 				if len(b.Content.Process) > 0 {
// 					for _, ps := range b.Content.Process {
// 						orderCount := fmt.Sprintf("%.3f", ps.Supplier.OrderCount)
// 						confirmCount := fmt.Sprintf("%d", ps.ConfirmCount)
// 						price := fmt.Sprintf("%.3f", ps.Supplier.OrderPrice)
// 						b.FormatToEmpty(&price)
// 						totalOrderRealPrice += ps.Supplier.OrderRealPrice
// 						totalRealPrice += ps.RealPrice
// 						orderRealPrice := fmt.Sprintf("%.3f", ps.Supplier.OrderRealPrice)
// 						b.FormatToEmpty(&orderRealPrice)
// 						realPrice := fmt.Sprintf("%.3f", ps.RealPrice)
// 						supplierName := ""
// 						if ps.Supplier.SupplierInfo != nil {
// 							supplierName = ps.Supplier.SupplierInfo.Name
// 						}

// 						b.drawRow(row, "", "", "", "", "", "", "", ps.ProcessInfo.Unit, orderCount, confirmCount, price, orderRealPrice, realPrice)

// 						b.Excel.MergeCell(b.SheetName, fmt.Sprintf("%s%d", "B", row), fmt.Sprintf("%s%d", "C", row))
// 						b.Excel.SetCellValue(b.SheetName, fmt.Sprintf("%s%d", "B", row), ps.ProcessInfo.Name)

// 						b.Excel.MergeCell(b.SheetName, fmt.Sprintf("%s%d", "D", row), fmt.Sprintf("%s%d", "E", row))
// 						b.Excel.SetCellValue(b.SheetName, fmt.Sprintf("%s%d", "D", row), supplierName)

// 						b.Excel.MergeCell(b.SheetName, fmt.Sprintf("%s%d", "F", row), fmt.Sprintf("%s%d", "G", row))
// 						b.Excel.SetCellValue(b.SheetName, fmt.Sprintf("%s%d", "F", row), ps.ProcessInfo.Norm)

// 						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)
// 	planOrderRealPrice := fmt.Sprintf("%.3f", totalOrderRealPrice)
// 	b.FormatToEmpty(&planOrderRealPrice)
// 	b.Excel.SetCellValue(b.SheetName, LCell, planOrderRealPrice)

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

// 	return nil
// }

func (b *PlanCostExcel) drawAllContent() error {
	b.Row += 2
	comps := b.Content.Pack.Components
	// 预算金额汇总
	var totalOrderPrice float64 = 0.00
	// 实际金额汇总
	var totalRealPrice float64 = 0.00
	if len(comps) > 0 {
		for _, comp := range comps {
			var perOrderPrice float64 = 0.00
			var perRealPrice float64 = 0.00
			if len(comp.Stages) > 0 {
				startRow := b.Row
				for _, stage := range comp.Stages {
					// 材料
					supplierName := ""
					if stage.SupplierInfo != nil {
						supplierName = stage.SupplierInfo.Name
					}
					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.RealCount)
					b.FormatToEmpty(&realCount)
					// 单价
					price := fmt.Sprintf("%.3f", stage.Price)
					b.FormatToEmpty(&price)
					// 预算金额
					orderPrice := fmt.Sprintf("%.3f", stage.OrderPrice)
					perOrderPrice += stage.OrderPrice
					b.FormatToEmpty(&orderPrice)
					// 实际金额
					perRealPrice += stage.RealPrice
					realPrice := fmt.Sprintf("%.3f", stage.RealPrice)
					b.FormatToEmpty(&realPrice)
					if stage.Unit == "吨" || stage.Unit == "平方米" {
						stage.Unit = "张"
					}
					b.drawRow(b.Row, "", stage.Name, "", supplierName, stage.Norm, matHeigth, matWidth, stage.Unit, orderCount, realCount, price, orderPrice, realPrice)

					b.Row++

				}
				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)
			}

			// 预算
			totalOrderPrice += perOrderPrice
			// 实际金额
			totalRealPrice += perRealPrice
		}
	}
	startACell := fmt.Sprintf("%s%d", "A", b.Row)
	endKCell := fmt.Sprintf("%s%d", "K", b.Row)
	LCell := fmt.Sprintf("%s%d", "L", b.Row)
	MCell := fmt.Sprintf("%s%d", "M", b.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)
	planOrderRealPrice := fmt.Sprintf("%.3f", totalOrderPrice)
	b.FormatToEmpty(&planOrderRealPrice)
	b.Excel.SetCellValue(b.SheetName, LCell, planOrderRealPrice)

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

	return nil
}

func (b *PlanCostExcel) Draws() {
	b.drawTitle()
	b.drawTableTitle()
	// if b.Content.SupplierId != "" {
	// 	b.drawSupplierContent()
	// } else {
	// 	b.drawAllContent()
	// }
	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.000" {
		*str = ""
	}

}