/// <summary>
/// 解释Condition条件
/// </summary>
/// <param name="scope">条件</param>
/// <param name="list"></param>
/// <param name="entityType"></param>
/// <param name="paramName"></param>
/// <param name="type"></param>
/// <param name="lstIndex"></param>
public static string Condition(Scope scope, ParamList list, Type entityType, string paramName, DbType type, int lstIndex)
{
DBInfo db = EntityInfoManager.GetEntityHandle(entityType).DBInfo;
string paramVal = db.CurrentDbAdapter.FormatValueName(DataAccessCommon.FormatParam(paramName, lstIndex));
string paramKey = db.CurrentDbAdapter.FormatParamKeyName(DataAccessCommon.FormatParam(paramName, lstIndex));
StringBuilder sql = new StringBuilder(500);
ScopeType ctype = scope.ScopeType;
string connectString = DataAccessCommon.GetConnectString(scope);
BQLCondition fhandle = scope.Value1 as BQLCondition;
if (!CommonMethods.IsNull(fhandle))
{
KeyWordInfomation info = new KeyWordInfomation();
info.Infos = new Buffalo.DB.BQLCommon.BQLInfos();
info.DBInfo = db;
info.ParamList = list;
sql.Append(" ");
sql.Append(connectString);
sql.Append(" (" + fhandle.DisplayValue(info) + ")");
}
return(sql.ToString());
}
/// <summary>
/// 直接查询数据库视图
/// </summary>
/// <param name="table">表</param>
/// <param name="lstScope">条件</param>
/// <param name="vParams">字段列表</param>
/// <param name="lstSort">排序类型</param>
/// <param name="objPage">分页对象</param>
/// <returns></returns>
public DataSet SelectTable(BQLOtherTableHandle table, ScopeList lstScope)
{
List <BQLParamHandle> lstParams = GetParam(table, lstScope);
List <BQLParamHandle> lstOrders = new List <BQLParamHandle>();
BQLParamHandle order = null;
foreach (Sort objSort in lstScope.OrderBy)
{
order = table[objSort.PropertyName];
if (objSort.SortType == SortType.ASC)
{
order = order.ASC;
}
else
{
order = order.DESC;
}
lstOrders.Add(order);
}
BQLCondition where = BQLCondition.TrueValue;
where = FillCondition(where, table, lstScope, null);
BQLQuery bql = BQL.Select(lstParams.ToArray()).From(table).Where(where).OrderBy(lstOrders.ToArray());
if (lstScope.HasPage)
{
using (BatchAction ba = _oper.StarBatchAction())
{
return(QueryDataSet(bql, null, lstScope.PageContent, lstScope.UseCache));
}
}
return(QueryDataSet(bql, null, lstScope.UseCache));
}
/// <summary>
/// Not
/// </summary>
/// <param name="value">Not</param>
/// <returns></returns>
public static BQLComparItem Not(BQLCondition handle)
{
BQLComparItem fHandle = new BQLComparItem(FunctionManager.DoNot, new BQLValueItem[] { handle });
fHandle.PriorityLevel = 4;
return(fHandle);
}
/// <summary>
/// LeftJoin关键字项
/// </summary>
/// <param name="prmsHandle">字段集合</param>
/// <param name="previous">上一个关键字</param>
internal KeyWordJoinItem(BQLTableHandle joinTable, BQLCondition condition, string keyWord, BQLQuery previous)
: base(new BQLTableHandle[] { joinTable }, previous)
{
//this.joinTable = joinTable;
this._condition = condition;
this._keyWord = keyWord;
}
/// <summary>
/// 关联表
/// </summary>
/// <param name="from"></param>
/// <param name="sourceTable"></param>
/// <param name="targetTable"></param>
/// <returns></returns>
private KeyWordFromItem InnerTable(KeyWordFromItem from, AliasTableMapping sourceTable)
{
KeyWordFromItem inner = from;
string sTableName = sourceTable.TableInfo.GetAliasName();
foreach (KeyValuePair <string, AliasTableMapping> tablePair in sourceTable.ChildTables)
{
AliasTableMapping tTable = tablePair.Value;
string tTableName = tTable.TableInfo.GetAliasName();
EntityMappingInfo minfo = tTable.MappingInfo;
BQLCondition fhandle = null;
//if (minfo.IsPrimary)
//{
fhandle = BQL.Tables[sTableName][minfo.SourceProperty.ParamName] == BQL.Tables[tTableName][minfo.TargetProperty.ParamName];
//}
//else
//{
// fhandle = BQLDbBase.BQL.Tables[sTableName][minfo.SourceProperty.ParamName] == BQLDbBase.BQL.Tables[tTableName][minfo.PrimaryKey];
//}
inner = inner.LeftJoin(tablePair.Value.TableInfo, fhandle);
if (tTable.ChildTables.Count > 0)
{
inner = InnerTable(inner, tTable);
}
}
return(inner);
}
/// <summary>
/// 获取唯一
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="condition">条件</param>
/// <returns></returns>
public static T GetUnique <T>(BQLCondition condition) where T : EntityBase, new()
{
ScopeList lstScope = new ScopeList();
if (!CommonMethods.IsNull(condition))
{
lstScope.Add(condition);
}
return(GetUnique <T>(lstScope));
}
/// <summary>
/// 查询数目
/// </summary>
/// <param name="condition">条件</param>
/// <returns></returns>
public static long SelectCount <T>(BQLCondition condition) where T : EntityBase, new()
{
ScopeList lstScope = new ScopeList();
if (!CommonMethods.IsNull(condition))
{
lstScope.Add(condition);
}
return(SelectCount <T>(lstScope));
}
/// <summary>
/// 添加新的范围
/// </summary>
/// <param name="property">属性</param>
/// <param name="minValue">最小值</param>
/// <param name="maxValue">最大值</param>
/// <param name="conntype">连接类型</param>
/// <returns>返回是否添加成功</returns>
public bool AddBetween(BQLEntityParamHandle property, object minValue, object maxValue, ConnectType conntype)
{
if (IsInnerTable(property))
{
BQLCondition where = property.Between(minValue, maxValue);
return(Add(where, conntype));
}
return(AddBetween(property.PInfo.PropertyName, minValue, maxValue, conntype));
}
public static DataSet Search(string[] searchs, PageContent objPage)
{
ScopeList lstScope = new ScopeList();
lstScope.PageContent = objPage;
double per = Math.Pow(2, searchs.Length);
lstScope.ShowProperty.Add(Management.SampleInfo.SampleName.As("SampleName"));
lstScope.ShowProperty.Add(Management.SampleInfo.SamplingTerrace.As("SamplingTerrace"));
lstScope.ShowProperty.Add(Management.SampleInfo.SampleNumber.As("SampleNumber"));
BQLValueItem perValue = BQLValueItem.ToValueItem(0);
BQLCondition nameCon = BQLCondition.FalseValue;
foreach (string search in searchs)
{
nameCon = nameCon | Management.SampleInfo.SampleName.Like(search);
perValue = perValue + BQL.Case().When(BQL.ToParam("SampleName").IndexOf(search, 0) <= 0).Then(0).Else(1).End *per;
per /= 2;
}
lstScope.ShowProperty.Add(perValue.As("per1"));
lstScope.Add(nameCon);
lstScope.OrderBy.Add(perValue.As("").DESC);
per = Math.Pow(2, searchs.Length);
perValue = BQLValueItem.ToValueItem(0);
nameCon = BQLCondition.FalseValue;
foreach (string search in searchs)
{
nameCon = nameCon | Management.SampleInfo.SamplingTerrace.Like(search);
perValue = perValue + BQL.Case().When(BQL.ToParam("SamplingTerrace").IndexOf(search, 0) <= 0).Then(0).Else(1).End *per;
per /= 2;
}
lstScope.ShowProperty.Add(perValue.As("per2"));
lstScope.Add(nameCon, ConnectType.OR);
lstScope.OrderBy.Add(perValue.As("").DESC);
per = Math.Pow(2, searchs.Length);
perValue = BQLValueItem.ToValueItem(0);
nameCon = BQLCondition.FalseValue;
foreach (string search in searchs)
{
nameCon = nameCon | Management.SampleInfo.SampleNumber.Like(search);
perValue = perValue + BQL.Case().When(BQL.ToParam("SampleNumber").IndexOf(search, 0) <= 0).Then(0).Else(1).End *per;
per /= 2;
}
lstScope.ShowProperty.Add(perValue.As("per3"));
lstScope.Add(nameCon, ConnectType.OR);
lstScope.OrderBy.Add(perValue.As("").DESC);
//lstScope.OrderBy.Add(BQL.ToParam("per1").DESC);
//lstScope.OrderBy.Add(BQL.ToParam("per2").DESC);
//lstScope.OrderBy.Add(BQL.ToParam("per3").DESC);
return(GetContext().Select(lstScope));
}
/// <summary>
/// 添加一个项
/// </summary>
/// <param name="prm">查询子项</param>
/// <param name="filter">筛选条件</param>
public void Add(BQLEntityTableHandle prm, BQLCondition filterCondition)
{
ScopeList filter = new ScopeList();
filter.Add(filterCondition);
ShowChildItem item = new ShowChildItem();
item.ChildItem = prm;
item.FilterScope = filter;
base.Add(item);
}
/// <summary>
/// 填充信息
/// </summary>
/// <param name="condition"></param>
/// <param name="table"></param>
/// <param name="lstScope"></param>
/// <param name="entityType"></param>
public BQLCondition FillCondition(BQLCondition condition, BQLTableHandle table, ScopeList lstScope, Type entityType)
{
BQLCondition ret;
EntityInfoHandle entityInfo = null;
if (entityType != null)
{
entityInfo = EntityInfoManager.GetEntityHandle(entityType);
}
ret = BQLConditionScope.FillCondition(condition, table, lstScope, entityInfo);
return(ret);
}
/// <summary>
/// 添加新的EndWith条件
/// </summary>
/// <param name="property">属性名</param>
/// <param name="value">值</param>
/// <param name="conntype">连接类型</param>
/// <returns>返回是否添加成功</returns>
public bool AddEndWith(BQLEntityParamHandle property, object value, ConnectType conntype)
{
if (IsInnerTable(property))
{
BQLCondition where = property.EndWith(value);
return(Add(where, conntype));
}
else
{
return(AddEndWith(property.PInfo.PropertyName, value, conntype));
}
}
/// <summary>
/// 添加新的NotIn条件(如果集合为空，则返回1=1)
/// </summary>
/// <param name="property">属性名</param>
/// <param name="valuesCollection">值集合</param>
/// <param name="conntype">连接类型</param>
/// <returns>返回是否添加成功</returns>
public bool AddNotIn(BQLEntityParamHandle property, IEnumerable valuesCollection, ConnectType conntype)
{
if (IsInnerTable(property))
{
BQLCondition where = property.NotIn(valuesCollection);
return(Add(where, conntype));
}
else
{
return(AddNotIn(property.PInfo.PropertyName, valuesCollection, conntype));
}
}
/// <summary>
/// 添加新的范围
/// </summary>
/// <param name="property">属性</param>
/// <param name="value">值</param>
/// <param name="conntype">连接类型</param>
/// <returns>返回是否添加成功</returns>
public bool AddContains(BQLEntityParamHandle property, object value, ConnectType conntype)
{
if (IsInnerTable(property))
{
BQLCondition where = property.Contains(value);
return(Add(where, conntype));
}
else
{
return(AddContains(property, value, conntype));
}
}
/// <summary>
/// 查询集合
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="condition">条件</param>
/// <param name="pager">分页条件</param>
/// <returns></returns>
public static List <T> SelectList <T>(BQLCondition condition, PageContent pager) where T : EntityBase, new()
{
ScopeList lstScope = new ScopeList();
if (!CommonMethods.IsNull(condition))
{
lstScope.Add(condition);
}
if (pager != null)
{
lstScope.PageContent = pager;
}
return(SelectList <T>(lstScope));
}
/// <summary>
/// 获取唯一
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="condition">条件</param>
/// <returns></returns>
public static T GetUnique <T>(BQLCondition condition) where T : EntityBase, new()
{
string name = typeof(T).FullName;
BusinessModelBase <T> bo = DataAccessLoader.GetBoInstance(name) as BusinessModelBase <T>;
if (bo == null)
{
throw new MissingMemberException("找不到:" + name + " 对应的业务类");
}
ScopeList lstScope = new ScopeList();
if (!CommonMethods.IsNull(condition))
{
lstScope.Add(condition);
}
return(bo.GetUnique(lstScope));
}
/// <summary>
/// 填充查询条件并返回条件的SQL语句( and 开头)
/// </summary>
/// <param name="lstParam">参数列表</param>
/// <param name="lstScope">范围查询集合</param>
/// <returns></returns>
internal static BQLCondition FillCondition(BQLCondition condition, BQLTableHandle table, ScopeList lstScope, EntityInfoHandle entityInfo)
{
BQLCondition ret = condition;
if (lstScope == null)
{
return(ret);
}
BQLCondition curHandle = null;
for (int i = 0; i < lstScope.Count; i++)
{
Scope objScope = lstScope[i];
EntityPropertyInfo info = null;
if (entityInfo != null)
{
if (objScope.ScopeType == ScopeType.Condition)
{
curHandle = objScope.Value1 as BQLCondition;
}
else
{
//info = entityInfo.PropertyInfo[objScope.PropertyName];
curHandle = FormatScorp(objScope, DbType.Object, objScope.PropertyName, ret, table, entityInfo);
}
}
else
{
curHandle = FormatScorp(objScope, DbType.Object, objScope.PropertyName, ret, table, entityInfo);
}
if (!Buffalo.Kernel.CommonMethods.IsNull(curHandle))
{
if (objScope.ConnectType == ConnectType.And)
{
ret = ret & curHandle;
}
else
{
ret = ret | curHandle;
}
}
}
return(ret);
}
/// <summary>
/// 查询表
/// </summary>
/// <typeparam name="E"></typeparam>
/// <param name="lstScope">条件</param>
/// <returns></returns>
public E GetUnique <E>(ScopeList lstScope)
where E : EntityBase, new()
{
Type eType = typeof(E);
BQLEntityTableHandle table = _oper.DBInfo.FindTable(eType);
if (CommonMethods.IsNull(table))
{
_oper.DBInfo.ThrowNotFondTable(eType);
}
List <BQLParamHandle> lstParams = GetParam(table, lstScope);
BQLCondition where = BQLCondition.TrueValue;
where = FillCondition(where, table, lstScope);
BQLQuery bql = BQL.Select(lstParams.ToArray())
.From(table)
.Where(where)
.OrderBy(GetSort(lstScope.OrderBy, table));
return(GetUnique <E>(bql, lstScope.UseCache));
}
/// <summary>
/// 获取范围表对应的BQL
/// </summary>
/// <param name="lstScope"></param>
/// <param name="table"></param>
/// <returns></returns>
private BQLQuery GetSelectSql(ScopeList lstScope, BQLEntityTableHandle table)
{
List <BQLParamHandle> lstParams = GetParam(table, lstScope);
BQLCondition where = BQLCondition.TrueValue;
where = FillCondition(where, table, lstScope);
BQLQuery bql = BQL.Select(lstParams.ToArray())
.From(table)
.Where(where);
if (lstScope.GroupBy.Count > 0)
{
bql = new KeyWordGroupByItem(lstScope.GroupBy, bql);
}
if (lstScope.OrderBy != null && lstScope.OrderBy.Count > 0)
{
bql = new KeyWordOrderByItem(GetSort(lstScope.OrderBy, table), bql);
}
return(bql);
}
/// <summary>
/// 查询是否存在符合条件的记录
/// </summary>
/// <param name="BQL">sql语句</param>
/// <returns></returns>
public bool ExistsRecord <E>(ScopeList lstScope)
where E : EntityBase, new()
{
Type eType = typeof(E);
BQLEntityTableHandle table = _oper.DBInfo.FindTable(eType);
if (CommonMethods.IsNull(table))
{
_oper.DBInfo.ThrowNotFondTable(eType);
}
List <BQLParamHandle> lstParams = new List <BQLParamHandle>();
lstParams.Add(table[table.GetEntityInfo().PrimaryProperty[0].PropertyName]);
BQLCondition where = BQLCondition.TrueValue;
where = FillCondition(where, table, lstScope);
BQLQuery bql = BQL.Select(lstParams.ToArray())
.From(table)
.Where(where)
.OrderBy(GetSort(lstScope.OrderBy, table));
return(ExistsRecord <E>(bql, lstScope.UseCache));
}
/// <summary>
/// 查询总条数
/// </summary>
/// <param name="lstScope"></param>
/// <returns></returns>
public virtual long SelectCount <E>(ScopeList lstScope)
{
long ret = 0;
Type eType = typeof(E);
TableAliasNameManager aliasManager = new TableAliasNameManager(new BQLEntityTableHandle(EntityInfoManager.GetEntityHandle(typeof(E))));
BQLEntityTableHandle table = _oper.DBInfo.FindTable(eType);
if (CommonMethods.IsNull(table))
{
_oper.DBInfo.ThrowNotFondTable(eType);
}
BQLCondition where = BQLCondition.TrueValue;
where = FillCondition(where, table, lstScope);
BQLQuery bql = BQL.Select(BQL.Count())
.From(table)
.Where(where);
//if(lstScope.GroupBy
AbsCondition con = BQLKeyWordManager.ToCondition(bql, _oper.DBInfo, aliasManager, true);
Dictionary <string, bool> cacheTables = null;
if (lstScope.UseCache)
{
cacheTables = con.CacheTables;
}
using (IDataReader reader = _oper.Query(con.GetSql(lstScope.UseCache), con.DbParamList, cacheTables))
{
if (reader.Read())
{
ret = Convert.ToInt64(reader[0]);
}
}
return(ret);
}
/// <summary>
/// 解释Scope
/// </summary>
/// <param name="scope"></param>
/// <param name="dbType"></param>
/// <param name="paramName"></param>
/// <param name="handle"></param>
/// <param name="table"></param>
/// <param name="entityInfo"></param>
/// <returns></returns>
internal static BQLCondition DoScope(Scope scope, DbType dbType, string paramName, BQLCondition handle, BQLTableHandle table, EntityInfoHandle entityInfo)
{
ScopeList lstInnerScope = scope.Value1 as ScopeList;
handle = BQLConditionScope.FillCondition(handle, table, lstInnerScope, entityInfo);
return(handle);
}
/// <summary>
/// 解释Condition
/// </summary>
/// <param name="scope"></param>
/// <param name="dbType"></param>
/// <param name="paramName"></param>
/// <param name="handle"></param>
/// <param name="table"></param>
/// <param name="entityInfo"></param>
/// <returns></returns>
internal static BQLCondition Condition(Scope scope, DbType dbType, string paramName, BQLCondition handle, BQLTableHandle table, EntityInfoHandle entityInfo)
{
BQLCondition fhandle = scope.Value1 as BQLCondition;
return(fhandle);
}
/// <summary>
/// 解释NotEqual
/// </summary>
/// <param name="scope"></param>
/// <param name="dbType"></param>
/// <param name="paramName"></param>
/// <param name="handle"></param>
/// <param name="table"></param>
/// <param name="entityInfo"></param>
/// <returns></returns>
internal static BQLCondition NotEqual(Scope scope, DbType dbType, string paramName, BQLCondition handle, BQLTableHandle table, EntityInfoHandle entityInfo)
{
BQLValueTypeItem cvalue1 = new BQLValueTypeItem(scope.Value1);
return(table[paramName, dbType] != cvalue1);
}
/// <summary>
/// 填充信息
/// </summary>
/// <param name="condition"></param>
/// <param name="table"></param>
/// <param name="lstScope"></param>
/// <param name="entityType"></param>
public BQLCondition FillCondition(BQLCondition condition, BQLEntityTableHandle table, ScopeList lstScope)
{
return(BQLConditionScope.FillCondition(condition, table, lstScope, table.GetEntityInfo()));
}
private static BQLCondition FormatScorp(Scope scope, DbType dbType, string pro, BQLCondition handle, BQLTableHandle table, EntityInfoHandle entityInfo)
{
ScopeType ctype = scope.ScopeType;
BQLScopeExplainer delFun = BQLExplainScope.GetExplainer(scope);
if (delFun != null)
{
//if (dbType == DbType.Object && scope.Value1 != null)
//{
// dbType = DefaultType.ToDbType(scope.Value1.GetType());
//}
handle = delFun(scope, dbType, pro, handle, table, entityInfo);
}
return(handle);
}
/// <summary>
/// 解释Between
/// </summary>
/// <param name="scope"></param>
/// <param name="dbType"></param>
/// <param name="paramName"></param>
/// <param name="handle"></param>
/// <param name="table"></param>
/// <param name="entityInfo"></param>
/// <returns></returns>
internal static BQLCondition Between(Scope scope, DbType dbType, string paramName, BQLCondition handle, BQLTableHandle table, EntityInfoHandle entityInfo)
{
BQLValueTypeItem cvalue1 = new BQLValueTypeItem(scope.Value1);
BQLValueTypeItem cvalue2 = null;
if (scope.Value2 != null)
{
cvalue2 = new BQLValueTypeItem(scope.Value2);
}
return(table[paramName, dbType].Between(cvalue1, cvalue2));
}
/// <summary>
/// 查询条件
/// </summary>
/// <param name="condition">条件</param>
/// <returns></returns>
public KeyWordWhereItem Where(BQLCondition condition)
{
KeyWordWhereItem item = new KeyWordWhereItem(condition, this);
return(item);
}
/// <summary>
/// 解释IN
/// </summary>
/// <param name="scope"></param>
/// <param name="dbType"></param>
/// <param name="paramName"></param>
/// <param name="handle"></param>
/// <param name="table"></param>
/// <param name="entityInfo"></param>
/// <returns></returns>
internal static BQLCondition IN(Scope scope, DbType dbType, string paramName, BQLCondition handle, BQLTableHandle table, EntityInfoHandle entityInfo)
{
BQLValueTypeItem cvalue1 = new BQLValueTypeItem(scope.Value1);
return(table[paramName, dbType].In(scope.Value1 as IEnumerable));
}
/// <summary>
/// 查询条件
/// </summary>
/// <param name="condition">条件</param>
/// <returns></returns>
public KeyWordHavingItem Having(BQLCondition condition)
{
KeyWordHavingItem item = new KeyWordHavingItem(condition, this);
return item;
}