Source: models/basedata.js

let momentHelperObj = require('../models/momenthelper');
const uuidv1 = require('uuid').v1;

/**
 * Class handles all basedata related functionality and offers methods for dealing with basedata objects.
  * @class
 */
class basedata {

    /**
     * Constructor of Basedata
     * @constructor
     * @param {object} logger logger to be used
     */
    constructor(logger) {
        // always initialize all instance properties
        this._logger = logger;

    }


    /**
     * returns a list of all basedata entries
     * @public
     * @param {function} callback rows with all basedata records or null if error during database querying
     */
    getListOfBasedata(callback) {
        let query = "SELECT * FROM `basedata`";
        global.coreDb.all(query, [], (err, rows) => {
            if (err) {
                return callback(null);
            } else {
                return callback(rows);
            }
        });
    }


    /**
     * Returns list of basedata combined with organization information
     * @param {function} callback rows of basedata for table view in UI
     */
    getListOfBasedataForTable(callback) {
        let query = "SELECT basedata.*, " +
            "organizations.id AS organizationid, " +
            "organizations.name AS organizationname, " +
            "users.id AS userId " +
            "FROM `basedata` " +
            "LEFT JOIN organizations ON basedata.organizationid = organizations.id " +
            "LEFT JOIN users ON users.basedataid = basedata.id";
        global.coreDb.all(query, [], (err, rows) => {
            if (err) {
                console.log(err);
                return callback(null);
            } else {
                return callback(rows);
            }
        });
    }


    /**
     * Removes a basedata entry in core database
     * @param {integer} bdId id of basedata entry in core database table basedata
     * @param {function} callback true or false
     */
    removeBasedata(bdId, callback) {
        let query = 'DELETE FROM `basedata` WHERE id = ?';
        global.coreDb.run(query,
            [
                bdId
            ],
            function (err) {
                if (err) {
                    callback(false);
                } else {
                    callback(true);
                }
            });
    }

    /**
     * Returns image filename for basedata image
     * @param {integer} bdId id of basedata entry in core database table basedata
     * @param {function} callback returns string of image or null if not set or not found
     */
    getImageForBasedata(bdId, callback) {
        let query = "SELECT * FROM `basedata` WHERE id = ?";
        global.coreDb.all(query, [
            bdId
        ], (err, rows) => {
            if (err) {
                return callback(null);
            } else {
                if (rows.length > 0) {
                    return callback(rows[0].image);
                } else {
                    return callback(null);
                }
            }
        });
    }


    /**
     * returns basedata entries from core database with organization information
     * @param {function} callback rows
     */
    getListOfBasedataByOrganizations(callback) {
        this._logger.info('Reading Basedata by Organizations from core database');


        let query = "SELECT `basedata`.*, `organizations`.name AS organizationname, `organizations`.id AS organizationid " +
            "FROM `basedata` " +
            "INNER JOIN organizations ON `basedata`.`organizationid` = `organizations`.`id` " +
            "ORDER BY `organizations`.`id`";

        global.coreDb.all(query, [], (err, rows) => {
            if (err) {
                return callback(null);
            } else {
                return callback(rows);
            }
        });
    }

    /**
     * Returns rows of all existing basedata entries in core database independent of state
     * @param {function} callback rows
     */
    getBaseDataStaffCount(callback) {
        this._logger.debug('BASEDATA-MODEL | Module-basedata-getBaseDataStaffCount: loading statistics');
        let query = "SELECT COUNT(basedata.id) AS countMembers FROM basedata";
        global.coreDb.all(query, [], (err, rows) => {
            if (err) {
                return callback(null);
            } else {
                return callback(rows);
            }
        });
    }

    /**
     * Returns rows of active staff from core database
     * @param {function} callback rows
     */
    getBaseDataStaffCountActive(callback) {
        this._logger.debug('BASEDATA-MODEL | Module-basedata-getBaseDataStaffCountActive: loading statistics');
        let query = "SELECT COUNT(basedata.id) AS countMembers FROM basedata WHERE date_entry <= DATE() AND ((date_exit >= DATE()) OR (date_active_exit IS NULL));";
        global.coreDb.all(query, [], (err, rows) => {
            if (err) {
                this._logger.error('BASEDATA-MODEL | Module-basedata-getBaseDataStaffCountActive: Error loading statistics from core datbase: ' + err.message);
                return callback(null);
            } else {
                return callback(rows);
            }
        });
    }

