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

type PlanStatusInfo struct {
	PlanName       string              `json:"planName"`
	PlanUnit       string              `json:"planUnit"`
	PlanCount      int                 `json:"planCount"`
	PlanRowStart   int                 `json:"planRowStart"`
	PlanRowEnd     int                 `json:"planRowEnd"`
	PlanCompStatus []map[string]string `json:"planCompStatus"`
}

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

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

			// ???:1 最后一个工序没下单
			// isBill := true
			// lastStage := comp.Stages[len(comp.Stages)-1]
			// if len(lastStage.BillId) < 24 {
			// 	isBill = false
			// }

			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
						}

					}
				}

			}
			// fmt.Println(tbills)

			// 如果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 !isBill {
					// 	continue
					// }
					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] = "√"
								}
							}

							// 直接赋值,如果这个订单是最后一个,则状态覆盖
							// ???思考:如果最后一个工序没有生成订单的话,是按最后一个工序还是最后一个订单？这里是最后一个订单
							// ???:1
							// if !isBill {
							// 	continue
							// }
							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 !isBill {
					// 	continue
					// }
					if lastBillType == billType {
						for _, product := range bill.Products {
							compStatus["交货"] = fmt.Sprintf("%d", product.ConfirmCount)
						}
					}
				}
			}

			planCompStatus = append(planCompStatus, compStatus)
		}
		// fmt.Println(plan.Name)
		// fmt.Println("rowstart:", startRow)
		// fmt.Println("rowend:", row-1)
		planStatusInfos = append(planStatusInfos, &PlanStatusInfo{
			PlanName: plan.Name,
			// !这个规格好像没有
			PlanUnit:       "",
			PlanCount:      plan.Total,
			PlanRowStart:   startRow,
			PlanRowEnd:     row - 1,
			PlanCompStatus: planCompStatus,
		})

	}
	// fmt.Printf("%#v\n", planStatusInfos[len(planStatusInfos)-1])
	// UpdateCell(planCompStatus)
	UpdateCell1(planStatusInfos)

}

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)
	// 定义样式
	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)
	}

	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 {
						// 设置单元格的值
						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)
	}
}

func UpdateCell1(planStatusInfos []*PlanStatusInfo) {
	tmpfile, err := excelize.OpenFile(EXCEL_TMPLATE_FILE)
	if err != nil {
		log.Fatal(err)
	}
	excelIndex := tmpfile.GetActiveSheetIndex()
	sheetName := tmpfile.GetSheetName(excelIndex)
	// 定义样式
	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)
	}

	for _, planStatusInfo := range planStatusInfos {
		// 填充品名/箱规/数量
		// 品名
		err = tmpfile.SetCellValue(sheetName, fmt.Sprintf("%s%d", "B", planStatusInfo.PlanRowStart), planStatusInfo.PlanName)
		if err != nil {
			log.Fatal(err)
		}
		// 数量
		err = tmpfile.SetCellValue(sheetName, fmt.Sprintf("%s%d", "D", planStatusInfo.PlanRowStart), planStatusInfo.PlanCount)
		if err != nil {
			log.Fatal(err)
		}

		planCompStatus := planStatusInfo.PlanCompStatus
		for _, compStatus := range planCompStatus {

			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 {
							// 设置单元格的值
							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)
	}
}