/** Business logic to execute. */ public VOListResponse loadItemVariants(GridParams pars, String serverLanguageId, String username) throws Throwable { PreparedStatement pstmt = null; Connection conn = null; try { if (this.conn == null) conn = getConn(); else conn = this.conn; String tableName = (String) pars.getOtherGridParams().get(ApplicationConsts.TABLE_NAME); ItemPK pk = (ItemPK) pars.getOtherGridParams().get(ApplicationConsts.ITEM_PK); String productVariant = (String) productVariants.get(tableName); String variantType = (String) variantTypes.get(tableName); String variantTypeJoin = (String) variantTypeJoins.get(tableName); String variantCodeJoin = (String) variantCodeJoins.get(tableName); String sql = "select " + tableName + "." + variantTypeJoin + "," + tableName + ".VARIANT_CODE,A.DESCRIPTION,B.DESCRIPTION, " + tableName + ".PROGRESSIVE_SYS10," + variantType + ".PROGRESSIVE_SYS10 " + "from " + tableName + "," + variantType + ",SYS10_COMPANY_TRANSLATIONS A,SYS10_COMPANY_TRANSLATIONS B " + "where " + tableName + ".COMPANY_CODE_SYS01=? and " + tableName + ".COMPANY_CODE_SYS01=" + variantType + ".COMPANY_CODE_SYS01 and " + tableName + "." + variantTypeJoin + "=" + variantType + ".VARIANT_TYPE and " + tableName + ".COMPANY_CODE_SYS01=A.COMPANY_CODE_SYS01 and " + tableName + ".PROGRESSIVE_SYS10=A.PROGRESSIVE and A.LANGUAGE_CODE=? and " + variantType + ".COMPANY_CODE_SYS01=B.COMPANY_CODE_SYS01 and " + variantType + ".PROGRESSIVE_SYS10=B.PROGRESSIVE and B.LANGUAGE_CODE=? and " + tableName + ".ENABLED='Y' and " + variantType + ".ENABLED='Y' and " + // and not "+tableName+"."+variantTypeJoin+"=? and "+ "not " + tableName + ".VARIANT_CODE=? " + "order by " + tableName + "." + variantTypeJoin + "," + tableName + ".CODE_ORDER"; Map attribute2dbField = new HashMap(); attribute2dbField.put("variantType", tableName + "." + variantTypeJoin); attribute2dbField.put("variantCode", tableName + ".VARIANT_CODE"); attribute2dbField.put("variantDesc", "A.DESCRIPTION"); attribute2dbField.put("variantTypeDesc", "B.DESCRIPTION"); attribute2dbField.put("variantProgressiveSys10", tableName + ".PROGRESSIVE_SYS10"); attribute2dbField.put("variantTypeProgressiveSys10", variantType + ".PROGRESSIVE_SYS10"); ArrayList values = new ArrayList(); values.add(pk.getCompanyCodeSys01ITM01()); values.add(serverLanguageId); values.add(serverLanguageId); // values.add(ApplicationConsts.JOLLY); values.add(ApplicationConsts.JOLLY); // read from ITMxxx table... Response answer = QueryUtil.getQuery( conn, new UserSessionParameters(username), sql, values, attribute2dbField, ItemVariantVO.class, "Y", "N", null, pars, 50, true); if (!answer.isError()) { java.util.List vos = ((VOListResponse) answer).getRows(); HashMap map = new HashMap(); ItemVariantVO vo = null; for (int i = 0; i < vos.size(); i++) { vo = (ItemVariantVO) vos.get(i); vo.setCompanyCodeSys01(pk.getCompanyCodeSys01ITM01()); vo.setItemCodeItm01(pk.getItemCodeITM01()); vo.setTableName(tableName); map.put(vo.getVariantType() + "." + vo.getVariantCode(), vo); } pstmt = conn.prepareStatement( "select " + productVariant + "." + variantTypeJoin + "," + productVariant + "." + variantCodeJoin + " " + "from " + productVariant + " " + "where " + productVariant + ".COMPANY_CODE_SYS01=? and " + productVariant + ".ITEM_CODE_ITM01=? and " + productVariant + ".ENABLED='Y' "); pstmt.setString(1, pk.getCompanyCodeSys01ITM01()); pstmt.setString(2, pk.getItemCodeITM01()); ResultSet rset = pstmt.executeQuery(); while (rset.next()) { vo = (ItemVariantVO) map.get(rset.getString(1) + "." + rset.getString(2)); if (vo != null) vo.setSelected(Boolean.TRUE); } rset.close(); pstmt.close(); } if (answer.isError()) throw new Exception(answer.getErrorMessage()); else return (VOListResponse) answer; } catch (Throwable ex) { Logger.error( username, this.getClass().getName(), "getItemVariants", "Error while fetching item variants list", ex); throw new Exception(ex.getMessage()); } finally { try { pstmt.close(); } catch (Exception ex2) { } try { if (this.conn == null && conn != null) { // close only local connection conn.commit(); conn.close(); } } catch (Exception exx) { } } }
/** Business logic to execute. */ public 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 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) { } } }