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