{ "edges": [ { "to": "2e8a77f9-b817-4214-b76d-43b6d3873fa1", "from": "05668922-02a9-44df-8bb6-11497940f3b3", "toPort": "0", "fromPort": "0" }, { "to": "567ca84c-5add-4bb6-ac0d-74d7127e6fcd", "from": "eac73b4b-7d0a-494b-b7a8-8fe94010e857", "toPort": "0", "fromPort": "0" }, { "to": "2c468a45-cafe-45aa-be73-c0c02ae5d0d0", "from": "6d1243f3-03d5-464f-9e9e-bf171931543f", "toPort": "0", "fromPort": "0" }, { "to": "38fb4a85-3895-444c-98b0-a4e8da125cc7", "from": "ca45596b-4743-42df-82f2-1089d7874284", "toPort": "0", "fromPort": "0" }, { "to": "6d1243f3-03d5-464f-9e9e-bf171931543f", "from": "05684b07-0d73-49b3-b67b-dfbaf29cafd6", "toPort": "0", "fromPort": "0" }, { "to": "26465ebd-c8ca-4422-858c-de253d000fca", "from": "98f88700-fc2c-4d4d-a427-0d535510ca30", "toPort": "0", "fromPort": "0" }, { "to": "05668922-02a9-44df-8bb6-11497940f3b3", "from": "85288925-d942-415c-ad0c-bccbd40a7638", "toPort": "0", "fromPort": "0" }, { "to": "ca45596b-4743-42df-82f2-1089d7874284", "from": "567ca84c-5add-4bb6-ac0d-74d7127e6fcd", "toPort": "0", "fromPort": "0" }, { "to": "69b46d09-691a-463f-a1cf-d387cd74e74a", "from": "26465ebd-c8ca-4422-858c-de253d000fca", "toPort": "0", "fromPort": "0" }, { "to": "eac73b4b-7d0a-494b-b7a8-8fe94010e857", "from": "2c468a45-cafe-45aa-be73-c0c02ae5d0d0", "toPort": "0", "fromPort": "0" }, { "to": "98f88700-fc2c-4d4d-a427-0d535510ca30", "from": "38fb4a85-3895-444c-98b0-a4e8da125cc7", "toPort": "0", "fromPort": "0" }, { "to": "05684b07-0d73-49b3-b67b-dfbaf29cafd6", "from": "2e8a77f9-b817-4214-b76d-43b6d3873fa1", "toPort": "0", "fromPort": "0" } ], "nodes": [ { "id": "6d1243f3-03d5-464f-9e9e-bf171931543f", "name": "Edit Fields", "type": "set", "params": { "options": {}, "assignments": { "assignments": [ { "id": "485712c6-e749-4452-b12c-4eddb625f592", "name": "Link", "type": "string", "value": "=https://secure.telr.com/tools/api/xml/report/{{ $json.id }}/file2" } ] } }, "position": [ -160, -240 ] }, { "id": "eac73b4b-7d0a-494b-b7a8-8fe94010e857", "name": "Compression", "type": "integration", "params": { "service": "compression", "operation": "run", "__originalType": "n8n-nodes-base.compression" }, "position": [ 256, -240 ] }, { "id": "567ca84c-5add-4bb6-ac0d-74d7127e6fcd", "name": "Extract from File", "type": "passthrough", "params": { "mode": "noOp", "options": { "sheetName": "Authorised" }, "operation": "xls", "binaryPropertyName": "file_0" }, "position": [ 464, -240 ] }, { "id": "38fb4a85-3895-444c-98b0-a4e8da125cc7", "name": "Code in JavaScript3", "type": "code", "params": { "jsCode": "return [\n {\n json: {\n transactionsJson: JSON.stringify($json.transactions)\n }\n }\n];" }, "position": [ -752, -16 ] }, { "id": "85288925-d942-415c-ad0c-bccbd40a7638", "name": "Schedule Trigger", "type": "trigger", "params": { "kind": "n8n-nodes-base.scheduleTrigger", "rule": { "interval": [ { "triggerAtHour": 9 } ] } }, "position": [ -992, -240 ] }, { "id": "69b46d09-691a-463f-a1cf-d387cd74e74a", "name": "Send email", "type": "integration", "params": { "html": "=\n\n\n \n\n\n\n \n\n \n \n \n\n \n \n \n\n \n \n \n\n

\n 📊 Daily Telr Summary\n

\n Date: {{ $now.toFormat(\"yyyy-MM-dd\") }}\n

\n \n

\n Total Transactions Today\n

\n\n

\n {{\n $('Extract from File').all().length\n }}\n

\n\n

\n Automated report generated by n8n\n

\n\n\n\n", "options": { "appendAttribution": false }, "service": "smtp", "subject": "Daily Auto Insert Telr Data", "toEmail": "user@example.com", "fromEmail": "user@example.com", "operation": "read" }, "position": [ -64, -32 ] }, { "id": "05668922-02a9-44df-8bb6-11497940f3b3", "name": "TELR API", "type": "http", "params": { "url": "https://secure.telr.com/tools/api/xml/report", "options": { "response": { "response": {} } }, "authentication": "genericCredentialType", "genericAuthType": "httpBasicAuth" }, "position": [ -784, -240 ] }, { "id": "2e8a77f9-b817-4214-b76d-43b6d3873fa1", "name": "Convert XML to JSON", "type": "integration", "params": { "options": {}, "service": "xml", "operation": "run", "__originalType": "n8n-nodes-base.xml", "dataPropertyName": "=data" }, "position": [ -576, -240 ] }, { "id": "05684b07-0d73-49b3-b67b-dfbaf29cafd6", "name": "Logic for date", "type": "code", "params": { "jsCode": "// Get all reports\nconst reports = items[0].json.reports.report;\n\n// Calculate yesterday date in UTC\nconst yesterday = new Date();\nyesterday.setUTCDate(yesterday.getUTCDate() - 1);\n\n// Format YYYYMMDD\nconst yyyy = yesterday.getUTCFullYear();\nconst mm = String(yesterday.getUTCMonth() + 1).padStart(2, '0');\nconst dd = String(yesterday.getUTCDate()).padStart(2, '0');\nconst ymd = `${yyyy}${mm}${dd}`;\n\n// Filter yesterday's Daily transaction report\nconst result = reports.filter(r =>\n r.description === 'Daily transaction report'&&\n r.file2 &&\n r.file2.includes(ymd)\n);\n\nreturn result.map(r => ({ json: r }));" }, "position": [ -368, -240 ] }, { "id": "2c468a45-cafe-45aa-be73-c0c02ae5d0d0", "name": "TELR API WITH DATE", "type": "http", "params": { "url": "={{ $json.Link }}", "options": {}, "authentication": "genericCredentialType", "genericAuthType": "httpBasicAuth" }, "position": [ 48, -240 ] }, { "id": "ca45596b-4743-42df-82f2-1089d7874284", "name": "Data Formation", "type": "code", "params": { "jsCode": "return [\n {\n json: {\n transactions: items.map(i => ({\n column1: i.json['column1'],\n column2: i.json['column2'],\n column3: i.json['column3'],\n column4: i.json['column4']\n }))\n }\n }\n];\n" }, "position": [ -960, -16 ] }, { "id": "98f88700-fc2c-4d4d-a427-0d535510ca30", "name": "Data Insert SQL", "type": "integration", "params": { "query": "DECLARE @json NVARCHAR(MAX);\nSET @json = N'{{$json[\"transactionsJson\"]}}';\n\n-- IMPORTANT: terminate statement before OPENJSON\n;\n\nINSERT INTO authorised_transactions (\n column1, column2, column3, column4 \n)\nSELECT\n column1,\n column2,\n column3,\n column4,\nFROM OPENJSON(@json)\nWITH (\n column1 NVARCHAR(50),\n column2 NVARCHAR(50),\n column3 NVARCHAR(MAX),\n column4 NVARCHAR(20)\n) t\nWHERE NOT EXISTS (\n SELECT 1\n FROM authorised_transactions_dates d\n WHERE d.transaction_date = CONVERT(date, t.transaction_date, 103)\n);\n\nINSERT INTO authorised_transactions_dates (transaction_date)\nSELECT DISTINCT CONVERT(date, JSON_VALUE(value, '$.transaction_date'), 103)\nFROM OPENJSON(@json)\nWHERE NOT EXISTS (\n SELECT 1 \n FROM authorised_transactions_dates d \n WHERE d.transaction_date = CONVERT(date, JSON_VALUE(value, '$.transaction_date'), 103)\n);\n\nSELECT 'SUCCESS' AS result;\n\n", "service": "microsoftsql", "operation": "executeQuery", "__originalType": "n8n-nodes-base.microsoftSql" }, "position": [ -528, -16 ] }, { "id": "26465ebd-c8ca-4422-858c-de253d000fca", "name": "Success Condition", "type": "if", "params": { "options": { "ignoreCase": "={{ false }}" }, "conditions": { "options": { "version": 3, "leftValue": "", "caseSensitive": false, "typeValidation": "strict" }, "combinator": "and", "conditions": [ { "id": "c46d4a8a-9407-4150-bdb4-11ba9d42ad1e", "operator": { "type": "string", "operation": "contains" }, "leftValue": "={{ $json.result }}", "rightValue": "SUCCESS" } ] } }, "position": [ -320, -16 ] } ] }

Fetch Telr payment reports to SQL and send daily email summaries