PROMPT Creating Package Body 'KONKA'
CREATE OR REPLACE PACKAGE BODY KONKA IS

FUNCTION AUTENTIKACIJA
 (P_USERNAME IN VARCHAR2
 ,P_PASSWORD IN VARCHAR2
 )
 RETURN BOOLEAN
 IS
/* Vraća "true" ako korisnik postoji i upisana je ispravna lozinka ili "false".
 * Koristi se samo za multitenant okruženje,
 * u singletenant se koristi Database Account Authentication Schema
 * (postavljena kao current u Shared Components).
 */

  vlasnikpodatka_l    NUMBER;
  vla_naziv_l         s_vlasnik.naziv%TYPE;
BEGIN
  -- vraća ili ID vlasnika ili null ako prijava nije uspjela
  EXECUTE IMMEDIATE 'SELECT pra_zmp_auth.provjeri_korisnika (:p_username, :p_password) FROM dual'
    INTO vlasnikpodatka_l USING p_username, p_password;

  IF vlasnikpodatka_l IS NOT null THEN
    -- postavljanje contexta na bazi
    pra_zmp_context.set_vlasnikpodatka(vlasnikpodatka_l);
    pra_zmp_context.set_korisnik('KONKA / ' || p_username);
    -- postavljanje APEX session varijabli
    apex_util.set_session_state( 'APP_KOR_NAZIV', p_username);
    apex_util.set_session_state( 'APP_VLASNIK_PODATKA', vlasnikpodatka_l);
    -- čitanje naziva vlasnika podatka za prikaz u footeru
    EXECUTE IMMEDIATE 'SELECT naziv FROM s_vlasnik WHERE vlasnikpodatka = :vlasnikpodatka_l'
      INTO vla_naziv_l USING vlasnikpodatka_l;
    apex_util.set_session_state( 'APP_VLA_NAZIV', vla_naziv_l);

    RETURN true;
  ELSE
    pra_zmp_context.set_vlasnikpodatka(null);
    RETURN false;
  END IF;
END;
PROCEDURE OSVJEZI_STATUS_SJ
 (P_DATUM IN DATE
 ,P_SO_ID IN NUMBER
 ,P_GSJ_ID IN NUMBER
 ,P_VSJ_ID IN NUMBER
 ,P_SJ_BROJ IN VARCHAR2
 )
 IS
