SHARE
TWEET

Untitled

a guest Oct 21st, 2019 92 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. DROP TABLE IF EXISTS sc_tmp_delayed_recovery;
  2.  
  3. CREATE TABLE sc_tmp_delayed_recovery(
  4.     id serial,
  5.     delay_no double precision,
  6.     total_value numeric,
  7.     value_upto100 numeric,
  8.     value_upto500 numeric,
  9.     value_upto1000 numeric,
  10.     value_above1000 numeric,
  11.     total_count bigint,
  12.     quant_upto100 bigint,
  13.     quant_upto500 bigint,
  14.     quant_upto1000 bigint,
  15.     quant_above1000 bigint,
  16.     sc_creditor_id integer
  17. );
  18.  
  19. --
  20. --
  21. --
  22.  
  23. DROP FUNCTION IF EXISTS sc_delayed_recovery_v() CASCADE;
  24.  
  25. CREATE OR REPLACE FUNCTION sc_delayed_recovery_v()
  26.   RETURNS TABLE(
  27.     delay double precision,
  28.     total_value numeric,
  29.     value_upto100 numeric,
  30.     value_upto500 numeric,
  31.     value_upto1000 numeric,
  32.     value_above1000 numeric,
  33.     total_count bigint,
  34.     quant_upto100 bigint,
  35.     quant_upto500 bigint,
  36.     quant_upto1000 bigint,
  37.     quant_above1000 bigint,
  38.     sc_creditor_id integer
  39.   )  AS
  40. $BODY$
  41.  
  42.   DECLARE i integer;
  43.  
  44. BEGIN
  45.  
  46.   TRUNCATE sc_tmp_delayed_recovery RESTART IDENTITY;
  47.  
  48. FOR i IN SELECT id FROM sc_creditors LOOP
  49.  
  50. IF i = 1 THEN
  51.  
  52. INSERT INTO sc_tmp_delayed_recovery(
  53.   delay_no,total_value, value_upto100, value_upto500, value_upto1000,
  54.   value_above1000, total_count,quant_upto100, quant_upto500,
  55.   quant_upto1000, quant_above1000, sc_creditor_id
  56. )
  57. SELECT date_part('day'::text, 'now'::text::date::timestamp without time zone - co.due_date),
  58.   sum(pay.received_value),
  59.     sum(
  60.       CASE
  61.           WHEN co.debt_value >= 0::numeric AND co.debt_value <= 100.00 THEN pay.received_value
  62.           ELSE NULL::numeric
  63.       END),
  64.     sum(
  65.       CASE
  66.           WHEN co.debt_value >= 100.01 AND co.debt_value <= 500.00 THEN pay.received_value
  67.           ELSE NULL::numeric
  68.       END),
  69.     sum(
  70.       CASE
  71.           WHEN co.debt_value >= 500.01 AND co.debt_value <= 1000.00 THEN pay.received_value
  72.           ELSE NULL::numeric
  73.       END),
  74.     sum(
  75.       CASE
  76.           WHEN co.debt_value > 1000.00 THEN pay.received_value
  77.           ELSE NULL::numeric
  78.       END) AS value_above1000,
  79.     count(pay.received_value),
  80.     count(
  81.       CASE
  82.           WHEN co.debt_value >= 0::numeric AND co.debt_value <= 100.00 THEN pay.received_value
  83.           ELSE NULL::numeric
  84.       END),
  85.     count(
  86.       CASE
  87.           WHEN co.debt_value >= 100.01 AND co.debt_value <= 500.00 THEN pay.received_value
  88.           ELSE NULL::numeric
  89.       END),
  90.     count(
  91.       CASE
  92.           WHEN co.debt_value >= 500.01 AND co.debt_value <= 1000.00 THEN pay.received_value
  93.           ELSE NULL::numeric
  94.       END),
  95.     count(
  96.       CASE
  97.           WHEN co.debt_value > 1000.00 THEN pay.received_value
  98.           ELSE NULL::numeric
  99.       END),
  100.         i              
  101.       FROM sc_contracts co
  102.       JOIN sc_payments pay ON (co.sc_costumer_id = pay.sc_costumer_id AND co.sc_creditor_id = pay.sc_creditor_id)
  103.     WHERE co.sc_contract_status_id = 1
  104.       AND co.sc_creditor_id = i
  105.       AND co.only_90 IS TRUE
  106.   GROUP BY (date_part('day'::text, 'now'::text::date::timestamp without time zone - co.due_date));
  107.  
  108. ELSE
  109.  
  110. INSERT INTO sc_tmp_delayed_recovery
  111.   (
  112.   delay_no,total_value, value_upto100, value_upto500, value_upto1000,
  113.   value_above1000, total_count,quant_upto100, quant_upto500,
  114.   quant_upto1000, quant_above1000, sc_creditor_id
  115.   )
  116. SELECT date_part('day'::text, 'now'::text::date::timestamp without time zone - co.due_date),
  117.   sum(pay.received_value),
  118.   sum(
  119.     CASE
  120.         WHEN co.debt_value >= 0::numeric AND co.debt_value <= 100.00 THEN pay.received_value
  121.         ELSE NULL::numeric
  122. END),
  123.   sum(
  124.     CASE
  125.         WHEN co.debt_value >= 100.01 AND co.debt_value <= 500.00 THEN pay.received_value
  126.         ELSE NULL::numeric
  127. END),
  128.   sum(
  129.     CASE
  130.         WHEN co.debt_value >= 500.01 AND co.debt_value <= 1000.00 THEN pay.received_value
  131.         ELSE NULL::numeric
  132. END),
  133.   sum(
  134.     CASE
  135.         WHEN co.debt_value > 1000.00 THEN pay.received_value
  136.         ELSE NULL::numeric
  137. END) AS value_above1000,
  138.   count(co.debt_value),
  139.   count(
  140.       CASE
  141.           WHEN co.debt_value >= 0::numeric AND co.debt_value <= 100.00 THEN pay.received_value
  142.           ELSE NULL::numeric
  143. END),
  144.   count(
  145.       CASE
  146.           WHEN co.debt_value >= 100.01 AND co.debt_value <= 500.00 THEN pay.received_value
  147.           ELSE NULL::numeric
  148. END),
  149.   count(
  150.       CASE
  151.           WHEN co.debt_value >= 500.01 AND co.debt_value <= 1000.00 THEN pay.received_value
  152.           ELSE NULL::numeric
  153. END),
  154.   count(
  155.       CASE
  156.           WHEN co.debt_value > 1000.00 THEN pay.received_value
  157.           ELSE NULL::numeric
  158. END),
  159.       i              
  160.    FROM sc_contracts co
  161.    JOIN sc_payments pay ON (co.sc_costumer_id = pay.sc_costumer_id AND co.sc_creditor_id = pay.sc_creditor_id)
  162.   WHERE co.sc_contract_status_id = 1
  163.     AND co.sc_creditor_id = i
  164.   GROUP BY (date_part('day'::text, 'now'::text::date::timestamp without time zone - co.due_date));
  165.  
  166. END IF;
  167.    
  168. END LOOP;
  169.  
  170. RETURN QUERY (
  171.   SELECT
  172.     delay_no, sc.total_value, sc.value_upto100, sc.value_upto500, sc.value_upto1000,
  173.     sc.value_above1000, sc.total_count, sc.quant_upto100, sc.quant_upto500,
  174.     sc.quant_upto1000, sc.quant_above1000, sc.sc_creditor_id
  175.   FROM sc_tmp_delayed_recovery sc);
  176.  
  177. END;
  178.  
  179. $BODY$
  180.   LANGUAGE plpgsql VOLATILE
  181.   COST 100;
  182.  
  183. --
  184. --
  185. --
  186.  
  187. DROP VIEW IF EXISTS v_sc_delayed_recovery CASCADE;
  188.  
  189. CREATE OR REPLACE VIEW v_sc_delayed_recovery
  190.  AS
  191.  SELECT sc_delayed_recovery_v.delay,
  192.     sc_delayed_recovery_v.total_value,
  193.     sc_delayed_recovery_v.value_upto100,
  194.     sc_delayed_recovery_v.value_upto500,
  195.     sc_delayed_recovery_v.value_upto1000,
  196.     sc_delayed_recovery_v.value_above1000,
  197.     sc_delayed_recovery_v.total_count,
  198.     sc_delayed_recovery_v.quant_upto100,
  199.     sc_delayed_recovery_v.quant_upto500,
  200.     sc_delayed_recovery_v.quant_upto1000,
  201.     sc_delayed_recovery_v.quant_above1000,
  202.     sc_delayed_recovery_v.sc_creditor_id
  203.    FROM sc_delayed_recovery_v() sc_delayed_recovery_v(
  204.         delay, total_value, value_upto100, value_upto500, value_upto1000,
  205.         value_above1000, total_count, quant_upto100, quant_upto500,
  206.         quant_upto1000, quant_above1000, sc_creditor_id
  207.     );
  208.  
  209. ALTER TABLE v_sc_delayed_recovery
  210.     OWNER TO postgres;
  211.  
  212. --
  213. --
  214. --
  215.  
  216. DROP FUNCTION IF EXISTS sc_recovery_report(integer);
  217.  
  218. --
  219. --
  220. --
  221.  
  222. CREATE OR REPLACE FUNCTION sc_recovery_report(IN r_creditor_id integer)
  223.   RETURNS TABLE(delay_start integer, delay_end integer, general_quantity integer, general_value numeric, up_to_100_quantity integer, up_to_100_value numeric, up_to_500_quantity integer, up_to_500_value numeric, up_to_1000_quantity integer, up_to_1000_value numeric, above_1000_quantity integer, above_1000_value numeric) AS
  224. $BODY$  
  225. DECLARE
  226.   tracks record;
  227.   resume record;
  228. BEGIN
  229.  
  230.   --
  231.   -- Quebrando por faixa de atraso
  232.   --
  233.  
  234.   FOR tracks IN
  235.     SELECT * FROM sc_delay_track order by id asc
  236.   LOOP  
  237.  
  238.     general_quantity := 0;
  239.     general_value := 0;
  240.     up_to_100_quantity := 0;
  241.     up_to_100_value := 0;
  242.     up_to_500_quantity := 0;
  243.     up_to_500_value := 0;
  244.     up_to_1000_quantity := 0;
  245.     up_to_1000_value := 0;
  246.     above_1000_quantity := 0;
  247.     above_1000_value := 0;
  248.    
  249.     delay_start := tracks.delay_start;
  250.     delay_end := tracks.delay_end;
  251.  
  252.     FOR resume IN
  253.        SELECT
  254.               sum(value_upto100) AS value_upto100,
  255.               sum(value_upto500) AS value_upto500,
  256.               sum(value_upto1000) AS value_upto1000,
  257.               sum(value_above1000) AS value_above1000,
  258.               sum(total_value) AS total_value,
  259.               sum(quant_upto100) AS quant_upto100,
  260.               sum(quant_upto500) AS quant_upto500,
  261.               sum(quant_upto1000) AS quant_upto1000,
  262.               sum(quant_above1000) AS quant_above1000,
  263.               sum(total_count) AS total_count
  264.          FROM v_sc_delayed_recovery
  265.         WHERE delay BETWEEN tracks.delay_start AND tracks.delay_end
  266.           AND sc_creditor_id = r_creditor_id
  267.     LOOP
  268.  
  269.       general_quantity    := resume.total_count;
  270.       general_value       := resume.total_value;
  271.       up_to_100_quantity  := resume.quant_upto100;
  272.       up_to_100_value     := resume.value_upto100;
  273.       up_to_500_quantity  := resume.quant_upto500;
  274.       up_to_500_value     := resume.value_upto500;
  275.       up_to_1000_quantity := resume.quant_upto1000;
  276.       up_to_1000_value    := resume.value_upto1000;
  277.       above_1000_quantity := resume.quant_above1000;
  278.       above_1000_value    := resume.value_above1000;
  279.  
  280.     END LOOP;
  281.  
  282.     RETURN NEXT;
  283.   END LOOP;
  284.  
  285.   --
  286.   -- Apresentando os totais gerais na linha start e end -1
  287.   --
  288.  
  289.   delay_start := -1;
  290.   delay_end := -1;
  291.  
  292.   FOR resume IN
  293.       SELECT
  294.             sum(value_upto100) AS value_upto100,
  295.             sum(value_upto500) AS value_upto500,
  296.             sum(value_upto1000) AS value_upto1000,
  297.             sum(value_above1000) AS value_above1000,
  298.             sum(total_value) AS total_value,
  299.             sum(quant_upto100) AS quant_upto100,
  300.             sum(quant_upto500) AS quant_upto500,
  301.             sum(quant_upto1000) AS quant_upto1000,
  302.             sum(quant_above1000) AS quant_above1000,
  303.             sum(total_count) AS total_count
  304.         FROM v_sc_delayed_recovery WHERE sc_creditor_id = r_creditor_id
  305.   LOOP
  306.  
  307.     general_quantity    := resume.total_count;
  308.     general_value       := resume.total_value;
  309.     up_to_100_quantity  := resume.quant_upto100;
  310.     up_to_100_value     := resume.value_upto100;
  311.     up_to_500_quantity  := resume.quant_upto500;
  312.     up_to_500_value     := resume.value_upto500;
  313.     up_to_1000_quantity := resume.quant_upto1000;
  314.     up_to_1000_value    := resume.value_upto1000;
  315.     above_1000_quantity := resume.quant_above1000;
  316.     above_1000_value    := resume.value_above1000;
  317.  
  318.     RETURN NEXT;
  319.   END LOOP;
  320. END;
  321. $BODY$
  322.   LANGUAGE plpgsql VOLATILE
  323.   COST 100
  324.   ROWS 1000;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top