#!/usr/bin/perl

#use utf8;
use strict;
use CGI;

use Opals::Context;
use Opals::Template qw(
    tmpl_read
    tmpl_write
);

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 @category = ($cgi->param('cat'));


my ($permission, $cookieList, $template) = tmpl_read(
    {
        dbh             => $dbh,
        cgi             => $cgi,
        tmplFile        => 'util/itemtype.tmpl',
        reqPermission   => 'pref_edit',
    }
);

# See User.pm for the list of permissions
if ($permission && $permission->{'pref_edit'}) {
    ($op) || ($op = 'list');
    my $method_post = ($ENV{'REQUEST_METHOD'} =~ m/post/i) ? 1 : 0;

    if ($op =~ m/^save$/i && $method_post) {
        # it: item type
        my $rv = saveItem($dbh, $cgi, $input);
        $template->param(
            op_save       => 1,
            op_successful => $rv,
            itSaved       => $input->{'item'},
        );
    }
    elsif ($op =~ m/^delete$/i && $method_post) {
        my ($itToDelete, $itDeleted) = deleteItem($dbh, $cgi);
        $template->param(
            op_delete   => 1,
            itToDeleteList  => $itToDelete,
            itDeletedList   => $itDeleted,
        );
    }
    
    if ($op =~ m/^open$/i) {
        my $itemTypeId = $input->{'item'};
        my ($userTypeList, $itemType) = openItem($dbh, $itemTypeId);
        my $itemCategoryList = getItemCategoryList($dbh); #khanh
        foreach my $it (@$itemCategoryList){
            if ($it->{'id'} == $itemType->{'itemCategory'}){
                $it->{'selected'} = 1;
                last;
            }
        }

        if ($itemTypeId =~ m/^__new__$/i) {
           $itemType->{'id'}            = '';
           $itemType->{'description'}   = '';
           $itemType->{'defaultType'}   = '';
        }
        
        my $colSpan = scalar(@{$userTypeList}) + 1;
        $template->param(
            op_open      => 1,
            existingType => ($itemTypeId !~ m/^__new__$/i) ? 1 : 0,
            userTypeList => $userTypeList,
            itemTypeData => $itemType->{'itemTypeData'},
            colSpan      => $colSpan,
            itId         => $itemType->{'id'},
            itDesc       => $itemType->{'description'},
            itDefault    => $itemType->{'defaultType'},
            itCategory   => $itemType->{'itemCategory'}, #khanh
            itemCategoryList    =>  $itemCategoryList,   #khanh
        );
    }
    else { # list/cancel
        my ($itemTypeList, $userTypeList) = listItem($dbh);
        my $colSpan = scalar(@{$userTypeList}) + 4;
        my $itemCategoryList = getItemCategoryList($dbh); #khanh
        $template->param(
            op_list      => 1,
            itemTypeList => $itemTypeList,
            userTypeList => $userTypeList,
            colSpan      => $colSpan,
        );

      
        my $checkAll = 1;
        foreach my $i(@$itemCategoryList){
            foreach my $c(@category){
                if ($i->{'id'} == $c){
                    $i->{'checked'} = "checked";
                    $checkAll = 0;
                    last;
                }
            }
        }
        $template->param(
            itemCategoryList    => $itemCategoryList,
            checkAll          => $checkAll,
        );
    }
}
#else { # input for login form
#    $template->param(
#        input => [
#            {name => '', value => $,},
#            {name => '', value => $,},
#        ],
#    );
#}

# Turn on/off search box
#$template->param(
#    search_off      => 1,
#);
$template->param(hlpUrl     => Opals::Constant->getHlpUrl('itemtype') );

tmpl_write($dbh, $cgi, $cookieList, $template);
#$dbh->disconnect();


################################################################################
sub getUserTypeList {
    my ($dbh) = @_;

    my @userType;
    my $sth = $dbh->prepare(<<_SQL_);
select  catid, catname
from    opl_category
order by catname
_SQL_

    $sth->execute();
    while (my $u = $sth->fetchrow_hashref) {
        push @userType, $u;
    }
    $sth->finish;

    return @userType;
}


