views.sql

CREATE OR REPLACE VIEW revendas AS
	SELECT 
		r.name as 'Nome', 
        count(uhr.user_id) as 'Funcionários',
        a.street as 'Rua',
        a.number as 'Numero',
        a.complement as 'Complemento',
        d.name as 'Bairro',
        c.name as 'Cidade'
    FROM resale r
    LEFT JOIN user_has_resale uhr ON r.id = uhr.resale_id
	INNER JOIN address a ON a.id = r.address_id
    INNER JOIN district d ON a.district_id = d.id
    INNER JOIN city c ON c.id = d.city_id
    GROUP BY r.id
    ORDER BY count(uhr.user_id);

CREATE OR REPLACE VIEW acesso_sistema_revendas AS
	SELECT 
        r.name as 'Revenda',
		u.name as 'Nome'
    FROM user u
    INNER JOIN acess_has_user ahu ON ahu.user_id = u.id
    INNER JOIN user_has_resale uhr ON uhr.user_id = u.id
    INNER JOIN resale r ON uhr.resale_id = r.id
    where ahu.acess_id = 1;
    
CREATE OR REPLACE VIEW usuarios_rs_brasil AS
	SELECT 
        r.name as 'Revenda',
        c.name as 'Cidade',
		u.name as 'Nome'
    FROM user u
    INNER JOIN acess_has_user ahu ON ahu.user_id = u.id
    INNER JOIN user_has_resale uhr ON uhr.user_id = u.id
    INNER JOIN resale r ON uhr.resale_id = r.id
    INNER JOIN address a on a.id = r.address_id
    INNER JOIN district d ON a.district_id = d.id
    INNER JOIN city c ON c.id = d.city_id
    INNER JOIN state s ON s.id = c.state_id
    where s.initials = 'RS';
    
-- select * from revendas;
-- select * from acesso_sistema_revendas;
-- select * from usuarios_rs_brasil;