package Textbook::Course;

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

    course_add
    course_update
    course_delete
    course_find
    course_findById
    course_addSection
);
# Version number
$VERSION   = 0.01;      

#use utf8;
use strict;


############################################################
#
# function course_add
#   
#  $params : hash table contains values of a section
#       - courseCode 
#       - courseName
#       - descriptions
#       - grade
#       - schoolYear
#       - instructionHours
#       - 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 tbk_courseList set $sql_fields ";
    my $sth=$dbh->prepare($sql);
    $sth->execute(@fieldData);
    my $courseId = $dbh->{'mysql_insertid'};
    return $courseId;
}
############################################################
#
# function course_update
#   
#  $params : hash table contains values of a section
#       - courseCode 
#       - courseName
#       - descriptions
#       - grade
#       - schoolYear
#       - instructionHours
#       - prerequisite
#   
#
############################################################
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} ne'' ){
            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 tbk_courseList  set $sql_fields where courseId =?";
    my $sth =$dbh->prepare($sql);
    $sth->execute(@fieldData);
    $sth->finish;
    return;
}
############################################################
#
# function course_delete
#   
#
############################################################
sub course_delete {
    my ($dbh,$courseId) = @_;

    return if($courseId eq'');

    my $sth_course = $dbh->prepare('delete from tbk_courseList where courseId=?');
    my $sth_section = $dbh->prepare('delete from tbk_courseSection where courseId=?');
    $sth_course->execute($courseId);
    $sth_section->execute($courseId);
    $sth_course->finish;
    $sth_section->finish;
 }

############################################################
#
# function course_addSection    
#
#   Add a section to a course
#
#   $params : hash table contains values of a section
#       - courseId 
#       - hour
#       - location
#       - notes
#       - building
#
############################################################

sub course_addSection{
    my ($dbh,$params)=@_;
    return if($params->{'courseId'} eq ''|| 
              $params->{'sectionId'}eq '' ||  
              0==course_findById($dbh,$params->{'courseId'}));
    my @fields=('courseId','hour','location','notes','building');
    my @fieldData;
    my $sql_fields="";
    foreach my $f (@fields){
         if($params->{$f} ne'' ){
            push @fieldData,$params->{$f};
            $sql_fields .= " , " if($sql_fields ne "");
            $sql_fields .= "$f =? ";
         }
    }
    return if ($sql_fields eq '');
    my $sql ="insert into tbk_courseSection set $sql_fields ";
    my $sth =$dbh->prepare($sql);
    $sth->execute(@fieldData);
    $sth->finish;
    return;
    
}
############################################################
#
# function course_updateSection    
#
#   Add a section to a course
#
#   $params : hash table contains values of a section
#       - courseId 
#       - hour
#       - location
#       - notes
#       - building
#
############################################################

sub course_updateSection{
    my ($dbh,$params)=@_;
    return if($params->{'courseId'} eq ''|| 
              $params->{'sectionId'}eq '' );
    my @fields=('hour','location','notes','building');
    my @fieldData;
    my $sql_fields="";
    foreach my $f (@fields){
         if($params->{$f} ne'' ){
            push @fieldData,$params->{$f};
            $sql_fields .= " , " if($sql_fields ne "");
            $sql_fields .= "$f =? ";
         }
    }
    return if ($sql_fields eq '');
    push @fieldData,$params->{'courseId'};
    push @fieldData,$params->{'sectionId'};
    my $sql ="update tbk_courseSection set $sql_fields where courseId=? && sectionId=? ";
    my $sth =$dbh->prepare($sql);
    $sth->execute(@fieldData);
    $sth->finish;
    return;
    
}

############################################################
#
# function course_deleteSection    
#
#   delete a section from a course
#
#   $courseId
#   $sectionId 
#
############################################################

sub course_deleteSection{
    my ($dbh,$courseId,$sectionId)=@_;
    return if($courseId eq '' || 
              $sectionId eq '' );
    my $sql ="delete from into tbk_courseSection where courseId= ? && sectionId= ? ";
    my $sth =$dbh->prepare($sql);
    $sth->execute($courseId,$sectionId);
    $sth->finish;
    return;
    
}

############################################################
#
# function course_find
#
# 
############################################################
sub course_find{
    my ($dbh,$params) = @_;
    
    my @courseList;
    my $sql ="select * from tbk_courseList c ";
    my $sql_cond="";
    my @condVals=();
    if($params->{'courseId'} ne ''){
       $sql_cond .= "   courseId=?";
       push @condVals,$params->{'courseId'}
    }
    if($params->{'courseName'} ne ''){
        $sql_cond .= " or " if($sql_cond ne '');
        $sql_cond .= " courseName =?";
        push @condVals,$params->{'courseName'}
    }

    if($params->{'courseCode'} ne ''){
        $sql_cond .= " or " if($sql_cond ne '');
        $sql_cond .= "  courseCode=?";
        push @condVals,$params->{'courseCode'}
    }
    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;
}
############################################################
#
# function course_findById
#   
#
############################################################
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;
   
}



1;