Am using the below NODE js code to run a timeclock. For one specific user, and seemingly this is a problem that's only reared its head today, this particular user (OPID 7) is being told he's worked minus time, whereas other users are shown their correct hours worked in positive time? The user's opid is 7
The tabular data is here, but I'm baffled as I can't see any difference betwene OPID 7 and OPID 3 or 5...
firebird.attach(options, function(err, db) {
if(err) throw err;
app.get('/', (req, res) => {
db.query('SELECT opid, name FROM TXOPS WHERE OPID NOT IN (1, -1, 2, 9, 10, 13, 14)', function(err, rows) {
if(err) throw err;
res.render('index', { operators: rows });
});
})
app.get('/clock/:id', (req, res) => {
if(req.params.id) {
db.query('SELECT opid, name FROM TXOPS WHERE OPID = ' + req.params.id, function(err, operator_row = []) {
if(operator_row.length === 1)
{
var clockedIn = false;
db.query('SELECT FIRST 1 * FROM TXATTENDETXNTS WHERE OPID = ' + req.params.id + ' AND DATETIME >= \'' + moment().startOf('day').format('YYYY-MM-DD HH:mm:ss') + '\' ORDER BY DATETIME DESC', function(err, last_row) {
console.log(last_row);
if(err) throw err;
if(last_row.length === 1 && last_row[0].ETXNTTYPE === 0) clockedIn = true;
if(clockedIn)
{
db.query('SELECT FIRST 10 * FROM TXATTENDETXNTS WHERE OPID = ' + req.params.id + ' AND DATETIME >= \'' + moment().startOf('day').format('YYYY-MM-DD HH:mm:ss') + '\'', function(err, today_rows) {
totalMinutes = 0;
console.log(today_rows);
today_rows.push({ DATETIME: moment().format('YYYY-MM-DD HH:mm:ss')});
splitRows = today_rows.reduce(function(result, value, index, array) {
if (index % 2 === 0)
result.push(array.slice(index, index + 2));
return result;
}, []);
splitRows.forEach(pair => {
totalMinutes += Math.round(moment.duration(moment(pair[1].DATETIME).diff(moment(pair[0].DATETIME))).asMinutes());
});
console.log(totalMinutes);
minuteString = null;
hours = 0;
remainingMinutes = 0;
if(totalMinutes > 60)
{
hours = Math.floor(totalMinutes / 60);
remainingMinutes = totalMinutes - (hours * 60);
}
if(hours > 0 && remainingMinutes > 0)
{
minuteString = hours + ' hours and ' + remainingMinutes + ' minutes'
}
else if(hours > 0 && remainingMinutes === 0)
{
minuteString = hours + ' hours';
}
else {
minuteString = totalMinutes + ' minutes';
}
res.render('clock', { operator: operator_row[0], working_string: minuteString, clockedIn: clockedIn });
});
}
else
{
res.render('clock', {operator: operator_row[0], clockedIn: clockedIn})
}
});
}
});
}
})
app.get('/clock-in/:id', (req, res) => {
db.query('SELECT opid, name FROM TXOPS WHERE OPID = ' + req.params.id, function(err, rows) {
if(rows.length === 0)
{
res.render('error');
}
// Check if he's already clocked in
db.query('SELECT FIRST 1 OPID FROM TXATTENDETXNTS WHERE OPID = ' + req.params.id + ' ORDER BY ATTENDETXNTID DESC', function(err, rows) {
if(err) throw err;
if(rows.length === 1 && rows[0].ETXNTTYPE == 0)
{
res.render('error');
}
else
{
db.query('SELECT FIRST 1 ATTENDETXNTID FROM TXATTENDETXNTS ORDER BY ATTENDETXNTID DESC', function(err, rows) {
lastID = 0;
if(rows.length === 1)
{
lastID = rows[0].ATTENDETXNTID;
}
db.query('INSERT INTO TXATTENDETXNTS (ATTENDETXNTID, OPID, DATETIME, ETXNTTYPE, SCANNEDSTR, BRANCHID, CHANGED_FLAG, DEPTID) VALUES (' + (lastID + 1) + ','+ req.params.id +', \'' + moment().format('YYYY-MM-DD HH:mm:ss') + '\', 0, null, 1, 1, 0)', function(err, rows) {
if(err) throw err;
res.render('success');
client.publish('timeclock'+ req.params.id, 'clocked in');
});
});
}
});
});
})
app.get('/clock-out/:id', (req, res) => {
db.query('SELECT opid, name FROM TXOPS WHERE OPID = ' + req.params.id, function(err, rows) {
if(rows.length === 0)
{
res.render('error');
}
// Check if he's already clocked out
db.query('SELECT FIRST 1 OPID FROM TXATTENDETXNTS WHERE OPID = ' + req.params.id + ' ORDER BY ATTENDETXNTID DESC', function(err, rows) {
if(err) throw err;
if(rows.length === 1 && rows[0].ETXNTTYPE == 1)
{
res.render('error');
}
else
{
db.query('SELECT FIRST 1 ATTENDETXNTID FROM TXATTENDETXNTS ORDER BY ATTENDETXNTID DESC', function(err, rows) {
lastID = 0;
if(rows.length === 1)
{
lastID = rows[0].ATTENDETXNTID;
}
db.query('INSERT INTO TXATTENDETXNTS (ATTENDETXNTID, OPID, DATETIME, ETXNTTYPE, SCANNEDSTR, BRANCHID, CHANGED_FLAG, DEPTID) VALUES (' + (lastID + 1) + ','+ req.params.id +', \''+ moment().format('YYYY-MM-DD HH:mm:ss') +'\', 1, null, 1, 1, 0)', function(err, rows) {
if(err) throw err;
res.render('success');
client.publish('timeclock'+ req.params.id, 'clocked out');
});
});
}
});
});
})
app.get('/break-out/:id', (req, res) => {
db.query('SELECT opid, name FROM TXOPS WHERE OPID = ' + req.params.id, function(err, rows) {
if(rows.length === 0)
{
res.render('error');
}
// Check if he's already clocked out
db.query('SELECT FIRST 1 OPID FROM TXATTENDETXNTS WHERE OPID = ' + req.params.id + ' ORDER BY ATTENDETXNTID DESC', function(err, rows) {
if(err) throw err;
if(rows.length === 1 && rows[0].ETXNTTYPE == 1)
{
res.render('error');
}
else
{
db.query('SELECT FIRST 1 ATTENDETXNTID FROM TXATTENDETXNTS ORDER BY ATTENDETXNTID DESC', function(err, rows) {
lastID = 0;
if(rows.length === 1)
{
lastID = rows[0].ATTENDETXNTID;
}
db.query('INSERT INTO TXATTENDETXNTS (ATTENDETXNTID, OPID, DATETIME, ETXNTTYPE, SCANNEDSTR, BRANCHID, CHANGED_FLAG, DEPTID) VALUES (' + (lastID + 1) + ','+ req.params.id +', \''+ moment().format('YYYY-MM-DD HH:mm:ss') +'\', 1, null, 1, 1, 0)', function(err, rows) {
if(err) throw err;
res.render('success');
client.publish('timeclock'+ req.params.id, 'broke out');
});
});
}
});
});
})
})
