631 lines
16 KiB
Go
631 lines
16 KiB
Go
package importer
|
||
|
||
import (
|
||
"bytes"
|
||
"context"
|
||
"encoding/json"
|
||
"fmt"
|
||
"io"
|
||
"mime/multipart"
|
||
"strconv"
|
||
"strings"
|
||
"time"
|
||
|
||
"cockpit/internal/domain"
|
||
|
||
"github.com/shopspring/decimal"
|
||
"github.com/xuri/excelize/v2"
|
||
"gorm.io/gorm"
|
||
)
|
||
|
||
const OrdersSheetName = "订单导入"
|
||
|
||
// 生成 Excel 模板（xlsx bytes）
|
||
func BuildOrdersTemplate() ([]byte, error) {
|
||
f := excelize.NewFile()
|
||
defer func() { _ = f.Close() }()
|
||
|
||
sheet := OrdersSheetName
|
||
f.SetSheetName("Sheet1", sheet)
|
||
|
||
headers := []string{"订单号", "下单日期", "出货日期", "下单金额", "订单金额", "客户名", "备注", "状态"}
|
||
for i, h := range headers {
|
||
cell, _ := excelize.CoordinatesToCellName(i+1, 1)
|
||
_ = f.SetCellValue(sheet, cell, h)
|
||
}
|
||
// 示例行（可删）
|
||
example := []any{"A20260401001", "2026-04-01", "2026-04-05", "1234.50", "1200.00", "示例客户", "备注示例", "已出货"}
|
||
for i, v := range example {
|
||
cell, _ := excelize.CoordinatesToCellName(i+1, 2)
|
||
_ = f.SetCellValue(sheet, cell, v)
|
||
}
|
||
|
||
_ = f.SetColWidth(sheet, "A", "H", 18)
|
||
|
||
buf, err := f.WriteToBuffer()
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
return buf.Bytes(), nil
|
||
}
|
||
|
||
type OrderInput struct {
|
||
OrderNo string `json:"order_no"`
|
||
OrderDate string `json:"order_date"`
|
||
ShipDate string `json:"ship_date"`
|
||
AmountA string `json:"amount_a"`
|
||
AmountB string `json:"amount_b"`
|
||
CustomerName string `json:"customer_name"`
|
||
Remark string `json:"remark"`
|
||
StatusName string `json:"status_name"`
|
||
}
|
||
|
||
type OrderClean struct {
|
||
OrderNo string
|
||
OrderDate time.Time
|
||
ShipDate *time.Time
|
||
AmountA decimal.Decimal
|
||
AmountB decimal.Decimal
|
||
CustomerID uint64
|
||
StatusID uint64
|
||
Remark string
|
||
CustomerName string // 仅用于预览回显
|
||
StatusName string // 仅用于预览回显
|
||
}
|
||
|
||
type RowError struct {
|
||
RowNo int `json:"rowNo"`
|
||
Field string `json:"field"`
|
||
Message string `json:"message"`
|
||
RawData any `json:"rawData,omitempty"`
|
||
}
|
||
|
||
func ParseOrdersExcel(ctx context.Context, db *gorm.DB, file multipart.File, previewLimit int) (total int, preview []OrderClean, errors []RowError, err error) {
|
||
data, err := io.ReadAll(file)
|
||
if err != nil {
|
||
return 0, nil, nil, err
|
||
}
|
||
f, err := excelize.OpenReader(bytes.NewReader(data))
|
||
if err != nil {
|
||
return 0, nil, nil, err
|
||
}
|
||
defer func() { _ = f.Close() }()
|
||
|
||
rows, err := f.GetRows(OrdersSheetName)
|
||
if err != nil || len(rows) == 0 {
|
||
// 兼容用户把 sheet 名改掉：取第一个 sheet
|
||
names := f.GetSheetList()
|
||
if len(names) == 0 {
|
||
return 0, nil, nil, fmt.Errorf("Excel 无可用 sheet")
|
||
}
|
||
rows, err = f.GetRows(names[0])
|
||
if err != nil {
|
||
return 0, nil, nil, err
|
||
}
|
||
}
|
||
if len(rows) < 2 {
|
||
return 0, nil, nil, fmt.Errorf("Excel 至少需要表头+1行数据")
|
||
}
|
||
|
||
header := rows[0]
|
||
idx := headerIndex(header)
|
||
|
||
// 缓存客户/状态映射
|
||
customerMap, err := loadCustomerMap(ctx, db)
|
||
if err != nil {
|
||
return 0, nil, nil, err
|
||
}
|
||
statusMap, err := loadStatusMap(ctx, db)
|
||
if err != nil {
|
||
return 0, nil, nil, err
|
||
}
|
||
|
||
for r := 1; r < len(rows); r++ {
|
||
total++
|
||
raw := rowToOrderInput(rows[r], idx)
|
||
clean, rowErrs := cleanOneOrder(raw, r+1, customerMap, statusMap)
|
||
if len(rowErrs) > 0 {
|
||
errors = append(errors, rowErrs...)
|
||
} else if len(preview) < previewLimit {
|
||
preview = append(preview, clean)
|
||
}
|
||
}
|
||
return total, preview, errors, nil
|
||
}
|
||
|
||
func ParseOrdersJSON(ctx context.Context, db *gorm.DB, body []byte, previewLimit int) (total int, preview []OrderClean, errors []RowError, err error) {
|
||
var inputs []OrderInput
|
||
if err := json.Unmarshal(body, &inputs); err != nil {
|
||
return 0, nil, nil, err
|
||
}
|
||
customerMap, err := loadCustomerMap(ctx, db)
|
||
if err != nil {
|
||
return 0, nil, nil, err
|
||
}
|
||
statusMap, err := loadStatusMap(ctx, db)
|
||
if err != nil {
|
||
return 0, nil, nil, err
|
||
}
|
||
for i, in := range inputs {
|
||
total++
|
||
clean, rowErrs := cleanOneOrder(in, i+1, customerMap, statusMap)
|
||
if len(rowErrs) > 0 {
|
||
errors = append(errors, rowErrs...)
|
||
} else if len(preview) < previewLimit {
|
||
preview = append(preview, clean)
|
||
}
|
||
}
|
||
return total, preview, errors, nil
|
||
}
|
||
|
||
func CommitOrders(ctx context.Context, db *gorm.DB, inputs []OrderClean, createdBy uint64, inputFmt, fileName string) (*domain.ImportJob, error) {
|
||
now := time.Now()
|
||
job := domain.ImportJob{
|
||
Type: "orders",
|
||
InputFmt: inputFmt,
|
||
Status: "running",
|
||
FileName: fileName,
|
||
TotalRows: len(inputs),
|
||
StartedAt: &now,
|
||
CreatedBy: createdBy,
|
||
}
|
||
if err := db.WithContext(ctx).Create(&job).Error; err != nil {
|
||
return nil, err
|
||
}
|
||
|
||
ok := 0
|
||
fail := 0
|
||
for _, in := range inputs {
|
||
order := domain.Order{
|
||
OrderNo: in.OrderNo,
|
||
OrderDate: in.OrderDate,
|
||
ShipDate: in.ShipDate,
|
||
AmountA: in.AmountA,
|
||
AmountB: in.AmountB,
|
||
CustomerID: in.CustomerID,
|
||
StatusID: in.StatusID,
|
||
Remark: in.Remark,
|
||
Source: "import",
|
||
CreatedBy: createdBy,
|
||
}
|
||
if err := db.WithContext(ctx).Create(&order).Error; err != nil {
|
||
fail++
|
||
_ = db.WithContext(ctx).Create(&domain.ImportJobError{
|
||
JobID: job.ID,
|
||
RowNo: 0,
|
||
Field: "order_no",
|
||
Message: "入库失败：" + err.Error(),
|
||
RawData: "",
|
||
}).Error
|
||
continue
|
||
}
|
||
ok++
|
||
}
|
||
|
||
job.OKRows = ok
|
||
job.FailRows = fail
|
||
finished := time.Now()
|
||
job.FinishedAt = &finished
|
||
if fail == 0 {
|
||
job.Status = "success"
|
||
} else if ok == 0 {
|
||
job.Status = "failed"
|
||
} else {
|
||
job.Status = "partial"
|
||
}
|
||
_ = db.WithContext(ctx).Save(&job).Error
|
||
return &job, nil
|
||
}
|
||
|
||
// CommitOrdersFromExcel：解析 Excel 并直接落库，返回 import_job
|
||
func CommitOrdersFromExcel(ctx context.Context, db *gorm.DB, file multipart.File, createdBy uint64, fileName string) (*domain.ImportJob, error) {
|
||
data, err := io.ReadAll(file)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
f, err := excelize.OpenReader(bytes.NewReader(data))
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
defer func() { _ = f.Close() }()
|
||
|
||
rows, err := f.GetRows(OrdersSheetName)
|
||
if err != nil || len(rows) == 0 {
|
||
names := f.GetSheetList()
|
||
if len(names) == 0 {
|
||
return nil, fmt.Errorf("Excel 无可用 sheet")
|
||
}
|
||
rows, err = f.GetRows(names[0])
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
}
|
||
if len(rows) < 2 {
|
||
return nil, fmt.Errorf("Excel 至少需要表头+1行数据")
|
||
}
|
||
|
||
customerMap, err := loadCustomerMap(ctx, db)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
statusMap, err := loadStatusMap(ctx, db)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
|
||
now := time.Now()
|
||
job := domain.ImportJob{
|
||
Type: "orders",
|
||
InputFmt: "excel",
|
||
Status: "running",
|
||
FileName: fileName,
|
||
StartedAt: &now,
|
||
CreatedBy: createdBy,
|
||
}
|
||
if err := db.WithContext(ctx).Create(&job).Error; err != nil {
|
||
return nil, err
|
||
}
|
||
|
||
idx := headerIndex(rows[0])
|
||
seen := map[string]bool{}
|
||
|
||
total := 0
|
||
ok := 0
|
||
fail := 0
|
||
for r := 1; r < len(rows); r++ {
|
||
total++
|
||
raw := rowToOrderInput(rows[r], idx)
|
||
rawErrs := []RowError{}
|
||
if strings.TrimSpace(raw.OrderNo) != "" && seen[strings.TrimSpace(raw.OrderNo)] {
|
||
rawErrs = append(rawErrs, RowError{RowNo: r + 1, Field: "order_no", Message: "文件内订单号重复", RawData: raw})
|
||
}
|
||
seen[strings.TrimSpace(raw.OrderNo)] = true
|
||
|
||
clean, errs := cleanOneOrder(raw, r+1, customerMap, statusMap)
|
||
errs = append(errs, rawErrs...)
|
||
if len(errs) > 0 {
|
||
fail++
|
||
for _, e := range errs {
|
||
b, _ := json.Marshal(e.RawData)
|
||
_ = db.WithContext(ctx).Create(&domain.ImportJobError{
|
||
JobID: job.ID,
|
||
RowNo: e.RowNo,
|
||
Field: e.Field,
|
||
Message: e.Message,
|
||
RawData: string(b),
|
||
}).Error
|
||
}
|
||
continue
|
||
}
|
||
|
||
// DB 去重
|
||
var exists int64
|
||
_ = db.WithContext(ctx).Model(&domain.Order{}).Where("order_no = ?", clean.OrderNo).Count(&exists).Error
|
||
if exists > 0 {
|
||
fail++
|
||
_ = db.WithContext(ctx).Create(&domain.ImportJobError{
|
||
JobID: job.ID,
|
||
RowNo: r + 1,
|
||
Field: "order_no",
|
||
Message: "订单号已存在（与数据库冲突）",
|
||
RawData: "",
|
||
}).Error
|
||
continue
|
||
}
|
||
|
||
order := domain.Order{
|
||
OrderNo: clean.OrderNo,
|
||
OrderDate: clean.OrderDate,
|
||
ShipDate: clean.ShipDate,
|
||
AmountA: clean.AmountA,
|
||
AmountB: clean.AmountB,
|
||
CustomerID: clean.CustomerID,
|
||
StatusID: clean.StatusID,
|
||
Remark: clean.Remark,
|
||
Source: "import",
|
||
CreatedBy: createdBy,
|
||
}
|
||
if err := db.WithContext(ctx).Create(&order).Error; err != nil {
|
||
fail++
|
||
_ = db.WithContext(ctx).Create(&domain.ImportJobError{
|
||
JobID: job.ID,
|
||
RowNo: r + 1,
|
||
Field: "order_no",
|
||
Message: "入库失败：" + err.Error(),
|
||
RawData: "",
|
||
}).Error
|
||
continue
|
||
}
|
||
ok++
|
||
}
|
||
|
||
job.TotalRows = total
|
||
job.OKRows = ok
|
||
job.FailRows = fail
|
||
finished := time.Now()
|
||
job.FinishedAt = &finished
|
||
if fail == 0 {
|
||
job.Status = "success"
|
||
} else if ok == 0 {
|
||
job.Status = "failed"
|
||
} else {
|
||
job.Status = "partial"
|
||
}
|
||
_ = db.WithContext(ctx).Save(&job).Error
|
||
return &job, nil
|
||
}
|
||
|
||
// CommitOrdersFromJSON：解析 JSON 并直接落库，返回 import_job
|
||
func CommitOrdersFromJSON(ctx context.Context, db *gorm.DB, body []byte, createdBy uint64) (*domain.ImportJob, error) {
|
||
var inputs []OrderInput
|
||
if err := json.Unmarshal(body, &inputs); err != nil {
|
||
return nil, err
|
||
}
|
||
customerMap, err := loadCustomerMap(ctx, db)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
statusMap, err := loadStatusMap(ctx, db)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
|
||
now := time.Now()
|
||
job := domain.ImportJob{
|
||
Type: "orders",
|
||
InputFmt: "json",
|
||
Status: "running",
|
||
StartedAt: &now,
|
||
CreatedBy: createdBy,
|
||
TotalRows: len(inputs),
|
||
}
|
||
if err := db.WithContext(ctx).Create(&job).Error; err != nil {
|
||
return nil, err
|
||
}
|
||
|
||
seen := map[string]bool{}
|
||
ok := 0
|
||
fail := 0
|
||
for i, raw := range inputs {
|
||
rowNo := i + 1
|
||
rawErrs := []RowError{}
|
||
if strings.TrimSpace(raw.OrderNo) != "" && seen[strings.TrimSpace(raw.OrderNo)] {
|
||
rawErrs = append(rawErrs, RowError{RowNo: rowNo, Field: "order_no", Message: "JSON 内订单号重复", RawData: raw})
|
||
}
|
||
seen[strings.TrimSpace(raw.OrderNo)] = true
|
||
|
||
clean, errs := cleanOneOrder(raw, rowNo, customerMap, statusMap)
|
||
errs = append(errs, rawErrs...)
|
||
if len(errs) > 0 {
|
||
fail++
|
||
for _, e := range errs {
|
||
b, _ := json.Marshal(e.RawData)
|
||
_ = db.WithContext(ctx).Create(&domain.ImportJobError{
|
||
JobID: job.ID,
|
||
RowNo: e.RowNo,
|
||
Field: e.Field,
|
||
Message: e.Message,
|
||
RawData: string(b),
|
||
}).Error
|
||
}
|
||
continue
|
||
}
|
||
var exists int64
|
||
_ = db.WithContext(ctx).Model(&domain.Order{}).Where("order_no = ?", clean.OrderNo).Count(&exists).Error
|
||
if exists > 0 {
|
||
fail++
|
||
_ = db.WithContext(ctx).Create(&domain.ImportJobError{
|
||
JobID: job.ID,
|
||
RowNo: rowNo,
|
||
Field: "order_no",
|
||
Message: "订单号已存在（与数据库冲突）",
|
||
RawData: "",
|
||
}).Error
|
||
continue
|
||
}
|
||
|
||
order := domain.Order{
|
||
OrderNo: clean.OrderNo,
|
||
OrderDate: clean.OrderDate,
|
||
ShipDate: clean.ShipDate,
|
||
AmountA: clean.AmountA,
|
||
AmountB: clean.AmountB,
|
||
CustomerID: clean.CustomerID,
|
||
StatusID: clean.StatusID,
|
||
Remark: clean.Remark,
|
||
Source: "import",
|
||
CreatedBy: createdBy,
|
||
}
|
||
if err := db.WithContext(ctx).Create(&order).Error; err != nil {
|
||
fail++
|
||
_ = db.WithContext(ctx).Create(&domain.ImportJobError{
|
||
JobID: job.ID,
|
||
RowNo: rowNo,
|
||
Field: "order_no",
|
||
Message: "入库失败：" + err.Error(),
|
||
RawData: "",
|
||
}).Error
|
||
continue
|
||
}
|
||
ok++
|
||
}
|
||
|
||
job.OKRows = ok
|
||
job.FailRows = fail
|
||
finished := time.Now()
|
||
job.FinishedAt = &finished
|
||
if fail == 0 {
|
||
job.Status = "success"
|
||
} else if ok == 0 {
|
||
job.Status = "failed"
|
||
} else {
|
||
job.Status = "partial"
|
||
}
|
||
_ = db.WithContext(ctx).Save(&job).Error
|
||
return &job, nil
|
||
}
|
||
|
||
// ---- helpers ----
|
||
|
||
func headerIndex(header []string) map[string]int {
|
||
idx := map[string]int{}
|
||
for i, h := range header {
|
||
h = strings.TrimSpace(h)
|
||
if h == "" {
|
||
continue
|
||
}
|
||
idx[h] = i
|
||
}
|
||
return idx
|
||
}
|
||
|
||
func rowToOrderInput(row []string, idx map[string]int) OrderInput {
|
||
get := func(name string) string {
|
||
i, ok := idx[name]
|
||
if !ok || i >= len(row) {
|
||
return ""
|
||
}
|
||
return strings.TrimSpace(row[i])
|
||
}
|
||
return OrderInput{
|
||
OrderNo: get("订单号"),
|
||
OrderDate: get("下单日期"),
|
||
ShipDate: get("出货日期"),
|
||
AmountA: get("下单金额"),
|
||
AmountB: get("订单金额"),
|
||
CustomerName: get("客户名"),
|
||
Remark: get("备注"),
|
||
StatusName: get("状态"),
|
||
}
|
||
}
|
||
|
||
func cleanOneOrder(in OrderInput, rowNo int, customerMap map[string]domain.Customer, statusMap map[string]domain.Status) (OrderClean, []RowError) {
|
||
var errs []RowError
|
||
trim := func(s string) string { return strings.TrimSpace(strings.ReplaceAll(s, "\u3000", " ")) }
|
||
|
||
in.OrderNo = trim(in.OrderNo)
|
||
in.CustomerName = trim(in.CustomerName)
|
||
in.StatusName = trim(in.StatusName)
|
||
in.AmountA = trim(in.AmountA)
|
||
in.AmountB = trim(in.AmountB)
|
||
in.OrderDate = trim(in.OrderDate)
|
||
in.ShipDate = trim(in.ShipDate)
|
||
|
||
if in.OrderNo == "" {
|
||
errs = append(errs, RowError{RowNo: rowNo, Field: "order_no", Message: "订单号必填", RawData: in})
|
||
}
|
||
if in.OrderDate == "" {
|
||
errs = append(errs, RowError{RowNo: rowNo, Field: "order_date", Message: "下单日期必填", RawData: in})
|
||
}
|
||
if in.CustomerName == "" {
|
||
errs = append(errs, RowError{RowNo: rowNo, Field: "customer_name", Message: "客户名必填", RawData: in})
|
||
}
|
||
if in.StatusName == "" {
|
||
errs = append(errs, RowError{RowNo: rowNo, Field: "status_name", Message: "状态必填", RawData: in})
|
||
}
|
||
|
||
orderDate, err := parseDate(in.OrderDate)
|
||
if err != nil {
|
||
errs = append(errs, RowError{RowNo: rowNo, Field: "order_date", Message: "下单日期格式错误", RawData: in})
|
||
}
|
||
var shipDate *time.Time
|
||
if in.ShipDate != "" {
|
||
t, err := parseDate(in.ShipDate)
|
||
if err != nil {
|
||
errs = append(errs, RowError{RowNo: rowNo, Field: "ship_date", Message: "出货日期格式错误", RawData: in})
|
||
} else {
|
||
shipDate = &t
|
||
}
|
||
}
|
||
|
||
a, err := parseDecimal(in.AmountA)
|
||
if err != nil || a.IsNegative() {
|
||
errs = append(errs, RowError{RowNo: rowNo, Field: "amount_a", Message: "下单金额格式错误", RawData: in})
|
||
}
|
||
b, err := parseDecimal(in.AmountB)
|
||
if err != nil || b.IsNegative() {
|
||
errs = append(errs, RowError{RowNo: rowNo, Field: "amount_b", Message: "订单金额格式错误", RawData: in})
|
||
}
|
||
|
||
cust, ok := customerMap[in.CustomerName]
|
||
if !ok {
|
||
errs = append(errs, RowError{RowNo: rowNo, Field: "customer_name", Message: "客户不存在，请先在系统维护客户："+in.CustomerName, RawData: in})
|
||
}
|
||
st, ok := statusMap[in.StatusName]
|
||
if !ok {
|
||
errs = append(errs, RowError{RowNo: rowNo, Field: "status_name", Message: "状态不存在，请先在系统维护状态："+in.StatusName, RawData: in})
|
||
}
|
||
|
||
if len(errs) > 0 {
|
||
return OrderClean{}, errs
|
||
}
|
||
return OrderClean{
|
||
OrderNo: in.OrderNo,
|
||
OrderDate: orderDate,
|
||
ShipDate: shipDate,
|
||
AmountA: a.Round(2),
|
||
AmountB: b.Round(2),
|
||
CustomerID: cust.ID,
|
||
StatusID: st.ID,
|
||
Remark: in.Remark,
|
||
CustomerName: cust.Name,
|
||
StatusName: st.Name,
|
||
}, nil
|
||
}
|
||
|
||
func parseDecimal(s string) (decimal.Decimal, error) {
|
||
s = strings.ReplaceAll(s, ",", "")
|
||
s = strings.TrimSpace(s)
|
||
if s == "" {
|
||
return decimal.Zero, fmt.Errorf("empty")
|
||
}
|
||
return decimal.NewFromString(s)
|
||
}
|
||
|
||
func parseDate(s string) (time.Time, error) {
|
||
s = strings.TrimSpace(s)
|
||
if s == "" {
|
||
return time.Time{}, fmt.Errorf("empty")
|
||
}
|
||
|
||
// 支持 Excel serial（整数字符串）
|
||
if i, err := strconv.Atoi(s); err == nil && i > 20000 {
|
||
return excelize.ExcelDateToTime(float64(i), false)
|
||
}
|
||
|
||
layouts := []string{"2006-01-02", "2006/01/02", "2006.01.02"}
|
||
for _, layout := range layouts {
|
||
if t, err := time.Parse(layout, s); err == nil {
|
||
return t, nil
|
||
}
|
||
}
|
||
return time.Time{}, fmt.Errorf("invalid date")
|
||
}
|
||
|
||
func loadCustomerMap(ctx context.Context, db *gorm.DB) (map[string]domain.Customer, error) {
|
||
var list []domain.Customer
|
||
if err := db.WithContext(ctx).Find(&list).Error; err != nil {
|
||
return nil, err
|
||
}
|
||
m := make(map[string]domain.Customer, len(list))
|
||
for _, c := range list {
|
||
m[strings.TrimSpace(c.Name)] = c
|
||
if strings.TrimSpace(c.Code) != "" {
|
||
m[strings.TrimSpace(c.Code)] = c
|
||
}
|
||
}
|
||
return m, nil
|
||
}
|
||
|
||
func loadStatusMap(ctx context.Context, db *gorm.DB) (map[string]domain.Status, error) {
|
||
var list []domain.Status
|
||
if err := db.WithContext(ctx).Find(&list).Error; err != nil {
|
||
return nil, err
|
||
}
|
||
m := make(map[string]domain.Status, len(list))
|
||
for _, s := range list {
|
||
m[strings.TrimSpace(s.Name)] = s
|
||
}
|
||
return m, nil
|
||
}
|