#!/usr/bin/perl

use strict;
use CGI;

use Opals::Context;
use Opals::Template qw(

    tmpl_read
    tmpl_write
    tmpl_rangedPageList

);

use Opals::TeacherCourse qw(
    
    teacherCourse_getList
    teacher_getCatCode
    
);
use Opals::Course qw(
    
    course_getSchoolYearList
    course_getSchoolYearInfo

);

use Opals::Constant;

my $dbh = Opals::Context->dbh();
END { $dbh->disconnect(); }

my $cgi = CGI->new;

my ($permission, $cookieList, $template) = tmpl_read(
    {
        dbh             => $dbh,
        cgi             => $cgi,
        tmplFile        => '/txtbk/ajax/teacher/getTeacherCourseList.tmpl',
        #reqPermission   => 'tb_user_edit',
    }
);

my $params = {};

my $schoolYearList      = course_getSchoolYearList($dbh);
my $curSchoolYearId = 0;



foreach my $y (@{$schoolYearList}){
    if ($y->{'curSchoolYear'} == 1){
        $curSchoolYearId = $y->{'id'};
        last;
    }
}


my $catCode_teacher = teacher_getCatCode($dbh);
    my $teacherCourseList;
    $teacherCourseList = getTeacherCourseList($dbh,$curSchoolYearId , $catCode_teacher);
    my $tcList;

    foreach my $t (@{$teacherCourseList}){
        if (!$tcList->{$t->{'tId'}}){
            $tcList->{$t->{'tId'}}->{'tId'} = $t->{'tId'};
            $tcList->{$t->{'tId'}}->{'firstname'} = $t->{'firstname'};
            $tcList->{$t->{'tId'}}->{'lastname'} = $t->{'lastname'};
        }
        push @{$tcList->{$t->{'tId'}}->{'courseList'}} , {
            tsId        => $t->{'tsId'},
            courseId    => $t->{'courseId'},  
            courseCode  => $t->{'courseCode'},
            courseName  => $t->{'courseName'},
        }
    }
    my @courstListByTeacher;
    foreach my $t (sort keys %{$tcList}){
        push @courstListByTeacher, $tcList->{$t};
    }
    @courstListByTeacher = sort { lc($a->{"lastname"}) cmp lc($b->{"lastname"})} @courstListByTeacher;

$template->param(
    teacherCourseList => \@courstListByTeacher
    
);

tmpl_write($dbh, $cgi, $cookieList, $template);


sub getTeacherCourseList {
    my ($dbh,$curSchoolYearId ,$catCode_teacher) = @_;
        
    my $sql = "select  u.uId as tId, u.firstname,u.lastname , ts.id as tsId, cl.courseId, cl.courseCode, cl.courseName 
	    from opl_user u 
        left outer join tb_teacherSchedule ts on u.uId = ts.teacherId 
        left outer join tb_courseList cl on cl.courseId = ts.courseId && cl.schoolYear = ?
        where u.categorycode = ?
        order by u.lastname, u.firstname ";
    my  @val =($curSchoolYearId, $catCode_teacher);
    my $ret = $dbh->selectall_arrayref($sql, {Slice => {}}, @val);
    return $ret;

}