package Opals::Session;

require Exporter;
@ISA       = qw(Exporter);
# Symbols to be exported by default
#@EXPORT    = qw(
#    opl_
#);
# Symbols to be exported on request
@EXPORT_OK = qw(
    SessionHdl_viewMovingHolding
    SessionHdl_mhPrevNextRecord
    SessionHdl_moveHolding
    SessionHdl_setTarget
    SessionHdl_setNote
    SessionHdl_add
    SessionHdl_del
    SessionHdl_get
    SessionHdl_clearVar
    SessionHdl_exist_rid
    SessionHdl_exist_bc
    SessionHdl_count
    SessionHdl_clean
    SessionHdl_getBriefRecList
    SessionHdl_getBrRecListByHolding
    SessionHdl_copy
    SessionHdl_getSSID
);
use Digest::SHA qw(
    sha1_base64
    sha1_hex
);

use Opals::Utility qw(
    util_escapeXml
);

use Opals::Search qw(
    srch_briefRecordById
);
use Opals::MarcXml qw(
    mxml_recordPath
);


# Version number
$VERSION   = 0.01;      

#use utf8;
use strict;
#use Opals::_Some_Module_;
#------------------------------------------------------------------------------
sub SessionHdl_getSSID{
    my($cgi)=@_;
   my $sessionID="";
   my $sessionID = $cgi->cookie('globalSessionID');
   if(!$sessionID){
        $sessionID = sha1_hex(time . rand(time));
   }
   return $sessionID; 
}


################################################################################
sub SessionHdl_viewMovingHolding {
    my ($dbh, $ssid, $varName) = @_;

    # Get target record
    my $sql = <<_SQL_;
select  *
from    opl_sessionVar
where   ssid = ?
     && var = ?
     && note = 'target'
limit 1
_SQL_

    my @val = ($ssid, $varName);
    my $target = $dbh->selectrow_hashref($sql, undef, @val);

    if (!$target) {
        $sql = <<_SQL_;
select  *
from    opl_sessionVar
where   ssid = ?
     && var = ?
order by sOrder asc
limit 1
_SQL_
        $target = $dbh->selectrow_hashref($sql, undef, @val);

        $sql = <<_SQL_;
update  opl_sessionVar
set     note = 'target'
where   ssid = ?
     && var = ?
     && rid = ?
_SQL_
        @val = ($ssid, $varName, $target->{'rid'});
        $dbh->do($sql, undef, @val);
    }

    my $sOrder  = $target->{'sOrder'};
    my $rid     = $target->{'rid'};
    my $targetRec = getRecordById($target->{'rid'});
    $targetRec =~ s/<record>[\r\n\s]*<leader>/<record type='target' sOrder='$sOrder' rid='$rid'>\n  <leader>/;

    # Get the next record after the target record
    my $viewRec =
        SessionHdl_mhPrevNextRecord($dbh, $ssid, $varName, $sOrder, 'mhNext');

    return ($targetRec, $viewRec);
}


################################################################################
sub SessionHdl_mhPrevNextRecord {
    my ($dbh, $ssid, $varName, $sOrder, $op) = @_;

    my $sql = <<_SQL_;
select  max(sOrder)
from    opl_sessionVar
where   ssid = ?
     && var = ?
_SQL_

    my @val = ($ssid, $varName);
    my ($maxOrder) = $dbh->selectrow_array($sql, undef, @val);

    $sql = <<_SQL_;
select  *
from    opl_sessionVar
where   ssid = ?
     && var = ?
     && note <> 'target'
     && sOrder GEL ?
     && rid not in (
            select  rid
            from    opl_sessionVar
            where   ssid = ?
                 && var = ?
                 && sOrder = ?
        )
order by sOrder DIRECTION
limit 1
_SQL_

    @val = ($ssid, $varName, $sOrder, $ssid, $varName, $sOrder);
    # sOrder value assigned in this block is only used when no more record to 
    # display.
    if ($op eq 'mhPrev') {
        $sql =~ s/GEL/</;
        $sql =~ s/DIRECTION/desc/;
        $sOrder = 0;
    }
    else {
        $sql =~ s/GEL/>/;
        $sql =~ s/DIRECTION/asc/;
        $sOrder = $maxOrder + 1;
    }

    my $view = $dbh->selectrow_hashref($sql, undef, @val);
    my $viewRec;
    if ($view) {
        $sOrder = $view->{'sOrder'};
        my $rid = $view->{'rid'};
        $viewRec = getRecordById($view->{'rid'});
        $viewRec   =~ s/<record>[\r\n\s]*<leader>/<record type='view' sOrder='$sOrder' rid='$rid'>\n  <leader>/;
    }
    else { # no more record to display.
        $viewRec = "<record type='view' sOrder='$sOrder' rid='0' empty='true' />";
    }

    return $viewRec;
}


