private void addNegatedQueryComponent() {
   QueryUtil.addNegatedNestedQuery(
       getKnowledgeBase(),
       getLuceneQueryPlugin(),
       groupListPanel,
       SlotFilterType.DIRECT_OWN_VALUE_PROPERTIES_APPLICABLE_TO_CLASSES);
 }
Example #2
0
    @Override
    protected String visitFunctionCall(FunctionCall node, Boolean unmangleNames) {
      StringBuilder builder = new StringBuilder();

      String arguments = joinExpressions(node.getArguments(), unmangleNames);
      if (node.getArguments().isEmpty() && "count".equalsIgnoreCase(node.getName().getSuffix())) {
        arguments = "*";
      }
      if (node.isDistinct()) {
        arguments = "DISTINCT " + arguments;
      }

      if (unmangleNames && node.getName().toString().startsWith(QueryUtil.FIELD_REFERENCE_PREFIX)) {
        checkState(
            node.getArguments().size() == 1, "Expected only one argument to field reference");
        QualifiedName name =
            QualifiedName.of(QueryUtil.unmangleFieldReference(node.getName().toString()));
        builder.append(arguments).append(".").append(name);
      } else {
        builder
            .append(formatQualifiedName(node.getName()))
            .append('(')
            .append(arguments)
            .append(')');
      }

      if (node.getWindow().isPresent()) {
        builder.append(" OVER ").append(visitWindow(node.getWindow().get(), unmangleNames));
      }

      return builder.toString();
    }
  /**
   * 메일템플릿보기
   *
   * @param templateID
   * @return
   * @throws DataAccessException
   */
  public MailTemplate viewMailTemplate(int templateID) throws DataAccessException {
    MailTemplate mailTemplate = new MailTemplate();
    Map<String, Object> resultMap = null;
    String sql =
        QueryUtil.getStringQuery(
            "content_sql", "content.mailtemplate.view"); // 쿼리 프로퍼티파일의 키값에 해당되는 sql문을 읽어온다.
    Map<String, Object> param = new HashMap<String, Object>();
    param.put("templateID", new Integer(templateID));

    // SQL문이 실행된다.
    try {
      resultMap = getSimpleJdbcTemplate().queryForMap(sql, param);
    } catch (EmptyResultDataAccessException e1) {
    }

    if (resultMap != null) {
      mailTemplate.setTemplateID(Integer.parseInt(String.valueOf(resultMap.get("templateID"))));
      mailTemplate.setTemplateName((String) resultMap.get("templateName"));
      mailTemplate.setTemplateContent((String) resultMap.get("templateContent"));
      mailTemplate.setShareGroupID((String) resultMap.get("shareGroupID"));
      mailTemplate.setUserID((String) resultMap.get("userID"));
      mailTemplate.setUserName((String) resultMap.get("userName"));
      mailTemplate.setUseYN((String) resultMap.get("useYN"));
      mailTemplate.setGroupName((String) resultMap.get("groupName"));
      mailTemplate.setRegistDate(String.valueOf(resultMap.get("registDate")));
      mailTemplate.setTemplateType((String) resultMap.get("templateType"));
    }

    return mailTemplate;
  }
Example #4
0
  /**
   * Executes a query and returns the data of the currunt page.
   *
   * @param em EntityManager
   * @param paginatedList Paginated list object containing parameters, and into which results are
   *     added by side effects
   * @param queryParam Query parameters
   * @return List of results
   */
  @SuppressWarnings("unchecked")
  private static <E> List<Object[]> executeResultQuery(
      PaginatedList<E> paginatedList, QueryParam queryParam) {
    Query q = QueryUtil.getNativeQuery(queryParam);

    q.setFirstResult(paginatedList.getOffset());
    q.setMaxResults(paginatedList.getLimit());
    return q.getResultList();
  }
Example #5
0
  /**
   * 功能:检查指定的线能否被删除,
   *
   * <p>参数:指定线的id
   *
   * <p>返回值:能删除返回true,否则返回false;
   */
  public boolean valiLineCanDele(String lineid) {

    ResultSet rst = null;
    String sql = "select count(*) aa from  sublineinfo  where lineid='" + lineid + "'";
    try {
      QueryUtil excu = new QueryUtil();
      rst = excu.executeQuery(sql);
      rst.next();
      if (rst.getInt("aa") == 0) {
        rst.close();
        return true;
      } else {
        rst.close();
        return false;
      }
    } catch (Exception e) {
      logger.error("检查指定的线能否被删除出错:" + e.getMessage());
      return false;
    }
  }
Example #6
0
  /**
   * Executes a native count(*) request to count the number of results.
   *
   * @param paginatedList Paginated list object containing parameters, and into which results are
   *     added by side effects
   * @param queryParam Query parameters
   */
  public static <E> void executeCountQuery(PaginatedList<E> paginatedList, QueryParam queryParam) {
    StringBuilder sb = new StringBuilder("select count(*) as result_count from (");
    sb.append(queryParam.getQueryString());
    sb.append(") as t1");

    QueryParam countQueryParam = new QueryParam(sb.toString(), queryParam.getParameterMap());

    Query q = QueryUtil.getNativeQuery(countQueryParam);

    Number resultCount = (Number) q.getSingleResult();
    paginatedList.setResultCount(resultCount.intValue());
  }
  /**
   * 메일템플릿 입력
   *
   * @param mailTemplate
   * @return
   * @throws DataAccessException
   */
  public int insertMailTemplate(MailTemplate mailTemplate) throws DataAccessException {
    String sql = QueryUtil.getStringQuery("content_sql", "content.mailtemplate.insert");
    Map<String, Object> param = new HashMap<String, Object>();
    param.put("templateName", mailTemplate.getTemplateName());
    param.put("templateContent", mailTemplate.getTemplateContent());
    param.put("shareGroupID", mailTemplate.getShareGroupID());
    param.put("userID", mailTemplate.getUserID());
    param.put("useYN", mailTemplate.getUseYN());
    param.put("templateType", mailTemplate.getTemplateType());

    return getSimpleJdbcTemplate().update(sql, param);
  }
Example #8
0
 /**
  * Get Tree ID for role
  *
  * @return AD_Tree_ID as int
  */
 private int getTreeID() {
   int AD_Role_ID = m_context.getAD_Role_ID();
   // Load Menu Structure ----------------------
   int AD_Tree_ID =
       QueryUtil.getSQLValue(
           null,
           "SELECT COALESCE(r.AD_Tree_Menu_ID, ci.AD_Tree_Menu_ID)"
               + "FROM AD_ClientInfo ci"
               + " INNER JOIN AD_Role r ON (ci.AD_Client_ID=r.AD_Client_ID) "
               + "WHERE AD_Role_ID=?",
           AD_Role_ID);
   if (AD_Tree_ID <= 0) AD_Tree_ID = 10; // Menu
   return AD_Tree_ID;
 } // getTreeID
 @Override
 protected VisitableQuery getQueryForType(Slot slot, ValueType type) throws InvalidQueryException {
   VisitableQuery query =
       QueryUtil.getQueryFromListPanel(groupListPanel, btnAndQuery.isSelected());
   OWLProperty property = (OWLProperty) slot;
   if (query != null) {
     if (property.isAnnotationProperty()) {
       return new NestedOwnSlotValueQuery(property, query);
     } else {
       return new OWLRestrictionQuery(getOWLModel(), property, query);
     }
   }
   return null;
 }
  /**
   * 메일템플릿 카운트
   *
   * @param currentPage
   * @param countPerPage
   * @param searchMap
   * @return
   * @throws DataAccessException
   */
  public int getCountMailTemplate(
      String userID, String groupID, String userAuth, Map<String, String> searchMap)
      throws DataAccessException {
    String sql = QueryUtil.getStringQuery("content_sql", "content.mailtemplate.selectcount");

    String searchType = searchMap.get("sSearchType");
    String searchText = searchMap.get("sSearchText");
    String useYN = searchMap.get("sUseYN");
    String selectedGroupID = searchMap.get("sSelectedGroupID");

    // 소속관리자라면
    if (userAuth.equals(Constant.USER_LEVEL_MASTER)) {
      sql += QueryUtil.getStringQuery("content_sql", "content.mailtemplate.selectbygroup");
    }
    // 일반사용자라면
    else if (userAuth.equals(Constant.USER_LEVEL_USER)) {
      sql += QueryUtil.getStringQuery("content_sql", "content.mailtemplate.selectbyuser");
    }

    // 검색조건이 있다면
    if (searchText != null && !searchText.equals("")) {
      sql += " AND  " + searchType + " LIKE :searchText ";
    }
    if (useYN != null && !useYN.equals("")) {
      sql += " AND  t.useYN ='" + useYN + "' ";
    }

    if (selectedGroupID != null && !selectedGroupID.equals("")) {
      sql += " AND  shareGroupID ='" + selectedGroupID + "' ";
    }

    Map<String, Object> param = new HashMap<String, Object>();
    param.put("userID", userID);
    param.put("groupID", groupID);
    param.put("searchText", "%" + searchText + "%");
    return getSimpleJdbcTemplate().queryForInt(sql, param);
  }
Example #11
0
 /**
  * 检验LineName 是否存在
  *
  * @param name String
  * @return boolean
  */
 public boolean validateLineName(String name, String type, String region) {
   String sql =
       "select count(lineid) i from lineinfo where linename='"
           + name
           + "' and regionid='"
           + region
           + "'";
   ResultSet rs = null;
   try {
     QueryUtil query = new QueryUtil();
     logger.info("validateLineName() sql :" + sql);
     rs = query.executeQuery(sql);
     rs.next();
     int i = rs.getInt("i");
     logger.info("i=" + i);
     if ("edit".equals(type)) {
       if (i < 1) {
         rs.close();
         return true;
       } else {
         rs.close();
         return false;
       }
     } else {
       if (i == 0) {
         rs.close();
         return true;
       } else {
         rs.close();
         return false;
       }
     }
   } catch (Exception ex) {
     logger.error("检查线路是否重名时出错: " + ex.getMessage());
     return false;
   }
 }
