@@ -, +, @@ --- C4/Budgets.pm | 150 ++++++++++++- .../csv_headers/reports/orders_by_budget.tt | 1 + .../en/modules/reports/csv/orders_by_budget.tt | 12 + .../prog/en/modules/reports/orders_by_budget.tt | 125 +++++++++++ .../prog/en/modules/reports/reports-home.tt | 79 +++---- reports/orders_by_budget.pl | 233 ++++++++++++++++++++ t/db_dependent/Acquisition.t | 27 +++ t/db_dependent/Budgets.t | 64 +++++- 8 files changed, 649 insertions(+), 42 deletions(-) create mode 100644 koha-tmpl/intranet-tmpl/prog/en/includes/csv_headers/reports/orders_by_budget.tt create mode 100644 koha-tmpl/intranet-tmpl/prog/en/modules/reports/csv/orders_by_budget.tt create mode 100644 koha-tmpl/intranet-tmpl/prog/en/modules/reports/orders_by_budget.tt create mode 100755 reports/orders_by_budget.pl --- a/C4/Budgets.pm +++ a/C4/Budgets.pm @@ -35,6 +35,9 @@ BEGIN { &GetBudgetByOrderNumber &GetBudgetByCode &GetBudgets + &BudgetsByActivity + &GetBudgetsReport + &GetBudgetReport &GetBudgetHierarchy &AddBudget &ModBudget @@ -55,6 +58,7 @@ BEGIN { &GetBudgetPeriods &ModBudgetPeriod &AddBudgetPeriod + &GetBudgetPeriodDescription &DelBudgetPeriod &ModBudgetPlan @@ -459,6 +463,37 @@ sub GetBudgetPeriod { } # ------------------------------------------------------------------- +=head2 GetBudgetPeriodDescription + + $description = GetBudgetPeriodDescription($budget_id); + + Return value: hashref + + Returns the budget period description for a given budget id. + + Description is fetched from aqbudgetperiods, id is compared aqbudgets. (INNER JOIN on budget_period_id) + +=cut +sub GetBudgetPeriodDescription { + my ($budget_id) = @_; + my $dbh = C4::Context->dbh; + my $sth; + if ($budget_id) { + $sth = $dbh->prepare( + "SELECT budget_period_description + FROM aqbudgetperiods bp + INNER JOIN aqbudgets b + ON bp.budget_period_id = b.budget_period_id + WHERE b.budget_id=? + " + ); + $sth->execute($budget_id); + } + my $data = $sth->fetchrow_hashref; + return $data; +} + +# ------------------------------------------------------------------- sub DelBudgetPeriod{ my ($budget_period_id) = @_; my $dbh = C4::Context->dbh; @@ -602,6 +637,7 @@ sub DelBudget { } +# ------------------------------------------------------------------- =head2 GetBudget &GetBudget($budget_id); @@ -610,7 +646,6 @@ get a specific budget =cut -# ------------------------------------------------------------------- sub GetBudget { my ( $budget_id ) = @_; my $dbh = C4::Context->dbh; @@ -625,6 +660,8 @@ sub GetBudget { return $result; } +# ------------------------------------------------------------------- + =head2 GetBudgetByOrderNumber &GetBudgetByOrderNumber($ordernumber); @@ -633,7 +670,6 @@ get a specific budget by order number =cut -# ------------------------------------------------------------------- sub GetBudgetByOrderNumber { my ( $ordernumber ) = @_; my $dbh = C4::Context->dbh; @@ -649,6 +685,116 @@ sub GetBudgetByOrderNumber { return $result; } +=head2 GetBudgetReport + + &GetBudgetReport( [$budget_id] ); + +Get all orders for a specific budget, without cancelled orders. + +Returns an array of hashrefs. + +=cut + +# -------------------------------------------------------------------- +sub GetBudgetReport { + my ( $budget_id ) = @_; + my $dbh = C4::Context->dbh; + my $query = ' + SELECT o.*, b.budget_name + FROM aqbudgets b + INNER JOIN aqorders o + ON b.budget_id = o.budget_id + WHERE b.budget_id=? + AND (o.orderstatus != "cancelled") + ORDER BY b.budget_name'; + + my $sth = $dbh->prepare($query); + $sth->execute( $budget_id ); + + my @results = (); + while ( my $data = $sth->fetchrow_hashref ) { + push( @results, $data ); + } + return @results; +} + +=head2 GetBudgetsByActivity + + &GetBudgetsByActivity( $budget_period_active ); + +Get all active or inactive budgets, depending of the value +of the parameter. + +1 = active +0 = inactive + +=cut + +# -------------------------------------------------------------------- +sub GetBudgetsByActivity { + my ( $budget_period_active ) = @_; + my $dbh = C4::Context->dbh; + my $query = " + SELECT DISTINCT b.* + FROM aqbudgetperiods bp + INNER JOIN aqbudgets b + ON bp.budget_period_id = b.budget_period_id + WHERE bp.budget_period_active=? + "; + my $sth = $dbh->prepare($query); + $sth->execute( $budget_period_active ); + my @results = (); + while ( my $data = $sth->fetchrow_hashref ) { + push( @results, $data ); + } + return @results; +} +# -------------------------------------------------------------------- +=head2 GetBudgetsReport + + &GetBudgetsReport( [$activity] ); + +Get all but cancelled orders for all funds. + +If the optionnal activity parameter is passed, returns orders for active/inactive budgets only. + +active = 1 +inactive = 0 + +Returns an array of hashrefs. + +=cut + +sub GetBudgetsReport { + my ($activity) = @_; + my $dbh = C4::Context->dbh; + my $query = ' + SELECT o.*, b.budget_name + FROM aqbudgetperiods bp + INNER JOIN aqbudgets b + ON bp.budget_period_id = b.budget_period_id + INNER JOIN aqorders o + ON b.budget_id = o.budget_id '; + if($activity ne ''){ + $query .= 'WHERE bp.budget_period_active=? '; + } + $query .= 'AND (o.orderstatus != "cancelled") + ORDER BY b.budget_name'; + + my $sth = $dbh->prepare($query); + if($activity ne ''){ + $sth->execute($activity); + } + else{ + $sth->execute; + } + my @results = (); + while ( my $data = $sth->fetchrow_hashref ) { + push( @results, $data ); + } + return @results; +} + =head2 GetBudgetByCode my $budget = &GetBudgetByCode($budget_code); --- a/koha-tmpl/intranet-tmpl/prog/en/includes/csv_headers/reports/orders_by_budget.tt +++ a/koha-tmpl/intranet-tmpl/prog/en/includes/csv_headers/reports/orders_by_budget.tt @@ -0,0 +1, @@ +Fund[% sep %]Basket[% sep %]Basket by[% sep %]biblionumber[% sep %]Title[% sep %]Currency[% sep %]Vendor Price[% sep %]RRP[% sep %]Budgeted Cost[% sep %]Quantity[% sep %]Total RRP[% sep %]Total Cost[% sep %]Entry Date[% sep %]Date Received[% sep %]Notes --- a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/csv/orders_by_budget.tt +++ a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/csv/orders_by_budget.tt @@ -0,0 +1,12 @@ +[% INCLUDE csv_headers/reports/orders_by_budget.tt %] +[%- FOREACH row IN rows %] + [%- FOREACH field IN row; + field; + sep IF !loop.last; + END %] +[% END -%] +TOTAL +[%- FOREACH field IN totalrow; + field; + sep IF !loop.last; +END %] --- a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/orders_by_budget.tt +++ a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/orders_by_budget.tt @@ -0,0 +1,125 @@ +[% INCLUDE 'doc-head-open.inc' %] +
Fund | +Basket | +Basket By | +Title | +Currency | +Vendor Price | +RRP | +Budgeted Cost | +Quantity | +Total RRP | +Total Cost | +Entry Date | +Date Received | +Notes | +
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
[% ordersloo.budget_name |html %] | ++ [% ordersloo.basketno |html %] + | +[% ordersloo.authorisedbyname %] | ++ [% ordersloo.title |html %] + | +[% ordersloo.currency %] | +[% ordersloo.listprice %] | +[% ordersloo.rrp %] | +[% ordersloo.ecost %] | +[% ordersloo.quantity %] | +[% ordersloo.total_rrp %] | +[% ordersloo.total_ecost %] | +[% ordersloo.entrydate %] | +[% ordersloo.datereceived %] | +[% ordersloo.notes |html %] | +
TOTAL | [% total_quantity %] | [% total_rrp %] | [% total_ecost %] |