methods.js 9.65 KB
Newer Older
1
2
const dbconn_OBSOLETE = require('./dbconn') // DO NOT USE THIS FOR NEW FEATURE
const dbconn = require('./dbconn2')
Rosanny Sihombing's avatar
Rosanny Sihombing committed
3
4

var methods = {
5
6
    // ===================== user db =====================
    registerNewUser: function(data, callback) {
7
        dbconn_OBSOLETE.user.beginTransaction(function(err) { // START TRANSACTION
8
9
10
11
            if (err) {
                throw err
            }
            // insert profile
12
            dbconn_OBSOLETE.user.query('INSERT INTO user SET ?', data.profile, function (err, results, fields) {
13
                if (err) {
14
                  return dbconn_OBSOLETE.user.rollback(function() {
15
16
17
18
19
20
21
22
23
                    throw err
                  });
                }
                var newUserId = results.insertId
                // set password
                var credentialData = {
                    user_id: newUserId,
                    password: data.password
                }
24
                dbconn_OBSOLETE.user.query('INSERT INTO credential SET ?', credentialData, function (err, results, fields) {
25
                    if (err) {
26
                        return dbconn_OBSOLETE.user.rollback(function() {
27
28
29
30
31
32
33
34
35
                            throw err
                        });
                    }
                    // set default user-project-role
                    var projectRoleData = {
                        project_id: 1,  //M4_LAB
                        role_id: 2,     // USER
                        user_id: newUserId
                    }
36
                    dbconn_OBSOLETE.user.query('INSERT INTO user_project_role SET ?', projectRoleData, function (err, results, fields) {
37
                        if (err) {
38
                            return dbconn_OBSOLETE.user.rollback(function() {
39
40
41
                                throw err
                            });
                        }
42
43
44
45
46
                        // MLAB-129: INSERT verification token
                        let verificationData = {
                            user_id: newUserId,
                            token: data.verificationToken
                        }
47
                        dbconn_OBSOLETE.user.query('INSERT INTO verification SET ?', verificationData, function (err, results, fields) {
48
                            if (err) {
49
                                return dbconn_OBSOLETE.user.rollback(function() {
50
51
52
                                    throw err
                                });
                            }
53
                            // COMMIT
54
                            dbconn_OBSOLETE.user.commit(function(err) {
55
                                if (err) {
56
                                    return dbconn_OBSOLETE.user.rollback(function() {
57
58
59
60
61
                                        throw err
                                    })
                                }
                            })
                        })
62
63
64
65
66
67
                    })
                });
            });
            callback(err)
        })
    },
68
69
    getUserByEmail_OBSOLETE: function(email, callback) {
        dbconn_OBSOLETE.user.query('SELECT id, verificationStatus, salutation, title, firstname, lastname, industry, organisation, speciality, m4lab_idp FROM user WHERE email = "' +email+'"', function (err, rows, fields) {
Rosanny Sihombing's avatar
Rosanny Sihombing committed
70
71
            let user
            if (err) { throw err }
72
73
            else {
                if ( rows.length > 0) {
Rosanny Sihombing's avatar
Rosanny Sihombing committed
74
                    user = rows[0]
75
76
                }
            }
Rosanny Sihombing's avatar
Rosanny Sihombing committed
77
            callback(user, err)
78
        });
79
    },
80
81
82
83
84
85
86
87
88
    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+'"')
            return rows[0][0]
        } catch (err) {
            console.error(err)
            return err
        }
    },
89
    getUserById: function(userId, callback) {
90
        dbconn_OBSOLETE.user.query('SELECT verificationStatus, email, salutation, title, firstname, lastname, industry, organisation, speciality FROM user WHERE id = ' +userId, function (err, rows, fields) {
Rosanny Sihombing's avatar
Rosanny Sihombing committed
91
92
            let user
            if (err) { throw err }
93
94
95
96
97
98
99
            else {
                if ( rows.length > 0) {
                    user = rows[0];
                }
            }
            callback(user, err);
        });
100
    },
Rosanny Sihombing's avatar
Rosanny Sihombing committed
101
	checkUserEmail: function(email, callback) {
102
        let user
103
        dbconn_OBSOLETE.user.query('SELECT id, email FROM user WHERE email = "' +email+'"', function (err, rows) {
Rosanny Sihombing's avatar
Rosanny Sihombing committed
104
            if (err) { throw err }
Rosanny Sihombing's avatar
Rosanny Sihombing committed
105
106
107
108
109
            else {
                if ( rows.length > 0) {
                    user = rows[0];
                }
            }
Rosanny Sihombing's avatar
Rosanny Sihombing committed
110
            callback(err, user)
Rosanny Sihombing's avatar
Rosanny Sihombing committed
111
112
        });
    },
113
    getUserByToken: function(token, callback) {
114
        let user
115
        dbconn_OBSOLETE.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 = "'
116
            +token+'" and resetPasswordExpires > '+Date.now(), function (err, rows, fields) {
Rosanny Sihombing's avatar
Rosanny Sihombing committed
117
                if (err) { throw err }
118
119
120
121
                else {
                    if ( rows.length > 0) {
                        user = rows[0]
                    }
Rosanny Sihombing's avatar
Rosanny Sihombing committed
122
                }
Rosanny Sihombing's avatar
Rosanny Sihombing committed
123
                callback(err, user)
Rosanny Sihombing's avatar
Rosanny Sihombing committed
124
            }
Rosanny Sihombing's avatar
Rosanny Sihombing committed
125
        )
