#!/usr/bin/perl

use strict;
use CGI;
use Time::localtime;

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


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


#my $aSubfieldMap={'manufacturer'=>'1','location'=>'11'};
my $aFieldMap = {
            'manufacturer'  =>{
                'code'      => '1',
                'type'      => 'record',
                'name'      => 'Manufacturer'
            },
            'location'      =>{
                'code'      => '11',
                'type'      => 'item',
                'name'      => 'Location In Bulding'
            },
            'status_notes'  =>{
                'code'      => '38',
                'type'      => 'item',
                'name'      => 'Item Status Notes'
            },
            'accessories'   =>{
                'code'      => '48',
                'type'      => 'item',
                'name'      => 'Accessories'
            },
            'itemType'     =>{
                'code'      => 'itemType',
                'type'      => 'item',
                'name'      => 'Item Type'
            }
};

my $cgi = CGI->new;
my $input = $cgi->Vars();
my ($permission, $cookie, $template) = tmpl_read(
    {
        dbh             => $dbh,
        cgi             => $cgi,
        tmplFile        => 'eqmnt/util/authorityCtrlMgr.tmpl',
        reqPermission   => 'eq_record_edit',
    }
);


my $authCtrlName = $input->{'authCtrlName'};

if($authCtrlName eq '' || !defined $authCtrlName){
    $authCtrlName= $cgi->cookie('authCtrlName');
}
push @$cookie, $cgi->cookie(-name => 'authCtrlName', -value => $authCtrlName,);

if($aFieldMap->{$authCtrlName}){
    my($name,$fCode)=($authCtrlName,$aFieldMap->{$authCtrlName}->{'code'});
    my $changes =getAuthCtrlChanges();
    if(defined $changes && scalar @$changes>0){
        updateAuthCtrlTbl($dbh,$authCtrlName,$changes);
    }
    my $geChanges = getGlobalChanges();
    if ($geChanges && scalar @$geChanges>0){
        updateDatabaseTbl($dbh,$authCtrlName,$fCode,$geChanges);
    }
    my $authList        = getAuthorityCtrlList($dbh,$name,$fCode);
    my $fieldDataStats  = getFieldsDataStats($dbh,$name,$fCode);
    
    $template->param(
        authCtrlList    =>$authList,
        numAuthList     =>scalar(@$authList) || 0,
        fieldDataStats  =>$fieldDataStats,
    );
}

$template->param(
    
    authCtrlName        =>$authCtrlName,
    authCtrlCode        =>$aFieldMap->{$authCtrlName}->{'code'},
    authCtrlType        =>$aFieldMap->{$authCtrlName}->{'type'},
    recordType          =>($aFieldMap->{$authCtrlName}->{'type'} eq 'record')?'1':'0',
    authCtrlTypeTxt     =>ucfirst($aFieldMap->{$authCtrlName}->{'type'}).'(s)',
    authCtrlNameTxt     =>$aFieldMap->{$authCtrlName}->{'name'}, 
    authCtrlNameTitleTxt=>" Authority Control List",
    addNewTxt           =>"Add New " . $aFieldMap->{$authCtrlName}->{'name'},
    selNewTxt           =>"Select New " . $aFieldMap->{$authCtrlName}->{'name'}
    
);


tmpl_write($dbh, $cgi, $cookie, $template);

