<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Library_model extends CI_Model {

    public function __construct() {
        parent::__construct();
    }
   
   function insert($table,$data){
      $this->db->insert($table,$data);
      return $this->db->insert_id(); 
   }
   function get($table){
       return  $this->db->get($table)->result_array();
   }
    function update($table,$data,$where){
       return $this->db->update($table,$data,$where);
   }
    function delete($table,$col,$id){
       return $this->db->where($col,$id)->delete($table);
   }
   function get_where($table,$where){
       return $this->db->get_where($table,$where)->result_array();
   }
    function get_library_stock_register(){
       return $this->db->query("SELECT title,`supplier_name`,`supplier_details`,`total_quantity`,`total_price`,`invoice_details`,`receive_date` FROM `library_stock_register` JOIN book ON library_stock_register.book_id=book.book_id")->result_array();
   }
   function update_book_quantity($quantity,$id){
       return $this->db->query("UPDATE `book` SET `total_copies`= (total_copies + ".$quantity.") ,`remaining_book`=(total_copies - book_issued) WHERE book_id=".$id);
   }
   function update_book_register($quantity,$id){
        return $this->db->query("UPDATE `book` SET `total_copies`= total_copies - (".$quantity.") , `remaining_book`=(total_copies-book_issued) WHERE book_id=".$id);
   }
   function get_libraryStockRegister(){
       return $this->db->query("SELECT * FROM `library_stock_register` JOIN book on (library_stock_register.book_id=book.book_id)")->result_array();
   }
   function get_book(){
       return $this->db->query("SELECT * FROM `book` ")->result_array();
   }
   function  get_issued_book($user_type,$user_id){
       return $this->db->query("SELECT * FROM `library_register` JOIN book ON (library_register.book_id = book.book_id) JOIN publisher ON (book.published_by=publisher.publisher_id) where user_id='".$user_id."' AND user_type='".$user_type."' AND
       library_register.status='issued' ")->result_array();
   }
   function book_update($book_id){
       return $this->db->query("UPDATE `book` SET `book_issued`= book_issued + 1 , `remaining_book`=(total_copies-book_issued) WHERE book_id=".$book_id);
   }
   function get_book_for_issue($user_type,$user_id){ 
     $max_book = $this->db->get_where("library_settings",array("user_type" => $user_type))->row()->number_of_book;
     $issued_books= count ( $this->db->get_where("library_register",array("user_type" => $user_type,"user_id" => $user_id , "status" => "issued"))->result_array());
     if($issued_books < $max_book ){
        if($issued_books > 0){
         return $this->db->query("SELECT *  FROM `book`  
                  where book_id NOT IN  (SELECT  book_id FROM  library_register where library_register.user_id='".$user_id."' AND library_register.user_type='".$user_type."' AND library_register.status='issued' ) 
                    GROUP BY title" )->result_array();
         }else{
              return $this->db->query("SELECT * FROM `book` 
              where book_id   NOT IN(SELECT book_id FROM `book_request` where user_type='".$user_type."' AND  user_id='".$user_id."' AND  book_request.status=0) GROUP BY title")->result_array();
         }              
     }else{
          
         return null;
     }
   }
   function return_book($user_type,$user_id,$book_id){
       $setting=$this->db->get_where("library_settings",array("user_type" => $user_type))->result_array();
       $book_issed=$this->db->get_where("library_register",array("user_type" =>  $user_type , "user_id" =>  $user_id,'status' => "issued"))->result_array();
       if($setting != NULL && $book_issed !=NULL){
            $max_day=$setting[0]['maximum_date'];
            $cost=$setting[0]['per_day_cost']; 
            $temp=$book_issed[0]['register_date'];
		    $r_date=date("Y-m-d",strtotime($temp));
		    $issue_date =date_create($r_date);
		    $day=date("Y-m-d");
		    $persent_day =date_create($day);
			$diff=date_diff($issue_date,$persent_day);
			$numdays = $diff->format("%a");
			$data=array("return_date" => $day, 'status' =>"returned" );
		   if($max_day >= $numdays ) { 
		        $this->db->update("library_register",$data,array("user_type" =>  $user_type , "user_id" => $user_id,'status' =>"issued","book_id" => $book_id));
		        $this->db->query("UPDATE `book` SET `book_issued`= book_issued - 1 , `remaining_book`=(total_copies-book_issued) WHERE book_id=".$book_id);
		        return 1;
		   }else{
		       $this->db->update("library_register",$data,array("user_type" =>  $user_type , "user_id" =>  $user_id,'status' =>"issued","book_id" => $book_id));
		       $this->db->query("UPDATE `book` SET `book_issued`= book_issued - 1 , `remaining_book`=(total_copies-book_issued) WHERE book_id=".$book_id);
		       $fine= $cost * ( $numdays - $max_day );
		       $this->db->insert("due_amount",array("user_type" =>  $user_type , "user_id" =>  $user_id,'book_id' => $book_id , 'fine_amount' =>$fine ,"fine_date" => $day));
		       return 1;
		   }
       }else{
            return 0;
       }
       
   }
   function get_fine_details($user_type,$user_id){
       return  $this->db->query("SELECT * FROM `due_amount` JOIN book on (due_amount.book_id=book.book_id)  where user_type='".$user_type."' AND  user_id='".$user_id."'" )->result_array();
   }
   function get_requested_book($user_type,$user_id){
       return $this->db->query("SELECT * FROM `book_request` JOIN book on (book_request.book_id=book.book_id) where user_type='".$user_type."' AND  user_id='".$user_id."' AND  book_request.status=0")->result_array();
   }
   function get_book_requested(){
       return $this->db->query("SELECT * FROM `book_request` JOIN book on (book_request.book_id=book.book_id) JOIN student on (book_request.user_id=student.student_id) where book_request.status=0")->result_array();
   }
   
   //=========================New changes =====================================//
   function  issued_book_get($user_type,$user_id){
       return $this->db->query("SELECT * FROM `library_register` JOIN book ON (library_register.book_id = book.book_id)  where user_id='".$user_id."' AND user_type='".$user_type."' AND
       library_register.status='issued' ")->result_array();
   }

   function book_for_issue_get($user_type,$user_id){ 
     $max_book = $this->db->get_where("library_settings",array("user_type" => $user_type))->row()->number_of_book;
     $issued_books= count ( $this->db->get_where("library_register",array("user_type" => $user_type,"user_id" => $user_id , "status" => "issued"))->result_array());
     if($issued_books < $max_book ){
        if($issued_books > 0){
         return $this->db->query("SELECT * ,count(title) as count_book FROM `book` where book_id NOT IN (SELECT book_id FROM library_register where library_register.user_id='".$user_id."' AND library_register.user_type='".$user_type."' AND library_register.status='issued' ) AND title NOT IN (SELECT book.title FROM library_register JOIN book ON library_register.book_id = book.book_id where library_register.user_id='".$user_id."' AND library_register.user_type='".$user_type."' AND (library_register.status='issued' OR library_register.status='return')) group by title
          ")->result_array();
         }else{
              return $this->db->query("SELECT  *, count(title) as count_book FROM `book`  where book_id   NOT IN(SELECT book_id FROM `book_request` where user_type='".$user_type."' AND  user_id='".$user_id."' AND  book_request.status=0) group by title")->result_array();
         }              
     }else{
          
         return null;
     }
   }

   function fine_details_get($user_type,$user_id){
       return  $this->db->query("SELECT * FROM `due_amount` JOIN book on (due_amount.book_id=book.book_id)  where user_type='".$user_type."' AND  user_id='".$user_id."'" )->result_array();
   }
   function requested_book_get($user_type,$user_id){
       return $this->db->query("SELECT * FROM `book_request` JOIN book on (book_request.book_id=book.book_id) where user_type='".$user_type."' AND  user_id='".$user_id."' AND  book_request.status=0")->result_array();
   }
   function update_book($book_id){
       return $this->db->query("UPDATE `book` SET `book_issued`= book_issued + 1 , `remaining_book`=(total_copies-book_issued) WHERE book_id=".$book_id);
   }
   
   /* Total Fine Amount*/
   function total_fine_amount()
   {
       return $this->db->query("SELECT * ,SUM(`fine_amount`) AS SUM FROM `due_amount` GROUP BY Year(`fine_date`), Month(`fine_date`)")->result_array();
   }
   /* Monthly Report*/
   function monthly_report($y,$m)
   {
       return $this->db->query("SELECT * ,SUM(`fine_amount`) AS SUM FROM `due_amount` WHERE `fine_date` LIKE ('%".$y."%-%".$m."%')")->result_array();
   }
   
   /* Total book Issued*/
   function total_book_issued(){
       return $this->db->query("SELECT *, COUNT(*) AS sum FROM `library_register` WHERE `status` = 'issued'  GROUP BY Year(`register_date`), Month(`register_date`)")->result_array();
   }
}