-- SQL struktura
-- import shapefile obcí (z ArcČR 500) do PG (přes Správce databází v QGIS) -> vrstva obce, důležitý id atribut ICOB

--seskupení všech sktivních ES (s ohledem na časovou platnost - vždy k rokům sčítání lidu) do dočasné tabulky
create table temp_sumea91 as select distinct(idob), count(icof) from res where idob is not null and vzn < date '1992-01-01' and zan > date '1991-12-31' group by idob;
alter table temp_sumea91 ADD PRIMARY KEY (idob); --pro rychlejší práci PG
--a nakopírujeme do tabulky obcí skrze idob
alter table obce add column ea91 integer;
update obce set ea91 = temp_sumea91.count from temp_sumea91 where obce.idob = temp_sumea91.idob;

--stejné i pro další roky
create table temp_sumea01 as select distinct(idob), count(icof) from res where idob is not null and vzn < date '2002-01-01' and zan > date '2001-12-31' group by idob;
alter table temp_sumea01 ADD PRIMARY KEY (idob);
alter table obce add column ea01 integer;
update obce set ea01 = temp_sumea01.count from temp_sumea01 where obce.idob = temp_sumea01.idob;

create table temp_sumea11 as select distinct(idob), count(icof) from res where idob is not null and vzn < date '2012-01-01' and zan > date '2011-12-31' group by idob;
alter table temp_sumea11 ADD PRIMARY KEY (idob);
alter table obce add column ea11 integer;
update obce set ea11 = temp_sumea11.count from temp_sumea11 where obce.idob = temp_sumea11.idob;


--podobně i pro pouze živnostníky
create table temp_sumfo91 as select distinct(idob), count(icof) from res where idob is not null and vzn < date '1992-01-01' and zan > date '1991-12-31' and formaf between 101 and 108 group by idob;
alter table temp_sumfo91 ADD PRIMARY KEY (idob);
alter table obce add column fo11 integer;
update obce set fo91 = temp_sumfo91.count from temp_sumfo91 where obce.idob = temp_sumfo91.idob;
--přidáme rovnou atribut s koncentrací
alter table obce add column foob91 double precision;
update obce set foob91 = (fo11 / ob91);

--a ostatní roky
create table temp_sumfo01 as select distinct(idob), count(icof) from res where idob is not null and vzn < date '2002-01-01' and zan > date '2001-12-31' and formaf between 101 and 108 group by idob;
alter table temp_sumfo01 ADD PRIMARY KEY (idob);
alter table obce add column fo01 integer;
update obce set fo01 = temp_sumfo01.count from temp_sumfo01 where obce.idob = temp_sumfo01.idob;
alter table obce add column foob01 double precision;
update obce set foob01 = (fo01 / ob01);

create table temp_sumfo11 as select distinct(idob), count(icof) from res where idob is not null and vzn < date '2012-01-01' and zan > date '2011-12-31' and formaf between 101 and 108 group by idob;
alter table temp_sumfo11 ADD PRIMARY KEY (idob);
alter table obce add column fo11 integer;
update obce set fo11 = temp_sumfo11.count from temp_sumfo11 where obce.idob = temp_sumfo11.idob;
alter table obce add column foob11 double precision;
update obce set foob11 = (fo11 / ob11);

--a i pro rok 2014 (pouze ty co zanikly nejdříve v lednu 2014 nebo jsou živé)
create table temp_sumfo14 as select distinct(idob), count(icof) from res where idob is not null and zan > date '2014-01-01' and formaf between 101 and 108 group by idob;
alter table temp_sumfo14 ADD PRIMARY KEY (idob);
alter table obce add column fo14 integer;
update obce set fo14 = temp_sumfo14.count from temp_sumfo14 where obce.idob = temp_sumfo14.idob;
alter table obce add column foob14 double precision;
update obce set foob14 = (fo14 / ob14);


--stanovení počtu zemědělských subjektů - pro začátky roků 1994 a 2014, NACE2 = 1
--přidáme hodnotu počtu obyvatel k 1.1. 2014 - http://www.mvcr.cz/clanek/statistiky-pocty-obyvatel-v-obcich.aspx
create table temp_sumzem94 as select distinct(idob), count(icof) from res where idob is not null and zan > date '1994-01-01' and vzn <= date '1994-01-01' and nace2 = 1 group by idob;
alter table temp_sumzem94 ADD PRIMARY KEY (idob);
alter table obce add column zem94 integer;
update obce set zem94 = temp_sumzem94.count from temp_sumzem94 where obce.idob = temp_sumzem94.idob;
alter table obce add column zemob94 double precision;
update obce set zemob94 = (zem94 / ob91);

