Option for creating a new Legal Database

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

diff --git a/src/habeas/Utility.java b/src/habeas/Utility.java
index dd83144..6ca96e5 100644 (file)
--- a/src/habeas/Utility.java
+++ b/src/habeas/Utility.java
@@ -17,7 +17,7 @@ import java.util.logging.Logger;
  */
 public class Utility {
 
-    static void setConnectionURL(String text) {
+    static void saveConnectionURL(String text) {
         connectionURL = text;
         Preferences.userRoot().put("ConnectionURL", text);
     }
@@ -206,7 +206,7 @@ public class Utility {
     }
     private static final String JDBC = "jdbc:sqlite:";
 
-    static boolean updateLegalNoticeDraftStatus(int selectednotice_id, boolean created, boolean approved) {
+    static boolean updateNoticeDraftStatus(int selectednotice_id, boolean created, boolean approved) {
         try {
             Connection conn = DriverManager.getConnection(JDBC + connectionURL);
             PreparedStatement st = conn.prepareStatement("UPDATE legalnotices "
@@ -222,6 +222,124 @@ public class Utility {
             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, int 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.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" +
+                ");";
+        String tblSettings = "CREATE TABLE IF NOT EXISTS \"settings\" (\n" +
+"      \"key\" TEXT UNIQUE,\n" +
+"      \"value\"       TEXT,\n" +
+"      PRIMARY KEY(\"key\")\n" +
+        ");"; 
+        try {
+            Connection conn = DriverManager.getConnection(JDBC + connectionURL);
+            Statement st1 = conn.createStatement();
+            st1.execute(tblSettings);
+            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;
+        }
+
     }
     public Utility () {
         
@@ -251,7 +369,7 @@ public class Utility {
         
     }
     
-    public static void getConnectionURL () {
+    public static void retrieveConnectionURL () {
         connectionURL = Preferences.userRoot().get("ConnectionURL", "legaldb");
     }