public ActionResult GetList(PromotionQueryModel model, int pageSize, int pageIndex)
{
if (model.PromotionStatusType == -1)
{
model.PromotionStatus = null;
}
else if (model.PromotionStatusType == 1)
{
model.PromotionStatus = new[] { 1, 2, 3 };
}
else
{
model.PromotionStatus = new[] { model.PromotionStatusType };
}
PageOf <PromotionInfoModel> result;
try
{
result = promotionBLL.GetPromotionList(model, pageSize, pageIndex);
}
catch (Exception ext)
{
LogHelper.Error(ext);
result = new PageOf <PromotionInfoModel>();
}
return(View(result));
}
public PageOf <PromotionInfoModel> GetPromotionList(PromotionQueryModel query, int pageSize, int pageIndex)
{
var sql = @"(SELECT p.[Id],p.[SupplierId],p.[PromotionName],p.[StartTime],p.[EndTime],p.[PromotionLable],p.[PromotionCost]
,p.[PromotionStatus],p.[PromotionType],p.[CreateTime],p.[CreateBy],s.CompanyName
FROM [Promotions] p
INNER JOIN [Supplier] s ON s.SupplierID = p.[SupplierId]";
var db = DbSFO2OMain;
var parameters = db.CreateParameterCollection();
#region 查询条件
var sb = new StringBuilder();
sb.Append(" AND ((@StartTime<=p.StartTime AND @EndTime>=p.StartTime) OR (@StartTime<=p.EndTime AND @EndTime>=p.EndTime))");
parameters.Append("StartTime", query.BeginDate);
parameters.Append("EndTime", query.EndDate);
if (query.PromotionStatus != null && query.PromotionStatus.Length > 0)
{
if (query.PromotionStatus.Length == 1)
{
sb.Append(" AND p.PromotionStatus=@PromotionStatus");
parameters.Append("PromotionStatus", query.PromotionStatus[0]);
}
else
{
sb.Append(" AND p.PromotionStatus IN (" + String.Join(",", query.PromotionStatus) + ")");
}
}
if (!string.IsNullOrWhiteSpace(query.PromotionName))
{
sb.Append(" AND p.PromotionName LIKE @PromotionName");
parameters.Append("PromotionName", "%" + query.PromotionName.Trim() + "%");
}
if (query.SupplierID > 0)
{
sb.Append(" AND p.SupplierId=@SupplierId");
parameters.Append("SupplierId", query.SupplierID);
}
#endregion
sql += sb.ToString() + ") t ";
string finallySql = string.Format(@"select * from (select ROW_NUMBER() OVER(order by t.CreateTime DESC) AS RowNum,* from {0}
) as A where A.RowNum BETWEEN (@PageIndex-1)* @PageSize+1 AND @PageIndex*@PageSize ORDER BY RowNum;
SELECT COUNT(1) FROM {0};", sql);
parameters.Append("PageSize", pageSize);
parameters.Append("PageIndex", pageIndex);
var ds = db.ExecuteSqlDataSet(finallySql, parameters);
return(new PageOf <PromotionInfoModel>()
{
PageIndex = pageIndex,
PageSize = pageSize,
RowCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]),
Items = ds.Tables[0].ToEntityList <PromotionInfoModel>()
});
}
public PageOf <PromotionInfoModel> GetPromotionList(PromotionQueryModel query, int pageSize, int pageIndex)
{
return(dao.GetPromotionList(query, pageSize, pageIndex));
}
public ActionResult PromotionList(PromotionQueryModel model)
{
return(View(model));
}
