From b1180360a84ce9caa77a47c8aa09bd0659787d1e Mon Sep 17 00:00:00 2001
From: Nahuel ANGELINETTI <nahuel.angelinetti@biblibre.com>
Date: Thu, 7 May 2009 11:48:44 +0200
Subject: [PATCH] (bug #3196) fix itemlost.pl

This patch fix 3 things:
* fix order by
* take care of item_level-itype
* use join in sql query

Signed-off-by: Galen Charlton <galen.charlton@liblime.com>
---
 C4/Items.pm                                     | 17 +++++++++--------
 .../prog/en/modules/reports/itemslost.tmpl      |  2 +-
 reports/itemslost.pl                            | 11 +++++++----
 3 files changed, 17 insertions(+), 13 deletions(-)

diff --git a/C4/Items.pm b/C4/Items.pm
index b637e82998..571646268f 100644
--- a/C4/Items.pm
+++ b/C4/Items.pm
@@ -933,23 +933,24 @@ sub GetLostItems {
 
     my $query   = "
         SELECT *
-        FROM   items, biblio, authorised_values
+        FROM   items
+            LEFT JOIN biblio ON (items.biblionumber = biblio.biblionumber)
+            LEFT JOIN biblioitems ON (items.biblionumber = biblioitems.biblionumber)
+            LEFT JOIN authorised_values ON (items.itemlost = authorised_values.authorised_value)
         WHERE
-        		items.biblionumber = biblio.biblionumber
-        		AND items.itemlost = authorised_values.authorised_value
-        		AND authorised_values.category = 'LOST'
+        	authorised_values.category = 'LOST'
           	AND itemlost IS NOT NULL
          	AND itemlost <> 0
-          
     ";
     my @query_parameters;
     foreach my $key (keys %$where) {
         $query .= " AND $key LIKE ?";
         push @query_parameters, "%$where->{$key}%";
     }
-    if ( defined $orderby ) {
-        $query .= ' ORDER BY ?';
-        push @query_parameters, $orderby;
+    my @ordervalues = qw/title author homebranch itype barcode price replacementprice lib datelastseen location/;
+    
+    if ( defined $orderby && grep($orderby, @ordervalues)) {
+        $query .= ' ORDER BY '.$orderby;
     }
 
     my $sth = $dbh->prepare($query);
diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/itemslost.tmpl b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/itemslost.tmpl
index 0f946854ef..3d8efd820c 100644
--- a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/itemslost.tmpl
+++ b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/itemslost.tmpl
@@ -54,7 +54,7 @@
             <td><!-- TMPL_VAR NAME="price" --></td>
             <td><!-- TMPL_VAR NAME="replacementprice" --></td>
             <td><!-- TMPL_VAR NAME="homebranch" --></td>
-            <td><!-- TMPL_VAR NAME="itype" --></td>
+            <td><!-- TMPL_IF NAME="itype_level" --><!-- TMPL_VAR NAME="itype" --><!-- TMPL_ELSE --><!-- TMPL_VAR NAME="itemtype" --><!-- /TMPL_IF --></td>
             <td><!-- TMPL_VAR NAME="holdingbranch"--></td>
             <td><!-- TMPL_VAR NAME="location"--></td>
         </tr>
diff --git a/reports/itemslost.pl b/reports/itemslost.pl
index c0482ab818..406fbbf870 100755
--- a/reports/itemslost.pl
+++ b/reports/itemslost.pl
@@ -56,14 +56,17 @@ if ( $get_items ) {
     my %where;
     $where{'homebranch'}       = $branchfilter    if defined $branchfilter;
     $where{'barcode'}          = $barcodefilter   if defined $barcodefilter;
-    $where{'itype'}            = $itemtypesfilter if defined $itemtypesfilter;
     $where{'authorised_value'} = $loststatusfilter if defined $loststatusfilter;
+    
+    my $itype = C4::Context->preference('item-level_itypes') ? "itype" : "itemtype";
+    $where{$itype}            = $itemtypesfilter if defined $itemtypesfilter;
 
     my $items = GetLostItems( \%where, $orderbyfilter ); 
     $template->param(
-                     total     => scalar @$items,
-                     itemsloop => $items,
-                     get_items => $get_items
+                     total       => scalar @$items,
+                     itemsloop   => $items,
+                     get_items   => $get_items,
+                     itype_level => C4::Context->preference('item-level_itypes'),
                  );
 }
 
-- 
2.39.5