public void generatePdf(java.lang.String memNo) {

    java.lang.Process wait_for_Pdf2Show;

    java.util.Calendar cal = java.util.Calendar.getInstance();

    java.util.Date dateStampPdf = cal.getTime();

    java.lang.String pdfDateStamp = dateStampPdf.toString();

    try {

      java.io.File tempFile =
          java.io.File.createTempFile("REP" + this.getDateLable() + "_", ".pdf");

      tempFile.deleteOnExit();

      java.lang.Runtime rt = java.lang.Runtime.getRuntime();

      java.lang.String debitTotal = null;

      java.lang.String creditTotal = null;

      // com.lowagie.text.Document docPdf = new com.lowagie.text.Document();
      com.lowagie.text.Document docPdf = new com.lowagie.text.Document(PageSize.A4.rotate());
      double osBalance1 = 0.00;
      double osBalance = 0.00;
      double current = 0.00;
      double osBalancebf = 0.00;
      try {

        try {

          com.lowagie.text.pdf.PdfWriter.getInstance(
              docPdf, new java.io.FileOutputStream(tempFile));

          String compName = null;
          String date = null;
          String Messg = null;

          docPdf.open();

          try {

            java.util.Calendar calendar = java.util.Calendar.getInstance();

            long dateNow = calendar.getTimeInMillis();

            java.sql.Date datenowSql = new java.sql.Date(dateNow);

            System.out.println(datenowSql.toString());

            java.lang.Object listofStaffNos[] = this.getListofStaffNos();

            com.lowagie.text.pdf.PdfPTable table1 = new com.lowagie.text.pdf.PdfPTable(7);
            //  com.lowagie.text.Table table = new com.lowagie.text.Table(7);

            // table.endHeaders();

            int headerwidths[] = {15, 15, 30, 15, 15, 15, 15};

            table1.setWidths(headerwidths);
            //  if (docPdf.getPageNumber() > 1) {
            //  table1.setHeaderRows(4);
            //  }
            table1.setWidthPercentage((100));

            table1.getDefaultCell().setBorder(Rectangle.BOTTOM);

            table1.getDefaultCell().setColspan(7);

            Phrase phrase = new Phrase();

            //  table.addCell(phrase);

            table1.getDefaultCell().setColspan(1);
            table1.getDefaultCell().setBackgroundColor(java.awt.Color.WHITE);
            table1.getDefaultCell().setBorderColor(java.awt.Color.WHITE);

            try {
              java.sql.Statement st3 = connectDB.createStatement();
              java.sql.Statement st2x = connectDB.createStatement();

              java.sql.ResultSet rset2x =
                  st2x.executeQuery("SELECT rep_currency from pb_hospitalprofile");
              while (rset2x.next()) {
                ks = rset2x.getObject(1).toString();
              }
              java.sql.ResultSet rset3 = st3.executeQuery("select header_name from pb_header");
              while (rset3.next()) {
                table1.getDefaultCell().setColspan(7);

                table1.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_CENTER);
                phrase = new Phrase(rset3.getObject(1).toString(), pFontHeader11);
                table1.addCell(phrase);
              }

            } catch (java.sql.SQLException SqlExec) {

              javax.swing.JOptionPane.showMessageDialog(
                  new javax.swing.JFrame(), SqlExec.getMessage());
            }
            docPdf.add(table1);
          } catch (com.lowagie.text.BadElementException BadElExec) {

            javax.swing.JOptionPane.showMessageDialog(
                new javax.swing.JFrame(), BadElExec.getMessage());
          }

          //  } catch(java.io.FileNotFoundException fnfExec) {

          //  javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(),
          // fnfExec.getMessage());

          /// }
          try {

            java.util.Calendar calendar = java.util.Calendar.getInstance();

            long dateNow = calendar.getTimeInMillis();

            java.sql.Date datenowSql = new java.sql.Date(dateNow);

            System.out.println(datenowSql.toString());

            java.lang.Object listofStaffNos[] = this.getListofStaffNos();

            com.lowagie.text.pdf.PdfPTable table = new com.lowagie.text.pdf.PdfPTable(9);
            //  com.lowagie.text.Table table = new com.lowagie.text.Table(7);

            // table.endHeaders();

            int headerwidths[] = {12, 10, 28, 11, 14, 12, 14, 14, 15};

            table.setWidths(headerwidths);
            //  if (docPdf.getPageNumber() > 1) {
            table.setHeaderRows(8);
            //  }
            table.setWidthPercentage((100));

            table.getDefaultCell().setBorder(Rectangle.BOTTOM);

            table.getDefaultCell().setColspan(9);

            Phrase phrase = new Phrase();

            //  table.addCell(phrase);

            table.getDefaultCell().setColspan(1);
            table.getDefaultCell().setBackgroundColor(java.awt.Color.WHITE);
            table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);

            try {
              double Debit = 0.00;
              double Credit = 0.00;
              java.sql.Statement st22 = connectDB.createStatement();
              java.sql.Statement st11 = connectDB.createStatement();
              java.sql.Statement st = connectDB.createStatement();
              java.sql.Statement st1 = connectDB.createStatement();
              java.sql.Statement st2 = connectDB.createStatement();
              java.sql.Statement st3 = connectDB.createStatement();
              java.sql.Statement st4 = connectDB.createStatement();
              java.sql.Statement st41 = connectDB.createStatement();
              java.sql.Statement st5 = connectDB.createStatement();
              java.sql.Statement st22D = connectDB.createStatement();
              java.sql.ResultSet rset3 =
                  st3.executeQuery(
                      "select hospital_name,postal_code||' '||box_no||' '||town,main_telno||' '||other_telno,initcap(street),main_faxno,email,website,room_no from pb_hospitalprofile");
              java.sql.ResultSet rset4 =
                  st4.executeQuery(
                      "select scheme_name,account_no,tel_main,address from ac_schemes where account_no = '"
                          + memNo
                          + "'");
              java.sql.ResultSet rset41 =
                  st41.executeQuery(
                      "select payer_name from ac_schemes where account_no = '" + memNo + "'");
              //      java.sql.ResultSet rset1 = st1.executeQuery("select date,admission_no||'
              // '||cheque_no, upper(item),invoice_no ||'
              // '||receipt_no,debit,credit,credit_bal,balance from ac_debtors where account_no = '"
              // + memNo + "' AND date::date BETWEEN '" + beginDate + "' AND '" + endDate + "' group
              // by 1, 2, 3order by date,invoice_no");// UNION select
              // pd.date::date,initcap(pd.scheme_staff_no), (sh.first_name||' '||sh.second_name||'
              // '||sh.last_name) as name,pd.reference,sum(pd.credit),pd.patient_no from
              // hp_patient_card pd,hp_inpatient_register sh where pd.patient_no = sh.patient_no and
              // pd.isurer = '"+memNo+"' AND pd.date::date BETWEEN '"+beginDate+"' AND '"+endDate+"'
              // and pd.credit > 0 group by pd.date::date,pd.scheme_staff_no,
              // name,pd.reference,pd.invoice_no,pd.patient_no order by pd.invoice_no");
              java.sql.ResultSet rsetTotals2 =
                  st22.executeQuery(
                      "select sum(debit - credit) from ac_debtors where account_no = '"
                          + memNo
                          + "' AND date::date < '"
                          + beginDate
                          + "'");
              java.sql.ResultSet rsetTotals =
                  st2.executeQuery(
                      "select sum(debit),sum(credit) from ac_debtors where account_no = '"
                          + memNo
                          + "' AND date::date BETWEEN '"
                          + beginDate
                          + "' AND '"
                          + endDate
                          + "'");

              table.getDefaultCell().setBorderColor(java.awt.Color.BLACK);
              table.getDefaultCell().setBorderWidth(Rectangle.TOP | Rectangle.BOTTOM);

              table.getDefaultCell().setColspan(9);
              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_CENTER);
              phrase = new Phrase("Statement of Account", pFontHeader11);
              table.addCell(phrase);

              while (rset4.next()) {
                table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);
                table.getDefaultCell().setColspan(9);

                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
                phrase = new Phrase(rset4.getObject(1).toString(), pFontHeader1);
                table.addCell(phrase);

                while (rset41.next()) {
                  table.getDefaultCell().setColspan(9);

                  table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
                  phrase = new Phrase(rset41.getString(1), pFontHeader1);
                  table.addCell(phrase);
                }
                table.getDefaultCell().setColspan(9);

                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
                phrase =
                    new Phrase(
                        "Account No : " + rset4.getObject(2).toString().toUpperCase(),
                        pFontHeader1);
                table.addCell(phrase);

                phrase =
                    new Phrase(
                        dbObject.getDBObject("Tel : " + rset4.getObject(3), "-"), pFontHeader1);
                table.addCell(phrase);
                phrase = new Phrase(dbObject.getDBObject(rset4.getObject(4), "-"), pFontHeader1);
                table.addCell(phrase);
                table.getDefaultCell().setColspan(6);
              }
              try {
                java.text.DateFormat dateFormat =
                    java.text.DateFormat.getDateInstance(java.text.DateFormat.MEDIUM); // MEDIUM);

                java.util.Date endDate1 =
                    dateFormat.parse(endDate.toLocaleString()); // dateInstance.toLocaleString());
                java.util.Date endDate11 =
                    dateFormat.parse(beginDate.toLocaleString()); // dateInstance.toLocaleString());

                System.out.println("" + endDate1);
                //  phrase = new Phrase(bank +" Report: " +dateFormat.format(formattedDate),
                // pFontHeader);

                //  table.addCell(phrase);

                phrase =
                    new Phrase(
                        "Period : "
                            + dateFormat.format(endDate11)
                            + " - "
                            + dateFormat.format(endDate1),
                        pFontHeader1);

                table.addCell(phrase);
              } catch (java.text.ParseException psExec) {

                javax.swing.JOptionPane.showMessageDialog(
                    new javax.swing.JFrame(), psExec.getMessage());
              }
              // table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
              // phrase = new Phrase("Period : '"+beginDate+"' - '"+endDate+"'", pFontHeader1);
              // table.addCell(phrase);

              table.getDefaultCell().setColspan(3);

              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
              phrase = new Phrase("Date " + datenowSql, pFontHeader1);
              table.addCell(phrase);

              table.getDefaultCell().setColspan(3);

              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
              table.getDefaultCell().setBorderColor(java.awt.Color.BLACK);
              table.getDefaultCell().setBorderWidth(Rectangle.TOP);
              table.getDefaultCell().setColspan(1);
              // phrase = new Phrase("Date", pFontHeader1);
              // table.addCell(phrase);
              table.getDefaultCell().setColspan(1);
              phrase = new Phrase("Staff No.", pFontHeader1);
              table.addCell(phrase);
              table.getDefaultCell().setColspan(2);
              phrase = new Phrase(" Staff Name", pFontHeader1);
              table.addCell(phrase);

              table.getDefaultCell().setColspan(1);
              phrase = new Phrase("", pFontHeader1);
              table.addCell(phrase);
              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);

              table.getDefaultCell().setColspan(1);
              phrase = new Phrase("", pFontHeader1);
              table.addCell(phrase);
              table.getDefaultCell().setColspan(1);
              phrase = new Phrase("", pFontHeader1);
              table.addCell(phrase);
              phrase = new Phrase("", pFontHeader1);
              table.addCell(phrase);
              phrase = new Phrase("Debit Bal. ", pFontHeader1);
              table.addCell(phrase);

              table.getDefaultCell().setColspan(1);
              phrase = new Phrase("Running Amt. " + ks, pFontHeader1);
              table.addCell(phrase);

              while (rsetTotals2.next()) {
                table.getDefaultCell().setColspan(5);

                table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);

                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
                phrase = new Phrase("BAL/BF", pFontHeader1);
                table.addCell(phrase);
                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
                table.getDefaultCell().setColspan(4);
                phrase =
                    new Phrase(
                        new com.afrisoftech.sys.Format2Currency()
                            .Format2Currency(rsetTotals2.getString(1)),
                        pFontHeader1);
                osBalancebf = osBalancebf + rsetTotals2.getDouble(1);
                table.addCell(phrase);
              }
              for (int i = 0; i < listofStaffNos.length; i++) {
                java.sql.ResultSet rset1 =
                    st1.executeQuery(
                        "select admission_no, upper(item), sum(debit - credit) from ac_debtors where account_no = '"
                            + memNo
                            + "' and admission_no = '"
                            + listofStaffNos[i]
                            + "' AND date::date BETWEEN '"
                            + beginDate
                            + "' AND '"
                            + endDate
                            + "' GROUP BY 1, 2");
                while (rset1.next()) {
                  table.getDefaultCell().setColspan(1);
                  table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);

                  table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
                  phrase = new Phrase(dbObject.getDBObject(rset1.getObject(1), "-"), pFontHeader);

                  table.addCell(phrase);
                  table.getDefaultCell().setColspan(6);

                  table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
                  phrase = new Phrase(dbObject.getDBObject(rset1.getObject(2), "-"), pFontHeader);

                  table.addCell(phrase);
                  table.getDefaultCell().setColspan(1);
                  table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
                  // phrase = new Phrase(dbObject.getDBObject(rset1.getObject(3), "-"),
                  // pFontHeader);

                  // table.addCell(phrase);

                  table.getDefaultCell().setColspan(1);
                  table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
                  // phrase = new Phrase(dbObject.getDBObject(rset1.getObject(4), "-"),
                  // pFontHeader);

                  // table.addCell(phrase);

                  table.getDefaultCell().setColspan(1);
                  table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
                  // phrase = new Phrase(new
                  // com.afrisoftech.sys.Format2Currency().Format2Currency(rset1.getString(5)),
                  // pFontHeader);
                  // Debit = Debit + rset1.getDouble(5);
                  // table.addCell(phrase);
                  // phrase = new Phrase(new
                  // com.afrisoftech.sys.Format2Currency().Format2Currency(rset1.getString(6)),
                  // pFontHeader);
                  // Credit = Credit + rset1.getDouble(6);
                  // table.addCell(phrase);

                  // phrase = new Phrase(new
                  // com.afrisoftech.sys.Format2Currency().Format2Currency(rset1.getString(7)),
                  // pFontHeader);
                  // osBalance1 = osBalance1 + rset1.getDouble(6);
                  // table.addCell(phrase);
                  phrase =
                      new Phrase(
                          new com.afrisoftech.sys.Format2Currency()
                              .Format2Currency(rset1.getString(3)),
                          pFontHeader);
                  osBalancebf = osBalancebf + rset1.getDouble(3);
                  // osBalancebf = osBalance1;
                  table.addCell(phrase);
                  phrase =
                      new Phrase(
                          new com.afrisoftech.sys.Format2Currency()
                              .Format2Currency(java.lang.String.valueOf(osBalancebf - osBalance1)),
                          pFontHeader);

                  table.addCell(phrase);
                }
              }
              table.getDefaultCell().setBorderColor(java.awt.Color.BLACK);

              table.getDefaultCell().setBorder(Rectangle.BOTTOM | Rectangle.TOP);

              //  while (rsetTotals.next()) {

              table.getDefaultCell().setColspan(4);

              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
              phrase = new Phrase("Total", pFontHeader);

              table.addCell(phrase);

              table.getDefaultCell().setColspan(1);

              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
              phrase = new Phrase("", pFontHeader);

              table.addCell(phrase);
              phrase = new Phrase("", pFontHeader);

              table.addCell(phrase);
              phrase = new Phrase("", pFontHeader);

              table.addCell(phrase);
              table.getDefaultCell().setColspan(2);
              phrase =
                  new Phrase(
                      new com.afrisoftech.sys.Format2Currency()
                          .Format2Currency(java.lang.String.valueOf(osBalancebf - osBalance1)),
                      pFontHeader);

              table.addCell(phrase);

              // phrase = new Phrase(" ");

              //     }
              try {

                java.sql.Statement st31 = connectDB.createStatement();
                // java.sql.Statement st4 = connectDB.createStatement();
                java.sql.ResultSet rset2 = st31.executeQuery("select name from pb_notice");

                // java.sql.ResultSet rset2 = st3.executeQuery("SELECT hospital_name from
                // pb_hospitalprofile");
                //   java.sql.ResultSet rset4 = st4.executeQuery("SELECT date('now') as Date");
                while (rset2.next()) {
                  Messg = rset2.getString(1);
                }
                com.lowagie.text.HeaderFooter footer =
                    new com.lowagie.text.HeaderFooter(
                        new Phrase("" + Messg + ""),
                        false); // FontFactory.getFont(com.lowagie.text.FontFactory.HELVETICA, 14,
                // Font.BOLDITALIC,java.awt.Color.blue)));

                //  com.lowagie.text.HeaderFooter headerFoter = new
                // com.lowagie.text.HeaderFooter(new Phrase(""+compName+""),false);//
                // FontFactory.getFont(com.lowagie.text.FontFactory.HELVETICA, 14,
                // Font.BOLDITALIC,java.awt.Color.blue)));
                //  headerFoter.ALIGN_CENTER;
                //  headerFoter.setRight(5);
                docPdf.setFooter(footer);

              } catch (java.sql.SQLException SqlExec) {

                SqlExec.printStackTrace();
                javax.swing.JOptionPane.showMessageDialog(
                    new javax.swing.JFrame(), SqlExec.getMessage());
              }

              docPdf.add(table);

            } catch (java.sql.SQLException SqlExec) {

              SqlExec.printStackTrace();
              javax.swing.JOptionPane.showMessageDialog(
                  new javax.swing.JFrame(), SqlExec.getMessage());
            }

            // }  // }

          } catch (com.lowagie.text.BadElementException BadElExec) {

            javax.swing.JOptionPane.showMessageDialog(
                new javax.swing.JFrame(), BadElExec.getMessage());
          }

        } catch (java.io.FileNotFoundException fnfExec) {

          javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), fnfExec.getMessage());
        }
      } catch (com.lowagie.text.DocumentException lwDocexec) {

        javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), lwDocexec.getMessage());
      }

      try {

        if (System.getProperty("os.name").equalsIgnoreCase("Linux")) {

          System.out.println(tempFile);

          wait_for_Pdf2Show = rt.exec("xpdf " + tempFile + "");

          wait_for_Pdf2Show.waitFor();

        } else {

          wait_for_Pdf2Show =
              rt.exec("c:/Program Files/Adobe/Acrobat 5.0/Reader/AcroRd32.exe " + tempFile);

          wait_for_Pdf2Show.waitFor();
        }

      } catch (java.lang.InterruptedException intrExec) {

        javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), intrExec.getMessage());
      }

    } catch (java.io.IOException IOexec) {

      javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), IOexec.getMessage());
    }
  }
  public void generatePdf(java.lang.String memNo) {

    java.lang.Process wait_for_Pdf2Show;

    java.util.Calendar cal = java.util.Calendar.getInstance();

    java.util.Date dateStampPdf = cal.getTime();

    java.lang.String pdfDateStamp = dateStampPdf.toString();

    try {

      java.io.File tempFile =
          java.io.File.createTempFile("REP" + this.getDateLable() + "_", ".pdf");

      tempFile.deleteOnExit();

      java.lang.Runtime rt = java.lang.Runtime.getRuntime();

      java.lang.String debitTotal = null;

      java.lang.String creditTotal = null;

      com.lowagie.text.Document docPdf = new com.lowagie.text.Document();
      double osBalance1 = 0.00;
      double osBalance = 0.00;
      double current = 0.00;
      double osBalancebf = 0.00;
      try {

        try {

          com.lowagie.text.pdf.PdfWriter.getInstance(
              docPdf, new java.io.FileOutputStream(tempFile));

          String compName = null;
          String date = null;
          String Messg = null;

          com.lowagie.text.HeaderFooter footer =
              new com.lowagie.text.HeaderFooter(
                  new Phrase("Allocation - Page: ", pFontHeader),
                  true); // FontFactory.getFont(com.lowagie.text.FontFactory.HELVETICA, 12,
                         // Font.BOLDITALIC,java.awt.Color.blue));

          docPdf.setFooter(footer);

          docPdf.open();

          try {

            java.util.Calendar calendar = java.util.Calendar.getInstance();

            long dateNow = calendar.getTimeInMillis();

            java.sql.Date datenowSql = new java.sql.Date(dateNow);

            System.out.println(datenowSql.toString());

            java.lang.Object listofStaffNos[] = this.getListofStaffNos();

            com.lowagie.text.pdf.PdfPTable table1 = new com.lowagie.text.pdf.PdfPTable(6);
            //  com.lowagie.text.Table table = new com.lowagie.text.Table(7);

            // table.endHeaders();

            int headerwidths[] = {15, 15, 30, 15, 15, 15};

            table1.setWidths(headerwidths);
            //  if (docPdf.getPageNumber() > 1) {
            //  table1.setHeaderRows(4);
            //  }
            table1.setWidthPercentage((100));

            table1.getDefaultCell().setBorder(Rectangle.BOTTOM);

            table1.getDefaultCell().setColspan(7);

            Phrase phrase = new Phrase();

            //  table.addCell(phrase);

            table1.getDefaultCell().setColspan(1);
            table1.getDefaultCell().setBackgroundColor(java.awt.Color.WHITE);
            table1.getDefaultCell().setBorderColor(java.awt.Color.WHITE);

            try {
              java.sql.Statement st3 = connectDB.createStatement();
              java.sql.ResultSet rset3 = st3.executeQuery("select header_name from pb_header");
              while (rset3.next()) table1.getDefaultCell().setColspan(6);

              table1.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_CENTER);
              phrase = new Phrase(rset3.getObject(1).toString(), pFontHeader11);
              table1.addCell(phrase);

            } catch (java.sql.SQLException SqlExec) {

              javax.swing.JOptionPane.showMessageDialog(
                  new javax.swing.JFrame(), SqlExec.getMessage());
            }
            docPdf.add(table1);
          } catch (com.lowagie.text.BadElementException BadElExec) {

            javax.swing.JOptionPane.showMessageDialog(
                new javax.swing.JFrame(), BadElExec.getMessage());
          }

          //  } catch(java.io.FileNotFoundException fnfExec) {

          //  javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(),
          // fnfExec.getMessage());

          /// }
          try {

            java.util.Calendar calendar = java.util.Calendar.getInstance();

            long dateNow = calendar.getTimeInMillis();

            java.sql.Date datenowSql = new java.sql.Date(dateNow);

            System.out.println(datenowSql.toString());

            java.lang.Object listofStaffNos[] = this.getListofStaffNos();

            com.lowagie.text.pdf.PdfPTable table = new com.lowagie.text.pdf.PdfPTable(10);
            //  com.lowagie.text.Table table = new com.lowagie.text.Table(7);

            // table.endHeaders();

            int headerwidths[] = {12, 10, 23, 10, 12, 13, 8, 11, 10, 8};

            table.setWidths(headerwidths);
            //  if (docPdf.getPageNumber() > 1) {
            table.setHeaderRows(10);
            //  }
            table.setWidthPercentage((100));

            table.getDefaultCell().setBorder(Rectangle.BOTTOM);

            table.getDefaultCell().setColspan(10);

            Phrase phrase = new Phrase();

            //  table.addCell(phrase);

            table.getDefaultCell().setColspan(1);
            table.getDefaultCell().setBackgroundColor(java.awt.Color.WHITE);
            table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);

            try {
              java.sql.Statement st22 = connectDB.createStatement();
              java.sql.Statement st11 = connectDB.createStatement();
              java.sql.Statement st = connectDB.createStatement();
              java.sql.Statement st1 = connectDB.createStatement();
              java.sql.Statement st2 = connectDB.createStatement();
              java.sql.Statement st3 = connectDB.createStatement();
              java.sql.Statement st4 = connectDB.createStatement();
              java.sql.Statement st41 = connectDB.createStatement();
              java.sql.Statement st5 = connectDB.createStatement();
              // java.sql.ResultSet rset3 = st3.executeQuery("select hospital_name,postal_code||'
              // '||box_no||' '||town,main_telno||'
              // '||other_telno,initcap(street),main_faxno,email,website,room_no from
              // pb_hospitalprofile");
              //  java.sql.ResultSet rset4 = st4.executeQuery("select scheme_manager from
              // ac_scheme_providers where scheme_manager = '"+memNo+"'");
              java.sql.ResultSet rset4 =
                  st4.executeQuery(
                      "select scheme_name,account_no from ac_schemes where account_no = '"
                          + memNo
                          + "'");
              java.sql.ResultSet rset41 =
                  st41.executeQuery(
                      "select payer_name from ac_schemes where account_no = '" + memNo + "'");
              //   java.sql.ResultSet rset1 = st1.executeQuery("select ac.date,pc.scheme_staff_no,
              // ac.payee,ac.reference_no,sum(ac.debit),ac.admission_no from hp_patient_card
              // pc,ac_debtors ac where pc.patient_no = ac.admission_no and ac.dealer = '"+memNo+"'
              // and ac.dealer = pc.scheme AND ac.date::date BETWEEN '"+beginDate+"' AND
              // '"+endDate+"' and ac.debit > 0 group by ac.date,pc.scheme_staff_no,
              // ac.payee,ac.reference_no,ac.admission_no");// UNION select
              // pd.date::date,initcap(pd.scheme_staff_no), (sh.first_name||' '||sh.second_name||'
              // '||sh.last_name) as name,pd.reference,sum(pd.credit),pd.patient_no from
              // hp_patient_card pd,hp_inpatient_register sh where pd.patient_no = sh.patient_no and
              // pd.isurer = '"+memNo+"' AND pd.date::date BETWEEN '"+beginDate+"' AND '"+endDate+"'
              // and pd.credit > 0 group by pd.date::date,pd.scheme_staff_no,
              // name,pd.reference,pd.invoice_no,pd.patient_no order by pd.invoice_no");
              java.sql.ResultSet rset1 =
                  st1.executeQuery(
                      "select al.date,db.admission_no,db.item,al.inv_no,al.inv_date,db.debit, al.cheque_no,al.amount,al.amount_alloc,(db.debit-al.amount_alloc) from db_allocation al,ac_debtors db where al.acc_no = '"
                          + memNo
                          + "' and al.inv_no = db.invoice_no AND al.date::date BETWEEN '"
                          + beginDate
                          + "' AND '"
                          + endDate
                          + "' order by al.date"); // UNION select
                                                   // pd.date::date,initcap(pd.scheme_staff_no),
                                                   // (sh.first_name||' '||sh.second_name||'
                                                   // '||sh.last_name) as
                                                   // name,pd.reference,sum(pd.credit),pd.patient_no
                                                   // from hp_patient_card pd,hp_inpatient_register
                                                   // sh where pd.patient_no = sh.patient_no and
                                                   // pd.isurer = '"+memNo+"' AND pd.date::date
                                                   // BETWEEN '"+beginDate+"' AND '"+endDate+"' and
                                                   // pd.credit > 0 group by
                                                   // pd.date::date,pd.scheme_staff_no,
                                                   // name,pd.reference,pd.invoice_no,pd.patient_no
                                                   // order by pd.invoice_no");
              // java.sql.ResultSet rsetTotals2 = st22.executeQuery("select sum(debit),sum(balance)
              // from ac_debtors where account_no = '"+memNo+"' AND date::date < '"+beginDate+"'");

              //  java.sql.ResultSet rset1 = st1.executeQuery("select
              // pc.date::date,pc.scheme_staff_no, initcap(sh.first_name||' '||sh.second_name||'
              // '||sh.last_name) as name,pc.reference,sum(pc.credit),pc.patient_no from
              // hp_patient_card pc,hp_patient_register sh where pc.scheme_staff_no = sh.account_no
              // and pc.isurer = '"+memNo+"' AND pc.date::date BETWEEN '"+beginDate+"' AND
              // '"+endDate+"' and pc.credit > 0 group by pc.date::date,pc.scheme_staff_no,
              // name,pc.reference,pc.invoice_no,pc.patient_no");// UNION select
              // pd.date::date,initcap(pd.scheme_staff_no), (sh.first_name||' '||sh.second_name||'
              // '||sh.last_name) as name,pd.reference,sum(pd.credit),pd.patient_no from
              // hp_patient_card pd,hp_inpatient_register sh where pd.patient_no = sh.patient_no and
              // pd.isurer = '"+memNo+"' AND pd.date::date BETWEEN '"+beginDate+"' AND '"+endDate+"'
              // and pd.credit > 0 group by pd.date::date,pd.scheme_staff_no,
              // name,pd.reference,pd.invoice_no,pd.patient_no order by pd.invoice_no");
              //  java.sql.ResultSet rsetTotals = st2.executeQuery("select
              // sum(amount),sum(amount_alloc) from db_allocation where acc_no = '"+memNo+"' AND
              // date::date BETWEEN '"+beginDate+"' AND '"+endDate+"' and acc_no = '"+memNo+"'");

              table.getDefaultCell().setBorderColor(java.awt.Color.BLACK);
              table.getDefaultCell().setBorderWidth(Rectangle.TOP | Rectangle.BOTTOM);

              table.getDefaultCell().setColspan(10);
              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_CENTER);
              phrase = new Phrase("Payment Allocation Report", pFontHeader11);
              table.addCell(phrase);

              while (rset4.next()) table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);
              table.getDefaultCell().setColspan(10);

              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
              phrase = new Phrase(rset4.getObject(1).toString(), pFontHeader1);
              table.addCell(phrase);
              table.getDefaultCell().setColspan(10);

              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
              phrase = new Phrase("Account No : " + rset4.getObject(2).toString(), pFontHeader1);
              table.addCell(phrase);
              while (rset41.next()) table.getDefaultCell().setColspan(10);

              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
              phrase = new Phrase(rset41.getString(1), pFontHeader1);
              table.addCell(phrase);

              //  while (rset4.next())

              table.getDefaultCell().setColspan(5);

              try {
                java.text.DateFormat dateFormat =
                    java.text.DateFormat.getDateInstance(java.text.DateFormat.MEDIUM); // MEDIUM);

                java.util.Date endDate1 =
                    dateFormat.parse(endDate.toLocaleString()); // dateInstance.toLocaleString());
                java.util.Date endDate11 =
                    dateFormat.parse(beginDate.toLocaleString()); // dateInstance.toLocaleString());

                System.out.println("" + endDate1);
                //  phrase = new Phrase(bank +" Report: " +dateFormat.format(formattedDate),
                // pFontHeader);

                //  table.addCell(phrase);

                table.getDefaultCell().setColspan(6);
                phrase =
                    new Phrase(
                        "Period : "
                            + dateFormat.format(endDate11)
                            + " - "
                            + dateFormat.format(endDate1),
                        pFontHeader1);

                table.addCell(phrase);
              } catch (java.text.ParseException psExec) {

                javax.swing.JOptionPane.showMessageDialog(
                    new javax.swing.JFrame(), psExec.getMessage());
              }
              // table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
              // phrase = new Phrase("Period : '"+beginDate+"' - '"+endDate+"'", pFontHeader1);
              // table.addCell(phrase);

              table.getDefaultCell().setColspan(4);

              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
              phrase = new Phrase(" Date Printed " + datenowSql, pFontHeader1);
              table.addCell(phrase);
              table.getDefaultCell().setBorderColor(java.awt.Color.BLACK);

              table.getDefaultCell().setBorder(Rectangle.BOTTOM | Rectangle.TOP);

              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
              //  table.getDefaultCell().setBorderColor(java.awt.Color.BLACK);

              table.getDefaultCell().setColspan(1);
              phrase = new Phrase("Date", pFontHeader1);
              table.addCell(phrase);
              table.getDefaultCell().setColspan(1);
              phrase = new Phrase("Patient No.", pFontHeader1);
              table.addCell(phrase);
              phrase = new Phrase("Patient Name", pFontHeader1);
              table.addCell(phrase);
              table.getDefaultCell().setColspan(1);
              phrase = new Phrase("Inv. No.", pFontHeader1);
              table.addCell(phrase);
              phrase = new Phrase("Inv.Date", pFontHeader1);
              table.addCell(phrase);
              phrase = new Phrase("Inv. Amt.", pFontHeader1);
              table.addCell(phrase);

              phrase = new Phrase("Chq No. ", pFontHeader1);
              table.addCell(phrase);
              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);

              table.getDefaultCell().setColspan(1);

              table.getDefaultCell().setColspan(1);
              phrase = new Phrase("Chq. Amt. ", pFontHeader1);
              table.addCell(phrase);
              phrase = new Phrase("Amt.Alloc ", pFontHeader1);
              table.addCell(phrase);
              phrase = new Phrase("Inv. Bal. ", pFontHeader1);
              table.addCell(phrase);
              table.getDefaultCell().setBorderWidth(Rectangle.BOTTOM);
              // table

              while (rset1.next()) {
                table.getDefaultCell().setColspan(1);
                table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);

                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
                phrase = new Phrase(dbObject.getDBObject(rset1.getObject(1), "-"), pFontHeader);

                table.addCell(phrase);
                table.getDefaultCell().setColspan(1);

                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
                phrase = new Phrase(dbObject.getDBObject(rset1.getObject(2), "-"), pFontHeader);

                table.addCell(phrase);
                table.getDefaultCell().setColspan(1);
                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
                phrase = new Phrase(dbObject.getDBObject(rset1.getObject(3), "-"), pFontHeader);

                table.addCell(phrase);

                table.getDefaultCell().setColspan(1);
                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
                phrase = new Phrase(dbObject.getDBObject(rset1.getObject(4), "-"), pFontHeader);

                table.addCell(phrase);
                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
                phrase = new Phrase(dbObject.getDBObject(rset1.getObject(5), "-"), pFontHeader);

                table.addCell(phrase);
                phrase =
                    new Phrase(
                        new com.afrisoftech.sys.Format2Currency()
                            .Format2Currency(rset1.getString(6)),
                        pFontHeader);
                // osBalance1 = osBalance1 + rset1.getDouble(5);
                table.addCell(phrase);
                table.getDefaultCell().setColspan(1);
                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
                phrase = new Phrase(dbObject.getDBObject(rset1.getObject(7), "-"), pFontHeader);

                table.addCell(phrase);

                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);

                phrase =
                    new Phrase(
                        new com.afrisoftech.sys.Format2Currency()
                            .Format2Currency(rset1.getString(8)),
                        pFontHeader);
                // osBalance1 = osBalance1 + rset1.getDouble(5);
                table.addCell(phrase);
                phrase =
                    new Phrase(
                        new com.afrisoftech.sys.Format2Currency()
                            .Format2Currency(rset1.getString(9)),
                        pFontHeader);
                osBalancebf = osBalancebf + rset1.getDouble(9);
                // osBalancebf = osBalance1;
                table.addCell(phrase);
                phrase =
                    new Phrase(
                        new com.afrisoftech.sys.Format2Currency()
                            .Format2Currency(rset1.getString(10)),
                        pFontHeader);
                osBalance1 = osBalance1 + rset1.getDouble(10);
                table.addCell(phrase);
              }
              table.getDefaultCell().setColspan(3);

              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
              phrase = new Phrase("", pFontHeader);

              table.addCell(phrase);
              table.getDefaultCell().setBorderColor(java.awt.Color.BLACK);

              table.getDefaultCell().setBorder(Rectangle.BOTTOM | Rectangle.TOP);

              //  while (rsetTotals.next()) {

              table.getDefaultCell().setColspan(5);

              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
              phrase = new Phrase("Total", pFontHeader);

              table.addCell(phrase);

              table.getDefaultCell().setColspan(1);

              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);

              phrase =
                  new Phrase(
                      new com.afrisoftech.sys.Format2Currency()
                          .Format2Currency(java.lang.String.valueOf(osBalancebf)),
                      pFontHeader);

              table.addCell(phrase);
              phrase =
                  new Phrase(
                      new com.afrisoftech.sys.Format2Currency()
                          .Format2Currency(java.lang.String.valueOf(osBalance1)),
                      pFontHeader);

              table.addCell(phrase);
              // phrase = new Phrase(" ");

              //     }
              /*   try {


                    java.sql.Statement st31 = connectDB.createStatement();
                    // java.sql.Statement st4 = connectDB.createStatement();
                    java.sql.ResultSet rset2 = st31.executeQuery("select name from pb_notice");

                    // java.sql.ResultSet rset2 = st3.executeQuery("SELECT hospital_name from pb_hospitalprofile");
                    //   java.sql.ResultSet rset4 = st4.executeQuery("SELECT date('now') as Date");
                    while(rset2.next())
                        Messg = rset2.getString(1);

                    com.lowagie.text.HeaderFooter footer = new com.lowagie.text.HeaderFooter(new Phrase(""+Messg+""),false);// FontFactory.getFont(com.lowagie.text.FontFactory.HELVETICA, 14, Font.BOLDITALIC,java.awt.Color.blue)));

                    //  com.lowagie.text.HeaderFooter headerFoter = new com.lowagie.text.HeaderFooter(new Phrase(""+compName+""),false);// FontFactory.getFont(com.lowagie.text.FontFactory.HELVETICA, 14, Font.BOLDITALIC,java.awt.Color.blue)));
                    //  headerFoter.ALIGN_CENTER;
                    //  headerFoter.setRight(5);
                    docPdf.setFooter(footer);




                } catch(java.sql.SQLException SqlExec) {

                    javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), SqlExec.getMessage());

                }

              */
              docPdf.add(table);

            } catch (java.sql.SQLException SqlExec) {

              javax.swing.JOptionPane.showMessageDialog(
                  new javax.swing.JFrame(), SqlExec.getMessage());
            }

            // }  // }

          } catch (com.lowagie.text.BadElementException BadElExec) {

            javax.swing.JOptionPane.showMessageDialog(
                new javax.swing.JFrame(), BadElExec.getMessage());
          }

        } catch (java.io.FileNotFoundException fnfExec) {

          javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), fnfExec.getMessage());
        }
      } catch (com.lowagie.text.DocumentException lwDocexec) {

        javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), lwDocexec.getMessage());
      }

      docPdf.close();

      try {

        if (System.getProperty("os.name").equalsIgnoreCase("Linux")) {

          System.out.println(tempFile);

          wait_for_Pdf2Show = rt.exec("kghostview " + tempFile + "");

          wait_for_Pdf2Show.waitFor();

        } else {

          wait_for_Pdf2Show =
              rt.exec("c:/Program Files/Adobe/Acrobat 5.0/Reader/AcroRd32.exe " + tempFile);

          wait_for_Pdf2Show.waitFor();
        }

      } catch (java.lang.InterruptedException intrExec) {

        javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), intrExec.getMessage());
      }

    } catch (java.io.IOException IOexec) {

      javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), IOexec.getMessage());
    }
  }
  public void generatePdf(java.lang.String memNo) {

    java.lang.Process wait_for_Pdf2Show;

    java.util.Calendar cal = java.util.Calendar.getInstance();

    java.util.Date dateStampPdf = cal.getTime();

    java.lang.String pdfDateStamp = dateStampPdf.toString();

    try {

      java.io.File tempFile =
          java.io.File.createTempFile("REP" + this.getDateLable() + "_", ".pdf");

      tempFile.deleteOnExit();

      java.lang.Runtime rt = java.lang.Runtime.getRuntime();

      java.lang.String debitTotal = null;

      java.lang.String creditTotal = null;

      com.lowagie.text.Document docPdf = new com.lowagie.text.Document();
      double osBalanceAmt = 0.00;
      double osBalanceQty = 0.00;
      double osBalanceQtybf = 0.00;
      double osBalanceAmtbf = 0.00;
      double ClosingQtyBalance = 0.00;
      double ClosingAmtBalance = 0.00;
      double osBalanceQty1 = 0.00;
      double osBalanceQty13 = 0.00;
      double osBalanceQty14 = 0.00;
      double osBalanceAmt1 = 0.00;
      double osBalanceAmt12 = 0.00;
      double current = 0.00;

      try {

        try {

          com.lowagie.text.pdf.PdfWriter.getInstance(
              docPdf, new java.io.FileOutputStream(tempFile));

          String compName = null;
          String date = null;
          String Messg = null;

          /*  try {


                  java.sql.Statement st3 = connectDB.createStatement();
                  java.sql.Statement st4 = connectDB.createStatement();
                    // java.sql.ResultSet rset2 = st3.executeQuery("select name from pb_notice");

                  java.sql.ResultSet rset2 = st3.executeQuery("SELECT hospital_name from pb_hospitalprofile");
               //   java.sql.ResultSet rset4 = st4.executeQuery("SELECT date('now') as Date");
                  while(rset2.next())
                      Messg = rset2.getObject(1).toString();



                //  while(rset4.next())
                  //    date = rset4.getObject(1).toString();

                 // com.lowagie.textHeaderFooter footer = new com.lowagie.text.HeaderFooter(new Phrase(""+Messg+""),false);// FontFactory.getFont(com.lowagie.text.FontFactory.HELVETICA, 14, Font.BOLDITALIC,java.awt.Color.blue)));

                    headerFoter = new com.lowagie.text.HeaderFooter(new Phrase(""+Messg+""),false);// FontFactory.getFont(com.lowagie.text.FontFactory.HELVETICA, 14, Font.BOLDITALIC,java.awt.Color.blue)));
                //  headerFoter.ALIGN_CENTER;
                   headerFoter.setRight(5);


              } catch(java.sql.SQLException SqlExec) {

                  javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), SqlExec.getMessage());

              }

          */
          /*
           try {


                java.sql.Statement st3 = connectDB.createStatement();
                java.sql.Statement st4 = connectDB.createStatement();
                   java.sql.ResultSet rset2 = st3.executeQuery("select name from pb_notice");

               // java.sql.ResultSet rset2 = st3.executeQuery("SELECT hospital_name from pb_hospitalprofile");
             //   java.sql.ResultSet rset4 = st4.executeQuery("SELECT date('now') as Date");
                while(rset2.next())
                    Messg = rset2.getObject(1).toString();



              //  while(rset4.next())
                //    date = rset4.getObject(1).toString();

                com.lowagie.text.HeaderFooter footer = new com.lowagie.text.HeaderFooter(new Phrase(""+Messg+""),false);// FontFactory.getFont(com.lowagie.text.FontFactory.HELVETICA, 14, Font.BOLDITALIC,java.awt.Color.blue)));

                //  com.lowagie.text.HeaderFooter headerFoter = new com.lowagie.text.HeaderFooter(new Phrase(""+compName+""),false);// FontFactory.getFont(com.lowagie.text.FontFactory.HELVETICA, 14, Font.BOLDITALIC,java.awt.Color.blue)));
              //  headerFoter.ALIGN_CENTER;
               //  headerFoter.setRight(5);
                docPdf.setFooter(footer);




            } catch(java.sql.SQLException SqlExec) {

                javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), SqlExec.getMessage());

            }
          */
          docPdf.open();

          try {

            java.util.Calendar calendar = java.util.Calendar.getInstance();

            long dateNow = calendar.getTimeInMillis();

            java.sql.Date datenowSql = new java.sql.Date(dateNow);

            System.out.println(datenowSql.toString());

            java.lang.Object listofStaffNos[] = this.getListofStaffNos();

            com.lowagie.text.pdf.PdfPTable table1 = new com.lowagie.text.pdf.PdfPTable(7);
            //  com.lowagie.text.Table table = new com.lowagie.text.Table(7);

            // table.endHeaders();

            int headerwidths[] = {15, 15, 30, 15, 15, 15, 15};

            table1.setWidths(headerwidths);
            //  if (docPdf.getPageNumber() > 1) {
            //  table1.setHeaderRows(4);
            //  }
            table1.setWidthPercentage((100));

            table1.getDefaultCell().setBorder(Rectangle.BOTTOM);

            table1.getDefaultCell().setColspan(7);

            Phrase phrase = new Phrase();

            //  table.addCell(phrase);

            table1.getDefaultCell().setColspan(1);
            table1.getDefaultCell().setBackgroundColor(java.awt.Color.WHITE);
            table1.getDefaultCell().setBorderColor(java.awt.Color.WHITE);

            try {
              java.sql.Statement st3 = connectDB.createStatement();
              java.sql.ResultSet rset3 = st3.executeQuery("select header_name from pb_header");
              while (rset3.next()) table1.getDefaultCell().setColspan(7);

              table1.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_CENTER);
              phrase = new Phrase(rset3.getObject(1).toString(), pFontHeader11);
              table1.addCell(phrase);

            } catch (java.sql.SQLException SqlExec) {

              javax.swing.JOptionPane.showMessageDialog(
                  new javax.swing.JFrame(), SqlExec.getMessage());
            }
            docPdf.add(table1);
          } catch (com.lowagie.text.BadElementException BadElExec) {

            javax.swing.JOptionPane.showMessageDialog(
                new javax.swing.JFrame(), BadElExec.getMessage());
          }

          //  } catch(java.io.FileNotFoundException fnfExec) {

          //  javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(),
          // fnfExec.getMessage());

          /// }
          try {

            java.util.Calendar calendar = java.util.Calendar.getInstance();

            long dateNow = calendar.getTimeInMillis();

            java.sql.Date datenowSql = new java.sql.Date(dateNow);

            System.out.println(datenowSql.toString());

            java.lang.Object listofStaffNos[] = this.getListofStaffNos();

            com.lowagie.text.pdf.PdfPTable table = new com.lowagie.text.pdf.PdfPTable(5);
            //  com.lowagie.text.Table table = new com.lowagie.text.Table(7);

            // table.endHeaders();

            int headerwidths[] = {10, 40, 15, 15, 20};

            table.setWidths(headerwidths);
            //  if (docPdf.getPageNumber() > 1) {
            table.setHeaderRows(3);
            //  }
            table.setWidthPercentage((100));

            table.getDefaultCell().setBorder(Rectangle.BOTTOM);

            table.getDefaultCell().setColspan(5);

            Phrase phrase = new Phrase();

            //  table.addCell(phrase);

            table.getDefaultCell().setColspan(1);
            table.getDefaultCell().setBackgroundColor(java.awt.Color.WHITE);
            table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);

            try {
              java.sql.Statement st22 = connectDB.createStatement();
              java.sql.Statement st11 = connectDB.createStatement();
              java.sql.Statement st111 = connectDB.createStatement();
              java.sql.Statement st = connectDB.createStatement();
              java.sql.Statement st1 = connectDB.createStatement();
              java.sql.Statement st1q = connectDB.createStatement();
              java.sql.Statement st2 = connectDB.createStatement();
              java.sql.Statement st3 = connectDB.createStatement();
              java.sql.Statement st4 = connectDB.createStatement();
              java.sql.Statement st41 = connectDB.createStatement();
              java.sql.Statement st5 = connectDB.createStatement();
              java.sql.ResultSet rset3 =
                  st3.executeQuery(
                      "select hospital_name,postal_code||' '||box_no||' '||town,main_telno||' '||other_telno,initcap(street),main_faxno,email,website,room_no from pb_hospitalprofile");
              java.sql.ResultSet rset1 =
                  st1.executeQuery(
                      "select '-' as sno, item,sum(qty)  ,sum(purchases),sum(sales),case when sum(sales) > 0 then sum(sales)*-1 else sum(purchases) end as balance  from drug_movt where date BETWEEN '"
                          + beginDate
                          + "' AND '"
                          + endDate
                          + "' and qty > 0 group by item order by item"); // UNION ALL SELECT
              // trans_date,store_name,user_name,issuing,total from st_sub_stores where (sub_store =
              // 'Pharmacy' OR sub_store = 'IP Pharmacy') and item ilike '"+memNo+"' AND trans_date
              // BETWEEN '"+beginDate+"' AND '"+endDate+"'");
              java.sql.ResultSet rset11 =
                  st11.executeQuery(
                      "select sum(purchases),sum(sales) from drug_movt"); // UNION ALL SELECT
              // trans_date,store_name,user_name,issuing,total from st_sub_stores where (sub_store =
              // 'Pharmacy' OR sub_store = 'IP Pharmacy') and item ilike '"+memNo+"' AND trans_date
              // BETWEEN '"+beginDate+"' AND '"+endDate+"'");
              // java.sql.ResultSet rset111 = st111.executeQuery("select sum(issuing),sum(amount)
              // from st_sub_stores where item ilike '"+memNo1+"' and sub_store ilike '"+memNo+"%'
              // AND date BETWEEN '"+beginDate+"' AND '"+endDate+"'");// UNION ALL SELECT
              // trans_date,store_name,user_name,issuing,total from st_sub_stores where (sub_store =
              // 'Pharmacy' OR sub_store = 'IP Pharmacy') and item ilike '"+memNo+"' AND trans_date
              // BETWEEN '"+beginDate+"' AND '"+endDate+"'");
              table.getDefaultCell().setBorderColor(java.awt.Color.BLACK);
              table.getDefaultCell().setBorderWidth(Rectangle.TOP | Rectangle.BOTTOM);

              table.getDefaultCell().setColspan(5);
              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_CENTER);
              phrase = new Phrase(" ", pFontHeader11);
              table.addCell(phrase);

              table.getDefaultCell().setColspan(5);
              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_CENTER);
              phrase =
                  new Phrase(
                      "Drug Movement(Supplies Vs Sales) for all stockables items", pFontHeader11);
              table.addCell(phrase);

              table.getDefaultCell().setColspan(3);

              try {
                java.text.DateFormat dateFormat =
                    java.text.DateFormat.getDateInstance(java.text.DateFormat.MEDIUM); // MEDIUM);

                java.util.Date endDate1 =
                    dateFormat.parse(endDate.toLocaleString()); // dateInstance.toLocaleString());
                java.util.Date endDate11 =
                    dateFormat.parse(beginDate.toLocaleString()); // dateInstance.toLocaleString());

                System.out.println("" + endDate1);
                //  phrase = new Phrase(bank +" Report: " +dateFormat.format(formattedDate),
                // pFontHeader);

                //  table.addCell(phrase);

                phrase =
                    new Phrase(
                        "Period : "
                            + dateFormat.format(endDate11)
                            + " - "
                            + dateFormat.format(endDate1),
                        pFontHeader1);

                table.addCell(phrase);
              } catch (java.text.ParseException psExec) {

                javax.swing.JOptionPane.showMessageDialog(
                    new javax.swing.JFrame(), psExec.getMessage());
              }
              // table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
              // phrase = new Phrase("Period : '"+beginDate+"' - '"+endDate+"'", pFontHeader1);
              // table.addCell(phrase);

              table.getDefaultCell().setColspan(2);

              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
              phrase = new Phrase("Date " + datenowSql, pFontHeader1);
              table.addCell(phrase);

              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
              table.getDefaultCell().setBorderColor(java.awt.Color.BLACK);
              table.getDefaultCell().setBorderWidth(Rectangle.TOP);
              table.getDefaultCell().setColspan(1);
              phrase = new Phrase("SNo.", pFontHeader1);
              table.addCell(phrase);
              table.getDefaultCell().setColspan(1);
              phrase = new Phrase("Item", pFontHeader1);
              table.addCell(phrase);
              phrase = new Phrase("Qty", pFontHeader1);
              table.addCell(phrase);

              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);

              table.getDefaultCell().setColspan(1);
              phrase = new Phrase("Purchases.", pFontHeader1);
              table.addCell(phrase);
              table.getDefaultCell().setColspan(1);
              phrase = new Phrase("Sales", pFontHeader1);
              table.addCell(phrase);
              table.getDefaultCell().setColspan(1);

              // table
              while (rset1.next()) {
                seq = seq + 1;
                table.getDefaultCell().setColspan(1);
                table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);

                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
                phrase = new Phrase("" + seq + ".", pFontHeader);

                table.addCell(phrase);
                table.getDefaultCell().setColspan(1);

                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
                phrase = new Phrase(dbObject.getDBObject(rset1.getObject(2), "-"), pFontHeader);

                table.addCell(phrase);
                table.getDefaultCell().setColspan(1);
                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
                phrase = new Phrase(dbObject.getDBObject(rset1.getObject(3), "-"), pFontHeader);
                osBalanceQty13 = osBalanceQty13 + rset1.getDouble(3);
                table.addCell(phrase);
                table.getDefaultCell().setColspan(1);

                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);

                phrase =
                    new Phrase(
                        new com.afrisoftech.sys.Format2Currency()
                            .Format2Currency(rset1.getString(4)),
                        pFontHeader);
                osBalanceQty1 = osBalanceQty1 + rset1.getDouble(4);
                table.addCell(phrase);
                phrase = new Phrase(dbObject.getDBObject(rset1.getObject(5), "0"), pFontHeader);
                osBalanceQty14 = osBalanceQty14 + rset1.getDouble(5);
                table.addCell(phrase);
                /*

                phrase = new Phrase(new com.afrisoftech.sys.Format2Currency().Format2Currency(rset1.getString(6)),pFontHeader);
                osBalanceAmt1 = osBalanceAmt1 + rset1.getDouble(5);
                 osBalanceAmt12 = osBalanceAmt12 + rset1.getDouble(6);
                table.addCell(phrase);
                */
              }
              table.getDefaultCell().setBorderColor(java.awt.Color.BLACK);

              table.getDefaultCell().setBorder(Rectangle.BOTTOM | Rectangle.TOP);

              //  while (rsetTotals.next()) {

              table.getDefaultCell().setColspan(2);

              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
              phrase = new Phrase("TOTAL", pFontHeader);

              table.addCell(phrase);

              table.getDefaultCell().setColspan(1);

              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);

              phrase =
                  new Phrase(
                      new com.afrisoftech.sys.Format2Currency()
                          .Format2Currency(java.lang.String.valueOf(osBalanceQty13)),
                      pFontHeader);

              table.addCell(phrase);

              // phrase = new Phrase(" ");

              // table.addCell(phrase);

              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
              phrase =
                  new Phrase(
                      new com.afrisoftech.sys.Format2Currency()
                          .Format2Currency(java.lang.String.valueOf(osBalanceQty1)),
                      pFontHeader);

              // phrase = new Phrase(" ",pFontHeader);
              // phrase = new Phrase(" ",pFontHeader);

              table.addCell(phrase);

              // table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
              phrase =
                  new Phrase(
                      new com.afrisoftech.sys.Format2Currency()
                          .Format2Currency(java.lang.String.valueOf(osBalanceQty14)),
                      pFontHeader);

              // phrase = new Phrase(" ",pFontHeader);
              // phrase = new Phrase(" ",pFontHeader);

              table.addCell(phrase);
              /*
                table.getDefaultCell().setColspan(3);

                 table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
                 phrase = new Phrase("Totals", pFontHeader);

                 table.addCell(phrase);

                 table.getDefaultCell().setColspan(2);

                 table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
              */
              while (rset11.next()) {
                /*
                // phrase = new Phrase(new com.afrisoftech.sys.Format2Currency().Format2Currency(java.lang.String.valueOf((osBalanceQtybf + osBalanceQty) - osBalanceQty1)),pFontHeader);
                //  phrase = new Phrase(new com.afrisoftech.sys.Format2Currency().Format2Currency(java.lang.String.valueOf(closingBal+osBalanceQty)),pFontHeader);
                phrase = new Phrase(new com.afrisoftech.sys.Format2Currency().Format2Currency(rset11.getString(1)),pFontHeader);

                table.addCell(phrase);

                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
                phrase = new Phrase(new com.afrisoftech.sys.Format2Currency().Format2Currency(rset11.getString(2)),pFontHeader);

                //  phrase = new Phrase(new com.afrisoftech.sys.Format2Currency().Format2Currency(java.lang.String.valueOf((closingBal+osBalanceQty)*buyingPrice)),pFontHeader);

                table.addCell(phrase);
                */
              }
              /*
                                   try {


                  java.sql.Statement st31 = connectDB.createStatement();
                 // java.sql.Statement st4 = connectDB.createStatement();
                     java.sql.ResultSet rset2 = st31.executeQuery("select name from pb_notice");

                 // java.sql.ResultSet rset2 = st3.executeQuery("SELECT hospital_name from pb_hospitalprofile");
               //   java.sql.ResultSet rset4 = st4.executeQuery("SELECT date('now') as Date");
                  while(rset2.next())
                      Messg = rset2.getString(1);

                  com.lowagie.text.HeaderFooter footer = new com.lowagie.text.HeaderFooter(new Phrase(""+Messg+""),false);// FontFactory.getFont(com.lowagie.text.FontFactory.HELVETICA, 14, Font.BOLDITALIC,java.awt.Color.blue)));

                  //  com.lowagie.text.HeaderFooter headerFoter = new com.lowagie.text.HeaderFooter(new Phrase(""+compName+""),false);// FontFactory.getFont(com.lowagie.text.FontFactory.HELVETICA, 14, Font.BOLDITALIC,java.awt.Color.blue)));
                //  headerFoter.ALIGN_CENTER;
                 //  headerFoter.setRight(5);
                  docPdf.setFooter(footer);




              } catch(java.sql.SQLException SqlExec) {

                  javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), SqlExec.getMessage());

              }
                           */
              docPdf.add(table);

            } catch (java.sql.SQLException SqlExec) {

              javax.swing.JOptionPane.showMessageDialog(
                  new javax.swing.JFrame(), SqlExec.getMessage());
            }

            // }  // }

          } catch (com.lowagie.text.BadElementException BadElExec) {

            javax.swing.JOptionPane.showMessageDialog(
                new javax.swing.JFrame(), BadElExec.getMessage());
          }

        } catch (java.io.FileNotFoundException fnfExec) {

          javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), fnfExec.getMessage());
        }
      } catch (com.lowagie.text.DocumentException lwDocexec) {

        javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), lwDocexec.getMessage());
      }

      docPdf.close();
      com.afrisoftech.lib.PDFRenderer.renderPDF(tempFile);

    } catch (java.io.IOException IOexec) {

      javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), IOexec.getMessage());
    }
  }
