*/
package habeas;
+import java.io.FileOutputStream;
+import java.io.FileWriter;
+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.logging.Level;
import java.util.logging.Logger;
+import org.sqlite.util.StringUtils;
/**
*
*/
public class Utility {
+ private static final String[] REPORTS_UNFILTERED = { "SELECT ReferenceNumber, Description"
+ + ", BillDate, BillAmount, ClientName FROM legalnotices INNER JOIN "
+ + "clients ON ClientId=clients.id WHERE BillStatus='AWAITING PAYMENT';",
+
+ "SELECT ReferenceNumber, Description, SentDate, RPADReference, "
+ + "ClientName FROM legalnotices INNER JOIN clients ON ClientId=clients.id"
+ + " WHERE NoticeSent=1 AND NoticeDelivered=0;",
+
+ "SELECT ReferenceNumber, Description, EntrustmentDate, ClientName FROM legalnotices"
+ + " INNER JOIN clients ON ClientId=clients.id"
+ + " WHERE DraftCreated=1 AND DraftApproved=0;",
+
+ "SELECT ReferenceNumber, Description, EntrustmentDate, "
+ + "ClarificationRemarks, ClientName FROM legalnotices INNER JOIN clients ON"
+ + " ClientId=clients.id WHERE ClarificationPending=1;",
+
+ "SELECT ReferenceNumber, Description, EntrustmentDate, ClarificationRemarks"
+ + ", ClientName from legalnotices INNER JOIN clients ON ClientId=clients.id "
+ + "WHERE DraftCreated=0;",
+
+ "SELECT ReferenceNumber, Description, EntrustmentDate, ClientName FROM"
+ + " legalnotices INNER JOIN clients ON ClientId=clients.id WHERE "
+ + " DraftApproved=1 AND NoticeSent=0;"
+ } ;
+
+ private static final String[] REPORTS_FILTERED = { "SELECT ReferenceNumber, Description"
+ + ", BillDate, BillAmount, ClientName FROM legalnotices INNER JOIN "
+ + "clients ON ClientId=clients.id WHERE BillStatus='AWAITING PAYMENT' "
+ + "AND ClientId=?;",
+
+ "SELECT ReferenceNumber, Description, SentDate, RPADReference, "
+ + "ClientName FROM legalnotices INNER JOIN clients ON ClientId=clients.id"
+ + " WHERE NoticeSent=1 AND NoticeDelivered=0 AND ClientId=?;",
+
+ "SELECT ReferenceNumber, Description, EntrustmentDate, ClientName FROM legalnotices"
+ + " INNER JOIN clients ON ClientId=clients.id"
+ + " WHERE DraftCreated=1 AND DraftApproved=0 AND ClientId=?;",
+
+ "SELECT ReferenceNumber, Description, EntrustmentDate, "
+ + "ClarificationRemarks, ClientName FROM legalnotices INNER JOIN clients ON"
+ + " ClientId=clients.id WHERE ClarificationPending=1 AND ClientId=?;",
+
+ "SELECT ReferenceNumber, Description, EntrustmentDate, ClarificationRemarks"
+ + ", ClientName from legalnotices INNER JOIN clients ON ClientId=clients.id "
+ + "WHERE DraftCreated=0 AND ClientId=?;",
+
+ "SELECT ReferenceNumber, Description, EntrustmentDate, ClientName FROM"
+ + " legalnotices INNER JOIN clients ON ClientId=clients.id WHERE "
+ + " DraftApproved=1 AND NoticeSent=0 AND ClientId=?;"
+ } ;
+
+ 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 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> notice = new ArrayList<>();
try {
Connection conn = DriverManager.getConnection(JDBC + connectionURL);
- PreparedStatement st = conn.prepareStatement("SELECT * FROM legalnotices WHERE id=?;");
+ 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 (getValidDate (rs, "BillDate"));
notice.add (rs.getBoolean ("ClarificationPending"));
notice.add (rs.getString("ClarificationRemarks"));
+ notice.add (rs.getString("ClientName"));
}
return notice;
} catch (SQLException ex) {
}
static boolean updateNoticeBillDetails(int selid, String bill_status,
- java.util.Date bill_date, int bill_amount) {
+ java.util.Date bill_date, long 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.setLong(3, bill_amount);
st.setInt(4, selid);
st.execute();
conn.close();
" \"ClarificationPending\" INTEGER DEFAULT 0,\n" +
" \"ClarificationRemarks\" TEXT\n" +
");";
- String tblSettings = "CREATE TABLE IF NOT EXISTS \"settings\" (\n" +
-" \"key\" TEXT UNIQUE,\n" +
-" \"value\" TEXT,\n" +
-" PRIMARY KEY(\"key\")\n" +
- ");";
+
try {
Connection conn = DriverManager.getConnection(JDBC + connectionURL);
Statement st1 = conn.createStatement();
- st1.execute(tblSettings);
st1.execute(tblClients);
st1.execute(tblNotices);
conn.close();
}
}
+
+ 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;
+ }
+
+ }
+
+ static ArrayList<Object> getReportData(int report_num, int clientid) {
+ ArrayList<Object> data = new ArrayList<>();
+ try {
+ Connection conn = DriverManager.getConnection(JDBC + connectionURL);
+
+ // if no client ID is specified i.e. -1 get ll
+ ResultSet rs;
+ if (clientid == -1) {
+ Statement st = conn.createStatement();
+ rs = st.executeQuery(REPORTS_UNFILTERED[report_num]);
+ }
+ else {
+ PreparedStatement st = conn.prepareStatement(REPORTS_FILTERED[report_num]);
+ st.setInt (1, clientid);
+ rs = st.executeQuery();
+ }
+
+ while (rs.next()) {
+ ResultSetMetaData md = rs.getMetaData();
+ SimpleDateFormat fmt = new SimpleDateFormat ("dd MMM yyyy");
+ for (int i = 1; i <= md.getColumnCount(); i ++ ) {
+ // for INTEGER Columns which are date alone, handle separately
+ if (md.getColumnName(i).contains("Date"))
+ data.add (fmt.format(getValidDate(rs, md.getColumnName(i))));
+ else
+ data.add (rs.getObject (i));
+ }
+ }
+ conn.close ();
+ return data;
+ } catch (SQLException ex) {
+ Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
+ return null;
+ }
+
+ }
+
+ static boolean saveReportCSV(String filePath, String[] reportCols, ArrayList<Object> reportData) {
+ try {
+ FileWriter f = new FileWriter (filePath);
+ for (int i = 0; i < reportCols.length; i ++) {
+ f.append (escapeQuoteCSV(reportCols[i]));
+ f.append (",");
+ }
+ f.append("\n");
+ for (int i = 0; i < reportData.size(); i += reportCols.length) {
+ for (int j = 0; j < reportCols.length; j ++) {
+ f.append (escapeQuoteCSV(reportData.get(i+j)));
+ f.append (",");
+ }
+ f.append ("\n");
+ }
+ f.flush();
+ f.close();
+ return true;
+ } catch (IOException ex) {
+ Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
+ return false;
+ }
+
+ }
+
+ private static String escapeQuoteCSV(Object bareStr) {
+ if (bareStr == null)
+ return "";
+ else if (bareStr.getClass() == String.class) {
+ String str = (String)bareStr;
+ String escapedStr = str.replace ("\"", "\"\"");
+ System.out.println(escapedStr);
+ String finalStr = String.format ("\"%s\"", escapedStr);
+ return finalStr;
+ }
+ else
+ return String.format("\"%s\"", bareStr.toString());
+ }
+
+ static boolean saveReportHTML(String filepath, String report_title,
+ String[] report_cols, ArrayList<Object> report_data) {
+ FileWriter htmlFile;
+ try {
+ htmlFile = new FileWriter(filepath);
+ htmlFile.append("<!DOCTYPE html>\n<html lang=\"en\">\n<head>\n<title>");
+ htmlFile.append(htmlEscape(report_title));
+ htmlFile.append("</title>\n</head>\n");
+ htmlFile.append ("<body>\n");
+ htmlFile.append("<h1>");
+ htmlFile.append(htmlEscape(report_title));
+ htmlFile.append("</h1>\n");
+ htmlFile.append ("<table>\n");
+ htmlFile.append ("\t<tr>\n");
+ for (int i = 0; i < report_cols.length; i ++) {
+ htmlFile.append ("\t\t<th>");
+ htmlFile.append (htmlEscape(report_cols[i]));
+ htmlFile.append("</th>\n");
+ }
+ htmlFile.append ("\t</tr>\n");
+ for (int r = 0; r < report_data.size(); r += report_cols.length) {
+ htmlFile.append("\t<tr>\n");
+ for (int c = 0; c < report_cols.length; c ++) {
+ htmlFile.append("\t\t<td>");
+ htmlFile.append(htmlEscape(report_data.get(r+c)));
+ htmlFile.append("</td>\n");
+ }
+ htmlFile.append ("\t</tr>\n");
+ }
+ htmlFile.append("</table>\n");
+ htmlFile.append("</body>\n");
+ htmlFile.append("</html>");
+ htmlFile.flush();
+ htmlFile.close();
+ return true;
+ } catch (IOException ex) {
+ Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
+ return false;
+ }
+ }
+
+ private static String htmlEscape(Object barestr) {
+ if (barestr == null)
+ return "";
+ else if (barestr.getClass() == String.class) {
+ String barestrs = (String)barestr;
+ String str = barestrs.replace("&", "&").replace("<", "<").
+ replace(">", ">").replace("\"", """).replace("\n", "<br>");
+ return str;
+ }
+ else
+ return barestr.toString();
+ }
+
+
public Utility () {
}
public static String connectionURL ;
+ public static String leftLetterHeader;
+ public static String rightLetterHeader;
+ public static String signatoryName;
/**
*
}
public static void retrieveConnectionURL () {
- connectionURL = Preferences.userRoot().get("ConnectionURL", "legaldb");
+ Preferences myPrefs = Preferences.userRoot().node("org/harishankar/Habeas");
+ connectionURL = myPrefs.get("ConnectionURL", "legaldb");
}
}
projects / habeas.git / blobdiff