Manage Legal Notices - Draft Status

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

diff --git a/src/habeas/Utility.java b/src/habeas/Utility.java
index a1148a2..dd83144 100644 (file)
--- a/src/habeas/Utility.java
+++ b/src/habeas/Utility.java
@@ -25,7 +25,7 @@ public class Utility {
     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 +51,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 +73,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 +94,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=?;");
@@ -121,7 +121,7 @@ public class Utility {
                 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 (?, ?, ?, ?);");
@@ -143,7 +143,7 @@ public class Utility {
     static ArrayList<Object> getNotices() {
         ArrayList<Object> notices = 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, ReferenceNumber, Description "
                     + "FROM legalnotices;");
@@ -159,6 +159,69 @@ public class Utility {
             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 * FROM legalnotices WHERE 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"));
+            }
+           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 updateLegalNoticeDraftStatus(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;
+        }
+        
     }
     public Utility () {
         
@@ -172,7 +235,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()) {