#!/usr/bin/perl 
## Notes:
#   Change table opl_reserve 
#      add fields : `priority` int(6) unsigned DEFAULT '1000', `suspendFrom` datetime DEFAULT NULL , `suspendUntil` datetime DEFAULT NULL,
#
use lib "/www/opals-working/module";;
use Opals::Context('/etc/opals/conf/opals_beta');
use strict;
use Opals::Constant; 
use Date::Calc::Object qw(
    :all
);
use Digest::SHA qw(
    sha1_hex
    sha512_hex
);

use POSIX qw(
    ceil
    floor
);
use Time::localtime;


use CGI;
use DBI;

use JSON;
my $dbh = Opals::Context->dbh();
END { $dbh->disconnect(); }
$dbh->do(<<_SQL_);
    alter table opl_reserve 
        add `priority` int(6) unsigned DEFAULT '1000' after `uid` ,  
        add `suspendFrom` datetime DEFAULT NULL , 
        add `suspendUntil` datetime DEFAULT NULL
_SQL_
    
$dbh->do(<<_SQL_);
   CREATE TABLE `opl_requestReserve` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `numCopyRequest` tinyint(4) DEFAULT '1',
  `idReserve` int(10) unsigned DEFAULT NULL,
  `rid` int(10) unsigned NOT NULL DEFAULT '0',
  `uid` int(10) unsigned NOT NULL DEFAULT '0',
  `email` varchar(60) DEFAULT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `dateRequest` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `dateExpiry` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `dateProcess` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `status` enum('cancel','ready','reject','reserve','hold','waiting') DEFAULT 'waiting',
  `noticeType` varchar(60) DEFAULT 'phone',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
    
_SQL_
$dbh->do(<<_SQL_);
 CREATE TABLE `opl_reqReserveHistory` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `reqId` int(10) unsigned NOT NULL,
  `uid` int(10) unsigned NOT NULL,
  `dateLog` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `action` varchar(100) DEFAULT NULL,
  `log` text,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 
_SQL_


$dbh->do("truncate table opl_requestReserve");
$dbh->do("truncate table opl_reqReserveHistory");
$dbh->do("insert into opl_requestReserve(id,idReserve,rid,uid,numCopyRequest,dateRequest,dateExpiry,dateProcess,status,noticeType) select idReserve,idReserve,rid,uid,numCopyReserve,dateReserve,dateExpiry,dateReserve,'reserve','email' from opl_reserve ");

my $sth=$dbh->prepare("select r.idReserve,r.rid,r.uid,numCopyReserve,dateReserve,r.dateExpiry dateExpiry_r, r.dateCancel dateCancel_r,idloan,barcode, h.idReserve idReserve_h, dateHold, h.dateExpiry dateExpiry_h,dateLoan, h.dateCancel dateCancel_h from opl_reserve r left outer join opl_hold h using(idReserve)  order by r.idReserve,dateHOld");
   $sth->execute();
   my $count=1; 
   my $preId=0;
   while(my $req = $sth->fetchrow_hashref){
       updateReqRsvStatus($dbh, $req->{'idReserve'},"reserve",$req->{'idReserve'},1,$req->{'dateExpiry_r'},$req->{'dateReserve'});

       if($req->{'dateCancel_r'} ne '' ){
           updateReqRsvStatus($dbh, $req->{'idReserve'},"cancel",undef,1,undef,$req->{'dateCancel_r'});

       }
       elsif($req->{'dateHold'} ne ''){
           updateReqRsvStatus($dbh, $req->{'idReserve'},"ready",undef,1,undef,$req->{'dateHold'});
       }
       if($preId != $req->{'idReserve'}){
           $dbh->do("update opl_requestReserve set numCopyRequest=numCopyRequest +? where idReserve= ?",undef,$count,$preId);
           $preId = $req->{'idReserve'};
           $count=0;
       }
       if(defined $req->{'dateHold'} && ($req->{'dateCancel_h'} eq '' || ($req->{'dateCancel_r'} ne '' && $req->{'dateCancel_h'} ne '')) ){
               $count++;
       }
   }
   $dbh->do("update opl_requestReserve set numCopyRequest=numCopyRequest +  ? where idReserve=?",undef,$count,$preId);


#-----------------------------------------------------------------------------------------------
sub updateReqRsvStatus{
    my ($dbh, $reqId,$status,$idreserve,$respUid,$dateExpiry,$dateProcess) = @_;
    my($todayStr)=$dbh->selectrow_array("select now()");

    if($status ne 'reserve' || (defined $idreserve && $idreserve>0 )){
        if($status eq 'reserve'){
            $dbh->do("update opl_requestReserve 
                      set status=?,idReserve=? where id=?",undef,$status,$idreserve,$reqId);
        }
         
        else{
            $dbh->do("update opl_requestReserve 
                      set status=? where id=?",undef,$status,$reqId);
        }

        my $log= to_json({notes=>'',dateProcess=>$dateProcess});
        $dbh->do("insert into opl_reqReserveHistory set reqId=?,uid=?,action=?,log=?"
                 ,undef,$reqId,$respUid,$status,$log);

    }
     
}