public static TimePeriod CreateTimePeriodFromModel(TimePeriodModel model)
{
TimePeriod tp;
switch (model.Type)
{
case "Cultivate": { tp = new Cultivate(); break; }
case "Implant": { tp = new Implant(); break; }
case "Bloom": { tp = new Bloom(); break; }
case "Sowing": { tp = new Sowing(); break; }
case "Harvest": { tp = new Harvest(); break; }
case "SeedMaturity": { tp = new SeedMaturity(); break; }
case "LifeTime": { tp = new LifeTime(); break; }
default: tp = new LifeTime(); break;
}
tp.Id = model.Id;
tp.Start = model.Start;
tp.StartArea = model.StartArea;
tp.StartMonth = model.StartMonth;
tp.EndArea = model.EndArea;
tp.EndMonth = model.EndMonth;
if (model.Next != null)
{
tp.Next = CreateTimePeriodFromModel(model.Next);
}
return(tp);
}
/// <summary>
/// مشخص می کند که آیا بازه زمانی موردنظر برای ارایه خالی است یا نه؟
/// </summary>
/// <param name="serviceSupply">ارایه</param>
/// <param name="datetime">تاریخ و زمان</param>
/// <returns></returns>
public bool IsEmptyTimePeriod(ServiceSupply serviceSupply, TimePeriodModel timePriod, ShiftCenterService polyclinicHealthService)
{
var founded = Calculate_Empty_TimePeriods(serviceSupply, timePriod.StartDateTime, polyclinicHealthService)
.Where(x => x.StartDateTime == timePriod.StartDateTime && x.EndDateTime == timePriod.EndDateTime).FirstOrDefault();
if (founded == null)
{
return(false);
}
return(true);
}
/// <summary>
/// 获取留存率每行的对象
/// </summary>
/// <param name="dateType"></param>
/// <param name="rowInitTime"></param>
/// <param name="usrType"></param>
/// <param name="regSource"></param>
/// <returns></returns>
public RetentionList GetRetentionRowData(string dateType, TimePeriodModel rowInitTime, string usrName)
{
RetentionList rowObj = new RetentionList();
List <int> accIdList = new List <int>();
StringBuilder strSql = new StringBuilder();
List <TimePeriodModel> rowPeriod = new List <TimePeriodModel>();
int usrCount = 0;
int initialCount = 0;
List <int> dayAccids = new List <int>();
DateTime edDate = DateTime.Now;
//根据日期类型对行数据段进行二次拆分
switch (dateType)
{
case "day":
edDate = rowInitTime.StDate.AddDays(12);
break;
case "week":
edDate = rowInitTime.StDate.AddDays(7 * 12);
break;
case "month":
edDate = rowInitTime.StDate.AddMonths(12);
break;
}
//拆出每行的时间段对象
rowPeriod = GetDatePeriod(rowInitTime.StDate, edDate, dateType);
if (rowPeriod.Count < 1)
{
return(null);
}
//获取每行首个数据
rowObj = GetInitialAccidByCondition(rowPeriod[0], usrName);
if (rowObj == null)
{
return(null);
}
initialCount = rowObj.InitialCount;
//拼接初始化的AccId列表，为零则返回null
string accids = "(";
foreach (int accid in rowObj.InitialAccId)
{
accids += accid.ToString() + ",";
}
if (accids.Length > 1)
{
accids = accids.Substring(0, accids.LastIndexOf(',')) + ")";
}
else
{
return(null);
}
int periodCount = 0;
rowPeriod.RemoveAt(0);
if (rowPeriod.Count > 0)
{
foreach (TimePeriodModel period in rowPeriod)
{
strSql.Clear();
strSql.Append("select count(distinct accountid) from i200.dbo.T_LOG where OperDate between @bgTime and @edTime and accountid in " + accids + " ;");
usrCount = DapperHelper.ExecuteScalar <int>(strSql.ToString(), new { bgTime = period.StDate, edTime = period.EdDate });
strSql.Clear();
strSql.Append("select distinct accountid from i200.dbo.T_LOG where OperDate between @bgTime and @edTime and accountid in " + accids + " ;");
dayAccids = DapperHelper.Query <int>(strSql.ToString(), new { bgTime = period.StDate, edTime = period.EdDate }).ToList();
periodCount++;
string mdString = "";
int ratio = 0;
if (initialCount != 0)
{
ratio = usrCount * 100 / initialCount;
}
else
{
ratio = 0;
}
List <int> lostAccids = new List <int>();
foreach (int i in rowObj.InitialAccId)
{
if (!dayAccids.Contains(i))
{
lostAccids.Add(i);
}
}
mdString = usrCount.ToString() + periodCount.ToString() + ratio.ToString();
rowObj.DataList.Add(new DailyRetention(periodCount, usrCount, ratio, CommonLib.Helper.JsonSerializeObject(dayAccids),
CommonLib.Helper.Md5Hash(mdString), CommonLib.Helper.JsonSerializeObject(lostAccids)));//定制列标记符，（规则为记录数+天数标识+比例数值）转MD5
}
}
return(rowObj);
}
/// <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);
}
