Untitled

DROP TABLE IF EXISTS sc_tmp_delayed_recovery;

CREATE TABLE sc_tmp_delayed_recovery(
    id serial,
    delay_no double precision,
    total_value numeric,
    value_upto100 numeric,
    value_upto500 numeric,
    value_upto1000 numeric,
    value_above1000 numeric,
    total_count bigint,
    quant_upto100 bigint,
    quant_upto500 bigint,
    quant_upto1000 bigint,
    quant_above1000 bigint,
    sc_creditor_id integer
);

--
--
--

DROP FUNCTION IF EXISTS sc_delayed_recovery_v() CASCADE;

CREATE OR REPLACE FUNCTION sc_delayed_recovery_v()
  RETURNS TABLE(
    delay double precision,
    total_value numeric,
    value_upto100 numeric,
    value_upto500 numeric,
    value_upto1000 numeric,
    value_above1000 numeric,
    total_count bigint,
    quant_upto100 bigint,
    quant_upto500 bigint,
    quant_upto1000 bigint,
    quant_above1000 bigint,
    sc_creditor_id integer
  )  AS
$BODY$

  DECLARE i integer;

BEGIN

  TRUNCATE sc_tmp_delayed_recovery RESTART IDENTITY;

FOR i IN SELECT id FROM sc_creditors LOOP

IF i = 1 THEN

INSERT INTO sc_tmp_delayed_recovery(
  delay_no,total_value, value_upto100, value_upto500, value_upto1000,
  value_above1000, total_count,quant_upto100, quant_upto500,
  quant_upto1000, quant_above1000, sc_creditor_id
)
SELECT date_part('day'::text, 'now'::text::date::timestamp without time zone - co.due_date),
  sum(pay.received_value),
    sum(
      CASE
          WHEN co.debt_value >= 0::numeric AND co.debt_value <= 100.00 THEN pay.received_value
          ELSE NULL::numeric
      END),
    sum(
      CASE
          WHEN co.debt_value >= 100.01 AND co.debt_value <= 500.00 THEN pay.received_value
          ELSE NULL::numeric
      END),
    sum(
      CASE
          WHEN co.debt_value >= 500.01 AND co.debt_value <= 1000.00 THEN pay.received_value
          ELSE NULL::numeric
      END),
    sum(
      CASE
          WHEN co.debt_value > 1000.00 THEN pay.received_value
          ELSE NULL::numeric
      END) AS value_above1000,
    count(pay.received_value),
    count(
      CASE
          WHEN co.debt_value >= 0::numeric AND co.debt_value <= 100.00 THEN pay.received_value
          ELSE NULL::numeric
      END),
    count(
      CASE
          WHEN co.debt_value >= 100.01 AND co.debt_value <= 500.00 THEN pay.received_value
          ELSE NULL::numeric
      END),
    count(
      CASE
          WHEN co.debt_value >= 500.01 AND co.debt_value <= 1000.00 THEN pay.received_value
          ELSE NULL::numeric
      END),
    count(
      CASE
          WHEN co.debt_value > 1000.00 THEN pay.received_value
          ELSE NULL::numeric
      END),
        i
      FROM sc_contracts co
      JOIN sc_payments pay ON (co.sc_costumer_id = pay.sc_costumer_id AND co.sc_creditor_id = pay.sc_creditor_id)
    WHERE co.sc_contract_status_id = 1
      AND co.sc_creditor_id = i
      AND co.only_90 IS TRUE
  GROUP BY (date_part('day'::text, 'now'::text::date::timestamp without time zone - co.due_date));

ELSE

INSERT INTO sc_tmp_delayed_recovery
  (
  delay_no,total_value, value_upto100, value_upto500, value_upto1000,
  value_above1000, total_count,quant_upto100, quant_upto500,
  quant_upto1000, quant_above1000, sc_creditor_id
  )
SELECT date_part('day'::text, 'now'::text::date::timestamp without time zone - co.due_date),
  sum(pay.received_value),
  sum(
    CASE
        WHEN co.debt_value >= 0::numeric AND co.debt_value <= 100.00 THEN pay.received_value
        ELSE NULL::numeric
END),
  sum(
    CASE
        WHEN co.debt_value >= 100.01 AND co.debt_value <= 500.00 THEN pay.received_value
        ELSE NULL::numeric
END),
  sum(
    CASE
        WHEN co.debt_value >= 500.01 AND co.debt_value <= 1000.00 THEN pay.received_value
        ELSE NULL::numeric
END),
  sum(
    CASE
        WHEN co.debt_value > 1000.00 THEN pay.received_value
        ELSE NULL::numeric
END) AS value_above1000,
  count(co.debt_value),
  count(
      CASE
          WHEN co.debt_value >= 0::numeric AND co.debt_value <= 100.00 THEN pay.received_value
          ELSE NULL::numeric
END),
  count(
      CASE
          WHEN co.debt_value >= 100.01 AND co.debt_value <= 500.00 THEN pay.received_value
          ELSE NULL::numeric
END),
  count(
      CASE
          WHEN co.debt_value >= 500.01 AND co.debt_value <= 1000.00 THEN pay.received_value
          ELSE NULL::numeric
END),
  count(
      CASE
          WHEN co.debt_value > 1000.00 THEN pay.received_value
          ELSE NULL::numeric
END),
      i
   FROM sc_contracts co
   JOIN sc_payments pay ON (co.sc_costumer_id = pay.sc_costumer_id AND co.sc_creditor_id = pay.sc_creditor_id)
  WHERE co.sc_contract_status_id = 1
    AND co.sc_creditor_id = i
  GROUP BY (date_part('day'::text, 'now'::text::date::timestamp without time zone - co.due_date));

