*/
package habeas;
-import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.IOException;
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 {
+
+ 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) {
}
- static ArrayList<Object> getPendingPaymentBills() {
+ static ArrayList<Object> getReportData(int report_num, int clientid) {
ArrayList<Object> data = new ArrayList<>();
try {
Connection conn = DriverManager.getConnection(JDBC + connectionURL);
- Statement st = conn.createStatement();
- ResultSet rs = st.executeQuery("SELECT ReferenceNumber, Description, "
- + "BillDate, BillAmount, ClientName"
- + " FROM legalnotices INNER JOIN clients ON ClientId=clients.id "
- + "WHERE BillStatus='AWAITING PAYMENT';");
-
- DateFormat fmt = new SimpleDateFormat ("dd/MM/yyyy");
+
+ // 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()) {
- 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"));
+ 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;
FileWriter f = new FileWriter (filePath);
for (int i = 0; i < reportCols.length; i ++) {
f.append (escapeQuote(reportCols[i]));
- f.append (", ");
+ 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 (",");
}
f.append ("\n");
}
else
return String.format("\"%s\"", bareStr.toString());
}
+
+
public Utility () {
}