import dbconn = require('../config/dbconn') var methods = { // ===================== user db ===================== registerNewUser: function(data:any, callback:any) { dbconn.user.beginTransaction(function(err:any) { // START TRANSACTION if (err) { throw err } // insert profile dbconn.user.query('INSERT INTO user SET ?', data.profile, function (err:any, results:any, fields:any) { if (err) { return dbconn.user.rollback(function() { throw err }); } let newUserId:number = results.insertId // set password var credentialData:any = { user_id: newUserId, password: data.password } dbconn.user.query('INSERT INTO credential SET ?', credentialData, function (err:any, results:any, fields:any) { if (err) { return dbconn.user.rollback(function() { throw err }); } // set default user-project-role var projectRoleData:any = { project_id: 1, //M4_LAB role_id: 2, // USER user_id: newUserId } dbconn.user.query('INSERT INTO user_project_role SET ?', projectRoleData, function (err:any, results:any, fields:any) { if (err) { return dbconn.user.rollback(function() { throw err }); } // MLAB-129: INSERT verification token let verificationData:any = { user_id: newUserId, token: data.verificationToken } dbconn.user.query('INSERT INTO verification SET ?', verificationData, function (err:any, results:any, fields:any) { if (err) { return dbconn.user.rollback(function() { throw err }); } // COMMIT dbconn.user.commit(function(err:any) { if (err) { return dbconn.user.rollback(function() { throw err }) } }) }) }) }); }); callback(err) }) }, getUserByEmail: async function(email:any) { try { let rows:any = 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:number) { try { let rows:any = 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:any) { try { let rows:any = 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:any) { try { let rows:any = 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: async function(userId:number, userData:any) { try { let result:any = await dbconn.user.promise().query('UPDATE user SET ? WHERE id = ' +userId, userData) return result } catch (err) { console.error(err) } return null }, updateCredential: async function(data:any) { try { let result:any = await dbconn.user.promise().query('UPDATE credential SET ? WHERE user_id = ' +data.user_id, data) return result } catch (err) { console.error(err) } return null }, addUserProjectRole_OBSOLETE: function(data:any, callback:any) { dbconn.user.query('INSERT INTO user_project_role SET ?', data, function (err:any){ if (err) throw err callback(err) }) }, getVerificationTokenByUserId: async function(userId:number) { try { let rows:any = 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:any) { try { let rows:any = 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:any, callback:any) { dbconn.user.beginTransaction(function(err:any) { // START TRANSACTION if (err) { throw err } // update user status dbconn.user.query('UPDATE user SET ? WHERE id =' +userData.id, userData, function (err:any, rows:any, fields:any) { if (err) { return dbconn.user.rollback(function() { throw err }) } // delete verification token dbconn.user.query('DELETE FROM verification WHERE user_id = '+userData.id, function (err:any, rows:any, fields:any) { if (err) { return dbconn.user.rollback(function() { throw err }) } // COMMIT dbconn.user.commit(function(err:any) { if (err) { return dbconn.user.rollback(function() { throw err }) } }) }) }) callback(err) }) }, /* ===== GitLab ===== */ getGitlabId: async function(userId:number) { try { let rows:any = 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:any, callback:any){ dbconn.user.query('INSERT INTO user_gitlab SET ?', data, function (err:any) { if (err) throw err callback(err) }) } }; export = methods