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

(-)a/C4/Serials.pm (-41 / +57 lines)
Lines 121-137 sub GetSuppliersWithLateIssues { Link Here
121
    my $dbh   = C4::Context->dbh;
121
    my $dbh   = C4::Context->dbh;
122
    my $statuses = join(',', ( LATE, MISSING_STATUSES, CLAIMED ) );
122
    my $statuses = join(',', ( LATE, MISSING_STATUSES, CLAIMED ) );
123
    my $query = qq|
123
    my $query = qq|
124
    SELECT DISTINCT id, name
124
        SELECT DISTINCT aqbooksellers.id, aqbooksellers.name
125
    FROM            subscription
125
        FROM aqbooksellers
126
    LEFT JOIN       serial ON serial.subscriptionid=subscription.subscriptionid
126
        LEFT JOIN serial ON serial.aqbooksellerid = aqbooksellers.id
127
    LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id
127
        LEFT JOIN subscription ON serial.subscriptionid = subscription.subscriptionid
128
    WHERE id > 0
128
        WHERE (
129
        AND (
129
                (serial.planneddate < NOW() AND serial.status = 1)
130
            (planneddate < now() AND serial.status=1)
130
                OR serial.status IN ( $statuses )
131
            OR serial.STATUS IN ( $statuses )
131
            )
132
        )
132
            AND subscription.closed = 0
133
        AND subscription.closed = 0
133
        ORDER BY name
134
    ORDER BY name|;
134
    |;
135
    return $dbh->selectall_arrayref($query, { Slice => {} });
135
    return $dbh->selectall_arrayref($query, { Slice => {} });
136
}
136
}
137
137
Lines 1433-1438 sub NewSubscription { Link Here
1433
            serialseq_y    => $subscription->{'lastvalue2'},
1433
            serialseq_y    => $subscription->{'lastvalue2'},
1434
            serialseq_z    => $subscription->{'lastvalue3'},
1434
            serialseq_z    => $subscription->{'lastvalue3'},
1435
            subscriptionid => $subscriptionid,
1435
            subscriptionid => $subscriptionid,
1436
            aqbooksellerid => $subscription->{aqbooksellerid},
1436
            biblionumber   => $biblionumber,
1437
            biblionumber   => $biblionumber,
1437
            status         => EXPECTED,
1438
            status         => EXPECTED,
1438
            planneddate    => $firstacquidate,
1439
            planneddate    => $firstacquidate,
Lines 1543-1548 sub NewIssue { Link Here
1543
            serialseq_y       => $subscription->lastvalue2(),
1544
            serialseq_y       => $subscription->lastvalue2(),
1544
            serialseq_z       => $subscription->lastvalue3(),
1545
            serialseq_z       => $subscription->lastvalue3(),
1545
            subscriptionid    => $subscriptionid,
1546
            subscriptionid    => $subscriptionid,
1547
            aqbooksellerid    => $subscription->aqbooksellerid(),
1546
            biblionumber      => $biblionumber,
1548
            biblionumber      => $biblionumber,
1547
            status            => $status,
1549
            status            => $status,
1548
            planneddate       => $planneddate,
1550
            planneddate       => $planneddate,
Lines 1769-1775 sub GetLateOrMissingIssues { Link Here
1769
    my $sth;
1771
    my $sth;
1770
    my $byserial = '';
1772
    my $byserial = '';
1771
    if ($serialid) {
1773
    if ($serialid) {
1772
        $byserial = "and serialid = " . $serialid;
1774
        $byserial = "and s.serialid = " . $serialid;
1773
    }
1775
    }
1774
    if ($order) {
1776
    if ($order) {
1775
        $order .= ", title";
1777
        $order .= ", title";
Lines 1778-1816 sub GetLateOrMissingIssues { Link Here
1778
    }
1780
    }
1779
    my $missing_statuses_string = join ',', (MISSING_STATUSES);
1781
    my $missing_statuses_string = join ',', (MISSING_STATUSES);
1780
    if ($supplierid) {
1782
    if ($supplierid) {
1781
        $sth = $dbh->prepare(
1783
        $sth = $dbh->prepare(qq{
1782
            "SELECT
1784
            SELECT
1783
                serialid,      aqbooksellerid,        name,
1785
                s.serialid, s.aqbooksellerid, aqbooksellers.name,
1784
                biblio.title,  biblioitems.issn,      planneddate,    serialseq,
1786
                biblio.title, biblioitems.issn, s.planneddate, s.serialseq,
1785
                serial.status, serial.subscriptionid, claimdate, claims_count,
1787
                s.status, s.subscriptionid, s.claimdate, s.claims_count,
1786
                subscription.branchcode
1788
                subscription.branchcode
1787
            FROM      serial
1789
            FROM serial s
1788
                LEFT JOIN subscription  ON serial.subscriptionid=subscription.subscriptionid
1790
                LEFT JOIN subscription  ON s.subscriptionid = subscription.subscriptionid
1789
                LEFT JOIN biblio        ON subscription.biblionumber=biblio.biblionumber
1791
                LEFT JOIN biblio        ON subscription.biblionumber = biblio.biblionumber
1790
                LEFT JOIN biblioitems   ON subscription.biblionumber=biblioitems.biblionumber
1792
                LEFT JOIN biblioitems   ON subscription.biblionumber = biblioitems.biblionumber
1791
                LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id
1793
                LEFT JOIN aqbooksellers ON s.aqbooksellerid = aqbooksellers.id
1792
                WHERE subscription.subscriptionid = serial.subscriptionid
1794
                WHERE
1793
                AND (serial.STATUS IN ($missing_statuses_string) OR ((planneddate < now() AND serial.STATUS = ?) OR serial.STATUS = ? OR serial.STATUS = ?))
1795
                    (
1794
                AND subscription.aqbooksellerid=$supplierid
1796
                        s.status IN ($missing_statuses_string)
1795
                $byserial
1797
                        OR (
1796
                ORDER BY $order"
1798
                            (s.planneddate < NOW() AND s.status = ?)
1797
        );
1799
                            OR s.status = ?
1800
                            OR s.status = ?
1801
                        )
1802
                    )
1803
                    AND s.aqbooksellerid = $supplierid
1804
                    $byserial
1805
                ORDER BY $order
1806
        });
1798
    } else {
1807
    } else {
1799
        $sth = $dbh->prepare(
1808
        $sth = $dbh->prepare(qq{
1800
            "SELECT
1809
            SELECT
1801
            serialid,      aqbooksellerid,         name,
1810
                s.serialid, s.aqbooksellerid, aqbooksellers.name,
1802
            biblio.title,  planneddate,           serialseq,
1811
                biblio.title, s.planneddate, s.serialseq,
1803
                serial.status, serial.subscriptionid, claimdate, claims_count,
1812
                s.status, s.subscriptionid, s.claimdate, s.claims_count,
1804
                subscription.branchcode
1813
                subscription.branchcode
1805
            FROM serial
1814
            FROM serial s
1806
                LEFT JOIN subscription ON serial.subscriptionid=subscription.subscriptionid
1815
                LEFT JOIN subscription ON s.subscriptionid = subscription.subscriptionid
1807
                LEFT JOIN biblio ON subscription.biblionumber=biblio.biblionumber
1816
                LEFT JOIN biblio ON subscription.biblionumber = biblio.biblionumber
1808
                LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id
1817
                LEFT JOIN aqbooksellers ON s.aqbooksellerid = aqbooksellers.id
1809
                WHERE subscription.subscriptionid = serial.subscriptionid
1818
            WHERE
1810
                        AND (serial.STATUS IN ($missing_statuses_string) OR ((planneddate < now() AND serial.STATUS = ?) OR serial.STATUS = ? OR serial.STATUS = ?))
1819
                (
1820
                    s.status IN ($missing_statuses_string)
1821
                    OR (
1822
                        (s.planneddate < now() AND s.status = ?)
1823
                        OR s.status = ?
1824
                        OR s.status = ?
1825
                    )
1826
                )
1811
                $byserial
1827
                $byserial
1812
                ORDER BY $order"
1828
            ORDER BY $order
1813
        );
1829
        });
1814
    }
1830
    }
1815
    $sth->execute( EXPECTED, LATE, CLAIMED );
1831
    $sth->execute( EXPECTED, LATE, CLAIMED );
1816
    my @issuelist;
1832
    my @issuelist;
(-)a/installer/data/mysql/atomicupdate/bug_18745.perl (+18 lines)
Line 0 Link Here
1
$DBversion = 'XXX';
2
if( CheckVersion( $DBversion ) ) {
3
    if (!column_exists('serial', 'aqbooksellerid')) {
4
        $dbh->do(q{
5
            ALTER TABLE `serial`
6
            ADD COLUMN `aqbooksellerid` int(11) NULL DEFAULT NULL AFTER `subscriptionid`,
7
            ADD CONSTRAINT `serial_aqbooksellerid_fk` FOREIGN KEY (`aqbooksellerid`) REFERENCES `aqbooksellers` (`id`)
8
        });
9
        $dbh->do(q{
10
            UPDATE serial s
11
            JOIN subscription sub ON s.subscriptionid = sub.subscriptionid
12
            SET s.aqbooksellerid = sub.aqbooksellerid
13
        });
14
    }
15
16
    SetVersion( $DBversion );
17
    print "Upgrade to $DBversion done (Bug 18745 - Serial claims: save supplier info on serial-level)\n";
18
}
(-)a/installer/data/mysql/kohastructure.sql (+2 lines)
Lines 1521-1526 CREATE TABLE `serial` ( -- issues related to subscriptions Link Here
1521
  `serialid` int(11) NOT NULL auto_increment, -- unique key for the issue
1521
  `serialid` int(11) NOT NULL auto_increment, -- unique key for the issue
1522
  `biblionumber` varchar(100) NOT NULL default '', -- foreign key for the biblio.biblionumber that this issue is attached to
1522
  `biblionumber` varchar(100) NOT NULL default '', -- foreign key for the biblio.biblionumber that this issue is attached to
1523
  `subscriptionid` varchar(100) NOT NULL default '', -- foreign key to the subscription.subscriptionid that this issue is part of
1523
  `subscriptionid` varchar(100) NOT NULL default '', -- foreign key to the subscription.subscriptionid that this issue is part of
1524
  `aqbooksellerid` int(11) NULL DEFAULT NULL, -- the subscription's bookseller at the time of creation (used for claims)
1524
  `serialseq` varchar(100) NOT NULL default '', -- issue information (volume, number, etc)
1525
  `serialseq` varchar(100) NOT NULL default '', -- issue information (volume, number, etc)
1525
  `serialseq_x` varchar( 100 ) NULL DEFAULT NULL, -- first part of issue information
1526
  `serialseq_x` varchar( 100 ) NULL DEFAULT NULL, -- first part of issue information
1526
  `serialseq_y` varchar( 100 ) NULL DEFAULT NULL, -- second part of issue information
1527
  `serialseq_y` varchar( 100 ) NULL DEFAULT NULL, -- second part of issue information
Lines 1533-1538 CREATE TABLE `serial` ( -- issues related to subscriptions Link Here
1533
  `claimdate` date default NULL, -- date claimed
1534
  `claimdate` date default NULL, -- date claimed
1534
  claims_count int(11) default 0, -- number of claims made related to this issue
1535
  claims_count int(11) default 0, -- number of claims made related to this issue
1535
  `routingnotes` MEDIUMTEXT, -- notes from the routing list
1536
  `routingnotes` MEDIUMTEXT, -- notes from the routing list
1537
  CONSTRAINT `serial_aqbooksellerid_fk` FOREIGN KEY (`aqbooksellerid`) REFERENCES `aqbooksellers` (`id`),
1536
  PRIMARY KEY (`serialid`)
1538
  PRIMARY KEY (`serialid`)
1537
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1539
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
1538
1540
(-)a/t/db_dependent/Serials/Claims.t (-2 / +25 lines)
Lines 1-5 Link Here
1
use Modern::Perl;
1
use Modern::Perl;
2
use Test::More tests => 17;
2
use Test::More tests => 18;
3
3
4
use C4::Acquisition;
4
use C4::Acquisition;
5
use C4::Budgets;
5
use C4::Budgets;
Lines 162-164 is( $serial_claimed->{claims_count}, 1, 'The serial should have been claimed' ); Link Here
162
my $today = output_pref({ dt => dt_from_string, dateformat => 'iso', dateonly => 1 });
162
my $today = output_pref({ dt => dt_from_string, dateformat => 'iso', dateonly => 1 });
163
# FIXME: This test should pass. The GetLateOrMissingIssues should not deal with date format!
163
# FIXME: This test should pass. The GetLateOrMissingIssues should not deal with date format!
164
#is( $serial_claimed->{claimdate}, $today, 'The serial should have been claimed today' );
164
#is( $serial_claimed->{claimdate}, $today, 'The serial should have been claimed today' );
165
- 
165
166
subtest "Claims when subscription's supplier has changed" => sub {
167
    plan tests => 2;
168
169
    my $bookseller1 = Koha::Acquisition::Bookseller->new()->store;
170
    my $bookseller2 = Koha::Acquisition::Bookseller->new()->store;
171
    my $subscriptionid = NewSubscription(
172
        undef, $branchcode, $bookseller1->id, undef, $budget_id, $biblionumber,
173
        '2013-01-01', undef, undef, undef,  undef,
174
        undef,      undef,  undef, undef, undef, undef,
175
        1,          "notes",undef, '2013-01-01', undef, undef,
176
        undef,       undef,  0,    "intnotes",  0,
177
        undef, undef, 0,          undef,         '2013-12-31', 0
178
    );
179
    NewIssue('2', $subscriptionid, $biblionumber, 3, '2013-01-02', '2013-01-02'); # 3 is late
180
    my $subscription = Koha::Subscriptions->find($subscriptionid);
181
    $subscription->aqbooksellerid($bookseller2->id)->store();
182
    NewIssue('3', $subscriptionid, $biblionumber, 3, '2013-01-03', '2013-01-03'); # 3 is late
183
184
    my @lateissues = GetLateOrMissingIssues($bookseller1->id);
185
    is(scalar @lateissues, 2, '2 late issues for bookseller1');
186
    @lateissues = GetLateOrMissingIssues($bookseller2->id);
187
    is(scalar @lateissues, 1, '1 late issue for bookseller2');
188
};

Return to bug 18745