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(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("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("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("StatMerTradeAmountQueue"); if(!string.IsNullOrEmpty(content)) { StatMerTradeAmount(content, 1); StatMerTradeAmount(content, 2); } else { Thread.Sleep(10000); } } } public void StatMerTradeAmount(string content, int Kind) { try { 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); } catch (Exception ex) { function.WriteLog(DateTime.Now.ToString() + "\n" + ex.ToString(), "实时统计商户交易额日志RDS异常"); } } // 统计激活数到RDS public void StartAct() { Thread th = new Thread(StartActDo); th.IsBackground = true; th.Start(); } public void StartActDo() { while (true) { string content = RedisDbconn.Instance.RPop("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(); 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"); } } }