public void BuildWhereTest()
{
FilterInfo filterInfo = new FilterInfo();
List<NormalFilterInfo> normalList = new List<NormalFilterInfo>() {
new NormalFilterInfo(){
ConnectRelation= ConnectRelationEnum.And,
FieldName="UserName",
FieldType= FieldTypeEnum.Text,
NormalWhereRelation= NormalWhereRelationEnum.IsNotNull,
},
new NormalFilterInfo(){
ConnectRelation= ConnectRelationEnum.And,
FieldName="Email",
FieldType= FieldTypeEnum.Text,
NormalWhereRelation= NormalWhereRelationEnum.Like,
WhereValue="123"
}
};
List<CycleFilterInfo> cycleList = new List<CycleFilterInfo>() {
new CycleFilterInfo(){
ConnectRelation= ConnectRelationEnum.And,
CycleRelation= CycleWhereRelationEnum.Day,
MinValue=10,
MaxValue=20,
FieldName="CreatedTime"
},
new CycleFilterInfo(){
ConnectRelation= ConnectRelationEnum.And,
CycleRelation= CycleWhereRelationEnum.Month,
MinValue=6,
MaxValue=12,
FieldName="CreatedTime"
},
};
List<PointInTimeFilterInfo> pointInTimeList = new List<PointInTimeFilterInfo>() {
new PointInTimeFilterInfo(){
ConnectRelation= ConnectRelationEnum.And,
FieldName="CreatedTime",
WhereValue=200,
PointInTimeWhereRelation= PointInTimeWhereRelationEnum.BeforeDay
},
new PointInTimeFilterInfo(){
ConnectRelation= ConnectRelationEnum.And,
FieldName="ModifiedTime",
WhereValue=1,
PointInTimeWhereRelation= PointInTimeWhereRelationEnum.BeforeYear
}
};
filterInfo.CycleFilterInfoList = cycleList;
filterInfo.NormalFilterInfoList = normalList;
filterInfo.PointInTimeFilterInfoList = pointInTimeList;
string sql = _provider.BuildWhere(filterInfo);
}
public void BuildWhereWithOrderTest()
{
//筛选出用户Id大于平均值的,并且密保问题不为空的用户
StatisticsInfo statisticsFilter = new StatisticsInfo()
{
TableName = "UserInfo",
FieldName = "UserId",
StatisticsRelation = StatisticsRelationEnum.Avg
};
string avgSql = _provider.BuildStatistics(statisticsFilter);
var normalList = new List<NormalFilterInfo>() {
new NormalFilterInfo(){
FieldName="UserId",
FieldType= FieldTypeEnum.Number,
ConnectRelation= ConnectRelationEnum.And,
NormalWhereRelation= NormalWhereRelationEnum.MoreThan,
WhereValue=avgSql
},
new NormalFilterInfo(){
FieldName="PwdQuestion",
FieldType= FieldTypeEnum.Text,
ConnectRelation= ConnectRelationEnum.And,
NormalWhereRelation= NormalWhereRelationEnum.IsNotNull,
}
};
List<OrderInfo> orderList = new List<OrderInfo>() {
new OrderInfo(){
FieldName = "UserId",
OrderMode = OrderModeEnum.Asc
},
new OrderInfo(){
FieldName = "Email",
OrderMode = OrderModeEnum.Desc
},
};
FilterInfo filterInfo = new FilterInfo();
filterInfo.NormalFilterInfoList = normalList;
filterInfo.OrderInfoList = orderList;
string sql = _provider.BuildWhere(filterInfo);
}
public virtual string BuildWhere(FilterInfo filterInfo)
{
StringBuilder where = new StringBuilder();
// where 1
where.AppendFormat("{0}{1}{0}{2}", Constants.WhiteSpace, Constants.Where, "1=1");
if (filterInfo != null)
{
if (filterInfo.NormalFilterInfoList != null)
{
where.Append(this.BuildNormalWhere(filterInfo.NormalFilterInfoList));
}
if (filterInfo.CycleFilterInfoList != null)
{
where.Append(this.BuildCycleWhere(filterInfo.CycleFilterInfoList));
}
if (filterInfo.PointInTimeFilterInfoList != null)
{
where.Append(this.BuildPointInTimeWhere(filterInfo.PointInTimeFilterInfoList));
}
if (filterInfo.OrderInfoList != null)
{
where.Append(this.BuildOrder(filterInfo.OrderInfoList));
}
}
return where.ToString();
}
