package Opals::ReserveShelf;
require Exporter;
@ISA       = qw(Exporter);

@EXPORT_OK = qw(
    rs_getBookRsList
    rs_getReserveShelfByID
    rs_getReserveShelfByField
    rs_getReserveShelfByGrp
    rs_getMaxGroupItem
    rs_delete
    rs_clone
    rs_save
    rs_getRShelfFromRecordId
);
use Opals::BookCover qw(
    bookCover_opals

);

# Version number
$VERSION   = 0.01;      

#use utf8;
use strict;
use POSIX qw(
    ceil
    floor
);

#======================================================================
sub rs_getRShelfFromRecordId{
    my ($dbh,$rid) = @_;
    my $rsList =[];
my  $sth = $dbh->prepare(<<_STH_);
    select distinct shelfId,title from opl_reserveShelf r inner join opl_reserveShelfItems using(shelfId) where rid=?
_STH_

    $sth->execute($rid); 
    while (my $rs =$sth->fetchrow_hashref){
        push @$rsList,$rs;
    }
    $sth->finish;
    return $rsList;

}
#======================================================================
sub rs_getReserveShelfByGrp{
    my ($dbh,$grp,$offset,$numOfRec,$op) = @_;
    my $srList={};
    my @srArr=();
    my $sql;
    if($grp eq 'titleApl'){
        $sql= "select ucase(left(title,1)) `group`,shelfId, title,author ,teacher, course ,createdDate ,modDate,reserveFrom,reserveTo,introduction from opl_reserveShelf r " ;
        if(defined $op && $op eq "avail"){
           $sql .=" where r.deleted =0 && reserveTo > now()";
       }
        $sql .=" order by title,reserveFrom ";
    }
    elsif($grp eq 'subject'){
        $sql= "select subject `group`, shelfId,title,author,teacher, course ,createdDate ,modDate,reserveFrom,reserveTo ,introduction from opl_reserveShelf r 
                left outer join opl_reserveShelfSubject s using(shelfId)";

        if(defined $op && $op eq "avail"){
           $sql .=" where r.deleted =0 && reserveTo > now()";
       }
        $sql .="  order by subject, title,reserveFrom ";
    }
    elsif($grp eq 'teacher' || $grp eq 'course'){
        $sql= "select " .$grp . "  `group`, shelfId,title,author,teacher, course ,createdDate ,modDate,reserveFrom,reserveTo ,introduction from opl_reserveShelf ";

        if(defined $op && $op eq "avail"){
           $sql .=" where deleted =0 && reserveTo > now()";
       }
        $sql .="  order by " . $grp .",title,author, reserveFrom ";
    }
    else{
        return \@srArr;
    }
    if(defined $numOfRec && $numOfRec>0){
        if(!$offset){
            $offset =0;
        }
        $sql .= " limit $offset,$numOfRec";
    }
    my $sth = $dbh->prepare($sql);  

    $sth->execute(); 
    
    while(my $rec= $sth->fetchrow_hashref){
        $rec->{'subjectList'} = _getSubjectAreaList($dbh,$rec->{'shelfId'});
        $rec->{'group'}= uc(substr($rec->{'group'},0,1)) . substr($rec->{'group'},1);
        $rec->{'reserveDate'} = substr($rec->{'reserveFrom'},0,10)  . "   -  " .  substr($rec->{'reserveTo'},0,10);
        $rec->{'reserveFrom'} = substr($rec->{'reserveFrom'},0,10);
        $rec->{'reserveTo'} = substr($rec->{'reserveTo'},0,10);

        push @{$srList->{$rec->{'group'}}},$rec;

    }
    foreach my $key (sort keys %$srList){
        push @srArr,{group=>$key,srRecList=>$srList->{$key}};
    } 

    return \@srArr;
}
#======================================================================
sub rs_getReserveShelfByField{
    my ($dbh,$sField,$sVal,$op)= @_;
    my @srArr=();
    my $srList={};
    my $sql="select  shelfId,title,author,teacher, course,createdDate ,modDate,reserveFrom,reserveTo ,introduction from opl_reserveShelf  where deleted =0 ";
   if(defined $op && $op eq "avail"){
        $sql .=" &&  reserveTo > now()";
    }
   
    if($sField &&  $sVal && $sField ne'' &&  $sVal ne ''){
        if($sField  =~ m/createdDate|modDate/s){
            $sql .= " && $sField  regexp  ?   order by title";
        }
        else{
            $sql .= " && $sField = ?   order by title ";
        }
    }
    else{
        return \@srArr;
    }

    my $sth = $dbh->prepare($sql);
    $sth->execute($sVal); 
    while(my $rec= $sth->fetchrow_hashref){
        $rec->{'subjectList'} = _getSubjectAreaList($dbh,$rec->{'shelfId'});
        $rec->{'createdDate'} = substr($rec->{'createdDate'},0,10);
        $rec->{'reserveFrom'} = substr($rec->{'reserveFrom'},0,10);
        $rec->{'reserveTo'} = substr($rec->{'reserveTo'},0,10);
        push @{$srList->{$rec->{$sField}}},$rec;
    }
    foreach my $key (sort keys %$srList){
        push @srArr,{group=>$key,srRecList=>$srList->{$key}};
    } 

    return \@srArr;

}
#======================================================================
sub _getSubjectAreaList{
    my ($dbh,$shelfId) = @_;  
    my $sth = $dbh->prepare(<<_STH_);
   select subject from opl_reserveShelfSubject where shelfId =? 
_STH_
    my @sbjList=();
    $sth->execute($shelfId);
    while(my $sbj=$sth->fetchrow_array){
        push @sbjList,{subject=>$sbj};
    }
    return \@sbjList;

}


