box-cost/boxcost/api/report-produce-excel.go

package api

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

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

type ReportProduceExcel struct {
	Offset int
	Row    int

	Title string //标题

	Excel *excelize.File

	SheetName string

	AlignCenterStyle int

	Content *model.ProduceBill

	BudgetCount float64
	RealCount   float64
}

func (b *ReportProduceExcel) drawTitle() error {
	// 设置外边距
	marginLeft := excelize.PageMarginLeft(0.15)
	b.Excel.SetPageMargins(b.SheetName, marginLeft)
	tileIndex := b.Offset + 1
	startCell := fmt.Sprintf("A%d", tileIndex)
	err := b.Excel.MergeCell(b.SheetName, startCell, fmt.Sprintf("L%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 *ReportProduceExcel) drawSubTitles() error {
	row := b.Offset + 2

	styleLeft, err := b.Excel.NewStyle(&excelize.Style{
		Alignment: &excelize.Alignment{Horizontal: "left", Vertical: "center"},
		Font:      &excelize.Font{Size: 11}})
	if err != nil {
		return err
	}
	styleRight, err := b.Excel.NewStyle(&excelize.Style{
		Alignment: &excelize.Alignment{Horizontal: "right", Vertical: "center"},
		Font:      &excelize.Font{Size: 11}})
	if err != nil {
		return err
	}

	var drawLeft = func(rowIndex int, value string) error {
		//左边1
		left1Cell := fmt.Sprintf("A%d", rowIndex)
		err = b.Excel.MergeCell(b.SheetName, left1Cell, fmt.Sprintf("E%d", rowIndex))
		if err != nil {
			return err
		}
		err = b.Excel.SetCellStyle(b.SheetName, left1Cell, left1Cell, styleLeft)
		if err != nil {
			return err
		}
		b.Excel.SetCellValue(b.SheetName, left1Cell, value)
		return nil
	}

	var drawRight = func(rowIndex int, value string) error {
		right1Cell := fmt.Sprintf("F%d", rowIndex)
		err = b.Excel.MergeCell(b.SheetName, right1Cell, fmt.Sprintf("L%d", rowIndex))
		if err != nil {
			return err
		}
		err = b.Excel.SetCellStyle(b.SheetName, right1Cell, right1Cell, styleRight)
		if err != nil {
			return err
		}
		b.Excel.SetCellValue(b.SheetName, right1Cell, value)
		return nil
	}

	//第一行
	drawLeft(row, "类别："+b.Content.Type)
	drawRight(row, "单号："+b.Content.SerialNumber)
	b.Excel.SetRowHeight(b.SheetName, row, 21)

	//第二行
	drawLeft(row+1, "供应商名称："+b.Content.Supplier)
	timeformat := b.Content.CreateTime.Local().Format("2006年01月02号 15:04:05")
	drawRight(row+1, "下单时间："+timeformat)
	b.Excel.SetRowHeight(b.SheetName, row+1, 21)

	//第三行
	drawLeft(row+2, "产品名称："+b.Content.ProductName)
	status := ""
	if b.Content.Status == "complete" {
		status = fmt.Sprintf("已完成(%d)", b.Content.ConfirmCount)
	}
	if b.Content.Status == "created" {
		status = "进行中"
	}
	drawRight(row+2, "状态："+status)
	b.Excel.SetRowHeight(b.SheetName, row+2, 21)

	return nil
}

func (b *ReportProduceExcel) drawTableTitle() error {
	row := b.Offset + 5

	//A加工项目 B规格(克) 尺寸C-D 数量E 单价F-G 交货时间H 备注I
	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 drawCol2 = func(prefix string, value1 string, value2 string) error {

		left1Cell := fmt.Sprintf("%s%d", prefix, row)
		left2Cell := fmt.Sprintf("%s%d", prefix, row+1)
		err := b.Excel.SetCellStyle(b.SheetName, left1Cell, left2Cell, b.AlignCenterStyle)
		if err != nil {
			return err
		}
		b.Excel.SetCellValue(b.SheetName, left1Cell, value1)
		b.Excel.SetCellValue(b.SheetName, left2Cell, value2)
		return nil
	}

	drawCol("A", "加工项目")
	drawCol("B", "规格")
	drawCol("C", "纸张")
	drawCol("D", "来纸尺寸")
	drawCol("E", "印刷尺寸")
	drawCol("F", "数量")
	drawCol("G", "完成数量")
	drawCol2("H", "单价", "元/张")
	drawCol("I", "预算金额")
	drawCol("J", "实际金额")
	drawCol("K", "交货时间")
	drawCol("L", "备注")

	return nil
}

func (b *ReportProduceExcel) drawTableContent() error {
	row := b.Offset + 7
	b.Row = row
	var DrawRow = func(rowIndex int, values ...string) {
		charas := []string{"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L"}
		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)
		}
	}

	produces := b.Content.Produces
	if len(produces) > 0 {
		for _, produce := range produces {

			confirmCount := "-"
			realAmount := "-"
			price, err := strconv.ParseFloat(produce.Price, 64)
			if err != nil {
				price = 0.00
			}

			// 预算金额
			budgetAmount := fmt.Sprintf("%.2f", float64(produce.Count)*price)
			b.FormatToEmpty(&budgetAmount)

			b.BudgetCount += float64(produce.Count) * price

			// 实际完成数
			confirmCount = fmt.Sprintf("%d", b.Content.ConfirmCount)
			b.FormatToEmpty(&confirmCount)

			// 实际金额
			realAmount = fmt.Sprintf("%.2f", float64(b.Content.ConfirmCount)*price)
			b.FormatToEmpty(&realAmount)

			b.RealCount += float64(b.Content.ConfirmCount) * price

			deliveryTime := produce.DeliveryTime.Local().Format("2006-01-02")
			DrawRow(row, produce.Name, produce.Norm, produce.Paper, produce.PaperSize, produce.PrintSize, fmt.Sprintf("%d", produce.Count), confirmCount, produce.Price, budgetAmount, realAmount, deliveryTime, produce.Remark)
			row++
			b.Row++
		}
	}

	return nil
}

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

