#!/usr/bin/perl

#use utf8;
use strict;
use CGI;

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

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

my $cgi = CGI->new;
my $input = $cgi->Vars();
my @category = split(/,/ , $input->{'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'}) {
       
        #my ($itemTypeList,$itemTypeList_1) = listItem($dbh);
        my ($itemTypeList) = listItem($dbh);
        my $itemTypeListJSON  = to_json($itemTypeList, { pretty => 1})  ;
        my $selectedCategory  = to_json(\@category, { pretty => 1})  ;
        #my $itemTypeList_1JSON = to_json($itemTypeList_1, { pretty => 1})  ;
        $template->param(
            itemTypeList => $itemTypeListJSON,
            selectedCategory=>$selectedCategory
        );
       
        my $itemCategoryList = getItemCategoryList($dbh); #khanh
        my $itemCategoryListJSON  = to_json($itemCategoryList, {pretty => 1})  ;
        $template->param(
            itemCategoryList  => $itemCategoryListJSON,
        );
}


$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 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_

    my $rv = $dbh->do($sql, undef, @val);

    if (!$rv) {
        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;
        }
    }

    return $rv;
}


################################################################################
sub deleteItem_bk{
    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;

    my $sql=("select t.*,c.description as category from opl_itemType t inner join opl_itemCategory c on t.itemCategory=c.id ");
    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 itemCategory,defaultType desc ,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) {
        $i->{'id'} =~ s/^\s+|\s+$//g;
        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, catname,
        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,maxItemLoan
from    opl_itemTypeParam p left outer join opl_category c on p.userTypeId=c.catid
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'}
            );

            if(!defined $iu || (!defined $iu->{'loanPeriod_day'} && !defined $iu->{'loanPeriod_hour'})){
                 $iu->{'userTypeId'}=$u->{'catid'};
                 $iu->{'catname'}=$u->{'catname'};

            }
            push @userTypeData, $iu 
        }
        $i->{'userTypeData'} = \@userTypeData;
    }

    my $list;
    my $index=0;
    foreach my $i(@itemType){
        if (!$list->{$i->{'itemCategory'}}){
            $list->{$i->{'itemCategory'}}->{'id'} = $i->{'itemCategory'};
            $list->{$i->{'itemCategory'}}->{'category'} = $i->{'category'};
        }
        $i->{'index'} = $index++;
        push @{$list->{$i->{'itemCategory'}}->{'list'}}, $i;
    }
    my @itList;
    foreach my $k (sort keys %$list){
        
        push @itList, $list->{$k}
    }
    return (\@itList);
}

#########################################
# 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;
}