package Opals::TeacherCourse;

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

    teacherCourse_assign
    teacherCourse_getList
    teacherCourse_getList
    teacherCourse_countCoursesAssigned
    teacherCourse_delete

    teacher_getCatCode
);

use Opals::Constant;

# Version number
$VERSION   = 0.01;      

#use utf8;
use strict;

sub teacherCourse_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 $tID =  $dbh->{'mysql_insertid'};
    return $tID;

}


sub teacherCourse_getList{
    my ($dbh, $schYearId,$catCode_teacher) = @_;
    my @teacherCourseList;
    my $sql = "select ts.id, u.uId as tId, cl.courseId, cl.courseCode, cl.courseName 
                from opl_user u 
                    inner join tb_teacherSchedule ts on u.uId = ts.teacherId && u.categorycode = ?
                    inner join tb_courseList cl on cl.courseId = ts.courseId && cl.schoolYear = ? ";
    my $sth = $dbh->prepare($sql);
    $sth->execute($catCode_teacher, $schYearId);
    my $i = 0;
    while ( my $rec = $sth->fetchrow_hashref) {
        $rec->{'i'} = $i;
        push @teacherCourseList, $rec;
        $i++;
    }
    $sth->finish;
    return \@teacherCourseList;
    
}

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

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

}


sub teacher_getCatCode{

    my ($dbh) = @_;
    my $sql = "select catid from opl_category where catname regexp '^teacher\$'";
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    my $ret = $sth->fetchrow_array();
    $sth->finish;
    return $ret?$ret:0;

}
1;