<%@ LANGUAGE="JavaScript" %> <% /* $Revision$ $Id$ File: CNT/cnt_contract_scope.asp Description: Afhankelijk van het type contract wordt hier gepresenteerd de bijbehorende scope cnt_srtcontract_type Scope [1] Onderhoud/Maintenance Objecten [2] Huur/Rental Onroerend goed (ruimten/terreinen) [3] Ondersteuning/Support Plaats (locatie, gebouw, verdieping, ruimte, terrein) en/of objecten [4] Algemeen/Generic Geen [5] Leverancier/Supplier Plaats (locatie, gebouw, verdieping, ruimte, terrein) [6] Mantel/Master Geen Parameters: cnt_key Contract key Context: Note: Weet niet of status=2 wel afgehandeld moet worden als scope; dat was eerder niet zo. */ %> <% var cnt_key = getQParamInt("cnt_key"); var this_cnt = cnt.func_enabled_contract(cnt_key); // Wat heb ik zoal aan rechten op dit contract? user.auth_required_or_abort(this_cnt.canReadAny); var showall = getQParamInt("showall", 0) == 1; var outputmode = getQParamInt("outputmode", 0); %>
<% FCLTHeader.Generate({outputmode: outputmode}); %>
<% var cnt_typecontract = -1; var sql = "SELECT c.ins_discipline_key" + " , c.cnt_contract_nummer" + " , dp.cnt_srtcontract_type" + " FROM cnt_contract c, cnt_disc_params dp" + " WHERE c.ins_discipline_key = dp.cnt_ins_discipline_key" + " AND cnt_contract_key = " + cnt_key; var oRs = Oracle.Execute(sql); if (!oRs.eof) { cnt_typecontract = oRs("cnt_srtcontract_type").value; } oRs.close(); var aantal = 0; var totaal = 0; sql = "SELECT COUNT (cnt_contract_plaats_key) aantal" + " , SUM(COALESCE(cnt_contract_plaats_gewicht, 0)) totaal" + " FROM cnt_contract_plaats" + " WHERE cnt_contract_key = " + cnt_key; oRs = Oracle.Execute(sql); // Altijd resultaat aantal = oRs("aantal").value; totaal = oRs("totaal").value; var kosten = 0; sql = "SELECT cnt_contract_kosten FROM cnt_contract WHERE cnt_contract_key = " + cnt_key; oRs = Oracle.Execute(sql); if (!oRs.eof) { kosten = oRs("cnt_contract_kosten").value; } sql = ""; var scopetext = ""; switch (cnt_typecontract) { // resultaatkolommen code, scope, gewicht, type, key case 1: // Onderhoud op objecten sql = "SELECT 0 code" + " , " + lcl.xsql('S.ins_srtdeel_omschrijving','S.ins_srtdeel_key') + " scope" + " , null gewicht" + " , 'S' type " + " , s.ins_srtdeel_key key " + " FROM cnt_contract_object, ins_srtdeel S " + " WHERE cnt_ins_deel_key IS NULL" + " AND cnt_ins_srtdeel_key = S.ins_srtdeel_key" + " AND cnt_contract_object_verwijder IS NULL" + " AND cnt_contract_key = " + cnt_key + " UNION ALL" + " SELECT 1 code" + " , COALESCE( (SELECT l.alg_locatie_omschrijving || ': ' || D.alg_plaatsaanduiding || ' ' || ins_deel_omschrijving || ' (' || " + lcl.xsql('D.ins_srtdeel_omschrijving','D.ins_srtdeel_key') + " || ')'" + " FROM ins_v_deel_gegevens D, alg_locatie l" + " WHERE cnt_ins_deel_key = D.ins_deel_key" + " AND l.alg_locatie_key =D.alg_locatie_key )," + " COALESCE(( SELECT pf.prs_perslid_naam_full || ' ' || ins_deel_omschrijving || ' (' || " + lcl.xsql('SD.ins_srtdeel_omschrijving','SD.ins_srtdeel_key') + " || ')'" + " FROM ins_deel D, ins_srtdeel SD, prs_v_perslid_fullnames pf " + " WHERE cnt_ins_deel_key = D.ins_deel_key" + " AND SD.ins_srtdeel_key = D.ins_srtdeel_key" + " AND pf.prs_perslid_key = D.ins_alg_ruimte_key" + " AND D.ins_alg_ruimte_type = 'P')," + " COALESCE(( SELECT " + S("prs_dep_string") + " || ' ' ||ins_deel_omschrijving || ' (' ||" + lcl.xsql('SD.ins_srtdeel_omschrijving','SD.ins_srtdeel_key') + " || ')'" + " FROM ins_deel D, ins_srtdeel SD, prs_v_aanwezigafdeling d" + " WHERE cnt_ins_deel_key = D.ins_deel_key" + " AND SD.ins_srtdeel_key = D.ins_srtdeel_key" + " AND d.prs_afdeling_key = D.ins_alg_ruimte_key" + " AND D.ins_alg_ruimte_type = 'A'), 'onb'))) scope" + ", null gewicht" + ", 'D' type" + ", cnt_ins_deel_key key" + " FROM cnt_contract_object" + " WHERE cnt_ins_deel_key IS NOT NULL" + " AND cnt_contract_object_verwijder IS NULL" + " AND cnt_contract_key = " + cnt_key + " ORDER BY 3"; scopetext = L("lcl_cnt_objects"); break; case 2: // Huur van ruimte of terreinsector: deze werden eigenlijk niet direct als scope getoond geloof ik sql = " SELECT NULL code" + ", plaatsaanduiding scope" + ", NULL gewicht" + ", type type" + ", totaal_opp" + ", contract_opp" + ", tariefsrt" + ", locatie" + ", (contract_opp * prijs) totaalprijs" + ", alg_onrgoed_key key" + " FROM" + " (SELECT co.alg_onrgoed_key" + ", rm.alg_ruimte_aanduiding plaatsaanduiding" + ", (SELECT fac.safe_to_number(alg_onrgoedkenmerk_waarde)" + " FROM alg_onrgoedkenmerk" + " WHERE alg_onrgoed_key = co.alg_onrgoed_key" + " AND alg_kenmerk_key = " + S("alg_ruimte_comm_opp_key") + ") totaal_opp" + ", co.cnt_contract_onrgoed_opp contract_opp" + ", alg_srtruimte_code tariefsrt" + ", rm.alg_locatie_omschrijving locatie" + ", sr.alg_srtruimte_prijs prijs" + ", 'R' type" + " FROM alg_v_ruimte_gegevens rm" + ", cnt_contract_onrgoed co" + ", alg_srtruimte sr" + " WHERE co.alg_onrgoed_niveau = 'R'" + " AND co.alg_onrgoed_key = rm.ALG_RUIMTE_KEY" + " AND sr.alg_srtruimte_key = co.alg_srtonrgoed_key" + " AND co.cnt_mld_melding_key IS NULL" + " AND co.cnt_contract_key = " + cnt_key + "" + " UNION" + " SELECT co.alg_onrgoed_key" + ", lc.alg_locatie_code || ' - ' || alg_terreinsector_omschrijving plaatsaanduiding" + ", (SELECT fac.safe_to_number(alg_onrgoedkenmerk_waarde) from alg_onrgoedkenmerk" + " WHERE alg_onrgoed_key = co.alg_onrgoed_key" + " AND alg_kenmerk_key = " + S("alg_terrein_comm_opp_key") + ") totaal_opp" + ", co.cnt_contract_onrgoed_opp contract_opp" + ", sr.alg_srtterreinsector_code tariefsrt" + ", lc.alg_locatie_omschrijving locatie" + ", sr.alg_srtterreinsector_prijs prijs" + ", 'T' type" + " FROM alg_terreinsector tr" + ", cnt_contract_onrgoed co" + ", alg_srtterreinsector sr" + ", alg_locatie lc" + " WHERE co.alg_onrgoed_niveau = 'T'" + " AND co.alg_onrgoed_key = tr.alg_terreinsector_key" + " AND sr.alg_srtterreinsector_key = co.alg_srtonrgoed_key" + " AND lc.alg_locatie_key = tr.alg_locatie_key" + " AND co.cnt_mld_melding_key IS NULL" + " AND co.cnt_contract_key = " + cnt_key + "" + " UNION" + " SELECT co.alg_onrgoed_key" + ", " + safe.quoted_sql(L("lcl_location")) + " plaatsaanduiding" + ", null totaal_opp" + ", co.cnt_contract_onrgoed_opp contract_opp" + ", sr.alg_srtruimte_code tariefsrt" + ", lc.alg_locatie_omschrijving locatie" + ", COALESCE(co.cnt_srtruimte_prijs,0) prijs" + ", 'L' type" + " FROM alg_locatie lc" + ", cnt_contract_onrgoed co" + ", alg_srtruimte sr" + " WHERE co.alg_onrgoed_niveau = 'L'" + " AND co.alg_onrgoed_key = lc.alg_locatie_key" + " AND sr.alg_srtruimte_key = co.alg_srtonrgoed_key" + " AND co.cnt_mld_melding_key IS NULL" + " AND co.cnt_contract_key = " + cnt_key + ")"; break; case 3: // onderhoud op objectsoorten en/of plaats case 5: // onderhoud op plaats if (cnt_typecontract == 3) { sql = "SELECT ins_srtdeel_code code" + " , " + lcl.xsql('sd.ins_srtdeel_omschrijving','SD.ins_srtdeel_key') + " scope" + " , null gewicht" + " , 'S' type" + " , co.cnt_ins_srtdeel_key key" + " FROM cnt_contract_object co" + " , ins_srtdeel sd" + " WHERE co.cnt_ins_srtdeel_key = sd.ins_srtdeel_key" + " AND co.cnt_contract_object_verwijder IS NULL" + " AND sd.ins_srtdeel_verwijder IS NULL" + " AND cnt_contract_key = " + cnt_key + " UNION ALL "; } sql += "SELECT cnt_alg_plaats_code" + " , l.alg_locatie_omschrijving || ' (' || l.alg_locatie_code || ')' scope" + " , cnt_contract_plaats_gewicht gewicht" + " , cnt_alg_plaats_code type" + " , cnt_alg_plaats_key key" + " FROM cnt_contract_plaats" + " , alg_locatie l" + " WHERE cnt_alg_plaats_key = l.alg_locatie_key" + " AND cnt_alg_plaats_code = 'L'" + " AND cnt_contract_plaats_verwijder IS NULL" + " AND cnt_contract_key = " + cnt_key + " UNION " + "SELECT cnt_alg_plaats_code" + " , l.alg_locatie_omschrijving || ' (' || l.alg_locatie_code || '): " + L("lcl_building") + " ' || g.alg_gebouw_omschrijving scope" + " , cnt_contract_plaats_gewicht gewicht, cnt_alg_plaats_code type" + " , cnt_alg_plaats_key key" + " FROM cnt_contract_plaats, alg_locatie l" + " , alg_gebouw g " + " WHERE cnt_alg_plaats_code = 'G'" + " AND cnt_alg_plaats_key=g.alg_gebouw_key" + " AND g.alg_locatie_key=l.alg_locatie_key" + " AND cnt_contract_plaats_verwijder IS NULL" + " AND cnt_contract_key = " + cnt_key + " UNION " + "SELECT cnt_alg_plaats_code" + " , l.alg_locatie_omschrijving || ' (' || l.alg_locatie_code || '): " + L("lcl_terra") + " ' || t.alg_terreinsector_omschrijving scope" + " , cnt_contract_plaats_gewicht gewicht" + " , cnt_alg_plaats_code type" + " , cnt_alg_plaats_key key" + " FROM cnt_contract_plaats" + " , alg_locatie l" + " , alg_terreinsector t" + " WHERE cnt_alg_plaats_code = 'T'" + " AND cnt_alg_plaats_key=t.alg_terreinsector_key" + " AND t.alg_locatie_key=l.alg_locatie_key" + " AND cnt_contract_plaats_verwijder IS NULL" + " AND cnt_contract_key = " + cnt_key + " ORDER BY 4,2"; scopetext = L("lcl_cnt_locations"); break; case 4: // scopeloos case 6: // mantelcontract sql = "SELECT 'x' code, 'x' scope, 'x' gewicht, 'x' type, -1 key FROM DUAL WHERE 1=0"; break; } function fnrowData(oRs) { var scopeType = oRs("type").Value; var data = {scopeType: scopeType}; return JSON.stringify(data); } function fnScopetype(oRs) { var result = ""; switch (oRs("type").value) { case 'G' : result = L("lcl_building"); break; case 'V' : result = L("lcl_floor"); break; case 'R' : result = L("lcl_room"); break; case 'L' : result = L("lcl_location"); break; case 'D' : result = L("lcl_obj_inf_name"); break; case 'S' : result = L("lcl_obj_sort"); break; } return result; } function fnGewicht(oRs) { var result = ""; if (aantal > 0 && totaal != 0) result = (kosten * oRs("gewicht").value / totaal); return safe.curr(result); } function fnTotaal_opp(oRs) { if (!oRs("totaal_opp").Value) return L("lcl_cnt_unknown"); return safe.displayfloat(oRs("totaal_opp").Value); } var buttons = []; if (cnt_typecontract != 2 && this_cnt.canChange) { buttons.push({title: L("lcl_change"), icon: "wijzigen.png", action: "cnt_scopechange()" }); } var rst = new ResultsetTable({ sql: sql, keyColumn: "key", ID: "cntscopetable", title: L("lcl_cnt_frame_scope"), showAll: showall, outputmode: outputmode, rowData: fnrowData, buttons: buttons }); if (cnt_typecontract == 2) rst.addColumn(new Column({ caption: L("lcl_location"), content: "locatie" })); rst.addColumn(new Column({ caption: L("lcl_cnt_scope_title"), content: "scope", hasActions: true, tooltip: fnScopetype })); if (cnt_typecontract != 1 && cnt_typecontract != 2) rst.addColumn(new Column({ caption: L("lcl_cnt_verdeling"), content: fnGewicht, datatype: "currency" })); if (cnt_typecontract == 2) { rst.addColumn(new Column({ caption: L("lcl_cnt_ruimte_opp"), content: fnTotaal_opp, datatype: "float" })); rst.addColumn(new Column({ caption: L("lcl_cnt_ruimte_occ"), content: "contract_opp", datatype: "float", total: true })); rst.addColumn(new Column({ caption: L("lcl_cnt_ruimte_tarief"), content: "tariefsrt" })); rst.addColumn(new Column({ caption: L("lcl_total_cost"), content: "totaalprijs", datatype: "currency", total: true })); } rst.addAction({ action: "gotoScope", caption: L("lcl_details"), isDefault: true }); var cnt = rst.processResultset(); %>