methods.js

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
                            });
                        }
                        // MLAB-129: INSERT verification token
                        let verificationData = {
                            user_id: newUserId,
                            token: data.verificationToken
                        }
                        dbconn.user.query('INSERT INTO verification SET ?', verificationData, 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 verificationStatus, 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) {
        let 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) {
        let 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);
            }
        );
    },
	updateUserById: function(userData, callback) {
		dbconn.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.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) {
        let 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)
        })
    },
    getVerificationTokenByUserId: function(userId, callback) {
        let token
        dbconn.user.query('SELECT token FROM verification WHERE user_id = "' +userId+'"', function (err, rows, fields) {
            if (err) {
                throw err
            }
            else {
                if (rows.length > 0) {
                    token = rows[0].token
                }
            }
            callback(token, err)
        })
    },
    getUserIdByVerificationToken: function(token, callback) {
        let userId
        dbconn.user.query('SELECT user_id FROM verification WHERE token = "' +token+'"', function (err, rows, fields) {
            if (err) {
                throw err
            }
            else if(rows[0]) {
                userId = rows[0].user_id
            }
            callback(userId, err)
        })
    },
    verifyUserAccount: function(userData, callback) {
        dbconn.user.beginTransaction(function(err) { // START TRANSACTION
            if (err) { throw err }
            // update user status
            dbconn.user.query('UPDATE user SET ? WHERE id =' +userData.id, userData, function (err, rows, fields) {
                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, rows, 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)
        })
    },
    // ======================= 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;