· 7 years ago · Nov 07, 2018, 05:28 AM
1CREATE OR REPLACE FUNCTION random_bytea(bytea_length integer)
2RETURNS bytea AS $body$
3 SELECT decode(string_agg(lpad(to_hex(width_bucket(random(), 0, 1, 256)-1),2,'0') ,''), 'hex')
4 FROM generate_series(1, $1);
5$body$
6LANGUAGE 'sql'
7VOLATILE
8SET search_path = 'pg_catalog';CREATE OR REPLACE FUNCTION GENERATE_CODE(size INT, customerNo VARCHAR, varTypeID INT) RETURNS TEXT AS $$
9DECLARE
10 characters TEXT := 'ABCDEFGHIJKLMNOPQRSTUVWYZ0123456789';
11 bytes BYTEA := NULL;
12 l INT := length(characters);
13 i INT := 0;
14 flag BOOLEAN := TRUE;
15 output TEXT := '';
16 foundIt VARCHAR := NULL;
17BEGIN
18 WHILE flag LOOP
19 foundIt := NULL;
20 output := '';
21 i := 0;
22 bytes := random_bytea(size);
23 WHILE i < size LOOP
24 output := output || substr(characters, get_byte(bytes, i) % l + 1, 1);
25 i := i + 1;
26 END LOOP;
27
28 SELECT "code" INTO foundIt
29 FROM public."Variables"
30 WHERE "customerNo" = customerNo
31 AND "VariableTypeId" = varTypeID
32 AND "code" = output;
33
34 IF foundIt IS NULL THEN
35 flag := FALSE;
36 END IF;
37
38 END LOOP;
39 RETURN output;
40END;
41$$ LANGUAGE plpgsql VOLATILE;
42CREATE OR REPLACE FUNCTION NEARESTS_LOCATIONS(dist INT, mylat DOUBLE PRECISION, mylon DOUBLE PRECISION, lim INT, mystate VARCHAR, mycity VARCHAR)
43RETURNS TABLE(
44 uuid UUID,
45 id INT,
46 locId CHARACTER VARYING,
47 "name" CHARACTER VARYING,
48 "address" CHARACTER VARYING,
49 city CHARACTER VARYING,
50 "state" CHARACTER VARYING,
51 zipcode CHARACTER VARYING,
52 latitude DOUBLE PRECISION,
53 longitude DOUBLE PRECISION,
54 distance DOUBLE PRECISION) AS $$
55DECLARE
56 minLat DOUBLE PRECISION := 0;
57 minLong DOUBLE PRECISION := 0;
58 maxLat DOUBLE PRECISION := 0;
59 maxLong DOUBLE PRECISION := 0;
60 deltaLat DOUBLE PRECISION := 0;
61 deltaLon DOUBLE PRECISION := 0;
62BEGIN
63 --1 degree -> 69.172 miles
64 deltaLat := dist / 69.172;
65 deltaLon := dist / (69.172 * cos(radians(mylat)));
66
67 minLat := mylat - deltaLat;
68 minLong := mylon - deltaLon;
69 maxLat := mylat + deltaLat;
70 maxLong := mylon + deltaLon;
71
72 RETURN QUERY SELECT
73 "Locations"."uuid",
74 "Locations"."id",
75 "Locations"."locId",
76 "Locations"."name",
77 "Locations"."address",
78 "Locations"."city",
79 "Locations"."state",
80 "Locations"."zipcode",
81 "Locations"."latitude",
82 "Locations"."longitude",
83 (3956 * 2 * ASIN(SQRT(POWER(SIN((mylat - "Locations"."latitude") * ((pi() / 180) / 2)), 2) +
84 COS(mylat * (pi() / 180)) * COS("Locations"."latitude" * (pi() / 180)) *
85 POWER(SIN((mylon - "Locations"."longitude") * ((pi() / 180) / 2)), 2)))
86 ) AS "distance"
87 FROM public."Locations"
88 WHERE
89 "Locations"."state" = mystate AND
90 "Locations"."city" = mycity AND
91 "Locations"."longitude" between minLong AND maxLong AND
92 "Locations"."latitude" between minLat AND maxLat
93 ORDER BY "distance", "Locations"."id"
94 LIMIT lim;
95END;
96$$ LANGUAGE plpgsql VOLATILE;CREATE OR REPLACE FUNCTION GENERATE_TRIPNO(size INT, codeType VARCHAR) RETURNS TEXT AS $$
97DECLARE
98 charactersAlpha TEXT := 'ABCDEFGHIJKLMNOPQRSTUVWYZ0123456789';
99 charactersNum TEXT := '0123456789';
100 characters TEXT := NULL;
101 bytes BYTEA := NULL;
102 l INT := 0;
103 i INT := 0;
104 flag BOOLEAN := TRUE;
105 output TEXT := '';
106 foundIt VARCHAR := NULL;
107BEGIN
108 IF codeType = 'ALPHA' THEN
109 characters := charactersAlpha;
110 ELSE
111 characters := charactersNum;
112 END IF;
113
114 l := length(characters);
115
116 WHILE flag LOOP
117 foundIt := NULL;
118 output := '';
119 i := 0;
120 bytes := random_bytea(size);
121 WHILE i < size LOOP
122 output := output || substr(characters, get_byte(bytes, i) % l + 1, 1);
123 i := i + 1;
124 END LOOP;
125
126 SELECT "tripNo" INTO foundIt
127 FROM public."Trips"
128 WHERE "tripNo" = output;
129
130 IF foundIt IS NULL THEN
131 flag := FALSE;
132 END IF;
133
134 END LOOP;
135 RETURN output;
136END;
137$$ LANGUAGE plpgsql VOLATILE;
138CREATE OR REPLACE FUNCTION GENERATE_STOPNO(size INT, codeType VARCHAR) RETURNS TEXT AS $$
139DECLARE
140 charactersAlpha TEXT := 'ABCDEFGHIJKLMNOPQRSTUVWYZ0123456789';
141 charactersNum TEXT := '0123456789';
142 characters TEXT := NULL;
143 bytes BYTEA := NULL;
144 l INT := 0;
145 i INT := 0;
146 flag BOOLEAN := TRUE;
147 output TEXT := '';
148 foundIt VARCHAR := NULL;
149BEGIN
150 IF codeType = 'ALPHA' THEN
151 characters := charactersAlpha;
152 ELSE
153 characters := charactersNum;
154 END IF;
155
156 l := length(characters);
157
158 WHILE flag LOOP
159 foundIt := NULL;
160 output := '';
161 i := 0;
162 bytes := random_bytea(size);
163 WHILE i < size LOOP
164 output := output || substr(characters, get_byte(bytes, i) % l + 1, 1);
165 i := i + 1;
166 END LOOP;
167
168 SELECT "stopNo" INTO foundIt
169 FROM public."TripStops"
170 WHERE "stopNo" = output;
171
172 IF foundIt IS NULL THEN
173 flag := FALSE;
174 END IF;
175
176 END LOOP;
177 RETURN output;
178END;
179$$ LANGUAGE plpgsql VOLATILE;
180CREATE OR REPLACE FUNCTION latest_weekly_report_by_client(clientId integer)RETURNS TEXT AS $$
181DECLARE
182 output TEXT := '';
183BEGIN
184 SET enable_seqscan = off;
185 SELECT "T"."id" INTO output
186 FROM
187 (SELECT "id", "createdAt"
188 FROM public."ClientWeeklyReports"
189 WHERE "ClientId" = clientId
190 LIMIT 1) AS "T";
191
192 RETURN output;
193END;
194$$ LANGUAGE plpgsql VOLATILE;CREATE OR REPLACE FUNCTION FIX_BAD_DATATYPE(the_date varchar)
195 RETURNS TIMESTAMP WITH TIME ZONE
196 LANGUAGE SQL
197 IMMUTABLE
198AS $$
199 SELECT TO_TIMESTAMP(the_date, 'yyyy-mm-dd');
200$$;
201
202DROP INDEX IF EXISTS "rule_selection";
203CREATE INDEX "rule_selection" ON public."Rules" ("type","status",COALESCE("validTo",FIX_BAD_DATATYPE('9999-12-31')));CREATE OR REPLACE FUNCTION FIND_RULE(trip_no VARCHAR, rule_type VARCHAR, old_version BOOLEAN )
204RETURNS TABLE("RuleId" INT, "noCondition" INT, "priority" INT) AS $$
205DECLARE
206 trip_rec RECORD;
207 count_val INT := NULL;
208BEGIN
209 SET enable_seqscan = off;
210 SELECT
211 "Trips"."ClientId",
212 "Trips"."startDate",
213 "Trips"."endDate",
214 "Trips"."originLocState",
215 "Trips"."endLocState",
216 "Trips"."trailerType",
217 "Trips"."productType"
218 INTO trip_rec
219 FROM public."Trips"
220 WHERE "Trips"."tripNo" = trip_no;
221 IF trip_rec."ClientId" IS NULL OR trip_rec."startDate" IS NULL OR trip_rec."endDate" IS NULL THEN
222 RETURN QUERY SELECT 0 AS "RuleId", 0 AS "noCondition", 0 AS "priority" WHERE FALSE;
223 RETURN;
224 END IF;
225
226 SELECT COUNT(*) INTO count_val FROM public."Rules" WHERE "Rules"."deletedAt" IS NULL;
227 IF count_val = 0 THEN
228 RETURN QUERY SELECT 0 AS "RuleId", 0 AS "noCondition", 0 AS "priority" WHERE FALSE;
229 RETURN;
230 END IF;
231
232 RETURN QUERY
233 SELECT
234 "t"."RuleId",
235 "t"."noConditions",
236 "t"."priority"
237 FROM
238 (SELECT
239 "Rules"."id" as "RuleId",
240 "Rules"."noConditions",
241 "Rules"."priority",
242 "Rules"."validFrom",
243 "Rules"."validTo",
244 COALESCE(
245 (SELECT
246 SUM(CASE
247 WHEN "RuleConditions"."type" = 'ORIGIN' THEN
248 CASE
249 WHEN "RuleConditions"."strValue" = trip_rec."originLocState" THEN 1
250 ELSE 0
251 END
252 WHEN "RuleConditions"."type" = 'DESTINATION' THEN
253 CASE
254 WHEN "RuleConditions"."strValue" = trip_rec."endLocState" THEN 1
255 ELSE 0
256 END
257 WHEN "RuleConditions"."type" = 'TRAILER TYPE' THEN
258 CASE
259 WHEN "RuleConditions"."strValue" = trip_rec."trailerType" THEN 1
260 ELSE 0
261 END
262 WHEN "RuleConditions"."type" = 'PRODUCT TYPE' THEN
263 CASE
264 WHEN "RuleConditions"."strValue" = trip_rec."productType" THEN 1
265 ELSE 0
266 END
267 ELSE 0
268 END)
269 FROM public."RuleConditions"
270 WHERE "RuleConditions"."RuleId" = "Rules"."id"), 0) AS "assert"
271 FROM public."Rules" "Rules"
272 WHERE
273 "Rules"."deletedAt" IS NULL AND
274 "Rules"."type"::VARCHAR = rule_type AND
275 ("Rules"."validFrom"::TIMESTAMP - '1 day'::INTERVAL) <= (trip_rec."startDate" ::TIMESTAMP) AND
276 COALESCE("Rules"."validTo" ,FIX_BAD_DATATYPE('9999-12-31')) >= (trip_rec."endDate"::TIMESTAMP) AND
277 "Rules"."status" = 'ACTIVE') AS "t"
278 WHERE "t"."assert" = "t"."noConditions"
279 ORDER BY "t"."priority" DESC, "t"."noConditions" DESC, "validTo" NULLS LAST
280 LIMIT 1;
281END;
282$$ LANGUAGE plpgsql VOLATILE;CREATE OR REPLACE FUNCTION GET_GAS_PRICE(p_state VARCHAR, p_date TIMESTAMP)RETURNS DOUBLE PRECISION AS $$
283DECLARE
284 output DOUBLE PRECISION := 0.0;
285BEGIN
286 SET enable_seqscan = off;
287 SELECT "T"."price" INTO output
288 FROM
289 (SELECT "price", "startDate"
290 FROM public."Gas"
291 WHERE "state" = p_state AND
292 "startDate" <= p_date
293 LIMIT 1) AS "T";
294
295 RETURN output;
296END;
297$$ LANGUAGE plpgsql VOLATILE;CREATE INDEX IF NOT EXISTS stop_dispatch ON "TripStops" ("TripId", "sequence");
298CREATE INDEX IF NOT EXISTS trip_dispatch ON "Trips" ("TractorId", "startDate" DESC);
299
300CREATE OR REPLACE VIEW "dispatchView" AS
301 SELECT
302 COALESCE(NULLIF(TRIM("Tractors"."unitNumber"),''),'N/A') AS "unitNumber",
303 COALESCE(NULLIF(TRIM("LTrips"."trailerNo"),''),'N/A') AS "trailerNo",
304 COALESCE(NULLIF(TRIM("LTrips"."driverName"),''),'N/A') AS "driverName",
305 COALESCE(NULLIF(TRIM("LTrips"."tripNo"),''),'N/A') AS "tripNo",
306 COALESCE(NULLIF(TRIM(SUBSTRING("LTrips"."stopInfo" FROM 510)),''),'N/A') AS "loadDate",
307 COALESCE(NULLIF(TRIM(SUBSTRING("LTrips"."stopInfo" FROM 0 FOR 255)),''),'N/A') AS "loadName",
308 COALESCE(NULLIF(TRIM("LTrips"."product"),''),'N/A') AS "product",
309 COALESCE(NULLIF(TRIM("LTrips"."cCompanyName"),''),'N/A') AS "cCompanyName",
310 COALESCE(NULLIF(TRIM(SUBSTRING("LTrips"."stopInfo" FROM 255 FOR 255)),''),'N/A') AS "shipper",
311 COALESCE(NULLIF(TRIM(SUBSTRING("LTrips"."lastUnloadInfo" FROM 0 FOR 255)),''),'N/A') AS "consigneeState",
312 COALESCE(NULLIF(TRIM(SUBSTRING("LTrips"."lastUnloadInfo" FROM 255 FOR 255)),''),'N/A') AS "consignee",
313 COALESCE(NULLIF(TRIM(SUBSTRING("LTrips"."lastUnloadInfo" FROM 510)),''),'N/A') AS "unloadDate",
314 COALESCE(NULLIF(TRIM("Tractors"."terminalLocation"),''),'N/A') AS "terminalLocation",
315 "Tractors"."id" AS "TractorsId",
316 "LTrips"."DriverId",
317 "LTrips"."ClientId"
318 FROM
319 public."Tractors" AS "Tractors"
320 LEFT JOIN LATERAL (
321 SELECT
322 "tripNo",
323 "trailerNo",
324 "product",
325 "driverName",
326 "DriverId",
327 "ClientId",
328 COALESCE(
329 (SELECT
330 RPAD("TripStops"."name",255,' ') || RPAD("TripStops"."shipper",255,' ') || TO_CHAR("TripStops"."startDate",'YYYY-MM-DD HH24:MI:SSTZ')
331 FROM public."TripStops"
332 WHERE
333 "TripStops"."deletedAt" IS NULL AND
334 "TripStops"."TripId" = "Trips"."id" AND
335 "TripStops"."tripNo" = "Trips"."tripNo" AND
336 "TripStops"."stopType"::VARCHAR = 'LOAD'
337 ORDER BY "sequence" ASC
338 LIMIT 1),
339 RPAD(' ',511,' ')
340 ) AS "stopInfo",
341 COALESCE(
342 (SELECT
343 RPAD("TripStops"."state",255,' ') || RPAD("TripStops"."consignee",255,' ') || TO_CHAR("TripStops"."endDate",'YYYY-MM-DD HH24:MI:SSTZ')
344 FROM public."TripStops"
345 WHERE
346 "TripStops"."deletedAt" IS NULL AND
347 "TripStops"."TripId" = "Trips"."id" AND
348 "TripStops"."tripNo" = "Trips"."tripNo" AND
349 "TripStops"."stopType"::VARCHAR = 'UNLOAD'
350 ORDER BY "sequence" DESC
351 LIMIT 1),
352 RPAD(' ',511,' ')
353 ) AS "lastUnloadInfo",
354 (SELECT
355 "Clients"."companyName"
356 FROM public."Clients"
357 WHERE
358 "Clients"."id" = "Trips"."ClientId" ) AS "cCompanyName"
359 FROM public."Trips"
360 WHERE
361 "Trips"."deletedAt" IS NULL AND
362 "Trips"."TractorId" = "Tractors"."id" AND
363 "Trips"."status"::VARCHAR IN ('IN PROGRESS', 'DOCS FINISHED')
364 ORDER BY "Trips"."TractorId","Trips"."startDate" DESC
365 LIMIT 1
366 ) AS "LTrips"
367 ON TRUE
368 WHERE
369 "Tractors"."deletedAt" IS NULL;CREATE OR REPLACE FUNCTION esrow_balance() RETURNS TRIGGER AS $$
370 DECLARE
371 bal DOUBLE PRECISION := 0.0;
372 driver_id INTEGER := NULL;
373 esrow_id INTEGER := NULL;
374 BEGIN
375 IF TG_OP <> 'DELETE' THEN
376 esrow_id := NEW."EsrowId";
377 ELSE
378 esrow_id := OLD."EsrowId";
379 END IF;
380
381 SELECT "Esrows"."DriverId" INTO driver_id
382 FROM public."Esrows"
383 WHERE "Esrows"."id" = esrow_id;
384
385 SELECT
386 SUM(
387 CASE
388 WHEN "Transactions"."type" = 'CR' THEN "Transactions"."amount"
389 ELSE "Transactions"."amount" * -1
390 END
391 ) INTO bal
392 FROM public."Transactions"
393 WHERE
394 "Transactions"."source" = 'ESCROW' AND
395 "Transactions"."EsrowId" = esrow_id AND
396 "Transactions"."sourceId" = esrow_id AND
397 "Transactions"."deletedAt" IS NULL;
398
399 UPDATE public."Esrows"
400 SET "balance" = COALESCE(bal,0)
401 WHERE "Esrows"."id" = esrow_id;
402
403 UPDATE public."SettlementsHomes"
404 SET "esrowTotal" = COALESCE(bal,0)
405 WHERE "SettlementsHomes"."DriverId" = driver_id;
406
407 IF TG_OP <> 'DELETE' THEN
408 return NEW;
409 ELSE
410 return OLD;
411 END IF;
412 END;
413$$ LANGUAGE plpgsql;
414
415CREATE TRIGGER esrow_balance AFTER INSERT OR UPDATE
416ON public."Transactions" FOR EACH ROW
417 WHEN (NEW."source" = 'ESCROW')
418 EXECUTE PROCEDURE esrow_balance();
419
420CREATE TRIGGER esrow_balance_delete AFTER DELETE
421ON public."Transactions" FOR EACH ROW
422 WHEN (OLD."source" = 'ESCROW')
423 EXECUTE PROCEDURE esrow_balance();CREATE OR REPLACE FUNCTION deficit_balance() RETURNS TRIGGER AS $$
424 DECLARE
425 bal DOUBLE PRECISION := 0.0;
426 driver_id INTEGER := NULL;
427 deficit_id INTEGER := NULL;
428 BEGIN
429
430 IF TG_OP <> 'DELETE' THEN
431 deficit_id := NEW."DeficitId";
432 ELSE
433 deficit_id := OLD."DeficitId";
434 END IF;
435
436 SELECT "Deficits"."DriverId" INTO driver_id
437 FROM public."Deficits"
438 WHERE "Deficits"."id" = deficit_id;
439
440 SELECT
441 SUM(
442 CASE
443 WHEN "Transactions"."type" = 'CR' THEN "Transactions"."amount"
444 ELSE "Transactions"."amount" * -1
445 END
446 ) INTO bal
447 FROM public."Transactions"
448 WHERE
449 "Transactions"."source" = 'DEFICIT' AND
450 "Transactions"."DeficitId" = deficit_id AND
451 "Transactions"."sourceId" = deficit_id AND
452 "Transactions"."deletedAt" IS NULL;
453
454
455
456 UPDATE public."Deficits"
457 SET "balance" = COALESCE(bal,0)
458 WHERE "Deficits"."id" = deficit_id;
459
460 UPDATE public."SettlementsHomes"
461 SET "deficitTotal" = COALESCE(bal,0)
462 WHERE "SettlementsHomes"."DriverId" = driver_id;
463
464 IF TG_OP <> 'DELETE' THEN
465 return NEW;
466 ELSE
467 return OLD;
468 END IF;
469 END;
470$$ LANGUAGE plpgsql;
471
472CREATE TRIGGER deficit_balance AFTER INSERT OR UPDATE
473ON public."Transactions" FOR EACH ROW
474 WHEN (NEW."source" = 'DEFICIT')
475 EXECUTE PROCEDURE deficit_balance();
476
477CREATE TRIGGER deficit_balance_delete AFTER DELETE
478ON public."Transactions" FOR EACH ROW
479 WHEN (OLD."source" = 'DEFICIT')
480 EXECUTE PROCEDURE deficit_balance();CREATE OR REPLACE FUNCTION esrow_batch_amount() RETURNS TRIGGER AS $$
481 DECLARE
482 driver_id INTEGER := NULL;
483 close_batch BOOLEAN := FALSE;
484 BEGIN
485
486 IF TG_OP <> 'DELETE' THEN
487 driver_id := NEW."DriverId";
488 ELSE
489 driver_id := OLD."DriverId";
490 END IF;
491
492 SELECT "batchReady" INTO close_batch FROM public."SettlementsHomes"
493 WHERE "DriverId" = driver_id;
494
495 IF close_batch = TRUE THEN
496 RETURN NULL;
497 END IF;
498
499 IF TG_OP = 'UPDATE' THEN
500 IF NEW."autoWithdraw" <> OLD."autoWithdraw" OR NEW."autoWithdrawValue" <> OLD."autoWithdrawValue" THEN
501 UPDATE public."SettlementsHomes"
502 SET "esrowBatch" = (CASE WHEN NEW."autoWithdraw" = TRUE THEN NEW."autoWithdrawValue" ELSE 0 END)
503 WHERE "SettlementsHomes"."DriverId" = NEW."DriverId";
504 END IF;
505 ELSE
506 IF TG_OP = 'INSERT' THEN
507 UPDATE public."SettlementsHomes"
508 SET "esrowBatch" = (CASE WHEN NEW."autoWithdraw" = TRUE THEN NEW."autoWithdrawValue" ELSE 0 END)
509 WHERE "SettlementsHomes"."DriverId" = NEW."DriverId";
510 ELSE
511 UPDATE public."SettlementsHomes"
512 SET "esrowBatch" = 0
513 WHERE "SettlementsHomes"."DriverId" = OLD."DriverId";
514 END IF;
515 END IF;
516
517 IF TG_OP <> 'DELETE' THEN
518 return NEW;
519 ELSE
520 return OLD;
521 END IF;
522 END;
523$$ LANGUAGE plpgsql;
524
525CREATE TRIGGER esrow_batch_amount AFTER INSERT OR UPDATE OR DELETE
526 ON public."Esrows" FOR EACH ROW
527 EXECUTE PROCEDURE esrow_batch_amount();CREATE OR REPLACE FUNCTION deficit_batch_amount() RETURNS TRIGGER AS $$
528 DECLARE
529 driver_id INTEGER := NULL;
530 close_batch BOOLEAN := FALSE;
531 BEGIN
532
533 IF TG_OP <> 'DELETE' THEN
534 driver_id := NEW."DriverId";
535 ELSE
536 driver_id := OLD."DriverId";
537 END IF;
538
539 SELECT "batchReady" INTO close_batch FROM public."SettlementsHomes"
540 WHERE "DriverId" = driver_id;
541
542 IF close_batch = TRUE THEN
543 RETURN NULL;
544 END IF;
545
546 IF TG_OP = 'UPDATE' THEN
547 IF NEW."autoWithdraw" <> OLD."autoWithdraw" OR NEW."autoWithdrawValue" <> OLD."autoWithdrawValue" THEN
548 UPDATE public."SettlementsHomes"
549 SET "deficitBatch" = (CASE WHEN NEW."autoWithdraw" = TRUE THEN NEW."autoWithdrawValue" ELSE 0 END)
550 WHERE "SettlementsHomes"."DriverId" = NEW."DriverId";
551 END IF;
552 ELSE
553 IF TG_OP = 'INSERT' THEN
554 UPDATE public."SettlementsHomes"
555 SET "deficitBatch" = (CASE WHEN NEW."autoWithdraw" = TRUE THEN NEW."autoWithdrawValue" ELSE 0 END)
556 WHERE "SettlementsHomes"."DriverId" = NEW."DriverId";
557 ELSE
558 UPDATE public."SettlementsHomes"
559 SET "deficitBatch" = 0
560 WHERE "SettlementsHomes"."DriverId" = OLD."DriverId";
561 END IF;
562 END IF;
563
564 IF TG_OP <> 'DELETE' THEN
565 return NEW;
566 ELSE
567 return OLD;
568 END IF;
569 END;
570$$ LANGUAGE plpgsql;
571
572CREATE TRIGGER deficit_batch_amount AFTER INSERT OR UPDATE OR DELETE
573 ON public."Deficits" FOR EACH ROW
574 EXECUTE PROCEDURE deficit_batch_amount();CREATE OR REPLACE FUNCTION loans_sum_batch() RETURNS TRIGGER AS $$
575 DECLARE
576 tot DOUBLE PRECISION := 0.0;
577 driver_id INTEGER := NULL;
578 start_dat DATE := NULL;
579 end_dat DATE := NULL;
580 close_batch BOOLEAN := FALSE;
581 BEGIN
582
583 SELECT "weekDate", "weekEndDate" INTO start_dat, end_dat
584 FROM public."SettlementsHomes" LIMIT 1;
585
586 IF TG_TABLE_NAME = 'Loans' THEN
587 IF TG_OP <> 'DELETE' THEN
588 driver_id := NEW."DriverId";
589 ELSE
590 driver_id := OLD."DriverId";
591 END IF;
592 ELSE
593 IF TG_OP <> 'DELETE' THEN
594 SELECT "Loans"."DriverId" INTO driver_id
595 FROM public."Loans"
596 WHERE "Loans"."id" = NEW."LoanId"
597 LIMIT 1;
598 ELSE
599 SELECT "Loans"."DriverId" INTO driver_id
600 FROM public."Loans"
601 WHERE "Loans"."id" = OLD."LoanId"
602 LIMIT 1;
603 END IF;
604 END IF;
605
606 SELECT "batchReady" INTO close_batch FROM public."SettlementsHomes"
607 WHERE "DriverId" = driver_id;
608
609 IF close_batch = TRUE THEN
610 RETURN NULL;
611 END IF;
612
613 SELECT SUM("LoanPlans"."fee" - "LoanPlans"."valuePaid") INTO tot
614 FROM public."Loans"
615 INNER JOIN public."LoanPlans"
616 ON "Loans"."id" = "LoanPlans"."LoanId"
617 AND "Loans"."DriverId" = driver_id
618 WHERE
619 "Loans"."deletedAt" IS NULL AND
620 "Loans"."status" IN ('ACTIVE') AND
621 "LoanPlans"."deletedAt" IS NULL AND
622 "LoanPlans"."status" IN ('PENDING', 'LATE') AND
623 "LoanPlans"."date"::DATE < end_dat;
624
625 UPDATE public."SettlementsHomes"
626 SET "loans" = COALESCE(tot,0)
627 WHERE "SettlementsHomes"."DriverId" = driver_id;
628
629 IF TG_OP <> 'DELETE' THEN
630 return NEW;
631 ELSE
632 return OLD;
633 END IF;
634 END;
635$$ LANGUAGE plpgsql;
636
637CREATE TRIGGER loans_sum_batch AFTER INSERT OR UPDATE OR DELETE
638 ON public."LoanPlans" FOR EACH ROW
639 EXECUTE PROCEDURE loans_sum_batch();
640
641CREATE TRIGGER loans_sum_batch_loan AFTER INSERT OR UPDATE OR DELETE
642 ON public."Loans" FOR EACH ROW
643 EXECUTE PROCEDURE loans_sum_batch();CREATE OR REPLACE FUNCTION deduction_balance() RETURNS TRIGGER AS $$
644 DECLARE
645 deduction_type VARCHAR := '';
646 deduction_id INTEGER := NULL;
647 cap DOUBLE PRECISION := 0.0;
648 bal DOUBLE PRECISION := 0.0;
649 tran_amt DOUBLE PRECISION := 0.0;
650 temp DOUBLE PRECISION := 0.0;
651 var_plan_curs REFCURSOR;
652 var_plan_rec RECORD;
653 con VARCHAR := '';
654 ord VARCHAR := '';
655 tran_type VARCHAR := '';
656 tran_operation VARCHAR := '';
657 BEGIN
658 IF TG_OP = 'INSERT' THEN
659 tran_operation := NEW."operation";
660 deduction_id := NEW."DeductionId";
661 tran_amt:= NEW."amount";
662 tran_type:= NEW."type";
663 ELSEIF TG_OP = 'UPDATE' THEN
664 tran_operation := NEW."operation";
665 deduction_id := NEW."DeductionId";
666 tran_amt:= NEW."amount" - OLD."amount";
667 tran_type:= NEW."type";
668 ELSE
669 tran_operation := OLD."operation";
670 deduction_id := OLD."DeductionId";
671 tran_amt:= OLD."amount" * -1;
672 tran_type:= OLD."type";
673 END IF;
674
675 IF tran_type = 'DB' THEN
676 tran_amt := tran_amt * -1;
677 END IF;
678
679 SELECT "type" INTO deduction_type FROM public."Deductions"
680 WHERE "Deductions"."id" = deduction_id;
681
682 SELECT
683 SUM(
684 CASE
685 WHEN "Transactions"."type" = 'CR' THEN "Transactions"."amount"
686 ELSE "Transactions"."amount" * -1
687 END
688 ) INTO bal
689 FROM public."Transactions"
690 WHERE
691 "Transactions"."source" = 'DEDUCTION' AND
692 "Transactions"."DeductionId" = deduction_id AND
693 "Transactions"."sourceId" = deduction_id AND
694 "Transactions"."operation" NOT IN ('REFINANCIANCE','INITIAL') AND
695 "Transactions"."deletedAt" IS NULL;
696
697 IF deduction_type IN ('FINITE PLAN', 'RENEWABLE PLAN') THEN
698 IF tran_amt < 0 THEN
699 con := ' AND "DeductionPlans"."valuePaid" > 0 ';
700 ord := 'DESC';
701 ELSE
702 con := ' AND "DeductionPlans"."status" NOT IN (''PAYOUT'',''REFINANCIATED'') ';
703 ord := 'ASC';
704 END IF;
705
706 IF tran_type <> 'DB' OR tran_operation = 'REVERSE' THEN
707 IF tran_operation NOT IN ('REFINANCIATED') THEN
708 OPEN var_plan_curs FOR
709 EXECUTE 'SELECT * FROM public."DeductionPlans"
710 WHERE "DeductionPlans"."DeductionId" = '||deduction_id||'AND
711 "DeductionPlans"."deletedAt" IS NULL '||con||'
712 ORDER BY "DeductionPlans"."sequence" '|| ord ||';';
713 LOOP
714 FETCH var_plan_curs INTO var_plan_rec;
715 EXIT WHEN NOT FOUND;
716
717 IF tran_amt <> 0 THEN
718 IF tran_amt < 0 THEN
719 IF var_plan_rec."valuePaid" > (tran_amt * -1) THEN
720 temp := tran_amt;
721 ELSEIF var_plan_rec."valuePaid" > 0 THEN
722 temp := var_plan_rec."valuePaid" * -1;
723 ELSE
724 CONTINUE;
725 END IF;
726 var_plan_rec."valuePaid" := var_plan_rec."valuePaid" + temp;
727 tran_amt := tran_amt - temp;
728 ELSE
729 IF var_plan_rec."fee" - var_plan_rec."valuePaid" < tran_amt THEN
730 temp := var_plan_rec."fee" - var_plan_rec."valuePaid";
731 ELSE
732 temp := tran_amt;
733 END IF;
734 var_plan_rec."valuePaid" := var_plan_rec."valuePaid" + temp;
735 tran_amt := tran_amt - temp;
736 END IF;
737
738 IF var_plan_rec."valuePaid" >= var_plan_rec."fee" THEN
739 UPDATE public."DeductionPlans"
740 SET
741 "valuePaid" = COALESCE(var_plan_rec."valuePaid",0),
742 "status" = 'PAYOUT',
743 "paidDate" = NOW()
744 WHERE
745 "DeductionPlans"."id" = var_plan_rec."id";
746 ELSEIF var_plan_rec."status" IN ('PAYOUT','REFINANCIATED') THEN
747 UPDATE public."DeductionPlans"
748 SET
749 "valuePaid" = COALESCE(var_plan_rec."valuePaid",0),
750 "status" = 'PENDING',
751 "paidDate" = null
752 WHERE
753 "DeductionPlans"."id" = var_plan_rec."id";
754 ELSE
755 UPDATE public."DeductionPlans"
756 SET "valuePaid" = COALESCE(var_plan_rec."valuePaid",0)
757 WHERE "DeductionPlans"."id" = var_plan_rec."id";
758 END IF;
759 ELSE
760 EXIT;
761 END IF;
762 IF tran_amt = 0 THEN
763 EXIT;
764 END IF;
765 END LOOP;
766 CLOSE var_plan_curs;
767 ELSE
768 UPDATE public."DeductionPlans"
769 SET "status" = 'REFINANCIATED'
770 WHERE
771 "DeductionPlans"."DeductionId" = deduction_id AND
772 "DeductionPlans"."deletedAt" IS NULL AND
773 "DeductionPlans"."status" <> 'PAYOUT';
774 END IF;
775 END IF;
776
777 SELECT
778 SUM(
779 CASE
780 WHEN "DeductionPlans"."valuePaid" > "DeductionPlans"."capitalValue" THEN "DeductionPlans"."capitalValue"
781 ELSE "DeductionPlans"."valuePaid"
782 END
783 ) INTO cap
784 FROM public."DeductionPlans"
785 WHERE
786 "DeductionPlans"."DeductionId" = deduction_id AND
787 "DeductionPlans"."deletedAt" IS NULL;
788
789 UPDATE public."Deductions"
790 SET "balance" = COALESCE(cap - "originalValue",0),
791 "lastPaymentDate" = NOW(),
792 "totalPaid" = COALESCE(bal,0)
793 WHERE "Deductions"."id" = deduction_id;
794
795 ELSEIF deduction_type IN ('FINITE AUTO', 'RENEWABLE AUTO') THEN
796
797 UPDATE public."Deductions"
798 SET "balance" = COALESCE(bal,0) - COALESCE("originalValue",0),
799 "lastPaymentDate" = NOW(),
800 "totalPaid" = COALESCE(bal,0)
801 WHERE "Deductions"."id" = deduction_id;
802
803 ELSE
804
805 UPDATE public."Deductions"
806 SET "lastPaymentDate" = NOW(),
807 "totalPaid" = COALESCE(bal,0)
808 WHERE "Deductions"."id" = deduction_id;
809
810 END IF;
811
812 IF TG_OP <> 'DELETE' THEN
813 return NEW;
814 ELSE
815 return OLD;
816 END IF;
817 END;
818$$ LANGUAGE plpgsql;
819
820CREATE TRIGGER deduction_balance AFTER INSERT OR UPDATE
821ON public."Transactions" FOR EACH ROW
822 WHEN (NEW."source" = 'DEDUCTION')
823 EXECUTE PROCEDURE deduction_balance();
824
825CREATE TRIGGER deduction_balance_delete AFTER DELETE
826ON public."Transactions" FOR EACH ROW
827 WHEN (OLD."source" = 'DEDUCTION')
828 EXECUTE PROCEDURE deduction_balance();CREATE OR REPLACE FUNCTION deduction_status() RETURNS TRIGGER AS $$
829 DECLARE
830 BEGIN
831 IF NEW."type" = 'FINITE AUTO' OR NEW."type" = 'FINITE PLAN' THEN
832 IF NEW."originalValue" <= NEW."balance" THEN
833 NEW."status" := 'PAYOUT';
834 END IF;
835 END IF;
836
837 return NEW;
838 END;
839$$ LANGUAGE plpgsql;
840
841CREATE TRIGGER deduction_status BEFORE UPDATE OF "balance"
842 ON public."Deductions" FOR EACH ROW
843 EXECUTE PROCEDURE deduction_status();CREATE OR REPLACE FUNCTION loan_balance() RETURNS TRIGGER AS $$
844 DECLARE
845 cap DOUBLE PRECISION := 0.0;
846 bal DOUBLE PRECISION := 0.0;
847 tran_amt DOUBLE PRECISION := 0.0;
848 temp DOUBLE PRECISION := 0.0;
849 loan_id INTEGER := NULL;
850 var_plan_curs REFCURSOR;
851 var_plan_rec RECORD;
852 con VARCHAR := '';
853 ord VARCHAR := '';
854 tran_type VARCHAR := '';
855 tran_operation VARCHAR := '';
856 BEGIN
857 IF TG_OP = 'INSERT' THEN
858 tran_operation := NEW."operation";
859 loan_id := NEW."LoanId";
860 tran_amt:= NEW."amount";
861 tran_type:= NEW."type";
862 ELSEIF TG_OP = 'UPDATE' THEN
863 tran_operation := NEW."operation";
864 loan_id := NEW."LoanId";
865 tran_amt:= NEW."amount" - OLD."amount";
866 tran_type:= NEW."type";
867 ELSE
868 tran_operation := OLD."operation";
869 loan_id := OLD."LoanId";
870 tran_amt:= OLD."amount" * -1;
871 tran_type:= OLD."type";
872 END IF;
873
874 IF tran_type = 'DB' THEN
875 tran_amt := tran_amt * -1;
876 END IF;
877
878 IF tran_amt < 0 THEN
879 con := ' AND "LoanPlans"."valuePaid" > 0 ';
880 ord := 'DESC';
881 ELSE
882 con := ' AND "LoanPlans"."status" NOT IN (''PAYOUT'',''REFINANCIATED'') ';
883 ord := 'ASC';
884 END IF;
885
886 IF tran_type <> 'DB' OR tran_operation = 'REVERSE' THEN
887 IF tran_operation NOT IN ('REFINANCIATED') THEN
888 OPEN var_plan_curs FOR
889 EXECUTE 'SELECT * FROM public."LoanPlans"
890 WHERE "LoanPlans"."LoanId" = '||loan_id||'AND
891 "LoanPlans"."deletedAt" IS NULL '||con||'
892 ORDER BY "LoanPlans"."sequence" '|| ord ||';';
893 LOOP
894 FETCH var_plan_curs INTO var_plan_rec;
895 EXIT WHEN NOT FOUND;
896
897 IF tran_amt <> 0 THEN
898 IF tran_amt < 0 THEN
899 IF var_plan_rec."valuePaid" > (tran_amt * -1) THEN
900 temp := tran_amt;
901 ELSEIF var_plan_rec."valuePaid" > 0 THEN
902 temp := var_plan_rec."valuePaid" * -1;
903 ELSE
904 CONTINUE;
905 END IF;
906 var_plan_rec."valuePaid" := var_plan_rec."valuePaid" + temp;
907 tran_amt := tran_amt - temp;
908 ELSE
909 IF var_plan_rec."fee" - var_plan_rec."valuePaid" < tran_amt THEN
910 temp := var_plan_rec."fee" - var_plan_rec."valuePaid";
911 ELSE
912 temp := tran_amt;
913 END IF;
914 var_plan_rec."valuePaid" := var_plan_rec."valuePaid" + temp;
915 tran_amt := tran_amt - temp;
916 END IF;
917
918 IF var_plan_rec."valuePaid" >= var_plan_rec."fee" THEN
919 UPDATE public."LoanPlans"
920 SET
921 "valuePaid" = COALESCE(var_plan_rec."valuePaid",0),
922 "status" = 'PAYOUT',
923 "paidDate" = NOW()
924 WHERE
925 "LoanPlans"."id" = var_plan_rec."id";
926 ELSEIF var_plan_rec."status" IN ('PAYOUT','REFINANCIATED') THEN
927 UPDATE public."LoanPlans"
928 SET
929 "valuePaid" = COALESCE(var_plan_rec."valuePaid",0),
930 "status" = 'PENDING',
931 "paidDate" = null
932 WHERE
933 "LoanPlans"."id" = var_plan_rec."id";
934 ELSE
935 UPDATE public."LoanPlans"
936 SET "valuePaid" = COALESCE(var_plan_rec."valuePaid",0)
937 WHERE "LoanPlans"."id" = var_plan_rec."id";
938 END IF;
939 ELSE
940 EXIT;
941 END IF;
942 IF tran_amt = 0 THEN
943 EXIT;
944 END IF;
945 END LOOP;
946 CLOSE var_plan_curs;
947 ELSE
948 UPDATE public."LoanPlans"
949 SET "status" = 'REFINANCIATED'
950 WHERE
951 "LoanPlans"."LoanId" = loan_id AND
952 "LoanPlans"."deletedAt" IS NULL AND
953 "LoanPlans"."status" <> 'PAYOUT';
954 END IF;
955 END IF;
956
957 SELECT
958 SUM(
959 CASE
960 WHEN "LoanPlans"."valuePaid" > "LoanPlans"."capitalValue" THEN "LoanPlans"."capitalValue"
961 ELSE "LoanPlans"."valuePaid"
962 END
963 ) INTO cap
964 FROM public."LoanPlans"
965 WHERE
966 "LoanPlans"."LoanId" = loan_id AND
967 "LoanPlans"."deletedAt" IS NULL;
968
969 SELECT
970 SUM(
971 CASE
972 WHEN "Transactions"."type" = 'CR' THEN "Transactions"."amount"
973 ELSE "Transactions"."amount" * -1
974 END
975 ) INTO bal
976 FROM public."Transactions"
977 WHERE
978 "Transactions"."source" = 'LOAN' AND
979 "Transactions"."LoanId" = loan_id AND
980 "Transactions"."sourceId" = loan_id AND
981 "Transactions"."operation" NOT IN ('REFINANCIANCE','INITIAL') AND
982 "Transactions"."deletedAt" IS NULL;
983
984 UPDATE public."Loans"
985 SET "balance" = COALESCE(cap - "originalValue",0),
986 "lastPaymentDate" = NOW(),
987 "totalPaid" = COALESCE(bal,0)
988 WHERE "Loans"."id" = loan_id;
989
990 IF TG_OP <> 'DELETE' THEN
991 return NEW;
992 ELSE
993 return OLD;
994 END IF;
995 END;
996$$ LANGUAGE plpgsql;
997
998CREATE TRIGGER loan_balance AFTER INSERT OR UPDATE
999ON public."Transactions" FOR EACH ROW
1000 WHEN (NEW."source" = 'LOAN')
1001 EXECUTE PROCEDURE loan_balance();
1002
1003CREATE TRIGGER loan_balance_delete AFTER DELETE
1004ON public."Transactions" FOR EACH ROW
1005 WHEN (OLD."source" = 'LOAN')
1006 EXECUTE PROCEDURE loan_balance();CREATE OR REPLACE FUNCTION loan_status() RETURNS TRIGGER AS $$
1007 DECLARE
1008 BEGIN
1009 IF NEW."balance" = 0 AND OLD."balance" < 0 THEN
1010 NEW."status" := 'PAYOUT';
1011 END IF;
1012
1013 IF NEW."refinancied" = TRUE THEN
1014 NEW."status" := 'REFINANCIATED';
1015 END IF;
1016
1017 return NEW;
1018 END;
1019$$ LANGUAGE plpgsql;
1020
1021CREATE TRIGGER loan_status BEFORE UPDATE OF "balance", "refinancied"
1022 ON public."Loans" FOR EACH ROW
1023 EXECUTE PROCEDURE loan_status();CREATE OR REPLACE FUNCTION interest_paid_loan() RETURNS TRIGGER AS $$
1024 DECLARE
1025 bal DOUBLE PRECISION := 0.0;
1026 loan_id INTEGER := NULL;
1027 BEGIN
1028 loan_id := NEW."LoanId";
1029
1030 SELECT
1031 SUM("LoanPlans"."interestValue") INTO bal
1032 FROM public."LoanPlans"
1033 WHERE
1034 "LoanPlans"."LoanId" = loan_id AND
1035 "LoanPlans"."deletedAt" IS NULL AND
1036 "LoanPlans"."status" = 'PAYOUT';
1037
1038
1039 UPDATE public."Loans"
1040 SET "interestCharge" = bal
1041 WHERE "Loans"."id" = loan_id;
1042
1043 return NEW;
1044 END;
1045$$ LANGUAGE plpgsql;
1046
1047CREATE TRIGGER interest_paid_loan AFTER UPDATE OF "paidDate"
1048 ON public."LoanPlans" FOR EACH ROW
1049 EXECUTE PROCEDURE interest_paid_loan();CREATE OR REPLACE FUNCTION other_deductions_sum() RETURNS TRIGGER AS $$
1050 DECLARE
1051 totW DOUBLE PRECISION := 0.0;
1052 totP DOUBLE PRECISION := 0.0;
1053 driver_id INTEGER := NULL;
1054 start_dat DATE := NULL;
1055 end_dat DATE := NULL;
1056 close_batch BOOLEAN := FALSE;
1057 BEGIN
1058
1059 SELECT "weekDate", "weekEndDate" INTO start_dat, end_dat
1060 FROM public."SettlementsHomes" LIMIT 1;
1061
1062 IF TG_TABLE_NAME = 'Deductions' THEN
1063 IF TG_OP <> 'DELETE' THEN
1064 driver_id := NEW."DriverId";
1065 ELSE
1066 driver_id := OLD."DriverId";
1067 END IF;
1068 ELSE
1069 IF TG_OP <> 'DELETE' THEN
1070 SELECT "Deductions"."DriverId" INTO driver_id
1071 FROM public."Deductions"
1072 WHERE "Deductions"."id" = NEW."DeductionId"
1073 LIMIT 1;
1074 ELSE
1075 SELECT "Deductions"."DriverId" INTO driver_id
1076 FROM public."Deductions"
1077 WHERE "Deductions"."id" = OLD."DeductionId"
1078 LIMIT 1;
1079 END IF;
1080 END IF;
1081
1082 SELECT "batchReady" INTO close_batch FROM public."SettlementsHomes"
1083 WHERE "DriverId" = driver_id;
1084
1085 IF close_batch = TRUE THEN
1086 RETURN NULL;
1087 END IF;
1088
1089 SELECT SUM("Deductions"."autoWithdrawValue") INTO totW
1090 FROM public."Deductions"
1091 LEFT JOIN public."DeductionTypes"
1092 ON "Deductions"."DeductionTypeId" = "DeductionTypes"."id"
1093 WHERE
1094 "Deductions"."deletedAt" IS NULL AND
1095 ("Deductions"."DeductionTypeId" IS NULL OR
1096 "DeductionTypes"."homeShow" = FALSE) AND
1097 "Deductions"."DriverId" = driver_id AND
1098 "Deductions"."status" = 'ACTIVE' AND
1099 "Deductions"."type" NOT IN ('FINITE PLAN','RENEWABLE PLAN');
1100
1101 SELECT SUM("DeductionPlans"."fee" - "DeductionPlans"."valuePaid") INTO totP
1102 FROM
1103 (SELECT
1104 "Deductions"."id",
1105 "Deductions"."deletedAt",
1106 "Deductions"."status"
1107 FROM public."Deductions"
1108 LEFT JOIN public."DeductionTypes"
1109 ON "Deductions"."DeductionTypeId" = "DeductionTypes"."id"
1110 WHERE
1111 "Deductions"."deletedAt" IS NULL AND
1112 ("Deductions"."DeductionTypeId" IS NULL OR
1113 "DeductionTypes"."homeShow" = FALSE) AND
1114 "Deductions"."DriverId" = driver_id AND
1115 "Deductions"."status" = 'ACTIVE' AND
1116 "Deductions"."type" IN ('FINITE PLAN','RENEWABLE PLAN')) AS "deduct"
1117 INNER JOIN public."DeductionPlans"
1118 ON "deduct"."id" = "DeductionPlans"."DeductionId"
1119 WHERE
1120 "deduct"."deletedAt" IS NULL AND
1121 "deduct"."status" IN ('ACTIVE') AND
1122 "DeductionPlans"."deletedAt" IS NULL AND
1123 "DeductionPlans"."status" IN ('PENDING', 'LATE') AND
1124 "DeductionPlans"."date"::DATE < end_dat;
1125
1126 UPDATE public."SettlementsHomes"
1127 SET "otherDeductions" = COALESCE(totP,0) + COALESCE(totW,0)
1128 WHERE "SettlementsHomes"."DriverId" = driver_id;
1129
1130 IF TG_OP <> 'DELETE' THEN
1131 return NEW;
1132 ELSE
1133 return OLD;
1134 END IF;
1135 END;
1136$$ LANGUAGE plpgsql;
1137
1138CREATE TRIGGER deduction_sum_batch AFTER INSERT OR UPDATE OR DELETE
1139 ON public."DeductionPlans" FOR EACH ROW
1140 EXECUTE PROCEDURE other_deductions_sum();
1141
1142CREATE TRIGGER deduction_sum_batch_ded AFTER INSERT OR UPDATE OR DELETE
1143 ON public."Deductions" FOR EACH ROW
1144 EXECUTE PROCEDURE other_deductions_sum();CREATE extension tablefunc;
1145CREATE OR REPLACE FUNCTION settlement_home_query() RETURNS TRIGGER AS $$
1146 DECLARE
1147 sql_query TEXT := '';
1148 columns TEXT := '';
1149 var_plan_curs REFCURSOR;
1150 var_plan_rec RECORD;
1151BEGIN
1152 OPEN var_plan_curs FOR
1153 SELECT 'FIELD_'||"id" AS "identify" FROM public."DeductionTypes" WHERE "homeShow" = TRUE;
1154 LOOP
1155 FETCH var_plan_curs INTO var_plan_rec;
1156 EXIT WHEN NOT FOUND;
1157 sql_query := sql_query || ', '|| var_plan_rec."identify"|| ' float8';
1158 columns := columns || ' - COALESCE("T".' || var_plan_rec."identify" ||',0)';
1159 END LOOP;
1160 CLOSE var_plan_curs;
1161 sql_query := sql_query || ') ';
1162 columns := columns || ') ';
1163
1164 UPDATE public."cParams"
1165 SET "value" = sql_query::VARCHAR
1166 WHERE "cParams"."variableName" = 'SETTLEMENT_PIVOT';
1167
1168 UPDATE public."cParams"
1169 SET "value" = columns::VARCHAR
1170 WHERE "cParams"."variableName" = 'SETTLEMENT_COLUMNS';
1171
1172 return NEW;
1173END;
1174$$ LANGUAGE plpgsql;
1175
1176CREATE TRIGGER settlement_home_query AFTER INSERT OR DELETE OR UPDATE OF "homeShow"
1177 ON public."DeductionTypes" FOR EACH ROW
1178 EXECUTE PROCEDURE settlement_home_query();CREATE OR REPLACE FUNCTION FIND_RULE_PARAMS(
1179 start_dte TIMESTAMP,
1180 end_dte TIMESTAMP,
1181 origin_state VARCHAR,
1182 dest_state VARCHAR,
1183 trailer_type VARCHAR,
1184 product_type VARCHAR,
1185 rule_type VARCHAR,
1186 old_version BOOLEAN )
1187RETURNS TABLE("RuleId" INT, "noCondition" INT, "priority" INT) AS $$
1188DECLARE
1189 count_val INT := NULL;
1190BEGIN
1191 SET enable_seqscan = off;
1192 IF start_dte IS NULL OR end_dte IS NULL THEN
1193 RETURN QUERY SELECT 0 AS "RuleId", 0 AS "noCondition", 0 AS "priority" WHERE FALSE;
1194 RETURN;
1195 END IF;
1196
1197 SELECT COUNT(*) INTO count_val FROM public."Rules" WHERE "Rules"."deletedAt" IS NULL;
1198 IF count_val = 0 THEN
1199 RETURN QUERY SELECT 0 AS "RuleId", 0 AS "noCondition", 0 AS "priority" WHERE FALSE;
1200 RETURN;
1201 END IF;
1202
1203 RETURN QUERY
1204 SELECT
1205 "t"."RuleId",
1206 "t"."noConditions",
1207 "t"."priority"
1208 FROM
1209 (SELECT
1210 "Rules"."id" as "RuleId",
1211 "Rules"."noConditions",
1212 "Rules"."priority",
1213 "Rules"."validFrom",
1214 "Rules"."validTo",
1215 COALESCE(
1216 (SELECT
1217 SUM(CASE
1218 WHEN "RuleConditions"."type" = 'ORIGIN' THEN
1219 CASE
1220 WHEN "RuleConditions"."strValue" = origin_state THEN 1
1221 ELSE 0
1222 END
1223 WHEN "RuleConditions"."type" = 'DESTINATION' THEN
1224 CASE
1225 WHEN "RuleConditions"."strValue" = dest_state THEN 1
1226 ELSE 0
1227 END
1228 WHEN "RuleConditions"."type" = 'TRAILER TYPE' THEN
1229 CASE
1230 WHEN "RuleConditions"."strValue" = trailer_type THEN 1
1231 ELSE 0
1232 END
1233 WHEN "RuleConditions"."type" = 'PRODUCT TYPE' THEN
1234 CASE
1235 WHEN "RuleConditions"."strValue" = product_type THEN 1
1236 ELSE 0
1237 END
1238 ELSE 0
1239 END)
1240 FROM public."RuleConditions"
1241 WHERE "RuleConditions"."RuleId" = "Rules"."id"), 0) AS "assert"
1242 FROM public."Rules" "Rules"
1243 WHERE
1244 "Rules"."deletedAt" IS NULL AND
1245 "Rules"."type"::VARCHAR = rule_type AND
1246 ("Rules"."validFrom"::TIMESTAMP - '1 day'::INTERVAL) <= (start_dte ::TIMESTAMP) AND
1247 COALESCE("Rules"."validTo" ,FIX_BAD_DATATYPE('9999-12-31')) >= (end_dte::TIMESTAMP) AND
1248 "Rules"."status" = 'ACTIVE') AS "t"
1249 WHERE "t"."assert" = "t"."noConditions"
1250 ORDER BY "t"."priority" DESC, "t"."noConditions" DESC, "validTo" NULLS LAST
1251 LIMIT 1;
1252END;
1253$$ LANGUAGE plpgsql VOLATILE;CREATE OR REPLACE FUNCTION trip_code() RETURNS TRIGGER AS $$
1254 DECLARE
1255 code VARCHAR := '';
1256 BEGIN
1257 SELECT GENERATE_TRIPNO(6,'ALPHA') INTO code;
1258 NEW."tripNo" = code;
1259 return NEW;
1260 END;
1261$$ LANGUAGE plpgsql;
1262
1263CREATE TRIGGER trip_code BEFORE INSERT
1264 ON public."Trips" FOR EACH ROW
1265 EXECUTE PROCEDURE trip_code();CREATE SEQUENCE IF NOT EXISTS bill_no_sequence INCREMENT BY 1 START 1;
1266SELECT setval('bill_no_sequence', (SELECT MAX(CAST(COALESCE("billNo",'0') AS INTEGER)) + 1 FROM public."Invoices"), FALSE);
1267
1268CREATE OR REPLACE FUNCTION bill_no() RETURNS TRIGGER AS $$
1269 DECLARE
1270 code VARCHAR := '';
1271 BEGIN
1272 SELECT LPAD(nextval('bill_no_sequence')::TEXT,6,'0') INTO code;
1273 NEW."billNo" = code;
1274 return NEW;
1275 END;
1276$$ LANGUAGE plpgsql;
1277
1278CREATE TRIGGER bill_no BEFORE INSERT
1279 ON public."Invoices" FOR EACH ROW
1280 EXECUTE PROCEDURE bill_no();CREATE OR REPLACE FUNCTION stop_code() RETURNS TRIGGER AS $$
1281 DECLARE
1282 code VARCHAR := '';
1283 BEGIN
1284 SELECT GENERATE_STOPNO(6,'ALPHA') INTO code;
1285 NEW."stopNo" = code;
1286 return NEW;
1287 END;
1288$$ LANGUAGE plpgsql;
1289
1290CREATE TRIGGER stop_code BEFORE INSERT
1291 ON public."TripStops" FOR EACH ROW
1292 EXECUTE PROCEDURE stop_code();CREATE OR REPLACE FUNCTION earnings_sum_batch() RETURNS TRIGGER AS $$
1293 DECLARE
1294 earnings DOUBLE PRECISION := 0.0;
1295 on_hold DOUBLE PRECISION := 0.0;
1296 driver_id INTEGER := NULL;
1297 close_batch BOOLEAN := FALSE;
1298 BEGIN
1299 IF TG_OP <> 'DELETE' THEN
1300 driver_id := NEW."DriverId";
1301 ELSE
1302 driver_id := OLD."DriverId";
1303 END IF;
1304
1305 SELECT "batchReady" INTO close_batch FROM public."SettlementsHomes"
1306 WHERE "DriverId" = driver_id;
1307
1308 IF close_batch = TRUE THEN
1309 RETURN NULL;
1310 END IF;
1311
1312 SELECT
1313 SUM(
1314 COALESCE(
1315 CASE
1316 WHEN "PaymentTrips"."status" IN ('ON REVIEW') THEN
1317 "PaymentTrips"."net"
1318 WHEN "PaymentTrips"."status" IN ('ON HOLD') THEN
1319 "PaymentTrips"."net"
1320 ELSE 0
1321 END
1322 , 0)
1323 ),
1324 SUM(
1325 COALESCE(
1326 CASE
1327 WHEN "PaymentTrips"."status" IN ('READY') THEN
1328 "PaymentTrips"."net"
1329 ELSE 0
1330 END
1331 , 0)
1332 )
1333 INTO on_hold, earnings
1334 FROM public."PaymentTrips"
1335 INNER JOIN public."Trips"
1336 ON "Trips"."id" = "PaymentTrips"."TripId"
1337 WHERE
1338 "PaymentTrips"."DriverId" = driver_id AND
1339 "Trips"."deletedAt" IS NULL AND
1340 "PaymentTrips"."deletedAt" IS NULL AND
1341 "PaymentTrips"."status" IN ('ON REVIEW', 'ON HOLD', 'READY');
1342
1343 UPDATE public."SettlementsHomes"
1344 SET
1345 "earn" = COALESCE(earnings, 0),
1346 "earnHold" = COALESCE(on_hold, 0)
1347 WHERE "SettlementsHomes"."DriverId" = driver_id;
1348
1349 IF TG_OP <> 'DELETE' THEN
1350 return NEW;
1351 ELSE
1352 return OLD;
1353 END IF;
1354 END;
1355$$ LANGUAGE plpgsql;
1356
1357CREATE TRIGGER earnings_sum_batch AFTER INSERT OR UPDATE OR DELETE
1358 ON public."PaymentTrips" FOR EACH ROW
1359 EXECUTE PROCEDURE earnings_sum_batch();CREATE OR REPLACE FUNCTION batch_fuel() RETURNS TRIGGER AS $$
1360 DECLARE
1361 fuel_ref_curs REFCURSOR;
1362 fuel_rec RECORD;
1363 close_batch BOOLEAN := FALSE;
1364 BEGIN
1365 OPEN fuel_ref_curs FOR
1366 SELECT
1367 "Drivers"."id" AS "DriverId",
1368 "Drivers"."TcheckCardNo" AS "TcheckCardNo",
1369 SUM("Tchecks"."calculatedTotalAmount") AS "total"
1370 FROM public."SettlementsHomes"
1371 INNER JOIN public."Drivers"
1372 ON "SettlementsHomes"."DriverId" = "Drivers"."id"
1373 INNER JOIN public."Tchecks"
1374 ON "Tchecks"."card" = "Drivers"."TcheckCardNo"
1375 WHERE
1376 "Tchecks"."deletedAt" IS NULL AND
1377 "Tchecks"."isDriverTransaction" IS TRUE
1378 GROUP BY "Drivers"."id", "Drivers"."TcheckCardNo";
1379 LOOP
1380 FETCH fuel_ref_curs INTO fuel_rec;
1381 EXIT WHEN NOT FOUND;
1382
1383 close_batch := FALSE;
1384
1385 SELECT "batchReady" INTO close_batch FROM public."SettlementsHomes"
1386 WHERE "DriverId" = fuel_rec."DriverId";
1387
1388 IF close_batch = FALSE THEN
1389 UPDATE public."SettlementsHomes"
1390 SET "fuel" = fuel_rec."total"
1391 WHERE "DriverId" = fuel_rec."DriverId";
1392 END IF;
1393
1394 END LOOP;
1395 CLOSE fuel_ref_curs;
1396 RETURN NULL;
1397 END;
1398$$ LANGUAGE plpgsql;
1399
1400CREATE TRIGGER batch_fuel AFTER INSERT OR UPDATE OR DELETE
1401 ON public."Tchecks" FOR EACH STATEMENT
1402 EXECUTE PROCEDURE batch_fuel();CREATE OR REPLACE FUNCTION from_date_nearest(rule_id INTEGER, date_from DATE)RETURNS INTEGER AS $$
1403DECLARE
1404 output INTEGER;
1405BEGIN
1406 SET enable_seqscan = off;
1407 SELECT "T"."version" INTO output
1408 FROM
1409 (SELECT "version"
1410 FROM public."RuleCharges"
1411 WHERE
1412 "validFrom" <= date_from::TIMESTAMP AND
1413 "RuleId" = rule_id
1414 LIMIT 1) AS "T";
1415
1416 RETURN output;
1417END;
1418$$ LANGUAGE plpgsql VOLATILE;CREATE OR REPLACE FUNCTION other_deductions_sum_masive() RETURNS TRIGGER AS $$
1419 DECLARE
1420 totW DOUBLE PRECISION := 0.0;
1421 totP DOUBLE PRECISION := 0.0;
1422 driver_id INTEGER := NULL;
1423 driver_ref_curs REFCURSOR;
1424 driver_rec RECORD;
1425 start_dat DATE := NULL;
1426 end_dat DATE := NULL;
1427 close_batch BOOLEAN := FALSE;
1428 BEGIN
1429 SELECT "weekDate", "weekEndDate" INTO start_dat, end_dat
1430 FROM public."SettlementsHomes" LIMIT 1;
1431
1432 OPEN driver_ref_curs FOR
1433 SELECT "DriverId" FROM public."SettlementsHomes";
1434 LOOP
1435 FETCH driver_ref_curs INTO driver_rec;
1436 EXIT WHEN NOT FOUND;
1437
1438 driver_id := driver_rec."DriverId";
1439 SELECT "batchReady" INTO close_batch FROM public."SettlementsHomes"
1440 WHERE "DriverId" = driver_id;
1441
1442 IF close_batch = FALSE THEN
1443 SELECT SUM("Deductions"."autoWithdrawValue") INTO totW
1444 FROM public."Deductions"
1445 LEFT JOIN public."DeductionTypes"
1446 ON "Deductions"."DeductionTypeId" = "DeductionTypes"."id"
1447 WHERE
1448 "Deductions"."deletedAt" IS NULL AND
1449 ("Deductions"."DeductionTypeId" IS NULL OR
1450 "DeductionTypes"."homeShow" = FALSE) AND
1451 "Deductions"."DriverId" = driver_id AND
1452 "Deductions"."status" = 'ACTIVE' AND
1453 "Deductions"."type" NOT IN ('FINITE PLAN','RENEWABLE PLAN');
1454
1455 SELECT SUM("DeductionPlans"."fee" - "DeductionPlans"."valuePaid") INTO totP
1456 FROM
1457 (SELECT
1458 "Deductions"."id",
1459 "Deductions"."deletedAt",
1460 "Deductions"."status"
1461 FROM public."Deductions"
1462 LEFT JOIN public."DeductionTypes"
1463 ON "Deductions"."DeductionTypeId" = "DeductionTypes"."id"
1464 WHERE
1465 "Deductions"."deletedAt" IS NULL AND
1466 ("Deductions"."DeductionTypeId" IS NULL OR
1467 "DeductionTypes"."homeShow" = FALSE) AND
1468 "Deductions"."DriverId" = driver_id AND
1469 "Deductions"."status" = 'ACTIVE' AND
1470 "Deductions"."type" IN ('FINITE PLAN','RENEWABLE PLAN')) AS "deduct"
1471 INNER JOIN public."DeductionPlans"
1472 ON "deduct"."id" = "DeductionPlans"."DeductionId"
1473 WHERE
1474 "deduct"."deletedAt" IS NULL AND
1475 "deduct"."status" IN ('ACTIVE') AND
1476 "DeductionPlans"."deletedAt" IS NULL AND
1477 "DeductionPlans"."status" IN ('PENDING', 'LATE') AND
1478 "DeductionPlans"."date"::DATE < end_dat;
1479
1480 UPDATE public."SettlementsHomes"
1481 SET "otherDeductions" = COALESCE(totP,0) + COALESCE(totW,0)
1482 WHERE "SettlementsHomes"."DriverId" = driver_id;
1483 END IF;
1484 END LOOP;
1485 CLOSE driver_ref_curs;
1486 RETURN NULL;
1487 END;
1488$$ LANGUAGE plpgsql;
1489
1490CREATE TRIGGER other_deductions_sum_masive AFTER DELETE OR UPDATE OF "homeShow"
1491 ON public."DeductionTypes" FOR EACH ROW
1492 EXECUTE PROCEDURE other_deductions_sum_masive();CREATE OR REPLACE VIEW "readyForBillingView" AS
1493 SELECT
1494 "T2"."tripNo",
1495 "T2"."driverName",
1496 "T2"."isTeam",
1497 "T2"."tractorNo",
1498 "T2"."trailerNo",
1499 "T2"."actualDeliveryDate",
1500 "T2"."invoiceUUID",
1501 "T2"."tripUUID",
1502 "T2"."approved",
1503 SUM("T2"."totalMiles") AS "totalMiles",
1504 SUM("T2"."LHRate") AS "LHRate",
1505 SUM("T2"."LHAmt") AS "LHAmt",
1506 SUM("T2"."teamRate") AS "teamRate",
1507 SUM("T2"."teamAmt") AS "teamAmt",
1508 SUM("T2"."flat") AS "flat",
1509 SUM("T2"."LDHrs") AS "LDHrs",
1510 SUM("T2"."LDRate") AS "LDRate",
1511 SUM("T2"."LDAmt") AS "LDAmt",
1512 SUM("T2"."UnldHrs") AS "UnldHrs",
1513 SUM("T2"."UnldRate") AS "UnldRate",
1514 SUM("T2"."UnldAmt") AS "UnldAmt",
1515 SUM("T2"."LDHrsCA") AS "LDHrsCA",
1516 SUM("T2"."LDRateCA") AS "LDRateCA",
1517 SUM("T2"."LDAmtCA") AS "LDAmtCA",
1518 SUM("T2"."UnldHrsCA") AS "UnldHrsCA",
1519 SUM("T2"."UnldRateCA") AS "UnldRateCA",
1520 SUM("T2"."UnldAmtCA") AS "UnldAmtCA",
1521 SUM("T2"."stops") AS "stops",
1522 SUM("T2"."stopRate") AS "stopRate",
1523 SUM("T2"."stopAmt") AS "stopAmt",
1524 SUM("T2"."FSCRate") AS "FSCRate",
1525 SUM("T2"."FSCAmt") AS "FSCAmt",
1526 SUM("T2"."insSub") AS "insSub",
1527 SUM("T2"."DriverId") AS "DriverId",
1528 SUM("T2"."ClientId") AS "ClientId",
1529 SUM("T2"."id" ) AS "TripId"
1530 FROM
1531 (SELECT
1532 "T"."tripNo",
1533 "T"."approved",
1534 "T"."driverName",
1535 "T"."isTeam",
1536 "T"."tractorNo",
1537 "T"."trailerNo",
1538 "T"."actualDeliveryDate",
1539 "T"."totalMiles",
1540 TO_NUMBER(NULLIF(TRIM(SUBSTRING("T"."chargesInfo" FROM 0 FOR 12)),''), '9999999D9999')::FLOAT AS "LHRate",
1541 TO_NUMBER(NULLIF(TRIM(SUBSTRING("T"."chargesInfo" FROM 12 FOR 12)),''), '9999999D9999')::FLOAT AS "LHAmt",
1542 TO_NUMBER(NULLIF(TRIM(SUBSTRING("T"."chargesInfo" FROM 24 FOR 12)),''), '9999999D9999')::FLOAT AS "teamRate",
1543 TO_NUMBER(NULLIF(TRIM(SUBSTRING("T"."chargesInfo" FROM 36 FOR 12)),''), '9999999D9999')::FLOAT AS "teamAmt",
1544 TO_NUMBER(NULLIF(TRIM(SUBSTRING("T"."chargesInfo" FROM 48 FOR 12)),''), '9999999D9999')::FLOAT AS "flat",
1545 TO_NUMBER(NULLIF(TRIM(SUBSTRING("T"."chargesInfo" FROM 60 FOR 12)),''), '9999999D9999')::FLOAT AS "LDHrs",
1546 TO_NUMBER(NULLIF(TRIM(SUBSTRING("T"."chargesInfo" FROM 72 FOR 12)),''), '9999999D9999')::FLOAT AS "LDRate",
1547 TO_NUMBER(NULLIF(TRIM(SUBSTRING("T"."chargesInfo" FROM 84 FOR 12)),''), '9999999D9999')::FLOAT AS "LDAmt",
1548 TO_NUMBER(NULLIF(TRIM(SUBSTRING("T"."chargesInfo" FROM 96 FOR 12)),''), '9999999D9999')::FLOAT AS "UnldHrs",
1549 TO_NUMBER(NULLIF(TRIM(SUBSTRING("T"."chargesInfo" FROM 108 FOR 12)),''), '9999999D9999')::FLOAT AS "UnldRate",
1550 TO_NUMBER(NULLIF(TRIM(SUBSTRING("T"."chargesInfo" FROM 120 FOR 12)),''), '9999999D9999')::FLOAT AS "UnldAmt",
1551 TO_NUMBER(NULLIF(TRIM(SUBSTRING("T"."chargesInfo" FROM 132 FOR 12)),''), '9999999D9999')::FLOAT AS "LDHrsCA",
1552 TO_NUMBER(NULLIF(TRIM(SUBSTRING("T"."chargesInfo" FROM 144 FOR 12)),''), '9999999D9999')::FLOAT AS "LDRateCA",
1553 TO_NUMBER(NULLIF(TRIM(SUBSTRING("T"."chargesInfo" FROM 156 FOR 12)),''), '9999999D9999')::FLOAT AS "LDAmtCA",
1554 TO_NUMBER(NULLIF(TRIM(SUBSTRING("T"."chargesInfo" FROM 168 FOR 12)),''), '9999999D9999')::FLOAT AS "UnldHrsCA",
1555 TO_NUMBER(NULLIF(TRIM(SUBSTRING("T"."chargesInfo" FROM 180 FOR 12)),''), '9999999D9999')::FLOAT AS "UnldRateCA",
1556 TO_NUMBER(NULLIF(TRIM(SUBSTRING("T"."chargesInfo" FROM 192 FOR 12)),''), '9999999D9999')::FLOAT AS "UnldAmtCA",
1557 TO_NUMBER(NULLIF(TRIM(SUBSTRING("T"."chargesInfo" FROM 204 FOR 12)),''), '9999999D9999')::FLOAT AS "stops",
1558 TO_NUMBER(NULLIF(TRIM(SUBSTRING("T"."chargesInfo" FROM 216 FOR 12)),''), '9999999D9999')::FLOAT AS "stopRate",
1559 TO_NUMBER(NULLIF(TRIM(SUBSTRING("T"."chargesInfo" FROM 228 FOR 12)),''), '9999999D9999')::FLOAT AS "stopAmt",
1560 TO_NUMBER(NULLIF(TRIM(SUBSTRING("T"."chargesInfo" FROM 240 FOR 12)),''), '9999999D9999')::FLOAT AS "FSCRate",
1561 TO_NUMBER(NULLIF(TRIM(SUBSTRING("T"."chargesInfo" FROM 252 FOR 12)),''), '9999999D9999')::FLOAT AS "FSCAmt",
1562 TO_NUMBER(NULLIF(TRIM(SUBSTRING("T"."chargesInfo" FROM 264 FOR 12)),''), '9999999D9999')::FLOAT AS "insSub",
1563 "T"."DriverId",
1564 "T"."ClientId",
1565 "T"."invoiceUUID",
1566 "T"."tripUUID",
1567 "T"."id"
1568 FROM
1569 (SELECT
1570 "Trips"."approved" AS "approved",
1571 COALESCE(NULLIF(TRIM("Trips"."tripNo"),''),'') AS "tripNo",
1572 COALESCE(NULLIF(TRIM("Trips"."driverName"),''),'') AS "driverName",
1573 CASE
1574 WHEN "Trips"."isTeam" = TRUE THEN
1575 'TEAM'
1576 ELSE
1577 ''
1578 END AS "isTeam",
1579 COALESCE(NULLIF(TRIM("Trips"."tractorNo"),''),'N/A') AS "tractorNo",
1580 COALESCE(NULLIF(TRIM("Trips"."trailerNo"),''),'N/A') AS "trailerNo",
1581 COALESCE(
1582 (SELECT
1583 TO_CHAR("TripStops"."endDate",'MM/DD/YYYY')
1584 FROM public."TripStops"
1585 WHERE
1586 "TripStops"."deletedAt" IS NULL AND
1587 "TripStops"."TripId" = "Trips"."id" AND
1588 "TripStops"."tripNo" = "Trips"."tripNo" AND
1589 "TripStops"."stopType"::VARCHAR = 'UNLOAD'
1590 ORDER BY "sequence" DESC
1591 LIMIT 1),
1592 ''
1593 ) AS "actualDeliveryDate",
1594 "Trips"."totalMiles" AS "totalMiles",
1595 COALESCE(
1596 (SELECT
1597 LPAD(COALESCE(TO_CHAR(SUM(CASE WHEN "InvoiceCharges"."rateType" = 'MILES' AND "InvoiceCharges"."chargeType" = 'VARIABLE' THEN "InvoiceCharges"."unitValue"::FLOAT END)::FLOAT, '9999999D9999'),' ') , 12, ' ') ||
1598 LPAD(COALESCE(TO_CHAR(SUM(CASE WHEN "InvoiceCharges"."rateType" = 'MILES' AND "InvoiceCharges"."chargeType" = 'VARIABLE' THEN "InvoiceCharges"."total"::FLOAT END)::FLOAT, '9999999D9999'),' ') , 12, ' ') ||
1599 LPAD(COALESCE(TO_CHAR(SUM(CASE WHEN "InvoiceCharges"."rateType" = 'TEAM' AND "InvoiceCharges"."chargeType" = 'VARIABLE' THEN "InvoiceCharges"."unitValue"::FLOAT END)::FLOAT, '9999999D9999'),' ') , 12, ' ') ||
1600 LPAD(COALESCE(TO_CHAR(SUM(CASE WHEN "InvoiceCharges"."rateType" = 'TEAM' AND "InvoiceCharges"."chargeType" = 'VARIABLE' THEN "InvoiceCharges"."total"::FLOAT END)::FLOAT, '9999999D9999'),' ') , 12, ' ') ||
1601 LPAD(COALESCE(TO_CHAR(SUM(CASE WHEN "InvoiceCharges"."rateType" = 'MILES' AND "InvoiceCharges"."chargeType" = 'FLAT' THEN "InvoiceCharges"."total"::FLOAT END)::FLOAT, '9999999D9999'),' ') , 12, ' ') ||
1602 LPAD(COALESCE(TO_CHAR(SUM(CASE WHEN "InvoiceCharges"."rateType" = 'LOAD TIME' AND "Trips"."originLocState" <> 'CA' THEN "InvoiceCharges"."quantity"::FLOAT END)::FLOAT, '9999999D9999'),' ') , 12, ' ') ||
1603 LPAD(COALESCE(TO_CHAR(SUM(CASE WHEN "InvoiceCharges"."rateType" = 'LOAD TIME' AND "Trips"."originLocState" <> 'CA' THEN "InvoiceCharges"."unitValue"::FLOAT END)::FLOAT, '9999999D9999'),' ') , 12, ' ') ||
1604 LPAD(COALESCE(TO_CHAR(SUM(CASE WHEN "InvoiceCharges"."rateType" = 'LOAD TIME' AND "Trips"."originLocState" <> 'CA' THEN "InvoiceCharges"."total"::FLOAT END)::FLOAT, '9999999D9999'),' ') , 12, ' ') ||
1605 LPAD(COALESCE(TO_CHAR(SUM(CASE WHEN "InvoiceCharges"."rateType" = 'UNLOAD TIME' AND "Trips"."originLocState" <> 'CA' THEN "InvoiceCharges"."quantity"::FLOAT END)::FLOAT, '9999999D9999'),' ') , 12, ' ') ||
1606 LPAD(COALESCE(TO_CHAR(SUM(CASE WHEN "InvoiceCharges"."rateType" = 'UNLOAD TIME' AND "Trips"."originLocState" <> 'CA' THEN "InvoiceCharges"."unitValue"::FLOAT END)::FLOAT, '9999999D9999'),' ') , 12, ' ') ||
1607 LPAD(COALESCE(TO_CHAR(SUM(CASE WHEN "InvoiceCharges"."rateType" = 'UNLOAD TIME' AND "Trips"."originLocState" <> 'CA' THEN "InvoiceCharges"."total"::FLOAT END)::FLOAT, '9999999D9999'),' ') , 12, ' ') ||
1608 LPAD(COALESCE(TO_CHAR(SUM(CASE WHEN "InvoiceCharges"."rateType" = 'LOAD TIME' AND "Trips"."originLocState" = 'CA' THEN "InvoiceCharges"."quantity"::FLOAT END)::FLOAT, '9999999D9999'),' ') , 12, ' ') ||
1609 LPAD(COALESCE(TO_CHAR(SUM(CASE WHEN "InvoiceCharges"."rateType" = 'LOAD TIME' AND "Trips"."originLocState" = 'CA' THEN "InvoiceCharges"."unitValue"::FLOAT END)::FLOAT, '9999999D9999'),' ') , 12, ' ') ||
1610 LPAD(COALESCE(TO_CHAR(SUM(CASE WHEN "InvoiceCharges"."rateType" = 'LOAD TIME' AND "Trips"."originLocState" = 'CA' THEN "InvoiceCharges"."total"::FLOAT END)::FLOAT, '9999999D9999'),' ') , 12, ' ') ||
1611 LPAD(COALESCE(TO_CHAR(SUM(CASE WHEN "InvoiceCharges"."rateType" = 'UNLOAD TIME' AND "Trips"."originLocState" = 'CA' THEN "InvoiceCharges"."quantity"::FLOAT END)::FLOAT, '9999999D9999'),' ') , 12, ' ') ||
1612 LPAD(COALESCE(TO_CHAR(SUM(CASE WHEN "InvoiceCharges"."rateType" = 'UNLOAD TIME' AND "Trips"."originLocState" = 'CA' THEN "InvoiceCharges"."unitValue"::FLOAT END)::FLOAT, '9999999D9999'),' ') , 12, ' ') ||
1613 LPAD(COALESCE(TO_CHAR(SUM(CASE WHEN "InvoiceCharges"."rateType" = 'UNLOAD TIME' AND "Trips"."originLocState" = 'CA' THEN "InvoiceCharges"."total"::FLOAT END)::FLOAT, '9999999D9999'),' ') , 12, ' ') ||
1614 LPAD(COALESCE(TO_CHAR(SUM(CASE WHEN "InvoiceCharges"."rateType" = 'STOP' THEN "InvoiceCharges"."quantity"::FLOAT END)::FLOAT, '9999999D9999'),' ') , 12, ' ') ||
1615 LPAD(COALESCE(TO_CHAR(SUM(CASE WHEN "InvoiceCharges"."rateType" = 'STOP' THEN "InvoiceCharges"."unitValue"::FLOAT END)::FLOAT, '9999999D9999'),' ') , 12, ' ') ||
1616 LPAD(COALESCE(TO_CHAR(SUM(CASE WHEN "InvoiceCharges"."rateType" = 'STOP' THEN "InvoiceCharges"."total"::FLOAT END)::FLOAT, '9999999D9999'),' ') , 12, ' ') ||
1617 LPAD(COALESCE(TO_CHAR(SUM(CASE WHEN "InvoiceCharges"."rateType" = 'FUEL' THEN "InvoiceCharges"."unitValue"::FLOAT END)::FLOAT, '9999999D9999'),' ') , 12, ' ') ||
1618 LPAD(COALESCE(TO_CHAR(SUM(CASE WHEN "InvoiceCharges"."rateType" = 'FUEL' THEN "InvoiceCharges"."total"::FLOAT END)::FLOAT, '9999999D9999'),' ') , 12, ' ') ||
1619 LPAD(COALESCE(TO_CHAR(SUM(CASE WHEN "InvoiceCharges"."rateType" = 'INSURANCE' THEN "InvoiceCharges"."total"::FLOAT END)::FLOAT, '9999999D9999'),' ') , 12, ' ') ||
1620 "InvoiceCharges"."TripId"
1621 FROM public."InvoiceCharges"
1622 WHERE
1623 "InvoiceCharges"."TripId" = "Trips"."id" AND
1624 "InvoiceCharges"."deletedAt" IS NULL AND
1625 "InvoiceCharges"."accesorialType" = 'RATE'
1626 GROUP BY "InvoiceCharges"."TripId"),
1627 RPAD(' ',277,' ')
1628 ) AS "chargesInfo",
1629 (SELECT
1630 "uuid"
1631 FROM public."Invoices"
1632 WHERE
1633 "Invoices"."deletedAt" IS NULL AND
1634 "Invoices"."TripId" = "Trips"."id"
1635 LIMIT 1) AS "invoiceUUID",
1636 "Trips"."DriverId" AS "DriverId",
1637 "Trips"."ClientId" AS "ClientId",
1638 "Trips"."uuid" AS "tripUUID",
1639 "Trips"."id" AS "id"
1640 FROM
1641 public."Trips" AS "Trips"
1642 WHERE
1643 "Trips"."deletedAt" IS NULL AND
1644 "Trips"."status" = 'BILLED') AS "T"
1645 ) AS "T2"
1646 GROUP BY
1647 ROLLUP (("T2"."tripNo",
1648 "T2"."driverName",
1649 "T2"."isTeam",
1650 "T2"."trailerNo",
1651 "T2"."actualDeliveryDate",
1652 "T2"."tractorNo",
1653 "T2"."invoiceUUID",
1654 "T2"."tripUUID",
1655 "T2"."approved"));
1656
1657 CREATE OR REPLACE FUNCTION from_date_nearest_payment(rule_id INTEGER, date_from DATE)RETURNS INTEGER AS $$
1658DECLARE
1659 output INTEGER;
1660BEGIN
1661 SET enable_seqscan = off;
1662 SELECT "T"."version" INTO output
1663 FROM
1664 (SELECT "version"
1665 FROM public."PaymentRules"
1666 WHERE
1667 "validFrom" <= date_from::TIMESTAMP AND
1668 "RuleId" = rule_id
1669 LIMIT 1) AS "T";
1670
1671 RETURN output;
1672END;
1673$$ LANGUAGE plpgsql VOLATILE;CREATE OR REPLACE FUNCTION tcheck_balance() RETURNS TRIGGER AS $$
1674 DECLARE
1675 bal DOUBLE PRECISION := 0.0;
1676 pending DOUBLE PRECISION := 0.0;
1677 tcheck_id INTEGER := NULL;
1678 BEGIN
1679 IF TG_OP <> 'DELETE' THEN
1680 tcheck_id := NEW."TcheckId";
1681 ELSE
1682 tcheck_id := OLD."TcheckId";
1683 END IF;
1684
1685 SELECT
1686 SUM(
1687 CASE
1688 WHEN "Transactions"."type" = 'CR' THEN "Transactions"."amount"
1689 ELSE "Transactions"."amount" * -1
1690 END
1691 ) INTO bal
1692 FROM public."Transactions"
1693 WHERE
1694 "Transactions"."source" = 'FUEL' AND
1695 "Transactions"."TcheckId" = tcheck_id AND
1696 "Transactions"."deletedAt" IS NULL;
1697
1698 SELECT "calculatedTotalAmount" - bal INTO pending
1699 FROM public."Tchecks"
1700 WHERE "Tchecks"."id" = tcheck_id;
1701
1702 UPDATE public."Tchecks"
1703 SET
1704 "valuePaid" = COALESCE(bal, 0),
1705 "valuePending"= COALESCE(pending, 0)
1706 WHERE "Tchecks"."id" = tcheck_id;
1707
1708 IF TG_OP <> 'DELETE' THEN
1709 return NEW;
1710 ELSE
1711 return OLD;
1712 END IF;
1713 END;
1714$$ LANGUAGE plpgsql;
1715
1716CREATE TRIGGER tcheck_balance AFTER INSERT OR UPDATE
1717ON public."Transactions" FOR EACH ROW
1718 WHEN (NEW."source" = 'FUEL')
1719 EXECUTE PROCEDURE tcheck_balance();
1720
1721CREATE TRIGGER tcheck_balance_delete AFTER DELETE
1722ON public."Transactions" FOR EACH ROW
1723 WHEN (OLD."source" = 'FUEL')
1724 EXECUTE PROCEDURE tcheck_balance();