public static Dictionary <Guid, KeyValuePair <Guid, string> > GetChildProjects(Guid projectId, UserConnection userConnection)
{
Query projectSelect =
new Terrasoft.Core.DB.Select(userConnection)
.Column("Id").As("Id")
.Column("Name").As("Name")
.Column("ParentProjectId").As("ParentProjectId")
.From("Project");
projectSelect.Parameters.Add(new QueryParameter("StartingUnitId", null));
projectSelect.Parameters.Add(new QueryParameter("MaxDepth", null));
projectSelect.InitializeParameters();
HierarchicalSelectOptions options = new HierarchicalSelectOptions()
{
PrimaryColumnName = "Id",
ParentColumnName = "ParentProjectId",
SelectType = HierarchicalSelectType.Children,
MaxDepthParameter = projectSelect.Parameters.GetByName("MaxDepth")
};
QueryCondition startingCondition = options.StartingPrimaryColumnCondition;
startingCondition.LeftExpression = new QueryColumnExpression("Id");
startingCondition.IsEqual(Column.Parameter(projectId, "ParentProjectId", Terrasoft.Common.ParameterDirection.Input));
string sqlText = userConnection.DBEngine.GetQuerySqlText(projectSelect as Select, options);
var parameters = new QueryParameterCollection();
parameters.Add(new QueryParameter()
{
Name = "ParentProjectId",
Value = projectId
});
var list = new Dictionary <Guid, KeyValuePair <Guid, string> >();
using (var dbExecutor = userConnection.EnsureDBConnection()) {
using (var reader = dbExecutor.ExecuteReader(sqlText, parameters)) {
while (reader.Read())
{
if (!reader.IsDBNull(0))
{
var parentId = Guid.Empty;
if (!reader.IsDBNull(2))
{
parentId = userConnection.DBTypeConverter.DBValueToGuid(reader[2]);
}
var id = userConnection.DBTypeConverter.DBValueToGuid(reader[0]);
list.Add(id, new KeyValuePair <Guid, string>(parentId, reader[1].ToString()));
}
}
}
}
return(list);
}
private static System.Collections.ObjectModel.Collection <KeyValuePair <Guid, string> > GetParentProjects(Guid projectId, UserConnection userConnection)
{
Query projectSelect =
new Terrasoft.Core.DB.Select(userConnection)
.Column("Id").As("Id")
.Column("Name").As("Name")
.Column("ParentProjectId").As("ParentProjectId")
.From("Project");
projectSelect.Parameters.Add(new QueryParameter("StartingUnitId", null));
projectSelect.Parameters.Add(new QueryParameter("MaxDepth", null));
projectSelect.InitializeParameters();
HierarchicalSelectOptions options = new HierarchicalSelectOptions()
{
PrimaryColumnName = "Id",
ParentColumnName = "ParentProjectId",
SelectType = HierarchicalSelectType.Parents,
MaxDepthParameter = projectSelect.Parameters.GetByName("MaxDepth")
};
QueryCondition startingCondition = options.StartingPrimaryColumnCondition;
startingCondition.LeftExpression = new QueryColumnExpression("Id");
startingCondition.IsEqual(Column.Parameter(projectId, "ParentProjectId", Terrasoft.Common.ParameterDirection.Input));
string sqlText = userConnection.DBEngine.GetQuerySqlText(projectSelect as Select, options);
var parameters = new QueryParameterCollection();
parameters.Add(new QueryParameter()
{
Name = "ParentProjectId",
Value = projectId
});
var list = new System.Collections.ObjectModel.Collection <KeyValuePair <Guid, string> >();
using (var dbExecutor = userConnection.EnsureDBConnection()) {
using (var reader = dbExecutor.ExecuteReader(sqlText, parameters)) {
while (reader.Read())
{
Guid id = Guid.Empty;
if (Guid.TryParse(reader[0].ToString(), out id))
{
list.Add(new KeyValuePair <Guid, string>(id, reader[1].ToString()));
}
}
}
}
return(list);
}
/// <summary>
/// ##### ## ########### ############## #### ###### ###### # ########## ############## #### ######## ###############
/// (###########, #############, ############ ### #######) ### ############## ##### ####### ##########.
/// </summary>
/// <param name="parentRoleId">############# ############### ### ############## ####.</param>
/// <returns>###### ############### #### ########### ############### #####, ####### ##########.</returns>
public object[] GetChildAdminUnits(string parentRoleId)
{
Query adminUnitSelect =
new Terrasoft.Core.DB.Select(UserConnection).Column("Id")
.As("Id")
.Column("Name")
.As("Name")
.Column("ParentRoleId")
.As("ParentRoleId")
.From("SysAdminUnit")
.Where("SysAdminUnitTypeValue")
.In(Column.Parameter(Terrasoft.Core.DB.SysAdminUnitType.Organisation, "AdminUnitType_Organisation", Common.ParameterDirection.Input),
Column.Parameter(Terrasoft.Core.DB.SysAdminUnitType.Department, "AdminUnitType_Department", Common.ParameterDirection.Input),
Column.Parameter(Terrasoft.Core.DB.SysAdminUnitType.Manager, "AdminUnitType_Manager", Common.ParameterDirection.Input),
Column.Parameter(Terrasoft.Core.DB.SysAdminUnitType.Team, "AdminUnitType_Team", Common.ParameterDirection.Input),
Column.Parameter(6, "AdminUnitType_FunctionalRole", Common.ParameterDirection.Input));
adminUnitSelect.Parameters.Add(new QueryParameter("StartingUnitId", null));
adminUnitSelect.Parameters.Add(new QueryParameter("MaxDepth", null));
adminUnitSelect.InitializeParameters();
HierarchicalSelectOptions options = new HierarchicalSelectOptions()
{
PrimaryColumnName = "Id",
ParentColumnName = "ParentRoleId",
SelectType = HierarchicalSelectType.Children,
MaxDepthParameter = adminUnitSelect.Parameters.GetByName("MaxDepth")
};
QueryCondition startingCondition = options.StartingPrimaryColumnCondition;
startingCondition.LeftExpression = new QueryColumnExpression("ParentRoleId");
startingCondition.IsEqual(Column.Parameter(parentRoleId, "FolderId", Common.ParameterDirection.Input));
string sqlText = UserConnection.DBEngine.GetQuerySqlText(adminUnitSelect as Select, options);
var parameters = new QueryParameterCollection();
parameters.Add(new QueryParameter()
{
Name = "AdminUnitType_Organisation",
Value = Terrasoft.Core.DB.SysAdminUnitType.Organisation,
ValueTypeName = "Integer"
});
parameters.Add(new QueryParameter()
{
Name = "AdminUnitType_Department",
Value = Terrasoft.Core.DB.SysAdminUnitType.Department,
ValueTypeName = "Integer"
});
parameters.Add(new QueryParameter()
{
Name = "AdminUnitType_Manager",
Value = Terrasoft.Core.DB.SysAdminUnitType.Manager,
ValueTypeName = "Integer"
});
parameters.Add(new QueryParameter()
{
Name = "AdminUnitType_Team",
Value = Terrasoft.Core.DB.SysAdminUnitType.Team,
ValueTypeName = "Integer"
});
parameters.Add(new QueryParameter()
{
Name = "AdminUnitType_FunctionalRole",
Value = 6,
ValueTypeName = "Integer"
});
parameters.Add(new QueryParameter()
{
Name = "FolderId",
Value = parentRoleId,
ValueTypeName = "Guid"
});
var list = new List <object>();
list.Add(new Guid(parentRoleId));
using (DBExecutor dbExecutor = UserConnection.EnsureDBConnection())
{
using (IDataReader reader = dbExecutor.ExecuteReader(sqlText, parameters))
{
while (reader.Read())
{
Guid id = reader.GetColumnValue <Guid>("Id");
list.Add(id);
}
}
}
return(list.ToArray());
}