Sestavy (Rozpad na projekty dle odpracované doby)

Sestavy (Rozpad na projekty dle odpracované doby)

Sestavy jsou určeny jako doplněk funkcionality

Rozpad na projekty dle odpracované doby

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é)

• hod - vykázané hodiny na projektu
• hod_cinn - součet vykázaných hodin činností na projektu (měl by být stejný jako hod)
• hod_bez_cinn - v případě prázdné hodnoty značí, že nebyly pořízeny činnosti

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,
(select sum (hod) from projekty_cinn where id_proj_hod = projekty_hod.id) as hod_cinn,
(hod -
(select sum (hod) from projekty_cinn where id_proj_hod = projekty_hod.id)) as hod_bez_cinn,
poz as poznamka

from projekty_hod, pracovnici,prac_pom, 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%

order by pracovnici.prijmeni, projekty_hod.id 

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

Sestava načítá všechny vykázané projekty s odpracovanou dobou a ke každému uvede pořízené činnosti. Projekty bez zadaných činností je možno zjistit tak, že ve sloupci hod_cinn (nebo nazev_cinn) je prázdná hodnota.

select
HOD.id,
HOD.id_poz,
HOD.schvaleno,
HOD.datum,
HOD.id_proj,
CISPROJ.cislo as cis_proj, CISPROJ.nazev as naz_proj,  
PRAC.os_cislo, PRAC.prijmeni, PRAC.jmeno, PRAC.titul,
PRACPOM.cislo_pom, PRACPOM.prac_str,
CIN.id, 
CIN.nazev as nazev_cinn, CIN.hod as hod_cinn, CIN.popis as popis_cinn

from projekty_hod HOD left outer join projekty_cinn CIN on HOD.id = CIN.id_proj_hod,
pracovnici PRAC, prac_pom PRACPOM, cis_projekty CISPROJ where

PRAC.os_cislo = PRACPOM.os_cislo and 
PRACPOM.os_cislo = HOD.os_cislo and 
PRACPOM.cislo_pom = HOD.cislo_pom and 
HOD.id_proj = CISPROJ.id and
HOD.datum >= %DATUMOD% and HOD.datum <= %DATUMDO%

order by PRAC.prijmeni, HOD.id, CIN.id

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

Včetně názvu mzdové položky

select
A.kod, vyznam, 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, ciselnik
where id_vazby is not null and ciselnik.kod = A.kod
group by A.kod, vyznam, id_vazby, cislo, nazev, plati_od, plati_do

union

select
A.kod, vyznam, 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, ciselnik
where id_vazby is null and ciselnik.kod = A.kod
group by A.kod, vyznam,  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: