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)) "; |