public virtual QueryResult QuerySaleGift(SaleGiftQueryFilter request)
{
int totalCount;
var dataTable = ObjectFactory <ISaleGiftQueryDA> .Instance.QuerySaleGift(request, out totalCount);
return(new QueryResult()
{
Data = dataTable,
TotalCount = totalCount
});
}
/// <summary>
/// 查询
/// </summary>
/// <param name="filterVM"></param>
/// <param name="callback"></param>
public void Query(SaleGiftQueryFilterViewModel filterVM, EventHandler <RestClientEventArgs <dynamic> > callback)
{
string relativeUrl = "/MKTService/SaleGift/Query";
SaleGiftQueryFilter filter = filterVM.ConvertVM <SaleGiftQueryFilterViewModel, SaleGiftQueryFilter>();
restClient.QueryDynamicData(relativeUrl, filter, (obj, args) =>
{
if (args.FaultsHandle())
{
return;
}
callback(obj, args);
});
}
public System.Data.DataTable QuerySaleGift(SaleGiftQueryFilter filter, out int totalCount)
{
PagingInfoEntity pagingEntity = new PagingInfoEntity();
pagingEntity.SortField = filter.PageInfo.SortBy;
pagingEntity.MaximumRows = filter.PageInfo.PageSize;
pagingEntity.StartRowIndex = filter.PageInfo.PageIndex * filter.PageInfo.PageSize;
var cmd = DataCommandManager.CreateCustomDataCommandFromConfig("SaleGift_QueryGift");
using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "SysNo DESC"))
{
//sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.WebChannelID", DbType.AnsiStringFixedLength,
// "@WebChannelID", QueryConditionOperatorType.Equal, filter.ChannelID);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.CompanyCode", DbType.AnsiStringFixedLength,
"@CompanyCode", QueryConditionOperatorType.Equal, filter.CompanyCode);
sqlBuilder.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.AND);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.OR, "B.BrandSysNo",
DbType.Int32, "@BrandSysNo", QueryConditionOperatorType.Equal, filter.BrandSysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.OR, "D.BrandSysNo",
DbType.Int32, "@BrandSysNo", QueryConditionOperatorType.Equal, filter.BrandSysNo);
sqlBuilder.ConditionConstructor.EndGroupCondition();
sqlBuilder.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.AND);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.OR, "B.C3SysNo",
DbType.Int32, "@Category3SysNo", QueryConditionOperatorType.Equal, filter.Category3SysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.OR, "D.Category3SysNo",
DbType.Int32, "@Category3SysNo", QueryConditionOperatorType.Equal, filter.Category3SysNo);
sqlBuilder.ConditionConstructor.EndGroupCondition();
#region Jack.G.tang 2013-1-7 update Bug 95316
/*修改原因:新增Category1和Category2的查询条件
*/
if (filter.Category1SysNo > 0)
{
sqlBuilder.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.AND);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.OR, "C3.Category1SysNo",
DbType.Int32, "@Category1SysNo", QueryConditionOperatorType.Equal, filter.Category1SysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.OR, "Category3.Category1SysNo",
DbType.Int32, "@Category1SysNo", QueryConditionOperatorType.Equal, filter.Category1SysNo);
sqlBuilder.ConditionConstructor.EndGroupCondition();
}
if (filter.Category2SysNo > 0)
{
sqlBuilder.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.AND);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.OR, "C3.Category2SysNo",
DbType.Int32, "@Category2SysNo", QueryConditionOperatorType.Equal, filter.Category2SysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.OR, "Category3.Category2SysNo",
DbType.Int32, "@Category2SysNo", QueryConditionOperatorType.Equal, filter.Category2SysNo);
sqlBuilder.ConditionConstructor.EndGroupCondition();
}
#endregion
if (filter.MasterProductSysNo.HasValue)
{
ProductBasic pMaster = GetProductBasic(filter.MasterProductSysNo.Value);
sqlBuilder.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.AND);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.OR, "B.ProductSysNo",
DbType.Int32, "@MasterProductSysNo", QueryConditionOperatorType.Equal, filter.MasterProductSysNo);
if (pMaster.C3SysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.OR, "B.C3SysNo",
DbType.Int32, "@MasterC3SysNo", QueryConditionOperatorType.Equal, pMaster.C3SysNo);
}
if (pMaster.BrandSysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.OR, "B.BrandSysNo",
DbType.Int32, "@MasterBrandSysNo", QueryConditionOperatorType.Equal, pMaster.BrandSysNo);
}
sqlBuilder.ConditionConstructor.EndGroupCondition();
}
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "C.ProductSysNo",
DbType.Int32, "@GiftProductSysNo", QueryConditionOperatorType.Equal, filter.GiftProductSysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.InUser",
DbType.String, "@PMUser", QueryConditionOperatorType.Equal, filter.PMUser);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.SysNo",
DbType.Int32, "@SysNo", QueryConditionOperatorType.Equal, filter.SysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.Status",
DbType.AnsiStringFixedLength, "@Status", QueryConditionOperatorType.Equal, filter.Status);
if (!string.IsNullOrEmpty(filter.PromotionName))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.PromotionName",
DbType.String, "@PromotionName", QueryConditionOperatorType.Like, filter.PromotionName);
}
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.Type",
DbType.AnsiStringFixedLength, "@Type", QueryConditionOperatorType.Equal, filter.Type);
if (filter.ActivityDateFrom.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.BeginDate",
DbType.DateTime, "@BeginDate", QueryConditionOperatorType.MoreThanOrEqual, filter.ActivityDateFrom);
}
if (filter.ActivityDateTo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.EndDate",
DbType.DateTime, "@EndDate", QueryConditionOperatorType.LessThan, filter.ActivityDateTo.Value);
}
if (filter.VendorSysNo > 0)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "VendorSysNo", DbType.Int32, "@VendorSysNo", QueryConditionOperatorType.Equal, filter.VendorSysNo);
}
cmd.CommandText = sqlBuilder.BuildQuerySql();
DataSet ds = cmd.ExecuteDataSet();
DataTable dt1 = ds.Tables[0];
DataTable dt2 = ds.Tables[1];
DataTable dt3 = ds.Tables[2];
if (dt1 != null && dt1.Rows.Count > 0)
{
foreach (DataRow row in dt1.Rows)
{
object status;
EnumCodeMapper.TryGetEnum(row["Status"], typeof(SaleGiftStatus), out status);
row["Status"] = status;
if (status != null)
{
row["StatusName"] = ((SaleGiftStatus)status).ToDisplayText();
}
object type;
EnumCodeMapper.TryGetEnum(row["Type"], typeof(SaleGiftType), out type);
row["Type"] = type;
if (type != null)
{
row["TypeName"] = ((SaleGiftType)type).ToDisplayText();
}
if (((SaleGiftType)type) == SaleGiftType.Full)
{
row["MasterProducts"] = "商品范围";
}
else
{
if (dt2 != null && dt2.Rows.Count > 0)
{
string masterProducts = "";
foreach (DataRow row2 in dt2.Rows)
{
if (row2["SysNo"].ToString() == row["SysNo"].ToString())
{
masterProducts += row2["ProductID"].ToString() + Environment.NewLine;
}
}
row["MasterProducts"] = masterProducts;
}
}
if (dt3 != null && dt3.Rows.Count > 0)
{
string giftProducts = "";
foreach (DataRow row3 in dt3.Rows)
{
if (row3["SysNo"].ToString() == row["SysNo"].ToString())
{
giftProducts += row3["ProductID"].ToString() + Environment.NewLine;
}
}
row["GiftProducts"] = giftProducts;
}
}
}
totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(dt1);
}
}
