--- /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;
+	}
+}
+
+?>