Mercurial > hg > LGToc
annotate check_sections.php @ 4:373c8ecad8b4
deploy to development server
| author | Zoe Hong <zhong@mpiwg-berlin.mpg.de> |
|---|---|
| date | Tue, 24 Mar 2015 11:32:31 +0100 |
| parents | 1f9d2bfe1d13 |
| children | 7e04cbe8b2a0 |
| rev | line source |
|---|---|
| 0 | 1 <?php |
|
4
373c8ecad8b4
deploy to development server
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
1
diff
changeset
|
2 include_once('config/Lib_mb_utf8.php'); |
|
373c8ecad8b4
deploy to development server
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
1
diff
changeset
|
3 include_once('config/config.php'); |
| 0 | 4 include_once('edit_section_db.php'); |
| 5 set_time_limit(0); | |
| 6 ini_set('memory_limit', '-1'); | |
| 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 if (isset($_POST['func'])){ | |
| 21 switch ($_POST['func']) { | |
| 22 case 'UpdateTOCstatus': | |
| 23 UpdateTOCstatus($_POST['id'],$_POST['status']); | |
| 24 break; | |
| 25 | |
| 26 case 'UpdateTOCcomments': | |
| 27 UpdateTOCcomments($_POST['id'],$_POST['notes']); | |
| 28 break; | |
| 29 default: | |
| 30 # code... | |
| 31 break; | |
| 32 } | |
| 33 } | |
| 34 | |
| 35 | |
| 36 function UpdateTOCcomments($id, $notes) { | |
| 37 $query = "UPDATE books SET comments='".$notes."' WHERE id='".$id."'"; | |
| 38 | |
| 39 $result = mysql_query($query); | |
| 40 if (!$result){ | |
| 41 echo mysql_error(); | |
| 42 echo ("Failed during updating books table."); | |
| 43 } | |
| 44 | |
| 45 } | |
| 46 | |
| 47 function UpdateTOCstatus($id, $status) { | |
| 48 | |
| 49 $query = "UPDATE books SET toc_correction='".$status."' WHERE id='".$id."'"; | |
| 50 | |
| 51 $result = mysql_query($query); | |
| 52 if (!$result){ | |
| 53 echo mysql_error(); | |
| 54 echo ("Failed during updating books table."); | |
| 55 } | |
| 56 } | |
| 57 | |
| 58 function checkSections($count){ | |
|
1
1f9d2bfe1d13
handle new section created by user, update sections_index table
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
0
diff
changeset
|
59 /* |
|
1f9d2bfe1d13
handle new section created by user, update sections_index table
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
0
diff
changeset
|
60 $query="(SELECT books.name AS book_name, books.level1, books.level2, books.period, books.id, sections.name, start_page, end_page, COUNT( * ) AS count |
| 0 | 61 FROM sections |
| 62 JOIN books ON sections.books_id = books.id | |
| 63 WHERE NOT EXISTS | |
| 64 (SELECT 1 FROM sections_versions WHERE books_id=sections.books_id) | |
| 65 GROUP BY books_id, start_page, end_page | |
|
1
1f9d2bfe1d13
handle new section created by user, update sections_index table
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
0
diff
changeset
|
66 HAVING COUNT( * ) >=".$count.") |
|
1f9d2bfe1d13
handle new section created by user, update sections_index table
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
0
diff
changeset
|
67 UNION |
|
1f9d2bfe1d13
handle new section created by user, update sections_index table
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
0
diff
changeset
|
68 (SELECT books.name AS book_name, books.level1, books.level2, books.period, books.id, sections_revisions.name, start_page, end_page, COUNT( * ) AS count |
|
1f9d2bfe1d13
handle new section created by user, update sections_index table
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
0
diff
changeset
|
69 FROM sections_revisions |
|
1f9d2bfe1d13
handle new section created by user, update sections_index table
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
0
diff
changeset
|
70 JOIN books ON sections_revisions.books_id = books.id |
|
1f9d2bfe1d13
handle new section created by user, update sections_index table
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
0
diff
changeset
|
71 JOIN ( |
|
1f9d2bfe1d13
handle new section created by user, update sections_index table
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
0
diff
changeset
|
72 SELECT sections_versions.id |
|
1f9d2bfe1d13
handle new section created by user, update sections_index table
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
0
diff
changeset
|
73 FROM sections_versions WHERE sections_versions.version=(SELECT MAX(version) FROM sections_versions) |
|
1f9d2bfe1d13
handle new section created by user, update sections_index table
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
0
diff
changeset
|
74 GROUP BY books_id |
|
1f9d2bfe1d13
handle new section created by user, update sections_index table
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
0
diff
changeset
|
75 ) AS t ON t.id=sections_revisions.versions_id |
|
1f9d2bfe1d13
handle new section created by user, update sections_index table
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
0
diff
changeset
|
76 WHERE deleted=0 |
|
1f9d2bfe1d13
handle new section created by user, update sections_index table
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
0
diff
changeset
|
77 GROUP BY books_id, versions_id,start_page, end_page |
|
1f9d2bfe1d13
handle new section created by user, update sections_index table
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
0
diff
changeset
|
78 HAVING COUNT( * ) >=".$count.") |
|
1f9d2bfe1d13
handle new section created by user, update sections_index table
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
0
diff
changeset
|
79 ORDER BY count DESC "; |
|
1f9d2bfe1d13
handle new section created by user, update sections_index table
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
0
diff
changeset
|
80 */ |
|
4
373c8ecad8b4
deploy to development server
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
1
diff
changeset
|
81 $query="(SELECT books.name AS book_name, books.level1, books.level2, books.period, books.id, sections_index.name, start_page, end_page, COUNT( * ) AS count |
|
1
1f9d2bfe1d13
handle new section created by user, update sections_index table
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
0
diff
changeset
|
82 FROM sections_index |
|
1f9d2bfe1d13
handle new section created by user, update sections_index table
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
0
diff
changeset
|
83 JOIN books ON sections_index.books_id = books.id |
| 0 | 84 WHERE NOT EXISTS |
|
1
1f9d2bfe1d13
handle new section created by user, update sections_index table
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
0
diff
changeset
|
85 (SELECT 1 FROM sections_versions WHERE books_id=sections_index.books_id) |
| 0 | 86 GROUP BY books_id, start_page, end_page |
| 87 HAVING COUNT( * ) >=".$count.") | |
| 88 UNION | |
| 89 (SELECT books.name AS book_name, books.level1, books.level2, books.period, books.id, sections_revisions.name, start_page, end_page, COUNT( * ) AS count | |
| 90 FROM sections_revisions | |
| 91 JOIN books ON sections_revisions.books_id = books.id | |
| 92 JOIN ( | |
| 93 SELECT sections_versions.id | |
| 94 FROM sections_versions WHERE sections_versions.version=(SELECT MAX(version) FROM sections_versions) | |
| 95 GROUP BY books_id | |
| 96 ) AS t ON t.id=sections_revisions.versions_id | |
| 97 WHERE deleted=0 | |
| 98 GROUP BY books_id, versions_id,start_page, end_page | |
| 99 HAVING COUNT( * ) >=".$count.") | |
| 100 ORDER BY count DESC "; | |
| 101 $result = mysql_query($query); | |
|
4
373c8ecad8b4
deploy to development server
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
1
diff
changeset
|
102 if (!$result) { |
|
373c8ecad8b4
deploy to development server
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
1
diff
changeset
|
103 echo mysql_error(); |
|
373c8ecad8b4
deploy to development server
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
1
diff
changeset
|
104 return; |
|
373c8ecad8b4
deploy to development server
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
1
diff
changeset
|
105 } |
| 0 | 106 echo mysql_num_rows($result)." result(s)<br><br>"; |
| 107 echo "<table>"; | |
| 108 echo "<tr>"; | |
| 109 echo "<td class='bookName'>book name<td class='level1'>level1<td class='level2'>level2<td class='period'>period<td class='sectionName'>section name<td class='sectionName'>page<td class='page'>count"; | |
| 110 while ($row = mysql_fetch_assoc($result)) { | |
| 111 $str="<tr>"; | |
| 112 $str.="<td>".$row['book_name']; | |
| 113 $str.="<td>".$row['level1']; | |
| 114 $str.="<td>".$row['level2']; | |
| 115 $str.="<td>".$row['period']; | |
| 116 $str.="<td><a href='tagging_text.php?id=".$row['id']."' target='_blank'>".$row['name']."</a>"; | |
| 117 $str.="<td>p".$row['start_page']."-".$row['end_page']; | |
| 118 $str.="<td><a href='check_sections_details.php?book_id=".$row['id']."&count=".$count."' target='_blank'>".$row['count']."</a>"; | |
| 119 echo $str; | |
| 120 } | |
| 121 echo "</table>"; | |
| 122 } | |
| 123 | |
| 124 | |
| 125 // sorting by the $sort_by parameter | |
| 126 function checkSections_sort_by($sort_by){ | |
| 127 switch ($sort_by) { | |
| 128 case 'time': | |
| 129 $query="(SELECT name AS book_name,level1,level2,period,dynasty,start_year,id,toc_correction,comments FROM books ORDER BY start_year)"; | |
| 130 $result = mysql_query($query); | |
| 131 echo mysql_num_rows($result)." result(s)<br><br>"; | |
| 132 | |
| 133 echo "<form autocomplete='off'>"; | |
| 134 echo "<table>"; | |
| 135 echo "<tr>"; | |
| 136 echo "<td class='bookName'>book name<td class='level1'>level1<td class='level2'>level2 | |
| 137 <td class='period'>period<td class='dynasty'>dynasty<td class='start_year'>start_year | |
| 138 <td class='last_editor'>last_editor<td class='level1'>edit_time | |
| 139 <td> <td> <td> Some notes for the book (optional)"; | |
| 140 while ($row = mysql_fetch_assoc($result)) { | |
| 141 $str="<tr>"; | |
| 142 $str.="<td>".$row['book_name']; | |
| 143 $str.="<td>".$row['level1']; | |
| 144 $str.="<td>".$row['level2']; | |
| 145 $str.="<td>".$row['period']; | |
| 146 $str.="<td>".$row['dynasty']; | |
| 147 $str.="<td>".$row['start_year']; | |
| 148 | |
| 149 $id = $row['id']; | |
| 150 | |
| 151 // query last_editor and last_edit_time | |
| 152 $query_editor = "(SELECT editor, date from sections_versions WHERE books_id='".$row['id']."' ORDER BY version DESC)"; | |
| 153 $row_sections_versions = mysql_fetch_assoc(mysql_query($query_editor)); // the first one | |
| 154 $last_editor = $row_sections_versions['editor']; | |
| 155 $last_edit_time = $row_sections_versions['date']; | |
| 156 $str.="<td>".$last_editor; | |
| 157 $str.="<td>".$last_edit_time; | |
| 158 | |
| 159 $str.="<td><a href='check_sections_details.php?book_id=".$row['id']."&count=100"."' target='_blank'>"."Edit"."</a>"; | |
| 160 | |
| 161 switch ($row['toc_correction']) { | |
| 162 | |
| 163 case '0': | |
| 164 $str.="<td> <input type='checkbox' name='".$id."' onchange='toc_status(this)' /> Finished"; | |
| 165 break; | |
| 166 | |
| 167 case '1': | |
| 168 $str.="<td> <input type='checkbox' name='".$id."' onchange='toc_status(this)' checked='true' /> Finished"; | |
| 169 break; | |
| 170 default: | |
| 171 break; | |
| 172 } | |
| 173 // comments for book | |
| 174 $comments = $row['comments']; | |
| 175 if ($comments != "") { | |
| 176 $str.="<td> <textarea rows='1' cols='30' name='".$id."' maxlength='100' onchange='toc_comments(this)'>".$comments."</textarea>"; | |
| 177 } else { | |
| 178 $str.="<td> <textarea rows='1' cols='30' name='".$id."' maxlength='100' onchange='toc_comments(this)'></textarea>"; | |
| 179 } | |
| 180 echo $str; | |
| 181 | |
| 182 } | |
| 183 echo "</table>"; | |
| 184 echo "</form>"; | |
| 185 break; | |
| 186 | |
| 187 default: | |
| 188 # code... | |
| 189 echo "no sorting parameter set"; | |
| 190 break; | |
| 191 } | |
| 192 } | |
| 193 | |
| 194 ?> | |
| 195 | |
| 196 | |
| 197 | |
| 198 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> | |
| 199 <html> | |
| 200 <head> | |
| 201 <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/> | |
|
4
373c8ecad8b4
deploy to development server
Zoe Hong <zhong@mpiwg-berlin.mpg.de>
parents:
1
diff
changeset
|
202 <!-- <link href="css/search.css" type="text/css" rel="stylesheet"/> --> |
| 0 | 203 <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.6.2/jquery.min.js" type="text/javascript"></script> |
| 204 <!--<script src="js/check_sections.js" charset="utf-8"></script>!--> | |
| 205 </head> | |
| 206 <body> | |
| 207 <form action='check_sections.php' method='GET'> | |
| 208 find sections overlapping more than <input type="text" name="count"> other sections | |
| 209 <input type="submit" name="find" value="find"> | |
| 210 </form> | |
| 211 <br> | |
| 212 <div id="search_result"> | |
| 213 <?php | |
| 214 if(isset($_GET['find']) && $_GET['count']!='' && is_numeric($_GET['count'])){ | |
| 215 checkSections($_GET['count']); | |
| 216 } | |
| 217 else if(isset($_GET['sort_by'])){ | |
| 218 checkSections_sort_by($_GET['sort_by']); | |
| 219 } | |
| 220 ?> | |
| 221 </div> | |
| 222 </body> | |
| 223 | |
| 224 | |
| 225 <script type="text/javascript"> | |
| 226 | |
| 227 function toc_status(t) { | |
| 228 var id = t.name; | |
| 229 var val; | |
| 230 | |
| 231 if (t.checked) { | |
| 232 val = '1'; | |
| 233 } else { | |
| 234 val = '0'; | |
| 235 } | |
| 236 | |
| 237 $.ajax({ | |
| 238 url : './check_sections.php', | |
| 239 async : false, | |
| 240 type : 'POST', | |
| 241 data : 'func=UpdateTOCstatus'+'&id='+id+'&status='+val, | |
| 242 complete: function(){ | |
| 243 switch (val){ | |
| 244 case '0': | |
| 245 alert('You update the status of book to "Not Finished"!'); | |
| 246 break; | |
| 247 case '1': | |
| 248 alert('You update the status of book to "Finished"!'); | |
| 249 break; | |
| 250 } | |
| 251 | |
| 252 }, | |
| 253 error:function() { | |
| 254 console.log("error to update toc status"); | |
| 255 alert('You have NOT updated the status of book. If this issue remains, please contact us. Thanks.'); | |
| 256 } | |
| 257 }); | |
| 258 | |
| 259 } | |
| 260 | |
| 261 function toc_comments(t) { | |
| 262 console.log(t.value); | |
| 263 var text = t.value; | |
| 264 var id = t.name; | |
| 265 | |
| 266 $.ajax({ | |
| 267 url: './check_sections.php', | |
| 268 async : false, | |
| 269 type: 'POST', | |
| 270 data: 'func=UpdateTOCcomments'+'&id='+id+'¬es='+text, | |
| 271 complete: function(){ | |
| 272 console.log('You update the comments of book!'); | |
| 273 }, | |
| 274 error:function() { | |
| 275 console.log("error to update toc comments"); | |
| 276 alert('You have NOT updated the status of book. If this issue remains, please contact us. Thanks.'); | |
| 277 } | |
| 278 }); | |
| 279 | |
| 280 } | |
| 281 </script> | |
| 282 | |
| 283 | |
| 284 | |
| 285 </html> | |
| 286 | |
| 287 <?php | |
| 288 ?> |