no message

zhaojs

2023-09-15 fc13938ff90213060532d99a600dea4a84456885

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
using Operater.DAL;
using Quartz;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Threading.Tasks;
using CommonUtil;
using Operater.DbModel;
 
namespace ScheduledTasks.Handle
{
    public class DataStatisticsHandle : IJob
    {
        /// <summary>
        /// 数据统计
        /// </summary>
        /// <param name="context"></param>
        /// <returns></returns>
        public async Task Execute(IJobExecutionContext context)
        {
            await Synchronize(context);
        }
        public static async Task Synchronize(IJobExecutionContext context)
        {
            try
            {
                //获取上次统计时间
                string timeSql = $"select max(static_time) as static_time from data_static;";
                DataTable timeDt = new YzOrderinfoDAL().SearchBySqlDataTable("", timeSql);
                DateTime staticTime = DateTime.Now.AddDays(-1).Date;
                //if (!timeDt.IsNull() && !timeDt.Rows[0]["static_time"].IsNull() && !timeDt.Rows[0]["static_time"].ToString().IsNullOrEmpty())
                //{
                //    staticTime = Convert.ToDateTime(timeDt.Rows[0]["static_time"].ToString()).Date;
                //}
                //if (staticTime == DateTime.Now.AddDays(-1).Date)
                //{
                //    Console.WriteLine($"已经统计过，跳过！");
                //    return;
                //}
                DataStatic dataStatic = new DataStatic()
                {
                    Id = Guid.NewGuid().ToString(),
                    StaticTime = staticTime
                };
 
                string relaStr = $"SELECT yz_tid as tid FROM trade_create_relation where run_time>='{staticTime.ToString("yyyy-MM-dd 00:00:00")}' and run_time<='{staticTime.ToString("yyyy-MM-dd 23:59:59")}' and run_status='2'";
                string staticCount = $"SELECT count(1) as yz_count,sum(payment) as yz_total_fee from yz_tradeinfo where tid in({relaStr}) ";
                DataTable staticCountDt = new YzOrderinfoDAL().SearchBySqlDataTable("", staticCount);
                if (!staticCountDt.IsNull())
                {
                    //有赞订单量
                    dataStatic.YzTradeCount = int.Parse(staticCountDt.Rows[0]["yz_count"].ToString());
                    //有赞订单总金额
                    dataStatic.YzTotalFee = staticCountDt.Rows[0]["yz_total_fee"].ToString().IsNullOrEmpty()?0:decimal.Parse(staticCountDt.Rows[0]["yz_total_fee"].ToString());
                }
                staticCount = $"SELECT count(1) as yz_count,sum(real_pay) as realpay from yz_tradeinfo where tid in({relaStr}) and real_pay>0";
                staticCountDt = new YzOrderinfoDAL().SearchBySqlDataTable("", staticCount);
                if (!staticCountDt.IsNull())
                {
                    //有赞付款订单量
                    dataStatic.YzPayCount = int.Parse(staticCountDt.Rows[0]["yz_count"].ToString());
                    //有赞付款金额
                    dataStatic.YzPayment = staticCountDt.Rows[0]["realpay"].ToString().IsNullOrEmpty()?0: decimal.Parse(staticCountDt.Rows[0]["realpay"].ToString());
                }
                relaStr = $"SELECT ali_tid as tid FROM trade_create_relation where run_time>='{staticTime.ToString("yyyy-MM-dd 00:00:00")}' and run_time<='{staticTime.ToString("yyyy-MM-dd 23:59:59")}' and run_status='2'";
                staticCount = $"select sum(sum_product_payment+shipping_fee) as payment from ali_tradeinfo where tid in({relaStr})";
                staticCountDt = new YzOrderinfoDAL().SearchBySqlDataTable("", staticCount);
                if (!staticCountDt.IsNull())
                {
                    //1688订单金额
                    dataStatic.AliTotalFee = staticCountDt.Rows[0]["payment"].ToString().IsNullOrEmpty()?0: decimal.Parse(staticCountDt.Rows[0]["payment"].ToString());
                    dataStatic.ProfitPay = dataStatic.YzPayment - dataStatic.AliTotalFee;
                }
                //获取时间内交易成功的订单
                string yzFeeStr = $"select sum(payment) as yz_total_fee from yz_tradeinfo where success_time>='{staticTime.ToString("yyyy-MM-dd 00:00:00")}' and success_time<='{staticTime.ToString("yyyy-MM-dd 23:59:59")}'";
                staticCountDt = new YzOrderinfoDAL().SearchBySqlDataTable("", yzFeeStr);
                if (!staticCountDt.IsNull() && !staticCountDt.Rows[0]["yz_total_fee"].ToString().IsNullOrEmpty() && decimal.Parse(staticCountDt.Rows[0]["yz_total_fee"].ToString()) > 0)
                {//有交易成功的订单
                    string successYzStr = $"select tid from yz_tradeinfo where success_time>='{staticTime.ToString("yyyy-MM-dd 00:00:00")}' and success_time<='{staticTime.ToString("yyyy-MM-dd 23:59:59")}'";
                    string relaALiTid = $"select ali_tid as tid FROM trade_create_relation where yz_tid in({successYzStr})";
                    relaALiTid = $"select sum(sum_product_payment+shipping_fee) as payment from ali_tradeinfo where tid in({relaALiTid})";
                    DataTable aliSucDt = new YzOrderinfoDAL().SearchBySqlDataTable("", relaALiTid);
                    if (!aliSucDt.IsNull())
                    {
                        dataStatic.ProfitSuccess = decimal.Parse(staticCountDt.Rows[0]["yz_total_fee"].ToString()) - decimal.Parse(aliSucDt.Rows[0]["payment"].ToString());
                    }
                }
                else
                {
                    dataStatic.ProfitSuccess = 0;
                }
                new DataStaticDAL().Insert(dataStatic);
            }
            catch (Exception e)
            {
                Console.WriteLine($"数据统计错误：{e.ToString()}");
                LogUtil.Info(e.ToString(), "数据统计错误");
            }
        }
    }
}