package Textbook::Search;

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

    search_prep_teacher
    search_prep_student
    search_prep_course
    search_prep_homeroom
    
    search_prep_reciveByTeacher
    search_prep_reciveByStudent
    
    search_dist_teacher
    search_dist_student
    search_dist_course
    search_dist_homeroom

    search_coll_student
    search_coll_teacher
    search_coll_course
    search_coll_homeroom
);

use Textbook::SchoolYear qw(
    schoolyear_getCurrent
);
use Textbook::Preference qw(    
    pref_getPreference
);
# Version number
$VERSION   = 0.01;      

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

my $defaultSchoolYearId;
my $schoolYear=schoolyear_getCurrent($dbh);
if ($schoolYear){
    $defaultSchoolYearId=$schoolYear->{'id'};
}
my $pref =pref_getPreference($dbh);

############################################################
#
# function search_user
#   
#   $params: a hash table may have 0 or more of the following key
#               - userId,
#               - name
#               - offset 
#               - count 
#
############################################################

sub search_user{
    my ($dbh,$params) = @_;

    my @condVal=();
    my($sql_fields,$sql_tables,$sql_cond)=("","","");

    my $sql_fields="select u.firstname as firstName, u.lastname as lastName ,u.userBarcode as userId, 
                    u.categorycode categoryCode,c.catname as userType";
    $sql_tables   =" from opl_user u inner join opl_category c on c.catid=u.categorycode";

   if($params->{"userId"} ne ""){
        $sql_cond =" u.userBarcode= ? ";
        push @condVal,$params->{"userId"};
    }
    if($params->{"name"} ne ""){
         my $name=$params->{"name"};
         $name =~ s/ +$//g;
         $name =~ s/ / \+/g;
         $name ="$name%";

         if ($sql_cond ne ""){
            $sql_cond ="(" . $sql_cond . " or u.firstname like '$name' or u.lastname like '$name' or username like  '$name')" ;
         }
         else {
            $sql_cond = " u.firstname like '$name' or u.lastname like '$name' or username like  '$name'" ;
         }
   }
   my $sql_count ="select count(distinct u.userBarcode)  $sql_tables  "; 
   my $sql =$sql_fields . $sql_tables ;
   if($sql_cond ne ""){
        $sql       .= " where $sql_cond " ;
        $sql_count .= " where $sql_cond " ;
   }
   if( $params->{'offset'} ne "" &&  $params->{'offset'} !~ m/\D/ 
       && $params->{'count'}  ne "" && $params->{'count'} !~ m/\D/){
        $sql .= " limit " . $params->{'offset'} ."," . $params->{'count'} ;
   }
   my $sth = $dbh->prepare($sql_count);
   $sth->execute(@condVal);
   my ($found)=$sth->fetchrow_array ;  
   my @userList=();    
   if($found>0){
       $sth = $dbh->prepare($sql);
       $sth->execute(@condVal);
       while ( my $rec = $sth->fetchrow_hashref ){
           push @userList,$rec;
       }
   }
   return {found=>$found,userList=>\@userList};

}



############################################################
#
# function search_student
#   
#   $params: a hash table may have 0 or more of the following key
#               - studentId,
#               - homeroom
#               - name
#               - courseCode
#               - teacherId
#               - sectionId
#               - offset 
#               - count 
#
############################################################



sub search_student{
    my ($dbh,$params) = @_;
    my @condVal=();
    my($sql_fields,$sql_tables,$sql_cond)=("","","");
    $sql_fields="select distinct s.* ";
    $sql_tables   =" from tbk_students s ";
    if($params->{"studentId"} ne ""){
        $sql_cond =" s.studentId= ? ";
        push @condVal,$params->{"studentId"};
    }
    if($params->{"name"} ne ""){
         if ($sql_cond ne ""){
            $sql_cond ="(" . $sql_cond . " or match s.firstName,s.lastName against(? IN BOOLEAN MODE) )" ;
         }
         else {
            $sql_cond =" match s.firstName,s.lastName against(? IN BOOLEAN MODE) ";
         }
         my $name=$params->{"name"};
         $name =~ s/ +$//g;
         $name =~ s/ / \+/g;
         $name ="+$name";
        push @condVal,$name;
    }
    if($params->{"homeroom"} ne ""){
        $sql_cond .=" && " if ($sql_cond ne "");
        $sql_cond .=" s.homeroom = ? ";
        push @condVal,$params->{"homeroom"};
    }

    if($params->{"courseCode"} ne "" || $params->{"teacherId"} ne "" ){

        $sql_tables .=" inner join tbk_studentSchedule ss on s.studentId= ss.studentId" ;
        if($params->{"courseCode"} ne "" ){
            $sql_cond .=" && " if ($sql_cond ne "");
            $sql_cond .=" ss.courseCode =?  ";
            push @condVal,$params->{"courseCode"};
            if($params->{"sectionId"} ne ""){
                $sql_cond .=" && ss.sectionId =?  ";
                push @condVal,$params->{"sectionId"};
            }    
        }
        if($params->{"teacherId"} ne ""){
            $sql_tables .=" inner join tbk_teacherSchedule ts on ts.courseCode= ss.courseCode && ts.sectionId=ss.sectionID " ;
            $sql_cond .=" && " if ($sql_cond ne "");
            $sql_cond .=" ts.teacherId =?  ";
            push @condVal,$params->{"teacherId"};

        }
    
    }
   my $sql_count ="select count(distinct s.studentId) $sql_tables "; 
   my $sql =$sql_fields . $sql_tables ;
   if($sql_cond ne ""){
        $sql       .= " where $sql_cond " ;
        $sql_count .= " where $sql_cond " ;
   }
   if( $params->{'offset'} ne "" &&  $params->{'offset'} !~ m/\D/ 
       && $params->{'count'}  ne "" && $params->{'count'} !~ m/\D/){
        $sql .= " limit " . $params->{'offset'} ."," . $params->{'count'} ;
   }
   my $sth = $dbh->prepare($sql_count);
   $sth->execute(@condVal);
   my ($found)=$sth->fetchrow_array ;
   my @studentList;
   
   if($found>0){
       $sth = $dbh->prepare($sql);
       $sth->execute(@condVal);
       while ( my $rec = $sth->fetchrow_hashref ){
           push @studentList,$rec;
       }
   }
   return {found=>$found,studentList=>\@studentList};


}

