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
示例#2
0
 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;
  }