<?php
include("../include/config.inc.php");
$conn = $con;
require_once('spreadsheet-reader-master/php-excel-reader/excel_reader2.php');
require_once('spreadsheet-reader-master/SpreadsheetReader.php');

if (isset($_POST["import"]))
{
       
  $allowedFileType = ['application/vnd.ms-excel','text/xls','text/xlsx','application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'];
  
  if(in_array($_FILES["file"]["type"],$allowedFileType)){

        $targetPath = 'uploads/'.$_FILES['file']['name'];
        move_uploaded_file($_FILES['file']['tmp_name'], $targetPath);
        
        $Reader = new SpreadsheetReader($targetPath);
        
        $sheetCount = count($Reader->sheets());
        
        $course_id=$_REQUEST['course_id'];
        $session_id=$_REQUEST['session_id'];
        for($i=0;$i<$sheetCount;$i++)
        {
            if($i==1){
                break;
            }
            $Reader->ChangeSheet($i);
            $x=0;
            foreach ($Reader as $Row)
            {$x++;
                
                $accession_code="";
                if(!empty($Row[0])) {
                    $accession_code = mysqli_real_escape_string($conn,$Row[0]);
                }
                $book_name="";
                if(!empty($Row[1])) {
                    $book_name = mysqli_real_escape_string($conn,$Row[1]);
                }
                $author="";
                if(!empty($Row[2])) {
                    $author = mysqli_real_escape_string($conn,$Row[2]);
                }
                $publisher="";
                if(!empty($Row[3])) {
                    $publisher = mysqli_real_escape_string($conn,$Row[3]);
                }
                $publication_place="";
                if(!empty($Row[4])) {
                    $publication_place = mysqli_real_escape_string($conn,$Row[4]);
                }
                $publication_year="";
                if(!empty($Row[5])) {
                    $publication_year = mysqli_real_escape_string($conn,$Row[5]);
                }
                $cost="";
                if(!empty($Row[6])) {
                    $cost = mysqli_real_escape_string($conn,$Row[6]);
                }
                $no_of_pages="";
                if(!empty($Row[7])) {
                    $no_of_pages = mysqli_real_escape_string($conn,$Row[7]);
                }
                $nature_of_binding="";
                if(!empty($Row[8])) {
                    $nature_of_binding = mysqli_real_escape_string($conn,$Row[8]);
                }
                $edition="";
                if(!empty($Row[9])) {
                    $edition = mysqli_real_escape_string($conn,$Row[9]);
                }
                $class_type="";
                if(!empty($Row[10])) {
                    $class_type = mysqli_real_escape_string($conn,$Row[10]);
                }
                $language="";
                if(!empty($Row[11])) {
                    $language = mysqli_real_escape_string($conn,$Row[11]);
                }
                $id=mysqli_fetch_assoc(mysqli_query($conn,"select id from book where book_name='$book_name' order by id desc limit 1"))['id'];
                if($id>0){
                    mysqli_query($conn,"insert into book_copies (book_id,accession_code) values ('$id','$accession_code')");
                    mysqli_query($conn,"update book set copies_number=copies_number+1,copies_available=copies_avialable+1 where id=$id");
                }else{
                    mysqli_query($conn,"insert into book (book_name,author,publisher,publication_place,publication_year,cost,no_of_pages,
                    nature_of_binding,edition,class_type,language,copies_number,copies_available) values ('$book_name','$author','$publisher','$publication_place',
                    '$publication_year','$cost','$no_of_pages','$nature_of_binding','$edition','$class_type','$language','1','1')");
                    $id=$conn->insert_id;
                    mysqli_query($conn,"insert into book_copies (book_id,accession_code) values ('$id','$accession_code')");
                }
                echo $id."<br>";
                if (!empty($result)) {
                    $type = "success";
                    $message = "Excel Data Imported into the Database";
                } else {
                    $type = "error";
                    $message = "Problem in Importing Excel Data";
                }
            }
        
        }
  }
  else
  { 
        $type = "error";
        $message = "Invalid File Type. Upload Excel File.";
  }
}
?>