From 5d6a89a004f6ed3ca7ca5a7584146e2d31575d04 Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Thu, 16 Feb 2012 12:54:12 +0100 Subject: [PATCH 1/1] Bug 7303: Adds unicity for budget and fund This patch adds a unique key on fields: aqbudgets.budget_name aqbudgets.budget_code aqbudgetperiods.budget_period_description To test, try to: - create 2 budgets with same description - create 2 funds with same name - create 2 funds with same code All of these cases should be generate an error --- admin/aqbudgetperiods.pl | 30 ++++++++++++-------- admin/aqbudgets.pl | 8 ++++- installer/data/mysql/kohastructure.sql | 13 +++++--- installer/data/mysql/updatedatabase.pl | 25 ++++++++++++++++ .../prog/en/modules/admin/aqbudgetperiods.tt | 6 ++++ .../prog/en/modules/admin/aqbudgets.tt | 6 ++++ 6 files changed, 69 insertions(+), 19 deletions(-) diff --git a/admin/aqbudgetperiods.pl b/admin/aqbudgetperiods.pl index 64b333a..5bda84a 100755 --- a/admin/aqbudgetperiods.pl +++ b/admin/aqbudgetperiods.pl @@ -69,8 +69,8 @@ my $budget_period_id = $input->param('budget_period_id'); my $op = $input->param('op')||"else"; my $budget_period_hashref= $input->Vars; -#my $sort1_authcat = $input->param('sort1_authcat'); -#my $sort2_authcat = $input->param('sort2_authcat'); + +my $sql_error; my $pagesize = 20; $searchfield =~ s/\,//g; @@ -140,15 +140,18 @@ if ( $op eq 'add_form' ) { elsif ( $op eq 'add_validate' ) { ## add or modify a budget period (confirmation) - ## update budget period data - if ( $budget_period_id ne '' ) { - $$budget_period_hashref{$_}||=0 for qw(budget_period_active budget_period_locked); - my $status=ModBudgetPeriod($budget_period_hashref); - } - else { # ELSE ITS AN ADD - my $budget_period_id=AddBudgetPeriod($budget_period_hashref); - } - $op='else'; + ## update budget period data + my $r; + if ( $budget_period_id ne '' ) { + $$budget_period_hashref{$_}||=0 for qw(budget_period_active budget_period_locked); + $r = ModBudgetPeriod($budget_period_hashref); + } + else { # ELSE ITS AN ADD + $r = AddBudgetPeriod($budget_period_hashref); + } + + $sql_error = 1 unless $r; + $op='else'; } #-------------------------------------------------- @@ -267,5 +270,8 @@ elsif ( $op eq 'duplicate_budget' ){ pagination_bar => pagination_bar("aqbudgetperiods.pl",getnbpages(scalar(@$results),$pagesize),$page), ); -$template->param($op=>1); +$template->param( + $op => 1, + sql_error => $sql_error, +); output_html_with_http_headers $input, $cookie, $template->output; diff --git a/admin/aqbudgets.pl b/admin/aqbudgets.pl index db99759..21d3b1e 100755 --- a/admin/aqbudgets.pl +++ b/admin/aqbudgets.pl @@ -40,6 +40,7 @@ use C4::Debug; my $input = new CGI; my $dbh = C4::Context->dbh; +my $sql_error; my ($template, $borrowernumber, $cookie, $staffflags ) = get_template_and_user( { template_name => "admin/aqbudgets.tmpl", @@ -217,11 +218,13 @@ if ($op eq 'add_form') { if ( $op eq 'delete_confirmed' ) { my $rc = DelBudget($budget_id); }elsif( $op eq 'add_validate' ) { + my $r; if ( defined $$budget_hash{budget_id} ) { - ModBudget( $budget_hash ); + $r = ModBudget( $budget_hash ); } else { - AddBudget( $budget_hash ); + $r = AddBudget( $budget_hash ); } + $sql_error = 1 unless $r; } my $branches = GetBranches(); $template->param( @@ -332,6 +335,7 @@ if ($op eq 'add_form') { period_alloc_total => $num->format_price($period_alloc_total), base_spent_total => $num->format_price($base_spent_total), branchloop => \@branchloop2, + sql_error => $sql_error, ); } #---- END $OP eq DEFAULT diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index 37113c2..f1ca330 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -2510,8 +2510,8 @@ DROP TABLE IF EXISTS `aqbudgets`; CREATE TABLE `aqbudgets` ( `budget_id` int(11) NOT NULL auto_increment, `budget_parent_id` int(11) default NULL, - `budget_code` varchar(30) default NULL, - `budget_name` varchar(80) default NULL, + `budget_code` varchar(30) NOT NULL, + `budget_name` varchar(80) NOT NULL, `budget_branchcode` varchar(10) default NULL, `budget_amount` decimal(28,6) NULL default '0.00', `budget_encumb` decimal(28,6) NULL default '0.00', @@ -2523,7 +2523,9 @@ CREATE TABLE `aqbudgets` ( `sort2_authcat` varchar(80) default NULL, `budget_owner_id` int(11) default NULL, `budget_permission` int(1) default '0', - PRIMARY KEY (`budget_id`) + PRIMARY KEY (`budget_id`), + UNIQUE KEY `uniq_budget_name_aqbudgets` (`budget_name`), + UNIQUE KEY `uniq_budget_code_aqbudgets` (`budget_code` ) ENGINE=InnoDB DEFAULT CHARSET=utf8; @@ -2538,12 +2540,13 @@ CREATE TABLE `aqbudgetperiods` ( `budget_period_startdate` date NOT NULL, `budget_period_enddate` date NOT NULL, `budget_period_active` tinyint(1) default '0', - `budget_period_description` mediumtext, + `budget_period_description` varchar(255) NOT NULL, `budget_period_total` decimal(28,6), `budget_period_locked` tinyint(1) default NULL, `sort1_authcat` varchar(10) default NULL, `sort2_authcat` varchar(10) default NULL, - PRIMARY KEY (`budget_period_id`) + PRIMARY KEY (`budget_period_id`), + UNIQUE KEY `uniq_aqbudgetperiods` (`budget_period_description`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index 0ef0b1f..bb26522 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -4684,6 +4684,31 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) { SetVersion($DBversion); } +$DBversion = "3.07.00.XXX"; +if (C4::Context->preference("Version") < TransformToNum($DBversion)) { + $dbh->do(q{ + ALTER TABLE `aqbudgetperiods` MODIFY COLUMN `budget_period_description` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL; + }); + $dbh->do(q{ + ALTER TABLE `aqbudgetperiods` ADD UNIQUE ( budget_period_description); + }); + $dbh->do(q{ + ALTER TABLE `aqbudgets` MODIFY COLUMN `budget_name` VARCHAR(80) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL; + }); + $dbh->do(q{ + ALTER TABLE `aqbudgets` MODIFY COLUMN `budget_code` VARCHAR(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL; + }); + $dbh->do(q{ + ALTER TABLE `aqbudgets` ADD UNIQUE ( budget_name ); + }); + $dbh->do(q{ + ALTER TABLE `aqbudgets` ADD UNIQUE ( budget_code ); + }); + + print "Upgrade to $DBversion done (aqbudgets and aqbudgetperiods. Unique constraint on field.)\n"; + SetVersion ($DBversion); +} + =head1 FUNCTIONS =head2 DropAllForeignKeys($table) diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/aqbudgetperiods.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/aqbudgetperiods.tt index 29dcf7f..21af3f0 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/aqbudgetperiods.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/aqbudgetperiods.tt @@ -149,6 +149,12 @@
+[% IF ( sql_error ) %] +
+ This budget name already exists +
+[% END %] + [% INCLUDE 'budgets-admin-toolbar.inc' %] [% IF ( duplicate_form ) %] diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/aqbudgets.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/aqbudgets.tt index 12a70bc..bfbc923 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/aqbudgets.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/aqbudgets.tt @@ -155,6 +155,12 @@
+[% IF ( sql_error ) %] +
+ This fund name (or code) already exists +
+[% END %] + [% UNLESS ( delete_confirm ) %][% INCLUDE 'budgets-admin-toolbar.inc' %][% END %] [% IF ( else ) %] -- 1.7.7.3