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(PageSize.A2.rotate());
try {
try {
Image img = Image.getInstance(com.afrisoftech.lib.CompanyLogo.getPath2Logo());
// Image imgWaterMark = Image.getInstance(System.getProperty("company.watermark"));
com.lowagie.text.pdf.PdfWriter.getInstance(
docPdf, new java.io.FileOutputStream(tempFile));
String compName = null;
String District = null;
String Region = null;
String date = null;
try {
java.sql.Statement st3 = connectDB.createStatement();
java.sql.Statement st4 = connectDB.createStatement();
java.sql.ResultSet rset2 =
st3.executeQuery("SELECT hospital_name,district_branch from pb_hospitalprofile");
java.sql.ResultSet rset4 = st4.executeQuery("SELECT date('now') as Date");
while (rset2.next()) {
compName = rset2.getObject(1).toString();
District = rset2.getObject(2).toString();
}
while (rset4.next()) {
date = rset4.getObject(1).toString();
}
com.lowagie.text.HeaderFooter headerFoter =
new com.lowagie.text.HeaderFooter(
new Phrase("MINISTRY OF HEALTH : " + compName.toUpperCase()),
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) {
SqlExec.printStackTrace();
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), SqlExec.getMessage());
}
com.lowagie.text.HeaderFooter footer =
new com.lowagie.text.HeaderFooter(
new Phrase("MOH - 204B : OP Over 5 Yrs Register : Page: "),
true); // FontFactory.getFont(com.lowagie.text.FontFactory.HELVETICA, 12,
// Font.BOLDITALIC,java.awt.Color.blue));
docPdf.setFooter(footer);
docPdf.open();
try {
com.lowagie.text.pdf.PdfPTable table = new com.lowagie.text.pdf.PdfPTable(21);
int headerwidths[] = {
5, 10, 10, 10, 25, 10, 12, 20, 12, 12, 12, 12, 12, 12, 12, 25, 25, 12, 12, 12, 25
};
table.setWidths(headerwidths);
table.setWidthPercentage((100));
table.setHeaderRows(4);
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());
com.afrisoftech.lib.DateFormatter dateFormatter =
new com.afrisoftech.lib.DateFormatter(
dateFormat.parse(endDate.toLocaleString()), "MMMM");
java.lang.String monthString = dateFormatter.getDateString();
com.afrisoftech.lib.DateFormatter dateFormatters =
new com.afrisoftech.lib.DateFormatter(
dateFormat.parse(endDate.toLocaleString()), "yyyy");
java.lang.String yearString = dateFormatters.getDateString();
table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);
table.getDefaultCell().setColspan(21);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_CENTER);
table.getDefaultCell().setFixedHeight(50);
table.addCell(Image.getInstance(com.afrisoftech.lib.CompanyLogo.getPath2Logo()));
table.getDefaultCell().setFixedHeight(-1);
// table.getDefaultCell().setNoWrap(false);
phrase = new Phrase("MOH 204B_Over 5 Yrs Register", pFontHeader2);
table.addCell(phrase);
table.getDefaultCell().setColspan(5);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase("OP OVER 5 YEARS REGISTER", pFontHeader22);
table.addCell(phrase);
table.getDefaultCell().setColspan(10);
phrase = new Phrase("HEALTH FACILITY : " + compName, pFontHeader22);
table.addCell(phrase);
table.getDefaultCell().setColspan(3);
phrase = new Phrase("MONTH : " + monthString.toUpperCase(), pFontHeader22);
table.addCell(phrase);
table.getDefaultCell().setColspan(3);
phrase = new Phrase("YEAR : " + yearString.toUpperCase(), pFontHeader22);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_CENTER);
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setBorderColor(java.awt.Color.BLACK);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
table.getDefaultCell().setColspan(1);
phrase = new Phrase("#", pFontHeader11);
table.addCell(phrase);
phrase = new Phrase("Date", pFontHeader11);
table.addCell(phrase);
phrase = new Phrase("OPD No.", pFontHeader11);
table.addCell(phrase);
phrase = new Phrase("Revisit", pFontHeader11);
table.addCell(phrase);
phrase = new Phrase("Full Names", pFontHeader11);
table.addCell(phrase);
phrase = new Phrase("Age YRS", pFontHeader11);
table.addCell(phrase);
phrase = new Phrase("Sex", pFontHeader11);
table.addCell(phrase);
phrase = new Phrase("Village", pFontHeader11);
table.addCell(phrase);
phrase = new Phrase("Phone. No.", pFontHeader11);
table.addCell(phrase);
phrase = new Phrase("Weight", pFontHeader11);
table.addCell(phrase);
phrase = new Phrase("Height", pFontHeader11);
table.addCell(phrase);
phrase = new Phrase("Visual Activity RE(Right Eye), LE(LEFT EYE)", pFontHeader11);
table.addCell(phrase);
phrase = new Phrase("HIV: 1=Cancelled, 2=Tested", pFontHeader11);
table.addCell(phrase);
phrase =
new Phrase(
"HIV Status: 1=Known Positive, 2=Positive this visit, 3=Negative, 4=Uknown",
pFontHeader11);
table.addCell(phrase);
phrase =
new Phrase(
"Nutrition Status: 1=Nutrition Education, 2=Nutrition Suppliments",
pFontHeader11);
table.addCell(phrase);
phrase = new Phrase("Diagnosis/Classification", pFontHeader11);
table.addCell(phrase);
phrase = new Phrase("Treatment/Prescription No.", pFontHeader11);
table.addCell(phrase);
phrase = new Phrase("Amount Charged", pFontHeader11);
table.addCell(phrase);
phrase = new Phrase("Receipt No.", pFontHeader11);
table.addCell(phrase);
phrase =
new Phrase(
"1 = From Other HF, 2 = To Other HF, 3 = From CU, 4 = To CU", pFontHeader11);
table.addCell(phrase);
phrase = new Phrase("Remarks", pFontHeader11);
table.addCell(phrase);
// Second header row
phrase = new Phrase("", pFontHeader11);
table.addCell(phrase);
phrase = new Phrase("A", pFontHeader11);
table.addCell(phrase);
phrase = new Phrase("B", pFontHeader11);
table.addCell(phrase);
phrase = new Phrase("C", pFontHeader11);
table.addCell(phrase);
phrase = new Phrase("D", pFontHeader11);
table.addCell(phrase);
phrase = new Phrase("E", pFontHeader11);
table.addCell(phrase);
phrase = new Phrase("F", pFontHeader11);
table.addCell(phrase);
phrase = new Phrase("G", pFontHeader11);
table.addCell(phrase);
phrase = new Phrase("H", pFontHeader11);
table.addCell(phrase);
phrase = new Phrase("I", pFontHeader11);
table.addCell(phrase);
phrase = new Phrase("J", pFontHeader11);
table.addCell(phrase);
phrase = new Phrase("K", pFontHeader11);
table.addCell(phrase);
phrase = new Phrase("L", pFontHeader11);
table.addCell(phrase);
phrase = new Phrase("M", pFontHeader11);
table.addCell(phrase);
phrase = new Phrase("N", pFontHeader11);
table.addCell(phrase);
phrase = new Phrase("O", pFontHeader11);
table.addCell(phrase);
phrase = new Phrase("P", pFontHeader11);
table.addCell(phrase);
phrase = new Phrase("Q", pFontHeader11);
table.addCell(phrase);
phrase = new Phrase("R", pFontHeader11);
table.addCell(phrase);
phrase = new Phrase("S", pFontHeader11);
table.addCell(phrase);
phrase = new Phrase("T", pFontHeader11);
table.addCell(phrase);
} catch (java.text.ParseException psExec) {
psExec.printStackTrace();
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), psExec.getMessage());
}
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
table.getDefaultCell().setBackgroundColor(java.awt.Color.WHITE);
try {
int noSeq = 0;
java.lang.Object[] listofAct = this.getListofStaffNos();
System.out.println("The array has been called...");
for (int i = 0; i < listofAct.length; i++) {
java.sql.PreparedStatement stw =
connectDB.prepareStatement(
"SELECT DISTINCT "
+ " date_part('day', date) ||'-'||date_part('month', date) ||'-'||date_part('year', date), patient_no,"
+ " (CASE WHEN comments = 'Old' THEN 'YES' ELSE 'NO' END) as revisit, "
+ " initcap(name), age::int, (SELECT initcap(gender) FROM hp_patient_register WHERE hp_patient_register.patient_no = hp_patient_visit.patient_no ORDER BY 1 DESC LIMIT 1) as gender, "
+ " (SELECT initcap(residence) FROM hp_inpatient_register"
+ " WHERE hp_patient_visit.patient_no = hp_inpatient_register.patient_no"
+ " UNION SELECT initcap(residence) FROM hp_patient_register WHERE "
+ " hp_patient_visit.patient_no = hp_patient_register.patient_no ORDER BY 1 "
+ " DESC LIMIT 1) as village, (SELECT tel_no FROM hp_patient_register WHERE hp_patient_register.patient_no = hp_patient_visit.patient_no "
+ " ORDER BY 1 DESC LIMIT 1) as telephone, (SELECT weight::numeric(5,1) FROM hp_signs_record WHERE hp_signs_record.patient_no = hp_patient_visit.patient_no AND hp_signs_record.date = hp_patient_visit.date ORDER BY 1 DESC LIMIT 1) as weight,"
+ " (SELECT height::numeric(5,1) FROM hp_signs_record WHERE hp_signs_record.patient_no = hp_patient_visit.patient_no AND hp_signs_record.date = hp_patient_visit.date ORDER BY 1 DESC LIMIT 1) as height, "
+ " ''," // to add eye conditions for over 5 years
+ " (SELECT (CASE WHEN hiv_counselled = true THEN '1' ELSE '' END) || ' '||"
+ "(CASE WHEN hiv_tested = true THEN '2' ELSE '' END) FROM key_health_indicators"
+ " WHERE key_health_indicators.patient_no = hp_patient_visit.patient_no) as hiv_tested,"
+ " (SELECT (CASE WHEN hiv_status ilike '%positive%' THEN '1' ELSE '' END) || ' '||"
+ "(CASE WHEN hiv_status ilike '%visit' THEN '2' ELSE '' END)||' '||"
+ "(CASE WHEN hiv_status ilike 'negative' THEN '3' ELSE '' END)||' '||"
+ "(CASE WHEN hiv_status ilike 'unknown' THEN '4' ELSE '' END) FROM key_health_indicators"
+ " WHERE key_health_indicators.patient_no = hp_patient_visit.patient_no) as hiv_status,"
+ " (SELECT (CASE WHEN nutrition_status ilike '%education%' THEN '1' ELSE '' END)||' '||"
+ "(CASE WHEN nutrition_status ILIKE '%suppliments%' THEN '2' ELSE '' END) "
+ "FROM key_health_indicators WHERE key_health_indicators.patient_no = hp_patient_visit.patient_no)"
+ " as nutrition_status, "
+ " (SELECT disease FROM hp_patient_diagnosis WHERE hp_patient_diagnosis.patient_no = hp_patient_visit.patient_no AND"
+ " hp_patient_diagnosis.date_recorded::date = hp_patient_visit.date::date AND disease is not null ORDER BY 1 DESC LIMIT 1) as diagnosis, "
+ " (SELECT replace(treatment, 'null', '') FROM hp_clinical_results WHERE hp_clinical_results.patient_no ="
+ " hp_patient_visit.patient_no AND hp_clinical_results.date = hp_patient_visit.date "
+ "ORDER BY 1 DESC LIMIT 1) as treatment, "
+ " (SELECT sum(credit-debit) FROM ac_ledger WHERE ac_ledger.patient_no = "
+ "hp_patient_visit.patient_no AND ac_ledger.date = hp_patient_visit.date) "
+ "as amount, '', "
+ " (SELECT refer_source FROM hp_patient_register WHERE hp_patient_register.patient_no"
+ " = hp_patient_visit.patient_no AND hp_patient_register.date = hp_patient_visit.date"
+ " ORDER BY 1 DESC LIMIT 1) as referral,"
+ " (SELECT comments FROM hp_clinical_results WHERE hp_clinical_results.patient_no "
+ "= hp_patient_visit.patient_no AND hp_clinical_results.date = hp_patient_visit.date"
+ " ORDER BY 1 DESC LIMIT 1 )as comments FROM hp_patient_visit WHERE "
+ " date::date BETWEEN '"
+ beginDate
+ "' AND '"
+ endDate
+ "' "
+ " AND patient_no = ?");
stw.setObject(1, listofAct[i]);
java.sql.ResultSet rsetw = stw.executeQuery();
while (rsetw.next()) {
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
table.getDefaultCell().setColspan(1);
noSeq = noSeq + 1;
// Index
phrase = new Phrase(java.lang.String.valueOf(noSeq), pFontHeader1);
table.addCell(phrase);
// Lab procedure date
phrase =
new Phrase(
new com.afrisoftech.lib.DBObject().getDBObject(rsetw.getString(1), ""),
pFontHeader1);
table.addCell(phrase);
// Patient Number
phrase =
new Phrase(
new com.afrisoftech.lib.DBObject().getDBObject(rsetw.getString(2), ""),
pFontHeader1);
table.addCell(phrase);
// Lab Number
phrase =
new Phrase(
new com.afrisoftech.lib.DBObject().getDBObject(rsetw.getString(3), ""),
pFontHeader1);
table.addCell(phrase);
// Revisit No
phrase =
new Phrase(
new com.afrisoftech.lib.DBObject().getDBObject(rsetw.getString(4), ""),
pFontHeader1);
table.addCell(phrase);
// Patient names
phrase =
new Phrase(
new com.afrisoftech.lib.DBObject().getDBObject(rsetw.getString(5), ""),
pFontHeader1);
table.addCell(phrase);
// Age
phrase =
new Phrase(
new com.afrisoftech.lib.DBObject().getDBObject(rsetw.getString(6), ""),
pFontHeader1);
table.addCell(phrase);
// Gender
phrase =
new Phrase(
new com.afrisoftech.lib.DBObject().getDBObject(rsetw.getString(7), ""),
pFontHeader1);
table.addCell(phrase);
// Village
phrase =
new Phrase(
new com.afrisoftech.lib.DBObject().getDBObject(rsetw.getString(8), ""),
pFontHeader1);
table.addCell(phrase);
// Telephone number
phrase =
new Phrase(
new com.afrisoftech.lib.DBObject().getDBObject(rsetw.getString(9), ""),
pFontHeader1);
table.addCell(phrase);
// Clinical diagnosis
phrase =
new Phrase(
new com.afrisoftech.lib.DBObject().getDBObject(rsetw.getString(10), ""),
pFontHeader1);
table.addCell(phrase);
// Prior treatment
phrase =
new Phrase(
new com.afrisoftech.lib.DBObject().getDBObject(rsetw.getString(11), ""),
pFontHeader1);
table.addCell(phrase);
// Type of specimen
phrase =
new Phrase(
new com.afrisoftech.lib.DBObject().getDBObject(rsetw.getString(12), ""),
pFontHeader1);
table.addCell(phrase);
// Condition of specimen
phrase =
new Phrase(
new com.afrisoftech.lib.DBObject().getDBObject(rsetw.getString(13), ""),
pFontHeader1);
table.addCell(phrase);
// Investigation required
phrase =
new Phrase(
new com.afrisoftech.lib.DBObject().getDBObject(rsetw.getString(14), ""),
pFontHeader1);
table.addCell(phrase);
// Date sample taken
phrase =
new Phrase(
new com.afrisoftech.lib.DBObject().getDBObject(rsetw.getString(15), ""),
pFontHeader1);
table.addCell(phrase);
// Date sample received
phrase =
new Phrase(
new com.afrisoftech.lib.DBObject().getDBObject(rsetw.getString(16), ""),
pFontHeader1);
table.addCell(phrase);
// Amount Paid by client
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase =
new Phrase(
new com.afrisoftech.lib.DBObject().getDBObject(rsetw.getString(17), ""),
pFontHeader1);
table.addCell(phrase);
// Date sample analysed
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase =
new Phrase(
new com.afrisoftech.lib.DBObject().getDBObject(rsetw.getString(18), ""),
pFontHeader1);
table.addCell(phrase);
// Results
phrase =
new Phrase(
new com.afrisoftech.lib.DBObject().getDBObject(rsetw.getString(19), ""),
pFontHeader1);
table.addCell(phrase);
// Date result dispatched
phrase =
new Phrase(
new com.afrisoftech.lib.DBObject().getDBObject(rsetw.getString(20), ""),
pFontHeader1);
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) {
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) {
IOexec.printStackTrace();
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), IOexec.getMessage());
}
}
public java.lang.Object[] getListofStaffNos1(java.lang.Object InvoiceNo) {
java.lang.Object[] listofStaffNos = null;
java.util.Vector listStaffNoVector = new java.util.Vector(1, 1);
try {
java.sql.PreparedStatement pSet1 =
connectDB.prepareStatement(
"SELECT distinct upper(main_service) FROM hp_patient_card WHERE invoice_no = '"
+ InvoiceNo
+ "' and debit <> 0 and main_service is not null and service not ilike 'invoice%' order by upper(main_service)");
// pSet1.setString(1,beginDate.toString());
// pSet1.setString(2,endDate.toString());
java.sql.ResultSet rSet1 = pSet1.executeQuery();
while (rSet1.next()) {
listStaffNoVector.addElement(dbObject.getDBObject(rSet1.getObject(1).toString(), "-"));
}
} catch (java.sql.SQLException sqlExec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), sqlExec.getMessage());
}
listofStaffNos = listStaffNoVector.toArray();
System.out.println("Done list of Staff Nos ...");
return listofStaffNos;
}
public java.lang.Object[] getListofActivities() {
java.lang.Object[] listofActivities = null;
java.util.Vector listActVector = new java.util.Vector(1, 1);
try {
// java.sql.Connection connDB =
// java.sql.DriverManager.getConnection("jdbc:postgresql://localhost:5432/sako","postgres","pilsiner");
java.sql.Statement stmt1 = connectDB.createStatement();
java.sql.PreparedStatement pSet1 =
connectDB.prepareStatement(
"SELECT DISTINCT code,activity FROM pb_activity where activity_category ilike 'I%' order by code");
java.sql.ResultSet rSet1 = pSet1.executeQuery();
while (rSet1.next()) {
System.out.println(rSet1.getObject(1).toString());
listActVector.addElement(rSet1.getObject(1).toString());
}
// System.out.println(rSet1.getObject(1).toString());
} catch (java.sql.SQLException sqlExec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), sqlExec.getMessage());
}
listofActivities = listActVector.toArray();
System.out.println("Done list of activities ...");
return listofActivities;
}
public java.lang.Object[] getListofActivities() {
java.lang.Object[] listofActivities = null;
java.util.Vector listActVector = new java.util.Vector(1, 1);
try {
java.sql.Statement stmt1 = connectDB.createStatement();
java.sql.PreparedStatement pSet1 =
connectDB.prepareStatement("SELECT DISTINCT code FROM pb_activity order by code");
java.sql.ResultSet rSet1 = pSet1.executeQuery();
while (rSet1.next()) {
listActVector.addElement(rSet1.getObject(1).toString());
}
// System.out.println(rSet1.getObject(1).toString());
} catch (java.sql.SQLException sqlExec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), sqlExec.getMessage());
}
listofActivities = listActVector.toArray();
System.out.println("Done list of activities ...");
return listofActivities;
}
public java.lang.Object[] getListofActivities() {
java.lang.Object[] listofActivities = null;
java.util.Vector listActVector = new java.util.Vector(1, 1);
try {
// java.sql.Connection connDB =
// java.sql.DriverManager.getConnection("jdbc:postgresql://localhost:5432/sako","postgres","pilsiner");
java.sql.Statement stmt1 = connectDB.createStatement();
// java.sql.PreparedStatement pSet1 = connectDB.prepareStatement("SELECT DISTINCT dealer FROM
// ac_debtors where date BETWEEN ? AND ? and dealer IS NOT NULL order by dealer");
java.sql.PreparedStatement pSet1 =
connectDB.prepareStatement(
"SELECT DISTINCT account_no,payee FROM ac_debtors where account_no IS NOT NULL and account_no ='"
+ memNo1
+ "' and balance > 0 order by account_no");
// pSet1.setString(1,beginDate.toString());
// pSet1.setString(2,endDate.toString());
// java.sql.ResultSet rSet1 = stmt1.executeQuery("SELECT DISTINCT dealer FROM ac_debtors where
// date BETWEEN '1-2-2003' AND '11-12-2003' and dealer IS NOT NULL order by dealer");
java.sql.ResultSet rSet1 = pSet1.executeQuery();
while (rSet1.next()) {
System.out.println(rSet1.getObject(1).toString());
listActVector.addElement(rSet1.getObject(1).toString());
}
// System.out.println(rSet1.getObject(1).toString());
} catch (java.sql.SQLException sqlExec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), sqlExec.getMessage());
}
listofActivities = listActVector.toArray();
System.out.println("Done list of activities ...");
return listofActivities;
}
public java.lang.Object[] getListofStaffNos() {
java.lang.Object[] listofStaffNos = null;
java.util.Vector listStaffNoVector = new java.util.Vector(1, 1);
try {
java.sql.PreparedStatement pSet1 =
connectDB.prepareStatement(
"SELECT invoice_no FROM ac_debtors WHERE invoice_no BETWEEN ? AND ? AND transaction_type = ? AND payee != ? order by invoice_no");
pSet1.setString(1, INV01);
pSet1.setString(2, INV02);
pSet1.setString(3, "Raise Invoice");
pSet1.setString(4, "''");
java.sql.ResultSet rSet1 = pSet1.executeQuery();
// java.sql.Statement stmt1 = connectDB.createStatement();
// java.sql.ResultSet rSet1 = stmt1.executeQuery("SELECT distinct patient_no FROM
// hp_patient_card WHERE date::date BETWEEN '"+beginDate+"' AND '"+endDate+"' AND payment_mode
// = 'Scheme' order by patient_no");
while (rSet1.next()) {
listStaffNoVector.addElement(rSet1.getObject(1).toString());
}
} catch (java.sql.SQLException sqlExec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), sqlExec.getMessage());
}
listofStaffNos = listStaffNoVector.toArray();
System.out.println("Done list of Staff Nos ...");
return listofStaffNos;
}
public java.lang.Object[] getListofStaffNos() {
java.lang.Object[] listofStaffNos = null;
java.util.Vector listStaffNoVector = new java.util.Vector(1, 1);
try {
java.sql.PreparedStatement stmt1 =
connectDB.prepareStatement(
"SELECT DISTINCT patient_no, name FROM hp_patient_visit where date::date BETWEEN '"
+ beginDate
+ "' AND '"
+ endDate
+ "' AND age::int >= 5 ORDER BY name ASC");
java.sql.ResultSet rSet1 = stmt1.executeQuery();
while (rSet1.next()) {
System.out.println("Patient names : [" + rSet1.getString(2) + "]");
listStaffNoVector.addElement(rSet1.getObject(1).toString());
}
} catch (java.sql.SQLException sqlExec) {
sqlExec.printStackTrace();
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), sqlExec.getMessage());
}
listofStaffNos = listStaffNoVector.toArray();
System.out.println("Done list of Staff Nos ...");
return listofStaffNos;
}
public 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));
try {
java.lang.Class.forName("org.postgresql.Driver");
} catch (java.lang.ClassNotFoundException cnfExec) {
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), cnfExec.getMessage());
}
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.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 (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)));
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 List - Page: ", pFontHeader),
true); // FontFactory.getFont(com.lowagie.text.FontFactory.HELVETICA, 12,
// Font.BOLDITALIC,java.awt.Color.blue));
docPdf.setFooter(footer);
docPdf.open();
try {
com.lowagie.text.pdf.PdfPTable table = new com.lowagie.text.pdf.PdfPTable(9);
int headerwidths[] = {5, 25, 10, 10, 14, 10, 14, 14, 8};
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(5);
phrase =
new Phrase(
"Sales ["
+ CashPoint
+ "] Report : Period : From "
+ dateFormat.format(endDate11)
+ " To "
+ dateFormat.format(endDate1),
pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(4);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase = new Phrase("Printed On : " + date, pFontHeader);
table.addCell(phrase);
} catch (java.text.ParseException psExec) {
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), psExec.getMessage());
}
// Phrase phrase = new Phrase("Patients List As At:" +endDate, pFontHeader);
// table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
table.getDefaultCell().setColspan(1);
// table.getDefaultCell().setBackgroundColor(java.awt.Color.LIGHT_GRAY);
phrase = new Phrase("No", pFontHeader);
table.addCell(phrase);
phrase = new Phrase("ITEM", pFontHeader);
table.addCell(phrase);
phrase = new Phrase("UNIT", pFontHeader);
// table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase = new Phrase("COUNT", pFontHeader);
table.addCell(phrase);
phrase = new Phrase("UNIT COST", pFontHeader);
table.addCell(phrase);
phrase = new Phrase("TOTAL COST", pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase = new Phrase("SELLING PRICE", pFontHeader);
table.addCell(phrase);
phrase = new Phrase("TOTAL " + ks, pFontHeader);
table.addCell(phrase);
phrase = new Phrase("PROFIT " + ks, pFontHeader);
table.addCell(phrase);
phrase = new Phrase("MARKUP%", pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setBackgroundColor(java.awt.Color.WHITE);
table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);
double osBalance11 = 0.00;
double osBalance31 = 0.00;
double current = 0.00;
double osBalance1 = 0.00;
double osBalance3 = 0.00;
try {
java.lang.Object[] listofAct1 = this.getDates();
// for (int k = 0; k < listofAct1.length; k++) {
double osBalance21 = 0.00;
double osBalance22 = 0.00;
double osBalance23 = 0.00;
table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);
table.getDefaultCell().setColspan(8);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
// phrase = new Phrase(listofAct1[k].toString().toUpperCase(), pFontHeader);
// table.addCell(phrase);
java.lang.Object[] listofAct = this.getListofActivities();
// 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);
// java.sql.Statement pSet1 = connectDB.createStatement();
java.sql.Statement st = connectDB.createStatement();
for (int i = 0; i < listofAct.length; i++) {
double qtys = 0.00;
double osBalance2 = 0.00;
// AND st.transfer_price > 0
System.out.println("item" + listofAct[i]);
// java.sql.Statement stbx = connectDB.createStatement();
java.sql.Statement st2 = connectDB.createStatement();
// java.sql.PreparedStatement st1 = connectDB.prepareStatement("select
// ph.description,sum(ph.quantity) as quantity,avg(st.transfer_price)::numeric(10,2)
// as bprice,sum((ph.quantity * st.transfer_price)::numeric(10,2)) as cost, CASE
// WHEN (sum(ph.quantity) > 0 ) THEN
// (sum(ph.amount)/sum(ph.quantity))::numeric(10,2) ELSE 1.00 END AS
// sprice,sum(amount)::numeric(10,2) as amt,sum(amount) - sum(ph.quantity *
// st.transfer_price)::numeric(10,2),CASE WHEN (sum(transfer_price) = 0) THEN 0.00
// ELSE (avg(st.selling_price)/avg(st.transfer_price))::numeric(10,2) END AS markup
// from hp_pharmacy ph,st_stock_prices st WHERE ph.main_service ilike
// '"+CashPoint+"%' AND st.department ilike '"+CashPoint+"%' and ph.description = ?
// AND ph.date_prescribed BETWEEN '"+beginDate+"' AND '"+endDate+"' AND
// ph.description ilike st.product GROUP BY ph.description");
java.sql.PreparedStatement st1 =
connectDB.prepareStatement(
"select ph.description,sum(ph.quantity) as quantity,avg(st.transfer_price)::numeric(10,2) as bprice,sum((ph.quantity * st.transfer_price)::numeric(10,2)) as cost, CASE WHEN (sum(ph.quantity) > 0 ) THEN (sum(ph.amount)/sum(ph.quantity))::numeric(10,2) ELSE 1.00 END AS sprice,sum(amount)::numeric(10,2) as amt,sum(amount) - sum(ph.quantity * st.transfer_price)::numeric(10,2),CASE WHEN (sum(transfer_price) = 0) THEN 0.00 ELSE (avg(st.selling_price)/avg(st.transfer_price))::numeric(10,2) END AS markup from hp_pharmacy ph,st_stock_prices st WHERE ph.main_service ilike '"
+ CashPoint
+ "%' AND st.department ilike '"
+ CashPoint
+ "%' and ph.description = ? AND ph.date_prescribed BETWEEN '"
+ beginDate
+ "' AND '"
+ endDate
+ "' AND ph.description = st.product GROUP BY ph.description");
// java.sql.PreparedStatement stbx = connectDB.prepareStatement("select
// sum(credit-debit) FROM ac_ledger WHERE activity_code ilike '"+gAccount+"%' and
// service_type ilike ? AND date BETWEEN '"+beginDate+"' AND '"+endDate+"' AND
// (transaction_type = 'Revenue' OR transaction_type = 'Refunds') GROUP BY
// service_type");
java.sql.PreparedStatement stbx =
connectDB.prepareStatement(
"select sum(credit-debit) FROM ac_ledger WHERE activity_code = '"
+ gAccount
+ "' and service_type = ? AND date::date BETWEEN '"
+ beginDate
+ "' AND '"
+ endDate
+ "' GROUP BY service_type");
st1.setString(1, listofAct[i].toString());
java.sql.ResultSet rset = st1.executeQuery();
stbx.setString(1, listofAct[i].toString());
java.sql.ResultSet rsetbx = stbx.executeQuery();
while (rset.next()) {
while (rsetbx.next()) {
// table.getDefaultCell().setColspan(1);
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
counter = counter + 1;
phrase = new Phrase("" + counter, pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase(dbObject.getDBObject(rset.getObject(1), "-"), pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(rset.getString(2)),
pFontHeader1);
table.addCell(phrase);
qtys = rset.getDouble(2);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(rset.getString(3)),
pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(rset.getString(4)),
pFontHeader1);
osBalance1 = rset.getDouble(4);
osBalance21 = osBalance21 + rset.getDouble(4);
osBalance11 = rset.getDouble(4);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
/* phrase = new Phrase(new com.afrisoftech.sys.Format2Currency().Format2Currency(rset.getString(6)),pFontHeader1);
osBalance2 = osBalance2 + rset.getDouble(6);
osBalance22 = osBalance22 + rset.getDouble(6);
table.addCell(phrase);
*/
// phrase = new Phrase(new
// com.afrisoftech.sys.Format2Currency().Format2Currency(rsetbx.getString(1)),pFontHeader1);
osBalance2 = rsetbx.getDouble(1);
osBalance22 = osBalance22 + rsetbx.getDouble(1);
if (qtys > 0) {
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(osBalance2 / qtys)),
pFontHeader1);
table.addCell(phrase);
} else {
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(osBalance2 / 1)),
pFontHeader1);
table.addCell(phrase);
}
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(osBalance2)),
pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
// phrase = new Phrase(new
// com.afrisoftech.sys.Format2Currency().Format2Currency(rset.getString(7)),pFontHeader1);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(osBalance2 - osBalance1)),
pFontHeader1);
/* osBalance3 = osBalance3 + rset.getDouble(7);
osBalance23 = osBalance23 + rset.getDouble(7);
osBalance31 = rset.getDouble(7);
*/
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
if (osBalance1 > 0) {
// phrase = new Phrase(new
// com.afrisoftech.sys.Format2Currency().Format2Currency(rset.getString(8)),pFontHeader1);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(
java.lang.String.valueOf(osBalance2 / osBalance1)),
pFontHeader1);
table.addCell(phrase);
} else {
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(osBalance2 / 1)),
pFontHeader1);
table.addCell(phrase);
}
}
}
}
table.getDefaultCell().setBorderColor(java.awt.Color.BLACK);
table.getDefaultCell().setBorder(Rectangle.BOTTOM | Rectangle.TOP);
// while (rsetTotals.next()) {
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase("" + counter, pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(2);
phrase = new Phrase(" ", pFontHeader);
table.addCell(phrase);
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(java.lang.String.valueOf(osBalance21)),
pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase(" ", pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(osBalance22)),
pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(osBalance22 - osBalance21)),
pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
// phrase = new Phrase(" ", pFontHeader);
if (osBalance21 > 0) {
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(osBalance22 / osBalance21)),
pFontHeader);
table.addCell(phrase);
} else {
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(0.00)),
pFontHeader);
table.addCell(phrase);
}
// }
/* table.getDefaultCell().setBorderColor(java.awt.Color.BLACK);
table.getDefaultCell().setBorder(Rectangle.BOTTOM | Rectangle.TOP);
// while (rsetTotals.next()) {
table.getDefaultCell().setColspan(2);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase(" ", pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase("Sum 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(osBalance1)), pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase(" ", pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase = new Phrase(new com.afrisoftech.sys.Format2Currency().Format2Currency(java.lang.String.valueOf(osBalance2)), pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase = new Phrase(new com.afrisoftech.sys.Format2Currency().Format2Currency(java.lang.String.valueOf(osBalance3)), pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase(" ", pFontHeader);
table.addCell(phrase);
*/
docPdf.add(table);
} catch (java.sql.SQLException SqlExec) {
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), SqlExec.getMessage());
}
} catch (com.lowagie.text.BadElementException BadElExec) {
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), BadElExec.getMessage());
}
} catch (java.io.FileNotFoundException fnfExec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), fnfExec.getMessage());
}
} catch (com.lowagie.text.DocumentException lwDocexec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), lwDocexec.getMessage());
}
docPdf.close();
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.util.Calendar calLast = java.util.Calendar.getInstance();
calLast.roll(java.util.Calendar.YEAR, -1);
dateLastyear = calLast.getTime().toString();
System.out.println(this.dateLastyear);
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;
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, 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);
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("CORPORATION TAX - Page: ", pFontHeader),
true); // FontFactory.getFont(com.lowagie.text.FontFactory.HELVETICA, 12,
// Font.BOLDITALIC,java.awt.Color.blue));
docPdf.setFooter(footer);
docPdf.open();
try {
com.lowagie.text.pdf.PdfPTable table = new com.lowagie.text.pdf.PdfPTable(4);
int headerwidths[] = {40, 40, 20, 20};
table.setWidths(headerwidths);
table.setWidthPercentage((100));
Phrase phrase = new Phrase("");
table.getDefaultCell().setBorder(Rectangle.BOTTOM);
table.getDefaultCell().setColspan(2);
try {
java.text.DateFormat dateFormat =
java.text.DateFormat.getDateInstance(java.text.DateFormat.MEDIUM); // MEDIUM);
java.util.Date endDate1 =
dateFormat.parse(endDate.toLocaleString()); // dateInstance.toLocaleString());
System.out.println("" + endDate1);
// phrase = new Phrase(bank +" Report: " +dateFormat.format(formattedDate),
// pFontHeader);
// table.addCell(phrase);
phrase =
new Phrase(
"Tax Computation : ".toUpperCase() + dateFormat.format(endDate1),
pFontHeader);
table.addCell(phrase);
} catch (java.text.ParseException psExec) {
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), psExec.getMessage());
}
table.getDefaultCell().setColspan(2);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase = new Phrase("Printed on : " + date, pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setBackgroundColor(java.awt.Color.WHITE);
table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);
try {
// java.lang.Object[] listofAct = this.getListofActivities();
java.lang.Object[] listofAct1 = this.getListofActivities1();
// java.sql.Connection conDb1 =
// java.sql.DriverManager.getConnection("jdbc:postgresql://localhost:5432/sako","postgres","pilsiner");
// System.out.println(listofAct.length);
java.sql.Statement st4 = connectDB.createStatement();
java.sql.PreparedStatement pstmt31w =
connectDB.prepareStatement(
"delete from ac_corporation_tax WHERE approved = false");
pstmt31w.executeUpdate();
java.sql.Statement st13 = connectDB.createStatement();
java.sql.ResultSet rset13 =
st13.executeQuery(" select sum(amount) from pl_account_view");
// rsetTotals1 = st3.executeQuery("SELECT SUM(debit),SUM(credit) from");
table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);
table.getDefaultCell().setColspan(4);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
table.addCell(" ");
table.getDefaultCell().setColspan(3);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
// phrase = new Phrase(rset3.getObject(1).toString().toUpperCase(), pFontHeader);
phrase = new Phrase("NET INCOME", pFontHeader1);
table.addCell(phrase);
// }
while (rset13.next()) {
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(rset13.getString(1)),
pFontHeader1);
table.addCell(phrase);
pl = rset13.getDouble(1);
java.sql.PreparedStatement pstmtC =
connectDB.prepareStatement(
"insert into ac_corporation_tax values(?, ?, ?, ?, ?,?)");
pstmtC.setObject(1, "" + endDate + "");
pstmtC.setObject(2, "NET INCOME");
pstmtC.setObject(3, "Income");
if (pl < 0) {
pstmtC.setDouble(5, pl * -1);
} else {
pstmtC.setDouble(5, pl);
}
pstmtC.setDouble(4, 0.00);
pstmtC.setDouble(6, 0.00);
pstmtC.executeUpdate();
}
table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);
table.getDefaultCell().setColspan(4);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
table.addCell(" ");
table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);
table.getDefaultCell().setColspan(4);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase("Add/Less: Disallowable Items ".toUpperCase(), pFontHeader1);
table.addCell(phrase);
// table.addCell("Add/Less: Disallowable Items ".toUpperCase(), pFontHeader1);
for (int i = 0; i < listofAct1.length; i++) {
java.sql.Statement st6 = connectDB.createStatement();
java.sql.Statement st7 = connectDB.createStatement();
// java.sql.Statement st8 = connectDB.createStatement();
java.sql.ResultSet rset7 =
st7.executeQuery(
"select description from ac_corporate_disallowables where gl_code = '"
+ listofAct1[i].toString()
+ "'");
java.sql.Statement st8 = connectDB.createStatement();
java.sql.ResultSet rset8 =
st8.executeQuery(
"select SUM(debit-credit) from ac_ledger where activity_code = '"
+ listofAct1[i].toString()
+ "' and date between '"
+ beginDate
+ "' AND '"
+ endDate
+ "'");
// java.sql.ResultSet rset8 = st8.executeQuery("select SUM(debit-credit) from
// ac_ledger where activity_code = '"+listofAct1[i].toString()+"' and date between
// '"+beginDate+"' AND '"+endDate+"'");
// rsetTotals1 = st3.executeQuery("SELECT SUM(debit),SUM(credit) from");
table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
table.addCell(" ");
while (rset7.next()) {
while (rset8.next()) {
table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase(rset7.getObject(1).toString().toUpperCase(), pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(rset8.getString(1)),
pFontHeader1);
table.addCell(phrase);
equity = equity + rset8.getDouble(1);
java.sql.PreparedStatement pstmt111 =
connectDB.prepareStatement(
"insert into ac_corporation_tax values(?, ?, ?, ?, ?,?)");
pstmt111.setObject(1, "" + endDate + "");
pstmt111.setObject(2, rset7.getObject(1).toString());
pstmt111.setObject(3, "Disallowable");
pstmt111.setDouble(4, rset8.getDouble(1));
pstmt111.setDouble(5, 0.00);
pstmt111.setDouble(6, 0.00);
pstmt111.executeUpdate();
}
}
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(equity)),
pFontHeader);
table.addCell(phrase);
}
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
table.getDefaultCell().setColspan(4);
// table.addCell("Add/Less: Allowable Items ");
phrase = new Phrase("Add/Less: Allowable Items ".toUpperCase(), pFontHeader1);
table.addCell(phrase);
// for (int i = 0; i < listofAct1.length; i++) {
java.sql.Statement st6 = connectDB.createStatement();
java.sql.Statement st7 = connectDB.createStatement();
java.sql.Statement st83 = connectDB.createStatement();
java.sql.ResultSet rset83 =
st83.executeQuery("select sum(investment_allow) from ac_asset_allocation");
// rsetTotals1 = st3.executeQuery("SELECT SUM(debit),SUM(credit) from");
table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);
// table.getDefaultCell().setBorder(Rectangle.BOTTOM | Rectangle.TOP);
while (rset83.next()) {
table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase(" ".toUpperCase(), pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase("Investment Allowance".toUpperCase(), pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(rset83.getString(1)),
pFontHeader1);
table.addCell(phrase);
inv = inv + rset83.getDouble(1);
phrase = new Phrase(" ".toUpperCase(), pFontHeader);
table.addCell(phrase);
java.sql.PreparedStatement pstmt1A =
connectDB.prepareStatement(
"insert into ac_corporation_tax values(?, ?, ?, ?, ?,?)");
pstmt1A.setObject(1, "" + endDate + "");
pstmt1A.setObject(2, "Investment Allowance");
pstmt1A.setObject(3, "Allowable");
pstmt1A.setDouble(5, inv);
pstmt1A.setDouble(4, 0.00);
pstmt1A.setDouble(6, 0.00);
pstmt1A.executeUpdate();
}
inv = inv;
java.lang.Object[] listofAct2 = this.getListofActivities2();
for (int y = 0; y < listofAct2.length; y++) {
java.sql.Statement st81 = connectDB.createStatement();
java.sql.ResultSet rset81 =
st81.executeQuery(
"select sum(wear_tear_allow*depreciation_rate/100) from ac_asset_allocation where gl_code = '"
+ listofAct2[y].toString()
+ "'");
while (rset81.next()) {
table.getDefaultCell().setBorderColor(java.awt.Color.BLACK);
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setColspan(1);
wta = wta = +rset81.getDouble(1);
// table.addCell(phrase);
}
Total = Total + wta;
}
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase(" ".toUpperCase(), pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase("Wear & Tear Allowance".toUpperCase(), pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(Total)),
pFontHeader);
table.addCell(phrase);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(inv + Total)),
pFontHeader);
table.addCell(phrase);
java.sql.PreparedStatement pstmtB1 =
connectDB.prepareStatement(
"insert into ac_corporation_tax values(?, ?, ?, ?, ?,?)");
pstmtB1.setObject(1, "" + endDate + "");
pstmtB1.setObject(2, "Wear & Tear Allowance");
pstmtB1.setObject(3, "Allowable");
pstmtB1.setDouble(5, Total);
pstmtB1.setDouble(4, 0.00);
pstmtB1.setDouble(6, 0.00);
pstmtB1.executeUpdate();
table.getDefaultCell().setColspan(3);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase("ADJUSTED TAXABLE INCOME".toUpperCase(), pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
if (pl < 0) {
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(
java.lang.String.valueOf((inv + Total + pl) - (-equity))),
pFontHeader);
table.addCell(phrase);
taxableIncome = (inv + Total + pl) - (-equity);
} else {
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf((inv + Total + pl) - equity)),
pFontHeader);
table.addCell(phrase);
taxableIncome = (inv + Total + pl) - (equity);
}
java.sql.Statement st8111 = connectDB.createStatement();
java.sql.ResultSet rset8111 =
st8111.executeQuery(
"select count(debit) from ac_corporation_tax where category Ilike 'Tax Loss BFWD'");
while (rset8111.next()) {
taxbf = rset8111.getInt(1);
}
if (taxbf > 0) {
java.sql.Statement st811 = connectDB.createStatement();
java.sql.ResultSet rset811 =
st811.executeQuery(
"select sum(debit-tax_divided) as debit from ac_corporation_tax WHERE category Ilike 'Tax loss BFWD'");
while (rset811.next()) {
table.getDefaultCell().setColspan(3);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase("TAX LOSSES BFWD".toUpperCase(), pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
table.getDefaultCell().setColspan(1);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(rset811.getString(1)),
pFontHeader1);
table.addCell(phrase);
loss = rset811.getDouble(1);
}
} else {
table.getDefaultCell().setColspan(3);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase("TAX LOSSES BFWD".toUpperCase(), pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(0.00)),
pFontHeader);
table.addCell(phrase);
loss = 0.00;
}
table.getDefaultCell().setColspan(3);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase("TAXABLE INCOME".toUpperCase(), pFontHeader1);
table.addCell(phrase);
taxable = taxableIncome - loss;
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(taxable)),
pFontHeader);
table.addCell(phrase);
java.sql.Statement st8112 = connectDB.createStatement();
java.sql.ResultSet rset8112 =
st8112.executeQuery(
" select ps.rate from pb_statutory ps, pb_activity pa where ps.gl_code = pa.code and pa.activity_category ilike 'CTX'");
while (rset8112.next()) {
table.getDefaultCell().setColspan(3);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase("TAX DUE".toUpperCase(), pFontHeader1);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
table.getDefaultCell().setColspan(1);
// phrase = new Phrase(new
// com.afrisoftech.sys.Format2Currency().Format2Currency(rset8112.getString(1)),
// pFontHeader1);
taxRate = rset8112.getDouble(1);
}
taxDue = taxable * taxRate / 100;
if (taxDue > 0) {
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(taxDue)),
pFontHeader);
table.addCell(phrase);
} else {
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(0.00)),
pFontHeader);
table.addCell(phrase);
}
java.sql.PreparedStatement pstmt1 =
connectDB.prepareStatement(
"insert into ac_corporation_tax values(?, ?, ?, ?, ?,?)");
pstmt1.setObject(1, "" + endDate + "");
if (taxDue < 0) {
pstmt1.setObject(2, "Tax Loss BFWD");
pstmt1.setObject(3, "Tax Loss BFWD");
pstmt1.setDouble(4, taxable * -1);
pstmt1.setDouble(6, 0.00);
} else {
pstmt1.setObject(2, "Tax For The Year");
pstmt1.setObject(3, "Tax Due");
pstmt1.setDouble(4, 0.00);
pstmt1.setDouble(6, taxDue);
}
pstmt1.setDouble(5, 0.00);
pstmt1.executeUpdate();
docPdf.add(table);
} catch (java.sql.SQLException SqlExec) {
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), SqlExec.getMessage());
}
} catch (com.lowagie.text.BadElementException BadElExec) {
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), BadElExec.getMessage());
}
} catch (java.io.FileNotFoundException fnfExec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), fnfExec.getMessage());
}
} catch (com.lowagie.text.DocumentException lwDocexec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), lwDocexec.getMessage());
}
docPdf.close();
com.afrisoftech.lib.PDFRenderer.renderPDF(tempFile);
} catch (java.io.IOException IOexec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), IOexec.getMessage());
}
}
public void generatePdf() {
java.lang.Object[][] rangeDates = ageingSeries.getAgeingDateSeries();
// ageingDates = ageingSeries.getAgeingDateSeries();
double columnTotals[] = new double[rangeDates.length];
java.lang.Process wait_for_Pdf2Show;
java.util.Calendar cal = java.util.Calendar.getInstance();
java.util.Date dateStampPdf = cal.getTime();
java.lang.String pdfDateStamp = dateStampPdf.toString();
int interval = 0;
try {
java.io.File tempFile =
java.io.File.createTempFile("REP" + this.getDateLable() + "_", ".pdf");
tempFile.deleteOnExit();
java.lang.Runtime rt = java.lang.Runtime.getRuntime();
java.lang.String debitTotal = null;
java.lang.String creditTotal = null;
com.lowagie.text.Document docPdf = new com.lowagie.text.Document();
java.util.Calendar calendar = java.util.Calendar.getInstance();
long dateNow = calendar.getTimeInMillis();
java.sql.Date datenowSql = new java.sql.Date(dateNow);
System.out.println(datenowSql.toString());
try {
try {
com.lowagie.text.pdf.PdfWriter.getInstance(
docPdf, new java.io.FileOutputStream(tempFile));
String compName = null;
String date = null;
try {
// java.sql.Connection conDb =
// java.sql.DriverManager.getConnection("jdbc:postgresql://localhost:5432/sako","postgres","pilsiner");
java.sql.Statement st3 = connectDB.createStatement();
java.sql.Statement st4 = connectDB.createStatement();
java.sql.ResultSet rset2 =
st3.executeQuery("SELECT hospital_name from pb_hospitalprofile");
java.sql.ResultSet rset4 = st4.executeQuery("SELECT date('now') as Date");
while (rset2.next()) compName = rset2.getObject(1).toString();
while (rset4.next()) date = rset4.getObject(1).toString();
com.lowagie.text.HeaderFooter headerFoter =
new com.lowagie.text.HeaderFooter(
new Phrase("" + compName, pFontHeader2),
false); // FontFactory.getFont(com.lowagie.text.FontFactory.HELVETICA, 14,
// Font.BOLDITALIC,java.awt.Color.blue)));
// com.lowagie.text.HeaderFooter headerFoter = new com.lowagie.text.HeaderFooter(new
// Phrase(""+compName+""),false);//
// FontFactory.getFont(com.lowagie.text.FontFactory.HELVETICA, 14,
// Font.BOLDITALIC,java.awt.Color.blue)));
headerFoter.setAlignment(com.lowagie.text.HeaderFooter.ALIGN_CENTER);
docPdf.setHeader(headerFoter);
} catch (java.sql.SQLException SqlExec) {
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), SqlExec.getMessage());
}
com.lowagie.text.HeaderFooter footer =
new com.lowagie.text.HeaderFooter(
new Phrase("Ageing Page: ", pFontHeader),
true); // FontFactory.getFont(com.lowagie.text.FontFactory.HELVETICA, 12,
// Font.BOLDITALIC,java.awt.Color.blue));
docPdf.setFooter(footer);
docPdf.open();
double Totals = 0.00;
double OS = 0.00;
try {
com.lowagie.text.pdf.PdfPTable table =
new com.lowagie.text.pdf.PdfPTable(rangeDates.length + 3);
String headerWidths = null;
java.util.Vector headerVector = new java.util.Vector(1, 1);
int z = rangeDates.length;
int headerwidths[] = {35, 15, 15, 10, 10};
table.setWidths(headerwidths);
table.setWidthPercentage((107));
table.setHeaderRows(2);
table.getDefaultCell().setBorder(Rectangle.BOTTOM);
table.getDefaultCell().setColspan(5);
Phrase phrase = new Phrase("");
table.getDefaultCell().setColspan(3);
try {
java.text.DateFormat dateFormat =
java.text.DateFormat.getDateInstance(java.text.DateFormat.MEDIUM); // MEDIUM);
java.util.Date endDate2 = dateFormat.parse(endDate.toLocaleString());
phrase =
new Phrase("Weekly Sales Comparison " + dateFormat.format(endDate2), pFontHeader);
table.addCell(phrase);
} catch (java.text.ParseException psExec) {
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), psExec.getMessage());
}
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
table.getDefaultCell().setColspan(2);
phrase = new Phrase("Printed on : " + date, pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase("DESCRIPTION", pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_CENTER);
for (int x = 0; x < rangeDates.length; x++) {
int days = 1;
try {
// Date parser
java.text.SimpleDateFormat dateFormat =
new java.text.SimpleDateFormat("EEE MMM dd HH:mm:ss zzz yyyy");
com.afrisoftech.lib.DateFormatter dateFormatter =
new com.afrisoftech.lib.DateFormatter(
dateFormat.parse(rangeDates[x][0].toString().trim()), "w");
com.afrisoftech.lib.DateFormatter yearFormatterCurrent =
new com.afrisoftech.lib.DateFormatter(endDate, "yy");
java.lang.String monthString = dateFormatter.getDateString();
java.lang.String yearStringCurrent = yearFormatterCurrent.getDateString();
// int days = 1;
// if (x < 1) {
com.afrisoftech.lib.DateFormatter dateFormatterCurrent =
new com.afrisoftech.lib.DateFormatter(endDate, "w");
com.afrisoftech.lib.DateFormatter yearFormatterCurrent1 =
new com.afrisoftech.lib.DateFormatter(endDate, "yy");
java.lang.String monthStringCurrent = dateFormatterCurrent.getDateString();
java.lang.String yearStringCurrent1 = yearFormatterCurrent1.getDateString();
// phrase = new Phrase("Week ["+monthStringCurrent+"/"+yearStringCurrent1+"]"
// ,pFontHeader);
// } else {
// phrase = new Phrase("Week ["+monthString+"/"+yearStringCurrent+"]",
// pFontHeader);
// phrase = new Phrase("+ "+x*days +"
// Month",pFontHeader);
interval = x;
// }
table.addCell(phrase);
// Catch java.text.parse exception.
} catch (java.text.ParseException prs) {
prs.printStackTrace();
}
}
// table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase("Change % (+/-)", pFontHeader);
table.addCell(phrase);
phrase = new Phrase("Change In Reporting Rate", pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setBackgroundColor(java.awt.Color.WHITE);
// table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);
try {
double GrandTotal = 0.00;
double Over120Total = 0.00;
double TurnOver = 0.00;
java.lang.Object[] listofAct = this.getListofActivities();
// java.sql.Connection conDb1 =
// java.sql.DriverManager.getConnection("jdbc:postgresql://localhost:5432/sako","postgres","pilsiner");
System.out.println(listofAct.length);
double Over120 = 0.00;
for (int i = 0; i < listofAct.length; i++) {
double TotalCount = 0.00;
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
table.getDefaultCell().setColspan(1);
java.sql.Statement stmta1 = connectDB.createStatement();
java.sql.PreparedStatement pSeta1 =
connectDB.prepareStatement(
"SELECT count(activity_code) FROM ac_ledger where activity_code = '"
+ listofAct[i]
+ "'");
java.sql.PreparedStatement pset22 =
connectDB.prepareStatement(
"select activity from pb_activity WHERE code = ?"); // < '"+endDate+"'::date
// and date >
// '"+endDate+"'::date -
// 30 group by dealer");
// java.sql.PreparedStatement pset22 = connectDB.prepareStatement("select
// description from ac_ledger WHERE activity_code = ?");//< '"+endDate+"'::date and
// date > '"+endDate+"'::date - 30 group by dealer");
pset22.setString(1, listofAct[i].toString().toUpperCase());
java.sql.ResultSet rSeta1 = pSeta1.executeQuery();
while (rSeta1.next()) {
name = rSeta1.getInt(1);
}
if (name > 0) {
java.sql.ResultSet rset22 = pset22.executeQuery();
while (rset22.next()) {
table.getDefaultCell().setColspan(1);
// table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase =
new Phrase(
dbObject.getDBObject(rset22.getObject(1).toString().toUpperCase(), "-"),
pFontHeader1);
table.addCell(phrase);
}
java.sql.Statement st2 = connectDB.createStatement();
java.sql.Statement st21 = connectDB.createStatement();
java.sql.Statement st22 = connectDB.createStatement();
java.sql.Statement st23 = connectDB.createStatement();
java.sql.Statement st211 = connectDB.createStatement();
java.sql.Statement st221 = connectDB.createStatement();
java.sql.Statement st231 = connectDB.createStatement();
java.sql.Statement st2A = connectDB.createStatement();
java.sql.Statement st2B = connectDB.createStatement();
java.sql.Statement st2C = connectDB.createStatement();
java.sql.Statement stc = connectDB.createStatement();
System.out.println("Dealer Is : [" + listofAct[i] + "].");
java.sql.PreparedStatement pset1 =
connectDB.prepareStatement(
"select sum(credit-debit) from ac_ledger WHERE activity_code = ? AND date = '"
+ rangeDates[rangeDates.length - 1][0]
+ "'"); // AND '"+rangeDates[rangeDates.length - 1][1]+"'");//<
// '"+endDate+"'::date and date > '"+endDate+"'::date - 30
// group by dealer");
pset1.setString(1, listofAct[i].toString());
java.sql.ResultSet rset1 = pset1.executeQuery();
// java.sql.PreparedStatement pset111 = connectDB.prepareStatement("select
// sum(credit-debit) from ac_ledger WHERE activity_code = ? AND date <
// '"+endDate+"'");//< '"+endDate+"'::date and date > '"+endDate+"'::date - 30
// group by dealer");
// pset111.setString(1,listofAct[i].toString());
// java.sql.ResultSet rset111 = pset111.executeQuery();
// java.sql.Statement st02 = connectDB.createStatement();
// java.sql.PreparedStatement pset112 = connectDB.prepareStatement("select
// sum(credit-debit) from ac_ledger WHERE activity_code = ? AND date <
// '"+endDate+"'");//< '"+endDate+"'::date and date > '"+endDate+"'::date - 30
// group by dealer");
// pset112.setString(1,listofAct[i].toString());
// java.sql.ResultSet rset112 = pset112.executeQuery();
for (int t = 0; t < rangeDates.length; t++) {
java.sql.Statement st01 = connectDB.createStatement();
java.sql.PreparedStatement pset =
connectDB.prepareStatement(
"select sum(credit-debit) from ac_ledger WHERE activity_code = ? AND date between '"
+ rangeDates[t][0]
+ "' AND '"
+ rangeDates[t][1]
+ "'"); // < '"+endDate+"'::date and date > '"+endDate+"'::date - 30
// group by dealer");
pset.setString(1, listofAct[i].toString().toUpperCase());
java.sql.ResultSet rset = pset.executeQuery();
while (rset.next()) {
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(rset.getString(1)),
pFontHeader1);
table.addCell(phrase);
columnTotals[t] = columnTotals[t] + rset.getDouble(1);
TotalCount = TotalCount + rset.getDouble(1);
TurnOver = rset.getDouble(1);
}
}
// phrase = new Phrase(new
// com.afrisoftech.sys.Format2Currency().Format2Currency(java.lang.String.valueOf(TotalCount)), pFontHeader);
// table.addCell(phrase);
while (rset1.next()) {
Over120 = rset1.getDouble(1);
if (Over120 > 0) {
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(
java.lang.String.valueOf((Over120 / TurnOver) * 100)),
pFontHeader);
table.addCell(phrase);
phrase = new Phrase("+", pFontHeader);
table.addCell(phrase);
} else {
phrase = new Phrase(java.lang.String.valueOf(0.00), pFontHeader);
table.addCell(phrase);
phrase = new Phrase("", pFontHeader);
table.addCell(phrase);
}
}
/* }else{
if (TotalCount <= 0 && Over120 > 0){
phrase = new Phrase(java.lang.String.valueOf((Over120/TurnOver)*100), pFontHeader);
table.addCell(phrase);
phrase = new Phrase("+", pFontHeader);
table.addCell(phrase);
}else{
if (TotalCount > 0 && Over120 < 0){
phrase = new Phrase(java.lang.String.valueOf((Over120/TurnOver)*100), pFontHeader);
table.addCell(phrase);
phrase = new Phrase("-", pFontHeader);
table.addCell(phrase);
}else{
if (TotalCount > 0 && Over120 == 0){
phrase = new Phrase(java.lang.String.valueOf((Over120/TurnOver)*100), pFontHeader);
table.addCell(phrase);
phrase = new Phrase("-", pFontHeader);
table.addCell(phrase);
}else{
if (TotalCount < 0 && Over120 < 0){
phrase = new Phrase(java.lang.String.valueOf((Over120/TurnOver)*100), pFontHeader);
table.addCell(phrase);
phrase = new Phrase("-", pFontHeader);
table.addCell(phrase);
}else{
phrase = new Phrase(java.lang.String.valueOf(0.00), pFontHeader);
table.addCell(phrase);
phrase = new Phrase("", pFontHeader);
table.addCell(phrase);
}
}
}
}
}*/
// }
}
}
table.getDefaultCell().setBorderColor(java.awt.Color.BLACK);
table.getDefaultCell().setBorder(Rectangle.BOTTOM | Rectangle.TOP);
// while (rsetTotals.next()) {
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase("Total", pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
// phrase = new Phrase(new
// com.afrisoftech.sys.Format2Currency().Format2Currency(rsetTotals.getString(1)),
// pFontHeader);
for (int x = 0; x < rangeDates.length; x++) {
// phrase = new Phrase("Current"+2*x,pFontHeader);
// table.addCell(phrase);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(columnTotals[x])),
pFontHeader);
table.addCell(phrase);
}
phrase = new Phrase(" ", pFontHeader);
table.addCell(phrase);
table.addCell(phrase);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(Over120Total)),
pFontHeader);
// table.addCell(phrase);
phrase =
new Phrase(
new com.afrisoftech.sys.Format2Currency()
.Format2Currency(java.lang.String.valueOf(OS)),
pFontHeader);
// table.addCell(phrase);
docPdf.add(table);
} catch (java.sql.SQLException SqlExec) {
SqlExec.printStackTrace();
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), SqlExec.getMessage());
}
} catch (com.lowagie.text.BadElementException BadElExec) {
// Bad
javax.swing.JOptionPane.showMessageDialog(
new javax.swing.JFrame(), BadElExec.getMessage());
}
} catch (java.io.FileNotFoundException fnfExec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), fnfExec.getMessage());
}
} catch (com.lowagie.text.DocumentException lwDocexec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), lwDocexec.getMessage());
}
docPdf.close();
try {
if (System.getProperty("os.name").equalsIgnoreCase("Linux")) {
System.out.println(tempFile);
wait_for_Pdf2Show = rt.exec("kghostview " + tempFile + "");
wait_for_Pdf2Show.waitFor();
} else {
wait_for_Pdf2Show =
rt.exec("c:/Program Files/Adobe/Acrobat 5.0/Reader/AcroRd32.exe " + tempFile);
wait_for_Pdf2Show.waitFor();
}
} catch (java.lang.InterruptedException intrExec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), intrExec.getMessage());
}
} catch (java.io.IOException IOexec) {
javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), IOexec.getMessage());
}
}
public 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.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.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 = 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) {
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;
int titleFont = 0;
int bodyFont = 0;
float Widths = 0;
float Heights = 0;
float Margins = 0;
try {
connectDB.setAutoCommit(false);
java.sql.Statement stm1 = connectDB.createStatement();
java.sql.ResultSet rse1 =
stm1.executeQuery(
"select header_font,body_font,width,height,margins from receipt_pref");
while (rse1.next()) {
titleFont = rse1.getInt(1);
bodyFont = rse1.getInt(2);
Widths = rse1.getFloat(3);
Heights = rse1.getFloat(4);
Margins = rse1.getFloat(5);
}
} catch (java.sql.SQLException sq) {
try {
connectDB.rollback();
} catch (java.sql.SQLException sql) {
// javax.swing.JOptionPane.showMessageDialog(this,sql.getMessage(),"Error
// Message!",javax.swing.JOptionPane.ERROR_MESSAGE);
}
System.out.println(sq.getMessage());
// javax.swing.JOptionPane.showMessageDialog(this,sq.getMessage(),
// "Error",javax.swing.JOptionPane.ERROR_MESSAGE);
}
com.lowagie.text.Font pFontHeader2 =
FontFactory.getFont(FontFactory.HELVETICA, bodyFont, Font.BOLD);
com.lowagie.text.Font pFontHeader =
FontFactory.getFont(FontFactory.HELVETICA, bodyFont, Font.NORMAL);
com.lowagie.text.Font pFontHeader1 =
FontFactory.getFont(FontFactory.HELVETICA, titleFont, Font.BOLD);
com.lowagie.text.Font pFontHeader11 =
FontFactory.getFont(FontFactory.TIMES_ITALIC, 8, Font.BOLD);
// com.lowagie.text.Document docPdf = new com.lowagie.text.Document(new
// Rectangle(java.lang.Float.parseFloat(System.getProperty("papersize_width")),
// java.lang.Float.parseFloat(System.getProperty("papersize_legnth"))),java.lang.Float.parseFloat(System.getProperty("receiptPageMargin")),java.lang.Float.parseFloat(System.getProperty("receiptPageMargin")),java.lang.Float.parseFloat(System.getProperty("receiptPageMargin")),java.lang.Float.parseFloat(System.getProperty("receiptPageMargin")));
///// com.lowagie.text.Document docPdf = new com.lowagie.text.Document(new Rectangle((Widths),
// Heights), Margins, Margins, Margins, Margins);
com.lowagie.text.Document docPdf = new com.lowagie.text.Document(PageSize.A4.rotate());
// com.lowagie.text.Document docPdf = new com.lowagie.text.Document(new
// Rectangle(java.lang.Float.parseFloat(System.getProperty("papersize_width")),
// java.lang.Float.parseFloat(System.getProperty("papersize_legnth"))));
try {
try {
com.lowagie.text.pdf.PdfWriter.getInstance(
docPdf, new java.io.FileOutputStream(tempFile));
String compName = null;
String date = null;
PdfWriter writer = PdfWriter.getInstance(docPdf, new FileOutputStream(tempFile));
docPdf.open();
PdfContentByte cb = writer.getDirectContent();
String footers = null;
try {
com.lowagie.text.pdf.PdfPTable table = new com.lowagie.text.pdf.PdfPTable(6);
int headerwidths[] = {15, 15, 15, 15, 15, 15};
table.setWidths(headerwidths);
table.setWidthPercentage((100));
table.getDefaultCell().setColspan(6);
table.getDefaultCell().setFixedHeight(55);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_CENTER);
table.addCell(Image.getInstance(com.afrisoftech.lib.CompanyLogo.getPath2Logo()));
table.getDefaultCell().setFixedHeight(16);
Phrase phrase = new Phrase("");
// table.addCell(phrase);
table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);
table.getDefaultCell().setColspan(1);
table.getDefaultCell().setBackgroundColor(java.awt.Color.WHITE);
table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);
try {
String patAge = "-";
java.sql.Statement st = connectDB.createStatement();
java.sql.Statement st1 = connectDB.createStatement();
java.sql.Statement st2 = connectDB.createStatement();
java.sql.Statement st5 = connectDB.createStatement();
java.sql.Statement st6 = connectDB.createStatement();
java.sql.Statement st51 = connectDB.createStatement();
java.sql.Statement st3 = connectDB.createStatement();
java.sql.ResultSet rset3 =
st3.executeQuery(
"select hospital_name,box_no,main_telno,main_faxno from pb_hospitalprofile");
java.sql.ResultSet rs =
st.executeQuery(
"select patient_no,patient_name,discharge_no,discharge_date,doctor, date_part('hour', discharge_time::time) ||':' || date_part('min', discharge_time::time) ||':'||round(date_part('sec', discharge_time::time)),admission_date,user_name,ward from hp_patient_discharge cb where inv_no = '"
+ invoiceNo
+ "'");
System.out.println(MNo);
while (rset3.next()) {
table.getDefaultCell().setColspan(6);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_CENTER);
phrase = new Phrase(rset3.getObject(1).toString(), pFontHeader1);
table.addCell(phrase);
phrase = new Phrase("P.O. BOX : " + rset3.getObject(2).toString(), pFontHeader2);
table.addCell(phrase);
phrase =
new Phrase(
"TEL : "
+ rset3.getObject(3).toString()
+ " FAX : "
+ rset3.getObject(4).toString(),
pFontHeader2);
table.addCell(phrase);
}
// table.getDefaultCell().setBorderColor(java.awt.Color.BLACK);
// table.getDefaultCell().setBorderWidth(Rectangle.TOP);
table.getDefaultCell().setColspan(6);
phrase = new Phrase("PATIENT RELEASE FORM", pFontHeader1);
table.addCell(phrase);
java.sql.PreparedStatement pstmtHeader =
connectDB.prepareStatement("SELECT nextval('release_seq_no'), current_user");
java.sql.ResultSet rsetheader = pstmtHeader.executeQuery();
table.getDefaultCell().setColspan(2);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase = new Phrase("Form No : 501".toUpperCase(), pFontHeader2);
table.addCell(phrase);
while (rsetheader.next()) {
table.getDefaultCell().setColspan(2);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase =
new Phrase("Serial No : ".toUpperCase() + rsetheader.getInt(1), pFontHeader2);
table.addCell(phrase);
table.getDefaultCell().setColspan(2);
phrase =
new Phrase(
"Printed by : ".toUpperCase() + rsetheader.getString(2).toUpperCase(),
pFontHeader2);
table.addCell(phrase);
}
table.getDefaultCell().setColspan(2);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
// phrase = new Phrase("Internal Claim No : ".toUpperCase(), pFontHeader);
// table.addCell(phrase);
java.sql.PreparedStatement pstmtClaimNo =
connectDB.prepareStatement(
"SELECT claim_no FROM hp_patient_discharge WHERE patient_no = ?");
pstmtClaimNo.setString(1, MNo);
java.sql.ResultSet rsetClaimNo = pstmtClaimNo.executeQuery();
table.getDefaultCell().setColspan(2);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
// while (rsetClaimNo.next()) {
// phrase = new Phrase("Internal Claim No : " + invoiceNo, pFontHeader);
// }
// table.addCell(phrase);
table.getDefaultCell().setColspan(2);
String claimNumber = "";
while (rsetClaimNo.next()) {
claimNumber = rsetClaimNo.getString(1).toUpperCase(); /* + rsheader.getString(6)*/
}
System.out.println("Printing claim number : [" + claimNumber + "]");
phrase =
new Phrase(
"NHIF Claim Number : ".toUpperCase() + claimNumber.toUpperCase(),
pFontHeader);
table.addCell(phrase);
while (rs.next()) {
table.getDefaultCell().setColspan(2);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase =
new Phrase(
"Discharge No : ".toUpperCase() + rs.getString(3).toUpperCase(),
pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(2);
table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
phrase =
new Phrase(
"Date : ".toUpperCase() + rs.getObject(4).toString().toUpperCase(),
pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(2);
phrase =
new Phrase(
"TIME : ".toUpperCase() + rs.getString(6).toUpperCase(), pFontHeader);
table.addCell(phrase);
patNo = rs.getString(1);
table.getDefaultCell().setColspan(2);
phrase =
new Phrase("Patient No : ".toUpperCase() + patNo.toUpperCase(), pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(2);
phrase =
new Phrase(
"Patient Name : ".toUpperCase() + rs.getObject(2).toString().toUpperCase(),
pFontHeader);
patientName = rs.getObject(2).toString();
table.addCell(phrase);
table.getDefaultCell().setColspan(2);
java.sql.ResultSet rsw =
st1.executeQuery(
"SELECT ('now'::date-year_of_birth::date)/365 FROM hp_inpatient_register WHERE patient_no = '"
+ rs.getString(1)
+ "'");
while (rsw.next()) {
phrase =
new Phrase(
"Age: ".toUpperCase() + rsw.getObject(1).toString().toUpperCase(),
pFontHeader);
table.addCell(phrase);
}
table.getDefaultCell().setColspan(2);
phrase =
new Phrase(
"Admission Date : ".toUpperCase() + rs.getString(7).toUpperCase(),
pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(2);
phrase =
new Phrase(
"Discharge Date : ".toUpperCase()
+ rs.getObject(4).toString().toUpperCase(),
pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(2);
phrase =
new Phrase(
"Ward : ".toUpperCase() + rs.getObject(9).toString().toUpperCase(),
pFontHeader);
table.addCell(phrase);
}
table.getDefaultCell().setColspan(6);
phrase = new Phrase(" ", pFontHeader);
table.addCell(phrase);
phrase = new Phrase("Method of Payment(Tick where appropriate)", pFontHeader11);
table.addCell(phrase);
phrase =
new Phrase(
"Cash [ ] / Cheque [ ] / N.H.I.F [ ] / CO-OP [ ] / M.R.M [ ] / OTHERS.......................................................... Final Invoice No : "
+ invoiceNo.toUpperCase(),
pFontHeader2);
table.addCell(phrase);
table.getDefaultCell().setColspan(6);
phrase = new Phrase(" ", pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(2);
phrase =
new Phrase("Finance Clearance : ____________________".toUpperCase(), pFontHeader);
table.addCell(phrase);
phrase = new Phrase("Sign/Stamp: ______________________".toUpperCase(), pFontHeader);
table.addCell(phrase);
phrase =
new Phrase(
"Check-Out Date and Time: _____________________".toUpperCase(), pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(6);
phrase = new Phrase(" ", pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(2);
phrase =
new Phrase(
"Nurse Clearance : _______________________".toUpperCase(), pFontHeader);
table.addCell(phrase);
phrase = new Phrase("Sign/Stamp: ______________________".toUpperCase(), pFontHeader);
table.addCell(phrase);
phrase =
new Phrase(
"Check-Out Date and Time: ______________________".toUpperCase(), pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(6);
phrase = new Phrase(" ", pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(2);
phrase =
new Phrase("Security Clearance: ___________________".toUpperCase(), pFontHeader);
table.addCell(phrase);
phrase = new Phrase("Sign/Stamp: ___________________".toUpperCase(), pFontHeader);
table.addCell(phrase);
phrase =
new Phrase(
"Check-Out Date and Time: ____________________".toUpperCase(), pFontHeader);
table.addCell(phrase);
table.getDefaultCell().setColspan(6);
// table.addCell(phrase);
table.getDefaultCell().setColspan(6);
phrase = new Phrase(" ", pFontHeader);
table.addCell(phrase);
phrase =
new Phrase(
"NB This form should be filled in duplicate, one copy to be retained in the ward and the other one be left at the Main Gate. PLEASE NOTE THAT CHECK-OUT DATE MUST BE THE DATE THE PATIENT LEAVES THE HOSPITAL",
pFontHeader11);
table.addCell(phrase);
phrase = new Phrase(" ");
table.addCell(phrase);
Barcode128 code128 = new Barcode128();
code128.setCode(patNo + " " + patientName + " " + invoiceNo);
code128.setBarHeight(20);
code128.setTextAlignment(Element.ALIGN_CENTER);
docPdf.add(table);
System.out.println(cb);
docPdf.add(code128.createImageWithBarcode(cb, null, null));
} 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();
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());
}
}
