public async Task <List <PersonForPanel> > PersonList(SipUser CurrentUser, int PageSectionId) { //string PersonConditionSQLFrom = " DECLARE @LanguageId int; " + // " SELECT @LanguageId = IntPreference " + // " FROM UserPreferences " + // " WHERE USerId = '" + CurrentUser.Id + "' " + // " AND UserPreferences.PreferenceTypeId = 1; " + // " SELECT ISNULL(Salutation, '') Salutation " + // " , ISNULL(FirstName, '')FirstName " + // " , ISNULL(MiddleName, '') MiddleName " + // " , ISNULL(LastName, '') LastName " + // " , ISNULL(NickName, '') NickName " + // " , ISNULL(FirstNameLocal, '') FirstNameLocal " + // " , ISNULL(MiddleNameLocal, '') MiddleNameLocal " + // " , ISNULL(LastNameLocal, '') LastNameLocal " + // " , CASE WHEN UserID IS NULL THEN 1 ELSE 0 END AS IsUser " + // " , ISNULL(UserLanguage.Name, ISNULL(DefaultLanguage.Name, 'No name for the organization')) DefaultOrganizationName " + // " , Creator.FirstName + ' ' + Creator.LastName CreatorName " + // " , Creator.PersonID CreatorID " + // " , Persons.CreatedDate " + // " , Modifier.FirstName + ' ' + Modifier.LastName ModifierName " + // " , Modifier.PersonId ModifierID " + // " , Persons.ModifiedDate " + // " , CONCAT('controlID', Persons.PersonId) ControlId " + // " FROM Persons " + // " LEFT JOIN(SELECT OrganizationId, Name FROM OrganizationLanguages WHERE LanguageId = @LanguageID) UserLanguage " + // " ON UserLanguage.OrganizationID = Persons.DefaultOrganizationID " + // " LEFT JOIN(SELECT OrganizationId, Name FROM OrganizationLanguages JOIN Settings ON OrganizationLanguages.LanguageId = Settings.IntValue WHERE Settings.SettingId = 1) DefaultLanguage " + // " ON DefaultLanguage.OrganizationId = Persons.DefaultOrganizationID " + // " JOIN Persons Creator " + // " ON Creator.UserId = Persons.CreatorID " + // " JOIN Persons Modifier " + // " ON Modifier.UserId = Persons.ModifierID "; string PersonConditionSQLFrom = " "; string PersonConditionSQLWhere = " WHERE 1=1 "; string PersonConditionSQLContains = ""; var PersonConditions = await _personProvider.PanelCondition(PageSectionId); foreach (var PersonCondition in PersonConditions) { switch (PersonCondition.PageSectionPersonConditionTypeId) { case 1: // Contains PersonConditionSQLWhere += " AND CONTAINS (Persons.FullText, '" + PersonCondition.PageSectionPersonConditionString + "' ) "; // PersonConditionSQLWhere += " AND CONTAINS = " + PersonCondition.PageSectionPersonConditionInt; break; case 2: // Default organization PersonConditionSQLWhere += " AND Persons.DefaultOrganizationId = " + PersonCondition.PageSectionPersonConditionInt; break; case 3: // Organization PersonConditionSQLWhere += " AND Persons.PersonId IN (SELECT PersonId FROM AspNetRoles JOIN AspNetUserRoles ON AspNetRoles.Id = AspNetUserRoles.RoleId JOIN Persons ON Persons.UserID = AspNetUserRoles.UserId WHERE AspNetRoles.OrganizationId = " + PersonCondition.PageSectionPersonConditionInt + " ) "; break; case 4: // Project PersonConditionSQLWhere += " AND Persons.PersonId IN (SELECT PersonId FROM AspNetRoles JOIN AspNetUserRoles ON AspNetRoles.Id = AspNetUserRoles.RoleId JOIN Persons ON Persons.UserID = AspNetUserRoles.UserId WHERE AspNetRoles.ProjectId = " + PersonCondition.PageSectionPersonConditionInt + " ) "; break; case 5: // Country PersonConditionSQLWhere += " AND Persons.PersonId IN (SELECT PersonId FROM PersonAddresses WHERE CountryId = " + PersonCondition.PageSectionPersonConditionInt + " ) "; break; case 6: // State / Province PersonConditionSQLWhere += " AND Persons.PersonId IN (SELECT PersonId FROM PersonAddresses WHERE ProvinceState LIKE '%" + PersonCondition.PageSectionPersonConditionString + "%' ) "; break; case 7: // City PersonConditionSQLWhere += " AND Persons.PersonId IN (SELECT PersonId FROM PersonAddresses WHERE City LIKE '%" + PersonCondition.PageSectionPersonConditionString + "%' ) "; break; case 8: // My relations PersonConditionSQLWhere += " AND ( Persons.PersonId IN (SELECT FromPersonId FROM PersonRelations JOIN Persons ON PersonRelations.ToPersonID = PersonId WHERE UserId = '" + CurrentUser.Id + "') OR Persons.PersonId IN (SELECT ToPersonId FROM PersonRelations JOIN Persons ON PersonRelations.FromPersonID = PersonId WHERE UserId = '" + CurrentUser.Id + "') ) "; break; case 9: // Is user if (PersonCondition.PageSectionPersonConditionInt == 1) { PersonConditionSQLWhere += " AND Persons.UserId IS NOT NULL "; } else { PersonConditionSQLWhere += " AND Persons.UserId IS NULL "; } break; } } var PersonList = await _personProvider.Panel(CurrentUser.Id, PersonConditionSQLFrom, PersonConditionSQLWhere, PersonConditionSQLContains); return(PersonList); }