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()); } }