Pom14: Porovnání verzí
(Stránka vyprázdněna) |
|||
| Řádek 1: | Řádek 1: | ||
''Zaměstnanci - CZ-ISCO, pracovní místo, rizika (sestava)'' je sestava určená pro [[Generátor sestav II]]. | |||
== Účel == | |||
Cílem je získat sestavu pro KHS (krajská hygienická stanice) s informacemi o zaměstnancích, pracech a rizicích. | |||
== Podmínky == | |||
* '''[[Pracovní místo]]''' - zjišťováno z agendy [[Pracovní poměr]] | |||
* '''[[CZ-ISCO]]''' - zjišťováno z agendy [[Pracovní poměr]] | |||
<small>Pokud uživatel vyplňuje i údaj v popisu pracovního místa, konkrétně vlastnost č. 5, měly by hodnoty vzájemně korespondovat: | |||
: [[Popisy pracovních míst - Základní údaje]] | |||
</small> | |||
* '''[[Rizika]]''' - zjišťována z popisu pracovního místa dotyčného zaměstnance, konkrétně z vlastností č. 76 a 77: | |||
: [[Popisy pracovních míst - Rizika]] | |||
== Sestava == | |||
[[Image: Zamestnanci-CZ_ISCO-pracovni misto-rizika-Sestava.png]] | |||
== Definice == | |||
Jedno pracovní místo může obsahovat více rizik. Z toho vyplývá, že se buď uvede zaměstnanec vícekrát (více řádky), nebo se rizika uvedou do sloupců. Byla zvolena druhá varianta. Přičemž script obsahuje konkrétně vyjmenovaná čísla z číselníku [[Rizikové faktory]] - tato je potřeba upravit na vaše konkrétní hodnoty (zde hodnoty 1-8). | |||
<small> | |||
select A.os_cislo, A.prijmeni, A.jmeno, | |||
B.cislo_pom, B.prac_str, B.druh_pom_j, B.prac_kateg, B.datum_nastupu, B.datum_ukonc, B.jkz as CZ_ISCO, CKZAM.nazev as nazev_CZ_ISCO, B.pov_funkce as cis_pm, PM.naz_pracmist, | |||
(select vyznam from cis_rizik where cislo = (select max(hodnota) from prac_mist_atrib PPM where PPM.hodnota = '1' and PPM.id_pracmist = B.id_pracmist and PPM.cis_atributu = 76 and | |||
(PPM.platnost_do is null or PPM.platnost_do > (select datum_prvni from par_map)) | |||
) | |||
) as riziko1, | |||
( | |||
select max(hodnota) from prac_mist_atrib PPM2 where PPM2.id_pracmist = B.id_pracmist and PPM2.cis_atributu = 77 and | |||
(PPM2.platnost_do is null or PPM2.platnost_do > (select datum_prvni from par_map)) and | |||
id_provazani = | |||
(select max(id) from prac_mist_atrib PPM3 where PPM3.id_pracmist = B.id_pracmist and PPM3.cis_atributu = 76 and PPM3.hodnota = '1' and | |||
(PPM3.platnost_do is null or PPM3.platnost_do > (select datum_prvni from par_map)) | |||
) | |||
) as kat_riz1, | |||
(select vyznam from cis_rizik where cislo = (select max(hodnota) from prac_mist_atrib PPM where PPM.hodnota = '2' and PPM.id_pracmist = B.id_pracmist and PPM.cis_atributu = 76 and | |||
(PPM.platnost_do is null or PPM.platnost_do > (select datum_prvni from par_map)) | |||
) | |||
) as riziko2, | |||
( | |||
select max(hodnota) from prac_mist_atrib PPM2 where PPM2.id_pracmist = B.id_pracmist and PPM2.cis_atributu = 77 and | |||
(PPM2.platnost_do is null or PPM2.platnost_do > (select datum_prvni from par_map)) and | |||
id_provazani = | |||
(select max(id) from prac_mist_atrib PPM3 where PPM3.id_pracmist = B.id_pracmist and PPM3.cis_atributu = 76 and PPM3.hodnota = '2' and | |||
(PPM3.platnost_do is null or PPM3.platnost_do > (select datum_prvni from par_map)) | |||
) | |||
) as kat_riz2, | |||
(select vyznam from cis_rizik where cislo = (select max(hodnota) from prac_mist_atrib PPM where PPM.hodnota = '3' and PPM.id_pracmist = B.id_pracmist and PPM.cis_atributu = 76 and | |||
(PPM.platnost_do is null or PPM.platnost_do > (select datum_prvni from par_map)) | |||
) | |||
) as riziko3, | |||
( | |||
select max(hodnota) from prac_mist_atrib PPM2 where PPM2.id_pracmist = B.id_pracmist and PPM2.cis_atributu = 77 and | |||
(PPM2.platnost_do is null or PPM2.platnost_do > (select datum_prvni from par_map)) and | |||
id_provazani = | |||
(select max(id) from prac_mist_atrib PPM3 where PPM3.id_pracmist = B.id_pracmist and PPM3.cis_atributu = 76 and PPM3.hodnota = '3' and | |||
(PPM3.platnost_do is null or PPM3.platnost_do > (select datum_prvni from par_map)) | |||
) | |||
) as kat_riz3, | |||
(select vyznam from cis_rizik where cislo = (select max(hodnota) from prac_mist_atrib PPM where PPM.hodnota = '4' and PPM.id_pracmist = B.id_pracmist and PPM.cis_atributu = 76 and | |||
(PPM.platnost_do is null or PPM.platnost_do > (select datum_prvni from par_map)) | |||
) | |||
) as riziko4, | |||
( | |||
select max(hodnota) from prac_mist_atrib PPM2 where PPM2.id_pracmist = B.id_pracmist and PPM2.cis_atributu = 77 and | |||
(PPM2.platnost_do is null or PPM2.platnost_do > (select datum_prvni from par_map)) and | |||
id_provazani = | |||
(select max(id) from prac_mist_atrib PPM3 where PPM3.id_pracmist = B.id_pracmist and PPM3.cis_atributu = 76 and PPM3.hodnota = '4' and | |||
(PPM3.platnost_do is null or PPM3.platnost_do > (select datum_prvni from par_map)) | |||
) | |||
) as kat_riz4, | |||
(select vyznam from cis_rizik where cislo = (select max(hodnota) from prac_mist_atrib PPM where PPM.hodnota = '5' and PPM.id_pracmist = B.id_pracmist and PPM.cis_atributu = 76 and | |||
(PPM.platnost_do is null or PPM.platnost_do > (select datum_prvni from par_map)) | |||
) | |||
) as riziko5, | |||
( | |||
select max(hodnota) from prac_mist_atrib PPM2 where PPM2.id_pracmist = B.id_pracmist and PPM2.cis_atributu = 77 and | |||
(PPM2.platnost_do is null or PPM2.platnost_do > (select datum_prvni from par_map)) and | |||
id_provazani = | |||
(select max(id) from prac_mist_atrib PPM3 where PPM3.id_pracmist = B.id_pracmist and PPM3.cis_atributu = 76 and PPM3.hodnota = '5' and | |||
(PPM3.platnost_do is null or PPM3.platnost_do > (select datum_prvni from par_map)) | |||
) | |||
) as kat_riz5, | |||
(select vyznam from cis_rizik where cislo = (select max(hodnota) from prac_mist_atrib PPM where PPM.hodnota = '6' and PPM.id_pracmist = B.id_pracmist and PPM.cis_atributu = 76 and | |||
(PPM.platnost_do is null or PPM.platnost_do > (select datum_prvni from par_map)) | |||
) | |||
) as riziko6, | |||
( | |||
select max(hodnota) from prac_mist_atrib PPM2 where PPM2.id_pracmist = B.id_pracmist and PPM2.cis_atributu = 77 and | |||
(PPM2.platnost_do is null or PPM2.platnost_do > (select datum_prvni from par_map)) and | |||
id_provazani = | |||
(select max(id) from prac_mist_atrib PPM3 where PPM3.id_pracmist = B.id_pracmist and PPM3.cis_atributu = 76 and PPM3.hodnota = '6' and | |||
(PPM3.platnost_do is null or PPM3.platnost_do > (select datum_prvni from par_map)) | |||
) | |||
) as kat_riz6, | |||
(select vyznam from cis_rizik where cislo = (select max(hodnota) from prac_mist_atrib PPM where PPM.hodnota = '7' and PPM.id_pracmist = B.id_pracmist and PPM.cis_atributu = 76 and | |||
(PPM.platnost_do is null or PPM.platnost_do > (select datum_prvni from par_map)) | |||
) | |||
) as riziko7, | |||
( | |||
select max(hodnota) from prac_mist_atrib PPM2 where PPM2.id_pracmist = B.id_pracmist and PPM2.cis_atributu = 77 and | |||
(PPM2.platnost_do is null or PPM2.platnost_do > (select datum_prvni from par_map)) and | |||
id_provazani = | |||
(select max(id) from prac_mist_atrib PPM3 where PPM3.id_pracmist = B.id_pracmist and PPM3.cis_atributu = 76 and PPM3.hodnota = '7' and | |||
(PPM3.platnost_do is null or PPM3.platnost_do > (select datum_prvni from par_map)) | |||
) | |||
) as kat_riz7, | |||
(select vyznam from cis_rizik where cislo = (select max(hodnota) from prac_mist_atrib PPM where PPM.hodnota = '8' and PPM.id_pracmist = B.id_pracmist and PPM.cis_atributu = 76 and | |||
(PPM.platnost_do is null or PPM.platnost_do > (select datum_prvni from par_map)) | |||
) | |||
) as riziko8, | |||
( | |||
select max(hodnota) from prac_mist_atrib PPM2 where PPM2.id_pracmist = B.id_pracmist and PPM2.cis_atributu = 77 and | |||
(PPM2.platnost_do is null or PPM2.platnost_do > (select datum_prvni from par_map)) and | |||
id_provazani = | |||
(select max(id) from prac_mist_atrib PPM3 where PPM3.id_pracmist = B.id_pracmist and PPM3.cis_atributu = 76 and PPM3.hodnota = '8' and | |||
(PPM3.platnost_do is null or PPM3.platnost_do > (select datum_prvni from par_map)) | |||
) | |||
) as kat_riz8 | |||
from pracovnici A, prac_pom B left outer join cis_kzam CKZAM on B.jkz = CKZAM.kzam, prac_mist PM | |||
where A.os_cislo = B.os_cislo and | |||
B.id_pracmist = PM.id_pracmist | |||
</small> | |||
== Odkazy == | |||
* [[Generátor sestav II]] | |||
Verze z 3. 6. 2019, 14:57
Zaměstnanci - CZ-ISCO, pracovní místo, rizika (sestava) je sestava určená pro Generátor sestav II.
Účel
Cílem je získat sestavu pro KHS (krajská hygienická stanice) s informacemi o zaměstnancích, pracech a rizicích.
Podmínky
- Pracovní místo - zjišťováno z agendy Pracovní poměr
- CZ-ISCO - zjišťováno z agendy Pracovní poměr
Pokud uživatel vyplňuje i údaj v popisu pracovního místa, konkrétně vlastnost č. 5, měly by hodnoty vzájemně korespondovat:
- Rizika - zjišťována z popisu pracovního místa dotyčného zaměstnance, konkrétně z vlastností č. 76 a 77:
Sestava
Definice
Jedno pracovní místo může obsahovat více rizik. Z toho vyplývá, že se buď uvede zaměstnanec vícekrát (více řádky), nebo se rizika uvedou do sloupců. Byla zvolena druhá varianta. Přičemž script obsahuje konkrétně vyjmenovaná čísla z číselníku Rizikové faktory - tato je potřeba upravit na vaše konkrétní hodnoty (zde hodnoty 1-8).
select A.os_cislo, A.prijmeni, A.jmeno, B.cislo_pom, B.prac_str, B.druh_pom_j, B.prac_kateg, B.datum_nastupu, B.datum_ukonc, B.jkz as CZ_ISCO, CKZAM.nazev as nazev_CZ_ISCO, B.pov_funkce as cis_pm, PM.naz_pracmist,
(select vyznam from cis_rizik where cislo = (select max(hodnota) from prac_mist_atrib PPM where PPM.hodnota = '1' and PPM.id_pracmist = B.id_pracmist and PPM.cis_atributu = 76 and (PPM.platnost_do is null or PPM.platnost_do > (select datum_prvni from par_map)) ) ) as riziko1,
( select max(hodnota) from prac_mist_atrib PPM2 where PPM2.id_pracmist = B.id_pracmist and PPM2.cis_atributu = 77 and (PPM2.platnost_do is null or PPM2.platnost_do > (select datum_prvni from par_map)) and id_provazani = (select max(id) from prac_mist_atrib PPM3 where PPM3.id_pracmist = B.id_pracmist and PPM3.cis_atributu = 76 and PPM3.hodnota = '1' and (PPM3.platnost_do is null or PPM3.platnost_do > (select datum_prvni from par_map)) ) ) as kat_riz1,
(select vyznam from cis_rizik where cislo = (select max(hodnota) from prac_mist_atrib PPM where PPM.hodnota = '2' and PPM.id_pracmist = B.id_pracmist and PPM.cis_atributu = 76 and (PPM.platnost_do is null or PPM.platnost_do > (select datum_prvni from par_map)) ) ) as riziko2,
( select max(hodnota) from prac_mist_atrib PPM2 where PPM2.id_pracmist = B.id_pracmist and PPM2.cis_atributu = 77 and (PPM2.platnost_do is null or PPM2.platnost_do > (select datum_prvni from par_map)) and id_provazani = (select max(id) from prac_mist_atrib PPM3 where PPM3.id_pracmist = B.id_pracmist and PPM3.cis_atributu = 76 and PPM3.hodnota = '2' and (PPM3.platnost_do is null or PPM3.platnost_do > (select datum_prvni from par_map)) ) ) as kat_riz2,
(select vyznam from cis_rizik where cislo = (select max(hodnota) from prac_mist_atrib PPM where PPM.hodnota = '3' and PPM.id_pracmist = B.id_pracmist and PPM.cis_atributu = 76 and
(PPM.platnost_do is null or PPM.platnost_do > (select datum_prvni from par_map))
)
) as riziko3,
( select max(hodnota) from prac_mist_atrib PPM2 where PPM2.id_pracmist = B.id_pracmist and PPM2.cis_atributu = 77 and (PPM2.platnost_do is null or PPM2.platnost_do > (select datum_prvni from par_map)) and id_provazani = (select max(id) from prac_mist_atrib PPM3 where PPM3.id_pracmist = B.id_pracmist and PPM3.cis_atributu = 76 and PPM3.hodnota = '3' and (PPM3.platnost_do is null or PPM3.platnost_do > (select datum_prvni from par_map)) ) ) as kat_riz3,
(select vyznam from cis_rizik where cislo = (select max(hodnota) from prac_mist_atrib PPM where PPM.hodnota = '4' and PPM.id_pracmist = B.id_pracmist and PPM.cis_atributu = 76 and
(PPM.platnost_do is null or PPM.platnost_do > (select datum_prvni from par_map))
)
) as riziko4,
( select max(hodnota) from prac_mist_atrib PPM2 where PPM2.id_pracmist = B.id_pracmist and PPM2.cis_atributu = 77 and (PPM2.platnost_do is null or PPM2.platnost_do > (select datum_prvni from par_map)) and id_provazani = (select max(id) from prac_mist_atrib PPM3 where PPM3.id_pracmist = B.id_pracmist and PPM3.cis_atributu = 76 and PPM3.hodnota = '4' and (PPM3.platnost_do is null or PPM3.platnost_do > (select datum_prvni from par_map)) ) ) as kat_riz4,
(select vyznam from cis_rizik where cislo = (select max(hodnota) from prac_mist_atrib PPM where PPM.hodnota = '5' and PPM.id_pracmist = B.id_pracmist and PPM.cis_atributu = 76 and
(PPM.platnost_do is null or PPM.platnost_do > (select datum_prvni from par_map))
)
) as riziko5,
( select max(hodnota) from prac_mist_atrib PPM2 where PPM2.id_pracmist = B.id_pracmist and PPM2.cis_atributu = 77 and (PPM2.platnost_do is null or PPM2.platnost_do > (select datum_prvni from par_map)) and id_provazani = (select max(id) from prac_mist_atrib PPM3 where PPM3.id_pracmist = B.id_pracmist and PPM3.cis_atributu = 76 and PPM3.hodnota = '5' and (PPM3.platnost_do is null or PPM3.platnost_do > (select datum_prvni from par_map)) ) ) as kat_riz5,
(select vyznam from cis_rizik where cislo = (select max(hodnota) from prac_mist_atrib PPM where PPM.hodnota = '6' and PPM.id_pracmist = B.id_pracmist and PPM.cis_atributu = 76 and
(PPM.platnost_do is null or PPM.platnost_do > (select datum_prvni from par_map))
)
) as riziko6,
( select max(hodnota) from prac_mist_atrib PPM2 where PPM2.id_pracmist = B.id_pracmist and PPM2.cis_atributu = 77 and (PPM2.platnost_do is null or PPM2.platnost_do > (select datum_prvni from par_map)) and id_provazani = (select max(id) from prac_mist_atrib PPM3 where PPM3.id_pracmist = B.id_pracmist and PPM3.cis_atributu = 76 and PPM3.hodnota = '6' and (PPM3.platnost_do is null or PPM3.platnost_do > (select datum_prvni from par_map)) ) ) as kat_riz6,
(select vyznam from cis_rizik where cislo = (select max(hodnota) from prac_mist_atrib PPM where PPM.hodnota = '7' and PPM.id_pracmist = B.id_pracmist and PPM.cis_atributu = 76 and
(PPM.platnost_do is null or PPM.platnost_do > (select datum_prvni from par_map))
)
) as riziko7,
( select max(hodnota) from prac_mist_atrib PPM2 where PPM2.id_pracmist = B.id_pracmist and PPM2.cis_atributu = 77 and (PPM2.platnost_do is null or PPM2.platnost_do > (select datum_prvni from par_map)) and id_provazani = (select max(id) from prac_mist_atrib PPM3 where PPM3.id_pracmist = B.id_pracmist and PPM3.cis_atributu = 76 and PPM3.hodnota = '7' and (PPM3.platnost_do is null or PPM3.platnost_do > (select datum_prvni from par_map)) ) ) as kat_riz7,
(select vyznam from cis_rizik where cislo = (select max(hodnota) from prac_mist_atrib PPM where PPM.hodnota = '8' and PPM.id_pracmist = B.id_pracmist and PPM.cis_atributu = 76 and
(PPM.platnost_do is null or PPM.platnost_do > (select datum_prvni from par_map))
)
) as riziko8,
( select max(hodnota) from prac_mist_atrib PPM2 where PPM2.id_pracmist = B.id_pracmist and PPM2.cis_atributu = 77 and (PPM2.platnost_do is null or PPM2.platnost_do > (select datum_prvni from par_map)) and id_provazani = (select max(id) from prac_mist_atrib PPM3 where PPM3.id_pracmist = B.id_pracmist and PPM3.cis_atributu = 76 and PPM3.hodnota = '8' and (PPM3.platnost_do is null or PPM3.platnost_do > (select datum_prvni from par_map)) ) ) as kat_riz8
from pracovnici A, prac_pom B left outer join cis_kzam CKZAM on B.jkz = CKZAM.kzam, prac_mist PM
where A.os_cislo = B.os_cislo and
B.id_pracmist = PM.id_pracmist
