/**
   * Get viewable subsystem list
   *
   * @param request
   * @return never null, elements are nds.schema.SubSystem
   */
  public List getSubSystems(HttpServletRequest request) {
    UserWebImpl userWeb =
        ((UserWebImpl)
            WebUtils.getSessionContextManager(request.getSession())
                .getActor(nds.util.WebKeys.USER));
    ArrayList subs = new ArrayList();
    if (userWeb.getUserId() == userWeb.GUEST_ID) {
      return subs;
    }
    List al = (List) userWeb.getProperty("subsystems"); // elements are subystem.id
    TableManager manager = TableManager.getInstance();
    if (al != null) {

      for (int i = 0; i < al.size(); i++) {
        int sid = ((Integer) al.get(i)).intValue();
        SubSystem ss = manager.getSubSystem(sid);
        if (ss != null) subs.add(ss);
      }
    } else {
      // search all tablecategoris for subsystem
      // add users subsystems param
      al = new ArrayList();
      String[] sub_list;
      try {
        String subsystems =
            (String)
                QueryEngine.getInstance()
                    .doQueryOne("SELECT subsystems from users where id=" + userWeb.getUserId());
        if (Validator.isNotNull(subsystems)) {
          sub_list = subsystems.split(",");
          for (int m = 0; m < sub_list.length; m++) {
            SubSystem usersub = manager.getSubSystem(sub_list[m].trim());

            if (usersub != null) {
              if (usersub.getId() == 10) continue;
              al.add(new Integer(usersub.getId()));
              subs.add(usersub);
            }
          }
          userWeb.setProperty("subsystems", al);
          return subs;
        }
      } catch (QueryException e) {
        logger.error("Fail to load subsystems from users", e);
      }

      for (int i = 0; i < manager.getSubSystems().size(); i++) {
        SubSystem ss = (SubSystem) manager.getSubSystems().get(i);
        if (containsViewableChildren(request, ss)) {
          al.add(new Integer(ss.getId()));
          subs.add(ss);
        }
      }
      userWeb.setProperty("subsystems", al);
    }
    return subs;
  }
  /**
   * @param request
   * @param subSystemId
   * @return
   */
  private boolean containsViewableActions(HttpServletRequest request, SubSystem ss) {
    List<WebAction> list = ss.getWebActions();
    Connection conn = null;
    try {
      UserWebImpl userWeb =
          ((UserWebImpl)
              WebUtils.getSessionContextManager(request.getSession())
                  .getActor(nds.util.WebKeys.USER));
      conn = QueryEngine.getInstance().getConnection();
      HashMap webActionEnv = new HashMap();
      webActionEnv.put("connection", conn);
      webActionEnv.put("httpservletrequest", request);
      webActionEnv.put("userweb", userWeb);

      for (int i = 0; i < list.size(); i++) {
        WebAction wa = list.get(i);
        if (wa.canDisplay(webActionEnv)) {
          return true;
        }
      }
    } catch (Throwable t) {
      logger.error("Fail to load subsystem webaction", t);
    } finally {
      try {
        if (conn != null) conn.close();
      } catch (Throwable te) {
      }
    }
    return false;
  }
  /**
   * @param request
   * @param permissionType PERMISSION_VIEWABLE, PERMISSION_NO_PERM or PERMISSION_NO_LICENSE
   * @return never null, elements are nds.schema.SubSystem
   */
  public List<SubSystem> getSubSystems(HttpServletRequest request, int permissionType)
      throws Exception {
    if (permissionType == PERMISSION_VIEWABLE) return getSubSystems(request);

    UserWebImpl userWeb =
        ((UserWebImpl)
            WebUtils.getSessionContextManager(request.getSession())
                .getActor(nds.util.WebKeys.USER));

    String subsystems =
        (String)
            QueryEngine.getInstance()
                .doQueryOne("SELECT subsystems from users where id=" + userWeb.getUserId());
    if (Validator.isNotNull(subsystems)) {
      return Collections.EMPTY_LIST;
    }
    TableManager manager = TableManager.getInstance();

    if (permissionType == PERMISSION_NO_PERM) {
      ArrayList subs = new ArrayList();
      List al = (List) userWeb.getProperty("subsystems"); // elements are subystem.id
      if (al == null) {
        getSubSystems(request);
        al = (List) userWeb.getProperty("subsystems");
      }
      //			 no perm
      List ss = manager.getSubSystems();
      for (int i = 0; i < ss.size(); i++) {
        SubSystem sa = (SubSystem) ss.get(i);
        boolean found = false;
        for (int j = 0; j < al.size(); j++) {
          if (((Integer) al.get(j)).intValue() == sa.getId()) {
            found = true;
            break;
          }
        }
        if (!found) subs.add(sa);
      }
      return subs;
    } // else{
    // no license
    if (subSystemNoLicense == null) {
      subSystemNoLicense = new ArrayList<SubSystem>();
      List al =
          QueryEngine.getInstance()
              .doQueryList(
                  "select id, name, orderno, iconurl,url from ad_subsystem s where exists(select 1 from ad_tablecategory c where c.ad_subsystem_id=s.id) order by orderno asc");
      for (int i = 0; i < al.size(); i++) {
        List als = (List) al.get(i);
        if (manager.getSubSystem(Tools.getInt(als.get(0), -1)) == null) {
          SubSystem ss = new SubSystem();
          ss.setId(Tools.getInt(als.get(0), -1));
          ss.setName((String) als.get(1));
          ss.setOrderno(Tools.getInt(als.get(2), -1));
          ss.setIconURL((String) als.get(3));
          ss.setPageURL((String) als.get(4));
          subSystemNoLicense.add(ss);
        }
      }
    }
    return subSystemNoLicense;
    // }

  }
  /**
   * if contains one category (and at least one table is menu object), will return true;
   *
   * @param request
   * @param subSystemId
   * @return
   */
  private boolean containsViewableChildren(HttpServletRequest request, SubSystem ss) {
    //		 Create categories and their tables in hashtable
    TableManager manager = TableManager.getInstance();
    // Iterator tables = manager.getAllTables().iterator();
    // Hashtable categories = new Hashtable(50,20); // key:Integer(category id), values :List of
    // table
    Integer tableCategoryId;
    Table table;
    WebAction action;
    ArrayList cats = new ArrayList();
    Connection conn = null;
    try {
      UserWebImpl userWeb =
          ((UserWebImpl)
              WebUtils.getSessionContextManager(request.getSession())
                  .getActor(nds.util.WebKeys.USER));
      conn = QueryEngine.getInstance().getConnection();
      HashMap webActionEnv = new HashMap();
      webActionEnv.put("connection", conn);
      webActionEnv.put("httpservletrequest", request);
      webActionEnv.put("userweb", userWeb);

      List categories = ss.children();
      for (int i = 0; i < categories.size(); i++) {
        Object o = categories.get(i); // TableCategory or WebAction
        if (o instanceof TableCategory) {
          TableCategory tc = (TableCategory) o;
          List children = tc.children();
          ArrayList catschild = new ArrayList();
          for (int j = 0; j < children.size(); j++) {
            if (children.get(j) instanceof Table) {
              table = (Table) children.get(j);
              if (!table.isMenuObject()) {
                continue;
              }
              try {
                WebUtils.checkTableQueryPermission(table.getName(), request);
              } catch (NDSSecurityException e) {
                continue;
              }
              // table is ok for current user to list
              return true;
            } else if (children.get(j) instanceof WebAction) {
              action = (WebAction) children.get(j);
              if (action.canDisplay(webActionEnv)) return true;
            } else {
              throw new NDSRuntimeException(
                  "Unsupported element in TableCategory children:" + children.get(j).getClass());
            }
          }

        } else if (o instanceof WebAction) {
          if (((WebAction) o).canDisplay(webActionEnv)) {
            return true;
          }
        } else {
          throw new NDSException(
              "Unexpected class in subsystem (id=" + ss.getId() + "), class is " + o.getClass());
        }
      }
    } catch (Throwable t) {
      logger.error("Fail to load subsystem tree", t);
    } finally {
      try {
        if (conn != null) conn.close();
      } catch (Throwable e) {
      }
    }

    return false;
  }