/// <summary>
/// Returns list of available licenses with license amount
/// and presence information for target user.
/// </summary>
/// <param name="userId">User identifier.</param>
/// <param name="isNew">Indicates if user is new or not.</param>
/// <returns>Licenses list.</returns>
private EntityCollection GetAvaliableLic(Guid userId, bool isNew)
{
QueryCase caseForAvailable = GenerateCaseForAvailableLic();
var select =
new Select(UserConnection)
.Column("SysLicPackageId")
.Column("Count").As("Purchased")
.Column(Column.SubSelect(GenerateSelectForCase())).As("Used")
.Column(caseForAvailable).As("Available")
.Column("licPackage", "Name").As("SysPackageName")
.Column("licPackage", "Operations").As("PackageOperations")
.Column("LicType")
.From("SysLic").As("lic")
.InnerJoin("SysLicPackage").As("licPackage").On("lic", "SysLicPackageId").IsEqual("licPackage", "Id")
.Where(new QueryParameter(DateTime.Now.Date)).IsBetween("StartDate").And("DueDate")
.OrderByAsc("licPackage", "Name").OrderByDesc("lic", "DueDate") as Select;
if (!isNew)
{
QueryCase caseForHadLic = GenerateCaseForHadLic(userId);
select
.Column(caseForHadLic).As("HadLicense");
}
EntitySchema licSchema = UserConnection.EntitySchemaManager.GetInstanceByName("SysLic");
var result = new EntityCollection(UserConnection, licSchema);
using (DBExecutor executor = UserConnection.EnsureDBConnection()) {
using (System.Data.IDataReader reader = select.ExecuteReader(executor)) {
result.Load(reader);
}
}
return(result);
}
/// <summary>
/// Uppdates current step and set next step to null for finalized campaign.
/// </summary>
/// <param name="campaignId">Unique identifier of campaign.</param>
/// <param name="userConnection">User connection.</param>
private void SetGoalForCampaignStep(Guid campaignId, UserConnection userConnection)
{
Guid goalCampaignStepId = GetFirstCampainStepByType(campaignId, CampaignConsts.GoalCampaignStepTypeId,
userConnection);
Guid exitCampaignStepId = GetFirstCampainStepByType(campaignId, CampaignConsts.ExitCampaignStepTypeId,
userConnection);
QueryColumnExpression nullParameter =
Column.Parameter(DBNull.Value, new GuidDataValueType(UserConnection.DataValueTypeManager));
QueryCase queryCase = new QueryCase();
QueryCondition queryCondition = new QueryCondition(QueryConditionType.Equal)
{
LeftExpression = new QueryColumnExpression("CurrentStepId")
};
queryCondition.RightExpressions.Add(Column.Parameter(goalCampaignStepId));
queryCase.AddWhenItem(queryCondition, Column.Parameter(goalCampaignStepId));
queryCondition = new QueryCondition(QueryConditionType.Equal)
{
LeftExpression = new QueryColumnExpression("NextStepId")
};
queryCondition.RightExpressions.Add(Column.Parameter(goalCampaignStepId));
queryCase.AddWhenItem(queryCondition, Column.Parameter(goalCampaignStepId));
queryCase.ElseExpression = new QueryColumnExpression(Column.Parameter(exitCampaignStepId));
var update = new Update(userConnection, "CampaignTarget")
.Set("CurrentStepId", new QueryColumnExpression(queryCase))
.Set("NextStepId", nullParameter)
.Where("CampaignId").IsEqual(Column.Parameter(campaignId)) as Update;
update.Execute();
}
/// <summary>
/// Retrieve contacts email by contact Id <paramref name="contactId"/> and domain <paramref name="domain"/>.
/// If domain not found in emails then return last created email.
/// </summary>
/// <param name="userConnection">Instance of the current user connection.</param>
/// <param name="contactId">Contact unique identifier.</param>
/// <param name="domain">Domain in email.</param>
/// <returns>Contact email.</returns>
public static string FindContactEmail(UserConnection userConnection, Guid contactId, string domain)
{
domain = "@" + domain;
string email = string.Empty;
var communicationTypeContact = Guid.Parse(Terrasoft.Configuration.CommunicationTypeConsts.EmailId);
var queryCase = new QueryCase();
var queryCondition = new QueryCondition(QueryConditionType.EndWith)
{
LeftExpression = new QueryColumnExpression("Number")
};
queryCondition.RightExpressions.Add(new QueryParameter(domain));
queryCase.AddWhenItem(queryCondition, Column.Parameter("1"));
queryCase.ElseExpression = Column.Parameter("0");
var select = new Select(userConnection)
.Top(1)
.Column(queryCase).As("IsContainDomain")
.Column("CreatedOn")
.Column("Number")
.From("ContactCommunication")
.Where("ContactId").IsEqual(Column.Parameter(contactId))
.And("CommunicationTypeId").IsEqual(Column.Parameter(communicationTypeContact))
.OrderByDesc("IsContainDomain")
.OrderByDesc("CreatedOn") as Select;
using (var dbExecutor = userConnection.EnsureDBConnection()) {
using (IDataReader dataReader = select.ExecuteReader(dbExecutor)) {
if (dataReader.Read())
{
email = dataReader.GetColumnValue <string>("Number");
}
}
}
return(email);
}
/// <summary>
/// ########## ######### CASE ## ######### ########### ##########.
/// </summary>
/// <param name="conditionForCaseExpression">########, #############
/// # ##### ##### ####### WHEN.</param>
/// <param name="expressionForThenInCase">#########, ####### #####
/// ######### # ##### THEN.</param>
/// <param name="expressionForElseInCase">#########, ####### #####
/// ######### # ##### ELSE.</param>
/// <returns>######### CASE.</returns>
private QueryCase GenerateCaseForLicQuery(QueryColumnExpression conditionForCaseExpression,
QueryColumnExpression expressionForThenInCase, QueryColumnExpression expressionForElseInCase)
{
var queryCase = new QueryCase();
QueryCondition queryCondition = CreateQueryCondition(conditionForCaseExpression);
queryCase.AddWhenItem(queryCondition, expressionForThenInCase);
queryCase.SetElseExpression(expressionForElseInCase);
return(queryCase);
}
public override Select GetEntitiesSelect()
{
Select sysAdminUnitInRoleSelect = GetSysAdminUnitInRoleSelect();
ISchemaManagerItem <EntitySchema> invoiceEntitySchema =
UserConnection.EntitySchemaManager.FindItemByName(InvoiceSchemaName);
Guid sysImage = GetNotificationImage(InvoiceVisaSchemaName, RemindingConsts.NotificationTypeRemindingId);
Guid[] finallyStatuses = NotificationUtilities.GetFinallyVisaStatuses(UserConnection);
string invoiceCaption = invoiceEntitySchema.Caption;
QueryCase queryCase = GetQueryCase();
var entitiesSelect = new Select(UserConnection)
.Column(InvoiceVisaSchemaName, "Id")
.Column(InvoiceVisaSchemaName, "CreatedOn")
.Column(InvoiceVisaSchemaName, "CreatedById")
.Column(InvoiceVisaSchemaName, "ModifiedOn")
.Column(InvoiceVisaSchemaName, "ModifiedById")
.Column(InvoiceVisaSchemaName, "ProcessListeners")
.Column(InvoiceVisaSchemaName, "Objective")
.Column(queryCase).As("VisaOwnerId")
.Column(InvoiceVisaSchemaName, "IsAllowedToDelegate")
.Column(InvoiceVisaSchemaName, "DelegatedFromId")
.Column(InvoiceVisaSchemaName, "StatusId")
.Column(InvoiceVisaSchemaName, "SetById")
.Column(InvoiceVisaSchemaName, "SetDate")
.Column(InvoiceVisaSchemaName, "IsCanceled")
.Column(InvoiceVisaSchemaName, "Comment")
.Column(Column.Parameter(InvoiceSchemaName)).As("VisaSchemaName")
.Column(InvoiceSchemaName, "Number").As("Title")
.Column(InvoiceSchemaName, "Id").As("VisaObjectId")
.Column(InvoiceSchemaName, "StartDate").As("Date")
.Column("Account", "Name").As("Account")
.Column("Contact", "Name").As("Contact")
.Column(Column.Parameter(Guid.Empty)).As("VisaSchemaTypeId")
.Column(Column.Parameter(string.Empty)).As("VisaTypeName")
.Column(Column.Parameter(invoiceCaption)).As("VisaSchemaCaption")
.Column(Column.Parameter(InvoiceSchemaName)).As("SchemaName")
.Column(Column.Parameter(sysImage)).As("ImageId")
.Distinct()
.From(InvoiceVisaSchemaName)
.InnerJoin(InvoiceSchemaName).On(InvoiceSchemaName, "Id").IsEqual(InvoiceVisaSchemaName, "InvoiceId")
.InnerJoin(sysAdminUnitInRoleSelect).As("SysAdminUnitRole")
.On(InvoiceVisaSchemaName, "VisaOwnerId").IsEqual("SysAdminUnitRole", "SysAdminUnitRoleId")
.LeftOuterJoin("SysUserInRole")
.On("SysUserInRole", "SysRoleId").IsEqual("SysAdminUnitRole", "SysAdminUnitId")
.LeftOuterJoin("Account").On("Account", "Id").IsEqual(InvoiceSchemaName, "AccountId")
.LeftOuterJoin("Contact").On("Contact", "Id").IsEqual(InvoiceSchemaName, "ContactId")
.Where(InvoiceVisaSchemaName, "VisaOwnerId").In(
new Select(UserConnection).Column("SysAdminUnitRoleId").From("SysAdminUnitInRole")
.Where("SysAdminUnitId").IsEqual(Column.Parameter(_sysAdminUnit)))
.And(InvoiceVisaSchemaName, "StatusId").Not().In(Column.Parameters(finallyStatuses))
.And(InvoiceVisaSchemaName, "IsCanceled").IsEqual(Column.Parameter(false)) as Select;
return(entitiesSelect);
}
/// <summary>
/// Adds when condition to case statement of select.
/// </summary>
/// <param name="queryCase">Case statement of select.</param>
/// <param name="sourceTableAlias">Alias of table in left expression.</param>
/// <param name="sourceColumnAlias">Alias of column in left expression.</param>
/// <param name="conditionType">Type of comparison condition.</param>
/// <param name="rightExpressionValue">Value of right expression.</param>
/// <param name="thenValue">Value that is returned in then statement.</param>
public static void AddWhenCondition(QueryCase queryCase, string sourceTableAlias, string sourceColumnAlias,
QueryConditionType conditionType, object rightExpressionValue, object thenValue)
{
var whenCondition = new QueryCondition(conditionType)
{
LeftExpression = new QueryColumnExpression(sourceTableAlias, sourceColumnAlias)
};
whenCondition.RightExpressions.Add(Column.Const(rightExpressionValue));
queryCase.AddWhenItem(whenCondition, Column.Parameter(thenValue));
}
private QueryCase GetQueryCase()
{
var queryCase = new QueryCase();
var conditionFirst = new QueryCondition(QueryConditionType.IsNull)
{
LeftExpression = new QueryColumnExpression("SysUserInRole", "SysUserId")
};
queryCase.AddWhenItem(conditionFirst, new QueryColumnExpression(OrderVisaSchemaName, "VisaOwnerId"));
queryCase.ElseExpression = new QueryColumnExpression("SysUserInRole", "SysUserId");
return(queryCase);
}
/// <summary>
/// Returns column expression with non-empty result.
/// </summary>
/// <param name="value">Value of column</param>
/// <param name="columnName">Name of column.</param>
/// <returns>Column expression with non-empty result.</returns>
public static QueryColumnExpression GetIsNotEmptyCountColumnExpression(int value, string columnName)
{
QueryCase queryCase = new QueryCase();
QueryCondition queryCondition = new QueryCondition(QueryConditionType.Equal)
{
LeftExpression = new QueryColumnExpression(columnName)
};
queryCondition.RightExpressions.Add(Column.Const(0));
queryCase.AddWhenItem(queryCondition, Column.Parameter(value));
queryCase.ElseExpression = new QueryColumnExpression(columnName);
return(new QueryColumnExpression(queryCase));
}
/// <summary>
/// Returns <see cref="Select"/> of entity./>
/// </summary>
/// <returns>A <see cref="Select"/> instance.</returns>
public override Select GetEntitiesSelect()
{
Select sysAdminUnitInRoleSelect = GetSysAdminUnitInRoleSelect();
Guid[] finallyStatuses = NotificationUtilities.GetFinallyVisaStatuses(UserConnection);
QueryCase queryCase = GetVisaOwnerQueryCase();
var entitiesSelect = new Select(UserConnection)
.Distinct()
.Column(Visa, "Id")
.Column(Visa, "CreatedOn")
.Column(Visa, "CreatedById")
.Column(Visa, "ModifiedOn")
.Column(Visa, "ModifiedById")
.Column(Visa, "ProcessListeners")
.Column(Visa, "Objective").As("Objective")
.Column(queryCase).As("VisaOwnerId")
.Column(Visa, "IsAllowedToDelegate")
.Column(Visa, "DelegatedFromId")
.Column(Visa, "StatusId")
.Column(Visa, "SetById")
.Column(Visa, "SetDate").As("SetDate")
.Column(Visa, "IsCanceled")
.Column(Visa, "Comment")
.Column(Column.Parameter(Visa)).As("VisaSchemaName")
.Column(Name, TitleColumn).As("Title")
.Column(Name, "Id").As("VisaObjectId")
.Column(Visa, "CreatedOn").As("Date")
.Column(Column.Parameter(string.Empty)).As("Account")
.Column(Column.Parameter(string.Empty)).As("Contact")
.Column(Column.Parameter(Guid.Empty)).As("VisaSchemaTypeId")
.Column(Column.Parameter(string.Empty)).As("VisaTypeName")
.Column(Column.Parameter(Caption)).As("VisaSchemaCaption")
.Column(Column.Parameter(Name)).As("SchemaName")
.Column(Column.Parameter(ImageId)).As("ImageId")
.From(Visa)
.InnerJoin(Name).On(Name, "Id").IsEqual(Visa, VisaMasterColumn)
.InnerJoin(sysAdminUnitInRoleSelect).As("SysAdminUnitRole")
.On(Visa, "VisaOwnerId").IsEqual("SysAdminUnitRole", "SysAdminUnitRoleId")
.LeftOuterJoin("SysUserInRole")
.On("SysUserInRole", "SysRoleId").IsEqual("SysAdminUnitRole", "SysAdminUnitId")
.Where(Visa, "VisaOwnerId").In(
new Select(UserConnection).Column("SysAdminUnitRoleId").From("SysAdminUnitInRole")
.Where("SysAdminUnitId").IsEqual(Column.Parameter(_sysAdminUnit)))
.And().OpenBlock(Visa, "StatusId").Not().In(Column.Parameters(finallyStatuses))
.Or(Visa, "StatusId").IsNull()
.CloseBlock()
.And(Visa, "IsCanceled").IsEqual(Column.Parameter(false)) as Select;
return(entitiesSelect);
}
private QueryCase GetDefaultResponseCase(bool isSystemEmail)
{
var responseCase = new QueryCase();
MailingDbUtilities.AddWhenCondition(
responseCase, "Contact", "Email", QueryConditionType.Equal, string.Empty,
(int)MailingResponseCode.CanceledBlankEmail);
MailingDbUtilities.AddWhenCondition(
responseCase, "Contact", "Email", QueryConditionType.IsNull, null,
(int)MailingResponseCode.CanceledBlankEmail);
MailingDbUtilities.AddWhenCondition(
responseCase, "Contact", "IsNonActualEmail", QueryConditionType.Equal, true,
(int)MailingResponseCode.CanceledInvalidEmail);
if (!isSystemEmail)
{
MailingDbUtilities.AddWhenCondition(
responseCase, "Contact", "DoNotUseEmail", QueryConditionType.Equal, true,
(int)MailingResponseCode.CanceledDoNotUseEmail);
}
responseCase.ElseExpression =
new QueryColumnExpression(Column.Parameter((int)MailingResponseCode.PostedProvider));
return(responseCase);
}
