create or replace view vw_dashboard_relacionamento as
  SELECT a.id,
         2 AS tipo,
         a.empresaid
  FROM (tbgercliente a
         JOIN tbgerpessoafisica b ON ((a.pessoaid = b.pessoaid)))
  WHERE ((to_char(b.nascimento, 'dd-MM'::text) = to_char((CURRENT_DATE)::timestamp with time zone, 'dd-MM'::text)) AND
         (a.ativo = true))
  UNION ALL
  SELECT tbpetanimal.id,
         1 AS tipo,
         tbpetanimal.empresaid
  FROM tbpetanimal
  WHERE ((to_char(tbpetanimal.datanascimento, 'dd-MM'::text) =
          to_char((CURRENT_DATE)::timestamp with time zone, 'dd-MM'::text)) AND (tbpetanimal.ativo = true))
  UNION ALL
  SELECT tbgercliente.id,
         4 AS tipo,
         tbgercliente.empresaid
  FROM tbgercliente
  WHERE ((to_char(tbgercliente.datacadastro, 'MM-YYYY'::text) =
          to_char((CURRENT_DATE)::timestamp with time zone, 'MM-YYYY'::text)) AND (tbgercliente.ativo = true))
  UNION ALL
  SELECT tbpetanimal.id,
         3 AS tipo,
         tbpetanimal.empresaid
  FROM tbpetanimal
  WHERE ((to_char(tbpetanimal.datacadastro, 'MM-YYYY'::text) =
          to_char((CURRENT_DATE)::timestamp with time zone, 'MM-YYYY'::text)) AND (tbpetanimal.ativo = true))
  UNION ALL
  SELECT tbgercliente.id,
         6 AS tipo,
         tbgercliente.empresaid
  FROM tbgercliente
  WHERE (NOT (tbgercliente.id IN (SELECT DISTINCT tbvenpedido.clienteid
                                  FROM tbvenpedido
                                  WHERE ((tbvenpedido.data >= (CURRENT_DATE - '15 days'::interval)) AND
                                         (tbvenpedido.data <= CURRENT_DATE) AND (tbvenpedido.clienteid IS NOT NULL) AND
                                         (tbvenpedido.situacao = 3)))))
  UNION ALL
  SELECT tbpetanimal.id,
         5 AS tipo,
         tbpetanimal.empresaid
  FROM tbpetanimal
  WHERE (NOT (tbpetanimal.id IN (SELECT DISTINCT tbvenpedido.animalid
                                 FROM tbvenpedido
                                 WHERE ((tbvenpedido.data >= (CURRENT_DATE - '15 days'::interval)) AND
                                        (tbvenpedido.data <= CURRENT_DATE) AND (tbvenpedido.animalid IS NOT NULL) AND
                                        (tbvenpedido.situacao = 3)))));