View | Details | Raw Unified | Return to bug 11206
Collapse All | Expand All

(-)a/C4/Acquisition.pm (-240 / +170 lines)
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)) ";
(-)a/acqui/lateorders-export.pl (-8 / +8 lines)
Lines 40-56 for my $ordernumber ( @ordernumbers ) { Link Here
40
            orderdate => $order->{orderdate},
40
            orderdate => $order->{orderdate},
41
            latesince => $order->{latesince},
41
            latesince => $order->{latesince},
42
            estimateddeliverydate => $order->{estimateddeliverydate},
42
            estimateddeliverydate => $order->{estimateddeliverydate},
43
            supplier => $order->{supplier},
43
            supplier_name => $order->{supplier_name},
44
            supplierid => $order->{supplierid},
44
            supplier_id => $order->{supplier_id},
45
            title => $order->{title},
45
            title => $order->{title},
46
            author => $order->{author},
46
            author => $order->{author},
47
            publisher => $order->{publisher},
47
            publishercode => $order->{publishercode},
48
            unitpricesupplier => $order->{unitpricesupplier},
48
            rrp => $order->{rrp},
49
            quantity_to_receive => $order->{quantity_to_receive},
49
            quantity_to_receive => $order->{quantity_to_receive},
50
            subtotal => $order->{subtotal},
50
            rrp_subtotal_to_receive => $order->{rrp_subtotal_to_receive},
51
            budget => $order->{budget},
51
            budget_name => $order->{budget_name},
52
            basketname => $order->{basketname},
52
            basket_name => $order->{basket_name},
53
            basketno => $order->{basketno},
53
            basket_id => $order->{basket_id},
54
            claims_count => $order->{claims_count},
54
            claims_count => $order->{claims_count},
55
            claimed_date => $order->{claimed_date},
55
            claimed_date => $order->{claimed_date},
56
        }
56
        }
