/// <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); }