################################################################################
sub getRecordById {
    my ($rid) = @_;

    my $file = mxml_recordPath($rid) . '/' . $rid . '.xml';
    if (! -r $file) {
        return;
    }

    my $record = '';
    open MARCXML, "<$file";
    while (<MARCXML>) {
        $record .= $_;
    }
    close MARCXML;

    return $record;
}


################################################################################
sub SessionHdl_moveHolding {
    my ($dbh, $ssid, $varName) = @_;

    # get target rid
    my $sql = <<_SQL_;
select  mr.rid, mr.title, substr(mr.leader,6,1) as recType
from    opl_sessionVar sv
left join opl_marcRecord mr
on      sv.rid = mr.rid
where   sv.ssid = ?
     && sv.var = ?
     && sv.note = 'target'
group by sv.rid
_SQL_

    my @val = ($ssid, $varName);
    my $target = $dbh->selectrow_hashref($sql, undef, @val);

    # get source rids
    $sql =~ s/= 'target'/<> 'target'/;

    my $src = $dbh->selectall_hashref($sql, 'rid', undef, @val);

    # check if holdings are movable
    my $movableRecord = 'true';
    my $targetTitle = lc($target->{'title'});
    my $srcTitle;

    $targetTitle =~ s/[\.;: ]+$//g;
    foreach my $rid (keys %{$src}) {
        $srcTitle = lc($src->{$rid}->{'title'});
        $srcTitle =~ s/[\.;: ]+$//g;
        if ($targetTitle ne $srcTitle ||
            $target->{'recType'} ne $src->{$rid}->{'recType'}) {
            $movableRecord = 'false';
            last;
        }
    }

    my $targetRid = $target->{'rid'};

    return "<movingRecord movable='$movableRecord' targetRid='$targetRid'/>";
}

################################################################################
sub SessionHdl_setNote {
    my ($dbh, $ssid, $varName, $rid,$note) = @_;

    my $sql = <<_SQL_;
update  opl_sessionVar
set     note = concat_ws(': ','added to pathfinder',concat_ws(', ',replace(note,'added to pathfinder:',''),?))
where   ssid = ? && var = ? && rid = ?
_SQL_

    my @val = ($note,$ssid, $varName, $rid);
    $dbh->do($sql, undef, @val);

}
################################################################################
sub SessionHdl_setTarget {
    my ($dbh, $ssid, $varName, $rid) = @_;


    my $sql = <<_SQL_;
update  opl_sessionVar
set     note =if(rid=$rid,"target","") 
where   ssid = ? && var = ?
_SQL_

    my @val = ($ssid, $varName);
    $dbh->do($sql, undef, @val);
    
    #SessionHdl_setNote($dbh, $ssid, $varName, $rid,'target');

}

#-----------------------------------------------------------------------------------------------

sub SessionHdl_add{
    my ($dbh,$ssid,$varName,$rid,$bc,$note) = @_;
   
    $rid = $rid || undef ;
    $bc = $bc || undef;        
    $note = $note || '';        
    SessionHdl_del($dbh,$ssid,$varName,$rid,$bc);
    my $sth = $dbh->prepare(<<_STH_);
insert  into opl_sessionVar
set     ssid   =?,
        var    =?,
        rid    =?,
        barcode=?,
        note   =?
_STH_
    
   $sth->execute($ssid,$varName,$rid,$bc,$note);
   $sth->finish;
   
    
}

#-----------------------------------------------------------------------------------------------

