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