예제 #4
0
  public void generatePdf(java.lang.String memNo) {

    java.lang.Process wait_for_Pdf2Show;

    java.util.Calendar cal = java.util.Calendar.getInstance();

    java.util.Date dateStampPdf = cal.getTime();

    java.lang.String pdfDateStamp = dateStampPdf.toString();

    try {

      java.io.File tempFile =
          java.io.File.createTempFile("REP" + this.getDateLable() + "_", ".pdf");

      tempFile.deleteOnExit();

      java.lang.Runtime rt = java.lang.Runtime.getRuntime();

      java.lang.String debitTotal = null;

      java.lang.String creditTotal = null;

      com.lowagie.text.Document docPdf = new com.lowagie.text.Document();

      double osBalance = 0.00;
      double current = 0.00;
      try {

        try {

          com.lowagie.text.pdf.PdfWriter.getInstance(
              docPdf, new java.io.FileOutputStream(tempFile));

          String compName = null;
          String date = null;
          String Messg = null;

          try {

            java.sql.Statement st3 = connectDB.createStatement();
            java.sql.Statement st4 = connectDB.createStatement();

            // java.sql.ResultSet rset2 = st3.executeQuery("SELECT hospital_name from
            // pb_hospitalprofile");
            java.sql.ResultSet rset4 =
                st4.executeQuery(
                    "SELECT TO_CHAR(current_timestamp(0),'FMDay FMDD/ MM/ YY HH12::MI')");

            while (rset4.next()) {
              date = rset4.getObject(1).toString();
            }

          } catch (java.sql.SQLException SqlExec) {

            javax.swing.JOptionPane.showMessageDialog(
                new javax.swing.JFrame(), SqlExec.getMessage());
          }
          docPdf.open();

          try {

            java.util.Calendar calendar = java.util.Calendar.getInstance();

            long dateNow = calendar.getTimeInMillis();

            java.sql.Date datenowSql = new java.sql.Date(dateNow);

            System.out.println(datenowSql.toString());

            java.lang.Object listofStaffNos[] = this.getListofStaffNos();

            com.lowagie.text.pdf.PdfPTable table1 = new com.lowagie.text.pdf.PdfPTable(7);
            //  com.lowagie.text.Table table = new com.lowagie.text.Table(7);

            // table.endHeaders();

            int headerwidths[] = {15, 15, 30, 15, 15, 15, 15};

            table1.setWidths(headerwidths);
            //  if (docPdf.getPageNumber() > 1) {
            //  table1.setHeaderRows(4);
            //  }
            table1.setWidthPercentage((100));

            table1.getDefaultCell().setBorder(Rectangle.BOTTOM);

            table1.getDefaultCell().setColspan(7);

            Phrase phrase = new Phrase();

            //  table.addCell(phrase);

            table1.getDefaultCell().setColspan(1);
            table1.getDefaultCell().setBackgroundColor(java.awt.Color.WHITE);
            table1.getDefaultCell().setBorderColor(java.awt.Color.WHITE);

            try {
              java.sql.Statement st2 = connectDB.createStatement();

              java.sql.ResultSet rset2 =
                  st2.executeQuery("SELECT rep_currency from pb_hospitalprofile");
              while (rset2.next()) {
                ks = rset2.getObject(1).toString();
              }
              java.sql.Statement st3 = connectDB.createStatement();
              java.sql.ResultSet rset3 = st3.executeQuery("select header_name from pb_header");
              while (rset3.next()) {
                table1.getDefaultCell().setColspan(7);

                table1.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_CENTER);
                phrase = new Phrase(rset3.getObject(1).toString(), pFontHeader11);
                table1.addCell(phrase);
              }
            } catch (java.sql.SQLException SqlExec) {

              javax.swing.JOptionPane.showMessageDialog(
                  new javax.swing.JFrame(), SqlExec.getMessage());
            }
            docPdf.add(table1);
          } catch (com.lowagie.text.BadElementException BadElExec) {

            javax.swing.JOptionPane.showMessageDialog(
                new javax.swing.JFrame(), BadElExec.getMessage());
          }

          try {

            java.util.Calendar calendar = java.util.Calendar.getInstance();

            long dateNow = calendar.getTimeInMillis();

            java.sql.Date datenowSql = new java.sql.Date(dateNow);

            System.out.println(datenowSql.toString());

            java.lang.Object listofStaffNos[] = this.getListofStaffNos();

            com.lowagie.text.pdf.PdfPTable table = new com.lowagie.text.pdf.PdfPTable(8);
            //  com.lowagie.text.Table table = new com.lowagie.text.Table(7);

            // table.endHeaders();

            int headerwidths[] = {12, 8, 30, 15, 10, 10, 10, 10};

            table.setWidths(headerwidths);
            //  if (docPdf.getPageNumber() > 1) {
            table.setHeaderRows(5);
            //  }
            table.setWidthPercentage((100));

            table.getDefaultCell().setBorder(Rectangle.BOTTOM);

            table.getDefaultCell().setColspan(8);

            Phrase phrase = new Phrase();

            //  table.addCell(phrase);

            table.getDefaultCell().setColspan(1);
            table.getDefaultCell().setBackgroundColor(java.awt.Color.WHITE);
            table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);

            try {
              double Debit = 0.00;
              //    double Credit = 0.00;

              java.sql.Statement st22 = connectDB.createStatement();
              java.sql.Statement st11 = connectDB.createStatement();
              java.sql.Statement st = connectDB.createStatement();
              java.sql.Statement st1 = connectDB.createStatement();
              java.sql.Statement st2 = connectDB.createStatement();
              java.sql.Statement st3 = connectDB.createStatement();
              java.sql.Statement st4 = connectDB.createStatement();
              java.sql.Statement st41 = connectDB.createStatement();
              java.sql.Statement st5 = connectDB.createStatement();
              java.sql.ResultSet rset3 =
                  st3.executeQuery(
                      "select hospital_name,postal_code||' '||box_no||' '||town,main_telno||' '||other_telno,initcap(street),main_faxno,email,website,room_no from pb_hospitalprofile");
              //  java.sql.ResultSet rset4 = st4.executeQuery("select scheme_manager from
              // ac_scheme_providers where scheme_manager = '"+memNo+"'");
              java.sql.ResultSet rset4 =
                  st4.executeQuery(
                      "select scheme_name,account_no from ac_schemes where account_no = '"
                          + memNo1
                          + "'");
              java.sql.ResultSet rset41 =
                  st41.executeQuery(
                      "select payer_name from ac_schemes where account_no = '" + memNo1 + "'");
              //   java.sql.ResultSet rset1 = st1.executeQuery("select ac.date,pc.scheme_staff_no,
              // ac.payee,ac.reference_no,sum(ac.debit),ac.admission_no from hp_patient_card
              // pc,ac_debtors ac where pc.patient_no = ac.admission_no and ac.dealer = '"+memNo+"'
              // and ac.dealer = pc.scheme AND ac.date::date BETWEEN '"+beginDate+"' AND
              // '"+endDate+"' and ac.debit > 0 group by ac.date,pc.scheme_staff_no,
              // ac.payee,ac.reference_no,ac.admission_no");// UNION select
              // pd.date::date,initcap(pd.scheme_staff_no), (sh.first_name||' '||sh.second_name||'
              // '||sh.last_name) as name,pd.reference,sum(pd.credit),pd.patient_no from
              // hp_patient_card pd,hp_inpatient_register sh where pd.patient_no = sh.patient_no and
              // pd.isurer = '"+memNo+"' AND pd.date::date BETWEEN '"+beginDate+"' AND '"+endDate+"'
              // and pd.credit > 0 group by pd.date::date,pd.scheme_staff_no,
              // name,pd.reference,pd.invoice_no,pd.patient_no order by pd.invoice_no");
              java.sql.ResultSet rset1 =
                  st1.executeQuery(
                      "select date, upper(item),credit from ac_debtors where account_no = '"
                          + memNo1
                          + "' AND receipt_no ilike '"
                          + memNo
                          + "' AND date::date BETWEEN '"
                          + beginDate
                          + "' AND '"
                          + endDate
                          + "' order by date"); // UNION select
              // pd.date::date,initcap(pd.scheme_staff_no),
              // (sh.first_name||' '||sh.second_name||'
              // '||sh.last_name) as
              // name,pd.reference,sum(pd.credit),pd.patient_no
              // from hp_patient_card pd,hp_inpatient_register sh
              // where pd.patient_no = sh.patient_no and pd.isurer
              // = '"+memNo+"' AND pd.date::date BETWEEN
              // '"+beginDate+"' AND '"+endDate+"' and pd.credit >
              // 0 group by pd.date::date,pd.scheme_staff_no,
              // name,pd.reference,pd.invoice_no,pd.patient_no
              // order by pd.invoice_no");
              // java.sql.ResultSet rsetTotals2 = st22.executeQuery("select sum(balance-credit_bal)
              // from ac_debtors where account_no = '"+memNo1+"' AND date::date < '"+beginDate+"'");

              table.getDefaultCell().setBorderColor(java.awt.Color.BLACK);
              table.getDefaultCell().setBorderWidth(Rectangle.TOP | Rectangle.BOTTOM);

              table.getDefaultCell().setColspan(4);
              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_CENTER);
              phrase = new Phrase("CREDIT NOTE", pFontHeader11);
              table.addCell(phrase);
              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
              phrase = new Phrase("CREDIT NOTE NO :  " + memNo, pFontHeader11);
              table.addCell(phrase);

              while (rset4.next()) {
                table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);
                table.getDefaultCell().setColspan(8);

                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
                phrase = new Phrase(dbObject.getDBObject(rset4.getObject(1), "-"), pFontHeader1);
                table.addCell(phrase);

                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
                phrase =
                    new Phrase(
                        "Account No : " + dbObject.getDBObject(rset4.getObject(2), "-"),
                        pFontHeader1);
                table.addCell(phrase);
              }
              while (rset41.next()) {
                table.getDefaultCell().setColspan(8);

                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
                phrase = new Phrase(dbObject.getDBObject(rset41.getObject(1), "-"), pFontHeader1);
                table.addCell(phrase);
              }
              table.getDefaultCell().setColspan(4);

              table.getDefaultCell().setColspan(8);

              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
              phrase = new Phrase("Print Date: " + date, pFontHeader1);
              table.addCell(phrase);

              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
              table.getDefaultCell().setBorderColor(java.awt.Color.BLACK);
              table.getDefaultCell().setBorderWidth(Rectangle.TOP);
              table.getDefaultCell().setColspan(2);
              phrase = new Phrase("Date", pFontHeader1);
              table.addCell(phrase);
              table.getDefaultCell().setColspan(2);
              phrase = new Phrase("Narration.", pFontHeader1);
              table.addCell(phrase);

              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);

              table.getDefaultCell().setColspan(2);
              phrase = new Phrase("Orig. Amt ", pFontHeader1);
              table.addCell(phrase);

              phrase = new Phrase("Running Amt. " + ks, pFontHeader1);
              table.addCell(phrase);

              while (rset1.next()) {
                table.getDefaultCell().setColspan(2);
                table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);

                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
                phrase = new Phrase(dbObject.getDBObject(rset1.getObject(1), "-"), pFontHeader);

                table.addCell(phrase);
                table.getDefaultCell().setColspan(2);

                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
                phrase = new Phrase(dbObject.getDBObject(rset1.getObject(2), "-"), pFontHeader);

                table.addCell(phrase);

                table.getDefaultCell().setColspan(2);
                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);

                phrase =
                    new Phrase(
                        new com.afrisoftech.sys.Format2Currency()
                            .Format2Currency(rset1.getString(3)),
                        pFontHeader);
                osBalance = osBalance + rset1.getDouble(3);
                table.addCell(phrase);
                phrase =
                    new Phrase(
                        new com.afrisoftech.sys.Format2Currency()
                            .Format2Currency(java.lang.String.valueOf(osBalance)),
                        pFontHeader);

                table.addCell(phrase);
              }
              table.getDefaultCell().setColspan(2);

              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
              phrase = new Phrase("", pFontHeader);

              table.addCell(phrase);
              table.getDefaultCell().setBorderColor(java.awt.Color.BLACK);

              table.getDefaultCell().setBorder(Rectangle.BOTTOM | Rectangle.TOP);

              table.getDefaultCell().setColspan(2);

              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
              phrase = new Phrase("Total", pFontHeader);

              table.addCell(phrase);

              table.getDefaultCell().setColspan(4);

              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);

              phrase =
                  new Phrase(
                      new com.afrisoftech.sys.Format2Currency()
                          .Format2Currency(java.lang.String.valueOf(osBalance)),
                      pFontHeader);

              table.addCell(phrase);

              docPdf.add(table);

            } catch (java.sql.SQLException SqlExec) {

              javax.swing.JOptionPane.showMessageDialog(
                  new javax.swing.JFrame(), SqlExec.getMessage());
            }

            // }  // }

          } catch (com.lowagie.text.BadElementException BadElExec) {

            javax.swing.JOptionPane.showMessageDialog(
                new javax.swing.JFrame(), BadElExec.getMessage());
          }

        } catch (java.io.FileNotFoundException fnfExec) {

          javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), fnfExec.getMessage());
        }
      } catch (com.lowagie.text.DocumentException lwDocexec) {

        javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), lwDocexec.getMessage());
      }

      docPdf.close();
      com.afrisoftech.lib.PDFRenderer.renderPDF(tempFile);

    } catch (java.io.IOException IOexec) {

      javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), IOexec.getMessage());
    }
  }
  public void generatePdf() {

    java.lang.Process wait_for_Pdf2Show;

    java.util.Calendar cal = java.util.Calendar.getInstance();

    java.util.Date dateStampPdf = cal.getTime();

    java.lang.String pdfDateStamp = dateStampPdf.toString();

    try {

      java.io.File tempFile =
          java.io.File.createTempFile("REP" + this.getDateLable() + "_", ".pdf");

      tempFile.deleteOnExit();

      java.lang.Runtime rt = java.lang.Runtime.getRuntime();

      java.lang.String debitTotal = null;

      java.lang.String creditTotal = null;

      //            com.lowagie.text.Document docPdf = new com.lowagie.text.Document();
      com.lowagie.text.Document docPdf = new com.lowagie.text.Document();

      try {

        try {

          com.lowagie.text.pdf.PdfWriter.getInstance(
              docPdf, new java.io.FileOutputStream(tempFile));

          double osBalancebf = 0.00;
          double osBalance = 0.00;
          double osBalanceb1 = 0.00;

          String compName = null;
          String date = null;

          docPdf.open();

          try {

            java.util.Calendar calendar = java.util.Calendar.getInstance();

            long dateNow = calendar.getTimeInMillis();

            java.sql.Date datenowSql = new java.sql.Date(dateNow);

            System.out.println(datenowSql.toString());

            //  java.lang.Object listofStaffNos[] = this.getListofStaffNos();

            com.lowagie.text.pdf.PdfPTable table1 = new com.lowagie.text.pdf.PdfPTable(7);
            //  com.lowagie.text.Table table = new com.lowagie.text.Table(7);

            // table.endHeaders();

            int headerwidths[] = {15, 15, 30, 15, 15, 15, 15};

            table1.setWidths(headerwidths);
            //  if (docPdf.getPageNumber() > 1) {
            //  table1.setHeaderRows(4);
            //  }
            table1.setWidthPercentage((100));

            table1.getDefaultCell().setBorder(Rectangle.BOTTOM);

            table1.getDefaultCell().setColspan(7);

            Phrase phrase = new Phrase();

            //  table.addCell(phrase);

            table1.getDefaultCell().setColspan(1);
            table1.getDefaultCell().setBackgroundColor(java.awt.Color.WHITE);
            table1.getDefaultCell().setBorderColor(java.awt.Color.WHITE);

            try {
              java.sql.Statement st3 = connectDB.createStatement();
              java.sql.ResultSet rset3 = st3.executeQuery("select header_name from pb_header");
              while (rset3.next()) table1.getDefaultCell().setColspan(7);

              table1.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_CENTER);
              phrase = new Phrase(rset3.getObject(1).toString(), pFontHeader1);
              table1.addCell(phrase);

            } catch (java.sql.SQLException SqlExec) {

              javax.swing.JOptionPane.showMessageDialog(
                  new javax.swing.JFrame(), SqlExec.getMessage());
            }
            docPdf.add(table1);
          } catch (com.lowagie.text.BadElementException BadElExec) {

            javax.swing.JOptionPane.showMessageDialog(
                new javax.swing.JFrame(), BadElExec.getMessage());
          }

          com.lowagie.text.HeaderFooter footer =
              new com.lowagie.text.HeaderFooter(
                  new Phrase("Debtors Statement - Page: ", pFontHeader),
                  true); // FontFactory.getFont(com.lowagie.text.FontFactory.HELVETICA, 12,
                         // Font.BOLDITALIC,java.awt.Color.blue));

          docPdf.setFooter(footer);

          try {

            java.util.Calendar calendar = java.util.Calendar.getInstance();

            long dateNow = calendar.getTimeInMillis();

            java.sql.Date datenowSql = new java.sql.Date(dateNow);

            System.out.println(datenowSql.toString());

            com.lowagie.text.pdf.PdfPTable table = new com.lowagie.text.pdf.PdfPTable(6);

            int headerwidths[] = {10, 30, 15, 15, 15, 20};

            table.setWidths(headerwidths);

            table.setWidthPercentage((100));

            table.setHeaderRows(2);

            table.getDefaultCell().setBorder(Rectangle.BOTTOM);

            Phrase phrase = new Phrase();
            //  table.getDefaultCell().setColspan(8);

            table.getDefaultCell().setColspan(4);

            try {
              java.text.DateFormat dateFormat =
                  java.text.DateFormat.getDateInstance(java.text.DateFormat.MEDIUM); // MEDIUM);

              java.util.Date endDate1 =
                  dateFormat.parse(endDate.toLocaleString()); // dateInstance.toLocaleString());
              java.util.Date endDate11 =
                  dateFormat.parse(beginDate.toLocaleString()); // dateInstance.toLocaleString());

              System.out.println("" + endDate1);
              //  phrase = new Phrase(bank +" Report: " +dateFormat.format(formattedDate),
              // pFontHeader);

              //  table.addCell(phrase);

              phrase =
                  new Phrase(
                      Debtor
                          + " Period : "
                          + dateFormat.format(endDate11)
                          + " - "
                          + dateFormat.format(endDate1),
                      pFontHeader1);

              table.addCell(phrase);
            } catch (java.text.ParseException psExec) {

              javax.swing.JOptionPane.showMessageDialog(
                  new javax.swing.JFrame(), psExec.getMessage());
            }
            // table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
            // phrase = new Phrase("Period : '"+beginDate+"' - '"+endDate+"'", pFontHeader1);
            // table.addCell(phrase);

            table.getDefaultCell().setColspan(2);

            table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
            phrase = new Phrase("Date " + datenowSql, pFontHeader1);
            table.addCell(phrase);

            table.getDefaultCell().setColspan(3);
            table.getDefaultCell().setColspan(1);

            //    table.getDefaultCell().setBackgroundColor(java.awt.Color.LIGHT_GRAY);

            phrase = new Phrase("Acc No", pFontHeader);
            table.addCell(phrase);

            phrase = new Phrase("Scheme", pFontHeader);
            table.addCell(phrase);

            table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
            phrase = new Phrase("Debit", pFontHeader);
            table.addCell(phrase);

            phrase = new Phrase("Credit", pFontHeader);
            table.addCell(phrase);

            phrase = new Phrase("Balance ", pFontHeader);
            table.addCell(phrase);

            phrase = new Phrase("Running Total", pFontHeader);
            table.addCell(phrase);

            table.getDefaultCell().setBackgroundColor(java.awt.Color.WHITE);
            // table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);

            try {

              java.sql.Statement st = connectDB.createStatement();
              java.sql.Statement st1 = connectDB.createStatement();

              java.sql.Statement st2 = connectDB.createStatement();

              java.sql.ResultSet rset =
                  st.executeQuery(
                      "select db.account_no,db.payee,sum(db.debit),sum(credit),SUM(db.balance) from ac_debtors db WHERE db.date BETWEEN '"
                          + beginDate
                          + "' AND '"
                          + endDate
                          + "' AND db.dealer ILIKE '"
                          + Debtor
                          + "' GROUP BY db.payee,db.account_no ORDER BY db.payee");
              java.sql.ResultSet rset1 =
                  st1.executeQuery(
                      "select SUM(db.balance) from ac_debtors db WHERE db.date < '"
                          + beginDate
                          + "' AND db.dealer ILIKE '"
                          + Debtor
                          + "' GROUP BY db.account_no");

              while (rset1.next()) {
                bfwd = rset1.getDouble(1);
                bfcd = bfcd + rset1.getDouble(1);
              }
              while (rset.next()) {

                table.getDefaultCell().setColspan(1);

                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
                phrase = new Phrase(dbObject.getDBObject(rset.getObject(1), "-"), pFontHeader);

                table.addCell(phrase);
                table.getDefaultCell().setColspan(1);

                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
                phrase = new Phrase(dbObject.getDBObject(rset.getObject(2), "-"), pFontHeader);

                table.addCell(phrase);

                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
                phrase =
                    new Phrase(
                        new com.afrisoftech.sys.Format2Currency()
                            .Format2Currency(rset.getString(3)),
                        pFontHeader1);
                osBalance = osBalance + rset.getDouble(3);
                table.addCell(phrase);

                phrase =
                    new Phrase(
                        new com.afrisoftech.sys.Format2Currency()
                            .Format2Currency(rset.getString(4)),
                        pFontHeader1);
                osBalanceb1 = osBalanceb1 + rset.getDouble(4);
                table.addCell(phrase);

                phrase =
                    new Phrase(
                        new com.afrisoftech.sys.Format2Currency()
                            .Format2Currency(rset.getString(5)),
                        pFontHeader);
                osBalancebf = osBalancebf + rset.getDouble(5);
                // osBalancebf = osBalance1;
                table.addCell(phrase);

                phrase =
                    new Phrase(
                        new com.afrisoftech.sys.Format2Currency()
                            .Format2Currency(java.lang.String.valueOf(osBalancebf)),
                        pFontHeader);

                table.addCell(phrase);
              }
              // }
              table.getDefaultCell().setBorderColor(java.awt.Color.BLACK);

              table.getDefaultCell().setBorder(Rectangle.BOTTOM | Rectangle.TOP);

              //   while (rsetTotals.next()) {

              table.getDefaultCell().setColspan(2);

              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
              phrase = new Phrase("Total", pFontHeader);

              table.addCell(phrase);

              table.getDefaultCell().setColspan(1);

              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);

              phrase =
                  new Phrase(
                      new com.afrisoftech.sys.Format2Currency()
                          .Format2Currency(java.lang.String.valueOf(osBalance)),
                      pFontHeader);

              table.addCell(phrase);

              // table.getDefaultCell().setColspan(2);

              // table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);

              phrase =
                  new Phrase(
                      new com.afrisoftech.sys.Format2Currency()
                          .Format2Currency(java.lang.String.valueOf(osBalanceb1)),
                      pFontHeader);

              table.addCell(phrase);

              table.getDefaultCell().setColspan(2);

              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);

              phrase =
                  new Phrase(
                      new com.afrisoftech.sys.Format2Currency()
                          .Format2Currency(java.lang.String.valueOf(osBalancebf)),
                      pFontHeader);

              table.addCell(phrase);

              docPdf.add(table);

            } catch (java.sql.SQLException SqlExec) {

              javax.swing.JOptionPane.showMessageDialog(
                  new javax.swing.JFrame(), SqlExec.getMessage());
            }

          } catch (com.lowagie.text.BadElementException BadElExec) {

            javax.swing.JOptionPane.showMessageDialog(
                new javax.swing.JFrame(), BadElExec.getMessage());
          }

        } catch (java.io.FileNotFoundException fnfExec) {

          javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), fnfExec.getMessage());
        }
      } catch (com.lowagie.text.DocumentException lwDocexec) {

        javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), lwDocexec.getMessage());
      }

      docPdf.close();

      try {

        if (System.getProperty("os.name").equalsIgnoreCase("Linux")) {

          System.out.println(tempFile);

          //  wait_for_Pdf2Show = rt.exec("kghostview "+tempFile+"");

          wait_for_Pdf2Show = rt.exec("kghostview " + tempFile + "");

          wait_for_Pdf2Show.waitFor();

        } else {

          wait_for_Pdf2Show =
              rt.exec("c:/Program Files/Adobe/Acrobat 5.0/Reader/AcroRd32.exe " + tempFile);

          wait_for_Pdf2Show.waitFor();
        }

      } catch (java.lang.InterruptedException intrExec) {

        javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), intrExec.getMessage());
      }

    } catch (java.io.IOException IOexec) {

      javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), IOexec.getMessage());
    }
  }
  public void generatePdf() {

    // java.lang.Object[][] ageingDates = periodicDates.getMonthlyDates();

    ageingDates = ageingSeries.getAgeingDateSeries();

    double columnTotals[] = new double[ageingDates.length];
    double columnTotals2[] = new double[ageingDates.length];
    java.lang.Process wait_for_Pdf2Show;

    java.util.Calendar cal = java.util.Calendar.getInstance();

    java.util.Date dateStampPdf = cal.getTime();

    java.lang.String pdfDateStamp = dateStampPdf.toString();

    int interval = 0;

    try {

      java.io.File tempFile =
          java.io.File.createTempFile("REP" + this.getDateLable() + "_", ".pdf");

      tempFile.deleteOnExit();

      java.lang.Runtime rt = java.lang.Runtime.getRuntime();

      java.lang.String debitTotal = null;

      java.lang.String creditTotal = null;

      com.lowagie.text.Document docPdf = new com.lowagie.text.Document();

      java.util.Calendar calendar = java.util.Calendar.getInstance();

      long dateNow = calendar.getTimeInMillis();

      java.sql.Date datenowSql = new java.sql.Date(dateNow);

      System.out.println(datenowSql.toString());

      try {

        try {

          com.lowagie.text.pdf.PdfWriter.getInstance(
              docPdf, new java.io.FileOutputStream(tempFile));

          String compName = null;
          String date = null;
          try {

            //   java.sql.Connection conDb =
            // java.sql.DriverManager.getConnection("jdbc:postgresql://localhost:5432/sako","postgres","pilsiner");

            java.sql.Statement st3 = connectDB.createStatement();
            java.sql.Statement st4 = connectDB.createStatement();

            java.sql.ResultSet rset2 =
                st3.executeQuery("SELECT hospital_name,rep_currency from pb_hospitalprofile");
            java.sql.ResultSet rset4 = st4.executeQuery("SELECT date('now') as Date");
            while (rset2.next()) {
              compName = rset2.getObject(1).toString();
              ks = rset2.getString(2);
            }
            while (rset4.next()) {
              date = rset4.getObject(1).toString();
            }
            com.lowagie.text.HeaderFooter headerFoter =
                new com.lowagie.text.HeaderFooter(
                    new Phrase("" + compName, pFontHeader2),
                    false); // FontFactory.getFont(com.lowagie.text.FontFactory.HELVETICA, 14,
                            // Font.BOLDITALIC,java.awt.Color.blue)));

            //  com.lowagie.text.HeaderFooter headerFoter = new com.lowagie.text.HeaderFooter(new
            // Phrase(""+compName+""),false);//
            // FontFactory.getFont(com.lowagie.text.FontFactory.HELVETICA, 14,
            // Font.BOLDITALIC,java.awt.Color.blue)));
            headerFoter.setAlignment(com.lowagie.text.HeaderFooter.ALIGN_CENTER);
            docPdf.setHeader(headerFoter);

          } catch (java.sql.SQLException SqlExec) {

            javax.swing.JOptionPane.showMessageDialog(
                new javax.swing.JFrame(), SqlExec.getMessage());
          }

          com.lowagie.text.HeaderFooter footer =
              new com.lowagie.text.HeaderFooter(
                  new Phrase("Ageing  Page: ", pFontHeader),
                  true); // FontFactory.getFont(com.lowagie.text.FontFactory.HELVETICA, 12,
                         // Font.BOLDITALIC,java.awt.Color.blue));

          docPdf.setFooter(footer);

          docPdf.open();

          double Totals = 0.00;
          double OS = 0.00;
          try {

            com.lowagie.text.pdf.PdfPTable table =
                new com.lowagie.text.pdf.PdfPTable(ageingDates.length + 5);

            String headerWidths = null;

            java.util.Vector headerVector = new java.util.Vector(1, 1);

            int z = ageingDates.length;

            int headerwidths[] = {7, 35, 13, 13, 13, 13, 13, 13, 13}; // ,13,13};

            table.setWidths(headerwidths);

            table.setWidthPercentage((107));

            table.setHeaderRows(2);

            table.getDefaultCell().setBorder(Rectangle.BOTTOM);

            table.getDefaultCell().setColspan(9);
            Phrase phrase = new Phrase("");
            table.getDefaultCell().setColspan(6);
            try {
              java.text.DateFormat dateFormat =
                  java.text.DateFormat.getDateInstance(java.text.DateFormat.MEDIUM); // MEDIUM);

              //                            java.util.Date endDate1 =
              // dateFormat.parse(beginDate.toLocaleString());//dateInstance.toLocaleString());
              java.util.Date endDate2 = dateFormat.parse(endDate.toLocaleString());

              phrase =
                  new Phrase("Debtors Ageing as at " + dateFormat.format(endDate2), pFontHeader);

              table.addCell(phrase);
            } catch (java.text.ParseException psExec) {

              javax.swing.JOptionPane.showMessageDialog(
                  new javax.swing.JFrame(), psExec.getMessage());
            }

            table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
            table.getDefaultCell().setColspan(3);
            phrase = new Phrase("Printed on : " + date, pFontHeader);
            table.addCell(phrase);

            table.getDefaultCell().setColspan(1);

            table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_CENTER);
            phrase = new Phrase("No.", pFontHeader);
            table.addCell(phrase);

            phrase = new Phrase("Name", pFontHeader);
            table.addCell(phrase);

            for (int x = 0; x < ageingDates.length; x++) {
              // for (int x = ageingDates.length - 1; x >= 0; x--) {

              int days = 30;
              if (x < 1) {
                phrase = new Phrase("Current", pFontHeader);
              } else {
                phrase = new Phrase("+ " + x * days + " Days", pFontHeader);
                interval = x;
              }

              table.addCell(phrase);
            }

            phrase = new Phrase("+ " + ((interval + 1) * 30) + " Days", pFontHeader);
            table.addCell(phrase);

            phrase = new Phrase("UnAlloc.", pFontHeader);
            table.addCell(phrase);

            phrase = new Phrase("O/S " + ks, pFontHeader);
            table.addCell(phrase);

            table.getDefaultCell().setBackgroundColor(java.awt.Color.WHITE);

            try {

              double GrandTotal = 0.00;
              double Over120Total = 0.00;
              java.lang.Object[] listofAct = this.getListofActivities();

              System.out.println(listofAct.length);

              for (int i = 0; i < listofAct.length; i++) {
                double TurnOver = 0.00;
                double Over120 = 0.00;
                double TotalCount = 0.00;
                double unalloc = 0.00;
                double Curr120 = 0.00;

                double bals = 0.00;
                // java.lang.Object[] listofActinv = this.getListofActivitiesinv(listofAct[i]);
                table.getDefaultCell().setColspan(1);

                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
                table.getDefaultCell().setColspan(1);
                java.sql.Statement stmta1 = connectDB.createStatement();
                java.sql.PreparedStatement pSeta1 =
                    connectDB.prepareStatement(
                        "SELECT count(account_no) FROM ac_debtors"
                            + " where  account_no = '"
                            + listofAct[i]
                            + "'");
                java.sql.PreparedStatement pset22 =
                    connectDB.prepareStatement(
                        "select distinct account_no||' '||scheme_name from "
                            + "ac_schemes WHERE account_no = ? order by 1 ASC"); // <
                                                                                 // '"+endDate+"'::date and date > '"+endDate+"'::date - 30 group by dealer");
                pset22.setString(1, listofAct[i].toString().toUpperCase());
                java.sql.ResultSet rSeta1 = pSeta1.executeQuery();
                while (rSeta1.next()) {
                  name = rSeta1.getInt(1);
                }
                if (name > 0) {
                  java.sql.ResultSet rset22 = pset22.executeQuery();

                  while (rset22.next()) {
                    cnt = cnt + 1;
                    phrase = new Phrase("" + cnt, pFontHeader1);
                    table.addCell(phrase);

                    table.getDefaultCell().setColspan(1);
                    phrase =
                        new Phrase(dbObject.getDBObject(rset22.getObject(1), "-"), pFontHeader1);
                    table.addCell(phrase);

                    java.sql.Statement st2 = connectDB.createStatement();
                    java.sql.Statement st21 = connectDB.createStatement();
                    java.sql.Statement st22 = connectDB.createStatement();
                    java.sql.Statement st23 = connectDB.createStatement();
                    java.sql.Statement st211 = connectDB.createStatement();
                    java.sql.Statement st221 = connectDB.createStatement();
                    java.sql.Statement st231 = connectDB.createStatement();
                    java.sql.Statement st2A = connectDB.createStatement();
                    java.sql.Statement st2B = connectDB.createStatement();
                    java.sql.Statement st2C = connectDB.createStatement();
                    java.sql.Statement stc = connectDB.createStatement();
                    System.out.println("Dealer Is : [" + listofAct[i] + "].");
                    java.sql.PreparedStatement pset111 =
                        connectDB.prepareStatement(
                            "select sum(debit-credit) from ac_debtors "
                                + "WHERE account_no = ?  AND date <= '"
                                + endDate
                                + "'"); // < '"+endDate+"'::date and date > '"+endDate+"'::date - 30
                                        // group by dealer");
                    pset111.setString(1, listofAct[i].toString());
                    java.sql.ResultSet rset111 = pset111.executeQuery();

                    java.sql.Statement st02 = connectDB.createStatement();

                    java.sql.PreparedStatement pset112 =
                        connectDB.prepareStatement(
                            "select sum(credit) from ac_debtors"
                                + " WHERE account_no = ? AND date <= '"
                                + endDate
                                + "'"); // < '"+endDate+"'::date and date > '"+endDate+"'::date - 30
                                        // group by dealer");
                    pset112.setString(1, listofAct[i].toString());
                    java.sql.ResultSet rset112 = pset112.executeQuery();

                    // for (int k = 0; k < listofActinv.length; k++) {

                    for (int t = ageingDates.length - 1; t >= 0; t--) {
                      columnTotals[t] = 0.00;
                      double credo = 0.00;
                      double credo1 = 0.00;
                      Over120 = 0.00;
                      java.sql.Statement st01 = connectDB.createStatement();

                      java.sql.PreparedStatement pset =
                          connectDB.prepareStatement(
                              "select sum(debit),invoice_no from ac_debtors "
                                  + "WHERE account_no = ?  AND date between '"
                                  + ageingDates[t][0]
                                  + "' AND '"
                                  + ageingDates[t][1]
                                  + "' "
                                  + " GROUP BY invoice_no order by 2 ASC");
                      pset.setString(1, listofAct[i].toString().toUpperCase());
                      java.sql.ResultSet rset = pset.executeQuery();

                      while (rset.next()) {
                        String invoiceNo = rset.getString(2);

                        java.sql.Statement st01x = connectDB.createStatement();
                        java.sql.PreparedStatement psetx =
                            connectDB.prepareStatement(
                                "select sum(credit),invoice_no from ac_debtors"
                                    + " WHERE account_no = ?  AND date <= '"
                                    + endDate
                                    + "' AND invoice_no = '"
                                    + invoiceNo
                                    + "'  GROUP BY invoice_no order by 2 ASC ");
                        psetx.setString(1, listofAct[i].toString().toUpperCase());
                        java.sql.ResultSet rsetx = psetx.executeQuery();
                        while (rsetx.next()) {
                          credo = rsetx.getDouble(1);
                        }

                        table.getDefaultCell().setColspan(1);
                        table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);

                        columnTotals[t] = columnTotals[t] + (rset.getDouble(1) - credo);
                        TotalCount = TotalCount + (rset.getDouble(1) - credo);
                        columnTotals2[t] = columnTotals2[t] + (rset.getDouble(1) - credo);
                      }
                    }

                    for (int t = ageingDates.length - 1; t >= 0; t--) {
                      table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);

                      phrase =
                          new Phrase(
                              new com.afrisoftech.sys.Format2Currency()
                                  .Format2Currency(java.lang.String.valueOf(columnTotals[t])),
                              pFontHeader1);
                      table.addCell(phrase);

                      // }
                    }
                    table.getDefaultCell().setColspan(1);
                    table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);

                    while (rset111.next()) {
                      bals = rset111.getDouble(1);
                      phrase =
                          new Phrase(
                              new com.afrisoftech.sys.Format2Currency()
                                  .Format2Currency(java.lang.String.valueOf(bals - TotalCount)),
                              pFontHeader1);

                      table.addCell(phrase);

                      Over120Total = Over120Total + (bals - TotalCount);
                      // Over120Total = Over120Total+ rset1.getDouble(1);
                      TurnOver = TotalCount + Curr120;

                      phrase =
                          new Phrase(
                              new com.afrisoftech.sys.Format2Currency()
                                  .Format2Currency(java.lang.String.valueOf(bals - bals)),
                              pFontHeader1);
                      table.addCell(phrase);
                      OS = OS + unalloc;

                      phrase =
                          new Phrase(
                              new com.afrisoftech.sys.Format2Currency()
                                  .Format2Currency(rset111.getString(1)),
                              pFontHeader1);
                      table.addCell(phrase);

                      Totals = Totals + rset111.getDouble(1);
                    }
                  }
                }
              }

              table.getDefaultCell().setBorderColor(java.awt.Color.BLACK);

              table.getDefaultCell().setBorder(Rectangle.BOTTOM | Rectangle.TOP);

              // while (rsetTotals.next()) {

              table.getDefaultCell().setColspan(2);

              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
              phrase = new Phrase("Total", pFontHeader);

              table.addCell(phrase);

              table.getDefaultCell().setColspan(1);

              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);

              for (int x = ageingDates.length - 1; x >= 0; x--) {

                phrase =
                    new Phrase(
                        new com.afrisoftech.sys.Format2Currency()
                            .Format2Currency(java.lang.String.valueOf(columnTotals2[x])),
                        pFontHeader);

                table.addCell(phrase);
                grandTot = grandTot + columnTotals2[x];
              }

              phrase =
                  new Phrase(
                      new com.afrisoftech.sys.Format2Currency()
                          .Format2Currency(java.lang.String.valueOf(Totals - grandTot)),
                      pFontHeader);

              table.addCell(phrase);
              phrase =
                  new Phrase(
                      new com.afrisoftech.sys.Format2Currency()
                          .Format2Currency(java.lang.String.valueOf(OS)),
                      pFontHeader);

              table.addCell(phrase);

              phrase =
                  new Phrase(
                      new com.afrisoftech.sys.Format2Currency()
                          .Format2Currency(java.lang.String.valueOf(Totals)),
                      pFontHeader);

              table.addCell(phrase);

              docPdf.add(table);

            } catch (java.sql.SQLException SqlExec) {

              SqlExec.printStackTrace();

              javax.swing.JOptionPane.showMessageDialog(
                  new javax.swing.JFrame(), SqlExec.getMessage());
            }

            /*   } catch(java.text.ParseException psExec) {

                 javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), psExec.getMessage());

             }
            */

          } catch (com.lowagie.text.BadElementException BadElExec) {

            // Bad

            javax.swing.JOptionPane.showMessageDialog(
                new javax.swing.JFrame(), BadElExec.getMessage());
          }

        } catch (java.io.FileNotFoundException fnfExec) {

          javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), fnfExec.getMessage());
        }
      } catch (com.lowagie.text.DocumentException lwDocexec) {

        javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), lwDocexec.getMessage());
      }

      docPdf.close();
      com.afrisoftech.lib.PDFRenderer.renderPDF(tempFile);

    } catch (java.io.IOException IOexec) {

      javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), IOexec.getMessage());
    }
  }
  public void generatePdf() {

    java.lang.Object[][] rangeDates = ageingSeries.getAgeingDateSeries();

    // ageingDates = ageingSeries.getAgeingDateSeries();

    double columnTotals[] = new double[rangeDates.length];

    java.lang.Process wait_for_Pdf2Show;

    java.util.Calendar cal = java.util.Calendar.getInstance();

    java.util.Date dateStampPdf = cal.getTime();

    java.lang.String pdfDateStamp = dateStampPdf.toString();

    int interval = 0;

    try {

      java.io.File tempFile =
          java.io.File.createTempFile("REP" + this.getDateLable() + "_", ".pdf");

      tempFile.deleteOnExit();

      java.lang.Runtime rt = java.lang.Runtime.getRuntime();

      java.lang.String debitTotal = null;

      java.lang.String creditTotal = null;

      com.lowagie.text.Document docPdf = new com.lowagie.text.Document();

      java.util.Calendar calendar = java.util.Calendar.getInstance();

      long dateNow = calendar.getTimeInMillis();

      java.sql.Date datenowSql = new java.sql.Date(dateNow);

      System.out.println(datenowSql.toString());

      try {

        try {

          com.lowagie.text.pdf.PdfWriter.getInstance(
              docPdf, new java.io.FileOutputStream(tempFile));

          String compName = null;
          String date = null;
          try {

            //   java.sql.Connection conDb =
            // java.sql.DriverManager.getConnection("jdbc:postgresql://localhost:5432/sako","postgres","pilsiner");

            java.sql.Statement st3 = connectDB.createStatement();
            java.sql.Statement st4 = connectDB.createStatement();

            java.sql.ResultSet rset2 =
                st3.executeQuery("SELECT hospital_name from pb_hospitalprofile");
            java.sql.ResultSet rset4 = st4.executeQuery("SELECT date('now') as Date");
            while (rset2.next()) compName = rset2.getObject(1).toString();

            while (rset4.next()) date = rset4.getObject(1).toString();

            com.lowagie.text.HeaderFooter headerFoter =
                new com.lowagie.text.HeaderFooter(
                    new Phrase("" + compName, pFontHeader2),
                    false); // FontFactory.getFont(com.lowagie.text.FontFactory.HELVETICA, 14,
                            // Font.BOLDITALIC,java.awt.Color.blue)));

            //  com.lowagie.text.HeaderFooter headerFoter = new com.lowagie.text.HeaderFooter(new
            // Phrase(""+compName+""),false);//
            // FontFactory.getFont(com.lowagie.text.FontFactory.HELVETICA, 14,
            // Font.BOLDITALIC,java.awt.Color.blue)));
            headerFoter.setAlignment(com.lowagie.text.HeaderFooter.ALIGN_CENTER);
            docPdf.setHeader(headerFoter);

          } catch (java.sql.SQLException SqlExec) {

            javax.swing.JOptionPane.showMessageDialog(
                new javax.swing.JFrame(), SqlExec.getMessage());
          }

          com.lowagie.text.HeaderFooter footer =
              new com.lowagie.text.HeaderFooter(
                  new Phrase("Ageing  Page: ", pFontHeader),
                  true); // FontFactory.getFont(com.lowagie.text.FontFactory.HELVETICA, 12,
                         // Font.BOLDITALIC,java.awt.Color.blue));

          docPdf.setFooter(footer);

          docPdf.open();

          double Totals = 0.00;
          double OS = 0.00;
          try {

            com.lowagie.text.pdf.PdfPTable table =
                new com.lowagie.text.pdf.PdfPTable(rangeDates.length + 3);

            String headerWidths = null;

            java.util.Vector headerVector = new java.util.Vector(1, 1);

            int z = rangeDates.length;

            int headerwidths[] = {35, 15, 15, 10, 10};

            table.setWidths(headerwidths);

            table.setWidthPercentage((107));

            table.setHeaderRows(2);

            table.getDefaultCell().setBorder(Rectangle.BOTTOM);

            table.getDefaultCell().setColspan(5);
            Phrase phrase = new Phrase("");
            table.getDefaultCell().setColspan(3);
            try {
              java.text.DateFormat dateFormat =
                  java.text.DateFormat.getDateInstance(java.text.DateFormat.MEDIUM); // MEDIUM);

              java.util.Date endDate2 = dateFormat.parse(endDate.toLocaleString());

              phrase =
                  new Phrase("Weekly Sales Comparison " + dateFormat.format(endDate2), pFontHeader);

              table.addCell(phrase);
            } catch (java.text.ParseException psExec) {

              javax.swing.JOptionPane.showMessageDialog(
                  new javax.swing.JFrame(), psExec.getMessage());
            }

            table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
            table.getDefaultCell().setColspan(2);
            phrase = new Phrase("Printed on : " + date, pFontHeader);
            table.addCell(phrase);

            table.getDefaultCell().setColspan(1);

            table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
            phrase = new Phrase("DESCRIPTION", pFontHeader);
            table.addCell(phrase);
            table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_CENTER);
            for (int x = 0; x < rangeDates.length; x++) {

              int days = 1;
              try {

                // Date parser

                java.text.SimpleDateFormat dateFormat =
                    new java.text.SimpleDateFormat("EEE MMM dd HH:mm:ss zzz yyyy");
                com.afrisoftech.lib.DateFormatter dateFormatter =
                    new com.afrisoftech.lib.DateFormatter(
                        dateFormat.parse(rangeDates[x][0].toString().trim()), "w");
                com.afrisoftech.lib.DateFormatter yearFormatterCurrent =
                    new com.afrisoftech.lib.DateFormatter(endDate, "yy");

                java.lang.String monthString = dateFormatter.getDateString();
                java.lang.String yearStringCurrent = yearFormatterCurrent.getDateString();
                // int days = 1;
                //  if (x < 1) {

                com.afrisoftech.lib.DateFormatter dateFormatterCurrent =
                    new com.afrisoftech.lib.DateFormatter(endDate, "w");
                com.afrisoftech.lib.DateFormatter yearFormatterCurrent1 =
                    new com.afrisoftech.lib.DateFormatter(endDate, "yy");

                java.lang.String monthStringCurrent = dateFormatterCurrent.getDateString();
                java.lang.String yearStringCurrent1 = yearFormatterCurrent1.getDateString();

                //      phrase = new Phrase("Week ["+monthStringCurrent+"/"+yearStringCurrent1+"]"
                // ,pFontHeader);
                //  } else {
                //       phrase = new Phrase("Week ["+monthString+"/"+yearStringCurrent+"]",
                // pFontHeader);
                //                                phrase = new Phrase("+ "+x*days +"
                // Month",pFontHeader);
                interval = x;
                //  }

                table.addCell(phrase);

                // Catch java.text.parse exception.

              } catch (java.text.ParseException prs) {
                prs.printStackTrace();
              }
            }
            //  table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
            phrase = new Phrase("Change % (+/-)", pFontHeader);
            table.addCell(phrase);
            phrase = new Phrase("Change In Reporting Rate", pFontHeader);
            table.addCell(phrase);

            table.getDefaultCell().setBackgroundColor(java.awt.Color.WHITE);
            // table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);

            try {

              double GrandTotal = 0.00;
              double Over120Total = 0.00;
              double TurnOver = 0.00;
              java.lang.Object[] listofAct = this.getListofActivities();

              //    java.sql.Connection conDb1 =
              // java.sql.DriverManager.getConnection("jdbc:postgresql://localhost:5432/sako","postgres","pilsiner");

              System.out.println(listofAct.length);
              double Over120 = 0.00;
              for (int i = 0; i < listofAct.length; i++) {

                double TotalCount = 0.00;

                table.getDefaultCell().setColspan(1);

                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
                table.getDefaultCell().setColspan(1);
                java.sql.Statement stmta1 = connectDB.createStatement();
                java.sql.PreparedStatement pSeta1 =
                    connectDB.prepareStatement(
                        "SELECT count(activity_code) FROM ac_ledger where  activity_code = '"
                            + listofAct[i]
                            + "'");
                java.sql.PreparedStatement pset22 =
                    connectDB.prepareStatement(
                        "select activity from pb_activity WHERE code = ?"); // < '"+endDate+"'::date
                                                                            // and date >
                                                                            // '"+endDate+"'::date -
                                                                            // 30 group by dealer");
                // java.sql.PreparedStatement pset22 = connectDB.prepareStatement("select
                // description from ac_ledger WHERE activity_code = ?");//< '"+endDate+"'::date and
                // date > '"+endDate+"'::date - 30 group by dealer");

                pset22.setString(1, listofAct[i].toString().toUpperCase());
                java.sql.ResultSet rSeta1 = pSeta1.executeQuery();
                while (rSeta1.next()) {
                  name = rSeta1.getInt(1);
                }
                if (name > 0) {
                  java.sql.ResultSet rset22 = pset22.executeQuery();

                  while (rset22.next()) {

                    table.getDefaultCell().setColspan(1);
                    //  table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
                    phrase =
                        new Phrase(
                            dbObject.getDBObject(rset22.getObject(1).toString().toUpperCase(), "-"),
                            pFontHeader1);
                    table.addCell(phrase);
                  }

                  java.sql.Statement st2 = connectDB.createStatement();
                  java.sql.Statement st21 = connectDB.createStatement();
                  java.sql.Statement st22 = connectDB.createStatement();
                  java.sql.Statement st23 = connectDB.createStatement();
                  java.sql.Statement st211 = connectDB.createStatement();
                  java.sql.Statement st221 = connectDB.createStatement();
                  java.sql.Statement st231 = connectDB.createStatement();
                  java.sql.Statement st2A = connectDB.createStatement();
                  java.sql.Statement st2B = connectDB.createStatement();
                  java.sql.Statement st2C = connectDB.createStatement();
                  java.sql.Statement stc = connectDB.createStatement();
                  System.out.println("Dealer Is : [" + listofAct[i] + "].");

                  java.sql.PreparedStatement pset1 =
                      connectDB.prepareStatement(
                          "select sum(credit-debit) from ac_ledger WHERE activity_code = ?  AND date = '"
                              + rangeDates[rangeDates.length - 1][0]
                              + "'"); // AND '"+rangeDates[rangeDates.length - 1][1]+"'");//<
                                      // '"+endDate+"'::date and date > '"+endDate+"'::date - 30
                                      // group by dealer");
                  pset1.setString(1, listofAct[i].toString());
                  java.sql.ResultSet rset1 = pset1.executeQuery();

                  //    java.sql.PreparedStatement pset111 = connectDB.prepareStatement("select
                  // sum(credit-debit) from ac_ledger WHERE activity_code = ?  AND date <
                  // '"+endDate+"'");//< '"+endDate+"'::date and date > '"+endDate+"'::date - 30
                  // group by dealer");
                  //  pset111.setString(1,listofAct[i].toString());
                  //   java.sql.ResultSet rset111 = pset111.executeQuery();
                  //   java.sql.Statement st02 = connectDB.createStatement();

                  //   java.sql.PreparedStatement pset112 = connectDB.prepareStatement("select
                  // sum(credit-debit) from ac_ledger WHERE activity_code = ? AND date <
                  // '"+endDate+"'");//< '"+endDate+"'::date and date > '"+endDate+"'::date - 30
                  // group by dealer");
                  //   pset112.setString(1,listofAct[i].toString());
                  //   java.sql.ResultSet rset112 = pset112.executeQuery();

                  for (int t = 0; t < rangeDates.length; t++) {
                    java.sql.Statement st01 = connectDB.createStatement();
                    java.sql.PreparedStatement pset =
                        connectDB.prepareStatement(
                            "select sum(credit-debit) from ac_ledger WHERE activity_code = ?  AND date between '"
                                + rangeDates[t][0]
                                + "' AND '"
                                + rangeDates[t][1]
                                + "'"); // < '"+endDate+"'::date and date > '"+endDate+"'::date - 30
                                        // group by dealer");
                    pset.setString(1, listofAct[i].toString().toUpperCase());
                    java.sql.ResultSet rset = pset.executeQuery();

                    while (rset.next()) {

                      table.getDefaultCell().setColspan(1);
                      table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
                      phrase =
                          new Phrase(
                              new com.afrisoftech.sys.Format2Currency()
                                  .Format2Currency(rset.getString(1)),
                              pFontHeader1);
                      table.addCell(phrase);

                      columnTotals[t] = columnTotals[t] + rset.getDouble(1);
                      TotalCount = TotalCount + rset.getDouble(1);
                      TurnOver = rset.getDouble(1);
                    }
                  }
                  // phrase = new Phrase(new
                  // com.afrisoftech.sys.Format2Currency().Format2Currency(java.lang.String.valueOf(TotalCount)), pFontHeader);
                  //  table.addCell(phrase);
                  while (rset1.next()) {
                    Over120 = rset1.getDouble(1);

                    if (Over120 > 0) {

                      phrase =
                          new Phrase(
                              new com.afrisoftech.sys.Format2Currency()
                                  .Format2Currency(
                                      java.lang.String.valueOf((Over120 / TurnOver) * 100)),
                              pFontHeader);
                      table.addCell(phrase);
                      phrase = new Phrase("+", pFontHeader);
                      table.addCell(phrase);

                    } else {

                      phrase = new Phrase(java.lang.String.valueOf(0.00), pFontHeader);
                      table.addCell(phrase);
                      phrase = new Phrase("", pFontHeader);
                      table.addCell(phrase);
                    }
                  }
                  /*                                     }else{
                      if (TotalCount <= 0 && Over120 > 0){

                          phrase = new Phrase(java.lang.String.valueOf((Over120/TurnOver)*100), pFontHeader);
                          table.addCell(phrase);
                          phrase = new Phrase("+", pFontHeader);
                          table.addCell(phrase);

                      }else{
                          if (TotalCount > 0 && Over120 < 0){

                              phrase = new Phrase(java.lang.String.valueOf((Over120/TurnOver)*100), pFontHeader);
                              table.addCell(phrase);
                              phrase = new Phrase("-", pFontHeader);
                              table.addCell(phrase);
                          }else{
                              if (TotalCount > 0 && Over120 == 0){

                                  phrase = new Phrase(java.lang.String.valueOf((Over120/TurnOver)*100), pFontHeader);
                                  table.addCell(phrase);
                                  phrase = new Phrase("-", pFontHeader);
                                  table.addCell(phrase);
                              }else{
                                  if (TotalCount < 0 && Over120 < 0){

                                      phrase = new Phrase(java.lang.String.valueOf((Over120/TurnOver)*100), pFontHeader);
                                      table.addCell(phrase);
                                      phrase = new Phrase("-", pFontHeader);
                                      table.addCell(phrase);
                                  }else{

                                      phrase = new Phrase(java.lang.String.valueOf(0.00), pFontHeader);
                                      table.addCell(phrase);
                                      phrase = new Phrase("", pFontHeader);
                                      table.addCell(phrase);
                                  }
                              }
                          }
                      }
                  }*/
                  // }
                }
              }

              table.getDefaultCell().setBorderColor(java.awt.Color.BLACK);

              table.getDefaultCell().setBorder(Rectangle.BOTTOM | Rectangle.TOP);

              // while (rsetTotals.next()) {

              table.getDefaultCell().setColspan(1);

              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
              phrase = new Phrase("Total", pFontHeader);

              table.addCell(phrase);

              table.getDefaultCell().setColspan(1);

              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);

              // phrase = new Phrase(new
              // com.afrisoftech.sys.Format2Currency().Format2Currency(rsetTotals.getString(1)),
              // pFontHeader);
              for (int x = 0; x < rangeDates.length; x++) {
                // phrase = new Phrase("Current"+2*x,pFontHeader);
                // table.addCell(phrase);

                phrase =
                    new Phrase(
                        new com.afrisoftech.sys.Format2Currency()
                            .Format2Currency(java.lang.String.valueOf(columnTotals[x])),
                        pFontHeader);

                table.addCell(phrase);
              }
              phrase = new Phrase(" ", pFontHeader);
              table.addCell(phrase);
              table.addCell(phrase);
              phrase =
                  new Phrase(
                      new com.afrisoftech.sys.Format2Currency()
                          .Format2Currency(java.lang.String.valueOf(Over120Total)),
                      pFontHeader);

              // table.addCell(phrase);
              phrase =
                  new Phrase(
                      new com.afrisoftech.sys.Format2Currency()
                          .Format2Currency(java.lang.String.valueOf(OS)),
                      pFontHeader);

              //  table.addCell(phrase);

              docPdf.add(table);

            } catch (java.sql.SQLException SqlExec) {

              SqlExec.printStackTrace();

              javax.swing.JOptionPane.showMessageDialog(
                  new javax.swing.JFrame(), SqlExec.getMessage());
            }

          } catch (com.lowagie.text.BadElementException BadElExec) {

            // Bad

            javax.swing.JOptionPane.showMessageDialog(
                new javax.swing.JFrame(), BadElExec.getMessage());
          }

        } catch (java.io.FileNotFoundException fnfExec) {

          javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), fnfExec.getMessage());
        }
      } catch (com.lowagie.text.DocumentException lwDocexec) {

        javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), lwDocexec.getMessage());
      }

      docPdf.close();

      try {

        if (System.getProperty("os.name").equalsIgnoreCase("Linux")) {

          System.out.println(tempFile);

          wait_for_Pdf2Show = rt.exec("kghostview " + tempFile + "");

          wait_for_Pdf2Show.waitFor();

        } else {

          wait_for_Pdf2Show =
              rt.exec("c:/Program Files/Adobe/Acrobat 5.0/Reader/AcroRd32.exe " + tempFile);

          wait_for_Pdf2Show.waitFor();
        }

      } catch (java.lang.InterruptedException intrExec) {

        javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), intrExec.getMessage());
      }

    } catch (java.io.IOException IOexec) {

      javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), IOexec.getMessage());
    }
  }
  public void generatePdf(java.lang.String memNo) {
    rangeDates = ageingSeries.getAgeingDateSeries();

    double columnTotals[] = new double[rangeDates.length];
    // java.lang.Object[][] rangeDates = periodicDates.getMonthlyDates();

    // double columnTotals[] = new double[rangeDates.length];

    java.lang.Process wait_for_Pdf2Show;

    java.util.Calendar cal = java.util.Calendar.getInstance();

    java.util.Date dateStampPdf = cal.getTime();

    java.lang.String pdfDateStamp = dateStampPdf.toString();

    int interval = 0;
    try {

      java.io.File tempFile =
          java.io.File.createTempFile("REP" + this.getDateLable() + "_", ".pdf");

      tempFile.deleteOnExit();

      java.lang.Runtime rt = java.lang.Runtime.getRuntime();

      java.lang.String debitTotal = null;

      java.lang.String creditTotal = null;

      com.lowagie.text.Document docPdf = new com.lowagie.text.Document(PageSize.A4.rotate());

      double osBalance = 0.00;
      double current = 0.00;
      try {

        try {

          com.lowagie.text.pdf.PdfWriter.getInstance(
              docPdf, new java.io.FileOutputStream(tempFile));

          String compName = null;
          String date = null;
          String Messg = null;

          docPdf.open();

          try {

            java.util.Calendar calendar = java.util.Calendar.getInstance();

            long dateNow = calendar.getTimeInMillis();

            java.sql.Date datenowSql = new java.sql.Date(dateNow);

            System.out.println(datenowSql.toString());

            //  java.lang.Object listofStaffNos[] = this.getListofStaffNos();

            com.lowagie.text.pdf.PdfPTable table1 = new com.lowagie.text.pdf.PdfPTable(7);
            //  com.lowagie.text.Table table = new com.lowagie.text.Table(7);

            // table.endHeaders();

            int headerwidths[] = {15, 15, 30, 15, 15, 15, 15};

            table1.setWidths(headerwidths);
            //  if (docPdf.getPageNumber() > 1) {
            //  table1.setHeaderRows(4);
            //  }
            table1.setWidthPercentage((100));

            table1.getDefaultCell().setBorder(Rectangle.BOTTOM);

            table1.getDefaultCell().setColspan(7);

            Phrase phrase = new Phrase();

            try {

              table1.getDefaultCell().setBorder(Rectangle.BOX);
              table1.getDefaultCell().setBorderColor(java.awt.Color.BLACK);
              table1.getDefaultCell().setColspan(2);
              table1.getDefaultCell().setFixedHeight(70);
              table1.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_CENTER);
              table1.addCell(Image.getInstance(com.afrisoftech.lib.CompanyLogo.getPath2Logo()));
              table1.getDefaultCell().setFixedHeight(16);
              java.sql.PreparedStatement st321 =
                  connectDB.prepareStatement("select header_name from pb_header");
              java.sql.ResultSet rset3 = st321.executeQuery();
              table1.getDefaultCell().setBorder(Rectangle.BOX);
              table1.getDefaultCell().setBorderColor(java.awt.Color.BLACK);
              while (rset3.next()) {
                table1.getDefaultCell().setColspan(5);

                table1.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_CENTER);
                phrase = new Phrase(rset3.getObject(1).toString().toUpperCase(), pFontHeader1);
                table1.addCell(phrase);
              }

              //  table.addCell(phrase);

              table1.getDefaultCell().setColspan(1);
              table1.getDefaultCell().setBackgroundColor(java.awt.Color.WHITE);
              table1.getDefaultCell().setBorderColor(java.awt.Color.WHITE);

              //         try {
              java.sql.Statement st3 = connectDB.createStatement();
              java.sql.Statement st2x = connectDB.createStatement();

              java.sql.ResultSet rset2x =
                  st2x.executeQuery("SELECT rep_currency from pb_hospitalprofile");
              while (rset2x.next()) {
                ks = rset2x.getObject(1).toString();
              }

            } catch (java.sql.SQLException SqlExec) {

              SqlExec.printStackTrace();

              javax.swing.JOptionPane.showMessageDialog(
                  new javax.swing.JFrame(), SqlExec.getMessage());
            }
            docPdf.add(table1);
          } catch (com.lowagie.text.BadElementException BadElExec) {

            javax.swing.JOptionPane.showMessageDialog(
                new javax.swing.JFrame(), BadElExec.getMessage());
          }

          try {

            java.util.Calendar calendar = java.util.Calendar.getInstance();

            long dateNow = calendar.getTimeInMillis();

            java.sql.Date datenowSql = new java.sql.Date(dateNow);

            System.out.println(datenowSql.toString());

            //       java.lang.Object listofStaffNos[] = this.getListofStaffNos();

            com.lowagie.text.pdf.PdfPTable table = new com.lowagie.text.pdf.PdfPTable(11);
            //  com.lowagie.text.Table table = new com.lowagie.text.Table(7);

            // table.endHeaders();

            int headerwidths[] = {12, 12, 30, 10, 12, 12, 12, 12, 13, 13, 15};

            table.setWidths(headerwidths);
            //  if (docPdf.getPageNumber() > 1) {
            table.setHeaderRows(8);
            //  }
            table.setWidthPercentage((100));

            table.getDefaultCell().setBorder(Rectangle.BOTTOM);

            table.getDefaultCell().setColspan(8);

            Phrase phrase = new Phrase();

            //  table.addCell(phrase);

            table.getDefaultCell().setColspan(1);
            table.getDefaultCell().setBackgroundColor(java.awt.Color.WHITE);
            table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);

            try {
              java.sql.Statement st22 = connectDB.createStatement();
              java.sql.Statement st11 = connectDB.createStatement();
              java.sql.Statement st = connectDB.createStatement();
              java.sql.Statement st1 = connectDB.createStatement();
              java.sql.Statement st2 = connectDB.createStatement();
              java.sql.Statement st3 = connectDB.createStatement();
              java.sql.Statement st4 = connectDB.createStatement();
              java.sql.Statement st41 = connectDB.createStatement();
              java.sql.Statement st5 = connectDB.createStatement();
              java.sql.ResultSet rset3 =
                  st3.executeQuery(
                      "select hospital_name,postal_code||' '||box_no||' '||town,main_telno||' '||other_telno,initcap(street),main_faxno,email,website,room_no from pb_hospitalprofile");
              java.sql.ResultSet rset4 =
                  st4.executeQuery(
                      "select scheme_name,account_no,tel_main,address from ac_schemes where account_no = '"
                          + memNo1
                          + "' ORDER BY 1 ASC");
              java.sql.ResultSet rset41 =
                  st41.executeQuery(
                      "select payer_name from ac_schemes where account_no = '"
                          + memNo1
                          + "' ORDER BY 1 ASC");
              java.sql.ResultSet rset1 =
                  st1.executeQuery(
                      "select date,admission_no, upper(item),invoice_no ||' '||receipt_no,journal_no,debit,balance-credit_bal, (select distinct f_name || ' '|| l_name from secure_menu_access where login_name = ac_debtors.user_name limit 1), reference_no from ac_debtors where account_no = '"
                          + memNo1
                          + "' AND date::date BETWEEN '"
                          + beginDate
                          + "' AND '"
                          + endDate
                          + "' and (balance > 0 or credit_bal > 0) order by date,invoice_no"); // UNION select pd.date::date,initcap(pd.scheme_staff_no), (sh.first_name||' '||sh.second_name||' '||sh.last_name) as name,pd.reference,sum(pd.credit),pd.patient_no from hp_patient_card pd,hp_inpatient_register sh where pd.patient_no = sh.patient_no and pd.isurer = '"+memNo+"' AND pd.date::date BETWEEN '"+beginDate+"' AND '"+endDate+"' and pd.credit > 0 group by pd.date::date,pd.scheme_staff_no, name,pd.reference,pd.invoice_no,pd.patient_no order by pd.invoice_no");
              java.sql.ResultSet rsetTotals2 =
                  st22.executeQuery(
                      "select sum(balance-credit_bal) from ac_debtors where account_no = '"
                          + memNo1
                          + "' AND date::date < '"
                          + beginDate
                          + "'");

              table.getDefaultCell().setBorderColor(java.awt.Color.BLACK);
              table.getDefaultCell().setBorderWidth(Rectangle.TOP | Rectangle.BOTTOM);

              table.getDefaultCell().setColspan(11);
              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_CENTER);
              phrase = new Phrase("Statement of Account", pFontHeader11);
              table.addCell(phrase);

              while (rset4.next()) {
                table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);
                table.getDefaultCell().setColspan(11);
                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
                phrase = new Phrase(dbObject.getDBObject(rset4.getObject(1), "-"), pFontHeader1);
                table.addCell(phrase);

                while (rset41.next()) {
                  table.getDefaultCell().setColspan(11);

                  table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
                  phrase = new Phrase(dbObject.getDBObject(rset41.getObject(1), "-"), pFontHeader1);
                  table.addCell(phrase);
                }
                table.getDefaultCell().setColspan(11);

                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
                phrase =
                    new Phrase(
                        "Account No : " + dbObject.getDBObject(rset4.getObject(2), "-"),
                        pFontHeader1);
                table.addCell(phrase);
                phrase =
                    new Phrase(
                        dbObject.getDBObject("Tel : " + rset4.getObject(3), "-"), pFontHeader1);
                table.addCell(phrase);
                phrase = new Phrase(dbObject.getDBObject(rset4.getObject(4), "-"), pFontHeader1);
                table.addCell(phrase);
              }
              table.getDefaultCell().setColspan(5);

              try {
                java.text.DateFormat dateFormat =
                    java.text.DateFormat.getDateInstance(java.text.DateFormat.MEDIUM); // MEDIUM);

                java.util.Date endDate1 =
                    dateFormat.parse(endDate.toLocaleString()); // dateInstance.toLocaleString());
                java.util.Date endDate11 =
                    dateFormat.parse(beginDate.toLocaleString()); // dateInstance.toLocaleString());

                System.out.println("" + endDate1);
                //  phrase = new Phrase(bank +" Report: " +dateFormat.format(formattedDate),
                // pFontHeader);

                //  table.addCell(phrase);

                phrase =
                    new Phrase(
                        "Period : "
                            + dateFormat.format(endDate11)
                            + " - "
                            + dateFormat.format(endDate1),
                        pFontHeader1);

                table.addCell(phrase);
              } catch (java.text.ParseException psExec) {

                javax.swing.JOptionPane.showMessageDialog(
                    new javax.swing.JFrame(), psExec.getMessage());
              }
              // table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
              // phrase = new Phrase("Period : '"+beginDate+"' - '"+endDate+"'", pFontHeader1);
              // table.addCell(phrase);

              table.getDefaultCell().setColspan(6);

              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
              phrase = new Phrase("Date " + datenowSql, pFontHeader1);
              table.addCell(phrase);

              table.getDefaultCell().setColspan(3);

              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
              table.getDefaultCell().setBorderColor(java.awt.Color.BLACK);
              table.getDefaultCell().setBorderWidth(Rectangle.TOP);
              table.getDefaultCell().setColspan(1);
              phrase = new Phrase("Date", pFontHeader1);
              table.addCell(phrase);
              table.getDefaultCell().setColspan(1);
              phrase = new Phrase("Patient No.", pFontHeader1);
              table.addCell(phrase);
              table.getDefaultCell().setColspan(1);
              phrase = new Phrase("Name", pFontHeader1);
              table.addCell(phrase);

              table.getDefaultCell().setColspan(1);
              phrase = new Phrase("Invoice No.", pFontHeader1);
              table.addCell(phrase);
              phrase = new Phrase("Member No.", pFontHeader1);
              table.addCell(phrase);
              table.getDefaultCell().setColspan(1);
              phrase = new Phrase("Claim No..", pFontHeader1);
              table.addCell(phrase);
              phrase = new Phrase("Ward Name", pFontHeader1);
              table.addCell(phrase);
              phrase = new Phrase("Prepared By", pFontHeader1);
              table.addCell(phrase);
              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);

              table.getDefaultCell().setColspan(1);
              phrase = new Phrase("Orig. Amt ", pFontHeader1);
              table.addCell(phrase);
              table.getDefaultCell().setColspan(1);
              phrase = new Phrase("Curr. Amt ", pFontHeader1);
              table.addCell(phrase);
              table.getDefaultCell().setColspan(1);
              phrase = new Phrase("Running Amt. " + ks, pFontHeader1);
              table.addCell(phrase);
              // table
              double osBalancex = 0.00;
              double osBalancex1 = 0.00;

              while (rsetTotals2.next()) {
                table.getDefaultCell().setColspan(5);

                table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);

                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
                phrase = new Phrase("BAL/BF", pFontHeader1);
                table.addCell(phrase);
                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
                table.getDefaultCell().setColspan(6);
                phrase =
                    new Phrase(
                        new com.afrisoftech.sys.Format2Currency()
                            .Format2Currency(rsetTotals2.getString(1)),
                        pFontHeader1);
                osBalance = osBalance + rsetTotals2.getDouble(1);
                table.addCell(phrase);
              }
              while (rset1.next()) {
                table.getDefaultCell().setColspan(1);
                table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);

                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
                phrase = new Phrase(dbObject.getDBObject(rset1.getObject(1), "-"), pFontHeader);

                table.addCell(phrase);
                table.getDefaultCell().setColspan(1);

                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
                phrase = new Phrase(dbObject.getDBObject(rset1.getObject(2), "-"), pFontHeader);

                table.addCell(phrase);
                table.getDefaultCell().setColspan(1);
                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
                phrase = new Phrase(dbObject.getDBObject(rset1.getObject(3), "-"), pFontHeader);

                table.addCell(phrase);

                table.getDefaultCell().setColspan(1);
                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
                phrase = new Phrase(dbObject.getDBObject(rset1.getObject(4), "-"), pFontHeader);

                table.addCell(phrase);
                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
                phrase = new Phrase(dbObject.getDBObject(rset1.getObject(5), "-"), pFontHeader);

                table.addCell(phrase);
                phrase = new Phrase(dbObject.getDBObject(rset1.getObject(9), "-"), pFontHeader);

                table.addCell(phrase);

                java.sql.PreparedStatement pstmt =
                    connectDB.prepareStatement(
                        "SELECT distinct main_service from hp_patient_card where patient_no = ? AND service ilike '%daily bed%' order by main_service desc limit 1");
                pstmt.setString(1, rset1.getString(2));
                java.sql.ResultSet rsets = pstmt.executeQuery();
                String wardName = "-";
                // System.out.println("Printing visit_id number ");
                while (rsets.next()) {

                  System.out.println("Ward name = [" + rsets.getString(1) + "]");
                  wardName = rsets.getString(1);
                }
                rsets.close();
                pstmt.close();
                phrase = new Phrase(wardName, pFontHeader);
                table.addCell(phrase);

                phrase =
                    new Phrase(
                        dbObject.getDBObject(rset1.getObject(8), "-").toUpperCase(), pFontHeader);

                table.addCell(phrase);

                table.getDefaultCell().setColspan(1);
                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);

                phrase =
                    new Phrase(
                        new com.afrisoftech.sys.Format2Currency()
                            .Format2Currency(rset1.getString(6)),
                        pFontHeader);

                table.addCell(phrase);
                osBalancex = osBalancex + rset1.getDouble(6);
                phrase =
                    new Phrase(
                        new com.afrisoftech.sys.Format2Currency()
                            .Format2Currency(rset1.getString(7)),
                        pFontHeader);
                osBalancex1 = osBalancex1 + rset1.getDouble(7);
                osBalance = osBalance + rset1.getDouble(7);
                table.addCell(phrase);
                phrase =
                    new Phrase(
                        new com.afrisoftech.sys.Format2Currency()
                            .Format2Currency(java.lang.String.valueOf(osBalance)),
                        pFontHeader);

                table.addCell(phrase);
              }
              table.getDefaultCell().setColspan(2);

              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
              phrase = new Phrase("", pFontHeader);

              table.addCell(phrase);
              table.getDefaultCell().setBorderColor(java.awt.Color.BLACK);

              table.getDefaultCell().setBorder(Rectangle.BOTTOM | Rectangle.TOP);

              //  while (rsetTotals.next()) {

              table.getDefaultCell().setColspan(6);

              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
              phrase = new Phrase("Total", pFontHeader);

              table.addCell(phrase);

              table.getDefaultCell().setColspan(1);

              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);

              phrase =
                  new Phrase(
                      new com.afrisoftech.sys.Format2Currency()
                          .Format2Currency(java.lang.String.valueOf(osBalancex)),
                      pFontHeader);

              table.addCell(phrase);

              phrase =
                  new Phrase(
                      new com.afrisoftech.sys.Format2Currency()
                          .Format2Currency(java.lang.String.valueOf(osBalancex1)),
                      pFontHeader);

              table.addCell(phrase);
              phrase =
                  new Phrase(
                      new com.afrisoftech.sys.Format2Currency()
                          .Format2Currency(java.lang.String.valueOf(osBalance)),
                      pFontHeader);

              table.addCell(phrase);

              table.getDefaultCell().setColspan(6);

              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
              phrase = new Phrase("     ''  ", pFontHeader);

              table.addCell(phrase);

              docPdf.add(table);

              double Totals = 0.00;

              try {

                com.lowagie.text.pdf.PdfPTable table12 =
                    new com.lowagie.text.pdf.PdfPTable(rangeDates.length + 4);

                String headerWidths = null;

                java.util.Vector headerVector = new java.util.Vector(1, 1);

                int z = rangeDates.length;

                int headerwidth[] = {22, 13, 13, 13, 13, 13, 13, 13}; // ,13,13};

                table12.setWidths(headerwidth);

                table12.setWidthPercentage((100));

                table12.setHeaderRows(2);

                table12.getDefaultCell().setBorder(Rectangle.BOTTOM);
                //
                table.getDefaultCell().setColspan(8);
                Phrase phraseA = new Phrase("");

                table12.getDefaultCell().setColspan(1);

                phraseA = new Phrase("AGEING DETAILS", pFontHeader1);
                table12.addCell(phraseA);

                interval = 0;
                for (int x = rangeDates.length - 1; x >= 0; x--) {
                  table12.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);

                  try {

                    // Date parser

                    table12.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);

                    java.text.SimpleDateFormat dateFormat =
                        new java.text.SimpleDateFormat("EEE MMM dd HH:mm:ss zzz yyyy");
                    com.afrisoftech.lib.DateFormatter dateFormatter =
                        new com.afrisoftech.lib.DateFormatter(
                            dateFormat.parse(rangeDates[x][0].toString().trim()), "MMM/yy");

                    java.lang.String monthString = dateFormatter.getDateString();

                    int days = 1;

                    phraseA = new Phrase(monthString, pFontHeader1);
                    table12.addCell(phraseA);

                  } catch (java.text.ParseException prs) {
                    prs.printStackTrace();
                  }

                  interval++;
                }
                phraseA = new Phrase("+ " + (interval * 30) + " Days", pFontHeader1);
                table12.addCell(phraseA);

                phraseA = new Phrase("Un Alloc.", pFontHeader1);
                table12.addCell(phraseA);

                phraseA = new Phrase("O/S " + ks, pFontHeader1);
                table12.addCell(phraseA);

                table12.getDefaultCell().setBackgroundColor(java.awt.Color.WHITE);
                table12.getDefaultCell().setBorderColor(java.awt.Color.WHITE);

                try {

                  double GrandTotal = 0.00;
                  double Over120Total = 0.00;
                  java.lang.Object[] listofAct = this.getListofActivities();

                  //    java.sql.Connection conDb1 =
                  // java.sql.DriverManager.getConnection("jdbc:postgresql://localhost:5432/sako","postgres","pilsiner");

                  System.out.println(listofAct.length);

                  //  for (int i = 0; i < listofAct.length; i++) {
                  double TurnOver = 0.00;
                  double Over120 = 0.00;
                  double TotalCount = 0.00;

                  table12.getDefaultCell().setColspan(1);
                  java.sql.Statement stmta1 = connectDB.createStatement();
                  java.sql.PreparedStatement pSeta1 =
                      connectDB.prepareStatement(
                          "SELECT count(distinct(account_no)) FROM ac_debtors where account_no IS NOT NULL and account_no ='"
                              + memNo1
                              + "'");
                  java.sql.PreparedStatement pset22 =
                      connectDB.prepareStatement(
                          "select distinct account_no||' '||scheme_name from ac_schemes WHERE account_no = '"
                              + memNo1
                              + "' "); // < '"+endDate+"'::date and date > '"+endDate+"'::date - 30
                                       // group by dealer");
                  //   pset22.setString(1,""+memNo1.toUpperCase());
                  java.sql.ResultSet rSeta1 = pSeta1.executeQuery();
                  while (rSeta1.next()) {
                    name = rSeta1.getInt(1);
                  }
                  if (name > 0) {
                    java.sql.ResultSet rset22 = pset22.executeQuery();

                    while (rset22.next()) {

                      table12.getDefaultCell().setColspan(1);
                      //     phraseA = new Phrase(dbObject.getDBObject(rset22.getObject(1),
                      // "-"),pFontHeader1);
                      phraseA = new Phrase(dbObject.getDBObject(" ", "-"), pFontHeader1);

                      table12.addCell(phraseA);
                      // columnTotals[t] = columnTotals[t] + rset.getDouble(1);

                    }
                  } else {
                    phraseA = new Phrase("-", pFontHeader1);
                    table12.addCell(phraseA);
                  }
                  // java.sql.Statement st2 = connectDB.createStatement();

                  table12.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);

                  java.sql.Statement st2B = connectDB.createStatement();
                  java.sql.Statement st21 = connectDB.createStatement();
                  java.sql.Statement st22A = connectDB.createStatement();
                  java.sql.Statement st23 = connectDB.createStatement();
                  java.sql.Statement st211 = connectDB.createStatement();
                  java.sql.Statement st221 = connectDB.createStatement();
                  java.sql.Statement st233 = connectDB.createStatement();
                  java.sql.Statement st2A = connectDB.createStatement();
                  java.sql.Statement st2B1 = connectDB.createStatement();
                  java.sql.Statement st2C = connectDB.createStatement();
                  java.sql.Statement stc = connectDB.createStatement();
                  System.out.println("Dealer Is : '" + memNo1 + "'");
                  java.sql.PreparedStatement pset1 =
                      connectDB.prepareStatement(
                          "select sum(balance) from ac_debtors WHERE account_no = '"
                              + memNo1
                              + "' AND dealer IS NOT NULL AND date < '"
                              + rangeDates[0][0]
                              + "'"); // < '"+endDate+"'::date and date > '"+endDate+"'::date - 30
                                      // group by dealer");
                  // pset1.setString(1,""+memNo1);
                  java.sql.ResultSet rset1A = pset1.executeQuery();

                  java.sql.PreparedStatement pset112 =
                      connectDB.prepareStatement(
                          "select sum(credit_bal) from ac_debtors WHERE account_no = ? AND date <= '"
                              + endDate
                              + "'"); // < '"+endDate+"'::date and date > '"+endDate+"'::date - 30
                                      // group by dealer");
                  pset112.setString(1, memNo1);
                  java.sql.ResultSet rset112 = pset112.executeQuery();

                  for (int t = rangeDates.length - 1; t >= 0; t--) {
                    java.sql.Statement st81 = connectDB.createStatement();
                    java.sql.Statement st01 = connectDB.createStatement();

                    java.sql.ResultSet rset01 =
                        st01.executeQuery(
                            "select count(balance) from ac_debtors WHERE account_no = '"
                                + memNo1
                                + "' AND dealer IS NOT NULL AND date between '"
                                + rangeDates[t][0]
                                + "' AND '"
                                + rangeDates[t][1]
                                + "'");

                    java.sql.PreparedStatement pset =
                        connectDB.prepareStatement(
                            "select sum(balance) from ac_debtors WHERE account_no = '"
                                + memNo1
                                + "' AND dealer IS NOT NULL AND date between '"
                                + rangeDates[t][0]
                                + "' AND '"
                                + rangeDates[t][1]
                                + "'"); // < '"+endDate+"'::date and date > '"+endDate+"'::date - 30
                                        // group by dealer");
                    while (rset01.next()) {
                      over = rset01.getInt(1);
                    }
                    if (t == 3) {
                      java.sql.PreparedStatement pset1x =
                          connectDB.prepareStatement(
                              "select sum(balance) from ac_debtors WHERE account_no = '"
                                  + memNo1
                                  + "' AND dealer IS NOT NULL AND date between '"
                                  + rangeDates[t][0]
                                  + "' AND '"
                                  + endDate
                                  + "'"); // < '"+endDate+"'::date and date > '"+endDate+"'::date -
                                          // 30 group by dealer");
                      System.out.print(rangeDates[t][0] + " AND " + endDate);
                      if (over > 0) {
                        //  pset.setString(1,""+memNo1.toString().toUpperCase());
                        java.sql.ResultSet rset = pset1x.executeQuery();
                        while (rset.next()) {

                          table12.getDefaultCell().setColspan(1);
                          table12.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
                          phraseA =
                              new Phrase(
                                  new com.afrisoftech.sys.Format2Currency()
                                      .Format2Currency(rset.getString(1)),
                                  pFontHeader1);
                          table12.addCell(phraseA);
                          columnTotals[t] = columnTotals[t] + rset.getDouble(1);

                          TotalCount = TotalCount + rset.getDouble(1);
                        }

                      } else {

                        table12.getDefaultCell().setColspan(1);
                        table12.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
                        phraseA =
                            new Phrase(
                                new com.afrisoftech.sys.Format2Currency().Format2Currency("0.00"),
                                pFontHeader1);
                        table12.addCell(phraseA);
                      }
                    } else {
                      if (over > 0) {
                        //  pset.setString(1,""+memNo1.toString().toUpperCase());
                        java.sql.ResultSet rset = pset.executeQuery();
                        while (rset.next()) {

                          table12.getDefaultCell().setColspan(1);
                          table12.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
                          phraseA =
                              new Phrase(
                                  new com.afrisoftech.sys.Format2Currency()
                                      .Format2Currency(rset.getString(1)),
                                  pFontHeader1);
                          table12.addCell(phraseA);
                          columnTotals[t] = columnTotals[t] + rset.getDouble(1);

                          TotalCount = TotalCount + rset.getDouble(1);
                        }

                      } else {

                        table12.getDefaultCell().setColspan(1);
                        table12.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
                        phraseA =
                            new Phrase(
                                new com.afrisoftech.sys.Format2Currency().Format2Currency("0.00"),
                                pFontHeader1);
                        table12.addCell(phraseA);
                      }
                    }
                  }

                  // GrandTotal = GrandTotal + TotalCount;
                  while (rset1A.next()) {
                    System.out.println("This Result " + rset1A.getString(1));
                    table12.getDefaultCell().setColspan(1);
                    table12.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
                    phraseA =
                        new Phrase(
                            new com.afrisoftech.sys.Format2Currency()
                                .Format2Currency(rset1A.getString(1)),
                            pFontHeader1);
                    Over120 = rset1A.getDouble(1);
                    table12.addCell(phraseA);
                    Over120Total = Over120Total + rset1A.getDouble(1);
                    TurnOver = TotalCount + Over120;
                  }

                  while (rset112.next()) {
                    phraseA =
                        new Phrase(
                            new com.afrisoftech.sys.Format2Currency()
                                .Format2Currency(rset112.getString(1)),
                            pFontHeader1);
                    table12.addCell(phraseA);
                    OS = OS + rset112.getDouble(1);
                  }
                  // Over120 = Over120+Over120;
                  phraseA =
                      new Phrase(
                          new com.afrisoftech.sys.Format2Currency()
                              .Format2Currency(java.lang.String.valueOf(TurnOver - OS)),
                          pFontHeader1);
                  table12.addCell(phraseA);
                  Totals = Totals + TurnOver;

                  // }

                  table12.getDefaultCell().setBorderColor(java.awt.Color.BLACK);

                  table12.getDefaultCell().setBorder(Rectangle.BOTTOM | Rectangle.TOP);

                  // while (rsetTotals.next()) {

                  table12.getDefaultCell().setColspan(1);

                  table12.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
                  phraseA = new Phrase("Total", pFontHeader);

                  table12.addCell(phraseA);

                  table12.getDefaultCell().setColspan(1);

                  table12.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);

                  // phrase = new Phrase(new
                  // com.afrisoftech.sys.Format2Currency().Format2Currency(rsetTotals.getString(1)),
                  // pFontHeader);
                  for (int x = rangeDates.length - 1; x >= 0; x--) {
                    // phrase = new Phrase("Current"+2*x,pFontHeader);
                    // table.addCell(phrase);

                    phraseA =
                        new Phrase(
                            new com.afrisoftech.sys.Format2Currency()
                                .Format2Currency(java.lang.String.valueOf(columnTotals[x])),
                            pFontHeader);

                    table12.addCell(phraseA);
                    columnTo = columnTo + columnTotals[x];
                  }

                  phraseA =
                      new Phrase(
                          new com.afrisoftech.sys.Format2Currency()
                              .Format2Currency(java.lang.String.valueOf(Over120Total)),
                          pFontHeader);

                  table12.addCell(phraseA);

                  phraseA =
                      new Phrase(
                          new com.afrisoftech.sys.Format2Currency()
                              .Format2Currency(java.lang.String.valueOf(OS)),
                          pFontHeader);

                  table12.addCell(phraseA);

                  phraseA =
                      new Phrase(
                          new com.afrisoftech.sys.Format2Currency()
                              .Format2Currency(
                                  java.lang.String.valueOf((columnTo + Over120Total) - OS)),
                          pFontHeader);

                  table12.addCell(phraseA);

                  try {

                    java.sql.Statement st31 = connectDB.createStatement();
                    // java.sql.Statement st4 = connectDB.createStatement();
                    java.sql.ResultSet rset2 = st31.executeQuery("select name from pb_notice");

                    while (rset2.next()) {
                      Messg = rset2.getString(1);
                    }
                    com.lowagie.text.HeaderFooter footer1 =
                        new com.lowagie.text.HeaderFooter(
                            new Phrase("" + Messg + ""),
                            false); // FontFactory.getFont(com.lowagie.text.FontFactory.HELVETICA,
                                    // 14, Font.BOLDITALIC,java.awt.Color.blue)));

                    docPdf.setFooter(footer1);

                  } catch (java.sql.SQLException SqlExec) {

                    SqlExec.printStackTrace();

                    javax.swing.JOptionPane.showMessageDialog(
                        new javax.swing.JFrame(), SqlExec.getMessage());
                  }
                  docPdf.add(table12);

                } catch (java.sql.SQLException SqlExec) {

                  SqlExec.printStackTrace();

                  javax.swing.JOptionPane.showMessageDialog(
                      new javax.swing.JFrame(), SqlExec.getMessage());
                }

              } catch (com.lowagie.text.BadElementException BadElExec) {

                // Bad

                javax.swing.JOptionPane.showMessageDialog(
                    new javax.swing.JFrame(), BadElExec.getMessage());
              }

              //  docPdf.add(table);

            } catch (java.sql.SQLException SqlExec) {

              SqlExec.printStackTrace();

              javax.swing.JOptionPane.showMessageDialog(
                  new javax.swing.JFrame(), SqlExec.getMessage());
            }

            // }  // }

          } catch (com.lowagie.text.BadElementException BadElExec) {

            javax.swing.JOptionPane.showMessageDialog(
                new javax.swing.JFrame(), BadElExec.getMessage());
          }

        } catch (java.io.FileNotFoundException fnfExec) {

          javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), fnfExec.getMessage());
        }
      } catch (com.lowagie.text.DocumentException lwDocexec) {

        javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), lwDocexec.getMessage());
      }

      docPdf.close();
      docPdf.close();
      com.afrisoftech.lib.PDFRenderer.renderPDF(tempFile);

    } catch (java.io.IOException IOexec) {

      javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), IOexec.getMessage());
    }
  }
  public void generatePdf() {

    java.lang.Process wait_for_Pdf2Show;

    java.util.Calendar cal = java.util.Calendar.getInstance();

    java.util.Date dateStampPdf = cal.getTime();

    java.lang.String pdfDateStamp = dateStampPdf.toString();

    try {

      java.io.File tempFile =
          java.io.File.createTempFile("REP" + this.getDateLable() + "_", ".pdf");

      tempFile.deleteOnExit();

      java.lang.Runtime rt = java.lang.Runtime.getRuntime();

      java.lang.String debitTotal = null;

      java.lang.String creditTotal = null;

      com.lowagie.text.Document docPdf = new com.lowagie.text.Document();

      try {

        try {

          com.lowagie.text.pdf.PdfWriter.getInstance(
              docPdf, new java.io.FileOutputStream(tempFile));

          String compName = null;
          String date = null;

          com.lowagie.text.HeaderFooter footer =
              new com.lowagie.text.HeaderFooter(
                  new Phrase("Family planning summary - Page: ", pFontHeader),
                  true); // FontFactory.getFont(com.lowagie.text.FontFactory.HELVETICA, 12,
                         // Font.BOLDITALIC,java.awt.Color.blue));

          docPdf.setFooter(footer);

          docPdf.open();

          try {

            java.util.Calendar calendar = java.util.Calendar.getInstance();

            long dateNow = calendar.getTimeInMillis();

            java.sql.Date datenowSql = new java.sql.Date(dateNow);

            System.out.println(datenowSql.toString());

            //  java.lang.Object listofStaffNos[] = this.getListofStaffNos();

            com.lowagie.text.pdf.PdfPTable table1 = new com.lowagie.text.pdf.PdfPTable(8);

            int headerwidths[] = {12, 15, 30, 15, 10, 15, 10, 18};

            table1.setWidths(headerwidths);

            table1.setWidthPercentage((100));

            table1.getDefaultCell().setBorder(Rectangle.BOTTOM);

            table1.getDefaultCell().setColspan(8);

            Phrase phrase = new Phrase();

            //  table.addCell(phrase);

            table1.getDefaultCell().setColspan(1);
            table1.getDefaultCell().setBackgroundColor(java.awt.Color.WHITE);
            table1.getDefaultCell().setBorderColor(java.awt.Color.WHITE);

            try {
              java.sql.Statement st3 = connectDB.createStatement();
              java.sql.ResultSet rset3 =
                  st3.executeQuery("select header_name ,current_date from pb_header");
              while (rset3.next()) {
                table1.getDefaultCell().setColspan(8);

                table1.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_CENTER);
                phrase = new Phrase(rset3.getObject(1).toString().toUpperCase(), pFontHeader);
                table1.addCell(phrase);
                date = rset3.getObject(2).toString();
              }
              // table1.addCell(phrase);
            } catch (java.sql.SQLException SqlExec) {

              javax.swing.JOptionPane.showMessageDialog(
                  new javax.swing.JFrame(), SqlExec.getMessage());
            }
            docPdf.add(table1);
          } catch (com.lowagie.text.BadElementException BadElExec) {

            javax.swing.JOptionPane.showMessageDialog(
                new javax.swing.JFrame(), BadElExec.getMessage());
          }

          try {

            com.lowagie.text.pdf.PdfPTable table = new com.lowagie.text.pdf.PdfPTable(8);

            int headerwidths[] = {20, 32, 9, 9, 9, 9, 2, 10};

            table.setWidths(headerwidths);

            table.setWidthPercentage((100));
            table.setHeaderRows(2);

            table.getDefaultCell().setBorder(Rectangle.BOTTOM);
            Phrase phrase;
            try {
              table.getDefaultCell().setColspan(4);
              java.text.DateFormat dateFormat =
                  java.text.DateFormat.getDateInstance(java.text.DateFormat.MEDIUM); // MEDIUM);

              java.util.Date endDate1 =
                  dateFormat.parse(endDate.toLocaleString()); // dateInstance.toLocaleString());
              java.util.Date endDate11 =
                  dateFormat.parse(beginDate.toLocaleString()); // dateInstance.toLocaleString());

              phrase =
                  new Phrase(
                      "CONTRACEPTIVES PER CLIENT TYPE : "
                          + dateFormat.format(endDate11)
                          + " - "
                          + dateFormat.format(endDate1),
                      pFontHeader);

              table.addCell(phrase);

              table.getDefaultCell().setColspan(4);
              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);

              phrase = new Phrase("Printed On  :" + date, pFontHeader);

              table.addCell(phrase);
            } catch (java.text.ParseException psExec) {

              javax.swing.JOptionPane.showMessageDialog(
                  new javax.swing.JFrame(), psExec.getMessage());
            }
            table.getDefaultCell().setColspan(1);

            // table.getDefaultCell().setBackgroundColor(java.awt.Color.LIGHT_GRAY);
            table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
            phrase = new Phrase("CODE", pFontHeader);
            table.addCell(phrase);

            table.getDefaultCell().setColspan(1);
            phrase = new Phrase("DESCRIPTION", pFontHeader);
            table.addCell(phrase);

            table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);

            table.getDefaultCell().setColspan(1);
            phrase = new Phrase("NA", pFontHeader);
            table.addCell(phrase);
            phrase = new Phrase("CA", pFontHeader);
            table.addCell(phrase);
            phrase = new Phrase("R", pFontHeader);
            table.addCell(phrase);
            phrase = new Phrase("O", pFontHeader);
            // table.addCell(phrase);
            table.getDefaultCell().setColspan(1);
            phrase = new Phrase("Total", pFontHeader);
            table.addCell(phrase);
            table.getDefaultCell().setColspan(2);
            phrase = new Phrase("Cumm.", pFontHeader);
            table.addCell(phrase);

            table.getDefaultCell().setBackgroundColor(java.awt.Color.WHITE);
            table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);
            double occ = 0.00;
            double total = 0.00;
            try {
              int osBalance = 0;
              int ca1 = 0;
              int nr = 0;
              int cr = 0;
              int rr = 0;
              int rro = 0;
              int rrx = 0;

              java.lang.Object[] listofActx = this.getListofActivities1();
              for (int y = 0; y < listofActx.length; y++) {
                int cum = 0;
                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
                table.getDefaultCell().setColspan(8);
                phrase = new Phrase(listofActx[y].toString().toUpperCase(), pFontHeader);
                table.addCell(phrase);
                java.lang.Object[] listofAct = this.getListofActivities(listofActx[y]);

                System.out.println(listofAct.length);

                for (int i = 0; i < listofAct.length; i++) {
                  int r = 0;
                  int na = 0;
                  int ca = 0;
                  int ra = 0;
                  int rax = 0;
                  int ro = 0;
                  String pnca = null;
                  String pnca1 = null;
                  String pnca1x = null;
                  java.sql.Statement st3 = connectDB.createStatement();
                  java.sql.Statement st31 = connectDB.createStatement();
                  java.sql.ResultSet rset3 =
                      st3.executeQuery(
                          "SELECT DISTINCT COUNT(patient_no) FROM fprecord  WHERE cotraceptive = '"
                              + listofAct[i]
                              + "' AND date_seen::date BETWEEN '"
                              + beginDate
                              + "' AND '"
                              + endDate
                              + "' AND PATIENT_TYPE ILIKE 'NA' AND fp_method not ilike 'Consultation%'");
                  java.sql.ResultSet rset31 =
                      st31.executeQuery(
                          "SELECT DISTINCT cotraceptive FROM fprecord  WHERE cotraceptive = '"
                              + listofAct[i]
                              + "' AND date_seen::date BETWEEN '"
                              + beginDate
                              + "' AND '"
                              + endDate
                              + "'");

                  while (rset31.next()) {
                    table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);

                    table.getDefaultCell().setColspan(1);
                    phrase = new Phrase(" ", pFontHeader);
                    table.addCell(phrase);
                    table.getDefaultCell().setColspan(1);
                    phrase =
                        new Phrase(
                            dbObject.getDBObject(rset31.getObject(1).toString().toUpperCase(), "-"),
                            pFontHeader1);

                    table.addCell(phrase);

                    while (rset3.next()) {

                      na = rset3.getInt(1);
                      nr = nr + rset3.getInt(1);
                    }

                    if (na > 0) {
                      table.getDefaultCell().setColspan(1);
                      table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
                      phrase = new Phrase(java.lang.String.valueOf(na), pFontHeader1);
                      table.addCell(phrase);
                      na = na;
                    } else {
                      table.getDefaultCell().setColspan(1);
                      table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
                      phrase = new Phrase(java.lang.String.valueOf(0), pFontHeader1);
                      table.addCell(phrase);
                      na = 0;
                    }

                    java.sql.Statement st32c = connectDB.createStatement();

                    java.sql.ResultSet rset32c =
                        st32c.executeQuery(
                            "SELECT DISTINCT patient_no FROM fprecord  WHERE date_seen::date BETWEEN '"
                                + beginDate
                                + "' AND '"
                                + endDate
                                + "' AND PATIENT_TYPE = 'CA' AND fp_method not ilike 'Consultation%' GROUP BY patient_no  EXCEPT SELECT patient_no FROM fprecord  WHERE date_seen::date BETWEEN '"
                                + beginDate
                                + "' AND '"
                                + endDate
                                + "' AND PATIENT_TYPE = 'NA' group by patient_no");
                    while (rset32c.next()) {

                      pnca = rset32c.getString(1);

                      java.sql.Statement st32 = connectDB.createStatement();

                      // java.sql.ResultSet rset32 = st32.executeQuery("SELECT DISTINCT
                      // COUNT(patient_no)  FROM fprecord  WHERE cotraceptive ilike
                      // '"+listofAct[i]+"' AND date_seen::date BETWEEN '"+beginDate+"' AND
                      // '"+endDate+"' AND PATIENT_TYPE ILIKE 'CA' AND fp_method not ilike
                      // 'Consultation%' AND patient_no = '"+pnca+"' GROUP BY patient_type EXCEPT
                      // SELECT COUNT(patient_no) FROM fprecord  WHERE date_seen::date BETWEEN
                      // '"+beginDate+"' AND '"+endDate+"' AND cotraceptive ilike '"+listofAct[i]+"'
                      // AND PATIENT_TYPE ILIKE 'NA' AND PATIENT_TYPE ILIKE 'R' AND fp_method not
                      // ilike 'Consultation%'  AND patient_no = '"+pnca+"'");
                      java.sql.ResultSet rset32 =
                          st32.executeQuery(
                              "SELECT DISTINCT COUNT(patient_no)  FROM fprecord  WHERE cotraceptive = '"
                                  + listofAct[i]
                                  + "' AND date_seen::date BETWEEN '"
                                  + beginDate
                                  + "' AND '"
                                  + endDate
                                  + "' AND PATIENT_TYPE = 'CA' AND fp_method not ilike 'Consultation%' AND patient_no = '"
                                  + pnca
                                  + "' GROUP BY patient_no LIMIT 1");

                      while (rset32.next()) {
                        if (rset32.getInt(1) > 1) {
                          ca = ca + 0;
                          cr = cr + 0;
                        } else {
                          ca = ca + rset32.getInt(1);
                          cr = cr + rset32.getInt(1);
                        }
                        // phrase = new Phrase(java.lang.String.valueOf(r), pFontHeader1);
                        //  table.addCell(phrase);
                      }
                    }
                    if (ca > 0) {
                      table.getDefaultCell().setColspan(1);
                      table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
                      phrase = new Phrase(java.lang.String.valueOf(ca), pFontHeader1);
                      table.addCell(phrase);
                      ca = ca;
                    } else {
                      table.getDefaultCell().setColspan(1);
                      table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
                      phrase = new Phrase(java.lang.String.valueOf(0), pFontHeader1);
                      table.addCell(phrase);
                      ca = 0;
                    }

                    //  java.sql.ResultSet rset32 = st32.executeQuery("SELECT COUNT(patient_no) FROM
                    // fprecord  WHERE cotraceptive ilike '"+listofAct[i]+"' AND date_seen::date
                    // BETWEEN '"+beginDate+"' AND '"+endDate+"' AND PATIENT_TYPE ILIKE 'CA' AND
                    // fp_method not ilike 'Consultation%' EXCEPT SELECT COUNT(patient_no) FROM
                    // fprecord  WHERE date_seen::date BETWEEN '"+beginDate+"' AND '"+endDate+"' AND
                    // PATIENT_TYPE ILIKE 'NA' AND PATIENT_TYPE ILIKE 'R' AND fp_method not ilike
                    // 'Consultation%'");
                    //   java.sql.ResultSet rset32 = st32.executeQuery("SELECT DISTINCT
                    // COUNT(patient_no) FROM fprecord  WHERE cotraceptive ilike '"+listofAct[i]+"'
                    // AND date_seen::date BETWEEN '"+beginDate+"' AND '"+endDate+"' AND
                    // PATIENT_TYPE ILIKE 'CA' AND fp_method not ilike 'Consultation%'");

                    java.sql.Statement st32c1 = connectDB.createStatement();

                    java.sql.ResultSet rset32c1 =
                        st32c1.executeQuery(
                            "SELECT DISTINCT patient_no FROM fprecord  WHERE date_seen::date BETWEEN '"
                                + beginDate
                                + "' AND '"
                                + endDate
                                + "' AND PATIENT_TYPE = 'R' AND fp_method not ilike 'Consultation%' GROUP BY patient_no  EXCEPT SELECT patient_no FROM fprecord  WHERE date_seen::date BETWEEN '"
                                + beginDate
                                + "' AND '"
                                + endDate
                                + "' AND (PATIENT_TYPE = 'NA' OR PATIENT_TYPE = 'CA') group by patient_no");
                    while (rset32c1.next()) {

                      pnca1 = rset32c1.getString(1);

                      java.sql.Statement st33 = connectDB.createStatement();
                      //  java.sql.ResultSet rset33 = st33.executeQuery("SELECT COUNT(patient_no)
                      // FROM fprecord  WHERE cotraceptive ilike '"+listofAct[i]+"%' AND
                      // date_seen::date BETWEEN '"+beginDate+"' AND '"+endDate+"' AND PATIENT_TYPE
                      // ILIKE 'R' EXCEPT SELECT COUNT(patient_no) FROM fprecord  WHERE cotraceptive
                      // ilike '"+listofAct[i]+"' AND date_seen::date BETWEEN '"+beginDate+"' AND
                      // '"+endDate+"' AND (PATIENT_TYPE ILIKE 'NA' OR PATIENT_TYPE ILIKE 'CA')");

                      java.sql.ResultSet rset33 =
                          st33.executeQuery(
                              "SELECT DISTINCT COUNT(patient_no) FROM fprecord  WHERE cotraceptive = '"
                                  + listofAct[i]
                                  + "' AND date_seen::date BETWEEN '"
                                  + beginDate
                                  + "' AND '"
                                  + endDate
                                  + "' AND patient_no = '"
                                  + pnca1
                                  + "' AND fp_method not ilike 'Consultation%' GROUP BY patient_no");
                      java.sql.Statement st331 = connectDB.createStatement();

                      while (rset33.next()) {
                        if (rset33.getInt(1) > 1) {
                          ra = ra + 0;
                          rr = rr + 0;
                        } else {
                          ra = ra + rset33.getInt(1);
                          rr = rr + rset33.getInt(1);
                        }
                      }
                    }

                    java.sql.Statement st32cx = connectDB.createStatement();

                    java.sql.ResultSet rset32cx =
                        st32cx.executeQuery(
                            "SELECT DISTINCT patient_no FROM fprecord  WHERE date_seen::date BETWEEN '"
                                + beginDate
                                + "' AND '"
                                + endDate
                                + "' AND cotraceptive = '"
                                + listofAct[i]
                                + "' AND fp_method ilike 'Consult%' group by patient_no EXCEPT SELECT DISTINCT patient_no FROM fprecord  WHERE date_seen::date BETWEEN '"
                                + beginDate
                                + "' AND '"
                                + endDate
                                + "' AND fp_method not ilike 'Consult%' group by patient_no");
                    while (rset32cx.next()) {

                      pnca1x = rset32cx.getString(1);

                      java.sql.Statement st33x = connectDB.createStatement();
                      //  java.sql.ResultSet rset33 = st33.executeQuery("SELECT COUNT(patient_no)
                      // FROM fprecord  WHERE cotraceptive ilike '"+listofAct[i]+"%' AND
                      // date_seen::date BETWEEN '"+beginDate+"' AND '"+endDate+"' AND PATIENT_TYPE
                      // ILIKE 'R' EXCEPT SELECT COUNT(patient_no) FROM fprecord  WHERE cotraceptive
                      // ilike '"+listofAct[i]+"' AND date_seen::date BETWEEN '"+beginDate+"' AND
                      // '"+endDate+"' AND (PATIENT_TYPE ILIKE 'NA' OR PATIENT_TYPE ILIKE 'CA')");

                      java.sql.ResultSet rset33x =
                          st33x.executeQuery(
                              "SELECT DISTINCT COUNT(patient_no) FROM fprecord  WHERE cotraceptive = '"
                                  + listofAct[i]
                                  + "' AND date_seen::date BETWEEN '"
                                  + beginDate
                                  + "' AND '"
                                  + endDate
                                  + "' AND patient_no = '"
                                  + pnca1x
                                  + "' AND fp_method ilike 'Consultation' GROUP BY patient_no");

                      while (rset33x.next()) {
                        if (rset33x.getInt(1) > 1) {
                          rax = rax + 0;
                          rrx = rrx + 0;
                        } else {
                          rax = rax + rset33x.getInt(1);
                          rrx = rrx + rset33x.getInt(1);
                        }
                      }
                    }

                    if (ra > 0 || rax > 0) {
                      table.getDefaultCell().setColspan(1);
                      table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
                      phrase = new Phrase(java.lang.String.valueOf(ra + rax), pFontHeader1);
                      table.addCell(phrase);
                      ra = ra + rax;
                    } else {
                      table.getDefaultCell().setColspan(1);
                      table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
                      phrase = new Phrase(java.lang.String.valueOf(0), pFontHeader1);
                      table.addCell(phrase);
                      ra = 0;
                    }

                    table.getDefaultCell().setColspan(1);
                    table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);

                    phrase = new Phrase(java.lang.String.valueOf(na + ca + ra), pFontHeader1);
                    table.addCell(phrase);
                    cum = cum + na + ca + ra;
                    table.getDefaultCell().setColspan(2);
                    phrase = new Phrase(java.lang.String.valueOf(cum), pFontHeader1);
                    table.addCell(phrase);
                  }
                }
              }

              table.getDefaultCell().setBorderColor(java.awt.Color.BLACK);

              table.getDefaultCell().setBorder(Rectangle.BOTTOM | Rectangle.TOP);

              //  while (rsetTotals.next()) {

              table.getDefaultCell().setColspan(2);

              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
              phrase = new Phrase("Total", pFontHeader);

              table.addCell(phrase);

              table.getDefaultCell().setColspan(1);

              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
              phrase = new Phrase(java.lang.String.valueOf(nr), pFontHeader);
              table.addCell(phrase);

              phrase = new Phrase(java.lang.String.valueOf(cr), pFontHeader);
              table.addCell(phrase);

              phrase = new Phrase(java.lang.String.valueOf(rr + rrx), pFontHeader);
              table.addCell(phrase);

              phrase = new Phrase(java.lang.String.valueOf(rro), pFontHeader);
              // table.addCell(phrase);

              table.getDefaultCell().setColspan(1);
              phrase = new Phrase(java.lang.String.valueOf(nr + cr + rr + rrx), pFontHeader);
              table.addCell(phrase);

              table.getDefaultCell().setColspan(2);
              phrase = new Phrase(" ", pFontHeader);

              table.addCell(phrase);

              docPdf.add(table);

            } catch (java.sql.SQLException SqlExec) {

              javax.swing.JOptionPane.showMessageDialog(
                  new javax.swing.JFrame(), SqlExec.getMessage());
            }

            // }

          } catch (com.lowagie.text.BadElementException BadElExec) {

            javax.swing.JOptionPane.showMessageDialog(
                new javax.swing.JFrame(), BadElExec.getMessage());
          }

        } catch (java.io.FileNotFoundException fnfExec) {

          javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), fnfExec.getMessage());
        }
      } catch (com.lowagie.text.DocumentException lwDocexec) {

        javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), lwDocexec.getMessage());
      }

      docPdf.close();
      com.afrisoftech.lib.PDFRenderer.renderPDF(tempFile);

    } catch (java.io.IOException IOexec) {

      javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), IOexec.getMessage());
    }
  }
