admin/aqbudget.pl

   1 #!/usr/bin/perl
   2
   3 #script to administer the aqbudget table
   4 #written 20/02/2002 by paul.poulain@free.fr
   5 # This software is placed under the gnu General Public License, v2 (http://www.gnu.org/licenses/gpl.html)
   6
   7 # ALGO :
   8 # this script use an $op to know what to do.
   9 # if $op is empty or none of the above values,
  10 #       - the default screen is build (with all records, or filtered datas).
  11 #       - the   user can clic on add, modify or delete record.
  12 # if $op=add_form
  13 #       - if primkey exists, this is a modification,so we read the $primkey record
  14 #       - builds the add/modify form
  15 # if $op=add_validate
  16 #       - the user has just send datas, so we create/modify the record
  17 # if $op=delete_form
  18 #       - we show the record having primkey=$primkey and ask for deletion validation form
  19 # if $op=delete_confirm
  20 #       - we delete the record having primkey=$primkey
  21
  22
  23 # Copyright 2000-2002 Katipo Communications
  24 #
  25 # This file is part of Koha.
  26 #
  27 # Koha is free software; you can redistribute it and/or modify it under the
  28 # terms of the GNU General Public License as published by the Free Software
  29 # Foundation; either version 2 of the License, or (at your option) any later
  30 # version.
  31 #
  32 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
  33 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
  34 # A PARTICULAR PURPOSE.  See the GNU General Public License for more details.
  35 #
  36 # You should have received a copy of the GNU General Public License along with
  37 # Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
  38 # Suite 330, Boston, MA  02111-1307 USA
  39
  40 use strict;
  41 use CGI;
  42 use C4::Branch; # GetBranches
  43 use List::Util qw/min/;
  44 use C4::Dates qw/format_date format_date_in_iso/;
  45 use C4::Auth;
  46 use C4::Acquisition;
  47 use C4::Context;
  48 use C4::Output;
  49 use C4::Koha;
  50
  51 my $input = new CGI;
  52 my $script_name="/cgi-bin/koha/admin/aqbudget.pl";
  53 my $bookfundid   = $input->param('bookfundid');
  54 my $aqbudgetid   = $input->param('aqbudgetid');
  55 my $branchcodeid = $input->param('branchcode');
  56 my $pagesize = 20;
  57 my $op = $input->param('op');
  58
  59 my ($template, $borrowernumber, $cookie)
  60     = get_template_and_user(
  61         {template_name => "admin/aqbudget.tmpl",
  62          query => $input,
  63          type => "intranet",
  64          authnotrequired => 0,
  65          flagsrequired => {parameters => 1},
  66          debug => 1,
  67      }
  68     );
  69
  70 $template->param(
  71     action => $script_name,
  72     DHTMLcalendar_dateformat => C4::Dates->DHTMLcalendar(),
  73     script_name => $script_name,
  74     $op || 'else' => 1,
  75 );
  76
  77 my $dbh = C4::Context->dbh;
  78 my $sthtemp = $dbh->prepare("Select flags, branchcode from borrowers where borrowernumber = ?");
  79 $sthtemp->execute($borrowernumber);
  80 my ($flags, $homebranch)=$sthtemp->fetchrow;
  81
  82 ################## ADD_FORM ##################################
  83 # called by default. Used to create form to add or  modify a record
  84 if ($op eq 'add_form') {
  85     my ($query, $dataaqbudget, $dataaqbookfund, $sth);
  86     my $dbh = C4::Context->dbh;
  87
  88     #---- if primkey exists, it's a modify action, so read values to modify...
  89     if ($aqbudgetid) {
  90         $query = '
  91 SELECT aqbudgetid,
  92        bookfundname,
  93        aqbookfund.bookfundid,
  94        startdate,
  95        enddate,
  96        budgetamount,
  97        aqbudget.branchcode
  98   FROM aqbudget
  99     INNER JOIN aqbookfund ON (aqbudget.bookfundid = aqbookfund.bookfundid)
 100   WHERE aqbudgetid = ? AND
 101        (aqbookfund.branchcode = aqbudget.branchcode  OR
 102         (aqbudget.branchcode IS NULL and aqbookfund.branchcode=""))
 103 ';
 104         $sth=$dbh->prepare($query);
 105         $sth->execute($aqbudgetid);
 106         $dataaqbudget=$sth->fetchrow_hashref;
 107         $sth->finish;
 108     }
 109
 110     $query = '
 111 SELECT aqbookfund.branchcode,
 112        branches.branchname,
 113        aqbookfund.bookfundname
 114   FROM aqbookfund
 115     LEFT JOIN branches ON aqbookfund.branchcode = branches.branchcode
 116   WHERE bookfundid = ? AND aqbookfund.branchcode=?
 117 ';
 118     $sth=$dbh->prepare($query);
 119     $sth->execute(
 120         defined $aqbudgetid ? $dataaqbudget->{bookfundid} : $bookfundid,
 121         $branchcodeid
 122     );
 123     $dataaqbookfund=$sth->fetchrow_hashref;
 124     $sth->finish;
 125
 126     if (defined $aqbudgetid) {
 127         $template->param(
 128             bookfundid => $dataaqbudget->{'bookfundid'},
 129             branchcode => $dataaqbudget->{'branchcode'},
 130             bookfundname => $dataaqbudget->{'bookfundname'}
 131         );
 132     }
 133     else {
 134         $template->param(
 135             bookfundid => $bookfundid,
 136             branchcode => $dataaqbookfund->{'branchcode'},
 137             bookfundname => $dataaqbookfund->{bookfundname},
 138         );
 139     }
 140
 141     # Available branches
 142     my @branches = ();
 143
 144     $query = '
 145 SELECT branchcode,
 146        branchname
 147   FROM branches
 148   ORDER BY branchname
 149 ';
 150     $sth=$dbh->prepare($query);
 151     $sth->execute();
 152     while (my $row = $sth->fetchrow_hashref) {
 153         my $branch = $row;
 154
 155         if (defined $dataaqbookfund->{branchcode}) {
 156             $branch->{selected} =
 157                 $dataaqbookfund->{branchcode} eq $row->{branchcode} ? 1 : 0;
 158         }
 159         elsif (defined $aqbudgetid) {
 160             $branch->{selected} =
 161                 $dataaqbudget->{branchcode} eq $row->{branchcode} ? 1 : 0;
 162         }
 163
 164         push @branches, $branch;
 165     }
 166     $sth->finish;
 167
 168     $template->param(
 169         dateformat => C4::Dates->new()->visual(),
 170         aqbudgetid => $dataaqbudget->{'aqbudgetid'},
 171         startdate => $dataaqbudget->{'startdate'},
 172           enddate => $dataaqbudget->{'enddate'},
 173         budgetamount => $dataaqbudget->{'budgetamount'},
 174         branches => \@branches,
 175     );
 176
 177     if ( $dataaqbookfund->{branchcode}) {
 178         $template->param(
 179             disable_branchselection => 1,
 180             branch => $dataaqbookfund->{branchcode},
 181         );
 182     }
 183                                                                                                         # END $OP eq ADD_FORM
 184 ################## ADD_VALIDATE ##################################
 185 # called by add_form, used to insert/modify data in DB
 186 } elsif ($op eq 'add_validate') {
 187     my ($query, $sth);
 188
 189     if (defined $aqbudgetid) {
 190         $query = '
 191 UPDATE aqbudget
 192   SET bookfundid = ?,
 193       startdate = ?,
 194       enddate = ?,
 195       budgetamount = ?,
 196       branchcode = ?
 197   WHERE aqbudgetid = ?
 198 ';
 199         $sth=$dbh->prepare($query);
 200         $sth->execute(
 201             $input->param('bookfundid'),
 202             format_date_in_iso($input->param('startdate')),
 203             format_date_in_iso($input->param('enddate')),
 204             $input->param('budgetamount'),
 205             $input->param('branch') || '',
 206             $aqbudgetid,
 207         );
 208         $sth->finish;
 209     }
 210     else {
 211         $query = '
 212 INSERT
 213   INTO aqbudget
 214   (bookfundid, startdate, enddate, budgetamount, branchcode)
 215   VALUES
 216   (?, ?, ?, ?, ?)
 217 ';
 218         $sth=$dbh->prepare($query);
 219         $sth->execute(
 220             $input->param('bookfundid'),
 221             format_date_in_iso($input->param('startdate')),
 222             format_date_in_iso($input->param('enddate')),
 223             $input->param('budgetamount'),
 224             $input->param('branch') || '',
 225         );
 226         $sth->finish;
 227     }
 228
 229     $input->redirect("aqbudget.pl");
 230
 231 # END $OP eq ADD_VALIDATE
 232 ################## DELETE_CONFIRM ##################################
 233 # called by default form, used to confirm deletion of data in DB
 234 } elsif ($op eq 'delete_confirm') {
 235         my $dbh = C4::Context->dbh;
 236         my $sth=$dbh->prepare("select aqbudgetid,bookfundid,startdate,enddate,budgetamount,branchcode from aqbudget where aqbudgetid=?");
 237         $sth->execute($aqbudgetid);
 238         my $data=$sth->fetchrow_hashref;
 239         $sth->finish;
 240         $template->param(bookfundid => $bookfundid);
 241         $template->param(aqbudgetid => $data->{'aqbudgetid'});
 242         $template->param(startdate => format_date($data->{'startdate'}));
 243         $template->param(enddate => format_date($data->{'enddate'}));
 244         $template->param(budgetamount => $data->{'budgetamount'});
 245                                                                                                         # END $OP eq DELETE_CONFIRM
 246 ################## DELETE_CONFIRMED ##################################
 247 # called by delete_confirm, used to effectively confirm deletion of data in DB
 248 } elsif ($op eq 'delete_confirmed') {
 249         my $dbh = C4::Context->dbh;
 250         my $aqbudgetid=uc($input->param('aqbudgetid'));
 251         my $sth=$dbh->prepare("delete from aqbudget where aqbudgetid=?");
 252         $sth->execute($aqbudgetid);
 253         $sth->finish;
 254          print $input->redirect("aqbookfund.pl");
 255          return;
 256                                                                                                         # END $OP eq DELETE_CONFIRMED
 257 ################## DEFAULT ##################################
 258 } else { # DEFAULT
 259     my ($query, $sth);
 260
 261     # create a look-up table for bookfund names from bookfund ids,
 262     # instead of having on query per budget
 263     my %bookfundname_of = ();
 264     $query = '
 265 SELECT bookfundid, bookfundname
 266   FROM aqbookfund
 267 ';
 268     $sth=$dbh->prepare($query);
 269     $sth->execute;
 270     while (my $row = $sth->fetchrow_hashref) {
 271         $bookfundname_of{ $row->{bookfundid} } = $row->{bookfundname};
 272     }
 273     $sth->finish;
 274
 275     # filters
 276     my $branches = GetBranches();
 277     my @branchloop;
 278     foreach my $branchcode (sort keys %{$branches}) {
 279         my $row = {
 280             code => $branchcode,
 281             name => $branches->{$branchcode}->{branchname},
 282         };
 283
 284         if (defined $input->param('filter_branchcode')
 285             and $input->param('filter_branchcode') eq $branchcode) {
 286             $row->{selected} = 1;
 287         }
 288
 289         push @branchloop, $row;
 290     }
 291
 292     my @bookfundids_loop;
 293     $query = '
 294 SELECT bookfundid
 295   FROM aqbookfund
 296 ';
 297     $sth = $dbh->prepare($query);
 298     $sth->execute();
 299     while (my $row = $sth->fetchrow_hashref) {
 300         if (defined $input->param('filter_bookfundid')
 301             and $input->param('filter_bookfundid') eq $row->{bookfundid}) {
 302             $row->{selected} = 1;
 303         }
 304
 305         push @bookfundids_loop, $row;
 306     }
 307     $sth->finish;
 308
 309     $template->param(
 310         filter_bookfundids => \@bookfundids_loop,
 311         filter_branches => \@branchloop,
 312         filter_amount => $input->param('filter_amount') || undef,
 313         filter_startdate => $input->param('filter_startdate') || undef,
 314         filter_enddate => $input->param('filter_enddate') || undef,
 315     );
 316
 317     my %sign_label_of = (
 318         '=' => 'equal',
 319         '>=' => 'superior',
 320         '<=' => 'inferior',
 321     );
 322
 323     foreach my $field (qw/startdate enddate amount/) {
 324         my $param = 'filter_'.$field.'_sign';
 325
 326         foreach my $sign (keys %sign_label_of) {
 327             if ($input->param($param) eq $sign) {
 328                 $template->param(
 329                     $param.'_'.$sign_label_of{$sign}.'_selected' => 1,
 330                 );
 331             }
 332         }
 333     }
 334
 335     # Search all available budgets
 336     $query = '
 337 SELECT aqbudgetid,
 338        bookfundid,
 339        startdate,
 340        enddate,
 341        budgetamount,
 342        branchcode
 343   FROM aqbudget
 344   WHERE 1 = 1';                 # What's the point?
 345
 346     my @bindings;
 347
 348     if ($input->param('filter_bookfundid')) {
 349         $query.= '
 350     AND bookfundid = ?
 351 ';
 352         push @bindings, $input->param('filter_bookfundid');
 353     }
 354     if ($input->param('filter_branchcode')) {
 355         $query.= '
 356     AND branchcode = ?
 357 ';
 358         push @bindings, $input->param('filter_branchcode');
 359     }
 360     if ($input->param('filter_startdate')) {
 361         $query.= '
 362     AND startdate '.$input->param('filter_startdate_sign').' ?
 363 ';
 364         push @bindings, format_date_in_iso($input->param('filter_startdate'));
 365     }
 366     if ($input->param('filter_enddate')) {
 367         $query.= '
 368     AND enddate '.$input->param('filter_enddate_sign').' ?
 369 ';
 370         push @bindings, format_date_in_iso($input->param('filter_enddate'));
 371     }
 372     if ($input->param('filter_amount')) {
 373         $query.= '
 374     AND budgetamount '.$input->param('filter_amount_sign').' ?
 375 ';
 376         # the amount must be a quantity, with 2 digits after the decimal
 377         # separator
 378         $input->param('filter_amount') =~ m{(\d* (?:\.\d{,2})? )}xms;
 379         my ($amount) = $1;
 380         push @bindings, $amount;
 381     }
 382
 383     $query.= '
 384   ORDER BY bookfundid, aqbudgetid
 385 ';
 386     $sth = $dbh->prepare($query);
 387     $sth->execute(@bindings);
 388     my @results;
 389     while (my $row = $sth->fetchrow_hashref){
 390         push @results, $row;
 391     }
 392     $sth->finish;
 393
 394     # filter budgets depending on the pagination
 395     my $page = $input->param('page') || 1;
 396     my $first = ($page - 1) * $pagesize;
 397
 398     # if we are on the last page, the number of the last word to display
 399     # must not exceed the length of the results array
 400     my $last = min(
 401         $first + $pagesize - 1,
 402         scalar @results - 1,
 403     );
 404
 405     my $toggle = 0;
 406     my @loop;
 407     foreach my $result (@results[$first .. $last]) {
 408         push(
 409             @loop,
 410             {
 411                 %{$result},
 412                 toggle => $toggle++%2,
 413                 bookfundname => $bookfundname_of{ $result->{'bookfundid'} },
 414                 branchname => $branches->{ $result->{branchcode} }->{branchname},
 415                 startdate => format_date($result->{startdate}),
 416                 enddate => format_date($result->{enddate}),
 417             }
 418         );
 419     }
 420
 421     $template->param(
 422         budget => \@loop,
 423         pagination_bar => pagination_bar(
 424             $script_name,
 425             getnbpages(scalar @results, $pagesize),
 426             $page,
 427             'page'
 428         )
 429     );
 430 } #---- END $OP eq DEFAULT
 431 output_html_with_http_headers $input, $cookie, $template->output;
 432