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

package api

import (
	"box-cost/db/model"
	"fmt"

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

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

	Title string //标题

	Excel *excelize.File

	SheetName string

	AlignCenterStyle int

	Content *model.ProductPlan
}

func (b *PlanCostExcel) drawTitle() error {
	tileIndex := b.Offset + 1
	startCell := fmt.Sprintf("A%d", tileIndex)
	err := b.Excel.MergeCell(b.SheetName, startCell, fmt.Sprintf("O%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", "产品名称")
	drawCol("B", "产品配料名称")
	drawCol2("C", "D", "材料/工序", "材料", "工序")
	drawCol("E", "供应商名称")
	drawCol3("F", "G", "H", "规格", "厚度(纸克)", "长", "宽")
	drawCol("I", "单位")
	drawCol2("J", "K", "数量", "下单数量", "成品数量")
	drawCol2("L", "M", "单价(预算)", "单价1", "单价2")
	drawCol("N", "预算金额")
	drawCol("O", "汇总金额")

	return nil
}

func (b *PlanCostExcel) drawTableContent() error {
	row := b.Offset + 4

	var DrawRow = func(rowIndex int, values ...string) {
		charas := []string{"A", "B", "C", "D", "E", "F", "G", "H", "I", "G", "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, 21)
		}
	}
	comps := b.Content.Pack.Components
	if len(comps) > 0 {
		row1 := row
		for _, comp := range comps {
			if len(comp.Mats) > 0 {
				startRow := row
				for _, mat := range comp.Mats {
					// 材料
					supplierName := ""
					if mat.Supplier.SupplierInfo != nil {
						supplierName = mat.Supplier.SupplierInfo.Name

					}
					heigth := fmt.Sprintf("%.2f", mat.MatInfo.Heigth)
					width := fmt.Sprintf("%.2f", mat.MatInfo.Width)
					orderCount := fmt.Sprintf("%.2f", mat.Supplier.OrderCount)
					price := fmt.Sprintf("%.2f", mat.MatInfo.Price)
					calcName := ""
					if mat.Supplier.Calc != nil {
						calcName = mat.Supplier.Calc.Name
					}
					orderPrice := fmt.Sprintf("%.2f", mat.Supplier.OrderPrice)
					orderRealPrice := fmt.Sprintf("%.2f", mat.Supplier.OrderRealPrice)
					DrawRow(row, b.Content.Name, "", mat.MatInfo.Name, "", supplierName, mat.MatInfo.Norm, heigth, width, mat.MatInfo.Unit, orderCount, "-", calcName, price, orderPrice, orderRealPrice)

					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("%.2f", craft.Supplier.OrderCount)
							carftCalcName := ""
							if craft.Supplier.Calc != nil {
								carftCalcName = craft.Supplier.Calc.Name
							}
							carftPrice := fmt.Sprintf("%.2f", craft.CraftInfo.Price)
							carftOrderPrice := fmt.Sprintf("%.2f", craft.Supplier.OrderPrice)
							carftOrderRealPrice := fmt.Sprintf("%.2f", craft.Supplier.OrderRealPrice)
							DrawRow(row, b.Content.Name, "", "", craft.CraftInfo.Name, craftSupplierName, craft.CraftInfo.Norm, "", "", craft.CraftInfo.Unit, carftOrderCount, "-", carftCalcName, carftPrice, carftOrderPrice, carftOrderRealPrice)
							row++

						}
					}
				}
				endRow := row - 1
				startCell := fmt.Sprintf("%s%d", "B", startRow)
				// left2Cell := fmt.Sprintf("%s%d", prefix2, row)
				endCell := fmt.Sprintf("%s%d", "B", endRow)
				b.Excel.MergeCell(b.SheetName, startCell, endCell)
				err := b.Excel.SetCellStyle(b.SheetName, startCell, endCell, b.AlignCenterStyle)
				if err != nil {
					return err
				}
				b.Excel.SetCellValue(b.SheetName, startCell, comp.Name)

			}
		}

		endRow := row - 1
		startCell := fmt.Sprintf("%s%d", "A", row1)
		endCell := fmt.Sprintf("%s%d", "A", endRow)
		b.Excel.MergeCell(b.SheetName, startCell, endCell)
		err := b.Excel.SetCellStyle(b.SheetName, startCell, endCell, b.AlignCenterStyle)
		if err != nil {
			return err
		}
		b.Excel.SetCellValue(b.SheetName, startCell, b.Content.Name)
	}

	return nil
}

func (b *PlanCostExcel) Draws() {
	b.drawTitle()
	b.drawTableTitle()
	b.drawTableContent()
}

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", "A", 17)
	f.SetColWidth(b.SheetName, "B", "B", 13)
	f.SetColWidth(b.SheetName, "C", "E", 12)
	f.SetColWidth(b.SheetName, "F", "O", 9.5)
	f.SetPageMargins(b.SheetName, excelize.PageMarginTop(0), excelize.PageMarginLeft(0), excelize.PageMarginRight(0))
	return b
}