(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/csv/lateorders.tt (-1 / +1 lines)
Lines 1-5 Link Here
1
[% USE KohaDates %][% INCLUDE csv_headers/acqui/lateorders.tt %]
1
[% USE KohaDates %][% INCLUDE csv_headers/acqui/lateorders.tt %]
2
[% FOREACH o IN orders %]"[% o.orderdate %] ([% o.latesince %] days)","[% o.estimateddeliverydate | $KohaDates %]","[% o.supplier (o.supplierid) %]","[% o.title %] [% IF o.author %]Author: [% o.author %].[% END %][% IF o.publisher %]Published by: [% o.publisher %].[% END %]","[% o.unitpricesupplier %] x [% o.quantity_to_receive %] = [% o.subtotal %] ([% o.budget %])","[% o.basketname %] ([% o.basketno %])","[% o.claims_count %]","[% o.claimed_date %]"
2
[% FOREACH o IN orders %]"[% o.orderdate %] ([% o.latesince %] days)","[% o.estimateddeliverydate | $KohaDates %]","[% o.supplier_name (o.supplier_id) %]","[% o.title %] [% IF o.author %]Author: [% o.author %].[% END %][% IF o.publishercode %]Published by: [% o.publishercode %].[% END %]","[% o.rrp %] x [% o.quantity_to_receive %] = [% o.rrp_subtotal_to_receive %] ([% o.budget_name %])","[% o.basket_name %] ([% o.basket_id %])","[% o.claims_count %]","[% o.claimed_date %]"
3
[% END %]
3
[% END %]
4
4
5
,,Total orders in late, [% orders.size %]
5
,,Total orders in late, [% orders.size %]
(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/lateorders.tt (-13 / +12 lines)
Lines 106-112 $(document).ready(function() { Link Here
106
    <table id="late_orders">
106
    <table id="late_orders">
107
      <thead>
107
      <thead>
108
        <tr>
108
        <tr>
109
            [% IF Supplier %]
109
            [% IF supplier_name %]
110
                <th><a id="CheckAll" href="#">Check all</a><br /><a id="CheckNone" href="#">Uncheck all</a></th>
110
                <th><a id="CheckAll" href="#">Check all</a><br /><a id="CheckNone" href="#">Uncheck all</a></th>
111
            [% ELSE %]
111
            [% ELSE %]
112
                <th></th>
112
                <th></th>
Lines 126-132 $(document).ready(function() { Link Here
126
        [% UNLESS ( loop.odd ) %]<tr class="highlight">
126
        [% UNLESS ( loop.odd ) %]<tr class="highlight">
127
        [% ELSE %]<tr>[% END %]
127
        [% ELSE %]<tr>[% END %]
128
            <td>
128
            <td>
129
                <input type="checkbox" value="[% lateorder.ordernumber %]" data-booksellerid="[% lateorder.supplierid %]" name="ordernumber">
129
                <input type="checkbox" value="[% lateorder.ordernumber %]" data-booksellerid="[% lateorder.supplier_id %]" name="ordernumber">
130
            </td>
130
            </td>
131
            <td>
131
            <td>
132
                [% lateorder.orderdate %]
132
                [% lateorder.orderdate %]
Lines 138-167 $(document).ready(function() { Link Here
138
                [% END %]
138
                [% END %]
139
            </td>
139
            </td>
140
            <td>
140
            <td>
141
                [% lateorder.supplier %]
141
                [% lateorder.supplier_name %]
142
                ([% lateorder.supplierid %])
142
                ([% lateorder.supplier_id %])
143
            </td>
143
            </td>
144
            <td>
144
            <td>
145
                <b>[% lateorder.title |html %]</b>
145
                <b>[% lateorder.title |html %]</b>
146
                   [% IF ( lateorder.author ) %]<br/><i>Author:</i> [% lateorder.author %][% END %]
146
                   [% IF ( lateorder.author ) %]<br/><i>Author:</i> [% lateorder.author %][% END %]
147
                   [% IF ( lateorder.publisher ) %]
147
                   [% IF ( lateorder.publishercode ) %]
148
                        <br/><i>Published by:</i> [% lateorder.publisher %]
148
                        <br/><i>Published by:</i> [% lateorder.publishercode %]
149
                        [% IF ( lateorder.publicationyear ) %]
149
                        [% IF ( lateorder.publicationyear ) %]
150
                            <i> in </i>[% lateorder.publicationyear %]
150
                            <i> in </i>[% lateorder.publicationyear %]
151
                        [% END %]
151
                        [% END %]
152
                   [% END %]
152
                   [% END %]
153
            </td>
153
            </td>
154
            <td>
154
            <td>
155
                   [% lateorder.unitpricesupplier %]x[% lateorder.quantity %] = 
155
                   [% lateorder.rrp %]x[% lateorder.quantity %] = 
156
                   [% lateorder.subtotal %]
156
                   [% lateorder.rrp_subtotal_to_receive %]
157
                    <p title="budget">[% lateorder.budget %]</p>
157
                    <p title="budget">[% lateorder.budget_name %]</p>
158
            </td>
158
            </td>
159
            <td>
159
            <td>
160
                 <p><a href="basket.pl?basketno=[% lateorder.basketno %]" title="basket">
160
                 <p><a href="basket.pl?basketno=[% lateorder.basket_id %]" title="basket">
161
                        [% lateorder.basketno %]
161
                        [% lateorder.basket_id %]
162
                 	</a>
162
                 	</a>
163
                 </p>
163
                 </p>
164
                 <p title="branch">[% lateorder.branch %]</p>
164
                 <p title="branch">[% lateorder.borrower_branchcode %]</p>
165
            </td>
165
            </td>
166
            <td>[% lateorder.claims_count %]</td>
166
            <td>[% lateorder.claims_count %]</td>
167
            <td>[% lateorder.claimed_date %]</td>
167
            <td>[% lateorder.claimed_date %]</td>
168
- 

Return to bug 11206