############################################################
#
# function search_teacher
#
#   $params: a hash table may have 0 or more of the following key
#               - teacherId
#               - name
#               - courseCode
#               - teacherId
#               - sectionId
#               - offset 
#               - count 
#
############################################################

sub search_teacher{
    my ($dbh,$params) = @_;
    my @condVal=();
    my($sql_fields,$sql_tables,$sql_cond)=("","","");
    $sql_fields="select distinct  t.* ";
    $sql_tables   =" from tbk_teachers t ";
    if($params->{"teacherId"} ne ""){
        $sql_cond =" t.teacherId= ? ";
        push @condVal,$params->{"teacherId"};
    }
    if($params->{"name"} ne ""){
        if ($sql_cond ne ""){
            $sql_cond ="  $sql_cond  or match t.firstName,t.lastName against(?)  ";
        }
        else{
            $sql_cond ="  match t.firstName,t.lastName against(?)  ";
        }
        push @condVal,$params->{"name"};
    }
    if($params->{"courseCode"} ne ""  ){
        $sql_tables .=" inner join tbk_teacherSchedule ts on t.teacherId=ts.teacherId " ;
        $sql_cond .=" && " if ($sql_cond ne "");
        $sql_cond .=" ts.courseCode =?  ";
        push @condVal,$params->{"courseCode"};
        if($params->{"sectionId"} ne ""){
            $sql_cond .=" && ts.sectionId =?  ";
            push @condVal,$params->{"sectionId"};
        }
    }

   my $sql_count ="select count(distinct t.teacherId) $sql_tables "; 
   my $sql       = $sql_fields . $sql_tables ;
   if($sql_cond ne ""){
        $sql       .= " where $sql_cond " ;
        $sql_count .= " where $sql_cond " ;
   }
   if( $params->{'offset'} ne "" &&  $params->{'offset'} !~ m/\D/ 
       && $params->{'count'}  ne "" && $params->{'count'} !~ m/\D/){
        $sql .= " limit " . $params->{'offset'} ."," . $params->{'count'} ;
   }
   
   my $sth = $dbh->prepare($sql_count);
   $sth->execute(@condVal);
   my ($found)=$sth->fetchrow_array ;
   my @teacherList;
   if($found>0){
       $sth = $dbh->prepare($sql);
       $sth->execute(@condVal);
       while ( my $rec = $sth->fetchrow_hashref ){
           push @teacherList,$rec;
       }
   }
   return {found=>$found,teacherList=>\@teacherList};

}

############################################################
#
# function search_course
#
#   $params: a hash table may have 0 or more of the following key
#               - teacherId
#               - studentId
#               - homeroom
#               - courseCode
#               - courseName
#               - offset 
#               - count 
#
############################################################

