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         pool.getConnection(function(err,connection){
 266                 if(err){
 267                         console.error( String(err) ); // ALARM
 268                         res.render("pages/signup", {result:{code:'error', msg:"Unable to get database connection. " + String(err)},header:process.env.MAIN_MENU});
 269                         return;
 270         }
 271                 //var sql = "SELECT AES_DECRYPT(password, '" + enckey + "') password FROM PORTAL_USERS";
 272                 var sql = "SELECT email FROM PORTAL_USERS WHERE email='" + req.body.nf_email + "'";
 273
 274                 console.log(sql);
 275
 276                 connection.query(sql, function(err,result){
 277                         if(err){
 278                                 res.render("pages/signup", {result:{code:'error', msg:"Unable to get database connection. " + String(err)},header:process.env.MAIN_MENU});
 279                                 return;
 280                         }
 281                         if (result.length == 1 || result.length > 1)
 282                         {
 283                                 res.render("pages/signup", {result:{code:'error', msg:'User Information already exists.'},header:process.env.MAIN_MENU});
 284                                 return;
 285                         }
 286
 287                         sql = "INSERT INTO PORTAL_USERS (email,password,privilege) VALUES ("
 288             +"'"+ req.body.nf_email + "',"
 289             + "AES_ENCRYPT('" + req.body.nf_password + "','" + enckey + "'),"
 290             +"'A')";
 291
 292                         console.log(sql);
 293
 294                         connection.query(sql, function(err,result){
 295                                 connection.release();
 296
 297                                 if(err){
 298                                         res.render("pages/signup", {result:{ code:'error', msg:String(err) },header:process.env.MAIN_MENU});;
 299                                         return;
 300                                 }
 301                                 res.render('pages/signup', {result:{code:'success', msg:'User created.  Please login.'},header:process.env.MAIN_MENU});
 302                                 return;
 303                         });
 304                 });
 305         });
 306 }
 307
 308 // delete User
 309 exports.deleteUser = function(req,res){
 310
 311         var rows={};
 312         var resultObj = { code:'', msg:'' };
 313         var privilegeObj = req.session.loggedInAdmin;
 314
 315     pool.getConnection(function(err,connection) {
 316         if(err){
 317                         console.error( String(err) ); // ALARM
 318             res.render("user/list", {rows: null, result:{code:'error', msg:"Unable to get database connection. Error:" + String(err),
 319                                 privilege:privilegeObj },header:process.env.MAIN_MENU});
 320                         return;
 321         }
 322
 323         var sqlUpdate = "DELETE FROM PORTAL_USERS WHERE email='" + req.query.email + "'";
 324
 325                 console.log(sqlUpdate);
 326
 327         connection.query(sqlUpdate,function(err,result){
 328
 329             if(err){
 330                  resultObj = {code:'error', msg:'Delete of user failed Error: '+ String(err) };
 331             }
 332
 333             // Need DB lookup logic here
 334             connection.query("SELECT email,password,privilege FROM PORTAL_USERS", function(err, rows) {
 335                 connection.release();
 336                 if(!err) {
 337                     if ( rows.length > 0 )
 338                     {
 339                                                 resultObj = {code:'success',msg:'Successfully deleted user.'};
 340                         res.render('user/list', { rows: rows, result:resultObj, privilege:privilegeObj,header:process.env.MAIN_MENU } );
 341                                                 return;
 342                     }else{
 343                         res.render("user/list", { rows: null, result:{code:'error', msg:'Unexpected no rows returned from database, please try again.',
 344                                                         privilege:privilegeObj },header:process.env.MAIN_MENU});
 345                                                 return;
 346                     }
 347                 } else {
 348                     res.render("user/list", { rows: null, result:{code:'error', msg:'Unexpected no rows returned from database. Error: ' + String(err),
 349                                                         privilege:privilegeObj },header:process.env.MAIN_MENU});
 350                                         return;
 351                 }
 352             }); //end query
 353         });
 354     }); // end of getConnection
 355 }
 356
 357 // add User
 358 exports.addUser = function(req,res){
 359
 360         var rows={};
 361     var resultObj = { code:'', msg:'' };
 362         var privilegeObj = req.session.loggedInAdmin;
 363
 364     pool.getConnection(function(err,connection) {
 365         if(err){
 366                         console.error( String(err) ); // ALARM
 367             res.render("user/list", {rows: null, result:{code:'error', msg:"Unable to get database connection. "+ String(err),
 368                                 privilege:privilegeObj },header:process.env.MAIN_MENU});
 369                         return;
 370         }
 371
 372         if( req.body.nf_privilege == "admin" ){
 373             var char_priv = 'A';
 374         }else if(req.body.nf_privilege == 'readonly'){
 375             var char_priv = 'R';
 376         }else{
 377             var char_priv = 'A';
 378         }
 379
 380
 381         //connection.query(sqlRequest, function(err,result){
 382         var sqlUpdate = "INSERT INTO PORTAL_USERS (email, password, privilege) VALUES ("
 383             +"'"+ req.body.nf_email + "',"
 384             + "AES_ENCRYPT('" + req.body.nf_password + "','" + enckey + "'),"
 385             +"'"+ char_priv + "')";
 386
 387                 console.log(sqlUpdate);
 388
 389         connection.query(sqlUpdate,function(err,result){
 390
 391             if(err){
 392                  resultObj = {code:'error', msg:'Add of user failed Error: '+err};
 393             }
 394
 395             // Need DB lookup logic here
 396             connection.query("SELECT email,AES_DECRYPT(password, '" + enckey + "') password,privilege FROM PORTAL_USERS", function(err, rows) {
 397
 398                 connection.release();
 399                 if(!err) {
 400                     if ( rows.length > 0 )
 401                     {
 402                                                 resultObj = {code:'success',msg:'Successfully added user.'};
 403                         res.render('user/list', { rows: rows, result:resultObj, privilege:privilegeObj,header:process.env.MAIN_MENU } );
 404                                                 return;
 405                     }else{
 406                         res.render("user/list", {rows: null, result:{code:'error', msg:'Unexpected no rows returned from database, please try again.',
 407                                                         privilege:privilegeObj },header:process.env.MAIN_MENU});
 408                                                 return;
 409                     }
 410                 } else {
 411                     res.render("user/list", {rows: null, result:{code:'error', msg:'Unexpected no rows returned from database. Error: '+ err ,
 412                                                 privilege:privilegeObj },header:process.env.MAIN_MENU});
 413                                         return;
 414                 }
 415             }); //end query
 416         });
 417
 418     }); // end of getConnection
 419 }
 420
 421 // updateUser
 422 exports.updateUser= function(req,res){
 423
 424     var rows={};
 425         var resultObj = { code:'', msg:'' };
 426         var privilegeObj = req.session.loggedInAdmin;
 427
 428     pool.getConnection(function(err,connection) {
 429
 430         if(err){
 431                         console.error( String(err) ); // ALARM
 432             res.render("user/list", {rows: null, result:{code:'error', msg:"Unable to get database connection. " + String(err),
 433                                 privilege:privilegeObj },header:process.env.MAIN_MENU});
 434                         return;
 435         }
 436
 437                 if( req.body.uf_privilege == "admin" ){
 438                         var char_priv = 'A';
 439                 }else if(req.body.uf_privilege == 'readonly'){
 440                         var char_priv = 'R';
 441                 }else{
 442                         var char_priv = 'A';
 443                 }
 444
 445
 446         //connection.query(sqlRequest, function(err,result){
 447                 var sqlUpdate = "UPDATE PORTAL_USERS SET "
 448                         + "email = '" + req.body.uf_email + "',"
 449                         + "password = " + "AES_ENCRYPT('" + req.body.uf_password + "','" + enckey + "'), "
 450                         + "privilege = '"+ char_priv + "'"
 451                         + " WHERE email = '" + req.body.uf_key_email + "'";
 452
 453                 console.log(sqlUpdate);
 454
 455         connection.query(sqlUpdate,function(err,result){
 456
 457                         if(err){
 458                                  resultObj = {code:'error', msg:'Update of user failed Error: '+err};
 459                         }
 460
 461             // Need DB lookup logic here
 462             connection.query("SELECT email, AES_DECRYPT(password,'" + enckey + "') password, privilege FROM PORTAL_USERS", function(err, rows) {
 463                 connection.release();
 464                 if(!err) {
 465                     if ( rows.length > 0 )
 466                     {
 467                                                 resultObj = {code:'success',msg:'Successfully updated user.'};
 468                         res.render('user/list', { rows: rows, result:resultObj, privilege:privilegeObj,header:process.env.MAIN_MENU} );
 469                                 return;
 470                     }else{
 471                         res.render("user/list", {rows: null, result:{ code:'error', msg:'Unexpected no rows returned from database.',
 472                                                         privilege:privilegeObj },header:process.env.MAIN_MENU});
 473                                                 return;
 474                     }
 475                 } else {
 476                     res.render("user/list", {rows: null, result:{code:'error', msg:'Unexpected no rows returned from database. ' + String(err),
 477                                                 privilege:privilegeObj },header:process.env.MAIN_MENU});
 478                                         return;
 479                                 }
 480             }); //end query
 481         });
 482     }); // end of getConnection
 483 };
 484
 485 exports.listUsers = function(req,res,resultObj){
 486
 487         var privilegeObj = req.session.loggedInAdmin;
 488     var rows={};
 489     pool.getConnection(function(err,connection) {
 490
 491         if(err){
 492                         console.error( String(err) ); // ALARM
 493             res.render("pages/list", {rows: null, result:{code:'error', msg:"Unable to get database connection. " + String(err),
 494                                 privilege:privilegeObj },header:process.env.MAIN_MENU});
 495                         return;
 496         }
 497
 498         // Need DB lookup logic here
 499         var selectUsers = "SELECT email, AES_DECRYPT(password,'" + enckey + "') password, privilege from PORTAL_USERS";
 500         console.log(selectUsers);
 501         connection.query(selectUsers, function(err, rows) {
 502
 503                         connection.release();
 504                         if(err){
 505                                  resultObj = {code:'error', msg:'Unable to SELECT users Error: '+err};
 506                         }
 507
 508             if(!err) {
 509                 if ( rows.length > 0 )
 510                 {
 511                     console.log(JSON.stringify(rows));
 512                     res.render('user/list', { rows: rows, result:resultObj, privilege:privilegeObj,header:process.env.MAIN_MENU });
 513                                         return;
 514                 }
 515                 else{
 516                     res.render("user/list", {rows: null, result:{code:'error', msg:'Unexpected no rows returned from database.',
 517                                                 privilege:privilegeObj },header:process.env.MAIN_MENU});
 518                                         return;
 519                 }
 520             } else {
 521                     res.render("user/list", {rows: null, result:{code:'error', msg:'Unexpected no rows returned from database. ' + String(err),
 522                                                 privilege:privilegeObj },header:process.env.MAIN_MENU});
 523                                         return;
 524                         }
 525         }); //end query
 526     }); // end getConnection
 527 }
 528
 529 exports.listSLA = function(req,res,resultObj){
 530
 531         var privilegeObj = req.session.loggedInAdmin;
 532
 533         pool.getConnection(function(err,connection) {
 534
 535         if(err){
 536                         console.error( String(err) ); // ALARM
 537             res.render("pages/err", {result:{code:'error', msg:"Unable to get database connection. "+ String(err)},header:process.env.MAIN_MENU});
 538                         return;
 539         }
 540
 541         // Need DB lookup logic here
 542                 connection.query("SELECT module,rpc,version,mode,active,graph FROM SVC_LOGIC", function(err, rows) {
 543
 544             connection.release();
 545             if(err) {
 546                 res.render("pages/err", {result:{code:'error',msg:'Database Error: '+ String(err)},header:process.env.MAIN_MENU});
 547                                 return;
 548                         }
 549                         else {
 550                                 res.render("sla/list", {rows:rows, result:resultObj, privilege:privilegeObj, header:process.env.MAIN_MENU} );
 551                                 return;
 552             }
 553         }); //end query
 554     }); // end getConnection
 555 }
 556
 557 exports.executeSQL = function(sql,req,res,callback){
 558
 559     console.log(sql);
 560
 561     pool.getConnection(function(err,connection) {
 562
 563         if(err){
 564             console.error( String(err) ); // ALARM
 565             callback(err, 'Unable to get database connection.' + err);
 566             return;
 567         }
 568
 569         connection.query(sql, function(err,result){
 570             connection.release();
 571                         if (err) {
 572                                 callback(err,'Database operation failed. ' + err );
 573                         }
 574             else
 575             {
 576 console.log('affectedRows='+result.affectedRows);
 577                 callback(null, result.affectedRows);
 578             }
 579        }); //end query
 580     }); // end getConnection
 581 }
 582
 583
 584 // gamma - deleteParameter
 585 exports.deleteParameter = function(req,res,callback){
 586
 587     var sql = "DELETE FROM PARAMETERS WHERE name='" + req.query.name + "'";
 588
 589     console.log(sql);
 590
 591     pool.getConnection(function(err,connection) {
 592
 593         if(err){
 594             console.log( String(err) ); // ALARM
 595             callback(err, 'Unable to get database connection.' + err);
 596             return;
 597         }
 598         connection.query(sql, function(err,result){
 599             connection.release();
 600                if(err){
 601                     console.log('Update failed. ' + err );
 602                     callback(err,'Update failed. ' + err );
 603                }
 604                else
 605                {
 606                     callback(null,'');
 607                }
 608        }); //end query
 609     }); // end getConnection
 610 }
 611
 612
 613 exports.getTable = function(req,res,sql,rdestination,resultObj,privilegeObj){
 614
 615 console.log('SQL:'+sql);
 616
 617     pool.getConnection(function(err,connection) {
 618
 619         if(err){
 620             console.error( String(err) ); // ALARM
 621             res.render("pages/err", {result:{code:'error', msg:"Unable to get database connection. "+ String(err)},header:process.env.MAIN_MENU});
 622             return;
 623         }
 624         connection.query(sql,function(err, rows)
 625         {
 626             connection.release();
 627             if(err) {
 628                 res.render(rdestination, {result:{code:'error',msg:'Database Error: '+ String(err)},header:process.env.MAIN_MENU});
 629                 return;
 630             }
 631             else {
 632                 res.render(rdestination, { rows: rows, result:resultObj, privilege:privilegeObj,header:process.env.MAIN_MENU } );
 633                 return;
 634             }
 635         }); //end query
 636     }); // end getConnection
 637 }
 638
 639 exports.getMetaTable = function(req,res,sql,rdestination,resultObj,privilegeObj){
 640
 641     console.log('SQL:'+ sql);
 642
 643     var rdata = [];
 644     var v_tables = [];
 645     var vtables = properties.viewTables;
 646
 647         for ( var i in vtables ) {
 648                 v_tables.push(vtables[i]);
 649         }
 650
 651     pool.getConnection(function(err,connection) {
 652
 653         if(err){
 654             console.error( String(err) ); // ALARM
 655             res.render("pages/err", {result:{code:'error', msg:"Unable to get database connection. "+ String(err)},header:process.env.MAIN_MENU});
 656             return;
 657         }
 658         connection.query(sql,function(err, rows, fields)
 659         {
 660             console.log('rows:' + JSON.stringify(rows,null,2));
 661             // http://stackoverflow.com/questions/14528385/how-to-convert-json-object-to-javascript-array
 662             //logger.debug(Object.keys(rows[0]).map(function(v) { return rows[0][v]; }));
 663             for ( var i in rows ){
 664                 rdata.push(Object.keys(rows[i]).map(function(v) { return rows[i][v]; }));
 665                 //logger.debug(Object.keys(rows[i]).map(function(v) { return rows[i][v]; }));
 666                 //logger.debug([i, rows[i]]);
 667             }
 668             for ( var x in rdata ){
 669                 for ( var j in rdata[x] ){
 670                     console.log('rdata[' + x + ']: ' + rdata[x][j]);
 671                 }
 672             }
 673             console.log('rdata:' + rdata[0]);
 674             console.log('fields:' + JSON.stringify(fields,null,2));
 675             connection.release();
 676             if(err) {
 677                 res.render(rdestination, {result:{code:'error',msg:'Database Error: '+ String(err)},header:process.env.MAIN_MENU});
 678                 return;
 679             }
 680             else {
 681                 res.render(rdestination, { displayTable:true, vtables:v_tables, rows:rdata, fields:fields, result:resultObj, privilege:privilegeObj, header:process.env.MAIN_MENU } );
 682                 return;
 683             }
 684         }); //end query
 685     }); // end getConnection
 686 }
 687
 688 exports.getVnfProfile = function(req,res,resultObj,privilegeObj){
 689
 690     pool.getConnection(function(err,connection) {
 691
 692         if(err){
 693             console.error( String(err) ); // ALARM
 694             res.render("pages/err", {result:{code:'error', msg:"Unable to get database connection. "+ String(err)},header:process.env.MAIN_MENU});
 695             return;
 696         }
 697
 698         connection.query("SELECT vnf_type,availability_zone_count,equipment_role "
 699             + "FROM VNF_PROFILE ORDER BY VNF_TYPE", function(err, rows)
 700         {
 701             connection.release();
 702             if(err) {
 703                 res.render("mobility/vnfProfile", {result:{code:'error',msg:'Database Error: '+ String(err)},header:process.env.MAIN_MENU});
 704                 return;
 705             }
 706             else {
 707                 res.render('mobility/vnfProfile', { rows: rows, result:resultObj, privilege:privilegeObj,header:process.env.MAIN_MENU } );
 708                 return;
 709             }
 710         }); //end query
 711 console.log('after query');
 712     }); // end getConnection
 713 }
 714
 715
 716 exports.getVnfPreloadData = function(req,res,dbtable,callback){
 717
 718     pool.getConnection(function(err,connection) {
 719
 720                  if(err){
 721             console.error( String(err) ); // ALARM
 722             callback(err, 'Unable to get database connection.' + err);
 723             return;
 724         }
 725
 726         // Need DB lookup logic here
 727         connection.query("SELECT preload_data FROM " + dbtable + " WHERE id="
 728                         + req.query.id, function(err, rows)
 729         {
 730             connection.release();
 731             if(err) {
 732                 callback(err);
 733                 return;
 734             }
 735             else {
 736                                 var buffer = rows[0].preload_data;
 737                 var decode_buffer = decodeURI(buffer);
 738                                 var content = JSON.parse(decode_buffer);
 739                                 callback(null,content);
 740                                 return;
 741             }
 742         }); //end query
 743     }); // end getConnection
 744 }
 745
 746
 747
 748 exports.getVnfNetworkData = function(req,res,resultObj,privilegeObj){
 749
 750
 751     pool.getConnection(function(err,connection) {
 752
 753         if(err){
 754             console.error( String(err) ); // ALARM
 755             res.render("pages/err", {result:{code:'error', msg:"Unable to get database connection. "+ String(err)},header:process.env.MAIN_MENU});
 756             return;
 757         }
 758
 759         // Need DB lookup logic here
 760         connection.query("SELECT id,svc_request_id,svc_action,status,filename,ts,preload_data "
 761             + "FROM PRE_LOAD_VNF_NETWORK_DATA ORDER BY id", function(err, rows)
 762         {
 763             var msgArray = new Array();
 764
 765             connection.release();
 766             if(err) {
 767                 msgArray = 'Database Error: '+ String(err);
 768                 res.render("mobility/vnfPreloadNetworkData", {
 769                                         result:{code:'error',msg:msgArray},
 770                                         preloadImportDirectory: properties.preloadImportDirectory,
 771                                         header:process.env.MAIN_MENU
 772                                 });
 773                 return;
 774             }
 775             else {
 776                 var retData = [];
 777                 for( r=0; r<rows.length; r++)
 778                 {
 779                     var rowObj = {};
 780                     rowObj.row = rows[r];
 781                     if ( rows[r].filename.length > 0 )
 782                     {
 783                         try{
 784                                                         var buffer = rows[r].preload_data;
 785                             var decode_buffer = decodeURI(buffer);
 786                             var filecontent = JSON.parse(decode_buffer);
 787                             rowObj.filecontent = filecontent;
 788                             rowObj.network_name = filecontent.input["network-topology-information"]["network-topology-identifier"]["network-name"];
 789                             rowObj.network_type = filecontent.input["network-topology-information"]["network-topology-identifier"]["network-type"];
 790                         }
 791                         catch(error){
 792                             msgArray.push('File ' + rows[r].filename + ' has invalid JSON. Error:' + error);
 793                         }
 794                     }
 795                     else {
 796                         rowObj.filecontent = '';
 797                     }
 798                     retData.push(rowObj);
 799                 }
 800                 if(msgArray.length>0){
 801                     resultObj.code = 'failure';
 802                     resultObj.msg = msgArray;
 803                 }
 804                 res.render('mobility/vnfPreloadNetworkData', {
 805                                         retData:retData,
 806                                         result:resultObj,
 807                                         privilege:privilegeObj,
 808                                         preloadImportDirectory: properties.preloadImportDirectory,
 809                                         header:process.env.MAIN_MENU
 810                                 });
 811                 return;
 812             }
 813         }); //end query
 814     }); // end getConnection
 815 }
 816
 817 exports.getVnfData = function(req,res,resultObj,privilegeObj){
 818
 819
 820     pool.getConnection(function(err,connection) {
 821
 822         if(err){
 823             console.error( String(err) ); // ALARM
 824             res.render("pages/err", {result:{code:'error', msg:"Unable to get database connection. "+ String(err)},header:process.env.MAIN_MENU});
 825             return;
 826         }
 827
 828         // Need DB lookup logic here
 829         connection.query("SELECT id,svc_request_id,svc_action,status,filename,ts,preload_data "
 830             + "FROM PRE_LOAD_VNF_DATA ORDER BY id", function(err, rows)
 831                 {
 832                         var msgArray = new Array();
 833
 834             connection.release();
 835             if(err) {
 836                                 msgArray = 'Database Error: '+ String(err);
 837                 res.render("mobility/vnfPreloadData", {
 838                                         result:{code:'error',msg:msgArray},
 839                                         preloadImportDirectory: properties.preloadImportDirectory,
 840                                         header:process.env.MAIN_MENU
 841                                 });
 842                 return;
 843             }
 844             else {
 845                                 var retData = [];
 846                                 for( r=0; r<rows.length; r++)
 847                                 {
 848                                         var rowObj = {};
 849                                         rowObj.row = rows[r];
 850                                         if ( rows[r].filename.length > 0 )
 851                                         {
 852                                                 try{
 853                                                         var buffer = rows[r].preload_data;
 854                             var s_buffer = decodeURI(buffer);
 855                                                         var filecontent = JSON.parse(s_buffer);
 856                                                         rowObj.filecontent = filecontent;
 857                                                         rowObj.vnf_name = filecontent.input["vnf-topology-information"]["vnf-topology-identifier"]["vnf-name"];
 858                                                         rowObj.vnf_type = filecontent.input["vnf-topology-information"]["vnf-topology-identifier"]["vnf-type"];
 859                                                 }
 860                                                 catch(error){
 861                             msgArray.push('File ' + rows[r].filename + ' has invalid JSON. Error:' + error);
 862                                                 }
 863                                         }
 864                                         else {
 865                                                 rowObj.filecontent = '';
 866                                         }
 867                                         retData.push(rowObj);
 868                                 }
 869                                 if(msgArray.length>0){
 870                                         resultObj.code = 'failure';
 871                                         resultObj.msg = msgArray;
 872                                 }
 873                 res.render('mobility/vnfPreloadData',{
 874                                                 retData:retData, result:resultObj,
 875                                                 privilege:privilegeObj,
 876                                                 header:process.env.MAIN_MENU,
 877                                                 preloadImportDirectory: properties.preloadImportDirectory
 878                                 });
 879                 return;
 880             }
 881         }); //end query
 882     }); // end getConnection
 883 }
 884
 885
 886 exports.findAdminUser = function(email,res,callback) {
 887
 888
 889         var adminUser={};
 890         pool.getConnection(function(err,connection) {
 891         if(err){
 892                         console.error( String(err) ); // ALARM
 893             res.render("pages/login", {result:{code:'error', msg:"Unable to get database connection. "+ String(err)},header:process.env.MAIN_MENU});
 894                         return;
 895         }
 896
 897                 // Need DB lookup logic here
 898                 connection.query("SELECT email, AES_DECRYPT(password, '" + enckey + "') password, privilege FROM PORTAL_USERS WHERE email='" + email + "'", function(err, rows) {
 899
 900                         connection.release();
 901                 if(!err) {
 902                                 if ( rows.length > 0 )
 903                 {
 904                         rows.forEach(function(row){
 905                         adminUser = {
 906                                 "email" : row.email,
 907                                 "password" : row.password,
 908                                 "privilege" : row.privilege };
 909                         });
 910                         callback(adminUser);
 911                                         return;
 912                 }else{
 913                         console.log("no rows returned");
 914                         res.render("pages/login", {result:{code:'error', msg:'User is not in database.'},header:process.env.MAIN_MENU});
 915                                         return;
 916                 }
 917             } else {
 918                     res.render("pages/err", {result:{code:'error',msg:'Unexpected no rows returned from database. '+ String(err)},header:process.env.MAIN_MENU});
 919                                         return;
 920                         }
 921                 }); //end query
 922     }); // end getConnection
 923 }
 924
 925
 926 exports.addRow = function(sql,req,res,callback){
 927
 928     console.log(sql);
 929
 930     pool.getConnection(function(err,connection) {
 931
 932         if(err){
 933             console.error( String(err) ); // ALARM
 934             callback(err, 'Unable to get database connection.' + err);
 935             return;
 936         }
 937
 938         connection.query(sql, function(err,result){
 939             connection.release();
 940                if(err){
 941                     console.debug('Database operation failed. ' + err );
 942                     callback(err,'Database operation failed. ' + err );
 943                }
 944                else
 945                {
 946                         callback(null, result.affectedRows);
 947                }
 948        }); //end query
 949     }); // end getConnection
 950 }
 951
 952
 953
 954 exports.addVnfProfile = function(row,res,callback){
 955
 956         var sqlInsert;
 957
 958     if ( row.length < 3 )
 959     {
 960         console.log('Row [' + row + '] does not have enough fields.');
 961         callback(null, 'Row [' + row + '] does not have enough fields.');
 962                 return;
 963     }
 964
 965     sqlInsert = "INSERT INTO VNF_PROFILE ("
 966         + "vnf_type,availability_zone_count,equipment_role) VALUES ("
 967         + "'" + row[0] + "',"
 968                 + row[1]
 969         + ",'" + row[2] + "')";
 970
 971     console.log('SQL='+sqlInsert);
 972
 973     pool.getConnection(function(err,connection) {
 974
 975         if(err){
 976             console.log( String(err) ); // ALARM
 977             callback(err, 'Unable to get database connection.');
 978             return;
 979         }
 980         connection.query(sqlInsert, function(err,result){
 981             connection.release();
 982             if(err){
 983                 console.log('Row [' + row + '] failed to insert. ' + err );
 984                 callback(null,'Row [' + row + '] failed to insert. ' + err );
 985             }
 986             else
 987             {
 988                 callback(null,'');
 989             }
 990         }); //end query
 991     }); // end getConnection
 992 }
 993
 994
 995 // Add to SVC_LOGIC table
 996 exports.addDG = function(_module, version, rpc, mode, xmlfile, req,res){
 997
 998         var privilegeObj = req.session.loggedInAdmin;
 999         var rows={};
1000
1001     pool.getConnection(function(err,connection) {
1002         if(err){
1003                         console.error( String(err) ); // ALARM
1004             res.render("pages/err", {result:{code:'error', msg:"Unable to get database connection. "+ String(err)},header:process.env.MAIN_MENU});
1005                         return;
1006         }
1007
1008         var post = {
1009             module  :  _module,
1010             rpc     : rpc,
1011             version : version,
1012             mode    : mode,
1013             active  : "N",
1014             graph   : xmlfile
1015         };
1016
1017         //logger.debug( JSON.stringify(post));
1018
1019         //connection.query(sqlRequest, function(err,result){
1020         connection.query('INSERT INTO SVC_LOGIC SET ?', post, function(err,result){
1021             // neat!
1022
1023             // Need DB lookup logic here
1024             connection.query("SELECT module,rpc,version,mode,active,graph FROM SVC_LOGIC", function(err, rows) {
1025
1026                 if(!err) {
1027                     if ( rows.length > 0 )
1028                     {
1029                         res.render('sla/list', { rows: rows, result:{code:'',msg:''}, privilege:privilegeObj,header:process.env.MAIN_MENU } );
1030                                                 return;
1031                     }else{
1032                         console.log("no rows returned");
1033                         res.render("pages/home");
1034                                                 return;
1035                     }
1036                 }
1037                 connection.on('error', function(err){
1038                     connection.release();
1039                     console.log(500, "An error has occurred -- " + err);
1040                     res.render("pages/home");
1041                                         return;
1042                 });
1043             }); //end query
1044
1045             connection.release();
1046         });
1047         //connection.query('INSERT INTO SVC_LOGIC SET ?', post, function(err,result){
1048             // neat!
1049             //logger.debug('inserted rows');
1050         //});
1051
1052         //if(err){
1053             //res.render('pages/home');
1054         //}
1055         return;
1056
1057     }); // end of getConnection
1058 };
1059
1060 exports.activate = function(req,res,_module,rpc,version,mode,callback){
1061
1062         var sql = "UPDATE SVC_LOGIC SET active=\'Y\' WHERE module=\'"
1063             + _module + "' AND rpc=\'"
1064             + rpc + "' AND version=\'"
1065             +  version + "' AND mode=\'"
1066             +  mode + "'";
1067
1068         console.log('SQL='+sql);
1069
1070     pool.getConnection(function(err,connection) {
1071
1072         if(err){
1073                         console.error( String(err) ); // ALARM
1074             callback(err, 'Unable to get database connection.' + err);
1075                         return;
1076         }
1077
1078         connection.query(sql, function(err,result){
1079
1080             connection.release();
1081                         if(err){
1082                 callback(err, 'Unable to get database connection.' + err);
1083                 }
1084             else
1085             {
1086                  callback(null,'');
1087             }
1088        }); //end query
1089     }); // end getConnection
1090 }
1091
1092
1093 exports.deactivate = function(req,res,_module,rpc,version,mode,callback){
1094
1095     var sql = "UPDATE SVC_LOGIC SET active=\'N\' WHERE module=\'"
1096             + _module + "' AND rpc=\'"
1097             + rpc + "' AND version=\'"
1098             +  version + "' AND mode=\'"
1099             +  mode + "'";
1100
1101         console.log('SQL='+sql);
1102
1103     pool.getConnection(function(err,connection) {
1104
1105         if(err){
1106                         console.error( String(err) ); // ALARM
1107             callback(err, 'Unable to get database connection.' + err);
1108                         return;
1109         }
1110
1111         connection.query(sql, function(err,result){
1112
1113             connection.release();
1114             if(err){
1115                 callback(err, 'Unable to get database connection.' + err);
1116             }
1117             else
1118             {
1119                  callback(null,'');
1120             }
1121        }); //end query
1122     }); // end getConnection
1123 }
1124
1125 exports.global_deactivate = function(req,res,_module,rpc,mode,callback){
1126
1127     var sql = "UPDATE SVC_LOGIC SET active=\'N\' WHERE module=\'"
1128             + _module + "' AND rpc=\'"
1129             + rpc + "' AND mode=\'"
1130             +  mode + "'";
1131
1132
1133     pool.getConnection(function(err,connection) {
1134
1135         if(err){
1136             callback(err, 'Unable to get database connection.' + err);
1137             return;
1138         }
1139
1140         connection.query(sql, function(err,result){
1141
1142             connection.release();
1143             if(err){
1144                 callback(err, err);
1145             }
1146             else
1147             {
1148                  callback(null,'');
1149             }
1150        }); //end query
1151     }); // end getConnection
1152 }
1153
1154
1155 exports.deleteDG = function(req,res,_module,rpc,version,mode,callback){
1156
1157         var sql = "DELETE FROM SVC_LOGIC WHERE module=\'"
1158             + _module + "' AND rpc=\'"
1159             + rpc + "' AND version=\'"
1160             +  version + "' AND mode=\'"
1161             +  mode + "'";
1162
1163         console.log('SQL='+sql);
1164
1165     pool.getConnection(function(err,connection) {
1166
1167         if(err){
1168                         console.error( String(err) ); // ALARM
1169             callback(err, 'Unable to get database connection.' + err);
1170                         return;
1171         }
1172
1173         connection.query(sql, function(err,result){
1174
1175             connection.release();
1176             if(err){
1177                 callback(err, 'Unable to get database connection.' + err);
1178             }
1179             else
1180             {
1181                  callback(null,'');
1182             }
1183        }); //end query
1184     }); // end getConnection
1185 }
1186
1187
1188
1189 function padLeft(nr, n, str){
1190     return Array(n-String(nr).length+1).join(str||'0')+nr;
1191 }
1192