dotnet/molilian.core/Plus/Alimama/RefundOrders.cs

using dodohold.core;
using Spire.Pdf.Xmp;
using System.Data;
using System.Drawing.Printing;
using System.Runtime.InteropServices;
using System.Text.Json;
using System.Text.RegularExpressions;
using static System.Runtime.InteropServices.JavaScript.JSType;

namespace molilian.core
{
    public partial class AlimamaPlus
    {
        private const string base_refund_orders_url = "https://pub.alimama.com/openapi/param2/1/gateway.unionpub/report.publisher.getRefundOrderDetail.json";

        public int GetHistoryRefundOrders(int sleep, DateTime startTime, DateTime endTime)
        {

            //DateTime endTime = DateTime.Now;
            //DateTime startTime = endTime.AddDays(-90);
            //if (_accountId == 3) endTime = DateTime.Parse("2024-04-07");
            //#if DEBUG

            //            startTime = DateTime.Parse("2024-05-01");
            //            endTime = DateTime.Parse("2024-06-01");


            //            int total = 0;

            //            List<DateTime> orderTimes = new List<DateTime>();

            //            for (DateTime date = startTime; date <= endTime; date = date.AddDays(1))
            //            {
            //                orderTimes.Add(date);
            //            }


            //#else
            //当日往前查询
            (int total, DateTime max_modifiedTime, List<DateTime> orderTimes) = GetTkRefundOrders(startTime, endTime, DateTime.MinValue, false, sleep);

            //#endif       

            string sql = @"
            UPDATE tk_report tr
            JOIN (
                SELECT
                    accountId, DATE(tbTradeCreateTime) AS date, SUM(refundFee) AS refund_order_ord_amt,
                    COUNT(*) AS refund_order_ord_num, SUM(showReturnFee) AS refund_order_ord_tfee
                FROM tk_refund_order_details
                WHERE DATE(tbTradeCreateTime) IN @orderTimes AND refundStatus=2 AND accountId=@accountId
                GROUP BY DATE(tbTradeCreateTime), accountId
            ) AS sub
            ON tr.accountId = sub.accountId AND tr.report_date = sub.date
            SET tr.refund_order_ord_amt = sub.refund_order_ord_amt, 
                tr.refund_order_ord_tfee = sub.refund_order_ord_tfee, 
                tr.refund_order_ord_num = sub.refund_order_ord_num;";
            DBContext.Execute(sql, new { accountId = _accountId, orderTimes });


            sql = @"
            UPDATE tk_report tr
            JOIN (
                SELECT
                    accountId, DATE(tbTradeCreateTime) AS date, SUM(refundFee) AS refund_order_ord_amt,
                    COUNT(*) AS refund_order_ord_num, SUM(showReturnFee) AS refund_order_ord_tfee
                FROM tk_refund_order_details
                WHERE DATE(tbTradeCreateTime) IN @orderTimes AND refundStatus=4 AND accountId=@accountId
                GROUP BY DATE(tbTradeCreateTime), accountId
            ) AS sub
            ON tr.accountId = sub.accountId AND tr.report_date = sub.date
            SET tr.refund_order_ord_amt_4 = sub.refund_order_ord_amt, 
                tr.refund_order_ord_tfee_4 = sub.refund_order_ord_tfee, 
                tr.refund_order_ord_num_4 = sub.refund_order_ord_num;";
            DBContext.Execute(sql, new { accountId = _accountId, orderTimes });




            sql = @"
            UPDATE tk_report tr
            JOIN (
                SELECT
                    accountId, DATE(RefundCreateTime) AS date, 
                    SUM(showReturnFee) AS refund_order_ord_tfee
                FROM tk_refund_order_details
                WHERE DATE(RefundCreateTime) IN @orderTimes AND accountId=@accountId
                GROUP BY DATE(RefundCreateTime), accountId
            ) AS sub
            ON tr.accountId = sub.accountId AND tr.report_date = sub.date
            SET tr.refund_order_ord_freeze_tfee = 0-sub.refund_order_ord_tfee;";
            DBContext.Execute(sql, new { accountId = _accountId, orderTimes });


            sql = @"
            UPDATE tk_report tr
            JOIN (
                SELECT
                    accountId, DATE(RefundFinishTime) AS date,
                    SUM(showReturnFee) AS refund_order_ord_tfee
                FROM tk_refund_order_details
                WHERE DATE(RefundFinishTime) IN @orderTimes AND refundStatus=2 AND accountId=@accountId
                GROUP BY DATE(RefundFinishTime), accountId
            ) AS sub
            ON tr.accountId = sub.accountId AND tr.report_date = sub.date
            SET tr.refund_order_ord_deduct_tfee = 0-sub.refund_order_ord_tfee;";
            DBContext.Execute(sql, new { accountId = _accountId, orderTimes });


            sql = @"
            UPDATE tk_report tr
            JOIN (
                SELECT
                    accountId, DATE(RefundFinishTime) AS date,
                    SUM(showReturnFee) AS refund_order_ord_tfee
                FROM tk_refund_order_details
                WHERE DATE(RefundFinishTime) IN @orderTimes AND refundStatus IN (2,3) AND accountId=@accountId
                GROUP BY DATE(RefundFinishTime), accountId
            ) AS sub
            ON tr.accountId = sub.accountId AND tr.report_date = sub.date
            SET tr.refund_order_ord_unfreeze_tfee = sub.refund_order_ord_tfee;";
            DBContext.Execute(sql, new { accountId = _accountId, orderTimes });



            //refundStatus3  失败
            //2 成功
            //4创建

            //public decimal refund_order_ord_deduct_tfee { get; set; } = 0;
            //public decimal refund_order_ord_freeze_tfee { get; set; } = 0;
            //public decimal refund_order_ord_unfreeze_tfee { get; set; } = 0;




            return total;
        }
        public int GetIncyRefundOrders(int sleep)
        {
            string cacheKey = $":cache:GetIncyRefundOrders:{_accountId}";
            DateTime endTime = DateTime.Now;
            DateTime startTime = endTime.AddDays(-1);


            DateTime last_modifiedTime = RedisHelper.Get<DateTime>(cacheKey);
            ////找出数据库最新的一天
            //var last = new DBContext.Table("tk_order_details")
            //     .Order("modifiedTime DESC")
            //     .Get<TkRefundOrderDetailDTO>("accountId=@accountId", new { accountId = _accountId });


            if (last_modifiedTime != DateTime.MinValue)
            {
                startTime = last_modifiedTime;
            }
            else
            {
                last_modifiedTime = startTime;
            }

            //当日往前查询
            (int total, DateTime max_modifiedTime, List<DateTime> orderTimes) = GetTkRefundOrders(startTime, endTime, last_modifiedTime, true, sleep);
            if (max_modifiedTime != DateTime.MinValue)
            {
                RedisHelper.Set(cacheKey, max_modifiedTime);
            }


            string sql = @"
            UPDATE tk_report tr
            JOIN (
                SELECT
                    accountId, DATE(tbTradeCreateTime) AS date, SUM(refundFee) AS refund_order_ord_amt,
                    COUNT(*) AS refund_order_ord_num, SUM(showReturnFee) AS refund_order_ord_tfee
                FROM tk_refund_order_details
                WHERE DATE(tbTradeCreateTime) IN @orderTimes AND refundStatus=2 AND accountId=@accountId
                GROUP BY DATE(tbTradeCreateTime), accountId
            ) AS sub
            ON tr.accountId = sub.accountId AND tr.report_date = sub.date
            SET tr.refund_order_ord_amt = sub.refund_order_ord_amt, 
                tr.refund_order_ord_tfee = sub.refund_order_ord_tfee, 
                tr.refund_order_ord_num = sub.refund_order_ord_num;";
            DBContext.Execute(sql, new { accountId = _accountId, orderTimes });


            sql = @"
            UPDATE tk_report tr
            JOIN (
                SELECT
                    accountId, DATE(tbTradeCreateTime) AS date, SUM(refundFee) AS refund_order_ord_amt,
                    COUNT(*) AS refund_order_ord_num, SUM(showReturnFee) AS refund_order_ord_tfee
                FROM tk_refund_order_details
                WHERE DATE(tbTradeCreateTime) IN @orderTimes AND refundStatus=4 AND accountId=@accountId
                GROUP BY DATE(tbTradeCreateTime), accountId
            ) AS sub
            ON tr.accountId = sub.accountId AND tr.report_date = sub.date
            SET tr.refund_order_ord_amt_4 = sub.refund_order_ord_amt, 
                tr.refund_order_ord_tfee_4 = sub.refund_order_ord_tfee, 
                tr.refund_order_ord_num_4 = sub.refund_order_ord_num;";
            DBContext.Execute(sql, new { accountId = _accountId, orderTimes });



            sql = @"
            UPDATE tk_report tr
            JOIN (
                SELECT
                    accountId, DATE(RefundCreateTime) AS date, 
                    SUM(showReturnFee) AS refund_order_ord_tfee
                FROM tk_refund_order_details
                WHERE DATE(RefundCreateTime) IN @orderTimes AND accountId=@accountId
                GROUP BY DATE(RefundCreateTime), accountId
            ) AS sub
            ON tr.accountId = sub.accountId AND tr.report_date = sub.date
            SET tr.refund_order_ord_freeze_tfee = 0-sub.refund_order_ord_tfee;";
            DBContext.Execute(sql, new { accountId = _accountId, orderTimes });


            sql = @"
            UPDATE tk_report tr
            JOIN (
                SELECT
                    accountId, DATE(RefundFinishTime) AS date,
                    SUM(showReturnFee) AS refund_order_ord_tfee
                FROM tk_refund_order_details
                WHERE DATE(RefundFinishTime) IN @orderTimes AND refundStatus=2 AND accountId=@accountId
                GROUP BY DATE(RefundFinishTime), accountId
            ) AS sub
            ON tr.accountId = sub.accountId AND tr.report_date = sub.date
            SET tr.refund_order_ord_deduct_tfee = 0-sub.refund_order_ord_tfee;";
            DBContext.Execute(sql, new { accountId = _accountId, orderTimes });


            sql = @"
            UPDATE tk_report tr
            JOIN (
                SELECT
                    accountId, DATE(RefundFinishTime) AS date,
                    SUM(showReturnFee) AS refund_order_ord_tfee
                FROM tk_refund_order_details
                WHERE DATE(RefundFinishTime) IN @orderTimes AND refundStatus IN (2,3) AND accountId=@accountId
                GROUP BY DATE(RefundFinishTime), accountId
            ) AS sub
            ON tr.accountId = sub.accountId AND tr.report_date = sub.date
            SET tr.refund_order_ord_unfreeze_tfee = sub.refund_order_ord_tfee;";
            DBContext.Execute(sql, new { accountId = _accountId, orderTimes });


            return total;
        }