################################################################################
sub openItem {
    my ($dbh, $itemTypeId) = @_;

    # Get list of user types
    my @userType = getUserTypeList($dbh);

    if ($itemTypeId =~ m/^__new__$/i) {
        ($itemTypeId) = $dbh->selectrow_array('select id from opl_itemType order by defaultType desc limit 1');
    }

    my $sql = <<_SQL_;
select  *
from    opl_itemType
where   id = ?
_SQL_
    my $itemType = $dbh->selectrow_hashref($sql, undef, $itemTypeId);

    # Exit if item type not found.
    if (!$itemType) {
        return (undef, undef);
    }

    $sql = <<_SQL_;
select  itemTypeId,
        userTypeId,
        loanPeriod    div 24 as    loanPeriod_day,
        renewalPeriod div 24 as renewalPeriod_day,
        reservePeriod div 24 as reservePeriod_day,
        holdPeriod    div 24 as    holdPeriod_day,
        if(gracePeriodUnit='hour',gracePeriod   div 24,0) as   gracePeriod_day,
        loanPeriod    mod 24 as    loanPeriod_hour,
        renewalPeriod mod 24 as renewalPeriod_hour,
        reservePeriod mod 24 as reservePeriod_hour,
        holdPeriod    mod 24 as    holdPeriod_hour,
        if(gracePeriodUnit='hour',gracePeriod   mod 24,0) as   gracePeriod_hour,
        if(gracePeriodUnit='minute',gracePeriod,0) as gracePeriod_minute,
        gracePeriodUnit,
        maxRenewal
from    opl_itemTypeParam
where   itemTypeId = ? &&
        userTypeId = ?
_SQL_

    my @userTypeData;
    foreach my $u (@userType) {
        my $iu = $dbh->selectrow_hashref(
            $sql, undef, $itemTypeId, $u->{'catid'}
        );
        if(!$iu){
            $iu={itemTypeId=>$itemTypeId,
                userTypeId=>$u->{'catid'},
                loanPeriod_day   =>0,
                renewalPeriod_day=>0,
                reservePeriod_day=>0,
                holdPeriod_day   =>0,
                gracePeriod_day  =>0,
                loanPeriod_hour   =>0,
                renewalPeriod_hour=>0,
                reservePeriod_hour=>0,
                holdPeriod_hour   =>0,
                gracePeriod_hour  =>0,
                gracePeriod_minute  =>0
                };

        }
        push @userTypeData, $iu;
    }

    my @itemTypeData;
    my $dataName;
    foreach my $i (qw(loanPeriod renewalPeriod reservePeriod holdPeriod gracePeriod maxRenewal)) {
        my @data;
        $dataName = $i . 'Data';
        foreach my $u (@userTypeData) {
            if ($i =~ m/Period$/i) {
                push @data, {
                    dataName        => $i,
                    uTypeId         => $u->{'userTypeId'},
                    iTypeVal_time   => 1,
                    iTypeVal_day    => $u->{$i . '_day'},
                    iTypeVal_hour   => $u->{$i . '_hour'},
                    iTypeVal_minute => $u->{$i . '_minute'},
                };
            }
            else {
                push @data, {
                    dataName    => $i,
                    uTypeId     => $u->{'userTypeId'},
                    iTypeVal    => $u->{$i},
                };
            }
        }

        push @itemTypeData, {
            $dataName   => 1,
            dataName    => $i,
            data        => \@data,
        }
    }

    # Add to item type info
    $itemType->{'itemTypeData'} = \@itemTypeData;

    return (\@userType, $itemType);
}


