protected virtual string CompileBetweenCondition <T>(SqlResult ctx, BetweenCondition <T> item)
{
ctx.Bindings.AddRange(new object[] { item.Lower, item.Higher });
var between = item.IsNot ? "NOT BETWEEN" : "BETWEEN";
return(Wrap(item.Column) + $" {between} ? AND ?");
}
protected virtual string CompileBetweenCondition <T>(SqlResult context, BetweenCondition <T> item)
{
string between = item.IsNot ? "NOT BETWEEN" : "BETWEEN";
string lower = Parameter(context, item.Lower);
string higher = Parameter(context, item.Higher);
return(wrapper.Wrap(item.Column) + $" {between} {lower} AND {higher}");
}
protected virtual string CompileBetweenCondition <T>(SqlResult ctx, BetweenCondition <T> item)
{
var between = item.IsNot ? "NOT BETWEEN" : "BETWEEN";
var lower = Parameter(ctx, item.Lower);
var higher = Parameter(ctx, item.Higher);
return(Wrap(item.Column) + $" {between} {lower} AND {higher}");
}
public void ToStringTest()
{
var operand = new FakeToStringCondition("Operando");
var min = new FakeToStringCondition("Minimo");
var max = new FakeToStringCondition("Maximo");
var sut = new BetweenCondition(operand, min, max);
var actual = sut.ToString();
actual.Should().Be("Operando Between Minimo And Maximo");
}
public static void NumericConditions <TProperty>(IDictionary <string, IValueTypeCondition <TProperty> > conditions) where TProperty : struct, IComparable, IComparable <TProperty>, IEquatable <TProperty>
{
conditions["none"] = new ValueTypeNoneCondition <TProperty>("None");
conditions["equal"] = new ValueTypeEqualCondition <TProperty>("Equal");
conditions["notequal"] = new ValueTypeNotEqualCondition <TProperty>("NotEqual");
conditions["between"] = new BetweenCondition <TProperty>("Between");
conditions["lessthan"] = new LessThanCondition <TProperty>("LessThan");
conditions["greaterthan"] = new GreaterThanCondition <TProperty>("GreaterThan");
conditions["lessthanorequal"] = new LessThanOrEqualCondition <TProperty>("LessThanOrEqual");
conditions["greaterthanorequal"] = new GreaterThanOrEqualCondition <TProperty>("GreaterThanOrEqual");
conditions["isnull"] = new ValueTypeIsNullCondition <TProperty>("IsNull");
conditions["isnotnull"] = new ValueTypeIsNotNullCondition <TProperty>("IsNotNull");
}
/// <summary>
/// Between条件解析
/// </summary>
/// <param name="condition">需要解析的条件对象</param>
/// <returns>返回解析后的条件字符串</returns>
protected override string BetweenParse(BetweenCondition condition)
{
StringBuilder conditionStr = new StringBuilder(" ");
string queryColName = condition.SearchColumn.CurrentSearcher.TableName + "." + condition.SearchColumn.ColumnName;
if (string.IsNullOrEmpty(condition.SearchColumn.ConditionString))
{
conditionStr.Append(queryColName);
}
else
{
conditionStr.Append(condition.SearchColumn.ConditionString.Replace(SQLPlaceholder.ColName, queryColName));
}
conditionStr.Append(" BETWEEN ");
if (condition.StartValue is SearchColumn)
{
SearchColumn tmpColumn = (SearchColumn)condition.StartValue;
string startValueColName = tmpColumn.CurrentSearcher.TableName + "." + tmpColumn.ColumnName;
conditionStr.Append(startValueColName);
}
else
{
string startParamName = "P" + Guid.NewGuid().ToString().ToLower().Replace("-", "");
conditionStr.Append(":" + startParamName);
this.ParamCollection.Add(new DBParam(startParamName, condition.StartValue));
}
conditionStr.Append(" AND ");
if (condition.EndValue is SearchColumn)
{
SearchColumn tmpColumn = (SearchColumn)condition.EndValue;
string endValueColName = tmpColumn.CurrentSearcher.TableName + "." + tmpColumn.ColumnName;
conditionStr.Append(endValueColName);
}
else
{
string endParamName = "P" + Guid.NewGuid().ToString().ToLower().Replace("-", "");
conditionStr.Append(":" + endParamName);
this.ParamCollection.Add(new DBParam(endParamName, condition.EndValue));
}
conditionStr.Append(" ");
return conditionStr.ToString();
}
/// <summary>
/// Between条件解析
/// </summary>
/// <param name="condition">需要解析的条件对象</param>
/// <returns>返回解析后的条件字符串</returns>
protected override string BetweenParse(BetweenCondition condition)
{
StringBuilder conditionStr = new StringBuilder(" ");
string queryColName = condition.SearchColumn.CurrentSearcher.TableName + "." + condition.SearchColumn.ColumnName;
if (string.IsNullOrEmpty(condition.SearchColumn.ConditionString))
{
conditionStr.Append(queryColName);
}
else
{
conditionStr.Append(condition.SearchColumn.ConditionString.Replace(SQLPlaceholder.ColName, queryColName));
}
conditionStr.Append(" BETWEEN ");
if (condition.StartValue is SearchColumn)
{
SearchColumn tmpColumn = (SearchColumn)condition.StartValue;
string startValueColName = tmpColumn.CurrentSearcher.TableName + "." + tmpColumn.ColumnName;
conditionStr.Append(startValueColName);
}
else
{
string startParamName = "P" + Guid.NewGuid().ToString().ToLower().Replace("-", "");
conditionStr.Append("@" + startParamName);
this.ParamCollection.Add(new MySqlParameter(startParamName, condition.StartValue));
}
conditionStr.Append(" AND ");
if (condition.EndValue is SearchColumn)
{
SearchColumn tmpColumn = (SearchColumn)condition.EndValue;
string endValueColName = tmpColumn.CurrentSearcher.TableName + "." + tmpColumn.ColumnName;
conditionStr.Append(endValueColName);
}
else
{
string endParamName = "P" + Guid.NewGuid().ToString().ToLower().Replace("-", "");
conditionStr.Append("@" + endParamName);
this.ParamCollection.Add(new MySqlParameter(endParamName, condition.EndValue));
}
conditionStr.Append(" ");
return(conditionStr.ToString());
}
public void Evaluate(object operandValue, object minValue, object maxValue, bool expectedResult)
{
var variables = A.Dummy <IVariableDictionary>();
var operand = A.Fake <IConditionKeyword>(i => i.Strict());
A.CallTo(() => operand.Evaluate(variables)).Returns(operandValue);
var min = A.Fake <IConditionKeyword>(i => i.Strict());
A.CallTo(() => min.Evaluate(variables)).Returns(minValue);
var max = A.Fake <IConditionKeyword>(i => i.Strict());
A.CallTo(() => max.Evaluate(variables)).Returns(maxValue);
var sut = new BetweenCondition(operand, min, max);
var actual = sut.Evaluate(variables);
actual.Should().Be(expectedResult);
}
public ProductFilter()
{
Add("CategoryId", new ValueTypeFilter <Product, int>())
.For(x => x.CategoryId)
.Conditions(conditions => conditions["isin"] = new ValueTypeIsInCondition <int>("IsIn"))
.SetTitle("Categories:")
.SetTemplate("FastFilterWithListBox");
Add("UnitPrice", new ValueTypeFilter <Product, decimal>())
.For(x => x.UnitPrice)
.Conditions(conditions => conditions["between"] = new BetweenCondition <decimal>("Between"))
.SetTitle("UnitPrice:")
.SetValueFormat("{0:G29}")
.SetTemplate("FastNumberFilter");
Add("Name", new ReferenceTypeFilter <Product, string>())
.For(x => x.Name)
.Conditions(conditions => conditions["contains"] = new ContainsCondition("Contains"))
.SetTitle("Name:")
.SetTemplate("FastStringFilter");
}
protected virtual BetweenCondition CreateBetweenCondition(string key, string value, ISearchSettings settings)
{
var lowerAndUpper = value.Split('|');
var lower = lowerAndUpper[0];
var upper = lowerAndUpper[1];
var betweenCondition = new BetweenCondition
{
TargetField = key,
LowerValue = Normalize(lower),
UpperValue = Normalize(upper),
};
return(betweenCondition);
string Normalize(string input)
{
// make datetime value parsable by DateTime.Parse
return(DateTime.TryParseExact(input, settings.DateFormat, CultureInfo.InvariantCulture, DateTimeStyles.None, out var date)
? date.ToUniversalTime().ToString()
: input);
}
}
public SimpleProductFilter(NorthwindDataContext dc)
{
if (dc == null)
{
throw new ArgumentNullException("dc");
}
var categories = dc.Categories.OrderBy(x => x.Name).Select(x =>
new SelectListItem
{
Value = SqlFunctions.StringConvert((double)x.ID).Trim(),
Text = x.Name
});
Add("CategoryId", new ValueTypeFilter <Product, int>())
.For(x => x.CategoryID)
.Conditions(conditions => conditions["isin"] = new ValueTypeIsInCondition <int>("IsIn"))
.SetDictionary(categories)
.SetTitle("Categories:")
.SetTemplate("FastFilterWithListBox");
Add("UnitPrice", new ValueTypeFilter <Product, decimal>())
.For(x => x.UnitPrice)
.Conditions(conditions => conditions["between"] = new BetweenCondition <decimal>("Between"))
.SetTitle("UnitPrice:")
.SetValueFormat("{0:G29}")
.SetTemplate("FastNumberFilter");
Add("Name", new ReferenceTypeFilter <Product, string>())
.For(x => x.Name)
.Conditions(conditions => conditions["contains"] = new ContainsCondition("Contains"))
.SetTitle("Name:")
.SetTemplate("FastStringFilter");
Action = "Grid";
UpdateTargetId = "products";
}
protected virtual string CompileBetweenCondition <T>(BetweenCondition <T> item)
{
var between = item.IsNot ? "NOT BETWEEN" : "BETWEEN";
return(Wrap(item.Column) + $" {between} {Parameter(item.Lower)} AND {Parameter(item.Higher)}");
}
/// <summary>
/// Between条件，用于查询介于两个值之间的值
/// </summary>
/// <param name="relation">条件关系</param>
/// <param name="column">查询字段对象</param>
/// <param name="stratVal">开始值</param>
/// <param name="endVal">结束值</param>
/// <returns>返回Between条件对象</returns>
public static BetweenCondition Between(ConditionRelation relation, SearchColumn column, object stratVal, object endVal)
{
BetweenCondition cond = new BetweenCondition(relation, column, stratVal, endVal);
return cond;
}
/// <summary>
/// Between条件，用于查询介于两个值之间的值
/// </summary>
/// <param name="stratVal">开始值</param>
/// <param name="endVal">结束值</param>
/// <returns>返回Between条件对象</returns>
public static BetweenCondition Between(object stratVal, object endVal)
{
BetweenCondition cond = new BetweenCondition(stratVal, endVal);
return cond;
}
public void BetweenCondition()
{
var betweenCondition = new BetweenCondition <decimal>();
var expr1 = betweenCondition.For <TestSource>(x => x.Price);
Assert.IsNull(expr1);
betweenCondition.Value = new FilterValue <decimal?>
{
LeftValue = 8M,
RightValue = 70M
};
var expr2 = betweenCondition.For <TestSource>(x => x.Price);
var expr2Func = expr2.Compile();
Assert.IsFalse(expr2Func(new TestSource {
Price = 1
}));
Assert.IsTrue(expr2Func(new TestSource {
Price = 8
}));
Assert.IsTrue(expr2Func(new TestSource {
Price = 15
}));
betweenCondition.Value = new FilterValue <decimal?>
{
LeftValue = 8M
};
var expr3 = betweenCondition.For <TestSource>(x => x.Price);
var expr3Func = expr3.Compile();
Assert.IsTrue(expr3Func(new TestSource {
Price = 9
}));
Assert.IsTrue(expr3Func(new TestSource {
Price = 346
}));
Assert.IsFalse(expr3Func(new TestSource {
Price = 0
}));
betweenCondition.Value = new FilterValue <decimal?>
{
RightValue = 70M
};
var expr4 = betweenCondition.For <TestSource>(x => x.Price);
var expr4Func = expr4.Compile();
Assert.IsTrue(expr4Func(new TestSource {
Price = 9
}));
Assert.IsTrue(expr4Func(new TestSource {
Price = -15
}));
Assert.IsFalse(expr4Func(new TestSource {
Price = 90
}));
}
/// <summary>
/// Between条件，用于查询介于两个值之间的值
/// </summary>
/// <param name="relation">条件关系</param>
/// <param name="column">查询字段对象</param>
/// <param name="stratVal">开始值</param>
/// <param name="endVal">结束值</param>
/// <returns>返回Between条件对象</returns>
public static BetweenCondition Between(ConditionRelation relation, SearchColumn column, object stratVal, object endVal)
{
BetweenCondition cond = new BetweenCondition(relation, column, stratVal, endVal);
return(cond);
}
/// <summary>
/// Between条件，用于查询介于两个值之间的值
/// </summary>
/// <param name="stratVal">开始值</param>
/// <param name="endVal">结束值</param>
/// <returns>返回Between条件对象</returns>
public static BetweenCondition Between(object stratVal, object endVal)
{
BetweenCondition cond = new BetweenCondition(stratVal, endVal);
return(cond);
}
