Changes between Initial Version and Version 1 of AdvancedReports

Timestamp:

01/29/24 13:35:05 (21 months ago)

Author:

211012

Comment:

--

AdvancedReports

@@ +1 @@
+= Напредни извештаи од базата (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
+}}}