/*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 sqlQuery = "SELECT appID FROM registration where appID = '"+body.appID+"'";
handlerDB(sqlQuery, function(err, result){
if (err){
callback(false,err);
}
else{
if(result.length == 0 && body.appID != "null"){
var newToken = token.token_generator();
var sqlQuery = "INSERT INTO registration ( token, appID, developerID) VALUES ('"+newToken+"','"+body.appID+"','"+body.developerID+"')";
handlerDB(sqlQuery, function(err, result){
if(err){
callback(false, result)
}else{
email.sendEmailToken(body.developerID, newToken);
callback(true,{token: newToken});
}
})
}
else{
callback(false,"App is already registered or AppID is Null");
}
}
})
}
/*
Insert Notifications
*/
module.exports.insertNotification= function(data, callback){
strSubject = data.subject + " = " + data.subjectValue;
var sqlQuery = "INSERT INTO notificationlist ( emailTo, subject, token, ntype) VALUES ('"+data.emailTo+"','"+strSubject+"', '"+data.token+"', "+parseInt(data.ntype)+")";
handlerDB(sqlQuery, function(err, result){
if (err){
callback(false,err);
}
else{
callback(true, result);
}
})
}
/*
Insert Rules
*/
module.exports.insertNotificationRules= function(body, callback){
var sql = "SELECT token FROM registration where appID = '"+body.appID+"'";
handlerDB(sql, function(err, res){
if (err){
callback(false,err);
}
else{
var sqlQuery = "INSERT INTO ruleslist ( description, parameter, conditionValue, controlValue, notificationType, token) VALUES ('"+body.description+"','"+body.parameter+"','"+body.conditionValue+"','"+body.controlValue+"','"+body.notificationType+"','"+res[0].token+"')";
handlerDB(sqlQuery, 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);
}
})
}
/*
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");
}
}
})
}
/*
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, rows);
}
});
}
})
}
})
}
}
})
}
/*
Delete Rule
*/
module.exports.deleteRule= function(rulesid, callback){
var sqlQuery = "DELETE FROM ruleslist where rulesID = '"+rulesid+"'";
handlerDB(sqlQuery, function(err, rows){
if (err) {
callback(false,err);
}
else{
if(rows.length == 0){
callback(false,"RuleID incorrect");
}
else{
callback(true,rows);
}
}
})
}
/*
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+"', 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);
}
})
}