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