From: Owen Leonard <oleon...@myacpl.org> - Updates cities management (admin/cities.pl) to record a separate state and country field. - Updates the cities table with these new columns - Modifies the patron entry form to populate city, state, zip and country when the user chooses a prepopulated city entry
Signed-off-by: Nicole C. Engard <neng...@bywatersolutions.com> --- C4/Members.pm | 8 +++-- admin/cities.pl | 18 +++++++---- installer/data/mysql/updatedatabase.pl | 8 +++++ .../intranet-tmpl/prog/en/modules/admin/cities.tt | 30 ++++++++++++++++---- .../prog/en/modules/members/memberentrygen.tt | 10 ++++-- 5 files changed, 54 insertions(+), 20 deletions(-) diff --git a/C4/Members.pm b/C4/Members.pm index 2ce3030..ce694e5 100644 --- a/C4/Members.pm +++ b/C4/Members.pm @@ -1304,10 +1304,10 @@ sub getzipnamecity { my $dbh = C4::Context->dbh; my $sth = $dbh->prepare( - "select city_name,city_zipcode from cities where cityid=? "); + "select city_name,city_state,city_zipcode,city_country from cities where cityid=? "); $sth->execute($cityid); my @data = $sth->fetchrow; - return $data[0], $data[1]; + return $data[0], $data[1], $data[2], $data[3]; } @@ -1627,13 +1627,15 @@ sub GetCities { my $dbh = C4::Context->dbh; my $city_arr = $dbh->selectall_arrayref( - q|SELECT cityid,city_zipcode,city_name FROM cities ORDER BY city_name|, + q|SELECT cityid,city_zipcode,city_name,city_state,city_country FROM cities ORDER BY city_name|, { Slice => {} }); if ( @{$city_arr} ) { unshift @{$city_arr}, { city_zipcode => q{}, city_name => q{}, cityid => q{}, + city_state => q{}, + city_country => q{}, }; } diff --git a/admin/cities.pl b/admin/cities.pl index 58e33ae..0303a48 100755 --- a/admin/cities.pl +++ b/admin/cities.pl @@ -58,14 +58,16 @@ if ($op eq 'add_form') { #---- if primkey exists, it's a modify action, so read values to modify... my $data; if ($cityid) { - my $sth=$dbh->prepare("select cityid,city_name,city_zipcode from cities where cityid=?"); + my $sth=$dbh->prepare("select cityid,city_name,city_state,city_zipcode,city_country from cities where cityid=?"); $sth->execute($cityid); $data=$sth->fetchrow_hashref; } $template->param( city_name => $data->{'city_name'}, - city_zipcode => $data->{'city_zipcode'}); + city_state => $data->{'city_state'}, + city_zipcode => $data->{'city_zipcode'}, + city_country => $data->{'city_country'}); # END $OP eq ADD_FORM ################## ADD_VALIDATE ################################## # called by add_form, used to insert/modify data in DB @@ -73,12 +75,12 @@ if ($op eq 'add_form') { my $sth; if ($input->param('cityid') ){ - $sth=$dbh->prepare("UPDATE cities SET city_name=?,city_zipcode=? WHERE cityid=?"); - $sth->execute($input->param('city_name'),$input->param('city_zipcode'),$input->param('cityid')); + $sth=$dbh->prepare("UPDATE cities SET city_name=?,city_state=?,city_zipcode=?,city_country=? WHERE cityid=?"); + $sth->execute($input->param('city_name'),$input->param('city_state'),$input->param('city_zipcode'),$input->param('city_country'),$input->param('cityid')); } else{ - $sth=$dbh->prepare("INSERT INTO cities (city_name,city_zipcode) values (?,?)"); - $sth->execute($input->param('city_name'),$input->param('city_zipcode')); + $sth=$dbh->prepare("INSERT INTO cities (city_name,city_state,city_zipcode,city_country) values (?,?,?,?)"); + $sth->execute($input->param('city_name'),$input->param('city_state'),$input->param('city_zipcode'),$input->param('city_country')); } print $input->redirect($script_name); exit; @@ -86,12 +88,14 @@ if ($op eq 'add_form') { # called by default form, used to confirm deletion of data in DB } elsif ($op eq 'delete_confirm') { $template->param(delete_confirm => 1); - my $sth=$dbh->prepare("select cityid,city_name,city_zipcode from cities where cityid=?"); + my $sth=$dbh->prepare("select cityid,city_name,city_state,city_zipcode,city_country from cities where cityid=?"); $sth->execute($cityid); my $data=$sth->fetchrow_hashref; $template->param( city_name => $data->{'city_name'}, + city_state => $data->{'city_state'}, city_zipcode => $data->{'city_zipcode'}, + city_country => $data->{'city_country'}, ); ################## DELETE_CONFIRMED ################################## # called by delete_confirm, used to effectively confirm deletion of data in DB diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index 9cbc8db..65fb9f6 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -4370,6 +4370,14 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) { SetVersion($DBversion); } +$DBversion = "3.05.00.XXX"; +if (C4::Context->preference("Version") < TransformToNum($DBversion)) { + $dbh->do("ALTER TABLE `cities` ADD `city_state` VARCHAR( 100 ) NULL DEFAULT NULL AFTER `city_name`;"); + $dbh->do("ALTER TABLE `cities` ADD `city_country` VARCHAR( 100 ) NULL DEFAULT NULL AFTER `city_zipcode`;"); + print "Add state and country to cities table corresponding to new columns in borrowers\n"; + SetVersion($DBversion); +} + =head1 FUNCTIONS =head2 DropAllForeignKeys($table) diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/cities.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/cities.tt index 2e9bdd2..f286b7c 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/cities.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/cities.tt @@ -4,9 +4,10 @@ <script type="text/javascript" src="[% themelang %]/lib/jquery/plugins/jquery.tablesorter.min.js"></script> <script type="text/javascript" src="[% themelang %]/lib/jquery/plugins/jquery.tablesorter.pager.js"></script> <script type="text/javascript" id="js">$(document).ready(function() { + $.tablesorter.defaults.widgets = ['zebra']; $("#table_cities").tablesorter({ sortList: [[1,0]], - headers: { 3: { sorter: false},4: { sorter: false}} + headers: { 5: { sorter: false},6: { sorter: false}} }).tablesorterPager({container: $("#pagertable_cities"),positionFixed: false,size: 20}); }); </script> <script type="text/javascript"> @@ -62,12 +63,20 @@ <span class="label">City ID: </span>[% cityid %]</li> [% END %] <li> - <label for="city_name">City, State: </label> + <label for="city_name" class="required" title="required">City: </label> <input type="text" name="city_name" id="city_name" size="80" maxlength="100" value="[% city_name |html %]" /> </li> + <li> + <label for="city_state">State: </label> + <input type="text" name="city_state" id="city_state" size="80" maxlength="100" value="[% city_state |html %]" /> + </li> <li> - <label for="city_zipcode">Zip/Postal code: </label> + <label for="city_zipcode" class="required" title="required">Zip/Postal code: </label> <input type="text" name="city_zipcode" id="city_zipcode" size="20" maxlength="20" value="[% city_zipcode %]" /> + </li> + <li> + <label for="city_country">Country: </label> + <input type="text" name="city_country" id="city_country" size="80" maxlength="100" value="[% city_country |html %]" /> </li></ol></fieldset> <fieldset class="action"> @@ -83,12 +92,18 @@ <tr><th>City id</th> <td>[% cityid %]</td> </tr> - <tr><th>City, State</th> + <tr><th>City</th> <td>[% city_name %]</td> </tr> + <tr><th>State</th> + <td>[% city_state %]</td> + </tr> <tr><th>Zip/Postal code</th> <td>[% city_zipcode %]</td> </tr> + <tr><th>Country</th> + <td>[% city_country %]</td> + </tr> </table> <form action="[% script_name %]" method="post"> <input type="hidden" name="op" value="delete_confirmed" /> @@ -135,8 +150,10 @@ <table id="table_cities"> <thead> <th>City ID</th> - <th>City, State</th> + <th>City</th> + <th>State</th> <th>Zip/Postal code</th> + <th>Country</th> <th> </th> <th> </th> </thead> @@ -148,8 +165,9 @@ [% END %] <td>[% loo.cityid %]</td> <td>[% loo.city_name %]</td> + <td>[% loo.city_state %]</td> <td>[% loo.city_zipcode %]</td> - + <td>[% loo.city_country %]</td> <td><a href="[% loo.script_name %]?op=add_form&cityid=[% loo.cityid %]">Edit</a></td> <td><a href="[% loo.script_name %]?op=delete_confirm&cityid=[% loo.cityid %]">Delete</a></td> </tr> diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/members/memberentrygen.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/members/memberentrygen.tt index f96c27f..236295d 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/members/memberentrygen.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/members/memberentrygen.tt @@ -16,10 +16,12 @@ $("#guarantorsearch").val("Set to Patron"); }); $("#select_city").change(function(){ - var myRegEx=new RegExp(/(.*)\|(.*)/); + var myRegEx=new RegExp(/(.*)\|(.*)\|(.*)\|(.*)/); document.form.select_city.value.match(myRegEx); document.form.zipcode.value=RegExp.$1; document.form.city.value=RegExp.$2; + document.form.state.value=RegExp.$3; + document.form.country.value=RegExp.$4; }); }); @@ -425,11 +427,11 @@ <select id="select_city" name="select_city"> [% FOREACH city_loo IN city_loop %] [% IF ( city_loo.selected ) %] - <option value="[% city_loo.city_zipcode %]|[% city_loo.city_name %]" selected="selected"> + <option value="[% city_loo.city_zipcode %]|[% city_loo.city_name %]|[% city_loo.city_state %]|[% city_loo.city_country %]" selected="selected"> [% ELSE %] - <option value="[% city_loo.city_zipcode %]|[% city_loo.city_name %]"> + <option value="[% city_loo.city_zipcode %]|[% city_loo.city_name %]|[% city_loo.city_state %]|[% city_loo.city_country %]"> [% END %] - [% city_loo.city_name %] [% city_loo.city_zipcode %] + [% city_loo.city_name %] [% city_loo.city_state %] [% city_loo.city_zipcode %] </option> [% END %] </select> -- 1.7.2.3 _______________________________________________ Koha-patches mailing list Koha-patches@lists.koha-community.org http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-patches website : http://www.koha-community.org/ git : http://git.koha-community.org/ bugs : http://bugs.koha-community.org/