package Textbook::Students;

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

    student_add
    student_update
    student_delete
    student_find
    student_findById
);
# Version number
$VERSION   = 0.01;      

#use utf8;
use strict;

############################################################
#
# function student_getListByCourse
#   
#   $params: a hash table may have 0 or more of the following key
#               - courseCode,
#               - section
#               - schoolYear
#   return student list
############################################################
sub student_getListByCourse {
    my ($dbh,$params) = @_;
    my($sql_fields,$sql_tables,$sql_cond)=("","","");
    $sql_fields ="select ss.sectionId, s.*  ";
    $sql_tables= "from tbk_students s inner join tbk_studentSchedule ss on s.studentId=ss.studentId 
             inner join tbk_courseList cs on cs.courseId=ss.courseId ";
    $sql_cond=" where cs.courseCode =? && schoolYear=? ";
    my @condVal=($params->{"courseCode"},$params->{"schoolYear"});
    if($params->{"sectionId"} ne ""){
        $sql_cond .=" && ss.sectionId= ? ";
        push @condVal,$params->{"sectionId"};
    }
    my $sql =$sql_fields . $sql_tables .  $sql_cond . " order by ss.sectionId ";
   my $stdList;
   my $sth = $dbh->prepare($sql);
   $sth->execute(@condVal);

   while ( my $rec = $sth->fetchrow_hashref ){
       push @{$stdList->{$rec->{'sectionId'}}},$rec;
   }
   return $stdList;

}
############################################################
#
# function student_getListByCourseSection
#   
#   $params: a hash table may have 0 or more of the following key
#               - courseCode,
#               - section
#               - schoolYear
#   return student list
############################################################
sub student_getListByCourseSection {
    my ($dbh,$params) = @_;
    my($sql_fields,$sql_tables,$sql_cond)=("","","");
    $sql_fields ="select ss.sectionId, s.*  ";
    $sql_tables= "from tbk_students s inner join tbk_studentSchedule ss on s.studentId=ss.studentId 
             inner join tbk_courseList cs on cs.courseId=ss.courseId ";
    $sql_cond=" where cs.courseCode =? && ss.sectionId=? && schoolYear=? ";
    my @condVal=($params->{"courseCode"}, $params->{"sectionId"}  ,$params->{"schoolYear"});
   my $sql =$sql_fields . $sql_tables .  $sql_cond . " order by s.studentId ";
   my $stdList;
   my $sth = $dbh->prepare($sql);
   $sth->execute(@condVal);

   while ( my $rec = $sth->fetchrow_hashref ){
       push @{$stdList->{$rec->{'sectionId'}}},$rec;
   }
   
   return $stdList->{$params->{'sectionId'}};

}

############################################################
#
# function student_getListByTeacher
#   
#   $params: a hash table 
#               - teacherId (required)
#               - courseCode
#               - section
#               - schoolYear (required)
#
############################################################
sub  student_getListByTeacher{
    my ($dbh,$params) = @_;
    my($sql_fields,$sql_tables,$sql_cond)=("","","");
    $sql_fields ="select ss.sectionId, s.*  ";
    $sql_tables= "from tbk_students s inner join tbk_studentSchedule ss on s.studentId=ss.studentId 
                  inner join (select ts.courseId,ts.sectionId ,ts.courseCode
                              from tbk_courseList cl inner join tbk_teacherSchedule ts on cl.courseId=ts.courseId
                              where ts.teacherId =? && schoolYear=?) as t
                  on t.courseId=ss.courseId && t.sectionId=ss.sectionId ";
    my @condVal=($params->{"teacherId"},$params->{"schoolYear"});
    if($params->{"courseCode"} ne ''){
        $sql_cond=" where t.courseCode =?  ";
        push @condVal,$params->{"courseCode"};
    
        if($params->{"sectionId"} ne ""){
            $sql_cond .=" && ss.sectionId= ? ";
            push @condVal,$params->{"sectionId"};
        }
    }
    my $sql =$sql_fields . $sql_tables .  $sql_cond . " order by ss.sectionId ";
    my $stdList;
    my $sth = $dbh->prepare($sql);
    $sth->execute(@condVal);
    while ( my $rec = $sth->fetchrow_hashref ){
       push @{$stdList->{$rec->{'sectionId'}}},$rec;
    }
    return $stdList;

}

