aboutsummaryrefslogtreecommitdiffstats
path: root/bin
diff options
context:
space:
mode:
Diffstat (limited to 'bin')
-rwxr-xr-xbin/oxfordshire/open311_service_request.cgi154
1 files changed, 41 insertions, 113 deletions
diff --git a/bin/oxfordshire/open311_service_request.cgi b/bin/oxfordshire/open311_service_request.cgi
index f2cf15cbf..1daa8d834 100755
--- a/bin/oxfordshire/open311_service_request.cgi
+++ b/bin/oxfordshire/open311_service_request.cgi
@@ -9,10 +9,11 @@
use strict;
use CGI;
+use Time::Piece;
use DBI;
use DBD::Oracle qw(:ora_types);
-### for local testing (no Oracle): use constant { ORA_VARCHAR2=>1, ORA_DATE=>1, ORA_NUMBER=>1};
-use Time::Piece;
+### for local testing (no Oracle):
+###use constant { ORA_VARCHAR2=>1, ORA_DATE=>1, ORA_NUMBER=>1};
###################################################################
# Config file: values in the config file override any values set
@@ -57,17 +58,6 @@ my $TESTING_WRITE_TO_FILE = 0; # write to file instead of DB
my $OUT_FILENAME = "fms-test.txt";
my $TEST_SERVICE_DISCOVERY=0; # switch to 1 to run service discovery, which confirms the DB connection at least
-my %PEM_BOUND_VAR_TYPES;
-my @PEM_BOUND_VAR_NAMES;
-foreach (get_pem_field_names()) {
- if (s/__(NUMBER|DATE)$//) {
- push @PEM_BOUND_VAR_NAMES, $_;
- $PEM_BOUND_VAR_TYPES{$_} = $1 || ''; # empty for varchar2
- } else {
- push @PEM_BOUND_VAR_NAMES, $_;
- }
-}
-
# Config file overrides existing values for these, if present:
if ($CONFIG_FILENAME && open(CONF, $CONFIG_FILENAME)) {
while (<CONF>) {
@@ -99,6 +89,8 @@ if ($CONFIG_FILENAME && open(CONF, $CONFIG_FILENAME)) {
# $ENV{PATH} ="$ORACLE_HOME/bin";
# $ENV{LD_LIBRARY_PATH}="$ORACLE_HOME/lib";
+my %PEM_BOUND_VAR_TYPES = get_pem_field_types();
+
my $ERR_MSG = 'error'; # unique key in data hash
# incoming (Open311, from FMS) field names
@@ -362,14 +354,18 @@ sub insert_into_pem {
$bindings{":postcode"} = substr($postcode, 0, 8); # calculated nearest street
foreach my $name (sort keys %bindings) {
- next if grep {$name eq $_} (':error_value', ':error_product', ':ce_doc_id'); # return values
- my $type = $PEM_BOUND_VAR_TYPES{$name} || 'VARCHAR2';
+ next if grep {$name eq $_} (':error_value', ':error_product', ':ce_doc_id'); # return values (see below)
$sth->bind_param(
$name,
$bindings{$name},
- $type eq 'NUMBER'? ORA_NUMBER : ($type eq 'DATE'? ORA_DATE : ORA_VARCHAR2 )
+ $PEM_BOUND_VAR_TYPES{$name} || ORA_VARCHAR2
);
}
+ # return values are bound explicitly here:
+ $sth->bind_param_inout(":error_value", \$error_value, 12); #> l_ERROR_VALUE # number
+ $sth->bind_param_inout(":error_product", \$error_product, 10); #> l_ERROR_PRODUCT (will always be 'DOC')
+ $sth->bind_param_inout(":ce_doc_id", \$pem_id, 12); #> l_ce_doc_id # number
+
# not used, but from the example docs, for reference
# $sth->bind_param(":ce_contact_title", $undef); # 'MR'
@@ -390,11 +386,6 @@ sub insert_into_pem {
# $sth->bind_param(":ce_hct_home_phone", $undef); # '0117 900 6201'
# $sth->bind_param(":ce_hct_mobile_phone", $undef); # '07111 1111111'
# $sth->bind_param(":ce_compl_remarks", $undef); # remarks (notes) max 254 char
-
- # return values:
- $sth->bind_param_inout(":error_value", \$error_value, 12); #> l_ERROR_VALUE # number
- $sth->bind_param_inout(":error_product", \$error_product, 10); #> l_ERROR_PRODUCT (will always be 'DOC')
- $sth->bind_param_inout(":ce_doc_id", \$pem_id, 12); #> l_ce_doc_id # number
$sth->execute();
$dbh->disconnect;
@@ -477,98 +468,35 @@ XML
}
#------------------------------------------------------------------
-# get_pem_field_names
-# return list of params for the stored procedure *in the correct order*
-# Also: __NUMBER and __DATE suffixes for anything that isn't a VARCHAR2
-# This also adds the colon prefix to all names.
-# Note the last three of these are inout params, and are handled
-# as a special (hardcoded) case when used (see above).
-#
-# These are currently only used for type lookup, so should probably
-# move them into the code as a name => type lookup; furthermore most of
-# these are not used, and default to varchar2 anyway.
+# get_pem_field_types
+# return hash of types by field name: any not explicitly set here
+# can be defaulted to VARCHAR2
#------------------------------------------------------------------
-sub get_pem_field_names {
- return map {":$_"} qw(
-ce_cat
-ce_class
-ce_title
-ce_contact_title
-ce_forename
-ce_surname
-ce_contact_type
-ce_flag
-ce_pcode_id
-ce_postcode
-ce_building_no
-ce_building_name
-ce_street
-ce_locality
-ce_town
-ce_county
-ce_organisation
-ce_work_phone
-ce_email
-ce_location
-ce_description
-ce_enquiry_type
-ce_source
-ce_incident_datetime__DATE
-ce_cpr_id
-ce_asset_id
-ce_rse_he_id
-ce_x__NUMBER
-ce_y__NUMBER
-ce_date_expires__DATE
-ce_doc_reference
-ce_issue_number__NUMBER
-ce_category
-ce_status_date__DATE
-ce_resp_of
-ce_compl_ack_flag
-ce_compl_ack_date__DATE
-ce_compl_flag
-ce_compl_peo_date__DATE
-ce_compl_target__DATE
-ce_compl_complete__DATE
-ce_compl_referred_to
-ce_compl_police_notif_flag
-ce_compl_date_police_notif
-ce_compl_from__DATE
-ce_compl_to__DATE
-ce_compl_claim
-ce_compl_corresp_date__DATE
-ce_compl_corresp_deliv_date__DATE
-ce_compl_no_of_petitioners__NUMBER
-ce_compl_remarks
-ce_compl_cause
-ce_compl_injuries
-ce_compl_damgae
-ce_compl_action
-ce_compl_litigation_flag
-ce_compl_litigation_reason
-ce_compl_claim_no
-ce_compl_determination
-ce_compl_est_cost__NUMBER
-ce_compl_adv_cost__NUMBER
-ce_compl_act_cost__NUMBER
-ce_compl_follow_up1__DATE
-ce_compl_follow_up2__DATE
-ce_compl_follow_uo3__DATE
-ce_compl_insurance_claim
-ce_compl_summons_received
-ce_compl_user_type
-ce_hct_vip
-ce_hct_home_phone
-ce_hct_mobile_phone
-ce_hct_fax
-ce_had_sub_build_name
-ce_had_property_type
-ce_status_code
-ce_date_time_arrived__DATE
-ce_reason_late
-error_value__NUMBER
-error_product
-ce_doc_id__NUMBER
+sub get_pem_field_types {
+ return (
+ ':ce_incident_datetime' => ORA_DATE,
+ ':ce_x' => ORA_NUMBER,
+ ':ce_y' => ORA_NUMBER,
+ ':ce_date_expires' => ORA_DATE,
+ ':ce_issue_number' => ORA_NUMBER,
+ ':ce_status_date' => ORA_DATE,
+ ':ce_compl_ack_date' => ORA_DATE,
+ ':ce_compl_peo_date' => ORA_DATE,
+ ':ce_compl_target' => ORA_DATE,
+ ':ce_compl_complete' => ORA_DATE,
+ ':ce_compl_from' => ORA_DATE,
+ ':ce_compl_to' => ORA_DATE,
+ ':ce_compl_corresp_date' => ORA_DATE,
+ ':ce_compl_corresp_deliv_date' => ORA_DATE,
+ ':ce_compl_no_of_petitioners' => ORA_NUMBER,
+ ':ce_compl_est_cost' => ORA_NUMBER,
+ ':ce_compl_adv_cost' => ORA_NUMBER,
+ ':ce_compl_act_cost' => ORA_NUMBER,
+ ':ce_compl_follow_up1' => ORA_DATE,
+ ':ce_compl_follow_up2' => ORA_DATE,
+ ':ce_compl_follow_uo3' => ORA_DATE,
+ ':ce_date_time_arrived' => ORA_DATE,
+ ':error_value' => ORA_NUMBER,
+ ':ce_doc_id' => ORA_NUMBER,
)
}
generated by cgit v1.2.3 (git 2.25.1) at 2025-04-26 14:44:21 +0000