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

package api

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

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

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

type ProductBillExcel struct {
	Offset int
	Row    int

	Title string //标题

	Excel *excelize.File

	SheetName string

	AlignCenterStyle int

	Content *model.ProductBill

	Signatures []*model.Signature
}

func (b *ProductBillExcel) 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 *ProductBillExcel) 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("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, 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+"  类别："+b.Content.Type)
	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 *ProductBillExcel) drawTableTitle() error {
	b.Row += 3
	// 品名 规格  数量 单位 单价 金额
	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 *ProductBillExcel) 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)

			b.Excel.SetRowHeight(b.SheetName, rowIndex, 21)
		}
	}
	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++
		}
	}

	return nil
}

func (b *ProductBillExcel) drawTableFooter() error {
	left1Cell := fmt.Sprintf("A%d", b.Row)
	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, _ := b.Excel.NewStyle(&excelize.Style{
		Alignment: &excelize.Alignment{Horizontal: "left", Vertical: "center"},
		Border:    border,
	})

	b.Excel.SetCellStyle(b.SheetName, left1Cell, left1Cell, styleLeft)
	b.Excel.SetCellValue(b.SheetName, left1Cell, "送货地址：")

	addCel := fmt.Sprintf("B%d", b.Row)

	b.Excel.MergeCell(b.SheetName, addCel, fmt.Sprintf("E%d", b.Row))
	b.Excel.SetCellStyle(b.SheetName, addCel, fmt.Sprintf("E%d", b.Row), styleLeft)
	b.Excel.SetCellValue(b.SheetName, addCel, b.Content.SendTo)

	sureCel := fmt.Sprintf("F%d", b.Row)
	b.Excel.MergeCell(b.SheetName, sureCel, fmt.Sprintf("J%d", b.Row))
	b.Excel.SetCellStyle(b.SheetName, sureCel, fmt.Sprintf("J%d", b.Row), styleLeft)
	b.Excel.SetCellValue(b.SheetName, sureCel, "收货人：")

	b.Excel.SetRowHeight(b.SheetName, b.Row, 30)

	return nil
}

func (b *ProductBillExcel) 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++
	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 *ProductBillExcel) drawTableSignature() error {
	b.Row += 2
	style1, _ := b.Excel.NewStyle(&excelize.Style{
		Alignment: &excelize.Alignment{Horizontal: "center", Vertical: "center"},
		// Border:    border,
	})

	// 制单人
	billUserCell := fmt.Sprintf("A%d", b.Row+1)
	b.Excel.SetCellValue(b.SheetName, billUserCell, "制单人：")
	billUservCell := fmt.Sprintf("B%d", b.Row+1)
	b.Excel.SetCellValue(b.SheetName, billUservCell, b.Content.UserName)

	fontCell := fmt.Sprintf("F%d", b.Row)
	imageCell1 := fmt.Sprintf("G%d", b.Row)
	imageCell2 := fmt.Sprintf("I%d", b.Row)
	eRow := b.Row + 2
	b.Excel.MergeCell(b.SheetName, fontCell, fmt.Sprintf("F%d", eRow))
	b.Excel.SetCellStyle(b.SheetName, fontCell, fontCell, style1)
	b.Excel.SetCellValue(b.SheetName, fontCell, "审核签字：")

	b.Excel.MergeCell(b.SheetName, imageCell1, fmt.Sprintf("H%d", eRow))
	b.Excel.SetCellStyle(b.SheetName, imageCell1, imageCell1, style1)
	b.Excel.SetCellValue(b.SheetName, imageCell1, "")

	b.Excel.MergeCell(b.SheetName, imageCell2, fmt.Sprintf("J%d", eRow))
	b.Excel.SetCellStyle(b.SheetName, imageCell2, imageCell1, style1)
	b.Excel.SetCellValue(b.SheetName, imageCell2, "")

	// 状态为已审核时，签字
	// `{
	// 	"x_scale": 0.12,
	// 	"y_scale": 0.12,
	// 	"x_offset": 30,
	// 	"print_obj": true,
	// 	"lock_aspect_ratio": false,
	// 	"locked": false,
	// 	"positioning": "oncell"
	// }`
	if b.Content.Reviewed == 1 {
		imageCells := []string{imageCell1, imageCell2}
		if len(b.Signatures) > 0 {
			for k, sign := range b.Signatures {
				b.Excel.AddPicture(b.SheetName, imageCells[k], sign.Path, sign.Format)

			}
		}
	}

	return nil
}

func (b *ProductBillExcel) Draws() {
	b.drawTitle()
	b.drawSubTitles()
	b.drawTableTitle()
	b.drawTableContent()
	b.drawTableFooter()
	b.drawRemark()
	b.drawTableSignature()
}

func NewProductBill(f *excelize.File) *ProductBillExcel {

	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 := &ProductBillExcel{
		Title:            "原材料采购单",
		SheetName:        "Sheet1",
		Excel:            f,
		Offset:           0,
		AlignCenterStyle: styleLeft,
		Signatures:       make([]*model.Signature, 0),
	}

	f.SetColWidth(b.SheetName, "A", "J", 11.5)
	f.SetPageMargins(b.SheetName, excelize.PageMarginTop(1.5), excelize.PageMarginLeft(0.25), excelize.PageMarginRight(0))
	return b
}

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

}

func (b *ProductBillExcel) SetContent(content interface{}) {
	b.Content = content.(*model.ProductBill)

}
func (b *ProductBillExcel) SetTitle(title string) {
	b.Title = title

}
func (b *ProductBillExcel) SetRow(row int) {
	b.Row = row
}
func (b *ProductBillExcel) GetRow() int {
	return b.Row
}

func (b *ProductBillExcel) SetSignatures(sign interface{}) {
	b.Signatures = sign.([]*model.Signature)

}