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
|
}
|
|
unitData, unitIndexMap := GetAllUnits()
|
unitLen := len(unitData)
|
|
// 自定义表头
|
headers := []interface{}{"产品编码", "产品名称", map[string][]string{"期初库存": unitData}, map[string][]string{"本月入库": unitData}, map[string][]string{"本月出库": unitData}, map[string][]string{"期末库存": unitData}}
|
lastColumnNumber, err := SetExcelHeaders(headers, f)
|
if err != nil {
|
return "", err
|
}
|
|
f.MergeCell("Sheet1", "A1", "A2") // 合并单元格
|
f.MergeCell("Sheet1", "B1", "B2") // 合并单元格
|
|
titleStyle, err := SetHeaderStyle(f)
|
if err != nil {
|
return "", err
|
}
|
|
lastColumn := getColumnAlphabet(lastColumnNumber)
|
f.SetCellStyle("Sheet1", "A1", lastColumn+"2", titleStyle)
|
// 设置列宽
|
f.SetColWidth("Sheet1", "A", "B", 30)
|
f.SetColWidth("Sheet1", "C", lastColumn, 15)
|
|
for i, v := range dataList {
|
row := strconv.Itoa(i + 3)
|
f.SetCellValue("Sheet1", "A"+row, v.ProductId)
|
f.SetCellValue("Sheet1", "B"+row, v.ProductName)
|
|
v.BeginMoreUnitsArr = append(v.BeginMoreUnitsArr, models.UnitItems{Amount: v.BeginAmount, Unit: v.Unit})
|
for _, v := range v.BeginMoreUnitsArr {
|
f.SetCellValue("Sheet1", getColumnAlphabet(3+unitIndexMap[v.Unit])+row, v.Amount)
|
}
|
|
v.InputMoreUnitsArr = append(v.InputMoreUnitsArr, models.UnitItems{Amount: v.InputAmount, Unit: v.Unit})
|
for _, v := range v.InputMoreUnitsArr {
|
f.SetCellValue("Sheet1", getColumnAlphabet(3+unitLen*1+unitIndexMap[v.Unit])+row, v.Amount)
|
}
|
|
v.OutputMoreUnitsArr = append(v.OutputMoreUnitsArr, models.UnitItems{Amount: v.OutputAmount, Unit: v.Unit})
|
for _, v := range v.OutputMoreUnitsArr {
|
f.SetCellValue("Sheet1", getColumnAlphabet(3+unitLen*2+unitIndexMap[v.Unit])+row, v.Amount)
|
}
|
|
v.EndMoreUnitsArr = append(v.EndMoreUnitsArr, models.UnitItems{Amount: v.EndAmount, Unit: v.Unit})
|
for _, v := range v.EndMoreUnitsArr {
|
f.SetCellValue("Sheet1", getColumnAlphabet(3+unitLen*3+unitIndexMap[v.Unit])+row, 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, inputProductMoreUnit, err := GetStatsByOperationType(beginTime, endTime, productIds, constvar.BaseOperationTypeIncoming)
|
if err != nil {
|
logx.Errorf("MonthStats GetStatsByOperationType input err:%v", err)
|
return
|
}
|
|
outputMap, outputProductMoreUnit, err := GetStatsByOperationType(beginTime, endTime, productIds, constvar.BaseOperationTypeOutgoing)
|
if err != nil {
|
logx.Errorf("MonthStats GetStatsByOperationType output err:%v", err)
|
return
|
}
|
|
productMoreUnit, err := GetProductMoreUnitMap(productIds)
|
|
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 = productMoreUnit[productId]
|
}
|
if !inputMap[productId].IsZero() {
|
inputMoreValueArr = inputProductMoreUnit[productId]
|
}
|
|
if !outputMap[productId].IsZero() {
|
outputMoreValueArr = outputProductMoreUnit[productId]
|
}
|
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, productIds []string, operationType constvar.BaseOperationType) (m map[string]decimal.Decimal, productMoreUnit map[string][]models.UnitItems, err error) {
|
operationIds, err := models.NewOperationSearch().SetBaseOperationType(operationType).SetFields("id").SetTimeBetween(beginTime, endTime).FindIds()
|
if err != nil {
|
return
|
}
|
|
productMoreUnit = make(map[string][]models.UnitItems)
|
detailProducts, err := models.NewOperationDetailsSearch().
|
SetProductIds(productIds).SetOperationIds(operationIds).
|
SetFields("product_id, amount, more_unit_value").FindAll()
|
m = make(map[string]decimal.Decimal, 0)
|
if err != nil {
|
logx.Errorf("MonthStats GetMoreUnitRecords err:%v", err)
|
return
|
}
|
|
for _, v := range detailProducts {
|
m[v.ProductId] = m[v.ProductId].Add(v.Amount)
|
if v.MoreUnitValue == "" {
|
continue
|
}
|
if productMoreUnit[v.ProductId] == nil {
|
productMoreUnit[v.ProductId] = v.MoreUnitList
|
continue
|
}
|
productMoreUnit[v.ProductId] = AddMoreUnit(productMoreUnit[v.ProductId], v.MoreUnitList)
|
}
|
|
return
|
}
|
|
func GetProductMoreUnitMap(productIds []string) (m map[string][]models.UnitItems, err error) {
|
m = make(map[string][]models.UnitItems)
|
locAmountRecords, err := models.NewLocationProductAmountSearch().SetProductIds(productIds).
|
SetFields("product_id, more_unit_value").FindAll()
|
if err != nil {
|
logx.Errorf("MonthStats GetMoreUnitRecords err:%v", err)
|
return
|
}
|
|
for _, v := range locAmountRecords {
|
if v.MoreUnitValue == "" {
|
continue
|
}
|
if m[v.ProductId] == nil {
|
m[v.ProductId] = v.MoreUnitList
|
continue
|
}
|
m[v.ProductId] = AddMoreUnit(m[v.ProductId], v.MoreUnitList)
|
}
|
return
|
}
|
