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) { StatTradeAmount(); Thread.Sleep(10000); } } public void StatTradeAmount() { 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 start = DateTime.Now.AddDays(-100).ToString("yyyy-MM-dd HH:mm:ss"); DataTable idsDt = CustomerSqlConn.dtable("select Id from ConsumerOrders where CreateDate>='" + start + "' and Status>0 and Version=0 order by Id limit 20", AppConfig.Base.SqlConn2); if(idsDt.Rows.Count > 0) { string ids = ""; foreach (DataRow idsDr in idsDt.Rows) { ids += idsDr["Id"].ToString() + ","; } DataTable selfDt = CustomerSqlConn.dtable("select UserId,MerchantId,SnNo,PayMode,IsAct,DATE_FORMAT(CreateDate,'%Y%m%d'),sum(PayMoney),count(Id),sum(MaxDivi),sum(MerchantActualAmount) from ConsumerOrders where Id in (" + ids.TrimEnd(',') + ") group by UserId,MerchantId,SnNo,PayMode,IsAct,DATE_FORMAT(CreateDate,'%Y%m%d')", AppConfig.Base.SqlConn2); 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(); 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); //创客交易额 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; 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.UserId == NavUserId && 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 = NavUserId, TradeMonth = TradeMonth, TradeDate = TradeDate, PayMode = PayMode, IsAct = IsAct, SeoTitle = "team", }).Entity; db.SaveChanges(); } teamStat.TotalAmount += TradeAmount; teamStat.TradeCount += TradeCount; } } db.SaveChanges(); //商户交易额 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; 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) { // RedisDbconn.Instance.AddList("ActProfitQueue", MerchantId.ToString()); merchant.ActivationStatus = 1; merchant.ActivationDate = DateTime.Now; } } } db.SaveChanges(); //码牌交易额 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(); string jsonString = "{\"TradeDate\":\"" + TradeDate + "\",\"PayMoney\":\"" + TradeAmount + "\",\"TradeCount\":\"" + TradeCount + "\",\"MerchantActualAmount\":\"" + ActualAmount + "\",\"MerchantId\":\"" + MerchantId + "\",\"PayMode\":\"" + PayMode + "\",\"IsAct\":\"" + IsAct + "\"}"; RedisDbconn.Instance.AddList("StatMerchantTradeSummaryQueue2", jsonString); } CustomerSqlConn.op("update ConsumerOrders set Version=1 where Id in (" + ids.TrimEnd(',') + ")", AppConfig.Base.SqlConn2); } } 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 StartPosActNum() { Thread th = new Thread(StartPosActNumFor); th.IsBackground = true; th.Start(); } public void StartPosActNumFor() { while (true) { if(DateTime.Now.Hour >= 3) { StartPosActNumEverTime(); } Thread.Sleep(20000); } } public void StartPosActNumEverTime() { function.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 UserId>0 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) { function.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) { 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 test() { function.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) { function.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) { 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", "实时执行创客激活数日志"); } } }