Public Bill and Standing Committees front end code.

DanielKinsman · Aug 29, 2007 · 32a8cb8 · 32a8cb8
1 parent 198ca13
commit 32a8cb8
Show file tree

Hide file tree

Showing 22 changed files with 1,018 additions and 143 deletions.
diff --git a/db/hansard.sql b/db/hansard.sql
@@ -62,7 +62,8 @@ CREATE TABLE `hansard` (
   KEY `major` (`major`),
   KEY `htype` (`htype`),
   KEY `majorhdate` (`major`,`hdate`),
-  KEY `modified` (`modified`)
+  KEY `modified` (`modified`),
+  KEY `majorhtypeminor` (`major`,`htype`,`minor`)
 );
 
 CREATE TABLE `member` (
@@ -95,7 +96,7 @@ CREATE TABLE `memberinfo` (
 
 CREATE TABLE `moffice` (
   `moffice_id` int(11) NOT NULL auto_increment,
-  `dept` varchar(100) NOT NULL default '',
+  `dept` varchar(255) NOT NULL default '',
   `position` varchar(200) NOT NULL default '',
   `from_date` date NOT NULL default '1000-01-01',
   `to_date` date NOT NULL default '9999-12-31',
@@ -126,3 +127,27 @@ CREATE TABLE `indexbatch` (
   PRIMARY KEY  (`indexbatch_id`)
 );
 
+-- For Public Bill Committees originally
+CREATE TABLE bills (
+	id integer not null auto_increment,
+	title varchar(255) not null,
+	url varchar(255) not null,
+	lords boolean not null,
+	session varchar(50) not null,
+	standingprefix varchar(255) not null,
+	primary key (id),
+	key title (title)
+);
+
+CREATE TABLE pbc_members (
+	id integer not null auto_increment,
+	member_id integer not null,
+	chairman boolean not null,
+	bill_id integer not null,
+	sitting varchar(4) not null,
+	attending boolean not null,
+	primary key (id),
+	key member_id (member_id),
+	key bill_id (bill_id)
+);
+
diff --git a/scripts/pbc2rss.pl b/scripts/pbc2rss.pl
@@ -0,0 +1,60 @@
+#!/usr/bin/perl
+
+use warnings;
+use strict;
+use XML::RSS;
+use config;
+use DBI;
+use URI::Escape;
+my $dbh = DBI->connect($config::dsn, $config::user, $config::pass );
+
+my $query = $dbh->selectall_arrayref('select gid, minor, hdate from hansard
+	where htype=10 and major=6
+	order by hdate desc limit 20');
+my $rss = new XML::RSS (version => '1.0');
+$rss->channel(
+	title => "Public Bill Committee debates",
+	link => "http://www.theyworkforyou.com/pbc/",
+	description => "Public Bill Committee debates via TheyWorkForYou.com- http://www.theyworkforyou.com/ .",
+	dc => {
+		subject => '',
+		creator => 'TheyWorkForYou.com',
+		publisher => 'TheyWorkForYou.com',
+		rights => 'Parliamentary Copyright',
+		language => 'en-gb',
+		ttl => 600
+	},
+	syn => {
+		updatePeriod => 'daily',
+		updateFrequency => '1',
+		updateBase => '1901-01-01T00:00+00:00',
+	},
+);
+foreach (@$query) {
+	my ($gid, $minor, $hdate) = @$_;
+	my ($title, $session) = $dbh->selectrow_array('select title, session from bills where id=?', {}, $minor);
+	$gid =~ /standing\d\d\d\d-\d\d-\d\d_.*?_(\d\d)-\d_\d\d\d\d-\d\d-\d\d/;
+	my $sitting = ordinal($1+0);
+	my $u_title = uri_escape($title);
+	$u_title =~ s/%20/+/g;
+	$rss->add_item(
+		title => "$title, $sitting sitting",
+		link => "http://www.theyworkforyou.com/pbc/$session/$u_title",
+		#description => $result->{body
+	);
+}
+print $rss->as_string;
+
+sub ordinal {
+	return $_[0] . ordsuf($_[0]);
+}
+sub ordsuf {
+	my $n = shift;
+	$n %= 100;
+	return 'th' if $n >= 11 && $n <= 13;
+	$n %= 10;
+	return 'st' if $n == 1;
+	return 'nd' if $n == 2;
+	return 'rd' if $n == 3;
+	return 'th';
+}
diff --git a/scripts/rssgenerate b/scripts/rssgenerate
@@ -23,5 +23,6 @@ cd ~/fawkes/scripts
 ./lords2rss.pl > /data/vhost/www.theyworkforyou.com/docs/lords/lords.rss
 ./whall2rss.pl > /data/vhost/www.theyworkforyou.com/docs/whall/whall.rss
 ./ni2rss.pl > /data/vhost/www.theyworkforyou.com/docs/ni/ni.rss
+./pbc2rss.pl > /data/vhost/www.theyworkforyou.com/docs/pbc/pbc.rss
 php -q mprss.php