public QueryResult QueryOptionalAccessories(OptionalAccessoriesQueryFilter msg)
{
int totalCount;
var dataTable = ObjectFactory <IOptionalAccessoriesQueryDA> .Instance.Query(msg, out totalCount);
return(new QueryResult()
{
Data = dataTable,
TotalCount = totalCount
});
}
public void Query(OptionalAccessoriesQueryReqVM vm, int PageSize, int PageIndex, string SortField, EventHandler <RestClientEventArgs <dynamic> > callback)
{
OptionalAccessoriesQueryFilter filter = vm.ConvertVM <OptionalAccessoriesQueryReqVM, OptionalAccessoriesQueryFilter>();
filter.CompanyCode = CPApplication.Current.CompanyCode;
filter.PagingInfo = new QueryFilter.Common.PagingInfo
{
PageSize = PageSize,
PageIndex = PageIndex,
SortBy = SortField
};
string relativeUrl = "/MKTService/OptionalAccessories/Query";
restClient.QueryDynamicData(relativeUrl, filter, (obj, args) =>
{
if (args.FaultsHandle())
{
return;
}
callback(obj, args);
});
}
public DataTable Query(OptionalAccessoriesQueryFilter filter, out int totalCount)
{
PagingInfoEntity pagingEntity = new PagingInfoEntity();
pagingEntity.SortField = filter.PagingInfo.SortBy;
pagingEntity.MaximumRows = filter.PagingInfo.PageSize;
pagingEntity.StartRowIndex = filter.PagingInfo.PageIndex * filter.PagingInfo.PageSize;
if (!string.IsNullOrEmpty(pagingEntity.SortField) && pagingEntity.SortField.Contains("DiscountAmt"))
{
pagingEntity.SortField = pagingEntity.SortField.Replace("DiscountAmt", "Isnull(Sum(si.[Quantity] * si.[Discount]),0)");
}
if (!string.IsNullOrEmpty(pagingEntity.SortField) && pagingEntity.SortField.Contains("PriceDiff"))
{
pagingEntity.SortField = pagingEntity.SortField.Replace("PriceDiff", "Isnull(Sum(pr.[CurrentPrice]*si.[Quantity] - pr.UnitCost*si.[Quantity]+si.[Quantity]* si.[Discount]),0)");
}
var cmd = DataCommandManager.CreateCustomDataCommandFromConfig("QueryCombo");
using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "sm.[CreateTime] DESC"))
{
if (filter.SysNo != null && filter.SysNo.Value > 0)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"sm.SysNo", DbType.Int32, "@SystemNumber", QueryConditionOperatorType.Equal, filter.SysNo);
}
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"sm.Status", DbType.Int32, "@Status", QueryConditionOperatorType.Equal, filter.Status);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"sm.CreateUserSysNo", DbType.Int32, "@PM", QueryConditionOperatorType.Equal, filter.PM);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "sm.SaleRuleName", DbType.AnsiStringFixedLength,
"@SaleRuleName", QueryConditionOperatorType.Equal, filter.SaleRuleName);
//sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
// "p.ProductID", DbType.String, "@ProductID", QueryConditionOperatorType.Equal, filter.ProductID);
//sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
// "si.ProductSysNo", DbType.Int32, "@ProductSysNo", QueryConditionOperatorType.Equal, filter.ProductSysNo);
if (filter.SysNoList != null && filter.SysNoList.Count > 0)
{
List <object> list = new List <object>();
filter.SysNoList.ForEach(p =>
{
list.Add(p);
});
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"sm.SysNo", DbType.Int32, "@SysNoList", QueryConditionOperatorType.In, list);
}
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"sm.CompanyCode", DbType.String, "@CompanyCode", QueryConditionOperatorType.Equal, filter.CompanyCode);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"sm.ReferenceType", DbType.Int32, "@ReferenceType", QueryConditionOperatorType.Equal, 3);
if (filter.MerchantSysNo == 1)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"V.VendorType",
DbType.Int32,
"@MerchantSysNo",
QueryConditionOperatorType.Equal,
0);
}
//如果大于 1 则查询sysno=MerchantSysNo的记录
if (filter.MerchantSysNo > 1)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"V.SysNo",
DbType.Int32,
"@MerchantSysNo",
QueryConditionOperatorType.Equal,
filter.MerchantSysNo);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"V.VendorType",
DbType.Int32,
"@VendorType",
QueryConditionOperatorType.Equal,
1);
}
cmd.CommandText = sqlBuilder.BuildQuerySql();
//构造商品系统编号，商品编号查询参数
string strWhere = "";
if (filter.ProductSysNo.HasValue)
{
strWhere += " and p.SysNo=@ProductSysNo ";
cmd.AddInputParameter("@ProductSysNo", DbType.Int32, filter.ProductSysNo);
}
if (!string.IsNullOrWhiteSpace(filter.ProductID))
{
strWhere += " and p.ProductID=@ProductID";
cmd.AddInputParameter("@ProductID", DbType.AnsiString, filter.ProductID);
}
if (filter.Category1SysNo > 0)
{
strWhere += " AND C2.C1SysNo = @C1SysNo ";
cmd.AddInputParameter("@C1SysNo", DbType.Int32, filter.Category1SysNo);
}
if (filter.Category2SysNo > 0)
{
strWhere += " AND C2.SysNo = @C2SysNo ";
cmd.AddInputParameter("@C2SysNo", DbType.Int32, filter.Category2SysNo);
}
if (filter.Category3SysNo > 0)
{
strWhere += " AND P.Category3SysNo = @C3SysNo ";
cmd.AddInputParameter("@C3SysNo", DbType.Int32, filter.Category3SysNo);
}
if (filter.Category1SysNo < 1 && filter.Category2SysNo < 1 && filter.Category3SysNo < 1)
{//如果没有类别条件，则不过滤 主、次 商品
filter.OptionalAccessoriesItemType = -1;
}
if (filter.OptionalAccessoriesItemType > -1)
{
strWhere += " AND SI.IsMasterItem = @OptionalAccessoriesItemType ";
cmd.AddInputParameter("@OptionalAccessoriesItemType", DbType.Int32, filter.OptionalAccessoriesItemType);
}
cmd.ReplaceParameterValue("#StrWhere_Product#", strWhere);
DataTable dt = cmd.ExecuteDataTable(new EnumColumnList {
{ "Status", typeof(ComboStatus) }
});
totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(dt);
}
}