*/
package habeas;
+import java.io.FileNotFoundException;
+import java.io.FileOutputStream;
+import java.io.IOException;
+import java.io.InputStream;
import java.util.ArrayList;
import java.util.prefs.Preferences;
import java.sql.*;
+import java.text.DateFormat;
+import java.text.MessageFormat;
+import java.text.SimpleDateFormat;
+import java.util.Arrays;
import java.util.logging.Level;
import java.util.logging.Logger;
+import jdk.dynalink.linker.support.Guards;
/**
*
* @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 myPrefs = Preferences.userRoot().node("org/harishankar/Habeas");
+ myPrefs.put ("LeftHeader", left_header);
+ myPrefs.put ("RightHeader", right_header);
+ myPrefs.put ("Signatory", signatory);
+ }
+
+ static void retrieveStationerySettings () {
+ Preferences myPrefs = Preferences.userRoot().node ("org/harishankar/Habeas");
+ leftLetterHeader = myPrefs.get("LeftHeader", "Left Header");
+ rightLetterHeader = myPrefs.get ("RightHeader", "Right Header");
+ signatoryName = myPrefs.get ("Signatory", "Signatory Name");
+ }
- static void setConnectionURL(String text) {
+ static void saveConnectionURL(String text) {
connectionURL = text;
- Preferences.userRoot().put("ConnectionURL", text);
+ Preferences myPrefs = Preferences.userRoot().node ("org/harishankar/Habeas");
+ myPrefs.put("ConnectionURL", text);
}
static ArrayList<Object> getClientDetails(int r) {
ArrayList<Object> 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();
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);
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);
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=?;");
static boolean addLegalNotice(String reference_number,
String description, java.util.Date entrustment_date, DBItem client) {
if ("".equals(reference_number) || "".equals(description) ||
- entrustment_date == null)
+ entrustment_date == null || client == null)
return false;
try {
- Connection conn = DriverManager.getConnection("jdbc:sqlite:" + connectionURL);
+ Connection conn = DriverManager.getConnection(JDBC + connectionURL);
PreparedStatement st = conn.prepareStatement("INSERT INTO legalnotices"
+ " (ReferenceNumber, Description, EntrustmentDate, ClientId) "
+ "VALUES (?, ?, ?, ?);");
return false;
}
+ }
+
+ static ArrayList<Object> getNotices() {
+ ArrayList<Object> 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<Object> getNoticeDetails(int selid) {
+ ArrayList<Object> 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;
+ }
+
+ }
+
+ static ArrayList<Object> getRaisedBills (int client_id) {
+ try {
+ Connection conn = DriverManager.getConnection(JDBC + connectionURL);
+ PreparedStatement st = conn.prepareStatement("SELECT ReferenceNumber, "
+ + "Description, BillDate, BillAmount"
+ + " FROM legalnotices WHERE BillStatus='RAISED' AND ClientId=?;");
+ st.setInt (1, client_id);
+
+ ResultSet rs = st.executeQuery ();
+ ArrayList<Object> bills = new ArrayList<>();
+ while (rs.next()) {
+ bills.add (rs.getString("ReferenceNumber"));
+ bills.add (rs.getString("Description"));
+ bills.add (getValidDate(rs, "BillDate"));
+ bills.add (rs.getInt("BillAmount"));
+ }
+ conn.close();
+ return bills;
+
+ } catch (SQLException ex) {
+ Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
+ return null;
+ }
+
+ }
+
+ static boolean generateRaisedNoticesBill(String fileName, String clientName,
+ String clientAddress, String contactPerson, ArrayList<Object> bills) {
+ if (bills == null)
+ return false;
+ try {
+ InputStream templ = Utility.class.getClassLoader().getResourceAsStream("resources/noticebill.template.fodt");
+ if (templ == null)
+ return false;
+ DateFormat fmt = new SimpleDateFormat("dd MMM yyyy");
+ String templateMain = new String (templ.readAllBytes());
+ // this is for openoffice ODT - replace normal line breaks with the XML equivalent
+ String left = leftLetterHeader.replaceAll("\n", "<text:line-break/>");
+ String right = rightLetterHeader.replaceAll("\n", "<text:line-break/>");
+ String client_address = clientAddress.replaceAll ("\n", "<text:line-break/>");
+ String rows = generateBillRows (bills);
+ System.out.println (rows);
+
+ String strMain = MessageFormat.format(templateMain,
+ left, right,
+ fmt.format(new Date(System.currentTimeMillis())),
+ clientName, client_address, contactPerson, signatoryName, rows);
+
+ FileOutputStream f = new FileOutputStream (fileName);
+ f.write (strMain.getBytes());
+ f.close();
+ return true;
+ } catch (IOException ex) {
+ Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
+ return false;
+ }
+ }
+
+ private static String generateBillRows(ArrayList<Object> bills) {
+ try {
+ InputStream tmpl = Utility.class.getClassLoader().getResourceAsStream("resources/tablerow.template.xml");
+ String rowtpl = new String(tmpl.readAllBytes());
+
+ DateFormat fmt = new SimpleDateFormat("dd/MM/yyyy");
+
+ StringBuilder bldr = new StringBuilder ();
+ for (int i = 0; i < bills.size(); i += 4) {
+ String row = MessageFormat.format (rowtpl, (String)bills.get(i),
+ (String)bills.get(i+1), fmt.format((java.util.Date)bills.get(i+2)),
+ (int)bills.get(i+3));
+ bldr.append(row);
+ }
+ return (bldr.toString());
+ } catch (IOException ex) {
+ Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
+ return null;
+ }
+ }
+
+ static boolean updateNoticeBillStatus(int client_id, String from_status,
+ String to_status) {
+ try {
+ Connection conn = DriverManager.getConnection(JDBC + connectionURL);
+ PreparedStatement st = conn.prepareStatement("UPDATE legalnotices"
+ + " SET BillStatus=? WHERE BillStatus=? AND ClientId=?;");
+ st.setString (1, to_status);
+ st.setString (2, from_status);
+ st.setInt (3, client_id);
+ 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;
/**
*
public static ArrayList<Object> getClientsNameAndId () {
ArrayList<Object> 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()) {
}
- public static void getConnectionURL () {
- connectionURL = Preferences.userRoot().get("ConnectionURL", "legaldb");
+ public static void retrieveConnectionURL () {
+ Preferences myPrefs = Preferences.userRoot().node("org/harishankar/Habeas");
+ connectionURL = myPrefs.get("ConnectionURL", "legaldb");
}
}
projects / habeas.git / blobdiff