insert_cities_to_db.pl
Copying Source is Forbidden
97 lines of code
1
#!/usr/local/bin/perl
2
3
# must have's!
4
use strict;
5
use warnings;
6
use CGI::Carp qw(fatalsToBrowser);
7
use DBI;
8
use URI::Escape;
9
10
use lib "/var/www/html/Pm";
11
12
use Bc_chef qw(cookie_get);
13
use Bc_sql qw(
14
get_constant
15
sql_execute
16
user_exists
17
$QUERY_PAGE
18
$QUERY_UID
19
$LOGGEDIN
20
get_country_name
21
get_country_id_byName
22
23
$DB
24
);
25
use User qw(isUserSuperAdmin $USER_DATA);
26
use Redir qw(error_redir notice_redir);
27
use Html qw(pre_html_header);
28
29
my $DEBUG = 0;
30
if ((not user_exists($LOGGEDIN) or
31
banned($LOGGEDIN)) and
32
not isUserSuperAdmin($LOGGEDIN)) {
33
my $msg = "Access Denied";
34
if ($DEBUG) { $msg .= " (insert_cities_to_db.pl)"; }
35
print error_redir("/", $msg);
36
37
exit 1;
38
}
39
40
print pre_html_header({type=>"text/plain"});
41
42
my $banana = read_text("USEFUL/cities.txt");
43
my @cities = split("\n", $banana);
44
my $currCountryName = get_country_name("1-1");
45
my $currCountryCode = get_country_id_byName($currCountryName);
46
my $cityNum = 1;
47
48
my $totalSuccessCount = 0;
49
my $totalFailureCount = 0;
50
51
for (my $i = 0; $i < @cities; $i++) {
52
my $cityData = $cities[$i];
53
my @data = split(',', $cityData);
54
my $output = "";
55
# now, it goes like this: city, city_ascii, lat, lng, pop, country, iso2, iso3, province
56
# we really only want city name and country. we'll use city and not city_ascii
57
# element 0 is the city
58
# element 5 is the country
59
my $countryID = get_country_id_byName($data[5]);
60
61
if ($countryID) {
62
if ($countryID ne $currCountryCode) {
63
$cityNum = 1;
64
$currCountryCode = $countryID;
65
$output .= "$data[5] ($currCountryCode*) -$data[0]\n";
66
} else {
67
$output .= "$data[5] ($currCountryCode) -$data[0]\n";
68
$cityNum++;
69
}
70
71
my $sql = "insert into cities values (";
72
$sql .= " NULL, " . $DB->quote($data[0]) . ", " . $DB->quote("$currCountryCode-$cityNum");
73
$sql .= ")";
74
if (sql_execute($sql, "insert_cities_to_db_db.pl")) {
75
$totalSuccessCount++;
76
} else {
77
$totalFailureCount++;
78
}
79
80
} else {
81
if ($currCountryCode ne $countryID) {
82
$output .= "COUNTRY ($data[5]) NOT IN DB!\n";
83
}
84
}
85
86
if ($currCountryCode ne $countryID) {
87
$currCountryCode = $countryID;
88
}
89
90
print $output;
91
}
92
93
94
print "cities entered: $totalSuccessCount\n";
95
print "cities NOT entered: $totalFailureCount\n";
96
97
exit 1;