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.io.FileNotFoundException;
   9 import java.io.FileOutputStream;
  10 import java.io.IOException;
  11 import java.io.InputStream;
  12 import java.util.ArrayList;
  13 import java.util.prefs.Preferences;
  14 import java.sql.*;
  15 import java.text.DateFormat;
  16 import java.text.MessageFormat;
  17 import java.text.SimpleDateFormat;
  18 import java.util.Arrays;
  19 import java.util.logging.Level;
  20 import java.util.logging.Logger;
  21 import jdk.dynalink.linker.support.Guards;
  22
  23 /**
  24  *
  25  * @author hari
  26  */
  27 public class Utility {
  28
  29     static void saveStationerySettings (String left_header,
  30             String right_header, String signatory) {
  31         leftLetterHeader = left_header;
  32         rightLetterHeader = right_header;
  33         signatoryName = signatory;
  34         Preferences myPrefs = Preferences.userRoot().node("org/harishankar/Habeas");
  35         myPrefs.put ("LeftHeader", left_header);
  36         myPrefs.put ("RightHeader", right_header);
  37         myPrefs.put ("Signatory", signatory);
  38     }
  39
  40     static void retrieveStationerySettings () {
  41         Preferences myPrefs = Preferences.userRoot().node ("org/harishankar/Habeas");
  42         leftLetterHeader = myPrefs.get("LeftHeader", "Left Header");
  43         rightLetterHeader = myPrefs.get ("RightHeader", "Right Header");
  44         signatoryName = myPrefs.get ("Signatory", "Signatory Name");
  45     }
  46
  47     static void saveConnectionURL(String text) {
  48         connectionURL = text;
  49         Preferences myPrefs = Preferences.userRoot().node ("org/harishankar/Habeas");
  50         myPrefs.put("ConnectionURL", text);
  51     }
  52
  53     static ArrayList<Object> getClientDetails(int r) {
  54         ArrayList<Object> res = new ArrayList <>();
  55         try {
  56             Connection conn = DriverManager.getConnection(JDBC+connectionURL);
  57             PreparedStatement st = conn.prepareStatement("SELECT * FROM clients WHERE id=?;");
  58             st.setInt(1, r);
  59             ResultSet rs = st.executeQuery();
  60             while (rs.next()) {
  61                 res.add (rs.getString("ClientName"));
  62                 res.add(rs.getString("ClientAddress"));
  63                 res.add (rs.getString("ContactPerson"));
  64                 res.add (rs.getString("MailID"));
  65                 res.add (rs.getString("ContactNumber"));
  66             }
  67             conn.close ();
  68             return res;
  69
  70         } catch (SQLException ex) {
  71             Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
  72             return null;
  73         }
  74
  75     }
  76
  77     static boolean addClient(String client_name, String client_address,
  78             String contact_person, String email_id, String phone_number) {
  79         if ("".equals(client_name))
  80             return false;
  81         try {
  82             Connection conn = DriverManager.getConnection(JDBC + connectionURL);
  83             PreparedStatement st = conn.prepareStatement("INSERT INTO clients (ClientName,"
  84                     + "ClientAddress, ContactPerson, MailID, ContactNumber) VALUES (?, ?, ?, ?, ?);");
  85             st.setString(1, client_name);
  86             st.setString(2, client_address);
  87             st.setString(3, contact_person);
  88             st.setString (4, email_id);
  89             st.setString (5, phone_number);
  90             st.execute();
  91             conn.close();
  92             return true;
  93
  94         } catch (SQLException ex) {
  95             Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
  96             return false;
  97         }
  98
  99
 100     }
 101
 102     static boolean deleteClient(int r) {
 103         try {
 104             Connection conn = DriverManager.getConnection(JDBC + connectionURL);
 105             PreparedStatement st = conn.prepareStatement("DELETE FROM clients WHERE id=?;");
 106             PreparedStatement st2 = conn.prepareStatement("DELETE FROM legalnotices WHERE ClientId=?;");
 107             st.setInt(1, r);
 108             st2.setInt (1, r);
 109             st.execute();
 110             st2.execute();
 111             conn.close();
 112             return true;
 113         } catch (SQLException ex) {
 114             Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
 115             return false;
 116         }
 117
 118     }
 119
 120     static boolean updateClient(int r, String client_name, String client_address,
 121             String contact_person, String mail_id, String phone_number) {
 122         if ("".equals(client_name))
 123                 return false;
 124         try {
 125             Connection conn = DriverManager.getConnection(JDBC + connectionURL);
 126             PreparedStatement st = conn.prepareStatement("UPDATE clients SET "
 127                     + "ClientName=?, ClientAddress=?,"
 128                     + "ContactPerson=?, MailID=?, ContactNumber=? WHERE id=?;");
 129             st.setString(1, client_name);
 130             st.setString(2, client_address);
 131             st.setString(3, contact_person);
 132             st.setString(4, mail_id);
 133             st.setString(5, phone_number);
 134             st.setInt (6, r);
 135             st.execute();
 136             conn.close();
 137             return true;
 138         } catch (SQLException ex) {
 139             Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
 140             return false;
 141         }
 142
 143
 144     }
 145
 146     static boolean addLegalNotice(String reference_number,
 147             String description, java.util.Date entrustment_date, DBItem client) {
 148         if ("".equals(reference_number) || "".equals(description) ||
 149                 entrustment_date == null || client == null)
 150             return false;
 151         try {
 152             Connection conn = DriverManager.getConnection(JDBC + connectionURL);
 153             PreparedStatement st = conn.prepareStatement("INSERT INTO legalnotices"
 154                     + " (ReferenceNumber, Description, EntrustmentDate, ClientId) "
 155                     + "VALUES (?, ?, ?, ?);");
 156             st.setString(1, reference_number);
 157             st.setString(2, description);
 158             st.setLong(3, entrustment_date.getTime()/1000);
 159             st.setInt (4, client.getKey());
 160             st.execute();
 161             conn.close();
 162             return true;
 163
 164         } catch (SQLException ex) {
 165             Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
 166             return false;
 167         }
 168
 169     }
 170
 171     static ArrayList<Object> getNotices() {
 172         ArrayList<Object> notices = new ArrayList<>();
 173         try {
 174             Connection conn = DriverManager.getConnection(JDBC + connectionURL);
 175             Statement st = conn.createStatement();
 176             ResultSet rs = st.executeQuery("SELECT id, ReferenceNumber, Description "
 177                     + "FROM legalnotices;");
 178             while (rs.next()) {
 179                 notices.add(rs.getInt("id"));
 180                 notices.add(rs.getString("ReferenceNumber"));
 181                 notices.add(rs.getString("Description"));
 182             }
 183             conn.close();
 184             return notices;
 185         } catch (SQLException ex) {
 186             Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
 187             return null;
 188         }
 189
 190     }
 191     static java.util.Date getValidDate (ResultSet rs, String datefield) throws SQLException {
 192         // Since resultset.getDate returns a java.sql.Date, we need a way to get
 193         // the date in java.util.Date which is usable in our application. Hence
 194         // this helper function. Since we don't want a valid Date object if the
 195         // field is null, we are using this if clause
 196         if (rs.getDate(datefield) == null)
 197             return null;
 198         else
 199             return (new java.util.Date(rs.getLong(datefield)*1000));
 200     }
 201
 202     static ArrayList<Object> getNoticeDetails(int selid) {
 203         ArrayList<Object> notice = new ArrayList<>();
 204         try {
 205             Connection conn = DriverManager.getConnection(JDBC + connectionURL);
 206             PreparedStatement st = conn.prepareStatement("SELECT legalnotices.*, clients.ClientName "
 207                     + "FROM legalnotices "
 208                     + "INNER JOIN clients WHERE ClientId=clients.id AND legalnotices.id=?;");
 209             st.setInt(1, selid);
 210             ResultSet rs = st.executeQuery();
 211             while (rs.next()) {
 212                 notice.add(rs.getString("ReferenceNumber"));
 213                 notice.add(rs.getString("Description"));
 214                 notice.add(getValidDate(rs, "EntrustmentDate"));
 215                 notice.add (rs.getInt ("ClientId"));
 216                 notice.add (rs.getBoolean("DraftCreated"));
 217                 notice.add (rs.getBoolean("DraftApproved"));
 218                 notice.add (rs.getBoolean("NoticeSent"));
 219                 notice.add (getValidDate(rs, "SentDate"));
 220                 notice.add (rs.getString("RPADReference"));
 221                 notice.add (rs.getBoolean("NoticeDelivered"));
 222                 notice.add (getValidDate(rs, "DeliveryDate"));
 223                 notice.add (rs.getString("BillStatus"));
 224                 notice.add (rs.getInt ("BillAmount"));
 225                 notice.add (getValidDate (rs, "BillDate"));
 226                 notice.add (rs.getBoolean ("ClarificationPending"));
 227                 notice.add (rs.getString("ClarificationRemarks"));
 228                 notice.add (rs.getString("ClientName"));
 229             }
 230            return notice;
 231         } catch (SQLException ex) {
 232             Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
 233             return null;
 234         }
 235
 236
 237     }
 238     private static final String JDBC = "jdbc:sqlite:";
 239
 240     static boolean updateNoticeDraftStatus(int selectednotice_id, boolean created, boolean approved) {
 241         try {
 242             Connection conn = DriverManager.getConnection(JDBC + connectionURL);
 243             PreparedStatement st = conn.prepareStatement("UPDATE legalnotices "
 244                     + "SET DraftCreated=?, DraftApproved=? WHERE id=?;");
 245             st.setBoolean(1, created);
 246             st.setBoolean(2, approved);
 247             st.setInt (3, selectednotice_id);
 248             st.execute();
 249             conn.close();
 250             return true;
 251         } catch (SQLException ex) {
 252             Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
 253             return false;
 254         }
 255
 256     }
 257
 258     static boolean updateNoticeDescription(int selectednotice_id, String text) {
 259         try {
 260             Connection conn = DriverManager.getConnection(JDBC + connectionURL);
 261             PreparedStatement st = conn.prepareStatement("UPDATE legalnotices "
 262                     + "SET Description=? WHERE id=?;");
 263             st.setString(1, text);
 264             st.setInt(2, selectednotice_id);
 265             st.execute();
 266             conn.close();
 267             return true;
 268         } catch (SQLException ex) {
 269             Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
 270             return false;
 271         }
 272
 273     }
 274
 275     static boolean updateNoticeDispatchDetails(int selid, boolean notice_sent,
 276             java.util.Date sent_date, String rpad_reference,
 277             boolean notice_delivered, java.util.Date delivery_date) {
 278         try {
 279             Connection conn = DriverManager.getConnection(JDBC + connectionURL);
 280             PreparedStatement st = conn.prepareStatement("UPDATE legalnotices"
 281                     + " SET NoticeSent=?, SentDate=?, RPADReference=?,"
 282                     + " NoticeDelivered=?, DeliveryDate=? WHERE id=?;");
 283
 284             st.setBoolean(1, notice_sent);
 285             st.setDate(2,  toSqlDate(sent_date));
 286             st.setString(3, rpad_reference);
 287             st.setBoolean(4, notice_delivered);
 288             st.setDate(5, toSqlDate(delivery_date));
 289             st.setInt (6, selid);
 290             st.execute();
 291             conn.close();
 292             return true;
 293         } catch (SQLException ex) {
 294             Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
 295             return false;
 296         }
 297
 298     }
 299
 300     private static Date toSqlDate(java.util.Date date) {
 301             if (date == null)
 302                 return null;
 303             Date sqldate = new Date (date.getTime()/1000);
 304             return sqldate;
 305     }
 306
 307     static boolean updateNoticeBillDetails(int selid, String bill_status,
 308             java.util.Date bill_date, int bill_amount) {
 309         try {
 310             Connection conn = DriverManager.getConnection(JDBC + connectionURL);
 311             PreparedStatement st = conn.prepareStatement("UPDATE legalnotices"
 312                     + " SET BillStatus=?,BillDate=?,BillAmount=? WHERE id=?;");
 313             st.setString (1, bill_status);
 314             st.setDate(2, toSqlDate(bill_date));
 315             st.setInt(3, bill_amount);
 316             st.setInt(4, selid);
 317             st.execute();
 318             conn.close();
 319             return true;
 320         } catch (SQLException ex) {
 321             Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
 322             return false;
 323         }
 324
 325     }
 326
 327     static boolean createDatabase(String db_path) {
 328         saveConnectionURL(db_path);
 329         String tblClients = "CREATE TABLE IF NOT EXISTS \"clients\" (\n" +
 330 "       \"id\"  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n" +
 331 "       \"ClientName\"  VARCHAR(255) NOT NULL UNIQUE,\n" +
 332 "       \"ClientAddress\"       TEXT,\n" +
 333 "       \"ContactPerson\"       TEXT,\n" +
 334 "       \"MailID\"      VARCHAR(255),\n" +
 335 "       \"ContactNumber\"       VARCHAR(30)\n" +
 336 "       );";
 337         String tblNotices = "CREATE TABLE IF NOT EXISTS \"legalnotices\" (\n" +
 338                 "       \"id\"  INTEGER PRIMARY KEY AUTOINCREMENT,\n" +
 339                 "       \"ReferenceNumber\"     TEXT NOT NULL UNIQUE,\n" +
 340                 "       \"Description\" TEXT NOT NULL,\n" +
 341                 "       \"EntrustmentDate\"     INTEGER NOT NULL,\n" +
 342                 "       \"ClientId\"    INTEGER,\n" +
 343                 "       \"DraftCreated\"        INTEGER DEFAULT 0,\n" +
 344                 "       \"DraftApproved\"       INTEGER DEFAULT 0 CHECK(DraftApproved<=DraftCreated),\n" +
 345                 "       \"NoticeSent\"  INTEGER DEFAULT 0 CHECK(NoticeSent<=DraftApproved),\n" +
 346                 "       \"SentDate\"    INTEGER CHECK(SentDate>=EntrustmentDate),\n" +
 347                 "       \"RPADReference\"       TEXT,\n" +
 348                 "       \"NoticeDelivered\"     INTEGER DEFAULT 0 CHECK(NoticeDelivered<=NoticeSent),\n" +
 349                 "       \"DeliveryDate\"        INTEGER CHECK(DeliveryDate>=SentDate),\n" +
 350                 "       \"BillStatus\"  TEXT DEFAULT 'PENDING',\n" +
 351                 "       \"BillAmount\"  INTEGER DEFAULT 1000 CHECK(BillAmount>0),\n" +
 352                 "       \"BillDate\"    INTEGER CHECK(BillDate>=DeliveryDate),\n" +
 353                 "       \"ClarificationPending\"        INTEGER DEFAULT 0,\n" +
 354                 "       \"ClarificationRemarks\"        TEXT\n" +
 355                 ");";
 356
 357         try {
 358             Connection conn = DriverManager.getConnection(JDBC + connectionURL);
 359             Statement st1 = conn.createStatement();
 360             st1.execute(tblClients);
 361             st1.execute(tblNotices);
 362             conn.close();
 363             return true;
 364         } catch (SQLException ex) {
 365             Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
 366             return false;
 367         }
 368
 369     }
 370
 371     static boolean updateNoticeClarificationDetails(int selected_id, boolean
 372             clarification_pending, String clarification_remarks) {
 373         try {
 374             Connection conn = DriverManager.getConnection(JDBC + connectionURL);
 375             PreparedStatement st = conn.prepareStatement ("UPDATE legalnotices"
 376                     + " SET ClarificationPending=?, ClarificationRemarks=? "
 377                     + "WHERE id=?;");
 378             st.setBoolean(1, clarification_pending);
 379             st.setString(2, clarification_remarks);
 380             st.setInt (3, selected_id);
 381             st.execute();
 382             conn.close ();
 383             return true;
 384         } catch (SQLException ex) {
 385             Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
 386             return false;
 387         }
 388
 389     }
 390
 391     static boolean deleteNotice(int r) {
 392         try {
 393             Connection conn = DriverManager.getConnection(JDBC + connectionURL);
 394             PreparedStatement st = conn.prepareStatement("DELETE FROM legalnotices"
 395                     + " WHERE id=?;");
 396             st.setInt(1, r);
 397             st.execute();
 398             conn.close();
 399             return true;
 400         } catch (SQLException ex) {
 401             Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
 402             return false;
 403         }
 404
 405     }
 406
 407     static ArrayList<Object> getRaisedBills (int client_id) {
 408         try {
 409             Connection conn = DriverManager.getConnection(JDBC + connectionURL);
 410             PreparedStatement st = conn.prepareStatement("SELECT ReferenceNumber, "
 411                      + "Description, BillDate, BillAmount"
 412                      + " FROM legalnotices WHERE BillStatus='RAISED' AND ClientId=?;");
 413             st.setInt (1, client_id);
 414
 415              ResultSet rs = st.executeQuery ();
 416              ArrayList<Object> bills = new ArrayList<>();
 417              while (rs.next()) {
 418                  bills.add (rs.getString("ReferenceNumber"));
 419                  bills.add (rs.getString("Description"));
 420                  bills.add (getValidDate(rs, "BillDate"));
 421                  bills.add (rs.getInt("BillAmount"));
 422              }
 423              conn.close();
 424              return bills;
 425
 426         } catch (SQLException ex) {
 427             Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
 428             return null;
 429         }
 430
 431     }
 432
 433     static boolean generateRaisedNoticesBill(String fileName, String clientName,
 434             String clientAddress, String contactPerson, ArrayList<Object> bills) {
 435         if (bills == null)
 436                 return false;
 437         try {
 438             InputStream templ = Utility.class.getClassLoader().getResourceAsStream("resources/noticebill.template.fodt");
 439             if (templ == null)
 440                 return false;
 441             DateFormat fmt = new SimpleDateFormat("dd MMM yyyy");
 442             String templateMain = new String (templ.readAllBytes());
 443             // this is for openoffice ODT - replace normal line breaks with the XML equivalent
 444             String left = leftLetterHeader.replaceAll("\n", "<text:line-break/>");
 445             String right = rightLetterHeader.replaceAll("\n", "<text:line-break/>");
 446             String client_address = clientAddress.replaceAll ("\n", "<text:line-break/>");
 447             String rows = generateBillRows (bills);
 448             System.out.println (rows);
 449
 450             String strMain = MessageFormat.format(templateMain,
 451                     left, right,
 452                     fmt.format(new Date(System.currentTimeMillis())),
 453                     clientName, client_address, contactPerson, signatoryName, rows);
 454
 455             FileOutputStream f = new FileOutputStream (fileName);
 456             f.write (strMain.getBytes());
 457             f.close();
 458             return true;
 459         } catch (IOException ex) {
 460             Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
 461             return false;
 462         }
 463     }
 464
 465     private static String generateBillRows(ArrayList<Object> bills) {
 466         try {
 467             InputStream tmpl = Utility.class.getClassLoader().getResourceAsStream("resources/tablerow.template.xml");
 468             String rowtpl = new String(tmpl.readAllBytes());
 469
 470             DateFormat fmt = new SimpleDateFormat("dd/MM/yyyy");
 471
 472           StringBuilder bldr = new StringBuilder ();
 473           for (int i = 0; i < bills.size(); i += 4) {
 474               String row = MessageFormat.format (rowtpl, (String)bills.get(i),
 475                       (String)bills.get(i+1), fmt.format((java.util.Date)bills.get(i+2)),
 476                               (int)bills.get(i+3));
 477               bldr.append(row);
 478           }
 479           return (bldr.toString());
 480         } catch (IOException ex) {
 481             Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
 482             return null;
 483         }
 484     }
 485
 486     static boolean updateNoticeBillStatus(int client_id, String from_status,
 487             String to_status) {
 488         try {
 489             Connection conn = DriverManager.getConnection(JDBC + connectionURL);
 490             PreparedStatement st = conn.prepareStatement("UPDATE legalnotices"
 491                     + " SET BillStatus=? WHERE BillStatus=? AND ClientId=?;");
 492             st.setString (1, to_status);
 493             st.setString (2, from_status);
 494             st.setInt (3, client_id);
 495             st.execute();
 496             conn.close();
 497             return true;
 498         } catch (SQLException ex) {
 499             Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
 500             return false;
 501         }
 502
 503     }
 504     public Utility () {
 505
 506     }
 507     public static String connectionURL ;
 508     public static String leftLetterHeader;
 509     public static String rightLetterHeader;
 510     public static String signatoryName;
 511
 512     /**
 513      *
 514      * @return
 515      */
 516     public static ArrayList<Object> getClientsNameAndId () {
 517         ArrayList<Object> data = new ArrayList<>();
 518         try {
 519             Connection conn = DriverManager.getConnection(JDBC + connectionURL);
 520             Statement st = conn.createStatement();
 521             ResultSet rs = st.executeQuery("SELECT id, ClientName from clients;");
 522             while (rs.next()) {
 523                 data.add(rs.getInt("id"));
 524                 data.add(rs.getString("ClientName"));
 525             }
 526             conn.close();
 527             return data;
 528         } catch (SQLException ex) {
 529             Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
 530             return null;
 531         }
 532
 533     }
 534
 535     public static void retrieveConnectionURL () {
 536         Preferences myPrefs = Preferences.userRoot().node("org/harishankar/Habeas");
 537         connectionURL = myPrefs.get("ConnectionURL", "legaldb");
 538     }
 539
 540 }