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