sub search_course{
    my ($dbh,$params) = @_;
    my @condVal=();
    my($sql_fields,$sql_tables,$sql_cond)=("","","");
    $sql_fields="select distinct c.courseCode,c.courseId,c.courseName,c.descriptions";

    $sql_tables   =" from tbk_courseList c ";
    
    if($params->{"courseCode"} ne ""){
        $sql_cond =" c.courseCode= ? ";
        push @condVal,$params->{"courseCode"};
    }
    if($params->{"courseName"} ne ""){
        if ($sql_cond ne ""){
            $sql_cond ="( $sql_cond or match c.courseName against( ? ))";
        }
        else {
            $sql_cond .=" match c.courseName against( ? ) ";
        }
        push @condVal,$params->{"courseName"};
    }
    if($params->{"studentId"} ne "" || $params->{"homeroom"} ne ""){
        $sql_tables .= " inner join tbk_studentSchedule ss on c.courseId=ss.courseId ";
        if($params->{"homeroom"}){
            $sql_tables .= " inner join tbk_students s on ss.studentId=s.studentId ";
            $sql_cond .=" && " if ($sql_cond ne "");
            $sql_cond .=" s.homeroom= ? ";
            push @condVal,$params->{"homeroom"};
        }
        if($params->{"studentId"} ne ""){
            $sql_cond .=" && " if ($sql_cond ne "");
            $sql_cond .=" ss.studentId= ? ";
            push @condVal,$params->{"studentId"};
        }
    }
    if($params->{"teacherId"} ne ""){
        $sql_tables .= " inner join tbk_teacherSchedule ts on c.courseId=ts.courseId ";
        $sql_cond .=" && " if ($sql_cond ne "");
        $sql_cond .=" ts.teacherId= ? ";
        push @condVal,$params->{"teacherId"};
    }
    
   my $sql_count ="select count(distinct c.courseId) $sql_tables "; 
   my $sql =$sql_fields . $sql_tables ;
   if($sql_cond ne ""){
        $sql       .= " where $sql_cond " ;
        $sql_count .= " where $sql_cond " ;
   }

   $sql .= " order by c.courseCode " ;
   if( $params->{'offset'} ne "" &&  $params->{'offset'} !~ m/\D/ 
       && $params->{'count'}  ne "" && $params->{'count'} !~ m/\D/){
        $sql .= " limit " . $params->{'offset'} ."," . $params->{'count'} ;
   }

   my $sth = $dbh->prepare($sql_count);
   $sth->execute(@condVal);
   my ($found)=$sth->fetchrow_array ;
   my @courseList;
   if($found>0){   
       $sth = $dbh->prepare($sql);
       $sth->execute(@condVal);
       while ( my $rec = $sth->fetchrow_hashref ){
            $params->{'courseCode'}=$rec->{'courseCode'} ;
            my $sections =getCourseSection($dbh,$params);
            push @courseList,{  courseCode=>$rec->{'courseCode'},
                                courseId=>$rec->{'courseId'},
                                courseName=>$rec->{'courseName'},
                                descriptions=>$rec->{'descriptions'},
                                sections=>$sections
                                    
                                 };
           
       }
   }
   
  return {found=>$found,courseList=>\@courseList};

}

############################################################
sub getCourseSection{
    my ($dbh,$params) = @_;
    my @sections=();
    return @sections if($params->{'courseCode'} eq '');
    my @condVal=();
    my($sql_fields,$sql_tables,$sql_cond)=("","","");
    $sql_fields ="select distinct cs.sectionId, cs.hour,cs.location,cs.building,cs.notes ";
    $sql_tables= "from  tbk_courseSection cs ";
    $sql_cond=" where cs.courseCode =? ";
    push @condVal,$params->{'courseCode'} ;
    if($params->{'teacherId'} ne '' ){
        $sql_tables .= " inner join tbk_teacherSchedule ts on cs.courseCode=ts.courseCode && cs.sectionId=ts.sectionId ";
        $sql_cond   .= " && ts.teacherId= ? ";
        push @condVal, $params->{'teacherId'} ;
    }
    if($params->{'studentId'} ne '' or $params->{'homeroom'} ne ''){
        $sql_tables .= " inner join tbk_studentSchedule ss on ss.courseCode=cs.courseCode && ss.sectionId=cs.sectionId ";
        if($params->{'studentId'} ne '' ){
            $sql_cond   .= " && ss.studentId= ? ";
            push @condVal,$params->{'studentId'};
        }
        if($params->{'homeroom'} ne '' ){
            $sql_tables .= " inner join tbk_students s on s.studentId=ss.studentId  ";
            $sql_cond   .= " && s.homeroom= ? ";
            push @condVal,$params->{'homeroom'};
        }

    }
    my $sql =$sql_fields . $sql_tables .$sql_cond;
    my $sth = $dbh->prepare($sql);
    $sth->execute(@condVal);
    while ( my $rec = $sth->fetchrow_hashref ){
        push @sections,$rec;
    }
    return \@sections;

}


############################################################
#
# function search_homeroom
#
#   $params: a hash table may have 0 or more of the following key
#               - studentId
#               - homeroom
#               - offset 
#               - count 
#
############################################################

