src/habeas/Utility.java

   1 /*
   2  * To change this license header, choose License Headers in Project Properties.
   3  * To change this template file, choose Tools | Templates
   4  * and open the template in the editor.
   5  */
   6 package habeas;
   7
   8 import java.util.ArrayList;
   9 import java.util.prefs.Preferences;
  10 import java.sql.*;
  11 import java.util.logging.Level;
  12 import java.util.logging.Logger;
  13
  14 /**
  15  *
  16  * @author hari
  17  */
  18 public class Utility {
  19
  20     static void saveStationerySettings (String left_header,
  21             String right_header, String signatory) {
  22         leftLetterHeader = left_header;
  23         rightLetterHeader = right_header;
  24         signatoryName = signatory;
  25         Preferences.userRoot().put ("LeftHeader", left_header);
  26         Preferences.userRoot().put ("RightHeader", right_header);
  27         Preferences.userRoot().put ("Signatory", signatory);
  28     }
  29
  30     static void retrieveStationerySettings () {
  31         leftLetterHeader = Preferences.userRoot().get("LeftHeader", "Left Header");
  32         rightLetterHeader = Preferences.userRoot().get ("RightHeader", "Right Header");
  33         signatoryName = Preferences.userRoot().get ("Signatory", "Signatory Name");
  34     }
  35
  36     static void saveConnectionURL(String text) {
  37         connectionURL = text;
  38         Preferences.userRoot().put("ConnectionURL", text);
  39     }
  40
  41     static ArrayList<Object> getClientDetails(int r) {
  42         ArrayList<Object> res = new ArrayList <>();
  43         try {
  44             Connection conn = DriverManager.getConnection(JDBC+connectionURL);
  45             PreparedStatement st = conn.prepareStatement("SELECT * FROM clients WHERE id=?;");
  46             st.setInt(1, r);
  47             ResultSet rs = st.executeQuery();
  48             while (rs.next()) {
  49                 res.add (rs.getString("ClientName"));
  50                 res.add(rs.getString("ClientAddress"));
  51                 res.add (rs.getString("ContactPerson"));
  52                 res.add (rs.getString("MailID"));
  53                 res.add (rs.getString("ContactNumber"));
  54             }
  55             conn.close ();
  56             return res;
  57
  58         } catch (SQLException ex) {
  59             Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
  60             return null;
  61         }
  62
  63     }
  64
  65     static boolean addClient(String client_name, String client_address,
  66             String contact_person, String email_id, String phone_number) {
  67         if ("".equals(client_name))
  68             return false;
  69         try {
  70             Connection conn = DriverManager.getConnection(JDBC + connectionURL);
  71             PreparedStatement st = conn.prepareStatement("INSERT INTO clients (ClientName,"
  72                     + "ClientAddress, ContactPerson, MailID, ContactNumber) VALUES (?, ?, ?, ?, ?);");
  73             st.setString(1, client_name);
  74             st.setString(2, client_address);
  75             st.setString(3, contact_person);
  76             st.setString (4, email_id);
  77             st.setString (5, phone_number);
  78             st.execute();
  79             conn.close();
  80             return true;
  81
  82         } catch (SQLException ex) {
  83             Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
  84             return false;
  85         }
  86
  87
  88     }
  89
  90     static boolean deleteClient(int r) {
  91         try {
  92             Connection conn = DriverManager.getConnection(JDBC + connectionURL);
  93             PreparedStatement st = conn.prepareStatement("DELETE FROM clients WHERE id=?;");
  94             PreparedStatement st2 = conn.prepareStatement("DELETE FROM legalnotices WHERE ClientId=?;");
  95             st.setInt(1, r);
  96             st2.setInt (1, r);
  97             st.execute();
  98             st2.execute();
  99             conn.close();
 100             return true;
 101         } catch (SQLException ex) {
 102             Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
 103             return false;
 104         }
 105
 106     }
 107
 108     static boolean updateClient(int r, String client_name, String client_address,
 109             String contact_person, String mail_id, String phone_number) {
 110         if ("".equals(client_name))
 111                 return false;
 112         try {
 113             Connection conn = DriverManager.getConnection(JDBC + connectionURL);
 114             PreparedStatement st = conn.prepareStatement("UPDATE clients SET "
 115                     + "ClientName=?, ClientAddress=?,"
 116                     + "ContactPerson=?, MailID=?, ContactNumber=? WHERE id=?;");
 117             st.setString(1, client_name);
 118             st.setString(2, client_address);
 119             st.setString(3, contact_person);
 120             st.setString(4, mail_id);
 121             st.setString(5, phone_number);
 122             st.setInt (6, r);
 123             st.execute();
 124             conn.close();
 125             return true;
 126         } catch (SQLException ex) {
 127             Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
 128             return false;
 129         }
 130
 131
 132     }
 133
 134     static boolean addLegalNotice(String reference_number,
 135             String description, java.util.Date entrustment_date, DBItem client) {
 136         if ("".equals(reference_number) || "".equals(description) ||
 137                 entrustment_date == null || client == null)
 138             return false;
 139         try {
 140             Connection conn = DriverManager.getConnection(JDBC + connectionURL);
 141             PreparedStatement st = conn.prepareStatement("INSERT INTO legalnotices"
 142                     + " (ReferenceNumber, Description, EntrustmentDate, ClientId) "
 143                     + "VALUES (?, ?, ?, ?);");
 144             st.setString(1, reference_number);
 145             st.setString(2, description);
 146             st.setLong(3, entrustment_date.getTime()/1000);
 147             st.setInt (4, client.getKey());
 148             st.execute();
 149             conn.close();
 150             return true;
 151
 152         } catch (SQLException ex) {
 153             Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
 154             return false;
 155         }
 156
 157     }
 158
 159     static ArrayList<Object> getNotices() {
 160         ArrayList<Object> notices = new ArrayList<>();
 161         try {
 162             Connection conn = DriverManager.getConnection(JDBC + connectionURL);
 163             Statement st = conn.createStatement();
 164             ResultSet rs = st.executeQuery("SELECT id, ReferenceNumber, Description "
 165                     + "FROM legalnotices;");
 166             while (rs.next()) {
 167                 notices.add(rs.getInt("id"));
 168                 notices.add(rs.getString("ReferenceNumber"));
 169                 notices.add(rs.getString("Description"));
 170             }
 171             conn.close();
 172             return notices;
 173         } catch (SQLException ex) {
 174             Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
 175             return null;
 176         }
 177
 178     }
 179     static java.util.Date getValidDate (ResultSet rs, String datefield) throws SQLException {
 180         // Since resultset.getDate returns a java.sql.Date, we need a way to get
 181         // the date in java.util.Date which is usable in our application. Hence
 182         // this helper function. Since we don't want a valid Date object if the
 183         // field is null, we are using this if clause
 184         if (rs.getDate(datefield) == null)
 185             return null;
 186         else
 187             return (new java.util.Date(rs.getLong(datefield)*1000));
 188     }
 189
 190     static ArrayList<Object> getNoticeDetails(int selid) {
 191         ArrayList<Object> notice = new ArrayList<>();
 192         try {
 193             Connection conn = DriverManager.getConnection(JDBC + connectionURL);
 194             PreparedStatement st = conn.prepareStatement("SELECT legalnotices.*, clients.ClientName "
 195                     + "FROM legalnotices "
 196                     + "INNER JOIN clients WHERE ClientId=clients.id AND legalnotices.id=?;");
 197             st.setInt(1, selid);
 198             ResultSet rs = st.executeQuery();
 199             while (rs.next()) {
 200                 notice.add(rs.getString("ReferenceNumber"));
 201                 notice.add(rs.getString("Description"));
 202                 notice.add(getValidDate(rs, "EntrustmentDate"));
 203                 notice.add (rs.getInt ("ClientId"));
 204                 notice.add (rs.getBoolean("DraftCreated"));
 205                 notice.add (rs.getBoolean("DraftApproved"));
 206                 notice.add (rs.getBoolean("NoticeSent"));
 207                 notice.add (getValidDate(rs, "SentDate"));
 208                 notice.add (rs.getString("RPADReference"));
 209                 notice.add (rs.getBoolean("NoticeDelivered"));
 210                 notice.add (getValidDate(rs, "DeliveryDate"));
 211                 notice.add (rs.getString("BillStatus"));
 212                 notice.add (rs.getInt ("BillAmount"));
 213                 notice.add (getValidDate (rs, "BillDate"));
 214                 notice.add (rs.getBoolean ("ClarificationPending"));
 215                 notice.add (rs.getString("ClarificationRemarks"));
 216                 notice.add (rs.getString("ClientName"));
 217             }
 218            return notice;
 219         } catch (SQLException ex) {
 220             Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
 221             return null;
 222         }
 223
 224
 225     }
 226     private static final String JDBC = "jdbc:sqlite:";
 227
 228     static boolean updateNoticeDraftStatus(int selectednotice_id, boolean created, boolean approved) {
 229         try {
 230             Connection conn = DriverManager.getConnection(JDBC + connectionURL);
 231             PreparedStatement st = conn.prepareStatement("UPDATE legalnotices "
 232                     + "SET DraftCreated=?, DraftApproved=? WHERE id=?;");
 233             st.setBoolean(1, created);
 234             st.setBoolean(2, approved);
 235             st.setInt (3, selectednotice_id);
 236             st.execute();
 237             conn.close();
 238             return true;
 239         } catch (SQLException ex) {
 240             Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
 241             return false;
 242         }
 243
 244     }
 245
 246     static boolean updateNoticeDescription(int selectednotice_id, String text) {
 247         try {
 248             Connection conn = DriverManager.getConnection(JDBC + connectionURL);
 249             PreparedStatement st = conn.prepareStatement("UPDATE legalnotices "
 250                     + "SET Description=? WHERE id=?;");
 251             st.setString(1, text);
 252             st.setInt(2, selectednotice_id);
 253             st.execute();
 254             conn.close();
 255             return true;
 256         } catch (SQLException ex) {
 257             Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
 258             return false;
 259         }
 260
 261     }
 262
 263     static boolean updateNoticeDispatchDetails(int selid, boolean notice_sent,
 264             java.util.Date sent_date, String rpad_reference,
 265             boolean notice_delivered, java.util.Date delivery_date) {
 266         try {
 267             Connection conn = DriverManager.getConnection(JDBC + connectionURL);
 268             PreparedStatement st = conn.prepareStatement("UPDATE legalnotices"
 269                     + " SET NoticeSent=?, SentDate=?, RPADReference=?,"
 270                     + " NoticeDelivered=?, DeliveryDate=? WHERE id=?;");
 271
 272             st.setBoolean(1, notice_sent);
 273             st.setDate(2,  toSqlDate(sent_date));
 274             st.setString(3, rpad_reference);
 275             st.setBoolean(4, notice_delivered);
 276             st.setDate(5, toSqlDate(delivery_date));
 277             st.setInt (6, selid);
 278             st.execute();
 279             conn.close();
 280             return true;
 281         } catch (SQLException ex) {
 282             Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
 283             return false;
 284         }
 285
 286     }
 287
 288     private static Date toSqlDate(java.util.Date date) {
 289             if (date == null)
 290                 return null;
 291             Date sqldate = new Date (date.getTime()/1000);
 292             return sqldate;
 293     }
 294
 295     static boolean updateNoticeBillDetails(int selid, String bill_status,
 296             java.util.Date bill_date, int bill_amount) {
 297         try {
 298             Connection conn = DriverManager.getConnection(JDBC + connectionURL);
 299             PreparedStatement st = conn.prepareStatement("UPDATE legalnotices"
 300                     + " SET BillStatus=?,BillDate=?,BillAmount=? WHERE id=?;");
 301             st.setString (1, bill_status);
 302             st.setDate(2, toSqlDate(bill_date));
 303             st.setInt(3, bill_amount);
 304             st.setInt(4, selid);
 305             st.execute();
 306             conn.close();
 307             return true;
 308         } catch (SQLException ex) {
 309             Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
 310             return false;
 311         }
 312
 313     }
 314
 315     static boolean createDatabase(String db_path) {
 316         saveConnectionURL(db_path);
 317         String tblClients = "CREATE TABLE IF NOT EXISTS \"clients\" (\n" +
 318 "       \"id\"  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n" +
 319 "       \"ClientName\"  VARCHAR(255) NOT NULL UNIQUE,\n" +
 320 "       \"ClientAddress\"       TEXT,\n" +
 321 "       \"ContactPerson\"       TEXT,\n" +
 322 "       \"MailID\"      VARCHAR(255),\n" +
 323 "       \"ContactNumber\"       VARCHAR(30)\n" +
 324 "       );";
 325         String tblNotices = "CREATE TABLE IF NOT EXISTS \"legalnotices\" (\n" +
 326                 "       \"id\"  INTEGER PRIMARY KEY AUTOINCREMENT,\n" +
 327                 "       \"ReferenceNumber\"     TEXT NOT NULL UNIQUE,\n" +
 328                 "       \"Description\" TEXT NOT NULL,\n" +
 329                 "       \"EntrustmentDate\"     INTEGER NOT NULL,\n" +
 330                 "       \"ClientId\"    INTEGER,\n" +
 331                 "       \"DraftCreated\"        INTEGER DEFAULT 0,\n" +
 332                 "       \"DraftApproved\"       INTEGER DEFAULT 0 CHECK(DraftApproved<=DraftCreated),\n" +
 333                 "       \"NoticeSent\"  INTEGER DEFAULT 0 CHECK(NoticeSent<=DraftApproved),\n" +
 334                 "       \"SentDate\"    INTEGER CHECK(SentDate>=EntrustmentDate),\n" +
 335                 "       \"RPADReference\"       TEXT,\n" +
 336                 "       \"NoticeDelivered\"     INTEGER DEFAULT 0 CHECK(NoticeDelivered<=NoticeSent),\n" +
 337                 "       \"DeliveryDate\"        INTEGER CHECK(DeliveryDate>=SentDate),\n" +
 338                 "       \"BillStatus\"  TEXT DEFAULT 'PENDING',\n" +
 339                 "       \"BillAmount\"  INTEGER DEFAULT 1000 CHECK(BillAmount>0),\n" +
 340                 "       \"BillDate\"    INTEGER CHECK(BillDate>=DeliveryDate),\n" +
 341                 "       \"ClarificationPending\"        INTEGER DEFAULT 0,\n" +
 342                 "       \"ClarificationRemarks\"        TEXT\n" +
 343                 ");";
 344
 345         try {
 346             Connection conn = DriverManager.getConnection(JDBC + connectionURL);
 347             Statement st1 = conn.createStatement();
 348             st1.execute(tblClients);
 349             st1.execute(tblNotices);
 350             conn.close();
 351             return true;
 352         } catch (SQLException ex) {
 353             Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
 354             return false;
 355         }
 356
 357     }
 358
 359     static boolean updateNoticeClarificationDetails(int selected_id, boolean
 360             clarification_pending, String clarification_remarks) {
 361         try {
 362             Connection conn = DriverManager.getConnection(JDBC + connectionURL);
 363             PreparedStatement st = conn.prepareStatement ("UPDATE legalnotices"
 364                     + " SET ClarificationPending=?, ClarificationRemarks=? "
 365                     + "WHERE id=?;");
 366             st.setBoolean(1, clarification_pending);
 367             st.setString(2, clarification_remarks);
 368             st.setInt (3, selected_id);
 369             st.execute();
 370             conn.close ();
 371             return true;
 372         } catch (SQLException ex) {
 373             Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
 374             return false;
 375         }
 376
 377     }
 378
 379     static boolean deleteNotice(int r) {
 380         try {
 381             Connection conn = DriverManager.getConnection(JDBC + connectionURL);
 382             PreparedStatement st = conn.prepareStatement("DELETE FROM legalnotices"
 383                     + " WHERE id=?;");
 384             st.setInt(1, r);
 385             st.execute();
 386             conn.close();
 387             return true;
 388         } catch (SQLException ex) {
 389             Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
 390             return false;
 391         }
 392
 393     }
 394     public Utility () {
 395
 396     }
 397     public static String connectionURL ;
 398     public static String leftLetterHeader;
 399     public static String rightLetterHeader;
 400     public static String signatoryName;
 401
 402     /**
 403      *
 404      * @return
 405      */
 406     public static ArrayList<Object> getClientsNameAndId () {
 407         ArrayList<Object> data = new ArrayList<>();
 408         try {
 409             Connection conn = DriverManager.getConnection(JDBC + connectionURL);
 410             Statement st = conn.createStatement();
 411             ResultSet rs = st.executeQuery("SELECT id, ClientName from clients;");
 412             while (rs.next()) {
 413                 data.add(rs.getInt("id"));
 414                 data.add(rs.getString("ClientName"));
 415             }
 416             conn.close();
 417             return data;
 418         } catch (SQLException ex) {
 419             Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
 420             return null;
 421         }
 422
 423     }
 424
 425     public static void retrieveConnectionURL () {
 426         connectionURL = Preferences.userRoot().get("ConnectionURL", "legaldb");
 427     }
 428
 429 }