--- /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+'&notes='+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
+?>