sub search_homeroom{
    my ($dbh,$params) = @_;
    my @condVal=();
    my($sql_fields,$sql_tables,$sql_cond)=("","","");
    $sql_fields="select distinct s.homeroom ";
    $sql_tables   =" from tbk_students s ";
    
    if($params->{"homeroom"} ne ""){
        $sql_cond =" s.homeroom= ? ";
        push @condVal,$params->{"homeroom"};
    }
    if($params->{"studentId"} ne "" ){
        $sql_cond .=" && " if ($sql_cond ne "");
        $sql_cond =" s.studentId= ? ";
        push @condVal,$params->{"studentId"};
        
    }
   my $sql_count ="select count(distinct s.homeroom ) $sql_tables ";        
   my $sql =$sql_fields . $sql_tables ;
   if($sql_cond ne ""){
        $sql       .= " where $sql_cond " ;
        $sql_count .= " where $sql_cond " ;
   }
   
   if( $params->{'offset'} ne "" &&  $params->{'offset'} !~ m/\D/ 
       && $params->{'count'}  ne "" && $params->{'count'} !~ m/\D/){
        $sql .= " limit " . $params->{'offset'} ."," . $params->{'count'} ;
   }
   
   my $sth = $dbh->prepare($sql_count);
   $sth->execute(@condVal);
   my ($found)=$sth->fetchrow_array ;
   my @homeroomList;
   if($found>0){
       $sth = $dbh->prepare($sql);
       $sth->execute(@condVal);

       while ( my $rec = $sth->fetchrow_hashref ){
           push @homeroomList,$rec;
       }
   }

    
   return {found=>$found,homeroomList=>\@homeroomList};

}
#################################################################################
sub search_dist_student{
    my ($dbh,$params) = @_;
    my @condVal=();
    my($sql_fields,$sql_cond)=("","","");
    
    $sql_fields="select distinct  s.*
                from tbk_students s inner join tbk_preparationDetails d
                     on s.studentId= d.studentId inner join tbk_preparation p 
                     on p.id=d.pid && p.type='student'";

    if($params->{"studentId"} ne""){
        $sql_cond =" s.studentId= ? ";
        push @condVal,$params->{"studentId"};
    }
    if($params->{"name"} ne ""){
        if ($sql_cond ne ""){
            $sql_cond ="  $sql_cond  or match s.firstName,s.lastName against(?)  ";
        }
        else{
            $sql_cond ="  match s.firstName,s.lastName against(?)  ";
        }
        push @condVal,$params->{"name"};
    }
    my $sql_count ="select count(distinct s.studentId)
                    from tbk_students s inner join tbk_preparationDetails d
                    on s.studentId= d.studentId inner join tbk_preparation p 
                     on p.id=d.pid && p.type='student'   ";
                     
    my $sql = $sql_fields ;
    if($sql_cond ne ""){
        $sql       .= " where $sql_cond && nDistributed >0  " ;
        $sql_count .= " where $sql_cond && nDistributed >0  " ;
   }
   else{
        $sql       .= " where nDistributed >0 " ;
        $sql_count .= " where nDistributed >0 " ;
   }
    my $sth = $dbh->prepare($sql_count);
    $sth->execute(@condVal);
    my ($found)=$sth->fetchrow_array ;
    my @studentList;
    if($found>0){
       $sth = $dbh->prepare($sql);
       $sth->execute(@condVal);
       while ( my $rec = $sth->fetchrow_hashref ){
           push @studentList,$rec;
       }
   }

   return {found=>$found,studentList=>\@studentList};


}
###############################################################################
sub search_dist_teacher{
    my ($dbh,$params) = @_;
    my @condVal=();
    my($sql_fields,$sql_cond)=("","","");
    
    $sql_fields="select distinct  t.* 
                from tbk_teachers t inner join tbk_preparationDetails d
                     on t.teacherId= d.teacherId inner join tbk_preparation p 
                     on p.id=d.pid && p.type='teacher'";

    if($params->{"teacherId"} ne""){
        $sql_cond =" t.teacherId= ? ";
        push @condVal,$params->{"teacherId"};
    }
    if($params->{"name"} ne ""){
        if ($sql_cond ne ""){
            $sql_cond ="  $sql_cond  or match t.firstName,t.lastName against(?)  ";
        }
        else{
            $sql_cond ="  match t.firstName,t.lastName against(?)  ";
        }
        push @condVal,$params->{"name"};
    }
    my $sql_count ="select count(distinct  t.teacherId)
                    from tbk_teachers t inner join tbk_preparationDetails d
                    on t.teacherId= d.teacherId inner join tbk_preparation p 
                     on p.id=d.pid && p.type='teacher'  ";
                     
    my $sql = $sql_fields ;
    if($sql_cond ne ""){
        $sql       .= " where $sql_cond && nDistributed >0 " ;
        $sql_count .= " where $sql_cond && nDistributed >0 " ;
   }
   else{
        $sql       .= " where nDistributed >0 " ;
        $sql_count .= " where nDistributed >0 " ;
   }


    my $sth = $dbh->prepare($sql_count);
    $sth->execute(@condVal);
    my ($found)=$sth->fetchrow_array ;
    my @teacherList;
    if($found>0){
       $sth = $dbh->prepare($sql);
       $sth->execute(@condVal);
       while ( my $rec = $sth->fetchrow_hashref ){
           push @teacherList,$rec;
       }
   }
   return {found=>$found,teacherList=>\@teacherList};

}
################################################################################
sub search_prep_reciveByTeacher{
    my ($dbh,$params) = @_;
    my @condVal=();
    my($sql_fields,$sql_cond)=("","","");
    $sql_fields="select  distinct  t.* 
                 from     tbk_teachers t inner join tbk_teacherSchedule ts on t.teacherId = ts.teacherId
		         inner    join  tbk_preparationDetails d on d.courseId=ts.courseId && d.sectionId=ts.sectionId ";

    if($params->{"teacherId"} ne""){
        $sql_cond =" t.teacherId= ? ";
        push @condVal,$params->{"teacherId"};
    }
    if($params->{"name"} ne ""){
        if ($sql_cond ne ""){
            $sql_cond ="  $sql_cond  or match t.firstName,t.lastName against(?)  ";
        }
        else{
            $sql_cond ="  match t.firstName,t.lastName against(?)  ";
        }
        push @condVal,$params->{"name"};
    }

    my $sql_count ="select   count(distinct t.teacherId) 
                 from     tbk_teachers t inner join tbk_teacherSchedule ts on t.teacherId = ts.teacherId
		         inner    join  tbk_preparationDetails d on d.courseId=ts.courseId && d.sectionId=ts.sectionId ";
        
    my $sql = $sql_fields ;
    if($sql_cond ne ""){
        $sql       .= " where d.recipient ='teacher' && $sql_cond " ;
        $sql_count .= " where d.recipient ='teacher' && $sql_cond " ;
    }
    else{
        $sql       .= " where d.recipient ='teacher'" ;
        $sql_count .= " where d.recipient ='teacher'" ;
    }
 
    my $sth = $dbh->prepare($sql_count);
    $sth->execute(@condVal);
    my ($found)=$sth->fetchrow_array ;
    my @teacherList;
    if($found>0){
       $sth = $dbh->prepare($sql);
       $sth->execute(@condVal);
       while ( my $rec = $sth->fetchrow_hashref ){
           push @teacherList,$rec;
       }
   }
   return {found=>$found,teacherList=>\@teacherList};

}
################################################################################
#   search the students have preparations by teacher, student, course, homeroom   
#
#
#
#
################################################################################
sub search_prep_reciveByStudent{
    my ($dbh,$params) = @_;
    my @condVal=();
    my($sql_fields,$sql_cond)=("","","");
        
    $sql_fields="select  distinct  s.* 
                 from     tbk_students s inner join tbk_studentSchedule ss on s.studentId=ss.studentId
		         inner    join  tbk_preparationDetails d on d.courseId=ss.courseId && d.sectionId=ss.sectionId ";

    if($params->{"studentId"} ne""){
        $sql_cond =" s.studentId= ? ";
        push @condVal,$params->{"studentId"};
    }
    if($params->{"name"} ne ""){
        if ($sql_cond ne ""){
            $sql_cond ="  $sql_cond  or match s.firstName,s.lastName against(?)  ";
        }
        else{
            $sql_cond ="  match s.firstName,s.lastName against(?)  ";
        }
        push @condVal,$params->{"name"};
    }

    my $sql_count ="select   count(distinct s.studentId) 
                 from     tbk_students s inner join tbk_studentSchedule ss on s.studentId=ss.studentId
		         inner    join  tbk_preparationDetails d on d.courseId=ss.courseId && d.sectionId=ss.sectionId ";
        
    my $sql = $sql_fields ;
    if($sql_cond ne ""){
        $sql       .= " where d.recipient ='student' && $sql_cond " ;
        $sql_count .= " where d.recipient ='student' && $sql_cond " ;
    }
    else{
        $sql       .= " where d.recipient ='teacher'" ;
        $sql_count .= " where d.recipient ='teacher'" ;
    }

    my $sth = $dbh->prepare($sql_count);
    $sth->execute(@condVal);
    my ($found)=$sth->fetchrow_array ;
    my @studentList;
    if($found>0){
       $sth = $dbh->prepare($sql);
       $sth->execute(@condVal);
       while ( my $rec = $sth->fetchrow_hashref ){
           push @studentList,$rec;
       }
   }
   return {found=>$found,studentList=>\@studentList};

}
################################################################################
sub search_prep_teacher{
    my ($dbh,$params) = @_;
    my @condVal=();
    my($sql_fields,$sql_cond)=("","","");
    
    $sql_fields="select distinct  t.*
                from tbk_teachers t inner join tbk_preparationDetails d
                     on t.teacherId= d.teacherId inner join tbk_preparation p 
                     on p.id=d.pid && p.type='teacher'";

    if($params->{"teacherId"} ne""){
        $sql_cond =" t.teacherId= ? ";
        push @condVal,$params->{"teacherId"};
    }
    if($params->{"name"} ne ""){
        if ($sql_cond ne ""){
            $sql_cond ="  $sql_cond  or match t.firstName,t.lastName against(?)  ";
        }
        else{
            $sql_cond ="  match t.firstName,t.lastName against(?)  ";
        }
        push @condVal,$params->{"name"};
    }
    my $sql_count ="select count(distinct t.teacherId)
                    from tbk_teachers t inner join tbk_preparationDetails d
                    on t.teacherId= d.teacherId inner join tbk_preparation p 
                     on p.id=d.pid && p.type='teacher'  ";
                     
    my $sql = $sql_fields ;
    if($sql_cond ne ""){
        $sql       .= " where $sql_cond " ;
        $sql_count .= " where $sql_cond " ;
   }

    my $sth = $dbh->prepare($sql_count);
    $sth->execute(@condVal);
    my ($found)=$sth->fetchrow_array ;
    my @teacherList;
    if($found>0){
       $sth = $dbh->prepare($sql);
       $sth->execute(@condVal);
       while ( my $rec = $sth->fetchrow_hashref ){
           push @teacherList,$rec;
       }
   }
   return {found=>$found,teacherList=>\@teacherList};
}

