#!/usr/bin/perl
#
# TODO: Add/delete function has been removed. Script and template need cleanup.
#
#use utf8;
use strict;
use CGI;

use Opals::Context; 
use Opals::Template qw(
    tmpl_searchFieldOrder
    tmpl_read
    tmpl_write
    tmpl_makeMenu
);
#    $searchField
#    $sfIndex


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        => 'util/syspref.tmpl',
            reqPermission   => 'pref_edit',
        }
);

#if ($permission && $permission->{'user_delete'}) 
#{
#    my $input = $cgi->Vars();
#    if ($input->{'op'} eq 'delete') 
#    {
#        if (user_delete($dbh, $input)) 
#        {
#            $template->param(success => 1);
#        }
#        else 
#        {
#            $template->param(error => 1);
#        }
#    }
#    $template->param(userDel => 1);
#}

my $szTitle = 'System Preference';
my $szTableName = 'opl_preference';
my @arrColumnName = ("var", "val", "valShow","opt", "varType", "description");
my @arrColumnTitle = ("Variable", "Value", "Option", "Description");
my @arrColumnEdit = ("var", "val","valShow", "opt", "varType","description");
my @arrColumnEditTitle = ("Variable", "Value", "valShow","Option", "varType","description");
my @arrColumnType = ();
my $nColSort = 0;
my $nItemStart = 0;
my $nItemInPage = ""; #10;
my $nItemTotal;
my @data = ();

# Define type of columns
=item
    my @LOV = ();
    push @arrColumnType, { Type => $StrType, LOV => \@LOV, Hide => 0 };   # Var
    push @arrColumnType, { Type => $StrType, LOV => \@LOV, Hide => 0 };   # Val
    push @arrColumnType, { Type => $StrType, LOV => \@LOV, Hide => 0 };   # Option
    push @arrColumnType, { Type => $IntType, LOV => \@LOV, Hide => 0 };   # Description
=cut

    if ( $input->{'ItemStart'} )
    {
        $nItemStart = $input->{'ItemStart'};
    }
    else
    {
        $nItemStart = 0;
    }

   
    if  ( $input->{'act'} && $input->{'act'} eq 'new' )
    {

        getPrefListGroup();
        SetAddFrame($szTitle, \@arrColumnTitle, \@arrColumnName, $nItemStart, $nColSort, $template);
        $template->param(AddItem => 1);
                tmpl_write($dbh, $cgi, $cookie, $template);
        goto __END_OF_FILE;
    }
    if (  $input->{'act'} && $input->{'act'} eq 'edit' )
    {
        my $KeyVal = $input->{'keyval'};
        #my $dataRow = LoadItem($szTableName, \@arrColumnName, $nColSort, $KeyVal, $dbh);
        my $dataRow = LoadItem($dbh,$KeyVal);
        #SetEditFrame($szTitle, \@arrColumnTitle, $nItemStart, \@arrColumnName, \@dataRow, $nColSort, $template);
        $template->param(EditItem => 1);
        $dataRow->{'varType'}='text' if(!defined $dataRow->{'varType'} || $dataRow->{'varType'} eq '');
        my $varInpuType="varType_" . $dataRow->{'varType'};
        $template->param($varInpuType => 1);

        while(my($key, $value) = each(%$dataRow)){
            $template->param($key => $value);
        }
        my $valParamList =getValParamList($dbh,$KeyVal);
        $template->param(valParamList => $valParamList);
        tmpl_write($dbh, $cgi, $cookie, $template);
        goto __END_OF_FILE;
    }

    
    if  ( $input->{'act'} && $input->{'act'} eq 'update' )
    {
        my $KeyVal = $input->{'keyval'};

        update_var($dbh,$input->{'var'},$input->{'val'});
        #UpdateItem($szTableName, \@arrColumnName, $nColSort, $KeyVal, $dbh, $input);
        if ($input->{'var'} ne 'useAttOrder') {
            $template->param($input->{'var'} => $input->{'val'});
        }
        else {
            my ($searchField, $sfIndex) = tmpl_searchFieldOrder($input->{'val'});
            my $sf0;
            ($sf0 = $cgi->cookie('sf0')) || ($sf0 = 1016);
            $template->param(
                sf  => $searchField,
                sfi => $sfIndex->{$sf0},
            );
        }
    }
