jkburges
commented
9 years ago Here is the query to generate the CSV file:
set search_path to 'aatams', 'pg_temp';
DROP FUNCTION IF EXISTS pg_temp.display_in_other_tz(
in_t timestamptz,
in_tzname text,
in_fmt text);
CREATE FUNCTION pg_temp.display_in_other_tz(
in_t timestamptz,
in_tzname text,
in_fmt text) RETURNS text
AS $$
DECLARE
v text;
save_tz text;
BEGIN
SHOW timezone into save_tz;
EXECUTE 'SET local timezone to ' || quote_literal(in_tzname);
SELECT to_char(in_t, in_fmt) || '+' || TO_CHAR((extract(timezone from in_t) || ' second')::interval, 'HH24:MI') INTO v;
EXECUTE 'SET local timezone to ' || quote_literal(save_tz);
RETURN v;
END;
$$ language plpgsql;
drop function if exists pg_temp.to_url(controller character varying, id bigint);
create function pg_temp.to_url(controller character varying, id bigint) returns text as
$$ select ('https://aatams.emii.org.au/aatams/' || controller || '/show/' || id)::text
$$ language sql;
copy (
with deployments as (
select
receiver_id,
receiver_deployment.id as receiver_deployment_id,
initialisationdatetime_timestamp,
deploymentdatetime_timestamp,
tstzrange(initialisationdatetime_timestamp, recoverydatetime_timestamp) as deployment_interval
from receiver_deployment
join receiver_recovery on receiver_deployment.id = receiver_recovery.deployment_id
where initialisationdatetime_timestamp is not null and recoverydatetime_timestamp is not null
and initialisationdatetime_timestamp <= recoverydatetime_timestamp
),
invalid_deployments as (
select lhs.receiver_deployment_id as id, 'OVERLAP' as invalid_reason, rhs.receiver_deployment_id as overlap_id
from deployments lhs
join deployments rhs
on lhs.receiver_id = rhs.receiver_id
and lhs.deployment_interval && rhs.deployment_interval
and lhs.receiver_deployment_id != rhs.receiver_deployment_id
and lhs.initialisationdatetime_timestamp <= rhs.initialisationdatetime_timestamp
union
select receiver_deployment.id as id, 'INIT/DEPLOY/RECOVERY OUT OF ORDER' as invalid_reason, null as overlap_id
from receiver_deployment
join receiver_recovery on receiver_deployment.id = receiver_recovery.deployment_id
where initialisationdatetime_timestamp > deploymentdatetime_timestamp
or deploymentdatetime_timestamp > recoverydatetime_timestamp
)
select
project.name as project_name,
installation.name as installation_name,
station.name as station_name,
receiver_name, pg_temp.to_url('receiver', receiver.id) as receiver_url,
pg_temp.to_url('receiverDeployment', receiver_deployment.id) as receiver_deployment_url,
pg_temp.to_url('receiverRecoverye', receiver_recovery.id) as receiver_recovery_url,
email_address,
invalid_reason,
pg_temp.display_in_other_tz(initialisationdatetime_timestamp, initialisationdatetime_zone, 'YYYY-MM-DD"T"HH24:MI:SS') as initialisation_datetime,
pg_temp.display_in_other_tz(deploymentdatetime_timestamp, deploymentdatetime_zone, 'YYYY-MM-DD"T"HH24:MI:SS') as deploymentdatetime_datetime,
pg_temp.display_in_other_tz(recoverydatetime_timestamp, recoverydatetime_zone, 'YYYY-MM-DD"T"HH24:MI:SS') as recoverydatetime_datetime
from invalid_deployments
join receiver_deployment on invalid_deployments.id = receiver_deployment.id
join receiver on receiver_deployment.receiver_id = receiver.id
join receiver_recovery on receiver_deployment.id = receiver_recovery.deployment_id
join installation_station station on receiver_deployment.station_id = station.id
join installation on station.installation_id = installation.id
join project on installation.project_id = project.id
join project_role on receiver_recovery.recoverer_id = project_role.id
join sec_user on project_role.person_id = sec_user.id
order by invalid_reason, project_name, installation_name, station_name
) to stdout with CSV HEADER;
xhoenner
astecken
jdpye
kereid
dnahodil
After some consideration amongst the development team, it was deemed sensible to follow the following process to correct invalid deployment records (invalid due to either overlaps or out-of order initialisation/deployment/recovery datetimes):
This differs a bit from what was originally proposed, specifically step 1: basically, a review of the problematic records before any attempt to autocorrect. It was deemed too risky to do the auto-correct before a review.
Step 3 can probably be made a bit less painful by making any corrections to the actual CSV file - this can then be fed back in to the DB automatically. This should be a bit easier than trying to make all the edits manually through the web interface.