source: trunk/freethepostcode.org/html/ruby/stats.rbx @ 731

Last change on this file since 731 was 731, checked in by Dominic Hargreaves, 10 years ago

externalise password here too

  • Property svn:executable set to *
File size: 1.8 KB
Line 
1#!/usr/bin/ruby
2
3require 'mysql'
4require 'secrets'
5
6MYSQL_SERVER = '127.0.0.1'
7MYSQL_USER = 'postcode'
8MYSQL_PASS = get_password
9MYSQL_DATABASE = 'postcode'
10
11def mysql_error(e)
12  print "Error code: ", e.errno, "\n"
13  print "Error message: ", e.error, "\n"
14end
15
16def get_connection
17  begin
18    return Mysql.real_connect(MYSQL_SERVER, MYSQL_USER, MYSQL_PASS, MYSQL_DATABASE)
19     
20  rescue MysqlError => e   
21    mysql_error(e)
22  end
23     
24end
25
26connection = get_connection
27
28puts '<html>'
29puts '<script type="text/javascript" src="sorttable.js"></script>'
30
31if connection
32  sql = 'select count(*) as c from (select * from codes where confirmed = 1 group by part1, part2) as a;'
33  res = connection.query(sql)
34  res.each_hash do |row|
35    puts 'total postcodes in system: ' +  row['c']
36  end
37
38  puts '<br><br>'
39
40
41if connection
42  sql = 'select part1, 10-count(*) as c from (select part1, left(part2, 1) as b  from codes where confirmed = 1 group by part1, b) as a group by part1 order by c, part1;'
43  res = connection.query(sql)
44  puts "To map postcodes exhaustively, we should first get a point for each of the first char of the second part of the postcode (eg '0' for NW1 0AA). So something at NW1 0something, NW1 1something, NW1 2something and so on. This gets the data reasonably accurate for many applications. The list below tells you how many we are missing for each postcode first part.<br><br>"
45  puts "Click the headings to change the sort order (but be patient, it may take a few seconds!<br><br>"
46  puts '<table class="sortable" border="1"><tr><th>First part of<br />postcode</th><th>number of<br />missing codes</th></tr>'
47  res.each_hash do |row|
48    puts '<tr><td>' +  row['part1'] + '</td><td align="right">' + row['c'] + "</td><td><img src='/redpixel.gif' width='#{row['c'].to_i * 10}' height='10' /></td></tr>"
49  end
50
51  puts '</table><br><br></html>'
52
53end
54
55
56end
57
Note: See TracBrowser for help on using the repository browser.