25936145661

Committed 15 May 2026 07:04PM UTC coverage: 89.917% (-1.6%) from 91.497%

Build # 25936145661

Build Type

push

github

Committed by

web-flow

Commit Message

Merge pull request #110 from witseie-elen4010/activity_log

Activity log

Coverage Stats

397 of 474 branches covered (83.76%)

Branch coverage included in aggregate %.

242 of 266 new or added lines in 11 files covered. (90.98%)

17 existing lines in 1 file now uncovered.

896 of 964 relevant lines covered (92.95%)

12.59 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

86.82

/src/controllers/admin-controller.js

const db = require('../../database/db')
const { logActivity } = require('../services/logging-service')
const ActionTypes = require('../services/action-types')
const { FK_DISPLAY, getInputType, friendlyError, buildSearchQuery } = require('../services/admin-helpers')

const PAGE_SIZE = 20

const getAllTables = () =>
  db.prepare('SELECT name FROM sqlite_master WHERE type=\'table\' ORDER BY name').all().map(r => r.name)

const getColumns = (tableName) =>
  db.prepare(`PRAGMA table_info("${tableName}")`).all()

const getForeignKeys = (tableName) =>
  db.prepare(`PRAGMA foreign_key_list("${tableName}")`).all()

const getForeignKeyOptions = (fkList) => {
  const options = {}
  fkList.forEach(fk => {
    if (!options[fk.from]) {
      const display = FK_DISPLAY[fk.table]
      if (display) {
        const [valCol, labelCol] = display
        const rows = db.prepare(`SELECT "${valCol}" as val, "${labelCol}" as label FROM "${fk.table}" ORDER BY 2`).all()
        options[fk.from] = rows.map(r => ({ value: r.val, label: `${r.val} — ${r.label}` }))
      } else {
        const rows = db.prepare(`SELECT "${fk.to}" as val FROM "${fk.table}" ORDER BY 1`).all()
        options[fk.from] = rows.map(r => ({ value: r.val, label: String(r.val) }))
      }
    }
  })
  return options
}

const getInputTypes = (columns) => {
  const types = {}
  columns.forEach(col => { types[col.name] = getInputType(col.name) })
  return types
}

const showAdminDashboard = (req, res) => {
  const user = { id: req.session.userId, name: req.session.userName }
  const tables = getAllTables()
  res.render('admin-dashboard', {
    user,
    tables,
    activeTable: null,
    columns: [],
    rows: [],
    page: 1,
    totalPages: 1,
    totalRows: 0,
    search: '',
    fkOptions: {},
    inputTypes: {},
    error: null,
    success: null
  })
}
// const user = { id: req.session.userId, name: req.session.userName };
// const tables = getAllTables();
// const stats = {
//   students:      db.prepare(`SELECT COUNT(*) as n FROM students`).get().n,
//   staff:         db.prepare(`SELECT COUNT(*) as n FROM staff`).get().n,
//   consultations: db.prepare(`SELECT COUNT(*) as n FROM consultations`).get().n,
//   availability:  db.prepare(`SELECT COUNT(*) as n FROM lecturer_availability`).get().n,
// };
// res.render('admin-dashboard', {
//   user, tables, stats,
//   activeTable: null, columns: [], rows: [], page: 1, totalPages: 1, totalRows: 0, search: '',
//   fkOptions: {}, inputTypes: {}, error: null, success: null,
// });
// };

