/*
   MassGISZoningHarvester
   Copyright (C) 2006 Open Geospatial Consortium Inc.

   This program is free software; you can redistribute it and/or
   modify it under the terms of the GNU Lesser General Public
   License as published by the Free Software Foundation; either
   version 2.1 of the License, or (at your option) any later version.

   This library is distributed in the hope that it will be useful,
   but WITHOUT ANY WARRANTY; without even the implied warranty of
   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
   Lesser General Public License for more details.

   You should have received a copy of the GNU Lesser General Public
   License along with this library; if not, write to the Free Software
   Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
*/

/*
 * Author: Raj Singh <rsingh@opengeospatial.org>
 * Online version: http://www.ogcnetwork.net/system/files?file=MassGISZoningHarvester_java.txt
 * Last modified July 19, 2006
 * Tested using JSE 1.5. JSE 1.4 should work with the JAXP 1.3 library.
 */
package org.ogc.wfs.client;

import java.awt.geom.Point2D;
import java.net.MalformedURLException;
import java.net.URL;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

import org.postgresql.PGConnection;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.NodeList;

/**
 * Simple example of using a Web Feature Service to keep a data 
 * set synchronized. This application uses a data set of Massachusetts zoning 
 * and harvests a rectangular area of interest. 
 * It first deletes all zoning features currently in its database, then copies the 
 * new data in from the WFS. A real application, of course, would do 
 * more sophisticated things, like moving the old data temporarily until 
 * the update has successfully finished, or even doing a more selective 
 * update of only items that have changed.
 * @TODO write the shape to the database
 */
public class MassGISZoningHarvester {
	Connection dbConn = null; // PostGIS database connection
	private static String wfsURLString = 
		"http://64.119.128.70/geoserver/wfs?";
	private static String featureTypeName = "massgis:GISDATA.ZONING_POLY";
	
	
	private boolean writeDB(Document xmldoc) {
		boolean everythingsOK = true;
		
		//this loop handles each feature (aka record/item/object/shape)
		NodeList featuremembers = xmldoc.getDocumentElement().getElementsByTagName("gml:featureMember");
		System.out.println("# feature members: " + featuremembers.getLength());
		for (int i = 0; i < featuremembers.getLength(); i++) {
			Element featuremember = (Element)featuremembers.item(i);
			Element zoningpoly = (Element)featuremember.getElementsByTagName("massgis:GISDATA.ZONING_POLY").item(0);
			
			String sql = "(town_id, zonecode, prim_use, prim_use2, gen_use, lastedited, the_geom";
			sql += ") VALUES (";

			Element e = (Element)zoningpoly.getElementsByTagName("massgis:TOWN_ID").item(0);
			sql += e.getTextContent() + ", ";

			e = (Element)zoningpoly.getElementsByTagName("massgis:ZONECODE").item(0);
			sql += "'" + e.getTextContent() + "', ";

			e = (Element)zoningpoly.getElementsByTagName("massgis:PRIM_USE").item(0);
			sql += "'" + e.getTextContent() + "', ";

			e = (Element)zoningpoly.getElementsByTagName("massgis:PRIM_USE2").item(0);
			sql += "'" + e.getTextContent() + "', ";

			e = (Element)zoningpoly.getElementsByTagName("massgis:GEN_USE").item(0);
			sql += e.getTextContent() + ", ";

			e = (Element)zoningpoly.getElementsByTagName("massgis:LASTEDITED").item(0);
			sql += e.getTextContent() + ", ";
			
			// handle the multipolygon shape (as a polygon)
			// get the CRS
			Element shapeel = (Element)zoningpoly.getElementsByTagName("massgis:SHAPE").item(0);
			e = (Element)shapeel.getElementsByTagName("gml:MultiPolygon").item(0);
			String crs = "";
			if ( e.hasAttribute("srsName") ) {
				String att = e.getAttribute("srsName");
				String crsprefix = "http://www.opengis.net/gml/srs/epsg.xml#";
				crs = att.substring( crsprefix.length() );
				if ( crs==null ) {
					System.err.println("Couldn't identify the CRS. Got null value. Setting to -1");
					crs = "-1";
					everythingsOK = false;
				}
				try {
					Integer.parseInt(crs);
				} catch (NumberFormatException nfe) {
					System.err.println("Couldn't identify the CRS. " + crs + " isn't a number. Setting to -1");
					crs = "-1";
					everythingsOK = false;
				}
			}
			
			// get the coordinates
			e = (Element)shapeel.getElementsByTagName("gml:coordinates").item(0);
			String coords = e.getTextContent();
			coords = coords.replace(',', '|');
			coords = coords.replace(' ', ',');
			coords = coords.replace('|', ' ');
			sql += "GeomFromText('POLYGON((" + coords + "))'," + crs + ")";
			
			sql += ")";

			try {
				Statement stInsert = dbConn.createStatement();
				stInsert.executeUpdate("INSERT INTO zoning " + sql);
			} catch (SQLException sqle) {
				sqle.printStackTrace();
				everythingsOK = false;
			}
		}
		
		return everythingsOK;
	}
	
