/// <summary>
/// 获取每行初始Model并获得初始数据
/// </summary>
/// <param name="timePeriod"></param>
/// <param name="usrType"></param>
/// <param name="regSource"></param>
/// <returns></returns>
public RetentionList GetInitialAccidByCondition(TimePeriodModel timePeriod, string usrName)
{
StringBuilder strSql = new StringBuilder();
StringBuilder strSource = new StringBuilder();
RetentionList rowFirstObj = new RetentionList();
int initialCount = 0;
List <int> initAccidList = new List <int>();
int firstDayLog = 0;
List <int> firstDayAccids = new List <int>();
if (usrName == "all")
{
strSql.Append("select count(distinct accid) from Sys_VisitInfo where insertTime between @stTime and @edTime and insertName<>'系统' ");
initialCount = DapperHelper.ExecuteScalar <int>(strSql.ToString(),
new { stTime = timePeriod.StDate, edTime = timePeriod.EdDate });
strSql.Clear();
strSql.Append(
"select distinct accid from Sys_VisitInfo where insertTime between @stTime and @edTime and insertName<>'系统'");
initAccidList =
DapperHelper.Query <int>(strSql.ToString(),
new { stTime = timePeriod.StDate, edTime = timePeriod.EdDate }).ToList();
}
else
{
strSql.Append(
"select count(distinct accid) from Sys_VisitInfo where insertTime between @stTime and @edTime and insertName='" +
usrName + "' ");
initialCount = DapperHelper.ExecuteScalar <int>(strSql.ToString(),
new { stTime = timePeriod.StDate, edTime = timePeriod.EdDate });
strSql.Clear();
strSql.Append(
"select distinct accid from Sys_VisitInfo where insertTime between @stTime and @edTime and insertName='" +
usrName + "' ");
initAccidList =
DapperHelper.Query <int>(strSql.ToString(),
new { stTime = timePeriod.StDate, edTime = timePeriod.EdDate }).ToList();
}
//添加一个新的留存率日期行对象
rowFirstObj.Date = timePeriod.TimePeriod;
rowFirstObj.InitialAccId = initAccidList;
rowFirstObj.RowMark = CommonLib.Helper.Md5Hash(rowFirstObj.Date);
rowFirstObj.InitialCount = initialCount;
DailyRetention monthlyRetention = new DailyRetention();
//首日注册用户登录量
//拼接初始化的AccId列表，为零则返回null
string accids = "(";
foreach (int accid in rowFirstObj.InitialAccId)
{
accids += accid.ToString() + ",";
}
if (accids.Length > 1)
{
accids = accids.Substring(0, accids.LastIndexOf(',')) + ")";
}
else
{
return(null);
}
strSql.Clear();
strSql.Append("select count(distinct accountid) from i200.dbo.T_LOG where OperDate between @bgTime and @edTime and accountid in " + accids + ";");
firstDayLog = DapperHelper.ExecuteScalar <int>(strSql.ToString(), new { bgTime = timePeriod.StDate, edTime = timePeriod.EdDate });
strSql.Clear();
strSql.Append("select distinct accountid from i200.dbo.T_LOG where OperDate between @bgTime and @edTime and accountid in " + accids + ";");
firstDayAccids =
DapperHelper.Query <int>(strSql.ToString(), new { bgTime = timePeriod.StDate, edTime = timePeriod.EdDate })
.ToList();
//添加首日初始化数据，日期标号为0
monthlyRetention.DayNum = 0;
monthlyRetention.NowActive = firstDayLog;
monthlyRetention.NoActive = initialCount;
if (initialCount != 0)
{
monthlyRetention.Ratio = firstDayLog * 100 / initialCount;
}
else
{
monthlyRetention.Ratio = 0;
}
monthlyRetention.DayAccids = CommonLib.Helper.JsonSerializeObject(firstDayAccids);
string mdString = firstDayLog.ToString() + monthlyRetention.DayNum.ToString() +
monthlyRetention.Ratio.ToString(); //定制列标记符，（规则为记录数+天数标识+比例数值）转MD5
monthlyRetention.ColumnMark = CommonLib.Helper.Md5Hash(mdString);
rowFirstObj.DataList.Add(monthlyRetention);
return(rowFirstObj);
}
/// <summary>
/// 获取每行初始Model并获得初始数据
/// </summary>
/// <param name="timePeriod"></param>
/// <param name="usrType"></param>
/// <param name="regSource"></param>
/// <returns></returns>
public RetentionList GetInitialAccidByCondition(TimePeriodModel timePeriod, string usrType, string regSource, string agent)
{
StringBuilder strSql = new StringBuilder();
StringBuilder strSource = new StringBuilder();
RetentionList rowFirstObj = new RetentionList();
int initialCount = 0;
List <int> initAccidList = new List <int>();
int firstDayLog = 0;
List <int> firstDayAccids = new List <int>();
switch (usrType)
{
case "regUser":
if (agent == "all")
{
strSql.Append("select count(*) from i200.dbo.T_Account where RegTime between @stTime and @edTime and state=1 and AgentId<>0 ");
}
else if (agent == "")
{
strSql.Append("select count(*) from i200.dbo.T_Account where RegTime between @stTime and @edTime and state=1");
}
else
{
strSql.Append("select count(*) from i200.dbo.T_Account where RegTime between @stTime and @edTime and state=1 and AgentId=" + Convert.ToInt32(agent) + " ");
}
if (regSource != "all")
{
strSql.Append(" and Remark='" + regSource + "';");
}
else
{
strSql.Append(";");
}
initialCount = DapperHelper.ExecuteScalar <int>(strSql.ToString(),
new { stTime = timePeriod.StDate, edTime = timePeriod.EdDate });
strSql.Clear();
if (agent == "all")
{
strSql.Append("select Id from i200.dbo.T_Account where RegTime between @stTime and @edTime and state=1 and AgentId<>0 ");
}
else if (agent == "")
{
strSql.Append("select Id from i200.dbo.T_Account where RegTime between @stTime and @edTime and state=1");
}
else
{
strSql.Append("select Id from i200.dbo.T_Account where RegTime between @stTime and @edTime and state=1 and AgentId=" + Convert.ToInt32(agent) + " ");
}
if (regSource != "all")
{
strSql.Append(" and Remark='" + regSource + "';");
}
else
{
strSql.Append(";");
}
initAccidList =
DapperHelper.Query <int>(strSql.ToString(),
new { stTime = timePeriod.StDate, edTime = timePeriod.EdDate }).ToList();
break;
case "paidUser":
string where = "";
if (agent == "all")
{
where += " a.AgentId<>0 ";
}
else if (agent == "")
{
where += " 1=1 ";
}
else
{
where += " a.AgentId=" + Convert.ToInt32(agent) + " ";
}
strSql.Append("select count(distinct accId) from i200.dbo.T_OrderInfo o left join i200.dbo.T_Account a on o.accId=a.ID ");
if (regSource != "all")
{
strSql.Append(" where a.Remark='" + regSource + "' and o.transactionDate between @stTime and @edTime and " + where + ";");
}
else
{
strSql.Append(" where transactionDate between @stTime and @edTime and " + where + ";");
}
initialCount = DapperHelper.ExecuteScalar <int>(strSql.ToString(), new { stTime = timePeriod.StDate, edTime = timePeriod.EdDate });
strSql.Clear();
strSql.Append("select accId from i200.dbo.T_OrderInfo o left join i200.dbo.T_Account a on o.accId=a.ID ");
if (regSource != "all")
{
strSql.Append(" where a.Remark='" + regSource + "' and o.transactionDate between @stTime and @edTime and " + where + " group by o.accId;");
}
else
{
strSql.Append(" where transactionDate between @stTime and @edTime and " + where + " group by accId;");
}
initAccidList = DapperHelper.Query <int>(strSql.ToString(), new { stTime = timePeriod.StDate, edTime = timePeriod.EdDate }).ToList();
break;
}
//添加一个新的留存率日期行对象
rowFirstObj.Date = timePeriod.TimePeriod;
rowFirstObj.InitialAccId = initAccidList;
rowFirstObj.RowMark = CommonLib.Helper.Md5Hash(rowFirstObj.Date);
rowFirstObj.InitialCount = initialCount;
DailyRetention monthlyRetention = new DailyRetention();
//首日注册用户登录量
//拼接初始化的AccId列表，为零则返回null
string accids = "(";
foreach (int accid in rowFirstObj.InitialAccId)
{
accids += accid.ToString() + ",";
}
if (accids.Length > 1)
{
accids = accids.Substring(0, accids.LastIndexOf(',')) + ")";
}
else
{
return(null);
}
strSql.Clear();
strSql.Append("select count(distinct accountid) from i200.dbo.T_LOG where OperDate between @bgTime and @edTime and accountid in " + accids + ";");
firstDayLog = DapperHelper.ExecuteScalar <int>(strSql.ToString(), new { bgTime = timePeriod.StDate, edTime = timePeriod.EdDate });
strSql.Clear();
strSql.Append("select distinct accountid from i200.dbo.T_LOG where OperDate between @bgTime and @edTime and accountid in " + accids + ";");
firstDayAccids =
DapperHelper.Query <int>(strSql.ToString(), new { bgTime = timePeriod.StDate, edTime = timePeriod.EdDate })
.ToList();
//添加首日初始化数据，日期标号为0
monthlyRetention.DayNum = 0;
monthlyRetention.NowActive = firstDayLog;
if (initialCount != 0)
{
monthlyRetention.Ratio = firstDayLog * 100 / initialCount;
}
else
{
monthlyRetention.Ratio = 0;
}
monthlyRetention.DayAccids = CommonLib.Helper.JsonSerializeObject(firstDayAccids);
string mdString = firstDayLog.ToString() + monthlyRetention.DayNum.ToString() +
monthlyRetention.Ratio.ToString(); //定制列标记符，（规则为记录数+天数标识+比例数值）转MD5
monthlyRetention.ColumnMark = CommonLib.Helper.Md5Hash(mdString);
rowFirstObj.DataList.Add(monthlyRetention);
return(rowFirstObj);
}