X-Git-Url: https://harishankar.org/repos/?p=habeas.git;a=blobdiff_plain;f=src%2Fhabeas%2FUtility.java;h=0dc423577bf8e1d96e18f2948906dabad3d16dc1;hp=3eabd875efad444d5c9b3c097a37df3bd2ca66bf;hb=9ec7870cd70d09a7d67efb17dae44baa51f77957;hpb=3d2f6cfab3becbf7ad69924e63b91fa7e2745fc7 diff --git a/src/habeas/Utility.java b/src/habeas/Utility.java index 3eabd87..0dc4235 100644 --- a/src/habeas/Utility.java +++ b/src/habeas/Utility.java @@ -8,19 +8,32 @@ package habeas; import java.util.ArrayList; import java.util.prefs.Preferences; import java.sql.*; -import java.util.Dictionary; -import java.util.HashMap; import java.util.logging.Level; import java.util.logging.Logger; -import javax.swing.JOptionPane; /** * * @author hari */ public class Utility { + + static void saveStationerySettings (String left_header, + String right_header, String signatory) { + leftLetterHeader = left_header; + rightLetterHeader = right_header; + signatoryName = signatory; + Preferences.userRoot().put ("LeftHeader", left_header); + Preferences.userRoot().put ("RightHeader", right_header); + Preferences.userRoot().put ("Signatory", signatory); + } + + static void retrieveStationerySettings () { + leftLetterHeader = Preferences.userRoot().get("LeftHeader", "Left Header"); + rightLetterHeader = Preferences.userRoot().get ("RightHeader", "Right Header"); + signatoryName = Preferences.userRoot().get ("Signatory", "Signatory Name"); + } - static void setConnectionURL(String text) { + static void saveConnectionURL(String text) { connectionURL = text; Preferences.userRoot().put("ConnectionURL", text); } @@ -28,7 +41,7 @@ public class Utility { static ArrayList getClientDetails(int r) { ArrayList res = new ArrayList <>(); try { - Connection conn = DriverManager.getConnection("jdbc:sqlite:"+connectionURL); + Connection conn = DriverManager.getConnection(JDBC+connectionURL); PreparedStatement st = conn.prepareStatement("SELECT * FROM clients WHERE id=?;"); st.setInt(1, r); ResultSet rs = st.executeQuery(); @@ -54,7 +67,7 @@ public class Utility { if ("".equals(client_name)) return false; try { - Connection conn = DriverManager.getConnection("jdbc:sqlite:" + connectionURL); + Connection conn = DriverManager.getConnection(JDBC + connectionURL); PreparedStatement st = conn.prepareStatement("INSERT INTO clients (ClientName," + "ClientAddress, ContactPerson, MailID, ContactNumber) VALUES (?, ?, ?, ?, ?);"); st.setString(1, client_name); @@ -76,7 +89,7 @@ public class Utility { static boolean deleteClient(int r) { try { - Connection conn = DriverManager.getConnection("jdbc:sqlite:" + connectionURL); + Connection conn = DriverManager.getConnection(JDBC + connectionURL); PreparedStatement st = conn.prepareStatement("DELETE FROM clients WHERE id=?;"); PreparedStatement st2 = conn.prepareStatement("DELETE FROM legalnotices WHERE ClientId=?;"); st.setInt(1, r); @@ -97,7 +110,7 @@ public class Utility { if ("".equals(client_name)) return false; try { - Connection conn = DriverManager.getConnection("jdbc:sqlite:" + connectionURL); + Connection conn = DriverManager.getConnection(JDBC + connectionURL); PreparedStatement st = conn.prepareStatement("UPDATE clients SET " + "ClientName=?, ClientAddress=?," + "ContactPerson=?, MailID=?, ContactNumber=? WHERE id=?;"); @@ -116,11 +129,275 @@ public class Utility { } + } + + static boolean addLegalNotice(String reference_number, + String description, java.util.Date entrustment_date, DBItem client) { + if ("".equals(reference_number) || "".equals(description) || + entrustment_date == null || client == null) + return false; + try { + Connection conn = DriverManager.getConnection(JDBC + connectionURL); + PreparedStatement st = conn.prepareStatement("INSERT INTO legalnotices" + + " (ReferenceNumber, Description, EntrustmentDate, ClientId) " + + "VALUES (?, ?, ?, ?);"); + st.setString(1, reference_number); + st.setString(2, description); + st.setLong(3, entrustment_date.getTime()/1000); + st.setInt (4, client.getKey()); + st.execute(); + conn.close(); + return true; + + } catch (SQLException ex) { + Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex); + return false; + } + + } + + static ArrayList getNotices() { + ArrayList notices = new ArrayList<>(); + try { + Connection conn = DriverManager.getConnection(JDBC + connectionURL); + Statement st = conn.createStatement(); + ResultSet rs = st.executeQuery("SELECT id, ReferenceNumber, Description " + + "FROM legalnotices;"); + while (rs.next()) { + notices.add(rs.getInt("id")); + notices.add(rs.getString("ReferenceNumber")); + notices.add(rs.getString("Description")); + } + conn.close(); + return notices; + } catch (SQLException ex) { + Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex); + return null; + } + + } + static java.util.Date getValidDate (ResultSet rs, String datefield) throws SQLException { + // Since resultset.getDate returns a java.sql.Date, we need a way to get + // the date in java.util.Date which is usable in our application. Hence + // this helper function. Since we don't want a valid Date object if the + // field is null, we are using this if clause + if (rs.getDate(datefield) == null) + return null; + else + return (new java.util.Date(rs.getLong(datefield)*1000)); + } + + static ArrayList getNoticeDetails(int selid) { + ArrayList notice = new ArrayList<>(); + try { + Connection conn = DriverManager.getConnection(JDBC + connectionURL); + PreparedStatement st = conn.prepareStatement("SELECT legalnotices.*, clients.ClientName " + + "FROM legalnotices " + + "INNER JOIN clients WHERE ClientId=clients.id AND legalnotices.id=?;"); + st.setInt(1, selid); + ResultSet rs = st.executeQuery(); + while (rs.next()) { + notice.add(rs.getString("ReferenceNumber")); + notice.add(rs.getString("Description")); + notice.add(getValidDate(rs, "EntrustmentDate")); + notice.add (rs.getInt ("ClientId")); + notice.add (rs.getBoolean("DraftCreated")); + notice.add (rs.getBoolean("DraftApproved")); + notice.add (rs.getBoolean("NoticeSent")); + notice.add (getValidDate(rs, "SentDate")); + notice.add (rs.getString("RPADReference")); + notice.add (rs.getBoolean("NoticeDelivered")); + notice.add (getValidDate(rs, "DeliveryDate")); + notice.add (rs.getString("BillStatus")); + notice.add (rs.getInt ("BillAmount")); + notice.add (getValidDate (rs, "BillDate")); + notice.add (rs.getBoolean ("ClarificationPending")); + notice.add (rs.getString("ClarificationRemarks")); + notice.add (rs.getString("ClientName")); + } + return notice; + } catch (SQLException ex) { + Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex); + return null; + } + + + } + private static final String JDBC = "jdbc:sqlite:"; + + static boolean updateNoticeDraftStatus(int selectednotice_id, boolean created, boolean approved) { + try { + Connection conn = DriverManager.getConnection(JDBC + connectionURL); + PreparedStatement st = conn.prepareStatement("UPDATE legalnotices " + + "SET DraftCreated=?, DraftApproved=? WHERE id=?;"); + st.setBoolean(1, created); + st.setBoolean(2, approved); + st.setInt (3, selectednotice_id); + st.execute(); + conn.close(); + return true; + } catch (SQLException ex) { + Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex); + return false; + } + + } + + static boolean updateNoticeDescription(int selectednotice_id, String text) { + try { + Connection conn = DriverManager.getConnection(JDBC + connectionURL); + PreparedStatement st = conn.prepareStatement("UPDATE legalnotices " + + "SET Description=? WHERE id=?;"); + st.setString(1, text); + st.setInt(2, selectednotice_id); + st.execute(); + conn.close(); + return true; + } catch (SQLException ex) { + Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex); + return false; + } + + } + + static boolean updateNoticeDispatchDetails(int selid, boolean notice_sent, + java.util.Date sent_date, String rpad_reference, + boolean notice_delivered, java.util.Date delivery_date) { + try { + Connection conn = DriverManager.getConnection(JDBC + connectionURL); + PreparedStatement st = conn.prepareStatement("UPDATE legalnotices" + + " SET NoticeSent=?, SentDate=?, RPADReference=?," + + " NoticeDelivered=?, DeliveryDate=? WHERE id=?;"); + + st.setBoolean(1, notice_sent); + st.setDate(2, toSqlDate(sent_date)); + st.setString(3, rpad_reference); + st.setBoolean(4, notice_delivered); + st.setDate(5, toSqlDate(delivery_date)); + st.setInt (6, selid); + st.execute(); + conn.close(); + return true; + } catch (SQLException ex) { + Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex); + return false; + } + + } + + private static Date toSqlDate(java.util.Date date) { + if (date == null) + return null; + Date sqldate = new Date (date.getTime()/1000); + return sqldate; + } + + static boolean updateNoticeBillDetails(int selid, String bill_status, + java.util.Date bill_date, int bill_amount) { + try { + Connection conn = DriverManager.getConnection(JDBC + connectionURL); + PreparedStatement st = conn.prepareStatement("UPDATE legalnotices" + + " SET BillStatus=?,BillDate=?,BillAmount=? WHERE id=?;"); + st.setString (1, bill_status); + st.setDate(2, toSqlDate(bill_date)); + st.setInt(3, bill_amount); + st.setInt(4, selid); + st.execute(); + conn.close(); + return true; + } catch (SQLException ex) { + Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex); + return false; + } + + } + + static boolean createDatabase(String db_path) { + saveConnectionURL(db_path); + String tblClients = "CREATE TABLE IF NOT EXISTS \"clients\" (\n" + +" \"id\" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n" + +" \"ClientName\" VARCHAR(255) NOT NULL UNIQUE,\n" + +" \"ClientAddress\" TEXT,\n" + +" \"ContactPerson\" TEXT,\n" + +" \"MailID\" VARCHAR(255),\n" + +" \"ContactNumber\" VARCHAR(30)\n" + +" );"; + String tblNotices = "CREATE TABLE IF NOT EXISTS \"legalnotices\" (\n" + + " \"id\" INTEGER PRIMARY KEY AUTOINCREMENT,\n" + + " \"ReferenceNumber\" TEXT NOT NULL UNIQUE,\n" + + " \"Description\" TEXT NOT NULL,\n" + + " \"EntrustmentDate\" INTEGER NOT NULL,\n" + + " \"ClientId\" INTEGER,\n" + + " \"DraftCreated\" INTEGER DEFAULT 0,\n" + + " \"DraftApproved\" INTEGER DEFAULT 0 CHECK(DraftApproved<=DraftCreated),\n" + + " \"NoticeSent\" INTEGER DEFAULT 0 CHECK(NoticeSent<=DraftApproved),\n" + + " \"SentDate\" INTEGER CHECK(SentDate>=EntrustmentDate),\n" + + " \"RPADReference\" TEXT,\n" + + " \"NoticeDelivered\" INTEGER DEFAULT 0 CHECK(NoticeDelivered<=NoticeSent),\n" + + " \"DeliveryDate\" INTEGER CHECK(DeliveryDate>=SentDate),\n" + + " \"BillStatus\" TEXT DEFAULT 'PENDING',\n" + + " \"BillAmount\" INTEGER DEFAULT 1000 CHECK(BillAmount>0),\n" + + " \"BillDate\" INTEGER CHECK(BillDate>=DeliveryDate),\n" + + " \"ClarificationPending\" INTEGER DEFAULT 0,\n" + + " \"ClarificationRemarks\" TEXT\n" + + ");"; + + try { + Connection conn = DriverManager.getConnection(JDBC + connectionURL); + Statement st1 = conn.createStatement(); + st1.execute(tblClients); + st1.execute(tblNotices); + conn.close(); + return true; + } catch (SQLException ex) { + Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex); + return false; + } + + } + + static boolean updateNoticeClarificationDetails(int selected_id, boolean + clarification_pending, String clarification_remarks) { + try { + Connection conn = DriverManager.getConnection(JDBC + connectionURL); + PreparedStatement st = conn.prepareStatement ("UPDATE legalnotices" + + " SET ClarificationPending=?, ClarificationRemarks=? " + + "WHERE id=?;"); + st.setBoolean(1, clarification_pending); + st.setString(2, clarification_remarks); + st.setInt (3, selected_id); + st.execute(); + conn.close (); + return true; + } catch (SQLException ex) { + Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex); + return false; + } + + } + + static boolean deleteNotice(int r) { + try { + Connection conn = DriverManager.getConnection(JDBC + connectionURL); + PreparedStatement st = conn.prepareStatement("DELETE FROM legalnotices" + + " WHERE id=?;"); + st.setInt(1, r); + st.execute(); + conn.close(); + return true; + } catch (SQLException ex) { + Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex); + return false; + } + } public Utility () { } public static String connectionURL ; + public static String leftLetterHeader; + public static String rightLetterHeader; + public static String signatoryName; /** * @@ -129,7 +406,7 @@ public class Utility { public static ArrayList getClientsNameAndId () { ArrayList data = new ArrayList<>(); try { - Connection conn = DriverManager.getConnection("jdbc:sqlite:" + connectionURL); + Connection conn = DriverManager.getConnection(JDBC + connectionURL); Statement st = conn.createStatement(); ResultSet rs = st.executeQuery("SELECT id, ClientName from clients;"); while (rs.next()) { @@ -145,7 +422,7 @@ public class Utility { } - public static void getConnectionURL () { + public static void retrieveConnectionURL () { connectionURL = Preferences.userRoot().get("ConnectionURL", "legaldb"); }