From b734534b18fbede703f55f620552284867c3e46f Mon Sep 17 00:00:00 2001 From: hdl Date: Wed, 6 Jul 2005 17:47:22 +0000 Subject: [PATCH] Add branch selection and filter for budgets and funds. May be too much... Funds could be enough.... It works provided you update your base adding branchcode both in aqbookfund AND aqbudget. If selection in budget is not needed, I shall remove. --- admin/aqbookfund.pl | 60 ++++++++++++++++++---- admin/aqbudget.pl | 44 ++++++++++++++-- .../default/en/parameters/aqbookfund.tmpl | 21 +++++++- .../default/en/parameters/aqbudget.tmpl | 5 ++ updater/updatedatabase | 26 +++++++++- 5 files changed, 140 insertions(+), 16 deletions(-) diff --git a/admin/aqbookfund.pl b/admin/aqbookfund.pl index c64dc4823d..b5d15f93f7 100755 --- a/admin/aqbookfund.pl +++ b/admin/aqbookfund.pl @@ -41,6 +41,7 @@ use strict; use CGI; use C4::Auth; use C4::Context; +use C4::Acquisition; use C4::Output; use C4::Interface::CGI::Output; use C4::Search; @@ -64,6 +65,7 @@ sub StringSearch { return (scalar(@results),\@results); } +my $dbh = C4::Context->dbh; my $input = new CGI; my $searchfield=$input->param('searchfield'); my $offset=$input->param('offset'); @@ -91,6 +93,39 @@ $template->param(script_name => $script_name, } $template->param(action => $script_name); +my @branches; +my @select_branch; +my %select_branches; +my ($count2,@branches)=branches(); + +push @select_branch,""; +$select_branches{""}=""; + +my $sthtemp = $dbh->prepare("Select flags, branchcode from borrowers where borrowernumber = ?"); +$sthtemp->execute($borrowernumber); +my ($flags, $homebranch)=$sthtemp->fetchrow; +if ($flags>1){ + if ($homebranch){ + push @select_branch, $homebranch;# + for (my $i=0;$i<$count2;$i++){ + $select_branches{$branches[$i]->{'branchcode'}} = $branches[$i]->{'branchname'} if ($branches[$i]->{'branchcode'} eq $homebranch); + } + } +} else { + for (my $i=0;$i<$count2;$i++){ + push @select_branch, $branches[$i]->{'branchcode'};# + $select_branches{$branches[$i]->{'branchcode'}} = $branches[$i]->{'branchname'}; + } +} +my $CGIbranch=CGI::scrolling_list( -name => 'branchcode', + -values => \@select_branch, + -labels => \%select_branches, + -size => 1, + -multiple => 0 ); +$template->param(CGIbranch => $CGIbranch); + +warn "bornum=".$borrowernumber . "flags = ".$flags. " homebranch= ".$homebranch; +$sthtemp->finish; ################## ADD_FORM ################################## # called by default. Used to create form to add or modify a record @@ -99,7 +134,6 @@ if ($op eq 'add_form') { my $data; my $header; if ($bookfundid) { - my $dbh = C4::Context->dbh; my $sth=$dbh->prepare("select bookfundid,bookfundname,bookfundgroup from aqbookfund where bookfundid=?"); $sth->execute($bookfundid); $data=$sth->fetchrow_hashref; @@ -126,7 +160,6 @@ if ($op eq 'add_form') { ################## ADD_VALIDATE ################################## # called by add_form, used to insert/modify data in DB } elsif ($op eq 'add_validate') { - my $dbh = C4::Context->dbh; my $bookfundid=uc($input->param('bookfundid')); my $sth=$dbh->prepare("delete from aqbookfund where bookfundid =?"); $sth->execute($bookfundid); @@ -141,7 +174,6 @@ if ($op eq 'add_form') { ################## DELETE_CONFIRM ################################## # called by default form, used to confirm deletion of data in DB } elsif ($op eq 'delete_confirm') { - my $dbh = C4::Context->dbh; my $sth=$dbh->prepare("select bookfundid,bookfundname,bookfundgroup from aqbookfund where bookfundid=?"); $sth->execute($bookfundid); my $data=$sth->fetchrow_hashref; @@ -152,7 +184,6 @@ if ($op eq 'add_form') { ################## DELETE_CONFIRMED ################################## # called by delete_confirm, used to effectively confirm deletion of data in DB } elsif ($op eq 'delete_confirmed') { - my $dbh = C4::Context->dbh; my $bookfundid=uc($input->param('bookfundid')); my $sth=$dbh->prepare("delete from aqbookfund where bookfundid=?"); $sth->execute($bookfundid); @@ -172,24 +203,35 @@ if ($op eq 'add_form') { my ($count,$results)=StringSearch($env,$searchfield,'web'); my $toggle="white"; my @loop_data =(); - my $dbh = C4::Context->dbh; - my $sth2 = $dbh->prepare("Select aqbudgetid,startdate,enddate,budgetamount from aqbudget where bookfundid = ? order by bookfundid"); + my $strsth2="Select aqbudgetid,startdate,enddate,budgetamount,aqbudget.branchcode from aqbudget where bookfundid = ? "; + if ($homebranch){ + $strsth2 .= "AND ((aqbudget.branchcode='') OR (aqbudget.branchcode= ".$dbh->quote($homebranch)."))" ; + } else { + $strsth2 .= "AND (aqbudget.branchcode='') " if ($flags>1); + } + $strsth2 .= "order by bookfundid"; + warn "".$strsth2; + my $sth2 = $dbh->prepare($strsth2); for (my $i=$offset; $i < ($offset+$pagesize<$count?$offset+$pagesize:$count); $i++){ my %row_data; $row_data{bookfundid} =$results->[$i]{'bookfundid'}; $row_data{bookfundname} = $results->[$i]{'bookfundname'}; + $row_data{branchname} = $select_branches{$results->[$i]{'branchcode'}}; $sth2->execute($row_data{bookfundid}); my @budget_loop; - while (my ($aqbudgetid,$startdate,$enddate,$budgetamount) = $sth2->fetchrow) { + while (my ($aqbudgetid,$startdate,$enddate,$budgetamount,$branchcode) = $sth2->fetchrow) { my %budgetrow_data; $budgetrow_data{aqbudgetid} = $aqbudgetid; $budgetrow_data{startdate} = format_date($startdate); $budgetrow_data{enddate} = format_date($enddate); $budgetrow_data{budgetamount} = $budgetamount; + $budgetrow_data{branchcode} = $branchcode; push @budget_loop,\%budgetrow_data; } - $row_data{budget} = \@budget_loop; - push @loop_data,\%row_data; + if ($sth2->rows){ + $row_data{budget} = \@budget_loop; + push @loop_data,\%row_data; + } } $template->param(bookfund => \@loop_data); } #---- END $OP eq DEFAULT diff --git a/admin/aqbudget.pl b/admin/aqbudget.pl index 8d8583eb3a..008d1b1176 100755 --- a/admin/aqbudget.pl +++ b/admin/aqbudget.pl @@ -41,6 +41,7 @@ use strict; use CGI; use C4::Date; use C4::Auth; +use C4::Acquisition; use C4::Context; use C4::Output; use C4::Interface::CGI::Output; @@ -53,7 +54,7 @@ sub StringSearch { $searchstring=~ s/\'/\\\'/g; my @data=split(' ',$searchstring); my $count=@data; - my $sth=$dbh->prepare("Select aqbudgetid,bookfundid,startdate,enddate,budgetamount from aqbudget where (bookfundid like ?) order by bookfundid,aqbudgetid"); + my $sth=$dbh->prepare("Select aqbudgetid,bookfundid,startdate,enddate,budgetamount,branchcode from aqbudget where (bookfundid like ?) order by bookfundid,aqbudgetid"); $sth->execute("$data[0]%"); my @results; my $cnt=0; @@ -94,6 +95,12 @@ $template->param(script_name => $script_name, } $template->param(action => $script_name); + +my $dbh = C4::Context->dbh; +my $sthtemp = $dbh->prepare("Select flags, branchcode from borrowers where borrowernumber = ?"); +$sthtemp->execute($borrowernumber); +my ($flags, $homebranch)=$sthtemp->fetchrow; + ################## ADD_FORM ################################## # called by default. Used to create form to add or modify a record if ($op eq 'add_form') { @@ -102,7 +109,7 @@ if ($op eq 'add_form') { my $dataaqbookfund; if ($aqbudgetid) { my $dbh = C4::Context->dbh; - my $sth=$dbh->prepare("select aqbudgetid,bookfundname,aqbookfund.bookfundid,startdate,enddate,budgetamount from aqbudget,aqbookfund where aqbudgetid=? and aqbudget.bookfundid=aqbookfund.bookfundid"); + my $sth=$dbh->prepare("select aqbudgetid,bookfundname,aqbookfund.bookfundid,startdate,enddate,budgetamount,aqbudget.branchcode from aqbudget,aqbookfund where aqbudgetid=? and aqbudget.bookfundid=aqbookfund.bookfundid"); $sth->execute($aqbudgetid); $dataaqbudget=$sth->fetchrow_hashref; $sth->finish; @@ -122,6 +129,32 @@ if ($op eq 'add_form') { $template->param(bookfundid => $bookfundid, adding => 1); } + my @branches; + my @select_branch; + my %select_branches; + my ($count2,@branches)=branches(); + push @select_branch,""; + $select_branches{""}=""; + if ($flags>1){ + if ($homebranch){ + push @select_branch, $homebranch;# + for (my $i=0;$i<$count2;$i++){ + $select_branches{$branches[$i]->{'branchcode'}} = $branches[$i]->{'branchname'} if ($branches[$i]->{'branchcode'} eq $homebranch); + } + } + } else { + for (my $i=0;$i<$count2;$i++){ + push @select_branch, $branches[$i]->{'branchcode'};# + $select_branches{$branches[$i]->{'branchcode'}} = $branches[$i]->{'branchname'}; + } + } + my $CGIbranch=CGI::scrolling_list( -name => 'branchcode', + -values => \@select_branch, + -labels => \%select_branches, + -default => $dataaqbudget->{branchcode}, + -size => 1, + -multiple => 0 ); + $template->param(CGIbranch => $CGIbranch); $template->param(dateformat => display_date_format(), aqbudgetid => $dataaqbudget->{'aqbudgetid'}, startdate => format_date($dataaqbudget->{'startdate'}), @@ -133,11 +166,12 @@ if ($op eq 'add_form') { # called by add_form, used to insert/modify data in DB } elsif ($op eq 'add_validate') { my $dbh = C4::Context->dbh; - my $sth=$dbh->prepare("replace aqbudget (aqbudgetid,bookfundid,startdate,enddate,budgetamount) values (?,?,?,?,?)"); + my $sth=$dbh->prepare("replace aqbudget (aqbudgetid,bookfundid,startdate,enddate,budgetamount,branchcode) values (?,?,?,?,?,?)"); $sth->execute($input->param('aqbudgetid'),$input->param('bookfundid'), format_date_in_iso($input->param('startdate')), format_date_in_iso($input->param('enddate')), - $input->param('budgetamount') + $input->param('budgetamount'), + $input->param('branchcode') ); $sth->finish; print $input->redirect("aqbookfund.pl"); @@ -147,7 +181,7 @@ if ($op eq 'add_form') { # called by default form, used to confirm deletion of data in DB } elsif ($op eq 'delete_confirm') { my $dbh = C4::Context->dbh; - my $sth=$dbh->prepare("select aqbudgetid,bookfundid,startdate,enddate,budgetamount from aqbudget where aqbudgetid=?"); + my $sth=$dbh->prepare("select aqbudgetid,bookfundid,startdate,enddate,budgetamount,branchcode from aqbudget where aqbudgetid=?"); $sth->execute($aqbudgetid); my $data=$sth->fetchrow_hashref; $sth->finish; diff --git a/koha-tmpl/intranet-tmpl/default/en/parameters/aqbookfund.tmpl b/koha-tmpl/intranet-tmpl/default/en/parameters/aqbookfund.tmpl index a23db9aa3d..c8384b7e97 100644 --- a/koha-tmpl/intranet-tmpl/default/en/parameters/aqbookfund.tmpl +++ b/koha-tmpl/intranet-tmpl/default/en/parameters/aqbookfund.tmpl @@ -11,6 +11,7 @@ Book fund Name + Branch Edit Delete @@ -18,6 +19,13 @@ + + + + +   + + ?op=add_form&bookfundid= "> //images/fileopen.png" alt="Edit" title="Edit" width="32" hspace="0" vspace="0" border="0"> @@ -26,6 +34,7 @@   Budget + Branch " class="button"> Add budget @@ -36,7 +45,13 @@   - - : + - : + + + + +   + "> @@ -138,6 +153,10 @@ "> 

+   +
Select one if budget is branch specific, otherwise, leave it blank. +

+

diff --git a/koha-tmpl/intranet-tmpl/default/en/parameters/aqbudget.tmpl b/koha-tmpl/intranet-tmpl/default/en/parameters/aqbudget.tmpl index 742fc2c18e..3544bc63e3 100644 --- a/koha-tmpl/intranet-tmpl/default/en/parameters/aqbudget.tmpl +++ b/koha-tmpl/intranet-tmpl/default/en/parameters/aqbudget.tmpl @@ -125,6 +125,11 @@

">

+

+ +
If budget is specifically allowed to a branch, select it here, otherwise leave it empty . +

+

">

diff --git a/updater/updatedatabase b/updater/updatedatabase index 247f1f0eea..aae26b476d 100755 --- a/updater/updatedatabase +++ b/updater/updatedatabase @@ -418,7 +418,8 @@ my %requirefields = ( aqorders => { 'budgetdate' => 'date', 'sort1' => 'char(80)', 'sort2' => 'char(80)', }, - aqbudget => {'aqbudgetid' => 'tinyint(4) auto_increment primary key'}, + aqbookfund =>{'branchcode'=> 'varchar(4) NULL'}, + aqbudget => {'aqbudgetid' => 'tinyint(4) auto_increment primary key', 'branchcode'=> 'varchar(4) NULL'}, items => {'paidfor' => 'text', 'location' => 'char(80)'}, #added so that reference items are not available for reserves... @@ -922,6 +923,14 @@ my %fielddefinitions = ( key => 'PRI', default => '' }, + { + field => 'branchcode', + type => 'varchar(4)', + null => 'NULL', + key => '', + default =>'', + extra => '' + }, ], aqbudget => [ { @@ -932,6 +941,14 @@ my %fielddefinitions = ( default =>'', extra => 'auto_increment' }, + { + field => 'branchcode', + type => 'varchar(4)', + null => 'NULL', + key => '', + default =>'', + extra => '' + }, ], z3950servers => [ { @@ -1485,6 +1502,13 @@ $sth->finish; exit; # $Log$ +# Revision 1.106 2005/07/06 17:47:22 hdl +# Add branch selection and filter for budgets and funds. +# May be too much... Funds could be enough.... +# +# It works provided you update your base adding branchcode both in aqbookfund AND aqbudget. +# If selection in budget is not needed, I shall remove. +# # Revision 1.105 2005/06/20 14:36:44 tipaul # synch'ing 2.2 and head # -- 2.11.0