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

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