import java.io.FileNotFoundException;
import java.io.FileOutputStream;
+import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
}
}
+
+ static ArrayList<Object> getPendingPaymentBills(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("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<Object> 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 () {
}