/// <summary>
/// 根据条件查看活跃信息数据（返回一些汇总信息）
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="sqlWhere"></param>
/// <param name="orderBy"></param>
public SysShopActiveList GetListContainSummary(int pageIndex, int pageSize, List <DapperWhere> sqlWhere)
{
SysShopActiveList activeList = new SysShopActiveList();
pageIndex = pageIndex > 0 ? pageIndex : 1;
StringBuilder sqlStr = new StringBuilder();
string whereSt = "";
Dictionary <string, object> parm = new Dictionary <string, object>();
foreach (DapperWhere item in sqlWhere)
{
if (whereSt.Length > 0)
{
whereSt += " and ";
}
whereSt += item.Where;
parm[item.ColumnName] = item.Value;
}
sqlStr.Append(" select DISTINCT accid into #activelist from SysRpt_ShopActive ");
if (whereSt.Length > 0)
{
sqlStr.Append(" where " + whereSt + " ");
}
sqlStr.Append(";");
sqlStr.Append(" declare @maxCount int; ");
sqlStr.Append(" select @maxCount=COUNT(*) from #activelist ; ");
sqlStr.Append(" select top (" + pageSize.ToString() + ") accid from #activelist where accid not in( ");
sqlStr.Append(" select top " + (pageSize * (pageIndex - 1)).ToString() + " accid from #activelist order by accid desc ");
sqlStr.Append(" ) order by accid desc; ");
sqlStr.Append(" select COUNT(*) countNum from #activelist; ");
sqlStr.Append(" drop table #activelist; ");
var data = DapperHelper.QueryMultiple(sqlStr.ToString(), parm);
activeList.rowCount = 0;
if (data.Count > 0)
{
List <int> accountIdList = new List <int>();
foreach (dynamic item in data[0].ToList())
{
accountIdList.Add(Convert.ToInt32(item.accid));
}
SysRpt_ShopInfoDAL shopDal = new SysRpt_ShopInfoDAL();
activeList.shopList = shopDal.GetAccountSummarize(accountIdList.ToArray());
}
if (data.Count > 1)
{
activeList.rowCount = Convert.ToInt32(data[1].ToList().First().countNum);
}
return(activeList);
}
public List <IEnumerable <dynamic> > GetDailyAnalyzeList(int page, int source, string column, string whereStr, DateTime dateTime, string orderWhere)
{
orderWhere = " order by " + (orderWhere.Length > 0 ? orderWhere : "id desc");
page = page < 1 ? 1 : page;
int pageSize = 15;
StringBuilder strSql = new StringBuilder();
//用户来源标签显示
var flagColumn = "";
var flagSql = new StringBuilder();
if (source == 0)
{
flagColumn = " ,Sys_TagNexus.t_Name";
flagSql.Append(" outer apply ( select Sys_TagInfo.t_Name from Sys_TagNexus left outer join Sys_TagInfo on Sys_TagInfo.id=Sys_TagNexus.tag_id");
flagSql.Append(" where Sys_TagNexus.tag_id in (21,22,23,24,30) and Sys_TagNexus.acc_id=SysRpt_ShopDayInfo.accountid) Sys_TagNexus");
}
else if (source != 0)
{
flagColumn = " ,Sys_TagNexus.t_Name";
flagSql.Append(" cross apply ( select Sys_TagInfo.t_Name from Sys_TagNexus left outer join Sys_TagInfo on Sys_TagInfo.id=Sys_TagNexus.tag_id");
flagSql.Append(" where Sys_TagNexus.tag_id=@tagId and Sys_TagNexus.acc_id=SysRpt_ShopDayInfo.accountid) Sys_TagNexus");
}
else
{
flagSql.Append("");
}
strSql.Append(" SELECT ");
strSql.Append(" " + column + " ");
strSql.Append(flagColumn);
strSql.Append(" ,ROW_NUMBER() OVER (" + orderWhere + ") AS 'RowNumber' into #OrderedOrders ");
strSql.Append(" FROM SysRpt_ShopDayInfo ");
strSql.Append(flagSql);
if (whereStr.Length > 0)
{
strSql.Append(" where " + whereStr);
}
strSql.Append("; SELECT #OrderedOrders.* ");
strSql.Append(" FROM #OrderedOrders ");
strSql.Append(" WHERE RowNumber BETWEEN ((@pageIndex-1) * @pageSize)+1 AND (@pageIndex * @pageSize) ");
strSql.Append(" select count(*) num from #OrderedOrders; drop table #OrderedOrders");
return(DapperHelper.QueryMultiple(strSql.ToString(), new
{
tagId = source,
pageIndex = page,
pageSize = pageSize,
nowDay = dateTime
}));
}
/// <summary>
/// 获得归属地店铺信息
/// </summary>
/// <param name="areaName">归属地名称</param>
/// <param name="bgDate">开始日期</param>
/// <param name="edDate">结束日期</param>
/// <param name="iPage">页数</param>
/// <returns></returns>
public Dictionary <string, object> GetAreaShopInfoEx(string areaName, DateTime bgDate, DateTime edDate, int iPage, int pageSize)
{
Dictionary <string, object> list = new Dictionary <string, object>();
areaName = areaName.Trim('市');
areaName = areaName.Trim('省');
if (pageSize < 1)
{
pageSize = 20;
}
if (iPage < 1)
{
iPage = 1;
}
List <AreaShopInfoEx> areaShopInfoList = new List <AreaShopInfoEx>();
StringBuilder strSql = new StringBuilder();
strSql.Append(" select accid into #list from Sys_Account where accid in(select id from i200.dbo.T_Account where ");
strSql.Append(" RegTime between @bgDate and @edDate and State=1) and sysAddress like '%'+ @AreaName +'%'; ");
strSql.Append(" select count(distinct accid) as accNum from #list; ");
strSql.Append(" select * from (select row_number() over(order by accountid desc) as rowNumer,accountid,I200.dbo.T_Account.RegTime,I200.dbo.T_Account.CompanyName, ");
strSql.Append(" max(allLoginNum) as LoginNum,max(lastLoginTime) as lastLoginTime ");
strSql.Append(" from SysRpt_ShopDayInfo left outer join I200.dbo.T_Account on I200.dbo.T_Account.ID=SysRpt_ShopDayInfo.accountid ");
strSql.Append(" where SysRpt_ShopDayInfo.accountid in(select accid from #list) group by accountid,I200.dbo.T_Account.RegTime,I200.dbo.T_Account.CompanyName ");
strSql.Append(" ) List ");
strSql.Append(" where List.rowNumer between @bgNumber and @edNumber ");
strSql.Append(" order by LoginNum desc ");
strSql.Append(" drop table #list ");
//页数计算
int bgNumber = ((iPage - 1) * pageSize) + 1;
int edNumber = (iPage) * pageSize;
var dataList = DapperHelper.QueryMultiple(strSql.ToString(), new {
AreaName = areaName,
bgDate = bgDate,
edDate = edDate,
bgNumber = bgNumber,
edNumber = edNumber
});
if (dataList != null && dataList.Count > 0)
{
var maxCntList = dataList[0].ToList();
int rowCount = Convert.ToInt32(maxCntList[0].accNum);
int maxPage = 0;
if (rowCount > 0)
{
maxPage = Convert.ToInt32(Math.Ceiling(Convert.ToDouble(rowCount) / pageSize));
}
foreach (dynamic dr in dataList[1].ToList())
{
AreaShopInfoEx areaShopInfo = new AreaShopInfoEx();
areaShopInfo.accID = int.Parse(dr.accountid.ToString());
areaShopInfo.accName = dr.CompanyName.ToString();
areaShopInfo.regTime = DateTime.Parse(dr.RegTime.ToString());
areaShopInfo.loginTimes = int.Parse(dr.LoginNum.ToString());
areaShopInfo.lastLoginTime = DateTime.Parse(dr.lastLoginTime.ToString());
areaShopInfoList.Add(areaShopInfo);
}
list["rowCount"] = rowCount;
list["maxPage"] = maxPage;
list["pageIndex"] = iPage;
list["listData"] = areaShopInfoList;
}
return(list);
}
/// <summary>
/// 分组取交集
/// </summary>
/// <param name="mainSqlWhere"></param>
/// <param name="followSqlWhere"></param>
/// <returns></returns>
public SysShopActiveList GetGroupListContainSummary(int pageIndex, int pageSize, List <DapperWhere> mainSqlWhere, List <DapperWhere> followSqlWhere)
{
SysShopActiveList activeList = new SysShopActiveList();
pageIndex = pageIndex > 0 ? pageIndex : 1;
Dictionary <string, object> sqlParm = new Dictionary <string, object>();
#region 主要条件
string mainWhereSt = "";
foreach (DapperWhere item in mainSqlWhere)
{
if (mainWhereSt.Length > 0)
{
mainWhereSt += " and ";
}
mainWhereSt += item.Where;
sqlParm[item.ColumnName] = item.Value;
}
#endregion
#region 次要条件
string followWhereSt = "";
foreach (DapperWhere item in followSqlWhere)
{
if (followWhereSt.Length > 0)
{
followWhereSt += " and ";
}
followWhereSt += item.Where;
sqlParm[item.ColumnName] = item.Value;
}
#endregion
StringBuilder strSql = new StringBuilder();
strSql.Append(" select DISTINCT accid into #main from SysRpt_ShopActive ");
if (mainWhereSt.Length > 0)
{
strSql.Append(" where " + mainWhereSt);
}
strSql.Append(" ; ");
strSql.Append(" select DISTINCT accid into #follow from SysRpt_ShopActive ");
if (followWhereSt.Length > 0)
{
strSql.Append(" where " + followWhereSt);
}
strSql.Append(" ; ");
strSql.Append(" delete #main where #main.accid not in(select accid from #follow); ");
strSql.Append(" drop table #follow; ");
strSql.Append(" select top " + pageSize.ToString() + " accid from #main where accid not in(select top " + (pageSize * (pageIndex - 1)) + " accid from #main order by accid desc)order by accid desc; ");
strSql.Append(" select COUNT(*) countNum from #main; ");
strSql.Append(" drop table #main; ");
var dsJson = DapperHelper.QueryMultiple(strSql.ToString(), sqlParm);
activeList.rowCount = 0;
if (dsJson.Count > 0)
{
List <int> accountIdList = new List <int>();
foreach (dynamic item in dsJson[0].ToList())
{
accountIdList.Add(Convert.ToInt32(item.accid));
}
SysRpt_ShopInfoDAL shopDal = new SysRpt_ShopInfoDAL();
activeList.shopList = shopDal.GetAccountSummarize(accountIdList.ToArray());
}
if (dsJson.Count > 1)
{
activeList.rowCount = Convert.ToInt32(dsJson[1].ToList().First().countNum);
}
return(activeList);
}
public static List <OrderRenewalModel> GetSingleOrderRenewalModels(DateTime time)
{
List <OrderRenewalModel> thisMonthList = new List <OrderRenewalModel>();
OrderRenewalModel thisMonth = new OrderRenewalModel();
StringBuilder strSql = new StringBuilder();
strSql.Append(
"select o.accid,o.transactionDate,r.itemId,r.itemQuantity into #List " +
"from i200.dbo.T_OrderInfo o " +
"left join i200.dbo.T_Order_List r " +
"on o.oid=r.oid " +
"where r.itemId<>1;");
strSql.Append(
"select accid into #accidList from #List where itemId<>11 and DATEDIFF(MONTH,transactionDate,@date)=itemQuantity " +
"union " +
"select accid from #List where itemId=11 and DATEDIFF(MONTH,transactionDate,@date)=itemQuantity*12;");
strSql.Append("select * from #accidList;");
strSql.Append("select o.accid from i200.dbo.T_OrderInfo o left join i200.dbo.T_Order_List r on o.oid=r.oid where r.itemId not in (1,21,22,23) and DateDiff(month,o.transactionDate,@date)=0 and o.accid in (select accid from #accidList);");
strSql.Append("drop table #accidList;");
strSql.Append("drop table #List;");
thisMonth.Date = time.ToString("yyyy-MM");
List <IEnumerable <dynamic> > multiList = DapperHelper.QueryMultiple(strSql.ToString(),
new { date = time }).ToList();
thisMonth.ExpireUsr = multiList[0].Count();
if (thisMonth.ExpireUsr != 0)
{
foreach (dynamic i in multiList[0])
{
if (!thisMonth.ExAccids.Contains(i.accid))
{
thisMonth.ExAccids.Add(i.accid);
}
}
}
thisMonth.RenewalUsr = multiList[1].Count();
if (thisMonth.RenewalUsr != 0)
{
foreach (dynamic i in multiList[1])
{
if (!thisMonth.ReAccids.Contains(i.accid))
{
thisMonth.ReAccids.Add(i.accid);
}
}
}
if (thisMonth.ExpireUsr != 0)
{
thisMonth.Ratio = thisMonth.RenewalUsr * 100 / thisMonth.ExpireUsr;
}
thisMonth.ExAccidStr = CommonLib.Helper.JsonSerializeObject(thisMonth.ExAccids);
thisMonth.ReAccidStr = CommonLib.Helper.JsonSerializeObject(thisMonth.ReAccids);
thisMonth.NotReUsr = thisMonth.ExpireUsr - thisMonth.RenewalUsr;
thisMonth.NotReAccidStr = CommonLib.Helper.JsonSerializeObject(thisMonth.ExAccids.Except(thisMonth.ReAccids));
thisMonthList.Add(thisMonth);
List <OrderRenewalModel> dailyList = GetDailyRenewalModel(time);
if (dailyList.Count > 0)
{
foreach (OrderRenewalModel mod in dailyList)
{
thisMonthList.Add(mod);
}
}
return(thisMonthList);
}
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);
}