public final Response executeCommand(
      Object inputPar,
      UserSessionParameters userSessionPars,
      HttpServletRequest request,
      HttpServletResponse response,
      HttpSession userSession,
      ServletContext context) {
    GridParams gridParams = (GridParams) inputPar;
    try {
      String companyCode =
          (String) gridParams.getOtherGridParams().get(ApplicationConsts.COMPANY_CODE_SYS01);
      BigDecimal progressiveHie02 =
          (BigDecimal) gridParams.getOtherGridParams().get(ApplicationConsts.PROGRESSIVE_HIE02);

      ItemFields bean = (ItemFields) JAIOBeanFactory.getInstance().getBean(ItemFields.class);
      Response answer =
          bean.loadItemFields(
              companyCode,
              progressiveHie02,
              ((JAIOUserSessionParameters) userSessionPars).getServerLanguageId(),
              userSessionPars.getUsername());

      return answer;
    } catch (Throwable ex) {
      Logger.error(
          userSessionPars.getUsername(),
          this.getClass().getName(),
          "executeCommand",
          "Error while processing request",
          ex);
      return new ErrorResponse(ex.getMessage());
    }
  }
  public final Response executeCommand(
      Object inputPar,
      UserSessionParameters userSessionPars,
      HttpServletRequest request,
      HttpServletResponse response,
      HttpSession userSession,
      ServletContext context) {
    try {
      Object[] pars = (Object[]) inputPar;
      String companyCodeSys01 = (String) pars[0];
      String warehouseCodeWAR01 = (String) pars[1];
      BigDecimal progressiveWAR06 = (BigDecimal) pars[2];

      Inventories bean = (Inventories) JAIOBeanFactory.getInstance().getBean(Inventories.class);
      Response answer =
          bean.confirmInventory(
              companyCodeSys01,
              warehouseCodeWAR01,
              progressiveWAR06,
              ((JAIOUserSessionParameters) userSessionPars).getServerLanguageId(),
              userSessionPars.getUsername());

      return answer;
    } catch (Throwable ex) {
      Logger.error(
          userSessionPars.getUsername(),
          this.getClass().getName(),
          "executeCommand",
          "Error while processing request",
          ex);
      return new ErrorResponse(ex.getMessage());
    }
  }
  public final Response executeCommand(
      Object inputPar,
      UserSessionParameters userSessionPars,
      HttpServletRequest request,
      HttpServletResponse response,
      HttpSession userSession,
      ServletContext context) {
    ArrayList vos = (ArrayList) inputPar;
    try {

      ItemDiscounts bean =
          (ItemDiscounts) JAIOBeanFactory.getInstance().getBean(ItemDiscounts.class);
      Response answer =
          bean.deleteItemDiscounts(
              vos,
              ((JAIOUserSessionParameters) userSessionPars).getServerLanguageId(),
              userSessionPars.getUsername());

      return answer;
    } catch (Throwable ex) {
      Logger.error(
          userSessionPars.getUsername(),
          this.getClass().getName(),
          "executeCommand",
          "Error while processing request",
          ex);
      return new ErrorResponse(ex.getMessage());
    }
  }
  public final Response executeCommand(
      Object inputPar,
      UserSessionParameters userSessionPars,
      HttpServletRequest request,
      HttpServletResponse response,
      HttpSession userSession,
      ServletContext context) {
    String companyCode = (String) inputPar;
    try {

      Companies bean = (Companies) JAIOBeanFactory.getInstance().getBean(Companies.class);
      Response answer =
          bean.loadCompany(
              companyCode,
              ((JAIOUserSessionParameters) userSessionPars).getServerLanguageId(),
              userSessionPars.getUsername());

      return answer;
    } catch (Throwable ex) {
      Logger.error(
          userSessionPars.getUsername(),
          this.getClass().getName(),
          "executeCommand",
          "Error while processing request",
          ex);
      return new ErrorResponse(ex.getMessage());
    }
  }
  public final Response executeCommand(
      Object inputPar,
      UserSessionParameters userSessionPars,
      HttpServletRequest request,
      HttpServletResponse response,
      HttpSession userSession,
      ServletContext context) {
    try {
      String tableName = ((Object[]) inputPar)[0].toString();
      ArrayList oldVOs = (ArrayList) ((Object[]) inputPar)[1];
      ArrayList newVOs = (ArrayList) ((Object[]) inputPar)[2];

      VariantTypes bean = (VariantTypes) JAIOBeanFactory.getInstance().getBean(VariantTypes.class);
      Response answer =
          bean.updateVariantTypes(
              tableName,
              oldVOs,
              newVOs,
              ((JAIOUserSessionParameters) userSessionPars).getServerLanguageId(),
              userSessionPars.getUsername());

      return answer;
    } catch (Throwable ex) {
      Logger.error(
          userSessionPars.getUsername(),
          this.getClass().getName(),
          "executeCommand",
          "Error while processing request",
          ex);
      return new ErrorResponse(ex.getMessage());
    }
  }
  public final Response executeCommand(
      Object inputPar,
      UserSessionParameters userSessionPars,
      HttpServletRequest request,
      HttpServletResponse response,
      HttpSession userSession,
      ServletContext context) {
    try {
      // retrieve internationalization settings (Resources object)...
      ServerResourcesFactory factory =
          (ServerResourcesFactory) context.getAttribute(Controller.RESOURCES_FACTORY);
      String serverLanguageId = ((JAIOUserSessionParameters) userSessionPars).getServerLanguageId();
      String t1 =
          factory
              .getResources(serverLanguageId)
              .getResource("there is already another people with the same first and last name.");
      String t2 =
          factory
              .getResources(serverLanguageId)
              .getResource("there is already another organization with the same corporate name.");
      String imagePath =
          (String)
              ((JAIOUserSessionParameters) userSessionPars)
                  .getAppParams()
                  .get(ApplicationConsts.IMAGE_PATH);

      Subjects bean = (Subjects) JAIOBeanFactory.getInstance().getBean(Subjects.class);
      Response answer = null;
      if (inputPar instanceof PeopleVO)
        answer =
            bean.insertPeople(
                (PeopleVO) inputPar,
                t1,
                t2,
                ((JAIOUserSessionParameters) userSessionPars).getServerLanguageId(),
                userSessionPars.getUsername());
      else
        answer =
            bean.insertOrganization(
                (OrganizationVO) inputPar,
                imagePath,
                t1,
                t2,
                ((JAIOUserSessionParameters) userSessionPars).getServerLanguageId(),
                userSessionPars.getUsername());

      return answer;
    } catch (Throwable ex) {
      Logger.error(
          userSessionPars.getUsername(),
          this.getClass().getName(),
          "executeCommand",
          "Error while processing request",
          ex);
      return new ErrorResponse(ex.getMessage());
    }
  }
