package osm.jp.coverage.busstop;
import osm.jp.api.HttpPOST;

import javax.xml.parsers.*;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerException;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;

import org.w3c.dom.*;
import org.xml.sax.*;

import java.io.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;

import jp.co.areaweb.tools.database.*;

public class ConvBusstop {

	String filter = "";
	String urlStr = "";

	public static final boolean DB_INIT = false;

	// 近くのバス停を探す範囲（バス停を中心としたNEER×２ｍ四方の領域
	static final int NEER = 150;		// 150m(0.15km)
	static boolean nocheck = false;
	
	public static SimpleDateFormat timeStampFmt = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss'Z'");

	/**
	 * メイン
	 *
	 *	java -cp .:ConvBusstop.jar:hayashi_0225.jar:hsqldb_2.2.9.jar osm.jp.ConvBusstop <option>
	 *		OPTION: -nocheck	OSMデータ上に既存のバス停が存在するかどうかをチェックしない
	 *		OPTION: -check	OSMデータ上に既存のバス停が存在するかどうかをチェックする
	 *
	 * @throws IOException
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 * @throws FileNotFoundException
	 * @throws TransformerException
	 * @throws SAXException
	 * @throws ParserConfigurationException */
	public static void main(String[] args) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException, TransformerException
	{
		int index = 0;
		if (args.length > index) {
			if (args[index].equals("-check")) {
				ConvBusstop.nocheck = false;
				index++;
			}
			else if (args[index].equals("-nocheck")) {
				ConvBusstop.nocheck = true;
				index++;
			}
		}

		/**
		 * アプリケーション [ConvBusstop]
		 * > java -jar ConvBusstop.jar <オプション>
		 * 	オプション： -exp	実行する直前にデータベースを初期化する（省略可能）
		 */
		File dbdir = new File("database");
		if (!dbdir.isDirectory()) {
			dbdir.mkdir();
		}

		Connection con = DatabaseTool.openDb("database");
		ConvBusstop.initDb(con);
		
		try {
			/**
			 * 都道府県ごとのGMLディレクトリの処理
			 */
			
			int fcounter = 0;
			File dir = new File(".");
			File[] files = dir.listFiles();
			for (File iDir : files) {
				if (checkGMLdir(iDir)) {
					// GMLディレクトリを処理する
					new ConvBusstop(con, iDir);
					fcounter++;
				}
			}
			System.out.println("["+ fcounter +"]つのファイルをインポートしました。");
		}
		finally {
			DatabaseTool.closeDb(con);
		}
	}
	
	public ConvBusstop(Connection con, File gmldir) throws SQLException, FileNotFoundException, ClassNotFoundException, IOException, ParserConfigurationException, SAXException, TransformerException {
		int areacode = Integer.parseInt(gmldir.getName().substring(GML_DIR_PREFIX.length(), GML_DIR_PREFIX.length()+2));

		ToPostgis postgis = null;
		
		File[] files = gmldir.listFiles();
		for (File iFile : files) {
			if (checkFile(iFile, areacode)) {
				ConvBusstop.clearDb(con);
				inputFile(con, iFile, areacode);

				/**
				 * 既存のOSMバス停を読み込む
				 * 		--> 'existing.xml'
				 */
				if (!ConvBusstop.nocheck) {
					/*
					 * 既存のOSMデータファイルがなければ、新たにダウンロードする。
					 * OSMデータファイルがあるときは、ダウンロードしないでそれを使う。
					 */
					File existingFile = new File(gmldir, String.format("existing_%02d.xml", areacode));
					if (!existingFile.isFile()) {
						/**
						 * インポートしたデータの緯度経度範囲を読み取る
						 */
						double maxLat = -90.0D;
						double minLat = 90.0D;
						double maxLon = -180.0D;
						double minLon = 180.0D;
						PreparedStatement ps8 = con.prepareStatement("SELECT lat,lon FROM bus_stop");
						ResultSet rset8 = ps8.executeQuery();
						while (rset8.next()) {
							Double lat = rset8.getDouble("lat");
							Double lon = rset8.getDouble("lon");

							if (lat > maxLat) {
								maxLat = lat;
							}
							if (lon > maxLon) {
								maxLon = lon;
							}
							if (lat < minLat) {
								minLat = lat;
							}
							if (lon < minLon) {
								minLon = lon;
							}
						}
						rset8.close();
						
						/**
						 * OSM OverPassAPI を使って、既存のOSMバス停のデータを取得して、「existing.xml」に出力する
						 */
						HttpPOST.getCapabilities(existingFile, minLat, maxLat, minLon, maxLon);
					}
					readExistingFile(con, existingFile);
					
					PreparedStatement ps1 = con.prepareStatement("SELECT idref,name,lat,lon FROM bus_stop WHERE area=?");
					PreparedStatement ps2 = con.prepareStatement("SELECT count(idref) FROM existing_data where (lat > ?) and (lat < ?) and (lon > ?) and (lon < ?)");
					PreparedStatement ps3 = con.prepareStatement("UPDATE bus_stop SET fixed=? WHERE idref=?");
					PreparedStatement ps4 = con.prepareStatement("SELECT count(idref) FROM existing_data where (lat > ?) and (lat < ?) and (lon > ?) and (lon < ?) and (name = ?)");
					ps1.setInt(1, areacode);
					ResultSet rset1 = ps1.executeQuery();
					while (rset1.next()) {
						String idref = rset1.getString("idref");
						String name = rset1.getString("name");
						Double lat = rset1.getDouble("lat");
						Double lon = rset1.getDouble("lon");
						
						// 指定の緯度経度を中心とする半径150x2m四方の矩形領域
						System.out.print(idref + "("+ name + ") ....");
						RectArea rect = new RectArea(lat, lon, NEER);		// 300m 四方
						ps2.setDouble(1, rect.minlat);
						ps2.setDouble(2, rect.maxlat);
						ps2.setDouble(3, rect.minlon);
						ps2.setDouble(4, rect.maxlon);
						ResultSet rset2 = ps2.executeQuery();
						if (rset2.next()) {
							int count = rset2.getInt(1);
							if (count > 0) {
								System.out.println("."+ count);
								ps3.setInt(1, count);
								ps3.setString(2, idref);
								ps3.executeUpdate();
							}
							else {
								// 指定の緯度経度を中心とする半径150x4m四方の矩形領域
								System.out.print("***");
								rect = new RectArea(lat, lon, NEER*2);		// 600m 四方
								ps4.setDouble(1, rect.minlat);
								ps4.setDouble(2, rect.maxlat);
								ps4.setDouble(3, rect.minlon);
								ps4.setDouble(4, rect.maxlon);
								ps4.setString(5, name);
								ResultSet rset4 = ps4.executeQuery();
								if (rset4.next()) {
									count = rset4.getInt(1);
									System.out.println(".."+ count);
									ps3.setInt(1, count);
									ps3.setString(2, idref);
									ps3.executeUpdate();
								}
								rset4.close();
							}
						}
						rset2.close();
					}
					rset1.close();
					ps3.close();
					ps2.close();
					ps1.close();
				}
				

				// ローカルデータベース内の情報を出力する
				outputDb2html(con, gmldir);
				
				// ローカルデータベース内の情報を「busstop.xml」に出力する
				int counter = outputDb(con, gmldir);
				System.out.println("["+ counter +"] 変換完了しました。");

				// ローカルデータベース内の情報をPostGIS用の「busstop.sql」に出力する
				postgis = new ToPostgis(gmldir);
				postgis.outputDb(con);
				postgis.close();
			}
		}
	}

