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 StatService2 { public readonly static StatService2 Instance = new StatService2(); private StatService2() { } // 统计交易额V2 public void Start() { Thread th = new Thread(StartDo); th.IsBackground = true; th.Start(); } public void StartDo() { while (true) { if(DateTime.Now.Hour >= 3 && RedisDbconn.Instance.Get("StatServerStatus") == "1") { StatTradeAmount(); } Thread.Sleep(2000); } } public void StatTradeAmount() { Utils.WriteLog(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"), "统计商户和创客交易额日志2"); WebCMSEntities db = new WebCMSEntities(); KxsMainModels.WebCMSEntities maindb = new KxsMainModels.WebCMSEntities(); using (var tran = db.Database.BeginTransaction()) { try { string start = DateTime.Now.AddDays(-100).ToString("yyyy-MM-dd HH:mm:ss"); Utils.WriteLog("开始时间:" + start, "统计商户和创客交易额日志2"); DataTable idsDt = CustomerSqlConn.dtable("select Id,UserId from ConsumerOrders where Id>=916515 and CreateDate>='" + start + "' and Status>0 and Version=0 order by Id limit 100", 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) { string ids = ""; List idlist = new List(); foreach (DataRow idsDr in idsDt.Rows) { ids += idsDr["Id"].ToString() + ","; idlist.Add(int.Parse(function.CheckInt(idsDr["UserId"].ToString()))); } List users = maindb.Users.Where(m => idlist.Contains(m.Id)).ToList(); Utils.WriteLog("ids:" + ids, "统计商户和创客交易额日志2"); 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 Id in (" + ids.TrimEnd(',') + ") group by UserId,MerchantId,SnNo,PayMode,IsAct,DATE_FORMAT(CreateDate,'%Y%m%d'),Sort", AppConfig.Base.SqlConn2); Utils.WriteLog("统计人数:" + selfDt.Rows.Count, "统计商户和创客交易额日志2"); Utils.WriteLog("2--" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"), "统计商户和创客交易额日志2"); if (selfDt.Rows.Count > 0) { Utils.WriteLog("开始循环", "统计商户和创客交易额日志2"); 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()); Utils.WriteLog("21--" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"), "统计商户和创客交易额日志2"); Utils.WriteLog("创客交易额", "统计商户和创客交易额日志2"); //创客交易额 // UserAmountSummary selfStat = db.UserAmountSummary.FirstOrDefault(m => m.Id > 1190107 && m.UserId == UserId && m.IsAct == IsAct && m.TradeMonth == TradeMonth && m.TradeDate == TradeDate && m.PayMode == PayMode && m.SeoTitle == "self" && m.Sort == Sort); // if (selfStat == null) // { // selfStat = db.UserAmountSummary.Add(new UserAmountSummary() // { // UserId = UserId, // TradeMonth = TradeMonth, // TradeDate = TradeDate, // PayMode = PayMode, // IsAct = IsAct, // SeoTitle = "self", // Sort = Sort, // }).Entity; // db.SaveChanges(); // } // 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 = db.UserAmountSummary.FirstOrDefault(m => m.Id > 1190107 && m.UserId == NavUserId && m.IsAct == IsAct && m.TradeMonth == TradeMonth && m.TradeDate == TradeDate && m.PayMode == PayMode && m.SeoTitle == "team" && m.Sort == Sort); // if (teamStat == null) // { // teamStat = db.UserAmountSummary.Add(new UserAmountSummary() // { // UserId = NavUserId, // TradeMonth = TradeMonth, // TradeDate = TradeDate, // PayMode = PayMode, // IsAct = IsAct, // SeoTitle = "team", // Sort = Sort, // }).Entity; // db.SaveChanges(); // } // teamStat.TotalAmount += TradeAmount; // teamStat.TradeCount += TradeCount; // } // } // db.SaveChanges(); Utils.WriteLog("22--" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"), "统计商户和创客交易额日志2"); Utils.WriteLog("商户交易额", "统计商户和创客交易额日志2"); Utils.WriteLog(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "\n" + MerchantId + ":" + IsAct + ":" + TradeMonth + ":" + TradeDate + ":" + PayMode + "----" + TradeAmount + ":" + TradeCount + ":" + ActualAmount, "统计商户交易额日志2"); //商户交易额 // MerchantAmountSummay merchantStat = db.MerchantAmountSummay.FirstOrDefault(m => m.MerchantId == MerchantId && m.IsAct == IsAct && m.TradeMonth == TradeMonth && m.TradeDate == TradeDate && m.PayMode == PayMode); // if (merchantStat == null) // { // merchantStat = db.MerchantAmountSummay.Add(new MerchantAmountSummay() // { // MerchantId = MerchantId, // TradeMonth = TradeMonth, // TradeDate = TradeDate, // PayMode = PayMode, // IsAct = IsAct, // }).Entity; // db.SaveChanges(); // } // merchantStat.TradeAmount += TradeAmount; // merchantStat.TradeCount += TradeCount; // merchantStat.TotalActual += ActualAmount; // db.SaveChanges(); // Utils.WriteLog("23--" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"), "统计商户和创客交易额日志2"); MerchantInfo merchant = db.MerchantInfo.FirstOrDefault(m => m.Id == MerchantId); if(merchant != null) { merchant.TotalAmount += TradeAmount; merchant.TotalConsumeCount += TradeCount; merchant.TotalActual += ActualAmount; merchant.ActMaxAmount += MaxDivi; //活动应返金额 if(IsAct == 1) { merchant.TotalActAmount += TradeAmount; merchant.TotalActActual += ActualAmount; if(merchant.ActivationStatus == 0 && merchant.TotalActAmount >= 10000M) { merchant.ActivationStatus = 1; merchant.ActivationDate = DateTime.Now; } } } db.SaveChanges(); // Utils.WriteLog("24--" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"), "统计商户和创客交易额日志2"); // Utils.WriteLog(str: "码牌交易额", "统计商户和创客交易额日志2"); // //码牌交易额 // 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(); Utils.WriteLog("25--" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"), "统计商户和创客交易额日志2"); // string jsonString = "{\"TradeDate\":\"" + TradeDate + "\",\"PayMoney\":\"" + TradeAmount + "\",\"TradeCount\":\"" + TradeCount + "\",\"MerchantActualAmount\":\"" + ActualAmount + "\",\"MerchantId\":\"" + MerchantId + "\",\"PayMode\":\"" + PayMode + "\",\"IsAct\":\"" + IsAct + "\"}"; // RedisDbconn.Instance.AddList("StatMerchantTradeSummaryQueue2", jsonString); Utils.WriteLog(str: "----", "统计商户和创客交易额日志2"); } CustomerSqlConn.op("update ConsumerOrders set Version=1 where Id in (" + ids.TrimEnd(',') + ")", AppConfig.Base.SqlConn2); } } tran.Commit(); Utils.WriteLog(str: "提交", "统计商户和创客交易额日志2"); } 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", "实时执行创客激活数日志"); } } }