#!/usr/bin/perl

#use utf8;
use strict;
use CGI;

use Opals::Constant;
use Opals::Context;
use Date::Calc qw(Day_of_Week Week_Number Day_of_Year);
use Opals::Date qw(
    date_parse
    date_today
    date_text
    date_f005
);
use Opals::Template qw(
    tmpl_read
    tmpl_write
    tmpl_preference
    tmpl_rangedPageList

);
use Digest::SHA qw(
    sha512_base64
);
use Opals::User qw(
    user_getInformationById
);
my $dbh = Opals::Context->dbh();
END { $dbh->disconnect(); }

my $cgi = CGI->new;
# $cgi->param('aaa') returns an array of aaa
my $input = $cgi->Vars();
my $op = $input->{'op'};

my ($permission, $cookieList, $template) = tmpl_read(
    {
        dbh             => $dbh,
        cgi             => $cgi,
        tmplFile        => 'record/ge852.tmpl',
        reqPermission   => 'marc_edit',
    }
);
my $loginuid = $template->param('curUserId');
my $syspref = tmpl_preference($dbh);
my $timeout = $syspref->{'timeout'}; 

my $self = $ENV{'SCRIPT_NAME'};
my ($who,$where,$userInfo, $guardian);

  
    if ($permission && $permission->{'marc_edit'}) {
        my $recordList;
            
        my $dateToday = date_f005();
        $dateToday =~ s/([\d]{4})([\d]{2})([\d]{2})[\d]+\.(0|1)/$1-$2-$3/;
           
       # GET REQUESTS FROM DATA
         my $requests852 = get852Request($dbh);
         $template->param(
             requests852Lst =>  $requests852 ,
         );  
      
        # START search subfield
        my ($code, $data) = ($input->{'code'}, $input->{'data'});
        my $sortField = $input->{'sField'};
        ($sortField =~ m/^(rid|title|author|callNumber|dateImport)$/i) || ($sortField = 'rid');
        my $sortOrder = $input->{'sOrder'};
        ($sortOrder =~ m/^(asc|desc)$/i) || ($sortOrder = 'asc');
        my $requestStatus ;
        
        if ( $op eq '' || $op eq 'view' ) {  
            if ($code && $code ne '') { 
                getRecordsWithSf($dbh,$code,$data,$sortOrder,$sortField); 
                $template->param(
                    searchedRec     =>  1,                       
                );
            }# if ($code && $code ne '')
        }#  if ($op eq 'view'  )
        else{  # if ( $op nq '' ) >>>> Append/Update/Delete for subfield852  of record 
          #my $loginuid = $template->param('curUserId'); 
          my $rid_bc_list;
          my($digest,$o_code,$n_code,$o_data,$n_data,$action,$status);
          $digest = sha512_base64(time . rand(time));
          $o_code = $input->{'sCode'};
          $n_code = $input->{'n_sCode'}; 
          $o_data = $input->{'sData'}; 
          $n_data = $input->{'n_sData'};  
          $status = 'waiting';
         # $action = $input->{'action852op'};
         $action = $op;
             if($input->{'whole'}==1){
                 $rid_bc_list=getRecordsWithSf_whole($dbh,$o_code,$o_data); 
              }      
              else {         
                  $rid_bc_list = $input->{'selRidList'};       
              }
              
              $rid_bc_list =~ s/\]\[/,/g;
              $rid_bc_list =~ s/\[|\]//g;
                
             my $reqExist ='';
            
             if ($reqExist  ne 'true'){
                  # later  check ($digest) before insert -- in case edit at the same time
                  if (($o_code ne '' || $n_code ne '' || $o_data ne '' || $n_data) && $action ne''){             
                    $requestStatus = putDataIntoTbl($dbh,$digest,$loginuid,$rid_bc_list, $o_code,$n_code,
                                                    $o_data,$n_data,$action,$status); 
  # GET REQUESTS FROM DATA
          $requests852 = get852Request($dbh);
         $template->param(
             requests852Lst =>  $requests852 ,

         );  

                    
                    $template->param(
                        msgDlg        =>  1 ,
                        msgRequest_OK => ($requestStatus==1)? 1 : 0, 
                        msgRequest_FL => ($requestStatus==1)? 0 : 1,
                     );    
                }
             }#if ($reqExist  ne 'true')
             else{
                    $template->param(
                        msgDlg =>  1 , 
                        msgRequestDup =>1,
                    );    
             }#if ($reqExist  eq 'true')

             if($action eq 'update'){
                $template->param(
                   act852Updt => 1,
                   act852op   => "update"
               );
             }
             elsif($action eq 'delete'){
                $template->param(
                    act852Delt => 1,
                    act852op   => "delete"
               );
             }
             else{
                $template->param(
                    act852Appd => 1,
                    act852op   => "append"
               );
             }
                      
        } # ENDif ( $op nq '' )
     

        $template->param(
            uid =>$loginuid,
            ge852Edit       => 1,
            marcEdit        => ($permission && $permission->{'marc_edit'}) ? 1 : 0,
            sField => $sortField,
            sOrder => $sortOrder,
        );
        
            $template->param( 
                sf852OptionY  =>1,
                 sf852Op   =>1
            );   

    }#//if ($permission && $permission->{'marc_edit'}) 
        
