Source: models/trainings.js

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;