using System; using System.Collections.Generic; using Library; using LitJson; using System.Linq; using System.Data; using System.Threading; using MySystem.PxcModels2; namespace MySystem { public class StatNew2 { public readonly static StatNew2 Instance = new StatNew2(); private StatNew2() { } // 统计交易额V2 public void Start() { Thread th = new Thread(StartDo); th.IsBackground = true; th.Start(); } public void StartDo() { while (true) { string date = DateTime.Now.ToString("yyyyMMdd"); if(DateTime.Now.Hour > 10 && DateTime.Now.Hour < 13) { string check = function.ReadInstance("/ResetTrade/" + date + "-1.txt"); if(string.IsNullOrEmpty(check)) { function.WritePage("/ResetTrade/", date + "-1.txt", DateTime.Now.ToString()); StatTradeAmount(DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd")); } } else if(DateTime.Now.Hour > 16 && DateTime.Now.Hour < 19) { string check = function.ReadInstance("/ResetTrade/" + date + "-2.txt"); if(string.IsNullOrEmpty(check)) { function.WritePage("/ResetTrade/", date + "-2.txt", DateTime.Now.ToString()); StatTradeAmount(DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd")); } } else if(DateTime.Now.Hour > 22) { string check = function.ReadInstance("/ResetTrade/" + date + "-3.txt"); if(string.IsNullOrEmpty(check)) { function.WritePage("/ResetTrade/", date + "-3.txt", DateTime.Now.ToString()); StatTradeAmount(DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd")); } } if(DateTime.Now.Day == 1 && DateTime.Now.Hour > 3 && DateTime.Now.Hour < 6) { string month = date.Substring(0, 6); string check = function.ReadInstance("/ResetTrade/" + month + ".txt"); if(string.IsNullOrEmpty(check)) { function.WritePage("/ResetTrade/", month + ".txt", DateTime.Now.ToString()); DateTime start = DateTime.Now.AddMonths(-1); DateTime end = DateTime.Now; for(DateTime i = start; i < end;i=i.AddDays(1)) { StatTradeAmount(i.ToString("yyyy-MM-dd")); } } } if(DateTime.Now.Hour >= 3 && RedisDbconn.Instance.Get("StatServerStatus") == "1") { StatTradeAmount(DateTime.Now.ToString("yyyy-MM-dd")); } Thread.Sleep(900000); } } public void StartReset() { Thread th = new Thread(StartResetDo); th.IsBackground = true; th.Start(); } public void StartResetDo() { while (true) { string date = RedisDbconn.Instance.RPop("StatNew2Queue"); if(!string.IsNullOrEmpty(date)) { StatTradeAmount(date); Thread.Sleep(2000); } else { Thread.Sleep(60000); } } } public void StatTradeAmount(string today) { string date = today.Replace("-", ""); WebCMSEntities db = new WebCMSEntities(); KxsMainModels.WebCMSEntities maindb = new KxsMainModels.WebCMSEntities(); List userstat = new List(); List merstat = new List(); List snstat = new List(); try { string start = today + " 00:00:00"; string end = today + " 23:59:59"; Utils.WriteLog("开始时间:" + start, "统计商户和创客交易额日志2"); DataTable idsDt = CustomerSqlConn.dtable("select distinct UserId from ConsumerOrders where CreateDate>='" + start + "' and CreateDate<='" + end + "' and Status>0", AppConfig.Base.SqlConn2); Utils.WriteLog("查询条数:" + idsDt.Rows.Count, "统计商户和创客交易额日志2"); Utils.WriteLog("1--" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"), "统计商户和创客交易额日志2"); if(idsDt.Rows.Count > 0) { List idlist = new List(); foreach (DataRow idsDr in idsDt.Rows) { idlist.Add(int.Parse(function.CheckInt(idsDr["UserId"].ToString()))); } List users = maindb.Users.Where(m => idlist.Contains(m.Id)).ToList(); DataTable selfDt = CustomerSqlConn.dtable("select UserId,MerchantId,SnNo,PayMode,IsAct,DATE_FORMAT(CreateDate,'%Y%m%d'),sum(PayMoney),count(Id),sum(MaxDivi),sum(MerchantActualAmount),Sort from ConsumerOrders where CreateDate>='" + start + "' and CreateDate<='" + end + "' and Status>0 group by UserId,MerchantId,SnNo,PayMode,IsAct,DATE_FORMAT(CreateDate,'%Y%m%d'),Sort", AppConfig.Base.SqlConn2); if (selfDt.Rows.Count > 0) { foreach (DataRow selfDr in selfDt.Rows) { int UserId = int.Parse(selfDr["UserId"].ToString()); KxsMainModels.Users user = users.FirstOrDefault(m => m.Id == UserId) ?? new KxsMainModels.Users(); string ParentNav = user.ParentNav; int MerchantId = int.Parse(selfDr["MerchantId"].ToString()); int PayMode = int.Parse(selfDr["PayMode"].ToString()); int IsAct = int.Parse(selfDr["IsAct"].ToString()); string SnNo = selfDr["SnNo"].ToString(); string TradeDate = selfDr[5].ToString(); decimal TradeAmount = decimal.Parse(selfDr[6].ToString()); int TradeCount = int.Parse(selfDr[7].ToString()); decimal MaxDivi = decimal.Parse(selfDr[8].ToString()); decimal ActualAmount = decimal.Parse(selfDr[9].ToString()); string TradeMonth = TradeDate.Substring(0, 6); int Sort = int.Parse(selfDr["Sort"].ToString()); //创客交易额 UserAmountSummary selfStat = userstat.FirstOrDefault(m => m.UserId == UserId && m.IsAct == IsAct && m.TradeMonth == TradeMonth && m.TradeDate == TradeDate && m.PayMode == PayMode && m.SeoTitle == "self" && m.Sort == Sort); if (selfStat == null) { userstat.Add(new UserAmountSummary() { UserId = UserId, TradeMonth = TradeMonth, TradeDate = TradeDate, PayMode = PayMode, IsAct = IsAct, SeoTitle = "self", Sort = Sort, TotalAmount = TradeAmount, TradeCount = TradeCount, }); } else { selfStat.TotalAmount += TradeAmount; selfStat.TradeCount += TradeCount; } ParentNav += "," + UserId + ","; if (!string.IsNullOrEmpty(ParentNav)) { string[] ParentNavList = ParentNav.Trim(',').Replace(",,", ",").Split(','); foreach (string NavUserIdString in ParentNavList) { int NavUserId = int.Parse(NavUserIdString); UserAmountSummary teamStat = userstat.FirstOrDefault(m => m.UserId == NavUserId && m.IsAct == IsAct && m.TradeMonth == TradeMonth && m.TradeDate == TradeDate && m.PayMode == PayMode && m.SeoTitle == "team" && m.Sort == Sort); if (teamStat == null) { userstat.Add(new UserAmountSummary() { UserId = NavUserId, TradeMonth = TradeMonth, TradeDate = TradeDate, PayMode = PayMode, IsAct = IsAct, SeoTitle = "team", Sort = Sort, TotalAmount = TradeAmount, TradeCount = TradeCount, }); } else { teamStat.TotalAmount += TradeAmount; teamStat.TradeCount += TradeCount; } } } //商户交易额 MerchantAmountSummay merchantStat = merstat.FirstOrDefault(m => m.MerchantId == MerchantId && m.IsAct == IsAct && m.TradeMonth == TradeMonth && m.TradeDate == TradeDate && m.PayMode == PayMode); if (merchantStat == null) { merstat.Add(new MerchantAmountSummay() { MerchantId = MerchantId, TradeMonth = TradeMonth, TradeDate = TradeDate, PayMode = PayMode, IsAct = IsAct, TradeAmount = TradeAmount, TradeCount = TradeCount, TotalActual = ActualAmount, }); } else { merchantStat.TradeAmount += TradeAmount; merchantStat.TradeCount += TradeCount; merchantStat.TotalActual += ActualAmount; } //码牌交易额 QrCodeAmountSummay qrcode = snstat.FirstOrDefault(m => m.SnNo == SnNo && m.IsAct == IsAct && m.TradeMonth == TradeMonth && m.TradeDate == TradeDate && m.PayMode == PayMode); if (qrcode == null) { snstat.Add(new QrCodeAmountSummay() { SnNo = SnNo, TradeMonth = TradeMonth, TradeDate = TradeDate, PayMode = PayMode, IsAct = IsAct, TradeAmount = TradeAmount, TradeCount = TradeCount, TotalActual = ActualAmount, }); } else { qrcode.TradeAmount += TradeAmount; qrcode.TradeCount += TradeCount; qrcode.TotalActual += ActualAmount; } } } } } catch (Exception ex) { Utils.WriteLog(DateTime.Now.ToString() + "\n" + ex.ToString(), "统计商户和创客交易额异常"); } using (var tran = db.Database.BeginTransaction()) { try { List userlist = db.UserAmountSummary.Where(m => m.TradeDate == date).ToList(); List merlist = db.MerchantAmountSummay.Where(m => m.TradeDate == date).ToList(); List snlist = db.QrCodeAmountSummay.Where(m => m.TradeDate == date).ToList(); foreach(UserAmountSummary substat in userstat) { Utils.WriteLog(Newtonsoft.Json.JsonConvert.SerializeObject(substat)); UserAmountSummary selfStat = userlist.FirstOrDefault(m => m.UserId == substat.UserId && m.IsAct == substat.IsAct && m.TradeMonth == substat.TradeMonth && m.TradeDate == substat.TradeDate && m.PayMode == substat.PayMode && m.SeoTitle == substat.SeoTitle && m.Sort == substat.Sort); if (selfStat == null) { db.UserAmountSummary.Add(new UserAmountSummary() { UserId = substat.UserId, TradeMonth = substat.TradeMonth, TradeDate = substat.TradeDate, PayMode = substat.PayMode, IsAct = substat.IsAct, SeoTitle = substat.SeoTitle, Sort = substat.Sort, TotalAmount = substat.TotalAmount, TradeCount = substat.TradeCount, }); } else { UserAmountSummary edit = db.UserAmountSummary.FirstOrDefault(m => m.Id == selfStat.Id); if(edit != null) { edit.TotalAmount = substat.TotalAmount; edit.TradeCount = substat.TradeCount; } } } db.SaveChanges(); foreach(MerchantAmountSummay substat in merstat) { Utils.WriteLog(Newtonsoft.Json.JsonConvert.SerializeObject(substat)); MerchantAmountSummay merchantStat = merlist.FirstOrDefault(m => m.MerchantId == substat.MerchantId && m.IsAct == substat.IsAct && m.TradeMonth == substat.TradeMonth && m.TradeDate == substat.TradeDate && m.PayMode == substat.PayMode); if (merchantStat == null) { db.MerchantAmountSummay.Add(new MerchantAmountSummay() { MerchantId = substat.MerchantId, TradeMonth = substat.TradeMonth, TradeDate = substat.TradeDate, PayMode = substat.PayMode, IsAct = substat.IsAct, TradeAmount = substat.TradeAmount, TradeCount = substat.TradeCount, TotalActual = substat.TotalActual, }); } else { MerchantAmountSummay edit = db.MerchantAmountSummay.FirstOrDefault(m => m.Id == merchantStat.Id); if(edit != null) { edit.TradeAmount = substat.TradeAmount; edit.TradeCount = substat.TradeCount; edit.TotalActual = substat.TotalActual; } } } db.SaveChanges(); foreach(QrCodeAmountSummay substat in snstat) { Utils.WriteLog(Newtonsoft.Json.JsonConvert.SerializeObject(substat)); QrCodeAmountSummay qrcode = snlist.FirstOrDefault(m => m.SnNo == substat.SnNo && m.IsAct == substat.IsAct && m.TradeMonth == substat.TradeMonth && m.TradeDate == substat.TradeDate && m.PayMode == substat.PayMode); if (qrcode == null) { db.QrCodeAmountSummay.Add(new QrCodeAmountSummay() { SnNo = substat.SnNo, TradeMonth = substat.TradeMonth, TradeDate = substat.TradeDate, PayMode = substat.PayMode, IsAct = substat.IsAct, TradeAmount = substat.TradeAmount, TradeCount = substat.TradeCount, TotalActual = substat.TotalActual, }); } else { QrCodeAmountSummay edit = db.QrCodeAmountSummay.FirstOrDefault(m => m.Id == qrcode.Id); if(edit != null) { edit.TradeAmount = substat.TradeAmount; edit.TradeCount = substat.TradeCount; edit.TotalActual = substat.TotalActual; } } } db.SaveChanges(); tran.Commit(); } catch (Exception ex) { tran.Rollback(); Utils.WriteLog(DateTime.Now.ToString() + "\n" + ex.ToString(), "统计商户和创客交易额入库异常"); } } db.Dispose(); maindb.Dispose(); Utils.WriteLog(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff") + "\n\n", "统计商户和创客交易额日志2"); } // 统计创客激活数 public void StartPosActNum() { Thread th = new Thread(StartPosActNumFor); th.IsBackground = true; th.Start(); } public void StartPosActNumFor() { while (true) { if(DateTime.Now.Hour >= 3 && RedisDbconn.Instance.Get("StatServerStatus") == "1") { StartPosActNumEverTime(); } Thread.Sleep(20000); } } public void StartPosActNumEverTime() { Utils.WriteLog(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"), "实时执行创客激活数日志"); WebCMSEntities db = new WebCMSEntities(); KxsMainModels.WebCMSEntities maindb = new KxsMainModels.WebCMSEntities(); try { DataTable idsDt = CustomerSqlConn.dtable("select Id from MerchantInfo where ActStat=0 and (Status=2 or QueryCount=2) and BrandId=0 and UserId>0 and UpdateDate is not null limit 50", AppConfig.Base.SqlConn2); if(idsDt.Rows.Count > 0) { string ids = ""; foreach (DataRow idsDr in idsDt.Rows) { ids += idsDr["Id"].ToString() + ","; } DataTable userDt = CustomerSqlConn.dtable("select UserId,DATE_FORMAT(UpdateDate,'%Y%m%d'),count(Id) from MerchantInfo where Id in (" + ids.TrimEnd(',') + ") group by UserId,DATE_FORMAT(UpdateDate,'%Y%m%d')", AppConfig.Base.SqlConn2); if (userDt.Rows.Count > 0) { Utils.WriteLog("人数:" + userDt.Rows.Count + "\n\n", "实时执行创客激活数日志"); foreach (DataRow userDr in userDt.Rows) { int UserId = int.Parse(userDr["UserId"].ToString()); string TradeDate = userDr[1].ToString(); int ActCount = int.Parse(function.CheckInt(userDr[2].ToString())); string TradeMonth = TradeDate.Substring(0, 6); string date = TradeDate.Substring(0, 4) + "-" + TradeDate.Substring(4, 2) + "-" + TradeDate.Substring(6, 2); string start = date + " 00:00:00"; string end = DateTime.Parse(date).AddDays(1).ToString("yyyy-MM-dd") + " 00:00:00"; KxsMainModels.Users user = maindb.Users.FirstOrDefault(m => m.Id == UserId) ?? new KxsMainModels.Users(); string ParentNav = user.ParentNav; UserTradeMonthSummary selfStat = db.UserTradeMonthSummary.FirstOrDefault(m => m.UserId == UserId && m.TradeMonth == TradeMonth && m.SeoKeyword == TradeDate && m.SeoTitle == "self"); if (selfStat == null) { selfStat = db.UserTradeMonthSummary.Add(new UserTradeMonthSummary() { UserId = UserId, TradeMonth = TradeMonth, SeoKeyword = TradeDate, SeoTitle = "self", }).Entity; db.SaveChanges(); } selfStat.ActiveBuddyMerStatus += ActCount; ParentNav += "," + UserId + ","; if (!string.IsNullOrEmpty(ParentNav)) { string[] ParentNavList = ParentNav.Trim(',').Replace(",,", ",").Split(','); foreach (string NavUserIdString in ParentNavList) { int NavUserId = int.Parse(NavUserIdString); UserTradeMonthSummary teamStat = db.UserTradeMonthSummary.FirstOrDefault(m => m.UserId == NavUserId && m.TradeMonth == TradeMonth && m.SeoKeyword == TradeDate && m.SeoTitle == "team"); if (teamStat == null) { teamStat = db.UserTradeMonthSummary.Add(new UserTradeMonthSummary() { UserId = NavUserId, TradeMonth = TradeMonth, SeoKeyword = TradeDate, SeoTitle = "team", }).Entity; db.SaveChanges(); } teamStat.ActiveBuddyMerStatus += ActCount; } } } db.SaveChanges(); CustomerSqlConn.op("update MerchantInfo set ActStat=1 where Id in (" + ids.TrimEnd(',') + ")", AppConfig.Base.SqlConn2); } } } catch (Exception ex) { Utils.WriteLog(DateTime.Now.ToString() + "\n" + ex.ToString(), "实时执行创客激活数异常"); } db.Dispose(); maindb.Dispose(); Utils.WriteLog(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff") + "\n\n", "实时执行创客激活数日志"); } public void test() { Utils.WriteLog(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"), "实时执行创客激活数日志"); WebCMSEntities db = new WebCMSEntities(); KxsMainModels.WebCMSEntities maindb = new KxsMainModels.WebCMSEntities(); try { DataTable idsDt = CustomerSqlConn.dtable("select 646 Id", AppConfig.Base.SqlConn2); if(idsDt.Rows.Count > 0) { string ids = ""; foreach (DataRow idsDr in idsDt.Rows) { ids += idsDr["Id"].ToString() + ","; } DataTable userDt = CustomerSqlConn.dtable("select UserId,DATE_FORMAT(SignDate,'%Y%m%d'),count(Id) from MerchantInfo where Id in (" + ids.TrimEnd(',') + ") group by UserId,DATE_FORMAT(SignDate,'%Y%m%d')", AppConfig.Base.SqlConn2); if (userDt.Rows.Count > 0) { Utils.WriteLog("人数:" + userDt.Rows.Count + "\n\n", "实时执行创客激活数日志"); foreach (DataRow userDr in userDt.Rows) { int UserId = int.Parse(userDr["UserId"].ToString()); string TradeDate = userDr[1].ToString(); int ActCount = int.Parse(function.CheckInt(userDr[2].ToString())); string TradeMonth = TradeDate.Substring(0, 6); string date = TradeDate.Substring(0, 4) + "-" + TradeDate.Substring(4, 2) + "-" + TradeDate.Substring(6, 2); string start = date + " 00:00:00"; string end = DateTime.Parse(date).AddDays(1).ToString("yyyy-MM-dd") + " 00:00:00"; KxsMainModels.Users user = maindb.Users.FirstOrDefault(m => m.Id == UserId) ?? new KxsMainModels.Users(); string ParentNav = user.ParentNav; UserTradeMonthSummary selfStat = db.UserTradeMonthSummary.FirstOrDefault(m => m.UserId == UserId && m.TradeMonth == TradeMonth && m.SeoKeyword == TradeDate && m.SeoTitle == "self"); if (selfStat == null) { selfStat = db.UserTradeMonthSummary.Add(new UserTradeMonthSummary() { UserId = UserId, TradeMonth = TradeMonth, SeoKeyword = TradeDate, SeoTitle = "self", }).Entity; db.SaveChanges(); } selfStat.ActiveBuddyMerStatus += ActCount; ParentNav += "," + UserId + ","; if (!string.IsNullOrEmpty(ParentNav)) { string[] ParentNavList = ParentNav.Trim(',').Replace(",,", ",").Split(','); foreach (string NavUserIdString in ParentNavList) { int NavUserId = int.Parse(NavUserIdString); UserTradeMonthSummary teamStat = db.UserTradeMonthSummary.FirstOrDefault(m => m.UserId == NavUserId && m.TradeMonth == TradeMonth && m.SeoKeyword == TradeDate && m.SeoTitle == "team"); if (teamStat == null) { teamStat = db.UserTradeMonthSummary.Add(new UserTradeMonthSummary() { UserId = NavUserId, TradeMonth = TradeMonth, SeoKeyword = TradeDate, SeoTitle = "team", }).Entity; db.SaveChanges(); } teamStat.ActiveBuddyMerStatus += ActCount; } } } db.SaveChanges(); CustomerSqlConn.op("update MerchantInfo set ActStat=1 where Id in (" + ids.TrimEnd(',') + ")", AppConfig.Base.SqlConn2); } } } catch (Exception ex) { Utils.WriteLog(DateTime.Now.ToString() + "\n" + ex.ToString(), "实时执行创客激活数异常"); } db.Dispose(); maindb.Dispose(); Utils.WriteLog(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff") + "\n\n", "实时执行创客激活数日志"); } } }