372 lines
12 KiB
Go
372 lines
12 KiB
Go
package controllers
|
|
|
|
import (
|
|
"strings"
|
|
"time"
|
|
|
|
"server/models"
|
|
)
|
|
|
|
// ERP 库存查询核心（两端共用）：结存、流水、批次、预警、补货建议的算法只在这里实现一份，
|
|
// /backend/erp/stock* 与 /api/erp/stock* 的 handler 只负责取参数与输出各自的响应信封，
|
|
// 保证「手机上看到的库存数」和「Web 端看到的」永远是同一套口径。
|
|
|
|
// erpStockFilter 结存查询条件
|
|
type erpStockFilter struct {
|
|
WarehouseID uint64
|
|
ProductID uint64
|
|
BatchNo string
|
|
OnlyStock bool
|
|
GroupByProduct bool
|
|
Keyword string
|
|
CategoryID uint64
|
|
SafetyState string
|
|
}
|
|
|
|
// erpStockListCore 库存结存列表（可按商品聚合），返回当前页、总数与金额合计
|
|
func erpStockListCore(a erpActor, f erpStockFilter, page, pageSize int) ([]erpStockRow, int64, float64, int, string) {
|
|
qs := models.Orm.QueryTable(new(models.ErpStock)).Filter("tenant_id", a.Tid)
|
|
if f.WarehouseID > 0 {
|
|
qs = qs.Filter("warehouse_id", f.WarehouseID)
|
|
}
|
|
if f.ProductID > 0 {
|
|
qs = qs.Filter("product_id", f.ProductID)
|
|
}
|
|
if f.BatchNo != "" {
|
|
qs = qs.Filter("batch_no", f.BatchNo)
|
|
}
|
|
if f.OnlyStock {
|
|
qs = qs.Filter("quantity__gt", 0)
|
|
}
|
|
|
|
var rows []models.ErpStock
|
|
if _, err := qs.OrderBy("product_id", "warehouse_id", "batch_no").All(&rows); err != nil {
|
|
return nil, 0, 0, 500, "查询失败: " + err.Error()
|
|
}
|
|
pmap := erpProductMap(a.Tid, erpStockProductIDs(rows))
|
|
wmap := erpWarehouseNameMap(a.Tid)
|
|
cats := erpCategoryMap(a.Tid)
|
|
|
|
list := make([]erpStockRow, 0, len(rows))
|
|
agg := map[uint64]int{} // 按商品聚合时的数量合计
|
|
for _, s := range rows {
|
|
p := pmap[s.ProductID]
|
|
amount := round2f(s.Quantity * s.CostPrice)
|
|
if f.GroupByProduct {
|
|
if i, hit := agg[s.ProductID]; hit {
|
|
r := &list[i]
|
|
r.Quantity = round4f(r.Quantity + s.Quantity)
|
|
// 加权成本 = 合计金额 / 合计数量
|
|
base := r.Amount
|
|
r.Amount = round2f(base + amount)
|
|
if r.Quantity > 0.000001 {
|
|
r.CostPrice = round2f(r.Amount / r.Quantity)
|
|
}
|
|
r.SafetyState = erpSafetyState(r.SafetyQty, r.Quantity, r.MaxQty)
|
|
continue
|
|
}
|
|
agg[s.ProductID] = len(list)
|
|
}
|
|
row := erpStockRow{
|
|
ProductID: s.ProductID, ProductCode: p.ProductCode, ProductName: p.ProductName,
|
|
Spec: p.Spec, Unit: p.BaseUnit, CategoryName: cats[p.CategoryID],
|
|
WarehouseID: s.WarehouseID, WarehouseName: wmap[s.WarehouseID], BatchNo: s.BatchNo,
|
|
Quantity: round4f(s.Quantity), CostPrice: round2f(s.CostPrice), Amount: amount,
|
|
SafetyQty: p.SafetyQty, MaxQty: p.MaxQty,
|
|
}
|
|
row.SafetyState = erpSafetyState(row.SafetyQty, row.Quantity, row.MaxQty)
|
|
list = append(list, row)
|
|
}
|
|
|
|
// 商品维度过滤（关键字/分类）在聚合后做，避免 SQL 里 JOIN 档案表
|
|
if f.Keyword != "" {
|
|
kw := f.Keyword
|
|
list = erpFilterRows(list, func(r erpStockRow) bool {
|
|
return strings.Contains(r.ProductName, kw) || strings.Contains(r.ProductCode, kw) ||
|
|
strings.Contains(r.Spec, kw) || strings.Contains(r.BatchNo, kw)
|
|
})
|
|
}
|
|
if f.CategoryID > 0 {
|
|
list = erpFilterRows(list, func(r erpStockRow) bool {
|
|
return pmap[r.ProductID].CategoryID == f.CategoryID
|
|
})
|
|
}
|
|
if f.SafetyState != "" {
|
|
list = erpFilterRows(list, func(r erpStockRow) bool { return r.SafetyState == f.SafetyState })
|
|
}
|
|
|
|
total := int64(len(list))
|
|
start, end := erpPageWindow(len(list), page, pageSize)
|
|
var sumAmount float64
|
|
for _, r := range list {
|
|
sumAmount += r.Amount
|
|
}
|
|
return list[start:end], total, round2f(sumAmount), 200, ""
|
|
}
|
|
|
|
// erpLedgerFilter 库存流水查询条件
|
|
type erpLedgerFilter struct {
|
|
ProductID uint64
|
|
WarehouseID uint64
|
|
DocType string
|
|
BatchNo string
|
|
DocNo string
|
|
DateFrom string
|
|
DateTo string
|
|
}
|
|
|
|
// erpLedgerCore 库存流水（收发存明细追溯）
|
|
func erpLedgerCore(a erpActor, f erpLedgerFilter, page, pageSize int) ([]models.ErpStockLedger, int64, int, string) {
|
|
qs := models.Orm.QueryTable(new(models.ErpStockLedger)).Filter("tenant_id", a.Tid)
|
|
if f.ProductID > 0 {
|
|
qs = qs.Filter("product_id", f.ProductID)
|
|
}
|
|
if f.WarehouseID > 0 {
|
|
qs = qs.Filter("warehouse_id", f.WarehouseID)
|
|
}
|
|
if f.DocType != "" && erpDocTypes[f.DocType] {
|
|
qs = qs.Filter("doc_type", f.DocType)
|
|
}
|
|
if f.BatchNo != "" {
|
|
qs = qs.Filter("batch_no", f.BatchNo)
|
|
}
|
|
if f.DocNo != "" {
|
|
qs = qs.Filter("doc_no__contains", f.DocNo)
|
|
}
|
|
if f.DateFrom != "" {
|
|
if t, e := time.ParseInLocation("2006-01-02", f.DateFrom, time.Local); e == nil {
|
|
qs = qs.Filter("create_time__gte", t)
|
|
}
|
|
}
|
|
if f.DateTo != "" {
|
|
if t, e := time.ParseInLocation("2006-01-02", f.DateTo, time.Local); e == nil {
|
|
qs = qs.Filter("create_time__lt", t.AddDate(0, 0, 1))
|
|
}
|
|
}
|
|
|
|
total, _ := qs.Count()
|
|
var rows []models.ErpStockLedger
|
|
if _, err := qs.OrderBy("-id").Limit(pageSize, (page-1)*pageSize).All(&rows); err != nil {
|
|
return nil, 0, 500, "查询失败: " + err.Error()
|
|
}
|
|
pmap := erpProductMap(a.Tid, erpLedgerProductIDs(rows))
|
|
wmap := erpWarehouseNameMap(a.Tid)
|
|
for i := range rows {
|
|
p := pmap[rows[i].ProductID]
|
|
rows[i].ProductName = p.ProductName
|
|
rows[i].ProductCode = p.ProductCode
|
|
rows[i].Spec = p.Spec
|
|
rows[i].BaseUnit = p.BaseUnit
|
|
rows[i].WarehouseName = wmap[rows[i].WarehouseID]
|
|
}
|
|
return rows, total, 200, ""
|
|
}
|
|
|
|
// erpWarningCore 库存预警：低于安全库存 / 超储 / 零库存 / 效期临近
|
|
func erpWarningCore(a erpActor) (map[string]interface{}, int, string) {
|
|
tid := a.Tid
|
|
var products []models.ErpProduct
|
|
_, _ = models.Orm.QueryTable(new(models.ErpProduct)).
|
|
Filter("tenant_id", tid).Filter("status", 1).Filter("is_draft", 0).
|
|
Filter("delete_time__isnull", true).All(&products)
|
|
var stocks []models.ErpStock
|
|
_, _ = models.Orm.QueryTable(new(models.ErpStock)).
|
|
Filter("tenant_id", tid).All(&stocks)
|
|
qtyMap := map[uint64]float64{}
|
|
for _, s := range stocks {
|
|
qtyMap[s.ProductID] += s.Quantity
|
|
}
|
|
// 在途量：已生效未执行完的采购订单剩余数量，用于判断"缺货但已在途"
|
|
inTransit := erpPurchaseInTransit(tid)
|
|
cats := erpCategoryMap(tid)
|
|
|
|
low, over, out := make([]erpWarnRow, 0), make([]erpWarnRow, 0), make([]erpWarnRow, 0)
|
|
for _, p := range products {
|
|
q := qtyMap[p.ID]
|
|
row := erpWarnRow{
|
|
ProductID: p.ID, ProductCode: p.ProductCode, ProductName: p.ProductName,
|
|
Spec: p.Spec, Unit: p.BaseUnit, CategoryName: cats[p.CategoryID],
|
|
Quantity: round4f(q), SafetyQty: p.SafetyQty, MaxQty: p.MaxQty,
|
|
InTransit: round4f(inTransit[p.ID]),
|
|
}
|
|
switch {
|
|
case p.SafetyQty > 0 && q < p.SafetyQty:
|
|
row.Type, row.TypeLabel = "low", "低于安全库存"
|
|
// 补货建议：补到安全库存的1.5倍，扣除在途
|
|
target := round4f(p.SafetyQty * 1.5)
|
|
if p.MaxQty > target {
|
|
target = round4f(p.MaxQty)
|
|
}
|
|
sug := target - q - inTransit[p.ID]
|
|
if sug > 0 {
|
|
row.SuggestQty = round4f(sug)
|
|
}
|
|
low = append(low, row)
|
|
if q <= 0.000001 {
|
|
o := row
|
|
o.Type, o.TypeLabel = "out", "缺货"
|
|
out = append(out, o)
|
|
}
|
|
case p.MaxQty > 0 && q > p.MaxQty:
|
|
row.Type, row.TypeLabel = "over", "库存积压"
|
|
row.SuggestQty = round4f(q - p.MaxQty)
|
|
over = append(over, row)
|
|
}
|
|
}
|
|
|
|
// 效期预警：按批次结存取最近一次流水的到期日
|
|
expiring := erpExpiringBatches(tid, 30)
|
|
|
|
return map[string]interface{}{
|
|
"low": low, "over": over, "out": out, "expiring": expiring,
|
|
"counts": map[string]int{
|
|
"low": len(low), "over": len(over), "out": len(out), "expiring": len(expiring),
|
|
},
|
|
}, 200, ""
|
|
}
|
|
|
|
// erpReplenishCore 智能补货建议（按供应商分组，可一键生成采购订单）
|
|
func erpReplenishCore(a erpActor) ([]erpReplenishRow, int, string) {
|
|
tid := a.Tid
|
|
var products []models.ErpProduct
|
|
_, _ = models.Orm.QueryTable(new(models.ErpProduct)).
|
|
Filter("tenant_id", tid).Filter("status", 1).Filter("is_draft", 0).
|
|
Filter("delete_time__isnull", true).Filter("safety_qty__gt", 0).All(&products)
|
|
var stocks []models.ErpStock
|
|
_, _ = models.Orm.QueryTable(new(models.ErpStock)).
|
|
Filter("tenant_id", tid).All(&stocks)
|
|
qtyMap := map[uint64]float64{}
|
|
for _, s := range stocks {
|
|
qtyMap[s.ProductID] += s.Quantity
|
|
}
|
|
inTransit := erpPurchaseInTransit(tid)
|
|
// 近期销量（30天销售出库）作为动态补货依据
|
|
sold := erpSoldQty(tid, 30)
|
|
|
|
rows := make([]erpReplenishRow, 0)
|
|
for _, p := range products {
|
|
q := qtyMap[p.ID]
|
|
target := p.SafetyQty * 1.5
|
|
if p.MaxQty > target {
|
|
target = p.MaxQty
|
|
}
|
|
// 有销量数据时按「月销 + 安全库存」定目标，更贴近实际周转
|
|
if s := sold[p.ID]; s > 0 && s > target {
|
|
target = s + p.SafetyQty*0.5
|
|
}
|
|
sug := target - q - inTransit[p.ID]
|
|
if sug <= 0.000001 {
|
|
continue
|
|
}
|
|
sug = round4f(sug)
|
|
rows = append(rows, erpReplenishRow{
|
|
ProductID: p.ID, ProductCode: p.ProductCode, ProductName: p.ProductName,
|
|
Spec: p.Spec, Unit: p.BaseUnit, Quantity: round4f(q), SafetyQty: p.SafetyQty,
|
|
InTransit: round4f(inTransit[p.ID]), Sold30: round4f(sold[p.ID]),
|
|
SuggestQty: sug, UnitPrice: p.PurchasePrice, Amount: round2f(sug * p.PurchasePrice),
|
|
})
|
|
}
|
|
return rows, 200, ""
|
|
}
|
|
|
|
// erpBatchFilter 批次台账查询条件
|
|
type erpBatchFilter struct {
|
|
WarehouseID uint64
|
|
ProductID uint64
|
|
OnlyPositive bool
|
|
Keyword string
|
|
ExpireState string
|
|
}
|
|
|
|
// erpBatchCore 批次与效期台账
|
|
func erpBatchCore(a erpActor, f erpBatchFilter, page, pageSize int) ([]erpBatchRow, int64, int, string) {
|
|
qs := models.Orm.QueryTable(new(models.ErpStock)).
|
|
Filter("tenant_id", a.Tid).Exclude("batch_no", "")
|
|
if f.WarehouseID > 0 {
|
|
qs = qs.Filter("warehouse_id", f.WarehouseID)
|
|
}
|
|
if f.ProductID > 0 {
|
|
qs = qs.Filter("product_id", f.ProductID)
|
|
}
|
|
if f.OnlyPositive {
|
|
qs = qs.Filter("quantity__gt", 0)
|
|
}
|
|
var rows []models.ErpStock
|
|
if _, err := qs.OrderBy("product_id", "warehouse_id", "batch_no").All(&rows); err != nil {
|
|
return nil, 0, 500, "查询失败: " + err.Error()
|
|
}
|
|
|
|
// 批次效期来自入库流水（同批次取最早到期日）
|
|
ids := erpStockProductIDs(rows)
|
|
pmap := erpProductMap(a.Tid, ids)
|
|
expireMap := erpBatchExpiry(a.Tid, ids, pmap)
|
|
wmap := erpWarehouseNameMap(a.Tid)
|
|
all := make([]erpBatchRow, 0, len(rows))
|
|
today := erpToday()
|
|
for _, s := range rows {
|
|
p := pmap[s.ProductID]
|
|
row := erpBatchRow{
|
|
ProductID: s.ProductID, ProductCode: p.ProductCode, ProductName: p.ProductName,
|
|
Spec: p.Spec, Unit: p.BaseUnit, WarehouseID: s.WarehouseID,
|
|
WarehouseName: wmap[s.WarehouseID], BatchNo: s.BatchNo,
|
|
Quantity: round4f(s.Quantity), CostPrice: round2f(s.CostPrice),
|
|
Amount: round2f(s.Quantity * s.CostPrice),
|
|
}
|
|
if t, hit := expireMap[erpBatchKey(s.ProductID, s.BatchNo)]; hit {
|
|
d := int(t.Sub(today).Hours() / 24)
|
|
row.ExpireDate = t.Format("2006-01-02")
|
|
row.ExpireInDays = &d
|
|
row.Expired = d < 0
|
|
}
|
|
all = append(all, row)
|
|
}
|
|
|
|
list := make([]erpBatchRow, 0, len(all))
|
|
for _, r := range all {
|
|
if f.Keyword != "" && !strings.Contains(r.ProductName, f.Keyword) &&
|
|
!strings.Contains(r.ProductCode, f.Keyword) &&
|
|
!strings.Contains(r.BatchNo, f.Keyword) &&
|
|
!strings.Contains(r.Spec, f.Keyword) {
|
|
continue
|
|
}
|
|
if f.ExpireState != "" {
|
|
d := r.ExpireInDays
|
|
switch f.ExpireState {
|
|
case "expired":
|
|
if d == nil || *d >= 0 {
|
|
continue
|
|
}
|
|
case "expiring":
|
|
if d == nil || *d < 0 || *d > 30 {
|
|
continue
|
|
}
|
|
case "normal":
|
|
if d != nil && *d <= 30 {
|
|
continue
|
|
}
|
|
}
|
|
}
|
|
list = append(list, r)
|
|
}
|
|
|
|
total := int64(len(list))
|
|
start, end := erpPageWindow(len(list), page, pageSize)
|
|
return list[start:end], total, 200, ""
|
|
}
|
|
|
|
// erpPageWindow 内存分页的取页区间，越界时收敛到末尾
|
|
func erpPageWindow(length, page, pageSize int) (int, int) {
|
|
start := (page - 1) * pageSize
|
|
if start > length {
|
|
start = length
|
|
}
|
|
if start < 0 {
|
|
start = 0
|
|
}
|
|
end := start + pageSize
|
|
if end > length {
|
|
end = length
|
|
}
|
|
return start, end
|
|
}
|