public void generatePdf() {

    java.lang.Process wait_for_Pdf2Show;

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

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

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

    try {

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

      tempFile.deleteOnExit();

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

      java.lang.String debitTotal = null;

      java.lang.String creditTotal = null;

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

      try {

        try {
          Image img = Image.getInstance(com.afrisoftech.lib.CompanyLogo.getPath2Logo());

          // Image imgWaterMark = Image.getInstance(System.getProperty("company.watermark"));

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

          String compName = null;

          String District = null;

          String Region = null;

          String date = null;

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

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

            headerFoter.setAlignment(com.lowagie.text.HeaderFooter.ALIGN_CENTER);

            headerFoter.setRight(5);

            docPdf.setHeader(headerFoter);

          } catch (java.sql.SQLException SqlExec) {

            SqlExec.printStackTrace();

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

          com.lowagie.text.HeaderFooter footer =
              new com.lowagie.text.HeaderFooter(
                  new Phrase("MOH - 204B : OP Over 5 Yrs Register : Page: "),
                  true); // FontFactory.getFont(com.lowagie.text.FontFactory.HELVETICA, 12,
          // Font.BOLDITALIC,java.awt.Color.blue));

          docPdf.setFooter(footer);

          docPdf.open();

          try {

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

            int headerwidths[] = {
              5, 10, 10, 10, 25, 10, 12, 20, 12, 12, 12, 12, 12, 12, 12, 25, 25, 12, 12, 12, 25
            };

            table.setWidths(headerwidths);

            table.setWidthPercentage((100));

            table.setHeaderRows(4);

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

            try {

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

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

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

              com.afrisoftech.lib.DateFormatter dateFormatter =
                  new com.afrisoftech.lib.DateFormatter(
                      dateFormat.parse(endDate.toLocaleString()), "MMMM");

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

              com.afrisoftech.lib.DateFormatter dateFormatters =
                  new com.afrisoftech.lib.DateFormatter(
                      dateFormat.parse(endDate.toLocaleString()), "yyyy");

              java.lang.String yearString = dateFormatters.getDateString();

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

              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_CENTER);
              table.getDefaultCell().setFixedHeight(50);
              table.addCell(Image.getInstance(com.afrisoftech.lib.CompanyLogo.getPath2Logo()));
              table.getDefaultCell().setFixedHeight(-1);
              // table.getDefaultCell().setNoWrap(false);
              phrase = new Phrase("MOH 204B_Over 5 Yrs Register", pFontHeader2);

              table.addCell(phrase);

              table.getDefaultCell().setColspan(5);
              table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);

              phrase = new Phrase("OP OVER 5 YEARS REGISTER", pFontHeader22);
              table.addCell(phrase);

              table.getDefaultCell().setColspan(10);
              phrase = new Phrase("HEALTH FACILITY : " + compName, pFontHeader22);
              table.addCell(phrase);

              table.getDefaultCell().setColspan(3);
              phrase = new Phrase("MONTH : " + monthString.toUpperCase(), pFontHeader22);
              table.addCell(phrase);

              table.getDefaultCell().setColspan(3);
              phrase = new Phrase("YEAR : " + yearString.toUpperCase(), pFontHeader22);
              table.addCell(phrase);

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

              table.getDefaultCell().setColspan(1);

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

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

              table.getDefaultCell().setColspan(1);

              phrase = new Phrase("#", pFontHeader11);

              table.addCell(phrase);

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

              phrase = new Phrase("OPD No.", pFontHeader11);
              table.addCell(phrase);

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

              phrase = new Phrase("Full Names", pFontHeader11);
              table.addCell(phrase);

              phrase = new Phrase("Age YRS", pFontHeader11);
              table.addCell(phrase);

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

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

              phrase = new Phrase("Phone. No.", pFontHeader11);
              table.addCell(phrase);

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

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

              phrase = new Phrase("Visual Activity RE(Right Eye), LE(LEFT EYE)", pFontHeader11);
              table.addCell(phrase);

              phrase = new Phrase("HIV: 1=Cancelled, 2=Tested", pFontHeader11);
              table.addCell(phrase);

              phrase =
                  new Phrase(
                      "HIV Status: 1=Known Positive, 2=Positive this visit, 3=Negative, 4=Uknown",
                      pFontHeader11);
              table.addCell(phrase);

              phrase =
                  new Phrase(
                      "Nutrition Status: 1=Nutrition Education, 2=Nutrition Suppliments",
                      pFontHeader11);
              table.addCell(phrase);

              phrase = new Phrase("Diagnosis/Classification", pFontHeader11);
              table.addCell(phrase);

              phrase = new Phrase("Treatment/Prescription No.", pFontHeader11);
              table.addCell(phrase);

              phrase = new Phrase("Amount Charged", pFontHeader11);
              table.addCell(phrase);

              phrase = new Phrase("Receipt No.", pFontHeader11);
              table.addCell(phrase);

              phrase =
                  new Phrase(
                      "1 = From Other HF, 2 = To Other HF, 3 = From CU, 4 = To CU", pFontHeader11);
              table.addCell(phrase);

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

              // Second header row

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

            } catch (java.text.ParseException psExec) {

              psExec.printStackTrace();

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

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

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

            try {

              int noSeq = 0;

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

              System.out.println("The array has been called...");

              for (int i = 0; i < listofAct.length; i++) {

                java.sql.PreparedStatement stw =
                    connectDB.prepareStatement(
                        "SELECT DISTINCT "
                            + " date_part('day', date) ||'-'||date_part('month', date) ||'-'||date_part('year', date), patient_no,"
                            + " (CASE WHEN comments = 'Old' THEN 'YES' ELSE 'NO' END) as revisit, "
                            + " initcap(name), age::int, (SELECT initcap(gender) FROM hp_patient_register WHERE hp_patient_register.patient_no = hp_patient_visit.patient_no ORDER BY 1 DESC LIMIT 1) as gender, "
                            + " (SELECT initcap(residence) FROM hp_inpatient_register"
                            + " WHERE hp_patient_visit.patient_no = hp_inpatient_register.patient_no"
                            + " UNION SELECT initcap(residence) FROM hp_patient_register WHERE "
                            + " hp_patient_visit.patient_no = hp_patient_register.patient_no ORDER BY 1 "
                            + " DESC LIMIT 1) as village, (SELECT tel_no FROM hp_patient_register WHERE hp_patient_register.patient_no = hp_patient_visit.patient_no "
                            + " ORDER BY 1 DESC LIMIT 1) as telephone, (SELECT weight::numeric(5,1) FROM hp_signs_record WHERE hp_signs_record.patient_no = hp_patient_visit.patient_no  AND hp_signs_record.date = hp_patient_visit.date ORDER BY 1 DESC LIMIT 1) as weight,"
                            + " (SELECT height::numeric(5,1) FROM hp_signs_record WHERE hp_signs_record.patient_no = hp_patient_visit.patient_no AND hp_signs_record.date = hp_patient_visit.date  ORDER BY 1 DESC LIMIT 1) as height, "
                            + " ''," // to add eye conditions for over 5 years
                            + " (SELECT (CASE WHEN hiv_counselled = true THEN '1' ELSE '' END) || ' '||"
                            + "(CASE WHEN hiv_tested = true THEN '2' ELSE '' END) FROM key_health_indicators"
                            + " WHERE key_health_indicators.patient_no = hp_patient_visit.patient_no) as hiv_tested,"
                            + " (SELECT (CASE WHEN hiv_status ilike '%positive%' THEN '1' ELSE '' END) || ' '||"
                            + "(CASE WHEN hiv_status ilike '%visit' THEN '2' ELSE '' END)||' '||"
                            + "(CASE WHEN hiv_status ilike 'negative' THEN '3' ELSE '' END)||' '||"
                            + "(CASE WHEN hiv_status ilike 'unknown' THEN '4' ELSE '' END) FROM key_health_indicators"
                            + " WHERE key_health_indicators.patient_no = hp_patient_visit.patient_no) as hiv_status,"
                            + " (SELECT (CASE WHEN nutrition_status ilike '%education%' THEN '1' ELSE '' END)||' '||"
                            + "(CASE WHEN nutrition_status ILIKE '%suppliments%' THEN '2' ELSE '' END) "
                            + "FROM key_health_indicators WHERE key_health_indicators.patient_no = hp_patient_visit.patient_no)"
                            + " as nutrition_status, "
                            + " (SELECT disease FROM hp_patient_diagnosis WHERE hp_patient_diagnosis.patient_no = hp_patient_visit.patient_no AND"
                            + " hp_patient_diagnosis.date_recorded::date = hp_patient_visit.date::date AND disease is not null ORDER BY 1 DESC LIMIT 1) as diagnosis, "
                            + " (SELECT replace(treatment, 'null', '') FROM hp_clinical_results WHERE hp_clinical_results.patient_no ="
                            + " hp_patient_visit.patient_no AND hp_clinical_results.date = hp_patient_visit.date "
                            + "ORDER BY 1 DESC LIMIT 1) as treatment, "
                            + " (SELECT sum(credit-debit) FROM ac_ledger WHERE ac_ledger.patient_no = "
                            + "hp_patient_visit.patient_no AND ac_ledger.date = hp_patient_visit.date) "
                            + "as amount, '', "
                            + " (SELECT refer_source FROM hp_patient_register WHERE hp_patient_register.patient_no"
                            + " = hp_patient_visit.patient_no AND hp_patient_register.date = hp_patient_visit.date"
                            + " ORDER BY 1 DESC LIMIT 1) as referral,"
                            + " (SELECT comments FROM hp_clinical_results WHERE hp_clinical_results.patient_no "
                            + "= hp_patient_visit.patient_no AND hp_clinical_results.date = hp_patient_visit.date"
                            + " ORDER BY 1 DESC LIMIT 1 )as comments FROM hp_patient_visit WHERE "
                            + " date::date BETWEEN '"
                            + beginDate
                            + "' AND '"
                            + endDate
                            + "' "
                            + " AND patient_no = ?");

                stw.setObject(1, listofAct[i]);

                java.sql.ResultSet rsetw = stw.executeQuery();

                while (rsetw.next()) {

                  table.getDefaultCell().setColspan(1);

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

                  table.getDefaultCell().setColspan(1);
                  noSeq = noSeq + 1;
                  // Index
                  phrase = new Phrase(java.lang.String.valueOf(noSeq), pFontHeader1);
                  table.addCell(phrase);
                  // Lab procedure date
                  phrase =
                      new Phrase(
                          new com.afrisoftech.lib.DBObject().getDBObject(rsetw.getString(1), ""),
                          pFontHeader1);
                  table.addCell(phrase);
                  // Patient Number
                  phrase =
                      new Phrase(
                          new com.afrisoftech.lib.DBObject().getDBObject(rsetw.getString(2), ""),
                          pFontHeader1);
                  table.addCell(phrase);
                  // Lab Number
                  phrase =
                      new Phrase(
                          new com.afrisoftech.lib.DBObject().getDBObject(rsetw.getString(3), ""),
                          pFontHeader1);
                  table.addCell(phrase);
                  // Revisit No
                  phrase =
                      new Phrase(
                          new com.afrisoftech.lib.DBObject().getDBObject(rsetw.getString(4), ""),
                          pFontHeader1);
                  table.addCell(phrase);
                  // Patient names
                  phrase =
                      new Phrase(
                          new com.afrisoftech.lib.DBObject().getDBObject(rsetw.getString(5), ""),
                          pFontHeader1);
                  table.addCell(phrase);
                  // Age
                  phrase =
                      new Phrase(
                          new com.afrisoftech.lib.DBObject().getDBObject(rsetw.getString(6), ""),
                          pFontHeader1);
                  table.addCell(phrase);
                  // Gender
                  phrase =
                      new Phrase(
                          new com.afrisoftech.lib.DBObject().getDBObject(rsetw.getString(7), ""),
                          pFontHeader1);
                  table.addCell(phrase);

                  // Village
                  phrase =
                      new Phrase(
                          new com.afrisoftech.lib.DBObject().getDBObject(rsetw.getString(8), ""),
                          pFontHeader1);
                  table.addCell(phrase);
                  // Telephone number
                  phrase =
                      new Phrase(
                          new com.afrisoftech.lib.DBObject().getDBObject(rsetw.getString(9), ""),
                          pFontHeader1);
                  table.addCell(phrase);
                  // Clinical diagnosis
                  phrase =
                      new Phrase(
                          new com.afrisoftech.lib.DBObject().getDBObject(rsetw.getString(10), ""),
                          pFontHeader1);
                  table.addCell(phrase);
                  // Prior treatment
                  phrase =
                      new Phrase(
                          new com.afrisoftech.lib.DBObject().getDBObject(rsetw.getString(11), ""),
                          pFontHeader1);
                  table.addCell(phrase);
                  // Type of specimen
                  phrase =
                      new Phrase(
                          new com.afrisoftech.lib.DBObject().getDBObject(rsetw.getString(12), ""),
                          pFontHeader1);
                  table.addCell(phrase);
                  // Condition of specimen
                  phrase =
                      new Phrase(
                          new com.afrisoftech.lib.DBObject().getDBObject(rsetw.getString(13), ""),
                          pFontHeader1);
                  table.addCell(phrase);
                  // Investigation required
                  phrase =
                      new Phrase(
                          new com.afrisoftech.lib.DBObject().getDBObject(rsetw.getString(14), ""),
                          pFontHeader1);
                  table.addCell(phrase);
                  // Date sample taken
                  phrase =
                      new Phrase(
                          new com.afrisoftech.lib.DBObject().getDBObject(rsetw.getString(15), ""),
                          pFontHeader1);
                  table.addCell(phrase);
                  // Date sample received
                  phrase =
                      new Phrase(
                          new com.afrisoftech.lib.DBObject().getDBObject(rsetw.getString(16), ""),
                          pFontHeader1);
                  table.addCell(phrase);
                  // Amount Paid by client
                  table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_RIGHT);
                  phrase =
                      new Phrase(
                          new com.afrisoftech.lib.DBObject().getDBObject(rsetw.getString(17), ""),
                          pFontHeader1);
                  table.addCell(phrase);
                  // Date sample analysed
                  table.getDefaultCell().setHorizontalAlignment(PdfCell.ALIGN_LEFT);
                  phrase =
                      new Phrase(
                          new com.afrisoftech.lib.DBObject().getDBObject(rsetw.getString(18), ""),
                          pFontHeader1);
                  table.addCell(phrase);
                  // Results
                  phrase =
                      new Phrase(
                          new com.afrisoftech.lib.DBObject().getDBObject(rsetw.getString(19), ""),
                          pFontHeader1);
                  table.addCell(phrase);
                  // Date result dispatched
                  phrase =
                      new Phrase(
                          new com.afrisoftech.lib.DBObject().getDBObject(rsetw.getString(20), ""),
                          pFontHeader1);
                  table.addCell(phrase);
                }
              }

              docPdf.add(table);

            } catch (java.sql.SQLException SqlExec) {

              SqlExec.printStackTrace();

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

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

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

        } catch (java.io.FileNotFoundException fnfExec) {

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

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

      docPdf.close();

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

    } catch (java.io.IOException IOexec) {

      IOexec.printStackTrace();

      javax.swing.JOptionPane.showMessageDialog(new javax.swing.JFrame(), IOexec.getMessage());
    }
  }
  public java.lang.Object[] getListofStaffNos() {

    java.lang.Object[] listofStaffNos = null;

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

    try {

      java.sql.PreparedStatement stmt1 =
          connectDB.prepareStatement(
              "SELECT DISTINCT patient_no, name FROM hp_patient_visit where date::date BETWEEN '"
                  + beginDate
                  + "' AND '"
                  + endDate
                  + "' AND age::int >= 5 ORDER BY name ASC");

      java.sql.ResultSet rSet1 = stmt1.executeQuery();

      while (rSet1.next()) {

        System.out.println("Patient names : [" + rSet1.getString(2) + "]");

        listStaffNoVector.addElement(rSet1.getObject(1).toString());
      }

    } catch (java.sql.SQLException sqlExec) {

      sqlExec.printStackTrace();

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

    listofStaffNos = listStaffNoVector.toArray();

    System.out.println("Done list of Staff Nos ...");

    return listofStaffNos;
  }