WITH elec_tariff AS (
SELECT stm.id, stm_svc.charge
FROM "Statement" stm
JOIN "StatementService" stm_svc ON stm.id = stm_svc."billId"
WHERE stm_svc."utilityType" = 'electric'
), gas_tariff AS (
SELECT stm.id, stm_svc.charge
FROM "Statement" stm
JOIN "StatementService" stm_svc ON stm.id = stm_svc."billId"
WHERE stm_svc."utilityType" = 'gas'
), statements AS (
SELECT
stm."houseContractId",
stm."tenantContractId",
date_trunc('month',stm."statementDate") AS "statementDate",
jsonb_agg(stm ORDER BY date_trunc('month', stm."statementDate"), stm.version DESC) AS "statements"
FROM "Statement" stm
WHERE stm.type='bill'
GROUP BY stm."tenantContractId", stm."houseContractId", date_trunc('month',stm."statementDate")
)
SELECT
distinct on (hc.id, stm_lvv."tenantContractId", date_trunc('month', stm."statementDate"))
row_number() OVER () as id,
hc.id as "houseContractId",
stm_lvv."tenantContractId",
et.charge as "elecTariffCharge",
gt.charge as "gasTariffCharge",
stm_cv.balance,
date_trunc('month', stm."statementDate") as "billDate",
statements,
COALESCE(ss.manual, false) AS "allowManualGeneration",
COALESCE((statements->0->>'scheduled')::bool, false) AS scheduled,
COALESCE((statements->0->>'emailed')::bool, false) AS emailed,
COALESCE((statements->0->>'paid')::bool, false) AS paid
FROM "HouseContract" hc
JOIN "StatementLastVersionView" stm_lvv ON stm_lvv."houseContractId" = hc.id
JOIN elec_tariff et ON et.id = stm_lvv.id
JOIN gas_tariff gt ON gt.id = stm_lvv.id
JOIN "StatementCalculatedView" stm_cv ON stm_cv.id = stm_lvv.id
JOIN "Statement" lstm ON lstm.id = stm_lvv.id
JOIN "Statement" stm ON stm."houseContractId" = hc.id
LEFT JOIN "StatementSettings" ss ON (ss."houseContractId" = hc.id
AND ss."statementType"='bill'
AND (ss."tenantContractId"=stm_lvv."tenantContractId" OR ss."tenantContractId" ISNULL))
JOIN statements ON (statements."houseContractId"=hc.id
AND statements."statementDate"=date_trunc('month', stm."statementDate")
AND (statements."tenantContractId"=stm_lvv."tenantContractId"))
OR (statements."houseContractId"=hc.id
AND statements."statementDate"=date_trunc('month', stm."statementDate")
AND (statements."tenantContractId" IS NULL AND stm_lvv."tenantContractId" ISNULL));
SELECT * FROM "StatementLastVersionView";
SELECT * FROM "StatementSettings";
WITH stm_agg AS (
SELECT fs."tenantContractId",
jsonb_agg(jsonb_build_object('id', fs.id, 'version', fs.version, 'url', u.url)
ORDER BY fs.version DESC) AS statements
FROM ("Statement" fs
LEFT JOIN "Uploads" u ON (((u."statementId" = fs.id) AND (u."resourceType" = 'STATEMENT'::text))))
WHERE (fs."tenantContractId" IS NOT NULL)
GROUP BY fs."tenantContractId"
)
SELECT tc.id,
tc."tenantId",
tc."houseContractId",
hc."houseId",
stm.id AS "statementId",
stm.type
FROM (((((((((("TenantContract" tc
JOIN "HouseContract" hc ON ((hc.id = tc."houseContractId")))
JOIN "House" h ON ((h.id = hc."houseId")))
JOIN "Address" a ON ((a.id = h."addressId")))
JOIN "Tenant" t ON ((t.id = tc."tenantId")))
LEFT JOIN "StatementSettings" ss ON (((ss."statementType" = 'tenant'::"StatementType") AND
(ss."tenantContractId" = tc.id))))
LEFT JOIN stm_agg ON ((stm_agg."tenantContractId" = tc.id)))
LEFT JOIN "StatementLastVersionView" stl ON ((stl."tenantContractId" = tc.id AND stl.type='tenant')))
LEFT JOIN "Statement" stm ON ((stm.id = stl.id AND stm.type='tenant')))
LEFT JOIN "StatementCalculatedView" scv ON ((scv.id = stl.id)))
LEFT JOIN "StatementTotalsView" stt ON ((stt.id = stl.id)));
WITH stm_agg AS (
SELECT fs."tenantContractId",
jsonb_agg(jsonb_build_object('id', fs.id, 'version', fs.version, 'url', u.url)
ORDER BY fs.version DESC) AS statements
FROM ("Statement" fs
LEFT JOIN "Uploads" u ON (((u."statementId" = fs.id) AND (u."resourceType" = 'STATEMENT'::text))))
WHERE (fs."tenantContractId" IS NOT NULL)
GROUP BY fs."tenantContractId"
)
SELECT tc.id,
tc."tenantId",
concat_ws(' '::text, t."firstName", t."lastName") AS name,
tc."houseContractId",
tc."endDate" AS "tenantContractEndDate",
tc."paymentComments" AS "tenantComments",
hc."houseComments",
hc."houseId",
stt."electricPlan",
stt."electricBill",
stt."electricWarnings",
stt."electricErrors",
stt."gasPlan",
stt."gasBill",
stt."gasWarnings",
stt."gasErrors",
stt."waterPlan",
stt."waterBill",
stt."waterWarnings",
stt."waterErrors",
stt."broadbandPlan",
stt."broadbandBill",
stt."broadbandWarnings",
stt."broadbandErrors",
stt."tvPlan",
stt."tvBill",
stt."tvWarnings",
stt."tvErrors",
stm.id AS "statementId",
stm.imported,
scv.balance,
scv.warnings,
scv.errors,
stm_agg.statements,
(hc."endDate" = tc."endDate") AS "equalEndDate",
h."agencyId" AS "_agencyId",
a."cityId" AS "_cityId",
tc."huddleStatus" AS "_huddleStatus",
COALESCE(ss.manual, false) AS "allowManualGeneration",
COALESCE(stm.scheduled, false) AS scheduled,
COALESCE(stm.emailed, false) AS emailed,
COALESCE(stm.paid, false) AS paid,
stm."dayToBePaid"
FROM (((((((((("TenantContract" tc
JOIN "HouseContract" hc ON ((hc.id = tc."houseContractId")))
JOIN "House" h ON ((h.id = hc."houseId")))
JOIN "Address" a ON ((a.id = h."addressId")))
JOIN "Tenant" t ON ((t.id = tc."tenantId")))
LEFT JOIN "StatementSettings" ss ON (((ss."statementType" = 'tenant'::"StatementType") AND
(ss."tenantContractId" = tc.id))))
LEFT JOIN stm_agg ON ((stm_agg."tenantContractId" = tc.id)))
LEFT JOIN "StatementLastVersionView" stl ON ((stl."tenantContractId" = tc.id)))
LEFT JOIN "Statement" stm ON ((stm.id = stl.id)))
LEFT JOIN "StatementCalculatedView" scv ON ((scv.id = stl.id)))
LEFT JOIN "StatementTotalsView" stt ON ((stt.id = stl.id)));
with ss as (SELECT
"houseContractId",
array_agg("startDate") as "startDate"
FROM "Statement"
GROUP BY "houseContractId")
select * from ss WHERE '2018-12-01T00:00:00.000Z'::date != ALL (ss."startDate")
SELECT
"houseContractId",
array_agg("startDate") as "startDate"
FROM "Statement"
GROUP BY "houseContractId"
WITH
electricTariffNames as (
SELECT hc.id, t."tariffName"
FROM "HouseContract" hc
JOIN "Service" s ON s."houseContractId" = hc.id
JOIN "Tariff" t ON s."tariffId" = t.id
WHERE s."utilityType"='electric'
),
gasTariffNames as (
SELECT hc.id, t."tariffName"
FROM "HouseContract" hc
JOIN "Service" s ON s."houseContractId" = hc.id
JOIN "Tariff" t ON s."tariffId" = t.id
WHERE s."utilityType"='gas'
),
lastStatement as (
SELECT
-- DISTINCT ON ("statementDate")
stm.*
FROM "Statement" stm
WHERE stm.type='bill'
ORDER BY "statementDate" ASC
),
statementsDates as (
SELECT
"houseContractId",
array_agg("startDate") as "startDate"
FROM "Statement"
GROUP BY "houseContractId"
)
SELECT
DISTINCT ON (hc.id)
hc.id,
hc."signUpDate",
hc."endDate",
a."formatedAddress",
etf."tariffName" as "electricTariffName",
gtf."tariffName" as "gasTariffName",
lstm.id as "statementId",
lstm."statementDate" as "lastBillDate",
stmd."startDate" as "statementsDates",
hc."houseId"
FROM "HouseContract" hc
JOIN "House" h ON h.id = hc."houseId"
JOIN "view_get_formatted_address" a ON h."addressId" = a."addressId"
JOIN "Service" s ON s."houseContractId" = hc.id
JOIN view_get_house_contract_service_info hcsi ON hcsi."houseContractId" = hc.id
JOIN "Tariff" t ON s."tariffId" = t.id
JOIN electricTariffNames etf ON etf.id = hc.id
JOIN gasTariffNames gtf ON gtf.id = hc.id
LEFT JOIN lastStatement lstm ON lstm."houseContractId"=hc.id
LEFT JOIN statementsDates stmd ON stmd."houseContractId"=hc.id
WHERE hc."billingType" = 'BILL'
AND s."utilityType" IN ('gas', 'electric')
AND hc."signUpDate" <= now() AND hc."endDate" >= now()
AND NOT hc."isPortfolio"
AND hc."status" <> 'Closed'
AND hcsi."liveEnergy";
WITH elec_tariff AS (
SELECT stm.id, stm_svc.charge
FROM "Statement" stm
JOIN "StatementService" stm_svc ON stm.id = stm_svc."billId"
WHERE stm_svc."utilityType" = 'electric'
), gas_tariff AS (
SELECT stm.id, stm_svc.charge
FROM "Statement" stm
JOIN "StatementService" stm_svc ON stm.id = stm_svc."billId"
WHERE stm_svc."utilityType" = 'gas'
), statements AS (
SELECT
stm."houseContractId",
stm."tenantContractId",
date_trunc('month',stm."statementDate") AS "statementDate",
jsonb_agg(stm ORDER BY date_trunc('month', stm."statementDate"), stm.version DESC) AS "statements"
FROM "Statement" stm
WHERE stm.type='bill'
GROUP BY stm."tenantContractId", stm."houseContractId", date_trunc('month',stm."statementDate")
)
SELECT
distinct on (hc.id, stm_lvv."tenantContractId", date_trunc('month', stm."statementDate"))
row_number() OVER () as id,
hc.id as "houseContractId",
stm_lvv."tenantContractId",
et.charge as "elecTariffCharge",
gt.charge as "gasTariffCharge",
stm_cv.balance,
date_trunc('month', stm."statementDate") as "billDate",
statements,
COALESCE(ss.manual, false) as "allowManualGeneration",
COALESCE((statements->0->>'scheduled')::bool, false) as scheduled,
COALESCE((statements->0->>'emailed')::bool, false) as emailed,
COALESCE((statements->0->>'paid')::bool, false) as paid
FROM "HouseContract" hc
JOIN "StatementLastVersionView" stm_lvv ON stm_lvv."houseContractId" = hc.id
LEFT JOIN elec_tariff et ON et.id = stm_lvv.id
LEFT JOIN gas_tariff gt ON gt.id = stm_lvv.id
JOIN "StatementCalculatedView" stm_cv ON stm_cv.id = stm_lvv.id
JOIN "Statement" lstm ON lstm.id = stm_lvv.id
JOIN "Statement" stm ON stm."houseContractId" = hc.id
LEFT JOIN "StatementSettings" ss ON (ss."houseContractId" = hc.id
AND ss."statementType"='bill'
AND (ss."tenantContractId"=stm_lvv."tenantContractId" OR ss."tenantContractId" ISNULL))
JOIN statements ON ((statements."houseContractId"=hc.id
AND statements."statementDate"=date_trunc('month', stm."statementDate")
AND (statements."tenantContractId"=stm_lvv."tenantContractId"))
OR (statements."houseContractId"=hc.id
AND statements."statementDate"=date_trunc('month', stm."statementDate")
AND (statements."tenantContractId" IS NULL AND stm_lvv."tenantContractId" ISNULL)));
WITH elec_tariff AS (
SELECT stm.id, stm_svc.charge
FROM "Statement" stm
JOIN "StatementService" stm_svc ON stm.id = stm_svc."billId"
WHERE stm_svc."utilityType" = 'electric'
), gas_tariff AS (
SELECT stm.id, stm_svc.charge
FROM "Statement" stm
JOIN "StatementService" stm_svc ON stm.id = stm_svc."billId"
WHERE stm_svc."utilityType" = 'gas'
), statements AS (
SELECT
stm."houseContractId",
stm."tenantContractId",
date_trunc('month',stm."statementDate") AS "statementDate",
jsonb_agg(jsonb_build_object('id', stm.id, 'version', stm.version, 'url', u.url)
ORDER BY date_trunc('month', stm."statementDate"), stm.version DESC) AS "statements"
FROM "Statement" stm
LEFT JOIN "Uploads" u ON (((u."statementId" = stm.id) AND (u."resourceType" = 'STATEMENT'::text)))
WHERE stm.type='bill'
GROUP BY stm."tenantContractId", stm."houseContractId", date_trunc('month',stm."statementDate")
)
SELECT
distinct on (hc.id, stm_lvv."tenantContractId", date_trunc('month', stm."statementDate"))
row_number() OVER () as id,
hc.id as "houseContractId",
stm_lvv."tenantContractId",
et.charge as "elecTariffCharge",
gt.charge as "gasTariffCharge",
stm_cv.balance,
date_trunc('month', stm."statementDate") as "billDate",
statements,
COALESCE(ss.manual, false) as "allowManualGeneration",
COALESCE((statements->0->>'scheduled')::bool, false) as scheduled,
COALESCE((statements->0->>'emailed')::bool, false) as emailed,
COALESCE((statements->0->>'paid')::bool, false) as paid,
stm_cv.warnings,
stm_cv.errors,
stt."electricPlan",
stt."electricBill",
stt."electricWarnings",
stt."electricErrors",
stt."gasPlan",
stt."gasBill",
stt."gasWarnings",
stt."gasErrors"
FROM "HouseContract" hc
JOIN "StatementLastVersionView" stm_lvv ON stm_lvv."houseContractId" = hc.id
LEFT JOIN elec_tariff et ON et.id = stm_lvv.id
LEFT JOIN gas_tariff gt ON gt.id = stm_lvv.id
JOIN "StatementCalculatedView" stm_cv ON stm_cv.id = stm_lvv.id
JOIN "Statement" lstm ON lstm.id = stm_lvv.id
JOIN "Statement" stm ON stm."houseContractId" = hc.id
LEFT JOIN "StatementSettings" ss ON (ss."houseContractId" = hc.id
AND ss."statementType"='bill'
AND (ss."tenantContractId"=stm_lvv."tenantContractId" OR ss."tenantContractId" ISNULL))
JOIN statements ON ((statements."houseContractId"=hc.id
AND statements."statementDate"=date_trunc('month', stm."statementDate")
AND (statements."tenantContractId"=stm_lvv."tenantContractId"))
OR (statements."houseContractId"=hc.id
AND statements."statementDate"=date_trunc('month', stm."statementDate")
AND (statements."tenantContractId" IS NULL AND stm_lvv."tenantContractId" ISNULL)))
LEFT JOIN "StatementTotalsView" stt ON ((stt.id = stm_lvv.id));