package service import ( "encoding/json" "fmt" "github.com/shopspring/decimal" "github.com/xuri/excelize/v2" "strconv" "time" "wms/constvar" "wms/models" "wms/pkg/logx" "wms/request" ) type MonthFormsService struct{} func NewMonthFormsService() *MonthFormsService { return &MonthFormsService{} } func (slf *MonthFormsService) Query(params request.GetMonthStats) (result []*models.MonthStats, err error) { search := slf.BuildSearch(params) search = search.SetPage(params.Page, params.PageSize) return search.FindNotTotal() } func (slf *MonthFormsService) BuildSearch(params request.GetMonthStats) (search *models.MonthStatsSearch) { search = models.NewMonthStatsSearch().SetKeyword(params.Keyword).SetDate(params.Date) return search } func (slf *MonthFormsService) Count(params request.GetMonthStats) (total int64, err error) { search := slf.BuildSearch(params) return search.Count() } func (slf *MonthFormsService) FetchAll(params request.GetMonthStats) (list []*models.MonthStats, err error) { total, err := slf.Count(params) if err != nil { return nil, err } list = make([]*models.MonthStats, 0, total) params.PageSize = 500 page := 1 for { params.Page = page data, err := slf.Query(params) if err != nil { return nil, err } if len(data) == 0 { break } list = append(list, data...) page++ } return } func (slf *MonthFormsService) Export(dataList []*models.MonthStats) (filename string, err error) { // 创建一个新的 Excel 文件 f := excelize.NewFile() if err != nil { logx.Errorf("NewSheet err:%v", err) return "", err } // 设置表头 f.SetCellValue("Sheet1", "A1", "产品编码") f.SetCellValue("Sheet1", "B1", "产品名称") f.SetCellValue("Sheet1", "C1", "期初库存") f.MergeCell("Sheet1", "C1", "F1") // 合并单元格 f.SetCellValue("Sheet1", "G1", "本月入库") f.MergeCell("Sheet1", "G1", "J1") // 合并单元格 f.SetCellValue("Sheet1", "K1", "本月出库") f.MergeCell("Sheet1", "K1", "N1") // 合并单元格 f.SetCellValue("Sheet1", "O1", "期末库存") f.MergeCell("Sheet1", "O1", "R1") // 合并单元格 unitData := []string{"件", "匹", "米", "重量"} for i, header := range unitData { cell := getColumnAlphabet(i+3) + "2" f.SetCellValue("Sheet1", cell, header) } for i, header := range unitData { cell := getColumnAlphabet(i+7) + "2" f.SetCellValue("Sheet1", cell, header) } for i, header := range unitData { cell := getColumnAlphabet(i+11) + "2" f.SetCellValue("Sheet1", cell, header) } for i, header := range unitData { cell := getColumnAlphabet(i+15) + "2" f.SetCellValue("Sheet1", cell, header) } // 设置表头样式 style := &excelize.Style{ Border: nil, Fill: excelize.Fill{ Type: "pattern", Pattern: 1, Shading: 0, }, Font: &excelize.Font{ Bold: true, }, Alignment: &excelize.Alignment{ Horizontal: "center", }, } titleStyle, err := f.NewStyle(style) if err != nil { return } f.SetCellStyle("Sheet1", "A1", "R2", titleStyle) // 设置列宽 f.SetColWidth("Sheet1", "A", "B", 30) f.SetColWidth("Sheet1", "C", "R", 15) for i, v := range dataList { column := strconv.Itoa(i + 3) f.SetCellValue("Sheet1", "A"+column, v.ProductId) f.SetCellValue("Sheet1", "B"+column, v.ProductName) f.SetCellValue("Sheet1", "C"+column, v.BeginAmount) for _, v := range v.BeginMoreUnitsArr { switch v.Unit { case "件": f.SetCellValue("Sheet1", "D"+column, v.Amount) case "匹": f.SetCellValue("Sheet1", "E"+column, v.Amount) case "米": f.SetCellValue("Sheet1", "F"+column, v.Amount) } } f.SetCellValue("Sheet1", "G"+column, v.InputAmount) for _, v := range v.InputMoreUnitsArr { switch v.Unit { case "件": f.SetCellValue("Sheet1", "H"+column, v.Amount) case "匹": f.SetCellValue("Sheet1", "I"+column, v.Amount) case "米": f.SetCellValue("Sheet1", "J"+column, v.Amount) } } f.SetCellValue("Sheet1", "K"+column, v.OutputAmount) for _, v := range v.OutputMoreUnitsArr { switch v.Unit { case "件": f.SetCellValue("Sheet1", "L"+column, v.Amount) case "匹": f.SetCellValue("Sheet1", "M"+column, v.Amount) case "米": f.SetCellValue("Sheet1", "N"+column, v.Amount) } } f.SetCellValue("Sheet1", "O"+column, v.EndAmount) for _, v := range v.EndMoreUnitsArr { switch v.Unit { case "件": f.SetCellValue("Sheet1", "P"+column, v.Amount) case "匹": f.SetCellValue("Sheet1", "Q"+column, v.Amount) case "米": f.SetCellValue("Sheet1", "R"+column, v.Amount) } } } fileName := fmt.Sprintf("月度统计报表%s.xlsx", time.Now().Format("2006-01-02-1504")) if err := f.SaveAs(fileName); err != nil { return fileName, err } return fileName, nil } func GetCurrentStats(date string, productIds []string) (statRecords []*models.MonthStats, err error) { //本月期初数量/上月结余数量 groupSumList, err := models.NewLocationProductAmountSearch().SetProductIds(productIds).GroupSum("product_id", "amount") productIds = make([]string, 0, len(groupSumList)) for _, groupSum := range groupSumList { productIds = append(productIds, groupSum.Class) } products, err := models.NewMaterialSearch().SetFields("id, name, unit, weight, more_unit, more_unit_value").SetIDs(productIds).FindNotTotal() if err != nil { logx.Errorf("MonthStats GetCurrentStats get products err:%v", err) return } productMap := models.MaterialMap(products) //按配置取开始时间和结束时间 beginTime, endTime, err := NewSystemConfigService().GetInventoryCutOffTime() if err != nil { logx.Errorf("MonthStats GetCurrentStats get GetInventoryCutOffTime err:%v", err) return } inputMap, err := GetStatsByOperationType(beginTime, endTime, constvar.BaseOperationTypeIncoming) if err != nil { logx.Errorf("MonthStats GetStatsByOperationType input err:%v", err) return } outputMap, err := GetStatsByOperationType(beginTime, endTime, constvar.BaseOperationTypeOutgoing) if err != nil { logx.Errorf("MonthStats GetStatsByOperationType output err:%v", err) return } for _, groupSum := range groupSumList { productId := groupSum.Class if productMap[productId] == nil { continue } product := productMap[productId] amount := groupSum.Sum record := models.MonthStats{ ProductId: productId, ProductName: product.Name, Unit: product.Unit, Weight: product.Weight.Mul(amount), Date: date, } var ( moreUnits string inputMoreUnits string outputMoreUnits string ) if *product.MoreUnit { moreValueArr := make([]models.UnitItems, 0, len(product.MoreUnitList)) inputMoreValueArr := make([]models.UnitItems, 0, len(product.MoreUnitList)) outputMoreValueArr := make([]models.UnitItems, 0, len(product.MoreUnitList)) if !amount.IsZero() { moreValueArr = CreateMoreUnit(amount, product.MoreUnitList) } if !inputMap[productId].IsZero() { inputMoreValueArr = CreateMoreUnit(inputMap[productId], product.MoreUnitList) } if !outputMap[productId].IsZero() { outputMoreValueArr = CreateMoreUnit(outputMap[productId], product.MoreUnitList) } bys, _ := json.Marshal(moreValueArr) moreUnits = string(bys) bys, _ = json.Marshal(inputMoreValueArr) inputMoreUnits = string(bys) bys, _ = json.Marshal(outputMoreValueArr) outputMoreUnits = string(bys) } record.InputAmount = inputMap[productId] record.InputMoreUnits = inputMoreUnits record.OutputAmount = outputMap[productId] record.OutputMoreUnits = outputMoreUnits record.EndAmount = amount record.EndMoreUnits = moreUnits statRecords = append(statRecords, &record) } return } func GetStatsByOperationType(beginTime, endTime time.Time, operationType constvar.BaseOperationType) (m map[string]decimal.Decimal, err error) { operationIds, err := models.NewOperationSearch().SetBaseOperationType(operationType).SetFields("id").SetTimeBetween(beginTime, endTime).FindIds() if err != nil { return } groupSumList, err := models.NewOperationDetailsSearch().SetOperationIds(operationIds).SetFields("product_id, amount").GroupSum("product_id", "amount") if err != nil { return } m = make(map[string]decimal.Decimal, len(groupSumList)) for _, v := range groupSumList { m[v.Class] = v.Sum } return }