############################################################
#
# function student_getListByHomeroom
#   
#   $params: a hash table may have 0 or more of the following key
#               - homeroom,
#
############################################################
sub student_getListByHomeroom {
    my ($dbh,$homeroom) = @_;
    my $sql ="select  s.*  from tbk_students s  where s.homeroom=? ";
    my @stdList;
    my $sth = $dbh->prepare($sql);
    $sth->execute($homeroom);

    while ( my $rec = $sth->fetchrow_hashref ){
       push @stdList,$rec;
   }
   return \@stdList;
}

############################################################
#
# function student_add
#   
#   $params: a hash table contains fields from tbk_students table
#   
#    return id;
#
############################################################
sub student_add {
    my ($dbh,$params) = @_;
    return -1 if($params->{'studentId'} eq '' || $params->{'lastName'} eq '' );
    my @fields=( 'studentId' ,
              'lastName' ,
              'firstName' ,
              'addrLine1' ,
              'addrLine2' ,
              'city' ,
              'state' ,
              'zip' ,
              'country' ,
              'grade' ,
              'homeroom' ,
              'phone' ,
              'userBarcode',
              'created',
              'importId' ,
              'email' ,
              'birthday' ,
              'gender' ,
              'status' ,
              'notes',
              'yearGraduation' ,
              'building' ,
              'teacher' ,
              'fax' );

    my $sql ="insert into tbk_students set studentId= ? ";
    my @fVals =($params->{'studentId'});
    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 student_update
#   
#   $params: a hash table contains fields from tbk_students table
#   
#
############################################################
sub student_update {
    my ($dbh,$params) = @_;
    return  if($params->{'studentId'} eq '');
    my @fields=( 'studentId' ,
              'lastName' ,
              'firstName' ,
              'addrLine1' ,
              'addrLine2' ,
              'city' ,
              'state' ,
              'zip' ,
              'country' ,
              'grade' ,
              'homeroom' ,
              'phone' ,
              'userBarcode',
              'created',
              'importId' ,
              'email' ,
              'birthday' ,
              'gender' ,
              'status' ,
              'notes',
              'yearGraduation' ,
              'building' ,
              'teacher' ,
              'fax' );

    my $sql ="update  tbk_students  ";
    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 studentId= ?";
    push @fVals,$params->{'studentId'};
    my $sth = $dbh->prepare($sql);
    $sth->execute(@fVals);
    $sth->finish;
}
############################################################
#
# function student_delete
#   
#
############################################################
sub student_delete {
    my ($dbh,$studentId) = @_;
    return  if($studentId eq '');
    
    my $sql ="delete from tbk_students where studentId=? ";
    my $sth = $dbh->prepare($sql);
    $sth->execute($studentId);
    $sth->finish;
}
############################################################
#
# function student_find
#   
#   $params: a hash table contains 
#           - studentId
#           - lastName
#           - firstName
#           - userBarcode
#   
#    return student list;
#
############################################################
sub student_find{
    my ($dbh,$params) = @_;
    
    my @studentList;
    my $sql ="select * from tbk_students  ";
    my $sql_cond="";
    my @condVals=();
    if($params->{'studentId'} ne ''){
        $sql_cond .= " or " if($sql_cond ne '');
        $sql_cond .= "  studentId =?";
        push @condVals,$params->{'studentId'}
    }
    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'}
    }
    if($params->{'userBarcode'} ne ''){
        $sql_cond .= " or " if($sql_cond ne '');
        $sql_cond .= " userBarcode =?";
        push @condVals,$params->{'userBarcode'}
    }
    return @studentList if($sql_cond eq'');
    $sql .=" where $sql_cond ";
    my $sth = $dbh->prepare($sql);
    $sth->execute(@condVals);

    while ( my $rec = $sth->fetchrow_hashref ){
       push @studentList,$rec;
    }
    $sth->finish;
    return \@studentList;
}

############################################################
#
# function student_findById
#   
#   
#    return student ;
#
############################################################
sub student_findById{
    my ($dbh,$studentId) = @_;
    return undef if($studentId eq '');
    my $studentList = student_find($dbh,{studentId=>$studentId});
    if( scalar (@$studentList) ==1 ){
        return @$studentList[0];
    }
    return undef;

}
1;