/// <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);
}
/// <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));
}
