|
|
| Řádek 1: |
Řádek 1: |
| '''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é) ==
| |
| [[Image: Odpracovana-doba-na-projektech-vcetne-neschvalenych.png|500px]]
| |
|
| |
| <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, 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%
| |
| </small>
| |
|
| |
| == Činnosti na projektech (vč. neschválených) ==
| |
| [[Image: Cinnosti-na-projektech-vcetne-neschvalenych.png|500px]]
| |
|
| |
| <small>
| |
| 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%
| |
| </small>
| |
|
| |
| == Vypočítané peníze po projektech ==
| |
| [[Image: Vypoctene-penize-po-projektech.png|500px]]
| |
|
| |
| <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|500px]]
| |
|
| |
| * '''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
| |
|
| |
| </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]]
| |
