const dbconn_OBSOLETE = require('../config/dbconn') // DO NOT USE THIS FOR NEW FUNCTIONS const dbconn = require('../config/dbconn2') var methods = { // ===================== user db ===================== registerNewUser: function(data, callback) { dbconn_OBSOLETE.user.beginTransaction(function(err) { // START TRANSACTION if (err) { throw err } // insert profile dbconn_OBSOLETE.user.query('INSERT INTO user SET ?', data.profile, function (err, results, fields) { if (err) { return dbconn_OBSOLETE.user.rollback(function() { throw err }); } var newUserId = results.insertId // set password var credentialData = { user_id: newUserId, password: data.password } dbconn_OBSOLETE.user.query('INSERT INTO credential SET ?', credentialData, function (err, results, fields) { if (err) { return dbconn_OBSOLETE.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_OBSOLETE.user.query('INSERT INTO user_project_role SET ?', projectRoleData, function (err, results, fields) { if (err) { return dbconn_OBSOLETE.user.rollback(function() { throw err }); } // MLAB-129: INSERT verification token let verificationData = { user_id: newUserId, token: data.verificationToken } dbconn_OBSOLETE.user.query('INSERT INTO verification SET ?', verificationData, function (err, results, fields) { if (err) { return dbconn_OBSOLETE.user.rollback(function() { throw err }); } // COMMIT dbconn_OBSOLETE.user.commit(function(err) { if (err) { return dbconn_OBSOLETE.user.rollback(function() { throw err }) } }) }) }) }); }); callback(err) }) }, getUserByEmail: async function(email) { try { let rows = await dbconn.user.promise().query('SELECT id, verificationStatus, salutation, title, firstname, lastname, industry, organisation, speciality, m4lab_idp FROM user WHERE email = "' +email+'"') if (rows[0][0]) { return rows[0][0] } else { return null } } catch (err) { console.error(err) } return null }, getUserEmailById: async function(userId) { try { let rows = await dbconn.user.promise().query('SELECT email FROM user WHERE id = ' +userId) if (rows[0][0]) { return rows[0][0].email } else { return null } } catch (err) { console.error(err) } return null }, checkUserEmail: async function(email) { try { let rows = await dbconn.user.promise().query('SELECT id, email FROM user WHERE email = "' +email+'"') if (rows[0][0]) { return rows[0][0] } else { return null } } catch (err) { console.error(err) } return null }, getUserByToken: async function(token) { try { let rows = await dbconn.user.promise().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()) if (rows[0][0]) { return rows[0][0] } else { return null } } catch (err) { console.error(err) } return null }, updateUserById: function(userData, callback) { dbconn_OBSOLETE.user.query('UPDATE user SET ? WHERE id = ' +userData.id, userData, function (err, rows, fields) { if (err) throw err callback(err) }) }, updateCredential: function(data, callback) { dbconn_OBSOLETE.user.query('UPDATE credential SET ? WHERE user_id = ' +data.user_id, data, function (err, rows, fields) { if (err) throw err callback(err) }) }, getUserIdByEmail_OBSOLETE: function(email, callback) { let userId dbconn_OBSOLETE.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_OBSOLETE: function(userId, callback) { dbconn_OBSOLETE.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_OBSOLETE.user.query('INSERT INTO user_project_role SET ?', data, function (err, results, fields){ if (err) throw err callback(err) }) }, getVerificationTokenByUserId: async function(userId) { try { let rows = await dbconn.user.promise().query('SELECT token FROM verification WHERE user_id = "' +userId+'"') if (rows[0][0]) { return rows[0][0].token } else { return null } } catch (err) { console.error(err) } return null }, getUserIdByVerificationToken: async function(token) { try { let rows = await dbconn.user.promise().query('SELECT user_id FROM verification WHERE token = "' +token+'"') if (rows[0][0]) { return rows[0][0].user_id } else { return null } } catch (err) { console.error(err) } return null }, verifyUserAccount: function(userData, callback) { dbconn_OBSOLETE.user.beginTransaction(function(err) { // START TRANSACTION if (err) { throw err } // update user status dbconn_OBSOLETE.user.query('UPDATE user SET ? WHERE id =' +userData.id, userData, function (err, rows, fields) { if (err) { return dbconn_OBSOLETE.user.rollback(function() { throw err }) } // delete verification token dbconn_OBSOLETE.user.query('DELETE FROM verification WHERE user_id = '+userData.id, function (err, rows, fields) { if (err) { return dbconn_OBSOLETE.user.rollback(function() { throw err }) } // COMMIT dbconn_OBSOLETE.user.commit(function(err) { if (err) { return dbconn_OBSOLETE.user.rollback(function() { throw err }) } }) }) }) callback(err) }) }, /* ===== GitLab ===== */ getGitlabId: async function(userId) { try { let rows = await dbconn.user.promise().query('SELECT gu.gitlab_userId FROM user_gitlab gu, user u WHERE u.id = "' +userId+'" and gu.user_id = u.id') if (rows[0][0]) { return rows[0][0].gitlab_userId } else { return null } } catch(err) { console.error(err) return err } }, addGitlabUser: function(data, callback){ dbconn_OBSOLETE.user.query('INSERT INTO user_gitlab SET ?', data, function (err) { if (err) throw err callback(err) }) } }; module.exports = methods;