	public boolean harvestEnvelope(Point2D lowerLeft, Point2D upperRight) {
		BasicWFSReader bwr = null;
		try {
			bwr = new BasicWFSReader(new URL(wfsURLString));
		} catch (MalformedURLException e) {
			e.printStackTrace();
			return false;
		}

		// create query
		String q = "<wfs:Query typeName=\"" + featureTypeName + "\">";
		q += "<ogc:PropertyName>massgis:TOWN_ID</ogc:PropertyName>";
		q += "<ogc:PropertyName>massgis:ZONECODE</ogc:PropertyName>";
		q += "<ogc:PropertyName>massgis:PRIM_USE</ogc:PropertyName>";
		q += "<ogc:PropertyName>massgis:PRIM_USE2</ogc:PropertyName>";
		q += "<ogc:PropertyName>massgis:GEN_USE</ogc:PropertyName>";
		q += "<ogc:PropertyName>massgis:LASTEDITED</ogc:PropertyName>";
		q += "<ogc:PropertyName>massgis:SHAPE</ogc:PropertyName>";
		
		if ( lowerLeft != null && upperRight != null ) {
			q += "<ogc:Filter><ogc:Within>";
			q += "<ogc:PropertyName>massgis:SHAPE</ogc:PropertyName>";
			q += "<gml:Box xmlns:gml=\"http://www.opengis.net/gml\"><gml:coordinates>";
			q += Double.toString(lowerLeft.getX()) + "," + Double.toString(lowerLeft.getY()) + " ";
			q += Double.toString(upperRight.getX()) + "," + Double.toString(upperRight.getY());
			q += "</gml:coordinates></gml:Box>";
			q += "</ogc:Within></ogc:Filter>";
		}
		
		q += "</wfs:Query>";
		
//		String f = "/Users/rajsingh/ogc/events/200607_geoweb/code/wfsservices/massgis/zoning_features_max3.xml";
//		boolean ok = writeDB ( bwr.getFeatureFromFile(f) );
		boolean ok = writeDB( bwr.getFeature(q, 0) );
		return ok;
	}
	
	public boolean emptyTable() {
		try {
			Statement dbSQLStatement = dbConn.createStatement();
			dbSQLStatement.execute("DELETE FROM zoning");
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}
	
	public MassGISZoningHarvester() {
		//// set up database connection
		try {
			Class.forName("org.postgresql.Driver");
			dbConn = DriverManager.getConnection("jdbc:postgresql:geoweb", "geowebuser", "whistler");
			((PGConnection)dbConn).addDataType("geometry","org.postgis.PGgeometry");
			((PGConnection)dbConn).addDataType("box3d","org.postgis.PGbox3d");
		} catch (ClassNotFoundException cnfe) {
		} catch (SQLException sqe) {
			sqe.printStackTrace();
			return;
		}
		if (dbConn == null) 
			System.err.println("Unable to establish database connection!");
	}

	public static void main(String[] args) {
		MassGISZoningHarvester zh = new MassGISZoningHarvester();
		zh.emptyTable();
		Point2D.Double ll = new Point2D.Double(246000.00,820000.00);
		Point2D.Double ur = new Point2D.Double(247000.00,821000.00);
		zh.harvestEnvelope(ll, ur);
	}

}