From 3ab463fc344458a71482a09e0c70f4fef1237de2 Mon Sep 17 00:00:00 2001
From: Henri-Damien LAURENT <henridamien.laurent@biblibre.com>
Date: Thu, 20 Aug 2009 22:21:50 +0200
Subject: [PATCH] suggestion.pl has to be updated on C4/Budgets

---
 C4/Suggestions.pm                      | 46 +++++++++++++++-----------
 installer/data/mysql/kohastructure.sql |  2 ++
 installer/data/mysql/updatedatabase.pl | 31 ++++++++++++++++-
 opac/opac-suggestions.pl               |  4 ---
 suggestion/suggestion.pl               |  5 ---
 5 files changed, 59 insertions(+), 29 deletions(-)

diff --git a/C4/Suggestions.pm b/C4/Suggestions.pm
index ba56ed2a9b..df1bec3b72 100644
--- a/C4/Suggestions.pm
+++ b/C4/Suggestions.pm
@@ -114,27 +114,30 @@ Note the status is stored twice :
 sub SearchSuggestion  {
     my ($suggestion)=@_;
     my $dbh = C4::Context->dbh;
-	my @sql_params;
-    my @query =(q{ 
-    SELECT suggestions.*,
-        U1.branchcode   AS branchcodesuggestedby,
-        B1.branchname AS branchnamesuggestedby,
-        U1.surname   AS surnamesuggestedby,
-        U1.firstname AS firstnamesuggestedby,
-        U1.borrowernumber AS borrnumsuggestedby,
+    my @sql_params;
+    my @query = (
+	q{ SELECT suggestions.*,
+	    U1.branchcode   AS branchcodesuggestedby,
+	    B1.branchname   AS branchnamesuggestedby,
+	    U1.surname   AS surnamesuggestedby,
+	    U1.firstname AS firstnamesuggestedby,
+	    U1.email AS emailsuggestedby,
+	    U1.borrowernumber AS borrnumsuggestedby,
         U1.categorycode AS categorycodesuggestedby,
         C1.description AS categorydescriptionsuggestedby,
-        U2.branchcode AS branchcodemanagedby,
-        B2.branchname AS branchnamemanagedby,
-        U2.surname   AS surnamemanagedby,
-        U2.firstname AS firstnamemanagedby,
-        U2.borrowernumber AS borrnummanagedby
-    FROM suggestions
-    LEFT JOIN borrowers AS U1 ON suggestedby=U1.borrowernumber
-    LEFT JOIN borrowers AS U2 ON managedby=U2.borrowernumber
+	    U2.surname   AS surnamemanagedby,
+	    U2.firstname AS firstnamemanagedby,
+	    B2.branchname   AS branchnamesuggestedby,
+	    U2.email AS emailmanagedby,
+	    U2.branchcode AS branchcodemanagedby,
+	    U2.borrowernumber AS borrnummanagedby
+	FROM suggestions
+	LEFT JOIN borrowers AS U1 ON suggestedby=U1.borrowernumber
+	LEFT JOIN branches AS B1 ON B1.branchcode=U1.branchcode
     LEFT JOIN categories AS C1 ON C1.categorycode = U1.categorycode
-    LEFT JOIN branches AS B1 ON B1.branchcode = U1.branchcode
-    LEFT JOIN branches AS B2 ON B2.branchcode = U2.branchcode
+	LEFT JOIN borrowers AS U2 ON managedby=U2.borrowernumber
+	LEFT JOIN branches AS B2 ON B2.branchcode=U2.branchcode
+    LEFT JOIN categories AS C2 ON C2.categorycode = U2.categorycode
 	WHERE STATUS NOT IN ('CLAIMED')
 	} , map {
 	    if ( my $s = $$suggestion{$_} ) {
@@ -172,7 +175,12 @@ sub SearchSuggestion  {
 	$debug && warn "@query";
     my $sth=$dbh->prepare("@query");
     $sth->execute(@sql_params);
-	return ($sth->fetchall_arrayref({}));
+    my @results;
+    while ( my $data=$sth->fetchrow_hashref ){
+        $$data{$$data{STATUS}} = 1;
+        push(@results,$data);
+    }
+    return (\@results);
 }
 
 =head2 GetSuggestion
diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index 4208690c86..e5bc5dc872 100644
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -1699,7 +1699,9 @@ DROP TABLE IF EXISTS `suggestions`;
 CREATE TABLE `suggestions` (
   `suggestionid` int(8) NOT NULL auto_increment,
   `suggestedby` int(11) NOT NULL default 0,
+  `suggesteddate` date NOT NULL default 0,
   `managedby` int(11) default NULL,
+  `manageddate` date default NULL,
   `STATUS` varchar(10) NOT NULL default '',
   `note` mediumtext,
   `author` varchar(80) default NULL,
diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl
index b239c256b5..4af909f0e7 100755
--- a/installer/data/mysql/updatedatabase.pl
+++ b/installer/data/mysql/updatedatabase.pl
@@ -2810,7 +2810,7 @@ ALTER TABLE aqbudget RENAME`aqbudgets`
 BUDGETNAME
     my $maxbudgetid=$dbh->selectcol_arrayref(<<IDsBUDGET);
 SELECT MAX(aqbudgetid) from aqbudgets 
-BUDGETNAME
+IDsBUDGET
 
     $dbh->do(<<BUDGETAUTOINCREMENT);
 ALTER TABLE `aqbudgets` AUTO_INCREMENT=$$maxbudgetid[0]
@@ -2995,6 +2995,35 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
     SetVersion ($DBversion);
 }
 
+$DBversion = "3.01.00.114";
+if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
+    $dbh->do(<<SUGGESTIONS);
+ALTER table suggestions 
+	ADD budgetid INT(11),
+	ADD branchcode VARCHAR(10) default NULL,
+	ADD acceptedby INT(11) default NULL,
+	ADD acceptedon date default NULL,
+	ADD suggestedby INT(11) default NULL,
+	ADD suggestedon date default NULL,
+	ADD managedby INT(11) default NULL,
+	ADD managedon date default NULL,
+	ADD rejectedby INT(11) default NULL,
+	ADD rejectedon date default NULL,
+	ADD collectiontitle text default NULL,
+	ADD itemtype VARCHAR(30) default NULL,
+	ADD biblionumber INT(11) default NULL,
+	ADD sort1 VARCHAR(80) default NULL,
+	ADD sort2 VARCHAR(80) default NULL
+	;
+SUGGESTIONS
+
+    print <<COMMENT;
+Upgrade to $DBversion done  
+	Suggestions
+COMMENT
+    SetVersion ($DBversion);
+}
+
 
 
 =item DropAllForeignKeys($table)
diff --git a/opac/opac-suggestions.pl b/opac/opac-suggestions.pl
index 6f518d7eb0..0a8b2f8e9f 100755
--- a/opac/opac-suggestions.pl
+++ b/opac/opac-suggestions.pl
@@ -108,10 +108,6 @@ $template->param(
 	itemtypeloop=> $supportlist,
     suggestions_loop => $suggestions_loop,
     showall    => $allsuggestions,
-    title            => $title,
-    author           => $author,
-    publishercode    => $publishercode,
-    status           => $status,
     "op_$op"         => 1,
 	  suggestionsview => 1
 );
diff --git a/suggestion/suggestion.pl b/suggestion/suggestion.pl
index c2e3a49fbe..c455e3f786 100755
--- a/suggestion/suggestion.pl
+++ b/suggestion/suggestion.pl
@@ -207,11 +207,6 @@ $template->param(
 ####################
 ## Initializing selection lists
 
-#branch display management
-my @bookfunds = GetBudgets($branchfilter||'');
-map{$_->{'selected'}=1 if ($$suggestion_ref{'bookfundid'} && $_->{'bookfundid'} eq $$suggestion_ref{'bookfundid'})} @bookfunds;
-$template->param( bookfundsloop => \@bookfunds);
-
 #branch display management
 my $onlymine=C4::Context->preference('IndependantBranches') && 
              C4::Context->userenv && 
-- 
2.39.5