From 3bec20f948c71dc71017747ee32ce43ec9564623 Mon Sep 17 00:00:00 2001 From: plg Date: Thu, 13 Apr 2006 08:36:42 +0000 Subject: [PATCH] new: function C4::Date::get_date_format_string_for_DHTMLcalendar based on the system preference prefered date format. improvement: book fund list and budget list screen redesigned. Filters on each field. Columns are not sortable yet. Using DHTML Calendar to fill date fields instead of manual filling. Pagination system. From the book fund list, you can reach the budget list, filtered on a book fund, or not. A budget can be added only from book fund list screen. bug fixed: branchcode was missing in table aqbudget. bug fixed: when setting a branchcode to a book fund, all associated budgets move to this branchcode. modification: when adding/modifying budget/fund, MySQL specific "REPLACE..." statements replaced by standard SQL compliant statement. bug fixed: when adding/modifying a budget, if the book fund is associated to a branch, the branch selection is disabled and set to the book fund branch. --- C4/Date.pm | 20 + admin/aqbookfund.pl | 370 ++++++++----- admin/aqbudget.pl | 490 ++++++++++++------ .../prog/en/admin/aqbookfund.tmpl | 254 ++++++--- .../intranet-tmpl/prog/en/admin/aqbudget.tmpl | 351 ++++++++++--- updater/updatedatabase | 22 + 6 files changed, 1085 insertions(+), 422 deletions(-) diff --git a/C4/Date.pm b/C4/Date.pm index 8c3f72d194..95e60b51fa 100644 --- a/C4/Date.pm +++ b/C4/Date.pm @@ -18,6 +18,7 @@ $VERSION = 0.01; &display_date_format &format_date &format_date_in_iso + get_date_format_string_for_DHTMLcalendar ); @@ -50,6 +51,25 @@ sub display_date_format } } +sub get_date_format_string_for_DHTMLcalendar { + my $dateformat = get_date_format(); + + if ($dateformat eq 'us') { + return '%m/%d/%Y'; + } + elsif ($dateformat eq 'metric') { + return '%d/%m/%Y'; + } + elsif ($dateformat eq "iso") { + return '%Y-%m-%d'; + } + else { + return + 'Invalid date format: '.$dateformat.'.' + .' Please change in system preferences'; + } +} + sub format_date { diff --git a/admin/aqbookfund.pl b/admin/aqbookfund.pl index 6527dd118a..8a52cd92a1 100755 --- a/admin/aqbookfund.pl +++ b/admin/aqbookfund.pl @@ -39,6 +39,9 @@ use strict; use CGI; +use HTML::Template; +use List::Util qw/min/; + use C4::Auth; use C4::Koha; use C4::Context; @@ -47,60 +50,33 @@ use C4::Output; use C4::Interface::CGI::Output; use C4::Search; use C4::Date; -use HTML::Template; - -sub StringSearch { - my ($env,$searchstring,$branches)=@_; - my $dbh = C4::Context->dbh; - $searchstring=~ s/\'/\\\'/g; - my @data=split(' ',$searchstring); - my $count=@data; - my $strsth= "select bookfundid,bookfundname,bookfundgroup,branchcode from aqbookfund where bookfundname like ? "; - if ($branches){ - $strsth.= "AND (aqbookfund.branchcode is null " ; - foreach my $branchcode (keys %$branches){ - $strsth .= "or aqbookfund.branchcode = '".$branchcode."' "; - } - $strsth .= ") "; - } - $strsth.= "order by aqbookfund.bookfundid"; -# warn "chaine de recherche : ".$strsth; - - my $sth=$dbh->prepare($strsth); - $sth->execute("%$data[0]%"); - my @results; - while (my $data=$sth->fetchrow_hashref){ - push(@results,$data); - } - # $sth->execute; - $sth->finish; - return (scalar(@results),\@results); -} my $dbh = C4::Context->dbh; my $input = new CGI; -my $searchfield=$input->param('searchfield') || ''; -my $offset=$input->param('offset'); my $script_name="/cgi-bin/koha/admin/aqbookfund.pl"; my $bookfundid=$input->param('bookfundid'); -my $pagesize=20; +my $pagesize = 10; my $op = $input->param('op') || ''; -$searchfield=~ s/\,//g; my ($template, $borrowernumber, $cookie) - = get_template_and_user({template_name => "admin/aqbookfund.tmpl", - query => $input, - type => "intranet", - authnotrequired => 0, - flagsrequired => {parameters => 1, management => 1}, - debug => 1, - }); + = get_template_and_user( + {template_name => "admin/aqbookfund.tmpl", + query => $input, + type => "intranet", + authnotrequired => 0, + flagsrequired => {parameters => 1, management => 1}, + debug => 1, + } + ); if ($op) { -$template->param(script_name => $script_name, - $op => 1); # we show only the TMPL_VAR names $op -} else { -$template->param(script_name => $script_name, + $template->param( + script_name => $script_name, + $op => 1, + ); # we show only the TMPL_VAR names $op +} +else { + $template->param(script_name => $script_name, else => 1); # we show only the TMPL_VAR names $op } $template->param(action => $script_name); @@ -110,40 +86,25 @@ $template->param(action => $script_name); # my %select_branches; my $branches = getbranches; -my @branchloop; -foreach my $thisbranch (sort keys %$branches) { -# my $selected = 1 if $thisbranch eq $branch; - my %row =(value => $thisbranch, -# selected => $selected, - branchname => $branches->{$thisbranch}->{'branchname'}, - ); - push @branchloop, \%row; -# $select_branches{$thisbranch} = $branches->{$thisbranch}->{'branchname'}; -} - -# my $homebranch=C4::Context->userenv->{branch}; -# 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(branchloop => \@branchloop); ################## ADD_FORM ################################## # called by default. Used to create form to add or modify a record if ($op eq 'add_form') { #---- if primkey exists, it's a modify action, so read values to modify... - my $data; + my $dataaqbookfund; my $header; if ($bookfundid) { - my $sth=$dbh->prepare("select bookfundid,bookfundname,bookfundgroup from aqbookfund where bookfundid=?"); + my $query = ' +SELECT bookfundid, + bookfundname, + bookfundgroup, + branchcode + FROM aqbookfund + WHERE bookfundid = ? +'; + my $sth=$dbh->prepare($query); $sth->execute($bookfundid); - $data=$sth->fetchrow_hashref; + $dataaqbookfund = $sth->fetchrow_hashref; $sth->finish; } if ($bookfundid) { @@ -161,22 +122,91 @@ if ($op eq 'add_form') { } $template->param(add_or_modify => $add_or_modify); $template->param(bookfundid =>$bookfundid); - $template->param(bookfundname =>$data->{'bookfundname'}); + $template->param(bookfundname =>$dataaqbookfund->{'bookfundname'}); + + my @branchloop; + foreach my $branchcode (sort keys %{$branches}) { + my $row = { + branchcode => $branchcode, + branchname => $branches->{$branchcode}->{branchname}, + }; + + if (defined $bookfundid + and defined $dataaqbookfund->{branchcode} + and $dataaqbookfund->{branchcode} eq $branchcode) { + $row->{selected} = 1; + } + + push @branchloop, $row; + } + + $template->param(branches => \@branchloop); + +} # END $OP eq ADD_FORM - # END $OP eq ADD_FORM ################## ADD_VALIDATE ################################## # called by add_form, used to insert/modify data in DB -} elsif ($op eq 'add_validate') { - my $bookfundid=uc($input->param('bookfundid')); - my $sth=$dbh->prepare("delete from aqbookfund where bookfundid =?"); +elsif ($op eq 'add_validate') { + my $bookfundid = uc $input->param('bookfundid'); + + my ($query, $sth); + + $query = ' +SELECT COUNT(*) AS counter + FROM aqbookfund + WHERE bookfundid = ? +'; + $sth=$dbh->prepare($query); $sth->execute($bookfundid); + my $data = $sth->fetchrow_hashref; $sth->finish; - $sth=$dbh->prepare("replace aqbookfund (bookfundid,bookfundname,branchcode) values (?,?,?)"); - $sth->execute($input->param('bookfundid'),$input->param('bookfundname'),$input->param('branchcode')); - $sth->finish; - print "Content-Type: text/html\n\n"; - exit; - + my $bookfund_already_exists = $data->{counter} > 0 ? 1 : 0; + + if ($bookfund_already_exists) { + $query = ' +UPDATE aqbookfund + SET bookfundname = ?, + branchcode = ? + WHERE bookfundid = ? +'; + $sth=$dbh->prepare($query); + $sth->execute( + $input->param('bookfundname'), + $input->param('branchcode') || undef, + $bookfundid, + ); + $sth->finish; + + # budgets depending on a bookfund must have the same branchcode + # if the bookfund branchcode is set + if (defined $input->param('branchcode')) { + $query = ' +UPDATE aqbudget + SET branchcode = ? +'; + $sth=$dbh->prepare($query); + $sth->execute($input->param('branchcode')); + $sth->finish; + } + } + else { + $query = ' +INSERT + INTO aqbookfund + (bookfundid, bookfundname, branchcode) + VALUES + (?, ?, ?) +'; + $sth=$dbh->prepare($query); + $sth->execute( + $bookfundid, + $input->param('bookfundname'), + $input->param('branchcode') || undef, + ); + $sth->finish; + } + + $input->redirect('aqbookfund.pl'); # END $OP eq ADD_VALIDATE ################## DELETE_CONFIRM ################################## # called by default form, used to confirm deletion of data in DB @@ -201,50 +231,140 @@ if ($op eq 'add_form') { # END $OP eq DELETE_CONFIRMED ################## DEFAULT ################################## } else { # DEFAULT - $template->param(scriptname => $script_name); - if ($searchfield ne '') { - $template->param(search => 1); - $template->param(searchfield => $searchfield); - } - my $env; - my ($count,$results)=StringSearch($env,$searchfield,$branches); - my $toggle="white"; - my @loop_data =(); - my $dbh = C4::Context->dbh; - 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'}; -# warn "".$results->[$i]{'bookfundid'}." ".$results->[$i]{'bookfundname'}." ".$results->[$i]{'branchcode'}; - $row_data{branchname} = $branches->{$results->[$i]{'branchcode'}}->{branchname}; - my $strsth2="Select aqbudgetid,startdate,enddate,budgetamount from aqbudget where aqbudget.bookfundid = ?"; -# my $strsth2="Select aqbudgetid,startdate,enddate,budgetamount,branchcode from aqbudget where aqbudget.bookfundid = ?"; -# if ($homebranch){ -# $strsth2 .= " AND ((aqbudget.branchcode is null) OR (aqbudget.branchcode='') OR (aqbudget.branchcode= ".$dbh->quote($homebranch).")) " ; -# } else { -# $strsth2 .= " AND (aqbudget.branchcode='') " if ((C4::Context->userenv) && (C4::Context->userenv->{flags}>1)); -# } - $strsth2 .= " order by aqbudgetid"; -# warn "".$strsth2; - my $sth2 = $dbh->prepare($strsth2); - $sth2->execute($row_data{bookfundid}); - my @budget_loop; -# while (my ($aqbudgetid,$startdate,$enddate,$budgetamount,$branchcode) = $sth2->fetchrow) { - while (my ($aqbudgetid,$startdate,$enddate,$budgetamount) = $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; - } - if ($sth2->rows){ - $row_data{budget} = \@budget_loop; - push @loop_data,\%row_data; - } - } - $template->param(bookfund => \@loop_data); + my ($query, $sth); + + $template->param(scriptname => $script_name); + + # filters + my @branchloop; + foreach my $branchcode (sort keys %{$branches}) { + my $row = { + code => $branchcode, + name => $branches->{$branchcode}->{branchname}, + }; + + if (defined $input->param('filter_branchcode') + and $input->param('filter_branchcode') eq $branchcode) { + $row->{selected} = 1; + } + + push @branchloop, $row; + } + + my @bookfundids_loop; + $query = ' +SELECT bookfundid + FROM aqbookfund +'; + $sth = $dbh->prepare($query); + $sth->execute(); + while (my $row = $sth->fetchrow_hashref) { + if (defined $input->param('filter_bookfundid') + and $input->param('filter_bookfundid') eq $row->{bookfundid}) { + $row->{selected} = 1; + } + + push @bookfundids_loop, $row; + } + $sth->finish; + + $template->param( + filter_bookfundids => \@bookfundids_loop, + filter_branches => \@branchloop, + filter_bookfundname => $input->param('filter_bookfundname') || undef, + ); + + # searching the bookfunds corresponding to our filtering rules + my @bindings; + + $query = ' +SELECT bookfundid, + bookfundname, + bookfundgroup, + branchcode + FROM aqbookfund + WHERE 1 = 1'; + if ($input->param('filter')) { + if ($input->param('filter_bookfundid')) { + $query.= ' + AND bookfundid = ? +'; + push @bindings, $input->param('filter_bookfundid'); + } + if ($input->param('filter_bookfundname')) { + $query.= ' + AND bookfundname like ? +'; + push @bindings, '%'.$input->param('filter_bookfundname').'%'; + } + if ($input->param('filter_branchcode')) { + $query.= ' + AND branchcode = ? +'; + push @bindings, $input->param('filter_branchcode'); + } + } + $query.= ' + ORDER BY bookfundid +'; + + $sth = $dbh->prepare($query); + $sth->execute(@bindings); + my @results; + while (my $row = $sth->fetchrow_hashref) { + push @results, $row; + } + + # does the book funds have budgets? + $query = ' +SELECT bookfundid, + COUNT(*) AS counter + FROM aqbudget + GROUP BY bookfundid +'; + $sth = $dbh->prepare($query); + $sth->execute(); + my %nb_budgets_of; + while (my $row = $sth->fetchrow_hashref) { + $nb_budgets_of{ $row->{bookfundid} } = $row->{counter}; + } + + # pagination informations + my $page = $input->param('page') || 1; + my @loop; + + my $first = ($page - 1) * $pagesize; + + # if we are on the last page, the number of the last word to display + # must not exceed the length of the results array + my $last = min( + $first + $pagesize - 1, + scalar(@results) - 1, + ); + + my $toggle = 0; + foreach my $result (@results[$first .. $last]) { + push( + @loop, + { + %{$result}, + toggle => $toggle++%2, + branchname => + $branches->{ $result->{branchcode} }->{branchname}, + has_budgets => defined $nb_budgets_of{ $result->{bookfundid} }, + } + ); + } + + $template->param( + bookfund => \@loop, + pagination_bar => pagination_bar( + $script_name, + getnbpages(scalar @results, $pagesize), + $page, + 'page' + ) + ); } #---- END $OP eq DEFAULT output_html_with_http_headers $input, $cookie, $template->output; diff --git a/admin/aqbudget.pl b/admin/aqbudget.pl index 36766b6365..d8bfdd97f6 100755 --- a/admin/aqbudget.pl +++ b/admin/aqbudget.pl @@ -39,6 +39,9 @@ use strict; use CGI; +use HTML::Template; +use List::Util qw/min/; + use C4::Date; use C4::Auth; use C4::Acquisition; @@ -46,55 +49,32 @@ use C4::Context; use C4::Output; use C4::Interface::CGI::Output; use C4::Search; -use HTML::Template; - -sub StringSearch { - my ($env,$searchstring,$type)=@_; - my $dbh = C4::Context->dbh; - $searchstring=~ s/\'/\\\'/g; - my @data=split(' ',$searchstring); - my $count=@data; - 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; - while (my $data=$sth->fetchrow_hashref){ - push(@results,$data); - $cnt ++; - } - # $sth->execute; - $sth->finish; - return ($cnt,\@results); -} +use C4::Koha; my $input = new CGI; -my $searchfield=$input->param('searchfield'); -my $offset=$input->param('offset'); my $script_name="/cgi-bin/koha/admin/aqbudget.pl"; my $bookfundid=$input->param('bookfundid'); my $aqbudgetid=$input->param('aqbudgetid'); -my $pagesize=20; +my $pagesize = 20; my $op = $input->param('op'); -$searchfield=~ s/\,//g; my ($template, $borrowernumber, $cookie) - = get_template_and_user({template_name => "admin/aqbudget.tmpl", - query => $input, - type => "intranet", - authnotrequired => 0, - flagsrequired => {parameters => 1}, - debug => 1, - }); - -if ($op) { -$template->param(script_name => $script_name, - $op => 1); # we show only the TMPL_VAR names $op -} else { -$template->param(script_name => $script_name, - else => 1); # we show only the TMPL_VAR names $op -} - -$template->param(action => $script_name); + = get_template_and_user( + {template_name => "admin/aqbudget.tmpl", + query => $input, + type => "intranet", + authnotrequired => 0, + flagsrequired => {parameters => 1}, + debug => 1, + } + ); + +$template->param( + action => $script_name, + DHTMLcalendar_dateformat => get_date_format_string_for_DHTMLcalendar(), + script_name => $script_name, + $op || 'else' => 1, +); my $dbh = C4::Context->dbh; my $sthtemp = $dbh->prepare("Select flags, branchcode from borrowers where borrowernumber = ?"); @@ -104,83 +84,147 @@ 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') { - #---- if primkey exists, it's a modify action, so read values to modify... - my $dataaqbudget; - my $dataaqbookfund; - if ($aqbudgetid) { - my $dbh = C4::Context->dbh; -# my $sth=$dbh->prepare("select aqbudgetid,bookfundname,aqbookfund.bookfundid,aqbookfund.branchcode as bfbranch,startdate,enddate,budgetamount,aqbudget.branchcode from aqbudget,aqbookfund where aqbudgetid=? and aqbudget.bookfundid=aqbookfund.bookfundid"); - my $sth=$dbh->prepare("select aqbudgetid,bookfundname,aqbookfund.bookfundid,aqbookfund.branchcode as bfbranch,startdate,enddate,budgetamount from aqbudget,aqbookfund where aqbudgetid=? and aqbudget.bookfundid=aqbookfund.bookfundid"); - $sth->execute($aqbudgetid); - $dataaqbudget=$sth->fetchrow_hashref; - $sth->finish; - } - if ($bookfundid) { - my $dbh = C4::Context->dbh; - my $sth=$dbh->prepare("select aqbookfund.branchcode, branches.branchname from aqbookfund LEFT JOIN branches ON aqbookfund.branchcode=branches.branchcode where bookfundid=?"); - $sth->execute($bookfundid); - $dataaqbookfund=$sth->fetchrow_hashref; - $sth->finish; - } - my $header; - if ($aqbudgetid) { - $header = "Modify budget"; - } else { - $header = "Add budget"; - } - $template->param(header => $header); - if ($aqbudgetid) { - $template->param(modify => 1); - $template->param(bookfundid => $dataaqbudget->{'bookfundid'}); - $template->param(bookfundname => $dataaqbudget->{'bookfundname'}); - } else { - $template->param(bookfundid => $bookfundid, - adding => 1); - } -# my @select_branch; -# my %select_branches; -# if ($dataaqbookfund->{branchcode}){ -# push @select_branch,$dataaqbookfund->{'branchcode'}; -# $select_branches{$dataaqbookfund->{'branchcode'}}=$dataaqbookfund->{'branchname'}; -# }else { -# my @branches; -# my ($count2,@branches)=branches(); -# push @select_branch,""; -# $select_branches{""}=""; -# 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 => ($dataaqbookfund->{branchcode}?$dataaqbookfund->{branchcode}:$dataaqbudget->{branchcode}), -# -size => 1, -# -multiple => 0 ); -# $template->param(CGIbranch => $CGIbranch); - - $template->param(dateformat => display_date_format(), - aqbudgetid => $dataaqbudget->{'aqbudgetid'}, - startdate => format_date($dataaqbudget->{'startdate'}), - enddate => format_date($dataaqbudget->{'enddate'}), - budgetamount => $dataaqbudget->{'budgetamount'} - ); + my ($query, $dataaqbudget, $dataaqbookfund, $sth); + my $dbh = C4::Context->dbh; + + #---- if primkey exists, it's a modify action, so read values to modify... + if ($aqbudgetid) { + $query = ' +SELECT aqbudgetid, + bookfundname, + aqbookfund.bookfundid, + startdate, + enddate, + budgetamount, + aqbudget.branchcode + FROM aqbudget + INNER JOIN aqbookfund ON aqbudget.bookfundid = aqbookfund.bookfundid + WHERE aqbudgetid = ? +'; + $sth=$dbh->prepare($query); + $sth->execute($aqbudgetid); + $dataaqbudget=$sth->fetchrow_hashref; + $sth->finish; + } + + $query = ' +SELECT aqbookfund.branchcode, + branches.branchname, + aqbookfund.bookfundname + FROM aqbookfund + LEFT JOIN branches ON aqbookfund.branchcode = branches.branchcode + WHERE bookfundid = ? +'; + $sth=$dbh->prepare($query); + $sth->execute( + defined $aqbudgetid ? $dataaqbudget->{bookfundid} : $bookfundid, + ); + $dataaqbookfund=$sth->fetchrow_hashref; + $sth->finish; + + if (defined $aqbudgetid) { + $template->param( + bookfundid => $dataaqbudget->{'bookfundid'}, + bookfundname => $dataaqbudget->{'bookfundname'} + ); + } + else { + $template->param( + bookfundid => $bookfundid, + bookfundname => $dataaqbookfund->{bookfundname}, + ); + } + + # Available branches + my @branches = (); + + $query = ' +SELECT branchcode, + branchname + FROM branches + ORDER BY branchname +'; + $sth=$dbh->prepare($query); + $sth->execute(); + while (my $row = $sth->fetchrow_hashref) { + my $branch = $row; + + if (defined $dataaqbookfund->{branchcode}) { + $branch->{selected} = + $dataaqbookfund->{branchcode} eq $row->{branchcode} ? 1 : 0; + } + elsif (defined $aqbudgetid) { + $branch->{selected} = + $dataaqbudget->{branchcode} eq $row->{branchcode} ? 1 : 0; + } + + push @branches, $branch; + } + $sth->finish; + + $template->param( + dateformat => display_date_format(), + aqbudgetid => $dataaqbudget->{'aqbudgetid'}, + startdate => format_date($dataaqbudget->{'startdate'}), + enddate => format_date($dataaqbudget->{'enddate'}), + budgetamount => $dataaqbudget->{'budgetamount'}, + branches => \@branches, + ); + + if (defined $dataaqbookfund->{branchcode}) { + $template->param( + disable_branchselection => 1, + branch => $dataaqbookfund->{branchcode}, + ); + } # END $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 $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('branchcode') - ); - $sth->finish; - print $input->redirect("aqbookfund.pl"); - exit; + my ($query, $sth); + + if (defined $aqbudgetid) { + $query = ' +UPDATE aqbudget + SET bookfundid = ?, + startdate = ?, + enddate = ?, + budgetamount = ?, + branchcode = ? + WHERE aqbudgetid = ? +'; + $sth=$dbh->prepare($query); + $sth->execute( + $input->param('bookfundid'), + format_date_in_iso($input->param('startdate')), + format_date_in_iso($input->param('enddate')), + $input->param('budgetamount'), + $input->param('branch') || undef, + $aqbudgetid, + ); + $sth->finish; + } + else { + $query = ' +INSERT + INTO aqbudget + (bookfundid, startdate, enddate, budgetamount, branchcode) + VALUES + (?, ?, ?, ?, ?) +'; + $sth=$dbh->prepare($query); + $sth->execute( + $input->param('bookfundid'), + format_date_in_iso($input->param('startdate')), + format_date_in_iso($input->param('enddate')), + $input->param('budgetamount'), + $input->param('branch') || undef, + ); + $sth->finish; + } + + $input->redirect("aqbudget.pl"); + # END $OP eq ADD_VALIDATE ################## DELETE_CONFIRM ################################## # called by default form, used to confirm deletion of data in DB @@ -209,53 +253,177 @@ if ($op eq 'add_form') { # END $OP eq DELETE_CONFIRMED ################## DEFAULT ################################## } else { # DEFAULT - if ($searchfield ne '') { - $template->param(search => 1); - $template->param(searchfield => $searchfield); - } - my $env; - my ($count,$results)=StringSearch($env,$searchfield,'web'); - my $toggle="white"; - my @loop_data =(); - for (my $i=$offset; $i < ($offset+$pagesize<$count?$offset+$pagesize:$count); $i++){ - #find out stats - # my ($od,$issue,$fines)=categdata2($env,$results->[$i]{'borrowernumber'}); - # $fines=$fines+0; - my $dataaqbookfund; - my $dbh = C4::Context->dbh; - my $sth=$dbh->prepare("select bookfundid,bookfundname from aqbookfund where bookfundid=?"); - $sth->execute($results->[$i]{'bookfundid'}); - $dataaqbookfund=$sth->fetchrow_hashref; - $sth->finish; - my @toggle = (); - my @bookfundid = (); - my @bookfundname = (); - my @startdate = (); - my @enddate = (); - my @budgetamount = (); - push(@toggle,$toggle); - push(@bookfundid,$results->[$i]{'bookfundid'}); - push(@bookfundname,$dataaqbookfund->{'bookfundname'}); - push(@startdate,format_date($results->[$i]{'startdate'})); - push(@enddate,format_date($results->[$i]{'enddate'})); - push(@budgetamount,$results->[$i]{'budgetamount'}); - if ($toggle eq 'white'){ - $toggle="#ffffcc"; - } else { - $toggle="white"; - } - while (@toggle and @bookfundid and @bookfundname and @startdate and @enddate and @budgetamount) { - my %row_data; - $row_data{toggle} = shift @toggle; - $row_data{bookfundid} = shift @bookfundid; - $row_data{bookfundname} = shift @bookfundname; - $row_data{startdate} = shift @startdate; - $row_data{enddate} = shift @enddate; - $row_data{budgetamount} = shift @budgetamount; - push(@loop_data, \%row_data); - } - } - $template->param(budget => \@loop_data); + my ($query, $sth); + + # create a look-up table for bookfund names from bookfund ids, + # instead of having on query per budget + my %bookfundname_of = (); + $query = ' +SELECT bookfundid, bookfundname + FROM aqbookfund +'; + $sth=$dbh->prepare($query); + $sth->execute; + while (my $row = $sth->fetchrow_hashref) { + $bookfundname_of{ $row->{bookfundid} } = $row->{bookfundname}; + } + $sth->finish; + + # filters + my $branches = getbranches(); + my @branchloop; + foreach my $branchcode (sort keys %{$branches}) { + my $row = { + code => $branchcode, + name => $branches->{$branchcode}->{branchname}, + }; + + if (defined $input->param('filter_branchcode') + and $input->param('filter_branchcode') eq $branchcode) { + $row->{selected} = 1; + } + + push @branchloop, $row; + } + + my @bookfundids_loop; + $query = ' +SELECT bookfundid + FROM aqbookfund +'; + $sth = $dbh->prepare($query); + $sth->execute(); + while (my $row = $sth->fetchrow_hashref) { + if (defined $input->param('filter_bookfundid') + and $input->param('filter_bookfundid') eq $row->{bookfundid}) { + $row->{selected} = 1; + } + + push @bookfundids_loop, $row; + } + $sth->finish; + + $template->param( + filter_bookfundids => \@bookfundids_loop, + filter_branches => \@branchloop, + filter_amount => $input->param('filter_amount') || undef, + filter_startdate => $input->param('filter_startdate') || undef, + filter_enddate => $input->param('filter_enddate') || undef, + ); + + my %sign_label_of = ( + '=' => 'equal', + '>=' => 'superior', + '<=' => 'inferior', + ); + + foreach my $field (qw/startdate enddate amount/) { + my $param = 'filter_'.$field.'_sign'; + + foreach my $sign (keys %sign_label_of) { + if ($input->param($param) eq $sign) { + $template->param( + $param.'_'.$sign_label_of{$sign}.'_selected' => 1, + ); + } + } + } + + # Search all available budgets + $query = ' +SELECT aqbudgetid, + bookfundid, + startdate, + enddate, + budgetamount, + branchcode + FROM aqbudget + WHERE 1 = 1'; + + my @bindings; + + if ($input->param('filter_bookfundid')) { + $query.= ' + AND bookfundid = ? +'; + push @bindings, $input->param('filter_bookfundid'); + } + if ($input->param('filter_branchcode')) { + $query.= ' + AND branchcode = ? +'; + push @bindings, $input->param('filter_branchcode'); + } + if ($input->param('filter_startdate')) { + $query.= ' + AND startdate '.$input->param('filter_startdate_sign').' ? +'; + push @bindings, format_date_in_iso($input->param('filter_startdate')); + } + if ($input->param('filter_enddate')) { + $query.= ' + AND enddate '.$input->param('filter_enddate_sign').' ? +'; + push @bindings, format_date_in_iso($input->param('filter_enddate')); + } + if ($input->param('filter_amount')) { + $query.= ' + AND budgetamount '.$input->param('filter_amount_sign').' ? +'; + # the amount must be a quantity, with 2 digits after the decimal + # separator + $input->param('filter_amount') =~ m{(\d* (?:\.\d{,2})? )}xms; + my ($amount) = $1; + push @bindings, $amount; + } + + $query.= ' + ORDER BY bookfundid, aqbudgetid +'; + $sth = $dbh->prepare($query); + $sth->execute(@bindings); + my @results; + while (my $row = $sth->fetchrow_hashref){ + push @results, $row; + } + $sth->finish; + + # filter budgets depending on the pagination + my $page = $input->param('page') || 1; + my $first = ($page - 1) * $pagesize; + + # if we are on the last page, the number of the last word to display + # must not exceed the length of the results array + my $last = min( + $first + $pagesize - 1, + scalar @results - 1, + ); + + my $toggle = 0; + my @loop; + foreach my $result (@results[$first .. $last]) { + push( + @loop, + { + %{$result}, + toggle => $toggle++%2, + bookfundname => $bookfundname_of{ $result->{'bookfundid'} }, + branchname => $branches->{ $result->{branchcode} }->{branchname}, + startdate => format_date($result->{startdate}), + enddate => format_date($result->{enddate}), + } + ); + } + + $template->param( + budget => \@loop, + pagination_bar => pagination_bar( + $script_name, + getnbpages(scalar @results, $pagesize), + $page, + 'page' + ) + ); } #---- END $OP eq DEFAULT output_html_with_http_headers $input, $cookie, $template->output; diff --git a/koha-tmpl/intranet-tmpl/prog/en/admin/aqbookfund.tmpl b/koha-tmpl/intranet-tmpl/prog/en/admin/aqbookfund.tmpl index a2e60b3f3c..3e1a3e3ee3 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/admin/aqbookfund.tmpl +++ b/koha-tmpl/intranet-tmpl/prog/en/admin/aqbookfund.tmpl @@ -1,59 +1,98 @@ -Koha -- System Administration: Book Funds - - + +Koha -- System Administration: Book Funds + - -Data Recorded -Confirm Deletion of Fund '' -Data Deleted -

Book Fund Administration

-
-
- - You Searched for - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Book FundNameEditDelete
?op=add_form&bookfundid= ">
" method="get">" />
" method="get">" />
 Budget
" />
  - - : -
" />
" />
-
-

-
- + + Code + Name + Branch + Actions + + +
" method="post"> + + + + + + " /> + + + + + + + + +
+ +
+ + + + + + + + + + + + + + + + + None + + + + ?op=add_form&bookfundid=">Edit + ?op=delete_confirm&bookfundid=">Delete + ">Add budget + + ">Show budgets + + + + + + + +
+ + +

+ Add bookfund + Show all budgets +

+ +
+ + +
" name="Aform" method="post"> - - + + - - - - - - - - -
Book fund" />
Book fund
Name" />
+ + + + + + + + + + + + + + + + + + + + + + + + + +
Book fund + " /> + +
Book fund + +
Name + " + /> +
Branch +
-

" onclick="Check(this.form); return false;" /> '; return false;" />

+

+ + +

- +

Data Recorded

-
" method="post"> - -
+ +

+ ">Return to fund list +

Confirm Deletion of Fund

- - -
Book fund
Name
Group
" method="post">" />
" method="post">
- + + Book fund + + + + Name + + + + Group + + + + +
" method="post"> + + " /> + +
+ +
+
+ +
" method="post"> + +
+

Data Deleted

" method="post"> -
- - + + + diff --git a/koha-tmpl/intranet-tmpl/prog/en/admin/aqbudget.tmpl b/koha-tmpl/intranet-tmpl/prog/en/admin/aqbudget.tmpl index 8a31b2020b..23371440dd 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/admin/aqbudget.tmpl +++ b/koha-tmpl/intranet-tmpl/prog/en/admin/aqbudget.tmpl @@ -1,36 +1,179 @@ -Koha -- System Administration: Budget admin + +Koha -- System Administration: Budgets + + + +/includes/calendar/calendar-system.css"> + + + + - -Budget Data Recorded -Delete Book Fund '' -Book fund deleted - + -

Budget admin

-
- - You Searched for - +

Budget admin

+ - - - - - - - - - - - -
Book fundStart dateEnd dateBudget amount 
()">Edit">Delete
-
-

- + + Book fund + Branch + Start date + End date + Budget amount + Actions + + +
+ + + + + + + + + + " /> + /includes/calendar/cal.gif" id="filter_startdate_button" /> + + + + + " /> + /includes/calendar/cal.gif" id="filter_enddate_button" /> + + + + + " size="8"/> + + + + + +
+ + + + + + + + + + + + + + ">Edit + ">Delete + + + + + +
+ +
- - -

+
+ Add budget + + - - - - - +
Book fund" />
Book fund name
+ + + + - - - + + + + + + + + + + + + + + + - - -
Book fund + " /> + +
" />
+ + + " /> + /includes/calendar/cal.gif" id="startdate_button" /> + +
+ + + " /> + /includes/calendar/cal.gif" id="enddate_button" /> + +
+ + + " size="8" /> +
+ + + + + + " /> + + -
" /> 
" />
" />
-" />" onclick="Check(this.form)" /> - + + + + + + + + + + + + - -

Budget Data Recorded

-
" method="post"> - -
- + + " /> + + +

+ + +

+
+ + -

Delete Book Budget?

- - - - -
Start date
End date
Budget Amount
+
" method="post"> +
+ Delete Book Budget? -" method="post">" />
" method="post">
- + + + + + + + + + + + + + + + +
Start date
End date
Budget Amount
- -

Budget deleted

-
" method="post"> - + + " /> + +

+ + +

+
diff --git a/updater/updatedatabase b/updater/updatedatabase index b5e4a4f2c8..5f6acefc92 100755 --- a/updater/updatedatabase +++ b/updater/updatedatabase @@ -141,6 +141,7 @@ my %requirefields = ( subscription => { 'letter' => 'char(20) NULL', 'distributedto' => 'text NULL'}, itemtypes => { 'imageurl' => 'char(200) NULL'}, aqbookfund => { 'branchcode' => 'varchar(4) NULL'}, + aqbudget => { 'branchcode' => 'varchar(4) NULL'}, # tablename => { 'field' => 'fieldtype' }, ); @@ -1285,6 +1286,27 @@ sub MARCgetitem { exit; # $Log$ +# Revision 1.133 2006/04/13 08:36:42 plg +# new: function C4::Date::get_date_format_string_for_DHTMLcalendar based on +# the system preference prefered date format. +# +# improvement: book fund list and budget list screen redesigned. Filters on +# each field. Columns are not sortable yet. Using DHTML Calendar to fill date +# fields instead of manual filling. Pagination system. From the book fund +# list, you can reach the budget list, filtered on a book fund, or not. A +# budget can be added only from book fund list screen. +# +# bug fixed: branchcode was missing in table aqbudget. +# +# bug fixed: when setting a branchcode to a book fund, all associated budgets +# move to this branchcode. +# +# modification: when adding/modifying budget/fund, MySQL specific "REPLACE..." +# statements replaced by standard SQL compliant statement. +# +# bug fixed: when adding/modifying a budget, if the book fund is associated to +# a branch, the branch selection is disabled and set to the book fund branch. +# # Revision 1.132 2006/04/06 12:37:05 hdl # Bugfixing : aqbookfund needed a field. # -- 2.20.1