public java.lang.Object[] getListofStaffNos1(java.lang.Object InvoiceNo) {
java.lang.Object[] listofStaffNos = null;
java.util.Vector listStaffNoVector = new java.util.Vector(1, 1);
try {
java.sql.PreparedStatement pSet1 =
connectDB.prepareStatement(
"SELECT distinct upper(main_service) FROM hp_patient_card WHERE invoice_no = '"
+ InvoiceNo
+ "' and debit <> 0 and main_service is not null and service not ilike 'invoice%' order by upper(main_service)");
// pSet1.setString(1,beginDate.toString());
// pSet1.setString(2,endDate.toString());
java.sql.ResultSet rSet1 = pSet1.executeQuery();
while (rSet1.next()) {
listStaffNoVector.addElement(dbObject.getDBObject(rSet1.getObject(1).toString(), "-"));
}
} catch (java.sql.SQLException sqlExec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), sqlExec.getMessage());
}
listofStaffNos = listStaffNoVector.toArray();
System.out.println("Done list of Staff Nos ...");
return listofStaffNos;
}
public java.lang.Object[] getListofActivities() {
java.lang.Object[] listofActivities = null;
java.util.Vector listActVector = new java.util.Vector(1, 1);
try {
java.sql.Statement stmt1 = connectDB.createStatement();
java.sql.ResultSet rSet1 =
stmt1.executeQuery(
"SELECT DISTINCT order_no FROM st_orders WHERE date BETWEEN '"
+ beginDate
+ "' AND '"
+ endDate
+ "' ORDER BY order_no");
while (rSet1.next()) {
listActVector.addElement(rSet1.getObject(1).toString().toUpperCase());
}
} catch (java.sql.SQLException sqlExec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), sqlExec.getMessage());
}
listofActivities = listActVector.toArray();
System.out.println("Done list of activities ...");
return listofActivities;
}
public java.lang.Object[] getListofActivities() {
java.lang.Object[] listofActivities = null;
java.util.Vector listActVector = new java.util.Vector(1, 1);
try {
// java.sql.Connection connDB =
// java.sql.DriverManager.getConnection("jdbc:postgresql://localhost:5432/sako","postgres","pilsiner");
java.sql.Statement stmt1 = connectDB.createStatement();
java.sql.ResultSet rSet1 =
stmt1.executeQuery(
"SELECT DISTINCT mother_serial_no FROM rh.post_natal_services WHERE service_date::date BETWEEN '"
+ beginDate
+ "' AND '"
+ endDate
+ "'");
while (rSet1.next()) {
listActVector.addElement(rSet1.getObject(1).toString());
}
} catch (java.sql.SQLException sqlExec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), sqlExec.getMessage());
}
listofActivities = listActVector.toArray();
System.out.println("Done list of activities ...");
return listofActivities;
}
public java.lang.Object[] getDates() {
java.lang.Object[] listofDates = null;
java.util.Vector listofDatesVector = new java.util.Vector(1, 1);
try {
// java.sql.Connection connDB =
// java.sql.DriverManager.getConnection("jdbc:postgresql://localhost:5432/sako","postgres","pilsiner");
java.sql.Statement stmt1 = connectDB.createStatement();
java.sql.ResultSet rSet1 =
stmt1.executeQuery(
"SELECT DISTINCT category FROM st_stock_prices where department ilike '"
+ CashPoint
+ "' ORDER BY category");
while (rSet1.next()) {
listofDatesVector.addElement(rSet1.getObject(1).toString());
}
} catch (java.sql.SQLException sqlExec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), sqlExec.getMessage());
}
listofDates = listofDatesVector.toArray();
System.out.println("Done list of Staff Nos ...");
return listofDates;
}
public java.lang.Object[] getListofActivities1() {
java.lang.Object[] listofActivities1 = null;
java.util.Vector listActVector = new java.util.Vector(1, 1);
try {
java.sql.Statement stmt1 = connectDB.createStatement();
java.sql.ResultSet rSet1 =
stmt1.executeQuery(
"SELECT DISTINCT gl_code FROM ac_corporate_disallowables order by gl_code");
while (rSet1.next()) {
listActVector.addElement(rSet1.getObject(1).toString());
}
} catch (java.sql.SQLException sqlExec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), sqlExec.getMessage());
}
listofActivities1 = listActVector.toArray();
return listofActivities1;
}
public java.lang.Object[] getListofActivities2() {
java.lang.Object[] listofActivities2 = null;
java.util.Vector listActVector = new java.util.Vector(1, 1);
try {
// java.sql.Connection connDB =
// java.sql.DriverManager.getConnection("jdbc:postgresql://localhost:5432/insurance","postgres","pilsiner");
java.sql.Statement stmt1 = connectDB.createStatement();
java.sql.ResultSet rSet1 =
stmt1.executeQuery(
"SELECT DISTINCT gl_code FROM ac_asset_allocation where cost > depreciation");
while (rSet1.next()) {
listActVector.addElement(rSet1.getObject(1).toString());
}
} catch (java.sql.SQLException sqlExec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), sqlExec.getMessage());
}
listofActivities2 = listActVector.toArray();
System.out.println("Total list of activities = " + listofActivities2.length);
return listofActivities2;
}
public java.lang.Object[] getListofActivities() {
java.lang.Object[] listofActivities = null;
java.util.Vector listActVector = new java.util.Vector(1, 1);
try {
java.sql.Statement stmt1 = connectDB.createStatement();
java.sql.PreparedStatement pSet1 =
connectDB.prepareStatement("SELECT DISTINCT code FROM pb_activity order by code");
java.sql.ResultSet rSet1 = pSet1.executeQuery();
while (rSet1.next()) {
listActVector.addElement(rSet1.getObject(1).toString());
}
// System.out.println(rSet1.getObject(1).toString());
} catch (java.sql.SQLException sqlExec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), sqlExec.getMessage());
}
listofActivities = listActVector.toArray();
System.out.println("Done list of activities ...");
return listofActivities;
}
public java.lang.Object[] getListofActivities1() {
java.lang.Object[] listofActivities1 = null;
java.util.Vector listActVector1 = new java.util.Vector(1, 1);
try {
// java.sql.Connection connDB =
// java.sql.DriverManager.getConnection("jdbc:postgresql://localhost:5432/sako","postgres","pilsiner");
java.sql.Statement stmt1 = connectDB.createStatement();
java.sql.ResultSet rSet1 =
stmt1.executeQuery(
"SELECT DISTINCT fp_method FROM fprecord WHERE date_seen::date BETWEEN '"
+ beginDate
+ "' AND '"
+ endDate
+ "' AND fp_method NOT ILIKE 'Other%' order by fp_method");
while (rSet1.next()) {
listActVector1.addElement(rSet1.getObject(1).toString());
}
} catch (java.sql.SQLException sqlExec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), sqlExec.getMessage());
}
listofActivities1 = listActVector1.toArray();
System.out.println("Done list of activities ...");
return listofActivities1;
}
/*
* public java.lang.Object[] getListofActivities() {
*
* java.lang.Object[] listofActivities = null;
*
* java.util.Vector listActVector = new java.util.Vector(1,1);
*
*
* /* try {
*
* // java.sql.Connection connDB =
* java.sql.DriverManager.getConnection("jdbc:postgresql://localhost:5432/sako","postgres","pilsiner");
*
* java.sql.Statement stmt1 = connectDB.createStatement();
*
* java.sql.ResultSet rSet1 = stmt1.executeQuery("SELECT DISTINCT
* sub_cat_code FROM st_stock_item order by sub_cat_code");
*
* while (rSet1.next()) {
*
* listActVector.addElement(rSet1.getObject(1).toString());
*
* }
*
* }catch (java.sql.SQLException sqlExec) {
*
* javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(),
* sqlExec.getMessage());
*
* }
*
* listofActivities = listActVector.toArray(); System.out.println("Done list
* of activities ..."); return listofActivities; }
*/
public java.lang.Object[] getListofActivities1() {
java.lang.Object[] listofActivities1 = null;
java.util.Vector listActVector1 = new java.util.Vector(1, 1);
try {
// java.sql.Connection connDB =
// java.sql.DriverManager.getConnection("jdbc:postgresql://localhost:5432/sako","postgres","pilsiner");
java.sql.Statement stmt1 = connectDB.createStatement();
java.sql.ResultSet rSet1 =
stmt1.executeQuery(
"SELECT DISTINCT initcap(main_service) FROM pb_operating_parameters order by 1");
while (rSet1.next()) {
listActVector1.addElement(rSet1.getObject(1).toString());
}
} catch (java.sql.SQLException sqlExec) {
sqlExec.printStackTrace();
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), sqlExec.getMessage());
}
listofActivities1 = listActVector1.toArray();
System.out.println("Done list of activities ...");
return listofActivities1;
}
public java.lang.Object[] getListofStaffNos() {
java.lang.Object[] listofStaffNos = null;
java.util.Vector listStaffNoVector = new java.util.Vector(1, 1);
try {
// java.sql.Connection connDB =
// java.sql.DriverManager.getConnection("jdbc:postgresql://localhost:5432/sako","postgres","pilsiner");
java.sql.Statement stmt1 = connectDB.createStatement();
java.sql.ResultSet rSet1 =
stmt1.executeQuery(
"SELECT DISTINCT patient_no FROM hp_patient_card WHERE date::date = current_date AND payment_mode = 'Scheme' order by patient_no");
while (rSet1.next()) {
listStaffNoVector.addElement(rSet1.getObject(1).toString());
}
} catch (java.sql.SQLException sqlExec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), sqlExec.getMessage());
}
listofStaffNos = listStaffNoVector.toArray();
System.out.println("Done list of Staff Nos ...");
return listofStaffNos;
}
public java.lang.Object[] getListofActivities() {
java.lang.Object[] listofActivities = null;
java.util.Vector listActVector = new java.util.Vector(1, 1);
try {
// java.sql.Connection connDB =
// java.sql.DriverManager.getConnection("jdbc:postgresql://localhost:5432/sako","postgres","pilsiner");
java.sql.Statement stmt1 = connectDB.createStatement();
java.sql.PreparedStatement pSet1 =
connectDB.prepareStatement(
"SELECT DISTINCT code,activity FROM pb_activity where activity_category ilike 'I%' order by code");
java.sql.ResultSet rSet1 = pSet1.executeQuery();
while (rSet1.next()) {
System.out.println(rSet1.getObject(1).toString());
listActVector.addElement(rSet1.getObject(1).toString());
}
// System.out.println(rSet1.getObject(1).toString());
} catch (java.sql.SQLException sqlExec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), sqlExec.getMessage());
}
listofActivities = listActVector.toArray();
System.out.println("Done list of activities ...");
return listofActivities;
}
public java.lang.Object[] getListofActivities1() {
java.lang.Object[] listofActivities1 = null;
java.util.Vector listActVector1 = new java.util.Vector(1, 1);
try {
// java.sql.Connection connDB =
// java.sql.DriverManager.getConnection("jdbc:postgresql://localhost:5432/sako","postgres","pilsiner");
java.sql.Statement stmt1 = connectDB.createStatement();
java.sql.ResultSet rSet1 =
stmt1.executeQuery(
"SELECT distinct patient_no from hp_patient_card where date BETWEEN '"
+ beginDate
+ "' AND '"
+ endDate
+ "' and invoice_no NOT iLike 'O%' OR invoice_no NOT iLike 'I%'");
while (rSet1.next()) {
listActVector1.addElement(rSet1.getObject(1).toString());
}
} catch (java.sql.SQLException sqlExec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), sqlExec.getMessage());
}
listofActivities1 = listActVector1.toArray();
System.out.println("Done list of activities ...");
return listofActivities1;
}
public java.lang.Object[] getListofStaffNos() {
java.lang.Object[] listofStaffNos = null;
java.util.Vector listStaffNoVector = new java.util.Vector(1, 1);
/*
try {
// java.sql.Connection connDB = java.sql.DriverManager.getConnection("jdbc:postgresql://localhost:5432/sako","postgres","pilsiner");
java.sql.Statement stmt1 = connectDB.createStatement();
java.sql.ResultSet rSet1 = stmt1.executeQuery("SELECT DISTINCT admission_no FROM ac_debtors WHERE date::date BETWEEN '"+beginDate+"' AND '"+endDate+"' AND invoice_no IS NOT NULL and admission_no IS NOT NULL");
while (rSet1.next()) {
listStaffNoVector.addElement(rSet1.getObject(1).toString());
}
}catch (java.sql.SQLException sqlExec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), sqlExec.getMessage());
}
*/
listofStaffNos = listStaffNoVector.toArray();
System.out.println("Done list of Staff Nos ...");
return listofStaffNos;
}
public java.lang.Object[] getListofActivities() {
java.lang.Object[] listofActivities = null;
java.util.Vector listActVector = new java.util.Vector(1, 1);
try {
// java.sql.Connection connDB =
// java.sql.DriverManager.getConnection("jdbc:postgresql://localhost:5432/sako","postgres","pilsiner");
java.sql.Statement stmt1 = connectDB.createStatement();
// java.sql.PreparedStatement pSet1 = connectDB.prepareStatement("SELECT DISTINCT dealer FROM
// ac_debtors where date BETWEEN ? AND ? and dealer IS NOT NULL order by dealer");
java.sql.PreparedStatement pSet1 =
connectDB.prepareStatement(
"SELECT DISTINCT account_no,payee FROM ac_debtors where account_no IS NOT NULL and account_no ='"
+ memNo1
+ "' and balance > 0 order by account_no");
// pSet1.setString(1,beginDate.toString());
// pSet1.setString(2,endDate.toString());
// java.sql.ResultSet rSet1 = stmt1.executeQuery("SELECT DISTINCT dealer FROM ac_debtors where
// date BETWEEN '1-2-2003' AND '11-12-2003' and dealer IS NOT NULL order by dealer");
java.sql.ResultSet rSet1 = pSet1.executeQuery();
while (rSet1.next()) {
System.out.println(rSet1.getObject(1).toString());
listActVector.addElement(rSet1.getObject(1).toString());
}
// System.out.println(rSet1.getObject(1).toString());
} catch (java.sql.SQLException sqlExec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), sqlExec.getMessage());
}
listofActivities = listActVector.toArray();
System.out.println("Done list of activities ...");
return listofActivities;
}
public java.lang.Object[] getListofActivities() {
java.lang.Object[] listofActivities = null;
java.util.Vector listActVector = new java.util.Vector(1, 1);
try {
// java.sql.Connection connDB =
// java.sql.DriverManager.getConnection("jdbc:postgresql://localhost:5432/sako","postgres","pilsiner");
java.sql.Statement stmt1 = connectDB.createStatement();
// java.sql.ResultSet rSet1 = stmt1.executeQuery("SELECT DISTINCT activity_code FROM
// ac_cash_collection where date BETWEEN '"+beginDate+"' AND '"+endDate+"' AND
// transaction_type not ilike 'Bank%' EXCEPT select code from pb_activity where activity ilike
// 'Pharmacy%' ORDER BY activity_code");
java.sql.ResultSet rSet1 =
stmt1.executeQuery(
"SELECT DISTINCT ph.service_type FROM ac_ledger ph where ph.date BETWEEN '"
+ beginDate
+ "' AND '"
+ endDate
+ "' AND ph.activity_code = '"
+ gAccount
+ "' ORDER BY ph.service_type");
while (rSet1.next()) {
listActVector.addElement(rSet1.getObject(1).toString().toUpperCase());
}
} catch (java.sql.SQLException sqlExec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), sqlExec.getMessage());
}
listofActivities = listActVector.toArray();
System.out.println("Done list of activities ...");
return listofActivities;
}
public java.lang.Object[] getListofActivities() {
java.lang.Object[] listofActivities = null;
java.util.Vector listActVector = new java.util.Vector(1, 1);
try {
// java.sql.Connection connDB =
// java.sql.DriverManager.getConnection("jdbc:postgresql://localhost:5432/sako","postgres","pilsiner");
java.sql.Statement stmt1 = connectDB.createStatement();
java.sql.ResultSet rSet1 =
stmt1.executeQuery(
"SELECT DISTINCT PH.description FROM hp_pharmacy PH,ST_STOCK_PRICES ST where PH.date_prescribed BETWEEN '"
+ beginDate
+ "' AND '"
+ endDate
+ "' AND PH.main_service ilike '"
+ StoreName
+ "' AND PH.description ilike ST.PRODUCT AND PH.main_service ilike ST.DEPARTMENT ORDER BY PH.description");
// java.sql.ResultSet rSet1 = pSet1.executeQuery()
while (rSet1.next()) {
// if (rSet1.getFloat(1) > 0){
listActVector.addElement(rSet1.getObject(1).toString());
// }
}
// System.out.println("description"+rSet1.getObject(1).toString());
} catch (java.sql.SQLException sqlExec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), sqlExec.getMessage());
}
listofActivities = listActVector.toArray();
System.out.println("Done list of activities ...");
return listofActivities;
}
public java.lang.Object[] getListofStaffNos() {
java.lang.Object[] listofStaffNos = null;
java.util.Vector listStaffNoVector = new java.util.Vector(1, 1);
try {
java.sql.PreparedStatement stmt1 =
connectDB.prepareStatement(
"SELECT DISTINCT patient_no, name FROM hp_patient_visit where date::date BETWEEN '"
+ beginDate
+ "' AND '"
+ endDate
+ "' AND age::int >= 5 ORDER BY name ASC");
java.sql.ResultSet rSet1 = stmt1.executeQuery();
while (rSet1.next()) {
System.out.println("Patient names : [" + rSet1.getString(2) + "]");
listStaffNoVector.addElement(rSet1.getObject(1).toString());
}
} catch (java.sql.SQLException sqlExec) {
sqlExec.printStackTrace();
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), sqlExec.getMessage());
}
listofStaffNos = listStaffNoVector.toArray();
System.out.println("Done list of Staff Nos ...");
return listofStaffNos;
}
public java.lang.Object[] getListofStaffNos() {
java.lang.Object[] listofStaffNos = null;
java.util.Vector listStaffNoVector = new java.util.Vector(1, 1);
try {
java.sql.PreparedStatement pSet1 =
connectDB.prepareStatement(
"SELECT invoice_no FROM ac_debtors WHERE invoice_no BETWEEN ? AND ? AND transaction_type = ? AND payee != ? order by invoice_no");
pSet1.setString(1, INV01);
pSet1.setString(2, INV02);
pSet1.setString(3, "Raise Invoice");
pSet1.setString(4, "''");
java.sql.ResultSet rSet1 = pSet1.executeQuery();
// java.sql.Statement stmt1 = connectDB.createStatement();
// java.sql.ResultSet rSet1 = stmt1.executeQuery("SELECT distinct patient_no FROM
// hp_patient_card WHERE date::date BETWEEN '"+beginDate+"' AND '"+endDate+"' AND payment_mode
// = 'Scheme' order by patient_no");
while (rSet1.next()) {
listStaffNoVector.addElement(rSet1.getObject(1).toString());
}
} catch (java.sql.SQLException sqlExec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), sqlExec.getMessage());
}
listofStaffNos = listStaffNoVector.toArray();
System.out.println("Done list of Staff Nos ...");
return listofStaffNos;
}
public java.lang.Object[] getListofActivities() {
java.lang.Object[] listofActivities = null;
java.util.Vector listActVector = new java.util.Vector(1, 1);
try {
java.sql.Statement stmt1 = connectDB.createStatement();
if (balnc.equalsIgnoreCase("both")) {
java.sql.PreparedStatement pSet1 =
connectDB.prepareStatement(
"SELECT DISTINCT CASE WHEN (account_no IS NULL) THEN '-' ELSE account_no END AS account_no,sum(debit-credit) as balance,payee FROM ac_debtors WHERE date::date <= '"
+ endDate
+ "' and account_no is not null group by account_no,payee HAVING sum(debit-credit) <> 0 order by 3 ASC");
// java.sql.PreparedStatement pSet1 = connectDB.prepareStatement("SELECT DISTINCT
// account_no FROM debtors_account where account_no IS NOT NULL and account_no !='' and bal
// <> 0 order by account_no");
java.sql.ResultSet rSet1 = pSet1.executeQuery();
while (rSet1.next()) {
System.out.println(rSet1.getObject(1).toString());
listActVector.addElement(rSet1.getObject(1).toString());
}
} else {
if (balnc.equalsIgnoreCase("neg")) {
// java.sql.PreparedStatement pSet1 = connectDB.prepareStatement("SELECT DISTINCT
// account_no FROM debtors_account where account_no IS NOT NULL and account_no !='' and
// bal < 0 order by account_no");
java.sql.PreparedStatement pSet1 =
connectDB.prepareStatement(
"SELECT DISTINCT CASE WHEN (account_no IS NULL) THEN '-' ELSE account_no END AS account_no,sum(debit-credit) as balance FROM ac_debtors WHERE date::date <= '"
+ endDate
+ "' group by account_no HAVING sum(debit-credit) < 0 order by 2 desc");
java.sql.ResultSet rSet1 = pSet1.executeQuery();
while (rSet1.next()) {
System.out.println(rSet1.getObject(1).toString());
listActVector.addElement(rSet1.getObject(1).toString());
}
} else {
java.sql.PreparedStatement pSet1 =
connectDB.prepareStatement(
"SELECT DISTINCT CASE WHEN (account_no IS NULL) THEN '-' ELSE account_no END AS account_no,sum(debit-credit) as balance FROM ac_debtors WHERE date::date <= '"
+ endDate
+ "' group by account_no HAVING sum(debit-credit) > 0 order by 2 desc");
// java.sql.PreparedStatement pSet1 = connectDB.prepareStatement("SELECT DISTINCT
// account_no FROM debtors_account where account_no IS NOT NULL and account_no !='' and
// bal > 0 order by account_no");
java.sql.ResultSet rSet1 = pSet1.executeQuery();
while (rSet1.next()) {
System.out.println(rSet1.getObject(1).toString());
listActVector.addElement(rSet1.getObject(1).toString());
}
}
}
} catch (java.sql.SQLException sqlExec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), sqlExec.getMessage());
}
listofActivities = listActVector.toArray();
System.out.println("Done list of activities ...");
return listofActivities;
}
