package Opals::Course;

require Exporter;
@ISA       = qw(Exporter);

@EXPORT_OK = qw(

    
    course_getSchoolYearList
    course_getSchoolYearInfo
    course_getCurrentSchoolYearInfo
    course_getList
    course_add
    course_delete
    course_markDeleted
    course_update
    course_findById
    
    course_countTeachersAssigned

);

# Version number
$VERSION   = 0.01;      

#use utf8;
use strict;



sub course_getSchoolYearList {
    my ($dbh) = @_;
    my @retList;
    my $sql = "select id,schoolYear,
        DATE_FORMAT(firstDate,'%m-%d-%Y') as firstDate,
        DATE_FORMAT(lastDate,'%m-%d-%Y') as lastDate,
        curSchoolYear 
    from opl_schoolYear ";
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    while (my $rec = $sth->fetchrow_hashref){
        push @retList, $rec;
    }
    $sth->finish;
    return \@retList;
}

sub course_getSchoolYearInfo {
    my ($dbh,$schoolYear) = @_;
    my @retList;
    return undef if $schoolYear eq '';
    my $sql = "select * from opl_schoolYear where schoolYear = ?";
    my $rec = $dbh->selectrow_hashref($sql,undef,$schoolYear);
    if ($rec) {
        return $rec;
    }
    return undef;
}
sub course_getCurrentSchoolYearInfo {
    my ($dbh,$schoolYear) = @_;
    my @retList;
    return undef if $schoolYear eq '';
    my $sql = "select * from opl_schoolYear where curSchoolYear = 1 ";
    my $rec = $dbh->selectrow_hashref($sql,undef);
    if ($rec) {
        return $rec;
    }
    return undef;
}

sub course_getList {

    my ($dbh, $offset, $pSize,$sortVal, $sortOrder, $schYearId) = @_;
    my @courseList;
    $sortVal =  " courseId" if ($sortVal eq '');
    $sortVal = "cl." . $sortVal . " + 0 " if ($sortVal eq 'grade');
    my $sql = " select cl.* ,";
      $sql .= "  sy.schoolYear as schoolYearName, ts.teacherId ";
      $sql .= " from tb_courseList as cl inner join opl_schoolYear as sy on cl.schoolYear = sy.id ";
      $sql .= " && cl.schoolYear = $schYearId "  if ($schYearId && $schYearId >= 0) ;
      $sql .= " left join (tb_teacherSchedule as ts inner join opl_user as u on u.uid = ts.teacherId ) on ts.courseId = cl.courseId ";
      #$sql .= " where  ts.deleted is NULL ";
      $sql .= " group by cl.courseId order by $sortVal ";

    my $sqlCount = "select count(*) from tb_courseList as cl inner join opl_schoolYear as sy on cl.schoolYear = sy.id ";
    $sqlCount .= " where cl.schoolYear = $schYearId "  if ($schYearId && $schYearId >= 0);
    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 @courseList, $rec;
        }
    }
    return ($resultSize,\@courseList);

}
#*****************************************
#
# function course_add
#   $params : hash table contains values of 
#       - courseCode
#       - courseName
#       - descriptions
#       - grade
#       - schoolYear
#       - instrctionHours
#       - prerequisite
# return courseId
#
#*****************************************

sub course_add{
    my ($dbh,$params) = @_;
    my @fields=('courseCode',
                'courseName',
                'descriptions',
                'grade',
                'schoolYear',
                'instructionHours',
                'prerequisite');

    my $sql_fields = "";
    my @fieldData;
    foreach my $f(@fields){
        if ($params->{$f} ne ''){
            push @fieldData, $params->{$f};
            $sql_fields .= " , " if ($sql_fields ne "");
            $sql_fields .= "$f =? ";
        }
    }
    return 0 if ($sql_fields eq '');
    my $sql = "insert into tb_courseList set $sql_fields ";
    my $sth = $dbh->prepare($sql);
    $sth->execute(@fieldData);
    my $courseId = $dbh->{'mysql_insertid'};
    
    return $courseId;
}

sub course_delete {

    my ($dbh, $courseId) = @_;
    return if ($courseId eq '');
    my $sth_course = $dbh->prepare('delete from tb_courseList where courseId = ?');
    $sth_course->execute($courseId);
    $sth_course->finish;
    my $sth_tSchedule =  $dbh->prepare('delete from tb_teacherSchedule where courseId = ?');
    $sth_tSchedule->execute($courseId);
    $sth_tSchedule->finish;

}

sub course_markDeleted {

    my ($dbh, $params) = @_;
    return if ($params->{'courseId'} eq '');
    my $sth = $dbh->prepare('update tb_teacherSchedule set deleted = 1 where courseId = ?');
    $sth->execute($params->{'courseId'});
    $sth->finish;
    return 1;

}


sub course_find {

    my ($dbh, $params) = @_;
    my @courseList;
    my $sql = "select * from tb_courseList ";
    my $sql_cond="";
    my @condVals = ();

    if ($params->{'courseId'} ne ''){
        $sql_cond .= "courseId=? ";
        push @condVals, $params->{'courseId'};
    }
    return @courseList if ($sql_cond eq '');
    $sql .= "where $sql_cond ";
    my $sth = $dbh->prepare($sql);
    $sth->execute(@condVals);
    while ( my $rec = $sth->fetchrow_hashref) {
        push @courseList, $rec;
    }
    $sth->finish;
    return \@courseList;
}

sub  course_update{
    my ($dbh,$params) = @_;
    my @fields=('courseCode',
                'courseName',
                'descriptions',
                'grade',
                'schoolYear',
                'instructionHours',
                'prerequisite');
    return if($params->{'courseId'} eq '');
    my $sql_fields ="";
    my @fieldData;
    foreach my $f(@fields){
        if($params->{$f}){
            push @fieldData,$params->{$f};
            $sql_fields .= " , " if($sql_fields ne "");
            $sql_fields .= "$f =? ";
        }
    }
    push  @fieldData,$params->{'courseId'};
    return if ($sql_fields eq '');
    my $sql ="update tb_courseList set $sql_fields where courseId =?";
    my $sth =$dbh->prepare($sql);
    $sth->execute(@fieldData);
    $sth->finish;
    return;
}

sub course_findById {

    my ($dbh, $courseId ) = @_;
    return undef if ($courseId eq '');
    my $courseList = course_find($dbh, {courseId=>$courseId});
    if (scalar (@{$courseList} == 1 )){
        return @$courseList[0];
    }
    return undef;
}

sub course_countTeachersAssigned{
    my ($dbh, $schoolYearId) = @_;
    my %nTeachersList = ();

    my $sql = "select cl.courseCode as courseCode, count(ts.teacherId) as count
        from tb_courseList cl left join tb_teacherSchedule ts on ts.courseId = cl.courseId && cl.schoolYear = $schoolYearId
        left join opl_user u on u.uid = ts.teacherId
        group by courseCode ";
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    while ( my $rec = $sth->fetchrow_hashref) {
        $nTeachersList{$rec->{'courseCode'}} = $rec->{'count'};
    }
    return \%nTeachersList;
}

1;