=item
 #Ha: Fri, Oct 09, 2009 @ 14:35:22 EDT
 
    SetViewFrame($szTitle, \@arrColumnTitle, $nItemStart, $template);
    @data = GetItems($szTableName, \@arrColumnName, $nColSort, $nItemStart, $nItemInPage, $dbh);
    $template->param( ViewItems => 1 );
    $template->param( RowData => \@data );
=cut

    # Ha: Fri, Oct 09, 2009 @ 14:35:22 EDT
    # Get new preference by group
    getPrefListGroup();

    $template->param(scriptname => 'syspref');
    my $menuEnable = tmpl_makeMenu($dbh, $permission);
        $template->param($menuEnable);
        tmpl_write($dbh, $cgi, $cookie, $template);

#-------------------------------------------------------------
# Load items from the table with given array column names.
# ItemsInPage items are loaded from the ItemBegin 
# After loaded, items are passed to template
#
sub GetItems
{
    my ($szTableName, $arrColumnName, $nColSort, $nItemBegin, $nItemsInPage, $dbh) = @_;

    # Create SQL command from column names
    my $szSQL = "select ";
    
    foreach my $column (@$arrColumnName)
    {
        $szSQL .= $column . ", ";
    }
    $szSQL =~ s/, $//;
    
    # Retrieve data
    $szSQL .= " from " . $szTableName . " where hidden=0 order by " . @$arrColumnName[$nColSort] . " asc";

    if ( $nItemBegin ge 0 && $nItemsInPage )
    {
        $szSQL .= " limit " . $nItemBegin . ", " . $nItemsInPage;
    }
    my $insertCmd = $dbh->prepare($szSQL);   
    my $bResult = $insertCmd->execute();
    
    # Convert data to array of hash
    my $nNumOfColumn = @$arrColumnName;
    my @dataSheet = ();
    my @row = ();
    my $j = 0;

    while ( @row  = $insertCmd->fetchrow_array )
    {
        my @rowData = ();
        for (my $i = 0; $i < $nNumOfColumn; $i++)
        {
            push @rowData, { CellData => @row[$i] };            
        }
        push @dataSheet, {  ColData => \@rowData, KeyName => @row[$nColSort],
                            OddRow => $j%2
                         };
        $j++;
    }
  
    $insertCmd->finish;
    return @dataSheet;
}

#-------------------------------------------------------------
# Set title, column titles and item number to display a page
#
sub SetViewFrame
{
    my ($szTitle, $arrColumnTitle, $nItemStart, $template) = @_;

    $template->param(Title => $szTitle);
    $template->param(ItemStart => $nItemStart);

    my @ColumnTitles = ();
    foreach my $szTitle ( @$arrColumnTitle )
    {
        push @ColumnTitles, { FieldTitle => $szTitle };
    }
    
    $template->param(ColumnTitle => \@ColumnTitles);
}

#-------------------------------------------------------------
# Set title, key name, key value, column titles and old column values
#
sub SetEditFrame
{
    my ($szTitle, $arrColumnTitle, $nItemStart, $arrColumnName, $arrColumnVal, $nSortCol, $template) = @_;

    $template->param(Title => $szTitle);
    $template->param(ItemStart => $nItemStart);

    my @ColumnTitles = ();
    $template->param( KeyTitle => @$arrColumnTitle[$nSortCol] );
    $template->param( KeyName => @$arrColumnName[$nSortCol] );
    $template->param( KeyVal => @$arrColumnVal[$nSortCol] );
    
    my $NumOfColumns = @$arrColumnTitle;
    for ( my $i = 0; $i < $NumOfColumns; $i++ )
    {
        if ( $i ne $nSortCol )
        {
            push @ColumnTitles, {   FieldTitle => @$arrColumnTitle[$i], 
                                    FieldName => @$arrColumnName[$i], 
                                    FieldValue => @$arrColumnVal[$i], 
                                    description=>(@$arrColumnName[$i] eq 'description')
                                };
        }
    }
     
    $template->param(DataSheet => \@ColumnTitles);
}