END IF;

END LOOP;

RETURN QUERY (
  SELECT
    delay_no, sc.total_value, sc.value_upto100, sc.value_upto500, sc.value_upto1000,
    sc.value_above1000, sc.total_count, sc.quant_upto100, sc.quant_upto500,
    sc.quant_upto1000, sc.quant_above1000, sc.sc_creditor_id
  FROM sc_tmp_delayed_recovery sc);

END;

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

--
--
--

DROP VIEW IF EXISTS v_sc_delayed_recovery CASCADE;

CREATE OR REPLACE VIEW v_sc_delayed_recovery
 AS
 SELECT sc_delayed_recovery_v.delay,
    sc_delayed_recovery_v.total_value,
    sc_delayed_recovery_v.value_upto100,
    sc_delayed_recovery_v.value_upto500,
    sc_delayed_recovery_v.value_upto1000,
    sc_delayed_recovery_v.value_above1000,
    sc_delayed_recovery_v.total_count,
    sc_delayed_recovery_v.quant_upto100,
    sc_delayed_recovery_v.quant_upto500,
    sc_delayed_recovery_v.quant_upto1000,
    sc_delayed_recovery_v.quant_above1000,
    sc_delayed_recovery_v.sc_creditor_id
   FROM sc_delayed_recovery_v() sc_delayed_recovery_v(
        delay, total_value, value_upto100, value_upto500, value_upto1000,
        value_above1000, total_count, quant_upto100, quant_upto500,
        quant_upto1000, quant_above1000, sc_creditor_id
    );

ALTER TABLE v_sc_delayed_recovery
    OWNER TO postgres;

--
--
--

DROP FUNCTION IF EXISTS sc_recovery_report(integer);

--
--
--

CREATE OR REPLACE FUNCTION sc_recovery_report(IN r_creditor_id integer)
  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
$BODY$
DECLARE
  tracks record;
  resume record;
BEGIN

  --
  -- Quebrando por faixa de atraso
  --

  FOR tracks IN
    SELECT * FROM sc_delay_track order by id asc
  LOOP

    general_quantity := 0;
    general_value := 0;
    up_to_100_quantity := 0;
    up_to_100_value := 0;
    up_to_500_quantity := 0;
    up_to_500_value := 0;
    up_to_1000_quantity := 0;
    up_to_1000_value := 0;
    above_1000_quantity := 0;
    above_1000_value := 0;

    delay_start := tracks.delay_start;
    delay_end := tracks.delay_end;

    FOR resume IN
       SELECT
              sum(value_upto100) AS value_upto100,
              sum(value_upto500) AS value_upto500,
              sum(value_upto1000) AS value_upto1000,
              sum(value_above1000) AS value_above1000,
              sum(total_value) AS total_value,
              sum(quant_upto100) AS quant_upto100,
              sum(quant_upto500) AS quant_upto500,
              sum(quant_upto1000) AS quant_upto1000,
              sum(quant_above1000) AS quant_above1000,
              sum(total_count) AS total_count
         FROM v_sc_delayed_recovery
        WHERE delay BETWEEN tracks.delay_start AND tracks.delay_end
          AND sc_creditor_id = r_creditor_id
    LOOP

      general_quantity    := resume.total_count;
      general_value       := resume.total_value;
      up_to_100_quantity  := resume.quant_upto100;
      up_to_100_value     := resume.value_upto100;
      up_to_500_quantity  := resume.quant_upto500;
      up_to_500_value     := resume.value_upto500;
      up_to_1000_quantity := resume.quant_upto1000;
      up_to_1000_value    := resume.value_upto1000;
      above_1000_quantity := resume.quant_above1000;
      above_1000_value    := resume.value_above1000;

    END LOOP;

    RETURN NEXT;
  END LOOP;

  --
  -- Apresentando os totais gerais na linha start e end -1
  --

  delay_start := -1;
  delay_end := -1;

  FOR resume IN
      SELECT
            sum(value_upto100) AS value_upto100,
            sum(value_upto500) AS value_upto500,
            sum(value_upto1000) AS value_upto1000,
            sum(value_above1000) AS value_above1000,
            sum(total_value) AS total_value,
            sum(quant_upto100) AS quant_upto100,
            sum(quant_upto500) AS quant_upto500,
            sum(quant_upto1000) AS quant_upto1000,
            sum(quant_above1000) AS quant_above1000,
            sum(total_count) AS total_count
        FROM v_sc_delayed_recovery WHERE sc_creditor_id = r_creditor_id
  LOOP

    general_quantity    := resume.total_count;
    general_value       := resume.total_value;
    up_to_100_quantity  := resume.quant_upto100;
    up_to_100_value     := resume.value_upto100;
    up_to_500_quantity  := resume.quant_upto500;
    up_to_500_value     := resume.value_upto500;
    up_to_1000_quantity := resume.quant_upto1000;
    up_to_1000_value    := resume.value_upto1000;
    above_1000_quantity := resume.quant_above1000;
    above_1000_value    := resume.value_above1000;

    RETURN NEXT;
  END LOOP;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;