#======================================================================
sub rs_getBookRsList{
   my ($dbh,$shelfId,$orderBy) = @_;

    my $sql = "select  distinct r.groupId,r.rid,i.callNumber callNumber_first,r.callNumber,
                m.isbn,m.author,m.title,m.deleted ,m.pubPlace,m.pubName,m.pubDate,m.pubDateSort,m.titleSort,
                g.header,g.description
   from    opl_reserveShelfItems r inner join opl_reserveShelfGroup g on r.shelfId=g.shelfId && r.groupId=g.groupId
           inner join opl_marcRecord m on m.rid= r.rid left outer join opl_item i on i.rid =r.rid && i.barcode not regexp '^___'
   where   r.shelfId=? && i.rid is not null ";
   if(defined $orderBy && $orderBy ne ''){
       $sql .=" order by r.groupId , $orderBy ";
   }
   else{
       $sql .="  order by iOrder "; 
   }

#open debug ,">/tmp/aa"; print debug "$sql\n";close debug;
 my $sth = $dbh->prepare($sql);

   my @rsList=();
   $sth->execute($shelfId);
   my $rsTbl={};
   my $order=0;
   while(my $rec=$sth->fetchrow_hashref){
       if(!defined $rsTbl->{$rec->{'groupId'}}){
            $rsTbl->{$rec->{'groupId'}}->{'header'}=$rec->{'header'};
            $rsTbl->{$rec->{'groupId'}}->{'description'}=$rec->{'description'};
            
       }
       if(!defined $rsTbl->{$rec->{'groupId'}}->{'itemList'}->{$rec->{'rid'}}){
           if($rec->{"callNumber"} ne $rec->{"callNumber_first"}){
               my ($tmpCn)=$dbh->selectrow_array("select callNumber from opl_item where rid=? && callNumber=?",undef,$rec->{"rid"},$rec->{"callNumber"});
               if(!$tmpCn){
                   $rec->{"callNumber"}=$rec->{"callNumber_first"};
               }
           }
           foreach my $f (qw(callNumber author title pubPlace pubName pubDate isbn pubDateSort titleSort )){
                $rsTbl->{$rec->{'groupId'}}->{'itemList'}->{$rec->{'rid'}}->{$f}=$rec->{$f};
           }
           $rsTbl->{$rec->{'groupId'}}->{'itemList'}->{$rec->{'rid'}}->{'order'}=$order++;
       }
    }
    
    foreach my $grpId(sort keys %$rsTbl){
        my $rs={header     =>$rsTbl->{$grpId}->{'header'},
                description=>$rsTbl->{$grpId}->{'description'} ,
                groupId     =>$grpId,
                itemList    =>[]              
                };
        my $itemList =$rsTbl->{$grpId}->{'itemList'};     
        foreach my $rid(keys %$itemList){
            my $item=  {
                        order      =>$itemList->{$rid}->{'order'},
                        rid        =>$rid,
                        callNumber =>$itemList->{$rid}->{'callNumber'},
                        author     =>$itemList->{$rid}->{'author'},
                        title      =>$itemList->{$rid}->{'title'},
                        pubPlace   =>$itemList->{$rid}->{'pubPlace'},
                        pubName    =>$itemList->{$rid}->{'pubName'},
                        pubDate    =>$itemList->{$rid}->{'pubDate'},
                        pubDateSort=>$itemList->{$rid}->{'pubDateSort'},
                        titleSort  =>$itemList->{$rid}->{'titleSort'},
                        isbn_first =>$itemList->{$rid}->{'isbn'} 

                     };
            if($itemList->{$rid}->{'isbn'} ne ""){
                $item->{'isbn'}= [{item=>$itemList->{$rid}->{'isbn'}}];
            }
            push @{$rs->{'itemList'}},$item;          

        }
        @{$rs->{'itemList'}} = sort {$a->{'order'}<=>$b->{'order'}} @{$rs->{'itemList'}};
        bookCover_opals($rs->{'itemList'});
        push @rsList,$rs;
    }
    return \@rsList;
}

