#!/usr/bin/perl

#use utf8;
use strict;
use POSIX qw(
    ceil
    floor
);
use CGI;

use Text::CSV_XS;
use Opals::Context;
use Opals::Constant;

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

use Opals::User qw(
    user_getUserList
    user_getUserCircCount
    user_getUserCircCount_textbook
);

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        => 'user/grpprint.tmpl',
            reqPermission   => 'user_edit',
        }
);
my $pref = tmpl_preference($dbh);

my $libType = $pref->{'libraryType'} || 'k-12';


my $fields={
    "public"    =>["uid","sid","title","firstname","middlename","lastname","birthday","gender",
                      "created","userbarcode", "categorycode", "email","phone","workPhone","cellPhone",
                      "addrLine1","addrLine2","city","zip","state","country","neighborhood","status"
                     ],
    
    "k-12"      =>["uid","sid","title","firstname","middlename","lastname","birthday","gender",
                      "created","userbarcode", "categorycode", "email","phone","cellPhone",
                      "addrLine1","addrLine2","city","zip","state","country","status",
                      "buildingcode","homeroom","teacher","grade","yeargraduation"
],
    "academy"=>["uid","sid","title","firstname","middlename","lastname","birthday","gender",
                      "created","userbarcode", "categorycode", "email","phone","workPhone","cellPhone",
                      "addrLine1","addrLine2","city","zip","state","country","status",
                      "program","studies","permanent","fulltime"
]
    };
       

    my $t =time; 
    my $r= floor(100*rand());
    my $tblName= "tmp_user_prt_$t" ."_" . $r ;
    my $sortBy =$input->{'sortBy'} || 'lastname';
    my $sortDir =$input->{'sortDir'} || 'asc';
    if($sortBy eq 'lastname'){
        $sortBy .=",firstname";
    }
    if($sortBy eq 'firstname'){
        $sortBy .=",lastname";
    }
    else{
        $sortBy .=",lastname,firstname";
    }
    createTmpTbl($dbh,$tblName,$sortBy,$sortDir);

    if($input->{'op'} eq 'exportCsv'){
        exportAsCSV($dbh,$tblName);
    }
    else{
        printList($dbh,$tblName);
    }

    cleanTmpTbl($dbh,$tblName);


#-------------------------------------------------------------------------------------------------
sub exportAsCSV1{
    my($dbh,$tblName) =@_;

    my $fields = $fields->{$libType};

   print "Content-Encoding: UTF-8\n";
   print "Content-type: text/csv; charset=UTF-8\n";
   print "Content-Disposition:attachment;filename=BorrowerList.csv\n\n"; 
   print  join(",",@$fields), "\r\n";
   my $sql ="select " . join(",",@$fields) . " from $tblName" ;
   my $sth=$dbh->prepare($sql);
   $sth->execute();
   while(my @data=$sth->fetchrow_array){
           print  join(",",@data), "\r\n";
   }
    
}

#-------------------------------------------------------------------------------------------------
sub exportAsCSV{
    my($dbh,$tblName) =@_;
my $csv = Text::CSV_XS->new ({ binary => 1, eol => $/ });

    my $fields = $fields->{$libType};

   print "Content-Encoding: UTF-8\n";
   print "Content-type: text/csv; charset=UTF-8\n";
   print "Content-Disposition:attachment;filename=BorrowerList.csv\n\n"; 
   print "\"",  join("\",\"",@$fields), "\"\r\n";
   my $sql ="select " . join(",",@$fields) . " from $tblName" ;
   my $sth=$dbh->prepare($sql);
   $sth->execute();
   while(my @data=$sth->fetchrow_array){
       $csv->combine( @data) ;
       print $csv->string();
   }
    
}
#-------------------------------------------------------------------------------------------------

sub printList{
    my($dbh,$tblName) =@_;
    my $userList=[];
    my $sth=$dbh->prepare("select * from $tblName ");
    $sth->execute();
    while(my $user=$sth->fetchrow_hashref){
          push @$userList,$user; 
    }
    $template->param(userlist => $userList);
    tmpl_write($dbh, $cgi, $cookie, $template);
}

#-------------------------------------------------------------------------------------------------
sub getUserList{
    my($dbh)= @_;
    my $ssId = $cgi->cookie('globalSessionID');

    
}
#-------------------------------------------------------------------------------------------------
sub cleanTmpTbl{
    my ($dbh,$tblName)=@_;
    $dbh->do("drop table $tblName");

}

#-------------------------------------------------------------------------------------------------
sub createTmpTbl{
    my ($dbh,$tblName,$sortBy,$sortDir)=@_;
    my $ssId = $cgi->cookie('globalSessionID');
    my $ssName="userGrpMod";
    my $sql="CREATE TABLE if not exists $tblName (uid int(10) unsigned NOT NULL,";
    my $fields = $fields->{$libType};
    foreach my $f(@$fields){
        next if($f eq 'uid');
        $sql .= "$f varchar(255) DEFAULT '' ," ;
    }
    $sql .= "od int(5) unsigned default 0,"; 
    $sql .= "loan int(5) unsigned default 0,"; 
    $sql .= "od_tb int(5) unsigned default 0,"; 
    $sql .= "loan_tb int(5) unsigned default 0,"; 

    $sql .= " PRIMARY KEY (`uid`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8";
    $dbh->do($sql);


    $sql="insert into $tblName (".  join("," , @$fields) ." ) select u." ;
    $sql .=  join(",u.", @$fields) ;
    $sql .=  " from opl_user u inner join opl_sessionVar s 
         on u.uid=s.rid && s.var='$ssName' && ssid='$ssId' order by $sortBy $sortDir";
    $dbh->do($sql);
    $dbh->do(<<_SQL_);
    update  $tblName u inner join (select uid,count(*) od from opl_loan l inner join $tblName u using(uid) 
                        where dateReturn is null && dateDue <now() group by uid) s using(uid) set u.od=s.od
_SQL_
                        
    $dbh->do(<<_SQL_);
    update  $tblName u inner join (select uid,count(*) loan from opl_loan l inner join $tblName u using(uid) 
                        where dateReturn is null  group by uid) s using(uid) set u.loan=s.loan
_SQL_
         
    $dbh->do(<<_SQL_);
    update  $tblName u inner join (select uid,count(*) od_tb from tb_loan l inner join $tblName u using(uid) 
                        where dateReturn is null && dateDue <now() group by uid) s using(uid) set u.od_tb=s.od_tb
_SQL_
                        
    $dbh->do(<<_SQL_);
    update  $tblName u inner join (select uid,count(*) loan_tb from tb_loan l inner join $tblName u using(uid) 
                        where dateReturn is null  group by uid) s using(uid) set u.loan_tb=s.loan_tb
_SQL_

}
#
#