public void generatePdf(java.lang.String memNo) {
java.lang.Process wait_for_Pdf2Show;
java.util.Calendar cal = java.util.Calendar.getInstance();
java.util.Date dateStampPdf = cal.getTime();
java.lang.String pdfDateStamp = dateStampPdf.toString();
try {
java.io.File tempFile =
java.io.File.createTempFile("REP" + this.getDateLable() + "_", ".pdf");
tempFile.deleteOnExit();
java.lang.Runtime rt = java.lang.Runtime.getRuntime();
java.lang.String debitTotal = null;
java.lang.String creditTotal = null;
// com.lowagie.text.Document docPdf = new com.lowagie.text.Document();
com.lowagie.text.Document docPdf = new com.lowagie.text.Document(PageSize.A4.rotate());
double osBalance1 = 0.00;
double osBalance = 0.00;
double current = 0.00;
double osBalancebf = 0.00;
try {
try {
com.lowagie.text.pdf.PdfWriter.getInstance(
docPdf, new java.io.FileOutputStream(tempFile));
String compName = null;
String date = null;
String Messg = null;
docPdf.open();
try {
java.util.Calendar calendar = java.util.Calendar.getInstance();
long dateNow = calendar.getTimeInMillis();
java.sql.Date datenowSql = new java.sql.Date(dateNow);
System.out.println(datenowSql.toString());
java.lang.Object listofStaffNos[] = this.getListofStaffNos();
com.lowagie.text.pdf.PdfPTable table1 = new com.lowagie.text.pdf.PdfPTable(7);
// com.lowagie.text.Table table = new com.lowagie.text.Table(7);
// table.endHeaders();
int headerwidths[] = {15, 15, 30, 15, 15, 15, 15};
table1.setWidths(headerwidths);
// if (docPdf.getPageNumber() > 1) {
// table1.setHeaderRows(4);
// }
table1.setWidthPercentage((100));
table1.getDefaultCell().setBorder(Rectangle.BOTTOM);
table1.getDefaultCell().setColspan(7);
Phrase phrase = new Phrase();
// table.addCell(phrase);
table1.getDefaultCell().setColspan(1);
table1.getDefaultCell().setBackgroundColor(java.awt.Color.WHITE);
table1.getDefaultCell().setBorderColor(java.awt.Color.WHITE);
try {
java.sql.Statement st3 = connectDB.createStatement();
java.sql.Statement st2x = connectDB.createStatement();
java.sql.ResultSet rset2x =
st2x.executeQuery("SELECT rep_currency from pb_hospitalprofile");
while (rset2x.next()) {
ks = rset2x.getObject(1).toString();
}
java.sql.ResultSet rset3 = st3.executeQuery("select header_name from pb_header");
while (rset3.next()) {
table1.getDefaultCell().setColspan(7);
table1.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_CENTER);
phrase = new Phrase(rset3.getObject(1).toString(), pFontHeader11);
table1.addCell(phrase);
}
} catch (java.sql.SQLException SqlExec) {
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), SqlExec.getMessage());
}
docPdf.add(table1);
} catch (com.lowagie.text.BadElementException BadElExec) {
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), BadElExec.getMessage());
}
// } catch(java.io.FileNotFoundException fnfExec) {
// javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(),
// fnfExec.getMessage());
/// }
try {
java.util.Calendar calendar = java.util.Calendar.getInstance();
long dateNow = calendar.getTimeInMillis();
java.sql.Date datenowSql = new java.sql.Date(dateNow);
System.out.println(datenowSql.toString());
java.lang.Object listofStaffNos[] = this.getListofStaffNos();
com.lowagie.text.pdf.PdfPTable table = new com.lowagie.text.pdf.PdfPTable(9);
// com.lowagie.text.Table table = new com.lowagie.text.Table(7);
// table.endHeaders();
int headerwidths[] = {12, 10, 28, 11, 14, 12, 14, 14, 15};
table.setWidths(headerwidths);
// if (docPdf.getPageNumber() > 1) {
table.setHeaderRows(8);
// }
table.setWidthPercentage((100));
table.getDefaultCell().setBorder(Rectangle.BOTTOM);
table.getDefaultCell().setColspan(9);
Phrase phrase = new Phrase();
// table.addCell(phrase);
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setBackgroundColor(java.awt.Color.WHITE);
table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);
try {
double Debit = 0.00;
double Credit = 0.00;
java.sql.Statement st22 = connectDB.createStatement();
java.sql.Statement st11 = connectDB.createStatement();
java.sql.Statement st = connectDB.createStatement();
java.sql.Statement st1 = connectDB.createStatement();
java.sql.Statement st2 = connectDB.createStatement();
java.sql.Statement st3 = connectDB.createStatement();
java.sql.Statement st4 = connectDB.createStatement();
java.sql.Statement st41 = connectDB.createStatement();
java.sql.Statement st5 = connectDB.createStatement();
java.sql.Statement st22D = connectDB.createStatement();
java.sql.ResultSet rset3 =
st3.executeQuery(
"select hospital_name,postal_code||' '||box_no||' '||town,main_telno||' '||other_telno,initcap(street),main_faxno,email,website,room_no from pb_hospitalprofile");
java.sql.ResultSet rset4 =
st4.executeQuery(
"select scheme_name,account_no,tel_main,address from ac_schemes where account_no = '"
+ memNo
+ "'");
java.sql.ResultSet rset41 =
st41.executeQuery(
"select payer_name from ac_schemes where account_no = '" + memNo + "'");
// java.sql.ResultSet rset1 = st1.executeQuery("select date,admission_no||'
// '||cheque_no, upper(item),invoice_no ||'
// '||receipt_no,debit,credit,credit_bal,balance from ac_debtors where account_no = '"
// + memNo + "' AND date::date BETWEEN '" + beginDate + "' AND '" + endDate + "' group
// by 1, 2, 3order by date,invoice_no");// UNION select
// pd.date::date,initcap(pd.scheme_staff_no), (sh.first_name||' '||sh.second_name||'
// '||sh.last_name) as name,pd.reference,sum(pd.credit),pd.patient_no from
// hp_patient_card pd,hp_inpatient_register sh where pd.patient_no = sh.patient_no and
// pd.isurer = '"+memNo+"' AND pd.date::date BETWEEN '"+beginDate+"' AND '"+endDate+"'
// and pd.credit > 0 group by pd.date::date,pd.scheme_staff_no,
// name,pd.reference,pd.invoice_no,pd.patient_no order by pd.invoice_no");
java.sql.ResultSet rsetTotals2 =
st22.executeQuery(
"select sum(debit - credit) from ac_debtors where account_no = '"
+ memNo
+ "' AND date::date < '"
+ beginDate
+ "'");
java.sql.ResultSet rsetTotals =
st2.executeQuery(
"select sum(debit),sum(credit) from ac_debtors where account_no = '"
+ memNo
+ "' AND date::date BETWEEN '"
+ beginDate
+ "' AND '"
+ endDate
+ "'");
table.getDefaultCell().setBorderColor(java.awt.Color.BLACK);
table.getDefaultCell().setBorderWidth(Rectangle.TOP | Rectangle.BOTTOM);
table.getDefaultCell().setColspan(9);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_CENTER);
phrase = new Phrase("Statement of Account", pFontHeader11);
table.addCell(phrase);
while (rset4.next()) {
table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);
table.getDefaultCell().setColspan(9);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase(rset4.getObject(1).toString(), pFontHeader1);
table.addCell(phrase);
while (rset41.next()) {
table.getDefaultCell().setColspan(9);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase(rset41.getString(1), pFontHeader1);
table.addCell(phrase);
}
table.getDefaultCell().setColspan(9);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase =
new Phrase(
"Account No : " + rset4.getObject(2).toString().toUpperCase(),
pFontHeader1);
table.addCell(phrase);
phrase =
new Phrase(
dbObject.getDBObject("Tel : " + rset4.getObject(3), "-"), pFontHeader1);
table.addCell(phrase);
phrase = new Phrase(dbObject.getDBObject(rset4.getObject(4), "-"), pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setColspan(6);
}
try {
java.text.DateFormat dateFormat =
java.text.DateFormat.getDateInstance(java.text.DateFormat.MEDIUM); // MEDIUM);
java.util.Date endDate1 =
dateFormat.parse(endDate.toLocaleString()); // dateInstance.toLocaleString());
java.util.Date endDate11 =
dateFormat.parse(beginDate.toLocaleString()); // dateInstance.toLocaleString());
System.out.println("" + endDate1);
// phrase = new Phrase(bank +" Report: " +dateFormat.format(formattedDate),
// pFontHeader);
// table.addCell(phrase);
phrase =
new Phrase(
"Period : "
+ dateFormat.format(endDate11)
+ " - "
+ dateFormat.format(endDate1),
pFontHeader1);
table.addCell(phrase);
} catch (java.text.ParseException psExec) {
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), psExec.getMessage());
}
// table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
// phrase = new Phrase("Period : '"+beginDate+"' - '"+endDate+"'", pFontHeader1);
// table.addCell(phrase);
table.getDefaultCell().setColspan(3);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase("Date " + datenowSql, pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setColspan(3);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
table.getDefaultCell().setBorderColor(java.awt.Color.BLACK);
table.getDefaultCell().setBorderWidth(Rectangle.TOP);
table.getDefaultCell().setColspan(1);
// phrase = new Phrase("Date", pFontHeader1);
// table.addCell(phrase);
table.getDefaultCell().setColspan(1);
phrase = new Phrase("Staff No.", pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setColspan(2);
phrase = new Phrase(" Staff Name", pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setColspan(1);
phrase = new Phrase("", pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
table.getDefaultCell().setColspan(1);
phrase = new Phrase("", pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setColspan(1);
phrase = new Phrase("", pFontHeader1);
table.addCell(phrase);
phrase = new Phrase("", pFontHeader1);
table.addCell(phrase);
phrase = new Phrase("Debit Bal. ", pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setColspan(1);
phrase = new Phrase("Running Amt. " + ks, pFontHeader1);
table.addCell(phrase);
while (rsetTotals2.next()) {
table.getDefaultCell().setColspan(5);
table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase("BAL/BF", pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
table.getDefaultCell().setColspan(4);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(rsetTotals2.getString(1)),
pFontHeader1);
osBalancebf = osBalancebf + rsetTotals2.getDouble(1);
table.addCell(phrase);
}
for (int i = 0; i < listofStaffNos.length; i++) {
java.sql.ResultSet rset1 =
st1.executeQuery(
"select admission_no, upper(item), sum(debit - credit) from ac_debtors where account_no = '"
+ memNo
+ "' and admission_no = '"
+ listofStaffNos[i]
+ "' AND date::date BETWEEN '"
+ beginDate
+ "' AND '"
+ endDate
+ "' GROUP BY 1, 2");
while (rset1.next()) {
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase(dbObject.getDBObject(rset1.getObject(1), "-"), pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(6);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase(dbObject.getDBObject(rset1.getObject(2), "-"), pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
// phrase = new Phrase(dbObject.getDBObject(rset1.getObject(3), "-"),
// pFontHeader);
// table.addCell(phrase);
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
// phrase = new Phrase(dbObject.getDBObject(rset1.getObject(4), "-"),
// pFontHeader);
// table.addCell(phrase);
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
// phrase = new Phrase(new
// com.afrisoftech.sys.Format2Currency().Format2Currency(rset1.getString(5)),
// pFontHeader);
// Debit = Debit + rset1.getDouble(5);
// table.addCell(phrase);
// phrase = new Phrase(new
// com.afrisoftech.sys.Format2Currency().Format2Currency(rset1.getString(6)),
// pFontHeader);
// Credit = Credit + rset1.getDouble(6);
// table.addCell(phrase);
// phrase = new Phrase(new
// com.afrisoftech.sys.Format2Currency().Format2Currency(rset1.getString(7)),
// pFontHeader);
// osBalance1 = osBalance1 + rset1.getDouble(6);
// table.addCell(phrase);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(rset1.getString(3)),
pFontHeader);
osBalancebf = osBalancebf + rset1.getDouble(3);
// osBalancebf = osBalance1;
table.addCell(phrase);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(osBalancebf - osBalance1)),
pFontHeader);
table.addCell(phrase);
}
}
table.getDefaultCell().setBorderColor(java.awt.Color.BLACK);
table.getDefaultCell().setBorder(Rectangle.BOTTOM | Rectangle.TOP);
// while (rsetTotals.next()) {
table.getDefaultCell().setColspan(4);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase("Total", pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase = new Phrase("", pFontHeader);
table.addCell(phrase);
phrase = new Phrase("", pFontHeader);
table.addCell(phrase);
phrase = new Phrase("", pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(2);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(osBalancebf - osBalance1)),
pFontHeader);
table.addCell(phrase);
// phrase = new Phrase(" ");
// }
try {
java.sql.Statement st31 = connectDB.createStatement();
// java.sql.Statement st4 = connectDB.createStatement();
java.sql.ResultSet rset2 = st31.executeQuery("select name from pb_notice");
// java.sql.ResultSet rset2 = st3.executeQuery("SELECT hospital_name from
// pb_hospitalprofile");
// java.sql.ResultSet rset4 = st4.executeQuery("SELECT date('now') as Date");
while (rset2.next()) {
Messg = rset2.getString(1);
}
com.lowagie.text.HeaderFooter footer =
new com.lowagie.text.HeaderFooter(
new Phrase("" + Messg + ""),
false); // FontFactory.getFont(com.lowagie.text.FontFactory.HELVETICA, 14,
// Font.BOLDITALIC,java.awt.Color.blue)));
// com.lowagie.text.HeaderFooter headerFoter = new
// com.lowagie.text.HeaderFooter(new Phrase(""+compName+""),false);//
// FontFactory.getFont(com.lowagie.text.FontFactory.HELVETICA, 14,
// Font.BOLDITALIC,java.awt.Color.blue)));
// headerFoter.ALIGN_CENTER;
// headerFoter.setRight(5);
docPdf.setFooter(footer);
} catch (java.sql.SQLException SqlExec) {
SqlExec.printStackTrace();
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), SqlExec.getMessage());
}
docPdf.add(table);
} catch (java.sql.SQLException SqlExec) {
SqlExec.printStackTrace();
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), SqlExec.getMessage());
}
// } // }
} catch (com.lowagie.text.BadElementException BadElExec) {
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), BadElExec.getMessage());
}
} catch (java.io.FileNotFoundException fnfExec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), fnfExec.getMessage());
}
} catch (com.lowagie.text.DocumentException lwDocexec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), lwDocexec.getMessage());
}
try {
if (System.getProperty("os.name").equalsIgnoreCase("Linux")) {
System.out.println(tempFile);
wait_for_Pdf2Show = rt.exec("xpdf " + tempFile + "");
wait_for_Pdf2Show.waitFor();
} else {
wait_for_Pdf2Show =
rt.exec("c:/Program Files/Adobe/Acrobat 5.0/Reader/AcroRd32.exe " + tempFile);
wait_for_Pdf2Show.waitFor();
}
} catch (java.lang.InterruptedException intrExec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), intrExec.getMessage());
}
} catch (java.io.IOException IOexec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), IOexec.getMessage());
}
}
public void generatePdf(java.lang.String memNo) {
java.lang.Process wait_for_Pdf2Show;
java.util.Calendar cal = java.util.Calendar.getInstance();
java.util.Date dateStampPdf = cal.getTime();
java.lang.String pdfDateStamp = dateStampPdf.toString();
try {
java.io.File tempFile =
java.io.File.createTempFile("REP" + this.getDateLable() + "_", ".pdf");
tempFile.deleteOnExit();
java.lang.Runtime rt = java.lang.Runtime.getRuntime();
java.lang.String debitTotal = null;
java.lang.String creditTotal = null;
com.lowagie.text.Document docPdf = new com.lowagie.text.Document();
double osBalance1 = 0.00;
double osBalance = 0.00;
double current = 0.00;
double osBalancebf = 0.00;
try {
try {
com.lowagie.text.pdf.PdfWriter.getInstance(
docPdf, new java.io.FileOutputStream(tempFile));
String compName = null;
String date = null;
String Messg = null;
com.lowagie.text.HeaderFooter footer =
new com.lowagie.text.HeaderFooter(
new Phrase("Allocation - Page: ", pFontHeader),
true); // FontFactory.getFont(com.lowagie.text.FontFactory.HELVETICA, 12,
// Font.BOLDITALIC,java.awt.Color.blue));
docPdf.setFooter(footer);
docPdf.open();
try {
java.util.Calendar calendar = java.util.Calendar.getInstance();
long dateNow = calendar.getTimeInMillis();
java.sql.Date datenowSql = new java.sql.Date(dateNow);
System.out.println(datenowSql.toString());
java.lang.Object listofStaffNos[] = this.getListofStaffNos();
com.lowagie.text.pdf.PdfPTable table1 = new com.lowagie.text.pdf.PdfPTable(6);
// com.lowagie.text.Table table = new com.lowagie.text.Table(7);
// table.endHeaders();
int headerwidths[] = {15, 15, 30, 15, 15, 15};
table1.setWidths(headerwidths);
// if (docPdf.getPageNumber() > 1) {
// table1.setHeaderRows(4);
// }
table1.setWidthPercentage((100));
table1.getDefaultCell().setBorder(Rectangle.BOTTOM);
table1.getDefaultCell().setColspan(7);
Phrase phrase = new Phrase();
// table.addCell(phrase);
table1.getDefaultCell().setColspan(1);
table1.getDefaultCell().setBackgroundColor(java.awt.Color.WHITE);
table1.getDefaultCell().setBorderColor(java.awt.Color.WHITE);
try {
java.sql.Statement st3 = connectDB.createStatement();
java.sql.ResultSet rset3 = st3.executeQuery("select header_name from pb_header");
while (rset3.next()) table1.getDefaultCell().setColspan(6);
table1.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_CENTER);
phrase = new Phrase(rset3.getObject(1).toString(), pFontHeader11);
table1.addCell(phrase);
} catch (java.sql.SQLException SqlExec) {
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), SqlExec.getMessage());
}
docPdf.add(table1);
} catch (com.lowagie.text.BadElementException BadElExec) {
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), BadElExec.getMessage());
}
// } catch(java.io.FileNotFoundException fnfExec) {
// javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(),
// fnfExec.getMessage());
/// }
try {
java.util.Calendar calendar = java.util.Calendar.getInstance();
long dateNow = calendar.getTimeInMillis();
java.sql.Date datenowSql = new java.sql.Date(dateNow);
System.out.println(datenowSql.toString());
java.lang.Object listofStaffNos[] = this.getListofStaffNos();
com.lowagie.text.pdf.PdfPTable table = new com.lowagie.text.pdf.PdfPTable(10);
// com.lowagie.text.Table table = new com.lowagie.text.Table(7);
// table.endHeaders();
int headerwidths[] = {12, 10, 23, 10, 12, 13, 8, 11, 10, 8};
table.setWidths(headerwidths);
// if (docPdf.getPageNumber() > 1) {
table.setHeaderRows(10);
// }
table.setWidthPercentage((100));
table.getDefaultCell().setBorder(Rectangle.BOTTOM);
table.getDefaultCell().setColspan(10);
Phrase phrase = new Phrase();
// table.addCell(phrase);
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setBackgroundColor(java.awt.Color.WHITE);
table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);
try {
java.sql.Statement st22 = connectDB.createStatement();
java.sql.Statement st11 = connectDB.createStatement();
java.sql.Statement st = connectDB.createStatement();
java.sql.Statement st1 = connectDB.createStatement();
java.sql.Statement st2 = connectDB.createStatement();
java.sql.Statement st3 = connectDB.createStatement();
java.sql.Statement st4 = connectDB.createStatement();
java.sql.Statement st41 = connectDB.createStatement();
java.sql.Statement st5 = connectDB.createStatement();
// java.sql.ResultSet rset3 = st3.executeQuery("select hospital_name,postal_code||'
// '||box_no||' '||town,main_telno||'
// '||other_telno,initcap(street),main_faxno,email,website,room_no from
// pb_hospitalprofile");
// java.sql.ResultSet rset4 = st4.executeQuery("select scheme_manager from
// ac_scheme_providers where scheme_manager = '"+memNo+"'");
java.sql.ResultSet rset4 =
st4.executeQuery(
"select scheme_name,account_no from ac_schemes where account_no = '"
+ memNo
+ "'");
java.sql.ResultSet rset41 =
st41.executeQuery(
"select payer_name from ac_schemes where account_no = '" + memNo + "'");
// java.sql.ResultSet rset1 = st1.executeQuery("select ac.date,pc.scheme_staff_no,
// ac.payee,ac.reference_no,sum(ac.debit),ac.admission_no from hp_patient_card
// pc,ac_debtors ac where pc.patient_no = ac.admission_no and ac.dealer = '"+memNo+"'
// and ac.dealer = pc.scheme AND ac.date::date BETWEEN '"+beginDate+"' AND
// '"+endDate+"' and ac.debit > 0 group by ac.date,pc.scheme_staff_no,
// ac.payee,ac.reference_no,ac.admission_no");// UNION select
// pd.date::date,initcap(pd.scheme_staff_no), (sh.first_name||' '||sh.second_name||'
// '||sh.last_name) as name,pd.reference,sum(pd.credit),pd.patient_no from
// hp_patient_card pd,hp_inpatient_register sh where pd.patient_no = sh.patient_no and
// pd.isurer = '"+memNo+"' AND pd.date::date BETWEEN '"+beginDate+"' AND '"+endDate+"'
// and pd.credit > 0 group by pd.date::date,pd.scheme_staff_no,
// name,pd.reference,pd.invoice_no,pd.patient_no order by pd.invoice_no");
java.sql.ResultSet rset1 =
st1.executeQuery(
"select al.date,db.admission_no,db.item,al.inv_no,al.inv_date,db.debit, al.cheque_no,al.amount,al.amount_alloc,(db.debit-al.amount_alloc) from db_allocation al,ac_debtors db where al.acc_no = '"
+ memNo
+ "' and al.inv_no = db.invoice_no AND al.date::date BETWEEN '"
+ beginDate
+ "' AND '"
+ endDate
+ "' order by al.date"); // UNION select
// pd.date::date,initcap(pd.scheme_staff_no),
// (sh.first_name||' '||sh.second_name||'
// '||sh.last_name) as
// name,pd.reference,sum(pd.credit),pd.patient_no
// from hp_patient_card pd,hp_inpatient_register
// sh where pd.patient_no = sh.patient_no and
// pd.isurer = '"+memNo+"' AND pd.date::date
// BETWEEN '"+beginDate+"' AND '"+endDate+"' and
// pd.credit > 0 group by
// pd.date::date,pd.scheme_staff_no,
// name,pd.reference,pd.invoice_no,pd.patient_no
// order by pd.invoice_no");
// java.sql.ResultSet rsetTotals2 = st22.executeQuery("select sum(debit),sum(balance)
// from ac_debtors where account_no = '"+memNo+"' AND date::date < '"+beginDate+"'");
// java.sql.ResultSet rset1 = st1.executeQuery("select
// pc.date::date,pc.scheme_staff_no, initcap(sh.first_name||' '||sh.second_name||'
// '||sh.last_name) as name,pc.reference,sum(pc.credit),pc.patient_no from
// hp_patient_card pc,hp_patient_register sh where pc.scheme_staff_no = sh.account_no
// and pc.isurer = '"+memNo+"' AND pc.date::date BETWEEN '"+beginDate+"' AND
// '"+endDate+"' and pc.credit > 0 group by pc.date::date,pc.scheme_staff_no,
// name,pc.reference,pc.invoice_no,pc.patient_no");// UNION select
// pd.date::date,initcap(pd.scheme_staff_no), (sh.first_name||' '||sh.second_name||'
// '||sh.last_name) as name,pd.reference,sum(pd.credit),pd.patient_no from
// hp_patient_card pd,hp_inpatient_register sh where pd.patient_no = sh.patient_no and
// pd.isurer = '"+memNo+"' AND pd.date::date BETWEEN '"+beginDate+"' AND '"+endDate+"'
// and pd.credit > 0 group by pd.date::date,pd.scheme_staff_no,
// name,pd.reference,pd.invoice_no,pd.patient_no order by pd.invoice_no");
// java.sql.ResultSet rsetTotals = st2.executeQuery("select
// sum(amount),sum(amount_alloc) from db_allocation where acc_no = '"+memNo+"' AND
// date::date BETWEEN '"+beginDate+"' AND '"+endDate+"' and acc_no = '"+memNo+"'");
table.getDefaultCell().setBorderColor(java.awt.Color.BLACK);
table.getDefaultCell().setBorderWidth(Rectangle.TOP | Rectangle.BOTTOM);
table.getDefaultCell().setColspan(10);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_CENTER);
phrase = new Phrase("Payment Allocation Report", pFontHeader11);
table.addCell(phrase);
while (rset4.next()) table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);
table.getDefaultCell().setColspan(10);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase(rset4.getObject(1).toString(), pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setColspan(10);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase("Account No : " + rset4.getObject(2).toString(), pFontHeader1);
table.addCell(phrase);
while (rset41.next()) table.getDefaultCell().setColspan(10);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase(rset41.getString(1), pFontHeader1);
table.addCell(phrase);
// while (rset4.next())
table.getDefaultCell().setColspan(5);
try {
java.text.DateFormat dateFormat =
java.text.DateFormat.getDateInstance(java.text.DateFormat.MEDIUM); // MEDIUM);
java.util.Date endDate1 =
dateFormat.parse(endDate.toLocaleString()); // dateInstance.toLocaleString());
java.util.Date endDate11 =
dateFormat.parse(beginDate.toLocaleString()); // dateInstance.toLocaleString());
System.out.println("" + endDate1);
// phrase = new Phrase(bank +" Report: " +dateFormat.format(formattedDate),
// pFontHeader);
// table.addCell(phrase);
table.getDefaultCell().setColspan(6);
phrase =
new Phrase(
"Period : "
+ dateFormat.format(endDate11)
+ " - "
+ dateFormat.format(endDate1),
pFontHeader1);
table.addCell(phrase);
} catch (java.text.ParseException psExec) {
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), psExec.getMessage());
}
// table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
// phrase = new Phrase("Period : '"+beginDate+"' - '"+endDate+"'", pFontHeader1);
// table.addCell(phrase);
table.getDefaultCell().setColspan(4);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase(" Date Printed " + datenowSql, pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setBorderColor(java.awt.Color.BLACK);
table.getDefaultCell().setBorder(Rectangle.BOTTOM | Rectangle.TOP);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
// table.getDefaultCell().setBorderColor(java.awt.Color.BLACK);
table.getDefaultCell().setColspan(1);
phrase = new Phrase("Date", pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setColspan(1);
phrase = new Phrase("Patient No.", pFontHeader1);
table.addCell(phrase);
phrase = new Phrase("Patient Name", pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setColspan(1);
phrase = new Phrase("Inv. No.", pFontHeader1);
table.addCell(phrase);
phrase = new Phrase("Inv.Date", pFontHeader1);
table.addCell(phrase);
phrase = new Phrase("Inv. Amt.", pFontHeader1);
table.addCell(phrase);
phrase = new Phrase("Chq No. ", pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setColspan(1);
phrase = new Phrase("Chq. Amt. ", pFontHeader1);
table.addCell(phrase);
phrase = new Phrase("Amt.Alloc ", pFontHeader1);
table.addCell(phrase);
phrase = new Phrase("Inv. Bal. ", pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setBorderWidth(Rectangle.BOTTOM);
// table
while (rset1.next()) {
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase(dbObject.getDBObject(rset1.getObject(1), "-"), pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase(dbObject.getDBObject(rset1.getObject(2), "-"), pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase(dbObject.getDBObject(rset1.getObject(3), "-"), pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase(dbObject.getDBObject(rset1.getObject(4), "-"), pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase(dbObject.getDBObject(rset1.getObject(5), "-"), pFontHeader);
table.addCell(phrase);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(rset1.getString(6)),
pFontHeader);
// osBalance1 = osBalance1 + rset1.getDouble(5);
table.addCell(phrase);
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase(dbObject.getDBObject(rset1.getObject(7), "-"), pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(rset1.getString(8)),
pFontHeader);
// osBalance1 = osBalance1 + rset1.getDouble(5);
table.addCell(phrase);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(rset1.getString(9)),
pFontHeader);
osBalancebf = osBalancebf + rset1.getDouble(9);
// osBalancebf = osBalance1;
table.addCell(phrase);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(rset1.getString(10)),
pFontHeader);
osBalance1 = osBalance1 + rset1.getDouble(10);
table.addCell(phrase);
}
table.getDefaultCell().setColspan(3);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase = new Phrase("", pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setBorderColor(java.awt.Color.BLACK);
table.getDefaultCell().setBorder(Rectangle.BOTTOM | Rectangle.TOP);
// while (rsetTotals.next()) {
table.getDefaultCell().setColspan(5);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase("Total", pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(osBalancebf)),
pFontHeader);
table.addCell(phrase);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(osBalance1)),
pFontHeader);
table.addCell(phrase);
// phrase = new Phrase(" ");
// }
/* try {
java.sql.Statement st31 = connectDB.createStatement();
// java.sql.Statement st4 = connectDB.createStatement();
java.sql.ResultSet rset2 = st31.executeQuery("select name from pb_notice");
// java.sql.ResultSet rset2 = st3.executeQuery("SELECT hospital_name from pb_hospitalprofile");
// java.sql.ResultSet rset4 = st4.executeQuery("SELECT date('now') as Date");
while(rset2.next())
Messg = rset2.getString(1);
com.lowagie.text.HeaderFooter footer = new com.lowagie.text.HeaderFooter(new Phrase(""+Messg+""),false);// FontFactory.getFont(com.lowagie.text.FontFactory.HELVETICA, 14, Font.BOLDITALIC,java.awt.Color.blue)));
// com.lowagie.text.HeaderFooter headerFoter = new com.lowagie.text.HeaderFooter(new Phrase(""+compName+""),false);// FontFactory.getFont(com.lowagie.text.FontFactory.HELVETICA, 14, Font.BOLDITALIC,java.awt.Color.blue)));
// headerFoter.ALIGN_CENTER;
// headerFoter.setRight(5);
docPdf.setFooter(footer);
} catch(java.sql.SQLException SqlExec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), SqlExec.getMessage());
}
*/
docPdf.add(table);
} catch (java.sql.SQLException SqlExec) {
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), SqlExec.getMessage());
}
// } // }
} catch (com.lowagie.text.BadElementException BadElExec) {
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), BadElExec.getMessage());
}
} catch (java.io.FileNotFoundException fnfExec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), fnfExec.getMessage());
}
} catch (com.lowagie.text.DocumentException lwDocexec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), lwDocexec.getMessage());
}
docPdf.close();
try {
if (System.getProperty("os.name").equalsIgnoreCase("Linux")) {
System.out.println(tempFile);
wait_for_Pdf2Show = rt.exec("kghostview " + tempFile + "");
wait_for_Pdf2Show.waitFor();
} else {
wait_for_Pdf2Show =
rt.exec("c:/Program Files/Adobe/Acrobat 5.0/Reader/AcroRd32.exe " + tempFile);
wait_for_Pdf2Show.waitFor();
}
} catch (java.lang.InterruptedException intrExec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), intrExec.getMessage());
}
} catch (java.io.IOException IOexec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), IOexec.getMessage());
}
}
public void generatePdf(java.lang.String memNo) {
java.lang.Process wait_for_Pdf2Show;
java.util.Calendar cal = java.util.Calendar.getInstance();
java.util.Date dateStampPdf = cal.getTime();
java.lang.String pdfDateStamp = dateStampPdf.toString();
try {
java.io.File tempFile =
java.io.File.createTempFile("REP" + this.getDateLable() + "_", ".pdf");
tempFile.deleteOnExit();
java.lang.Runtime rt = java.lang.Runtime.getRuntime();
java.lang.String debitTotal = null;
java.lang.String creditTotal = null;
com.lowagie.text.Document docPdf = new com.lowagie.text.Document();
double osBalanceAmt = 0.00;
double osBalanceQty = 0.00;
double osBalanceQtybf = 0.00;
double osBalanceAmtbf = 0.00;
double ClosingQtyBalance = 0.00;
double ClosingAmtBalance = 0.00;
double osBalanceQty1 = 0.00;
double osBalanceQty13 = 0.00;
double osBalanceQty14 = 0.00;
double osBalanceAmt1 = 0.00;
double osBalanceAmt12 = 0.00;
double current = 0.00;
try {
try {
com.lowagie.text.pdf.PdfWriter.getInstance(
docPdf, new java.io.FileOutputStream(tempFile));
String compName = null;
String date = null;
String Messg = null;
/* try {
java.sql.Statement st3 = connectDB.createStatement();
java.sql.Statement st4 = connectDB.createStatement();
// java.sql.ResultSet rset2 = st3.executeQuery("select name from pb_notice");
java.sql.ResultSet rset2 = st3.executeQuery("SELECT hospital_name from pb_hospitalprofile");
// java.sql.ResultSet rset4 = st4.executeQuery("SELECT date('now') as Date");
while(rset2.next())
Messg = rset2.getObject(1).toString();
// while(rset4.next())
// date = rset4.getObject(1).toString();
// com.lowagie.textHeaderFooter footer = new com.lowagie.text.HeaderFooter(new Phrase(""+Messg+""),false);// FontFactory.getFont(com.lowagie.text.FontFactory.HELVETICA, 14, Font.BOLDITALIC,java.awt.Color.blue)));
headerFoter = new com.lowagie.text.HeaderFooter(new Phrase(""+Messg+""),false);// FontFactory.getFont(com.lowagie.text.FontFactory.HELVETICA, 14, Font.BOLDITALIC,java.awt.Color.blue)));
// headerFoter.ALIGN_CENTER;
headerFoter.setRight(5);
} catch(java.sql.SQLException SqlExec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), SqlExec.getMessage());
}
*/
/*
try {
java.sql.Statement st3 = connectDB.createStatement();
java.sql.Statement st4 = connectDB.createStatement();
java.sql.ResultSet rset2 = st3.executeQuery("select name from pb_notice");
// java.sql.ResultSet rset2 = st3.executeQuery("SELECT hospital_name from pb_hospitalprofile");
// java.sql.ResultSet rset4 = st4.executeQuery("SELECT date('now') as Date");
while(rset2.next())
Messg = rset2.getObject(1).toString();
// while(rset4.next())
// date = rset4.getObject(1).toString();
com.lowagie.text.HeaderFooter footer = new com.lowagie.text.HeaderFooter(new Phrase(""+Messg+""),false);// FontFactory.getFont(com.lowagie.text.FontFactory.HELVETICA, 14, Font.BOLDITALIC,java.awt.Color.blue)));
// com.lowagie.text.HeaderFooter headerFoter = new com.lowagie.text.HeaderFooter(new Phrase(""+compName+""),false);// FontFactory.getFont(com.lowagie.text.FontFactory.HELVETICA, 14, Font.BOLDITALIC,java.awt.Color.blue)));
// headerFoter.ALIGN_CENTER;
// headerFoter.setRight(5);
docPdf.setFooter(footer);
} catch(java.sql.SQLException SqlExec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), SqlExec.getMessage());
}
*/
docPdf.open();
try {
java.util.Calendar calendar = java.util.Calendar.getInstance();
long dateNow = calendar.getTimeInMillis();
java.sql.Date datenowSql = new java.sql.Date(dateNow);
System.out.println(datenowSql.toString());
java.lang.Object listofStaffNos[] = this.getListofStaffNos();
com.lowagie.text.pdf.PdfPTable table1 = new com.lowagie.text.pdf.PdfPTable(7);
// com.lowagie.text.Table table = new com.lowagie.text.Table(7);
// table.endHeaders();
int headerwidths[] = {15, 15, 30, 15, 15, 15, 15};
table1.setWidths(headerwidths);
// if (docPdf.getPageNumber() > 1) {
// table1.setHeaderRows(4);
// }
table1.setWidthPercentage((100));
table1.getDefaultCell().setBorder(Rectangle.BOTTOM);
table1.getDefaultCell().setColspan(7);
Phrase phrase = new Phrase();
// table.addCell(phrase);
table1.getDefaultCell().setColspan(1);
table1.getDefaultCell().setBackgroundColor(java.awt.Color.WHITE);
table1.getDefaultCell().setBorderColor(java.awt.Color.WHITE);
try {
java.sql.Statement st3 = connectDB.createStatement();
java.sql.ResultSet rset3 = st3.executeQuery("select header_name from pb_header");
while (rset3.next()) table1.getDefaultCell().setColspan(7);
table1.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_CENTER);
phrase = new Phrase(rset3.getObject(1).toString(), pFontHeader11);
table1.addCell(phrase);
} catch (java.sql.SQLException SqlExec) {
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), SqlExec.getMessage());
}
docPdf.add(table1);
} catch (com.lowagie.text.BadElementException BadElExec) {
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), BadElExec.getMessage());
}
// } catch(java.io.FileNotFoundException fnfExec) {
// javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(),
// fnfExec.getMessage());
/// }
try {
java.util.Calendar calendar = java.util.Calendar.getInstance();
long dateNow = calendar.getTimeInMillis();
java.sql.Date datenowSql = new java.sql.Date(dateNow);
System.out.println(datenowSql.toString());
java.lang.Object listofStaffNos[] = this.getListofStaffNos();
com.lowagie.text.pdf.PdfPTable table = new com.lowagie.text.pdf.PdfPTable(5);
// com.lowagie.text.Table table = new com.lowagie.text.Table(7);
// table.endHeaders();
int headerwidths[] = {10, 40, 15, 15, 20};
table.setWidths(headerwidths);
// if (docPdf.getPageNumber() > 1) {
table.setHeaderRows(3);
// }
table.setWidthPercentage((100));
table.getDefaultCell().setBorder(Rectangle.BOTTOM);
table.getDefaultCell().setColspan(5);
Phrase phrase = new Phrase();
// table.addCell(phrase);
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setBackgroundColor(java.awt.Color.WHITE);
table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);
try {
java.sql.Statement st22 = connectDB.createStatement();
java.sql.Statement st11 = connectDB.createStatement();
java.sql.Statement st111 = connectDB.createStatement();
java.sql.Statement st = connectDB.createStatement();
java.sql.Statement st1 = connectDB.createStatement();
java.sql.Statement st1q = connectDB.createStatement();
java.sql.Statement st2 = connectDB.createStatement();
java.sql.Statement st3 = connectDB.createStatement();
java.sql.Statement st4 = connectDB.createStatement();
java.sql.Statement st41 = connectDB.createStatement();
java.sql.Statement st5 = connectDB.createStatement();
java.sql.ResultSet rset3 =
st3.executeQuery(
"select hospital_name,postal_code||' '||box_no||' '||town,main_telno||' '||other_telno,initcap(street),main_faxno,email,website,room_no from pb_hospitalprofile");
java.sql.ResultSet rset1 =
st1.executeQuery(
"select '-' as sno, item,sum(qty) ,sum(purchases),sum(sales),case when sum(sales) > 0 then sum(sales)*-1 else sum(purchases) end as balance from drug_movt where date BETWEEN '"
+ beginDate
+ "' AND '"
+ endDate
+ "' and qty > 0 group by item order by item"); // UNION ALL SELECT
// trans_date,store_name,user_name,issuing,total from st_sub_stores where (sub_store =
// 'Pharmacy' OR sub_store = 'IP Pharmacy') and item ilike '"+memNo+"' AND trans_date
// BETWEEN '"+beginDate+"' AND '"+endDate+"'");
java.sql.ResultSet rset11 =
st11.executeQuery(
"select sum(purchases),sum(sales) from drug_movt"); // UNION ALL SELECT
// trans_date,store_name,user_name,issuing,total from st_sub_stores where (sub_store =
// 'Pharmacy' OR sub_store = 'IP Pharmacy') and item ilike '"+memNo+"' AND trans_date
// BETWEEN '"+beginDate+"' AND '"+endDate+"'");
// java.sql.ResultSet rset111 = st111.executeQuery("select sum(issuing),sum(amount)
// from st_sub_stores where item ilike '"+memNo1+"' and sub_store ilike '"+memNo+"%'
// AND date BETWEEN '"+beginDate+"' AND '"+endDate+"'");// UNION ALL SELECT
// trans_date,store_name,user_name,issuing,total from st_sub_stores where (sub_store =
// 'Pharmacy' OR sub_store = 'IP Pharmacy') and item ilike '"+memNo+"' AND trans_date
// BETWEEN '"+beginDate+"' AND '"+endDate+"'");
table.getDefaultCell().setBorderColor(java.awt.Color.BLACK);
table.getDefaultCell().setBorderWidth(Rectangle.TOP | Rectangle.BOTTOM);
table.getDefaultCell().setColspan(5);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_CENTER);
phrase = new Phrase(" ", pFontHeader11);
table.addCell(phrase);
table.getDefaultCell().setColspan(5);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_CENTER);
phrase =
new Phrase(
"Drug Movement(Supplies Vs Sales) for all stockables items", pFontHeader11);
table.addCell(phrase);
table.getDefaultCell().setColspan(3);
try {
java.text.DateFormat dateFormat =
java.text.DateFormat.getDateInstance(java.text.DateFormat.MEDIUM); // MEDIUM);
java.util.Date endDate1 =
dateFormat.parse(endDate.toLocaleString()); // dateInstance.toLocaleString());
java.util.Date endDate11 =
dateFormat.parse(beginDate.toLocaleString()); // dateInstance.toLocaleString());
System.out.println("" + endDate1);
// phrase = new Phrase(bank +" Report: " +dateFormat.format(formattedDate),
// pFontHeader);
// table.addCell(phrase);
phrase =
new Phrase(
"Period : "
+ dateFormat.format(endDate11)
+ " - "
+ dateFormat.format(endDate1),
pFontHeader1);
table.addCell(phrase);
} catch (java.text.ParseException psExec) {
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), psExec.getMessage());
}
// table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
// phrase = new Phrase("Period : '"+beginDate+"' - '"+endDate+"'", pFontHeader1);
// table.addCell(phrase);
table.getDefaultCell().setColspan(2);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase("Date " + datenowSql, pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
table.getDefaultCell().setBorderColor(java.awt.Color.BLACK);
table.getDefaultCell().setBorderWidth(Rectangle.TOP);
table.getDefaultCell().setColspan(1);
phrase = new Phrase("SNo.", pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setColspan(1);
phrase = new Phrase("Item", pFontHeader1);
table.addCell(phrase);
phrase = new Phrase("Qty", pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
table.getDefaultCell().setColspan(1);
phrase = new Phrase("Purchases.", pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setColspan(1);
phrase = new Phrase("Sales", pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setColspan(1);
// table
while (rset1.next()) {
seq = seq + 1;
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase("" + seq + ".", pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase(dbObject.getDBObject(rset1.getObject(2), "-"), pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase = new Phrase(dbObject.getDBObject(rset1.getObject(3), "-"), pFontHeader);
osBalanceQty13 = osBalanceQty13 + rset1.getDouble(3);
table.addCell(phrase);
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(rset1.getString(4)),
pFontHeader);
osBalanceQty1 = osBalanceQty1 + rset1.getDouble(4);
table.addCell(phrase);
phrase = new Phrase(dbObject.getDBObject(rset1.getObject(5), "0"), pFontHeader);
osBalanceQty14 = osBalanceQty14 + rset1.getDouble(5);
table.addCell(phrase);
/*
phrase = new Phrase(new com.afrisoftech.sys.Format2Currency().Format2Currency(rset1.getString(6)),pFontHeader);
osBalanceAmt1 = osBalanceAmt1 + rset1.getDouble(5);
osBalanceAmt12 = osBalanceAmt12 + rset1.getDouble(6);
table.addCell(phrase);
*/
}
table.getDefaultCell().setBorderColor(java.awt.Color.BLACK);
table.getDefaultCell().setBorder(Rectangle.BOTTOM | Rectangle.TOP);
// while (rsetTotals.next()) {
table.getDefaultCell().setColspan(2);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase("TOTAL", pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(osBalanceQty13)),
pFontHeader);
table.addCell(phrase);
// phrase = new Phrase(" ");
// table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(osBalanceQty1)),
pFontHeader);
// phrase = new Phrase(" ",pFontHeader);
// phrase = new Phrase(" ",pFontHeader);
table.addCell(phrase);
// table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(osBalanceQty14)),
pFontHeader);
// phrase = new Phrase(" ",pFontHeader);
// phrase = new Phrase(" ",pFontHeader);
table.addCell(phrase);
/*
table.getDefaultCell().setColspan(3);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase("Totals", pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(2);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
*/
while (rset11.next()) {
/*
// phrase = new Phrase(new com.afrisoftech.sys.Format2Currency().Format2Currency(java.lang.String.valueOf((osBalanceQtybf + osBalanceQty) - osBalanceQty1)),pFontHeader);
// phrase = new Phrase(new com.afrisoftech.sys.Format2Currency().Format2Currency(java.lang.String.valueOf(closingBal+osBalanceQty)),pFontHeader);
phrase = new Phrase(new com.afrisoftech.sys.Format2Currency().Format2Currency(rset11.getString(1)),pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase = new Phrase(new com.afrisoftech.sys.Format2Currency().Format2Currency(rset11.getString(2)),pFontHeader);
// phrase = new Phrase(new com.afrisoftech.sys.Format2Currency().Format2Currency(java.lang.String.valueOf((closingBal+osBalanceQty)*buyingPrice)),pFontHeader);
table.addCell(phrase);
*/
}
/*
try {
java.sql.Statement st31 = connectDB.createStatement();
// java.sql.Statement st4 = connectDB.createStatement();
java.sql.ResultSet rset2 = st31.executeQuery("select name from pb_notice");
// java.sql.ResultSet rset2 = st3.executeQuery("SELECT hospital_name from pb_hospitalprofile");
// java.sql.ResultSet rset4 = st4.executeQuery("SELECT date('now') as Date");
while(rset2.next())
Messg = rset2.getString(1);
com.lowagie.text.HeaderFooter footer = new com.lowagie.text.HeaderFooter(new Phrase(""+Messg+""),false);// FontFactory.getFont(com.lowagie.text.FontFactory.HELVETICA, 14, Font.BOLDITALIC,java.awt.Color.blue)));
// com.lowagie.text.HeaderFooter headerFoter = new com.lowagie.text.HeaderFooter(new Phrase(""+compName+""),false);// FontFactory.getFont(com.lowagie.text.FontFactory.HELVETICA, 14, Font.BOLDITALIC,java.awt.Color.blue)));
// headerFoter.ALIGN_CENTER;
// headerFoter.setRight(5);
docPdf.setFooter(footer);
} catch(java.sql.SQLException SqlExec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), SqlExec.getMessage());
}
*/
docPdf.add(table);
} catch (java.sql.SQLException SqlExec) {
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), SqlExec.getMessage());
}
// } // }
} catch (com.lowagie.text.BadElementException BadElExec) {
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), BadElExec.getMessage());
}
} catch (java.io.FileNotFoundException fnfExec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), fnfExec.getMessage());
}
} catch (com.lowagie.text.DocumentException lwDocexec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), lwDocexec.getMessage());
}
docPdf.close();
com.afrisoftech.lib.PDFRenderer.renderPDF(tempFile);
} catch (java.io.IOException IOexec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), IOexec.getMessage());
}
}
public void generatePdf(java.lang.String memNo) {
java.lang.Process wait_for_Pdf2Show;
java.util.Calendar cal = java.util.Calendar.getInstance();
java.util.Date dateStampPdf = cal.getTime();
java.lang.String pdfDateStamp = dateStampPdf.toString();
try {
java.io.File tempFile =
java.io.File.createTempFile("REP" + this.getDateLable() + "_", ".pdf");
tempFile.deleteOnExit();
java.lang.Runtime rt = java.lang.Runtime.getRuntime();
java.lang.String debitTotal = null;
java.lang.String creditTotal = null;
com.lowagie.text.Document docPdf = new com.lowagie.text.Document();
double osBalance = 0.00;
double current = 0.00;
try {
try {
com.lowagie.text.pdf.PdfWriter.getInstance(
docPdf, new java.io.FileOutputStream(tempFile));
String compName = null;
String date = null;
String Messg = null;
try {
java.sql.Statement st3 = connectDB.createStatement();
java.sql.Statement st4 = connectDB.createStatement();
// java.sql.ResultSet rset2 = st3.executeQuery("SELECT hospital_name from
// pb_hospitalprofile");
java.sql.ResultSet rset4 =
st4.executeQuery(
"SELECT TO_CHAR(current_timestamp(0),'FMDay FMDD/ MM/ YY HH12::MI')");
while (rset4.next()) {
date = rset4.getObject(1).toString();
}
} catch (java.sql.SQLException SqlExec) {
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), SqlExec.getMessage());
}
docPdf.open();
try {
java.util.Calendar calendar = java.util.Calendar.getInstance();
long dateNow = calendar.getTimeInMillis();
java.sql.Date datenowSql = new java.sql.Date(dateNow);
System.out.println(datenowSql.toString());
java.lang.Object listofStaffNos[] = this.getListofStaffNos();
com.lowagie.text.pdf.PdfPTable table1 = new com.lowagie.text.pdf.PdfPTable(7);
// com.lowagie.text.Table table = new com.lowagie.text.Table(7);
// table.endHeaders();
int headerwidths[] = {15, 15, 30, 15, 15, 15, 15};
table1.setWidths(headerwidths);
// if (docPdf.getPageNumber() > 1) {
// table1.setHeaderRows(4);
// }
table1.setWidthPercentage((100));
table1.getDefaultCell().setBorder(Rectangle.BOTTOM);
table1.getDefaultCell().setColspan(7);
Phrase phrase = new Phrase();
// table.addCell(phrase);
table1.getDefaultCell().setColspan(1);
table1.getDefaultCell().setBackgroundColor(java.awt.Color.WHITE);
table1.getDefaultCell().setBorderColor(java.awt.Color.WHITE);
try {
java.sql.Statement st2 = connectDB.createStatement();
java.sql.ResultSet rset2 =
st2.executeQuery("SELECT rep_currency from pb_hospitalprofile");
while (rset2.next()) {
ks = rset2.getObject(1).toString();
}
java.sql.Statement st3 = connectDB.createStatement();
java.sql.ResultSet rset3 = st3.executeQuery("select header_name from pb_header");
while (rset3.next()) {
table1.getDefaultCell().setColspan(7);
table1.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_CENTER);
phrase = new Phrase(rset3.getObject(1).toString(), pFontHeader11);
table1.addCell(phrase);
}
} catch (java.sql.SQLException SqlExec) {
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), SqlExec.getMessage());
}
docPdf.add(table1);
} catch (com.lowagie.text.BadElementException BadElExec) {
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), BadElExec.getMessage());
}
try {
java.util.Calendar calendar = java.util.Calendar.getInstance();
long dateNow = calendar.getTimeInMillis();
java.sql.Date datenowSql = new java.sql.Date(dateNow);
System.out.println(datenowSql.toString());
java.lang.Object listofStaffNos[] = this.getListofStaffNos();
com.lowagie.text.pdf.PdfPTable table = new com.lowagie.text.pdf.PdfPTable(8);
// com.lowagie.text.Table table = new com.lowagie.text.Table(7);
// table.endHeaders();
int headerwidths[] = {12, 8, 30, 15, 10, 10, 10, 10};
table.setWidths(headerwidths);
// if (docPdf.getPageNumber() > 1) {
table.setHeaderRows(5);
// }
table.setWidthPercentage((100));
table.getDefaultCell().setBorder(Rectangle.BOTTOM);
table.getDefaultCell().setColspan(8);
Phrase phrase = new Phrase();
// table.addCell(phrase);
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setBackgroundColor(java.awt.Color.WHITE);
table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);
try {
double Debit = 0.00;
// double Credit = 0.00;
java.sql.Statement st22 = connectDB.createStatement();
java.sql.Statement st11 = connectDB.createStatement();
java.sql.Statement st = connectDB.createStatement();
java.sql.Statement st1 = connectDB.createStatement();
java.sql.Statement st2 = connectDB.createStatement();
java.sql.Statement st3 = connectDB.createStatement();
java.sql.Statement st4 = connectDB.createStatement();
java.sql.Statement st41 = connectDB.createStatement();
java.sql.Statement st5 = connectDB.createStatement();
java.sql.ResultSet rset3 =
st3.executeQuery(
"select hospital_name,postal_code||' '||box_no||' '||town,main_telno||' '||other_telno,initcap(street),main_faxno,email,website,room_no from pb_hospitalprofile");
// java.sql.ResultSet rset4 = st4.executeQuery("select scheme_manager from
// ac_scheme_providers where scheme_manager = '"+memNo+"'");
java.sql.ResultSet rset4 =
st4.executeQuery(
"select scheme_name,account_no from ac_schemes where account_no = '"
+ memNo1
+ "'");
java.sql.ResultSet rset41 =
st41.executeQuery(
"select payer_name from ac_schemes where account_no = '" + memNo1 + "'");
// java.sql.ResultSet rset1 = st1.executeQuery("select ac.date,pc.scheme_staff_no,
// ac.payee,ac.reference_no,sum(ac.debit),ac.admission_no from hp_patient_card
// pc,ac_debtors ac where pc.patient_no = ac.admission_no and ac.dealer = '"+memNo+"'
// and ac.dealer = pc.scheme AND ac.date::date BETWEEN '"+beginDate+"' AND
// '"+endDate+"' and ac.debit > 0 group by ac.date,pc.scheme_staff_no,
// ac.payee,ac.reference_no,ac.admission_no");// UNION select
// pd.date::date,initcap(pd.scheme_staff_no), (sh.first_name||' '||sh.second_name||'
// '||sh.last_name) as name,pd.reference,sum(pd.credit),pd.patient_no from
// hp_patient_card pd,hp_inpatient_register sh where pd.patient_no = sh.patient_no and
// pd.isurer = '"+memNo+"' AND pd.date::date BETWEEN '"+beginDate+"' AND '"+endDate+"'
// and pd.credit > 0 group by pd.date::date,pd.scheme_staff_no,
// name,pd.reference,pd.invoice_no,pd.patient_no order by pd.invoice_no");
java.sql.ResultSet rset1 =
st1.executeQuery(
"select date, upper(item),credit from ac_debtors where account_no = '"
+ memNo1
+ "' AND receipt_no ilike '"
+ memNo
+ "' AND date::date BETWEEN '"
+ beginDate
+ "' AND '"
+ endDate
+ "' order by date"); // UNION select
// pd.date::date,initcap(pd.scheme_staff_no),
// (sh.first_name||' '||sh.second_name||'
// '||sh.last_name) as
// name,pd.reference,sum(pd.credit),pd.patient_no
// from hp_patient_card pd,hp_inpatient_register sh
// where pd.patient_no = sh.patient_no and pd.isurer
// = '"+memNo+"' AND pd.date::date BETWEEN
// '"+beginDate+"' AND '"+endDate+"' and pd.credit >
// 0 group by pd.date::date,pd.scheme_staff_no,
// name,pd.reference,pd.invoice_no,pd.patient_no
// order by pd.invoice_no");
// java.sql.ResultSet rsetTotals2 = st22.executeQuery("select sum(balance-credit_bal)
// from ac_debtors where account_no = '"+memNo1+"' AND date::date < '"+beginDate+"'");
table.getDefaultCell().setBorderColor(java.awt.Color.BLACK);
table.getDefaultCell().setBorderWidth(Rectangle.TOP | Rectangle.BOTTOM);
table.getDefaultCell().setColspan(4);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_CENTER);
phrase = new Phrase("CREDIT NOTE", pFontHeader11);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase = new Phrase("CREDIT NOTE NO : " + memNo, pFontHeader11);
table.addCell(phrase);
while (rset4.next()) {
table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);
table.getDefaultCell().setColspan(8);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase(dbObject.getDBObject(rset4.getObject(1), "-"), pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase =
new Phrase(
"Account No : " + dbObject.getDBObject(rset4.getObject(2), "-"),
pFontHeader1);
table.addCell(phrase);
}
while (rset41.next()) {
table.getDefaultCell().setColspan(8);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase(dbObject.getDBObject(rset41.getObject(1), "-"), pFontHeader1);
table.addCell(phrase);
}
table.getDefaultCell().setColspan(4);
table.getDefaultCell().setColspan(8);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase = new Phrase("Print Date: " + date, pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
table.getDefaultCell().setBorderColor(java.awt.Color.BLACK);
table.getDefaultCell().setBorderWidth(Rectangle.TOP);
table.getDefaultCell().setColspan(2);
phrase = new Phrase("Date", pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setColspan(2);
phrase = new Phrase("Narration.", pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
table.getDefaultCell().setColspan(2);
phrase = new Phrase("Orig. Amt ", pFontHeader1);
table.addCell(phrase);
phrase = new Phrase("Running Amt. " + ks, pFontHeader1);
table.addCell(phrase);
while (rset1.next()) {
table.getDefaultCell().setColspan(2);
table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase(dbObject.getDBObject(rset1.getObject(1), "-"), pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(2);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase(dbObject.getDBObject(rset1.getObject(2), "-"), pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(2);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(rset1.getString(3)),
pFontHeader);
osBalance = osBalance + rset1.getDouble(3);
table.addCell(phrase);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(osBalance)),
pFontHeader);
table.addCell(phrase);
}
table.getDefaultCell().setColspan(2);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase = new Phrase("", pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setBorderColor(java.awt.Color.BLACK);
table.getDefaultCell().setBorder(Rectangle.BOTTOM | Rectangle.TOP);
table.getDefaultCell().setColspan(2);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase("Total", pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(4);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(osBalance)),
pFontHeader);
table.addCell(phrase);
docPdf.add(table);
} catch (java.sql.SQLException SqlExec) {
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), SqlExec.getMessage());
}
// } // }
} catch (com.lowagie.text.BadElementException BadElExec) {
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), BadElExec.getMessage());
}
} catch (java.io.FileNotFoundException fnfExec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), fnfExec.getMessage());
}
} catch (com.lowagie.text.DocumentException lwDocexec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), lwDocexec.getMessage());
}
docPdf.close();
com.afrisoftech.lib.PDFRenderer.renderPDF(tempFile);
} catch (java.io.IOException IOexec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), IOexec.getMessage());
}
}
public void generatePdf() {
java.lang.Process wait_for_Pdf2Show;
java.util.Calendar cal = java.util.Calendar.getInstance();
java.util.Date dateStampPdf = cal.getTime();
java.lang.String pdfDateStamp = dateStampPdf.toString();
try {
java.io.File tempFile =
java.io.File.createTempFile("REP" + this.getDateLable() + "_", ".pdf");
tempFile.deleteOnExit();
java.lang.Runtime rt = java.lang.Runtime.getRuntime();
java.lang.String debitTotal = null;
java.lang.String creditTotal = null;
// com.lowagie.text.Document docPdf = new com.lowagie.text.Document();
com.lowagie.text.Document docPdf = new com.lowagie.text.Document();
try {
try {
com.lowagie.text.pdf.PdfWriter.getInstance(
docPdf, new java.io.FileOutputStream(tempFile));
double osBalancebf = 0.00;
double osBalance = 0.00;
double osBalanceb1 = 0.00;
String compName = null;
String date = null;
docPdf.open();
try {
java.util.Calendar calendar = java.util.Calendar.getInstance();
long dateNow = calendar.getTimeInMillis();
java.sql.Date datenowSql = new java.sql.Date(dateNow);
System.out.println(datenowSql.toString());
// java.lang.Object listofStaffNos[] = this.getListofStaffNos();
com.lowagie.text.pdf.PdfPTable table1 = new com.lowagie.text.pdf.PdfPTable(7);
// com.lowagie.text.Table table = new com.lowagie.text.Table(7);
// table.endHeaders();
int headerwidths[] = {15, 15, 30, 15, 15, 15, 15};
table1.setWidths(headerwidths);
// if (docPdf.getPageNumber() > 1) {
// table1.setHeaderRows(4);
// }
table1.setWidthPercentage((100));
table1.getDefaultCell().setBorder(Rectangle.BOTTOM);
table1.getDefaultCell().setColspan(7);
Phrase phrase = new Phrase();
// table.addCell(phrase);
table1.getDefaultCell().setColspan(1);
table1.getDefaultCell().setBackgroundColor(java.awt.Color.WHITE);
table1.getDefaultCell().setBorderColor(java.awt.Color.WHITE);
try {
java.sql.Statement st3 = connectDB.createStatement();
java.sql.ResultSet rset3 = st3.executeQuery("select header_name from pb_header");
while (rset3.next()) table1.getDefaultCell().setColspan(7);
table1.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_CENTER);
phrase = new Phrase(rset3.getObject(1).toString(), pFontHeader1);
table1.addCell(phrase);
} catch (java.sql.SQLException SqlExec) {
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), SqlExec.getMessage());
}
docPdf.add(table1);
} catch (com.lowagie.text.BadElementException BadElExec) {
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), BadElExec.getMessage());
}
com.lowagie.text.HeaderFooter footer =
new com.lowagie.text.HeaderFooter(
new Phrase("Debtors Statement - Page: ", pFontHeader),
true); // FontFactory.getFont(com.lowagie.text.FontFactory.HELVETICA, 12,
// Font.BOLDITALIC,java.awt.Color.blue));
docPdf.setFooter(footer);
try {
java.util.Calendar calendar = java.util.Calendar.getInstance();
long dateNow = calendar.getTimeInMillis();
java.sql.Date datenowSql = new java.sql.Date(dateNow);
System.out.println(datenowSql.toString());
com.lowagie.text.pdf.PdfPTable table = new com.lowagie.text.pdf.PdfPTable(6);
int headerwidths[] = {10, 30, 15, 15, 15, 20};
table.setWidths(headerwidths);
table.setWidthPercentage((100));
table.setHeaderRows(2);
table.getDefaultCell().setBorder(Rectangle.BOTTOM);
Phrase phrase = new Phrase();
// table.getDefaultCell().setColspan(8);
table.getDefaultCell().setColspan(4);
try {
java.text.DateFormat dateFormat =
java.text.DateFormat.getDateInstance(java.text.DateFormat.MEDIUM); // MEDIUM);
java.util.Date endDate1 =
dateFormat.parse(endDate.toLocaleString()); // dateInstance.toLocaleString());
java.util.Date endDate11 =
dateFormat.parse(beginDate.toLocaleString()); // dateInstance.toLocaleString());
System.out.println("" + endDate1);
// phrase = new Phrase(bank +" Report: " +dateFormat.format(formattedDate),
// pFontHeader);
// table.addCell(phrase);
phrase =
new Phrase(
Debtor
+ " Period : "
+ dateFormat.format(endDate11)
+ " - "
+ dateFormat.format(endDate1),
pFontHeader1);
table.addCell(phrase);
} catch (java.text.ParseException psExec) {
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), psExec.getMessage());
}
// table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
// phrase = new Phrase("Period : '"+beginDate+"' - '"+endDate+"'", pFontHeader1);
// table.addCell(phrase);
table.getDefaultCell().setColspan(2);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase("Date " + datenowSql, pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setColspan(3);
table.getDefaultCell().setColspan(1);
// table.getDefaultCell().setBackgroundColor(java.awt.Color.LIGHT_GRAY);
phrase = new Phrase("Acc No", pFontHeader);
table.addCell(phrase);
phrase = new Phrase("Scheme", pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase = new Phrase("Debit", pFontHeader);
table.addCell(phrase);
phrase = new Phrase("Credit", pFontHeader);
table.addCell(phrase);
phrase = new Phrase("Balance ", pFontHeader);
table.addCell(phrase);
phrase = new Phrase("Running Total", pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setBackgroundColor(java.awt.Color.WHITE);
// table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);
try {
java.sql.Statement st = connectDB.createStatement();
java.sql.Statement st1 = connectDB.createStatement();
java.sql.Statement st2 = connectDB.createStatement();
java.sql.ResultSet rset =
st.executeQuery(
"select db.account_no,db.payee,sum(db.debit),sum(credit),SUM(db.balance) from ac_debtors db WHERE db.date BETWEEN '"
+ beginDate
+ "' AND '"
+ endDate
+ "' AND db.dealer ILIKE '"
+ Debtor
+ "' GROUP BY db.payee,db.account_no ORDER BY db.payee");
java.sql.ResultSet rset1 =
st1.executeQuery(
"select SUM(db.balance) from ac_debtors db WHERE db.date < '"
+ beginDate
+ "' AND db.dealer ILIKE '"
+ Debtor
+ "' GROUP BY db.account_no");
while (rset1.next()) {
bfwd = rset1.getDouble(1);
bfcd = bfcd + rset1.getDouble(1);
}
while (rset.next()) {
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase(dbObject.getDBObject(rset.getObject(1), "-"), pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase(dbObject.getDBObject(rset.getObject(2), "-"), pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(rset.getString(3)),
pFontHeader1);
osBalance = osBalance + rset.getDouble(3);
table.addCell(phrase);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(rset.getString(4)),
pFontHeader1);
osBalanceb1 = osBalanceb1 + rset.getDouble(4);
table.addCell(phrase);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(rset.getString(5)),
pFontHeader);
osBalancebf = osBalancebf + rset.getDouble(5);
// osBalancebf = osBalance1;
table.addCell(phrase);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(osBalancebf)),
pFontHeader);
table.addCell(phrase);
}
// }
table.getDefaultCell().setBorderColor(java.awt.Color.BLACK);
table.getDefaultCell().setBorder(Rectangle.BOTTOM | Rectangle.TOP);
// while (rsetTotals.next()) {
table.getDefaultCell().setColspan(2);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase("Total", pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(osBalance)),
pFontHeader);
table.addCell(phrase);
// table.getDefaultCell().setColspan(2);
// table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(osBalanceb1)),
pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(2);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(osBalancebf)),
pFontHeader);
table.addCell(phrase);
docPdf.add(table);
} catch (java.sql.SQLException SqlExec) {
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), SqlExec.getMessage());
}
} catch (com.lowagie.text.BadElementException BadElExec) {
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), BadElExec.getMessage());
}
} catch (java.io.FileNotFoundException fnfExec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), fnfExec.getMessage());
}
} catch (com.lowagie.text.DocumentException lwDocexec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), lwDocexec.getMessage());
}
docPdf.close();
try {
if (System.getProperty("os.name").equalsIgnoreCase("Linux")) {
System.out.println(tempFile);
// wait_for_Pdf2Show = rt.exec("kghostview "+tempFile+"");
wait_for_Pdf2Show = rt.exec("kghostview " + tempFile + "");
wait_for_Pdf2Show.waitFor();
} else {
wait_for_Pdf2Show =
rt.exec("c:/Program Files/Adobe/Acrobat 5.0/Reader/AcroRd32.exe " + tempFile);
wait_for_Pdf2Show.waitFor();
}
} catch (java.lang.InterruptedException intrExec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), intrExec.getMessage());
}
} catch (java.io.IOException IOexec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), IOexec.getMessage());
}
}
public void generatePdf() {
// java.lang.Object[][] ageingDates = periodicDates.getMonthlyDates();
ageingDates = ageingSeries.getAgeingDateSeries();
double columnTotals[] = new double[ageingDates.length];
double columnTotals2[] = new double[ageingDates.length];
java.lang.Process wait_for_Pdf2Show;
java.util.Calendar cal = java.util.Calendar.getInstance();
java.util.Date dateStampPdf = cal.getTime();
java.lang.String pdfDateStamp = dateStampPdf.toString();
int interval = 0;
try {
java.io.File tempFile =
java.io.File.createTempFile("REP" + this.getDateLable() + "_", ".pdf");
tempFile.deleteOnExit();
java.lang.Runtime rt = java.lang.Runtime.getRuntime();
java.lang.String debitTotal = null;
java.lang.String creditTotal = null;
com.lowagie.text.Document docPdf = new com.lowagie.text.Document();
java.util.Calendar calendar = java.util.Calendar.getInstance();
long dateNow = calendar.getTimeInMillis();
java.sql.Date datenowSql = new java.sql.Date(dateNow);
System.out.println(datenowSql.toString());
try {
try {
com.lowagie.text.pdf.PdfWriter.getInstance(
docPdf, new java.io.FileOutputStream(tempFile));
String compName = null;
String date = null;
try {
// java.sql.Connection conDb =
// java.sql.DriverManager.getConnection("jdbc:postgresql://localhost:5432/sako","postgres","pilsiner");
java.sql.Statement st3 = connectDB.createStatement();
java.sql.Statement st4 = connectDB.createStatement();
java.sql.ResultSet rset2 =
st3.executeQuery("SELECT hospital_name,rep_currency from pb_hospitalprofile");
java.sql.ResultSet rset4 = st4.executeQuery("SELECT date('now') as Date");
while (rset2.next()) {
compName = rset2.getObject(1).toString();
ks = rset2.getString(2);
}
while (rset4.next()) {
date = rset4.getObject(1).toString();
}
com.lowagie.text.HeaderFooter headerFoter =
new com.lowagie.text.HeaderFooter(
new Phrase("" + compName, pFontHeader2),
false); // FontFactory.getFont(com.lowagie.text.FontFactory.HELVETICA, 14,
// Font.BOLDITALIC,java.awt.Color.blue)));
// com.lowagie.text.HeaderFooter headerFoter = new com.lowagie.text.HeaderFooter(new
// Phrase(""+compName+""),false);//
// FontFactory.getFont(com.lowagie.text.FontFactory.HELVETICA, 14,
// Font.BOLDITALIC,java.awt.Color.blue)));
headerFoter.setAlignment(com.lowagie.text.HeaderFooter.ALIGN_CENTER);
docPdf.setHeader(headerFoter);
} catch (java.sql.SQLException SqlExec) {
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), SqlExec.getMessage());
}
com.lowagie.text.HeaderFooter footer =
new com.lowagie.text.HeaderFooter(
new Phrase("Ageing Page: ", pFontHeader),
true); // FontFactory.getFont(com.lowagie.text.FontFactory.HELVETICA, 12,
// Font.BOLDITALIC,java.awt.Color.blue));
docPdf.setFooter(footer);
docPdf.open();
double Totals = 0.00;
double OS = 0.00;
try {
com.lowagie.text.pdf.PdfPTable table =
new com.lowagie.text.pdf.PdfPTable(ageingDates.length + 5);
String headerWidths = null;
java.util.Vector headerVector = new java.util.Vector(1, 1);
int z = ageingDates.length;
int headerwidths[] = {7, 35, 13, 13, 13, 13, 13, 13, 13}; // ,13,13};
table.setWidths(headerwidths);
table.setWidthPercentage((107));
table.setHeaderRows(2);
table.getDefaultCell().setBorder(Rectangle.BOTTOM);
table.getDefaultCell().setColspan(9);
Phrase phrase = new Phrase("");
table.getDefaultCell().setColspan(6);
try {
java.text.DateFormat dateFormat =
java.text.DateFormat.getDateInstance(java.text.DateFormat.MEDIUM); // MEDIUM);
// java.util.Date endDate1 =
// dateFormat.parse(beginDate.toLocaleString());//dateInstance.toLocaleString());
java.util.Date endDate2 = dateFormat.parse(endDate.toLocaleString());
phrase =
new Phrase("Debtors Ageing as at " + dateFormat.format(endDate2), pFontHeader);
table.addCell(phrase);
} catch (java.text.ParseException psExec) {
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), psExec.getMessage());
}
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
table.getDefaultCell().setColspan(3);
phrase = new Phrase("Printed on : " + date, pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_CENTER);
phrase = new Phrase("No.", pFontHeader);
table.addCell(phrase);
phrase = new Phrase("Name", pFontHeader);
table.addCell(phrase);
for (int x = 0; x < ageingDates.length; x++) {
// for (int x = ageingDates.length - 1; x >= 0; x--) {
int days = 30;
if (x < 1) {
phrase = new Phrase("Current", pFontHeader);
} else {
phrase = new Phrase("+ " + x * days + " Days", pFontHeader);
interval = x;
}
table.addCell(phrase);
}
phrase = new Phrase("+ " + ((interval + 1) * 30) + " Days", pFontHeader);
table.addCell(phrase);
phrase = new Phrase("UnAlloc.", pFontHeader);
table.addCell(phrase);
phrase = new Phrase("O/S " + ks, pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setBackgroundColor(java.awt.Color.WHITE);
try {
double GrandTotal = 0.00;
double Over120Total = 0.00;
java.lang.Object[] listofAct = this.getListofActivities();
System.out.println(listofAct.length);
for (int i = 0; i < listofAct.length; i++) {
double TurnOver = 0.00;
double Over120 = 0.00;
double TotalCount = 0.00;
double unalloc = 0.00;
double Curr120 = 0.00;
double bals = 0.00;
// java.lang.Object[] listofActinv = this.getListofActivitiesinv(listofAct[i]);
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
table.getDefaultCell().setColspan(1);
java.sql.Statement stmta1 = connectDB.createStatement();
java.sql.PreparedStatement pSeta1 =
connectDB.prepareStatement(
"SELECT count(account_no) FROM ac_debtors"
+ " where account_no = '"
+ listofAct[i]
+ "'");
java.sql.PreparedStatement pset22 =
connectDB.prepareStatement(
"select distinct account_no||' '||scheme_name from "
+ "ac_schemes WHERE account_no = ? order by 1 ASC"); // <
// '"+endDate+"'::date and date > '"+endDate+"'::date - 30 group by dealer");
pset22.setString(1, listofAct[i].toString().toUpperCase());
java.sql.ResultSet rSeta1 = pSeta1.executeQuery();
while (rSeta1.next()) {
name = rSeta1.getInt(1);
}
if (name > 0) {
java.sql.ResultSet rset22 = pset22.executeQuery();
while (rset22.next()) {
cnt = cnt + 1;
phrase = new Phrase("" + cnt, pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setColspan(1);
phrase =
new Phrase(dbObject.getDBObject(rset22.getObject(1), "-"), pFontHeader1);
table.addCell(phrase);
java.sql.Statement st2 = connectDB.createStatement();
java.sql.Statement st21 = connectDB.createStatement();
java.sql.Statement st22 = connectDB.createStatement();
java.sql.Statement st23 = connectDB.createStatement();
java.sql.Statement st211 = connectDB.createStatement();
java.sql.Statement st221 = connectDB.createStatement();
java.sql.Statement st231 = connectDB.createStatement();
java.sql.Statement st2A = connectDB.createStatement();
java.sql.Statement st2B = connectDB.createStatement();
java.sql.Statement st2C = connectDB.createStatement();
java.sql.Statement stc = connectDB.createStatement();
System.out.println("Dealer Is : [" + listofAct[i] + "].");
java.sql.PreparedStatement pset111 =
connectDB.prepareStatement(
"select sum(debit-credit) from ac_debtors "
+ "WHERE account_no = ? AND date <= '"
+ endDate
+ "'"); // < '"+endDate+"'::date and date > '"+endDate+"'::date - 30
// group by dealer");
pset111.setString(1, listofAct[i].toString());
java.sql.ResultSet rset111 = pset111.executeQuery();
java.sql.Statement st02 = connectDB.createStatement();
java.sql.PreparedStatement pset112 =
connectDB.prepareStatement(
"select sum(credit) from ac_debtors"
+ " WHERE account_no = ? AND date <= '"
+ endDate
+ "'"); // < '"+endDate+"'::date and date > '"+endDate+"'::date - 30
// group by dealer");
pset112.setString(1, listofAct[i].toString());
java.sql.ResultSet rset112 = pset112.executeQuery();
// for (int k = 0; k < listofActinv.length; k++) {
for (int t = ageingDates.length - 1; t >= 0; t--) {
columnTotals[t] = 0.00;
double credo = 0.00;
double credo1 = 0.00;
Over120 = 0.00;
java.sql.Statement st01 = connectDB.createStatement();
java.sql.PreparedStatement pset =
connectDB.prepareStatement(
"select sum(debit),invoice_no from ac_debtors "
+ "WHERE account_no = ? AND date between '"
+ ageingDates[t][0]
+ "' AND '"
+ ageingDates[t][1]
+ "' "
+ " GROUP BY invoice_no order by 2 ASC");
pset.setString(1, listofAct[i].toString().toUpperCase());
java.sql.ResultSet rset = pset.executeQuery();
while (rset.next()) {
String invoiceNo = rset.getString(2);
java.sql.Statement st01x = connectDB.createStatement();
java.sql.PreparedStatement psetx =
connectDB.prepareStatement(
"select sum(credit),invoice_no from ac_debtors"
+ " WHERE account_no = ? AND date <= '"
+ endDate
+ "' AND invoice_no = '"
+ invoiceNo
+ "' GROUP BY invoice_no order by 2 ASC ");
psetx.setString(1, listofAct[i].toString().toUpperCase());
java.sql.ResultSet rsetx = psetx.executeQuery();
while (rsetx.next()) {
credo = rsetx.getDouble(1);
}
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
columnTotals[t] = columnTotals[t] + (rset.getDouble(1) - credo);
TotalCount = TotalCount + (rset.getDouble(1) - credo);
columnTotals2[t] = columnTotals2[t] + (rset.getDouble(1) - credo);
}
}
for (int t = ageingDates.length - 1; t >= 0; t--) {
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(columnTotals[t])),
pFontHeader1);
table.addCell(phrase);
// }
}
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
while (rset111.next()) {
bals = rset111.getDouble(1);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(bals - TotalCount)),
pFontHeader1);
table.addCell(phrase);
Over120Total = Over120Total + (bals - TotalCount);
// Over120Total = Over120Total+ rset1.getDouble(1);
TurnOver = TotalCount + Curr120;
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(bals - bals)),
pFontHeader1);
table.addCell(phrase);
OS = OS + unalloc;
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(rset111.getString(1)),
pFontHeader1);
table.addCell(phrase);
Totals = Totals + rset111.getDouble(1);
}
}
}
}
table.getDefaultCell().setBorderColor(java.awt.Color.BLACK);
table.getDefaultCell().setBorder(Rectangle.BOTTOM | Rectangle.TOP);
// while (rsetTotals.next()) {
table.getDefaultCell().setColspan(2);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase("Total", pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
for (int x = ageingDates.length - 1; x >= 0; x--) {
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(columnTotals2[x])),
pFontHeader);
table.addCell(phrase);
grandTot = grandTot + columnTotals2[x];
}
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(Totals - grandTot)),
pFontHeader);
table.addCell(phrase);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(OS)),
pFontHeader);
table.addCell(phrase);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(Totals)),
pFontHeader);
table.addCell(phrase);
docPdf.add(table);
} catch (java.sql.SQLException SqlExec) {
SqlExec.printStackTrace();
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), SqlExec.getMessage());
}
/* } catch(java.text.ParseException psExec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), psExec.getMessage());
}
*/
} catch (com.lowagie.text.BadElementException BadElExec) {
// Bad
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), BadElExec.getMessage());
}
} catch (java.io.FileNotFoundException fnfExec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), fnfExec.getMessage());
}
} catch (com.lowagie.text.DocumentException lwDocexec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), lwDocexec.getMessage());
}
docPdf.close();
com.afrisoftech.lib.PDFRenderer.renderPDF(tempFile);
} catch (java.io.IOException IOexec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), IOexec.getMessage());
}
}
public void generatePdf() {
java.lang.Object[][] rangeDates = ageingSeries.getAgeingDateSeries();
// ageingDates = ageingSeries.getAgeingDateSeries();
double columnTotals[] = new double[rangeDates.length];
java.lang.Process wait_for_Pdf2Show;
java.util.Calendar cal = java.util.Calendar.getInstance();
java.util.Date dateStampPdf = cal.getTime();
java.lang.String pdfDateStamp = dateStampPdf.toString();
int interval = 0;
try {
java.io.File tempFile =
java.io.File.createTempFile("REP" + this.getDateLable() + "_", ".pdf");
tempFile.deleteOnExit();
java.lang.Runtime rt = java.lang.Runtime.getRuntime();
java.lang.String debitTotal = null;
java.lang.String creditTotal = null;
com.lowagie.text.Document docPdf = new com.lowagie.text.Document();
java.util.Calendar calendar = java.util.Calendar.getInstance();
long dateNow = calendar.getTimeInMillis();
java.sql.Date datenowSql = new java.sql.Date(dateNow);
System.out.println(datenowSql.toString());
try {
try {
com.lowagie.text.pdf.PdfWriter.getInstance(
docPdf, new java.io.FileOutputStream(tempFile));
String compName = null;
String date = null;
try {
// java.sql.Connection conDb =
// java.sql.DriverManager.getConnection("jdbc:postgresql://localhost:5432/sako","postgres","pilsiner");
java.sql.Statement st3 = connectDB.createStatement();
java.sql.Statement st4 = connectDB.createStatement();
java.sql.ResultSet rset2 =
st3.executeQuery("SELECT hospital_name from pb_hospitalprofile");
java.sql.ResultSet rset4 = st4.executeQuery("SELECT date('now') as Date");
while (rset2.next()) compName = rset2.getObject(1).toString();
while (rset4.next()) date = rset4.getObject(1).toString();
com.lowagie.text.HeaderFooter headerFoter =
new com.lowagie.text.HeaderFooter(
new Phrase("" + compName, pFontHeader2),
false); // FontFactory.getFont(com.lowagie.text.FontFactory.HELVETICA, 14,
// Font.BOLDITALIC,java.awt.Color.blue)));
// com.lowagie.text.HeaderFooter headerFoter = new com.lowagie.text.HeaderFooter(new
// Phrase(""+compName+""),false);//
// FontFactory.getFont(com.lowagie.text.FontFactory.HELVETICA, 14,
// Font.BOLDITALIC,java.awt.Color.blue)));
headerFoter.setAlignment(com.lowagie.text.HeaderFooter.ALIGN_CENTER);
docPdf.setHeader(headerFoter);
} catch (java.sql.SQLException SqlExec) {
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), SqlExec.getMessage());
}
com.lowagie.text.HeaderFooter footer =
new com.lowagie.text.HeaderFooter(
new Phrase("Ageing Page: ", pFontHeader),
true); // FontFactory.getFont(com.lowagie.text.FontFactory.HELVETICA, 12,
// Font.BOLDITALIC,java.awt.Color.blue));
docPdf.setFooter(footer);
docPdf.open();
double Totals = 0.00;
double OS = 0.00;
try {
com.lowagie.text.pdf.PdfPTable table =
new com.lowagie.text.pdf.PdfPTable(rangeDates.length + 3);
String headerWidths = null;
java.util.Vector headerVector = new java.util.Vector(1, 1);
int z = rangeDates.length;
int headerwidths[] = {35, 15, 15, 10, 10};
table.setWidths(headerwidths);
table.setWidthPercentage((107));
table.setHeaderRows(2);
table.getDefaultCell().setBorder(Rectangle.BOTTOM);
table.getDefaultCell().setColspan(5);
Phrase phrase = new Phrase("");
table.getDefaultCell().setColspan(3);
try {
java.text.DateFormat dateFormat =
java.text.DateFormat.getDateInstance(java.text.DateFormat.MEDIUM); // MEDIUM);
java.util.Date endDate2 = dateFormat.parse(endDate.toLocaleString());
phrase =
new Phrase("Weekly Sales Comparison " + dateFormat.format(endDate2), pFontHeader);
table.addCell(phrase);
} catch (java.text.ParseException psExec) {
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), psExec.getMessage());
}
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
table.getDefaultCell().setColspan(2);
phrase = new Phrase("Printed on : " + date, pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase("DESCRIPTION", pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_CENTER);
for (int x = 0; x < rangeDates.length; x++) {
int days = 1;
try {
// Date parser
java.text.SimpleDateFormat dateFormat =
new java.text.SimpleDateFormat("EEE MMM dd HH:mm:ss zzz yyyy");
com.afrisoftech.lib.DateFormatter dateFormatter =
new com.afrisoftech.lib.DateFormatter(
dateFormat.parse(rangeDates[x][0].toString().trim()), "w");
com.afrisoftech.lib.DateFormatter yearFormatterCurrent =
new com.afrisoftech.lib.DateFormatter(endDate, "yy");
java.lang.String monthString = dateFormatter.getDateString();
java.lang.String yearStringCurrent = yearFormatterCurrent.getDateString();
// int days = 1;
// if (x < 1) {
com.afrisoftech.lib.DateFormatter dateFormatterCurrent =
new com.afrisoftech.lib.DateFormatter(endDate, "w");
com.afrisoftech.lib.DateFormatter yearFormatterCurrent1 =
new com.afrisoftech.lib.DateFormatter(endDate, "yy");
java.lang.String monthStringCurrent = dateFormatterCurrent.getDateString();
java.lang.String yearStringCurrent1 = yearFormatterCurrent1.getDateString();
// phrase = new Phrase("Week ["+monthStringCurrent+"/"+yearStringCurrent1+"]"
// ,pFontHeader);
// } else {
// phrase = new Phrase("Week ["+monthString+"/"+yearStringCurrent+"]",
// pFontHeader);
// phrase = new Phrase("+ "+x*days +"
// Month",pFontHeader);
interval = x;
// }
table.addCell(phrase);
// Catch java.text.parse exception.
} catch (java.text.ParseException prs) {
prs.printStackTrace();
}
}
// table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase("Change % (+/-)", pFontHeader);
table.addCell(phrase);
phrase = new Phrase("Change In Reporting Rate", pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setBackgroundColor(java.awt.Color.WHITE);
// table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);
try {
double GrandTotal = 0.00;
double Over120Total = 0.00;
double TurnOver = 0.00;
java.lang.Object[] listofAct = this.getListofActivities();
// java.sql.Connection conDb1 =
// java.sql.DriverManager.getConnection("jdbc:postgresql://localhost:5432/sako","postgres","pilsiner");
System.out.println(listofAct.length);
double Over120 = 0.00;
for (int i = 0; i < listofAct.length; i++) {
double TotalCount = 0.00;
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
table.getDefaultCell().setColspan(1);
java.sql.Statement stmta1 = connectDB.createStatement();
java.sql.PreparedStatement pSeta1 =
connectDB.prepareStatement(
"SELECT count(activity_code) FROM ac_ledger where activity_code = '"
+ listofAct[i]
+ "'");
java.sql.PreparedStatement pset22 =
connectDB.prepareStatement(
"select activity from pb_activity WHERE code = ?"); // < '"+endDate+"'::date
// and date >
// '"+endDate+"'::date -
// 30 group by dealer");
// java.sql.PreparedStatement pset22 = connectDB.prepareStatement("select
// description from ac_ledger WHERE activity_code = ?");//< '"+endDate+"'::date and
// date > '"+endDate+"'::date - 30 group by dealer");
pset22.setString(1, listofAct[i].toString().toUpperCase());
java.sql.ResultSet rSeta1 = pSeta1.executeQuery();
while (rSeta1.next()) {
name = rSeta1.getInt(1);
}
if (name > 0) {
java.sql.ResultSet rset22 = pset22.executeQuery();
while (rset22.next()) {
table.getDefaultCell().setColspan(1);
// table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase =
new Phrase(
dbObject.getDBObject(rset22.getObject(1).toString().toUpperCase(), "-"),
pFontHeader1);
table.addCell(phrase);
}
java.sql.Statement st2 = connectDB.createStatement();
java.sql.Statement st21 = connectDB.createStatement();
java.sql.Statement st22 = connectDB.createStatement();
java.sql.Statement st23 = connectDB.createStatement();
java.sql.Statement st211 = connectDB.createStatement();
java.sql.Statement st221 = connectDB.createStatement();
java.sql.Statement st231 = connectDB.createStatement();
java.sql.Statement st2A = connectDB.createStatement();
java.sql.Statement st2B = connectDB.createStatement();
java.sql.Statement st2C = connectDB.createStatement();
java.sql.Statement stc = connectDB.createStatement();
System.out.println("Dealer Is : [" + listofAct[i] + "].");
java.sql.PreparedStatement pset1 =
connectDB.prepareStatement(
"select sum(credit-debit) from ac_ledger WHERE activity_code = ? AND date = '"
+ rangeDates[rangeDates.length - 1][0]
+ "'"); // AND '"+rangeDates[rangeDates.length - 1][1]+"'");//<
// '"+endDate+"'::date and date > '"+endDate+"'::date - 30
// group by dealer");
pset1.setString(1, listofAct[i].toString());
java.sql.ResultSet rset1 = pset1.executeQuery();
// java.sql.PreparedStatement pset111 = connectDB.prepareStatement("select
// sum(credit-debit) from ac_ledger WHERE activity_code = ? AND date <
// '"+endDate+"'");//< '"+endDate+"'::date and date > '"+endDate+"'::date - 30
// group by dealer");
// pset111.setString(1,listofAct[i].toString());
// java.sql.ResultSet rset111 = pset111.executeQuery();
// java.sql.Statement st02 = connectDB.createStatement();
// java.sql.PreparedStatement pset112 = connectDB.prepareStatement("select
// sum(credit-debit) from ac_ledger WHERE activity_code = ? AND date <
// '"+endDate+"'");//< '"+endDate+"'::date and date > '"+endDate+"'::date - 30
// group by dealer");
// pset112.setString(1,listofAct[i].toString());
// java.sql.ResultSet rset112 = pset112.executeQuery();
for (int t = 0; t < rangeDates.length; t++) {
java.sql.Statement st01 = connectDB.createStatement();
java.sql.PreparedStatement pset =
connectDB.prepareStatement(
"select sum(credit-debit) from ac_ledger WHERE activity_code = ? AND date between '"
+ rangeDates[t][0]
+ "' AND '"
+ rangeDates[t][1]
+ "'"); // < '"+endDate+"'::date and date > '"+endDate+"'::date - 30
// group by dealer");
pset.setString(1, listofAct[i].toString().toUpperCase());
java.sql.ResultSet rset = pset.executeQuery();
while (rset.next()) {
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(rset.getString(1)),
pFontHeader1);
table.addCell(phrase);
columnTotals[t] = columnTotals[t] + rset.getDouble(1);
TotalCount = TotalCount + rset.getDouble(1);
TurnOver = rset.getDouble(1);
}
}
// phrase = new Phrase(new
// com.afrisoftech.sys.Format2Currency().Format2Currency(java.lang.String.valueOf(TotalCount)), pFontHeader);
// table.addCell(phrase);
while (rset1.next()) {
Over120 = rset1.getDouble(1);
if (Over120 > 0) {
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(
java.lang.String.valueOf((Over120 / TurnOver) * 100)),
pFontHeader);
table.addCell(phrase);
phrase = new Phrase("+", pFontHeader);
table.addCell(phrase);
} else {
phrase = new Phrase(java.lang.String.valueOf(0.00), pFontHeader);
table.addCell(phrase);
phrase = new Phrase("", pFontHeader);
table.addCell(phrase);
}
}
/* }else{
if (TotalCount <= 0 && Over120 > 0){
phrase = new Phrase(java.lang.String.valueOf((Over120/TurnOver)*100), pFontHeader);
table.addCell(phrase);
phrase = new Phrase("+", pFontHeader);
table.addCell(phrase);
}else{
if (TotalCount > 0 && Over120 < 0){
phrase = new Phrase(java.lang.String.valueOf((Over120/TurnOver)*100), pFontHeader);
table.addCell(phrase);
phrase = new Phrase("-", pFontHeader);
table.addCell(phrase);
}else{
if (TotalCount > 0 && Over120 == 0){
phrase = new Phrase(java.lang.String.valueOf((Over120/TurnOver)*100), pFontHeader);
table.addCell(phrase);
phrase = new Phrase("-", pFontHeader);
table.addCell(phrase);
}else{
if (TotalCount < 0 && Over120 < 0){
phrase = new Phrase(java.lang.String.valueOf((Over120/TurnOver)*100), pFontHeader);
table.addCell(phrase);
phrase = new Phrase("-", pFontHeader);
table.addCell(phrase);
}else{
phrase = new Phrase(java.lang.String.valueOf(0.00), pFontHeader);
table.addCell(phrase);
phrase = new Phrase("", pFontHeader);
table.addCell(phrase);
}
}
}
}
}*/
// }
}
}
table.getDefaultCell().setBorderColor(java.awt.Color.BLACK);
table.getDefaultCell().setBorder(Rectangle.BOTTOM | Rectangle.TOP);
// while (rsetTotals.next()) {
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase("Total", pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
// phrase = new Phrase(new
// com.afrisoftech.sys.Format2Currency().Format2Currency(rsetTotals.getString(1)),
// pFontHeader);
for (int x = 0; x < rangeDates.length; x++) {
// phrase = new Phrase("Current"+2*x,pFontHeader);
// table.addCell(phrase);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(columnTotals[x])),
pFontHeader);
table.addCell(phrase);
}
phrase = new Phrase(" ", pFontHeader);
table.addCell(phrase);
table.addCell(phrase);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(Over120Total)),
pFontHeader);
// table.addCell(phrase);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(OS)),
pFontHeader);
// table.addCell(phrase);
docPdf.add(table);
} catch (java.sql.SQLException SqlExec) {
SqlExec.printStackTrace();
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), SqlExec.getMessage());
}
} catch (com.lowagie.text.BadElementException BadElExec) {
// Bad
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), BadElExec.getMessage());
}
} catch (java.io.FileNotFoundException fnfExec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), fnfExec.getMessage());
}
} catch (com.lowagie.text.DocumentException lwDocexec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), lwDocexec.getMessage());
}
docPdf.close();
try {
if (System.getProperty("os.name").equalsIgnoreCase("Linux")) {
System.out.println(tempFile);
wait_for_Pdf2Show = rt.exec("kghostview " + tempFile + "");
wait_for_Pdf2Show.waitFor();
} else {
wait_for_Pdf2Show =
rt.exec("c:/Program Files/Adobe/Acrobat 5.0/Reader/AcroRd32.exe " + tempFile);
wait_for_Pdf2Show.waitFor();
}
} catch (java.lang.InterruptedException intrExec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), intrExec.getMessage());
}
} catch (java.io.IOException IOexec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), IOexec.getMessage());
}
}
public void generatePdf(java.lang.String memNo) {
rangeDates = ageingSeries.getAgeingDateSeries();
double columnTotals[] = new double[rangeDates.length];
// java.lang.Object[][] rangeDates = periodicDates.getMonthlyDates();
// double columnTotals[] = new double[rangeDates.length];
java.lang.Process wait_for_Pdf2Show;
java.util.Calendar cal = java.util.Calendar.getInstance();
java.util.Date dateStampPdf = cal.getTime();
java.lang.String pdfDateStamp = dateStampPdf.toString();
int interval = 0;
try {
java.io.File tempFile =
java.io.File.createTempFile("REP" + this.getDateLable() + "_", ".pdf");
tempFile.deleteOnExit();
java.lang.Runtime rt = java.lang.Runtime.getRuntime();
java.lang.String debitTotal = null;
java.lang.String creditTotal = null;
com.lowagie.text.Document docPdf = new com.lowagie.text.Document(PageSize.A4.rotate());
double osBalance = 0.00;
double current = 0.00;
try {
try {
com.lowagie.text.pdf.PdfWriter.getInstance(
docPdf, new java.io.FileOutputStream(tempFile));
String compName = null;
String date = null;
String Messg = null;
docPdf.open();
try {
java.util.Calendar calendar = java.util.Calendar.getInstance();
long dateNow = calendar.getTimeInMillis();
java.sql.Date datenowSql = new java.sql.Date(dateNow);
System.out.println(datenowSql.toString());
// java.lang.Object listofStaffNos[] = this.getListofStaffNos();
com.lowagie.text.pdf.PdfPTable table1 = new com.lowagie.text.pdf.PdfPTable(7);
// com.lowagie.text.Table table = new com.lowagie.text.Table(7);
// table.endHeaders();
int headerwidths[] = {15, 15, 30, 15, 15, 15, 15};
table1.setWidths(headerwidths);
// if (docPdf.getPageNumber() > 1) {
// table1.setHeaderRows(4);
// }
table1.setWidthPercentage((100));
table1.getDefaultCell().setBorder(Rectangle.BOTTOM);
table1.getDefaultCell().setColspan(7);
Phrase phrase = new Phrase();
try {
table1.getDefaultCell().setBorder(Rectangle.BOX);
table1.getDefaultCell().setBorderColor(java.awt.Color.BLACK);
table1.getDefaultCell().setColspan(2);
table1.getDefaultCell().setFixedHeight(70);
table1.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_CENTER);
table1.addCell(Image.getInstance(com.afrisoftech.lib.CompanyLogo.getPath2Logo()));
table1.getDefaultCell().setFixedHeight(16);
java.sql.PreparedStatement st321 =
connectDB.prepareStatement("select header_name from pb_header");
java.sql.ResultSet rset3 = st321.executeQuery();
table1.getDefaultCell().setBorder(Rectangle.BOX);
table1.getDefaultCell().setBorderColor(java.awt.Color.BLACK);
while (rset3.next()) {
table1.getDefaultCell().setColspan(5);
table1.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_CENTER);
phrase = new Phrase(rset3.getObject(1).toString().toUpperCase(), pFontHeader1);
table1.addCell(phrase);
}
// table.addCell(phrase);
table1.getDefaultCell().setColspan(1);
table1.getDefaultCell().setBackgroundColor(java.awt.Color.WHITE);
table1.getDefaultCell().setBorderColor(java.awt.Color.WHITE);
// try {
java.sql.Statement st3 = connectDB.createStatement();
java.sql.Statement st2x = connectDB.createStatement();
java.sql.ResultSet rset2x =
st2x.executeQuery("SELECT rep_currency from pb_hospitalprofile");
while (rset2x.next()) {
ks = rset2x.getObject(1).toString();
}
} catch (java.sql.SQLException SqlExec) {
SqlExec.printStackTrace();
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), SqlExec.getMessage());
}
docPdf.add(table1);
} catch (com.lowagie.text.BadElementException BadElExec) {
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), BadElExec.getMessage());
}
try {
java.util.Calendar calendar = java.util.Calendar.getInstance();
long dateNow = calendar.getTimeInMillis();
java.sql.Date datenowSql = new java.sql.Date(dateNow);
System.out.println(datenowSql.toString());
// java.lang.Object listofStaffNos[] = this.getListofStaffNos();
com.lowagie.text.pdf.PdfPTable table = new com.lowagie.text.pdf.PdfPTable(11);
// com.lowagie.text.Table table = new com.lowagie.text.Table(7);
// table.endHeaders();
int headerwidths[] = {12, 12, 30, 10, 12, 12, 12, 12, 13, 13, 15};
table.setWidths(headerwidths);
// if (docPdf.getPageNumber() > 1) {
table.setHeaderRows(8);
// }
table.setWidthPercentage((100));
table.getDefaultCell().setBorder(Rectangle.BOTTOM);
table.getDefaultCell().setColspan(8);
Phrase phrase = new Phrase();
// table.addCell(phrase);
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setBackgroundColor(java.awt.Color.WHITE);
table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);
try {
java.sql.Statement st22 = connectDB.createStatement();
java.sql.Statement st11 = connectDB.createStatement();
java.sql.Statement st = connectDB.createStatement();
java.sql.Statement st1 = connectDB.createStatement();
java.sql.Statement st2 = connectDB.createStatement();
java.sql.Statement st3 = connectDB.createStatement();
java.sql.Statement st4 = connectDB.createStatement();
java.sql.Statement st41 = connectDB.createStatement();
java.sql.Statement st5 = connectDB.createStatement();
java.sql.ResultSet rset3 =
st3.executeQuery(
"select hospital_name,postal_code||' '||box_no||' '||town,main_telno||' '||other_telno,initcap(street),main_faxno,email,website,room_no from pb_hospitalprofile");
java.sql.ResultSet rset4 =
st4.executeQuery(
"select scheme_name,account_no,tel_main,address from ac_schemes where account_no = '"
+ memNo1
+ "' ORDER BY 1 ASC");
java.sql.ResultSet rset41 =
st41.executeQuery(
"select payer_name from ac_schemes where account_no = '"
+ memNo1
+ "' ORDER BY 1 ASC");
java.sql.ResultSet rset1 =
st1.executeQuery(
"select date,admission_no, upper(item),invoice_no ||' '||receipt_no,journal_no,debit,balance-credit_bal, (select distinct f_name || ' '|| l_name from secure_menu_access where login_name = ac_debtors.user_name limit 1), reference_no from ac_debtors where account_no = '"
+ memNo1
+ "' AND date::date BETWEEN '"
+ beginDate
+ "' AND '"
+ endDate
+ "' and (balance > 0 or credit_bal > 0) order by date,invoice_no"); // UNION select pd.date::date,initcap(pd.scheme_staff_no), (sh.first_name||' '||sh.second_name||' '||sh.last_name) as name,pd.reference,sum(pd.credit),pd.patient_no from hp_patient_card pd,hp_inpatient_register sh where pd.patient_no = sh.patient_no and pd.isurer = '"+memNo+"' AND pd.date::date BETWEEN '"+beginDate+"' AND '"+endDate+"' and pd.credit > 0 group by pd.date::date,pd.scheme_staff_no, name,pd.reference,pd.invoice_no,pd.patient_no order by pd.invoice_no");
java.sql.ResultSet rsetTotals2 =
st22.executeQuery(
"select sum(balance-credit_bal) from ac_debtors where account_no = '"
+ memNo1
+ "' AND date::date < '"
+ beginDate
+ "'");
table.getDefaultCell().setBorderColor(java.awt.Color.BLACK);
table.getDefaultCell().setBorderWidth(Rectangle.TOP | Rectangle.BOTTOM);
table.getDefaultCell().setColspan(11);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_CENTER);
phrase = new Phrase("Statement of Account", pFontHeader11);
table.addCell(phrase);
while (rset4.next()) {
table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);
table.getDefaultCell().setColspan(11);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase(dbObject.getDBObject(rset4.getObject(1), "-"), pFontHeader1);
table.addCell(phrase);
while (rset41.next()) {
table.getDefaultCell().setColspan(11);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase(dbObject.getDBObject(rset41.getObject(1), "-"), pFontHeader1);
table.addCell(phrase);
}
table.getDefaultCell().setColspan(11);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase =
new Phrase(
"Account No : " + dbObject.getDBObject(rset4.getObject(2), "-"),
pFontHeader1);
table.addCell(phrase);
phrase =
new Phrase(
dbObject.getDBObject("Tel : " + rset4.getObject(3), "-"), pFontHeader1);
table.addCell(phrase);
phrase = new Phrase(dbObject.getDBObject(rset4.getObject(4), "-"), pFontHeader1);
table.addCell(phrase);
}
table.getDefaultCell().setColspan(5);
try {
java.text.DateFormat dateFormat =
java.text.DateFormat.getDateInstance(java.text.DateFormat.MEDIUM); // MEDIUM);
java.util.Date endDate1 =
dateFormat.parse(endDate.toLocaleString()); // dateInstance.toLocaleString());
java.util.Date endDate11 =
dateFormat.parse(beginDate.toLocaleString()); // dateInstance.toLocaleString());
System.out.println("" + endDate1);
// phrase = new Phrase(bank +" Report: " +dateFormat.format(formattedDate),
// pFontHeader);
// table.addCell(phrase);
phrase =
new Phrase(
"Period : "
+ dateFormat.format(endDate11)
+ " - "
+ dateFormat.format(endDate1),
pFontHeader1);
table.addCell(phrase);
} catch (java.text.ParseException psExec) {
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), psExec.getMessage());
}
// table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
// phrase = new Phrase("Period : '"+beginDate+"' - '"+endDate+"'", pFontHeader1);
// table.addCell(phrase);
table.getDefaultCell().setColspan(6);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase("Date " + datenowSql, pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setColspan(3);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
table.getDefaultCell().setBorderColor(java.awt.Color.BLACK);
table.getDefaultCell().setBorderWidth(Rectangle.TOP);
table.getDefaultCell().setColspan(1);
phrase = new Phrase("Date", pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setColspan(1);
phrase = new Phrase("Patient No.", pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setColspan(1);
phrase = new Phrase("Name", pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setColspan(1);
phrase = new Phrase("Invoice No.", pFontHeader1);
table.addCell(phrase);
phrase = new Phrase("Member No.", pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setColspan(1);
phrase = new Phrase("Claim No..", pFontHeader1);
table.addCell(phrase);
phrase = new Phrase("Ward Name", pFontHeader1);
table.addCell(phrase);
phrase = new Phrase("Prepared By", pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
table.getDefaultCell().setColspan(1);
phrase = new Phrase("Orig. Amt ", pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setColspan(1);
phrase = new Phrase("Curr. Amt ", pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setColspan(1);
phrase = new Phrase("Running Amt. " + ks, pFontHeader1);
table.addCell(phrase);
// table
double osBalancex = 0.00;
double osBalancex1 = 0.00;
while (rsetTotals2.next()) {
table.getDefaultCell().setColspan(5);
table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase("BAL/BF", pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
table.getDefaultCell().setColspan(6);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(rsetTotals2.getString(1)),
pFontHeader1);
osBalance = osBalance + rsetTotals2.getDouble(1);
table.addCell(phrase);
}
while (rset1.next()) {
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase(dbObject.getDBObject(rset1.getObject(1), "-"), pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase(dbObject.getDBObject(rset1.getObject(2), "-"), pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase(dbObject.getDBObject(rset1.getObject(3), "-"), pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase(dbObject.getDBObject(rset1.getObject(4), "-"), pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase(dbObject.getDBObject(rset1.getObject(5), "-"), pFontHeader);
table.addCell(phrase);
phrase = new Phrase(dbObject.getDBObject(rset1.getObject(9), "-"), pFontHeader);
table.addCell(phrase);
java.sql.PreparedStatement pstmt =
connectDB.prepareStatement(
"SELECT distinct main_service from hp_patient_card where patient_no = ? AND service ilike '%daily bed%' order by main_service desc limit 1");
pstmt.setString(1, rset1.getString(2));
java.sql.ResultSet rsets = pstmt.executeQuery();
String wardName = "-";
// System.out.println("Printing visit_id number ");
while (rsets.next()) {
System.out.println("Ward name = [" + rsets.getString(1) + "]");
wardName = rsets.getString(1);
}
rsets.close();
pstmt.close();
phrase = new Phrase(wardName, pFontHeader);
table.addCell(phrase);
phrase =
new Phrase(
dbObject.getDBObject(rset1.getObject(8), "-").toUpperCase(), pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(rset1.getString(6)),
pFontHeader);
table.addCell(phrase);
osBalancex = osBalancex + rset1.getDouble(6);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(rset1.getString(7)),
pFontHeader);
osBalancex1 = osBalancex1 + rset1.getDouble(7);
osBalance = osBalance + rset1.getDouble(7);
table.addCell(phrase);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(osBalance)),
pFontHeader);
table.addCell(phrase);
}
table.getDefaultCell().setColspan(2);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase = new Phrase("", pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setBorderColor(java.awt.Color.BLACK);
table.getDefaultCell().setBorder(Rectangle.BOTTOM | Rectangle.TOP);
// while (rsetTotals.next()) {
table.getDefaultCell().setColspan(6);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase("Total", pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(osBalancex)),
pFontHeader);
table.addCell(phrase);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(osBalancex1)),
pFontHeader);
table.addCell(phrase);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(osBalance)),
pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(6);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase(" '' ", pFontHeader);
table.addCell(phrase);
docPdf.add(table);
double Totals = 0.00;
try {
com.lowagie.text.pdf.PdfPTable table12 =
new com.lowagie.text.pdf.PdfPTable(rangeDates.length + 4);
String headerWidths = null;
java.util.Vector headerVector = new java.util.Vector(1, 1);
int z = rangeDates.length;
int headerwidth[] = {22, 13, 13, 13, 13, 13, 13, 13}; // ,13,13};
table12.setWidths(headerwidth);
table12.setWidthPercentage((100));
table12.setHeaderRows(2);
table12.getDefaultCell().setBorder(Rectangle.BOTTOM);
//
table.getDefaultCell().setColspan(8);
Phrase phraseA = new Phrase("");
table12.getDefaultCell().setColspan(1);
phraseA = new Phrase("AGEING DETAILS", pFontHeader1);
table12.addCell(phraseA);
interval = 0;
for (int x = rangeDates.length - 1; x >= 0; x--) {
table12.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
try {
// Date parser
table12.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
java.text.SimpleDateFormat dateFormat =
new java.text.SimpleDateFormat("EEE MMM dd HH:mm:ss zzz yyyy");
com.afrisoftech.lib.DateFormatter dateFormatter =
new com.afrisoftech.lib.DateFormatter(
dateFormat.parse(rangeDates[x][0].toString().trim()), "MMM/yy");
java.lang.String monthString = dateFormatter.getDateString();
int days = 1;
phraseA = new Phrase(monthString, pFontHeader1);
table12.addCell(phraseA);
} catch (java.text.ParseException prs) {
prs.printStackTrace();
}
interval++;
}
phraseA = new Phrase("+ " + (interval * 30) + " Days", pFontHeader1);
table12.addCell(phraseA);
phraseA = new Phrase("Un Alloc.", pFontHeader1);
table12.addCell(phraseA);
phraseA = new Phrase("O/S " + ks, pFontHeader1);
table12.addCell(phraseA);
table12.getDefaultCell().setBackgroundColor(java.awt.Color.WHITE);
table12.getDefaultCell().setBorderColor(java.awt.Color.WHITE);
try {
double GrandTotal = 0.00;
double Over120Total = 0.00;
java.lang.Object[] listofAct = this.getListofActivities();
// java.sql.Connection conDb1 =
// java.sql.DriverManager.getConnection("jdbc:postgresql://localhost:5432/sako","postgres","pilsiner");
System.out.println(listofAct.length);
// for (int i = 0; i < listofAct.length; i++) {
double TurnOver = 0.00;
double Over120 = 0.00;
double TotalCount = 0.00;
table12.getDefaultCell().setColspan(1);
java.sql.Statement stmta1 = connectDB.createStatement();
java.sql.PreparedStatement pSeta1 =
connectDB.prepareStatement(
"SELECT count(distinct(account_no)) FROM ac_debtors where account_no IS NOT NULL and account_no ='"
+ memNo1
+ "'");
java.sql.PreparedStatement pset22 =
connectDB.prepareStatement(
"select distinct account_no||' '||scheme_name from ac_schemes WHERE account_no = '"
+ memNo1
+ "' "); // < '"+endDate+"'::date and date > '"+endDate+"'::date - 30
// group by dealer");
// pset22.setString(1,""+memNo1.toUpperCase());
java.sql.ResultSet rSeta1 = pSeta1.executeQuery();
while (rSeta1.next()) {
name = rSeta1.getInt(1);
}
if (name > 0) {
java.sql.ResultSet rset22 = pset22.executeQuery();
while (rset22.next()) {
table12.getDefaultCell().setColspan(1);
// phraseA = new Phrase(dbObject.getDBObject(rset22.getObject(1),
// "-"),pFontHeader1);
phraseA = new Phrase(dbObject.getDBObject(" ", "-"), pFontHeader1);
table12.addCell(phraseA);
// columnTotals[t] = columnTotals[t] + rset.getDouble(1);
}
} else {
phraseA = new Phrase("-", pFontHeader1);
table12.addCell(phraseA);
}
// java.sql.Statement st2 = connectDB.createStatement();
table12.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
java.sql.Statement st2B = connectDB.createStatement();
java.sql.Statement st21 = connectDB.createStatement();
java.sql.Statement st22A = connectDB.createStatement();
java.sql.Statement st23 = connectDB.createStatement();
java.sql.Statement st211 = connectDB.createStatement();
java.sql.Statement st221 = connectDB.createStatement();
java.sql.Statement st233 = connectDB.createStatement();
java.sql.Statement st2A = connectDB.createStatement();
java.sql.Statement st2B1 = connectDB.createStatement();
java.sql.Statement st2C = connectDB.createStatement();
java.sql.Statement stc = connectDB.createStatement();
System.out.println("Dealer Is : '" + memNo1 + "'");
java.sql.PreparedStatement pset1 =
connectDB.prepareStatement(
"select sum(balance) from ac_debtors WHERE account_no = '"
+ memNo1
+ "' AND dealer IS NOT NULL AND date < '"
+ rangeDates[0][0]
+ "'"); // < '"+endDate+"'::date and date > '"+endDate+"'::date - 30
// group by dealer");
// pset1.setString(1,""+memNo1);
java.sql.ResultSet rset1A = pset1.executeQuery();
java.sql.PreparedStatement pset112 =
connectDB.prepareStatement(
"select sum(credit_bal) from ac_debtors WHERE account_no = ? AND date <= '"
+ endDate
+ "'"); // < '"+endDate+"'::date and date > '"+endDate+"'::date - 30
// group by dealer");
pset112.setString(1, memNo1);
java.sql.ResultSet rset112 = pset112.executeQuery();
for (int t = rangeDates.length - 1; t >= 0; t--) {
java.sql.Statement st81 = connectDB.createStatement();
java.sql.Statement st01 = connectDB.createStatement();
java.sql.ResultSet rset01 =
st01.executeQuery(
"select count(balance) from ac_debtors WHERE account_no = '"
+ memNo1
+ "' AND dealer IS NOT NULL AND date between '"
+ rangeDates[t][0]
+ "' AND '"
+ rangeDates[t][1]
+ "'");
java.sql.PreparedStatement pset =
connectDB.prepareStatement(
"select sum(balance) from ac_debtors WHERE account_no = '"
+ memNo1
+ "' AND dealer IS NOT NULL AND date between '"
+ rangeDates[t][0]
+ "' AND '"
+ rangeDates[t][1]
+ "'"); // < '"+endDate+"'::date and date > '"+endDate+"'::date - 30
// group by dealer");
while (rset01.next()) {
over = rset01.getInt(1);
}
if (t == 3) {
java.sql.PreparedStatement pset1x =
connectDB.prepareStatement(
"select sum(balance) from ac_debtors WHERE account_no = '"
+ memNo1
+ "' AND dealer IS NOT NULL AND date between '"
+ rangeDates[t][0]
+ "' AND '"
+ endDate
+ "'"); // < '"+endDate+"'::date and date > '"+endDate+"'::date -
// 30 group by dealer");
System.out.print(rangeDates[t][0] + " AND " + endDate);
if (over > 0) {
// pset.setString(1,""+memNo1.toString().toUpperCase());
java.sql.ResultSet rset = pset1x.executeQuery();
while (rset.next()) {
table12.getDefaultCell().setColspan(1);
table12.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phraseA =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(rset.getString(1)),
pFontHeader1);
table12.addCell(phraseA);
columnTotals[t] = columnTotals[t] + rset.getDouble(1);
TotalCount = TotalCount + rset.getDouble(1);
}
} else {
table12.getDefaultCell().setColspan(1);
table12.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phraseA =
new Phrase(
new com.afrisoftech.sys.Format2Currency().Format2Currency("0.00"),
pFontHeader1);
table12.addCell(phraseA);
}
} else {
if (over > 0) {
// pset.setString(1,""+memNo1.toString().toUpperCase());
java.sql.ResultSet rset = pset.executeQuery();
while (rset.next()) {
table12.getDefaultCell().setColspan(1);
table12.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phraseA =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(rset.getString(1)),
pFontHeader1);
table12.addCell(phraseA);
columnTotals[t] = columnTotals[t] + rset.getDouble(1);
TotalCount = TotalCount + rset.getDouble(1);
}
} else {
table12.getDefaultCell().setColspan(1);
table12.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phraseA =
new Phrase(
new com.afrisoftech.sys.Format2Currency().Format2Currency("0.00"),
pFontHeader1);
table12.addCell(phraseA);
}
}
}
// GrandTotal = GrandTotal + TotalCount;
while (rset1A.next()) {
System.out.println("This Result " + rset1A.getString(1));
table12.getDefaultCell().setColspan(1);
table12.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phraseA =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(rset1A.getString(1)),
pFontHeader1);
Over120 = rset1A.getDouble(1);
table12.addCell(phraseA);
Over120Total = Over120Total + rset1A.getDouble(1);
TurnOver = TotalCount + Over120;
}
while (rset112.next()) {
phraseA =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(rset112.getString(1)),
pFontHeader1);
table12.addCell(phraseA);
OS = OS + rset112.getDouble(1);
}
// Over120 = Over120+Over120;
phraseA =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(TurnOver - OS)),
pFontHeader1);
table12.addCell(phraseA);
Totals = Totals + TurnOver;
// }
table12.getDefaultCell().setBorderColor(java.awt.Color.BLACK);
table12.getDefaultCell().setBorder(Rectangle.BOTTOM | Rectangle.TOP);
// while (rsetTotals.next()) {
table12.getDefaultCell().setColspan(1);
table12.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phraseA = new Phrase("Total", pFontHeader);
table12.addCell(phraseA);
table12.getDefaultCell().setColspan(1);
table12.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
// phrase = new Phrase(new
// com.afrisoftech.sys.Format2Currency().Format2Currency(rsetTotals.getString(1)),
// pFontHeader);
for (int x = rangeDates.length - 1; x >= 0; x--) {
// phrase = new Phrase("Current"+2*x,pFontHeader);
// table.addCell(phrase);
phraseA =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(columnTotals[x])),
pFontHeader);
table12.addCell(phraseA);
columnTo = columnTo + columnTotals[x];
}
phraseA =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(Over120Total)),
pFontHeader);
table12.addCell(phraseA);
phraseA =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(OS)),
pFontHeader);
table12.addCell(phraseA);
phraseA =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(
java.lang.String.valueOf((columnTo + Over120Total) - OS)),
pFontHeader);
table12.addCell(phraseA);
try {
java.sql.Statement st31 = connectDB.createStatement();
// java.sql.Statement st4 = connectDB.createStatement();
java.sql.ResultSet rset2 = st31.executeQuery("select name from pb_notice");
while (rset2.next()) {
Messg = rset2.getString(1);
}
com.lowagie.text.HeaderFooter footer1 =
new com.lowagie.text.HeaderFooter(
new Phrase("" + Messg + ""),
false); // FontFactory.getFont(com.lowagie.text.FontFactory.HELVETICA,
// 14, Font.BOLDITALIC,java.awt.Color.blue)));
docPdf.setFooter(footer1);
} catch (java.sql.SQLException SqlExec) {
SqlExec.printStackTrace();
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), SqlExec.getMessage());
}
docPdf.add(table12);
} catch (java.sql.SQLException SqlExec) {
SqlExec.printStackTrace();
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), SqlExec.getMessage());
}
} catch (com.lowagie.text.BadElementException BadElExec) {
// Bad
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), BadElExec.getMessage());
}
// docPdf.add(table);
} catch (java.sql.SQLException SqlExec) {
SqlExec.printStackTrace();
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), SqlExec.getMessage());
}
// } // }
} catch (com.lowagie.text.BadElementException BadElExec) {
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), BadElExec.getMessage());
}
} catch (java.io.FileNotFoundException fnfExec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), fnfExec.getMessage());
}
} catch (com.lowagie.text.DocumentException lwDocexec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), lwDocexec.getMessage());
}
docPdf.close();
docPdf.close();
com.afrisoftech.lib.PDFRenderer.renderPDF(tempFile);
} catch (java.io.IOException IOexec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), IOexec.getMessage());
}
}
public void generatePdf() {
java.lang.Process wait_for_Pdf2Show;
java.util.Calendar cal = java.util.Calendar.getInstance();
java.util.Date dateStampPdf = cal.getTime();
java.lang.String pdfDateStamp = dateStampPdf.toString();
try {
java.io.File tempFile =
java.io.File.createTempFile("REP" + this.getDateLable() + "_", ".pdf");
tempFile.deleteOnExit();
java.lang.Runtime rt = java.lang.Runtime.getRuntime();
java.lang.String debitTotal = null;
java.lang.String creditTotal = null;
com.lowagie.text.Document docPdf = new com.lowagie.text.Document();
try {
try {
com.lowagie.text.pdf.PdfWriter.getInstance(
docPdf, new java.io.FileOutputStream(tempFile));
String compName = null;
String date = null;
com.lowagie.text.HeaderFooter footer =
new com.lowagie.text.HeaderFooter(
new Phrase("Family planning summary - Page: ", pFontHeader),
true); // FontFactory.getFont(com.lowagie.text.FontFactory.HELVETICA, 12,
// Font.BOLDITALIC,java.awt.Color.blue));
docPdf.setFooter(footer);
docPdf.open();
try {
java.util.Calendar calendar = java.util.Calendar.getInstance();
long dateNow = calendar.getTimeInMillis();
java.sql.Date datenowSql = new java.sql.Date(dateNow);
System.out.println(datenowSql.toString());
// java.lang.Object listofStaffNos[] = this.getListofStaffNos();
com.lowagie.text.pdf.PdfPTable table1 = new com.lowagie.text.pdf.PdfPTable(8);
int headerwidths[] = {12, 15, 30, 15, 10, 15, 10, 18};
table1.setWidths(headerwidths);
table1.setWidthPercentage((100));
table1.getDefaultCell().setBorder(Rectangle.BOTTOM);
table1.getDefaultCell().setColspan(8);
Phrase phrase = new Phrase();
// table.addCell(phrase);
table1.getDefaultCell().setColspan(1);
table1.getDefaultCell().setBackgroundColor(java.awt.Color.WHITE);
table1.getDefaultCell().setBorderColor(java.awt.Color.WHITE);
try {
java.sql.Statement st3 = connectDB.createStatement();
java.sql.ResultSet rset3 =
st3.executeQuery("select header_name ,current_date from pb_header");
while (rset3.next()) {
table1.getDefaultCell().setColspan(8);
table1.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_CENTER);
phrase = new Phrase(rset3.getObject(1).toString().toUpperCase(), pFontHeader);
table1.addCell(phrase);
date = rset3.getObject(2).toString();
}
// table1.addCell(phrase);
} catch (java.sql.SQLException SqlExec) {
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), SqlExec.getMessage());
}
docPdf.add(table1);
} catch (com.lowagie.text.BadElementException BadElExec) {
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), BadElExec.getMessage());
}
try {
com.lowagie.text.pdf.PdfPTable table = new com.lowagie.text.pdf.PdfPTable(8);
int headerwidths[] = {20, 32, 9, 9, 9, 9, 2, 10};
table.setWidths(headerwidths);
table.setWidthPercentage((100));
table.setHeaderRows(2);
table.getDefaultCell().setBorder(Rectangle.BOTTOM);
Phrase phrase;
try {
table.getDefaultCell().setColspan(4);
java.text.DateFormat dateFormat =
java.text.DateFormat.getDateInstance(java.text.DateFormat.MEDIUM); // MEDIUM);
java.util.Date endDate1 =
dateFormat.parse(endDate.toLocaleString()); // dateInstance.toLocaleString());
java.util.Date endDate11 =
dateFormat.parse(beginDate.toLocaleString()); // dateInstance.toLocaleString());
phrase =
new Phrase(
"CONTRACEPTIVES PER CLIENT TYPE : "
+ dateFormat.format(endDate11)
+ " - "
+ dateFormat.format(endDate1),
pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(4);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase = new Phrase("Printed On :" + date, pFontHeader);
table.addCell(phrase);
} catch (java.text.ParseException psExec) {
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), psExec.getMessage());
}
table.getDefaultCell().setColspan(1);
// table.getDefaultCell().setBackgroundColor(java.awt.Color.LIGHT_GRAY);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase("CODE", pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(1);
phrase = new Phrase("DESCRIPTION", pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
table.getDefaultCell().setColspan(1);
phrase = new Phrase("NA", pFontHeader);
table.addCell(phrase);
phrase = new Phrase("CA", pFontHeader);
table.addCell(phrase);
phrase = new Phrase("R", pFontHeader);
table.addCell(phrase);
phrase = new Phrase("O", pFontHeader);
// table.addCell(phrase);
table.getDefaultCell().setColspan(1);
phrase = new Phrase("Total", pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(2);
phrase = new Phrase("Cumm.", pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setBackgroundColor(java.awt.Color.WHITE);
table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);
double occ = 0.00;
double total = 0.00;
try {
int osBalance = 0;
int ca1 = 0;
int nr = 0;
int cr = 0;
int rr = 0;
int rro = 0;
int rrx = 0;
java.lang.Object[] listofActx = this.getListofActivities1();
for (int y = 0; y < listofActx.length; y++) {
int cum = 0;
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
table.getDefaultCell().setColspan(8);
phrase = new Phrase(listofActx[y].toString().toUpperCase(), pFontHeader);
table.addCell(phrase);
java.lang.Object[] listofAct = this.getListofActivities(listofActx[y]);
System.out.println(listofAct.length);
for (int i = 0; i < listofAct.length; i++) {
int r = 0;
int na = 0;
int ca = 0;
int ra = 0;
int rax = 0;
int ro = 0;
String pnca = null;
String pnca1 = null;
String pnca1x = null;
java.sql.Statement st3 = connectDB.createStatement();
java.sql.Statement st31 = connectDB.createStatement();
java.sql.ResultSet rset3 =
st3.executeQuery(
"SELECT DISTINCT COUNT(patient_no) FROM fprecord WHERE cotraceptive = '"
+ listofAct[i]
+ "' AND date_seen::date BETWEEN '"
+ beginDate
+ "' AND '"
+ endDate
+ "' AND PATIENT_TYPE ILIKE 'NA' AND fp_method not ilike 'Consultation%'");
java.sql.ResultSet rset31 =
st31.executeQuery(
"SELECT DISTINCT cotraceptive FROM fprecord WHERE cotraceptive = '"
+ listofAct[i]
+ "' AND date_seen::date BETWEEN '"
+ beginDate
+ "' AND '"
+ endDate
+ "'");
while (rset31.next()) {
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
table.getDefaultCell().setColspan(1);
phrase = new Phrase(" ", pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(1);
phrase =
new Phrase(
dbObject.getDBObject(rset31.getObject(1).toString().toUpperCase(), "-"),
pFontHeader1);
table.addCell(phrase);
while (rset3.next()) {
na = rset3.getInt(1);
nr = nr + rset3.getInt(1);
}
if (na > 0) {
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase = new Phrase(java.lang.String.valueOf(na), pFontHeader1);
table.addCell(phrase);
na = na;
} else {
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase = new Phrase(java.lang.String.valueOf(0), pFontHeader1);
table.addCell(phrase);
na = 0;
}
java.sql.Statement st32c = connectDB.createStatement();
java.sql.ResultSet rset32c =
st32c.executeQuery(
"SELECT DISTINCT patient_no FROM fprecord WHERE date_seen::date BETWEEN '"
+ beginDate
+ "' AND '"
+ endDate
+ "' AND PATIENT_TYPE = 'CA' AND fp_method not ilike 'Consultation%' GROUP BY patient_no EXCEPT SELECT patient_no FROM fprecord WHERE date_seen::date BETWEEN '"
+ beginDate
+ "' AND '"
+ endDate
+ "' AND PATIENT_TYPE = 'NA' group by patient_no");
while (rset32c.next()) {
pnca = rset32c.getString(1);
java.sql.Statement st32 = connectDB.createStatement();
// java.sql.ResultSet rset32 = st32.executeQuery("SELECT DISTINCT
// COUNT(patient_no) FROM fprecord WHERE cotraceptive ilike
// '"+listofAct[i]+"' AND date_seen::date BETWEEN '"+beginDate+"' AND
// '"+endDate+"' AND PATIENT_TYPE ILIKE 'CA' AND fp_method not ilike
// 'Consultation%' AND patient_no = '"+pnca+"' GROUP BY patient_type EXCEPT
// SELECT COUNT(patient_no) FROM fprecord WHERE date_seen::date BETWEEN
// '"+beginDate+"' AND '"+endDate+"' AND cotraceptive ilike '"+listofAct[i]+"'
// AND PATIENT_TYPE ILIKE 'NA' AND PATIENT_TYPE ILIKE 'R' AND fp_method not
// ilike 'Consultation%' AND patient_no = '"+pnca+"'");
java.sql.ResultSet rset32 =
st32.executeQuery(
"SELECT DISTINCT COUNT(patient_no) FROM fprecord WHERE cotraceptive = '"
+ listofAct[i]
+ "' AND date_seen::date BETWEEN '"
+ beginDate
+ "' AND '"
+ endDate
+ "' AND PATIENT_TYPE = 'CA' AND fp_method not ilike 'Consultation%' AND patient_no = '"
+ pnca
+ "' GROUP BY patient_no LIMIT 1");
while (rset32.next()) {
if (rset32.getInt(1) > 1) {
ca = ca + 0;
cr = cr + 0;
} else {
ca = ca + rset32.getInt(1);
cr = cr + rset32.getInt(1);
}
// phrase = new Phrase(java.lang.String.valueOf(r), pFontHeader1);
// table.addCell(phrase);
}
}
if (ca > 0) {
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase = new Phrase(java.lang.String.valueOf(ca), pFontHeader1);
table.addCell(phrase);
ca = ca;
} else {
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase = new Phrase(java.lang.String.valueOf(0), pFontHeader1);
table.addCell(phrase);
ca = 0;
}
// java.sql.ResultSet rset32 = st32.executeQuery("SELECT COUNT(patient_no) FROM
// fprecord WHERE cotraceptive ilike '"+listofAct[i]+"' AND date_seen::date
// BETWEEN '"+beginDate+"' AND '"+endDate+"' AND PATIENT_TYPE ILIKE 'CA' AND
// fp_method not ilike 'Consultation%' EXCEPT SELECT COUNT(patient_no) FROM
// fprecord WHERE date_seen::date BETWEEN '"+beginDate+"' AND '"+endDate+"' AND
// PATIENT_TYPE ILIKE 'NA' AND PATIENT_TYPE ILIKE 'R' AND fp_method not ilike
// 'Consultation%'");
// java.sql.ResultSet rset32 = st32.executeQuery("SELECT DISTINCT
// COUNT(patient_no) FROM fprecord WHERE cotraceptive ilike '"+listofAct[i]+"'
// AND date_seen::date BETWEEN '"+beginDate+"' AND '"+endDate+"' AND
// PATIENT_TYPE ILIKE 'CA' AND fp_method not ilike 'Consultation%'");
java.sql.Statement st32c1 = connectDB.createStatement();
java.sql.ResultSet rset32c1 =
st32c1.executeQuery(
"SELECT DISTINCT patient_no FROM fprecord WHERE date_seen::date BETWEEN '"
+ beginDate
+ "' AND '"
+ endDate
+ "' AND PATIENT_TYPE = 'R' AND fp_method not ilike 'Consultation%' GROUP BY patient_no EXCEPT SELECT patient_no FROM fprecord WHERE date_seen::date BETWEEN '"
+ beginDate
+ "' AND '"
+ endDate
+ "' AND (PATIENT_TYPE = 'NA' OR PATIENT_TYPE = 'CA') group by patient_no");
while (rset32c1.next()) {
pnca1 = rset32c1.getString(1);
java.sql.Statement st33 = connectDB.createStatement();
// java.sql.ResultSet rset33 = st33.executeQuery("SELECT COUNT(patient_no)
// FROM fprecord WHERE cotraceptive ilike '"+listofAct[i]+"%' AND
// date_seen::date BETWEEN '"+beginDate+"' AND '"+endDate+"' AND PATIENT_TYPE
// ILIKE 'R' EXCEPT SELECT COUNT(patient_no) FROM fprecord WHERE cotraceptive
// ilike '"+listofAct[i]+"' AND date_seen::date BETWEEN '"+beginDate+"' AND
// '"+endDate+"' AND (PATIENT_TYPE ILIKE 'NA' OR PATIENT_TYPE ILIKE 'CA')");
java.sql.ResultSet rset33 =
st33.executeQuery(
"SELECT DISTINCT COUNT(patient_no) FROM fprecord WHERE cotraceptive = '"
+ listofAct[i]
+ "' AND date_seen::date BETWEEN '"
+ beginDate
+ "' AND '"
+ endDate
+ "' AND patient_no = '"
+ pnca1
+ "' AND fp_method not ilike 'Consultation%' GROUP BY patient_no");
java.sql.Statement st331 = connectDB.createStatement();
while (rset33.next()) {
if (rset33.getInt(1) > 1) {
ra = ra + 0;
rr = rr + 0;
} else {
ra = ra + rset33.getInt(1);
rr = rr + rset33.getInt(1);
}
}
}
java.sql.Statement st32cx = connectDB.createStatement();
java.sql.ResultSet rset32cx =
st32cx.executeQuery(
"SELECT DISTINCT patient_no FROM fprecord WHERE date_seen::date BETWEEN '"
+ beginDate
+ "' AND '"
+ endDate
+ "' AND cotraceptive = '"
+ listofAct[i]
+ "' AND fp_method ilike 'Consult%' group by patient_no EXCEPT SELECT DISTINCT patient_no FROM fprecord WHERE date_seen::date BETWEEN '"
+ beginDate
+ "' AND '"
+ endDate
+ "' AND fp_method not ilike 'Consult%' group by patient_no");
while (rset32cx.next()) {
pnca1x = rset32cx.getString(1);
java.sql.Statement st33x = connectDB.createStatement();
// java.sql.ResultSet rset33 = st33.executeQuery("SELECT COUNT(patient_no)
// FROM fprecord WHERE cotraceptive ilike '"+listofAct[i]+"%' AND
// date_seen::date BETWEEN '"+beginDate+"' AND '"+endDate+"' AND PATIENT_TYPE
// ILIKE 'R' EXCEPT SELECT COUNT(patient_no) FROM fprecord WHERE cotraceptive
// ilike '"+listofAct[i]+"' AND date_seen::date BETWEEN '"+beginDate+"' AND
// '"+endDate+"' AND (PATIENT_TYPE ILIKE 'NA' OR PATIENT_TYPE ILIKE 'CA')");
java.sql.ResultSet rset33x =
st33x.executeQuery(
"SELECT DISTINCT COUNT(patient_no) FROM fprecord WHERE cotraceptive = '"
+ listofAct[i]
+ "' AND date_seen::date BETWEEN '"
+ beginDate
+ "' AND '"
+ endDate
+ "' AND patient_no = '"
+ pnca1x
+ "' AND fp_method ilike 'Consultation' GROUP BY patient_no");
while (rset33x.next()) {
if (rset33x.getInt(1) > 1) {
rax = rax + 0;
rrx = rrx + 0;
} else {
rax = rax + rset33x.getInt(1);
rrx = rrx + rset33x.getInt(1);
}
}
}
if (ra > 0 || rax > 0) {
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase = new Phrase(java.lang.String.valueOf(ra + rax), pFontHeader1);
table.addCell(phrase);
ra = ra + rax;
} else {
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase = new Phrase(java.lang.String.valueOf(0), pFontHeader1);
table.addCell(phrase);
ra = 0;
}
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase = new Phrase(java.lang.String.valueOf(na + ca + ra), pFontHeader1);
table.addCell(phrase);
cum = cum + na + ca + ra;
table.getDefaultCell().setColspan(2);
phrase = new Phrase(java.lang.String.valueOf(cum), pFontHeader1);
table.addCell(phrase);
}
}
}
table.getDefaultCell().setBorderColor(java.awt.Color.BLACK);
table.getDefaultCell().setBorder(Rectangle.BOTTOM | Rectangle.TOP);
// while (rsetTotals.next()) {
table.getDefaultCell().setColspan(2);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase("Total", pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase = new Phrase(java.lang.String.valueOf(nr), pFontHeader);
table.addCell(phrase);
phrase = new Phrase(java.lang.String.valueOf(cr), pFontHeader);
table.addCell(phrase);
phrase = new Phrase(java.lang.String.valueOf(rr + rrx), pFontHeader);
table.addCell(phrase);
phrase = new Phrase(java.lang.String.valueOf(rro), pFontHeader);
// table.addCell(phrase);
table.getDefaultCell().setColspan(1);
phrase = new Phrase(java.lang.String.valueOf(nr + cr + rr + rrx), pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(2);
phrase = new Phrase(" ", pFontHeader);
table.addCell(phrase);
docPdf.add(table);
} catch (java.sql.SQLException SqlExec) {
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), SqlExec.getMessage());
}
// }
} catch (com.lowagie.text.BadElementException BadElExec) {
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), BadElExec.getMessage());
}
} catch (java.io.FileNotFoundException fnfExec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), fnfExec.getMessage());
}
} catch (com.lowagie.text.DocumentException lwDocexec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), lwDocexec.getMessage());
}
docPdf.close();
com.afrisoftech.lib.PDFRenderer.renderPDF(tempFile);
} catch (java.io.IOException IOexec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), IOexec.getMessage());
}
}
public void generatePdf(java.lang.String memNo) {
java.lang.Process wait_for_Pdf2Show;
java.util.Calendar cal = java.util.Calendar.getInstance();
java.util.Date dateStampPdf = cal.getTime();
java.lang.String pdfDateStamp = dateStampPdf.toString();
try {
java.io.File tempFile =
java.io.File.createTempFile("REP" + this.getDateLable() + "_", ".pdf");
tempFile.deleteOnExit();
java.lang.Runtime rt = java.lang.Runtime.getRuntime();
java.lang.String debitTotal = null;
java.lang.String creditTotal = null;
com.lowagie.text.Document docPdf = new com.lowagie.text.Document();
double osBalance = 0.00;
double current = 0.00;
try {
try {
com.lowagie.text.pdf.PdfWriter.getInstance(
docPdf, new java.io.FileOutputStream(tempFile));
String compName = null;
String date = null;
String Messg = null;
docPdf.open();
try {
java.util.Calendar calendar = java.util.Calendar.getInstance();
long dateNow = calendar.getTimeInMillis();
java.sql.Date datenowSql = new java.sql.Date(dateNow);
System.out.println(datenowSql.toString());
java.lang.Object listofStaffNos[] = this.getListofStaffNos();
com.lowagie.text.pdf.PdfPTable table1 = new com.lowagie.text.pdf.PdfPTable(7);
// com.lowagie.text.Table table = new com.lowagie.text.Table(7);
// table.endHeaders();
int headerwidths[] = {15, 15, 30, 15, 15, 15, 15};
table1.setWidths(headerwidths);
// if (docPdf.getPageNumber() > 1) {
// table1.setHeaderRows(4);
// }
table1.setWidthPercentage((100));
table1.getDefaultCell().setBorder(Rectangle.BOTTOM);
table1.getDefaultCell().setColspan(7);
Phrase phrase = new Phrase();
// table.addCell(phrase);
table1.getDefaultCell().setColspan(1);
table1.getDefaultCell().setBackgroundColor(java.awt.Color.WHITE);
table1.getDefaultCell().setBorderColor(java.awt.Color.WHITE);
try {
java.sql.Statement st3 = connectDB.createStatement();
java.sql.Statement st4 = connectDB.createStatement();
java.sql.ResultSet rset4 =
st4.executeQuery(
"SELECT TO_CHAR(current_timestamp(0),'FMDay FMDD/ MM/ YY HH12::MI')");
while (rset4.next()) {
date = rset4.getObject(1).toString();
}
java.sql.ResultSet rset3 = st3.executeQuery("select header_name from pb_header");
while (rset3.next()) {
table1.getDefaultCell().setColspan(7);
table1.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_CENTER);
phrase = new Phrase(rset3.getObject(1).toString(), pFontHeader11);
table1.addCell(phrase);
}
} catch (java.sql.SQLException SqlExec) {
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), SqlExec.getMessage());
}
docPdf.add(table1);
} catch (com.lowagie.text.BadElementException BadElExec) {
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), BadElExec.getMessage());
}
// } catch(java.io.FileNotFoundException fnfExec) {
// javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(),
// fnfExec.getMessage());
/// }
try {
java.util.Calendar calendar = java.util.Calendar.getInstance();
long dateNow = calendar.getTimeInMillis();
java.sql.Date datenowSql = new java.sql.Date(dateNow);
System.out.println(datenowSql.toString());
java.lang.Object listofStaffNos[] = this.getListofStaffNos();
com.lowagie.text.pdf.PdfPTable table = new com.lowagie.text.pdf.PdfPTable(9);
// com.lowagie.text.Table table = new com.lowagie.text.Table(7);
// table.endHeaders();
int headerwidths[] = {12, 12, 9, 10, 12, 10, 13, 8, 8};
table.setWidths(headerwidths);
// if (docPdf.getPageNumber() > 1) {
table.setHeaderRows(5);
// }
table.setWidthPercentage((100));
table.getDefaultCell().setBorder(Rectangle.BOTTOM);
table.getDefaultCell().setColspan(9);
Phrase phrase = new Phrase();
// table.addCell(phrase);
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setBackgroundColor(java.awt.Color.WHITE);
table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);
try {
double Debit = 0.00;
// double Credit = 0.00;
java.sql.Statement st22 = connectDB.createStatement();
java.sql.Statement st11 = connectDB.createStatement();
java.sql.Statement st = connectDB.createStatement();
java.sql.Statement st1 = connectDB.createStatement();
java.sql.Statement st2 = connectDB.createStatement();
java.sql.Statement st3 = connectDB.createStatement();
java.sql.Statement st4 = connectDB.createStatement();
java.sql.Statement st41 = connectDB.createStatement();
java.sql.Statement st5 = connectDB.createStatement();
java.sql.ResultSet rset3 =
st3.executeQuery(
"select hospital_name,postal_code||' '||box_no||' '||town,main_telno||' '||other_telno,initcap(street),main_faxno,email,website,room_no from pb_hospitalprofile");
// java.sql.ResultSet rset4 = st4.executeQuery("select scheme_manager from
// ac_scheme_providers where scheme_manager = '"+memNo+"'");
java.sql.ResultSet rset4 =
st4.executeQuery(
"select scheme_name,account_no from ac_schemes where account_no = '"
+ memNo1
+ "'");
java.sql.ResultSet rset41 =
st41.executeQuery(
"select payer_name from ac_schemes where account_no = '" + memNo1 + "'");
// java.sql.ResultSet rset1 = st1.executeQuery("select ac.date,pc.scheme_staff_no,
// ac.payee,ac.reference_no,sum(ac.debit),ac.admission_no from hp_patient_card
// pc,ac_debtors ac where pc.patient_no = ac.admission_no and ac.dealer = '"+memNo+"'
// and ac.dealer = pc.scheme AND ac.date::date BETWEEN '"+beginDate+"' AND
// '"+endDate+"' and ac.debit > 0 group by ac.date,pc.scheme_staff_no,
// ac.payee,ac.reference_no,ac.admission_no");// UNION select
// pd.date::date,initcap(pd.scheme_staff_no), (sh.first_name||' '||sh.second_name||'
// '||sh.last_name) as name,pd.reference,sum(pd.credit),pd.patient_no from
// hp_patient_card pd,hp_inpatient_register sh where pd.patient_no = sh.patient_no and
// pd.isurer = '"+memNo+"' AND pd.date::date BETWEEN '"+beginDate+"' AND '"+endDate+"'
// and pd.credit > 0 group by pd.date::date,pd.scheme_staff_no,
// name,pd.reference,pd.invoice_no,pd.patient_no order by pd.invoice_no");
java.sql.ResultSet rset1 =
st1.executeQuery(
" select submission_date,date_returned,doc_no,inv_no,reason,inv_amount,initcap(action),org_dispatch_no,submission_no from pb_returned_claim where dispatched = true and acc_no = '"
+ memNo1
+ "' AND submission_date BETWEEN '"
+ beginDate
+ "' AND '"
+ endDate
+ "' order by submission_date"); // UNION select
// pd.date::date,initcap(pd.scheme_staff_no), (sh.first_name||' '||sh.second_name||' '||sh.last_name) as name,pd.reference,sum(pd.credit),pd.patient_no from hp_patient_card pd,hp_inpatient_register sh where pd.patient_no = sh.patient_no and pd.isurer = '"+memNo+"' AND pd.date::date BETWEEN '"+beginDate+"' AND '"+endDate+"' and pd.credit > 0 group by pd.date::date,pd.scheme_staff_no, name,pd.reference,pd.invoice_no,pd.patient_no order by pd.invoice_no");
// java.sql.ResultSet rsetTotals2 = st22.executeQuery("select sum(balance-credit_bal)
// from ac_debtors where account_no = '"+memNo1+"' AND date::date < '"+beginDate+"'");
table.getDefaultCell().setBorderColor(java.awt.Color.BLACK);
table.getDefaultCell().setBorderWidth(Rectangle.TOP | Rectangle.BOTTOM);
table.getDefaultCell().setColspan(9);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_CENTER);
phrase = new Phrase("Claim Re-submission Report", pFontHeader11);
table.addCell(phrase);
while (rset4.next()) {
table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);
table.getDefaultCell().setColspan(9);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase =
new Phrase(
"Scheme Name : " + dbObject.getDBObject(rset4.getObject(1), "-"),
pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setColspan(9);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase =
new Phrase(
"Account No : " + dbObject.getDBObject(rset4.getObject(2), "-"),
pFontHeader1);
table.addCell(phrase);
}
while (rset41.next()) {
table.getDefaultCell().setColspan(9);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase =
new Phrase(
"Payer : " + dbObject.getDBObject(rset41.getObject(1), "-"), pFontHeader1);
table.addCell(phrase);
}
table.getDefaultCell().setColspan(5);
try {
java.text.DateFormat dateFormat =
java.text.DateFormat.getDateInstance(java.text.DateFormat.MEDIUM); // MEDIUM);
java.util.Date endDate1 =
dateFormat.parse(endDate.toLocaleString()); // dateInstance.toLocaleString());
java.util.Date endDate11 =
dateFormat.parse(beginDate.toLocaleString()); // dateInstance.toLocaleString());
System.out.println("" + endDate1);
// phrase = new Phrase(bank +" Report: " +dateFormat.format(formattedDate),
// pFontHeader);
// table.addCell(phrase);
phrase =
new Phrase(
"Period : "
+ dateFormat.format(endDate11)
+ " - "
+ dateFormat.format(endDate1),
pFontHeader1);
table.addCell(phrase);
} catch (java.text.ParseException psExec) {
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), psExec.getMessage());
}
// table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
// phrase = new Phrase("Period : '"+beginDate+"' - '"+endDate+"'", pFontHeader1);
// table.addCell(phrase);
table.getDefaultCell().setColspan(4);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase("Print Date " + date, pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setColspan(3);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
table.getDefaultCell().setBorderColor(java.awt.Color.BLACK);
table.getDefaultCell().setBorderWidth(Rectangle.TOP);
table.getDefaultCell().setColspan(1);
phrase = new Phrase("Subm. Date", pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setColspan(1);
phrase = new Phrase("Date Rcd", pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setColspan(1);
phrase = new Phrase("Doc No", pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setColspan(1);
phrase = new Phrase("Invoice No.", pFontHeader1);
table.addCell(phrase);
phrase = new Phrase("Reason", pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
table.getDefaultCell().setColspan(1);
phrase = new Phrase("Amt ", pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
table.getDefaultCell().setColspan(1);
phrase = new Phrase("Action Taken", pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setColspan(1);
phrase = new Phrase("Org Disp No ", pFontHeader1);
table.addCell(phrase);
phrase = new Phrase("Sbm No.", pFontHeader1);
table.addCell(phrase);
while (rset1.next()) {
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase(dbObject.getDBObject(rset1.getObject(1), "-"), pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase(dbObject.getDBObject(rset1.getObject(2), "-"), pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase(dbObject.getDBObject(rset1.getObject(3), "-"), pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase(dbObject.getDBObject(rset1.getObject(4), "-"), pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase(dbObject.getDBObject(rset1.getObject(5), "-"), pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(rset1.getString(6)),
pFontHeader);
Debit = Debit + rset1.getDouble(6);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase(dbObject.getDBObject(rset1.getObject(7), "-"), pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase(dbObject.getDBObject(rset1.getObject(8), "-"), pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase(dbObject.getDBObject(rset1.getObject(9), "-"), pFontHeader);
table.addCell(phrase);
}
table.getDefaultCell().setColspan(2);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase = new Phrase("", pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setBorderColor(java.awt.Color.BLACK);
table.getDefaultCell().setBorder(Rectangle.BOTTOM | Rectangle.TOP);
// while (rsetTotals.next()) {
table.getDefaultCell().setColspan(2);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase("Total", pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(2);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(Debit)),
pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(3);
phrase = new Phrase(" ", pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);
table.getDefaultCell().setColspan(27);
phrase = new Phrase(" ");
table.addCell(phrase);
table.getDefaultCell().setColspan(9);
phrase =
new Phrase(
"PREPARED BY: _____________________________ CHECKED BY: ____________________________________",
pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setColspan(27);
phrase = new Phrase(" ");
table.addCell(phrase);
table.getDefaultCell().setColspan(9);
phrase =
new Phrase(
"RECEIVED BY: ___________________ Sign: _______________ Date: ____/____/______ Time: ___________",
pFontHeader1);
table.addCell(phrase);
try {
java.sql.Statement st31 = connectDB.createStatement();
// java.sql.Statement st4 = connectDB.createStatement();
java.sql.ResultSet rset2 = st31.executeQuery("select name from pb_notice");
// java.sql.ResultSet rset2 = st3.executeQuery("SELECT hospital_name from
// pb_hospitalprofile");
// java.sql.ResultSet rset4 = st4.executeQuery("SELECT date('now') as Date");
while (rset2.next()) {
Messg = rset2.getString(1);
}
com.lowagie.text.HeaderFooter footer =
new com.lowagie.text.HeaderFooter(
new Phrase("" + Messg + ""),
false); // FontFactory.getFont(com.lowagie.text.FontFactory.HELVETICA, 14,
// Font.BOLDITALIC,java.awt.Color.blue)));
// com.lowagie.text.HeaderFooter headerFoter = new
// com.lowagie.text.HeaderFooter(new Phrase(""+compName+""),false);//
// FontFactory.getFont(com.lowagie.text.FontFactory.HELVETICA, 14,
// Font.BOLDITALIC,java.awt.Color.blue)));
// headerFoter.ALIGN_CENTER;
// headerFoter.setRight(5);
docPdf.setFooter(footer);
} catch (java.sql.SQLException SqlExec) {
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), SqlExec.getMessage());
}
docPdf.add(table);
} catch (java.sql.SQLException SqlExec) {
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), SqlExec.getMessage());
}
// } // }
} catch (com.lowagie.text.BadElementException BadElExec) {
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), BadElExec.getMessage());
}
} catch (java.io.FileNotFoundException fnfExec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), fnfExec.getMessage());
}
} catch (com.lowagie.text.DocumentException lwDocexec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), lwDocexec.getMessage());
}
docPdf.close();
docPdf.close();
com.afrisoftech.lib.PDFRenderer.renderPDF(tempFile);
} catch (java.io.IOException IOexec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), IOexec.getMessage());
}
}
public void generatePdf() {
// java.lang.Object[][] ageingDates = periodicDates.getMonthlyDates();
ageingDates = ageingSeries.getAgeingDateSeries();
double columnTotals[] = new double[ageingDates.length];
java.lang.Process wait_for_Pdf2Show;
java.util.Calendar cal = java.util.Calendar.getInstance();
java.util.Date dateStampPdf = cal.getTime();
java.lang.String pdfDateStamp = dateStampPdf.toString();
int interval = 0;
try {
java.io.File tempFile =
java.io.File.createTempFile("REP" + this.getDateLable() + "_", ".pdf");
tempFile.deleteOnExit();
java.lang.Runtime rt = java.lang.Runtime.getRuntime();
java.lang.String debitTotal = null;
java.lang.String creditTotal = null;
com.lowagie.text.Document docPdf = new com.lowagie.text.Document();
java.util.Calendar calendar = java.util.Calendar.getInstance();
long dateNow = calendar.getTimeInMillis();
java.sql.Date datenowSql = new java.sql.Date(dateNow);
System.out.println(datenowSql.toString());
try {
try {
com.lowagie.text.pdf.PdfWriter.getInstance(
docPdf, new java.io.FileOutputStream(tempFile));
String compName = null;
String date = null;
try {
// java.sql.Connection conDb =
// java.sql.DriverManager.getConnection("jdbc:postgresql://localhost:5432/sako","postgres","pilsiner");
java.sql.Statement st3 = connectDB.createStatement();
java.sql.Statement st4 = connectDB.createStatement();
java.sql.ResultSet rset2 =
st3.executeQuery("SELECT hospital_name from pb_hospitalprofile");
java.sql.ResultSet rset4 = st4.executeQuery("SELECT date('now') as Date");
while (rset2.next()) compName = rset2.getObject(1).toString();
while (rset4.next()) date = rset4.getObject(1).toString();
com.lowagie.text.HeaderFooter headerFoter =
new com.lowagie.text.HeaderFooter(
new Phrase("" + compName, pFontHeader2),
false); // FontFactory.getFont(com.lowagie.text.FontFactory.HELVETICA, 14,
// Font.BOLDITALIC,java.awt.Color.blue)));
// com.lowagie.text.HeaderFooter headerFoter = new com.lowagie.text.HeaderFooter(new
// Phrase(""+compName+""),false);//
// FontFactory.getFont(com.lowagie.text.FontFactory.HELVETICA, 14,
// Font.BOLDITALIC,java.awt.Color.blue)));
headerFoter.setAlignment(com.lowagie.text.HeaderFooter.ALIGN_CENTER);
docPdf.setHeader(headerFoter);
} catch (java.sql.SQLException SqlExec) {
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), SqlExec.getMessage());
}
com.lowagie.text.HeaderFooter footer =
new com.lowagie.text.HeaderFooter(
new Phrase(Debtor + ": Ageing Page: ", pFontHeader),
true); // FontFactory.getFont(com.lowagie.text.FontFactory.HELVETICA, 12,
// Font.BOLDITALIC,java.awt.Color.blue));
docPdf.setFooter(footer);
docPdf.open();
double Totals = 0.00;
double OS = 0.00;
try {
com.lowagie.text.pdf.PdfPTable table =
new com.lowagie.text.pdf.PdfPTable(ageingDates.length + 4);
String headerWidths = null;
java.util.Vector headerVector = new java.util.Vector(1, 1);
int z = ageingDates.length;
int headerwidths[] = {22, 13, 13, 13, 13, 13, 13, 13}; // ,13,13};
table.setWidths(headerwidths);
table.setWidthPercentage((100));
table.setHeaderRows(2);
table.getDefaultCell().setBorder(Rectangle.BOTTOM);
table.getDefaultCell().setColspan(8);
Phrase phrase = new Phrase("");
table.getDefaultCell().setColspan(5);
try {
java.text.DateFormat dateFormat =
java.text.DateFormat.getDateInstance(java.text.DateFormat.MEDIUM); // MEDIUM);
// java.util.Date endDate1 =
// dateFormat.parse(beginDate.toLocaleString());//dateInstance.toLocaleString());
java.util.Date endDate2 = dateFormat.parse(endDate.toLocaleString());
// System.out.println(""+endDate1);
// phrase = new Phrase(bank +" Report: " +dateFormat.format(formattedDate),
// pFontHeader);
// table.addCell(phrase);
phrase =
new Phrase(
Debtor + ": Payer Ageing as at " + dateFormat.format(endDate2), pFontHeader);
table.addCell(phrase);
} catch (java.text.ParseException psExec) {
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), psExec.getMessage());
}
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
table.getDefaultCell().setColspan(3);
phrase = new Phrase("Printed on : " + date, pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_CENTER);
phrase = new Phrase("Name", pFontHeader);
table.addCell(phrase);
// table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
// phrase = new Phrase("O/S Balance",pFontHeader);
// table.addCell(phrase);
for (int x = 0; x < ageingDates.length; x++) {
int days = 30;
if (x < 1) {
phrase = new Phrase("Current", pFontHeader);
} else {
phrase = new Phrase("Over " + x * days + " Days", pFontHeader);
interval = x;
}
table.addCell(phrase);
}
// table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase("Over " + ((interval + 1) * 30) + " Days", pFontHeader);
table.addCell(phrase);
// table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase("UnAlloc.", pFontHeader);
table.addCell(phrase);
phrase = new Phrase("O/S", pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setBackgroundColor(java.awt.Color.WHITE);
// table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);
try {
double GrandTotal = 0.00;
double Over120Total = 0.00;
java.lang.Object[] listofAct = this.getListofActivities();
// java.sql.Connection conDb1 =
// java.sql.DriverManager.getConnection("jdbc:postgresql://localhost:5432/sako","postgres","pilsiner");
System.out.println(listofAct.length);
for (int i = 0; i < listofAct.length; i++) {
double TurnOver = 0.00;
double Over120 = 0.00;
double TotalCount = 0.00;
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
java.sql.PreparedStatement pset10 =
connectDB.prepareStatement(
"select account_no||' '||scheme_name from ac_schemes WHERE account_no = ?"); // < '"+endDate+"'::date and date > '"+endDate+"'::date - 30 group by dealer");
// java.sql.PreparedStatement pset10 = connectDB.prepareStatement("select distinct
// initcap(scheme_manager) as dealer from ac_scheme_providers WHERE scheme_manager =
// ?");//< '"+endDate+"'::date and date > '"+endDate+"'::date - 30 group by
// dealer");
pset10.setString(1, listofAct[i].toString());
java.sql.ResultSet rset10 = pset10.executeQuery();
while (rset10.next()) table.getDefaultCell().setColspan(1);
// table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase = new Phrase(dbObject.getDBObject(rset10.getObject(1), "-"), pFontHeader1);
table.addCell(phrase);
// columnTotals[t] = columnTotals[t] + rset.getDouble(1);
// table.addCell(phrase);
java.sql.Statement st2 = connectDB.createStatement();
java.sql.Statement st21 = connectDB.createStatement();
java.sql.Statement st22 = connectDB.createStatement();
java.sql.Statement st23 = connectDB.createStatement();
java.sql.Statement st211 = connectDB.createStatement();
java.sql.Statement st221 = connectDB.createStatement();
java.sql.Statement st231 = connectDB.createStatement();
java.sql.Statement st2A = connectDB.createStatement();
java.sql.Statement st2B = connectDB.createStatement();
java.sql.Statement st2C = connectDB.createStatement();
java.sql.Statement stc = connectDB.createStatement();
System.out.println("Dealer Is : [" + listofAct[i] + "].");
java.sql.PreparedStatement pset1 =
connectDB.prepareStatement(
"select sum(balance) from ac_debtors WHERE account_no = ? and dealer = '"
+ Debtor
+ "' AND date < '"
+ ageingDates[0][0]
+ "'"); // < '"+endDate+"'::date and date > '"+endDate+"'::date - 30
// group by dealer");
// java.sql.PreparedStatement pset1 = connectDB.prepareStatement("select
// sum(balance) from ac_debtors WHERE account_no = ? and dealer = '"+Debtor+"' AND
// date < '"+ageingDates[ageingDates.length - 1][0]+"'");//< '"+endDate+"'::date and
// date > '"+endDate+"'::date - 30 group by dealer");
pset1.setString(1, listofAct[i].toString());
java.sql.ResultSet rset1 = pset1.executeQuery();
java.sql.PreparedStatement pset111 =
connectDB.prepareStatement(
"select sum(balance-credit_bal) from ac_debtors WHERE account_no = ? and dealer = '"
+ Debtor
+ "' AND date < '"
+ endDate
+ "'"); // < '"+endDate+"'::date and date > '"+endDate+"'::date - 30
// group by dealer");
pset111.setString(1, listofAct[i].toString());
java.sql.ResultSet rset111 = pset111.executeQuery();
java.sql.Statement st02 = connectDB.createStatement();
java.sql.PreparedStatement pset112 =
connectDB.prepareStatement(
"select sum(credit_bal) from ac_debtors WHERE account_no = ? and dealer = '"
+ Debtor
+ "' AND date < '"
+ endDate
+ "'"); // < '"+endDate+"'::date and date > '"+endDate+"'::date - 30
// group by dealer");
pset112.setString(1, listofAct[i].toString());
java.sql.ResultSet rset112 = pset112.executeQuery();
for (int t = ageingDates.length - 1; t >= 0; t--) {
// for (int t = 0; t < ageingDates.length; t++) {
java.sql.Statement st01 = connectDB.createStatement();
java.sql.PreparedStatement pset =
connectDB.prepareStatement(
"select sum(balance) from ac_debtors WHERE account_no = ? and dealer = '"
+ Debtor
+ "' AND date between '"
+ ageingDates[t][0]
+ "' AND '"
+ ageingDates[t][1]
+ "'");
pset.setString(1, listofAct[i].toString().toUpperCase());
java.sql.ResultSet rset = pset.executeQuery();
while (rset.next()) {
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(rset.getString(1)),
pFontHeader1);
table.addCell(phrase);
columnTotals[t] = columnTotals[t] + rset.getDouble(1);
}
TotalCount = TotalCount + rset.getDouble(1);
}
while (rset1.next()) {
System.out.println("This Result " + rset1.getString(1));
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(rset1.getString(1)),
pFontHeader1);
// phrase = new Phrase(new
// com.afrisoftech.sys.Format2Currency().Format2Currency("0"),pFontHeader1);
Over120 = rset1.getDouble(1);
table.addCell(phrase);
Over120Total = Over120Total + rset1.getDouble(1);
TurnOver = TotalCount + Over120;
}
while (rset112.next()) {
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(rset112.getString(1)),
pFontHeader1);
table.addCell(phrase);
OS = OS + rset112.getDouble(1);
}
while (rset111.next()) {
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(rset111.getString(1)),
pFontHeader1);
table.addCell(phrase);
Totals = Totals + rset111.getDouble(1);
}
}
table.getDefaultCell().setBorderColor(java.awt.Color.BLACK);
table.getDefaultCell().setBorder(Rectangle.BOTTOM | Rectangle.TOP);
// while (rsetTotals.next()) {
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase("Total", pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
// phrase = new Phrase(new
// com.afrisoftech.sys.Format2Currency().Format2Currency(rsetTotals.getString(1)),
// pFontHeader);
// for (int x = 0; x < ageingDates.length; x++) {
for (int x = ageingDates.length - 1; x >= 0; x--) {
// phrase = new Phrase("Current"+2*x,pFontHeader);
// table.addCell(phrase);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(columnTotals[x])),
pFontHeader);
table.addCell(phrase);
}
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(Over120Total)),
pFontHeader);
table.addCell(phrase);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(OS)),
pFontHeader);
table.addCell(phrase);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(Totals)),
pFontHeader);
table.addCell(phrase);
docPdf.add(table);
} catch (java.sql.SQLException SqlExec) {
SqlExec.printStackTrace();
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), SqlExec.getMessage());
}
} catch (com.lowagie.text.BadElementException BadElExec) {
// Bad
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), BadElExec.getMessage());
}
} catch (java.io.FileNotFoundException fnfExec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), fnfExec.getMessage());
}
} catch (com.lowagie.text.DocumentException lwDocexec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), lwDocexec.getMessage());
}
docPdf.close();
try {
if (System.getProperty("os.name").equalsIgnoreCase("Linux")) {
System.out.println(tempFile);
wait_for_Pdf2Show = rt.exec("kghostview " + tempFile + "");
wait_for_Pdf2Show.waitFor();
} else {
wait_for_Pdf2Show =
rt.exec("c:/Program Files/Adobe/Acrobat 5.0/Reader/AcroRd32.exe " + tempFile);
wait_for_Pdf2Show.waitFor();
}
} catch (java.lang.InterruptedException intrExec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), intrExec.getMessage());
}
} catch (java.io.IOException IOexec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), IOexec.getMessage());
}
}
public void generatePdf(java.lang.String memNo) {
java.lang.Process wait_for_Pdf2Show;
java.util.Calendar cal = java.util.Calendar.getInstance();
java.util.Date dateStampPdf = cal.getTime();
java.lang.String pdfDateStamp = dateStampPdf.toString();
try {
java.io.File tempFile =
java.io.File.createTempFile("REP" + this.getDateLable() + "_", ".pdf");
tempFile.deleteOnExit();
java.lang.Runtime rt = java.lang.Runtime.getRuntime();
java.lang.String debitTotal = null;
java.lang.String creditTotal = null;
com.lowagie.text.Document docPdf = new com.lowagie.text.Document();
try {
try {
com.lowagie.text.pdf.PdfWriter.getInstance(
docPdf, new java.io.FileOutputStream(tempFile));
String compName = null;
String date = null;
com.lowagie.text.HeaderFooter footer =
new com.lowagie.text.HeaderFooter(
new Phrase("Doctor's Statement (all transactions)- Page: "),
true); // FontFactory.getFont(com.lowagie.text.FontFactory.HELVETICA, 12,
// Font.BOLDITALIC,java.awt.Color.blue));
docPdf.setFooter(footer);
docPdf.open();
try {
java.util.Calendar calendar = java.util.Calendar.getInstance();
long dateNow = calendar.getTimeInMillis();
java.sql.Date datenowSql = new java.sql.Date(dateNow);
System.out.println(datenowSql.toString());
com.lowagie.text.pdf.PdfPTable table1 = new com.lowagie.text.pdf.PdfPTable(7);
// com.lowagie.text.Table table = new com.lowagie.text.Table(7);
// table.endHeaders();
int headerwidths[] = {15, 15, 30, 15, 15, 15, 15};
table1.setWidths(headerwidths);
// if (docPdf.getPageNumber() > 1) {
// table1.setHeaderRows(4);
// }
table1.setWidthPercentage((100));
table1.getDefaultCell().setBorder(Rectangle.BOTTOM);
table1.getDefaultCell().setColspan(7);
Phrase phrase = new Phrase();
// table.addCell(phrase);
table1.getDefaultCell().setColspan(1);
table1.getDefaultCell().setBackgroundColor(java.awt.Color.WHITE);
table1.getDefaultCell().setBorderColor(java.awt.Color.WHITE);
try {
java.sql.Statement st2 = connectDB.createStatement();
java.sql.ResultSet rset2 =
st2.executeQuery("SELECT rep_currency from pb_hospitalprofile");
while (rset2.next()) {
ks = rset2.getObject(1).toString();
}
java.sql.Statement st3 = connectDB.createStatement();
java.sql.ResultSet rset3 = st3.executeQuery("select header_name from pb_header");
while (rset3.next()) {
table1.getDefaultCell().setColspan(7);
table1.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_CENTER);
phrase = new Phrase(rset3.getObject(1).toString().toUpperCase(), pFontHeader11);
table1.addCell(phrase);
}
} catch (java.sql.SQLException SqlExec) {
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), SqlExec.getMessage());
}
docPdf.add(table1);
} catch (com.lowagie.text.BadElementException BadElExec) {
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), BadElExec.getMessage());
}
double osBalance1 = 0.00;
double osBalance = 0.00;
double current = 0.00;
double osBalancebf = 0.00;
try {
com.lowagie.text.pdf.PdfPTable table = new com.lowagie.text.pdf.PdfPTable(8);
int headerwidths[] = {12, 30, 10, 20, 10, 10, 10, 13};
table.setWidths(headerwidths);
table.setWidthPercentage((100));
table.setHeaderRows(7);
table.getDefaultCell().setBorder(Rectangle.BOTTOM);
table.getDefaultCell().setColspan(8);
Phrase phrase = new Phrase();
// table.addCell(phrase);
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setBackgroundColor(java.awt.Color.WHITE);
table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);
try {
java.sql.Statement st = connectDB.createStatement();
java.sql.Statement st1 = connectDB.createStatement();
java.sql.Statement st2 = connectDB.createStatement();
java.sql.Statement st3 = connectDB.createStatement();
java.sql.Statement st2x = connectDB.createStatement();
java.sql.ResultSet rset2x =
st2x.executeQuery("SELECT rep_currency from pb_hospitalprofile");
while (rset2x.next()) {
ks = rset2x.getObject(1).toString();
}
java.sql.ResultSet rset3 =
st3.executeQuery(
"select hospital_name,postal_code||' '||box_no||' '||town,main_telno||' '||other_telno,initcap(street),main_faxno,email,website,room_no from pb_hospitalprofile");
// java.sql.ResultSet rset = st.executeQuery("select DISTINCT member_code,
// member_name,date from shares_transactions order by member_code");
java.sql.ResultSet rset =
st.executeQuery(
"select staff_no,initcap(first_name||' '||middle_name),last_name,estate,tel_no from pb_doctors_list where staff_no = '"
+ memNo
+ "'");
java.sql.ResultSet rset1 =
st1.executeQuery(
"select input_date::date,initcap(patient_no||''||patient_name),initcap(description),CASE WHEN (debit > 0) THEN debit ELSE credit END AS debit,credit_bal,pat_inv,debit_bal from ac_doctors_ledger where acc_no = '"
+ memNo
+ "' order by input_date::date"); // union select
// date::date,initcap(service) as
// service,dosage,reference,credit from
// hp_patient_card where patient_no =
// '"+memNo+"' and credit > 0 order by
// date");
java.sql.ResultSet rsetTotals =
st2.executeQuery(
"select sum(credit_bal) from ac_doctors_ledger where acc_no = '"
+ memNo
+ "'");
table.getDefaultCell().setColspan(8);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_CENTER);
phrase =
new Phrase("Doctor's Statement [ All transactions ]".toUpperCase(), pFontHeader1);
table.addCell(phrase);
while (rset.next()) {
table.getDefaultCell().setColspan(4);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase("Account No.: " + memNo, pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(4);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase =
new Phrase(
"Residence : " + dbObject.getDBObject(rset.getObject(4), "-"), pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(4);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase =
new Phrase(
"Doctor's Name : " + dbObject.getDBObject(rset.getObject(2), "-"),
pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(4);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase =
new Phrase(
"Address : " + dbObject.getDBObject(rset.getObject(3), "-"), pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(8);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase =
new Phrase(
"Tel NO : " + dbObject.getDBObject(rset.getObject(1), "-"), pFontHeader);
table.addCell(phrase);
}
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
table.getDefaultCell().setBorderColor(java.awt.Color.BLACK);
table.getDefaultCell().setBorderWidth(Rectangle.TOP);
table.getDefaultCell().setColspan(1);
phrase = new Phrase("Date", pFontHeader1);
table.addCell(phrase);
phrase = new Phrase("Pat No.", pFontHeader1);
table.addCell(phrase);
phrase = new Phrase("Invoice No.", pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setColspan(1);
phrase = new Phrase("Description", pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
table.getDefaultCell().setColspan(1);
phrase = new Phrase("Orig. Amt", pFontHeader1);
table.addCell(phrase);
// table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
table.getDefaultCell().setColspan(1);
phrase = new Phrase("Debit Bal", pFontHeader1);
table.addCell(phrase);
phrase = new Phrase("Credit Bal", pFontHeader1);
table.addCell(phrase);
phrase = new Phrase("Balance " + ks, pFontHeader1);
table.addCell(phrase);
while (rset1.next()) {
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase(dbObject.getDBObject(rset1.getObject(1), "-"), pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase(dbObject.getDBObject(rset1.getObject(2), "-"), pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase(dbObject.getDBObject(rset1.getObject(6), "-"), pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase(dbObject.getDBObject(rset1.getObject(3), "-"), pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(rset1.getString(4)),
pFontHeader);
table.addCell(phrase);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(rset1.getString(7)),
pFontHeader);
table.addCell(phrase);
osBalancebf = osBalancebf + rset1.getDouble(7);
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(rset1.getString(5)),
pFontHeader);
table.addCell(phrase);
osBalance = osBalance + rset1.getDouble(5);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(osBalance - osBalancebf)),
pFontHeader);
// current = current + osBalance;
table.addCell(phrase);
}
table.getDefaultCell().setBorderColor(java.awt.Color.BLACK);
table.getDefaultCell().setBorder(Rectangle.BOTTOM | Rectangle.TOP);
while (rsetTotals.next()) {
table.getDefaultCell().setColspan(6);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase("Total", pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(rsetTotals.getString(1)),
pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(1);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(osBalance - osBalancebf)),
pFontHeader);
table.addCell(phrase);
}
docPdf.add(table);
} catch (java.sql.SQLException SqlExec) {
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), SqlExec.getMessage());
}
// }
} catch (com.lowagie.text.BadElementException BadElExec) {
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), BadElExec.getMessage());
}
} catch (java.io.FileNotFoundException fnfExec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), fnfExec.getMessage());
}
} catch (com.lowagie.text.DocumentException lwDocexec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), lwDocexec.getMessage());
}
docPdf.close();
com.afrisoftech.lib.PDFRenderer.renderPDF(tempFile);
} catch (java.io.IOException IOexec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), IOexec.getMessage());
}
}