OLSBackend_Common/JiaZhiQuan.Common/Repository/RepositoryExtension.UEBill.cs

using static JiaZhiQuan.Common.Models.MallGoodsModel.RefundModel;
using System.Data;
using System.Threading.Tasks;
using Wicture.DbRESTFul.Infrastructure.Repository;
using JiaZhiQuan.Common.JuheAPI.HuiFu;
using System.Runtime.CompilerServices;
using System;
using JiaZhiQuan.Common.Models.PO;
using JiaZhiQuan.Common.Models.VO.UE;
using JiaZhiQuan.Common.Config;
using MailKit;
using Elasticsearch.Net;
using Newtonsoft.Json;
using System.Collections.Generic;
using Wicture.DbRESTFul;
using System.Net.Http.Headers;
using NPOI.SS.UserModel;
using JiaZhiQuan.Common.Response;

namespace JiaZhiQuan.Common {
    public partial class RepositoryExtension {

        #region 账户逻辑
        //插入支出锁定表
        public static async Task<InsertResult> AddUserExpend(this DbRESTFulRepository repository,
            UEUserExpend info, IDbConnection conn, IDbTransaction trans) {
            info.state = 1;
            var expend = await repository.QuerySingleOrDefaultAsync<UEUserExpend>(
                $@"select * from ue_user_expend where orgId=@orgId", 
                new { info.orgId,info.expendType });
            if (expend == null) {
                return await repository.SimpleInsert("ue_user_expend", info, conn, trans);
            } else {
                var cnt= await repository.SimpleUpdate("ue_user_expend", new { info.expend,state=1 },
                    new { info.orgId, info.expendType }, conn, trans);
                return new InsertResult { count= cnt.count };
            }
        }
        /// <summary>
        /// 查询用户支出中的数据
        /// </summary>
        /// <param name="repository"></param>
        /// <param name="userId"></param>
        /// <param name="expendType">支出类型(1，平台账户支出，2，价值币支出)</param>
        /// <param name="conn"></param>
        /// <param name="trans"></param>
        /// <returns></returns>
        public static async Task<int> UserExpending(this DbRESTFulRepository repository,
            long userId,int expendType, 
            IDbConnection conn=null, IDbTransaction trans= null) {

            return await repository.QuerySingleOrDefaultAsync<int?>(
                @$"select sum(expend) expend from ue_user_expend 
                where state in(1,3) and userId={userId} and expendType={expendType}", null, conn, trans)??0;
        }
        /// <summary>
        /// 更新支出为支出结束状态
        /// </summary>
        /// <param name="repository"></param>
        /// <param name="orgId"></param>
        /// <param name="expendType">1:平台账号，2：价值币</param>
        /// <param name="conn"></param>
        /// <param name="trans"></param>
        /// <returns></returns>
        public static async Task<OperateResult> UserExpendEnd(this DbRESTFulRepository repository,
            string orgId,int expendType, IDbConnection conn = null, IDbTransaction trans = null) {

            return await repository.SimpleUpdate("ue_user_expend", new {state=2 }, new { orgId, expendType });
        }
        public static async Task<OperateResult> UserExpendExcept(this DbRESTFulRepository repository,
            string orgId, int expendType, IDbConnection conn = null, IDbTransaction trans = null) {

            return await repository.SimpleUpdate("ue_user_expend", new { state = 3 }, new { orgId, expendType });
        }

        //插入平台流水表
        public static async Task<InsertResult> AddPlatformBill(this DbRESTFulRepository repository,
            UEPlatformBill info, IDbConnection conn, IDbTransaction trans) {
            return await repository.SimpleInsert("ue_platform_bill", info, conn, trans); ;
        }

        //插入个人钱包表
        public static async Task<InsertResult> AddWalletDetail(this DbRESTFulRepository repository,
            UEWalletDetail info, IDbConnection conn, IDbTransaction trans) {
            var oldWallet= await repository.QuerySingleOrDefaultAsync<dynamic>(
                $@"select id,balance from ue_wallet where userId=@userId",
                new { info.userId }, conn, trans);
            var oldBalance = oldWallet?.balance ?? 0;
            var wallet = new {
                info.userId,
                balance = (info.detailType == 1 ?
                info.amount : -info.amount) + oldBalance
            };
            if (wallet.balance < 0) {
                throw new Exception("平台账号资金不支持此操作");
            }
            info.balance = wallet.balance;
            var cnt =await repository.SimpleInsert("ue_wallet_detail",info,conn,trans);
            if(cnt.count>0 ) {
                if (oldWallet != null) {
                    await repository.SimpleUpdate("ue_wallet", new { wallet.balance },
                        new { oldWallet.id }, conn, trans);                    
                } else {
                    await repository.SimpleInsert("ue_wallet",wallet,conn,trans);
                }
            }
            return cnt;
        }

        //查询个人钱包可用的钱
        public static async Task<int> QueryAccountAmount(this DbRESTFulRepository repository, 
            long userId) {
            var exist = await repository.QuerySingleOrDefaultAsync<int>(
                $"select balance from ue_wallet where userId={userId}");
            var expending = await repository.UserExpending(userId, 1);
            var canUse = exist - expending;
            if (canUse < 0) {
                canUse = 0;
            }
            return canUse;
        }
        #endregion

        //汇付接口请求插入
        public static async Task<InsertResult> AddHuiFuReq(this DbRESTFulRepository repository,
            HuiFuReqBase info, IDbConnection conn=null, IDbTransaction trans = null) {
            return await repository.SimpleInsert("ue_huifu_req",
                new {
                    info.reqId,
                    info.orgId, reqType = (int)info.ReqType(), 
                    billEventType=info.eventType,dealResult=0,
                    info.orderId,info.userId }, conn, trans);
        }

        #region 退款逻辑
        /// <summary>
        /// 发起退款
        /// </summary>
        /// <param name="repository"></param>
        /// <param name="order"></param>
        /// <param name="takeoutEarnest">是否扣除保证金</param>
        /// <param name="info">发起请求的基础信息，如 orderId,userId,eventType等</param>
        /// <returns></returns>
        public static async Task<dynamic> LaunchRefund(this DbRESTFulRepository repository,
            ConfigFromDb config, UEOrderHang order, bool? takeoutEarnest, HuiFuReqBase info) {
            //order.earnestMoneyRefunded==1 表示已支付保证金,其他状态表示未支付。
            //takeoutEarnest==false 表示要退保证金
            order = await repository.QuerySingleOrDefaultAsync<UEOrderHang>(
                $@"select * from ue_order_hang where orderId=@orderId", new { order.orderId});
            var hastEarnest = true;
            if (order.earnestAmount <= 0 ||
                order.earnestMoneyRefunded != 1) {
                takeoutEarnest = null;
                hastEarnest = false;
            }
            //仅退保证金
            var onlyEarnest = order.orderState == 5 && hastEarnest && takeoutEarnest == false;
            if(!onlyEarnest) {
                if (order.orderState != -1) {
                    throw new Exception("订单状态不正常，不能退款");
                }
            }
            //仅退保证金
            var refundAmt = onlyEarnest ? order.earnestAmount :
                order.payAmount - (hastEarnest&&takeoutEarnest==true ? order.earnestAmount : 0);

            //更改保证金的状态为待退回 ,不扣除，是需要退回的
            if (takeoutEarnest == false && hastEarnest) {
                await repository.SimpleUpdate("ue_order_hang",
                    new { earnestMoneyRefunded = 4 }, new { order.orderId, earnestMoneyRefunded = 1 });
            }

            if (refundAmt + order.refundAmount > order.payAmount) {
                throw new Exception("不支持此操作，退款金额大于支付金额");
            }
            if (refundAmt <= 0) {
                var baseResp=new HuiFuRespBase();
                baseResp.CopyBaseInfo(info);
                return await repository.RefundSuccess(baseResp, null,order) ;
            }

            //查询原先的支付信息。
            var payReqInfo = await repository.QuerySingleOrDefaultAsync<UEHuiFuReq>(
                $@"select * from ue_huifu_req where orderId=@orderId
                and billEventType=1 and reqType=1 and dealResult=1 ",new { order.orderId })??
                throw new Exception("不存在支付信息，无法申请退款");

            //生成请求参数
            var reqInfo = new HuiFuRefundInfo {
                ord_amt = refundAmt,
                org_req_date = payReqInfo.createAt.ToString("yyyyMMdd"),
                org_req_seq_id = payReqInfo.reqId
            };
            reqInfo.CopyBaseInfo(info);
            
            //发起汇付请求。
            var resp= await repository.HuiFuRequest<HuiFuRefundResp>(config, reqInfo);
            return resp;
        }

        public static async Task<dynamic> RefundCallback(this DbRESTFulRepository repository,
            HuiFuRespBase info,HuiFuRefundAsyncResp orgResp) {
            var rst = new { count = 0 };
            try {
                var exist = await repository.QuerySingleOrDefaultAsync<UEHuiFuReq>(
                @"select * from ue_huifu_req as r 
                    where r.reqId=@reqId  limit 1",
                new { info.reqId });
                if (exist==null) {
                    LoggerManager.Logger.Error($"存在退款回调伪造嫌疑 - 源id:{info.orgId}" +
                        $" - extra:{JsonConvert.SerializeObject(info.extra)}");
                    return rst;
                }
                info.orderId = exist.orderId.ToString();
                info.orgId = exist.orgId;

                using var conn = repository.ConnectionManager.GetConnection();
                conn.Open();
                var trans = conn.BeginTransaction();
                try {
                    //更新流水表状态,因为同一个orgId可能会有多次请求，所以要更新reqId
                    await repository.SimpleUpdate("ue_huifu_req", new {
                        info.dealResult,
                        info.hfSeqId
                    }, new { info.reqId, dealResult = new List<int> { 0, 2, 3 } }, conn, trans);
                    
                    //退款成功，更新相关状态
                    if (info.dealResult == 1) {
                        await repository.RefundSuccess(info, orgResp,null, conn, trans);                        
                    }
                    trans.Commit();
                } catch (Exception ex) {
                    trans.Rollback();
                    throw;
                }
            } catch (Exception ex) {
                LoggerManager.Logger.Error("处理逻辑出错：" + ex.Message + ex.StackTrace);
            }
            return rst;
        }

        //退款成功操作
        public static async Task<bool> RefundSuccess(this DbRESTFulRepository repository,
            HuiFuRespBase info, HuiFuRefundAsyncResp orgResp, UEOrderHang saleOrder,
            IDbConnection conn = null, IDbTransaction trans = null) {
            var rst = new InsertResult() { count = 0 };
            //更新订单退款金额。
            var order = saleOrder ?? await repository.QuerySingleOrDefaultAsync<UEOrderHang>(
                    $@"select * from ue_order_hang where orderId=@orderId ",
                    new { info.orderId }) ?? throw new Exception("订单不存在");
            var now = DateTime.Now;
            //仅退保证金
            var onlyEarnest = order.orderState == 5 && order.earnestMoneyRefunded == 4;
            if (!onlyEarnest) {
                if (order.payPoints > 0) {
                    //退回价值币
                    var canPoints = order.payPoints;
                    var count = await repository.QuerySingleOrDefaultAsync<int>(
                        @$"update n_user_statistic set points=points+{canPoints} 
                            where userId={order.buyerId};select row_count() as count;",
                        null, conn, trans);
                    if (count > 0) {
                        // 添加价值币变动记录
                        await repository.QueryAsync<dynamic>(@$"insert into n_user_points_record 
                    (userId, points, `way`, extra, description, createAt, createAtDate)
                        values({order.buyerId}, {canPoints}, 'UORDER', '{order.orderId}', 
                    '交易取消-订单{order.orderId}', now(), now())", null, conn, trans);
                    }
                }
                //退回平台账户抵扣金额。
                if (order.accountDeductionAmount > 0) {
                    //钱包金额增加
                    var detail = new UEWalletDetail {
                        detailId = Guid.NewGuid().ToString("N"),
                        detailType = 1,//收入
                        detailEvent = 11,
                        orderId = order.orderId,
                        amount = order.accountDeductionAmount.Value,
                        payway = 1,
                        userId = order.buyerId,
                        createAt = now,
                    };
                    await repository.AddWalletDetail(detail, conn, trans);
                }
            }

            //添加平台流水
            var refundAmt = double.Parse(orgResp?.actual_ref_amt ?? "0") * 100;
            if (refundAmt > 0) {
                var bill = new UEPlatformBill {
                    billId = Guid.NewGuid().ToString("N"),
                    billType = 2,
                    billEvent = onlyEarnest ? 20 : 21,
                    orderId = order.orderId,
                    amount = (int)refundAmt,
                    payway = 1,
                    userId = order.buyerId,
                    createAt = now,
                };
                await repository.AddPlatformBill(bill, conn, trans);

                var cnt = await repository.SimpleUpdate("ue_order_hang",
               new {
                   refundAmount = order.refundAmount + refundAmt
               },
               new { order.orderId }, conn, trans);
            }
            await repository.SimpleUpdate("ue_order_hang",
                new {
                    earnestMoneyRefunded = 2
                },
                new { order.orderId, earnestMoneyRefunded = 4 }, conn, trans);

            return true;
        }


        #endregion

        /// <summary>
        /// 请求汇付接口
        /// </summary>
        /// <typeparam name="TResp"></typeparam>
        /// <param name="repository"></param>
        /// <param name="config"></param>
        /// <param name="info"></param>
        /// <param name="isOrgIdUnique">源id在记录表里是否唯一</param>
        /// <param name="conn"></param>
        /// <param name="trans"></param>
        /// <returns></returns>
        public static async Task<TResp> HuiFuRequest<TResp>(this DbRESTFulRepository repository,
            ConfigFromDb config, HuiFuReqBase info,bool isOrgIdUnique=false,
            IDbConnection conn=null,IDbTransaction trans = null) {
            info.InitNoticeUrl(config);
            HuiFuUtil.InitReqId(info);
            //插入请求记录
            if (isOrgIdUnique) {
                //查询库中的记录。
                var reqRecord = await repository.QuerySingleOrDefaultAsync<UEHuiFuReq>(
                    $@"select orgId from ue_huifu_req where orgId=@orgId limit 1",
                    new { info.orgId });
                if (reqRecord != null) {
                    await repository.SimpleUpdate("ue_huifu_req", new { info.reqId },
                        new { info.orgId });
                } else {
                    await repository.AddHuiFuReq(info);
                }
            } else {
                await repository.AddHuiFuReq(info);
            }

            //插入请求内容
            await repository.SimpleInsert("ue_huifu_req_result", new {
                info.reqId,
                reqInfo = JsonConvert.SerializeObject(info),
            }, conn, trans);

            var resp = HuiFuUtil.Request<TResp>(config.HuiFuConfig,info);
            //状态要改成处理中，失败改成失败
            var dealResult = 3;
            if (resp == null) {
                dealResult = 2;
            } else {
                //请求成功，处理结果为处理中
                if(resp is HuiFuIndvResp indvResp&&
                    !string.IsNullOrEmpty(indvResp.huifu_id)) {
                    dealResult = 1;
                } else {
                    dealResult = 3;
                }
                
                await repository.SimpleUpdate("ue_huifu_req_result", 
                    new { respInfo = JsonConvert.SerializeObject(resp) },
                    new { info.reqId }, conn, trans);
            }
            //更新处理结果
            await repository.SimpleUpdate("ue_huifu_req", new { dealResult },
                new {
                    dealResult = 0,
                    info.reqId
                }, conn, trans);
            return resp;
        }
        public class UEUserExpend {
        /// <summary>
        /// 主键ID
        /// </summary>
        /// <remarks>
        /// 非空
        /// </remarks>
        public int id { get; set; }

        /// <summary>
        /// 操作源id（如支付id，退款id，或提现id等）
        /// </summary>
        /// <remarks>
        /// 非空
        /// </remarks>
        public string orgId { get; set; }

        /// <summary>
        /// 支出数量（货币单位是分，价值币单位是1价值币）
        /// </summary>
        public int expend { get; set; }

        /// <summary>
        /// 支出类型
        /// </summary>
        /// <remarks>
        /// 1，平台账户支出，2，价值币支出
        /// </remarks>
        public int expendType { get; set; }

        /// <summary>
        /// 支出状态
        /// </summary>
        /// <remarks>
        /// 1，支出中，2支出完成，默认1
        /// </remarks>
        public int state { get; set; }

        /// <summary>
        /// 用户id
        /// </summary>
        public long userId { get; set; }

        /// <summary>
        /// 创建时间
        /// </summary>
        /// <remarks>
        /// 非空
        /// </remarks>
        public DateTime createAt { get; set; }

        /// <summary>
        /// 修改时间
        /// </summary>
        public DateTime? updateAt { get; set; }
    }
    public class UEHuiFuReq {
        /// <summary>
        /// 主键ID
        /// </summary>
        /// <remarks>
        /// 非空
        /// </remarks>
        public int id { get; set; }

        /// <summary>
        /// 请求流水号（由原始id加前缀组成，reqType为1,前缀加P,2加I,3加O，4加C，5加R,6加RQ，后缀加随机4位，方便同样的orgId可以多次请求）
        /// </summary>
        /// <remarks>
        /// 非空
        /// </remarks>
        public string reqId { get; set; }

        /// <summary>
        /// 原始id（如果是支付，就是支付id，如果是退款，就是退款id，如果没有，就为空）
        /// </summary>
        public string orgId { get; set; }

        /// <summary>
        /// 汇付流水号（全局流水号）
        /// </summary>
        public string hfSeqId { get; set; }

        /// <summary>
        /// 请求类型（1,聚合正扫，2，用户开户3，用户入驻4，用户提现5，取消订单退款6，取消订单退款查询）
        /// </summary>
        public sbyte reqType { get; set; }

        /// <summary>
        /// 处理结果（0待处理，1成功，2失败，3处理中）
        /// </summary>
        public sbyte dealResult { get; set; }

        /// <summary>
        /// 事件类型（0无，1，订单支付，2商品取回，仓储费支付，3订单取消退款，4，取货保证金退款，5，下单抵扣，6，提现）
        /// </summary>
        public sbyte billEventType { get; set; }

        /// <summary>
        /// 订单编号
        /// </summary>
        public long orderId { get; set; }

        /// <summary>
        /// 用户id
        /// </summary>
        public long userId { get; set; }

        /// <summary>
        /// 原交易流水号
        /// </summary>
        public string orgReqSeqId { get; set; }

        /// <summary>
        /// 创建时间
        /// </summary>
        /// <remarks>
        /// 非空
        /// </remarks>
        public DateTime createAt { get; set; }
    }
        public class UEPlatformBill {
            /// <summary>
            /// 主键ID，流水编号
            /// </summary>
            /// <remarks>
            /// 非空
            /// </remarks>
            public string billId { get; set; }

            /// <summary>
            /// 流水类型
            /// </summary>
            /// <remarks>
            /// 1: 收入，2: 支出
            /// 非空
            /// </remarks>
            public int billType { get; set; }

            /// <summary>
            /// 流水事件
            /// </summary>
            /// <remarks>
            /// 收入类型范围在[10-19]，支出类型范围在[20-29]
            /// 例如：收入：10-订单收入，11-仓储费收入；支出：20-取货保证金退款，21-订单退款，22-提现
            /// </remarks>
            public int billEvent { get; set; }

            /// <summary>
            /// 订单编号
            /// </summary>
            public long? orderId { get; set; }

            /// <summary>
            /// 流水金额单位分
            /// </summary>
            public int amount { get; set; }

            /// <summary>
            /// 支付方式
            /// </summary>
            /// <remarks>
            /// 1: 支付宝，2: 银行卡
            /// </remarks>
            public int payway { get; set; }

            /// <summary>
            /// 用户id
            /// </summary>
            public long userId { get; set; }

            /// <summary>
            /// 创建时间
            /// </summary>
            /// <remarks>
            /// 非空
            /// </remarks>
            public DateTime createAt { get; set; }

            /// <summary>
            /// 修改时间
            /// </summary>
            public DateTime? updateAt { get; set; }
        }
        public class UEWalletDetail {
            /// <summary>
            /// 主键ID，明细编号
            /// </summary>
            /// <remarks>
            /// 非空
            /// </remarks>
            public string detailId { get; set; }

            /// <summary>
            /// 明细类型
            /// </summary>
            /// <remarks>
            /// 1: 收入，2: 支出
            /// 非空
            /// </remarks>
            public int detailType { get; set; }

            /// <summary>
            /// 流水事件
            /// </summary>
            /// <remarks>
            /// 收入类型范围在[10-19]，支出类型范围在[20-29]
            /// 例如：收入：10-卖货收入，11-撤单退回，12-取货保证金退回；
            /// 支出：20-下单抵扣，21-提现
            /// </remarks>
            public int detailEvent { get; set; }

            /// <summary>
            /// 订单编号
            /// </summary>
            public long? orderId { get; set; }

            /// <summary>
            /// 流水金额 单位分
            /// </summary>
            public int amount { get; set; }
            /// <summary>
            /// 当前结存
            /// </summary>
            public int balance { get; set; }

            /// <summary>
            /// 支付方式
            /// </summary>
            /// <remarks>
            /// 1: 支付宝，2: 银行卡
            /// </remarks>
            public int payway { get; set; }

            /// <summary>
            /// 用户id
            /// </summary>
            public long userId { get; set; }

            /// <summary>
            /// 创建时间
            /// </summary>
            /// <remarks>
            /// 非空
            /// </remarks>
            public DateTime createAt { get; set; }

            /// <summary>
            /// 修改时间
            /// </summary>
            public DateTime? updateAt { get; set; }
        }
    }
}