/// <summary>
/// 获取不同用户的服务比
/// </summary>
/// <param name="stDate"></param>
/// <param name="edDate"></param>
/// <param name="usrName"></param>
/// <param name="partIndex"></param>
/// <returns></returns>
public static string GetCarePartitionPer(DateTime stDate, DateTime edDate, string usrName, int partIndex)
{
CarePercentModel carePer = new CarePercentModel();
carePer = CustomerCareBLL.GetCarePartitionPer(stDate, edDate, usrName, partIndex);
return(CommonLib.Helper.JsonSerializeObject(carePer));
}
public CarePercentModel GetCarePartitionPer(DateTime stDate, DateTime edDate, string usrName, int partIndex)
{
CarePercentModel careModel = new CarePercentModel();
StringBuilder strSql = new StringBuilder();
string filter = "";
if (usrName == "all")
{
filter = " and insertName <> '系统' ";
}
else
{
filter = " and insertName='" + usrName + "'";
}
//服务过用户
List <int> newRegAccid = new List <int>();
List <int> activeAccid = new List <int>();
List <int> sleepAccid = new List <int>();
List <int> lostAccid = new List <int>();
//全部用户
List <int> newRegAccidAll = new List <int>();
List <int> activeAccidAll = new List <int>();
List <int> sleepAccidAll = new List <int>();
List <int> lostAccidAll = new List <int>();
switch (partIndex)
{
case 1:
while (stDate < edDate)
{
//活跃用户
strSql.Append(
"select accid from SysRpt_ShopActive where DATEDIFF(DAY,@date,updatetime)<lastNum and DATEDIFF(DAY,@date,updatetime)>0 and active in (5,7);");
List <int> active = DapperHelper.Query <int>(strSql.ToString(), new { date = stDate }).ToList();
foreach (int i in active)
{
if (!activeAccidAll.Contains(i))
{
activeAccidAll.Add(i);
}
}
string accids = "(";
if (active.Count > 1)
{
foreach (int accid in active)
{
accids += accid.ToString() + ",";
}
accids = accids.Substring(0, accids.LastIndexOf(',')) + ")";
strSql.Clear();
strSql.Append(
"select accid from Sys_VisitInfo where DATEDIFF(DAY,@date,insertTime)=0 and accid in " + accids + " " + filter + " group by accid;");
List <int> accList = DapperHelper.Query <int>(strSql.ToString(), new { date = stDate, dateEnd = stDate.AddHours(23).AddMinutes(59).AddSeconds(59) }).ToList();
foreach (int i in accList)
{
if (!activeAccid.Contains(i))
{
activeAccid.Add(i);
}
}
}
strSql.Clear();
stDate = stDate.AddDays(1);
}
break;
case 2:
while (stDate < edDate)
{
//新用户
strSql.Append(
"select accid from SysRpt_ShopActive where DATEDIFF(DAY,@date,updatetime)<lastNum and DATEDIFF(DAY,@date,updatetime)>0 and active=1;");
List <int> newReg = DapperHelper.Query <int>(strSql.ToString(), new { date = stDate }).ToList();
foreach (int i in newReg)
{
if (!newRegAccidAll.Contains(i))
{
newRegAccidAll.Add(i);
}
}
string newRegAccids = "(";
if (newReg.Count > 1)
{
foreach (int accid in newReg)
{
newRegAccids += accid.ToString() + ",";
}
newRegAccids = newRegAccids.Substring(0, newRegAccids.LastIndexOf(',')) + ")";
strSql.Clear();
strSql.Append(
"select accid from Sys_VisitInfo where insertTime between @date and @dateEnd and accid in " + newRegAccids + " " + filter + " group by accid;");
List <int> regAccId = DapperHelper.Query <int>(strSql.ToString(), new { date = stDate, dateEnd = stDate.AddHours(23).AddMinutes(59).AddSeconds(59) }).ToList();
foreach (int i in regAccId)
{
if (!newRegAccid.Contains(i))
{
newRegAccid.Add(i);
}
}
}
strSql.Clear();
stDate = stDate.AddDays(1);
}
break;
case 3:
while (stDate < edDate)
{
//休眠用户
strSql.Append(
"select accid from SysRpt_ShopActive where DATEDIFF(DAY,@date,updatetime)<lastNum and DATEDIFF(DAY,@date,updatetime)>0 and active=-1;");
List <int> sleep = DapperHelper.Query <int>(strSql.ToString(), new { date = stDate }).ToList();
foreach (int i in sleep)
{
if (!sleepAccidAll.Contains(i))
{
sleepAccidAll.Add(i);
}
}
string sleepAccids = "(";
if (sleep.Count > 1)
{
foreach (int accid in sleep)
{
sleepAccids += accid.ToString() + ",";
}
sleepAccids = sleepAccids.Substring(0, sleepAccids.LastIndexOf(',')) + ")";
strSql.Clear();
strSql.Append(
"select accid from Sys_VisitInfo where insertTime between @date and @dateEnd and accid in " + sleepAccids + " " + filter + " group by accid;");
List <int> sleepAccIdList = DapperHelper.Query <int>(strSql.ToString(), new { date = stDate, dateEnd = stDate.AddHours(23).AddMinutes(59).AddSeconds(59) }).ToList();
foreach (int i in sleepAccIdList)
{
if (!sleepAccid.Contains(i))
{
sleepAccid.Add(i);
}
}
}
strSql.Clear();
stDate = stDate.AddDays(1);
}
break;
case 4:
while (stDate < edDate)
{
//流失用户
strSql.Append(
"select accid into #List from SysRpt_ShopActive where DATEDIFF(DAY,@date,updatetime)<lastNum and DATEDIFF(DAY,@date,updatetime)>0 and active=-3;" +
"select accid from #List;");
//List<int> lost = DapperHelper.Query<int>(strSql.ToString(), new { date = stDate }).ToList();
strSql.Append(
"select accid from Sys_VisitInfo where insertTime between @date and @dateEnd and accid in (select accid from #List) " + filter + " group by accid;" +
"drop table #List;");
List <IEnumerable <dynamic> > lostAccidList = DapperHelper.QueryMultiple(strSql.ToString(), new { date = stDate, dateEnd = stDate.AddHours(23).AddMinutes(59).AddSeconds(59) }).ToList();
List <dynamic> lost = lostAccidList[0].ToList();
foreach (dynamic i in lost)
{
if (!lostAccidAll.Contains(i.accid))
{
lostAccidAll.Add(i.accid);
}
}
List <dynamic> lostId = lostAccidList[1].ToList();
foreach (dynamic i in lostId)
{
if (!lostAccid.Contains(i.accid))
{
lostAccid.Add(i.accid);
}
}
strSql.Clear();
stDate = stDate.AddDays(1);
}
break;
}
switch (partIndex)
{
case 1:
careModel.ListData.Add(new PercentPart("active", activeAccid.Count, activeAccidAll.Count, (activeAccidAll.Count == 0 ? 0 : (Convert.ToDouble(activeAccid.Count) * 100 / activeAccidAll.Count)).ToString()));
break;
case 2:
careModel.ListData.Add(new PercentPart("new", newRegAccid.Count, newRegAccidAll.Count, (newRegAccidAll.Count == 0 ? 0 : (Convert.ToDouble(newRegAccid.Count) * 100 / newRegAccidAll.Count)).ToString()));
break;
case 3:
careModel.ListData.Add(new PercentPart("sleep", sleepAccid.Count, sleepAccidAll.Count, (sleepAccidAll.Count == 0 ? 0 : (Convert.ToDouble(sleepAccid.Count) * 100 / sleepAccidAll.Count)).ToString()));
break;
case 4:
careModel.ListData.Add(new PercentPart("lost", lostAccid.Count, lostAccidAll.Count, (lostAccidAll.Count == 0 ? 0 : (Convert.ToDouble(lostAccid.Count) * 100 / lostAccidAll.Count)).ToString()));
break;
}
return(careModel);
}