private static void BuilCondition <Q>(Q filter, string dataType, List <FilterField> filterFields, DynamicQuerySqlBuilder sqlBuilder)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "r.BusinessDataType", DbType.String, "@BusinessDataType", QueryConditionOperatorType.Equal, dataType);
List <string> changeTypes = new List <string>()
{
"A", "U"
};
sqlBuilder.ConditionConstructor.AddInCondition(QueryConditionRelationType.AND, "r.ChangeType", DbType.String, changeTypes);
int index = 0;
filterFields.ForEach(p =>
{
object parameterValue = Invoker.PropertyGet(filter, p.Name);
string[] pNames = p.ValuePath.Split(new char[] { '.' }, StringSplitOptions.RemoveEmptyEntries);
string path = string.Format("(//{0}/text())[1]", pNames.Join("/"));
DbType dbType = GetDbType(p.DBType);
string field = string.Format("r.BusinessData.value('{0}','{1}')", path, p.DBType);
QueryConditionOperatorType operatorType = GetEnum <QueryConditionOperatorType>(p.OperatorType);
QueryConditionRelationType relationType = GetEnum <QueryConditionRelationType>(p.RelationType);
sqlBuilder.ConditionConstructor.AddCondition(relationType,
field, dbType, "@Parameter" + index.ToString(), operatorType, parameterValue);
index++;
});
}
public void AddCondition(
QueryConditionRelationType conditionRelationType,
string fieldName, DbType parameterDbType, string parameterName, QueryConditionOperatorType conditionOperatorType,
object parameterValue)
{
AddCondition(conditionRelationType, fieldName, parameterDbType, parameterName,
conditionOperatorType, parameterValue, DefaultParameterValueValidationCheck);
}
public void AddBetweenCondition(
QueryConditionRelationType conditionRelationType,
string fieldName, DbType parameterDbType, string parameterName,
QueryConditionOperatorType leftConditionOperatorType, QueryConditionOperatorType rightConditionOperatorType,
object leftParameterValue, object rightParameterValue)
{
AddBetweenCondition(conditionRelationType, fieldName, parameterDbType, parameterName,
leftConditionOperatorType, rightConditionOperatorType, leftParameterValue, rightParameterValue,
DefaultParameterValueValidationCheck);
}
private void AddInOrNotInCondition(QueryConditionOperatorType operationType,
QueryConditionRelationType conditionRelationType, string fieldName, DbType listValueDbType, List <Object> inValues)
{
if (operationType != QueryConditionOperatorType.In && operationType != QueryConditionOperatorType.NotIn)
{
throw new ArgumentException("Operation Type must be 'In' or 'NotIn'.");
}
AddCondition(conditionRelationType, fieldName, listValueDbType, null,
operationType, inValues, value => { return(inValues != null && inValues.Count > 0); });
}
public void AddNullCheckCondition(
QueryConditionRelationType conditionRelationType, string fieldName, QueryConditionOperatorType conditionOperatorType)
{
if (conditionOperatorType != QueryConditionOperatorType.IsNull &&
conditionOperatorType != QueryConditionOperatorType.IsNotNull)
{
throw new ArgumentException("Parameter conditionOperatorType must be IsNull or IsNotNull in this method.");
}
AddCondition(conditionRelationType, fieldName, DbType.Object, null,
conditionOperatorType, null, value => { return(true); });
}
public void AddBetweenCondition(
QueryConditionRelationType conditionRelationType,
string fieldName, DbType parameterDbType, string parameterName,
QueryConditionOperatorType leftConditionOperatorType, QueryConditionOperatorType rightConditionOperatorType,
object leftParameterValue, object rightParameterValue,
ParameterValueValidateCheckDelegate parameterValueValidateCheckHandler)
{
BeginGroupCondition(conditionRelationType);
AddCondition(QueryConditionRelationType.AND, fieldName, parameterDbType, parameterName + "_Left",
leftConditionOperatorType, leftParameterValue, parameterValueValidateCheckHandler);
AddCondition(QueryConditionRelationType.AND, fieldName, parameterDbType, parameterName + "_Right",
rightConditionOperatorType, rightParameterValue, parameterValueValidateCheckHandler);
EndGroupCondition();
}
private void AddInOrNotInCondition <TListValueType>(QueryConditionOperatorType operationType,
QueryConditionRelationType conditionRelationType, string fieldName, DbType listValueDbType, List <TListValueType> inValues)
{
List <Object> convertedInValues = new List <object>();
if (inValues != null)
{
foreach (TListValueType element in inValues)
{
convertedInValues.Add(element);
}
}
AddInOrNotInCondition(operationType, conditionRelationType, fieldName, listValueDbType, convertedInValues);
}
protected string GetOperatorString(QueryConditionOperatorType operatorType)
{
switch (operatorType)
{
case QueryConditionOperatorType.Equal:
return("=");
case QueryConditionOperatorType.LessThan:
return("<");
case QueryConditionOperatorType.LessThanOrEqual:
return("<=");
case QueryConditionOperatorType.MoreThan:
return(">");
case QueryConditionOperatorType.MoreThanOrEqual:
return(">=");
case QueryConditionOperatorType.NotEqual:
return("<>");
case QueryConditionOperatorType.Like:
case QueryConditionOperatorType.LeftLike:
case QueryConditionOperatorType.RightLike:
return("LIKE");
case QueryConditionOperatorType.IsNull:
return("IS NULL");
case QueryConditionOperatorType.IsNotNull:
return("IS NOT NULL");
case QueryConditionOperatorType.In:
return("IN");
case QueryConditionOperatorType.NotIn:
return("NOT IN");
case QueryConditionOperatorType.Exist:
return("EXISTS");
case QueryConditionOperatorType.NotExist:
return("NOT EXISTS");
default: return(string.Empty);
}
}
protected object TryConvertToLikeString(object value, QueryConditionOperatorType type)
{
if (value != null && value.GetType().Equals(typeof(string)) && !ConditionConstructor.IsStringNullOrEmpty(value.ToString()))
{
if (type == QueryConditionOperatorType.Like)
{
return("%" + value.ToString() + "%");
}
else if (type == QueryConditionOperatorType.LeftLike)
{
return(value.ToString() + "%");
}
else if (type == QueryConditionOperatorType.RightLike)
{
return("%" + value.ToString());
}
}
return(value);
}
public ConditionConstructor AddSubQueryCondition(QueryConditionRelationType conditionRelationType,
string filedName, QueryConditionOperatorType conditionOperatorType, string subQuerySQLTemplate)
{
if (!IsStringNullOrEmpty(subQuerySQLTemplate))
{
SubQueryCondition condition = new SubQueryCondition()
{
ConditionRelationType = conditionRelationType,
FieldName = filedName,
OperatorType = conditionOperatorType,
SubQuerySQLTemplate = subQuerySQLTemplate,
SubQueryConditions = new List <Condition>()
};
m_conditions.Add(condition);
ConditionConstructor result = new ConditionConstructor(condition.SubQueryConditions);
return(result);
}
return(null);
}
public void AddCondition(
QueryConditionRelationType conditionRelationType,
string fieldName, DbType parameterDbType, string parameterName, QueryConditionOperatorType conditionOperatorType,
object parameterValue, ParameterValueValidateCheckDelegate parameterValueValidateCheckHandler)
{
if (parameterValueValidateCheckHandler == null)
{
parameterValueValidateCheckHandler = DefaultParameterValueValidationCheck;
}
if (parameterValueValidateCheckHandler(parameterValue))
{
m_conditions.Add(new SqlCondition
{
ConditionRelationType = conditionRelationType,
ParameterDbType = parameterDbType,
FieldName = fieldName,
ParameterName = parameterName,
OperatorType = conditionOperatorType,
ParameterValue = parameterValue
});
}
}
public System.Data.DataTable QueryMaster(ComputerConfigQueryFilter 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("GetComputerConfigMasterList");
var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "M.SysNo DESC");
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"M.SysNo",
DbType.Int32,
"@SysNo",
QueryConditionOperatorType.Equal,
filter.SysNo);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"M.ComputerConfigName",
DbType.String,
"@ComputerConfigName",
QueryConditionOperatorType.Like,
filter.ComputerConfigName);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"M.ComputerConfigTypeSysNo",
DbType.Int32,
"@ComputerConfigTypeSysNo",
QueryConditionOperatorType.Equal,
filter.ComputerConfigType);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"M.Status",
DbType.AnsiStringFixedLength,
"@Status",
QueryConditionOperatorType.Equal,
filter.Status);
#region 过滤前台删除状态C 的数据，后台不对此状态数据做处理
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"M.Status",
DbType.AnsiStringFixedLength,
"@ForeStatus",
QueryConditionOperatorType.NotEqual,
"C");
#endregion
if (filter.Owner.HasValue)
{
//根据Owner确定查询操作符
QueryConditionOperatorType ownerConditionOperator = QueryConditionOperatorType.LessThanOrEqual;
//客户
if (filter.Owner == ComputerConfigOwner.Customer)
{
ownerConditionOperator = QueryConditionOperatorType.MoreThan;
}
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"M.CustomerSysNo",
DbType.Int32,
"@CustomerSysNo",
ownerConditionOperator,
0);
}
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"VCC.BaseTotalPrice",
DbType.Decimal,
"@MinPriceRange",
QueryConditionOperatorType.MoreThanOrEqual,
filter.MinPriceRange);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"VCC.BaseTotalPrice",
DbType.Decimal,
"@MaxPriceRange",
QueryConditionOperatorType.LessThanOrEqual,
filter.MaxPriceRange);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"M.Priority",
DbType.Int32,
"@Priority",
QueryConditionOperatorType.Equal,
filter.Priority);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"M.EditUser",
DbType.AnsiString,
"@EditUser",
QueryConditionOperatorType.Like,
filter.EditUser);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"M.CompanyCode",
DbType.AnsiStringFixedLength,
"@CompanyCode",
QueryConditionOperatorType.Equal,
filter.CompanyCode);
//TODO:添加ChannelID参数
cmd.CommandText = sqlBuilder.BuildQuerySql();
//构造商品系统编号，商品编号查询参数
string strWhere = "";
if (filter.ProductSysNo.HasValue)
{
strWhere += " and Product.SysNo=@ProductSysNo ";
cmd.AddInputParameter("@ProductSysNo", DbType.Int32, filter.ProductSysNo);
}
if (!string.IsNullOrWhiteSpace(filter.ProductID))
{
strWhere += " and Product.ProductID=@ProductID";
cmd.AddInputParameter("@ProductID", DbType.AnsiString, filter.ProductID);
}
cmd.ReplaceParameterValue("#StrWhere_Product#", strWhere);
EnumColumnList enumConfig = new EnumColumnList();
enumConfig.Add("Status", typeof(ComputerConfigStatus));
var dt = cmd.ExecuteDataTable(enumConfig);
totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(dt);
}
/// <summary>
/// 查询条件设置
/// </summary>
/// <param name="queryEntity"></param>
/// <param name="cmd"></param>
/// <param name="sqlBuilder"></param>
private void AddParameterCustomerList(ECCentral.QueryFilter.Customer.CustomerQueryFilter queryEntity, CustomDataCommand cmd, DynamicQuerySqlBuilder sqlBuilder)
{
if (queryEntity.CustomerSysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"C.SysNo", DbType.Int32, "@SystemNumber",
QueryConditionOperatorType.Equal,
queryEntity.CustomerSysNo);
}
if (!string.IsNullOrEmpty(queryEntity.CustomerID))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"C.CustomerID", DbType.String, "@CustomerID",
QueryConditionOperatorType.LeftLike,
queryEntity.CustomerID);
#region 中蛋AZCustomerID
//sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.OR, "C.CustomerID LIKE @AZCustomerID");
//cmd.AddInputParameter("@AZCustomerID", DbType.String, string.Format("{0}%", "AstraZeneca-" + queryEntity.CustomerID));
#endregion
//sqlBuilder.ConditionConstructor.EndGroupCondition();
}
if (!string.IsNullOrEmpty(queryEntity.Email))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"C.Email", DbType.String, "@EmailQueryParameter",
QueryConditionOperatorType.LeftLike,
queryEntity.Email);
}
if (!string.IsNullOrEmpty(queryEntity.IdentityCard))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"C.IdentityCard", DbType.String, "@IdentityCard",
QueryConditionOperatorType.Like,
queryEntity.IdentityCard);
}
if (!string.IsNullOrEmpty(queryEntity.Phone))
{
sqlBuilder.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.AND);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.OR,
"C.Phone", DbType.String, "@Phone",
QueryConditionOperatorType.LeftLike,
queryEntity.Phone);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.OR,
"C.CellPhone", DbType.String, "@CellPhone",
QueryConditionOperatorType.LeftLike,
queryEntity.Phone);
sqlBuilder.ConditionConstructor.EndGroupCondition();
}
if (!string.IsNullOrEmpty(queryEntity.CustomerName))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"C.CustomerName", DbType.String, "@CustomerName",
QueryConditionOperatorType.LeftLike,
queryEntity.CustomerName);
}
if (queryEntity.Status.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"C.Status", DbType.Int32, "@Status",
QueryConditionOperatorType.Equal,
queryEntity.Status);
}
if (queryEntity.IsVip.HasValue)
{
if (queryEntity.IsVip.Value == CustomerVipOnly.VIP)
{
sqlBuilder.ConditionConstructor.AddInCondition(QueryConditionRelationType.AND, "C.VIPRank", DbType.Int32, new List <int>()
{
2, 4
});
}
else if (queryEntity.IsVip.Value == CustomerVipOnly.Ordinary)
{
sqlBuilder.ConditionConstructor.AddInCondition(QueryConditionRelationType.AND, "C.VIPRank", DbType.Int32, new List <int>()
{
1, 3
});
}
}
if (!string.IsNullOrEmpty(queryEntity.Address))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"C.DwellAddress", DbType.String, "@Address",
QueryConditionOperatorType.LeftLike,
queryEntity.Address);
}
if (queryEntity.IsPhoneConfirmed.HasValue)
{
if (queryEntity.IsPhoneConfirmed.Value == 0)
{
sqlBuilder.ConditionConstructor.AddNullCheckCondition(QueryConditionRelationType.AND,
"confirm.SysNo", QueryConditionOperatorType.IsNull);
}
else
{
sqlBuilder.ConditionConstructor.AddNullCheckCondition(QueryConditionRelationType.AND,
"confirm.SysNo", QueryConditionOperatorType.IsNotNull);
}
}
if (queryEntity.IsEmailConfirmed.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"C.IsEmailConfirmed", DbType.Int32, "@IsEmailConfirmed",
QueryConditionOperatorType.Equal,
queryEntity.IsEmailConfirmed);
}
if (!string.IsNullOrEmpty(queryEntity.FromLinkSource))
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"C.FromLinkSource", DbType.String, "@FromLinkSource",
QueryConditionOperatorType.LeftLike,
queryEntity.FromLinkSource);
}
if (!string.IsNullOrEmpty(queryEntity.RecommendedByCustomerID))
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"C.RecommendedByCustomerID", DbType.String, "@RecommendedByCustomerID",
QueryConditionOperatorType.LeftLike,
queryEntity.RecommendedByCustomerID);
}
if (queryEntity.AvtarImageStatus.HasValue)
{
if (queryEntity.AvtarImageStatus == AvtarShowStatus.NotSet)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"E.AvtarImageStatus", DbType.AnsiStringFixedLength, "@AvtarImageStatus",
QueryConditionOperatorType.IsNull,
"NULL");
}
else
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"E.AvtarImageStatus", DbType.AnsiStringFixedLength, "@AvtarImageStatus",
QueryConditionOperatorType.Equal,
queryEntity.AvtarImageStatus == AvtarShowStatus.NotShow ? "D" : "A");
}
}
if (queryEntity.CustomersType.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"C.CustomersType", DbType.Int32, "@CustomersType",
QueryConditionOperatorType.Equal,
queryEntity.CustomersType);
}
//数据库不支持ChannelSysNo
if (queryEntity.ChannelSysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"E.ChannelMasterSysNo", DbType.Int32, "@ChannelMasterSysNo",
QueryConditionOperatorType.Equal,
queryEntity.ChannelSysNo);
}
#region 注册时间
if (queryEntity.RegisterTimeFrom.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"C.RegisterTime", DbType.DateTime, "@RegisterTimeFrom",
QueryConditionOperatorType.MoreThanOrEqual,
queryEntity.RegisterTimeFrom);
}
if (queryEntity.RegisterTimeTo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"C.RegisterTime", DbType.DateTime, "@RegisterTimeTo",
QueryConditionOperatorType.LessThan,
queryEntity.RegisterTimeTo.Value);
}
#endregion
#region 购买数量
if (queryEntity.IsBuyCountCheck)
{
if (queryEntity.IsBuyCountRadio == 0)
{
QueryConditionOperatorType operatorType = QueryConditionOperatorType.Equal;
if (queryEntity.OperationSign.HasValue)
{
switch (queryEntity.OperationSign.Value)
{
case OperationSignType.Equal:
operatorType = QueryConditionOperatorType.Equal;
break;
case OperationSignType.MoreThanOrEqual:
operatorType = QueryConditionOperatorType.MoreThanOrEqual;
break;
case OperationSignType.LessThanOrEqual:
operatorType = QueryConditionOperatorType.LessThanOrEqual;
break;
case OperationSignType.MoreThan:
operatorType = QueryConditionOperatorType.MoreThan;
break;
case OperationSignType.LessThan:
operatorType = QueryConditionOperatorType.LessThan;
break;
case OperationSignType.NotEqual:
operatorType = QueryConditionOperatorType.NotEqual;
break;
}
}
if (queryEntity.BuyCountValue.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"ISNULL(E.BuyCount,0)", DbType.Int32, "@BuyCountValue",
operatorType,
queryEntity.BuyCountValue);
}
}
else
{
if (queryEntity.BuyCountBeginPoint.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"ISNULL(E.BuyCount,0)", DbType.Int32, "@BuyCountBeginPoint",
QueryConditionOperatorType.MoreThanOrEqual,
queryEntity.BuyCountBeginPoint);
}
if (queryEntity.BuyCountEndPoint.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"ISNULL(E.BuyCount,0)", DbType.Int32, "@BuyCountEndPoint",
QueryConditionOperatorType.LessThanOrEqual,
queryEntity.BuyCountEndPoint);
}
}
}
#endregion
if (!string.IsNullOrEmpty(queryEntity.VipCardNo))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"C.MemberShipCard", DbType.String, "@MemberShipCard",
QueryConditionOperatorType.Equal,
queryEntity.VipCardNo);
}
#region 社团ID
if (queryEntity.SocietyID.HasValue && queryEntity.SocietyID.Value > 0)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"C.SocietyID", DbType.Int32, "@SocietyID",
QueryConditionOperatorType.Equal,
queryEntity.SocietyID);
}
#endregion
cmd.CommandText = sqlBuilder.BuildQuerySql();
}
public DataTable QueryRegister(RegisterQueryFilter 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;
CustomDataCommand cmd = null;
if (filter.IsRepeatRegister.HasValue && filter.IsRepeatRegister.Value)
{
cmd = DataCommandManager.CreateCustomDataCommandFromConfig("QueryRegistersForRepeatRegister");
}
else
{
cmd = DataCommandManager.CreateCustomDataCommandFromConfig("QueryRegisters");
}
using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, " register.SysNo DESC "))
{
#region build conditions
#region
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"request.CustomerSysNo",
DbType.Int32,
"@CustomerSysNo",
QueryConditionOperatorType.Equal,
filter.CustomerSysNo
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"register.ProductSysNo",
DbType.Int32,
"@ProductSysNo",
QueryConditionOperatorType.Equal,
filter.ProductSysNo
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"request.RequestID",
DbType.String,
"@RequestID",
QueryConditionOperatorType.Equal,
filter.RequestID
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"register.IsRecommendRefund",
DbType.Int32,
"@IsRecommendRefund",
QueryConditionOperatorType.Equal,
filter.IsRecommendRefund
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"register.RequestType",
DbType.Int32,
"@RequestTypeCode",
QueryConditionOperatorType.Equal,
filter.RequestType
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"request.SOSysNo",
DbType.Int32,
"@SOSysNo",
QueryConditionOperatorType.Equal,
filter.SOSysNo
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"register.SysNo",
DbType.Int32,
"@SysNo",
QueryConditionOperatorType.Equal,
filter.RegisterSysNo
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"customer.CustomerID",
DbType.String,
"@CustomerID",
QueryConditionOperatorType.Equal,
filter.CustomerID
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"request.CreateTime",
DbType.DateTime,
"@CreateTimeFrom",
QueryConditionOperatorType.MoreThanOrEqual,
filter.CreateTimeFrom
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"request.CreateTime",
DbType.DateTime,
"@CreateTimeTo",
QueryConditionOperatorType.LessThanOrEqual,
filter.CreateTimeTo
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"register.RevertStatus",
DbType.Int32,
"@RevertStatusCode",
QueryConditionOperatorType.Equal,
filter.RevertStatus
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"register.NewProductStatus",
DbType.Int32,
"@NewProductStatusCode",
QueryConditionOperatorType.Equal,
filter.NewProductStatus
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"register.OutBoundStatus",
DbType.Int32,
"@OutBoundStatusCode",
QueryConditionOperatorType.Equal,
filter.OutBoundStatus
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"register.ReturnStatus",
DbType.Int32,
"@ReturnStatusCode",
QueryConditionOperatorType.Equal,
filter.ReturnStatus
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"register.Status",
DbType.Int32,
"@StatusCode",
QueryConditionOperatorType.Equal,
filter.RequestStatus
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"register.RefundStatus",
DbType.Int32,
"@RefundStatusCode",
QueryConditionOperatorType.Equal,
filter.RefundStatus
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"register.NextHandler",
DbType.Int32,
"@NextHandlerCode",
QueryConditionOperatorType.Equal,
filter.NextHandler
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"register.IsWithin7Days",
DbType.Int32,
"@IsWithin7Days",
QueryConditionOperatorType.Equal,
filter.IsWithin7Days
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"register.RMAReason",
DbType.Int32,
"@RMAReason",
QueryConditionOperatorType.Equal,
filter.RMAReason
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"product.PMUserSysNo",
DbType.Int32,
"@PMUserSysNo",
QueryConditionOperatorType.Equal,
filter.PMUserSysNo
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"category3.Category1SysNo",
DbType.Int32,
"@C1SysNo",
QueryConditionOperatorType.Equal,
filter.Category1SysNo
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"category3.Category2SysNo",
DbType.Int32,
"@C2SysNo",
QueryConditionOperatorType.Equal,
filter.Category2SysNo
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"category3.Category3SysNo",
DbType.Int32,
"@C3SysNo",
QueryConditionOperatorType.Equal,
filter.Category3SysNo
);
//销售方
if (filter.SellersType != null)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"request.InvoiceType",
DbType.AnsiStringFixedLength,
"@InvoiceType",
QueryConditionOperatorType.Equal,
filter.SellersType
);
}
//是否已打印标签
if (filter.IsLabelPrinted.HasValue)
{
string isLabelPrinted = string.Empty;
if (filter.IsLabelPrinted == false)
{
isLabelPrinted = "N";
}
else if (filter.IsLabelPrinted == true)
{
isLabelPrinted = "Y";
}
sqlBuilder.ConditionConstructor.AddCondition
(
QueryConditionRelationType.AND,
"request.IsLabelPrinted",
DbType.String,
"@IsLabelPrinted",
QueryConditionOperatorType.Equal,
isLabelPrinted
);
}
//商家处理结果
sqlBuilder.ConditionConstructor.AddCondition
(
QueryConditionRelationType.AND,
"register.SellerOperationInfo",
DbType.String,
"@SellerOperationInfo",
QueryConditionOperatorType.Equal,
filter.SellerOperationInfo
);
if (filter.IsRepeatRegister.HasValue)
{
if (filter.IsRepeatRegister.Value)
{
QueryConditionOperatorType qcOperator = QueryConditionOperatorType.MoreThan;
if (filter.CompareSymbol == CompareSymbolType.Equal)
{
qcOperator = QueryConditionOperatorType.Equal;
}
else if (filter.CompareSymbol == CompareSymbolType.LessThan)
{
qcOperator = QueryConditionOperatorType.LessThan;
}
else if (filter.CompareSymbol == CompareSymbolType.MoreThan)
{
qcOperator = QueryConditionOperatorType.MoreThan;
}
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"Repeat.RepeatProductCount",
DbType.Int32,
"@RepeatProductCount",
qcOperator,
filter.ProductCount);
}
else
{
string feedBackCondition1 = string.Empty;
string feedBackCondition2 = string.Empty;
feedBackCondition1 = @"SELECT 1
FROM [OverseaServiceManagement].[dbo].[RepeatRegister] WITH(NOLOCK)
WHERE SOSysNo = request.SOSysNo
AND [ProductSysNo] = register.ProductSysNo
AND [CurrentCount] = 1" ;
feedBackCondition2 = @"SELECT 1
FROM [OverseaServiceManagement].[dbo].[RepeatRegister] WITH(NOLOCK)
WHERE SOSysNo = request.SOSysNo
AND [ProductSysNo] = register.ProductSysNo" ;
using (GroupCondition group = new GroupCondition(sqlBuilder, QueryConditionRelationType.AND))
{
sqlBuilder.ConditionConstructor.AddSubQueryCondition(
QueryConditionRelationType.AND,
null,
QueryConditionOperatorType.Exist,
feedBackCondition1
);
sqlBuilder.ConditionConstructor.AddSubQueryCondition(
QueryConditionRelationType.OR,
null,
QueryConditionOperatorType.NotExist,
feedBackCondition2
);
}
}
}
if (!string.IsNullOrEmpty(filter.NextHandlerList) && filter.NextHandler == RMANextHandler.Dun)
{
string feedBackCondition = string.Empty;
feedBackCondition = "SELECT 1 FROM dbo.RMA_Register_Dunlog Dunlog WITH(NOLOCK) WHERE Dunlog.RegisterSysNo = register.SysNo"
+ " AND Dunlog.FeedBack IN (" + filter.NextHandlerList + ")";
ConditionConstructor subCondition = sqlBuilder.ConditionConstructor.AddSubQueryCondition(
QueryConditionRelationType.AND,
null,
QueryConditionOperatorType.Exist,
feedBackCondition
);
}
#endregion
#region DateTime conditions
if (filter.IsUnReceive.HasValue && filter.IsUnReceive.Value)
{
sqlBuilder.ConditionConstructor.AddNullCheckCondition(
QueryConditionRelationType.AND, "request.RecvTime", QueryConditionOperatorType.IsNull
);
}
else
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"request.RecvTime",
DbType.DateTime,
"@RecvTimeFrom",
QueryConditionOperatorType.MoreThanOrEqual,
filter.RecvTimeFrom
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"request.RecvTime",
DbType.DateTime,
"@RecvTimeTo",
QueryConditionOperatorType.LessThanOrEqual,
filter.RecvTimeTo
);
}
if (filter.IsUnReturn.HasValue && filter.IsUnReturn.Value)
{
sqlBuilder.ConditionConstructor.AddNullCheckCondition(
QueryConditionRelationType.AND, "rmareturn.ReturnTime", QueryConditionOperatorType.IsNull
);
}
else
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"rmareturn.ReturnTime",
DbType.DateTime,
"@ReturnTimeFrom",
QueryConditionOperatorType.MoreThanOrEqual,
filter.ReturnTimeFrom
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"rmareturn.ReturnTime",
DbType.DateTime,
"@ReturnTimeTo",
QueryConditionOperatorType.LessThanOrEqual,
filter.ReturnTimeTo
);
}
if (filter.IsUnRefund.HasValue && filter.IsUnRefund.Value)
{
sqlBuilder.ConditionConstructor.AddNullCheckCondition(
QueryConditionRelationType.AND, "refund.RefundTime", QueryConditionOperatorType.IsNull
);
}
else
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"refund.RefundTime",
DbType.DateTime,
"@RefundTimeFrom",
QueryConditionOperatorType.MoreThanOrEqual,
filter.RefundTimeFrom
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"refund.RefundTime",
DbType.DateTime,
"@RefundTimeTo",
QueryConditionOperatorType.LessThanOrEqual,
filter.RefundTimeTo
);
}
if (filter.IsUnResponse.HasValue && filter.IsUnResponse.Value)
{
sqlBuilder.ConditionConstructor.AddNullCheckCondition(
QueryConditionRelationType.AND, "register.ResponseTime", QueryConditionOperatorType.IsNull
);
}
else
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"register.ResponseTime",
DbType.DateTime,
"@ResponseTimeFrom",
QueryConditionOperatorType.MoreThanOrEqual,
filter.ResponseTimeFrom
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"register.ResponseTime",
DbType.DateTime,
"@ResponseTimeTo",
QueryConditionOperatorType.LessThanOrEqual,
filter.ResponseTimeTo
);
}
if (filter.IsUnOutbound.HasValue && filter.IsUnOutbound.Value)
{
sqlBuilder.ConditionConstructor.AddNullCheckCondition(
QueryConditionRelationType.AND, "outbound.OutTime", QueryConditionOperatorType.IsNull
);
}
else
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"outbound.OutTime",
DbType.DateTime,
"@OutBoundTimeFrom",
QueryConditionOperatorType.MoreThanOrEqual,
filter.OutboundTimeFrom
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"outbound.OutTime",
DbType.DateTime,
"@OutBoundTimeTo",
QueryConditionOperatorType.LessThanOrEqual,
filter.OutboundTimeTo
);
}
if (filter.IsUnCheck.HasValue && filter.IsUnCheck.Value)
{
sqlBuilder.ConditionConstructor.AddNullCheckCondition(
QueryConditionRelationType.AND, "register.CheckTime", QueryConditionOperatorType.IsNull
);
}
else
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"register.CheckTime",
DbType.DateTime,
"@CheckTimeFrom",
QueryConditionOperatorType.MoreThanOrEqual,
filter.CheckTimeFrom
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"register.CheckTime",
DbType.DateTime,
"@CheckTimeTo",
QueryConditionOperatorType.LessThanOrEqual,
filter.CheckTimeTo
);
}
if (filter.IsUnRevert.HasValue && filter.IsUnRevert.Value)
{
sqlBuilder.ConditionConstructor.AddNullCheckCondition(
QueryConditionRelationType.AND, "rmarevert.OutTime", QueryConditionOperatorType.IsNull
);
}
else
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"rmarevert.OutTime",
DbType.DateTime,
"@RevertTimeFrom",
QueryConditionOperatorType.MoreThanOrEqual,
filter.RevertTimeFrom
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"rmarevert.OutTime",
DbType.DateTime,
"@RevertTimeTo",
QueryConditionOperatorType.LessThanOrEqual,
filter.RevertTimeTo
);
}
#endregion
#region VIP condition
if (filter.IsVIP.HasValue)
{
using (GroupCondition group = new GroupCondition(sqlBuilder, QueryConditionRelationType.AND))
{
if (filter.IsVIP == true)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"customer.VIPRank",
DbType.Int32,
"@AutoVIP",
QueryConditionOperatorType.Equal,
2 // VIPRank.AutoVIP
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.OR,
"customer.VIPRank",
DbType.Int32,
"@ManualVIP",
QueryConditionOperatorType.Equal,
4 // VIPRank.ManualVIP
);
}
else
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"customer.VIPRank",
DbType.Int32,
"@AutoNonVIP",
QueryConditionOperatorType.Equal,
1 // VIPRank.AutoNonVIP
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.OR,
"customer.VIPRank",
DbType.Int32,
"@ManualNonVIP",
QueryConditionOperatorType.Equal,
3 // VIPRank.ManualNonVIP
);
}
}
}
#endregion
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"register.CompanyCode",
DbType.String,
"@CompanyCode",
QueryConditionOperatorType.Equal,
filter.CompanyCode
);
#endregion
cmd.CommandText = MakeSqlWithCondition(filter, sqlBuilder.BuildQuerySql());
EnumColumnList enumList = new EnumColumnList();
enumList.Add("OutBoundStatus", typeof(RMAOutBoundStatus));
enumList.Add("RevertStatus", typeof(RMARevertStatus));
enumList.Add("RefundStatus", typeof(RMARefundStatus));
enumList.Add("ReturnStatus", typeof(RMAReturnStatus));
enumList.Add("NewProductStatus", typeof(RMANewProductStatus));
enumList.Add("Status", typeof(RMARequestStatus));
enumList.Add("InvoiceType", typeof(SellersType));
CodeNamePairColumnList codeNameList = new CodeNamePairColumnList();
codeNameList.Add("RMAReason", "RMA", "RMAReason");
codeNameList.Add("SellerOperationInfo", "RMA", "SellerOperationInfo");
var dt = cmd.ExecuteDataTable(enumList, codeNameList);
totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(dt);
}
}
/// <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);
}
}
/// <summary>
/// 查询VendorProdcut
/// </summary>
/// <returns></returns>
public DataTable VendorProductQuery(VendorProductQueryFilter filter, out int TotalCount)
{
CustomDataCommand command = DataCommandManager.
CreateCustomDataCommandFromConfig("External_Query_VendorProduct");
using (DynamicQuerySqlBuilder sb = new DynamicQuerySqlBuilder(command.CommandText, command, HelpDA.ToPagingInfo(filter.PagingInfo), "a.SysNo DESC"))
{
sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.ProductType", DbType.Int32,
"@ProductType", QueryConditionOperatorType.Equal,
0);
sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.Status", DbType.Int32,
"@Status", QueryConditionOperatorType.MoreThanOrEqual,
0);
sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.ManufacturerSysNo", DbType.Int32,
"@ManufacturerSysNo", QueryConditionOperatorType.Equal,
filter.VendorManufacturerSysNo);
command.AddInputParameter("@UserSysNo", DbType.Int32, filter.UserSysNo);
if (filter.C3SysNo.HasValue)
{
sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.C3SysNo", DbType.Int32,
"@C3SysNo", QueryConditionOperatorType.Equal,
filter.C3SysNo);
}
else
{
if (filter.C2SysNo.HasValue)
{
ConditionConstructor subQueryBuilder = sb.ConditionConstructor.AddSubQueryCondition(
QueryConditionRelationType.AND,
"a.C3SysNo",
QueryConditionOperatorType.In,
@"SELECT [Category3Sysno]
FROM [OverseaContentManagement].[dbo].[V_CM_CategoryInfo]
WHERE [Category2Sysno]=@C2SysNo");
command.AddInputParameter("@C2SysNo", DbType.Int32, filter.C2SysNo);
}
}
if (!filter.IsAuto)
{
QueryConditionOperatorType ot = QueryConditionOperatorType.Exist;
if (!filter.IsMapping)
{
ot = QueryConditionOperatorType.NotExist;
}
sb.ConditionConstructor.AddSubQueryCondition(
QueryConditionRelationType.AND,
null,
ot,
@"SELECT TOP 1 1
FROM [IPP3].[dbo].VendorUser_ProductMapping
WHERE
ProductSysNo=a.sysno
AND UserSysNo=@UserSysNo
AND VendorExSysNo=@VendorExSysNo");
}
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"a.MerchantSysNo",
DbType.Int32,
"@MerchantSysNo",
QueryConditionOperatorType.Equal, filter.VendorSysNo);
command.AddInputParameter(
"@VendorManufacturerSysNo",
DbType.Int32,
filter.ManufacturerSysNo);
command.CommandText = sb.BuildQuerySql();
DataTable dt = command.ExecuteDataTable();
TotalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount"));
return(dt);
}
}
