![Page 1: Node.js with Sql Server - Cleveland State Universitycis.csuohio.edu/~sschung/cis612/WebApplicationExampleNodeJSSQL... · Node.js with Sql Server · Install Node.js runtime and npm](https://reader034.vdocuments.us/reader034/viewer/2022042801/5ab13b1f7f8b9a00728c1115/html5/thumbnails/1.jpg)
Node.js with Sql Server · Install Node.js runtime and npm package manager
Import amazon json data to mongodb
· Start with mongod --dbpath "D:\db"
mongoimport --db amazon --collection videogames --drop --file "D:/Amazon
Data/Videogames/reviews_Video_Games.json"
![Page 2: Node.js with Sql Server - Cleveland State Universitycis.csuohio.edu/~sschung/cis612/WebApplicationExampleNodeJSSQL... · Node.js with Sql Server · Install Node.js runtime and npm](https://reader034.vdocuments.us/reader034/viewer/2022042801/5ab13b1f7f8b9a00728c1115/html5/thumbnails/2.jpg)
On a separate cmd open and run mongo
![Page 3: Node.js with Sql Server - Cleveland State Universitycis.csuohio.edu/~sschung/cis612/WebApplicationExampleNodeJSSQL... · Node.js with Sql Server · Install Node.js runtime and npm](https://reader034.vdocuments.us/reader034/viewer/2022042801/5ab13b1f7f8b9a00728c1115/html5/thumbnails/3.jpg)
Web Application
The front end is developed in Angular JS and the server side is developed in Node.js. For the database,
we stored Walmart data in SQL Server as a table and Amazon data in MongoDB as an Amazon
collection.
Here is the front page of the system which includes fields of product type, price range, and review range
for user input.
Two options are available if the user just wants to filter the product or if the user wants some
recommendation.
Two separate calls to the database materialize according to the request.
Code for the Application for Walmart:
· Main page - index.html
<!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <title>Integrated Porduct Review and Recommendation System</title> <script src="https://ajax.googleapis.com/ajax/libs/angularjs/1.2.16/angular.js"></script> <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css"> <link rel="stylesheet" href="app.css"> <script type="text/javascript" src = "app.js"></script> <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.1/jquery.min.js"></script> <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script> </head>
![Page 4: Node.js with Sql Server - Cleveland State Universitycis.csuohio.edu/~sschung/cis612/WebApplicationExampleNodeJSSQL... · Node.js with Sql Server · Install Node.js runtime and npm](https://reader034.vdocuments.us/reader034/viewer/2022042801/5ab13b1f7f8b9a00728c1115/html5/thumbnails/4.jpg)
<body> <div class="container"> <div class="row heading" > <h1 align="center">Integrated Porduct Review and Recommendation System</h1> </div> <div class="row"><p></p></div> <div class="row"><p></p></div> <div ng-app="myApp" class="row" ng-controller="product"> <form> <div class="row"> <div class="form-group"> <div class="col-lg-3"></div> <div class="col-lg-6"> <label>Product Type:</label> <input type="text" ng-model="name" class="form-control" type="text" placeholder="Type of the Product" /> </div> <div class="col-lg-3"></div> </div> </div> <div class="row"><p></p></div>
<div class="row"> <div class="form-group"> <div class="col-lg-3"></div> <div class="col-lg-6"> <label>Lower Range: </label> <input type="text" ng-model="lowerRange" class="form-control" type="text" placeholder="Type of the Product" /> </div> <div class="col-lg-3"></div> </div> </div> <div class="row"><p></p></div> <div class="row"> <div class="form-group"> <div class="col-lg-3"></div> <div class="col-lg-6"> <label>Upper Range: </label> <input type="text" ng-model="upperRange" class="form-control" type="text" placeholder="Type of the Product" /> </div> <div class="col-lg-3"></div> </div> </div> <div class="row"><p></p></div>
<div class="row"> <div class="form-group"> <div class="col-lg-3"></div> <div class="col-lg-6"> <label>Lower Review Range: </label> <input type="text" ng-model="lowerReview" class="form-control" type="text" placeholder="Type of the Product" /> </div> <div class="col-lg-3"></div> </div> </div> <div class="row"><p></p></div>
<div class="row"> <div class="form-group"> <div class="col-lg-3"></div> <div class="col-lg-6"> <label>Upper Review Range: </label>
![Page 5: Node.js with Sql Server - Cleveland State Universitycis.csuohio.edu/~sschung/cis612/WebApplicationExampleNodeJSSQL... · Node.js with Sql Server · Install Node.js runtime and npm](https://reader034.vdocuments.us/reader034/viewer/2022042801/5ab13b1f7f8b9a00728c1115/html5/thumbnails/5.jpg)
<input ng-model="upperReview" class="form-control" type="text" placeholder="Lower Price Range" /> </div> <div class="col-lg-3"></div> </div> </div> <div class="row"><p></p></div>
<div class="row"> <div class="form-group"> <div class="col-lg-4"></div> <div class="col-lg-2"> <button type = "submit" align="center" class="btn btn-primary" ng-click="getData()"> Submit Request
</button> </div> <div class="col-lg-2"> <button type = "submit" align="center" class="btn btn-primary" ng-click="getRecommendedData()"> Recommend me!!
</button> </div> <div class="col-lg-4"></div> </div> </div>
</form> <div class="row"><p></p></div> <table class="table table-striped" ng-show = "IsVisible"> <thead> <tr> <td><label class="label label-primary">Product Name</label></td> <td><label class="label label-primary">Category</label></td> <td><label class="label label-primary">Price</label></td> <td><label class="label label-primary">Brand</label></td> <td><label class="label label-primary">Review</label></td> <td><label class="label label-primary">Rating</label></td> </tr> </thead> <tbody> <tr ng-repeat="data in dataSet"> <td>{{data.name}}</td> <td>{{data.categoryName}}</td> <td>{{data.salePrice}}</td> <td>{{data.brandName}}</td> <td>{{data.reviews_reviewText}}</td> <td>{{data.reviews_overallRating_rating}}</td> </tr> </tbody> </table>
</div> </div> </body> </html>
![Page 6: Node.js with Sql Server - Cleveland State Universitycis.csuohio.edu/~sschung/cis612/WebApplicationExampleNodeJSSQL... · Node.js with Sql Server · Install Node.js runtime and npm](https://reader034.vdocuments.us/reader034/viewer/2022042801/5ab13b1f7f8b9a00728c1115/html5/thumbnails/6.jpg)
· Angular JS script – app.js
var myApp = angular.module("myApp", []); myApp.controller('product',['$scope', '$http', '$log', function ($scope, $http, $log) {
$scope.name = "Video Games"; $scope.lowerRange = 8; $scope.upperRange = 10; $scope.lowerReview = 3; $scope.upperReview = 5; $scope.IsVisible = false; $scope.getData = function () { $http({
url:'http://localhost:4467/Product?a=' + $scope.name +'&b=' + $scope.lowerRange +'&c=' + $scope.upperRange +'&d=' + $scope.lowerReview +'&e=' + $scope.upperReview, method: 'GET' }).then(function (resp) { $scope.dataSet = resp.data; }, function (resp) { $log.error("Error Occurred"); });
$scope.IsVisible = true; };
$scope.getRecommendedData = function () { var lowPrice = $scope.upperRange; var upPrice = parseInt($scope.upperRange) + 10; $http({
url:'http://localhost:4467/Product?a=' + $scope.name +'&b=' + lowPrice +'&c=' + upPrice +'&d=' + 5 +'&e=' + 5, method: 'GET' }).then(function (resp) { $scope.dataSet = resp.data; }, function (resp) { $log.error("Error Occurred"); });
$scope.IsVisible = true; };
}])
· Server side Node.js – nodeApp.js
var express = require("express"); var sql = require("mssql"); var cors = require("cors"); var nodeApp = express();
nodeApp.use(cors());
var router = express.Router();
router.get("/Product", function (req, res) { var a = req.query.a; var b = req.query.b; var c = req.query.c; var d = req.query.d; var e = req.query.e; var query1 = "SELECT * FROM ProductData WHERE salePrice BETWEEN "+b+" AND "+c +" AND reviews_overallRating_rating BETWEEN " + d +" AND " + e + " AND categoryName='" +a+"'" console.log(query1); sql.connect("mssql://sa:Illuminati@28@localhost/walmartreviewdb").then(function () {
![Page 7: Node.js with Sql Server - Cleveland State Universitycis.csuohio.edu/~sschung/cis612/WebApplicationExampleNodeJSSQL... · Node.js with Sql Server · Install Node.js runtime and npm](https://reader034.vdocuments.us/reader034/viewer/2022042801/5ab13b1f7f8b9a00728c1115/html5/thumbnails/7.jpg)
new sql.Request().query(query1).then(function (recordset) { res.status(200).json(recordset);
})
.catch(function (err) { console.log(err);
});
})
});
nodeApp.use("/", router);
nodeApp.listen("4467", function () { console.log("Started Listening at 4467..."); });
· CSS – app.css
/* app css stylesheet */
.menu { list-style: none; border-bottom: 0.1em solid black; margin-bottom: 2em; padding: 0 0 0.5em; }
.menu:before { content: "["; }
.menu:after { content: "]"; }
.menu > li { display: inline; }
.menu > li + li:before { content: "|"; padding-right: 0.3em; }
.heading{ background-color: gainsboro;
}
![Page 8: Node.js with Sql Server - Cleveland State Universitycis.csuohio.edu/~sschung/cis612/WebApplicationExampleNodeJSSQL... · Node.js with Sql Server · Install Node.js runtime and npm](https://reader034.vdocuments.us/reader034/viewer/2022042801/5ab13b1f7f8b9a00728c1115/html5/thumbnails/8.jpg)
Sample Output:
· Output with the basic input
· Recommend me for the same inputs.
![Page 9: Node.js with Sql Server - Cleveland State Universitycis.csuohio.edu/~sschung/cis612/WebApplicationExampleNodeJSSQL... · Node.js with Sql Server · Install Node.js runtime and npm](https://reader034.vdocuments.us/reader034/viewer/2022042801/5ab13b1f7f8b9a00728c1115/html5/thumbnails/9.jpg)
A separate application for handing Amazon data is made. Source code for the application is as follows.
Code for the Application for Amazon: · Main page - index.html
<!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <title>Integrated Porduct Review and Recommendation System</title> <script src="https://ajax.googleapis.com/ajax/libs/angularjs/1.2.16/angular.js"></script> <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css"> <script type="text/javascript" src = "app.js"></script> <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.1/jquery.min.js"></script> <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
</head> <body> <div class="container"> <div class="row"> <h1 align="center">Integrated Porduct Review and Recommendation System</h1> <h3 align="center">Enter Your Choice</h3> </div> <div ng-app="myApp" class="row" ng-controller="product"> <form> <div class="row"> <div class="form-group"> <div class="col-lg-3"></div> <div class="col-lg-6"> <label for="formGroupExampleInput">Product Type:</label> <input type="text" ng-model="name" class="form-control" type="text" placeholder="Type of the Product" /> </div> <div class="col-lg-3"></div> </div> </div> <div class="row"><p></p></div>
<div class="row"> <div class="form-group"> <div class="col-lg-3"></div> <div class="col-lg-6">
![Page 10: Node.js with Sql Server - Cleveland State Universitycis.csuohio.edu/~sschung/cis612/WebApplicationExampleNodeJSSQL... · Node.js with Sql Server · Install Node.js runtime and npm](https://reader034.vdocuments.us/reader034/viewer/2022042801/5ab13b1f7f8b9a00728c1115/html5/thumbnails/10.jpg)
<label>Lower Range: </label> <input type="text" ng-model="lowerRange" class="form-control" type="text" placeholder="Type of the Product" /> </div> <div class="col-lg-3"></div> </div> </div> <div class="row"><p></p></div> <div class="row"> <div class="form-group"> <div class="col-lg-3"></div> <div class="col-lg-6"> <label>Upper Range: </label> <input type="text" ng-model="upperRange" class="form-control" type="text" placeholder="Type of the Product" /> </div> <div class="col-lg-3"></div> </div> </div> <div class="row"><p></p></div>
<div class="row"> <div class="form-group"> <div class="col-lg-3"></div> <div class="col-lg-6"> <label>Lower Review Range: </label> <input type="text" ng-model="lowerReview" class="form-control" type="text" placeholder="Type of the Product" /> </div> <div class="col-lg-3"></div> </div> </div> <div class="row"><p></p></div>
<div class="row"> <div class="form-group"> <div class="col-lg-3"></div> <div class="col-lg-6"> <label>Upper Review Range: </label> <input ng-model="upperReview" class="form-control" type="text" placeholder="Lower Price Range" /> </div> <div class="col-lg-3"></div> </div> </div> <div class="row"><p></p></div>
<div class="row"> <div class="form-group"> <div class="col-lg-3"></div> <div class="col-lg-6"> <button type = "submit" align="center" class="btn btn-primary btn-success" ng-click="getData()"> Submit Request
</button> </div> <div class="col-lg-3"></div> </div> </div>
</form> <table class="table"> <thead class="thead-inverse"> <tr> <td>Title</td>
![Page 11: Node.js with Sql Server - Cleveland State Universitycis.csuohio.edu/~sschung/cis612/WebApplicationExampleNodeJSSQL... · Node.js with Sql Server · Install Node.js runtime and npm](https://reader034.vdocuments.us/reader034/viewer/2022042801/5ab13b1f7f8b9a00728c1115/html5/thumbnails/11.jpg)
<td>Price</td> <td>URL</td> </tr> </thead> <tbody> <tr ng-repeat="data in dataSet"> <td>{{data.title}}</td> <td>{{data.price}}</td> <td><img class="img-rounded" alt="Cinque Terre" width="100" height="80" src= {{data.imUrl}}></td> </tr> </tbody> </table> <table border="1" align="center">
</table> </div> </div> </body> </html>
![Page 12: Node.js with Sql Server - Cleveland State Universitycis.csuohio.edu/~sschung/cis612/WebApplicationExampleNodeJSSQL... · Node.js with Sql Server · Install Node.js runtime and npm](https://reader034.vdocuments.us/reader034/viewer/2022042801/5ab13b1f7f8b9a00728c1115/html5/thumbnails/12.jpg)
· Angular JS script – app.js
var myApp = angular.module("myApp", []); myApp.controller('product',['$scope', '$http', '$log', function ($scope, $http, $log) {
$scope.name = "Video Games"; $scope.lowerRange = 8; $scope.upperRange = 10; $scope.lowerReview = 3; $scope.upperReview = 5; $scope.getData = function () { $http({
url:'http://localhost:4467/Product?a=' + $scope.name +'&b=' + $scope.lowerRange +'&c=' + $scope.upperRange +'&d=' + $scope.lowerReview +'&e=' + $scope.upperReview, method: 'GET' }).then(function (resp) { $scope.dataSet = resp.data; }, function (resp) { $log.error("Error Occurred"); });
};
}])
· Server side Node.js – nodeMongoApp.js
//let's require/import the MongoDB native drivers.
var express = require('express'); var app = express(); var mongojs = require('mongojs'); var mongodb = require('mongodb'); var cors = require("cors"); app.use(cors());
//We need to work with "MongoClient" interface in order to connect to a MongoDB
server.
var MongoClient = mongodb.MongoClient; // Connection URL. This is where your MongoDB server is running.
var url = 'mongodb://localhost:27017/amazon';
// Use connect method to connect to the Server
MongoClient.connect(url, function (err, db) { if (err) { console.log('Unable to connect to the mongoDB server. Error:', err); } else { //HURRAY!! We are connected. :)
console.log('Connection established to', url); // Get the documents collection
//var collection = db.collection('contactlist');
var collection_1 = db.collection('videogamesmeta'); var collection_2 = db.collection('videogames');
// Insert some users collection_1.aggregate([{$match: {price: {$gt:10, $lt:13}, title: {$regex:
".*Protector.*"}}}, {$lookup: {from:"videogames", localField:"asin", foreignField:"asin", as:"reviewVideo"}}]) .toArray(function (err, result) { if (err) { //console.log(err);
} else if (result.length) { console.log('Found:', result); } else { console.log('No document(s) found with defined "find" criteria!'); }
![Page 13: Node.js with Sql Server - Cleveland State Universitycis.csuohio.edu/~sschung/cis612/WebApplicationExampleNodeJSSQL... · Node.js with Sql Server · Install Node.js runtime and npm](https://reader034.vdocuments.us/reader034/viewer/2022042801/5ab13b1f7f8b9a00728c1115/html5/thumbnails/13.jpg)
app.get("/Product", function (req, res) { res.status(200).json(result);
}) app.listen(4467);
//Close connection
db.close();
});
}
});
· CSS – app.css
/* app css stylesheet */
.menu { list-style: none; border-bottom: 0.1em solid black; margin-bottom: 2em; padding: 0 0 0.5em; }
.menu:before { content: "["; }
.menu:after { content: "]"; }
.menu > li { display: inline; }
.menu > li + li:before { content: "|"; padding-right: 0.3em; }
.heading{ background-color: gainsboro; }
![Page 14: Node.js with Sql Server - Cleveland State Universitycis.csuohio.edu/~sschung/cis612/WebApplicationExampleNodeJSSQL... · Node.js with Sql Server · Install Node.js runtime and npm](https://reader034.vdocuments.us/reader034/viewer/2022042801/5ab13b1f7f8b9a00728c1115/html5/thumbnails/14.jpg)
Sample Output:
· MongoDB Connection initialized.