#-------------------------------------------------------------
# Set title, column titles
#
sub SetAddFrame
{
    my ($szTitle, $arrColumnTitle, $arrColumnName, $nItemStart, $nSortCol, $template) = @_;

    $template->param(Title => $szTitle);
    $template->param(ItemStart => $nItemStart);

    my @ColumnTitles = ();

    push @ColumnTitles, { FieldTitle => @$arrColumnTitle[$nSortCol], FieldName => @$arrColumnName[$nSortCol] };

    my $NumOfColumns = @$arrColumnTitle;
    for ( my $i = 0; $i < $NumOfColumns; $i++ )
    {
        if ( $i ne $nSortCol )
        {
            push @ColumnTitles, {   FieldTitle => @$arrColumnTitle[$i], 
                                    FieldName => @$arrColumnName[$i] };
        }
    }
    $template->param( DataSheet => \@ColumnTitles );
}

#-------------------------------------------------------------
# Load an item from the table name with given column names
# The key of item is given in szKeyVal, correspond to the 
# column nColSort
#
sub LoadItem_old
{
    my ($szTableName, $arrColumnName, $nColSort, $szKeyVal, $dbh) = @_;

    # Create SQL command
    my $szSQL = "select * from " . $szTableName . " where " . @$arrColumnName[$nColSort] . "=?";
    
    # Retrieve data
    my $insertCmd = $dbh->prepare($szSQL);   
    my @arrParam = ();
    push @arrParam, $szKeyVal;
    my $bResult = $insertCmd->execute(@arrParam);

    my @row = $insertCmd->fetchrow_array;

    $insertCmd->finish;
    return @row;
}
#-------------------------------------------------------------

sub LoadItem
{
    my ($dbh,$var) = @_;

    # Create SQL command
    my $sth = $dbh->prepare("select * from opl_preference where var =?");
    
    $sth->execute($var);

    my $rec = $sth->fetchrow_hashref;

    $sth->finish;
    return $rec;
}
#-------------------------------------------------------------

sub getValParamList{
    my($dbh,$var)=@_;
    my @retArr=();
    my @selList=();
    my $sth = $dbh->prepare("select varType,val from opl_preference where var =?");
    $sth->execute($var);
    if(my ($t,$v)=$sth->fetchrow_array){
        if($t =~ m/^checkbox$/g){
            @selList= split(/,/,$v);
        }
        else{
            push @selList,$v;
        }
    }
    $sth = $dbh->prepare("select * from opl_prefFormParam where var =?");
    $sth->execute($var);
    while(my $rec=$sth->fetchrow_hashref){
        foreach my $selVal(@selList){
            if($selVal eq $rec->{'val'}){
                $rec->{'selected'}=1;
                last;
            }
        }
        push @retArr,$rec;
    }
    return \@retArr;


}
#-------------------------------------------------------------
# Delete the row in table szTableName with given key value KeyVal
#
sub DeleteItem
{
    my ($szTableName, $szKeyCol, $szKeyVal, $dbh) = @_;

    # Create SQL command
    my $szSQL = "delete from " . $szTableName . " where " . $szKeyCol . "=?";

    my $insertCmd = $dbh->prepare($szSQL);   
    my @arrParam = ();
    push @arrParam, $szKeyVal;
    my $bResult = $insertCmd->execute(@arrParam);

    $insertCmd->finish;
    return $bResult;
}

#-------------------------------------------------------------
# Update szTableName with given input and key value KeyVal
#
sub UpdateItem
{
    my ($szTableName, $arrColumnName, $nColSort, $szKeyVal, $dbh, $input) = @_;

    # Create SQL command
    my $szSQL = "update " . $szTableName . " set ";

    my $NumOfColumns = @$arrColumnName;
    for ( my $i = 0; $i < $NumOfColumns; $i++ )
    {
        if ( $i ne $nColSort )
        {
            $szSQL .= @$arrColumnName[$i] . "=?, ";
        }
    }
    $szSQL =~ s/, $//;
    $szSQL .= " where " . $arrColumnName[$nColSort] . "=?";
 
    my $sz = '';
    my @arrParam = ();
    for ( my $i = 0; $i < $NumOfColumns; $i++ )
    {
        if ( $i ne $nColSort )
        {
            push @arrParam, $input->{@$arrColumnName[$i]};
            $sz .=  $input->{@$arrColumnName[$i]} . "__";
        }
    }
    push @arrParam, $input->{@$arrColumnName[$nColSort]};
    
    my $insertCmd = $dbh->prepare($szSQL);   
    my $bResult = $insertCmd->execute(@arrParam);

    $insertCmd->finish;
    return $bResult;
}

