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[][] ageingDates = ageingSeries.getAgeingDateSeries();
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();
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());
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/medic","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 + ""),
false); // FontFactory.getFont(com.lowagie.text.FontFactory.HELVETICA, 14,
// Font.BOLDITALIC,java.awt.Color.blue)));
headerFoter.setAlignment(com.lowagie.text.HeaderFooter.ALIGN_CENTER);
headerFoter.setRight(5);
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("Extendend Trial Balance - Page: ", pFontHeader),
true); // FontFactory.getFont(com.lowagie.text.FontFactory.HELVETICA, 12,
// Font.BOLDITALIC,java.awt.Color.blue));
docPdf.setFooter(footer);
docPdf.open();
docPdf.open();
try {
com.lowagie.text.pdf.PdfPTable table = new com.lowagie.text.pdf.PdfPTable(8);
int headerwidths[] = {8, 20, 12, 12, 12, 12, 12, 12};
table.setWidths(headerwidths);
table.setWidthPercentage((100));
table.setHeaderRows(3);
table.getDefaultCell().setBorder(Rectangle.BOTTOM);
Phrase phrase = new Phrase("", pFontHeader);
try {
double GrandTotal = 0.00;
double Over120Total = 0.00;
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(5);
phrase =
new Phrase(
"Extended Trial Balance : As at " + dateFormat.format(endDate1), pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(3);
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());
}
// Phrase phrase = new Phrase("Patients List As At:" +endDate, pFontHeader);
// table.addCell(phrase);
try {
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(endDate.toString().trim()), "MMMM yyyy");
java.lang.String monthString = dateFormatter.getDateString();
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
table
.getDefaultCell()
.setBorder(Rectangle.BOTTOM | Rectangle.TOP | Rectangle.LEFT | Rectangle.RIGHT);
table.getDefaultCell().setColspan(2);
phrase = new Phrase(" ", pFontHeader);
// table.addCell(phrase);
phrase = new Phrase(" ", pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(2);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_CENTER);
phrase = new Phrase("Prior Balances", pFontHeader);
table.addCell(phrase);
phrase = new Phrase(monthString + " Movement", pFontHeader);
table.addCell(phrase);
phrase = new Phrase("Ending Balance", pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(1);
phrase = new Phrase("GL. Code", pFontHeader);
table.addCell(phrase);
phrase = new Phrase("Description", 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("Debit", pFontHeader);
table.addCell(phrase);
phrase = new Phrase("Credit", pFontHeader);
table.addCell(phrase);
phrase = new Phrase("Debit", pFontHeader);
table.addCell(phrase);
phrase = new Phrase("Credit", pFontHeader);
table.addCell(phrase);
} catch (java.text.ParseException prs) {
prs.printStackTrace();
}
table.getDefaultCell().setBackgroundColor(java.awt.Color.WHITE);
// table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);
java.lang.Object[] listofAct = this.getListofActivities();
// for (int j = 0; j < listofStaffNos.length; j++) {
try {
int name = 0;
double TurnOver = 0.00;
double Over120 = 0.00;
double TotalCount = 0.00;
double current = 0.00;
for (int i = 0; i < listofAct.length; i++) {
java.sql.Statement stmta1 = connectDB.createStatement();
java.sql.PreparedStatement pSeta1 =
connectDB.prepareStatement(
"SELECT count(code) FROM ac_trial_balance where code = '"
+ listofAct[i]
+ "'");
java.sql.ResultSet rSeta1 = pSeta1.executeQuery();
while (rSeta1.next()) {
name = rSeta1.getInt(1);
}
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
if (name > 0) {
java.sql.PreparedStatement pset22 =
connectDB.prepareStatement(
"select distinct code,initcap(activity) from pb_activity WHERE code = ? GROUP BY code,activity"); // < '"+endDate+"'::date and date > '"+endDate+"'::date - 30 group by dealer");
pset22.setString(1, listofAct[i].toString());
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(), "-"),
pFontHeader1);
table.addCell(phrase);
phrase =
new Phrase(
dbObject.getDBObject(rset22.getObject(2).toString(), "-"),
pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
// for (int t = ageingDates.length - 1; t >= 0; t--) {
double amount1 = 0.00;
double amounta = 0.00;
double amount1a = 0.00;
for (int t = 0; t < ageingDates.length; t++) {
double amount = 0.00;
double amount1b = 0.00;
System.out.println("Checking dates : " + ageingDates[t][1].toString());
java.sql.Statement st01 = connectDB.createStatement();
// java.sql.PreparedStatement pset = connectDB.prepareStatement("select
// sum(mtd_debit-mtd_credit) from ac_trial_balance WHERE code = ? AND date
// between '"+ageingDates[t][0]+"' AND '"+ageingDates[t][1]+"'");
java.sql.PreparedStatement pset =
connectDB.prepareStatement(
"select sum(mtd_debit-mtd_credit) from ac_trial_balance WHERE code = ? AND date <= '"
+ ageingDates[t][1]
+ "'");
pset.setString(1, listofAct[i].toString().toUpperCase());
java.sql.ResultSet rset = pset.executeQuery();
while (rset.next()) {
amount = rset.getDouble(1);
columnTotals[t] = columnTotals[t] + amount;
}
if (t == 0) {
if (amount > 0) {
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(amount)),
pFontHeader1);
table.addCell(phrase);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(0.00)),
pFontHeader1);
table.addCell(phrase);
totalDebits = totalDebits + amount;
amount1 = amount;
} else {
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(0.00)),
pFontHeader1);
table.addCell(phrase);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(amount * -1)),
pFontHeader1);
table.addCell(phrase);
totalCredits = totalCredits + (amount * -1);
amount1 = amount;
}
} else {
java.sql.PreparedStatement pset1 =
connectDB.prepareStatement(
"select sum(mtd_debit),sum(mtd_credit) from ac_trial_balance WHERE code = ? AND date between '"
+ ageingDates[t][0]
+ "' AND '"
+ ageingDates[t][1]
+ "'");
pset1.setString(1, listofAct[i].toString().toUpperCase());
java.sql.ResultSet rset1 = pset1.executeQuery();
while (rset1.next()) {
amount = rset1.getDouble(1);
amount1b = rset1.getDouble(2);
columnTotals[t] = columnTotals[t] + amount;
}
// if (amount > 0){
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(amount)),
pFontHeader1);
table.addCell(phrase);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(amount1b)),
pFontHeader1);
table.addCell(phrase);
totalDebits1 = totalDebits1 + amount;
totalCredits1 = totalCredits1 + amount1b;
amounta = amount - amount1b;
/* }else{
phrase = new Phrase(new com.afrisoftech.sys.Format2Currency().Format2Currency(java.lang.String.valueOf(0.00)),pFontHeader1);
table.addCell(phrase);
phrase = new Phrase(new com.afrisoftech.sys.Format2Currency().Format2Currency(java.lang.String.valueOf(amount*-1)),pFontHeader1);
table.addCell(phrase);
totalCredits1 = totalCredits1 + (amount*-1);
amounta = amount;
}*/
}
}
balance = amounta + amount1;
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
if (balance > 0) {
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(balance)),
pFontHeader1);
table.addCell(phrase);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(0.00)),
pFontHeader1);
table.addCell(phrase);
totalDebits11 = totalDebits11 + balance;
} else {
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(0.00)),
pFontHeader1);
table.addCell(phrase);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(balance * -1)),
pFontHeader1);
table.addCell(phrase);
totalCredits11 = totalCredits11 + balance;
}
}
}
}
// 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--) {
for (int t = 0; t < ageingDates.length; t++) {
if (t == 0) {
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(totalDebits)),
pFontHeader);
table.addCell(phrase);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(totalCredits)),
pFontHeader);
table.addCell(phrase);
} else {
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(totalDebits1)),
pFontHeader);
table.addCell(phrase);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(totalCredits1)),
pFontHeader);
table.addCell(phrase);
// totalDebits = columnTotals[x] + columnTotals1[x];
}
}
/* phrase = new Phrase(new com.afrisoftech.sys.Format2Currency().Format2Currency(java.lang.String.valueOf(totalDebits1+totalDebits-)), pFontHeader);
table.addCell(phrase);
phrase = new Phrase(new com.afrisoftech.sys.Format2Currency().Format2Currency(java.lang.String.valueOf(totalCredits1+totalCredits)), pFontHeader);
table.addCell(phrase);*/
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(totalDebits11)),
pFontHeader);
table.addCell(phrase);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(totalCredits11 * -1)),
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("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) {
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.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());
}
}