#!/usr/bin/perl

#use utf8;
use strict;
use CGI;

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

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

my $cgi = CGI->new;
my $input = $cgi->Vars();
my $op = $input->{'op'};
my ($permission, $cookieList, $template) = tmpl_read(
        {
            dbh             => $dbh,
            cgi             => $cgi,
            tmplFile        => 'util/libgroup.tmpl',
            reqPermission   => 'pref_edit',
#            op              => $op,
        }
);

# See User.pm for the list of permissions
if ($permission && $permission->{'pref_edit'}) {
    my $opOK = 1;
    
    if ($ENV{'REQUEST_METHOD'} =~ m/post/i) {
        if ($op && $op =~ m/^(saveSystem|delSystem|saveLibrary|delLibrary)$/i) {
            if ($op =~ m/^delSystem$/i) {
                $opOK = delSystem($dbh, $input);
            }
            elsif ($op =~ m/^delLibrary$/i) {
                $opOK = delLibrary($dbh, $input);
            }
            elsif ($op =~ m/^saveSystem$/i) {
                $opOK = saveSystem($dbh, $input);
            }
            else {
                $opOK = saveLibrary($dbh, $input);
            }

#            $template->param($input);
            $template->param(
                sysAdd          => ($input->{'sid'} == 2) ? 1 : 0,
                sysMod          => ($input->{'sid'} >  2) ? 1 : 0,
                libAdd          => ($input->{'lid'} == 2) ? 1 : 0,
                libMod          => ($input->{'lid'} >  2) ? 1 : 0,
                opSaveSystem    => ($op =~ m/^saveSystem$/i) ? 1 : 0,
                opSaveLibrary   => ($op =~ m/^saveLibrary$/i)? 1 : 0,
                opDelSystem     => ($op =~ m/^delSystem$/i)  ? 1 : 0,
                opDelLibrary    => ($op =~ m/^delLibrary$/i) ? 1 : 0,
                i_sCode         => $input->{'sCode'},
                i_sName         => $input->{'sName'},
                i_lCode         => $input->{'lCode'},
                i_lName         => $input->{'lName'},
            );
        }
    }

    my $systemList = srch_systemList($dbh);
    $template->param(
        systemList  => $systemList,
        opOK        => $opOK,
    );
}
#else { # input for login form
#    $template->param(
#        input => [
#            {name => '', value => $,},
#            {name => '', value => $,},
#            {name => '', value => $,},
#            {name => '', value => $,},
#        ],
#    );
#}

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

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

sub saveSystem {
    my ($dbh, $input) = @_;
    my ($sth, $rv);

    my @val = (
        $input->{'sCode'}, 
        $input->{'sName'}, 
        $input->{'sAddrLine1'}, 
        $input->{'sAddrLine2'}, 
        $input->{'sCity'}, 
        $input->{'sState'}, 
        $input->{'sZip'}, 
        $input->{'sPhone'}, 
        $input->{'sFax'}, 
        $input->{'sContact'}, 
        $input->{'sEmail'}
    );
    my $sid = $input->{'sid'};

    my $sql = <<_STH_;
COMMAND opl_libSystem
set sCode       = ?,
    sName       = ?,
    sAddrLine1  = ?,
    sAddrLine2  = ?,
    sCity       = ?,
    sState      = ?,
    sZip        = ?,
    sPhone      = ?,
    sFax        = ?,
    sContact    = ?,
    sEmail      = ?
_STH_

    if ($sid && $sid =~ m/^[\d]+$/ && $sid > 0) {
        if ($sid == 2) {#new system
            $sql =~ s/COMMAND/insert into/;
            $sth = $dbh->prepare($sql);
            $rv = $sth->execute(@val);
            $sth->finish;
        }
        elsif ($sid > 2) {#modify data
            # update system code in library setting
            my $sql_lib = <<_SQL_;
update  opl_library
set     sysCode = ?
where   sysCode in (
            select  sCode
            from    opl_libSystem
            where   sid = ?
        )
_SQL_
            my @val_lib = ($input->{'sCode'}, $sid);
            $dbh->do($sql_lib, undef, @val_lib);

            # update system
            $sql =~ s/COMMAND/update/;
            $sql .= 'where sid = ?';
            $sth = $dbh->prepare($sql);
            push @val, $sid;
            $rv = $sth->execute(@val);
            $sth->finish;
        }
    }

    return $rv;
}

sub saveLibrary {
    my ($dbh, $input) = @_;
    my ($sth, $rv);

    my $lid = $input->{'lid'};
    if ($lid && $lid =~ m/^[\d]+$/ && $lid > 0) {
        if ($lid == 2) {#new library
            $sth = $dbh->prepare(<<_STH_);
insert into opl_library
set sysCode     = ?,
    lCode       = ?,
    lName       = ?,
    lAddrLine1  = ?,
    lAddrLine2  = ?,
    lCity       = ?,
    lState      = ?,
    lZip        = ?,
    lPhone      = ?,
    lFax        = ?,
    lContact    = ?,
    lEmail      = ?
_STH_
            $rv = $sth->execute(
                $input->{'sCode'},
                $input->{'lCode'}, 
                $input->{'lName'}, 
                $input->{'lAddrLine1'}, 
                $input->{'lAddrLine2'}, 
                $input->{'lCity'}, 
                $input->{'lState'}, 
                $input->{'lZip'}, 
                $input->{'lPhone'}, 
                $input->{'lFax'}, 
                $input->{'lContact'}, 
                $input->{'lEmail'}
            );
            $sth->finish;
        }
        elsif ($lid > 2) {#modify data
            $sth = $dbh->prepare(<<_STH_);
update  opl_library
set     sysCode     = ?,
        lCode       = ?,
        lName       = ?,
        lAddrLine1  = ?,
        lAddrLine2  = ?,
        lCity       = ?,
        lState      = ?,
        lZip        = ?,
        lPhone      = ?,
        lFax        = ?,
        lContact    = ?,
        lEmail      = ?
where   lid = ?
_STH_
            $rv = $sth->execute(
                $input->{'sCode'},
                $input->{'lCode'}, 
                $input->{'lName'}, 
                $input->{'lAddrLine1'}, 
                $input->{'lAddrLine2'}, 
                $input->{'lCity'}, 
                $input->{'lState'}, 
                $input->{'lZip'}, 
                $input->{'lPhone'}, 
                $input->{'lFax'}, 
                $input->{'lContact'}, 
                $input->{'lEmail'}, 
                $lid
            );
            $sth->finish;
        }
    }

    return $rv;
}

sub delSystem {
    my ($dbh, $input) = @_;
    my $rv;
    
    my $sid = $input->{'sid'};
    my ($libCount) = $dbh->selectrow_array(<<_STH_);
select  count(*)
from    opl_library
where   sid = $sid
_STH_

    if (!$libCount && $sid && $sid =~ m/^[\d]+$/ && $sid > 2) {
        $rv = $dbh->do(<<_STH_);
delete from opl_libSystem
where sid = $sid
_STH_
    }

    return $rv;
}

sub delLibrary {
    my ($dbh, $input) = @_;
    my $rv;

    my $lid = $input->{'lid'};
    if ($lid && $lid =~ m/^[\d]+$/ && $lid > 2) {
        $rv = $dbh->do(<<_STH_);
delete from opl_library
where lid = $lid
_STH_
    }

    return $rv;
}