368 lines
12 KiB
Go
368 lines
12 KiB
Go
package payment
|
||
|
||
import (
|
||
"context"
|
||
"encoding/csv"
|
||
"fmt"
|
||
"strings"
|
||
"time"
|
||
|
||
"server/models"
|
||
|
||
"github.com/beego/beego/v2/client/orm"
|
||
beelog "github.com/beego/beego/v2/core/logs"
|
||
)
|
||
|
||
/* =============================================================
|
||
* 日终对账：渠道账单导入 -> 与本地支付流水比对 -> 产出差异 -> 人工标记处理
|
||
* 差异类型：long 长款（渠道有本地无）/ short 短款（本地有渠道无）/ status_mismatch 状态不一致
|
||
* ============================================================= */
|
||
|
||
// BillRow 渠道账单明细行（导入用）
|
||
type BillRow struct {
|
||
ChannelTradeNo string // 渠道流水号
|
||
OutTradeNo string // 渠道侧商户订单号（下单时传的 pay_no）
|
||
Amount int64 // 渠道金额（分）
|
||
Fee int64 // 渠道手续费（分）
|
||
TradeState string // 渠道侧交易状态
|
||
TradeTime *time.Time // 渠道交易时间
|
||
}
|
||
|
||
// ImportChannelBill 导入某渠道某日的账单明细（幂等：按 channel + channel_trade_no 更新或插入）
|
||
func ImportChannelBill(ctx context.Context, channel, billDate string, rows []BillRow) (int, error) {
|
||
day, err := time.ParseInLocation("2006-01-02", billDate, time.Local)
|
||
if err != nil {
|
||
return 0, fmt.Errorf("对账日期格式应为 YYYY-MM-DD")
|
||
}
|
||
imported := 0
|
||
for _, r := range rows {
|
||
if strings.TrimSpace(r.ChannelTradeNo) == "" {
|
||
continue
|
||
}
|
||
exist := &models.PlatformPaymentChannelBill{}
|
||
err := models.Orm.QueryTable(new(models.PlatformPaymentChannelBill)).
|
||
Filter("channel", channel).
|
||
Filter("channel_trade_no", r.ChannelTradeNo).
|
||
Filter("delete_time__isnull", true).
|
||
One(exist)
|
||
if err == nil {
|
||
_, _ = models.Orm.QueryTable(new(models.PlatformPaymentChannelBill)).
|
||
Filter("id", exist.ID).
|
||
Update(orm.Params{
|
||
"amount": r.Amount, "fee": r.Fee, "trade_state": r.TradeState,
|
||
"update_time": time.Now(),
|
||
})
|
||
imported++
|
||
continue
|
||
}
|
||
row := &models.PlatformPaymentChannelBill{
|
||
Channel: channel,
|
||
BillDate: day,
|
||
ChannelTradeNo: r.ChannelTradeNo,
|
||
OutTradeNo: r.OutTradeNo,
|
||
Amount: r.Amount,
|
||
Fee: r.Fee,
|
||
TradeState: r.TradeState,
|
||
TradeTime: r.TradeTime,
|
||
}
|
||
if _, err := models.Orm.Insert(row); err == nil {
|
||
imported++
|
||
}
|
||
}
|
||
return imported, nil
|
||
}
|
||
|
||
// ParseChannelBillCSV 解析账单 CSV。
|
||
// 约定列：channel_trade_no, out_trade_no, amount(元), fee(元), trade_state, trade_time
|
||
// TODO(渠道): 各渠道账单文件列名/编码不同（微信为制表符分隔、支付宝为 GBK CSV），
|
||
// 对接时按渠道导出文件写各自的解析器，统一转成 BillRow。
|
||
func ParseChannelBillCSV(data []byte) ([]BillRow, error) {
|
||
reader := csv.NewReader(strings.NewReader(string(data)))
|
||
reader.TrimLeadingSpace = true
|
||
records, err := reader.ReadAll()
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
rows := make([]BillRow, 0, len(records))
|
||
for i, rec := range records {
|
||
if i == 0 || len(rec) == 0 {
|
||
continue
|
||
}
|
||
get := func(idx int) string {
|
||
if idx < len(rec) {
|
||
return strings.TrimSpace(rec[idx])
|
||
}
|
||
return ""
|
||
}
|
||
row := BillRow{
|
||
ChannelTradeNo: get(0),
|
||
OutTradeNo: get(1),
|
||
TradeState: get(4),
|
||
}
|
||
if row.ChannelTradeNo == "" {
|
||
continue
|
||
}
|
||
if fen, err := YuanToFen(get(2)); err == nil {
|
||
row.Amount = fen
|
||
}
|
||
if fen, err := YuanToFen(get(3)); err == nil {
|
||
row.Fee = fen
|
||
}
|
||
if t := get(5); t != "" {
|
||
for _, layout := range []string{"2006-01-02 15:04:05", time.RFC3339, "2006-01-02"} {
|
||
if tt, err := time.ParseInLocation(layout, t, time.Local); err == nil {
|
||
row.TradeTime = &tt
|
||
break
|
||
}
|
||
}
|
||
}
|
||
rows = append(rows, row)
|
||
}
|
||
return rows, nil
|
||
}
|
||
|
||
// ReconcileSummary 一次对账的执行结果
|
||
type ReconcileSummary struct {
|
||
BatchNo string `json:"batch_no"`
|
||
Channel string `json:"channel"`
|
||
BillDate string `json:"bill_date"`
|
||
BillCount int `json:"bill_count"`
|
||
Matched int `json:"matched"`
|
||
LongCount int `json:"long_count"`
|
||
ShortCount int `json:"short_count"`
|
||
MismatchCount int `json:"mismatch_count"`
|
||
}
|
||
|
||
// RunReconcile 对某渠道某日的账单与本地支付流水做比对，产出差异（幂等：按唯一键更新已有差异）。
|
||
// 匹配策略：渠道账单的商户订单号即本地下单时传的 pay_no，优先按 pay_no 匹配，其次按 out_trade_no；
|
||
// 本地侧只统计支付成功日（paid_at）落在对账日内的支付单。
|
||
func RunReconcile(ctx context.Context, channel, billDate string) (*ReconcileSummary, error) {
|
||
day, err := time.ParseInLocation("2006-01-02", billDate, time.Local)
|
||
if err != nil {
|
||
return nil, fmt.Errorf("对账日期格式应为 YYYY-MM-DD")
|
||
}
|
||
if _, err := LoadChannelConfig(channel); err != nil {
|
||
return nil, err
|
||
}
|
||
|
||
var bills []models.PlatformPaymentChannelBill
|
||
_, err = models.Orm.QueryTable(new(models.PlatformPaymentChannelBill)).
|
||
Filter("channel", channel).
|
||
Filter("bill_date", day).
|
||
Filter("delete_time__isnull", true).
|
||
All(&bills)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
|
||
var locals []models.PlatformPaymentOrder
|
||
_, err = models.Orm.QueryTable(new(models.PlatformPaymentOrder)).
|
||
Filter("delete_time__isnull", true).
|
||
Filter("status", models.PayStatusPaid).
|
||
Filter("paid_at__gte", day).
|
||
Filter("paid_at__lt", day.AddDate(0, 0, 1)).
|
||
All(&locals)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
localByPayNo := map[string]*models.PlatformPaymentOrder{}
|
||
localByOutTrade := map[string]*models.PlatformPaymentOrder{}
|
||
for i := range locals {
|
||
localByPayNo[locals[i].PayNo] = &locals[i]
|
||
localByOutTrade[locals[i].OutTradeNo] = &locals[i]
|
||
}
|
||
|
||
batchNo := NextBatchNo()
|
||
summary := &ReconcileSummary{BatchNo: batchNo, Channel: channel, BillDate: billDate, BillCount: len(bills)}
|
||
matchedPayNos := map[string]bool{}
|
||
|
||
for i := range bills {
|
||
bill := bills[i]
|
||
local := localByPayNo[bill.OutTradeNo]
|
||
if local == nil {
|
||
local = localByOutTrade[bill.OutTradeNo]
|
||
}
|
||
|
||
if local == nil {
|
||
summary.LongCount++
|
||
_ = upsertDiff(channel, day, batchNo, "", bill.ChannelTradeNo,
|
||
models.DiffTypeLong, nil, &bill.Amount, "渠道账单存在该流水，本地无对应支付单")
|
||
continue
|
||
}
|
||
|
||
if local.Status == models.PayStatusPaid && bill.Amount == local.Amount {
|
||
summary.Matched++
|
||
matchedPayNos[local.PayNo] = true
|
||
_, _ = models.Orm.QueryTable(new(models.PlatformPaymentChannelBill)).
|
||
Filter("id", bill.ID).
|
||
Update(orm.Params{"is_matched": 1, "matched_pay_no": local.PayNo, "update_time": time.Now()})
|
||
continue
|
||
}
|
||
|
||
summary.MismatchCount++
|
||
reason := "本地与渠道金额不一致"
|
||
if bill.Amount == local.Amount {
|
||
reason = fmt.Sprintf("渠道已成功但本地状态为 %s", local.Status)
|
||
}
|
||
_ = upsertDiff(channel, day, batchNo, local.PayNo, bill.ChannelTradeNo,
|
||
models.DiffTypeStatusMismatch, &local.Amount, &bill.Amount, reason)
|
||
}
|
||
|
||
for i := range locals {
|
||
if matchedPayNos[locals[i].PayNo] {
|
||
continue
|
||
}
|
||
summary.ShortCount++
|
||
_ = upsertDiff(channel, day, batchNo, locals[i].PayNo, "",
|
||
models.DiffTypeShort, &locals[i].Amount, nil, "本地已支付，渠道账单无对应流水")
|
||
}
|
||
|
||
beelog.Info("对账完成: 渠道=%s 日期=%s 账单 %d 笔，匹配 %d，长款 %d，短款 %d，不一致 %d（批次 %s）",
|
||
channel, billDate, summary.BillCount, summary.Matched, summary.LongCount, summary.ShortCount, summary.MismatchCount, batchNo)
|
||
return summary, nil
|
||
}
|
||
|
||
// upsertDiff 写入/更新差异记录（幂等：唯一键 channel+bill_date+diff_type+channel_trade_no+local_trade_no）
|
||
func upsertDiff(channel string, day time.Time, batchNo, localTradeNo, channelTradeNo, diffType string, localAmount, channelAmount *int64, remark string) error {
|
||
exist := &models.PlatformPaymentReconcileDiff{}
|
||
err := models.Orm.QueryTable(new(models.PlatformPaymentReconcileDiff)).
|
||
Filter("channel", channel).
|
||
Filter("bill_date", day).
|
||
Filter("diff_type", diffType).
|
||
Filter("channel_trade_no", channelTradeNo).
|
||
Filter("local_trade_no", localTradeNo).
|
||
Filter("delete_time__isnull", true).
|
||
One(exist)
|
||
|
||
diffAmount := int64(0)
|
||
switch {
|
||
case channelAmount != nil && localAmount != nil:
|
||
diffAmount = *channelAmount - *localAmount
|
||
case channelAmount != nil:
|
||
diffAmount = *channelAmount
|
||
case localAmount != nil:
|
||
diffAmount = -*localAmount
|
||
}
|
||
|
||
if err == nil {
|
||
_, uerr := models.Orm.QueryTable(new(models.PlatformPaymentReconcileDiff)).
|
||
Filter("id", exist.ID).
|
||
Update(orm.Params{
|
||
"batch_no": batchNo,
|
||
"local_amount": localAmount,
|
||
"channel_amount": channelAmount,
|
||
"diff_amount": diffAmount,
|
||
"update_time": time.Now(),
|
||
})
|
||
return uerr
|
||
}
|
||
|
||
row := &models.PlatformPaymentReconcileDiff{
|
||
Channel: channel,
|
||
BillDate: day,
|
||
BatchNo: batchNo,
|
||
LocalTradeNo: localTradeNo,
|
||
ChannelTradeNo: channelTradeNo,
|
||
LocalAmount: localAmount,
|
||
ChannelAmount: channelAmount,
|
||
DiffAmount: diffAmount,
|
||
DiffType: diffType,
|
||
HandleStatus: models.DiffHandleUnhandled,
|
||
Remark: remark,
|
||
}
|
||
_, err = models.Orm.Insert(row)
|
||
return err
|
||
}
|
||
|
||
// ReconcileListInput 差异列表查询条件
|
||
type ReconcileListInput struct {
|
||
Channel string
|
||
BillDate string
|
||
DiffType string
|
||
HandleStatus string
|
||
Page int
|
||
PageSize int
|
||
}
|
||
|
||
// ListReconcileDiffs 对账差异分页
|
||
func ListReconcileDiffs(in ReconcileListInput) ([]models.PlatformPaymentReconcileDiff, int64, error) {
|
||
qs := models.Orm.QueryTable(new(models.PlatformPaymentReconcileDiff)).Filter("delete_time__isnull", true)
|
||
if in.Channel != "" {
|
||
qs = qs.Filter("channel", in.Channel)
|
||
}
|
||
if in.BillDate != "" {
|
||
if t, err := time.ParseInLocation("2006-01-02", in.BillDate, time.Local); err == nil {
|
||
qs = qs.Filter("bill_date", t)
|
||
}
|
||
}
|
||
if in.DiffType != "" {
|
||
qs = qs.Filter("diff_type", in.DiffType)
|
||
}
|
||
if in.HandleStatus != "" {
|
||
qs = qs.Filter("handle_status", in.HandleStatus)
|
||
}
|
||
total, err := qs.Count()
|
||
if err != nil {
|
||
return nil, 0, err
|
||
}
|
||
if in.Page <= 0 {
|
||
in.Page = 1
|
||
}
|
||
if in.PageSize <= 0 || in.PageSize > 200 {
|
||
in.PageSize = 10
|
||
}
|
||
var rows []models.PlatformPaymentReconcileDiff
|
||
_, err = qs.OrderBy("-id").Limit(in.PageSize, (in.Page-1)*in.PageSize).All(&rows)
|
||
return rows, total, err
|
||
}
|
||
|
||
// MarkDiffHandledInput 标记差异处理入参
|
||
type MarkDiffHandledInput struct {
|
||
ID uint64
|
||
HandleStatus string // handled / handling / ignored
|
||
Remark string
|
||
OperatorID string
|
||
OperatorName string
|
||
}
|
||
|
||
// MarkDiffHandled 标记对账差异处理状态
|
||
func MarkDiffHandled(ctx context.Context, in MarkDiffHandledInput) (*models.PlatformPaymentReconcileDiff, error) {
|
||
row := &models.PlatformPaymentReconcileDiff{}
|
||
if err := models.Orm.QueryTable(new(models.PlatformPaymentReconcileDiff)).
|
||
Filter("id", in.ID).
|
||
Filter("delete_time__isnull", true).
|
||
One(row); err != nil {
|
||
return nil, fmt.Errorf("差异记录不存在")
|
||
}
|
||
switch in.HandleStatus {
|
||
case models.DiffHandleHandled, models.DiffHandleHandling, models.DiffHandleIgnored:
|
||
default:
|
||
return nil, fmt.Errorf("处理状态不合法")
|
||
}
|
||
now := time.Now()
|
||
_, err := models.Orm.QueryTable(new(models.PlatformPaymentReconcileDiff)).
|
||
Filter("id", row.ID).
|
||
Update(orm.Params{
|
||
"handle_status": in.HandleStatus,
|
||
"remark": in.Remark,
|
||
"handle_user_id": in.OperatorID,
|
||
"handle_user_name": in.OperatorName,
|
||
"handle_time": now,
|
||
"update_time": now,
|
||
})
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
return GetReconcileDiffByID(in.ID)
|
||
}
|
||
|
||
// GetReconcileDiffByID 按 ID 查询差异记录
|
||
func GetReconcileDiffByID(id uint64) (*models.PlatformPaymentReconcileDiff, error) {
|
||
row := &models.PlatformPaymentReconcileDiff{}
|
||
err := models.Orm.QueryTable(new(models.PlatformPaymentReconcileDiff)).
|
||
Filter("id", id).
|
||
Filter("delete_time__isnull", true).
|
||
One(row)
|
||
return row, err
|
||
}
|