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 * FROM legalnotices WHERE id=?;");
 179             st.setInt(1, selid);
 180             ResultSet rs = st.executeQuery();
 181             while (rs.next()) {
 182                 notice.add(rs.getString("ReferenceNumber"));
 183                 notice.add(rs.getString("Description"));
 184                 notice.add(getValidDate(rs, "EntrustmentDate"));
 185                 notice.add (rs.getInt ("ClientId"));
 186                 notice.add (rs.getBoolean("DraftCreated"));
 187                 notice.add (rs.getBoolean("DraftApproved"));
 188                 notice.add (rs.getBoolean("NoticeSent"));
 189                 notice.add (getValidDate(rs, "SentDate"));
 190                 notice.add (rs.getString("RPADReference"));
 191                 notice.add (rs.getBoolean("NoticeDelivered"));
 192                 notice.add (getValidDate(rs, "DeliveryDate"));
 193                 notice.add (rs.getString("BillStatus"));
 194                 notice.add (rs.getInt ("BillAmount"));
 195                 notice.add (getValidDate (rs, "BillDate"));
 196                 notice.add (rs.getBoolean ("ClarificationPending"));
 197                 notice.add (rs.getString("ClarificationRemarks"));
 198             }
 199            return notice;
 200         } catch (SQLException ex) {
 201             Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
 202             return null;
 203         }
 204
 205
 206     }
 207     private static final String JDBC = "jdbc:sqlite:";
 208
 209     static boolean updateNoticeDraftStatus(int selectednotice_id, boolean created, boolean approved) {
 210         try {
 211             Connection conn = DriverManager.getConnection(JDBC + connectionURL);
 212             PreparedStatement st = conn.prepareStatement("UPDATE legalnotices "
 213                     + "SET DraftCreated=?, DraftApproved=? WHERE id=?;");
 214             st.setBoolean(1, created);
 215             st.setBoolean(2, approved);
 216             st.setInt (3, selectednotice_id);
 217             st.execute();
 218             conn.close();
 219             return true;
 220         } catch (SQLException ex) {
 221             Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
 222             return false;
 223         }
 224
 225     }
 226
 227     static boolean updateNoticeDescription(int selectednotice_id, String text) {
 228         try {
 229             Connection conn = DriverManager.getConnection(JDBC + connectionURL);
 230             PreparedStatement st = conn.prepareStatement("UPDATE legalnotices "
 231                     + "SET Description=? WHERE id=?;");
 232             st.setString(1, text);
 233             st.setInt(2, selectednotice_id);
 234             st.execute();
 235             conn.close();
 236             return true;
 237         } catch (SQLException ex) {
 238             Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
 239             return false;
 240         }
 241
 242     }
 243
 244     static boolean updateNoticeDispatchDetails(int selid, boolean notice_sent,
 245             java.util.Date sent_date, String rpad_reference,
 246             boolean notice_delivered, java.util.Date delivery_date) {
 247         try {
 248             Connection conn = DriverManager.getConnection(JDBC + connectionURL);
 249             PreparedStatement st = conn.prepareStatement("UPDATE legalnotices"
 250                     + " SET NoticeSent=?, SentDate=?, RPADReference=?,"
 251                     + " NoticeDelivered=?, DeliveryDate=? WHERE id=?;");
 252
 253             st.setBoolean(1, notice_sent);
 254             st.setDate(2,  toSqlDate(sent_date));
 255             st.setString(3, rpad_reference);
 256             st.setBoolean(4, notice_delivered);
 257             st.setDate(5, toSqlDate(delivery_date));
 258             st.setInt (6, selid);
 259             st.execute();
 260             conn.close();
 261             return true;
 262         } catch (SQLException ex) {
 263             Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
 264             return false;
 265         }
 266
 267     }
 268
 269     private static Date toSqlDate(java.util.Date date) {
 270             if (date == null)
 271                 return null;
 272             Date sqldate = new Date (date.getTime()/1000);
 273             return sqldate;
 274     }
 275
 276     static boolean updateNoticeBillDetails(int selid, String bill_status,
 277             java.util.Date bill_date, int bill_amount) {
 278         try {
 279             Connection conn = DriverManager.getConnection(JDBC + connectionURL);
 280             PreparedStatement st = conn.prepareStatement("UPDATE legalnotices"
 281                     + " SET BillStatus=?,BillDate=?,BillAmount=? WHERE id=?;");
 282             st.setString (1, bill_status);
 283             st.setDate(2, toSqlDate(bill_date));
 284             st.setInt(3, bill_amount);
 285             st.setInt(4, selid);
 286             st.execute();
 287             conn.close();
 288             return true;
 289         } catch (SQLException ex) {
 290             Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
 291             return false;
 292         }
 293
 294     }
 295
 296     static boolean createDatabase(String db_path) {
 297         saveConnectionURL(db_path);
 298         String tblClients = "CREATE TABLE IF NOT EXISTS \"clients\" (\n" +
 299 "       \"id\"  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n" +
 300 "       \"ClientName\"  VARCHAR(255) NOT NULL UNIQUE,\n" +
 301 "       \"ClientAddress\"       TEXT,\n" +
 302 "       \"ContactPerson\"       TEXT,\n" +
 303 "       \"MailID\"      VARCHAR(255),\n" +
 304 "       \"ContactNumber\"       VARCHAR(30)\n" +
 305 "       );";
 306         String tblNotices = "CREATE TABLE IF NOT EXISTS \"legalnotices\" (\n" +
 307                 "       \"id\"  INTEGER PRIMARY KEY AUTOINCREMENT,\n" +
 308                 "       \"ReferenceNumber\"     TEXT NOT NULL UNIQUE,\n" +
 309                 "       \"Description\" TEXT NOT NULL,\n" +
 310                 "       \"EntrustmentDate\"     INTEGER NOT NULL,\n" +
 311                 "       \"ClientId\"    INTEGER,\n" +
 312                 "       \"DraftCreated\"        INTEGER DEFAULT 0,\n" +
 313                 "       \"DraftApproved\"       INTEGER DEFAULT 0 CHECK(DraftApproved<=DraftCreated),\n" +
 314                 "       \"NoticeSent\"  INTEGER DEFAULT 0 CHECK(NoticeSent<=DraftApproved),\n" +
 315                 "       \"SentDate\"    INTEGER CHECK(SentDate>=EntrustmentDate),\n" +
 316                 "       \"RPADReference\"       TEXT,\n" +
 317                 "       \"NoticeDelivered\"     INTEGER DEFAULT 0 CHECK(NoticeDelivered<=NoticeSent),\n" +
 318                 "       \"DeliveryDate\"        INTEGER CHECK(DeliveryDate>=SentDate),\n" +
 319                 "       \"BillStatus\"  TEXT DEFAULT 'PENDING',\n" +
 320                 "       \"BillAmount\"  INTEGER DEFAULT 1000 CHECK(BillAmount>0),\n" +
 321                 "       \"BillDate\"    INTEGER CHECK(BillDate>=DeliveryDate),\n" +
 322                 "       \"ClarificationPending\"        INTEGER DEFAULT 0,\n" +
 323                 "       \"ClarificationRemarks\"        TEXT\n" +
 324                 ");";
 325         String tblSettings = "CREATE TABLE IF NOT EXISTS \"settings\" (\n" +
 326 "       \"key\" TEXT UNIQUE,\n" +
 327 "       \"value\"       TEXT,\n" +
 328 "       PRIMARY KEY(\"key\")\n" +
 329         ");";
 330         try {
 331             Connection conn = DriverManager.getConnection(JDBC + connectionURL);
 332             Statement st1 = conn.createStatement();
 333             st1.execute(tblSettings);
 334             st1.execute(tblClients);
 335             st1.execute(tblNotices);
 336             conn.close();
 337             return true;
 338         } catch (SQLException ex) {
 339             Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
 340             return false;
 341         }
 342
 343     }
 344
 345     static boolean updateNoticeClarificationDetails(int selected_id, boolean
 346             clarification_pending, String clarification_remarks) {
 347         try {
 348             Connection conn = DriverManager.getConnection(JDBC + connectionURL);
 349             PreparedStatement st = conn.prepareStatement ("UPDATE legalnotices"
 350                     + " SET ClarificationPending=?, ClarificationRemarks=? "
 351                     + "WHERE id=?;");
 352             st.setBoolean(1, clarification_pending);
 353             st.setString(2, clarification_remarks);
 354             st.setInt (3, selected_id);
 355             st.execute();
 356             conn.close ();
 357             return true;
 358         } catch (SQLException ex) {
 359             Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
 360             return false;
 361         }
 362
 363     }
 364
 365     static boolean deleteNotice(int r) {
 366         try {
 367             Connection conn = DriverManager.getConnection(JDBC + connectionURL);
 368             PreparedStatement st = conn.prepareStatement("DELETE FROM legalnotices"
 369                     + " WHERE id=?;");
 370             st.setInt(1, r);
 371             st.execute();
 372             conn.close();
 373             return true;
 374         } catch (SQLException ex) {
 375             Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
 376             return false;
 377         }
 378
 379     }
 380     public Utility () {
 381
 382     }
 383     public static String connectionURL ;
 384
 385     /**
 386      *
 387      * @return
 388      */
 389     public static ArrayList<Object> getClientsNameAndId () {
 390         ArrayList<Object> data = new ArrayList<>();
 391         try {
 392             Connection conn = DriverManager.getConnection(JDBC + connectionURL);
 393             Statement st = conn.createStatement();
 394             ResultSet rs = st.executeQuery("SELECT id, ClientName from clients;");
 395             while (rs.next()) {
 396                 data.add(rs.getInt("id"));
 397                 data.add(rs.getString("ClientName"));
 398             }
 399             conn.close();
 400             return data;
 401         } catch (SQLException ex) {
 402             Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
 403             return null;
 404         }
 405
 406     }
 407
 408     public static void retrieveConnectionURL () {
 409         connectionURL = Preferences.userRoot().get("ConnectionURL", "legaldb");
 410     }
 411
 412 }