################################################################################
sub search_prep_student{
    my ($dbh,$params) = @_;
    my @condVal=();
    my($sql_fields,$sql_cond)=("","","");
    
    $sql_fields="select distinct  s.*
                from tbk_students s inner join tbk_preparationDetails d
                     on s.studentId= d.studentId inner join tbk_preparation p 
                     on p.id=d.pid && p.type='student'";

    if($params->{"studentId"} ne""){
        $sql_cond =" s.studentId= ? ";
        push @condVal,$params->{"studentId"};
    }
    if($params->{"name"} ne ""){
        if ($sql_cond ne ""){
            $sql_cond ="  $sql_cond  or match s.firstName,s.lastName against(?)  ";
        }
        else{
            $sql_cond ="  match s.firstName,s.lastName against(?)  ";
        }
        push @condVal,$params->{"name"};
    }
    my $sql_count ="select count(distinct s.studentId)
                    from tbk_students s inner join tbk_preparationDetails d
                    on s.studentId= d.studentId inner join tbk_preparation p 
                     on p.id=d.pid && p.type='student'   ";
                     
    my $sql = $sql_fields ;
    if($sql_cond ne ""){
        $sql       .= " where $sql_cond " ;
        $sql_count .= " where $sql_cond " ;
   }

    my $sth = $dbh->prepare($sql_count);
    $sth->execute(@condVal);
    my ($found)=$sth->fetchrow_array ;
    my @studentList;
    if($found>0){
       $sth = $dbh->prepare($sql);
       $sth->execute(@condVal);
       while ( my $rec = $sth->fetchrow_hashref ){
           push @studentList,$rec;
       }
   }
   return {found=>$found,studentList=>\@studentList};
}

