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

(-)a/C4/Serials.pm (-41 / +57 lines)
Lines 122-138 sub GetSuppliersWithLateIssues { Link Here
122
    my $dbh   = C4::Context->dbh;
122
    my $dbh   = C4::Context->dbh;
123
    my $statuses = join(',', ( LATE, MISSING_STATUSES, CLAIMED ) );
123
    my $statuses = join(',', ( LATE, MISSING_STATUSES, CLAIMED ) );
124
    my $query = qq|
124
    my $query = qq|
125
    SELECT DISTINCT id, name
125
        SELECT DISTINCT aqbooksellers.id, aqbooksellers.name
126
    FROM            subscription
126
        FROM aqbooksellers
127
    LEFT JOIN       serial ON serial.subscriptionid=subscription.subscriptionid
127
        LEFT JOIN serial ON serial.aqbooksellerid = aqbooksellers.id
128
    LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id
128
        LEFT JOIN subscription ON serial.subscriptionid = subscription.subscriptionid
129
    WHERE id > 0
129
        WHERE (
130
        AND (
130
                (serial.planneddate < NOW() AND serial.status = 1)
131
            (planneddate < now() AND serial.status=1)
131
                OR serial.status IN ( $statuses )
132
            OR serial.STATUS IN ( $statuses )
132
            )
133
        )
133
            AND subscription.closed = 0
134
        AND subscription.closed = 0
134
        ORDER BY name
135
    ORDER BY name|;
135
    |;
136
    return $dbh->selectall_arrayref($query, { Slice => {} });
136
    return $dbh->selectall_arrayref($query, { Slice => {} });
137
}
137
}
138
138
Lines 1473-1478 sub NewSubscription { Link Here
1473
            serialseq_y    => $subscription->{'lastvalue2'},
1473
            serialseq_y    => $subscription->{'lastvalue2'},
1474
            serialseq_z    => $subscription->{'lastvalue3'},
1474
            serialseq_z    => $subscription->{'lastvalue3'},
1475
            subscriptionid => $subscriptionid,
1475
            subscriptionid => $subscriptionid,
1476
            aqbooksellerid => $subscription->{aqbooksellerid},
1476
            biblionumber   => $biblionumber,
1477
            biblionumber   => $biblionumber,
1477
            status         => EXPECTED,
1478
            status         => EXPECTED,
1478
            planneddate    => $firstacquidate,
1479
            planneddate    => $firstacquidate,
Lines 1580-1585 sub NewIssue { Link Here
1580
            serialseq_y       => $subscription->lastvalue2(),
1581
            serialseq_y       => $subscription->lastvalue2(),
1581
            serialseq_z       => $subscription->lastvalue3(),
1582
            serialseq_z       => $subscription->lastvalue3(),
1582
            subscriptionid    => $subscriptionid,
1583
            subscriptionid    => $subscriptionid,
1584
            aqbooksellerid    => $subscription->aqbooksellerid(),
1583
            biblionumber      => $biblionumber,
1585
            biblionumber      => $biblionumber,
1584
            status            => $status,
1586
            status            => $status,
1585
            planneddate       => $planneddate,
1587
            planneddate       => $planneddate,
Lines 1826-1832 sub GetLateOrMissingIssues { Link Here
1826
    my $sth;
1828
    my $sth;
1827
    my $byserial = '';
1829
    my $byserial = '';
1828
    if ($serialid) {
1830
    if ($serialid) {
1829
        $byserial = "and serialid = " . $serialid;
1831
        $byserial = "and s.serialid = " . $serialid;
1830
    }
1832
    }
1831
    if ($order) {
1833
    if ($order) {
1832
        $order .= ", title";
1834
        $order .= ", title";
Lines 1835-1873 sub GetLateOrMissingIssues { Link Here
1835
    }
1837
    }
1836
    my $missing_statuses_string = join ',', (MISSING_STATUSES);
1838
    my $missing_statuses_string = join ',', (MISSING_STATUSES);
1837
    if ($supplierid) {
1839
    if ($supplierid) {
1838
        $sth = $dbh->prepare(
1840
        $sth = $dbh->prepare(qq{
1839
            "SELECT
1841
            SELECT
1840
                serialid,      aqbooksellerid,        name,
1842
                s.serialid, s.aqbooksellerid, aqbooksellers.name,
1841
                biblio.title,  biblioitems.issn,      planneddate,    serialseq,
1843
                biblio.title, biblioitems.issn, s.planneddate, s.serialseq,
1842
                serial.status, serial.subscriptionid, claimdate, claims_count,
1844
                s.status, s.subscriptionid, s.claimdate, s.claims_count,
1843
                subscription.branchcode
1845
                subscription.branchcode
1844
            FROM      serial
1846
            FROM serial s
1845
                LEFT JOIN subscription  ON serial.subscriptionid=subscription.subscriptionid
1847
                LEFT JOIN subscription  ON s.subscriptionid = subscription.subscriptionid
1846
                LEFT JOIN biblio        ON subscription.biblionumber=biblio.biblionumber
1848
                LEFT JOIN biblio        ON subscription.biblionumber = biblio.biblionumber
1847
                LEFT JOIN biblioitems   ON subscription.biblionumber=biblioitems.biblionumber
1849
                LEFT JOIN biblioitems   ON subscription.biblionumber = biblioitems.biblionumber
1848
                LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id
1850
                LEFT JOIN aqbooksellers ON s.aqbooksellerid = aqbooksellers.id
1849
                WHERE subscription.subscriptionid = serial.subscriptionid
1851
                WHERE
1850
                AND (serial.STATUS IN ($missing_statuses_string) OR ((planneddate < now() AND serial.STATUS = ?) OR serial.STATUS = ? OR serial.STATUS = ?))
1852
                    (
1851
                AND subscription.aqbooksellerid=$supplierid
1853
                        s.status IN ($missing_statuses_string)
1852
                $byserial
1854
                        OR (
1853
                ORDER BY $order"
1855
                            (s.planneddate < NOW() AND s.status = ?)
1854
        );
1856
                            OR s.status = ?
1857
                            OR s.status = ?
1858
                        )
1859
                    )
1860
                    AND s.aqbooksellerid = $supplierid
1861
                    $byserial
1862
                ORDER BY $order
1863
        });
1855
    } else {
1864
    } else {
1856
        $sth = $dbh->prepare(
1865
        $sth = $dbh->prepare(qq{
1857
            "SELECT
1866
            SELECT
1858
            serialid,      aqbooksellerid,         name,
1867
                s.serialid, s.aqbooksellerid, aqbooksellers.name,
1859
            biblio.title,  planneddate,           serialseq,
1868
                biblio.title, s.planneddate, s.serialseq,
1860
                serial.status, serial.subscriptionid, claimdate, claims_count,
1869
                s.status, s.subscriptionid, s.claimdate, s.claims_count,
1861
                subscription.branchcode
1870
                subscription.branchcode
1862
            FROM serial
1871
            FROM serial s
1863
                LEFT JOIN subscription ON serial.subscriptionid=subscription.subscriptionid
1872
                LEFT JOIN subscription ON s.subscriptionid = subscription.subscriptionid
1864
                LEFT JOIN biblio ON subscription.biblionumber=biblio.biblionumber
1873
                LEFT JOIN biblio ON subscription.biblionumber = biblio.biblionumber
1865
                LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id
1874
                LEFT JOIN aqbooksellers ON s.aqbooksellerid = aqbooksellers.id
1866
                WHERE subscription.subscriptionid = serial.subscriptionid
1875
            WHERE
1867
                        AND (serial.STATUS IN ($missing_statuses_string) OR ((planneddate < now() AND serial.STATUS = ?) OR serial.STATUS = ? OR serial.STATUS = ?))
1876
                (
1877
                    s.status IN ($missing_statuses_string)
1878
                    OR (
1879
                        (s.planneddate < now() AND s.status = ?)
1880
                        OR s.status = ?
1881
                        OR s.status = ?
1882
                    )
1883
                )
1868
                $byserial
1884
                $byserial
1869
                ORDER BY $order"
1885
            ORDER BY $order
1870
        );
1886
        });
1871
    }
1887
    }
1872
    $sth->execute( EXPECTED, LATE, CLAIMED );
1888
    $sth->execute( EXPECTED, LATE, CLAIMED );
1873
    my @issuelist;
1889
    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 1556-1561 CREATE TABLE `serial` ( -- issues related to subscriptions Link Here
1556
  `serialid` int(11) NOT NULL auto_increment, -- unique key for the issue
1556
  `serialid` int(11) NOT NULL auto_increment, -- unique key for the issue
1557
  `biblionumber` varchar(100) NOT NULL default '', -- foreign key for the biblio.biblionumber that this issue is attached to
1557
  `biblionumber` varchar(100) NOT NULL default '', -- foreign key for the biblio.biblionumber that this issue is attached to
1558
  `subscriptionid` varchar(100) NOT NULL default '', -- foreign key to the subscription.subscriptionid that this issue is part of
1558
  `subscriptionid` varchar(100) NOT NULL default '', -- foreign key to the subscription.subscriptionid that this issue is part of
1559
  `aqbooksellerid` int(11) NULL DEFAULT NULL, -- the subscription's bookseller at the time of creation (used for claims)
1559
  `serialseq` varchar(100) NOT NULL default '', -- issue information (volume, number, etc)
1560
  `serialseq` varchar(100) NOT NULL default '', -- issue information (volume, number, etc)
1560
  `serialseq_x` varchar( 100 ) NULL DEFAULT NULL, -- first part of issue information
1561
  `serialseq_x` varchar( 100 ) NULL DEFAULT NULL, -- first part of issue information
1561
  `serialseq_y` varchar( 100 ) NULL DEFAULT NULL, -- second part of issue information
1562
  `serialseq_y` varchar( 100 ) NULL DEFAULT NULL, -- second part of issue information
Lines 1568-1573 CREATE TABLE `serial` ( -- issues related to subscriptions Link Here
1568
  `claimdate` date default NULL, -- date claimed
1569
  `claimdate` date default NULL, -- date claimed
1569
  claims_count int(11) default 0, -- number of claims made related to this issue
1570
  claims_count int(11) default 0, -- number of claims made related to this issue
1570
  `routingnotes` text, -- notes from the routing list
1571
  `routingnotes` text, -- notes from the routing list
1572
  CONSTRAINT `serial_aqbooksellerid_fk` FOREIGN KEY (`aqbooksellerid`) REFERENCES `aqbooksellers` (`id`),
1571
  PRIMARY KEY  (`serialid`)
1573
  PRIMARY KEY  (`serialid`)
1572
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
1574
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
1573
1575
(-)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 161-163 is( $serial_claimed->{claims_count}, 1, 'The serial should have been claimed' ); Link Here
161
my $today = output_pref({ dt => dt_from_string, dateformat => 'iso', dateonly => 1 });
161
my $today = output_pref({ dt => dt_from_string, dateformat => 'iso', dateonly => 1 });
162
# FIXME: This test should pass. The GetLateOrMissingIssues should not deal with date format!
162
# FIXME: This test should pass. The GetLateOrMissingIssues should not deal with date format!
163
#is( $serial_claimed->{claimdate}, $today, 'The serial should have been claimed today' );
163
#is( $serial_claimed->{claimdate}, $today, 'The serial should have been claimed today' );
164
- 
164
165
subtest "Claims when subscription's supplier has changed" => sub {
166
    plan tests => 2;
167
168
    my $bookseller1 = Koha::Acquisition::Bookseller->new()->store;
169
    my $bookseller2 = Koha::Acquisition::Bookseller->new()->store;
170
    my $subscriptionid = NewSubscription(
171
        undef, $branchcode, $bookseller1->id, undef, $budget_id, $biblionumber,
172
        '2013-01-01', undef, undef, undef,  undef,
173
        undef,      undef,  undef, undef, undef, undef,
174
        1,          "notes",undef, '2013-01-01', undef, undef,
175
        undef,       undef,  0,    "intnotes",  0,
176
        undef, undef, 0,          undef,         '2013-12-31', 0
177
    );
178
    NewIssue('2', $subscriptionid, $biblionumber, 3, '2013-01-02', '2013-01-02'); # 3 is late
179
    my $subscription = Koha::Subscriptions->find($subscriptionid);
180
    $subscription->aqbooksellerid($bookseller2->id)->store();
181
    NewIssue('3', $subscriptionid, $biblionumber, 3, '2013-01-03', '2013-01-03'); # 3 is late
182
183
    my @lateissues = GetLateOrMissingIssues($bookseller1->id);
184
    is(scalar @lateissues, 2, '2 late issues for bookseller1');
185
    @lateissues = GetLateOrMissingIssues($bookseller2->id);
186
    is(scalar @lateissues, 1, '1 late issue for bookseller2');
187
};

Return to bug 18745