#================================================================================
sub getAuthorityCtrlList {
    my($dbh,$name,$fCode)=@_;

    my $authList = [];
    my $i=0;
    my $sth=$dbh->prepare("select fData valStr 
                           from eq_authCtrl where name=? &&  fCode=?");
    $sth->execute($name,$fCode);
    while(my $rec=$sth->fetchrow_hashref){
        $rec->{'index'}=$i++;
        $rec->{'valStr'} =_escapeXml($rec->{'valStr'});
        push @$authList,$rec;
    }
    return $authList;
}

sub _escapeXml {
    my ($str) = @_;

    return unless $str;
    
    $str =~ s/&/&/g;
    $str =~ s/"/"/g;
    $str =~ s/'/'/g;
    $str =~ s/</&lt;/g;
    $str =~ s/>/&gt;/g;
    return $str;
}

sub getAuthCtrlChanges{
    my $i=0;
    my $changes=[];

    while(defined $input->{"authorCtrlVal_" . $i}){
        if($input->{"authorCtrlVal_". $i} ne $input->{"old_authorCtrlVal_". $i}){
            push @$changes,{oldVal=>$input->{"old_authorCtrlVal_". $i},
                            newVal=>$input->{"authorCtrlVal_". $i}};
        }
        $i++;
    }
    my @newVal=$cgi->param("authorCtrlVal_new");
    foreach my $val(@newVal){
         push @$changes,{newVal=>$val};
    }
    return $changes;
}

sub updateAuthCtrlTbl{
    my($db,$authCtrlName,$changes)=@_;
    my($name,$fCode)=($authCtrlName, $aFieldMap->{$authCtrlName}->{'code'});
    my $sth_update=$dbh->prepare("update eq_authCtrl set fData=? where fData=? && name=? && fCode=?");
    my $sth_insert=$dbh->prepare("insert into eq_authCtrl set fData=? ,name=? ,fCode=?");
    my $sth_del=$dbh->prepare("delete from eq_authCtrl where name=? && fCode=? && fData=?");
    foreach my $c(@$changes){
        if($c->{'newVal'} eq '' ){
            $sth_del->execute($name,$fCode,$c->{'oldVal'});
        }
        elsif($c->{'oldVal'} ne '' ){
           $sth_update->execute($c->{'newVal'},$c->{'oldVal'},$name,$fCode);
        }
        else{
            $sth_insert->execute($c->{'newVal'},$name,$fCode);
        }
    }
    $sth_insert->finish;
    $sth_update->finish;
}

sub getFieldsDataStats {
    my ($dbh,$name, $fId) = @_;
    my $stats = [];
    my $sql = "";
    if ($aFieldMap->{$authCtrlName}->{'type'} eq "record"){
        $sql = "
        select rf.rid, if(rf.fValue is NULL,'',rf.fValue) as fValue,
        (select count(*) from eq_items i where i.rid  = r.rid && i.barcode not regexp '^___') as iCount,
        if (at.fData is null, 0, 1) as inAuthList              
        from eq_records r              
        inner join eq_recordFields rf using(rid)              
        left outer join eq_authCtrl at on rf.fId=at.fCode && binary rf.fValue = at.fData              
        where rf.fId= ? group by r.rid,rf.fValue order by rf.fValue ";
    }
    else{
        if ( $authCtrlName eq 'itemType'){
            $sql = "
            select i.rid, if(i.typeId is NULL,'',i.typeId) as fValue, 1 as iCount,if (at.fData is null, 0, 1) as inAuthList 
            from eq_records r inner join eq_items i using(rid) left outer join eq_authCtrl at on i.typeId = at.fData  
            where  i.barcode not regexp '^___'  order by i.typeId";
        }
        else{
            $sql ="
            select i.rid, 
            if(itf.sfValue is NULL,'',itf.sfValue) as fValue,
            1 as iCount,
            if (at.fData is null, 0, 1) as inAuthList              
            from eq_records r
            inner join eq_items i using(rid)
            inner join eq_itemFields itf using(rid,iid)              
            left outer join eq_authCtrl at on itf.sfId=at.fCode && binary itf.sfValue = at.fData              
            where itf.sfId= ? && i.barcode not regexp '^___'
            group by i.rid, i.iid, itf.sfValue order by itf.sfValue";
        }
    }
    my $sth = $dbh->prepare($sql);        
    if ($authCtrlName eq 'itemType'){
        $sth->execute();
    }
    else{
        $sth->execute($fId);
    }
    my $tmp;
    while( my $rec = $sth->fetchrow_hashref){
        if (!$tmp->{$rec->{'fValue'}}){
            $tmp->{$rec->{'fValue'}}->{'fValue'} = $rec->{'fValue'};
            $tmp->{$rec->{'fValue'}}->{'rCount'} = 1;
            $tmp->{$rec->{'fValue'}}->{'iCount'} = 1;
            $tmp->{$rec->{'fValue'}}->{'inAuthList'} = $rec->{'inAuthList'};
        }
        else{
            $tmp->{$rec->{'fValue'}}->{'rCount'} += 1;
            $tmp->{$rec->{'fValue'}}->{'iCount'} += $rec->{'iCount'};
            $tmp->{$rec->{'fValue'}}->{'inAuthList'} += $rec->{'inAuthList'};
        }
    }
    $sth->finish;
    my $i =0;
    foreach my $k (sort {lc $a cmp lc $b} keys %{$tmp}){
        $tmp->{$k}->{'index'} = $i++;
        push @$stats, $tmp->{$k};
    }
    return $stats;
}

sub getGlobalChanges {

    my $i = 0;
    my $changes = [];
    while(defined $input->{'geField_src_'.$i}){
        if ($input->{'geField_src_'.$i} ne $input->{'geField_tar_'.$i} ){
            push @$changes,  {
                oldVal=>$input->{'geField_src_' .$i},
                newVal=>$input->{'geField_tar_' .$i}
            }
        }
        $i++;
    }
    return $changes;
}


sub updateDatabaseTbl {
    
    my($db,$authCtrlName,$fId, $changes)=@_;
    my($name,$fCode,$fType)=($authCtrlName, $aFieldMap->{$authCtrlName}->{'code'},$aFieldMap->{$authCtrlName}->{'type'});
    my $sth_updateRecField = $dbh->prepare("update eq_recordFields set fValue = ? where fId=? && fValue=? ");
    my $sth_updateItmField = $dbh->prepare("update eq_itemFields set sfValue = ? where sfId=? && sfValue=? ");

    foreach my $c(@$changes){
        if ($fType eq 'record'){
            $sth_updateRecField->execute($c->{'newVal'},$fId,$c->{'oldVal'});
        }
        elsif ($fType eq 'item'){
            $sth_updateItmField->execute($c->{'newVal'},$fId,$c->{'oldVal'});
        }
    }
    $sth_updateRecField->finish;
    $sth_updateItmField->finish;

}