const dbconn = require('./dbconn'); var methods = { // test method currentDate: function() { console.log('Current Date is: ' + new Date().toISOString().slice(0, 10)); }, // ===================== user db ===================== registerNewUser: function(data, callback) { dbconn.user.beginTransaction(function(err) { // START TRANSACTION if (err) { throw err } // insert profile dbconn.user.query('INSERT INTO user SET ?', data.profile, function (err, results, fields) { if (err) { return dbconn.user.rollback(function() { throw err }); } var newUserId = results.insertId // set password var credentialData = { user_id: newUserId, password: data.password } dbconn.user.query('INSERT INTO credential SET ?', credentialData, function (err, results, fields) { if (err) { return dbconn.user.rollback(function() { throw err }); } // set default user-project-role var projectRoleData = { project_id: 1, //M4_LAB role_id: 2, // USER user_id: newUserId } dbconn.user.query('INSERT INTO user_project_role SET ?', projectRoleData, function (err, results, fields) { if (err) { return dbconn.user.rollback(function() { throw err }); } // COMMIT dbconn.user.commit(function(err) { if (err) { return dbconn.user.rollback(function() { throw err }); } }); }) }); }); callback(err) }) }, getUserByEmail: function(email, callback) { dbconn.user.query('SELECT salutation, title, firstname, lastname, industry, organisation, speciality FROM user WHERE email = "' +email+'"', function (err, rows, fields) { if (err) { throw err; } else { if ( rows.length > 0) { user = rows[0]; } } callback(user, err); }); }, checkUserEmail: function(email, callback) { var user; dbconn.user.query('SELECT id, email FROM user WHERE email = "' +email+'"', function (err, rows, fields) { if (err) { throw err; } else { if ( rows.length > 0) { user = rows[0]; } } callback(err, user); }); }, getUserByToken: function(token, callback) { var user; dbconn.user.query('SELECT t1.user_id, t2.email FROM userdb.credential AS t1 INNER JOIN userdb.user AS t2 ON t1.user_id = t2.id AND t1.resetPasswordToken = "' +token+'" and resetPasswordExpires > '+Date.now(), function (err, rows, fields) { if (err) { throw err; } else { if ( rows.length > 0) { user = rows[0] console.log(user) } } callback(err, user); } ); }, updateUser: function(userData, callback) { dbconn.user.query('UPDATE user SET ? WHERE email = "' +userData.email+'"', userData, function (err, rows, fields) { if (err) throw err; callback(err); }) }, updateCredential: function(data, callback) { dbconn.user.query('UPDATE credential SET ? WHERE user_id = ' +data.user_id, data, function (err, rows, fields) { if (err) throw err; callback(err); }) }, getUserIdByEmail: function(email, callback) { var userId dbconn.user.query('SELECT id FROM user WHERE email = "' +email+'"', function (err, rows, fields) { if (err) { throw err; } else { if ( rows.length > 0) { userId = rows[0].id; } } callback(userId, err); }); }, getUserProjectRole: function(userId, callback) { dbconn.user.query('SELECT project_id, role_id FROM user_project_role WHERE user_id = "' +userId+'"', function (err, rows, fields) { if (err) throw err; callback(rows, err); }); }, addUserProjectRole: function(data, callback) { dbconn.user.query('INSERT INTO user_project_role SET ?', data, function (err, results, fields){ if (err) throw err; callback(err); }) }, // ======================= project db ======================= getAllProjects: function(callback) { dbconn.project.query('CALL getAllprojects', function (err, rows, fields){ if (err) throw err; callback(rows[0], err); }) }, getAllMailinglists: function(callback) { dbconn.project.query('CALL getAllLists', function (err, rows, fields){ if (err) throw err; callback(rows[0], err); }) }, getProjectOverviewById: function(projectId, callback) { dbconn.project.query('CALL GetProjectInformationByProjectID(' + projectId+ ')', function (err, rows, fields){ if (err) throw err; callback(rows[0], err); }) }, getProjectImagesById: function(projectId, callback) { dbconn.project.query('CALL getImagesByProjectID(' + projectId+ ')', function (err, rows, fields){ if (err) throw err; callback(rows[0], err); }) }, addProjectOverview: function(data, callback) { dbconn.project.query('INSERT INTO project_overview SET ?', data, function (err, results, fields){ if (err) { console.error(err); } callback(results, err); }) } }; module.exports = methods;