orders.js

var mysql = require('mysql');
var ini = require('ini');
var fs = require('fs');
var logger = require("winston");
var config = ini.parse(fs.readFileSync('./config.ini', 'utf-8'))

var serverPort = config.server.port;
var dbUser = config.database.user;
var dbPass = config.database.password;
var dbHost = config.database.host;
var dbName = config.database.db;




var io = require('socket.io').listen(serverPort);
logger.info("server is running on %s port", serverIp);

var db = mysql.createPool({
	host:dbHost,
	user: dbUser,
	password: dbPass,
	database: dbName,
	connectionLimit: 10
});
io.sockets.on('connection', function(socket){
	socket.on('disconnect', function(){
		logger.info("disconnect");
	})
	logger.info("new connection");
		socket.on("update-ordersList", function(msg){

			console.log(msg.info);
			sql = "SELECT * FROM ("+
                "SELECT "+
                " o.*, astext(o.coord_from_adres) as coord_from_adres_t, astext(o.coord_to_adres) as coord_to_adres_t, c.Mobile as client_phone_number, "+
                " c.FirstName as client_name, CONCAT(u.name, ' ', u.number) as car_number, u.id as unit_id, "+
                " IF (o.status in (2,4,8,9,11,12,13,14,15,16,17,18), UNIX_TIMESTAMP(CURRENT_TIMESTAMP)-UNIX_TIMESTAMP(`status_time`), 0) as vaqtRazn,"+
                " IF (o.status in (7), UNIX_TIMESTAMP(CURRENT_TIMESTAMP)-UNIX_TIMESTAMP(`status_time`), 0) as vaqtRazn_7,"+
				" IF (o.status = 0, UNIX_TIMESTAMP(`time_order`) - UNIX_TIMESTAMP(CURRENT_TIMESTAMP), 0) as BUDvaqtRazn,"+
                " d.driver_phone, us.login as user_name, (UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(o.`status_time`)) as ayirma "+
                " FROM "+
                "max_taxi_incoming_orders o "+
                "LEFT OUTER JOIN max_taxi_server_clients c ON c.ClientID = o.client_id "+
                "LEFT OUTER JOIN max_drivers d ON d.id = o.driver_id "+
                "LEFT OUTER JOIN max_units u ON u.id = d.unit_id "+
                "LEFT OUTER JOIN max_users us ON us.id = o.user_id "+
                " WHERE "+
                " ((o.driver_id <> 0 AND o.driver_id in (SELECT id FROM max_drivers WHERE fleet_id = "+msg.fleet+"))  OR (o.driver_id = 0 AND companies like '%"+msg.fleet+"%'))) t "+
                " WHERE (vaqtRazn < 3600 AND vaqtRazn_7 < 120) AND BUDvaqtRazn < 2400 "+
				" ORDER BY time_order DESC LIMIT 0, 100";
				
			//console.log(sql);
            function Sek2cTime(sk)	{
            	h = Math.floor(sk / 3600);
            	m = Math.floor((sk - h * 3600) / 60);
                s = sk-((h*3600)+(m*60));
            	if (h || m || s)	{
                    if (m<10) m = '0'+m;
                    if (s<10) s = '0'+s;
                    return h+':'+m+':'+s;
            	} else return "0"; 
            }
            
            db.query("SET NAMES 'utf8'");
			db.query("SET CHARACTER SET 'utf8'");
			
            	try{
					db.query(sql, function(err, rows, fields) {
	     			if (err) console.log(err);
					var data = new Array(); //data to send via websocket
		     		for (var i in rows) {
		     		    if (rows[i].date == '0000-00-00 00:00:00') rows[i].date = ' ';
						data.push([
							rows[i].id,
							rows[i].client_id,
							rows[i].driver_id,
							rows[i].status,
							rows[i].from_adres,
							rows[i].sub_region_from,
							rows[i].coord_from_adres_t,
							rows[i].to_adres,
							rows[i].sub_region_to,
							rows[i].coord_to_adres_t,
							rows[i].date,
							rows[i].time_order,
							rows[i].reqs,
							rows[i].companies,
							rows[i].orderFrom,
							rows[i].distance,
							rows[i].description,
							rows[i].dr_arrived_time,
							rows[i].client_sat_time,
							rows[i].order_finished_time,
							rows[i].status_time,
							rows[i].order_del_status,
							rows[i].client_phone_number,
							rows[i].client_name,
							rows[i].car_number,
							Sek2cTime(rows[i].ayirma),
							rows[i].unit_id,
							rows[i].driver_phone,
						    rows[i].user_name
						]);			
		     		}
		    	socket.json.send(data);
				})
			}
			catch(e){
				console.log(e);
			}
	})
});