public async Task <List <OrganizationForPanel> > OrganizationList(SipUser CurrentUser, int PageSectionId) { //string OrganizationConditionSQLFrom = " DECLARE @LanguageId int; " + // " SELECT @LanguageId = IntPreference " + // " FROM UserPreferences " + // " WHERE USerId = '" + CurrentUser.Id + "' " + // " AND UserPreferences.PreferenceTypeId = 1; " + // " SELECT " + // " Organizations.OrganizationID " + // " , Organizations.OrganizationTypeID " + // " , ISNULL(UserLanguage.Name, ISNULL(DefaultLanguage.Name, \"No name for this organization\")) Name " + // " , ISNULL(UserLanguage.Description, ISNULL(DefaultLanguage.Description, \"No description for this organization\")) Description " + // " , ISNULL(UserLanguage.MenuName, ISNULL(DefaultLanguage.MenuName, \"No menu name for this organization\")) MenuName " + // " , ISNULL(UserLanguage.MouseOver, ISNULL(DefaultLanguage.MouseOver, \"No mouse over for this organization\")) MouseOver " + // " , ISNULL(UserTypeLanguage.Name, ISNULL(DefaultTypeLanguage.Name, \"No name for this organization type\")) OrganizationTypeName " + // " , ISNULL(UserStatusName.Customization, StatusName.Name) StatusName " + // " , OrganizationTypes.Internal " + // " , OrganizationTypes.LegalEntity " + // " , Creator.FirstName + ' ' + Creator.LastName CreatorName " + // " , Creator.PersonID CreatorID " + // " , Organizations.CreatedDate " + // " , Modifier.FirstName + ' ' + Modifier.LastName ModifierName " + // " , Modifier.PersonId ModifierID " + // " , Organizations.ModifiedDate " + // " , CONCAT('controlID', Organizations.OrganizationId) ControlId " + // " FROM Organizations JOIN Statuses " + // " ON Statuses.StatusId = Organizations.StatusID " + // " JOIN OrganizationTypes " + // " ON Organizations.OrganizationTypeId = OrganizationTypes.OrganizationTypeID " + // " LEFT JOIN(SELECT OrganizationId, Name, Description, MenuName, MouseOver FROM OrganizationLanguages WHERE LanguageId = @LanguageID) UserLanguage " + // " ON UserLanguage.OrganizationID = Organizations.OrganizationID " + // " LEFT JOIN(SELECT OrganizationId, Name, Description, MenuName, MouseOver FROM OrganizationLanguages JOIN Settings ON OrganizationLanguages.LanguageId = Settings.IntValue WHERE Settings.SettingId = 1) DefaultLanguage " + // " ON DefaultLanguage.OrganizationId = Organizations.OrganizationID " + // " LEFT JOIN(SELECT OrganizationTypeId, Name FROM OrganizationTypeLanguages WHERE LanguageId = @LanguageID) UserTypeLanguage " + // " ON UserTypeLanguage.OrganizationTypeID = Organizations.OrganizationTypeID " + // "LEFT JOIN(SELECT OrganizationTypeId, Name FROM OrganizationTypeLanguages JOIN Settings ON OrganizationTypeLanguages.LanguageId = Settings.IntValue WHERE Settings.SettingId = 1) DefaultTypeLanguage " + // " ON DefaultTypeLanguage.OrganizationTypeID = Organizations.OrganizationTypeID " + // " JOIN UITermLanguages StatusName " + // " ON Statuses.NameTermId = StatusName.UITermId " + // " LEFT JOIN(SELECT* FROM UITermLanguageCustomizations WHERE UITermLanguageCustomizations.LanguageId = @LanguageID) UserStatusName " + // " ON Statuses.NameTermId = UserStatusName.UITermId" + // " JOIN Persons Creator " + // " ON Creator.UserId = Organizations.CreatorID " + // " JOIN Persons Modifier " + // " ON Modifier.UserId = Organizations.ModifierID "; string OrganizationConditionSQLFrom = " "; string OrganizationConditionSQLWhere = " WHERE StatusName.LanguageId = @LanguageId "; string OrganizationConditionSQLContains = ""; var OrganizationConditions = await _organizationProvider.PanelCondition(PageSectionId); foreach (var OrganizationCondition in OrganizationConditions) { switch (OrganizationCondition.PageSectionOrganizationConditionTypeId) { case 1: // Contains OrganizationConditionSQLFrom += " JOIN ( SELECT OrganizationId FROM OrganizationLanguages WHERE LanguageId = @LanguageID AND CONTAINS (OrganizationLanguages.FullText, '" + OrganizationCondition.PageSectionOrganizationConditionString + "' )) OrganizationTable" + OrganizationCondition.PageSectionOrganizationConditionId + " ON OrganizationTable" + OrganizationCondition.PageSectionOrganizationConditionId + ".OrganizationId = Organizations.OrganizationID "; // OrganizationConditionSQLWhere += " AND CONTAINS = " + OrganizationCondition.PageSectionOrganizationConditionInt; break; case 2: // Parent organization OrganizationConditionSQLWhere += " AND Organizations.ParentOrganizationId = " + OrganizationCondition.PageSectionOrganizationConditionInt; break; case 3: // Country OrganizationConditionSQLWhere += " AND Organizations.OrganizationId IN (SELECT OrganizationId FROM OrganizationAddresses WHERE CountryId = " + OrganizationCondition.PageSectionOrganizationConditionInt + " ) "; break; case 4: // State province OrganizationConditionSQLWhere += " AND Organizations.OrganizationId IN (SELECT OrganizationId FROM OrganizationAddresses WHERE ProvinceState LIKE '%" + OrganizationCondition.PageSectionOrganizationConditionString + "%' ) "; break; case 5: // Organization type OrganizationConditionSQLWhere += " AND Organizations.OrganizationTypeId = " + OrganizationCondition.PageSectionOrganizationConditionInt; break; case 6: // My organization OrganizationConditionSQLWhere += " AND Organizations.OrganizationId IN (SELECT AspNetRoles.OrganizationId FROM AspNetRoles JOIN AspNetUserRoles ON AspNetRoles.Id JOIN AspNetUserRoles.RoleId WHERE AspNetUserRoles.UserId = '" + CurrentUser.Id + "' ) "; break; case 7: // Internal OrganizationConditionSQLWhere += " AND Organizations.OrganizationTypeId IN (SELECT OrganizationTypeId FROM OrganizationTypes WHERE Internal = " + OrganizationCondition.PageSectionOrganizationConditionInt + " )"; break; case 8: // City OrganizationConditionSQLWhere += " AND Organizations.OrganizationId IN (SELECT OrganizationId FROM OrganizationAddresses WHERE City LIKE '%" + OrganizationCondition.PageSectionOrganizationConditionString + "%' ) "; break; case 9: // Legal entity OrganizationConditionSQLWhere += " AND Organizations.OrganizationTypeId IN (SELECT OrganizationTypeId FROM OrganizationTypes WHERE LegalEntity = " + OrganizationCondition.PageSectionOrganizationConditionInt + " )"; break; } } var OrganizationList = await _organizationProvider.Panel(CurrentUser.Id, OrganizationConditionSQLFrom, OrganizationConditionSQLWhere, OrganizationConditionSQLContains); return(OrganizationList); }