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);
}
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);
}
/// <summary>
/// ####### ####### ### CASE, # ####### ########### c######## ############
/// ######### ## ######### #######.
/// </summary>
/// <param name="columnExpression">########, ############# # ##### #####
/// #########.</param>
/// <returns>####### ######## ######### ######### ## #########
/// #######.</returns>
private QueryCondition CreateQueryCondition(QueryColumnExpression columnExpression)
{
var queryCondition = new QueryCondition(QueryConditionType.Greater)
{
LeftExpression = columnExpression
};
queryCondition.IsEqual(Column.Const(1));
return(queryCondition);
}
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());
}
/// <summary>
/// Deletes elements from queue with filtration.
/// </summary>
/// <param name="entitySchemaName">Entity schema name in queue.</param>
/// <param name="entitySelect">Query to queue object with filtration.</param>
/// <param name="queueId">Identifier of queue.</param>
/// <param name="queueName">Queue name.</param>
/// <returns></returns>
private int DeleteEntityQueueItemsByFilter(string entitySchemaName, Select entitySelect, Guid queueId,
string queueName)
{
try {
var idColumnExpression = new QueryColumnExpression(entitySchemaName, "Id");
var selectCondition = new QueryCondition(idColumnExpression);
selectCondition.IsEqual("QueueItem", "EntityRecordId");
if (!entitySelect.HasCondition)
{
entitySelect.Where(selectCondition);
}
else
{
entitySelect.And(selectCondition);
}
Select select =
(Select) new Select(_userConnection)
.Column("Id")
.From("QueueItemStatus")
.Where("IsFinal").IsEqual(Column.Parameter(false));
var delete =
(Delete) new Delete(_userConnection)
.From("QueueItem")
.Where("OperatorId").IsNull()
.And("QueueItem", "QueueId").IsEqual(Column.Parameter(queueId))
.And("StatusId").In(Column.SubSelect(select))
.And().Not().Exists(entitySelect);
DateTime addedRecordsStartTime = DateTime.Now;
int deletedRecords = delete.Execute();
if (addedRecordsStartTime.AddSeconds(60) < DateTime.Now)
{
delete.BuildParametersAsValue = true;
QueuesUtilities.LogDebug(string.Format(GetResourceValue("LongDeleteQueueItemsMessage"),
queueName, _autoUpdateQueuesProcessId.ToString(), delete.GetSqlText()));
}
return(deletedRecords);
} catch (Exception e) {
QueuesUtilities.LogError(string.Format(GetResourceValue("InvokeMethodErrorMessage"),
string.Concat("DeleteEntityQueueItemsByFilter ", queueName), e.Message), e);
throw;
}
}
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="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());
}