Attachment #31277 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;

=cut

sub GetLateOrMissingIssues {
    my ( $supplierid ) = @_;

    return unless $supplierid;

    my $dbh = C4::Context->dbh;
    my $sth;
    $sth = $dbh->prepare(
        "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=?
            ORDER BY title"
    );
    $sth->execute( $supplierid );
                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
                ORDER BY $order"
        );
    } else {
        $sth = $dbh->prepare(
            "SELECT 
            serialid,      aqbooksellerid,         name,
            biblio.title,  planneddate,           serialseq,
                serial.status, serial.subscriptionid, claimdate,
                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"
        );
    }
    $sth->execute;
    my @issuelist;
    while ( my $line = $sth->fetchrow_hashref ) {


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 1935-1940 CREATE TABLE `serial` ( Link Here

(-)a/installer/data/mysql/kohastructure.sql (+1 lines)
1935	`notes` text,	1935	`notes` text,
1936	`publisheddate` date default NULL,	1936	`publisheddate` date default NULL,
1937	`claimdate` date default NULL,	1937	`claimdate` date default NULL,
		1938	claims_count int(11) default 0,
1938	`routingnotes` text,	1939	`routingnotes` text,
1939	PRIMARY KEY (`serialid`)	1940	PRIMARY KEY (`serialid`)
1940	) ENGINE=InnoDB DEFAULT CHARSET=utf8;	1941	) 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





         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.</div>
        [% ELSIF error_claim == 'no_loggedin_user_email' %]
            <div class="error">No email is configured for this account.</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 13-21 my $dbh = C4::Context->dbh; Link Here

(-)a/t/db_dependent/Acquisition/OrderFromSubscription.t (-3 lines)
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 (-57 / +33 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 1973-2029 name,title,planneddate,serialseq,serial.subscriptionid from tables : subscriptio Link Here
1973	=cut	1977	=cut
1974		1978
1975	sub GetLateOrMissingIssues {	1979	sub GetLateOrMissingIssues {
1976	my ( $supplierid, $serialid, $order ) = @_;	1980	my ( $supplierid ) = @_;
1977		1981
1978	return unless ( $supplierid or $serialid );	1982	return unless $supplierid;
1979		1983
1980	my $dbh = C4::Context->dbh;	1984	my $dbh = C4::Context->dbh;
1981	my $sth;	1985	my $sth;
1982	my $byserial = '';	1986	$sth = $dbh->prepare(
1983	if ($serialid) {	1987	"SELECT
1984	$byserial = "and serialid = " . $serialid;	1988	serialid, aqbooksellerid, name,
1985	}	1989	biblio.title, biblioitems.issn, planneddate, serialseq,
1986	if ($order) {	1990	serial.status, serial.subscriptionid, claimdate, claims_count,
1987	$order .= ", title";	1991	subscription.branchcode
1988	} else {	1992	FROM serial
1989	$order = "title";	1993	LEFT JOIN subscription ON serial.subscriptionid=subscription.subscriptionid
1990	}	1994	LEFT JOIN biblio ON subscription.biblionumber=biblio.biblionumber
1991	if ($supplierid) {	1995	LEFT JOIN biblioitems ON subscription.biblionumber=biblioitems.biblionumber
1992	$sth = $dbh->prepare(	1996	LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id
1993	"SELECT	1997	WHERE subscription.subscriptionid = serial.subscriptionid
1994	serialid, aqbooksellerid, name,	1998	AND (serial.STATUS IN (4, 41, 42, 43, 44) OR ((planneddate < now() AND serial.STATUS =1) OR serial.STATUS = 3 OR serial.STATUS = 7))
1995	biblio.title, biblioitems.issn, planneddate, serialseq,	1999	AND subscription.aqbooksellerid=?
1996	serial.status, serial.subscriptionid, claimdate,	2000	ORDER BY title"
1997	subscription.branchcode	2001	);
1998	FROM serial	2002	$sth->execute( $supplierid );
1999	LEFT JOIN subscription ON serial.subscriptionid=subscription.subscriptionid
2000	LEFT JOIN biblio ON subscription.biblionumber=biblio.biblionumber
2001	LEFT JOIN biblioitems ON subscription.biblionumber=biblioitems.biblionumber
2002	LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id
2003	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))
2005	AND subscription.aqbooksellerid=$supplierid
2006	$byserial
2007	ORDER BY $order"
2008	);
2009	} else {
2010	$sth = $dbh->prepare(
2011	"SELECT
2012	serialid, aqbooksellerid, name,
2013	biblio.title, planneddate, serialseq,
2014	serial.status, serial.subscriptionid, claimdate,
2015	subscription.branchcode
2016	FROM serial
2017	LEFT JOIN subscription ON serial.subscriptionid=subscription.subscriptionid
2018	LEFT JOIN biblio ON subscription.biblionumber=biblio.biblionumber
2019	LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id
2020	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))
2022	$byserial
2023	ORDER BY $order"
2024	);
2025	}
2026	$sth->execute;
2027	my @issuelist;	2003	my @issuelist;
2028	while ( my $line = $sth->fetchrow_hashref ) {	2004	while ( my $line = $sth->fetchrow_hashref ) {
2029		2005
Lines 2095-2106 called from claims.pl file Link Here
2095	sub updateClaim {	2071	sub updateClaim {
2096	my ($serialid) = @_;	2072	my ($serialid) = @_;
2097	my $dbh = C4::Context->dbh;	2073	my $dbh = C4::Context->dbh;
2098	my $sth = $dbh->prepare(	2074	$dbh->do(q\|
2099	"UPDATE serial SET claimdate = now()	2075	UPDATE serial
2100	WHERE serialid = ?	2076	SET claimdate = NOW(),
2101	"	2077	claims_count = claims_count + 1
2102	);	2078	WHERE serialid = ?
2103	$sth->execute($serialid);	2079	\|, {}, $serialid );
2104	return;	2080	return;
2105	}	2081	}
2106		2082

Lines 8669-8674 if ( CheckVersion($DBversion) ) { Link Here

(-)a/installer/data/mysql/updatedatabase.pl (+13 lines)
8669	SetVersion($DBversion);	8669	SetVersion($DBversion);
8670	}	8670	}
8671		8671
		8672	$DBversion = "3.17.00.XXX";
		8673	if ( CheckVersion($DBversion) ) {
		8674	$dbh->do(q\|
		8675	ALTER TABLE serial ADD COLUMN claims_count INT(11) DEFAULT 0 after claimdate
		8676	\|);
		8677	$dbh->do(q\|
		8678	UPDATE serial
		8679	SET claims_count = 1
		8680	WHERE claimdate IS NOT NULL
		8681	\|);
		8682	print "Upgrade to $DBversion done (Bug 5342: Add claims_count field in serial table)\n";
		8683	SetVersion($DBversion);
		8684	}
8672		8685
8673	=head1 FUNCTIONS	8686	=head1 FUNCTIONS
8674		8687

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.</div>
		179	[% ELSIF error_claim == 'no_loggedin_user_email' %]
		180	<div class="error">No email is configured for this account.</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	-