lkb-stat/AppStart/Helper/StatResetService.cs

using System;
using System.Collections.Generic;
using Library;
using LitJson;
using System.Linq;
using System.Data;
using System.Threading;
using MySystem.PxcModels;

namespace MySystem
{
    public class StatResetService
    {
        public readonly static StatResetService Instance = new StatResetService();
        private StatResetService()
        { }


        public void StartReset()
        {
            Thread th = new Thread(StartResetMerchantDo);
            th.IsBackground = true;
            th.Start();

            Thread thUser = new Thread(StartResetUserSelfDo);
            thUser.IsBackground = true;
            thUser.Start();

            Thread thUserTeam = new Thread(StartResetUserTeamDo);
            thUserTeam.IsBackground = true;
            thUserTeam.Start();

            Thread thSn = new Thread(StartResetSnDo);
            thSn.IsBackground = true;
            thSn.Start();
        }
        public void StartResetMerchantDo()
        {
            while (true)
            {
                string content = RedisDbconn.Instance.RPop<string>("ResetMerchantStatDataQueue");
                if (!string.IsNullOrEmpty(content))
                {
                    StatResetMerchantAmount(content);
                }
                Thread.Sleep(10000);
            }
        }
        public void StatResetMerchantAmount(string content)
        {
            JsonData jsonObj = JsonMapper.ToObject(content);
            string merchantId = jsonObj["MerchantId"].ToString();
            string startDate = jsonObj["StartDate"].ToString();
            string endDate = jsonObj["EndDate"].ToString();
            function.WriteLog(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"), "重置商户交易额日志");
            WebCMSEntities db = new WebCMSEntities();
            MySystem.KxsMainModels.WebCMSEntities kxsdb = new MySystem.KxsMainModels.WebCMSEntities();
            var merId = int.Parse(merchantId);
            var merchant = db.MerchantInfo.FirstOrDefault(m => m.Id == merId) ?? new MerchantInfo();
            var user = kxsdb.Users.FirstOrDefault(m => m.Id == merchant.UserId) ?? new KxsMainModels.Users();
            var ParentNavs = user.ParentNav + "," + user.Id;
            string[] ParentNavList = ParentNavs.Trim(',').Replace(",,", ",").Split(',');
            var ParentNav = ParentNavs.Trim(',').Replace(",,", ",").TrimEnd(',');
            try
            {
                for (DateTime start = DateTime.Parse(startDate.Substring(0, 4) + "-" + startDate.Substring(4, 2) + "-" + startDate.Substring(6, 2)); start <= DateTime.Parse(endDate.Substring(0, 4) + "-" + endDate.Substring(4, 2) + "-" + endDate.Substring(6, 2)); start = start.AddDays(1))
                {
                    var date = start.ToString("yyyy-MM-dd");
                    var smonth = start.ToString("yyyy-MM");
                    var emonth = start.AddMonths(1).ToString("yyyy-MM");
                    CustomerSqlConn.op("delete from QrCodePlateStatServer.MerchantTradeSummary" + date.Replace("-", "") + " where  MerchantId=" + merchantId + " and TradeDate='" + date.Replace("-", "") + "'", AppConfig.Base.StatSqlConn);
                    CustomerSqlConn.op("INSERT INTO QrCodePlateStatServer.MerchantTradeSummary" + date.Replace("-", "") + " (MerchantId,TradeDate,TradeMonth,TradeAmount,InFactAmount,OderCount,WeChatInfactAmount,AliPayInFactAmount,ActAmount,NonActAmount) select MerchantId,DATE_FORMAT(CreateDate,'%Y%m%d') Date ,DATE_FORMAT(CreateDate,'%Y%m') Month,sum(PayMoney) TradeAmount,sum(MerchantActualAmount) InFactAmount,count(Id) Count,sum(if(PayMode=2,MerchantActualAmount,0)) WeChatInfactAmount,sum(if(PayMode=1,MerchantActualAmount,0)) AliPayInFactAmount,sum(if(IsAct=1,PayMoney,0)) ActAmount,sum(if(IsAct=0,PayMoney,0)) NonActAmount from ConsumerOrders where Status>0 AND MerchantId=" + merchantId + " AND CreateDate>='" + date + " 00:00:00' and CreateDate<='" + date + " 23:59:59' group by MerchantId,DATE_FORMAT(CreateDate,'%Y%m%d'),DATE_FORMAT(CreateDate,'%Y%m')", AppConfig.Base.SqlConn);

                    CustomerSqlConn.op("delete from MerchantAmountSummay where  MerchantId=" + merchantId + " and TradeDate='" + date.Replace("-", "") + "'", AppConfig.Base.SqlConn);
                    CustomerSqlConn.op("INSERT INTO MerchantAmountSummay (PayMode,TotalActual,TradeAmount,TradeDate,TradeMonth,MerchantId,TradeCount,IsAct) select PayMode,sum(MerchantActualAmount) TotalActual,sum(PayMoney) TradeAmount,DATE_FORMAT(CreateDate,'%Y%m%d') TradeDate,DATE_FORMAT(CreateDate,'%Y%m') TradeMonth,MerchantId,count(Id) TradeCount,IsAct from ConsumerOrders where Status>0 AND MerchantId=" + merchantId + " AND CreateDate>='" + date + " 00:00:00' and CreateDate<='" + date + " 23:59:59' group by MerchantId,PayMode,IsAct,DATE_FORMAT(CreateDate,'%Y%m%d'),DATE_FORMAT(CreateDate,'%Y%m')", AppConfig.Base.SqlConn);

                    CustomerSqlConn.op("delete from UserAmountSummary where  UserId in(" + ParentNav + ") and TradeDate='" + date.Replace("-", "") + "'", AppConfig.Base.SqlConn);
                    CustomerSqlConn.op("INSERT INTO UserAmountSummary (SeoTitle,PayMode,TotalAmount,TradeMonth,TradeDate,UserId,TradeCount,IsAct) select 'self' SeoTitle,PayMode,SUM(PayMoney) TotalAmount,DATE_FORMAT(CreateDate,'%Y%m') TradeMonth,DATE_FORMAT(CreateDate,'%Y%m%d') TradeDate, " + merchant.UserId + " UserId,count(Id) TradeCount,IsAct from ConsumerOrders where Status>0 AND MerchantId=" + merchantId + " AND CreateDate>='" + date + " 00:00:00' and CreateDate<='" + date + " 23:59:59' group by PayMode,IsAct,DATE_FORMAT(CreateDate,'%Y%m%d'),DATE_FORMAT(CreateDate,'%Y%m')", AppConfig.Base.SqlConn);
                    for (int a = 0; a < ParentNavList.Length; a++)
                    {
                        var UserId = ParentNavList[a];
                        CustomerSqlConn.op("INSERT INTO UserAmountSummary (SeoTitle,PayMode,TotalAmount,TradeMonth,TradeDate,UserId,TradeCount,IsAct) select 'team' SeoTitle,PayMode,SUM(PayMoney) TotalAmount,DATE_FORMAT(CreateDate,'%Y%m') TradeMonth,DATE_FORMAT(CreateDate,'%Y%m%d') TradeDate, " + UserId + " UserId,count(Id) TradeCount,IsAct from ConsumerOrders where Status>0 AND MerchantId=" + merchantId + " AND CreateDate>='" + date + " 00:00:00' and CreateDate<='" + date + " 23:59:59' group by PayMode,IsAct,DATE_FORMAT(CreateDate,'%Y%m%d'),DATE_FORMAT(CreateDate,'%Y%m')", AppConfig.Base.SqlConn);
                    }

                    CustomerSqlConn.op("delete from QrCodePlateStatServer.MerchantTradeSummary" + date.Replace("-", "").Substring(0, 6) + " where  MerchantId=" + merchantId + "", AppConfig.Base.StatSqlConn);
                    CustomerSqlConn.op("INSERT INTO QrCodePlateStatServer.MerchantTradeSummary" + date.Replace("-", "").Substring(0, 6) + " (MerchantId,TradeAmount,InFactAmount,OderCount,WeChatInfactAmount,AliPayInFactAmount,ActAmount,NonActAmount,TradeMonth) select MerchantId,sum(PayMoney) TradeAmount,sum(MerchantActualAmount) InFactAmount,count(Id) Count,sum(if(PayMode=2,MerchantActualAmount,0)) WeChatInfactAmount,sum(if(PayMode=1,MerchantActualAmount,0)) AliPayInFactAmount,sum(if(IsAct=1,PayMoney,0)) ActAmount,sum(if(IsAct=0,PayMoney,0)) NonActAmount,DATE_FORMAT(CreateDate,'%Y%m') TradeMonth from ConsumerOrders where Status>0 AND MerchantId=" + merchantId + " AND CreateDate>='" + smonth + "-01" + " 00:00:00' and CreateDate<'" + emonth + "-01" + " 00:00:00'   group by MerchantId,DATE_FORMAT(CreateDate,'%Y%m')", AppConfig.Base.SqlConn);

                    CustomerSqlConn.op("delete from QrCodePlateStatServer.MerchantTradeSummaryList" + date.Replace("-", "").Substring(0, 6) + " where  MerchantId=" + merchantId + " and TradeDate='" + date.Replace("-", "") + "'", AppConfig.Base.StatSqlConn);
                    CustomerSqlConn.op("INSERT INTO QrCodePlateStatServer.MerchantTradeSummaryList" + date.Replace("-", "").Substring(0, 6) + " (MerchantId,TradeDate,TradeMonth,TradeAmount,InFactAmount,OderCount,WeChatInfactAmount,AliPayInFactAmount,ActAmount,NonActAmount) select MerchantId,DATE_FORMAT(CreateDate,'%Y%m%d') Date ,DATE_FORMAT(CreateDate,'%Y%m') Month,sum(PayMoney) TradeAmount,sum(MerchantActualAmount) InFactAmount,count(Id) Count,sum(if(PayMode=2,MerchantActualAmount,0)) WeChatInfactAmount,sum(if(PayMode=1,MerchantActualAmount,0)) AliPayInFactAmount,sum(if(IsAct=1,PayMoney,0)) ActAmount,sum(if(IsAct=0,PayMoney,0)) NonActAmount from ConsumerOrders where Status>0 AND MerchantId=" + merchantId + " AND CreateDate>='" + date + " 00:00:00' and CreateDate<='" + date + " 23:59:59' group by MerchantId,DATE_FORMAT(CreateDate,'%Y%m%d'),DATE_FORMAT(CreateDate,'%Y%m')", AppConfig.Base.SqlConn);
                }
            }
            catch (Exception ex)
            {
                function.WriteLog(DateTime.Now.ToString() + "\n" + ex.ToString(), "重置商户交易额异常");
            }
            function.WriteLog(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff") + "\n\n", "重置商户交易额日志");
        }

        public void StartResetSnDo()
        {
            while (true)
            {
                string content = RedisDbconn.Instance.RPop<string>("ResetSnStatDataQueue");
                if (!string.IsNullOrEmpty(content))
                {
                    StatResetSnAmount(content);
                }
                Thread.Sleep(10000);
            }
        }
        public void StatResetSnAmount(string content)
        {
            JsonData jsonObj = JsonMapper.ToObject(content);
            string snNo = jsonObj["SnNo"].ToString();
            string startDate = jsonObj["StartDate"].ToString();
            string endDate = jsonObj["EndDate"].ToString();
            function.WriteLog(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"), "重置码牌交易额日志");
            WebCMSEntities db = new WebCMSEntities();
            KxsMainModels.WebCMSEntities maindb = new KxsMainModels.WebCMSEntities();
            using (var tran = db.Database.BeginTransaction())
            {
                try
                {
                    DataTable selfDt = CustomerSqlConn.dtable("select SnNo,PayMode,IsAct,DATE_FORMAT(CreateDate,'%Y%m%d'),sum(PayMoney),count(Id),sum(MerchantActualAmount) from ConsumerOrders where SnNo=" + snNo + " and CreateDate>='" + startDate.Substring(0, 4) + "-" + startDate.Substring(4, 2) + "-" + startDate.Substring(6, 2) + " 00:00:00' and CreateDate<='" + endDate.Substring(0, 4) + "-" + endDate.Substring(4, 2) + "-" + endDate.Substring(6, 2) + " 23:59:59' group by SnNo,PayMode,IsAct,DATE_FORMAT(CreateDate,'%Y%m%d')", AppConfig.Base.SqlConn);
                    if (selfDt.Rows.Count > 0)
                    {
                        function.WriteLog("统计人数:" + selfDt.Rows.Count + "\n\n", "重置码牌交易额日志");
                        foreach (DataRow selfDr in selfDt.Rows)
                        {
                            int PayMode = int.Parse(selfDr["PayMode"].ToString());
                            int IsAct = int.Parse(selfDr["IsAct"].ToString());
                            string SnNo = selfDr["SnNo"].ToString();
                            string TradeDate = selfDr[3].ToString();
                            decimal TradeAmount = decimal.Parse(selfDr[4].ToString());
                            int TradeCount = int.Parse(selfDr[5].ToString());
                            decimal ActualAmount = decimal.Parse(selfDr[6].ToString());
                            string TradeMonth = TradeDate.Substring(0, 6);

                            //码牌交易额
                            QrCodeAmountSummay qrcode = db.QrCodeAmountSummay.FirstOrDefault(m => m.SnNo == SnNo && m.IsAct == IsAct && m.TradeMonth == TradeMonth && m.TradeDate == TradeDate && m.PayMode == PayMode);
                            if (qrcode == null)
                            {
                                qrcode = db.QrCodeAmountSummay.Add(new QrCodeAmountSummay()
                                {
                                    SnNo = SnNo,
                                    TradeMonth = TradeMonth,
                                    TradeDate = TradeDate,
                                    PayMode = PayMode,
                                    IsAct = IsAct,
                                }).Entity;
                                db.SaveChanges();
                            }
                            qrcode.TradeAmount += TradeAmount;
                            qrcode.TradeCount += TradeCount;
                            qrcode.TotalActual += ActualAmount;
                            db.SaveChanges();
                        }
                    }
                    tran.Commit();
                }
                catch (Exception ex)
                {
                    tran.Rollback();
                    function.WriteLog(DateTime.Now.ToString() + "\n" + ex.ToString(), "重置码牌交易额异常");
                }
            }
            db.Dispose();
            maindb.Dispose();
            function.WriteLog(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff") + "\n\n", "重置码牌交易额日志");
        }

        public void StartResetUserSelfDo()
        {
            while (true)
            {
                string content = RedisDbconn.Instance.RPop<string>("ResetUserSelfStatDataQueue");
                if (!string.IsNullOrEmpty(content))
                {
                    StatResetUserSelfAmount(content);
                }
                Thread.Sleep(10000);
            }
        }
        public void StatResetUserSelfAmount(string content)
        {
            JsonData jsonObj = JsonMapper.ToObject(content);
            string userId = jsonObj["UserId"].ToString();
            string startDate = jsonObj["StartDate"].ToString();
            string endDate = jsonObj["EndDate"].ToString();
            function.WriteLog(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"), "重置创客个人交易额日志");
            WebCMSEntities db = new WebCMSEntities();
            KxsMainModels.WebCMSEntities maindb = new KxsMainModels.WebCMSEntities();
            using (var tran = db.Database.BeginTransaction())
            {
                try
                {
                    DataTable selfDt = CustomerSqlConn.dtable("select UserId,PayMode,IsAct,DATE_FORMAT(CreateDate,'%Y%m%d'),sum(PayMoney),count(Id) from ConsumerOrders where UserId=" + userId + " and CreateDate>='" + startDate.Substring(0, 4) + "-" + startDate.Substring(4, 2) + "-" + startDate.Substring(6, 2) + " 00:00:00' and CreateDate<='" + endDate.Substring(0, 4) + "-" + endDate.Substring(4, 2) + "-" + endDate.Substring(6, 2) + " 23:59:59' group by UserId,PayMode,IsAct,DATE_FORMAT(CreateDate,'%Y%m%d')", AppConfig.Base.SqlConn);
                    if (selfDt.Rows.Count > 0)
                    {
                        function.WriteLog("统计人数:" + selfDt.Rows.Count + "\n\n", "重置创客个人交易额日志");
                        foreach (DataRow selfDr in selfDt.Rows)
                        {
                            int UserId = int.Parse(selfDr["UserId"].ToString());
                            KxsMainModels.Users user = maindb.Users.FirstOrDefault(m => m.Id == UserId) ?? new KxsMainModels.Users();
                            int PayMode = int.Parse(selfDr["PayMode"].ToString());
                            int IsAct = int.Parse(selfDr["IsAct"].ToString());
                            string TradeDate = selfDr[3].ToString();
                            decimal TradeAmount = decimal.Parse(selfDr[4].ToString());
                            int TradeCount = int.Parse(selfDr[5].ToString());
                            string TradeMonth = TradeDate.Substring(0, 6);

                            //创客交易额
                            UserAmountSummary selfStat = db.UserAmountSummary.FirstOrDefault(m => m.UserId == UserId && m.IsAct == IsAct && m.TradeMonth == TradeMonth && m.TradeDate == TradeDate && m.PayMode == PayMode && m.SeoTitle == "self");
                            if (selfStat == null)
                            {
                                selfStat = db.UserAmountSummary.Add(new UserAmountSummary()
                                {
                                    UserId = UserId,
                                    TradeMonth = TradeMonth,
                                    TradeDate = TradeDate,
                                    PayMode = PayMode,
                                    IsAct = IsAct,
                                    SeoTitle = "self",
                                }).Entity;
                                db.SaveChanges();
                            }
                            selfStat.TotalAmount = TradeAmount;
                            selfStat.TradeCount = TradeCount;
                            db.SaveChanges();

                            string ParentNav = user.ParentNav + "," + userId + ",";
                            if (!string.IsNullOrEmpty(ParentNav))
                            {
                                string[] ParentNavList = ParentNav.Replace(",,", ",").Trim(',').Split(',');
                                foreach (string id in ParentNavList)
                                {
                                    string reqdata = "{\"UserId\":\"" + id + "\",\"StartDate\":\"" + startDate + "\",\"EndDate\":\"" + endDate + "\"}";
                                    RedisDbconn.Instance.AddList("ResetUserTeamStatDataQueue", reqdata);
                                }
                            }
                        }
                    }
                    tran.Commit();
                }
                catch (Exception ex)
                {
                    tran.Rollback();
                    function.WriteLog(DateTime.Now.ToString() + "\n" + ex.ToString(), "重置创客个人交易额异常");
                }
            }
            db.Dispose();
            maindb.Dispose();
            function.WriteLog(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff") + "\n\n", "重置创客个人交易额日志");
        }

        public void StartResetUserTeamDo()
        {
            while (true)
            {
                string content = RedisDbconn.Instance.RPop<string>("ResetUserTeamStatDataQueue");
                if (!string.IsNullOrEmpty(content))
                {
                    StatResetUserTeamAmount(content);
                }
                Thread.Sleep(10000);
            }
        }
        public void StatResetUserTeamAmount(string content)
        {
            JsonData jsonObj = JsonMapper.ToObject(content);
            int userId = int.Parse(jsonObj["UserId"].ToString());
            string startDate = jsonObj["StartDate"].ToString();
            string endDate = jsonObj["EndDate"].ToString();
            function.WriteLog(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"), "重置团队创客交易额日志");
            WebCMSEntities db = new WebCMSEntities();
            KxsMainModels.WebCMSEntities maindb = new KxsMainModels.WebCMSEntities();
            using (var tran = db.Database.BeginTransaction())
            {
                try
                {
                    string subUserId = "0";
                    var subusers = maindb.Users.Select(m => new { m.Id, m.ParentUserId }).Where(m => m.ParentUserId == userId).ToList();
                    foreach (var subuser in subusers)
                    {
                        subUserId += "," + subuser.Id;
                    }
                    DataTable selfDt = CustomerSqlConn.dtable("select PayMode,TotalAmount,TradeMonth,TradeDate,UserId,TradeCount,IsAct from UserAmountSummary where UserId in (" + subUserId + ") and TradeDate>='" + startDate + "' and TradeDate<='" + endDate + "' and SeoTitle='team'", AppConfig.Base.SqlConn);
                    if (selfDt.Rows.Count > 0)
                    {
                        function.WriteLog("统计人数:" + selfDt.Rows.Count + "\n\n", "重置团队创客交易额日志");
                        foreach (DataRow selfDr in selfDt.Rows)
                        {
                            int UserId = int.Parse(selfDr["UserId"].ToString());
                            int PayMode = int.Parse(selfDr["PayMode"].ToString());
                            int IsAct = int.Parse(selfDr["IsAct"].ToString());
                            string TradeDate = selfDr["TradeDate"].ToString();
                            decimal TradeAmount = decimal.Parse(selfDr["TotalAmount"].ToString());
                            int TradeCount = int.Parse(selfDr["TradeCount"].ToString());
                            string TradeMonth = selfDr["TradeMonth"].ToString();

                            //创客交易额
                            UserAmountSummary teamStat = db.UserAmountSummary.FirstOrDefault(m => m.UserId == UserId && m.IsAct == IsAct && m.TradeMonth == TradeMonth && m.TradeDate == TradeDate && m.PayMode == PayMode && m.SeoTitle == "Team");
                            if (teamStat == null)
                            {
                                teamStat = db.UserAmountSummary.Add(new UserAmountSummary()
                                {
                                    UserId = UserId,
                                    TradeMonth = TradeMonth,
                                    TradeDate = TradeDate,
                                    PayMode = PayMode,
                                    IsAct = IsAct,
                                    SeoTitle = "self",
                                }).Entity;
                                db.SaveChanges();
                            }
                            teamStat.TotalAmount = TradeAmount;
                            teamStat.TradeCount = TradeCount;
                            db.SaveChanges();
                        }
                    }
                    tran.Commit();
                }
                catch (Exception ex)
                {
                    tran.Rollback();
                    function.WriteLog(DateTime.Now.ToString() + "\n" + ex.ToString(), "重置团队创客交易额异常");
                }
            }
            db.Dispose();
            maindb.Dispose();
            function.WriteLog(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff") + "\n\n", "重置团队创客交易额日志");
        }
    }
}