Lines 1036-1049
sub GetBasketgroups {
Link Here
|
1036 |
|
1036 |
|
1037 |
@orders = &GetOrders($basketnumber, $orderby); |
1037 |
@orders = &GetOrders($basketnumber, $orderby); |
1038 |
|
1038 |
|
1039 |
Looks up the pending (non-cancelled) orders with the given basket |
1039 |
Looks up the non-cancelled (pending and received) orders with the given basketnumber. |
1040 |
number. If C<$booksellerID> is non-empty, only orders from that seller |
1040 |
Parameters: |
1041 |
are returned. |
1041 |
- C<$basketnumber> : mandatory parameter, the basket number |
1042 |
|
1042 |
- C<$orderby> : optional parameter, used to sort the results (must be a valid expression |
1043 |
return : |
1043 |
using some fields of aqorders, aqbugdets, biblio, deletedbiblio, |
1044 |
C<&basket> returns a two-element array. C<@orders> is an array of |
1044 |
biblioitems, deletedbiblioitems, aqorders_transfers tables) |
1045 |
references-to-hash, whose keys are the fields from the aqorders, |
1045 |
If C<$orderby> is not provided, results are sorted according to publishercode and title |
1046 |
biblio, and biblioitems tables in the Koha database. |
1046 |
|
|
|
1047 |
Return : |
1048 |
The returned C<@orders> is an array of references-to-hash, whose keys are: |
1049 |
- the fields from the aqorders table |
1050 |
- the fields from the aqbudgets table |
1051 |
- 2 fields from aqorders_transfers table : ordernumber_from, return as transferred_from |
1052 |
and timestamp, return as transferred_from_timestamp |
1053 |
- 5 fields of biblio or deletedbiblio tables : title, author, seriestitle, serial, copyrightdate |
1054 |
- 9 fields of biblioitems or deletedbiblioitems tables : publicationyear, publishercode, |
1055 |
editionstatement, issn, isbn, ean, itemtype, volume, number |
1056 |
If the record still exists, the biblionumber key contains the biblionumber |
1057 |
If the record have been deleted, the biblionumber key is blank, |
1058 |
and the deletedbiblionumber key contains the biblionumber of the deleted record. |
1047 |
|
1059 |
|
1048 |
=cut |
1060 |
=cut |
1049 |
|
1061 |
|
Lines 1052-1058
sub GetOrders {
Link Here
|
1052 |
return () unless $basketno; |
1064 |
return () unless $basketno; |
1053 |
my $dbh = C4::Context->dbh; |
1065 |
my $dbh = C4::Context->dbh; |
1054 |
my $query =" |
1066 |
my $query =" |
1055 |
SELECT biblio.*,biblioitems.*, |
1067 |
SELECT |
|
|
1068 |
COALESCE(biblio.title, deletedbiblio.title) AS title, |
1069 |
COALESCE(biblio.author, deletedbiblio.author) AS author, |
1070 |
COALESCE(biblio.seriestitle, deletedbiblio.seriestitle) AS seriestitle, |
1071 |
COALESCE(biblio.serial, deletedbiblio.serial) AS serial, |
1072 |
COALESCE(biblio.copyrightdate, deletedbiblio.copyrightdate) AS copyrightdate, |
1073 |
COALESCE(biblioitems.publicationyear, deletedbiblioitems.publicationyear) AS publicationyear, |
1074 |
COALESCE(biblioitems.publishercode, deletedbiblioitems.publishercode) AS publishercode, |
1075 |
COALESCE(biblioitems.editionstatement, deletedbiblioitems.editionstatement) AS editionstatement, |
1076 |
COALESCE(biblioitems.isbn, deletedbiblioitems.isbn) AS isbn, |
1077 |
COALESCE(biblioitems.issn, deletedbiblioitems.issn) AS issn, |
1078 |
COALESCE(biblioitems.ean, deletedbiblioitems.ean) AS ean, |
1079 |
COALESCE(biblioitems.itemtype, deletedbiblioitems.itemtype) AS itemtype, |
1080 |
COALESCE(biblioitems.volume, deletedbiblioitems.volume) AS volume, |
1081 |
COALESCE(biblioitems.number, deletedbiblioitems.number) AS number, |
1056 |
aqorders.*, |
1082 |
aqorders.*, |
1057 |
aqbudgets.*, |
1083 |
aqbudgets.*, |
1058 |
aqorders_transfers.ordernumber_from AS transferred_from, |
1084 |
aqorders_transfers.ordernumber_from AS transferred_from, |
Lines 1061-1072
sub GetOrders {
Link Here
|
1061 |
LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id |
1087 |
LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id |
1062 |
LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber |
1088 |
LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber |
1063 |
LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber |
1089 |
LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber |
|
|
1090 |
LEFT JOIN deletedbiblio ON deletedbiblio.biblionumber=aqorders.deletedbiblionumber |
1091 |
LEFT JOIN deletedbiblioitems ON deletedbiblioitems.biblionumber=deletedbiblio.biblionumber |
1064 |
LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_to = aqorders.ordernumber |
1092 |
LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_to = aqorders.ordernumber |
1065 |
WHERE basketno=? |
1093 |
WHERE basketno=? |
1066 |
AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00') |
1094 |
AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00') |
1067 |
"; |
1095 |
"; |
1068 |
|
1096 |
|
1069 |
$orderby = "biblioitems.publishercode,biblio.title" unless $orderby; |
1097 |
$orderby = "COALESCE(biblioitems.publishercode,deletedbiblioitems.publishercode), COALESCE(biblio.title,deletedbiblio.title)" unless $orderby; |
1070 |
$query .= " ORDER BY $orderby"; |
1098 |
$query .= " ORDER BY $orderby"; |
1071 |
my $result_set = |
1099 |
my $result_set = |
1072 |
$dbh->selectall_arrayref( $query, { Slice => {} }, $basketno ); |
1100 |
$dbh->selectall_arrayref( $query, { Slice => {} }, $basketno ); |
Lines 1093-1101
sub GetOrdersByBiblionumber {
Link Here
|
1093 |
return unless $biblionumber; |
1121 |
return unless $biblionumber; |
1094 |
my $dbh = C4::Context->dbh; |
1122 |
my $dbh = C4::Context->dbh; |
1095 |
my $query =" |
1123 |
my $query =" |
1096 |
SELECT biblio.*,biblioitems.*, |
1124 |
SELECT |
1097 |
aqorders.*, |
1125 |
biblio.*, biblioitems.*, |
1098 |
aqbudgets.* |
1126 |
aqorders.*, |
|
|
1127 |
aqbudgets.* |
1099 |
FROM aqorders |
1128 |
FROM aqorders |
1100 |
LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id |
1129 |
LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id |
1101 |
LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber |
1130 |
LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber |
Lines 1116-1123
sub GetOrdersByBiblionumber {
Link Here
|
1116 |
|
1145 |
|
1117 |
Looks up an order by order number. |
1146 |
Looks up an order by order number. |
1118 |
|
1147 |
|
1119 |
Returns a reference-to-hash describing the order. The keys of |
1148 |
Returns a reference-to-hash describing the order. |
1120 |
C<$order> are fields from the biblio, biblioitems, aqorders tables of the Koha database. |
1149 |
The keys of C<$order> are |
|
|
1150 |
- all fields from the aqorders table |
1151 |
- 2 fields from aqorders table returned with alternate names : rrp as unitpricesupplier, ecost as unitpricelib |
1152 |
- 1 field from aqbasket table : basketname |
1153 |
- 1 field from borrowers table : branchcode |
1154 |
- 1 field from aqbudgets table : budget_name returned as budget |
1155 |
- 2 fields from aqbooksellers : name returned as supplier, id returned as supplierid |
1156 |
- estimateddeliverydate : ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) |
1157 |
- quantity_to_receive : aqorders.quantity - COALESCE(aqorders.quantityreceived,0) |
1158 |
- subtotal : (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp |
1159 |
- orderdate : DATE (aqbasket.closedate) |
1160 |
- latesince : DATEDIFF(CURDATE( ),closedate) |
1161 |
- 5 fields of biblio or deletedbiblio tables : title, author, seriestitle, serial, copyrightdate |
1162 |
- 9 fields of biblioitems or deletedbiblioitems tables : publicationyear, publishercode, |
1163 |
editionstatement, issn, isbn, ean, itemtype, volume, number |
1164 |
If the record still exists, the biblionumber key contains the biblionumber |
1165 |
If the record have been deleted, the biblionumber key is blank, |
1166 |
and the deletedbiblionumber key contains the biblionumber of the deleted record. |
1167 |
|
1121 |
|
1168 |
|
1122 |
=cut |
1169 |
=cut |
1123 |
|
1170 |
|
Lines 1126-1144
sub GetOrder {
Link Here
|
1126 |
return unless $ordernumber; |
1173 |
return unless $ordernumber; |
1127 |
|
1174 |
|
1128 |
my $dbh = C4::Context->dbh; |
1175 |
my $dbh = C4::Context->dbh; |
|
|
1176 |
# 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 |
1129 |
my $query = qq{SELECT |
1177 |
my $query = qq{SELECT |
|
|
1178 |
COALESCE(biblio.title, deletedbiblio.title) AS title, |
1179 |
COALESCE(biblio.author, deletedbiblio.author) AS author, |
1180 |
COALESCE(biblio.seriestitle, deletedbiblio.seriestitle) AS seriestitle, |
1181 |
COALESCE(biblio.serial, deletedbiblio.serial) AS serial, |
1182 |
COALESCE(biblio.copyrightdate, deletedbiblio.copyrightdate) AS copyrightdate, |
1183 |
COALESCE(biblioitems.publicationyear, deletedbiblioitems.publicationyear) AS publicationyear, |
1184 |
COALESCE(biblioitems.publishercode, deletedbiblioitems.publishercode) AS publishercode, |
1185 |
COALESCE(biblioitems.publishercode, deletedbiblioitems.publishercode) AS publisher, |
1186 |
COALESCE(biblioitems.editionstatement, deletedbiblioitems.editionstatement) AS editionstatement, |
1187 |
COALESCE(biblioitems.isbn, deletedbiblioitems.isbn) AS isbn, |
1188 |
COALESCE(biblioitems.issn, deletedbiblioitems.issn) AS issn, |
1189 |
COALESCE(biblioitems.ean, deletedbiblioitems.ean) AS ean, |
1190 |
COALESCE(biblioitems.itemtype, deletedbiblioitems.itemtype) AS itemtype, |
1191 |
COALESCE(biblioitems.volume, deletedbiblioitems.volume) AS volume, |
1192 |
COALESCE(biblioitems.number, deletedbiblioitems.number) AS number, |
1130 |
aqorders.*, |
1193 |
aqorders.*, |
1131 |
biblio.title, |
|
|
1132 |
biblio.author, |
1133 |
aqbasket.basketname, |
1194 |
aqbasket.basketname, |
1134 |
borrowers.branchcode, |
1195 |
borrowers.branchcode, |
1135 |
biblioitems.publicationyear, |
|
|
1136 |
biblio.copyrightdate, |
1137 |
biblioitems.editionstatement, |
1138 |
biblioitems.isbn, |
1139 |
biblioitems.ean, |
1140 |
biblio.seriestitle, |
1141 |
biblioitems.publishercode, |
1142 |
aqorders.rrp AS unitpricesupplier, |
1196 |
aqorders.rrp AS unitpricesupplier, |
1143 |
aqorders.ecost AS unitpricelib, |
1197 |
aqorders.ecost AS unitpricelib, |
1144 |
aqorders.claims_count AS claims_count, |
1198 |
aqorders.claims_count AS claims_count, |
Lines 1146-1152
sub GetOrder {
Link Here
|
1146 |
aqbudgets.budget_name AS budget, |
1200 |
aqbudgets.budget_name AS budget, |
1147 |
aqbooksellers.name AS supplier, |
1201 |
aqbooksellers.name AS supplier, |
1148 |
aqbooksellers.id AS supplierid, |
1202 |
aqbooksellers.id AS supplierid, |
1149 |
biblioitems.publishercode AS publisher, |
|
|
1150 |
ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate, |
1203 |
ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate, |
1151 |
DATE(aqbasket.closedate) AS orderdate, |
1204 |
DATE(aqbasket.closedate) AS orderdate, |
1152 |
aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity_to_receive, |
1205 |
aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity_to_receive, |
Lines 1154-1159
sub GetOrder {
Link Here
|
1154 |
DATEDIFF(CURDATE( ),closedate) AS latesince |
1207 |
DATEDIFF(CURDATE( ),closedate) AS latesince |
1155 |
FROM aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber |
1208 |
FROM aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber |
1156 |
LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber |
1209 |
LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber |
|
|
1210 |
LEFT JOIN deletedbiblio ON deletedbiblio.biblionumber=aqorders.deletedbiblionumber |
1211 |
LEFT JOIN deletedbiblioitems ON deletedbiblioitems.biblionumber=deletedbiblio.biblionumber |
1157 |
LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id, |
1212 |
LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id, |
1158 |
aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber |
1213 |
aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber |
1159 |
LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id |
1214 |
LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id |
Lines 1396-1404
sub ModItemOrder {
Link Here
|
1396 |
|
1451 |
|
1397 |
=head3 GetCancelledOrders |
1452 |
=head3 GetCancelledOrders |
1398 |
|
1453 |
|
1399 |
my @orders = GetCancelledOrders($basketno, $orderby); |
1454 |
my @orders = GetCancelledOrders($basketnumber, $orderby); |
1400 |
|
1455 |
|
1401 |
Returns cancelled orders for a basket |
1456 |
Looks up the cancelled orders with the given basketnumber. |
|
|
1457 |
Parameters: |
1458 |
- C<$basketnumber> : mandatory parameter, the basket number |
1459 |
- C<$orderby> : optional parameter, used to sort the results (must be a valid expression |
1460 |
using some fields of aqorders, aqbugdets, biblio, deletedbiblio, |
1461 |
biblioitems, deletedbiblioitems, aqorders_transfers tables) |
1462 |
If C<$orderby> is not provided, results are sorted according to publishercode and title |
1463 |
|
1464 |
Return : |
1465 |
The returned C<@orders> is an array of references-to-hash, whose keys are: |
1466 |
- the fields from the aqorders table |
1467 |
- the fields from the aqbudgets table |
1468 |
- 2 fields from aqorders_transfers table : ordernumber_from, return as transferred_from |
1469 |
and timestamp, return as transferred_from_timestamp |
1470 |
- 5 fields of biblio or deletedbiblio tables : title, author, seriestitle, serial, copyrightdate |
1471 |
- 9 fields of biblioitems or deletedbiblioitems tables : publicationyear, publishercode, |
1472 |
editionstatement, issn, isbn, ean, itemtype, volume, number |
1473 |
If the record still exists, the biblionumber key contains the biblionumber |
1474 |
If the record have been deleted, the biblionumber key is blank, |
1475 |
and the deletedbiblionumber key contains the biblionumber of the deleted record. |
1402 |
|
1476 |
|
1403 |
=cut |
1477 |
=cut |
1404 |
|
1478 |
|
Lines 1410-1417
sub GetCancelledOrders {
Link Here
|
1410 |
my $dbh = C4::Context->dbh; |
1484 |
my $dbh = C4::Context->dbh; |
1411 |
my $query = " |
1485 |
my $query = " |
1412 |
SELECT |
1486 |
SELECT |
1413 |
biblio.*, |
1487 |
COALESCE(biblio.title, deletedbiblio.title) AS title, |
1414 |
biblioitems.*, |
1488 |
COALESCE(biblio.author, deletedbiblio.author) AS author, |
|
|
1489 |
COALESCE(biblio.seriestitle, deletedbiblio.seriestitle) AS seriestitle, |
1490 |
COALESCE(biblio.serial, deletedbiblio.serial) AS serial, |
1491 |
COALESCE(biblio.copyrightdate, deletedbiblio.copyrightdate) AS copyrightdate, |
1492 |
COALESCE(biblioitems.publicationyear, deletedbiblioitems.publicationyear) AS publicationyear, |
1493 |
COALESCE(biblioitems.publishercode, deletedbiblioitems.publishercode) AS publishercode, |
1494 |
COALESCE(biblioitems.editionstatement, deletedbiblioitems.editionstatement) AS editionstatement, |
1495 |
COALESCE(biblioitems.isbn, deletedbiblioitems.isbn) AS isbn, |
1496 |
COALESCE(biblioitems.issn, deletedbiblioitems.issn) AS issn, |
1497 |
COALESCE(biblioitems.ean, deletedbiblioitems.ean) AS ean, |
1498 |
COALESCE(biblioitems.itemtype, deletedbiblioitems.itemtype) AS itemtype, |
1499 |
COALESCE(biblioitems.volume, deletedbiblioitems.volume) AS volume, |
1500 |
COALESCE(biblioitems.number, deletedbiblioitems.number) AS number, |
1415 |
aqorders.*, |
1501 |
aqorders.*, |
1416 |
aqbudgets.*, |
1502 |
aqbudgets.*, |
1417 |
aqorders_transfers.ordernumber_to AS transferred_to, |
1503 |
aqorders_transfers.ordernumber_to AS transferred_to, |
Lines 1420-1425
sub GetCancelledOrders {
Link Here
|
1420 |
LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id |
1506 |
LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id |
1421 |
LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber |
1507 |
LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber |
1422 |
LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber |
1508 |
LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber |
|
|
1509 |
LEFT JOIN deletedbiblio ON deletedbiblio.biblionumber=aqorders.deletedbiblionumber |
1510 |
LEFT JOIN deletedbiblioitems ON deletedbiblioitems.biblionumber=deletedbiblio.biblionumber |
1423 |
LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_from = aqorders.ordernumber |
1511 |
LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_from = aqorders.ordernumber |
1424 |
WHERE basketno = ? |
1512 |
WHERE basketno = ? |
1425 |
AND (datecancellationprinted IS NOT NULL |
1513 |
AND (datecancellationprinted IS NOT NULL |
Lines 1717-1723
C<$ordered> Finds orders to receive only (status 'ordered' or 'partial').
Link Here
|
1717 |
|
1805 |
|
1718 |
|
1806 |
|
1719 |
C<@results> is an array of references-to-hash with the keys are fields |
1807 |
C<@results> is an array of references-to-hash with the keys are fields |
1720 |
from aqorders, biblio, biblioitems and aqbasket tables. |
1808 |
from aqorders, biblio, deletedbiblio, biblioitems, deletedbiblioitems, |
|
|
1809 |
aqbasket and aqbasketgroups tables. |
1810 |
If the record still exists, the biblionumber key contains the biblionumber |
1811 |
If the record have been deleted, the biblionumber key is blank, |
1812 |
and the deletedbiblionumber key contains the biblionumber of the deleted record. |
1721 |
|
1813 |
|
1722 |
=cut |
1814 |
=cut |
1723 |
|
1815 |
|
Lines 1742-1750
sub SearchOrders {
Link Here
|
1742 |
SELECT aqbasket.basketno, |
1834 |
SELECT aqbasket.basketno, |
1743 |
borrowers.surname, |
1835 |
borrowers.surname, |
1744 |
borrowers.firstname, |
1836 |
borrowers.firstname, |
1745 |
biblio.*, |
1837 |
COALESCE(biblio.title, deletedbiblio.title) AS title, |
1746 |
biblioitems.isbn, |
1838 |
COALESCE(biblio.author, deletedbiblio.author) AS author, |
1747 |
biblioitems.biblioitemnumber, |
1839 |
COALESCE(biblio.seriestitle, deletedbiblio.seriestitle) AS seriestitle, |
|
|
1840 |
COALESCE(biblio.serial, deletedbiblio.serial) AS serial, |
1841 |
COALESCE(biblio.copyrightdate, deletedbiblio.copyrightdate) AS copyrightdate, |
1842 |
COALESCE(biblioitems.publicationyear, deletedbiblioitems.publicationyear) AS publicationyear, |
1843 |
COALESCE(biblioitems.publishercode, deletedbiblioitems.publishercode) AS publishercode, |
1844 |
COALESCE(biblioitems.editionstatement, deletedbiblioitems.editionstatement) AS editionstatement, |
1845 |
COALESCE(biblioitems.isbn, deletedbiblioitems.isbn) AS isbn, |
1846 |
COALESCE(biblioitems.issn, deletedbiblioitems.issn) AS issn, |
1847 |
COALESCE(biblioitems.ean, deletedbiblioitems.ean) AS ean, |
1848 |
COALESCE(biblioitems.itemtype, deletedbiblioitems.itemtype) AS itemtype, |
1849 |
COALESCE(biblioitems.volume, deletedbiblioitems.volume) AS volume, |
1850 |
COALESCE(biblioitems.number, deletedbiblioitems.number) AS number, |
1748 |
aqbasket.authorisedby, |
1851 |
aqbasket.authorisedby, |
1749 |
aqbasket.booksellerid, |
1852 |
aqbasket.booksellerid, |
1750 |
aqbasket.closedate, |
1853 |
aqbasket.closedate, |
Lines 1759-1764
sub SearchOrders {
Link Here
|
1759 |
LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber |
1862 |
LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber |
1760 |
LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber |
1863 |
LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber |
1761 |
LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber |
1864 |
LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber |
|
|
1865 |
LEFT JOIN deletedbiblio ON aqorders.deletedbiblionumber=deletedbiblio.biblionumber |
1866 |
LEFT JOIN deletedbiblioitems ON deletedbiblioitems.biblionumber=deletedbiblio.biblionumber |
1762 |
}; |
1867 |
}; |
1763 |
|
1868 |
|
1764 |
# If we search on ordernumber, we retrieve the transfered order if a transfer has been done. |
1869 |
# If we search on ordernumber, we retrieve the transfered order if a transfer has been done. |
Lines 1799-1809
sub SearchOrders {
Link Here
|
1799 |
push @args, $biblionumber; |
1904 |
push @args, $biblionumber; |
1800 |
} |
1905 |
} |
1801 |
if( $search ) { |
1906 |
if( $search ) { |
1802 |
$query .= ' AND (biblio.title LIKE ? OR biblio.author LIKE ? OR biblioitems.isbn LIKE ?)'; |
1907 |
$query .= ' AND (COALESCE (biblio.title, deletedbiblio.title) LIKE ? OR COALESCE (biblio.author, deletedbiblio.author) LIKE ? OR COALESCE (biblioitems.isbn, deletedbiblioitems.isbn) LIKE ?)'; |
1803 |
push @args, ("%$search%","%$search%","%$search%"); |
1908 |
push @args, ("%$search%","%$search%","%$search%"); |
1804 |
} |
1909 |
} |
1805 |
if ( $ean ) { |
1910 |
if ( $ean ) { |
1806 |
$query .= ' AND biblioitems.ean = ?'; |
1911 |
$query .= ' AND (COALESCE biblioitems.ean,deletedbiblioitems.ean) = ?'; |
1807 |
push @args, $ean; |
1912 |
push @args, $ean; |
1808 |
} |
1913 |
} |
1809 |
if ( $booksellerid ) { |
1914 |
if ( $booksellerid ) { |
Lines 1947-1953
Looks up all of the received items from the supplier with the given
Link Here
|
1947 |
bookseller ID at the given date, for the given code (bookseller Invoice number). Ignores cancelled and completed orders. |
2052 |
bookseller ID at the given date, for the given code (bookseller Invoice number). Ignores cancelled and completed orders. |
1948 |
|
2053 |
|
1949 |
C<@results> is an array of references-to-hash. The keys of each element are fields from |
2054 |
C<@results> is an array of references-to-hash. The keys of each element are fields from |
1950 |
the aqorders, biblio, and biblioitems tables of the Koha database. |
2055 |
the aqorders, aqbasket, aqinvoices, borrowers, biblio, and deletedbiblio tables of the Koha database. |
1951 |
|
2056 |
|
1952 |
C<@results> is sorted alphabetically by book title. |
2057 |
C<@results> is sorted alphabetically by book title. |
1953 |
|
2058 |
|
Lines 1976-1986
sub GetParcel {
Link Here
|
1976 |
aqorders.rrp, |
2081 |
aqorders.rrp, |
1977 |
aqorders.ecost, |
2082 |
aqorders.ecost, |
1978 |
aqorders.gstrate, |
2083 |
aqorders.gstrate, |
1979 |
biblio.title |
2084 |
COALESCE (biblio.title, deletedbiblio.title) AS title |
1980 |
FROM aqorders |
2085 |
FROM aqorders |
1981 |
LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno |
2086 |
LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno |
1982 |
LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber |
2087 |
LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber |
1983 |
LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber |
2088 |
LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber |
|
|
2089 |
LEFT JOIN deletedbiblio ON aqorders.deletedbiblionumber=deletedbiblio.biblionumber |
1984 |
LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid |
2090 |
LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid |
1985 |
WHERE |
2091 |
WHERE |
1986 |
aqbasket.booksellerid = ? |
2092 |
aqbasket.booksellerid = ? |
Lines 2096-2108
sub GetParcels {
Link Here
|
2096 |
|
2202 |
|
2097 |
=head3 GetLateOrders |
2203 |
=head3 GetLateOrders |
2098 |
|
2204 |
|
2099 |
@results = &GetLateOrders; |
2205 |
@results = &GetLateOrders ($delay, $supplierid, $branch, $estimateddeliverydatefrom, $estimateddeliverydateto) ; |
2100 |
|
2206 |
|
2101 |
Searches for bookseller with late orders. |
2207 |
Looks up the orders to receive (non cancelled and non completely received) from a given supplier. |
2102 |
|
2208 |
|
2103 |
return: |
2209 |
return: |
2104 |
the table of supplier with late issues. This table is full of hashref. |
2210 |
the table of supplier with late issues. This table is full of hashref. |
2105 |
|
2211 |
|
|
|
2212 |
Looks up the cancelled orders with the given basketnumber. |
2213 |
Optional parameters are used to limit the results : |
2214 |
- C<$delay> : the number of days after the closing of the basket after which an order is late |
2215 |
- C<$supplierid> : the id of the vendor this function has to get orders |
2216 |
- C<$branch> : the branchcode of the branch this function has to get orders |
2217 |
- C<$estimateddeliverydatefrom> : the oldest expected date of delivery (based on aqbooksellers.deliverytime) |
2218 |
- C<$estimateddeliverydateto> : the most recent expected date of delivery (based on aqbooksellers.deliverytime). If only |
2219 |
C<$estimateddeliverydatefrom> is provided and not C<$estimateddeliverydatefrom>, the current day is used as most recent |
2220 |
expected date of delivery. |
2221 |
|
2222 |
Return : |
2223 |
The returned C<@results> is an array of references-to-hash, whose keys are: |
2224 |
- 9 fields from the aqorders table : ordernumber, biblionumber, deletedbiblionumber, claims_count, claims_date, |
2225 |
closedate as orderdate, rrp as unitpricesupplier, ecost in unitpricelib, budget_name as budget |
2226 |
- 1 field from the aqbasket table : basketno |
2227 |
- 1 field from the borrowers table : branchcode as branch |
2228 |
- 2 fields from aqbooksellers : name as supplier, id as supplierid |
2229 |
- 2 fields of biblio or deletedbiblio tables : title, author |
2230 |
- 2 fields of biblioitems or deletedbiblioitems tables : publishercode as publisher, publicationyear |
2231 |
- estimateddeliverydate : ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) |
2232 |
- quantity ; aqorders.quantity - COALESCE(aqorders.quantityreceived,0) |
2233 |
- subtotal : (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal |
2234 |
- latesince : DATEDIFF(CAST(now() AS date),closedate) AS latesince |
2235 |
If the record still exists, the biblionumber key contains the biblionumber |
2236 |
If the record have been deleted, the biblionumber key is blank, |
2237 |
and the deletedbiblionumber key contains the biblionumber of the deleted record. |
2238 |
|
2239 |
|
2106 |
=cut |
2240 |
=cut |
2107 |
|
2241 |
|
2108 |
sub GetLateOrders { |
2242 |
sub GetLateOrders { |
Lines 2121-2126
sub GetLateOrders {
Link Here
|
2121 |
my $select = " |
2255 |
my $select = " |
2122 |
SELECT aqbasket.basketno, |
2256 |
SELECT aqbasket.basketno, |
2123 |
aqorders.ordernumber, |
2257 |
aqorders.ordernumber, |
|
|
2258 |
aqorders.biblionumber, |
2259 |
aqorders.deletedbiblionumber, |
2124 |
DATE(aqbasket.closedate) AS orderdate, |
2260 |
DATE(aqbasket.closedate) AS orderdate, |
2125 |
aqbasket.basketname AS basketname, |
2261 |
aqbasket.basketname AS basketname, |
2126 |
aqbasket.basketgroupid AS basketgroupid, |
2262 |
aqbasket.basketgroupid AS basketgroupid, |
Lines 2133-2147
sub GetLateOrders {
Link Here
|
2133 |
borrowers.branchcode AS branch, |
2269 |
borrowers.branchcode AS branch, |
2134 |
aqbooksellers.name AS supplier, |
2270 |
aqbooksellers.name AS supplier, |
2135 |
aqbooksellers.id AS supplierid, |
2271 |
aqbooksellers.id AS supplierid, |
2136 |
biblio.author, biblio.title, |
2272 |
COALESCE (biblio.author, deletedbiblio.author) AS author, |
2137 |
biblioitems.publishercode AS publisher, |
2273 |
COALESCE (biblio.title, deletedbiblio.title) AS title, |
2138 |
biblioitems.publicationyear, |
2274 |
COALESCE (biblioitems.publishercode, deletedbiblioitems.publishercode) AS publisher, |
|
|
2275 |
COALESCE (biblioitems.publicationyear, deletedbiblioitems.publicationyear) AS publicationyear, |
2139 |
ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate, |
2276 |
ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate, |
2140 |
"; |
2277 |
"; |
2141 |
my $from = " |
2278 |
my $from = " |
2142 |
FROM |
2279 |
FROM |
2143 |
aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber |
2280 |
aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber |
2144 |
LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber |
2281 |
LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber |
|
|
2282 |
LEFT JOIN deletedbiblio ON deletedbiblio.biblionumber = aqorders.deletedbiblionumber |
2283 |
LEFT JOIN deletedbiblioitems ON deletedbiblioitems.biblionumber = deletedbiblio.biblionumber |
2145 |
LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id, |
2284 |
LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id, |
2146 |
aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber |
2285 |
aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber |
2147 |
LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id |
2286 |
LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id |
Lines 2269-2274
returns:
Link Here
|
2269 |
$total_qty is the sum of all of the quantities in $order_loop |
2408 |
$total_qty is the sum of all of the quantities in $order_loop |
2270 |
$total_price is the cost of each in $order_loop times the quantity |
2409 |
$total_price is the cost of each in $order_loop times the quantity |
2271 |
$total_qtyreceived is the sum of all of the quantityreceived entries in $order_loop |
2410 |
$total_qtyreceived is the sum of all of the quantityreceived entries in $order_loop |
|
|
2411 |
If the record still exists, the biblionumber key contains the biblionumber |
2412 |
If the record have been deleted, the biblionumber key is blank, |
2413 |
and the deletedbiblionumber key contains the biblionumber of the deleted record. |
2272 |
|
2414 |
|
2273 |
=cut |
2415 |
=cut |
2274 |
|
2416 |
|
Lines 2305-2310
sub GetHistory {
Link Here
|
2305 |
COALESCE(biblio.author, deletedbiblio.author) AS author, |
2447 |
COALESCE(biblio.author, deletedbiblio.author) AS author, |
2306 |
COALESCE(biblioitems.isbn, deletedbiblioitems.isbn) AS isbn, |
2448 |
COALESCE(biblioitems.isbn, deletedbiblioitems.isbn) AS isbn, |
2307 |
COALESCE(biblioitems.ean, deletedbiblioitems.ean) AS ean, |
2449 |
COALESCE(biblioitems.ean, deletedbiblioitems.ean) AS ean, |
|
|
2450 |
aqorders.deletedbiblionumber, |
2308 |
aqorders.basketno, |
2451 |
aqorders.basketno, |
2309 |
aqbasket.basketname, |
2452 |
aqbasket.basketname, |
2310 |
aqbasket.basketgroupid, |
2453 |
aqbasket.basketgroupid, |
Lines 2334-2341
sub GetHistory {
Link Here
|
2334 |
LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber |
2477 |
LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber |
2335 |
LEFT JOIN aqbudgets ON aqorders.budget_id=aqbudgets.budget_id |
2478 |
LEFT JOIN aqbudgets ON aqorders.budget_id=aqbudgets.budget_id |
2336 |
LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid |
2479 |
LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid |
2337 |
LEFT JOIN deletedbiblio ON deletedbiblio.biblionumber=aqorders.biblionumber |
2480 |
LEFT JOIN deletedbiblio ON deletedbiblio.biblionumber=aqorders.deletedbiblionumber |
2338 |
LEFT JOIN deletedbiblioitems ON deletedbiblioitems.biblionumber=aqorders.biblionumber |
2481 |
LEFT JOIN deletedbiblioitems ON deletedbiblioitems.biblionumber=deletedbiblio.biblionumber |
2339 |
"; |
2482 |
"; |
2340 |
|
2483 |
|
2341 |
if ( C4::Context->preference("IndependentBranches") ) { |
2484 |
if ( C4::Context->preference("IndependentBranches") ) { |
Lines 2356-2377
sub GetHistory {
Link Here
|
2356 |
} |
2499 |
} |
2357 |
|
2500 |
|
2358 |
if ( $title ) { |
2501 |
if ( $title ) { |
2359 |
$query .= " AND biblio.title LIKE ? "; |
2502 |
$query .= " AND COALESCE (biblio.title,deletedbiblio.title) LIKE ? "; |
2360 |
$title =~ s/\s+/%/g; |
2503 |
$title =~ s/\s+/%/g; |
2361 |
push @query_params, "%$title%"; |
2504 |
push @query_params, "%$title%"; |
2362 |
} |
2505 |
} |
2363 |
|
2506 |
|
2364 |
if ( $author ) { |
2507 |
if ( $author ) { |
2365 |
$query .= " AND biblio.author LIKE ? "; |
2508 |
$query .= " AND COALESCE (biblio.author,deletedbiblio.author) LIKE ? "; |
2366 |
push @query_params, "%$author%"; |
2509 |
push @query_params, "%$author%"; |
2367 |
} |
2510 |
} |
2368 |
|
2511 |
|
2369 |
if ( $isbn ) { |
2512 |
if ( $isbn ) { |
2370 |
$query .= " AND biblioitems.isbn LIKE ? "; |
2513 |
$query .= " AND COALESCE (biblioitems.isbn,deletedbiblioitems.isbn) LIKE ? "; |
2371 |
push @query_params, "%$isbn%"; |
2514 |
push @query_params, "%$isbn%"; |
2372 |
} |
2515 |
} |
2373 |
if ( $ean ) { |
2516 |
if ( $ean ) { |
2374 |
$query .= " AND biblioitems.ean = ? "; |
2517 |
$query .= " AND OALESCE (biblioitems.ean,deletedbiblioitems.ean) = ? "; |
2375 |
push @query_params, "$ean"; |
2518 |
push @query_params, "$ean"; |
2376 |
} |
2519 |
} |
2377 |
if ( $name ) { |
2520 |
if ( $name ) { |