X-Git-Url: https://harishankar.org/repos/?p=habeas.git;a=blobdiff_plain;f=src%2Fhabeas%2FUtility.java;h=14edb1f0e0ae635693b1e690eea8523ac631e8df;hp=89d06cf8eb3824c5db935ce6fe9da2aa03e6faed;hb=HEAD;hpb=06a2c4c692e08fdc860e489c86f34b2fe64696d1

diff --git a/src/habeas/Utility.java b/src/habeas/Utility.java
index 89d06cf..14edb1f 100644
--- 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,66 @@ 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;
+import org.sqlite.util.StringUtils;
 
 /**
  *
  * @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;",
+            
+            "SELECT ReferenceNumber, Description, EntrustmentDate, ClientName FROM legalnotices"
+            + " INNER JOIN clients ON ClientId=clients.id"
+            + " WHERE DraftCreated=1 AND DraftApproved=0;",
+            
+            "SELECT ReferenceNumber, Description, EntrustmentDate, "
+            + "ClarificationRemarks, ClientName FROM legalnotices INNER JOIN clients ON"
+            + " ClientId=clients.id WHERE ClarificationPending=1;",
+            
+            "SELECT ReferenceNumber, Description, EntrustmentDate, ClarificationRemarks"
+            + ", ClientName from legalnotices INNER JOIN clients ON ClientId=clients.id "
+            + "WHERE DraftCreated=0;",
+
+            "SELECT ReferenceNumber, Description, EntrustmentDate, ClientName FROM"
+            + " legalnotices INNER JOIN clients ON ClientId=clients.id WHERE "
+            + " DraftApproved=1 AND NoticeSent=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=?;",
+            
+            "SELECT ReferenceNumber, Description, EntrustmentDate, ClientName FROM legalnotices"
+            + " INNER JOIN clients ON ClientId=clients.id"
+            + " WHERE DraftCreated=1 AND DraftApproved=0 AND ClientId=?;",
+
+            "SELECT ReferenceNumber, Description, EntrustmentDate, "
+            + "ClarificationRemarks, ClientName FROM legalnotices INNER JOIN clients ON"
+            + " ClientId=clients.id WHERE ClarificationPending=1 AND ClientId=?;",
+
+            "SELECT ReferenceNumber, Description, EntrustmentDate, ClarificationRemarks"
+            + ", ClientName from legalnotices INNER JOIN clients ON ClientId=clients.id "
+            + "WHERE DraftCreated=0 AND ClientId=?;",            
+
+            "SELECT ReferenceNumber, Description, EntrustmentDate, ClientName FROM"
+            + " legalnotices INNER JOIN clients ON ClientId=clients.id WHERE "
+            + " DraftApproved=1 AND NoticeSent=0 AND ClientId=?;"            
+    } ;    
     
     static void saveStationerySettings (String left_header, 
             String right_header, String signatory) {
@@ -306,14 +355,14 @@ public class Utility {
     }
 
     static boolean updateNoticeBillDetails(int selid, String bill_status, 
-            java.util.Date bill_date, int bill_amount) {
+            java.util.Date bill_date, long bill_amount) {
         try {
             Connection conn = DriverManager.getConnection(JDBC + connectionURL);
             PreparedStatement st = conn.prepareStatement("UPDATE legalnotices"
                     + " SET BillStatus=?,BillDate=?,BillAmount=? WHERE id=?;");
             st.setString (1, bill_status);
             st.setDate(2, toSqlDate(bill_date));
-            st.setInt(3, bill_amount);
+            st.setLong(3, bill_amount);
             st.setInt(4, selid);
             st.execute();
             conn.close();
@@ -503,7 +552,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 +561,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;
@@ -546,13 +593,13 @@ public class Utility {
         try {
             FileWriter f = new FileWriter (filePath);
             for (int i = 0; i < reportCols.length; i ++) {
-                f.append (escapeQuote(reportCols[i]));
+                f.append (escapeQuoteCSV(reportCols[i]));
                 f.append (",");
             }
             f.append("\n");
             for (int i = 0; i < reportData.size(); i += reportCols.length) {
                 for (int j = 0; j < reportCols.length; j ++) {
-                    f.append (escapeQuote(reportData.get(i+j)));
+                    f.append (escapeQuoteCSV(reportData.get(i+j)));
                     f.append (",");
                 }
                 f.append ("\n");
@@ -567,8 +614,10 @@ public class Utility {
         
     }
 
-    private static String escapeQuote(Object bareStr) {
-        if (bareStr.getClass() == String.class) {
+    private static String escapeQuoteCSV(Object bareStr) {
+        if (bareStr == null) 
+            return "";
+        else if (bareStr.getClass() == String.class) {
             String str = (String)bareStr;
             String escapedStr = str.replace ("\"", "\"\"");
             System.out.println(escapedStr);
@@ -578,6 +627,62 @@ public class Utility {
         else
             return String.format("\"%s\"", bareStr.toString());
     }
+
+    static boolean saveReportHTML(String filepath, String report_title, 
+            String[] report_cols, ArrayList<Object> report_data) {
+        FileWriter htmlFile;
+        try {
+            htmlFile = new FileWriter(filepath);
+            htmlFile.append("<!DOCTYPE html>\n<html lang=\"en\">\n<head>\n<title>");
+            htmlFile.append(htmlEscape(report_title));
+            htmlFile.append("</title>\n</head>\n");
+            htmlFile.append ("<body>\n");
+            htmlFile.append("<h1>");
+            htmlFile.append(htmlEscape(report_title));
+            htmlFile.append("</h1>\n");
+            htmlFile.append ("<table>\n");
+            htmlFile.append ("\t<tr>\n");
+            for (int i = 0; i < report_cols.length; i ++) {
+                htmlFile.append ("\t\t<th>");
+                htmlFile.append (htmlEscape(report_cols[i]));
+                htmlFile.append("</th>\n");
+            }
+            htmlFile.append ("\t</tr>\n");
+            for (int r = 0; r < report_data.size(); r += report_cols.length) {
+                htmlFile.append("\t<tr>\n");
+                for (int c = 0; c < report_cols.length; c ++) {
+                    htmlFile.append("\t\t<td>");
+                    htmlFile.append(htmlEscape(report_data.get(r+c)));
+                    htmlFile.append("</td>\n");
+                }
+                htmlFile.append ("\t</tr>\n");
+            }
+            htmlFile.append("</table>\n");
+            htmlFile.append("</body>\n");
+            htmlFile.append("</html>");
+            htmlFile.flush();
+            htmlFile.close();
+            return true;
+        } catch (IOException ex) {
+            Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
+            return false;
+        }
+    }
+
+    private static String htmlEscape(Object barestr) {
+        if (barestr == null)
+            return "";
+        else if (barestr.getClass() == String.class) {
+            String barestrs = (String)barestr;
+            String str = barestrs.replace("&", "&amp;").replace("<", "&lt;").
+                    replace(">", "&gt;").replace("\"", "&quot;").replace("\n", "<br>");
+            return str;
+        }
+        else
+            return barestr.toString();
+    }
+
+
     public Utility () {
         
     }