func (b *ReportProduceExcel) Count() {
	row := b.Row + 1

	startCell := fmt.Sprintf("%s%d", "A", row)
	endCell := fmt.Sprintf("%s%d", "H", row)
	b.Excel.MergeCell(b.SheetName, startCell, endCell)
	b.Excel.SetCellStyle(b.SheetName, startCell, endCell, b.AlignCenterStyle)
	b.Excel.SetCellValue(b.SheetName, startCell, "汇总金额")

	// 预算金额汇总
	budgetCountCell := fmt.Sprintf("%s%d", "I", row)
	b.Excel.SetCellValue(b.SheetName, budgetCountCell, b.BudgetCount)
	b.Excel.SetCellStyle(b.SheetName, budgetCountCell, budgetCountCell, b.AlignCenterStyle)

	// 实际金额汇总
	RealCountCell := fmt.Sprintf("%s%d", "J", row)
	b.Excel.SetCellValue(b.SheetName, RealCountCell, b.RealCount)
	b.Excel.SetCellStyle(b.SheetName, RealCountCell, RealCountCell, b.AlignCenterStyle)

	b.Excel.SetRowHeight(b.SheetName, row, 21)

}

func NewReportProduceExcel(f *excelize.File) *ReportProduceExcel {

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

	b := &ReportProduceExcel{
		Title:            "中鱼互动加工单",
		SheetName:        "Sheet1",
		Excel:            f,
		Offset:           0,
		AlignCenterStyle: styleLeft,
	}
	f.SetColWidth(b.SheetName, "A", "A", 12)
	f.SetColWidth(b.SheetName, "B", "K", 9.5)
	f.SetColWidth(b.SheetName, "L", "L", 10)
	f.SetPageMargins(b.SheetName, excelize.PageMarginTop(0), excelize.PageMarginLeft(0), excelize.PageMarginRight(0))

	return b
}

func (b *ReportProduceExcel) FormatToEmpty(str *string) {
	if *str == "0" || *str == "0.00" {
		*str = ""
	}
}