create table temp_sumzem14 as select distinct(idob), count(icof) from res where idob is not null and zan > date '2014-01-01' and nace2 = 1 group by idob;
alter table temp_sumzem14 ADD PRIMARY KEY (idob);
alter table obce add column zem14 integer;
update obce set zem14 = temp_sumzem14.count from temp_sumzem14 where obce.idob = temp_sumzem14.idob;
alter table obce add column zemob14 double precision;
update obce set zemob14 = (zem14 / ob14);


--stanovení počtu vinařů - opět 1994 a 2014, NACE4 121 (pěstování vinných hroznů) a NACE4 1102 (výroba vína)
create table temp_sumvin94 as select distinct(idob), count(icof) from res where idob is not null and zan > date '1994-01-01' and vzn <= date '1994-01-01' and nace4 = 121 or idob is not null and zan > date '1994-01-01' and vzn <= date '1994-01-01' and nace4 = 1102 group by idob;
alter table temp_sumvin94 ADD PRIMARY KEY (idob);
alter table obce add column vin94 integer;
update obce set vin94 = temp_sumvin94.count from temp_sumvin94 where obce.idob = temp_sumvin94.idob;
alter table obce add column vinob94 double precision;
update obce set vinob94 = (vin94 / ob91);

create table temp_sumvin14 as select distinct(idob), count(icof) from res where idob is not null and zan > date '2014-01-01' and nace4 = 121 or idob is not null and zan > date '2014-01-01' and nace4 = 1102 group by idob;
alter table temp_sumvin14 ADD PRIMARY KEY (idob);
alter table obce add column vin14 integer;
update obce set vin14 = temp_sumvin14.count from temp_sumvin14 where obce.idob = temp_sumvin14.idob;
alter table obce add column vinob14 double precision;
update obce set vinob14 = (vin14 / ob14);


--srovnání s daňovým příjmem obcí
--nahrání tabulky rozpočtů obcí do PG
--vytvoření prázdné tabulky pro import
CREATE TABLE public.budget11
(
  nazob character varying(250),
  csukod character varying(10),
  icofob integer,
  okres character varying(30),
  kraj character varying(30),
  tridakod character varying(10),
  tridanazev character varying(20),
  skupinakod character varying(10),
  skupinanazev character varying(254),
  podskupinakod character varying(10),
  podskupinanazev character varying(254),
  polozkakod character varying(10),
  polozkanazev character varying(254),
  kategoriekod character varying(10),
  kategorienazev character varying(254),
  sekcekod character varying(10),
  sekcenazev character varying(254),
  subsekcekod character varying(10),
  subsekcenazev character varying(254),
  paragrafkod character varying(10),
  paragrafnazev character varying(254),
  rok integer,
  pocetobyv integer,
  hodnota double precision
); 

--import tabulky všechna-data-2011.csv (z kapacitních důvodů nepřiloženo na CD, nutno stáhnout z http://www.rozpocetobce.cz/data/rozpocet-obce-vsechna-data.zip)
--přidání PK a komentáře
alter table budget11 add column id serial;
alter table budget11 add primary key (id);
COMMENT ON TABLE public.budget11 IS 'tabulka položek rozpočtů obcí v roce 2011';

--důležité položky pod kódem 1112 (příjmy fyz. osob ze samostatné činnosti) zkopírujeme do tabulky obce
alter table obce add column income11fo bigint;
update obce set income11fo = budget11.hodnota from budget11 where polozkakod = '1112' and budget11.csukod = obce.icob;
update obce set income11fo = 0 where income11fo is null;

--vytvoření parametru s poměrem hodnota daňového příjmu/počet FO v obci
alter table obce add column pomerbudgfo double precision;
update obce set pomerbudgfo = (income11fo / ob11);
--- kategorie:
1, obce s nulovým nebo záporným daňovým příjmem (nehledě na počet živnostníků, který je často nízký až nulový) (hodilo by se přidat nějaké charakteristiky)
2, obce s příjmem do 1000,- v přepočtu na jednoho živnostníka (FO samostatně výdělečně činnou)
3, obce s příjmem mezi <1000 - 5000> v přepočtu na jednoho živnostníka
4, obce s příjmem mezi <5000 - 15000> v přepočtu na jednoho živnostníka
4, obce s příjmem nad 15000 kč na jednoho živnostníka