Attachment #4825 for bug #5433

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




    my $dbh      = C4::Context->dbh;
    my $sth      =
      $dbh->prepare(
        "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], $data[2], $data[3];
}




    my $dbh   = C4::Context->dbh;
    my $city_arr = $dbh->selectall_arrayref(
        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{},
        };
    }





	#---- 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_state,city_zipcode,city_country from cities where  cityid=?");
		$sth->execute($cityid);
		$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'});
# END $OP eq ADD_FORM
################## ADD_VALIDATE ##################################
# called by add_form, used to insert/modify data in DB

 	my $sth;
	
	if ($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_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;

# 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_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

Lines 4370-4375 if (C4::Context->preference("Version") < TransformToNum($DBversion)) { Link Here

(-)a/installer/data/mysql/updatedatabase.pl (+8 lines)
4370	SetVersion($DBversion);	4370	SetVersion($DBversion);
4371	}	4371	}
4372		4372
		4373	$DBversion = "3.05.00.XXX";
		4374	if (C4::Context->preference("Version") < TransformToNum($DBversion)) {
		4375	$dbh->do("ALTER TABLE `cities` ADD `city_state` VARCHAR( 100 ) NULL DEFAULT NULL AFTER `city_name`;");
		4376	$dbh->do("ALTER TABLE `cities` ADD `city_country` VARCHAR( 100 ) NULL DEFAULT NULL AFTER `city_zipcode`;");
		4377	print "Add state and country to cities table corresponding to new columns in borrowers\n";
		4378	SetVersion($DBversion);
		4379	}
		4380
4373	=head1 FUNCTIONS	4381	=head1 FUNCTIONS
4374		4382
4375	=head2 DropAllForeignKeys($table)	4383	=head2 DropAllForeignKeys($table)

Lines 4-12 Link Here

