/*Procedura na konzoli vypíše města (viz. obrázek), která budou vyhovovat vstupním parametrům.
Parametry P_PRUM_MZDA_OD a P_PRUM_MZDA_DO reprezentují rozsah průměrné mzdy pracovníků v daném městě,
které se má zobrazit. Druhá dvojice atributů, P_PRUM_LET_OD a P_PRUM_LET vyjadřují, v jakém rozsahu musí být průměr
odpracovaných let pracovníků pracujících v daném městě(odpracovaná léta se počítají od data nástupu do teď).
Ve výpise se musí kromě názvu města objevit i ve kterém statě a regionu se město nachází,
jaká je průměrná mzda v daném městě a kolik zaměstnanců ve městě pracuje.

London (United Kingdon, Europe):
Počet zaměstnanců: 1
Průmerná mzda: 6500
Průměrně odpracováno: 18*/



CREATE OR REPLACE PROCEDURE
ZK_HR_MESTA(P_PRUM_MZDA_OD IN NUMBER, P_PRUM_MZDA_DO IN NUMBER, P_PRUM_LET_OD IN NUMBER, P_PRUM_LET_DO IN NUMBER ) AS

mesto varchar2(100);
stat varchar2(100);
region varchar2(100);
pocetZam NUMBER;
prum_mzda NUMBER;
prum_odprac NUMBER;

cursor k1 IS SELECT adr.mesto,zem.zeme_nazev,regiony.region_nazev,
COUNT(zam.zamestnanec_id) pocet_zam, (SUM(zam.mzda)/COUNT(zam.zamestnanec_id)) prum_mzda,
(SUM(sysdate-zam.datum_nastup)/COUNT(sysdate-zam.datum_nastup)) /365.25 AS prumer
FROM a_hr.adresy adr
JOIN a_hr.oddeleni odd ON odd.adresa_id = adr.adresa_id
JOIN a_hr.zeme zem ON zem.zeme_id = adr.zeme_id
JOIN a_hr.regiony ON regiony.region_id =zem.region_id
JOIN a_hr.zamestnanci zam ON zam.oddeleni_id = odd.oddeleni_id
HAVING (SUM(zam.mzda)/COUNT(zam.zamestnanec_id)) >= P_PRUM_MZDA_OD AND (SUM(zam.mzda)/COUNT(zam.zamestnanec_id)) <= P_PRUM_MZDA_DO AND
(SUM(sysdate-zam.datum_nastup)/COUNT(sysdate-zam.datum_nastup)) /365.25 >= P_PRUM_LET_OD AND (SUM(sysdate-zam.datum_nastup)/COUNT(sysdate-zam.datum_nastup)) /365.25 <= P_PRUM_LET_DO
GROUP BY adr.mesto, zem.zeme_nazev, regiony.region_nazev;

BEGIN
  OPEN k1;
      LOOP
      FETCH k1 INTO mesto, stat, region, pocetZam, prum_mzda, prum_odprac;
      EXIT WHEN k1%NOTFOUND;
          dbms_output.put_line(mesto || ' (' || stat || ', ' || region || ')');
                    dbms_output.put_line('Počet zaměstnanců: ' || pocetZam);
                    dbms_output.put_line('Průměrná mzda: ' || round(prum_mzda,0));
                    dbms_output.put_line('Průměrně odpracováno: ' || round(prum_odprac,0));
                    dbms_output.put_line('');

      END LOOP;
    close k1;
END ZK_HR_MESTA;

EXECUTE ZK_HR_MESTA(0, 100000000, 0, 50000);