From 3a4868251425cafc6349df2503856ed255c5ea4d Mon Sep 17 00:00:00 2001 From: hdl Date: Tue, 9 Aug 2005 14:13:27 +0000 Subject: [PATCH] Adding lateorders page. It provides the user with the list of items that have been ordered for a delay and are NOT yet received. The user may filter by supplier or branch or delay. This page is still under developpement. Goal is to make it ready to print to reorder the books. 2 new functions have been written in Acquisition module : getsupplierlistwithlateorders getlateorders branches has been modified to manage branch independancy. Request for comment. STILL UNDER developpment --- C4/Acquisition.pm | 102 +++++++++++++++++- acqui/lateorders.pl | 68 ++++++++++++ .../default/en/acqui/lateorders.tmpl | 86 +++++++++++++++ 3 files changed, 255 insertions(+), 1 deletion(-) create mode 100755 acqui/lateorders.pl create mode 100644 koha-tmpl/intranet-tmpl/default/en/acqui/lateorders.tmpl diff --git a/C4/Acquisition.pm b/C4/Acquisition.pm index de67a3ca30..cfa57b05dc 100644 --- a/C4/Acquisition.pm +++ b/C4/Acquisition.pm @@ -56,6 +56,8 @@ orders, converting money to different currencies, and so forth. &ordersearch &histsearch &modorder &getsingleorder &invoice &receiveorder &updaterecorder &newordernum + &getsupplierlistwithlateorders + &getlateorders &bookfunds &curconvert &getcurrencies &bookfundbreakdown &updatecurrencies &getcurrency @@ -512,6 +514,95 @@ sub getallorders { return(scalar(@results),@results); } +=item getsupplierlistwithlateorders + + %results = &getsupplierlistwithlateorders; + +Searches for suppliers with late orders. + +=cut +#' +sub getsupplierlistwithlateorders { + my $delay=shift; + my $dbh = C4::Context->dbh; +#FIXME NOT quite sure that this operation is valid for DBMs different from Mysql, HOPING so +#should be tested with other DBMs + + my $strsth; + my$dbdriver = C4::Context->config("db_scheme")||"mysql"; + if ($dbdriver eq "mysql"){ + $strsth="SELECT DISTINCT aqbasket.booksellerid, aqbooksellers.name + FROM aqorders, aqbasket + LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id + WHERE aqorders.basketno = aqbasket.basketno AND + (closedate < DATE_SUB(CURDATE( ),INTERVAL $delay DAY) AND (datereceived = '' or datereceived is null)) + "; + }else { + $strsth="SELECT DISTINCT aqbasket.booksellerid, aqbooksellers.name + FROM aqorders, aqbasket + LEFT JOIN aqbooksellers ON aqbasket.aqbooksellerid = aqbooksellers.id + WHERE aqorders.basketno = aqbasket.basketno AND + (closedate < (CURDATE( )-(INTERVAL $delay DAY))) AND (datereceived = '' or datereceived is null)) + "; + } + warn "C4::Acquisition getsupplierlistwithlateorders : ".$strsth; + my $sth = $dbh->prepare($strsth); + $sth->execute; + my %supplierlist; + while (my ($id,$name) = $sth->fetchrow) { + $supplierlist{$id} = $name; + } + return %supplierlist; +} + +=item getlateorders + + %results = &getlateorders; + +Searches for suppliers with late orders. + +=cut +#' +sub getlateorders { + my $delay=shift; + my $supplierid = shift; + my $branch = shift; + + my $dbh = C4::Context->dbh; +#BEWARE, order of parenthesis and LEFT JOIN is important for speed + my $strsth ="SELECT DISTINCT aqbasket.basketno, + DATE(aqbasket.closedate) as orderdate, aqorders.quantity, aqorders.unitprice, + aqbookfund.bookfundname as budget, aqorderbreakdown.branchcode as branch, + aqbooksellers.name as supplier, + biblio.title, biblio.author, biblioitems.publishercode as publisher, + DATEDIFF(DATE_SUB(CURDATE( ),INTERVAL $delay DAY),closedate) AS latesince + FROM + ( + (aqorders LEFT JOIN biblio on biblio.biblionumber = aqorders.biblionumber) LEFT JOIN biblioitems on biblioitems.biblionumber=biblio.biblionumber + ) LEFT JOIN + (aqorderbreakdown LEFT JOIN aqbookfund on aqorderbreakdown.bookfundid = aqbookfund.bookfundid) + on aqorders.ordernumber = aqorderbreakdown.ordernumber, + aqbasket LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id + WHERE aqorders.basketno = aqbasket.basketno AND + (closedate < DATE_SUB(CURDATE( ),INTERVAL $delay DAY) AND (datereceived = '' or datereceived is null)) + "; + $strsth .= " AND aqbasket.booksellerid = $supplierid " if ($supplierid); + $strsth .= " AND aqorderbreakdown.branchcode like \'".$branch."\'" if ($branch); + $strsth .= " ORDER BY latesince,basketno,branch, supplier"; + warn "C4::Acquisition : getlateorders SQL:".$strsth; + my $sth = $dbh->prepare($strsth); + $sth->execute; + my @results; + my $hilighted = 1; + while (my $data = $sth->fetchrow_hashref) { + $data->{hilighted}=$hilighted if ($hilighted>0); + push @results, $data; + $hilighted= -$hilighted; + } + $sth->finish; + return(scalar(@results),@results); +} + # FIXME - Never used sub getrecorders { #gets all orders from a certain supplier, orders them alphabetically @@ -886,7 +977,16 @@ table of the Koha database. #' sub branches { my $dbh = C4::Context->dbh; - my $sth = $dbh->prepare("Select * from branches order by branchname"); + my $sth; + if (C4::Context->preference("IndependantBranches") && (C4::Context->userenv->{flags}!=1)){ + my $strsth ="Select * from branches "; + $strsth.= " WHERE branchcode = ".$dbh->quote(C4::Context->userenv->{branch}); + $strsth.= " order by branchname"; + warn "C4::Acquisition->branches : ".$strsth; + $sth=$dbh->prepare($strsth); + } else { + $sth = $dbh->prepare("Select * from branches order by branchname"); + } my @results = (); $sth->execute(); diff --git a/acqui/lateorders.pl b/acqui/lateorders.pl new file mode 100755 index 0000000000..fca247d05a --- /dev/null +++ b/acqui/lateorders.pl @@ -0,0 +1,68 @@ +#!/usr/bin/perl + +use strict; +use CGI; +use C4::Acquisition; +use C4::Auth; +use C4::Output; +use C4::Interface::CGI::Output; +use C4::Context; +use HTML::Template; + +my $query = new CGI; +my ($template, $loggedinuser, $cookie) += get_template_and_user({template_name => "acqui/lateorders.tmpl", + query => $query, + type => "intranet", + authnotrequired => 0, + flagsrequired => {acquisition => 1}, + debug => 1, + }); +# my $title = $query->param('title'); +# my $ISSN = $query->param('ISSN'); +# my @subscriptions = getsubscriptions($title,$ISSN); + +my $supplierid = $query->param('supplierid'); +my $delay = $query->param('delay'); +my $branch = $query->param('branch'); + +$delay =($delay?$delay:30); + +my %supplierlist = getsupplierlistwithlateorders($delay,$branch); +my @select_supplier; +push @select_supplier,""; +foreach my $supplierid (keys %supplierlist){ + push @select_supplier, $supplierid; +} +my $CGIsupplier=CGI::scrolling_list( -name => 'supplierid', + -values => \@select_supplier, + -default => $supplierid, + -labels => \%supplierlist, + -size => 1, + -multiple => 0 ); + +my @select_branches; +my %select_branches; +push @select_branches,""; +$select_branches{""}=""; +my ($count, @branches) = branches(); +#branches is IndependantBranches aware +foreach my $branch (@branches){ + push @select_branches, $branch->{branchcode}; + $select_branches{$branch->{branchcode}}=$branch->{branchname}; +} +my $CGIbranch=CGI::scrolling_list( -name => 'branch', + -values => \@select_branches, + -labels => \%select_branches, + -size => 1, + -multiple => 0 ); + +my ($count, @lateorders) = getlateorders($delay,$supplierid,$branch); + +$template->param(delay=>$delay) if ($delay); +$template->param( + CGIbranch => $CGIbranch, + CGIsupplier => $CGIsupplier, + lateorders => \@lateorders + ); +output_html_with_http_headers $query, $cookie, $template->output; diff --git a/koha-tmpl/intranet-tmpl/default/en/acqui/lateorders.tmpl b/koha-tmpl/intranet-tmpl/default/en/acqui/lateorders.tmpl new file mode 100644 index 0000000000..de8b46a1b5 --- /dev/null +++ b/koha-tmpl/intranet-tmpl/default/en/acqui/lateorders.tmpl @@ -0,0 +1,86 @@ + + +
+

Late issues

+ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + +
SupplierBudgetTitleAuthorPublisherBranchOrder DateIncluding BasketQuantityUnit PriceLate since 
+ + +   + + + +   + + "> days + + +
+ + + + + + + + + + + + + + + + + + + + + + +   +
+
+ + -- 2.39.5