package service
|
|
import (
|
"fmt"
|
"github.com/shopspring/decimal"
|
"github.com/xuri/excelize/v2"
|
"strconv"
|
"time"
|
"wms/constvar"
|
"wms/models"
|
"wms/request"
|
"wms/response"
|
)
|
|
type Detail struct {
|
ProductId string `json:"productId"`
|
Amount decimal.Decimal `json:"amount"`
|
Status constvar.OperationStatus `json:"status"`
|
//ProductName string `json:"productName"`
|
}
|
|
type InventoryReportFormsService struct{}
|
|
func NewInventoryReportFormsService() *InventoryReportFormsService {
|
return &InventoryReportFormsService{}
|
}
|
|
func (slf *InventoryReportFormsService) Query(params request.GetInventoryForms) (result []*response.InventoryForms, err error) {
|
var (
|
materials = make([]*models.Material, 0)
|
)
|
search, productIds, productAmounts, locationIds, err := slf.BuildSearch(params)
|
if err != nil {
|
return nil, err
|
}
|
|
if params.Page*params.PageSize > 0 {
|
search.Orm = search.Orm.Offset((params.Page - 1) * params.PageSize).Limit(params.PageSize)
|
}
|
if err := search.Orm.Find(&materials).Error; err != nil {
|
return nil, err
|
}
|
//查询在库数量
|
if len(productIds) == 0 {
|
for _, material := range materials {
|
productIds = append(productIds, material.ID)
|
}
|
productAmounts, err = models.NewLocationProductAmountSearch().SetProductIds(productIds).SetLocationIds(locationIds).Find()
|
if err != nil {
|
return nil, err
|
}
|
}
|
|
//查询出入库就绪数量
|
var inHouse []Detail
|
var outHouse []Detail
|
dbIn := models.NewOperationDetailsSearch().Orm.Model(&models.OperationDetails{}).
|
Select("wms_operation_details.product_id,wms_operation_details.amount,wms_operation.status as status").
|
Joins("left join wms_operation ON wms_operation_details.operation_id=wms_operation.id").
|
Where("wms_operation.base_operation_type in (?)", []constvar.BaseOperationType{constvar.BaseOperationTypeIncoming, constvar.BaseOperationTypeInternal}).
|
Where("wms_operation.status in (?)", []constvar.OperationStatus{constvar.OperationStatus_Finish})
|
dbOut := models.NewOperationDetailsSearch().Orm.Model(&models.OperationDetails{}).
|
Select("wms_operation_details.product_id,wms_operation_details.amount,wms_operation.status as status").
|
Joins("left join wms_operation ON wms_operation_details.operation_id=wms_operation.id").
|
Where("wms_operation.base_operation_type in (?)", []constvar.BaseOperationType{constvar.BaseOperationTypeOutgoing, constvar.BaseOperationTypeInternal, constvar.BaseOperationTypeDisuse}).
|
Where("wms_operation.status in (?)", []constvar.OperationStatus{constvar.OperationStatus_Ready, constvar.OperationStatus_Finish})
|
if len(locationIds) > 0 {
|
dbIn.Where("wms_operation_details.to_location_id in (?)", locationIds)
|
dbOut.Where("wms_operation_details.from_location_id in (?)", locationIds)
|
}
|
if len(productIds) > 0 {
|
dbIn.Where("wms_operation_details.product_id in (?)", productIds)
|
dbOut.Where("wms_operation_details.product_id in (?)", productIds)
|
}
|
err = dbIn.Find(&inHouse).Error
|
if err != nil {
|
return
|
}
|
err = dbOut.Find(&outHouse).Error
|
if err != nil {
|
return
|
}
|
for _, material := range materials {
|
data := new(response.InventoryForms)
|
data.ProduceId = material.ID
|
data.ProductName = material.Name
|
data.Cost = material.Cost
|
data.Unit = material.Unit
|
data.Value = material.Amount.Mul(material.Cost)
|
data.ProductType = material.CategoryName
|
for _, amount := range productAmounts {
|
if material.ID == amount.ProductId {
|
data.Amount = data.Amount.Add(amount.Amount)
|
}
|
}
|
for _, details := range inHouse {
|
if material.ID == details.ProductId {
|
data.In = data.In.Add(details.Amount)
|
}
|
}
|
available := decimal.NewFromInt(0)
|
for _, details := range outHouse {
|
if material.ID == details.ProductId {
|
if details.Status == constvar.OperationStatus_Ready {
|
available = available.Add(details.Amount)
|
} else {
|
data.Out = data.Out.Add(details.Amount)
|
}
|
}
|
}
|
data.AvailableNumber = data.Amount.Sub(available)
|
|
if *material.MoreUnit {
|
data.AmountMoreUnits = CreateMoreUnit(data.Amount, material.MoreUnitList)
|
data.AvailableNumberMoreUnits = CreateMoreUnit(data.AvailableNumber, material.MoreUnitList)
|
data.InMoreUnits = CreateMoreUnit(data.In, material.MoreUnitList)
|
data.OutMoreUnits = CreateMoreUnit(data.Out, material.MoreUnitList)
|
}
|
|
result = append(result, data)
|
}
|
return
|
}
|
|
func (slf *InventoryReportFormsService) BuildSearch(params request.GetInventoryForms) (
|
search *models.MaterialSearch,
|
productIds []string,
|
productAmounts []*models.LocationProductAmount,
|
locationIds []int,
|
err error) {
|
productAmounts = make([]*models.LocationProductAmount, 0)
|
locationSearch := models.NewLocationSearch()
|
if params.WarehouseCode != "" {
|
locationSearch.SetJointName(params.WarehouseCode)
|
}
|
|
locations, err := locationSearch.FindNotTotal()
|
if err != nil {
|
return nil, nil, nil, nil, err
|
}
|
|
for _, location := range locations {
|
locationIds = append(locationIds, location.Id)
|
}
|
productAmounts, err = models.NewLocationProductAmountSearch().
|
SetLocationIds(locationIds).SetQuery("amount > 0").
|
Find()
|
if err != nil {
|
return nil, nil, nil, nil, err
|
}
|
for _, amount := range productAmounts {
|
productIds = append(productIds, amount.ProductId)
|
}
|
|
//查询产品
|
search = models.NewMaterialSearch()
|
search.Orm = search.Orm.Model(&models.Material{}).
|
Select(`material.id, material.name, material.cost, material.amount, material.unit, material.more_unit, material.more_unit_value, wms_product_category.name as category_name `).
|
Joins("left join wms_product_category on material.category_id = wms_product_category.id")
|
if len(params.CategoryIds) > 0 {
|
search.Orm.Where("material.category_id in (?)", params.CategoryIds)
|
}
|
if params.KeyWord != "" {
|
search.Orm.Where("material.name like ?", "%"+params.KeyWord+"%").Or("wms_product_category.name like ?", "%"+params.KeyWord+"%")
|
}
|
if len(productIds) > 0 {
|
search.Orm.Where("material.id in (?)", productIds)
|
}
|
|
return search, productIds, productAmounts, locationIds, nil
|
}
|
|
func (slf *InventoryReportFormsService) Count(params request.GetInventoryForms) (total int64, err error) {
|
search, _, _, _, err := slf.BuildSearch(params)
|
if err != nil {
|
return 0, err
|
}
|
if err := search.Orm.Count(&total).Error; err != nil {
|
return 0, err
|
}
|
return total, nil
|
}
|
|
func (slf *InventoryReportFormsService) FetchAll(params request.GetInventoryForms) (list []*response.InventoryForms, err error) {
|
total, err := slf.Count(params)
|
if err != nil {
|
return nil, err
|
}
|
list = make([]*response.InventoryForms, 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 *InventoryReportFormsService) Export(dataList []*response.InventoryForms) (filename string, err error) {
|
var fileName string
|
f := excelize.NewFile()
|
|
// 自定义表头
|
headers := []string{"产品", "产品类别", "单位成本", "总价值", "在库", "可用库存", "入库", "出库"}
|
// 设置表头
|
for i, header := range headers {
|
cell := getColumnAlphabet(i) + "1"
|
f.SetCellValue("Sheet1", cell, header)
|
}
|
|
f.SetCellValue("Sheet1", getColumnAlphabet(0)+"1", "产品")
|
f.SetCellValue("Sheet1", getColumnAlphabet(1)+"1", "产品类别")
|
f.SetCellValue("Sheet1", getColumnAlphabet(2)+"1", "单位成本")
|
f.SetCellValue("Sheet1", getColumnAlphabet(3)+"1", "总价值")
|
f.SetCellValue("Sheet1", getColumnAlphabet(4)+"1", "在库")
|
f.MergeCell("Sheet1", getColumnAlphabet(4)+"1", getColumnAlphabet(7)+"1") // 合并单元格
|
f.SetCellValue("Sheet1", getColumnAlphabet(8)+"1", "可用库存")
|
f.MergeCell("Sheet1", getColumnAlphabet(8)+"1", getColumnAlphabet(11)+"1") // 合并单元格
|
f.SetCellValue("Sheet1", getColumnAlphabet(12)+"1", "入库")
|
f.MergeCell("Sheet1", getColumnAlphabet(12)+"1", getColumnAlphabet(15)+"1") // 合并单元格
|
f.SetCellValue("Sheet1", getColumnAlphabet(16)+"1", "出库")
|
f.MergeCell("Sheet1", getColumnAlphabet(16)+"1", getColumnAlphabet(19)+"1") // 合并单元格
|
|
unitData := []string{"件", "匹", "米", "重量"}
|
for i, header := range unitData {
|
f.SetCellValue("Sheet1", getColumnAlphabet(i+4)+"2", header)
|
f.SetCellValue("Sheet1", getColumnAlphabet(i+8)+"2", header)
|
f.SetCellValue("Sheet1", getColumnAlphabet(i+12)+"2", header)
|
f.SetCellValue("Sheet1", getColumnAlphabet(i+16)+"2", 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
|
}
|
|
lastColumn := getColumnAlphabet(19)
|
f.SetCellStyle("Sheet1", "A1", lastColumn+"2", titleStyle)
|
// 设置列宽
|
f.SetColWidth("Sheet1", "A", "D", 30)
|
f.SetColWidth("Sheet1", "E", lastColumn, 15)
|
|
for i, v := range dataList {
|
column := strconv.Itoa(i + 3)
|
f.SetCellValue("Sheet1", getColumnAlphabet(0)+column, v.ProductName)
|
f.SetCellValue("Sheet1", getColumnAlphabet(1)+column, v.ProductType)
|
f.SetCellValue("Sheet1", getColumnAlphabet(2)+column, v.Cost)
|
f.SetCellValue("Sheet1", getColumnAlphabet(3)+column, v.Value)
|
|
f.SetCellValue("Sheet1", getColumnAlphabet(4)+column, v.Amount)
|
FillMoreUnitToExcel(v.Amount, v.AmountMoreUnits, 5, i+3, f)
|
|
f.SetCellValue("Sheet1", getColumnAlphabet(8)+column, v.AvailableNumber)
|
FillMoreUnitToExcel(v.AvailableNumber, v.AvailableNumberMoreUnits, 9, i+3, f)
|
|
f.SetCellValue("Sheet1", getColumnAlphabet(12)+column, v.In)
|
FillMoreUnitToExcel(v.In, v.AmountMoreUnits, 13, i+3, f)
|
|
f.SetCellValue("Sheet1", getColumnAlphabet(16)+column, v.Out)
|
FillMoreUnitToExcel(v.Out, v.AmountMoreUnits, 17, i+3, f)
|
}
|
|
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
|
}
|
|
// 根据索引获取 Excel 列名（A、B、C...）
|
func getColumnAlphabet(index int) string {
|
const alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
|
if index <= 26 {
|
return string(alphabet[index])
|
}
|
return getColumnAlphabet((index)/26) + getColumnAlphabet((index)%26+1)
|
}
|
