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;