#}# END record not lock
tmpl_write($dbh, $cgi, $cookieList, $template);

################################################################################
sub isRequestExist{
   my ($dbh,$o_code,$n_code,$o_data,$n_data,$dateToday,$action,$rid_bc_list) = @_;
    my $retval = 'false';
    my $reqid ='';
    my $sth;
$sth = $dbh->prepare(<<_STH_);
select req_id  from opl_ge852request 
where o_code = ? && 
      n_code = ? &&
      o_data = ? &&
      n_data = ? &&
      action = ? &&
      finishDate is NULL;
_STH_
    $sth->execute($o_code,$n_code,$o_data,$n_data,$action);
    ($reqid) = $sth->fetchrow_array;
    $sth->finish;
    if ($reqid ne ''){
        $retval = 'true';
    }
    return $retval;   
}
################################################################################
sub getRecordsWithSf_whole{
     my ($dbh,$code,$data)= @_;
     my $MIN_LENGTH = 4;

     my $sql = <<_SQL_;
select  i.rid, i.barcode 
from    opl_itemInfo as ii, opl_item as i, opl_marcRecord as m
where   ii.sf852Code = ?
_SQL_
    
    my @bindVal = ($code);
    $data =~ s/\*/%/g;
    my $dataCondition = '';
    if ($input->{'sType'} =~ m/^exact$/i || $data eq '') {
        $dataCondition = " && ii.sf852Data = ?";
        @bindVal = (@bindVal, $data);
    }
    elsif ($data =~ m/[\%]/ || $data eq '') {
        $dataCondition = " && ii.sf852Data like ?";
        @bindVal = (@bindVal, $data);
    }
    elsif ($data =~ m/^".+"$/  || $data eq '') {
        $dataCondition = " && ii.sf852Data like ?";
        $data =~ s/(^"|"$)/%/g;
        @bindVal = (@bindVal, $data);
    }
    else {
        foreach my $word (split(/ /, $data)) {
            if (length $word < $MIN_LENGTH) {
                $dataCondition .= " && (ii.sf852Data = ? || ii.sf852Data like ? || ii.sf852Data like ? || ii.sf852Data like ?)";
                @bindVal = (@bindVal, $word, "$word %", "% $word %", "% $word");
            }
            else {
                $dataCondition .= " && match(sf852Data) against (?)";
                @bindVal = (@bindVal, $word);
            }
        }
    }
  
    $sql .= <<_SQL_;
    $dataCondition && 
ii.barcode = i.barcode &&
i.rid = m.rid
group by i.rid
_SQL_
    my $sth = $dbh->prepare($sql);
    $sth->execute(@bindVal);
    my $retVal; 
    
    while (my $sf = $sth->fetchrow_hashref) {
        $retVal .= '[' . $sf->{'rid'}  . ':' . $sf->{'barcode'} . ']';
    }
    $sth->finish;
    return $retVal;
}

