package Textbook::Collection;

require Exporter;
@ISA       = qw(Exporter);
# Symbols to be exported by default
#@EXPORT    = qw(
#    opl_
#);
# Symbols to be exported on request
@EXPORT_OK = qw(
   collectFromTeacher_woBc
   collectFromStudent_woBc

);
# Version number
$VERSION   = 0.01;      

#use utf8;
use strict;


############################################################
#
# function collectFromTeacher_woBc
#   
#   $params:   Hash table 
#               - pid
#               - ISBN
#               - courseId
#               - sectionId
#               - teacherId 
#               - nCopies_ok
#               - nCopies_damaged
#               - nCopies_lost
#               - schoolYearId
#
############################################################
sub collectFromTeacher_woBc{
    my ($dbh,$params) = @_;
    my @condVal;
    my ($nCopies_ok,$nCopies_damaged,$nCopies_lost) = (
        int($params->{'nCopies_ok'}), 
        int($params->{'nCopies_damaged'}),
        int($params->{'nCopies_lost'}));

    my $nReturns = $nCopies_ok + $nCopies_damaged + $nCopies_lost;
    return if($params->{'ISBN'} eq '' || int($nReturns) <1); 


    @condVal = ($params->{'ISBN'},$params->{'courseId'},$params->{'sectionId'},
                $params->{'pid'},$params->{'schoolYearId'},$params->{'teacherId'});
    
    my $sth_select=$dbh->prepare( "select  id,ISBN
                                   from    tbk_bookDistribution 
                                   where   ISBN         = ? && 
                                           courseId     = ? && 
                                           sectionId    = ? && 
                                           pid          = ? &&  
                                           schoolYearId = ? &&  
                                           teacherId    = ? && 
                                           returnDate   is  null " 
                                );
    my $sth_selectItems=$dbh->prepare("select id from tbk_items where ISBN=?");  
      
    my $sth_update=$dbh->prepare( "update    tbk_bookDistribution  
                              set       returnDate   = now() ,
                                        returnStatus = ?, 
                                        returnBy     = 'teacher' 

                              where     id         = ?" 
                        );
    my $sth_tblItems=$dbh->prepare( "update    tbk_items
                                     set       status =?
                                     where     id =? 
                                   ");


   $sth_select->execute(@condVal);
   my ($count_ok,$count_damaged, $count_lost) = (0,0,0);
   while(my $rec =$sth_select->fetchrow_hashref){
       if ($count_ok <$nCopies_ok){
           $sth_update->execute('ok',$rec->{'id'});
           $count_ok++;
           next; 
       }
       if ($count_damaged <$nCopies_damaged){
           $sth_update->execute('damaged',$rec->{'id'});
           $sth_tblItems->execute('damaged',$rec->{'ISBN'});
           $count_damaged++;
           next; 
       }  
       if ($count_lost<$nCopies_lost){
            $sth_update->execute('lost',$rec->{'id'});
           $sth_tblItems->execute('lost',$rec->{'ISBN'});
           $count_lost++;
           next; 
       }     
   }
   if($count_damaged> 0 || $count_lost>0){
       $count_damaged=0;
       $count_lost=0;
      $sth_selectItems->execute($params->{'ISBN'}); 
      while(my $rec =$sth_selectItems->fetchrow_hashref){
       
       if ($count_damaged <$nCopies_damaged){
           $sth_tblItems->execute('damaged',$rec->{'id'});
           $count_damaged++;
           next; 
       }  
       if ($count_lost<$nCopies_lost){
           $sth_tblItems->execute('lost',$rec->{'id'});
           $count_lost++;
           next; 
       }     
   }

  }  
  
  return 1;   
      
}

############################################################
#
# function  collectFromStudent_woBc
#   
#   $params:   Hash table 
#               - pid
#               - ISBN
#               - courseId
#               - sectionId
#               - studentId 
#               - nCopies_ok
#               - nCopies_damaged
#               - nCopies_lost
#               - schoolYearId
#
############################################################
sub collectFromStudent_woBc{
    my ($dbh,$params) = @_;
    my @condVal;
    my ($nCopies_ok,$nCopies_damaged,$nCopies_lost) = (
        int($params->{'nCopies_ok'}), 
        int($params->{'nCopies_damaged'}),
        int($params->{'nCopies_lost'}));

    my $nReturns = $nCopies_ok + $nCopies_damaged + $nCopies_lost;
    return if($params->{'ISBN'} eq '' || int($nReturns) <1); 
    
    @condVal = ($params->{'ISBN'},$params->{'courseId'},$params->{'sectionId'},
                $params->{'pid'},$params->{'schoolYearId'},$params->{'studentId'});

    my $sth_select=$dbh->prepare( "select  id,ISBN
                                   from    tbk_bookDistribution 
                                   where   ISBN         = ? && 
                                           courseId     = ? && 
                                           sectionId    = ? && 
                                           pid          = ? &&  
                                           schoolYearId = ? &&  
                                           teacherId    = ? && 
                                           returnDate   is  null " 
                                );
    my $sth_selectItems=$dbh->prepare("select id from tbk_items where ISBN=?");  
      
    my $sth_update=$dbh->prepare( "update    tbk_bookDistribution  
                              set       returnDate   = now() ,
                                        returnStatus = ?, 
                                        returnBy     = 'student' 

                              where     id         = ?" 
                        );
    my $sth_tblItems=$dbh->prepare( "update    tbk_items
                                     set       status =?
                                     where     id =? 
                                   ");

     $sth_select->execute(@condVal);
     my ($count_ok,$count_damaged, $count_lost) = (0,0,0);
     while(my $rec =$sth_select->fetchrow_hashref){
       if ($count_ok <$nCopies_ok){
           $sth_update->execute('ok',$rec->{'id'});
           $count_ok++;
           next; 
       }
       if ($count_damaged <$nCopies_damaged){
           $sth_update->execute('damaged',$rec->{'id'});
           $sth_tblItems->execute('damaged',$rec->{'ISBN'});
           $count_damaged++;
           next; 
       }  
       if ($count_lost<$nCopies_lost){
            $sth_update->execute('lost',$rec->{'id'});
           $sth_tblItems->execute('lost',$rec->{'ISBN'});
           $count_lost++;
           next; 
       }     
    }
    if($count_damaged> 0 || $count_lost>0){
           $count_damaged=0;
           $count_lost=0;
          $sth_selectItems->execute($params->{'ISBN'}); 
          while(my $rec =$sth_selectItems->fetchrow_hashref){
           
           if ($count_damaged <$nCopies_damaged){
               $sth_tblItems->execute('damaged',$rec->{'id'});
               $count_damaged++;
               next; 
           }  
           if ($count_lost<$nCopies_lost){
               $sth_tblItems->execute('lost',$rec->{'id'});
               $count_lost++;
               next; 
           }     
       }

    }  
  
  return 1;   

}