Modify convert script: add user rate to database

3 files changed, 37 insertions, 10 deletions

diff --git a/scripts/Common/datafile.php b/scripts/Common/datafile.php
index 83b4c024..7741a51a 100644
--- a/scripts/Common/datafile.php
+++ b/scripts/Common/datafile.php
@@ -38,6 +38,7 @@ function create_tables($sqlite_db)
       lang char(2),
       year integer,
       description text,
+      rate text,
       PRIMARY KEY(id,id_author)
       );
   ");
diff --git a/scripts/Flibusta/conv_book.php b/scripts/Flibusta/conv_book.php
index d6abc7f0..cce7c609 100644
--- a/scripts/Flibusta/conv_book.php
+++ b/scripts/Flibusta/conv_book.php
@@ -74,6 +74,17 @@ function convert_genres($mysql_db, $sqlite_db, $min)
   $sqlite_db->query("commit;");
 }
 
+function book_rate($mysql_db, $id)
+{
+    $subsql = "SELECT ROUND(AVG(rate),3) AS rate, COUNT(UserId) AS user FROM librate WHERE BookId=$id";
+    $subquery = $mysql_db->query($subsql);
+    while ($subrow = $subquery->fetch_array()) {
+      if ($subrow['user'] == 0) return NULL;
+      return $subrow['rate']."/".$subrow['user'];
+    }
+  return NULL;
+}
+
 function convert_books($mysql_db, $sqlite_db, $min)
 {
   $sqlite_db->query("begin transaction;");
@@ -93,8 +104,9 @@ function convert_books($mysql_db, $sqlite_db, $min)
   while ($row = $query->fetch_array()) {
     echo "Book: ".$row['Time']." - ".$row['BookId']." - ".$row['FileType']." - ".$row['AvtorId']." - ".$row['Title']."\n";
 
+	$id = $row['BookId'];
     $genres = "";
-    $subsql = "SELECT GenreCode FROM libgenre LEFT JOIN libgenrelist ON libgenre.GenreId = libgenrelist.GenreId WHERE BookId=".$row['BookId'];
+    $subsql = "SELECT GenreCode FROM libgenre LEFT JOIN libgenrelist ON libgenre.GenreId = libgenrelist.GenreId WHERE BookId=$id";
     $subquery = $mysql_db->query($subsql);
     while ($subrow = $subquery->fetch_array()) {
       $genres = $genres.GenreCode($subrow['GenreCode']);
@@ -107,11 +119,12 @@ function convert_books($mysql_db, $sqlite_db, $min)
     $lang = $row['Lang'];
     $lang = strtolower($lang);
     $lang = strtolowerEx($lang);
-    $sql = "INSERT INTO books (id, id_author, title, deleted, file_name, file_size, file_type, genres, created, lang, year, md5sum) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)";
+    $rate = book_rate($mysql_db, $id);
+    $sql = "INSERT INTO books (id, id_author, title, deleted, file_name, file_size, file_type, genres, created, lang, year, rate, md5sum) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)";
     $insert = $sqlite_db->prepare($sql);
-    if($insert === false){ $err= $dbh->errorInfo(); die($err[2]); }
-    $err= $insert->execute(array($row['BookId'], $row['AvtorId'], trim($row['Title']), $deleted, $row['FileName'], $row['FileSize'], $file_type, $genres, $row['Time'], $lang, $row['Year'], $row['md5']));
-    if($err === false){ $err= $dbh->errorInfo(); die($err[2]); }
+    if($insert === false){ $err= $sqlite_db->errorInfo(); die($err[2]); }
+    $err= $insert->execute(array($id, $row['AvtorId'], trim($row['Title']), $deleted, $row['FileName'], $row['FileSize'], $file_type, $genres, $row['Time'], $lang, $row['Year'], $rate, $row['md5']));
+    if($err === false){ $err= $sqlite_db->errorInfo(); die($err[2]); }
     $insert->closeCursor();
   }
   
diff --git a/scripts/LibRusEc/convert.php b/scripts/LibRusEc/convert.php
index 54bb1f3e..81d46804 100644
--- a/scripts/LibRusEc/convert.php
+++ b/scripts/LibRusEc/convert.php
@@ -72,6 +72,17 @@ function convert_genres($mysql_db, $sqlite_db, $min)
   $sqlite_db->query("commit;");
 }
 
+function book_rate($mysql_db, $id)
+{
+    $subsql = "SELECT ROUND(AVG(rate),3) AS rate, COUNT(uid) AS user FROM librate WHERE bid=$id";
+    $subquery = $mysql_db->query($subsql);
+    while ($subrow = $subquery->fetch_array()) {
+      if ($subrow['user'] == 0) return NULL;
+      return $subrow['rate']."/".$subrow['user'];
+    }
+  return NULL;
+}
+
 function convert_books($mysql_db, $sqlite_db, $min)
 {
   $sqlite_db->query("begin transaction;");
@@ -91,8 +102,9 @@ function convert_books($mysql_db, $sqlite_db, $min)
   while ($row = $query->fetch_array()) {
     echo "Book: ".$row['Time']." - ".$row['bid']." - ".$row['FileType']." - ".$row['aid']." - ".$row['Title']."\n";
 
+	$id = $row['bid'];
     $genres = "";
-    $subsql = "SELECT code FROM libgenre LEFT JOIN libgenrelist ON libgenre.gid = libgenrelist.gid WHERE bid=".$row['bid'];
+    $subsql = "SELECT code FROM libgenre LEFT JOIN libgenrelist ON libgenre.gid = libgenrelist.gid WHERE bid=$id";
     $subquery = $mysql_db->query($subsql);
     while ($subrow = $subquery->fetch_array()) {
       $genres = $genres.GenreCode($subrow['code']);
@@ -105,11 +117,12 @@ function convert_books($mysql_db, $sqlite_db, $min)
     $lang = $row['Lang'];
     $lang = strtolower($lang);
     $lang = strtolowerEx($lang);
-    $sql = "INSERT INTO books (id, id_author, title, deleted, file_name, file_size, file_type, genres, created, lang, year, md5sum) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)";
+    $rate = book_rate($mysql_db, $id);
+    $sql = "INSERT INTO books (id, id_author, title, deleted, file_name, file_size, file_type, genres, created, lang, year, rate, md5sum) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)";
     $insert = $sqlite_db->prepare($sql);
-    if($insert === false){ $err= $dbh->errorInfo(); die($err[2]); }
-    $err= $insert->execute(array($row['bid'], $row['aid'], trim($row['Title']), $deleted, $row['FileName'], $row['FileSize'], $file_type, $genres, $row['Time'], $lang, $row['Year'], $row['md5']));
-    if($err === false){ $err= $dbh->errorInfo(); die($err[2]); }
+    if($insert === false){ $err= $sqlite_db->errorInfo(); die($err[2]); }
+    $err= $insert->execute(array($id, $row['aid'], trim($row['Title']), $deleted, $row['FileName'], $row['FileSize'], $file_type, $genres, $row['Time'], $lang, $row['Year'], $rate, $row['md5']));
+    if($err === false){ $err= $sqlite_db->errorInfo(); die($err[2]); }
     $insert->closeCursor();
   }