private Select GetParticipantsAnalyticsSelect(ParticipantsAnalyticsRequest analyticsRequest)
{
var select = new Select(UserConnection)
.Column("cp", "CampaignItemId")
.Column("cp", "StepCompleted")
.Column("cp", "StatusId")
.Column(Func.Count("cp", "CampaignItemId")).As("Count")
.From("CampaignParticipant").As("cp")
.Where("cp", "CampaignId").IsEqual(Column.Parameter(analyticsRequest.CampaignId))
.GroupBy("cp", "CampaignItemId")
.GroupBy("cp", "StatusId")
.GroupBy("cp", "StepCompleted") as Select;
select.SpecifyNoLockHints();
if (analyticsRequest.UseTimeFilters)
{
var filterStartDateCondition = select.AddCondition(select.SourceExpression.Alias, "CreatedOn",
LogicalOperation.And);
filterStartDateCondition.IsGreaterOrEqual(Column.Parameter(analyticsRequest.FilterStartDate));
var filterDueDateCondition = select.AddCondition(select.SourceExpression.Alias, "CreatedOn",
LogicalOperation.And);
filterDueDateCondition.IsLessOrEqual(Column.Parameter(analyticsRequest.FilterDueDate));
}
return(select);
}
protected virtual void ApplyLookupColumnsConditions(Select select, Entity entity)
{
select
.AddCondition("ParameterTypeId", LogicalOperation.And).IsEqual(Column.Parameter(entity.GetTypedColumnValue <Guid>("ParameterTypeId")))
.And("PartnerParamCategoryId").IsEqual(Column.Parameter(entity.GetTypedColumnValue <Guid>("PartnerParamCategoryId")))
.And("PartnerLevelId").IsEqual(Column.Parameter(entity.GetTypedColumnValue <Guid>("PartnerLevelId")));
}
protected virtual void ApplyOpenPeriodColumnsConditions(Select select, DateTime startDate)
{
select.AddCondition(LogicalOperation.And)
.OpenBlock("DueDate").IsNull()
.Or("DueDate").IsGreaterOrEqual(Column.Parameter(startDate))
.CloseBlock();
}
/// <summary>
/// Calculate completeness value from detail.
/// </summary>
/// <param name="param">Completeness parameter for detail completeness.</param>
/// <param name="recordId">Record id.</param>
/// <returns>Completeness value.</returns>
protected int GetCompletenessFromDetail(CompletenessParameter param, Guid recordId)
{
int detailCompleteness = 0;
Select entitiesSelect =
new Select(AppConnection.SystemUserConnection)
.Column(Func.Count(param.DetailColumn))
.From(param.DetailEntityName)
.Where(param.DetailColumn).IsEqual(new QueryParameter(recordId)) as Select;
if (!string.IsNullOrEmpty(param.TypeColumn) && !param.TypeValue.IsEmpty())
{
var typeCondition = new QueryCondition {
LeftExpression = new QueryColumnExpression(param.TypeColumn)
};
typeCondition.In(new QueryParameter(param.TypeValue));
entitiesSelect.AddCondition(typeCondition, LogicalOperation.And);
}
entitiesSelect.SpecifyNoLockHints();
using (DBExecutor dbExecutor = EnsureSystemUserDbConnection()) {
using (IDataReader dataReader = entitiesSelect.ExecuteReader(dbExecutor)) {
while (dataReader.Read())
{
if (Convert.ToInt32(dataReader.GetValue(0)) > 0)
{
detailCompleteness = param.Percentage;
}
}
}
}
return(detailCompleteness);
}
public void ApplyCustomConditions(string schemaName, Select select)
{
var sysModuleEntityInPortalSelect = new Select(UserConnection)
.Column("SysModuleEntityId")
.From("SysModuleEntityInPortal") as Select;
select.AddCondition(schemaName, "SysModuleEntityId", LogicalOperation.And).Not().In(sysModuleEntityInPortalSelect);
}
protected void ApplyDeactivatedRecordsConditions(Select select, EntitySchema schema)
{
if (schema.UseRecordDeactivation)
{
string inactiveColumnName = schema.GetInactiveColumn().Name;
select.AddCondition(inactiveColumnName, LogicalOperation.And)
.IsEqual(Column.Const(false));
}
}
/// <summary>
/// Gets the hierarchy rows.
/// </summary>
/// <param name="sheet">The forecast sheet <see cref="Sheet" />.</param>
/// <param name="hierarchy">The hierarchy <see cref="HierarchySettingItem" />.</param>
/// <param name="pageableConfig">The pageable configuration <see cref="PageableConfig" />.</param>
/// <returns> Collection of <see cref="HierarchyRow" /></returns>
public IEnumerable <HierarchyRow> GetHierarchyRows(Sheet sheet,
IEnumerable <HierarchySettingItem> hierarchy, PageableConfig pageableConfig)
{
sheet.CheckArgumentNull(nameof(sheet));
pageableConfig.CheckArgumentNull(nameof(pageableConfig));
EntitySchema entitySchema = UserConnection.EntitySchemaManager.GetInstanceByUId(sheet.ForecastEntityUId);
var level = pageableConfig.HierarchyLevel;
var columns = GetHierarchyColumns(entitySchema, hierarchy).Where(c => c.Key <= level);
Select select = GetForecastEntitySelect(entitySchema.Name, sheet);
AddColumnsToSelect(select, entitySchema, columns);
var rightsCondition = GetRightsCondition(select, entitySchema);
if (level > 0)
{
AddConditionsToSelect(select, columns.Where(c => c.Key < level), pageableConfig.HierarchyRowsId);
}
if (rightsCondition != null)
{
select.AddCondition(rightsCondition, LogicalOperation.And);
}
if (!string.IsNullOrEmpty(pageableConfig.PrimaryFilterValue))
{
EntitySchema forecastEntitySchema = UserConnection.EntitySchemaManager.GetInstanceByName(entitySchema.Name);
select.AddCondition(Func.Upper(FORECAST_ENTITY_ALIAS, forecastEntitySchema.PrimaryDisplayColumn.Name),
LogicalOperation.And)
.IsLike(Column.Parameter($"%{pageableConfig.PrimaryFilterValue.ToUpper()}%"));
}
if (pageableConfig.RowCount > 0 && pageableConfig.RowsOffset >= 0)
{
select = GetPageableSelect(select, pageableConfig);
}
if (!select.HasOrderByItems)
{
select.OrderByAsc(select.Columns.First());
}
var lastHierarchyColumn = columns.First(c => c.Key == level);
var emptyValue = GetEmptyHierarchyValue(lastHierarchyColumn.Value, entitySchema);
var result = GetFilledHierarchyRows(select, level, emptyValue);
return(result);
}
private void AddRightsConditions(Sheet sheet, Select select, EntitySchema entitySchema)
{
var rightsCondition = GetRightsCondition(select, entitySchema);
if (rightsCondition != null)
{
select.AddCondition(rightsCondition, LogicalOperation.And);
}
if (UserConnection.GetIsFeatureEnabled("ForecastRowRights"))
{
AddForecastRowRightsCondition(sheet, select);
}
}
private Select GetSelectWithAggregationColumnsConditions(Select select, string[] aggregationColumns, string detailNameAlias)
{
foreach (string columnName in aggregationColumns)
{
QueryCondition condition = select.AddCondition(LogicalOperation.And);
condition
.OpenBlock(select.SourceExpression.Alias, columnName)
.IsEqual(detailNameAlias, columnName)
.Or()
.OpenBlock(select.SourceExpression.Alias, columnName)
.IsNull()
.And(detailNameAlias, columnName).IsNull()
.CloseBlock()
.CloseBlock();
}
return(select);
}
private void AddConditionsToSelect(Select select, IEnumerable <KeyValuePair <int, string> > columns, IEnumerable <Guid> hierarchyRowsId)
{
columns.ForEach(c => {
var value = hierarchyRowsId.ElementAt(c.Key);
var sourceAlias = select.Columns.GetByAlias($"hierarchyColumn{c.Key}Id")?.SourceAlias;
if (sourceAlias.IsNotNullOrEmpty())
{
string columnName = "Id";
var condition = select.AddCondition(sourceAlias, columnName, LogicalOperation.And);
if (value.IsEmpty())
{
condition.IsNull();
}
else
{
condition.IsEqual(Column.Const(value));
}
}
});
}
private Update CreateConversionDuplicatesToGoldUpdate(string detailName, string recordIdColumnName,
string[] aggregationColumns, Guid goldRecoredId, List <Guid> duplicateRecoredIds)
{
var detailNameAlias = $"{detailName}RootSchema";
Select excludeGoldRecordSelect = new Select(UserConnection)
.Column(Column.Const(null))
.From(detailName).As(detailName);
excludeGoldRecordSelect = GetSelectWithAggregationColumnsConditions(excludeGoldRecordSelect, aggregationColumns, detailNameAlias);
excludeGoldRecordSelect.AddCondition(excludeGoldRecordSelect.SourceExpression.Alias, recordIdColumnName,
LogicalOperation.And).IsEqual(Column.Parameter(goldRecoredId));
Select recordIdInAggregated = new Select(UserConnection)
.Top(1)
.Column("Id")
.From(detailName).As(detailName);
recordIdInAggregated = GetSelectWithAggregationColumnsConditions(recordIdInAggregated, aggregationColumns, detailNameAlias);
List <QueryParameter> duplicateIds
= duplicateRecoredIds.Select(recordId => new QueryParameter(recordId)).ToList();
Select recordIdsForUpdate = new Select(UserConnection)
.Column(recordIdInAggregated).As("Id")
.From(detailName).As(detailNameAlias)
.Where(recordIdColumnName).In(duplicateIds)
.And().Not().Exists(excludeGoldRecordSelect) as Select;
foreach (string columnName in aggregationColumns)
{
recordIdsForUpdate.GroupBy(detailNameAlias, columnName);
}
string modifiedOnColumnName = FindModifiedOnColumn(detailName);
Update update = new Update(UserConnection, detailName)
.Set(recordIdColumnName, Column.Parameter(goldRecoredId));
if (modifiedOnColumnName != "")
{
update.Set(modifiedOnColumnName, Column.Parameter(DateTime.UtcNow));
}
update.Where("Id").In(recordIdsForUpdate);
return(update);
}
private void AddForecastRowRightsCondition(Sheet sheet, Select select)
{
EntitySchema entityInCellSchema = UserConnection.EntitySchemaManager
.GetInstanceByUId(sheet.ForecastEntityInCellUId);
var securityEngine = UserConnection.DBSecurityEngine;
EntitySchema forecastRowSchema = UserConnection.EntitySchemaManager.GetInstanceByName(ForecastRowSchemaName);
var forecastRowCondition = securityEngine.GetRecordsByRightCondition(new RecordsByRightOptions {
EntitySchemaName = ForecastRowSchemaName,
EntitySchemaSourceAlias = ForecastRowAlias,
RightEntitySchemaName = securityEngine.GetRecordRightsSchemaName(ForecastRowSchemaName),
Operation = Core.Configuration.EntitySchemaRecordRightOperation.Read,
PrimaryColumnName = IdColumnName,
UserId = UserConnection.CurrentUser.Id,
UseDenyRecordRights = forecastRowSchema.UseDenyRecordRights
});
if (forecastRowCondition != null)
{
select.InnerJoin(ForecastRowSchemaName).As(ForecastRowAlias).On(ForecastRowAlias, IdColumnName)
.IsEqual(entityInCellSchema.Name, "RowId");
select.AddCondition(forecastRowCondition, LogicalOperation.And);
}
}
protected void ApplyExcludeCurrentRecordConditions(Select select, TEntity entity)
{
select.AddCondition(entity.Schema.GetPrimaryColumnName(), LogicalOperation.And)
.IsNotEqual(Column.Parameter(entity.PrimaryColumnValue));
}