var express = require('express'), app = express(); var mysql = require('mysql'); var properties = require(process.env.SDNC_CONFIG_DIR + '/admportal.json'); var fs = require('fs.extra'); var util = require('util'); var os = require('os'); var async = require('async'); var l_ = require('lodash'); var dns = require('dns'); var dnsSync = require('dns-sync'); var pool = ''; var currentDB = ''; var currentDbName = ''; var fabricDB = properties.dbFabricServer; var dbArray = properties.databases; var enckey = properties.passwordKey; console.log('dbFabric=' + properties.dbFabric); if ( properties.dbFabric == 'true' ) { connectFabric(); } else { initDB(); } exports.dbConnect = function(){ console.log('fabric=' + fabricDB); if ( properties.dbFabric == 'true' ) { connectFabric(); } else { initDB(); } } function setCurrentDbName(){ function createFunction(dbentry) { return function(callback) { findCurrentDbIP(dbentry,callback); } } var tasks = []; for (var x=0; x 0 && currentHost != DBmasterHost ) { // need to copy file so SLA functionality works var source = process.env.SDNC_CONFIG_DIR + "/svclogic.properties." + currentHost; var target = process.env.SDNC_CONFIG_DIR + "/svclogic.properties"; fs.copy(source,target,{replace:true}, function(err){ if(err){ res.render("pages/err", {result:{code:'error', msg:"Unable to copy svclogic.properties. "+ String(err) }}); return; } }); } } */ function initDB( next ) { var tasks = []; for (var x=0; x 1) { res.render("pages/signup", {result:{code:'error', msg:'User Information already exists.'},header:process.env.MAIN_MENU}); return; } sql = "INSERT INTO PORTAL_USERS (email,password,privilege) VALUES (" +"'"+ req.body.nf_email + "'," + "AES_ENCRYPT('" + req.body.nf_password + "','" + enckey + "')," +"'A')"; console.log(sql); connection.query(sql, function(err,result){ connection.release(); if(err){ res.render("pages/signup", {result:{ code:'error', msg:String(err) },header:process.env.MAIN_MENU});; return; } res.render('pages/signup', {result:{code:'success', msg:'User created. Please login.'},header:process.env.MAIN_MENU}); return; }); }); }); } // delete User exports.deleteUser = function(req,res){ var rows={}; var resultObj = { code:'', msg:'' }; var privilegeObj = req.session.loggedInAdmin; pool.getConnection(function(err,connection) { if(err){ console.error( String(err) ); // ALARM res.render("user/list", {rows: null, result:{code:'error', msg:"Unable to get database connection. Error:" + String(err), privilege:privilegeObj },header:process.env.MAIN_MENU}); return; } var sqlUpdate = "DELETE FROM PORTAL_USERS WHERE email='" + req.query.email + "'"; console.log(sqlUpdate); connection.query(sqlUpdate,function(err,result){ if(err){ resultObj = {code:'error', msg:'Delete of user failed Error: '+ String(err) }; } // Need DB lookup logic here connection.query("SELECT email,password,privilege FROM PORTAL_USERS", function(err, rows) { connection.release(); if(!err) { if ( rows.length > 0 ) { resultObj = {code:'success',msg:'Successfully deleted user.'}; res.render('user/list', { rows: rows, result:resultObj, privilege:privilegeObj,header:process.env.MAIN_MENU } ); return; }else{ res.render("user/list", { rows: null, result:{code:'error', msg:'Unexpected no rows returned from database, please try again.', privilege:privilegeObj },header:process.env.MAIN_MENU}); return; } } else { res.render("user/list", { rows: null, result:{code:'error', msg:'Unexpected no rows returned from database. Error: ' + String(err), privilege:privilegeObj },header:process.env.MAIN_MENU}); return; } }); //end query }); }); // end of getConnection } // add User exports.addUser = function(req,res){ var rows={}; var resultObj = { code:'', msg:'' }; var privilegeObj = req.session.loggedInAdmin; pool.getConnection(function(err,connection) { if(err){ console.error( String(err) ); // ALARM res.render("user/list", {rows: null, result:{code:'error', msg:"Unable to get database connection. "+ String(err), privilege:privilegeObj },header:process.env.MAIN_MENU}); return; } if( req.body.nf_privilege == "admin" ){ var char_priv = 'A'; }else if(req.body.nf_privilege == 'readonly'){ var char_priv = 'R'; }else{ var char_priv = 'A'; } //connection.query(sqlRequest, function(err,result){ var sqlUpdate = "INSERT INTO PORTAL_USERS (email, password, privilege) VALUES (" +"'"+ req.body.nf_email + "'," + "AES_ENCRYPT('" + req.body.nf_password + "','" + enckey + "')," +"'"+ char_priv + "')"; console.log(sqlUpdate); connection.query(sqlUpdate,function(err,result){ if(err){ resultObj = {code:'error', msg:'Add of user failed Error: '+err}; } // Need DB lookup logic here connection.query("SELECT email,AES_DECRYPT(password, '" + enckey + "') password,privilege FROM PORTAL_USERS", function(err, rows) { connection.release(); if(!err) { if ( rows.length > 0 ) { resultObj = {code:'success',msg:'Successfully added user.'}; res.render('user/list', { rows: rows, result:resultObj, privilege:privilegeObj,header:process.env.MAIN_MENU } ); return; }else{ res.render("user/list", {rows: null, result:{code:'error', msg:'Unexpected no rows returned from database, please try again.', privilege:privilegeObj },header:process.env.MAIN_MENU}); return; } } else { res.render("user/list", {rows: null, result:{code:'error', msg:'Unexpected no rows returned from database. Error: '+ err , privilege:privilegeObj },header:process.env.MAIN_MENU}); return; } }); //end query }); }); // end of getConnection } // updateUser exports.updateUser= function(req,res){ var rows={}; var resultObj = { code:'', msg:'' }; var privilegeObj = req.session.loggedInAdmin; pool.getConnection(function(err,connection) { if(err){ console.error( String(err) ); // ALARM res.render("user/list", {rows: null, result:{code:'error', msg:"Unable to get database connection. " + String(err), privilege:privilegeObj },header:process.env.MAIN_MENU}); return; } if( req.body.uf_privilege == "admin" ){ var char_priv = 'A'; }else if(req.body.uf_privilege == 'readonly'){ var char_priv = 'R'; }else{ var char_priv = 'A'; } //connection.query(sqlRequest, function(err,result){ var sqlUpdate = "UPDATE PORTAL_USERS SET " + "email = '" + req.body.uf_email + "'," + "password = " + "AES_ENCRYPT('" + req.body.uf_password + "','" + enckey + "'), " + "privilege = '"+ char_priv + "'" + " WHERE email = '" + req.body.uf_key_email + "'"; console.log(sqlUpdate); connection.query(sqlUpdate,function(err,result){ if(err){ resultObj = {code:'error', msg:'Update of user failed Error: '+err}; } // Need DB lookup logic here connection.query("SELECT email, AES_DECRYPT(password,'" + enckey + "') password, privilege FROM PORTAL_USERS", function(err, rows) { connection.release(); if(!err) { if ( rows.length > 0 ) { resultObj = {code:'success',msg:'Successfully updated user.'}; res.render('user/list', { rows: rows, result:resultObj, privilege:privilegeObj,header:process.env.MAIN_MENU} ); return; }else{ res.render("user/list", {rows: null, result:{ code:'error', msg:'Unexpected no rows returned from database.', privilege:privilegeObj },header:process.env.MAIN_MENU}); return; } } else { res.render("user/list", {rows: null, result:{code:'error', msg:'Unexpected no rows returned from database. ' + String(err), privilege:privilegeObj },header:process.env.MAIN_MENU}); return; } }); //end query }); }); // end of getConnection }; exports.listUsers = function(req,res,resultObj){ var privilegeObj = req.session.loggedInAdmin; var rows={}; pool.getConnection(function(err,connection) { if(err){ console.error( String(err) ); // ALARM res.render("pages/list", {rows: null, result:{code:'error', msg:"Unable to get database connection. " + String(err), privilege:privilegeObj },header:process.env.MAIN_MENU}); return; } // Need DB lookup logic here var selectUsers = "SELECT email, AES_DECRYPT(password,'" + enckey + "') password, privilege from PORTAL_USERS"; console.log(selectUsers); connection.query(selectUsers, function(err, rows) { connection.release(); if(err){ resultObj = {code:'error', msg:'Unable to SELECT users Error: '+err}; } if(!err) { if ( rows.length > 0 ) { console.log(JSON.stringify(rows)); res.render('user/list', { rows: rows, result:resultObj, privilege:privilegeObj,header:process.env.MAIN_MENU }); return; } else{ res.render("user/list", {rows: null, result:{code:'error', msg:'Unexpected no rows returned from database.', privilege:privilegeObj },header:process.env.MAIN_MENU}); return; } } else { res.render("user/list", {rows: null, result:{code:'error', msg:'Unexpected no rows returned from database. ' + String(err), privilege:privilegeObj },header:process.env.MAIN_MENU}); return; } }); //end query }); // end getConnection } exports.listSLA = function(req,res,resultObj){ var privilegeObj = req.session.loggedInAdmin; pool.getConnection(function(err,connection) { if(err){ console.error( String(err) ); // ALARM res.render("pages/err", {result:{code:'error', msg:"Unable to get database connection. "+ String(err)},header:process.env.MAIN_MENU}); return; } // Need DB lookup logic here connection.query("SELECT module,rpc,version,mode,active,graph FROM SVC_LOGIC", function(err, rows) { connection.release(); if(err) { res.render("pages/err", {result:{code:'error',msg:'Database Error: '+ String(err)},header:process.env.MAIN_MENU}); return; } else { res.render("sla/list", {rows:rows, result:resultObj, privilege:privilegeObj, header:process.env.MAIN_MENU} ); return; } }); //end query }); // end getConnection } exports.executeSQL = function(sql,req,res,callback){ console.log(sql); pool.getConnection(function(err,connection) { if(err){ console.error( String(err) ); // ALARM callback(err, 'Unable to get database connection.' + err); return; } connection.query(sql, function(err,result){ connection.release(); if (err) { callback(err,'Database operation failed. ' + err ); } else { console.log('affectedRows='+result.affectedRows); callback(null, result.affectedRows); } }); //end query }); // end getConnection } // gamma - deleteParameter exports.deleteParameter = function(req,res,callback){ var sql = "DELETE FROM PARAMETERS WHERE name='" + req.query.name + "'"; console.log(sql); pool.getConnection(function(err,connection) { if(err){ console.log( String(err) ); // ALARM callback(err, 'Unable to get database connection.' + err); return; } connection.query(sql, function(err,result){ connection.release(); if(err){ console.log('Update failed. ' + err ); callback(err,'Update failed. ' + err ); } else { callback(null,''); } }); //end query }); // end getConnection } exports.getTable = function(req,res,sql,rdestination,resultObj,privilegeObj){ console.log('SQL:'+sql); pool.getConnection(function(err,connection) { if(err){ console.error( String(err) ); // ALARM res.render("pages/err", {result:{code:'error', msg:"Unable to get database connection. "+ String(err)},header:process.env.MAIN_MENU}); return; } connection.query(sql,function(err, rows) { connection.release(); if(err) { res.render(rdestination, {result:{code:'error',msg:'Database Error: '+ String(err)},header:process.env.MAIN_MENU}); return; } else { res.render(rdestination, { rows: rows, result:resultObj, privilege:privilegeObj,header:process.env.MAIN_MENU } ); return; } }); //end query }); // end getConnection } exports.getMetaTable = function(req,res,sql,rdestination,resultObj,privilegeObj){ console.log('SQL:'+ sql); var rdata = []; var v_tables = []; var vtables = properties.viewTables; for ( var i in vtables ) { v_tables.push(vtables[i]); } pool.getConnection(function(err,connection) { if(err){ console.error( String(err) ); // ALARM res.render("pages/err", {result:{code:'error', msg:"Unable to get database connection. "+ String(err)},header:process.env.MAIN_MENU}); return; } connection.query(sql,function(err, rows, fields) { console.log('rows:' + JSON.stringify(rows,null,2)); // http://stackoverflow.com/questions/14528385/how-to-convert-json-object-to-javascript-array //logger.debug(Object.keys(rows[0]).map(function(v) { return rows[0][v]; })); for ( var i in rows ){ rdata.push(Object.keys(rows[i]).map(function(v) { return rows[i][v]; })); //logger.debug(Object.keys(rows[i]).map(function(v) { return rows[i][v]; })); //logger.debug([i, rows[i]]); } for ( var x in rdata ){ for ( var j in rdata[x] ){ console.log('rdata[' + x + ']: ' + rdata[x][j]); } } console.log('rdata:' + rdata[0]); console.log('fields:' + JSON.stringify(fields,null,2)); connection.release(); if(err) { res.render(rdestination, {result:{code:'error',msg:'Database Error: '+ String(err)},header:process.env.MAIN_MENU}); return; } else { res.render(rdestination, { displayTable:true, vtables:v_tables, rows:rdata, fields:fields, result:resultObj, privilege:privilegeObj, header:process.env.MAIN_MENU } ); return; } }); //end query }); // end getConnection } exports.getVnfProfile = function(req,res,resultObj,privilegeObj){ pool.getConnection(function(err,connection) { if(err){ console.error( String(err) ); // ALARM res.render("pages/err", {result:{code:'error', msg:"Unable to get database connection. "+ String(err)},header:process.env.MAIN_MENU}); return; } connection.query("SELECT vnf_type,availability_zone_count,equipment_role " + "FROM VNF_PROFILE ORDER BY VNF_TYPE", function(err, rows) { connection.release(); if(err) { res.render("mobility/vnfProfile", {result:{code:'error',msg:'Database Error: '+ String(err)},header:process.env.MAIN_MENU}); return; } else { res.render('mobility/vnfProfile', { rows: rows, result:resultObj, privilege:privilegeObj,header:process.env.MAIN_MENU } ); return; } }); //end query console.log('after query'); }); // end getConnection } exports.getVnfPreloadData = function(req,res,dbtable,callback){ pool.getConnection(function(err,connection) { if(err){ console.error( String(err) ); // ALARM callback(err, 'Unable to get database connection.' + err); return; } // Need DB lookup logic here connection.query("SELECT preload_data FROM " + dbtable + " WHERE id=" + req.query.id, function(err, rows) { connection.release(); if(err) { callback(err); return; } else { var buffer = rows[0].preload_data; var decode_buffer = decodeURI(buffer); var content = JSON.parse(decode_buffer); callback(null,content); return; } }); //end query }); // end getConnection } exports.getVnfNetworkData = function(req,res,resultObj,privilegeObj){ pool.getConnection(function(err,connection) { if(err){ console.error( String(err) ); // ALARM res.render("pages/err", {result:{code:'error', msg:"Unable to get database connection. "+ String(err)},header:process.env.MAIN_MENU}); return; } // Need DB lookup logic here connection.query("SELECT id,svc_request_id,svc_action,status,filename,ts,preload_data " + "FROM PRE_LOAD_VNF_NETWORK_DATA ORDER BY id", function(err, rows) { var msgArray = new Array(); connection.release(); if(err) { msgArray = 'Database Error: '+ String(err); res.render("mobility/vnfPreloadNetworkData", { result:{code:'error',msg:msgArray}, preloadImportDirectory: properties.preloadImportDirectory, header:process.env.MAIN_MENU }); return; } else { var retData = []; for( r=0; r 0 ) { try{ var buffer = rows[r].preload_data; var decode_buffer = decodeURI(buffer); var filecontent = JSON.parse(decode_buffer); rowObj.filecontent = filecontent; rowObj.network_name = filecontent.input["network-topology-information"]["network-topology-identifier"]["network-name"]; rowObj.network_type = filecontent.input["network-topology-information"]["network-topology-identifier"]["network-type"]; } catch(error){ msgArray.push('File ' + rows[r].filename + ' has invalid JSON. Error:' + error); } } else { rowObj.filecontent = ''; } retData.push(rowObj); } if(msgArray.length>0){ resultObj.code = 'failure'; resultObj.msg = msgArray; } res.render('mobility/vnfPreloadNetworkData', { retData:retData, result:resultObj, privilege:privilegeObj, preloadImportDirectory: properties.preloadImportDirectory, header:process.env.MAIN_MENU }); return; } }); //end query }); // end getConnection } exports.getVnfData = function(req,res,resultObj,privilegeObj){ pool.getConnection(function(err,connection) { if(err){ console.error( String(err) ); // ALARM res.render("pages/err", {result:{code:'error', msg:"Unable to get database connection. "+ String(err)},header:process.env.MAIN_MENU}); return; } // Need DB lookup logic here connection.query("SELECT id,svc_request_id,svc_action,status,filename,ts,preload_data " + "FROM PRE_LOAD_VNF_DATA ORDER BY id", function(err, rows) { var msgArray = new Array(); connection.release(); if(err) { msgArray = 'Database Error: '+ String(err); res.render("mobility/vnfPreloadData", { result:{code:'error',msg:msgArray}, preloadImportDirectory: properties.preloadImportDirectory, header:process.env.MAIN_MENU }); return; } else { var retData = []; for( r=0; r 0 ) { try{ var buffer = rows[r].preload_data; var s_buffer = decodeURI(buffer); var filecontent = JSON.parse(s_buffer); rowObj.filecontent = filecontent; rowObj.vnf_name = filecontent.input["vnf-topology-information"]["vnf-topology-identifier"]["vnf-name"]; rowObj.vnf_type = filecontent.input["vnf-topology-information"]["vnf-topology-identifier"]["vnf-type"]; } catch(error){ msgArray.push('File ' + rows[r].filename + ' has invalid JSON. Error:' + error); } } else { rowObj.filecontent = ''; } retData.push(rowObj); } if(msgArray.length>0){ resultObj.code = 'failure'; resultObj.msg = msgArray; } res.render('mobility/vnfPreloadData',{ retData:retData, result:resultObj, privilege:privilegeObj, header:process.env.MAIN_MENU, preloadImportDirectory: properties.preloadImportDirectory }); return; } }); //end query }); // end getConnection } exports.findAdminUser = function(email,res,callback) { var adminUser={}; pool.getConnection(function(err,connection) { if(err){ console.error( String(err) ); // ALARM res.render("pages/login", {result:{code:'error', msg:"Unable to get database connection. "+ String(err)},header:process.env.MAIN_MENU}); return; } // Need DB lookup logic here connection.query("SELECT email, AES_DECRYPT(password, '" + enckey + "') password, privilege FROM PORTAL_USERS WHERE email='" + email + "'", function(err, rows) { connection.release(); if(!err) { if ( rows.length > 0 ) { rows.forEach(function(row){ adminUser = { "email" : row.email, "password" : row.password, "privilege" : row.privilege }; }); callback(adminUser); return; }else{ console.log("no rows returned"); res.render("pages/login", {result:{code:'error', msg:'User is not in database.'},header:process.env.MAIN_MENU}); return; } } else { res.render("pages/err", {result:{code:'error',msg:'Unexpected no rows returned from database. '+ String(err)},header:process.env.MAIN_MENU}); return; } }); //end query }); // end getConnection } exports.addRow = function(sql,req,res,callback){ console.log(sql); pool.getConnection(function(err,connection) { if(err){ console.error( String(err) ); // ALARM callback(err, 'Unable to get database connection.' + err); return; } connection.query(sql, function(err,result){ connection.release(); if(err){ console.debug('Database operation failed. ' + err ); callback(err,'Database operation failed. ' + err ); } else { callback(null, result.affectedRows); } }); //end query }); // end getConnection } exports.addVnfProfile = function(row,res,callback){ var sqlInsert; if ( row.length < 3 ) { console.log('Row [' + row + '] does not have enough fields.'); callback(null, 'Row [' + row + '] does not have enough fields.'); return; } sqlInsert = "INSERT INTO VNF_PROFILE (" + "vnf_type,availability_zone_count,equipment_role) VALUES (" + "'" + row[0] + "'," + row[1] + ",'" + row[2] + "')"; console.log('SQL='+sqlInsert); pool.getConnection(function(err,connection) { if(err){ console.log( String(err) ); // ALARM callback(err, 'Unable to get database connection.'); return; } connection.query(sqlInsert, function(err,result){ connection.release(); if(err){ console.log('Row [' + row + '] failed to insert. ' + err ); callback(null,'Row [' + row + '] failed to insert. ' + err ); } else { callback(null,''); } }); //end query }); // end getConnection } // Add to SVC_LOGIC table exports.addDG = function(_module, version, rpc, mode, xmlfile, req,res){ var privilegeObj = req.session.loggedInAdmin; var rows={}; pool.getConnection(function(err,connection) { if(err){ console.error( String(err) ); // ALARM res.render("pages/err", {result:{code:'error', msg:"Unable to get database connection. "+ String(err)},header:process.env.MAIN_MENU}); return; } var post = { module : _module, rpc : rpc, version : version, mode : mode, active : "N", graph : xmlfile }; //logger.debug( JSON.stringify(post)); //connection.query(sqlRequest, function(err,result){ connection.query('INSERT INTO SVC_LOGIC SET ?', post, function(err,result){ // neat! // Need DB lookup logic here connection.query("SELECT module,rpc,version,mode,active,graph FROM SVC_LOGIC", function(err, rows) { if(!err) { if ( rows.length > 0 ) { res.render('sla/list', { rows: rows, result:{code:'',msg:''}, privilege:privilegeObj,header:process.env.MAIN_MENU } ); return; }else{ console.log("no rows returned"); res.render("pages/home"); return; } } connection.on('error', function(err){ connection.release(); console.log(500, "An error has occurred -- " + err); res.render("pages/home"); return; }); }); //end query connection.release(); }); //connection.query('INSERT INTO SVC_LOGIC SET ?', post, function(err,result){ // neat! //logger.debug('inserted rows'); //}); //if(err){ //res.render('pages/home'); //} return; }); // end of getConnection }; exports.activate = function(req,res,_module,rpc,version,mode,callback){ var sql = "UPDATE SVC_LOGIC SET active=\'Y\' WHERE module=\'" + _module + "' AND rpc=\'" + rpc + "' AND version=\'" + version + "' AND mode=\'" + mode + "'"; console.log('SQL='+sql); pool.getConnection(function(err,connection) { if(err){ console.error( String(err) ); // ALARM callback(err, 'Unable to get database connection.' + err); return; } connection.query(sql, function(err,result){ connection.release(); if(err){ callback(err, 'Unable to get database connection.' + err); } else { callback(null,''); } }); //end query }); // end getConnection } exports.deactivate = function(req,res,_module,rpc,version,mode,callback){ var sql = "UPDATE SVC_LOGIC SET active=\'N\' WHERE module=\'" + _module + "' AND rpc=\'" + rpc + "' AND version=\'" + version + "' AND mode=\'" + mode + "'"; console.log('SQL='+sql); pool.getConnection(function(err,connection) { if(err){ console.error( String(err) ); // ALARM callback(err, 'Unable to get database connection.' + err); return; } connection.query(sql, function(err,result){ connection.release(); if(err){ callback(err, 'Unable to get database connection.' + err); } else { callback(null,''); } }); //end query }); // end getConnection } exports.global_deactivate = function(req,res,_module,rpc,mode,callback){ var sql = "UPDATE SVC_LOGIC SET active=\'N\' WHERE module=\'" + _module + "' AND rpc=\'" + rpc + "' AND mode=\'" + mode + "'"; pool.getConnection(function(err,connection) { if(err){ callback(err, 'Unable to get database connection.' + err); return; } connection.query(sql, function(err,result){ connection.release(); if(err){ callback(err, err); } else { callback(null,''); } }); //end query }); // end getConnection } exports.deleteDG = function(req,res,_module,rpc,version,mode,callback){ var sql = "DELETE FROM SVC_LOGIC WHERE module=\'" + _module + "' AND rpc=\'" + rpc + "' AND version=\'" + version + "' AND mode=\'" + mode + "'"; console.log('SQL='+sql); pool.getConnection(function(err,connection) { if(err){ console.error( String(err) ); // ALARM callback(err, 'Unable to get database connection.' + err); return; } connection.query(sql, function(err,result){ connection.release(); if(err){ callback(err, 'Unable to get database connection.' + err); } else { callback(null,''); } }); //end query }); // end getConnection } function padLeft(nr, n, str){ return Array(n-String(nr).length+1).join(str||'0')+nr; }