    /**
     * Returns statistics of basedata for organization from core database
     * @param {*} callback 
     */
    getBaseDataStatisticsStaff(callback) {
        this._logger.debug('BASEDATA-MODEL | Module-basedata-getBaseDataStatistics: loading statistics');
        let query = "SELECT " +
            "organizations.`name`, " +
            "COUNT(basedata.id) AS countMembers, " +
            "SUM(CASE " +
            "WHEN ((basedata.date_active_entry IS NOT NULL) AND (basedata.date_active_exit IS NULL)) THEN 1 ELSE 0 END) AS activeMembersCount " +
            "FROM organizations " +
            "INNER JOIN " +
            "basedata ON basedata.organizationid = organizations.id " +
            "GROUP BY organizations.name";
        global.coreDb.all(query, [], (err, rows) => {
            if (err) {
                return callback(null);
            } else {
                return callback(rows);
            }
        });
    }

    /**
     * Returns list of upcomig birthdays of members
     * @param {function} callback rows
     */
    getBaseDataStatisticsBirthdaylist(callback) {
        var moment = require('moment');
        moment().format();
        this._logger.debug('BASEDATA-MODEL | Module-basedata-getBaseDataStatisticsBirthdayList: loading statistics');

        let query = "SELECT " +
            "basedata.id, " +
            "basedata.firstname, " +
            "basedata.lastname, " +
            "basedata.birthday, " +
            "organizations.name AS organizationname, " +
            "case " +
            "    when date(basedata.birthday, '+' || " +
            "        (strftime('%Y', 'now') - strftime('%Y', basedata.birthday)) || ' years') <= date('now')   " +
            "    then strftime('%Y', 'now') - strftime('%Y', basedata.birthday)  " +
            "    else strftime('%Y', 'now') - strftime('%Y', basedata.birthday) - 1  " +
            "end as age, " +
            "case " +
            "    when date(basedata.birthday, '+' || (strftime('%Y', 'now') - strftime('%Y', basedata.birthday)) || ' years') <= date('now')  " +
            "    then strftime('%Y', 'now') || '-' || strftime('%m', birthday) || '-' || strftime('%d', birthday) " +
            "    else (strftime('%Y', 'now')+1) || '-' || strftime('%m', birthday) || '-' || strftime('%d', birthday) " +
            "end AS nextBirthday " +
            "FROM basedata " +
            "INNER JOIN organizations ON basedata.organizationid = organizations.id  " +
            "WHERE `birthday` IS NOT NULL";


        global.coreDb.all(query, [], (err, rows) => {
            if (err) {
                this._logger.error('BASEDATA-MODEL | Module-basedata-getBaseDataStatisticsBirthdayList: Error loading birthday statistics: ' + err.message);
                return callback(null);
            } else {
                return callback(rows);
            }
        });
    }

    /**
     * Returns basedata row for given basedata id
     * @param {function} callback rows
     */
    getBaseDataForId(bdataId, callback) {
        this._logger.debug('BASEDATA-MODEL | Module-basedata-getBaseDataForId: loading basedata entry');
        let query = "SELECT * FROM basedata WHERE id = ?";
        global.coreDb.all(query, [
            bdataId
        ], (err, rows) => {
            if (err) {
                return callback(null);
            } else {
                return callback(rows);
            }
        });
    }

    /**
     * Returns basedata row for given uuid
     * @param {function} callback rows
     */
    getBaseDataForUUID(uuid, callback) {
        this._logger.debug('BASEDATA-MODEL | Module-basedata-getBaseDataForUUID: loading basedata entry');
        let query = "SELECT * FROM basedata WHERE uuid = ?";
        global.coreDb.all(query, [
            uuid
        ], (err, rows) => {
            if (err) {
                return callback(null);
            } else {
                return callback(rows);
            }
        });
    }

