= Напредни извештаи од базата (SQL)

=== Листање на секоја реализација заедно со резултат на сите кандидати за таа реализацијата
{{{#!sql
select ri.ri_id, ri.ri_datum, ri.ri_ime, k.kan_id, kan.g_id, g.g_ime, count(distinct gk.ug_id)
from realizacii_na_izbori ri
join realizacii_so_kandidaturi rk on ri.ri_id = rk.ri_id
left join kandidaturi k on rk.ri_id = k.ri_id
left join glasovi_za_kandidat gk on k.kan_id = gk.kan_id
left join kandidati kan on kan.g_id = k.g_id
left join gragjani g on k.g_id = g.g_id
group by ri.ri_id, ri.ri_datum, ri.ri_ime, k.kan_id, kan.g_id, g.g_ime
}}}


=== Целосни резултати по избирачко место за реализации со кандидатура
{{{#!sql
CREATE EXTENSION IF NOT EXISTS tablefunc;

DO
$$
    DECLARE
        dynamic_sql        text;
        column_definitions text;
    BEGIN
        EXECUTE 'SELECT DISTINCT g.g_ime FROM eglas2.kandidaturi kan join eglas2.kandidati k on kan.g_id = k.g_id join eglas2.gragjani g on g.g_id = k.g_id' INTO dynamic_sql;

        column_definitions := (SELECT string_agg(quote_ident(att) || ' bigint', ', ')
                               FROM (SELECT DISTINCT g.g_ime att
                                     FROM kandidaturi kan
                                              join kandidati k on kan.g_id = k.g_id
                                              join gragjani g on g.g_id = k.g_id) AS t);

        RAISE NOTICE 'Result: %', column_definitions;

        dynamic_sql := format(
                'drop view if exists rezultati_kandidati; ' ||
                'create view rezultati_kandidati as ' ||
                'SELECT * FROM crosstab(''select im.im_mesto, g.g_ime attribute, ' ||
                '(select count(gla.ug_id) from glasanja gla join glasovi_za_kandidat glk on gla.ug_id = glk.ug_id where kan_id = kan.kan_id and gla.im_id = im.im_id) value from izbiracki_mesta im cross join kandidaturi kan join kandidati k on kan.g_id = k.g_id join gragjani g on k.g_id = g.g_id order by 1,2'') AS ct(row_name varchar, %s);',
                column_definitions
                       );

        RAISE NOTICE 'Result: %', dynamic_sql;
        EXECUTE dynamic_sql;

    END
$$;

select *
from rezultati_kandidati
}}}


=== Целосни резултати по избирачко место за реализации со кандидатска листа
{{{#!sql
CREATE EXTENSION IF NOT EXISTS tablefunc;

DO
$$
    DECLARE
        dynamic_sql        text;
        column_definitions text;
    BEGIN
        EXECUTE 'SELECT DISTINCT p.p_ime FROM eglas2.partii p' INTO dynamic_sql;

        column_definitions := (SELECT string_agg(quote_ident(att) || ' bigint', ', ')
                               FROM (SELECT DISTINCT p.p_ime att
                                     FROM kandidatski_listi kan
                                      join partii p on kan.p_id = p.p_id) AS t);

        RAISE NOTICE 'Result: %', column_definitions;

        dynamic_sql := format(
                        'drop view if exists rezultati_partii;
                        create view rezultati_partii as
                        select * from crosstab(''select im.im_mesto, p.p_ime, (select count(gla.ug_id) from glasanja gla join glasovi_za_lista glk on gla.ug_id = glk.ug_id join kandidatski_listi kl on kl.kl_id = glk.kl_id where kl.p_id = p.p_id and gla.im_id = im.im_id) from izbiracki_mesta im cross join partii p order by 1, 2'')  AS ct(row_name varchar, %s);
', column_definitions
                       );

        RAISE NOTICE 'Result: %', dynamic_sql;
        EXECUTE dynamic_sql;

    END
$$;

select *
from rezultati_partii
}}}