|
|
| Řádek 1: |
Řádek 1: |
| '''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é) ==
| |
| [[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>
| |
| 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
| |
|
| |
| </small>
| |
|
| |
| == Činnosti na projektech (vč. neschválených) ==
| |
| [[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>
| |
| 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>
| |
|
| |
| == Vypočítané peníze po projektech ==
| |
| [[Image: Vypoctene-penize-po-projektech.png|800px]]
| |
|
| |
| <small>
| |
| 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
| |
| </small>
| |
|
| |
| == Částky po měsících a po kódech a projektech ==
| |
| [[Image: Castky-po-mesich-po-kodech-a-projektech.png|900px]]
| |
|
| |
| * '''Pro MS SQL'''
| |
| <small>
| |
| 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
| |
|
| |
| </small>
| |
|
| |
| * '''Pro Informix'''
| |
| <small>
| |
|
| |
| 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
| |
|
| |
| </small>
| |
|
| |
| == Odkazy ==
| |
| * [[Rozpad na projekty dle odpracované doby]]
| |
| * DB: [[Image: Projekty hod cinn tabulky.png|70px]]
| |
