public object MaxInTree(IConnectionProvider conn, IEntity rootEntity, IDbColumn column, DbRelation recursiveRelation, SearchCondition leafFilter, int beginAtLevel, int endAtLevel)
{
EnsureRecursiveRelation(recursiveRelation);
EnsureValidBeginAndEndLevels(beginAtLevel, endAtLevel);
RecursiveStatement cte = new RecursiveStatement(rootEntity, recursiveRelation);
cte.Select = new SelectStatement(rootEntity.Table, AggregateFunctionFactory.Max(column, null));
cte.Select.Where = CreateWhere(rootEntity, cte, leafFilter, beginAtLevel, endAtLevel);
DataTable result = cte.ExecuteSelect(conn);
return result.Rows[0][0];
}
public int CountTree(IConnectionProvider conn, IEntity rootEntity, DbRelation recursiveRelation, SearchCondition leafFilter, int beginAtLevel, int endAtLevel)
{
EnsureRecursiveRelation(recursiveRelation);
EnsureValidBeginAndEndLevels(beginAtLevel, endAtLevel);
RecursiveStatement cte = new RecursiveStatement(rootEntity, recursiveRelation);
cte.Select = new SelectStatement(rootEntity.Table, AggregateFunctionFactory.Count(null));
cte.Select.Where = CreateWhere(rootEntity, cte, leafFilter, beginAtLevel, endAtLevel);
DataTable result = cte.ExecuteSelect(conn);
int count = Convert.ToInt32(result.Rows[0][0], CultureInfo.InvariantCulture);
return count;
}
public int UpdateTree(IConnectionProvider conn, IEntity rootEntity, DbRelation recursiveRelation, UpdateList setExpressions, SearchCondition leafFilter, int beginAtLevel, int endAtLevel)
{
EnsureRecursiveRelation(recursiveRelation);
EnsureValidBeginAndEndLevels(beginAtLevel, endAtLevel);
// update target set ... where exists ... and ...
RecursiveStatement cte = new RecursiveStatement(rootEntity, recursiveRelation);
cte.Update = new UpdateStatement(rootEntity.Table);
cte.Update.UpdateList = setExpressions;
cte.Update.Where = CreateWhere(rootEntity, cte, leafFilter, beginAtLevel, endAtLevel);
int rowsAffected = cte.ExecuteUpdate(conn);
return rowsAffected;
}
public int DetermineTreeDepth(IConnectionProvider conn, IEntity rootEntity, DbRelation recursiveRelation)
{
EnsureRecursiveRelation(recursiveRelation);
// select max(level) as maxLevelIndex from hierarchy
RecursiveStatement cte = new RecursiveStatement(rootEntity, recursiveRelation);
cte.Select = new SelectStatement(cte.Hierarchy, AggregateFunctionFactory.Max(cte.Hierarchy.Level, null));
DataTable results = cte.ExecuteSelect(conn);
int highestLevel = (results.Rows[0][0] != DBNull.Value) ? Convert.ToInt32(results.Rows[0][0], CultureInfo.InvariantCulture) : 0;
int depth = highestLevel + 1;
return depth;
}
public DataTable SelectTree(IConnectionProvider conn, IEntity rootEntity, DbRelation recursiveRelation, SearchCondition leafFilter, OrderByClause sorter, bool sortOnDb, int beginAtLevel, int endAtLevel)
{
EnsureRecursiveRelation(recursiveRelation);
EnsureValidBeginAndEndLevels(beginAtLevel, endAtLevel);
// select target.* from target where exists ... and ...
RecursiveStatement cte = new RecursiveStatement(rootEntity, recursiveRelation);
cte.Select = new SelectStatement(rootEntity.Table, rootEntity.Table.Columns);
cte.Select.Where = CreateWhere(rootEntity, cte, leafFilter, beginAtLevel, endAtLevel);
if (sortOnDb && !OrderByClause.IsNullOrEmpty(sorter))
cte.Select.OrderBy = sorter;
DataTable data = cte.ExecuteSelect(conn);
if (!sortOnDb && !OrderByClause.IsNullOrEmpty(sorter))
data = GenericHierarchicalQueryExecutor.SortDataTable(data, sorter);
return data;
}
private static SearchCondition CreateWhere(IEntity rootEntity, RecursiveStatement cte, SearchCondition leafFilter, int beginAtLevel, int endAtLevel)
{
// where exists (select hierarchy.id from hierarchy where hierarchy.id = target.id and level betwen beginAtLevel and endAtLevel)
// and status = ...
SelectStatement existsInHierarchy = new SelectStatement(cte.Hierarchy, cte.GetHierarchyPK());
foreach (IDbColumn targetPkField in rootEntity.Table.PrimaryKey)
{
IDbColumn hierarchyPkField = cte.Hierarchy.Columns.GetByColumnName(targetPkField.ColumnName);
existsInHierarchy.Where.Add(PredicateFactory.Compare(hierarchyPkField, "=", targetPkField));
}
bool hasLevelConstraints = (beginAtLevel > 0) || (endAtLevel < int.MaxValue);
if (hasLevelConstraints)
existsInHierarchy.Where.Add(PredicateFactory.Between(cte.Hierarchy.Level, beginAtLevel, endAtLevel));
SearchCondition where = (SearchCondition)PredicateFactory.Exists(existsInHierarchy);
if (leafFilter != null && !leafFilter.IsEmpty)
where.Add(leafFilter);
return where;
}