#!/usr/bin/perl

use strict;
use CGI;

use Time::localtime;

use Opals::Context;

use Opals::Constant;

use Opals::Template qw(
    tmpl_read
    tmpl_write

);

use Opals::Date qw(
    date_text
);

use Opals::Course qw(
    
    course_getSchoolYearList
    course_getSchoolYearInfo
);


my $tm = localtime;
my $todayStr = sprintf("%04d-%02d-%02d", $tm->year+1900, ($tm->mon)+1, $tm->mday);

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

my $cgi = CGI->new;
my $input = $cgi->Vars();


my ($permission, $cookie, $template);
    ($permission, $cookie, $template) = tmpl_read(
        {
            dbh             => $dbh,
            cgi             => $cgi,
            tmplFile        => 'txtbk/report/setCurrentSchoolYear.tmpl',
            reqPermission   => 'tb_report',
        }
    );

my $op = $input->{'op'};

my $curYear = $input->{'curYear'};
my $idDel = $input->{'idDel'};
if ($op && $op eq "update"){
    if ($curYear && $curYear > 0) {
        setCurrentSchoolYear($dbh,{curYear=>$curYear});
    }
}
elsif ($op && $op eq "delete"){
    if ($idDel && $idDel > 0) {
       deleteSchoolYear($dbh, {id=>$idDel}) ;
    }
}
my $schoolYearList  = getSchoolYearList($dbh);

$template->param (
    schoolYearList  => $schoolYearList
);

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

#########################################################################

sub setCurrentSchoolYear {

    my ($dbh,$params) = @_;
    return if ($params->{'curYear'} eq '' || $params->{'curYear'} <= 0);

    my $sth = $dbh->prepare('update opl_schoolYear set curSchoolYear = 1 where id=? ');
    $sth->execute($params->{'curYear'});
    $sth = $dbh->prepare('update opl_schoolYear set curSchoolYear = 0 where id <> ? ');
    $sth->execute($params->{'curYear'});
    
    $sth->finish;
}


sub getSchoolYearList {
    my ($dbh) = @_;
    my @retList;
    my $sql = "select y.id,y.schoolYear,
        DATE_FORMAT(firstDate,'%m-%d-%Y') as firstDate,
        DATE_FORMAT(lastDate,'%m-%d-%Y') as lastDate,
        curSchoolYear , count(c.courseId) as count
    from opl_schoolYear y left join tb_courseList c on y.id = c.schoolYear group by y.id";
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    while (my $rec = $sth->fetchrow_hashref){
        push @retList, $rec;
    }
    $sth->finish;
    return \@retList;
}

sub deleteSchoolYear {

    my ($dbh,$params) = @_;
    return if ($params->{'id'} eq '' || $params->{'id'} <= 0);

    my $sth = $dbh->prepare('delete from opl_schoolYear where id=?  limit 1');
    $sth->execute($params->{'id'});
    $sth->finish;
}