Attachment #93482 for bug #11708

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




    my $dbh        = C4::Context->dbh;
    my $sth = $dbh->prepare("
        UPDATE aqbasketgroups
        SET closeddate=CAST(NOW() AS DATE)
        WHERE  id=?
    ");
    $sth->execute($basketgroupno);

    my $dbh        = C4::Context->dbh;
    my $sth = $dbh->prepare("
        UPDATE aqbasketgroups
        SET    closeddate=NULL
        WHERE  id=?
    ");
    $sth->execute($basketgroupno);


$hashref->{'deliverycomment'} is the 'deliverycomment' field of the basketgroup in the aqbasketgroups table,

$hashref->{'closed'} set 'closeddate' if true, or reset 'closeddate' if defined but false.

=cut


    die "booksellerid is required to create a basketgroup" unless $basketgroupinfo->{'booksellerid'};
    my $query = "INSERT INTO aqbasketgroups (";
    my @params;
    foreach my $field (qw(name billingplace deliveryplace freedeliveryplace deliverycomment)) {
        if ( defined $basketgroupinfo->{$field} ) {
            $query .= "$field, ";
            push(@params, $basketgroupinfo->{$field});
        }
    }

    if ($basketgroupinfo->{closed}) {
        $query .= "closeddate, ";
    }
    $query .= "booksellerid) VALUES (";
    foreach (@params) {
        $query .= "?, ";
    }
    if ($basketgroupinfo->{closed}) {
        $query .= "CAST(NOW() AS DATE), ";
    }
    $query .= "?)";
    push(@params, $basketgroupinfo->{'booksellerid'});
    my $dbh = C4::Context->dbh;


$hashref->{'deliverycomment'} is the 'deliverycomment' field of the basketgroup in the aqbasketgroups table,

$hashref->{'closed'} set 'closeddate' if true, or reset 'closeddate' if defined but false.

=cut


    my $dbh = C4::Context->dbh;
    my $query = "UPDATE aqbasketgroups SET ";
    my @params;

    if ($basketgroupinfo->{closed}) {
        $query .= "closeddate = IF(closeddate IS NULL, CAST(NOW() AS DATE), closeddate), ";
    } elsif (defined $basketgroupinfo->{closed}) {
        $query .= "closeddate = NULL, ";
    }

    foreach my $field (qw(name billingplace deliveryplace freedeliveryplace deliverycomment)) {
        if ( defined $basketgroupinfo->{$field} ) {
            $query .= "$field=?, ";
            push(@params, $basketgroupinfo->{$field});

        { Slice => {} },
        $basketgroupid
    );
    my $basketgroup = $result_set->[0];    # id is unique
    $basketgroup->{closed} = defined($basketgroup->{closeddate}) ? 1 : 0;
    return $basketgroup;
}

#------------------------------------------------------------#

    my $dbh = C4::Context->dbh;
    my $sth = $dbh->prepare($query);
    $sth->execute($booksellerid);
    my $basketgroups = $sth->fetchall_arrayref({});
    foreach my $bg (@$basketgroups) {
        $bg->{closed} = defined($bg->{closeddate}) ? 1 : 0;
    }
    return $basketgroups;
}

#------------------------------------------------------------#




$DBversion = 'XXX';
if( CheckVersion( $DBversion ) ) {
    unless (column_exists('aqbasketgroups', 'closeddate')) {
        $dbh->do(q{
            ALTER TABLE aqbasketgroups
            ADD COLUMN closeddate DATE DEFAULT NULL AFTER name
        });
    }

    if (column_exists('aqbasketgroups', 'closed')) {
        # Set basketgroup's close date to the latest close date of its baskets,
        # or NOW() if it has no baskets
        $dbh->do(q{
            UPDATE aqbasketgroups
                LEFT JOIN (
                    SELECT basketgroupid, MAX(closedate) AS closedate
                    FROM aqbasket
                    GROUP BY basketgroupid
                ) AS aqbasket ON (aqbasketgroups.id = aqbasket.basketgroupid)
            SET aqbasketgroups.closeddate = COALESCE(aqbasket.closedate, CAST(NOW() AS DATE))
            WHERE closed = 1
              AND closeddate IS NULL
        });
        $dbh->do(q{
            ALTER TABLE aqbasketgroups
            DROP COLUMN closed
        });
    }

    SetVersion( $DBversion );
    print "Upgrade to $DBversion done (Bug 11708 - Change aqbasketgroups.closed to closeddate)\n";
}

Lines 2772-2778 DROP TABLE IF EXISTS `aqbasketgroups`; Link Here

(-)a/installer/data/mysql/kohastructure.sql (-1 / +1 lines)
2772	CREATE TABLE `aqbasketgroups` (	2772	CREATE TABLE `aqbasketgroups` (
2773	`id` int(11) NOT NULL auto_increment,	2773	`id` int(11) NOT NULL auto_increment,
2774	`name` varchar(50) default NULL,	2774	`name` varchar(50) default NULL,
2775	`closed` tinyint(1) default NULL,	2775	`closeddate` date default NULL,
2776	`booksellerid` int(11) NOT NULL,	2776	`booksellerid` int(11) NOT NULL,
2777	`deliveryplace` varchar(10) default NULL,	2777	`deliveryplace` varchar(10) default NULL,
2778	`freedeliveryplace` MEDIUMTEXT default NULL,	2778	`freedeliveryplace` MEDIUMTEXT default NULL,




#!/usr/bin/perl

use Modern::Perl;

use DateTime;

use Koha::Database;

use C4::Context;
use C4::Acquisition;

use Test::More tests => 13;

use t::lib::TestBuilder;

my $schema = Koha::Database->new->schema;
$schema->storage->txn_begin;

my $builder = t::lib::TestBuilder->new;

my $bookseller = $builder->build_object({
    class => 'Koha::Acquisition::Booksellers',
    value => {
        name => 'Bookseller test 1',
    }
});

my $booksellerid = $bookseller->id;
my $basketgroupid = NewBasketgroup({
    booksellerid => $booksellerid,
    name => "Basketgroup test 1",
});

my $basketgroup = GetBasketgroup($basketgroupid);
ok(!$basketgroup->{closed}, "basket group is open");
ok(!defined($basketgroup->{closeddate}), "basket group closed date is empty");

CloseBasketgroup($basketgroupid);
$basketgroup = GetBasketgroup($basketgroupid);
ok($basketgroup->{closed}, "basket group is closed");
ok(defined($basketgroup->{closeddate}), "basket group closed date is not empty");
my $today = DateTime->now(time_zone => C4::Context->tz);
is($basketgroup->{closeddate}, $today->ymd, "basket group closed date is correct");

ReOpenBasketgroup($basketgroupid);
$basketgroup = GetBasketgroup($basketgroupid);
ok(!$basketgroup->{closed}, "basket group is open");
ok(!defined($basketgroup->{closeddate}), "basket group closed date is empty");

$basketgroup->{closed} = 1;
ModBasketgroup($basketgroup);
$basketgroup = GetBasketgroup($basketgroupid);
ok($basketgroup->{closed}, "basket group is closed");
ok(defined($basketgroup->{closeddate}), "basket group closed date is not empty");
is($basketgroup->{closeddate}, $today->ymd, "basket group closed date is correct");

$basketgroupid = NewBasketgroup({
    booksellerid => $booksellerid,
    name => "Basketgroup test 1",
    closed => 1,
});
$basketgroup = GetBasketgroup($basketgroupid);
ok($basketgroup->{closed}, "basket group is closed");
ok(defined($basketgroup->{closeddate}), "basket group closed date is not empty");
is($basketgroup->{closeddate}, $today->ymd, "basket group closed date is correct");

$schema->storage->txn_rollback();

Return to bug 11708

Lines 457-463 sub CloseBasketgroup { Link Here

(-)a/C4/Acquisition.pm (-8 / +28 lines)
457	my $dbh = C4::Context->dbh;	457	my $dbh = C4::Context->dbh;
458	my $sth = $dbh->prepare("	458	my $sth = $dbh->prepare("
459	UPDATE aqbasketgroups	459	UPDATE aqbasketgroups
460	SET closed=1	460	SET closeddate=CAST(NOW() AS DATE)
461	WHERE id=?	461	WHERE id=?
462	");	462	");
463	$sth->execute($basketgroupno);	463	$sth->execute($basketgroupno);
Lines 478-484 sub ReOpenBasketgroup { Link Here
478	my $dbh = C4::Context->dbh;	478	my $dbh = C4::Context->dbh;
479	my $sth = $dbh->prepare("	479	my $sth = $dbh->prepare("
480	UPDATE aqbasketgroups	480	UPDATE aqbasketgroups
481	SET closed=0	481	SET closeddate=NULL
482	WHERE id=?	482	WHERE id=?
483	");	483	");
484	$sth->execute($basketgroupno);	484	$sth->execute($basketgroupno);
Lines 914-920 $hashref->{'freedeliveryplace'} is the 'freedeliveryplace' field of the basketgr Link Here
914		914
915	$hashref->{'deliverycomment'} is the 'deliverycomment' field of the basketgroup in the aqbasketgroups table,	915	$hashref->{'deliverycomment'} is the 'deliverycomment' field of the basketgroup in the aqbasketgroups table,
916		916
917	$hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise.	917	$hashref->{'closed'} set 'closeddate' if true, or reset 'closeddate' if defined but false.
918		918
919	=cut	919	=cut
920		920
Lines 923-938 sub NewBasketgroup { Link Here
923	die "booksellerid is required to create a basketgroup" unless $basketgroupinfo->{'booksellerid'};	923	die "booksellerid is required to create a basketgroup" unless $basketgroupinfo->{'booksellerid'};
924	my $query = "INSERT INTO aqbasketgroups (";	924	my $query = "INSERT INTO aqbasketgroups (";
925	my @params;	925	my @params;
926	foreach my $field (qw(name billingplace deliveryplace freedeliveryplace deliverycomment closed)) {	926	foreach my $field (qw(name billingplace deliveryplace freedeliveryplace deliverycomment)) {
927	if ( defined $basketgroupinfo->{$field} ) {	927	if ( defined $basketgroupinfo->{$field} ) {
928	$query .= "$field, ";	928	$query .= "$field, ";
929	push(@params, $basketgroupinfo->{$field});	929	push(@params, $basketgroupinfo->{$field});
930	}	930	}
931	}	931	}
		932
		933	if ($basketgroupinfo->{closed}) {
		934	$query .= "closeddate, ";
		935	}
932	$query .= "booksellerid) VALUES (";	936	$query .= "booksellerid) VALUES (";
933	foreach (@params) {	937	foreach (@params) {
934	$query .= "?, ";	938	$query .= "?, ";
935	}	939	}
		940	if ($basketgroupinfo->{closed}) {
		941	$query .= "CAST(NOW() AS DATE), ";
		942	}
936	$query .= "?)";	943	$query .= "?)";
937	push(@params, $basketgroupinfo->{'booksellerid'});	944	push(@params, $basketgroupinfo->{'booksellerid'});
938	my $dbh = C4::Context->dbh;	945	my $dbh = C4::Context->dbh;
Lines 971-977 $hashref->{'freedeliveryplace'} is the 'freedeliveryplace' field of the basketgr Link Here
971		978
972	$hashref->{'deliverycomment'} is the 'deliverycomment' field of the basketgroup in the aqbasketgroups table,	979	$hashref->{'deliverycomment'} is the 'deliverycomment' field of the basketgroup in the aqbasketgroups table,
973		980
974	$hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise.	981	$hashref->{'closed'} set 'closeddate' if true, or reset 'closeddate' if defined but false.
975		982
976	=cut	983	=cut
977		984
Lines 981-987 sub ModBasketgroup { Link Here
981	my $dbh = C4::Context->dbh;	988	my $dbh = C4::Context->dbh;
982	my $query = "UPDATE aqbasketgroups SET ";	989	my $query = "UPDATE aqbasketgroups SET ";
983	my @params;	990	my @params;
984	foreach my $field (qw(name billingplace deliveryplace freedeliveryplace deliverycomment closed)) {	991
		992	if ($basketgroupinfo->{closed}) {
		993	$query .= "closeddate = IF(closeddate IS NULL, CAST(NOW() AS DATE), closeddate), ";
		994	} elsif (defined $basketgroupinfo->{closed}) {
		995	$query .= "closeddate = NULL, ";
		996	}
		997
		998	foreach my $field (qw(name billingplace deliveryplace freedeliveryplace deliverycomment)) {
985	if ( defined $basketgroupinfo->{$field} ) {	999	if ( defined $basketgroupinfo->{$field} ) {
986	$query .= "$field=?, ";	1000	$query .= "$field=?, ";
987	push(@params, $basketgroupinfo->{$field});	1001	push(@params, $basketgroupinfo->{$field});
Lines 1054-1060 sub GetBasketgroup { Link Here
1054	{ Slice => {} },	1068	{ Slice => {} },
1055	$basketgroupid	1069	$basketgroupid
1056	);	1070	);
1057	return $result_set->[0]; # id is unique	1071	my $basketgroup = $result_set->[0]; # id is unique
		1072	$basketgroup->{closed} = defined($basketgroup->{closeddate}) ? 1 : 0;
		1073	return $basketgroup;
1058	}	1074	}
1059		1075
1060	#------------------------------------------------------------#	1076	#------------------------------------------------------------#
Lines 1074-1080 sub GetBasketgroups { Link Here
1074	my $dbh = C4::Context->dbh;	1090	my $dbh = C4::Context->dbh;
1075	my $sth = $dbh->prepare($query);	1091	my $sth = $dbh->prepare($query);
1076	$sth->execute($booksellerid);	1092	$sth->execute($booksellerid);
1077	return $sth->fetchall_arrayref({});	1093	my $basketgroups = $sth->fetchall_arrayref({});
		1094	foreach my $bg (@$basketgroups) {
		1095	$bg->{closed} = defined($bg->{closeddate}) ? 1 : 0;
		1096	}
		1097	return $basketgroups;
1078	}	1098	}
1079		1099
1080	#------------------------------------------------------------#	1100	#------------------------------------------------------------#

Line 0 Link Here

(-)a/installer/data/mysql/atomicupdate/bug_11708.perl (+32 lines)
	1	$DBversion = 'XXX';
	2	if( CheckVersion( $DBversion ) ) {
	3	unless (column_exists('aqbasketgroups', 'closeddate')) {
	4	$dbh->do(q{
	5	ALTER TABLE aqbasketgroups
	6	ADD COLUMN closeddate DATE DEFAULT NULL AFTER name
	7	});
	8	}
	9
	10	if (column_exists('aqbasketgroups', 'closed')) {
	11	# Set basketgroup's close date to the latest close date of its baskets,
	12	# or NOW() if it has no baskets
	13	$dbh->do(q{
	14	UPDATE aqbasketgroups
	15	LEFT JOIN (
	16	SELECT basketgroupid, MAX(closedate) AS closedate
	17	FROM aqbasket
	18	GROUP BY basketgroupid
	19	) AS aqbasket ON (aqbasketgroups.id = aqbasket.basketgroupid)
	20	SET aqbasketgroups.closeddate = COALESCE(aqbasket.closedate, CAST(NOW() AS DATE))
	21	WHERE closed = 1
	22	AND closeddate IS NULL
	23	});
	24	$dbh->do(q{
	25	ALTER TABLE aqbasketgroups
	26	DROP COLUMN closed
	27	});
	28	}
	29
	30	SetVersion( $DBversion );
	31	print "Upgrade to $DBversion done (Bug 11708 - Change aqbasketgroups.closed to closeddate)\n";
	32	}

Line 0 Link Here

(-)a/t/db_dependent/Acquisition/Basketgroups.t (-1 / +67 lines)
0	-	1	#!/usr/bin/perl
		2
		3	use Modern::Perl;
		4
		5	use DateTime;
		6
		7	use Koha::Database;
		8
		9	use C4::Context;
		10	use C4::Acquisition;
		11
		12	use Test::More tests => 13;
		13
		14	use t::lib::TestBuilder;
		15
		16	my $schema = Koha::Database->new->schema;
		17	$schema->storage->txn_begin;
		18
		19	my $builder = t::lib::TestBuilder->new;
		20
		21	my $bookseller = $builder->build_object({
		22	class => 'Koha::Acquisition::Booksellers',
		23	value => {
		24	name => 'Bookseller test 1',
		25	}
		26	});
		27
		28	my $booksellerid = $bookseller->id;
		29	my $basketgroupid = NewBasketgroup({
		30	booksellerid => $booksellerid,
		31	name => "Basketgroup test 1",
		32	});
		33
		34	my $basketgroup = GetBasketgroup($basketgroupid);
		35	ok(!$basketgroup->{closed}, "basket group is open");
		36	ok(!defined($basketgroup->{closeddate}), "basket group closed date is empty");
		37
		38	CloseBasketgroup($basketgroupid);
		39	$basketgroup = GetBasketgroup($basketgroupid);
		40	ok($basketgroup->{closed}, "basket group is closed");
		41	ok(defined($basketgroup->{closeddate}), "basket group closed date is not empty");
		42	my $today = DateTime->now(time_zone => C4::Context->tz);
		43	is($basketgroup->{closeddate}, $today->ymd, "basket group closed date is correct");
		44
		45	ReOpenBasketgroup($basketgroupid);
		46	$basketgroup = GetBasketgroup($basketgroupid);
		47	ok(!$basketgroup->{closed}, "basket group is open");
		48	ok(!defined($basketgroup->{closeddate}), "basket group closed date is empty");
		49
		50	$basketgroup->{closed} = 1;
		51	ModBasketgroup($basketgroup);
		52	$basketgroup = GetBasketgroup($basketgroupid);
		53	ok($basketgroup->{closed}, "basket group is closed");
		54	ok(defined($basketgroup->{closeddate}), "basket group closed date is not empty");
		55	is($basketgroup->{closeddate}, $today->ymd, "basket group closed date is correct");
		56
		57	$basketgroupid = NewBasketgroup({
		58	booksellerid => $booksellerid,
		59	name => "Basketgroup test 1",
		60	closed => 1,
		61	});
		62	$basketgroup = GetBasketgroup($basketgroupid);
		63	ok($basketgroup->{closed}, "basket group is closed");
		64	ok(defined($basketgroup->{closeddate}), "basket group closed date is not empty");
		65	is($basketgroup->{closeddate}, $today->ymd, "basket group closed date is correct");
66
67	$schema->storage->txn_rollback();