################################################################################
sub search_dist_course{
    my ($dbh,$params) = @_;
    my @condVal=();
    my($sql_fields,$sql_cond)=("","","");
    
    $sql_fields="select distinct  c.*
                from tbk_courseList c inner join tbk_preparationDetails d
                     on c.courseId= d.courseId inner join tbk_preparation p 
                     on p.id=d.pid && p.type='course'";

    if($params->{"courseId"} ne""){
        $sql_cond =" c.courseId= ? ";
        push @condVal,$params->{"courseId"};
    }
    if($params->{"courseName"} ne ""){
        if ($sql_cond ne ""){
            $sql_cond ="  $sql_cond  or match c.courseName against(?)  ";
        }
        else{
            $sql_cond ="  match c.courseName against(?)  ";
        }
        push @condVal,$params->{"courseName"};
    }
    my $sql_count ="select count(distinct c.courseId)
                    from tbk_courseList c inner join tbk_preparationDetails d
                     on c.courseId= d.courseId inner join tbk_preparation p 
                     on p.id=d.pid && p.type='course' ";
                     
    my $sql = $sql_fields ;
    if($sql_cond ne ""){
        $sql       .= " where $sql_cond  && nDistributed >0 " ;
        $sql_count .= " where $sql_cond  && nDistributed >0 " ;
   }
   else{
        $sql       .= " where nDistributed >0 " ;
        $sql_count .= " where nDistributed >0 " ;
   }
    my $sth = $dbh->prepare($sql_count);
    $sth->execute(@condVal);
    my ($found)=$sth->fetchrow_array ;
    my @courseList;
    if($found>0){
       $sth = $dbh->prepare($sql);
       $sth->execute(@condVal);
       while ( my $rec = $sth->fetchrow_hashref ){
           push @courseList,$rec;
       }
   }
   return {found=>$found,courseList=>\@courseList};
}

################################################################################
sub search_prep_course{
    my ($dbh,$params) = @_;
    my @condVal=();
    my($sql_fields,$sql_cond)=("","","");
    
    $sql_fields="select distinct  c.*
                from tbk_courseList c inner join tbk_preparationDetails d
                     on c.courseId= d.courseId inner join tbk_preparation p 
                     on p.id=d.pid && p.type='course'";

    if($params->{"courseId"} ne""){
        $sql_cond =" c.courseId= ? ";
        push @condVal,$params->{"courseId"};
    }
    if($params->{"courseName"} ne ""){
        if ($sql_cond ne ""){
            $sql_cond ="  $sql_cond  or match c.courseName against(?)  ";
        }
        else{
            $sql_cond ="  match c.courseName against(?)  ";
        }
        push @condVal,$params->{"courseName"};
    }
    my $sql_count ="select count(distinct c.courseId)
                    from tbk_courseList c inner join tbk_preparationDetails d
                     on c.courseId= d.courseId inner join tbk_preparation p 
                     on p.id=d.pid && p.type='course' ";
                     
    my $sql = $sql_fields ;
    if($sql_cond ne ""){
        $sql       .= " where $sql_cond " ;
        $sql_count .= " where $sql_cond " ;
   }

    my $sth = $dbh->prepare($sql_count);
    $sth->execute(@condVal);
    my ($found)=$sth->fetchrow_array ;
    my @courseList;
    if($found>0){
       $sth = $dbh->prepare($sql);
       $sth->execute(@condVal);
       while ( my $rec = $sth->fetchrow_hashref ){
           push @courseList,$rec;
       }
   }
   return {found=>$found,courseList=>\@courseList};
}

