CREATE TABLE td_consumoconcepto
(
  td_idconsumoconcepto character varying(12) NOT NULL,
  tm_idproyecto character varying(10),
  tm_idpropiedad character varying(10),
  tm_idconcepto character varying(10),
  tm_per_mes smallint,
  tm_per_ano smallint,
  td_importe numeric(9,2),
  activo smallint,
  idusuarioreg integer,
  fechareg timestamp without time zone,
  idusuarioact integer,
  fechaact timestamp without time zone,
  CONSTRAINT td_consumoconcepto_pkey PRIMARY KEY (td_idconsumoconcepto)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE td_consumoconcepto
  OWNER TO postgres;

DROP FUNCTION IF EXISTS pa_propiedad_consumo_concepto_listar(character varying, character varying, smallint, smallint, character varying, character varying);

CREATE OR REPLACE FUNCTION pa_propiedad_consumo_concepto_listar(
IN tipo character varying,
IN idproyecto character varying,
IN anho smallint,
IN mes smallint,
IN idpropiedad character varying,
IN idconcepto character varying,
OUT td_idconsumoescalonable character varying,
OUT tm_idconcepto character varying,
OUT tm_idpropiedad character varying,
OUT tm_descripcionpropiedad character varying,
OUT td_importe numeric)
  RETURNS SETOF record AS
$BODY$

BEGIN
  IF tipo = '1' THEN
    RETURN QUERY SELECT '0'::character varying, idconcepto, a.tm_idpropiedad, a.tm_descripcionpropiedad, 0::numeric
    FROM tm_propiedad AS a
        LEFT JOIN td_consumoconcepto AS conscpto ON a.tm_idpropiedad = conscpto.tm_idpropiedad
    WHERE a.tm_idproyecto = idproyecto AND a.tm_idtipopropiedad = 'DPT' AND a.activo = 1;
   ELSIF tipo = '2' THEN

     RETURN QUERY SELECT COALESCE(b.td_idconsumoconcepto, '0'), idconcepto, a.tm_idpropiedad, a.tm_descripcionpropiedad, COALESCE(b.td_importe, 0)

     FROM tm_propiedad AS a

        LEFT JOIN td_consumoconcepto AS b ON a.tm_idpropiedad = b.tm_idpropiedad AND b.tm_per_ano = anho AND b.tm_per_mes = mes AND b.activo = 1 AND b.tm_idconcepto = idconcepto

     WHERE a.tm_idproyecto = idproyecto AND a.tm_idtipopropiedad = 'DPT' AND a.activo = 1;

  END IF;
END;

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION pa_propiedad_consumo_concepto_listar(character varying, character varying, smallint, smallint, character varying, character varying)
  OWNER TO postgres;

DROP FUNCTION IF EXISTS pa_propiedad_concepto_exportar(character varying, character varying);

CREATE OR REPLACE FUNCTION pa_propiedad_concepto_exportar(IN idproyecto character varying, IN idconcepto character varying, OUT codigoproyecto character varying, OUT idpropiedad character varying, OUT propiedad character varying, OUT tm_idconcepto character varying, OUT importe numeric)
  RETURNS SETOF record AS
$BODY$
BEGIN

    RETURN QUERY SELECT tm_idproyecto, tm_idpropiedad, tm_descripcionpropiedad, idconcepto, 0::numeric
    FROM tm_propiedad WHERE tm_idproyecto = idproyecto AND tm_idtipopropiedad = 'DPT' AND activo = 1;
END$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION pa_propiedad_concepto_exportar(character varying, character varying)
  OWNER TO postgres;