Views of all alert not send by email

[pferrariuy]

Feature Request

Description

Create 2 view to retrieve all the alerts that are enabled and ready to be send my email:

• vw_cli_gen_alert_to_send_by_email
• vw_cli_loc_alert_to_send_by_email

Use Case

Views will be use by: Renovus-Tech/solarec-java#45

Expected Behavior

• Client is enabled to send alerts by email
• Location / Generator is enabled to send alerts by email
• Alert has not been send by email before
• The alert must by seend to all users (with access to the client + location / generator) that are enabled to receive alerts by email

[pferrariuy]

Flags taken according to definition in: Renovus-Tech/solarec-java#45

CREATE VIEW vw_cli_gen_alert_to_send_by_email AS
select
 cga.*,
 g.loc_id,
 g.gen_name,
 g.gen_code,
 l.loc_name,
 l.loc_code,
 c.cli_name
from
client c
join location l on c.cli_id_auto = l.cli_id
join generator g on g.cli_id = l.cli_id and g.loc_id = l.loc_id_auto
join cli_gen_alert cga on l.cli_id = cga.cli_id and g.gen_id_auto = cga.gen_id
where c.cli_flags ilike '__1%' and g.gen_flags ilike '_1%' and cga.cli_gen_alert_flags ilike '_0%';--


CREATE VIEW vw_cli_loc_alert_to_send_by_email AS 
select
 cla.*,
 l.loc_name,
 l.loc_code,
 c.cli_name
from
client c
join location l on c.cli_id_auto = l.cli_id
join cli_loc_alert cla on l.cli_id = cla.cli_id and l.loc_id_auto = cla.loc_id
where c.cli_flags ilike '__1%' and l.loc_flags ilike '_1%' and cla.cli_loc_alert_flags ilike '_0%';--