<%@language = "javascript" %>
<% /*
     $Revision$
     $Id$

    File:               SuggestReferentie.asp
    Description:        Suggereer (standaard)meldteksten
    Parameters:         k             De eerste paar letters
                        filtercode beperk de lijst met getoonde locaties TO BE DEFINED
                        perslidKey  : verplicht  Voor wie is de melding

    Globals:
    Context:
    Note:
*/ %>

<%
JSON_Result = true;
%>

<!-- #include file="../../Shared/common.inc" -->
<!-- #include file="../../Shared/discx3d.inc" -->
<!-- #include file="./suggest.inc" -->
<!-- #include file="./referentieFilter.inc" -->
<!-- #include file="../../mld/mld.inc" -->

<%
var chars = getQParam("k", "");
var bAll = getQParamInt("SuggestAll", 0) == 1;
var perslidKey = getQParamInt("prs_key", user_key); // Voor wie is de melding
var stdm_key = getQParamInt("stdm_key", -1); // Voor wie is de melding
var filtercode = getQParam("filtercode", "");
var excludemldkey = getQParamInt("excludemldkey", -1);

if (stdm_key > 0)
{
  var stdm_info = mld.mld_stdmeldinginfo(stdm_key);
  var kanverwijzen = stdm_info.kanverwijzen;
}
else
  kanverwijzen = 15; // Alleen M, O, R en B actief. X, C en F nog niet actief vanwege toekomstig gebruik.

chars = chars.replace(/\*/g,"%");

//var extraInf = getExtraInfReferentie(extracode);

// Ben ik zelf de perslidKey of isCollega, dan is het altijd goed.
var ikBenVerantwoordelijke = (perslidKey == user_key || user.isCollega(perslidKey));

// Heb ik dan FO of BO rechten om meldingen te lezen? Dan mag ik ook alle referenties ophalen.
var authparams = user.checkAutorisation(["WEB_MLDFOF", "WEB_MLDBOF", "WEB_MLDBAC"], true /* isOptional */, (stdm_info? stdm_info.ins_discipline_key : null));
var mld_read  = authparams && (authparams.PRSreadlevel  < 9 && authparams.ALGreadlevel  < 9);
var func_keys = authparams && authparams.autfunctionkeys;

var filt_params = { perslidKey: perslidKey,
                    funcKeys: func_keys };
var filtClause = getFiltClauseReferentie(filtercode, filt_params);


