ecomp-portal-BE-common/src/main/java/org/openecomp/portalapp/portal/service/FunctionalMenuServiceImpl.java

   1 /*-
   2  * ================================================================================
   3  * ECOMP Portal
   4  * ================================================================================
   5  * Copyright (C) 2017 AT&T Intellectual Property
   6  * ================================================================================
   7  * Licensed under the Apache License, Version 2.0 (the "License");
   8  * you may not use this file except in compliance with the License.
   9  * You may obtain a copy of the License at
  10  *
  11  *      http://www.apache.org/licenses/LICENSE-2.0
  12  *
  13  * Unless required by applicable law or agreed to in writing, software
  14  * distributed under the License is distributed on an "AS IS" BASIS,
  15  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  16  * See the License for the specific language governing permissions and
  17  * limitations under the License.
  18  * ================================================================================
  19  */
  20 package org.openecomp.portalapp.portal.service;
  21
  22 import java.util.ArrayList;
  23 import java.util.HashMap;
  24 import java.util.List;
  25 import java.util.Map;
  26
  27 import javax.annotation.PostConstruct;
  28 import javax.servlet.http.HttpServletResponse;
  29
  30 import org.apache.commons.lang3.StringUtils;
  31 import org.hibernate.Criteria;
  32 import org.hibernate.Query;
  33 import org.hibernate.Session;
  34 import org.hibernate.SessionFactory;
  35 import org.hibernate.Transaction;
  36 import org.hibernate.criterion.Projections;
  37 import org.hibernate.criterion.Restrictions;
  38 import org.springframework.beans.factory.annotation.Autowired;
  39 import org.springframework.context.annotation.EnableAspectJAutoProxy;
  40 import org.springframework.stereotype.Service;
  41
  42 import org.openecomp.portalsdk.core.logging.logic.EELFLoggerDelegate;
  43 import org.openecomp.portalsdk.core.service.DataAccessService;
  44 import org.openecomp.portalsdk.core.util.SystemProperties;
  45 import org.openecomp.portalapp.portal.domain.EPApp;
  46 import org.openecomp.portalapp.portal.domain.EPUser;
  47 import org.openecomp.portalapp.portal.domain.FunctionalMenuItemWithAppID;
  48 import org.openecomp.portalapp.portal.logging.aop.EPMetricsLog;
  49 import org.openecomp.portalapp.portal.transport.BusinessCardApplicationRole;
  50 import org.openecomp.portalapp.portal.transport.FavoritesFunctionalMenuItem;
  51 import org.openecomp.portalapp.portal.transport.FavoritesFunctionalMenuItemJson;
  52 import org.openecomp.portalapp.portal.transport.FieldsValidator;
  53 import org.openecomp.portalapp.portal.transport.FunctionalMenuItem;
  54 import org.openecomp.portalapp.portal.transport.FunctionalMenuItemWithRoles;
  55 import org.openecomp.portalapp.portal.transport.FunctionalMenuRole;
  56 import org.openecomp.portalapp.portal.utils.EPCommonSystemProperties;
  57 import org.openecomp.portalapp.portal.utils.EcompPortalUtils;
  58
  59 @Service("functionalMenuService")
  60 @org.springframework.context.annotation.Configuration
  61 @EnableAspectJAutoProxy
  62 @EPMetricsLog
  63 public class FunctionalMenuServiceImpl implements FunctionalMenuService {
  64         EELFLoggerDelegate logger = EELFLoggerDelegate.getLogger(FunctionalMenuServiceImpl.class);
  65
  66         private Long ACCOUNT_ADMIN_ROLE_ID = 999L;
  67         private String RESTRICTED_APP_ROLE_ID = "900";
  68
  69         @Autowired
  70         private DataAccessService dataAccessService;
  71         @Autowired
  72         private SessionFactory sessionFactory;
  73
  74         @PostConstruct
  75         private void init() {
  76                 try {
  77                         ACCOUNT_ADMIN_ROLE_ID = Long.valueOf(SystemProperties.getProperty(EPCommonSystemProperties.ACCOUNT_ADMIN_ROLE_ID));
  78                         RESTRICTED_APP_ROLE_ID = SystemProperties.getProperty(EPCommonSystemProperties.RESTRICTED_APP_ROLE_ID);
  79                 } catch(Exception e) {
  80                 }
  81         }
  82
  83         public List<FunctionalMenuItem> getFunctionalMenuItems(EPUser user) {
  84                 List<FunctionalMenuItem> menuItems = new ArrayList<FunctionalMenuItem>();
  85                 return menuItems;
  86         }
  87
  88         public List<FunctionalMenuItem> getFunctionalMenuItems() {
  89                 return getFunctionalMenuItems(false);
  90         }
  91
  92         public List<FunctionalMenuItem> getFunctionalMenuItems(Boolean all) {
  93                 // Divide this into 2 queries: one which returns the bottom-level menu items associated with Restricted apps,
  94                 // and one that returns all the other menu items. Then we can easily add the boolean flag
  95                 // restrictedApp to each FunctionalMenuItem, to be used by the front end.
  96                 String activeWhereClause = "";
  97                 if (! all) {
  98                         activeWhereClause = " AND UPPER(m.active_yn) = 'Y' ";
  99                 }
 100                 String sql = "SELECT m.menu_id, m.column_num, m.text, m.parent_menu_id, m.url, m.active_yn, r.app_id "
 101                                 + "FROM fn_menu_functional m, fn_menu_functional_roles r "
 102                                 + "WHERE m.menu_id = r.menu_id "
 103                                 + activeWhereClause //" AND UPPER(m.active_yn) = 'Y' "
 104                                 + " AND r.role_id != '" + RESTRICTED_APP_ROLE_ID + "' "
 105                         + " UNION "
 106                         + " SELECT m.menu_id, m.column_num, m.text, m.parent_menu_id, m.url, m.active_yn,-1 app_id "
 107                                 + " FROM fn_menu_functional m "
 108                                 + " WHERE m.url='' "
 109                                 + activeWhereClause; //" AND UPPER(m.active_yn) = 'Y' ";
 110                 logQuery(sql);
 111
 112                 @SuppressWarnings("unchecked")
 113                 List<FunctionalMenuItemWithAppID> menuItemsWithAppIdList = dataAccessService.executeSQLQuery(sql,FunctionalMenuItemWithAppID.class, null);
 114                 List<FunctionalMenuItem> menuItems = new ArrayList<>();
 115                 menuItems = transformFunctionalMenuItemWithAppIDToFunctionalMenuItem(menuItemsWithAppIdList);
 116                 for (FunctionalMenuItem menuItem : menuItems) {
 117                         menuItem.restrictedApp = false;
 118                 }
 119
 120                 sql = "SELECT m.menu_id, m.column_num, m.text, m.parent_menu_id, m.url, m.active_yn, r.app_id "
 121                                 + "FROM fn_menu_functional m, fn_menu_functional_roles r "
 122                                 + "WHERE m.menu_id = r.menu_id "
 123                                 + activeWhereClause //" AND UPPER(m.active_yn) = 'Y' "
 124                                 + " AND r.role_id = '" + RESTRICTED_APP_ROLE_ID + "' ";
 125                 logQuery(sql);
 126                 @SuppressWarnings("unchecked")
 127                 List<FunctionalMenuItem> menuItems2 = dataAccessService.executeSQLQuery(sql, FunctionalMenuItem.class, null);
 128                 for (FunctionalMenuItem menuItem : menuItems2) {
 129                         menuItem.restrictedApp = true;
 130                         menuItems.add(menuItem);
 131                 }
 132
 133                 return menuItems;
 134         }
 135
 136
 137         public List<FunctionalMenuItem> getFunctionalMenuItemsForNotificationTree(Boolean all) {
 138                 // Divide this into 2 queries: one which returns the bottom-level menu items associated with Restricted apps,
 139                 // and one that returns all the other menu items which are active. Then we can easily add the boolean flag
 140                 // restrictedApp to each FunctionalMenuItem, to be used by the front end.
 141                 String activeWhereClause = "";
 142                 if (! all) {
 143                         activeWhereClause = " AND UPPER(m.active_yn) = 'Y' ";
 144                 }
 145                 String sql = "SELECT m.menu_id, m.column_num, m.text, m.parent_menu_id, m.url, m.active_yn, r.app_id "
 146                                 + "FROM fn_menu_functional m, fn_menu_functional_roles r "
 147                                 + "WHERE m.menu_id = r.menu_id "
 148                                 + activeWhereClause + " AND UPPER(m.active_yn) = 'Y' "
 149                                 + " AND r.role_id != '" + RESTRICTED_APP_ROLE_ID + "' "
 150                         + " UNION "
 151                         + " SELECT m.menu_id, m.column_num, m.text, m.parent_menu_id, m.url, m.active_yn,-1 app_id "
 152                                 + " FROM fn_menu_functional m "
 153                                 + " WHERE m.url='' "
 154                                 + activeWhereClause + " AND UPPER(m.active_yn) = 'Y' ";
 155                 logQuery(sql);
 156
 157                 @SuppressWarnings("unchecked")
 158                 List<FunctionalMenuItemWithAppID> menuItemsWithAppIdList = dataAccessService.executeSQLQuery(sql,FunctionalMenuItemWithAppID.class, null);
 159                 List<FunctionalMenuItem> menuItems = new ArrayList<>();
 160                 menuItems = transformFunctionalMenuItemWithAppIDToFunctionalMenuItem(menuItemsWithAppIdList);
 161                 for (FunctionalMenuItem menuItem : menuItems) {
 162                         menuItem.restrictedApp = false;
 163                 }
 164
 165                 sql = "SELECT m.menu_id, m.column_num, m.text, m.parent_menu_id, m.url, m.active_yn, r.app_id "
 166                                 + "FROM fn_menu_functional m, fn_menu_functional_roles r "
 167                                 + "WHERE m.menu_id = r.menu_id "
 168                                 + activeWhereClause + " AND UPPER(m.active_yn) = 'Y' "
 169                                 + " AND r.role_id = '" + RESTRICTED_APP_ROLE_ID + "' ";
 170                 logQuery(sql);
 171                 @SuppressWarnings("unchecked")
 172                 List<FunctionalMenuItem> menuItems2 = dataAccessService.executeSQLQuery(sql, FunctionalMenuItem.class, null);
 173                 for (FunctionalMenuItem menuItem : menuItems2) {
 174                         menuItem.restrictedApp = true;
 175                         menuItems.add(menuItem);
 176                 }
 177
 178                 return menuItems;
 179         }
 180
 181         public List<FunctionalMenuItem> getFunctionalMenuItemsForApp(Integer appId) {
 182                 String sql = "SELECT DISTINCT m1.menu_id, m1.column_num, m1.text, m1.parent_menu_id, m1.url, m.active_yn "
 183                                 + " FROM fn_menu_functional m, fn_menu_functional m1, fn_menu_functional_ancestors a, fn_menu_functional_roles mr "
 184                                 + " WHERE " + " mr.app_id='" + appId + "' " + " AND mr.menu_id = m.menu_id " + " AND UPPER(m.active_yn) = 'Y'"
 185                                 + " AND UPPER(m1.active_yn) ='Y'" + " AND a.menu_id = m.menu_id " + " AND a.ancestor_menu_id = m1.menu_id";
 186                 logQuery(sql);
 187                 logger.debug(EELFLoggerDelegate.debugLogger, "getFunctionalMenuItemsForApp: logged the query");
 188
 189                 @SuppressWarnings("unchecked")
 190                 List<FunctionalMenuItem> menuItems = dataAccessService.executeSQLQuery(sql, FunctionalMenuItem.class, null);
 191
 192                 return menuItems;
 193         }
 194         /**
 195          * convert List of FunctionalMenuItemWithAppID into List of FunctionalMenuItem
 196          *
 197          */
 198         public List<FunctionalMenuItem> transformFunctionalMenuItemWithAppIDToFunctionalMenuItem(List<FunctionalMenuItemWithAppID> functionalMenuItemWithAppIDList) {
 199                 List<FunctionalMenuItem> functionalMenuItemList = new ArrayList<FunctionalMenuItem>();
 200                 for (FunctionalMenuItemWithAppID functionalMenuItemWithAppID : functionalMenuItemWithAppIDList) {
 201                         FunctionalMenuItem menuItem = new FunctionalMenuItem();
 202                         menuItem.menuId=functionalMenuItemWithAppID.menuId;
 203                         menuItem.column=functionalMenuItemWithAppID.column;
 204                         menuItem.text=functionalMenuItemWithAppID.text;
 205                         menuItem.parentMenuId=functionalMenuItemWithAppID.parentMenuId;
 206                         menuItem.url=functionalMenuItemWithAppID.url;
 207                         menuItem.active_yn=functionalMenuItemWithAppID.active_yn;
 208                         menuItem.appid=functionalMenuItemWithAppID.appid;
 209                         menuItem.setRoles(functionalMenuItemWithAppID.roles);
 210                         menuItem.restrictedApp=functionalMenuItemWithAppID.restrictedApp;
 211                         functionalMenuItemList.add(menuItem);
 212                 }
 213                 return functionalMenuItemList;
 214         }
 215
 216         public List<FunctionalMenuItem> getFunctionalMenuItemsForUser(String orgUserId) {
 217                 // m represents the functional menu items that are the leaf nodes
 218                 // m1 represents the functional menu items for all the nodes
 219
 220                 // Divide this into 2 queries: one which returns the bottom-level menu items associated with Restricted apps,
 221                 // and one that returns all the other menu items. Then we can easily add the boolean flag
 222                 // restrictedApp to each FunctionalMenuItem, to be used by the front end.
 223                 String sql = "SELECT DISTINCT m1.menu_id, m1.column_num, m1.text, m1.parent_menu_id, m1.url, m.active_yn "
 224                                 + " FROM fn_menu_functional m, fn_menu_functional m1, fn_menu_functional_ancestors a, "
 225                                 + " fn_menu_functional_roles mr, fn_user u , fn_user_role ur " + " WHERE " + " u.org_user_id='" + orgUserId
 226                                 + "' " + " AND u.user_id = ur.user_id " + " AND ur.app_id = mr.app_id " +
 227                                 // " AND ur.role_id = mr.role_id " +
 228                                 " AND (ur.role_id = mr.role_id " + "     OR ur.role_id = '" + ACCOUNT_ADMIN_ROLE_ID + "') "
 229                                 + " AND m.menu_id = mr.menu_id " + " AND UPPER(m.active_yn) = 'Y'" + " AND UPPER(m1.active_yn) ='Y' "
 230                                 + " AND a.menu_id = m.menu_id " + " AND a.ancestor_menu_id = m1.menu_id "
 231                                 + " UNION "
 232                                 // the ancestors of the restricted app menu items
 233                                 + " select m1.menu_id, m1.column_num, m1.text, m1.parent_menu_id, m1.url, m1.active_yn "
 234                                 + " FROM fn_menu_functional m, fn_menu_functional_roles mr, fn_menu_functional m1, "
 235                                 + " fn_menu_functional_ancestors a "
 236                                 + " where a.menu_id = m.menu_id "
 237                                 + " AND a.ancestor_menu_id = m1.menu_id "
 238                                 + " AND m.menu_id != m1.menu_id "
 239                                 + " AND m.menu_id = mr.menu_id "
 240                                 + " AND mr.role_id = '" + RESTRICTED_APP_ROLE_ID + "' "
 241                                 + " AND UPPER(m.active_yn) = 'Y'" + " AND UPPER(m1.active_yn) ='Y' "
 242                                 // Add the Favorites menu item
 243                                 + " UNION "
 244                                 + " SELECT m.menu_id, m.column_num, m.text, m.parent_menu_id, m.url, m.active_yn "
 245                                 + " FROM fn_menu_functional m "
 246                                 + " WHERE m.text in ('Favorites','Get Access','Contact Us','Support','User Guide','Help')";
 247
 248                 logQuery(sql);
 249                 logger.debug(EELFLoggerDelegate.debugLogger, "getFunctionalMenuItemsForUser: logged the query");
 250
 251                 @SuppressWarnings("unchecked")
 252                 List<FunctionalMenuItem> menuItems = dataAccessService.executeSQLQuery(sql, FunctionalMenuItem.class, null);
 253                 for (FunctionalMenuItem menuItem : menuItems) {
 254                         menuItem.restrictedApp = false;
 255                 }
 256
 257                 sql = " SELECT m.menu_id, m.column_num, m.text, m.parent_menu_id, m.url, m.active_yn "
 258                         + " FROM fn_menu_functional m, fn_menu_functional_roles r "
 259                         + " WHERE m.menu_id = r.menu_id "
 260                         + " AND UPPER(m.active_yn) = 'Y' "
 261                         + " AND r.role_id = '" + RESTRICTED_APP_ROLE_ID + "' ";
 262                 logQuery(sql);
 263                 @SuppressWarnings("unchecked")
 264                 List<FunctionalMenuItem> menuItems2 = dataAccessService.executeSQLQuery(sql, FunctionalMenuItem.class, null);
 265                 for (FunctionalMenuItem menuItem : menuItems2) {
 266                         menuItem.restrictedApp = true;
 267                         menuItems.add(menuItem);
 268                 }
 269
 270                 return menuItems;
 271         }
 272
 273         public FunctionalMenuItem getFunctionalMenuItemDetails(Integer menuid) {
 274                 // First, fill in the fields that apply to all menu items
 275
 276                 String sql = "SELECT * FROM fn_menu_functional WHERE menu_id = '" + menuid + "'";
 277                 logQuery(sql);
 278                 @SuppressWarnings("unchecked")
 279                 List<FunctionalMenuItem> menuItems = dataAccessService.executeSQLQuery(sql, FunctionalMenuItem.class, null);
 280                 FunctionalMenuItem menuItem = (menuItems == null || menuItems.isEmpty() ? null : menuItems.get(0));
 281                 // If it is a bottom-level menu item, must fill in the appid and the
 282                 // roles
 283                 sql = "SELECT * FROM fn_menu_functional_roles WHERE menu_id = '" + menuid + "'";
 284                 logQuery(sql);
 285                 @SuppressWarnings("unchecked")
 286                 List<FunctionalMenuRole> roleItems = dataAccessService.executeSQLQuery(sql, FunctionalMenuRole.class, null);
 287                 if (roleItems.size() > 0) {
 288                         Integer appid = roleItems.get(0).appId;
 289                         menuItem.appid = appid;
 290                         List<Integer> roles = new ArrayList<Integer>();
 291                         for (FunctionalMenuRole roleItem : roleItems) {
 292                                 logger.debug(EELFLoggerDelegate.debugLogger, "LR: app_id: " + roleItem.appId + "; role_id: " + roleItem.roleId + "\n");
 293                                 roles.add(roleItem.roleId);
 294                         }
 295                         menuItem.setRoles(roles);
 296                 }
 297
 298                 return menuItem;
 299         }
 300
 301         private FieldsValidator menuItemFieldsChecker(FunctionalMenuItemWithRoles menuItemJson) {
 302                 FieldsValidator fieldsValidator = new FieldsValidator();
 303                 try {
 304                         // TODO: validate all the fields
 305                         @SuppressWarnings("unchecked")
 306                         List<FunctionalMenuItem> functionalMenuItems = dataAccessService.getList(FunctionalMenuItem.class,
 307                                         " where text = '" + menuItemJson.text + "'", null, null);
 308
 309                         boolean dublicatedName = false;
 310                         for (FunctionalMenuItem fnMenuItem : functionalMenuItems) {
 311                                 if (menuItemJson.menuId != null && menuItemJson.menuId.equals(fnMenuItem.menuId)) {
 312                                         // FunctionalMenuItem should not be compared with itself
 313                                         continue;
 314                                 }
 315
 316                                 if (!dublicatedName && fnMenuItem.text.equalsIgnoreCase(menuItemJson.text)) {
 317                                         dublicatedName = true;
 318                                         break;
 319                                 }
 320                         }
 321                         if (dublicatedName) {
 322                                 fieldsValidator.addProblematicFieldName("text");
 323                                 fieldsValidator.httpStatusCode = new Long(HttpServletResponse.SC_CONFLICT);
 324                                 fieldsValidator.errorCode = new Long(EPCommonSystemProperties.DUBLICATED_FIELD_VALUE_ECOMP_ERROR);
 325                                 logger.debug(EELFLoggerDelegate.debugLogger, "In menuItemFieldsChecker, Error: we have an duplicate text field");
 326                         } else if (StringUtils.isEmpty(menuItemJson.text) && menuItemJson.menuId == null) {
 327                                 // text must be non empty for a create. For an edit, can be empty, which means it is a move request.
 328                                 // a null menuId indicates a create.
 329                                 fieldsValidator.addProblematicFieldName("text");
 330                                 fieldsValidator.httpStatusCode = new Long(HttpServletResponse.SC_BAD_REQUEST);
 331                                 logger.debug(EELFLoggerDelegate.debugLogger, "In menuItemFieldsChecker, Error: we have an empty text field");
 332                         } else {
 333                                 // The url, appid, and roles must either be all filled or all empty.
 334                                 Boolean urlIsEmpty = StringUtils.isEmpty(menuItemJson.url);
 335                                 Boolean rolesIsEmpty = menuItemJson.getRoles() == null || menuItemJson.getRoles().isEmpty();
 336                                 Boolean appidIsEmpty = menuItemJson.appid == null || menuItemJson.appid == 0;
 337                                 logger.debug(EELFLoggerDelegate.debugLogger, "LR: menuItemfieldsChecker: urlIsEmpty: " + urlIsEmpty + "; rolesIsEmpty: " + rolesIsEmpty + "; appidIsEmpty: " + appidIsEmpty +"\n");
 338                                 if (!((urlIsEmpty && rolesIsEmpty && appidIsEmpty) || (!urlIsEmpty && !rolesIsEmpty && !appidIsEmpty)))
 339                                 {
 340                                         fieldsValidator.addProblematicFieldName("url,roles,appid");
 341                                         fieldsValidator.httpStatusCode = new Long(HttpServletResponse.SC_BAD_REQUEST);
 342                                         logger.debug(EELFLoggerDelegate.debugLogger, "In menuItemFieldsChecker, Error: we don't have: either all 3 fields empty or all 3 fields nonempty");
 343                                 } else {
 344                                         logger.debug(EELFLoggerDelegate.debugLogger, "In menuItemFieldsChecker, Success: either all 3 fields empty or all 3 fields nonempty");
 345                                 }
 346                         }
 347                 } catch (Exception e) {
 348                         logger.error(EELFLoggerDelegate.errorLogger, "Exception occurred while validating the FunctionalMenuItems. Details: " + EcompPortalUtils.getStackTrace(e));
 349                         fieldsValidator.httpStatusCode = new Long(HttpServletResponse.SC_INTERNAL_SERVER_ERROR);
 350                 }
 351
 352                 return fieldsValidator;
 353         }
 354
 355         // Turn foreign key checks on or off
 356         protected void setForeignKeys(Session localSession, Boolean on) {
 357                 String keyCheck = "0";
 358                 if (on) {
 359                         keyCheck = "1";
 360                 }
 361                 String sql = "set FOREIGN_KEY_CHECKS="+keyCheck;
 362                 logQuery(sql);
 363                 Query query = localSession.createSQLQuery(sql);
 364                 query.executeUpdate();
 365         }
 366
 367         public FieldsValidator createFunctionalMenuItem(FunctionalMenuItemWithRoles menuItemJson) {
 368                 FieldsValidator fieldsValidator = menuItemFieldsChecker(menuItemJson);
 369                 if (fieldsValidator.httpStatusCode.intValue() == HttpServletResponse.SC_OK) {
 370                         logger.debug(EELFLoggerDelegate.debugLogger, "LR: createFunctionalMenuItem: test 1");
 371                         boolean result = false;
 372                         Session localSession = null;
 373                         Transaction transaction = null;
 374                         try {
 375                                 FunctionalMenuItem menuItem = new FunctionalMenuItem();
 376                                 menuItem.appid = menuItemJson.appid;
 377                                 menuItem.setRoles(menuItemJson.getRoles());
 378                                 menuItem.url = menuItemJson.url;
 379                                 menuItem.text = menuItemJson.text;
 380                                 menuItem.parentMenuId = menuItemJson.parentMenuId;
 381                                 menuItem.active_yn = "Y";
 382                                 localSession = sessionFactory.openSession();
 383
 384                                 // If the app is disabled, deactivate the menu item.
 385                                 if (menuItemJson.appid != null) {
 386                                         Long appidLong = Long.valueOf(menuItemJson.appid);
 387                                         EPApp app = (EPApp) localSession.get(EPApp.class, appidLong);
 388                                         if (app != null && ! app.getEnabled()) {
 389                                                 menuItem.active_yn = "N";
 390                                         }
 391                                 }
 392
 393                                 // Set the column number to 1 higher than the highest column
 394                                 // number under this parent.
 395                                 Criteria criteria = localSession.createCriteria(FunctionalMenuItem.class);
 396                                 criteria.setProjection(Projections.max("column"));
 397                                 criteria.add(Restrictions.eq("parentMenuId", menuItem.parentMenuId));
 398                                 Integer maxColumn = (Integer) criteria.uniqueResult();
 399                                 if (maxColumn == null) {
 400                                         maxColumn = 0;
 401                                 }
 402                                 menuItem.column = maxColumn + 1;
 403                                 logger.debug(EELFLoggerDelegate.debugLogger, "about to create menu item: " + menuItem.toString());
 404
 405                                 transaction = localSession.beginTransaction();
 406                                 // localSession.saveOrUpdate(newMenuItem);
 407                                 localSession.save(menuItem);
 408                                 Long menuid = menuItem.menuId;
 409                                 menuItemJson.menuId = menuid;
 410                                 logger.debug(EELFLoggerDelegate.debugLogger, "after saving menu object, new id: " + menuid);
 411
 412                                 // Next, save all the roles
 413
 414                                 addRoles(menuItemJson, localSession);
 415                                 transaction.commit();
 416                                 result = true;
 417                         } catch (Exception e) {
 418                                 EcompPortalUtils.rollbackTransaction(transaction,
 419                                                 "createFunctionalMenuItem rollback, exception = " + e);
 420                                 logger.error(EELFLoggerDelegate.errorLogger, EcompPortalUtils.getStackTrace(e));
 421                         } finally {
 422                                 EcompPortalUtils.closeLocalSession(localSession, "createFunctionalMenuItem");
 423                         }
 424                         if (result) {
 425                         } else {
 426                                 logger.debug(EELFLoggerDelegate.debugLogger, "LR: createFunctionalMenuItem: no result. setting httpStatusCode to "
 427                                                 + HttpServletResponse.SC_INTERNAL_SERVER_ERROR);
 428                                 fieldsValidator.httpStatusCode = new Long(HttpServletResponse.SC_INTERNAL_SERVER_ERROR);
 429                         }
 430                 } else {
 431                         logger.error(EELFLoggerDelegate.errorLogger, "FunctionalMenuServiceImpl.createFunctionalMenuItem: bad request");
 432                 }
 433                 return fieldsValidator;
 434         }
 435
 436         /* Add all the roles in the menu item to the database */
 437         public void addRoles(FunctionalMenuItemWithRoles menuItemJson, Session localSession) {
 438                 logger.debug(EELFLoggerDelegate.debugLogger, "entering addRoles.");
 439                 List<Integer> roles = menuItemJson.getRoles();
 440                 if (roles != null && roles.size() > 0) {
 441                         Integer appid = menuItemJson.appid;
 442                         Long menuid = menuItemJson.menuId;
 443                         for (Integer roleid : roles) {
 444                                 logger.debug(EELFLoggerDelegate.debugLogger, "about to create record for role: " + roleid);
 445                                 FunctionalMenuRole role = new FunctionalMenuRole();
 446                                 role.appId = appid;
 447                                 role.menuId = menuid;
 448                                 role.roleId = roleid;
 449                                 localSession.save(role);
 450                                 logger.debug(EELFLoggerDelegate.debugLogger, "after saving role menu object, new id: " + role.id);
 451                         }
 452                 }
 453         }
 454
 455         /* Delete all the roles associated with the menu item from the database */
 456         public void deleteRoles(Long menuId) {
 457                 dataAccessService.deleteDomainObjects(FunctionalMenuRole.class, "menu_id='" + menuId + "'", null);
 458         }
 459
 460         /* Delete all favorites associated with the menu item from the database */
 461         public void deleteFavorites(Long menuId) {
 462                 dataAccessService.deleteDomainObjects(FavoritesFunctionalMenuItem.class, "menu_id='" + menuId + "'", null);
 463         }
 464
 465         private Boolean parentMenuIdEqual(Integer menuId1, Integer menuId2) {
 466                 return ((menuId1 == null && menuId2 == null) || (menuId1 != null && menuId1.equals(menuId2)));
 467         }
 468
 469         private void updateColumnForSiblings(Session localSession, Long menuId, Integer oldParentMenuId,
 470                         Integer newParentMenuId, Integer oldColumn, Integer newColumn) {
 471                 logger.debug(EELFLoggerDelegate.debugLogger, "entering updateColumnForSiblings");
 472                 Criteria criteria = localSession.createCriteria(FunctionalMenuItem.class);
 473                 criteria.add(Restrictions.ne("menuId", menuId));
 474                 if (parentMenuIdEqual(oldParentMenuId, newParentMenuId)) {
 475                         logger.debug(EELFLoggerDelegate.debugLogger, "moving under the same parent");
 476                         // We are moving to a new position under the same parent
 477                         if (newParentMenuId == null) {
 478                                 logger.debug(EELFLoggerDelegate.debugLogger, "newParentMenuId is null, so using isNull");
 479                                 criteria.add(Restrictions.isNull("parentMenuId"));
 480                         } else {
 481                                 logger.debug(EELFLoggerDelegate.debugLogger, "newParentMenuId is NOT null, so using eq");
 482                                 criteria.add(Restrictions.eq("parentMenuId", newParentMenuId));
 483                         }
 484                         if (oldColumn > newColumn) {
 485                                 logger.debug(EELFLoggerDelegate.debugLogger, "moving to a lower column under the same parent");
 486                                 // We are moving to a lower column under the same parent
 487                                 criteria.add(Restrictions.ge("column", newColumn));
 488                                 criteria.add(Restrictions.lt("column", oldColumn));
 489                                 @SuppressWarnings("unchecked")
 490                                 List<FunctionalMenuItem> menuItems = criteria.list();
 491                                 for (FunctionalMenuItem menuItem : menuItems) {
 492                                         menuItem.column += 1;
 493                                         localSession.save(menuItem);
 494                                 }
 495                         } else if (oldColumn < newColumn) {
 496                                 logger.debug(EELFLoggerDelegate.debugLogger, "moving to a higher column under the same parent");
 497                                 // We are moving to a higher column under the same parent
 498                                 criteria.add(Restrictions.gt("column", oldColumn));
 499                                 criteria.add(Restrictions.le("column", newColumn));
 500                                 @SuppressWarnings("unchecked")
 501                                 List<FunctionalMenuItem> menuItems = criteria.list();
 502                                 for (FunctionalMenuItem menuItem : menuItems) {
 503                                         menuItem.column -= 1;
 504                                         localSession.save(menuItem);
 505                                 }
 506                         } else {
 507                                 // No info has changed
 508                                 logger.debug(EELFLoggerDelegate.debugLogger, "no info has changed, so we are not moving");
 509                         }
 510                 } else {
 511                         logger.debug(EELFLoggerDelegate.debugLogger, "moving under a new parent");
 512                         // We are moving under a new parent.
 513
 514                         // Adjust the children under the old parent
 515                         logger.debug(EELFLoggerDelegate.debugLogger, "about to adjust the children under the old parent");
 516
 517                         // If the parentId is null, must check for its children differently
 518                         if (oldParentMenuId == null) {
 519                                 logger.debug(EELFLoggerDelegate.debugLogger, "oldParentMenuId is null, so using isNull");
 520                                 criteria.add(Restrictions.isNull("parentMenuId"));
 521                         } else {
 522                                 logger.debug(EELFLoggerDelegate.debugLogger, "oldParentMenuId is NOT null, so using eq");
 523                                 criteria.add(Restrictions.eq("parentMenuId", oldParentMenuId));
 524                         }
 525
 526                         criteria.add(Restrictions.gt("column", oldColumn));
 527                         @SuppressWarnings("unchecked")
 528                         List<FunctionalMenuItem> menuItems1 = criteria.list();
 529                         for (FunctionalMenuItem menuItem : menuItems1) {
 530                                 menuItem.column -= 1;
 531                                 localSession.save(menuItem);
 532                         }
 533                         // Adjust the children under the new parent.
 534                         logger.debug(EELFLoggerDelegate.debugLogger, "about to adjust the children under the new parent");
 535                         logger.debug(EELFLoggerDelegate.debugLogger, "get all menu items where menuId!=" + menuId + "; parentMenuId==" + newParentMenuId
 536                                         + "; column>=" + newColumn);
 537                         criteria = localSession.createCriteria(FunctionalMenuItem.class);
 538                         criteria.add(Restrictions.ne("menuId", menuId));
 539                         if (newParentMenuId == null) {
 540                                 logger.debug(EELFLoggerDelegate.debugLogger, "newParentMenuId is null, so using isNull");
 541                                 criteria.add(Restrictions.isNull("parentMenuId"));
 542                         } else {
 543                                 logger.debug(EELFLoggerDelegate.debugLogger, "newParentMenuId is NOT null, so using eq");
 544                                 criteria.add(Restrictions.eq("parentMenuId", newParentMenuId));
 545                         }
 546
 547                         criteria.add(Restrictions.ge("column", newColumn));
 548                         @SuppressWarnings("unchecked")
 549                         List<FunctionalMenuItem> menuItems2 = criteria.list();
 550                         if (menuItems2 != null) {
 551                                 logger.debug(EELFLoggerDelegate.debugLogger, "found " + menuItems2.size() + " menu items");
 552                         } else {
 553                                 logger.debug(EELFLoggerDelegate.debugLogger, "found null menu items");
 554                         }
 555                         for (FunctionalMenuItem menuItem : menuItems2) {
 556                                 menuItem.column += 1;
 557                                 localSession.save(menuItem);
 558                         }
 559                 }
 560                 logger.debug(EELFLoggerDelegate.debugLogger, "done with updateColumnForSiblings");
 561         }
 562
 563         public void removeAppInfo(Session localSession, Long menuId) {
 564                 // Remove the url, role, and app info from a menu item
 565                 FunctionalMenuItem menuItem = (FunctionalMenuItem) localSession.get(FunctionalMenuItem.class, menuId);
 566                 menuItem.url = "";
 567                 deleteRoles(menuId);
 568         }
 569
 570         public FieldsValidator editFunctionalMenuItem(FunctionalMenuItemWithRoles menuItemJson) {
 571                 boolean result                  = false;
 572                 Session localSession    = null;
 573                 Transaction transaction = null;
 574                 Long menuId                     = menuItemJson.menuId;
 575
 576                 logger.debug(EELFLoggerDelegate.debugLogger, "LR: editFunctionalMenuItem: test 1");
 577                 FieldsValidator fieldsValidator = menuItemFieldsChecker(menuItemJson);
 578                 if (fieldsValidator.httpStatusCode.intValue() == HttpServletResponse.SC_OK) {
 579                         // TODO: make sure menuId is here. And, it might not already exist
 580                         // in db table.
 581                         if (menuId == null) {
 582                                 fieldsValidator.httpStatusCode = new Long(HttpServletResponse.SC_BAD_REQUEST);
 583                                 logger.error(EELFLoggerDelegate.errorLogger, "FunctionalMenuServiceImpl.editFunctionalMenuItem: bad request");
 584                         } else {
 585                                 // To simplify the code, assume we will have a transaction
 586                                 try {
 587                                         localSession = sessionFactory.openSession();
 588                                         transaction = localSession.beginTransaction();
 589
 590                                         // Get the existing info associated with menuItem from the DB
 591                                         FunctionalMenuItem menuItem = (FunctionalMenuItem) localSession.get(FunctionalMenuItem.class, menuId);
 592                                         Integer oldColumn = menuItem.column;
 593                                         Integer oldParentMenuId = menuItem.parentMenuId;
 594                                         Integer newColumn = menuItemJson.column;
 595                                         Integer newParentMenuId = menuItemJson.parentMenuId;
 596
 597                                         logger.debug(EELFLoggerDelegate.debugLogger, "prev info: column: " + oldColumn + "; parentMenuId: " + oldParentMenuId);
 598
 599                                         if (menuItemJson.appid != null && menuItemJson.getRoles() != null && !menuItemJson.getRoles().isEmpty()
 600                                                         && menuItemJson.url != null && !menuItemJson.url.isEmpty() && menuItemJson.text != null
 601                                                         && !menuItemJson.text.isEmpty()) {
 602                                                 // Scenario: appid, roles, url and text are all non-null.
 603                                                 // This menu item is associated with an app.
 604                                                 // (Note: this should only occur for a bottom-level menu
 605                                                 // item with no children.)
 606                                                 // 1) Remove all the records from fn_menu_functional_role
 607                                                 // for this menuId.
 608                                                 // 2) Add records to the fn_menu_function_role table for the
 609                                                 // appId and each roleId
 610                                                 // 3) Update the url and text for this menu item.
 611
 612                                                 // Because of foreign key constraints, delete the roles,
 613                                                 // then update the menuItem then add the roles.
 614                                                 deleteRoles(menuId);
 615                                                 // Assumption: this is not a Move, so don't change the
 616                                                 // parentMenuId and column.
 617                                                 menuItem.appid = menuItemJson.appid;
 618                                                 menuItem.setRoles(menuItemJson.getRoles());
 619                                                 menuItem.url = menuItemJson.url;
 620                                                 menuItem.text = menuItemJson.text;
 621
 622                                                 // If the app is disabled, deactivate the menu item.
 623                                                 Long appidLong = Long.valueOf(menuItemJson.appid);
 624                                                 EPApp app = (EPApp) localSession.get(EPApp.class, appidLong);
 625                                                 if (app != null && ! app.getEnabled()) {
 626                                                         menuItem.active_yn = "N";
 627                                                 } else {
 628                                                         menuItem.active_yn = "Y";
 629                                                 }
 630
 631
 632                                                 localSession.update(menuItem);
 633                                                 addRoles(menuItemJson, localSession);
 634
 635                                         } else if (menuItemJson.appid == null && (menuItemJson.getRoles() == null || menuItemJson.getRoles().isEmpty())
 636                                                         && (menuItemJson.url == null || menuItemJson.url.isEmpty()) && menuItemJson.text != null
 637                                                         && !menuItemJson.text.isEmpty()) {
 638                                                 // Scenario: appid, roles and url are all null; text is
 639                                                 // non-null.
 640                                                 // This menu item is NOT associated with an app.
 641                                                 // 1) Remove all the records from fn_menu_functional_role
 642                                                 // for this menuId
 643                                                 // (in case it was previously associated with an app).
 644                                                 // 2) Update the text for this menu item.
 645                                                 // 3) Set the url to ""
 646                                                 deleteRoles(menuId);
 647                                                 // Assumption: this is not a Move, so don't change the
 648                                                 // parentMenuId and column.
 649                                                 menuItem.text = menuItemJson.text;
 650                                                 menuItem.url = "";
 651                                                 menuItem.active_yn = "Y";
 652                                                 localSession.update(menuItem);
 653
 654                                         } else if (newColumn != null) {
 655                                                 // This is a "move" request.
 656                                                 // Menu item has been moved to a different position under
 657                                                 // the same parent, or under a new parent.
 658                                                 logger.debug(EELFLoggerDelegate.debugLogger, "Doing a move operation.");
 659                                                 if (parentMenuIdEqual(oldParentMenuId, newParentMenuId)) {
 660                                                         // The parent is the same. We have just changed the
 661                                                         // column
 662                                                         logger.debug(EELFLoggerDelegate.debugLogger, "moving under the same parent");
 663                                                         menuItem.column = newColumn;
 664                                                         localSession.update(menuItem);
 665                                                 } else {
 666                                                         logger.debug(EELFLoggerDelegate.debugLogger, "moving under a different parent");
 667                                                         menuItem.parentMenuId = newParentMenuId;
 668                                                         menuItem.column = newColumn;
 669                                                         localSession.update(menuItem);
 670                                                         // If we are moving under a new parent, must delete any
 671                                                         // app/role info from
 672                                                         // the new parent, since it is no longer a leaf menu
 673                                                         // item and cannot have app info
 674                                                         // associated with it. The front end will have warned
 675                                                         // the user and gotten confirmation.
 676                                                         if (menuItemJson.parentMenuId != null) {
 677                                                                 Long parentMenuIdLong = new Long(menuItemJson.parentMenuId);
 678                                                                 removeAppInfo(localSession, parentMenuIdLong);
 679                                                                 // deleteRoles(parentMenuIdLong);
 680                                                         }
 681                                                 }
 682                                                 // must update the column for all old and new sibling menu
 683                                                 // items
 684                                                 updateColumnForSiblings(localSession, menuId, oldParentMenuId, newParentMenuId, oldColumn,
 685                                                                 newColumn);
 686                                         }
 687
 688                                         transaction.commit();
 689                                         logger.debug(EELFLoggerDelegate.debugLogger, "LR: editFunctionalMenuItem: finished committing transaction");
 690                                         result = true;
 691                                 } catch (Exception e) {
 692                                         EcompPortalUtils.rollbackTransaction(transaction,
 693                                                         "createFunctionalMenuItem rollback, exception = " + e);
 694                                         logger.error(EELFLoggerDelegate.errorLogger, EcompPortalUtils.getStackTrace(e));
 695                                 } finally {
 696                                         EcompPortalUtils.closeLocalSession(localSession, "editFunctionalMenuItem");
 697                                 }
 698
 699                                 if (result) {
 700                                 } else {
 701                                         logger.debug(EELFLoggerDelegate.debugLogger, "LR: createFunctionalMenuItem: no result. setting httpStatusCode to "
 702                                                         + HttpServletResponse.SC_INTERNAL_SERVER_ERROR);
 703                                         fieldsValidator.httpStatusCode = new Long(HttpServletResponse.SC_INTERNAL_SERVER_ERROR);
 704                                 }
 705                         }
 706                 }
 707
 708                 return fieldsValidator;
 709         }
 710
 711         public FieldsValidator deleteFunctionalMenuItem(Long menuId) {
 712                 FieldsValidator fieldsValidator = new FieldsValidator();
 713                 logger.debug(EELFLoggerDelegate.debugLogger, "LR: deleteFunctionalMenuItem: test 1");
 714                 boolean result = false;
 715                 Session localSession = null;
 716                 Transaction transaction = null;
 717
 718                 try {
 719                         localSession = sessionFactory.openSession();
 720                         transaction = localSession.beginTransaction();
 721                         // We must turn off foreign keys before deleting a menu item. Otherwise there will be a
 722                         // constraint violation from the ancestors table.
 723                         setForeignKeys(localSession, false);
 724                         deleteRoles(menuId);
 725                         logger.debug(EELFLoggerDelegate.debugLogger, "deleteFunctionalMenuItem: after deleting roles");
 726                         deleteFavorites(menuId);
 727                         logger.debug(EELFLoggerDelegate.debugLogger, "deleteFunctionalMenuItem: after deleting favorites");
 728                         localSession.delete(localSession.get(FunctionalMenuItem.class, menuId));
 729                         logger.debug(EELFLoggerDelegate.debugLogger, "deleteFunctionalMenuItem: about to commit");
 730                         transaction.commit();
 731                         result = true;
 732                 } catch (Exception e) {
 733                         EcompPortalUtils.rollbackTransaction(transaction,
 734                                         "deleteFunctionalMenuItem rollback, exception = " + e);
 735                         logger.error(EELFLoggerDelegate.errorLogger, EcompPortalUtils.getStackTrace(e));
 736                 } finally {
 737                         EcompPortalUtils.closeLocalSession(localSession, "deleteFunctionalMenuItem");
 738                 }
 739                 if (result) {
 740                 } else {
 741                         logger.debug(EELFLoggerDelegate.debugLogger, "LR: deleteFunctionalMenuItem: no result. setting httpStatusCode to "
 742                                         + HttpServletResponse.SC_INTERNAL_SERVER_ERROR);
 743                         fieldsValidator.httpStatusCode = new Long(HttpServletResponse.SC_INTERNAL_SERVER_ERROR);
 744                 }
 745                 return fieldsValidator;
 746         }
 747
 748         // Regenerate the fn_menu_functional_ancestors table, which is used
 749         // by the queries that return the functional menu items.
 750         public FieldsValidator regenerateAncestorTable() {
 751                 FieldsValidator fieldsValidator = new FieldsValidator();
 752                 Session localSession = null;
 753                 Transaction transaction = null;
 754
 755                 try {
 756                         localSession = sessionFactory.openSession();
 757                         transaction = localSession.beginTransaction();
 758                         String sql = "DELETE FROM fn_menu_functional_ancestors";
 759                         logQuery(sql);
 760                         Query query = localSession.createSQLQuery(sql);
 761                         query.executeUpdate();
 762                         logger.debug(EELFLoggerDelegate.debugLogger, "regenerateAncestorTable: finished query 1");
 763
 764                         sql = "ALTER TABLE fn_menu_functional_ancestors AUTO_INCREMENT=1";
 765                         logQuery(sql);
 766                         query = localSession.createSQLQuery(sql);
 767                         query.executeUpdate();
 768                         logger.debug(EELFLoggerDelegate.debugLogger, "regenerateAncestorTable: reset AUTO_INCREMENT to 1");
 769
 770                         int depth = 0;
 771                         sql = "INSERT INTO fn_menu_functional_ancestors(menu_id, ancestor_menu_id, depth) "
 772                                         + "SELECT m.menu_id, m.menu_id, " + depth + " FROM fn_menu_functional m";
 773                         logQuery(sql);
 774                         query = localSession.createSQLQuery(sql);
 775                         query.executeUpdate();
 776                         logger.debug(EELFLoggerDelegate.debugLogger, "regenerateAncestorTable: finished query 2");
 777                         for (depth = 0; depth < 3; depth++) {
 778                                 int depthPlusOne = depth + 1;
 779                                 sql = "INSERT INTO fn_menu_functional_ancestors(menu_id, ancestor_menu_id, depth) "
 780                                                 + " SELECT a.menu_id, m.parent_menu_id, " + depthPlusOne
 781                                                 + " FROM fn_menu_functional m, fn_menu_functional_ancestors a " + " WHERE a.depth='" + depth
 782                                                 + "' AND " + " a.ancestor_menu_id = m.menu_id AND " + " m.parent_menu_id != '-1'";
 783                                 logQuery(sql);
 784                                 query = localSession.createSQLQuery(sql);
 785                                 query.executeUpdate();
 786                         }
 787                         logger.debug(EELFLoggerDelegate.debugLogger, "regenerateAncestorTable: finished query 3");
 788                         transaction.commit();
 789                 } catch (Exception e) {
 790                         EcompPortalUtils.rollbackTransaction(transaction,
 791                                         "regenerateAncestorTable rollback, exception = " + e);
 792                         logger.error(EELFLoggerDelegate.errorLogger, EcompPortalUtils.getStackTrace(e));
 793                         fieldsValidator.httpStatusCode = new Long(HttpServletResponse.SC_INTERNAL_SERVER_ERROR);
 794                 } finally {
 795                         EcompPortalUtils.closeLocalSession(localSession, "regenerateAncestorTable");
 796                 }
 797                 return fieldsValidator;
 798         }
 799
 800         private void logQuery(String sql) {
 801                 logger.debug(EELFLoggerDelegate.debugLogger, "Executing query: " + sql);
 802         }
 803
 804         public FieldsValidator setFavoriteItem(FavoritesFunctionalMenuItem menuItemJson) {
 805                 boolean result = false;
 806                 FieldsValidator fieldsValidator = new FieldsValidator();
 807
 808                 Session localSession = null;
 809                 Transaction transaction = null;
 810
 811                 try {
 812             logger.debug(EELFLoggerDelegate.debugLogger, String.format("Before adding favorite for user id:{0} and menu id:{1} ",menuItemJson.userId,menuItemJson.menuId));
 813                         localSession = sessionFactory.openSession();
 814                         transaction = localSession.beginTransaction();
 815                         localSession.save(menuItemJson);
 816                         transaction.commit();
 817                         result = true;
 818             logger.debug(EELFLoggerDelegate.debugLogger, String.format("After adding favorite for user id:{0} and menu id:{1} ",menuItemJson.userId,menuItemJson.menuId));
 819                 } catch (Exception e) {
 820                         EcompPortalUtils.rollbackTransaction(transaction,"setFavoriteItem rollback, exception = " + e);
 821                         logger.error(EELFLoggerDelegate.errorLogger, EcompPortalUtils.getStackTrace(e));
 822                 } finally {
 823                         EcompPortalUtils.closeLocalSession(localSession, "setFavoriteItem");
 824                 }
 825
 826                 if(result) {
 827                 }
 828                 else {
 829                         fieldsValidator.httpStatusCode = new Long(HttpServletResponse.SC_INTERNAL_SERVER_ERROR);
 830                 }
 831
 832                 return fieldsValidator;
 833         }
 834
 835         public List<FavoritesFunctionalMenuItemJson> getFavoriteItems(Long userId) {
 836                 try {
 837                         logger.debug(EELFLoggerDelegate.debugLogger, "Before getting favorites for user id: " + userId);
 838
 839                         // Divide this into 2 queries: one which returns the favorites items associated with Restricted apps,
 840                         // and one that returns all the other favorites items. Then we can easily add the boolean flag
 841                         // restrictedApp to each FavoritesFunctionalMenuItemJson, to be used by the front end.
 842
 843                         String sql = "SELECT f.user_id,f.menu_id,m.text,m.url "
 844                                         + " FROM fn_menu_favorites f, fn_menu_functional m, fn_menu_functional_roles mr "
 845                                         + " WHERE f.user_id='" + userId + "' AND f.menu_id = m.menu_id "
 846                                         + " AND f.menu_id = mr.menu_id "
 847                                         + " AND mr.role_id = '" + RESTRICTED_APP_ROLE_ID + "' ";
 848
 849                         @SuppressWarnings("unchecked")
 850                         List<FavoritesFunctionalMenuItemJson> menuItems = dataAccessService.executeSQLQuery(sql, FavoritesFunctionalMenuItemJson.class, null);
 851                         for (FavoritesFunctionalMenuItemJson menuItem : menuItems) {
 852                                 menuItem.restrictedApp = true;
 853                         }
 854
 855                         sql = "SELECT DISTINCT f.user_id,f.menu_id,m.text,m.url "
 856                                         + " FROM fn_menu_favorites f, fn_menu_functional m, fn_menu_functional_roles mr "
 857                                         + " WHERE f.user_id='" + userId + "' AND f.menu_id = m.menu_id "
 858                                         + " AND f.menu_id = mr.menu_id "
 859                                         + " AND mr.role_id != '" + RESTRICTED_APP_ROLE_ID + "' ";
 860                         @SuppressWarnings("unchecked")
 861                         List<FavoritesFunctionalMenuItemJson> menuItems2 = dataAccessService.executeSQLQuery(sql, FavoritesFunctionalMenuItemJson.class, null);
 862                         for (FavoritesFunctionalMenuItemJson menuItem : menuItems2) {
 863                                 menuItem.restrictedApp = false;
 864                                 menuItems.add(menuItem);
 865                         }
 866
 867                         return menuItems;
 868                 } catch (Exception e) {
 869                         logger.error(EELFLoggerDelegate.errorLogger, "Exception occurred in FunctionalMenuServiceImpl.getFavoriteItems. Details: " + EcompPortalUtils.getStackTrace(e));
 870                         List<FavoritesFunctionalMenuItemJson> menuItems = new ArrayList<FavoritesFunctionalMenuItemJson>();
 871                         return menuItems;
 872                 }
 873         }
 874
 875         public FieldsValidator removeFavoriteItem(Long userId, Long menuId) {
 876                 boolean result = false;
 877                 FieldsValidator fieldsValidator = new FieldsValidator();
 878
 879                 Session localSession = null;
 880                 Transaction transaction = null;
 881
 882                 try {
 883
 884                         FavoritesFunctionalMenuItem menuItemJson = new FavoritesFunctionalMenuItem();
 885                         menuItemJson.userId = userId;
 886                         menuItemJson.menuId = menuId;
 887
 888                         localSession = sessionFactory.openSession();
 889                         transaction = localSession.beginTransaction();
 890                         localSession.delete(menuItemJson);
 891                         localSession.flush();
 892                         transaction.commit();
 893                         result = true;
 894                         logger.debug(EELFLoggerDelegate.debugLogger, String.format("After removing favorite for user id: " + userId + "; menu id: " + menuId));
 895                 } catch (Exception e) {
 896                         EcompPortalUtils.rollbackTransaction(transaction,"removeFavoriteItem rollback, exception = " + e);
 897                         logger.error(EELFLoggerDelegate.errorLogger, EcompPortalUtils.getStackTrace(e));
 898                 } finally {
 899                         EcompPortalUtils.closeLocalSession(localSession, "removeFavoriteItem");
 900                 }
 901
 902                 if(result) {
 903                 }
 904                 else {
 905                         fieldsValidator.httpStatusCode = new Long(HttpServletResponse.SC_INTERNAL_SERVER_ERROR);
 906                 }
 907
 908                 return fieldsValidator;
 909         }
 910
 911         @Override
 912         public void assignHelpURLs(List<FunctionalMenuItem> menuItems) {
 913                 try {
 914                         String user_guide_link = SystemProperties.getProperty(EPCommonSystemProperties.USER_GUIDE_URL);
 915
 916                         for(FunctionalMenuItem menuItem: menuItems){
 917                                 if(menuItem.text.equalsIgnoreCase("Contact Us")){
 918                                         menuItem.setUrl("contactUs");
 919                                         //menuItem.setRestrictedApp(true);
 920                                 }
 921                                 if(menuItem.text.equalsIgnoreCase("Get Access")) {
 922                                         menuItem.setUrl("getAccess");
 923                                 }
 924                                 if(menuItem.text.equalsIgnoreCase("User Guide")) {
 925                                         menuItem.setUrl(user_guide_link);
 926                                         menuItem.setRestrictedApp(true);
 927                                 }
 928                         }
 929                 } catch (Exception e) {
 930                         logger.error(EELFLoggerDelegate.errorLogger, "assignHelpURLs process failed. Details: " + EcompPortalUtils.getStackTrace(e));
 931                 }
 932
 933         }
 934
 935         public List<FunctionalMenuRole> getFunctionalMenuRole() {
 936                 String sql = "SELECT * from fn_menu_functional_roles";
 937                 logQuery(sql);
 938                 logger.debug(EELFLoggerDelegate.debugLogger, "getFunctionalMenuRole: logged the query");
 939
 940                 @SuppressWarnings("unchecked")
 941                 List<FunctionalMenuRole> functionalMenuRole = dataAccessService.executeSQLQuery(sql, FunctionalMenuRole.class, null);
 942
 943                 return functionalMenuRole;
 944         }
 945
 946         @SuppressWarnings("unchecked")
 947         @Override
 948         public List<BusinessCardApplicationRole> getUserAppRolesList(String userId) {
 949                 Map<String, String> params = new HashMap<>();
 950                 params.put("userId", userId);
 951
 952                 List<BusinessCardApplicationRole> userAppRoles = null;
 953                 try {
 954                         userAppRoles = dataAccessService
 955                                         .executeNamedQuery("getUserApproles", params, null);
 956                 } catch (Exception e) {
 957                         // TODO Auto-generated catch block
 958                         logger.error(EELFLoggerDelegate.errorLogger, "getUserAppRolesList failed", e);
 959                 }
 960                 return userAppRoles;
 961         }
 962
 963 }