sub update_var
{
    my ($dbh,$varName,$varValue) = @_;

    my $sth =$dbh->prepare("select * from opl_preference  where var=?");
    $sth->execute($varName);
    if(my $rec=$sth->fetchrow_hashref){
        my $val='';
        my $valShow=$varValue;
        if($rec->{'varType'} =~ m/^radio$|^select$/g){
            ($valShow)=$dbh->selectrow_array("select name from  opl_prefFormParam where var='$varName' && val='$varValue'");
        }
        if($rec->{'varType'} =~ m/^checkbox$/g){
            $val =join(",",@$varValue);
        }
        else{
            $val      =$varValue;
            $valShow  =$varValue;
        }
        $sth =$dbh->prepare("update opl_preference set val=?,valShow=? where var=?");
        $sth->execute($val,$valShow,$varName);
    }
    $sth->finish;
}

#-------------------------------------------------------------
# Insert given input into table szTableName
#
sub InsertItem
{
    my ($szTableName, $arrColumnName, $dbh, $input) = @_;

    # Create SQL command
    my $szSQL = "insert into " . $szTableName . " set ";

    my $nNumOfColumn = @$arrColumnName;
    for ( my $i=0; $i < $nNumOfColumn; $i++)
    {
        $szSQL .= @$arrColumnName[$i] . "=?, ";
    }
    $szSQL =~ s/, $//;
    
    my $insertCmd = $dbh->prepare($szSQL);   
    my @arrParam = ();
    for ( my $i = 0; $i < $nNumOfColumn; $i++ )
    {
        push @arrParam, $input->{$arrColumnName->[$i]};
    }
    my $bResult = $insertCmd->execute(@arrParam);

    $insertCmd->finish;
    return $bResult;
}

#--------------------------------------------------------------
# Retrieve the row number with given key value KeyVal
# 

sub GetItem
{
    my ($szTableName, $arrColumnName, $nColSort, $szKeyVal) = @_;

    # Create SQL command
    my $szSQL = "select * from " . $szTableName . " where ";

    $szSQL .= @$arrColumnName[$nColSort] . "=" . $szKeyVal;
    
    my $searchCmd = $dbh->prepare($szSQL);   
    my $bResult = $searchCmd->execute();

    my $arrResult = $searchCmd->fetchrow_hashref;
    my $IsExist = ( $arrResult ) ? 1 : 0 ;

    $searchCmd->finish;

    return $IsExist;
}

#--------------------------------------------------------------
sub getPrefListGroup{
my $sth_pref=$dbh->prepare('select * from opl_preference p inner join opl_prefGroup g on g.id=p.gid where  hidden=0 order by gid,gOrder');
$sth_pref->execute();

my @prefMenu =();
my $pNum = -1;
my $preGid=0;
while(my $p=$sth_pref->fetchrow_hashref){
     if($preGid != $p->{'gid'}){
         my $pref;
         $pNum++;
         $preGid   = $p->{'id'};
         my $title = $p->{'title'};
         $pref->{'gid'} = $preGid;
         $pref->{'title'} = $title;
         $pref->{'submenu'}=();
         $pref->{'submenuCount'}=0;
         my $submenuPref=();
         push @prefMenu,$pref;
     }
     
     my $subPref;

     my $i=@prefMenu[$pNum]->{'submenuCount'};
     $subPref->{'gOrder'}       = $p->{'gOrder'};
     $subPref->{'var'}          = $p->{'var'};
     $subPref->{'val'}          = $p->{'val'};
     $subPref->{'valShow'}      = $p->{'valShow'};
     $subPref->{'hidden'}       = $p->{'hidden'};
     $subPref->{'description'}  = $p->{'description'};
     $subPref->{'opt'}          = $p->{'opt'};
     @prefMenu[$pNum]->{'submenu'}->[$i] =$subPref;
     @prefMenu[$pNum]->{'submenuCount'} +=1;
}
$template->param(prefMenu => \@prefMenu);

$sth_pref->finish;

}


#--------------------------------------------------------------
__END_OF_FILE: