Node data

const app = require('express')();
const bodyParser = require('body-parser');
// Load the full build.
const _ = require('lodash');
const cron = require('node-cron');
const fs = require('fs');


const admin = require("firebase-admin");


// parse application/x-www-form-urlencoded
app.use(bodyParser.urlencoded({ extended: true }));
// parse application/json
app.use(bodyParser.json());

cron.schedule('0 */1 * * * *', function(){
    console.log('Running on '+new Date());
    DoStuff();
    syncCustList();
  });

//var mysql = require('mysql');

//var con = mysql.createConnection({
//  host: "db4free.net",
//  user: "testdb1234",
//  port: "3307",
//  database: "testdb1234",
//  password: "testdb1234"
//});



// con.connect(function(err) {

// 	  if (err) throw err;
// 	  console.log("Connected!");
// 	  var sql = `select * from table1`;

// 	  con.query(sql, function (err, result) {
// 	    if (err) throw err;
// 	 	for(var i = 0; i < result.length;i++){
// 	 		var data = result[i];
// 	 		var ref  = admin.database().ref('/nodejs-data');
// 	 		ref = ref.push();
// 	 		ref.set(data);
// 	 		ref = null; //dispose the ref for saving memory;
// 	 	}
// 	    console.log(result);

// 	  });

// 	console.log("Done");
// });
var Connection = require('tedious').Connection;
var Request = require('tedious').Request;
var TYPES = require('tedious').TYPES;



var config = {
  userName: 'devuser', // update me
  password: '123456', // update me
  server: 'localhost',
  options: {
      database: 'MedSQL',
	  rowCollectionOnDone : true
  }
}

function syncCustList() {
    var conn = new Connection(config);
    var data;
    conn.on('connect', function(err) {
    if (err) throw err;
        console.log("Connected!");
        var sql = "SELECT BB.Usr_Code, BB.Led_Name	FROM dbo.tbl_TransactionBalance   AS AA WITH (NOLOCK) LEFT JOIN dbo.tbl_LedgerSetup     AS BB WITH (NOLOCK) On AA.Led_Id = BB.Led_Id LEFT JOIN dbo.tbl_GroupDetail     AS CC WITH (NOLOCK) On BB.Grp_Id_City = CC.GrpId 	LEFT JOIN dbo.tbl_GroupDetail  AS DD WITH (NOLOCK) On BB.Grp_Id_Area = DD.GrpId LEFT JOIN dbo.tbl_sw_LedgerHead   AS EE WITH (NOLOCK) On AA.Type_Id = EE.Type_Id LEFT JOIN dbo.tbl_LedgerSetup     AS FF WITH (NOLOCK) On BB.Led_Id_Salesperson = FF.Led_Id LEFT JOIN dbo.tbl_GroupDetail     AS GG WITH (NOLOCK) On BB.GrpId_Grade = GG.GrpId LEFT JOIN dbo.tbl_sw_FixedLstData AS II WITH (NOLOCK) On BB.Fxd_TermId_S = II.SelfId    LEFT JOIN dbo.tbl_GroupDetail     AS JJ WITH (NOLOCK) On BB.GrpId_Catagory = JJ.GrpId    LEFT JOIN dbo.tbl_GroupDetail     AS KK WITH (NOLOCK) On BB.GrpId_Zone = KK.GrpId    LEFT JOIN dbo.tbl_GdwnSetup       AS LL WITH (NOLOCK) On BB.GrpId_Cost = LL.GdwnId    LEFT JOIN dbo.tbl_GroupDetail     AS MM WITH (NOLOCK) On BB.GrpId_SubLed = MM.GrpId    LEFT JOIN dbo.tbl_GroupDetail     AS NN WITH (NOLOCK) On BB.Grp_Id_Head = NN.GrpId    LEFT JOIN dbo.tbl_LedgerSetup     AS OO WITH (NOLOCK) On BB.LedId_Dman = OO.Led_Id   LEFT JOIN dbo.tbl_sw_FixedLstData AS PP WITH (NOLOCK) On BB.SelfId_Stat = PP.SelfId   LEFT JOIN dbo.tbl_GroupDetail     AS QQ WITH (NOLOCK) On BB.Grp_Id_Transport = QQ.GrpId   LEFT JOIN dbo.tbl_sw_FixedLstData AS RR WITH (NOLOCK) On BB.GrpId_State = RR.SelfId Where AA.CompanyId = 1	And AA.YearId = 13        And AA.Type_Id = 3           And BB.CompanyId In (0, 1) 	ORDER By ShowOrder, BB.Led_Name";
        var result='';

        // Read all rows from tablecls

        request = new Request(
    sql,
        function(err, rowCount, rows) {

        if (err) {
            callback(err);
        } else {
            console.log(rowCount + ' row(s) returned');
            console.log(rows);
        // callback(null);
        }
        });

        request.on('doneInProc', function(rowCount, more, rows) {

            data = [];
            rows.forEach(function(row) {
                dataItem = {};
                for(var key in row){
                    dataItem[row[key]['metadata']['colName']] = row[key]['value'];
                }
                // data.push(dataItem);
                fs.writeFile("custList.txt", dataObj, function(err){
                    if (err) {
                        return console.log(err);
                    }
                    console.log("Saved!");
                });
            }, this);

            console.log(data.length);



            var props = ['Usr_Code', 'Led_Name'];

                var notNull = _.negate(_.isNull);

                var groups = _.groupBy(data, function(dataItem){
                    return _.find(_.pick(dataItem, props), notNull);
                });


                for(var key in groups){
                    if(groups[key].length > 1){
                        groups[key] = _.sortBy(groups[key], function(dataItem) {
                            return new Date(dataItem.EntryDate);
                        });
                        var tempDI = groups[key].splice(0,1);
                        groups[key] = [];
                        groups[key].push(tempDI);
                        tempDI = null;

                    }
                }


            //    / console.log(groups["RHINO"]);
            // var refOld  = admin.database().ref('/nodejs-data').child('Quant');
            // refOld.remove();
            // refOld = null;
            for(var prodname in groups ){
                var dataObj = groups[prodname][0][0] === undefined ? groups[prodname][0] : groups[prodname][0][0] ;
                // var ref  = admin.database().ref('tempDB').child('/nodejs-data').child('Quant').child(prodname.replace(/\./g,"_"));

                // ref.set(dataObj);
                // ref = null; //dispose the ref for saving memory; DONE
            }

        });


        conn.execSql(request);
        console.log("Done");
    })


}

