public SOPhysicalCard(IPage page, string companyCode)
{
this.Page = page;
InitializeComponent();
UtilityHelper.ReadOnlyControl(gridTotalInfo, gridTotalInfo.Children.Count, true);
ProductQueryFilter query = new ProductQueryFilter();
query.ProductID = "GC-002-";
query.IsNotAbandon = true;
query.CompanyCode = CPApplication.Current.CompanyCode;
query.PagingInfo = new PagingInfo()
{
PageSize = 1000,
PageIndex = 0,
SortBy = "PP.CurrentPrice"
};
new OtherDomainQueryFacade(this.Page).QueryProductRequest(query, (obj, args) =>
{
if (args.FaultsHandle())
{
return;
}
var list = args.Result.Rows;
InitDataGrid(list);
});
}
public async Task <IEnumerable <Product> > FindAllAsync(ProductQueryFilter filters)
{
return(await _productRepository.Queryable()
.WhereFor(!filters.IncludeInactivated, product => !product.Inactivated)
.WhereFor(filters.Description, product => EF.Functions.Like(product.Description, $"%{filters.Description}%"))
.ToListAsync());
}
public IActionResult GetAllWithPaging([FromBody] ProductQueryFilter filter)
{
var products = _unitOfWork.Products.GetAllProductsWithPaging(filter);
var metadata = new Metadata
{
TotalCount = products.TotalCount,
PageSize = products.PageSize,
CurrentPage = products.CurrentPage,
TotalPages = products.TotalPages,
HasNextPage = products.HasNextPage,
HasPreviousPage = products.HasPreviousPage,
NextPageUrl = _uriService.GetProductPaginationUri(filter, Url.RouteUrl(nameof(GetAllWithPaging))).ToString(),
PreviousPageUrl = _uriService.GetProductPaginationUri(filter, Url.RouteUrl(nameof(GetAllWithPaging))).ToString()
};
var response = new ApiResponse <IEnumerable <Product> >(products)
{
Meta = metadata,
Status = (int)HttpStatusCode.OK,
Message = "Success",
};
Response.Headers.Add("X-Pagination", JsonConvert.SerializeObject(metadata));
return(Ok(response));
}
public UCProductSearchForSO()
{
InitializeComponent();
this.Grid.DataContext = m_queryReq = new ProductQueryFilter();
this.Loaded += UCProductSearchForSO_Loaded;
m_selectData = new Dictionary <string, ProductVM>();
}
/// <summary>
/// 分页查询商品
/// </summary>
/// <param name="queryFilter"></param>
/// <returns></returns>
public static QueryResult <ProductQueryInfo> QueryProduct(ProductQueryFilter queryFilter)
{
if (queryFilter == null || string.IsNullOrEmpty(queryFilter.VendorSysNo))
{
throw new BusinessException("商家编号不能为空");
}
return(ProductDA.QueryProduct(queryFilter));
}
public async Task <IActionResult> Get([FromQuery] ProductQueryFilter filters)
{
var products = await _productService.FindAllAsync(filters);
var response = new Response(products);
return(Ok(response));
}
public QueryResult QueryProduct(ProductQueryFilter request)
{
int totalCount;
var dataTable = ObjectFactory <IProductQueryDA> .Instance.QueryProduct(request, out totalCount);
return(new QueryResult
{
Data = dataTable,
TotalCount = totalCount
});
}
public PagedList <Product> GetAllProductsWithPaging(ProductQueryFilter filter)
{
filter.PageNumber = filter.PageNumber == 0 ? _paginationOption.DefaultPageNumber : filter.PageNumber;
filter.PageSize = filter.PageSize == 0 ? _paginationOption.DefaultPageSize : filter.PageSize;
var sql = "SELECT * FROM Products";
var result = base.DbConnection.Query <Product>(sql);
var pagedProducts = PagedList <Product> .Create(result, filter.PageNumber, filter.PageSize);
return(pagedProducts);
}
void UCProductSearchForCombo_Loaded(object sender, RoutedEventArgs e)
{
Loaded -= new RoutedEventHandler(UCProductSearchForCombo_Loaded);
this.Filter = new ProductQueryFilter();
//商品类型
this.cmbType.ItemsSource = EnumConverter.GetKeyValuePairs <ProductType>(EnumConverter.EnumAppendItemType.All);
this.cmbType.SelectedIndex = 0;
//商品状态
this.cmbStatus.ItemsSource = EnumConverter.GetKeyValuePairs <ProductStatus>(EnumConverter.EnumAppendItemType.All);
this.cmbStatus.SelectedIndex = 0;
}
/// <summary>
/// 使用商家编号及ProductSysNo查询商品
/// </summary>
/// <param name="sellerSysNo">商家编号</param>
/// <param name="productSysNo">商品编号</param>
/// <returns></returns>
public static ProductQueryInfo GetProductBySysNo(int productSysNo)
{
ProductQueryFilter filter = new ProductQueryFilter();
filter.PageIndex = 0;
filter.PageSize = int.MaxValue;
filter.ProductSysNo = productSysNo;
var result = ProductDA.QueryProduct(filter);
if (result.ResultList.Count == 1)
{
return(result.ResultList[0]);
}
else if (result.ResultList.Count > 1)
{
string msg = string.Format("商品#{0}存在重复记录，请确认。", productSysNo);
throw new BusinessException(msg);
}
string notFoundMsg = string.Format("商品#{0}不存在。", productSysNo);
throw new BusinessException(notFoundMsg);
}
/// <summary>
/// 获取商品销售区域列表
/// </summary>
/// <param name="productSysNo"></param>
/// <returns></returns>
public static QueryResult <ProductSalesAreaInfo> GetProductSalesAreaInfoBySysNo(ProductQueryFilter queryFilter)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("GetProductSalesAreaInfoBySysNo");
using (var sqlBuilder = new DynamicQuerySqlBuilder(command.CommandText, command, queryFilter, string.IsNullOrEmpty(queryFilter.SortFields) ? "pra.SysNo ASC" : queryFilter.SortFields))
{
if (queryFilter.ProductSysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "pra.ProductSysNo", DbType.Int32, "@ProductSysNo", QueryConditionOperatorType.Equal, queryFilter.ProductSysNo.Value);
}
command.CommandText = sqlBuilder.BuildQuerySql();
List <ProductSalesAreaInfo> resultList = command.ExecuteEntityList <ProductSalesAreaInfo>();
int totalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount"));
return(new QueryResult <ProductSalesAreaInfo>()
{
PageInfo = new PageInfo()
{
PageIndex = queryFilter.PageIndex, PageSize = queryFilter.PageSize, TotalCount = totalCount, SortBy = queryFilter.SortFields
}, ResultList = resultList
});
}
}
/// <summary>
/// 分页查询商品
/// </summary>
/// <param name="queryFilter"></param>
/// <returns></returns>
public static QueryResult <ProductQueryInfo> QueryProduct(ProductQueryFilter queryFilter)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("QueryProduct");
using (var sqlBuilder = new DynamicQuerySqlBuilder(command.CommandText, command, queryFilter, string.IsNullOrEmpty(queryFilter.SortFields) ? "P.[SysNo] DESC" : queryFilter.SortFields))
{
if (!string.IsNullOrEmpty(queryFilter.VendorSysNo))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "P.MerchantSysNo", DbType.Int32, "@VendorSysNo", QueryConditionOperatorType.Equal, queryFilter.VendorSysNo);
}
if (queryFilter.ProductSysNo.HasValue && queryFilter.ProductSysNo > 0)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND
, "p.[SysNo]"
, DbType.Int32
, "@ProductSysNo"
, QueryConditionOperatorType.Equal
, queryFilter.ProductSysNo
);
}
if (!string.IsNullOrEmpty(queryFilter.ProductID))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "p.[ProductID]", DbType.String, "@ProductID", QueryConditionOperatorType.Like, queryFilter.ProductID);
}
if (!string.IsNullOrEmpty(queryFilter.ProductTitle))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "P.[ProductTitle]", DbType.String, "@ProductTitle", QueryConditionOperatorType.Like, queryFilter.ProductTitle);
}
if (!string.IsNullOrEmpty(queryFilter.Status))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "P.[Status]", DbType.Int32, "@Status", queryFilter.StatusCondition == 0 ? QueryConditionOperatorType.Equal : QueryConditionOperatorType.NotEqual, queryFilter.Status);
}
if (!string.IsNullOrEmpty(queryFilter.CategorySysNo))
{
sqlBuilder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, "FPC.[SysNo]", QueryConditionOperatorType.In, queryFilter.CategorySysNo);
}
if (queryFilter.CreateTimeBegin != null && queryFilter.CreateTimeBegin != new DateTime())
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "P.[CreateTime]", DbType.DateTime, "@CreateTimeBegin", QueryConditionOperatorType.MoreThanOrEqual, queryFilter.CreateTimeBegin.ToString("yyyy-MM-dd"));
}
if (queryFilter.CreateTimeEnd != null && queryFilter.CreateTimeEnd != new DateTime())
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "P.[CreateTime]", DbType.DateTime, "@CreateTimeEnd", QueryConditionOperatorType.LessThan, queryFilter.CreateTimeEnd.AddDays(1).ToString("yyyy-MM-dd"));
}
if (!string.IsNullOrEmpty(queryFilter.ProductTradeType))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "PE.[ProductTradeType]", DbType.String, "@ProductTradeType", QueryConditionOperatorType.Equal, queryFilter.ProductTradeType);
}
if (!string.IsNullOrEmpty(queryFilter.UPCCode))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "PC.[UPCCode]"
, DbType.String
, "@UPCCode"
, QueryConditionOperatorType.Like, queryFilter.UPCCode
);
}
string sqlstr = sqlBuilder.BuildQuerySql();
command.CommandText = sqlstr;
List <ProductQueryInfo> resultList = command.ExecuteEntityList <ProductQueryInfo>();
int totalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount"));
return(new QueryResult <ProductQueryInfo>()
{
PageInfo = new PageInfo()
{
PageIndex = queryFilter.PageIndex,
PageSize = queryFilter.PageSize,
TotalCount = totalCount,
SortBy = queryFilter.SortFields
},
ResultList = resultList
});
}
}
public void QueryProduct(ProductQueryFilter filter, EventHandler <RestClientEventArgs <dynamic> > callback)
{
filter.CompanyCode = CPApplication.Current.CompanyCode;
restClient.QueryDynamicData("/IMService/Product/QueryProduct", filter, callback);
}
public ActionResult AjaxQuery()
{
ProductQueryFilter queryFilter = BuildQueryFilterEntity <ProductQueryFilter>();
int currentSellerSysNoValue = UserAuthHelper.GetCurrentUser().SellerSysNo;
if (currentSellerSysNoValue > 0)
{
queryFilter.VendorSysNo = currentSellerSysNoValue.ToString();
}
if (queryFilter.CategorySysNo == "0")
{
queryFilter.CategorySysNo = string.Empty;
}
if (queryFilter.StatusCondition == -1)
{
queryFilter.StatusCondition = 0;
queryFilter.Status = string.Empty;
}
//假设我拿到的是类别的CategoryCode，我需要根据他的值“CategoryCode-层级数-CategorySysNo” 拿到对应的三级CategorySysNo字符列表
if ((!string.IsNullOrWhiteSpace(queryFilter.CategoryCode)) && queryFilter.CategoryCode != "0")
{
string CategoryCode = queryFilter.CategoryCode.Split('-')[0].Trim();
string CategorySysNo = queryFilter.CategoryCode.Split('-')[2];
string IsLeaf = queryFilter.CategoryCode.Split('-')[3];
switch (queryFilter.CategoryCode.Split('-')[1])
{
case "1":
if (IsLeaf == "0")
{
var CateGory2List_One = ProductMaintainService.GetFrontProductCategoryByParentCode(CategoryCode);
foreach (var CateGory2 in CateGory2List_One)
{
if (CateGory2.IsLeaf == CommonYesOrNo.Yes)
{
queryFilter.CategorySysNo += CateGory2.SysNo + ",";
}
var Category3List_One = ProductMaintainService.GetFrontProductCategoryByParentCode(CateGory2.CategoryCode);
foreach (var item in Category3List_One)
{
queryFilter.CategorySysNo += item.SysNo + ",";
}
}
//去掉最后余出来的“，”
if (!string.IsNullOrWhiteSpace(queryFilter.CategorySysNo))
{
int RemoveIndex_One = queryFilter.CategorySysNo.Length - 1;
queryFilter.CategorySysNo = queryFilter.CategorySysNo.Remove(RemoveIndex_One);
}
else
{
queryFilter.CategorySysNo = CategorySysNo;
}
}
else
{
queryFilter.CategorySysNo = CategorySysNo;
}
break;
case "2":
if (IsLeaf == "0")
{
var Category3List_Two = ProductMaintainService.GetFrontProductCategoryByParentCode(CategoryCode);
foreach (var item in Category3List_Two)
{
queryFilter.CategorySysNo += item.SysNo + ",";
}
//去掉最后余出来的“，”
if (!string.IsNullOrWhiteSpace(queryFilter.CategorySysNo))
{
int RemoveIndex_One = queryFilter.CategorySysNo.Length - 1;
queryFilter.CategorySysNo = queryFilter.CategorySysNo.Remove(RemoveIndex_One);
}
else
{
queryFilter.CategorySysNo = CategorySysNo;
}
}
else
{
queryFilter.CategorySysNo = CategorySysNo;
}
break;
case "3":
queryFilter.CategorySysNo = CategorySysNo;
break;
default:
queryFilter.CategorySysNo = string.Empty;
break;
}
}
QueryResult <ProductQueryInfo>
result = ProductService.QueryProduct(queryFilter);
return(AjaxGridJson(result));
}
/// <summary>
/// 获取订单相关RMA
/// </summary>
/// <param name="query">过滤条件</param>
/// <param name="callback">回调函数</param>
public void QueryProductRequest(ProductQueryFilter query, EventHandler <RestClientEventArgs <dynamic> > callback)
{
query.CompanyCode = CPApplication.Current.CompanyCode;
GetRestClient(ConstValue.DomainName_IM).QueryDynamicData("/IMService/Product/QueryProduct", query, callback);
}
public async Task <IEnumerable <ProductResponseModel> > FindAllAsync(ProductQueryFilter filters)
{
var products = await _productService.FindAllAsync(filters);
return(_mapper.Map <IEnumerable <ProductResponseModel> >(products));
}
public Uri GetProductPaginationUri(ProductQueryFilter filter, string actionUrl)
{
string baseUrl = $"{_baseUri}{actionUrl}";
return(new Uri(baseUrl));
}
/// <summary>
/// 查询商品
/// </summary>
/// <returns></returns>
public virtual DataTable QueryProduct(ProductQueryFilter filter, out int totalCount)
{
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("QueryProduct");
using (var sb = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, ToPagingInfo(filter.PagingInfo), "Product.SysNo Desc"))
{
#region AddParameter
//商品ID
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"Product.ProductID",
DbType.String,
"@ProductID",
QueryConditionOperatorType.LeftLike,
StringUtility.IsEmpty(filter.ProductID) ? filter.ProductID : filter.ProductID.Trim()
);
//商品系统编号
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"Product.SysNo",
DbType.Int32,
"@ProductSysNo",
QueryConditionOperatorType.Equal,
filter.ProductSysNo
);
//商品名称
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"Product.ProductTitle",
DbType.String,
"@ProductName",
QueryConditionOperatorType.Like,
filter.ProductName
);
if (filter.VendorSysNo.HasValue)
{
// 供应商系统编号
// string template = string.Format(@"SELECT DISTINCT PO_ITM.ProductSysNo
// FROM [IPP3].[dbo].[PO_Item] PO_ITM WITH(NOLOCK)
// INNER JOIN [IPP3].[dbo].[PO_Master] PO_MST WITH(NOLOCK)
// ON PO_MST.SysNo = PO_ITM.POSysNo WHERE PO_MST.VendorSysNo={0}", filter.VendorSysNo.Value);
// sb.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, "Product.SysNo",
// QueryConditionOperatorType.In, template);
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"Product.MerchantSysNo",
DbType.Int32,
"@MerchantSysNo",
QueryConditionOperatorType.Equal,
filter.VendorSysNo
);
}
//PM操作人员
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"Product.PMUserSysNo",
DbType.Int32,
"@PMUserSysNo",
QueryConditionOperatorType.Equal,
filter.PMUserSysNo
);
//商品类型
if (filter.ProductType.HasValue)
{
//需要转变映射关系
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"Product.ProductType",
DbType.Int32,
"@ProductType",
QueryConditionOperatorType.Equal,
(int)filter.ProductType.Value
);
}
//商品状态
if (filter.ProductStatus.HasValue)
{
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"Product.Status",
DbType.Int32,
"@Status",
QueryConditionOperatorType.Equal,
(int)filter.ProductStatus.Value
);
}
//商品状态不为作废状态
if (filter.IsNotAbandon.HasValue && filter.IsNotAbandon.Value)
{
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"Product.Status",
DbType.Int32,
"@IsNotAbandon",
QueryConditionOperatorType.NotEqual,
ProductStatus.Abandon
);
}
//品牌
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"Product.BrandSysNo",
DbType.Int32,
"@BrandSysNo",
QueryConditionOperatorType.Equal,
filter.BrandSysNo
);
//创建时间
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"Product.[CreateTime]",
DbType.DateTime,
"@CreateDateFrom",
QueryConditionOperatorType.MoreThanOrEqual,
filter.CreateDateFrom
);
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"Product.[CreateTime]",
DbType.DateTime,
"@CreateDateTo",
QueryConditionOperatorType.LessThanOrEqual,
filter.CreateDateTo
);
//第三级分类
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"Product.C3SysNo",
DbType.Int32,
"@C3SysNo",
QueryConditionOperatorType.Equal,
filter.C3SysNo
);
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"C3.C2SysNo",
DbType.Int32,
"@C2SysNo",
QueryConditionOperatorType.Equal,
filter.C2SysNo
);
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"C2.C1SysNo",
DbType.Int32,
"@C1SysNo",
QueryConditionOperatorType.Equal,
filter.C1SysNo
);
//仓库查询后续提供
//sb.ConditionConstructor.AddCondition(
// QueryConditionRelationType.AND,
// "Inventory.WareHouseSysNumber",
// DbType.Int32,
// "@StockSysNo",
// QueryConditionOperatorType.Equal,
// filter.StockSysNo
// );
//频道？
//是否代销
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"Product.IsConsign",
DbType.String,
"@IsConsign",
QueryConditionOperatorType.Equal,
filter.IsConsign
);
//库存数量
if (filter.OnlineQty.HasValue)
{
QueryConditionOperatorType operatorType = QueryConditionOperatorType.Equal;
switch (filter.OnlineCondition.ToLower())
{
case "greater":
operatorType = QueryConditionOperatorType.MoreThan;
break;
case "less":
operatorType = QueryConditionOperatorType.LessThan;
break;
default:
operatorType = QueryConditionOperatorType.Equal;
break;
}
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"Inventory.OnlineQty",
DbType.Int32,
"@OnlineQty",
operatorType,
filter.OnlineQty
);
}
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"EX.CompanyProduct",
DbType.Int32,
"@CompanyProduct",
QueryConditionOperatorType.Equal,
filter.AZCustomer
);
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"Product.MerchantSysNo",
DbType.Int32,
"@MerchantSysNo",
QueryConditionOperatorType.Equal,
filter.MerchantSysNo
);
//公司编码
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"Product.CompanyCode",
DbType.String,
"@CompanyCode",
QueryConditionOperatorType.Equal,
filter.CompanyCode
);
if (filter.ProductIds != null && filter.ProductIds.Count > 0)
{
sb.ConditionConstructor.AddInCondition <string>(
QueryConditionRelationType.AND,
"Product.ProductID",
DbType.String,
filter.ProductIds
);
}
cmd.CommandText = sb.BuildQuerySql();
#endregion
EnumColumnList columnConfig = new EnumColumnList();
columnConfig.Add("Status", typeof(ProductStatus));
columnConfig.Add("ProductType", typeof(ProductType));
columnConfig.Add("InventoryType", typeof(ProductInventoryType));
var dt = cmd.ExecuteDataTable(columnConfig);
totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(dt);
}
}