Example #12
0
 public List getLine(String sql) {
   QueryUtil query = null;
   BasicDynaBean dynaBean = null;
   // Vector resultVct = new Vector();
   ArrayList lableList = new ArrayList();
   logger.info("SQL :" + sql);
   try {
     query = new QueryUtil();
     Iterator it = query.queryBeans(sql).iterator();
     while (it.hasNext()) {
       dynaBean = (BasicDynaBean) it.next();
       // logger.info("lavel :"+dynaBean.get("linename")+"  value :"+dynaBean.get("lineid"));
       lableList.add(
           new LabelValueBean(
               (String) (dynaBean.get("linename")), (String) (dynaBean.get("lineid"))));
     }
     // resultVct.add(lableList);
     logger.info(lableList);
     return lableList;
   } catch (Exception ex) {
     logger.error("加载线路时出错:" + ex.getMessage());
     return null;
   }
 }
  /** Business logic to execute. */
  public final Response executeCommand(
      Object inputPar,
      UserSessionParameters userSessionPars,
      HttpServletRequest request,
      HttpServletResponse response,
      HttpSession userSession,
      ServletContext context) {
    Connection conn = null;
    try {
      conn = ConnectionManager.getConnection(context);

      // fires the GenericEvent.CONNECTION_CREATED event...
      EventsManager.getInstance()
          .processEvent(
              new GenericEvent(
                  this,
                  getRequestName(),
                  GenericEvent.CONNECTION_CREATED,
                  (JAIOUserSessionParameters) userSessionPars,
                  request,
                  response,
                  userSession,
                  context,
                  conn,
                  inputPar,
                  null));
      CallOutTaskVO vo = null;

      ArrayList list = (ArrayList) inputPar;

      Map attribute2dbField = new HashMap();
      attribute2dbField.put("callOutCodeSch10SCH12", "CALL_OUT_CODE_SCH10");
      attribute2dbField.put("companyCodeSys01SCH12", "COMPANY_CODE_SYS01");
      attribute2dbField.put("taskCodeReg07SCH12", "TASK_CODE_REG07");

      Response res = null;
      for (int i = 0; i < list.size(); i++) {
        vo = (CallOutTaskVO) list.get(i);

        // insert into SCH12...
        res =
            QueryUtil.insertTable(
                conn,
                userSessionPars,
                vo,
                "SCH12_CALL_OUT_TASKS",
                attribute2dbField,
                "Y",
                "N",
                context,
                true);
        if (res.isError()) {
          conn.rollback();
          return res;
        }
      }

      Response answer = new VOListResponse(list, false, list.size());

      // fires the GenericEvent.BEFORE_COMMIT event...
      EventsManager.getInstance()
          .processEvent(
              new GenericEvent(
                  this,
                  getRequestName(),
                  GenericEvent.BEFORE_COMMIT,
                  (JAIOUserSessionParameters) userSessionPars,
                  request,
                  response,
                  userSession,
                  context,
                  conn,
                  inputPar,
                  answer));

      conn.commit();

      // fires the GenericEvent.AFTER_COMMIT event...
      EventsManager.getInstance()
          .processEvent(
              new GenericEvent(
                  this,
                  getRequestName(),
                  GenericEvent.AFTER_COMMIT,
                  (JAIOUserSessionParameters) userSessionPars,
                  request,
                  response,
                  userSession,
                  context,
                  conn,
                  inputPar,
                  answer));

      return answer;
    } catch (Throwable ex) {
      Logger.error(
          userSessionPars.getUsername(),
          this.getClass().getName(),
          "executeCommand",
          "Error while inserting new call-out tasks",
          ex);
      try {
        conn.rollback();
      } catch (Exception ex3) {
      }
      return new ErrorResponse(ex.getMessage());
    } finally {
      try {
        ConnectionManager.releaseConnection(conn, context);
      } catch (Exception ex1) {
      }
    }
  }
 /**
  * 메일템플릿 삭제
  *
  * @param maps
  * @return
  * @throws DataAccessException
  */
 public int[] deleteMailTemplate(Map<String, Object>[] maps) throws DataAccessException {
   String sql =
       QueryUtil.getStringQuery(
           "content_sql", "content.mailtemplate.delete"); // 쿼리 프로퍼티파일의 키값에 해당되는 sql문을 읽어온다.	
   return getSimpleJdbcTemplate().batchUpdate(sql, maps);
 }
  /**
   * 메일템플릿 리스트
   *
   * @param currentPage
   * @param countPerPage
   * @param searchMap
   * @return
   * @throws DataAccessException
   */
  @SuppressWarnings("unchecked")
  public List<MailTemplate> listMailTemplate(
      String userID,
      String groupID,
      String userAuth,
      int currentPage,
      int countPerPage,
      Map<String, String> searchMap)
      throws DataAccessException {
    int start = (currentPage - 1) * countPerPage;
    String searchType = searchMap.get("sSearchType");
    String searchText = searchMap.get("sSearchText");
    String useYN = searchMap.get("sUseYN");
    String selectedGroupID = searchMap.get("sSelectedGroupID");

    String sql = QueryUtil.getStringQuery("content_sql", "content.mailtemplate.select");

    // 소속관리자라면
    if (userAuth.equals(Constant.USER_LEVEL_MASTER)) {
      sql += QueryUtil.getStringQuery("content_sql", "content.mailtemplate.selectbygroup");
    }
    // 일반사용자라면
    else if (userAuth.equals(Constant.USER_LEVEL_USER)) {
      sql += QueryUtil.getStringQuery("content_sql", "content.mailtemplate.selectbyuser");
    }

    // 검색조건이 있다면
    if (searchText != null && !searchText.equals("")) {
      sql += " AND  " + searchType + " LIKE :searchText ";
    }

    if (useYN != null && !useYN.equals("")) {
      sql += " AND  t.useYN ='" + useYN + "' ";
    }

    if (selectedGroupID != null && !selectedGroupID.equals("")) {
      sql += " AND  shareGroupID ='" + selectedGroupID + "' ";
    }

    String sqlTail = QueryUtil.getStringQuery("content_sql", "content.mailtemplate.tail");
    sql += sqlTail;

    if (db_type.equals(DB_TYPE_ORACLE))
      sql = QueryUtil.getPagingQueryByOracle(sql, start, countPerPage * currentPage);

    ParameterizedRowMapper rowMapper =
        new ParameterizedRowMapper() {
          public Object mapRow(ResultSet rs, int rownum) throws SQLException {
            MailTemplate mailTemplate = new MailTemplate();
            mailTemplate.setTemplateID(rs.getInt("templateID"));
            mailTemplate.setTemplateName(rs.getString("templateName"));
            mailTemplate.setShareGroupID(rs.getString("shareGroupID"));
            mailTemplate.setUserID(rs.getString("userID"));
            mailTemplate.setUserName(rs.getString("userName"));
            mailTemplate.setUseYN(rs.getString("useYN"));
            mailTemplate.setTemplateType(rs.getString("templateType"));
            if (rs.getString("shareGroupID").equals("ALL")) {
              mailTemplate.setGroupName("전체공유");
            } else if (rs.getString("shareGroupID").equals("NOT")) {
              mailTemplate.setGroupName("비공유");
            } else {
              mailTemplate.setGroupName(rs.getString("groupName"));
            }

            mailTemplate.setRegistDate(DateUtils.getStringDate(rs.getString("registDate")));
            return mailTemplate;
          }
        };

    Map<String, Object> param = new HashMap<String, Object>();
    param.put("start", new Integer(start));
    param.put("countPerPage", new Integer(countPerPage));
    param.put("userID", userID);
    param.put("groupID", groupID);
    param.put("searchText", "%" + searchText + "%");

    return getSimpleJdbcTemplate().query(sql, rowMapper, param);
  }
Example #16
0
  /**
   * Upserts data from CSV file. Data is batched up based on connection batch size. Column PDataType
   * is read from metadata and is used to convert column value to correct type before upsert. Note:
   * Column Names are expected as first line of CSV file.
   *
   * @param reader CSVReader instance
   * @throws Exception
   */
  public void upsert(CSVReader reader) throws Exception {
    List<String> columns = this.columns;
    if (columns != null && columns.isEmpty()) {
      columns = Arrays.asList(reader.readNext());
    }
    ColumnInfo[] columnInfo = generateColumnInfo(columns);
    PreparedStatement stmt = null;
    PreparedStatement[] stmtCache = null;
    if (columns == null) {
      stmtCache = new PreparedStatement[columnInfo.length];
    } else {
      String upsertStatement =
          QueryUtil.constructUpsertStatement(
              columnInfo, tableName, columnInfo.length - unfoundColumnCount);
      stmt = conn.prepareStatement(upsertStatement);
    }
    String[] nextLine;
    int rowCount = 0;
    int upsertBatchSize = conn.getMutateBatchSize();
    boolean wasAutoCommit = conn.getAutoCommit();
    try {
      conn.setAutoCommit(false);
      Object upsertValue = null;
      long start = System.currentTimeMillis();

      // Upsert data based on SqlType of each column
      while ((nextLine = reader.readNext()) != null) {
        if (columns == null) {
          stmt = stmtCache[nextLine.length - 1];
          if (stmt == null) {
            String upsertStatement =
                QueryUtil.constructUpsertStatement(columnInfo, tableName, nextLine.length);
            stmt = conn.prepareStatement(upsertStatement);
            stmtCache[nextLine.length - 1] = stmt;
          }
        }
        for (int index = 0; index < columnInfo.length; index++) {
          if (columnInfo[index] == null) {
            continue;
          }
          String line = nextLine[index];
          Integer info = columnInfo[index].getSqlType();
          upsertValue = convertTypeSpecificValue(line, info);
          if (upsertValue != null) {
            stmt.setObject(index + 1, upsertValue, columnInfo[index].getSqlType());
          } else {
            stmt.setNull(index + 1, columnInfo[index].getSqlType());
          }
        }
        stmt.execute();

        // Commit when batch size is reached
        if (++rowCount % upsertBatchSize == 0) {
          conn.commit();
          System.out.println("Rows upserted: " + rowCount);
        }
      }
      conn.commit();
      double elapsedDuration = ((System.currentTimeMillis() - start) / 1000.0);
      System.out.println("CSV Upsert complete. " + rowCount + " rows upserted");
      System.out.println("Time: " + elapsedDuration + " sec(s)\n");
    } finally {
      if (stmt != null) {
        stmt.close();
      }
      if (wasAutoCommit) conn.setAutoCommit(true);
    }
  }
Example #17
0
 /**
  * Executes a paginated request with 2 native queries (one to count the number of results, and one
  * to return the page).
  *
  * @param paginatedList Paginated list object containing parameters, and into which results are
  *     added by side effects
  * @param queryParam Query parameters
  * @param sortCriteria Sort criteria
  * @return List of results
  */
 public static <E> List<Object[]> executePaginatedQuery(
     PaginatedList<E> paginatedList, QueryParam queryParam, SortCriteria sortCriteria) {
   QueryParam sortedQueryParam = QueryUtil.getSortedQueryParam(queryParam, sortCriteria);
   executeCountQuery(paginatedList, sortedQueryParam);
   return executeResultQuery(paginatedList, sortedQueryParam);
 }
