I am making a simple check-in and check-out system for employees. In the main panel I want to show the number of hours worked per day and in another field the weekly total.
I can now show the hours per day in a table, the problem comes when I have to show the total since I make that query to the database with another query.
With the following code I perform the query and perform a render in the index view using the EJS templates.:
router.get('/', (req, res) => {
const week = DateTime.now().weekNumber;
conexion.query('SELECT * FROM assistance WHERE week = ?', week, (error, results) => {
if(error){
throw error;
}else{
res.render('index', {results: results});
}
})
});
With this code I perform the query of the total hours. but I don't know how to show it in the same index view since it is a separate query from the first one:
conexion.query('SELECT time_format(SUM(TIMEDIFF(a.exit, a.entry)), "%H:%i") AS hours from assistance a WHERE a.week = ?', week, (error, results) => {
if(error){
throw error;
}else{
return('index', {totalHours: results});
}
})
In this way I am trying to receive the information in the index view with EJS:
<div>
<div>Total hours:<%= totalHours%></div>
</div>
Use try/catch async-await, then just do both queries.
router.get('/', async(req, res) => {
const week = DateTime.now().weekNumber
try {
// query 1
const totalHours = await conexion.query('SELECT time_format(SUM(TIMEDIFF(a.exit, a.entry)), "%H:%i") AS hours from assistance a WHERE a.week = ?', week)
// query 2
const results = await conexion.query('SELECT * FROM assistance WHERE week = ?', week)
res.render('index', {
totalHours,
results
})
} catch {
res.render('error-page')
}
})