Rosanny Sihombing's avatar
Rosanny Sihombing committed
126
    },
127
	updateUserById: function(userData, callback) {
128
		dbconn_OBSOLETE.user.query('UPDATE user SET ? WHERE id = ' +userData.id, userData, function (err, rows, fields) {
129
130
            if (err) throw err
            callback(err)
Rosanny Sihombing's avatar
Rosanny Sihombing committed
131
132
        })
    },
133
    updateCredential: function(data, callback) {
134
        dbconn_OBSOLETE.user.query('UPDATE credential SET ? WHERE user_id = ' +data.user_id, data, function (err, rows, fields) {
135
136
            if (err) throw err
            callback(err)
Rosanny Sihombing's avatar
Rosanny Sihombing committed
137
138
139
        })
    },
    getUserIdByEmail: function(email, callback) {
140
        let userId
141
        dbconn_OBSOLETE.user.query('SELECT id FROM user WHERE email = "' +email+'"', function (err, rows, fields) {
Rosanny Sihombing's avatar
Rosanny Sihombing committed
142
            if (err) {
143
                throw err
Rosanny Sihombing's avatar
Rosanny Sihombing committed
144
145
146
            }
            else {
                if ( rows.length > 0) {
147
                    userId = rows[0].id
Rosanny Sihombing's avatar
Rosanny Sihombing committed
148
149
                }
            }
150
            callback(userId, err)
Rosanny Sihombing's avatar
Rosanny Sihombing committed
151
152
153
        });
    },
    getUserProjectRole: function(userId, callback) {
154
        dbconn_OBSOLETE.user.query('SELECT project_id, role_id FROM user_project_role WHERE user_id = "' +userId+'"', function (err, rows, fields) {
155
156
            if (err) throw err
            callback(rows, err)
Rosanny Sihombing's avatar
Rosanny Sihombing committed
157
158
        });
    },
Wolfgang Knopki's avatar
Wolfgang Knopki committed
159
    addUserProjectRole: function(data, callback) {
160
        dbconn_OBSOLETE.user.query('INSERT INTO user_project_role SET ?', data, function (err, results, fields){
161
162
163
164
165
166
            if (err) throw err
            callback(err)
        })
    },
    getVerificationTokenByUserId: function(userId, callback) {
        let token
167
        dbconn_OBSOLETE.user.query('SELECT token FROM verification WHERE user_id = "' +userId+'"', function (err, rows, fields) {
168
169
170
171
172
173
174
175
176
177
178
179
180
            if (err) {
                throw err
            }
            else {
                if (rows.length > 0) {
                    token = rows[0].token
                }
            }
            callback(token, err)
        })
    },
    getUserIdByVerificationToken: function(token, callback) {
        let userId
181
        dbconn_OBSOLETE.user.query('SELECT user_id FROM verification WHERE token = "' +token+'"', function (err, rows, fields) {
182
183
184
185
186
187
188
189
190
191
            if (err) {
                throw err
            }
            else if(rows[0]) {
                userId = rows[0].user_id
            }
            callback(userId, err)
        })
    },
    verifyUserAccount: function(userData, callback) {
192
        dbconn_OBSOLETE.user.beginTransaction(function(err) { // START TRANSACTION
193
194
            if (err) { throw err }
            // update user status
195
            dbconn_OBSOLETE.user.query('UPDATE user SET ? WHERE id =' +userData.id, userData, function (err, rows, fields) {
196
                if (err) {
197
                    return dbconn_OBSOLETE.user.rollback(function() { throw err })
198
199
                }
                // delete verification token
200
                dbconn_OBSOLETE.user.query('DELETE FROM verification WHERE user_id = '+userData.id, function (err, rows, fields) {
201
                    if (err) {
202
                        return dbconn_OBSOLETE.user.rollback(function() { throw err })
203
204
                    }
                    // COMMIT
205
                    dbconn_OBSOLETE.user.commit(function(err) {
206
                        if (err) {
207
                            return dbconn_OBSOLETE.user.rollback(function() { throw err })
208
209
210
211
212
                        }
                    })
                })
            })
            callback(err)
Wolfgang Knopki's avatar
Wolfgang Knopki committed
213
        })
214
215
    },
    /* ===== GitLab ===== */
216
    getGitlabId_OBSOLETE: function(userId, callback){
217
        let gitlabUserId
218
        dbconn_OBSOLETE.user.query('SELECT gu.gitlab_userId FROM user_gitlab gu, user u WHERE u.id = "' +userId+'" and gu.user_id = u.id', function (err, rows) {
219
220
221
222
223
224
225
226
227
            if (err) {
                throw err
            }
            else if(rows[0]) {
                gitlabUserId = rows[0].gitlab_userId
            }
            callback(gitlabUserId, err)
        })
    },
228
229
230
231
232
233
234
235
236
237
    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')
            return rows[0][0].gitlab_userId
        }
        catch(err) {
            console.error(err)
            return err
        }
    },
238
    addGitlabUser: function(data, callback){
239
        dbconn_OBSOLETE.user.query('INSERT INTO user_gitlab SET ?', data, function (err) {
240
241
242
            if (err) throw err
            callback(err)
        })
Rosanny Sihombing's avatar
Rosanny Sihombing committed
243
244
245
246
    }
};

module.exports = methods;