#!/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::MarcXml qw(
    mxml_delete
);
use Opals::Date qw(
    date_parse
    date_today
    date_text
    date_f005
);
use Opals::Template qw(
    tmpl_read
    tmpl_write
    tmpl_rangedPageList
);

use Opals::Session qw(
    SessionHdl_get
);
use Opals::Circulation qw(
    circ_getItemStatus
);
use Opals::Locale qw(
    loc_getMsgFile
    loc_write
);
my $dbh = Opals::Context->dbh();
END { $dbh->disconnect(); }

my $cgi = CGI->new;
my $input = $cgi->Vars();
my $sessionID = $cgi->cookie('globalSessionID');
my ($permission, $cookie, $template) = tmpl_read(
        {
            dbh             => $dbh,
            cgi             => $cgi,
            tmplFile        => 'report/shelfRange_prt.tmpl',
            reqPermission   => 'rpt_notice|rpt_circ',
        }
);
($input->{'type'} && $input->{'type'} eq 'fine')?genFineSid():genOverdueSid();

######################################################################
sub genOverdueSid{

my $sth =$dbh->prepare(<<_SQL_);
select distinct (u.sid ) as sid  
from opl_user u inner join opl_loan l using(uid) 
     inner join opl_category c on c.catid = u.categorycode
where u.categorycode =1   && l.dateDue <now() && dateReturn is null order by sid
_SQL_

   print "Content-Type:application/x-download\n";
   print "Content-Disposition:attachment;filename=ovedue_sid.csv\n\n";    
   print "\"student id\"\n" ;

    $sth->execute();
    while( my ($sid)=$sth->fetchrow_array){
        print "\"$sid\"\n";
    }
          
  } 
######################################################################
sub genFineSid{

my $sth =$dbh->prepare(<<_SQL_);
select u.sid 
from opl_transactions t1 inner join (
               select   max(tid) as tid   
               from     opl_transactions  
               group by uid 
               )as r  
   ON t1.tid=r.tid 
   inner join opl_user u ON u.uid=t1.uid
 where t1.balance <> 0 
_SQL_

   print "Content-Type:application/x-download\n";
   print "Content-Disposition:attachment;filename=fine_sid.csv\n\n";    
   print "\"student id\"\n" ;

    $sth->execute();
    while( my ($sid)=$sth->fetchrow_array){
        print "\"$sid\"\n";
    }
          
  } 

######################################################################
__END_OF_FILE: