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