/* Čita statuse svih SJ prema zadanim ulaznim parametrima i puni kolekciju STATUS_SJ,
 * koristi se na stranici Statusi SJ.
 */

  -- TODO: dodati filtriranje zapisa u samom upitu u proceduri, sada se filtrira se na klijentu
  CURSOR c_sj IS
    SELECT
      vsj_sifra,
      gsj_sifra,
      sj_broj,
      x.dat_ponisten,
      status,
      DECODE(status,
        'U', zak.naziv,
        'Z', gost.prezime || ' ' || gost.ime,
        'R', rez.prezime || ' ' || rez.ime, ''
      ) AS naziv,
      DECODE(status,
        'U', zak.dat_poc,
        'Z', gost.dat_dolaska_u_sj,
        'R', rez.dat_dolaska, ''
      ) AS dat_poc,
      DECODE(status,
        'U', zak.dat_zav,
        'Z', gost.dat_odlaska,
        'R', rez.dat_odlaska, ''
      ) AS dat_zav,
      rez_id,
      gost.id AS gos_id,
      zak_id,
      so_id,
      x.sj_id,
      gsj_id,
      x.vsj_id
    FROM
      (SELECT
        s_vsj.sifra vsj_sifra,
        m_gsj.sifra gsj_sifra,
        sj.broj sj_broj,
        sj.dat_ponisten dat_ponisten,
        sj.id sj_id,
        sj.so_id,
        m_gsj.id gsj_id,
        s_vsj.id vsj_id,
        DECODE(pra_rec.stsj_status (sj.id, TO_DATE(p_datum, 'dd.mm.rrrr')), 'R*', 'R', 'N', 'R', 'N*', 'R', pra_rec.stsj_status (sj.id, TO_DATE(p_datum, 'dd.mm.rrrr'))) AS status,
        gost_id
      FROM
        m_sj sj,
        m_so,
        s_vsj,
        m_gsj,
        (SELECT * FROM i_statusisj WHERE datum = TO_DATE(p_datum, 'dd.mm.rrrr')) statusi
      WHERE
        m_so.id = p_so_id
        AND sj.id = statusi.sj_id (+)
        AND sj.so_id = m_so.id
        AND sj.vsj_id = s_vsj.id
        AND sj.gsj_id = m_gsj.id(+)
        AND (TO_DATE(p_datum, 'dd.mm.rrrr') BETWEEN sj.dat_valjan AND NVL (sj.dat_ponisten, TO_DATE('31.12.9999', 'dd.mm.rrrr')) - 1)
      ) x,
      t_gosti gost,
      (SELECT zak.id zak_id, sj_id,NVL(age.naziv, adr.prezime || ' ' || adr.ime) naziv, zak.dat_poc, zak.dat_zav
        FROM t_zakup zak, t_zak_sj zaksj, m_agencije age, mpka_adrese adr
        WHERE zak.id = zaksj.zak_id
          AND TO_DATE(p_datum, 'dd.mm.rrrr') BETWEEN zak.dat_poc AND zak.dat_zav - 1
          AND age.id(+) = zak.age_id
          AND adr.id(+) = zak.pkaadr_id) zak,
        (SELECT rez_id, sj_id, ime, prezime, dat_dolaska, dat_odlaska
            FROM (
                SELECT rez.id rez_id, rezvsj.sj_id, ime, prezime, dat_dolaska, dat_odlaska
                  FROM t_rezervacije rez, t_rez_vsj rezvsj
                WHERE rez.id = rezvsj.rez_id
                  AND rez.oznaka = 'V'
                  AND rez.ozn_vstorna IS NULL
                  AND TO_DATE(p_datum, 'dd.mm.rrrr') BETWEEN rez.dat_dolaska AND rez.dat_odlaska - 1
                UNION
                SELECT rez.id rez_id, recrezsj.sj_id, ime, prezime, dat_dolaska, dat_odlaska
                  FROM t_rezervacije rez, t_rez_vsj rezvsj, trec_rez_sj recrezsj
                WHERE rez.id = rezvsj.rez_id
                  AND recrezsj.rezvsj_id = rezvsj.id
                  AND rez.oznaka = 'V'
                  AND rez.ozn_vstorna IS NULL
                  AND TO_DATE(p_datum, 'dd.mm.rrrr') BETWEEN rez.dat_dolaska AND rez.dat_odlaska - 1) ) rez
    WHERE x.gost_id = gost.id (+)
      AND x.sj_id = zak.sj_id (+)
      AND x.sj_id = rez.sj_id (+);
BEGIN
  -- osvježavanje liste statusa SJ
  apex_debug.info('DM: početak procedure osvjezi_status_sj' ||
    '(p_datum = %0, p_so_id = %1, p_gsj_id = %2, p_vsj_id = %3, p_sj_broj = %4)',
    p_datum, p_so_id, p_gsj_id, p_vsj_id, p_sj_broj);
  -- kreiranje kolekcije ili čišćenje ako već postoji
  apex_collection.create_or_truncate_collection
    (p_collection_name => 'STATUS_SJ');

  FOR sj_rec IN c_sj
  LOOP
    apex_debug.info('DM: u cursor loop-u osvjezi_status_sj, ' ||
      'SJ= %0, status=%1, rez_id=%2, gos_id=%3, zak_id=%4',
      sj_rec.sj_broj, sj_rec.status, sj_rec.rez_id, sj_rec.gos_id, sj_rec.zak_id);
    apex_collection.add_member(
      p_collection_name => 'STATUS_SJ',
      p_c001 => sj_rec.status,
      p_c002 => CASE sj_rec.status
                  WHEN 'U' THEN 'fa-file-text-o'
                  WHEN 'Z' THEN 'fa-users'
                  WHEN 'R' THEN 'fa-calendar-user'
                  WHEN 'I' THEN 'fa-ban'
                  ELSE 'fa-circle-o'
                END,
      p_c003 => CASE sj_rec.status
                  WHEN 'U' THEN '#1fa985'
                  WHEN 'Z' THEN '#af3f3a'
                  WHEN 'R' THEN '#dfb252'
                  WHEN 'I' THEN 'Black'
                  ELSE 'Gray'
                END,
      p_c004 => sj_rec.sj_broj,
      p_c005 => sj_rec.vsj_sifra,
      p_c006 => sj_rec.gsj_sifra,
      p_c007 => sj_rec.naziv,
      p_d001 => sj_rec.dat_poc,
      p_d002 => sj_rec.dat_zav,
      p_n001 => sj_rec.rez_id,
      p_n002 => sj_rec.gos_id,
      p_n003 => sj_rec.zak_id
    );
  END LOOP;  -- c_sj