sub SessionHdl_copy{
    my ($dbh,$ssid,$src,$target) = @_;
    my $sql="replace into opl_sessionVar(ssid,var,sOrder,rid) 
       select distinct ssid,'$target',sOrder,rid  
       from opl_sessionVar 
       where var=? && ssid=?";

    my $sth = $dbh->prepare($sql);
    $sth->execute($src,$ssid);
    $sth->finish;
}

#-----------------------------------------------------------------------------------------------

sub SessionHdl_del{
    my ($dbh,$ssid,$varName,$rid,$bc) = @_;   
    
   my $query = "delete  from opl_sessionVar where   ssid   = ? ";
   if($varName){
      $query .=  " && var = '$varName' ";
   }
   if($rid){
      $query .=  " && rid =$rid  ";
   }
   if($bc){
      $query .=  " && barcode ='$bc'  ";
   }
   my $sth = $dbh->prepare($query);
   $sth->execute($ssid);
    $sth->finish;
}
#-----------------------------------------------------------------------------------------------

sub SessionHdl_get{
    my ($dbh,$ssid,$varName,$offset,$count) = @_;
my $query="
select  distinct rid,barcode,note 
from    opl_sessionVar
where   ssid   = ? AND
        var    = ? ";
    if($count){
        $query .= "limit   $offset,$count";
    }
    
    my  $sth =  $dbh->prepare( $query);
    $sth->execute($ssid,$varName);
    my @retval=();
    while(my $h = $sth->fetchrow_hashref){
        push @retval,$h;
    }
  return \@retval;      
}


#-----------------------------------------------------------------------------------------------

sub SessionHdl_getBrRecListByHolding{
    my ($dbh,$ssid,$varName,$offset,$count,$filo) = @_;

    #my $sth = $dbh->prepare(<<_STH_);
    my $query="   
select distinct m.rid,m.title,m.author,s.barcode,callNumber
from opl_sessionVar s inner join opl_marcRecord  m on m.rid=s.rid
inner join opl_item i using(barcode)
where   ssid   = ? AND
        var    = ?   ";

if(defined $filo && $filo){
    $query .= " order by sOrder desc ";
}

$query .= " limit   $offset,$count    ";


my  $sth =  $dbh->prepare( $query);


    $sth->execute($ssid,$varName);
    my $isTarget;
    my $retXml="<collection>";
    my $preRid=-1;
    while (my ($rid,$title,$author,$barcode,$callNumber) = $sth->fetchrow_array) {
        if($rid ==$preRid){
            $retXml .="<holding><barcode>" .  util_escapeXml($barcode) . "</barcode>" .
                      "<callNumber>" . util_escapeXml($callNumber) . "</callNumber></holding>";
        }
        else{
            $retXml .="</record>" if($preRid !=-1);
            $preRid =$rid;
            $retXml .="<record><rid>" . $rid . "</rid>" . 
                  "<title>"  . util_escapeXml($title)  ."</title>" . 
                  "<author>" . util_escapeXml($author) ."</author>".
                  "<holding><barcode>" .  util_escapeXml($barcode) . "</barcode>" .
                  "<callNumber>" . util_escapeXml($callNumber) . "</callNumber></holding>";

        }

    }
    $retXml .="</record>" if($preRid !=-1);
    $retXml .="</collection>";
  return $retXml;      
}


#-----------------------------------------------------------------------------------------------

sub SessionHdl_getBriefRecList{
    my ($dbh,$ssid,$varName,$offset,$count,$filo) = @_;

    #my $sth = $dbh->prepare(<<_STH_);
    my $query="   
select distinct m.rid,m.title,m.author 
from opl_sessionVar s inner join opl_marcRecord  m on m.rid=s.rid
where   ssid   = ? AND
        var    = ?   ";

if(defined $filo && $filo){
    $query .= " order by sOrder desc ";
}

$query .= " limit   $offset,$count    ";


my  $sth =  $dbh->prepare( $query);


    $sth->execute($ssid,$varName);
    my $isTarget;
    my $retXml="<collection>";
    while (my $r = $sth->fetchrow_hashref) {
        $retXml .="<record><rid>" . $r->{'rid'} . "</rid>" . 
                  "<title>"  . util_escapeXml($r->{"title"})  ."</title>" . 
                  "<author>" . util_escapeXml($r->{"author"}) ."</author>";
        my @hList=   getHolding($dbh,$ssid,$varName,$r->{'rid'})      ;

        foreach my  $h(@hList){
            $retXml .="<holding><barcode>" .  util_escapeXml($h->{'barcode'}) . "</barcode>" .
                      "<callNumber>" . util_escapeXml($h->{'callNumber'}) . "</callNumber></holding>";
        }
        $retXml .="</record>";
    }
    $retXml .="</collection>";
  return $retXml;      
}

