Automatisierung der Datenerhebung für niederländische öffentliche Ausschreibungen mit TenderNed

• •Möglicherweise sind Ziel-API-Anmeldedaten erforderlich

{
  "meta": {
    "instanceId": "89249a8a187ba6e01e16112a0d334a3aa01d510ad8f88d223e12cc0a2a8beb6b"
  },
  "nodes": [
    {
      "id": "7369f055-b55d-491c-8dee-12988a5c2dc8",
      "name": "Haal XML Details",
      "type": "n8n-nodes-base.httpRequest",
      "notes": "Haalt volledige XML voor elke tender",
      "position": [
        1296,
        864
      ],
      "parameters": {
        "url": "=https://www.tenderned.nl/papi/tenderned-rs-tns/v2/publicaties/{{ $json.publicaties.publicatieId }}/public-xml",
        "options": {
          "timeout": 30000,
          "response": {
            "response": {
              "responseFormat": "text"
            }
          }
        },
        "authentication": "genericCredentialType",
        "genericAuthType": "httpBasicAuth"
      },
      "notesInFlow": true,
      "typeVersion": 4.2
    },
    {
      "id": "ecb51d0b-b6a9-4b50-879d-6f7aa7240bff",
      "name": "Bei Klick auf 'Workflow ausführen'",
      "type": "n8n-nodes-base.manualTrigger",
      "position": [
        -560,
        768
      ],
      "parameters": {},
      "typeVersion": 1
    },
    {
      "id": "bf05ee04-4ee0-4217-8b02-7fe5e8f67600",
      "name": "Aufteilen",
      "type": "n8n-nodes-base.splitOut",
      "position": [
        432,
        976
      ],
      "parameters": {
        "include": "allOtherFields",
        "options": {},
        "fieldToSplitOut": "publicaties"
      },
      "typeVersion": 1
    },
    {
      "id": "ed07bb23-b6ed-4cff-9327-d410150d061a",
      "name": "Über Elemente iterieren",
      "type": "n8n-nodes-base.splitInBatches",
      "position": [
        832,
        976
      ],
      "parameters": {
        "options": {}
      },
      "typeVersion": 3
    },
    {
      "id": "6855d01b-c1bd-429d-a2c6-610528ec192c",
      "name": "XML",
      "type": "n8n-nodes-base.xml",
      "position": [
        1472,
        864
      ],
      "parameters": {
        "options": {
          "trim": true
        }
      },
      "typeVersion": 1
    },
    {
      "id": "7c209557-d141-43d4-9fa9-ced8cc06229d",
      "name": "Zusammenführen",
      "type": "n8n-nodes-base.merge",
      "position": [
        1840,
        928
      ],
      "parameters": {
        "numberInputs": 3
      },
      "typeVersion": 3.2
    },
    {
      "id": "f49fa844-4585-4274-8086-3c7bd8cef555",
      "name": "Haal JSON Details",
      "type": "n8n-nodes-base.httpRequest",
      "notes": "Haalt JSON details: kenmerk, pbNummerTed, trefwoorden",
      "position": [
        1376,
        704
      ],
      "parameters": {
        "url": "=https://www.tenderned.nl/papi/tenderned-rs-tns/v2/publicaties/{{ $json.publicaties.publicatieId }}",
        "options": {
          "timeout": 30000
        },
        "authentication": "genericCredentialType",
        "genericAuthType": "httpBasicAuth"
      },
      "typeVersion": 4.2
    },
    {
      "id": "1e619b5f-9fdb-44af-95f2-1c4c45fa5703",
      "name": "Aggregieren",
      "type": "n8n-nodes-base.aggregate",
      "position": [
        2240,
        944
      ],
      "parameters": {
        "options": {},
        "aggregate": "aggregateAllItemData",
        "destinationFieldName": "allData"
      },
      "typeVersion": 1
    },
    {
      "id": "e1816ca0-4f73-449f-a9b5-7a0b9e6cd7e3",
      "name": "Alle Felder aufteilen",
      "type": "n8n-nodes-base.code",
      "notes": "Pakt ALLE XML velden uit naar losse, bruikbare velden",
      "position": [
        2544,
        944
      ],
      "parameters": {
        "jsCode": "// ============================================\n// VERSIE 6: COMPLETE FIX + GUNNINGSCRITERIA + PLATFORM\n// Node: \"Splits Alle Velden\"\n// Toevoegingen: Gunningscriteria wegingen + Platform detectie\n// ============================================\n\n// Extract data sources from aggregated input\nconst aggregatedData = $json.allData || [];\n\nconsole.log(`📊 Aggregate bevat ${aggregatedData.length} items`);\n\n// Initialize data containers\nlet xmlData = {};\nlet publicatiesData = {};\nlet jsonDetail = null;\n\n// Separate the three data sources\nfor (const item of aggregatedData) {\n  // Check XML data\n  if (item.PriorInformationNotice || item.ContractNotice || item.ContractAwardNotice) {\n    xmlData = item.PriorInformationNotice || item.ContractNotice || item.ContractAwardNotice || {};\n    console.log('✅ XML data gevonden');\n  }\n  // Check publicaties data  \n  else if (item.publicaties) {\n    publicatiesData = item.publicaties;\n    console.log('✅ Publicaties data gevonden');\n  }\n  // Check JSON detail data\n  else if (item.publicatieId || item.kenmerk || item.pbNummerTed) {\n    jsonDetail = item;\n    console.log('✅ JSON detail data gevonden');\n  }\n}\n\n// ============================================\n// FLATTEN FUNCTIE\n// ============================================\nfunction flattenXML(obj, prefix = '') {\n  let result = {};\n  for (const key in obj) {\n    const value = obj[key];\n    const newKey = prefix ? `${prefix}_${key}` : key;\n    \n    if (value === null || value === undefined) {\n      result[newKey] = null;\n    } else if (typeof value === 'object' && !Array.isArray(value)) {\n      Object.assign(result, flattenXML(value, newKey));\n    } else if (Array.isArray(value)) {\n      result[newKey] = value;\n    } else {\n      result[newKey] = value;\n    }\n  }\n  return result;\n}\n\n// Flatten XML & Publicaties\nconst alleXMLVelden = flattenXML(xmlData, 'xml');\n\n// ============================================\n// FIX 1: NOTE VELD (CORRECT PAD)\n// ============================================\nconst cbcNote = alleXMLVelden['xml_cac:ProcurementProject_cbc:Note__'] || '';\nconsole.log(`✅ Note veld: ${cbcNote ? 'GEVONDEN' : 'NIET GEVONDEN'}`);\n\n// ============================================\n// FIX 2: DESCRIPTIONS (HOOFD + ALLE PERCELEN)\n// ============================================\nconst hoofdDescription = alleXMLVelden['xml_cac:ProcurementProject_cbc:Description__'] || '';\nconst lots = alleXMLVelden['xml_cac:ProcurementProjectLot'] || [];\n\nlet cbcDescription = hoofdDescription;\nlet perceelInfoArray = [];\n\nif (Array.isArray(lots) && lots.length > 0) {\n  console.log(`✅ Aantal percelen: ${lots.length}`);\n  \n  lots.forEach((lot, index) => {\n    const perceelProj = lot?.['cac:ProcurementProject'] || {};\n    const perceelNaam = perceelProj?.['cbc:Name']?.['_'] || `Perceel ${index + 1}`;\n    const perceelDesc = perceelProj?.['cbc:Description']?.['_'] || '';\n    const perceelId = lot?.['cbc:ID']?.['_'] || '';\n    \n    // Duration\n    const durationObj = perceelProj?.['cac:PlannedPeriod']?.['cbc:DurationMeasure'];\n    const perceelDuur = durationObj?.['_'] || '';\n    const perceelDuurEenheid = durationObj?.['unitCode'] || '';\n    \n    // CPV codes per perceel\n    const mainCpv = perceelProj?.['cac:MainCommodityClassification']?.['cbc:ItemClassificationCode']?.['_'] || '';\n    const addCpv = perceelProj?.['cac:AdditionalCommodityClassification']?.['cbc:ItemClassificationCode']?.['_'] || '';\n    \n    // Store perceel info\n    perceelInfoArray.push({\n      id: perceelId,\n      naam: perceelNaam,\n      beschrijving: perceelDesc,\n      duur: perceelDuur,\n      duurEenheid: perceelDuurEenheid,\n      cpvMain: mainCpv,\n      cpvAdditional: addCpv\n    });\n    \n    // Add to combined description\n    if (perceelDesc) {\n      cbcDescription += `\\n\\n=== ${perceelNaam} ===\\n${perceelDesc}`;\n    }\n    \n    console.log(`   Perceel ${index + 1}: ${perceelNaam} (${perceelDuur} ${perceelDuurEenheid})`);\n  });\n}\n\n// ============================================\n// FIX 3: GESCHATTE WAARDE\n// ============================================\nconst geschatteWaarde = alleXMLVelden['xml_cac:ProcurementProject_cac:RequestedTenderTotal_cbc:EstimatedOverallContractAmount__'] || '';\nconst geschatteWaardeCurrency = alleXMLVelden['xml_cac:ProcurementProject_cac:RequestedTenderTotal_cbc:EstimatedOverallContractAmount_currencyID'] || 'EUR';\n\nlet geschatteWaardeFormatted = '';\nif (geschatteWaarde) {\n  const bedrag = parseFloat(geschatteWaarde);\n  if (!isNaN(bedrag)) {\n    geschatteWaardeFormatted = `€${bedrag.toLocaleString('nl-NL')}`;\n  }\n}\nconsole.log(`✅ Geschatte waarde: ${geschatteWaardeFormatted || 'Niet opgegeven'}`);\n\n// ============================================\n// FIX 4: DEADLINE VOOR VRAGEN\n// ============================================\nlet deadlineVragen = '';\nlet deadlineVragenTime = '';\n\nif (Array.isArray(lots) && lots.length > 0) {\n  const firstLot = lots[0];\n  const infoRequestPeriod = firstLot?.['cac:TenderingProcess']?.['cac:AdditionalInformationRequestPeriod'];\n  deadlineVragen = infoRequestPeriod?.['cbc:EndDate'] || '';\n  deadlineVragenTime = infoRequestPeriod?.['cbc:EndTime'] || '';\n}\n\nconst deadlineVragenFull = deadlineVragen && deadlineVragenTime \n  ? `${deadlineVragen} ${deadlineVragenTime}` \n  : deadlineVragen;\n  \nconsole.log(`✅ Deadline vragen: ${deadlineVragenFull || 'Niet opgegeven'}`);\n\n// ============================================\n// FIX 5: RAAMOVEREENKOMST TYPE\n// ============================================\nlet raamovereenkomstType = '';\nif (Array.isArray(lots) && lots.length > 0) {\n  const firstLot = lots[0];\n  const contractingSystems = firstLot?.['cac:TenderingProcess']?.['cac:ContractingSystem'];\n  if (Array.isArray(contractingSystems)) {\n    const faSystem = contractingSystems.find(cs => \n      cs?.['cbc:ContractingSystemTypeCode']?.['listName'] === 'framework-agreement'\n    );\n    raamovereenkomstType = faSystem?.['cbc:ContractingSystemTypeCode']?.['_'] || '';\n  }\n}\nconsole.log(`✅ Raamovereenkomst type: ${raamovereenkomstType || 'Niet van toepassing'}`);\n\n// ============================================\n// FIX 6: CONTACT INFORMATIE\n// ============================================\nconst orgData = alleXMLVelden['xml_ext:UBLExtensions_ext:UBLExtension_ext:ExtensionContent_efext:EformsExtension_efac:Organizations_efac:Organization_efac:Company'] || {};\n\nconst contactNaam = orgData?.['cac:Contact_cbc:Name'] || '';\nconst contactTelefoon = orgData?.['cac:Contact_cbc:Telephone'] || '';\nconst contactEmail = orgData?.['cac:Contact_cbc:ElectronicMail'] || '';\nconst organisatieWebsite = orgData?.['cbc:WebsiteURI'] || '';\n\nconsole.log(`✅ Contact: ${contactNaam || 'Niet opgegeven'}`);\n\n// ============================================\n// FIX 7: RECHTBANK / REVIEW BODY\n// ============================================\nconst touchpointData = alleXMLVelden['xml_ext:UBLExtensions_ext:UBLExtension_ext:ExtensionContent_efext:EformsExtension_efac:Organizations_efac:Organization_efac:TouchPoint'] || {};\n\nconst reviewBodyNaam = touchpointData?.['cac:PartyName_cbc:Name__'] || '';\nconst reviewBodyEmail = touchpointData?.['cac:Contact_cbc:ElectronicMail'] || '';\nconst reviewBodyTelefoon = touchpointData?.['cac:Contact_cbc:Telephone'] || '';\n\n// ============================================\n// FIX 8: GERELATEERDE PUBLICATIES\n// ============================================\nlet gerelateerdePublicatiesText = '';\nlet gerelateerdePublicatiesIds = [];\n\nif (jsonDetail?.gerelateerdePublicaties && Array.isArray(jsonDetail.gerelateerdePublicaties)) {\n  jsonDetail.gerelateerdePublicaties.forEach(pub => {\n    gerelateerdePublicatiesIds.push(pub.publicatieId);\n    const pubDate = pub.publicatieDatum ? pub.publicatieDatum.split('T')[0] : '';\n    gerelateerdePublicatiesText += `${pub.typePublicatie} (ID: ${pub.publicatieId}, Datum: ${pubDate})\\n`;\n  });\n  console.log(`✅ Gerelateerde publicaties: ${gerelateerdePublicatiesIds.length}`);\n}\n\n// ============================================\n// FIX 9: UBL METADATA\n// ============================================\nconst ublVersionId = alleXMLVelden['xml_cbc:UBLVersionID'] || '';\nconst customizationId = alleXMLVelden['xml_cbc:CustomizationID'] || '';\nconst noticeId = alleXMLVelden['xml_cbc:ID__'] || '';\nconst issueDate = alleXMLVelden['xml_cbc:IssueDate'] || '';\nconst plannedDate = alleXMLVelden['xml_cbc:PlannedDate'] || '';\n\n// ============================================\n// NIEUW: GUNNINGSCRITERIA WEGINGEN\n// ============================================\n\nlet gunningsCriteriaText = '';\nlet criteriaArray = [];\n\nif (Array.isArray(lots) && lots.length > 0) {\n  lots.forEach((lot, lotIndex) => {\n    const tenderingTerms = lot?.['cac:TenderingTerms'];\n    const awardingTerms = tenderingTerms?.['cac:AwardingTerms'];\n    const awardingCriteria = awardingTerms?.['cac:AwardingCriterion'];\n    \n    if (awardingCriteria) {\n      const criteriaList = Array.isArray(awardingCriteria) ? awardingCriteria : [awardingCriteria];\n      const perceelNaam = lot?.['cac:ProcurementProject']?.['cbc:Name']?.['_'] || `Perceel ${lotIndex + 1}`;\n      \n      gunningsCriteriaText += `\\n=== ${perceelNaam} ===\\n`;\n      \n      criteriaList.forEach(criterion => {\n        const description = criterion?.['cbc:Description']?.['_'] || '';\n        const weight = criterion?.['cbc:Weight']?.['_'] || criterion?.['cbc:WeightNumeric']?.['_'] || '';\n        const calculationExpression = criterion?.['cbc:CalculationExpression']?.['_'] || '';\n        \n        // Sub-criteria\n        const subCriteria = criterion?.['cac:SubordinateAwardingCriterion'];\n        \n        if (description || weight) {\n          const criteriaLine = weight \n            ? `${description}: ${weight}%` \n            : description;\n          \n          gunningsCriteriaText += `• ${criteriaLine}\\n`;\n          \n          criteriaArray.push({\n            perceel: perceelNaam,\n            criterium: description,\n            weging: weight,\n            berekening: calculationExpression\n          });\n        }\n        \n        // Process sub-criteria\n        if (subCriteria) {\n          const subList = Array.isArray(subCriteria) ? subCriteria : [subCriteria];\n          subList.forEach(subCrit => {\n            const subDesc = subCrit?.['cbc:Description']?.['_'] || '';\n            const subWeight = subCrit?.['cbc:Weight']?.['_'] || subCrit?.['cbc:WeightNumeric']?.['_'] || '';\n            \n            if (subDesc || subWeight) {\n              const subLine = subWeight \n                ? `${subDesc}: ${subWeight}%` \n                : subDesc;\n              \n              gunningsCriteriaText += `  - ${subLine}\\n`;\n              \n              criteriaArray.push({\n                perceel: perceelNaam,\n                criterium: `${description} > ${subDesc}`,\n                weging: subWeight,\n                parent: description\n              });\n            }\n          });\n        }\n      });\n    }\n  });\n}\n\n// Als er geen criteria in percelen staan, zoek op hoofdniveau\nif (!gunningsCriteriaText) {\n  const mainTenderingTerms = alleXMLVelden['xml_cac:TenderingTerms'];\n  const mainAwardingTerms = mainTenderingTerms?.['cac:AwardingTerms'];\n  const mainCriteria = mainAwardingTerms?.['cac:AwardingCriterion'];\n  \n  if (mainCriteria) {\n    const criteriaList = Array.isArray(mainCriteria) ? mainCriteria : [mainCriteria];\n    gunningsCriteriaText = '=== Gunningscriteria ===\\n';\n    \n    criteriaList.forEach(criterion => {\n      const description = criterion?.['cbc:Description']?.['_'] || '';\n      const weight = criterion?.['cbc:Weight']?.['_'] || criterion?.['cbc:WeightNumeric']?.['_'] || '';\n      \n      if (description || weight) {\n        const criteriaLine = weight \n          ? `${description}: ${weight}%` \n          : description;\n        \n        gunningsCriteriaText += `• ${criteriaLine}\\n`;\n        \n        criteriaArray.push({\n          criterium: description,\n          weging: weight\n        });\n      }\n    });\n  }\n}\n\nconsole.log(`✅ Gunningscriteria: ${criteriaArray.length} criteria gevonden`);\n\n// ============================================\n// NIEUW: PLATFORM DETECTIE\n// ============================================\n\nlet platform = 'TenderNed'; // Default\nlet platformUrl = '';\n\n// Check de URL uit JSON detail\nif (jsonDetail?.urlTsenderWebsite) {\n  platformUrl = jsonDetail.urlTsenderWebsite.toLowerCase();\n  \n  if (platformUrl.includes('mercell.com') || platformUrl.includes('opic.com')) {\n    platform = 'Mercell';\n  } else if (platformUrl.includes('negometrix.com')) {\n    platform = 'Negometrix';\n  } else if (platformUrl.includes('ted.europa.eu')) {\n    platform = 'TED (Europa)';\n  } else if (platformUrl.includes('tenderned.nl')) {\n    platform = 'TenderNed';\n  } else if (platformUrl.includes('pianoo.nl')) {\n    platform = 'PIANOo';\n  } else if (platformUrl) {\n    // Onbekend platform maar wel een URL\n    platform = 'Overig';\n  }\n}\n\nconsole.log(`✅ Platform gedetecteerd: ${platform}`);\n\n// ============================================\n// EXTRACT CPV CODES (BESTAANDE FUNCTIONALITEIT)\n// ============================================\nconst cpvCodes = [];\nfor (const key in alleXMLVelden) {\n  if (key.includes('ItemClassificationCode__') && alleXMLVelden[key]) {\n    cpvCodes.push(alleXMLVelden[key]);\n  }\n}\nconst uniqueCpvCodes = [...new Set(cpvCodes)];\n\n// ============================================\n// PROCESS JSON DETAIL VELDEN (BESTAAND)\n// ============================================\nlet json_trefwoorden = '-';\nlet json_hoofdOpdracht = '';\nlet json_bijkomendeOpdrachten = '';\nlet json_nutsCode = '';\n\nif (jsonDetail) {\n  // Trefwoorden\n  const trefwoorden = [\n    jsonDetail.trefwoord1?.replace(/\\\"/g, ''),\n    jsonDetail.trefwoord2?.replace(/\\\"/g, ''),\n    jsonDetail.trefwoord3?.replace(/\\\"/g, '')\n  ].filter(Boolean);\n  json_trefwoorden = trefwoorden.length > 0 ? trefwoorden.join(', ') : '-';\n  \n  // CPV codes met omschrijvingen\n  if (jsonDetail.cpvCodes && Array.isArray(jsonDetail.cpvCodes)) {\n    const hoofdCpv = jsonDetail.cpvCodes.find(c => c.isHoofdOpdracht);\n    const bijkomendeCpvs = jsonDetail.cpvCodes.filter(c => !c.isHoofdOpdracht);\n    json_hoofdOpdracht = hoofdCpv ? `${hoofdCpv.code} ${hoofdCpv.omschrijving}` : '';\n    json_bijkomendeOpdrachten = bijkomendeCpvs.length > 0 ? bijkomendeCpvs.map(c => `${c.code} ${c.omschrijving}`).join(', ') : '';\n  }\n  \n  // NUTS code\n  if (jsonDetail.nutsCodes && Array.isArray(jsonDetail.nutsCodes) && jsonDetail.nutsCodes.length > 0) {\n    json_nutsCode = jsonDetail.nutsCodes[0].omschrijving || '';\n  }\n}\n\n// ============================================\n// GET PUBLICATIE ID FOR URLs\n// ============================================\nconst publicatieId = jsonDetail?.publicatieId || publicatiesData.publicatieId || '';\nconst correctTenderNedUrl = `https://www.tenderned.nl/aankondigingen/overzicht/${publicatieId}`;\nconst originalLink = jsonDetail?.urlTsenderWebsite || correctTenderNedUrl;\n\n// ============================================\n// LOGGING\n// ============================================\nconsole.log(`✅ CPV codes: ${uniqueCpvCodes.join(', ')}`);\nconsole.log(`✅ Trefwoorden: ${json_trefwoorden}`);\nconsole.log(`✅ Beschrijving (inclusief percelen): ${cbcDescription.length} tekens`);\nconsole.log(`✅ Note: ${cbcNote.length} tekens`);\n\n// ============================================\n// RETURN ALLE VELDEN (INCLUSIEF NIEUWE)\n// ============================================\nreturn {\n  json: {\n    // Alle platte XML velden\n    ...alleXMLVelden,\n    \n    // CPV codes\n    cpv_codes: uniqueCpvCodes,\n    cpv_main: uniqueCpvCodes[0] || null,\n    cpv_all_text: uniqueCpvCodes.join(', '),\n    \n    // JSON detail fields (basis)\n    json_publicatieId: publicatieId,\n    json_kenmerk: jsonDetail?.kenmerk || null,\n    json_referentieNummer: jsonDetail?.referentieNummer || '',\n    json_aanbestedingNaam: jsonDetail?.aanbestedingNaam || publicatiesData.aanbestedingNaam || '',\n    json_opdrachtgeverNaam: jsonDetail?.opdrachtgeverNaam || publicatiesData.opdrachtgeverNaam || '',\n    json_opdrachtBeschrijving: jsonDetail?.opdrachtBeschrijving || publicatiesData.opdrachtBeschrijving || '',\n    json_publicatieDatum: jsonDetail?.publicatieDatum || publicatiesData.publicatieDatum || '',\n    json_sluitingsDatum: jsonDetail?.sluitingsDatum || publicatiesData.sluitingsDatum || '',\n    json_pbNummerTed: jsonDetail?.pbNummerTed || '',\n    json_juridischKader: jsonDetail?.juridischKaderCode?.omschrijving || '',\n    json_typeOpdracht: jsonDetail?.typeOpdrachtCode?.omschrijving || '',\n    json_aardOpdracht: jsonDetail?.opdrachtAardCode?.omschrijving || '',\n    json_procedure: jsonDetail?.procedureCode?.omschrijving || '',\n    json_europeesNationaal: jsonDetail?.nationaalOfEuropeesCode?.omschrijving || '',\n    json_trefwoorden: json_trefwoorden,\n    json_hoofdOpdracht: json_hoofdOpdracht,\n    json_bijkomendeOpdrachten: json_bijkomendeOpdrachten,\n    json_nutsCode: json_nutsCode,\n    \n    // FIX 1: Gecorrigeerd Note veld\n    xml_cbcNote: cbcNote,\n    \n    // FIX 2: Gecombineerde beschrijving (hoofd + percelen)\n    xml_cbcDescription: cbcDescription,\n    \n    // FIX 3: Geschatte waarde\n    xml_geschatteWaarde: geschatteWaarde,\n    xml_geschatteWaardeCurrency: geschatteWaardeCurrency,\n    xml_geschatteWaardeFormatted: geschatteWaardeFormatted,\n    \n    // FIX 4: Deadline voor vragen\n    xml_deadlineVragen: deadlineVragenFull,\n    \n    // FIX 5: Raamovereenkomst type\n    xml_raamovereenkomstType: raamovereenkomstType,\n    \n    // FIX 6: Contact informatie\n    xml_contactNaam: contactNaam,\n    xml_contactTelefoon: contactTelefoon,\n    xml_contactEmail: contactEmail,\n    xml_organisatieWebsite: organisatieWebsite,\n    \n    // FIX 7: Review body (rechtbank)\n    xml_reviewBodyNaam: reviewBodyNaam,\n    xml_reviewBodyEmail: reviewBodyEmail,\n    xml_reviewBodyTelefoon: reviewBodyTelefoon,\n    \n    // FIX 8: Gerelateerde publicaties\n    json_gerelateerdePublicaties: gerelateerdePublicatiesText,\n    json_gerelateerdePublicatiesIds: gerelateerdePublicatiesIds.join(', '),\n    \n    // FIX 9: UBL metadata\n    xml_ublVersionId: ublVersionId,\n    xml_customizationId: customizationId,\n    xml_noticeId: noticeId,\n    xml_issueDate: issueDate,\n    xml_plannedDate: plannedDate,\n    \n    // NIEUW: Gunningscriteria wegingen\n    xml_gunningsCriteria: gunningsCriteriaText,\n    xml_criteriaArray: JSON.stringify(criteriaArray),\n    xml_aantalCriteria: criteriaArray.length,\n    \n    // NIEUW: Platform detectie\n    xml_platform: platform,\n    xml_platformUrl: jsonDetail?.urlTsenderWebsite || '',\n    \n    // Perceel informatie (gestructureerd)\n    percelen_aantal: perceelInfoArray.length,\n    percelen_data: JSON.stringify(perceelInfoArray),\n    perceel1_naam: perceelInfoArray[0]?.naam || '',\n    perceel1_id: perceelInfoArray[0]?.id || '',\n    perceel1_duur: perceelInfoArray[0]?.duur || '',\n    perceel1_duurEenheid: perceelInfoArray[0]?.duurEenheid || '',\n    perceel2_naam: perceelInfoArray[1]?.naam || '',\n    perceel2_id: perceelInfoArray[1]?.id || '',\n    perceel2_duur: perceelInfoArray[1]?.duur || '',\n    perceel2_duurEenheid: perceelInfoArray[1]?.duurEenheid || '',\n    \n    // URLs\n    originalLink: originalLink,\n    tenderNedUrl: correctTenderNedUrl,\n    \n    // Timestamps\n    verwerkingTimestamp: new Date().toISOString(),\n    \n    // Originele data backup\n    originele_data_json: JSON.stringify({\n      xml_data: xmlData,\n      publicatie_data: publicatiesData,\n      json_detail: jsonDetail,\n      aggregated_raw: aggregatedData\n    })\n  }\n};"
      },
      "typeVersion": 2
    },
    {
      "id": "cc3b434f-fbae-4e6b-8e4a-3cd59220a19f",
      "name": "Response verarbeiten",
      "type": "n8n-nodes-base.code",
      "notes": "Extract publicaties uit API response",
      "position": [
        128,
        976
      ],
      "parameters": {
        "jsCode": "// Verwerk de response en extract de publicaties\nconst response = $input.item.json;\n\n// Spring Boot paginated response heeft 'content' veld\nlet publicaties = [];\nif (response.content && Array.isArray(response.content)) {\n  publicaties = response.content;\n} else if (Array.isArray(response)) {\n  publicaties = response;\n}\n\nconsole.log(`✅ ${publicaties.length} nieuwe publicaties gevonden`);\n\nif (publicaties.length === 0) {\n  return {\n    json: {\n      aantalPublicaties: 0,\n      publicaties: [],\n      bericht: 'Geen nieuwe tenders gevonden'\n    }\n  };\n}\n\n// Return met array voor Split node\nreturn {\n  json: {\n    aantalPublicaties: publicaties.length,\n    publicaties: publicaties\n  }\n};"
      },
      "notesInFlow": true,
      "typeVersion": 2
    },
    {
      "id": "aba4d103-97ef-4ead-bb1c-44a9b179deee",
      "name": "Notiz",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -208,
        -224
      ],
      "parameters": {
        "color": 6,
        "width": 1280,
        "height": 1056,
        "content": "## TenderNed Tender Scraper Workflow\n\n**What this workflow does:**\n- Automatically fetches new tenders from TenderNed API\n- Extracts both XML and JSON data for each tender\n- Filters based on your criteria (keywords, categories, etc.)\n- Stores results in Google Sheets\n\n**Prerequisites:**\n- TenderNed API credentials (HTTP Basic Auth)\n- Configure filter criteria in the Filter node\n\n**Schedule:** Runs daily at 9 AM to check for new tenders\n\n\n\n# Tenderned API Filters - Documentatie\n\n## Configuratie\n## API URL\n```\nhttps://www.tenderned.nl/papi/tenderned-rs-tns/v2/publicaties\n```\nAanvragen Tenderned API\n### https://data.overheid.nl/dataset/aankondigingen-van-overheidsopdrachten---tenderned\n\n**Documentatie:**\n### https://www.tenderned.nl/info/swagger/\n- **TenderNed CPV Info:** https://www.tenderned.nl/cms/nl/vraag/zoek-op-omschrijving-cpv-code\n\n---\n\n## Alle Beschikbare Query Parameters\n\n| Parameter               | Type                | Beschrijving                  | Voorbeelden                |\n|-------------------------|---------------------|-------------------------------|----------------------------|\n| `publicatieDatumVanaf`  | Date (YYYY-MM-DD)   | Startdatum voor publicaties   | `2024-01-01`               |\n| `publicatieDatumTot`    | Date (YYYY-MM-DD)   | Einddatum voor publicaties    | `2024-12-31`               |\n| `publicatieDatumPreset` | String              | Preset periode                | `AGP`                      |\n| `publicatieType`        | String              | Type publicatie               | `AAO`                      |\n| `cpvCodes`              | String              | CPV code ( 1 CVPV!)           | `71000000-8` of '71000000' |\n| `page`                  | Integer             | Paginanummer (start bij 0)    | `0'                        |\n| `size`                  | Integer             | Resultaten per pagina         | `50`                       |\n\n---\n### Let op! max 100 'page' per call\n---\n\n## Publicatie Types (`publicatieType`)\n\n| Code    | Beschrijving                      | Gebruik          |\n|---------|-----------------------------------|------------------|\n| **AAO** | Aankondiging van een Opdracht     | Nieuwe tenders   |\n| **AGO** | Aankondiging van Gegunde Opdracht | Gunningen        |\n| **VOP** | Vooraankondiging                  | Geplande tenders |\n| **RVO** | Rectificatie van Opdracht         | Correcties       |\n| **WNO** | Wijziging van Opdracht            | Wijzigingen      |\n\n---\n\n## Meerdere CPV Codes \n**Herhaal de parameter met `&`: dit kan alleen in de url zelf**\n```\nVoorbeeld URL: https://www.tenderned.nl/papi/tenderned-rs-tns/v2/publicaties?cpvCodes=71000000-8&cpvCodes=72000000-5&cpvCodes=48000000-8\n```\n\n"
      },
      "typeVersion": 1
    },
    {
      "id": "b42b8cfe-b5fd-4bb3-9cee-72848ced7849",
      "name": "Filtern nach ...",
      "type": "n8n-nodes-base.filter",
      "position": [
        2944,
        944
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "version": 2,
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "639f0c72-2220-4f4f-ad1f-35f14db5d89e",
              "operator": {
                "type": "boolean",
                "operation": "true",
                "singleValue": true
              },
              "leftValue": "={{ $json.cpv_codes && Array.isArray($json.cpv_codes) && $json.cpv_codes.some(code => [\n  '71000000', '71200000', '71210000', '71220000', '71221000', '71222000', '71222100', '71222200', '71223000', '71230000', '71240000', '71241000', '71242000', '71243000', '71244000', '71245000', '71246000', '71247000', '71248000', '71250000', '71251000', '71300000', '71310000', '71311000', '71311100', '71311200', '71311210', '71311220', '71311230', '71311240', '71311300', '71312000', '71313000', '71313100', '71313200', '71313400', '71313410', '71313420', '71313430', '71313440', '71313450', '71314000', '71314100', '71314200', '71314300', '71314310', '71315000', '71315100', '71315200', '71315210', '71315300', '71315400', '71315410', '71316000', '71317000', '71317100', '71317200', '71317210', '71318000', '71318100', '71319000', '71320000', '71321000', '71321100', '71321200', '71321300', '71321400', '71322000', '71322100', '71322200', '71322300', '71322400', '71322500', '71323000', '71323100', '71323200', '71324000', '71325000', '71326000', '71327000', '71328000', '71330000', '71331000', '71332000', '71333000', '71334000', '71335000', '71336000', '71337000', '71340000', '71350000', '71351000', '71351100', '71351200', '71351210', '71351220', '71351300', '71351400', '71351500', '71351600', '71351610', '71351611', '71351612', '71351700', '71351710', '71351720', '71351730', '71351800', '71351810', '71351811', '71351820', '71351900', '71351910', '71351911', '71351912', '71351913', '71351914', '71351920', '71351921', '71351922', '71351923', '71351924', '71352000', '71352100', '71352110', '71352120', '71352130', '71352140', '71352300', '71353000', '71353100', '71353200', '71354000', '71354100', '71354200', '71354300', '71354400', '71354500', '71355000', '71355100', '71355200', '71356000', '71356100', '71356200', '71356300', '71356400', '71400000', '71410000', '71420000', '71421000', '71500000', '71510000', '71520000', '71521000', '71530000', '71540000', '71541000', '71550000', '71600000', '71610000', '71620000', '71621000', '71630000', '71631000', '71631100', '71631200', '71631300', '71631400', '71631420', '71631430', '71631440', '71631450', '71631460', '71631470', '71631480', '71631490', '71632000', '71632100', '71632200', '71700000', '71730000', '71731000', '70100000', '70110000', '70120000'\n].includes(code)) }}",
              "rightValue": "={{ $json.cpv_codes.some(code => ['71000000-8', '71200000-0', '71210000-3'].includes(code)) }}"
            }
          ]
        }
      },
      "typeVersion": 2.2
    },
    {
      "id": "e4a9f68f-ecca-4dc7-acde-da3becc2e716",
      "name": "Zeile einfügen",
      "type": "n8n-nodes-base.dataTable",
      "position": [
        3456,
        944
      ],
      "parameters": {
        "columns": {
          "value": {},
          "schema": [],
          "mappingMode": "autoMapInputData",
          "matchingColumns": [],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {},
        "dataTableId": {
          "__rl": true,
          "mode": "list",
          "value": ""
        }
      },
      "typeVersion": 1
    },
    {
      "id": "1eb5c070-8fc3-479f-99f9-e9d0899ed471",
      "name": "Tenderned Publicaties",
      "type": "n8n-nodes-base.httpRequest",
      "notes": "FILTERT DIRECT IN API: alleen laatste 24 uur!",
      "position": [
        -208,
        976
      ],
      "parameters": {
        "url": "https://www.tenderned.nl/papi/tenderned-rs-tns/v2/publicaties",
        "options": {
          "timeout": 30000
        },
        "sendQuery": true,
        "authentication": "genericCredentialType",
        "genericAuthType": "httpBasicAuth",
        "queryParameters": {
          "parameters": [
            {
              "name": "publicatieDatumVanaf",
              "value": "=2025-01-01"
            },
            {
              "name": "size",
              "value": "100"
            },
            {
              "name": "publicatieType",
              "value": "VAK"
            }
          ]
        }
      },
      "notesInFlow": true,
      "typeVersion": 4.2
    },
    {
      "id": "a0a5cde9-e8ee-48ab-ac14-22ed71d0f181",
      "name": "Zeitgesteuerter Trigger",
      "type": "n8n-nodes-base.scheduleTrigger",
      "position": [
        -560,
        976
      ],
      "parameters": {
        "rule": {
          "interval": [
            {}
          ]
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "78c96a84-7b35-4e93-b6c8-f59ad8684067",
      "name": "Zeitplan-Info",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -640,
        1136
      ],
      "parameters": {
        "color": 5,
        "width": 300,
        "height": 368,
        "content": "## Schedule Trigger\n\n**Purpose:** Runs this workflow automatically\n\n**Schedule:** Daily at 9:00 AM\n\n**Note:** You can also trigger manually for testing using the manual trigger below\n\n## Manual Testing\n\n**For development and testing:**\nClick this node to run the workflow manually"
      },
      "typeVersion": 1
    },
    {
      "id": "d84a3615-f414-49d5-baee-b58b561c9c8c",
      "name": "API Fetch Info",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -320,
        1136
      ],
      "parameters": {
        "color": 5,
        "width": 344,
        "height": 340,
        "content": "## Fetch Tender Publications\n\n**API Endpoint:** TenderNed Publicaties API\n\n**What it does:**\n- Fetches latest tender publications\n- Returns array of tenders with basic info\n- Each tender has a publicatieId for detailed lookups\n\n**Authentication:** HTTP Basic Auth\n\n**Setup Required:** Configure your API credentials in this node"
      },
      "typeVersion": 1
    },
    {
      "id": "c6ce7ebc-3b35-45ab-bb99-3a9090f123cc",
      "name": "Response-Info verarbeiten",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        48,
        1136
      ],
      "parameters": {
        "color": 7,
        "width": 288,
        "height": 324,
        "content": "## Process API Response\n\n**What it does:**\n- Extracts the nested 'publicaties' array from API response\n- Prepares data structure for further processing\n\n**Note:** The API returns results wrapped in an object - this node unwraps it"
      },
      "typeVersion": 1
    },
    {
      "id": "18844cf6-b38e-4743-9af1-657168b75684",
      "name": "Info aufteilen",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        2464,
        1152
      ],
      "parameters": {
        "color": 7,
        "width": 320,
        "height": 292,
        "content": "## Split Into Individual Tenders\n\n**What it does:**\n- Takes the array of tenders\n- Creates one item per tender\n- Enables processing each tender individually\n\n**Why?** We need to fetch detailed data for each tender separately"
      },
      "typeVersion": 1
    },
    {
      "id": "9771a8c2-ee4c-4241-8d7f-6fcbd51d7db2",
      "name": "Info-Iteration",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        656,
        1136
      ],
      "parameters": {
        "color": 7,
        "width": 340,
        "height": 328,
        "content": "## Loop Over Items (Rate Limiting)\n\n**What it does:**\n- Processes tenders one at a time\n- Prevents API rate limiting\n- Ensures reliable data fetching\n\n**How it works:**\n- Batch size: 1 (processes one tender at a time)\n- Loops until all tenders are processed"
      },
      "typeVersion": 1
    },
    {
      "id": "853deb2c-0cb4-41ef-b0a4-9d50ea901023",
      "name": "JSON Details Info",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1248,
        288
      ],
      "parameters": {
        "color": 5,
        "width": 340,
        "height": 392,
        "content": "## Fetch JSON Details\n\n**API:** `/publicaties/{id}`\n\n**Returns:**\n- `kenmerk`: Reference number\n- `pbNummerTed`: TED publication number\n- `trefwoorden`: Keywords array\n- Categories and classifications\n\n**Uses:** publicatieId from the split tender"
      },
      "typeVersion": 1
    },
    {
      "id": "b0681ea1-7fb4-4c8a-89dc-762efcc26628",
      "name": "XML Details Info",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1104,
        1152
      ],
      "parameters": {
        "color": 5,
        "width": 340,
        "height": 364,
        "content": "## Fetch XML Details\n\n**API:** `/publicaties/{id}/public-xml`\n\n**Returns:**\n- Full tender XML document\n- Legal requirements\n- Detailed specifications\n- Submission procedures\n\n**Format:** Raw XML (converted to JSON in next step)\n\n**Note:** Response format set to 'text' for XML parsing"
      },
      "typeVersion": 1
    },
    {
      "id": "4efb2c89-67c7-4267-8aad-89563ccc490b",
      "name": "XML Parser Info",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1472,
        1152
      ],
      "parameters": {
        "color": 7,
        "width": 252,
        "height": 288,
        "content": "## Parse XML to JSON\n\n**What it does:**\n- Converts XML string to JSON object\n- Makes data accessible for filtering\n- Trims whitespace for cleaner data\n\n**Why?** JSON is easier to work with in n8n than raw XML"
      },
      "typeVersion": 1
    },
    {
      "id": "3336713a-b66b-44c5-8535-ec5867913802",
      "name": "Info zusammenführen",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1744,
        1152
      ],
      "parameters": {
        "color": 7,
        "width": 340,
        "height": 300,
        "content": "## Merge All Data\n\n**Combines three data sources:**\n1. JSON metadata (Input 1)\n2. Parsed XML details (Input 2)\n3. Original tender info (Input 3)\n\n**Result:** Complete tender record with all available information\n\n**Mode:** Merge by position (combines items in order)"
      },
      "typeVersion": 1
    },
    {
      "id": "fea0a1ef-83d5-4c59-90c2-f0a51c11d4b5",
      "name": "Info aggregieren",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        2112,
        1152
      ],
      "parameters": {
        "color": 7,
        "width": 320,
        "height": 260,
        "content": "## Aggregate Loop Results\n\n**What it does:**\n- Collects all processed tenders from the loop\n- Combines them into a single array\n- Stores in 'allData' field\n\n**Why?** Prepares all tenders for batch processing in the next steps"
      },
      "typeVersion": 1
    },
    {
      "id": "9e2778bb-853f-459e-be8e-6eecce467ba6",
      "name": "Info für Filter aufteilen",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        352,
        1136
      ],
      "parameters": {
        "color": 7,
        "width": 272,
        "height": 324,
        "content": "## Split for Filtering\n\n**What it does:**\n- Unpacks the aggregated array\n- Creates individual items again\n- Prepares for filtering\n\n**Note:** We aggregate first to exit the loop, then split again for filtering"
      },
      "typeVersion": 1
    },
    {
      "id": "4cf90e94-5a43-4c0e-bad0-0ec8ccc1cf63",
      "name": "Filterkonfiguration",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        2832,
        1152
      ],
      "parameters": {
        "color": 3,
        "width": 360,
        "height": 360,
        "content": "## Filter Tenders\n\n**⚠️ CONFIGURE THIS NODE**\n\n**Purpose:** Filter tenders based on your criteria\n\n**Examples:**\n- CPV-code\n- Keywords in description\n- Specific categories (e.g., IT, Construction)\n- Budget ranges\n- Deadline dates\n- Geographic regions\n\n**Current Filter:** Configure your conditions here\n\n**Tip:** Test with loose filters first, then refine"
      },
      "typeVersion": 1
    },
    {
      "id": "a3c38818-7340-4886-a5c2-44c3d422f2f7",
      "name": "Datenbankeinrichtung",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        3296,
        1152
      ],
      "parameters": {
        "color": 4,
        "width": 376,
        "height": 360,
        "content": "## Save to Data Table\n\n**What it does:**\n- Inserts filtered tenders into database\n- Each tender becomes a new row\n\n**⚠️ Setup Required:**\n1. Run workflow first for available kolomn\n2. Build a n8n Data Table \n3. Choose the Data Table\n4. Map fields to columns\n"
      },
      "typeVersion": 1
    }
  ],
  "pinData": {},
  "connections": {
    "6855d01b-c1bd-429d-a2c6-610528ec192c": {
      "main": [
        [
          {
            "node": "7c209557-d141-43d4-9fa9-ced8cc06229d",
            "type": "main",
            "index": 1
          }
        ]
      ]
    },
    "7c209557-d141-43d4-9fa9-ced8cc06229d": {
      "main": [
        [
          {
            "node": "1e619b5f-9fdb-44af-95f2-1c4c45fa5703",
            "type": "main",
            "index": 0
          },
          {
            "node": "ed07bb23-b6ed-4cff-9327-d410150d061a",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "1e619b5f-9fdb-44af-95f2-1c4c45fa5703": {
      "main": [
        [
          {
            "node": "e1816ca0-4f73-449f-a9b5-7a0b9e6cd7e3",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "bf05ee04-4ee0-4217-8b02-7fe5e8f67600": {
      "main": [
        [
          {
            "node": "ed07bb23-b6ed-4cff-9327-d410150d061a",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "b42b8cfe-b5fd-4bb3-9cee-72848ced7849": {
      "main": [
        [
          {
            "node": "e4a9f68f-ecca-4dc7-acde-da3becc2e716",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "ed07bb23-b6ed-4cff-9327-d410150d061a": {
      "main": [
        [],
        [
          {
            "node": "7369f055-b55d-491c-8dee-12988a5c2dc8",
            "type": "main",
            "index": 0
          },
          {
            "node": "f49fa844-4585-4274-8086-3c7bd8cef555",
            "type": "main",
            "index": 0
          },
          {
            "node": "7c209557-d141-43d4-9fa9-ced8cc06229d",
            "type": "main",
            "index": 2
          }
        ]
      ]
    },
    "7369f055-b55d-491c-8dee-12988a5c2dc8": {
      "main": [
        [
          {
            "node": "6855d01b-c1bd-429d-a2c6-610528ec192c",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "a0a5cde9-e8ee-48ab-ac14-22ed71d0f181": {
      "main": [
        [
          {
            "node": "1eb5c070-8fc3-479f-99f9-e9d0899ed471",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "cc3b434f-fbae-4e6b-8e4a-3cd59220a19f": {
      "main": [
        [
          {
            "node": "bf05ee04-4ee0-4217-8b02-7fe5e8f67600",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "f49fa844-4585-4274-8086-3c7bd8cef555": {
      "main": [
        [
          {
            "node": "7c209557-d141-43d4-9fa9-ced8cc06229d",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "e1816ca0-4f73-449f-a9b5-7a0b9e6cd7e3": {
      "main": [
        [
          {
            "node": "b42b8cfe-b5fd-4bb3-9cee-72848ced7849",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "1eb5c070-8fc3-479f-99f9-e9d0899ed471": {
      "main": [
        [
          {
            "node": "cc3b434f-fbae-4e6b-8e4a-3cd59220a19f",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "ecb51d0b-b6a9-4b50-879d-6f7aa7240bff": {
      "main": [
        [
          {
            "node": "1eb5c070-8fc3-479f-99f9-e9d0899ed471",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}