Esempio n. 7
0
  /** Business logic to execute. */
  public VOResponse deleteCharges(ArrayList list, String serverLanguageId, String username)
      throws Throwable {
    Statement stmt = null;
    Connection conn = null;
    try {
      if (this.conn == null) conn = getConn();
      else conn = this.conn;
      stmt = conn.createStatement();

      ChargeVO vo = null;
      for (int i = 0; i < list.size(); i++) {
        // logically delete the record in SAL06...
        vo = (ChargeVO) list.get(i);
        stmt.execute(
            "update SAL06_CHARGES set ENABLED='N' where COMPANY_CODE_SYS01='"
                + vo.getCompanyCodeSys01SAL06()
                + "' and CHARGE_CODE='"
                + vo.getChargeCodeSAL06()
                + "'");
      }

      return new VOResponse(new Boolean(true));
    } catch (Throwable ex) {
      Logger.error(
          username,
          this.getClass().getName(),
          "executeCommand",
          "Error while deleting existing charges",
          ex);
      try {
        if (this.conn == null && conn != null)
          // rollback only local connection
          conn.rollback();
      } catch (Exception ex3) {
      }

      throw new Exception(ex.getMessage());
    } finally {
      try {
        stmt.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 updateWindowCustomizations(
      ArrayList oldRows, ArrayList newRows, String serverLanguageId, String username)
      throws Throwable {
    Statement stmt = null;
    Connection conn = null;
    try {
      if (this.conn == null) conn = getConn();
      else conn = this.conn;

      WindowCustomizationVO oldVO = null;
      WindowCustomizationVO newVO = null;
      for (int i = 0; i < oldRows.size(); i++) {
        oldVO = (WindowCustomizationVO) oldRows.get(i);
        newVO = (WindowCustomizationVO) newRows.get(i);
        TranslationUtils.updateTranslation(
            oldVO.getDescriptionSYS10(),
            newVO.getDescriptionSYS10(),
            newVO.getProgressiveSys10SYS12(),
            serverLanguageId,
            conn);
      }

      return new VOListResponse(newRows, false, newRows.size());
    } catch (Throwable ex) {
      Logger.error(
          username,
          this.getClass().getName(),
          "executeCommand",
          "Error while updating customized columns",
          ex);
      try {
        if (this.conn == null && conn != null)
          // rollback only local connection
          conn.rollback();
      } catch (Exception ex3) {
      }

      throw new Exception(ex.getMessage());
    } finally {
      try {
        if (this.conn == null && conn != null) {
          // close only local connection
          conn.commit();
          conn.close();
        }

      } catch (Exception exx) {
      }
    }
  }
  public final Response executeCommand(
      Object inputPar,
      UserSessionParameters userSessionPars,
      HttpServletRequest request,
      HttpServletResponse response,
      HttpSession userSession,
      ServletContext context) {
    try {
      DetailSaleDocRowVO rowVO = (DetailSaleDocRowVO) inputPar;
      VariantDescriptionsVO vo =
          (VariantDescriptionsVO)
              ((JAIOUserSessionParameters) userSessionPars)
                  .getVariantDescriptionsVO()
                  .get(rowVO.getCompanyCodeSys01DOC02());
      InsertSaleItem bean =
          (InsertSaleItem) JAIOBeanFactory.getInstance().getBean(InsertSaleItem.class);
      Response answer =
          bean.insertSaleItem(
              vo.getVariant1Descriptions(),
              vo.getVariant2Descriptions(),
              vo.getVariant3Descriptions(),
              vo.getVariant4Descriptions(),
              vo.getVariant5Descriptions(),
              rowVO,
              ((JAIOUserSessionParameters) userSessionPars).getServerLanguageId(),
              userSessionPars.getUsername());

      return answer;
    } catch (Throwable ex) {
      Logger.error(
          userSessionPars.getUsername(),
          this.getClass().getName(),
          "executeCommand",
          "Error while processing request",
          ex);
      return new ErrorResponse(ex.getMessage());
    }
  }
  /**
   * Check if components required by specified products are available.
   *
   * @param products list of ProdOrderProductVO objects
   * @params compAltComps collection of <component item code,HashSet of alternative component item
   *     codes>; filled by this method (and given back by reference)
   * @return VOListResponse of ProdOrderComponentVO objects
   */
  public final Response checkComponentsAvailability(
      Connection conn,
      Hashtable compAltComps,
      ArrayList products,
      UserSessionParameters userSessionPars,
      HttpServletRequest request,
      HttpServletResponse response,
      HttpSession userSession,
      ServletContext context) {
    String serverLanguageId = ((JAIOUserSessionParameters) userSessionPars).getServerLanguageId();
    try {

      // retrieve internationalization settings (Resources object)...
      ServerResourcesFactory factory =
          (ServerResourcesFactory) context.getAttribute(Controller.RESOURCES_FACTORY);
      Resources resources = factory.getResources(userSessionPars.getLanguageId());

      if (products.size() == 0) {
        return new VOListResponse(new ArrayList(), false, 0);
      }

      // fill in comps hashtable with the collection of required components...
      ItemPK pk = null;
      ProdOrderProductVO prodVO = null;
      ArrayList components = null;
      MaterialVO compVO = null;
      Response res = null;
      ProdOrderComponentVO componentVO = null;
      Hashtable comps =
          new Hashtable(); // collection of <component item code,ProdOrderComponentVO object>
      for (int i = 0; i < products.size(); i++) {
        // retrieve bill of materials for each product...
        prodVO = (ProdOrderProductVO) products.get(i);
        pk = new ItemPK(prodVO.getCompanyCodeSys01DOC23(), prodVO.getItemCodeItm01DOC23());
        res =
            bean.getBillOfMaterials(
                conn, pk, userSessionPars, request, response, userSession, context);
        if (res.isError()) {
          return res;
        }

        // extract components only (leaf nodes)...
        components =
            getComponents(
                (DefaultMutableTreeNode) ((TreeModel) ((VOResponse) res).getVo()).getRoot());
        for (int j = 0; j < components.size(); j++) {
          compVO = (MaterialVO) components.get(j);
          componentVO = (ProdOrderComponentVO) comps.get(compVO.getItemCodeItm01ITM03());
          if (componentVO == null) {
            componentVO = new ProdOrderComponentVO();
            comps.put(compVO.getItemCodeItm01ITM03(), componentVO);
            componentVO.setAvailableQty(new BigDecimal(0));
            componentVO.setCompanyCodeSys01DOC24(compVO.getCompanyCodeSys01ITM03());
            componentVO.setDescriptionSYS10(compVO.getDescriptionSYS10());
            componentVO.setDocNumberDOC24(prodVO.getDocNumberDOC23());
            componentVO.setDocYearDOC24(prodVO.getDocYearDOC23());
            componentVO.setItemCodeItm01DOC24(compVO.getItemCodeItm01ITM03());
            componentVO.setMinSellingQtyUmCodeReg02ITM01(compVO.getMinSellingQtyUmCodeReg02ITM01());
            componentVO.setQtyDOC24(new BigDecimal(0));
          }
          componentVO.setQtyDOC24(
              componentVO.getQtyDOC24().add(compVO.getQtyITM03().multiply(prodVO.getQtyDOC23())));
        }
      }

      // check components availability in the specified warehouse...
      Enumeration en = comps.keys();
      GridParams gridParams = new GridParams();
      gridParams
          .getOtherGridParams()
          .put(ApplicationConsts.COMPANY_CODE_SYS01, prodVO.getCompanyCodeSys01DOC23());
      gridParams
          .getOtherGridParams()
          .put(ApplicationConsts.WAREHOUSE_CODE, prodVO.getWarehouseCodeWar01DOC22());
      gridParams.getOtherGridParams().put(ApplicationConsts.LOAD_ALL, Boolean.TRUE);
      ItemAvailabilityVO availVO = null;
      BigDecimal availability, altAvailability, delta;
      String itemCode = null;
      ArrayList list, availList;
      AltComponentVO altVO = null;
      ArrayList alternativeComps = new ArrayList();
      ArrayList compsToRemove = new ArrayList();
      ProdOrderComponentVO altComponentVO = null;
      HashSet altCodes = null; // list of alternative component item codes...
      BigDecimal altQty = null;
      while (en.hasMoreElements()) {
        itemCode = en.nextElement().toString();
        componentVO = (ProdOrderComponentVO) comps.get(itemCode);

        gridParams
            .getOtherGridParams()
            .put(
                ApplicationConsts.ITEM_PK, new ItemPK(prodVO.getCompanyCodeSys01DOC23(), itemCode));
        res =
            avail.executeCommand(
                gridParams, userSessionPars, request, response, userSession, context);
        if (res.isError()) return res;

        availList = ((VOListResponse) res).getRows();
        componentVO.setAvailabilities(availList);
        availability = new BigDecimal(0);
        for (int i = 0; i < availList.size(); i++) {
          availVO = (ItemAvailabilityVO) availList.get(i);
          availability = availability.add(availVO.getAvailableQtyWAR03());
        }
        componentVO.setAvailableQty(availability);

        if (componentVO.getQtyDOC24().doubleValue() > componentVO.getAvailableQty().doubleValue()) {
          // check if there exist some alternative component...
          res =
              altComps.executeCommand(
                  gridParams, userSessionPars, request, response, userSession, context);
          if (res.isError()) return res;
          list = ((VOListResponse) res).getRows();
          for (int i = 0; i < list.size(); i++) {
            altVO = (AltComponentVO) list.get(i);
            gridParams
                .getOtherGridParams()
                .put(
                    ApplicationConsts.ITEM_PK,
                    new ItemPK(prodVO.getCompanyCodeSys01DOC23(), altVO.getItemCodeItm01ITM04()));
            res =
                avail.executeCommand(
                    gridParams, userSessionPars, request, response, userSession, context);
            if (res.isError()) return res;
            availList = ((VOListResponse) res).getRows();
            altAvailability = new BigDecimal(0);
            for (int j = 0; j < availList.size(); j++) {
              availVO = (ItemAvailabilityVO) availList.get(j);
              altAvailability = altAvailability.add(availVO.getAvailableQtyWAR03());
            }
            if (altAvailability.doubleValue() > 0) {
              altComponentVO = new ProdOrderComponentVO();
              altComponentVO.setAvailabilities(availList);
              altComponentVO.setAvailableQty(altAvailability);
              altComponentVO.setCompanyCodeSys01DOC24(altVO.getCompanyCodeSys01ITM04());
              altComponentVO.setDescriptionSYS10(altVO.getDescriptionSYS10());
              altComponentVO.setDocNumberDOC24(prodVO.getDocNumberDOC23());
              altComponentVO.setDocYearDOC24(prodVO.getDocYearDOC23());
              altComponentVO.setItemCodeItm01DOC24(altVO.getItemCodeItm01ITM04());
              altComponentVO.setMinSellingQtyUmCodeReg02ITM01(
                  altVO.getMinSellingQtyUmCodeReg02ITM01());
              altQty =
                  conv.convertQty(
                      altVO.getMinSellingQtyUmCodeReg02ITM01(),
                      componentVO.getMinSellingQtyUmCodeReg02ITM01(),
                      altAvailability,
                      userSessionPars,
                      request,
                      response,
                      userSession,
                      context);
              if (componentVO.getQtyDOC24().subtract(availability).doubleValue()
                  > altQty.doubleValue()) {
                delta = altQty;
                altComponentVO.setQtyDOC24(altAvailability);
              } else {
                delta = componentVO.getQtyDOC24();
                altComponentVO.setQtyDOC24(
                    conv.convertQty(
                        componentVO.getMinSellingQtyUmCodeReg02ITM01(),
                        altVO.getMinSellingQtyUmCodeReg02ITM01(),
                        delta,
                        userSessionPars,
                        request,
                        response,
                        userSession,
                        context));
              }
              componentVO.setQtyDOC24(componentVO.getQtyDOC24().subtract(delta));
              alternativeComps.add(altComponentVO);

              altCodes = (HashSet) compAltComps.get(itemCode);
              if (altCodes == null) {
                altCodes = new HashSet();
                compAltComps.put(itemCode, altCodes);
              }
              altCodes.add(altVO.getItemCodeItm01ITM04());

              if (componentVO.getQtyDOC24().doubleValue() == 0) {
                compsToRemove.add(componentVO);
                break;
              }
              if (componentVO.getQtyDOC24().subtract(availability).doubleValue() == 0) break;
            }
          }
        }
      }

      list = new ArrayList(comps.values());
      list.addAll(alternativeComps);
      list.removeAll(compsToRemove);
      return new VOListResponse(list, false, list.size());
    } catch (Throwable ex) {
      Logger.error(
          userSessionPars.getUsername(),
          this.getClass().getName(),
          "checkComponentsAvailability",
          "Error while retrieving components availability for the specified production order",
          ex);
      return new ErrorResponse(ex.getMessage());
    }
  }
  /** 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) {
      }
    }
  }
Esempio n. 12
0
  /** Business logic to execute. */
  public VOListResponse insertCharges(
      ArrayList list, String serverLanguageId, String username, ArrayList customizedFields)
      throws Throwable {
    Connection conn = null;
    try {
      if (this.conn == null) conn = getConn();
      else conn = this.conn;
      ChargeVO vo = null;

      Map attribute2dbField = new HashMap();
      attribute2dbField.put("companyCodeSys01SAL06", "COMPANY_CODE_SYS01");
      attribute2dbField.put("chargeCodeSAL06", "CHARGE_CODE");
      attribute2dbField.put("chargeCodeSAL06", "CHARGE_CODE");
      attribute2dbField.put("progressiveSys10SAL06", "PROGRESSIVE_SYS10");
      attribute2dbField.put("valueSAL06", "VALUE");
      attribute2dbField.put("percSAL06", "PERC");
      attribute2dbField.put("vatCodeReg01SAL06", "VAT_CODE_REG01");
      attribute2dbField.put("currencyCodeReg03SAL06", "CURRENCY_CODE_REG03");
      attribute2dbField.put("enabledSAL06", "ENABLED");

      BigDecimal progressiveSYS10 = null;
      Response res = null;
      for (int i = 0; i < list.size(); i++) {
        vo = (ChargeVO) list.get(i);
        vo.setEnabledSAL06("Y");

        // insert record in SYS10...
        progressiveSYS10 =
            TranslationUtils.insertTranslations(
                vo.getDescriptionSYS10(), vo.getCompanyCodeSys01SAL06(), conn);
        vo.setProgressiveSys10SAL06(progressiveSYS10);

        // insert into SAL06...
        res =
            CustomizeQueryUtil.insertTable(
                conn,
                new UserSessionParameters(username),
                vo,
                "SAL06_CHARGES",
                attribute2dbField,
                "Y",
                "N",
                null,
                true,
                customizedFields);
        if (res.isError()) {
          throw new Exception(res.getErrorMessage());
        }
      }

      return new VOListResponse(list, false, list.size());
    } catch (Throwable ex) {
      Logger.error(
          username,
          this.getClass().getName(),
          "executeCommand",
          "Error while inserting new charges",
          ex);
      try {
        if (this.conn == null && conn != null)
          // rollback only local connection
          conn.rollback();
      } catch (Exception ex3) {
      }

      throw new Exception(ex.getMessage());
    } finally {
      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) {
      }
    }
  }
Esempio n. 14
0
  /** Business logic to execute. */
  public VOListResponse loadCharges(
      GridParams gridParams,
      String serverLanguageId,
      String username,
      ArrayList companiesList,
      ArrayList customizedFields)
      throws Throwable {
    PreparedStatement pstmt = null;
    Connection conn = null;
    try {
      if (this.conn == null) conn = getConn();
      else conn = this.conn;

      // retrieve companies list...
      String companies =
          (String)
              gridParams
                  .getOtherGridParams()
                  .get(ApplicationConsts.COMPANY_CODE_SYS01); // used in lookup grid...
      if (companies == null) {
        companies = "";
        for (int i = 0; i < companiesList.size(); i++)
          companies += "'" + companiesList.get(i).toString() + "',";
        companies = companies.substring(0, companies.length() - 1);
      } else companies = "'" + companies + "'";

      String sql =
          "select SAL06_CHARGES.COMPANY_CODE_SYS01,SAL06_CHARGES.CHARGE_CODE,SAL06_CHARGES.PROGRESSIVE_SYS10,"
              + "SYS10_TRANSLATIONS.DESCRIPTION,SAL06_CHARGES.VALUE,SAL06_CHARGES.PERC,SAL06_CHARGES.VAT_CODE_REG01,"
              + "SAL06_CHARGES.CURRENCY_CODE_REG03,SAL06_CHARGES.ENABLED"
              + " from SAL06_CHARGES,SYS10_TRANSLATIONS where "
              + "SAL06_CHARGES.PROGRESSIVE_SYS10=SYS10_TRANSLATIONS.PROGRESSIVE and "
              + "SYS10_TRANSLATIONS.LANGUAGE_CODE=? and "
              + "SAL06_CHARGES.ENABLED='Y' and "
              + "SAL06_CHARGES.COMPANY_CODE_SYS01 in ("
              + companies
              + ")";

      Map attribute2dbField = new HashMap();
      attribute2dbField.put("companyCodeSys01SAL06", "SAL06_CHARGES.COMPANY_CODE_SYS01");
      attribute2dbField.put("chargeCodeSAL06", "SAL06_CHARGES.CHARGE_CODE");
      attribute2dbField.put("descriptionSYS10", "SYS10_TRANSLATIONS.DESCRIPTION");
      attribute2dbField.put("progressiveSys10SAL06", "SAL06_CHARGES.PROGRESSIVE_SYS10");
      attribute2dbField.put("valueSAL06", "SAL06_CHARGES.VALUE");
      attribute2dbField.put("percSAL06", "SAL06_CHARGES.PERC");
      attribute2dbField.put("vatCodeReg01SAL06", "SAL06_CHARGES.VAT_CODE_REG01");
      attribute2dbField.put("currencyCodeReg03SAL06", "SAL06_CHARGES.CURRENCY_CODE_REG03");
      attribute2dbField.put("enabledSAL06", "SAL06_CHARGES.ENABLED");

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

      // read from SAL06 table...
      Response res =
          CustomizeQueryUtil.getQuery(
              conn,
              new UserSessionParameters(username),
              sql,
              values,
              attribute2dbField,
              ChargeVO.class,
              "Y",
              "N",
              null,
              gridParams,
              50,
              true,
              customizedFields);
      if (res.isError()) throw new Exception(res.getErrorMessage());

      java.util.List list = ((VOListResponse) res).getRows();
      ChargeVO vo = null;
      sql =
          "select SYS10_TRANSLATIONS.DESCRIPTION,REG01_VATS.VALUE,REG01_VATS.DEDUCTIBLE "
              + "from SYS10_TRANSLATIONS,REG01_VATS where "
              + "REG01_VATS.PROGRESSIVE_SYS10=SYS10_TRANSLATIONS.PROGRESSIVE and "
              + "SYS10_TRANSLATIONS.LANGUAGE_CODE=? and "
              + "REG01_VATS.VAT_CODE=?";
      pstmt = conn.prepareStatement(sql);
      ResultSet rset = null;
      for (int i = 0; i < list.size(); i++) {
        vo = (ChargeVO) list.get(i);
        if (vo.getVatCodeReg01SAL06() != null) {
          // retrieve vat data from REG01...
          pstmt.setString(1, serverLanguageId);
          pstmt.setString(2, vo.getVatCodeReg01SAL06());
          rset = pstmt.executeQuery();
          if (rset.next()) {
            vo.setVatDescriptionSYS10(rset.getString(1));
            vo.setVatValueREG01(rset.getBigDecimal(2));
            vo.setVatDeductibleREG01(rset.getBigDecimal(3));
          }
          rset.close();
        }
      }

      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 charges list",
          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;
    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));
      DetailSaleDocVO docVO = (DetailSaleDocVO) inputPar;

      // retrieve internationalization settings (Resources object)...
      ServerResourcesFactory factory =
          (ServerResourcesFactory) context.getAttribute(Controller.RESOURCES_FACTORY);
      Resources resources = factory.getResources(userSessionPars.getLanguageId());

      // insert header...
      docVO.setDocStateDOC01(ApplicationConsts.HEADER_BLOCKED);
      Response res =
          insDocBean.insertSaleDoc(
              conn, docVO, userSessionPars, request, response, userSession, context);
      if (res.isError()) {
        conn.rollback();
        return res;
      }

      SaleDocPK refPK =
          new SaleDocPK(
              docVO.getCompanyCodeSys01Doc01DOC01(),
              docVO.getDocTypeDoc01DOC01(),
              docVO.getDocYearDoc01DOC01(),
              docVO.getDocNumberDoc01DOC01());

      // retrieve ref. document item rows...
      GridParams gridParams = new GridParams();
      gridParams.getOtherGridParams().put(ApplicationConsts.SALE_DOC_PK, refPK);
      res =
          rowsAction.executeCommand(
              gridParams, userSessionPars, request, response, userSession, context);
      if (res.isError()) {
        conn.rollback();
        return res;
      }
      java.util.List rows = ((VOListResponse) res).getRows();

      // create rows..
      GridSaleDocRowVO gridRowVO = null;
      DetailSaleDocRowVO rowVO = null;
      java.util.List discRows = null;
      SaleDocRowPK docRowPK = null;
      SaleItemDiscountVO itemDiscVO = null;
      gridParams = new GridParams();
      for (int i = 0; i < rows.size(); i++) {
        gridRowVO = (GridSaleDocRowVO) rows.get(i);

        // retrieve row detail...
        docRowPK =
            new SaleDocRowPK(
                gridRowVO.getCompanyCodeSys01DOC02(),
                gridRowVO.getDocTypeDOC02(),
                gridRowVO.getDocYearDOC02(),
                gridRowVO.getDocNumberDOC02(),
                gridRowVO.getItemCodeItm01DOC02(),
                gridRowVO.getVariantTypeItm06DOC02(),
                gridRowVO.getVariantCodeItm11DOC02(),
                gridRowVO.getVariantTypeItm07DOC02(),
                gridRowVO.getVariantCodeItm12DOC02(),
                gridRowVO.getVariantTypeItm08DOC02(),
                gridRowVO.getVariantCodeItm13DOC02(),
                gridRowVO.getVariantTypeItm09DOC02(),
                gridRowVO.getVariantCodeItm14DOC02(),
                gridRowVO.getVariantTypeItm10DOC02(),
                gridRowVO.getVariantCodeItm15DOC02());

        res =
            rowAction.executeCommand(
                docRowPK, userSessionPars, request, response, userSession, context);
        if (res.isError()) {
          conn.rollback();
          return res;
        }
        rowVO = (DetailSaleDocRowVO) ((VOResponse) res).getVo();
        rowVO.setDocTypeDOC02(docVO.getDocTypeDOC01());
        rowVO.setDocNumberDOC02(docVO.getDocNumberDOC01());
        if (rowVO.getInvoiceQtyDOC02().doubleValue() < rowVO.getQtyDOC02().doubleValue()
            && rowVO.getQtyDOC02().doubleValue() == rowVO.getOutQtyDOC02().doubleValue()) {
          rowVO.setQtyDOC02(
              rowVO
                  .getQtyDOC02()
                  .subtract(
                      rowVO
                          .getInvoiceQtyDOC02()
                          .setScale(
                              rowVO.getDecimalsReg02DOC02().intValue(), BigDecimal.ROUND_HALF_UP)));
          rowVO.setTaxableIncomeDOC02(
              rowVO
                  .getQtyDOC02()
                  .multiply(rowVO.getValueSal02DOC02())
                  .setScale(docVO.getDecimalsREG03().intValue(), BigDecimal.ROUND_HALF_UP));
          rowVO.setTotalDiscountDOC02(new BigDecimal(0));

          // calculate row vat...
          double vatPerc =
              rowVO.getValueReg01DOC02().doubleValue()
                  * (1d - rowVO.getDeductibleReg01DOC02().doubleValue() / 100d)
                  / 100;
          rowVO.setVatValueDOC02(
              rowVO
                  .getTaxableIncomeDOC02()
                  .multiply(new BigDecimal(vatPerc))
                  .setScale(docVO.getDecimalsREG03().intValue(), BigDecimal.ROUND_HALF_UP));

          // calculate row total...
          rowVO.setValueDOC02(rowVO.getTaxableIncomeDOC02().add(rowVO.getVatValueDOC02()));

          res =
              insRowBean.insertSaleItem(
                  conn, rowVO, userSessionPars, request, response, userSession, context);
          if (res.isError()) {
            conn.rollback();
            return res;
          }

          // create item discounts...
          gridParams.getOtherGridParams().put(ApplicationConsts.SALE_DOC_ROW_PK, docRowPK);
          res =
              itemDiscAction.executeCommand(
                  gridParams, userSessionPars, request, response, userSession, context);
          if (res.isError()) {
            conn.rollback();
            return res;
          }
          discRows = ((VOListResponse) res).getRows();
          for (int j = 0; j < discRows.size(); j++) {
            itemDiscVO = (SaleItemDiscountVO) discRows.get(j);
            itemDiscVO.setDocTypeDOC04(docVO.getDocTypeDOC01());
            itemDiscVO.setDocNumberDOC04(docVO.getDocNumberDOC01());
            res =
                insItemDiscBean.insertSaleDocRowDiscount(
                    conn, itemDiscVO, userSessionPars, request, response, userSession, context);
            if (res.isError()) {
              conn.rollback();
              return res;
            }
          }
        }
      }

      // create charges...
      gridParams = new GridParams();
      gridParams.getOtherGridParams().put(ApplicationConsts.SALE_DOC_PK, refPK);
      res =
          chargesAction.executeCommand(
              gridParams, userSessionPars, request, response, userSession, context);
      SaleDocChargeVO chargeVO = null;
      if (res.isError()) {
        conn.rollback();
        return res;
      }
      rows = ((VOListResponse) res).getRows();
      for (int i = 0; i < rows.size(); i++) {
        chargeVO = (SaleDocChargeVO) rows.get(i);
        chargeVO.setDocTypeDOC03(docVO.getDocTypeDOC01());
        chargeVO.setDocNumberDOC03(docVO.getDocNumberDOC01());
        if (chargeVO.getValueDOC03() == null
            || chargeVO.getValueDOC03() != null
                && chargeVO.getInvoicedValueDOC03().doubleValue()
                    < chargeVO.getValueDOC03().doubleValue()) {
          if (chargeVO.getValueDOC03() != null)
            chargeVO.setValueDOC03(
                chargeVO
                    .getValueDOC03()
                    .subtract(chargeVO.getInvoicedValueDOC03())
                    .setScale(docVO.getDecimalsREG03().intValue(), BigDecimal.ROUND_HALF_UP));

          res =
              insChargeBean.insertSaleDocCharge(
                  conn, chargeVO, userSessionPars, request, response, userSession, context);
          if (res.isError()) {
            conn.rollback();
            return res;
          }
        }
      }

      // create activities...
      res =
          actAction.executeCommand(
              gridParams, userSessionPars, request, response, userSession, context);
      if (res.isError()) {
        conn.rollback();
        return res;
      }
      SaleDocActivityVO actVO = null;
      rows = ((VOListResponse) res).getRows();
      for (int i = 0; i < rows.size(); i++) {
        actVO = (SaleDocActivityVO) rows.get(i);
        actVO.setDocTypeDOC13(docVO.getDocTypeDOC01());
        actVO.setDocNumberDOC13(docVO.getDocNumberDOC01());
        if (actVO.getInvoicedValueDOC13().doubleValue() < actVO.getValueDOC13().doubleValue()) {
          actVO.setValueDOC13(
              actVO
                  .getValueDOC13()
                  .subtract(actVO.getInvoicedValueDOC13())
                  .setScale(docVO.getDecimalsREG03().intValue(), BigDecimal.ROUND_HALF_UP));
          res =
              insActBean.insertSaleActivity(
                  conn, actVO, userSessionPars, request, response, userSession, context);
          if (res.isError()) {
            conn.rollback();
            return res;
          }
        }
      }

      // create header discounts...
      res =
          discAction.executeCommand(
              gridParams, userSessionPars, request, response, userSession, context);
      if (res.isError()) {
        conn.rollback();
        return res;
      }
      SaleDocDiscountVO discVO = null;
      rows = ((VOListResponse) res).getRows();
      for (int i = 0; i < rows.size(); i++) {
        discVO = (SaleDocDiscountVO) rows.get(i);
        discVO.setDocTypeDOC05(docVO.getDocTypeDOC01());
        discVO.setDocNumberDOC05(docVO.getDocNumberDOC01());
        res =
            insDiscBean.insertSaleDocDiscount(
                conn, discVO, userSessionPars, request, response, userSession, context);
        if (res.isError()) {
          conn.rollback();
          return res;
        }
      }

      // recalculate all taxable incomes, vats, totals...
      SaleDocPK pk =
          new SaleDocPK(
              docVO.getCompanyCodeSys01DOC01(),
              docVO.getDocTypeDOC01(),
              docVO.getDocYearDOC01(),
              docVO.getDocNumberDOC01());
      res =
          totals.updateTaxableIncomes(
              conn, pk, userSessionPars, request, response, userSession, context);
      if (res.isError()) {
        conn.rollback();
        return res;
      }

      // reload doc header with updated totals...
      Response answer =
          docAction.loadSaleDoc(conn, pk, userSessionPars, request, response, userSession, context);

      // 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 creating a sale invoice from a sale 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) {
      }
    }
  }
  /** Business logic to execute. */
  public VOListResponse loadSupplierPriceItems(
      GridParams pars, String serverLanguageId, String username, ArrayList customizedFields)
      throws Throwable {
    PreparedStatement pstmt = null;
    Connection conn = null;
    try {
      if (this.conn == null) conn = getConn();
      else conn = this.conn;

      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);
      BigDecimal progressiveREG04 =
          (BigDecimal) pars.getOtherGridParams().get(ApplicationConsts.PROGRESSIVE_REG04);
      String companyCodeSYS01 =
          (String) pars.getOtherGridParams().get(ApplicationConsts.COMPANY_CODE_SYS01);
      String pricelistCodePUR03 =
          (String) pars.getOtherGridParams().get(ApplicationConsts.PRICELIST);

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

      String sql =
          "select PUR02_SUPPLIER_ITEMS.COMPANY_CODE_SYS01,PUR02_SUPPLIER_ITEMS.ITEM_CODE_ITM01,PUR02_SUPPLIER_ITEMS.SUPPLIER_ITEM_CODE,PUR02_SUPPLIER_ITEMS.PROGRESSIVE_REG04,"
              + "PUR02_SUPPLIER_ITEMS.PROGRESSIVE_HIE02,PUR02_SUPPLIER_ITEMS.PROGRESSIVE_HIE01,PUR02_SUPPLIER_ITEMS.MIN_PURCHASE_QTY,PUR02_SUPPLIER_ITEMS.MULTIPLE_QTY,"
              + "PUR02_SUPPLIER_ITEMS.UM_CODE_REG02,PUR02_SUPPLIER_ITEMS.ENABLED,SYS10_COMPANY_TRANSLATIONS.DESCRIPTION,REG02_MEASURE_UNITS.DECIMALS,"
              + "ITM01_ITEMS.VAT_CODE_REG01,SYS10_VAT.DESCRIPTION,REG01_VATS.DEDUCTIBLE,REG01_VATS.VALUE,"
              + "PUR04_SUPPLIER_PRICES.VALUE,PUR04_SUPPLIER_PRICES.START_DATE,PUR04_SUPPLIER_PRICES.END_DATE,"
              + "ITM01_ITEMS.USE_VARIANT_1,ITM01_ITEMS.USE_VARIANT_2,ITM01_ITEMS.USE_VARIANT_3,ITM01_ITEMS.USE_VARIANT_4,ITM01_ITEMS.USE_VARIANT_5, "
              + "ITM01_ITEMS.NO_WAREHOUSE_MOV "
              + " from PUR02_SUPPLIER_ITEMS,SYS10_COMPANY_TRANSLATIONS,ITM01_ITEMS,REG02_MEASURE_UNITS,SYS10_TRANSLATIONS SYS10_VAT,REG01_VATS,PUR04_SUPPLIER_PRICES where "
              + "PUR02_SUPPLIER_ITEMS.PROGRESSIVE_HIE02=? and "
              + "PUR02_SUPPLIER_ITEMS.UM_CODE_REG02=REG02_MEASURE_UNITS.UM_CODE and "
              + "PUR02_SUPPLIER_ITEMS.COMPANY_CODE_SYS01=ITM01_ITEMS.COMPANY_CODE_SYS01 and "
              + "PUR02_SUPPLIER_ITEMS.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 "
              + "PUR02_SUPPLIER_ITEMS.COMPANY_CODE_SYS01 = ? and "
              + "PUR02_SUPPLIER_ITEMS.PROGRESSIVE_REG04=? and "
              + "PUR02_SUPPLIER_ITEMS.ENABLED='Y' and "
              + "ITM01_ITEMS.VAT_CODE_REG01=REG01_VATS.VAT_CODE and "
              + "REG01_VATS.PROGRESSIVE_SYS10=SYS10_VAT.PROGRESSIVE and "
              + "SYS10_VAT.LANGUAGE_CODE=? and "
              + "PUR02_SUPPLIER_ITEMS.COMPANY_CODE_SYS01=PUR04_SUPPLIER_PRICES.COMPANY_CODE_SYS01 and "
              + "PUR02_SUPPLIER_ITEMS.PROGRESSIVE_REG04=PUR04_SUPPLIER_PRICES.PROGRESSIVE_REG04 and "
              + "PUR02_SUPPLIER_ITEMS.ITEM_CODE_ITM01=PUR04_SUPPLIER_PRICES.ITEM_CODE_ITM01 and "
              + "PUR04_SUPPLIER_PRICES.PRICELIST_CODE_PUR03=? and "
              + "PUR04_SUPPLIER_PRICES.START_DATE<=? and "
              + "PUR04_SUPPLIER_PRICES.END_DATE>? ";

      if (rootProgressiveHIE01 == null || !rootProgressiveHIE01.equals(progressiveHIE01)) {
        // retrieve all subnodes of the specified node...
        pstmt =
            conn.prepareStatement(
                "select HIE01_COMPANY_LEVELS.PROGRESSIVE,HIE01_COMPANY_LEVELS.PROGRESSIVE_HIE01,HIE01_COMPANY_LEVELS.LEV from HIE01_COMPANY_LEVELS "
                    + "where COMPANY_CODE_SYS01='"
                    + companyCodeSYS01
                    + "' and 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);
        if (rootProgressiveHIE01 != null || nodes.length() > 0)
          sql += " and PUR02_SUPPLIER_ITEMS.PROGRESSIVE_HIE01 in (" + nodes + ")";
      }

      Map attribute2dbField = new HashMap();
      attribute2dbField.put("companyCodeSys01PUR02", "PUR02_SUPPLIER_ITEMS.COMPANY_CODE_SYS01");
      attribute2dbField.put("itemCodeItm01PUR02", "PUR02_SUPPLIER_ITEMS.ITEM_CODE_ITM01");
      attribute2dbField.put("supplierItemCodePUR02", "PUR02_SUPPLIER_ITEMS.SUPPLIER_ITEM_CODE");
      attribute2dbField.put("progressiveReg04PUR02", "PUR02_SUPPLIER_ITEMS.PROGRESSIVE_REG04");
      attribute2dbField.put("progressiveHie02PUR02", "PUR02_SUPPLIER_ITEMS.PROGRESSIVE_HIE02");
      attribute2dbField.put("progressiveHie01PUR02", "PUR02_SUPPLIER_ITEMS.PROGRESSIVE_HIE01");
      attribute2dbField.put("minPurchaseQtyPUR02", "PUR02_SUPPLIER_ITEMS.MIN_PURCHASE_QTY");
      attribute2dbField.put("multipleQtyPUR02", "PUR02_SUPPLIER_ITEMS.MULTIPLE_QTY");
      attribute2dbField.put("umCodeReg02PUR02", "PUR02_SUPPLIER_ITEMS.UM_CODE_REG02");
      attribute2dbField.put("enabledPUR02", "PUR02_SUPPLIER_ITEMS.ENABLED");
      attribute2dbField.put("descriptionSYS10", "SYS10_COMPANY_TRANSLATIONS.DESCRIPTION");
      attribute2dbField.put("decimalsREG02", "REG02_MEASURE_UNITS.DECIMALS");

      attribute2dbField.put("vatCodeReg01ITM01", "ITM01_ITEMS.VAT_CODE_REG01");
      attribute2dbField.put("vatDescriptionSYS10", "SYS10_VAT.DESCRIPTION");
      attribute2dbField.put("deductibleREG01", "REG01_VATS.DEDUCTIBLE");
      attribute2dbField.put("valueREG01", "REG01_VATS.VALUE");
      attribute2dbField.put("valuePUR04", "PUR04_SUPPLIER_PRICES.VALUE");
      attribute2dbField.put("startDatePUR04", "PUR04_SUPPLIER_PRICES.START_DATE");
      attribute2dbField.put("endDatePUR04", "PUR04_SUPPLIER_PRICES.END_DATE");

      attribute2dbField.put("useVariant1ITM01", "ITM01_ITEMS.USE_VARIANT_1");
      attribute2dbField.put("useVariant2ITM01", "ITM01_ITEMS.USE_VARIANT_2");
      attribute2dbField.put("useVariant3ITM01", "ITM01_ITEMS.USE_VARIANT_3");
      attribute2dbField.put("useVariant4ITM01", "ITM01_ITEMS.USE_VARIANT_4");
      attribute2dbField.put("useVariant5ITM01", "ITM01_ITEMS.USE_VARIANT_5");

      attribute2dbField.put("noWarehouseMovITM01", "ITM01_ITEMS.NO_WAREHOUSE_MOV");

      ArrayList values = new ArrayList();
      values.add(progressiveHIE02);
      values.add(serverLanguageId);
      values.add(companyCodeSYS01);
      values.add(progressiveREG04);
      values.add(serverLanguageId);
      values.add(pricelistCodePUR03);
      values.add(new java.sql.Date(System.currentTimeMillis()));
      values.add(new java.sql.Date(System.currentTimeMillis()));

      // read from PUR02 table...
      Response answer =
          CustomizeQueryUtil.getQuery(
              conn,
              new UserSessionParameters(username),
              sql,
              values,
              attribute2dbField,
              SupplierPriceItemVO.class,
              "Y",
              "N",
              null,
              pars,
              50,
              true,
              customizedFields);
      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 supplier items list",
          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 updateExpirations(
      String t1,
      String t2,
      ArrayList oldVOs,
      ArrayList newVOs,
      String serverLanguageId,
      String username)
      throws Throwable {
    PreparedStatement pstmt = null;

    Connection conn = null;
    try {
      if (this.conn == null) conn = getConn();
      else conn = this.conn;
      insJornalItemAction.setConn(conn); // use same transaction...
      userParamAction.setConn(conn); // use same transaction...

      ExpirationVO oldVO = null;
      ExpirationVO newVO = null;
      Response res = null;

      HashSet pkAttrs = new HashSet();
      pkAttrs.add("companyCodeSys01DOC19");
      pkAttrs.add("progressiveDOC19");

      HashMap attribute2dbField = new HashMap();
      attribute2dbField.put("progressiveDOC19", "PROGRESSIVE");
      attribute2dbField.put("companyCodeSys01DOC19", "COMPANY_CODE_SYS01");
      attribute2dbField.put("docTypeDOC19", "DOC_TYPE");
      attribute2dbField.put("docNumberDOC19", "DOC_NUMBER");
      attribute2dbField.put("docYearDOC19", "DOC_YEAR");
      attribute2dbField.put("docSequenceDOC19", "DOC_SEQUENCE");
      attribute2dbField.put("name_1DOC19", "NAME_1");
      attribute2dbField.put("name_2DOC19", "NAME_2");
      attribute2dbField.put("descriptionDOC19", "DESCRIPTION");
      attribute2dbField.put("valueDOC19", "VALUE");
      attribute2dbField.put("payedDOC19", "PAYED");
      attribute2dbField.put("docDateDOC19", "DOC_DATE");
      attribute2dbField.put("expirationDateDOC19", "EXPIRATION_DATE");
      attribute2dbField.put("progressiveReg04DOC19", "PROGRESSIVE_REG04");
      attribute2dbField.put("customerSupplierCodeDOC19", "CUSTOMER_SUPPLIER_CODE");

      attribute2dbField.put("payedDateDOC19", "PAYED_DATE");
      attribute2dbField.put("payedValueDOC19", "PAYED_VALUE");
      attribute2dbField.put("realPaymentTypeCodeReg11DOC19", "REAL_PAYMENT_TYPE_CODE_REG11");
      attribute2dbField.put("paymentTypeCodeReg11DOC19", "PAYMENT_TYPE_CODE_REG11");

      JournalHeaderVO jhVO = null;
      HashMap map = new HashMap();
      String bankAccountCode = null;

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

        res =
            new QueryUtil()
                .updateTable(
                    conn,
                    new UserSessionParameters(username),
                    pkAttrs,
                    oldVO,
                    newVO,
                    "DOC19_EXPIRATIONS",
                    attribute2dbField,
                    "Y",
                    "N",
                    null,
                    true);
        if (res.isError()) {
          throw new Exception(res.getErrorMessage());
        }

        map.put(ApplicationConsts.COMPANY_CODE_SYS01, newVO.getCompanyCodeSys01DOC19());
        map.put(ApplicationConsts.PARAM_CODE, ApplicationConsts.BANK_ACCOUNT);
        res = userParamAction.loadUserParam(map, serverLanguageId, username);
        if (res.isError()) {
          throw new Exception(res.getErrorMessage());
        }
        bankAccountCode = ((VOResponse) res).getVo().toString();

        // generate an accounting item if the row has been payed...
        if (!oldVO.getPayedDOC19().booleanValue() && newVO.getPayedDOC19().booleanValue()) {

          jhVO = new JournalHeaderVO();
          jhVO.setCompanyCodeSys01ACC05(newVO.getCompanyCodeSys01DOC19());
          String creditDebitAccountCode = null;
          String accountCodeTypeACC06 = null;
          if (newVO.getDocTypeDOC19().equals(ApplicationConsts.SALE_DESK_DOC_TYPE)
              || newVO.getDocTypeDOC19().equals(ApplicationConsts.SALE_INVOICE_DOC_TYPE)
              || newVO.getDocTypeDOC19().equals(ApplicationConsts.SALE_INVOICE_FROM_DN_DOC_TYPE)
              || newVO.getDocTypeDOC19().equals(ApplicationConsts.SALE_INVOICE_FROM_SD_DOC_TYPE)
              || newVO.getDocTypeDOC19().equals(ApplicationConsts.SALE_CREDIT_NOTE_DOC_TYPE)
              || newVO.getDocTypeDOC19().equals(ApplicationConsts.SALE_GENERIC_INVOICE)) {
            jhVO.setAccountingMotiveCodeAcc03ACC05(ApplicationConsts.MOTIVE_INVOICE_PROCEEDS);
            jhVO.setDescriptionACC05(
                newVO.getDescriptionDOC19()
                    + " - "
                    + t1
                    + " "
                    + newVO.getName_1DOC19()
                    + " "
                    + (newVO.getName_2DOC19() == null ? "" : newVO.getName_2DOC19()));

            // determine account codes defined for the current customer...
            pstmt =
                conn.prepareStatement(
                    "select CREDIT_ACCOUNT_CODE_ACC02 from SAL07_CUSTOMERS where COMPANY_CODE_SYS01=? and PROGRESSIVE_REG04=?");
            pstmt.setString(1, newVO.getCompanyCodeSys01DOC19());
            pstmt.setBigDecimal(2, newVO.getProgressiveReg04DOC19());
            ResultSet rset = pstmt.executeQuery();
            if (!rset.next()) {
              rset.close();
              throw new Exception("customer not found");
            }
            creditDebitAccountCode = rset.getString(1);
            rset.close();
            pstmt.close();

            accountCodeTypeACC06 = ApplicationConsts.ACCOUNT_TYPE_CUSTOMER;
          } else {
            jhVO.setAccountingMotiveCodeAcc03ACC05(ApplicationConsts.MOTIVE_PURCHASE_INVOICE_PAYED);
            jhVO.setDescriptionACC05(
                newVO.getDescriptionDOC19()
                    + " - "
                    + t2
                    + " "
                    + newVO.getName_1DOC19()
                    + " "
                    + (newVO.getName_2DOC19() == null ? "" : newVO.getName_2DOC19()));

            // determine account codes defined for the current supplier...
            pstmt =
                conn.prepareStatement(
                    "select DEBIT_ACCOUNT_CODE_ACC02 from PUR01_SUPPLIERS where COMPANY_CODE_SYS01=? and PROGRESSIVE_REG04=?");
            pstmt.setString(1, newVO.getCompanyCodeSys01DOC19());
            pstmt.setBigDecimal(2, newVO.getProgressiveReg04DOC19());
            ResultSet rset = pstmt.executeQuery();
            if (!rset.next()) {
              rset.close();
              throw new Exception("supplier not found");
            }
            creditDebitAccountCode = rset.getString(1);
            rset.close();
            pstmt.close();

            accountCodeTypeACC06 = ApplicationConsts.ACCOUNT_TYPE_SUPPLIER;
          }
          jhVO.setItemDateACC05(newVO.getPayedDateDOC19());
          Calendar cal = Calendar.getInstance();
          cal.setTime(newVO.getPayedDateDOC19());
          jhVO.setItemYearACC05(new BigDecimal(cal.get(Calendar.YEAR)));

          JournalRowVO jrVO = new JournalRowVO();
          jrVO.setCompanyCodeSys01ACC06(jhVO.getCompanyCodeSys01ACC05());
          jrVO.setAccountCodeAcc02ACC06(creditDebitAccountCode);
          jrVO.setAccountCodeACC06(newVO.getCustomerSupplierCodeDOC19());
          jrVO.setAccountCodeTypeACC06(accountCodeTypeACC06);
          jrVO.setCreditAmountACC06(newVO.getPayedValueDOC19());
          jrVO.setDescriptionACC06("");
          jrVO.setItemYearAcc05ACC06(jhVO.getItemYearACC05());
          jrVO.setProgressiveAcc05ACC06(jhVO.getProgressiveACC05());
          jhVO.addJournalRow(jrVO);

          jrVO = new JournalRowVO();
          jrVO.setCompanyCodeSys01ACC06(jhVO.getCompanyCodeSys01ACC05());
          jrVO.setAccountCodeAcc02ACC06(bankAccountCode);
          jrVO.setAccountCodeACC06(bankAccountCode);
          jrVO.setAccountCodeTypeACC06(ApplicationConsts.ACCOUNT_TYPE_ACCOUNT);
          jrVO.setDebitAmountACC06(newVO.getPayedValueDOC19());
          jrVO.setDescriptionACC06("");
          jrVO.setItemYearAcc05ACC06(jhVO.getItemYearACC05());
          jrVO.setProgressiveAcc05ACC06(jhVO.getProgressiveACC05());
          jhVO.addJournalRow(jrVO);

          res = insJornalItemAction.insertJournalItem(jhVO, serverLanguageId, username);
          if (res.isError()) {
            throw new Exception(res.getErrorMessage());
          }
        }
      }

      return new VOListResponse(newVOs, false, newVOs.size());
    } catch (Throwable ex) {
      Logger.error(
          username,
          this.getClass().getName(),
          "executeCommand",
          "Error while updating existing expirations",
          ex);
      try {
        if (this.conn == null && conn != null)
          // rollback only local connection
          conn.rollback();
      } catch (Exception ex3) {
      }
      throw new Exception(ex.getMessage());
    } finally {
      try {
        if (pstmt != null) pstmt.close();
      } catch (Exception ex1) {
      }

      try {
        insJornalItemAction.setConn(null);
        userParamAction.setConn(null);
      } catch (Exception ex) {
      }
      try {
        if (this.conn == null && conn != null) {
          // close only local connection
          conn.commit();
          conn.close();
        }

      } catch (Exception exx) {
      }
    }
  }
  /**
   * 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 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 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 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) {
    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));

      Response responseVO =
          bean.insertItem(
              conn,
              (JournalHeaderVO) inputPar,
              userSessionPars,
              request,
              response,
              userSession,
              context);
      if (responseVO.isError()) {
        conn.rollback();
        return responseVO;
      }

      if (inputPar instanceof JournalHeaderWithVatVO) {
        JournalHeaderWithVatVO vo = (JournalHeaderWithVatVO) inputPar;

        // insert vat rows in the specified vat register...
        Response regRes =
            vatRegisterAction.insertVatRows(
                conn, vo.getVats(), userSessionPars, request, response, userSession, context);
        if (regRes.isError()) {
          conn.rollback();
          return regRes;
        }

        // retrieve payment instalments...
        Response payRes =
            payAction.executeCommand(
                new LookupValidationParams(vo.getPaymentCodeREG10(), new HashMap()),
                userSessionPars,
                request,
                response,
                userSession,
                context);
        if (payRes.isError()) {
          conn.rollback();
          return payRes;
        }
        PaymentVO payVO = (PaymentVO) ((VOListResponse) payRes).getRows().get(0);
        GridParams gridParams = new GridParams();
        gridParams
            .getOtherGridParams()
            .put(ApplicationConsts.PAYMENT_CODE_REG10, vo.getPaymentCodeREG10());
        payRes =
            paysAction.executeCommand(
                gridParams, userSessionPars, request, response, userSession, context);
        if (payRes.isError()) {
          conn.rollback();
          return payRes;
        }
        java.util.List rows = ((VOListResponse) payRes).getRows();

        // create expirations in DOC19 ONLY if:
        // - there are more than one instalment OR
        // - there is only one instalment and this instalment has more than 0 instalment days
        if (rows.size() > 1
            || (rows.size() == 1
                && ((PaymentInstalmentVO) rows.get(0)).getInstalmentDaysREG17().intValue() > 0)) {

          // retrieve internationalization settings (Resources object)...
          ServerResourcesFactory factory =
              (ServerResourcesFactory) context.getAttribute(Controller.RESOURCES_FACTORY);
          Resources resources = factory.getResources(userSessionPars.getLanguageId());

          PaymentInstalmentVO inVO = null;
          pstmt =
              conn.prepareStatement(
                  "insert into DOC19_EXPIRATIONS(COMPANY_CODE_SYS01,DOC_TYPE,DOC_YEAR,DOC_NUMBER,DOC_SEQUENCE,PROGRESSIVE,DOC_DATE,EXPIRATION_DATE,NAME_1,NAME_2,VALUE,PAYED,DESCRIPTION,CUSTOMER_SUPPLIER_CODE,PROGRESSIVE_REG04,CURRENCY_CODE_REG03) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
          long startTime = vo.getItemDateACC05().getTime(); // item date...
          if (payVO.getStartDayREG10().equals(ApplicationConsts.START_DAY_END_MONTH)) {
            Calendar cal = Calendar.getInstance();
            if (cal.get(cal.MONTH) == 10
                || cal.get(cal.MONTH) == 3
                || cal.get(cal.MONTH) == 5
                || cal.get(cal.MONTH) == 8) cal.set(cal.DAY_OF_MONTH, 30);
            else if (cal.get(cal.MONTH) == 1) {
              if (cal.get(cal.YEAR) % 4 == 0) cal.set(cal.DAY_OF_MONTH, 29);
              else cal.set(cal.DAY_OF_MONTH, 28);
            } else cal.set(cal.DAY_OF_MONTH, 31);
            startTime = cal.getTime().getTime();
          }
          BigDecimal amount = null;

          for (int i = 0; i < rows.size(); i++) {
            inVO = (PaymentInstalmentVO) rows.get(i);
            pstmt.setString(1, vo.getCompanyCodeSys01ACC05());
            pstmt.setString(2, vo.getDocTypeDOC19());
            pstmt.setBigDecimal(3, vo.getItemYearACC05());
            pstmt.setBigDecimal(4, null);
            pstmt.setBigDecimal(5, vo.getDocSequenceDOC19());
            pstmt.setBigDecimal(
                6,
                ProgressiveUtils.getConsecutiveProgressive(
                    "DOC19_EXPIRATIONS", "PROGRESSIVE", conn));
            pstmt.setDate(7, vo.getItemDateACC05());
            pstmt.setDate(
                8,
                new java.sql.Date(
                    startTime
                        + inVO.getInstalmentDaysREG17().longValue()
                            * 86400
                            * 1000)); // expiration date
            pstmt.setString(9, vo.getName_1REG04());
            pstmt.setString(10, vo.getName_2REG04());
            amount =
                vo.getTotalValue()
                    .multiply(inVO.getPercentageREG17())
                    .divide(new BigDecimal(100), BigDecimal.ROUND_HALF_UP)
                    .setScale(vo.getTotalValue().scale(), BigDecimal.ROUND_HALF_UP); // value

            pstmt.setBigDecimal(11, amount);
            pstmt.setString(12, "N");

            if (vo.getDocTypeDOC19().equals(ApplicationConsts.SALE_GENERIC_INVOICE))
              pstmt.setString(
                  13,
                  resources.getResource("sale generic document")
                      + " "
                      + vo.getDocSequenceDOC19()
                      + "/"
                      + vo.getItemYearACC05()
                      + " - "
                      + resources.getResource("valueREG01")
                      + " "
                      + resources.getResource("rateNumberREG17")
                      + " "
                      + (i + 1)
                      + " - "
                      + inVO.getPaymentTypeDescriptionSYS10()); // description
            else
              pstmt.setString(
                  13,
                  resources.getResource("purchase generic document")
                      + " "
                      + vo.getDocSequenceDOC19()
                      + "/"
                      + vo.getItemYearACC05()
                      + " - "
                      + resources.getResource("valueREG01")
                      + " "
                      + resources.getResource("rateNumberREG17")
                      + " "
                      + (i + 1)
                      + " - "
                      + inVO.getPaymentTypeDescriptionSYS10()); // description
            pstmt.setString(14, vo.getCustomerCodeSAL07());
            pstmt.setBigDecimal(15, vo.getProgressiveREG04());
            pstmt.setString(16, vo.getCurrencyCodeREG01());
            pstmt.execute();
          }
          pstmt.close();
        }

        // create an item registration for proceeds, according to expiration settings (e.g. retail
        // selling):
        // there must be only one instalment and this instalment has 0 instalment days
        if (rows.size() == 1
            && ((PaymentInstalmentVO) rows.get(0)).getInstalmentDaysREG17().intValue() == 0) {

          // retrieve internationalization settings (Resources object)...
          ServerResourcesFactory factory =
              (ServerResourcesFactory) context.getAttribute(Controller.RESOURCES_FACTORY);
          Resources resources = factory.getResources(userSessionPars.getLanguageId());

          HashMap map = new HashMap();
          map.put(ApplicationConsts.COMPANY_CODE_SYS01, vo.getCompanyCodeSys01ACC05());
          map.put(ApplicationConsts.PARAM_CODE, ApplicationConsts.CASE_ACCOUNT);
          Response res =
              userParamAction.executeCommand(
                  map, userSessionPars, request, response, userSession, context);
          if (res.isError()) {
            conn.rollback();
            return res;
          }
          String caseAccountCode = ((VOResponse) res).getVo().toString();

          JournalHeaderVO jhVO = new JournalHeaderVO();
          jhVO.setCompanyCodeSys01ACC05(vo.getCompanyCodeSys01ACC05());
          if (vo.getDocTypeDOC19().equals(ApplicationConsts.SALE_GENERIC_INVOICE)) {
            jhVO.setDescriptionACC05(
                resources.getResource("sale generic document")
                    + " "
                    + vo.getDocSequenceDOC19()
                    + "/"
                    + vo.getItemYearACC05()
                    + " - "
                    + resources.getResource("customer")
                    + " "
                    + vo.getName_1REG04()
                    + " "
                    + (vo.getName_2REG04() == null ? "" : vo.getName_2REG04()));
            jhVO.setAccountingMotiveCodeAcc03ACC05(ApplicationConsts.MOTIVE_INVOICE_PROCEEDS);
          } else {
            jhVO.setDescriptionACC05(
                resources.getResource("purchase generic document")
                    + " "
                    + vo.getDocSequenceDOC19()
                    + "/"
                    + vo.getItemYearACC05()
                    + " - "
                    + resources.getResource("supplier")
                    + " "
                    + vo.getName_1REG04()
                    + " "
                    + (vo.getName_2REG04() == null ? "" : vo.getName_2REG04()));
            jhVO.setAccountingMotiveCodeAcc03ACC05(ApplicationConsts.MOTIVE_PURCHASE_INVOICE_PAYED);
          }

          jhVO.setItemDateACC05(new java.sql.Date(System.currentTimeMillis()));
          jhVO.setItemYearACC05(new BigDecimal(Calendar.getInstance().get(Calendar.YEAR)));

          JournalRowVO jrVO = new JournalRowVO();
          jrVO.setCompanyCodeSys01ACC06(jhVO.getCompanyCodeSys01ACC05());
          if (vo.getDocTypeDOC19().equals(ApplicationConsts.SALE_GENERIC_INVOICE)) {
            jrVO.setAccountCodeAcc02ACC06(vo.getCreditAccountCodeAcc02SAL07());
            jrVO.setAccountCodeACC06(vo.getCustomerCodeSAL07());
            jrVO.setAccountCodeTypeACC06(ApplicationConsts.ACCOUNT_TYPE_CUSTOMER);
            jrVO.setCreditAmountACC06(vo.getTotalValue());
          } else {
            jrVO.setAccountCodeAcc02ACC06(vo.getDebitAccountCodeAcc02PUR01());
            jrVO.setAccountCodeACC06(vo.getSupplierCodePUR01());
            jrVO.setAccountCodeTypeACC06(ApplicationConsts.ACCOUNT_TYPE_SUPPLIER);
            jrVO.setDebitAmountACC06(vo.getTotalValue());
          }
          jrVO.setDescriptionACC06("");
          jrVO.setItemYearAcc05ACC06(jhVO.getItemYearACC05());
          jrVO.setProgressiveAcc05ACC06(jhVO.getProgressiveACC05());
          jhVO.addJournalRow(jrVO);

          jrVO = new JournalRowVO();
          jrVO.setCompanyCodeSys01ACC06(jhVO.getCompanyCodeSys01ACC05());
          jrVO.setAccountCodeAcc02ACC06(caseAccountCode);
          jrVO.setAccountCodeACC06(caseAccountCode);
          jrVO.setAccountCodeTypeACC06(ApplicationConsts.ACCOUNT_TYPE_ACCOUNT);
          if (vo.getDocTypeDOC19().equals(ApplicationConsts.SALE_GENERIC_INVOICE)) {
            jrVO.setDebitAmountACC06(vo.getTotalValue());
          } else {
            jrVO.setCreditAmountACC06(vo.getTotalValue());
          }
          jrVO.setDescriptionACC06("");
          jrVO.setItemYearAcc05ACC06(jhVO.getItemYearACC05());
          jrVO.setProgressiveAcc05ACC06(jhVO.getProgressiveACC05());
          jhVO.addJournalRow(jrVO);
          Response proceedsRes =
              bean.insertItem(conn, jhVO, userSessionPars, request, response, userSession, context);
          if (proceedsRes.isError()) {
            conn.rollback();
            return proceedsRes;
          }
        }
      }

      Response answer = responseVO;

      // 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 a new item in the journal",
          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) {
      }
    }
  }
  /**
   * Insert a new accounting item in ACC05/ACC06 tables.
   *
   * @return JournalHeaderVO object with the progressive attribute filled
   */
  public VOResponse insertJournalItem(JournalHeaderVO vo, String serverLanguageId, String username)
      throws Throwable {
    PreparedStatement pstmt = null;
    Connection conn = null;
    try {
      if (this.conn == null) conn = getConn();
      else conn = this.conn;

      // generate progressive for journal item number...
      vo.setProgressiveACC05(
          CompanyProgressiveUtils.getConsecutiveProgressive(
              vo.getCompanyCodeSys01ACC05(),
              "ACC05_JOURNAL_HEADER_YEAR=" + vo.getItemYearACC05(),
              "PROGRESSIVE",
              conn));

      // insert into ACC05...
      Map attribute2dbField = new HashMap();
      attribute2dbField.put("companyCodeSys01ACC05", "COMPANY_CODE_SYS01");
      attribute2dbField.put("accountingMotiveCodeAcc03ACC05", "ACCOUNTING_MOTIVE_CODE_ACC03");
      attribute2dbField.put("progressiveACC05", "PROGRESSIVE");
      attribute2dbField.put("itemYearACC05", "ITEM_YEAR");
      attribute2dbField.put("itemDateACC05", "ITEM_DATE");
      attribute2dbField.put("descriptionACC05", "DESCRIPTION");
      Response res =
          org.jallinone.commons.server.QueryUtilExtension.insertTable(
              conn,
              new UserSessionParameters(username),
              vo,
              "ACC05_JOURNAL_HEADER",
              attribute2dbField,
              "Y",
              "N",
              null,
              true);
      if (res.isError()) throw new Exception(res.getErrorMessage());

      // insert into ACC06...
      attribute2dbField.clear();

      attribute2dbField.put("companyCodeSys01ACC06", "COMPANY_CODE_SYS01");
      attribute2dbField.put("progressiveAcc05ACC06", "PROGRESSIVE_ACC05");
      attribute2dbField.put("itemYearAcc05ACC06", "ITEM_YEAR_ACC05");
      attribute2dbField.put("progressiveACC06", "PROGRESSIVE");
      attribute2dbField.put("debitAmountACC06", "DEBIT_AMOUNT");
      attribute2dbField.put("creditAmountACC06", "CREDIT_AMOUNT");
      attribute2dbField.put("accountCodeTypeACC06", "ACCOUNT_CODE_TYPE");
      attribute2dbField.put("accountCodeACC06", "ACCOUNT_CODE");
      attribute2dbField.put("accountCodeAcc02ACC06", "ACCOUNT_CODE_ACC02");
      attribute2dbField.put("descriptionACC06", "DESCRIPTION");
      JournalRowVO rowVO = null;
      for (int i = 0; i < vo.getJournalRows().size(); i++) {
        rowVO = (JournalRowVO) vo.getJournalRows().get(i);
        rowVO.setProgressiveAcc05ACC06(vo.getProgressiveACC05());
        rowVO.setProgressiveACC06(
            CompanyProgressiveUtils.getConsecutiveProgressive(
                vo.getCompanyCodeSys01ACC05(), "ACC06_JOURNAL_ROWS", "PROGRESSIVE", conn));
        res =
            org.jallinone.commons.server.QueryUtilExtension.insertTable(
                conn,
                new UserSessionParameters(username),
                rowVO,
                "ACC06_JOURNAL_ROWS",
                attribute2dbField,
                "Y",
                "N",
                null,
                true);
        if (res.isError()) throw new Exception(res.getErrorMessage());
      }

      return new VOResponse(vo);
    } catch (Throwable ex) {
      Logger.error(
          username,
          this.getClass().getName(),
          "insertItem",
          "Error while inserting a new item in the journal",
          ex);
      try {
        if (this.conn == null && conn != null)
          // rollback only local connection
          conn.rollback();
      } catch (Exception ex3) {
      }
      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();
    Connection conn = null;
    Statement stmt = 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));
      java.util.List list = (ArrayList) inputPar;

      HierarItemDiscountVO vo = null;
      ResultSet rset = null;
      stmt = conn.createStatement();
      for (int i = 0; i < list.size(); i++) {
        vo = (HierarItemDiscountVO) list.get(i);
        vo.setDiscountTypeSAL03(ApplicationConsts.DISCOUNT_CUSTOMER);

        // retrieve COMPANY_CODE from progressiveHIE01...
        rset =
            stmt.executeQuery(
                "select COMPANY_CODE_SYS01 from ITM02_ITEM_TYPES where PROGRESSIVE_HIE02 in "
                    + "(select PROGRESSIVE_HIE02 from HIE01_LEVELS where PROGRESSIVE="
                    + vo.getProgressiveHie01SAL05()
                    + ")");
        if (rset.next()) vo.setCompanyCodeSys01SAL03(rset.getString(1));
        else {
          rset.close();
          conn.rollback();
          return new ErrorResponse("Item hierarchy not found.");
        }
        rset.close();

        DiscountBean.insertDiscount(conn, vo);

        stmt.execute(
            "insert into SAL05_ITEM_HIERAR_DISCOUNTS(COMPANY_CODE_SYS01,PROGRESSIVE_HIE01,DISCOUNT_CODE_SAL03) "
                + "values('"
                + vo.getCompanyCodeSys01SAL03()
                + "',"
                + vo.getProgressiveHie01SAL05()
                + ",'"
                + vo.getDiscountCodeSAL03()
                + "')");
      }

      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 hierarchy item discounts",
          ex);
      try {
        conn.rollback();
      } catch (Exception ex3) {
      }
      return new ErrorResponse(ex.getMessage());
    } finally {
      try {
        stmt.close();
      } catch (Exception ex2) {
      }
      try {
        ConnectionManager.releaseConnection(conn, context);
      } catch (Exception ex1) {
      }
    }
  }
