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 * FROM legalnotices WHERE 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"));
+ }
+ 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;
+ }
+
}
public Utility () {
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()) {