#!/usr/bin/perl

#use utf8;
use strict;
use CGI;
use JSON;

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

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

my $cgi    = CGI->new;
my $input  = $cgi->Vars();

my $pref = tmpl_preference($dbh);
my $libType=$pref->{'libraryType'};
my $rptType =$input->{"rType"};
my $mapField={unpaid        =>{fields=>['balance','date']    ,tbl=>'opl_charge'},
              pendingRefund =>{fields=>['balance','date']    ,tbl=>'opl_charge'},
              waived        =>{fields=>['waive'  ,'date']    ,tbl=>'opl_charge'},
              refunds       =>{fields=>['withdrawn','ondate'],tbl=>'opl_paymentReceipt'},
              collected     =>{fields=>['recieved','ondate'] ,tbl=>'opl_paymentReceipt'}
              };
my ($rTbl,$rFields) = ($mapField->{$rptType}->{'tbl'},$mapField->{$rptType}->{'fields'});
    my @fields=();
    if($libType eq "k-12"){
        @fields=(
                {h=>"BUILDINGCODE"	,f=>"u.buildingcode"},
                {h=>"HOMEROOM"		,f=>"u.homeroom"},
                {h=>"TEACHER"		,f=>"u.teacher"},
                {h=>"GRADE"			,f=>"u.grade"},
                {h=>"YEARGRADUATION",f=>"u.yeargraduation"}
        );
    }
    elsif($libType eq "academy"){
        @fields=(
            {h=>"PROGRAM"        ,f =>"u.program"},
            {h=>"STUDIES"        ,f=>"u.studies"}
            );
    }
    @fields=( @fields,
                {h=>"SID"           ,f=>"u.sid"},
                {h=>"USERBARCODE"   ,f=>"u.userbarcode"},
                {h=>"TITLE"         ,f=>"u.title"},
                {h=>"FIRSTNAME"     ,f=>"u.firstname"},
                {h=>"LASTNAME"      ,f=>"u.lastname"},
                {h=>"ADDRLINE1"		,f=>"u.addrLine1"},
                {h=>"ADDRLINE2"		,f=>"u.addrLine2"},
                {h=>"CITY"			,f=>"u.city"},
                {h=>"ZIP"			,f=>"u.zip"},
                {h=>"STATE"			,f=>"u.state"},
                {h=>"PHONE"			,f=>"u.phone"},
                {h=>"CELLPHONE"		,f=>"u.cellphone"},
                {h=>"FAX"			,f=>"u.fax"},
                {h=>"EMAIL"			,f=>"u.email"},
                {h=>"NOTES"			,f=>"u.notes"},
                {h=>"DATE"			,f=>"max(@$rFields[1])"},
                {h=>"AMOUNT"  		,f=>"sum(@$rFields[0])"}
            );

    my $request =decode_json($input->{"transListJson"});
    #my $request =getRquest();
    my $fArr=[];
    my $hArr=[];
    foreach my $f(@fields){
        push @$hArr,$f->{'h'};
        push @$fArr,$f->{'f'};
    }

   
    my $sql = sprintf "select %s
                  from opl_user u inner join $rTbl c using(uid) 
                  where  uid= ? group by c.uid ", join(', ',@$fArr); 
    
    #open debug ,">/tmp/dd"; print debug "$sql\n"; close debug;

    print "Content-Encoding: UTF-8\n";
    print "Content-type: text/csv; charset=UTF-8\n";
    print "Content-Disposition:attachment;filename=fineReport.csv\n\n"; 

    print  join(",",@$hArr) . "\n";
    my $sth=$dbh->prepare($sql);
    foreach my $t(@$request){
        $sth->execute($t->{'uid'});
        while( my @aa=$sth->fetchrow_array){
            foreach my $c(@aa){
                $c=~ s/"/""/g;
                $c="\"$c\"";
            }
            print  join(",",@aa)  ."\n";
          }
    }

#------------------------------------------------------------------------------

sub getRquest{
  my $request={};
  if ($ENV{'REQUEST_METHOD'} eq "POST") {
        my $json ="";
        while (<STDIN>) {
            $json .= $_;
        }
        $request = decode_json($json);
   }
   return $request;
}