@@ -, +, @@ options Add option show or no inactive budget and more options Use subroutine GetBudgetHierarchy for return all budgets Delete subroutine GetBudgetPeriodDescription and theire tests Use Price TT plugin Correct name of column and capitalization the first letter Add checkbox for show inactive budgets, default the drop down list containt a active budget Not use [i] for inactive budgets, i add (inactive) at the end of inactive budget Add vendor note in the list of show attribute Test case: Go to Home > Reports > Orders by fund Select one or all budgets You can show the inactive budget, default the drop down list containt a active budget Choose output to screen ou csv file --- C4/Budgets.pm | 123 ++++++++++- .../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 | 161 +++++++++++++++ .../prog/en/modules/reports/reports-home.tt | 79 +++---- reports/orders_by_fund.pl | 216 ++++++++++++++++++++ t/db_dependent/Acquisition.t | 22 +- t/db_dependent/Budgets.t | 13 +- 8 files changed, 581 insertions(+), 46 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_fund.pl --- a/C4/Budgets.pm +++ a/C4/Budgets.pm @@ -33,6 +33,9 @@ BEGIN { &GetBudgetByOrderNumber &GetBudgetByCode &GetBudgets + &BudgetsByActivity + &GetBudgetsReport + &GetBudgetReport &GetBudgetHierarchy &AddBudget &ModBudget @@ -53,6 +56,7 @@ BEGIN { &GetBudgetPeriods &ModBudgetPeriod &AddBudgetPeriod + &GetBudgetPeriodDescription &DelBudgetPeriod &ModBudgetPlan @@ -71,7 +75,6 @@ BEGIN { # ----------------------------BUDGETS.PM-----------------------------"; - =head1 FUNCTIONS ABOUT BUDGETS =cut @@ -451,7 +454,6 @@ sub GetBudgetPeriod { return $data; } -# ------------------------------------------------------------------- sub DelBudgetPeriod{ my ($budget_period_id) = @_; my $dbh = C4::Context->dbh; @@ -595,6 +597,8 @@ sub DelBudget { } +# ------------------------------------------------------------------- + =head2 GetBudget &GetBudget($budget_id); @@ -603,7 +607,6 @@ get a specific budget =cut -# ------------------------------------------------------------------- sub GetBudget { my ( $budget_id ) = @_; my $dbh = C4::Context->dbh; @@ -618,6 +621,8 @@ sub GetBudget { return $result; } +# ------------------------------------------------------------------- + =head2 GetBudgetByOrderNumber &GetBudgetByOrderNumber($ordernumber); @@ -626,7 +631,6 @@ get a specific budget by order number =cut -# ------------------------------------------------------------------- sub GetBudgetByOrderNumber { my ( $ordernumber ) = @_; my $dbh = C4::Context->dbh; @@ -642,6 +646,117 @@ 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 num"[% sep %]"Basket name"[% sep %]"Authorised by"[% sep %]"Biblio number"[% 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 %]"Internal note"[% sep %]"Vendor note" --- 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,161 @@ +[% USE Price %] +[% INCLUDE 'doc-head-open.inc' %] +
Fund | +Basket | +Basket name | +Basket by | +Title | +Currency | +List price | +RRP | +Budgeted cost | +Quantity | +Total RRP | +Total cost | +Entry date | +Date deceived | +Internal note | +Vendor note | +
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
[% ordersloo.budget_name |html %] | +[% ordersloo.basketno |html %] | +[% ordersloo.basketname |html %] | +[% ordersloo.authorisedbyname %] | +[% ordersloo.title |html %] | +[% ordersloo.currency %] | +[% ordersloo.listprice | $Price %] | +[% ordersloo.rrp | $Price %] | +[% ordersloo.ecost | $Price %] | +[% ordersloo.quantity %] | +[% ordersloo.total_rrp | $Price %] | +[% ordersloo.total_ecost | $Price %] | +[% ordersloo.entrydate %] | +[% ordersloo.datereceived %] | +[% ordersloo.order_internalnote |html %] | +[% ordersloo.order_vendornote |html %] | +
TOTAL | [% total_quantity %] | [% total_rrp | $Price %] | [% total_ecost | $Price %] |