const showTable = (req, res) => {
  const user = { id: req.session.userId, name: req.session.userName }
  const tables = getAllTables()
  const { tableName } = req.params

  if (!tables.includes(tableName)) return res.status(404).send('Table not found')

  const page = Math.max(1, parseInt(req.query.page) || 1)
  const offset = (page - 1) * PAGE_SIZE
  const search = (req.query.search || '').trim()
  const columns = getColumns(tableName)
  const fkOptions = getForeignKeyOptions(getForeignKeys(tableName))
  const inputTypes = getInputTypes(columns)

  let totalRows, rows
  if (search) {
    const { whereClauses, params } = buildSearchQuery(columns, search)
    totalRows = db.prepare(`SELECT COUNT(*) as count FROM "${tableName}" WHERE ${whereClauses}`).get(...params).count
    rows = db.prepare(`SELECT *, rowid as rowid FROM "${tableName}" WHERE ${whereClauses} LIMIT ? OFFSET ?`).all(...params, PAGE_SIZE, offset)
  } else {
    totalRows = db.prepare(`SELECT COUNT(*) as count FROM "${tableName}"`).get().count
    rows = db.prepare(`SELECT *, rowid as rowid FROM "${tableName}" LIMIT ? OFFSET ?`).all(PAGE_SIZE, offset)
  }

  const totalPages = Math.max(1, Math.ceil(totalRows / PAGE_SIZE))

  res.render('admin-dashboard', {
    user,
    tables,
    activeTable: tableName,
    columns,
    rows,
    page,
    totalPages,
    totalRows,
    search,
    fkOptions,
    inputTypes,
    // user, tables, stats: null,
    // activeTable: tableName, columns, rows, page, totalPages, totalRows, search, fkOptions, inputTypes,
    error: req.query.error || null,
    success: req.query.success || null
  })
}

const createRecord = async (req, res) => {
  const tables = getAllTables()
  const { tableName } = req.params
  if (!tables.includes(tableName)) return res.status(404).send('Table not found')

  const columns = getColumns(tableName)
  const fields = columns.map(c => c.name)
  const values = fields.map(f => (req.body[f] !== '' && req.body[f] !== undefined) ? req.body[f] : null)
  const placeholders = fields.map(() => '?').join(', ')
  const fieldList = fields.map(f => `"${f}"`).join(', ')

  try {
    db.prepare(`INSERT INTO "${tableName}" (${fieldList}) VALUES (${placeholders})`).run(...values)
    await logActivity(req.session.userId, ActionTypes.ADMIN_USER_ADD, [{ table: tableName, id: db.prepare('SELECT last_insert_rowid() as id').get().id }])
    res.redirect(`/admin/table/${tableName}?success=Record+added`)
  } catch (err) {
    const fkOptions = getForeignKeyOptions(getForeignKeys(tableName))
    const inputTypes = getInputTypes(columns)
    const totalRows = db.prepare(`SELECT COUNT(*) as count FROM "${tableName}"`).get().count
    const totalPages = Math.max(1, Math.ceil(totalRows / PAGE_SIZE))
    const rows = db.prepare(`SELECT *, rowid as rowid FROM "${tableName}" LIMIT ?`).all(PAGE_SIZE)
    res.render('admin-dashboard', {
      user: { id: req.session.userId, name: req.session.userName },
      tables,
      activeTable: tableName,
      columns,
      rows,
      page: 1,
      totalPages,
      totalRows,
      search: '',
      fkOptions,
      inputTypes,
      error: friendlyError(err.message),
      success: null
    })
    //   tables, stats: null, activeTable: tableName, columns, rows, page: 1, totalPages, totalRows, search: '',
    //   fkOptions, inputTypes, error: friendlyError(err.message), success: null,
    // });
  }
}

const updateRecord = async (req, res) => {
  const tables = getAllTables()
  const { tableName, rowId } = req.params
  if (!tables.includes(tableName)) return res.status(404).send('Table not found')

  const columns = getColumns(tableName)
  const updatable = columns.filter(c => c.pk === 0)
  if (updatable.length === 0) { return res.redirect(`/admin/table/${tableName}?error=This+table+has+no+editable+columns`) }

  const setClauses = updatable.map(c => `"${c.name}" = ?`).join(', ')
  const values = [
    ...updatable.map(c => (req.body[c.name] !== '' && req.body[c.name] !== undefined) ? req.body[c.name] : null),
    rowId
  ]

  try {
    db.prepare(`UPDATE "${tableName}" SET ${setClauses} WHERE rowid = ?`).run(...values)
    await logActivity(req.session.userId, ActionTypes.ADMIN_USER_EDIT, [{ table: tableName, id: rowId }])
    res.redirect(`/admin/table/${tableName}?success=Record+updated`)
  } catch (err) {
    res.redirect(`/admin/table/${tableName}?error=${encodeURIComponent(friendlyError(err.message))}`)
  }
}

