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", "实时执行创客激活数日志");
}
}
}