<?php
include_once('config/Lib_mb_utf8.php');
include_once('config/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.")
		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 ";
		*/
	$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
		FROM sections_index
		JOIN books ON sections_index.books_id = books.id
		WHERE NOT EXISTS
		(SELECT 1 FROM sections_versions WHERE books_id=sections_index.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);
	if (!$result) {
		echo mysql_error();
		return;
	}
	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="js/jquery/1.6.2/jquery.min.js" type="text/javascript"></script>
                <!--<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
?>