#Calling file
#!/usr/bin/perl
use strict;
use Template;
use CGI;
use CGI::Session ( '-ip_match' );
use DBI;
use Data::Dumper;
require "class.pm";
my $cgi = new CGI;

my @cases = show_cases_home($library_id,$users_id);

my $template = Template->new();
my $file = 'templates/home.tt';
my $vars = {
  
     cases          =>  @cases
};

$template->process($file,$vars)
   || die $template->error(), "\n";

# SUB ROUTINE IN CLASS FILE

sub show_cases_home {
    my $dbh = new_dbh();
    my @cases;
    my $library_id = $_[0];
    my $assigned_to = $_[1];
    #my $query = 'SELECT c.*, DATE_FORMAT(c.added_date, "%d/%m/%Y"), b.firstname, b.surname from cases c, borrower b
    #WHERE c.library_id=? AND c.assigned_to = ? and c.borrower_id = b.borrower_id order by added_date desc';
    my $query = 'SELECT c.caseid,c.status,c.case_header,DATE_FORMAT(c.added_date, "%d/%m/%Y"), b.firstname, b.surname 
                 FROM cases c
                 LEFT JOIN borrower b USING (borrower_id)
                 WHERE c.library_id=? AND c.assigned_to =?
                 AND c.status = "open"
                 order by added_date desc';   
    my $sth = $dbh->prepare($query);
    $sth->execute($library_id,$assigned_to);
    while (my @row = $sth->fetchrow_array()) {
    push @cases, \@row;
    }
    return \@cases;
}

# TEMPLATE FILE SHOWING ALL CORRECTLY
[% INCLUDE templates/header_auth.inc %]
[% FOREACH wah IN cases %]
            <td><a href="show_case.pl?caseid=[% wah.0 %]">[% wah.0 %]</a></td><td>[% wah.3 %]</td><td>[% wah.2 %]</td><td>[% wah.4 %] [% wah.5 %]</td><td>[% wah.1 %]</td>
        </tr>
[% END %]

# END of WORKING FILES

##</code><code>##


use strict;
use Template;
use CGI;
use CGI::Session ( '-ip_match' );
use DBI;
use Data::Dumper;
use Sphinx::Search;
require "class.pm";
my $cgi = new CGI;
my $dbh = new_dbh();
my $caseid = $cgi->param("caseid");
my $users_id = "31";
my @lines = word_test($caseid,$users_id);

# Sub routine

sub word_test {
    my $dbh = new_dbh();
    my @cases;
    my $caseid = $_[0];
    my $assigned_to = $_[1];
    my $query = 'SELECT caseid,status,case_header,DATE_FORMAT(added_date, "%d/%m/%Y")
                 FROM cases
                 where caseid = ? and assigned_to = ?
                 order by added_date desc';   
    my $sth = $dbh->prepare($query);
    $sth->execute($caseid,$assigned_to);
    while (my @row = $sth->fetchrow_array()) {
    push @cases, \@row;
    }
    return \@cases;
}

# TEMPLATE FILE
[% INCLUDE templates/header_auth.inc %]

[% FOREACH row IN lines %]
"[% row.0 %] [% row.2 %] "
<br />
[% END %]

# END FILE