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