Example #18
0
  public List<IServerExtension> getExtensions(IProgressMonitor monitor)
      throws CoreException, ProvisionException {
    try {
      /*
       * To discovery the server adapter, there are three methods:
       * 1. Looking at the site.xml (if it exists). This is the legacy method
       * 2. Looking for the org.eclipse.wst.server.core.serverAdapter property in a p2
       *    update site (that may not have a site.xml). The property is necessary to identify
       *    the InstallableUnit as a server type. Otherwise, all the InstallableUnits will show
       *    up regardless of whether it is a server or not
       * 3. If the user created the p2 update site using a category.xml file (migrating old site.xml
       *    to use category.xml)
       */
      BundleContext bd =
          org.eclipse.wst.server.discovery.internal.Activator.getDefault()
              .getBundle()
              .getBundleContext();
      IProvisioningAgent agent = ExtensionUtility.getAgent(bd);

      URI url2 = new URI(url);

      // Method 1: Looking at the site.xml
      UpdateSiteMetadataRepositoryFactory mrf = new UpdateSiteMetadataRepositoryFactory();
      mrf.setAgent(ExtensionUtility.getAgent(bd));
      // If the site.xml does not exist, the load will throw a
      // org.eclipse.equinox.p2.core.ProvisionException
      List<IServerExtension> list = new ArrayList<IServerExtension>();
      try {
        IMetadataRepository repo = mrf.load(url2, IRepositoryManager.REPOSITORIES_ALL, monitor);
        IQuery<IInstallableUnit> query =
            QueryUtil.createMatchQuery(
                "id ~=/*org.eclipse.wst.server.core.serverAdapter/"); //$NON-NLS-1$

        list = getInstallableUnits(repo, query, url2, monitor);
      } catch (ProvisionException pe) {
        Trace.trace(Trace.WARNING, "Error getting update site information", pe); // $NON-NLS-1$
      }

      // Call Method 2 if there are no results from Method 1 (e.g. if the site.xml exists without
      // specifying any server adapters there or no site.xml exists)
      if (list.isEmpty()) {
        IMetadataRepositoryManager manager =
            (IMetadataRepositoryManager) agent.getService(IMetadataRepositoryManager.SERVICE_NAME);
        manager.addRepository(url2);
        // Need to query for all IUs
        IQuery<IInstallableUnit> query = QueryUtil.createIUAnyQuery();

        IMetadataRepository repo = manager.loadRepository(url2, monitor);
        List<IServerExtension> list2 = getInstallableUnits(repo, query, url2, monitor);

        int size = list2.size();
        for (int i = 0; i < size; i++) {
          Extension e = (Extension) list2.get(i);
          IInstallableUnit[] iuArr = e.getIUs();
          if (iuArr != null && iuArr.length > 0) {
            if (iuArr[0] != null) {
              if (iuArr[0].getProperty(SERVER_ADAPTER_ID) != null) {
                list.add(e);
              }
            }
          }
        }
      }

      // Call Method 3 if no results from Method 2. Creating the p2 update site using the
      // category.xml will generate
      // a provider property with org.eclipse.wst.server.core.serverAdapter
      if (list.isEmpty()) {
        IMetadataRepositoryManager manager =
            (IMetadataRepositoryManager) agent.getService(IMetadataRepositoryManager.SERVICE_NAME);
        manager.addRepository(url2);
        IQuery<IInstallableUnit> query =
            QueryUtil.createMatchQuery(
                "id ~=/*org.eclipse.wst.server.core.serverAdapter/"); //$NON-NLS-1$

        IMetadataRepository repo = manager.loadRepository(url2, monitor);
        list = getInstallableUnits(repo, query, url2, monitor);
      }

      return list;
    } catch (ProvisionException e) {
      Trace.trace(Trace.WARNING, "Error getting update info", e); // $NON-NLS-1$
      throw e;
    } catch (Exception e) {
      Trace.trace(Trace.WARNING, "Error getting update info", e); // $NON-NLS-1$

      return new ArrayList<IServerExtension>(0);
    }
  }
  /** Business logic to execute. */
  public final Response executeCommand(
      Object inputPar,
      UserSessionParameters userSessionPars,
      HttpServletRequest request,
      HttpServletResponse response,
      HttpSession userSession,
      ServletContext context) {
    Connection conn = null;
    PreparedStatement pstmt = null;
    try {
      conn = ConnectionManager.getConnection(context);

      // fires the GenericEvent.CONNECTION_CREATED event...
      EventsManager.getInstance()
          .processEvent(
              new GenericEvent(
                  this,
                  getRequestName(),
                  GenericEvent.CONNECTION_CREATED,
                  (JAIOUserSessionParameters) userSessionPars,
                  request,
                  response,
                  userSession,
                  context,
                  conn,
                  inputPar,
                  null));

      Object[] pars = (Object[]) inputPar;
      DetailPurchaseDocRowVO voTemplate = (DetailPurchaseDocRowVO) pars[0];
      VariantsMatrixVO matrixVO = (VariantsMatrixVO) pars[1];
      Object[][] cells = (Object[][]) pars[2];
      BigDecimal currencyDecimals = (BigDecimal) pars[3];

      Map attribute2dbField = new HashMap();
      attribute2dbField.put("companyCodeSys01DOC07", "COMPANY_CODE_SYS01");
      attribute2dbField.put("docTypeDOC07", "DOC_TYPE");
      attribute2dbField.put("docYearDOC07", "DOC_YEAR");
      attribute2dbField.put("docNumberDOC07", "DOC_NUMBER");
      attribute2dbField.put("rowNumberDOC07", "ROW_NUMBER");
      attribute2dbField.put("itemCodeItm01DOC07", "ITEM_CODE_ITM01");
      attribute2dbField.put("supplierItemCodePur02DOC07", "SUPPLIER_ITEM_CODE_PUR02");
      attribute2dbField.put("vatCodeItm01DOC07", "VAT_CODE_ITM01");
      attribute2dbField.put("valuePur04DOC07", "VALUE_PUR04");
      attribute2dbField.put("valueDOC07", "VALUE");
      attribute2dbField.put("qtyDOC07", "QTY");
      attribute2dbField.put("discountValueDOC07", "DISCOUNT_VALUE");
      attribute2dbField.put("discountPercDOC07", "DISCOUNT_PERC");
      attribute2dbField.put("vatValueDOC07", "VAT_VALUE");

      attribute2dbField.put("vatDescriptionDOC07", "VAT_DESCRIPTION");
      attribute2dbField.put("startDatePur04DOC07", "START_DATE_PUR04");
      attribute2dbField.put("endDatePur04DOC07", "END_DATE_PUR04");
      attribute2dbField.put("umCodePur02DOC07", "UM_CODE_PUR02");
      attribute2dbField.put("decimalsReg02DOC07", "DECIMALS_REG02");
      attribute2dbField.put("minPurchaseQtyPur02DOC07", "MIN_PURCHASE_QTY_PUR02");
      attribute2dbField.put("multipleQtyPur02DOC07", "MULTIPLE_QTY_PUR02");
      attribute2dbField.put("valueReg01DOC07", "VALUE_REG01");
      attribute2dbField.put("deductibleReg01DOC07", "DEDUCTIBLE_REG01");
      attribute2dbField.put("taxableIncomeDOC07", "TAXABLE_INCOME");
      attribute2dbField.put("progressiveHie02DOC07", "PROGRESSIVE_HIE02");
      attribute2dbField.put("deliveryDateDOC07", "DELIVERY_DATE");
      attribute2dbField.put("inQtyDOC07", "IN_QTY");
      attribute2dbField.put("orderQtyDOC07", "ORDER_QTY");
      attribute2dbField.put("invoiceQtyDOC07", "INVOICE_QTY");

      attribute2dbField.put("variantTypeItm06DOC07", "VARIANT_TYPE_ITM06");
      attribute2dbField.put("variantCodeItm11DOC07", "VARIANT_CODE_ITM11");
      attribute2dbField.put("variantTypeItm07DOC07", "VARIANT_TYPE_ITM07");
      attribute2dbField.put("variantCodeItm12DOC07", "VARIANT_CODE_ITM12");
      attribute2dbField.put("variantTypeItm08DOC07", "VARIANT_TYPE_ITM08");
      attribute2dbField.put("variantCodeItm13DOC07", "VARIANT_CODE_ITM13");
      attribute2dbField.put("variantTypeItm09DOC07", "VARIANT_TYPE_ITM09");
      attribute2dbField.put("variantCodeItm14DOC07", "VARIANT_CODE_ITM14");
      attribute2dbField.put("variantTypeItm10DOC07", "VARIANT_TYPE_ITM10");
      attribute2dbField.put("variantCodeItm15DOC07", "VARIANT_CODE_ITM15");

      DetailPurchaseDocRowVO vo = null;
      VariantsMatrixColumnVO colVO = null;
      VariantsMatrixRowVO rowVO = null;
      Response res = null;
      for (int i = 0; i < cells.length; i++) {
        rowVO = (VariantsMatrixRowVO) matrixVO.getRowDescriptors().get(i);

        if (matrixVO.getColumnDescriptors().size() == 0) {

          if (cells[i][0] != null) {
            vo = (DetailPurchaseDocRowVO) voTemplate.clone();

            if (!containsVariant(matrixVO, "ITM11_VARIANTS_1")) {
              // e.g. color but not no size...
              vo.setVariantCodeItm11DOC07(ApplicationConsts.JOLLY);
              vo.setVariantTypeItm06DOC07(ApplicationConsts.JOLLY);
            } else {
              vo.setVariantCodeItm11DOC07(rowVO.getVariantCodeITM11());
              vo.setVariantTypeItm06DOC07(rowVO.getVariantTypeITM06());
            }
            if (!containsVariant(matrixVO, "ITM12_VARIANTS_2")) {
              vo.setVariantCodeItm12DOC07(ApplicationConsts.JOLLY);
              vo.setVariantTypeItm07DOC07(ApplicationConsts.JOLLY);
            } else {
              vo.setVariantCodeItm12DOC07(rowVO.getVariantCodeITM11());
              vo.setVariantTypeItm07DOC07(rowVO.getVariantTypeITM06());
            }
            if (!containsVariant(matrixVO, "ITM13_VARIANTS_3")) {
              vo.setVariantCodeItm13DOC07(ApplicationConsts.JOLLY);
              vo.setVariantTypeItm08DOC07(ApplicationConsts.JOLLY);
            } else {
              vo.setVariantCodeItm13DOC07(rowVO.getVariantCodeITM11());
              vo.setVariantTypeItm08DOC07(rowVO.getVariantTypeITM06());
            }
            if (!containsVariant(matrixVO, "ITM14_VARIANTS_4")) {
              vo.setVariantCodeItm14DOC07(ApplicationConsts.JOLLY);
              vo.setVariantTypeItm09DOC07(ApplicationConsts.JOLLY);
            } else {
              vo.setVariantCodeItm14DOC07(rowVO.getVariantCodeITM11());
              vo.setVariantTypeItm09DOC07(rowVO.getVariantTypeITM06());
            }
            if (!containsVariant(matrixVO, "ITM15_VARIANTS_5")) {
              vo.setVariantCodeItm15DOC07(ApplicationConsts.JOLLY);
              vo.setVariantTypeItm10DOC07(ApplicationConsts.JOLLY);
            } else {
              vo.setVariantCodeItm15DOC07(rowVO.getVariantCodeITM11());
              vo.setVariantTypeItm10DOC07(rowVO.getVariantTypeITM06());
            }

            vo.setQtyDOC07((BigDecimal) cells[i][0]);

            PurchaseUtils.updateTotals(vo, currencyDecimals.intValue());

            /*
                        vo.setVariantCodeItm12DOC07(ApplicationConsts.JOLLY);
                        vo.setVariantCodeItm13DOC07(ApplicationConsts.JOLLY);
                        vo.setVariantCodeItm14DOC07(ApplicationConsts.JOLLY);
                        vo.setVariantCodeItm15DOC07(ApplicationConsts.JOLLY);

                        vo.setVariantTypeItm07DOC07(ApplicationConsts.JOLLY);
                        vo.setVariantTypeItm08DOC07(ApplicationConsts.JOLLY);
                        vo.setVariantTypeItm09DOC07(ApplicationConsts.JOLLY);
                        vo.setVariantTypeItm10DOC07(ApplicationConsts.JOLLY);
            */
            vo.setInQtyDOC07(new BigDecimal(0));
            vo.setOrderQtyDOC07(vo.getQtyDOC07());
            if (vo.getInvoiceQtyDOC07() == null) vo.setInvoiceQtyDOC07(new BigDecimal(0));
            vo.setRowNumberDOC07(
                progBean.getInternalProgressive("DOC07_PURCHASE_ITEMS", "ROW_NUMBER", conn));

            // insert into DOC07...
            res =
                QueryUtil.insertTable(
                    conn,
                    userSessionPars,
                    vo,
                    "DOC07_PURCHASE_ITEMS",
                    attribute2dbField,
                    "Y",
                    "N",
                    context,
                    true);

            if (res.isError()) {
              conn.rollback();
              return res;
            }
          } // end if on not null

        } else
          for (int k = 0; k < matrixVO.getColumnDescriptors().size(); k++) {

            colVO = (VariantsMatrixColumnVO) matrixVO.getColumnDescriptors().get(k);
            if (cells[i][k] != null) {
              vo = (DetailPurchaseDocRowVO) voTemplate.clone();

              vo.setQtyDOC07((BigDecimal) cells[i][k]);

              PurchaseUtils.updateTotals(vo, currencyDecimals.intValue());

              vo.setVariantCodeItm11DOC07(rowVO.getVariantCodeITM11());
              vo.setVariantTypeItm06DOC07(rowVO.getVariantTypeITM06());

              vo.setVariantCodeItm12DOC07(
                  colVO.getVariantCodeITM12() == null
                      ? ApplicationConsts.JOLLY
                      : colVO.getVariantCodeITM12());
              vo.setVariantCodeItm13DOC07(
                  colVO.getVariantCodeITM13() == null
                      ? ApplicationConsts.JOLLY
                      : colVO.getVariantCodeITM13());
              vo.setVariantCodeItm14DOC07(
                  colVO.getVariantCodeITM14() == null
                      ? ApplicationConsts.JOLLY
                      : colVO.getVariantCodeITM14());
              vo.setVariantCodeItm15DOC07(
                  colVO.getVariantCodeITM15() == null
                      ? ApplicationConsts.JOLLY
                      : colVO.getVariantCodeITM15());

              vo.setVariantTypeItm07DOC07(
                  colVO.getVariantTypeITM07() == null
                      ? ApplicationConsts.JOLLY
                      : colVO.getVariantTypeITM07());
              vo.setVariantTypeItm08DOC07(
                  colVO.getVariantTypeITM08() == null
                      ? ApplicationConsts.JOLLY
                      : colVO.getVariantTypeITM08());
              vo.setVariantTypeItm09DOC07(
                  colVO.getVariantTypeITM09() == null
                      ? ApplicationConsts.JOLLY
                      : colVO.getVariantTypeITM09());
              vo.setVariantTypeItm10DOC07(
                  colVO.getVariantTypeITM10() == null
                      ? ApplicationConsts.JOLLY
                      : colVO.getVariantTypeITM10());

              vo.setInQtyDOC07(new BigDecimal(0));
              vo.setOrderQtyDOC07(vo.getQtyDOC07());
              if (vo.getInvoiceQtyDOC07() == null) vo.setInvoiceQtyDOC07(new BigDecimal(0));
              vo.setRowNumberDOC07(
                  progBean.getInternalProgressive("DOC07_PURCHASE_ITEMS", "ROW_NUMBER", conn));

              // insert into DOC07...
              res =
                  QueryUtil.insertTable(
                      conn,
                      userSessionPars,
                      vo,
                      "DOC07_PURCHASE_ITEMS",
                      attribute2dbField,
                      "Y",
                      "N",
                      context,
                      true);

              if (res.isError()) {
                conn.rollback();
                return res;
              }
            } // end if on not null
          } // end inner for
      } // end outer for

      // recalculate totals...
      PurchaseDocPK pk =
          new PurchaseDocPK(
              vo.getCompanyCodeSys01DOC07(),
              vo.getDocTypeDOC07(),
              vo.getDocYearDOC07(),
              vo.getDocNumberDOC07());
      Response docResponse =
          docBean.loadPurchaseDoc(
              conn, pk, userSessionPars, request, response, userSession, context);
      if (docResponse.isError()) {
        conn.rollback();
        return docResponse;
      }
      DetailPurchaseDocVO docVO = (DetailPurchaseDocVO) ((VOResponse) docResponse).getVo();
      Response totalResponse =
          totalBean.calcDocTotals(
              conn, docVO, userSessionPars, request, response, userSession, context);
      if (totalResponse.isError()) {
        conn.rollback();
        return totalResponse;
      }

      pstmt =
          conn.prepareStatement(
              "update DOC06_PURCHASE set TAXABLE_INCOME=?,TOTAL_VAT=?,TOTAL=?,DOC_STATE=? where COMPANY_CODE_SYS01=? and DOC_TYPE=? and DOC_YEAR=? and DOC_NUMBER=?");
      pstmt.setBigDecimal(1, docVO.getTaxableIncomeDOC06());
      pstmt.setBigDecimal(2, docVO.getTotalVatDOC06());
      pstmt.setBigDecimal(3, docVO.getTotalDOC06());
      pstmt.setString(4, ApplicationConsts.HEADER_BLOCKED);
      pstmt.setString(5, vo.getCompanyCodeSys01DOC07());
      pstmt.setString(6, vo.getDocTypeDOC07());
      pstmt.setBigDecimal(7, vo.getDocYearDOC07());
      pstmt.setBigDecimal(8, vo.getDocNumberDOC07());
      pstmt.execute();

      Response answer = new VOResponse(vo);

      // fires the GenericEvent.BEFORE_COMMIT event...
      EventsManager.getInstance()
          .processEvent(
              new GenericEvent(
                  this,
                  getRequestName(),
                  GenericEvent.BEFORE_COMMIT,
                  (JAIOUserSessionParameters) userSessionPars,
                  request,
                  response,
                  userSession,
                  context,
                  conn,
                  inputPar,
                  answer));

      conn.commit();

      // fires the GenericEvent.AFTER_COMMIT event...
      EventsManager.getInstance()
          .processEvent(
              new GenericEvent(
                  this,
                  getRequestName(),
                  GenericEvent.AFTER_COMMIT,
                  (JAIOUserSessionParameters) userSessionPars,
                  request,
                  response,
                  userSession,
                  context,
                  conn,
                  inputPar,
                  answer));

      return answer;
    } catch (Throwable ex) {
      Logger.error(
          userSessionPars.getUsername(),
          this.getClass().getName(),
          "executeCommand",
          "Error while inserting new purchase order rows",
          ex);
      try {
        conn.rollback();
      } catch (Exception ex3) {
      }
      return new ErrorResponse(ex.getMessage());
    } finally {
      try {
        pstmt.close();
      } catch (Exception ex2) {
      }
      try {
        ConnectionManager.releaseConnection(conn, context);
      } catch (Exception ex1) {
      }
    }
  }
  /** Business logic to execute. */
  public final Response executeCommand(
      Object inputPar,
      UserSessionParameters userSessionPars,
      HttpServletRequest request,
      HttpServletResponse response,
      HttpSession userSession,
      ServletContext context) {
    String serverLanguageId = ((JAIOUserSessionParameters) userSessionPars).getServerLanguageId();

    Connection conn = null;
    try {
      conn = ConnectionManager.getConnection(context);

      // fires the GenericEvent.CONNECTION_CREATED event...
      EventsManager.getInstance()
          .processEvent(
              new GenericEvent(
                  this,
                  getRequestName(),
                  GenericEvent.CONNECTION_CREATED,
                  (JAIOUserSessionParameters) userSessionPars,
                  request,
                  response,
                  userSession,
                  context,
                  conn,
                  inputPar,
                  null));

      String sql =
          "select SCH01_EMPLOYEES.COMPANY_CODE_SYS01,SCH01_EMPLOYEES.PROGRESSIVE_REG04,SCH01_EMPLOYEES.EMPLOYEE_CODE,"
              + "REG07_TASKS.TASK_CODE,REG04_SUBJECTS.NAME_1,REG04_SUBJECTS.NAME_2,SYS10_TRANSLATIONS.DESCRIPTION,SCH01_EMPLOYEES.PHONE_NUMBER,SCH01_EMPLOYEES.OFFICE "
              + "from SCH01_EMPLOYEES,SYS10_TRANSLATIONS,REG07_TASKS,REG04_SUBJECTS where "
              + "SCH01_EMPLOYEES.COMPANY_CODE_SYS01=REG07_TASKS.COMPANY_CODE_SYS01 and "
              + "SCH01_EMPLOYEES.TASK_CODE_REG07=REG07_TASKS.TASK_CODE and "
              + "REG07_TASKS.PROGRESSIVE_SYS10=SYS10_TRANSLATIONS.PROGRESSIVE and "
              + "SYS10_TRANSLATIONS.LANGUAGE_CODE=? and "
              + "SCH01_EMPLOYEES.COMPANY_CODE_SYS01=REG04_SUBJECTS.COMPANY_CODE_SYS01 and "
              + "SCH01_EMPLOYEES.PROGRESSIVE_REG04=REG04_SUBJECTS.PROGRESSIVE and "
              + "SCH01_EMPLOYEES.ENABLED='Y'";

      GridParams gridParams = (GridParams) inputPar;

      if (gridParams.getOtherGridParams().get(ApplicationConsts.COMPANY_CODE_SYS01) != null) {
        sql +=
            " and SCH01_EMPLOYEES.COMPANY_CODE_SYS01='"
                + gridParams.getOtherGridParams().get(ApplicationConsts.COMPANY_CODE_SYS01)
                + "'";
      } else {
        // retrieve companies list...
        ArrayList companiesList =
            ((JAIOUserSessionParameters) userSessionPars).getCompanyBa().getCompaniesList("SCH01");
        String companies = "";
        for (int i = 0; i < companiesList.size(); i++)
          companies += "'" + companiesList.get(i).toString() + "',";
        companies = companies.substring(0, companies.length() - 1);

        sql += " and SCH01_EMPLOYEES.COMPANY_CODE_SYS01 in (" + companies + ")";
      }

      Map attribute2dbField = new HashMap();
      attribute2dbField.put("companyCodeSys01SCH01", "SCH01_EMPLOYEES.COMPANY_CODE_SYS01");
      attribute2dbField.put("progressiveReg04SCH01", "SCH01_EMPLOYEES.PROGRESSIVE_REG04");
      attribute2dbField.put("name_1REG04", "REG04_SUBJECTS.NAME_1");
      attribute2dbField.put("name_2REG04", "REG04_SUBJECTS.NAME_2");
      attribute2dbField.put("phoneNumberSCH01", "SCH01_EMPLOYEES.PHONE_NUMBER");
      attribute2dbField.put("officeSCH01", "SCH01_EMPLOYEES.OFFICE");
      attribute2dbField.put("employeeCodeSCH01", "SCH01_EMPLOYEES.EMPLOYEE_CODE");
      attribute2dbField.put("descriptionSYS10", "SYS10_TRANSLATIONS.DESCRIPTION");
      attribute2dbField.put("taskCodeReg07SCH01", "REG07_TASKS.TASK_CODE");

      ArrayList values = new ArrayList();
      values.add(serverLanguageId);

      if (gridParams.getOtherGridParams().get(ApplicationConsts.TASK_CODE_REG07) != null) {
        sql +=
            " and SCH01_EMPLOYEES.TASK_CODE_REG07='"
                + gridParams.getOtherGridParams().get(ApplicationConsts.TASK_CODE_REG07)
                + "'";
      }

      // read from SCH01 table...
      Response answer =
          QueryUtil.getQuery(
              conn,
              userSessionPars,
              sql,
              values,
              attribute2dbField,
              GridEmployeeVO.class,
              "Y",
              "N",
              context,
              gridParams,
              50,
              true);

      // fires the GenericEvent.BEFORE_COMMIT event...
      EventsManager.getInstance()
          .processEvent(
              new GenericEvent(
                  this,
                  getRequestName(),
                  GenericEvent.BEFORE_COMMIT,
                  (JAIOUserSessionParameters) userSessionPars,
                  request,
                  response,
                  userSession,
                  context,
                  conn,
                  inputPar,
                  answer));
      return answer;

    } catch (Throwable ex) {
      Logger.error(
          userSessionPars.getUsername(),
          this.getClass().getName(),
          "executeCommand",
          "Error while fetching employees list",
          ex);
      return new ErrorResponse(ex.getMessage());
    } finally {
      try {
        ConnectionManager.releaseConnection(conn, context);
      } catch (Exception ex1) {
      }
    }
  }
  /** Business logic to execute. */
  public final Response executeCommand(
      Object inputPar,
      UserSessionParameters userSessionPars,
      HttpServletRequest request,
      HttpServletResponse response,
      HttpSession userSession,
      ServletContext context) {
    String serverLanguageId = ((JAIOUserSessionParameters) userSessionPars).getServerLanguageId();

    Connection conn = null;
    try {
      conn = ConnectionManager.getConnection(context);

      // fires the GenericEvent.CONNECTION_CREATED event...
      EventsManager.getInstance()
          .processEvent(
              new GenericEvent(
                  this,
                  getRequestName(),
                  GenericEvent.CONNECTION_CREATED,
                  (JAIOUserSessionParameters) userSessionPars,
                  request,
                  response,
                  userSession,
                  context,
                  conn,
                  inputPar,
                  null));
      String sql =
          "select WAR04_WAREHOUSE_MOTIVES.QTY_SIGN,WAR04_WAREHOUSE_MOTIVES.ITEM_TYPE,WAR02_WAREHOUSE_MOVEMENTS.PROGRESSIVE,WAR02_WAREHOUSE_MOVEMENTS.COMPANY_CODE_SYS01,"
              + "WAR02_WAREHOUSE_MOVEMENTS.WAREHOUSE_CODE_WAR01,WAR02_WAREHOUSE_MOVEMENTS.ITEM_CODE_ITM01,WAR02_WAREHOUSE_MOVEMENTS.PROGRESSIVE_HIE01,"
              + "SYS10_LOC.DESCRIPTION,WAR02_WAREHOUSE_MOVEMENTS.MOVEMENT_DATE,WAR02_WAREHOUSE_MOVEMENTS.USERNAME,WAR02_WAREHOUSE_MOVEMENTS.NOTE,"
              + "WAR02_WAREHOUSE_MOVEMENTS.DELTA_QTY,WAR02_WAREHOUSE_MOVEMENTS.WAREHOUSE_MOTIVE_WAR04,SYS10_WAR04.DESCRIPTION,SYS10_ITM01.DESCRIPTION,"
              + "WAR01_WAREHOUSES.DESCRIPTION,"
              + "WAR02_WAREHOUSE_MOVEMENTS.VARIANT_TYPE_ITM06,WAR02_WAREHOUSE_MOVEMENTS.VARIANT_CODE_ITM11,"
              + "WAR02_WAREHOUSE_MOVEMENTS.VARIANT_TYPE_ITM07,WAR02_WAREHOUSE_MOVEMENTS.VARIANT_CODE_ITM12,"
              + "WAR02_WAREHOUSE_MOVEMENTS.VARIANT_TYPE_ITM08,WAR02_WAREHOUSE_MOVEMENTS.VARIANT_CODE_ITM13,"
              + "WAR02_WAREHOUSE_MOVEMENTS.VARIANT_TYPE_ITM09,WAR02_WAREHOUSE_MOVEMENTS.VARIANT_CODE_ITM14,"
              + "WAR02_WAREHOUSE_MOVEMENTS.VARIANT_TYPE_ITM10,WAR02_WAREHOUSE_MOVEMENTS.VARIANT_CODE_ITM15 "
              + " from "
              + "WAR04_WAREHOUSE_MOTIVES,WAR02_WAREHOUSE_MOVEMENTS,SYS10_TRANSLATIONS SYS10_LOC,SYS10_TRANSLATIONS SYS10_WAR04,"
              + "SYS10_TRANSLATIONS SYS10_ITM01,WAR01_WAREHOUSES,ITM01_ITEMS where "
              + "WAR02_WAREHOUSE_MOVEMENTS.WAREHOUSE_MOTIVE_WAR04=WAR04_WAREHOUSE_MOTIVES.WAREHOUSE_MOTIVE and "
              + "WAR04_WAREHOUSE_MOTIVES.PROGRESSIVE_SYS10=SYS10_WAR04.PROGRESSIVE and "
              + "SYS10_WAR04.LANGUAGE_CODE=? and "
              + "WAR02_WAREHOUSE_MOVEMENTS.PROGRESSIVE_HIE01=SYS10_LOC.PROGRESSIVE and "
              + "SYS10_LOC.LANGUAGE_CODE=? and "
              + "WAR02_WAREHOUSE_MOVEMENTS.COMPANY_CODE_SYS01=ITM01_ITEMS.COMPANY_CODE_SYS01 and "
              + "WAR02_WAREHOUSE_MOVEMENTS.ITEM_CODE_ITM01=ITM01_ITEMS.ITEM_CODE and "
              + "ITM01_ITEMS.PROGRESSIVE_SYS10=SYS10_ITM01.PROGRESSIVE and "
              + "SYS10_ITM01.LANGUAGE_CODE=? and "
              + "WAR02_WAREHOUSE_MOVEMENTS.COMPANY_CODE_SYS01=WAR01_WAREHOUSES.COMPANY_CODE_SYS01 and "
              + "WAR02_WAREHOUSE_MOVEMENTS.WAREHOUSE_CODE_WAR01=WAR01_WAREHOUSES.WAREHOUSE_CODE ";

      Map attribute2dbField = new HashMap();
      attribute2dbField.put("qtySignWAR04", "WAR04_WAREHOUSE_MOTIVES.QTY_SIGN");
      attribute2dbField.put("itemTypeWAR04", "WAR04_WAREHOUSE_MOTIVES.ITEM_TYPE");
      attribute2dbField.put("progressiveWAR02", "WAR02_WAREHOUSE_MOVEMENTS.PROGRESSIVE");
      attribute2dbField.put(
          "companyCodeSys01WAR02", "WAR02_WAREHOUSE_MOVEMENTS.COMPANY_CODE_SYS01");
      attribute2dbField.put(
          "warehouseCodeWar01WAR02", "WAR02_WAREHOUSE_MOVEMENTS.WAREHOUSE_CODE_WAR01");
      attribute2dbField.put("itemCodeItm01WAR02", "WAR02_WAREHOUSE_MOVEMENTS.ITEM_CODE_ITM01");
      attribute2dbField.put("progressiveHie01WAR02", "WAR02_WAREHOUSE_MOVEMENTS.PROGRESSIVE_HIE01");
      attribute2dbField.put("locationDescriptionSYS10", "SYS10_LOC.DESCRIPTION");
      attribute2dbField.put("movementDateWAR02", "WAR02_WAREHOUSE_MOVEMENTS.MOVEMENT_DATE");
      attribute2dbField.put("usernameWAR02", "WAR02_WAREHOUSE_MOVEMENTS.USERNAME");
      attribute2dbField.put("noteWAR02", "WAR02_WAREHOUSE_MOVEMENTS.NOTE");
      attribute2dbField.put("deltaQtyWAR02", "WAR02_WAREHOUSE_MOVEMENTS.DELTA_QTY");
      attribute2dbField.put(
          "warehouseMotiveWar04WAR02", "WAR02_WAREHOUSE_MOVEMENTS.WAREHOUSE_MOTIVE_WAR04");
      attribute2dbField.put("motiveDescriptionSYS10", "SYS10_WAR04.DESCRIPTION");
      attribute2dbField.put("itemDescriptionSYS10", "SYS10_ITM01.DESCRIPTION");
      attribute2dbField.put("descriptionWAR01", "WAR01_WAREHOUSES.DESCRIPTION");

      attribute2dbField.put(
          "variantTypeItm06WAR02", "WAR02_WAREHOUSE_MOVEMENTS.VARIANT_TYPE_ITM06");
      attribute2dbField.put(
          "variantCodeItm11WAR02", "WAR02_WAREHOUSE_MOVEMENTS.VARIANT_CODE_ITM11");
      attribute2dbField.put(
          "variantTypeItm07WAR02", "WAR02_WAREHOUSE_MOVEMENTS.VARIANT_TYPE_ITM07");
      attribute2dbField.put(
          "variantCodeItm12WAR02", "WAR02_WAREHOUSE_MOVEMENTS.VARIANT_CODE_ITM12");
      attribute2dbField.put(
          "variantTypeItm08WAR02", "WAR02_WAREHOUSE_MOVEMENTS.VARIANT_TYPE_ITM08");
      attribute2dbField.put(
          "variantCodeItm13WAR02", "WAR02_WAREHOUSE_MOVEMENTS.VARIANT_CODE_ITM13");
      attribute2dbField.put(
          "variantTypeItm09WAR02", "WAR02_WAREHOUSE_MOVEMENTS.VARIANT_TYPE_ITM09");
      attribute2dbField.put(
          "variantCodeItm14WAR02", "WAR02_WAREHOUSE_MOVEMENTS.VARIANT_CODE_ITM14");
      attribute2dbField.put(
          "variantTypeItm10WAR02", "WAR02_WAREHOUSE_MOVEMENTS.VARIANT_TYPE_ITM10");
      attribute2dbField.put(
          "variantCodeItm15WAR02", "WAR02_WAREHOUSE_MOVEMENTS.VARIANT_CODE_ITM15");

      ArrayList values = new ArrayList();
      values.add(serverLanguageId);
      values.add(serverLanguageId);
      values.add(serverLanguageId);

      GridParams gridParams = (GridParams) inputPar;

      // read from WAR02 table...
      Response answer =
          QueryUtil.getQuery(
              conn,
              userSessionPars,
              sql,
              values,
              attribute2dbField,
              MovementVO.class,
              "Y",
              "N",
              context,
              gridParams,
              true);

      // fires the GenericEvent.BEFORE_COMMIT event...
      EventsManager.getInstance()
          .processEvent(
              new GenericEvent(
                  this,
                  getRequestName(),
                  GenericEvent.BEFORE_COMMIT,
                  (JAIOUserSessionParameters) userSessionPars,
                  request,
                  response,
                  userSession,
                  context,
                  conn,
                  inputPar,
                  answer));

      return answer;

    } catch (Throwable ex) {
      Logger.error(
          userSessionPars.getUsername(),
          this.getClass().getName(),
          "executeCommand",
          "Error while fetching warehouse movements list",
          ex);
      return new ErrorResponse(ex.getMessage());
    } finally {
      try {
        ConnectionManager.releaseConnection(conn, context);
      } catch (Exception ex1) {
      }
    }
  }
