 --
 
 COMMENT ON VIEW nonpv_mlh IS 'This is the Meter Location History for nonPV service points. @author Daniel Zhang (張道博)';
-
-
---
--- Name: deprecated_dz_pv_readings_without_pv_service_point_ids; Type: VIEW; Schema: public; Owner: postgres
---
-
-CREATE VIEW deprecated_dz_pv_readings_without_pv_service_point_ids AS
-    SELECT nonpv_mlh.meter_name, nonpv_mlh.service_point_id, dz_pv_readings.end_time, dz_pv_readings.channel, dz_pv_readings.value FROM (deprecated_dz_pv_readings dz_pv_readings JOIN nonpv_mlh ON ((dz_pv_readings.meter_name = (nonpv_mlh.meter_name)::bpchar)));
-
-
-ALTER TABLE public.deprecated_dz_pv_readings_without_pv_service_point_ids OWNER TO postgres;
-
---
--- Name: VIEW deprecated_dz_pv_readings_without_pv_service_point_ids; Type: COMMENT; Schema: public; Owner: postgres
---
-
-COMMENT ON VIEW deprecated_dz_pv_readings_without_pv_service_point_ids IS 'Working on reporting PV readings without PV Service Point IDs. @author Daniel Zhang (張道博)';
-
-
---
--- Name: deprecated_meter_ids_for_houses_without_pv; Type: VIEW; Schema: public; Owner: postgres
---
-
-CREATE VIEW deprecated_meter_ids_for_houses_without_pv AS
-    SELECT "LocationRecords".device_util_id FROM ("LocationRecords" LEFT JOIN "MSG_PV_Data" ON ((("LocationRecords".device_util_id)::text = ("MSG_PV_Data".util_device_id)::text))) WHERE ("MSG_PV_Data".util_device_id IS NULL);
-
-
-ALTER TABLE public.deprecated_meter_ids_for_houses_without_pv OWNER TO postgres;
-
---
--- Name: VIEW deprecated_meter_ids_for_houses_without_pv; Type: COMMENT; Schema: public; Owner: postgres
---
-
-COMMENT ON VIEW deprecated_meter_ids_for_houses_without_pv IS 'Meter IDs for houses that do not have PV. @author Daniel Zhang (張道博)';
-
-
---
--- Name: deprecated_nonpv_service_point_ids; Type: VIEW; Schema: public; Owner: postgres
---
-
-CREATE VIEW deprecated_nonpv_service_point_ids AS
-    SELECT "MeterLocationHistory".service_point_id, "MeterLocationHistory".meter_name, "MeterLocationHistory".installed, "MeterLocationHistory".uninstalled FROM "MeterLocationHistory" WHERE (NOT (EXISTS (SELECT "PVServicePointIDs".pv_service_point_id FROM "PVServicePointIDs" WHERE (("PVServicePointIDs".pv_service_point_id)::text = ("MeterLocationHistory".service_point_id)::text))));
-
-
-ALTER TABLE public.deprecated_nonpv_service_point_ids OWNER TO postgres;
-
---
--- Name: VIEW deprecated_nonpv_service_point_ids; Type: COMMENT; Schema: public; Owner: postgres
---
-
-COMMENT ON VIEW deprecated_nonpv_service_point_ids IS 'Service Point IDs, in the MLH,  that are not PV Service Point IDs. @author Daniel Zhang (張道博)';
-
-
---
--- Name: dz_energy_voltages_for_houses_without_pv; Type: VIEW; Schema: public; Owner: postgres
---
-
-CREATE VIEW dz_energy_voltages_for_houses_without_pv AS
-    SELECT readings_by_meter_location_history.meter_name, readings_by_meter_location_history.end_time, max(CASE WHEN (readings_by_meter_location_history.channel = (1)::smallint) THEN readings_by_meter_location_history.value ELSE NULL::real END) AS "Energy to House kwH", zero_to_null(max(CASE WHEN (readings_by_meter_location_history.channel = (4)::smallint) THEN readings_by_meter_location_history.value ELSE NULL::real END)) AS "Voltage", max(readings_by_meter_location_history.service_point_longitude) AS service_pt_longitude, max(readings_by_meter_location_history.service_point_latitude) AS service_pt_latitude, max((readings_by_meter_location_history.address)::text) AS address FROM (deprecated_meter_ids_for_houses_without_pv meter_ids_for_houses_without_pv JOIN readings_by_meter_location_history readings_by_meter_location_history ON (((meter_ids_for_houses_without_pv.device_util_id)::text = (readings_by_meter_location_history.meter_name)::text))) WHERE ((readings_by_meter_location_history.channel = (1)::smallint) OR (readings_by_meter_location_history.channel = (4)::smallint)) GROUP BY readings_by_meter_location_history.meter_name, readings_by_meter_location_history.end_time;
-
-
-ALTER TABLE public.dz_energy_voltages_for_houses_without_pv OWNER TO postgres;
-
---
--- Name: VIEW dz_energy_voltages_for_houses_without_pv; Type: COMMENT; Schema: public; Owner: postgres
---
-
-COMMENT ON VIEW dz_energy_voltages_for_houses_without_pv IS 'Energy and voltages for houses without PV limited by Meter Location History. @author Daniel Zhang (張道博)';
-
-
---
--- Name: dz_monthly_energy_summary_double_pv_meter; Type: VIEW; Schema: public; Owner: postgres
---
-
-CREATE VIEW dz_monthly_energy_summary_double_pv_meter AS
-    SELECT max((readings_by_meter_location_history.service_point_id)::text) AS service_point_id, sum(CASE WHEN (readings_by_meter_location_history.channel = (1)::smallint) THEN readings_by_meter_location_history.value ELSE NULL::real END) AS total_energy_to_house_kwh, sum(CASE WHEN (readings_by_meter_location_history.channel = (2)::smallint) THEN readings_by_meter_location_history.value ELSE NULL::real END) AS total_energy_from_house_kwh, sum(CASE WHEN (readings_by_meter_location_history.channel = (3)::smallint) THEN readings_by_meter_location_history.value ELSE NULL::real END) AS total_net_energy_kwh, avg(CASE WHEN (readings_by_meter_location_history.channel = (4)::smallint) THEN readings_by_meter_location_history.value ELSE NULL::real END) AS avg, to_char(date_trunc('month'::text, readings_by_meter_location_history.end_time), 'yyyy-mm'::text) AS service_month, max(readings_by_meter_location_history.service_point_latitude) AS sp_latitude, max(readings_by_meter_location_history.service_point_longitude) AS sp_longitude, max((readings_by_meter_location_history.location)::text) AS location_id, max((readings_by_meter_location_history.address)::text) AS address, max(readings_by_meter_location_history.latitude) AS location_latitude, max(readings_by_meter_location_history.longitude) AS location_longitude, ((count(readings_by_meter_location_history.end_time) / 4) / 24) AS count_day FROM (readings_by_meter_location_history JOIN "PVServicePointIDs" ON ((((readings_by_meter_location_history.service_point_id)::text = ("PVServicePointIDs".house_service_point_id)::text) OR ((readings_by_meter_location_history.service_point_id)::text = ("PVServicePointIDs".pv_service_point_id)::text)))) WHERE ("PVServicePointIDs".pv_service_point_id IS NOT NULL) GROUP BY readings_by_meter_location_history.service_point_id, to_char(date_trunc('month'::text, readings_by_meter_location_history.end_time), 'yyyy-mm'::text);
-
-
-ALTER TABLE public.dz_monthly_energy_summary_double_pv_meter OWNER TO postgres;
-
---
--- Name: VIEW dz_monthly_energy_summary_double_pv_meter; Type: COMMENT; Schema: public; Owner: postgres
---
-
-COMMENT ON VIEW dz_monthly_energy_summary_double_pv_meter IS 'Monthly energy summary for service points that have PV with a second PV meter. @author Daniel Zhang (張道博)';
-
-
---
--- Name: nonpv_service_point_ids; Type: VIEW; Schema: public; Owner: postgres
---
-
-CREATE VIEW nonpv_service_point_ids AS
-    SELECT "MeterLocationHistory".service_point_id, "MeterLocationHistory".meter_name, "MeterLocationHistory".installed, "MeterLocationHistory".uninstalled FROM "MeterLocationHistory" WHERE ((NOT (EXISTS (SELECT "PVServicePointIDs".pv_service_point_id FROM "PVServicePointIDs" WHERE (("PVServicePointIDs".pv_service_point_id)::text = ("MeterLocationHistory".service_point_id)::text)))) OR (NOT (EXISTS (SELECT "PVServicePointIDs".house_service_point_id FROM "PVServicePointIDs" WHERE (("PVServicePointIDs".house_service_point_id)::text = ("MeterLocationHistory".service_point_id)::text)))));
-
-
-ALTER TABLE public.nonpv_service_point_ids OWNER TO postgres;
-
---
--- Name: VIEW nonpv_service_point_ids; Type: COMMENT; Schema: public; Owner: postgres
---
-
-COMMENT ON VIEW nonpv_service_point_ids IS 'Service Point IDs, in the MLH,  that are not PV Service Point IDs. @author Daniel Zhang (張道博)';
-
-
---
--- Name: dz_monthly_energy_summary_for_nonpv_service_points; Type: VIEW; Schema: public; Owner: postgres
---
-
-CREATE VIEW dz_monthly_energy_summary_for_nonpv_service_points AS
-    SELECT max((readings_by_meter_location_history.service_point_id)::text) AS service_point_id, sum(CASE WHEN (readings_by_meter_location_history.channel = (1)::smallint) THEN readings_by_meter_location_history.value ELSE NULL::real END) AS total_energy_to_house_kwh, sum(CASE WHEN (readings_by_meter_location_history.channel = (2)::smallint) THEN readings_by_meter_location_history.value ELSE NULL::real END) AS total_energy_from_house_kwh, sum(CASE WHEN (readings_by_meter_location_history.channel = (3)::smallint) THEN readings_by_meter_location_history.value ELSE NULL::real END) AS total_net_energy_kwh, avg(CASE WHEN (readings_by_meter_location_history.channel = (4)::smallint) THEN readings_by_meter_location_history.value ELSE NULL::real END) AS avg, to_char(date_trunc('month'::text, readings_by_meter_location_history.end_time), 'yyyy-mm'::text) AS service_month, max(readings_by_meter_location_history.service_point_latitude) AS sp_latitude, max(readings_by_meter_location_history.service_point_longitude) AS sp_longitude, max((readings_by_meter_location_history.location)::text) AS location_id, max((readings_by_meter_location_history.address)::text) AS address, max(readings_by_meter_location_history.latitude) AS location_latitude, max(readings_by_meter_location_history.longitude) AS location_longitude, ((count(readings_by_meter_location_history.end_time) / 4) / 24) AS count_day FROM (readings_by_meter_location_history JOIN nonpv_service_point_ids nonpv_service_point_ids_v2 ON (((readings_by_meter_location_history.service_point_id)::text = (nonpv_service_point_ids_v2.service_point_id)::text))) GROUP BY readings_by_meter_location_history.service_point_id, to_char(date_trunc('month'::text, readings_by_meter_location_history.end_time), 'yyyy-mm'::text);
-
-
-ALTER TABLE public.dz_monthly_energy_summary_for_nonpv_service_points OWNER TO postgres;
-
---
--- Name: VIEW dz_monthly_energy_summary_for_nonpv_service_points; Type: COMMENT; Schema: public; Owner: postgres
---
-
-COMMENT ON VIEW dz_monthly_energy_summary_for_nonpv_service_points IS 'Monthly energy summary for Non-PV service points. @author Daniel Zhang (張道博)';
-
-
---
--- Name: dz_monthly_energy_summary_single_pv_meter; Type: VIEW; Schema: public; Owner: postgres
---
-
-CREATE VIEW dz_monthly_energy_summary_single_pv_meter AS
-    SELECT max((readings_by_meter_location_history.service_point_id)::text) AS service_point_id, sum(CASE WHEN (readings_by_meter_location_history.channel = (1)::smallint) THEN readings_by_meter_location_history.value ELSE NULL::real END) AS total_energy_to_house_kwh, sum(CASE WHEN (readings_by_meter_location_history.channel = (2)::smallint) THEN readings_by_meter_location_history.value ELSE NULL::real END) AS total_energy_from_house_kwh, sum(CASE WHEN (readings_by_meter_location_history.channel = (3)::smallint) THEN readings_by_meter_location_history.value ELSE NULL::real END) AS total_net_energy_kwh, avg(CASE WHEN (readings_by_meter_location_history.channel = (4)::smallint) THEN readings_by_meter_location_history.value ELSE NULL::real END) AS avg, to_char(date_trunc('month'::text, readings_by_meter_location_history.end_time), 'yyyy-mm'::text) AS service_month, max(readings_by_meter_location_history.service_point_latitude) AS sp_latitude, max(readings_by_meter_location_history.service_point_longitude) AS sp_longitude, max((readings_by_meter_location_history.location)::text) AS location_id, max((readings_by_meter_location_history.address)::text) AS address, max(readings_by_meter_location_history.latitude) AS location_latitude, max(readings_by_meter_location_history.longitude) AS location_longitude, ((count(readings_by_meter_location_history.end_time) / 4) / 24) AS count_day FROM (readings_by_meter_location_history JOIN "PVServicePointIDs" ON (((readings_by_meter_location_history.service_point_id)::text = ("PVServicePointIDs".house_service_point_id)::text))) WHERE ("PVServicePointIDs".pv_service_point_id IS NULL) GROUP BY readings_by_meter_location_history.service_point_id, to_char(date_trunc('month'::text, readings_by_meter_location_history.end_time), 'yyyy-mm'::text);
-
-
-ALTER TABLE public.dz_monthly_energy_summary_single_pv_meter OWNER TO postgres;
-
---
--- Name: VIEW dz_monthly_energy_summary_single_pv_meter; Type: COMMENT; Schema: public; Owner: postgres
---
-
-COMMENT ON VIEW dz_monthly_energy_summary_single_pv_meter IS 'Monthly energy summary for service points that have PV but NO second PV meter. @author Daniel Zhang (張道博)';
 
 
 --

ALTER TABLE public.deprecated_dz_pv_readings OWNER TO postgres;

--
-- Name: nonpv_mlh; Type: VIEW; Schema: public; Owner: postgres
--



REVOKE ALL ON TABLE cd_meter_ids_for_houses_with_pv_with_locations FROM PUBLIC;
REVOKE ALL ON TABLE cd_meter_ids_for_houses_with_pv_with_locations FROM eileen;
GRANT SELECT,INSERT,REFERENCES,TRIGGER,TRUNCATE ON TABLE cd_meter_ids_for_houses_with_pv_with_locations TO eileen;
GRANT SELECT,INSERT,REFERENCES,TRIGGER,TRUNCATE ON TABLE cd_meter_ids_for_houses_with_pv_with_locations TO sepgroup;
GRANT SELECT ON TABLE cd_meter_ids_for_houses_with_pv_with_locations TO sepgroupreadonly;


