|
0
|
1 <?php
|
|
|
2 include_once('Lib_mb_utf8.php');
|
|
|
3 include_once('config.php');
|
|
|
4 set_time_limit(0);
|
|
|
5 ini_set('memory_limit', '-1');
|
|
|
6
|
|
|
7 $link_mysql = mysql_connect($mysql_server, $mysql_user, $mysql_password);
|
|
|
8 mysql_query("SET NAMES utf8");
|
|
|
9
|
|
|
10 if (!$link_mysql) {
|
|
|
11 die('Could not connect: ' . mysql_error());
|
|
|
12 }
|
|
|
13
|
|
|
14 $db_selected = mysql_select_db($mysql_database, $link_mysql);
|
|
|
15 if (!$db_selected) {
|
|
|
16 die ('Can\'t use foo : ' . mysql_error());
|
|
|
17 }
|
|
|
18 function checkSectionInfo($bookId){
|
|
|
19 $query="SELECT * FROM sections_versions WHERE books_id=".$bookId." ORDER BY version DESC";
|
|
|
20 $result=mysql_query($query);
|
|
|
21 if(mysql_num_rows($result)==0){
|
|
|
22 $row['version']=0;
|
|
|
23 $row['editor']="";
|
|
|
24 $row['date']="";
|
|
|
25 return $row;
|
|
|
26 }else{
|
|
|
27 $row=mysql_fetch_assoc($result);
|
|
|
28 return $row;
|
|
|
29 }
|
|
|
30 }
|
|
|
31 function checkMissingPage($bookInfo,$versionInfo){
|
|
|
32 $bookId=$bookInfo['id'];
|
|
|
33 $bookPage=$bookInfo['line'];
|
|
|
34 if($versionInfo['version']!=0){
|
|
|
35 $table="sections_revisions";
|
|
|
36 $condition=" AND versions_id=".$versionInfo['id']." AND deleted=0 ";
|
|
|
37 $versionId=$versionInfo['id'];
|
|
|
38 }else{
|
|
|
39 $table="sections";
|
|
|
40 $condition="";
|
|
|
41 }
|
|
|
42 $query="SELECT *
|
|
|
43 FROM ".$table."
|
|
|
44 WHERE books_id=".$bookId." ".$condition."
|
|
|
45 GROUP BY books_id,start_page,end_page
|
|
|
46 ORDER BY books_id,start_page,level,end_page";
|
|
|
47 $result=mysql_query($query);
|
|
|
48 $lastPage=1;
|
|
|
49 $i=0;
|
|
|
50 $pageArray=array();
|
|
|
51 while($row=mysql_fetch_assoc($result)){
|
|
|
52 if($row['start_page']-1>$lastPage){
|
|
|
53 $pageArray[$i]['start_page']=$lastPage;
|
|
|
54 $pageArray[$i]['end_page']=$row['start_page'];
|
|
|
55 //echo $pageArray[$i]['start_page']." ".$pageArray[$i]['end_page']."<br>";
|
|
|
56 $i++;
|
|
|
57 }
|
|
|
58 $lastPage=$row['end_page'];
|
|
|
59 }
|
|
|
60 if($bookPage>$lastPage){
|
|
|
61 $pageArray[$i]['start_page']=$lastPage;
|
|
|
62 $pageArray[$i]['end_page']=$bookPage;
|
|
|
63 }
|
|
|
64 return $pageArray;
|
|
|
65 }
|
|
|
66 function getSectionArray($bookId,$count,$versionInfo){
|
|
|
67
|
|
|
68 $str="";
|
|
|
69 if($versionInfo['version']!=0){
|
|
|
70 $table="sections_revisions";
|
|
|
71 $condition="AND deleted=0 ";
|
|
|
72 $condition=" AND versions_id=".$versionInfo['id']." AND deleted=0 ";
|
|
|
73 }else{
|
|
|
74 $table="sections";
|
|
|
75 $condition="";
|
|
|
76 }
|
|
|
77
|
|
|
78 // echo "select from ".$table."\n";
|
|
|
79
|
|
|
80
|
|
|
81 $query="SELECT *
|
|
|
82 FROM ".$table."
|
|
|
83 WHERE books_id=".$bookId." ".$condition."
|
|
|
84 GROUP BY books_id, start_page, end_page
|
|
|
85 HAVING COUNT( * ) >=".$count."
|
|
|
86 ORDER BY id ASC";
|
|
|
87 /*ORDER BY start_page ASC, level ASC, id ASC,end_page DESC";*/
|
|
|
88 $result=mysql_query($query);
|
|
|
89
|
|
|
90 $i=0;
|
|
|
91 $pageArray=array();
|
|
|
92 while($row=mysql_fetch_assoc($result)){
|
|
|
93 $pageArray[$i]['start_page']=$row['start_page'];
|
|
|
94 $pageArray[$i]['end_page']=$row['end_page'];
|
|
|
95 $i++;
|
|
|
96 }
|
|
|
97 if($versionInfo['version']!=0){
|
|
|
98 $query="SELECT id AS revisions_id, name,books_id,section_after,start_page,end_page,level,split_from,sections_id AS id, deleted FROM sections_revisions WHERE versions_id=".$versionInfo['id']." ORDER BY revisions_id ASC";
|
|
|
99 }else{
|
|
|
100 $query="SELECT * FROM sections WHERE books_id=".$bookId." ORDER BY id ASC";
|
|
|
101 }
|
|
|
102 $result=mysql_query($query);
|
|
|
103
|
|
|
104 $i=0;
|
|
|
105 $sectionArray=array();
|
|
|
106 while($row=mysql_fetch_assoc($result)){
|
|
|
107 $flag="";
|
|
|
108 if($i!=sizeof($pageArray) && $pageArray[$i]['start_page']==$row['start_page'] && $pageArray[$i]['end_page']==$row['end_page']){
|
|
|
109 $flag="overlapper";
|
|
|
110 $i++;
|
|
|
111 }
|
|
|
112 if($i!=0 && $pageArray[$i-1]['start_page']==$row['start_page'] && $pageArray[$i-1]['end_page']==$row['end_page']){
|
|
|
113 $flag="overlapper";
|
|
|
114 }
|
|
|
115 $row['flag']=$flag;
|
|
|
116 if(!isset($row['deleted'])){
|
|
|
117 $row['deleted']="";
|
|
|
118 }else{
|
|
|
119 if($row['deleted']==0){
|
|
|
120 $row['deleted']="";
|
|
|
121 }else{
|
|
|
122 $row['deleted']="deleted";
|
|
|
123 }
|
|
|
124 }
|
|
|
125 $sectionArray[]=$row;
|
|
|
126 }
|
|
|
127 return $sectionArray;
|
|
|
128 }
|
|
|
129 function updateSectionArray($bookId,$sectionArray,$version,$editor){
|
|
|
130 $query="INSERT INTO sections_versions (version,editor,date,books_id) VALUES (".($version+1).",'".$editor."',NOW(),'".$bookId."')";
|
|
|
131 $result=mysql_query($query);
|
|
|
132 if(!$result){
|
|
|
133 echo json_encode("Failed during inserting sections_version records."); // .mysql_error();
|
|
|
134 return;
|
|
|
135 }
|
|
|
136 $versionId=mysql_insert_id();
|
|
|
137 $query="INSERT INTO sections_revisions (name,books_id,section_after,start_page,end_page,level,split_from,sections_id,versions_id,deleted) VALUES ";
|
|
|
138 foreach($sectionArray as $idx=>$row){
|
|
|
139 $str="(";
|
|
|
140 $str.="'".$row['name']."',";
|
|
|
141 $str.="'".sprintf("%05d",$row['booksId'])."',";
|
|
|
142 $str.="'".$row['sectionAfter']."',";
|
|
|
143 $str.=$row['startPage'].",";
|
|
|
144 $str.=$row['endPage'].",";
|
|
|
145 $str.=$row['level'].",";
|
|
|
146 $str.=$row['splitFrom'].",";
|
|
|
147 $str.=$row['id'].",";
|
|
|
148 $str.=$versionId.",";
|
|
|
149 $str.=$row['deleted'];
|
|
|
150 $str.=")";
|
|
|
151 if($idx!=sizeof($sectionArray)-1){
|
|
|
152 $str.=", ";
|
|
|
153 }
|
|
|
154 $query.=$str;
|
|
|
155 }
|
|
|
156 $result=mysql_query($query);
|
|
|
157 if(!$result){
|
|
|
158 echo json_encode("Failed during inserting sections_revisions records."); // .mysql_error();
|
|
|
159
|
|
|
160 // Delete the sections_versions for this user
|
|
|
161 // delete $versionId in table sections_versions
|
|
|
162 $query="DELETE FROM sections_versions WHERE id=".$versionId;
|
|
|
163 $result_deleting=mysql_query($query);
|
|
|
164 if(!$result_deleting){
|
|
|
165 echo json_encode("Failed during deleting wrongly inserted sections_version record.");
|
|
|
166 }
|
|
|
167 return;
|
|
|
168 }else{
|
|
|
169 echo json_encode("Succeeded.");
|
|
|
170 }
|
|
|
171 }
|
|
|
172
|
|
|
173
|
|
|
174 function _select_distinct(){
|
|
|
175 /*
|
|
|
176 $query="SELECT DISTINCT (books_id) FROM sections";
|
|
|
177
|
|
|
178 $result=mysql_query($query);
|
|
|
179 if(!$result){
|
|
|
180 echo json_encode("Failed during inserting section records.");
|
|
|
181 return;
|
|
|
182 }else{
|
|
|
183 echo json_encode("Succeeded.");
|
|
|
184 }
|
|
|
185
|
|
|
186 while($row=mysql_fetch_assoc($result)){
|
|
|
187 // echo $row['books_id'], ", ";
|
|
|
188 }
|
|
|
189
|
|
|
190 echo "section num: ".mysql_num_rows($result);
|
|
|
191 echo '\n';
|
|
|
192 */
|
|
|
193
|
|
|
194
|
|
|
195 //$query="SELECT id FROM books WHERE id NOT IN (SELECT DISTINCT (books_id) FROM sections)";
|
|
|
196 $query="SELECT books.id, books.name FROM books LEFT JOIN sections ON books.id=sections.books_id WHERE sections.books_id IS NULL";
|
|
|
197
|
|
|
198 $result=mysql_query($query);
|
|
|
199 if(!$result){
|
|
|
200 echo json_encode("Failed during querying records.");
|
|
|
201 return;
|
|
|
202 }
|
|
|
203
|
|
|
204 echo "missing books num: ".mysql_num_rows($result);
|
|
|
205 echo '\n';
|
|
|
206
|
|
|
207
|
|
|
208
|
|
|
209
|
|
|
210 }
|
|
|
211
|
|
|
212
|
|
|
213
|
|
|
214 function add_missing_books_to_sections(){
|
|
|
215 // find missing books
|
|
|
216 // Run the following line/mysql to find out what books are missing in sections table
|
|
|
217 // _select_distinct();
|
|
|
218 // Or load missing_books_id.csv
|
|
|
219 if (($missing_books = fopen("intermediate_results/missing_books.csv", "r")) !== FALSE) {
|
|
|
220 while (($data = fgetcsv($missing_books, ",")) !== FALSE) {
|
|
|
221
|
|
|
222 $bookId = $data[0];
|
|
|
223 $name = $data[1];
|
|
|
224
|
|
|
225 _add_book_to_sections($name, $bookId);
|
|
|
226
|
|
|
227 }
|
|
|
228 fclose($missing_books);
|
|
|
229 }
|
|
|
230
|
|
|
231 }
|
|
|
232
|
|
|
233
|
|
|
234 function _add_book_to_sections($name, $bookId){
|
|
|
235
|
|
|
236 /*
|
|
|
237 // get Auto_increment
|
|
|
238 $query = mysql_query("SHOW TABLE STATUS WHERE name='sections'");
|
|
|
239 if (mysql_num_rows($query)) {
|
|
|
240 $result = mysql_fetch_assoc($query);
|
|
|
241 echo $result['Auto_increment'];
|
|
|
242 } else {//error
|
|
|
243 //error control here
|
|
|
244 echo "error in getting Auto_increment";
|
|
|
245 }
|
|
|
246 */
|
|
|
247
|
|
|
248 // id should be autoincremated
|
|
|
249 $query="INSERT INTO sections (name,books_id,section_after) VALUES ";
|
|
|
250
|
|
|
251 $str="(";
|
|
|
252 $str.="'".$name."','";
|
|
|
253 $str.=$bookId."',";
|
|
|
254 $str.="''";
|
|
|
255 $str.=")";
|
|
|
256 $query.=$str;
|
|
|
257
|
|
|
258 // echo $name.", ".$bookId."\n";
|
|
|
259
|
|
|
260 $result=mysql_query($query);
|
|
|
261 if(!$result){
|
|
|
262 echo json_encode("Failed during inserting section records.");
|
|
|
263 return;
|
|
|
264 }
|
|
|
265 // echo mysql_insert_id();
|
|
|
266
|
|
|
267 }
|
|
|
268
|
|
|
269
|
|
|
270 if(isset($_POST['command']) && $_POST['command']!=''){
|
|
|
271 $command=$_POST['command'];
|
|
|
272 if($command=="write"){
|
|
|
273
|
|
|
274 if(isset($_POST['bookId'])&&isset($_POST['sectionArray'])&&isset($_POST['version'])&&isset($_POST['editor'])&&\
|
|
|
275 is_numeric($_POST['bookId']) && sizeof($_POST['sectionArray'])!=0 && is_numeric($_POST['version']) && $_POST['editor']!=""){
|
|
|
276 $bookId=$_POST['bookId'];
|
|
|
277 $sectionArray=$_POST['sectionArray'];
|
|
|
278 $version=$_POST['version'];
|
|
|
279 $editor=$_POST['editor'];
|
|
|
280
|
|
|
281 updateSectionArray($bookId,$sectionArray,$version,$editor);
|
|
|
282 }
|
|
|
283 else{
|
|
|
284 echo json_encode("Editing info not correct.");
|
|
|
285
|
|
|
286 }
|
|
|
287 }
|
|
|
288 else{
|
|
|
289 echo json_encode("Wrong command.");
|
|
|
290 return;
|
|
|
291 }
|
|
|
292 }
|
|
|
293
|
|
|
294 ?>
|
