import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;

import org.w3c.dom.Attr;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.w3c.dom.Text;
import org.xml.sax.SAXException;

import com.sun.org.apache.xml.internal.serialize.OutputFormat;
import com.sun.org.apache.xml.internal.serialize.XMLSerializer;

/**
 * 
 * Acord "Policy Product Inquiry" example written with JDBC/DOM
 *
 */
public class Acord201JDBC {

  // Change base directory to reference the folder where you have downloaded the XQuery files
  public static final String queryBaseDir  = "C:/acord-xquery/";

  // Change server/account information to be consistent with your database settings
  public static final String connectionUrl = "jdbc:datadirect:sqlserver://localhost:1433;databaseName=pubs;user=user;password=pass";
  
  /** The TXLife XML request */ 
  public static final String inputDoc      = "201-1.xml";

  // result codes
  public static final String RESULT_SUCCESS = "1";
  public static final String RESULT_FAILURE = "5";
  
  
  /**
   * 
   */
  public static void main(String[] args) throws Exception {
    // register JDBC driver
    Class.forName("com.ddtek.jdbc.sqlserver.SQLServerDriver");

    new Acord201JDBC().run();
  }

  public void run() throws IOException, ParserConfigurationException, SAXException {
    // read request document
    String uri = new File(Acord201JDBC.queryBaseDir + Acord201JDBC.inputDoc).toURI().toString();
    Document request = Acord201JDBC.readDocument(uri);

    // create response document
    DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
    DocumentBuilder builder = factory.newDocumentBuilder();
    Document response = builder.newDocument();

    // create tx:TXLife and tx:TXLifeResponse nodes in response document
    Element root = response.createElementNS("http://ACORD.org/Standards/Life/2", "tx:TXLife");
    response.appendChild(root);
    Attr attr = response.createAttributeNS("http://www.w3.org/2000/xmlns/", "xmlns:tx");
    attr.setNodeValue("http://ACORD.org/Standards/Life/2");
    root.setAttributeNodeNS(attr);
    Element txLifeResponse = response.createElement("tx:TXLifeResponse");
    root.appendChild(txLifeResponse);

    // get tx:TXLifeRequest
    Element txLifeRequest = getElementByTagName(request.getDocumentElement(), "tx:TXLifeRequest");
    if ( txLifeRequest != null ) {
      
      // and import the TransRefGUID and TransType from the request
      Node transRefGuid = getElementByTagName(txLifeRequest, "tx:TransRefGUID");
      if ( transRefGuid != null ) {
        txLifeResponse.appendChild(response.importNode(transRefGuid, true));
      }
      Node transType = getElementByTagName(txLifeRequest, "tx:TransType");
      if ( transType != null ) { 
        txLifeResponse.appendChild(response.importNode(transType, true));
      }
  
      // set TransExeDate and TransExeTime
      java.util.Date now = new java.util.Date();
      Node node = txLifeResponse.appendChild(response.createElement("tx:TransExeDate"));
      DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
      addChildText(response, node, df.format(now));
      node = txLifeResponse.appendChild(response.createElement("tx:TransExeTime"));
      df = new SimpleDateFormat("HH:mm:ss");
      addChildText(response, node, df.format(now));
  
      // create ResultCode node - will be filled after execution
      Element transResult = addChildElement(response, txLifeResponse, "tx:TransResult");
      Element resultCode = addChildElement(response, transResult, "tx:ResultCode");
  
      // create OLifE node
      Element olife = addChildElement(response, txLifeResponse, "tx:OLifE");
      olife.setAttribute("Version", "2.12.00");
  
      Connection conn = null;
      try {
        //connect
        conn = DriverManager.getConnection(connectionUrl);
  
        // process transaction
        process201(conn, request, response, olife);
  
        // Succeeded - set result code
        resultCode.setAttribute("tc", Acord201JDBC.RESULT_SUCCESS);
        addChildText(response, resultCode, "Success");
  
      }
      catch ( SQLException ex) {
        // Failed - set result code
        resultCode.setAttribute("tc", Acord201JDBC.RESULT_FAILURE);
        addChildText(response, resultCode, "Failure");
        
        Element child = addChildElement(response, transResult, "tx:ResultInfo");
        child = addChildElement(response, child, "tx:ResultInfoDesc");
        
        addChildText(response, child, ex.toString());
        
      }
      finally {
        if (conn != null) {
          try {
            conn.close();
          }
          catch ( SQLException ex ) {
          }
        }
      }
    }
    
    Acord201JDBC.dumpDocument(response);
  }

