Attachment #32171 for bug #5342

View | Details | Raw Unified | Return to bug 5342
Collapse All | Expand All

Lines 368-374 sub SendAlerts { Link Here

(-)a/C4/Letters.pm (-1 / +5 lines)
368	Message => Encode::encode( "utf8", "" . $letter->{content} ),	368	Message => Encode::encode( "utf8", "" . $letter->{content} ),
369	'Content-Type' => 'text/plain; charset="utf8"',	369	'Content-Type' => 'text/plain; charset="utf8"',
370	);	370	);
371	sendmail(%mail) or carp $Mail::Sendmail::error;	371
		372	unless ( sendmail(%mail) ) {
		373	carp $Mail::Sendmail::error;
		374	return { error => $Mail::Sendmail::error };
		375	}
372		376
373	logaction(	377	logaction(
374	"ACQUISITION",	378	"ACQUISITION",




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 IN (3, 4, 41, 42, 43, 44, 7)
        )
        AND subscription.closed = 0
    ORDER BY name|;

    my $dbh = C4::Context->dbh;
    $date = strftime( "%Y-%m-%d", localtime ) unless ($date);
    my $query = "
        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;

            "SELECT
                serialid,      aqbooksellerid,        name,
                biblio.title,  biblioitems.issn,      planneddate,    serialseq,
                serial.status, serial.subscriptionid, claimdate, claims_count,
                subscription.branchcode
            FROM      serial
                LEFT JOIN subscription  ON serial.subscriptionid=subscription.subscriptionid
                LEFT JOIN biblio        ON subscription.biblionumber=biblio.biblionumber
                LEFT JOIN biblioitems   ON subscription.biblionumber=biblioitems.biblionumber
                LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id
                WHERE subscription.subscriptionid = serial.subscriptionid
                AND (serial.STATUS IN (4, 41, 42, 43, 44) OR ((planneddate < now() AND serial.STATUS =1) OR serial.STATUS = 3 OR serial.STATUS = 7))
                AND subscription.aqbooksellerid=$supplierid
                $byserial

        );
    } else {
        $sth = $dbh->prepare(
            "SELECT
            serialid,      aqbooksellerid,         name,
            biblio.title,  planneddate,           serialseq,
                serial.status, serial.subscriptionid, claimdate, claims_count,
                subscription.branchcode
            FROM serial
                LEFT JOIN subscription ON serial.subscriptionid=subscription.subscriptionid
                LEFT JOIN biblio ON subscription.biblionumber=biblio.biblionumber
                LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id
                WHERE subscription.subscriptionid = serial.subscriptionid
                        AND (serial.STATUS IN (4, 41, 42, 43, 44) OR ((planneddate < now() AND serial.STATUS =1) OR serial.STATUS = 3 OR serial.STATUS = 7))
                $byserial
                ORDER BY $order"

sub updateClaim {
    my ($serialid) = @_;
    my $dbh        = C4::Context->dbh;
    $dbh->do(q|
        UPDATE serial
        SET claimdate = NOW(),
            claims_count = claims_count + 1
        WHERE serialid = ?
    |, {}, $serialid );
    return;
}


Lines 1938-1943 CREATE TABLE `serial` ( Link Here

(-)a/installer/data/mysql/kohastructure.sql (+1 lines)
1938	`notes` text,	1938	`notes` text,
1939	`publisheddate` date default NULL,	1939	`publisheddate` date default NULL,
1940	`claimdate` date default NULL,	1940	`claimdate` date default NULL,
		1941	claims_count int(11) default 0,
1941	`routingnotes` text,	1942	`routingnotes` text,
1942	PRIMARY KEY (`serialid`)	1943	PRIMARY KEY (`serialid`)
1943	) ENGINE=InnoDB DEFAULT CHARSET=utf8;	1944	) ENGINE=InnoDB DEFAULT CHARSET=utf8;




    SetVersion($DBversion);
}

$DBversion = "3.17.00.XXX";
if ( CheckVersion($DBversion) ) {
    $dbh->do(q|
        ALTER TABLE serial ADD COLUMN claims_count INT(11) DEFAULT 0 after claimdate
    |);
    $dbh->do(q|
        UPDATE serial
        SET claims_count = 1
        WHERE claimdate IS NOT NULL
    |);
    print "Upgrade to $DBversion done (Bug 5342: Add claims_count field in serial table)\n";
    SetVersion($DBversion);
}

=head1 FUNCTIONS

