From cbaf94a82ced7ce05486596ce82094ea792678b5 Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Wed, 12 Sep 2012 15:14:38 +0200 Subject: [PATCH] Bug 8369: Followup: indent sql queries in admin/smart-rules.pl Content-Type: text/plain; charset="utf-8" http://koha-community.org Signed-off-by: Kyle M Hall Signed-off-by: Mason James --- admin/smart-rules.pl | 219 +++++++++++++++++++++++++++++++++++-------------- 1 files changed, 156 insertions(+), 63 deletions(-) diff --git a/admin/smart-rules.pl b/admin/smart-rules.pl index 1f0bee3..07f1919 100755 --- a/admin/smart-rules.pl +++ b/admin/smart-rules.pl @@ -50,39 +50,97 @@ if ($op eq 'delete') { my $categorycode = $input->param('categorycode'); $debug and warn "deleting $1 $2 $branch"; - my $sth_Idelete = $dbh->prepare("delete from issuingrules where branchcode=? and categorycode=? and itemtype=?"); + my $sth_Idelete = $dbh->prepare(" + DELETE FROM issuingrules + WHERE branchcode = ? + AND categorycode = ? + AND itemtype = ? + "); $sth_Idelete->execute($branch, $categorycode, $itemtype); } elsif ($op eq 'delete-branch-cat') { my $categorycode = $input->param('categorycode'); if ($categorycode eq "*") { - my $sth_delete = $dbh->prepare("DELETE FROM circ_rules WHERE branchcode = ?"); + my $sth_delete = $dbh->prepare(" + DELETE FROM circ_rules + WHERE branchcode = ? + "); $sth_delete->execute( $branch ); } else { - my $sth_delete = $dbh->prepare("DELETE FROM borrower_circ_rules - WHERE branchcode = ? - AND categorycode = ?"); + my $sth_delete = $dbh->prepare(" + DELETE FROM borrower_circ_rules + WHERE branchcode = ? + AND categorycode = ? + "); $sth_delete->execute( $branch, $categorycode ); } } elsif ($op eq 'delete-branch-item') { my $itemtype = $input->param('itemtype'); if ($itemtype eq "*") { - my $sth_delete = $dbh->prepare("DELETE FROM circ_rules WHERE branchcode = ?"); + my $sth_delete = $dbh->prepare(" + DELETE FROM circ_rules + WHERE branchcode = ? + "); $sth_delete->execute( $branch); } else { - my $sth_delete = $dbh->prepare("DELETE FROM item_circ_rules - WHERE branchcode = ? - AND itemtype = ?"); + my $sth_delete = $dbh->prepare(" + DELETE FROM item_circ_rules + WHERE branchcode = ? + AND itemtype = ? + "); $sth_delete->execute( $branch, $itemtype ); } } # save the values entered elsif ($op eq 'add') { - my $sth_search = $dbh->prepare('SELECT COUNT(*) AS total FROM issuingrules WHERE branchcode=? AND categorycode=? AND itemtype=?'); - my $sth_insert = $dbh->prepare('INSERT INTO issuingrules (branchcode, categorycode, itemtype, maxissueqty, renewalsallowed, reservesallowed, issuelength, lengthunit, hardduedate, hardduedatecompare, fine, finedays, firstremind, chargeperiod,rentaldiscount, overduefinescap) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)'); - my $sth_update=$dbh->prepare("UPDATE issuingrules SET fine=?, finedays=?, firstremind=?, chargeperiod=?, maxissueqty=?, renewalsallowed=?, reservesallowed=?, issuelength=?, lengthunit = ?, hardduedate=?, hardduedatecompare=?, rentaldiscount=?, overduefinescap=? WHERE branchcode=? AND categorycode=? AND itemtype=?"); - + my $sth_search = $dbh->prepare(' + SELECT COUNT(*) AS total + FROM issuingrules + WHERE branchcode=? + AND categorycode=? + AND itemtype=? + '); + my $sth_insert = $dbh->prepare(' + INSERT INTO issuingrules ( + branchcode, + categorycode, + itemtype, + maxissueqty, + renewalsallowed, + reservesallowed, + issuelength, + lengthunit, + hardduedate, + hardduedatecompare, + fine, + finedays, + firstremind, + chargeperiod, + rentaldiscount, + overduefinescap + ) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) + '); + my $sth_update=$dbh->prepare(" + UPDATE issuingrules + SET fine = ?, + finedays = ?, + firstremind = ?, + chargeperiod = ?, + maxissueqty = ?, + renewalsallowed = ?, + reservesallowed = ?, + issuelength = ?, + lengthunit = ?, + hardduedate = ?, + hardduedatecompare = ?, + rentaldiscount = ?, + overduefinescap = ? + WHERE branchcode = ? + AND categorycode = ? + AND itemtype = ? + "); + my $br = $branch; # branch my $bor = $input->param('categorycode'); # borrower category my $cat = $input->param('itemtype'); # item type @@ -121,15 +179,20 @@ elsif ($op eq "set-branch-defaults") { $maxissueqty = undef if $maxissueqty !~ /^\d+/; $holdallowed =~ s/\s//g; $holdallowed = undef if $holdallowed !~ /^\d+/; - my $sth_search = $dbh->prepare("SELECT count(*) AS total - FROM circ_rules - WHERE branchcode = ?"); - my $sth_insert = $dbh->prepare("INSERT INTO circ_rules - (branchcode, maxissueqty, holdallowed, returnbranch) - VALUES (?, ?, ?, ?)"); - my $sth_update = $dbh->prepare("UPDATE circ_rules - SET maxissueqty = ?, holdallowed = ?, returnbranch = ? - WHERE branchcode = ?"); + my $sth_search = $dbh->prepare(" + SELECT count(*) AS total + FROM circ_rules + WHERE branchcode = ? + "); + my $sth_insert = $dbh->prepare(" + INSERT INTO circ_rules (branchcode, maxissueqty, holdallowed, returnbranch) + VALUES (?, ?, ?, ?) + "); + my $sth_update = $dbh->prepare(" + UPDATE circ_rules + SET maxissueqty = ?, holdallowed = ?, returnbranch = ? + WHERE branchcode = ? + "); $sth_search->execute( $branch ); my $res = $sth_search->fetchrow_hashref(); @@ -146,15 +209,20 @@ elsif ($op eq "add-branch-cat") { $maxissueqty = undef if $maxissueqty !~ /^\d+/; if ($categorycode eq "*") { - my $sth_search = $dbh->prepare("SELECT count(*) AS total - FROM circ_rules - WHERE branchcode = ?"); - my $sth_insert = $dbh->prepare("INSERT INTO circ_rules - (branchcode, maxissueqty) - VALUES (?, ?)"); - my $sth_update = $dbh->prepare("UPDATE circ_rules - SET maxissueqty = ? - WHERE branchcode = ?"); + my $sth_search = $dbh->prepare(" + SELECT count(*) AS total + FROM circ_rules + WHERE branchcode = ? + "); + my $sth_insert = $dbh->prepare(" + INSERT INTO circ_rules (branchcode, maxissueqty) + VALUES (?, ?) + "); + my $sth_update = $dbh->prepare(" + UPDATE circ_rules + SET maxissueqty = ? + WHERE branchcode = ? + "); $sth_search->execute( $branch ); my $res = $sth_search->fetchrow_hashref(); @@ -164,17 +232,22 @@ elsif ($op eq "add-branch-cat") { $sth_insert->execute( $maxissueqty, $branch ); } } else { - my $sth_search = $dbh->prepare("SELECT count(*) AS total - FROM borrower_circ_rules - WHERE branchcode = ? - AND categorycode = ?"); - my $sth_insert = $dbh->prepare("INSERT INTO borrower_circ_rules - (branchcode, categorycode, maxissueqty) - VALUES (?, ?, ?)"); - my $sth_update = $dbh->prepare("UPDATE borrower_circ_rules - SET maxissueqty = ? - WHERE branchcode = ? - AND categorycode = ?"); + my $sth_search = $dbh->prepare(" + SELECT count(*) AS total + FROM borrower_circ_rules + WHERE branchcode = ? + AND categorycode = ? + "); + my $sth_insert = $dbh->prepare(" + INSERT INTO borrower_circ_rules + (branchcode, categorycode, maxissueqty) + VALUES (?, ?, ?)"); + my $sth_update = $dbh->prepare(" + UPDATE borrower_circ_rules + SET maxissueqty = ? + WHERE branchcode = ? + AND categorycode = ? + "); $sth_search->execute( $branch, $categorycode ); my $res = $sth_search->fetchrow_hashref(); if ($res->{total}) { @@ -192,15 +265,21 @@ elsif ($op eq "add-branch-item") { $holdallowed = undef if $holdallowed !~ /^\d+/; if ($itemtype eq "*") { - my $sth_search = $dbh->prepare("SELECT count(*) AS total - FROM circ_rules - WHERE branchcode = ?"); - my $sth_insert = $dbh->prepare("INSERT INTO circ_rules - (branchcode, holdallowed, returnbranch) - VALUES (?, ?, ?)"); - my $sth_update = $dbh->prepare("UPDATE circ_rules - SET holdallowed = ?, returnbranch = ? - WHERE branchcode = ?"); + my $sth_search = $dbh->prepare(" + SELECT count(*) AS total + FROM circ_rules + WHERE branchcode = ? + "); + my $sth_insert = $dbh->prepare(" + INSERT INTO circ_rules + (branchcode, holdallowed, returnbranch) + VALUES (?, ?, ?) + "); + my $sth_update = $dbh->prepare(" + UPDATE circ_rules + SET holdallowed = ?, returnbranch = ? + WHERE branchcode = ? + "); $sth_search->execute( $branch ); my $res = $sth_search->fetchrow_hashref(); @@ -210,16 +289,22 @@ elsif ($op eq "add-branch-item") { $sth_insert->execute($branch, $holdallowed, $returnbranch); } } else { - my $sth_search = $dbh->prepare("SELECT count(*) AS total - FROM item_circ_rules - WHERE itemtype = ?"); - my $sth_insert = $dbh->prepare("INSERT INTO item_circ_rules - (branchcode, itemtype, holdallowed, returnbranch) - VALUES (?, ?, ?, ?)"); - my $sth_update = $dbh->prepare("UPDATE item_circ_rules - SET holdallowed = ?, returnbranch = ? - WHERE branchcode = ? - AND itemtype = ?"); + my $sth_search = $dbh->prepare(" + SELECT count(*) AS total + FROM item_circ_rules + WHERE itemtype = ? + "); + my $sth_insert = $dbh->prepare(" + INSERT INTO item_circ_rules + (branchcode, itemtype, holdallowed, returnbranch) + VALUES (?, ?, ?, ?) + "); + my $sth_update = $dbh->prepare(" + UPDATE item_circ_rules + SET holdallowed = ?, returnbranch = ? + WHERE branchcode = ? + AND itemtype = ? + "); $sth_search->execute($itemtype); my $res = $sth_search->fetchrow_hashref(); if ($res->{total}) { @@ -240,7 +325,11 @@ for my $thisbranch (sort { $branches->{$a}->{branchname} cmp $branches->{$b}->{b }; } -my $sth=$dbh->prepare("SELECT description,categorycode FROM categories ORDER BY description"); +my $sth=$dbh->prepare(" + SELECT description,categorycode + FROM categories + ORDER BY description +"); $sth->execute; my @category_loop; while (my $data=$sth->fetchrow_hashref){ @@ -248,7 +337,11 @@ while (my $data=$sth->fetchrow_hashref){ } $sth->finish; -$sth=$dbh->prepare("SELECT description,itemtype FROM itemtypes ORDER BY description"); +$sth=$dbh->prepare(" + SELECT description,itemtype + FROM itemtypes + ORDER BY description +"); $sth->execute; # $i=0; my @row_loop; -- 1.7.2.5