box-cost/el/excel.go

package el

import (
	"box-cost/model"
	"context"
	"fmt"
	"log"
	"regexp"
	"strings"
	"time"

	"github.com/xuri/excelize/v2"
	"go.mongodb.org/mongo-driver/bson"
	"go.mongodb.org/mongo-driver/bson/primitive"
	"go.mongodb.org/mongo-driver/mongo"
)

const (
	EXCEL_TMPLATE_FILE  = "tmplate/tmplate.xlsx"
	PURCHASE_COLLECTION = "bill-purchase"
	PRODUCE_COLLECTION  = "bill-produce"
	PRODUCT_COLLECTION  = "bill-product"
	CELL_BACKGROUND     = "808080"
)

var needChangeCol = map[string]string{
	"部件": "E",
	"下单": "F",
	"纸张": "G",
	"印刷": "H",
	"覆膜": "I",
	"烫金": "J",
	"丝印": "K",
	"对裱": "L",
	"压纹": "M",
	"裱瓦": "N",
	"模切": "O",
	"粘盒": "P",
	"组装": "Q",
	"交货": "R",
}

func MatchString(targetStr string, regexPattern string) bool {
	// 编译正则表达式
	re := regexp.MustCompile(regexPattern)
	// 检查目标字符串是否包含匹配的子串
	return re.MatchString(targetStr)
}

func UpdateExcel(client *mongo.Client, plans []*model.ProductPlan) {
	row := 5
	planCompStatus := []map[string]string{}

	for _, plan := range plans {

		for _, comp := range plan.Pack.Components {
			// ""代表该部件没有该工艺 "〇"代表正在进行的工艺
			// "808080"背景颜色代表部件所含未进行工艺 √代表已完成工序
			compStatus := map[string]string{
				"部件": " ",
				"行数": "5",
				// 部件中只要有一个订单就说明下单了
				"下单": " ",
				// 采购单中type为纸张 订单对应状态为完成
				"纸张": " ",
				// 遍历工艺单,工序中包含印刷
				"印刷": " ",
				// 遍历工艺单,工序中包含覆膜
				"覆膜": " ",
				// 遍历工艺单,工序中包含烫金
				"烫金": " ",
				// 遍历工艺单,工序中包含丝印
				"丝印": " ",
				// 遍历工艺单,工序中包含对裱
				"对裱": " ",
				// 遍历工艺单,工序中包含压纹
				"压纹": " ",
				// 遍历工艺单,工序中包含裱瓦
				"裱瓦": " ",
				// 遍历工艺单,工序中包含模切
				"模切": " ",
				// 遍历工艺单,工序中包含粘盒
				"粘盒": " ",
				// 遍历工艺单,工序中包含组装
				"组装": " ",
				"交货": " ",
			}
			compStatus["部件"] = comp.Name
			compStatus["行数"] = fmt.Sprintf("%d", row)
			row++
			// 去重获取所有订单
			seen := make(map[string]bool)
			tbills := make([]string, 0, len(comp.Stages)) // 结果数组，容量初始化为原数组的长度

			for _, stage := range comp.Stages {

				if len(stage.BillId) > 0 {
					value := fmt.Sprintf("%d_%s", stage.BillType, stage.BillId)
					if _, ok := seen[value]; !ok {
						// 标记为已出现
						seen[value] = true
						// 添加到结果数组
						tbills = append(tbills, value)
					}
				} else {
					// 产品中填写了这个工序但是没有下单 黑色背景
					for k := range compStatus {
						if k == "下单" || k == "交货" || k == "部件" || k == "行数" {
							continue
						}
						// 纸张 只要是采购单就设置纸张状态
						if stage.Type == 1 {
							compStatus["纸张"] = CELL_BACKGROUND
						}

						// 匹配工艺关键字 匹配到就设置状态为黑背景
						if MatchString(stage.Name, k) {
							compStatus[k] = CELL_BACKGROUND
						}

					}
				}

			}

			// 如果tbills为空，说明该部件没有订单
			if len(tbills) == 0 {
				// 该部件没有订单，跳过
				planCompStatus = append(planCompStatus, compStatus)
				continue
			}

			// 查询数据库获取bill详细信息
			// 最后工序订单号
			lastBillType := tbills[len(tbills)-1]
			for _, billType := range tbills {

				compStatus["下单"] = "√"
				bt := strings.Split(billType, "_")[0]
				billId, _ := primitive.ObjectIDFromHex(strings.Split(billType, "_")[1])
				if bt == "1" {
					// 查询采购单
					bill := &model.PurchaseBill{}
					err := client.Database("box-cost").Collection(PURCHASE_COLLECTION).FindOne(context.Background(), bson.M{"_id": billId}).Decode(bill)
					if err != nil {
						log.Fatal(err)
					}
					// if bill.Type == "纸张" {
					// 	compStatus["纸张"] = "〇"
					// 	if bill.Status == "complete" {
					// 		compStatus["纸张"] = "√"
					// 	}

					// }

					compStatus["纸张"] = "〇"
					if bill.Status == "complete" {
						compStatus["纸张"] = "√"
					}

					if lastBillType == billType {
						for _, paper := range bill.Paper {
							compStatus["交货"] = fmt.Sprintf("%d", paper.ConfirmCount)
						}
					}

				}
				if bt == "2" {
					// 查询工艺单
					bill := &model.ProduceBill{}
					err := client.Database("box-cost").Collection(PRODUCE_COLLECTION).FindOne(context.Background(), bson.M{"_id": billId}).Decode(bill)
					if err != nil {
						log.Fatal(err)
					}
					for _, produce := range bill.Produces {
						for k := range compStatus {
							if k == "下单" || k == "纸张" || k == "交货" || k == "部件" || k == "行数" {
								continue
							}
							if MatchString(produce.Name, k) {
								compStatus[k] = "〇"
								if bill.Status == "complete" {
									compStatus[k] = "√"
								}
							}

							compStatus["交货"] = fmt.Sprintf("%d", produce.ConfirmCount)
						}

					}
				}

				// 暂时没有状态标定
				if bt == "3" {
					// 查询成品单
					bill := &model.ProductBill{}
					err := client.Database("box-cost").Collection(PRODUCT_COLLECTION).FindOne(context.Background(), bson.M{"_id": billId}).Decode(bill)
					if err != nil {
						log.Fatal(err)
					}
					// fmt.Println(bill)
					if lastBillType == billType {
						for _, product := range bill.Products {
							compStatus["交货"] = fmt.Sprintf("%d", product.ConfirmCount)
						}
					}
				}
			}

			planCompStatus = append(planCompStatus, compStatus)
		}

	}
	// fmt.Printf("%#v\n", planCompStatus)
	UpdateCell(planCompStatus)

}

