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

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