Attachment #20662 for bug #10641

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




    # FIXME NOT quite sure that this operation is valid for DBMs different from Mysql, HOPING so
    # should be tested with other DBMs

    my $query;
    my @query_params = ();
    my $dbdriver = C4::Context->config("db_scheme") || "mysql";
    $query = "
        SELECT DISTINCT aqbasket.booksellerid, aqbooksellers.name
        FROM aqorders LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno
        LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id

            AND aqorders.quantity - COALESCE(aqorders.quantityreceived,0) <> 0
            AND aqbasket.closedate IS NOT NULL
    ";
    if ( defined $delay && $delay >= 0 ) {
        $query .= " AND (closedate <= DATE_SUB(CAST(now() AS date),INTERVAL ? + COALESCE(aqbooksellers.deliverytime,0) DAY)) ";
        push @query_params, $delay;
    } elsif ( $delay < 0 ){
        warn 'WARNING: GetBooksellerWithLateOrders is called with a negative value';
        return;
    }
    if ( defined $estimateddeliverydatefrom ) {
        $query .= '
            AND ADDDATE(aqbasket.closedate, INTERVAL COALESCE(aqbooksellers.deliverytime,0) DAY) >= ?';
            push @query_params, $estimateddeliverydatefrom;
            if ( defined $estimateddeliverydateto ) {
                $query .= ' AND ADDDATE(aqbasket.closedate, INTERVAL COALESCE(aqbooksellers.deliverytime, 0) DAY) <= ?';
                push @query_params, $estimateddeliverydateto;
            } else {
                    $query .= ' AND ADDDATE(aqbasket.closedate, INTERVAL COALESCE(aqbooksellers.deliverytime, 0) DAY) <= CAST(now() AS date)';
            }
    }
    if ( defined $estimateddeliverydateto ) {
        $query .= ' AND ADDDATE(aqbasket.closedate, INTERVAL COALESCE(aqbooksellers.deliverytime,0) DAY) <= ?';
        push @query_params, $estimateddeliverydateto;
    } elsif ( defined $estimateddeliverydatefrom ) {
        $strsth .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= CAST(now() AS date)';
    }

    my $sth = $dbh->prepare($query);
    $sth->execute( @query_params );
    my %supplierlist;
    while ( my ( $id, $name ) = $sth->fetchrow ) {





use Modern::Perl;

use Test::More tests => 61;
use C4::Context;
use Koha::DateUtils;
use DateTime::Duration;

%suppliers = C4::Bookseller::GetBooksellersWithLateOrders( 4, undef, undef );
isnt( exists( $suppliers{$id_supplier1} ),
    1, "Supplier1 has late orders but  today  > now() - 4 days" );
isnt( exists( $suppliers{$id_supplier2} ),
    1, "Supplier2 has late orders and $daysago5 <= now() - (4 days+2)" );
#    1, "Supplier2 has late orders and $daysago5 <= now() - (4 days+2)" );
ok( exists( $suppliers{$id_supplier3} ),
    "Supplier3 has late orders and $daysago10  <= now() - (4 days+3)" );
isnt( exists( $suppliers{$id_supplier4} ), 1, "Supplier4 hasnt late orders" );

#Case 3: With $delay = -1
is( C4::Bookseller::GetBooksellersWithLateOrders( -1, undef, undef ),
    undef, "-1 is a wrong value for a delay" );
#    undef, "-1 is a wrong value for a delay" );

#Case 4: With $delay = 0
#    today  == now-0 -LATE- (if no deliverytime or deliverytime == 0)

%suppliers =
  C4::Bookseller::GetBooksellersWithLateOrders( undef, $daysago4, undef );

ok( exists( $suppliers{$id_supplier1} ),
    "Supplier1 has late orders and $today >= $daysago4 -deliverytime undef" );
#    "Supplier1 has late orders and $today >= $daysago4 -deliverytime undef" );
ok( exists( $suppliers{$id_supplier2} ),
    "Supplier2 has late orders and $daysago5 + 2 days >= $daysago4 " );
isnt( exists( $suppliers{$id_supplier3} ),

#    quantityreceived = quantity -NOT LATE-
%suppliers =
  C4::Bookseller::GetBooksellersWithLateOrders( undef, undef, $daysago5 );
isnt( exists( $suppliers{$id_supplier1} ),
    1,
    "Supplier1 has late orders but $today >= $daysago5 - deliverytime undef" );
isnt( exists( $suppliers{$id_supplier2} ),
    1, "Supplier2 has late orders but  $daysago5 + 2 days  > $daysago5 " );
#isnt( exists( $suppliers{$id_supplier2} ),
#    1, "Supplier2 has late orders but  $daysago5 + 2 days  > $daysago5 " );
ok( exists( $suppliers{$id_supplier3} ),
    "Supplier3 has late orders and $daysago10 + 3  <= $daysago5" );
isnt( exists( $suppliers{$id_supplier4} ), 1, "Supplier4 hasnt late orders" );

  C4::Bookseller::GetBooksellersWithLateOrders( 5, undef, $daysago5 );
isnt( exists( $suppliers{$id_supplier1} ),
    1, "Supplier2 has late orders but today > $daysago5 today > now() -5" );
isnt(
    exists( $suppliers{$id_supplier2} ),
    1,
"Supplier2 has late orders but $daysago5 + 2 days > $daysago5  and $daysago5 > now() - 2+5 days"
);
#"Supplier2 has late orders but $daysago5 + 2 days > $daysago5  and $daysago5 > now() - 2+5 days"
#);
ok(
    exists( $suppliers{$id_supplier3} ),
"Supplier2 has late orders and $daysago10 + 3 days <= $daysago5 and $daysago10 <= now() - 3+5 days "

ModBasket($basket1info);
%suppliers = C4::Bookseller::GetBooksellersWithLateOrders( undef, $daysago10,
    $daysago10 );
ok( exists( $suppliers{$id_supplier1} ),
    "Supplier1 has late orders and $daysago10==$daysago10==$daysago10 " )
  ;
#  ;
isnt( exists( $suppliers{$id_supplier2} ),
    1,
    "Supplier2 has late orders but $daysago10==$daysago10<$daysago5+2" );

#Case 12: closedate == $estimateddeliverydatefrom =today-10
%suppliers =
  C4::Bookseller::GetBooksellersWithLateOrders( undef, $daysago10, undef );
ok( exists( $suppliers{$id_supplier1} ),
    "Supplier1 has late orders and $daysago10==$daysago10 " );
#    "Supplier1 has late orders and $daysago10==$daysago10 " );

#Case 13: closedate == $estimateddeliverydateto =today-10
%suppliers =
- 

Return to bug 10641

Lines 111-120 sub GetBooksellersWithLateOrders { Link Here

(-)a/C4/Bookseller.pm (-13 / +19 lines)
111	# FIXME NOT quite sure that this operation is valid for DBMs different from Mysql, HOPING so	111	# FIXME NOT quite sure that this operation is valid for DBMs different from Mysql, HOPING so
112	# should be tested with other DBMs	112	# should be tested with other DBMs
113		113
114	my $strsth;	114	my $query;
115	my @query_params = ();	115	my @query_params = ();
116	my $dbdriver = C4::Context->config("db_scheme") \|\| "mysql";	116	my $dbdriver = C4::Context->config("db_scheme") \|\| "mysql";
117	$strsth = "	117	$query = "
118	SELECT DISTINCT aqbasket.booksellerid, aqbooksellers.name	118	SELECT DISTINCT aqbasket.booksellerid, aqbooksellers.name
119	FROM aqorders LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno	119	FROM aqorders LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno
120	LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id	120	LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
Lines 128-151 sub GetBooksellersWithLateOrders { Link Here
128	AND aqorders.quantity - COALESCE(aqorders.quantityreceived,0) <> 0	128	AND aqorders.quantity - COALESCE(aqorders.quantityreceived,0) <> 0
129	AND aqbasket.closedate IS NOT NULL	129	AND aqbasket.closedate IS NOT NULL
130	";	130	";
131	if ( defined $delay ) {	131	if ( defined $delay && $delay >= 0 ) {
132	$strsth .= " AND (closedate <= DATE_SUB(CAST(now() AS date),INTERVAL ? DAY)) ";	132	$query .= " AND (closedate <= DATE_SUB(CAST(now() AS date),INTERVAL ? + COALESCE(aqbooksellers.deliverytime,0) DAY)) ";
133	push @query_params, $delay;	133	push @query_params, $delay;
		134	} elsif ( $delay < 0 ){
		135	warn 'WARNING: GetBooksellerWithLateOrders is called with a negative value';
		136	return;
134	}	137	}
135	if ( defined $estimateddeliverydatefrom ) {	138	if ( defined $estimateddeliverydatefrom ) {
136	$strsth .= '	139	$query .= '
137	AND aqbooksellers.deliverytime IS NOT NULL	140	AND ADDDATE(aqbasket.closedate, INTERVAL COALESCE(aqbooksellers.deliverytime,0) DAY) >= ?';
138	AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) >= ?';	141	push @query_params, $estimateddeliverydatefrom;
139	push @query_params, $estimateddeliverydatefrom;	142	if ( defined $estimateddeliverydateto ) {
		143	$query .= ' AND ADDDATE(aqbasket.closedate, INTERVAL COALESCE(aqbooksellers.deliverytime, 0) DAY) <= ?';
		144	push @query_params, $estimateddeliverydateto;
		145	} else {
		146	$query .= ' AND ADDDATE(aqbasket.closedate, INTERVAL COALESCE(aqbooksellers.deliverytime, 0) DAY) <= CAST(now() AS date)';
		147	}
140	}	148	}
141	if ( defined $estimateddeliverydatefrom and defined $estimateddeliverydateto ) {	149	if ( defined $estimateddeliverydateto ) {
142	$strsth .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= ?';	150	$query .= ' AND ADDDATE(aqbasket.closedate, INTERVAL COALESCE(aqbooksellers.deliverytime,0) DAY) <= ?';
143	push @query_params, $estimateddeliverydateto;	151	push @query_params, $estimateddeliverydateto;
144	} elsif ( defined $estimateddeliverydatefrom ) {
145	$strsth .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= CAST(now() AS date)';
146	}	152	}
147		153
148	my $sth = $dbh->prepare($strsth);	154	my $sth = $dbh->prepare($query);
149	$sth->execute( @query_params );	155	$sth->execute( @query_params );
150	my %supplierlist;	156	my %supplierlist;
151	while ( my ( $id, $name ) = $sth->fetchrow ) {	157	while ( my ( $id, $name ) = $sth->fetchrow ) {

Lines 2-8 Link Here

(-)a/t/db_dependent/Bookseller.t (-32 / +22 lines)
2		2
3	use Modern::Perl;	3	use Modern::Perl;
4		4
5	use Test::More tests => 53;	5	use Test::More tests => 61;
6	use C4::Context;	6	use C4::Context;
7	use Koha::DateUtils;	7	use Koha::DateUtils;
8	use DateTime::Duration;	8	use DateTime::Duration;
Lines 476-492 isnt( exists( $suppliers{$id_supplier4} ), 1, "Supplier4 hasnt late orders" ) Link Here
476	%suppliers = C4::Bookseller::GetBooksellersWithLateOrders( 4, undef, undef );	476	%suppliers = C4::Bookseller::GetBooksellersWithLateOrders( 4, undef, undef );
477	isnt( exists( $suppliers{$id_supplier1} ),	477	isnt( exists( $suppliers{$id_supplier1} ),
478	1, "Supplier1 has late orders but today > now() - 4 days" );	478	1, "Supplier1 has late orders but today > now() - 4 days" );
479	#FIXME: If only the field delay is given, it doen't consider the deliverytime	479	isnt( exists( $suppliers{$id_supplier2} ),
480	#isnt( exists( $suppliers{$id_supplier2} ),	480	1, "Supplier2 has late orders and $daysago5 <= now() - (4 days+2)" );
481	# 1, "Supplier2 has late orders and $daysago5 <= now() - (4 days+2)" );
482	ok( exists( $suppliers{$id_supplier3} ),	481	ok( exists( $suppliers{$id_supplier3} ),
483	"Supplier3 has late orders and $daysago10 <= now() - (4 days+3)" );	482	"Supplier3 has late orders and $daysago10 <= now() - (4 days+3)" );
484	isnt( exists( $suppliers{$id_supplier4} ), 1, "Supplier4 hasnt late orders" );	483	isnt( exists( $suppliers{$id_supplier4} ), 1, "Supplier4 hasnt late orders" );
485		484
486	#Case 3: With $delay = -1	485	#Case 3: With $delay = -1
487	#FIXME: GetBooksellersWithLateOrders doesn't test if the delay is a positive value	486	is( C4::Bookseller::GetBooksellersWithLateOrders( -1, undef, undef ),
488	#is( C4::Bookseller::GetBooksellersWithLateOrders( -1, undef, undef ),	487	undef, "-1 is a wrong value for a delay" );
489	# undef, "-1 is a wrong value for a delay" );
490		488
491	#Case 4: With $delay = 0	489	#Case 4: With $delay = 0
492	# today == now-0 -LATE- (if no deliverytime or deliverytime == 0)	490	# today == now-0 -LATE- (if no deliverytime or deliverytime == 0)
Lines 516-524 my $daysago4 = output_pref( $dt_today3, 'iso', '24hr', 1 ); Link Here
516	%suppliers =	514	%suppliers =
517	C4::Bookseller::GetBooksellersWithLateOrders( undef, $daysago4, undef );	515	C4::Bookseller::GetBooksellersWithLateOrders( undef, $daysago4, undef );
518		516
519	#FIXME: if the deliverytime is undef, it doesn't consider the supplier	517	ok( exists( $suppliers{$id_supplier1} ),
520	#ok( exists( $suppliers{$id_supplier1} ),	518	"Supplier1 has late orders and $today >= $daysago4 -deliverytime undef" );
521	# "Supplier1 has late orders and $today >= $daysago4 -deliverytime undef" );
522	ok( exists( $suppliers{$id_supplier2} ),	519	ok( exists( $suppliers{$id_supplier2} ),
523	"Supplier2 has late orders and $daysago5 + 2 days >= $daysago4 " );	520	"Supplier2 has late orders and $daysago5 + 2 days >= $daysago4 " );
524	isnt( exists( $suppliers{$id_supplier3} ),	521	isnt( exists( $suppliers{$id_supplier3} ),
Lines 552-564 isnt( exists( $suppliers{$id_supplier4} ), 1, "Supplier4 hasnt late orders" ); Link Here
552	# quantityreceived = quantity -NOT LATE-	549	# quantityreceived = quantity -NOT LATE-
553	%suppliers =	550	%suppliers =
554	C4::Bookseller::GetBooksellersWithLateOrders( undef, undef, $daysago5 );	551	C4::Bookseller::GetBooksellersWithLateOrders( undef, undef, $daysago5 );
555	#FIXME: if only the estimateddeliverydatefrom is given, it doesn't consider the parameters,	552	isnt( exists( $suppliers{$id_supplier1} ),
556	#but it replaces it today's date	553	1,
557	#isnt( exists( $suppliers{$id_supplier1} ),	554	"Supplier1 has late orders but $today >= $daysago5 - deliverytime undef" );
558	# 1,	555	isnt( exists( $suppliers{$id_supplier2} ),
559	# "Supplier1 has late orders but $today >= $daysago5 - deliverytime undef" );	556	1, "Supplier2 has late orders but $daysago5 + 2 days > $daysago5 " );
560	#isnt( exists( $suppliers{$id_supplier2} ),
561	# 1, "Supplier2 has late orders but $daysago5 + 2 days > $daysago5 " );
562	ok( exists( $suppliers{$id_supplier3} ),	557	ok( exists( $suppliers{$id_supplier3} ),
563	"Supplier3 has late orders and $daysago10 + 3 <= $daysago5" );	558	"Supplier3 has late orders and $daysago10 + 3 <= $daysago5" );
564	isnt( exists( $suppliers{$id_supplier4} ), 1, "Supplier4 hasnt late orders" );	559	isnt( exists( $suppliers{$id_supplier4} ), 1, "Supplier4 hasnt late orders" );
Lines 616-628 isnt( exists( $suppliers{$id_supplier4} ), 1, "Supplier4 hasnt late orders" ); Link Here
616	C4::Bookseller::GetBooksellersWithLateOrders( 5, undef, $daysago5 );	611	C4::Bookseller::GetBooksellersWithLateOrders( 5, undef, $daysago5 );
617	isnt( exists( $suppliers{$id_supplier1} ),	612	isnt( exists( $suppliers{$id_supplier1} ),
618	1, "Supplier2 has late orders but today > $daysago5 today > now() -5" );	613	1, "Supplier2 has late orders but today > $daysago5 today > now() -5" );
619	#FIXME: GetBookSellersWithLateOrders replace estimateddeliverydateto by	614	isnt(
620	#today's date when no estimateddeliverydatefrom is give	615	exists( $suppliers{$id_supplier2} ),
621	#isnt(	616	1,
622	# exists( $suppliers{$id_supplier2} ),	617	"Supplier2 has late orders but $daysago5 + 2 days > $daysago5 and $daysago5 > now() - 2+5 days"
623	# 1,	618	);
624	#"Supplier2 has late orders but $daysago5 + 2 days > $daysago5 and $daysago5 > now() - 2+5 days"
625	#);
626	ok(	619	ok(
627	exists( $suppliers{$id_supplier3} ),	620	exists( $suppliers{$id_supplier3} ),
628	"Supplier2 has late orders and $daysago10 + 3 days <= $daysago5 and $daysago10 <= now() - 3+5 days "	621	"Supplier2 has late orders and $daysago10 + 3 days <= $daysago5 and $daysago10 <= now() - 3+5 days "
Lines 643-652 $basket1info = { Link Here
643	ModBasket($basket1info);	636	ModBasket($basket1info);
644	%suppliers = C4::Bookseller::GetBooksellersWithLateOrders( undef, $daysago10,	637	%suppliers = C4::Bookseller::GetBooksellersWithLateOrders( undef, $daysago10,
645	$daysago10 );	638	$daysago10 );
646	#FIXME :GetBookSellers doesn't take care if the closedate is ==$estimateddeliverydateto	639	ok( exists( $suppliers{$id_supplier1} ),
647	# ok( exists( $suppliers{$id_supplier1} ),	640	"Supplier1 has late orders and $daysago10==$daysago10==$daysago10 " )
648	# "Supplier1 has late orders and $daysago10==$daysago10==$daysago10 " )	641	;
649	# ;
650	isnt( exists( $suppliers{$id_supplier2} ),	642	isnt( exists( $suppliers{$id_supplier2} ),
651	1,	643	1,
652	"Supplier2 has late orders but $daysago10==$daysago10<$daysago5+2" );	644	"Supplier2 has late orders but $daysago10==$daysago10<$daysago5+2" );
Lines 658-666 isnt( exists( $suppliers{$id_supplier4} ), 1, "Supplier4 hasnt late orders" ); Link Here
658	#Case 12: closedate == $estimateddeliverydatefrom =today-10	650	#Case 12: closedate == $estimateddeliverydatefrom =today-10
659	%suppliers =	651	%suppliers =
660	C4::Bookseller::GetBooksellersWithLateOrders( undef, $daysago10, undef );	652	C4::Bookseller::GetBooksellersWithLateOrders( undef, $daysago10, undef );
661	#FIXME :GetBookSellers doesn't take care if the closedate is ==$estimateddeliverydateto	653	ok( exists( $suppliers{$id_supplier1} ),
662	#ok( exists( $suppliers{$id_supplier1} ),	654	"Supplier1 has late orders and $daysago10==$daysago10 " );
663	# "Supplier1 has late orders and $daysago10==$daysago10 " );
664		655
665	#Case 13: closedate == $estimateddeliverydateto =today-10	656	#Case 13: closedate == $estimateddeliverydateto =today-10
666	%suppliers =	657	%suppliers =
667	-