package Textbook::Distribution;

require Exporter;
@ISA       = qw(Exporter);
# Symbols to be exported by default
#@EXPORT    = qw(
#    opl_
#);
# Symbols to be exported on request
@EXPORT_OK = qw(
    distribute_toStudent_withBc 
    distribute_toStudent_woBc 
    distribute_toTeacher_withBc 
    distribute_toTeacher_woBc
   
    getDistribution_byId 
    getDistributions2ReceiverByType

    countBookDistByISBN

    getDistribution_teacher
    getDistribution_student
    getDistribution_course
    getDistribution_homeroom
);
# Version number
$VERSION   = 0.01;      

#use utf8;
use strict;

############################################################
#
# function countBookDistByISBN
#   
#   $params:   Hash table 
#               - ISBN
#               - receiverType    :students OR teachers
#               - receiverId      :studentId OR teacherId
#               - courseId
#               - sectionId
#               - schoolYearId
#
############################################################
sub  countBookDistByISBN{  
    my ($dbh,$params) = @_;
    return if($params->{'ISBN'} eq ''||
              $params->{'schoolYearId'} eq '');
    my ($sql_cond,$sql_groupby);

    $sql_cond =" where ISBN =? && schoolYearId= ? " ;
    my @condVal=($params->{'ISBN'},$params->{'schoolYearId'});

    my $sql = "select  ISBN,count(ISBN) as bCount ";
    $sql_groupby =" GROUP BY  ISBN ";  

    if($params->{'receiverType'} eq 'teachers'){
        $sql .= " ,teacherId from tbk_bookDistribution ";
        $sql_cond .=" && teacherId=? " ;
         push @condVal,$params->{'receiverId'};
        $sql_groupby .="  ,teacherId ";  
    }
    elsif($params->{'receiverType'} eq 'students'){
        $sql .= " ,studentId  from tbk_bookDistribution ";
        $sql_cond .=" && studentId=?" ;
         push @condVal,$params->{'receiverId'};
        $sql_groupby .=",studentId ";  
    }
    if($params->{'courseId'}){
        $sql_cond .= " && courseId = ?";
        push @condVal,$params->{'courseId'};
    }

    if($params->{'sectionId'}){
        $sql_cond .= " && sectionId = ?";
        push @condVal,$params->{'sectionId'};
    }

                        
    if($sql_cond){
        $sql .=  $sql_cond  . $sql_groupby; 
    }
    my $sth= $dbh->prepare($sql);
    $sth->execute(@condVal);
    
    my @retval;
    while(my $rec =$sth->fetchrow_hashref){
        push @retval,$rec;
    }
                     
     return \@retval;
}



