package Opals::Teachers;

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

    teacher_getListByCourse
    teacher_getList

    teacher_add
    teacher_update
    teacher_delete
    teacher_find
    teacher_findById

    teacher_course_assign
    teacher_course_getList
    teacher_countCoursesAssigned
    teacher_course_delete
);
# Version number
$VERSION   = 0.01;      

#use utf8;
use strict;

=item
############################################################
#
# function teacher_findByCourseSectionId
#   
#   $params: a hash table 
#               - courseCode,
#               - sectionId,
#               - schoolYear
#
############################################################
sub teacher_findByCourseSectionId {
    my ($dbh,$params) = @_;
    my($sql_fields,$sql_tables,$sql_cond)=("","","");
    $sql_fields ="select distinct  t.*  ";
    $sql_tables= "from tbk_teachers t inner join tbk_teacherSchedule ts on t.teacherId=ts.teacherId 
             inner join tbk_courseList cl on cl.courseId=ts.courseId ";
    $sql_cond=" where ts.courseCode =? && ts.sectionId =?  && schoolYear=? ";
    my @condVal=($params->{"courseCode"},$params->{"sectionId"},$params->{"schoolYear"});
    my $sql =$sql_fields . $sql_tables .  $sql_cond . " order by ts.sectionId ";
   
   my @teacherList;
   my $sth = $dbh->prepare($sql);
   $sth->execute(@condVal);
   
   while ( my $rec = $sth->fetchrow_hashref ){
       push @teacherList,$rec;
   }

   if( scalar (@teacherList) ==1 ){
        return @teacherList[0];
    }

   return undef;

}
=cut

############################################################
#
# function teacher_getListByCourse
#   
#   $params: a hash table 
#               - courseCode,
#               - section
#               - schoolYear
#
############################################################
sub teacher_getListByCourse {
    my ($dbh,$params) = @_;
    my($sql_fields,$sql_tables,$sql_cond)=("","","");
    $sql_fields ="select distinct  t.*  ";
    $sql_tables= "from tb_teachers t inner join tbk_teacherSchedule ts on t.teacherId=ts.teacherId 
             inner join tb_courseList cl on cl.courseId=ts.courseId ";
    $sql_cond=" where cl.courseCode =? && schoolYear=? ";
    my @condVal=($params->{"courseCode"},$params->{"schoolYear"});
    if($params->{"sectionId"} ne ""){
        $sql_cond .=" && ts.sectionId= ? ";
        push @condVal,$params->{"sectionId"};
    }
    my $sql =$sql_fields . $sql_tables .  $sql_cond . " order by ts.sectionId ";
   my @teacherList;
   my $sth = $dbh->prepare($sql);
   $sth->execute(@condVal);
   while ( my $rec = $sth->fetchrow_hashref ){
       push @teacherList,$rec;
   }
   return \@teacherList;

}

###########################################################
# function teacher_getList
#   
############################################################
sub teacher_getList {
    
    my ($dbh, $offset, $pSize, $sortVal, $sortOrder) = @_;
    my @teacherList;
    
    $sortVal = " id " if ($sortVal eq '');
    my $sql = "select * from tb_teachers order by $sortVal ";
    my $sqlCount = "select count(id) from tb_teachers ";
    my ($resultSize) = $dbh->selectrow_array($sqlCount, undef);
    my $availRange = $resultSize - $offset + 1;
    if ($availRange > $pSize){
        $availRange = $pSize;    }
    elsif ($availRange <= 0){
        $availRange = $resultSize % $pSize;
        $availRange = $pSize if ($availRange == 0);
        $offset = $resultSize - $availRange + 1;    }

    if ($sortOrder && $sortOrder == 2){
        $sql .= " desc ";
    }
    else{
        $sql .= " asc ";
    }
    if ($resultSize > 0){
        $sql  .= " LIMIT $offset , $availRange " if ($offset >= 0 && $pSize > 0);
        my $sth = $dbh->prepare($sql);
        $sth->execute();
        while(my $rec = $sth->fetchrow_hashref){
            push @teacherList, $rec;
        }
    }
    return ($resultSize, \@teacherList);
    
}

