public void check_all() { boolean checkall = false; if (xProductTable.getRowCount() > 0) { IDColumn id = (IDColumn) xProductTable.getValueAt(0, 0); if (id.isSelected()) { checkall = false; } else { checkall = true; } } for (int row = 0; row < xProductTable.getRowCount(); row++) { IDColumn old_id = (IDColumn) xProductTable.getValueAt(row, 0); IDColumn id = new IDColumn(old_id.getRecord_ID()); id.setSelected(checkall); xProductTable.setValueAt(id, row, 0); } }
private void tableInit() { m_sql = new StringBuffer(); m_sql2 = new StringBuffer(); // Mostrar todos los ACC y AAP if (tableInit_option == 0) { m_sql.append( "select cb.value ||'-'|| cb.name vendor, co.name country, extract(month from cn.CREATED) month," + " extract (year from cn.CREATED) year, NVL(round(sum(cn.XX_UNITPURCHASEPRICEBS),2),0) total," + " dep.value || '-' || dep.name department, cat.value || '-' || cat.name category," + " o.XX_VMR_DEPARTMENT_ID, o.XX_VMR_CATEGORY_ID, o.C_BPARTNER_ID vendorID" + " from XX_CREDITNOTIFYRETURN cn, C_BPARTNER cb, C_COUNTRY co, C_ORDER o, XX_VMR_DEPARTMENT dep, XX_VMR_CATEGORY cat" + " where o.C_BPARTNER_ID=cb.C_BPARTNER_ID and co.C_COUNTRY_ID=o.C_COUNTRY_ID and" + " cn.XX_NOTIFICATIONTYPE='ACC'and cn.C_ORDER_ID=o.C_ORDER_ID and" + " dep.XX_VMR_DEPARTMENT_ID=o.XX_VMR_DEPARTMENT_ID and o.XX_VMR_CATEGORY_ID=cat.XX_VMR_CATEGORY_ID and o.AD_CLIENT_ID=" + ctx.getAD_Client_ID() + " AND o.ISSOTRX = 'N'"); m_groupBy = " group by cb.value ||'-'|| cb.name, co.name, extract(month from cn.CREATED), extract (year from cn.CREATED), dep.value || '-' || dep.name," + " cat.value || '-' || cat.name,o.XX_VMR_DEPARTMENT_ID, o.XX_VMR_CATEGORY_ID, o.C_BPARTNER_ID"; m_sql2.append( "select cb.value ||'-'|| cb.name vendor, co.name country, det.XX_MONTH month," + " det.XX_YEAR year, round(cn.XX_UNITPURCHASEPRICEBS,2) total, o.C_BPARTNER_ID vendorID," + " o.XX_VMR_DEPARTMENT_ID, o.XX_VMR_CATEGORY_ID, cn.XX_CREDITNOTIFYRETURN_ID creditNotID, cn.XX_NOTIFICATIONTYPE tipo" + " from XX_CREDITNOTIFYRETURN cn, C_BPARTNER cb, C_COUNTRY co, C_ORDER o, XX_VMR_DEPARTMENT dep, XX_VMR_CATEGORY cat, XX_VCN_DETAILADVICE det" + " where o.C_BPARTNER_ID=cb.C_BPARTNER_ID and co.C_COUNTRY_ID=o.C_COUNTRY_ID and cn.XX_NOTIFICATIONTYPE<>'ACC'" + " and cn.C_ORDER_ID=o.C_ORDER_ID and dep.XX_VMR_DEPARTMENT_ID=o.XX_VMR_DEPARTMENT_ID" + " and o.XX_VMR_CATEGORY_ID=cat.XX_VMR_CATEGORY_ID and det.XX_CREDITNOTIFYRETURN_ID=cn.XX_CREDITNOTIFYRETURN_ID and o.AD_CLIENT_ID=" + ctx.getAD_Client_ID() + " AND o.ISSOTRX = 'N'"); m_groupBy2 = " group by cb.value ||'-'|| cb.name, co.name, det.XX_MONTH, det.XX_YEAR, cn.XX_UNITPURCHASEPRICEBS," + " o.XX_VMR_DEPARTMENT_ID, o.XX_VMR_CATEGORY_ID, o.C_BPARTNER_ID, cn.XX_CREDITNOTIFYRETURN_ID, cn.XX_NOTIFICATIONTYPE"; // Búsqueda por mes if (monthCombo.getSelectedIndex() != 0 && monthCombo.getSelectedItem() != null) { m_sql .append(" AND ") .append("extract(month from cn.created)=") .append(monthCombo.getSelectedIndex()); m_sql2.append(" AND ").append("det.XX_MONTH=").append(monthCombo.getSelectedIndex()); } // Búsqueda por año if (yearField.getValue() != null) { m_sql .append(" AND ") .append("extract(year from cn.created)=") .append(((BigDecimal) yearField.getValue()).intValue()); m_sql2 .append(" AND ") .append("det.XX_YEAR=") .append(((BigDecimal) yearField.getValue()).intValue()); } // Búsqueda por proveedor if (comboBPartner.getSelectedIndex() != 0 && comboBPartner.getSelectedItem() != null) { if (((KeyNamePair) comboBPartner.getSelectedItem()).getKey() != 0) { int clave_vendor = ((KeyNamePair) comboBPartner.getSelectedItem()).getKey(); m_sql.append(" AND ").append("o.C_BPartner_ID=").append(clave_vendor); m_sql2.append(" AND ").append("o.C_BPartner_ID=").append(clave_vendor); } } // agrego la categoria if (categoryCombo.getValue() != null) { KeyNamePair cat = (KeyNamePair) categoryCombo.getValue(); if (cat.getKey() != -1) { m_sql.append(" AND o.XX_VMR_CATEGORY_ID = ").append(cat.getKey()).append(" "); m_sql2.append(" AND o.XX_VMR_CATEGORY_ID = ").append(cat.getKey()).append(" "); } } // agrego el departamento al query if (departmentCombo.getValue() != null) { KeyNamePair dep = (KeyNamePair) departmentCombo.getValue(); if (dep.getKey() != -1) { m_sql.append(" AND o.XX_VMR_DEPARTMENT_ID = ").append(dep.getKey()).append(" "); m_sql2.append(" AND o.XX_VMR_DEPARTMENT_ID = ").append(dep.getKey()).append(" "); } } } String SQL = m_sql.toString() + m_groupBy; int i = 0; xTableACC.setRowCount(i); PreparedStatement pstmt = null; ResultSet rs = null; try { pstmt = DB.prepareStatement(SQL, null); rs = pstmt.executeQuery(); while (rs.next()) { xTableACC.setRowCount(i + 1); // Proveedor xTableACC.setValueAt(rs.getString("vendor"), i, 0); // 1 // País xTableACC.setValueAt(rs.getString("country"), i, 1); // Departamento xTableACC.setValueAt(rs.getString("department"), i, 2); // Categoría xTableACC.setValueAt(rs.getString("category"), i, 3); // Mes xTableACC.setValueAt(rs.getInt("month"), i, 4); // Año xTableACC.setValueAt(rs.getInt("year"), i, 5); // Compras hechas xTableACC.setValueAt(rs.getBigDecimal("total"), i, 6); // Se calculan los avisos cerrados(CER) de el mismo mes, año, proveedor, depto y categoría String SQL2 = "select nvl(round(sum(cn.XX_AMOUNT),2),0) totalDesc" + " from XX_CREDITNOTIFYRETURN cn, C_BPARTNER cb, C_COUNTRY co, C_ORDER o, XX_VMR_DEPARTMENT dep, XX_VMR_CATEGORY cat" + " where o.C_BPARTNER_ID=cb.C_BPARTNER_ID and co.C_COUNTRY_ID=o.C_COUNTRY_ID" + " and cn.XX_NOTIFICATIONTYPE='ACC' and cn.C_ORDER_ID=o.C_ORDER_ID" + " and dep.XX_VMR_DEPARTMENT_ID=o.XX_VMR_DEPARTMENT_ID and o.XX_VMR_CATEGORY_ID=cat.XX_VMR_CATEGORY_ID" + " and XX_STATUS='CER' and extract(month from cn.CREATED)=" + rs.getInt("month") + " and extract (year from cn.CREATED)=" + rs.getInt("year") + " and o.C_BPARTNER_ID=" + rs.getInt("vendorID") + " and o.AD_CLIENT_ID=" + ctx.getAD_Client_ID() + " AND o.ISSOTRX = 'N'"; PreparedStatement pstmt2 = null; ResultSet rs2 = null; try { pstmt2 = DB.prepareStatement(SQL2, null); rs2 = pstmt2.executeQuery(); if (rs2.next()) xTableACC.setValueAt(rs2.getBigDecimal("totalDesc"), i, 7); else xTableACC.setValueAt(0, i, 7); } catch (SQLException e) { System.out.print(e.getMessage()); } finally { DB.closeResultSet(rs2); DB.closeStatement(pstmt2); } // Se calculan los avisos pendientes(ACT) de el mismo mes, año, proveedor, depto y categoría String SQL3 = "select nvl(round(sum(cn.XX_AMOUNT),2),0) totalDesc" + " from XX_CREDITNOTIFYRETURN cn, C_BPARTNER cb, C_COUNTRY co, C_ORDER o, XX_VMR_DEPARTMENT dep, XX_VMR_CATEGORY cat" + " where o.C_BPARTNER_ID=cb.C_BPARTNER_ID and co.C_COUNTRY_ID=o.C_COUNTRY_ID" + " and cn.XX_NOTIFICATIONTYPE='ACC' and cn.C_ORDER_ID=o.C_ORDER_ID" + " and dep.XX_VMR_DEPARTMENT_ID=o.XX_VMR_DEPARTMENT_ID and o.XX_VMR_CATEGORY_ID=cat.XX_VMR_CATEGORY_ID" + " and XX_STATUS='ACT' and extract(month from cn.CREATED)=" + rs.getInt("month") + " and extract (year from cn.CREATED)=" + rs.getInt("year") + " and o.C_BPARTNER_ID=" + rs.getInt("vendorID") + " and o.AD_CLIENT_ID=" + ctx.getAD_Client_ID() + " AND o.ISSOTRX = 'N'"; PreparedStatement pstmt3 = null; ResultSet rs3 = null; try { pstmt3 = DB.prepareStatement(SQL3, null); rs3 = pstmt3.executeQuery(); if (rs3.next()) xTableACC.setValueAt(rs3.getBigDecimal("totalDesc"), i, 8); else xTableACC.setValueAt(0, i, 8); } catch (SQLException e) { System.out.print(e.getMessage()); } finally { DB.closeResultSet(rs3); DB.closeStatement(pstmt3); } i++; } } catch (SQLException e) { e.getMessage(); } finally { DB.closeResultSet(rs); DB.closeStatement(pstmt); } String sqlAAP = m_sql2.toString() + m_groupBy2; i = 0; xTableAAP.setRowCount(i); pstmt = null; rs = null; try { pstmt = DB.prepareStatement(sqlAAP, null); rs = pstmt.executeQuery(); while (rs.next()) { xTableAAP.setRowCount(i + 1); // Proveedor xTableAAP.setValueAt( new KeyNamePair(rs.getInt("vendorID"), rs.getString("vendor")), i, 0); // 1 // País xTableAAP.setValueAt( new KeyNamePair(rs.getInt("creditNotID"), rs.getString("country")), i, 1); // Mes xTableAAP.setValueAt(rs.getInt("month"), i, 2); // Año xTableAAP.setValueAt(rs.getInt("year"), i, 3); // Tipo xTableAAP.setValueAt(rs.getString("tipo"), i, 4); // Compras hechas xTableAAP.setValueAt(rs.getBigDecimal("total"), i, 5); // Se calculan los avisos cerrados(CER) de el mismo mes, año, proveedor, depto y categoría String SQL2 = "select nvl(round(sum(cn.XX_AMOUNT),2),0) totalDesc" + " from XX_CREDITNOTIFYRETURN cn, C_BPARTNER cb, C_COUNTRY co, C_ORDER o, XX_VMR_DEPARTMENT dep, XX_VMR_CATEGORY cat" + " where o.C_BPARTNER_ID=cb.C_BPARTNER_ID and co.C_COUNTRY_ID=o.C_COUNTRY_ID" + " and cn.XX_NOTIFICATIONTYPE<>'ACC' and cn.C_ORDER_ID=o.C_ORDER_ID" + " and dep.XX_VMR_DEPARTMENT_ID=o.XX_VMR_DEPARTMENT_ID and o.XX_VMR_CATEGORY_ID=cat.XX_VMR_CATEGORY_ID" + " and XX_STATUS='CER' and cn.XX_CREDITNOTIFYRETURN_ID=" + rs.getInt("creditNotID") + " and o.C_BPARTNER_ID=" + rs.getInt("vendorID") + " and o.AD_CLIENT_ID=" + ctx.getAD_Client_ID() + " AND o.ISSOTRX = 'N'"; PreparedStatement pstmt2 = null; ResultSet rs2 = null; try { pstmt2 = DB.prepareStatement(SQL2, null); rs2 = pstmt2.executeQuery(); if (rs2.next()) xTableAAP.setValueAt(rs2.getBigDecimal("totalDesc"), i, 6); else xTableAAP.setValueAt(0, i, 6); } catch (SQLException e) { System.out.print(e.getMessage()); } finally { DB.closeResultSet(rs2); DB.closeStatement(pstmt2); } // Se calculan los avisos pendientes(ACT) de el mismo mes, año, proveedor, depto y categoría String SQL3 = "select nvl(round(sum(cn.XX_AMOUNT),2),0) totalDesc" + " from XX_CREDITNOTIFYRETURN cn, C_BPARTNER cb, C_COUNTRY co, C_ORDER o, XX_VMR_DEPARTMENT dep, XX_VMR_CATEGORY cat" + " where o.C_BPARTNER_ID=cb.C_BPARTNER_ID and co.C_COUNTRY_ID=o.C_COUNTRY_ID" + " and cn.XX_NOTIFICATIONTYPE<>'ACC' and cn.C_ORDER_ID=o.C_ORDER_ID" + " and dep.XX_VMR_DEPARTMENT_ID=o.XX_VMR_DEPARTMENT_ID and o.XX_VMR_CATEGORY_ID=cat.XX_VMR_CATEGORY_ID" + " and XX_STATUS='ACT' and cn.XX_CREDITNOTIFYRETURN_ID=" + rs.getInt("creditNotID") + " and o.C_BPARTNER_ID=" + rs.getInt("vendorID") + " and o.AD_CLIENT_ID=" + ctx.getAD_Client_ID() + " AND o.ISSOTRX = 'N'"; PreparedStatement pstmt3 = null; ResultSet rs3 = null; try { pstmt3 = DB.prepareStatement(SQL3, null); rs3 = pstmt3.executeQuery(); if (rs3.next()) xTableAAP.setValueAt(rs3.getBigDecimal("totalDesc"), i, 7); else xTableAAP.setValueAt(0, i, 7); } catch (SQLException e) { System.out.print(e.getMessage()); } finally { DB.closeResultSet(rs3); DB.closeStatement(pstmt3); } i++; } } catch (SQLException e) { e.getMessage(); } finally { DB.closeResultSet(rs); DB.closeStatement(pstmt); } } // tableInit
private void fillProductTable() { int row = 0; try { String get_details = " SELECT XX_VMR_DiscountAppliDetail_ID FROM XX_VMR_DiscountAppliDetail " + " WHERE XX_VMR_DiscountRequest_ID = " + header.get_ID(); PreparedStatement pstmt = DB.prepareStatement(get_details, null); // Setting the query parameters ResultSet rs = pstmt.executeQuery(); while (rs.next()) { row = xProductTable.getRowCount(); xProductTable.setRowCount(row + 1); MVMRDiscountAppliDetail detail = new MVMRDiscountAppliDetail(Env.getCtx(), rs.getInt(1), null); IDColumn id = new IDColumn(rs.getInt(1)); id.setSelected(true); xProductTable.setValueAt(id, row, 0); MProduct prod = new MProduct(Env.getCtx(), detail.getM_Product_ID(), null); xProductTable.setValueAt(new KeyNamePair(prod.get_ID(), prod.getValue()), row, 1); xProductTable.setValueAt(prod.getKeyNamePair(), row, 3); X_XX_VMR_VendorProdRef ref_proveedor = new X_XX_VMR_VendorProdRef(Env.getCtx(), prod.getXX_VMR_VendorProdRef_ID(), null); xProductTable.setValueAt( new KeyNamePair(ref_proveedor.get_ID(), ref_proveedor.getValue()), row, 2); // Colocar el consecutivo de precio DecimalFormat formato = new DecimalFormat("000"); X_XX_VMR_PriceConsecutive priceConsecutive = new X_XX_VMR_PriceConsecutive(Env.getCtx(), detail.getXX_PriceConsecutive_ID(), null); xProductTable.setValueAt(formato.format(priceConsecutive.getXX_PriceConsecutive()), row, 4); xProductTable.setValueAt(detail.getXX_LoweringQuantity(), row, 5); xProductTable.setValueAt(detail.getXX_LoweringQuantity(), row, 6); X_XX_VMR_Category cat = new X_XX_VMR_Category(Env.getCtx(), prod.getXX_VMR_Category_ID(), null); xProductTable.setValueAt(cat.getKeyNamePair(), row, 7); X_XX_VMR_Department dep = new X_XX_VMR_Department(Env.getCtx(), header.getXX_VMR_Department_ID(), null); xProductTable.setValueAt(dep.getKeyNamePair(), row, 8); X_XX_VMR_Line lin = new X_XX_VMR_Line(Env.getCtx(), detail.getXX_VMR_Line_ID(), null); xProductTable.setValueAt(lin.getKeyNamePair(), row, 9); X_XX_VMR_Section sec = new X_XX_VMR_Section(Env.getCtx(), detail.getXX_VMR_Section_ID(), null); xProductTable.setValueAt(sec.getKeyNamePair(), row, 10); if (priceConsecutive.getM_AttributeSetInstance_ID() != 0) { MAttributeSetInstance attins = new MAttributeSetInstance( Env.getCtx(), priceConsecutive.getM_AttributeSetInstance_ID(), null); xProductTable.setValueAt( new KeyNamePair(attins.get_ID(), attins.getDescription()), row, 11); } else { xProductTable.setValueAt(new KeyNamePair(0, ""), row, 11); } } rs.close(); pstmt.close(); } catch (Exception E) { E.printStackTrace(); } }