package Textbook::Books;

require Exporter;
@ISA       = qw(Exporter);
# Symbols to be exported by default
#@EXPORT    = qw(
#    opl_
#);
# Symbols to be exported on request
@EXPORT_OK = qw(
    bookList_forCourse
    bookList_forTeacher
    bookList_forStudent
    bookList_forHomeroom
    book_TotalAvailable

    book_getByIsbn
    

);
# Version number
$VERSION   = 0.01;      

#use utf8;
use strict;

############################################################
#
# function bookList_forCourse
#   
#   $params: a hash table may have 0 or more of the following key
#               - courseCode,
#               - courseName
#
############################################################
sub bookList_forCourse{
    my ($dbh,$params) = @_;
    my @condVal=();
    my($sql_fields,$sql_tables,$sql_cond)=("","","");
    $sql_fields="select m.rid,  m.title, m.author, m.ISBN, m.pubName, m.pubDate, m.pubPlace ";
    $sql_tables   =" from tbk_marcRecord m inner join  tbk_courseBookAssignment cba on m.ISBN=cba.ISBN ";
    if($params->{"courseCode"} ne ""){
        $sql_cond =" cba.courseCode= ? ";
        push @condVal,$params->{"courseCode"};
    }
    if($params->{"courseName"} ne ""){
        $sql_tables .=" inner join tbk_courseList c on cba.courseCode=c.courseCode ";
        $sql_cond .=" && " if ($sql_cond ne "");
        $sql_cond .=" c.courseName= ? ";
        push @condVal,$params->{"courseName"};
    }
   my $sql =$sql_fields . $sql_tables ;
   $sql .= " where $sql_cond " if($sql_cond ne "");
   my @bookList;
   my $sth = $dbh->prepare($sql);
   $sth->execute(@condVal);

   while ( my $rec = $sth->fetchrow_hashref ){
       push @bookList,$rec;
   }
    
   return @bookList;

}
############################################################
#
# function bookList_forTeacher
#   
#   $params: a hash table may have 0 or more of the following key
#               - teacherId
#
############################################################
sub bookList_forTeacher{
    my ($dbh,$teacherId) = @_;
    my @bookList;
    return @bookList if($teacherId eq '');
    my $sql= "select m.rid,  m.title, m.author, m.ISBN, m.pubName, m.pubDate, m.pubPlace
              from tbk_marcRecord m inner join  tbk_courseBookAssignment cba on m.ISBN=cba.ISBN 
                    inner join tbk_teacherSchedule ts on ts.courseCode = cba.courseCode 
              where ts.teacherId=? ";

   my $sth = $dbh->prepare($sql);
   $sth->execute($teacherId);

   while ( my $rec = $sth->fetchrow_hashref ){
       push @bookList,$rec;
   }
    
   return @bookList;
}

############################################################
#
# function bookList_forStudent
#   
#   $params: a hash table may have 0 or more of the following key
#               - studentId,
#
############################################################
sub bookList_forStudent{
    my ($dbh,$studentId) = @_;
    my @bookList;
    return @bookList if($studentId eq '');
    my $sql= "select m.rid,  m.title, m.author, m.ISBN, m.pubName, m.pubDate, m.pubPlace
              from tbk_marcRecord m inner join  tbk_courseBookAssignment cba on m.ISBN=cba.ISBN 
                    inner join tbk_studentSchedule ss on ss.courseCode= cba.courseCode 
              where ss.studentId=? ";

   my $sth = $dbh->prepare($sql);
   $sth->execute($studentId);

   while ( my $rec = $sth->fetchrow_hashref ){
       push @bookList,$rec;
   }
    
   return @bookList;
}

############################################################
#
# function bookList_foHomeroom
#   
#   $params: a hash table may have 0 or more of the following key
#               - studentId,
#
############################################################
sub bookList_forHomeroom{
    my ($dbh,$homeroom) = @_;
    my @bookList;
    return @bookList if($homeroom eq '');
    my $sql= "select m.rid,  m.title, m.author, m.ISBN, m.pubName, m.pubDate, m.pubPlace
              from tbk_marcRecord m inner join  tbk_courseBookAssignment cba on m.ISBN=cba.ISBN 
                    inner join tbk_studentSchedule ss on ss.courseCode= cba.courseCode 
                    innder join tbk_students s on ss.studentID=s.studentId 
              where s.homeroom=?";

   my $sth = $dbh->prepare($sql);
   $sth->execute($homeroom);

   while ( my $rec = $sth->fetchrow_hashref ){
       push @bookList,$rec;
   }
    
   return @bookList;
}   
############################################################
#
# function book_TotalAvailable
#   
#   $isbn 
#
#   return total books available 
#       (total book - number of distributed - number of prepared books but not yet distributed)
#
############################################################
sub book_TotalAvailable{
    my ($dbh,$isbn) = @_;
    my ($totalBooks,$bookDistributed,$bookPrepared)=(0,0,0);
    return 0 if($isbn eq '');

    my $sth_total = $dbh->prepare("select count(*) as count from tbk_items where ISBN=? && status='ok'");
    my $sth_dist  = $dbh->prepare("select count(*) as count from tbk_bookDistribution where ISBN=? && returnDate is null");
    my $sth_prep  = $dbh->prepare("select sum(total) - sum(nDistributed) as count from tbk_preparationDetails where isbn= ?");

   $sth_total->execute($isbn);
   $sth_dist->execute($isbn);
   $sth_prep->execute($isbn);
   ($totalBooks)        =$sth_total->fetchrow_array;
   ($bookDistributed)   =$sth_dist->fetchrow_array;
   ($bookPrepared)      =$sth_prep->fetchrow_array;
   my $totalAvailable =$totalBooks - $bookDistributed - $bookPrepared;
   return $totalAvailable;
}   


############################################################
#
# function book_getByIsbn
#   
#   $isbn 
#
#   return  book
#
############################################################
sub book_getByIsbn{
    my ($dbh,$isbn) = @_;
    my $book;
    return $book if($isbn eq '');
    my $sql="select m.rid,  m.title, m.author, m.ISBN, m.pubName, m.pubDate, m.pubPlace 
          from tbk_marcRecord m where ISBN =?";
    my $sth=$dbh->prepare($sql);      
    $sth->execute($isbn);
    if(my $rec = $sth->fetchrow_hashref){
        $book=$rec;
    }

    return $book;
}   
   
1;