const deleteRecord = async (req, res) => {
  const tables = getAllTables()
  const { tableName, rowId } = req.params
  if (!tables.includes(tableName)) return res.status(404).send('Table not found')
  if (tableName === 'admins') return res.redirect('/admin/table/admins?error=Admin+accounts+cannot+be+deleted')

  try {
    db.prepare(`DELETE FROM "${tableName}" WHERE rowid = ?`).run(rowId)
    await logActivity(req.session.userId, ActionTypes.ADMIN_USER_DELETE, [{ table: tableName, id: rowId }])
    res.redirect(`/admin/table/${tableName}?success=Record+deleted`)
  } catch (err) {
    res.redirect(`/admin/table/${tableName}?error=${encodeURIComponent(friendlyError(err.message))}`)
  }
}

module.exports = { showAdminDashboard, showTable, createRecord, updateRecord, deleteRecord }

1	const db = require('../../database/db')	10✔
2	const { logActivity } = require('../services/logging-service')	10✔
3	const ActionTypes = require('../services/action-types')	10✔
4	const { FK_DISPLAY, getInputType, friendlyError, buildSearchQuery } = require('../services/admin-helpers')	10✔
5
6	const PAGE_SIZE = 20	10✔
7
8	const getAllTables = () =>	10✔
9	db.prepare('SELECT name FROM sqlite_master WHERE type=\'table\' ORDER BY name').all().map(r => r.name)	33✔
10
11	const getColumns = (tableName) =>	10✔
12	db.prepare(`PRAGMA table_info("${tableName}")`).all()	6✔
13
14	const getForeignKeys = (tableName) =>	10✔
15	db.prepare(`PRAGMA foreign_key_list("${tableName}")`).all()	3✔
16
17	const getForeignKeyOptions = (fkList) => {	10✔
18	const options = {}	3✔
19	fkList.forEach(fk => {	3✔
20	if (!options[fk.from]) {	×
NEW 21	const display = FK_DISPLAY[fk.table]	×
22	if (display) {	×
NEW 23	const [valCol, labelCol] = display	×
NEW 24	const rows = db.prepare(`SELECT "${valCol}" as val, "${labelCol}" as label FROM "${fk.table}" ORDER BY 2`).all()	×
NEW 25	options[fk.from] = rows.map(r => ({ value: r.val, label: `${r.val} — ${r.label}` }))	×
26	} else {
NEW 27	const rows = db.prepare(`SELECT "${fk.to}" as val FROM "${fk.table}" ORDER BY 1`).all()	×
NEW 28	options[fk.from] = rows.map(r => ({ value: r.val, label: String(r.val) }))	×
29	}
30	}
31	})
32	return options	3✔
33	}
34
35	const getInputTypes = (columns) => {	10✔
36	const types = {}	3✔
37	columns.forEach(col => { types[col.name] = getInputType(col.name) })	6✔
38	return types	3✔
39	}
40
41	const showAdminDashboard = (req, res) => {	10✔
42	const user = { id: req.session.userId, name: req.session.userName }	1✔
43	const tables = getAllTables()	1✔
44	res.render('admin-dashboard', {	1✔
45	user,
46	tables,
47	activeTable: null,
48	columns: [],
49	rows: [],
50	page: 1,
51	totalPages: 1,
52	totalRows: 0,
53	search: '',
54	fkOptions: {},
55	inputTypes: {},
56	error: null,
57	success: null
58	})
59	}
60	// const user = { id: req.session.userId, name: req.session.userName };
61	// const tables = getAllTables();
62	// const stats = {
63	// students: db.prepare(`SELECT COUNT(*) as n FROM students`).get().n,
64	// staff: db.prepare(`SELECT COUNT(*) as n FROM staff`).get().n,
65	// consultations: db.prepare(`SELECT COUNT(*) as n FROM consultations`).get().n,
66	// availability: db.prepare(`SELECT COUNT(*) as n FROM lecturer_availability`).get().n,
67	// };
68	// res.render('admin-dashboard', {
69	// user, tables, stats,
70	// activeTable: null, columns: [], rows: [], page: 1, totalPages: 1, totalRows: 0, search: '',
71	// fkOptions: {}, inputTypes: {}, error: null, success: null,
72	// });
73	// };
74
75	const showTable = (req, res) => {	10✔
76	const user = { id: req.session.userId, name: req.session.userName }	3✔
77	const tables = getAllTables()	3✔
78	const { tableName } = req.params	3✔
79
80	if (!tables.includes(tableName)) return res.status(404).send('Table not found')	3✔
81
82	const page = Math.max(1, parseInt(req.query.page) \|\| 1)	2✔
83	const offset = (page - 1) * PAGE_SIZE	2✔
84	const search = (req.query.search \|\| '').trim()	2✔
85	const columns = getColumns(tableName)	2✔
86	const fkOptions = getForeignKeyOptions(getForeignKeys(tableName))	2✔
87	const inputTypes = getInputTypes(columns)	2✔
88
89	let totalRows, rows
90	if (search) {	2✔
91	const { whereClauses, params } = buildSearchQuery(columns, search)	1✔
92	totalRows = db.prepare(`SELECT COUNT(*) as count FROM "${tableName}" WHERE ${whereClauses}`).get(...params).count	1✔
93	rows = db.prepare(`SELECT *, rowid as rowid FROM "${tableName}" WHERE ${whereClauses} LIMIT ? OFFSET ?`).all(...params, PAGE_SIZE, offset)	1✔
94	} else {
95	totalRows = db.prepare(`SELECT COUNT(*) as count FROM "${tableName}"`).get().count	1✔
96	rows = db.prepare(`SELECT *, rowid as rowid FROM "${tableName}" LIMIT ? OFFSET ?`).all(PAGE_SIZE, offset)	1✔
97	}
98
99	const totalPages = Math.max(1, Math.ceil(totalRows / PAGE_SIZE))	2✔
100
101	res.render('admin-dashboard', {	2✔
102	user,
103	tables,
104	activeTable: tableName,
105	columns,
106	rows,
107	page,
108	totalPages,
109	totalRows,
110	search,
111	fkOptions,
112	inputTypes,
113	// user, tables, stats: null,
114	// activeTable: tableName, columns, rows, page, totalPages, totalRows, search, fkOptions, inputTypes,
115	error: req.query.error \|\| null,	4✔
116	success: req.query.success \|\| null	4✔
117	})
118	}
119
120	const createRecord = async (req, res) => {	10✔
121	const tables = getAllTables()	2✔
122	const { tableName } = req.params	2✔
123	if (!tables.includes(tableName)) return res.status(404).send('Table not found')	2!
124
125	const columns = getColumns(tableName)	2✔
126	const fields = columns.map(c => c.name)	4✔
127	const values = fields.map(f => (req.body[f] !== '' && req.body[f] !== undefined) ? req.body[f] : null)	4!
128	const placeholders = fields.map(() => '?').join(', ')	4✔
129	const fieldList = fields.map(f => `"${f}"`).join(', ')	4✔
130
131	try {	2✔
132	db.prepare(`INSERT INTO "${tableName}" (${fieldList}) VALUES (${placeholders})`).run(...values)	2✔
133	await logActivity(req.session.userId, ActionTypes.ADMIN_USER_ADD, [{ table: tableName, id: db.prepare('SELECT last_insert_rowid() as id').get().id }])	1✔
134	res.redirect(`/admin/table/${tableName}?success=Record+added`)	1✔
135	} catch (err) {
136	const fkOptions = getForeignKeyOptions(getForeignKeys(tableName))	1✔
137	const inputTypes = getInputTypes(columns)	1✔
138	const totalRows = db.prepare(`SELECT COUNT(*) as count FROM "${tableName}"`).get().count	1✔
139	const totalPages = Math.max(1, Math.ceil(totalRows / PAGE_SIZE))	1✔
140	const rows = db.prepare(`SELECT *, rowid as rowid FROM "${tableName}" LIMIT ?`).all(PAGE_SIZE)	1✔
141	res.render('admin-dashboard', {	1✔
142	user: { id: req.session.userId, name: req.session.userName },
143	tables,
144	activeTable: tableName,
145	columns,
146	rows,
147	page: 1,
148	totalPages,
149	totalRows,
150	search: '',
151	fkOptions,
152	inputTypes,
153	error: friendlyError(err.message),
154	success: null
155	})
156	// tables, stats: null, activeTable: tableName, columns, rows, page: 1, totalPages, totalRows, search: '',
157	// fkOptions, inputTypes, error: friendlyError(err.message), success: null,
158	// });
159	}
160	}
161
162	const updateRecord = async (req, res) => {	10✔
163	const tables = getAllTables()	2✔
164	const { tableName, rowId } = req.params	2✔
165	if (!tables.includes(tableName)) return res.status(404).send('Table not found')	2!
166
167	const columns = getColumns(tableName)	2✔
168	const updatable = columns.filter(c => c.pk === 0)	4✔
169	if (updatable.length === 0) { return res.redirect(`/admin/table/${tableName}?error=This+table+has+no+editable+columns`) }	2!
170
171	const setClauses = updatable.map(c => `"${c.name}" = ?`).join(', ')	2✔
172	const values = [	2✔
173	...updatable.map(c => (req.body[c.name] !== '' && req.body[c.name] !== undefined) ? req.body[c.name] : null),	2✔
174	rowId
175	]
176
177	try {	2✔
178	db.prepare(`UPDATE "${tableName}" SET ${setClauses} WHERE rowid = ?`).run(...values)	2✔
179	await logActivity(req.session.userId, ActionTypes.ADMIN_USER_EDIT, [{ table: tableName, id: rowId }])	1✔
180	res.redirect(`/admin/table/${tableName}?success=Record+updated`)	1✔
181	} catch (err) {
182	res.redirect(`/admin/table/${tableName}?error=${encodeURIComponent(friendlyError(err.message))}`)	1✔
183	}
184	}
185
186	const deleteRecord = async (req, res) => {	10✔
187	const tables = getAllTables()	3✔
188	const { tableName, rowId } = req.params	3✔
189	if (!tables.includes(tableName)) return res.status(404).send('Table not found')	3!
190	if (tableName === 'admins') return res.redirect('/admin/table/admins?error=Admin+accounts+cannot+be+deleted')	3✔
191
192	try {	2✔
193	db.prepare(`DELETE FROM "${tableName}" WHERE rowid = ?`).run(rowId)	2✔
194	await logActivity(req.session.userId, ActionTypes.ADMIN_USER_DELETE, [{ table: tableName, id: rowId }])	1✔
195	res.redirect(`/admin/table/${tableName}?success=Record+deleted`)	1✔
196	} catch (err) {
197	res.redirect(`/admin/table/${tableName}?error=${encodeURIComponent(friendlyError(err.message))}`)	1✔
198	}
199	}
200
201	module.exports = { showAdminDashboard, showTable, createRecord, updateRecord, deleteRecord }	10✔

witseie-elen4010 / 2026-group-lab-002 / 25936145661

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous