public ActionResult AjaxQuery()
{
ProductMatchedTradingQueryFilter queryCriteria = BuildQueryFilterEntity <ProductMatchedTradingQueryFilter>();
queryCriteria.SortFields = "ProductSysNo";
int?currentSellerSysNo = null;
int currentSellerSysNoValue = UserAuthHelper.GetCurrentUser().SellerSysNo;
if (currentSellerSysNoValue > 0)
{
currentSellerSysNo = currentSellerSysNoValue;
}
queryCriteria.SellerSysNo = currentSellerSysNo;
QueryResult <ProductMatchedTradingQueryBasicInfo> result = ProductMatchedTradingService.QueryProductMatchedTradingBasicInfoList(queryCriteria);
return(AjaxGridJson(result));
}
/// <summary>
/// Query查询
/// </summary>
/// <param name="queryCriteria"></param>
/// <returns></returns>
public static QueryResult <ProductMatchedTradingQueryBasicInfo> QueryProductMatchedTradingBasicInfoList(ProductMatchedTradingQueryFilter queryCriteria)
{
int totalCount = 0;
QueryResult <ProductMatchedTradingQueryBasicInfo> result = new QueryResult <ProductMatchedTradingQueryBasicInfo>();
List <ProductMatchedTradingQueryBasicInfo> list = ProductMatchedTradingDA.QueryProductMatchedTradingBasicInfoList(queryCriteria, out totalCount);
result.ResultList = list;
result.PageInfo = new PageInfo
{
PageIndex = queryCriteria.PageIndex,
PageSize = queryCriteria.PageSize,
TotalCount = totalCount,
};
return(result);
}
/// <summary>
/// 产品咨询查询
/// </summary>
/// <param name="filter"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public static List <ProductMatchedTradingQueryBasicInfo> QueryProductMatchedTradingBasicInfoList(ProductMatchedTradingQueryFilter filter, out int totalCount)
{
PagingInfoEntity pagingEntity = new PagingInfoEntity();
pagingEntity.SortField = filter.SortFields;
pagingEntity.MaximumRows = filter.PageSize;
pagingEntity.StartRowIndex = filter.PageIndex * filter.PageSize;
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("ProductMatchedTrading_GetProductMatchedTradingDetailList");
using (var sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, pagingEntity, string.IsNullOrEmpty(pagingEntity.SortField) ? pagingEntity.SortField : "A.SysNo DESC"))
{
if (filter.SellerSysNo.HasValue)
{
dataCommand.AddInputParameter("@SellerSysNo", DbType.String, filter.SellerSysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"B.MerchantSysNo",
DbType.String, "@SellerSysNo",
QueryConditionOperatorType.Equal,
filter.SellerSysNo);
}
//sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ChannelID", DbType.String, "@ChannelID", QueryConditionOperatorType.Equal, filter.ChannelID);
//中蛋故有
if (filter.VendorType == 1)
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "VD.VendorType=0");
}
else
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "VD.SysNo", DbType.Int32, "@VendorType", QueryConditionOperatorType.Equal, filter.VendorType);
}
if (filter.ProductGroupNo != 0)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "PC.ProductGroupSysno", DbType.Int32, "@GroupID", QueryConditionOperatorType.Equal, filter.ProductGroupNo);
}
if (filter.IsByGroup)
{
if (filter.ProductSysNo > 0)
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "B.SysNo in(SELECT ProductSysNo FROM [OverseaContentManagement].[dbo].[V_CM_AllProductInfo] WHERE [ProductGroupSysno] IN (SELECT [ProductGroupSysno] FROM [OverseaContentManagement].[dbo].[V_CM_AllProductInfo] WHERE ProductID='" + filter.ProductID + "' AND ProductSysNo=" + filter.ProductSysNo + "))");
}
}
else
{
if (!string.IsNullOrEmpty(filter.ProductID))
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "A.ProductSysNo IN (SELECT SysNo FROM ipp3.dbo.Product WITH (NOLOCK) WHERE ProductID = '" + filter.ProductID + "')");
}
}
sqlBuilder.ConditionConstructor.AddBetweenCondition(QueryConditionRelationType.AND, "A.InDate", DbType.DateTime, "@InDate", QueryConditionOperatorType.MoreThanOrEqual, QueryConditionOperatorType.LessThanOrEqual, filter.InDateFrom, filter.InDateTo);
sqlBuilder.ConditionConstructor.AddBetweenCondition(QueryConditionRelationType.AND, "A.EditDate", DbType.DateTime, "@EditDate", QueryConditionOperatorType.MoreThanOrEqual, QueryConditionOperatorType.LessThanOrEqual, filter.EditDateFrom, filter.EditDateTo);
//商品类别
if (filter.Category1SysNo != null && filter.Category2SysNo != null && filter.Category3SysNo != null)
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "A.ProductSysNo IN (SELECT SysNo FROM ipp3.dbo.Product WITH (NOLOCK) WHERE C3SysNo = " + filter.Category3SysNo + " AND CompanyCode=" + filter.CompanyCode + ")");
}
else if (filter.Category1SysNo != null && filter.Category2SysNo != null)
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "A.ProductSysNo IN (SELECT p.SysNo FROM ipp3.dbo.Product p WITH (NOLOCK),ipp3.dbo.Category3 c WITH (NOLOCK) WHERE p.C3SysNo = c.SysNo AND c.C2SysNo =" + filter.Category2SysNo + " AND P.CompanyCode=" + filter.CompanyCode + " AND C.CompanyCode=" + filter.CompanyCode + ")");
}
else if (filter.Category1SysNo != null)
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "A.ProductSysNo IN (SELECT p.SysNo FROM ipp3.dbo.Product p WITH (NOLOCK),ipp3.dbo.Category3 c3 WITH (NOLOCK),ipp3.dbo.Category2 c2 WITH (NOLOCK) WHERE p.C3SysNo = c3.SysNo AND c3.C2SysNo = c2.SysNo AND c2.C1SysNo = " + filter.Category1SysNo + " AND P.companycode=" + filter.CompanyCode + " AND c2.companycode=" + filter.CompanyCode + " AND c3.companycode=" + filter.CompanyCode + ")");
}
if (filter.PMUserSysNo != null)
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "A.ProductSysNo IN (SELECT SysNo FROM ipp3.dbo.Product WITH (NOLOCK) WHERE PMUserSysNo = " + filter.PMUserSysNo + ")");
}
if (filter.ProductStatus != null)
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "A.ProductSysNo IN (SELECT SysNo FROM ipp3.dbo.Product WITH (NOLOCK) WHERE Status = " + filter.ProductStatus + ")");
}
if (!string.IsNullOrEmpty(filter.Status))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.Status", DbType.String, "@Status", QueryConditionOperatorType.Equal, filter.Status.Substring(0, 1));
if (filter.Status.Length > 1)
{
if (filter.Status.Substring(2, 1) == "1")
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "A.EditUser='******'");
}
else if (filter.Status.Substring(2, 1) == "2")
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "A.EditUser<>'System'");
}
}
}
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.Type", DbType.String, "@Type", QueryConditionOperatorType.Equal, filter.Type);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.Content", DbType.String, "@Content", QueryConditionOperatorType.Like, filter.Content);
if (filter.CustomerSysNo != null)
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "A.CustomerSysNo=" + filter.CustomerSysNo + "");
}
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.EditUser", DbType.String, "@EditUser", QueryConditionOperatorType.Like, filter.EditUser);
if (filter.CustomerCategory != null)
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "A.CustomerSysNo IN ( SELECT SysNo FROM ipp3.dbo.Customer WITH (NOLOCK) WHERE CompanyCustomer = " + filter.CustomerCategory + ")");
}
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.CompanyCode", DbType.String, "@CompanyCode", QueryConditionOperatorType.Equal, filter.CompanyCode);
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
//CodeNamePairColumnList pairList = new CodeNamePairColumnList();
//pairList.Add("Status", "MKT", "ReplyStatus");//咨询状态
////pairList.Add("ReferenceType", "MKT", "CommentsCategory");
//var dt = cmd.ExecuteDataTable(pairList);
//totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
List <ProductMatchedTradingQueryBasicInfo> list = dataCommand.ExecuteEntityList <ProductMatchedTradingQueryBasicInfo>();
totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
return(list);
}
}
