#!/usr/bin/perl

#use utf8;
use strict;
use CGI;

use Opals::Context;
use Date::Calc qw(Day_of_Week Week_Number Day_of_Year);

use Opals::User qw(
    user_getInformation
);
use Opals::Date qw(
    date_parse
    date_today
    date_text
    date_f005
);
use Opals::Template qw(
    tmpl_read
    tmpl_write
    tmpl_rangedPageList
);


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

my $cgi = CGI->new;
my $input = $cgi->Vars();
my ($code, $data,$sField,$sOrder) = ($input->{'code'}, $input->{'data'}, 
                                  $input->{'sField'}, $input->{'sOrder'});

 
my ($permission, $cookie, $template) = tmpl_read(
        {
            dbh             => $dbh,
            cgi             => $cgi,
            tmplFile        =>'search/f852sf_prt.tmpl',
            reqPermission   => 'report',
        }
);


    my $op    = $input->{'op'};
    if ($code && $data) {
        my ($contStr ,@bindVal) =  genCont ($code, $data);
        if($op && $op eq 'csv'){
            #Thu, May 05, 2011 @ 13:46:49 EDT
            my $csv= GetRecordCSV_all($dbh,$contStr ,\@bindVal,$sField,$sOrder);
            print $cgi->header(-type => 'text/csv', 
                                -attachment=>'sf852report.csv'
                                );
            foreach my $r (@$csv){
                print "$r\n";
            }
        }
        else{
            my @recordList;
            @recordList = GetRecordPrint_all($dbh,$contStr ,\@bindVal,$sField,$sOrder);
            $template->param(recordPrint  => \@recordList,
                             sfCode           => $input->{'code'},
                             sfData           => $input->{'data'}
                            );
            tmpl_write($dbh, $cgi, $cookie, $template);
        }
    }  
 
    
#$dbh->disconnect();

################################################################################
sub genCont{
    my ($code, $data) = @_;
    my $MIN_LENGTH = 2;
    my @bindVal = ();

    $data =~ s/\*/%/g;
    my $dataCondition = ' i.available =1 && ';
   # if($code eq 'p'){
   #     $dataCondition .="  ii.barcode like ?";
   #     @bindVal=($data);
   # }
   # else{
        @bindVal=($code);
        $dataCondition .=" ii.sf852Code = ? ";
        if ($input->{'sType'} =~ m/^exact$/i) {
            $dataCondition .= " && ii.sf852Data = ?";
            @bindVal = (@bindVal, $data);
        }
        elsif ($data =~ m/[\%]/) {
            $dataCondition .= " && ii.sf852Data like ?";
            @bindVal = (@bindVal, $data);
        }
        elsif ($data =~ m/^".+"$/) {
            $dataCondition .= " && ii.sf852Data like ?";
            $data =~ s/(^"|"$)/%/g;
            @bindVal = (@bindVal, $data);
        }
        else {
            foreach my $word (split(/ /, $data)) {
                if (length $word < $MIN_LENGTH) {
                    $dataCondition .= " && (ii.sf852Data = ? || ii.sf852Data like ? || ii.sf852Data like ? || ii.sf852Data like ?)";
                    @bindVal = (@bindVal, $word, "$word %", "% $word %", "% $word");
                }
                else {
                    $dataCondition .= " && match(sf852Data) against (?)";
                    @bindVal = (@bindVal, $word);
                }
            }
        }
  #  }


    return ($dataCondition,@bindVal);

}
################################################################################

sub GetRecordCSV_all{
  my ($dbh,$dataCondition,$bindVal,$sField,$sOrder) = @_;
  if($sField =~ m/barcode|rid/i){
    $sField = 'i.' .$sField;
  }

  my $sql = <<_SQL_;
select  concat('"',i.rid,'","', 
replace(if (title is not null,title,''),'"','""'),'","', 
replace(if(author is not null,author,''),'"','""'),'","',
replace(if(pubDate is not null,pubDate,''),'"','""'),'","',
replace(if(i.callNumber is not null,i.callNumber,''),'"','""'),'","',i.barcode,'","',
if(price is not null,price,''),'","', 
replace(if(sf852Data is not null,sf852Data,''),'"','""'),'"')
        
from    opl_itemInfo as ii inner join  opl_item as i on i.barcode=ii.barcode inner join opl_marcRecord as m on m.rid=i.rid
_SQL_

  $sql .= " where $dataCondition " if($dataCondition && $dataCondition ne '');
  $sql .= " order by $sField $sOrder " if($sField ne '');
  my $sth = $dbh->prepare($sql);
  $sth->execute(@$bindVal);
  my @rList=();

  push @rList,'"Rid","Title","Author","Pub. Date","CallNumber","Barcode","Price","Subfield Data"';
  while(my ($r)=$sth->fetchrow_array){
        push @rList,$r;
  }
  $sth->finish;
 return \@rList;

}


################################################################################
sub GetRecordPrint_all {
    my ($dbh,$dataCondition,$bindVal,$sField,$sOrder) = @_;
    
  if($sField =~ m/barcode/i){
    $sField = 'i.' .$sField;
  }

    my $sql = <<_SQL_;
select  i.rid, i.barcode,i.price,i.callNumber,
        m.title, m.author,m.pubDate, m.pubDateSort,ii.sf852Data
from    opl_itemInfo as ii inner join  opl_item as i on i.barcode=ii.barcode inner join opl_marcRecord as m on m.rid=i.rid
_SQL_


       $sql .= " where $dataCondition " if($dataCondition && $dataCondition ne '');
       $sql .= " order by $sField $sOrder " if($sField ne '');
      my $sth = $dbh->prepare($sql);
    $sth->execute(@$bindVal);
    my @recDisplay;
    my $preRid=-1;
    my $rec=undef;
    my @holdingInfo;
    my $i=-1;
    while (my $r = $sth->fetchrow_hashref) {
            if($preRid != $r->{'rid'}){
                $preRid=$r->{'rid'};
                $i++;
                $recDisplay[$i]={rid=>$r->{'rid'},
                                 title=>$r->{'title'},
                                 author=>$r->{'author'},
                                 pubDate=>$r->{'pubDate'},
                                 holdingCount=>1,
                                 holding=>[{ barcode=>$r->{'barcode'},
                                            price=>$r->{'price'},
                                            sf852Data=>$r->{'sf852Data'},
                                            callNumber=>$r->{'callNumber'}}]
                                            
                                 };

            }
            else{
                push @{$recDisplay[$i]->{'holding'}},{ barcode=>$r->{'barcode'},
                                            price=>$r->{'price'},
                                            sf852Data=>$r->{'sf852Data'},
                                            callNumber=>$r->{'callNumber'}};
                $recDisplay[$i]->{'holdingCount'} +=1;
            }
    }

    $sth->finish;

    return @recDisplay;

 }

################################################################################


sub getHoldingInfo_bk {
    my ($dbh, $rid, $dataCondition, $bindValue) = @_;
    $dataCondition =~ s/^ [\&]{2} //;
    my @bindVal = ($rid, @{$bindValue});

    my $sth = $dbh->prepare(<<_SQL_);
select  i.*,
        ii.sf852Data
from    opl_item as i inner join opl_itemInfo as ii on i.barcode =ii.barcode
where   i.rid = ? &&
        $dataCondition
_SQL_

    $sth->execute(@bindVal);
    my @holdingInfo;
    while (my $sf = $sth->fetchrow_hashref) {
        push @holdingInfo, $sf;
    }
    $sth->finish;

    return \@holdingInfo;
}