Mercurial > hg > LGToc
annotate check_sections.php @ 1:1f9d2bfe1d13
handle new section created by user, update sections_index table
| author | Zoe Hong <zhong@mpiwg-berlin.mpg.de> |
|---|---|
| date | Thu, 19 Mar 2015 18:36:21 +0100 |
| parents | 723a162b6627 |
| children | 373c8ecad8b4 |
| rev | line source |
|---|---|
| 0 | 1 <?php |
| 2 include_once('Lib_mb_utf8.php'); | |
| 3 include_once('config.php'); | |
| 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 */ |
| 0 | 81 $query="(SELECT books.name AS book_name, books.level1, books.level2, books.period, books.id, sections.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); | |
| 102 echo mysql_num_rows($result)." result(s)<br><br>"; | |
| 103 echo "<table>"; | |
| 104 echo "<tr>"; | |
| 105 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"; | |
| 106 while ($row = mysql_fetch_assoc($result)) { | |
| 107 $str="<tr>"; | |
| 108 $str.="<td>".$row['book_name']; | |
| 109 $str.="<td>".$row['level1']; | |
| 110 $str.="<td>".$row['level2']; | |
| 111 $str.="<td>".$row['period']; | |
| 112 $str.="<td><a href='tagging_text.php?id=".$row['id']."' target='_blank'>".$row['name']."</a>"; | |
| 113 $str.="<td>p".$row['start_page']."-".$row['end_page']; | |
| 114 $str.="<td><a href='check_sections_details.php?book_id=".$row['id']."&count=".$count."' target='_blank'>".$row['count']."</a>"; | |
| 115 echo $str; | |
| 116 } | |
| 117 echo "</table>"; | |
| 118 } | |
| 119 | |
| 120 | |
| 121 // sorting by the $sort_by parameter | |
| 122 function checkSections_sort_by($sort_by){ | |
| 123 switch ($sort_by) { | |
| 124 case 'time': | |
| 125 $query="(SELECT name AS book_name,level1,level2,period,dynasty,start_year,id,toc_correction,comments FROM books ORDER BY start_year)"; | |
| 126 $result = mysql_query($query); | |
| 127 echo mysql_num_rows($result)." result(s)<br><br>"; | |
| 128 | |
| 129 echo "<form autocomplete='off'>"; | |
| 130 echo "<table>"; | |
| 131 echo "<tr>"; | |
| 132 echo "<td class='bookName'>book name<td class='level1'>level1<td class='level2'>level2 | |
| 133 <td class='period'>period<td class='dynasty'>dynasty<td class='start_year'>start_year | |
| 134 <td class='last_editor'>last_editor<td class='level1'>edit_time | |
| 135 <td> <td> <td> Some notes for the book (optional)"; | |
| 136 while ($row = mysql_fetch_assoc($result)) { | |
| 137 $str="<tr>"; | |
| 138 $str.="<td>".$row['book_name']; | |
| 139 $str.="<td>".$row['level1']; | |
| 140 $str.="<td>".$row['level2']; | |
| 141 $str.="<td>".$row['period']; | |
| 142 $str.="<td>".$row['dynasty']; | |
| 143 $str.="<td>".$row['start_year']; | |
| 144 | |
| 145 $id = $row['id']; | |
| 146 | |
| 147 // query last_editor and last_edit_time | |
| 148 $query_editor = "(SELECT editor, date from sections_versions WHERE books_id='".$row['id']."' ORDER BY version DESC)"; | |
| 149 $row_sections_versions = mysql_fetch_assoc(mysql_query($query_editor)); // the first one | |
| 150 $last_editor = $row_sections_versions['editor']; | |
| 151 $last_edit_time = $row_sections_versions['date']; | |
| 152 $str.="<td>".$last_editor; | |
| 153 $str.="<td>".$last_edit_time; | |
| 154 | |
| 155 $str.="<td><a href='check_sections_details.php?book_id=".$row['id']."&count=100"."' target='_blank'>"."Edit"."</a>"; | |
| 156 | |
| 157 switch ($row['toc_correction']) { | |
| 158 | |
| 159 case '0': | |
| 160 $str.="<td> <input type='checkbox' name='".$id."' onchange='toc_status(this)' /> Finished"; | |
| 161 break; | |
| 162 | |
| 163 case '1': | |
| 164 $str.="<td> <input type='checkbox' name='".$id."' onchange='toc_status(this)' checked='true' /> Finished"; | |
| 165 break; | |
| 166 default: | |
| 167 break; | |
| 168 } | |
| 169 // comments for book | |
| 170 $comments = $row['comments']; | |
| 171 if ($comments != "") { | |
| 172 $str.="<td> <textarea rows='1' cols='30' name='".$id."' maxlength='100' onchange='toc_comments(this)'>".$comments."</textarea>"; | |
| 173 } else { | |
| 174 $str.="<td> <textarea rows='1' cols='30' name='".$id."' maxlength='100' onchange='toc_comments(this)'></textarea>"; | |
| 175 } | |
| 176 echo $str; | |
| 177 | |
| 178 } | |
| 179 echo "</table>"; | |
| 180 echo "</form>"; | |
| 181 break; | |
| 182 | |
| 183 default: | |
| 184 # code... | |
| 185 echo "no sorting parameter set"; | |
| 186 break; | |
| 187 } | |
| 188 } | |
| 189 | |
| 190 ?> | |
| 191 | |
| 192 | |
| 193 | |
| 194 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> | |
| 195 <html> | |
| 196 <head> | |
| 197 <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/> | |
| 198 <link href="css/search.css" type="text/css" rel="stylesheet"/> | |
| 199 <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.6.2/jquery.min.js" type="text/javascript"></script> | |
| 200 <!--<script src="js/check_sections.js" charset="utf-8"></script>!--> | |
| 201 </head> | |
| 202 <body> | |
| 203 <form action='check_sections.php' method='GET'> | |
| 204 find sections overlapping more than <input type="text" name="count"> other sections | |
| 205 <input type="submit" name="find" value="find"> | |
| 206 </form> | |
| 207 <br> | |
| 208 <div id="search_result"> | |
| 209 <?php | |
| 210 if(isset($_GET['find']) && $_GET['count']!='' && is_numeric($_GET['count'])){ | |
| 211 checkSections($_GET['count']); | |
| 212 } | |
| 213 else if(isset($_GET['sort_by'])){ | |
| 214 checkSections_sort_by($_GET['sort_by']); | |
| 215 } | |
| 216 ?> | |
| 217 </div> | |
| 218 </body> | |
| 219 | |
| 220 | |
| 221 <script type="text/javascript"> | |
| 222 | |
| 223 function toc_status(t) { | |
| 224 var id = t.name; | |
| 225 var val; | |
| 226 | |
| 227 if (t.checked) { | |
| 228 val = '1'; | |
| 229 } else { | |
| 230 val = '0'; | |
| 231 } | |
| 232 | |
| 233 $.ajax({ | |
| 234 url : './check_sections.php', | |
| 235 async : false, | |
| 236 type : 'POST', | |
| 237 data : 'func=UpdateTOCstatus'+'&id='+id+'&status='+val, | |
| 238 complete: function(){ | |
| 239 switch (val){ | |
| 240 case '0': | |
| 241 alert('You update the status of book to "Not Finished"!'); | |
| 242 break; | |
| 243 case '1': | |
| 244 alert('You update the status of book to "Finished"!'); | |
| 245 break; | |
| 246 } | |
| 247 | |
| 248 }, | |
| 249 error:function() { | |
| 250 console.log("error to update toc status"); | |
| 251 alert('You have NOT updated the status of book. If this issue remains, please contact us. Thanks.'); | |
| 252 } | |
| 253 }); | |
| 254 | |
| 255 } | |
| 256 | |
| 257 function toc_comments(t) { | |
| 258 console.log(t.value); | |
| 259 var text = t.value; | |
| 260 var id = t.name; | |
| 261 | |
| 262 $.ajax({ | |
| 263 url: './check_sections.php', | |
| 264 async : false, | |
| 265 type: 'POST', | |
| 266 data: 'func=UpdateTOCcomments'+'&id='+id+'¬es='+text, | |
| 267 complete: function(){ | |
| 268 console.log('You update the comments of book!'); | |
| 269 }, | |
| 270 error:function() { | |
| 271 console.log("error to update toc comments"); | |
| 272 alert('You have NOT updated the status of book. If this issue remains, please contact us. Thanks.'); | |
| 273 } | |
| 274 }); | |
| 275 | |
| 276 } | |
| 277 </script> | |
| 278 | |
| 279 | |
| 280 | |
| 281 </html> | |
| 282 | |
| 283 <?php | |
| 284 ?> |