(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/cities.tt (-6 / +24 lines)
4	<script type="text/javascript" src="[% themelang %]/lib/jquery/plugins/jquery.tablesorter.min.js"></script>	4	<script type="text/javascript" src="[% themelang %]/lib/jquery/plugins/jquery.tablesorter.min.js"></script>
5	<script type="text/javascript" src="[% themelang %]/lib/jquery/plugins/jquery.tablesorter.pager.js"></script>	5	<script type="text/javascript" src="[% themelang %]/lib/jquery/plugins/jquery.tablesorter.pager.js"></script>
6	<script type="text/javascript" id="js">$(document).ready(function() {	6	<script type="text/javascript" id="js">$(document).ready(function() {
		7	$.tablesorter.defaults.widgets = ['zebra'];
7	$("#table_cities").tablesorter({	8	$("#table_cities").tablesorter({
8	sortList: [[1,0]],	9	sortList: [[1,0]],
9	headers: { 3: { sorter: false},4: { sorter: false}}	10	headers: { 5: { sorter: false},6: { sorter: false}}
10	}).tablesorterPager({container: $("#pagertable_cities"),positionFixed: false,size: 20});	11	}).tablesorterPager({container: $("#pagertable_cities"),positionFixed: false,size: 20});
11	}); </script>	12	}); </script>
12	<script type="text/javascript">	13	<script type="text/javascript">
Lines 62-73 Link Here
62	<span class="label">City ID: </span>[% cityid %]</li>	63	<span class="label">City ID: </span>[% cityid %]</li>
63	[% END %]	64	[% END %]
64	<li>	65	<li>
65	<label for="city_name">City, State: </label>	66	<label for="city_name" class="required" title="required">City: </label>
66	<input type="text" name="city_name" id="city_name" size="80" maxlength="100" value="[% city_name \|html %]" />	67	<input type="text" name="city_name" id="city_name" size="80" maxlength="100" value="[% city_name \|html %]" />
67	</li>	68	</li>
		69	<li>
		70	<label for="city_state">State: </label>
		71	<input type="text" name="city_state" id="city_state" size="80" maxlength="100" value="[% city_state \|html %]" />
		72	</li>
68	<li>	73	<li>
69	<label for="city_zipcode">Zip/Postal code: </label>	74	<label for="city_zipcode" class="required" title="required">Zip/Postal code: </label>
70	<input type="text" name="city_zipcode" id="city_zipcode" size="20" maxlength="20" value="[% city_zipcode %]" />	75	<input type="text" name="city_zipcode" id="city_zipcode" size="20" maxlength="20" value="[% city_zipcode %]" />
		76	</li>
		77	<li>
		78	<label for="city_country">Country: </label>
		79	<input type="text" name="city_country" id="city_country" size="80" maxlength="100" value="[% city_country \|html %]" />
71	</li></ol></fieldset>	80	</li></ol></fieldset>
72		81
73	<fieldset class="action">	82	<fieldset class="action">
Lines 83-94 Link Here
83	<tr><th>City id</th>	92	<tr><th>City id</th>
84	<td>[% cityid %]</td>	93	<td>[% cityid %]</td>
85	</tr>	94	</tr>
86	<tr><th>City, State</th>	95	<tr><th>City</th>
87	<td>[% city_name %]</td>	96	<td>[% city_name %]</td>
88	</tr>	97	</tr>
		98	<tr><th>State</th>
		99	<td>[% city_state %]</td>
		100	</tr>
89	<tr><th>Zip/Postal code</th>	101	<tr><th>Zip/Postal code</th>
90	<td>[% city_zipcode %]</td>	102	<td>[% city_zipcode %]</td>
91	</tr>	103	</tr>
		104	<tr><th>Country</th>
		105	<td>[% city_country %]</td>
		106	</tr>
92	</table>	107	</table>
93	<form action="[% script_name %]" method="post">	108	<form action="[% script_name %]" method="post">
94	<input type="hidden" name="op" value="delete_confirmed" />	109	<input type="hidden" name="op" value="delete_confirmed" />
Lines 135-142 Link Here
135	<table id="table_cities">	150	<table id="table_cities">
136	<thead>	151	<thead>
137	<th>City ID</th>	152	<th>City ID</th>
138	<th>City, State</th>	153	<th>City</th>
		154	<th>State</th>
139	<th>Zip/Postal code</th>	155	<th>Zip/Postal code</th>
		156	<th>Country</th>
140	<th> </th>	157	<th> </th>
141	<th> </th>	158	<th> </th>
142	</thead>	159	</thead>
Lines 148-155 Link Here
148	[% END %]	165	[% END %]
149	<td>[% loo.cityid %]</td>	166	<td>[% loo.cityid %]</td>
150	<td>[% loo.city_name %]</td>	167	<td>[% loo.city_name %]</td>
		168	<td>[% loo.city_state %]</td>
151	<td>[% loo.city_zipcode %]</td>	169	<td>[% loo.city_zipcode %]</td>
152		170	<td>[% loo.city_country %]</td>
153	<td><a href="[% loo.script_name %]?op=add_form&cityid=[% loo.cityid %]">Edit</a></td>	171	<td><a href="[% loo.script_name %]?op=add_form&cityid=[% loo.cityid %]">Edit</a></td>
154	<td><a href="[% loo.script_name %]?op=delete_confirm&cityid=[% loo.cityid %]">Delete</a></td>	172	<td><a href="[% loo.script_name %]?op=delete_confirm&cityid=[% loo.cityid %]">Delete</a></td>
155	</tr>	173	</tr>




            $("#guarantorsearch").val("Set to Patron");
        });
        $("#select_city").change(function(){
            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;
        });
    });


        <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 %]|[% city_loo.city_state %]|[% city_loo.city_country %]" selected="selected">
            [% ELSE %]
            <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_state %] [% city_loo.city_zipcode %]
            </option>
        [% END %]
        </select>
- 

Return to bug 5433

