Sestavy (Rozpad na projekty dle odpracované doby): Porovnání verzí
Z Prirucky
| Řádek 6: | Řádek 6: | ||
== Odpracovaná doba na projektech (vč. neschválené) == | == Odpracovaná doba na projektech (vč. neschválené) == | ||
[[Image: Odpracovana-doba-na-projektech-vcetne-neschvalenych.png| | [[Image: Odpracovana-doba-na-projektech-vcetne-neschvalenych.png|900px]] | ||
* ''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 | |||
<small> | <small> | ||
| Řádek 13: | Řádek 17: | ||
pracovnici.os_cislo, prijmeni, jmeno, titul, | pracovnici.os_cislo, prijmeni, jmeno, titul, | ||
prac_pom.cislo_pom, prac_pom.prac_str, | prac_pom.cislo_pom, prac_pom.prac_str, | ||
cis_projekty.cislo, cis_projekty.nazev, id_poz, schvaleno, hod, poz | 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 pracovnici,prac_pom | from projekty_hod, pracovnici,prac_pom, cis_projekty | ||
where pracovnici.os_cislo = prac_pom.os_cislo and | where pracovnici.os_cislo = prac_pom.os_cislo and | ||
prac_pom.os_cislo = projekty_hod.os_cislo and | prac_pom.os_cislo = projekty_hod.os_cislo and | ||
| Řádek 21: | Řádek 29: | ||
projekty_hod.id_proj = cis_projekty.id | projekty_hod.id_proj = cis_projekty.id | ||
and datum >= %DATUMOD% and datum <= %DATUMDO% | and datum >= %DATUMOD% and datum <= %DATUMDO% | ||
order by pracovnici.prijmeni, projekty_hod.id | |||
</small> | </small> | ||
== Činnosti na projektech (vč. neschválených) == | == Činnosti na projektech (vč. neschválených) == | ||
[[Image: Cinnosti-na-projektech-vcetne-neschvalenych.png| | [[Image: Cinnosti-na-projektech-vcetne-neschvalenych.png|900px]] | ||
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. | |||
<small> | <small> | ||
select | 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 | |||
</small> | </small> | ||
== Vypočítané peníze po projektech == | == Vypočítané peníze po projektech == | ||
[[Image: Vypoctene-penize-po-projektech.png| | [[Image: Vypoctene-penize-po-projektech.png|800px]] | ||
<small> | <small> | ||
| Řádek 67: | Řádek 87: | ||
== Částky po měsících a po kódech a projektech == | == Částky po měsících a po kódech a projektech == | ||
[[Image: Castky-po-mesich-po-kodech-a-projektech.png| | [[Image: Castky-po-mesich-po-kodech-a-projektech.png|900px]] | ||
* '''Pro MS SQL''' | * '''Pro MS SQL''' | ||
Verze z 26. 8. 2016, 15:08
Sestavy (Rozpad na projekty dle odpracované doby)
Sestavy jsou určeny jako doplněk funkcionality
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
