X-Git-Url: https://harishankar.org/repos/?p=habeas.git;a=blobdiff_plain;f=src%2Fhabeas%2FUtility.java;h=89d06cf8eb3824c5db935ce6fe9da2aa03e6faed;hp=6ca96e5d6c63cc92fa14d61ac546dcebd259cf37;hb=06a2c4c692e08fdc860e489c86f34b2fe64696d1;hpb=43efa7c67c7da936d3395345983dde4603253896 diff --git a/src/habeas/Utility.java b/src/habeas/Utility.java index 6ca96e5..89d06cf 100644 --- a/src/habeas/Utility.java +++ b/src/habeas/Utility.java @@ -5,21 +5,50 @@ */ package habeas; +import java.io.FileNotFoundException; +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.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 saveConnectionURL(String text) { connectionURL = text; - Preferences.userRoot().put("ConnectionURL", text); + Preferences myPrefs = Preferences.userRoot().node ("org/harishankar/Habeas"); + myPrefs.put("ConnectionURL", text); } static ArrayList getClientDetails(int r) { @@ -175,7 +204,9 @@ public class Utility { ArrayList 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()) { @@ -195,6 +226,7 @@ public class Utility { 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) { @@ -322,15 +354,10 @@ public class Utility { " \"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(); @@ -341,10 +368,223 @@ public class Utility { } } + + 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 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 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 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", ""); + String right = rightLetterHeader.replaceAll("\n", ""); + String client_address = clientAddress.replaceAll ("\n", ""); + 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 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 getPendingPaymentBills(int clientid) { + ArrayList 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("SELECT ReferenceNumber, Description, " + + "BillDate, BillAmount, ClientName" + + " FROM legalnotices INNER JOIN clients ON ClientId=clients.id " + + "WHERE BillStatus='AWAITING PAYMENT';"); + } + else { + PreparedStatement st = conn.prepareStatement("SELECT ReferenceNumber, " + + "Description, BillDate, BillAmount, ClientName" + + " FROM legalnotices INNER JOIN clients ON ClientId=clients.id " + + "WHERE BillStatus='AWAITING PAYMENT' AND ClientId=?;"); + st.setInt (1, clientid); + rs = st.executeQuery(); + } + DateFormat fmt = new SimpleDateFormat ("dd/MM/yyyy"); + while (rs.next()) { + data.add (rs.getString("ReferenceNumber")); + data.add (rs.getString("Description")); + data.add (fmt.format (getValidDate(rs, "BillDate"))); + data.add (rs.getInt ("BillAmount")); + data.add (rs.getString("ClientName")); + } + 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 reportData) { + try { + FileWriter f = new FileWriter (filePath); + for (int i = 0; i < reportCols.length; i ++) { + f.append (escapeQuote(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 (escapeQuote(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 escapeQuote(Object bareStr) { + 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()); + } public Utility () { } public static String connectionURL ; + public static String leftLetterHeader; + public static String rightLetterHeader; + public static String signatoryName; /** * @@ -370,7 +610,8 @@ public class Utility { } public static void retrieveConnectionURL () { - connectionURL = Preferences.userRoot().get("ConnectionURL", "legaldb"); + Preferences myPrefs = Preferences.userRoot().node("org/harishankar/Habeas"); + connectionURL = myPrefs.get("ConnectionURL", "legaldb"); } }