/*made by Miguel Rodrigues @ KBZ miguel.rodrigues@knowledgebiz.pt*/
var mysql = require('mysql');
var db = require ('../config/db');
var token = require ('../backend/token/tokenGenerator');
var dbpool = require ('../backend/server');
var email = require('./emailer');
var logger = require('../config/logger.js');
function handlerDB (query, cb){
dbpool.getConnection(function(err, conn){
if(err){
logger.error("handlerDB",err);
cb(true, err);
}
logger.info("handlerDB","connected as id ", conn.threadId);
conn.query(query,function(err, rows){
conn.release();
if(!err){
cb(false, rows)
}
})
conn.on('error', function(err){
logger.error("handlerDB","error on database connection");
})
})
}
/*
Insert vApp
*/
module.exports.insertVapp= function(body, callback){
var response = [];
var bool = false;
body.appID.forEach(function(eachBody, bodyIndex){
var sqlQuery = "SELECT appID FROM registration where appID = '"+eachBody+"'";
handlerDB(sqlQuery, function(err, result){
if (err){
callback(false, err);
}
else{
if(result.length == 0 && eachBody != "null"){
var newToken = token.token_generator();
var sqlQuery = "INSERT INTO registration ( token, appID, developerID) VALUES ('"+newToken+"','"+eachBody+"','"+body.developerID[bodyIndex]+"')";
handlerDB(sqlQuery, function(err, result){
if(err){
callback(false, err);
}else{
email.sendEmailToken(body.developerID[bodyIndex], newToken);
response.push({token: newToken});
bool = true;
if(bodyIndex == body.appID.length - 1){
callback(true,response);
}
}
})
}
else{
response.push({token: "App is already registered or AppID is Null"});
if(bodyIndex == body.appID.length - 1){
if(bool == true){
callback(true,response);
}else{
callback(false,response);
}
}
}
}
})
})
}
/*
Insert Notifications
*/
module.exports.insertNotification= function(data, callback){
strSubject = data.subject + " = " + data.subjectValue;
var sqlQuery = "INSERT INTO notificationlist ( subject, token, rulesID) VALUES ('"+strSubject+"', '"+data.token+"', '"+data.rulesID+"')";
handlerDB(sqlQuery, function(err, result){
if (err){
callback(false,err);
}
else{
callback(true, result);
}
})
}
/*
Insert Rules
*/
module.exports.insertNotificationRules= function(body, callback){
console.log("body.emailTo.length: " + JSON.stringify(body));
console.log("body.emailTo: " + body.emailTo[0]);
var destinations ="";
for(i = 0; i < body.emailTo.length; i++){
if(body.emailTo[i] == null && body.emailTo.length == 1){
destinations = null;
}else{
if(body.emailTo[i] != null){
destinations += body.emailTo[i];
if(i < body.emailTo.length - 1 && body.emailTo.length != 1){
destinations += ", "
}
}
}
}
if(body.hasOwnProperty('token')){
var sqlQuery = "INSERT INTO ruleslist ( description, parameter, conditionValue, controlValue, threshold, notifyType, emailTo, notificationType, hostname, port, path, method, token) VALUES ('"+body.description+"','"+body.parameter+"','"+body.conditionValue+"','"+body.controlValue+"','"+body.threshold+"','"+body.notifyType+"','"+destinations+"','"+body.notificationType+"','"+body.hostname+"','"+body.port+"','"+body.path+"','"+body.method+"','"+body.token+"')";
handlerDB(sqlQuery, function(err, result){
if (err){
callback(false,err);
}else {
callback(true);
}
});
}else{
var sql = "SELECT token FROM registration where appID = '"+body.appID+"'";
handlerDB(sql, function(err, res){
if (err){
callback(false,err);
}else{
var sqlQuery2 = "INSERT INTO ruleslist ( description, parameter, conditionValue, controlValue, threshold, notifyType, emailTo, notificationType, hostname, port, path, method, token) VALUES ('"+body.description+"','"+body.parameter+"','"+body.conditionValue+"','"+body.controlValue+"','"+body.threshold+"','"+body.notifyType+"','"+destinations+"','"+body.notificationType+"','"+body.hostname+"','"+body.port+"','"+body.path+"','"+body.method+"','"+res[0].token+"')";
console.log(sqlQuery2);
handlerDB(sqlQuery2, function(err, result){
if (err){
callback(false,err);
}
else {
callback(true);
}
});
}
})
}
}
/*
Insert Statistics
*/
module.exports.insertStatistics = function(result, subjectValue, subject, rulesid, callback){
var sqlQuery = "INSERT INTO statisticslist ( result, subjectValue, subject, rulesID) VALUES ('"+result+"', '"+subjectValue+"', '"+subject+"', '"+rulesid+"')";
handlerDB(sqlQuery, function(err, result){
if (err) {
logger.info("Update Values for Statistics: " + err);
callback(false,err);
}
else{
logger.info("Update Values for Statistics: Success");
callback(true, subject);
}
})
}
/*
Retrieve vApp
*/
module.exports.retrieveApp= function(developerid,callback){
var sqlQuery = "SELECT * FROM registration where developerID = '"+developerid+"'";
handlerDB(sqlQuery, function(err,result){
if (err){
callback(false,err);
}
else {
if(result.length == 0){
callback(false,"DeveloperID does not exist");
}
else{
callback(true,result);
}
}
})
}
module.exports.getNotificationsByListId = function(IDArray, cb){
handlerDB("select * from notificationlist where notificationID IN "+IDArray,function(err,res){
if(err){
cb(true, err)
}else{
cb(false, res)
}
})
}
/*
Retrieve Notifications by appid
*/
module.exports.retrieveNotificationList= function(appid,callback){
var sql = "SELECT token FROM registration where appID = '"+appid+"'";
handlerDB(sql, function(err, res){
if (err){
callback(false,err);
}
else{
if(res.length == 0){
callback(false,"AppID incorrect");
}
else{
var sqlQuery = "SELECT * FROM notificationlist where token = '"+res[0].token+"'";
handlerDB(sqlQuery, function(err, rows){
if (err){
callback(false,err);
}
else {
callback(true,rows);
}
})
}
}
})
}
/*
Retrieve Rules
*/
module.exports.retrieveRulesList= function(appid,callback){
var sql = "SELECT token FROM registration where appID = '"+appid+"'";
handlerDB(sql, function(err, res){
if (err){
callback(false,err);
}
else{
if(res.length == 0){
callback(false,"AppID incorrect");
}
else{
var sqlQuery = "SELECT * FROM ruleslist where token = '"+res[0].token+"'";
handlerDB(sqlQuery, function(err, rows){
if (err){
callback(false,err);
}
else{
callback(true,rows);
}
})
}
}
})
}
/*
Retrieve Statistics
*/
module.exports.retrieveStatisticsList = function(ruleid, callback){
var handler = "retrieveStatisticsList";
var totalNotifications = 0;
var totalNotificationsApplyByRules = 0;
var totalValue = 0;
var averageValue = null;
var percentage = null;
var sqlQuery = "SELECT * FROM statisticslist where rulesID = '"+ruleid+"'";
handlerDB(sqlQuery, function(err, rows){
if (err) {
logger.error(handler,err);
callback(false,err);
}
else{
totalNotifications = rows.length;
if(totalNotifications == 0){
totalNotifications = 0;
totalValue = 0;
totalNotificationsApplyByRules = 0;
averageValue = 0.0;
percentage = 0.0;
}else{
for (var i = 0; i < totalNotifications; i++){
if(rows[i].result == "true"){
totalNotificationsApplyByRules++;
totalValue = parseFloat(totalValue + parseFloat(rows[i].subjectValue));
}
else{
totalValue = parseFloat(totalValue + parseFloat(rows[i].subjectValue));
}
}
averageValue = (totalValue / totalNotifications).toFixed(2);
percentage = ((parseInt(totalNotificationsApplyByRules) * 100 ) / parseInt(totalNotifications)).toFixed(2);
}
callback(true, rows, {totalNotifications: totalNotifications, totalNotificationsApplyByRules: totalNotificationsApplyByRules, averageValue: averageValue, percentage: percentage});
}
})
}
/*
Get Rules by Token
*/
module.exports.getRulesListByToken = function(token, callback){
var sqlQuery = "SELECT * FROM ruleslist where token = '"+token+"'";
handlerDB(sqlQuery, function(err, ruleslist){
if (err) {
callback(false,err);
}
else{
callback(true, ruleslist);
}
})
}
/*
Get Rules by Token
*/
module.exports.getEmailTovAppByToken = function(token, callback){
var sqlQuery = "SELECT developerID FROM registration where token = '"+token+"'";
handlerDB(sqlQuery, function(err, developer){
if (err) {
callback(false,err);
}
else{
callback(true, developer);
}
})
}
/*
Get Rules by RulesID
*/
module.exports.getRulesListByRulesID = function(rulesID, callback){
var sqlQuery = "SELECT * FROM ruleslist where rulesID = '"+rulesID+"'";
handlerDB(sqlQuery, function(err, ruleslist){
if (err) {
callback(false,err);
}
else{
callback(true, ruleslist);
}
})
}
/*
Get All Rules
*/
module.exports.getRulesList = function(callback){
var sqlQuery = "SELECT * FROM ruleslist";
handlerDB(sqlQuery, function(err, ruleslist){
if (err) {
callback(false,err);
}
else{
callback(true, ruleslist);
}
})
}
/*
Check If Token Exists
*/
module.exports.checkIfTokenExists = function(token, callback){
var sql = "SELECT token FROM registration where token = '"+token+"'";
handlerDB(sql, function(err, res){
if (err){
callback(false);
}
else{
if(res.length == 1){
callback(true, res);
}
else{
callback(false, "Invalid Token");
}
}
})
}
/*
Check If Rule Have Automatic Rule Created by System
*/
module.exports.checkIfRuleAutomaticExists = function(rule, callback){
var aux = false;
var sql = "SELECT description FROM ruleslist";
handlerDB(sql, function(err, res){
if (err){
callback(false);
}
else{
for(var i = 0; i < res.length; i++){
if(res[i].description.search("System Create Automatically New rule: " + rule.description) > -1){
aux = true;
}
}
}
if(aux == false){
callback(false);
}else{
callback(true);
}
})
}
/*
Delete vApp and Rules
*/
module.exports.deleteVapp= function(appid, callback){
var sql = "SELECT token FROM registration where appID = '"+appid+"'";
handlerDB(sql, function(err, res){
if (err){
callback(false,err);
}
else{
if(res.length == 0){
callback(false,"AppID incorrect");
}
else{
var sqlNotifcations = "DELETE FROM notificationlist where token = '"+res[0].token+"'";
handlerDB(sqlNotifcations, function(err, rows){
if (err) {
callback(false,err);
}
else{
var sqlRules = "DELETE FROM ruleslist where token = '"+res[0].token+"'";
handlerDB(sqlRules, function(err, rows){
if (err) {
callback(false,err);
}
else{
var sqlQuery = "DELETE FROM registration where token = '"+res[0].token+"'";
handlerDB(sqlQuery, function (err, rows) {
if (err){
callback(false,err);
}
else{
callback(true);
}
});
}
})
}
})
}
}
})
}
/*
Delete Rule
*/
module.exports.deleteRule= function(rulesid, callback){
var sql = "SELECT rulesID FROM ruleslist where rulesID = '"+rulesid+"'";
handlerDB(sql, function(err, res){
if (err){
callback(false,err);
}
else{
if(res.length == 0){
callback(false,"RuleID incorrect");
}else{
var sqlRules = "DELETE FROM statisticslist where rulesID = '"+rulesid+"'";
handlerDB(sqlRules, function(err, res){
if (err) {
callback(false,err);
}
else{
var sqlQuery = "DELETE FROM ruleslist where rulesID = '"+rulesid+"'";
handlerDB(sqlQuery, function(err, rows){
if (err) {
callback(false,err);
}
else{
callback(true);
}
});
}
})
}
}
})
}
/*
Update Rule
*/
module.exports.updateRule= function(data, callback){
var sqlQuery = "UPDATE ruleslist SET description = '"+data.description+"', parameter = '"+data.parameter+"', conditionValue = '"+data.conditionValue+"', controlValue = '"+data.controlValue+"', threshold = '"+data.threshold+"', notificationType = '"+data.notificationType+"' where rulesID = '"+data.rulesid+"'";
handlerDB(sqlQuery, function(err, result){
if (err){
callback(false,err);
}
else{
callback(true, result);
}
})
}
/*
Get All Parameters on Specific Rule
*/
module.exports.retrieveAllRule= function(RuleID, callback){
var sqlQuery = "SELECT * FROM ruleslist where rulesID = '"+RuleID+"'";
handlerDB(sqlQuery, function(err, result){
if (err){
callback(false,err);
}
else{
callback(true, result);
}
})
}