        public (int, DateTime, List<DateTime>) GetTkRefundOrders(DateTime startTime, DateTime endTime, DateTime last_modifiedTime, bool desc = true, int sleep = 100)
        {
            List<DateTime> orderTimes = [];

            // 确保 startTime 小于 endTime
            if (startTime > endTime)
            {
                (startTime, endTime) = (endTime, startTime);
            }

            DateTime max_modifiedTime = last_modifiedTime;

            int pageNo = 1;
            string positionIndex = string.Empty;
            int pageSize = 100;

            bool hasNext = true;
            int total = 0;
            while (hasNext)
            {
                var ts = DateTime.Now.Convert2UnixTimestamp(true);
                //string jumpType = desc ? pageNo == 1 ? "0" : "1" : "-1";
                string jumpType = pageNo == 1 ? "0" : "1";

                //string queryType = "1";
                //1 创建时间；3结算时间 2付款时间 4更新时间
                //string queryType = "2";
                string queryType = desc ? "4" : "2";

                string url = $"{base_refund_orders_url}?t={ts}&_tb_token_={_tb_token}&pageNo={pageNo}&pageSize={pageSize}&startTime={startTime:yyyy-MM-dd}&endTime={endTime:yyyy-MM-dd}&payStatus=&queryType={queryType}&toPage=1&perPageSize={pageSize * 2}&jumpType={jumpType}&isFullRefund=0&memberType=&positionIndex={positionIndex.UrlEncode()}";

                string body = "";

                JsonElement data;
                try
                {
                    for (var i = 1; i <= 5; i++)
                    {
                        var client = new WebClientUtility().SetContentType("application/json;charset=utf-8")
                              .AddHeaders("X-Requested-With", "XMLHttpRequest")
                              .AddHeaders("Cookie", _cookies);
#if DEBUG
#else
                        client.Proxy = _proxy;
#endif
                        if (!string.IsNullOrEmpty(_user_agent)) client.UserAgent = _user_agent;
                        var response = client.Request(url);

                        if (response.ResponseException != null)
                        {
                            _ = new LoggerLibrary("api_error", "fail")
                                .Info(response.ResponseException.Message, response.ResponseException.StackTrace)
                                .SaveAsync();
                            throw response.ResponseException;
                        }
                        body = response.Body();
                        if (body.Contains("{\"action\":\"captcha\""))
                        {
                            string message = $"【GetTkRefundOrders】【{_accountId}:{_accountName}】第 {i} 次出现滑动验证码";
                            NotifyCore.Notify(new NifyMessage
                            {
                                message = message,
                                priority = NifyMessagePriority.high,
                                tags = ["red_circle"]
                            });
                            Thread.Sleep(i * 60 * 1000);
                            continue;
                        }

                        if (body.Contains("\"resultCode\":500"))
                        {
                            string message = $"【GetTkRefundOrders】【{_accountId}:{_accountName}】第 {i} 次出现错误\n{body}";
                            NotifyCore.Notify(new NifyMessage
                            {
                                message = message,
                                priority = NifyMessagePriority.high,
                                tags = ["red_circle"]
                            });
                            Thread.Sleep(i * 60 * 1000);
                            continue;
                        }
                        break;
                    }
                    var root = body.Convert2JsonElement();
                    var success = root.Read<bool>("success");
                    data = root.ElementRead("data");


                    //{"code":601,"info":{"ok":false,"message":"nologin"}}
                    if (!success && body.Contains("nologin"))
                    {
                        (success, string message) = RenewCookie();
                        if (!success)
                        {
                            TkPoolCore.Disabled(_accountId, _accountName, $"{body}");
                        }
                        return (0, DateTime.MinValue, new List<DateTime>());
                    }

                    if (!success)
                    {
                        throw new APIException(body);
                    }
                }
                catch (Exception ex)
                {
                    throw new APIException(body);
                }
                using var conn = DBContext.GetOpenConnection();
                conn.Open();

                try
                {
                    foreach (var order in data.ElementRead("result").EnumerateArray())
                    {
                        TkRefundOrderDetailDTO item = order.GetRawText().Convert2Object<TkRefundOrderDetailDTO>();
                        // 处理每个订单数据 

                        item.accountId = _accountId;
                        item.accountName = _company;

                        if (item.ModifiedTime > max_modifiedTime) max_modifiedTime = item.ModifiedTime;

                        if (last_modifiedTime != DateTime.MinValue && last_modifiedTime >= item.ModifiedTime)
                        {
                            return (total, max_modifiedTime, orderTimes);
                        }

                        //进行订单和转链匹配
                        orderTimes.Add(item.TbTradeCreateTime.Date);
                        switch (item.RefundStatus)
                        {
                            case 4:
                                orderTimes.Add(item.RefundCreateTime.Date);
                                break;
                            case 2:
                            case 3:
                                orderTimes.Add(item.RefundFinishTime.Date);
                                break;
                        }

                        if (item.ShowReturnFee == 0)
                        {
                            item.ShowReturnFee = Math.Round(item.PubShareFee / item.AlipayTotalPrice * item.RefundFee, 2);
                        }
                        int? id = conn.Replace(item);
                        if (id != null)
                        {
                            item.id = (int)id;
                        }
                        total++;
                    }
                }
                catch (Exception ex)
                {
                    throw;
                }
                finally
                {
                    conn.Dispose();
                }
                pageNo++;
                hasNext = data.Read<bool>("hasNext");
                positionIndex = data.Read<string>("positionIndex");

                if (sleep > 0) Thread.Sleep(sleep);
            }
            return (total, max_modifiedTime, orderTimes);
        }


    }
}