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

diff --git a/src/habeas/Utility.java b/src/habeas/Utility.java
index 8b27e7e..14edb1f 100644
--- a/src/habeas/Utility.java
+++ b/src/habeas/Utility.java
@@ -5,11 +5,19 @@
  */
 package habeas;
 
+import java.io.FileOutputStream;
+import java.io.FileWriter;
+import java.io.IOException;
+import java.io.InputStream;
 import java.util.ArrayList;
 import java.util.prefs.Preferences;
 import java.sql.*;
+import java.text.DateFormat;
+import java.text.MessageFormat;
+import java.text.SimpleDateFormat;
 import java.util.logging.Level;
 import java.util.logging.Logger;
+import org.sqlite.util.StringUtils;
 
 /**
  *
@@ -17,15 +25,85 @@ import java.util.logging.Logger;
  */
 public class Utility {
 
-    static void setConnectionURL(String text) {
+    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) {
+        leftLetterHeader = left_header;
+        rightLetterHeader = right_header;
+        signatoryName = signatory;
+        Preferences myPrefs = Preferences.userRoot().node("org/harishankar/Habeas");
+        myPrefs.put ("LeftHeader", left_header);
+        myPrefs.put ("RightHeader", right_header);
+        myPrefs.put ("Signatory", signatory);
+    }
+    
+    static void retrieveStationerySettings () {
+        Preferences myPrefs = Preferences.userRoot().node ("org/harishankar/Habeas");
+        leftLetterHeader = myPrefs.get("LeftHeader", "Left Header");
+        rightLetterHeader = myPrefs.get ("RightHeader", "Right Header");
+        signatoryName = myPrefs.get ("Signatory", "Signatory Name");
+    }
+
+    static void saveConnectionURL(String text) {
         connectionURL = text;
-        Preferences.userRoot().put("ConnectionURL", text);
+        Preferences myPrefs = Preferences.userRoot().node ("org/harishankar/Habeas");
+        myPrefs.put("ConnectionURL", text);
     }
 
     static ArrayList<Object> getClientDetails(int r) {
         ArrayList<Object> res = new ArrayList <>();
         try {
-            Connection conn = DriverManager.getConnection("jdbc:sqlite:"+connectionURL);
+            Connection conn = DriverManager.getConnection(JDBC+connectionURL);
             PreparedStatement st = conn.prepareStatement("SELECT * FROM clients WHERE id=?;");
             st.setInt(1, r);
             ResultSet rs = st.executeQuery();
@@ -51,7 +129,7 @@ public class Utility {
         if ("".equals(client_name)) 
             return false;
         try {
-            Connection conn = DriverManager.getConnection("jdbc:sqlite:" + connectionURL);
+            Connection conn = DriverManager.getConnection(JDBC + connectionURL);
             PreparedStatement st = conn.prepareStatement("INSERT INTO clients (ClientName,"
                     + "ClientAddress, ContactPerson, MailID, ContactNumber) VALUES (?, ?, ?, ?, ?);");
             st.setString(1, client_name);
@@ -73,7 +151,7 @@ public class Utility {
 
     static boolean deleteClient(int r) {        
         try {
-            Connection conn = DriverManager.getConnection("jdbc:sqlite:" + connectionURL);
+            Connection conn = DriverManager.getConnection(JDBC + connectionURL);
             PreparedStatement st = conn.prepareStatement("DELETE FROM clients WHERE id=?;");
             PreparedStatement st2 = conn.prepareStatement("DELETE FROM legalnotices WHERE ClientId=?;");
             st.setInt(1, r);
@@ -94,7 +172,7 @@ public class Utility {
         if ("".equals(client_name)) 
                 return false;
         try {
-            Connection conn = DriverManager.getConnection("jdbc:sqlite:" + connectionURL);
+            Connection conn = DriverManager.getConnection(JDBC + connectionURL);
             PreparedStatement st = conn.prepareStatement("UPDATE clients SET "
                     + "ClientName=?, ClientAddress=?,"
                     + "ContactPerson=?, MailID=?, ContactNumber=? WHERE id=?;");
@@ -118,10 +196,10 @@ public class Utility {
     static boolean addLegalNotice(String reference_number, 
             String description, java.util.Date entrustment_date, DBItem client) {
         if ("".equals(reference_number) || "".equals(description) ||
-                entrustment_date == null)
+                entrustment_date == null || client == null)
             return false;
         try {
-            Connection conn = DriverManager.getConnection("jdbc:sqlite:" + connectionURL);
+            Connection conn = DriverManager.getConnection(JDBC + connectionURL);
             PreparedStatement st = conn.prepareStatement("INSERT INTO legalnotices"
                     + " (ReferenceNumber, Description, EntrustmentDate, ClientId) "
                     + "VALUES (?, ?, ?, ?);");
@@ -139,10 +217,479 @@ public class Utility {
         }
         
     }
+
+    static ArrayList<Object> getNotices() {
+        ArrayList<Object> notices = new ArrayList<>();
+        try {
+            Connection conn = DriverManager.getConnection(JDBC + connectionURL);
+            Statement st = conn.createStatement();
+            ResultSet rs = st.executeQuery("SELECT id, ReferenceNumber, Description "
+                    + "FROM legalnotices;");
+            while (rs.next()) {
+                notices.add(rs.getInt("id"));
+                notices.add(rs.getString("ReferenceNumber"));
+                notices.add(rs.getString("Description"));
+            }
+            conn.close();
+            return notices;
+        } catch (SQLException ex) {
+            Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
+            return null;
+        }
+        
+    }
+    static java.util.Date getValidDate (ResultSet rs, String datefield) throws SQLException {
+        // Since resultset.getDate returns a java.sql.Date, we need a way to get
+        // the date in java.util.Date which is usable in our application. Hence
+        // this helper function. Since we don't want a valid Date object if the 
+        // field is null, we are using this if clause
+        if (rs.getDate(datefield) == null)
+            return null;
+        else
+            return (new java.util.Date(rs.getLong(datefield)*1000)); 
+    }
+
+    static ArrayList<Object> getNoticeDetails(int selid) {
+        ArrayList<Object> notice = new ArrayList<>();
+        try {
+            Connection conn = DriverManager.getConnection(JDBC + connectionURL);
+            PreparedStatement st = conn.prepareStatement("SELECT legalnotices.*, clients.ClientName "
+                    + "FROM legalnotices "
+                    + "INNER JOIN clients WHERE ClientId=clients.id AND legalnotices.id=?;");
+            st.setInt(1, selid);
+            ResultSet rs = st.executeQuery();
+            while (rs.next()) {
+                notice.add(rs.getString("ReferenceNumber"));
+                notice.add(rs.getString("Description"));
+                notice.add(getValidDate(rs, "EntrustmentDate"));
+                notice.add (rs.getInt ("ClientId"));
+                notice.add (rs.getBoolean("DraftCreated"));
+                notice.add (rs.getBoolean("DraftApproved"));
+                notice.add (rs.getBoolean("NoticeSent"));
+                notice.add (getValidDate(rs, "SentDate"));
+                notice.add (rs.getString("RPADReference"));
+                notice.add (rs.getBoolean("NoticeDelivered"));
+                notice.add (getValidDate(rs, "DeliveryDate"));
+                notice.add (rs.getString("BillStatus"));
+                notice.add (rs.getInt ("BillAmount"));
+                notice.add (getValidDate (rs, "BillDate"));
+                notice.add (rs.getBoolean ("ClarificationPending"));
+                notice.add (rs.getString("ClarificationRemarks"));
+                notice.add (rs.getString("ClientName"));
+            }
+           return notice;
+        } catch (SQLException ex) {
+            Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
+            return null;
+        }
+        
+
+    }
+    private static final String JDBC = "jdbc:sqlite:";
+
+    static boolean updateNoticeDraftStatus(int selectednotice_id, boolean created, boolean approved) {
+        try {
+            Connection conn = DriverManager.getConnection(JDBC + connectionURL);
+            PreparedStatement st = conn.prepareStatement("UPDATE legalnotices "
+                    + "SET DraftCreated=?, DraftApproved=? WHERE id=?;");
+            st.setBoolean(1, created);
+            st.setBoolean(2, approved);
+            st.setInt (3, selectednotice_id);
+            st.execute();
+            conn.close();
+            return true;
+        } catch (SQLException ex) {
+            Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
+            return false;
+        }
+        
+    }
+
+    static boolean updateNoticeDescription(int selectednotice_id, String text) {
+        try {
+            Connection conn = DriverManager.getConnection(JDBC + connectionURL);
+            PreparedStatement st = conn.prepareStatement("UPDATE legalnotices "
+                    + "SET Description=? WHERE id=?;");
+            st.setString(1, text);
+            st.setInt(2, selectednotice_id);
+            st.execute();
+            conn.close();
+            return true;
+        } catch (SQLException ex) {
+            Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
+            return false;
+        }
+        
+    }
+
+    static boolean updateNoticeDispatchDetails(int selid, boolean notice_sent, 
+            java.util.Date sent_date, String rpad_reference, 
+            boolean notice_delivered, java.util.Date delivery_date) {
+        try {
+            Connection conn = DriverManager.getConnection(JDBC + connectionURL);
+            PreparedStatement st = conn.prepareStatement("UPDATE legalnotices"
+                    + " SET NoticeSent=?, SentDate=?, RPADReference=?,"
+                    + " NoticeDelivered=?, DeliveryDate=? WHERE id=?;");
+            
+            st.setBoolean(1, notice_sent);
+            st.setDate(2,  toSqlDate(sent_date));
+            st.setString(3, rpad_reference);
+            st.setBoolean(4, notice_delivered);
+            st.setDate(5, toSqlDate(delivery_date));
+            st.setInt (6, selid);
+            st.execute();
+            conn.close();
+            return true;
+        } catch (SQLException ex) {
+            Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
+            return false;
+        }
+        
+    }
+
+    private static Date toSqlDate(java.util.Date date) {
+            if (date == null)
+                return null;
+            Date sqldate = new Date (date.getTime()/1000);
+            return sqldate;
+    }
+
+    static boolean updateNoticeBillDetails(int selid, String bill_status, 
+            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.setLong(3, bill_amount);
+            st.setInt(4, selid);
+            st.execute();
+            conn.close();
+            return true;
+        } catch (SQLException ex) {
+            Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
+            return false;
+        }
+        
+    }
+
+    static boolean createDatabase(String db_path) {
+        saveConnectionURL(db_path);
+        String tblClients = "CREATE TABLE IF NOT EXISTS \"clients\" (\n" +
+"	\"id\"	INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n" +
+"	\"ClientName\"	VARCHAR(255) NOT NULL UNIQUE,\n" +
+"	\"ClientAddress\"	TEXT,\n" +
+"	\"ContactPerson\"	TEXT,\n" +
+"	\"MailID\"	VARCHAR(255),\n" +
+"	\"ContactNumber\"	VARCHAR(30)\n" +
+"       );";
+        String tblNotices = "CREATE TABLE IF NOT EXISTS \"legalnotices\" (\n" +
+                "	\"id\"	INTEGER PRIMARY KEY AUTOINCREMENT,\n" +
+                "	\"ReferenceNumber\"	TEXT NOT NULL UNIQUE,\n" +
+                "	\"Description\"	TEXT NOT NULL,\n" +
+                "	\"EntrustmentDate\"	INTEGER NOT NULL,\n" +
+                "	\"ClientId\"	INTEGER,\n" +
+                "	\"DraftCreated\"	INTEGER DEFAULT 0,\n" +
+                "	\"DraftApproved\"	INTEGER DEFAULT 0 CHECK(DraftApproved<=DraftCreated),\n" +
+                "	\"NoticeSent\"	INTEGER DEFAULT 0 CHECK(NoticeSent<=DraftApproved),\n" +
+                "	\"SentDate\"	INTEGER CHECK(SentDate>=EntrustmentDate),\n" +
+                "	\"RPADReference\"	TEXT,\n" +
+                "	\"NoticeDelivered\"	INTEGER DEFAULT 0 CHECK(NoticeDelivered<=NoticeSent),\n" +
+                "	\"DeliveryDate\"	INTEGER CHECK(DeliveryDate>=SentDate),\n" +
+                "	\"BillStatus\"	TEXT DEFAULT 'PENDING',\n" +
+                "	\"BillAmount\"	INTEGER DEFAULT 1000 CHECK(BillAmount>0),\n" +
+                "	\"BillDate\"	INTEGER CHECK(BillDate>=DeliveryDate),\n" +
+                "	\"ClarificationPending\"	INTEGER DEFAULT 0,\n" +
+                "	\"ClarificationRemarks\"	TEXT\n" +
+                ");";
+
+        try {
+            Connection conn = DriverManager.getConnection(JDBC + connectionURL);
+            Statement st1 = conn.createStatement();
+            st1.execute(tblClients);
+            st1.execute(tblNotices);
+            conn.close();
+            return true;
+        } catch (SQLException ex) {
+            Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
+            return false;
+        }
+
+    }
+
+    static boolean updateNoticeClarificationDetails(int selected_id, boolean 
+            clarification_pending, String clarification_remarks) {
+        try {
+            Connection conn = DriverManager.getConnection(JDBC + connectionURL);
+            PreparedStatement st = conn.prepareStatement ("UPDATE legalnotices"
+                    + " SET ClarificationPending=?, ClarificationRemarks=? "
+                    + "WHERE id=?;");
+            st.setBoolean(1, clarification_pending);
+            st.setString(2, clarification_remarks);
+            st.setInt (3, selected_id);
+            st.execute();
+            conn.close ();
+            return true;
+        } catch (SQLException ex) {
+            Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
+            return false;
+        }
+        
+    }
+
+    static boolean deleteNotice(int r) {
+        try {
+            Connection conn = DriverManager.getConnection(JDBC + connectionURL);
+            PreparedStatement st = conn.prepareStatement("DELETE FROM legalnotices"
+                    + " WHERE id=?;");
+            st.setInt(1, r);
+            st.execute();
+            conn.close();
+            return true;
+        } catch (SQLException ex) {
+            Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
+            return false;
+        }
+        
+    }
+    
+    static ArrayList<Object> getRaisedBills (int client_id) {
+        try {
+            Connection conn = DriverManager.getConnection(JDBC + connectionURL);
+            PreparedStatement st = conn.prepareStatement("SELECT ReferenceNumber, "
+                     + "Description, BillDate, BillAmount"
+                     + " FROM legalnotices WHERE BillStatus='RAISED' AND ClientId=?;");
+            st.setInt (1, client_id);
+            
+             ResultSet rs = st.executeQuery ();
+             ArrayList<Object> bills = new ArrayList<>();
+             while (rs.next()) {
+                 bills.add (rs.getString("ReferenceNumber"));
+                 bills.add (rs.getString("Description"));
+                 bills.add (getValidDate(rs, "BillDate"));
+                 bills.add (rs.getInt("BillAmount"));
+             }
+             conn.close();
+             return bills;
+             
+        } catch (SQLException ex) {
+            Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
+            return null;
+        }
+            
+    }
+
+    static boolean generateRaisedNoticesBill(String fileName, String clientName,
+            String clientAddress, String contactPerson, ArrayList<Object> bills) {
+        if (bills == null)
+                return false;
+        try {
+            InputStream templ = Utility.class.getClassLoader().getResourceAsStream("resources/noticebill.template.fodt");
+            if (templ == null)
+                return false;
+            DateFormat fmt = new SimpleDateFormat("dd MMM yyyy");
+            String templateMain = new String (templ.readAllBytes());
+            // this is for openoffice ODT - replace normal line breaks with the XML equivalent
+            String left = leftLetterHeader.replaceAll("\n", "<text:line-break/>");
+            String right = rightLetterHeader.replaceAll("\n", "<text:line-break/>");
+            String client_address = clientAddress.replaceAll ("\n", "<text:line-break/>");
+            String rows = generateBillRows (bills);
+            System.out.println (rows);
+            
+            String strMain = MessageFormat.format(templateMain, 
+                    left, right, 
+                    fmt.format(new Date(System.currentTimeMillis())),
+                    clientName, client_address, contactPerson, signatoryName, rows);
+            
+            FileOutputStream f = new FileOutputStream (fileName);
+            f.write (strMain.getBytes());
+            f.close();
+            return true; 
+        } catch (IOException ex) {
+            Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
+            return false;
+        }
+    }
+
+    private static String generateBillRows(ArrayList<Object> bills) {
+        try {
+            InputStream tmpl = Utility.class.getClassLoader().getResourceAsStream("resources/tablerow.template.xml");
+            String rowtpl = new String(tmpl.readAllBytes());
+         
+            DateFormat fmt = new SimpleDateFormat("dd/MM/yyyy");
+
+          StringBuilder bldr = new StringBuilder ();
+          for (int i = 0; i < bills.size(); i += 4) {
+              String row = MessageFormat.format (rowtpl, (String)bills.get(i), 
+                      (String)bills.get(i+1), fmt.format((java.util.Date)bills.get(i+2)),
+                              (int)bills.get(i+3));
+              bldr.append(row);
+          }
+          return (bldr.toString());
+        } catch (IOException ex) {
+            Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
+            return null;
+        }
+    }
+
+    static boolean updateNoticeBillStatus(int client_id, String from_status, 
+            String to_status) {
+        try {
+            Connection conn = DriverManager.getConnection(JDBC + connectionURL);
+            PreparedStatement st = conn.prepareStatement("UPDATE legalnotices"
+                    + " SET BillStatus=? WHERE BillStatus=? AND ClientId=?;");
+            st.setString (1, to_status);
+            st.setString (2, from_status);
+            st.setInt (3, client_id);
+            st.execute();
+            conn.close();
+            return true;
+        } catch (SQLException ex) {
+            Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
+            return false;
+        }
+        
+    }
+
+    static ArrayList<Object> getReportData(int report_num, int clientid) {
+        ArrayList<Object> data = new ArrayList<>();
+        try {
+            Connection conn = DriverManager.getConnection(JDBC + connectionURL);
+
+            // if no client ID is specified i.e. -1 get ll
+            ResultSet rs;
+            if (clientid == -1) {
+                Statement st = conn.createStatement();                
+                rs = st.executeQuery(REPORTS_UNFILTERED[report_num]);
+            }
+            else {
+                PreparedStatement st = conn.prepareStatement(REPORTS_FILTERED[report_num]);
+                st.setInt (1, clientid);
+                rs = st.executeQuery();
+            }
+
+            while (rs.next()) {
+                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;
+        } catch (SQLException ex) {
+            Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
+            return null;
+        }
+        
+    }
+
+    static boolean saveReportCSV(String filePath, String[] reportCols, ArrayList<Object> reportData) {
+        try {
+            FileWriter f = new FileWriter (filePath);
+            for (int i = 0; i < reportCols.length; 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 (escapeQuoteCSV(reportData.get(i+j)));
+                    f.append (",");
+                }
+                f.append ("\n");
+            }
+            f.flush();
+            f.close();
+            return true;         
+        } catch (IOException ex) {
+            Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
+            return false;
+        }
+        
+    }
+
+    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);
+            String finalStr = String.format ("\"%s\"", escapedStr);
+            return finalStr;
+        }
+        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 () {
         
     }
     public static String connectionURL ;
+    public static String leftLetterHeader; 
+    public static String rightLetterHeader;
+    public static String signatoryName;
     
     /**
      *
@@ -151,7 +698,7 @@ public class Utility {
     public static ArrayList<Object> getClientsNameAndId () {
         ArrayList<Object> data = new ArrayList<>();
         try {
-            Connection conn = DriverManager.getConnection("jdbc:sqlite:" + connectionURL);        
+            Connection conn = DriverManager.getConnection(JDBC + connectionURL);        
             Statement st = conn.createStatement();
             ResultSet rs = st.executeQuery("SELECT id, ClientName from clients;");
             while (rs.next()) {
@@ -167,8 +714,9 @@ public class Utility {
         
     }
     
-    public static void getConnectionURL () {
-        connectionURL = Preferences.userRoot().get("ConnectionURL", "legaldb");
+    public static void retrieveConnectionURL () {
+        Preferences myPrefs = Preferences.userRoot().node("org/harishankar/Habeas");
+        connectionURL = myPrefs.get("ConnectionURL", "legaldb");
     }
     
 }