package Textbook::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_add
    teacher_update
    teacher_delete
    teacher_find
    teacher_findById
    
    teacher_findByCourseSectionId
);
# Version number
$VERSION   = 0.01;      

#use utf8;
use strict;

############################################################
#
# 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;

}


############################################################
#
# 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 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 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_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 tbk_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  tbk_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 tbk_teachers where teacherId=? ";
    my $sth = $dbh->prepare($sql);
    $sth->execute($teacherId);
    $sth->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 tbk_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;
   
}
1;