|
Lines 85-113
BEGIN {
Link Here
|
| 85 |
} |
85 |
} |
| 86 |
|
86 |
|
| 87 |
|
87 |
|
| 88 |
|
|
|
| 89 |
|
| 90 |
|
| 91 |
sub GetOrderFromItemnumber { |
| 92 |
my ($itemnumber) = @_; |
| 93 |
my $dbh = C4::Context->dbh; |
| 94 |
my $query = qq| |
| 95 |
|
| 96 |
SELECT * from aqorders LEFT JOIN aqorders_items |
| 97 |
ON ( aqorders.ordernumber = aqorders_items.ordernumber ) |
| 98 |
WHERE itemnumber = ? |; |
| 99 |
|
| 100 |
my $sth = $dbh->prepare($query); |
| 101 |
|
| 102 |
# $sth->trace(3); |
| 103 |
|
| 104 |
$sth->execute($itemnumber); |
| 105 |
|
| 106 |
my $order = $sth->fetchrow_hashref; |
| 107 |
return ( $order ); |
| 108 |
|
| 109 |
} |
| 110 |
|
| 111 |
# Returns the itemnumber(s) associated with the ordernumber given in parameter |
88 |
# Returns the itemnumber(s) associated with the ordernumber given in parameter |
| 112 |
sub GetItemnumbersFromOrder { |
89 |
sub GetItemnumbersFromOrder { |
| 113 |
my ($ordernumber) = @_; |
90 |
my ($ordernumber) = @_; |
|
Lines 127-135
sub GetItemnumbersFromOrder {
Link Here
|
| 127 |
|
104 |
|
| 128 |
|
105 |
|
| 129 |
|
106 |
|
| 130 |
|
|
|
| 131 |
|
| 132 |
|
| 133 |
=head1 NAME |
107 |
=head1 NAME |
| 134 |
|
108 |
|
| 135 |
C4::Acquisition - Koha functions for dealing with orders and acquisitions |
109 |
C4::Acquisition - Koha functions for dealing with orders and acquisitions |
|
Lines 1017-1022
sub GetBasketgroups {
Link Here
|
| 1017 |
|
991 |
|
| 1018 |
=head2 FUNCTIONS ABOUT ORDERS |
992 |
=head2 FUNCTIONS ABOUT ORDERS |
| 1019 |
|
993 |
|
|
|
994 |
=head3 _get_orders_with_params |
| 995 |
|
| 996 |
$results = _get_orders_with_params ([$params]); |
| 997 |
|
| 998 |
Non exported |
| 999 |
$params is an optional hashref |
| 1000 |
if no $params, $results return a hashref describing all the orders, cancelled or not |
| 1001 |
$param can contain : |
| 1002 |
- $ordernumber |
| 1003 |
- $biblionumber |
| 1004 |
- $basket_id |
| 1005 |
- $basketgroup_id |
| 1006 |
- $supplier_id |
| 1007 |
- \@status_list : a list of status to filter on (active, cancelled) |
| 1008 |
- $orderby : a key for ordering results |
| 1009 |
|
| 1010 |
=cut |
| 1011 |
|
| 1012 |
sub _get_orders_with_params { |
| 1013 |
my ($params) =@_; |
| 1014 |
my $dbh = C4::Context->dbh; |
| 1015 |
my $query =qq{ |
| 1016 |
SELECT aqorders.*, |
| 1017 |
biblio.title, |
| 1018 |
biblio.author, |
| 1019 |
biblio.unititle, |
| 1020 |
biblio.notes AS biblio_notes, |
| 1021 |
biblio.serial, |
| 1022 |
biblio.seriestitle, |
| 1023 |
biblio.copyrightdate, |
| 1024 |
biblio.abstract, |
| 1025 |
biblioitems.itemtype, |
| 1026 |
biblioitems.publicationyear, |
| 1027 |
biblioitems.editionstatement, |
| 1028 |
biblioitems.publishercode, |
| 1029 |
biblioitems.isbn, |
| 1030 |
biblioitems.ean, |
| 1031 |
biblioitems.marcxml, |
| 1032 |
aqbasket.basketname AS basket_name, |
| 1033 |
aqbasket.basketno AS basket_id, |
| 1034 |
aqbasket.deliveryplace AS basket_deliveryplace, |
| 1035 |
aqbasket.billingplace AS basket_billingplace, |
| 1036 |
aqbasket.creationdate AS basket_creationdate, |
| 1037 |
aqbasket.closedate AS basket_closedate, |
| 1038 |
aqbasketgroups.id AS basketgroup_id, |
| 1039 |
aqbasketgroups.name AS basketgroup_name, |
| 1040 |
aqbasketgroups.closed AS basketgroup_closed, |
| 1041 |
aqbasketgroups.deliveryplace AS basketgroup_deliveryplace, |
| 1042 |
aqbasketgroups.freedeliveryplace AS basketgroup_freedeliveryplace, |
| 1043 |
aqbasketgroups.billingplace AS basketgroup_billingplace, |
| 1044 |
borrowers.branchcode AS borrower_branchcode, |
| 1045 |
aqbudgets.budget_name, |
| 1046 |
aqbooksellers.name AS supplier_name, |
| 1047 |
aqbooksellers.id AS supplier_id, |
| 1048 |
aqorders_transfers.ordernumber_from AS transferred_from, |
| 1049 |
aqorders_transfers.timestamp AS transferred_from_timestamp, |
| 1050 |
ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate, |
| 1051 |
DATE(aqbasket.closedate) AS orderdate, |
| 1052 |
aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity_to_receive, |
| 1053 |
(aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS rrp_subtotal_to_receive, |
| 1054 |
(aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.ecost AS ecost_subtotal_to_receive, |
| 1055 |
DATEDIFF(CURDATE( ),aqbasket.closedate) AS latesince |
| 1056 |
FROM aqorders |
| 1057 |
LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber |
| 1058 |
LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber |
| 1059 |
LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_to = aqorders.ordernumber |
| 1060 |
LEFT JOIN aqorders_items ON aqorders.ordernumber = aqorders_items.ordernumber |
| 1061 |
LEFT JOIN aqbasket ON aqbasket.basketno = aqorders.basketno |
| 1062 |
LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id |
| 1063 |
LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id |
| 1064 |
LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id |
| 1065 |
LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber |
| 1066 |
}; |
| 1067 |
my @list_for_where; |
| 1068 |
push @list_for_where, "aqorders.ordernumber=".$params->{ordernumber} if defined $params->{ordernumber}; |
| 1069 |
push @list_for_where, "aqorders.biblionumber".$params->{biblionumber} if defined $params->{biblionumber}; |
| 1070 |
push @list_for_where, "aqorders.basketno=".$params->{basket_id} if defined $params->{basket_id}; |
| 1071 |
push @list_for_where, "aqbasket.basketgroupid=".$params->{basketgroup_id} if defined $params->{basketgroup_id}; |
| 1072 |
push @list_for_where, "aqorders_items.itemnumber=".$params->{itemnumber} if defined $params->{itemnumber}; |
| 1073 |
push @list_for_where, "aqbasket.booksellerid=".$params->{supplier_id} if defined $params->{supplier_id}; |
| 1074 |
push @list_for_where, "aqbasket.basketname LIKE".$params->{basket_name} if defined $params->{basket_name}; |
| 1075 |
push @list_for_where, "aqbasketgroups.name LIKE".$params->{basketgroup_name} if defined $params->{basketgroup_name}; |
| 1076 |
push @list_for_where, "aqbasket.authorisedby=".$params->{borrowernumber} if defined $params->{borrowernumber}; |
| 1077 |
push @list_for_where, "biblioitems.ean LIKE ".$params->{ean} if defined $params->{ean}; |
| 1078 |
push @list_for_where, "biblioitems.isbn LIKE ".$params->{isbn} if defined $params->{isbn}; |
| 1079 |
push @list_for_where, "biblio.title LIKE ".$params->{title} if defined $params->{title}; |
| 1080 |
push @list_for_where, "biblio.author LIKE ".$params->{author} if defined $params->{author}; |
| 1081 |
push @list_for_where, "(biblio.title LIKE ".$params->{multisearch}." OR biblio.author LIKE ".$params->{multisearch}." OR biblioitems.isbn LIKE ".$params->{multisearch}.")" if defined $params->{multisearch}; |
| 1082 |
push @list_for_where, "(borrowers.branchcode = ".$params->{borrower_branchcode}." OR borrowers.branchcode = '')" if defined $params->{borrower_branchcode}; |
| 1083 |
push @list_for_where, "aqorders.orderstatus IN (\"".(join "\",\"",@{$params->{include_status}})."\")" if defined $params->{include_status}; |
| 1084 |
push @list_for_where, "aqorders.orderstatus NOT IN (\"".(join "\",\"",@{$params->{exclude_status}})."\")" if defined $params->{exclude_status}; |
| 1085 |
push @list_for_where, "(quantity > quantityreceived OR quantityreceived is NULL)" if defined $params->{pending}; |
| 1086 |
$query .= "WHERE ".join " AND ",@list_for_where if (scalar @list_for_where > 0 ); |
| 1087 |
$query .= " ORDER BY ".$params->{orderby} if defined $params->{orderby}; |
| 1088 |
warn $query; |
| 1089 |
my $sth = $dbh->prepare($query); |
| 1090 |
$sth->execute; |
| 1091 |
my $results = $sth->fetchall_arrayref({}); |
| 1092 |
return $results; |
| 1093 |
} |
| 1094 |
|
| 1095 |
|
| 1096 |
sub GetOrderFromItemnumber { |
| 1097 |
my ($itemnumber) = @_; |
| 1098 |
my $param = {itemnumber=>$itemnumber}; |
| 1099 |
my $results = _get_orders_with_params ($param); |
| 1100 |
return if scalar @$results != 1; |
| 1101 |
return $$results[0]; |
| 1102 |
} |
| 1103 |
|
| 1020 |
=head3 GetOrders |
1104 |
=head3 GetOrders |
| 1021 |
|
1105 |
|
| 1022 |
@orders = &GetOrders($basketnumber, $orderby); |
1106 |
@orders = &GetOrders($basketnumber, $orderby); |
|
Lines 1034-1062
biblio, and biblioitems tables in the Koha database.
Link Here
|
| 1034 |
|
1118 |
|
| 1035 |
sub GetOrders { |
1119 |
sub GetOrders { |
| 1036 |
my ( $basketno, $orderby ) = @_; |
1120 |
my ( $basketno, $orderby ) = @_; |
| 1037 |
my $dbh = C4::Context->dbh; |
1121 |
my $param = {basket_id=>$basketno,orderby=>"biblioitems.publishercode,biblio.title",exclude_status=>['cancelled']}; |
| 1038 |
my $query =" |
1122 |
my $results = _get_orders_with_params ($param); |
| 1039 |
SELECT biblio.*,biblioitems.*, |
|
|
| 1040 |
aqorders.*, |
| 1041 |
aqbudgets.*, |
| 1042 |
biblio.title, |
| 1043 |
aqorders_transfers.ordernumber_from AS transferred_from, |
| 1044 |
aqorders_transfers.timestamp AS transferred_from_timestamp |
| 1045 |
FROM aqorders |
| 1046 |
LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id |
| 1047 |
LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber |
| 1048 |
LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber |
| 1049 |
LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_to = aqorders.ordernumber |
| 1050 |
WHERE basketno=? |
| 1051 |
AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00') |
| 1052 |
"; |
| 1053 |
|
| 1054 |
$orderby = "biblioitems.publishercode,biblio.title" unless $orderby; |
| 1055 |
$query .= " ORDER BY $orderby"; |
| 1056 |
my $sth = $dbh->prepare($query); |
| 1057 |
$sth->execute($basketno); |
| 1058 |
my $results = $sth->fetchall_arrayref({}); |
| 1059 |
$sth->finish; |
| 1060 |
return @$results; |
1123 |
return @$results; |
| 1061 |
} |
1124 |
} |
| 1062 |
|
1125 |
|
|
Lines 1076-1100
fields from the aqorders, biblio, and biblioitems tables in the Koha database.
Link Here
|
| 1076 |
|
1139 |
|
| 1077 |
sub GetOrdersByBiblionumber { |
1140 |
sub GetOrdersByBiblionumber { |
| 1078 |
my $biblionumber = shift; |
1141 |
my $biblionumber = shift; |
| 1079 |
return unless $biblionumber; |
1142 |
return unless $biblionumber; |
| 1080 |
my $dbh = C4::Context->dbh; |
1143 |
my $param = {biblionumber=>$biblionumber,exclude_status=>['cancelled']}; |
| 1081 |
my $query =" |
1144 |
my $results = _get_orders_with_params ($param); |
| 1082 |
SELECT biblio.*,biblioitems.*, |
|
|
| 1083 |
aqorders.*, |
| 1084 |
aqbudgets.* |
| 1085 |
FROM aqorders |
| 1086 |
LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id |
| 1087 |
LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber |
| 1088 |
LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber |
| 1089 |
WHERE aqorders.biblionumber=? |
| 1090 |
"; |
| 1091 |
my $sth = $dbh->prepare($query); |
| 1092 |
$sth->execute($biblionumber); |
| 1093 |
my $results = $sth->fetchall_arrayref({}); |
| 1094 |
$sth->finish; |
| 1095 |
return @$results; |
1145 |
return @$results; |
| 1096 |
} |
1146 |
} |
| 1097 |
|
1147 |
|
|
|
1148 |
|
| 1098 |
#------------------------------------------------------------# |
1149 |
#------------------------------------------------------------# |
| 1099 |
|
1150 |
|
| 1100 |
=head3 GetOrder |
1151 |
=head3 GetOrder |
|
Lines 1103-1155
sub GetOrdersByBiblionumber {
Link Here
|
| 1103 |
|
1154 |
|
| 1104 |
Looks up an order by order number. |
1155 |
Looks up an order by order number. |
| 1105 |
|
1156 |
|
| 1106 |
Returns a reference-to-hash describing the order. The keys of |
1157 |
Returns a reference-to-hash describing the order. The keys of C<$order> are : |
| 1107 |
C<$order> are fields from the biblio, biblioitems, aqorders tables of the Koha database. |
1158 |
- all the fields from the aqorder table |
|
|
1159 |
- some fields from biblio and biblioitems (biblio.notes returned as biblio_notes), |
| 1160 |
- budget : the budget name (aqbudgets.budget_name) |
| 1161 |
- supplier : the supplier's name (aqbooksellers.name) |
| 1162 |
- supplierid : the supplier's id (aqbooksellers.id) |
| 1163 |
- basketname : the basket name (aqbasket.basketname) |
| 1164 |
- borrower_branchcode : the branchcode of the borrower managing the basket |
| 1165 |
- estimateddeliverydate : ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) |
| 1166 |
- orderdate : the date of the closure of the basket DATE(aqbasket.closedate) |
| 1167 |
- quantity_to_receive |
| 1168 |
- rrp_subtotal and ecost_subtotal : the sum of prices of items yet to received (without gst management) |
| 1169 |
- latesince : DATEDIFF(CURDATE( ),closedate) |
| 1108 |
|
1170 |
|
| 1109 |
=cut |
1171 |
=cut |
| 1110 |
|
1172 |
|
| 1111 |
sub GetOrder { |
1173 |
sub GetOrder { |
| 1112 |
my ($ordernumber) = @_; |
1174 |
my ($ordernumber) = @_; |
| 1113 |
my $dbh = C4::Context->dbh; |
1175 |
my $param = {ordernumber=>$ordernumber}; |
| 1114 |
my $query = qq{SELECT |
1176 |
my $results = _get_orders_with_params ($param); |
| 1115 |
aqorders.*, |
1177 |
return if scalar @$results != 1; |
| 1116 |
biblio.title, |
1178 |
$$results [0]->{orderdate} = format_date( $$results [0]->{orderdate} ); |
| 1117 |
biblio.author, |
1179 |
return $$results[0]; |
| 1118 |
aqbasket.basketname, |
|
|
| 1119 |
borrowers.branchcode, |
| 1120 |
biblioitems.publicationyear, |
| 1121 |
biblio.copyrightdate, |
| 1122 |
biblioitems.editionstatement, |
| 1123 |
biblioitems.isbn, |
| 1124 |
biblioitems.ean, |
| 1125 |
biblio.seriestitle, |
| 1126 |
biblioitems.publishercode, |
| 1127 |
aqorders.rrp AS unitpricesupplier, |
| 1128 |
aqorders.ecost AS unitpricelib, |
| 1129 |
aqorders.claims_count AS claims_count, |
| 1130 |
aqorders.claimed_date AS claimed_date, |
| 1131 |
aqbudgets.budget_name AS budget, |
| 1132 |
aqbooksellers.name AS supplier, |
| 1133 |
aqbooksellers.id AS supplierid, |
| 1134 |
biblioitems.publishercode AS publisher, |
| 1135 |
ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate, |
| 1136 |
DATE(aqbasket.closedate) AS orderdate, |
| 1137 |
aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity_to_receive, |
| 1138 |
(aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal, |
| 1139 |
DATEDIFF(CURDATE( ),closedate) AS latesince |
| 1140 |
FROM aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber |
| 1141 |
LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber |
| 1142 |
LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id, |
| 1143 |
aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber |
| 1144 |
LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id |
| 1145 |
WHERE aqorders.basketno = aqbasket.basketno |
| 1146 |
AND ordernumber=?}; |
| 1147 |
my $sth= $dbh->prepare($query); |
| 1148 |
$sth->execute($ordernumber); |
| 1149 |
my $data = $sth->fetchrow_hashref; |
| 1150 |
$data->{orderdate} = format_date( $data->{orderdate} ); |
| 1151 |
$sth->finish; |
| 1152 |
return $data; |
| 1153 |
} |
1180 |
} |
| 1154 |
|
1181 |
|
| 1155 |
=head3 GetLastOrderNotReceivedFromSubscriptionid |
1182 |
=head3 GetLastOrderNotReceivedFromSubscriptionid |
|
Lines 1389-1423
Returns cancelled orders for a basket
Link Here
|
| 1389 |
|
1416 |
|
| 1390 |
sub GetCancelledOrders { |
1417 |
sub GetCancelledOrders { |
| 1391 |
my ( $basketno, $orderby ) = @_; |
1418 |
my ( $basketno, $orderby ) = @_; |
| 1392 |
|
|
|
| 1393 |
return () unless $basketno; |
1419 |
return () unless $basketno; |
| 1394 |
|
1420 |
my $param = {basket_id=>$basketno,orderby=>"aqorders.datecancellationprinted desc, aqorders.timestamp desc",include_status=>['cancelled']}; |
| 1395 |
my $dbh = C4::Context->dbh; |
1421 |
my $results = _get_orders_with_params ($param); |
| 1396 |
my $query = " |
|
|
| 1397 |
SELECT |
| 1398 |
biblio.*, |
| 1399 |
biblioitems.*, |
| 1400 |
aqorders.*, |
| 1401 |
aqbudgets.*, |
| 1402 |
aqorders_transfers.ordernumber_to AS transferred_to, |
| 1403 |
aqorders_transfers.timestamp AS transferred_to_timestamp |
| 1404 |
FROM aqorders |
| 1405 |
LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id |
| 1406 |
LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber |
| 1407 |
LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber |
| 1408 |
LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_from = aqorders.ordernumber |
| 1409 |
WHERE basketno = ? |
| 1410 |
AND (datecancellationprinted IS NOT NULL |
| 1411 |
AND datecancellationprinted <> '0000-00-00') |
| 1412 |
"; |
| 1413 |
|
| 1414 |
$orderby = "aqorders.datecancellationprinted desc, aqorders.timestamp desc" |
| 1415 |
unless $orderby; |
| 1416 |
$query .= " ORDER BY $orderby"; |
| 1417 |
my $sth = $dbh->prepare($query); |
| 1418 |
$sth->execute($basketno); |
| 1419 |
my $results = $sth->fetchall_arrayref( {} ); |
| 1420 |
|
| 1421 |
return @$results; |
1422 |
return @$results; |
| 1422 |
} |
1423 |
} |
| 1423 |
|
1424 |
|
|
Lines 1632-1638
sub CancelReceipt {
Link Here
|
| 1632 |
|
1633 |
|
| 1633 |
=head3 SearchOrders |
1634 |
=head3 SearchOrders |
| 1634 |
|
1635 |
|
| 1635 |
@results = &SearchOrders({ |
1636 |
\@results = &SearchOrders({ |
| 1636 |
ordernumber => $ordernumber, |
1637 |
ordernumber => $ordernumber, |
| 1637 |
search => $search, |
1638 |
search => $search, |
| 1638 |
biblionumber => $biblionumber, |
1639 |
biblionumber => $biblionumber, |
|
Lines 1648-1749
Searches for orders.
Link Here
|
| 1648 |
C<$owner> Finds order for the logged in user. |
1649 |
C<$owner> Finds order for the logged in user. |
| 1649 |
C<$pending> Finds pending orders. Ignores completed and cancelled orders. |
1650 |
C<$pending> Finds pending orders. Ignores completed and cancelled orders. |
| 1650 |
|
1651 |
|
| 1651 |
|
1652 |
C<\@results> is an hashref to an array of references-to-hash with the keys are fields |
| 1652 |
C<@results> is an array of references-to-hash with the keys are fields |
|
|
| 1653 |
from aqorders, biblio, biblioitems and aqbasket tables. |
1653 |
from aqorders, biblio, biblioitems and aqbasket tables. |
| 1654 |
|
1654 |
|
| 1655 |
=cut |
1655 |
=cut |
| 1656 |
|
1656 |
|
| 1657 |
sub SearchOrders { |
1657 |
sub SearchOrders { |
| 1658 |
my ( $params ) = @_; |
1658 |
my ( $params ) = @_; |
| 1659 |
my $ordernumber = $params->{ordernumber}; |
1659 |
my $userenv = C4::Context->userenv; |
| 1660 |
my $search = $params->{search}; |
1660 |
my $new_param = {basket_id=>$params->{basketno}, |
| 1661 |
my $ean = $params->{ean}; |
1661 |
ordernumber=>$params->{ordernumber}, |
| 1662 |
my $booksellerid = $params->{booksellerid}; |
1662 |
multisearch=>$params->{search}, |
| 1663 |
my $basketno = $params->{basketno}; |
1663 |
ean=>$params->{ean}, |
| 1664 |
my $basketname = $params->{basketname}; |
1664 |
supplier_id=> $params->{booksellerid}, |
| 1665 |
my $basketgroupname = $params->{basketgroupname}; |
1665 |
basket_id=> $params->{basketno}, |
| 1666 |
my $owner = $params->{owner}; |
1666 |
basket_name=> $params->{basketname}, |
| 1667 |
my $pending = $params->{pending}; |
1667 |
basketgroup_name=> $params->{basketgroupname}, |
| 1668 |
|
1668 |
pending=> $params->{pending}, |
| 1669 |
my $dbh = C4::Context->dbh; |
1669 |
orderby=>"aqbasket.basketno", |
| 1670 |
my @args = (); |
1670 |
exclude_status=>['cancelled']}; |
| 1671 |
my $query = q{ |
1671 |
$new_param ->{borrowernumber} = $userenv->{'number'} if defined $params->{owner}; |
| 1672 |
SELECT aqbasket.basketno, |
1672 |
$new_param ->{borrower_branchcode} = $userenv->{branch} if (defined C4::Context->preference("IndependentBranches")) and ((defined $userenv ) and ( $userenv->{flags} != 1 ) ); |
| 1673 |
borrowers.surname, |
1673 |
my $results = _get_orders_with_params ($new_param); |
| 1674 |
borrowers.firstname, |
1674 |
return $results; |
| 1675 |
biblio.*, |
|
|
| 1676 |
biblioitems.isbn, |
| 1677 |
biblioitems.biblioitemnumber, |
| 1678 |
aqbasket.closedate, |
| 1679 |
aqbasket.creationdate, |
| 1680 |
aqbasket.basketname, |
| 1681 |
aqorders.* |
| 1682 |
FROM aqorders |
| 1683 |
LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno |
| 1684 |
LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id |
| 1685 |
LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber |
| 1686 |
LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber |
| 1687 |
LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber |
| 1688 |
WHERE (datecancellationprinted is NULL) |
| 1689 |
}; |
| 1690 |
|
| 1691 |
$query .= q{ |
| 1692 |
AND (quantity > quantityreceived OR quantityreceived is NULL) |
| 1693 |
} if $pending; |
| 1694 |
|
| 1695 |
my $userenv = C4::Context->userenv; |
| 1696 |
if ( C4::Context->preference("IndependentBranches") ) { |
| 1697 |
if ( ( $userenv ) and ( $userenv->{flags} != 1 ) ) { |
| 1698 |
$query .= q{ |
| 1699 |
AND ( |
| 1700 |
borrowers.branchcode = ? |
| 1701 |
OR borrowers.branchcode = '' |
| 1702 |
) |
| 1703 |
}; |
| 1704 |
push @args, $userenv->{branch}; |
| 1705 |
} |
| 1706 |
} |
| 1707 |
|
| 1708 |
if ( $ordernumber ) { |
| 1709 |
$query .= ' AND (aqorders.ordernumber=?)'; |
| 1710 |
push @args, $ordernumber; |
| 1711 |
} |
| 1712 |
if( $search ) { |
| 1713 |
$query .= ' AND (biblio.title LIKE ? OR biblio.author LIKE ? OR biblioitems.isbn LIKE ?)'; |
| 1714 |
push @args, ("%$search%","%$search%","%$search%"); |
| 1715 |
} |
| 1716 |
if ( $ean ) { |
| 1717 |
$query .= ' AND biblioitems.ean = ?'; |
| 1718 |
push @args, $ean; |
| 1719 |
} |
| 1720 |
if ( $booksellerid ) { |
| 1721 |
$query .= 'AND aqbasket.booksellerid = ?'; |
| 1722 |
push @args, $booksellerid; |
| 1723 |
} |
| 1724 |
if( $basketno ) { |
| 1725 |
$query .= 'AND aqbasket.basketno = ?'; |
| 1726 |
push @args, $basketno; |
| 1727 |
} |
| 1728 |
if( $basketname ) { |
| 1729 |
$query .= 'AND aqbasket.basketname LIKE ?'; |
| 1730 |
push @args, "%$basketname%"; |
| 1731 |
} |
| 1732 |
if( $basketgroupname ) { |
| 1733 |
$query .= ' AND aqbasketgroups.name LIKE ?'; |
| 1734 |
push @args, "%$basketgroupname%"; |
| 1735 |
} |
| 1736 |
|
| 1737 |
if ( $owner ) { |
| 1738 |
$query .= ' AND aqbasket.authorisedby=? '; |
| 1739 |
push @args, $userenv->{'number'}; |
| 1740 |
} |
| 1741 |
|
| 1742 |
$query .= ' ORDER BY aqbasket.basketno'; |
| 1743 |
|
| 1744 |
my $sth = $dbh->prepare($query); |
| 1745 |
$sth->execute(@args); |
| 1746 |
return $sth->fetchall_arrayref({}); |
| 1747 |
} |
1675 |
} |
| 1748 |
|
1676 |
|
| 1749 |
#------------------------------------------------------------# |
1677 |
#------------------------------------------------------------# |
|
Lines 2029-2044
sub GetLateOrders {
Link Here
|
| 2029 |
SELECT aqbasket.basketno, |
1957 |
SELECT aqbasket.basketno, |
| 2030 |
aqorders.ordernumber, |
1958 |
aqorders.ordernumber, |
| 2031 |
DATE(aqbasket.closedate) AS orderdate, |
1959 |
DATE(aqbasket.closedate) AS orderdate, |
| 2032 |
aqorders.rrp AS unitpricesupplier, |
1960 |
aqorders.rrp, |
| 2033 |
aqorders.ecost AS unitpricelib, |
1961 |
aqorders.ecost, |
| 2034 |
aqorders.claims_count AS claims_count, |
1962 |
aqorders.claims_count, |
| 2035 |
aqorders.claimed_date AS claimed_date, |
1963 |
aqorders.claimed_date, |
| 2036 |
aqbudgets.budget_name AS budget, |
1964 |
aqbudgets.budget_name AS budget, |
| 2037 |
borrowers.branchcode AS branch, |
1965 |
borrowers.branchcode AS borrower_branchcode, |
| 2038 |
aqbooksellers.name AS supplier, |
1966 |
aqbooksellers.name AS supplier, |
| 2039 |
aqbooksellers.id AS supplierid, |
1967 |
aqbooksellers.id AS supplierid, |
| 2040 |
biblio.author, biblio.title, |
1968 |
biblio.author, biblio.title, |
| 2041 |
biblioitems.publishercode AS publisher, |
1969 |
biblioitems.publishercode, |
| 2042 |
biblioitems.publicationyear, |
1970 |
biblioitems.publicationyear, |
| 2043 |
ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate, |
1971 |
ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate, |
| 2044 |
"; |
1972 |
"; |
|
Lines 2060-2068
sub GetLateOrders {
Link Here
|
| 2060 |
my $having = ""; |
1988 |
my $having = ""; |
| 2061 |
if ($dbdriver eq "mysql") { |
1989 |
if ($dbdriver eq "mysql") { |
| 2062 |
$select .= " |
1990 |
$select .= " |
| 2063 |
aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity, |
1991 |
aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity, |
| 2064 |
(aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal, |
1992 |
(aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS rrp_subtotal, |
| 2065 |
DATEDIFF(CAST(now() AS date),closedate) AS latesince |
1993 |
(aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.ecost AS ecost_subtotal, |
|
|
1994 |
DATEDIFF(CAST(now() AS date),closedate) AS latesince |
| 2066 |
"; |
1995 |
"; |
| 2067 |
if ( defined $delay ) { |
1996 |
if ( defined $delay ) { |
| 2068 |
$from .= " AND (closedate <= DATE_SUB(CAST(now() AS date),INTERVAL ? DAY)) " ; |
1997 |
$from .= " AND (closedate <= DATE_SUB(CAST(now() AS date),INTERVAL ? DAY)) " ; |
|
Lines 2070-2084
sub GetLateOrders {
Link Here
|
| 2070 |
} |
1999 |
} |
| 2071 |
$having = " |
2000 |
$having = " |
| 2072 |
HAVING quantity <> 0 |
2001 |
HAVING quantity <> 0 |
| 2073 |
AND unitpricesupplier <> 0 |
2002 |
AND rrp <> 0 |
| 2074 |
AND unitpricelib <> 0 |
2003 |
AND ecost <> 0 |
| 2075 |
"; |
2004 |
"; |
| 2076 |
} else { |
2005 |
} else { |
| 2077 |
# FIXME: account for IFNULL as above |
2006 |
# FIXME: account for IFNULL as above |
| 2078 |
$select .= " |
2007 |
$select .= " |
| 2079 |
aqorders.quantity AS quantity, |
2008 |
aqorders.quantity AS quantity, |
| 2080 |
aqorders.quantity * aqorders.rrp AS subtotal, |
2009 |
aqorders.quantity * aqorders.rrp AS rrp_subtotal, |
| 2081 |
(CAST(now() AS date) - closedate) AS latesince |
2010 |
aqorders.quantity * aqorders.ecost AS ecost_subtotal, |
|
|
2011 |
(CAST(now() AS date) - closedate) AS latesince |
| 2082 |
"; |
2012 |
"; |
| 2083 |
if ( defined $delay ) { |
2013 |
if ( defined $delay ) { |
| 2084 |
$from .= " AND (closedate <= (CAST(now() AS date) -(INTERVAL ? DAY)) "; |
2014 |
$from .= " AND (closedate <= (CAST(now() AS date) -(INTERVAL ? DAY)) "; |