From 2860f45c8a6cceec3f8d0bcf17c3f15faa005135 Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Thu, 16 Feb 2012 12:54:12 +0100 Subject: [PATCH] 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 | 7 +++- installer/data/mysql/updatedatabase.pl | 25 ++++++++++++++++ .../prog/en/modules/admin/aqbudgetperiods.tt | 6 ++++ .../prog/en/modules/admin/aqbudgets.tt | 5 +++ 6 files changed, 65 insertions(+), 16 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 c5f99e3..96f8de8 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", @@ -248,20 +249,22 @@ if ($op eq 'add_form') { @budgetusersid = split(':', $budget_hash->{'budget_users_ids'}); } + my $r; if ( defined $$budget_hash{budget_id} ) { if (CanUserModifyBudget($borrowernumber, $budget_hash->{budget_id}, $staffflags) ) { - ModBudget( $budget_hash ); + $r = ModBudget( $budget_hash ); ModBudgetUsers($budget_hash->{budget_id}, @budgetusersid); } else { $template->param(error_not_authorised_to_modify => 1); } } else { - AddBudget( $budget_hash ); + $r = AddBudget( $budget_hash ); ModBudgetUsers($budget_hash->{budget_id}, @budgetusersid); } + $sql_error = 1 unless $r; } my $branches = GetBranches(); $template->param( @@ -364,6 +367,7 @@ if ($op eq 'add_form') { period_alloc_total => $period_alloc_total, base_spent_total => $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 69304e1..2b7da21 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -2644,7 +2644,9 @@ CREATE TABLE `aqbudgets` ( -- information related to Funds `sort2_authcat` varchar(80) default NULL, -- second statistical category for this fund `budget_owner_id` int(11) default NULL, -- borrowernumber of the person who owns this fund (borrowers.borrowernumber) `budget_permission` int(1) default '0', -- level of permission for this fund (used only by the owner, only by the library, or anyone) - 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; -- @@ -2680,7 +2682,8 @@ CREATE TABLE `aqbudgetperiods` ( -- information related to Budgets `budget_period_locked` tinyint(1) default NULL, -- whether this budget is locked or not (1 for yes, 0 for no) `sort1_authcat` varchar(10) default NULL, -- statistical category for this budget `sort2_authcat` varchar(10) default NULL, -- second statistical category for this budget - 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 ed0c674..ef98c67 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -5635,6 +5635,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 TableExists($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 deb5442..0e50bd2 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/aqbudgetperiods.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/aqbudgetperiods.tt @@ -151,6 +151,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 ba308b6..2746220 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/aqbudgets.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/aqbudgets.tt @@ -206,6 +206,11 @@
+[% IF ( sql_error ) %] +
+ This fund name (or code) already exists +
+[% END %] [% UNLESS ( delete_confirm ) %][% INCLUDE 'budgets-admin-toolbar.inc' %][% END %] -- 1.7.7.3