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); } }