################################################################################     
sub get852Request{
    my ($dbh)= @_;
    my $sql = <<_SQL_;
select rq.*, u.username,rc.rid
from (opl_ge852record as rc inner join opl_ge852request as rq ON rc.req_id= rq.req_id)
inner join opl_user as u 
ON u.uid = rq.uid 
GROUP BY rq.req_id
ORDER BY rq.reqDate desc
_SQL_
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    my @retVal;
    my $statusDone = 0 ;
    while (my $sf = $sth->fetchrow_hashref) {
        if( $sf->{'status'} eq 'done'){
            $statusDone = 1;
        }
        $sf->{'statusDone'} = $statusDone;
        push @retVal, $sf;  
    }
    $sth->finish;

    return \@retVal;
 }



################################################################################
sub getRecordsWithoutSf_whole_bk{
       my ($dbh,$code,$dateFrom,$dateTo)= @_;
       my $sql = <<_SQL_;
select i.rid,i.barcode 
from  opl_marcRecord as m, opl_item as i  left outer join opl_itemInfo as ii 
ON ii.barcode = i.barcode AND ii.sf852code =?   
where ii.sf852code is NULL AND m.rid= i.rid &&
      substring(i.barcode, 1, 3) <> '___'&&
      i.dateImport >= '$dateFrom' && i.dateImport <='$dateTo'   
group by i.rid
_SQL_
    my $sth = $dbh->prepare($sql);
    $sth->execute($code);
    my $retVal;

     while (my $sf = $sth->fetchrow_hashref) {
        $retVal .= '[' . $sf->{'rid'}  . ':' . $sf->{'barcode'} . ']';
    }   
    $sth->finish;
    return $retVal;  
}

################################################################################

