Mercurial > hg > LGToc
changeset 0:723a162b6627
first commit
author | Zoe Hong <zhong@mpiwg-berlin.mpg.de> |
---|---|
date | Thu, 19 Mar 2015 15:06:34 +0100 |
parents | |
children | 1f9d2bfe1d13 |
files | check_sections.php check_sections_details.php config.php css/check_sections.css edit_section_db.php js/check_sections_details.js |
diffstat | 6 files changed, 1437 insertions(+), 0 deletions(-) [+] |
line wrap: on
line diff
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/check_sections.php Thu Mar 19 15:06:34 2015 +0100 @@ -0,0 +1,271 @@ +<?php +include_once('Lib_mb_utf8.php'); +include_once('config.php'); +include_once('edit_section_db.php'); +set_time_limit(0); +ini_set('memory_limit', '-1'); + +$link_mysql = mysql_connect($mysql_server, $mysql_user, $mysql_password); +mysql_query("SET NAMES utf8"); + +if (!$link_mysql) { + die('Could not connect: ' . mysql_error()); +} + +$db_selected = mysql_select_db($mysql_database, $link_mysql); +if (!$db_selected) { + die ('Can\'t use foo : ' . mysql_error()); +} + +if (isset($_POST['func'])){ + switch ($_POST['func']) { + case 'UpdateTOCstatus': + UpdateTOCstatus($_POST['id'],$_POST['status']); + break; + + case 'UpdateTOCcomments': + UpdateTOCcomments($_POST['id'],$_POST['notes']); + break; + default: + # code... + break; + } +} + + +function UpdateTOCcomments($id, $notes) { + $query = "UPDATE books SET comments='".$notes."' WHERE id='".$id."'"; + + $result = mysql_query($query); + if (!$result){ + echo mysql_error(); + echo ("Failed during updating books table."); + } + +} + +function UpdateTOCstatus($id, $status) { + + $query = "UPDATE books SET toc_correction='".$status."' WHERE id='".$id."'"; + + $result = mysql_query($query); + if (!$result){ + echo mysql_error(); + echo ("Failed during updating books table."); + } +} + +function checkSections($count){ + +/* $query="SELECT books.name AS book_name, books.level1, books.level2, books.period, books.id, sections.name, start_page, end_page, COUNT( * ) AS count + FROM sections + JOIN books ON sections.books_id = books.id + WHERE NOT EXISTS + (SELECT 1 FROM sections_versions WHERE books_id=sections.books_id) + GROUP BY books_id, start_page, end_page + HAVING COUNT( * ) >=".$count." + ORDER BY COUNT( * ) DESC ";*/ + $query="(SELECT books.name AS book_name, books.level1, books.level2, books.period, books.id, sections.name, start_page, end_page, COUNT( * ) AS count + FROM sections + JOIN books ON sections.books_id = books.id + WHERE NOT EXISTS + (SELECT 1 FROM sections_versions WHERE books_id=sections.books_id) + GROUP BY books_id, start_page, end_page + HAVING COUNT( * ) >=".$count.") + UNION + (SELECT books.name AS book_name, books.level1, books.level2, books.period, books.id, sections_revisions.name, start_page, end_page, COUNT( * ) AS count + FROM sections_revisions + JOIN books ON sections_revisions.books_id = books.id + JOIN ( + SELECT sections_versions.id + FROM sections_versions WHERE sections_versions.version=(SELECT MAX(version) FROM sections_versions) + GROUP BY books_id + ) AS t ON t.id=sections_revisions.versions_id + WHERE deleted=0 + GROUP BY books_id, versions_id,start_page, end_page + HAVING COUNT( * ) >=".$count.") + ORDER BY count DESC "; + $result = mysql_query($query); + echo mysql_num_rows($result)." result(s)<br><br>"; + echo "<table>"; + echo "<tr>"; + 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"; + while ($row = mysql_fetch_assoc($result)) { + $str="<tr>"; + $str.="<td>".$row['book_name']; + $str.="<td>".$row['level1']; + $str.="<td>".$row['level2']; + $str.="<td>".$row['period']; + $str.="<td><a href='tagging_text.php?id=".$row['id']."' target='_blank'>".$row['name']."</a>"; + $str.="<td>p".$row['start_page']."-".$row['end_page']; + $str.="<td><a href='check_sections_details.php?book_id=".$row['id']."&count=".$count."' target='_blank'>".$row['count']."</a>"; + echo $str; + } + echo "</table>"; +} + + +// sorting by the $sort_by parameter +function checkSections_sort_by($sort_by){ + switch ($sort_by) { + case 'time': + $query="(SELECT name AS book_name,level1,level2,period,dynasty,start_year,id,toc_correction,comments FROM books ORDER BY start_year)"; + $result = mysql_query($query); + echo mysql_num_rows($result)." result(s)<br><br>"; + + echo "<form autocomplete='off'>"; + echo "<table>"; + echo "<tr>"; + echo "<td class='bookName'>book name<td class='level1'>level1<td class='level2'>level2 + <td class='period'>period<td class='dynasty'>dynasty<td class='start_year'>start_year + <td class='last_editor'>last_editor<td class='level1'>edit_time + <td> <td> <td> Some notes for the book (optional)"; + while ($row = mysql_fetch_assoc($result)) { + $str="<tr>"; + $str.="<td>".$row['book_name']; + $str.="<td>".$row['level1']; + $str.="<td>".$row['level2']; + $str.="<td>".$row['period']; + $str.="<td>".$row['dynasty']; + $str.="<td>".$row['start_year']; + + $id = $row['id']; + + // query last_editor and last_edit_time + $query_editor = "(SELECT editor, date from sections_versions WHERE books_id='".$row['id']."' ORDER BY version DESC)"; + $row_sections_versions = mysql_fetch_assoc(mysql_query($query_editor)); // the first one + $last_editor = $row_sections_versions['editor']; + $last_edit_time = $row_sections_versions['date']; + $str.="<td>".$last_editor; + $str.="<td>".$last_edit_time; + + $str.="<td><a href='check_sections_details.php?book_id=".$row['id']."&count=100"."' target='_blank'>"."Edit"."</a>"; + + switch ($row['toc_correction']) { + + case '0': + $str.="<td> <input type='checkbox' name='".$id."' onchange='toc_status(this)' /> Finished"; + break; + + case '1': + $str.="<td> <input type='checkbox' name='".$id."' onchange='toc_status(this)' checked='true' /> Finished"; + break; + default: + break; + } + // comments for book + $comments = $row['comments']; + if ($comments != "") { + $str.="<td> <textarea rows='1' cols='30' name='".$id."' maxlength='100' onchange='toc_comments(this)'>".$comments."</textarea>"; + } else { + $str.="<td> <textarea rows='1' cols='30' name='".$id."' maxlength='100' onchange='toc_comments(this)'></textarea>"; + } + echo $str; + + } + echo "</table>"; + echo "</form>"; + break; + + default: + # code... + echo "no sorting parameter set"; + break; + } +} + +?> + + + +<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> +<html> + <head> + <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/> + <link href="css/search.css" type="text/css" rel="stylesheet"/> + <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.6.2/jquery.min.js" type="text/javascript"></script> + <!--<script src="js/check_sections.js" charset="utf-8"></script>!--> + </head> + <body> + <form action='check_sections.php' method='GET'> + find sections overlapping more than <input type="text" name="count"> other sections + <input type="submit" name="find" value="find"> + </form> + <br> + <div id="search_result"> + <?php + if(isset($_GET['find']) && $_GET['count']!='' && is_numeric($_GET['count'])){ + checkSections($_GET['count']); + } + else if(isset($_GET['sort_by'])){ + checkSections_sort_by($_GET['sort_by']); + } + ?> + </div> + </body> + + + <script type="text/javascript"> + + function toc_status(t) { + var id = t.name; + var val; + + if (t.checked) { + val = '1'; + } else { + val = '0'; + } + + $.ajax({ + url : './check_sections.php', + async : false, + type : 'POST', + data : 'func=UpdateTOCstatus'+'&id='+id+'&status='+val, + complete: function(){ + switch (val){ + case '0': + alert('You update the status of book to "Not Finished"!'); + break; + case '1': + alert('You update the status of book to "Finished"!'); + break; + } + + }, + error:function() { + console.log("error to update toc status"); + alert('You have NOT updated the status of book. If this issue remains, please contact us. Thanks.'); + } + }); + + } + + function toc_comments(t) { + console.log(t.value); + var text = t.value; + var id = t.name; + + $.ajax({ + url: './check_sections.php', + async : false, + type: 'POST', + data: 'func=UpdateTOCcomments'+'&id='+id+'¬es='+text, + complete: function(){ + console.log('You update the comments of book!'); + }, + error:function() { + console.log("error to update toc comments"); + alert('You have NOT updated the status of book. If this issue remains, please contact us. Thanks.'); + } + }); + + } + </script> + + + +</html> + +<?php +?>
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/check_sections_details.php Thu Mar 19 15:06:34 2015 +0100 @@ -0,0 +1,206 @@ +<?php +include_once('Lib_mb_utf8.php'); +include_once('config.php'); +include_once('edit_section_db.php'); +set_time_limit(0); +ini_set('memory_limit', '-1'); + +$link_mysql = mysql_connect($mysql_server, $mysql_user, $mysql_password); +mysql_query("SET NAMES utf8"); + +if (!$link_mysql) { + die('Could not connect: ' . mysql_error()); +} + +$db_selected = mysql_select_db($mysql_database, $link_mysql); +if (!$db_selected) { + die ('Can\'t use foo : ' . mysql_error()); +} +function getBookInfo($bookId){ + $query="SELECT * FROM books WHERE id=".$bookId; + $result=mysql_query($query); + $bookInfo=mysql_fetch_assoc($result); + return $bookInfo; +} +function printBookInfo($bookInfo){ + $str="<table>"; + $str.="<tr><td class='bookId'>book id + <td class='bookName'>book name + <td class='level1'>level1 + <td class='level2'>level2 + <td class='period'>period + <td class='bookId'>pages + <td class='bookId'>version + <td class='bookName'>editor + <td class='bookName'>date"; + $str.="<tr>"; + $str.="<td>".$bookInfo['id']; + $str.="<td>".$bookInfo['name']; + $str.="<td>".$bookInfo['level1']; + $str.="<td>".$bookInfo['level2']; + $str.="<td>".$bookInfo['period']; + $str.="<td>".$bookInfo['line']; + $str.="<td>".$bookInfo['version']; + $str.="<td>".$bookInfo['editor']; + $str.="<td>".$bookInfo['date']; + $str.="</table>"; + echo $str; +} +function printSectionArray($sectionArray,$missingPageArray){ + $str="<div id='bookContainer'>"; + echo $str; + $i=0; + foreach($sectionArray as $idx=>$row){ + /* + $flag=""; + if($i!=sizeof($pageArray) && $pageArray[$i]['start_page']==$row['start_page'] && $pageArray[$i]['end_page']==$row['end_page']){ + $flag="overlapper"; + $i++; + } + if($i!=0 && $pageArray[$i-1]['start_page']==$row['start_page'] && $pageArray[$i-1]['end_page']==$row['end_page']){ + $flag="overlapper"; + }*/ + if(!($idx!=0 && $sectionArray[$idx-1]['start_page']==$row['start_page'] && $sectionArray[$idx-1]['end_page']==$row['end_page']) + && $i<sizeof($missingPageArray) && $missingPageArray[$i]['end_page']==$row['start_page']){ + $str="<div class='missingPageContainer'><div class='missingPage'>Missing pages: ".$missingPageArray[$i]['start_page']." - ".$missingPageArray[$i]['end_page']."</div></div>"; + //$str.="<br>"; + $i++; + echo $str; + } + + $str="<div class='entry level".$row['level']." ".$row['flag']." ".$row['deleted']."'>"; + $str.="<div class='hiddenInfo'>"; + $str.="<div class='id'>".$row['id']."</div>"; + $str.="<div class='booksId'>".sprintf("%05d",$row['books_id'])."</div>"; + $str.="<div class='splitFrom'>".$row['split_from']."</div>"; + $str.="<div class='sectionAfter'>".$row['section_after']."</div>"; + $str.="</div>";//end of hiddenInfo + $str.="<div class='sequence'>".($idx+1)."</div>"; + $str.="<div class='name'>".$row['name']."</div>"; + $str.="<div class='page'>"; + $str.="<div class='startPage'>".$row['start_page']."</div>"; + $str.=" - "; + $str.="<div class='endPage'>".$row['end_page']."</div>"; + $str.="</div>";//end of page + $str.="</div>"; //end of class entry + echo $str; + + } + if($i<sizeof($missingPageArray)){ + $str="<div class='missingPage'>Missing pages: ".$missingPageArray[$i]['start_page']." - ".$missingPageArray[$i]['end_page']."</div>"; + echo $str; + } + $str="</div>"; + echo $str; +} + +?> + +<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> +<html> + <head> + <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/> + <link href="https://code.jquery.com/ui/1.10.4/themes/smoothness/jquery-ui.css" type="text/css" rel="stylesheet"/> + <link href="css/check_sections.css" type="text/css" rel="stylesheet"/> + <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.6.2/jquery.min.js" type="text/javascript"></script> + <script src="js/check_sections_details.js" type="text/javascript"></script> + <script src="https://code.jquery.com/ui/1.10.4/jquery-ui.js" type="text/javascript"></script> + <!--<script src="js/check_sections.js" charset="utf-8"></script>!--> + </head> + <body> + <div id="loading">...</div> + <div id="container"> + <?php + $version=0; + $editor=""; + $date=""; + if(isset($_GET['book_id']) && isset($_GET['count']) && is_numeric($_GET['count'])){ + $bookId=$_GET['book_id']; + $count=$_GET['count']; + $bookInfo=getBookInfo($bookId); + $info=checkSectionInfo($bookId); + $version=$info['version']; + $editor=$info['editor']; + $date=$info['date']; + $bookInfo=array_merge($bookInfo,["version"=>$version,"editor"=>$editor,"date"=>$date]); + printBookInfo($bookInfo); + //echo "version: ".$version."<br>last editor: ".$editor; + $missingPageArray=checkMissingPage($bookInfo,$info); + ?> + + <table> + <thead>Show editing records: + <button id="show_all">Show all</button> + <button id="close">Close</button> + </thead> + <tbody> + <tr> + <td id="editor_th"></td> + <td id="date_th"></td> + </tr> + <tr> + <td><div id="editor"></div></td> + <td><div id="date"></div></td> + </tr> + </tbody> + </table> + + + + <div id="searchResults"> + <?php + $sectionArray=getSectionArray($bookId,$count,$info); + printSectionArray($sectionArray,$missingPageArray); + }else{ + echo "Cannot process the data due to incomplete input."; + } + ?> + </div> + </div> + <div id="panel"></div> + <script>var version=<?php echo $version;?>;</script> + + <script type="text/javascript"> + + document.getElementById("show_all").onclick = function(){showAll()}; + + function showAll() { + document.getElementById("editor_th").innerHTML = "Editor(version)"; + document.getElementById("date_th").innerHTML = "Date/Time"; + + <?php + $editors = ""; + $editing_time = ""; + + $query_editor = "(SELECT editor, date, version from sections_versions WHERE books_id='".$bookId."' ORDER BY version DESC)"; + $result = mysql_query($query_editor); + + while ($row = mysql_fetch_assoc($result)) { + $editor = $row['editor']; + $edit_time = $row['date']; + $version = $row['version']; + ?> + document.getElementById("editor").innerHTML += '<?php echo $editor; ?>' +'('+'<?php echo $version;?>' +')<br>'; + document.getElementById("date").innerHTML += '<?php echo $edit_time; ?>' + '<br>'; + <?php + + } + ?> + + } + + document.getElementById("close").onclick = function(){closeAll()}; + + function closeAll() { + document.getElementById("editor_th").innerHTML = ""; + document.getElementById("date_th").innerHTML = ""; + document.getElementById("editor").innerHTML = ""; + document.getElementById("date").innerHTML = ""; + } + </script> + + + </body> + +</html> +
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/config.php Thu Mar 19 15:06:34 2015 +0100 @@ -0,0 +1,26 @@ +<?php + +$at_local = true; + +if (!$at_local) { + // host at localgazetteers-dev server + $systemNAME = "interface"; + $mysql_database = "Gazetteer"; + $mysql_server = "localhost"; + $mysql_user = "root"; + $mysql_password = "admin"; + $system_root_url = "http://localgazetteers-dev/extraction-interface"; + $lgserver_url = "http://localgazetteers-dev/LGServer/"; + +} else { + // localhost + $systemNAME = "interface"; + $mysql_database = "Gazetteers"; + $mysql_server = "localhost"; + $mysql_user = "root"; + $mysql_password = "root"; + $system_root_url = "http://localhost:1080/localgazetteers-dev/extraction-interface"; + $lgserver_url = "http://localgazetteers-dev/LGServer/"; +} + +?>
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/css/check_sections.css Thu Mar 19 15:06:34 2015 +0100 @@ -0,0 +1,269 @@ +body{ + font-family:Helvetica,"Adobe 宋體"; + font-size:18px; + padding:0; + margin:0; +} +#loading{ + position:fixed; + z-index:2000; + background:rgba(0,0,0,0.5); + color:#fff; + width:100vw; + height:100vh; + padding:300px 0; + text-align:center; + vertical-align:middle; + font-size:20px; + line-height:20px; +} +#container{ + padding:10px; +} +#searchResults{ + margin:30px 0; +} +#bookContainer{ + display:table; + border-collapse: collapse; + border-spacing:10px; +} +.missingPageContainer{ + font-style:italic; + display:table-row; +} +.missingPage{ + border:1px dashed #000; + width:400px; + text-align:center; + padding:10px; + margin:20px 0; +} +#bookContainer td{ + padding:10px; +} +table{ + margin-bottom:20px; +} +td.level1, td.level2,td.level3, td.period{ + width:70px; +} +div.entry{ + display:table-row; + vertical-align:middle; + cursor:move; +} +div.entry .cell{ + display:table-cell; +} +div.level1 .cell{ + vertical-align:middle; + padding:15px 0px 5px 0px; +} +div.level2 .cell{ + padding:5px 0px; +} +div.level3 .cell{ + padding:10px 0px; +} +div.entry .sequence{ + display:inline-block; + width:36px; + text-align:right; + padding-left:10px; + padding-right:10px; + font-size:14px; + margin-right:30px; + border-right:1px #888 solid; + color:#888; +} +div.entry .name{ + display:inline-block; + width:350px; + margin-left:20px; + min-height:20px; +} +div.entry .page{ + display:inline-block; + padding:0px; +} +div.startPage{ + display:inline-block; + text-align:center; + width:50px; + min-height:20px; +} +div.endPage{ + display:inline-block; + text-align:center; + width:50px; + min-height:20px; +} +div.level1{ + border-bottom:1px solid #000; +} +div.level2{ +} +div.level2 .name{ + text-indent:40px; +} +div.level3{ +} +div.level3 .name{ + text-indent:80px; +} +div.overlapper{ + background:#F5A9A9; +} +div.entry .editLevel{ + display:inline-block; + margin:0 5px; +} +div.entry .deleteEntry{ + display:inline-block; + padding-right:5px; + position:relative; +} +div.deleteEntry input{ + width:60px; + text-align:center; +} +#deleteRange{ + position:absolute; + z-index:102; + background:#000; + color:#fff; + -webkit-border-radius: 10px; + -moz-border-radius: 10px; + border-radius: 10px; + padding:10px; + width:260px; + font-size:16px; + line-height:24px; + top:-15px; + left:65px +} +#deleteRange .deleteFrom,#deleteRange .deleteTo,#deleteRange .deleteRangeButton{ + display:inline-block; +} +#deleteRange .deleteButton{ + margin-left:10px; +} +div.deleted{ + opacity:0.2; +} +div.page input[type="text"]{ + width:45px; + height:17px; + text-align:center; +} +input{ + margin:0; + padding:0; +} +input[type="text"]{ + -webkit-border-radius: 4px; + -moz-border-radius: 4px; + border-radius: 4px; + border:1px #888 solid; + font-size:16px; + font-family:inherit; +} +input[type="button"]{ + font-size:20px; + font-family:inherit; +} +#panel{ + position:fixed; + top:30px; + right:0px; + font-size:16px; + line-height:24px; +} +#saveBook,#configure{ + background:#000; + color:#fff; + padding:15px; + -webkit-border-top-left-radius: 10px; + -webkit-border-bottom-left-radius: 10px; + -moz-border-radius-topleft: 10px; + -moz-border-radius-bottomleft: 10px; + border-top-left-radius: 10px; + border-bottom-left-radius: 10px; + vertical-align:middle; +} +#configure{ + +} +#configure input[type="checkbox"]{ + margin-right:5px; + -ms-transform: scale(1.2); /* IE */ + -moz-transform: scale(1.2); /* FF */ + -webkit-transform: scale(1.2); /* Safari and Chrome */ + -o-transform: scale(1.2); /* Opera */ + position:relative; + top:-2px; + *overflow:hidden; +} +#saveBook{ + margin-top:10px; + text-align:right; +} +#saveBook .label{ + display:block; + text-align:left; + margin-bottom:10px; +} +#saveBook .editor{ + display:block; + margin:4px 0; +} +#saveBook .save{ +} +.hovered{ + background:#ddd !important; +} +div.entry .insert{ + display:inline-block; + color:#fff; + text-align:center; + vertical-align:center; + position:relative; +} +.insertIcon{ + display:inline-block; + margin: auto 5px; + background:#444; + padding:5px 7px; + -webkit-border-radius: 5px; + -moz-border-radius: 5px; + border-radius: 5px; + cursor:pointer; + font-size:16px; + line-height:16px; +} +.insertOption{ + position:absolute; + left:33px; + width:190px; + top:-15px; + text-align:left; + background:#000; + color:#fff; + -webkit-border-radius: 10px; + -moz-border-radius: 10px; + border-radius: 10px; + padding:10px; + font-size:16px; + line-height:24px; +} +.insertBefore{ + text-align:center; + display:inline-block; + cursor:pointer; +} +.insertAfter{ + text-align:center; + display:inline-block; + margin-left:5px; +}
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/edit_section_db.php Thu Mar 19 15:06:34 2015 +0100 @@ -0,0 +1,294 @@ +<?php +include_once('Lib_mb_utf8.php'); +include_once('config.php'); +set_time_limit(0); +ini_set('memory_limit', '-1'); + +$link_mysql = mysql_connect($mysql_server, $mysql_user, $mysql_password); +mysql_query("SET NAMES utf8"); + +if (!$link_mysql) { + die('Could not connect: ' . mysql_error()); +} + +$db_selected = mysql_select_db($mysql_database, $link_mysql); +if (!$db_selected) { + die ('Can\'t use foo : ' . mysql_error()); +} +function checkSectionInfo($bookId){ + $query="SELECT * FROM sections_versions WHERE books_id=".$bookId." ORDER BY version DESC"; + $result=mysql_query($query); + if(mysql_num_rows($result)==0){ + $row['version']=0; + $row['editor']=""; + $row['date']=""; + return $row; + }else{ + $row=mysql_fetch_assoc($result); + return $row; + } +} +function checkMissingPage($bookInfo,$versionInfo){ + $bookId=$bookInfo['id']; + $bookPage=$bookInfo['line']; + if($versionInfo['version']!=0){ + $table="sections_revisions"; + $condition=" AND versions_id=".$versionInfo['id']." AND deleted=0 "; + $versionId=$versionInfo['id']; + }else{ + $table="sections"; + $condition=""; + } + $query="SELECT * + FROM ".$table." + WHERE books_id=".$bookId." ".$condition." + GROUP BY books_id,start_page,end_page + ORDER BY books_id,start_page,level,end_page"; + $result=mysql_query($query); + $lastPage=1; + $i=0; + $pageArray=array(); + while($row=mysql_fetch_assoc($result)){ + if($row['start_page']-1>$lastPage){ + $pageArray[$i]['start_page']=$lastPage; + $pageArray[$i]['end_page']=$row['start_page']; + //echo $pageArray[$i]['start_page']." ".$pageArray[$i]['end_page']."<br>"; + $i++; + } + $lastPage=$row['end_page']; + } + if($bookPage>$lastPage){ + $pageArray[$i]['start_page']=$lastPage; + $pageArray[$i]['end_page']=$bookPage; + } + return $pageArray; +} +function getSectionArray($bookId,$count,$versionInfo){ + + $str=""; + if($versionInfo['version']!=0){ + $table="sections_revisions"; + $condition="AND deleted=0 "; + $condition=" AND versions_id=".$versionInfo['id']." AND deleted=0 "; + }else{ + $table="sections"; + $condition=""; + } + + // echo "select from ".$table."\n"; + + + $query="SELECT * + FROM ".$table." + WHERE books_id=".$bookId." ".$condition." + GROUP BY books_id, start_page, end_page + HAVING COUNT( * ) >=".$count." + ORDER BY id ASC"; + /*ORDER BY start_page ASC, level ASC, id ASC,end_page DESC";*/ + $result=mysql_query($query); + + $i=0; + $pageArray=array(); + while($row=mysql_fetch_assoc($result)){ + $pageArray[$i]['start_page']=$row['start_page']; + $pageArray[$i]['end_page']=$row['end_page']; + $i++; + } + if($versionInfo['version']!=0){ + $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"; + }else{ + $query="SELECT * FROM sections WHERE books_id=".$bookId." ORDER BY id ASC"; + } + $result=mysql_query($query); + + $i=0; + $sectionArray=array(); + while($row=mysql_fetch_assoc($result)){ + $flag=""; + if($i!=sizeof($pageArray) && $pageArray[$i]['start_page']==$row['start_page'] && $pageArray[$i]['end_page']==$row['end_page']){ + $flag="overlapper"; + $i++; + } + if($i!=0 && $pageArray[$i-1]['start_page']==$row['start_page'] && $pageArray[$i-1]['end_page']==$row['end_page']){ + $flag="overlapper"; + } + $row['flag']=$flag; + if(!isset($row['deleted'])){ + $row['deleted']=""; + }else{ + if($row['deleted']==0){ + $row['deleted']=""; + }else{ + $row['deleted']="deleted"; + } + } + $sectionArray[]=$row; + } + return $sectionArray; +} +function updateSectionArray($bookId,$sectionArray,$version,$editor){ + $query="INSERT INTO sections_versions (version,editor,date,books_id) VALUES (".($version+1).",'".$editor."',NOW(),'".$bookId."')"; + $result=mysql_query($query); + if(!$result){ + echo json_encode("Failed during inserting sections_version records."); // .mysql_error(); + return; + } + $versionId=mysql_insert_id(); + $query="INSERT INTO sections_revisions (name,books_id,section_after,start_page,end_page,level,split_from,sections_id,versions_id,deleted) VALUES "; + foreach($sectionArray as $idx=>$row){ + $str="("; + $str.="'".$row['name']."',"; + $str.="'".sprintf("%05d",$row['booksId'])."',"; + $str.="'".$row['sectionAfter']."',"; + $str.=$row['startPage'].","; + $str.=$row['endPage'].","; + $str.=$row['level'].","; + $str.=$row['splitFrom'].","; + $str.=$row['id'].","; + $str.=$versionId.","; + $str.=$row['deleted']; + $str.=")"; + if($idx!=sizeof($sectionArray)-1){ + $str.=", "; + } + $query.=$str; + } + $result=mysql_query($query); + if(!$result){ + echo json_encode("Failed during inserting sections_revisions records."); // .mysql_error(); + + // Delete the sections_versions for this user + // delete $versionId in table sections_versions + $query="DELETE FROM sections_versions WHERE id=".$versionId; + $result_deleting=mysql_query($query); + if(!$result_deleting){ + echo json_encode("Failed during deleting wrongly inserted sections_version record."); + } + return; + }else{ + echo json_encode("Succeeded."); + } +} + + +function _select_distinct(){ + /* + $query="SELECT DISTINCT (books_id) FROM sections"; + + $result=mysql_query($query); + if(!$result){ + echo json_encode("Failed during inserting section records."); + return; + }else{ + echo json_encode("Succeeded."); + } + + while($row=mysql_fetch_assoc($result)){ + // echo $row['books_id'], ", "; + } + + echo "section num: ".mysql_num_rows($result); + echo '\n'; + */ + + + //$query="SELECT id FROM books WHERE id NOT IN (SELECT DISTINCT (books_id) FROM sections)"; + $query="SELECT books.id, books.name FROM books LEFT JOIN sections ON books.id=sections.books_id WHERE sections.books_id IS NULL"; + + $result=mysql_query($query); + if(!$result){ + echo json_encode("Failed during querying records."); + return; + } + + echo "missing books num: ".mysql_num_rows($result); + echo '\n'; + + + + +} + + + +function add_missing_books_to_sections(){ + // find missing books + // Run the following line/mysql to find out what books are missing in sections table + // _select_distinct(); + // Or load missing_books_id.csv + if (($missing_books = fopen("intermediate_results/missing_books.csv", "r")) !== FALSE) { + while (($data = fgetcsv($missing_books, ",")) !== FALSE) { + + $bookId = $data[0]; + $name = $data[1]; + + _add_book_to_sections($name, $bookId); + + } + fclose($missing_books); + } + +} + + +function _add_book_to_sections($name, $bookId){ + + /* + // get Auto_increment + $query = mysql_query("SHOW TABLE STATUS WHERE name='sections'"); + if (mysql_num_rows($query)) { + $result = mysql_fetch_assoc($query); + echo $result['Auto_increment']; + } else {//error + //error control here + echo "error in getting Auto_increment"; + } + */ + + // id should be autoincremated + $query="INSERT INTO sections (name,books_id,section_after) VALUES "; + + $str="("; + $str.="'".$name."','"; + $str.=$bookId."',"; + $str.="''"; + $str.=")"; + $query.=$str; + + // echo $name.", ".$bookId."\n"; + + $result=mysql_query($query); + if(!$result){ + echo json_encode("Failed during inserting section records."); + return; + } + // echo mysql_insert_id(); + +} + + +if(isset($_POST['command']) && $_POST['command']!=''){ + $command=$_POST['command']; + if($command=="write"){ + + if(isset($_POST['bookId'])&&isset($_POST['sectionArray'])&&isset($_POST['version'])&&isset($_POST['editor'])&&\ + is_numeric($_POST['bookId']) && sizeof($_POST['sectionArray'])!=0 && is_numeric($_POST['version']) && $_POST['editor']!=""){ + $bookId=$_POST['bookId']; + $sectionArray=$_POST['sectionArray']; + $version=$_POST['version']; + $editor=$_POST['editor']; + + updateSectionArray($bookId,$sectionArray,$version,$editor); + } + else{ + echo json_encode("Editing info not correct."); + + } + } + else{ + echo json_encode("Wrong command."); + return; + } +} + +?>
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/js/check_sections_details.js Thu Mar 19 15:06:34 2015 +0100 @@ -0,0 +1,371 @@ +var saving=0; +$(document).ready(function(){ + initEntry(); + initName(); + initPage(); + initLevel(); + initDelete(); + initInsert(); + initConfigure(); + initSave(version); + $("#loading").hide(); +}); +window.onbeforeunload = function() { + if(saving==0){ + return 'Remember to save your revision.'; + }else{ + } +}; +function initEntry(){ + //create an empty entry by cloning the entry we have, so that when a new entry is to be inserted, we can just clone the empty entry + var clone=$(".entry").first().clone(); + clone.addClass("clone"); + if(clone.hasClass("overlapper")){clone.removeClass('overlapper');} + clone.children(".hiddenInfo").children(".id").html("-1"); + clone.children(".hiddenInfo").children(".splitFrom").html(""); + clone.children(".hiddenInfo").children(".sectionAfter").html(""); + clone.children(".name").html("<input type='text'>"); + clone.children(".page").children(".startPage").html("<input type='text'>"); + clone.children(".page").children(".endPage").html("<input type='text'>"); + clone.children(".editLevel").children("select").val(1); + clone.insertAfter("#searchResults"); + clone.hide(); + $(".entry").hover(function(){ + $(this).addClass("hovered"); + },function(){ + $(this).removeClass("hovered"); + }); + $(".hiddenInfo").hide(); + $(".entry").children().each(function(){ + $(this).addClass("cell");//for layout + }); + + //$("#bookContainer").droppable(); + //drag and drop + $(".entry").draggable(); + $(".entry").droppable({ + drop:function(e,ui){ + $(ui.draggable).insertAfter($(this)); + $(this).mouseleave(); + $(ui.draggable).mouseenter(); + } + }); +} +function dropHandler(e){//for debug + console.log("QQ"); + console.log($(event.target).children("hiddenInfo").html()); +} +function initName(){ + $(".entry .name").click(function(){ + var editMode=false; + var name=$(this).html(); + if($(this).children().length!=0){//if the name element has children, i.e. the input text + editMode=true; + } + if(!editMode){ + $(this).html("<input type='text' value='"+name+"'>"); + $(this).children("input").focus(); + } + }); +} +function initInsert(){ + var insert=$("<div/>").addClass("insert"); + var insertIcon=$("<div/>").addClass("insertIcon").html("+"); + var insertBefore=$("<input type='button'>").addClass("insertBefore").val("insert before"); + var insertAfter=$("<input type='button'>").addClass("insertAfter").val("insert after"); + var insertOption=$("<div/>").addClass("insertOption").append("insert a new section<br>").append(insertBefore).append(insertAfter); + insert.append(insertIcon); + //insert.append(insertBefore); + //insert.append(insertAfter); + $(".entry").append(insert); + insertOption.insertAfter("#searchResults"); + //$(".insertAfter").hide(); + //$(".insertBefore").hide(); + insertOption.hide(); + $(".insert").hover(function(){ + $(this).append($(".insertOption")); + $(".insertOption").show(); + //$(this).children(".insertAfter").show(); + //$(this).children(".insertBefore").show(); + },function(){ + $(".insertOption").hide(); + //$(this).children(".insertAfter").hide(); + //$(this).children(".insertBefore").hide(); + }); + $(".insertAfter").click(function(){ + addEntry($(this).parent().parent().parent(),1); + $(this).parent().hide(); + }); + $(".insertBefore").click(function(){ + addEntry($(this).parent().parent().parent(),0); + $(this).parent().hide(); + }); +} +function addEntry(obj,pos){//obj: the obj next to which we would like to add a new entry, pos: before the obj or after the obj + var clone=$(".clone").clone(true); + clone.removeClass("clone"); + clone.show(); + if(pos==0){ + clone.insertBefore(obj); + //$("<br>").insertAfter(clone); + }else{ + clone.insertAfter(obj); + //$("<br>").insertBefore(clone); + } + $("#searchResults .sequence").each(function(idx){ + $(this).html(idx+1); + }); +} +function initPage(){// init the column start page & end page, so that they turn into input boxes when clicked + //page + $("div.page").click(function(e){ + var startPage=$(this).children(".startPage").html(); + var endPage=$(this).children(".endPage").html(); + var editMode=false; + if($(this).children(".startPage").children().length!=0){ + editMode=true; + } + if(!editMode){ + $(this).children(".startPage").html("<input type='text' value='"+startPage+"'>"); + //$(this).children(".startPage").children("input").focus(); + $(this).children(".endPage").html("<input type='text' value='"+endPage+"'>"); + //$(this).children(".endPage").children("input").focus(); + $(e.target).children("input").focus(); + }else{ + //console.log($(this).parent().children(".editLevel").children().first().val()); + } + }); + +} +function initLevel(){//init the column level, turn it into select element + //level + var levelObj=$("<div/>").addClass("editLevel").append("<select/>"); + $(".entry").append(levelObj); + $(".editLevel select").append("<option value='1'>level 1</option>"); + $(".editLevel select").append("<option value='2'>level 2</option>"); + $(".editLevel select").append("<option value='3'>level 3</option>"); + for(var i=1; i<=3; i++){ + $("div.level"+i+" .editLevel select").val(i); + } + + $(".editLevel select").change(function(){//add classes for text indent, to control layout style + var entryObj=$(this).parent().parent(); + var className=""; + if(entryObj.hasClass("level1")){ + className="level1"; + } + if(entryObj.hasClass("level2")){ + className="level2"; + } + if(entryObj.hasClass("level3")){ + className="level3"; + } + entryObj.removeClass(className); + entryObj.addClass("level"+$(this).val()); + }); +} +function initDelete(){ + //delete + var deleteObj=$("<div/>").addClass("deleteEntry").append("<input type='button' value='delete' class='deleteButton'>"); + $(".entry").append(deleteObj); + $(".deleted .deleteEntry input").val("recover"); + var deleteRangeObj=$("<div/>").attr("id","deleteRange").append("delete entry <br>from "); + var deleteFromObj=$("<input type='text'>").addClass("deleteFrom"); + var deleteToObj=$("<input type='text'>").addClass("deleteTo"); + var deleteButtonObj=$("<input type='button'>").addClass("deleteButton").val("delete"); + deleteRangeObj.append(deleteFromObj).append(" to ").append(deleteToObj).append(deleteButtonObj); + deleteRangeObj.insertAfter($("#searchResults")); + deleteRangeObj.hide(); + $(".deleteEntry .deleteButton").click(function(){ + var entryObj=$(this).parent().parent(); + var buttonVal=$(this).val(); + if(buttonVal=="delete"){ + entryObj.addClass("deleted"); + $(this).val("recover"); + }else{ + entryObj.removeClass("deleted"); + $(this).val("delete"); + } + if($("#deleteRange").is(":visible")){ + deleteRangeObj.insertAfter($("#searchResults")); + $("#deleteRange").hide(); + } + }); + $(".deleteEntry").hover(function(){ + var entryObj=$(this).parent(); + var deleteRangeObj=$("#deleteRange"); + var buttonVal=$(this).children(".deleteButton").val(); + if(buttonVal=="delete"){ + $(this).append(deleteRangeObj); + deleteRangeObj.show(); + deleteRangeObj.children(".deleteFrom").val(entryObj.children(".sequence").html()); + } + },function(){ + var buttonVal=$(this).children(".deleteButton").val(); + var deleteRangeObj=$("#deleteRange"); + if(buttonVal=="delete"){ + deleteRangeObj.children("input:text").val(""); + deleteRangeObj.insertAfter($("#searchResults")); + deleteRangeObj.hide(); + } + + }); + $("#deleteRange .deleteButton").click(function(){ + var from=$("#deleteRange .deleteFrom").val().trim(); + var to=$("#deleteRange .deleteTo").val().trim(); + from=Number(from); + to=Number(to); + if(isNaN(from) || isNaN(to) || to <from){ + return; + } + var i; + for(i=from; i<=to; i++){ + if(i<1){i=1;} + if(i>$(".entry").size()){console.log($(".entry").size());break;} + var entryObj=$(".entry:eq("+(i-1)+")"); + var buttonObj=entryObj.children(".deleteEntry").children(".deleteButton"); + var buttonVal=buttonObj.val(); + if(buttonVal=="delete"){ + buttonObj.click(); + } + $("#deleteRange").children("input:text").val(""); + } + }); +}/* +function isNumeric(str){ + if(str.search(/\D/)>=0){ + console.log("not a number"); + return false; + }else{ + return true; + } +}*/ +function initConfigure(){ + var configureObj=$("<div/>").attr("id","configure"); + var hideDeletedObj=$("<div/>").addClass("hideDeleted").append("<input type='checkbox'>"); + hideDeletedObj.append("hide deleted sections"); + configureObj.append(hideDeletedObj); + $("#panel").append(configureObj); + $(".hideDeleted input:checkbox").change(function(){ + if($(this).is(":checked")){ + $(".deleted").hide(); + //$(".deleted").next().hide(); + }else{ + $(".deleted").show(); + //$(".deleted").next().show(); + } + }); + var href=window.location.href; + var reg=/hideDeletedChecked=[a-zA-Z]+/g; + var hidden=reg.exec(href); + if(hidden!=null){ + hidden=hidden[0].replace("hideDeletedChecked=",""); + if(hidden=="true"){ + //if(hidden.search("true")!=-1){ + $(".hideDeleted input:checkbox").attr("checked","true"); + $(".deleted").hide(); + } + } +} +function initSave(version){ + //save + var saveObj=$("<div/>").attr("id","saveBook"); + var labelObj=$("<div/>").addClass("label").html("Name of the editor:"); + saveObj.append(labelObj); + labelObj.append("<input type='text' class='editor'>"); + saveObj.append("<input type='button' value='save' class='save'>"); + $("#panel").append(saveObj); + $("#saveBook .save").click(function(){ + if($("#saveBook .editor").val()!=""){ + saveBook(version); + }else{ + alert("Please fill in the name of the editor."); + } + }); + var href=window.location.href; + var reg=/editor=[^\?^\&]+/g; + var editor=reg.exec(href); + if(editor!=null){ + editor=editor[0].replace("editor=",""); + editor=editor.replace("%20"," "); + $("#saveBook .editor").val(editor); + } +} +function saveBook(version){ + var sectionArray=collectSectionArray(); + var bookId=$(".entry").first().children(".hiddenInfo").children(".booksId").html(); + var editor=$("#saveBook .editor").val(); + console.log("editor:"+editor); + + var version=version; + $("#loading").show(); + $.post("edit_section_db.php", // use ajax to post to a php then wait for return + {//post data + command:"write", + version:version, + editor:editor, + bookId:bookId, + sectionArray:sectionArray + }, + function(data){//things to do after edit_section_db is done + if(data=="Succeeded."){ + saving=1; + var hideDeletedChecked=$(".hideDeleted input:checkbox").is(":checked");//get the previous setting for checked + var href=window.location.href; + var reg=/count=[0-9^\?]+/g; + var count=reg.exec(href); + reg = /[^\?]+/g; + href = reg.exec(href); + href=href+"?book_id="+bookId+"&"+count+"&hideDeletedChecked="+hideDeletedChecked+"&editor="+editor; + location.href=href; //refresh the page to load the sections saved in db + + }else{ + console.log("db failed. log: ", data); + alert("Saving failed. You've NOT saved your editing."); + alert("Please check your editing again. If the issue remains, please contact us."); + $("#loading").hide(); + + } + }, + "json" + ); +} +function collectSectionArray(){ + var sectionArray=new Array(); + var idx=0; + $("#searchResults .entry").each(function(){//go through all the entries wrapped in the element searchResults + var startPage,endPage,name,deleted="FALSE"; + if($(this).children(".page").children(".startPage").children().length!=0){ //if the value is a input box + startPage=$(this).children(".page").children(".startPage").children().first().val(); + endPage=$(this).children(".page").children(".endPage").children().first().val(); + }else{ // if the value is not an input box (has not been edited) + startPage=$(this).children(".page").children(".startPage").html(); + endPage=$(this).children(".page").children(".endPage").html(); + } + if($(this).children(".name").children().length!=0){ + name=$(this).children(".name").children().first().val(); + }else{ + name=$(this).children(".name").html(); + } + startPage=$.trim(startPage); + endPage=$.trim(endPage); + name=$.trim(name); + if(name=="" || startPage=="" ||endPage==""){ // if any of the fields is blank, dont insert to db (since none of them should be blank) + console.log("empty row: "+name+" -- "+startPage+" -- "+endPage); + return; // "continue" in jquery + } + if($(this).hasClass("deleted")){ deleted="TRUE";} + var id=$(this).children(".hiddenInfo").children(".id").html(); + var booksId=$(this).children(".hiddenInfo").children(".booksId").html(); + var sectionAfter=$(this).children(".hiddenInfo").children(".sectionAfter").html() + var level=$(this).children(".editLevel").children("select").val(); + var splitFrom=$(this).children(".hiddenInfo").children(".splitFrom").html(); + if(splitFrom==""){splitFrom="NULL";} + sectionArray[idx]={name:name,booksId:booksId,sectionAfter:sectionAfter, + startPage:startPage,endPage:endPage, + level:level,splitFrom:splitFrom, + id:id,deleted:deleted}; + idx++; + }); + return sectionArray; +}