/**
   * Method called by the Form panel to update existing data.
   *
   * @param oldPersistentObject original value object, previous to the changes
   * @param persistentObject value object to save
   * @return an ErrorResponse value object in case of errors, VOResponse if the operation is
   *     successfully completed
   */
  public Response updateRecord(ValueObject oldPersistentObject, ValueObject persistentObject)
      throws Exception {
    // mapping between attributes and database fields...
    Map attribute2dbField = new HashMap();
    attribute2dbField.put("empCode", "EMP_CODE");
    attribute2dbField.put("firstName", "FIRST_NAME");
    attribute2dbField.put("lastName", "LAST_NAME");
    attribute2dbField.put("deptCode", "DEPT_CODE");
    attribute2dbField.put("taskCode", "TASK_CODE");
    attribute2dbField.put("sex", "SEX");
    attribute2dbField.put("hireDate", "HIRE_DATE");
    attribute2dbField.put("salary", "SALARY");
    attribute2dbField.put("note", "NOTE");

    HashSet pk = new HashSet();
    pk.add("empCode");

    Response res =
        QueryUtil.updateTable(
            conn,
            pk,
            oldPersistentObject,
            persistentObject,
            "EMP",
            attribute2dbField,
            "Y",
            "N",
            true);
    if (res.isError()) conn.rollback();
    else conn.commit();
    return res;

    /*
        // an alternative way: you can define your own business logic to store data at hand...
        PreparedStatement stmt = null;
        try {
          stmt = conn.prepareStatement("update EMP set EMP_CODE=?,FIRST_NAME=?,LAST_NAME=?,DEPT_CODE=?,TASK_CODE=?,SEX=?,HIRE_DATE=?,SALARY=?,NOTE=? where EMP_CODE=?");
          EmpVO vo = (EmpVO)persistentObject;
          stmt.setString(1,vo.getEmpCode());
          stmt.setString(2,vo.getFirstName());
          stmt.setString(3,vo.getLastName());
          stmt.setString(4,vo.getDeptCode());
          stmt.setString(5,vo.getTaskCode());
          stmt.setString(6,vo.getSex());
          stmt.setDate(7,vo.getHireDate());
          stmt.setBigDecimal(8,vo.getSalary());
          stmt.setString(9,vo.getNote());
          stmt.setString(10,vo.getEmpCode());
          stmt.execute();
          gridFrame.reloadData();
          return new VOResponse(vo);
        }
        catch (SQLException ex) {
          ex.printStackTrace();
          return new ErrorResponse(ex.getMessage());
        }
        finally {
          try {
            stmt.close();
            conn.commit();
          }
          catch (SQLException ex1) {
          }
        }
    */
  }
  /**
   * This method must be overridden by the subclass to retrieve data and return the valorized value
   * object.
   *
   * @param valueObjectClass value object class
   * @return a VOResponse object if data loading is successfully completed, or an ErrorResponse
   *     object if an error occours
   */
  public Response loadData(Class valueObjectClass) {
    try {
      // since this method could be invoked also when selecting another row on the linked grid,
      // the pk attribute must be recalculated from the grid...
      int row = gridFrame.getGrid().getSelectedRow();
      if (row != -1) {
        GridEmpVO gridVO =
            (GridEmpVO) gridFrame.getGrid().getVOListTableModel().getObjectForRow(row);
        pk = gridVO.getEmpCode();
      }

      String sql =
          "select EMP.EMP_CODE,EMP.FIRST_NAME, EMP.LAST_NAME,EMP.DEPT_CODE,DEPT.DESCRIPTION,EMP.TASK_CODE,TASKS.DESCRIPTION,EMP.SEX,EMP.HIRE_DATE,EMP.SALARY,EMP.NOTE "
              + "from EMP,DEPT,TASKS where EMP.DEPT_CODE=DEPT.DEPT_CODE and EMP.TASK_CODE=TASKS.TASK_CODE and EMP.EMP_CODE='"
              + pk
              + "'";

      // mapping between attributes and database fields...
      Map attribute2dbField = new HashMap();
      attribute2dbField.put("empCode", "EMP.EMP_CODE");
      attribute2dbField.put("firstName", "EMP.FIRST_NAME");
      attribute2dbField.put("lastName", "EMP.LAST_NAME");
      attribute2dbField.put("deptCode", "EMP.DEPT_CODE");
      attribute2dbField.put("deptDescription", "DEPT.DESCRIPTION");
      attribute2dbField.put("taskCode", "EMP.TASK_CODE");
      attribute2dbField.put("taskDescription", "TASKS.DESCRIPTION");
      attribute2dbField.put("sex", "EMP.SEX");
      attribute2dbField.put("hireDate", "EMP.HIRE_DATE");
      attribute2dbField.put("salary", "EMP.SALARY");
      attribute2dbField.put("note", "EMP.NOTE");

      return QueryUtil.getQuery(
          conn,
          sql,
          new ArrayList(), // list of values linked to "?" parameters in sql
          attribute2dbField,
          EmpVO.class, // v.o. to dinamically create for each row...
          "Y",
          "N",
          true // log query...
          );
    } catch (Exception ex) {
      ex.printStackTrace();
      return new ErrorResponse(ex.getMessage());
    }

    /*
        // an alternative way: you can define your own business logic to retrieve data and adding filtering/sorting conditions at hand...
        Statement stmt = null;
        try {
          stmt = conn.createStatement();
          ResultSet rset = stmt.executeQuery(
            "select EMP.EMP_CODE,EMP.FIRST_NAME, EMP.LAST_NAME,EMP.DEPT_CODE,DEPT.DESCRIPTION,EMP.TASK_CODE,TASKS.DESCRIPTION,EMP.SEX,EMP.HIRE_DATE,EMP.SALARY,EMP.NOTE "+
            "from EMP,DEPT,TASKS where EMP.DEPT_CODE=DEPT.DEPT_CODE and EMP.TASK_CODE=TASKS.TASK_CODE and EMP.EMP_CODE='"+pk+"'"
          );
          if (rset.next()) {
            EmpVO vo = new EmpVO();
            vo.setEmpCode(rset.getString(1));
            vo.setFirstName(rset.getString(2));
            vo.setLastName(rset.getString(3));
            vo.setDeptCode(rset.getString(4));
            vo.setDeptDescription(rset.getString(5));
            vo.setTaskCode(rset.getString(6));
            vo.setTaskDescription(rset.getString(7));
            vo.setSex(rset.getString(8));
            vo.setHireDate(rset.getDate(9));
            vo.setSalary(rset.getBigDecimal(10));
            vo.setNote(rset.getString(11));
            return new VOResponse(vo);
          }
          else
            return new ErrorResponse("No data found.");
        }
        catch (SQLException ex) {
          ex.printStackTrace();
          return new ErrorResponse(ex.getMessage());
        }
        finally {
          try {
            stmt.close();
          }
          catch (SQLException ex1) {
          }
        }
    */
  }
  /** 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) {
      }
    }
  }
  /**
   * Method called by the Form panel to insert new data.
   *
   * @param newValueObject value object to save
   * @return an ErrorResponse value object in case of errors, VOResponse if the operation is
   *     successfully completed
   */
  public Response insertRecord(ValueObject newPersistentObject) throws Exception {
    // mapping between attributes and database fields...
    Map attribute2dbField = new HashMap();
    attribute2dbField.put("empCode", "EMP_CODE");
    attribute2dbField.put("firstName", "FIRST_NAME");
    attribute2dbField.put("lastName", "LAST_NAME");
    attribute2dbField.put("deptCode", "DEPT_CODE");
    attribute2dbField.put("taskCode", "TASK_CODE");
    attribute2dbField.put("sex", "SEX");
    attribute2dbField.put("hireDate", "HIRE_DATE");
    attribute2dbField.put("salary", "SALARY");
    attribute2dbField.put("note", "NOTE");

    Response res =
        QueryUtil.insertTable(conn, newPersistentObject, "EMP", attribute2dbField, "Y", "N", true);
    if (res.isError()) {
      conn.rollback();
      return res;
    } else {
      // insert 7 records in WORKING_DAYS, one for each day of week...
      PreparedStatement pstmt = null;
      try {
        pstmt = conn.prepareStatement("insert into WORKING_DAYS(EMP_CODE,DAY) values(?,?)");

        pstmt.setString(1, ((EmpVO) newPersistentObject).getEmpCode());
        pstmt.setInt(2, Calendar.SUNDAY);
        pstmt.execute();

        pstmt.setString(1, ((EmpVO) newPersistentObject).getEmpCode());
        pstmt.setInt(2, Calendar.MONDAY);
        pstmt.execute();

        pstmt.setString(1, ((EmpVO) newPersistentObject).getEmpCode());
        pstmt.setInt(2, Calendar.TUESDAY);
        pstmt.execute();

        pstmt.setString(1, ((EmpVO) newPersistentObject).getEmpCode());
        pstmt.setInt(2, Calendar.WEDNESDAY);
        pstmt.execute();

        pstmt.setString(1, ((EmpVO) newPersistentObject).getEmpCode());
        pstmt.setInt(2, Calendar.THURSDAY);
        pstmt.execute();

        pstmt.setString(1, ((EmpVO) newPersistentObject).getEmpCode());
        pstmt.setInt(2, Calendar.FRIDAY);
        pstmt.execute();

        pstmt.setString(1, ((EmpVO) newPersistentObject).getEmpCode());
        pstmt.setInt(2, Calendar.SATURDAY);
        pstmt.execute();

        conn.commit();

        frame
            .getGrid()
            .getOtherGridParams()
            .put("empCode", ((EmpVO) newPersistentObject).getEmpCode());
        frame.getGrid().reloadData();

        return res;
      } catch (Exception ex) {
        conn.rollback();
        return new ErrorResponse(ex.getMessage());
      } finally {
        try {
          pstmt.close();
        } catch (Exception ex1) {
        }
      }
    }

    /*
        // an alternative way: you can define your own business logic to store data at hand...
        PreparedStatement stmt = null;
        try {
          stmt = conn.prepareStatement("insert into EMP(EMP_CODE,FIRST_NAME,LAST_NAME,DEPT_CODE,TASK_CODE,SEX,HIRE_DATE,SALARY,NOTE) values(?,?,?,?,?,?,?,?,?)");
          EmpVO vo = (EmpVO)newPersistentObject;
          stmt.setString(1,vo.getEmpCode());
          stmt.setString(2,vo.getFirstName());
          stmt.setString(3,vo.getLastName());
          stmt.setString(4,vo.getDeptCode());
          stmt.setString(5,vo.getTaskCode());
          stmt.setString(6,vo.getSex());
          stmt.setDate(7,vo.getHireDate());
          stmt.setBigDecimal(8,vo.getSalary());
          stmt.setString(9,vo.getNote());

          stmt.execute();
          pk = vo.getEmpCode();
          gridFrame.reloadData();
          return new VOResponse(vo);
        }
        catch (SQLException ex) {
          ex.printStackTrace();
          return new ErrorResponse(ex.getMessage());
        }
        finally {
          try {
            stmt.close();
            conn.commit();
          }
          catch (SQLException ex1) {
          }
        }
    */
  }
  /**
   * Retrieve a DefaultTreeModel object that contains the bill of materials (MaterialVO objects) of
   * the specified product.
   */
  public static VOResponse getBillOfMaterials(
      Connection conn,
      CurrenciesBean compCurr,
      ItemPK pk,
      String langId,
      String username,
      ArrayList customizedFields)
      throws Throwable {
    PreparedStatement pstmt = null;
    PreparedStatement pstmt2 = null;
    try {

      String sql1 =
          "select ITM03_COMPONENTS.COMPANY_CODE_SYS01,ITM03_COMPONENTS.ITEM_CODE_ITM01,ITM01_ITEMS.MIN_SELLING_QTY_UM_CODE_REG02,"
              + "ITM03_COMPONENTS.PARENT_ITEM_CODE_ITM01,ITM03_COMPONENTS.QTY,SYS10_COMPANY_TRANSLATIONS.DESCRIPTION "
              + "from ITM03_COMPONENTS,ITM01_ITEMS,SYS10_COMPANY_TRANSLATIONS "
              + "where "
              + "ITM03_COMPONENTS.COMPANY_CODE_SYS01 = ITM01_ITEMS.COMPANY_CODE_SYS01 and "
              + "ITM03_COMPONENTS.ITEM_CODE_ITM01 = ITM01_ITEMS.ITEM_CODE and "
              + "ITM01_ITEMS.COMPANY_CODE_SYS01 = SYS10_COMPANY_TRANSLATIONS.COMPANY_CODE_SYS01 and "
              + "ITM01_ITEMS.PROGRESSIVE_SYS10 = SYS10_COMPANY_TRANSLATIONS.PROGRESSIVE and "
              + "SYS10_COMPANY_TRANSLATIONS.LANGUAGE_CODE=? and ITM03_COMPONENTS.ENABLED='Y' and "
              + "ITM03_COMPONENTS.COMPANY_CODE_SYS01=? and "
              + "ITM03_COMPONENTS.PARENT_ITEM_CODE_ITM01 in (";
      String sql3 = ") order by ITM03_COMPONENTS.PARENT_ITEM_CODE_ITM01,ITM03_COMPONENTS.SEQUENCE";

      Map attribute2dbField = new HashMap();
      attribute2dbField.put("companyCodeSys01ITM03", "ITM03_COMPONENTS.COMPANY_CODE_SYS01");
      attribute2dbField.put("itemCodeItm01ITM03", "ITM03_COMPONENTS.ITEM_CODE_ITM01");
      attribute2dbField.put("parentItemCodeItm01ITM03", "ITM03_COMPONENTS.PARENT_ITEM_CODE_ITM01");
      attribute2dbField.put("descriptionSYS10", "SYS10_COMPANY_TRANSLATIONS.DESCRIPTION");
      attribute2dbField.put("qtyITM03", "ITM03_COMPONENTS.QTY");
      attribute2dbField.put(
          "minSellingQtyUmCodeReg02ITM01", "ITM01_ITEMS.MIN_SELLING_QTY_UM_CODE_REG02");

      ArrayList values = new ArrayList();
      values.add(langId);
      values.add(pk.getCompanyCodeSys01ITM01());

      // retrieve the whole tree...
      MaterialVO compVO = new MaterialVO();
      compVO.setCompanyCodeSys01ITM03(pk.getCompanyCodeSys01ITM01());
      compVO.setDescriptionSYS10("");
      compVO.setItemCodeItm01ITM03(pk.getItemCodeITM01());
      compVO.setQtyITM03(new BigDecimal(1));
      DefaultMutableTreeNode node = new OpenSwingTreeNode(compVO);
      DefaultTreeModel model = new DefaultTreeModel(node);

      String sql2 = "'" + pk.getItemCodeITM01() + "'";
      ArrayList nodes = new ArrayList();
      ArrayList nodes2 = new ArrayList(); // current nodes...
      Hashtable auxnodes = new Hashtable();
      nodes.add(node);

      // read from ITM03 table...
      Response res = null;
      java.util.List rows = null;
      java.util.List auxlist = null;
      DefaultMutableTreeNode auxnode, auxnode2 = null;
      String currentItemCode = null;

      do {
        res =
            QueryUtil.getQuery(
                conn,
                new UserSessionParameters(username),
                sql1 + sql2 + sql3,
                values,
                attribute2dbField,
                MaterialVO.class,
                "Y",
                "N",
                null,
                new GridParams(),
                true);
        if (!res.isError()) {
          rows = ((VOListResponse) res).getRows();
          auxnodes.clear();
          nodes2 = new ArrayList();
          sql2 = "";

          // group items per item code parent...
          for (int i = 0; i < rows.size(); i++) {
            compVO = (MaterialVO) rows.get(i);
            sql2 += "'" + compVO.getItemCodeItm01ITM03() + "',";
            auxnode = new OpenSwingTreeNode(compVO);
            auxlist = (ArrayList) auxnodes.get(compVO.getParentItemCodeItm01ITM03());
            if (auxlist == null) {
              auxlist = new ArrayList();
              auxnodes.put(compVO.getParentItemCodeItm01ITM03(), auxlist);
            }
            auxlist.add(auxnode);
            nodes2.add(auxnode);
          }

          if (sql2.length() > 0) sql2 = sql2.substring(0, sql2.length() - 1);

          // add items to the corresponding parent...
          for (int i = 0; i < nodes.size(); i++) {
            auxnode = (DefaultMutableTreeNode) nodes.get(i);
            compVO = (MaterialVO) auxnode.getUserObject();
            auxlist = (ArrayList) auxnodes.get(compVO.getItemCodeItm01ITM03());
            if (auxlist != null)
              for (int j = 0; j < auxlist.size(); j++) {
                auxnode.add((DefaultMutableTreeNode) auxlist.get(j));
                ((MaterialVO) ((DefaultMutableTreeNode) auxlist.get(j)).getUserObject())
                    .setQtyITM03(
                        ((MaterialVO) ((DefaultMutableTreeNode) auxlist.get(j)).getUserObject())
                            .getQtyITM03()
                            .multiply(((MaterialVO) auxnode.getUserObject()).getQtyITM03())
                            .setScale(5, BigDecimal.ROUND_HALF_UP));
              }
          }

          nodes = nodes2;
        }

      } while (!res.isError() && rows.size() > 0);
      if (res.isError()) throw new Exception(res.getErrorMessage());

      res =
          compCurr.loadCompanyCurrency(
              pk.getCompanyCodeSys01ITM01(), langId, username, customizedFields);
      if (res.isError()) throw new Exception(res.getErrorMessage());
      CurrencyVO currVO = (CurrencyVO) ((VOResponse) res).getVo();

      // navigate through the tree and fetch item prices...
      pstmt =
          conn.prepareStatement(
              "select sum(PUR04_SUPPLIER_PRICES.VALUE),count(PUR04_SUPPLIER_PRICES.VALUE),PUR03_SUPPLIER_PRICELISTS.CURRENCY_CODE_REG03 "
                  + "from PUR04_SUPPLIER_PRICES,PUR03_SUPPLIER_PRICELISTS "
                  + "where "
                  + "PUR04_SUPPLIER_PRICES.COMPANY_CODE_SYS01=PUR03_SUPPLIER_PRICELISTS.COMPANY_CODE_SYS01 and "
                  + "PUR04_SUPPLIER_PRICES.PROGRESSIVE_REG04=PUR03_SUPPLIER_PRICELISTS.PROGRESSIVE_REG04 and "
                  + "PUR04_SUPPLIER_PRICES.PRICELIST_CODE_PUR03=PUR03_SUPPLIER_PRICELISTS.PRICELIST_CODE and "
                  + "PUR04_SUPPLIER_PRICES.COMPANY_CODE_SYS01=? and "
                  + "PUR04_SUPPLIER_PRICES.ITEM_CODE_ITM01=? and "
                  + "PUR04_SUPPLIER_PRICES.START_DATE<=? and "
                  + "PUR04_SUPPLIER_PRICES.END_DATE>=? "
                  + "group by PUR03_SUPPLIER_PRICELISTS.CURRENCY_CODE_REG03");
      pstmt2 =
          conn.prepareStatement(
              "select sum(PRO02_MANUFACTURE_PHASES.VALUE) "
                  + "from PRO02_MANUFACTURE_PHASES,ITM01_ITEMS "
                  + "where "
                  + "PRO02_MANUFACTURE_PHASES.COMPANY_CODE_SYS01=ITM01_ITEMS.COMPANY_CODE_SYS01 and "
                  + "PRO02_MANUFACTURE_PHASES.MANUFACTURE_CODE_PRO01=ITM01_ITEMS.MANUFACTURE_CODE_PRO01 and "
                  + "ITM01_ITEMS.COMPANY_CODE_SYS01=? and "
                  + "ITM01_ITEMS.ITEM_CODE=?");
      res = expandNode(conn, currVO, pstmt, pstmt2, (DefaultMutableTreeNode) model.getRoot());
      if (res.isError()) throw new Exception(res.getErrorMessage());

      return new VOResponse(model);
    } catch (Exception ex1) {
      Logger.error(
          username, "", "getBillOfMaterials", "Error while fetching bill of materials", ex1);
      throw new Exception(ex1.getMessage());
    } finally {
      try {
        pstmt.close();
      } catch (Exception ex) {
      }
      try {
        pstmt2.close();
      } catch (Exception ex) {
      }
    }
  }
  /** 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) {
      }
    }
  }
  /** 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) {
      }
    }
  }
  /** Business logic to execute. */
  public static VOResponse loadItemImplosion(
      Connection conn, ItemPK pk, String langId, String username) throws Throwable {
    PreparedStatement pstmt = null;
    PreparedStatement pstmt2 = null;
    try {

      String sql1 =
          "select ITM01_ITEMS.COMPANY_CODE_SYS01,ITM01_ITEMS.ITEM_CODE,ITM01_ITEMS.MIN_SELLING_QTY_UM_CODE_REG02,"
              + "ITM03_COMPONENTS.ITEM_CODE_ITM01,ITM03_COMPONENTS.QTY,SYS10_COMPANY_TRANSLATIONS.DESCRIPTION "
              + "from ITM03_COMPONENTS,ITM01_ITEMS,SYS10_COMPANY_TRANSLATIONS "
              + "where "
              + "ITM03_COMPONENTS.COMPANY_CODE_SYS01 = ITM01_ITEMS.COMPANY_CODE_SYS01 and "
              + "ITM03_COMPONENTS.PARENT_ITEM_CODE_ITM01 = ITM01_ITEMS.ITEM_CODE and "
              + "ITM01_ITEMS.COMPANY_CODE_SYS01 = SYS10_COMPANY_TRANSLATIONS.COMPANY_CODE_SYS01 and "
              + "ITM01_ITEMS.PROGRESSIVE_SYS10 = SYS10_COMPANY_TRANSLATIONS.PROGRESSIVE and "
              + "SYS10_COMPANY_TRANSLATIONS.LANGUAGE_CODE=? and ITM03_COMPONENTS.ENABLED='Y' and "
              + "ITM03_COMPONENTS.COMPANY_CODE_SYS01=? and "
              + "ITM03_COMPONENTS.ITEM_CODE_ITM01 in (";
      String sql3 = ") ";

      Map attribute2dbField = new HashMap();
      attribute2dbField.put("companyCodeSys01ITM03", "ITM01_ITEMS.COMPANY_CODE_SYS01");
      attribute2dbField.put("itemCodeItm01ITM03", "ITM03_COMPONENTS.ITEM_CODE_ITM01");
      attribute2dbField.put("parentItemCodeItm01ITM03", "ITM01_ITEMS.ITEM_CODE");
      attribute2dbField.put("descriptionSYS10", "SYS10_COMPANY_TRANSLATIONS.DESCRIPTION");
      attribute2dbField.put("qtyITM03", "ITM03_COMPONENTS.QTY");
      attribute2dbField.put(
          "minSellingQtyUmCodeReg02ITM01", "ITM01_ITEMS.MIN_SELLING_QTY_UM_CODE_REG02");

      ArrayList values = new ArrayList();
      values.add(langId);
      values.add(pk.getCompanyCodeSys01ITM01());

      // retrieve the whole tree...
      MaterialVO rootVO = new MaterialVO();
      rootVO.setCompanyCodeSys01ITM03(pk.getCompanyCodeSys01ITM01());
      rootVO.setDescriptionSYS10("");
      rootVO.setParentItemCodeItm01ITM03("");
      rootVO.setItemCodeItm01ITM03("");
      DefaultMutableTreeNode root = new OpenSwingTreeNode(rootVO);
      DefaultTreeModel model = new DefaultTreeModel(root);

      String sql2 = "'" + pk.getItemCodeITM01() + "'";

      // read from ITM03 table...
      Response res = null;
      java.util.List rows = null;
      DefaultMutableTreeNode auxnode, auxnode2;
      MaterialVO auxVO = null;
      Hashtable nodes = new Hashtable(); // collections of <item component code,related node>
      Hashtable auxnodes = new Hashtable(); // collections of <item component code,related node>
      Enumeration en = null;

      do {
        res =
            QueryUtil.getQuery(
                conn,
                new UserSessionParameters(username),
                sql1 + sql2 + sql3,
                values,
                attribute2dbField,
                MaterialVO.class,
                "Y",
                "N",
                null,
                new GridParams(),
                true);
        if (!res.isError()) {
          rows = ((VOListResponse) res).getRows();

          sql2 = "";

          for (int i = 0; i < rows.size(); i++) {
            auxVO = (MaterialVO) rows.get(i);
            sql2 += "'" + auxVO.getParentItemCodeItm01ITM03() + "',";
            auxnode = new OpenSwingTreeNode(auxVO);
            auxnode2 = (DefaultMutableTreeNode) nodes.get(auxVO.getItemCodeItm01ITM03());
            if (auxnode2 != null) auxnode.add(auxnode2);
            nodes.remove(auxVO.getItemCodeItm01ITM03());
            auxnodes.put(auxVO.getParentItemCodeItm01ITM03(), auxnode);
          }

          en = nodes.keys();
          while (en.hasMoreElements()) {
            root.add((DefaultMutableTreeNode) nodes.get(en.nextElement()));
          }

          nodes = auxnodes;
          if (sql2.length() > 0) sql2 = sql2.substring(0, sql2.length() - 1);
        }
      } while (!res.isError() && rows.size() > 0);
      if (res.isError()) throw new Exception(res.getErrorMessage());

      return new VOResponse(model);
    } catch (Exception ex1) {
      Logger.error(
          username,
          BillOfMaterialsUtil.class.getName(),
          "executeCommand",
          "Error while fetching products that use the specified component",
          ex1);
      throw new Exception(ex1.getMessage());
    }
  }
  /** 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) {
      }
    }
  }
  /** 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 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) {
    Connection conn = null;
    PreparedStatement pstmt = null;
    try {
      String serverLanguageId = ((JAIOUserSessionParameters) userSessionPars).getServerLanguageId();
      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));

      ArrayList oldVOs = ((ArrayList[]) inputPar)[0];
      ArrayList newVOs = ((ArrayList[]) inputPar)[1];

      Map attribute2dbField = new HashMap();
      attribute2dbField.put("companyCodeSys01DOC20", "COMPANY_CODE_SYS01");
      attribute2dbField.put("progressiveDoc14DOC20", "PROGRESSIVE_DOC14");
      attribute2dbField.put("progressiveSys10DOC20", "PROGRESSIVE_SYS10");
      attribute2dbField.put("textValueDOC20", "TEXT_VALUE");
      attribute2dbField.put("numValueDOC20", "NUM_VALUE");
      attribute2dbField.put("dateValueDOC20", "DATE_VALUE");

      HashSet pkAttributes = new HashSet();
      pkAttributes.add("companyCodeSys01DOC20");
      pkAttributes.add("progressiveDoc14DOC20");
      pkAttributes.add("progressiveSys10DOC20");

      Response res = null;
      DocPropertyVO oldVO = null;
      DocPropertyVO newVO = null;

      pstmt =
          conn.prepareStatement(
              "select PROGRESSIVE_DOC14 from DOC20_DOC_PROPERTIES where "
                  + "COMPANY_CODE_SYS01=? and PROGRESSIVE_DOC14=? and PROGRESSIVE_SYS10=?");
      ResultSet rset = null;

      for (int i = 0; i < oldVOs.size(); i++) {
        oldVO = (DocPropertyVO) oldVOs.get(i);
        newVO = (DocPropertyVO) newVOs.get(i);

        // check if the record already exists: if it does not exist, then insert it...
        pstmt.setString(1, newVO.getCompanyCodeSys01DOC20());
        pstmt.setBigDecimal(2, newVO.getProgressiveDoc14DOC20());
        pstmt.setBigDecimal(3, newVO.getProgressiveSys10DOC20());
        rset = pstmt.executeQuery();
        if (rset.next()) {
          // the record exixts: it will be updated...
          res =
              QueryUtil.updateTable(
                  conn,
                  userSessionPars,
                  pkAttributes,
                  oldVO,
                  newVO,
                  "DOC20_DOC_PROPERTIES",
                  attribute2dbField,
                  "Y",
                  "N",
                  context,
                  true);
          if (res.isError()) {
            conn.rollback();
            return res;
          }
        } else {
          // the record does not exixt: it will be inserted...
          res =
              QueryUtil.insertTable(
                  conn,
                  userSessionPars,
                  newVO,
                  "DOC20_DOC_PROPERTIES",
                  attribute2dbField,
                  "Y",
                  "N",
                  context,
                  true);
          if (res.isError()) {
            conn.rollback();
            return res;
          }
        }
        rset.close();
      }

      Response answer = new VOListResponse(newVOs, false, newVOs.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 updating property values for the specified document",
          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) {
      }
    }
  }