admportal/server/router/routes/dbRoutes.js

   1 var express = require('express'),
   2     app = express();
   3 var mysql = require('mysql');
   4 var properties = require(process.env.SDNC_CONFIG_DIR + '/admportal.json');
   5 var fs = require('fs.extra');
   6 var util = require('util');
   7 var os = require('os');
   8 var async = require('async');
   9 var l_ = require('lodash');
  10 var dns = require('dns');
  11 var dnsSync = require('dns-sync');
  12
  13 var pool = '';
  14 var currentDB = '';
  15 var currentDbName = '';
  16 var fabricDB = properties.dbFabricServer;
  17 var dbArray = properties.databases;
  18 var enckey = properties.passwordKey;
  19
  20 console.log('dbFabric=' + properties.dbFabric);
  21
  22 if ( properties.dbFabric == 'true' )
  23 {
  24         connectFabric();
  25 }
  26 else
  27 {
  28     initDB();
  29 }
  30
  31
  32 exports.dbConnect = function(){
  33
  34         console.log('fabric=' + fabricDB);
  35         if ( properties.dbFabric == 'true' )
  36         {
  37                 connectFabric();
  38         }
  39         else
  40         {
  41         initDB();
  42         }
  43 }
  44
  45 function setCurrentDbName(){
  46
  47         function createFunction(dbentry)
  48         {
  49                 return function(callback) { findCurrentDbIP(dbentry,callback); }
  50         }
  51
  52         var tasks = [];
  53         for (var x=0; x<dbArray.length; x++){
  54                 var dbElement = dbArray[x];
  55                 var dbElementArray = dbElement.split("|");
  56
  57                 tasks.push( createFunction(dbElement) );
  58         }
  59         async.series(tasks, function(err,result){
  60
  61                 if(err){
  62                         currentDbName = err;
  63                         console.log('currentDbName: ' + err);
  64             return;
  65         }
  66         else {
  67                         console.log('not found');
  68             return;
  69         }
  70         });
  71 }
  72
  73
  74 function findCurrentDbIP(dbElement, callback){
  75
  76         var dbElementArray = dbElement.split("|");
  77
  78         dns.lookup( dbElementArray[0], function onLookup(err, addresses, family) {
  79
  80         if ( currentDB == addresses ){
  81                 callback(dbElementArray[1]);
  82             return;
  83         }
  84                 else {
  85                 callback(null);
  86                 return;
  87                 }
  88     });
  89 }
  90
  91
  92 exports.getCurrentDB = function(){
  93         return currentDbName;
  94 }
  95
  96
  97 exports.testdb = function(req,res,callback){
  98 console.log('testdb');
  99
 100         osObj = {
 101         'hostname' : os.hostname(),
 102         'type'     : os.type(),
 103         'platform' : os.platform(),
 104         'arch'     : os.arch(),
 105         'release'  : os.release(),
 106         'uptime'   : os.uptime(),
 107         'totalmem' : os.totalmem(),
 108         'dbhealth' : ''
 109     };
 110
 111     pool.getConnection(function(err,connection)
 112         {
 113         if(err){
 114             callback(err);
 115             return;
 116         }
 117
 118                 // http://stackoverflow.com/questions/10982281/mysql-connection-validity-test-in-datasource-select-1-or-something-better
 119                 connection.query("/* pint */ SELECT 1", function(err,result){
 120
 121             connection.release();
 122             if(err) {
 123                                 callback(err);
 124                 return;
 125             }
 126                         callback(null,'Database Connectivity to ' + currentDB + ' is working.');
 127             return;
 128         }); //end query
 129     }); // end getConnection
 130 }
 131
 132 /*
 133 exports.checkSvcLogic = function(req,res){
 134
 135         if ( DBmasterHost.length > 0 && currentHost != DBmasterHost )
 136         {
 137                  // need to copy file so SLA functionality works
 138          var source = process.env.SDNC_CONFIG_DIR
 139              + "/svclogic.properties." + currentHost;
 140          var target = process.env.SDNC_CONFIG_DIR
 141              + "/svclogic.properties";
 142          fs.copy(source,target,{replace:true}, function(err){
 143                 if(err){
 144                 res.render("pages/err",
 145                         {result:{code:'error',
 146                               msg:"Unable to copy svclogic.properties. "+ String(err) }});
 147                 return;
 148             }
 149          });
 150         }
 151 }
 152 */
 153 function initDB( next ) {
 154
 155
 156         var tasks = [];
 157         for (var x=0; x<properties.databases.length; x++){
 158
 159                 var db = properties.databases[x];
 160                 var dbArray = db.split("|");
 161                 var _dbIP = dnsSync.resolve(dbArray[0]);
 162                 var _dbName = dbArray[1];
 163
 164         tasks.push( createFindMasterFunctionObj(_dbIP, _dbName) );
 165         }
 166     async.series(tasks, function(err,result)
 167     {
 168         if(err){
 169                         if ( err == 'found' ){
 170                                 if ( typeof next != 'undefined'){
 171                                         next();
 172                                 }
 173                                 else {
 174                                         return;
 175                                 }
 176                         }
 177                         else {
 178                 console.error( String(err) ); // ALARM
 179                 return;
 180                         }
 181         }
 182                 console.log('result=' + result);
 183         });
 184         return;
 185 }
 186
 187
 188 function createFindMasterFunctionObj(dbIP,dbName){
 189         return function(callback) { findMaster(dbIP, dbName, callback); }
 190 }
 191
 192 function findMaster (ldbIP, ldbName, callback){
 193 var dbIP = ldbIP;
 194 var dbName = ldbName;
 195
 196         console.log('checking dbIP:' + dbIP);
 197
 198     pool = mysql.createPool({
 199         connectionLimit : properties.dbConnLimit,
 200         host            : dbIP,
 201         user            : properties.dbUser,
 202         password        : properties.dbPassword,
 203         database        : properties.dbName,
 204         multipleStatements: true,
 205         debug           : false
 206     });
 207
 208         pool.getConnection(function(err,connection){
 209
 210         if(err){
 211                         callback( String(err) );
 212             return;
 213         }
 214         var sql = 'select @@read_only';
 215         connection.query(sql, function(err,result){
 216             connection.release();
 217
 218             // @@read_only=0 means db is writeable
 219             console.log('@@read_only=' + result[0]['@@read_only']);
 220             if ( result[0]['@@read_only'] == '0' )
 221             { // writeable
 222                                 // if this is not the current DB, make it since its writeable
 223                                         currentDB = dbIP;
 224                                         currentDbName = dbName;
 225                                         console.log('currentDB=' + currentDB + "|" + currentDbName);
 226                         var newpool = mysql.createPool({
 227                                 connectionLimit : properties.dbConnLimit,
 228                                 host            : currentDB,
 229                                 user            : properties.dbUser,
 230                                 password        : properties.dbPassword,
 231                                 database        : properties.dbName,
 232                                 multipleStatements: true,
 233                                 debug           : false
 234                         }); // end create
 235                         pool = newpool;
 236                                         callback('found', currentDB);
 237                                         return;
 238                         }
 239                 // otherwise this is the current db and its writeable, just return
 240                 callback(null, currentDB);
 241                 return;
 242         });
 243     });
 244 }
 245
 246 exports.checkDB = function(req,res,next){
 247
 248 console.log('checkDB');
 249
 250
 251         if ( properties.dbFabric == 'true' )
 252         {
 253                 connectFabric();
 254                 next();
 255         }
 256         else
 257         {
 258                 initDB( next );
 259         }
 260 }
 261
 262
 263 exports.saveUser = function(req,res){
 264
 265         var tkn = req.csrfToken();
 266         var email = req.sanitize(req.body.nf_email);
 267         var pswd = req.sanitize(req.body.nf_password);
 268
 269         pool.getConnection(function(err,connection)
 270         {
 271                 if(err){
 272                         console.error( String(err) ); // ALARM
 273                         res.render("pages/signup", {csrfToken:tkn,result:{code:'error', msg:"Unable to get database connection. " + String(err)},header:process.env.MAIN_MENU});
 274                         return;
 275                 }
 276                 var sql = "SELECT email FROM PORTAL_USERS WHERE email=" + connection.escape(email);
 277
 278                 connection.query(sql, function(err,result)
 279                 {
 280                         if(err){
 281                                 connection.release();
 282                                 res.render("pages/signup", {csrfToken:tkn, result:{code:'error', msg:"Unable to get database connection. " + String(err)},header:process.env.MAIN_MENU});
 283                                 return;
 284                         }
 285                         if (result.length == 1 || result.length > 1)
 286                         {
 287                                 connection.release();
 288                                 res.render("pages/signup", {csrfToken:tkn, result:{code:'error', msg:'User Information already exists.'},header:process.env.MAIN_MENU});
 289                                 return;
 290                         }
 291                         sql = "INSERT INTO PORTAL_USERS (email,password,privilege) VALUES ("
 292             + connection.escape(email) + ","
 293             + "AES_ENCRYPT(" + connection.escape(pswd) + ",'" + enckey + "'),'A')";
 294
 295                         connection.query(sql, function(err,result)
 296                         {
 297                                 connection.release();
 298
 299                                 if(err){
 300                                         res.render("pages/signup", {csrfToken:tkn, result:{ code:'error', msg:String(err) },header:process.env.MAIN_MENU});;
 301                                         return;
 302                                 }
 303                                 res.render('pages/signup', {csrfToken:tkn, result:{code:'success', msg:'User created.  Please login.'},header:process.env.MAIN_MENU});
 304                                 return;
 305                         });
 306                 });
 307         });
 308 }
 309
 310 // delete User
 311 exports.deleteUser = function(req,res){
 312
 313         var rows={};
 314         var resultObj = { code:'', msg:'' };
 315         var privilegeObj = req.session.loggedInAdmin;
 316
 317   pool.getConnection(function(err,connection) {
 318
 319     if(err){
 320                         console.error( String(err) ); // ALARM
 321       res.render("user/list", {rows: null, result:{code:'error', msg:"Unable to get database connection. Error:" + String(err),
 322                         privilege:privilegeObj },header:process.env.MAIN_MENU});
 323                         return;
 324     }
 325
 326     var sqlUpdate = "DELETE FROM PORTAL_USERS WHERE email=" + connection.escape(req.query.email);
 327                 console.log(sqlUpdate);
 328
 329     connection.query(sqlUpdate,function(err,result){
 330
 331       if(err){
 332         resultObj = {code:'error', msg:'Delete of user failed Error: '+ String(err) };
 333       }
 334
 335       // Need DB lookup logic here
 336       connection.query("SELECT email,password,privilege FROM PORTAL_USERS", function(err, rows) {
 337         connection.release();
 338         if(!err)
 339                                 {
 340           if ( rows.length > 0 )
 341           {
 342                                                 resultObj = {code:'success',msg:'Successfully deleted user.'};
 343             res.render('user/list', { rows: rows, result:resultObj, privilege:privilegeObj,header:process.env.MAIN_MENU } );
 344                                                 return;
 345           }else{
 346             res.render("user/list", { rows: null, result:{code:'error', msg:'Unexpected no rows returned from database, please try again.',
 347                                                 privilege:privilegeObj },header:process.env.MAIN_MENU});
 348                                                 return;
 349           }
 350         }
 351                                 else {
 352           res.render("user/list", { rows: null, result:{code:'error', msg:'Unexpected no rows returned from database. Error: ' + String(err),
 353                                         privilege:privilegeObj },header:process.env.MAIN_MENU});
 354                                         return;
 355         }
 356       }); //end query
 357     });
 358   }); // end of getConnection
 359 }
 360
 361 // add User
 362 exports.addUser = function(req,res){
 363
 364         var rows={};
 365         var resultObj = { code:'', msg:'' };
 366         var privilegeObj = req.session.loggedInAdmin;
 367         var privilege = req.sanitize(req.body.nf_privilege);
 368         var email = req.sanitize(req.body.nf_email);
 369   var pswd = req.sanitize(req.body.nf_password);
 370
 371
 372         pool.getConnection(function(err,connection)
 373         {
 374                 if(err)
 375                 {
 376                         console.error( String(err) ); // ALARM
 377                         res.render("user/list", {rows: null, result:{code:'error', msg:"Unable to get database connection. "+ String(err),
 378                         privilege:privilegeObj },header:process.env.MAIN_MENU});
 379                         return;
 380                 }
 381
 382                 if( privilege == "admin" ){
 383                         var char_priv = 'A';
 384                 }else if(privilege == 'readonly'){
 385                         var char_priv = 'R';
 386                 }else{
 387                         var char_priv = 'R';
 388                 }
 389
 390                 //connection.query(sqlRequest, function(err,result)
 391                 var sqlUpdate = "INSERT INTO PORTAL_USERS (email, password, privilege) VALUES ("
 392                         + connection.escape(email) + ","
 393                         + "AES_ENCRYPT(" + connection.escape(pswd) + ",'" + enckey + "'),"
 394                         + "'" + char_priv + "')";
 395
 396
 397                 connection.query(sqlUpdate,function(err,result)
 398                 {
 399                         if(err){
 400                                 resultObj = {code:'error', msg:'Add of user failed Error: '+err};
 401                         }
 402                         // Need DB lookup logic here
 403                         connection.query("SELECT email,AES_DECRYPT(password, '" + enckey + "') password,privilege FROM PORTAL_USERS", function(err, rows)
 404                         {
 405                                 connection.release();
 406                                 if(!err)
 407                                 {
 408                                         if ( rows.length > 0 )
 409                                         {
 410                                                 resultObj = {code:'success',msg:'Successfully added user.'};
 411                                                 res.render('user/list', { rows: rows, result:resultObj, privilege:privilegeObj,header:process.env.MAIN_MENU } );
 412                                                 return;
 413                                         }else{
 414                                                 res.render("user/list", {rows: null, result:{code:'error', msg:'Unexpected no rows returned from database, please try again.',
 415                                                         privilege:privilegeObj },header:process.env.MAIN_MENU});
 416                                                 return;
 417                                         }
 418                                 }
 419                                 else {
 420                                         res.render("user/list", {rows: null, result:{code:'error', msg:'Unexpected no rows returned from database. Error: '+ err ,
 421                                                 privilege:privilegeObj },header:process.env.MAIN_MENU});
 422                                         return;
 423                                 }
 424                         }); //end query
 425                 });
 426         }); // end of getConnection
 427 }
 428
 429 // updateUser
 430 exports.updateUser= function(req,res){
 431
 432         var rows={};
 433         var resultObj = { code:'', msg:'' };
 434         var privilegeObj = req.session.loggedInAdmin;
 435         var email = req.sanitize(req.body.uf_email);
 436         var key_email = req.sanitize(req.body.uf_key_email)
 437   var pswd = req.sanitize(req.body.uf_password);
 438   var privilege = req.sanitize(req.body.uf_privilege);
 439
 440         pool.getConnection(function(err,connection)
 441         {
 442                 if(err){
 443                         console.error( String(err) ); // ALARM
 444                         res.render("user/list", {rows: null, result:{code:'error', msg:"Unable to get database connection. " + String(err),
 445                                 privilege:privilegeObj },header:process.env.MAIN_MENU});
 446                         return;
 447                 }
 448
 449                 if( privilege == "admin" ){
 450                         var char_priv = 'A';
 451                 }else if(privilege == 'readonly'){
 452                         var char_priv = 'R';
 453                 }else{
 454                         var char_priv = 'R';
 455                 }
 456
 457                 var sqlUpdate = "UPDATE PORTAL_USERS SET "
 458                         + "email = " + connection.escape(email) + ","
 459                         + "password = " + "AES_ENCRYPT(" + connection.escape(pswd) + ",'" + enckey + "'), "
 460                         + "privilege = '" + char_priv + "'"
 461                         + " WHERE email = " + connection.escape(key_email);
 462
 463                 console.log(sqlUpdate);
 464                 connection.query(sqlUpdate,function(err,result)
 465                 {
 466                         if(err){
 467                                 resultObj = {code:'error', msg:'Update of user failed Error: '+err};
 468                         }
 469                         // Need DB lookup logic here
 470                         connection.query("SELECT email, AES_DECRYPT(password,'" + enckey + "') password, privilege FROM PORTAL_USERS", function(err, rows)
 471                         {
 472                                 connection.release();
 473                                 if(!err)
 474                                 {
 475                                         if ( rows.length > 0 )
 476                                         {
 477                                                 resultObj = {code:'success',msg:'Successfully updated user.'};
 478                                                 res.render('user/list', { rows: rows, result:resultObj, privilege:privilegeObj,header:process.env.MAIN_MENU} );
 479                                                 return;
 480                                         }else{
 481                                                 res.render("user/list", {rows: null, result:{ code:'error', msg:'Unexpected no rows returned from database.',
 482                                                         privilege:privilegeObj },header:process.env.MAIN_MENU});
 483                                                 return;
 484                                         }
 485                                 } else {
 486                                         res.render("user/list", {rows: null, result:{code:'error', msg:'Unexpected no rows returned from database. ' + String(err),
 487                                                 privilege:privilegeObj },header:process.env.MAIN_MENU});
 488                                         return;
 489                                 }
 490                         }); //end query
 491                 });
 492         }); // end of getConnection
 493 }
 494
 495 exports.listUsers = function(req,res,resultObj){
 496
 497         var privilegeObj = req.session.loggedInAdmin;
 498         var rows={};
 499         pool.getConnection(function(err,connection)
 500         {
 501
 502                 if(err){
 503                         console.error( String(err) ); // ALARM
 504                         res.render("pages/list",
 505                         {
 506                                 rows: null,
 507                                 result:{
 508                                         code:'error',
 509                                         msg:"Unable to get database connection. " + String(err),
 510                                         privilege:privilegeObj },
 511                                         header:process.env.MAIN_MENU
 512                         });
 513                         return;
 514                 }
 515
 516                 // Need DB lookup logic here
 517                 var selectUsers = "SELECT email, AES_DECRYPT(password,'"
 518                         + enckey + "') password, privilege from PORTAL_USERS";
 519
 520                 connection.query(selectUsers, function(err, rows) {
 521
 522                 connection.release();
 523                 if(err){
 524                         resultObj = {code:'error', msg:'Unable to SELECT users Error: '+err};
 525                 }
 526                 if(!err)
 527                 {
 528                         if ( rows.length > 0 )
 529                         {
 530                                 console.log(JSON.stringify(rows));
 531                                 res.render('user/list',
 532                                 {
 533                                         rows: rows,
 534                                         result:resultObj,
 535                                         privilege:privilegeObj,
 536                                         header:process.env.MAIN_MENU
 537                                 });
 538                                 return;
 539                         }
 540                         else{
 541                                 res.render("user/list",
 542                                 {
 543                                         rows: null,
 544                                         result:{
 545                                                 code:'error',
 546                                                 msg:'Unexpected no rows returned from database.',
 547                                                 privilege:privilegeObj },
 548                                                 header:process.env.MAIN_MENU
 549                                 });
 550                                 return;
 551                         }
 552                 }
 553                 else
 554                 {
 555                         res.render("user/list",
 556                         {
 557                                 rows: null,
 558                                 result:{
 559                                         code:'error',
 560                                         msg:'Unexpected no rows returned from database. ' + String(err),
 561                                         privilege:privilegeObj },header:process.env.MAIN_MENU
 562                         });
 563                         return;
 564                 }
 565                 }); //end query
 566         }); // end getConnection
 567 }
 568
 569 exports.listSLA = function(req,res,resultObj){
 570
 571         var privilegeObj = req.session.loggedInAdmin;
 572
 573         pool.getConnection(function(err,connection) {
 574
 575         if(err){
 576                         console.error( String(err) ); // ALARM
 577             res.render("pages/err", {result:{code:'error', msg:"Unable to get database connection. "+ String(err)},header:process.env.MAIN_MENU});
 578                         return;
 579         }
 580
 581         // Need DB lookup logic here
 582                 connection.query("SELECT module,rpc,version,mode,active,graph FROM SVC_LOGIC", function(err, rows) {
 583
 584             connection.release();
 585             if(err) {
 586                 res.render("pages/err", {result:{code:'error',msg:'Database Error: '+ String(err)},header:process.env.MAIN_MENU});
 587                                 return;
 588                         }
 589                         else {
 590                                 res.render("sla/list", {rows:rows, result:resultObj, privilege:privilegeObj, header:process.env.MAIN_MENU} );
 591                                 return;
 592             }
 593         }); //end query
 594     }); // end getConnection
 595 }
 596
 597 exports.executeSQL = function(sql,req,res,callback){
 598
 599         console.log(sql);
 600         pool.getConnection(function(err,connection) {
 601
 602                 if(err){
 603       console.error( String(err) ); // ALARM
 604       callback(err, 'Unable to get database connection.' + err);
 605       return;
 606     }
 607     connection.query(sql, function(err,result){
 608       connection.release();
 609                         if (err) {
 610                                 callback(err,'Database operation failed. ' + err );
 611                                 return;
 612                         }
 613       else
 614       {
 615                                 console.log('affectedRows='+result.affectedRows);
 616         callback(null, result.affectedRows);
 617                                 return;
 618       }
 619     }); //end query
 620   }); // end getConnection
 621 }
 622
 623
 624 // gamma - deleteParameter
 625 exports.deleteParameter = function(req,res,callback){
 626
 627         pool.getConnection(function(err,connection) {
 628
 629         if(err){
 630         console.log( String(err) ); // ALARM
 631         callback(err, 'Unable to get database connection.' + err);
 632         return;
 633         }
 634         var sql = "DELETE FROM PARAMETERS WHERE name=" + connection.escape(req.query.name);
 635
 636         console.log(sql);
 637         connection.query(sql, function(err,result){
 638         connection.release();
 639         if(err){
 640         callback(err,'Update failed. ' + err );
 641                                 return;
 642         }
 643         else
 644         {
 645         callback(null,'');
 646                                 return;
 647         }
 648         }); //end query
 649   }); // end getConnection
 650 }
 651
 652
 653 exports.getTable = function(req,res,sql,rdestination,resultObj,privilegeObj){
 654
 655 console.log('SQL:'+sql);
 656
 657     pool.getConnection(function(err,connection) {
 658
 659         if(err){
 660             console.error( String(err) ); // ALARM
 661             res.render("pages/err", {result:{code:'error', msg:"Unable to get database connection. "+ String(err)},header:process.env.MAIN_MENU});
 662             return;
 663         }
 664         connection.query(sql,function(err, rows)
 665         {
 666             connection.release();
 667             if(err) {
 668                 res.render(rdestination, {result:{code:'error',msg:'Database Error: '+ String(err)},header:process.env.MAIN_MENU});
 669                 return;
 670             }
 671             else {
 672                 res.render(rdestination, { rows: rows, result:resultObj, privilege:privilegeObj,header:process.env.MAIN_MENU } );
 673                 return;
 674             }
 675         }); //end query
 676     }); // end getConnection
 677 }
 678
 679 exports.getMetaTable = function(req,res,sql,rdestination,resultObj,privilegeObj){
 680
 681     console.log('SQL:'+ sql);
 682
 683     var rdata = [];
 684     var v_tables = [];
 685     var vtables = properties.viewTables;
 686
 687         for ( var i in vtables ) {
 688                 v_tables.push(vtables[i]);
 689         }
 690
 691     pool.getConnection(function(err,connection) {
 692
 693         if(err){
 694             console.error( String(err) ); // ALARM
 695             res.render("pages/err", {result:{code:'error', msg:"Unable to get database connection. "+ String(err)},header:process.env.MAIN_MENU});
 696             return;
 697         }
 698         connection.query(sql,function(err, rows, fields)
 699         {
 700             console.log('rows:' + JSON.stringify(rows,null,2));
 701             // http://stackoverflow.com/questions/14528385/how-to-convert-json-object-to-javascript-array
 702             //logger.debug(Object.keys(rows[0]).map(function(v) { return rows[0][v]; }));
 703             for ( var i in rows ){
 704                 rdata.push(Object.keys(rows[i]).map(function(v) { return rows[i][v]; }));
 705                 //logger.debug(Object.keys(rows[i]).map(function(v) { return rows[i][v]; }));
 706                 //logger.debug([i, rows[i]]);
 707             }
 708             for ( var x in rdata ){
 709                 for ( var j in rdata[x] ){
 710                     console.log('rdata[' + x + ']: ' + rdata[x][j]);
 711                 }
 712             }
 713             console.log('rdata:' + rdata[0]);
 714             console.log('fields:' + JSON.stringify(fields,null,2));
 715             connection.release();
 716             if(err) {
 717                 res.render(rdestination, {result:{code:'error',msg:'Database Error: '+ String(err)},header:process.env.MAIN_MENU});
 718                 return;
 719             }
 720             else {
 721                 res.render(rdestination, { displayTable:true, vtables:v_tables, rows:rdata, fields:fields, result:resultObj, privilege:privilegeObj, header:process.env.MAIN_MENU } );
 722                 return;
 723             }
 724         }); //end query
 725     }); // end getConnection
 726 }
 727
 728 exports.getVnfProfile = function(req,res,resultObj,privilegeObj){
 729
 730         pool.getConnection(function(err,connection)
 731         {
 732                 if(err){
 733                         console.error( String(err) ); // ALARM
 734                         res.render("pages/err", {result:{code:'error', msg:"Unable to get database connection. "+ String(err)},header:process.env.MAIN_MENU});
 735                         return;
 736                 }
 737                 var sql = "SELECT vnf_type,availability_zone_count,equipment_role FROM VNF_PROFILE ORDER BY VNF_TYPE";
 738                 console.log(sql);
 739                 connection.query(sql, function(err, rows)
 740                 {
 741                         connection.release();
 742                         if(err) {
 743                                 res.render("mobility/vnfProfile", {result:{code:'error',msg:'Database Error: '+ String(err)},header:process.env.MAIN_MENU});
 744                                 return;
 745                         }
 746                         else {
 747                                 console.log('render vnfProfile');
 748                                 res.render('mobility/vnfProfile', { rows: rows, result:resultObj, privilege:privilegeObj,header:process.env.MAIN_MENU } );
 749                                 return;
 750                         }
 751                 }); //end query
 752         }); // end getConnection
 753 }
 754
 755
 756 exports.getVnfPreloadData = function(req,res,dbtable,callback){
 757
 758     pool.getConnection(function(err,connection) {
 759
 760                  if(err){
 761             console.error( String(err) ); // ALARM
 762             callback(err, 'Unable to get database connection.' + err);
 763             return;
 764         }
 765
 766         // Need DB lookup logic here
 767         connection.query("SELECT preload_data FROM " + dbtable + " WHERE id="
 768                         + req.query.id, function(err, rows)
 769         {
 770             connection.release();
 771             if(err) {
 772                 callback(err);
 773                 return;
 774             }
 775             else {
 776                                 var buffer = rows[0].preload_data;
 777                 var decode_buffer = decodeURI(buffer);
 778                                 var content = JSON.parse(decode_buffer);
 779                                 callback(null,content);
 780                                 return;
 781             }
 782         }); //end query
 783     }); // end getConnection
 784 }
 785
 786
 787
 788 exports.getVnfNetworkData = function(req,res,resultObj,privilegeObj)
 789 {
 790         pool.getConnection(function(err,connection)
 791         {
 792                 if(err){
 793                         console.error( String(err) ); // ALARM
 794                         res.render("pages/err",
 795                                 {result:{code:'error', msg:"Unable to get database connection. "+ String(err)},header:process.env.MAIN_MENU});
 796                         return;
 797                 }
 798                 // Need DB lookup logic here
 799                 var sql = "SELECT id,svc_request_id,svc_action,status,filename,ts,preload_data FROM PRE_LOAD_VNF_NETWORK_DATA ORDER BY id";
 800                 console.log(sql);
 801                 connection.query(sql, function(err, rows)
 802                 {
 803                         var msgArray = new Array();
 804                         connection.release();
 805                         if(err) {
 806                                 msgArray = 'Database Error: '+ String(err);
 807                                 res.render("mobility/vnfPreloadNetworkData", {
 808                                         result:{code:'error',msg:msgArray},
 809                                         privilege:privilegeObj,
 810                                         preloadImportDirectory: properties.preloadImportDirectory,
 811                                         header:process.env.MAIN_MENU
 812                                 });
 813                                 return;
 814                         }
 815                         else {
 816                                 var retData = [];
 817                                 for( r=0; r<rows.length; r++)
 818                                 {
 819                                         var rowObj = {};
 820                                         rowObj.row = rows[r];
 821                                         if ( rows[r].filename.length > 0 )
 822                                         {
 823                                                 try{
 824                                                         var buffer = rows[r].preload_data;
 825                                                         var decode_buffer = decodeURI(buffer);
 826                                                         var filecontent = JSON.parse(decode_buffer);
 827                                                         rowObj.filecontent = filecontent;
 828                                                         rowObj.network_name = filecontent.input["network-topology-information"]["network-topology-identifier"]["network-name"];
 829                                                         rowObj.network_type = filecontent.input["network-topology-information"]["network-topology-identifier"]["network-type"];
 830                                                 }
 831                                                 catch(error){
 832                                                         msgArray.push('File ' + rows[r].filename + ' has invalid JSON. Error:' + error);
 833                                                 }
 834                                         }
 835                                         else {
 836                                                 rowObj.filecontent = '';
 837                                         }
 838                                         retData.push(rowObj);
 839                                 }//endloop
 840                                 if(msgArray.length>0){
 841                                         resultObj.code = 'failure';
 842                                         resultObj.msg = msgArray;
 843                                 }
 844                                 res.render('mobility/vnfPreloadNetworkData', {
 845                                         retData:retData,
 846                                         result:resultObj,
 847                                         privilege:privilegeObj,
 848                                         preloadImportDirectory: properties.preloadImportDirectory,
 849                                         header:process.env.MAIN_MENU
 850                                 });
 851                                 return;
 852                         }
 853                 }); //end query
 854         }); // end getConnection
 855 }
 856
 857 exports.getVnfData = function(req,res,resultObj,privilegeObj)
 858 {
 859         pool.getConnection(function(err,connection)
 860         {
 861                 if(err){
 862                         console.error( String(err) ); // ALARM
 863                         res.render("pages/err", {result:{code:'error', msg:"Unable to get database connection. "+ String(err)},header:process.env.MAIN_MENU});
 864                         return;
 865                 }
 866                 // Need DB lookup logic here
 867                 var sql = "SELECT id,svc_request_id,svc_action,status,filename,ts,preload_data FROM PRE_LOAD_VNF_DATA ORDER BY id";
 868                 console.log(sql);
 869                 connection.query(sql,function(err, rows)
 870                 {
 871                         var msgArray = new Array();
 872                         connection.release();
 873                         if(err) {
 874                                 msgArray = 'Database Error: '+ String(err);
 875                                 res.render("mobility/vnfPreloadData", {
 876                                         result:{code:'error',msg:msgArray},
 877                                         privilege:privilegeObj,
 878                                         preloadImportDirectory: properties.preloadImportDirectory,
 879                                         header:process.env.MAIN_MENU
 880                                 });
 881                                 return;
 882                         }
 883                         else {
 884                                 var retData = [];
 885                                 for( r=0; r<rows.length; r++)
 886                                 {
 887                                         var rowObj = {};
 888                                         rowObj.row = rows[r];
 889                                         if ( rows[r].filename.length > 0 )
 890                                         {
 891                                                 try{
 892                                                         var buffer = rows[r].preload_data;
 893                                                         var s_buffer = decodeURI(buffer);
 894                                                         var filecontent = JSON.parse(s_buffer);
 895                                                         rowObj.filecontent = filecontent;
 896                                                         rowObj.vnf_name = filecontent.input["vnf-topology-information"]["vnf-topology-identifier"]["vnf-name"];
 897                                                         rowObj.vnf_type = filecontent.input["vnf-topology-information"]["vnf-topology-identifier"]["vnf-type"];
 898                                                 }
 899                                                 catch(error){
 900                                                         msgArray.push('File ' + rows[r].filename + ' has invalid JSON. Error:' + error);
 901                                                 }
 902                                         }
 903                                         else {
 904                                                 rowObj.filecontent = '';
 905                                         }
 906                                         retData.push(rowObj);
 907                                 }//endloop
 908                                 if(msgArray.length>0){
 909                                         resultObj.code = 'failure';
 910                                         resultObj.msg = msgArray;
 911                                 }
 912                                 res.render('mobility/vnfPreloadData',{
 913                                         retData:retData, result:resultObj,
 914                                         privilege:privilegeObj,
 915                                         header:process.env.MAIN_MENU,
 916                                         preloadImportDirectory: properties.preloadImportDirectory
 917                                 });
 918                                 return;
 919                         }
 920                 }); //end query
 921         }); // end getConnection
 922 }
 923
 924
 925 exports.findAdminUser = function(email,res,callback) {
 926
 927         var adminUser={};
 928         pool.getConnection(function(err,connection)
 929         {
 930                 if(err)
 931                 {
 932       res.render("pages/err", {result:{code:'error', msg:err},header:process.env.MAIN_MENU});
 933                         return;
 934                 }
 935
 936                 // Need DB lookup logic here
 937                 connection.query("SELECT email, AES_DECRYPT(password, '" + enckey + "') password, privilege FROM PORTAL_USERS WHERE email=" + connection.escape(email), function(err, rows) {
 938
 939                         connection.release();
 940                         if(err)
 941                         {
 942         res.render("pages/err", {result:{code:'error', msg:err},header:process.env.MAIN_MENU});
 943                                 return;
 944                         }
 945                         if ( rows.length > 0 )
 946                         {
 947                                 rows.forEach(function(row){
 948                                         adminUser = {
 949                                                 "email" : row.email,
 950                                                 "password" : row.password,
 951                                                 "privilege" : row.privilege };
 952                                         });
 953                                 callback(adminUser);
 954                                 return;
 955                         }
 956                         else{
 957                 res.render("pages/err", {result:{code:'error', msg:'User is not in database.'},header:process.env.MAIN_MENU});
 958                                         return;
 959                         }
 960                 }); //end query
 961   }); // end getConnection
 962 }
 963
 964
 965 exports.addRow = function(sql,req,res,callback){
 966
 967         console.log(sql);
 968
 969         pool.getConnection(function(err,connection) {
 970
 971                 if(err){
 972                         console.error( String(err) ); // ALARM
 973                         callback(err, 'Unable to get database connection.' + err);
 974                         return;
 975                 }
 976                 connection.query(sql, function(err,result){
 977                         connection.release();
 978                         if(err){
 979                                 console.debug('Database operation failed. ' + err );
 980                                 callback(err,'Database operation failed. ' + err );
 981                         }
 982                         else
 983                         {
 984                                 callback(null, result.affectedRows);
 985                         }
 986                 }); //end query
 987         }); // end getConnection
 988 }
 989
 990
 991
 992 exports.addVnfProfile = function(row,res,callback){
 993
 994         var sqlInsert;
 995
 996     if ( row.length < 3 )
 997     {
 998         console.log('Row [' + row + '] does not have enough fields.');
 999         callback(null, 'Row [' + row + '] does not have enough fields.');
1000                 return;
1001     }
1002
1003     sqlInsert = "INSERT INTO VNF_PROFILE ("
1004         + "vnf_type,availability_zone_count,equipment_role) VALUES ("
1005         + "'" + row[0] + "',"
1006                 + row[1]
1007         + ",'" + row[2] + "')";
1008
1009     console.log('SQL='+sqlInsert);
1010
1011     pool.getConnection(function(err,connection) {
1012
1013         if(err){
1014             console.log( String(err) ); // ALARM
1015             callback(err, 'Unable to get database connection.');
1016             return;
1017         }
1018         connection.query(sqlInsert, function(err,result){
1019             connection.release();
1020             if(err){
1021                 console.log('Row [' + row + '] failed to insert. ' + err );
1022                 callback(null,'Row [' + row + '] failed to insert. ' + err );
1023             }
1024             else
1025             {
1026                 callback(null,'');
1027             }
1028         }); //end query
1029     }); // end getConnection
1030 }
1031
1032 exports.deleteVnfProfile = function(req,res,callback){
1033
1034         var privilegeObj = req.session.loggedInAdmin;
1035         var rows={};
1036
1037         pool.getConnection(function(err,connection) {
1038
1039                 var sql = 'DELETE FROM VNF_PROFILE WHERE vnf_type = ' + connection.escape(req.sanitize(req.query.vnf_type));
1040                 console.log(sql);
1041                 if(err){
1042                         console.error( String(err) ); // ALARM
1043                         res.render("pages/err", {result:{code:'error', msg:"Unable to get database connection. "+ String(err)},header:process.env.MAIN_MENU});
1044                         return;
1045                 }
1046
1047                 //var vt = req.sanitize(req.query.vnf_type);
1048                 //var vnf_type = { vnf_type: vt };
1049                 //var vnf_type = connection.escape(vt);
1050                 //console.log('type='+vnf_type);
1051                 //connection.query('DELETE FROM VNF_PROFILE WHERE vnf_type = ?', vnf_type, function(err,result)
1052                 connection.query(sql, function(err,result)
1053                 {
1054                         connection.release();
1055       if (err) {
1056         callback(err,'Database operation failed. ' + err );
1057                                 return;
1058       }
1059       else
1060         {
1061                                 if (result.affectedRows == 0)
1062                                 {
1063                                         callback('No rows deleted.');
1064                                         return;
1065                                 }
1066                                 console.log('rows deleted: ' + result.affectedRows);
1067                                 callback(null, result.affectedRows);
1068                                 return;
1069         }
1070                 });
1071         }); // end of getConnection
1072 };
1073
1074 exports.deleteVnfData = function(req,res,callback){
1075
1076         var privilegeObj = req.session.loggedInAdmin;
1077         var rows={};
1078
1079         pool.getConnection(function(err,connection) {
1080
1081                 var sql = 'DELETE FROM PRE_LOAD_VNF_DATA WHERE id =' + connection.escape(req.sanitize(req.query.id));
1082                 console.log(sql);
1083                 if(err){
1084                         console.error( String(err) ); // ALARM
1085                         res.render("pages/err", {result:{code:'error', msg:"Unable to get database connection. "+ String(err)},header:process.env.MAIN_MENU});
1086                         return;
1087                 }
1088
1089                 connection.query(sql, function(err,result)
1090                 {
1091                         connection.release();
1092       if (err) {
1093         callback(err,'Database operation failed. ' + err );
1094                                 return;
1095       }
1096       else
1097         {
1098                                 if (result.affectedRows == 0)
1099                                 {
1100                                         callback('No rows deleted.');
1101                                         return;
1102                                 }
1103                                 console.log('rows deleted: ' + result.affectedRows);
1104                                 callback(null, result.affectedRows);
1105                                 return;
1106         }
1107                 });
1108         }); // end of getConnection
1109 };
1110
1111 exports.deleteVnfNetworkData = function(req,res,callback){
1112
1113         var privilegeObj = req.session.loggedInAdmin;
1114         var rows={};
1115
1116         pool.getConnection(function(err,connection) {
1117
1118                 var sql = 'DELETE FROM PRE_LOAD_VNF_NETWORK_DATA WHERE id =' + connection.escape(req.sanitize(req.query.id));
1119                 console.log(sql);
1120                 if(err){
1121                         console.error( String(err) ); // ALARM
1122                         res.render("pages/err", {result:{code:'error', msg:"Unable to get database connection. "+ String(err)},header:process.env.MAIN_MENU});
1123                         return;
1124                 }
1125
1126                 connection.query(sql, function(err,result)
1127                 {
1128                         connection.release();
1129       if (err) {
1130         callback(err,'Database operation failed. ' + err );
1131                                 return;
1132       }
1133       else
1134         {
1135                                 if (result.affectedRows == 0)
1136                                 {
1137                                         callback('No rows deleted.');
1138                                         return;
1139                                 }
1140                                 console.log('rows deleted: ' + result.affectedRows);
1141                                 callback(null, result.affectedRows);
1142                                 return;
1143         }
1144                 });
1145         }); // end of getConnection
1146 };
1147
1148 // Add to SVC_LOGIC table
1149 exports.addDG = function(_module, version, rpc, mode, xmlfile, req,res){
1150
1151         var privilegeObj = req.session.loggedInAdmin;
1152         var rows={};
1153
1154     pool.getConnection(function(err,connection) {
1155         if(err){
1156                         console.error( String(err) ); // ALARM
1157             res.render("pages/err", {result:{code:'error', msg:"Unable to get database connection. "+ String(err)},header:process.env.MAIN_MENU});
1158                         return;
1159         }
1160
1161         var post = {
1162             module  :  _module,
1163             rpc     : rpc,
1164             version : version,
1165             mode    : mode,
1166             active  : "N",
1167             graph   : xmlfile
1168         };
1169
1170         //logger.debug( JSON.stringify(post));
1171
1172         //connection.query(sqlRequest, function(err,result){
1173         connection.query('INSERT INTO SVC_LOGIC SET ?', post, function(err,result){
1174             // neat!
1175
1176             // Need DB lookup logic here
1177             connection.query("SELECT module,rpc,version,mode,active,graph FROM SVC_LOGIC", function(err, rows) {
1178
1179                 if(!err) {
1180                     if ( rows.length > 0 )
1181                     {
1182                         res.render('sla/list', { rows: rows, result:{code:'',msg:''}, privilege:privilegeObj,header:process.env.MAIN_MENU } );
1183                                                 return;
1184                     }else{
1185                         console.log("no rows returned");
1186                         res.render("pages/home");
1187                                                 return;
1188                     }
1189                 }
1190                 connection.on('error', function(err){
1191                     connection.release();
1192                     console.log(500, "An error has occurred -- " + err);
1193                     res.render("pages/home");
1194                                         return;
1195                 });
1196             }); //end query
1197
1198             connection.release();
1199         });
1200         //connection.query('INSERT INTO SVC_LOGIC SET ?', post, function(err,result){
1201             // neat!
1202             //logger.debug('inserted rows');
1203         //});
1204
1205         //if(err){
1206             //res.render('pages/home');
1207         //}
1208         return;
1209
1210     }); // end of getConnection
1211 };
1212
1213 exports.updatePreloadStatus = function(sql,req,res,_module,rpc,version,mode,callback){
1214
1215         pool.getConnection(function(err,connection) {
1216
1217     if(err){
1218                         console.error( String(err) ); // ALARM
1219       callback(err, 'Unable to get database connection.' + err);
1220                         return;
1221     }
1222
1223                 var sql = _sql + " WHERE id = " + connection.escape(req.query.id);
1224
1225                 console.log(sql);
1226     connection.query(sql, function(err,result){
1227
1228         connection.release();
1229                         if(err){
1230         callback(err, 'Unable to get database connection.' + err);
1231                                 return;
1232       }
1233       else
1234       {
1235                                 if (result.affectedRows == 0)
1236                                 {
1237                                         callback('Unable to update preload status.');
1238                                         return;
1239                                 }
1240                                 callback(null, result.affectedRows);
1241                                 return;
1242       }
1243     }); //end query
1244   }); // end getConnection
1245 }
1246
1247 exports.activate = function(req,res,_module,rpc,version,mode,callback){
1248
1249         pool.getConnection(function(err,connection) {
1250
1251     if(err){
1252                         console.error( String(err) ); // ALARM
1253       callback(err, 'Unable to get database connection.' + err);
1254                         return;
1255     }
1256
1257                 var sql = "UPDATE SVC_LOGIC SET active=\'Y\' WHERE "
1258                         + "module = " + connection.escape(_module) + " AND "
1259                         + "rpc = " + connection.escape(rpc) + " AND "
1260                         + "version = " + connection.escape(version) + " AND "
1261                         + "mode = " + connection.escape(mode);
1262
1263                 console.log('SQL='+sql);
1264     connection.query(sql, function(err,result){
1265
1266         connection.release();
1267                         if(err){
1268         callback(err, 'Unable to get database connection.' + err);
1269                                 return;
1270       }
1271       else
1272       {
1273                                 if (result.affectedRows == 0)
1274                                 {
1275                                         callback('Unable to activate directed graph.');
1276                                         return;
1277                                 }
1278                                 console.log('rows deleted: ' + result.affectedRows);
1279                                 callback(null, result.affectedRows);
1280                                 return;
1281       }
1282     }); //end query
1283   }); // end getConnection
1284 }
1285
1286
1287 exports.deactivate = function(req,res,_module,rpc,version,mode,callback){
1288
1289         pool.getConnection(function(err,connection) {
1290
1291                 if(err){
1292                         console.error( String(err) ); // ALARM
1293       callback(err, 'Unable to get database connection.' + err);
1294                         return;
1295     }
1296
1297     var sql = "UPDATE SVC_LOGIC SET active=\'N\' WHERE "
1298                         + "module = " + connection.escape(_module) + " AND "
1299                         + "rpc = " + connection.escape(rpc) + " AND "
1300                         + "version = " + connection.escape(version) + " AND "
1301                         + "mode = " + connection.escape(mode);
1302
1303                 console.log('SQL='+sql);
1304                 connection.query(sql, function(err,result){
1305
1306                         connection.release();
1307       if(err){
1308         callback(err, 'Unable to get database connection.' + err);
1309                                 return;
1310       }
1311       else
1312       {
1313                                 if (result.affectedRows == 0)
1314                                 {
1315                                         callback('Unable to deactivate directed graph.');
1316                                         return;
1317                                 }
1318                                 console.log('rows deleted: ' + result.affectedRows);
1319                                 callback(null, result.affectedRows);
1320                                 return;
1321       }
1322     }); //end query
1323   }); // end getConnection
1324 }
1325
1326 exports.global_deactivate = function(req,res,_module,rpc,mode,callback){
1327
1328         pool.getConnection(function(err,connection) {
1329
1330                 if(err){
1331       callback(err, 'Unable to get database connection.' + err);
1332       return;
1333     }
1334
1335                 // deactivate all versions
1336     var sql = "UPDATE SVC_LOGIC SET active=\'N\' WHERE "
1337                         + "module = " + connection.escape(_module) + " AND "
1338                         + "rpc = " + connection.escape(rpc) + " AND "
1339                         + "mode = " + connection.escape(mode);
1340
1341                 console.log(sql);
1342                 connection.query(sql, function(err,result){
1343
1344                         connection.release();
1345       if(err){
1346         callback(err, err);
1347                                 return;
1348                         }
1349       else
1350       {
1351                                 if (result.affectedRows == 0)
1352                                 {
1353                                         callback('Unable to set all versions to deactivate.');
1354                                         return;
1355                                 }
1356         callback(null,result.affectedRows);
1357                                 return;
1358       }
1359     }); //end query
1360   }); // end getConnection
1361 }
1362
1363
1364 exports.deleteDG = function(req,res,_module,rpc,version,mode,callback){
1365
1366         pool.getConnection(function(err,connection) {
1367
1368                 if(err){
1369                         console.error( String(err) ); // ALARM
1370       callback(err, 'Unable to get database connection.' + err);
1371                         return;
1372     }
1373
1374                 var sql = "DELETE FROM SVC_LOGIC WHERE "
1375                         + "module = " + connection.escape(_module) + " AND "
1376                         + "rpc = " + connection.escape(rpc) + " AND "
1377                         + "version = " + connection.escape(version) + " AND "
1378                         + "mode = " + connection.escape(mode);
1379
1380                 console.log(sql);
1381     connection.query(sql, function(err,result){
1382
1383         connection.release();
1384       if(err){
1385         callback(err, 'Unable to get database connection.' + err);
1386                                 return;
1387       }
1388       else
1389       {
1390                                 if (result.affectedRows == 0)
1391                                 {
1392                                         callback('No rows deleted.');
1393                                         return;
1394                                 }
1395         callback(null,result.affectedRows);
1396                                 return;
1397       }
1398     }); //end query
1399   }); // end getConnection
1400 }
1401
1402
1403
1404 function padLeft(nr, n, str){
1405     return Array(n-String(nr).length+1).join(str||'0')+nr;
1406 }
1407