############################################################
#
# function distribute_toStudent_woBc
#   
#   $params:   Hash table 
#               - pid
#               - ISBN
#               - teacherId 
#               - studentId
#               - nCopies
#               - 
#
############################################################
sub distribute_toStudent_woBc{
    my ($dbh,$params) = @_;

    return if($params->{'ISBN'} eq '');
    my $sth=$dbh->prepare( "insert into tbk_bookDistribution set
                            ISBN         = ?,
                            pid          = ?,
                            studentId    = ?, 
                            courseId     = ?,
                            sectionId    = ?,
                            schoolYearId = ?, 
                            returnBy     = 'student',
                            distDate     =now()"
                        );

    my $sth_preparation = $dbh->prepare( "UPDATE tbk_preparationDetails 
                            SET    nDistributed = nDistributed + ? 
                            WHERE  pid          = ? AND
                                   courseId     = ? AND
                                   sectionId    = ? AND
                                   ISBN         = ?  "
                        );

    $sth_preparation->execute($params->{'nCopies'},
                      $params->{'pid'},
                      $params->{'courseId'},
                      $params->{'sectionId'},
                      $params->{'ISBN'},
                      );


    for(my $i=0; $i<$params->{'nCopies'};$i++){
        $sth->execute($params->{'ISBN'},
                      $params->{'pid'},
                      $params->{'studentId'},
                      $params->{'courseId'},
                      $params->{'sectionId'},
                      $params->{'schoolYearId'},
                      );
    }
  
}
############################################################
#
# function distribute_toStudent_woBc
#   
#   $params:   Hash table 
#               - pid
#               - ISBN
#               - teacherId 
#               - studentId
#               - bcList :reference to  an array of book's barcode
#               - 
#
############################################################
sub distribute_toStudent_withBc{
    my ($dbh,$params) = @_;
=title
    return if($params->{'ISBN'} eq '');
    my $sth=$dbh->prepare( "insert into tbk_bookDistribution set 
                            ISBN=? ,
                            barcode=?,
                            pid=?,                
                            studentId =?, 
                            teacherId=?"
                        );
    my $bcList=$params->{'bcList'};
    foreach(my $bc (@$bcList)){
        $sth->execute($params->{'ISBN'},
                      $bc,
                      $params->{'pid'},
                      $params->{'studentId'},
                      $params->{'teacherId'},  
                      );
    }
=cut  
}
############################################################
#
# function distribute_toTeacher_woBc
#   
#   $params:   Hash table 
#               - pid
#               - ISBN
#               - courseId
#               - sectionId
#               - teacherId 
#               - nCopies
#               - schoolYearId
#
############################################################
sub distribute_toTeacher_woBc{
    my ($dbh,$params) = @_;

    my $nCopies= $params->{'nCopies'};
    return if($params->{'ISBN'} eq '' || int($nCopies) <1);
    my $sth=$dbh->prepare( "insert into tbk_bookDistribution set 
                            ISBN         = ?,
                            courseId     = ?,
                            sectionId    = ?,
                            pid          = ?, 
                            schoolYearId = ?, 
                            teacherId    = ?,
                            distDate     =now()"
                        );
     my $sth_preparation = $dbh->prepare( "UPDATE tbk_preparationDetails 
                            SET    nDistributed = ? 
                            WHERE  pid          = ? AND
                                   courseId     = ? AND
                                   sectionId    = ? AND
                                   ISBN         = ?  "
                        );
    $sth_preparation->execute($params->{'nCopies'},
                      $params->{'pid'},
                      $params->{'courseId'},
                      $params->{'sectionId'},
                      $params->{'ISBN'},
                      );

    for(my $i=0 ;$i <$nCopies; $i++){
        $sth->execute($params->{'ISBN'},
                      $params->{'courseId'},
                      $params->{'sectionId'},
                      $params->{'pid'},
                      $params->{'schoolYearId'},
                      $params->{'teacherId'},  
                      );
    }
  
}



############################################################
#
# function getDistribution_byId
#
#   $params:   Hash table    
#      - pid
#      - schoolYearId
#      - recipient
#   return  distribution ; 
############################################################

sub  getDistribution_byId{
    my($dbh,$params)=@_;
    
    return if($params->{'pid'} eq '' || $params->{'schoolYearId'} eq '');
    my @condVal=($params->{'pid'},$params->{'schoolYearId'} );
    my $sql_cond =" where d.pid=? && schoolYearId= ?" ;
    
    if($params->{'recipient'}){
        $sql_cond .= " && p.recipient= ?";
        push @condVal,$params->{'recipient'};
    }
   
    my $sql="SELECT d.*,p.nDistributed ,p.allocationType,p.recipient 
             FROM tbk_preparationDetails  p  inner join tbk_bookDistribution d 
             ON p.pid=d.pid  && p.ISBN=d.ISBN  &&  
                d.courseId=p.courseId && d.sectionId=p.sectionId  ";
    if($sql_cond){
        $sql .= " $sql_cond   GROUP BY d.courseId, d.sectionId, d.ISBN ";
    }
    my $sth= $dbh->prepare($sql);
    $sth->execute(@condVal);
    my @distribution;
    while( my $rec=$sth->fetchrow_hashref){
      push  @distribution,$rec;
    }

         return  \@distribution;

}


############################################################
#
# function getDistributions2ReceiverByType
#
#   $params:   Hash table    
#      - receiverType    :students OR teachers 
#      - receiverId      :studentId OR teacherId
#      - schoolYearId
#      - courseId
#      - sectionId 
#      - distributionDate
#   return  distribution ; 
############################################################

