dotnet/molilian.core/Plus/JDUnion/order.cs

using dodohold.core;
using Sayaka.Common;
using System.Text.Json;
using System.Threading;


namespace molilian.core
{
    public partial class JdUnionPlus
    {

        public async Task<int> GetHistoryOrders(int sleep, DateTime startTime, DateTime endTime)
        {
            //当日往前查询
            (int total, DateTime max_modifiedTime, List<DateTime> orderTimes) = await GetTkOrdersAsync(startTime, endTime, DateTime.MinValue, optType: 2, sleep);

            string sql = @"
UPDATE tk_report tr
JOIN (
    SELECT
        accountId, DATE(tbPaidTime) AS date, SUM(alipayTotalPrice) AS order_ord_amt,
        COUNT(*) AS order_ord_num, SUM(pubShareFeeForCommission) AS order_ord_tfee
    FROM tk_order_details
    WHERE DATE(tbPaidTime) IN @orderTimes AND tkStatus=3 AND accountId=@accountId
    GROUP BY DATE(tbPaidTime), accountId
) AS sub
ON tr.accountId = sub.accountId AND tr.report_date = sub.date
SET tr.order_ord_amt = sub.order_ord_amt, tr.order_ord_num_3 = sub.order_ord_num, 
    tr.order_ord_tfee = sub.order_ord_tfee;";
            //DBContext.Execute(sql, new { accountId = _account.id, orderTimes });

            sql = @"
UPDATE tk_report tr
JOIN (
    SELECT
        accountId, DATE(tbPaidTime) AS date, SUM(alipayTotalPrice) AS order_ord_amt, 
        COUNT(*) AS order_ord_num, SUM(pubSharePreFeeForCommission) AS order_ord_tfee
    FROM tk_order_details
    WHERE DATE(tbPaidTime) IN @orderTimes AND tkStatus=12 AND accountId=@accountId
    GROUP BY DATE(tbPaidTime), accountId
) AS sub
ON tr.accountId = sub.accountId AND tr.report_date = sub.date
SET tr.order_ord_amt_12 = sub.order_ord_amt, tr.order_ord_num_12 = sub.order_ord_num, 
    tr.order_ord_tfee_12 = sub.order_ord_tfee;";
            //DBContext.Execute(sql, new { accountId = _account.id, orderTimes });

            sql = @"
UPDATE tk_report tr
JOIN (
    SELECT
        accountId, DATE(tbPaidTime) AS date, SUM(alipayTotalPrice) AS order_ord_amt, 
        COUNT(*) AS order_ord_num, SUM(pubSharePreFeeForCommission) AS order_ord_tfee
    FROM tk_order_details
    WHERE DATE(tbPaidTime) IN @orderTimes AND tkStatus=13 AND accountId=@accountId
    GROUP BY DATE(tbPaidTime), accountId
) AS sub
ON tr.accountId = sub.accountId AND tr.report_date = sub.date
SET tr.order_ord_amt_13 = sub.order_ord_amt, tr.order_ord_num_13 = sub.order_ord_num, 
    tr.order_ord_tfee_13 = sub.order_ord_tfee;";
            //DBContext.Execute(sql, new { accountId = _account.id, orderTimes });

            sql = @"
UPDATE tk_report tr
JOIN (
    SELECT
        accountId, DATE(tbPaidTime) AS date, SUM(alipayTotalPrice) AS order_ord_amt, 
        COUNT(*) AS order_ord_num, SUM(pubSharePreFeeForCommission) AS order_ord_tfee
    FROM tk_order_details
    WHERE DATE(tbPaidTime) IN @orderTimes AND tkStatus=14 AND accountId=@accountId
    GROUP BY DATE(tbPaidTime), accountId
) AS sub
ON tr.accountId = sub.accountId AND tr.report_date = sub.date
SET tr.order_ord_amt_14 = sub.order_ord_amt, tr.order_ord_num_14 = sub.order_ord_num, 
    tr.order_ord_tfee_14 = sub.order_ord_tfee;";
            //DBContext.Execute(sql, new { accountId = _account.id, orderTimes });

            sql = @"UPDATE tk_report 
SET  order_ord_num = order_ord_num_3 + order_ord_num_12 + order_ord_num_13 + order_ord_num_14
 WHERE DATE(report_date) IN @orderTimes AND accountId=@accountId;";
            //DBContext.Execute(sql, new { accountId = _account.id, orderTimes });

            return total;

        }
        public async Task<int> GetIncyOrders(int sleep)
        {
            string cacheKey = $":cache:GetIncyJDOrders:{_account.name}";
            string cacheKey2 = $":cache:GetIncyJDOrders:{_account.id}";
            DateTime endTime = DateTime.Now;
            DateTime startTime = endTime.AddDays(-7);


            DateTime last_modifiedTime = RedisHelper.Get<DateTime>(cacheKey);

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

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

            //todo 根据orderTimes、settlementTimes 更新被影响的相关行 

            string sql = @"
UPDATE tk_report tr
JOIN (
    SELECT
        accountId, DATE(tbPaidTime) AS date, SUM(alipayTotalPrice) AS order_ord_amt,
        COUNT(*) AS order_ord_num, SUM(pubShareFeeForCommission) AS order_ord_tfee
    FROM tk_order_details
    WHERE DATE(tbPaidTime) IN @orderTimes AND tkStatus=3 AND accountId=@accountId
    GROUP BY DATE(tbPaidTime), accountId
) AS sub
ON tr.accountId = sub.accountId AND tr.report_date = sub.date
SET tr.order_ord_amt = sub.order_ord_amt, tr.order_ord_num_3 = sub.order_ord_num, 
    tr.order_ord_tfee = sub.order_ord_tfee;";
            //DBContext.Execute(sql, new { accountId = _account.id, orderTimes });

            sql = @"
UPDATE tk_report tr
JOIN (
    SELECT
        accountId, DATE(tbPaidTime) AS date, SUM(alipayTotalPrice) AS order_ord_amt, 
        COUNT(*) AS order_ord_num, SUM(pubSharePreFeeForCommission) AS order_ord_tfee
    FROM tk_order_details
    WHERE DATE(tbPaidTime) IN @orderTimes AND tkStatus=12 AND accountId=@accountId
    GROUP BY DATE(tbPaidTime), accountId
) AS sub
ON tr.accountId = sub.accountId AND tr.report_date = sub.date
SET tr.order_ord_amt_12 = sub.order_ord_amt, tr.order_ord_num_12 = sub.order_ord_num, 
    tr.order_ord_tfee_12 = sub.order_ord_tfee;";
            //DBContext.Execute(sql, new { accountId = _account.id, orderTimes });

            sql = @"
UPDATE tk_report tr
JOIN (
    SELECT
        accountId, DATE(tbPaidTime) AS date, SUM(alipayTotalPrice) AS order_ord_amt, 
        COUNT(*) AS order_ord_num, SUM(pubSharePreFeeForCommission) AS order_ord_tfee
    FROM tk_order_details
    WHERE DATE(tbPaidTime) IN @orderTimes AND tkStatus=13 AND accountId=@accountId
    GROUP BY DATE(tbPaidTime), accountId
) AS sub
ON tr.accountId = sub.accountId AND tr.report_date = sub.date
SET tr.order_ord_amt_13 = sub.order_ord_amt, tr.order_ord_num_13 = sub.order_ord_num, 
    tr.order_ord_tfee_13 = sub.order_ord_tfee;";
            //DBContext.Execute(sql, new { accountId = _account.id, orderTimes });

            sql = @"
UPDATE tk_report tr
JOIN (
    SELECT
        accountId, DATE(tbPaidTime) AS date, SUM(alipayTotalPrice) AS order_ord_amt, 
        COUNT(*) AS order_ord_num, SUM(pubSharePreFeeForCommission) AS order_ord_tfee
    FROM tk_order_details
    WHERE DATE(tbPaidTime) IN @orderTimes AND tkStatus=14 AND accountId=@accountId
    GROUP BY DATE(tbPaidTime), accountId
) AS sub
ON tr.accountId = sub.accountId AND tr.report_date = sub.date
SET tr.order_ord_amt_14 = sub.order_ord_amt, tr.order_ord_num_14 = sub.order_ord_num, 
    tr.order_ord_tfee_14 = sub.order_ord_tfee;";
            //DBContext.Execute(sql, new { accountId = _account.id, orderTimes });

            sql = @"UPDATE tk_report 
SET  order_ord_num = order_ord_num_3 + order_ord_num_12 + order_ord_num_13 + order_ord_num_14
 WHERE DATE(report_date) IN @orderTimes AND accountId=@accountId;";
            //DBContext.Execute(sql, new { accountId = _account.id, orderTimes });

            return total;
        }



