onhandExample.js

function route_GetOnHandByDateRange(req, res) {
    u.log('[route_GetOnHandByDateRange] ', req.query);
    var _cache = [];
    var request = req.query;

    function validateRequest(cb) {
        var requestIsValid = true;
        var errorText = "";
        if (request.startDate == undefined || request.startDate == "" || request.startDate == null) {
            requestIsValid = false;
            errorText += 'Invalid Start Date. ';
        }
        if (request.endDate == undefined || request.endDate == "" || request.endDate == null) {
            requestIsValid = false;
            errorText += 'Invalid End Date. ';
        }

        if (requestIsValid == false) {
            cb(true, errorText);
            return;
        } else {
            cb(false, 'Validation Success!');
        }
    }

    function getOrderData(cb) {
        u.log('[getOrderData] gathering orders')
        var query = "";


        query += " SELECT JobRecID, " +
            " DateAdded " +
            " ,[tagnet].dbo.tagnet_AppointmentSPN_Orders.PartnerPrimaryOrderID, " +
            " [tagnet].dbo.tagnet_AppointmentSPN_Orders.JobControlNumber " +
            " ,[tagnet].dbo.tagnet_AppointmentSPN_Orders.DLActualArrive " +
            " ,PUCity " +
            " ,DLCity " +
            " FROM " +
            " [tagnet].dbo.tagnet_AppointmentSPN_Orders, " +
            " [tagnet].dbo.tagnet_YRC_OrderHeaders " +
            " WHERE " +
            " DateAdded between '" + request.startDate + "' AND '" + request.endDate + "' " +
            " AND [tagnet].dbo.tagnet_AppointmentSPN_Orders.PartnerPrimaryOrderID = [tagnet].dbo.tagnet_YRC_OrderHeaders.MasterProNumber " +
            " GROUP BY " +
            " [tagnet].dbo.tagnet_AppointmentSPN_Orders.PartnerPrimaryOrderID, " +
            " [tagnet].dbo.tagnet_AppointmentSPN_Orders.JobControlNumber " +
            " ,[tagnet].dbo.tagnet_AppointmentSPN_Orders.DLActualArrive " +
            " ,PUCity " +
            " ,DLCity " +
            " ,DateAdded" +
            " ,JobRecID " +
            " ORDER BY " +
            " [tagnet].dbo.tagnet_AppointmentSPN_Orders.PartnerPrimaryOrderID, " +
            " [tagnet].dbo.tagnet_AppointmentSPN_Orders.JobControlNumber ";
        sql.tagnet(query, function(err, reply){
            if(err){
                cb(true, reply);
                return;
            }
            _cache = reply;
            cb(false, "");
        });

    }
    function getRE(cb){
        u.log('[getDeliveryDateSched] finding RE Date for ',_cache.length, " Orders ");
        var _tasks = [];
        var len = _cache.length - 1;
        _cache.forEach(function(e, i, a){

            _tasks.push(function (callback){
                var index = i;
                query = "SELECT [NoteDate]" +
                    " FROM [tagnet].[dbo].[tagnet_AppointmentSPN_OrderNotes] " +
                    " WHERE " +
                    " NoteCode = 'RE' " +
                    " and [PartnerPrimaryOrderID] ='"+e.PartnerPrimaryOrderID+"' ";
                sql.tagnet(query, function(err, reply){
                    if(err){
                        callback(true, res);
                        return;
                    }

                    if(reply == null){
                        _cache[index].NoteCodeRE = null;
                    }else{
                        if(reply.length > 1){
                            _cache[index].NoteCodeRE = reply[reply.length -1];
                        }else{
                            _cache[index].NoteCodeRE = reply[0];
                        }
                        if(_cache[index].DateAdded != null && _cache[index].NoteCodeRE){
                            _cache[index].NoteCodeRE.NumDays = workingDaysBetweenDates(new Date(_cache[index].DateAdded), new Date(_cache[index].NoteCodeRE.NoteDate));
                        }else{
                            _cache[index].NoteCodeRE = {};
                            _cache[index].NoteCodeRE.NumDays = 0;
                        }
                        if(_cache[index].DLActualArrive != null && _cache[index].NoteCodeRE.NoteDate){
                            _cache[index].DaysFromREtoPOD = workingDaysBetweenDates(new Date(_cache[index].NoteCodeRE.NoteDate), new Date(_cache[index].DLActualArrive));
                        }else{
                            //_cache[index].NoteCodeRE = {};
                            _cache[index].DaysFromREtoPOD = 0;
                        }
                        //_cache[index].NoteCodeRE.NumDays = DateDiff(new Date(_cache[index].DateAdded), new Date(_cache[index].NoteCodeRE.NoteDate));
                    }


                    callback(false, "");
                });
            });
            if(i == len){
                async.series(_tasks, function (err, reply) {
                    if(err){
                        cb(true, reply);
                        return;
                    }
                    cb(false, "")
                });
            }
        });

    }
    function getDeliveryDateSched(cb){
        u.log('[getDeliveryDateSched] finding Sched Date for ',_cache.length, " Orders ");
        var _tasks = [];
        var len = _cache.length - 1;
        _cache.forEach(function(e, i, a){

            _tasks.push(function (callback){
                var index = i;
                query = "SELECT [LogDate]" +
                    " FROM [tagnet].[dbo].[tagnet_AppointmentSPN_AuditLog] " +
                    " WHERE " +
                    " LogCode = 'State Change' " +
                    " and [JobRecID] ='"+e.JobRecID+"' " +
                    " and LogText LIKE '%Delivery Date Scheduled%' ";
                sql.tagnet(query, function(err, reply){
                    if(err){
                        callback(true, res);
                        return;
                    }
                    if(reply == null){
                        _cache[index].DeliveryDateSchedueled = null;
                    }else{
                        if(reply.length > 1){
                            _cache[index].DeliveryDateSchedueled = reply[reply.length -1];
                        }else{
                            _cache[index].DeliveryDateSchedueled = reply[0];
                        }
                        if(_cache[index].NoteCodeRE  && _cache[index].DeliveryDateSchedueled){
                            _cache[index].DeliveryDateSchedueled.NumDays = workingDaysBetweenDates(new Date(_cache[index].NoteCodeRE.NoteDate), new Date(_cache[index].DeliveryDateSchedueled.LogDate));
                        }else{
                            _cache[index].DeliveryDateSchedueled = {};
                            _cache[index].DeliveryDateSchedueled.NumDays = 0;
                        }

                    }

                    callback(false, "");
                });
            });
            if(i == len){
                async.series(_tasks, function (err, reply) {
                    if(err){
                        cb(true, reply);
                        return;
                    }
                    cb(false, "")
                });
            }
        });

    }
    function get1C(cb){
        u.log('[getDeliveryDateSched] finding 1C Date for ',_cache.length, " Orders ");
        var _tasks = [];
        var len = _cache.length - 1;
        _cache.forEach(function(e, i, a){

            _tasks.push(function (callback){
                var index = i;
                query = "SELECT [NoteDate]" +
                    " FROM [tagnet].[dbo].[tagnet_AppointmentSPN_OrderNotes] " +
                    " WHERE " +
                    " NoteCode = '1C' " +
                    " and [PartnerPrimaryOrderID] ='"+e.PartnerPrimaryOrderID+"' ";
                sql.tagnet(query, function(err, reply){
                    if(err){
                        callback(true, res);
                        return;
                    }

                    if(reply == null){
                        _cache[index].NoteCode1C = null;
                    }else{
                        if(reply.length > 1){
                            _cache[index].NoteCode1C = reply[reply.length -1];
                        }else{
                            _cache[index].NoteCode1C = reply[0];
                        }
                        if(_cache[index].NoteCodeRE && _cache[index].NoteCode1C){
                            _cache[index].NoteCode1C.NumDays = workingDaysBetweenDates(new Date(_cache[index].NoteCodeRE.NoteDate), new Date(_cache[index].NoteCode1C.NoteDate));
                        }else{
                            _cache[index].NoteCode1C = {}
                            _cache[index].NoteCode1C.NumDays = 0;
                        }

                    }

                    callback(false, "");
                });
            });
            if(i == len){
                async.series(_tasks, function (err, reply) {
                    if(err){
                        cb(true, reply);
                        return;
                    }
                    cb(false, "")
                });
            }
        });

    }
    function get2C(cb){
        u.log('[getDeliveryDateSched] finding 2C Date for ',_cache.length, " Orders ");
        var _tasks = [];
        var len = _cache.length - 1;
        _cache.forEach(function(e, i, a){

            _tasks.push(function (callback){
                var index = i;
                query = "SELECT [NoteDate]" +
                    " FROM [tagnet].[dbo].[tagnet_AppointmentSPN_OrderNotes] " +
                    " WHERE " +
                    " NoteCode = '2C' " +
                    " and [PartnerPrimaryOrderID] ='"+e.PartnerPrimaryOrderID+"' ";
                sql.tagnet(query, function(err, reply){
                    if(err){
                        callback(true, res);
                        return;
                    }

                    if(reply == null){
                        _cache[index].NoteCode2C = null;
                    }else{
                        if(reply.length > 1){
                            _cache[index].NoteCode2C = reply[reply.length -1];
                        }else{
                            _cache[index].NoteCode2C = reply[0];
                        }
                        if(_cache[index].NoteCodeRE && _cache[index].NoteCode2C){
                            _cache[index].NoteCode2C.NumDays = workingDaysBetweenDates(new Date(_cache[index].NoteCodeRE.NoteDate), new Date(_cache[index].NoteCode2C.NoteDate));
                        }else{
                            _cache[index].NoteCode2C ={}
                            _cache[index].NoteCode2C.NumDays = 0;
                        }

                    }

                    callback(false, "");
                });
            });
            if(i == len){
                async.series(_tasks, function (err, reply) {
                    if(err){
                        cb(true, reply);
                        return;
                    }
                    cb(false, "")
                });
            }
        });

    }
    function get3C(cb){
        u.log('[getDeliveryDateSched] finding 3C Date for ',_cache.length, " Orders ");
        var _tasks = [];
        var len = _cache.length - 1;
        _cache.forEach(function(e, i, a){

            _tasks.push(function (callback){
                var index = i;
                query = "SELECT [NoteDate]" +
                    " FROM [tagnet].[dbo].[tagnet_AppointmentSPN_OrderNotes] " +
                    " WHERE " +
                    " NoteCode = '3C' " +
                    " and [PartnerPrimaryOrderID] ='"+e.PartnerPrimaryOrderID+"' ";
                sql.tagnet(query, function(err, reply){
                    if(err){
                        callback(true, res);
                        return;
                    }

                    if(reply == null){
                        _cache[index].NoteCode3C = null;
                    }else{
                        if(reply.length > 1){
                            _cache[index].NoteCode3C = reply[reply.length -1];
                        }else{
                            _cache[index].NoteCode3C = reply[0];
                        }
                        if(_cache[index].NoteCodeRE.NoteDate && _cache[index].NoteCode3C){
                            _cache[index].NoteCode3C.NumDays = workingDaysBetweenDates(new Date(_cache[index].NoteCodeRE.NoteDate), new Date(_cache[index].NoteCode3C.NoteDate));
                        }else{
                            _cache[index].NoteCode3C = {}
                            _cache[index].NoteCode3C.NumDays = 0;
                        }

                    }

                    callback(false, "");
                });
            });
            if(i == len){
                async.series(_tasks, function (err, reply) {
                    if(err){
                        cb(true, reply);
                        return;
                    }
                    cb(false, "")
                });
            }
        });

    }
    function getMC(cb){
        u.log('[getDeliveryDateSched] finding MC Date for ',_cache.length, " Orders ");
        var _tasks = [];
        var len = _cache.length - 1;
        _cache.forEach(function(e, i, a){

            _tasks.push(function (callback){
                var index = i;
                query = "SELECT [NoteDate]" +
                    " FROM [tagnet].[dbo].[tagnet_AppointmentSPN_OrderNotes] " +
                    " WHERE " +
                    " NoteCode = 'MC' " +
                    " and [PartnerPrimaryOrderID] ='"+e.PartnerPrimaryOrderID+"' ";
                sql.tagnet(query, function(err, reply){
                    if(err){
                        callback(true, res);
                        return;
                    }


                    if(reply == null){
                        _cache[index].NoteCodeMC = null;
                    }else{
                        if(reply.length > 1){
                            _cache[index].NoteCodeMC = reply[reply.length -1];
                        }else{
                            _cache[index].NoteCodeMC = reply[0];
                        }
                        if(_cache[index].NoteCodeRE && _cache[index].NoteCodeMC ){
                            _cache[index].NoteCodeMC.NumDays = workingDaysBetweenDates(new Date(_cache[index].NoteCodeRE.NoteDate), new Date(_cache[index].NoteCodeMC.NoteDate));
                        }else{
                            _cache[index].NoteCodeMC = {};
                            _cache[index].NoteCodeMC.NumDays = 0;
                        }

                    }

                    callback(false, "");
                });
            });
            if(i == len){
                async.series(_tasks, function (err, reply) {
                    if(err){
                        cb(true, reply);
                        return;
                    }
                    cb(false, "")
                });
            }
        });

    }

    async.series([validateRequest, getOrderData, getRE, getDeliveryDateSched, get1C, get2C, get3C, getMC], function (err, reply) {
        if (err) {
            var json = JSON.stringify({
                error: true,
                errorText: reply.toString(),
                data: _cache
            });
            res.send(json);
            return;
        }
        var json = JSON.stringify({
            error: false,
            errorText: null,
            data: _cache
        });
        res.send(json);
    });
}
function workingDaysBetweenDates(startDate, endDate) {
    var millisecondsPerDay = 86400 * 1000;
    startDate.setHours(0,0,0,1);
    endDate.setHours(23,59,59,999);
    var diff = endDate - startDate;
    var days = Math.ceil(diff / millisecondsPerDay);

    // Subtract two weekend days for every week in between
    var weeks = Math.floor(days / 7);
    days = days - (weeks * 2);

    // Handle special cases
    var startDay = startDate.getDay();
    var endDay = endDate.getDay();

    // Remove weekend not previously removed.
    if (startDay - endDay > 1)
        days = days - 2;

    // Remove start day if span starts on Sunday but ends before Saturday
    if (startDay === 0 && endDay != 6)
        days = days - 1 ;

    // Remove end day if span ends on Saturday but starts after Sunday
    if (endDay === 6 && startDay !== 0)
        days = days - 1  ;

    return days;
}