  /**
   * This function is functionally equivalent with next expression in XQuery:<BR>
   * 
   * <pre>

    for $policyProduct at $pos in $request/tx:OLifE/tx:PolicyProduct
    let $carrierCode := xs:string($policyProduct/tx:CarrierCode)
    let $productCode := xs:string($policyProduct/tx:ProductCode)
    for $policy as element() in collection("ACORD_POLICY")/ACORD_POLICY[CarrierCode eq $carrierCode][ProductCode eq $productCode]
    return <tx:PolicyProduct> {
                attribute id {fn:concat("PolicyProduct_", xs:string($pos))},
                $carrierCode,
                <tx:PlanName>{xs:string($policy/PlanName)}</tx:PlanName>,
                $productCode,
                <tx:MarketingName>{xs:string($policy/MarketingName)}</tx:MarketingName>
                }
               </tx:PolicyProduct>
             
   * </pre><BR>
   * 
   * 
   */
  public void process201( Connection conn, 
                          Document request, 
                          Document response, 
                          Element olifeResponse) throws SQLException {
    // get tx:TXLifeRequest
    Element txLifeRequest = getElementByTagName(request.getDocumentElement(), "tx:TXLifeRequest");

    // get OLifE object
    Element olifeRequest = getElementByTagName(txLifeRequest, "tx:OLifE");

    if ( olifeRequest != null ) {
      // get policyProduct list
      NodeList policyProductList = olifeRequest.getElementsByTagName("tx:PolicyProduct");
  
      // prepare select statement
      PreparedStatement stmt = conn.prepareStatement(
          "SELECT PlanName, MarketingName FROM ACORD_POLICY WHERE CarrierCode = ? AND ProductCode = ? ORDER BY CarrierCode, ProductCode, PollNumber");
  
      try {
        // iterate over policy inquiry and select form ACORD_POLICY
        for (int i = 0; i < policyProductList.getLength(); i++) {
          Element policyProduct = (Element) policyProductList.item(i);
    
          // get tx:CarrierCode
          String carrierCode = null;
          Element child = getElementByTagName(policyProduct, "tx:CarrierCode");
          if ( child != null && child.getFirstChild() instanceof Text ) {
            carrierCode = child.getFirstChild().getNodeValue();
          }
    
          // get tx:ProductCode
          String productCode = null;
          child = getElementByTagName(policyProduct, "tx:ProductCode");
          if ( child != null && child.getFirstChild() instanceof Text ) {
            productCode = child.getFirstChild().getNodeValue();
          }
    
          
          stmt.setString(1, carrierCode);
          stmt.setString(2, productCode);
    
          if (stmt.execute()) {
            ResultSet rs = stmt.getResultSet();
            
            if (rs.next()) {
              String planName = rs.getString(1);
              String marketingName = rs.getString(2);
    
              Element result = addChildElement(response, olifeResponse, "tx:PolicyProduct");
              Attr attr = response.createAttribute("id");
              attr.setNodeValue("PolicyProduct_" + Integer.toString(i + 1));
              result.setAttributeNodeNS(attr);
              
              child = addChildElement(response, result, "tx:CarrierCode");
              addChildText(response, child, carrierCode);
              child = addChildElement(response, result, "tx:PlanName");
              addChildText(response, child, planName);
              child = addChildElement(response, result, "tx:ProductCode");
              addChildText(response, child, productCode);
              child = addChildElement(response, result, "tx:MarketingName");
              addChildText(response, child, marketingName);
  
              // should only return one object
              assert !rs.next();
            }
            
            rs.close();
          }
        }
      }
      finally {
        stmt.close();
      }
    }
  }

  /** DOM utility to find a single child element by tag name */
  public Element getElementByTagName( Element node, 
                                      String name) {
    NodeList list = node.getElementsByTagName(name);
    if (list.getLength() == 1) {
      return (Element) list.item(0);
    }

    return null;
  }

  /** DOM utility to create a text node and append it as child node of a given parent. */
  public Text addChildText( Document doc, 
                            Node parent, 
                            String content) {
    Text text = doc.createTextNode(content);
    parent.appendChild(text);

    return text;
  }

  /** DOM utility to create a element node and append it as child node of a given parent. */
  public Element addChildElement( Document doc, 
                                  Node parent, 
                                  String name) {
    Element element = doc.createElement(name);
    parent.appendChild(element);

    return element;
  }

  /**
   * Utility to read an XML document as DOM tree. 
   */
  public static Document readDocument(String uri) throws SAXException, ParserConfigurationException, IOException {
    DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
    DocumentBuilder builder = factory.newDocumentBuilder();
    return builder.parse(uri);
  }

  /**
   * Utility to dump a DOM Document to System.out .
   */
  public static void dumpDocument(Document document) throws IOException {
    OutputFormat format = new OutputFormat(document);
    format.setIndent(2);
    XMLSerializer output = new XMLSerializer(System.out, format);
    output.serialize(document);
  }
}