sub getRecordsWithSf{
     my ($dbh,$code,$data,$sortOrder,$sortField)= @_;
       
        my $MIN_LENGTH = 4;
        my $pSize = 20;
        my $pNum  = $input->{'pNum'};
        $pNum = 1 if ( !$pNum );
        my $pOffset = ($pNum - 1) * $pSize;
      
        my $sql = <<_SQL_;
select  i.rid, i.callNumber,i.barcode,
        m.title, m.author
from    opl_itemInfo as ii, opl_item as i, opl_marcRecord as m
where   i.available = 1 &&  
        substring(i.barcode, 1, 3) <> '___' &&        
        ii.sf852Code = ?
_SQL_
    my @bindVal = ($code);
    $data =~ s/\*/%/g;
    my $dataCondition = '';
    if ($input->{'sType'} =~ m/^exact$/i || $data eq '') {
        $dataCondition = " && ii.sf852Data = ?";
        @bindVal = (@bindVal, $data);
    }
    elsif ($data =~ m/[\%]/ || $data eq '') {
        $dataCondition = " && ii.sf852Data like ?";
        @bindVal = (@bindVal, $data);
    }
    elsif ($data =~ m/^".+"$/  || $data eq '') {
        $dataCondition = " && ii.sf852Data like ?";
        $data =~ s/(^"|"$)/%/g;
        @bindVal = (@bindVal, $data);
    }
    else {
        foreach my $word (split(/ /, $data)) {
            if (length $word < $MIN_LENGTH) {
                $dataCondition .= " && (ii.sf852Data = ? || ii.sf852Data like ? || ii.sf852Data like ? || ii.sf852Data like ?)";
                @bindVal = (@bindVal, $word, "$word %", "% $word %", "% $word");
            }
            else {
                $dataCondition .= " && match(sf852Data) against (?)";
                @bindVal = (@bindVal, $word);
            }
        }
    }
    $sql .= <<_SQL_;
    $dataCondition && 
ii.barcode = i.barcode &&
i.rid = m.rid
group by i.rid
order by $sortField $sortOrder
_SQL_
    my $sth = $dbh->prepare($sql);
#limit $pOffset, $pSize

        $sth->execute(@bindVal);
        my @recDisplay;
        my $count = 0;
        my $pMax  = $pOffset + $pSize;
        my $rec_end = $pOffset;
        my $tt='';
        while (my $r = $sth->fetchrow_hashref) {
            if ($count >= $pOffset && $count < $pMax) {
                $r->{'holding'} = 
                    getHoldingInfo($dbh, $r->{'rid'}, $dataCondition, \@bindVal);
                $r->{'holdingCount'} = scalar @{$r->{'holding'}};
                
                push @recDisplay, $r;
                $rec_end++;
            }
            $count++;
                    
             
        }
        $sth->finish;
         
        my @rangedPageList = tmpl_rangedPageList($count, $pNum, $pSize, 10);
        $template->param(
            recDisplay      => \@recDisplay,
            rec_total       => $count,
            rec_start       => $pOffset+1,
            rec_end         => $rec_end,
            rangedPageList  => \@rangedPageList,
            iCode           => $input->{'code'},
            iData           => $input->{'data'},
            "sort_$sortField" => 1,
            exactSearch     => ($input->{'sType'} =~ m/^exact$/i) ? 1 : 0,
            marcEdit        => ($permission && $permission->{'marc_edit'}) ? 1 : 0,
            ge852Edit       => 1,
            cur_f852Code    =>$code ,
            sql_debug => $sql ,
            );

}
################################################################################
sub getRecordsWithoutSf_bk{
# CASE: record have more than one holding 
# and one of holdings has no subfield 
       my ($dbh,$code,$sortOrder,$sortField,$dateFrom,$dateTo)= @_;
       
        my $MIN_LENGTH = 4;
        my $pSize = 20;
        my $pNum  = $input->{'pNum'};
        $pNum = 1 if ( !$pNum );
        my $pOffset = ($pNum - 1) * $pSize;
      
        my $sql = <<_SQL_;
select i.rid,i.callNumber,i.dateImport,i.barcode, m.title, m.author 
from  opl_marcRecord as m, opl_item as i  left outer join opl_itemInfo as ii 
ON ii.barcode = i.barcode AND ii.sf852code =?   
where ii.sf852code is NULL AND m.rid= i.rid &&
      substring(i.barcode, 1, 3) <> '___' &&
      i.available =1 && 
      i.dateImport >= '$dateFrom' && i.dateImport <='$dateTo'  
group by i.rid
order by $sortField $sortOrder      
_SQL_
    my $sth = $dbh->prepare($sql);
        $sth->execute($code);
        my @recDisplay;
        my $count = 0;
        my $pMax  = $pOffset + $pSize;
        my $rec_end = $pOffset;
    my $dataCondition = '';
    my @bindVal = ($code);
        while (my $r = $sth->fetchrow_hashref) {
            if ($count >= $pOffset && $count < $pMax) {
                $r->{'holding'} = 
                    getHoldingInfo_WithoutSf_bk($dbh, $r->{'rid'}, $dataCondition, \@bindVal,$dateFrom,$dateTo);
                $r->{'holdingCount'} = scalar @{$r->{'holding'}};
                    
                push @recDisplay, $r;
                $rec_end++;
            }
                    $count++;
             
        }
        $sth->finish;
        
        my @rangedPageList = tmpl_rangedPageList($count, $pNum, $pSize, 10);
        $template->param(
            recDisplay      => \@recDisplay,
            rec_total       => $count,
            rec_start       => $pOffset+1,
            rec_end         => $rec_end,
            rangedPageList  => \@rangedPageList,
            iCode           => $input->{'code'},
           # iData           => $input->{'data'},
            "sort_$sortField" => 1,
            exactSearch     => ($input->{'sType'} =~ m/^exact$/i) ? 1 : 0,
            marcEdit        => ($permission && $permission->{'marc_edit'}) ? 1 : 0,
            ge852Edit       => 1,
            cur_f852Code     =>$code ,
            dateFrom        =>$dateFrom,
            dateTo        =>$dateTo,          
                 sql_debug => $sql,
            );


}
################################################################################