sub  getDistributions2ReceiverByType{
    my($dbh,$params)=@_;
   
    my $sql_cond;
    return if($params->{'receiverType'} eq '' ||  
              $params->{'receiverId'}   eq '' ||
              $params->{'schoolYearId'} eq '');

    my @condVal=($params->{'receiverId'},$params->{'schoolYearId'} );
    my @condVal_status;
    if($params->{'receiverType'} eq 'teachers'){
        $sql_cond =" where teacherId=? && schoolYearId= ? " ;

    }
    elsif($params->{'receiverType'} eq 'students'){
        $sql_cond =" where studentId=? && schoolYearId= ? " ;
    }

    if($params->{'bookWasCollected'}){
        $sql_cond .= " && returnDate is not null ";
    }   
     
    if($params->{'courseId'}){
        $sql_cond .= " && courseId = ?";
        push @condVal,$params->{'courseId'};
    }


    if($params->{'sectionId'}){
        $sql_cond .= " && sectionId = ?";
        push @condVal,$params->{'sectionId'};
    }
   
     if($params->{'distDate'}){
        $sql_cond .= " && distDate like ?";
        my  $dDate =  $params->{'distDate'} . "%";
        push @condVal,$dDate;
    }
   
    my $sql=" select pid,courseId,sectionId,ISBN, count(ISBN) as distributed
                        from tbk_bookDistribution "; 

    if($sql_cond){
        $sql .= " $sql_cond   GROUP BY pid,courseId,sectionId, ISBN ";
    }

    my $sth= $dbh->prepare($sql);
    $sth->execute(@condVal);
    my $distributions;
    my $sql_status ="select  returnStatus ,count(returnStatus) as sCount
                      from tbk_bookDistribution 
                      where ISBN= ? && courseId=? && sectionId=?
                      && returnDate is not null && schoolYearId=? ";
        
        if($params->{'receiverType'} eq 'teachers'){               
            $sql_status .="  && teacherId=? ";

        }
        elsif($params->{'receiverType'} eq 'students'){
            $sql_status .="  && studentId=? ";
        }

        $sql_status .= "  group by ISBN, returnStatus ";

    my $sth_status= $dbh->prepare($sql_status);

    while(my $rec =$sth->fetchrow_hashref){
            $rec->{'ok'}=0;
            $rec->{'damaged'}=0;
            $rec->{'lost'}=0;

            $sth_status->execute($rec->{'ISBN'},$rec->{'courseId'},$rec->{'sectionId'},
                             $params->{'schoolYearId'},$params->{'receiverId'});
           
            while(my $sCount =$sth_status->fetchrow_hashref ){
                $rec->{$sCount->{'returnStatus'}}= $sCount->{'sCount'};
            }

            if(!$distributions->{$rec->{'pid'}}){
                $distributions->{$rec->{'pid'}}->{'pid'}=$rec->{'pid'};
            }
            push  @{$distributions->{$rec->{'pid'}}->{'bookDist'}},$rec;

    }  
   

    my @retval;
    foreach my $pid(sort keys %{$distributions}){
        push @retval,$distributions->{$pid};
    }
    return \@retval;

}
############################################################
#
# function getDistribution_homeroom
#   
#   $teacherId
#   return distribution for homeroom ; 
############################################################

