Mercurial > hg > extraction-interface

annotate map/coordinates/provincial_capital_coordinates.php @ 22:a8ae5cbc3364

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
update topic_tag_relation table when adding new tag
author Zoe Hong <zhong@mpiwg-berlin.mpg.de>
date Tue, 24 Feb 2015 17:29:56 +0100
parents b12c99b7c3f0
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
0
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
1 <?php
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
2 include_once("../../interface/config.php");
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
3 include_once('../../interface/Lib_mb_utf8.php');
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
4 set_time_limit(0);
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
5 ini_set('memory_limit', '-1');
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
6 ini_set("auto_detect_line_endings", true);
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
7
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
8 $link_mysql = mysql_connect($mysql_server, $mysql_user, $mysql_password);
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
9 mysql_query("SET NAMES utf8");
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
10
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
11 if (!$link_mysql) {
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
12 die('Could not connect: ' . mysql_error());
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
13 }
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
14
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
15 $db_selected = mysql_select_db($mysql_database, $link_mysql);
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
16 if (!$db_selected) {
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
17 die ('Can\'t use foo : ' . mysql_error());
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
18 }
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
19
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
20 function getCoordinateFromFile(){
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
21 $fp=fopen("provincial_capital_coordinates.txt","r");
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
22 $columnNameMapping=[0=>"province_cht",1=>"province_py",2=>"province_chs",
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
23 3=>"capital_cht",4=>"capital_py",5=>"capital_chs",
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
24 6=>"x",7=>"y"];
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
25 $data=fgetcsv($fp,10000,"\t");
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
26 $coordinateArray=array();
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
27 $count=0;
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
28 while(!feof($fp)){
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
29 $data=fgetcsv($fp,10000,"\t");
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
30 foreach($columnNameMapping as $oldName=>$newName){
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
31 $coordinateArray[$count][$newName]=$data[$oldName];
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
32 }
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
33 $count++;
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
34 }
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
35 return $coordinateArray;
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
36 }
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
37
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
38 function printCoordinate($coordinateArray){
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
39 echo "<table>";
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
40 foreach($coordinateArray as $coordinate){
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
41 echo "<tr>";
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
42 foreach($coordinate as $column){
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
43 echo "<td>".$column;
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
44 }
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
45 }
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
46 echo "</table>";
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
47 }
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
48 function provinceIsInDatabase($province){
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
49 $query="SELECT * FROM coordinates_provincial_capitals WHERE province_cht='".$province."'";
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
50 $result=mysql_query($query);
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
51 if(mysql_num_rows($result)!=0){
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
52 return true;
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
53 }
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
54 return false;
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
55 }
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
56 function insertCoordinateIntoDatabase($coordinateArray){
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
57 $row=0;
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
58 foreach($coordinateArray as $coordinate){
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
59 if(provinceIsInDatabase($coordinate['province_cht'])){
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
60 echo "------------".$coordinate['province_cht']."<br>";
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
61 }else{
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
62 $query="INSERT INTO coordinates_provincial_capitals (";
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
63 $count=0;
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
64 foreach($coordinate as $columnName=>$column){
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
65 $query.=$columnName;
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
66 if($count!=sizeof($coordinate)-1){
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
67 $query.=",";
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
68 }
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
69 $count++;
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
70 }
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
71 $query.=") VALUES (";
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
72 $count=0;
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
73 foreach($coordinate as $columnName=>$column){
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
74 $query.="'".$column."'";
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
75 if($count!=sizeof($coordinate)-1){
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
76 $query.=",";
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
77 }
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
78 $count++;
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
79 }
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
80 $query.=")";
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
81 echo $query."<br>";
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
82 $result=mysql_query($query);
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
83 if(!$result){
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
84 echo "Failed during inserting: <br>";
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
85 echo $query."<br>";
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
86 }else{
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
87 echo "++++++++++++".$coordinate['province_cht']."<br>";
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
88 $row++;
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
89 }
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
90 }
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
91 }
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
92 echo "# of rows: ".$row."<br>";
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
93 }
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
94
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
95 function getBookListFromDatabase(){
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
96 $query="SELECT books.level1 AS Address, books.level1 AS LEVEL1, books.name AS Name, books.period AS PERIOD,
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
97 books.level2 AS LEVEL2, books.start_year AS 'TimeSpan:begin', books.end_year AS 'TimeSpan:end',
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
98 books.id AS BOOK_ID, coor.x AS Longitude, coor.y AS Latitude,
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
99 info.volume AS VOLUME, info.author AS AUTHOR, info.edition AS EDITION FROM books
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
100 JOIN coordinates_provincial_capitals coor ON books.level1=coor.province_cht
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
101 JOIN books_info info ON books.id=info.books_id";
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
102 $bookArray=array();
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
103 $result=mysql_query($query);
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
104 while($row=mysql_fetch_assoc($result)){
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
105 $bookArray[]=$row;
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
106 }
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
107 return $bookArray;
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
108 }
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
109 function getBookListFromFile(){
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
110 $fp=fopen("local_monographs_list.txt","r");
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
111 fgetcsv($fp,100000,"\t");
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
112 $bookArray=array();
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
113 $count=0;
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
114 //$columnNameMapping=[0=>"level1",2=>"level2",4=>"years",5=>"period",6=>"bookId",8=>"title",9=>"placeName"];
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
115 $columnNameMapping=["Address"=>0,"Name"=>8,"LEVEL1"=>0,"PLACE_NAME"=>9,
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
116 "Longitude"=>0,"Latitude"=>0,
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
117 "TimeSpan:begin"=>0,"TimeSpan:end"=>0,"PERIOD"=>5,
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
118 "BOOK_ID"=>6,"Description"=>8];
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
119 while(!feof($fp)){
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
120 $data=fgetcsv($fp,100000,"\t");
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
121 $pattern='/([0-9]+)-([0-9]+)/';
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
122 if(!preg_match($pattern,$data[4],$match)){
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
123 continue;
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
124 }
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
125 foreach($columnNameMapping as $newName=>$oldName){
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
126 $bookArray[$count][$newName]=$data[$oldName];
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
127 }
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
128 $bookArray[$count]['BOOK_ID']=sprintf("%05d",$bookArray[$count]['BOOK_ID']);
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
129 $bookArray[$count]['Name']="(".$bookArray[$count]['PERIOD'].") ".$bookArray[$count]['Name'];
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
130 $bookArray[$count]["TimeSpan:begin"]=$match[1];
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
131 $bookArray[$count]["TimeSpan:end"]=$match[2];
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
132 $coordinate=getCoordinate($bookArray[$count]["LEVEL1"]);
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
133 $bookArray[$count]["Longitude"]=$coordinate["x"];
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
134 $bookArray[$count]["Latitude"]=$coordinate["y"];
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
135 $count++;
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
136 }
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
137 fclose($fp);
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
138
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
139 $fp=fopen("local_monographs_list_176.txt","r");
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
140 fgetcsv($fp,100000,"\t");
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
141 while(!feof($fp)){
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
142 $data=fgetcsv($fp,100000,"\t");
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
143 $pattern='/([0-9]+)-([0-9]+)/';
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
144 if(!preg_match($pattern,$data[4],$match)){
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
145 continue;
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
146 }
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
147 foreach($columnNameMapping as $newName=>$oldName){
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
148 $bookArray[$count][$newName]=$data[$oldName];
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
149 }
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
150 $bookArray[$count]['BOOK_ID']=sprintf("%05d",$bookArray[$count]['BOOK_ID']);
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
151 $bookArray[$count]['Name']="(".$bookArray[$count]['PERIOD'].") ".$bookArray[$count]['Name'];
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
152 $bookArray[$count]["TimeSpan:begin"]=$match[1];
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
153 $bookArray[$count]["TimeSpan:end"]=$match[2];
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
154 $coordinate=getCoordinate($bookArray[$count]["LEVEL1"]);
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
155 $bookArray[$count]["Longitude"]=$coordinate["x"];
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
156 $bookArray[$count]["Latitude"]=$coordinate["y"];
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
157 $count++;
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
158 }
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
159 fclose($fp);
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
160
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
161 return $bookArray;
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
162 }
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
163 function getCoordinate($province){
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
164 $query="SELECT * FROM coordinates_provincial_capitals WHERE province_cht='".$province."'";
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
165 $result=mysql_query($query);
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
166 $coordinate=mysql_fetch_assoc($result);
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
167 return $coordinate;
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
168 }
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
169 function bookIsInDatabase($bookId){
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
170 $query="SELECT * FROM books WHERE id='".$bookId."'";
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
171 $result=mysql_query($query);
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
172 if(mysql_num_rows($result)!=0){
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
173 return true;
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
174 }
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
175 return false;
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
176 }
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
177 function updateYearInDatabase($bookArray){
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
178 $row=0;
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
179 foreach($bookArray as $book){
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
180 if(!bookIsInDatabase($book['BOOK_ID'])){
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
181 echo $book['BOOK_ID']."!!!<br>";
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
182 continue;
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
183 }
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
184 $query="UPDATE books SET start_year=".$book['TimeSpan:begin'].", end_year=".$book['TimeSpan:end']." WHERE id='".$book['BOOK_ID']."'";
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
185 $result=mysql_query($query);
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
186 if(!$result){
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
187 echo "Failed during updating: <br>";
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
188 echo $query."<br>";
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
189 }else{
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
190 $row++;
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
191 }
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
192 }
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
193 }
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
194
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
195 ?>
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
196
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
197 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
198 <html>
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
199 <head>
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
200 <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
201 </head>
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
202 <body>
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
203 <?php
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
204 //$coordinateArray=getCoordinateFromFile();
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
205 //printCoordinate($coordinateArray);
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
206 //insertCoordinateIntoDatabase($coordinateArray);//insert coordinates of provincial capitals into table "coordinates_provincial_capitals"
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
207 //$bookArray=getBookListFromFile();//get the additional info which is not yet in table "books"
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
208 //updateYearInDatabase($bookArray);//fill in the "start_year" and "end_year" columns in "books"
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
209 $bookArray=getBookListFromDatabase();
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
210 //PROVINCIAL: Address,LEVEL1,Name,PERIOD,LEVEL2,TimeSpan:begin,TimeSpan:end,BOOK_ID,Longitude,Latitude,VOLUME,AUTHOR,EDITION
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
211 foreach($bookArray as $idx=>$book){
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
212 $bookArray[$idx]['Name']="(".$book['PERIOD'].") ".$book['Name'];
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
213 $book['AUTHOR']=str_replace("（","(",$book['AUTHOR']);
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
214 $book['AUTHOR']=str_replace("）",") ",$book['AUTHOR']);
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
215 $bookArray[$idx]['Description']=$book['VOLUME']." ╱ ".$book['AUTHOR']." ╱ ".$book['EDITION'];
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
216 }
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
217 $columnNameArray=['Address','LEVEL1','Name','PERIOD','TimeSpan:begin','TimeSpan:end','Longitude','Latitude','BOOK_ID','Description'];
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
218
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
219 echo sizeof($bookArray)."<br>";
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
220 echo "<table>";
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
221 echo "<tr>";
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
222 $fp=fopen("provincial_capital_coordinates.csv","w");
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
223 foreach($columnNameArray as $column){
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
224 echo "<td>".$column;
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
225 }
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
226 fputcsv($fp,$columnNameArray);
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
227 $count=0;
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
228 foreach($bookArray as $book){
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
229 echo "<tr>";
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
230 $row=array();
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
231 foreach($columnNameArray as $column){
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
232 echo "<td>".$book[$column];
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
233 $row[]=$book[$column];
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
234 }
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
235 fputcsv($fp,$row);
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
236 $count++;
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
237 }
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
238 echo "</table>";
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
239
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
240 ?>
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
241
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
242 </body>
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
243 </html>
b12c99b7c3f0 commit for previous development
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
diff changeset
244