public QueryResult QueryCombo(ComboQueryFilter msg)
{
int totalCount;
var dataTable = ObjectFactory <IComboQueryDA> .Instance.Query(msg, out totalCount);
return(new QueryResult()
{
Data = dataTable,
TotalCount = totalCount
});
}
public void Query(ComboQueryReqVM vm, int PageSize, int PageIndex, string SortField, EventHandler <RestClientEventArgs <dynamic> > callback)
{
ComboQueryFilter filter = vm.ConvertVM <ComboQueryReqVM, ComboQueryFilter>();
filter.CompanyCode = CPApplication.Current.CompanyCode;
filter.PM = null;
filter.PagingInfo = new QueryFilter.Common.PagingInfo
{
PageSize = PageSize,
PageIndex = PageIndex,
SortBy = SortField
};
string relativeUrl = "/MKTService/Combo/Query";
restClient.QueryDynamicData(relativeUrl, filter, (obj, args) =>
{
if (args.FaultsHandle())
{
return;
}
callback(obj, args);
});
}
public QueryResult <ComboQueryResult> Query(ComboQueryFilter filter)
{
return(_comboDA.Query(filter));
}
public QueryResult <ComboQueryResult> Query(ComboQueryFilter filter)
{
PagingInfoEntity pagingEntity = new PagingInfoEntity();
pagingEntity.SortField = filter.SortFields;
pagingEntity.MaximumRows = filter.PageSize;
pagingEntity.StartRowIndex = filter.PageIndex * filter.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"))
{
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.SaleRuleName", DbType.String,
"@SaleRuleName", QueryConditionOperatorType.Like, filter.Name);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"sm.VendorSysNo",
DbType.Int32,
"@MerchantSysNo",
QueryConditionOperatorType.Equal,
filter.SellerSysNo);
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);
}
cmd.ReplaceParameterValue("#StrWhere_Product#", strWhere);
var dataList = cmd.ExecuteEntityList <ComboQueryResult>();
int totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
var result = new QueryResult <ComboQueryResult>();
result.ResultList = dataList;
result.PageInfo = new PageInfo()
{
TotalCount = totalCount,
PageIndex = filter.PageIndex,
PageSize = filter.PageSize
};
return(result);
}
}
public DataTable Query(ComboQueryFilter 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"))
{
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, "sm.SysNo", DbType.Int32,
"@SaleRuleName", QueryConditionOperatorType.Equal, filter.RulesSysNo);
//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);
if (filter.MerchantSysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"V.SysNo",
DbType.Int32,
"@MerchantSysNo",
QueryConditionOperatorType.Equal,
filter.MerchantSysNo);
}
//如果大于 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);
//}
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"isnull(sm.ReferenceType,1)", DbType.Int32, "@ReferenceType", QueryConditionOperatorType.Equal, 1);
//if (filter.ProductSysNo > 0)
//{
// sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
// "si.ProductSysNo", DbType.Int32, "@ProductSysNo", QueryConditionOperatorType.Equal, filter.ProductSysNo);
//}
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);
}
cmd.ReplaceParameterValue("#StrWhere_Product#", strWhere);
DataTable dt = cmd.ExecuteDataTable(new EnumColumnList {
{ "Status", typeof(ComboStatus) }
});
totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(dt);
}
}