################################################################################
sub search_dist_homeroom{
    my ($dbh,$params) = @_;
    my @condVal=();
    my($sql_fields,$sql_cond)=("","","");
    
    $sql_fields="select distinct  d.homeroom
                from  tbk_preparationDetails d
                      inner join tbk_preparation p 
                     on p.id=d.pid && p.type='homeroom'";

    if($params->{"homeroom"} ne""){
        $sql_cond =" d.homeroom= ? ";
        push @condVal,$params->{"homeroom"};
    }
    
    my $sql_count ="select count(distinct d.homeroom)
                    from tbk_preparationDetails d
                    inner join tbk_preparation p 
                    on p.id=d.pid && p.type='homeroom'   ";
                     
    my $sql = $sql_fields ;
    if($sql_cond ne ""){
        $sql       .= " where $sql_cond && nDistributed >0 " ;
        $sql_count .= " where $sql_cond && nDistributed >0 " ;
    }
    else{
        $sql       .= " where nDistributed >0 " ;
        $sql_count .= " where nDistributed >0 " ;
    }
    
    my $sth = $dbh->prepare($sql_count);
    $sth->execute(@condVal);
    my ($found)=$sth->fetchrow_array ;
    my @homeroomList;
    if($found>0){
       $sth = $dbh->prepare($sql);
       $sth->execute(@condVal);
       while ( my $rec = $sth->fetchrow_hashref ){
           push @homeroomList,$rec;
       }
   }
   return {found=>$found,homeroomList=>\@homeroomList};
}

################################################################################
sub search_prep_homeroom{
   my ($dbh,$params) = @_;
    my @condVal=();
    my($sql_fields,$sql_cond)=("","","");
    
    $sql_fields="select distinct  d.homeroom
                from  tbk_preparationDetails d
                      inner join tbk_preparation p 
                     on p.id=d.pid && p.type='homeroom'";

    if($params->{"homeroom"} ne""){
        $sql_cond =" d.homeroom= ? ";
        push @condVal,$params->{"homeroom"};
    }
    
    my $sql_count ="select count(distinct d.homeroom)
                    from tbk_preparationDetails d
                    inner join tbk_preparation p 
                    on p.id=d.pid && p.type='homeroom'   ";
                     
    my $sql = $sql_fields ;
    if($sql_cond ne ""){
        $sql       .= " where $sql_cond " ;
        $sql_count .= " where $sql_cond " ;
   }

    my $sth = $dbh->prepare($sql_count);
    $sth->execute(@condVal);
    my ($found)=$sth->fetchrow_array ;
    my @homeroomList;
    if($found>0){
       $sth = $dbh->prepare($sql);
       $sth->execute(@condVal);
       while ( my $rec = $sth->fetchrow_hashref ){
           push @homeroomList,$rec;
       }
   }
   return {found=>$found,homeroomList=>\@homeroomList};
}


################################################################################
sub search_coll_teacher{
    my ($dbh,$params) = @_;
    my @condVal=();
    my($sql_fields,$sql_cond)=("","","");
    
    $sql_fields="select distinct  t.*
                from tbk_teachers t inner join tbk_bookDistribution d
                     on t.teacherId= d.teacherId inner join tbk_preparation p 
                     on p.id=d.pid && p.type='teacher'";

    if($params->{"teacherId"} ne""){
        $sql_cond =" t.teacherId= ? ";
        push @condVal,$params->{"teacherId"};
    }
    if($params->{"name"} ne ""){
        if ($sql_cond ne ""){
            $sql_cond ="  $sql_cond  or match t.firstName,t.lastName against(?)  ";
        }
        else{
            $sql_cond ="  match t.firstName,t.lastName against(?)  ";
        }
        push @condVal,$params->{"name"};
    }

    my $sql_count ="select count(distinct t.teacherId)
                   from tbk_teachers t inner join tbk_bookDistribution d
                     on t.teacherId= d.teacherId inner join tbk_preparation p 
                     on p.id=d.pid && p.type='teacher' ";
                     
    my $sql = $sql_fields ;
    if($sql_cond ne ""){
        $sql       .= " where $sql_cond " ;
        $sql_count .= " where $sql_cond " ;
   }

    my $sth = $dbh->prepare($sql_count);
    $sth->execute(@condVal);
    my ($found)=$sth->fetchrow_array ;
    my @teacherList;
    if($found>0){
       $sth = $dbh->prepare($sql);
       $sth->execute(@condVal);
       while ( my $rec = $sth->fetchrow_hashref ){
           push @teacherList,$rec;
       }
   }
   return {found=>$found,teacherList=>\@teacherList};
}

