Sestavy (Rozpad na projekty dle odpracované doby)

Sestavy (Rozpad na projekty dle odpracované doby)

Sestavy jsou určeny převážně pro Generátor sestav II, kam je také potřeba zadat příslušnou definici (uváděnou níže u každé sestavy) - Generátor sestav II (nová definice SQL).

Odpracovaná doba na projektech (vč. neschválené)

select 
projekty_hod.id, projekty_hod.id_proj, datum, 
pracovnici.os_cislo, prijmeni, jmeno, titul,
prac_pom.cislo_pom, prac_pom.prac_str,
cis_projekty.cislo, cis_projekty.nazev, id_poz, schvaleno, hod, poz

from pracovnici,prac_pom, projekty_hod, cis_projekty
where pracovnici.os_cislo = prac_pom.os_cislo and
prac_pom.os_cislo = projekty_hod.os_cislo and
prac_pom.cislo_pom = projekty_hod.cislo_pom and
projekty_hod.id_proj = cis_projekty.id
and datum >= %DATUMOD% and datum <= %DATUMDO%

Činnosti na projektech (vč. neschválených)

select
projekty_cinn.id, 
projekty_hod.id_proj,projekty_hod.datum, 
pracovnici.os_cislo, prijmeni, jmeno, titul,
prac_pom.cislo_pom, prac_pom.prac_str,
cis_projekty.cislo as cis_proj, cis_projekty.nazev as naz_proj, id_poz, schvaleno,
projekty_cinn.nazev as nazev_cinn, projekty_cinn.hod as hod_cinn, projekty_cinn.popis as popis_cinn

from pracovnici, prac_pom, projekty_cinn, projekty_hod, cis_projekty where pracovnici.os_cislo = prac_pom.os_cislo and prac_pom.os_cislo = projekty_hod.os_cislo and prac_pom.cislo_pom = projekty_hod.cislo_pom and projekty_hod.id_proj = cis_projekty.id and projekty_hod.id = projekty_cinn.id_proj_hod and datum >= %DATUMOD% and datum <= %DATUMDO%

Vypočítané peníze po projektech

select 
pracovnici.os_cislo, 
prac_pom.cislo_pom, 
prijmeni, jmeno, 
pichacky.kod,
(select vyznam from ciselnik where kod = pichacky.kod) as vyznam,
pichacky.prac_str, dat_od, dat_do, dny, hodiny, sazba, castka, prizn_aut, pichacky.prac_kateg, datum, id_vazby, typ_vazby,
(select cislo from cis_projekty where id = pichacky.id_vazby and typ_vazby = 'ph') as cis_projektu,
(select nazev from cis_projekty where id = pichacky.id_vazby and typ_vazby = 'ph') as naz_projektu

from pracovnici, prac_pom, pichacky where
pracovnici.os_cislo = prac_pom.os_cislo and 
pracovnici.os_cislo = pichacky.os_cislo and
prac_pom.cislo_pom = pichacky.cislo_pom and 
datum >= %DATUMOD% and datum <= %DATUMDO%
order by datum, os_cislo, cislo_pom, kod

Částky po měsících a po kódech a projektech

• Pro MS SQL

select
kod, id_vazby, cislo, nazev, plati_od, plati_do,
(select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby = A.id_vazby and B.datum = '2015-01-01') as Kc_01_2015,
(select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby = A.id_vazby and B.datum = '2015-02-01') as Kc_02_2015,
(select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby = A.id_vazby and B.datum = '2015-03-01') as Kc_03_2015,
(select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby = A.id_vazby and B.datum = '2015-04-01') as Kc_04_2015,
(select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby = A.id_vazby and B.datum = '2015-05-01') as Kc_05_2015,
(select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby = A.id_vazby and B.datum = '2015-06-01') as Kc_06_2015,
(select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby = A.id_vazby and B.datum = '2015-07-01') as Kc_07_2015,
(select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby = A.id_vazby and B.datum = '2015-08-01') as Kc_08_2015,
(select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby = A.id_vazby and B.datum = '2015-09-01') as Kc_09_2015,
(select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby = A.id_vazby and B.datum = '2015-10-01') as Kc_10_2015,
(select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby = A.id_vazby and B.datum = '2015-11-01') as Kc_11_2015,
(select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby = A.id_vazby and B.datum = '2015-12-01') as Kc_12_2015
from pichacky A left outer join cis_projekty on
A.id_vazby = cis_projekty.id
where id_vazby is not null
group by kod, id_vazby, cislo, nazev, plati_od, plati_do