################################################################################
sub saveItem {
    my ($dbh, $cgi, $in) = @_;

    if ($in->{'defaultType'}) {
        $dbh->do('update opl_itemType set defaultType = 0');
    }

    my @val = ($in->{'item'}, $in->{'description'}, $in->{'defaultType'}, $in->{'itemCategory'});
    my $sql = <<_SQL_;
replace into opl_itemType
set     id          = ?,
        description = ?,
        defaultType = ?,
        itemCategory = ?
_SQL_

#open CCC, '>/tmp/ccc';
#print CCC "$sql\n$in->{'item'}, $in->{'description'}, $in->{'defaultType'},$in->{'itemCategory'}\n";
    my $rv = $dbh->do($sql, undef, @val);

    if (!$rv) {
        #close CCC;
        return;
    }

    $sql = <<_SQL_;
replace into opl_itemTypeParam
set     itemTypeId    = ?,
        userTypeId    = ?,
        loanPeriod    = ?,
        renewalPeriod = ?,
        reservePeriod = ?,
        holdPeriod    = ?,
        gracePeriod   = ?,
        gracePeriodUnit=?,
        maxRenewal    = ?
_SQL_

    # Get list of user types
    my @userType = getUserTypeList($dbh);
    my ($dataName, $dataValue,$unit);
    my $gracePeriodUnit='hour';

    foreach my $u (@userType) {
        my $userData;
        $gracePeriodUnit='hour';
        foreach my $d ($cgi->param('userData_' . $u->{'catid'})) {
            ($dataName, $dataValue,$unit) = split(/::/, $d);
            $userData->{$dataName} = $dataValue;
            $gracePeriodUnit='minute' if($unit eq 'm' && $dataName eq 'gracePeriod');

        }
        @val = (
            $in->{'item'},
            $u->{'catid'},
            $userData->{'loanPeriod'},
            $userData->{'renewalPeriod'},
            $userData->{'reservePeriod'},
            $userData->{'holdPeriod'},
            $userData->{'gracePeriod'},
            $gracePeriodUnit,
            $userData->{'maxRenewal'}
        );
        $rv = $dbh->do($sql, undef, @val);
        if (!$rv) {
            return;
        }
#print CCC <<_STR_;
#            $in->{'item'},
#            $u->{'catid'}
#            loanPeriod::$userData->{'loanPeriod'},
#            renewalPeriod::$userData->{'renewalPeriod'},
#            reservePeriod::$userData->{'reservePeriod'},
#            holdPeriod::$userData->{'holdPeriod'},
#            gracePeriod::$userData->{'gracePeriod'},
#            maxRenewal::$userData->{'maxRenewal'},
#            fine::$userData->{'fine'}
#
#_STR_
    }
#close CCC;

    return $rv;
}


################################################################################
sub deleteItem {
    my ($dbh, $cgi) = @_;

    my $sql_countLib = <<_SQL_;
select  count(*)
from    opl_item
where   typeId = ? && barcode not regexp '^\_\_\_'
_SQL_
my $sql_countTb = <<_SQL_;
select  count(*)
from    tb_items
where   typeId = ? && barcode not regexp '^\_\_\_'
_SQL_
my $sql_countEq = <<_SQL_;
select  count(*)
from    eq_items
where   typeId = ? && barcode not regexp '^\_\_\_'
_SQL_

    my $sth_itemType = $dbh->prepare(<<_SQL_);
delete from opl_itemType
where   id = ?
_SQL_
    my $sth_itemTypeParam = $dbh->prepare(<<_SQL_);
delete from opl_itemTypeParam
where   itemTypeId = ?
_SQL_

    my ($countLib,$countTb,$countEq) =(0,0,0);
    my @itemTypeToDelete;
    my @itemTypeDeleted;
    foreach my $i ($cgi->param('typeId')) {
        ($countLib) = $dbh->selectrow_array($sql_countLib, undef, $i);
        ($countTb) = $dbh->selectrow_array($sql_countTb, undef, $i);
        ($countEq) = $dbh->selectrow_array($sql_countEq, undef, $i);
        if ($countLib || $countTb || $countEq) {
            push @itemTypeToDelete, {itToDelete => $i};
        }
        else {
            push @itemTypeDeleted, {itDeleted => $i};
            $sth_itemTypeParam->execute($i);
            $sth_itemType->execute($i);
        }
    }

    return (\@itemTypeToDelete, \@itemTypeDeleted);
}


################################################################################
sub listItem {

    my ($dbh) = @_;
    
    # Get list of item types
    my @itemType;
=item
    my $sth = $dbh->prepare(<<_SQL_);
select  *
from    opl_itemType 
order by defaultType desc, id
_SQL_
 $sth->execute();
=cut

    my $sql = ("select * from opl_itemType ");
    my $sqlWhere;
    my @val;
    foreach my $c(@category){
        $sqlWhere .= " or " if ($sqlWhere ne "");
        $sqlWhere .= " itemCategory = ? " ;
        push @val , $c;
    }
    
    $sql .= " where " . $sqlWhere if $sqlWhere ne "";
    $sql .= " order by defaultType desc, itemCategory, id";
    my $sth = $dbh->prepare($sql);
    
    my $sth_count_1 = $dbh->prepare(<<_SQL_);
select  count(*)
from    opl_item
where   typeId = ? && barcode not regExp '^\_\_\_'
_SQL_

 my $sth_count_2 = $dbh->prepare(<<_SQL_);
select  count(*)
from    tb_items
where   typeId = ? && barcode not regExp '^\_\_\_'
_SQL_

 my $sth_count_3 = $dbh->prepare(<<_SQL_);
select  count(*)
from    eq_items
where   typeId = ? && barcode not regExp '^\_\_\_'
_SQL_

    $sth->execute(@val);
    while (my $i = $sth->fetchrow_hashref) {
        if ($i->{'itemCategory'} == 3){
            $sth_count_3->execute($i->{'id'});
            ($i->{'itemCount'}) = $sth_count_3->fetchrow_array;
        }
        elsif ($i->{'itemCategory'} == 2){
            $sth_count_2->execute($i->{'id'});
            ($i->{'itemCount'}) = $sth_count_2->fetchrow_array;
            }
        else{
            $sth_count_1->execute($i->{'id'});
            ($i->{'itemCount'}) = $sth_count_1->fetchrow_array;
        }
        push @itemType, $i;
    }
    $sth_count_1->finish;
    $sth_count_2->finish;
    $sth_count_3->finish;
    $sth->finish;
    
    # Get list of user types
    my @userType = getUserTypeList($dbh);

    # Get the matrix of item-user types
    my @itemTypeParam;
    my $sql = <<_SQL_;
select  itemTypeId,
        userTypeId,
        loanPeriod    div 24 as    loanPeriod_day,
        renewalPeriod div 24 as renewalPeriod_day,
        reservePeriod div 24 as reservePeriod_day,
        holdPeriod    div 24 as    holdPeriod_day,
        if(gracePeriodUnit='hour',gracePeriod   div 24,0) as   gracePeriod_day,
        loanPeriod    mod 24 as    loanPeriod_hour,
        renewalPeriod mod 24 as renewalPeriod_hour,
        reservePeriod mod 24 as reservePeriod_hour,
        holdPeriod    mod 24 as    holdPeriod_hour,
        if(gracePeriodUnit='hour',gracePeriod   mod 24,0) as   gracePeriod_hour,
        if(gracePeriodUnit='minute',gracePeriod,0) as   gracePeriod_minute,    
        maxRenewal
from    opl_itemTypeParam
where   itemTypeId = ? &&
        userTypeId = ?
_SQL_

    foreach my $i (@itemType) {
        my @userTypeData;
        foreach my $u (@userType) {
            my $iu = $dbh->selectrow_hashref(
                $sql, undef, $i->{'id'}, $u->{'catid'}
            );
            push @userTypeData, $iu;
        }
        $i->{'userTypeData'} = \@userTypeData;
    }
    return (\@itemType, \@userType);
}

#########################################
# khanh: 14-jan-2009 
# Function: getItemCategoryList
# Return:   A list of item Category (ie: library, textbook, equipment...etc...)
# Purpose:  Allow user to filter selected category
##########################################
sub getItemCategoryList {
    my ($dbh) = @_;
    my @itemCatList = ();
    my $sth = $dbh->prepare("select *  from opl_itemCategory order by id");
    $sth->execute();
    while ( my $ic = $sth->fetchrow_hashref){
        push @itemCatList, {
            id          => $ic->{'id'},
            description => $ic->{'description'},
        };
   }
   $sth->finish;
   return \@itemCatList;
}