예제 #10
0
  public void generatePdf(java.lang.String memNo) {

    java.lang.Process wait_for_Pdf2Show;

    java.util.Calendar cal = java.util.Calendar.getInstance();

    java.util.Date dateStampPdf = cal.getTime();

    java.lang.String pdfDateStamp = dateStampPdf.toString();

    try {

      java.io.File tempFile =
          java.io.File.createTempFile("REP" + this.getDateLable() + "_", ".pdf");

      tempFile.deleteOnExit();

      java.lang.Runtime rt = java.lang.Runtime.getRuntime();

      java.lang.String debitTotal = null;

      java.lang.String creditTotal = null;

      com.lowagie.text.Document docPdf = new com.lowagie.text.Document();

      double osBalance = 0.00;
      double current = 0.00;
      try {

        try {

          com.lowagie.text.pdf.PdfWriter.getInstance(
              docPdf, new java.io.FileOutputStream(tempFile));

          String compName = null;
          String date = null;
          String Messg = null;

          docPdf.open();

          try {

            java.util.Calendar calendar = java.util.Calendar.getInstance();

            long dateNow = calendar.getTimeInMillis();

            java.sql.Date datenowSql = new java.sql.Date(dateNow);

            System.out.println(datenowSql.toString());

            java.lang.Object listofStaffNos[] = this.getListofStaffNos();

            com.lowagie.text.pdf.PdfPTable table1 = new com.lowagie.text.pdf.PdfPTable(7);
            //  com.lowagie.text.Table table = new com.lowagie.text.Table(7);

            // table.endHeaders();

            int headerwidths[] = {15, 15, 30, 15, 15, 15, 15};

            table1.setWidths(headerwidths);
            //  if (docPdf.getPageNumber() > 1) {
            //  table1.setHeaderRows(4);
            //  }
            table1.setWidthPercentage((100));

            table1.getDefaultCell().setBorder(Rectangle.BOTTOM);

            table1.getDefaultCell().setColspan(7);

            Phrase phrase = new Phrase();

            //  table.addCell(phrase);

            table1.getDefaultCell().setColspan(1);
            table1.getDefaultCell().setBackgroundColor(java.awt.Color.WHITE);
            table1.getDefaultCell().setBorderColor(java.awt.Color.WHITE);

            try {
              java.sql.Statement st3 = connectDB.createStatement();
              java.sql.Statement st4 = connectDB.createStatement();
              java.sql.ResultSet rset4 =
                  st4.executeQuery(
                      "SELECT TO_CHAR(current_timestamp(0),'FMDay FMDD/ MM/ YY HH12::MI')");

              while (rset4.next()) {
                date = rset4.getObject(1).toString();
              }
              java.sql.ResultSet rset3 = st3.executeQuery("select header_name from pb_header");
              while (rset3.next()) {
                table1.getDefaultCell().setColspan(7);

                table1.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_CENTER);
                phrase = new Phrase(rset3.getObject(1).toString(), pFontHeader11);
                table1.addCell(phrase);
              }
            } catch (java.sql.SQLException SqlExec) {

              javax.swing.JOptionPane.showMessageDialog(
                  new javax.swing.JFrame(), SqlExec.getMessage());
            }
            docPdf.add(table1);
          } catch (com.lowagie.text.BadElementException BadElExec) {

            javax.swing.JOptionPane.showMessageDialog(
                new javax.swing.JFrame(), BadElExec.getMessage());
          }

          //  } catch(java.io.FileNotFoundException fnfExec) {

          //  javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(),
          // fnfExec.getMessage());

          /// }
          try {

            java.util.Calendar calendar = java.util.Calendar.getInstance();

            long dateNow = calendar.getTimeInMillis();

            java.sql.Date datenowSql = new java.sql.Date(dateNow);

            System.out.println(datenowSql.toString());

            java.lang.Object listofStaffNos[] = this.getListofStaffNos();

            com.lowagie.text.pdf.PdfPTable table = new com.lowagie.text.pdf.PdfPTable(9);
            //  com.lowagie.text.Table table = new com.lowagie.text.Table(7);

            // table.endHeaders();

            int headerwidths[] = {12, 12, 9, 10, 12, 10, 13, 8, 8};

            table.setWidths(headerwidths);
            //  if (docPdf.getPageNumber() > 1) {
            table.setHeaderRows(5);
            //  }
            table.setWidthPercentage((100));

            table.getDefaultCell().setBorder(Rectangle.BOTTOM);

            table.getDefaultCell().setColspan(9);

            Phrase phrase = new Phrase();

            //  table.addCell(phrase);

            table.getDefaultCell().setColspan(1);
            table.getDefaultCell().setBackgroundColor(java.awt.Color.WHITE);
            table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);

            try {
              double Debit = 0.00;
              //    double Credit = 0.00;

              java.sql.Statement st22 = connectDB.createStatement();
              java.sql.Statement st11 = connectDB.createStatement();
              java.sql.Statement st = connectDB.createStatement();
              java.sql.Statement st1 = connectDB.createStatement();
              java.sql.Statement st2 = connectDB.createStatement();
              java.sql.Statement st3 = connectDB.createStatement();
              java.sql.Statement st4 = connectDB.createStatement();
              java.sql.Statement st41 = connectDB.createStatement();
              java.sql.Statement st5 = connectDB.createStatement();
              java.sql.ResultSet rset3 =
                  st3.executeQuery(
                      "select hospital_name,postal_code||' '||box_no||' '||town,main_telno||' '||other_telno,initcap(street),main_faxno,email,website,room_no from pb_hospitalprofile");
              //  java.sql.ResultSet rset4 = st4.executeQuery("select scheme_manager from
              // ac_scheme_providers where scheme_manager = '"+memNo+"'");
              java.sql.ResultSet rset4 =
                  st4.executeQuery(
                      "select scheme_name,account_no from ac_schemes where account_no = '"
                          + memNo1
                          + "'");
              java.sql.ResultSet rset41 =
                  st41.executeQuery(
                      "select payer_name from ac_schemes where account_no = '" + memNo1 + "'");
              //   java.sql.ResultSet rset1 = st1.executeQuery("select ac.date,pc.scheme_staff_no,
              // ac.payee,ac.reference_no,sum(ac.debit),ac.admission_no from hp_patient_card
              // pc,ac_debtors ac where pc.patient_no = ac.admission_no and ac.dealer = '"+memNo+"'
              // and ac.dealer = pc.scheme AND ac.date::date BETWEEN '"+beginDate+"' AND
              // '"+endDate+"' and ac.debit > 0 group by ac.date,pc.scheme_staff_no,
              // ac.payee,ac.reference_no,ac.admission_no");// UNION select
              // pd.date::date,initcap(pd.scheme_staff_no), (sh.first_name||' '||sh.second_name||'
              // '||sh.last_name) as name,pd.reference,sum(pd.credit),pd.patient_no from
              // hp_patient_card pd,hp_inpatient_register sh where pd.patient_no = sh.patient_no and
              // pd.isurer = '"+memNo+"' AND pd.date::date BETWEEN '"+beginDate+"' AND '"+endDate+"'
              // and pd.credit > 0 group by pd.date::date,pd.scheme_staff_no,
              // name,pd.reference,pd.invoice_no,pd.patient_no order by pd.invoice_no");
              java.sql.ResultSet rset1 =
                  st1.executeQuery(
                      " select submission_date,date_returned,doc_no,inv_no,reason,inv_amount,initcap(action),org_dispatch_no,submission_no from pb_returned_claim where dispatched = true and acc_no = '"
                          + memNo1
                          + "' AND submission_date BETWEEN '"
                          + beginDate
                          + "' AND '"
                          + endDate
                          + "'  order by submission_date"); // UNION select
                                                            // pd.date::date,initcap(pd.scheme_staff_no), (sh.first_name||' '||sh.second_name||' '||sh.last_name) as name,pd.reference,sum(pd.credit),pd.patient_no from hp_patient_card pd,hp_inpatient_register sh where pd.patient_no = sh.patient_no and pd.isurer = '"+memNo+"' AND pd.date::date BETWEEN '"+beginDate+"' AND '"+endDate+"' and pd.credit > 0 group by pd.date::date,pd.scheme_staff_no, name,pd.reference,pd.invoice_no,pd.patient_no order by pd.invoice_no");
              // java.sql.ResultSet rsetTotals2 = st22.executeQuery("select sum(balance-credit_bal)
              // from ac_debtors where account_no = '"+memNo1+"' AND date::date < '"+beginDate+"'");

              table.getDefaultCell().setBorderColor(java.awt.Color.BLACK);
              table.getDefaultCell().setBorderWidth(Rectangle.TOP | Rectangle.BOTTOM);

              table.getDefaultCell().setColspan(9);
              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_CENTER);
              phrase = new Phrase("Claim Re-submission Report", pFontHeader11);
              table.addCell(phrase);

              while (rset4.next()) {
                table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);
                table.getDefaultCell().setColspan(9);

                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
                phrase =
                    new Phrase(
                        "Scheme Name : " + dbObject.getDBObject(rset4.getObject(1), "-"),
                        pFontHeader1);
                table.addCell(phrase);
                table.getDefaultCell().setColspan(9);

                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
                phrase =
                    new Phrase(
                        "Account No : " + dbObject.getDBObject(rset4.getObject(2), "-"),
                        pFontHeader1);
                table.addCell(phrase);
              }
              while (rset41.next()) {
                table.getDefaultCell().setColspan(9);

                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
                phrase =
                    new Phrase(
                        "Payer : " + dbObject.getDBObject(rset41.getObject(1), "-"), pFontHeader1);
                table.addCell(phrase);
              }

              table.getDefaultCell().setColspan(5);

              try {
                java.text.DateFormat dateFormat =
                    java.text.DateFormat.getDateInstance(java.text.DateFormat.MEDIUM); // MEDIUM);

                java.util.Date endDate1 =
                    dateFormat.parse(endDate.toLocaleString()); // dateInstance.toLocaleString());
                java.util.Date endDate11 =
                    dateFormat.parse(beginDate.toLocaleString()); // dateInstance.toLocaleString());

                System.out.println("" + endDate1);
                //  phrase = new Phrase(bank +" Report: " +dateFormat.format(formattedDate),
                // pFontHeader);

                //  table.addCell(phrase);

                phrase =
                    new Phrase(
                        "Period : "
                            + dateFormat.format(endDate11)
                            + " - "
                            + dateFormat.format(endDate1),
                        pFontHeader1);

                table.addCell(phrase);
              } catch (java.text.ParseException psExec) {

                javax.swing.JOptionPane.showMessageDialog(
                    new javax.swing.JFrame(), psExec.getMessage());
              }
              // table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
              // phrase = new Phrase("Period : '"+beginDate+"' - '"+endDate+"'", pFontHeader1);
              // table.addCell(phrase);

              table.getDefaultCell().setColspan(4);

              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
              phrase = new Phrase("Print Date " + date, pFontHeader1);
              table.addCell(phrase);

              table.getDefaultCell().setColspan(3);

              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
              table.getDefaultCell().setBorderColor(java.awt.Color.BLACK);
              table.getDefaultCell().setBorderWidth(Rectangle.TOP);
              table.getDefaultCell().setColspan(1);
              phrase = new Phrase("Subm. Date", pFontHeader1);
              table.addCell(phrase);
              table.getDefaultCell().setColspan(1);
              phrase = new Phrase("Date Rcd", pFontHeader1);
              table.addCell(phrase);
              table.getDefaultCell().setColspan(1);
              phrase = new Phrase("Doc No", pFontHeader1);
              table.addCell(phrase);

              table.getDefaultCell().setColspan(1);
              phrase = new Phrase("Invoice No.", pFontHeader1);
              table.addCell(phrase);
              phrase = new Phrase("Reason", pFontHeader1);
              table.addCell(phrase);
              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);

              table.getDefaultCell().setColspan(1);
              phrase = new Phrase("Amt ", pFontHeader1);
              table.addCell(phrase);
              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
              table.getDefaultCell().setColspan(1);
              phrase = new Phrase("Action Taken", pFontHeader1);
              table.addCell(phrase);
              table.getDefaultCell().setColspan(1);
              phrase = new Phrase("Org Disp No ", pFontHeader1);
              table.addCell(phrase);
              phrase = new Phrase("Sbm No.", pFontHeader1);
              table.addCell(phrase);

              while (rset1.next()) {
                table.getDefaultCell().setColspan(1);
                table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);

                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
                phrase = new Phrase(dbObject.getDBObject(rset1.getObject(1), "-"), pFontHeader);

                table.addCell(phrase);
                table.getDefaultCell().setColspan(1);

                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
                phrase = new Phrase(dbObject.getDBObject(rset1.getObject(2), "-"), pFontHeader);

                table.addCell(phrase);
                table.getDefaultCell().setColspan(1);
                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
                phrase = new Phrase(dbObject.getDBObject(rset1.getObject(3), "-"), pFontHeader);

                table.addCell(phrase);

                table.getDefaultCell().setColspan(1);
                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
                phrase = new Phrase(dbObject.getDBObject(rset1.getObject(4), "-"), pFontHeader);

                table.addCell(phrase);
                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
                phrase = new Phrase(dbObject.getDBObject(rset1.getObject(5), "-"), pFontHeader);

                table.addCell(phrase);
                table.getDefaultCell().setColspan(1);
                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);

                phrase =
                    new Phrase(
                        new com.afrisoftech.sys.Format2Currency()
                            .Format2Currency(rset1.getString(6)),
                        pFontHeader);
                Debit = Debit + rset1.getDouble(6);
                table.addCell(phrase);
                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
                phrase = new Phrase(dbObject.getDBObject(rset1.getObject(7), "-"), pFontHeader);

                table.addCell(phrase);

                table.getDefaultCell().setColspan(1);
                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
                phrase = new Phrase(dbObject.getDBObject(rset1.getObject(8), "-"), pFontHeader);

                table.addCell(phrase);
                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
                phrase = new Phrase(dbObject.getDBObject(rset1.getObject(9), "-"), pFontHeader);

                table.addCell(phrase);
              }
              table.getDefaultCell().setColspan(2);

              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
              phrase = new Phrase("", pFontHeader);

              table.addCell(phrase);
              table.getDefaultCell().setBorderColor(java.awt.Color.BLACK);

              table.getDefaultCell().setBorder(Rectangle.BOTTOM | Rectangle.TOP);

              //  while (rsetTotals.next()) {

              table.getDefaultCell().setColspan(2);

              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
              phrase = new Phrase("Total", pFontHeader);

              table.addCell(phrase);

              table.getDefaultCell().setColspan(2);

              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
              phrase =
                  new Phrase(
                      new com.afrisoftech.sys.Format2Currency()
                          .Format2Currency(java.lang.String.valueOf(Debit)),
                      pFontHeader);

              table.addCell(phrase);
              table.getDefaultCell().setColspan(3);
              phrase = new Phrase(" ", pFontHeader);

              table.addCell(phrase);

              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
              table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);
              table.getDefaultCell().setColspan(27);
              phrase = new Phrase(" ");
              table.addCell(phrase);
              table.getDefaultCell().setColspan(9);
              phrase =
                  new Phrase(
                      "PREPARED BY:  _____________________________      CHECKED BY: ____________________________________",
                      pFontHeader1);
              table.addCell(phrase);
              table.getDefaultCell().setColspan(27);
              phrase = new Phrase(" ");
              table.addCell(phrase);
              table.getDefaultCell().setColspan(9);
              phrase =
                  new Phrase(
                      "RECEIVED BY: ___________________  Sign:   _______________ Date: ____/____/______  Time: ___________",
                      pFontHeader1);
              table.addCell(phrase);
              try {

                java.sql.Statement st31 = connectDB.createStatement();
                // java.sql.Statement st4 = connectDB.createStatement();
                java.sql.ResultSet rset2 = st31.executeQuery("select name from pb_notice");

                // java.sql.ResultSet rset2 = st3.executeQuery("SELECT hospital_name from
                // pb_hospitalprofile");
                //   java.sql.ResultSet rset4 = st4.executeQuery("SELECT date('now') as Date");
                while (rset2.next()) {
                  Messg = rset2.getString(1);
                }
                com.lowagie.text.HeaderFooter footer =
                    new com.lowagie.text.HeaderFooter(
                        new Phrase("" + Messg + ""),
                        false); // FontFactory.getFont(com.lowagie.text.FontFactory.HELVETICA, 14,
                                // Font.BOLDITALIC,java.awt.Color.blue)));

                //  com.lowagie.text.HeaderFooter headerFoter = new
                // com.lowagie.text.HeaderFooter(new Phrase(""+compName+""),false);//
                // FontFactory.getFont(com.lowagie.text.FontFactory.HELVETICA, 14,
                // Font.BOLDITALIC,java.awt.Color.blue)));
                //  headerFoter.ALIGN_CENTER;
                //  headerFoter.setRight(5);
                docPdf.setFooter(footer);

              } catch (java.sql.SQLException SqlExec) {

                javax.swing.JOptionPane.showMessageDialog(
                    new javax.swing.JFrame(), SqlExec.getMessage());
              }

              docPdf.add(table);

            } catch (java.sql.SQLException SqlExec) {

              javax.swing.JOptionPane.showMessageDialog(
                  new javax.swing.JFrame(), SqlExec.getMessage());
            }

            // }  // }

          } catch (com.lowagie.text.BadElementException BadElExec) {

            javax.swing.JOptionPane.showMessageDialog(
                new javax.swing.JFrame(), BadElExec.getMessage());
          }

        } catch (java.io.FileNotFoundException fnfExec) {

          javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), fnfExec.getMessage());
        }
      } catch (com.lowagie.text.DocumentException lwDocexec) {

        javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), lwDocexec.getMessage());
      }

      docPdf.close();
      docPdf.close();
      com.afrisoftech.lib.PDFRenderer.renderPDF(tempFile);

    } catch (java.io.IOException IOexec) {

      javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), IOexec.getMessage());
    }
  }
  public void generatePdf() {

    // java.lang.Object[][] ageingDates = periodicDates.getMonthlyDates();

    ageingDates = ageingSeries.getAgeingDateSeries();

    double columnTotals[] = new double[ageingDates.length];

    java.lang.Process wait_for_Pdf2Show;

    java.util.Calendar cal = java.util.Calendar.getInstance();

    java.util.Date dateStampPdf = cal.getTime();

    java.lang.String pdfDateStamp = dateStampPdf.toString();

    int interval = 0;

    try {

      java.io.File tempFile =
          java.io.File.createTempFile("REP" + this.getDateLable() + "_", ".pdf");

      tempFile.deleteOnExit();

      java.lang.Runtime rt = java.lang.Runtime.getRuntime();

      java.lang.String debitTotal = null;

      java.lang.String creditTotal = null;

      com.lowagie.text.Document docPdf = new com.lowagie.text.Document();

      java.util.Calendar calendar = java.util.Calendar.getInstance();

      long dateNow = calendar.getTimeInMillis();

      java.sql.Date datenowSql = new java.sql.Date(dateNow);

      System.out.println(datenowSql.toString());

      try {

        try {

          com.lowagie.text.pdf.PdfWriter.getInstance(
              docPdf, new java.io.FileOutputStream(tempFile));

          String compName = null;
          String date = null;
          try {

            //   java.sql.Connection conDb =
            // java.sql.DriverManager.getConnection("jdbc:postgresql://localhost:5432/sako","postgres","pilsiner");

            java.sql.Statement st3 = connectDB.createStatement();
            java.sql.Statement st4 = connectDB.createStatement();

            java.sql.ResultSet rset2 =
                st3.executeQuery("SELECT hospital_name from pb_hospitalprofile");
            java.sql.ResultSet rset4 = st4.executeQuery("SELECT date('now') as Date");
            while (rset2.next()) compName = rset2.getObject(1).toString();

            while (rset4.next()) date = rset4.getObject(1).toString();

            com.lowagie.text.HeaderFooter headerFoter =
                new com.lowagie.text.HeaderFooter(
                    new Phrase("" + compName, pFontHeader2),
                    false); // FontFactory.getFont(com.lowagie.text.FontFactory.HELVETICA, 14,
            // Font.BOLDITALIC,java.awt.Color.blue)));

            //  com.lowagie.text.HeaderFooter headerFoter = new com.lowagie.text.HeaderFooter(new
            // Phrase(""+compName+""),false);//
            // FontFactory.getFont(com.lowagie.text.FontFactory.HELVETICA, 14,
            // Font.BOLDITALIC,java.awt.Color.blue)));
            headerFoter.setAlignment(com.lowagie.text.HeaderFooter.ALIGN_CENTER);
            docPdf.setHeader(headerFoter);

          } catch (java.sql.SQLException SqlExec) {

            javax.swing.JOptionPane.showMessageDialog(
                new javax.swing.JFrame(), SqlExec.getMessage());
          }

          com.lowagie.text.HeaderFooter footer =
              new com.lowagie.text.HeaderFooter(
                  new Phrase(Debtor + ": Ageing  Page: ", pFontHeader),
                  true); // FontFactory.getFont(com.lowagie.text.FontFactory.HELVETICA, 12,
          // Font.BOLDITALIC,java.awt.Color.blue));

          docPdf.setFooter(footer);

          docPdf.open();

          double Totals = 0.00;
          double OS = 0.00;
          try {

            com.lowagie.text.pdf.PdfPTable table =
                new com.lowagie.text.pdf.PdfPTable(ageingDates.length + 4);

            String headerWidths = null;

            java.util.Vector headerVector = new java.util.Vector(1, 1);

            int z = ageingDates.length;

            int headerwidths[] = {22, 13, 13, 13, 13, 13, 13, 13}; // ,13,13};

            table.setWidths(headerwidths);

            table.setWidthPercentage((100));

            table.setHeaderRows(2);

            table.getDefaultCell().setBorder(Rectangle.BOTTOM);

            table.getDefaultCell().setColspan(8);
            Phrase phrase = new Phrase("");
            table.getDefaultCell().setColspan(5);
            try {
              java.text.DateFormat dateFormat =
                  java.text.DateFormat.getDateInstance(java.text.DateFormat.MEDIUM); // MEDIUM);

              //                            java.util.Date endDate1 =
              // dateFormat.parse(beginDate.toLocaleString());//dateInstance.toLocaleString());
              java.util.Date endDate2 = dateFormat.parse(endDate.toLocaleString());
              //  System.out.println(""+endDate1);
              //  phrase = new Phrase(bank +" Report: " +dateFormat.format(formattedDate),
              // pFontHeader);

              //  table.addCell(phrase);

              phrase =
                  new Phrase(
                      Debtor + ": Payer  Ageing as at " + dateFormat.format(endDate2), pFontHeader);

              table.addCell(phrase);
            } catch (java.text.ParseException psExec) {

              javax.swing.JOptionPane.showMessageDialog(
                  new javax.swing.JFrame(), psExec.getMessage());
            }

            table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
            table.getDefaultCell().setColspan(3);
            phrase = new Phrase("Printed on : " + date, pFontHeader);
            table.addCell(phrase);

            table.getDefaultCell().setColspan(1);

            table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_CENTER);
            phrase = new Phrase("Name", pFontHeader);
            table.addCell(phrase);
            // table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);

            //                 phrase = new Phrase("O/S Balance",pFontHeader);
            //                 table.addCell(phrase);

            for (int x = 0; x < ageingDates.length; x++) {

              int days = 30;
              if (x < 1) {
                phrase = new Phrase("Current", pFontHeader);
              } else {
                phrase = new Phrase("Over " + x * days + " Days", pFontHeader);
                interval = x;
              }

              table.addCell(phrase);
            }
            //  table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
            phrase = new Phrase("Over " + ((interval + 1) * 30) + " Days", pFontHeader);
            table.addCell(phrase);

            //      table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
            phrase = new Phrase("UnAlloc.", pFontHeader);
            table.addCell(phrase);

            phrase = new Phrase("O/S", pFontHeader);
            table.addCell(phrase);

            table.getDefaultCell().setBackgroundColor(java.awt.Color.WHITE);
            // table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);

            try {

              double GrandTotal = 0.00;
              double Over120Total = 0.00;
              java.lang.Object[] listofAct = this.getListofActivities();

              //    java.sql.Connection conDb1 =
              // java.sql.DriverManager.getConnection("jdbc:postgresql://localhost:5432/sako","postgres","pilsiner");

              System.out.println(listofAct.length);

              for (int i = 0; i < listofAct.length; i++) {
                double TurnOver = 0.00;
                double Over120 = 0.00;
                double TotalCount = 0.00;

                table.getDefaultCell().setColspan(1);

                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
                java.sql.PreparedStatement pset10 =
                    connectDB.prepareStatement(
                        "select account_no||' '||scheme_name from ac_schemes WHERE account_no = ?"); // < '"+endDate+"'::date and date > '"+endDate+"'::date - 30 group by dealer");

                // java.sql.PreparedStatement pset10 = connectDB.prepareStatement("select distinct
                // initcap(scheme_manager) as dealer from ac_scheme_providers WHERE scheme_manager =
                // ?");//< '"+endDate+"'::date and date > '"+endDate+"'::date - 30 group by
                // dealer");
                pset10.setString(1, listofAct[i].toString());
                java.sql.ResultSet rset10 = pset10.executeQuery();
                while (rset10.next()) table.getDefaultCell().setColspan(1);
                //  table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
                phrase = new Phrase(dbObject.getDBObject(rset10.getObject(1), "-"), pFontHeader1);
                table.addCell(phrase);
                // columnTotals[t] = columnTotals[t] + rset.getDouble(1);

                // table.addCell(phrase);

                java.sql.Statement st2 = connectDB.createStatement();
                java.sql.Statement st21 = connectDB.createStatement();
                java.sql.Statement st22 = connectDB.createStatement();
                java.sql.Statement st23 = connectDB.createStatement();
                java.sql.Statement st211 = connectDB.createStatement();
                java.sql.Statement st221 = connectDB.createStatement();
                java.sql.Statement st231 = connectDB.createStatement();
                java.sql.Statement st2A = connectDB.createStatement();
                java.sql.Statement st2B = connectDB.createStatement();
                java.sql.Statement st2C = connectDB.createStatement();
                java.sql.Statement stc = connectDB.createStatement();
                System.out.println("Dealer Is : [" + listofAct[i] + "].");

                java.sql.PreparedStatement pset1 =
                    connectDB.prepareStatement(
                        "select sum(balance) from ac_debtors WHERE account_no = ? and dealer = '"
                            + Debtor
                            + "'  AND date < '"
                            + ageingDates[0][0]
                            + "'"); // < '"+endDate+"'::date and date > '"+endDate+"'::date - 30
                // group by dealer");
                // java.sql.PreparedStatement pset1 = connectDB.prepareStatement("select
                // sum(balance) from ac_debtors WHERE account_no = ? and dealer = '"+Debtor+"'  AND
                // date < '"+ageingDates[ageingDates.length - 1][0]+"'");//< '"+endDate+"'::date and
                // date > '"+endDate+"'::date - 30 group by dealer");

                pset1.setString(1, listofAct[i].toString());
                java.sql.ResultSet rset1 = pset1.executeQuery();

                java.sql.PreparedStatement pset111 =
                    connectDB.prepareStatement(
                        "select sum(balance-credit_bal) from ac_debtors WHERE account_no = ? and dealer = '"
                            + Debtor
                            + "'  AND date < '"
                            + endDate
                            + "'"); // < '"+endDate+"'::date and date > '"+endDate+"'::date - 30
                // group by dealer");
                pset111.setString(1, listofAct[i].toString());
                java.sql.ResultSet rset111 = pset111.executeQuery();
                java.sql.Statement st02 = connectDB.createStatement();

                java.sql.PreparedStatement pset112 =
                    connectDB.prepareStatement(
                        "select sum(credit_bal) from ac_debtors WHERE account_no = ? and dealer = '"
                            + Debtor
                            + "' AND date < '"
                            + endDate
                            + "'"); // < '"+endDate+"'::date and date > '"+endDate+"'::date - 30
                // group by dealer");
                pset112.setString(1, listofAct[i].toString());
                java.sql.ResultSet rset112 = pset112.executeQuery();

                for (int t = ageingDates.length - 1; t >= 0; t--) {
                  // for (int t = 0; t < ageingDates.length; t++) {
                  java.sql.Statement st01 = connectDB.createStatement();

                  java.sql.PreparedStatement pset =
                      connectDB.prepareStatement(
                          "select sum(balance) from ac_debtors WHERE account_no = ? and dealer = '"
                              + Debtor
                              + "'  AND date between '"
                              + ageingDates[t][0]
                              + "' AND '"
                              + ageingDates[t][1]
                              + "'");
                  pset.setString(1, listofAct[i].toString().toUpperCase());
                  java.sql.ResultSet rset = pset.executeQuery();

                  while (rset.next()) {

                    table.getDefaultCell().setColspan(1);
                    table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
                    phrase =
                        new Phrase(
                            new com.afrisoftech.sys.Format2Currency()
                                .Format2Currency(rset.getString(1)),
                            pFontHeader1);
                    table.addCell(phrase);
                    columnTotals[t] = columnTotals[t] + rset.getDouble(1);
                  }
                  TotalCount = TotalCount + rset.getDouble(1);
                }

                while (rset1.next()) {
                  System.out.println("This Result " + rset1.getString(1));
                  table.getDefaultCell().setColspan(1);
                  table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
                  phrase =
                      new Phrase(
                          new com.afrisoftech.sys.Format2Currency()
                              .Format2Currency(rset1.getString(1)),
                          pFontHeader1);
                  // phrase = new Phrase(new
                  // com.afrisoftech.sys.Format2Currency().Format2Currency("0"),pFontHeader1);

                  Over120 = rset1.getDouble(1);
                  table.addCell(phrase);
                  Over120Total = Over120Total + rset1.getDouble(1);
                  TurnOver = TotalCount + Over120;
                }

                while (rset112.next()) {
                  phrase =
                      new Phrase(
                          new com.afrisoftech.sys.Format2Currency()
                              .Format2Currency(rset112.getString(1)),
                          pFontHeader1);
                  table.addCell(phrase);
                  OS = OS + rset112.getDouble(1);
                }

                while (rset111.next()) {
                  phrase =
                      new Phrase(
                          new com.afrisoftech.sys.Format2Currency()
                              .Format2Currency(rset111.getString(1)),
                          pFontHeader1);
                  table.addCell(phrase);
                  Totals = Totals + rset111.getDouble(1);
                }
              }

              table.getDefaultCell().setBorderColor(java.awt.Color.BLACK);

              table.getDefaultCell().setBorder(Rectangle.BOTTOM | Rectangle.TOP);

              // while (rsetTotals.next()) {

              table.getDefaultCell().setColspan(1);

              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
              phrase = new Phrase("Total", pFontHeader);

              table.addCell(phrase);

              table.getDefaultCell().setColspan(1);

              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);

              // phrase = new Phrase(new
              // com.afrisoftech.sys.Format2Currency().Format2Currency(rsetTotals.getString(1)),
              // pFontHeader);
              // for (int x = 0; x < ageingDates.length; x++) {
              for (int x = ageingDates.length - 1; x >= 0; x--) {
                // phrase = new Phrase("Current"+2*x,pFontHeader);
                // table.addCell(phrase);

                phrase =
                    new Phrase(
                        new com.afrisoftech.sys.Format2Currency()
                            .Format2Currency(java.lang.String.valueOf(columnTotals[x])),
                        pFontHeader);

                table.addCell(phrase);
              }

              phrase =
                  new Phrase(
                      new com.afrisoftech.sys.Format2Currency()
                          .Format2Currency(java.lang.String.valueOf(Over120Total)),
                      pFontHeader);

              table.addCell(phrase);
              phrase =
                  new Phrase(
                      new com.afrisoftech.sys.Format2Currency()
                          .Format2Currency(java.lang.String.valueOf(OS)),
                      pFontHeader);

              table.addCell(phrase);

              phrase =
                  new Phrase(
                      new com.afrisoftech.sys.Format2Currency()
                          .Format2Currency(java.lang.String.valueOf(Totals)),
                      pFontHeader);

              table.addCell(phrase);

              docPdf.add(table);

            } catch (java.sql.SQLException SqlExec) {

              SqlExec.printStackTrace();

              javax.swing.JOptionPane.showMessageDialog(
                  new javax.swing.JFrame(), SqlExec.getMessage());
            }

          } catch (com.lowagie.text.BadElementException BadElExec) {

            // Bad

            javax.swing.JOptionPane.showMessageDialog(
                new javax.swing.JFrame(), BadElExec.getMessage());
          }

        } catch (java.io.FileNotFoundException fnfExec) {

          javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), fnfExec.getMessage());
        }
      } catch (com.lowagie.text.DocumentException lwDocexec) {

        javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), lwDocexec.getMessage());
      }

      docPdf.close();

      try {

        if (System.getProperty("os.name").equalsIgnoreCase("Linux")) {

          System.out.println(tempFile);

          wait_for_Pdf2Show = rt.exec("kghostview " + tempFile + "");

          wait_for_Pdf2Show.waitFor();

        } else {

          wait_for_Pdf2Show =
              rt.exec("c:/Program Files/Adobe/Acrobat 5.0/Reader/AcroRd32.exe " + tempFile);

          wait_for_Pdf2Show.waitFor();
        }

      } catch (java.lang.InterruptedException intrExec) {

        javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), intrExec.getMessage());
      }

    } catch (java.io.IOException IOexec) {

      javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), IOexec.getMessage());
    }
  }