sub getDistribution_homeroom{
    my($dbh,$homeroom,$schoolYearId)=@_;

    my $sth= $dbh->prepare("select * from tbk_preparation p inner join
                            tbk_preparationDetails d on p.id=d.pid 
                            where d.nDistributed>0 && p.type='homeroom' && homeroom=? && schoolYearId= ?");
    $sth->execute($homeroom,$schoolYearId);
    my $preparations;
    while(my $rec=$sth->fetchrow_hashref){
        if(!$preparations->{$rec->{'pid'}}){
            $preparations->{$rec->{'pid'}}->{'pid'}=$rec->{'pid'};
            $preparations->{$rec->{'pid'}}->{'type'} =$rec->{'type'};
            $preparations->{$rec->{'pid'}}->{'preparedDate'} =$rec->{'preparedDate'};
            $preparations->{$rec->{'pid'}}->{'expectedReadiness'} =$rec->{'expectedReadiness'};
        }
        push  @{$preparations->{$rec->{'pid'}}->{'book2Prepare'}},$rec;
            
    }
    my @retval=();
    foreach my $p(sort keys %{$preparations}){
        push  @retval, $preparations->{$p};

    }
   return  \@retval;
}


############################################################
#
# function getDistribution_course
#   
#   $teacherId
#   
#   return distribution for course ; 
############################################################

sub getDistribution_course{
    my($dbh,$courseId,$schoolYearId)=@_;

    my $sth= $dbh->prepare("select * from tbk_preparation p inner join
                            tbk_preparationDetails d on p.id=d.pid 
                            where  d.nDistributed>0 && p.type='course' && courseId=? && schoolYearId= ?");
    $sth->execute($courseId,$schoolYearId);
    my $preparations;
    while(my $rec=$sth->fetchrow_hashref){
        if(!$preparations->{$rec->{'pid'}}){
            $preparations->{$rec->{'pid'}}->{'pid'}=$rec->{'pid'};
            $preparations->{$rec->{'pid'}}->{'type'} =$rec->{'type'};
            $preparations->{$rec->{'pid'}}->{'preparedDate'} =$rec->{'preparedDate'};
            $preparations->{$rec->{'pid'}}->{'expectedReadiness'} =$rec->{'expectedReadiness'};
        }
        push  @{$preparations->{$rec->{'pid'}}->{'book2Prepare'}},$rec;
            
    }
    my @retval=();
    foreach my $p(sort keys %{$preparations}){
        push  @retval, $preparations->{$p};

    }
   return  \@retval;

}


############################################################
#
# function getDistribution_teacher
#   
#   $params hash table of 
#       - teacherId
#       - recipient
#       - expectedReadiness
#       - schoolYearId
#
#   return distribution for teacher ; 
############################################################

sub getDistribution_teacher{
    my($dbh,$params)=@_;
    return if($params->{'teacherId'} eq '' || $params->{'schoolYearId'} eq '');
    
    my @condVal=($params->{'teacherId'},$params->{'schoolYearId'} );
    my $sql_cond ="where d.nDistributed>0 && p.type='teacher' && teacherId=? && schoolYearId= ?" ;

    my $sql = "select * from tbk_preparation p inner join
                            tbk_preparationDetails d on p.id=d.pid ";
    if($params->{'recipient'} eq 'teacher'){
        $sql_cond .= " && d.recipient='teacher'";
    }
    elsif($params->{'recipient'} eq 'student'){
        $sql_cond .= " && d.recipient='student'";
    }
    if($params->{'expectedReadiness'}){
        $sql_cond .= " && p.expectedReadiness <= ?"; 
        push @condVal,$params->{'expectedReadiness'};
    }
   $sql .= " $sql_cond  order by d.pid ,d.courseId,d.sectionId ";                           
    my $sth= $dbh->prepare($sql);

    $sth->execute(@condVal);
    my $preparations;
    while(my $rec=$sth->fetchrow_hashref){
        if(!$preparations->{$rec->{'pid'}}){
            $preparations->{$rec->{'pid'}}->{'pid'}=$rec->{'pid'};
            $preparations->{$rec->{'pid'}}->{'type'} =$rec->{'type'};
            $preparations->{$rec->{'pid'}}->{'preparedDate'} =$rec->{'preparedDate'};
            $preparations->{$rec->{'pid'}}->{'expectedReadiness'} =$rec->{'expectedReadiness'};
        }
        push  @{$preparations->{$rec->{'pid'}}->{'book2Prepare'}},$rec;
            
    }
    my @retval;
    foreach my $pid(sort keys %{$preparations}){
        push @retval,$preparations->{$pid};
    }
    return \@retval;

}

    
############################################################
#
# function getDistribution_student
#   
#   $teacherId
#   return distribution for student ; 
############################################################

sub getDistribution_student{
    my($dbh,$params)=@_;
     

    return if($params->{'studentId'} eq '' || $params->{'schoolYearId'} eq '');


    my @condVal=($params->{'studentId'},$params->{'schoolYearId'} );
    my $sql = "select   p.*, d.*, s.studentId  
               from     tbk_preparation p inner join
                      ( tbk_preparationDetails d inner join  
                            ( tbk_studentSchedule s  inner join  tbk_teacherSchedule t  
                                     on  t.courseId = s.courseId && t.sectionId = s.sectionId 
                            )  on  s.courseId = d.courseId && s.sectionId = d.sectionId 
                      )  on   p.id=d.pid";
    
    my $sql_cond =" where d.nDistributed>0 && (p.type='student' || d.recipient ='student')  && s.studentId=? && schoolYearId= ? " ;

    if($params->{'courseId'} ){
        $sql_cond .= " && d.courseId &&  = ? ";
        push @condVal,$params->{'courseId'};
    }
  
    if($params->{'teacherId'} ){
        $sql_cond .= " &&  d.teacherId &&  = ? ";
        push @condVal,$params->{'teacherId'};
    }

    if($params->{'expectedReadiness'}){
        $sql_cond .= " && p.expectedReadiness <= ?"; 
        push @condVal,$params->{'expectedReadiness'};
    }
        
    $sql .= " $sql_cond  order by d.pid ,d.courseId,d.sectionId ";
    my $sth= $dbh->prepare($sql);
    $sth->execute(@condVal);
    my $preparations;
    while(my $rec=$sth->fetchrow_hashref){

        if(!$preparations->{$rec->{'pid'}}){
            $preparations->{$rec->{'pid'}}->{'pid'}=$rec->{'pid'};
            $preparations->{$rec->{'pid'}}->{'type'} =$rec->{'type'};
            $preparations->{$rec->{'pid'}}->{'preparedDate'} =$rec->{'preparedDate'};
            $preparations->{$rec->{'pid'}}->{'expectedReadiness'} =$rec->{'expectedReadiness'};
        }
        push  @{$preparations->{$rec->{'pid'}}->{'book2Prepare'}},$rec;
            
    }
    my @retval;
    foreach my $p(sort keys %{$preparations}){
        push  @retval, $preparations->{$p};

    }
    return \@retval;

}
 
############################################################
#
# function getDistribution_teacher
#   
#   $params hash table of 
#       - teacherId
#       - recipient
#       - expectedReadiness
#       - schoolYearId
#
#   return distribution for teacher ; 
############################################################

sub getDistribution_teacher{
    my($dbh,$params)=@_;
    return if($params->{'teacherId'} eq '' || $params->{'schoolYearId'} eq '');
    
    my @condVal=($params->{'teacherId'},$params->{'schoolYearId'} );
    my $sql_cond ="where d.nDistributed>0 && p.type='teacher' && teacherId=? && schoolYearId= ?" ;

    my $sql = "select * from tbk_preparation p inner join
                            tbk_preparationDetails d on p.id=d.pid ";
    if($params->{'recipient'} eq 'teacher'){
        $sql_cond .= " && d.recipient='teacher'";
    }
    elsif($params->{'recipient'} eq 'student'){
        $sql_cond .= " && d.recipient='student'";
    }
    if($params->{'expectedReadiness'}){
        $sql_cond .= " && p.expectedReadiness <= ?"; 
        push @condVal,$params->{'expectedReadiness'};
    }
   $sql .= " $sql_cond  order by d.pid ,d.courseId,d.sectionId ";                           
    my $sth= $dbh->prepare($sql);

    $sth->execute(@condVal);
    my $preparations;
    while(my $rec=$sth->fetchrow_hashref){
        if(!$preparations->{$rec->{'pid'}}){
            $preparations->{$rec->{'pid'}}->{'pid'}=$rec->{'pid'};
            $preparations->{$rec->{'pid'}}->{'type'} =$rec->{'type'};
            $preparations->{$rec->{'pid'}}->{'preparedDate'} =$rec->{'preparedDate'};
            $preparations->{$rec->{'pid'}}->{'expectedReadiness'} =$rec->{'expectedReadiness'};
        }
        push  @{$preparations->{$rec->{'pid'}}->{'book2Prepare'}},$rec;
            
    }
    my @retval;
    foreach my $pid(sort keys %{$preparations}){
        push @retval,$preparations->{$pid};
    }
    return \@retval;

}
  
1;