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

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