#!/usr/bin/perl

#use utf8;
use strict;
use CGI;

use Opals::Context;
use Opals::Constant;
use Opals::Date qw(
    date_text
);
use Opals::Template qw(
    tmpl_read
    tmpl_write
);
use Opals::Search qw(
    srch_searchRecord
);
use Opals::MarcXml qw(
    mxml_updateDbZebra
    mxml_updateItemStatus
);
use Opals::Locale qw(
    loc_getMsgFile
    loc_write
);

my $dbh = Opals::Context->dbh();
END { $dbh->disconnect(); }

my $cgi = CGI->new;
my $input = $cgi->Vars();

my ($permission, $cookie, $template) = tmpl_read(
        {
            dbh             => $dbh,
            cgi             => $cgi,
            tmplFile        => 'record/itemstatus.tmpl',
            reqPermission   => 'marc_edit|circ_loan|circ_return|circ_rsrv',
        }
);

my $bc = $input->{'bc'};
#Thu, Jan 14, 2010 @ 16:01:09 EST
my $borrower;
if (!$bc) {
    $template->param(error => 1);
}
else {
    my $sth = $dbh->prepare(<<_STH_);
select  r.rid, r.title, r.author, r.pubName, r.pubDate
from    opl_item as i, opl_marcRecord as r
where   i.barcode = ? &&
        i.rid = r.rid
_STH_

    $sth->execute($bc) || return;
    my $rec = $sth->fetchrow_hashref;
    $sth->finish;

    if ($rec) {
        $template->param($rec);
    }

    if ($input->{'op'}) {
        SaveStatus($template, $dbh, $input);
    }
    GetBorrower($template, $dbh, $bc);
}
   my $msgValMap={
                    msg_04=>{lastname=>$borrower->{'loan_lastname'} , 
                             firstname=>$borrower->{'loan_firstname'},
                             loanDate=>$borrower->{'loan_dateLoan'} },
                    msg_05=>{lastname=>$borrower->{'hold_lastname'}, 
                             firstname=>$borrower->{'hold_firstname'},
                             holdDateStart=>$borrower->{'hold_dateHold'},
                             holdDateEnd=>$borrower->{'hold_dateExpiry'}
                             
                              },
                    msg_07=>{lastname=>$borrower->{'rsvr_lastname'},
                             firstname=>$borrower->{'rsvr_firstname'},
                             reserveDate=>$borrower->{'rsvr_dateReserve'},
                             reserveExpire=>$borrower->{'rsvr_dateExpiry'}},

                    msg_10=>{lastname=>$borrower->{'LastName'},
                             firstname=>$borrower->{'FirstName'}},
                             
                    msg_12=>{lastname=>$borrower->{'LastName'},
                             firstname=>$borrower->{'FirstName'}}

                    };
    my $itemStatusMsgMap =loc_getMsgFile('circ/itemStatus.msg',$msgValMap);
    loc_write($template,$itemStatusMsgMap);


$template->param(bc => $bc);
tmpl_write($dbh, $cgi, $cookie, $template);