        public async Task<(int, DateTime, List<DateTime>)> GetTkOrdersAsync(DateTime startTime, DateTime endTime, DateTime last_modifiedTime, int optType, int sleep = 100)
        {
            List<DateTime> orderTimes = [];

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

            DateTime max_modifiedTime = last_modifiedTime;

            int pageNo = 1;
            int pageSize = 100;

            bool hasNext = true;
            int total = 0;
            while (hasNext)
            {
                string body = "";
                JsonElement data;
                try
                {
                    for (var i = 1; i <= 5; i++)
                    {

                        var ts = DateTime.Now.Convert2UnixTimestamp(true);
                        string cookies = _account.cookies;

                        string uuid = cookies.GetContentPart("__jdu=", ";");
                        string __jda = cookies.GetContentPart("__jda=", ";");
                        if (__jda.Split('.').Length > 1)
                        {
                            uuid = __jda.Split('.')[1];
                        }

                        string useragent = _account.user_agent;
                        if (string.IsNullOrEmpty(useragent)) useragent = ProviderFakeUserAgent.RandomComputer;
                        string url = $"https://api.m.jd.com/api?functionId=listOrderSku&" +
                            $"appid=unionpc&_={ts}&loginType=3&uuid={uuid}";


                        var args = new
                        {
                            funName = "listOrderSku",
                            page = new
                            {
                                pageNo,
                                pageSize
                            },
                            param = new
                            {
                                unionRole = 1,
                                startTime = startTime.ToString("yyyy-MM-dd HH:mm:ss"),
                                endTime = endTime.ToString("yyyy-MM-dd HH:mm:ss"),
                                orderStatus = 0,
                                unionTags = new string[] { "0" },
                                optType,
                                spId = ""
                            },
                            lientPageId = "jingfen_pc"
                        };

                        string post = args.Convert2Json().UrlEncode();

                        WebClientUtility client = new WebClientUtility();
                        client.Proxy = _proxy;
#if DEBUG
                        client.Proxy = null;
#endif
                        client.SetContentType("application/x-www-form-urlencoded");
                        client.AddHeaders("X-Referer-Page", "https://union.jd.com/proManager/index");
                        client.AddHeaders("X-Rp-Client", "h5_1.0.0");
                        client.AddHeaders("Referer", "https://union.jd.com/");
                        client.AddHeaders("Origin", "https://union.jd.com");
                        client.UserAgent = useragent;
                        client.AddHeaders("Cookie", cookies);
                        client.Post($"body={post}");
                        var response = await client.RequestAsync(url, "POST");
#if DEBUG
#else
                        client.Proxy = _proxy;
#endif

                        if (response.ResponseException != null)
                        {
                            _ = new LoggerLibrary("api_error", "fail")
                                .Info(response.ResponseException.Message, response.ResponseException.StackTrace)
                                .SaveAsync();
                            throw response.ResponseException;
                        }
                        body = response.Body();
#if DEBUG
                        _ = new LoggerLibrary("debug", "GetJdOrders")
                            .Info(body)
                            .SaveAsync();
#endif  
                        break;
                    }
                    data = body.Convert2JsonElement();

                    //{"code":452,"hasNext":false,"message":"单位时间内订单量太多,请缩短查询时间范围","totalNum":0}

                    var code = data.Read<int>("code");

                    if (code != 200)
                    {
                        if (body.Contains("no login"))
                        {
                            JdPoolCore.Disabled(_account.id, _account.name, $"{body}");
                            return (0, DateTime.MinValue, new List<DateTime>());
                        }
                        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())
                    {
                        JdOrderDetailsDTO item;
                        try
                        {

                            item = order.GetRawText().Convert2Object<JdOrderDetailsDTO>();
                            //todo 处理结算时间和结算金额 balanceExt "{\"20240920\":100.539}"

                        }
                        catch (Exception ex)
                        {
                            throw;
                        }

                        //todo 处理结算时间和结算金额 balanceExt "{\"20240920\":100.539}"
                        item.accountId = _account.id;
                        item.accountName = _account.name;

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

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

                        var exist = new DBContext.Table("jd_order_details")
                                   .Fields("id, orderId")
                                   .Get<dynamic>("orderid=@orderid", new { item.orderId });
                        if (exist != null)
                        {
                            var id = DBContext.Update<JdOrderDetailsDTO>(exist.id, item);
                        }
                        else
                        {
                            conn.Insert(item);
                        }
                        total++;
                    }
                }
                catch (Exception ex)
                {
                    throw;
                }
                finally
                {
                    conn.Dispose();
                }
                pageNo++;
                hasNext = data.Read<bool>("hasNext");
                if (sleep > 0) Thread.Sleep(sleep);
            }
            return (total, max_modifiedTime, orderTimes);
        }

    }
}