/**
 * Example: Soap service providing Settlement Quotes
 *
 * @author Steve Taplin <stevet@chp.co.uk>
 */


import java.io.*;
import java.text.*;
import java.sql.*;
import org.jdom.Element;
import org.jdom.Document;
import org.jdom.output.XMLOutputter;

public class SettlementQuote {

	String url;
	Connection con;
	Statement stmt;

	public SettlementQuote() throws Exception {

		try {
			String url = "jdbc:odbc:PSRemote";

			try {
				Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			} catch (java.lang.ClassNotFoundException exc) {
				System.err.print("ClassNotFoundException: ");
				System.err.println(exc.getMessage());
			}

			con = DriverManager.getConnection(url, "stevet", "nov2001");
			stmt = con.createStatement();
		} catch (SQLException ex) {
			System.err.println("SQLException: " + ex.getMessage());
		}
	}

	public String getCustomerAgreements(String customerNumber) throws Exception {

		String query =
			"SELECT AAGRNUM FROM POSDTALIB.AAGRMNT "
				+ "WHERE AINVCUS = '"
				+ customerNumber
				+ "'";
		ResultSet rs = null;
		try {
			rs = stmt.executeQuery(query);
		} catch (SQLException ex) {
			System.err.println("SQLException: " + ex.getMessage());
		}

		return writeXMLAgreementsList(rs, customerNumber);
	}

	public String getQuote(String agreementNumber) throws Exception {

		String query =
			"SELECT APRRNTT, ARNTDUE, ARCTTOT, ARNVTDU "
				+ "FROM POSDTALIB.ASCHEDL "
				+ "WHERE AAGRNUM = '"
				+ agreementNumber
				+ "' AND ATRMNUM = 0";
		ResultSet rs = null;
		try {
			rs = stmt.executeQuery(query);
		} catch (SQLException ex) {
			System.err.println("SQLException: " + ex.getMessage());
		}

		return writeQuote(rs);
	}

	public String getCustomerQuote(String customerNumber) throws Exception {

		String query =
			"SELECT SUM(APRRNTT), SUM(ARNTDUE), SUM(ARCTTOT), SUM(ARNVTDU) "
				+ "FROM POSDTALIB.ASCHEDL A, POSDTALIB.AAGRMNT B "
				+ "WHERE ATRMNUM = 0 AND A.AAGRNUM = B.AAGRNUM "
				+ "AND B.AINVCUS = '"
				+ customerNumber
				+ "'";
		ResultSet rs = null;
		try {
			rs = stmt.executeQuery(query);
		} catch (SQLException ex) {
			System.err.println("SQLException: " + ex.getMessage());
		}

		return writeQuote(rs);
	}

	private String writeQuote(ResultSet rs) throws Exception {

		StringBuffer out = new StringBuffer("ALFA Quote:\n");

		if (rs.next()) {
			float primeRents = rs.getFloat(1);
			float rentsDue = rs.getFloat(2);
			float balance = primeRents - rentsDue;

			float rentsVat = rs.getFloat(4);
			float totalReceipts = rs.getFloat(3);
			float arrears = rentsDue + rentsVat - totalReceipts;

			out.append("Your current Balance is:\n");
			out.append("GBP " + balance + "\n");
			out.append("Your current Arrears are:\n");
			out.append("GBP " + arrears + "\n");
		} else {
			out.append("Incorrect Agreement Number!");
		}

		return out.toString();

	}

	private String writeXMLAgreementsList(ResultSet rs, String customerNumber)
		throws Exception {

		Element root = new Element("CUSTOMER");

		while (rs.next()) {
			Element agreement = new Element("Agreement");
			agreement.setText(rs.getString("AAGRNUM"));
			root.addContent(agreement);
		}


		String query =
			"SELECT C3PYNAM FROM POSDTALIB.C3PYFIL "
				+ "WHERE C3PYNUM = '"
				+ customerNumber
				+ "'";
		ResultSet rs2 = null;
		try {
			rs2 = stmt.executeQuery(query);
			rs2.next();
		} catch (SQLException ex) {
			System.err.println("SQLException: " + ex.getMessage());
		}
		root.setAttribute("Name", rs2.getString("C3PYNAM").trim());

		Document resultDoc = new Document(root);
		return docToString(resultDoc);
	}

	private String docToString(Document doc) {

		StringWriter stringOut = new StringWriter();
		XMLOutputter outputter = new XMLOutputter(" ", true);
		outputter.setEncoding("ISO-8859-1");
		outputter.setOmitDeclaration(true);
	    try {
 			outputter.output(doc, stringOut);       
		} catch (Exception e) {
			e.printStackTrace();
		}
		return stringOut.toString();
	}
}