function DoStuff(){

    var conn = new Connection(config);
    var data;
    conn.on('connect', function(err) {
    if (err) throw err;
        console.log("Connected!");
        var sql = "SELECT distinct ISNULL(TABLE1.EntryDate,' ') as EntryDate, TABLE2.ItemUsrCode, TABLE2.ItemName,TABLE2.MRP, TABLE2.TSTK as TotalStock, ISNULL(TABLE1.SchCatagory,' ') as Scheme FROM ( select AA.UsrDate As EntryDate, AA.ItemUsrCode, AA.ItemName As ProductName,AA.MRP, FF.GrpName As SchCatagory FROM dbo.tbl_BatchSel AS AA WITH (NOLOCK, Index = ix_ItemUsrCode) LEFT JOIN dbo.tbl_ItemMaster AS BB WITH (NOLOCK) On AA.ItemDetailId = BB.ItemDetailId LEFT JOIN dbo.tbl_PI_Hdr AS CC WITH (NOLOCK) On AA.MyType = CC.MyType And AA.MyId = CC.PIH_Id LEFT JOIN dbo.tbl_GroupDetail AS DD WITH (NOLOCK) On BB.Pckg_GrpId = DD.GrpId LEFT JOIN dbo.tbl_LedgerSetup AS EE WITH (NOLOCK) On BB.Mfr_Led_Id = EE.Led_Id LEFT JOIN dbo.tbl_GroupDetail AS FF WITH (NOLOCK) On AA.GrpId_Sch = FF.GrpId  WHERE (SchDescS <> '' OR GrpId_Sch > 0) And (SchUptoDate >= CONVERT(DATETIME,2017-10-31) OR SchUptoDate Is Null OR SchUptoQty >= (Sch-SchIssd)) And AA.CompanyId = 1 And AA.YearId <= 13 And AA.MyType Not in ('ICNTE') And BB.NIP = 0 ) TABLE1 RIGHT JOIN ( SELECT BS.MRP,BB.ItemUsrCode, BB.ItemName, AA.ClQty As TSTK FROM dbo.tbl_Inventory AS AA WITH (NOLOCK) LEFT JOIN dbo.tbl_ItemMaster AS BB WITH (NOLOCK) On AA.ItemDetailId = BB.ItemDetailId LEFT JOIN dbo.tbl_GroupDetail AS CC WITH (NOLOCK) On BB.Pckg_GrpId = CC.GrpId LEFT JOIN dbo.tbl_LedgerSetup AS DD WITH (NOLOCK) On BB.Mfr_Led_Id = DD.Led_Id LEFT JOIN dbo.tbl_LedgerSetup AS EE WITH (NOLOCK) On BB.Division_LedId = EE.Led_Id LEFT JOIN dbo.Med_FN_All_HSTK (1, 13) As GG On AA.ItemDetailId = GG.ItemDetailId LEFT JOIN dbo.Med_FN_All_PQTY (1, 13) As HH On AA.ItemDetailId = HH.ItemDetailId LEFT JOIN dbo.Med_FN_All_SQTY (1, 13) As II On AA.ItemDetailId = II.ItemDetailId  left join DBO.tbl_BatchSel BS with(nolock) on BS.ItemUsrCode = AA.ItemUsrCode and BS.ItemDetailId = AA.ItemDetailId and BS.MyType <> 'ICNTE' LEFT JOIN dbo.tbl_GroupDetail AS JJ WITH (NOLOCK) On BB.ItemCatg_GrpId = JJ.GrpId Where AA.CompanyId = 1 And AA.YearId = 13 And (AA.OpQty + AA.Inward + AA.Outward + AA.ClQty) <> 0 And BB.NIP = 0 ) TABLE2 ON TABLE1.ItemUsrCode = TABLE2.ItemUsrCode";
        var result='';

        // Read all rows from tablecls

        request = new Request(
    sql,
        function(err, rowCount, rows) {

        if (err) {
            callback(err);
        } else {
            console.log(rowCount + ' row(s) returned');
            console.log(rows);
        // callback(null);
        }
        });

        request.on('doneInProc', function(rowCount, more, rows) {

            data = [];
            rows.forEach(function(row) {
                dataItem = {};
                for(var key in row){
                    dataItem[row[key]['metadata']['colName']] = row[key]['value'];
                }
                data.push(dataItem);
            }, this);

            console.log(data.length);



            var props = ['ItemUserCode', 'ItemName', 'MRP', 'TotalStock'];

                var notNull = _.negate(_.isNull);

                var groups = _.groupBy(data, function(dataItem){
                    return _.find(_.pick(dataItem, props), notNull);
                });


                for(var key in groups){
                    if(groups[key].length > 1){
                        groups[key] = _.sortBy(groups[key], function(dataItem) {
                            return new Date(dataItem.EntryDate);
                        });
                        var tempDI = groups[key].splice(0,1);
                        groups[key] = [];
                        groups[key].push(tempDI);
                        tempDI = null;

                    }
                }

            //    / console.log(groups["RHINO"]);
            var refOld  = admin.database().ref('/nodejs-data').child('Quant');
            refOld.remove();
            refOld = null;
            for(var prodname in groups ){
                var dataObj = groups[prodname][0][0] === undefined ? groups[prodname][0] : groups[prodname][0][0] ;
                var ref  = admin.database().ref('tempDB').child('/nodejs-data').child('Quant').child(prodname.replace(/\./g,"_"));

                ref.set(dataObj);
                ref = null; //dispose the ref for saving memory; DONE
            }

        });


        conn.execSql(request);
        console.log("Done");
    })


}

app.get('/Firebase', function(req,res){

	var dataToBeSentAsRepsonse = "";
	var ref  =  admin.database().ref('nodejs-data');
	ref.once('value', function(snapshot){
		var dataArr = snapshot.val();
		for(var key in dataArr){
			dataToBeSentAsRepsonse += `<b>Key:</b> ${key} &nbsp; Value: &nbsp;column1: ${dataArr[key]['col1']} - &nbsp; column2: ${dataArr[key]['col2']} - &nbsp; column3: ${dataArr[key]['col3']} - &nbsp; column4: ${dataArr[key]['col4']} <br>`;
		}
        res.send(dataToBeSentAsRepsonse);
        console.log(dataToBeSentAsRepsonse);
	});

});


app.listen(3000);