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