END;
FUNCTION KOL_SJ_RASPOLOZIVE
 (P_DATUM IN DATE
 ,P_SO_ID IN NUMBER
 )
 RETURN NUMBER
 IS
/* Vraća količinu raspoloživih SJ (valjane SJ koje nisu izvan upotrebe) za zadani datum i SO. */

  l_kol_sj_valjane NUMBER;
  l_kol_sj_izvan_upotrebe NUMBER;
BEGIN
  -- valjane SJ za zadani SO i datum
  apex_debug.info('DM: početak procedure kol_sj_raspolozive(datum=%0, so_id=%1)', p_datum, p_so_id);
  SELECT COUNT(id)
  INTO l_kol_sj_valjane
  FROM m_sj
  WHERE so_id = p_so_id
    AND p_datum >= dat_valjan
    AND (p_datum < dat_ponisten OR dat_ponisten IS NULL);

  -- količina SJ izvan upotrebe
  SELECT COUNT(DISTINCT(stsj.sj_id))
  INTO l_kol_sj_izvan_upotrebe
  FROM i_statusisj stsj,
        m_sj sj
  WHERE stsj.sj_id = sj.id
    AND sj.so_id = p_so_id
    AND p_datum = stsj.datum
    AND stsj.ozn_i = 3;

  apex_debug.info('DM: kol_sj_raspolozive(datum=%0, so_id=%1)=%2',
    p_datum, p_so_id, (l_kol_sj_valjane - l_kol_sj_izvan_upotrebe));
  RETURN (l_kol_sj_valjane - l_kol_sj_izvan_upotrebe);
END;
FUNCTION KOL_SJ_SLOBODNE
 (P_DATUM IN DATE
 ,P_SO_ID IN NUMBER
 )
 RETURN NUMBER
 IS

/* Vraća količinu slobodnih SJ
 * (valjane SJ koje nisu izvan upotrebe ili zauzete) za zadani datum i SO.
 */

  l_kol_sj_valjane NUMBER;
  l_kol_sj_izvan_upotrebe NUMBER;
  l_kol_sj_zauzete NUMBER;
BEGIN
  -- valjane SJ za zadani SO i datum
  apex_debug.info('DM: početak procedure kol_sj_slobodne(datum=%0, so_id=%1)',
    p_datum, p_so_id);
  SELECT COUNT(id)
  INTO l_kol_sj_valjane
  FROM m_sj
  WHERE so_id = p_so_id
    AND p_datum >= dat_valjan
    AND (p_datum < dat_ponisten OR dat_ponisten IS NULL);

  -- količina SJ izvan upotrebe
  SELECT COUNT(DISTINCT(stsj.sj_id))
  INTO l_kol_sj_izvan_upotrebe
  FROM i_statusisj stsj,
        m_sj sj
  WHERE stsj.sj_id = sj.id
    AND sj.so_id = p_so_id
    AND p_datum = stsj.datum
    AND stsj.ozn_i = 3;

  -- količina zauzetih SJ
  SELECT COUNT (gost1.sj_id)
  INTO l_kol_sj_zauzete
  FROM t_gosti gost2,
        (
        SELECT sj.id AS sj_id,
              MIN (gost.id) AS id
        FROM t_gosti gost,
            m_sj sj
        WHERE pra_rec_s2.sj_id_recgostbor (gost.id, p_datum) = sj.id
          AND sj.so_id = p_so_id
          AND p_datum BETWEEN gost.dat_dolaska AND (gost.dat_odlaska - 1)
          AND gost.id NOT IN (SELECT id FROM trec_kplovila)
        GROUP BY sj.id
        ) gost1
  WHERE gost2.id = gost1.id;

  apex_debug.info('DM: kol_sj_slobodne(datum=%0, so_id=%1)=%2',
    p_datum, p_so_id, (l_kol_sj_valjane - l_kol_sj_izvan_upotrebe - l_kol_sj_zauzete));
  RETURN (l_kol_sj_valjane - l_kol_sj_izvan_upotrebe - l_kol_sj_zauzete);
