|
Lines 1014-1027
sub GetBasketgroups {
Link Here
|
| 1014 |
|
1014 |
|
| 1015 |
@orders = &GetOrders($basketnumber, $orderby); |
1015 |
@orders = &GetOrders($basketnumber, $orderby); |
| 1016 |
|
1016 |
|
| 1017 |
Looks up the pending (non-cancelled) orders with the given basket |
1017 |
Looks up the non-cancelled (pending and received) orders with the given basketnumber. |
| 1018 |
number. If C<$booksellerID> is non-empty, only orders from that seller |
1018 |
Parameters: |
| 1019 |
are returned. |
1019 |
- C<$basketnumber> : mandatory parameter, the basket number |
| 1020 |
|
1020 |
- C<$orderby> : optional parameter, used to sort the results (must be a valid expression |
| 1021 |
return : |
1021 |
using some fields of aqorders, aqbugdets, biblio, deletedbiblio, |
| 1022 |
C<&basket> returns a two-element array. C<@orders> is an array of |
1022 |
biblioitems, deletedbiblioitems, aqorders_transfers tables) |
| 1023 |
references-to-hash, whose keys are the fields from the aqorders, |
1023 |
If C<$orderby> is not provided, results are sorted according to publishercode and title |
| 1024 |
biblio, and biblioitems tables in the Koha database. |
1024 |
|
|
|
1025 |
Return : |
| 1026 |
The returned C<@orders> is an array of references-to-hash, whose keys are: |
| 1027 |
- the fields from the aqorders table |
| 1028 |
- the fields from the aqbudgets table |
| 1029 |
- 2 fields from aqorders_transfers table : ordernumber_from, return as transferred_from |
| 1030 |
and timestamp, return as transferred_from_timestamp |
| 1031 |
- 5 fields of biblio or deletedbiblio tables : title, author, seriestitle, serial, copyrightdate |
| 1032 |
- 9 fields of biblioitems or deletedbiblioitems tables : publicationyear, publishercode, |
| 1033 |
editionstatement, issn, isbn, ean, itemtype, volume, number |
| 1034 |
If the record still exists, the biblionumber key contains the biblionumber |
| 1035 |
If the record have been deleted, the biblionumber key is blank, |
| 1036 |
and the deletedbiblionumber key contains the biblionumber of the deleted record. |
| 1025 |
|
1037 |
|
| 1026 |
=cut |
1038 |
=cut |
| 1027 |
|
1039 |
|
|
Lines 1030-1051
sub GetOrders {
Link Here
|
| 1030 |
return () unless $basketno; |
1042 |
return () unless $basketno; |
| 1031 |
my $dbh = C4::Context->dbh; |
1043 |
my $dbh = C4::Context->dbh; |
| 1032 |
my $query =" |
1044 |
my $query =" |
| 1033 |
SELECT biblio.*,biblioitems.*, |
1045 |
SELECT |
|
|
1046 |
COALESCE(biblio.title, deletedbiblio.title) AS title, |
| 1047 |
COALESCE(biblio.author, deletedbiblio.author) AS author, |
| 1048 |
COALESCE(biblio.seriestitle, deletedbiblio.seriestitle) AS seriestitle, |
| 1049 |
COALESCE(biblio.serial, deletedbiblio.serial) AS serial, |
| 1050 |
COALESCE(biblio.copyrightdate, deletedbiblio.copyrightdate) AS copyrightdate, |
| 1051 |
COALESCE(biblioitems.publicationyear, deletedbiblioitems.publicationyear) AS publicationyear, |
| 1052 |
COALESCE(biblioitems.publishercode, deletedbiblioitems.publishercode) AS publishercode, |
| 1053 |
COALESCE(biblioitems.editionstatement, deletedbiblioitems.editionstatement) AS editionstatement, |
| 1054 |
COALESCE(biblioitems.isbn, deletedbiblioitems.isbn) AS isbn, |
| 1055 |
COALESCE(biblioitems.issn, deletedbiblioitems.issn) AS issn, |
| 1056 |
COALESCE(biblioitems.ean, deletedbiblioitems.ean) AS ean, |
| 1057 |
COALESCE(biblioitems.itemtype, deletedbiblioitems.itemtype) AS itemtype, |
| 1058 |
COALESCE(biblioitems.volume, deletedbiblioitems.volume) AS volume, |
| 1059 |
COALESCE(biblioitems.number, deletedbiblioitems.number) AS number, |
| 1034 |
aqorders.*, |
1060 |
aqorders.*, |
| 1035 |
aqbudgets.*, |
1061 |
aqbudgets.*, |
| 1036 |
biblio.title, |
|
|
| 1037 |
aqorders_transfers.ordernumber_from AS transferred_from, |
1062 |
aqorders_transfers.ordernumber_from AS transferred_from, |
| 1038 |
aqorders_transfers.timestamp AS transferred_from_timestamp |
1063 |
aqorders_transfers.timestamp AS transferred_from_timestamp |
| 1039 |
FROM aqorders |
1064 |
FROM aqorders |
| 1040 |
LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id |
1065 |
LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id |
| 1041 |
LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber |
1066 |
LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber |
| 1042 |
LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber |
1067 |
LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber |
|
|
1068 |
LEFT JOIN deletedbiblio ON deletedbiblio.biblionumber=aqorders.deletedbiblionumber |
| 1069 |
LEFT JOIN deletedbiblioitems ON deletedbiblioitems.biblionumber=deletedbiblio.biblionumber |
| 1043 |
LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_to = aqorders.ordernumber |
1070 |
LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_to = aqorders.ordernumber |
| 1044 |
WHERE basketno=? |
1071 |
WHERE basketno=? |
| 1045 |
AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00') |
1072 |
AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00') |
| 1046 |
"; |
1073 |
"; |
| 1047 |
|
1074 |
|
| 1048 |
$orderby = "biblioitems.publishercode,biblio.title" unless $orderby; |
1075 |
$orderby = "COALESCE(biblioitems.publishercode,deletedbiblioitems.publishercode), COALESCE(biblio.title,deletedbiblio.title)" unless $orderby; |
| 1049 |
$query .= " ORDER BY $orderby"; |
1076 |
$query .= " ORDER BY $orderby"; |
| 1050 |
my $result_set = |
1077 |
my $result_set = |
| 1051 |
$dbh->selectall_arrayref( $query, { Slice => {} }, $basketno ); |
1078 |
$dbh->selectall_arrayref( $query, { Slice => {} }, $basketno ); |
|
Lines 1072-1080
sub GetOrdersByBiblionumber {
Link Here
|
| 1072 |
return unless $biblionumber; |
1099 |
return unless $biblionumber; |
| 1073 |
my $dbh = C4::Context->dbh; |
1100 |
my $dbh = C4::Context->dbh; |
| 1074 |
my $query =" |
1101 |
my $query =" |
| 1075 |
SELECT biblio.*,biblioitems.*, |
1102 |
SELECT |
| 1076 |
aqorders.*, |
1103 |
biblio.*, biblioitems.*, |
| 1077 |
aqbudgets.* |
1104 |
aqorders.*, |
|
|
1105 |
aqbudgets.* |
| 1078 |
FROM aqorders |
1106 |
FROM aqorders |
| 1079 |
LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id |
1107 |
LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id |
| 1080 |
LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber |
1108 |
LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber |
|
Lines 1095-1102
sub GetOrdersByBiblionumber {
Link Here
|
| 1095 |
|
1123 |
|
| 1096 |
Looks up an order by order number. |
1124 |
Looks up an order by order number. |
| 1097 |
|
1125 |
|
| 1098 |
Returns a reference-to-hash describing the order. The keys of |
1126 |
Returns a reference-to-hash describing the order. |
| 1099 |
C<$order> are fields from the biblio, biblioitems, aqorders tables of the Koha database. |
1127 |
The keys of C<$order> are |
|
|
1128 |
- all fields from the aqorders table |
| 1129 |
- 2 fields from aqorders table returned with alternate names : rrp as unitpricesupplier, ecost as unitpricelib |
| 1130 |
- 1 field from aqbasket table : basketname |
| 1131 |
- 1 field from borrowers table : branchcode |
| 1132 |
- 1 field from aqbudgets table : budget_name returned as budget |
| 1133 |
- 2 fields from aqbooksellers : name returned as supplier, id returned as supplierid |
| 1134 |
- estimateddeliverydate : ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) |
| 1135 |
- quantity_to_receive : aqorders.quantity - COALESCE(aqorders.quantityreceived,0) |
| 1136 |
- subtotal : (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp |
| 1137 |
- orderdate : DATE (aqbasket.closedate) |
| 1138 |
- latesince : DATEDIFF(CURDATE( ),closedate) |
| 1139 |
- 5 fields of biblio or deletedbiblio tables : title, author, seriestitle, serial, copyrightdate |
| 1140 |
- 9 fields of biblioitems or deletedbiblioitems tables : publicationyear, publishercode, |
| 1141 |
editionstatement, issn, isbn, ean, itemtype, volume, number |
| 1142 |
If the record still exists, the biblionumber key contains the biblionumber |
| 1143 |
If the record have been deleted, the biblionumber key is blank, |
| 1144 |
and the deletedbiblionumber key contains the biblionumber of the deleted record. |
| 1145 |
|
| 1100 |
|
1146 |
|
| 1101 |
=cut |
1147 |
=cut |
| 1102 |
|
1148 |
|
|
Lines 1105-1123
sub GetOrder {
Link Here
|
| 1105 |
return unless $ordernumber; |
1151 |
return unless $ordernumber; |
| 1106 |
|
1152 |
|
| 1107 |
my $dbh = C4::Context->dbh; |
1153 |
my $dbh = C4::Context->dbh; |
|
|
1154 |
# FIXME publishercode column should probably be returned only as publisher or publishercode, but we must first investigate templates to check the use of this value |
| 1108 |
my $query = qq{SELECT |
1155 |
my $query = qq{SELECT |
|
|
1156 |
COALESCE(biblio.title, deletedbiblio.title) AS title, |
| 1157 |
COALESCE(biblio.author, deletedbiblio.author) AS author, |
| 1158 |
COALESCE(biblio.seriestitle, deletedbiblio.seriestitle) AS seriestitle, |
| 1159 |
COALESCE(biblio.serial, deletedbiblio.serial) AS serial, |
| 1160 |
COALESCE(biblio.copyrightdate, deletedbiblio.copyrightdate) AS copyrightdate, |
| 1161 |
COALESCE(biblioitems.publicationyear, deletedbiblioitems.publicationyear) AS publicationyear, |
| 1162 |
COALESCE(biblioitems.publishercode, deletedbiblioitems.publishercode) AS publishercode, |
| 1163 |
COALESCE(biblioitems.publishercode, deletedbiblioitems.publishercode) AS publisher, |
| 1164 |
COALESCE(biblioitems.editionstatement, deletedbiblioitems.editionstatement) AS editionstatement, |
| 1165 |
COALESCE(biblioitems.isbn, deletedbiblioitems.isbn) AS isbn, |
| 1166 |
COALESCE(biblioitems.issn, deletedbiblioitems.issn) AS issn, |
| 1167 |
COALESCE(biblioitems.ean, deletedbiblioitems.ean) AS ean, |
| 1168 |
COALESCE(biblioitems.itemtype, deletedbiblioitems.itemtype) AS itemtype, |
| 1169 |
COALESCE(biblioitems.volume, deletedbiblioitems.volume) AS volume, |
| 1170 |
COALESCE(biblioitems.number, deletedbiblioitems.number) AS number, |
| 1109 |
aqorders.*, |
1171 |
aqorders.*, |
| 1110 |
biblio.title, |
|
|
| 1111 |
biblio.author, |
| 1112 |
aqbasket.basketname, |
1172 |
aqbasket.basketname, |
| 1113 |
borrowers.branchcode, |
1173 |
borrowers.branchcode, |
| 1114 |
biblioitems.publicationyear, |
|
|
| 1115 |
biblio.copyrightdate, |
| 1116 |
biblioitems.editionstatement, |
| 1117 |
biblioitems.isbn, |
| 1118 |
biblioitems.ean, |
| 1119 |
biblio.seriestitle, |
| 1120 |
biblioitems.publishercode, |
| 1121 |
aqorders.rrp AS unitpricesupplier, |
1174 |
aqorders.rrp AS unitpricesupplier, |
| 1122 |
aqorders.ecost AS unitpricelib, |
1175 |
aqorders.ecost AS unitpricelib, |
| 1123 |
aqorders.claims_count AS claims_count, |
1176 |
aqorders.claims_count AS claims_count, |
|
Lines 1125-1131
sub GetOrder {
Link Here
|
| 1125 |
aqbudgets.budget_name AS budget, |
1178 |
aqbudgets.budget_name AS budget, |
| 1126 |
aqbooksellers.name AS supplier, |
1179 |
aqbooksellers.name AS supplier, |
| 1127 |
aqbooksellers.id AS supplierid, |
1180 |
aqbooksellers.id AS supplierid, |
| 1128 |
biblioitems.publishercode AS publisher, |
|
|
| 1129 |
ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate, |
1181 |
ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate, |
| 1130 |
DATE(aqbasket.closedate) AS orderdate, |
1182 |
DATE(aqbasket.closedate) AS orderdate, |
| 1131 |
aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity_to_receive, |
1183 |
aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity_to_receive, |
|
Lines 1133-1138
sub GetOrder {
Link Here
|
| 1133 |
DATEDIFF(CURDATE( ),closedate) AS latesince |
1185 |
DATEDIFF(CURDATE( ),closedate) AS latesince |
| 1134 |
FROM aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber |
1186 |
FROM aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber |
| 1135 |
LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber |
1187 |
LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber |
|
|
1188 |
LEFT JOIN deletedbiblio ON deletedbiblio.biblionumber=aqorders.deletedbiblionumber |
| 1189 |
LEFT JOIN deletedbiblioitems ON deletedbiblioitems.biblionumber=deletedbiblio.biblionumber |
| 1136 |
LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id, |
1190 |
LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id, |
| 1137 |
aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber |
1191 |
aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber |
| 1138 |
LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id |
1192 |
LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id |
|
Lines 1375-1383
sub ModItemOrder {
Link Here
|
| 1375 |
|
1429 |
|
| 1376 |
=head3 GetCancelledOrders |
1430 |
=head3 GetCancelledOrders |
| 1377 |
|
1431 |
|
| 1378 |
my @orders = GetCancelledOrders($basketno, $orderby); |
1432 |
my @orders = GetCancelledOrders($basketnumber, $orderby); |
| 1379 |
|
1433 |
|
| 1380 |
Returns cancelled orders for a basket |
1434 |
Looks up the cancelled orders with the given basketnumber. |
|
|
1435 |
Parameters: |
| 1436 |
- C<$basketnumber> : mandatory parameter, the basket number |
| 1437 |
- C<$orderby> : optional parameter, used to sort the results (must be a valid expression |
| 1438 |
using some fields of aqorders, aqbugdets, biblio, deletedbiblio, |
| 1439 |
biblioitems, deletedbiblioitems, aqorders_transfers tables) |
| 1440 |
If C<$orderby> is not provided, results are sorted according to publishercode and title |
| 1441 |
|
| 1442 |
Return : |
| 1443 |
The returned C<@orders> is an array of references-to-hash, whose keys are: |
| 1444 |
- the fields from the aqorders table |
| 1445 |
- the fields from the aqbudgets table |
| 1446 |
- 2 fields from aqorders_transfers table : ordernumber_from, return as transferred_from |
| 1447 |
and timestamp, return as transferred_from_timestamp |
| 1448 |
- 5 fields of biblio or deletedbiblio tables : title, author, seriestitle, serial, copyrightdate |
| 1449 |
- 9 fields of biblioitems or deletedbiblioitems tables : publicationyear, publishercode, |
| 1450 |
editionstatement, issn, isbn, ean, itemtype, volume, number |
| 1451 |
If the record still exists, the biblionumber key contains the biblionumber |
| 1452 |
If the record have been deleted, the biblionumber key is blank, |
| 1453 |
and the deletedbiblionumber key contains the biblionumber of the deleted record. |
| 1381 |
|
1454 |
|
| 1382 |
=cut |
1455 |
=cut |
| 1383 |
|
1456 |
|
|
Lines 1389-1396
sub GetCancelledOrders {
Link Here
|
| 1389 |
my $dbh = C4::Context->dbh; |
1462 |
my $dbh = C4::Context->dbh; |
| 1390 |
my $query = " |
1463 |
my $query = " |
| 1391 |
SELECT |
1464 |
SELECT |
| 1392 |
biblio.*, |
1465 |
COALESCE(biblio.title, deletedbiblio.title) AS title, |
| 1393 |
biblioitems.*, |
1466 |
COALESCE(biblio.author, deletedbiblio.author) AS author, |
|
|
1467 |
COALESCE(biblio.seriestitle, deletedbiblio.seriestitle) AS seriestitle, |
| 1468 |
COALESCE(biblio.serial, deletedbiblio.serial) AS serial, |
| 1469 |
COALESCE(biblio.copyrightdate, deletedbiblio.copyrightdate) AS copyrightdate, |
| 1470 |
COALESCE(biblioitems.publicationyear, deletedbiblioitems.publicationyear) AS publicationyear, |
| 1471 |
COALESCE(biblioitems.publishercode, deletedbiblioitems.publishercode) AS publishercode, |
| 1472 |
COALESCE(biblioitems.editionstatement, deletedbiblioitems.editionstatement) AS editionstatement, |
| 1473 |
COALESCE(biblioitems.isbn, deletedbiblioitems.isbn) AS isbn, |
| 1474 |
COALESCE(biblioitems.issn, deletedbiblioitems.issn) AS issn, |
| 1475 |
COALESCE(biblioitems.ean, deletedbiblioitems.ean) AS ean, |
| 1476 |
COALESCE(biblioitems.itemtype, deletedbiblioitems.itemtype) AS itemtype, |
| 1477 |
COALESCE(biblioitems.volume, deletedbiblioitems.volume) AS volume, |
| 1478 |
COALESCE(biblioitems.number, deletedbiblioitems.number) AS number, |
| 1394 |
aqorders.*, |
1479 |
aqorders.*, |
| 1395 |
aqbudgets.*, |
1480 |
aqbudgets.*, |
| 1396 |
aqorders_transfers.ordernumber_to AS transferred_to, |
1481 |
aqorders_transfers.ordernumber_to AS transferred_to, |
|
Lines 1399-1404
sub GetCancelledOrders {
Link Here
|
| 1399 |
LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id |
1484 |
LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id |
| 1400 |
LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber |
1485 |
LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber |
| 1401 |
LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber |
1486 |
LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber |
|
|
1487 |
LEFT JOIN deletedbiblio ON deletedbiblio.biblionumber=aqorders.deletedbiblionumber |
| 1488 |
LEFT JOIN deletedbiblioitems ON deletedbiblioitems.biblionumber=deletedbiblio.biblionumber |
| 1402 |
LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_from = aqorders.ordernumber |
1489 |
LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_from = aqorders.ordernumber |
| 1403 |
WHERE basketno = ? |
1490 |
WHERE basketno = ? |
| 1404 |
AND (datecancellationprinted IS NOT NULL |
1491 |
AND (datecancellationprinted IS NOT NULL |
|
Lines 1643-1649
C<$ordered> Finds orders to receive only (status 'ordered' or 'partial').
Link Here
|
| 1643 |
|
1730 |
|
| 1644 |
|
1731 |
|
| 1645 |
C<@results> is an array of references-to-hash with the keys are fields |
1732 |
C<@results> is an array of references-to-hash with the keys are fields |
| 1646 |
from aqorders, biblio, biblioitems and aqbasket tables. |
1733 |
from aqorders, biblio, deletedbiblio, biblioitems, deletedbiblioitems, |
|
|
1734 |
aqbasket and aqbasketgroups tables. |
| 1735 |
If the record still exists, the biblionumber key contains the biblionumber |
| 1736 |
If the record have been deleted, the biblionumber key is blank, |
| 1737 |
and the deletedbiblionumber key contains the biblionumber of the deleted record. |
| 1647 |
|
1738 |
|
| 1648 |
=cut |
1739 |
=cut |
| 1649 |
|
1740 |
|
|
Lines 1666-1674
sub SearchOrders {
Link Here
|
| 1666 |
SELECT aqbasket.basketno, |
1757 |
SELECT aqbasket.basketno, |
| 1667 |
borrowers.surname, |
1758 |
borrowers.surname, |
| 1668 |
borrowers.firstname, |
1759 |
borrowers.firstname, |
| 1669 |
biblio.*, |
1760 |
COALESCE(biblio.title, deletedbiblio.title) AS title, |
| 1670 |
biblioitems.isbn, |
1761 |
COALESCE(biblio.author, deletedbiblio.author) AS author, |
| 1671 |
biblioitems.biblioitemnumber, |
1762 |
COALESCE(biblio.seriestitle, deletedbiblio.seriestitle) AS seriestitle, |
|
|
1763 |
COALESCE(biblio.serial, deletedbiblio.serial) AS serial, |
| 1764 |
COALESCE(biblio.copyrightdate, deletedbiblio.copyrightdate) AS copyrightdate, |
| 1765 |
COALESCE(biblioitems.publicationyear, deletedbiblioitems.publicationyear) AS publicationyear, |
| 1766 |
COALESCE(biblioitems.publishercode, deletedbiblioitems.publishercode) AS publishercode, |
| 1767 |
COALESCE(biblioitems.editionstatement, deletedbiblioitems.editionstatement) AS editionstatement, |
| 1768 |
COALESCE(biblioitems.isbn, deletedbiblioitems.isbn) AS isbn, |
| 1769 |
COALESCE(biblioitems.issn, deletedbiblioitems.issn) AS issn, |
| 1770 |
COALESCE(biblioitems.ean, deletedbiblioitems.ean) AS ean, |
| 1771 |
COALESCE(biblioitems.itemtype, deletedbiblioitems.itemtype) AS itemtype, |
| 1772 |
COALESCE(biblioitems.volume, deletedbiblioitems.volume) AS volume, |
| 1773 |
COALESCE(biblioitems.number, deletedbiblioitems.number) AS number, |
| 1672 |
aqbasket.authorisedby, |
1774 |
aqbasket.authorisedby, |
| 1673 |
aqbasket.booksellerid, |
1775 |
aqbasket.booksellerid, |
| 1674 |
aqbasket.closedate, |
1776 |
aqbasket.closedate, |
|
Lines 1683-1688
sub SearchOrders {
Link Here
|
| 1683 |
LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber |
1785 |
LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber |
| 1684 |
LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber |
1786 |
LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber |
| 1685 |
LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber |
1787 |
LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber |
|
|
1788 |
LEFT JOIN deletedbiblio ON aqorders.deletedbiblionumber=deletedbiblio.biblionumber |
| 1789 |
LEFT JOIN deletedbiblioitems ON deletedbiblioitems.biblionumber=deletedbiblio.biblionumber |
| 1686 |
WHERE (datecancellationprinted is NULL) |
1790 |
WHERE (datecancellationprinted is NULL) |
| 1687 |
}; |
1791 |
}; |
| 1688 |
|
1792 |
|
|
Lines 1711-1721
sub SearchOrders {
Link Here
|
| 1711 |
push @args, $ordernumber; |
1815 |
push @args, $ordernumber; |
| 1712 |
} |
1816 |
} |
| 1713 |
if( $search ) { |
1817 |
if( $search ) { |
| 1714 |
$query .= ' AND (biblio.title LIKE ? OR biblio.author LIKE ? OR biblioitems.isbn LIKE ?)'; |
1818 |
$query .= ' AND (COALESCE (biblio.title, deletedbiblio.title) LIKE ? OR COALESCE (biblio.author, deletedbiblio.author) LIKE ? OR COALESCE (biblioitems.isbn, deletedbiblioitems.isbn) LIKE ?)'; |
| 1715 |
push @args, ("%$search%","%$search%","%$search%"); |
1819 |
push @args, ("%$search%","%$search%","%$search%"); |
| 1716 |
} |
1820 |
} |
| 1717 |
if ( $ean ) { |
1821 |
if ( $ean ) { |
| 1718 |
$query .= ' AND biblioitems.ean = ?'; |
1822 |
$query .= ' AND (COALESCE biblioitems.ean,deletedbiblioitems.ean) = ?'; |
| 1719 |
push @args, $ean; |
1823 |
push @args, $ean; |
| 1720 |
} |
1824 |
} |
| 1721 |
if ( $booksellerid ) { |
1825 |
if ( $booksellerid ) { |
|
Lines 1849-1855
Looks up all of the received items from the supplier with the given
Link Here
|
| 1849 |
bookseller ID at the given date, for the given code (bookseller Invoice number). Ignores cancelled and completed orders. |
1953 |
bookseller ID at the given date, for the given code (bookseller Invoice number). Ignores cancelled and completed orders. |
| 1850 |
|
1954 |
|
| 1851 |
C<@results> is an array of references-to-hash. The keys of each element are fields from |
1955 |
C<@results> is an array of references-to-hash. The keys of each element are fields from |
| 1852 |
the aqorders, biblio, and biblioitems tables of the Koha database. |
1956 |
the aqorders, aqbasket, aqinvoices, borrowers, biblio, and deletedbiblio tables of the Koha database. |
| 1853 |
|
1957 |
|
| 1854 |
C<@results> is sorted alphabetically by book title. |
1958 |
C<@results> is sorted alphabetically by book title. |
| 1855 |
|
1959 |
|
|
Lines 1878-1888
sub GetParcel {
Link Here
|
| 1878 |
aqorders.rrp, |
1982 |
aqorders.rrp, |
| 1879 |
aqorders.ecost, |
1983 |
aqorders.ecost, |
| 1880 |
aqorders.gstrate, |
1984 |
aqorders.gstrate, |
| 1881 |
biblio.title |
1985 |
COALESCE (biblio.title, deletedbiblio.title) AS title |
| 1882 |
FROM aqorders |
1986 |
FROM aqorders |
| 1883 |
LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno |
1987 |
LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno |
| 1884 |
LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber |
1988 |
LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber |
| 1885 |
LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber |
1989 |
LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber |
|
|
1990 |
LEFT JOIN deletedbiblio ON aqorders.deletedbiblionumber=deletedbiblio.biblionumber |
| 1886 |
LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid |
1991 |
LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid |
| 1887 |
WHERE |
1992 |
WHERE |
| 1888 |
aqbasket.booksellerid = ? |
1993 |
aqbasket.booksellerid = ? |
|
Lines 1998-2010
sub GetParcels {
Link Here
|
| 1998 |
|
2103 |
|
| 1999 |
=head3 GetLateOrders |
2104 |
=head3 GetLateOrders |
| 2000 |
|
2105 |
|
| 2001 |
@results = &GetLateOrders; |
2106 |
@results = &GetLateOrders ($delay, $supplierid, $branch, $estimateddeliverydatefrom, $estimateddeliverydateto) ; |
| 2002 |
|
2107 |
|
| 2003 |
Searches for bookseller with late orders. |
2108 |
Looks up the orders to receive (non cancelled and non completely received) from a given supplier. |
| 2004 |
|
2109 |
|
| 2005 |
return: |
2110 |
return: |
| 2006 |
the table of supplier with late issues. This table is full of hashref. |
2111 |
the table of supplier with late issues. This table is full of hashref. |
| 2007 |
|
2112 |
|
|
|
2113 |
Looks up the cancelled orders with the given basketnumber. |
| 2114 |
Optional parameters are used to limit the results : |
| 2115 |
- C<$delay> : the number of days after the closing of the basket after which an order is late |
| 2116 |
- C<$supplierid> : the id of the vendor this function has to get orders |
| 2117 |
- C<$branch> : the branchcode of the branch this function has to get orders |
| 2118 |
- C<$estimateddeliverydatefrom> : the oldest expected date of delivery (based on aqbooksellers.deliverytime) |
| 2119 |
- C<$estimateddeliverydateto> : the most recent expected date of delivery (based on aqbooksellers.deliverytime). If only |
| 2120 |
C<$estimateddeliverydatefrom> is provided and not C<$estimateddeliverydatefrom>, the current day is used as most recent |
| 2121 |
expected date of delivery. |
| 2122 |
|
| 2123 |
Return : |
| 2124 |
The returned C<@results> is an array of references-to-hash, whose keys are: |
| 2125 |
- 9 fields from the aqorders table : ordernumber, biblionumber, deletedbiblionumber, claims_count, claims_date, |
| 2126 |
closedate as orderdate, rrp as unitpricesupplier, ecost in unitpricelib, budget_name as budget |
| 2127 |
- 1 field from the aqbasket table : basketno |
| 2128 |
- 1 field from the borrowers table : branchcode as branch |
| 2129 |
- 2 fields from aqbooksellers : name as supplier, id as supplierid |
| 2130 |
- 2 fields of biblio or deletedbiblio tables : title, author |
| 2131 |
- 2 fields of biblioitems or deletedbiblioitems tables : publishercode as publisher, publicationyear |
| 2132 |
- estimateddeliverydate : ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) |
| 2133 |
- quantity ; aqorders.quantity - COALESCE(aqorders.quantityreceived,0) |
| 2134 |
- subtotal : (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal |
| 2135 |
- latesince : DATEDIFF(CAST(now() AS date),closedate) AS latesince |
| 2136 |
If the record still exists, the biblionumber key contains the biblionumber |
| 2137 |
If the record have been deleted, the biblionumber key is blank, |
| 2138 |
and the deletedbiblionumber key contains the biblionumber of the deleted record. |
| 2139 |
|
| 2140 |
|
| 2008 |
=cut |
2141 |
=cut |
| 2009 |
|
2142 |
|
| 2010 |
sub GetLateOrders { |
2143 |
sub GetLateOrders { |
|
Lines 2023-2028
sub GetLateOrders {
Link Here
|
| 2023 |
my $select = " |
2156 |
my $select = " |
| 2024 |
SELECT aqbasket.basketno, |
2157 |
SELECT aqbasket.basketno, |
| 2025 |
aqorders.ordernumber, |
2158 |
aqorders.ordernumber, |
|
|
2159 |
aqorders.biblionumber, |
| 2160 |
aqorders.deletedbiblionumber, |
| 2026 |
DATE(aqbasket.closedate) AS orderdate, |
2161 |
DATE(aqbasket.closedate) AS orderdate, |
| 2027 |
aqorders.rrp AS unitpricesupplier, |
2162 |
aqorders.rrp AS unitpricesupplier, |
| 2028 |
aqorders.ecost AS unitpricelib, |
2163 |
aqorders.ecost AS unitpricelib, |
|
Lines 2032-2046
sub GetLateOrders {
Link Here
|
| 2032 |
borrowers.branchcode AS branch, |
2167 |
borrowers.branchcode AS branch, |
| 2033 |
aqbooksellers.name AS supplier, |
2168 |
aqbooksellers.name AS supplier, |
| 2034 |
aqbooksellers.id AS supplierid, |
2169 |
aqbooksellers.id AS supplierid, |
| 2035 |
biblio.author, biblio.title, |
2170 |
COALESCE (biblio.author, deletedbiblio.author) AS author, |
| 2036 |
biblioitems.publishercode AS publisher, |
2171 |
COALESCE (biblio.title, deletedbiblio.title) AS title, |
| 2037 |
biblioitems.publicationyear, |
2172 |
COALESCE (biblioitems.publishercode, deletedbiblioitems.publishercode) AS publisher, |
|
|
2173 |
COALESCE (biblioitems.publicationyear, deletedbiblioitems.publicationyear) AS publicationyear, |
| 2038 |
ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate, |
2174 |
ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate, |
| 2039 |
"; |
2175 |
"; |
| 2040 |
my $from = " |
2176 |
my $from = " |
| 2041 |
FROM |
2177 |
FROM |
| 2042 |
aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber |
2178 |
aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber |
| 2043 |
LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber |
2179 |
LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber |
|
|
2180 |
LEFT JOIN deletedbiblio ON deletedbiblio.biblionumber = aqorders.deletedbiblionumber |
| 2181 |
LEFT JOIN deletedbiblioitems ON deletedbiblioitems.biblionumber = deletedbiblio.biblionumber |
| 2044 |
LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id, |
2182 |
LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id, |
| 2045 |
aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber |
2183 |
aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber |
| 2046 |
LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id |
2184 |
LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id |
|
Lines 2169-2174
returns:
Link Here
|
| 2169 |
$total_qty is the sum of all of the quantities in $order_loop |
2307 |
$total_qty is the sum of all of the quantities in $order_loop |
| 2170 |
$total_price is the cost of each in $order_loop times the quantity |
2308 |
$total_price is the cost of each in $order_loop times the quantity |
| 2171 |
$total_qtyreceived is the sum of all of the quantityreceived entries in $order_loop |
2309 |
$total_qtyreceived is the sum of all of the quantityreceived entries in $order_loop |
|
|
2310 |
If the record still exists, the biblionumber key contains the biblionumber |
| 2311 |
If the record have been deleted, the biblionumber key is blank, |
| 2312 |
and the deletedbiblionumber key contains the biblionumber of the deleted record. |
| 2172 |
|
2313 |
|
| 2173 |
=cut |
2314 |
=cut |
| 2174 |
|
2315 |
|
|
Lines 2203-2208
sub GetHistory {
Link Here
|
| 2203 |
COALESCE(biblio.author, deletedbiblio.author) AS author, |
2344 |
COALESCE(biblio.author, deletedbiblio.author) AS author, |
| 2204 |
COALESCE(biblioitems.isbn, deletedbiblioitems.isbn) AS isbn, |
2345 |
COALESCE(biblioitems.isbn, deletedbiblioitems.isbn) AS isbn, |
| 2205 |
COALESCE(biblioitems.ean, deletedbiblioitems.ean) AS ean, |
2346 |
COALESCE(biblioitems.ean, deletedbiblioitems.ean) AS ean, |
|
|
2347 |
aqorders.deletedbiblionumber, |
| 2206 |
aqorders.basketno, |
2348 |
aqorders.basketno, |
| 2207 |
aqbasket.basketname, |
2349 |
aqbasket.basketname, |
| 2208 |
aqbasket.basketgroupid, |
2350 |
aqbasket.basketgroupid, |
|
Lines 2232-2239
sub GetHistory {
Link Here
|
| 2232 |
LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber |
2374 |
LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber |
| 2233 |
LEFT JOIN aqbudgets ON aqorders.budget_id=aqbudgets.budget_id |
2375 |
LEFT JOIN aqbudgets ON aqorders.budget_id=aqbudgets.budget_id |
| 2234 |
LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid |
2376 |
LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid |
| 2235 |
LEFT JOIN deletedbiblio ON deletedbiblio.biblionumber=aqorders.biblionumber |
2377 |
LEFT JOIN deletedbiblio ON deletedbiblio.biblionumber=aqorders.deletedbiblionumber |
| 2236 |
LEFT JOIN deletedbiblioitems ON deletedbiblioitems.biblionumber=aqorders.biblionumber |
2378 |
LEFT JOIN deletedbiblioitems ON deletedbiblioitems.biblionumber=deletedbiblio.biblionumber |
| 2237 |
"; |
2379 |
"; |
| 2238 |
|
2380 |
|
| 2239 |
if ( C4::Context->preference("IndependentBranches") ) { |
2381 |
if ( C4::Context->preference("IndependentBranches") ) { |
|
Lines 2254-2275
sub GetHistory {
Link Here
|
| 2254 |
} |
2396 |
} |
| 2255 |
|
2397 |
|
| 2256 |
if ( $title ) { |
2398 |
if ( $title ) { |
| 2257 |
$query .= " AND biblio.title LIKE ? "; |
2399 |
$query .= " AND COALESCE (biblio.title,deletedbiblio.title) LIKE ? "; |
| 2258 |
$title =~ s/\s+/%/g; |
2400 |
$title =~ s/\s+/%/g; |
| 2259 |
push @query_params, "%$title%"; |
2401 |
push @query_params, "%$title%"; |
| 2260 |
} |
2402 |
} |
| 2261 |
|
2403 |
|
| 2262 |
if ( $author ) { |
2404 |
if ( $author ) { |
| 2263 |
$query .= " AND biblio.author LIKE ? "; |
2405 |
$query .= " AND COALESCE (biblio.author,deletedbiblio.author) LIKE ? "; |
| 2264 |
push @query_params, "%$author%"; |
2406 |
push @query_params, "%$author%"; |
| 2265 |
} |
2407 |
} |
| 2266 |
|
2408 |
|
| 2267 |
if ( $isbn ) { |
2409 |
if ( $isbn ) { |
| 2268 |
$query .= " AND biblioitems.isbn LIKE ? "; |
2410 |
$query .= " AND COALESCE (biblioitems.isbn,deletedbiblioitems.isbn) LIKE ? "; |
| 2269 |
push @query_params, "%$isbn%"; |
2411 |
push @query_params, "%$isbn%"; |
| 2270 |
} |
2412 |
} |
| 2271 |
if ( $ean ) { |
2413 |
if ( $ean ) { |
| 2272 |
$query .= " AND biblioitems.ean = ? "; |
2414 |
$query .= " AND OALESCE (biblioitems.ean,deletedbiblioitems.ean) = ? "; |
| 2273 |
push @query_params, "$ean"; |
2415 |
push @query_params, "$ean"; |
| 2274 |
} |
2416 |
} |
| 2275 |
if ( $name ) { |
2417 |
if ( $name ) { |