	static String[] shiftArgs(String[] args) {
		String[] values = new String[args.length - 1];
		for (int i=1; i < args.length; i++) {
			values[i - 1] = new String(args[i]);
		}
		return values;
	}

	public static void readExistingFile (Connection con, File existingFile) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException {
		int iCounter = 0;

		DocumentBuilderFactory factory;
		DocumentBuilder        builder;
		Node root;

		iCounter = 0;
		factory = DocumentBuilderFactory.newInstance();
		builder = factory.newDocumentBuilder();
		factory.setIgnoringElementContentWhitespace(true);
		factory.setIgnoringComments(true);
		factory.setValidating(true);
		root    = builder.parse(existingFile);

		iCounter += readExistingNodes(con, root);
		System.out.println("既存バス停数["+ iCounter +"]");
	}
	
	static int readExistingNodes(Connection con, Node node) throws IOException, SQLException {
		int iCounter = 0;
		
		NodeList nodes = node.getChildNodes();
		for (int i = 0; i < nodes.getLength(); i++) {
			Node node2 = nodes.item(i);
			if (node2.getNodeName().equals("node")) {
				iCounter++;
				importExistingNode(con, node2);
			}
			else {
				iCounter += readExistingNodes(con, node2);
			}
		}
		return iCounter;
	}

	static void importExistingNode(Connection con, Node node) throws IOException, SQLException {
		String idrefStr = "";
		String latStr = "";
		String lonStr = "";
		String nameStr = "";
		PreparedStatement ps5 = con.prepareStatement("INSERT INTO existing_data (idref,lat,lon, name) VALUES (?,?,?,?)");

		NamedNodeMap nodeMap = node.getAttributes();
		if (null != nodeMap) {
			for (int j=0; j < nodeMap.getLength(); j++) {
				if (nodeMap.item(j).getNodeName().equals("id")) {
					idrefStr = nodeMap.item(j).getNodeValue();
				}
				else if (nodeMap.item(j).getNodeName().equals("lat")) {
					latStr = nodeMap.item(j).getNodeValue();
				}
				else if (nodeMap.item(j).getNodeName().equals("lon")) {
					lonStr = nodeMap.item(j).getNodeValue();
				}
			}
			
			NodeList nodes = node.getChildNodes();
			for (int i = 0; i < nodes.getLength(); i++) {
				Node node2 = nodes.item(i);
				if (node2.getNodeName().equals("tag")) {
					NamedNodeMap nodeMap2 = node2.getAttributes();
					if (null != nodeMap2) {
						String key = null;
						String value = null;
						for (int j=0; j < nodeMap2.getLength(); j++) {
							if (nodeMap2.item(j).getNodeName().equals("k")) {
								key = nodeMap2.item(j).getNodeValue();
							}
							else if (nodeMap2.item(j).getNodeName().equals("v")) {
								value = nodeMap2.item(j).getNodeValue();
							}
						}
						
						if ((key != null) && key.toLowerCase().equals("name") && (value != null)) {
							nameStr = value;
							break;
						}
					}
				}
			}

			// idref と nameStr をデータベースに格納する
			System.out.println("import existing_data : "+ idrefStr +" ("+ latStr +","+ lonStr+")"+ nameStr);
			ps5.setString(1, idrefStr);
			ps5.setDouble(2, Double.parseDouble(latStr));
			ps5.setDouble(3, Double.parseDouble(lonStr));
			ps5.setString(4, nameStr);
			ps5.executeUpdate();
			ps5.close();
		}
	}
	
