/// <summary>
/// Called at the end of the authorization process.
/// Creates transaction for write to the DB needed user data.
/// </summary>
/// <param name="userLogin">OAuth application user login.</param>
/// <param name="tokenStorageId">Created token storage identifier.</param>
protected override void PostprocessAuthentication(string userLogin, Guid tokenStorageId)
{
using (DBExecutor dbExecutor = UserConnection.EnsureDBConnection()) {
dbExecutor.StartTransaction();
try {
Guid mailboxId = CreateNewMailboxSyncSettings(userLogin, tokenStorageId);
CreateNewContactCommunications(userLogin);
CreateNewContactSyncSettings(mailboxId);
CreateNewActivitySyncSettings(mailboxId);
dbExecutor.CommitTransaction();
} catch {
dbExecutor.RollbackTransaction();
throw;
}
}
}
private object GetData(GaugeDashboardItemSelectBuilder dashboardSelectBuilder)
{
ApplySectionBindingFilter(dashboardSelectBuilder);
Select select = dashboardSelectBuilder.Build();
using (var dbExecutor = UserConnection.EnsureDBConnection()) {
using (IDataReader dataReader = select.ExecuteReader(dbExecutor)) {
if (dataReader.Read())
{
object value = GetValueByColumnMap(dataReader, dashboardSelectBuilder.EntityColumnMap);
return(value);
}
}
}
return(null);
}
private List <Guid> GetNotificationRecipients(UserConnection userConnection)
{
var recipientIds = new List <Guid>();
var thirtyDaysAgoDate = DateTime.UtcNow.AddDays(-30);
var unionSelect = CreateNotificationRecipientsSelect(userConnection, thirtyDaysAgoDate);
using (var dbExecutor = userConnection.EnsureDBConnection()) {
using (var dr = unionSelect.ExecuteReader(dbExecutor)) {
while (dr.Read())
{
recipientIds.Add(userConnection.DBTypeConverter.DBValueToGuid(dr["OwnerId"]));
}
}
}
return(recipientIds);
}
public static DateTime GetActualProjectEndDate(Guid projectId, UserConnection userConnection)
{
var dataValueTypeManager = (DataValueTypeManager)userConnection.AppManagerProvider.GetManager("DataValueTypeManager");
var storedProcedure = new StoredProcedure(userConnection, "tsp_GetProjectActualEndDate");
storedProcedure.WithParameter(Column.Const(projectId));
storedProcedure.WithOutputParameter("return_value", dataValueTypeManager.GetInstanceByName("DateTime"));
using (DBExecutor dbExecutor = userConnection.EnsureDBConnection()) {
storedProcedure.Execute(dbExecutor);
if (storedProcedure.Parameters.FindByName("return_value")?.Value is DateTime)
{
return((DateTime)storedProcedure.Parameters.FindByName("return_value")?.Value);
}
}
return(DateTime.Now);
}
public virtual Guid GetActivityType()
{
var select = (Select) new Select(UserConnection)
.Column("Id")
.From("ActivityType")
.Where("Code").IsEqual(Column.Parameter("Activity"));
using (DBExecutor dbExecutor = UserConnection.EnsureDBConnection()) {
using (IDataReader dr = select.ExecuteReader(dbExecutor)) {
if (dr.Read())
{
return(UserConnection.DBTypeConverter.DBValueToGuid(dr[0]));
}
}
}
return(Guid.Empty);
}
protected virtual decimal GetCurrencyDivision(UserConnection userConnection, Guid currencyId)
{
Select currencyDivisionSelect = (Select) new Select(userConnection)
.Column("c", "Division")
.From("Currency").As("c")
.Where("Id").IsEqual(new QueryParameter(currencyId));
using (DBExecutor dbExecutor = userConnection.EnsureDBConnection()) {
using (var dataReader = currencyDivisionSelect.ExecuteReader(dbExecutor)) {
if (dataReader.Read())
{
return(userConnection.DBTypeConverter.DBValueToDecimal(dataReader[0]));
}
}
}
return(1m);
}
/// <summary>
/// Create instance for case rules.
/// </summary>
/// <param name="entity">Entity</param>
public void Execute(Entity entity)
{
using (var dbExecutor = _userConnection.EnsureDBConnection()) {
var oldValue = entity.GetTypedOldColumnValue <Guid>(StatusIdColumnName);
var value = entity.GetTypedColumnValue <Guid>(StatusIdColumnName);
var rulesSelect = GetRuleQuery(oldValue, value);
using (IDataReader reader = rulesSelect.ExecuteReader(dbExecutor)) {
while (reader.Read())
{
string ruleClassName = reader.GetColumnValue <string>("ClassName");
var fullRuleTypeName = Type.GetType(ruleClassName).AssemblyQualifiedName;
ICaseRuleHandler instance = ClassFactory.ForceGet <ICaseRuleHandler>(fullRuleTypeName, new ConstructorArgument("userConnection", _userConnection));
instance.Handle(entity);
}
}
}
}
public decimal GetErSum(string name)
{
decimal sum = decimal.Zero;
//Search for Parent Id
Select selectER = new Select(UserConnection)
.Column("Id")
.From("ExpenseReport")
.Where("Name").IsEqual(Column.Parameter(name)) as Select;
Guid Id = selectER.ExecuteScalar <Guid>();
//Sum up all lines
Select select = new Select(UserConnection)
.Column("AmountHC")
.Column("AmountFC")
.From("ExpenseReportLines")
.Where("ExpenseReportId").IsEqual(Column.Parameter(Id)) as Select;
decimal amountHc = decimal.Zero;
//decimal amountFc = decimal.Zero;
using (DBExecutor executor = UserConnection.EnsureDBConnection())
{
using (IDataReader dataReader = select.ExecuteReader(executor))
{
while (dataReader.Read())
{
amountHc = Convert.ToDecimal(dataReader.GetValue(dataReader.GetOrdinal("AmountHC")));
//amountFc = Convert.ToDecimal(dataReader.GetValue(dataReader.GetOrdinal("AmountFC")));
sum += amountHc;
var a = dataReader;
}
}
}
return(sum);
}
/// <summary>
/// Sets route filters to null if route period greater or equal to response (campaign's campaign)
/// actualization period.
/// </summary>
/// <param name="campaignId">Unique identifier of campaign.</param>
private void UpdateUnconditionedRoutes(Guid campaignId)
{
var actualizePeriod = (int)SysSettings.GetValue(UserConnection, "PeriodActualizeBulkEmailRecipients") /
DayHourCount;
Select routePeriodSelect = GetStepRoutesSelectTemplate(campaignId,
CampaignConsts.BulkEmailCampaignStepTypeId);
using (DBExecutor dbExecutor = UserConnection.EnsureDBConnection()) {
using (IDataReader dr = routePeriodSelect.ExecuteReader(dbExecutor)) {
while (dr.Read())
{
var id = new Guid(dr[0].ToString());
var json = dr.GetColumnValue <string>("JSON");
double routePeriod = CampaignStepsHandler.DeserializeDayTransitionCount(json, UserConnection);
if (routePeriod <= 0)
{
routePeriod = DefaultMinimumDayTransitionCount;
}
if (routePeriod >= actualizePeriod)
{
continue;
}
JObject jsonData = Json.Deserialize(json) as JObject;
if (jsonData == null)
{
continue;
}
JObject jsonInfoData = jsonData[RouteJsonAddInfoSection] as JObject;
if (jsonInfoData == null)
{
continue;
}
JArray jsonFiltersData = jsonInfoData[RouteJsonFilterSection] as JArray;
if (jsonFiltersData == null)
{
continue;
}
jsonFiltersData.Replace(new JArray());
json = Json.Serialize(jsonData);
new Update(_userConnection, "CampaignStepRoute")
.Set("JSON", Column.Parameter(json))
.Where("Id").IsEqual(Column.Parameter(id)).Execute();
}
}
}
}
/// <summary>
/// Get collection with data of activity files.
/// </summary>
/// <param name="activityFilesSelect">Query to File table.<see cref="Select"/></param>
/// <returns>Collection with ActivityFileContainer.<see cref="ActivityFileContainer"/></returns>
protected List <ActivityFileContainer> GetActivityFilesCollection(Select activityFilesSelect)
{
var resultCollection = new List <ActivityFileContainer>();
using (var dbExecutor = UserConnection.EnsureDBConnection()) {
using (var dataReader = activityFilesSelect.ExecuteReader(dbExecutor)) {
while (dataReader.Read())
{
resultCollection.Add(new ActivityFileContainer {
Name = dataReader.GetColumnValue <string>("Name"),
Id = dataReader.GetColumnValue <Guid>("ActivityFileId")
});
}
}
}
return(resultCollection);
}
private void ReadDataFromQuery(CustomSelectQuery query)
{
List <string> emails = GetEmailsWithoutContact();
if (emails.Any())
{
var contactSelectQuery = query(emails);
using (DBExecutor dbExec = UserConnection.EnsureDBConnection()) {
using (IDataReader reader = contactSelectQuery.ExecuteReader(dbExec)) {
while (reader.Read())
{
FillInviteData(reader);
}
}
}
}
}
protected override bool InternalExecute(ProcessExecutingContext context)
{
Select settingsSelectQuery = new Select(UserConnection)
.Column("PerformSearchOnSave")
.From("DuplicatesSearchParameter")
.Where("SchemaToSearchName").In(Column.Parameter(SearchSchemaName)) as Select;
using (var dbExecutor = UserConnection.EnsureDBConnection()) {
using (IDataReader dr = settingsSelectQuery.ExecuteReader(dbExecutor)) {
while (dr.Read())
{
PerformSearchOnSave = UserConnection.DBTypeConverter.DBValueToBool(dr.GetValue(0));
}
}
}
return(true);
}
/// <summary>
/// Returns day of license expiration.
/// </summary>
/// <param name="userConnection">User connection instance.</param>
/// <returns>Day of license expiration.</returns>
private static DateTime GetLicenseDueDate(UserConnection userConnection, string licensePackageName)
{
var dueDate = DateTime.MinValue;
var select = GetLicenseDueDateSelect(userConnection, licensePackageName);
using (var dbExecutor = userConnection.EnsureDBConnection()) {
using (var reader = select.ExecuteReader(dbExecutor)) {
var dueDateColumnIndex = reader.GetOrdinal("DueDate");
while (reader.Read())
{
dueDate = reader.GetDateTime(dueDateColumnIndex);
break;
}
}
}
return(dueDate);
}
/// <summary>
/// Returns mail server name.</summary>
/// <param name="serverId">Mail server identifier.</param>
/// <returns>Mail server name.</returns>
private string GetMailServerName(Guid serverId)
{
Select select = new Select(_userConnection)
.Column("ExchangeEmailAddress")
.From("MailServer")
.Where("Id").IsEqual(Column.Parameter(serverId)) as Select;
using (var dbExecutor = _userConnection.EnsureDBConnection()) {
using (var reader = select.ExecuteReader(dbExecutor)) {
if (reader.Read())
{
return(reader.GetColumnValue <string>("ExchangeEmailAddress"));
}
}
}
return(string.Empty);
}
/// <summary>
/// Executes current flow element for selected audience.
/// Adds participants to <see cref="CampaignQueue"/> and sets them
/// <see cref="CampaignConstants.CampaignParticipantInProgressStatusId"/>.
/// </summary>
/// <param name="audieceQuery">Query for item audience to be processed.</param>
/// <returns>Count of participants which has been processed.</returns>
protected override int SingleExecute(Query audieceQuery)
{
using (var dbExecutor = UserConnection.EnsureDBConnection()) {
dbExecutor.StartTransaction(IsolationLevel.ReadCommitted);
try {
var contactsSelect = GetParticipatingContactsSelect((Select)audieceQuery);
CampaignQueueManager.Enqueue(contactsSelect, CampaignItemId, QueueItemExpirationPeriod);
var result = SetParticipantsStatus((Select)audieceQuery,
CampaignConstants.CampaignParticipantInProgressStatusId, false);
dbExecutor.CommitTransaction();
return(result);
} catch {
dbExecutor.RollbackTransaction();
throw;
}
}
}
/// <summary>
/// Returns current server <see cref="MailboxSyncSettings"/> identifiers.
/// </summary>
/// <returns>Current server <see cref="MailboxSyncSettings"/> identifiers.</returns>
protected virtual List <Guid> GetCurrentMailserverMailboxIds()
{
var select = (Select) new Select(UserConnection)
.Column("Id")
.From("MailboxSyncSettings")
.Where("MailServerId").IsEqual(Column.Parameter(Entity.PrimaryColumnValue));
var mailboxIds = new List <Guid>();
using (DBExecutor dbExecutor = UserConnection.EnsureDBConnection()) {
using (IDataReader dataReader = select.ExecuteReader(dbExecutor)) {
while (dataReader.Read())
{
mailboxIds.Add(dataReader.GetColumnValue <Guid>("Id"));
}
}
}
return(mailboxIds);
}
private List <Guid> GetLanguageIdByCode(IEnumerable <string> culturesCode)
{
var userLangInfo = new List <(Guid id, string code)>();
if (culturesCode.Any())
{
var select = GetLangSelect(culturesCode);
using (DBExecutor dbExecutor = UserConnection.EnsureDBConnection()) {
using (var reader = select.ExecuteReader(dbExecutor)) {
while (reader.Read())
{
userLangInfo.Add((reader.GetColumnValue <Guid>("Id"), reader.GetColumnValue <string>("Code")));
}
}
}
}
return(GetLangListOrdered(culturesCode, userLangInfo));
}
private void ReadAndFillContactData()
{
List <Guid> contacts = UsersToCreate.Where(x => x.ContactId != Guid.Empty).
Select(user => user.ContactId).ToList();
if (contacts.Any())
{
var contactSelectQuery = GetContactQuery(contacts);
using (DBExecutor dbExec = UserConnection.EnsureDBConnection()) {
using (IDataReader reader = contactSelectQuery.ExecuteReader(dbExec)) {
while (reader.Read())
{
FillInviteData(reader);
}
}
}
}
}
/// <summary>
/// Returns entity connection columns unique identifiers for <see cref="EntitySchemaUId"/>.
/// </summary>
/// <param name="userConnection"><see cref="UserConnection"/> instance.</param>
/// <returns>Entity connection columns unique identifiers list.</returns>
protected IEnumerable <Guid> GetEntityRelationColumnUIds(UserConnection userConnection)
{
var select = new Select(userConnection)
.Column("ColumnUId")
.From("EntityConnection")
.Where("SysEntitySchemaUId").IsEqual(Column.Parameter(EntitySchemaUId)) as Select;
List <Guid> result = new List <Guid>();
using (var dbExecutor = userConnection.EnsureDBConnection()) {
using (var reader = select.ExecuteReader(dbExecutor)) {
while (reader.Read())
{
result.Add(reader.GetColumnValue <Guid>("ColumnUId"));
}
}
}
return(result);
}
private Guid GetPartnershipFromFund(Guid prmFundId)
{
Guid partnershipId = Guid.Empty;
Select selectQuery = new Select(_userConnection)
.Column(_partnershipColumnName)
.From(_fundLookupSchemaName)
.Where("Id").IsEqual(Column.Const(prmFundId)) as Select;
using (DBExecutor dbExecutor = _userConnection.EnsureDBConnection()) {
using (IDataReader reader = selectQuery.ExecuteReader(dbExecutor)) {
while (reader.Read())
{
partnershipId = reader.GetColumnValue <Guid>(_partnershipColumnName);
}
}
}
return(partnershipId);
}
/// <summary>
/// Get portal message files.
/// </summary>
/// <param name="PortalMessageId">Portal message uniqueidentifier.</param>
/// <param name="readingOptions">Readin message options <see cref="ReadingOptions"/>.</param>
/// <returns>Container with Portal message files collection <see cref="PortalFileServiceResponse"/>.</returns>
public IEnumerable <PortalMessageFileDTO> GetPortalMessageFiles(string portalMessageId, ReadingOptions readingOptions)
{
var options = GetPageableOptions(readingOptions);
var portalMessageFileSelect = GetPortalMessageFilesSelect(portalMessageId).ToPageable(options);
var files = new List <PortalMessageFileDTO>();
using (DBExecutor dbExec = userConnection.EnsureDBConnection()) {
using (IDataReader reader = portalMessageFileSelect.ExecuteReader(dbExec)) {
var canReadData = false;
while (reader.Read() && ((canReadData || CheckIfUserCanReadFiles(reader.GetColumnValue <Guid>("EntitySchemaUId"),
reader.GetColumnValue <Guid>("EntityId"), out canReadData))))
{
files.Add(CreatePortalMessageDTO(reader));
}
}
}
return(files);
}
/// <summary>
/// Returns sync flag value from database.
/// </summary>
/// <param name="userConnection"><see cref="UserConnection"/> instance.</param>
/// <returns>
/// Sync flag value from application database.
/// </returns>
private string GetSyncKeyFromDB(UserConnection userConnection)
{
var result = string.Empty;
var select = new Select(userConnection)
.Column("Name")
.From("EmailSynchronizedKey")
.OrderByAsc("CreatedOn") as Select;
using (var dbExecutor = userConnection.EnsureDBConnection()) {
using (var reader = select.ExecuteReader(dbExecutor)) {
if (reader.Read())
{
result = reader.GetString(0);
}
}
}
return(result);
}
/// <summary>
/// Get language identifier related with contact.
/// </summary>
/// <param name="contactId">Contact identifier.</param>
/// <returns>Language identifier.</returns>
public Guid Get(Guid contactId)
{
Guid languageId = Guid.Empty;
Select selectQuery = GetSelect(contactId);
if (selectQuery != null)
{
using (DBExecutor dbExec = _userConnection.EnsureDBConnection()) {
using (IDataReader reader = dbExec.ExecuteReader(selectQuery.GetSqlText())) {
while (reader.Read())
{
languageId = reader.GetColumnValue <Guid>("LanguageId");
}
}
}
}
return(languageId);
}
/// <summary>
/// Template method for extracting data based on Extractor query and converting it to specific format.
/// </summary>
/// <returns>Data in specific format.</returns>
public IEnumerable <Dictionary <string, object> > ExtractData()
{
PrepareQuery();
AddFilterFromFiltersCollection();
if (Query != null)
{
using (DBExecutor dbExecutor = UserConnection.EnsureDBConnection()) {
using (IDataReader dataReader = Query.ExecuteReader(dbExecutor)) {
IEnumerable <Dictionary <string, object> > dataSet = ConvertQueryResult(dataReader);
return(dataSet);
}
}
}
else
{
throw new NullReferenceException("Extract Query didn't set up in PrepareQuery method");
}
}
/// <summary>
/// Check if parent element in hierarchy of his parent.
/// </summary>
/// <param name="childId"></param>
/// <param name="parentId"></param>
/// <returns><c>true</c> if element in hierarchy</returns>
public bool HasParentInBranch(Guid childId, Guid parentId)
{
Guid currentId = parentId;
List <Guid> checkedIds = new List <Guid> {
currentId
};
using (var dbExecutor = _userConnection.EnsureDBConnection()) {
while (true)
{
Select currentElementSelect = new Select(_userConnection)
.Column(_parentColumnName)
.From(_entitySchemaName).Where("Id").IsEqual(Column.Parameter(currentId)) as Select;
using (IDataReader reader = currentElementSelect.ExecuteReader(dbExecutor)) {
if (reader.Read())
{
if (reader.IsDBNull(reader.GetOrdinal(_parentColumnName)))
{
return(false);
}
else
{
Guid currentElementParentId = reader.GetColumnValue <Guid>(_parentColumnName);
if (currentElementParentId.Equals(childId))
{
return(true);
}
if (checkedIds.Contains(currentElementParentId))
{
string LocalizableString = new LocalizableString(_userConnection.Workspace.ResourceStorage,
"HierarchicalRecordsHelper", "LocalizableStrings.HierarchicalErrorMessage.Value");
throw new LoopInHierarchyException(LocalizableString);
}
else
{
checkedIds.Add(currentElementParentId);
currentId = currentElementParentId;
}
}
}
}
}
}
}
private static Dictionary <Guid, Guid> GetEmailsForActualization(UserConnection userConnections)
{
var bulkEmailRIds = new Dictionary <Guid, Guid>();
var getByIdsSelect = GetEmailForActualizationSelect(userConnections, "Id", "BulkEmailId");
var getByRIdsSelect = GetEmailForActualizationSelect(userConnections, "RId", "BulkEmailRId");
getByIdsSelect.Union(getByRIdsSelect);
using (DBExecutor dbExecutor = userConnections.EnsureDBConnection()) {
using (IDataReader reader = getByIdsSelect.ExecuteReader(dbExecutor)) {
while (reader.Read())
{
Guid id = userConnections.DBTypeConverter.DBValueToGuid(reader.GetValue(0));
Guid categoryId = userConnections.DBTypeConverter.DBValueToGuid(reader.GetValue(1));
bulkEmailRIds[id] = categoryId;
}
}
}
return(bulkEmailRIds);
}
/// <summary>
/// Updates processing campaign participants' state, actualizes relations
/// and removes operation campaign participants in transaction.
/// </summary>
/// <param name="userConnection">Instance of <see cref="UserConnection"/>.</param>
/// <returns>Count of synchronized participants.</returns>
protected override int InternalSynchronize(UserConnection userConnection)
{
int syncParticipantsCount;
using (var dbExecutor = userConnection.EnsureDBConnection()) {
dbExecutor.StartTransaction();
try {
syncParticipantsCount = UpdateParticipants(userConnection);
SyncParticipantRelations(userConnection);
DeleteSynchronizedRelations(userConnection);
DeleteSynchronizedContacts(userConnection);
dbExecutor.CommitTransaction();
} catch (Exception) {
dbExecutor.RollbackTransaction();
throw;
}
}
return(syncParticipantsCount);
}
public List <Guid> GetUserIdsByContactId(Guid contactId)
{
var userIdsQuery = new Select(_userConnection)
.Column("Id")
.From("SysAdminUnit")
.Where("ContactId").IsEqual(Column.Parameter(contactId))
.And("SysAdminUnitTypeValue").IsEqual(Column.Parameter(SysAdminUnitType.User));
var userIds = new List <Guid>();
using (DBExecutor dbExecutor = _userConnection.EnsureDBConnection())
using (IDataReader dr = ((Select)userIdsQuery).ExecuteReader(dbExecutor)) {
while (dr.Read())
{
var val = dr.GetValue(0).ToString();
userIds.Add(new Guid(val));
}
}
return(userIds);
}
public override void SetIsAdministratedByOperations()
{
Select select =
new Select(UserConnection)
.Column(Column.Const(1))
.From("VwAdministrativeObjects")
.InnerJoin("SysSSPEntitySchemaAccessList").On("SysSSPEntitySchemaAccessList", "EntitySchemaUId")
.IsEqual("VwAdministrativeObjects", "UId")
.Where("VwAdministrativeObjects", "Id")
.IsEqual(Column.Parameter(SelectedNodePrimaryColumnValue))
.And("VwAdministrativeObjects", "SysWorkspaceId").IsEqual(Column.Parameter(Page.UserConnection.Workspace.Id))
.And("VwAdministrativeObjects", "AdministratedByOperations").IsEqual(Column.Parameter(false)) as Select;
using (DBExecutor dbExecutor = UserConnection.EnsureDBConnection()) {
using (var dataReader = select.ExecuteReader(dbExecutor)) {
IsOperationAdministrated = dataReader.Read();
}
}
}
private List <InitialResultItem> GetInitialResults(string schemaName, int offset)
{
string recordIdColumnName = GetRecordIdColumnName(schemaName);
List <InitialResultItem> result = new List <InitialResultItem>();
Select initialResultsQuery = CreateInitialResultsSelect(schemaName, offset);
using (DBExecutor dbExecutor = _userConnection.EnsureDBConnection()) {
using (IDataReader reader = initialResultsQuery.ExecuteReader(dbExecutor)) {
while (reader.Read())
{
InitialResultItem resultItem = new InitialResultItem();
resultItem.RecordId = reader.GetColumnValue <Guid>(recordIdColumnName);
resultItem.GroupId = reader.GetColumnValue <int>("GroupId");
result.Add(resultItem);
}
}
}
return(result);
}
