/**
* Dynamic Init
*
* @param value value
* @param M_Warehouse_ID warehouse
* @param M_PriceList_ID price list
*/
private void initInfo(String value, int M_Warehouse_ID, int M_PriceList_ID) {
// Pick init
fillPicks(M_PriceList_ID);
int M_PriceList_Version_ID = findPLV(M_PriceList_ID);
// Set Value or Name
if (value.startsWith("@") && value.endsWith("@"))
fieldName.setText(value.substring(1, value.length() - 1));
else fieldValue.setText(value);
// Set Warehouse
if (M_Warehouse_ID == 0) M_Warehouse_ID = Env.getContextAsInt(Env.getCtx(), "#M_Warehouse_ID");
if (M_Warehouse_ID != 0) setWarehouse(M_Warehouse_ID);
// Set PriceList Version
if (M_PriceList_Version_ID != 0) setPriceListVersion(M_PriceList_Version_ID);
// Create Grid
StringBuffer where = new StringBuffer();
where.append("p.IsActive='Y'");
if (M_Warehouse_ID != 0) where.append(" AND p.IsSummary='N'");
// dynamic Where Clause
if (p_whereClause != null && p_whereClause.length() > 0)
where
.append(" AND ") // replace fully qualified name with alias
.append(Util.replace(p_whereClause, "M_Product.", "p."));
//
prepareTable(
getProductLayout(), s_productFrom, where.toString(), "QtyAvailable DESC, Margin DESC");
p_table.setShowTotals(false);
//
pickWarehouse.addActionListener(this);
pickPriceList.addActionListener(this);
pickProductCategory.addActionListener(this);
pickAS.addActionListener(this); // @Trifon
} // initInfo
/** Save Selection Settings for PriceList */
protected void saveSelectionDetail() {
// publish for Callout to read
Integer ID = getSelectedRowKey();
Env.setContext(
Env.getCtx(), p_WindowNo, Env.TAB_INFO, "M_Product_ID", ID == null ? "0" : ID.toString());
KeyNamePair kn = (KeyNamePair) pickPriceList.getSelectedItem();
Env.setContext(Env.getCtx(), p_WindowNo, Env.TAB_INFO, "M_PriceList_Version_ID", kn.getID());
kn = (KeyNamePair) pickWarehouse.getSelectedItem();
Env.setContext(Env.getCtx(), p_WindowNo, Env.TAB_INFO, "M_Warehouse_ID", kn.getID());
//
if (m_M_AttributeSetInstance_ID == -1) // not selected
{
Env.setContext(Env.getCtx(), p_WindowNo, Env.TAB_INFO, "M_AttributeSetInstance_ID", "0");
Env.setContext(Env.getCtx(), p_WindowNo, Env.TAB_INFO, "M_Locator_ID", "0");
} else {
Env.setContext(
Env.getCtx(),
p_WindowNo,
Env.TAB_INFO,
"M_AttributeSetInstance_ID",
String.valueOf(m_M_AttributeSetInstance_ID));
Env.setContext(
Env.getCtx(), p_WindowNo, Env.TAB_INFO, "M_Locator_ID", String.valueOf(m_M_Locator_ID));
}
} // saveSelectionDetail
/**
* Set Warehouse
*
* @param M_Warehouse_ID warehouse
*/
private void setWarehouse(int M_Warehouse_ID) {
for (int i = 0; i < pickWarehouse.getItemCount(); i++) {
KeyNamePair kn = (KeyNamePair) pickWarehouse.getItemAt(i);
if (kn.getKey() == M_Warehouse_ID) {
pickWarehouse.setSelectedIndex(i);
return;
}
}
} // setWarehouse
/**
* Set PriceList
*
* @param M_PriceList_Version_ID price list
*/
private void setPriceListVersion(int M_PriceList_Version_ID) {
log.config("M_PriceList_Version_ID=" + M_PriceList_Version_ID);
for (int i = 0; i < pickPriceList.getItemCount(); i++) {
KeyNamePair kn = (KeyNamePair) pickPriceList.getItemAt(i);
if (kn.getKey() == M_PriceList_Version_ID) {
pickPriceList.setSelectedIndex(i);
return;
}
}
log.fine("NOT found");
} // setPriceList
/** Initial table state */
private void loadBasicInfo() {
comboCNotifySearch.removeActionListener(this);
comboBPartner.removeActionListener(this);
monthCombo.removeAllItems();
yearField.setDisplayType(0);
comboBPartner.setEnabled(true);
comboBPartner.removeAllItems();
categoryCombo.removeAllItems();
departmentCombo.removeAllItems();
agreementTypeCombo.removeAllItems();
// Llenar los filtros de busquedas
llenarcombos();
}
/**
* ************************************************************************ Action Listener
*
* @param e event
*/
public void actionPerformed(ActionEvent e) {
// don't requery if fieldValue and fieldName are empty
if ((e.getSource() == pickWarehouse || e.getSource() == pickPriceList)
&& (fieldValue.getText().length() == 0 && fieldName.getText().length() == 0)) return;
// Product Attribute Search
if (e.getSource().equals(m_InfoPAttributeButton)) {
cmd_InfoPAttribute();
return;
}
m_pAttributeWhere = null;
// Query Product Attribute Instance
int row = p_table.getSelectedRow();
if (e.getSource().equals(m_PAttributeButton) && row != -1) {
Integer productInteger = getSelectedRowKey();
String productName = (String) p_table.getValueAt(row, INDEX_NAME);
KeyNamePair warehouse = (KeyNamePair) pickWarehouse.getSelectedItem();
if (productInteger == null || productInteger.intValue() == 0 || warehouse == null) return;
String title = warehouse.getName() + " - " + productName;
IPAttributeInstance pai =
PAttributeInstance.get(
this, title, warehouse.getKey(), 0, productInteger.intValue(), m_C_BPartner_ID);
m_M_AttributeSetInstance_ID = pai.getM_AttributeSetInstance_ID();
m_M_Locator_ID = pai.getM_Locator_ID();
if (m_M_AttributeSetInstance_ID != -1) dispose(true);
return;
}
//
super.actionPerformed(e);
} // actionPerformed
/** Fill Picks with values */
private void fillPicks() {
// Price List
String sql =
"SELECT M_PriceList_Version.M_PriceList_Version_ID,"
+ " M_PriceList_Version.Name || ' (' || c.Iso_Code || ')' AS ValueName "
+ "FROM M_PriceList_Version, M_PriceList pl, C_Currency c "
+ "WHERE M_PriceList_Version.M_PriceList_ID=pl.M_PriceList_ID"
+ " AND pl.C_Currency_ID=c.C_Currency_ID"
+ " AND M_PriceList_Version.IsActive='Y' AND pl.IsActive='Y'";
// Add Access & Order
sql =
MRole.getDefault()
.addAccessSQL(sql, "M_PriceList_Version", true, false) // fully qualidfied - RO
+ " ORDER BY M_PriceList_Version.Name";
try {
pickPriceList.addItem(new KeyNamePair(0, ""));
PreparedStatement pstmt = DB.prepareStatement(sql, null);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
KeyNamePair kn = new KeyNamePair(rs.getInt(1), rs.getString(2));
pickPriceList.addItem(kn);
}
rs.close();
pstmt.close();
// Warehouse
sql =
"SELECT M_Warehouse_ID, Value || ' - ' || Name AS ValueName "
+ "FROM M_Warehouse "
+ "WHERE IsActive='Y'";
sql =
MRole.getDefault().addAccessSQL(sql, "M_Warehouse", MRole.SQL_NOTQUALIFIED, MRole.SQL_RO)
+ " ORDER BY Value";
pickWarehouse.addItem(new KeyNamePair(0, ""));
pstmt = DB.prepareStatement(sql, null);
rs = pstmt.executeQuery();
while (rs.next()) {
KeyNamePair kn = new KeyNamePair(rs.getInt("M_Warehouse_ID"), rs.getString("ValueName"));
pickWarehouse.addItem(kn);
}
rs.close();
pstmt.close();
} catch (SQLException e) {
log.log(Level.SEVERE, sql, e);
}
} // fillPicks
private void llenarcombos() {
dynCategory();
dynDepartament();
// Cargar el combo box de mes
monthCombo.addItem(null);
monthCombo.addItem(Msg.translate(Env.getCtx(), "XX_January"));
monthCombo.addItem(Msg.translate(Env.getCtx(), "XX_February"));
monthCombo.addItem(Msg.translate(Env.getCtx(), "XX_March"));
monthCombo.addItem(Msg.translate(Env.getCtx(), "XX_April"));
monthCombo.addItem(Msg.translate(Env.getCtx(), "XX_May"));
monthCombo.addItem(Msg.translate(Env.getCtx(), "XX_June"));
monthCombo.addItem(Msg.translate(Env.getCtx(), "XX_July"));
monthCombo.addItem(Msg.translate(Env.getCtx(), "XX_August"));
monthCombo.addItem(Msg.translate(Env.getCtx(), "XX_September"));
monthCombo.addItem(Msg.translate(Env.getCtx(), "XX_October"));
monthCombo.addItem(Msg.translate(Env.getCtx(), "XX_November"));
monthCombo.addItem(Msg.translate(Env.getCtx(), "XX_December"));
// Cargar proveedores
String sql = "SELECT b.C_BPARTNER_ID, b.NAME FROM C_BPARTNER b WHERE isVendor='Y' ";
sql = MRole.getDefault().addAccessSQL(sql, "b", MRole.SQL_FULLYQUALIFIED, MRole.SQL_RO);
sql += " ORDER BY b.NAME";
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = DB.prepareStatement(sql, null);
rs = pstmt.executeQuery();
loadKNP = new KeyNamePair(0, new String());
comboBPartner.addItem(loadKNP);
while (rs.next()) {
loadKNP = new KeyNamePair(rs.getInt(1), rs.getString(2));
comboBPartner.addItem(loadKNP);
// comboBPartner.addItem(new KeyNamePair(rs.getInt(1), rs.getString(2)));
}
comboBPartner.setEditable(false);
} catch (SQLException e) {
log.log(Level.SEVERE, sql, e);
} finally {
DB.closeResultSet(rs);
DB.closeStatement(pstmt);
}
} // fin de llenar combos
/** Show History */
protected void showHistory() {
log.info("");
Integer M_Product_ID = getSelectedRowKey();
if (M_Product_ID == null) return;
KeyNamePair kn = (KeyNamePair) pickWarehouse.getSelectedItem();
int M_Warehouse_ID = kn.getKey();
int M_AttributeSetInstance_ID = m_M_AttributeSetInstance_ID;
if (m_M_AttributeSetInstance_ID < -1) // not selected
M_AttributeSetInstance_ID = 0;
//
InvoiceHistory ih =
new InvoiceHistory(
this, 0, M_Product_ID.intValue(), M_Warehouse_ID, M_AttributeSetInstance_ID);
ih.setVisible(true);
ih = null;
} // showHistory
/**
* ************************************************************************ Construct SQL Where
* Clause and define parameters (setParameters needs to set parameters) Includes first AND
*
* @return SQL WHERE clause
*/
protected String getSQLWhere() {
StringBuffer where = new StringBuffer();
// Optional PLV
int M_PriceList_Version_ID = 0;
KeyNamePair pl = (KeyNamePair) pickPriceList.getSelectedItem();
if (pl != null) M_PriceList_Version_ID = pl.getKey();
if (M_PriceList_Version_ID != 0) where.append(" AND pr.M_PriceList_Version_ID=?");
// Optional Product Category
if (getM_Product_Category_ID() > 0) {
where.append(" AND p.M_Product_Category_ID=?");
}
// Optional Attribute Set
if (getM_AttributeSet_ID() > 0) {
where.append(" AND p.M_AttributeSet_ID=?");
}
// Product Attribute Search
if (m_pAttributeWhere != null) {
where.append(m_pAttributeWhere);
return where.toString();
}
// => Value
String value = fieldValue.getText().toUpperCase();
if (!(value.equals("") || value.equals("%"))) where.append(" AND UPPER(p.Value) LIKE ?");
// => Name
String name = fieldName.getText().toUpperCase();
if (!(name.equals("") || name.equals("%"))) where.append(" AND UPPER(p.Name) LIKE ?");
// => UPC
String upc = fieldUPC.getText().toUpperCase();
if (!(upc.equals("") || upc.equals("%"))) where.append(" AND UPPER(p.UPC) LIKE ?");
// => SKU
String sku = fieldSKU.getText().toUpperCase();
if (!(sku.equals("") || sku.equals("%"))) where.append(" AND UPPER(p.SKU) LIKE ?");
// => Vendor
String vendor = fieldVendor.getText().toUpperCase();
if (!(vendor.equals("") || vendor.equals("%")))
where.append(" AND UPPER(bp.Name) LIKE ? AND ppo.IsCurrentVendor='Y'");
return where.toString();
} // getSQLWhere
/**
* Set Parameters for Query (as defined in getSQLWhere)
*
* @param pstmt pstmt
* @param forCount for counting records
* @throws SQLException
*/
protected void setParameters(PreparedStatement pstmt, boolean forCount) throws SQLException {
int index = 1;
// => Warehouse
int M_Warehouse_ID = 0;
KeyNamePair wh = (KeyNamePair) pickWarehouse.getSelectedItem();
if (wh != null) M_Warehouse_ID = wh.getKey();
if (!forCount) // parameters in select
{
for (int i = 0; i < p_layout.length; i++) {
if (p_layout[i].getColSQL().indexOf('?') != -1) pstmt.setInt(index++, M_Warehouse_ID);
}
}
log.fine("M_Warehouse_ID=" + M_Warehouse_ID + " (" + (index - 1) + "*)");
// => PriceList
int M_PriceList_Version_ID = 0;
KeyNamePair pl = (KeyNamePair) pickPriceList.getSelectedItem();
if (pl != null) M_PriceList_Version_ID = pl.getKey();
if (M_PriceList_Version_ID != 0) {
pstmt.setInt(index++, M_PriceList_Version_ID);
log.fine("M_PriceList_Version_ID=" + M_PriceList_Version_ID);
}
// => Product Category
int M_Product_Category_ID = getM_Product_Category_ID();
if (M_Product_Category_ID > 0) {
pstmt.setInt(index++, M_Product_Category_ID);
log.fine("M_Product_Category_ID=" + M_Product_Category_ID);
}
// => Attribute Set - @Trifon
int M_AttributeSet_ID = getM_AttributeSet_ID();
if (M_AttributeSet_ID > 0) {
pstmt.setInt(index++, M_AttributeSet_ID);
log.fine("M_AttributeSet_ID=" + M_AttributeSet_ID);
}
// Rest of Parameter in Query for Attribute Search
if (m_pAttributeWhere != null) return;
// => Value
String value = fieldValue.getText().toUpperCase();
if (!(value.equals("") || value.equals("%"))) {
if (!value.endsWith("%")) value += "%";
pstmt.setString(index++, value);
log.fine("Value: " + value);
}
// => Name
String name = fieldName.getText().toUpperCase();
if (!(name.equals("") || name.equals("%"))) {
if (!name.endsWith("%")) name += "%";
pstmt.setString(index++, name);
log.fine("Name: " + name);
}
// => UPC
String upc = fieldUPC.getText().toUpperCase();
if (!(upc.equals("") || upc.equals("%"))) {
if (!upc.endsWith("%")) upc += "%";
pstmt.setString(index++, upc);
log.fine("UPC: " + upc);
}
// => SKU
String sku = fieldSKU.getText().toUpperCase();
if (!(sku.equals("") || sku.equals("%"))) {
if (!sku.endsWith("%")) sku += "%";
pstmt.setString(index++, sku);
log.fine("SKU: " + sku);
}
// => Vendor
String vendor = fieldVendor.getText().toUpperCase();
if (!(vendor.equals("") || vendor.equals("%"))) {
if (!vendor.endsWith("%")) vendor += "%";
pstmt.setString(index++, vendor);
log.fine("Vendor: " + vendor);
}
} // setParameters
/**
* Fill Picks with values
*
* @param M_PriceList_ID price list
*/
private void fillPicks(int M_PriceList_ID) {
// Price List
String SQL =
"SELECT M_PriceList_Version.M_PriceList_Version_ID,"
+ " M_PriceList_Version.Name || ' (' || c.Iso_Code || ')' AS ValueName "
+ "FROM M_PriceList_Version, M_PriceList pl, C_Currency c "
+ "WHERE M_PriceList_Version.M_PriceList_ID=pl.M_PriceList_ID"
+ " AND pl.C_Currency_ID=c.C_Currency_ID"
+ " AND M_PriceList_Version.IsActive='Y' AND pl.IsActive='Y'";
// Same PL currency as original one
if (M_PriceList_ID != 0)
SQL +=
" AND EXISTS (SELECT * FROM M_PriceList xp WHERE xp.M_PriceList_ID="
+ M_PriceList_ID
+ " AND pl.C_Currency_ID=xp.C_Currency_ID)";
// Add Access & Order
SQL =
MRole.getDefault()
.addAccessSQL(SQL, "M_PriceList_Version", true, false) // fully qualified - RO
+ " ORDER BY M_PriceList_Version.Name";
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pickPriceList.addItem(new KeyNamePair(0, ""));
pstmt = DB.prepareStatement(SQL, null);
rs = pstmt.executeQuery();
while (rs.next()) {
KeyNamePair kn = new KeyNamePair(rs.getInt(1), rs.getString(2));
pickPriceList.addItem(kn);
}
DB.close(rs, pstmt);
// Warehouse
SQL =
MRole.getDefault()
.addAccessSQL(
"SELECT M_Warehouse_ID, Value || ' - ' || Name AS ValueName "
+ "FROM M_Warehouse "
+ "WHERE IsActive='Y'",
"M_Warehouse",
MRole.SQL_NOTQUALIFIED,
MRole.SQL_RO)
+ " ORDER BY Value";
pickWarehouse.addItem(new KeyNamePair(0, ""));
pstmt = DB.prepareStatement(SQL, null);
rs = pstmt.executeQuery();
while (rs.next()) {
KeyNamePair kn = new KeyNamePair(rs.getInt("M_Warehouse_ID"), rs.getString("ValueName"));
pickWarehouse.addItem(kn);
}
DB.close(rs, pstmt);
// Product Category
SQL =
MRole.getDefault()
.addAccessSQL(
"SELECT M_Product_Category_ID, Value || ' - ' || Name FROM M_Product_Category WHERE IsActive='Y'",
"M_Product_Category",
MRole.SQL_NOTQUALIFIED,
MRole.SQL_RO)
+ " ORDER BY Value";
for (KeyNamePair kn : DB.getKeyNamePairs(SQL, true)) {
pickProductCategory.addItem(kn);
}
// Attribute Set - @Trifon
SQL =
MRole.getDefault()
.addAccessSQL(
"SELECT M_AttributeSet_ID, Name FROM M_AttributeSet WHERE IsActive='Y'",
"M_AttributeSet",
MRole.SQL_NOTQUALIFIED,
MRole.SQL_RO)
+ " ORDER BY Name";
for (KeyNamePair kn : DB.getKeyNamePairs(SQL, true)) {
pickAS.addItem(kn);
}
} catch (SQLException e) {
log.log(Level.SEVERE, SQL, e);
setStatusLine(e.getLocalizedMessage(), true);
} finally {
DB.close(rs, pstmt);
rs = null;
pstmt = null;
}
} // fillPicks
/** Static Setup - add fields to parameterPanel */
private void statInit() {
labelValue.setText(Msg.getMsg(Env.getCtx(), "Value"));
fieldValue.setBackground(AdempierePLAF.getInfoBackground());
fieldValue.addActionListener(this);
labelName.setText(Msg.getMsg(Env.getCtx(), "Name"));
fieldName.setBackground(AdempierePLAF.getInfoBackground());
fieldName.addActionListener(this);
labelUPC.setText(Msg.translate(Env.getCtx(), "UPC"));
fieldUPC.setBackground(AdempierePLAF.getInfoBackground());
fieldUPC.addActionListener(this);
labelSKU.setText(Msg.translate(Env.getCtx(), "SKU"));
fieldSKU.setBackground(AdempierePLAF.getInfoBackground());
fieldSKU.addActionListener(this);
labelWarehouse.setText(Msg.getMsg(Env.getCtx(), "Warehouse"));
pickWarehouse.setBackground(AdempierePLAF.getInfoBackground());
labelPriceList.setText(Msg.getMsg(Env.getCtx(), "PriceListVersion"));
pickPriceList.setBackground(AdempierePLAF.getInfoBackground());
labelProductCategory.setText(Msg.translate(Env.getCtx(), "M_Product_Category_ID"));
pickProductCategory.setBackground(AdempierePLAF.getInfoBackground());
// @Trifon
labelAS.setText(Msg.translate(Env.getCtx(), "M_AttributeSet_ID"));
pickAS.setBackground(AdempierePLAF.getInfoBackground());
m_InfoPAttributeButton.setMargin(new Insets(2, 2, 2, 2));
m_InfoPAttributeButton.setToolTipText(Msg.getMsg(Env.getCtx(), "InfoPAttribute"));
m_InfoPAttributeButton.addActionListener(this);
labelVendor.setText(Msg.translate(Env.getCtx(), "Vendor"));
fieldVendor.setBackground(AdempierePLAF.getInfoBackground());
fieldVendor.addActionListener(this);
// Line 1
parameterPanel.setLayout(new ALayout());
parameterPanel.add(labelValue, new ALayoutConstraint(0, 0));
parameterPanel.add(fieldValue, null);
parameterPanel.add(labelUPC, null);
parameterPanel.add(fieldUPC, null);
parameterPanel.add(labelWarehouse, null);
parameterPanel.add(pickWarehouse, null);
parameterPanel.add(m_InfoPAttributeButton);
// Line 2
parameterPanel.add(labelName, new ALayoutConstraint(1, 0));
parameterPanel.add(fieldName, null);
parameterPanel.add(labelSKU, null);
parameterPanel.add(fieldSKU, null);
parameterPanel.add(labelVendor, null);
parameterPanel.add(fieldVendor, null);
// Line 3
parameterPanel.add(labelPriceList, new ALayoutConstraint(2, 0));
parameterPanel.add(pickPriceList, null);
parameterPanel.add(labelProductCategory, null);
parameterPanel.add(pickProductCategory, null);
parameterPanel.add(labelAS, null); // @Trifon
parameterPanel.add(pickAS, null); // @Trifon
// Product Attribute Instance
m_PAttributeButton = ConfirmPanel.createPAttributeButton(true);
confirmPanel.addButton(m_PAttributeButton);
m_PAttributeButton.addActionListener(this);
m_PAttributeButton.setEnabled(false);
// Begin - fer_luck @ centuryon
// add taskpane
fieldDescription.setBackground(AdempierePLAF.getInfoBackground());
fieldDescription.setEditable(false);
fieldDescription.setPreferredSize(new Dimension(INFO_WIDTH - 100, 100));
warehouseStockPanel.setTitle(Msg.translate(Env.getCtx(), "WarehouseStock"));
warehouseStockPanel.setUI(new AdempiereTaskPaneUI());
warehouseStockPanel.getContentPane().setBackground(new ColorUIResource(251, 248, 241));
warehouseStockPanel.getContentPane().setForeground(new ColorUIResource(251, 0, 0));
ColumnInfo[] s_layoutWarehouse =
new ColumnInfo[] {
new ColumnInfo(Msg.translate(Env.getCtx(), "Warehouse"), "Warehouse", String.class),
new ColumnInfo(
Msg.translate(Env.getCtx(), "QtyAvailable"), "sum(QtyAvailable)", Double.class),
new ColumnInfo(Msg.translate(Env.getCtx(), "QtyOnHand"), "sum(QtyOnHand)", Double.class),
new ColumnInfo(
Msg.translate(Env.getCtx(), "QtyReserved"), "sum(QtyReserved)", Double.class),
new ColumnInfo(
Msg.translate(Env.getCtx(), "QtyAllocated"), "sum(QtyAllocated)", Double.class)
};
/** From Clause */
String s_sqlFrom = " M_PRODUCT_STOCK_V ";
/** Where Clause */
String s_sqlWhere = "Value = ?";
m_sqlWarehouse =
warehouseTbl.prepareTable(
s_layoutWarehouse, s_sqlFrom, s_sqlWhere, false, "M_PRODUCT_STOCK_V");
m_sqlWarehouse += " Group By Warehouse, documentnote ";
warehouseTbl.setRowSelectionAllowed(true);
warehouseTbl.setMultiSelection(false);
warehouseTbl.addMouseListener(this);
warehouseTbl.getSelectionModel().addListSelectionListener(this);
warehouseTbl.setShowTotals(true);
warehouseTbl.autoSize();
ColumnInfo[] s_layoutSubstitute =
new ColumnInfo[] {
new ColumnInfo(Msg.translate(Env.getCtx(), "Warehouse"), "orgname", String.class),
new ColumnInfo(
Msg.translate(Env.getCtx(), "Value"),
"(Select Value from M_Product p where p.M_Product_ID=M_PRODUCT_SUBSTITUTERELATED_V.Substitute_ID)",
String.class),
new ColumnInfo(Msg.translate(Env.getCtx(), "Name"), "Name", String.class),
new ColumnInfo(Msg.translate(Env.getCtx(), "QtyAvailable"), "QtyAvailable", Double.class),
new ColumnInfo(Msg.translate(Env.getCtx(), "QtyOnHand"), "QtyOnHand", Double.class),
new ColumnInfo(Msg.translate(Env.getCtx(), "QtyReserved"), "QtyReserved", Double.class),
new ColumnInfo(Msg.translate(Env.getCtx(), "PriceStd"), "PriceStd", Double.class)
};
s_sqlFrom = "M_PRODUCT_SUBSTITUTERELATED_V";
s_sqlWhere = "M_Product_ID = ? AND M_PriceList_Version_ID = ? and RowType = 'S'";
m_sqlSubstitute =
substituteTbl.prepareTable(
s_layoutSubstitute, s_sqlFrom, s_sqlWhere, false, "M_PRODUCT_SUBSTITUTERELATED_V");
substituteTbl.setRowSelectionAllowed(false);
substituteTbl.setMultiSelection(false);
substituteTbl.addMouseListener(this);
substituteTbl.getSelectionModel().addListSelectionListener(this);
substituteTbl.autoSize();
ColumnInfo[] s_layoutRelated =
new ColumnInfo[] {
new ColumnInfo(Msg.translate(Env.getCtx(), "Warehouse"), "orgname", String.class),
new ColumnInfo(
Msg.translate(Env.getCtx(), "Value"),
"(Select Value from M_Product p where p.M_Product_ID=M_PRODUCT_SUBSTITUTERELATED_V.Substitute_ID)",
String.class),
new ColumnInfo(Msg.translate(Env.getCtx(), "Name"), "Name", String.class),
new ColumnInfo(Msg.translate(Env.getCtx(), "QtyAvailable"), "QtyAvailable", Double.class),
new ColumnInfo(Msg.translate(Env.getCtx(), "QtyOnHand"), "QtyOnHand", Double.class),
new ColumnInfo(Msg.translate(Env.getCtx(), "QtyReserved"), "QtyReserved", Double.class),
new ColumnInfo(Msg.translate(Env.getCtx(), "PriceStd"), "PriceStd", Double.class)
};
s_sqlFrom = "M_PRODUCT_SUBSTITUTERELATED_V";
s_sqlWhere = "M_Product_ID = ? AND M_PriceList_Version_ID = ? and RowType = 'R'";
m_sqlRelated =
relatedTbl.prepareTable(
s_layoutRelated, s_sqlFrom, s_sqlWhere, false, "M_PRODUCT_SUBSTITUTERELATED_V");
relatedTbl.setRowSelectionAllowed(false);
relatedTbl.setMultiSelection(false);
relatedTbl.addMouseListener(this);
relatedTbl.getSelectionModel().addListSelectionListener(this);
relatedTbl.autoSize();
// Available to Promise Tab
m_tableAtp.setRowSelectionAllowed(false);
m_tableAtp.setMultiSelection(false);
CTabbedPane jTab = new CTabbedPane();
jTab.addTab(Msg.translate(Env.getCtx(), "Warehouse"), new JScrollPane(warehouseTbl));
jTab.setPreferredSize(new Dimension(INFO_WIDTH, SCREEN_HEIGHT > 600 ? 250 : 105));
jTab.addTab(Msg.translate(Env.getCtx(), "Description"), new JScrollPane(fieldDescription));
jTab.addTab(Msg.translate(Env.getCtx(), "Substitute_ID"), new JScrollPane(substituteTbl));
jTab.addTab(Msg.translate(Env.getCtx(), "RelatedProduct_ID"), new JScrollPane(relatedTbl));
jTab.addTab(Msg.getMsg(Env.getCtx(), "ATP"), new JScrollPane(m_tableAtp));
jTab.addChangeListener(this);
tablePanel.setPreferredSize(new Dimension(INFO_WIDTH, SCREEN_HEIGHT > 600 ? 255 : 110));
tablePanel.add(jTab);
warehouseStockPanel.setCollapsed(true);
warehouseStockPanel.add(tablePanel);
this.addonPanel.add(warehouseStockPanel);
this.p_table.addKeyListener(
new KeyAdapter() {
public void keyReleased(KeyEvent ke) {
int row = ((MiniTable) ke.getSource()).getSelectedRow();
refresh(
((MiniTable) ke.getSource()).getValueAt(row, 2),
new BigDecimal(pickWarehouse.getValue().toString()).intValue(),
new BigDecimal(pickPriceList.getValue().toString()).intValue());
warehouseStockPanel.setCollapsed(false);
}
});
this.p_table.addMouseListener(
new MouseAdapter() {
public void mouseClicked(MouseEvent me) {
int row = ((MiniTable) me.getSource()).getSelectedRow();
refresh(
((MiniTable) me.getSource()).getValueAt(row, 2),
new BigDecimal(pickWarehouse.getValue().toString()).intValue(),
new BigDecimal(pickPriceList.getValue().toString()).intValue());
warehouseStockPanel.setCollapsed(false);
}
});
// End - fer_luck @ centuryon
} // statInit
/** @return selected Attribute Set ID */
public int getM_AttributeSet_ID() {
int M_AttributeSet_ID = 0;
KeyNamePair as = (KeyNamePair) pickAS.getSelectedItem();
if (as != null) M_AttributeSet_ID = as.getKey();
return M_AttributeSet_ID;
}
/** @return selected product category ID */
public int getM_Product_Category_ID() {
int M_Product_Category_ID = 0;
KeyNamePair pc = (KeyNamePair) pickProductCategory.getSelectedItem();
if (pc != null) M_Product_Category_ID = pc.getKey();
return M_Product_Category_ID;
}
/** Create Grid */
private void createGrid() {
if (attributeCombo1 == null || m_setting) return; // init
int indexAttr1 = attributeCombo1.getSelectedIndex();
int indexAttr2 = attributeCombo2.getSelectedIndex();
if (indexAttr1 == indexAttr2) {
log.warning("Same Attribute Selected");
tabbedPane.setSelectedIndex(0);
return;
}
m_setting = true;
m_M_PriceList_Version_ID = 0;
KeyNamePair pl = (KeyNamePair) pickPriceList.getSelectedItem();
if (pl != null) m_M_PriceList_Version_ID = pl.getKey();
m_M_Warehouse_ID = 0;
KeyNamePair wh = (KeyNamePair) pickWarehouse.getSelectedItem();
if (wh != null) m_M_Warehouse_ID = wh.getKey();
// x dimension
int cols = 2;
MAttributeValue[] xValues = null;
if (indexAttr1 > 0) xValues = m_attributes[indexAttr1 - 1].getMAttributeValues();
if (xValues != null) {
cols = xValues.length;
log.info("X - " + m_attributes[indexAttr1 - 1].getName() + " #" + xValues.length);
}
// y dimension
int rows = 2;
MAttributeValue[] yValues = null;
if (indexAttr2 > 0) yValues = m_attributes[indexAttr2 - 1].getMAttributeValues();
if (yValues != null) {
rows = yValues.length;
log.info("Y - " + m_attributes[indexAttr2 - 1].getName() + " #" + yValues.length);
}
//
gridPanel.removeAll();
CPanel grid = new CPanel(new GridLayout(rows, cols, 5, 5));
gridPanel.add(modePanel, BorderLayout.NORTH);
gridPanel.add(new CScrollPane(grid), BorderLayout.CENTER);
//
log.info("Rows=" + rows + " - Cols=" + cols);
for (int row = 0; row < rows; row++) {
for (int col = 0; col < cols; col++) {
MAttributeValue xValue = null;
if (xValues != null) xValue = xValues[col];
MAttributeValue yValue = null;
if (yValues != null) yValue = yValues[row];
// log.fine("Row=" + row + " - Col=" + col);
//
if (row == 0 && col == 0) {
CPanel descr = new CPanel(new GridLayout(2, 1, 0, 0));
if (xValues != null)
descr.add(new JLabel(m_attributes[indexAttr1 - 1].getName(), JLabel.TRAILING));
if (yValues != null) descr.add(new JLabel(m_attributes[indexAttr2 - 1].getName()));
grid.add(descr);
} else if (row == 0) // column labels
{
if (xValue != null) {
grid.add(new JLabel(xValue.getName(), JLabel.TRAILING));
} else grid.add(new JLabel());
} else if (col == 0) // row labels
{
if (yValue != null) grid.add(new JLabel(yValue.getName()));
else grid.add(new JLabel());
} else {
grid.add(getGridElement(xValue, yValue));
}
}
}
//
tabbedPane.setSelectedIndex(1);
m_setting = false;
m_frame.pack();
} // createGrid
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