#----------------------------------------------------------
sub SaveStatus
{
    my ($template, $dbh, $input) = @_;

    my $sql = "select idloan, dateLoan, dateDue, dateReturn, u.uid as uid, 
                firstname, lastname, userbarcode, username
        from opl_loan as l, opl_user as u where l.uid=u.uid 
        and barcode=? order by dateLoan desc";

    my $query = $dbh->prepare($sql);
    $query->execute($input->{'bc'});

    my $rec = $query->fetchrow_hashref;
    $query->finish;

    my $newstatus = 0;
    $newstatus = ITEM_INACTIVE if ( $input->{'itemstatus'} eq 'inactive' );
    $newstatus = ITEM_ACTIVE if ( $input->{'itemstatus'} eq 'active' );
    $newstatus = ITEM_DAMAGED if ( $input->{'itemstatus'} eq 'damaged' );
    $newstatus = ITEM_LOST if ( $input->{'itemstatus'} eq 'lost' );

    # Cannot make an item inactive when it is being on loan
    if ( $rec && !$rec->{'dateReturn'} && $newstatus == ITEM_ACTIVE )
    {
        $template->param(InActiveLoanItem => 1);
        return;
    }

    my ($status, $circ, $firstname, $lastname) = GetStatusDate($dbh, $bc);

    # Status of item lost by a borrower can't be changed
    if ( $status == ITEM_LOST && $circ )
    {
        if ( $newstatus != ITEM_ACTIVE  ) 
        {
            $template->param(ChangeLostItem => 1);
            return;
        }
    }
        
    #----------- Store into database the new item type, the new status for this item
    $sql = "update opl_item set typeId=?, available=? where barcode=?";
    $query = $dbh->prepare($sql);
    my $avail = ( $newstatus == ITEM_ACTIVE ) ? 1 : 0;
    my $typeId = $input->{'typeId'};
    $query->execute($typeId, $avail, $input->{'bc'});

    $sql = "delete from opl_itemInfo where  barcode=? && sf852Code='3'";
    $query = $dbh->prepare($sql);
    $query->execute($input->{'bc'});

 
    $sql = "insert into opl_itemInfo set sf852Data=? , barcode=?, sf852Code='3'";
    $query = $dbh->prepare($sql);
    $query->execute($typeId, $input->{'bc'});
    $query->finish;


    #----------- Store into zebra the new item type for this item
    $query = $dbh->prepare("select rid from opl_item where barcode=?");
    $query->execute($input->{'bc'});
    my ($rid) = $query->fetchrow_array;
    $query->finish;
    
    my $pqf = "\@attr 1=12 $rid";
    my ($resultSize, $xml) = srch_searchRecord($dbh, 0, 'f', $pqf, $ENV{'Z_INDEX_BASE'}, 1);
   
    my $f852 = ''; 
    while ($xml->[0] =~ s/([\s]*<datafield tag="852" ind1="[\d ]" ind2="[\d ]">\n([\s]*<subfield code="[\w\d]">.*<\/subfield>\n)*[\s]*<\/datafield>\n)//)
    {
        my ($fsub, $ftmp) = ($1, $1);
        if ($fsub =~ s/<subfield code="p">(.*)<\/subfield>//)
        {
            my $bc = $1;
            $bc =~ s/ *[:\/] *$//;
            if ( $bc eq $input->{'bc'} )
            {
                $fsub = $ftmp;
                if ( $fsub =~ m/<subfield code="3">(.*)<\/subfield>/ )
                {
                    $fsub =~ s/<subfield code="3">(.*)<\/subfield>/_asdf_/;
                }
                else
                {
                    $fsub =~ s/<\/datafield>/    _asdf_\n<\/datafield>/;
                }
                $fsub =~ s/_asdf_/<subfield code="3">$typeId<\/subfield>/;
            }
            else { $fsub = $ftmp; }
        }
        $f852 .= $fsub;
    }
    $f852 .= '</record>';
    $xml->[0] =~ s/<\/record>/$f852/;
    mxml_updateDbZebra($dbh, $rid, $xml->[0]);

    #---------- Store new status of this item
    if ( $newstatus == ITEM_DAMAGED && $status == ITEM_DAMAGED && $circ )
    {
        $template->param(ChangeDamagedItem => 1);
    }
    else
    {   
        if ( $status != $newstatus )
        {
            #$query = $dbh->prepare("insert into opl_itemstatus set barcode=?, ondate=now(), status=?");
            #$query->execute($input->{'bc'}, $newstatus);
            #$query->finish;
            mxml_updateItemStatus($dbh,$input->{'bc'},$newstatus,'');

            if ( ($newstatus == ITEM_ACTIVE || $newstatus == ITEM_DAMAGED) 
                && ($status == ITEM_LOST || $status == ITEM_INACTIVE ) )
            {
                $query = $dbh->prepare("insert into opl_found set barcode=?, ondate=now()");
                $query->execute($input->{'bc'});
                $query->finish;
            }
        }
    }
}

#----------------------------------------------------------
sub GetBorrower
{
    my ($template, $dbh, $bc) = @_;

    my $sql = "select * from opl_item where barcode='$bc'";
    my $query = $dbh->prepare($sql);
    $query->execute();
    my $recItem = $query->fetchrow_hashref;
    $query->finish;

# Get loan info 
    my @borrowers = (); 
    $sql = "select idloan, dateLoan, dateDue, dateReturn, u.uid as uid, 
                firstname, lastname, userbarcode, username
        from opl_loan as l, opl_user as u where l.uid=u.uid 
        and barcode='$bc' order by dateLoan desc";

    $query = $dbh->prepare($sql);
    $query->execute();
    
    my $rec;
    for (my $i=0; $i<3; $i++)
    {
        my $rec = $query->fetchrow_hashref;
        last if ( !$rec );

        $rec->{'dateLoan'} = date_text($rec->{'dateLoan'}, 0);
        $rec->{'dateDue'} = date_text($rec->{'dateDue'}, 0);
        if ( $rec->{'dateReturn'} )
        { 
            $rec->{'dateReturn'} = date_text($rec->{'dateReturn'}, 0);
        }

        push @borrowers, $rec; 
    }
    $query->finish;
    
    $template->param(brwrlist => \@borrowers);
    if ( scalar(@borrowers) > 0 && !$borrowers[0]->{'dateReturn'})
    {
        $template->param(
            loan_userbarcode => $borrowers[0]->{'userbarcode'},
            loan_username    => $borrowers[0]->{'username'},
            loan_firstname   => $borrowers[0]->{'firstname'},
            loan_lastname    => $borrowers[0]->{'lastname'},
            loan_uid    => $borrowers[0]->{'uid'},
            loan_dateLoan    => $borrowers[0]->{'dateLoan'},
            loan_dateDue     => $borrowers[0]->{'dateDue'},
            loan_dateReturn  => $borrowers[0]->{'dateReturn'},
            );
        $template->param(loaning => 1);

        $borrower->{'loan_userbarcode'} =$borrowers[0]->{'userbarcode'};
        $borrower->{'loan_username'} =$borrowers[0]->{'username'};
        $borrower->{'loan_firstname'} =$borrowers[0]->{'firstname'};
        $borrower->{'loan_lastname'} =$borrowers[0]->{'lastname'};
        $borrower->{'loan_uid'} =$borrowers[0]->{'uid'};
        $borrower->{'loan_dateLoan'} =$borrowers[0]->{'dateLoan'};
        $borrower->{'loan_dateDue'} =$borrowers[0]->{'dateDue'};
        $borrower->{'loan_dateReturn'} =$borrowers[0]->{'dateReturn'};




    }

# Get hold info
#    $sql = "select dateHold, h.dateExpiry as dateExpiry, u.uid as uid, 
#                firstname, lastname, userbarcode, username
#        from opl_hold as h, opl_reserve as r, opl_user as u
#        where h.idReserve=r.idReserve and r.uid=u.uid and h.barcode='$bc'
#            and h.dateCancel is null and h.dateLoan is null
#            and to_days(h.dateExpiry) >= to_days(now())";

$sql ="select h.* , u.uid as uid, 
                firstname, lastname, userbarcode, username 
from (opl_reserve as r inner join opl_item as i on r.rid=i.rid ) 
inner join opl_hold as h on h.idReserve=r.idReserve  inner join opl_user as u ON r.uid=u.uid
where i.barcode = ?
AND h.dateExpiry> now() 
AND h.dateCancel is null 
AND h.dateLoan is null
order by dateReserve";

    $query = $dbh->prepare($sql);
    $query->execute($bc);
    
    $rec = $query->fetchrow_hashref;
    $query->finish;

    if ( $rec )
    {
        $rec->{'dateHold'} = date_text($rec->{'dateHold'}, 0);
        $rec->{'dateExpiry'} = date_text($rec->{'dateExpiry'}, 0);
        $template->param(
            hold_userbarcode => $rec->{'userbarcode'},
            hold_username    => $rec->{'username'},
            hold_firstname   => $rec->{'firstname'},
            hold_lastname    => $rec->{'lastname'},
            hold_uid         => $rec->{'uid'},
            hold_dateHold    => $rec->{'dateHold'},
            hold_dateExpiry  => $rec->{'dateExpiry'},
            );
        $template->param(holding => 1);

        $borrower->{'hold_userbarcode'} =$rec->{'userbarcode'};
        $borrower->{'hold_username'}    =$rec->{'username'};
        $borrower->{'hold_firstname'}   =$rec->{'firstname'};
        $borrower->{'hold_lastname'}    =$rec->{'lastname'};
        $borrower->{'hold_uid'}         =$rec->{'uid'};
        $borrower->{'hold_dateHold'}    =$rec->{'dateHold'};
        $borrower->{'hold_dateExpiry'}  =$rec->{'dateExpiry'};
    }

# Get reserve info
    $sql = "select  r.*,u.* from opl_reserve as r inner join opl_user as u on r.uid=u.uid
            where r.rid=?  && r.dateCancel is null && r.dateExpiry > now() && numcopyreserve>0 
            order by dateReserve limit 1;";

    $query = $dbh->prepare($sql);
    $query->execute($recItem->{'rid'});
    
    $rec = $query->fetchrow_hashref;
    $query->finish;

    if ( $rec )
    {
        $rec->{'dateReserve'} = date_text($rec->{'dateReserve'}, 0);
        $rec->{'dateExpiry'} = date_text($rec->{'dateExpiry'}, 0);
        $template->param(
            rsvr_userbarcode => $rec->{'userbarcode'},
            rsvr_username    => $rec->{'username'},
            rsvr_firstname   => $rec->{'firstname'},
            rsvr_lastname    => $rec->{'lastname'},
            rsvr_uid    => $rec->{'uid'},
            rsvr_dateReserve => $rec->{'dateReserve'},
            rsvr_dateExpiry  => $rec->{'dateExpiry'},
            );
        $template->param(reserving => 1);
        
        $borrower->{'rsvr_userbarcode'} =$rec->{'userbarcode'};
        $borrower->{'rsvr_username'} =$rec->{'username'};
        $borrower->{'rsvr_firstname'} =$rec->{'firstname'};
        $borrower->{'rsvr_lastname'} =$rec->{'lastname'};
        $borrower->{'rsvr_uid'} =$rec->{'uid'};
        $borrower->{'rsvr_dateReserve'} =$rec->{'dateReserve'};
        $borrower->{'rsvr_dateExpiry'} =$rec->{'dateExpiry'};
    }

# Get list of item types
    $query = $dbh->prepare("select * from opl_itemType");
    $query->execute();

    my @itemTypes = ();
    while ($rec = $query->fetchrow_hashref) {
        if ($rec->{'id'} eq $recItem->{'typeId'}) {
            $rec->{'sel'} = 1;
        }
        push @itemTypes, $rec;
    }
    $query->finish;
    $template->param(itemTypes => \@itemTypes);

# Get the total times of loans/reserves
    $sql = "select count(*) as total from opl_loan where barcode='$bc'";
    $query = $dbh->prepare($sql);
    $query->execute();
    $rec = $query->fetchrow_hashref;
    $template->param(totalLoan => $rec->{'total'});
    $query->finish;
    
    $sql = "select count(*) as total from opl_reserve where rid=?";
    $query = $dbh->prepare($sql);
    $query->execute($recItem->{'rid'});
    $rec = $query->fetchrow_hashref;
    $template->param(totalReserve => $rec->{'total'});
    $query->finish;

# Set status of item
    if ( $recItem->{'available'} == 1 ) 
    { 
        $template->param(itemactive => 1); 
    }
    else
    {
        my ($status, $circ, $firstname, $lastname) = GetStatusDate($dbh, $bc);

        if ( $status == ITEM_LOST ) { $template->param(itemlost => 1); }
        elsif ( $status == ITEM_DAMAGED ) { $template->param(itemdamage => 1); }
        elsif ( $status == ITEM_INACTIVE ) { $template->param(iteminactive => 1); }
        
        if ( !$circ ) { $template->param(nocirc => 1); }
        $template->param(FirstName => $firstname);
        $template->param(LastName => $lastname);
        $borrower->{'FirstName'}=$firstname;
        $borrower->{'LastName'}=$lastname;
    }
}

#----------------------------------------------------------
sub GetStatusDate
{
    my ($dbh, $bc) = @_;

    my ($dateLost, $dateDamage, $uid);
    
    my $sql = "select ondate, uid from opl_loan l inner join opl_odl as d on l.idloan=d.idloan 
                    where l.barcode='$bc' and dateReturn is not null && d.type='lost' order by dateLoan desc limit 1";
    my $query = $dbh->prepare($sql);
    $query->execute();
    ($dateLost, $uid) = $query->fetchrow_array;
    $query->finish;

    $sql = "select ondate from opl_loan l inner join opl_odl as d on l.idloan=d.idloan 
                    where l.barcode='$bc' and dateReturn is not null && d.type='damaged' order by dateLoan desc limit 1";
    $query = $dbh->prepare($sql);
    $query->execute();
    ($dateDamage) = $query->fetchrow_array;
    $query->finish;

    $sql = "select ondate, status from opl_itemstatus where barcode='$bc' && status <>" . ITEM_NEW . " order by ondate desc limit 1";
    $query = $dbh->prepare($sql);
    $query->execute();
    my ($dateStatus, $status) = $query->fetchrow_array;
    $query->finish;


    if ( !$dateLost && !$dateDamage && !$dateStatus ) { $status = ITEM_ACTIVE; return ($status, 0, undef, undef); }
    my $circ = 1;
    if ( ($dateLost ge $dateDamage) && ($dateLost ge $dateStatus) ) { $status = ITEM_LOST; }
    elsif ( ($dateDamage ge $dateLost) && ($dateDamage ge $dateStatus) ) { $status = ITEM_DAMAGED; }
    elsif ( ($dateStatus ge $dateLost) && ($dateStatus ge $dateDamage) )
    {
        if ( $status == ITEM_DAMAGED || $status == ITEM_LOST ) 
        { 
            $circ = 0;
        }
    }

    
    my ($firstname, $lastname);
    if ( ($status == ITEM_LOST || $status == ITEM_DAMAGED) && $circ )
    {
        $query = $dbh->prepare("select firstname, lastname from opl_user where uid=$uid");
        $query->execute();
        ($firstname, $lastname) = $query->fetchrow_array;
        $query->finish;
    }
    return ($status, $circ, $firstname, $lastname);
}