END kol_sj_slobodne;
FUNCTION KOL_SJ_ZAUZETE
 (P_DATUM IN DATE
 ,P_SO_ID IN NUMBER
 )
 RETURN NUMBER
 IS

/* Vraća količinu zauzetih SJ (SJ u kojima borave gosti) za zadani datum i SO. */

  l_kol_sj_zauzete NUMBER;
BEGIN
  -- količina zauzetih SJ
  apex_debug.info('DM: početak procedure kol_sj_zauzete(datum=%0, so_id=%1)', p_datum, p_so_id);
  SELECT COUNT (gost1.sj_id)
  INTO l_kol_sj_zauzete
  FROM t_gosti gost2,
       (
       SELECT sj.id AS sj_id,
              MIN (gost.id) AS id
         FROM t_gosti gost, m_sj sj
        WHERE pra_rec_s2.sj_id_recgostbor (gost.id, p_datum) = sj.id
          AND sj.so_id = p_so_id
          AND p_datum BETWEEN gost.dat_dolaska AND (gost.dat_odlaska - 1)
          AND gost.id NOT IN (SELECT id FROM trec_kplovila)
        GROUP BY sj.id
       ) gost1
  WHERE gost2.id = gost1.id;

  apex_debug.info('DM: kol_sj_zauzete(datum=%0, so_id=%1)=%2',
    p_datum, p_so_id, l_kol_sj_zauzete);
  RETURN l_kol_sj_zauzete;
END kol_sj_zauzete;
FUNCTION GOST_NAPOMENA2
 (P_SJ_ID IN NUMBER
 )
 RETURN VARCHAR2
 IS

/* Vraća t_gosti.napomena2 prvoprijavljenog prisutnog gosta zadane SJ. */

  CURSOR c_gost IS
    SELECT napomena2
      FROM t_gosti
     WHERE sj_id = p_sj_id
       AND ozn_prisutnosti = 'P'
     ORDER BY god_poslovna, rbr;
  l_napomena2 t_gosti.napomena2%TYPE;
BEGIN
  OPEN c_gost;
  FETCH c_gost INTO l_napomena2;
  CLOSE c_gost;
  RETURN l_napomena2;
END gost_napomena2;
PROCEDURE AZURIRAJ_GOST_NAPOMENA2
 (P_SJ_ID IN NUMBER
 ,P_NAPOMENA2 IN VARCHAR2
 )
 IS

/* Ažurira t_gosti.napomena2 prvoprijavljenom prisutnom gostu zadane SJ. */

  CURSOR c_gost IS
    SELECT id
      FROM t_gosti
     WHERE sj_id = p_sj_id
       AND ozn_prisutnosti = 'P'
     ORDER BY god_poslovna, rbr;
  l_gost_id t_gosti.id%TYPE;
BEGIN
  apex_debug.info('DM: azuriraj_gost_napomena2(sj_id=%0, napomena=%1)', p_sj_id, p_napomena2);
  OPEN c_gost;
  FETCH c_gost INTO l_gost_id;
  CLOSE c_gost;

  IF l_gost_id IS NOT NULL THEN
    UPDATE t_gosti
       SET napomena2 = p_napomena2
     WHERE id = l_gost_id;
    COMMIT;
  END IF;
END azuriraj_gost_napomena2;

END KONKA;
/
SHOW ERROR