public java.lang.Object[] getListofActivities() {
java.lang.Object[] listofActivities = null;
java.util.Vector listActVector = new java.util.Vector(1, 1);
try {
java.sql.Statement stmt1 = connectDB.createStatement();
if (balnc.equalsIgnoreCase("both")) {
java.sql.PreparedStatement pSet1 =
connectDB.prepareStatement(
"SELECT DISTINCT CASE WHEN (account_no IS NULL) THEN '-' ELSE account_no END AS account_no,sum(debit-credit) as balance,payee FROM ac_debtors WHERE date::date <= '"
+ endDate
+ "' and account_no is not null group by account_no,payee HAVING sum(debit-credit) <> 0 order by 3 ASC");
// java.sql.PreparedStatement pSet1 = connectDB.prepareStatement("SELECT DISTINCT
// account_no FROM debtors_account where account_no IS NOT NULL and account_no !='' and bal
// <> 0 order by account_no");
java.sql.ResultSet rSet1 = pSet1.executeQuery();
while (rSet1.next()) {
System.out.println(rSet1.getObject(1).toString());
listActVector.addElement(rSet1.getObject(1).toString());
}
} else {
if (balnc.equalsIgnoreCase("neg")) {
// java.sql.PreparedStatement pSet1 = connectDB.prepareStatement("SELECT DISTINCT
// account_no FROM debtors_account where account_no IS NOT NULL and account_no !='' and
// bal < 0 order by account_no");
java.sql.PreparedStatement pSet1 =
connectDB.prepareStatement(
"SELECT DISTINCT CASE WHEN (account_no IS NULL) THEN '-' ELSE account_no END AS account_no,sum(debit-credit) as balance FROM ac_debtors WHERE date::date <= '"
+ endDate
+ "' group by account_no HAVING sum(debit-credit) < 0 order by 2 desc");
java.sql.ResultSet rSet1 = pSet1.executeQuery();
while (rSet1.next()) {
System.out.println(rSet1.getObject(1).toString());
listActVector.addElement(rSet1.getObject(1).toString());
}
} else {
java.sql.PreparedStatement pSet1 =
connectDB.prepareStatement(
"SELECT DISTINCT CASE WHEN (account_no IS NULL) THEN '-' ELSE account_no END AS account_no,sum(debit-credit) as balance FROM ac_debtors WHERE date::date <= '"
+ endDate
+ "' group by account_no HAVING sum(debit-credit) > 0 order by 2 desc");
// java.sql.PreparedStatement pSet1 = connectDB.prepareStatement("SELECT DISTINCT
// account_no FROM debtors_account where account_no IS NOT NULL and account_no !='' and
// bal > 0 order by account_no");
java.sql.ResultSet rSet1 = pSet1.executeQuery();
while (rSet1.next()) {
System.out.println(rSet1.getObject(1).toString());
listActVector.addElement(rSet1.getObject(1).toString());
}
}
}
} catch (java.sql.SQLException sqlExec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), sqlExec.getMessage());
}
listofActivities = listActVector.toArray();
System.out.println("Done list of activities ...");
return listofActivities;
}
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;
String Activity = null;
try {
java.sql.Statement st3 = connectDB.createStatement();
java.sql.Statement st5 = connectDB.createStatement();
java.sql.ResultSet rset5 =
st5.executeQuery(
"SELECT activity from pb_activity where code ilike '" + bank + "'");
java.sql.Statement st4 = 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 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 (rset5.next()) {
Activity = rset5.getObject(1).toString();
}
while (rset4.next()) {
date = rset4.getObject(1).toString();
}
com.lowagie.text.HeaderFooter headerFoter =
new com.lowagie.text.HeaderFooter(
new Phrase("" + compName, pFontHeader),
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);
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("Transaction By Account - Page: ", pFontHeader),
true); // FontFactory.getFont(com.lowagie.text.FontFactory.HELVETICA, 12,
// Font.BOLDITALIC,java.awt.Color.blue));
docPdf.setFooter(footer);
docPdf.open();
double Debit = 0.00;
double Credit = 0.00;
double bal = 0.00;
int nos = 0;
double acBal = 0.00;
try {
com.lowagie.text.pdf.PdfPTable table = new com.lowagie.text.pdf.PdfPTable(9);
int headerwidths[] = {5, 12, 10, 20, 15, 10, 13, 13, 13};
table.setWidths(headerwidths);
table.setWidthPercentage((100));
table.setHeaderRows(2);
table.getDefaultCell().setBorder(Rectangle.BOTTOM);
table.getDefaultCell().setColspan(9);
Phrase phrase = new Phrase("", pFontHeader);
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(7);
phrase =
new Phrase(
bank
+ " "
+ Activity
+ " : Transactions Report ["
+ paType
+ "] Period : From "
+ dateFormat.format(endDate11)
+ " To "
+ dateFormat.format(endDate1),
pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(2);
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().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
table.getDefaultCell().setColspan(1);
phrase = new Phrase("No", pFontHeader);
table.addCell(phrase);
phrase = new Phrase("Date", pFontHeader);
table.addCell(phrase);
phrase = new Phrase("Trans No.", pFontHeader);
table.addCell(phrase);
phrase = new Phrase("Description", pFontHeader);
table.addCell(phrase);
phrase = new Phrase("Narration", pFontHeader);
table.addCell(phrase);
phrase = new Phrase("Doc. No", pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase = new Phrase("Debit", pFontHeader);
table.addCell(phrase);
phrase = new Phrase("Credit " + ks, pFontHeader);
table.addCell(phrase);
phrase = new Phrase("Balance " + ks, pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setBackgroundColor(java.awt.Color.WHITE);
table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);
try {
// java.sql.Connection conDb1 =
// java.sql.DriverManager.getConnection("jdbc:postgresql://localhost:5432/sako","postgres","pilsiner");
java.sql.Statement st = connectDB.createStatement();
java.sql.Statement stx = connectDB.createStatement();
java.sql.Statement st2 = connectDB.createStatement();
if (paType.equalsIgnoreCase("OP")) {
java.sql.ResultSet rset =
st.executeQuery(
"select date,transaction_no,description,transaction_type,voucher_no,sum(debit),sum(credit) from ac_ledger WHERE date BETWEEN '"
+ beginDate
+ "' AND '"
+ endDate
+ "' AND activity_code = '"
+ bank
+ "' AND drawer = 'OP' GROUP BY date,description,transaction_no,transaction_type,voucher_no ORDER BY date,transaction_no ASC");
java.sql.ResultSet rset1 =
stx.executeQuery(
"select sum(debit-credit) from ac_ledger WHERE date < '"
+ beginDate
+ "' AND activity_code = '"
+ bank
+ "' AND drawer = 'OP'");
while (rset1.next()) {
table.getDefaultCell().setColspan(7);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase("BBF", pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
bal = bal + rset1.getDouble(1);
table.getDefaultCell().setColspan(2);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(bal)),
pFontHeader);
table.addCell(phrase);
}
while (rset.next()) {
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
nos = nos + 1;
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase(java.lang.String.valueOf(nos), pFontHeader1);
table.addCell(phrase);
phrase = new Phrase(dbObject.getDBObject(rset.getObject(1), "-"), pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
// table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase(dbObject.getDBObject(rset.getObject(2), "-"), pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase(dbObject.getDBObject(rset.getObject(3), "-"), pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase(dbObject.getDBObject(rset.getObject(4), "-"), pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase(dbObject.getDBObject(rset.getObject(5), "-"), pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
// phrase = new Phrase(new
// com.afrisoftech.sys.Format2Currency().Format2Currency(rsetTotals.getString(1)),pFontHeader);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(rset.getString(6)),
pFontHeader);
Debit = Debit + rset.getDouble(6);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(rset.getString(7)),
pFontHeader);
Credit = Credit + rset.getDouble(7);
table.addCell(phrase);
acBal = acBal + (bal + Debit - Credit);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(acBal)),
pFontHeader);
table.addCell(phrase);
}
} else {
if (paType.equalsIgnoreCase("IP")) {
java.sql.ResultSet rset =
st.executeQuery(
"select date,transaction_no,description,transaction_type,voucher_no,sum(debit),sum(credit) from ac_ledger WHERE date BETWEEN '"
+ beginDate
+ "' AND '"
+ endDate
+ "' AND activity_code = '"
+ bank
+ "' AND drawer = 'IP' GROUP BY date,description,transaction_no,transaction_type,voucher_no ORDER BY date ASC"); // tn,debit_note db WHERE tn.policy_no != '' and tn.policy_no = db.policy_no GROUP BY tn.policy_no,db.policy_class");
while (rset.next()) {
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase(dbObject.getDBObject(rset.getObject(1), "-"), pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
// table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase(dbObject.getDBObject(rset.getObject(2), "-"), pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase(dbObject.getDBObject(rset.getObject(3), "-"), pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase(dbObject.getDBObject(rset.getObject(4), "-"), pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase(dbObject.getDBObject(rset.getObject(5), "-"), pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
// phrase = new Phrase(new
// com.afrisoftech.sys.Format2Currency().Format2Currency(rsetTotals.getString(1)),pFontHeader);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(rset.getString(6)),
pFontHeader);
Debit = Debit + rset.getDouble(6);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(rset.getString(7)),
pFontHeader);
Credit = Credit + rset.getDouble(7);
table.addCell(phrase);
}
} else {
java.sql.ResultSet rset =
st.executeQuery(
"select date,transaction_no,description,transaction_type,invoice_no,sum(debit),sum(credit) from transaction_list_view WHERE date BETWEEN '"
+ beginDate
+ "' AND '"
+ endDate
+ "' AND activity_code = '"
+ bank
+ "' GROUP BY date,description,transaction_no,transaction_type,invoice_no ORDER BY date ASC"); // tn,debit_note db WHERE tn.policy_no != '' and tn.policy_no = db.policy_no GROUP BY tn.policy_no,db.policy_class");
while (rset.next()) {
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase(dbObject.getDBObject(rset.getObject(1), "-"), pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
// table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase(dbObject.getDBObject(rset.getObject(2), "-"), pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase(dbObject.getDBObject(rset.getObject(3), "-"), pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase(dbObject.getDBObject(rset.getObject(4), "-"), pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase(dbObject.getDBObject(rset.getObject(5), "-"), pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
// phrase = new Phrase(new
// com.afrisoftech.sys.Format2Currency().Format2Currency(rsetTotals.getString(1)),pFontHeader);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(rset.getString(6)),
pFontHeader);
Debit = Debit + rset.getDouble(6);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(rset.getString(7)),
pFontHeader);
Credit = Credit + rset.getDouble(7);
table.addCell(phrase);
}
}
}
// java.sql.ResultSet rset = st.executeQuery("select
// date,transaction_no,description,transaction_type,invoice_no,sum(debit),sum(credit)
// from transaction_list_view WHERE date BETWEEN '"+beginDate+"' AND '"+endDate+"' AND
// activity_code = '"+bank+"' GROUP BY
// date,description,transaction_no,transaction_type,invoice_no ORDER BY date ASC");//
// tn,debit_note db WHERE tn.policy_no != '' and tn.policy_no = db.policy_no GROUP BY
// tn.policy_no,db.policy_class");
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(Debit)),
pFontHeader);
table.addCell(phrase);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(Credit)),
pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(4);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase("Debit - Credit", pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(3);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(Debit - Credit)),
pFontHeader);
table.addCell(phrase);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(acBal)),
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();
docPdf.close();
com.afrisoftech.lib.PDFRenderer.renderPDF(tempFile);
} catch (java.io.IOException IOexec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), IOexec.getMessage());
}
}
