package Opals::Rating;

require Exporter;
@ISA       = qw(Exporter);

@EXPORT_OK = qw(
    cmntRating_getTeacherList
    cmntRating_insert
    cmntRating_update
    cmntRating_delete
    cmntRating_getInfoByRid
    cmntRating_getComntList
    cmntRating_getReviewedList
    cmntRating_getList2Review
    cmntRating_getAllComntsByRid
    cmntRating_getAvgRating
    cmntRating_numOfRating
    cmntRating_getComntsByRid
    cmntRating_countComntList
    cmntRating_getComntList_byFilter
    cmntRating_countComntList_byFilter
    );

# Version number
$VERSION   = 0.01;      

use strict;
use Encode;

use Time::gmtime;
use POSIX qw(
    ceil
);
use Digest::SHA qw(
    hmac_sha256_base64
);
use Opals::Circulation qw(
    circ_getRecInfo
);

use URI::Escape;


#-----------------------------------------------------------------------
sub cmntRating_insert{ 
    my($dbh,$rid, $rating, $comment, $firstname, $lastname,$t_firstname,$t_lastname) =@_;
    my $insertStr = " insert into opl_cmntRating set 
                        rid          = ?,
                        rate         = ?,
                        comment      = ?,
                        firstname    = ?,
                        lastname     = ?,
                        t_firstname  = ?,
                        t_lastname   = ?,
                        cmntDate     = now() ";
     my $query = $dbh->prepare($insertStr);
     $query->execute($rid, $rating, $comment, $firstname, $lastname,$t_firstname,$t_lastname);
     my $cid = $dbh->{'mysql_insertid'};
     $query->finish;
     return $cid;
}
#-----------------------------------------------------------------------
sub cmntRating_update{ 
    my($dbh,$cmntRating) =@_;
    my $updateStr = " update   opl_cmntRating set ";
    my $qfStr="";
    my @updateData=();
    my @fields = qw(reviewerUid rate comment reviewed accepted firstname lastname note t_firstname t_lastname );
       $qfStr .= " reviewDate  = now() ";
    foreach my $f(@fields){
        if(defined $cmntRating->{$f}){
            $qfStr .=", " if($qfStr ne '');
            $qfStr .= " $f=? ";
            push @updateData,$cmntRating->{$f};
        }
    }

    if($qfStr ne '' && $cmntRating->{'id'}>0){
        $updateStr .=" $qfStr where id          = ?";
        push @updateData,$cmntRating->{'id'};
        my $query = $dbh->prepare($updateStr);
         $query->execute(@updateData);
         $query->finish;
    }
}
#-----------------------------------------------------------------------
sub cmntRating_delete{ 
    my($dbh,$id) =@_;
   # my $query = $dbh->prepare(" delete from opl_cmntRating where id  = ?");
   my $query = $dbh->prepare(" update opl_cmntRating set deleted=1  where id  = ?");
     my $result= $query->execute($id);
    $query->finish;
    return $result eq '0E0'?0:1;


}

#-----------------------------------------------------------------------
sub cmntRating_getInfoByRid{ 
    my($dbh,$rid) =@_;
    my $queryStr = "select count(rid) totalRating,
                    sum(reviewed) totalReviewed ,
                    sum(accepted) totalAccepted,
                    sum(reviewed) - sum(accepted) as totalRejected,
                    count(rid) - sum(reviewed) as totalNotReview
                    from opl_cmntRating where rid =? && deleted =0";
                    
     my $query = $dbh->prepare($queryStr);
     $query->execute($rid);
     my $ratingInfo = $query->fetchrow_hashref ;
     if($ratingInfo){
        $ratingInfo->{'avgRating'}=cmntRating_getAvgRating($dbh,$rid);
     }
     $query->finish;
    
     return $ratingInfo;
}
#-----------------------------------------------------------------------
sub cmntRating_getAvgRating{
    my($dbh,$rid) =@_;
    my $queryStr = "select avg(rate) from opl_cmntRating  
                           where rid =? && accepted=1 && deleted =0";
     my $query = $dbh->prepare($queryStr);
     $query->execute($rid);
     my ($avgRating) = $query->fetchrow_array;
     $query->finish;
     return $avgRating;
}
#-----------------------------------------------------------------------
sub cmntRating_numOfRating{
    my($dbh,$rid) =@_;
    my $queryStr = "select count(*) from opl_cmntRating  
                           where rid =? && accepted=1 && deleted =0 ";
     my $query = $dbh->prepare($queryStr);
     $query->execute($rid);
     my ($numOfRating) = $query->fetchrow_array;
     $query->finish;
     return $numOfRating;
}

