package Textbook::Preparation;

require Exporter;
@ISA       = qw(Exporter);
# Symbols to be exported by default
#@EXPORT    = qw(
#    opl_
#);
# Symbols to be exported on request
@EXPORT_OK = qw(
    prepare_student
    prepare_teacher
    prepare_course
    prepare_homeroom

    getPreparation_byId
    getPreparation_teacher
    getPreparation_student
    getPreparation_course
    getPreparation_homeroom
);
# Version number
$VERSION   = 0.01;      

#use utf8;
use strict;

############################################################
#
# function prepare_student
#   
#   $params: a hash table 
#               - expectedReadiness
#               - preparedBy
#               - schoolYearId
#               - bookPrepared :reference to an array of hash 
#                  
#                    + studentId
#                    + courseCode
#                    + sectionId
#                    + nCopies
#                    + ISBN
#
#  return preparation id ; 
#
############################################################
sub prepare_student{
    my($dbh,$params)=@_;
    
    return 0 if(scalar(@{$params->{'book2Prepare'}}) <1);
    $params->{'type'}='student';
    
    foreach my $p (@{$params->{'book2Prepare'}}){
        $p->{'teacherId'}='';
        $p->{'homeroom'}='';
        $p->{'nStudents'}=0;
        $p->{'allocationType'}='perStudent';
        $p->{'nCopiesPerAllocation'}=$p->{'nCopies'};
        $p->{'recipient'}='student';
        $p->{'total'}=$p->{'nCopies'};
    }
    
    return doPreparation($dbh,$params);


    
}
############################################################
#
# function prepare_teacher
#   
#   $params: a hash table 
#               - expectedReadiness
#               - preparedBy
#               - schoolYearId
#               - bookPrepared :reference to an array of hash 
#
#                   + teacherId
#                   + courseCode
#                   + sectionId
#                   + ISBN
#                   + nStudents
#                   + allocationType
#                   + nCopiesPerAllocation
#                   + recipient
#
#  return preparation id ; 
#
############################################################


sub prepare_teacher{
    my($dbh,$params)=@_;
    return 0 if(scalar(@{$params->{'book2Prepare'}}) <1);
    $params->{'type'}='teacher';
    foreach my $p (@{$params->{'book2Prepare'}}){
        $p->{'studendId'}='';
        $p->{'homeroom'}='';
        my $total=0;
        if($p->{'allocationType'} eq 'perStudent'){
            $total= $p->{'nStudents'} * $p->{'nCopiesPerAllocation'}
        }
        else{
            $total = $p->{'nCopiesPerAllocation'};
        }
        $p->{'total'}=$total;
    }
    return doPreparation($dbh,$params);

    
}
############################################################
#
# function prepare_course
#   
#   $params: a hash table 
#               - expectedReadiness
#               - preparedBy
#               - schoolYearId
#               - bookPrepared :reference to an array of hash 
#
#                    + courseCode
#                    + sectionId
#                    + ISBN
#                    + nStudents
#                    + allocationType
#                    + nCopiesPerAllocation
#                    + recipient
#                
#  return preparation id ; 
#
############################################################
sub prepare_course{
    my($dbh,$params)=@_;

    return 0 if(scalar(@{$params->{'book2Prepare'}}) <1);
    $params->{'type'}='course';

    foreach my $p (@{$params->{'book2Prepare'}}){
        $p->{'teacherId'}='';
        $p->{'studendId'}='';
        $p->{'homeroom'}='';
        my $total=0;
        if($p->{'allocationType'} eq 'perStudent'){
            $total= $p->{'nStudents'} * $p->{'nCopiesPerAllocation'}
        }
        else{
            $total = $p->{'nCopiesPerAllocation'};
        }
        $p->{'total'}=$total;

    }
    return doPreparation($dbh,$params);


    
}
############################################################
#
# function prepare_homeroom
#   
#   $params: a hash table 
#               - expectedReadiness
#               - preparedBy
#               - schoolYearId
#               - bookPrepared :reference to an array of hash 
#
#                    + homeroom 
#                    + courseCode
#                    + sectionId
#                    + ISBN
#                    + nStudents
#                    + allocationType
#                    + nCopiesPerAllocation
#                    + recipient
#
#  return preparation id ; 
#
############################################################
sub prepare_homeroom{
    my($dbh,$params)=@_;

   return 0 if(scalar(@{$params->{'book2Prepare'}}) <1);
    $params->{'type'}='homeroom';

    foreach my $p (@{$params->{'book2Prepare'}}){
        $p->{'teacherId'}='';
        $p->{'studendId'}='';
        my $total=0;
        if($p->{'allocationType'} eq 'perStudent'){
            $total= $p->{'nStudents'} * $p->{'nCopiesPerAllocation'}
        }
        else{
            $total = $p->{'nCopiesPerAllocation'};
        }
        $p->{'total'}=$total;

    }
    return doPreparation($dbh,$params);
    
    
}
     
