From f53b82430bc08942ea41cfb41513039a897131c2 Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Fri, 9 Mar 2012 12:09:24 +0100 Subject: [PATCH 1/2] Bug 5342: Adds a claims_count field in the serial table This patch adds 1 column in the serial table : - claims_count : number of claims for an orders You can add a tag around your item fields, like this : -- Begin example <> <> The following issues are in late: <>, <> (<>) -- End example For more explication, see the discussion on the Bug 5347 --- C4/Serials.pm | 26 ++++-- installer/data/mysql/kohastructure.sql | 1 + installer/data/mysql/updatedatabase.pl | 10 ++ .../prog/en/modules/serials/claims.tt | 96 +++++++++++--------- serials/claims.pl | 46 ++++++---- 5 files changed, 107 insertions(+), 72 deletions(-) diff --git a/C4/Serials.pm b/C4/Serials.pm index af5c833..412abdc 100644 --- a/C4/Serials.pm +++ b/C4/Serials.pm @@ -88,10 +88,12 @@ sub GetSuppliersWithLateIssues { my $dbh = C4::Context->dbh; my $query = qq| SELECT DISTINCT id, name - FROM subscription - LEFT JOIN serial ON serial.subscriptionid=subscription.subscriptionid - LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id - WHERE id > 0 AND ((planneddate < now() AND serial.status=1) OR serial.STATUS = 3 OR serial.STATUS = 4) ORDER BY name|; + FROM subscription + LEFT JOIN serial ON serial.subscriptionid=subscription.subscriptionid + LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id + WHERE id > 0 AND ((planneddate < now() AND serial.status=1) OR serial.STATUS = 3 OR serial.STATUS = 4 OR serial.STATUS = 7) + ORDER BY name + |; return $dbh->selectall_arrayref($query, { Slice => {} }); } @@ -284,8 +286,12 @@ sub UpdateClaimdateIssues { my $dbh = C4::Context->dbh; $date = strftime( "%Y-%m-%d", localtime ) unless ($date); my $query = " - UPDATE serial SET claimdate = ?, status = 7 - WHERE serialid in (" . join( ",", map { '?' } @$serialids ) . ")"; + UPDATE serial + SET claimdate = ?, + status = 7, + claims_count = claims_count + 1 + WHERE serialid in (" . join( ",", map { '?' } @$serialids ) . ") + "; my $rq = $dbh->prepare($query); $rq->execute($date, @$serialids); return $rq->rows; @@ -1756,7 +1762,7 @@ sub GetLateOrMissingIssues { "SELECT serialid, aqbooksellerid, name, biblio.title, planneddate, serialseq, - serial.status, serial.subscriptionid, claimdate, + serial.status, serial.subscriptionid, claimdate, claims_count, subscription.branchcode FROM serial LEFT JOIN subscription ON serial.subscriptionid=subscription.subscriptionid @@ -1773,7 +1779,7 @@ sub GetLateOrMissingIssues { "SELECT serialid, aqbooksellerid, name, biblio.title, planneddate, serialseq, - serial.status, serial.subscriptionid, claimdate, + serial.status, serial.subscriptionid, claimdate, claims_count, subscription.branchcode FROM serial LEFT JOIN subscription ON serial.subscriptionid=subscription.subscriptionid @@ -1853,7 +1859,9 @@ sub updateClaim { my ($serialid) = @_; my $dbh = C4::Context->dbh; my $sth = $dbh->prepare( - "UPDATE serial SET claimdate = now() + "UPDATE serial + SET claimdate = now(), + claims_count = claims_count + 1 WHERE serialid = ? " ); diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 3137f38..fabf96a 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -1768,6 +1768,7 @@ CREATE TABLE `serial` ( `publisheddate` date default NULL, `itemnumber` text default NULL, `claimdate` date default NULL, + claims_count int(11) default 0, `routingnotes` text, PRIMARY KEY (`serialid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index dbbdee3..a89573d 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -5308,6 +5308,16 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) { SetVersion ($DBversion); } + + + +$DBversion = "3.07.00.XXX"; +if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { + $dbh->do("ALTER TABLE serial ADD COLUMN claims_count INT(11) DEFAULT 0"); + print "Upgrade to $DBversion done (Add claims_count field in serial table)\n"; + SetVersion($DBversion); +} + =head1 FUNCTIONS =head2 TableExists($table) diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/serials/claims.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/serials/claims.tt index 399c933..520f031 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/serials/claims.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/serials/claims.tt @@ -164,6 +164,17 @@

Claims

+ [% IF error_claim %] + [% IF ( error_claim == "no_email" ) %] +
This vendor has no email
+ [% ELSE %] +
[% error_claim %]
+ [% END %] + [% END %] + [% IF info_claim %] +
Email has been sent.
+ [% END %] + [% IF ( letter ) %][% UNLESS ( missingissues ) %][% IF ( supplierid ) %]
No missing issues found.
[% ELSE %]
Please choose a vendor.
[% END %][% END %][% END %] [% IF ( SHOWCONFIRMATION ) %] @@ -214,6 +225,8 @@
  • +
    • +
  • - - - [% IF ( letter ) %] - - [% END %] - - - - - - - - - + +
    VendorLibraryTitleIssue numberStatusSinceClaim dateBegin claim
    + + + [% IF ( letter ) %] + + [% END %] + + + + + + + + + + [% FOREACH missingissue IN missingissues %] [% IF ( letter ) %] [% END %] - - + + - + - - - + + + [% END %]
    VendorLibraryTitleIssue numberStatusSinceClaims countClaim date
    - + - [% missingissue.name %] - - [% missingissue.branchcode %] - [% missingissue.name %][% missingissue.branchcode %] [% missingissue.title |html %] - [% missingissue.serialseq %] - [% missingissue.serialseq %] [% IF ( missingissue.status1 ) %]Expected[% END %] [% IF ( missingissue.status2 ) %]Arrived[% END %] @@ -305,28 +313,26 @@ [% IF ( missingissue.status4 ) %]Missing[% END %] [% IF ( missingissue.status7 ) %]Claimed[% END %] - [% missingissue.planneddate %] - - [% missingissue.claimdate %] - - Export item data - [% missingissue.planneddate %][% missingissue.claims_count %][% missingissue.claimdate %]
    -

    - -[% IF ( letter ) %] -
    - -
    +

    + + [% IF ( letter ) %] +
    + + + + + +
    [% END %] diff --git a/serials/claims.pl b/serials/claims.pl index e51148c..70fc5c2 100755 --- a/serials/claims.pl +++ b/serials/claims.pl @@ -36,7 +36,6 @@ my $op = $input->param('op'); my $claimletter = $input->param('claimletter'); my $supplierid = $input->param('supplierid'); my $suppliername = $input->param('suppliername'); -my $order = $input->param('order'); # open template first (security & userenv set here) my ($template, $loggedinuser, $cookie) @@ -51,12 +50,38 @@ my ($template, $loggedinuser, $cookie) # supplierlist is returned in name order my $supplierlist = GetSuppliersWithLateIssues(); for my $s (@{$supplierlist} ) { - $s->{count} = scalar GetLateOrMissingIssues($s->{id}, q{}, $order); + $s->{count} = scalar GetLateOrMissingIssues($s->{id}, q{}); if ($supplierid && $s->{id} == $supplierid) { $s->{selected} = 1; } } +my $branchloop = GetBranchesLoop(); +unshift @$branchloop, {value=> 'all',name=>''}; + +my $preview=0; +if($op && $op eq 'preview'){ + $preview = 1; +} else { + my @serialnums=$input->param('serialid'); + if (@serialnums) { # i.e. they have been flagged to generate claims + my $err; + eval { + $err = SendAlerts('claimissues',\@serialnums,$input->param("letter_code")); + if ( not ref $err or not exists $err->{error} ) { + UpdateClaimdateIssues(\@serialnums); + } + }; + if ( $@ ) { + $template->param(error_claim => $@); + } elsif ( ref $err and exists $err->{error} and $err->{error} eq "no_email" ) { + $template->{VARS}->{'error_claim'} = "no_email"; + } else { + $template->{VARS}->{'info_claim'} = 1; + } + } +} + my $letters = GetLetters('claimissues'); my @letters; foreach (keys %{$letters}){ @@ -67,27 +92,12 @@ my $letter=((scalar(@letters)>1) || ($letters[0]->{name}||$letters[0]->{code})); my @missingissues; my @supplierinfo; if ($supplierid) { - @missingissues = GetLateOrMissingIssues($supplierid,$serialid,$order); + @missingissues = GetLateOrMissingIssues($supplierid); @supplierinfo=GetBookSeller($supplierid); } -my $branchloop = GetBranchesLoop(); -unshift @$branchloop, {value=> 'all',name=>''}; - -my $preview=0; -if($op && $op eq 'preview'){ - $preview = 1; -} else { - my @serialnums=$input->param('serialid'); - if (@serialnums) { # i.e. they have been flagged to generate claims - SendAlerts('claimissues',\@serialnums,$input->param("letter_code")); - my $cntupdate=UpdateClaimdateIssues(\@serialnums); - ### $cntupdate SHOULD be equal to scalar(@$serialnums) - } -} $template->param('letters'=>\@letters,'letter'=>$letter); $template->param( - order =>$order, suploop => $supplierlist, phone => $supplierinfo[0]->{phone}, booksellerfax => $supplierinfo[0]->{booksellerfax}, -- 1.7.7.3