	/**
	 * ソースファイルを読み取ってローカルベータベースへ記録する
	 * @param con
	 * @param iFile
	 * @throws FileNotFoundException
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 * @throws IOException
	 * @throws ParserConfigurationException 
	 * @throws SAXException 
	 */
	public static void inputFile (Connection con, File iFile, int areacode) throws FileNotFoundException, ClassNotFoundException, SQLException, IOException, ParserConfigurationException, SAXException {
		int iCounter = 0;
		String timeStampStr = null;

		String iStr = iFile.getName();

		DocumentBuilderFactory factory;
		DocumentBuilder        builder;
		Node root;

		iCounter = 0;
		factory = DocumentBuilderFactory.newInstance();
		builder = factory.newDocumentBuilder();
		factory.setIgnoringElementContentWhitespace(true);
		factory.setIgnoringComments(true);
		factory.setValidating(true);
		root    = builder.parse(iFile);

		iCounter += showNodes(con, root, iStr.substring(0, iStr.length() - 4), timeStampStr, areacode);
		System.out.println("バス停数["+ iCounter +"]");
	}

	public static void clearDb(Connection con) throws SQLException {
		Statement stmt = con.createStatement();
		long count = stmt.executeUpdate("delete from bus_stop");
		System.out.println("'Database.bus_stop'から "+ count +" 件のデータを削除しました。");
	    
	    count = stmt.executeUpdate("delete from existing_data");
	    System.out.println("'Database.existing_data'から "+ count +" 件のデータを削除しました。");

	    count = stmt.executeUpdate("delete from bus_course");
	    System.out.println("'Database.bus_course'から "+ count +" 件のデータを削除しました。");

	    count = stmt.executeUpdate("delete from bus_ref");
	    System.out.println("'Database.bus_ref'から "+ count +" 件のデータを削除しました。");
	    stmt.close();
	}

	public static void initDb(Connection con) throws SQLException {
		// 'table.BUS_STOP'を新規に作る
		DbBusstop.create(con);
	}


