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

package api

import (
	"fmt"

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

// 生产成本表
type PlanCostExcel struct {
	Row              int
	Title            string
	Excel            *excelize.File
	SheetName        string
	AlignCenterStyle int
	Content          *SupplierPlanSummary
}

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

	style, err := b.Excel.NewStyle(&excelize.Style{
		Alignment: &excelize.Alignment{Horizontal: "center", Vertical: "center", WrapText: true},
		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 {
	b.Row++
	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)
		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", "产品名称")
	drawCol("A", "产品部件名称")
	drawCol2("B", "C", "类型/项目", "类型", "项目")
	drawCol("D", "下单数量")
	drawCol("E", "实际数量")
	drawCol("F", "状态") // 生成订单状态 审核状态 发送状态 完成状态
	drawCol("G", "供应商名称")
	drawCol("H", "单价")
	drawCol3("I", "J", "K", "规格", "厚度(纸克)", "长", "宽")
	drawCol("L", "单位")
	drawCol("M", "下单单价")
	drawCol("N", "预算金额")
	drawCol("O", "实际金额")
	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", "O"}
	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, 32)
	}

}

func (b *PlanCostExcel) drawAllContent() error {
	b.Row += 2

	// summaryPlans
	// 预算金额汇总
	var totalBudgetPrice float64 = 0.00
	// 实际金额汇总
	var totalRealPrice float64 = 0.00

	for _, splan := range b.Content.Plans {
		plan := splan.Plan
		comps := plan.Pack.Components
		// 预算金额汇总
		var planBudgetPrice float64 = 0.00
		// 实际金额汇总
		var planRealPrice 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 = "成品采购"
						}

						// 状态
						stageStatus := ""

						if len(stage.BillId) < 1 {
							stageStatus = "未生成订单"
						} else {
							if splan.State[stage.BillId] == "created" {
								stageStatus = "进行中"
								// 审核状态
								if splan.Reviewed[stage.BillId] == 1 {
									stageStatus = "已审核"
									if splan.IsSend[stage.BillId] {
										stageStatus = "已发送"
										if splan.IsAck[stage.BillId] {
											stageStatus = "已接单"
										} else {
											stageStatus = "未接单"
										}

									} else {
										stageStatus = "未发送"
									}

								} else {
									stageStatus = "未审核"
								}

							} else if splan.State[stage.BillId] == "complete" {
								stageStatus = "已完成"
							} else {
								stageStatus = "未生成订单"
							}
						}

						b.drawRow(b.Row, "", stageType, stage.Name, orderCount, realCount, stageStatus, supplierName, fmt.Sprintf("%.3f元/%s", stage.Price, stage.Unit), stage.Norm, matHeigth, matWidth, unit, price, budgetPrice, realPrice)
						// if stage.SupplierInfo != nil {
						// 	cates[stage.SupplierInfo.Name] = append(cates[stage.SupplierInfo.Name], b.Row)

						// }
						b.Row++
					}
					// fmt.Println(cates)
					// for supplierName, cate := range cates {

					// 	mergeStartRow := cate[0]
					// 	mergeEndRow := cate[len(cate)-1]
					// 	b.Excel.MergeCell(b.SheetName, fmt.Sprintf("G%d", mergeStartRow), fmt.Sprintf("G%d", mergeEndRow))
					// 	b.Excel.SetCellValue(b.SheetName, fmt.Sprintf("G%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)
				}

				// 预算
				planBudgetPrice += perBudgetPrice
				// 实际金额
				planRealPrice += perRealPrice
			}
		}
		totalBudgetPrice += planBudgetPrice
		totalRealPrice += planRealPrice

	}

	summaryEndRow := b.Row
	startACell := fmt.Sprintf("%s%d", "A", summaryEndRow)
	endMell := fmt.Sprintf("%s%d", "M", summaryEndRow)
	NCell := fmt.Sprintf("%s%d", "N", summaryEndRow)
	OCell := fmt.Sprintf("%s%d", "O", summaryEndRow)
	b.Excel.MergeCell(b.SheetName, startACell, endMell)
	b.Excel.SetCellStyle(b.SheetName, startACell, endMell, b.AlignCenterStyle)
	b.Excel.SetCellValue(b.SheetName, startACell, "生产计划汇总金额")

	// 生产预算汇总

	b.Excel.SetCellStyle(b.SheetName, NCell, NCell, b.AlignCenterStyle)
	totalOrderRealPrice := fmt.Sprintf("%.3f", totalBudgetPrice)
	b.FormatToEmpty(&totalOrderRealPrice)
	b.Excel.SetCellValue(b.SheetName, NCell, totalOrderRealPrice)

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

	return nil
}

