default console

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
CREATE OR REPLACE FUNCTION update_house_contract_status_by_service_trigger() RETURNS TRIGGER AS $_$
DECLARE
house_contract_id int := NEW."houseContractId";
other_service "Service";
active_service_count int;
card_count int;
BEGIN
IF NEW."utilityStatus"='OBJECTED' AND OLD."utilityStatus"!='OBJECTED' AND NEW."utilityType" IN ('gas', 'electric') THEN
RAISE NOTICE 'IN FIRST IF';
SELECT * INTO other_service
FROM "Service"
WHERE "houseContractId" = house_contract_id
AND "utilityType" = CASE NEW."utilityType"
WHEN 'gas' THEN 'electric'
WHEN 'electric' THEN 'gas'
END;
RAISE NOTICE 'other_service %', other_service;
IF other_service."utilityStatus" = 'OBJECTED' THEN
RAISE NOTICE 'IN SECOND IF';
SELECT count(id) INTO active_service_count
FROM "Service"
WHERE "utilityStatus" IN ('ACTIVE', 'ACTIONED')
AND "houseContractId"=house_contract_id
AND "utilityType" NOT IN ('gas', 'electric');
SELECT count(pi.id) INTO card_count
FROM "PaymentInfo" pi
JOIN "Tenant" T on pi."tenantId" = T.id
JOIN "TenantContract" TC on T.id = TC."tenantId"
WHERE TC."houseContractId"=house_contract_id;
RAISE NOTICE 'service_count %', active_service_count;
RAISE NOTICE 'card_count %', card_count;
IF active_service_count=0 AND card_count < 2 THEN
RAISE NOTICE 'IN THIRD IF';
UPDATE "HouseContract" SET status='Pending' WHERE id = house_contract_id;
END IF;
END IF;
END IF;
RETURN NEW;
END;
$_$ LANGUAGE plpgsql;
UPDATE "Service" SET "utilityStatus"='OBJECTED' WHERE id=12;
UPDATE "Service" SET "utilityStatus"='ACTIVE' WHERE id=12;
-- Wipe out journal records
DELETE FROM audit.logged_actions
WHERE id IN (SELECT id FROM audit.logged_actions WHERE client_query ILIKE '%insert into "public"."SalesJournal"%' AND (app_user_info->>'id')::int = 0);
-- Show stats on queries
SELECT client_query, count(*)
FROM audit.logged_actions
GROUP BY client_query
ORDER BY count(*) DESC;
-- Show stats on actions
SELECT action, count(*)
FROM audit.logged_actions
GROUP BY action;
-- Compact audit table - run garbage collector, etc.
VACUUM FULL audit.logged_actions;
-- Check size of all tables
SELECT *, pg_size_pretty(total_bytes) AS total
, pg_size_pretty(index_bytes) AS INDEX
, pg_size_pretty(toast_bytes) AS toast
, pg_size_pretty(table_bytes) AS TABLE
FROM (
SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (
SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
, c.reltuples AS row_estimate
, pg_total_relation_size(c.oid) AS total_bytes
, pg_indexes_size(c.oid) AS index_bytes
, pg_total_relation_size(reltoastrelid) AS toast_bytes
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE relkind = 'r'
) a
) a
ORDER BY table_bytes DESC;
select client_query, table_name, count(*) from audit.logged_actions WHERE client_query ilike '%generate_statement%' group by client_query, table_name order by count(*) desc
SELECT table_name , count(*) from audit.logged_actions GROUP BY table_name order by count(*) desc;
SELECT count(*) FROM audit.logged_actions WHERE table_name='SageExport' and (client_query ilike '%generate_statement%' or client_query ilike '%import_statement%')
SELECT client_query, action, count(*) FROM audit.logged_actions WHERE table_name='SalesJournal' GROUP BY client_query, action order by count(*) desc
SELECT * FROM audit.logged_actions
where changed_fields isnull
and (app_user_info->>'id')::int = 0
-- AND action='DELETE'
AND "table_name" NOT IN ('state', 'MessageQueue', 'PdfQueue')
and coalesce(application_name, '') = '';
SELECT
-- count(*)
*
FROM audit.logged_actions
WHERE
application_name not ilike 'pgadmin%'
and application_name not ilike 'psql%'
ORDER BY application_name desc
-- coalesce(application_name, '') != '' --pgadmin or psql
-- OR client_query ilike '--20%' -- migration
-- OR client_query ilike '-- 20%' -- migration
-- OR "table_name" IN ('state', 'MessageQueue', 'PdfQueue') -- beam-1291
SELECT * FROM huddle_filter_houses('{}', 'CURRENT');
-- OLD
WITH
house_contract AS (
SELECT contract.*,
(row_number() OVER (PARTITION BY "houseId" ORDER BY "endDate" DESC)) AS rank
FROM "HouseContract" contract
WHERE contract."signUpDate" <= CURRENT_TIMESTAMP
AND contract."endDate" >= CURRENT_TIMESTAMP
),
services AS (
SELECT s."houseContractId", jsonb_object_agg(s."utilityType", array[ s."utilityStatus" ]) AS services_list
FROM "Service" s
GROUP BY s."houseContractId"
)
SELECT house.*
FROM public."House" house
INNER JOIN house_contract ON house_contract."houseId" = house.id AND house_contract.rank = 1
INNER JOIN services ON services."houseContractId" = house_contract.id
LEFT JOIN public."Address" address ON address.id = house."addressId"
LEFT JOIN public."Agency" agency ON agency.id = house."agencyId"
-- NEW
WITH
house_contract AS (
SELECT contract.*,
(row_number() OVER (PARTITION BY "houseId" ORDER BY "endDate" DESC)) AS rank
FROM "HouseContract" contract
WHERE contract."signUpDate" <= CURRENT_TIMESTAMP
AND contract."endDate" >= CURRENT_TIMESTAMP
),
services AS (
SELECT s."houseContractId", jsonb_object_agg(s."utilityType", array[ s."utilityStatus" ]) AS services_list
FROM "Service" s
GROUP BY s."houseContractId"
)
SELECT DISTINCT ON (house.id) house.*
FROM public."House" house
INNER JOIN house_contract ON house_contract."houseId" = house.id AND house_contract.rank = 1
INNER JOIN services ON services."houseContractId" = house_contract.id
LEFT JOIN public."TenantContract" tenant_contract ON tenant_contract."houseContractId" = house_contract."id"
LEFT JOIN public."Address" address ON address.id = house."addressId"
LEFT JOIN public."Agency" agency ON agency.id = house."agencyId"
ORDER BY id asc
WITH
house_contract AS (
SELECT contract.*,
(row_number() OVER (PARTITION BY "houseId" ORDER BY "endDate" DESC)) AS rank
FROM "HouseContract" contract
WHERE contract."signUpDate" <= CURRENT_TIMESTAMP
AND contract."endDate" >= CURRENT_TIMESTAMP
),
services AS (
SELECT s."houseContractId", jsonb_object_agg(s."utilityType", array[ s."utilityStatus" ]) AS services_list
FROM "Service" s
GROUP BY s."houseContractId"
)
SELECT
house.*
FROM public."House" house
INNER JOIN house_contract ON house_contract."houseId" = house.id AND house_contract.rank = 1
INNER JOIN services ON services."houseContractId" = house_contract.id
LEFT JOIN public."TenantContract" tenant_contract ON tenant_contract."houseContractId" = house_contract."id"
LEFT JOIN public."Address" address ON address.id = house."addressId"
LEFT JOIN public."Agency" agency ON agency.id = house."agencyId"
GROUP BY house.id, house_contract.id, agency.name
ORDER BY house_contract.id asc
SELECT h.id, hc.id, tc.id from "House" h
LEFT JOIN "HouseContract" hc on h.id = hc."houseId"
LEFT JOIN "TenantContract" tc on hc.id = tc."houseContractId";
select * from "HouseContract";
SELECT * FROM audit.logged_actions WHERE table_name='PaymentInfo';
UPDATE audit.logged_actions SET original_data = original_data-'number', changed_fields= changed_fields - 'number', new_data = new_data - 'number' WHERE table_name='PaymentInfo';
SELECT '{"a":1, "b":2}'::jsonb-'c'::text;
UPDATE "PaymentInfo" SET number='5211111111111119' WHERE id=1;
-- concat_ws('|', array_to_string(hcv."tenantIds", '|'), h.id::text, hcv.id::text, a."streetName", a.address2, a."cityName", a."county", a."postCode") ILIKE concat('%%', $4, '%%')
SELECT
hcv."houseId",
hcv.id,
hcv."tenantIds",
concat_ws('|', array_to_string(hcv."tenantIds", '|'), hcv."houseId"),
hcv."endDate",
hcv."signUpDate"
FROM "HouseContractView" hcv
-- INNER JOIN s ON s."houseContractId" = hcv.id
LEFT JOIN "House" h ON h.id = hcv."houseId"
LEFT JOIN "Address" a ON a.id = h."addressId"
WHERE concat_ws('|', array_to_string(hcv."tenantIds", '|'), h.id::text, hcv.id::text, a."streetName", a.address2, a."cityName", a."county", a."postCode") ILIKE concat('%%', '10', '%%')
AND now() BETWEEN hcv."signUpDate" AND hcv."endDate"
(32, 19, 0.220000000000000001, 0.0464200000000000029, NULL, NULL, NULL, 'gas', 40, '2016-07-01 00:00:00+00', '2018-02-28 23:59:59+00', true, 'Huddle_Standard_2016_July_Yorkshire_gas')
(60, 19, 0.129000000000000004, 0.0473000000000000018, NULL, NULL, NULL, 'gas', 40, '2018-03-01 00:00:00+00', '2018-06-30 23:59:59+00', true, 'Huddle_Standard_2018_March_Yorkshire_gas')
(90, 19, 0.380000000000000004, 0.0500000000000000028, NULL, NULL, NULL, 'gas', 40, '2018-07-01 00:00:00+00', '2019-02-28 23:59:59+00', true, 'Huddle_Standard_2018_July_Yorkshire_gas')
select
date_trunc('month', current_timestamp)::timestamptz - '2 month'::interval,
date_trunc('month', current_timestamp) + '4 month'::interval - '1 minute'::interval -- 3 период
select
date_trunc('month', current_timestamp)::timestamptz - '8 month'::interval,
date_trunc('month', current_timestamp) - '2 month 1 minute'::interval -- 2 период
select
date_trunc('month', current_timestamp)::timestamptz - '15 month'::interval,
date_trunc('month', current_timestamp) - '8 month 1 minute'::interval -- 1 период
select date_trunc('month', current_timestamp)
select date_trunc('month', '2018-11-01'::date)::timestamptz - '2 month'::interval, date_trunc('month', '2018-11-01'::date) + '4 month'::interval - '1 minute'::interval;
CREATE TABLE "MPxN" (
id serial PRIMARY KEY,
mpxn text, --UNIQUE,
house_id int REFERENCES "House"
);
ALTER TABLE "Meter" ADD COLUMN "MPxN" int;
-- ALTER TABLE "Meter" ALTER COLUMN
alter table "Meter"
add column "MPxN" integer,
add constraint mpxn_foreign_key
foreign key ("MPxN")
references "MPxN" (id);
INSERT INTO "MPxN"(mpxn, house_id)
SELECT "meterPointNumber", "houseId" FROM "Meter";
UPDATE "Meter" SET "MPxN"="MPxN".id FROM "MPxN" WHERE "meterPointNumber"="MPxN"."mpxn";
ALTER TABLE "Meter" DROP COLUMN "meterPointNumber" CASCADE ;
ALTER TABLE "Meter" DROP COLUMN "houseId" CASCADE ;
SELECT m.*
FROM "MPxN" mpxn
JOIN "Meter" m ON m."MPxN" = mpxn.id
WHERE coalesce(mpxn."house_id" = 800001, true)
AND coalesce(m."utilityType" = 'gas', true)
SELECT * FROM format($$'
SELECT m.*
FROM "MPxN" mpxn
JOIN "Meter" m ON m."MPxN" = mpxn.id
WHERE coalesce(mpxn."house_id" = $1, true)
AND coalesce(m."utilityType" = $2, true)
ORDER BY %I %s
'$$, 'id', 'ASC');
SELECT count(r.*)
FROM public."TenantReading" r
INNER JOIN public."Meter" m ON m.id = r."meterId"
INNER JOIN public."MPxN" mpxn ON m."MPxN" = mpxn.id
LEFT JOIN (
SELECT COUNT("tenantReadingId") > 0 as image, "tenantReadingId"
FROM public."Uploads"
GROUP BY "tenantReadingId"
) AS u ON r.id = u."tenantReadingId"
SELECT * FROM util.upsert_row(NULL::"MPxN", '{"mpxn":"23","house_id":"800001"}')
SELECT * FROM util.upsert_row(NULL::"Meter",
'{"meterType":"CREDIT","rateType":"DUAL","serial":"1234","unitType":"KWh","utilityType":"electric","installDate":"2019-01-30T00:00:00.000Z","comment":"","MPxN":34,
"meterIssue":false,"readingRequested":false}')
select * from "MPxN" ORDER BY id desc
SELECT * FROM upsert_meter(
jsonb_populate_record(NULL::"Meter",
'{"meterType":"CREDIT","rateType":"DUAL","serial":"1234","unitType":"KWh","utilityType":"electric","installDate":"2019-01-30T00:00:00.000Z","comment":"", "meterIssue":false,"readingRequested":false}'),
jsonb_populate_record(NULL::"MPxN", '{"mpxn":"234","house_id":"800001"}'));
VACUUM FULL;
select date_trunc('month', current_timestamp) - '2 month 6 days 1 minute'::interval
select "meterPointNumber", count(*) from "Meter" GROUP BY "meterPointNumber" HAVING count(*) > 1
select count(*) from "Meter"
SELECT "meterPointNumber", "houseId", count(*) FROM "Meter" GROUP BY "meterPointNumber", "houseId" HAVING count(*) > 1;
select "meterPointNumber", count(*)
from
(SELECT distinct on ("meterPointNumber") "meterPointNumber", "houseId", count(*) FROM "Meter") as foo GROUP BY "meterPointNumber", "houseId";
SELECT
DISTINCT ON ("meterPointNumber", "houseId")
case
WHEN char_length("meterPointNumber")<1 THEN null
ELSE "meterPointNumber"
END, "houseId"
FROM "Meter" WHERE "meterPointNumber" NOTNULL AND char_length("meterPointNumber")<1
SELECT "meterPointNumber", "houseId", count(*) FROM "Meter" WHERE "meterPointNumber" != 'Virtual meter' GROUP BY "meterPointNumber", "houseId" HAVING count(*)>0 AND "meterPointNumber" != 'Virtual meter'
-- 1507889240050
SELECT * FROM "Meter" WHERE "meterPointNumber"='1507889240050'
SELECT a.id
FROM "Meter" a
JOIN "House" h on a."houseId" = h.id
JOIN "Address" add on h."addressId" = add.id
WHERE Exists (
SELECT "meterPointNumber"
FROM "Meter" c
WHERE a."houseId" != c."houseId"
AND a."meterPointNumber" = c."meterPointNumber"
AND c."meterPointNumber" != 'Virtual meter'
)
ORDER BY "meterPoi
ntNumber"
delete from "Meter" where id in(SELECT a.id
FROM "Meter" a
JOIN "House" h on a."houseId" = h.id
JOIN "Address" add on h."addressId" = add.id
WHERE Exists (
SELECT "meterPointNumber"
FROM "Meter" c
WHERE a."houseId" != c."houseId"
AND a."meterPointNumber" = c."meterPointNumber"
AND c."meterPointNumber" != 'Virtual meter'
)
ORDER BY "meterPointNumber")