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