################################################################################
sub search_coll_student{
    my ($dbh,$params) = @_;
    my @condVal=();
    my($sql_fields,$sql_cond)=("","","");
    
    $sql_fields="select distinct  s.*
                from tbk_students s inner join tbk_bookDistribution d
                     on s.studentId= d.studentId inner join tbk_preparation p 
                     on p.id=d.pid && p.type='student'";

    if($params->{"studentId"} ne""){
        $sql_cond =" s.studentId= ? ";
        push @condVal,$params->{"studentId"};
    }
    if($params->{"name"} ne ""){
        if ($sql_cond ne ""){
            $sql_cond ="  $sql_cond  or match s.firstName,s.lastName against(?)  ";
        }
        else{
            $sql_cond ="  match s.firstName,s.lastName against(?)  ";
        }
        push @condVal,$params->{"name"};
    }
    my $sql_count ="select count(distinct s.studentId)
                    from tbk_students s inner join tbk_bookDistribution d
                    on s.studentId= d.studentId inner join tbk_preparation p 
                     on p.id=d.pid && p.type='student'   ";
                     
    my $sql = $sql_fields ;
    if($sql_cond ne ""){
        $sql       .= " where $sql_cond " ;
        $sql_count .= " where $sql_cond " ;
   }

    my $sth = $dbh->prepare($sql_count);
    $sth->execute(@condVal);
    my ($found)=$sth->fetchrow_array ;
    my @studentList;
    if($found>0){
       $sth = $dbh->prepare($sql);
       $sth->execute(@condVal);
       while ( my $rec = $sth->fetchrow_hashref ){
           push @studentList,$rec;
       }
   }
   return {found=>$found,studentList=>\@studentList};
}



################################################################################
sub search_coll_course{
    my ($dbh,$params) = @_;
    my @condVal=();
    my($sql_fields,$sql_cond)=("","","");
    
    $sql_fields="select distinct  c.*
                from tbk_courseList c inner join tbk_bookDistribution d
                     on c.courseId= d.courseId inner join tbk_preparation p 
                     on p.id=d.pid && p.type='course'";

    if($params->{"courseId"} ne""){
        $sql_cond =" c.courseId= ? ";
        push @condVal,$params->{"courseId"};
    }
    if($params->{"courseName"} ne ""){
        if ($sql_cond ne ""){
            $sql_cond ="  $sql_cond  or match c.courseName against(?)  ";
        }
        else{
            $sql_cond ="  match c.courseName against(?)  ";
        }
        push @condVal,$params->{"courseName"};
    }
    my $sql_count ="select count(distinct c.courseId)
                    from tbk_courseList c inner join tbk_bookDistribution d
                     on c.courseId= d.courseId inner join tbk_preparation p 
                     on p.id=d.pid && p.type='course' ";
                     
    my $sql = $sql_fields ;
    if($sql_cond ne ""){
        $sql       .= " where $sql_cond " ;
        $sql_count .= " where $sql_cond " ;
   }

    my $sth = $dbh->prepare($sql_count);
    $sth->execute(@condVal);
    my ($found)=$sth->fetchrow_array ;
    my @courseList;
    if($found>0){
       $sth = $dbh->prepare($sql);
       $sth->execute(@condVal);
       while ( my $rec = $sth->fetchrow_hashref ){
           push @courseList,$rec;
       }
   }
   return {found=>$found,courseList=>\@courseList};
}

################################################################################
sub search_coll_homeroom{
   my ($dbh,$params) = @_;
    my @condVal=();
    my($sql_fields,$sql_cond)=("","","");
    
    $sql_fields="select distinct  d.homeroom
                from  tbk_bookDistribution  d
                      inner join tbk_preparation p 
                     on p.id=d.pid && p.type='homeroom'";

    if($params->{"homeroom"} ne""){
        $sql_cond =" d.homeroom= ? ";
        push @condVal,$params->{"homeroom"};
    }
    
    my $sql_count ="select count(distinct d.homeroom)
                    from tbk_bookDistribution d
                    inner join tbk_preparation p 
                    on p.id=d.pid && p.type='homeroom'   ";
                     
    my $sql = $sql_fields ;
    if($sql_cond ne ""){
        $sql       .= " where $sql_cond " ;
        $sql_count .= " where $sql_cond " ;
   }

    my $sth = $dbh->prepare($sql_count);
    $sth->execute(@condVal);
    my ($found)=$sth->fetchrow_array ;
    my @homeroomList;
    if($found>0){
       $sth = $dbh->prepare($sql);
       $sth->execute(@condVal);
       while ( my $rec = $sth->fetchrow_hashref ){
           push @homeroomList,$rec;
       }
   }
   return {found=>$found,homeroomList=>\@homeroomList};
}


1;