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