func (b *PlanCostExcel) drawSupplierContent() error {
	b.Row += 2
	supplier := ""
	// summaryPlans
	// 预算金额汇总
	var totalBudgetPrice float64 = 0.00
	// 实际金额汇总
	var totalRealPrice float64 = 0.00

	for _, splan := range b.Content.Plans {
		plan := splan.Plan
		comps := plan.Pack.Components
		// 预算金额汇总
		var planBudgetPrice float64 = 0.00
		// 实际金额汇总
		var planRealPrice 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 b.Content.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 = "张"
								}

								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 = "成品采购"
								}

								// 状态
								stageStatus := ""

								if len(stage.BillId) < 1 {
									stageStatus = "未生成订单"
								} else {
									if splan.State[stage.BillId] == "created" {
										stageStatus = "进行中"
										// 审核状态
										if splan.Reviewed[stage.BillId] == 1 {
											stageStatus = "已审核"
											if splan.IsSend[stage.BillId] {
												stageStatus = "已发送"
												if splan.IsAck[stage.BillId] {
													stageStatus = "已接单"
												} else {
													stageStatus = "未接单"
												}

											} else {
												stageStatus = "未发送"
											}

										} else {
											stageStatus = "未审核"
										}

									} else if splan.State[stage.BillId] == "complete" {
										stageStatus = "已完成"
									} else {
										stageStatus = "未生成订单"
									}
								}

								b.drawRow(b.Row, "", stageType, stage.Name, orderCount, realCount, stageStatus, supplierName, fmt.Sprintf("%.3f元/%s", stage.Price, stage.Unit), stage.Norm, matHeigth, matWidth, unit, price, budgetPrice, realPrice)
								// if stage.SupplierInfo != nil {
								// 	cates[stage.SupplierInfo.Name] = append(cates[stage.SupplierInfo.Name], b.Row)

								// }
								b.Row++
							}

						}
					}

					// todo 有bug 合并同一供应商名
					// for supplierName, cate := range cates {
					// 	mergeStartRow := cate[0]
					// 	mergeEndRow := cate[len(cate)-1]
					// 	b.Excel.MergeCell(b.SheetName, fmt.Sprintf("G%d", mergeStartRow), fmt.Sprintf("G%d", mergeEndRow))
					// 	b.Excel.SetCellValue(b.SheetName, fmt.Sprintf("G%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
			}

		}

		totalBudgetPrice += planBudgetPrice
		totalRealPrice += planRealPrice
	}

	summaryEndRow := b.Row
	startACell := fmt.Sprintf("%s%d", "A", summaryEndRow)
	endMell := fmt.Sprintf("%s%d", "M", summaryEndRow)
	NCell := fmt.Sprintf("%s%d", "N", summaryEndRow)
	OCell := fmt.Sprintf("%s%d", "O", summaryEndRow)
	b.Excel.MergeCell(b.SheetName, startACell, endMell)
	b.Excel.SetCellStyle(b.SheetName, startACell, endMell, b.AlignCenterStyle)
	b.Excel.SetCellValue(b.SheetName, startACell, "生产计划汇总金额")

	// 生产预算汇总

	b.Excel.SetCellStyle(b.SheetName, NCell, NCell, b.AlignCenterStyle)
	totalOrderRealPrice := fmt.Sprintf("%.3f", totalBudgetPrice)
	b.FormatToEmpty(&totalOrderRealPrice)
	b.Excel.SetCellValue(b.SheetName, NCell, totalOrderRealPrice)

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

	// 供应商名字标题
	style, err := b.Excel.NewStyle(&excelize.Style{
		Alignment: &excelize.Alignment{Horizontal: "center", Vertical: "center", WrapText: true},
	})
	if err != nil {
		return err
	}
	err = b.Excel.SetCellStyle(b.SheetName, "A1", "G1", style)
	if err != nil {
		return err
	}
	b.Excel.SetRowHeight(b.SheetName, 1, 32)
	b.Excel.SetCellValue(b.SheetName, "A1", fmt.Sprintf("【%s】-生产成本表", supplier))

	return nil
}

func (b *PlanCostExcel) Draws() {
	b.drawTitle()
	b.drawTableTitle()
	if !b.Content.SupplierId.IsZero() {
		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", WrapText: true},
		Border:    border,
		Font:      &excelize.Font{Size: 10},
	})

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

	f.SetColWidth(b.SheetName, "A", "D", 16)
	f.SetColWidth(b.SheetName, "E", "F", 12)
	f.SetColWidth(b.SheetName, "G", "G", 25)
	f.SetColWidth(b.SheetName, "H", "K", 16)
	f.SetColWidth(b.SheetName, "L", "L", 10)
	f.SetColWidth(b.SheetName, "M", "O", 16)
	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 = ""
	}

}