app.post('/getSchoolDetail', function (req, res) {
var schoolAbbr = req.body.schoolAbbr;
console.dir('schoolAbbr = ' + schoolAbbr);
mariadbClient.query('SELECT * FROM all_schools ' +
' WHERE school_abbr = :param1',
{param1: schoolAbbr},
function(err, rows) {
if (err)
throw err;
if (rows.length > 0) {
var tmp = '[ {' +
'"SchoolAbbr":' + '"' + rows[0].school_abbr + '",' +
'"SchoolCertNo":' + '"' + rows[0].school_cert_no + '",' +
'"SchoolFullName":' + '"' + rows[0].school_full_name + '",' +
'"SchoolFullNameThai":' + '"' + rows[0].school_full_name_thai + '"' +
'} ]';
res.end(tmp);
} else {
res.end('[]');
}
});
mariadbClient.end();
});
app.post('/login', function (req, res) {
var username = req.body.username_param;
var password = req.body.password_param;
console.dir('User='******' Pass='******'SELECT * FROM all_users ' +
' WHERE username = :param1',
{param1: username},
function(err, rows) {
if (err)
throw err;
if (rows.length > 0) {
var hashedPassword = rows[0].password;
bcrypt.compare(password, hashedPassword, function(err, result) {
if (result == true) {
res.end('SUCCESS,' + rows[0].school_abbr + ',' + rows[0].school_cert_no);
} else {
res.end('FAIL');
}
});
} else {
res.end('FAIL');
}
});
mariadbClient.end();
});
socket.on('mparte',function(data){
var client = new Client();
client.connect({
host: '127.0.0.1'
,user: '******'
,password: '******'
,db: 'julio'
});
client.query('SELECT * FROM (PARTE NATURAL JOIN PUNTO)').on
('result',function(result){
var cadenita = "<tr><th>RUTA</th><th>NOMBRE</th><th>DISTANCIA</th></tr>";
result.on(
'row',function(row){
cadenita = cadenita + '<tr><td>'+row.RUTA + '</td><td>' + row.NOMBRE + '</td><td> ' + row.DISTANCIA +' </td></tr>';
}
).on
('error',function(err) {
console.log('Resultor: ' + inspect(err));
}).on
('end',function(info) {
console.log('Resultished successfully');
socket.emit('rmparte', {'todo':'<table>'+cadenita+'</table>'});
});
});
client.end();
});
.on('end', function() {
console.log('Done with all results');
c.end();
var counter = 0;
readline = require('readline');
MongoClient.connect('mongodb://localhost/sibexplorer_dev', function(error, db) {
if (error) console.info(error);
var rd = readline.createInterface({
input: fs.createReadStream('output.txt'),
output: process.stdout,
terminal: false
});
rd.on('line', function(line) {
db.collection('pointtwo_cell_density').insert(JSON.parse(line), {safe: false});
if(counter%500 == 0) {
console.log("Total records saved in mongoDB pointtwo_cell_density collection: "+counter);
}
counter++;
});
rd.on('close', function() {
db.close();
fs.unlinkSync('output.txt');
console.log("Centi cell records data transfered from MySQL to MongoDB.")
});
});
});
app.post('/addStudentEnrol', function (req, res) {
var fullname = req.body.fullname;
var citizenId = req.body.citizenId;
var courseType = req.body.courseType;
var enrolDate = req.body.enrolDate;
var newEnrolDate = "";
if (enrolDate.indexOf('/') > -1) {
var tmpArr = enrolDate.split('/');
if (tmpArr.length == 3) {
newEnrolDate = tmpArr[2] + '-' + tmpArr[1] + '-' + tmpArr[0] + " 00:00:01";
}
}
mariadbClient.query('INSERT INTO student_enrol (fullname, citizen_id, course_type, enrol_date, exam_count)' +
' VALUES (:param1, :param2, :param3, :param4, :param5)',
{param1: fullname, param2: citizenId, param3: courseType, param4: newEnrolDate, param5:0},
function(err, rows) {
if (err) {
var errorMsg = err.message + "";
console.log("studentEnrol error = " + errorMsg);
if(errorMsg.indexOf("Duplicate") > -1) {
res.end('DUPLICATE');
} else {
res.end('ERROR');
}
}
res.end('SUCCESS');
});
mariadbClient.end();
});
socket.on('sres',function(data){
var client = new Client();
client.connect({
host: '127.0.0.1'
,user: '******'
,password: '******'
,db: 'julio'
});
client.query('SELECT * FROM PARTE P, (SELECT C.RUTA AS CC, A.PUNTO AS A,B.PUNTO AS B,A.DISTANCIA AS AA,B.DISTANCIA AS BB FROM PARTE A, PARTE B, (SELECT * FROM RESERVACION WHERE RESERVACION = '+data['res']+') C WHERE A.PUNTO = C.ORIGEN AND B.PUNTO = C.DESTINO AND A.RUTA = C.RUTA AND B.RUTA = C.RUTA) X WHERE P.DISTANCIA >= X.AA AND P.DISTANCIA <= X.BB AND X.CC = P.RUTA').on
('result',function(result){
var cadenita = "<tr><th>PUNTO</th><th>RUTA</th><th>DISTANCIA</th></tr>";
result.on(
'row',function(row){
cadenita = cadenita +'<tr><td>'+ row.PUNTO + '</td><td>' + row.RUTA + '</td><td>'+ row.DISTANCIA + '</td></tr>';
}
).on
('error',function(err) {
console.log('Resultor: ' + inspect(err));
}).on
('end',function(info) {
console.log('Resultished successfully');
socket.emit('rmres', {'todo':'<table>'+cadenita+'</table>'});
});
});
client.end();
});
app.post('/addExamHistory', function (req, res) {
var fullname = req.body.fullname;
var citizenId = req.body.citizenId;
var examNumber = req.body.examNumber;
var examTime = req.body.examTime;
var examScore = req.body.examScore;
var courseType = req.body.courseType;
var examDateTime = req.body.examDateTime;
var examResult = req.body.examResult;
var schoolCertNo = req.body.schoolCertNo;
var examResultPdfBase64String = req.body.examResultPdfBase64String;
var examResultPdfFileName = generateExamResultPdfFileName(examDateTime, citizenId);
var examResultPdfFilePath = examResultPdfFolderPath + '/' + examResultPdfFileName;
writeBase64StringToFilePath(examResultPdfBase64String, examResultPdfFilePath);
mariadbClient.query('INSERT INTO exam_history ' +
'(fullname, citizen_id, exam_number, exam_time, exam_score, course_type, ' +
' exam_datetime, exam_result, school_cert_no, exam_result_pdf_filename)' +
' VALUES (:param1, :param2, :param3, :param4, :param5, :param6, :param7, :param8, :param9, :param10)',
{param1: fullname, param2: citizenId, param3: examNumber, param4: examTime,
param5: examScore, param6: courseType, param7: examDateTime, param8: examResult,
param9: schoolCertNo, param10: examResultPdfFileName},
function(err, rows) {
if (err)
throw err;
var str = 'Add Exam History DONE';
console.dir(str);
res.end(str);
});
mariadbClient.end();
});
socket.on('masig',function(data){
var client = new Client();
client.connect({
host: '127.0.0.1'
,user: '******'
,password: '******'
,db: 'julio'
});
client.query('SELECT * FROM ASIGNACION').on
('result',function(result){
var cadenita = "<tr><th>RUTA</th><th>BUS</th><th>FECHA</th></tr>";
result.on(
'row',function(row){
cadenita = cadenita +'<tr><td>'+ row.RUTA + '</td><td>' + row.BUS + '</td><td>'+ row.FECHA + '</td></tr>';
}
).on
('error',function(err) {
console.log('Resultor: ' + inspect(err));
}).on
('end',function(info) {
console.log('Resultished successfully');
socket.emit('rmasig', {'todo':'<table>'+cadenita+'</table>'});
});
});
client.end();
});
socket.on('bruta',function(data){
var client = new Client();
client.connect({
host: '127.0.0.1'
,user: '******'
,password: '******'
,db: 'julio'
});
client.query('DELETE FROM PARTE WHERE RUTA = '+data['ruta']).on
('result',function(result){
result.on
('error',function(err) {
console.log('Resultor: ' + inspect(err));
}).on
('end',function(info) {
console.log('Resultished successfully');
});
});
client.query('DELETE FROM RUTA WHERE RUTA = '+data['ruta']).on
('result',function(result){
result.on
('error',function(err) {
console.log('Resultor: ' + inspect(err));
}).on
('end',function(info) {
console.log('Resultished successfully');
});
});
client.end();
});
socket.on('mbus',function(data){
var client = new Client();
client.connect({
host: '127.0.0.1'
,user: '******'
,password: '******'
,db: 'julio'
});
client.query('SELECT * FROM (BUS NATURAL JOIN TIPO_BUS)').on
('result',function(result){
var cadenita = "<table><tr><th>BUS</th><th>TIPO</th></tr>";
result.on(
'row',function(row){
cadenita = cadenita + '<tr><td>'+row.BUS + '</td><td>' + row.NOMBRE + '</td></tr>';
}
).on
('error',function(err) {
console.log('Resultor: ' + inspect(err));
}).on
('end',function(info) {
console.log('Resultished successfully');
socket.emit('rmbus', {'todo':cadenita+'</table>'});
});
});
client.end();
});
exports.list = function(req, res){
//res.send("respond from users");
console.log("respond from USER");
var query = "select * from USER";
var json ='';
c.query(query ,null,{metadate:true},function(err,rows){
if(err)
throw err;
//console.dir(rows);
json = JSON.stringify(rows);
console.log('the query-result is :',json);
res.setHeader('content-type','application/json');
res.end(json);
});
c.end();
};
app.post('/incrementExamCount', function (req, res) {
var allCitizenIds = req.body.allCitizenIds;
var allCourseTypes = req.body.allCourseTypes;
var delimiter = ',';
var citizenIdsArray = allCitizenIds.split(delimiter);
var courseTypesArray = allCourseTypes.split(delimiter);
if (citizenIdsArray.length == 0 ||
citizenIdsArray.length != courseTypesArray.length)
return;
var sqlStatement = '';
for (var i = 0; i < citizenIdsArray.length; i++)
{
var tmpCitizenId = citizenIdsArray[i];
var tmpCourseType = courseTypesArray[i];
sqlStatement += ' (citizen_id="' + tmpCitizenId + '" AND course_type="' + tmpCourseType + '"' + ') ';
if (i < citizenIdsArray.length - 1)
sqlStatement += ' OR ';
}
var str = '';
mariadbClient.query('UPDATE student_enrol SET exam_count = exam_count + 1 WHERE ' + sqlStatement,
{},
function(err, rows) {
if (err) {
throw err;
}
res.end('SUCCESS');
});
mariadbClient.end();
});
exports.get_one = function(req,res){
console.log('get user by email :' + req.params.email);
var email = req.params.email;
var sql = "SELECT * FROM USER WHERE EMAIL=:email";
c.query(sql,{email:req.params.email},function(err,rows){
if(err){
//throw err;
console.error(err.stack);
res.status(500).send('Something broke!');
}
if(rows.length==0){
res.status(404).send('查無資料！');
}
var json = JSON.stringify(rows);
console.log('the query-result is :',json);
res.setHeader('content-type','application/json');
res.end(json);
});
c.end();
//res.send('get user by key');
};
socket.on('mres',function(data){
var client = new Client();
client.connect({
host: '127.0.0.1'
,user: '******'
,password: '******'
,db: 'julio'
});
client.query('SELECT * FROM RESERVACION WHERE CLIENTE = '+data['cliente']).on
('result',function(result){
var cadenita = "<tr><th>RESERVACION</th><th>RUTA</th><th>CLIENTE</th><th>FECHA</th><th>PAGADA</th><th>TOTAL</th></tr>";
result.on(
'row',function(row){
cadenita = cadenita +'<tr><td>'+ row.RESERVACION + '</td><td>' + row.RUTA + '</td><td>'+ row.CLIENTE + '</td><td>'+row.FECHA + '</td><td>'+row.PAGADA + '</td><td>'+row.TOTAL + '</td></tr>';
}
).on
('error',function(err) {
console.log('Resultor: ' + inspect(err));
}).on
('end',function(info) {
console.log('Resultished successfully');
socket.emit('rmres', {'todo':'<table>'+cadenita+'</table>'});
});
});
client.end();
});
var executeQuery = function (counter) {
connection.query("SELECT SLEEP(1)").on("result", function onResult(result) {
result.on("end", function onRow(row) {
console.log("Ended Query #" + counter);
});
});
connection.end();
};
app.post('/deleteStudentEnrol', function (req, res) {
var citizenId = req.body.citizenId;
var courseType = req.body.courseType;
mariadbClient.query('DELETE FROM student_enrol WHERE citizen_id=:param1 AND course_type=:param2',
{param1: citizenId, param2: courseType},
function(err, rows) {
res.end('SUCCESS');
});
mariadbClient.end();
});
c.query(exec ,{email:req.body.email,nick_name:req.body.nick_name},function(err,rows){
if(err){
//throw err;
console.error(err.stack);
res.status(500).send('Something broke!');
}
res.end('更新成功');
c.end();
});
c.query(exec ,{email:req.body.email,pwd:req.body.pwd},function(err,rows){
if(err){
//throw err;
console.error(err.stack);
res.status(500).send('註冊帳號失敗');
}
res.end('新增成功');
c.end();
});
app.post('/updateStudentEnrol', function (req, res) {
var citizenId = req.body.citizenId;
var courseType = req.body.courseType;
var examCount = req.body.examCount;
mariadbClient.query('UPDATE student_enrol SET exam_count = :param1 WHERE citizen_id=:param2 AND course_type=:param3',
{param1: examCount, param2: citizenId, param3: courseType},
function(err, rows) {
if (err) {
throw err;
}
res.end('SUCCESS');
});
mariadbClient.end();
});
bcrypt.hash(password, saltRounds, function(err, hashedPassword) {
console.dir('User='******' Pass='******' Bcrypt='+hashedPassword);
console.dir('schoolAbbr=' + schoolAbbr + ' schoolCertNo=' + schoolCertNo);
mariadbClient.query('INSERT INTO all_users (username, password, school_abbr, school_cert_no)' +
' VALUES (:param1, :param2, :param3, :param4)',
{param1: username, param2: hashedPassword, param3: schoolAbbr, param4: schoolCertNo},
function(err, rows) {
if (err)
throw err;
res.end('SUCCESS');
});
mariadbClient.end();
});
app.post('/user',function(req,res){
if(req.body.user.dpi == 'admin'){
res.render('admin',{title:'Administrador'});
}else{
var client = new Client();
var funciono = 0;
client.connect
(
{
host: '127.0.0.1'
,user: '******'
,password: '******'
,db: 'julio'
}
);
client.query('SELECT * FROM CLIENTE WHERE DPI = '+req.body.user.dpi).on
(
'result',
function(result)
{
result.on
(
'row',
function(row) {
res.render('user',{title:row.NOMBRE});
funciono = 1;
}
).on
(
'error',
function(err) { console.log('Resultor: ' + inspect(err));
}
).on
(
'end',
function(info) { console.log('Resultished successfully'); }
);
}
).on
(
'end',
function() { console.log('Doneh all results'); }
);
client.end();
}
});
process.on('SIGINT', function () {
console.log('Got Ctrl+C! Sending all client disconnect packets, and closing the MQTT server.');
// For each client, send a disconnect and close the connection,
// probably all synchronous, but who cares when you're closing down?
for (var clientId in myMQTTServer.clients) {
console.log('Sending disconnect to client: ' + clientId);
myMQTTServer.clients[clientId].disconnect();
myMQTTServer.clients[clientId].stream.end();
}
// Now close the server
myMQTTServer.close();
// Now close the DB connection
dbclient.end();
});
var send_data_to_implio = new CronJob('00,30 * * * * *', function(){
console.log("send_data_to_implio cron job started");
var c = new Client();
c.connect(mysql_config);
//Getting the data from my database
c.query('SELECT * FROM my_ads WHERE implio_treated = 0;',
function(err, rows) {
if (err){
console.log('An error have been encountered with the query. error message: ' + err);
}
else{
console.log("just got the ads from my database");
var implio_request = [];
//Here we are going to map our data format, to the data format of implio
for (var i = 0, iLen = rows.length; i < iLen; i ++){
var implio_formated = {
"id": rows[i].id.toString() || "1",
"content": {
"title": rows[i].title,
"body": rows[i].body
}
};
implio_request.push(implio_formated);
}
//then we do the request (we don't care to much right now if it works or not)
var req = http.request(send_options, function (res) {
console.log("implio respond to our post of ads");
});
console.log("sending data to implio");
req.write(JSON.stringify(implio_request));
req.end();
}
}
);
c.end();
}, null, true);
socket.on('cbus',function(data){
var client = new Client();
client.connect({
host: '127.0.0.1'
,user: '******'
,password: '******'
,db: 'julio'
});
client.query('UPDATE BUS SET TIPO_BUS = '+data['tipo']+' WHERE BUS ='+data['bus']).on
('result',function(result){
result.on
('error',function(err) {
console.log('Resultor: ' + inspect(err));
}).on
('end',function(info) {
console.log('Resultished successfully');
});
});
client.end();
});
socket.on('cres',function(data){
var client = new Client();
client.connect({
host: '127.0.0.1'
,user: '******'
,password: '******'
,db: 'julio'
});
client.query('UPDATE RESERVACION SET PAGADA = 1 WHERE RESERVACION ='+data['res']).on
('result',function(result){
result.on
('error',function(err) {
console.log('Resultor: ' + inspect(err));
}).on
('end',function(info) {
console.log('Resultished successfully');
});
});
client.end();
});
socket.on('register',function(data){
var client = new Client();
client.connect({
host: '127.0.0.1'
,user: '******'
,password: '******'
,db: 'julio'
});
client.query('INSERT INTO CLIENTE VALUES('+data['dpi'] + ','+data['nombre']+','+data['tel']+')').on
('result',function(result){
result.on
('error',function(err) {
console.log('Resultor: ' + inspect(err));
}).on
('end',function(info) {
console.log('Resultished successfully');
});
});
client.end();
});
app.get('/getExamResultPdf', function (req, res) {
var recordId = req.query['record_id'];
mariadbClient.query('SELECT exam_result_pdf_filename FROM exam_history WHERE record_id = :param1',
{param1: recordId},
function(err, rows) {
if (err)
throw err;
var pdfBase64String = '';
for (var i = 0; i < rows.length; i++) {
var fileName = rows[i].exam_result_pdf_filename;
var filePath = examResultPdfFolderPath + '/' + fileName;
var base64Str = readBase64StringFromFilePath(filePath);
if (base64Str != null) {
pdfBase64String = base64Str;
}
break;
}
res.end(pdfBase64String);
});
mariadbClient.end();
});
socket.on('aasig',function(data){
var now = new Date();
var client = new Client();
client.connect({
host: '127.0.0.1'
,user: '******'
,password: '******'
,db: 'julio'
});
client.query('INSERT INTO ASIGNACION VALUES ('+data['bus']+',\''+now.getFullYear() +'/' + (parseInt(now.getMonth())+1) + '/' + now.getDate() +'\','+data['ruta']+')').on
console.log(now);
('result',function(result){
result.on
('error',function(err) {
console.log('Resultor: ' + inspect(err));
}).on
('end',function(info) {
console.log('Resultished successfully');
});
});
client.end();
});
.on('end', function() {
console.log('Done with all results');
c.end();
var counter = 0;
var numUpdated = 1;
readline = require('readline');
MongoClient.connect('mongodb://localhost/sibexplorer_dev', function(error, db) {
if (error) console.info(error);
var rd = readline.createInterface({
input: fs.createReadStream('output.txt'),
output: process.stdout,
terminal: false
});
rd.on('line', function(line) {
var lineData = line.split("\t");
db.collection('occurrences').update({"taxonomy.species_id": parseInt(lineData[0])}, {$push: {common_names: JSON.parse(lineData[1])}}, {multi:true, safe: true}, function(err) {
if (err) {
console.warn(err.message);
} else {
console.log('Successfully updated num of occurrences: '+numUpdated);
numUpdated++;
if (numUpdated-1 == counter) {
db.close();
console.log("Common names for occurence records data transfered from MySQL to MongoDB.")
}
}
});
counter++;
});
rd.on('close', function() {
fs.unlinkSync('output.txt');
});
});
});
res.on("end", function () {
console.log("implio finished to send us his response");
var body = Buffer.concat(chunks);
var implio_result = JSON.parse(body);
var c = new Client();
c.connect(mysql_config);
//Once we got the result, we are going to update our database with the information received (for each ad)
for (var i = 0, iLen = implio_result.ads.length; i < iLen; i ++) {
var prep = c.prepare('UPDATE my_ads SET time_retrieved=NOW(), implio_treated=1, decision=:implio_decision WHERE id=:ad_id;');
c.query(prep({
"implio_decision": implio_result.ads[i].result.outcome,
"ad_id": implio_result.ads[i].ad.id
}),
function (err, rows) {
console.log("Our database is updated.");
}
);
}
c.end();
});