|
Lines 1033-1046
sub GetBasketgroups {
Link Here
|
| 1033 |
|
1033 |
|
| 1034 |
@orders = &GetOrders($basketnumber, $orderby); |
1034 |
@orders = &GetOrders($basketnumber, $orderby); |
| 1035 |
|
1035 |
|
| 1036 |
Looks up the pending (non-cancelled) orders with the given basket |
1036 |
Looks up the non-cancelled (pending and received) orders with the given basketnumber. |
| 1037 |
number. If C<$booksellerID> is non-empty, only orders from that seller |
1037 |
Parameters: |
| 1038 |
are returned. |
1038 |
- C<$basketnumber> : mandatory parameter, the basket number |
| 1039 |
|
1039 |
- C<$orderby> : optional parameter, used to sort the results (must be a valid expression |
| 1040 |
return : |
1040 |
using some fields of aqorders, aqbugdets, biblio, deletedbiblio, |
| 1041 |
C<&basket> returns a two-element array. C<@orders> is an array of |
1041 |
biblioitems, deletedbiblioitems, aqorders_transfers tables) |
| 1042 |
references-to-hash, whose keys are the fields from the aqorders, |
1042 |
If C<$orderby> is not provided, results are sorted according to publishercode and title |
| 1043 |
biblio, and biblioitems tables in the Koha database. |
1043 |
|
|
|
1044 |
Return : |
| 1045 |
The returned C<@orders> is an array of references-to-hash, whose keys are: |
| 1046 |
- the fields from the aqorders table |
| 1047 |
- the fields from the aqbudgets table |
| 1048 |
- 2 fields from aqorders_transfers table : ordernumber_from, return as transferred_from |
| 1049 |
and timestamp, return as transferred_from_timestamp |
| 1050 |
- 5 fields of biblio or deletedbiblio tables : title, author, seriestitle, serial, copyrightdate |
| 1051 |
- 9 fields of biblioitems or deletedbiblioitems tables : publicationyear, publishercode, |
| 1052 |
editionstatement, issn, isbn, ean, itemtype, volume, number |
| 1053 |
If the record still exists, the biblionumber key contains the biblionumber |
| 1054 |
If the record have been deleted, the biblionumber key is blank, |
| 1055 |
and the deletedbiblionumber key contains the biblionumber of the deleted record. |
| 1044 |
|
1056 |
|
| 1045 |
=cut |
1057 |
=cut |
| 1046 |
|
1058 |
|
|
Lines 1049-1070
sub GetOrders {
Link Here
|
| 1049 |
return () unless $basketno; |
1061 |
return () unless $basketno; |
| 1050 |
my $dbh = C4::Context->dbh; |
1062 |
my $dbh = C4::Context->dbh; |
| 1051 |
my $query =" |
1063 |
my $query =" |
| 1052 |
SELECT biblio.*,biblioitems.*, |
1064 |
SELECT |
|
|
1065 |
COALESCE(biblio.title, deletedbiblio.title) AS title, |
| 1066 |
COALESCE(biblio.author, deletedbiblio.author) AS author, |
| 1067 |
COALESCE(biblio.seriestitle, deletedbiblio.seriestitle) AS seriestitle, |
| 1068 |
COALESCE(biblio.serial, deletedbiblio.serial) AS serial, |
| 1069 |
COALESCE(biblio.copyrightdate, deletedbiblio.copyrightdate) AS copyrightdate, |
| 1070 |
COALESCE(biblioitems.publicationyear, deletedbiblioitems.publicationyear) AS publicationyear, |
| 1071 |
COALESCE(biblioitems.publishercode, deletedbiblioitems.publishercode) AS publishercode, |
| 1072 |
COALESCE(biblioitems.editionstatement, deletedbiblioitems.editionstatement) AS editionstatement, |
| 1073 |
COALESCE(biblioitems.isbn, deletedbiblioitems.isbn) AS isbn, |
| 1074 |
COALESCE(biblioitems.issn, deletedbiblioitems.issn) AS issn, |
| 1075 |
COALESCE(biblioitems.ean, deletedbiblioitems.ean) AS ean, |
| 1076 |
COALESCE(biblioitems.itemtype, deletedbiblioitems.itemtype) AS itemtype, |
| 1077 |
COALESCE(biblioitems.volume, deletedbiblioitems.volume) AS volume, |
| 1078 |
COALESCE(biblioitems.number, deletedbiblioitems.number) AS number, |
| 1053 |
aqorders.*, |
1079 |
aqorders.*, |
| 1054 |
aqbudgets.*, |
1080 |
aqbudgets.*, |
| 1055 |
biblio.title, |
|
|
| 1056 |
aqorders_transfers.ordernumber_from AS transferred_from, |
1081 |
aqorders_transfers.ordernumber_from AS transferred_from, |
| 1057 |
aqorders_transfers.timestamp AS transferred_from_timestamp |
1082 |
aqorders_transfers.timestamp AS transferred_from_timestamp |
| 1058 |
FROM aqorders |
1083 |
FROM aqorders |
| 1059 |
LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id |
1084 |
LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id |
| 1060 |
LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber |
1085 |
LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber |
| 1061 |
LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber |
1086 |
LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber |
|
|
1087 |
LEFT JOIN deletedbiblio ON deletedbiblio.biblionumber=aqorders.deletedbiblionumber |
| 1088 |
LEFT JOIN deletedbiblioitems ON deletedbiblioitems.biblionumber=deletedbiblio.biblionumber |
| 1062 |
LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_to = aqorders.ordernumber |
1089 |
LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_to = aqorders.ordernumber |
| 1063 |
WHERE basketno=? |
1090 |
WHERE basketno=? |
| 1064 |
AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00') |
1091 |
AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00') |
| 1065 |
"; |
1092 |
"; |
| 1066 |
|
1093 |
|
| 1067 |
$orderby = "biblioitems.publishercode,biblio.title" unless $orderby; |
1094 |
$orderby = "COALESCE(biblioitems.publishercode,deletedbiblioitems.publishercode), COALESCE(biblio.title,deletedbiblio.title)" unless $orderby; |
| 1068 |
$query .= " ORDER BY $orderby"; |
1095 |
$query .= " ORDER BY $orderby"; |
| 1069 |
my $result_set = |
1096 |
my $result_set = |
| 1070 |
$dbh->selectall_arrayref( $query, { Slice => {} }, $basketno ); |
1097 |
$dbh->selectall_arrayref( $query, { Slice => {} }, $basketno ); |
|
Lines 1091-1099
sub GetOrdersByBiblionumber {
Link Here
|
| 1091 |
return unless $biblionumber; |
1118 |
return unless $biblionumber; |
| 1092 |
my $dbh = C4::Context->dbh; |
1119 |
my $dbh = C4::Context->dbh; |
| 1093 |
my $query =" |
1120 |
my $query =" |
| 1094 |
SELECT biblio.*,biblioitems.*, |
1121 |
SELECT |
| 1095 |
aqorders.*, |
1122 |
biblio.*, biblioitems.*, |
| 1096 |
aqbudgets.* |
1123 |
aqorders.*, |
|
|
1124 |
aqbudgets.* |
| 1097 |
FROM aqorders |
1125 |
FROM aqorders |
| 1098 |
LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id |
1126 |
LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id |
| 1099 |
LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber |
1127 |
LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber |
|
Lines 1114-1121
sub GetOrdersByBiblionumber {
Link Here
|
| 1114 |
|
1142 |
|
| 1115 |
Looks up an order by order number. |
1143 |
Looks up an order by order number. |
| 1116 |
|
1144 |
|
| 1117 |
Returns a reference-to-hash describing the order. The keys of |
1145 |
Returns a reference-to-hash describing the order. |
| 1118 |
C<$order> are fields from the biblio, biblioitems, aqorders tables of the Koha database. |
1146 |
The keys of C<$order> are |
|
|
1147 |
- all fields from the aqorders table |
| 1148 |
- 2 fields from aqorders table returned with alternate names : rrp as unitpricesupplier, ecost as unitpricelib |
| 1149 |
- 1 field from aqbasket table : basketname |
| 1150 |
- 1 field from borrowers table : branchcode |
| 1151 |
- 1 field from aqbudgets table : budget_name returned as budget |
| 1152 |
- 2 fields from aqbooksellers : name returned as supplier, id returned as supplierid |
| 1153 |
- estimateddeliverydate : ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) |
| 1154 |
- quantity_to_receive : aqorders.quantity - COALESCE(aqorders.quantityreceived,0) |
| 1155 |
- subtotal : (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp |
| 1156 |
- orderdate : DATE (aqbasket.closedate) |
| 1157 |
- latesince : DATEDIFF(CURDATE( ),closedate) |
| 1158 |
- 5 fields of biblio or deletedbiblio tables : title, author, seriestitle, serial, copyrightdate |
| 1159 |
- 9 fields of biblioitems or deletedbiblioitems tables : publicationyear, publishercode, |
| 1160 |
editionstatement, issn, isbn, ean, itemtype, volume, number |
| 1161 |
If the record still exists, the biblionumber key contains the biblionumber |
| 1162 |
If the record have been deleted, the biblionumber key is blank, |
| 1163 |
and the deletedbiblionumber key contains the biblionumber of the deleted record. |
| 1164 |
|
| 1119 |
|
1165 |
|
| 1120 |
=cut |
1166 |
=cut |
| 1121 |
|
1167 |
|
|
Lines 1124-1142
sub GetOrder {
Link Here
|
| 1124 |
return unless $ordernumber; |
1170 |
return unless $ordernumber; |
| 1125 |
|
1171 |
|
| 1126 |
my $dbh = C4::Context->dbh; |
1172 |
my $dbh = C4::Context->dbh; |
|
|
1173 |
# 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 |
| 1127 |
my $query = qq{SELECT |
1174 |
my $query = qq{SELECT |
|
|
1175 |
COALESCE(biblio.title, deletedbiblio.title) AS title, |
| 1176 |
COALESCE(biblio.author, deletedbiblio.author) AS author, |
| 1177 |
COALESCE(biblio.seriestitle, deletedbiblio.seriestitle) AS seriestitle, |
| 1178 |
COALESCE(biblio.serial, deletedbiblio.serial) AS serial, |
| 1179 |
COALESCE(biblio.copyrightdate, deletedbiblio.copyrightdate) AS copyrightdate, |
| 1180 |
COALESCE(biblioitems.publicationyear, deletedbiblioitems.publicationyear) AS publicationyear, |
| 1181 |
COALESCE(biblioitems.publishercode, deletedbiblioitems.publishercode) AS publishercode, |
| 1182 |
COALESCE(biblioitems.publishercode, deletedbiblioitems.publishercode) AS publisher, |
| 1183 |
COALESCE(biblioitems.editionstatement, deletedbiblioitems.editionstatement) AS editionstatement, |
| 1184 |
COALESCE(biblioitems.isbn, deletedbiblioitems.isbn) AS isbn, |
| 1185 |
COALESCE(biblioitems.issn, deletedbiblioitems.issn) AS issn, |
| 1186 |
COALESCE(biblioitems.ean, deletedbiblioitems.ean) AS ean, |
| 1187 |
COALESCE(biblioitems.itemtype, deletedbiblioitems.itemtype) AS itemtype, |
| 1188 |
COALESCE(biblioitems.volume, deletedbiblioitems.volume) AS volume, |
| 1189 |
COALESCE(biblioitems.number, deletedbiblioitems.number) AS number, |
| 1128 |
aqorders.*, |
1190 |
aqorders.*, |
| 1129 |
biblio.title, |
|
|
| 1130 |
biblio.author, |
| 1131 |
aqbasket.basketname, |
1191 |
aqbasket.basketname, |
| 1132 |
borrowers.branchcode, |
1192 |
borrowers.branchcode, |
| 1133 |
biblioitems.publicationyear, |
|
|
| 1134 |
biblio.copyrightdate, |
| 1135 |
biblioitems.editionstatement, |
| 1136 |
biblioitems.isbn, |
| 1137 |
biblioitems.ean, |
| 1138 |
biblio.seriestitle, |
| 1139 |
biblioitems.publishercode, |
| 1140 |
aqorders.rrp AS unitpricesupplier, |
1193 |
aqorders.rrp AS unitpricesupplier, |
| 1141 |
aqorders.ecost AS unitpricelib, |
1194 |
aqorders.ecost AS unitpricelib, |
| 1142 |
aqorders.claims_count AS claims_count, |
1195 |
aqorders.claims_count AS claims_count, |
|
Lines 1144-1150
sub GetOrder {
Link Here
|
| 1144 |
aqbudgets.budget_name AS budget, |
1197 |
aqbudgets.budget_name AS budget, |
| 1145 |
aqbooksellers.name AS supplier, |
1198 |
aqbooksellers.name AS supplier, |
| 1146 |
aqbooksellers.id AS supplierid, |
1199 |
aqbooksellers.id AS supplierid, |
| 1147 |
biblioitems.publishercode AS publisher, |
|
|
| 1148 |
ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate, |
1200 |
ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate, |
| 1149 |
DATE(aqbasket.closedate) AS orderdate, |
1201 |
DATE(aqbasket.closedate) AS orderdate, |
| 1150 |
aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity_to_receive, |
1202 |
aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity_to_receive, |
|
Lines 1152-1157
sub GetOrder {
Link Here
|
| 1152 |
DATEDIFF(CURDATE( ),closedate) AS latesince |
1204 |
DATEDIFF(CURDATE( ),closedate) AS latesince |
| 1153 |
FROM aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber |
1205 |
FROM aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber |
| 1154 |
LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber |
1206 |
LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber |
|
|
1207 |
LEFT JOIN deletedbiblio ON deletedbiblio.biblionumber=aqorders.deletedbiblionumber |
| 1208 |
LEFT JOIN deletedbiblioitems ON deletedbiblioitems.biblionumber=deletedbiblio.biblionumber |
| 1155 |
LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id, |
1209 |
LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id, |
| 1156 |
aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber |
1210 |
aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber |
| 1157 |
LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id |
1211 |
LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id |
|
Lines 1394-1402
sub ModItemOrder {
Link Here
|
| 1394 |
|
1448 |
|
| 1395 |
=head3 GetCancelledOrders |
1449 |
=head3 GetCancelledOrders |
| 1396 |
|
1450 |
|
| 1397 |
my @orders = GetCancelledOrders($basketno, $orderby); |
1451 |
my @orders = GetCancelledOrders($basketnumber, $orderby); |
| 1398 |
|
1452 |
|
| 1399 |
Returns cancelled orders for a basket |
1453 |
Looks up the cancelled orders with the given basketnumber. |
|
|
1454 |
Parameters: |
| 1455 |
- C<$basketnumber> : mandatory parameter, the basket number |
| 1456 |
- C<$orderby> : optional parameter, used to sort the results (must be a valid expression |
| 1457 |
using some fields of aqorders, aqbugdets, biblio, deletedbiblio, |
| 1458 |
biblioitems, deletedbiblioitems, aqorders_transfers tables) |
| 1459 |
If C<$orderby> is not provided, results are sorted according to publishercode and title |
| 1460 |
|
| 1461 |
Return : |
| 1462 |
The returned C<@orders> is an array of references-to-hash, whose keys are: |
| 1463 |
- the fields from the aqorders table |
| 1464 |
- the fields from the aqbudgets table |
| 1465 |
- 2 fields from aqorders_transfers table : ordernumber_from, return as transferred_from |
| 1466 |
and timestamp, return as transferred_from_timestamp |
| 1467 |
- 5 fields of biblio or deletedbiblio tables : title, author, seriestitle, serial, copyrightdate |
| 1468 |
- 9 fields of biblioitems or deletedbiblioitems tables : publicationyear, publishercode, |
| 1469 |
editionstatement, issn, isbn, ean, itemtype, volume, number |
| 1470 |
If the record still exists, the biblionumber key contains the biblionumber |
| 1471 |
If the record have been deleted, the biblionumber key is blank, |
| 1472 |
and the deletedbiblionumber key contains the biblionumber of the deleted record. |
| 1400 |
|
1473 |
|
| 1401 |
=cut |
1474 |
=cut |
| 1402 |
|
1475 |
|
|
Lines 1408-1415
sub GetCancelledOrders {
Link Here
|
| 1408 |
my $dbh = C4::Context->dbh; |
1481 |
my $dbh = C4::Context->dbh; |
| 1409 |
my $query = " |
1482 |
my $query = " |
| 1410 |
SELECT |
1483 |
SELECT |
| 1411 |
biblio.*, |
1484 |
COALESCE(biblio.title, deletedbiblio.title) AS title, |
| 1412 |
biblioitems.*, |
1485 |
COALESCE(biblio.author, deletedbiblio.author) AS author, |
|
|
1486 |
COALESCE(biblio.seriestitle, deletedbiblio.seriestitle) AS seriestitle, |
| 1487 |
COALESCE(biblio.serial, deletedbiblio.serial) AS serial, |
| 1488 |
COALESCE(biblio.copyrightdate, deletedbiblio.copyrightdate) AS copyrightdate, |
| 1489 |
COALESCE(biblioitems.publicationyear, deletedbiblioitems.publicationyear) AS publicationyear, |
| 1490 |
COALESCE(biblioitems.publishercode, deletedbiblioitems.publishercode) AS publishercode, |
| 1491 |
COALESCE(biblioitems.editionstatement, deletedbiblioitems.editionstatement) AS editionstatement, |
| 1492 |
COALESCE(biblioitems.isbn, deletedbiblioitems.isbn) AS isbn, |
| 1493 |
COALESCE(biblioitems.issn, deletedbiblioitems.issn) AS issn, |
| 1494 |
COALESCE(biblioitems.ean, deletedbiblioitems.ean) AS ean, |
| 1495 |
COALESCE(biblioitems.itemtype, deletedbiblioitems.itemtype) AS itemtype, |
| 1496 |
COALESCE(biblioitems.volume, deletedbiblioitems.volume) AS volume, |
| 1497 |
COALESCE(biblioitems.number, deletedbiblioitems.number) AS number, |
| 1413 |
aqorders.*, |
1498 |
aqorders.*, |
| 1414 |
aqbudgets.*, |
1499 |
aqbudgets.*, |
| 1415 |
aqorders_transfers.ordernumber_to AS transferred_to, |
1500 |
aqorders_transfers.ordernumber_to AS transferred_to, |
|
Lines 1418-1423
sub GetCancelledOrders {
Link Here
|
| 1418 |
LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id |
1503 |
LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id |
| 1419 |
LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber |
1504 |
LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber |
| 1420 |
LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber |
1505 |
LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber |
|
|
1506 |
LEFT JOIN deletedbiblio ON deletedbiblio.biblionumber=aqorders.deletedbiblionumber |
| 1507 |
LEFT JOIN deletedbiblioitems ON deletedbiblioitems.biblionumber=deletedbiblio.biblionumber |
| 1421 |
LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_from = aqorders.ordernumber |
1508 |
LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_from = aqorders.ordernumber |
| 1422 |
WHERE basketno = ? |
1509 |
WHERE basketno = ? |
| 1423 |
AND (datecancellationprinted IS NOT NULL |
1510 |
AND (datecancellationprinted IS NOT NULL |
|
Lines 1707-1713
C<$ordered> Finds orders to receive only (status 'ordered' or 'partial').
Link Here
|
| 1707 |
|
1794 |
|
| 1708 |
|
1795 |
|
| 1709 |
C<@results> is an array of references-to-hash with the keys are fields |
1796 |
C<@results> is an array of references-to-hash with the keys are fields |
| 1710 |
from aqorders, biblio, biblioitems and aqbasket tables. |
1797 |
from aqorders, biblio, deletedbiblio, biblioitems, deletedbiblioitems, |
|
|
1798 |
aqbasket and aqbasketgroups tables. |
| 1799 |
If the record still exists, the biblionumber key contains the biblionumber |
| 1800 |
If the record have been deleted, the biblionumber key is blank, |
| 1801 |
and the deletedbiblionumber key contains the biblionumber of the deleted record. |
| 1711 |
|
1802 |
|
| 1712 |
=cut |
1803 |
=cut |
| 1713 |
|
1804 |
|
|
Lines 1730-1738
sub SearchOrders {
Link Here
|
| 1730 |
SELECT aqbasket.basketno, |
1821 |
SELECT aqbasket.basketno, |
| 1731 |
borrowers.surname, |
1822 |
borrowers.surname, |
| 1732 |
borrowers.firstname, |
1823 |
borrowers.firstname, |
| 1733 |
biblio.*, |
1824 |
COALESCE(biblio.title, deletedbiblio.title) AS title, |
| 1734 |
biblioitems.isbn, |
1825 |
COALESCE(biblio.author, deletedbiblio.author) AS author, |
| 1735 |
biblioitems.biblioitemnumber, |
1826 |
COALESCE(biblio.seriestitle, deletedbiblio.seriestitle) AS seriestitle, |
|
|
1827 |
COALESCE(biblio.serial, deletedbiblio.serial) AS serial, |
| 1828 |
COALESCE(biblio.copyrightdate, deletedbiblio.copyrightdate) AS copyrightdate, |
| 1829 |
COALESCE(biblioitems.publicationyear, deletedbiblioitems.publicationyear) AS publicationyear, |
| 1830 |
COALESCE(biblioitems.publishercode, deletedbiblioitems.publishercode) AS publishercode, |
| 1831 |
COALESCE(biblioitems.editionstatement, deletedbiblioitems.editionstatement) AS editionstatement, |
| 1832 |
COALESCE(biblioitems.isbn, deletedbiblioitems.isbn) AS isbn, |
| 1833 |
COALESCE(biblioitems.issn, deletedbiblioitems.issn) AS issn, |
| 1834 |
COALESCE(biblioitems.ean, deletedbiblioitems.ean) AS ean, |
| 1835 |
COALESCE(biblioitems.itemtype, deletedbiblioitems.itemtype) AS itemtype, |
| 1836 |
COALESCE(biblioitems.volume, deletedbiblioitems.volume) AS volume, |
| 1837 |
COALESCE(biblioitems.number, deletedbiblioitems.number) AS number, |
| 1736 |
aqbasket.authorisedby, |
1838 |
aqbasket.authorisedby, |
| 1737 |
aqbasket.booksellerid, |
1839 |
aqbasket.booksellerid, |
| 1738 |
aqbasket.closedate, |
1840 |
aqbasket.closedate, |
|
Lines 1747-1752
sub SearchOrders {
Link Here
|
| 1747 |
LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber |
1849 |
LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber |
| 1748 |
LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber |
1850 |
LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber |
| 1749 |
LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber |
1851 |
LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber |
|
|
1852 |
LEFT JOIN deletedbiblio ON aqorders.deletedbiblionumber=deletedbiblio.biblionumber |
| 1853 |
LEFT JOIN deletedbiblioitems ON deletedbiblioitems.biblionumber=deletedbiblio.biblionumber |
| 1750 |
}; |
1854 |
}; |
| 1751 |
|
1855 |
|
| 1752 |
# If we search on ordernumber, we retrieve the transfered order if a transfer has been done. |
1856 |
# If we search on ordernumber, we retrieve the transfered order if a transfer has been done. |
|
Lines 1783-1793
sub SearchOrders {
Link Here
|
| 1783 |
push @args, ( $ordernumber, $ordernumber ); |
1887 |
push @args, ( $ordernumber, $ordernumber ); |
| 1784 |
} |
1888 |
} |
| 1785 |
if( $search ) { |
1889 |
if( $search ) { |
| 1786 |
$query .= ' AND (biblio.title LIKE ? OR biblio.author LIKE ? OR biblioitems.isbn LIKE ?)'; |
1890 |
$query .= ' AND (COALESCE (biblio.title, deletedbiblio.title) LIKE ? OR COALESCE (biblio.author, deletedbiblio.author) LIKE ? OR COALESCE (biblioitems.isbn, deletedbiblioitems.isbn) LIKE ?)'; |
| 1787 |
push @args, ("%$search%","%$search%","%$search%"); |
1891 |
push @args, ("%$search%","%$search%","%$search%"); |
| 1788 |
} |
1892 |
} |
| 1789 |
if ( $ean ) { |
1893 |
if ( $ean ) { |
| 1790 |
$query .= ' AND biblioitems.ean = ?'; |
1894 |
$query .= ' AND (COALESCE biblioitems.ean,deletedbiblioitems.ean) = ?'; |
| 1791 |
push @args, $ean; |
1895 |
push @args, $ean; |
| 1792 |
} |
1896 |
} |
| 1793 |
if ( $booksellerid ) { |
1897 |
if ( $booksellerid ) { |
|
Lines 1921-1927
Looks up all of the received items from the supplier with the given
Link Here
|
| 1921 |
bookseller ID at the given date, for the given code (bookseller Invoice number). Ignores cancelled and completed orders. |
2025 |
bookseller ID at the given date, for the given code (bookseller Invoice number). Ignores cancelled and completed orders. |
| 1922 |
|
2026 |
|
| 1923 |
C<@results> is an array of references-to-hash. The keys of each element are fields from |
2027 |
C<@results> is an array of references-to-hash. The keys of each element are fields from |
| 1924 |
the aqorders, biblio, and biblioitems tables of the Koha database. |
2028 |
the aqorders, aqbasket, aqinvoices, borrowers, biblio, and deletedbiblio tables of the Koha database. |
| 1925 |
|
2029 |
|
| 1926 |
C<@results> is sorted alphabetically by book title. |
2030 |
C<@results> is sorted alphabetically by book title. |
| 1927 |
|
2031 |
|
|
Lines 1950-1960
sub GetParcel {
Link Here
|
| 1950 |
aqorders.rrp, |
2054 |
aqorders.rrp, |
| 1951 |
aqorders.ecost, |
2055 |
aqorders.ecost, |
| 1952 |
aqorders.gstrate, |
2056 |
aqorders.gstrate, |
| 1953 |
biblio.title |
2057 |
COALESCE (biblio.title, deletedbiblio.title) AS title |
| 1954 |
FROM aqorders |
2058 |
FROM aqorders |
| 1955 |
LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno |
2059 |
LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno |
| 1956 |
LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber |
2060 |
LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber |
| 1957 |
LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber |
2061 |
LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber |
|
|
2062 |
LEFT JOIN deletedbiblio ON aqorders.deletedbiblionumber=deletedbiblio.biblionumber |
| 1958 |
LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid |
2063 |
LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid |
| 1959 |
WHERE |
2064 |
WHERE |
| 1960 |
aqbasket.booksellerid = ? |
2065 |
aqbasket.booksellerid = ? |
|
Lines 2070-2082
sub GetParcels {
Link Here
|
| 2070 |
|
2175 |
|
| 2071 |
=head3 GetLateOrders |
2176 |
=head3 GetLateOrders |
| 2072 |
|
2177 |
|
| 2073 |
@results = &GetLateOrders; |
2178 |
@results = &GetLateOrders ($delay, $supplierid, $branch, $estimateddeliverydatefrom, $estimateddeliverydateto) ; |
| 2074 |
|
2179 |
|
| 2075 |
Searches for bookseller with late orders. |
2180 |
Looks up the orders to receive (non cancelled and non completely received) from a given supplier. |
| 2076 |
|
2181 |
|
| 2077 |
return: |
2182 |
return: |
| 2078 |
the table of supplier with late issues. This table is full of hashref. |
2183 |
the table of supplier with late issues. This table is full of hashref. |
| 2079 |
|
2184 |
|
|
|
2185 |
Looks up the cancelled orders with the given basketnumber. |
| 2186 |
Optional parameters are used to limit the results : |
| 2187 |
- C<$delay> : the number of days after the closing of the basket after which an order is late |
| 2188 |
- C<$supplierid> : the id of the vendor this function has to get orders |
| 2189 |
- C<$branch> : the branchcode of the branch this function has to get orders |
| 2190 |
- C<$estimateddeliverydatefrom> : the oldest expected date of delivery (based on aqbooksellers.deliverytime) |
| 2191 |
- C<$estimateddeliverydateto> : the most recent expected date of delivery (based on aqbooksellers.deliverytime). If only |
| 2192 |
C<$estimateddeliverydatefrom> is provided and not C<$estimateddeliverydatefrom>, the current day is used as most recent |
| 2193 |
expected date of delivery. |
| 2194 |
|
| 2195 |
Return : |
| 2196 |
The returned C<@results> is an array of references-to-hash, whose keys are: |
| 2197 |
- 9 fields from the aqorders table : ordernumber, biblionumber, deletedbiblionumber, claims_count, claims_date, |
| 2198 |
closedate as orderdate, rrp as unitpricesupplier, ecost in unitpricelib, budget_name as budget |
| 2199 |
- 1 field from the aqbasket table : basketno |
| 2200 |
- 1 field from the borrowers table : branchcode as branch |
| 2201 |
- 2 fields from aqbooksellers : name as supplier, id as supplierid |
| 2202 |
- 2 fields of biblio or deletedbiblio tables : title, author |
| 2203 |
- 2 fields of biblioitems or deletedbiblioitems tables : publishercode as publisher, publicationyear |
| 2204 |
- estimateddeliverydate : ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) |
| 2205 |
- quantity ; aqorders.quantity - COALESCE(aqorders.quantityreceived,0) |
| 2206 |
- subtotal : (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal |
| 2207 |
- latesince : DATEDIFF(CAST(now() AS date),closedate) AS latesince |
| 2208 |
If the record still exists, the biblionumber key contains the biblionumber |
| 2209 |
If the record have been deleted, the biblionumber key is blank, |
| 2210 |
and the deletedbiblionumber key contains the biblionumber of the deleted record. |
| 2211 |
|
| 2212 |
|
| 2080 |
=cut |
2213 |
=cut |
| 2081 |
|
2214 |
|
| 2082 |
sub GetLateOrders { |
2215 |
sub GetLateOrders { |
|
Lines 2095-2100
sub GetLateOrders {
Link Here
|
| 2095 |
my $select = " |
2228 |
my $select = " |
| 2096 |
SELECT aqbasket.basketno, |
2229 |
SELECT aqbasket.basketno, |
| 2097 |
aqorders.ordernumber, |
2230 |
aqorders.ordernumber, |
|
|
2231 |
aqorders.biblionumber, |
| 2232 |
aqorders.deletedbiblionumber, |
| 2098 |
DATE(aqbasket.closedate) AS orderdate, |
2233 |
DATE(aqbasket.closedate) AS orderdate, |
| 2099 |
aqbasket.basketname AS basketname, |
2234 |
aqbasket.basketname AS basketname, |
| 2100 |
aqbasket.basketgroupid AS basketgroupid, |
2235 |
aqbasket.basketgroupid AS basketgroupid, |
|
Lines 2107-2121
sub GetLateOrders {
Link Here
|
| 2107 |
borrowers.branchcode AS branch, |
2242 |
borrowers.branchcode AS branch, |
| 2108 |
aqbooksellers.name AS supplier, |
2243 |
aqbooksellers.name AS supplier, |
| 2109 |
aqbooksellers.id AS supplierid, |
2244 |
aqbooksellers.id AS supplierid, |
| 2110 |
biblio.author, biblio.title, |
2245 |
COALESCE (biblio.author, deletedbiblio.author) AS author, |
| 2111 |
biblioitems.publishercode AS publisher, |
2246 |
COALESCE (biblio.title, deletedbiblio.title) AS title, |
| 2112 |
biblioitems.publicationyear, |
2247 |
COALESCE (biblioitems.publishercode, deletedbiblioitems.publishercode) AS publisher, |
|
|
2248 |
COALESCE (biblioitems.publicationyear, deletedbiblioitems.publicationyear) AS publicationyear, |
| 2113 |
ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate, |
2249 |
ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate, |
| 2114 |
"; |
2250 |
"; |
| 2115 |
my $from = " |
2251 |
my $from = " |
| 2116 |
FROM |
2252 |
FROM |
| 2117 |
aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber |
2253 |
aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber |
| 2118 |
LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber |
2254 |
LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber |
|
|
2255 |
LEFT JOIN deletedbiblio ON deletedbiblio.biblionumber = aqorders.deletedbiblionumber |
| 2256 |
LEFT JOIN deletedbiblioitems ON deletedbiblioitems.biblionumber = deletedbiblio.biblionumber |
| 2119 |
LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id, |
2257 |
LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id, |
| 2120 |
aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber |
2258 |
aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber |
| 2121 |
LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id |
2259 |
LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id |
|
Lines 2243-2248
returns:
Link Here
|
| 2243 |
$total_qty is the sum of all of the quantities in $order_loop |
2381 |
$total_qty is the sum of all of the quantities in $order_loop |
| 2244 |
$total_price is the cost of each in $order_loop times the quantity |
2382 |
$total_price is the cost of each in $order_loop times the quantity |
| 2245 |
$total_qtyreceived is the sum of all of the quantityreceived entries in $order_loop |
2383 |
$total_qtyreceived is the sum of all of the quantityreceived entries in $order_loop |
|
|
2384 |
If the record still exists, the biblionumber key contains the biblionumber |
| 2385 |
If the record have been deleted, the biblionumber key is blank, |
| 2386 |
and the deletedbiblionumber key contains the biblionumber of the deleted record. |
| 2246 |
|
2387 |
|
| 2247 |
=cut |
2388 |
=cut |
| 2248 |
|
2389 |
|
|
Lines 2279-2284
sub GetHistory {
Link Here
|
| 2279 |
COALESCE(biblio.author, deletedbiblio.author) AS author, |
2420 |
COALESCE(biblio.author, deletedbiblio.author) AS author, |
| 2280 |
COALESCE(biblioitems.isbn, deletedbiblioitems.isbn) AS isbn, |
2421 |
COALESCE(biblioitems.isbn, deletedbiblioitems.isbn) AS isbn, |
| 2281 |
COALESCE(biblioitems.ean, deletedbiblioitems.ean) AS ean, |
2422 |
COALESCE(biblioitems.ean, deletedbiblioitems.ean) AS ean, |
|
|
2423 |
aqorders.deletedbiblionumber, |
| 2282 |
aqorders.basketno, |
2424 |
aqorders.basketno, |
| 2283 |
aqbasket.basketname, |
2425 |
aqbasket.basketname, |
| 2284 |
aqbasket.basketgroupid, |
2426 |
aqbasket.basketgroupid, |
|
Lines 2308-2315
sub GetHistory {
Link Here
|
| 2308 |
LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber |
2450 |
LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber |
| 2309 |
LEFT JOIN aqbudgets ON aqorders.budget_id=aqbudgets.budget_id |
2451 |
LEFT JOIN aqbudgets ON aqorders.budget_id=aqbudgets.budget_id |
| 2310 |
LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid |
2452 |
LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid |
| 2311 |
LEFT JOIN deletedbiblio ON deletedbiblio.biblionumber=aqorders.biblionumber |
2453 |
LEFT JOIN deletedbiblio ON deletedbiblio.biblionumber=aqorders.deletedbiblionumber |
| 2312 |
LEFT JOIN deletedbiblioitems ON deletedbiblioitems.biblionumber=aqorders.biblionumber |
2454 |
LEFT JOIN deletedbiblioitems ON deletedbiblioitems.biblionumber=deletedbiblio.biblionumber |
| 2313 |
"; |
2455 |
"; |
| 2314 |
|
2456 |
|
| 2315 |
if ( C4::Context->preference("IndependentBranches") ) { |
2457 |
if ( C4::Context->preference("IndependentBranches") ) { |
|
Lines 2330-2351
sub GetHistory {
Link Here
|
| 2330 |
} |
2472 |
} |
| 2331 |
|
2473 |
|
| 2332 |
if ( $title ) { |
2474 |
if ( $title ) { |
| 2333 |
$query .= " AND biblio.title LIKE ? "; |
2475 |
$query .= " AND COALESCE (biblio.title,deletedbiblio.title) LIKE ? "; |
| 2334 |
$title =~ s/\s+/%/g; |
2476 |
$title =~ s/\s+/%/g; |
| 2335 |
push @query_params, "%$title%"; |
2477 |
push @query_params, "%$title%"; |
| 2336 |
} |
2478 |
} |
| 2337 |
|
2479 |
|
| 2338 |
if ( $author ) { |
2480 |
if ( $author ) { |
| 2339 |
$query .= " AND biblio.author LIKE ? "; |
2481 |
$query .= " AND COALESCE (biblio.author,deletedbiblio.author) LIKE ? "; |
| 2340 |
push @query_params, "%$author%"; |
2482 |
push @query_params, "%$author%"; |
| 2341 |
} |
2483 |
} |
| 2342 |
|
2484 |
|
| 2343 |
if ( $isbn ) { |
2485 |
if ( $isbn ) { |
| 2344 |
$query .= " AND biblioitems.isbn LIKE ? "; |
2486 |
$query .= " AND COALESCE (biblioitems.isbn,deletedbiblioitems.isbn) LIKE ? "; |
| 2345 |
push @query_params, "%$isbn%"; |
2487 |
push @query_params, "%$isbn%"; |
| 2346 |
} |
2488 |
} |
| 2347 |
if ( $ean ) { |
2489 |
if ( $ean ) { |
| 2348 |
$query .= " AND biblioitems.ean = ? "; |
2490 |
$query .= " AND OALESCE (biblioitems.ean,deletedbiblioitems.ean) = ? "; |
| 2349 |
push @query_params, "$ean"; |
2491 |
push @query_params, "$ean"; |
| 2350 |
} |
2492 |
} |
| 2351 |
if ( $name ) { |
2493 |
if ( $name ) { |