if (ikBenVerantwoordelijke || mld_read)
{ // Ik heb rechten om referenties op te halen.
  var sql = "";
  if ((kanverwijzen & 1) == 1)
  { // Meldingen
    sql = "SELECT m.mld_melding_key refkey"
        + "     , sd.ins_srtdiscipline_prefix || TO_CHAR(m.mld_melding_key) || ' - ' || "
          + lcl.xsql("md.ins_discipline_omschrijving", "md.ins_discipline_key") + " || '/' || "
          + lcl.xsql("sm.mld_stdmelding_omschrijving", "sm.mld_stdmelding_key") + " refname"
        + "     , TO_CHAR(m.mld_melding_einddatum, 'DD-MM-YYYY') refdesc"
        + "     , SUBSTR(m.mld_melding_omschrijving, 1, 200) reftitle"
        + "     , m.mld_melding_einddatum datum"
        + "     , 'M' extra"
        + "     , '' AS restype"
        + "  FROM mld_melding m"
        + "     , mld_stdmelding sm"
        + "     , mld_discipline md"
        + "     , ins_srtdiscipline sd"
        // Zoektekst op melding, vakgroep, meldingnummer.
        + "  WHERE (TO_CHAR(m.mld_melding_key) LIKE " + safe.quoted_sql_wild(chars + "%") + ")"
        + "   AND sm.mld_ins_discipline_key = md.ins_discipline_key"
        + "   AND m.mld_stdmelding_key = sm.mld_stdmelding_key"
        + "   AND sd.ins_srtdiscipline_key = md.ins_srtdiscipline_key"
        + filtClause.M
        + (excludemldkey > 0
          ? " AND mld_melding_key != " + excludemldkey
          : "")
        + "   AND TRUNC(m.mld_melding_einddatum, 'DD') >= SYSDATE - " + S("mld_reference_days");
  }

  if ((kanverwijzen & 2) == 2)
  { // Meldingsopdrachten
    if (sql != "")
      sql += " UNION ALL ";
    sql += "SELECT o.mld_opdr_key refkey"
         + "     , sd.ins_srtdiscipline_prefix || TO_CHAR (o.mld_melding_key) || '/' || TO_CHAR(o.mld_opdr_bedrijfopdr_volgnr) || ' - ' ||"
           + lcl.xsql("md.ins_discipline_omschrijving", "md.ins_discipline_key") + " || '/' || "
         + lcl.xsql("std.mld_stdmelding_omschrijving", "std.mld_stdmelding_key") + " || ' - ' || "
           + "     (SELECT u.naam FROM mld_v_uitvoerende u WHERE u.mld_uitvoerende_key = o.mld_uitvoerende_keys) refname"
         + "     , TO_CHAR(o.mld_opdr_einddatum, 'DD-MM-YYYY') refdesc"
         + "     , SUBSTR(o.mld_opdr_omschrijving, 1, 200) reftitle"
         + "     , o.mld_opdr_einddatum datum"
         + "     , 'O' extra"
         + "     , '' AS restype"
         + "  FROM mld_opdr o"
         + "     , mld_stdmelding std"
         + "     , mld_melding m"
         + "     , mld_discipline md"
         + "     , ins_srtdiscipline sd"
         + (!mld_read
           ? "   , mld_typeopdr t"
           : "")
         // Zoektekst op melding, vakgroep, uitvoerende of meldingnummer.
         + " WHERE (TO_CHAR(m.mld_melding_key) LIKE " + safe.quoted_sql_wild(chars + "%") + ")"
         + "   AND m.mld_melding_key = o.mld_melding_key"
         + "   AND std.mld_ins_discipline_key = md.ins_discipline_key"
         + "   AND o.fac_activiteit_key IS NULL"
         + "   AND sd.ins_srtdiscipline_key = md.ins_srtdiscipline_key"
         + "   AND m.mld_stdmelding_key = std.mld_stdmelding_key"
         + "   AND TRUNC(o.mld_opdr_datumbegin, 'DD') >= SYSDATE - " + S("mld_reference_days")
         + filtClause.O
         + (!mld_read
           ? " AND o.mld_typeopdr_key = t.mld_typeopdr_key"
             + " AND t.mld_typeopdr_zichtbaarfe = 1"
           : "");
  }

  if ((kanverwijzen & 4) == 4)
  { // Reserveringen
    if (sql != "")
      sql += " UNION ALL ";

    // RES_RSV_ROOM
    sql += "SELECT rm.res_rsv_ruimte_key refkey"
         + "     , 'R' || rm.res_reservering_key || '/' || rm.res_rsv_ruimte_volgnr || ' - ' || " + lcl.xsql('r.res_ruimte_nr', 'r.res_ruimte_key') + " refname"
         + "     , TO_CHAR(rm.res_rsv_ruimte_van, 'DD-MM-YYYY') refdesc"
         + "     , rm.res_rsv_ruimte_omschrijving reftitle"
         + "     , rm.res_rsv_ruimte_van datum"
         + "     , 'R' extra"
         + "     , 'R' AS restype"
         + "  FROM res_rsv_ruimte rm"
         + "     , res_ruimte_opstelling ro"
         + "     , res_ruimte r"
         + " WHERE ('R' || TO_CHAR(rm.res_reservering_key) LIKE " + safe.quoted_sql_wild("%" + chars + "%") + ")"
         + "   AND rm.res_ruimte_opstel_key = ro.res_ruimte_opstel_key"
       + "   AND ro.res_ruimte_key = r.res_ruimte_key"
         + filtClause.R
         + "   AND TRUNC(rm.res_rsv_ruimte_van, 'DD') >= SYSDATE - " + S("mld_reference_days")
         + "   AND rm.res_rsv_ruimte_verwijder IS NULL";
  }

  if ((kanverwijzen & 8) == 8)
  { // Bestellingen
    if (sql != "")
      sql += " UNION ALL ";

    sql += "SELECT b.bes_bestelling_key refkey"
         + "     , 'B' || b.bes_bestelling_key refname"
         + "     , TO_CHAR(b.bes_bestelling_datum, 'DD-MM-YYYY') refdesc"
         + "     , b.bes_bestelling_opmerking reftitle"
         + "     , b.bes_bestelling_datum datum"
         + "     , 'B' extra"
         + "     , '' AS restype"
         + "  FROM bes_bestelling b"
         // Zoektekst op bestelling nummer of bestelling opmerking.
         + " WHERE ('B' || TO_CHAR(b.bes_bestelling_key) LIKE " + safe.quoted_sql_wild("%" + chars + "%") + ")"
         + "   AND b.fac_activiteit_key IS NULL"
         // Alleen change bestellingen (embargo) tonen.
         + "   AND b.bes_bestelling_key NOT IN (SELECT b1.bes_bestelling_parentkey"
         + "                                      FROM bes_bestelling b1"
         + "                                     WHERE b1.bes_bestelling_parentkey IS NOT NULL)"
         + filtClause.B
         + "   AND TRUNC(b.bes_bestelling_datum, 'DD') > SYSDATE - " + S("mld_reference_days");
  }

  if ((kanverwijzen & 16) == 16)
  { // Bestelopdrachten
    if (sql != "")
      sql += " UNION ALL ";

        // Distinct omdat een bes_bestelopdr over meerdere bes_bestelling_items kan gaan
    var sqlBO = "SELECT DISTINCT b.bes_bestelopdr_key refkey"
              + "     , 'B' || b.bes_bestelopdr_id || ' - ' || be.prs_bedrijf_naam refname"
              + "     , TO_CHAR(b.bes_bestelopdr_datum, 'DD-MM-YYYY') refdesc"
              + "     , b.bes_bestelopdr_datum datum"
              + "     , 'X' extra"
              + "     , '' AS restype"
              + "  FROM bes_bestelopdr b"
              + ",      prs_bedrijf be"
              + ",      bes_bestelopdr_item boi"
              + ",      bes_bestelling_item bi"
              + ",      bes_bestelling bst"
              // Zoektekst op bestel opdracht id of uitvoerder.
            + " WHERE ('B' || UPPER(b.bes_bestelopdr_id) LIKE " + safe.quoted_sql_wild("%" + chars + "%") + ")"
              + "   AND be.prs_bedrijf_key = b.prs_bedrijf_key "
              + "   AND bi.bes_bestelopdr_item_key = boi.bes_bestelopdr_item_key"
              + "   AND bst.bes_bestelling_key = bi.bes_bestelling_key "
              + "   AND boi.bes_bestelopdr_key = b.bes_bestelopdr_key "
              // Alleen change bestellingopdrachten (embargo) tonen.
              + "   AND bst.bes_bestelling_key NOT IN (SELECT b1.bes_bestelling_parentkey"
              + "                                        FROM bes_bestelling b1"
              + "                                       WHERE b1.bes_bestelling_parentkey IS NOT NULL)"
              + filtClause.X
              + " AND TRUNC(b.bes_bestelopdr_datum, 'DD') > SYSDATE - " + S("mld_reference_days");


    // bes.bestelopdr_tooltip pas helemaal aan de buitenkant voor performance
    sql += "SELECT xx.refkey"
         + "     , xx.refname"
         + "     , xx.refdesc"
         + "     , bes.bestelopdr_tooltip (xx.refkey) reftitle" // Dit is een dure tooltip. Desnoods uitschakelen. Bij opvragen enkele regels gaat het wel.
         + "     , xx.datum"
         + "     , xx.extra"
         + "     , xx.restype"
         + "  FROM (" + sqlBO + ") xx";
  }

  if ((kanverwijzen & 32) == 32)
  { // Contracten
    if (sql != "")
      sql += " UNION ALL ";

    sql += "SELECT c.cnt_contract_key refkey"
         + "     , 'C' || c.cnt_contract_nummer_intern || ' - ' || c.cnt_contract_nummer || ' - ' || c.cnt_contract_omschrijving refname"
         + "     , TO_CHAR(c.cnt_contract_looptijd_van, 'DD-MM-YYYY') || ' - ' || TO_CHAR(c.cnt_contract_looptijd_tot, 'DD-MM-YYYY') refdesc"
         + "     , SUBSTR(c.cnt_contract_opmerking, 1, 200) reftitle"
         + "     , c.cnt_contract_looptijd_van datum"
         + "     , 'C' extra"
         + "     , '' AS restype"
         + "  FROM cnt_v_aanwezigcontract c"
         // Zoektekst op contractnummer, contractnummer intern of contract omschrijving.
         + " WHERE ('C' || UPPER(c.cnt_contract_nummer) LIKE " + safe.quoted_sql_wild("%" + chars + "%")
         + "        OR 'C' || UPPER(c.cnt_contract_nummer_intern) LIKE " + safe.quoted_sql_wild("%" + chars + "%") + ")"
         + filtClause.C
         // TODO!!! WELKE TIJDSPAD? HUIDIGE??
         //+ "   AND c.cnt_contract_looptijd_tot BETWEEN SYSDATE AND SYSDATE - " + S("mld_reference_days")
         + "   AND TRUNC(c.cnt_contract_looptijd_van, 'DD') < SYSDATE"
         + "   AND TRUNC(c.cnt_contract_looptijd_tot, 'DD') > SYSDATE"
         + "   AND c.cnt_contract_status IN (0, 1)";
  }

  if ((kanverwijzen & 64) == 64)
  { // Facturen
    if (sql != "")
      sql += " UNION ALL ";

    sql += "SELECT f.fin_factuur_key refkey"
         + "     , 'F' || f.fin_factuur_key || ' - ' ||"
           + "      COALESCE(sd.ins_srtdiscipline_prefix || o.mld_melding_key,"
           + "               CASE WHEN c.cnt_contract_nummer_intern IS NULL THEN '' ELSE 'C' END || c.cnt_contract_nummer_intern,"
           + "               bo.bes_bestelopdr_id) || ' (' ||"
           + "     COALESCE((SELECT b.prs_bedrijf_naam"
           + "                 FROM prs_bedrijf b"
           + "                WHERE prs_bedrijf_key = COALESCE (o.mld_uitvoerende_keys, c.cnt_prs_bedrijf_key, bo.prs_bedrijf_key)),"
           + "              (SELECT " + S("prs_dep_string")
           + "                 FROM prs_v_afdeling d"
           + "                WHERE d.prs_afdeling_key = c.cnt_prs_afdeling_key),"
           + "              (SELECT " + S("prs_pers_string")
           + "                 FROM prs_perslid p"
           + "                WHERE p.prs_perslid_key = c.cnt_prs_perslid_key),"
           + "              '-') || ')' refname"
         + "     , TO_CHAR(f.fin_factuur_datum, 'DD-MM-YYYY') refdesc"
         + "     , f.fin_factuur_opmerking reftitle"
         + "     , f.fin_factuur_datum datum"
         + "     , 'F' extra"
         + "     , '' AS restype"
         + "  FROM fin_factuur f"
         + "     , mld_opdr o"
         + "     , mld_melding m"
         + "     , mld_stdmelding sm"
         + "     , mld_discipline d"
         + "     , ins_srtdiscipline sd"
         + "     , cnt_contract c"
         + "     , bes_bestelopdr bo"
         + "     , bes_bestelopdr_item boi"
         + "     , bes_bestelling_item bi"
         + "     , bes_bestelling b"
         // Zoektekst op meldingnummer, contractnummer, bestellingnummer, factuurnummer of factuurkey.
         + " WHERE (UPPER(sd.ins_srtdiscipline_prefix) || TO_CHAR(o.mld_melding_key) LIKE " + safe.quoted_sql_wild("%" + chars + "%")
         + "        OR 'C' || UPPER(c.cnt_contract_nummer_intern) LIKE " + safe.quoted_sql_wild("%" + chars + "%")
         + "        OR UPPER(bo.bes_bestelopdr_id) LIKE " + safe.quoted_sql_wild("%" + chars + "%")
         + "        OR 'F' || UPPER(fin_factuur_nr) LIKE " + safe.quoted_sql_wild("%" + chars + "%")
         + "        OR 'F' || TO_CHAR(fin_factuur_key) LIKE " + safe.quoted_sql_wild("%" + chars + "%") + ")"
         + "   AND f.mld_opdr_key            = o.mld_opdr_key(+)"         // vast staat in welke tak we zitten
         + "   AND o.mld_melding_key         = m.mld_melding_key(+)"
         + "   AND m.mld_stdmelding_key      = sm.mld_stdmelding_key(+)"
         + "   AND sm.mld_ins_discipline_key = d.ins_discipline_key(+)"
         + "   AND d.ins_srtdiscipline_key   = sd.ins_srtdiscipline_key(+)"
         + "   AND f.cnt_contract_key        = c.cnt_contract_key(+)"
         + "   AND f.bes_bestelopdr_key      = bo.bes_bestelopdr_key(+)"
         + "   AND bo.bes_bestelopdr_key = boi.bes_bestelopdr_key(+)"
         + "   AND boi.bes_bestelopdr_item_key = bi.bes_bestelopdr_item_key(+)"
         + "   AND bi.bes_bestelling_key = b.bes_bestelling_key(+)"
         + "   AND f.fin_factuur_statuses_key = 2"
         + "   AND f.fin_factuur_verwijder IS NULL"
         + filtClause.F
         + "    AND TRUNC(f.fin_factuur_datum, 'DD') > SYSDATE - " + S("mld_reference_days");
  }

  if ((kanverwijzen & 128) == 128)
  { // Alle meldingen, niet in combinatie met een van de andere waarden voor kanverwijzen.
    sql = "";
      sql = "SELECT m.mld_melding_key refkey"
          + "     , sd.ins_srtdiscipline_prefix || TO_CHAR(m.mld_melding_key) || ' - ' || "
            + lcl.xsql("md.ins_discipline_omschrijving", "md.ins_discipline_key") + " || '/' || "
            + lcl.xsql("sm.mld_stdmelding_omschrijving", "sm.mld_stdmelding_key") + " refname"
          + "     , TO_CHAR(m.mld_melding_einddatum, 'DD-MM-YYYY') refdesc"
          + "     , SUBSTR(m.mld_melding_omschrijving, 1, 200) reftitle"
          + "     , m.mld_melding_einddatum datum"
          + "     , 'M' extra"
          + "     , '' AS restype"
          + "  FROM mld_melding m"
          + "     , mld_stdmelding sm"
          + "     , mld_discipline md"
          + "     , ins_srtdiscipline sd"
          + " WHERE (TO_CHAR (m.mld_melding_key) LIKE " + safe.quoted_sql_wild(chars + "%") + ")"
          + "   AND sm.mld_ins_discipline_key = md.ins_discipline_key"
          + "   AND m.mld_stdmelding_key = sm.mld_stdmelding_key"
          + "   AND sd.ins_srtdiscipline_key = md.ins_srtdiscipline_key"
          + filtClause.M
          + (excludemldkey > 0
            ? " AND mld_melding_key != " + excludemldkey
            : "")
          + "   AND m.mld_melding_datum >= SYSDATE - " + S("mld_reference_days");
  }
  if (sql)
    sql += " ORDER BY datum DESC, refname";
}

if (!sql)
  sql = "SELECT 'x' FROM DUAL WHERE 1 = 2";

WriteResult2(sql,
             { bAll:          bAll,
               fldName:       "refname",
               fldKey:        "refkey",
               fldDesc:       "refdesc",
               fldExtraParam: "extra",
               fldTitle:      'reftitle'
             })
%>
