Reporting Functionality - fixed generic reports

[habeas.git] / src / habeas / Utility.java

diff --git a/src/habeas/Utility.java b/src/habeas/Utility.java
index 89d06cf..8795204 100644 (file)
--- a/src/habeas/Utility.java
+++ b/src/habeas/Utility.java
@@ -5,7 +5,6 @@
  */
 package habeas;
 
-import java.io.FileNotFoundException;
 import java.io.FileOutputStream;
 import java.io.FileWriter;
 import java.io.IOException;
@@ -16,16 +15,33 @@ import java.sql.*;
 import java.text.DateFormat;
 import java.text.MessageFormat;
 import java.text.SimpleDateFormat;
-import java.util.Arrays;
 import java.util.logging.Level;
 import java.util.logging.Logger;
-import jdk.dynalink.linker.support.Guards;
 
 /**
  *
  * @author hari
  */
 public class Utility {
+
+    private static final String[] REPORTS_UNFILTERED = { "SELECT ReferenceNumber, Description"
+            + ", BillDate, BillAmount, ClientName FROM legalnotices INNER JOIN "
+            + "clients ON ClientId=clients.id WHERE BillStatus='AWAITING PAYMENT';", 
+    
+            "SELECT ReferenceNumber, Description, SentDate, RPADReference, "
+            + "ClientName FROM legalnotices INNER JOIN clients ON ClientId=clients.id"
+            + " WHERE NoticeSent=1 AND NoticeDelivered=0;"
+    } ;
+
+    private static final String[] REPORTS_FILTERED = { "SELECT ReferenceNumber, Description"
+            + ", BillDate, BillAmount, ClientName FROM legalnotices INNER JOIN "
+            + "clients ON ClientId=clients.id WHERE BillStatus='AWAITING PAYMENT' "
+            + "AND ClientId=?;", 
+    
+            "SELECT ReferenceNumber, Description, SentDate, RPADReference, "
+            + "ClientName FROM legalnotices INNER JOIN clients ON ClientId=clients.id"
+            + " WHERE NoticeSent=1 AND NoticeDelivered=0 AND ClientId=?;"
+    } ;    
     
     static void saveStationerySettings (String left_header, 
             String right_header, String signatory) {
@@ -503,7 +519,7 @@ public class Utility {
         
     }
 
-    static ArrayList<Object> getPendingPaymentBills(int clientid) {
+    static ArrayList<Object> getReportData(int report_num, int clientid) {
         ArrayList<Object> data = new ArrayList<>();
         try {
             Connection conn = DriverManager.getConnection(JDBC + connectionURL);
@@ -512,26 +528,24 @@ public class Utility {
             ResultSet rs;
             if (clientid == -1) {
                 Statement st = conn.createStatement();                
-                rs = st.executeQuery("SELECT ReferenceNumber, Description, "
-                    + "BillDate, BillAmount, ClientName"
-                    + " FROM legalnotices INNER JOIN clients ON ClientId=clients.id "
-                    + "WHERE BillStatus='AWAITING PAYMENT';");
+                rs = st.executeQuery(REPORTS_UNFILTERED[report_num]);
             }
             else {
-                PreparedStatement st = conn.prepareStatement("SELECT ReferenceNumber, "
-                        + "Description, BillDate, BillAmount, ClientName"
-                    + " FROM legalnotices INNER JOIN clients ON ClientId=clients.id "
-                    + "WHERE BillStatus='AWAITING PAYMENT' AND ClientId=?;");
+                PreparedStatement st = conn.prepareStatement(REPORTS_FILTERED[report_num]);
                 st.setInt (1, clientid);
                 rs = st.executeQuery();
             }
-            DateFormat fmt = new SimpleDateFormat ("dd/MM/yyyy");
+
             while (rs.next()) {
-                data.add (rs.getString("ReferenceNumber"));
-                data.add (rs.getString("Description"));
-                data.add (fmt.format (getValidDate(rs, "BillDate")));
-                data.add (rs.getInt ("BillAmount"));
-                data.add (rs.getString("ClientName"));
+                ResultSetMetaData md =  rs.getMetaData();
+                SimpleDateFormat fmt = new SimpleDateFormat ("dd MMM yyyy");
+                for (int i = 1; i <= md.getColumnCount(); i ++ ) {
+                    // for INTEGER Columns which are date alone, handle separately
+                    if (md.getColumnName(i).contains("Date"))
+                        data.add (fmt.format(getValidDate(rs, md.getColumnName(i))));
+                    else
+                        data.add (rs.getObject (i));
+                }
             }
             conn.close ();
             return data;
@@ -578,6 +592,8 @@ public class Utility {
         else
             return String.format("\"%s\"", bareStr.toString());
     }
+
+
     public Utility () {
         
     }