private static System.Collections.ObjectModel.Collection <KeyValuePair <Guid, string> > GetParentProjects(Guid projectId, UserConnection userConnection)
{
Select projectSelect =
new Terrasoft.Core.DB.Select(userConnection)
.Column("Id").As("Id")
.Column("Name").As("Name")
.Column("ParentProjectId").As("ParentProjectId")
.From("Project") as Select;
HierarchicalSelectOptions options = new HierarchicalSelectOptions()
{
PrimaryColumnName = "Id",
ParentColumnName = "ParentProjectId",
SelectType = HierarchicalSelectType.Parents
};
QueryCondition startingCondition = options.StartingPrimaryColumnCondition;
startingCondition.LeftExpression = new QueryColumnExpression("Id");
startingCondition.IsEqual(Column.Parameter(projectId, "ParentProjectId", Terrasoft.Common.ParameterDirection.Input));
projectSelect.HierarchicalOptions = options;
var list = new System.Collections.ObjectModel.Collection <KeyValuePair <Guid, string> >();
using (var dbExecutor = userConnection.EnsureDBConnection()) {
using (var reader = projectSelect.ExecuteReader(dbExecutor)) {
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);
}
private List <Guid> GetParentProjects(Guid projectId)
{
var projects = new List <Guid>();
var projectSelect = new Select(UserConnection)
.Column("Id").As("Id")
.Column("ParentProjectId").As("ParentProjectId")
.From("Project") as Select;
var options = new HierarchicalSelectOptions()
{
PrimaryColumnName = "Id",
ParentColumnName = "ParentProjectId",
SelectType = HierarchicalSelectType.Parents
};
var startingCondition = options.StartingPrimaryColumnCondition;
startingCondition.LeftExpression = new QueryColumnExpression("Id");
startingCondition.IsEqual(
Column.Parameter(projectId, "ParentProjectId", Terrasoft.Common.ParameterDirection.Input));
projectSelect.HierarchicalOptions = options;
var parameters = new QueryParameterCollection();
using (var dbExecutor = UserConnection.EnsureDBConnection()) {
using (var reader = projectSelect.ExecuteReader(dbExecutor)) {
while (reader.Read())
{
var id = UserConnection.DBTypeConverter.DBValueToGuid(reader["Id"]);
projects.Add(id);
}
}
}
return(projects);
}
public virtual Dictionary <Guid, string> GetGroupContacts(Guid sysAdminUnitId)
{
Dictionary <Guid, string> emails = new Dictionary <Guid, string>();
Select manhourHierarchicalSelect = new Select(UserConnection)
.Column("SysAdminUnit", "Id").As("Id")
.Column("Contact", "Id").As("ContactId")
.Column("Contact", "Email").As("Email")
.Column("Contact2", "Id").As("BaseContactId")
.Column("Contact2", "Email").As("BaseEmail")
.Column("SysAdminUnit", "ParentRoleId").As("ParentRoleId")
.From("SysAdminUnit")
.LeftOuterJoin("Contact").As("Contact2").On("Contact2", "Id").IsEqual("SysAdminUnit", "ContactId")
.LeftOuterJoin("SysUserInRole").On("SysUserInRole", "SysRoleId").IsEqual("SysAdminUnit", "Id")
.LeftOuterJoin("SysAdminUnit").As("SysAdminUnit2").On("SysUserInRole", "SysUserId").IsEqual("SysAdminUnit2", "Id")
.LeftOuterJoin("Contact").On("Contact", "Id").IsEqual("SysAdminUnit2", "ContactId") as Select;
HierarchicalSelectOptions options = new HierarchicalSelectOptions()
{
PrimaryColumnName = "Id",
ParentColumnName = "ParentRoleId",
SelectType = HierarchicalSelectType.Children,
IncludeLevelColumn = true,
IsDistinct = true
};
QueryCondition startingCondition = options.StartingPrimaryColumnCondition;
startingCondition.LeftExpression = new QueryColumnExpression("Id");
startingCondition.IsEqual(Column.Parameter(sysAdminUnitId, "ParentRoleId", Terrasoft.Common.ParameterDirection.Input));
manhourHierarchicalSelect.HierarchicalOptions = options;
using (var dbExecutor = UserConnection.EnsureDBConnection()) {
using (var reader = manhourHierarchicalSelect.ExecuteReader(dbExecutor)) {
while (reader.Read())
{
if (!reader.IsDBNull(1))
{
var contactId = UserConnection.DBTypeConverter.DBValueToGuid(reader[1]);
var email = reader[2].ToString();
if (!string.IsNullOrEmpty(email) && !emails.ContainsKey(contactId))
{
emails.Add(contactId, email);
}
}
if (!reader.IsDBNull(3))
{
var contactId = UserConnection.DBTypeConverter.DBValueToGuid(reader[3]);
var email = reader[4].ToString();
if (!string.IsNullOrEmpty(email) && !emails.ContainsKey(contactId))
{
emails.Add(contactId, email);
}
}
}
}
}
return(emails);
}
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 HierarchicalSelectOptions GetSelectOptions(Guid projectId)
{
var hierarhicalOptions = new HierarchicalSelectOptions {
PrimaryColumnName = "Id",
ParentColumnName = "ParentProjectId",
SelectType = HierarchicalSelectType.Children,
IncludeLevelColumn = true
};
QueryCondition startingCondition = hierarhicalOptions.StartingPrimaryColumnCondition;
startingCondition.LeftExpression = new QueryColumnExpression("Id");
startingCondition.IsEqual(new QueryParameter(ProjectIdParameterName, projectId, GuidTypeName));
return(hierarhicalOptions);
}
/// <summary>
/// ########## ######### ### ########## ############## #######.
/// </summary>
/// <param name="select">######</param>
/// <param name="hierarchicalSelectType">### ############## #######.</param>
/// <returns>######### ### ########## ############## #######.</returns>
private HierarchicalSelectOptions GetHierarchicalSelectOptions(Select select,
HierarchicalSelectType hierarchicalSelectType)
{
var hierarhicalOptions = new HierarchicalSelectOptions {
PrimaryColumnName = "Id",
ParentColumnName = "Parent.Id",
SelectType = hierarchicalSelectType,
IncludeLevelColumn = true
};
QueryCondition startingCondition = hierarhicalOptions.StartingPrimaryColumnCondition;
startingCondition.LeftExpression = new QueryColumnExpression("Id");
startingCondition.IsEqual(select.Parameters.GetByName("StartingAccountId"));
return(hierarhicalOptions);
}
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)
{
Select 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)) as Select;
HierarchicalSelectOptions options = new HierarchicalSelectOptions()
{
PrimaryColumnName = "Id",
ParentColumnName = "ParentRoleId",
SelectType = HierarchicalSelectType.Children
};
QueryCondition startingCondition = options.StartingPrimaryColumnCondition;
startingCondition.LeftExpression = new QueryColumnExpression("ParentRoleId");
startingCondition.IsEqual(Column.Parameter(new Guid(parentRoleId), "FolderId", Common.ParameterDirection.Input));
adminUnitSelect.HierarchicalOptions = options;
var list = new List <object>();
list.Add(new Guid(parentRoleId));
using (DBExecutor dbExecutor = UserConnection.EnsureDBConnection())
{
using (IDataReader reader = adminUnitSelect.ExecuteReader(dbExecutor))
{
while (reader.Read())
{
Guid id = reader.GetColumnValue <Guid>("Id");
list.Add(id);
}
}
}
return(list.ToArray());
}
public static Dictionary <Guid, KeyValuePair <Guid, string> > GetChildProjects(Guid projectId, UserConnection userConnection)
{
Select projectSelect =
new Terrasoft.Core.DB.Select(userConnection)
.Column("Id").As("Id")
.Column("Name").As("Name")
.Column("ParentProjectId").As("ParentProjectId")
.From("Project") as Select;
HierarchicalSelectOptions options = new HierarchicalSelectOptions()
{
PrimaryColumnName = "Id",
ParentColumnName = "ParentProjectId",
SelectType = HierarchicalSelectType.Children
};
QueryCondition startingCondition = options.StartingPrimaryColumnCondition;
startingCondition.LeftExpression = new QueryColumnExpression("Id");
startingCondition.IsEqual(Column.Parameter(projectId, "ParentProjectId", Terrasoft.Common.ParameterDirection.Input));
projectSelect.HierarchicalOptions = options;
var list = new Dictionary <Guid, KeyValuePair <Guid, string> >();
using (var dbExecutor = userConnection.EnsureDBConnection()) {
using (var reader = projectSelect.ExecuteReader(dbExecutor)) {
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);
}
/// <summary>
/// ######### ########## ############## ###### ######## ############# ########### # ###### #### #######.
/// </summary>
/// <param name="accountId">Id ###########.</param>
/// <returns>########## ############# ###########.</returns>
private Guid GetParentAccount(Guid accountId)
{
Guid result = Guid.Empty;
int levelMax = Int32.MinValue;
Select accountSelect = GetSelectQuery(accountId, null);
HierarchicalSelectOptions hierarhicalOptions = GetHierarchicalSelectOptions(accountSelect, HierarchicalSelectType.Parents);
string sqlText = UserConnection.DBEngine.GetQuerySqlText(accountSelect, hierarhicalOptions);
using (DBExecutor dbExecutor = UserConnection.EnsureDBConnection()) {
using (IDataReader reader = dbExecutor.ExecuteReader(sqlText, accountSelect.Parameters)) {
while (reader.Read())
{
int level = reader.GetColumnValue <int>("Level");
if (level > levelMax)
{
result = reader.GetColumnValue <Guid>("Id");
levelMax = level;
}
}
}
}
return(result);
}
/// <summary>
/// ######### #### ######## ############ # ###### #### #######.
/// </summary>
/// <param name="accountId">########## ############# ###########.</param>
/// <param name="additionalColumnNames">###### ######## ############## #######.</param>
/// <returns>###### ######## ############.</returns>
private List <AccountInfo> GetChildAccounts(Guid accountId, List <string> additionalColumnNames)
{
var resultList = new List <AccountInfo>();
additionalColumnNames = additionalColumnNames ?? new List <string>();
additionalColumnNames.Add("Type.Name");
Select accountSelect = GetSelectQuery(accountId, additionalColumnNames);
HierarchicalSelectOptions hierarhicalOptions = GetHierarchicalSelectOptions(accountSelect, HierarchicalSelectType.Children);
string sqlText = UserConnection.DBEngine.GetQuerySqlText(accountSelect, hierarhicalOptions);
using (DBExecutor dbExecutor = UserConnection.EnsureDBConnection()) {
using (IDataReader reader = dbExecutor.ExecuteReader(sqlText, accountSelect.Parameters)) {
while (reader.Read())
{
Guid id = reader.GetColumnValue <Guid>("Id");
string name = reader.GetColumnValue <string>("Name");
string accountType = reader.GetColumnValue <string>("Type.Name");
int level = reader.GetColumnValue <int>("Level");
Guid parentId = reader.GetColumnValue <Guid>("ParentId");
var additionalColumnValues = new Dictionary <string, object>();
foreach (string columnName in additionalColumnNames)
{
additionalColumnValues.Add(columnName, reader.GetColumnValue(columnName));
}
resultList.Add(new AccountInfo {
Id = id,
Name = name,
ParentId = parentId,
AccountType = accountType,
Level = level,
AdditionalColumnValues = additionalColumnValues
});
}
}
}
return(resultList);
}
/// <summary>
/// ######### ########## ############## ###### ######## ############# ########### # ###### #### #######.
/// </summary>
/// <param name="accountId">Id ###########.</param>
/// <returns>########## ############# ###########.</returns>
private Guid GetRootParentAccount(Guid accountId)
{
Guid result = Guid.Empty;
int levelMax = Int32.MinValue;
Select accountSelect = GetAccountSelect(accountId, null, useAdminRights: true);
HierarchicalSelectOptions hierarhicalOptions =
GetHierarchicalSelectOptions(accountSelect, HierarchicalSelectType.Parents);
accountSelect.HierarchicalOptions = hierarhicalOptions;
using (DBExecutor dbExecutor = UserConnection.EnsureDBConnection()) {
using (IDataReader reader = accountSelect.ExecuteReader(dbExecutor)) {
while (reader.Read())
{
int level = reader.GetColumnValue <int>("Level");
if (level > levelMax)
{
result = reader.GetColumnValue <Guid>("Id");
levelMax = level;
}
}
}
}
return(result);
}
/// <summary>
/// Receives all child accounts of the specified parent account. Does not consider user access rights.
/// </summary>
/// <param name="parentAccountId">Parent account for all accounts to receive.</param>
/// <param name="additionalColumnNames">List of names of additional columns to receive.</param>
/// <returns>List of child accounts.</returns>
private List <AccountInfo> GetAllChildAccounts(Guid parentAccountId, List <string> additionalColumnNames)
{
var resultList = new List <AccountInfo>();
additionalColumnNames = additionalColumnNames ?? new List <string>();
additionalColumnNames.Add("Name");
additionalColumnNames.Add("Type.Name");
Select accountSelect = GetAccountSelect(parentAccountId, additionalColumnNames, useAdminRights: false);
HierarchicalSelectOptions hierarhicalOptions =
GetHierarchicalSelectOptions(accountSelect, HierarchicalSelectType.Children);
accountSelect.HierarchicalOptions = hierarhicalOptions;
using (DBExecutor dbExecutor = UserConnection.EnsureDBConnection()) {
using (IDataReader reader = accountSelect.ExecuteReader(dbExecutor)) {
while (reader.Read())
{
Guid id = reader.GetColumnValue <Guid>("Id");
string name = reader.GetColumnValue <string>("Name");
string accountType = reader.GetColumnValue <string>("Type.Name");
int level = reader.GetColumnValue <int>("Level");
Guid parentId = reader.GetColumnValue <Guid>("Parent.Id");
var additionalColumnValues = additionalColumnNames.ToDictionary(columnName => columnName,
columnName => reader.GetColumnValue(columnName));
resultList.Add(new AccountInfo {
Id = id,
Name = name,
ParentId = parentId,
AccountType = accountType,
Level = level,
AdditionalColumnValues = additionalColumnValues
});
}
}
}
return(resultList);
}
/// <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());
}