############################################################
#
# function doPreparation
#   
#   $params: a hash table 
#               - type :'teachers','students','courses','homeroom','request' 
#               - expetedRediness
#               - schoolYear
#               - preparedBy
#               - book2Prepare : reference to an array of hash of following keys
#                       + courseCode
#                       + sectionId
#                       + teacherId
#                       + homeroom
#                       + allocationType 'CperStudent','perClass','perSection.'
#                       + nStudents 
#                       + numCopies
#                       + recipient :teacher,student
#                       + ISBN
#
#  return pid : preparation id ; 
############################################################


sub doPreparation{
    my($dbh,$params)=@_;
    my $books =$params->{'book2Prepare'};
    return 0 if(scalar(@$books) <1);

    my $sql_preparation=<<_SQL_;
    insert into tbk_preparation set
            type =?,
            expectedReadiness =?,
            preparedBy =?,
            schoolYearId =?,
            preparedDate =now()
       
_SQL_

    my $sql_detail=<<_SQL_;
    insert into tbk_preparationDetails set 
            pid=?,
            courseId =?,
            sectionId =?,
            teacherId =?,
            studentId =?,
            homeroom =?,
            nStudents =?,
            allocationType =?,
            nCopiesPerAllocation =?, 
            total =?,
            recipient =?,
            ISBN =?
_SQL_

    my $sth = $dbh->prepare($sql_preparation);
    $sth->execute($params->{'type'},
                  $params->{'expectedReadiness'},
                  $params->{'preparedBy'},
                  $params->{'schoolYearId'});

    my $pid = $dbh->{'mysql_insertid'};
    return 0 if($pid<1);
     $sth = $dbh->prepare($sql_detail);
     foreach my $b (@$books){
        $sth->execute($pid,
                  $b->{'courseId'},
                  $b->{'sectionId'},
                  $b->{'teacherId'},
                  $b->{'studentId'},
                  $b->{'homeroom'},
                  $b->{'nStudents'},
                  $b->{'allocationType'},
                  $b->{'nCopiesPerAllocation'},
                  $b->{'total'},
                  $b->{'recipient'},
                  $b->{'ISBN'}
     ); 
     }
     return $pid

}
############################################################
#
# function getPreparation_byId
#   
#   $pid
#   $schoolYearId
#   return preparation  ; 
############################################################

sub getPreparation_byId {
    my($dbh,$pid,$schoolYearId)=@_;

    my $sth= $dbh->prepare("select id as pid, type, expectedReadiness,expiryDate,preparedDate,schoolYearId
                            from tbk_preparation p 
                            where p.id=? && schoolYearId= ?");
    $sth->execute($pid,$schoolYearId);
    my $preparations;
    if(my $rec=$sth->fetchrow_hashref){
        $preparations=$rec;
        $sth= $dbh->prepare("select * 
                            from tbk_preparationDetails d 
                            where pid=?");

        $sth->execute($pid);
        while(my $pDetail=$sth->fetchrow_hashref){
            push  @{$preparations->{'book2Prepare'}},$pDetail;
                
        }

    }
    return $preparations;

}



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

sub getPreparation_teacher{
    my($dbh,$params)=@_; 
    
    return if($params->{'teacherId'} eq '' || $params->{'schoolYearId'} eq '');
    
    my @condVal=($params->{'teacherId'},$params->{'schoolYearId'} );
    my $sql_cond ="where 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 getPreparation_student
#   
#   $teacherId
#   return preparation for student ; 
############################################################

sub getPreparation_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 (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 getPreparation_course
#   
#   $teacherId
#   
#   return preparation for course ; 
############################################################

sub getPreparation_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 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 getPreparation_homeroom
#   
#   $teacherId
#   return preparation for homeroom ; 
############################################################

sub getPreparation_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 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;
}

=item
############################################################
#
# function getPreparation
#   
#   $params hash table of 
#       - type   :teacher, student ,course, homeroom ,request
#       - recipient
#       - expectedReadiness
#       - schoolYearId
#
#   return preparation for teacher ; 
############################################################

sub getPreparation{
    my($dbh,$params)=@_;
    return if($params->{$params->{'schoolYearId'} eq '');

    my @condVal=($params->{'schoolYearId'} );
    my $sql_cond =" where 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->{'type'} ne ''){
        $sql_cond .= " && p.type =?";
        push @condVal,$params->{'type'};
    }

    $sql .= " $sql_cond  order by p.type, d.pid ,d.courseId,d.sectionId ";
    $sth->execute(@condVal);
    
     
}
=cut

1;