#-----------------------------------------------------------------------------------------------
sub getHolding{
    my ($dbh,$ssid,$varName,$rid)=@_;

    my $sth = $dbh->prepare(<<_STH_);
select i.barcode,i.callNumber from opl_item i inner join opl_sessionVar s
on i.rid=s.rid && i.barcode=s.barcode
where   ssid   = ? AND
        var    = ? AND
        s.rid=   ?
_STH_

$sth->execute($ssid,$varName,$rid);
    my @retval=();
    while(my $h = $sth->fetchrow_hashref){
        push @retval,$h;
    }
   return @retval;

}
#-----------------------------------------------------------------------------------------------

sub SessionHdl_clearVar{
    my ($dbh,$ssid,$varName) = @_; 

    if(!$ssid || $ssid eq ''){
        return;
    }
    my $sqlStr= "delete from opl_sessionVar where ssid='$ssid' ";
    if($varName && $varName ne ''){
        $sqlStr .=  "&& var='$varName'";
    }

    my $sth = $dbh->prepare($sqlStr);
    $sth->execute();     

}
#-----------------------------------------------------------------------------------------------

sub SessionHdl_clean{
    my ($dbh,$sessionTimeOutLength) = @_;  
    if(!$sessionTimeOutLength || $sessionTimeOutLength eq '' ){
        return;
    }
        
    my $sth = $dbh->prepare(<<_STH_);
delete s1.* from opl_sessionVar s1 inner join
   (select ssid from opl_sessionVar group by ssid 
    having max(timestamp) < SUBDATE(NOW(), INTERVAL ? MINUTE)) as s2
   on s1.ssid =s2.ssid   
_STH_

    $sth->execute($sessionTimeOutLength);     

}
#-----------------------------------------------------------------------------------------------
sub SessionHdl_exist_rid{
    my ($dbh,$ssid,$varName,$rid) = @_;  
    if(!$ssid || $ssid eq '' || !$varName || $varName eq ''){
        return 0;
    }
        
    my $sth = $dbh->prepare(<<_STH_);
select rid from opl_sessionVar where ssid=? && var=?  && rid= ?  
_STH_

    $sth->execute($ssid,$varName,$rid);     
    if(my $r =$sth->fetchrow_hashref){
        return 1;
    }
    return 0;
}
#-----------------------------------------------------------------------------------------------
sub SessionHdl_exist_bc{
    my ($dbh,$ssid,$varName,$bc) = @_;  
    if(!$ssid || $ssid eq '' || !$varName || $varName eq ''){
        return 0;
    }
        
    my $sth = $dbh->prepare(<<_STH_);
select rid from opl_sessionVar where ssid=? && var=?  && barcode= ?  
_STH_

    $sth->execute($ssid,$varName,$bc);     
    if(my $r =$sth->fetchrow_hashref){
        return 1;
    }
    return 0;
}

#-----------------------------------------------------------------------------------------------
sub SessionHdl_count{
    my ($dbh,$ssid,$varName) = @_;  
    if(!$ssid || $ssid eq '' || !$varName || $varName eq ''){
        return 0;
    }
        
    my $sth = $dbh->prepare(<<_STH_);
select count(distinct rid) as ridCount ,count(barcode) as bcCount from opl_sessionVar where ssid=? && var=? 
_STH_

    $sth->execute($ssid,$varName);     
    if(my $r =$sth->fetchrow_hashref){
        return ($r->{'ridCount'},$r->{'bcCount'});
    }
    return (0,0);
}
#-----------------------------------------------------------------------------------------------

sub SessionHdl_clear{
    my ($dbh,$ssid) = @_;  
my $sth = $dbh->prepare(<<_STH_);
delete from opl_sessionvar where ssid=?   
_STH_

    $sth->execute($ssid);     
}



1;