#-----------------------------------------------------------------------
sub cmntRating_getReviewedList{ 
    my($dbh) =@_;
    my $queryStr = " select * from  opl_cmntRating 
                     where rid= ? && reviewed = 1 && deleted =0 
                     order by rid,id";
     my $query = $dbh->prepare($queryStr);
     $query->execute();
     my @rList_y =(); # list of comments have been accepted
     my @rList_n =(); # list of comments have not been accepted

     while (my $c = $query->fetchrow_hashref) {
        if($c->{'accepted'} == 1){
             push @rList_y, $c;
        }
        else{
             push @rList_n, $c;
        }
     }
     return(\@rList_y,\@rList_n);
}

#-----------------------------------------------------------------------
sub cmntRating_getList2Review{ 
    my($dbh,$rid) =@_;
    my $queryStr = " select * from  opl_cmntRating 
                     where rid= ? && reviewed =0 && deleted =0
                     order by rid,id";
     my $query = $dbh->prepare($queryStr);
     $query->execute($rid);
     my @rList =(); 
     my $order=1;
     while (my $c = $query->fetchrow_hashref) {
          $c->{'order'} = $order++;
          push @rList, $c;
     }
     return(\@rList);
}
#-----------------------------------------------------------------------
sub cmntRating_getAllComntsByRid{ 
    my($dbh,$rid) =@_;
    my $queryStr = " select * from  opl_cmntRating 
                     where rid= ? && deleted =0
                     order by id";
     my $query = $dbh->prepare($queryStr);
     $query->execute($rid);
     my @rList =(); 
     my $order=1;

     while (my $c = $query->fetchrow_hashref) {
          $c->{'order'} = $order++;
          if($c->{'reviewed'} ==0){
              $c->{'accepted'} = '';
          }
          push @rList, $c;
     }
     return(\@rList);
}

#-----------------------------------------------------------------------
sub cmntRating_getComntsByRid{ 
    my($dbh,$rid,$filter,$offset,$pSize) =@_;
    
    my $queryStr = " select * from  opl_cmntRating 
                     where  deleted =0 && rid= ?";

    if($filter eq 'accepted'){
        $queryStr .= " && accepted=1 ";
    }
    elsif($filter eq 'rejected'){
        $queryStr .= " && accepted=0 && reviewed=1";
    }
    elsif($filter eq 'reviewed'){
        $queryStr .= " && reviewed=1 ";
    }
    elsif($filter eq 'notReviewed'){
        $queryStr .= " && reviewed =0 ";
    }

    $queryStr .= " order by id";
    if($offset>=0){
        if(!$pSize || $pSize<=0){
            $pSize=10;
        }
        $queryStr .= " limit $offset,$pSize";
    }
     my $query = $dbh->prepare($queryStr);
     $query->execute($rid);
     my @rList =(); 
     my $order=1;

     while (my $c = $query->fetchrow_hashref) {
        $c->{'order'} = $order++;
        $c->{'teacher'} = $c->{'t_firstname'} if($c->{'t_firstname'} ne "");
        if($c->{'t_lastname'} ne ""){
            $c->{'teacher'} .= "  " if($c->{'teacher'} ne "");
            $c->{'teacher'} .= $c->{'t_lastname'};
        }
         push @rList, $c;
     }
     $query->finish;
     return \@rList;
}
#-----------------------------------------------------------------------
sub cmntRating_getTeacherList{
    my($dbh)=@_;
    my $queryStr = "select  distinct(t_firstname),t_lastname 
                    from    opl_cmntRating 
                    where   deleted=0 &&  t_firstname is not null && t_lastname is not null order by t_firstname";
    my $query = $dbh->prepare($queryStr);
    $query->execute();
    my @tList=();
    while (my $rec = $query->fetchrow_hashref) {
        $rec->{'teacher'} = $rec->{'t_firstname'} if($rec->{'t_firstname'} ne "");
        if($rec->{'t_lastname'} ne ""){
            $rec->{'teacher'} .= "  " if($rec->{'teacher'} ne "");
            $rec->{'teacher'} .= $rec->{'t_lastname'};
        }
         push @tList, $rec;
    }

    $query->finish;  
    return \@tList;
}

