<?php
include_once("../config/config.php");
include_once('../config/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>