# HG changeset patch # User Zoe Hong # Date 1426786581 -3600 # Node ID 1f9d2bfe1d1376162d61ced613aa86e2fd546ce3 # Parent 723a162b6627ac478ee763211b72d5d51d18ca10 handle new section created by user, update sections_index table diff -r 723a162b6627 -r 1f9d2bfe1d13 check_sections.php --- a/check_sections.php Thu Mar 19 15:06:34 2015 +0100 +++ b/check_sections.php Thu Mar 19 18:36:21 2015 +0100 @@ -56,20 +56,33 @@ } 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 + /* + $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 ";*/ + 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.name, start_page, end_page, COUNT( * ) AS count - FROM sections - JOIN books ON sections.books_id = books.id + FROM sections_index + JOIN books ON sections_index.books_id = books.id WHERE NOT EXISTS - (SELECT 1 FROM sections_versions WHERE books_id=sections.books_id) + (SELECT 1 FROM sections_versions WHERE books_id=sections_index.books_id) GROUP BY books_id, start_page, end_page HAVING COUNT( * ) >=".$count.") UNION diff -r 723a162b6627 -r 1f9d2bfe1d13 edit_section_db.php --- a/edit_section_db.php Thu Mar 19 15:06:34 2015 +0100 +++ b/edit_section_db.php Thu Mar 19 18:36:21 2015 +0100 @@ -36,7 +36,8 @@ $condition=" AND versions_id=".$versionInfo['id']." AND deleted=0 "; $versionId=$versionInfo['id']; }else{ - $table="sections"; + // $table="sections"; + $table="sections_index"; $condition=""; } $query="SELECT * @@ -71,7 +72,8 @@ $condition="AND deleted=0 "; $condition=" AND versions_id=".$versionInfo['id']." AND deleted=0 "; }else{ - $table="sections"; + // $table="sections"; + $table="sections_index"; $condition=""; } @@ -97,7 +99,8 @@ 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"; + //$query="SELECT * FROM sections WHERE books_id=".$bookId." ORDER BY id ASC"; + $query="SELECT * FROM sections_index WHERE books_id=".$bookId." ORDER BY id ASC"; } $result=mysql_query($query); @@ -127,15 +130,70 @@ return $sectionArray; } function updateSectionArray($bookId,$sectionArray,$version,$editor){ + + // TODO: ---transaction --- + + $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; + //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){ + // get the section_id + $section_id = $row['id']; + $name = $row['name']; + $books_id = sprintf("%05d",$row['booksId']); + $section_after = $row['sectionAfter']; + $start_page = $row['startPage']; + $end_page = $row['endPage']; + $level = $row['level']; + $split_from = $row['splitFrom']; + $deleted = $row['deleted']; + + // update to section_index table, which is the up-to-date section table + if ($section_id == 0) { // section_id = 0 means it is a new record created by user + // insert new row to section_index table + $qry = "INSERT INTO sections_index (name,books_id,section_after,start_page,end_page,level,split_from) VALUES + ('".$name."','".$books_id."','".$section_after."',".$start_page. + ",".$end_page.",".$level.",".$split_from.")"; + $rst = mysql_query($qry); + if (!$rst) { + echo json_encode(mysql_error()); + } + $section_id = mysql_insert_id(); + + } else { + // update + $qry = "UPDATE sections_index SET name='".$name."',books_id='".$books_id."',section_after='".$section_after. + "',start_page=".$start_page.",end_page=".$end_page.",level=".$level.",split_from=".$split_from." WHERE id=".$section_id; + $rst = mysql_query($qry); + if (!$rst){ + echo json_encode(mysql_error()); + } + } + + $str="("; + $str.="'".$name."',"; + $str.="'".$books_id."',"; + $str.="'".$section_after."',"; + $str.=$start_page.","; + $str.=$end_page.","; + $str.=$level.","; + $str.=$split_from.","; + $str.=$section_id.","; // sections_id should get from section_index table + $str.=$versionId.","; + $str.=$deleted; + $str.=")"; + if($idx!=sizeof($sectionArray)-1){ + $str.=", "; + } + + + /* $str="("; $str.="'".$row['name']."',"; $str.="'".sprintf("%05d",$row['booksId'])."',"; @@ -144,13 +202,14 @@ $str.=$row['endPage'].","; $str.=$row['level'].","; $str.=$row['splitFrom'].","; - $str.=$row['id'].","; + $str.=$section_id.","; // sections_id should get from section_index table $str.=$versionId.","; $str.=$row['deleted']; $str.=")"; if($idx!=sizeof($sectionArray)-1){ $str.=", "; } + */ $query.=$str; } $result=mysql_query($query); @@ -168,6 +227,8 @@ }else{ echo json_encode("Succeeded."); } + + } @@ -193,7 +254,8 @@ //$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"; + // $query="SELECT books.id, books.name FROM books LEFT JOIN sections ON books.id=sections.books_id WHERE sections.books_id IS NULL"; + $query="SELECT books.id, books.name FROM books LEFT JOIN sections_index ON books.id=sections_index.books_id WHERE sections_index.books_id IS NULL"; $result=mysql_query($query); if(!$result){ @@ -246,7 +308,8 @@ */ // id should be autoincremated - $query="INSERT INTO sections (name,books_id,section_after) VALUES "; + // $query="INSERT INTO sections (name,books_id,section_after) VALUES "; + $query="INSERT INTO sections_index (name,books_id,section_after) VALUES "; $str="("; $str.="'".$name."','"; diff -r 723a162b6627 -r 1f9d2bfe1d13 js/check_sections_details.js --- a/js/check_sections_details.js Thu Mar 19 15:06:34 2015 +0100 +++ b/js/check_sections_details.js Thu Mar 19 18:36:21 2015 +0100 @@ -105,6 +105,12 @@ var clone=$(".clone").clone(true); clone.removeClass("clone"); clone.show(); + + clone.children(".hiddenInfo").children(".id").html(0); // set section id to 0 + + // bug: section_after field is not correct + + if(pos==0){ clone.insertBefore(obj); //$("
").insertAfter(clone); @@ -112,6 +118,8 @@ clone.insertAfter(obj); //$("
").insertBefore(clone); } + + $("#searchResults .sequence").each(function(idx){ $(this).html(idx+1); });