Sestavy (Rozpad na projekty dle odpracované doby)
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.
- hod_celk - celkové vykázané hodiny za projekt - uvádí se ke každé pořízené činnosti, tj. pak je u více řádků stejná hodnota Jedná se o informativní kontrolní údaj.
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, HOD.hod as hod_celk, 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
Tato sestava byla později nahrazena sestavou SZ411 (viz dále).
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 in ('ph', 'cp')) as cis_projektu, (select nazev from cis_projekty where id = pichacky.id_vazby and typ_vazby in ('ph', 'cp')) 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
Rozpad částek na projekty (sestava SZ411)
Menu: Evidence > Zákaznické speciality / Rozpad částek na projekty
Tato sestava ve srovnání s předchozí je bohatší - uvádí také poměrnou část zdravotního a sociálního pojištění firmy rozpadnuté po jednotlivých zakázkách (tak jak bude/bylo převedeno do účetnictví). hd 39695
Čá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