3 # Copyright 2000-2002 Katipo Communications
5 # This file is part of Koha.
7 # Koha is free software; you can redistribute it and/or modify it under the
8 # terms of the GNU General Public License as published by the Free Software
9 # Foundation; either version 2 of the License, or (at your option) any later
12 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
13 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
14 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
16 # You should have received a copy of the GNU General Public License along with
17 # Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
18 # Suite 330, Boston, MA 02111-1307 USA
22 use C4::Dates qw(format_date format_date_in_iso);
25 use vars qw($VERSION @ISA @EXPORT);
28 # set the version for version checking
48 &GetBudgetPeriodsDropbox
64 &CheckBudgetParentPerm
68 # ----------------------------BUDGETS.PM-----------------------------";
69 sub CheckBudgetParentPerm {
70 my ( $budget, $borrower_id ) = @_;
71 my $depth = $budget->{depth};
72 my $parent_id = $budget->{budget_parent_id};
74 my $parent = GetBudget($parent_id);
75 $parent_id = $parent->{budget_parent_id};
76 if ( $parent->{budget_owner_id} == $borrower_id ) {
84 # -------------------------------------------------------------------
86 my $dbh = C4::Context->dbh;
87 my $sth = $dbh->prepare("
88 SELECT COUNT(*) AS sum FROM aqbudgetperiods ");
90 my $res = $sth->fetchrow_hashref;
94 # -------------------------------------------------------------------
95 sub CheckBudgetParent {
96 my ( $new_parent, $budget ) = @_;
97 my $new_parent_id = $new_parent->{'budget_id'};
98 my $budget_id = $budget->{'budget_id'};
99 my $dbh = C4::Context->dbh;
100 my $parent_id_tmp = $new_parent_id;
102 # check new-parent is not a child (or a child's child ;)
103 my $sth = $dbh->prepare(qq|
104 SELECT budget_parent_id FROM
105 aqbudgets where budget_id = ? | );
107 $sth->execute($parent_id_tmp);
108 my $res = $sth->fetchrow_hashref;
109 if ( $res->{'budget_parent_id'} == $budget_id ) {
112 if ( not defined $res->{'budget_parent_id'} ) {
115 $parent_id_tmp = $res->{'budget_parent_id'};
119 # -------------------------------------------------------------------
120 sub BudgetHasChildren {
121 my ( $budget_id ) = @_;
122 my $dbh = C4::Context->dbh;
123 my $sth = $dbh->prepare(qq|
124 SELECT count(*) as sum FROM aqbudgets
125 WHERE budget_parent_id = ? | );
126 $sth->execute( $budget_id );
127 my $sum = $sth->fetchrow_hashref;
129 return $sum->{'sum'};
132 # -------------------------------------------------------------------
133 sub GetBudgetsPlanCell {
134 my ( $cell, $period, $budget ) = @_;
136 my $dbh = C4::Context->dbh;
137 if ( $cell->{'authcat'} eq 'MONTHS' ) {
138 # get the actual amount
139 $sth = $dbh->prepare( qq|
141 SELECT SUM(ecost) AS actual FROM aqorders
142 WHERE budget_id = ? AND
143 entrydate like "$cell->{'authvalue'}%" |
145 $sth->execute( $cell->{'budget_id'} );
146 } elsif ( $cell->{'authcat'} eq 'BRANCHES' ) {
147 # get the actual amount
148 $sth = $dbh->prepare( qq|
150 SELECT SUM(ecost) FROM aqorders
151 LEFT JOIN aqorders_items
152 ON (aqorders.ordernumber = aqorders_items.ordernumber)
154 ON (aqorders_items.itemnumber = items.itemnumber)
155 WHERE budget_id = ? AND homebranch = ? | );
157 $sth->execute( $cell->{'budget_id'}, $cell->{'authvalue'} );
158 } elsif ( $cell->{'authcat'} eq 'ITEMTYPES' ) {
159 # get the actual amount
160 $sth = $dbh->prepare( qq|
162 SELECT SUM( ecost * quantity) AS actual
163 FROM aqorders JOIN biblioitems
164 ON (biblioitems.biblionumber = aqorders.biblionumber )
165 WHERE aqorders.budget_id = ? and itemtype = ? |
167 $sth->execute( $cell->{'budget_id'},
168 $cell->{'authvalue'} );
170 # ELSE GENERIC ORDERS SORT1/SORT2 STAT COUNT.
172 # get the actual amount
173 $sth = $dbh->prepare( qq|
175 SELECT SUM(ecost * quantity) AS actual
177 JOIN aqbudgets ON (aqbudgets.budget_id = aqorders.budget_id )
178 WHERE aqorders.budget_id = ? AND
179 ((aqbudgets.sort1_authcat = ? AND sort1 =?) OR
180 (aqbudgets.sort2_authcat = ? AND sort2 =?)) |
182 $sth->{TraceLevel} = 2;
183 $sth->execute( $cell->{'budget_id'},
184 $budget->{'sort1_authcat'},
185 $cell->{'authvalue'},
186 $budget->{'sort2_authcat'},
190 $actual = $sth->fetchrow_array;
192 # get the estimated amount
193 my $sth = $dbh->prepare( qq|
195 SELECT estimated_amount AS estimated FROM aqbudgets_planning
196 WHERE budget_period_id = ? AND
201 $sth->execute( $cell->{'budget_period_id'},
202 $cell->{'budget_id'},
203 $cell->{'authvalue'},
206 my $estimated = $sth->fetchrow_array;
207 return $actual, $estimated;
210 # -------------------------------------------------------------------
212 my ( $budget_plan, $budget_period_id, $authcat ) = @_;
213 my $dbh = C4::Context->dbh;
214 foreach my $buds (@$budget_plan) {
215 my $lines = $buds->{lines};
216 my $sth = $dbh->prepare( qq|
217 DELETE FROM aqbudgets_planning
218 WHERE budget_period_id = ? AND
222 #delete a aqplan line of cells, then insert new cells,
223 # these could be UPDATES rather than DEL/INSERTS...
224 $sth->execute( $budget_period_id, $lines->[0]{budget_id} , $authcat );
226 foreach my $cell (@$lines) {
227 my $sth = $dbh->prepare( qq|
229 INSERT INTO aqbudgets_planning
231 budget_period_id = ?,
233 estimated_amount = ?,
237 $cell->{'budget_id'},
238 $cell->{'budget_period_id'},
240 $cell->{'estimated_amount'},
241 $cell->{'authvalue'},
247 # -------------------------------------------------------------------
249 my ($budget_id) = @_;
250 my $dbh = C4::Context->dbh;
251 my $sth = $dbh->prepare(qq|
252 SELECT SUM(ecost * quantity ) AS sum FROM aqorders
253 WHERE budget_id = ? AND
254 datecancellationprinted IS NULL
257 $sth->execute($budget_id);
258 my $sum = $sth->fetchrow_array;
259 # $sum = sprintf "%.2f", $sum;
263 # -------------------------------------------------------------------
264 sub GetBudgetPermDropbox {
267 $labels{'0'} = 'None';
268 $labels{'1'} = 'Owner';
269 $labels{'2'} = 'Library';
270 my $radio = CGI::scrolling_list(
271 -name => 'budget_permission',
272 -values => [ '0', '1', '2' ],
280 # -------------------------------------------------------------------
281 sub GetAuthcatDropbox {
282 my ($name, $default ) = @_;
283 my @authorised_values;
285 my $dbh = C4::Context->dbh;
286 my $sth = $dbh->prepare(qq|
287 SELECT distinct(category)
288 FROM authorised_values WHERE category LIKE 'Asort%'
293 push @authorised_values, '';
294 while (my $value = $sth->fetchrow_array) {
295 push @authorised_values, $value;
298 my $budget_authcat_dropbox = CGI::scrolling_list(
300 -values => \@authorised_values,
303 -default => $default,
308 return $budget_authcat_dropbox;
311 # -------------------------------------------------------------------
312 sub GetBudgetAuthCats {
315 my $dbh = C4::Context->dbh;
316 my $sth = $dbh->prepare(
317 "SELECT distinct(category)
318 FROM authorised_values where category like 'Asort%'
322 while ( my $value = $sth->fetchrow_array ) {
323 push @auth_cats, $value;
325 my @loop_data = (); # initialize an array to hold your loop
327 my %row_data; # get a fresh hash for the row data
328 $row_data{authcat} = shift @auth_cats;
329 push( @loop_data, \%row_data );
334 # -------------------------------------------------------------------
335 sub GetAuthvalueDropbox {
336 my ( $name, $authcat, $default ) = @_;
337 my @authorised_values;
340 my $dbh = C4::Context->dbh;
341 my $sth = $dbh->prepare(
342 "SELECT authorised_value,lib
343 FROM authorised_values
347 $sth->execute( $authcat );
349 push @authorised_values, '';
350 while (my ($value, $lib) = $sth->fetchrow_array) {
351 push @authorised_values, $value;
352 $authorised_lib{$value} = $lib;
355 return 0 if keys(%authorised_lib) == 0;
357 my $budget_authvalue_dropbox = CGI::scrolling_list(
358 -values => \@authorised_values,
359 -labels => \%authorised_lib,
360 -default => $default,
368 return $budget_authvalue_dropbox
371 # -------------------------------------------------------------------
372 sub GetBudgetPeriodsDropbox {
373 my ($budget_period_id) = @_;
376 my ($active, $periods) = GetBudgetPeriods();
377 foreach my $r (@$periods) {
378 $labels{"$r->{budget_period_id}"} = $r->{budget_period_description};
379 push @values, $r->{budget_period_id};
382 # if no buget_id is passed then its an add
383 my $budget_period_dropbox = CGI::scrolling_list(
384 -name => 'budget_period_id',
386 -default => $budget_period_id ? $budget_period_id : $active,
390 return $budget_period_dropbox;
393 # -------------------------------------------------------------------
394 sub GetBudgetPeriods {
395 my $dbh = C4::Context->dbh;
396 my $sth = $dbh->prepare(qq|
399 ORDER BY budget_period_startdate, budget_period_enddate |
404 while (my $data = $sth->fetchrow_hashref) {
405 if ($data->{'budget_period_active'} == 1) {
406 $active = $data->{'budget_period_id'};
408 push(@results, $data);
411 return ($active, \@results);
414 # -------------------------------------------------------------------
415 sub GetBudgetPeriod {
416 my ($budget_period_id) = @_;
417 my $dbh = C4::Context->dbh;
418 ## $total = number of records linked to the record that must be deleted
420 ## get information about the record that will be deleted
422 if ($budget_period_id gt 0) {
423 $sth = $dbh->prepare( qq|
426 WHERE budget_period_id=? |
428 $sth->execute($budget_period_id);
429 } else { # ACTIVE BUDGET
430 $sth = $dbh->prepare(qq|
433 WHERE budget_period_active=1 |
437 my $data = $sth->fetchrow_hashref;
442 # -------------------------------------------------------------------
443 sub DelBudgetPeriod() {
444 my ($budget_period_id) = @_;
445 my $dbh = C4::Context->dbh;
446 ; ## $total = number of records linked to the record that must be deleted
449 ## get information about the record that will be deleted
450 my $sth = $dbh->prepare(qq|
451 SELECT budget_period_id
452 , budget_period_startdate
453 , budget_period_enddate
454 , budget_period_amount
456 , budget_period_description
458 WHERE budget_period_id=? |
460 $sth->execute($budget_period_id);
461 my $data = $sth->fetchrow_hashref;
465 # -------------------------------------------------------------------
466 sub ModBudgetPeriod() {
467 my ($budget_period_id) = @_;
468 my $dbh = C4::Context->dbh
469 ; ## $total = number of records linked to the record that must be deleted my $total = 0;
471 ## get information about the record that will be deleted
472 my $sth = $dbh->prepare("
473 SELECT budget_period_id
474 , budget_period_startdate
475 , budget_period_enddate
476 , budget_period_amount
478 , budget_period_description
480 WHERE budget_period_id=?;"
482 $sth->execute($budget_period_id);
483 my $data = $sth->fetchrow_hashref;
487 # -------------------------------------------------------------------
488 sub GetBudgetHierarchy {
489 my ($budget_period_id, $branchcode, $owner) = @_;
491 my $dbh = C4::Context->dbh;
495 JOIN aqbudgetperiods USING (budget_period_id)
496 WHERE budget_period_active=1 |;
497 # show only period X if requested
498 if ($budget_period_id) {
499 $query .= "AND aqbudgets.budget_period_id = ?";
500 push @bind_params, $budget_period_id;
502 # show only budgets owned by me, my branch or everyone
505 $query .= " AND (budget_owner_id = ? OR budget_branchcode = ? OR (budget_branchcode IS NULL AND budget_owner_id IS NULL))";
506 push @bind_params, $owner;
507 push @bind_params, $branchcode;
509 $query .= ' AND budget_owner_id = ? OR budget_owner_id IS NULL';
510 push @bind_params, $owner;
514 $query .= " AND (budget_branchcode =? or budget_branchcode is NULL)";
515 push @bind_params, $branchcode;
518 my $sth = $dbh->prepare($query);
519 $sth->execute(@bind_params);
520 my $results = $sth->fetchall_arrayref({});
525 foreach my $r (@res) {
528 $r->{depth} = '0' if !defined $r->{budget_parent_id};
529 foreach my $r2 (@res) {
530 if (defined $r2->{budget_parent_id}
531 && $r2->{budget_parent_id} == $r->{budget_id}) {
532 push @child, $r2->{budget_id};
533 $r2->{depth} = ($r->{depth} + 1) if defined $r->{depth};
536 $r->{child} = \@child if scalar @child > 0; # add the child
537 $depth_cnt++ if !defined $r->{'depth'};
539 last if ($depth_cnt == 0 || $i == 100);
543 # look for top parents 1st
545 my ($i, $depth_count) = 0;
548 foreach my $r (@res) {
549 if ($r->{depth} == $depth_count) {
550 $children++ if (ref $r->{child} eq 'ARRAY');
552 # find the parent id element_id and insert it after
555 if ($depth_count > 0) {
558 my $depth = $r->{depth} * 2;
559 my $space = pack "A[$depth]";
560 $r->{budget_code_indent} = $space . $r->{budget_code};
561 $r->{budget_name_indent} = $space . $r->{budget_name};
562 foreach my $r3 (@sort) {
563 if ($r3->{budget_id} == $r->{budget_parent_id}) {
570 $r->{budget_code_indent} = $r->{budget_code};
571 $r->{budget_name_indent} = $r->{budget_name};
574 if (defined $parent) {
575 splice @sort, ($parent + 1), 0, $r;
582 } # --------------foreach
584 last if $children == 0;
587 # add budget-percent and allocation, and flags for html-template
588 foreach my $r (@sort) {
589 my $subs_href = $r->{'child'};
590 my @subs_arr = @$subs_href if defined $subs_href;
592 my $moo = $r->{'budget_code_indent'};
593 $moo =~ s/\ /\ \;/g;
594 $r->{'budget_code_indent'} = $moo;
596 my $moo = $r->{'budget_name_indent'};
597 $moo =~ s/\ /\ \;/g;
598 $r->{'budget_name_indent'} = $moo;
600 $r->{'budget_spent'} = GetBudgetSpent( $r->{'budget_id'} );
602 # $budget->{'budget_alloc'} = sprintf( "%.2f", $budget->{'budget_alloc'} - $budget->{'budget_amount alloc'} );
603 # $budget->{'budget_alloc'} = sprintf( "%.2f", $budget->{'budget_alloc'} );
605 $r->{'budget_amount_total'} = $r->{'budget_amount'} + $r->{'budget_amount_sublevel'} ;
606 # $r->{budget_alloc} = $r->{'budget_amount'} - $r->{'budget_amount_sublevel'} ;
608 # $r->{'budget_amount_sublevel'} ;
613 foreach my $sub (@subs_arr) {
614 my $sub_budget = GetBudget($sub);
615 # $r->{budget_spent_sublevel} += $bud->{'budget_amount'} ;
617 $r->{budget_spent_sublevel} += GetBudgetSpent( $sub_budget->{'budget_id'} );
618 $unalloc_count += $sub_budget->{'budget_amount'} + $sub_budget->{'budget_amount_sublevel'};
621 $r->{budget_unalloc_sublevel} = $r->{'budget_amount_sublevel'} - $unalloc_count;
623 # (($r->{'budget_amount'} - $r->{'budget_alloc'}) / $r->{'budget_amount'}) * 100;
626 # my $percent = $r->{'budget_amount'} ? ( $r->{'budget_alloc'} / $r->{'budget_amount'} ) * 100 : 0;
627 # my $spent_percent = ( $r->{'budget_spent'} / $r->{'budget_amount'} ) * 100 if $r->{'budget_amount'};
629 # (($r->{'budget_amount'} - $r->{'budget_alloc'}) / $r->{'budget_amount'}) * 100;
630 # my $percent = ( $r->{'budget_alloc'} / $r->{'budget_amount'} ) * 100 if $r->{'budget_amount'};
631 # my $spent_percent = ( $r->{'budget_spent'} / $r->{'budget_amount'} ) * 100 if $r->{'budget_amount'};
633 $r->{budget_alloc_none} = 1;
634 } elsif ($percent == 100) {
635 $r->{budget_alloc_full} = 1
638 $r->{budget_alloc_percent} = sprintf("%00d", $percent);
642 if ( scalar @subs_arr == 0 && $r->{budget_amount_sublevel} > 0 ) {
643 $r->{warn_no_subs} = 1;
649 # -------------------------------------------------------------------
652 my $dbh = C4::Context->dbh;
654 INSERT INTO aqbudgets
656 budget_period_id = ?,
657 budget_parent_id = ?,
659 budget_branchcode = ?,
661 budget_amount_sublevel = ?,
668 budget_permission = ?
670 my $sth = $dbh->prepare($query);
672 $budget->{'budget_code'} ? $budget->{'budget_code'} : undef,
673 $budget->{'budget_period_id'} ? $budget->{'budget_period_id'} : undef,
674 $budget->{'budget_parent_id'} ? $budget->{'budget_parent_id'} : undef,
675 $budget->{'budget_name'} ? $budget->{'budget_name'} : undef,
676 $budget->{'budget_branchcode'} ? $budget->{'budget_branchcode'} : undef,
677 $budget->{'budget_amount'} ? $budget->{'budget_amount'} : undef,
678 $budget->{'budget_amount_sublevel'} ? $budget->{'budget_amount_sublevel'} : undef,
679 $budget->{'budget_encumb'} ? $budget->{'budget_encumb'} : undef,
680 $budget->{'budget_expend'} ? $budget->{'budget_expend'} : undef,
681 $budget->{'budget_notes'} ? $budget->{'budget_notes'} : undef,
682 $budget->{'sort1_authcat'} ? $budget->{'sort1_authcat'} : undef,
683 $budget->{'sort2_authcat'} ? $budget->{'sort2_authcat'} : undef,
684 $budget->{'budget_owner_id'} ? $budget->{'budget_owner_id'} : undef,
685 $budget->{'budget_permission'} ? $budget->{'budget_permission'} : undef,
690 # -------------------------------------------------------------------
693 my $dbh = C4::Context->dbh;
697 budget_period_id = ?,
698 budget_parent_id = ?,
700 budget_branchcode = ?,
702 budget_amount_sublevel = ?,
709 budget_permission = ?
713 my $sth = $dbh->prepare($query);
715 $budget->{'budget_code'} ? $budget->{'budget_code'} : undef,
716 $budget->{'budget_period_id'} ? $budget->{'budget_period_id'} : undef,
717 $budget->{'budget_parent_id'} ? $budget->{'budget_parent_id'} : undef,
718 $budget->{'budget_name'} ? $budget->{'budget_name'} : undef,
719 $budget->{'budget_branchcode'} ? $budget->{'budget_branchcode'} : undef,
720 $budget->{'budget_amount'} ? $budget->{'budget_amount'} : undef,
721 $budget->{'budget_amount_sublevel'} ? $budget->{'budget_amount_sublevel'} : undef,
722 $budget->{'budget_encumb'} ? $budget->{'budget_encumb'} : undef,
723 $budget->{'budget_expend'} ? $budget->{'budget_expend'} : undef,
724 $budget->{'budget_notes'} ? $budget->{'budget_notes'} : undef,
725 $budget->{'sort1_authcat'} ? $budget->{'sort1_authcat'} : undef,
726 $budget->{'sort2_authcat'} ? $budget->{'sort2_authcat'} : undef,
727 $budget->{'budget_owner_id'} ? $budget->{'budget_owner_id'} : undef,
728 $budget->{'budget_permission'} ? $budget->{'budget_permission'} : undef,
729 $budget->{'budget_id'},
734 # -------------------------------------------------------------------
736 my ($budget_id) = @_;
737 my $dbh = C4::Context->dbh;
738 my $sth = $dbh->prepare("delete from aqbudgets where budget_id=?");
739 my $rc = $sth->execute($budget_id);
746 =head2 FUNCTIONS ABOUT BUDGETS
756 &GetBudget($budget_id);
758 get a specific budget
764 # -------------------------------------------------------------------
766 my ( $budget_id ) = @_;
767 my $dbh = C4::Context->dbh;
774 my $sth = $dbh->prepare($query);
775 $sth->execute( $budget_id );
776 my $result = $sth->fetchrow_hashref;
784 &GetBudget($budget_id);
792 # -------------------------------------------------------------------
795 my $dbh = C4::Context->dbh;
796 my $q = "SELECT * from aqbudgets";
800 $sth = $dbh->prepare($q);
803 $q = "select budget_period_id from aqbudgetperiods where budget_period_active = 1 ";
804 $sth = $dbh->prepare($q);
806 $row = $sth->fetchrow_hashref();
807 $q = "select * from aqbudgets WHERE budget_period_id =? ";
808 $sth = $dbh->prepare($q);
809 $sth->execute( $row->{'budget_period_id'} );
811 my $results = $sth->fetchall_arrayref( {} );
816 # -------------------------------------------------------------------
820 @currencies = &GetCurrencies;
822 Returns the list of all known currencies.
824 C<$currencies> is a array; its elements are references-to-hash, whose
825 keys are the fields from the currency table in the Koha database.
830 my $dbh = C4::Context->dbh;
835 my $sth = $dbh->prepare($query);
838 while ( my $data = $sth->fetchrow_hashref ) {
839 push( @results, $data );
845 # -------------------------------------------------------------------
848 my $dbh = C4::Context->dbh;
850 SELECT * FROM currency where active = '1' ";
851 my $sth = $dbh->prepare($query);
853 my $r = $sth->fetchrow_hashref;
860 &ModCurrencies($currency, $newrate);
862 Sets the exchange rate for C<$currency> to be C<$newrate>.
867 my ( $currency, $rate ) = @_;
868 my $dbh = C4::Context->dbh;
873 my $sth = $dbh->prepare($query);
874 $sth->execute( $rate, $currency );
877 # -------------------------------------------------------------------
879 =head3 ConvertCurrency
881 $foreignprice = &ConvertCurrency($currency, $localprice);
883 Converts the price C<$localprice> to foreign currency C<$currency> by
884 dividing by the exchange rate, and returns the result.
886 If no exchange rate is found,e is one
891 sub ConvertCurrency {
892 my ( $currency, $price ) = @_;
893 my $dbh = C4::Context->dbh;
899 my $sth = $dbh->prepare($query);
900 $sth->execute($currency);
901 my $cur = ( $sth->fetchrow_array() )[0];
905 return ( $price / $cur );
908 END { } # module clean-up code here (global destructor)
917 Koha Developement team <info@koha.org>