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

package api

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

	_ "image/gif"
	_ "image/jpeg"
	_ "image/png"

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

type ReportProductExcel struct {
	Offset int
	Row    int

	Title string //标题

	Excel *excelize.File

	SheetName string

	AlignCenterStyle int

	Content *model.ProductBill

	BudgetAmount float64
	RealAmount   float64
}

func (b *ReportProductExcel) drawTitle() error {
	b.Row++
	startCell := fmt.Sprintf("A%d", b.Row)
	err := b.Excel.MergeCell(b.SheetName, startCell, fmt.Sprintf("J%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 *ReportProductExcel) drawSubTitles() error {
	b.Row++
	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("F%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("G%d", rowIndex)
		err = b.Excel.MergeCell(b.SheetName, right1Cell, fmt.Sprintf("J%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(b.Row, "需求方（甲方）："+b.Title+"订单")
	drawRight(b.Row, "单号："+b.Content.SerialNumber)
	b.Excel.SetRowHeight(b.SheetName, b.Row, 21)

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

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

	return nil
}

func (b *ReportProductExcel) 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)
	}
	// a采购项目 b规格 c下单数量 d单位 e完成数量 f单价 g预算金额 h实际金额 i交货时间 j备注
	drawCol("A", "采购项目")
	drawCol("B", "规格")
	drawCol("C", "下单数量")
	drawCol("D", "单位")
	drawCol("E", "完成数量")
	drawCol("F", "单价")
	drawCol("G", "预算金额")
	drawCol("H", "实际金额")
	drawCol("I", "交货时间")
	drawCol("J", "备注")

	return nil
}

func (b *ReportProductExcel) drawTableContent() error {
	b.Row += 2

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

			// var magN float64 = 1
			// lenv := len([]rune(v))
			// magN = math.Ceil(float64(lenv) / 10)
			// b.Excel.SetRowHeight(b.SheetName, rowIndex, 21*magN)
			b.Excel.SetRowHeight(b.SheetName, rowIndex, 46)
		}
	}

	products := b.Content.Products
	if len(products) > 0 {
		for _, product := range products {
			deliveryTime := product.DeliveryTime.Local().Format("2006-01-02")
			realCount := ""
			realPrice := ""
			// 预算金额
			budgetAmount := fmt.Sprintf("%.3f", float64(product.OrderCount)*product.OrderPrice)
			b.FormatToEmpty(&budgetAmount)

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

			// 实际金额
			realPrice = fmt.Sprintf("%.3f", float64(product.ConfirmCount)*product.OrderPrice)
			b.FormatToEmpty(&realPrice)

			// a采购项目 b规格 c下单数量 d单位 e完成数量 f单价 g预算金额 h实际金额 i交货时间 j备注
			orderCount := fmt.Sprintf("%d", product.OrderCount)
			price := fmt.Sprintf("%.3f", product.OrderPrice)
			b.FormatToEmpty(&price)
			DrawRow(b.Row, product.Name, product.Norm, orderCount, product.Unit, realCount, price, budgetAmount, realPrice, deliveryTime, product.Remark)
			b.Row++
			b.BudgetAmount += float64(product.OrderCount) * product.OrderPrice
			b.RealAmount += float64(product.ConfirmCount) * product.OrderPrice
		}
	}

	return nil
}

func (b *ReportProductExcel) drawRemark() error {
	// 填备注
	b.Row++
	remarkTitleCell := fmt.Sprintf("A%d", b.Row)
	b.Excel.SetCellValue(b.SheetName, remarkTitleCell, "备注：")
	b.Row++

	remarkContentScell := fmt.Sprintf("A%d", b.Row)
	// 根据换行符确定多少行单元格
	reg := regexp.MustCompile(`\n`)
	remarkRowNum := len(reg.FindAllString(b.Content.Remark, -1)) + 1
	b.Row += remarkRowNum
	remarkContentEcell := fmt.Sprintf("J%d", b.Row)
	b.Excel.MergeCell(b.SheetName, remarkContentScell, remarkContentEcell)
	b.Excel.SetCellValue(b.SheetName, remarkContentScell, b.Content.Remark)

	// 签字位置
	b.Row += 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
	}
	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("J%d", rowIndex))
		if err != nil {
			return err
		}
		err = b.Excel.SetCellStyle(b.SheetName, right1Cell, right1Cell, styleLeft)
		if err != nil {
			return err
		}
		b.Excel.SetCellValue(b.SheetName, right1Cell, value)
		return nil
	}

	//第一行
	drawLeft(b.Row, "甲方："+b.Title)
	drawRight(b.Row, "乙方："+b.Content.Supplier)
	b.Excel.SetRowHeight(b.SheetName, b.Row, 21)

	//第二行
	drawLeft(b.Row+1, "联系人：")
	timeformat := b.Content.CreateTime.Local().Format("2006年01月02号 15:04:05")
	drawRight(b.Row+1, "联系人：")
	b.Excel.SetRowHeight(b.SheetName, b.Row+1, 21)

	//第三行
	drawLeft(b.Row+2, "时间："+timeformat)
	drawRight(b.Row+2, "时间："+timeformat)
	b.Excel.SetRowHeight(b.SheetName, b.Row+2, 21)
	b.Row += 2

	return nil

}

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

func NewReportProductBill(f *excelize.File) *ReportProductExcel {

	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 := &ReportProductExcel{
		Title:            "原材料采购单",
		SheetName:        "Sheet1",
		Excel:            f,
		Offset:           0,
		AlignCenterStyle: styleLeft,
	}

	// f.SetColWidth(b.SheetName, "A", "J", 11.5)

	f.SetColWidth(b.SheetName, "A", "A", 17)
	f.SetColWidth(b.SheetName, "B", "B", 12)
	f.SetColWidth(b.SheetName, "C", "H", 10.5)
	f.SetColWidth(b.SheetName, "I", "J", 12)
	f.SetPageMargins(b.SheetName, excelize.PageMarginTop(1), excelize.PageMarginLeft(0.25), excelize.PageMarginRight(0))
	return b
}

func (b *ReportProductExcel) FormatToEmpty(str *string) {
	if *str == "0" || *str == "0.000" {
		*str = ""
	}

}