const MomentHelper = require('../models/momenthelper');
/**
* Class represents training objects in core database and offers methods for handling of this training objects.
* @class
*/
class trainings {
/**
* @constructor
* @param {object} logger logger to be used for output of the class instance (usually req.app.logger)
*/
constructor(logger) {
// always initialize all instance properties
this._logger = logger;
}
/**
* Returns list of Trainings for ui view
* @param {function} callback rows or null
* @memberof trainings
*/
getListOfTrainingsForUI(callback) {
let query = "SELECT trainings_schedule.*, " +
"organizations.id AS organizationid, " +
"organizations.name AS organizationname, " +
"basedata.id AS trainerid, " +
"(basedata.firstname || ' ' || basedata.lastname) AS trainername, " +
"trainings_state.title AS stateAlias, " +
"trainings_state.stateId AS stateId " +
"FROM trainings_schedule " +
"INNER JOIN organizations ON trainings_schedule.organizationid = organizations.id " +
"INNER JOIN basedata ON trainings_schedule.trainer = basedata.id " +
"INNER JOIN trainings_state ON trainings_schedule.state = trainings_state.stateId " +
"ORDER BY trainings_schedule.startdatetime ASC";
global.coreDb.all(query, [], (err, rows) => {
if (err) {
this._logger.error('TRAININGS-MODEL | Error loading trainings from core data: ' + err.message);
return callback(null);
} else {
return callback(rows);
}
});
}
/**
* Returns trainings for month in year
* @param {integer} month
* @param {integer} year
* @param {function} callback rows or null
*/
getListOfTrainingsForCalendar(month, year, callback) {
var lastDay = new Date(year, month, 0).getDate();
if (month < 10) {
month = '0' + month;
}
var startDate = year + '-' + month + '-01';
var endDate = year + '-' + month + '-' + lastDay;
console.log('Start: ' + startDate + ' end: ' + endDate);
let query = "SELECT " +
"trainings_schedule.*, " +
"strftime('%d', `enddatetime`) AS day " +
"FROM trainings_schedule " +
"WHERE trainings_schedule.enddatetime BETWEEN '" + startDate + "' AND '" + endDate + "'";
global.coreDb.all(query, [], (err, rows) => {
if (err) {
this._logger.error('TRAININGS-MODEL | Error loading trainings from core data: ' + err.message);
return callback(null);
} else {
return callback(rows);
}
});
}
/**
* Returns list of training states from core database
* @param {function} callback rows or null
*/
getListOfTrainingsStatus(callback) {
let query = "SELECT * FROM trainings_state";
global.coreDb.all(query, [], (err, rows) => {
if (err) {
this._logger.error('TRAININGS-MODEL | Error loading trainings status from core data: ' + err.message);
return callback(null);
} else {
return callback(rows);
}
});
}
/**
* Returns a training from core database
* @param {integer} trainingId
* @param {*} callback row or null
*/
getTraining(trainingId, callback) {
let query = "SELECT * FROM `trainings_schedule` WHERE id = ?";
global.coreDb.all(query, [
trainingId
], (err, rows) => {
if (err) {
this._logger.error('TRAININGS-MODEL | Error loading training from core data: ' + err.message);
return callback(null);
} else {
return callback(rows);
}
});
}
/**
*
* @param {object} reqBody req.body with form content
* @param {function} callback success, true or false
*/
addTraining(reqBody, callback) {
var momenthelper = new MomentHelper();
let title = reqBody.title;
let trainingtype = reqBody.trainingtype;
let trainerexternal = reqBody.trainerexternal;
let basedataid = reqBody.basedataid;
let organizationid = reqBody.organizationid;
let startdate = reqBody.startdate;
let starttime = reqBody.starttime;
let enddate = reqBody.enddate;
let endtime = reqBody.endtime;
let description = reqBody.description;
let state = reqBody.state;
let startdatetime = momenthelper.formDateTimeToMysql(startdate + ' ' + starttime.replace(/\s/g, ''));
let enddatetime = momenthelper.formDateTimeToMysql(enddate + ' ' + endtime.replace(/\s/g, ''));
let query = "INSERT INTO `trainings_schedule` (title, description, startdatetime, enddatetime, trainer, trainerexternal, organizationid, trainingtype, state) " +
"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
global.coreDb.run(query,
[
title,
description,
startdatetime,
enddatetime,
basedataid,
trainerexternal,
organizationid,
trainingtype,
state
],
function (err) {
if (err) {
console.log('ERROR: ' + err.message);
callback(false);
} else {
callback(true);
}
});
}
/**
*
* @param {object} reqBody req.body with form fields of training edit form
* @param {integer} trainingId id of trainig to be updated
* @param {function} callback success, true or false
*/
updateTraining(reqBody, trainingId, callback) {
var momenthelper = new MomentHelper();
let title = reqBody.title;
let trainingtype = reqBody.trainingtype;
let trainerexternal = reqBody.trainerexternal;
let basedataid = reqBody.basedataid;
let organizationid = reqBody.organizationid;
let description = reqBody.description;
let state = reqBody.state;
let startdate = reqBody.startdate;
let starttime = reqBody.starttime;
let enddate = reqBody.enddate;
let endtime = reqBody.endtime;
let startdatetime = momenthelper.formDateTimeToMysql(startdate + ' ' + starttime.replace(/\s/g, ''));
let enddatetime = momenthelper.formDateTimeToMysql(enddate + ' ' + endtime.replace(/\s/g, ''));
let query = "UPDATE `trainings_schedule` SET `title` = ?, " +
"trainingtype = ?, " +
"trainerexternal = ?, " +
"trainer = ?, " +
"organizationid = ?, " +
"startdatetime = ?," +
"enddatetime = ?, " +
"description = ?, " +
"state = ? " +
"WHERE `trainings_schedule`.`id` = ?";
global.coreDb.run(query,
[
title,
trainingtype,
trainerexternal,
basedataid,
organizationid,
startdatetime,
enddatetime,
description,
state,
trainingId
],
function (err) {
if (err) {
console.log('Error updating training: ' + err.message);
callback(false);
} else {
callback(true);
}
});
}
/**
* Deletes a training object from core database
* @param {integer} trainingId id of training in core database to be deleted
* @param {*} callback success (true or false)
*/
removeTraining(trainingId, callback) {
let query = "DELETE FROM trainings WHERE id = ?";
global.coreDb.run(query,
[
trainingId
],
function (err) {
if (err) {
callback(false);
} else {
callback(true);
}
});
}
/**
* returns the next upcoming training as a row
* @param {function} callback row or null
*/
getNextTraining(callback) {
this._logger.debug('MODELS-TRAININGS | Read next Training');
let query = "SELECT * FROM trainings_schedule WHERE DATE(startdatetime) >= DATE('now') ORDER BY startdatetime ASC LIMIT 1";
global.coreDb.all(query, [], (err, rows) => {
if (err) {
this._logger.error('TRAININGS-MODEL | Error loading trainings status from core data: ' + err.message);
return callback(null);
} else {
return callback(rows);
}
});
}
/**
* sets the attendence state of users (basedata) for a training
* @param {array} basedataIdArray basedataid which attends the training
* @param {integer} trainingscheduleId id of training to be updated for
* @param {integer} setByBaseDataId basedata id of the user making this change
* @param {integer} attendanceState state of attendence
* @param {function} callback success (true or false)
*/
setTrainingAttendance(basedataIdArray, trainingscheduleId, setByBaseDataId, attendanceState, callback) {
// Check if Array is set
basedataIdArray = (typeof basedataIdArray != 'undefined' && basedataIdArray instanceof Array) ? basedataIdArray : []
// first empty existing training attendance entries in Database
let query = "DELETE FROM `trainings_history` WHERE trainingid = ? AND attendanceState = ?";
global.coreDb.run(query,
[
trainingscheduleId,
attendanceState
],
function (err) {
if (err) {
console.log('MODELS-TRAININGS | Error removing Training attendence for update: ' + err.message);
callback(false);
} else {
if (basedataIdArray.length > 0) {
var stmt = global.coreDb.prepare("INSERT INTO `trainings_history` (trainingid, basedataid, setByBasedataId, attendanceState) VALUES (?, ?, ?, ?)");
basedataIdArray.forEach(basedataId => {
stmt.run(trainingscheduleId, basedataId, setByBaseDataId, attendanceState);
});
stmt.finalize((success) => {
console.log('MODELS-TRAINING | added all attendees to a training objectin core data: ' + success);
return callback(true);
});
} else {
return callback(true);
}
}
});
}
/**
* Returns rows for training attendence from core database
* @param {integer} trainingId id of training object in core database
* @param {function} callback rows or null
*/
getTrainingAttendeesforTrainingId(trainingId, callback) {
let query = "SELECT * FROM `trainings_history` WHERE trainingid = ?";
global.coreDb.all(query, [
trainingId
], (err, rows) => {
if (err) {
this._logger.error('TRAININGS-MODEL | Error loading training attendance from core data: ' + err.message);
return callback(null);
} else {
return callback(rows);
}
});
}
/**
* returns training history for a given basedata id from core database
* @param {integer} basedataId basedata id history should be returned for from core database
* @param {*} callback rows or null
*/
getTrainingHistoryforBasedataId(basedataId, callback) {
let query = "SELECT " +
"trainings_schedule.* " +
"FROM trainings_schedule " +
"INNER JOIN trainings_history ON trainings_schedule.id = trainings_history.trainingid " +
"WHERE trainings_history.basedataid = ? " +
"ORDER BY startdatetime DESC;"
global.coreDb.all(query, [
basedataId
], (err, rows) => {
if (err) {
console.log(`TRAININGS-MODEL | Error loading training history for basedata id ${basedataId} from core data: ${err.message}`);
return callback(null);
} else {
return callback(rows);
}
});
}
}
// export the class
module.exports = trainings;