|
Lines 1812-1875
this function selects missing issues on database - where serial.status = MISSING
Link Here
|
| 1812 |
|
1812 |
|
| 1813 |
return : |
1813 |
return : |
| 1814 |
the issuelist as an array of hash refs. Each element of this array contains |
1814 |
the issuelist as an array of hash refs. Each element of this array contains |
| 1815 |
name,title,planneddate,serialseq,serial.subscriptionid from tables : subscription, serial & biblio |
1815 |
name,title,issn,planneddate,serialseq,serial.subscriptionid from tables : subscription, serial & biblio |
| 1816 |
|
1816 |
|
| 1817 |
=cut |
1817 |
=cut |
| 1818 |
|
1818 |
|
| 1819 |
sub GetLateOrMissingIssues { |
1819 |
sub GetLateOrMissingIssues { |
| 1820 |
my ( $supplierid, $serialid, $order ) = @_; |
1820 |
my ( $supplierid, $serialid ) = @_; |
| 1821 |
|
1821 |
|
| 1822 |
return unless ( $supplierid or $serialid ); |
1822 |
return unless ( $supplierid or $serialid ); |
| 1823 |
|
1823 |
|
| 1824 |
my $dbh = C4::Context->dbh; |
1824 |
my $dbh = C4::Context->dbh; |
| 1825 |
|
1825 |
|
| 1826 |
my $sth; |
|
|
| 1827 |
my $byserial = ''; |
| 1828 |
if ($serialid) { |
| 1829 |
$byserial = "and serialid = " . $serialid; |
| 1830 |
} |
| 1831 |
if ($order) { |
| 1832 |
$order .= ", title"; |
| 1833 |
} else { |
| 1834 |
$order = "title"; |
| 1835 |
} |
| 1836 |
my $missing_statuses_string = join ',', (MISSING_STATUSES); |
1826 |
my $missing_statuses_string = join ',', (MISSING_STATUSES); |
|
|
1827 |
my @params = (EXPECTED, LATE, CLAIMED); |
| 1828 |
|
| 1829 |
my $query = qq{ |
| 1830 |
SELECT |
| 1831 |
serial.serialid, |
| 1832 |
serial.planneddate, |
| 1833 |
serial.serialseq, |
| 1834 |
serial.status, |
| 1835 |
serial.subscriptionid, |
| 1836 |
serial.claimdate, |
| 1837 |
serial.claims_count, |
| 1838 |
subscription.branchcode, |
| 1839 |
subscription.aqbooksellerid, |
| 1840 |
biblio.title, |
| 1841 |
biblioitems.issn, |
| 1842 |
aqbooksellers.name |
| 1843 |
FROM serial |
| 1844 |
LEFT JOIN subscription ON serial.subscriptionid = subscription.subscriptionid |
| 1845 |
LEFT JOIN biblio ON subscription.biblionumber = biblio.biblionumber |
| 1846 |
LEFT JOIN biblioitems ON subscription.biblionumber = biblioitems.biblionumber |
| 1847 |
LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id |
| 1848 |
WHERE subscription.subscriptionid = serial.subscriptionid |
| 1849 |
AND ( |
| 1850 |
serial.STATUS IN ($missing_statuses_string) |
| 1851 |
OR ((serial.planneddate < now() AND serial.STATUS = ?) OR serial.STATUS = ? OR serial.STATUS = ?) |
| 1852 |
) |
| 1853 |
}; |
| 1837 |
if ($supplierid) { |
1854 |
if ($supplierid) { |
| 1838 |
$sth = $dbh->prepare( |
1855 |
$query .= qq{ |
| 1839 |
"SELECT |
1856 |
AND subscription.aqbooksellerid = ? |
| 1840 |
serialid, aqbooksellerid, name, |
1857 |
}; |
| 1841 |
biblio.title, biblioitems.issn, planneddate, serialseq, |
1858 |
push @params, $supplierid; |
| 1842 |
serial.status, serial.subscriptionid, claimdate, claims_count, |
1859 |
} |
| 1843 |
subscription.branchcode |
1860 |
if ($serialid) { |
| 1844 |
FROM serial |
1861 |
$query .= qq{ |
| 1845 |
LEFT JOIN subscription ON serial.subscriptionid=subscription.subscriptionid |
1862 |
AND serial.serialid = ? |
| 1846 |
LEFT JOIN biblio ON subscription.biblionumber=biblio.biblionumber |
1863 |
}; |
| 1847 |
LEFT JOIN biblioitems ON subscription.biblionumber=biblioitems.biblionumber |
1864 |
push @params, $serialid; |
| 1848 |
LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id |
|
|
| 1849 |
WHERE subscription.subscriptionid = serial.subscriptionid |
| 1850 |
AND (serial.STATUS IN ($missing_statuses_string) OR ((planneddate < now() AND serial.STATUS = ?) OR serial.STATUS = ? OR serial.STATUS = ?)) |
| 1851 |
AND subscription.aqbooksellerid=$supplierid |
| 1852 |
$byserial |
| 1853 |
ORDER BY $order" |
| 1854 |
); |
| 1855 |
} else { |
| 1856 |
$sth = $dbh->prepare( |
| 1857 |
"SELECT |
| 1858 |
serialid, aqbooksellerid, name, |
| 1859 |
biblio.title, planneddate, serialseq, |
| 1860 |
serial.status, serial.subscriptionid, claimdate, claims_count, |
| 1861 |
subscription.branchcode |
| 1862 |
FROM serial |
| 1863 |
LEFT JOIN subscription ON serial.subscriptionid=subscription.subscriptionid |
| 1864 |
LEFT JOIN biblio ON subscription.biblionumber=biblio.biblionumber |
| 1865 |
LEFT JOIN aqbooksellers ON subscription.aqbooksellerid = aqbooksellers.id |
| 1866 |
WHERE subscription.subscriptionid = serial.subscriptionid |
| 1867 |
AND (serial.STATUS IN ($missing_statuses_string) OR ((planneddate < now() AND serial.STATUS = ?) OR serial.STATUS = ? OR serial.STATUS = ?)) |
| 1868 |
$byserial |
| 1869 |
ORDER BY $order" |
| 1870 |
); |
| 1871 |
} |
1865 |
} |
| 1872 |
$sth->execute( EXPECTED, LATE, CLAIMED ); |
1866 |
$query .= q{ |
|
|
1867 |
ORDER BY biblio.title |
| 1868 |
}; |
| 1869 |
|
| 1870 |
my $sth = $dbh->prepare($query); |
| 1871 |
$sth->execute(@params); |
| 1872 |
|
| 1873 |
my @issuelist; |
1873 |
my @issuelist; |
| 1874 |
while ( my $line = $sth->fetchrow_hashref ) { |
1874 |
while ( my $line = $sth->fetchrow_hashref ) { |
| 1875 |
|
1875 |
|
| 1876 |
- |
|
|