openhealth views - DHIS-2

CREATE OR REPLACE VIEW view_dataelement AS
SELECT DISTINCT dataelement.dataelementid, dataelement.shortname
FROM dataelement
NATURAL JOIN aggregateddatavalue
ORDER BY dataelement.dataelementid, dataelement.shortname;

CREATE OR REPLACE VIEW view_indicator AS
SELECT DISTINCT aggregatedindicatorvalue.indicatorid, indicator.shortname
FROM indicator, aggregatedindicatorvalue
WHERE indicator.indicatorid = aggregatedindicatorvalue.indicatorid;

CREATE OR REPLACE VIEW view_ou2 AS
SELECT organisationunit.organisationunitid AS ou1id, organisationunit.shortname AS ou1short, organisationunit.geocode AS ou1geo, organisationunit_1.organisationunitid AS ou2id, organisationunit_1.shortname AS ou2short, organisationunit_1.geocode AS ou2geo
FROM organisationunit organisationunit, orgunitstructure orgunitstructure, organisationunit organisationunit_1
WHERE organisationunit.organisationunitid = orgunitstructure.idlevel1 AND organisationunit_1.organisationunitid = orgunitstructure.idlevel2 AND orgunitstructure.level = 2;

CREATE OR REPLACE VIEW view_ou3 AS
SELECT organisationunit.organisationunitid AS ou1id, organisationunit.shortname AS ou1short, organisationunit.geocode AS ou1geo, organisationunit_1.organisationunitid AS ou2id, organisationunit_1.shortname AS ou2short, organisationunit_1.geocode AS ou2geo, organisationunit_2.organisationunitid AS ou3id, organisationunit_2.shortname AS ou3short, organisationunit_2.geocode AS ou3geo
FROM organisationunit organisationunit, orgunitstructure orgunitstructure, organisationunit organisationunit_1, organisationunit organisationunit_2
WHERE organisationunit.organisationunitid = orgunitstructure.idlevel1 AND organisationunit_1.organisationunitid = orgunitstructure.idlevel2 AND organisationunit_2.organisationunitid = orgunitstructure.idlevel3 AND orgunitstructure.level = 3;

CREATE OR REPLACE VIEW view_ou4 AS
SELECT organisationunit.organisationunitid AS ou1id, organisationunit.shortname AS ou1short, organisationunit.geocode AS ou1geo, organisationunit_1.organisationunitid AS ou2id, organisationunit_1.shortname AS ou2short, organisationunit_1.geocode AS ou2geo, organisationunit_2.organisationunitid AS ou3id, organisationunit_2.shortname AS ou3short, organisationunit_2.geocode AS ou3geo, organisationunit_3.organisationunitid AS ou4id, organisationunit_3.shortname AS ou4short, organisationunit_3.geocode AS ou4geo
FROM organisationunit organisationunit, orgunitstructure orgunitstructure, organisationunit organisationunit_1, organisationunit organisationunit_2, organisationunit organisationunit_3
WHERE organisationunit.organisationunitid = orgunitstructure.idlevel1 AND organisationunit_1.organisationunitid = orgunitstructure.idlevel2 AND organisationunit_2.organisationunitid = orgunitstructure.idlevel3 AND organisationunit_3.organisationunitid = orgunitstructure.idlevel4 AND orgunitstructure.level = 4;

CREATE OR REPLACE VIEW view_ou5 AS
SELECT organisationunit.organisationunitid AS ou1id, organisationunit.shortname AS ou1short, organisationunit.geocode AS ou1geo, organisationunit_1.organisationunitid AS ou2id, organisationunit_1.shortname AS ou2short, organisationunit_1.geocode AS ou2geo, organisationunit_2.organisationunitid AS ou3id, organisationunit_2.shortname AS ou3short, organisationunit_2.geocode AS ou3geo, organisationunit_3.organisationunitid AS ou4id, organisationunit_3.shortname AS ou4short, organisationunit_3.geocode AS ou4geo,
organisationunit_4.shortname AS ou5short, organisationunit_4.geocode AS ou5geo
FROM organisationunit organisationunit, orgunitstructure orgunitstructure, organisationunit organisationunit_1, organisationunit organisationunit_2, organisationunit organisationunit_3, organisationunit organisationunit_4
WHERE organisationunit.organisationunitid = orgunitstructure.idlevel1 AND organisationunit_1.organisationunitid = orgunitstructure.idlevel2 AND organisationunit_2.organisationunitid = orgunitstructure.idlevel3 AND organisationunit_3.organisationunitid = orgunitstructure.idlevel4 AND organisationunit_4.organisationunitid = orgunitstructure.idlevel5 AND orgunitstructure.level = 5;

#PostgreSQL
CREATE OR REPLACE VIEW view_period_data AS
SELECT DISTINCT period.periodid, period.startdate, date_part('year'::text, period.startdate) AS year, date_part('month'::text, period.startdate) AS monthnumber, to_char(period.startdate::timestamp with time zone, 'Mon'::text) AS monthname
FROM period
NATURAL JOIN aggregateddatavalue
ORDER BY period.periodid, period.startdate, date_part('year'::text, period.startdate), date_part('month'::text, period.startdate), to_char(period.startdate::timestamp with time zone, 'Mon'::text);

CREATE OR REPLACE VIEW view_period_ind AS
SELECT DISTINCT period.periodid, period.startdate, date_part('year'::text, period.startdate) AS year, date_part('month'::text, period.startdate) AS monthnumber, to_char(period.startdate::timestamp with time zone, 'Mon'::text) AS monthname
FROM period
NATURAL JOIN aggregatedindicatorvalue
ORDER BY period.periodid, period.startdate, date_part('year'::text, period.startdate), date_part('month'::text, period.startdate), to_char(period.startdate::timestamp with time zone, 'Mon'::text);

#MySQL
CREATE or REPLACE VIEW view_period_data as SELECT DISTINCT period.periodid, period.startdate, YEAR(period.startdate) AS year, MONTH(period.startdate) AS monthnumber, SUBSTRING(MONTHNAME(period.startdate),1,3) AS monthname
FROM period
NATURAL JOIN aggregateddatavalue
ORDER BY period.periodid, period.startdate, YEAR(period.startdate), MONTH(period.startdate), MONTHNAME(period.startdate);

CREATE or REPLACE VIEW view_period_ind as SELECT DISTINCT period.periodid, period.startdate, YEAR(period.startdate) AS year, MONTH(period.startdate) AS monthnumber, SUBSTRING(MONTHNAME(period.startdate),1,3) AS monthname
FROM period
NATURAL JOIN aggregatedindicatorvalue
ORDER BY period.periodid, period.startdate, YEAR(period.startdate), MONTH(period.startdate), MONTHNAME(period.startdate);