[{"_1":2,"_172":-5,"_173":-5},"loaderData",{"_3":4,"_91":92},"root",{"_5":6,"_34":35,"_64":65},"ctx",{"_7":8,"_23":-5,"_24":25,"_26":27,"_28":20,"_29":20,"_30":20,"_31":32,"_33":20},"auth",{"_9":-5,"_10":-5,"_11":-5,"_12":-5,"_13":-5,"_14":-5,"_15":-5,"_16":-5,"_17":-5,"_18":-5,"_19":20,"_21":22},"sessionClaims","sessionId","sessionStatus","userId","actor","orgId","orgRole","orgSlug","orgPermissions","factorVerificationAge","isAuthenticated",false,"tokenType","session_token","user","preferencesKeybindings","default","preferencesTheme","light","preferencesFixers","preferencesCopilot","preferencesSelectionAi","notificationCount",0,"flagDenols","ENV",{"_36":37,"_38":39,"_40":41,"_42":43,"_44":45,"_46":47,"_48":49,"_50":51,"_52":53,"_54":55,"_56":57,"_58":59,"_60":61,"_62":63},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","VAL_RUNTIME_NAME","prod","RENDER_GIT_COMMIT","8e9ab478faef4805babde6d59c528a1818a04d96","RENDER_GIT_BRANCH","main","CLERK_PUBLISHABLE_KEY","pk_live_Y2xlcmsudmFsLnRvd24k","POSTHOG_API_TOKEN","phc_nmoyuAkStEZuUp69rYlg1DZgrIt19Gx0Y4Hwf8tGgJP","POSTHOG_API_HOST","https://wart.val.town","WEB_API_TEMPLATE","https://{val}.web.val.run","APP_URL","https://www.val.town","MODULE_URL","https://esm.town","VANITY_WEB_API_TEMPLATE","https://{label}.val.run/","DENOLS_URL","https://lsp.val.town","NODE_ENV","production","API_URL","https://api.val.town","clerkState",{"_66":67},"__internal_clerk_state",{"_68":69,"_70":45,"_71":72,"_73":72,"_74":20,"_75":72,"_76":72,"_77":72,"_78":72,"_79":80,"_89":20,"_90":20},"__clerk_ssr_state",{"_21":22,"_9":-5,"_10":-5,"_11":-5,"_12":-5,"_13":-5,"_14":-5,"_15":-5,"_16":-5,"_17":-5,"_18":-5,"_19":20},"__publishableKey","__proxyUrl","","__domain","__isSatellite","__signInUrl","__signUpUrl","__afterSignInUrl","__afterSignUpUrl","__clerk_debug",{"_81":20,"_19":20,"_82":72,"_83":84,"_85":72,"_86":45,"_87":20,"_88":72},"isSignedIn","proxyUrl","reason","session-token-and-uat-missing","message","publishableKey","isSatellite","domain","__telemetryDisabled","__telemetryDebug","routes/embed.x.$handle.$name.$",{"_93":94,"_95":96,"_97":98,"_99":100,"_146":147,"_169":170,"_171":-5},"handle","DHIRAJ7655","name","Attendancetracker","path","backend/database/queries.ts","project",{"_101":102,"_95":96,"_103":104,"_105":-5,"_106":20,"_107":108,"_109":-5,"_110":-5,"_12":111,"_112":-5,"_113":20,"_114":-5,"_115":32,"_14":116,"_117":118,"_119":118,"_120":121,"_122":121,"_123":121,"_124":118,"_125":118,"_23":126,"_129":130,"_132":133,"_145":20},"id","7840f768-3f65-11f0-8979-76b3cceeab13","createdAt",["D",1748836549023],"deletedAt","legacyEnvVars","privacy","public","description","imageUrl","31cbc510-3f65-11f0-b6e9-ab0b2f88b858","pinnedFileId","pinPreview","screenshotVersion","likeCount","8d2433cd-5ff9-48d4-a558-132c486bb8f8","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_93":94,"_127":128},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoiZGVmYXVsdCIsImlpZCI6Imluc18yTm10cGhQUklwU0tqZHNiOHNnNGd2ZjJURFUiLCJyaWQiOiJ1c2VyXzJ4dzVadVNocFFOMWdNOERhblZUbm44TU5saSJ9","org",{"_93":94,"_131":128,"_12":111},"avatar","branch",{"_134":135,"_95":43,"_101":136,"_137":102,"_138":-5,"_139":-5,"_140":-5,"_141":-5,"_142":-5,"_143":96,"_144":94},"version",7,"78416e32-3f65-11f0-8979-76b3cceeab13","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","isOwned","file",{"_101":148,"_95":149,"_150":151,"_152":153,"_154":136,"_155":156,"_157":158,"_103":159,"_134":32,"_160":161,"_162":163,"_164":20,"_165":-5,"_97":166},"cc4a4e72-3f65-11f0-bdb3-76b3cceeab13","queries.ts","fileId","cc4a4f62-3f65-11f0-bdb3-76b3cceeab13","parentId","68470509-1318-47fd-8b98-355621456109","branchId","branchVersion",4,"latest",true,["D",1748836690008],"fileType","script","content","import { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\nimport { TABLES } from \"./migrations.ts\";\nimport type { \n Employee, \n AttendanceRecord, \n CreateEmployeeRequest, \n UpdateEmployeeRequest,\n AttendanceStatus,\n DailyReport \n} from \"../../shared/types.ts\";\n\n// Employee queries\nexport class EmployeeQueries {\n static async getAll(): Promise {\n const rows = await sqlite.execute(`\n SELECT * FROM ${TABLES.EMPLOYEES} \n WHERE is_active = 1 \n ORDER BY first_name, last_name\n `);\n \n return rows.map(this.mapRowToEmployee);\n }\n\n static async getById(id: number): Promise {\n const rows = await sqlite.execute(`\n SELECT * FROM ${TABLES.EMPLOYEES} WHERE id = ?\n `, [id]);\n \n return rows.length > 0 ? this.mapRowToEmployee(rows[0]) : null;\n }\n\n static async getByEmployeeId(employeeId: string): Promise {\n const rows = await sqlite.execute(`\n SELECT * FROM ${TABLES.EMPLOYEES} WHERE employee_id = ?\n `, [employeeId]);\n \n return rows.length > 0 ? this.mapRowToEmployee(rows[0]) : null;\n }\n\n static async create(data: CreateEmployeeRequest): Promise {\n const result = await sqlite.execute(`\n INSERT INTO ${TABLES.EMPLOYEES} \n (employee_id, first_name, last_name, email, department, position, hire_date)\n VALUES (?, ?, ?, ?, ?, ?, ?)\n `, [\n data.employeeId,\n data.firstName,\n data.lastName,\n data.email,\n data.department,\n data.position,\n data.hireDate\n ]);\n\n const employee = await this.getById(result.lastInsertRowId);\n if (!employee) throw new Error('Failed to create employee');\n return employee;\n }\n\n static async update(id: number, data: UpdateEmployeeRequest): Promise {\n const updates: string[] = [];\n const values: any[] = [];\n\n if (data.employeeId !== undefined) {\n updates.push('employee_id = ?');\n values.push(data.employeeId);\n }\n if (data.firstName !== undefined) {\n updates.push('first_name = ?');\n values.push(data.firstName);\n }\n if (data.lastName !== undefined) {\n updates.push('last_name = ?');\n values.push(data.lastName);\n }\n if (data.email !== undefined) {\n updates.push('email = ?');\n values.push(data.email);\n }\n if (data.department !== undefined) {\n updates.push('department = ?');\n values.push(data.department);\n }\n if (data.position !== undefined) {\n updates.push('position = ?');\n values.push(data.position);\n }\n if (data.hireDate !== undefined) {\n updates.push('hire_date = ?');\n values.push(data.hireDate);\n }\n if (data.isActive !== undefined) {\n updates.push('is_active = ?');\n values.push(data.isActive ? 1 : 0);\n }\n\n updates.push('updated_at = CURRENT_TIMESTAMP');\n values.push(id);\n\n await sqlite.execute(`\n UPDATE ${TABLES.EMPLOYEES} \n SET ${updates.join(', ')} \n WHERE id = ?\n `, values);\n\n const employee = await this.getById(id);\n if (!employee) throw new Error('Employee not found after update');\n return employee;\n }\n\n static async delete(id: number): Promise {\n await sqlite.execute(`\n UPDATE ${TABLES.EMPLOYEES} \n SET is_active = 0, updated_at = CURRENT_TIMESTAMP \n WHERE id = ?\n `, [id]);\n }\n\n private static mapRowToEmployee(row: any): Employee {\n return {\n id: row.id,\n employeeId: row.employee_id,\n firstName: row.first_name,\n lastName: row.last_name,\n email: row.email,\n department: row.department,\n position: row.position,\n hireDate: row.hire_date,\n isActive: Boolean(row.is_active),\n createdAt: row.created_at,\n updatedAt: row.updated_at\n };\n }\n}\n\n// Attendance queries\nexport class AttendanceQueries {\n static async clockIn(employeeId: number, notes?: string): Promise {\n const now = new Date().toISOString();\n const date = now.split('T')[0];\n\n // Check if already clocked in today\n const existing = await this.getTodayRecord(employeeId);\n if (existing && !existing.clockOut) {\n throw new Error('Employee is already clocked in');\n }\n\n const result = await sqlite.execute(`\n INSERT INTO ${TABLES.ATTENDANCE} \n (employee_id, clock_in, date, status, notes)\n VALUES (?, ?, ?, 'clocked-in', ?)\n `, [employeeId, now, date, notes || null]);\n\n const record = await this.getById(result.lastInsertRowId);\n if (!record) throw new Error('Failed to create attendance record');\n return record;\n }\n\n static async clockOut(employeeId: number, notes?: string): Promise {\n const record = await this.getTodayRecord(employeeId);\n if (!record || record.clockOut) {\n throw new Error('No active clock-in found for today');\n }\n\n const now = new Date().toISOString();\n const clockInTime = new Date(record.clockIn);\n const clockOutTime = new Date(now);\n const totalHours = (clockOutTime.getTime() - clockInTime.getTime()) / (1000 * 60 * 60);\n\n await sqlite.execute(`\n UPDATE ${TABLES.ATTENDANCE} \n SET clock_out = ?, total_hours = ?, status = 'present', notes = ?, updated_at = CURRENT_TIMESTAMP\n WHERE id = ?\n `, [now, totalHours, notes || record.notes, record.id]);\n\n const updatedRecord = await this.getById(record.id);\n if (!updatedRecord) throw new Error('Failed to update attendance record');\n return updatedRecord;\n }\n\n static async getStatus(employeeId: number): Promise {\n const record = await this.getTodayRecord(employeeId);\n \n return {\n employeeId,\n isClocked: Boolean(record && !record.clockOut),\n clockInTime: record?.clockIn,\n currentSession: record && !record.clockOut ? record : undefined\n };\n }\n\n static async getHistory(employeeId: number, limit = 30): Promise {\n const rows = await sqlite.execute(`\n SELECT * FROM ${TABLES.ATTENDANCE} \n WHERE employee_id = ? \n ORDER BY date DESC, clock_in DESC \n LIMIT ?\n `, [employeeId, limit]);\n\n return rows.map(this.mapRowToAttendance);\n }\n\n static async getDailyReport(date: string): Promise {\n // Get all active employees\n const employees = await EmployeeQueries.getAll();\n \n // Get attendance records for the date\n const attendanceRows = await sqlite.execute(`\n SELECT a.*, e.first_name, e.last_name, e.employee_id as emp_id\n FROM ${TABLES.ATTENDANCE} a\n JOIN ${TABLES.EMPLOYEES} e ON a.employee_id = e.id\n WHERE a.date = ? AND e.is_active = 1\n `, [date]);\n\n const attendanceMap = new Map();\n attendanceRows.forEach(row => {\n attendanceMap.set(row.employee_id, this.mapRowToAttendance(row));\n });\n\n const employeeReports = employees.map(employee => {\n const attendance = attendanceMap.get(employee.id);\n let status: 'present' | 'absent' | 'late' | 'clocked-in' = 'absent';\n \n if (attendance) {\n if (attendance.status === 'clocked-in') {\n status = 'clocked-in';\n } else if (attendance.status === 'present') {\n // Check if late (assuming 9 AM is standard start time)\n const clockInTime = new Date(attendance.clockIn);\n const standardStart = new Date(attendance.clockIn);\n standardStart.setHours(9, 0, 0, 0);\n status = clockInTime > standardStart ? 'late' : 'present';\n }\n }\n\n return {\n employee,\n attendance,\n status\n };\n });\n\n const presentCount = employeeReports.filter(r => r.status === 'present' || r.status === 'clocked-in').length;\n const lateCount = employeeReports.filter(r => r.status === 'late').length;\n const absentCount = employees.length - presentCount - lateCount;\n\n return {\n date,\n totalEmployees: employees.length,\n presentEmployees: presentCount,\n absentEmployees: absentCount,\n lateEmployees: lateCount,\n attendanceRate: employees.length > 0 ? (presentCount / employees.length) * 100 : 0,\n employees: employeeReports\n };\n }\n\n private static async getTodayRecord(employeeId: number): Promise {\n const today = new Date().toISOString().split('T')[0];\n const rows = await sqlite.execute(`\n SELECT * FROM ${TABLES.ATTENDANCE} \n WHERE employee_id = ? AND date = ?\n ORDER BY clock_in DESC\n LIMIT 1\n `, [employeeId, today]);\n\n return rows.length > 0 ? this.mapRowToAttendance(rows[0]) : null;\n }\n\n private static async getById(id: number): Promise {\n const rows = await sqlite.execute(`\n SELECT * FROM ${TABLES.ATTENDANCE} WHERE id = ?\n `, [id]);\n\n return rows.length > 0 ? this.mapRowToAttendance(rows[0]) : null;\n }\n\n private static mapRowToAttendance(row: any): AttendanceRecord {\n return {\n id: row.id,\n employeeId: row.employee_id,\n clockIn: row.clock_in,\n clockOut: row.clock_out || undefined,\n date: row.date,\n totalHours: row.total_hours || undefined,\n status: row.status,\n notes: row.notes || undefined,\n createdAt: row.created_at,\n updatedAt: row.updated_at\n };\n }\n}","deleted","label",[167,168,149],"backend","database","permalink","https://www.val.town/x/DHIRAJ7655/Attendancetracker/code/backend/database/queries.ts","extension","actionData","errors"]