	/**
	 * ローカルデータベース内の情報を出力する
	 * @param con
	 * @param iCode
	 * @throws IOException
	 * @throws SQLException
	 * @throws TransformerException 
	 * @throws ParserConfigurationException 
	 * @throws SAXException 
	 */
	public static void outputDb2html(Connection con, File dir) throws IOException, SQLException, SAXException, ParserConfigurationException, TransformerException {
		String timeStampStr = timeStampFmt.format(new Date(Calendar.getInstance().getTimeInMillis()));
		String iCode = dir.getName();

		BufferedWriter ow = null;
		BufferedWriter gw = null;
		BufferedWriter hw = null;
		BufferedWriter ww = null;

		// HTML header
		File htmlFile = new File(dir, iCode +".html");
		hw = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(htmlFile), "UTF-8"));
		hw.write("<!DOCTYPE html>");
		hw.newLine();
		hw.write("<html><head><meta charset=\"utf-8\" /></head>");
		hw.newLine();
		hw.write("<body><table border='1'>");
		hw.newLine();
		hw.write("<tr>");
		hw.write("<td>type</td>");
		hw.write("<td>corp</td>");
		hw.write("<td>course</td>");
		hw.write("<td>GPX</td>");
		hw.write("<td>SAMPLE</td>");
		hw.write("<td>バス停数</td>");
		hw.write("<td>未入力</td>");
		hw.write("<td>既存</td>");
		hw.write("</tr>");
		hw.newLine();

		// Wiki header
		File wikiFile = new File(dir, iCode+".wiki.txt");
		ww = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(wikiFile), "UTF-8"));
		ww.write("= バス路線毎のマッピング状況 =");
		ww.newLine();
		ww.newLine();
		
		String maeCorp = "";
		boolean firstCorp = true;
		
		PreparedStatement ps7 = con.prepareStatement("SELECT code,type,corp,course,ifile FROM bus_course WHERE ifile=? ORDER BY type,corp,course");
		PreparedStatement ps9 = con.prepareStatement("SELECT idref FROM bus_ref WHERE code=?");
		PreparedStatement ps8 = con.prepareStatement("SELECT name,lat,lon,fixed FROM bus_stop WHERE idref=?");
		ps7.setString(1, dir.getName());
		ResultSet rset7 = ps7.executeQuery();
		while (rset7.next()) {
			int code = rset7.getInt(1);
			int type = rset7.getInt(2);
			String corp = rset7.getString(3);
			String course = rset7.getString(4);
			

			File osmFile = new File(dir, iCode + String.format("_%1$08d", code) +".osm");
			File gpxFile = new File(dir, iCode + String.format("_%1$08d", code) +".gpx");
			File osmSample = new File(dir, iCode + String.format("_s%1$08d", code) +".osm");

			System.out.println("course = "+ course);
			int stopCount = 0;
			int fixedCount = 0;
			int unfixedCount = 0;

			// index file header
			hw.write("<tr>");
			hw.write("<td>"+ type +"</td>");
			hw.write("<td>"+ corp +"</td>");
			hw.write("<td><a href='"+ iCode +"/"+ osmFile.getName() +"'>"+ course +"</a></td>");
			hw.write("<td><a href='"+ iCode +"/"+ gpxFile.getName() +"'>"+ gpxFile.getName() +"</a></td>");
			hw.write("<td><a href='"+ iCode +"/"+ osmSample.getName() +"'>"+ osmSample.getName() +"</a></td>");
			hw.newLine();

			//--------------------------------------------
			//	Wiki見出し２: 運行会社
			//------------
			if (!maeCorp.equals(corp)) {
				if (firstCorp == false) {
					ww.write("|}");
					ww.newLine();
					ww.newLine();
				}
				firstCorp = false;
				
				ww.write("=== "+ corp +" ===");
				ww.newLine();
				ww.newLine();

				ww.write(":{{JA:Tag|network||"+ corp +"}}");
				ww.newLine();
				ww.write(":{{JA:Tag|operator||"+ corp +"}}");
				ww.newLine();
				ww.newLine();

				ww.write("{| class=\"wikitable sortable\" style=\"table-layout: fixed; width: 100%\"");
				ww.newLine();
				ww.write("!style=\"width: 100px\"| ref");
				ww.newLine();
				ww.write("!class=\"unsortable\" style=\"width: 152px\"| 編集状況(マスタ)");
				ww.newLine();
				ww.write("!class=\"unsortable\" style=\"width: 152px\"| 編集状況(往路)");
				ww.newLine();
				ww.write("!class=\"unsortable\" style=\"width: 152px\"| 編集状況(復路)");
				ww.newLine();
				ww.write("!class=\"unsortable\"| 備考");
				ww.newLine();

				maeCorp = new String(corp);
			}
			
			// Wiki
			ww.write("|-");
			ww.newLine();
			ww.write("| "+ course +" ");		// ref
			ww.write("|| {{State Route|r=0}} {{relation|0|tools=no}} ");			// 編集状況 (マスタ)
			ww.write("|| {{State Route|r=0|h=0}} {{relation|0|tools=no}} ");		// 編集状況 (往路)
			ww.write("|| {{State Route|r=0|h=0}} {{relation|0|tools=no}} ");		// 編集状況 (復路)
			ww.write("|| ");		// 備考
			ww.newLine();


			// OSM file header
			ow = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(osmFile), "UTF-8"));
			ow.write("<?xml version=\"1.0\" encoding=\"UTF-8\" ?>");
			ow.newLine();
			ow.write("<osm version=\"0.6\" generator=\"ConvBusstop\">");
			ow.newLine();

			// GPX file header
			gw = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(gpxFile), "UTF-8"));
			gw.write("<?xml version=\"1.0\" encoding=\"UTF-8\" ?>");
			gw.newLine();
			gw.write("<gpx xmlns=\"http://www.topografix.com/GPX/1/1\" version=\"1.1\" creator=\"osmtracker-android\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:schemaLocation=\"http://www.topografix.com/GPX/1/1 http://www.topografix.com/GPX/1/1/gpx.xsd \">");
			gw.newLine();

			double maxLat = -180.0;
			double minLat = 180.0;
			double maxLon = -180.0;
			double minLon = 180.0;

			ps9.setInt(1, code);
			ResultSet rset9 = ps9.executeQuery();
			while (rset9.next()) {
				String idref = rset9.getString(1);

				ps8.setString(1, idref);
				ResultSet rset8 = ps8.executeQuery();
				if (rset8.next()) {
					stopCount++;
					String name = rset8.getString(1);
					Double lat = rset8.getDouble(2);
					Double lon = rset8.getDouble(3);
					int fixed = rset8.getInt(4);

					if (lat > maxLat) {
						maxLat = lat;
					}
					if (lon > maxLon) {
						maxLon = lon;
					}
					if (lat < minLat) {
						minLat = lat;
					}
					if (lon < minLon) {
						minLon = lon;
					}
					
					System.out.println("\tway point = "+ idref +", lat="+ lat +", lon="+ lon +", name="+ name);
					fixedCount += fixed;
					if (fixed == 0) {
						unfixedCount++;

						// OSM node
						String osm_node = nodeBusstop(con, idref, name, lat, lon, timeStampStr);
						ow.write(osm_node);
						ow.newLine();

						// TEXT node
						//File txtFile = new File(dir, iCode + idref +".txt");
						//BufferedWriter gw2 = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(txtFile), "UTF-8"));
						//gw2.write(osm_node);
						//gw2.newLine();
						//gw2.close();

						// GPX waypoint
						gw.write("<wpt lat=\""+ lat +"\" lon=\""+ lon +"\">\n");
						gw.write(" <time>"+ timeStampStr +"</time>\n");
						gw.write(" <name><![CDATA["+ name +"]]></name>\n");
						//gw.write(" <link href=\""+ txtFile.getName() +"\"><text>"+ idref +"</text></link>\n");
						gw.write("</wpt>\n");
						gw.newLine();
					}
				}
				rset8.close();
			}
			rset9.close();

			// INDEX file
			hw.write("<td>"+ stopCount +"</td>");
			hw.write("<td>"+ unfixedCount +"</td>");
			hw.write("<td>"+ fixedCount +"</td>");
			hw.write("</tr>");
			hw.newLine();

			// OSM file header
			BufferedWriter ow2 = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(osmSample), "UTF-8"));
			ow2.write("<?xml version=\"1.0\" encoding=\"UTF-8\" ?>");
			ow2.newLine();
			ow2.write("<osm version=\"0.5\" generator=\"ConvBusstop\">");
			ow2.newLine();

			ow2.write(nodeBusstop(con, "n0", "SAMPLE", ((maxLat+minLat)/2), ((maxLon+minLon)/2), timeStampStr));
			ow2.newLine();
			ow2.write("</osm>");
			ow2.newLine();
			ow2.close();

			// OSM file footer
			ow.write("</osm>");
			ow.newLine();
			ow.close();

			// GPX file footer
			gw.write("</gpx>");
			gw.newLine();
			gw.close();
		}
		rset7.close();

		// Wiki footer
		ww.close();

		// index file footer
		hw.write("</table></body></html>");
		hw.newLine();
		hw.close();
	}

	public static int outputDb(Connection con, File dir) throws IOException, SQLException, SAXException, ParserConfigurationException, TransformerException {
		File osmFile = new File(dir, "busstop.osm");
		int counter = 0;

		String timeStampStr = timeStampFmt.format(new Date(Calendar.getInstance().getTimeInMillis()));
		BufferedWriter ow = null;
		
		// OSM file header
		ow = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(osmFile), "UTF-8"));
		ow.write("<?xml version=\"1.0\" encoding=\"UTF-8\" ?>");
		ow.newLine();
		ow.write("<osm version=\"0.6\" generator=\"ConvBusstop\">");
		ow.newLine();
		
		PreparedStatement ps8 = con.prepareStatement("SELECT idref,name,lat,lon,fixed FROM bus_stop");
		ResultSet rset8 = ps8.executeQuery();
		while (rset8.next()) {
			String idref = rset8.getString("idref");
			String name = rset8.getString("name");
			Double lat = rset8.getDouble("lat");
			Double lon = rset8.getDouble("lon");
			int fixed = rset8.getInt("fixed");
			
			if (ConvBusstop.nocheck || (fixed < 1)) {
				// OSM node
				counter++;
				String osm_node = nodeBusstop(con, idref, name, lat, lon, timeStampStr);
				System.out.println(osm_node);
				ow.write(osm_node);
				ow.newLine();
			}
		}
		rset8.close();

		// OSM file footer
		ow.write("</osm>");
		ow.newLine();
		ow.close();
		return counter;
	}
	
	public static Element osmTemplateNode = null;
	
	public static String nodeBusstop(Connection con, String idref, String name, Double lat, Double lon, String timeStampStr) throws SAXException, IOException, ParserConfigurationException, TransformerException, SQLException {
		int nodeid = Integer.parseInt(idref.substring(1)) * -1;

		PreparedStatement ps9 = con.prepareStatement("SELECT corp,course FROM bus_ref,bus_course WHERE bus_ref.idref=? and bus_ref.code=bus_course.code ORDER BY corp,course");

		DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
		factory.setIgnoringElementContentWhitespace(true);
		factory.setIgnoringComments(true);
		factory.setValidating(true);

		if (osmTemplateNode == null) {
			Document root = factory.newDocumentBuilder().parse(new File("osmTag.xml"));
			osmTemplateNode = root.getDocumentElement();
		}
		
        Document document = factory.newDocumentBuilder().newDocument();          // Documentの生成
        Element node = document.createElement("node");
        document.appendChild(node);
        
        node.setAttribute("id", String.valueOf(nodeid));
        node.setAttribute("timestamp", timeStampStr);
        node.setAttribute("lat", String.valueOf(lat));
        node.setAttribute("lon", String.valueOf(lon));
        
        
		NodeList nodes = osmTemplateNode.getChildNodes();
		for (int i = 0; i < nodes.getLength(); i++) {
			Node node2 = nodes.item(i);
			
			if (node2.getNodeName().equals("tag")) {
				NamedNodeMap nodeMap2 = node2.getAttributes();
				if (null != nodeMap2) {
					String key = null;
					String value = null;
					for (int j=0; j < nodeMap2.getLength(); j++) {
						if (nodeMap2.item(j).getNodeName().equals("k")) {
							key = nodeMap2.item(j).getNodeValue();
						}
						else if (nodeMap2.item(j).getNodeName().equals("v")) {
							value = nodeMap2.item(j).getNodeValue();
						}
					}
					if ((key != null) && (value != null)) {
				        Element tag = document.createElement("tag");
				        tag.setAttribute("k", key);
				        tag.setAttribute("v", value);
				        node.appendChild(tag);
					}
				}
			}
			else if (node2.getNodeName().equals("ksj")) {
				NamedNodeMap nodeMap2 = node2.getAttributes();
				if (null != nodeMap2) {
					String company = null;
					String lineName = null;
					for (int j=0; j < nodeMap2.getLength(); j++) {
						String nodeName = nodeMap2.item(j).getNodeName();
						String nodeValue = nodeMap2.item(j).getNodeValue();
						if (nodeName.equals("busStopName")) {
							Element tagName = document.createElement("tag");
					        tagName.setAttribute("k", nodeValue);
					        tagName.setAttribute("v", name);
					        node.appendChild(tagName);
						}
						else if (nodeName.equals("busOperationCompany")) {
							company = nodeValue;
						}
						else if (nodeName.equals("busLineName")) {
							lineName = nodeValue;
						}
					}
					if ((company != null || lineName != null)) {
						String operationStr = "";
						String lineStr = "";
						String cBefore = "";
						String lBefore = "";
						ps9.setString(1, idref);
						ResultSet rset9 = ps9.executeQuery();
						while (rset9.next()) {
							String cCurrent = rset9.getString("corp");
							String lCurrent = rset9.getString("course");
							if (!cBefore.equals(cCurrent)) {
								operationStr += (operationStr.equals("") ? "" : ";") + cCurrent;
								cBefore = new String(cCurrent);
							}
							if (!lBefore.equals(lCurrent)) {
								lineStr += (lineStr.equals("") ? "" : ";") + lCurrent;
								lBefore = new String(lCurrent);
							}
						}
						if (company != null) {
							Element tagName = document.createElement("tag");
					        tagName.setAttribute("k", company);
					        tagName.setAttribute("v", operationStr);
					        node.appendChild(tagName);
						}
						if (lineName != null) {
							Element tagName = document.createElement("tag");
					        tagName.setAttribute("k", lineName);
					        tagName.setAttribute("v", lineStr);
					        node.appendChild(tagName);
						}
					}
				}
			}
		}

		StringWriter sw = new StringWriter();
		TransformerFactory tfactory = TransformerFactory.newInstance(); 
		Transformer transformer = tfactory.newTransformer(); 
		transformer.setOutputProperty(OutputKeys.OMIT_XML_DECLARATION, "yes");
		transformer.transform(new DOMSource(node), new StreamResult(sw)); 
		return sw.toString();
	}

	/**
	 *
	 * @param con
	 * @param node
	 * @param iFileName		// ソースファイル名（拡張子を含まない）
	 * @param timeStampStr
	 * @return
	 * @throws IOException
	 * @throws SQLException
	 */
	public static int showNodes(Connection con, Node node, String iFileName, String timeStampStr, int areacode) throws IOException, SQLException {
		int iCounter = 0;
		NamedNodeMap nodeMap = node.getAttributes();
		if ( null != nodeMap ) {
			for ( int j=0; j < nodeMap.getLength(); j++ ) {
				if (nodeMap.item(j).getNodeName().equals("timeStamp")) {
					timeStampStr = nodeMap.item(j).getNodeValue();
				}
			}
		}

		NodeList nodes = node.getChildNodes();
		for (int i=0; i<nodes.getLength(); i++) {
			Node node2 = nodes.item(i);
			if (node2.getNodeName().equals("jps:GM_Point")) {
				showGmPoint(con, node2);
			}
			else if (node2.getNodeName().equals("gml:Point")) {
				showGmlPoint(con, node2, areacode);
			}

			else if (node2.getNodeName().equals("ksj:ED01")) {
				iCounter++;
				showED01(con, node2, iFileName);
			}
			else if (node2.getNodeName().equals("ksj:BusStop")) {
				iCounter++;
				showBusStop(con, node2, iFileName);
			}

			else {
				iCounter += showNodes(con, node2, iFileName, timeStampStr, areacode);
			}
		}
		return iCounter;
	}

	/**
	 *
	 * @param con
	 * @param node
	 * @param iFileName		// ソースファイル名（拡張子を含まない）
	 * @throws IOException
	 * @throws SQLException
	 */
	public static void showED01(Connection con, Node node, String iFileName) throws IOException, SQLException {
		String idrefStr = "";
		String nameStr = "";
		PreparedStatement ps1 = con.prepareStatement("SELECT idref FROM bus_stop WHERE idref=?");
		PreparedStatement ps2 = con.prepareStatement("INSERT INTO bus_stop (idref,name,ifile) VALUES (?,?,?)");
		PreparedStatement ps3 = con.prepareStatement("SELECT code FROM bus_course WHERE course=? AND corp=? AND ifile=?");
		PreparedStatement ps4 = con.prepareStatement("INSERT INTO bus_course (code,type,corp,course,ifile) VALUES (?,?,?,?,?)");
		PreparedStatement ps5 = con.prepareStatement("INSERT INTO bus_ref (idref,code) VALUES (?,?)");
		PreparedStatement ps6 = con.prepareStatement("SELECT max(code) FROM bus_course WHERE ifile=?");

		ArrayList<String[]> bris = new ArrayList<String[]>();
		NodeList nodes = node.getChildNodes();
		for (int i=0; i < nodes.getLength(); i++) {
			Node node2 = nodes.item(i);
			if (node2.getNodeName().equals("ksj:POS")) {
				NamedNodeMap nodeMap = node2.getAttributes();
				if (null != nodeMap) {
					for ( int j=0; j < nodeMap.getLength(); j++ ) {
						if (nodeMap.item(j).getNodeName().equals("idref")) {
							idrefStr = nodeMap.item(j).getNodeValue();
							System.out.println("found idref='"+ idrefStr +"'");
							break;
						}
					}
				}
			}
			else if (node2.getNodeName().equals("ksj:BSN")) {
				nameStr = node2.getTextContent();
			}
			else if (node2.getNodeName().equals("ksj:BRI")) {
				String[] rtn = anaComm(node2);
				if (rtn != null) {
					bris.add(rtn);
				}
			}
		}

		// idref と nameStr をデータベースに格納する
		boolean insert = true;
		ps1.setString(1, idrefStr);
		ResultSet rset = ps1.executeQuery();
		if (rset.next()) {
			insert = false;
		}
		rset.close();

		if (insert) {
			ps2.setString(1, idrefStr);
			ps2.setString(2, nameStr);
			ps2.setString(3, iFileName);
			System.out.println("INSERT INTO bus_stop (idref,name,ifile) VALUES ('"+ idrefStr +"','"+ nameStr +"','"+ iFileName +"')");
			ps2.executeUpdate();
		}

		for (String[] rtn : bris) {
			int code = 0;
			ps3.setString(1, rtn[1]);
			ps3.setString(2, rtn[2]);
			ps3.setString(3, iFileName);
			rset = ps3.executeQuery();
			if (rset.next()) {
				code = rset.getInt(1);
			}
			rset.close();

			if (code == 0) {
				ps6.setString(1, iFileName);
				ResultSet rset6 = ps6.executeQuery();
				if (rset6.next()) {
					code = rset6.getInt(1);
				}
				rset6.close();
				code++;

				System.out.println("code="+code);
				ps4.setInt(1, code);
				ps4.setInt(2, Integer.parseInt(rtn[0]));
				ps4.setString(3, rtn[2]);
				ps4.setString(4, rtn[1]);
				ps4.setString(5, iFileName);
				ps4.executeUpdate();
			}

			ps5.setString(1, idrefStr);
			ps5.setInt(2, code);
			ps5.executeUpdate();
		}

		ps1.close();
		ps2.close();
		ps3.close();
		ps4.close();
		ps5.close();
	}

	/**
	 * <ksj:BusStop gml:id="ED01_1">
	 * 	<ksj:position xlink:href="#n1"/>
	 * 	<ksj:busStopName>城堀</ksj:busStopName>
	 * 	<ksj:busRouteInformation>
	 * 		<ksj:BusRouteInformation>
	 * 			<ksj:busType>1</ksj:busType>
	 * 			<ksj:busOperationCompany>箱根登山バス</ksj:busOperationCompany>
	 * 			<ksj:busLineName>小01</ksj:busLineName>
	 * 		</ksj:BusRouteInformation>
	 * 	</ksj:busRouteInformation>
	 * 	<ksj:busRouteInformation>
	 * 		<ksj:BusRouteInformation>
	 * 			<ksj:busType>1</ksj:busType>
	 * 			<ksj:busOperationCompany>箱根登山バス</ksj:busOperationCompany>
	 * 			<ksj:busLineName>湯07</ksj:busLineName>
	 * 		</ksj:BusRouteInformation>
	 * 	</ksj:busRouteInformation>
	 * 	<ksj:busRouteInformation>
	 * 		<ksj:BusRouteInformation>
	 * 			<ksj:busType>1</ksj:busType>
	 * 			<ksj:busOperationCompany>箱根登山バス</ksj:busOperationCompany>
	 * 			<ksj:busLineName>湯11</ksj:busLineName>
	 * 		</ksj:BusRouteInformation>
	 * 	</ksj:busRouteInformation>
	 * </ksj:BusStop>
	 *
	 * @param con
	 * @param node
	 * @param iFileName		// ソースファイル名（拡張子を含まない）
	 * @throws IOException
	 * @throws SQLException
	 */
	public static void showBusStop(Connection con, Node node, String iFileName) throws IOException, SQLException {
		String idrefStr = "";
		String nameStr = "";
		PreparedStatement ps2 = con.prepareStatement("UPDATE bus_stop SET name=?,ifile=? WHERE idref=?");
		PreparedStatement ps3 = con.prepareStatement("SELECT code FROM bus_course WHERE course=? AND corp=? AND ifile=?");
		PreparedStatement ps4 = con.prepareStatement("INSERT INTO bus_course (code,type,corp,course,ifile) VALUES (?,?,?,?,?)");
		PreparedStatement ps5 = con.prepareStatement("INSERT INTO bus_ref (idref,code) VALUES (?,?)");
		PreparedStatement ps6 = con.prepareStatement("SELECT max(code) FROM bus_course WHERE ifile=?");

		ArrayList<String[]> bris = new ArrayList<String[]>();
		NodeList nodes = node.getChildNodes();
		for (int i=0; i < nodes.getLength(); i++) {
			Node node2 = nodes.item(i);
			if (node2.getNodeName().equals("ksj:position")) {
				NamedNodeMap nodeMap = node2.getAttributes();
				if (null != nodeMap) {
					for ( int j=0; j < nodeMap.getLength(); j++ ) {
						if (nodeMap.item(j).getNodeName().equals("xlink:href")) {
							idrefStr = nodeMap.item(j).getNodeValue();
							idrefStr = idrefStr.substring(1);
							System.out.println("found idref='"+ idrefStr +"'");
							break;
						}
					}
				}
			}
			else if (node2.getNodeName().equals("ksj:busStopName")) {
				nameStr = node2.getTextContent();
			}
			else if (node2.getNodeName().equals("ksj:busRouteInformation")) {
				String[] rtn = anaCommJGD(node2);
				if (rtn != null) {
					bris.add(rtn);
				}
			}
		}

		// idref と nameStr をデータベースに格納する
		ps2.setString(1, nameStr);
		ps2.setString(2, iFileName);
		ps2.setString(3, idrefStr);
		ps2.executeUpdate();

		for (String[] rtn : bris) {
			int code = 0;
			ps3.setString(1, rtn[1]);
			ps3.setString(2, rtn[2]);
			ps3.setString(3, iFileName);
			ResultSet rset = ps3.executeQuery();
			if (rset.next()) {
				code = rset.getInt(1);
			}
			rset.close();

			if (code == 0) {
				ps6.setString(1, iFileName);
				ResultSet rset6 = ps6.executeQuery();
				if (rset6.next()) {
					code = rset6.getInt(1);
				}
				rset6.close();
				code++;

				System.out.println("bus_course="+ code +" : "+ rtn[0] +" : "+ rtn[1] +" : "+ rtn[2] );
				ps4.setInt(1, code);
				ps4.setInt(2, Integer.parseInt(rtn[0]));
				ps4.setString(3, rtn[2]);
				ps4.setString(4, rtn[1]);
				ps4.setString(5, iFileName);
				ps4.executeUpdate();
			}

			System.out.println("bus_ref=("+ idrefStr +", "+ code +")");
			ps5.setString(1, idrefStr);
			ps5.setInt(2, code);
			ps5.executeUpdate();
		}

		ps2.close();
		ps3.close();
		ps4.close();
		ps5.close();
	}

	public static String[] anaComm(Node briNode) {
		String[] rtn = new String[3];
		rtn[0] = "";	// corp type
		rtn[1] = "";	// course name
		rtn[2] = "";	// corp name

		NodeList nodes = briNode.getChildNodes();
		for (int i=0; i < nodes.getLength(); i++) {
			Node node2 = nodes.item(i);
			if (node2.getNodeName().equals("ksj:BSC")) {
				rtn[0] = node2.getTextContent();
			}
			else if (node2.getNodeName().equals("ksj:BLN")) {
				rtn[1] = node2.getTextContent();
			}
			else if (node2.getNodeName().equals("ksj:BOC")) {
				rtn[2] = node2.getTextContent();
			}
		}
		return rtn;
	}

	/**
	 *
	 * 	<ksj:busRouteInformation>
	 * 		<ksj:BusRouteInformation>
	 * 			<ksj:busType>1</ksj:busType>
	 * 			<ksj:busOperationCompany>箱根登山バス</ksj:busOperationCompany>
	 * 			<ksj:busLineName>小01</ksj:busLineName>
	 * 		</ksj:BusRouteInformation>
	 * 	</ksj:busRouteInformation>
	 *
	 * @param briNode
	 * @return
	 */
	public static String[] anaCommJGD(Node briNode) {
		String[] rtn = new String[3];
		int vcnt = 0;

		NodeList nodes2 = briNode.getChildNodes();
		for (int i=0; i < nodes2.getLength(); i++) {
			Node node2 = nodes2.item(i);
			if (node2.getNodeName().equals("ksj:BusRouteInformation")) {
				NodeList nodes3 = node2.getChildNodes();
				for (int j=0; j < nodes3.getLength(); j++) {
					Node node3 = nodes3.item(j);
					if (node3.getNodeName().equals("ksj:busType")) {
						rtn[0] = new String(node3.getTextContent());
						vcnt++;
					}
					else if (node3.getNodeName().equals("ksj:busLineName")) {
						rtn[1] = new String(node3.getTextContent());
						vcnt++;
					}
					else if (node3.getNodeName().equals("ksj:busOperationCompany")) {
						rtn[2] = new String(node3.getTextContent());
						vcnt++;
					}
				}
			}
		}

		if (vcnt > 0) {
			return rtn;
		}
		return null;
	}

	public static void showGmPoint(Connection con, Node node) throws IOException, SQLException {
		String positionStr = "";
		String latStr = "";
		String lonStr = "";
		String idStr = "";

		NamedNodeMap nodeMap = node.getAttributes();
		if ( null != nodeMap ) {
			for ( int j=0; j<nodeMap.getLength(); j++ ) {
				if (nodeMap.item(j).getNodeName().equals("id")) {
					idStr = nodeMap.item(j).getNodeValue();
				}
			}
		}

		NodeList nodes = node.getChildNodes();
		for (int i=0; i < nodes.getLength(); i++) {
			Node node2 = nodes.item(i);
			if (node2.getNodeName().equals("jps:GM_Point.position")) {
				NodeList nodes3 = node2.getChildNodes();
				for (int j=0; j < nodes3.getLength(); j++) {
					Node node3 = nodes3.item(j);
					if (node3.getNodeName().equals("jps:DirectPosition")) {
						NodeList nodes4 = node3.getChildNodes();
						for (int k=0; k < nodes4.getLength(); k++) {
							Node node4 = nodes4.item(k);
							if (node4.getNodeName().equals("DirectPosition.coordinate")) {
								positionStr = node4.getTextContent();
								String[] str4Ary = positionStr.split(" ");
								latStr = str4Ary[0];
								lonStr = str4Ary[1];
								break;
							}
						}
						break;
					}
				}

				PreparedStatement ps6 = con.prepareStatement("UPDATE bus_stop SET lat=?,lon=?,fixed=? WHERE idref=?");
				double lat = Double.parseDouble(latStr);
				double lon = Double.parseDouble(lonStr);
				ps6.setDouble(1, lat);
				ps6.setDouble(2, lon);
				// ps6.setInt(3, (ConvBusstop.nocheck ? 0 : HttpGET.getMap(lat, lon, NEER)));
				ps6.setInt(3, 0);
				ps6.setString(4, idStr);
				System.out.println("UPDATE bus_stop("+ idStr +") lat="+ lat +", lon="+ lon +", fixed=0");
				ps6.executeUpdate();
				ps6.close();
			}
		}
	}

	/**
	 * <gml:Point gml:id="n1">
	 * 	<gml:pos>35.14591397 139.10569573</gml:pos>
	 * </gml:Point>
	 *
	 * @param con
	 * @param node
	 * @throws IOException
	 * @throws SQLException
	 */
	public static void showGmlPoint(Connection con, Node node, int areacode) throws IOException, SQLException {
		String positionStr = "";
		String latStr = "";
		String lonStr = "";
		String idStr = "";

		NamedNodeMap nodeMap = node.getAttributes();
		if ( null != nodeMap ) {
			for ( int j=0; j<nodeMap.getLength(); j++ ) {
				if (nodeMap.item(j).getNodeName().equals("gml:id")) {
					idStr = nodeMap.item(j).getNodeValue();
				}
			}
		}

		NodeList nodes = node.getChildNodes();
		for (int i=0; i < nodes.getLength(); i++) {
			Node node2 = nodes.item(i);
			if (node2.getNodeName().equals("gml:pos")) {
				positionStr = node2.getTextContent().trim();
				String[] str4Ary = positionStr.split(" ");
				latStr = str4Ary[0];
				lonStr = str4Ary[1];
				
				PreparedStatement ps6 = con.prepareStatement("INSERT INTO bus_stop (lat,lon,fixed, area,idref) VALUES (?,?,?,?,?)");
				double lat = Double.parseDouble(latStr);
				double lon = Double.parseDouble(lonStr);
				System.out.println("INSERT INTO bus_stop (lat,lon,fixed,area, idref) VALUES ('"+ latStr +"','"+ lonStr +"','0',"+ areacode +",'"+ idStr +"')");

				ps6.setDouble(1, lat);
				ps6.setDouble(2, lon);
				//ps6.setInt(3, (ConvBusstop.nocheck ? 0 : HttpGET.getMap(lat, lon, NEER)));
				ps6.setInt(3, 0);
				ps6.setInt(4, areacode);
				ps6.setString(5, idStr);
				ps6.executeUpdate();
				ps6.close();
			}
		}
	}

	/**
	 * OSMデータファイルかどうかを見極める
	 * @param f
	 * @return
	 */
	static boolean checkFile(File f, int areacode) {
		String name = f.getName();
		if (!name.startsWith(GML_DIR_PREFIX)) {
			return false;
		}
		if (!name.toUpperCase().endsWith(".XML")) {
			return false;
		}
		if (Integer.parseInt(name.substring(GML_DIR_PREFIX.length(), GML_DIR_PREFIX.length()+2)) == areacode) {
			return true;
		}
		return false;
	}
	
	/**
	 * 数値地図情報のデータファイルかどうかを見極める
	 * @param f
	 * @return
	 */
	static boolean checkGMLdir(File f) {
		if (!f.isDirectory()) {
			return false;
		}
		String name = f.getName();
		if (!name.startsWith(GML_DIR_PREFIX)) {
			return false;
		}
		if (!name.toUpperCase().endsWith(GML_DIR_PRIFIX)) {
			return false;
		}
		return true;
	}

	public static final String GML_DIR_PREFIX = "P11-10_";
	public static final String GML_DIR_PRIFIX = "_GML";

}