#-----------------------------------------------------------------------
sub cmntRating_getComntList{ 
    my($dbh,$offset,$pSize,$sortOrder,$sortDir,$t_firstname,$t_lastname) =@_;
     my $queryStr_bk = "select r.rid,count(r.rid) totalRating,
                    count(r.rid)-sum(reviewed) as totalNotReview,max(r.cmntDate) lastCommentOn,
                    sum(reviewed) totalReviewed ,sum(accepted) totalAccepted
                    from  (select  distinct r.rid,r.id ,reviewed,accepted ,cmntDate
                           from    opl_cmntRating r left outer join opl_item i using(rid) 
                           where   i.barcode not regexp '^\_\_\_'   && r.deleted=0 )  as r group by rid ";  
                           
     my $teacherCond="";
     $teacherCond .= " && t_firstname = '$t_firstname'"  if (defined $t_firstname && $t_firstname ne "");
     if (defined $t_lastname  && $t_lastname ne ""){   
        $teacherCond .= " && " if($teacherCond ne "");                             
        $teacherCond .= " t_lastname  = '$t_lastname'";
     }

     my $queryStr= "select r.rid,m.title,m.titleSort,m.author,count(r.rid) totalRating,
                    count(r.rid)-sum(reviewed) as totalNotReview,max(r.cmntDate) lastCommentOn,
                    sum(reviewed) totalReviewed ,sum(accepted) totalAccepted
                    from  (select  distinct r.rid,r.id ,reviewed,accepted ,left(cmntDate,10) cmntDate 
                           from    opl_cmntRating r left outer join opl_item i using(rid) 
                           where   i.barcode not regexp '^\_\_\_'   && r.deleted=0 $teacherCond)  as r 
                           left outer join opl_marcRecord m using(rid) group by rid ";

    if($sortOrder && $sortOrder ne '' ){
        $queryStr .= " order by  " . $sortOrder ." " .$sortDir;
    }
    $queryStr .= ', totalNotReview desc';
    
    if(($offset || $offset  >=0) && $pSize >=0){
        $queryStr .= " limit $offset,$pSize";
    }
     my @rList=();
     my $query = $dbh->prepare($queryStr);
     $query->execute();
     while (my $rec = $query->fetchrow_hashref) {
        my $recInfo              = circ_getRecInfo($dbh,$rec->{'rid'});
=item        
        $recInfo->{'rid'}=$rec->{'rid'};
        $recInfo->{'lastCommentOn'}= $rec->{'lastCommentOn'};
        $recInfo->{'totalReviewed'}=$rec->{'totalReviewed'};
        $recInfo->{'totalAccepted'}=$rec->{'totalAccepted'};
        $recInfo->{'totalRating'}=$rec->{'totalRating'};
        $recInfo->{'totalRejected'}=$rec->{'totalReviewed'}- $rec->{'totalAccepted'};
        $recInfo->{'totalNotReview'}=$rec->{'totalNotReview'};

        push @rList, $recInfo;
=cut        
        push @rList, $rec;
     }
     $query->finish;
      
     return \@rList;
}
#-----------------------------------------------------------------------
sub cmntRating_countComntList{ 
    my($dbh) =@_;
    my $queryStr_bk = "select count(distinct i.rid) 
                    from opl_cmntRating r inner join opl_item i  on i.rid=r.rid
                    where i.barcode not regexp '^\_\_\_' && deleted=0";
     my $queryStr = "select count(distinct r.rid)
                     from  (select distinct r.rid,r.id ,reviewed,accepted from opl_cmntRating r left outer join opl_item i using(rid) where i.barcode not regexp '^\_\_\_' && deleted=0 )  as r ";   
     my $query = $dbh->prepare($queryStr);
     $query->execute();
     my ($count) = $query->fetchrow_array;

     $query->finish;
      
     return $count;
}
#-----------------------------------------------------------------------
# To get all commands /ratings 
# remove condition: accepted=1  from WHERE  on Tue, Jun 03, 2014 @ 08:44:53 EDT
#
sub cmntRating_getComntList_byFilter{ 
    my($dbh,$offset,$pSize,$sortOrder,$sortDir,$rFilter) =@_;
    my $queryStr = "select r.cmntDate,r.firstname,r.lastname,r.rid,count(r.rid) totalRating,
                    count(r.rid)-sum(reviewed) as totalNotReview,avg(rate) avgRate, 
                    sum(reviewed) totalReviewed ,sum(accepted) totalAccepted
                    from opl_cmntRating r inner join opl_marcRecord m on m.rid=r.rid 
                     WHERE   r.deleted=0 ";
    my $filterStr=''; 
    if($rFilter->{'dateFrom'}){ 
        $filterStr .= " r.cmntDate >=' " . $rFilter->{'dateFrom'}. "' ";  
    }   
    if($rFilter->{'dateTo'}){ 
        $filterStr .=  " AND " if($filterStr ne '');
        $filterStr .= " r.cmntDate<='" . $rFilter->{'dateTo'}. "' ";
    }
    if($rFilter->{'firstname'}){ 
        $filterStr .=  " AND " if($filterStr ne '');
        $filterStr .= " r.firstname regexp '" . $rFilter->{'firstname'}. "' ";
    }
    if($rFilter->{'lastname'}){ 
        $filterStr .=  " AND " if($filterStr ne '');
        $filterStr .= " r.lastname regexp '" . $rFilter->{'lastname'}. "' ";
    }        
    if($rFilter->{'t_firstname'}){ 
        $filterStr .=  " AND " if($filterStr ne '');
        $filterStr .= " r.t_firstname regexp '" . $rFilter->{'t_firstname'}. "' ";
    }
    if($rFilter->{'t_lastname'}){ 
        $filterStr .=  " AND " if($filterStr ne '');
        $filterStr .= " r.t_lastname regexp '" . $rFilter->{'t_lastname'}. "' ";
    }
    if($filterStr ne ''){
        $queryStr  .= " && " . $filterStr ;
    }
    $queryStr  .= " group by rid " ;
                         
   # if($sortOrder && $sortOrder ne '' && $sortOrder ne 'avgRating' ){
    if($sortOrder && $sortOrder ne ''  ){
        $queryStr .= " order by  " . $sortOrder ." " .$sortDir;
    }
    
    if(($offset || $offset  >=0) && $pSize >=0){
        $queryStr .= " limit $offset,$pSize";
    }
 #open debug,">/tmp/cm"; print debug  $queryStr."\n"; close debug;
     my @rList=();
     my $query = $dbh->prepare($queryStr);
     $query->execute();
     while (my $rec = $query->fetchrow_hashref) {
        my $recInfo              = circ_getRecInfo($dbh,$rec->{'rid'});
        $recInfo->{'rid'}        =$rec->{'rid'};
        $recInfo->{'avgRate'}        =$rec->{'avgRate'};
        $recInfo->{'cmntDate'}      =substr($rec->{'cmntDate'},0,10);
        $recInfo->{'totalReviewed'} =$rec->{'totalReviewed'};
        $recInfo->{'totalAccepted'} =$rec->{'totalAccepted'};
        $recInfo->{'totalRating'}   =$rec->{'totalRating'};
        $recInfo->{'totalRejected'} =$rec->{'totalReviewed'}- $rec->{'totalAccepted'};
        $recInfo->{'totalNotReview'}=$rec->{'totalNotReview'};

        push @rList, $recInfo;
     }
    
     $query->finish;
      
     return \@rList;
}
#-----------------------------------------------------------------------
sub cmntRating_countComntList_byFilter{ 
    my($dbh,$rFilter) =@_;
    my $queryStr = "select count(distinct rid) 
                    from opl_cmntRating r";
    my $filterStr=''; 
    if($rFilter->{'dateFrom'}){ 
        $filterStr .= " r.cmntDate >='" . $rFilter->{'dateFrom'} . "' ";  
    }   
    if($rFilter->{'dateTo'}){ 
        $filterStr .=  " AND " if($filterStr ne '');
        $filterStr .= " r.cmntDate<='" . $rFilter->{'dateTo'}. "' ";
    }
    if($rFilter->{'firstname'}){ 
        $filterStr .=  " AND " if($filterStr ne '');
        $filterStr .= " r.firstname regexp '^" . $rFilter->{'firstname'}. "' ";
    }
    if($rFilter->{'lastname'}){ 
        $filterStr .=  " AND " if($filterStr ne '');
        $filterStr .= " r.lastname regexp '^" . $rFilter->{'lastname'}. "' ";
    }        
    if($rFilter->{'t_firstname'}){ 
        $filterStr .=  " AND " if($filterStr ne '');
        $filterStr .= " r.t_firstname regexp '" . $rFilter->{'t_firstname'}. "' ";
    }
    if($rFilter->{'t_lastname'}){ 
        $filterStr .=  " AND " if($filterStr ne '');
        $filterStr .= " r.t_lastname regexp '" . $rFilter->{'t_lastname'}. "' ";
    }
    if($filterStr ne ''){
        $queryStr  .= " WHERE deleted=0 && " . $filterStr ;
    }

     my $query = $dbh->prepare($queryStr);
     $query->execute();
     my ($count) = $query->fetchrow_array;

     $query->finish;
      
     return $count;
}

#-----------------------------------------------------------------------
1;