union

select
kod, id_vazby, cislo, nazev, plati_od, plati_do,
(select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby is null and B.datum = '2015-01-01') as Kc_01_2015,
(select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby is null and B.datum = '2015-02-01') as Kc_02_2015,
(select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby is null and B.datum = '2015-03-01') as Kc_03_2015,
(select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby is null and B.datum = '2015-04-01') as Kc_04_2015,
(select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby is null and B.datum = '2015-05-01') as Kc_05_2015,
(select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby is null and B.datum = '2015-06-01') as Kc_06_2015,
(select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby is null and B.datum = '2015-07-01') as Kc_07_2015,
(select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby is null and B.datum = '2015-08-01') as Kc_08_2015,
(select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby is null and B.datum = '2015-09-01') as Kc_09_2015,
(select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby is null and B.datum = '2015-10-01') as Kc_10_2015,
(select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby is null and B.datum = '2015-11-01') as Kc_11_2015,
(select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby is null and B.datum = '2015-12-01') as Kc_12_2015

from pichacky A left outer join cis_projekty on
A.id_vazby = cis_projekty.id
where id_vazby is null
group by kod, id_vazby, cislo, nazev, plati_od, plati_do

• Pro Informix

select
kod, id_vazby, cislo, nazev, plati_od, plati_do,
(select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby = A.id_vazby and B.datum = '01.01.2015') as Kc_01_2015,
(select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby = A.id_vazby and B.datum = '01.02.2015') as Kc_02_2015,
(select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby = A.id_vazby and B.datum = '01.03.2015') as Kc_03_2015,
(select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby = A.id_vazby and B.datum = '01.04.2015') as Kc_04_2015,
(select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby = A.id_vazby and B.datum = '01.05.2015') as Kc_05_2015,
(select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby = A.id_vazby and B.datum = '01.06.2015') as Kc_06_2015,
(select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby = A.id_vazby and B.datum = '01.07.2015') as Kc_07_2015,
(select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby = A.id_vazby and B.datum = '01.08.2015') as Kc_08_2015,
(select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby = A.id_vazby and B.datum = '01.09.2015') as Kc_09_2015,
(select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby = A.id_vazby and B.datum = '01.10.2015') as Kc_10_2015,
(select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby = A.id_vazby and B.datum = '01.11.2015') as Kc_11_2015,
(select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby = A.id_vazby and B.datum = '01.12.2015') as Kc_12_2015

from pichacky A left outer join cis_projekty on
A.id_vazby = cis_projekty.id
where id_vazby is not null
group by kod, id_vazby, cislo, nazev, plati_od, plati_do

union

select
kod, id_vazby, cislo, nazev, plati_od, plati_do,
(select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby is null and B.datum = '01.01.2015') as Kc_01_2015,
(select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby is null and B.datum = '01.02.2015') as Kc_02_2015,
(select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby is null and B.datum = '01.03.2015') as Kc_03_2015,
(select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby is null and B.datum = '01.04.2015') as Kc_04_2015,
(select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby is null and B.datum = '01.05.2015') as Kc_05_2015,
(select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby is null and B.datum = '01.06.2015') as Kc_06_2015,
(select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby is null and B.datum = '01.07.2015') as Kc_07_2015,
(select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby is null and B.datum = '01.08.2015') as Kc_08_2015,
(select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby is null and B.datum = '01.09.2015') as Kc_09_2015,
(select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby is null and B.datum = '01.10.2015') as Kc_10_2015,
(select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby is null and B.datum = '01.11.2015') as Kc_11_2015,
(select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby is null and B.datum = '01.12.2015') as Kc_12_2015

from pichacky A left outer join cis_projekty on
A.id_vazby = cis_projekty.id
where id_vazby is null
group by kod, id_vazby, cislo, nazev, plati_od, plati_do

Odkazy

• Rozpad na projekty dle odpracované doby
• DB: