private void perDNOTEComboBoxItemStateChanged(
java.awt.event.ItemEvent evt) { // GEN-FIRST:event_perDNOTEComboBoxItemStateChanged
{
{
try {
reportBodyTable.setModel(
com.afrisoftech.dbadmin.TableModel.createTableVectors(
connectDB,
""
+ "SELECT order_no as LPO_NO,invoice_no as DNOTE_no, item,item_code ,units, price_per_item, quantity_received, vat_amount, quantity_ordered as Amount, supplier, store, received_by, date "
+ "FROM st_stock_cardex where transaction_type='Stock Returns' and ORDER_NO='"
+ perLPOComboBox.getSelectedItem().toString()
+ "' and requisition_no ilike 'PCRT%' "
+ " and date between '"
+ com.afrisoftech.lib.SQLDateFormat.getSQLDate(stratDatePicker.getDate())
+ "' and '"
+ com.afrisoftech.lib.SQLDateFormat.getSQLDate(endDatePicker.getDate())
+ "' "
+ "and delivery_note_no='"
+ perDNOTEComboBox.getSelectedItem().toString()
+ "' "
+ "group by order_no ,invoice_no , item,item_code ,units, price_per_item, quantity_received, vat_amount, quantity_ordered , supplier, store, received_by, date\n"
+ " order by 1,2,3,4,5,6,7 "));
} catch (Exception ex) {
System.err.println("the item error is " + ex);
}
}
}
} // GEN-LAST:event_perDNOTEComboBoxItemStateChanged
private void perDNOTEComboBoxItemStateChanged(
java.awt.event.ItemEvent evt) { // GEN-FIRST:event_perDNOTEComboBoxItemStateChanged
{
{
try {
reportBodyTable.setModel(
com.afrisoftech.dbadmin.TableModel.createTableVectors(
connectDB,
""
+ "SELECT grn_no as S13,order_no,delivery_note_no,supplier,item,item_code,SUM(quantity_required)as QTY_Required,SUM(quantity_received) as QTY_Supplied, "
+ "price_per_item,sum(debit) as Amount FROM st_stock_cardex "
+ "where ORDER_NO='"
+ perLPOComboBox.getSelectedItem().toString()
+ "' and grn_no='"
+ perDNOTEComboBox.getSelectedItem().toString()
+ "' "
+ " and date between '"
+ com.afrisoftech.lib.SQLDateFormat.getSQLDate(stratDatePicker.getDate())
+ "' and '"
+ com.afrisoftech.lib.SQLDateFormat.getSQLDate(endDatePicker.getDate())
+ "' "
+ "GROUP BY grn_no,order_no,delivery_note_no,supplier,item,price_per_item,item_code having sum(debit-quantity_ordered)>0 ORDER BY grn_no,order_no,delivery_note_no,item_code"));
} catch (Exception ex) {
System.err.println("the item error is " + ex);
}
}
}
} // GEN-LAST:event_perDNOTEComboBoxItemStateChanged
private void creditorsTableViewMouseClicked(
java.awt.event.MouseEvent evt) { // GEN-FIRST:event_creditorsTableViewMouseClicked
debtorbreakdown.setModel(
com.afrisoftech.dbadmin.TableModel.createTableVectors(
connectDB,
"select date, admission_no as Patient_No, upper(item) as Patient_name, invoice_no ||' '||receipt_no as Invoice_no,journal_no as Member_no,debit,credit,debit-credit as Balance, ' ' as Running_Balance "
+ " from ac_debtors where admission_no='"
+ creditorsTableView.getValueAt(creditorsTableView.getSelectedRow(), 0)
+ "' and"
+ " item='"
+ creditorsTableView.getValueAt(creditorsTableView.getSelectedRow(), 1)
+ "' and"
+ " account_no ilike '"
+ reportBodyTable.getValueAt(reportBodyTable.getSelectedRow(), 0)
+ "' AND date::date BETWEEN '"
+ com.afrisoftech.lib.SQLDateFormat.getSQLDate(beginDatePicker.getDate())
+ "' AND '"
+ com.afrisoftech.lib.SQLDateFormat.getSQLDate(endDatePicker.getDate())
+ "' group by 1,2,3,4,5,6,7,8 order by date,invoice_no"));
} // GEN-LAST:event_creditorsTableViewMouseClicked
private void reportBodyTableMouseClicked(
java.awt.event.MouseEvent evt) { // GEN-FIRST:event_reportBodyTableMouseClicked
this.setCursor(new java.awt.Cursor(java.awt.Cursor.WAIT_CURSOR));
totalAmounttxt.setVisible(true);
for (int t = 1; t < creditorsTableView.getModel().getRowCount(); t++) {
for (int c = 0; c < creditorsTableView.getModel().getColumnCount(); c++) {
creditorsTableView.getModel().setValueAt(null, t, c);
}
totalAmounttxt.setText(null);
}
try {
creditorsTableView.setModel(
com.afrisoftech.dbadmin.TableModel.createTableVectors(
connectDB,
""
+ " SELECT activity_code, service_type,sum(credit-debit) as Sales "
+ " FROM ac_ledger "
+ "where description ilike '%"
+ reportBodyTable.getValueAt(reportBodyTable.getSelectedRow(), 0)
+ "%' "
+ " and date::date BETWEEN '"
+ com.afrisoftech.lib.SQLDateFormat.getSQLDate(beginDatePicker.getDate())
+ "' "
+ "AND '"
+ com.afrisoftech.lib.SQLDateFormat.getSQLDate(endDatePicker.getDate())
+ "'"
+ " group by 1,2"));
} catch (Exception meza) {
System.out.println("the creditorsTableView populate table error is " + meza);
}
totalAmounttxt.setText(
com.afrisoftech.lib.CurrencyFormatter.getFormattedDouble(
(com.afrisoftech.lib.TableColumnTotal.getTableColumnTotal(creditorsTableView, 2))));
this.setCursor(new java.awt.Cursor(java.awt.Cursor.DEFAULT_CURSOR));
// TODO add your handling code here:
} // GEN-LAST:event_reportBodyTableMouseClicked
private Double populateBalances(String store) {
Double balance = 0.00;
try {
PreparedStatement pst =
connectDB.prepareStatement(
"select sum((receiving-issuing)*price) from st_sub_stores where store_name ilike '"
+ store
+ "' and trans_date::date <= '"
+ SQLDateFormat.getSQLDate(beginDatePicker.getDate())
+ "'");
ResultSet rset = pst.executeQuery();
while (rset.next()) {
balance = rset.getDouble(1);
}
} catch (SQLException ex) {
Exceptions.printStackTrace(ex);
}
return balance;
}
private Double populateitemBalances(String code) {
try {
PreparedStatement pst =
connectDB.prepareStatement(
"SELECT sum(qty)::NUMERIC(15,0) AS BALANCE FROM stock_balance_qty where department ilike '"
+ store
+ "' AND item_code ilike '"
+ code
+ "%' and dates::date <= '"
+ SQLDateFormat.getSQLDate(beginDatePicker.getDate())
+ "'");
ResultSet rset = pst.executeQuery();
while (rset.next()) {
bal = rset.getDouble(1);
}
} catch (SQLException ex) {
Exceptions.printStackTrace(ex);
}
return bal;
}