package service
|
|
import (
|
"fmt"
|
"github.com/shopspring/decimal"
|
"github.com/xuri/excelize/v2"
|
"strconv"
|
"time"
|
"wms/constvar"
|
"wms/models"
|
"wms/pkg/logx"
|
"wms/request"
|
)
|
|
type WarehouseMonthFormsService struct{}
|
|
func NewWarehouseMonthFormsService() *WarehouseMonthFormsService {
|
return &WarehouseMonthFormsService{}
|
}
|
|
func (slf *WarehouseMonthFormsService) Query(params request.GetMonthStats) (result []*models.WarehouseMonthStats, err error) {
|
search := slf.BuildSearch(params)
|
search = search.SetPage(params.Page, params.PageSize)
|
return search.FindNotTotal()
|
}
|
|
func (slf *WarehouseMonthFormsService) BuildSearch(params request.GetMonthStats) (search *models.WarehouseMonthStatsSearch) {
|
search = models.NewWarehouseMonthStatsSearch().
|
SetKeyword(params.Keyword).SetDate(params.Date).SetWarehouseId(params.WarehouseID)
|
if params.Preload {
|
search = search.SetPreload(true)
|
}
|
return search
|
}
|
|
func (slf *WarehouseMonthFormsService) Count(params request.GetMonthStats) (total int64, err error) {
|
search := slf.BuildSearch(params)
|
return search.Count()
|
}
|
|
func (slf *WarehouseMonthFormsService) FetchAll(params request.GetMonthStats) (list []*models.WarehouseMonthStats, err error) {
|
total, err := slf.Count(params)
|
if err != nil {
|
return nil, err
|
}
|
list = make([]*models.WarehouseMonthStats, 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 *WarehouseMonthFormsService) Export(dataList []*models.WarehouseMonthStats) (filename string, err error) {
|
// 创建一个新的 Excel 文件
|
f := excelize.NewFile()
|
|
if err != nil {
|
logx.Errorf("NewSheet err:%v", err)
|
return "", err
|
}
|
|
headers, headerLen, inputTypes, outputTypes := slf.GetHeaders()
|
_, err = SetExcelHeaders(headers, f)
|
if err != nil {
|
logx.Errorf("SetExcelHeader err:%v", err)
|
return "", err
|
}
|
|
//表头样式
|
style, err := SetHeaderStyle(f)
|
if err != nil {
|
return "", err
|
}
|
|
f.SetCellStyle("Sheet1", "A1", getColumnAlphabet(headerLen)+"2", style)
|
// 设置列宽
|
f.SetColWidth("Sheet1", "A", "F", 30)
|
f.SetColWidth("Sheet1", "G", getColumnAlphabet(headerLen), 15)
|
|
inputStart := 6
|
outputStart := 6 + len(inputTypes)
|
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.EndAmount)
|
f.SetCellValue("Sheet1", "D"+column, v.BeginAmount)
|
f.SetCellValue("Sheet1", "E"+column, v.Unit)
|
f.SetCellValue("Sheet1", "F"+column, v.SalePrice)
|
|
slf.FillDealerTypeToExcel(v.InputItems, inputStart, i+3, inputTypes, f, constvar.InputTotalHeader)
|
slf.FillDealerTypeToExcel(v.OutputItems, outputStart, i+3, outputTypes, f, constvar.OutPutTotalHeader)
|
}
|
|
fileName := fmt.Sprintf("%s月度统计报表%s.xlsx", "仓库", time.Now().Format("2006-01-02-1504"))
|
if err := f.SaveAs(fileName); err != nil {
|
return fileName, err
|
}
|
|
return fileName, nil
|
}
|
|
func (slf *WarehouseMonthFormsService) GetHeaders() (headers []interface{}, headerLen int, inputTypes, outputTypes []string) {
|
// 自定义表头
|
|
//查询入库类型
|
inputTypes = GetDictNameListByType(constvar.StorageType)
|
inputTypes = append(inputTypes, constvar.InputTotalHeader)
|
|
headerLen += len(inputTypes)
|
|
//查询入库类型
|
outputTypes = GetDictNameListByType(constvar.StockoutType)
|
outputTypes = append(outputTypes, constvar.OutPutTotalHeader)
|
|
headerLen += len(inputTypes)
|
|
headerLen += 6
|
|
headers = []interface{}{"物料编号", "物料编码", "月末结存", "月初结存", "单位", "单价", map[string][]string{"本月入库": inputTypes}, map[string][]string{"本月出库": outputTypes}}
|
return headers, headerLen, inputTypes, outputTypes
|
}
|
|
func (slf *WarehouseMonthFormsService) SumItems(items []*models.WarehouseStatsItems) (sum decimal.Decimal) {
|
for _, v := range items {
|
sum = sum.Add(v.Amount)
|
}
|
return sum
|
}
|
|
func (slf *WarehouseMonthFormsService) FillDealerTypeToExcel(items []*models.WarehouseStatsItems, startIndex int, column int, dealerTypes []string, f *excelize.File, totalHeader string) {
|
columnStr := strconv.Itoa(column)
|
sum := slf.SumItems(items)
|
detailMap := models.WarehouseStatsItemMap(items)
|
for k := range detailMap {
|
if k == "" {
|
detailMap["其他"] = detailMap[k]
|
delete(detailMap, k)
|
}
|
}
|
|
for i := 0; i < len(dealerTypes); i++ {
|
var amount decimal.Decimal
|
if detailMap[dealerTypes[i]] != nil {
|
amount = detailMap[dealerTypes[i]].Amount
|
} else if dealerTypes[i] == totalHeader {
|
amount = sum
|
}
|
f.SetCellValue("Sheet1", getColumnAlphabet(startIndex+i)+columnStr, amount)
|
}
|
return
|
}
|
|
func GetCurrentWarehouseStats(date string, warehouseId int, productIds []string, realTime bool) (statRecords []*models.WarehouseMonthStats, 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 realTime {
|
beginTime = endTime
|
endTime = time.Now()
|
}
|
if err != nil {
|
logx.Errorf("MonthStats GetCurrentStats get GetInventoryCutOffTime err:%v", err)
|
return
|
}
|
inputMap, err := GetStatsMulti(beginTime, endTime, constvar.BaseOperationTypeIncoming, warehouseId)
|
if err != nil {
|
logx.Errorf("MonthStats GetStatsByOperationType input err:%v", err)
|
return
|
}
|
|
outputMap, err := GetStatsMulti(beginTime, endTime, constvar.BaseOperationTypeOutgoing, warehouseId)
|
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.WarehouseMonthStats{
|
WarehouseId: warehouseId,
|
ProductId: productId,
|
ProductName: product.Name,
|
Unit: product.Unit,
|
SalePrice: product.SalePrice,
|
EndAmount: amount,
|
InputAmount: SumMapAmount(inputMap[productId]),
|
InputItems: GetDealerItems(inputMap[productId], models.MonthStatsItemsTypeInput),
|
OutputAmount: SumMapAmount(outputMap[productId]),
|
OutputItems: GetDealerItems(outputMap[productId], models.MonthStatsItemsTypeOutput),
|
Date: date,
|
}
|
statRecords = append(statRecords, &record)
|
}
|
|
return
|
}
|
|
func GetStatsMulti(beginTime, endTime time.Time, operationType constvar.BaseOperationType, warehouseId int) (m map[string]map[string]decimal.Decimal, err error) {
|
m = make(map[string]map[string]decimal.Decimal)
|
operationIds, err := models.NewOperationSearch().SetBaseOperationType(operationType).
|
SetFields("id").SetTimeBetween(beginTime, endTime).
|
SetWarehouseId(warehouseId).
|
FindIds()
|
if err != nil {
|
return
|
}
|
if len(operationIds) == 0 {
|
return
|
}
|
groupSumList, err := models.NewOperationDetailsSearch().SetOperationIds(operationIds).
|
SetFields("product_id, dealer_type, amount").
|
GroupMultiSumAmount()
|
if err != nil {
|
return
|
}
|
m = make(map[string]map[string]decimal.Decimal)
|
for _, v := range groupSumList {
|
if m[v.ProductID] == nil {
|
m[v.ProductID] = make(map[string]decimal.Decimal)
|
}
|
m[v.ProductID][v.DealerType] = v.Sum
|
}
|
return
|
}
|
|
func SumMapAmount(m map[string]decimal.Decimal) (sum decimal.Decimal) {
|
for _, v := range m {
|
sum = sum.Add(v)
|
}
|
return
|
}
|
|
func GetDealerItems(m map[string]decimal.Decimal, tp models.MonthStatsItemsType) (items []*models.WarehouseStatsItems) {
|
for k, v := range m {
|
name := k
|
if name == "" {
|
name = "其他"
|
}
|
items = append(items, &models.WarehouseStatsItems{
|
Type: tp,
|
Name: name,
|
Amount: v,
|
})
|
}
|
return
|
}
|