Esempio n. 25
0
  /** Business logic to execute. */
  public VOListResponse updateCharges(
      ArrayList oldVOs,
      ArrayList newVOs,
      String serverLanguageId,
      String username,
      ArrayList customizedFields)
      throws Throwable {
    Connection conn = null;
    try {
      if (this.conn == null) conn = getConn();
      else conn = this.conn;

      ChargeVO oldVO = null;
      ChargeVO newVO = null;
      Response res = null;

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

        // update SYS10 table...
        TranslationUtils.updateTranslation(
            oldVO.getDescriptionSYS10(),
            newVO.getDescriptionSYS10(),
            newVO.getProgressiveSys10SAL06(),
            serverLanguageId,
            conn);

        HashSet pkAttrs = new HashSet();
        pkAttrs.add("companyCodeSys01SAL06");
        pkAttrs.add("chargeCodeSAL06");

        HashMap attr2dbFields = new HashMap();
        attr2dbFields.put("companyCodeSys01SAL06", "COMPANY_CODE_SYS01");
        attr2dbFields.put("chargeCodeSAL06", "CHARGE_CODE");
        attr2dbFields.put("progressiveSys10SAL06", "PROGRESSIVE_SYS10");
        attr2dbFields.put("valueSAL06", "VALUE");
        attr2dbFields.put("percSAL06", "PERC");
        attr2dbFields.put("vatCodeReg01SAL06", "VAT_CODE_REG01");
        attr2dbFields.put("currencyCodeReg03SAL06", "CURRENCY_CODE_REG03");

        res =
            new CustomizeQueryUtil()
                .updateTable(
                    conn,
                    new UserSessionParameters(username),
                    pkAttrs,
                    oldVO,
                    newVO,
                    "SAL06_CHARGES",
                    attr2dbFields,
                    "Y",
                    "N",
                    null,
                    true,
                    customizedFields);
        if (res.isError()) {
          throw new Exception(res.getErrorMessage());
        }
      }

      return new VOListResponse(newVOs, false, newVOs.size());
    } catch (Throwable ex) {
      Logger.error(
          username,
          this.getClass().getName(),
          "executeCommand",
          "Error while updating existing charges",
          ex);
      try {
        if (this.conn == null && conn != null)
          // rollback only local connection
          conn.rollback();
      } catch (Exception ex3) {
      }

      throw new Exception(ex.getMessage());
    } finally {
      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) {
    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) {
      }
    }
  }
  public VOResponse payImmediately(
      String companyCode,
      String docType,
      BigDecimal docYear,
      BigDecimal docNumber,
      BigDecimal docSequence,
      String t1,
      String t2,
      String serverLanguageId,
      String username)
      throws Throwable {
    PreparedStatement pstmt = null;
    ResultSet rset = null;
    Connection conn = null;
    try {
      if (this.conn == null) conn = getConn();
      else conn = this.conn;
      insJornalItemAction.setConn(conn); // use same transaction...
      userParamAction.setConn(conn); // use same transaction...

      pstmt =
          conn.prepareStatement(
              "UPDATE DOC19_EXPIRATIONS SET PAYED_VALUE=VALUE,PAYED_DATE=EXPIRATION_DATE,PAYED='Y',REAL_PAYMENT_TYPE_CODE_REG11=PAYMENT_TYPE_CODE_REG11 WHERE "
                  + "COMPANY_CODE_SYS01=? AND DOC_TYPE=? AND DOC_YEAR=? AND DOC_NUMBER=? ");
      pstmt.setString(1, companyCode);
      pstmt.setString(2, docType);
      pstmt.setBigDecimal(3, docYear);
      pstmt.setBigDecimal(4, docNumber);
      int rows = pstmt.executeUpdate();
      if (rows != 1) throw new Exception("invalid number of payments");
      pstmt.close();

      pstmt =
          conn.prepareStatement(
              "SELECT DESCRIPTION,NAME_1,NAME_2,PROGRESSIVE_REG04,VALUE,CUSTOMER_SUPPLIER_CODE "
                  + "FROM DOC19_EXPIRATIONS WHERE "
                  + "COMPANY_CODE_SYS01=? AND DOC_TYPE=? AND DOC_YEAR=? AND DOC_NUMBER=?");
      pstmt.setString(1, companyCode);
      pstmt.setString(2, docType);
      pstmt.setBigDecimal(3, docYear);
      pstmt.setBigDecimal(4, docNumber);
      rset = pstmt.executeQuery();
      rset.next();
      String descriptionDOC19 = rset.getString(1);
      String name_1DOC19 = rset.getString(2);
      String name_2DOC19 = rset.getString(3);
      BigDecimal progressiveReg04DOC19 = rset.getBigDecimal(4);
      BigDecimal valueDOC19 = rset.getBigDecimal(5);
      String customerSupplierCodeDOC19 = rset.getString(6);
      rset.close();
      pstmt.close();

      HashMap map = new HashMap();
      map.put(ApplicationConsts.COMPANY_CODE_SYS01, companyCode);
      map.put(ApplicationConsts.PARAM_CODE, ApplicationConsts.BANK_ACCOUNT);
      Response res = userParamAction.loadUserParam(map, serverLanguageId, username);
      if (res.isError()) {
        throw new Exception(res.getErrorMessage());
      }
      String bankAccountCode = ((VOResponse) res).getVo().toString();

      // generate an accounting item, since the row has been payed...
      JournalHeaderVO jhVO = new JournalHeaderVO();
      jhVO.setCompanyCodeSys01ACC05(companyCode);
      String creditDebitAccountCode = null;
      String accountCodeTypeACC06 = null;
      if (docType.equals(ApplicationConsts.SALE_DESK_DOC_TYPE)
          || docType.equals(ApplicationConsts.SALE_INVOICE_DOC_TYPE)
          || docType.equals(ApplicationConsts.SALE_INVOICE_FROM_DN_DOC_TYPE)
          || docType.equals(ApplicationConsts.SALE_INVOICE_FROM_SD_DOC_TYPE)
          || docType.equals(ApplicationConsts.SALE_CREDIT_NOTE_DOC_TYPE)
          || docType.equals(ApplicationConsts.SALE_GENERIC_INVOICE)) {
        jhVO.setAccountingMotiveCodeAcc03ACC05(ApplicationConsts.MOTIVE_INVOICE_PROCEEDS);
        jhVO.setDescriptionACC05(
            descriptionDOC19
                + " - "
                + t1
                + " "
                + name_1DOC19
                + " "
                + (name_2DOC19 == null ? "" : name_2DOC19));

        // determine account codes defined for the current customer...
        pstmt =
            conn.prepareStatement(
                "select CREDIT_ACCOUNT_CODE_ACC02 from SAL07_CUSTOMERS where COMPANY_CODE_SYS01=? and PROGRESSIVE_REG04=?");
        pstmt.setString(1, companyCode);
        pstmt.setBigDecimal(2, progressiveReg04DOC19);
        rset = pstmt.executeQuery();
        if (!rset.next()) {
          rset.close();
          throw new Exception("customer not found");
        }
        creditDebitAccountCode = rset.getString(1);
        rset.close();
        pstmt.close();

        accountCodeTypeACC06 = ApplicationConsts.ACCOUNT_TYPE_CUSTOMER;
      } else {
        jhVO.setAccountingMotiveCodeAcc03ACC05(ApplicationConsts.MOTIVE_PURCHASE_INVOICE_PAYED);
        jhVO.setDescriptionACC05(
            descriptionDOC19
                + " - "
                + t2
                + " "
                + name_1DOC19
                + " "
                + (name_2DOC19 == null ? "" : name_2DOC19));

        // determine account codes defined for the current supplier...
        pstmt =
            conn.prepareStatement(
                "select DEBIT_ACCOUNT_CODE_ACC02 from PUR01_SUPPLIERS where COMPANY_CODE_SYS01=? and PROGRESSIVE_REG04=?");
        pstmt.setString(1, companyCode);
        pstmt.setBigDecimal(2, progressiveReg04DOC19);
        rset = pstmt.executeQuery();
        if (!rset.next()) {
          rset.close();
          throw new Exception("supplier not found");
        }
        creditDebitAccountCode = rset.getString(1);
        rset.close();
        pstmt.close();

        accountCodeTypeACC06 = ApplicationConsts.ACCOUNT_TYPE_SUPPLIER;
      }
      jhVO.setItemDateACC05(new java.sql.Date(System.currentTimeMillis()));
      jhVO.setItemYearACC05(new BigDecimal(Calendar.getInstance().get(Calendar.YEAR)));

      JournalRowVO jrVO = new JournalRowVO();
      jrVO.setCompanyCodeSys01ACC06(jhVO.getCompanyCodeSys01ACC05());
      jrVO.setAccountCodeAcc02ACC06(creditDebitAccountCode);
      jrVO.setAccountCodeACC06(customerSupplierCodeDOC19);
      jrVO.setAccountCodeTypeACC06(accountCodeTypeACC06);
      jrVO.setCreditAmountACC06(valueDOC19);
      jrVO.setDescriptionACC06("");
      jrVO.setItemYearAcc05ACC06(jhVO.getItemYearACC05());
      jrVO.setProgressiveAcc05ACC06(jhVO.getProgressiveACC05());
      jhVO.addJournalRow(jrVO);

      jrVO = new JournalRowVO();
      jrVO.setCompanyCodeSys01ACC06(jhVO.getCompanyCodeSys01ACC05());
      jrVO.setAccountCodeAcc02ACC06(bankAccountCode);
      jrVO.setAccountCodeACC06(bankAccountCode);
      jrVO.setAccountCodeTypeACC06(ApplicationConsts.ACCOUNT_TYPE_ACCOUNT);
      jrVO.setDebitAmountACC06(valueDOC19);
      jrVO.setDescriptionACC06("");
      jrVO.setItemYearAcc05ACC06(jhVO.getItemYearACC05());
      jrVO.setProgressiveAcc05ACC06(jhVO.getProgressiveACC05());
      jhVO.addJournalRow(jrVO);

      res = insJornalItemAction.insertJournalItem(jhVO, serverLanguageId, username);
      if (res.isError()) {
        throw new Exception(res.getErrorMessage());
      }

      return new VOResponse(Boolean.TRUE);
    } catch (Throwable ex) {
      Logger.error(
          username,
          this.getClass().getName(),
          "executeCommand",
          "Error while updating existing expiration",
          ex);
      try {
        if (this.conn == null && conn != null)
          // rollback only local connection
          conn.rollback();
      } catch (Exception ex3) {
      }
      throw new Exception(ex.getMessage());
    } finally {
      try {
        if (rset != null) rset.close();
      } catch (Exception ex1) {
      }
      try {
        if (pstmt != null) pstmt.close();
      } catch (Exception ex1) {
      }

      try {
        insJornalItemAction.setConn(null);
        userParamAction.setConn(null);
      } catch (Exception ex) {
      }
      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;
    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));

      // retrieve companies list...
      GridParams gridParams = (GridParams) inputPar;
      String companies =
          (String)
              gridParams
                  .getOtherGridParams()
                  .get(ApplicationConsts.COMPANY_CODE_SYS01); // used in lookup grid...
      if (companies == null) {
        ArrayList companiesList =
            ((JAIOUserSessionParameters) userSessionPars).getCompanyBa().getCompaniesList("SAL06");
        companies = "";
        for (int i = 0; i < companiesList.size(); i++)
          companies += "'" + companiesList.get(i).toString() + "',";
        companies = companies.substring(0, companies.length() - 1);
      } else companies = "'" + companies + "'";

      String sql =
          "select SAL06_CHARGES.COMPANY_CODE_SYS01,SAL06_CHARGES.CHARGE_CODE,SAL06_CHARGES.PROGRESSIVE_SYS10,"
              + "SYS10_TRANSLATIONS.DESCRIPTION,SAL06_CHARGES.VALUE,SAL06_CHARGES.PERC,SAL06_CHARGES.VAT_CODE_REG01,"
              + "SAL06_CHARGES.CURRENCY_CODE_REG03,SAL06_CHARGES.ENABLED"
              + " from SAL06_CHARGES,SYS10_TRANSLATIONS where "
              + "SAL06_CHARGES.PROGRESSIVE_SYS10=SYS10_TRANSLATIONS.PROGRESSIVE and "
              + "SYS10_TRANSLATIONS.LANGUAGE_CODE=? and "
              + "SAL06_CHARGES.ENABLED='Y' and "
              + "SAL06_CHARGES.COMPANY_CODE_SYS01 in ("
              + companies
              + ")";

      Map attribute2dbField = new HashMap();
      attribute2dbField.put("companyCodeSys01SAL06", "SAL06_CHARGES.COMPANY_CODE_SYS01");
      attribute2dbField.put("chargeCodeSAL06", "SAL06_CHARGES.CHARGE_CODE");
      attribute2dbField.put("descriptionSYS10", "SYS10_TRANSLATIONS.DESCRIPTION");
      attribute2dbField.put("progressiveSys10SAL06", "SAL06_CHARGES.PROGRESSIVE_SYS10");
      attribute2dbField.put("valueSAL06", "SAL06_CHARGES.VALUE");
      attribute2dbField.put("percSAL06", "SAL06_CHARGES.PERC");
      attribute2dbField.put("vatCodeReg01SAL06", "SAL06_CHARGES.VAT_CODE_REG01");
      attribute2dbField.put("currencyCodeReg03SAL06", "SAL06_CHARGES.CURRENCY_CODE_REG03");
      attribute2dbField.put("enabledSAL06", "SAL06_CHARGES.ENABLED");

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

      // read from SAL06 table...
      Response res =
          CustomizeQueryUtil.getQuery(
              conn,
              userSessionPars,
              sql,
              values,
              attribute2dbField,
              ChargeVO.class,
              "Y",
              "N",
              context,
              gridParams,
              50,
              true,
              new BigDecimal(292) // window identifier...
              );
      if (res.isError()) return res;

      ArrayList list = ((VOListResponse) res).getRows();
      ChargeVO vo = null;
      sql =
          "select SYS10_TRANSLATIONS.DESCRIPTION,REG01_VATS.VALUE,REG01_VATS.DEDUCTIBLE "
              + "from SYS10_TRANSLATIONS,REG01_VATS where "
              + "REG01_VATS.PROGRESSIVE_SYS10=SYS10_TRANSLATIONS.PROGRESSIVE and "
              + "SYS10_TRANSLATIONS.LANGUAGE_CODE=? and "
              + "REG01_VATS.VAT_CODE=?";
      pstmt = conn.prepareStatement(sql);
      ResultSet rset = null;
      for (int i = 0; i < list.size(); i++) {
        vo = (ChargeVO) list.get(i);
        if (vo.getVatCodeReg01SAL06() != null) {
          // retrieve vat data from REG01...
          pstmt.setString(1, serverLanguageId);
          pstmt.setString(2, vo.getVatCodeReg01SAL06());
          rset = pstmt.executeQuery();
          if (rset.next()) {
            vo.setVatDescriptionSYS10(rset.getString(1));
            vo.setVatValueREG01(rset.getBigDecimal(2));
            vo.setVatDeductibleREG01(rset.getBigDecimal(3));
          }
          rset.close();
        }
      }

      Response answer = res;

      // 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 charges 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 final Response executeCommand(
      Object inputPar,
      UserSessionParameters userSessionPars,
      HttpServletRequest request,
      HttpServletResponse response,
      HttpSession userSession,
      ServletContext context) {
    String serverLanguageId = ((JAIOUserSessionParameters) userSessionPars).getServerLanguageId();
    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));

      ArrayList oldVOs = ((ArrayList[]) inputPar)[0];
      ArrayList newVOs = ((ArrayList[]) inputPar)[1];
      RoleFunctionCompanyVO oldVO = null;
      RoleFunctionCompanyVO newVO = null;
      Response res = null;

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

        if (!oldVO.getCanView().booleanValue()) {
          // no record in SYS02 yet...
          if (newVO.getCanView().booleanValue()) {
            pstmt =
                conn.prepareStatement(
                    "insert into SYS02_COMPANIES_ACCESS(PROGRESSIVE_SYS04,FUNCTION_CODE_SYS06,CAN_INS,CAN_UPD,CAN_DEL,COMPANY_CODE_SYS01) "
                        + "values(?,?,?,?,?,?)");
            pstmt.setBigDecimal(1, newVO.getProgressiveSys04SYS02());
            pstmt.setString(2, newVO.getFunctionCodeSys06SYS02());
            pstmt.setString(3, newVO.getCanInsSYS02().booleanValue() ? "Y" : "N");
            pstmt.setString(4, newVO.getCanUpdSYS02().booleanValue() ? "Y" : "N");
            pstmt.setString(5, newVO.getCanDelSYS02().booleanValue() ? "Y" : "N");
            pstmt.setString(6, newVO.getCompanyCodeSys01SYS02());
            pstmt.execute();
          }
        } else {
          // record already exists in SYS02...
          if (newVO.getCanView().booleanValue()) {
            // record in SYS02 will be updated...
            pstmt =
                conn.prepareStatement(
                    "update SYS02_COMPANIES_ACCESS set CAN_INS=?,CAN_UPD=?,CAN_DEL=? where "
                        + "PROGRESSIVE_SYS04=? and FUNCTION_CODE_SYS06=? and COMPANY_CODE_SYS01=? ");
            pstmt.setString(1, newVO.getCanInsSYS02().booleanValue() ? "Y" : "N");
            pstmt.setString(2, newVO.getCanUpdSYS02().booleanValue() ? "Y" : "N");
            pstmt.setString(3, newVO.getCanDelSYS02().booleanValue() ? "Y" : "N");
            pstmt.setBigDecimal(4, newVO.getProgressiveSys04SYS02());
            pstmt.setString(5, newVO.getFunctionCodeSys06SYS02());
            pstmt.setString(6, newVO.getCompanyCodeSys01SYS02());
            pstmt.execute();
          } else {
            // delete record from SYS02...
            pstmt =
                conn.prepareStatement(
                    "delete from SYS02_COMPANIES_ACCESS where PROGRESSIVE_SYS04=? and FUNCTION_CODE_SYS06=? and COMPANY_CODE_SYS01=?");
            pstmt.setBigDecimal(1, newVO.getProgressiveSys04SYS02());
            pstmt.setString(2, newVO.getFunctionCodeSys06SYS02());
            pstmt.setString(3, newVO.getCompanyCodeSys01SYS02());
            pstmt.execute();
          }
        }
      }

      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 company-role-function settings",
          ex);
      try {
        pstmt.close();
      } catch (Exception ex2) {
      }
      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 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) {
      }
    }
  }