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