Employee Attendance Tracker with Daily Summary

{
  "id": "QaExWEuXV0wzelZO",
  "meta": {
    "instanceId": "dd69efaf8212c74ad206700d104739d3329588a6f3f8381a46a481f34c9cc281",
    "templateCredsSetupCompleted": true
  },
  "name": "Employee Attendance Tracker with Daily Summary",
  "tags": [],
  "nodes": [
    {
      "id": "0e4bdcc5-f1e5-4bcd-8ebb-9345a564c334",
      "name": "Schedule Trigger",
      "type": "n8n-nodes-base.scheduleTrigger",
      "position": [
        -1440,
        0
      ],
      "parameters": {
        "rule": {
          "interval": [
            {
              "field": "hours"
            }
          ]
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "5f2a24aa-5e38-4296-8071-0c7630cc61fc",
      "name": "Fetch Attendance Records",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        -1216,
        -96
      ],
      "parameters": {
        "options": {},
        "sheetName": {
          "__rl": true,
          "mode": "name",
          "value": "AttendanceLogs"
        },
        "documentId": {
          "__rl": true,
          "mode": "id",
          "value": "YOUR_ATTENDANCE_SPREADSHEET_ID"
        },
        "authentication": "serviceAccount"
      },
      "credentials": {
        "googleApi": {
          "id": "ScSS2KxGQULuPtdy",
          "name": "Google Sheets- test"
        }
      },
      "typeVersion": 4.2
    },
    {
      "id": "0d136a8a-09d5-41bc-a74f-fe6e14a5686a",
      "name": "Fetch Employee Master Data",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        -1216,
        96
      ],
      "parameters": {
        "options": {},
        "sheetName": {
          "__rl": true,
          "mode": "name",
          "value": "Employees"
        },
        "documentId": {
          "__rl": true,
          "mode": "id",
          "value": "YOUR_EMPLOYEE_SPREADSHEET_ID"
        },
        "authentication": "serviceAccount"
      },
      "credentials": {
        "googleApi": {
          "id": "ScSS2KxGQULuPtdy",
          "name": "Google Sheets- test"
        }
      },
      "typeVersion": 4.2
    },
    {
      "id": "7a72aa69-8c3d-472c-86d6-b04b4dfa0913",
      "name": "Analytics Engine",
      "type": "n8n-nodes-base.code",
      "position": [
        -992,
        0
      ],
      "parameters": {
        "jsCode": "const attendanceData = $('Fetch Attendance Records').all();\nconst employeeData = $('Fetch Employee Master Data').all();\nconst now = new Date();\nconst today = now.toISOString().split('T')[0];\nconst currentHour = now.getHours();\n\nconst employeeMap = {};\nemployeeData.forEach(emp => {\n  employeeMap[emp.json.EmployeeID] = {\n    name: emp.json.EmployeeName,\n    department: emp.json.Department,\n    manager: emp.json.Manager,\n    shift: emp.json.Shift || 'Day',\n    email: emp.json.Email\n  };\n});\n\nconst todayRecords = attendanceData.filter(item => item.json.Date === today);\n\nconst statusCount = {\n  present: 0,\n  absent: 0,\n  late: 0,\n  leave: 0,\n  wfh: 0\n};\n\nconst statusLists = {\n  late: [],\n  absent: [],\n  wfh: []\n};\n\nconst deptMetrics = {};\n\ntodayRecords.forEach(record => {\n  const empId = record.json.EmployeeID;\n  const status = record.json.Status;\n  const checkIn = record.json.CheckInTime;\n  const employee = employeeMap[empId];\n  \n  if (!employee) return;\n  \n  const dept = employee.department;\n  if (!deptMetrics[dept]) {\n    deptMetrics[dept] = { present: 0, absent: 0, late: 0, total: 0 };\n  }\n  \n  deptMetrics[dept].total++;\n  \n  switch(status) {\n    case 'Present':\n      statusCount.present++;\n      deptMetrics[dept].present++;\n      break;\n    case 'Absent':\n      statusCount.absent++;\n      deptMetrics[dept].absent++;\n      statusLists.absent.push({ name: employee.name, department: dept, manager: employee.manager });\n      break;\n    case 'Late':\n      statusCount.late++;\n      deptMetrics[dept].late++;\n      statusLists.late.push({ name: employee.name, department: dept, checkIn: checkIn || 'N/A', lateBy: '15 min' });\n      break;\n    case 'Leave':\n      statusCount.leave++;\n      break;\n    case 'WFH':\n      statusCount.wfh++;\n      statusLists.wfh.push({ name: employee.name, department: dept });\n      break;\n  }\n});\n\nconst totalEmployees = Object.keys(employeeMap).length;\nconst activeEmployees = statusCount.present + statusCount.absent + statusCount.late;\nconst attendanceRate = activeEmployees > 0 ? ((statusCount.present + statusCount.late) / activeEmployees * 100).toFixed(2) : 0;\nconst punctualityRate = (statusCount.present + statusCount.late) > 0 ? (statusCount.present / (statusCount.present + statusCount.late) * 100).toFixed(2) : 0;\n\nconst alerts = [];\nif (statusCount.late > totalEmployees * 0.1) {\n  alerts.push({ type: 'warning', severity: 'medium', message: `High tardiness: ${statusCount.late} employees late`, action: 'Review shift timings' });\n}\n\nif (statusCount.absent > totalEmployees * 0.15) {\n  alerts.push({ type: 'alert', severity: 'high', message: `High absence rate: ${statusCount.absent} employees absent`, action: 'Investigate issues' });\n}\n\nreturn {\n  date: today,\n  timestamp: now.toISOString(),\n  hour: currentHour,\n  totalEmployees: totalEmployees,\n  recordsProcessed: todayRecords.length,\n  present: statusCount.present,\n  absent: statusCount.absent,\n  late: statusCount.late,\n  onLeave: statusCount.leave,\n  wfh: statusCount.wfh,\n  attendanceRate: parseFloat(attendanceRate),\n  punctualityRate: parseFloat(punctualityRate),\n  absenteeismRate: parseFloat(((statusCount.absent / totalEmployees) * 100).toFixed(2)),\n  lateEmployees: statusLists.late,\n  absentEmployees: statusLists.absent,\n  wfhEmployees: statusLists.wfh,\n  departmentBreakdown: deptMetrics,\n  alerts: alerts,\n  alertCount: alerts.length,\n  hasHighPriorityAlerts: alerts.some(a => a.severity === 'high'),\n  shouldNotifyManagement: alerts.length > 0 || statusCount.absent > 5\n};"
      },
      "typeVersion": 2
    },
    {
      "id": "7651642d-6966-49bd-b503-7b57369026d2",
      "name": "Records Available",
      "type": "n8n-nodes-base.if",
      "position": [
        -768,
        0
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "number": [
            {
              "value1": "={{ $json.recordsProcessed }}",
              "value2": 0,
              "operation": "larger"
            }
          ]
        }
      },
      "typeVersion": 2
    },
    {
      "id": "bc5e3d2a-5946-4223-af9a-696f54b79828",
      "name": "Critical Alerts",
      "type": "n8n-nodes-base.if",
      "position": [
        -544,
        -192
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "boolean": [
            {
              "value1": "={{ $json.shouldNotifyManagement }}",
              "value2": true
            }
          ]
        }
      },
      "typeVersion": 2
    },
    {
      "id": "36732aa7-c12f-4af0-acfe-e94b4f64b5ca",
      "name": "Format Email",
      "type": "n8n-nodes-base.code",
      "position": [
        -320,
        -192
      ],
      "parameters": {
        "jsCode": "const data = $input.first().json;\n\nconst alertsHtml = data.alerts.length > 0 ? `\n  <div style=\"background: #fff3cd; border-left: 4px solid #ffc107; padding: 15px; margin: 20px 0;\">\n    <h3 style=\"color: #856404; margin-top: 0;\">⚠️ Alerts</h3>\n    ${data.alerts.map(alert => `<div style=\"margin: 10px 0;\"><strong>${alert.severity.toUpperCase()}</strong>: ${alert.message}</div>`).join('')}\n  </div>\n` : '';\n\nconst emailHtml = `\n<html>\n<body style=\"font-family: Arial, sans-serif; padding: 20px;\">\n  <div style=\"background: linear-gradient(135deg, #667eea 0%, #764ba2 100%); color: white; padding: 30px; text-align: center;\">\n    <h1>📊 Daily Attendance Report</h1>\n    <p>${data.date} • ${data.hour}:00</p>\n  </div>\n  <div style=\"padding: 20px;\">\n    ${alertsHtml}\n    <div style=\"display: grid; grid-template-columns: repeat(4, 1fr); gap: 15px; margin: 20px 0;\">\n      <div style=\"background: #e8f5e9; padding: 20px; text-align: center;\">\n        <div style=\"font-size: 32px; font-weight: bold; color: #2e7d32;\">${data.present}</div>\n        <div>✅ Present</div>\n      </div>\n      <div style=\"background: #fff3e0; padding: 20px; text-align: center;\">\n        <div style=\"font-size: 32px; font-weight: bold; color: #e65100;\">${data.late}</div>\n        <div>⏰ Late</div>\n      </div>\n      <div style=\"background: #ffebee; padding: 20px; text-align: center;\">\n        <div style=\"font-size: 32px; font-weight: bold; color: #c62828;\">${data.absent}</div>\n        <div>❌ Absent</div>\n      </div>\n      <div style=\"background: #e3f2fd; padding: 20px; text-align: center;\">\n        <div style=\"font-size: 32px; font-weight: bold; color: #1565c0;\">${data.onLeave}</div>\n        <div>🏖️ Leave</div>\n      </div>\n    </div>\n    <h3>📈 Key Metrics</h3>\n    <p><strong>Attendance Rate:</strong> ${data.attendanceRate}%</p>\n    <p><strong>Punctuality Rate:</strong> ${data.punctualityRate}%</p>\n    <p><strong>Total Employees:</strong> ${data.totalEmployees}</p>\n  </div>\n</body>\n</html>\n`;\n\nreturn {\n  ...data,\n  emailHtml: emailHtml,\n  emailSubject: `${data.hasHighPriorityAlerts ? '🚨 URGENT: ' : '📊'} Attendance Report - ${data.date}`\n};"
      },
      "typeVersion": 2
    },
    {
      "id": "c6b97279-622c-479a-94b9-7591ac89b885",
      "name": "Send Email",
      "type": "n8n-nodes-base.emailSend",
      "position": [
        -96,
        -192
      ],
      "webhookId": "a887bf69-69cb-4199-813b-eea241a81965",
      "parameters": {
        "options": {},
        "subject": "={{ $json.emailSubject }}",
        "toEmail": "[email protected]",
        "fromEmail": "[email protected]"
      },
      "credentials": {
        "smtp": {
          "id": "G1kyF8cSWTZ4vouN",
          "name": "SMTP -test"
        }
      },
      "typeVersion": 2.1
    },
    {
      "id": "674e5e0a-3f26-4980-be64-aeaf1cdc4b48",
      "name": "Format Slack",
      "type": "n8n-nodes-base.code",
      "position": [
        -544,
        0
      ],
      "parameters": {
        "jsCode": "const data = $input.first().json;\n\nconst lateList = data.lateEmployees.length > 0 ? data.lateEmployees.slice(0, 5).map(e => `• ${e.name} (${e.department})`).join('\\n') : 'None';\nconst absentList = data.absentEmployees.length > 0 ? data.absentEmployees.slice(0, 5).map(e => `• ${e.name} (${e.department})`).join('\\n') : 'None';\n\nconst blocks = [\n  {\n    type: \"header\",\n    text: { type: \"plain_text\", text: `📊 Attendance Report - ${data.date}`, emoji: true }\n  },\n  {\n    type: \"section\",\n    fields: [\n      { type: \"mrkdwn\", text: `*✅ Present*\\n${data.present}` },\n      { type: \"mrkdwn\", text: `*⏰ Late*\\n${data.late}` },\n      { type: \"mrkdwn\", text: `*❌ Absent*\\n${data.absent}` },\n      { type: \"mrkdwn\", text: `*🏖️ Leave*\\n${data.onLeave}` }\n    ]\n  },\n  { type: \"divider\" },\n  {\n    type: \"section\",\n    text: { type: \"mrkdwn\", text: `*⏰ Late Arrivals*\\n${lateList}` }\n  },\n  {\n    type: \"section\",\n    text: { type: \"mrkdwn\", text: `*❌ Absences*\\n${absentList}` }\n  }\n];\n\nreturn { ...data, slackBlocks: blocks };"
      },
      "typeVersion": 2
    },
    {
      "id": "39c44862-38ad-4c32-aa12-c55f1a3d1de6",
      "name": "Post to Slack",
      "type": "n8n-nodes-base.slack",
      "position": [
        -320,
        0
      ],
      "webhookId": "6549ed3c-d1f9-4ec9-b74c-d7469709511e",
      "parameters": {
        "text": "=Daily Attendance Report",
        "select": "channel",
        "channelId": {
          "__rl": true,
          "mode": "id",
          "value": "C12345678"
        },
        "otherOptions": {}
      },
      "credentials": {
        "slackApi": {
          "id": "MQ0fgwuS8AzfwFvy",
          "name": "Slack account - test "
        }
      },
      "typeVersion": 2.2
    },
    {
      "id": "69043dab-0525-462b-b2de-12ce885def34",
      "name": "Log Summary",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        -544,
        192
      ],
      "parameters": {
        "columns": {
          "value": {},
          "schema": [],
          "mappingMode": "autoMapInputData",
          "matchingColumns": [],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {},
        "operation": "append",
        "sheetName": {
          "__rl": true,
          "mode": "name",
          "value": "DailySummary"
        },
        "documentId": {
          "__rl": true,
          "mode": "id",
          "value": "YOUR_SUMMARY_SPREADSHEET_ID"
        },
        "authentication": "serviceAccount"
      },
      "credentials": {
        "googleApi": {
          "id": "ScSS2KxGQULuPtdy",
          "name": "Google Sheets- test"
        }
      },
      "typeVersion": 4.2
    },
    {
      "id": "b2f9027b-2c48-4e26-b175-081ee2fc73ea",
      "name": "Sticky Note1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1472,
        -160
      ],
      "parameters": {
        "color": 3,
        "width": 150,
        "height": 288,
        "content": "Runs hourly to monitor attendance with zero manual effort."
      },
      "typeVersion": 1
    },
    {
      "id": "1cda22e2-28de-40fa-b380-80264166deb3",
      "name": "Sticky Note2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1280,
        -224
      ],
      "parameters": {
        "color": 3,
        "width": 416,
        "height": 464,
        "content": "Fetches attendance and employee master data, then merges them for enriched analytics and analyzes attendance, calculates key metrics, detects anomalies, and generates alerts."
      },
      "typeVersion": 1
    },
    {
      "id": "e2fbe0c6-3e13-4509-a6cc-f941376979ae",
      "name": "Sticky Note3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -816,
        -304
      ],
      "parameters": {
        "color": 3,
        "width": 448,
        "height": 656,
        "content": "Validates data, prioritizes alerts, and routes notifications via email, Slack, and database and logs daily summaries, maintains audit trails, and prepares data for trend analysis dashboards."
      },
      "typeVersion": 1
    },
    {
      "id": "1dbde325-390a-4164-9e64-84f67fc6c1c8",
      "name": "Sticky Note",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -352,
        -320
      ],
      "parameters": {
        "color": 3,
        "width": 416,
        "height": 464,
        "content": "Sends visually formatted reports with metrics, alerts, and detailed employee breakdowns."
      },
      "typeVersion": 1
    }
  ],
  "active": false,
  "pinData": {},
  "settings": {
    "executionOrder": "v1"
  },
  "versionId": "ea71b953-3354-4852-af6f-5708906566fb",
  "connections": {
    "Format Email": {
      "main": [
        [
          {
            "node": "Send Email",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Format Slack": {
      "main": [
        [
          {
            "node": "Post to Slack",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Critical Alerts": {
      "main": [
        [
          {
            "node": "Format Email",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Analytics Engine": {
      "main": [
        [
          {
            "node": "Records Available",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Schedule Trigger": {
      "main": [
        [
          {
            "node": "Fetch Attendance Records",
            "type": "main",
            "index": 0
          },
          {
            "node": "Fetch Employee Master Data",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Records Available": {
      "main": [
        [
          {
            "node": "Critical Alerts",
            "type": "main",
            "index": 0
          },
          {
            "node": "Format Slack",
            "type": "main",
            "index": 0
          },
          {
            "node": "Log Summary",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Fetch Attendance Records": {
      "main": [
        [
          {
            "node": "Analytics Engine",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Fetch Employee Master Data": {
      "main": [
        [
          {
            "node": "Analytics Engine",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}