예제 #12
0
  public void generatePdf(java.lang.String memNo) {

    java.lang.Process wait_for_Pdf2Show;

    java.util.Calendar cal = java.util.Calendar.getInstance();

    java.util.Date dateStampPdf = cal.getTime();

    java.lang.String pdfDateStamp = dateStampPdf.toString();

    try {

      java.io.File tempFile =
          java.io.File.createTempFile("REP" + this.getDateLable() + "_", ".pdf");

      tempFile.deleteOnExit();

      java.lang.Runtime rt = java.lang.Runtime.getRuntime();

      java.lang.String debitTotal = null;

      java.lang.String creditTotal = null;

      com.lowagie.text.Document docPdf = new com.lowagie.text.Document();

      try {

        try {

          com.lowagie.text.pdf.PdfWriter.getInstance(
              docPdf, new java.io.FileOutputStream(tempFile));

          String compName = null;
          String date = null;

          com.lowagie.text.HeaderFooter footer =
              new com.lowagie.text.HeaderFooter(
                  new Phrase("Doctor's Statement (all transactions)- Page: "),
                  true); // FontFactory.getFont(com.lowagie.text.FontFactory.HELVETICA, 12,
                         // Font.BOLDITALIC,java.awt.Color.blue));

          docPdf.setFooter(footer);

          docPdf.open();

          try {

            java.util.Calendar calendar = java.util.Calendar.getInstance();

            long dateNow = calendar.getTimeInMillis();

            java.sql.Date datenowSql = new java.sql.Date(dateNow);

            System.out.println(datenowSql.toString());
            com.lowagie.text.pdf.PdfPTable table1 = new com.lowagie.text.pdf.PdfPTable(7);
            //  com.lowagie.text.Table table = new com.lowagie.text.Table(7);

            // table.endHeaders();

            int headerwidths[] = {15, 15, 30, 15, 15, 15, 15};

            table1.setWidths(headerwidths);
            //  if (docPdf.getPageNumber() > 1) {
            //  table1.setHeaderRows(4);
            //  }
            table1.setWidthPercentage((100));

            table1.getDefaultCell().setBorder(Rectangle.BOTTOM);

            table1.getDefaultCell().setColspan(7);

            Phrase phrase = new Phrase();

            //  table.addCell(phrase);

            table1.getDefaultCell().setColspan(1);
            table1.getDefaultCell().setBackgroundColor(java.awt.Color.WHITE);
            table1.getDefaultCell().setBorderColor(java.awt.Color.WHITE);

            try {
              java.sql.Statement st2 = connectDB.createStatement();

              java.sql.ResultSet rset2 =
                  st2.executeQuery("SELECT rep_currency from pb_hospitalprofile");
              while (rset2.next()) {
                ks = rset2.getObject(1).toString();
              }
              java.sql.Statement st3 = connectDB.createStatement();
              java.sql.ResultSet rset3 = st3.executeQuery("select header_name from pb_header");
              while (rset3.next()) {
                table1.getDefaultCell().setColspan(7);

                table1.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_CENTER);
                phrase = new Phrase(rset3.getObject(1).toString().toUpperCase(), pFontHeader11);
                table1.addCell(phrase);
              }
            } catch (java.sql.SQLException SqlExec) {

              javax.swing.JOptionPane.showMessageDialog(
                  new javax.swing.JFrame(), SqlExec.getMessage());
            }
            docPdf.add(table1);
          } catch (com.lowagie.text.BadElementException BadElExec) {

            javax.swing.JOptionPane.showMessageDialog(
                new javax.swing.JFrame(), BadElExec.getMessage());
          }
          double osBalance1 = 0.00;
          double osBalance = 0.00;
          double current = 0.00;
          double osBalancebf = 0.00;

          try {

            com.lowagie.text.pdf.PdfPTable table = new com.lowagie.text.pdf.PdfPTable(8);

            int headerwidths[] = {12, 30, 10, 20, 10, 10, 10, 13};

            table.setWidths(headerwidths);

            table.setWidthPercentage((100));

            table.setHeaderRows(7);
            table.getDefaultCell().setBorder(Rectangle.BOTTOM);

            table.getDefaultCell().setColspan(8);

            Phrase phrase = new Phrase();

            //  table.addCell(phrase);

            table.getDefaultCell().setColspan(1);
            table.getDefaultCell().setBackgroundColor(java.awt.Color.WHITE);
            table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);

            try {

              java.sql.Statement st = connectDB.createStatement();
              java.sql.Statement st1 = connectDB.createStatement();
              java.sql.Statement st2 = connectDB.createStatement();
              java.sql.Statement st3 = connectDB.createStatement();
              java.sql.Statement st2x = connectDB.createStatement();

              java.sql.ResultSet rset2x =
                  st2x.executeQuery("SELECT rep_currency from pb_hospitalprofile");
              while (rset2x.next()) {
                ks = rset2x.getObject(1).toString();
              }
              java.sql.ResultSet rset3 =
                  st3.executeQuery(
                      "select hospital_name,postal_code||' '||box_no||' '||town,main_telno||' '||other_telno,initcap(street),main_faxno,email,website,room_no from pb_hospitalprofile");

              //  java.sql.ResultSet rset = st.executeQuery("select DISTINCT member_code,
              // member_name,date from shares_transactions order by member_code");
              java.sql.ResultSet rset =
                  st.executeQuery(
                      "select staff_no,initcap(first_name||' '||middle_name),last_name,estate,tel_no from pb_doctors_list where staff_no = '"
                          + memNo
                          + "'");
              java.sql.ResultSet rset1 =
                  st1.executeQuery(
                      "select input_date::date,initcap(patient_no||''||patient_name),initcap(description),CASE WHEN (debit > 0) THEN debit ELSE credit END AS debit,credit_bal,pat_inv,debit_bal from ac_doctors_ledger where acc_no = '"
                          + memNo
                          + "' order by input_date::date"); // union select
                                                            // date::date,initcap(service) as
                                                            // service,dosage,reference,credit from
                                                            // hp_patient_card where patient_no =
                                                            // '"+memNo+"' and credit > 0 order by
                                                            // date");
              java.sql.ResultSet rsetTotals =
                  st2.executeQuery(
                      "select sum(credit_bal) from ac_doctors_ledger where acc_no = '"
                          + memNo
                          + "'");

              table.getDefaultCell().setColspan(8);
              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_CENTER);
              phrase =
                  new Phrase("Doctor's Statement [ All transactions ]".toUpperCase(), pFontHeader1);
              table.addCell(phrase);
              while (rset.next()) {

                table.getDefaultCell().setColspan(4);
                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
                phrase = new Phrase("Account No.:  " + memNo, pFontHeader);
                table.addCell(phrase);
                table.getDefaultCell().setColspan(4);
                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
                phrase =
                    new Phrase(
                        "Residence : " + dbObject.getDBObject(rset.getObject(4), "-"), pFontHeader);
                table.addCell(phrase);

                table.getDefaultCell().setColspan(4);
                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
                phrase =
                    new Phrase(
                        "Doctor's Name : " + dbObject.getDBObject(rset.getObject(2), "-"),
                        pFontHeader);
                table.addCell(phrase);

                table.getDefaultCell().setColspan(4);
                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
                phrase =
                    new Phrase(
                        "Address : " + dbObject.getDBObject(rset.getObject(3), "-"), pFontHeader);
                table.addCell(phrase);

                table.getDefaultCell().setColspan(8);
                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
                phrase =
                    new Phrase(
                        "Tel NO : " + dbObject.getDBObject(rset.getObject(1), "-"), pFontHeader);
                table.addCell(phrase);
              }

              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
              table.getDefaultCell().setBorderColor(java.awt.Color.BLACK);
              table.getDefaultCell().setBorderWidth(Rectangle.TOP);
              table.getDefaultCell().setColspan(1);
              phrase = new Phrase("Date", pFontHeader1);
              table.addCell(phrase);
              phrase = new Phrase("Pat No.", pFontHeader1);
              table.addCell(phrase);

              phrase = new Phrase("Invoice No.", pFontHeader1);
              table.addCell(phrase);

              table.getDefaultCell().setColspan(1);
              phrase = new Phrase("Description", pFontHeader1);
              table.addCell(phrase);

              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);

              table.getDefaultCell().setColspan(1);
              phrase = new Phrase("Orig. Amt", pFontHeader1);
              table.addCell(phrase);
              // table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);

              table.getDefaultCell().setColspan(1);
              phrase = new Phrase("Debit Bal", pFontHeader1);
              table.addCell(phrase);

              phrase = new Phrase("Credit Bal", pFontHeader1);
              table.addCell(phrase);
              phrase = new Phrase("Balance " + ks, pFontHeader1);
              table.addCell(phrase);

              while (rset1.next()) {

                table.getDefaultCell().setColspan(1);
                table.getDefaultCell().setBorderColor(java.awt.Color.WHITE);

                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
                phrase = new Phrase(dbObject.getDBObject(rset1.getObject(1), "-"), pFontHeader);

                table.addCell(phrase);
                table.getDefaultCell().setColspan(1);

                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
                phrase = new Phrase(dbObject.getDBObject(rset1.getObject(2), "-"), pFontHeader);

                table.addCell(phrase);

                table.getDefaultCell().setColspan(1);
                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
                phrase = new Phrase(dbObject.getDBObject(rset1.getObject(6), "-"), pFontHeader);

                table.addCell(phrase);
                table.getDefaultCell().setColspan(1);
                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
                phrase = new Phrase(dbObject.getDBObject(rset1.getObject(3), "-"), pFontHeader);

                table.addCell(phrase);

                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);

                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
                phrase =
                    new Phrase(
                        new com.afrisoftech.sys.Format2Currency()
                            .Format2Currency(rset1.getString(4)),
                        pFontHeader);

                table.addCell(phrase);

                phrase =
                    new Phrase(
                        new com.afrisoftech.sys.Format2Currency()
                            .Format2Currency(rset1.getString(7)),
                        pFontHeader);

                table.addCell(phrase);
                osBalancebf = osBalancebf + rset1.getDouble(7);
                table.getDefaultCell().setColspan(1);
                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);

                phrase =
                    new Phrase(
                        new com.afrisoftech.sys.Format2Currency()
                            .Format2Currency(rset1.getString(5)),
                        pFontHeader);

                table.addCell(phrase);

                osBalance = osBalance + rset1.getDouble(5);

                phrase =
                    new Phrase(
                        new com.afrisoftech.sys.Format2Currency()
                            .Format2Currency(java.lang.String.valueOf(osBalance - osBalancebf)),
                        pFontHeader);
                //   current = current + osBalance;

                table.addCell(phrase);
              }

              table.getDefaultCell().setBorderColor(java.awt.Color.BLACK);

              table.getDefaultCell().setBorder(Rectangle.BOTTOM | Rectangle.TOP);

              while (rsetTotals.next()) {

                table.getDefaultCell().setColspan(6);

                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
                phrase = new Phrase("Total", pFontHeader);

                table.addCell(phrase);

                table.getDefaultCell().setColspan(1);

                table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);

                phrase =
                    new Phrase(
                        new com.afrisoftech.sys.Format2Currency()
                            .Format2Currency(rsetTotals.getString(1)),
                        pFontHeader);

                table.addCell(phrase);
                table.getDefaultCell().setColspan(1);
                phrase =
                    new Phrase(
                        new com.afrisoftech.sys.Format2Currency()
                            .Format2Currency(java.lang.String.valueOf(osBalance - osBalancebf)),
                        pFontHeader);

                table.addCell(phrase);
              }

              docPdf.add(table);

            } catch (java.sql.SQLException SqlExec) {

              javax.swing.JOptionPane.showMessageDialog(
                  new javax.swing.JFrame(), SqlExec.getMessage());
            }

            // }

          } catch (com.lowagie.text.BadElementException BadElExec) {

            javax.swing.JOptionPane.showMessageDialog(
                new javax.swing.JFrame(), BadElExec.getMessage());
          }

        } catch (java.io.FileNotFoundException fnfExec) {

          javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), fnfExec.getMessage());
        }
      } catch (com.lowagie.text.DocumentException lwDocexec) {

        javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), lwDocexec.getMessage());
      }

      docPdf.close();
      com.afrisoftech.lib.PDFRenderer.renderPDF(tempFile);

    } catch (java.io.IOException IOexec) {

      javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), IOexec.getMessage());
    }
  }