Example #22
0
  /**
   * Before Save. Truncate Dates
   *
   * @param newRecord new
   * @return true
   */
  @Override
  protected boolean beforeSave(boolean newRecord) {
    Timestamp startdate = getStartDate();
    Timestamp enddate = getEndDate();

    if (enddate != null && startdate.after(enddate)) {

      s_log.saveError("Error", Msg.getMsg(getCtx(), "CalPeriodInvalidDate"));
      return false;
    }
    //	Truncate Dates
    startdate = TimeUtil.getDay(startdate);
    setStartDate(startdate);

    if (enddate != null) enddate = TimeUtil.getDay(enddate);
    else enddate = TimeUtil.getMonthLastDay(getStartDate());

    //		Adding the time component of 23:59:59 to the end date
    enddate = new Timestamp(enddate.getTime() + 86399000);
    setEndDate(enddate);

    MPeriod[] periods = getAllPeriodsInYear(getC_Year_ID(), "S", getCtx(), get_Trx());
    MPeriod[] allperiods = getAllPeriodsInCalendar(getC_Calendar_ID(), "S", getCtx(), get_Trx());
    //		Check for non-negative period number
    if (getPeriodNo() < 0) {
      s_log.saveError("Error", Msg.getMsg(getCtx(), "CalNegPeriodNo"));
      return false;
    }

    //		Check for standard period
    if (isStandardPeriod() == true) {
      // Check Period number is in ascending order

      Timestamp nextPeriodStartDate = null;
      Timestamp prevPeriodStartDate = null;

      // Get the next standard period number Start Date in this year
      String sql =
          "SELECT StartDate FROM C_Period WHERE "
              + "C_Period.IsActive='Y' AND PeriodType='S' "
              + "AND C_Period.C_Year_ID =? "
              + "AND C_Period.C_Period_ID <> ?"
              + "AND  C_Period.PeriodNo "
              + " >  ?  ORDER BY  C_Period.PeriodNo ASC";
      Object[][] result = null;
      result =
          QueryUtil.executeQuery(get_Trx(), sql, getC_Year_ID(), getC_Period_ID(), getPeriodNo());

      if (result.length != 0) nextPeriodStartDate = (Timestamp) result[0][0];

      // Get the previous standard period number Start Date in this year
      sql =
          "SELECT StartDate FROM C_Period WHERE "
              + "C_Period.IsActive='Y' AND PeriodType='S'  "
              + "AND C_Period.C_Year_ID =? "
              + "AND C_Period.C_Period_ID <> ?"
              + "AND C_Period.PeriodNo "
              + "< ?  ORDER BY  C_Period.PeriodNo DESC";

      result =
          QueryUtil.executeQuery(get_Trx(), sql, getC_Year_ID(), getC_Period_ID(), getPeriodNo());
      if (result.length != 0) prevPeriodStartDate = (Timestamp) result[0][0];

      if ((prevPeriodStartDate != null
          && TimeUtil.max(prevPeriodStartDate, startdate) == prevPeriodStartDate)) {
        s_log.saveError("Error", Msg.getMsg(getCtx(), "CalPeriodAsc"));
        return false;
      }

      if ((nextPeriodStartDate != null
          && TimeUtil.max(nextPeriodStartDate, startdate) == startdate)) {
        s_log.saveError("Error", Msg.getMsg(getCtx(), "CalPeriodAsc"));
        return false;
      }

      //  Check if the Standard Period is overlapping other periods.

      for (MPeriod period : allperiods) {
        if ((TimeUtil.isValid(period.getStartDate(), period.getEndDate(), startdate) == true
                || TimeUtil.isValid(period.getStartDate(), period.getEndDate(), enddate) == true)
            && period.getC_Period_ID() != getC_Period_ID()) {
          s_log.saveError("Error", Msg.getMsg(getCtx(), "CalPeriodOverlap"));
          return false;
        }
      }

    }
    //		Check for adjusting period
    else {
      boolean startflag = false;
      boolean endflag = false;
      for (MPeriod period : periods) {
        if (TimeUtil.isValid(period.getStartDate(), period.getEndDate(), startdate) == true)
          startflag = true;
        if (TimeUtil.isValid(period.getStartDate(), period.getEndDate(), enddate) == true)
          endflag = true;
        if (startflag == true && endflag == true) break;
      }
      if (startflag == false || endflag == false) {
        s_log.saveError("Error", Msg.getMsg(getCtx(), "CalAdjPeriod"));
        return false;
      }
    }
    return true;
  } //	beforeSave
  /** Business logic to execute. */
  public VOListResponse loadOutDeliveryNotesForSaleDoc(
      GridParams pars, String serverLanguageId, String username, ArrayList companiesList)
      throws Throwable {
    PreparedStatement pstmt = null;
    Connection conn = null;
    try {
      if (this.conn == null) conn = getConn();
      else conn = this.conn;

      // retrieve companies list...
      String companies = "";
      for (int i = 0; i < companiesList.size(); i++)
        companies += "'" + companiesList.get(i).toString() + "',";
      companies = companies.substring(0, companies.length() - 1);

      String sql =
          "select DOC08_DELIVERY_NOTES.COMPANY_CODE_SYS01,DOC08_DELIVERY_NOTES.DOC_TYPE,"
              + "DOC08_DELIVERY_NOTES.DOC_YEAR,DOC08_DELIVERY_NOTES.DOC_NUMBER,DOC08_DELIVERY_NOTES.DOC_DATE, "
              + "DOC08_DELIVERY_NOTES.DESTINATION_CODE_REG18,DOC08_DELIVERY_NOTES.DESCRIPTION,"
              + "DOC08_DELIVERY_NOTES.DOC_SEQUENCE "
              + " from DOC08_DELIVERY_NOTES where "
              + "DOC08_DELIVERY_NOTES.COMPANY_CODE_SYS01 in ("
              + companies
              + ") and "
              + "DOC08_DELIVERY_NOTES.ENABLED='Y' and "
              + "DOC08_DELIVERY_NOTES.DOC_TYPE=? and "
              + "DOC08_DELIVERY_NOTES.DOC_STATE=? and "
              + "(DOC08_DELIVERY_NOTES.COMPANY_CODE_SYS01,DOC08_DELIVERY_NOTES.DOC_TYPE,DOC08_DELIVERY_NOTES.DOC_YEAR,DOC08_DELIVERY_NOTES.DOC_NUMBER) "
              + " in (select DOC10_OUT_DELIVERY_NOTE_ITEMS.COMPANY_CODE_SYS01,DOC10_OUT_DELIVERY_NOTE_ITEMS.DOC_TYPE,DOC10_OUT_DELIVERY_NOTE_ITEMS.DOC_YEAR,DOC10_OUT_DELIVERY_NOTE_ITEMS.DOC_NUMBER "
              + " from DOC10_OUT_DELIVERY_NOTE_ITEMS where "
              + " DOC10_OUT_DELIVERY_NOTE_ITEMS.COMPANY_CODE_SYS01=? and "
              + " DOC10_OUT_DELIVERY_NOTE_ITEMS.DOC_TYPE_DOC01=? and "
              + " DOC10_OUT_DELIVERY_NOTE_ITEMS.DOC_YEAR_DOC01=? and "
              + " DOC10_OUT_DELIVERY_NOTE_ITEMS.DOC_NUMBER_DOC01=? ";

      DetailSaleDocVO docVO =
          (DetailSaleDocVO)
              pars.getOtherGridParams().get(ApplicationConsts.SALE_DOC_VO); // invoice document...
      if (docVO.getDocNumberDOC01() == null)
        sql +=
            " and DOC10_OUT_DELIVERY_NOTE_ITEMS.QTY-DOC10_OUT_DELIVERY_NOTE_ITEMS.INVOICE_QTY>0)";
      else sql += ")";

      Map attribute2dbField = new HashMap();
      attribute2dbField.put("companyCodeSys01DOC08", "DOC08_DELIVERY_NOTES.COMPANY_CODE_SYS01");
      attribute2dbField.put("docTypeDOC08", "DOC08_DELIVERY_NOTES.DOC_TYPE");
      attribute2dbField.put("docYearDOC08", "DOC08_DELIVERY_NOTES.DOC_YEAR");
      attribute2dbField.put("docNumberDOC08", "DOC08_DELIVERY_NOTES.DOC_NUMBER");
      attribute2dbField.put("docDateDOC08", "DOC08_DELIVERY_NOTES.DOC_DATE");
      attribute2dbField.put(
          "destinationCodeReg18DOC08", "DOC08_DELIVERY_NOTES.DESTINATION_CODE_REG18");
      attribute2dbField.put("descriptionDOC08", "DOC08_DELIVERY_NOTES.DESCRIPTION");
      attribute2dbField.put("docSequenceDOC08", "DOC08_DELIVERY_NOTES.DOC_SEQUENCE");

      ArrayList values = new ArrayList();
      values.add(ApplicationConsts.OUT_DELIVERY_NOTE_DOC_TYPE);
      values.add(ApplicationConsts.CLOSED);
      values.add(docVO.getCompanyCodeSys01DOC01());
      values.add(docVO.getDocTypeDoc01DOC01());
      values.add(docVO.getDocYearDoc01DOC01());
      values.add(docVO.getDocNumberDoc01DOC01());

      // read from DOC08 table...
      Response res =
          QueryUtil.getQuery(
              conn,
              new UserSessionParameters(username),
              sql,
              values,
              attribute2dbField,
              OutDeliveryNotesVO.class,
              "Y",
              "N",
              null,
              pars,
              true);

      if (res.isError()) throw new Exception(res.getErrorMessage());

      // check if the invoice document has been already created and there exists delivery notes
      // linked to it...
      if (docVO.getDocNumberDOC01() != null) {
        pstmt =
            conn.prepareStatement(
                "select DOC08_DELIVERY_NOTES.DOC_NUMBER from DOC08_DELIVERY_NOTES where "
                    + "DOC08_DELIVERY_NOTES.COMPANY_CODE_SYS01 in ("
                    + companies
                    + ") and "
                    + "DOC08_DELIVERY_NOTES.ENABLED='Y' and "
                    + "DOC08_DELIVERY_NOTES.DOC_TYPE=? and "
                    + "DOC08_DELIVERY_NOTES.DOC_STATE=? and "
                    + "(DOC08_DELIVERY_NOTES.COMPANY_CODE_SYS01,DOC08_DELIVERY_NOTES.DOC_TYPE,DOC08_DELIVERY_NOTES.DOC_YEAR,DOC08_DELIVERY_NOTES.DOC_NUMBER) "
                    + " in (select DOC10_OUT_DELIVERY_NOTE_ITEMS.COMPANY_CODE_SYS01,DOC10_OUT_DELIVERY_NOTE_ITEMS.DOC_TYPE,DOC10_OUT_DELIVERY_NOTE_ITEMS.DOC_YEAR,DOC10_OUT_DELIVERY_NOTE_ITEMS.DOC_NUMBER "
                    + " from DOC10_OUT_DELIVERY_NOTE_ITEMS,DOC02_SELLING_ITEMS where "
                    + " DOC10_OUT_DELIVERY_NOTE_ITEMS.COMPANY_CODE_SYS01=? and "
                    + " DOC10_OUT_DELIVERY_NOTE_ITEMS.DOC_TYPE_DOC01=? and "
                    + " DOC10_OUT_DELIVERY_NOTE_ITEMS.DOC_YEAR_DOC01=? and "
                    + " DOC10_OUT_DELIVERY_NOTE_ITEMS.DOC_NUMBER_DOC01=? and "
                    + " DOC10_OUT_DELIVERY_NOTE_ITEMS.COMPANY_CODE_SYS01=DOC02_SELLING_ITEMS.COMPANY_CODE_SYS01 and "
                    + " DOC02_SELLING_ITEMS.DOC_TYPE=? and "
                    + " DOC02_SELLING_ITEMS.DOC_YEAR=? and "
                    + " DOC02_SELLING_ITEMS.DOC_NUMBER=? and "
                    + " DOC10_OUT_DELIVERY_NOTE_ITEMS.ITEM_CODE_ITM01=DOC02_SELLING_ITEMS.ITEM_CODE_ITM01)");

        pstmt.setString(1, ApplicationConsts.OUT_DELIVERY_NOTE_DOC_TYPE);
        pstmt.setString(2, ApplicationConsts.CLOSED);
        pstmt.setString(3, docVO.getCompanyCodeSys01DOC01());
        pstmt.setString(4, docVO.getDocTypeDoc01DOC01());
        pstmt.setBigDecimal(5, docVO.getDocYearDoc01DOC01());
        pstmt.setBigDecimal(6, docVO.getDocNumberDoc01DOC01());
        pstmt.setString(7, docVO.getDocTypeDOC01());
        pstmt.setBigDecimal(8, docVO.getDocYearDOC01());
        pstmt.setBigDecimal(9, docVO.getDocNumberDOC01());

        HashSet docNumberDOC08s = new HashSet();
        ResultSet rset = pstmt.executeQuery();
        while (rset.next()) docNumberDOC08s.add(rset.getBigDecimal(1));
        rset.close();

        java.util.List rows = ((VOListResponse) res).getRows();
        OutDeliveryNotesVO vo = null;
        for (int i = 0; i < rows.size(); i++) {
          vo = (OutDeliveryNotesVO) rows.get(i);
          if (docNumberDOC08s.contains(vo.getDocNumberDOC08())) vo.setSelected(Boolean.TRUE);
        }
      }

      Response answer = res;
      if (answer.isError()) throw new Exception(answer.getErrorMessage());
      else return (VOListResponse) answer;
    } catch (Throwable ex) {
      Logger.error(
          username,
          this.getClass().getName(),
          "executeCommand",
          "Error while fetching out delivery notes list, related to the specified sale document",
          ex);
      throw new Exception(ex.getMessage());
    } finally {
      try {
        pstmt.close();
      } catch (Exception exx) {
      }
      try {
        if (this.conn == null && conn != null) {
          // close only local connection
          conn.commit();
          conn.close();
        }

      } catch (Exception exx) {
      }
    }
  }
  /** Business logic to execute. */
  public VOListResponse loadItemVariants(GridParams pars, String serverLanguageId, String username)
      throws Throwable {

    PreparedStatement pstmt = null;

    Connection conn = null;
    try {
      if (this.conn == null) conn = getConn();
      else conn = this.conn;

      String tableName = (String) pars.getOtherGridParams().get(ApplicationConsts.TABLE_NAME);
      ItemPK pk = (ItemPK) pars.getOtherGridParams().get(ApplicationConsts.ITEM_PK);
      String productVariant = (String) productVariants.get(tableName);
      String variantType = (String) variantTypes.get(tableName);
      String variantTypeJoin = (String) variantTypeJoins.get(tableName);
      String variantCodeJoin = (String) variantCodeJoins.get(tableName);

      String sql =
          "select "
              + tableName
              + "."
              + variantTypeJoin
              + ","
              + tableName
              + ".VARIANT_CODE,A.DESCRIPTION,B.DESCRIPTION, "
              + tableName
              + ".PROGRESSIVE_SYS10,"
              + variantType
              + ".PROGRESSIVE_SYS10 "
              + "from "
              + tableName
              + ","
              + variantType
              + ",SYS10_COMPANY_TRANSLATIONS A,SYS10_COMPANY_TRANSLATIONS B "
              + "where "
              + tableName
              + ".COMPANY_CODE_SYS01=? and "
              + tableName
              + ".COMPANY_CODE_SYS01="
              + variantType
              + ".COMPANY_CODE_SYS01 and "
              + tableName
              + "."
              + variantTypeJoin
              + "="
              + variantType
              + ".VARIANT_TYPE and "
              + tableName
              + ".COMPANY_CODE_SYS01=A.COMPANY_CODE_SYS01 and "
              + tableName
              + ".PROGRESSIVE_SYS10=A.PROGRESSIVE and A.LANGUAGE_CODE=? and "
              + variantType
              + ".COMPANY_CODE_SYS01=B.COMPANY_CODE_SYS01 and "
              + variantType
              + ".PROGRESSIVE_SYS10=B.PROGRESSIVE and B.LANGUAGE_CODE=? and "
              + tableName
              + ".ENABLED='Y' and "
              + variantType
              + ".ENABLED='Y' and "
              + // and not "+tableName+"."+variantTypeJoin+"=? and "+
              "not "
              + tableName
              + ".VARIANT_CODE=? "
              + "order by "
              + tableName
              + "."
              + variantTypeJoin
              + ","
              + tableName
              + ".CODE_ORDER";

      Map attribute2dbField = new HashMap();
      attribute2dbField.put("variantType", tableName + "." + variantTypeJoin);
      attribute2dbField.put("variantCode", tableName + ".VARIANT_CODE");
      attribute2dbField.put("variantDesc", "A.DESCRIPTION");
      attribute2dbField.put("variantTypeDesc", "B.DESCRIPTION");
      attribute2dbField.put("variantProgressiveSys10", tableName + ".PROGRESSIVE_SYS10");
      attribute2dbField.put("variantTypeProgressiveSys10", variantType + ".PROGRESSIVE_SYS10");

      ArrayList values = new ArrayList();
      values.add(pk.getCompanyCodeSys01ITM01());
      values.add(serverLanguageId);
      values.add(serverLanguageId);
      // values.add(ApplicationConsts.JOLLY);
      values.add(ApplicationConsts.JOLLY);

      // read from ITMxxx table...
      Response answer =
          QueryUtil.getQuery(
              conn,
              new UserSessionParameters(username),
              sql,
              values,
              attribute2dbField,
              ItemVariantVO.class,
              "Y",
              "N",
              null,
              pars,
              50,
              true);

      if (!answer.isError()) {
        java.util.List vos = ((VOListResponse) answer).getRows();
        HashMap map = new HashMap();
        ItemVariantVO vo = null;
        for (int i = 0; i < vos.size(); i++) {
          vo = (ItemVariantVO) vos.get(i);
          vo.setCompanyCodeSys01(pk.getCompanyCodeSys01ITM01());
          vo.setItemCodeItm01(pk.getItemCodeITM01());
          vo.setTableName(tableName);
          map.put(vo.getVariantType() + "." + vo.getVariantCode(), vo);
        }

        pstmt =
            conn.prepareStatement(
                "select "
                    + productVariant
                    + "."
                    + variantTypeJoin
                    + ","
                    + productVariant
                    + "."
                    + variantCodeJoin
                    + " "
                    + "from "
                    + productVariant
                    + " "
                    + "where "
                    + productVariant
                    + ".COMPANY_CODE_SYS01=? and "
                    + productVariant
                    + ".ITEM_CODE_ITM01=? and "
                    + productVariant
                    + ".ENABLED='Y' ");
        pstmt.setString(1, pk.getCompanyCodeSys01ITM01());
        pstmt.setString(2, pk.getItemCodeITM01());
        ResultSet rset = pstmt.executeQuery();

        while (rset.next()) {
          vo = (ItemVariantVO) map.get(rset.getString(1) + "." + rset.getString(2));
          if (vo != null) vo.setSelected(Boolean.TRUE);
        }
        rset.close();
        pstmt.close();
      }

      if (answer.isError()) throw new Exception(answer.getErrorMessage());
      else return (VOListResponse) answer;

    } catch (Throwable ex) {
      Logger.error(
          username,
          this.getClass().getName(),
          "getItemVariants",
          "Error while fetching item variants list",
          ex);
      throw new Exception(ex.getMessage());
    } finally {
      try {
        pstmt.close();
      } catch (Exception ex2) {
      }
      try {
        if (this.conn == null && conn != null) {
          // close only local connection
          conn.commit();
          conn.close();
        }

      } catch (Exception exx) {
      }
    }
  }
  /** Business logic to execute. */
  public final Response executeCommand(
      Object inputPar,
      UserSessionParameters userSessionPars,
      HttpServletRequest request,
      HttpServletResponse response,
      HttpSession userSession,
      ServletContext context) {
    String serverLanguageId = ((JAIOUserSessionParameters) userSessionPars).getServerLanguageId();

    PreparedStatement pstmt = null;
    Connection conn = null;
    try {
      conn = ConnectionManager.getConnection(context);

      // fires the GenericEvent.CONNECTION_CREATED event...
      EventsManager.getInstance()
          .processEvent(
              new GenericEvent(
                  this,
                  getRequestName(),
                  GenericEvent.CONNECTION_CREATED,
                  (JAIOUserSessionParameters) userSessionPars,
                  request,
                  response,
                  userSession,
                  context,
                  conn,
                  inputPar,
                  null));

      GridParams pars = (GridParams) inputPar;

      BigDecimal rootProgressiveHIE01 =
          (BigDecimal) pars.getOtherGridParams().get(ApplicationConsts.ROOT_PROGRESSIVE_HIE01);
      BigDecimal progressiveHIE01 =
          (BigDecimal) pars.getOtherGridParams().get(ApplicationConsts.PROGRESSIVE_HIE01);
      BigDecimal progressiveHIE02 =
          (BigDecimal) pars.getOtherGridParams().get(ApplicationConsts.PROGRESSIVE_HIE02);
      Boolean productsOnly =
          (Boolean) pars.getOtherGridParams().get(ApplicationConsts.PRODUCTS_ONLY);
      Boolean compsOnly =
          (Boolean) pars.getOtherGridParams().get(ApplicationConsts.COMPONENTS_ONLY);

      HierarchyLevelVO vo =
          (HierarchyLevelVO) pars.getOtherGridParams().get(ApplicationConsts.TREE_FILTER);
      if (vo != null) {
        progressiveHIE01 = vo.getProgressiveHIE01();
        progressiveHIE02 = vo.getProgressiveHie02HIE01();
      }

      // retrieve companies list...
      ArrayList companiesList =
          ((JAIOUserSessionParameters) userSessionPars).getCompanyBa().getCompaniesList("ITM01");
      String companies = "";
      for (int i = 0; i < companiesList.size(); i++)
        companies += "'" + companiesList.get(i).toString() + "',";
      companies = companies.substring(0, companies.length() - 1);

      String sql =
          "select ITM01_ITEMS.COMPANY_CODE_SYS01,ITM01_ITEMS.ITEM_CODE,SYS10_TRANSLATIONS.DESCRIPTION,ITM01_ITEMS.PROGRESSIVE_HIE02,ITM01_ITEMS.MIN_SELLING_QTY_UM_CODE_REG02,"
              + "ITM01_ITEMS.PROGRESSIVE_HIE01,ITM01_ITEMS.SERIAL_NUMBER_REQUIRED,REG02_MEASURE_UNITS.DECIMALS "
              + " from ITM01_ITEMS,SYS10_TRANSLATIONS,REG02_MEASURE_UNITS where "
              + "ITM01_ITEMS.PROGRESSIVE_HIE02=? and "
              + "ITM01_ITEMS.PROGRESSIVE_SYS10=SYS10_TRANSLATIONS.PROGRESSIVE and "
              + "SYS10_TRANSLATIONS.LANGUAGE_CODE=? and "
              + "ITM01_ITEMS.COMPANY_CODE_SYS01 in ("
              + companies
              + ") and "
              + "ITM01_ITEMS.ENABLED='Y' and "
              + "ITM01_ITEMS.MIN_SELLING_QTY_UM_CODE_REG02=REG02_MEASURE_UNITS.UM_CODE ";

      if (productsOnly != null && productsOnly.booleanValue())
        sql += " and ITM01_ITEMS.MANUFACTURE_CODE_PRO01 is not null ";

      if (compsOnly != null && compsOnly.booleanValue())
        sql += " and ITM01_ITEMS.MANUFACTURE_CODE_PRO01 is null ";

      if (rootProgressiveHIE01 == null || !rootProgressiveHIE01.equals(progressiveHIE01)) {
        // retrieve all subnodes of the specified node...
        pstmt =
            conn.prepareStatement(
                "select HIE01_LEVELS.PROGRESSIVE,HIE01_LEVELS.PROGRESSIVE_HIE01,HIE01_LEVELS.LEV from HIE01_LEVELS "
                    + "where ENABLED='Y' and PROGRESSIVE_HIE02=? and PROGRESSIVE>=? "
                    + "order by LEV,PROGRESSIVE_HIE01,PROGRESSIVE");
        pstmt.setBigDecimal(1, progressiveHIE02);
        pstmt.setBigDecimal(2, progressiveHIE01);
        ResultSet rset = pstmt.executeQuery();

        HashSet currentLevelNodes = new HashSet();
        HashSet newLevelNodes = new HashSet();
        String nodes = "";
        int currentLevel = -1;
        while (rset.next()) {
          if (currentLevel != rset.getInt(3)) {
            // next level...
            currentLevel = rset.getInt(3);
            currentLevelNodes = newLevelNodes;
            newLevelNodes = new HashSet();
          }
          if (rset.getBigDecimal(1).equals(progressiveHIE01)) {
            newLevelNodes.add(rset.getBigDecimal(1));
            nodes += rset.getBigDecimal(1) + ",";
          } else if (currentLevelNodes.contains(rset.getBigDecimal(2))) {
            newLevelNodes.add(rset.getBigDecimal(1));
            nodes += rset.getBigDecimal(1) + ",";
          }
        }
        rset.close();
        pstmt.close();
        if (nodes.length() > 0) nodes = nodes.substring(0, nodes.length() - 1);
        sql += " and PROGRESSIVE_HIE01 in (" + nodes + ")";
      }

      Map attribute2dbField = new HashMap();
      attribute2dbField.put("companyCodeSys01ITM01", "ITM01_ITEMS.COMPANY_CODE_SYS01");
      attribute2dbField.put("itemCodeITM01", "ITM01_ITEMS.ITEM_CODE");
      attribute2dbField.put("descriptionSYS10", "SYS10_TRANSLATIONS.DESCRIPTION");
      attribute2dbField.put("progressiveHie02ITM01", "ITM01_ITEMS.PROGRESSIVE_HIE02");
      attribute2dbField.put(
          "minSellingQtyUmCodeReg02ITM01", "ITM01_ITEMS.MIN_SELLING_QTY_UM_CODE_REG02");
      attribute2dbField.put("progressiveHie01ITM01", "ITM01_ITEMS.PROGRESSIVE_HIE01");
      attribute2dbField.put("serialNumberRequiredITM01", "ITM01_ITEMS.SERIAL_NUMBER_REQUIRED");
      attribute2dbField.put("decimalsREG02", "REG02_MEASURE_UNITS.DECIMALS");

      ArrayList values = new ArrayList();
      values.add(progressiveHIE02);
      values.add(serverLanguageId);

      // read from ITM01 table...
      Response answer =
          QueryUtil.getQuery(
              conn,
              userSessionPars,
              sql,
              values,
              attribute2dbField,
              GridItemVO.class,
              "Y",
              "N",
              context,
              pars,
              50,
              true);

      // fires the GenericEvent.BEFORE_COMMIT event...
      EventsManager.getInstance()
          .processEvent(
              new GenericEvent(
                  this,
                  getRequestName(),
                  GenericEvent.BEFORE_COMMIT,
                  (JAIOUserSessionParameters) userSessionPars,
                  request,
                  response,
                  userSession,
                  context,
                  conn,
                  inputPar,
                  answer));
      return answer;

    } catch (Throwable ex) {
      Logger.error(
          userSessionPars.getUsername(),
          this.getClass().getName(),
          "executeCommand",
          "Error while fetching items list",
          ex);
      return new ErrorResponse(ex.getMessage());
    } finally {
      try {
        pstmt.close();
      } catch (Exception ex2) {
      }
      try {
        ConnectionManager.releaseConnection(conn, context);
      } catch (Exception ex1) {
      }
    }
  }