    addUserAvatarIfSet(reqFiles, callback) {
        if ((typeof reqFiles !== 'undefined') && (reqFiles !== null)) {
            let uploadedFile = reqFiles.image;
            // check the filetype before uploading it
            if (uploadedFile.mimetype === 'image/png' || uploadedFile.mimetype === 'image/jpeg' || uploadedFile.mimetype === 'image/gif') {
                // upload the file to the /public/assets/img directory
                // get extension first
                var extension = null;
                switch (uploadedFile.mimetype) {
                    case 'image/png':
                        extension = '.png';
                        break;
                    case 'image/jpeg':
                        extension = '.png';
                        break;
                    case 'image/gif':
                        extension = '.gif';
                        break;
                    default:
                        extension = '.gif';
                }
                // Generate uuid for having more or less unique filename for image
                var uuidFilename = uuidv1() + extension;
                var targetFileName = `public/assets/img/${uuidFilename}`;
                uploadedFile.mv(targetFileName, (err) => {
                    if (err) {
                        // error in moving uploaded file
                        return callback(null);
                    } else {
                        // successfully moved file to target
                        return callback(uuidFilename);
                    }
                });
            } else {
                // no file has been uploaded
                return callback(null);
            }
        } else {
            return callback(null);
        }

    }

    /**
     * Adding basedata enty to core database
     * @param {object} reqBody req.body with form data
     * @param {object} reqFiles req.files with files information from form
     * @param {function} callback true or false
     */
    addBasedata(reqBody, reqFiles, callback) {
        let salutation = reqBody.salutation;
        let title = reqBody.title;
        let firstname = reqBody.firstname;
        let lastname = reqBody.lastname;
        let gender = reqBody.gender;
        let birthday = reqBody.birthday;
        let died = reqBody.died;
        let street = reqBody.street;
        let zip = reqBody.zip;
        let city = reqBody.city;
        let state = reqBody.state;
        let country = reqBody.country;
        let personnelnumber = reqBody.personnelnumber;
        let djfnumber = reqBody.djfnumber;
        let date_entry = reqBody.date_entry;
        let date_exit = reqBody.date_exit;
        let date_active_entry = reqBody.date_active_entry;
        let date_active_exit = reqBody.date_active_exit;
        let active_months_additional = reqBody.active_months_additional;
        let phone = reqBody.phone;
        let email = reqBody.email;
        let cellphone = reqBody.cellphone;
        let uuid = uuidv1();

        // let uploadedFile = reqFiles.image;
        let organizationid = reqBody.organizationid;

        let momenthelper = new momentHelperObj();

        var sql_birthday = 'null';
        if (birthday != '') {
            sql_birthday = momenthelper.formDateToMysql(birthday);
        }

        var sql_died = 'null';
        if (died != '') {
            sql_died = momenthelper.formDateToMysql(died);
        }

        var sql_date_entry = 'null';
        if (date_entry != '') {
            sql_date_entry = momenthelper.formDateToMysql(date_entry);
        }

        var sql_date_exit = 'null';
        if (date_exit != '') {
            sql_date_exit = momenthelper.formDateToMysql(sql_date_exit);
        }

        var sql_date_active_entry = 'null';
        if (date_active_entry != '') {
            sql_date_active_entry = momenthelper.formDateToMysql(date_active_entry);
        }

        var sql_date_active_exit = 'null';
        if (date_active_exit != '') {
            sql_date_active_exit = momenthelper.formDateToMysql(date_active_exit);
        }

        // Preset active_months_additional
        if (active_months_additional < 1) { active_months_additional = 0; }

        this.addUserAvatarIfSet(reqFiles, (imageTarget) => {
            // send the user's details to the database
            let query = "INSERT INTO `basedata` " +
                "(salutation, title, firstname, lastname, gender, street, zip, city, state, country, personnelnumber, djfnumber, active_months_additional, image, organizationid, phone, email, cellphone, birthday, died, date_entry, date_exit, date_active_entry, date_active_exit, uuid) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            global.coreDb.run(query,
                [
                    salutation,
                    title,
                    firstname,
                    lastname,
                    gender,
                    street,
                    zip,
                    city,
                    state,
                    country,
                    personnelnumber,
                    djfnumber,
                    active_months_additional,
                    imageTarget,
                    organizationid,
                    phone,
                    email,
                    cellphone,
                    sql_birthday,
                    sql_died,
                    sql_date_entry,
                    sql_date_exit,
                    sql_date_active_entry,
                    sql_date_active_exit,
                    uuid
                ],
                function (err) {
                    if (err) {
                        console.log('ERROR Adding new basedata enty to core database: ' + err.message);
                        callback(false);
                    } else {
                        callback(true);
                    }
                });
        })
    }


    /**
     * Updates information for a basedata entry in core database
     * @param {object} reqBody form data wirh form fields, usually req.body (hint: avatar can not be updated with this method. {@link setAvatar} should has to used for avatar updates)
     * @param {integer} basedataId id of basedata entry to be updated / edited
     * @param {function} callback success (true / false)
     */
    editBasedata(reqBody, basedataId, callback) {
        let salutation = reqBody.salutation;
        let title = reqBody.title;
        let firstname = reqBody.firstname;
        let lastname = reqBody.lastname;
        let gender = reqBody.gender;
        let birthday = reqBody.birthday;
        let died = reqBody.died;
        let street = reqBody.street;
        let zip = reqBody.zip;
        let city = reqBody.city;
        let state = reqBody.state;
        let country = reqBody.country;
        let personnelnumber = reqBody.personnelnumber;
        let djfnumber = reqBody.djfnumber;
        let date_entry = reqBody.date_entry;
        let date_exit = reqBody.date_exit;
        let date_active_entry = reqBody.date_active_entry;
        let date_active_exit = reqBody.date_active_exit;
        let active_months_additional = reqBody.active_months_additional;
        let phone = reqBody.phone;
        let email = reqBody.email;
        let cellphone = reqBody.cellphone;
        let organizationid = reqBody.organizationid;

        let momenthelper = new momentHelperObj();

        var sql_birthday = 'null';
        if (birthday != '') {
            sql_birthday = momenthelper.formDateToMysql(birthday);
        }

        var sql_died = 'null';
        if (died != '') {
            sql_died = momenthelper.formDateToMysql(died);
        }

        var sql_date_entry = 'null';
        if (date_entry != '') {
            sql_date_entry = momenthelper.formDateToMysql(date_entry);
        }

        var sql_date_exit = 'null';
        if (date_exit != '') {
            sql_date_exit = momenthelper.formDateToMysql(sql_date_exit);
        }

        var sql_date_active_entry = 'null';
        if (date_active_entry != '') {
            sql_date_active_entry = momenthelper.formDateToMysql(date_active_entry);
        }

        var sql_date_active_exit = 'null';
        if (date_active_exit != '') {
            sql_date_active_exit = momenthelper.formDateToMysql(date_active_exit);
        }

        // send the user's details to the database
        let query = "UPDATE `basedata` SET " +
            "salutation = ?, " +
            "title = ?, " +
            "firstname = ?, " +
            "lastname = ?, " +
            "gender = ?, " +
            "street = ?, " +
            "zip = ?, " +
            "city = ?, " +
            "state = ?, " +
            "country = ?, " +
            "personnelnumber = ?, " +
            "djfnumber = ?, " +
            "active_months_additional = ?, " +
            "organizationid = ?, " +
            "phone = ?, " +
            "email = ?, " +
            "cellphone = ?, " +
            "birthday = ?, " +
            "died = ?, " +
            "date_entry = ?, " +
            "date_exit = ?, " +
            "date_active_entry = ?, " +
            "date_active_exit = ? " +
            "WHERE id = ?";

        console.log('OrgID: ' + organizationid + ' basedataid: ' + basedataId);

        global.coreDb.run(query,
            [
                salutation,
                title,
                firstname,
                lastname,
                gender,
                street,
                zip,
                city,
                state,
                country,
                personnelnumber,
                djfnumber,
                active_months_additional,
                organizationid,
                phone,
                email,
                cellphone,
                sql_birthday,
                sql_died,
                sql_date_entry,
                sql_date_exit,
                sql_date_active_entry,
                sql_date_active_exit,
                basedataId
            ],
            function (err) {
                if (err) {
                    console.log(`ERROR updating basedata enty with id ${basedataId} to core database: ${err.message}`);
                    callback(false);
                } else {
                    console.log('Successfully updated basedata entry');
                    callback(true);
                }
            });
    };



    /**
     * Sets the avatar for a basedata entry in core database
     * @param {string} imagePath Path to the image to be set as avatar for basedata-entry in core database
     * @param {integer} basedataId id of basedata entry the avatar should be updated for
     * @param {function} callback success (true or false)
     */
    setAvatar(imagePath, basedataId, callback) {
        let query = "UPDATE `basedata` SET image = ? WHERE id = ?";
        global.coreDb.run(query,
            [
                imagePath,
                basedataId
            ],
            function (err) {
                if (err) {
                    console.log('ERROR setting avatar for basedata in core database: ' + err.message);
                    callback(false);
                } else {
                    callback(true);
                }
            });
    }
}

// export the class
module.exports = basedata;