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
.util
.ArrayList
;
9 import java
.util
.prefs
.Preferences
;
11 import java
.util
.logging
.Level
;
12 import java
.util
.logging
.Logger
;
18 public class Utility
{
20 static void setConnectionURL(String text
) {
22 Preferences
.userRoot().put("ConnectionURL", text
);
25 static ArrayList
<Object
> getClientDetails(int r
) {
26 ArrayList
<Object
> res
= new ArrayList
<>();
28 Connection conn
= DriverManager
.getConnection(JDBC
+connectionURL
);
29 PreparedStatement st
= conn
.prepareStatement("SELECT * FROM clients WHERE id=?;");
31 ResultSet rs
= st
.executeQuery();
33 res
.add (rs
.getString("ClientName"));
34 res
.add(rs
.getString("ClientAddress"));
35 res
.add (rs
.getString("ContactPerson"));
36 res
.add (rs
.getString("MailID"));
37 res
.add (rs
.getString("ContactNumber"));
42 } catch (SQLException ex
) {
43 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
49 static boolean addClient(String client_name
, String client_address
,
50 String contact_person
, String email_id
, String phone_number
) {
51 if ("".equals(client_name
))
54 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
55 PreparedStatement st
= conn
.prepareStatement("INSERT INTO clients (ClientName,"
56 + "ClientAddress, ContactPerson, MailID, ContactNumber) VALUES (?, ?, ?, ?, ?);");
57 st
.setString(1, client_name
);
58 st
.setString(2, client_address
);
59 st
.setString(3, contact_person
);
60 st
.setString (4, email_id
);
61 st
.setString (5, phone_number
);
66 } catch (SQLException ex
) {
67 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
74 static boolean deleteClient(int r
) {
76 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
77 PreparedStatement st
= conn
.prepareStatement("DELETE FROM clients WHERE id=?;");
78 PreparedStatement st2
= conn
.prepareStatement("DELETE FROM legalnotices WHERE ClientId=?;");
85 } catch (SQLException ex
) {
86 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
92 static boolean updateClient(int r
, String client_name
, String client_address
,
93 String contact_person
, String mail_id
, String phone_number
) {
94 if ("".equals(client_name
))
97 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
98 PreparedStatement st
= conn
.prepareStatement("UPDATE clients SET "
99 + "ClientName=?, ClientAddress=?,"
100 + "ContactPerson=?, MailID=?, ContactNumber=? WHERE id=?;");
101 st
.setString(1, client_name
);
102 st
.setString(2, client_address
);
103 st
.setString(3, contact_person
);
104 st
.setString(4, mail_id
);
105 st
.setString(5, phone_number
);
110 } catch (SQLException ex
) {
111 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
118 static boolean addLegalNotice(String reference_number
,
119 String description
, java
.util
.Date entrustment_date
, DBItem client
) {
120 if ("".equals(reference_number
) || "".equals(description
) ||
121 entrustment_date
== null || client
== null)
124 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
125 PreparedStatement st
= conn
.prepareStatement("INSERT INTO legalnotices"
126 + " (ReferenceNumber, Description, EntrustmentDate, ClientId) "
127 + "VALUES (?, ?, ?, ?);");
128 st
.setString(1, reference_number
);
129 st
.setString(2, description
);
130 st
.setLong(3, entrustment_date
.getTime()/1000);
131 st
.setInt (4, client
.getKey());
136 } catch (SQLException ex
) {
137 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
143 static ArrayList
<Object
> getNotices() {
144 ArrayList
<Object
> notices
= new ArrayList
<>();
146 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
147 Statement st
= conn
.createStatement();
148 ResultSet rs
= st
.executeQuery("SELECT id, ReferenceNumber, Description "
149 + "FROM legalnotices;");
151 notices
.add(rs
.getInt("id"));
152 notices
.add(rs
.getString("ReferenceNumber"));
153 notices
.add(rs
.getString("Description"));
157 } catch (SQLException ex
) {
158 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
163 static java
.util
.Date
getValidDate (ResultSet rs
, String datefield
) throws SQLException
{
164 // Since resultset.getDate returns a java.sql.Date, we need a way to get
165 // the date in java.util.Date which is usable in our application. Hence
166 // this helper function. Since we don't want a valid Date object if the
167 // field is null, we are using this if clause
168 if (rs
.getDate(datefield
) == null)
171 return (new java
.util
.Date(rs
.getLong(datefield
)*1000));
174 static ArrayList
<Object
> getNoticeDetails(int selid
) {
175 ArrayList
<Object
> notice
= new ArrayList
<>();
177 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
178 PreparedStatement st
= conn
.prepareStatement("SELECT * FROM legalnotices WHERE id=?;");
180 ResultSet rs
= st
.executeQuery();
182 notice
.add(rs
.getString("ReferenceNumber"));
183 notice
.add(rs
.getString("Description"));
184 notice
.add(getValidDate(rs
, "EntrustmentDate"));
185 notice
.add (rs
.getInt ("ClientId"));
186 notice
.add (rs
.getBoolean("DraftCreated"));
187 notice
.add (rs
.getBoolean("DraftApproved"));
188 notice
.add (rs
.getBoolean("NoticeSent"));
189 notice
.add (getValidDate(rs
, "SentDate"));
190 notice
.add (rs
.getString("RPADReference"));
191 notice
.add (rs
.getBoolean("NoticeDelivered"));
192 notice
.add (getValidDate(rs
, "DeliveryDate"));
193 notice
.add (rs
.getString("BillStatus"));
194 notice
.add (rs
.getInt ("BillAmount"));
195 notice
.add (getValidDate (rs
, "BillDate"));
196 notice
.add (rs
.getBoolean ("ClarificationPending"));
197 notice
.add (rs
.getString("ClarificationRemarks"));
200 } catch (SQLException ex
) {
201 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
207 private static final String JDBC
= "jdbc:sqlite:";
209 static boolean updateNoticeDraftStatus(int selectednotice_id
, boolean created
, boolean approved
) {
211 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
212 PreparedStatement st
= conn
.prepareStatement("UPDATE legalnotices "
213 + "SET DraftCreated=?, DraftApproved=? WHERE id=?;");
214 st
.setBoolean(1, created
);
215 st
.setBoolean(2, approved
);
216 st
.setInt (3, selectednotice_id
);
220 } catch (SQLException ex
) {
221 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
227 static boolean updateNoticeDescription(int selectednotice_id
, String text
) {
229 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
230 PreparedStatement st
= conn
.prepareStatement("UPDATE legalnotices "
231 + "SET Description=? WHERE id=?;");
232 st
.setString(1, text
);
233 st
.setInt(2, selectednotice_id
);
237 } catch (SQLException ex
) {
238 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
244 static boolean updateNoticeDispatchDetails(int selid
, boolean notice_sent
,
245 java
.util
.Date sent_date
, String rpad_reference
,
246 boolean notice_delivered
, java
.util
.Date delivery_date
) {
248 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
249 PreparedStatement st
= conn
.prepareStatement("UPDATE legalnotices"
250 + " SET NoticeSent=?, SentDate=?, RPADReference=?,"
251 + " NoticeDelivered=?, DeliveryDate=? WHERE id=?;");
253 st
.setBoolean(1, notice_sent
);
254 st
.setDate(2, toSqlDate(sent_date
));
255 st
.setString(3, rpad_reference
);
256 st
.setBoolean(4, notice_delivered
);
257 st
.setDate(5, toSqlDate(delivery_date
));
258 st
.setInt (6, selid
);
262 } catch (SQLException ex
) {
263 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
269 private static Date
toSqlDate(java
.util
.Date date
) {
272 Date sqldate
= new Date (date
.getTime()/1000);
276 static boolean updateNoticeBillDetails(int selid
, String bill_status
,
277 java
.util
.Date bill_date
, int bill_amount
) {
279 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
280 PreparedStatement st
= conn
.prepareStatement("UPDATE legalnotices"
281 + " SET BillStatus=?,BillDate=?,BillAmount=? WHERE id=?;");
282 st
.setString (1, bill_status
);
283 st
.setDate(2, toSqlDate(bill_date
));
284 st
.setInt(3, bill_amount
);
289 } catch (SQLException ex
) {
290 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
298 public static String connectionURL
;
304 public static ArrayList
<Object
> getClientsNameAndId () {
305 ArrayList
<Object
> data
= new ArrayList
<>();
307 Connection conn
= DriverManager
.getConnection(JDBC
+ connectionURL
);
308 Statement st
= conn
.createStatement();
309 ResultSet rs
= st
.executeQuery("SELECT id, ClientName from clients;");
311 data
.add(rs
.getInt("id"));
312 data
.add(rs
.getString("ClientName"));
316 } catch (SQLException ex
) {
317 Logger
.getLogger(Utility
.class.getName()).log(Level
.SEVERE
, null, ex
);
323 public static void getConnectionURL () {
324 connectionURL
= Preferences
.userRoot().get("ConnectionURL", "legaldb");