############################################################
#
# function teacher_add
#   
#   $params: a hash table contains fields from tbk_teachers table
#   
#    return id;
#
############################################################
sub teacher_add {
    my ($dbh,$params) = @_;
    return -1 if($params->{'teacherId'} eq '' || $params->{'lastName'} eq '' );
    my @fields=(  'teacherId' ,
          'lastName' ,
          'firstName' ,
          'addrLine1' ,
          'addrLine2' ,
          'city' ,
          'state' ,
          'zip' ,
          'phone' ,
          'country',
          'email' ,
          'birthday',
          'importId',
          'gender' ,
          'status' ,
          'notes' 
          );

    my $sql ="insert into tb_teachers set teacherId= ? ";
    my @fVals =($params->{'teacherId'});
    for(my $i=1; $i< scalar (@fields); $i++){
        if($params->{$fields[$i]} ne ''){

            $sql .= ", $fields[$i] = ?";
            push @fVals,$params->{$fields[$i]};
        }
    }

    my $sth = $dbh->prepare($sql);
    $sth->execute(@fVals);
    my $id =  $dbh->{'mysql_insertid'};
    $sth->finish;
    return $id
}
############################################################
#
# function teacher_update
#   
#   $params: a hash table contains fields from tbk_students table
#   
#
############################################################
sub teacher_update {
    my ($dbh,$params) = @_;
    return  if($params->{'teacherId'} eq '');
    my @fields=(  'teacherId' ,
          'lastName' ,
          'firstName' ,
          'addrLine1' ,
          'addrLine2' ,
          'city' ,
          'state' ,
          'zip' ,
          'phone' ,
          'country',
          'email' ,
          'birthday',
          'importId',
          'gender' ,
          'status' ,
          'notes' 
          );


    my $sql ="update  tb_teachers  ";
    my $sql_fields="";
    my @fVals=();
    for(my $i=1; $i< scalar (@fields); $i++){
        if($params->{$fields[$i]} ne ''){
            $sql_fields .=", " if ($sql_fields ne '');
            $sql_fields .= " $fields[$i] = ?";
            push @fVals,$params->{$fields[$i]};
        }
    }
    return  if($sql_fields eq '');
    $sql .= " set $sql_fields where teacherId= ?";
    push @fVals,$params->{'teacherId'};
    my $sth = $dbh->prepare($sql);
    $sth->execute(@fVals);
    $sth->finish;
}
############################################################
#
# function teacher_delete
#   
#
############################################################
sub teacher_delete {
    my ($dbh,$teacherId) = @_;
    return  if($teacherId eq '');
    
    my $sql ="delete from tb_teachers where teacherId=? limit 1";
    my $sth = $dbh->prepare($sql);
    $sth->execute($teacherId);
    $sth->finish;
    my $sth_tSchedule =  $dbh->prepare('delete from tb_teacherSchedule where teacherId = ?');
    $sth_tSchedule->execute($teacherId);
    $sth_tSchedule->finish;

}
############################################################
#
# function teacher_find
#   
#   $params: a hash table contains 
#           - teacherId
#           - lastName
#           - firstName
#   
#    return teacher list;
#
############################################################
sub teacher_find{
    my ($dbh,$params) = @_;
    
    my @teacherList;
    my $sql ="select * from tb_teachers  ";
    my $sql_cond="";
    my @condVals=();
    if($params->{'teacherId'} ne ''){
        $sql_cond .= " or " if($sql_cond ne '');
        $sql_cond .= "   teacherId=?";
        push @condVals,$params->{'teacherId'}
    }
    if($params->{'lastName'} ne ''){
        $sql_cond .= " or " if($sql_cond ne '');
        $sql_cond .= " lastName =?";
        push @condVals,$params->{'lastName'}
    }

    if($params->{'firstName'} ne ''){
        $sql_cond .= " or " if($sql_cond ne '');
        $sql_cond .= " firstName =?";
        push @condVals,$params->{'firstName'}
    }
    return @teacherList if($sql_cond eq'');
    $sql .=" where $sql_cond ";
    my $sth = $dbh->prepare($sql);
    $sth->execute(@condVals);

    while ( my $rec = $sth->fetchrow_hashref ){
       push @teacherList,$rec;
    }
    $sth->finish;
    return \@teacherList;
}
############################################################
#
# function teacher_findById
#   
#   $params: a hash table contains 
#           - teacherId
#   
#    return hash table of teacher info;
#
############################################################
sub teacher_findById{
    my ($dbh,$teacherId) = @_;
    return undef if($teacherId eq '');
    my $teacherList = teacher_find($dbh,{teacherId=>$teacherId});
    if( scalar (@$teacherList) ==1 ){
        return @$teacherList[0];
    }
    return undef;
   
}


sub teacher_course_assign{
    
    my ($dbh, $teacherId, $courseId, $courseCode) = @_;
    my $sql = 'insert into tb_teacherSchedule set courseId = ? ,courseCode=?, teacherId = ?, createdDate = now()';
    my $sth = $dbh->prepare($sql);
    $sth->execute($courseId, $courseCode,$teacherId);
    my $tScheduleID =  $dbh->{'mysql_insertid'};
    return $tScheduleID;

}

sub teacher_course_getList{
    my ($dbh, $schYearId) = @_;
    my @teacherCourseList;
    my $sql = "select t.teacherId, cl.courseId, cl.courseCode, cl.courseName 
                from tb_teachers t 
                    inner join tb_teacherSchedule ts using(teacherId)
                    inner join tb_courseList cl using (courseId) ";
    $sql .= " where cl.schoolYear = $schYearId " if ($schYearId && $schYearId > 0);
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    my $i = 0;
    while ( my $rec = $sth->fetchrow_hashref) {
        $rec->{'i'} = $i;
        push @teacherCourseList, $rec;
        $i++;
    }
    $sth->finish;
    return \@teacherCourseList;
    
}

sub teacher_countCoursesAssigned{
    my ($dbh, $schYearId) = @_;
    my %nCoursesList = ();
    my $sql = "select t.teacherId as tId , count(distinct ts.courseCode) as count
            from tb_teachers t left join
                (tb_teacherSchedule ts  inner join tb_courseList cl) 
                ON ts.courseCode = cl.courseCode AND t.teacherId = ts.teacherId " ;
    $sql .= " AND cl.schoolYear = $schYearId " if ($schYearId && $schYearId > 0);
    $sql .= " group by t.teacherId ";
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    while ( my $rec = $sth->fetchrow_hashref) {
        $nCoursesList{$rec->{'tId'}} = $rec->{'count'};
    }
    return \%nCoursesList;
}

sub teacher_course_delete{
    
    my ($dbh, $teacherId) = @_;
    my $sql ="delete from tb_teacherSchedule where teacherId=? ";
    my $sth = $dbh->prepare($sql);
    $sth->execute($teacherId);
    $sth->finish;

}

1;