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

(-)a/C4/Serials.pm (-43 / +63 lines)
Lines 135-151 sub GetSuppliersWithLateIssues { Link Here
135
    my $dbh   = C4::Context->dbh;
135
    my $dbh   = C4::Context->dbh;
136
    my $statuses = join(',', ( LATE, MISSING_STATUSES, CLAIMED ) );
136
    my $statuses = join(',', ( LATE, MISSING_STATUSES, CLAIMED ) );
137
    my $query = qq|
137
    my $query = qq|
138
    SELECT DISTINCT id, name
138
        SELECT DISTINCT aqbooksellers.id, aqbooksellers.name
139
    FROM            subscription
139
        FROM aqbooksellers
140
    LEFT JOIN       serial ON serial.subscriptionid=subscription.subscriptionid
140
        LEFT JOIN serial ON serial.aqbooksellerid = aqbooksellers.id
141
    LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id
141
        LEFT JOIN subscription ON serial.subscriptionid = subscription.subscriptionid
142
    WHERE id > 0
142
        WHERE (
143
        AND (
143
                (serial.planneddate < NOW() AND serial.status = 1)
144
            (planneddate < now() AND serial.status=1)
144
                OR serial.status IN ( $statuses )
145
            OR serial.STATUS IN ( $statuses )
145
            )
146
        )
146
            AND subscription.closed = 0
147
        AND subscription.closed = 0
147
        ORDER BY name
148
    ORDER BY name|;
148
    |;
149
    return $dbh->selectall_arrayref($query, { Slice => {} });
149
    return $dbh->selectall_arrayref($query, { Slice => {} });
150
}
150
}
151
151
Lines 1325-1330 sub ModSubscription { Link Here
1325
    $itemtype, $previousitemtype, $mana_id, $ccode
1325
    $itemtype, $previousitemtype, $mana_id, $ccode
1326
    ) = @_;
1326
    ) = @_;
1327
1327
1328
    $aqbooksellerid ||= undef;
1329
1328
    my $subscription = Koha::Subscriptions->find($subscriptionid);
1330
    my $subscription = Koha::Subscriptions->find($subscriptionid);