func UpdateCell(planCompStatus []map[string]string) {
	tmpfile, err := excelize.OpenFile(EXCEL_TMPLATE_FILE)
	if err != nil {
		log.Fatal(err)
	}
	excelIndex := tmpfile.GetActiveSheetIndex()
	sheetName := tmpfile.GetSheetName(excelIndex)

	for _, compStatus := range planCompStatus {
		// fmt.Println("-----------------------------------------------")
		// fmt.Println(compStatus)
		row := compStatus["行数"]
		for colk, col := range needChangeCol {
			for csk, csv := range compStatus {
				if colk == csk {
					cell := fmt.Sprintf("%s%s", col, row)
					// fmt.Println(cell)

					// 设置背景
					if csv == CELL_BACKGROUND {
						// 设置单元格的值

						// 定义样式
						style, err := tmpfile.NewStyle(&excelize.Style{
							Border: []excelize.Border{
								{
									Type:  "left",
									Color: "FF000000",
									Style: 1,
								},
								{
									Type:  "right",
									Color: "FF000000",
									Style: 1,
								},
								{
									Type:  "top",
									Color: "FF000000",
									Style: 1,
								},
								{
									Type:  "bottom",
									Color: "FF000000",
									Style: 1,
								},
							},
							Fill: excelize.Fill{
								Type:    "pattern",
								Pattern: 1, // 1 表示实心填充
								Color:   []string{CELL_BACKGROUND},
							},
						})
						if err != nil {
							log.Fatal(err)
						}

						err = tmpfile.SetCellStyle(sheetName, cell, cell, style)
						if err != nil {
							log.Fatal(err)
						}
					} else {
						// 设置字符
						err = tmpfile.SetCellValue(sheetName, cell, csv)
						if err != nil {
							log.Fatal(err)
						}

					}

				}

			}
		}
	}

	// 获取当前日期
	date := time.Now().Format("2006年01月02日_150405")
	fileName := fmt.Sprintf("礼盒加工追踪表_%s.xlsx", date)

	// 以新的文件名保存文件
	err = tmpfile.SaveAs(fileName)
	if err != nil {
		log.Fatal(err)
	}
}