=head2 TableExists($table)




         var sTable = $("#claimst").dataTable($.extend(true, {}, dataTablesDefaults, {
            "sDom": 't',
                "aoColumnDefs": [
                    { "aTargets": [ 0 ], "bSortable": false, "bSearchable": false },
                        { "aTargets": [ 0,1 ], "bSortable": false, "bSearchable": false },
                    [% ELSE %]
                        { "aTargets": [ 0 ], "bSortable": false, "bSearchable": false },
                    [% END %]
                    { 'sType': "anti-the", 'aTargets' : [ 'anti-the'] },
                    { 'sType': "title-string", 'aTargets' : [ 'title-string'] }
                ],

            $("#claimst tr:visible :checkbox").attr('checked', $("#CheckAll").is(':checked'));
        });

	    // Generates a dynamic link for exporting the selections data as CSV
	    $("#ExportSelected").click(function() {
               // We need to use "input[name=serialid]:checked" instead of "input:checked". Otherwise, the "check all" box will pass the value of "on" as a serialid, which produces a SQL error.
             var selected = $("input[name=serialid]:checked");


    <h1>Claims</h1>

    [% IF error_claim %]
        [% IF error_claim == 'no_vendor_email' %]
            <div class="error">This vendor has no email defined for late issues.</div>
        [% ELSIF error_claim == 'no_loggedin_user_email' %]
            <div class="error">No email is configured for your user.</div>
        [% ELSE %]
            <div class="error">[% error_claim %]</div>
        [% END %]
    [% END %]
    [% IF info_claim %]
        <div class="dialog message">Email has been sent.</div>
    [% END %]

[% IF letters %][% UNLESS ( missingissues ) %][% IF ( supplierid ) %] <div class="dialog alert">No missing issues found.</div>[% ELSE %]<div class="dialog message">Please choose a vendor.</div>[% END %][% END %][% END %]
	
	     [% IF ( SHOWCONFIRMATION ) %]

    </form>

    <fieldset>
        <form action="claims.pl" method="post" class="checkboxed" onsubmit="return checkForm()">
            <table id="claimst">
                <thead>
                    <tr>
                        <th><input type="checkbox" id="CheckAll"></th>
                        <th>Vendor</th>
                        <th>Library</th>
                        <th class="anti-the">Title</th>
                        <th>Issue number</th>
                        <th>Status</th>
                        <th class="title-string">Since</th>
                        <th>Claims count</th>
                        <th class="title-string">Claim date</th>
                    </tr>
                </thead>
                <tbody>[% FOREACH missingissue IN missingissues %]
                    <tr>
                        <td>
                            <input type="checkbox" name="serialid" value="[% missingissue.serialid %]" />
                        </td>
                        <td>[% missingissue.name %]</td>
                        [% missingissue.name %]
                        </td>
                        <td>
                            <span class="branch-[% missingissue.branchcode %]">[% Branches.GetName( missingissue.branchcode ) %]</span>
                        </td>
                        <td>
                        <a href="/cgi-bin/koha/serials/subscription-detail.pl?subscriptionid=[% missingissue.subscriptionid %]">[% missingissue.title |html %]</a>
                        </td>
                        <td>[% missingissue.serialseq %]</td>
                          [% missingissue.issn %]
                        </td>
                        <td>
                        [% missingissue.serialseq %]
                        </td>
                        <td>
                            [% IF ( missingissue.status1 ) %]<span class="status-expected">Expected</span>[% END %]
                            [% IF ( missingissue.status3 ) %]<span class="status-late">Late</span>[% END %]

                                <span title="0000-00-00"></span>
                            [% END %]
                        </td>
                        <td>[% missingissue.claims_count %]</td>
                        <td>
                            [% IF ( missingissue.claimdate ) %]
                                <span title="[% missingissue.claimdateISO %]">[% missingissue.claimdate %]</span>

                [% END %]</tbody>
            </table>

            [% IF csv_profiles %]
              <fieldset class="action">
                <label for="csv_code">Select CSV profile:</label>
                <select id="csv_profile_for_export">
                  [% FOR csv IN csv_profiles %]
                    <option value="[% csv.export_format_id %]">[% csv.profile %]</option>
                   [% END %]
                </select>
                <span class="exportSelected"><a id="ExportSelected" href="/cgi-bin/koha/serials/claims.pl">Download selected claims</a></span>
            [% END %]

            [% IF letters %]
                <fieldset class="action">
                    <label for="letter_code">Select notice:</label>
                    <select name="letter_code" id="letter_code">
                        [% FOREACH letter IN letters %]
                            <option value="[% letter.code %]">[% letter.name %]</option>
                        [% END %]
                    </select>
                    <input type="hidden" name="op" value="send_alert" />
                    <input type="hidden" name="supplierid" value="[% supplierid %]" />
                    <input type="submit" name="submit" class="button" value="Send notification" />
                </fieldset>
            [% END %]
        </form>
    </fieldset>




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)

# supplierlist is returned in name order
my $supplierlist = GetSuppliersWithLateIssues();
for my $s (@{$supplierlist} ) {
    $s->{count} = scalar  GetLateOrMissingIssues($s->{id});
    if ($supplierid && $s->{id} == $supplierid) {
        $s->{selected} = 1;
    }
}

my $letters = GetLetters({ module => 'claimissues' });

my @missingissues;
my @supplierinfo;
if ($supplierid) {
    @missingissues = GetLateOrMissingIssues($supplierid,$serialid,$order);
    @supplierinfo=GetBookSeller($supplierid);
}

my $branchloop = GetBranchesLoop();

my $preview=0;

} 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} ) {
            if ( $err->{error} eq "no_email" ) {
                $template->param( error_claim => 'no_vendor_email' );
            } elsif ( $err->{error} =~ m|Bad or missing From address| ) {
                $template->param( error_claim => 'no_loggedin_user_email' );
            }
        } else {
            $template->param( info_claim => 1 );
        }
    }
}

my $letters = GetLetters({ module => 'claimissues' });

my @missingissues;
my @supplierinfo;
if ($supplierid) {
    @missingissues = GetLateOrMissingIssues($supplierid);
    @supplierinfo=GetBookSeller($supplierid);
}

