|
|
| Řá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 sestavy pro KHS (krajská hygienická stanice) a interní účely s informacemi o zaměstnancích, pracech a rizicích. Příklad: Seznam zaměstnanců s CZ-ISCO a riziky. Sestava: středisko, CZ-ISCO, počet zaměstnanců, z toho muži, z toho ženy, nejvyšší kategorie rizika.
| |
|
| |
| == 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,
| |
| case when substring(A.rodne_cislo, 3,1) in (5,6) then 'Ž'
| |
| else 'M'
| |
| end as pohlavi,
| |
| 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]]
| |
