Skip to content

Instantly share code, notes, and snippets.

@dperussina

dperussina/onhandExample.js

Last active January 10, 2017 19:00
Show Gist options
  • Save dperussina/adcd6196aab8ccedd10108de26c7514a to your computer and use it in GitHub Desktop.
Save dperussina/adcd6196aab8ccedd10108de26c7514a to your computer and use it in GitHub Desktop.
Download ZIP
Raw
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 " +
" AND OrderState != 10 " +
" 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 ";
/*
Remove Serbice Lebel Corss Dock
Remove Canceled
*/
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){
//u.log('comparing dates ', _cache[index].DateAdded.toISOString().split('T')[0] , _cache[index].NoteCodeRE.NoteDate.toISOString().split("T")[0]);
if(_cache[index].DateAdded.toISOString().split('T')[0] == _cache[index].NoteCodeRE.NoteDate.toISOString().split("T")[0]){
_cache[index].NoteCodeRE.NumDays = 1;
}else{
_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){
if(_cache[index].NoteCodeRE.NoteDate.toISOString().split('T')[0] == _cache[index].DLActualArrive.toISOString().split("T")[0]){
_cache[index].DaysFromREtoPOD = 1;
}else{
_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%' " +
" ORDER BY LogDate DESC ";
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].NoteCodeRE.NoteDate){
if(_cache[index].NoteCodeRE.NoteDate.toISOString().split('T')[0] == _cache[index].DeliveryDateSchedueled.LogDate.toISOString().split("T")[0]){
_cache[index].DeliveryDateSchedueled.NumDays = 1;
}else {
_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.NoteDate && _cache[index].NoteCodeRE.NoteDate){
if(_cache[index].NoteCodeRE.NoteDate.toISOString().split('T')[0] == _cache[index].NoteCode1C.NoteDate.toISOString().split("T")[0]){
_cache[index].NoteCode1C.NumDays = 1;
}else{
_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.NoteDate && _cache[index].NoteCodeRE.NoteDate){
if(_cache[index].NoteCodeRE.NoteDate.toISOString().split('T')[0] == _cache[index].NoteCode2C.NoteDate.toISOString().split("T")[0]){
_cache[index].NoteCode2C.NumDays = 1;
}else {
_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.NoteDate && _cache[index].NoteCodeRE.NoteDate){
if(_cache[index].NoteCodeRE.NoteDate.toISOString().split('T')[0] == _cache[index].NoteCode3C.NoteDate.toISOString().split("T")[0]){
_cache[index].NoteCode3C.NumDays = 1;
}else {
_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.NoteDate && _cache[index].NoteCodeRE.NoteDate){
if(_cache[index].NoteCodeRE.NoteDate.toISOString().split('T')[0] == _cache[index].NoteCodeMC.NoteDate.toISOString().split("T")[0]){
_cache[index].NoteCodeMC.NumDays = 1;
}else {
_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);
});
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment