private void BindProductPromotions() { ProductPromotionsQuery giftQuery = new ProductPromotionsQuery(); giftQuery.Page.PageSize = this.pager.PageSize; giftQuery.Page.PageIndex = this.pager.PageIndex; giftQuery.Page.SortOrder = SortAction.Desc; giftQuery.IsPromotion = true; giftQuery.IsWholesale = this.isWholesale; giftQuery.BeginTime = this.calendarStart.Text; giftQuery.EndTime = this.calendarEnd.Text; int count; if (!string.IsNullOrWhiteSpace(giftQuery.EndTime)) { giftQuery.EndTime += " 23:59:59"; } this.grdPromoteSales.DataSource = PromoteHelper.GetPromotions(giftQuery, out count); this.grdPromoteSales.DataBind(); this.pager.TotalRecords = count; }
public static System.Data.DataTable GetPromotions(ProductPromotionsQuery query, out int count) { return(new PromotionDao().GetPromotions(query, out count)); }
public DataTable GetPromotions(ProductPromotionsQuery query, out int count) { DbCommand sqlStringCommand = this.database.GetSqlStringCommand("SELECT * FROM Ecshop_Promotions"); string sqlcount = ""; if (query.IsPromotion) { string sql = "SELECT ROW_NUMBER() over(order by ActivityId) as row_num,* FROM Ecshop_Promotions"; if (query.IsWholesale) { DbCommand expr_28 = sqlStringCommand; sql += string.Format(" WHERE PromoteType = {0}", 4); if (!string.IsNullOrWhiteSpace(query.BeginTime)) { sql += string.Format(" AND StartDate >= convert(datetime,'{0}')", query.BeginTime); } if (!string.IsNullOrWhiteSpace(query.EndTime)) { sql += string.Format(" WHERE StartDate <= convert(datetime,'{0}')", query.EndTime); } if (query.SupplierId.HasValue) { sql += string.Format(" AND SupplierId ={0} ", query.SupplierId); } sql = string.Format("SELECT * FROM ({0}) as a where 1=1", sql); sqlcount = sql; sql += string.Format(" and row_num between {0} and {1}", (query.Page.PageIndex - 1) * query.Page.PageSize + 1, (query.Page.PageIndex) * query.Page.PageSize); expr_28.CommandText = sql; } else { DbCommand expr_4C = sqlStringCommand; sql += string.Format(" WHERE PromoteType <> {0} AND PromoteType <= 10", 4); if (!string.IsNullOrWhiteSpace(query.BeginTime)) { sql += string.Format(" AND StartDate >= convert(datetime,'{0}')", query.BeginTime); } if (!string.IsNullOrWhiteSpace(query.EndTime)) { sql += string.Format(" AND StartDate <= convert(datetime,'{0}')", query.EndTime); } if (query.SupplierId.HasValue) { sql += string.Format(" AND SupplierId ={0} ", query.SupplierId); } sql = string.Format("SELECT * FROM ({0}) as a where 1=1", sql); sqlcount = sql; sql += string.Format(" and row_num between {0} and {1}", (query.Page.PageIndex - 1) * query.Page.PageSize + 1, (query.Page.PageIndex) * query.Page.PageSize); expr_4C.CommandText = sql; } } else { if (query.IsWholesale) { DbCommand expr_7C = sqlStringCommand; expr_7C.CommandText += string.Format(" WHERE PromoteType = {0} OR PromoteType = {1}", 13, 14); } else { DbCommand expr_A8 = sqlStringCommand; expr_A8.CommandText += string.Format(" WHERE PromoteType <> {0} AND PromoteType <> {1} AND PromoteType > 10", 13, 14); } } DbCommand expr_D3 = sqlStringCommand; expr_D3.CommandText += " ORDER BY ActivityId DESC"; if (sqlcount != "") { expr_D3.CommandText += ";SELECT COUNT(*) FROM (" + sqlcount + ") as c"; } DataTable result; count = 0; using (IDataReader dataReader = this.database.ExecuteReader(sqlStringCommand)) { result = DataHelper.ConverDataReaderToDataTable(dataReader); dataReader.NextResult(); while (dataReader.Read()) { count = dataReader.GetInt32(0); } } return(result); }