kxs-stat/AppStart/Helper/SplitTableData/StatNewService.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 StatNewService
    {
        public readonly static StatNewService Instance = new StatNewService();
        private StatNewService()
        { }



        public void CreateTable()
        {
            Thread th = new Thread(CreateTableDo);
            th.IsBackground = true;
            th.Start();
        }
        public void CreateTableDo()
        {
            while (true)
            {
                string TradeDate = DateTime.Now.AddDays(1).ToString("yyyyMMdd");
                string TradeMonth = DateTime.Now.AddMonths(1).ToString("yyyyMM");

                CreateTableOp(TradeDate, 1, "TradeDaySummary");
                CreateTableOp(TradeDate, 1, "PosMerchantTradeSummay");
                CreateTableOp(TradeDate, 1, "UserTradeMonthSummary");

                if(DateTime.Now.Day > 25)
                {
                    CreateTableOp(TradeMonth, 2, "TradeDaySummary");
                    CreateTableOp(TradeMonth, 2, "PosMerchantTradeSummay");
                    CreateTableOp(TradeMonth, 2, "UserTradeMonthSummary");
                    CreateTableOp(TradeMonth, 2, "TradeRecord");
                    CreateTableOp(TradeMonth, 2, "UserAccountRecord");
                    CreateTableOp(TradeMonth, 2, "SpBindRecord");
                    CreateTableOp(TradeMonth, 2, "SpMerchants");
                    CreateTableOp(TradeMonth, 2, "SpActivateRecord");
                    CreateTableOp(TradeMonth, 2, "SpTradeRecord");
                }

                int timespan = 1000 * 60 * 60;
                Thread.Sleep(timespan);
            }
        }
        private void CreateTableOp(string TradeDate, int Kind, string TableName)
        {
            if(string.IsNullOrEmpty(RedisDbconn.Instance.Get<string>(TableName + TradeDate)))
            {
                string sql = "";
                if(TableName == "TradeDaySummary") sql = AppConfig.Base.CreateTradeDaySummary.Replace("#DateTime#", TradeDate);
                if(TableName == "TradeRecord") sql = AppConfig.Base.CreateTradeRecord.Replace("#DateTime#", TradeDate);
                if(TableName == "PosMerchantTradeSummay") sql = AppConfig.Base.CreatePosMerchantTradeSummay.Replace("#DateTime#", TradeDate);
                if(TableName == "UserAccountRecord") sql = AppConfig.Base.CreateUserAccountRecord.Replace("#DateTime#", TradeDate);
                if(TableName == "UserTradeMonthSummary") sql = AppConfig.Base.CreateUserTradeMonthSummary.Replace("#DateTime#", TradeDate);
                if(TableName == "SpBindRecord") sql = AppConfig.Base.CreateSpBindRecord.Replace("#DateTime#", TradeDate);
                if(TableName == "SpMerchants") sql = AppConfig.Base.CreateSpMerchants.Replace("#DateTime#", TradeDate);
                if(TableName == "SpActivateRecord") sql = AppConfig.Base.CreateSpActivateRecord.Replace("#DateTime#", TradeDate);
                if(TableName == "SpTradeRecord") sql = AppConfig.Base.CreateSpTradeRecord.Replace("#DateTime#", TradeDate);
                CustomerSqlConn.op(sql, AppConfig.Base.StatSqlConn);
                RedisDbconn.Instance.Set(TableName + TradeDate, "1");
                int sec = 3600 * 25;
                if(Kind == 2)
                {
                    sec = 3600 * 24 * 35;
                }
                RedisDbconn.Instance.SetExpire(TableName + TradeDate, sec);
            }
        }



        // 统计交易额到RDS
        public void StartStat()
        {
            Thread th = new Thread(StartStatDo);
            th.IsBackground = true;
            th.Start();
        }
        public void StartStatDo()
        {
            while (true)
            {
                string content = RedisDbconn.Instance.RPop<string>("StatTradeAmountQueue");
                if(!string.IsNullOrEmpty(content))
                {
                    StatTradeAmount(content, 1);
                    StatTradeAmount(content, 2);
                }
                else
                {
                    Thread.Sleep(10000);
                }
            }
        }
        public void StatTradeAmount(string content, int Kind)
        {
            WebCMSEntities db = new WebCMSEntities();
            try
            {
                JsonData selfDr = JsonMapper.ToObject(content);
                int UserId = int.Parse(selfDr["UserId"].ToString());
                Users user = db.Users.FirstOrDefault(m => m.Id == UserId) ?? new Users();
                string ParentNav = user.ParentNav;
                int BrandId = int.Parse(selfDr["BrandId"].ToString());
                int BankCardType = int.Parse(selfDr["BankCardType"].ToString());
                int QrPayFlag = int.Parse(selfDr["QrPayFlag"].ToString());
                int MerHelpFlag = int.Parse(selfDr["MerHelpFlag"].ToString());
                int Version = int.Parse(selfDr["Version"].ToString());
                int CapFlag = int.Parse(selfDr["CapFlag"].ToString());
                int VipFlag = int.Parse(selfDr["VipFlag"].ToString());
                int PayType = int.Parse(selfDr["PayType"].ToString());
                string TradeDate = selfDr["TradeDate"].ToString();
                decimal TradeAmount = decimal.Parse(selfDr["TradeAmount"].ToString());
                int TradeCount = int.Parse(selfDr["TradeCount"].ToString());
                if(Kind == 2)
                {
                    TradeDate = TradeDate.Substring(0, 6);
                }
                if(string.IsNullOrEmpty(RedisDbconn.Instance.Get<string>("TradeDaySummary" + TradeDate)))
                {
                    CustomerSqlConn.op(AppConfig.Base.CreateTradeDaySummary.Replace("#DateTime#", TradeDate), AppConfig.Base.StatSqlConn);
                    RedisDbconn.Instance.Set("TradeDaySummary" + TradeDate, "1");
                    int sec = 3600 * 25;
                    if(Kind == 2)
                    {
                        sec = 3600 * 24 * 35;
                    }
                    RedisDbconn.Instance.SetExpire("TradeDaySummary" + TradeDate, sec);
                    Thread.Sleep(2000);
                }

                string Id = "0";
                DataTable check = CustomerSqlConn.dtable("select Id from TradeDaySummary" + TradeDate + " where UserId=" + UserId + " and BrandId=" + BrandId + " and QueryCount=" + QrPayFlag + " and VipFlag=" + VipFlag + " and PayType=" + PayType + " and SeoTitle='self'", AppConfig.Base.StatSqlConn);
                if(check.Rows.Count < 1)
                {
                    check = CustomerSqlConn.dtable("insert into TradeDaySummary" + TradeDate + " (UserId,BrandId,QueryCount,VipFlag,PayType,SeoTitle) values (" + UserId + "," + BrandId + "," + QrPayFlag + "," + VipFlag + "," + PayType + ",'self');select @@IDENTITY", AppConfig.Base.StatSqlConn);
                    if(check.Rows.Count > 0)
                    {
                        Id = check.Rows[0][0].ToString();
                    }
                }
                else
                {
                    Id = check.Rows[0][0].ToString();
                }
                string selfStat = "";
                if (BankCardType == 0)
                {
                    if (Version == 1)
                    {
                        selfStat += "ProfitDirectDebitTradeAmt = ProfitDirectDebitTradeAmt+" + TradeAmount + ",";
                        if (CapFlag == 1)
                        {
                            selfStat += "ProfitDirectDebitCapTradeAmt = ProfitDirectDebitCapTradeAmt+" + TradeAmount + ",";
                            selfStat += "ProfitDirectDebitCapNum = ProfitDirectDebitCapNum+" + TradeCount + ",";
                        }
                    }
                    else if (MerHelpFlag == 1)
                    {
                        selfStat += "HelpDirectDebitTradeAmt = HelpDirectDebitTradeAmt+" + TradeAmount + ",";
                        if (CapFlag == 1)
                        {
                            selfStat += "HelpDirectDebitCapTradeAmt = HelpDirectDebitCapTradeAmt+" + TradeAmount + ",";
                            selfStat += "HelpDirectDebitCapNum = HelpDirectDebitCapNum+" + TradeCount + ",";
                        }
                    }
                    else
                    {
                        selfStat += "NotHelpDirectDebitTradeAmt = NotHelpDirectDebitTradeAmt+" + TradeAmount + ",";
                        if (CapFlag == 1)
                        {
                            selfStat += "NotHelpDirectDebitCapTradeAmt = NotHelpDirectDebitCapTradeAmt+" + TradeAmount + ",";
                            selfStat += "NotHelpDirectDebitCapNum = NotHelpDirectDebitCapNum+" + TradeCount + ",";
                        }
                    }
                }
                else if (BankCardType != 0)
                {
                    if (Version == 1)
                    {
                        selfStat += "ProfitDirectTradeAmt = ProfitDirectTradeAmt+" + TradeAmount + ",";
                    }
                    else if (MerHelpFlag == 1)
                    {
                        selfStat += "HelpDirectTradeAmt = HelpDirectTradeAmt+" + TradeAmount + ",";
                    }
                    else
                    {
                        selfStat += "NotHelpDirectTradeAmt = NotHelpDirectTradeAmt+" + TradeAmount + ",";
                    }
                }
                CustomerSqlConn.op("update TradeDaySummary" + TradeDate + " set " + selfStat.TrimEnd(',') + " where Id=" + Id, AppConfig.Base.StatSqlConn);
                ParentNav += "," + UserId + ",";
                if (!string.IsNullOrEmpty(ParentNav))
                {
                    string[] ParentNavList = ParentNav.Trim(',').Replace(",,", ",").Split(',');
                    foreach (string NavUserIdString in ParentNavList)
                    {
                        int NavUserId = int.Parse(NavUserIdString);

                        string PId = "0";
                        DataTable pcheck = CustomerSqlConn.dtable("select Id from TradeDaySummary" + TradeDate + " where UserId=" + NavUserId + " and BrandId=" + BrandId + " and QueryCount=" + QrPayFlag + " and VipFlag=" + VipFlag + " and PayType=" + PayType + " and SeoTitle='team'", AppConfig.Base.StatSqlConn);
                        if(pcheck.Rows.Count < 1)
                        {
                            pcheck = CustomerSqlConn.dtable("insert into TradeDaySummary" + TradeDate + " (UserId,BrandId,QueryCount,VipFlag,PayType,SeoTitle) values (" + NavUserId + "," + BrandId + "," + QrPayFlag + "," + VipFlag + "," + PayType + ",'team');select @@IDENTITY", AppConfig.Base.StatSqlConn);
                            if(pcheck.Rows.Count > 0)
                            {
                                PId = pcheck.Rows[0][0].ToString();
                            }
                        }
                        else
                        {
                            PId = pcheck.Rows[0][0].ToString();
                        }

                        string teamStat = "";
                        if (BankCardType == 0)
                        {
                            if (Version == 1)
                            {
                                teamStat += "ProfitNonDirectDebitTradeAmt = ProfitNonDirectDebitTradeAmt+" + TradeAmount + ",";
                                if (CapFlag == 1)
                                {
                                    teamStat += "ProfitDirectDebitCapTradeAmt = ProfitDirectDebitCapTradeAmt+" + TradeAmount + ",";
                                    teamStat += "ProfitDirectDebitCapNum = ProfitDirectDebitCapNum+" + TradeCount + ",";
                                }
                            }
                            else if (MerHelpFlag == 1)
                            {
                                teamStat += "HelpNonDirectDebitTradeAmt = HelpNonDirectDebitTradeAmt+" + TradeAmount + ",";
                                if (CapFlag == 1)
                                {
                                    teamStat += "HelpDirectDebitCapTradeAmt = HelpDirectDebitCapTradeAmt+" + TradeAmount + ",";
                                    teamStat += "HelpDirectDebitCapNum = HelpDirectDebitCapNum+" + TradeCount + ",";
                                }
                            }
                            else
                            {
                                teamStat += "NotHelpNonDirectDebitTradeAmt = NotHelpNonDirectDebitTradeAmt+" + TradeAmount + ",";
                                if (CapFlag == 1)
                                {
                                    teamStat += "NotHelpDirectDebitCapTradeAmt = NotHelpDirectDebitCapTradeAmt+" + TradeAmount + ",";
                                    teamStat += "NotHelpDirectDebitCapNum = NotHelpDirectDebitCapNum+" + TradeCount + ",";
                                }
                            }
                        }
                        else if (BankCardType != 0)
                        {
                            if (Version == 1)
                            {
                                teamStat += "ProfitNonDirectTradeAmt = ProfitNonDirectTradeAmt+" + TradeAmount + ",";
                            }
                            else if (MerHelpFlag == 1)
                            {
                                teamStat += "HelpNonDirectTradeAmt = HelpNonDirectTradeAmt+" + TradeAmount + ",";
                            }
                            else
                            {
                                teamStat += "NotHelpNonDirectTradeAmt = NotHelpNonDirectTradeAmt+" + TradeAmount + ",";
                            }
                        }
                        CustomerSqlConn.op("update TradeDaySummary" + TradeDate + " set " + teamStat.TrimEnd(',') + " where Id=" + PId, AppConfig.Base.StatSqlConn);
                    }
                }
                db.SaveChanges();
            }
            catch (Exception ex)
            {
                function.WriteLog(DateTime.Now.ToString() + "\n" + ex.ToString(), "实时统计交易额日志RDS异常");
            }
            db.Dispose();
            function.WriteLog(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff") + "\n\n", "实时统计交易额日志RDS");
        }




        // 统计商户交易额到RDS
        public void StartMer()
        {
            Thread th = new Thread(StartMerDo);
            th.IsBackground = true;
            th.Start();
        }
        public void StartMerDo()
        {
            while (true)
            {
                string content = RedisDbconn.Instance.RPop<string>("StatMerTradeAmountQueue");
                if(!string.IsNullOrEmpty(content))
                {
                    StatMerTradeAmount(content, 1);
                    StatMerTradeAmount(content, 2);
                }
                else
                {
                    Thread.Sleep(10000);
                }
            }
        }
        public void StatMerTradeAmount(string content, int Kind)
        {
            JsonData selfDr = JsonMapper.ToObject(content);
            string TradeDate = selfDr["TradeDate"].ToString();
            decimal TradeAmount = decimal.Parse(selfDr["TradeAmount"].ToString());
            int MerchantId = int.Parse(selfDr["MerchantId"].ToString());
            int BrandId = int.Parse(selfDr["BrandId"].ToString());
            if(Kind == 2)
            {
                TradeDate = TradeDate.Substring(0, 6);
            }
            string Id = "0";
            DataTable check = CustomerSqlConn.dtable("select Id from PosMerchantTradeSummay" + TradeDate + " where MerchantId=" + MerchantId + " and BrandId=" + BrandId + "", AppConfig.Base.StatSqlConn);
            if(check.Rows.Count < 1)
            {
                check = CustomerSqlConn.dtable("insert into PosMerchantTradeSummay" + TradeDate + " (MerchantId,BrandId) values (" + MerchantId + "," + BrandId + ");select @@IDENTITY", AppConfig.Base.StatSqlConn);
                if(check.Rows.Count > 0)
                {
                    Id = check.Rows[0][0].ToString();
                }
            }
            else
            {
                Id = check.Rows[0][0].ToString();
            }
            CustomerSqlConn.op("update PosMerchantTradeSummay" + TradeDate + " set TradeAmount=TradeAmount+" + TradeAmount + " where Id=" + Id, AppConfig.Base.StatSqlConn);
        }




        // 统计激活数到RDS
        public void StartAct()
        {
            Thread th = new Thread(StartActDo);
            th.IsBackground = true;
            th.Start();
        }
        public void StartActDo()
        {
            while (true)
            {
                string content = RedisDbconn.Instance.RPop<string>("StatActQueue");
                if(!string.IsNullOrEmpty(content))
                {
                    StatAct(content, 1);
                    StatAct(content, 2);
                }
                else
                {
                    Thread.Sleep(10000);
                }
            }
        }
        public void StatAct(string content, int Kind)
        {
            WebCMSEntities db = new WebCMSEntities();
            try
            {
                JsonData selfDr = JsonMapper.ToObject(content);
                int UserId = int.Parse(selfDr["UserId"].ToString());
                Users user = db.Users.FirstOrDefault(m => m.Id == UserId) ?? new Users();
                string ParentNav = user.ParentNav;
                int BrandId = int.Parse(selfDr["BrandId"].ToString());
                string TradeDate = selfDr["TradeDate"].ToString();
                decimal TradeAmount = decimal.Parse(selfDr["TradeAmount"].ToString());
                int TradeCount = int.Parse(selfDr["TradeCount"].ToString());
                if(Kind == 2)
                {
                    TradeDate = TradeDate.Substring(0, 6);
                }

                string Id = "0";
                DataTable check = CustomerSqlConn.dtable("select Id from UserTradeMonthSummary" + TradeDate + " where UserId=" + UserId + " and BrandId=" + BrandId + " and SeoTitle='self'", AppConfig.Base.StatSqlConn);
                if(check.Rows.Count < 1)
                {
                    check = CustomerSqlConn.dtable("insert into UserTradeMonthSummary" + TradeDate + " (UserId,BrandId,SeoTitle) values (" + UserId + "," + BrandId + ",'self');select @@IDENTITY", AppConfig.Base.StatSqlConn);
                    if(check.Rows.Count > 0)
                    {
                        Id = check.Rows[0][0].ToString();
                    }
                }
                else
                {
                    Id = check.Rows[0][0].ToString();
                }
                CustomerSqlConn.op("update UserTradeMonthSummary" + TradeDate + " set ActiveBuddyMerStatus=ActiveBuddyMerStatus+1 where Id=" + Id, AppConfig.Base.StatSqlConn);
                ParentNav += "," + UserId + ",";
                if (!string.IsNullOrEmpty(ParentNav))
                {
                    string[] ParentNavList = ParentNav.Trim(',').Replace(",,", ",").Split(',');
                    foreach (string NavUserIdString in ParentNavList)
                    {
                        int NavUserId = int.Parse(NavUserIdString);

                        string PId = "0";
                        DataTable pcheck = CustomerSqlConn.dtable("select Id from UserTradeMonthSummary" + TradeDate + " where UserId=" + NavUserId + " and BrandId=" + BrandId + " and SeoTitle='team'", AppConfig.Base.StatSqlConn);
                        if(pcheck.Rows.Count < 1)
                        {
                            pcheck = CustomerSqlConn.dtable("insert into UserTradeMonthSummary" + TradeDate + " (UserId,BrandId,SeoTitle) values (" + NavUserId + "," + BrandId + ",'team');select @@IDENTITY", AppConfig.Base.StatSqlConn);
                            if(pcheck.Rows.Count > 0)
                            {
                                PId = pcheck.Rows[0][0].ToString();
                            }
                        }
                        else
                        {
                            PId = pcheck.Rows[0][0].ToString();
                        }

                        CustomerSqlConn.op("update UserTradeMonthSummary" + TradeDate + " set ActiveBuddyMerStatus=ActiveBuddyMerStatus+1 where Id=" + PId, AppConfig.Base.StatSqlConn);
                    }
                }
                db.SaveChanges();
            }
            catch (Exception ex)
            {
                function.WriteLog(DateTime.Now.ToString() + "\n" + ex.ToString(), "实时统计交易额日志RDS异常");
            }
            db.Dispose();
            function.WriteLog(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff") + "\n\n", "实时统计交易额日志RDS");
        }

    }
}