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.
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
;
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
;
27 public class Utility
{
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
);
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");
47 static void saveConnectionURL(String text
) {
49 Preferences myPrefs
= Preferences
.userRoot().node ("org/harishankar/Habeas");
50 myPrefs
.put("ConnectionURL", text
);
53 static ArrayList
<Object
> getClientDetails(int r
) {
54 ArrayList
<Object
> res
= new ArrayList
<>();
56 Connection conn
= DriverManager
.getConnection(JDBC
+connectionURL
);
57 PreparedStatement st
= conn
.prepareStatement("SELECT * FROM clients WHERE id=?;");
59 ResultSet rs
= st
.executeQuery();
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"));
70 } catch (SQLException ex
) {
71 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
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
))
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
);
94 } catch (SQLException ex
) {
95 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
102 static boolean deleteClient(int r
) {
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=?;");
113 } catch (SQLException ex
) {
114 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
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
))
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
);
138 } catch (SQLException ex
) {
139 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
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)
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());
164 } catch (SQLException ex
) {
165 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
171 static ArrayList
<Object
> getNotices() {
172 ArrayList
<Object
> notices
= new ArrayList
<>();
174 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
175 Statement st
= conn
.createStatement();
176 ResultSet rs
= st
.executeQuery("SELECT id, ReferenceNumber, Description "
177 + "FROM legalnotices;");
179 notices
.add(rs
.getInt("id"));
180 notices
.add(rs
.getString("ReferenceNumber"));
181 notices
.add(rs
.getString("Description"));
185 } catch (SQLException ex
) {
186 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
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)
199 return (new java
.util
.Date(rs
.getLong(datefield
)*1000));
202 static ArrayList
<Object
> getNoticeDetails(int selid
) {
203 ArrayList
<Object
> notice
= new ArrayList
<>();
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=?;");
210 ResultSet rs
= st
.executeQuery();
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"));
231 } catch (SQLException ex
) {
232 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
238 private static final String JDBC
= "jdbc:sqlite:";
240 static boolean updateNoticeDraftStatus(int selectednotice_id
, boolean created
, boolean approved
) {
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
);
251 } catch (SQLException ex
) {
252 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
258 static boolean updateNoticeDescription(int selectednotice_id
, String text
) {
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
);
268 } catch (SQLException ex
) {
269 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
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
) {
279 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
280 PreparedStatement st
= conn
.prepareStatement("UPDATE legalnotices"
281 + " SET NoticeSent=?, SentDate=?, RPADReference=?,"
282 + " NoticeDelivered=?, DeliveryDate=? WHERE id=?;");
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
);
293 } catch (SQLException ex
) {
294 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
300 private static Date
toSqlDate(java
.util
.Date date
) {
303 Date sqldate
= new Date (date
.getTime()/1000);
307 static boolean updateNoticeBillDetails(int selid
, String bill_status
,
308 java
.util
.Date bill_date
, int bill_amount
) {
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
);
320 } catch (SQLException ex
) {
321 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
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" +
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" +
358 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
359 Statement st1
= conn
.createStatement();
360 st1
.execute(tblClients
);
361 st1
.execute(tblNotices
);
364 } catch (SQLException ex
) {
365 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
371 static boolean updateNoticeClarificationDetails(int selected_id
, boolean
372 clarification_pending
, String clarification_remarks
) {
374 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
375 PreparedStatement st
= conn
.prepareStatement ("UPDATE legalnotices"
376 + " SET ClarificationPending=?, ClarificationRemarks=? "
378 st
.setBoolean(1, clarification_pending
);
379 st
.setString(2, clarification_remarks
);
380 st
.setInt (3, selected_id
);
384 } catch (SQLException ex
) {
385 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
391 static boolean deleteNotice(int r
) {
393 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
394 PreparedStatement st
= conn
.prepareStatement("DELETE FROM legalnotices"
400 } catch (SQLException ex
) {
401 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
407 static ArrayList
<Object
> getRaisedBills (int client_id
) {
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
);
415 ResultSet rs
= st
.executeQuery ();
416 ArrayList
<Object
> bills
= new ArrayList
<>();
418 bills
.add (rs
.getString("ReferenceNumber"));
419 bills
.add (rs
.getString("Description"));
420 bills
.add (getValidDate(rs
, "BillDate"));
421 bills
.add (rs
.getInt("BillAmount"));
426 } catch (SQLException ex
) {
427 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
433 static boolean generateRaisedNoticesBill(String fileName
, String clientName
,
434 String clientAddress
, String contactPerson
, ArrayList
<Object
> bills
) {
438 InputStream templ
= Utility
.class.getClassLoader().getResourceAsStream("resources/noticebill.template.fodt");
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
);
450 String strMain
= MessageFormat
.format(templateMain
,
452 fmt
.format(new Date(System
.currentTimeMillis())),
453 clientName
, client_address
, contactPerson
, signatoryName
, rows
);
455 FileOutputStream f
= new FileOutputStream (fileName
);
456 f
.write (strMain
.getBytes());
459 } catch (IOException ex
) {
460 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
465 private static String
generateBillRows(ArrayList
<Object
> bills
) {
467 InputStream tmpl
= Utility
.class.getClassLoader().getResourceAsStream("resources/tablerow.template.xml");
468 String rowtpl
= new String(tmpl
.readAllBytes());
470 DateFormat fmt
= new SimpleDateFormat("dd/MM/yyyy");
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));
479 return (bldr
.toString());
480 } catch (IOException ex
) {
481 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
488 public static String connectionURL
;
489 public static String leftLetterHeader
;
490 public static String rightLetterHeader
;
491 public static String signatoryName
;
497 public static ArrayList
<Object
> getClientsNameAndId () {
498 ArrayList
<Object
> data
= new ArrayList
<>();
500 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
501 Statement st
= conn
.createStatement();
502 ResultSet rs
= st
.executeQuery("SELECT id, ClientName from clients;");
504 data
.add(rs
.getInt("id"));
505 data
.add(rs
.getString("ClientName"));
509 } catch (SQLException ex
) {
510 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
516 public static void retrieveConnectionURL () {
517 Preferences myPrefs
= Preferences
.userRoot().node("org/harishankar/Habeas");
518 connectionURL
= myPrefs
.get("ConnectionURL", "legaldb");