--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/coordinates/provincial_capital_coordinates.php	Tue Mar 24 11:37:17 2015 +0100
@@ -0,0 +1,244 @@
+<?php
+include_once("../../interface/config.php");
+include_once('../../interface/Lib_mb_utf8.php');
+set_time_limit(0);
+ini_set('memory_limit', '-1');
+ini_set("auto_detect_line_endings", true);
+
+$link_mysql = mysql_connect($mysql_server, $mysql_user, $mysql_password);
+mysql_query("SET NAMES utf8");
+
+if (!$link_mysql) {
+	die('Could not connect: ' . mysql_error());
+}
+
+$db_selected = mysql_select_db($mysql_database, $link_mysql);
+if (!$db_selected) {
+	die ('Can\'t use foo : ' . mysql_error());
+}
+
+function getCoordinateFromFile(){
+	$fp=fopen("provincial_capital_coordinates.txt","r");
+	$columnNameMapping=[0=>"province_cht",1=>"province_py",2=>"province_chs",
+		3=>"capital_cht",4=>"capital_py",5=>"capital_chs",
+		6=>"x",7=>"y"];
+	$data=fgetcsv($fp,10000,"\t");
+	$coordinateArray=array();
+	$count=0;
+	while(!feof($fp)){
+		$data=fgetcsv($fp,10000,"\t");
+		foreach($columnNameMapping as $oldName=>$newName){
+			$coordinateArray[$count][$newName]=$data[$oldName];
+		}
+		$count++;
+	}
+	return $coordinateArray;
+}
+
+function printCoordinate($coordinateArray){
+	echo "<table>";
+	foreach($coordinateArray as $coordinate){
+		echo "<tr>";
+		foreach($coordinate as $column){
+			echo "<td>".$column;
+		}
+	}
+	echo "</table>";
+}
+function provinceIsInDatabase($province){
+	$query="SELECT * FROM coordinates_provincial_capitals WHERE province_cht='".$province."'";
+	$result=mysql_query($query);
+	if(mysql_num_rows($result)!=0){
+		return true;
+	}
+	return false;
+}
+function insertCoordinateIntoDatabase($coordinateArray){
+	$row=0;
+	foreach($coordinateArray as $coordinate){
+		if(provinceIsInDatabase($coordinate['province_cht'])){
+			echo "------------".$coordinate['province_cht']."<br>";
+		}else{
+			$query="INSERT INTO coordinates_provincial_capitals (";
+			$count=0;
+			foreach($coordinate as $columnName=>$column){
+				$query.=$columnName;
+				if($count!=sizeof($coordinate)-1){
+					$query.=",";
+				}
+				$count++;
+			}
+			$query.=") VALUES (";
+			$count=0;
+			foreach($coordinate as $columnName=>$column){
+				$query.="'".$column."'";
+				if($count!=sizeof($coordinate)-1){
+					$query.=",";
+				}
+				$count++;
+			}
+			$query.=")";
+			echo $query."<br>";
+			$result=mysql_query($query);
+			if(!$result){
+				echo "Failed during inserting: <br>";
+				echo $query."<br>";
+			}else{
+				echo "++++++++++++".$coordinate['province_cht']."<br>";
+				$row++;
+			}
+		}
+	}	
+	echo "# of rows: ".$row."<br>";
+}
+
+function getBookListFromDatabase(){
+	$query="SELECT books.level1 AS Address, books.level1 AS LEVEL1, books.name AS Name, books.period AS PERIOD, 
+		books.level2 AS LEVEL2, books.start_year AS 'TimeSpan:begin', books.end_year AS 'TimeSpan:end',
+		books.id AS BOOK_ID, coor.x AS Longitude, coor.y AS Latitude, 
+		info.volume AS VOLUME, info.author AS AUTHOR, info.edition AS EDITION FROM books 
+			JOIN coordinates_provincial_capitals coor ON books.level1=coor.province_cht 
+			JOIN books_info info ON books.id=info.books_id";
+	$bookArray=array();
+	$result=mysql_query($query);
+	while($row=mysql_fetch_assoc($result)){
+		$bookArray[]=$row;
+	}
+	return $bookArray;
+}
+function getBookListFromFile(){
+	$fp=fopen("local_monographs_list.txt","r");
+	fgetcsv($fp,100000,"\t");
+	$bookArray=array();
+	$count=0;
+	//$columnNameMapping=[0=>"level1",2=>"level2",4=>"years",5=>"period",6=>"bookId",8=>"title",9=>"placeName"];
+	$columnNameMapping=["Address"=>0,"Name"=>8,"LEVEL1"=>0,"PLACE_NAME"=>9,
+		"Longitude"=>0,"Latitude"=>0,
+		"TimeSpan:begin"=>0,"TimeSpan:end"=>0,"PERIOD"=>5,
+		"BOOK_ID"=>6,"Description"=>8];
+	while(!feof($fp)){
+		$data=fgetcsv($fp,100000,"\t");
+		$pattern='/([0-9]+)-([0-9]+)/';
+		if(!preg_match($pattern,$data[4],$match)){
+			continue;
+		}
+		foreach($columnNameMapping as $newName=>$oldName){
+			$bookArray[$count][$newName]=$data[$oldName];
+		}
+		$bookArray[$count]['BOOK_ID']=sprintf("%05d",$bookArray[$count]['BOOK_ID']);
+		$bookArray[$count]['Name']="(".$bookArray[$count]['PERIOD'].") ".$bookArray[$count]['Name'];
+		$bookArray[$count]["TimeSpan:begin"]=$match[1];
+		$bookArray[$count]["TimeSpan:end"]=$match[2];
+		$coordinate=getCoordinate($bookArray[$count]["LEVEL1"]);
+		$bookArray[$count]["Longitude"]=$coordinate["x"];
+		$bookArray[$count]["Latitude"]=$coordinate["y"];
+		$count++;
+	}
+	fclose($fp);
+
+	$fp=fopen("local_monographs_list_176.txt","r");
+	fgetcsv($fp,100000,"\t");
+	while(!feof($fp)){
+		$data=fgetcsv($fp,100000,"\t");
+		$pattern='/([0-9]+)-([0-9]+)/';
+		if(!preg_match($pattern,$data[4],$match)){
+			continue;
+		}
+		foreach($columnNameMapping as $newName=>$oldName){
+			$bookArray[$count][$newName]=$data[$oldName];
+		}
+		$bookArray[$count]['BOOK_ID']=sprintf("%05d",$bookArray[$count]['BOOK_ID']);
+		$bookArray[$count]['Name']="(".$bookArray[$count]['PERIOD'].") ".$bookArray[$count]['Name'];
+		$bookArray[$count]["TimeSpan:begin"]=$match[1];
+		$bookArray[$count]["TimeSpan:end"]=$match[2];
+		$coordinate=getCoordinate($bookArray[$count]["LEVEL1"]);
+		$bookArray[$count]["Longitude"]=$coordinate["x"];
+		$bookArray[$count]["Latitude"]=$coordinate["y"];
+		$count++;
+	}
+	fclose($fp);
+
+	return $bookArray;
+}
+function getCoordinate($province){
+	$query="SELECT * FROM coordinates_provincial_capitals WHERE province_cht='".$province."'";
+	$result=mysql_query($query);
+	$coordinate=mysql_fetch_assoc($result);
+	return $coordinate;
+}
+function bookIsInDatabase($bookId){
+	$query="SELECT * FROM books WHERE id='".$bookId."'";
+	$result=mysql_query($query);
+	if(mysql_num_rows($result)!=0){
+		return true;
+	}
+	return false;
+}
+function updateYearInDatabase($bookArray){
+	$row=0;
+	foreach($bookArray as $book){
+		if(!bookIsInDatabase($book['BOOK_ID'])){
+			echo $book['BOOK_ID']."!!!<br>";
+			continue;
+		}
+		$query="UPDATE books SET start_year=".$book['TimeSpan:begin'].", end_year=".$book['TimeSpan:end']." WHERE id='".$book['BOOK_ID']."'";
+		$result=mysql_query($query);
+		if(!$result){
+			echo "Failed during updating: <br>";
+			echo $query."<br>";
+		}else{
+			$row++;
+		}
+	}
+}
+
+?>
+
+<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
+<html>
+<head>
+<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
+</head>
+<body>
+<?php
+//$coordinateArray=getCoordinateFromFile();
+//printCoordinate($coordinateArray);
+//insertCoordinateIntoDatabase($coordinateArray);//insert coordinates of provincial capitals into table "coordinates_provincial_capitals"
+//$bookArray=getBookListFromFile();//get the additional info which is not yet in table "books"
+//updateYearInDatabase($bookArray);//fill in the "start_year" and "end_year" columns in "books"
+$bookArray=getBookListFromDatabase();
+//PROVINCIAL: Address,LEVEL1,Name,PERIOD,LEVEL2,TimeSpan:begin,TimeSpan:end,BOOK_ID,Longitude,Latitude,VOLUME,AUTHOR,EDITION
+foreach($bookArray as $idx=>$book){
+	$bookArray[$idx]['Name']="(".$book['PERIOD'].") ".$book['Name'];
+	$book['AUTHOR']=str_replace("（","(",$book['AUTHOR']);
+	$book['AUTHOR']=str_replace("）",") ",$book['AUTHOR']);
+	$bookArray[$idx]['Description']=$book['VOLUME']." ╱ ".$book['AUTHOR']." ╱ ".$book['EDITION'];
+}
+$columnNameArray=['Address','LEVEL1','Name','PERIOD','TimeSpan:begin','TimeSpan:end','Longitude','Latitude','BOOK_ID','Description'];
+
+echo sizeof($bookArray)."<br>";
+echo "<table>";
+echo "<tr>";
+$fp=fopen("provincial_capital_coordinates.csv","w");
+foreach($columnNameArray as $column){
+	echo "<td>".$column;
+}
+fputcsv($fp,$columnNameArray);
+$count=0;
+foreach($bookArray as $book){
+	echo "<tr>";
+	$row=array();
+	foreach($columnNameArray as $column){
+		echo "<td>".$book[$column];
+		$row[]=$book[$column];
+	}
+	fputcsv($fp,$row);
+	$count++;
+}
+echo "</table>";
+
+?>
+
+</body>
+</html>
+