Finances Database

gistfile1.txt

CREATE TABLE entry(
    "d" VARCHAR(128) NOT NULL,
    "v" money NOT NULL,
    "a" VARCHAR(128) NOT NULL DEFAULT 'pagamento',
    "t" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    id BIGSERIAL PRIMARY KEY
);


CREATE TABLE plan(
    "d" VARCHAR(128) NOT NULL,
    "v" money NOT NULL,
    id BIGSERIAL PRIMARY KEY
);

CREATE OR REPLACE VIEW public.proventos AS
 SELECT to_char(entry.t, 'DD/MM/YYYY'::text) AS "Data",
    entry.v AS "Valor",
    entry.d AS "Descrição"
   FROM entry
  WHERE entry.a::text ~~ '%renda%'::text;
  
  CREATE OR REPLACE VIEW public.movimentacoes AS
 SELECT to_char(entry.t, 'DD/MM/YYYY'::text) AS "Data",
    entry.a AS "Tipo",
    entry.v AS "Valor",
    entry.d AS "Descrição"
   FROM entry
  WHERE entry.a::text ~~ '%deposito%'::text OR entry.a::text ~~ '%debito%'::text OR entry.a::text ~~ '%saque%'::text;

CREATE OR REPLACE VIEW public.pagamentos AS
 SELECT to_char(entry.t, 'DD/MM/YYYY'::text) AS "Data",
    entry.a AS "Tipo",
    entry.v AS "Valor",
    entry.d AS "Descrição"
   FROM entry
  WHERE entry.a::text ~~ '%pagamento%'::text OR entry.a::text ~~ '%debito%'::text;




create or replace view informacoes as
select i AS "Informação", v AS "Valor"
from (

	(SELECT 'Dinheiro no Banco' AS i,
        (SELECT SUM(v) FROM entry WHERE a LIKE '%deposito%') 
          - 
        (SELECT SUM(v) FROM entry WHERE a LIKE '%saque%' or a LIKE '%debito%') 
    AS v)
    
    UNION

	(SELECT 'Dinheiro em Mãos' AS i,
        (SELECT SUM(v) FROM entry WHERE a LIKE '%saque%' or a LIKE '%receita%') 
          - 
        (SELECT COALESCE(SUM(v), 0::money) FROM entry WHERE a LIKE '%pagamento%') 
    AS v)
    
    UNION 
    
    (SELECT 'Caixa Total' AS i,
        (SELECT SUM(v) FROM entry WHERE a LIKE '%deposito%' or a LIKE '%receita%') 
          - 
        (SELECT COALESCE(SUM(v), 0::money) FROM entry WHERE a LIKE '%pagamento%' or a LIKE '%debito%') 
    AS v)
    
    UNION 
    
    (SELECT 'Gasto no Mês Atual' AS i,
        (SELECT SUM(v) FROM entry WHERE a LIKE '%debito%' or (a LIKE '%pagamento%') and EXTRACT(MONTH FROM "t"::date) >= EXTRACT(MONTH FROM current_date))
    AS v)
    
    UNION
    
    (SELECT 'Dízimo Pendente' AS i,
        (SELECT SUM(v / 10) FROM entry WHERE a LIKE '%renda%') 
          - 
        (SELECT COALESCE(SUM(v), 0::money) FROM entry WHERE a LIKE '%dizimo%') 
    AS v)

    UNION
    
    (SELECT 'Planejamento: Dívidas' AS i,
        (SELECT COALESCE(SUM(v * -1), 0::money) FROM plan WHERE v < 0::money) 
    AS v)
    
    UNION
    
    (SELECT 'Planejamento: Disponível' AS i,
        (SELECT SUM(v) FROM entry WHERE a LIKE '%deposito%' or a LIKE '%receita%') 
          - 
        (SELECT COALESCE(SUM(v), 0::money) FROM entry WHERE a LIKE '%pagamento%' or a LIKE '%debito%') 
          + 
        (SELECT COALESCE(SUM(v), 0::money) FROM plan WHERE v < 0::money)  
	  -
        ((SELECT SUM(v / 10) FROM entry WHERE a LIKE '%renda%') 
          - 
        (SELECT COALESCE(SUM(v), 0::money) FROM entry WHERE a LIKE '%dizimo%')) 
    AS v)
    
    
) infos;