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

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

DO
$$
    DECLARE
        dynamic_sql        text;
        column_definitions text;
    BEGIN

        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

        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
}}}

=== Податоци за излезноста на граѓани по избирачко место по даден интервал X од час Y до час Z, за дадена реализација Q

{{{#!sql
with broj_glasaci_po_mesto as (
    select im.im_id, count(distinct g.g_id) broj_glasaci
    from izbiracki_mesta im
    join adresi a on im.im_id = a.im_id
    join gragjani g on a.a_id = g.a_id
    group by im.im_id
)

select interval_cas, im.im_id, im.im_mesto,
       coalesce((select broj_glasaci from broj_glasaci_po_mesto where im_id = im.im_id), 0) vkupno_registrirani,
       count(distinct gl.ug_id) vkupno_izlezeni,
       coalesce(count(distinct gl.ug_id) * 100.0 / (select broj_glasaci from broj_glasaci_po_mesto where im_id = im.im_id), 0) procentualna_izleznost
from generate_series(Y, Z, Q) interval_cas
cross join izbiracki_mesta im
left join glasanja gl on im.im_id = gl.im_id and gl.ri_id = X and make_time(extract(hour from ug_vreme)::int, extract(min from ug_vreme)::int, 0)  between make_time(interval_cas::int, 0, 0) and make_time((interval_cas::int + (Q - 1)) % 24, 59, 0)
left join glasovi_za_kandidat gzk on gl.ug_id = gzk.ug_id
group by im.im_id, im.im_mesto, interval_cas
order by im.im_id, im.im_mesto asc
}}}

=== Листање на членовите на комисиите во избирачките места за реализација на избори X каде бројот на приговори е поголем од Y

{{{#!sql
with izleznost_count as (select im.im_id,
                                im.im_mesto     izbiracko_mesto,
                                count(pr.pr_id) broj_prigovori
                         from izbiracki_mesta im
                         left join prigovori pr on im.im_id = pr.im_id
                         where pr.ri_id = X
                         group by im.im_id, im.im_mesto)
select g.g_ime,
       g.g_prezime,
       k.kom_id,
       scn.kom_id,
       k.ri_id,
       im.im_mesto,
       (select broj_prigovori from izleznost_count where im_id = im.im_id) broj_progovori
from clenovi_na_komisija ck
         join se_clenovi_na scn on scn.g_id = ck.g_id
         join komisii k on scn.kom_id = k.kom_id and k.ri_id = X
         join izbiracki_mesta im on k.im_id = im.im_id
         join gragjani g on ck.g_id = g.g_id
where (select broj_prigovori from izleznost_count where im_id = im.im_id) > Y
order by broj_progovori desc, im.im_mesto asc;
}}}

=== Листање на секоја реализација заедно со резултат на сите кандидати за таа реализацијата
{{{#!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
select o.o_ime,
       count(distinct g.g_id) vkupno,
       count(distinct gl.ug_id) glasale,
       case
           when count(distinct g.g_id) = 0 then true
           else count(distinct gl.ug_id) * 100.0 / count(distinct g.g_id) >= 35
       end ima_cenzus
from opstini o
left join adresi a on o.o_id = a.o_id
left join gragjani g on a.a_id = g.a_id
left join glasanja gl on gl.g_id = g.g_id and gl.ri_id = 1
group by o.o_ime
}}}

=== Детални податоци за излезноста на граѓани по избирачко место за реализација на избори со кандидати / кандидатска листа
{{{#!sql
select im.im_id, im.im_mesto,
       count(distinct gl.ug_id) vkupno_izlezeni,
       count(distinct gzk.ug_id) vazecki_glasovi,
       count(distinct gl.ug_id) - count(distinct gzk.ug_id) nevazceki_glasovi
from izbiracki_mesta im
left join glasanja gl on im.im_id = gl.im_id and gl.ri_id = 1
left join glasovi_za_kandidat gzk on gl.ug_id = gzk.ug_id
group by im.im_id, im.im_mesto
}}}
// //
{{{#!sql
select im.im_id, im.im_mesto,
       count(distinct gl.ug_id) vkupno_izlezeni,
       count(distinct gzk.ug_id) vazecki_glasovi,
       count(distinct gl.ug_id) - count(distinct gzk.ug_id) nevazceki_glasovi
from izbiracki_mesta im
left join glasanja gl on im.im_id = gl.im_id and gl.ri_id = 1
left join glasovi_za_lista gzk on gl.ug_id = gzk.ug_id
group by im.im_id, im.im_mesto
}}}