$template->param(
        order =>$order,
        suploop => $supplierlist,
        phone => $supplierinfo[0]->{phone},
        booksellerfax => $supplierinfo[0]->{booksellerfax},

Lines 1-6 Link Here

(-)a/t/db_dependent/Acquisition/OrderFromSubscription.t (-4 / +1 lines)
1	use Modern::Perl;	1	use Modern::Perl;
2		2
3	use Test::More tests => 13;	3	use Test::More tests => 12;
4	use Data::Dumper;	4	use Data::Dumper;
5		5
6	use_ok('C4::Acquisition');	6	use_ok('C4::Acquisition');
Lines 13-21 my $dbh = C4::Context->dbh; Link Here
13	$dbh->{AutoCommit} = 0;	13	$dbh->{AutoCommit} = 0;
14	$dbh->{RaiseError} = 1;	14	$dbh->{RaiseError} = 1;
15		15
16	my $supplierlist=eval{GetSuppliersWithLateIssues()};
17	ok(length($@)==0,"No SQL problem in GetSuppliersWithLateIssues");
18
19	my $booksellerid = C4::Bookseller::AddBookseller(	16	my $booksellerid = C4::Bookseller::AddBookseller(
20	{	17	{
21	name => "my vendor",	18	name => "my vendor",




use Modern::Perl;
use Test::More tests => 13;

use C4::Acquisition;
use C4::Budgets;
use_ok('C4::Serials');

use Koha::DateUtils qw( dt_from_string output_pref );

my $dbh = C4::Context->dbh;
$dbh->{AutoCommit} = 0;
$dbh->{RaiseError} = 1;

my $branchcode = 'CPL';
my $bpid = AddBudgetPeriod({
    budget_period_startdate   => '2015-01-01',
    budget_period_enddate     => '2015-12-31',
    budget_period_description => "budget desc"
});

my $budget_id = AddBudget({
    budget_code        => "ABCD",
    budget_amount      => "123.132",
    budget_name        => "Périodiques",
    budget_notes       => "This is a note",
    budget_period_id   => $bpid
});

my $record = MARC::Record->new();
my ( $biblionumber, $biblioitemnumber ) = C4::Biblio::AddBiblio($record, '');

my $sample_supplier1 = {
    name          => 'Name1',
    address1      => 'address1_1',
    address2      => 'address1-2',
    address3      => 'address1_2',
    address4      => 'address1_2',
    postal        => 'postal1',
    phone         => 'phone1',
    accountnumber => 'accountnumber1',
    fax           => 'fax1',
    url           => 'url1',
    active        => 1,
    gstreg        => 1,
    listincgst    => 1,
    invoiceincgst => 1,
    gstrate       => '1.0000',
    discount      => '1.0000',
    notes         => 'notes1',
    deliverytime  => undef
};
my $sample_supplier2 = {
    name          => 'Name2',
    address1      => 'address1_2',
    address2      => 'address2-2',
    address3      => 'address3_2',
    address4      => 'address4_2',
    postal        => 'postal2',
    phone         => 'phone2',
    accountnumber => 'accountnumber2',
    fax           => 'fax2',
    url           => 'url2',
    active        => 1,
    gstreg        => 1,
    listincgst    => 1,
    invoiceincgst => 1,
    gstrate       => '2.0000',
    discount      => '2.0000',
    notes         => 'notes2',
    deliverytime  => 2
};

my $supplier_id1 = C4::Bookseller::AddBookseller($sample_supplier1);
my $supplier_id2 = C4::Bookseller::AddBookseller($sample_supplier2);

my $supplierlist = eval { GetSuppliersWithLateIssues() };
is( length($@), 0, "No SQL problem in GetSuppliersWithLateIssues" );
is ( scalar(@$supplierlist), 0, 'There is no late issues yet');

my $subscriptionid_not_late = NewSubscription(
    undef,      $branchcode,     $supplier_id1, undef, $budget_id, $biblionumber,
    '2013-01-01', undef, undef, undef,  undef,
    undef,      undef,  undef, undef, undef, undef,
    1,          "notes",undef, '9999-01-01', undef, undef,
    undef,       undef,  0,    "intnotes",  0,
    undef, undef, 0,          undef,         '2013-12-31', 0
);
$supplierlist = GetSuppliersWithLateIssues();
is ( scalar(@$supplierlist), 0, 'There is still no late issues yet');

my $subscriptionid_inlate1 = NewSubscription(
    undef,      $branchcode,     $supplier_id1, undef, $budget_id, $biblionumber,
    '2013-01-01', undef, undef, undef,  undef,
    undef,      undef,  undef, undef, undef, undef,
    1,          "notes",undef, '2013-01-01', undef, undef,
    undef,       undef,  0,    "intnotes",  0,
    undef, undef, 0,          undef,         '2013-12-31', 0
);

my $subscriptionid_inlate2 = NewSubscription(
    undef,      $branchcode,     $supplier_id2, undef, $budget_id, $biblionumber,
    '2013-01-01', undef, undef, undef,  undef,
    undef,      undef,  undef, undef, undef, undef,
    1,          "notes",undef, '2013-01-01', undef, undef,
    undef,       undef,  0,    "intnotes",  0,
    undef, undef, 0,          undef,         '2013-12-31', 0
);

my $subscriptionid_inlate3 = NewSubscription(
    undef,      $branchcode,     $supplier_id2, undef, $budget_id, $biblionumber,
    '2013-01-02', undef, undef, undef,  undef,
    undef,      undef,  undef, undef, undef, undef,
    1,          "notes",undef, '2013-01-02', undef, undef,
    undef,       undef,  0,    "intnotes",  0,
    undef, undef, 0,          undef,         '2013-12-31', 0
);


$supplierlist = GetSuppliersWithLateIssues();
is ( scalar(@$supplierlist), 2, '2 suppliers should have issues in late');

is( GetLateOrMissingIssues(), undef, 'GetLateOrMissingIssues should return undef without parameter' );

my @late_or_missing_issues = GetLateOrMissingIssues( $supplier_id1 );
is( scalar(@late_or_missing_issues), 1, 'supplier 1 should have 1 issue in late' );

@late_or_missing_issues = GetLateOrMissingIssues( $supplier_id2);
is( scalar(@late_or_missing_issues), 2, 'supplier 2 should have 2 issues in late' );

is( exists $late_or_missing_issues[0]->{claimdate}, 1, 'GetLateOrMissingIssues returns claimdate' );
is( exists $late_or_missing_issues[0]->{claims_count}, 1, 'GetLateOrMissingIssues returns claims_count' );
is( $late_or_missing_issues[0]->{claims_count}, 0, 'The issues should not habe been claimed yet' );

my $serialid_to_claim = $late_or_missing_issues[0]->{serialid};
updateClaim( $serialid_to_claim );

@late_or_missing_issues = GetLateOrMissingIssues( $supplier_id2);
is( scalar(@late_or_missing_issues), 2, 'supplier 2 should have 2 issues in late (already claimed issues are returns)' );

my ( $serial_claimed ) = grep { ($_->{serialid} == $serialid_to_claim) ? $_ : () } @late_or_missing_issues;
is( $serial_claimed->{claims_count}, 1, 'The serial should have been claimed' );

my $today = output_pref({ dt => dt_from_string, dateformat => 'iso', dateonly => 1 });
# FIXME: This test should pass. The GetLateOrMissingIssues should not deal with date format!
#is( $serial_claimed->{claimdate}, $today, 'The serial should have been claimed today' );




#!/usr/bin/perl
use Modern::Perl;

use Test::More tests => 36;

use MARC::Record;


$dbh->{AutoCommit} = 0;
$dbh->{RaiseError} = 1;


my $supplierlist=eval{GetSuppliersWithLateIssues()};
ok(length($@)==0,"No SQL problem in GetSuppliersWithLateIssues");

my $record = MARC::Record->new();
$record->append_fields(
    MARC::Field->new( '952', '0', '0', a => 'CPL', b => 'CPL' )
- 

Return to bug 5342

Lines 96-109 the array is in name order Link Here

(-)a/C4/Serials.pm (-19 / +23 lines)
96	sub GetSuppliersWithLateIssues {	96	sub GetSuppliersWithLateIssues {
97	my $dbh = C4::Context->dbh;	97	my $dbh = C4::Context->dbh;
98	my $query = qq\|	98	my $query = qq\|
99	SELECT DISTINCT id, name	99	SELECT DISTINCT id, name
100	FROM subscription	100	FROM subscription
101	LEFT JOIN serial ON serial.subscriptionid=subscription.subscriptionid	101	LEFT JOIN serial ON serial.subscriptionid=subscription.subscriptionid
102	LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id	102	LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id
103	WHERE id > 0	103	WHERE id > 0
104	AND (	104	AND (
105	(planneddate < now() AND serial.status=1)	105	(planneddate < now() AND serial.status=1)
106	OR serial.STATUS IN (3, 4, 41, 42, 43, 44)	106	OR serial.STATUS IN (3, 4, 41, 42, 43, 44, 7)
107	)	107	)
108	AND subscription.closed = 0	108	AND subscription.closed = 0
109	ORDER BY name\|;	109	ORDER BY name\|;
Lines 310-317 sub UpdateClaimdateIssues { Link Here
310	my $dbh = C4::Context->dbh;	310	my $dbh = C4::Context->dbh;
311	$date = strftime( "%Y-%m-%d", localtime ) unless ($date);	311	$date = strftime( "%Y-%m-%d", localtime ) unless ($date);
312	my $query = "	312	my $query = "
313	UPDATE serial SET claimdate = ?, status = 7	313	UPDATE serial
314	WHERE serialid in (" . join( ",", map { '?' } @$serialids ) . ")";	314	SET claimdate = ?,
		315	status = 7,
		316	claims_count = claims_count + 1
		317	WHERE serialid in (" . join( ",", map { '?' } @$serialids ) . ")
		318	";
315	my $rq = $dbh->prepare($query);	319	my $rq = $dbh->prepare($query);
316	$rq->execute($date, @$serialids);	320	$rq->execute($date, @$serialids);
317	return $rq->rows;	321	return $rq->rows;
Lines 1993-2006 sub GetLateOrMissingIssues { Link Here
1993	"SELECT	1997	"SELECT
1994	serialid, aqbooksellerid, name,	1998	serialid, aqbooksellerid, name,
1995	biblio.title, biblioitems.issn, planneddate, serialseq,	1999	biblio.title, biblioitems.issn, planneddate, serialseq,
1996	serial.status, serial.subscriptionid, claimdate,	2000	serial.status, serial.subscriptionid, claimdate, claims_count,
1997	subscription.branchcode	2001	subscription.branchcode
1998	FROM serial	2002	FROM serial
1999	LEFT JOIN subscription ON serial.subscriptionid=subscription.subscriptionid	2003	LEFT JOIN subscription ON serial.subscriptionid=subscription.subscriptionid
2000	LEFT JOIN biblio ON subscription.biblionumber=biblio.biblionumber	2004	LEFT JOIN biblio ON subscription.biblionumber=biblio.biblionumber
2001	LEFT JOIN biblioitems ON subscription.biblionumber=biblioitems.biblionumber	2005	LEFT JOIN biblioitems ON subscription.biblionumber=biblioitems.biblionumber
2002	LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id	2006	LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id
2003	WHERE subscription.subscriptionid = serial.subscriptionid	2007	WHERE subscription.subscriptionid = serial.subscriptionid
2004	AND (serial.STATUS IN (4, 41, 42, 43, 44) OR ((planneddate < now() AND serial.STATUS =1) OR serial.STATUS = 3 OR serial.STATUS = 7))	2008	AND (serial.STATUS IN (4, 41, 42, 43, 44) OR ((planneddate < now() AND serial.STATUS =1) OR serial.STATUS = 3 OR serial.STATUS = 7))
2005	AND subscription.aqbooksellerid=$supplierid	2009	AND subscription.aqbooksellerid=$supplierid
2006	$byserial	2010	$byserial
Lines 2008-2023 sub GetLateOrMissingIssues { Link Here
2008	);	2012	);
2009	} else {	2013	} else {
2010	$sth = $dbh->prepare(	2014	$sth = $dbh->prepare(
2011	"SELECT	2015	"SELECT
2012	serialid, aqbooksellerid, name,	2016	serialid, aqbooksellerid, name,
2013	biblio.title, planneddate, serialseq,	2017	biblio.title, planneddate, serialseq,
2014	serial.status, serial.subscriptionid, claimdate,	2018	serial.status, serial.subscriptionid, claimdate, claims_count,
2015	subscription.branchcode	2019	subscription.branchcode
2016	FROM serial	2020	FROM serial
2017	LEFT JOIN subscription ON serial.subscriptionid=subscription.subscriptionid	2021	LEFT JOIN subscription ON serial.subscriptionid=subscription.subscriptionid
2018	LEFT JOIN biblio ON subscription.biblionumber=biblio.biblionumber	2022	LEFT JOIN biblio ON subscription.biblionumber=biblio.biblionumber
2019	LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id	2023	LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id
2020	WHERE subscription.subscriptionid = serial.subscriptionid	2024	WHERE subscription.subscriptionid = serial.subscriptionid
2021	AND (serial.STATUS IN (4, 41, 42, 43, 44) OR ((planneddate < now() AND serial.STATUS =1) OR serial.STATUS = 3 OR serial.STATUS = 7))	2025	AND (serial.STATUS IN (4, 41, 42, 43, 44) OR ((planneddate < now() AND serial.STATUS =1) OR serial.STATUS = 3 OR serial.STATUS = 7))
2022	$byserial	2026	$byserial
2023	ORDER BY $order"	2027	ORDER BY $order"
Lines 2095-2106 called from claims.pl file Link Here
2095	sub updateClaim {	2099	sub updateClaim {
2096	my ($serialid) = @_;	2100	my ($serialid) = @_;
2097	my $dbh = C4::Context->dbh;	2101	my $dbh = C4::Context->dbh;
2098	my $sth = $dbh->prepare(	2102	$dbh->do(q\|
2099	"UPDATE serial SET claimdate = now()	2103	UPDATE serial
2100	WHERE serialid = ?	2104	SET claimdate = NOW(),
2101	"	2105	claims_count = claims_count + 1
2102	);	2106	WHERE serialid = ?
2103	$sth->execute($serialid);	2107	\|, {}, $serialid );
2104	return;	2108	return;
2105	}	2109	}
2106		2110

Lines 8781-8786 if ( CheckVersion($DBversion) ) { Link Here

(-)a/installer/data/mysql/updatedatabase.pl (+14 lines)
8781	SetVersion($DBversion);	8781	SetVersion($DBversion);
8782	}	8782	}
8783		8783
		8784	$DBversion = "3.17.00.XXX";
		8785	if ( CheckVersion($DBversion) ) {
		8786	$dbh->do(q\|
		8787	ALTER TABLE serial ADD COLUMN claims_count INT(11) DEFAULT 0 after claimdate
		8788	\|);
		8789	$dbh->do(q\|
		8790	UPDATE serial
		8791	SET claims_count = 1
		8792	WHERE claimdate IS NOT NULL
		8793	\|);
		8794	print "Upgrade to $DBversion done (Bug 5342: Add claims_count field in serial table)\n";
		8795	SetVersion($DBversion);
		8796	}
		8797
8784	=head1 FUNCTIONS	8798	=head1 FUNCTIONS
8785		8799
8786	=head2 TableExists($table)	8800	=head2 TableExists($table)

Lines 13-23 Link Here

(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/serials/claims.tt (-47 / +55 lines)
13	var sTable = $("#claimst").dataTable($.extend(true, {}, dataTablesDefaults, {	13	var sTable = $("#claimst").dataTable($.extend(true, {}, dataTablesDefaults, {
14	"sDom": 't',	14	"sDom": 't',
15	"aoColumnDefs": [	15	"aoColumnDefs": [
16	[% IF ( letter ) %]	16	{ "aTargets": [ 0 ], "bSortable": false, "bSearchable": false },
17	{ "aTargets": [ 0,1 ], "bSortable": false, "bSearchable": false },
18	[% ELSE %]
19	{ "aTargets": [ 0 ], "bSortable": false, "bSearchable": false },
20	[% END %]
21	{ 'sType': "anti-the", 'aTargets' : [ 'anti-the'] },	17	{ 'sType': "anti-the", 'aTargets' : [ 'anti-the'] },
22	{ 'sType': "title-string", 'aTargets' : [ 'title-string'] }	18	{ 'sType': "title-string", 'aTargets' : [ 'title-string'] }
23	],	19	],
Lines 35-41 Link Here
35	$("#claimst tr:visible :checkbox").attr('checked', $("#CheckAll").is(':checked'));	31	$("#claimst tr:visible :checkbox").attr('checked', $("#CheckAll").is(':checked'));
36	});	32	});
37		33
38	// Generates a dynamic link for exporting the selection's data as CSV	34	// Generates a dynamic link for exporting the selections data as CSV
39	$("#ExportSelected").click(function() {	35	$("#ExportSelected").click(function() {
40	// We need to use "input[name=serialid]:checked" instead of "input:checked". Otherwise, the "check all" box will pass the value of "on" as a serialid, which produces a SQL error.	36	// We need to use "input[name=serialid]:checked" instead of "input:checked". Otherwise, the "check all" box will pass the value of "on" as a serialid, which produces a SQL error.
41	var selected = $("input[name=serialid]:checked");	37	var selected = $("input[name=serialid]:checked");
Lines 177-182 Link Here
177		173
178	<h1>Claims</h1>	174	<h1>Claims</h1>
179		175
		176	[% IF error_claim %]
		177	[% IF error_claim == 'no_vendor_email' %]
		178	<div class="error">This vendor has no email defined for late issues.</div>
		179	[% ELSIF error_claim == 'no_loggedin_user_email' %]
		180	<div class="error">No email is configured for your user.</div>
		181	[% ELSE %]
		182	<div class="error">[% error_claim %]</div>
		183	[% END %]
		184	[% END %]
		185	[% IF info_claim %]
		186	<div class="dialog message">Email has been sent.</div>
		187	[% END %]
		188
180	[% IF letters %][% UNLESS ( missingissues ) %][% IF ( supplierid ) %] <div class="dialog alert">No missing issues found.</div>[% ELSE %]<div class="dialog message">Please choose a vendor.</div>[% END %][% END %][% END %]	189	[% IF letters %][% UNLESS ( missingissues ) %][% IF ( supplierid ) %] <div class="dialog alert">No missing issues found.</div>[% ELSE %]<div class="dialog message">Please choose a vendor.</div>[% END %][% END %][% END %]
181		190
182	[% IF ( SHOWCONFIRMATION ) %]	191	[% IF ( SHOWCONFIRMATION ) %]
Lines 258-297 Link Here
258	</form>	267	</form>
259		268
260	<fieldset>	269	<fieldset>
261	<form action="claims.pl" method="post" class="checkboxed" onsubmit="return checkForm()">	270	<form action="claims.pl" method="post" class="checkboxed" onsubmit="return checkForm()">
262	<input type="hidden" name="order" value="[% order %]" />	271	<table id="claimst">
263	<table id="claimst">	272	<thead>
264	<thead><tr>	273	<tr>
265	<th><input type="checkbox" id="CheckAll"></th>	274	<th><input type="checkbox" id="CheckAll"></th>
266	<th>Vendor</th>	275	<th>Vendor</th>
267	<th>Library</th>	276	<th>Library</th>
268	<th class="anti-the">Title</th>	277	<th class="anti-the">Title</th>
269	<th>ISSN</th>	278	<th>Issue number</th>
270	<th>Issue number</th>	279	<th>Status</th>
271	<th>Status</th>	280	<th class="title-string">Since</th>
272	<th class="title-string">Since</th>	281	<th>Claims count</th>
273	<th class="title-string">Claim date</th>	282	<th class="title-string">Claim date</th>
274	</tr></thead>	283	</tr>
		284	</thead>
275	<tbody>[% FOREACH missingissue IN missingissues %]	285	<tbody>[% FOREACH missingissue IN missingissues %]
276	<tr>	286	<tr>
277	<td>	287	<td>
278	<input type="checkbox" name="serialid" value="[% missingissue.serialid %]" />	288	<input type="checkbox" name="serialid" value="[% missingissue.serialid %]" />
279	</td>	289	</td>
280	<td>	290	<td>[% missingissue.name %]</td>
281	[% missingissue.name %]
282	</td>
283	<td>	291	<td>
284	<span class="branch-[% missingissue.branchcode %]">[% Branches.GetName( missingissue.branchcode ) %]</span>	292	<span class="branch-[% missingissue.branchcode %]">[% Branches.GetName( missingissue.branchcode ) %]</span>
285	</td>	293	</td>
286	<td>	294	<td>
287	<a href="/cgi-bin/koha/serials/subscription-detail.pl?subscriptionid=[% missingissue.subscriptionid %]">[% missingissue.title \|html %]</a>	295	<a href="/cgi-bin/koha/serials/subscription-detail.pl?subscriptionid=[% missingissue.subscriptionid %]">[% missingissue.title \|html %]</a>
288	</td>	296	</td>
289	<td>	297	<td>[% missingissue.serialseq %]</td>
290	[% missingissue.issn %]
291	</td>
292	<td>
293	[% missingissue.serialseq %]
294	</td>
295	<td>	298	<td>
296	[% IF ( missingissue.status1 ) %]<span class="status-expected">Expected</span>[% END %]	299	[% IF ( missingissue.status1 ) %]<span class="status-expected">Expected</span>[% END %]
297	[% IF ( missingissue.status3 ) %]<span class="status-late">Late</span>[% END %]	300	[% IF ( missingissue.status3 ) %]<span class="status-late">Late</span>[% END %]
Lines 309-314 Link Here
309	<span title="0000-00-00"></span>	312	<span title="0000-00-00"></span>
310	[% END %]	313	[% END %]
311	</td>	314	</td>
		315	<td>[% missingissue.claims_count %]</td>
312	<td>	316	<td>
313	[% IF ( missingissue.claimdate ) %]	317	[% IF ( missingissue.claimdate ) %]
314	<span title="[% missingissue.claimdateISO %]">[% missingissue.claimdate %]</span>	318	<span title="[% missingissue.claimdateISO %]">[% missingissue.claimdate %]</span>
Lines 320-344 Link Here
320	[% END %]</tbody>	324	[% END %]</tbody>
321	</table>	325	</table>
322		326
323	[% IF csv_profiles %]	327	[% IF csv_profiles %]
324	<fieldset class="action">	328	<fieldset class="action">
325	<label for="csv_code">Select CSV profile:</label>	329	<label for="csv_code">Select CSV profile:</label>
326	<select id="csv_profile_for_export">	330	<select id="csv_profile_for_export">
327	[% FOR csv IN csv_profiles %]	331	[% FOR csv IN csv_profiles %]
328	<option value="[% csv.export_format_id %]">[% csv.profile %]</option>	332	<option value="[% csv.export_format_id %]">[% csv.profile %]</option>
329	[% END %]	333	[% END %]
330	</select>	334	</select>
331	<span class="exportSelected"><a id="ExportSelected" href="/cgi-bin/koha/serials/claims.pl">Download selected claims</a></span>	335	<span class="exportSelected"><a id="ExportSelected" href="/cgi-bin/koha/serials/claims.pl">Download selected claims</a></span>
332	[% END %]	336	[% END %]
333		337
334	[% IF ( letters ) %]	338	[% IF letters %]
335	<fieldset class="action"> <label for="letter_code">Select notice:</label>	339	<fieldset class="action">
336	<select name="letter_code" id="letter_code">	340	<label for="letter_code">Select notice:</label>
337	[% FOREACH letter IN letters %]	341	<select name="letter_code" id="letter_code">
338	<option value="[% letter.code %]">[% letter.name %]</option>	342	[% FOREACH letter IN letters %]
339	[% END %]	343	<option value="[% letter.code %]">[% letter.name %]</option>
340	</select>	344	[% END %]
341	<input type="hidden" name="op" value="send_alert" /><input type="submit" name="submit" class="button" value="Send notification" /></fieldset>	345	</select>
		346	<input type="hidden" name="op" value="send_alert" />
		347	<input type="hidden" name="supplierid" value="[% supplierid %]" />
		348	<input type="submit" name="submit" class="button" value="Send notification" />
		349	</fieldset>
342	[% END %]	350	[% END %]
343	</form>	351	</form>
344	</fieldset>	352	</fieldset>

Lines 37-43 my $op = $input->param('op'); Link Here

(-)a/serials/claims.pl (-15 / +28 lines)
37	my $claimletter = $input->param('claimletter');	37	my $claimletter = $input->param('claimletter');
38	my $supplierid = $input->param('supplierid');	38	my $supplierid = $input->param('supplierid');
39	my $suppliername = $input->param('suppliername');	39	my $suppliername = $input->param('suppliername');
40	my $order = $input->param('order');
41		40
42	# open template first (security & userenv set here)	41	# open template first (security & userenv set here)
43	my ($template, $loggedinuser, $cookie)	42	my ($template, $loggedinuser, $cookie)
Lines 52-72 my ($template, $loggedinuser, $cookie) Link Here
52	# supplierlist is returned in name order	51	# supplierlist is returned in name order
53	my $supplierlist = GetSuppliersWithLateIssues();	52	my $supplierlist = GetSuppliersWithLateIssues();
54	for my $s (@{$supplierlist} ) {	53	for my $s (@{$supplierlist} ) {
55	$s->{count} = scalar GetLateOrMissingIssues($s->{id}, q{}, $order);	54	$s->{count} = scalar GetLateOrMissingIssues($s->{id});
56	if ($supplierid && $s->{id} == $supplierid) {	55	if ($supplierid && $s->{id} == $supplierid) {
57	$s->{selected} = 1;	56	$s->{selected} = 1;
58	}	57	}
59	}	58	}
60		59
61	my $letters = GetLetters({ module => 'claimissues' });
62
63	my @missingissues;
64	my @supplierinfo;
65	if ($supplierid) {
66	@missingissues = GetLateOrMissingIssues($supplierid,$serialid,$order);
67	@supplierinfo=GetBookSeller($supplierid);
68	}
69
70	my $branchloop = GetBranchesLoop();	60	my $branchloop = GetBranchesLoop();
71		61
72	my $preview=0;	62	my $preview=0;
Lines 75-88 if($op && $op eq 'preview'){ Link Here
75	} else {	65	} else {
76	my @serialnums=$input->param('serialid');	66	my @serialnums=$input->param('serialid');
77	if (@serialnums) { # i.e. they have been flagged to generate claims	67	if (@serialnums) { # i.e. they have been flagged to generate claims
78	SendAlerts('claimissues',\@serialnums,$input->param("letter_code"));	68	my $err;
79	my $cntupdate=UpdateClaimdateIssues(\@serialnums);	69	eval {
80	### $cntupdate SHOULD be equal to scalar(@$serialnums)	70	$err = SendAlerts('claimissues',\@serialnums,$input->param("letter_code"));
		71	if ( not ref $err or not exists $err->{error} ) {
		72	UpdateClaimdateIssues(\@serialnums);
		73	}
		74	};
		75	if ( $@ ) {
		76	$template->param(error_claim => $@);
		77	} elsif ( ref $err and exists $err->{error} ) {
		78	if ( $err->{error} eq "no_email" ) {
		79	$template->param( error_claim => 'no_vendor_email' );
		80	} elsif ( $err->{error} =~ m\|Bad or missing From address\| ) {
		81	$template->param( error_claim => 'no_loggedin_user_email' );
		82	}
		83	} else {
		84	$template->param( info_claim => 1 );
		85	}
81	}	86	}
82	}	87	}
83		88
		89	my $letters = GetLetters({ module => 'claimissues' });
		90
		91	my @missingissues;
		92	my @supplierinfo;
		93	if ($supplierid) {
		94	@missingissues = GetLateOrMissingIssues($supplierid);
		95	@supplierinfo=GetBookSeller($supplierid);
		96	}
		97
84	$template->param(	98	$template->param(
85	order =>$order,
86	suploop => $supplierlist,	99	suploop => $supplierlist,
87	phone => $supplierinfo[0]->{phone},	100	phone => $supplierinfo[0]->{phone},
88	booksellerfax => $supplierinfo[0]->{booksellerfax},	101	booksellerfax => $supplierinfo[0]->{booksellerfax},

Line 0 Link Here

(-)a/t/db_dependent/Serials/Claims.t (+145 lines)
		1	use Modern::Perl;
		2	use Test::More tests => 13;
		3
		4	use C4::Acquisition;
		5	use C4::Budgets;
		6	use_ok('C4::Serials');
		7
		8	use Koha::DateUtils qw( dt_from_string output_pref );
		9
		10	my $dbh = C4::Context->dbh;
		11	$dbh->{AutoCommit} = 0;
		12	$dbh->{RaiseError} = 1;
		13
		14	my $branchcode = 'CPL';
		15	my $bpid = AddBudgetPeriod({
		16	budget_period_startdate => '2015-01-01',
		17	budget_period_enddate => '2015-12-31',
		18	budget_period_description => "budget desc"
		19	});
		20
		21	my $budget_id = AddBudget({
		22	budget_code => "ABCD",
		23	budget_amount => "123.132",
		24	budget_name => "Périodiques",
		25	budget_notes => "This is a note",
		26	budget_period_id => $bpid
		27	});
		28
		29	my $record = MARC::Record->new();
		30	my ( $biblionumber, $biblioitemnumber ) = C4::Biblio::AddBiblio($record, '');
		31
		32	my $sample_supplier1 = {
		33	name => 'Name1',
		34	address1 => 'address1_1',
		35	address2 => 'address1-2',
		36	address3 => 'address1_2',
		37	address4 => 'address1_2',
		38	postal => 'postal1',
		39	phone => 'phone1',
		40	accountnumber => 'accountnumber1',
		41	fax => 'fax1',
		42	url => 'url1',
		43	active => 1,
		44	gstreg => 1,
		45	listincgst => 1,
		46	invoiceincgst => 1,
		47	gstrate => '1.0000',
		48	discount => '1.0000',
		49	notes => 'notes1',
		50	deliverytime => undef
		51	};
		52	my $sample_supplier2 = {
		53	name => 'Name2',
		54	address1 => 'address1_2',
		55	address2 => 'address2-2',
		56	address3 => 'address3_2',
		57	address4 => 'address4_2',
		58	postal => 'postal2',
		59	phone => 'phone2',
		60	accountnumber => 'accountnumber2',
		61	fax => 'fax2',
		62	url => 'url2',
		63	active => 1,
		64	gstreg => 1,
65	listincgst => 1,
66	invoiceincgst => 1,
67	gstrate => '2.0000',
68	discount => '2.0000',
69	notes => 'notes2',
70	deliverytime => 2
71	};
72
73	my $supplier_id1 = C4::Bookseller::AddBookseller($sample_supplier1);
74	my $supplier_id2 = C4::Bookseller::AddBookseller($sample_supplier2);
75
76	my $supplierlist = eval { GetSuppliersWithLateIssues() };
77	is( length($@), 0, "No SQL problem in GetSuppliersWithLateIssues" );
78	is ( scalar(@$supplierlist), 0, 'There is no late issues yet');
79
80	my $subscriptionid_not_late = NewSubscription(
81	undef, $branchcode, $supplier_id1, undef, $budget_id, $biblionumber,
82	'2013-01-01', undef, undef, undef, undef,
83	undef, undef, undef, undef, undef, undef,
84	1, "notes",undef, '9999-01-01', undef, undef,
85	undef, undef, 0, "intnotes", 0,
86	undef, undef, 0, undef, '2013-12-31', 0
87	);
88	$supplierlist = GetSuppliersWithLateIssues();
89	is ( scalar(@$supplierlist), 0, 'There is still no late issues yet');
90
91	my $subscriptionid_inlate1 = NewSubscription(
92	undef, $branchcode, $supplier_id1, undef, $budget_id, $biblionumber,
93	'2013-01-01', undef, undef, undef, undef,
94	undef, undef, undef, undef, undef, undef,
95	1, "notes",undef, '2013-01-01', undef, undef,
96	undef, undef, 0, "intnotes", 0,
97	undef, undef, 0, undef, '2013-12-31', 0
98	);
99
100	my $subscriptionid_inlate2 = NewSubscription(
101	undef, $branchcode, $supplier_id2, undef, $budget_id, $biblionumber,
102	'2013-01-01', undef, undef, undef, undef,
103	undef, undef, undef, undef, undef, undef,
104	1, "notes",undef, '2013-01-01', undef, undef,
105	undef, undef, 0, "intnotes", 0,
106	undef, undef, 0, undef, '2013-12-31', 0
107	);
108
109	my $subscriptionid_inlate3 = NewSubscription(
110	undef, $branchcode, $supplier_id2, undef, $budget_id, $biblionumber,
111	'2013-01-02', undef, undef, undef, undef,
112	undef, undef, undef, undef, undef, undef,
113	1, "notes",undef, '2013-01-02', undef, undef,
114	undef, undef, 0, "intnotes", 0,
115	undef, undef, 0, undef, '2013-12-31', 0
116	);
117
118
119	$supplierlist = GetSuppliersWithLateIssues();
120	is ( scalar(@$supplierlist), 2, '2 suppliers should have issues in late');
121
122	is( GetLateOrMissingIssues(), undef, 'GetLateOrMissingIssues should return undef without parameter' );
123
124	my @late_or_missing_issues = GetLateOrMissingIssues( $supplier_id1 );
125	is( scalar(@late_or_missing_issues), 1, 'supplier 1 should have 1 issue in late' );
126
127	@late_or_missing_issues = GetLateOrMissingIssues( $supplier_id2);
128	is( scalar(@late_or_missing_issues), 2, 'supplier 2 should have 2 issues in late' );
129
130	is( exists $late_or_missing_issues[0]->{claimdate}, 1, 'GetLateOrMissingIssues returns claimdate' );
131	is( exists $late_or_missing_issues[0]->{claims_count}, 1, 'GetLateOrMissingIssues returns claims_count' );
132	is( $late_or_missing_issues[0]->{claims_count}, 0, 'The issues should not habe been claimed yet' );
133
134	my $serialid_to_claim = $late_or_missing_issues[0]->{serialid};
135	updateClaim( $serialid_to_claim );
136
137	@late_or_missing_issues = GetLateOrMissingIssues( $supplier_id2);
138	is( scalar(@late_or_missing_issues), 2, 'supplier 2 should have 2 issues in late (already claimed issues are returns)' );
139
140	my ( $serial_claimed ) = grep { ($_->{serialid} == $serialid_to_claim) ? $_ : () } @late_or_missing_issues;
141	is( $serial_claimed->{claims_count}, 1, 'The serial should have been claimed' );
142
143	my $today = output_pref({ dt => dt_from_string, dateformat => 'iso', dateonly => 1 });
144	# FIXME: This test should pass. The GetLateOrMissingIssues should not deal with date format!
145	#is( $serial_claimed->{claimdate}, $today, 'The serial should have been claimed today' );

Lines 1-7 Link Here

(-)a/t/db_dependent/Serials_2.t (-6 / +1 lines)
1	#!/usr/bin/perl	1	#!/usr/bin/perl
2	use Modern::Perl;	2	use Modern::Perl;
3		3
4	use Test::More tests => 37;	4	use Test::More tests => 36;
5		5
6	use MARC::Record;	6	use MARC::Record;
7		7
Lines 20-29 my $dbh = C4::Context->dbh; Link Here
20	$dbh->{AutoCommit} = 0;	20	$dbh->{AutoCommit} = 0;
21	$dbh->{RaiseError} = 1;	21	$dbh->{RaiseError} = 1;
22		22
23
24	my $supplierlist=eval{GetSuppliersWithLateIssues()};
25	ok(length($@)==0,"No SQL problem in GetSuppliersWithLateIssues");
26
27	my $record = MARC::Record->new();	23	my $record = MARC::Record->new();
28	$record->append_fields(	24	$record->append_fields(
29	MARC::Field->new( '952', '0', '0', a => 'CPL', b => 'CPL' )	25	MARC::Field->new( '952', '0', '0', a => 'CPL', b => 'CPL' )
30	-