1329
    $subscription->set(
1331
    $subscription->set(
1330
        {
1332
        {
Lines 1407-1412 sub NewSubscription { Link Here
1407
    ) = @_;
1409
    ) = @_;
1408
    my $dbh = C4::Context->dbh;
1410
    my $dbh = C4::Context->dbh;
1409
1411
1412
    $aqbooksellerid ||= undef;
1413
1410
    my $subscription = Koha::Subscription->new(
1414
    my $subscription = Koha::Subscription->new(
1411
        {
1415
        {
1412
            librarian         => $auser,
1416
            librarian         => $auser,
Lines 1486-1491 sub NewSubscription { Link Here
1486
            serialseq_y    => $subscription->{'lastvalue2'},
1490
            serialseq_y    => $subscription->{'lastvalue2'},
1487
            serialseq_z    => $subscription->{'lastvalue3'},
1491
            serialseq_z    => $subscription->{'lastvalue3'},
1488
            subscriptionid => $subscriptionid,
1492
            subscriptionid => $subscriptionid,
1493
            aqbooksellerid => $subscription->{aqbooksellerid},
1489
            biblionumber   => $biblionumber,
1494
            biblionumber   => $biblionumber,
1490
            status         => EXPECTED,
1495
            status         => EXPECTED,
1491
            planneddate    => $firstacquidate,
1496
            planneddate    => $firstacquidate,
Lines 1629-1634 sub NewIssue { Link Here
1629
            serialseq_y       => $subscription->lastvalue2(),
1634
            serialseq_y       => $subscription->lastvalue2(),
1630
            serialseq_z       => $subscription->lastvalue3(),
1635
            serialseq_z       => $subscription->lastvalue3(),
1631
            subscriptionid    => $subscriptionid,
1636
            subscriptionid    => $subscriptionid,
1637
            aqbooksellerid    => $subscription->aqbooksellerid(),
1632
            biblionumber      => $biblionumber,
1638
            biblionumber      => $biblionumber,
1633
            status            => $status,
1639
            status            => $status,
1634
            planneddate       => $planneddate,
1640
            planneddate       => $planneddate,
Lines 1854-1860 sub GetLateOrMissingIssues { Link Here
1854
    my $sth;
1860
    my $sth;
1855
    my $byserial = '';
1861
    my $byserial = '';
1856
    if ($serialid) {
1862
    if ($serialid) {
1857
        $byserial = "and serialid = " . $serialid;
1863
        $byserial = "and s.serialid = " . $serialid;
1858
    }
1864
    }
1859
    if ($order) {
1865
    if ($order) {
1860
        $order .= ", title";
1866
        $order .= ", title";
Lines 1863-1901 sub GetLateOrMissingIssues { Link Here
1863
    }
1869
    }
1864
    my $missing_statuses_string = join ',', (MISSING_STATUSES);
1870
    my $missing_statuses_string = join ',', (MISSING_STATUSES);
1865
    if ($supplierid) {
1871
    if ($supplierid) {
1866
        $sth = $dbh->prepare(
1872
        $sth = $dbh->prepare(qq{
1867
            "SELECT
1873
            SELECT
1868
                serialid,      aqbooksellerid,        name,
1874
                s.serialid, s.aqbooksellerid, aqbooksellers.name,
1869
                biblio.title,  biblioitems.issn,      planneddate,    serialseq,
1875
                biblio.title, biblioitems.issn, s.planneddate, s.serialseq,
1870
                serial.status, serial.subscriptionid, claimdate, claims_count,
1876
                s.status, s.subscriptionid, s.claimdate, s.claims_count,
1871
                subscription.branchcode, serial.publisheddate
1877
                subscription.branchcode, s.publisheddate
1872
            FROM      serial
1878
            FROM serial s
1873
                LEFT JOIN subscription  ON serial.subscriptionid=subscription.subscriptionid
1879
                LEFT JOIN subscription  ON s.subscriptionid = subscription.subscriptionid
1874
                LEFT JOIN biblio        ON subscription.biblionumber=biblio.biblionumber
1880
                LEFT JOIN biblio        ON subscription.biblionumber = biblio.biblionumber
1875
                LEFT JOIN biblioitems   ON subscription.biblionumber=biblioitems.biblionumber
1881
                LEFT JOIN biblioitems   ON subscription.biblionumber = biblioitems.biblionumber
1876
                LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id
1882
                LEFT JOIN aqbooksellers ON s.aqbooksellerid = aqbooksellers.id
1877
                WHERE subscription.subscriptionid = serial.subscriptionid
1883
                WHERE
1878
                AND (serial.STATUS IN ($missing_statuses_string) OR ((planneddate < now() AND serial.STATUS = ?) OR serial.STATUS = ? OR serial.STATUS = ?))
1884
                    (
1879
                AND subscription.aqbooksellerid=$supplierid
1885
                        s.status IN ($missing_statuses_string)
1880
                $byserial
1886
                        OR (
1881
                ORDER BY $order"
1887
                            (s.planneddate < NOW() AND s.status = ?)
1882
        );
1888
                            OR s.status = ?
1889
                            OR s.status = ?
1890
                        )
1891
                    )
1892
                    AND s.aqbooksellerid = $supplierid
1893
                    $byserial
1894
                ORDER BY $order
1895
        });
1883
    } else {
1896
    } else {
1884
        $sth = $dbh->prepare(
1897
        $sth = $dbh->prepare(qq{
1885
            "SELECT
1898
            SELECT
1886
            serialid,      aqbooksellerid,         name,
1899
                s.serialid, s.aqbooksellerid, aqbooksellers.name,
1887
            biblio.title,  planneddate,           serialseq,
1900
                biblio.title, s.planneddate, s.serialseq,
1888
                serial.status, serial.subscriptionid, claimdate, claims_count,
1901
                s.status, s.subscriptionid, s.claimdate, s.claims_count,
1889
                subscription.branchcode, serial.publisheddate
1902
                subscription.branchcode, s.publisheddate
1890
            FROM serial
1903
            FROM serial s
1891
                LEFT JOIN subscription ON serial.subscriptionid=subscription.subscriptionid
1904
                LEFT JOIN subscription ON s.subscriptionid = subscription.subscriptionid
1892
                LEFT JOIN biblio ON subscription.biblionumber=biblio.biblionumber
1905
                LEFT JOIN biblio ON subscription.biblionumber = biblio.biblionumber
1893
                LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id
1906
                LEFT JOIN aqbooksellers ON s.aqbooksellerid = aqbooksellers.id
1894
                WHERE subscription.subscriptionid = serial.subscriptionid
1907
            WHERE
1895
                        AND (serial.STATUS IN ($missing_statuses_string) OR ((planneddate < now() AND serial.STATUS = ?) OR serial.STATUS = ? OR serial.STATUS = ?))
1908
                (
1909
                    s.status IN ($missing_statuses_string)
1910
                    OR (
1911
                        (s.planneddate < now() AND s.status = ?)
1912
                        OR s.status = ?
1913
                        OR s.status = ?
1914
                    )
1915
                )
1896
                $byserial
1916
                $byserial
1897
                ORDER BY $order"
1917
            ORDER BY $order
1898
        );
1918
        });
1899
    }
1919
    }
1900
    $sth->execute( EXPECTED, LATE, CLAIMED );
1920
    $sth->execute( EXPECTED, LATE, CLAIMED );
1901
    my @issuelist;
1921
    my @issuelist;
(-)a/installer/data/mysql/atomicupdate/bug_18745.perl (+29 lines)
Line 0 Link Here
1
$DBversion = 'XXX';
2
if( CheckVersion( $DBversion ) ) {
3
    $dbh->do(q{
4
       UPDATE subscription
5
       SET aqbooksellerid = NULL
6
       WHERE aqbooksellerid = 0
7
    });
8
    $dbh->do(q{
9
        ALTER TABLE subscription
10
        MODIFY COLUMN aqbooksellerid int(11) NULL DEFAULT NULL,
11
        ADD CONSTRAINT subscription_ibfk_aqbooksellerid FOREIGN KEY (aqbooksellerid) REFERENCES aqbooksellers (id) ON DELETE RESTRICT ON UPDATE CASCADE
12
    });
13
14
    if (!column_exists('serial', 'aqbooksellerid')) {
15
        $dbh->do(q{
16
            ALTER TABLE `serial`
17
            ADD COLUMN `aqbooksellerid` int(11) NULL DEFAULT NULL AFTER `subscriptionid`,
18
            ADD CONSTRAINT `serial_aqbooksellerid_fk` FOREIGN KEY (`aqbooksellerid`) REFERENCES `aqbooksellers` (`id`)
19
        });
20
        $dbh->do(q{
21
            UPDATE serial s
22
            JOIN subscription sub ON s.subscriptionid = sub.subscriptionid
23
            SET s.aqbooksellerid = sub.aqbooksellerid
24
        });
25
    }
26
27
    SetVersion( $DBversion );
28
    print "Upgrade to $DBversion done (Bug 18745 - Serial claims: save supplier info on serial-level)\n";
29
}
(-)a/installer/data/mysql/kohastructure.sql (-3 / +6 lines)
Lines 4775-4780 CREATE TABLE `serial` ( Link Here
4775
  `serialid` int(11) NOT NULL AUTO_INCREMENT COMMENT 'unique key for the issue',
4775
  `serialid` int(11) NOT NULL AUTO_INCREMENT COMMENT 'unique key for the issue',
4776
  `biblionumber` int(11) NOT NULL COMMENT 'foreign key for the biblio.biblionumber that this issue is attached to',
4776
  `biblionumber` int(11) NOT NULL COMMENT 'foreign key for the biblio.biblionumber that this issue is attached to',
4777
  `subscriptionid` int(11) NOT NULL COMMENT 'foreign key to the subscription.subscriptionid that this issue is part of',
4777
  `subscriptionid` int(11) NOT NULL COMMENT 'foreign key to the subscription.subscriptionid that this issue is part of',
4778
  `aqbooksellerid` int(11) NULL DEFAULT NULL COMMENT 'the subscription''s bookseller at the time of creation (used for claims)',
4778
  `serialseq` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'issue information (volume, number, etc)',
4779
  `serialseq` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'issue information (volume, number, etc)',
4779
  `serialseq_x` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'first part of issue information',
4780
  `serialseq_x` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'first part of issue information',
4780
  `serialseq_y` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'second part of issue information',
4781
  `serialseq_y` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'second part of issue information',
Lines 4791-4797 CREATE TABLE `serial` ( Link Here
4791
  KEY `serial_ibfk_1` (`biblionumber`),
4792
  KEY `serial_ibfk_1` (`biblionumber`),
4792
  KEY `serial_ibfk_2` (`subscriptionid`),
4793
  KEY `serial_ibfk_2` (`subscriptionid`),
4793
  CONSTRAINT `serial_ibfk_1` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
4794
  CONSTRAINT `serial_ibfk_1` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
4794
  CONSTRAINT `serial_ibfk_2` FOREIGN KEY (`subscriptionid`) REFERENCES `subscription` (`subscriptionid`) ON DELETE CASCADE ON UPDATE CASCADE
4795
  CONSTRAINT `serial_ibfk_2` FOREIGN KEY (`subscriptionid`) REFERENCES `subscription` (`subscriptionid`) ON DELETE CASCADE ON UPDATE CASCADE,
4796
  CONSTRAINT `serial_ibfk_3` FOREIGN KEY (`aqbooksellerid`) REFERENCES `aqbooksellers` (`id`)
4795
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
4797
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
4796
/*!40101 SET character_set_client = @saved_cs_client */;
4798
/*!40101 SET character_set_client = @saved_cs_client */;
4797
4799
Lines 5003-5009 CREATE TABLE `subscription` ( Link Here
5003
  `subscriptionid` int(11) NOT NULL AUTO_INCREMENT COMMENT 'unique key for this subscription',
5005
  `subscriptionid` int(11) NOT NULL AUTO_INCREMENT COMMENT 'unique key for this subscription',
5004
  `librarian` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT 'the librarian''s username from borrowers.userid',
5006
  `librarian` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT 'the librarian''s username from borrowers.userid',
5005
  `startdate` date DEFAULT NULL COMMENT 'start date for this subscription',
5007
  `startdate` date DEFAULT NULL COMMENT 'start date for this subscription',
5006
  `aqbooksellerid` int(11) DEFAULT 0 COMMENT 'foreign key for aqbooksellers.id to link to the vendor',
5008
  `aqbooksellerid` int(11) NULL DEFAULT NULL COMMENT 'foreign key for aqbooksellers.id to link to the vendor',
5007
  `cost` int(11) DEFAULT 0,
5009
  `cost` int(11) DEFAULT 0,
5008
  `aqbudgetid` int(11) DEFAULT 0,
5010
  `aqbudgetid` int(11) DEFAULT 0,
5009
  `weeklength` int(11) DEFAULT 0 COMMENT 'subscription length in weeks (will not be filled in if monthlength or numberlength is set)',
5011
  `weeklength` int(11) DEFAULT 0 COMMENT 'subscription length in weeks (will not be filled in if monthlength or numberlength is set)',
Lines 5049-5055 CREATE TABLE `subscription` ( Link Here
5049
  KEY `subscription_ibfk_3` (`biblionumber`),
5051
  KEY `subscription_ibfk_3` (`biblionumber`),
5050
  CONSTRAINT `subscription_ibfk_1` FOREIGN KEY (`periodicity`) REFERENCES `subscription_frequencies` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
5052
  CONSTRAINT `subscription_ibfk_1` FOREIGN KEY (`periodicity`) REFERENCES `subscription_frequencies` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
5051
  CONSTRAINT `subscription_ibfk_2` FOREIGN KEY (`numberpattern`) REFERENCES `subscription_numberpatterns` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
5053
  CONSTRAINT `subscription_ibfk_2` FOREIGN KEY (`numberpattern`) REFERENCES `subscription_numberpatterns` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
5052
  CONSTRAINT `subscription_ibfk_3` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE
5054
  CONSTRAINT `subscription_ibfk_3` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
5055
  CONSTRAINT `subscription_ibfk_4` FOREIGN KEY (`aqbooksellerid`) REFERENCES `aqbooksellers` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE
5053
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
5056
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
5054
/*!40101 SET character_set_client = @saved_cs_client */;
5057
/*!40101 SET character_set_client = @saved_cs_client */;
5055
5058
(-)a/serials/subscription-add.pl (-2 / +2 lines)
Lines 297-303 sub redirect_add_subscription { Link Here
297
297
298
    my $auser          = $query->param('user');
298
    my $auser          = $query->param('user');
299
    my $branchcode     = $query->param('branchcode');
299
    my $branchcode     = $query->param('branchcode');
300
    my $aqbooksellerid = $query->param('aqbooksellerid');
300
    my $aqbooksellerid = $query->param('aqbooksellerid') || undef;
301
    my $cost           = $query->param('cost');
301
    my $cost           = $query->param('cost');
302
    my $aqbudgetid     = $query->param('aqbudgetid');
302
    my $aqbudgetid     = $query->param('aqbudgetid');
303
    my @irregularity   = $query->multi_param('irregularity');
303
    my @irregularity   = $query->multi_param('irregularity');
Lines 393-399 sub redirect_mod_subscription { Link Here
393
    my $librarian => scalar $query->param('librarian'),
393
    my $librarian => scalar $query->param('librarian'),
394
    my $branchcode = $query->param('branchcode');
394
    my $branchcode = $query->param('branchcode');
395
    my $cost = $query->param('cost');
395
    my $cost = $query->param('cost');
396
    my $aqbooksellerid = $query->param('aqbooksellerid');
396
    my $aqbooksellerid = $query->param('aqbooksellerid') || undef;
397
    my $biblionumber = $query->param('biblionumber');
397
    my $biblionumber = $query->param('biblionumber');
398
    my $aqbudgetid = $query->param('aqbudgetid');
398
    my $aqbudgetid = $query->param('aqbudgetid');
399
399
(-)a/t/db_dependent/Serials/Claims.t (-3 / +26 lines)
Lines 1-11 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 qw( AddBudgetPeriod AddBudget );
5
use C4::Budgets qw( AddBudgetPeriod AddBudget );
6
use Koha::Database;
6
use Koha::Database;
7
use Koha::Acquisition::Booksellers;
7
use Koha::Acquisition::Booksellers;
8
use_ok('C4::Serials', qw( GetSuppliersWithLateIssues NewSubscription GetLateOrMissingIssues updateClaim ));
8
use_ok('C4::Serials', qw( GetSuppliersWithLateIssues NewSubscription GetLateOrMissingIssues updateClaim NewIssue ));
9
9
10
use Koha::DateUtils qw( dt_from_string output_pref );
10
use Koha::DateUtils qw( dt_from_string output_pref );
11
11
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({ name => 'Bookseller #1' })->store;
170
    my $bookseller2 = Koha::Acquisition::Bookseller->new({ name => 'Bookseller #2' })->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