sub getHoldingInfo {
    my ($dbh, $rid, $dataCondition, $bindValue) = @_;
    $dataCondition =~ s/^ [\&]{2} //;
    my @bindVal = ($rid, @{$bindValue});
    my $sth;

$sth = <<_SQL_;
select  i.*,
        ii.sf852Data
from    opl_item as i, opl_itemInfo as ii
where   i.rid = ? &&
        i.barcode = ii.barcode &&
        ii.sf852Code = ?  
_SQL_

if ($dataCondition ne ''){
  $sth .= " &&  $dataCondition "
}
   $sth = $dbh->prepare($sth);

    $sth->execute(@bindVal);
    my @holdingInfo;
    while (my $sf = $sth->fetchrow_hashref) {
        $sf->{'dateImport'} =~ s/ [\d]{2}:[\d]{2}:[\d]{2}//; #remove hour min sec
        push @holdingInfo, $sf;
    }
    $sth->finish;


    return \@holdingInfo;
}
################################################################################

sub getHoldingInfo_WithoutSf_bk {
    my ($dbh, $rid, $dataCondition, $bindValue,$dateFrom,$dateTo) = @_;
    $dataCondition =~ s/^ [\&]{2} //;
    my @bindVal = (@{$bindValue},$rid);
    my $sth;

$sth = <<_SQL_;

select  i.*,ii.sf852Data 
from    opl_item as i left outer join opl_itemInfo as ii 
        ON i.barcode = ii.barcode && ii.sf852Code = ? 
where   i.rid = ? AND ii.sf852code is NULL &&
        substring(i.barcode, 1, 3) <> '___' &&
        i.dateImport >= '$dateFrom' && i.dateImport <='$dateTo'
group by i.barcode    
_SQL_


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

    $sth->execute(@bindVal);
    my @holdingInfo;
    while (my $sf = $sth->fetchrow_hashref) {
        $sf->{'dateImport'} =~ s/ [\d]{2}:[\d]{2}:[\d]{2}//; #remove hour min sec
        push @holdingInfo, $sf;
    }
    $sth->finish;

    return \@holdingInfo;
}
################################################################################
sub putDataIntoTbl{
     my ($dbh,$digest,$loginuid,$rid_bc_list, $o_code,$n_code,$o_data,$n_data,$action,$status) = @_;
     my $sth ;

     # ===== opl_ge852request TABLE ======
             $sth = $dbh->prepare(<<_STH_);
insert into opl_ge852request (digest,uid, o_code, n_code, o_data, n_data, reqDate, action,status) 
values (?,?,?,?,?,?,?,?,now(),?,?)
_STH_
       $sth->execute($digest,$loginuid, $o_code,$n_code,$o_data,$n_data,$action,$status) || return FALSE;

       $sth->finish;
    
     # ===== Get new req_id from opl_ge852record TABLE ======
 my $newReqid;
$sth = $dbh->prepare(<<_STH_);
select max(req_id) from opl_ge852request;
_STH_
    $sth->execute() || return FALSE;
    ($newReqid) = $sth->fetchrow_array;
    $sth->finish;


     # ===== opl_ge852record TABLE ======
     my $rid;
     my @a= split(/,/,$rid_bc_list);
     for (my $i=0; $ i <scalar(@a);$i++){
            my @b=split(/:/,@a[$i]);
            if(scalar(@b)==2){
               $rid  = @b[0];
               $sth = $dbh->prepare(<<_STH_);
insert into opl_ge852record (req_id ,rid) 
values (?,?)
_STH_
                $sth->execute($newReqid,$rid) || return FALSE;
                $sth->finish;
              
            }
      }

  return TRUE;   
    
}

################################################################################
sub updatePreference {
    my ($dbh,$uid,$applName) = @_;
    my $preference;
    my $val = $uid .','. $applName;
    my $sth = $dbh->prepare(<<_STH_);
update opl_preference 
   set val= '$val'
where var = 'recordLock';
_STH_
    $sth->execute() ;
    $sth->finish;
}