#======================================================================
sub rs_getReserveShelfByID{
    my ($dbh,$shelfId,$orderBy)= @_;
    my $sth= $dbh->prepare(<<_SQL_);
select  *  from opl_reserveShelf 
where      shelfId = ?
_SQL_
    $sth->execute($shelfId);
    my $reserveShelf={};
    if(my $s= $sth->fetchrow_hashref){
        $s->{'reserveForm'} = substr($s->{'reserveForm'},0,10);
        $s->{'reserveTo'}   = substr($s->{'reserveTo'},0,10);
        $s->{'subjectList'} = _getSubjectAreaList($dbh,$shelfId);
        $s->{'rsList'}      = rs_getBookRsList($dbh,$shelfId,$orderBy);
        $s->{'userList'}    = rs_getUserList($dbh,$shelfId);
        if($s->{'loanType'} eq 'daily'){
            $s->{'loanPeriod'} = floor($s->{'loanPeriod'} /24);
        }
        elsif($s->{'loanType'} eq 'default'){
            $s->{'loanPeriod'} =0;
        }
        $reserveShelf= $s;
    }

    $sth->finish;
    return $reserveShelf;
}

#======================================================================
sub rs_getUserList{
    my($dbh,$shelfId)=@_;
    my $sth = $dbh->prepare("select distinct uid ,lastName,firstName  
                             from opl_reserveShelfUsers r 
                                  inner join opl_user u using(uid)where shelfId=? 
                              order by firstName ,lastName
                              ");
    my @userList=();
    $sth->execute($shelfId);
    while(my ($uid,$lastName,$firstName)=$sth->fetchrow_array){
        my $userName = $firstName . " " . $lastName;
        push @userList,{uid     =>$uid,
                        userName=>$userName};
    }
    return \@userList;

}

#======================================================================
sub rs_getMaxGroupItem{
    my ($dbh,$shelfId) =@_;
    my $groupId =0;
    my $sth = $dbh->prepare("select max(groupId) from opl_reserveShelfGroup where shelfId=?");
    $sth->execute($shelfId);    
    ($groupId) = $sth->fetchrow_array;
    $sth->finish;
    return $groupId;
}

#======================================================================
sub rs_delete{
    my ($dbh,$shelfId) =@_;
    $dbh->do("delete from opl_reserveShelf where shelfId=$shelfId ");
    $dbh->do("delete from opl_reserveShelfGroup where shelfId=$shelfId ");
    $dbh->do("delete from opl_opl_reserveShelfItems where shelfId=$shelfId ");
    $dbh->do("delete from opl_reserveShelfSubject where shelfId=$shelfId ");

}



#======================================================================
sub rs_save{
    my ($dbh,$rsXml) =@_;
    my $srRec =_parseSRXml($rsXml);
    if($rsXml eq '' || $srRec->{'title'} eq ''){
        return 0;
    }
    my $shelfId = $srRec->{'shelfId'};
    if(defined $shelfId && $shelfId>0){
        $shelfId = _updateReserveShelf($dbh,$srRec);
    }
    else{
        $shelfId = _insertReserveShelf($dbh,$srRec);
    }
    return   $shelfId;
    
}
#======================================================================
sub _parseSRXml{
    #Thu, Nov 28, 2013 @ 08:27:42 EST
    my ($xml) =@_;
    my $ret={};
    $xml =~ s/<([a-zA-Z_0-9]+)\/>/<$1><\/$1>/g;
    my $tmpXml="$xml";
    ### SHELFT RESERVE INFO 
    my $fields=[{name=>'shelfId',       rep=>0}, #rep: 1=>repeatable, 0=>non-repeatable
                {name=>'title',         rep=>0},
                {name=>'author' ,       rep=>0},
                {name=>'teacher' ,      rep=>0},
                {name=>'reserveFrom' ,  rep=>0},
                {name=>'reserveTo' ,    rep=>0},
                {name=>'course' ,       rep=>0},
                {name=>'createdDate',   rep=>0},
                {name=>'subject',       rep=>1},
                {name=>'introduction',  rep=>0},
                {name=>'loanPeriod',    rep=>0},
                {name=>'loanType',      rep=>0}
              ];
    $tmpXml =~ s/<resourceList>(.*)<\/resourceList>//;
    foreach my $f(@$fields){
       $ret->{$f->{'name'}}=[] if($f->{'rep'}==1);
       if(!$f->{'rep'} && $tmpXml =~ m/<$f->{'name'}>(.*?)<\/$f->{'name'}>/s){
           $ret->{$f->{'name'}}=unescapeXML($1);           
       }
       else{
           while($tmpXml =~ m/<$f->{'name'}>(.*?)<\/$f->{'name'}>(.*)/s){
                $tmpXml=$2;
                push @{$ret->{$f->{'name'}}},{item=>unescapeXML($1)};
           }
       }
    }
   $ret->{'loanPeriod'} =~ s/\D//g;
   if($ret->{'loanPeriod'}>0){
        if($ret->{'loanType'} eq 'daily'){
            $ret->{'loanPeriod'} *=24;
        }
        elsif($ret->{'loanType'} eq 'default'){
            $ret->{'loanPeriod'}=0;
        }
   }
   else{
       $ret->{'loanType'} ='default';
       $ret->{'loanPeriod'}=0;
   }    
   ### RESOURCE ITEM(S) 
    my $rs=undef;
    $tmpXml=$xml;
    while($tmpXml =~ m/<resource>(.*?)<\/resource>(.*)/s){
        next if($1 eq '');
        $rs=_getLibRs($1);
        push @{$ret->{"resourceList"}},$rs if(defined $rs);
        $tmpXml=$2;    
    }


    ### USER LIST
    $ret->{"userList"}=_parserUserList($xml);

    return $ret;
}
#======================================================================
sub _parserUserList{
    my ($xml)=@_;
    my $userList=undef;
#    my $user    =undef;
#    my $tmpXml=$xml;
#    while($tmpXml =~ m/<uid>(.*?)<\/uid>(.*)/s){
#        next if($1 eq '');
#        $user->{'uid'} = $1;
#        push @{$userList},$user if(defined $user);
#        $tmpXml=$2;    
#     }

     my $tmpXml=$xml;
     while($tmpXml =~ m/<uid>(.*?)<\/uid>(.*)/s){
        next if($1 eq '');
        push @{$userList},$1 if(defined $1);
        $tmpXml=$2;    
    }

    return $userList;
}
#======================================================================
sub unescapeXML {
    my ($txt)=@_;
    $txt =~ s/&lt;/</g;
    $txt =~ s/&gt;/>/g;
    $txt =~ s/&amp;/&/g;
    $txt =~ s/&quot;/"/g;
    return $txt;
}

#======================================================================
sub  _getLibRs{
    my ($xml) =@_;
    my $libRs=undef;
    my $tmpXml=$xml;
    if($tmpXml =~ m/<resourceTitle>(.*?)<\/resourceTitle>/s){
        $libRs->{'resourceTitle'} =unescapeXML($1) if($1 ne'');
    }
    if($tmpXml =~ m/<resourceDesc>(.*?)<\/resourceDesc>/s){
        $libRs->{'resourceDesc'} =unescapeXML($1) if($1 ne'');
    }
    $tmpXml=$xml;
    $libRs->{'itemList'} =[] if(!defined $libRs->{'itemList'});
    while($tmpXml =~ m/<rid callNumber="(.*?)">(.*?)<\/rid>(.*)/s){
        push @{$libRs->{'itemList'}},{rid=>$2,callNumber=>$1};
        $tmpXml=$3;
    }
    
    return $libRs; 

}
#======================================================================
sub _insertReserveShelf{
    my ($dbh,$resvShelf)= @_;
    
    # TABLE: opl_reserveShelf
    my $sql_reserveShelf=" insert into opl_reserveShelf  
                           set  title=?,      author=?,  introduction=?, 
                                teacher=?   , course=?,   loanPeriod =?,loanType =?,
                                reserveFrom =?,reserveTo =? ,createdDate=now()";

    my $sth = $dbh->prepare($sql_reserveShelf);
    $sth->execute($resvShelf->{'title'},$resvShelf->{'author'},$resvShelf->{'introduction'},
                  $resvShelf->{'teacher'},$resvShelf->{'course'},$resvShelf->{'loanPeriod'},$resvShelf->{'loanType'},
                  $resvShelf->{'reserveFrom'},$resvShelf->{'reserveTo'});   
    my $shelfId = $dbh->{'mysql_insertid'};


    # TABLE:opl_reserveShelfGroup
    my $sql_shelfRsGrp ="insert into opl_reserveShelfGroup set shelfId=?, groupId=?,header=?,description=?";  
    my $sth_shelfRsGrp = $dbh->prepare($sql_shelfRsGrp);
   
    # TABLE:opl_reserveShelfItems
    my $sql_itemShelfRs ="insert into opl_reserveShelfItems set shelfId=?, groupId=?,rid=?,callNumber=?, iOrder=?";  
    my $sth_itemShelfRs = $dbh->prepare($sql_itemShelfRs);
    my $groupId=1;my $iOrder=0;
    foreach my $rs (@{$resvShelf->{'resourceList'}}){
        $sth_shelfRsGrp->execute($shelfId,$groupId,$rs->{'resourceTitle'}, $rs->{'resourceDesc'});
        foreach my $item (@{$rs->{'itemList'}}){
            $sth_itemShelfRs->execute($shelfId,$groupId,$item->{'rid'},$item->{'callNumber'},$iOrder++);
        }

        $groupId++;
    }
    _saveSubject($dbh,$shelfId,$resvShelf->{'subject'});
    $sth->finish;
    $sth_shelfRsGrp->finish;
    $sth_itemShelfRs->finish;
    return $shelfId;
}
#======================================================================
sub _updateReserveShelf{
    my ($dbh,$resvShelf)= @_;
    my $shelfId  = $resvShelf->{'shelfId'};
    # TABLE: opl_reserveShelf
    my $sql_reserveShelf=" update  opl_reserveShelf  
                           set     title=?,      author=?,  introduction=?, 
                                   teacher=?,   course=?,   loanPeriod =?,loanType =?,
                                   reserveFrom =?,reserveTo =? ,createdDate=now()
                           where   shelfId=?    ";

    my $sth = $dbh->prepare($sql_reserveShelf);
    $sth->execute($resvShelf->{'title'},$resvShelf->{'author'},$resvShelf->{'introduction'},
                  $resvShelf->{'teacher'},$resvShelf->{'course'},$resvShelf->{'loanPeriod'},$resvShelf->{'loanType'},
                  $resvShelf->{'reserveFrom'},$resvShelf->{'reserveTo'},$shelfId);   
    
   $dbh->do("update opl_reserveShelfGroup set deleted=1 where shelfId=$shelfId");
   $dbh->do("update opl_reserveShelfItems set deleted=1 where shelfId=$shelfId");
   $dbh->do("update opl_reserveShelfUsers set shelfId=10000000 where shelfId=$shelfId");
   # TABLE:opl_reserveShelfGroup
   my $groupId=1;
   my $sql_shelfRsGrp ="insert into opl_reserveShelfGroup set shelfId=?, groupId=?,header=?,description=?";  
   my $sth_grp = $dbh->prepare($sql_shelfRsGrp);
   # TABLE:opl_reserveShelfItems
   my $sql_itemShelfRs ="insert into opl_reserveShelfItems set shelfId=?, groupId=?,rid=?,callNumber=?, iOrder=?";  
   my $sth_item = $dbh->prepare($sql_itemShelfRs);
   # TABLE:opl_reserveShelfUsers
   my $sql_users ="insert into opl_reserveShelfUsers set shelfId=?, uid=?";  
   my $sth_users = $dbh->prepare($sql_users);
 
 
   # ITEM RESOURCE(S) 
   my $r; my $i;my $iOrder=0;
   foreach my $rs (@{$resvShelf->{'resourceList'}}){
        next if(scalar(@{$rs->{'itemList'}}==0 && !defined $rs->{'resourceTitle'} && !defined$rs->{'resourceDesc'}));
        $r= $sth_grp->execute($shelfId,$groupId,$rs->{'resourceTitle'}, $rs->{'resourceDesc'});

        foreach my $item (@{$rs->{'itemList'}}){
            $i = $sth_item->execute($shelfId,$groupId,$item->{'rid'},$item->{'callNumber'},$iOrder++);
        }
        $groupId++;
    }
    _saveSubject($dbh,$shelfId,$resvShelf->{'subject'});
    $sth->finish;
    $sth_grp->finish;
    $sth_item->finish;

   $dbh->do("delete from opl_reserveShelfGroup where shelfId=$shelfId && deleted=1")  if($r ne '0E0');
   $dbh->do("delete from opl_reserveShelfItems where shelfId=$shelfId && deleted=1")  if($i ne '0E0');


    # USER LIST
    foreach my $uid(@{$resvShelf->{'userList'}}){
        next if(!$uid || $uid eq '');
        $sth_users->execute($shelfId,$uid) ;
    }

    $dbh->do("delete from opl_reserveShelfUsers where shelfId=10000000");

    return $shelfId;
}
#======================================================================
sub _saveSubject{
    my ($dbh,$shelfId,$sbjList)= @_;
    my $sth = $dbh->prepare("delete from opl_reserveShelfSubject where shelfId=?");
    $sth->execute($shelfId);
    $sth = $dbh->prepare("insert into opl_reserveShelfSubject set shelfId=?, subject= ?");

    foreach my $sbj(@$sbjList){
        next if($sbj->{'item'} eq '');
        $sth->execute($shelfId,$sbj->{'item'}) ;
    }
    $sth->finish;
}



#======================================================================
1;