1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
|
#!/usr/bin/perl
# script for querying the higatlas.fms_update table provided by
# Bentley and offering them up as XML service request updates.
# https://github.com/mysociety/fixmystreet/wiki/Open311-FMS---Proposed-differences-to-Open311
#
# mySociety: http://fixmystreet.org/
#-----------------------------------------------------------------
require 'open311_services.pm';
# incoming query params
my $CGI_VAR_START_DATE = 'start_date';
my $CGI_VAR_END_DATE = 'end_date';
my $CGI_VAR_NO_DEFAULT_DATE = 'force_no_default_date'; # for testing scratchy Oracle date stuff
my $CGI_VAR_LIMIT = 'limit'; # for testing
my $CGI_VAR_ANY_STATUS = 'any_status'; # for testing
my $USE_ONLY_DATES = 0; # dates not times
my $MAX_LIMIT = 1000;
my $STATUS_CRITERIA = "(status='OPEN' OR status='CLOSED')";
my $req = new CGI;
get_service_request_updates($req);
sub prepare_for_xml {
my $s = shift;
foreach ($s) {
from_to($_, 'utf8', 'Windows-1252') if $DECODE_FROM_WIN1252;
s/</</g; # numpty escaping pending XML Simple?
s/>/>/g;
s/&/&/g;
}
return $s;
}
#------------------------------------------------------------------
# get_service_discovery
# Although not really implementing this, use it as a test to read the
# db and confirm connectivity.
#
# TABLE "HIGATLAS"."FMS_UPDATE"
#
# "ROW_ID" NUMBER(9,0) NOT NULL ENABLE,
# "SERVICE_REQUEST_ID" NUMBER(9,0) NOT NULL ENABLE,
# "UPDATED_TIMEDATE" DATE DEFAULT SYSDATE NOT NULL ENABLE,
# "STATUS" VARCHAR2(10 BYTE) NOT NULL ENABLE,
# "DESCRIPTION" VARCHAR2(254 BYTE) NOT NULL ENABLE,
#
# CONSTRAINT "FMS_UPDATE_PK" PRIMARY KEY ("ROW_ID")
#------------------------------------------------------------------
sub get_service_request_updates {
# by default, we only want last 24 hours
# also, limit to 1000 records
my $raw_start_date = $req -> param($CGI_VAR_START_DATE);
my $raw_end_date = $req -> param($CGI_VAR_END_DATE);
my $start_date = get_date_or_nothing( $raw_start_date, $USE_ONLY_DATES );
my $end_date = get_date_or_nothing( $raw_end_date, $USE_ONLY_DATES );
if (! $req -> param($CGI_VAR_NO_DEFAULT_DATE)) {
$start_date = get_date_or_nothing( $YESTERDAY, $USE_ONLY_DATES ) unless ($start_date or $end_date);
}
my $date_format = 'YYYY-MM-DD HH24:MI:SS'; # NB: hh24 (not hh)
$start_date = "updated_timedate >= to_date('$start_date', '$date_format')" if $start_date;
$end_date = "updated_timedate <= to_date('$end_date', '$date_format')" if $end_date;
my $where_clause = '';
my @criteria = ($start_date, $end_date);
push @criteria, $STATUS_CRITERIA unless $req -> param($CGI_VAR_ANY_STATUS);
$where_clause = join(' AND ', grep {$_} @criteria);
$where_clause = "WHERE $where_clause" if $where_clause;
my $sql = qq(SELECT row_id, service_request_id, to_char(updated_timedate, '$date_format'), status, description FROM higatlas.fms_update $where_clause ORDER BY updated_timedate DESC);
my $limit = $req -> param($CGI_VAR_LIMIT) =~ /^(\d{1,3})$/? $1 : $MAX_LIMIT;
$sql = "SELECT * FROM ($sql) WHERE ROWNUM <= $limit" if $limit;
my $debug_str;
my $ary_ref;
if ($TESTING_WRITE_TO_FILE) {
$ary_ref = [
[97, 1000, '2013-01-05', 'OPEN', 'report was opened'],
[99, 1000, '2013-01-06', 'CLOSED', 'report was closed']
];
# only add debug now if config says we're testing
$debug_str = <<XML;
<!-- DEBUG: from: $raw_start_date => $start_date -->
<!-- DEBUG: to: $raw_end_date => $end_date -->
<!-- DEBUG: sql: $sql -->
XML
} else {
my $dbh = get_db_connection();
$ary_ref = $dbh->selectall_arrayref($sql);
}
# rough and ready XML dump now (should use XML Simple to build/escape properly!)
my $xml = "";
foreach my $row(@{$ary_ref}) {
if (defined $row) {
my ($id, $service_req_id, $updated_at, $status, $desc) = map { prepare_for_xml($_) } @$row;
$updated_at=~s/(\d{4}-\d\d-\d\d) (\d\d:\d\d:\d\d)/${1}T${2}Z/; # for now assume OCC in Zulu time
$xml.= <<XML;
<request_update>
<update_id>$id</update_id>
<service_request_id>$service_req_id</service_request_id>
<status>$status</status>
<updated_datetime>$updated_at</updated_datetime>
<description>$desc</description>
</request_update>
XML
}
}
print <<XML;
Content-type: text/xml
<?xml version="1.0" encoding="utf-8"?>
<service_request_updates>
$xml
</service_request_updates>
$debug_str
XML
}
|
