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