Lines 1304-1313 sub getzipnamecity { Link Here

(-)a/C4/Members.pm (-3 / +5 lines)
1304	my $dbh = C4::Context->dbh;	1304	my $dbh = C4::Context->dbh;
1305	my $sth =	1305	my $sth =
1306	$dbh->prepare(	1306	$dbh->prepare(
1307	"select city_name,city_zipcode from cities where cityid=? ");	1307	"select city_name,city_state,city_zipcode,city_country from cities where cityid=? ");
1308	$sth->execute($cityid);	1308	$sth->execute($cityid);
1309	my @data = $sth->fetchrow;	1309	my @data = $sth->fetchrow;
1310	return $data[0], $data[1];	1310	return $data[0], $data[1], $data[2], $data[3];
1311	}	1311	}
1312		1312
1313		1313
Lines 1627-1639 sub GetCities { Link Here
1627		1627
1628	my $dbh = C4::Context->dbh;	1628	my $dbh = C4::Context->dbh;
1629	my $city_arr = $dbh->selectall_arrayref(	1629	my $city_arr = $dbh->selectall_arrayref(
1630	q\|SELECT cityid,city_zipcode,city_name FROM cities ORDER BY city_name\|,	1630	q\|SELECT cityid,city_zipcode,city_name,city_state,city_country FROM cities ORDER BY city_name\|,
1631	{ Slice => {} });	1631	{ Slice => {} });
1632	if ( @{$city_arr} ) {	1632	if ( @{$city_arr} ) {
1633	unshift @{$city_arr}, {	1633	unshift @{$city_arr}, {
1634	city_zipcode => q{},	1634	city_zipcode => q{},
1635	city_name => q{},	1635	city_name => q{},
1636	cityid => q{},	1636	cityid => q{},
		1637	city_state => q{},
		1638	city_country => q{},
1637	};	1639	};
1638	}	1640	}
1639		1641

Lines 58-71 if ($op eq 'add_form') { Link Here

(-)a/admin/cities.pl (-7 / +11 lines)
58	#---- if primkey exists, it's a modify action, so read values to modify...	58	#---- if primkey exists, it's a modify action, so read values to modify...
59	my $data;	59	my $data;
60	if ($cityid) {	60	if ($cityid) {
61	my $sth=$dbh->prepare("select cityid,city_name,city_zipcode from cities where cityid=?");	61	my $sth=$dbh->prepare("select cityid,city_name,city_state,city_zipcode,city_country from cities where cityid=?");
62	$sth->execute($cityid);	62	$sth->execute($cityid);
63	$data=$sth->fetchrow_hashref;	63	$data=$sth->fetchrow_hashref;
64	}	64	}
65		65
66	$template->param(	66	$template->param(
67	city_name => $data->{'city_name'},	67	city_name => $data->{'city_name'},
68	city_zipcode => $data->{'city_zipcode'});	68	city_state => $data->{'city_state'},
		69	city_zipcode => $data->{'city_zipcode'},
		70	city_country => $data->{'city_country'});
69	# END $OP eq ADD_FORM	71	# END $OP eq ADD_FORM
70	################## ADD_VALIDATE ##################################	72	################## ADD_VALIDATE ##################################
71	# called by add_form, used to insert/modify data in DB	73	# called by add_form, used to insert/modify data in DB
Lines 73-84 if ($op eq 'add_form') { Link Here
73	my $sth;	75	my $sth;
74		76
75	if ($input->param('cityid') ){	77	if ($input->param('cityid') ){
76	$sth=$dbh->prepare("UPDATE cities SET city_name=?,city_zipcode=? WHERE cityid=?");	78	$sth=$dbh->prepare("UPDATE cities SET city_name=?,city_state=?,city_zipcode=?,city_country=? WHERE cityid=?");
77	$sth->execute($input->param('city_name'),$input->param('city_zipcode'),$input->param('cityid'));	79	$sth->execute($input->param('city_name'),$input->param('city_state'),$input->param('city_zipcode'),$input->param('city_country'),$input->param('cityid'));
78	}	80	}
79	else{	81	else{
80	$sth=$dbh->prepare("INSERT INTO cities (city_name,city_zipcode) values (?,?)");	82	$sth=$dbh->prepare("INSERT INTO cities (city_name,city_state,city_zipcode,city_country) values (?,?,?,?)");
81	$sth->execute($input->param('city_name'),$input->param('city_zipcode'));	83	$sth->execute($input->param('city_name'),$input->param('city_state'),$input->param('city_zipcode'),$input->param('city_country'));
82	}	84	}
83	print $input->redirect($script_name);	85	print $input->redirect($script_name);
84	exit;	86	exit;
Lines 86-97 if ($op eq 'add_form') { Link Here
86	# called by default form, used to confirm deletion of data in DB	88	# called by default form, used to confirm deletion of data in DB
87	} elsif ($op eq 'delete_confirm') {	89	} elsif ($op eq 'delete_confirm') {
88	$template->param(delete_confirm => 1);	90	$template->param(delete_confirm => 1);
89	my $sth=$dbh->prepare("select cityid,city_name,city_zipcode from cities where cityid=?");	91	my $sth=$dbh->prepare("select cityid,city_name,city_state,city_zipcode,city_country from cities where cityid=?");
90	$sth->execute($cityid);	92	$sth->execute($cityid);
91	my $data=$sth->fetchrow_hashref;	93	my $data=$sth->fetchrow_hashref;
92	$template->param(	94	$template->param(
93	city_name => $data->{'city_name'},	95	city_name => $data->{'city_name'},
		96	city_state => $data->{'city_state'},
94	city_zipcode => $data->{'city_zipcode'},	97	city_zipcode => $data->{'city_zipcode'},
		98	city_country => $data->{'city_country'},
95	);	99	);
96	################## DELETE_CONFIRMED ##################################	100	################## DELETE_CONFIRMED ##################################
97	# called by delete_confirm, used to effectively confirm deletion of data in DB	101	# called by delete_confirm, used to effectively confirm deletion of data in DB

Lines 16-25 Link Here

(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/members/memberentrygen.tt (-5 / +6 lines)
16	$("#guarantorsearch").val("Set to Patron");	16	$("#guarantorsearch").val("Set to Patron");
17	});	17	});
18	$("#select_city").change(function(){	18	$("#select_city").change(function(){
19	var myRegEx=new RegExp(/(.)\\|(.)/);	19	var myRegEx=new RegExp(/(.)\\|(.)\\|(.)\\|(.)/);
20	document.form.select_city.value.match(myRegEx);	20	document.form.select_city.value.match(myRegEx);
21	document.form.zipcode.value=RegExp.$1;	21	document.form.zipcode.value=RegExp.$1;
22	document.form.city.value=RegExp.$2;	22	document.form.city.value=RegExp.$2;
		23	document.form.state.value=RegExp.$3;
		24	document.form.country.value=RegExp.$4;
23	});	25	});
24	});	26	});
25		27
Lines 425-435 Link Here
425	<select id="select_city" name="select_city">	427	<select id="select_city" name="select_city">
426	[% FOREACH city_loo IN city_loop %]	428	[% FOREACH city_loo IN city_loop %]
427	[% IF ( city_loo.selected ) %]	429	[% IF ( city_loo.selected ) %]
428	<option value="[% city_loo.city_zipcode %]\|[% city_loo.city_name %]" selected="selected">	430	<option value="[% city_loo.city_zipcode %]\|[% city_loo.city_name %]\|[% city_loo.city_state %]\|[% city_loo.city_country %]" selected="selected">
429	[% ELSE %]	431	[% ELSE %]
430	<option value="[% city_loo.city_zipcode %]\|[% city_loo.city_name %]">	432	<option value="[% city_loo.city_zipcode %]\|[% city_loo.city_name %]\|[% city_loo.city_state %]\|[% city_loo.city_country %]">
431	[% END %]	433	[% END %]
432	[% city_loo.city_name %] [% city_loo.city_zipcode %]	434	[% city_loo.city_name %] [% city_loo.city_state %] [% city_loo.city_zipcode %]
433	</option>	435	</option>
434	[% END %]	436	[% END %]
435	</select>	437	</select>
436	-