sqlquerybuilder - npm Package Compare versions

Comparing version 0.0.51 to 0.0.54

lib/cudMethods.js

@@ -52,2 +52,9 @@ module.exports = function cudMethods(self) {
                     if (typeof (valuesInput[objs[i]]) == 'string' && valuesInput[objs[i]].indexOf("()") == -1 && valuesInput[objs[i]].charAt(0) != '\'') {   //makes sure it's a string and not a function
+                        var last4 = objs[i].slice(-4);
+                        if (last4 == "Date" || last4 == "Time") {
+                            // format date string to SQL time style 127
+                            var re = /(^[0-9]{1,2}\/)([0-9]{1,2}\/)([0-9]{4,})/;
+                            if ((needToReplace = re.exec(valuesInput[objs[i]])))
+                                valuesInput[objs[i]] = valuesInput[objs[i]].replace(needToReplace[0], RegExp.$3 + '-' + RegExp.$1.substring(0, 2) + '-' + RegExp.$2.substring(0, 2));
+                        }
                         vals.push("'" + valuesInput[objs[i]].replace(/'/g, "''") + "'");  //makes it a string
@@ -54,0 +61,0 @@ }

lib/index.js

@@ -22,3 +22,3 @@ var uuid = require('node-uuid'), pluralizer = require('pluralizer'), _ = require('lodash');
         tables: opt.tables || [],
-        sqlFunctions: opt.sqlFunctions || ['count', 'sum', 'isnull']
+        sqlFunctions: opt.sqlFunctions || ['count', 'sum', 'isnull', 'convert', 'cast']
     };
@@ -127,3 +127,2 @@ };
     self.from = function from(fromString, alias) {
-
         self._sqlObject.from = fromString.trim();
@@ -169,3 +168,2 @@ self._sqlObject.fromAlias = alias ? alias.trim() : self._sqlObject.from;
 
-
     self.set = function _set(values) {
@@ -180,2 +178,9 @@ if (typeof(values) == 'string') {
                 if (typeof (values[props[i]]) == 'string' && values[props[i]].indexOf("()") == -1) {   //makes sure it's a string and not a function
+                    var last4 = props[i].slice(-4);
+                    if (last4 == "Date" || last4 == "Time") {
+                        // format date string to SQL time style 127
+                        var re = /(^[0-9]{1,2}\/)([0-9]{1,2}\/)([0-9]{4,})/;
+                        if((needToReplace = re.exec(values[props[i]])))
+                            values[props[i]] = values[props[i]].replace(needToReplace[0], RegExp.$3 + '-' + RegExp.$1.substring(0, 2) + '-' + RegExp.$2.substring(0, 2));
+                    }
                     self._sqlObject.set += props[i] + " = '" + values[props[i]].replace(/'/g, "''") + "'";  //makes it a string
@@ -194,2 +199,24 @@ }
     return self;
-}
+}
+
+String.prototype.regexIndexOf = function(regex, startpos) {
+    var indexOf = this.substring(startpos || 0).search(regex);
+    return (indexOf >= 0) ? (indexOf + (startpos || 0)) : indexOf;
+};
+
+String.prototype.regexLastIndexOf = function(regex, startpos) {
+    regex = (regex.global) ? regex : new RegExp(regex.source, "g" + (regex.ignoreCase ? "i" : "") + (regex.multiLine ? "m" : ""));
+    if(typeof (startpos) == "undefined") {
+        startpos = this.length;
+    } else if(startpos < 0) {
+        startpos = 0;
+    }
+    var stringToWorkWith = this.substring(0, startpos + 1);
+    var lastIndexOf = -1;
+    var nextStop = 0;
+    while((result = regex.exec(stringToWorkWith)) !== null) {
+        lastIndexOf = result.index;
+        regex.lastIndex = ++nextStop;
+    }
+    return lastIndexOf;
+};

lib/joins.js

@@ -64,3 +64,2 @@ var JOINTYPE = {
 
-
         if (!alias) {
@@ -223,3 +222,3 @@ if (joinString.toUpperCase().indexOf(" ON ") == -1) {   //if there is no 'on' then join[table] will just be the trimmed string
 
-            if (self._sqlObject.joins.join[i].toUpperCase().indexOf(' ON ') != -1) {                                                //is it full criteria
+            if (self._sqlObject.joins.join[i].toUpperCase().indexOf(' ON ') != -1) {                                    //is it full criteria
                 continue;
@@ -235,9 +234,9 @@ }
             joinString += " ON ";
-            if (self._sqlObject.joins.joinOn[i] && self._sqlObject.joins.joinOn[i].indexOf(".") == -1)                                            //table name not included?
-                self._sqlObject.joins.joinOn[i] = self._sqlObject.fromAlias + "." + self._sqlObject.joins.joinOn[i];                                   //add table name
+            if (self._sqlObject.joins.joinOn[i] && self._sqlObject.joins.joinOn[i].indexOf(".") == -1)                  //table name not included?
+                self._sqlObject.joins.joinOn[i] = self._sqlObject.fromAlias + "." + self._sqlObject.joins.joinOn[i];    //add table name
 
-            if (self._sqlObject.joins.joinTo[i] && self._sqlObject.joins.joinTo[i]) {                                                             //are there 3 parameters
+            if (self._sqlObject.joins.joinOn[i] && self._sqlObject.joins.joinTo[i]) {                                   //are there 3 parameters
                 joinString += self._sqlObject.joins.joinOn[i] + " = ";
 
-                if (self._sqlObject.joins.joinTo[i].indexOf(".") == -1)                                                             //table name not included?
+                if (self._sqlObject.joins.joinTo[i].indexOf(".") == -1)                                                 //table name not included?
                     joinString += joinTable + ".";
@@ -247,22 +246,27 @@
             }
-            else if (self._sqlObject.joins.joinOn[i]) {                                                                             //are there 2 parameters
-                joinString += self._sqlObject.joins.joinOn[i] + " = " + joinTable + ".Id";                                          //given prop joins to given table's Id field
-            } else if (self._sqlObject.joins.join[i].indexOf("(") != -1) {                                                          //for subqueries
-                //locates and extracts table name from subquery
-                var index = self._sqlObject.joins.join[i].indexOf("FROM");
+            else if (self._sqlObject.joins.joinOn[i]) {                                                                 //are there 2 parameters
+                joinString += self._sqlObject.joins.joinOn[i] + " = " + joinTable + ".Id";                              //given prop joins to given table's Id field
+            }
+            else if (self._sqlObject.joins.join[i].indexOf("(") != -1) {                                                //for subqueries
+                var index = self._sqlObject.joins.join[i].indexOf("FROM");                                              //locates and extracts table name from subquery
                 var parenthesis = self._sqlObject.joins.join[i].indexOf(")", index + 5);
                 var space = self._sqlObject.joins.join[i].indexOf(" ", index + 5);
-                var table = self._sqlObject.joins.join[i].substring(index + 5, Math.min(parenthesis, (parenthesis > 0 && space > 0) ? Math.min(parenthesis, space)
-                    : Math.max(parenthesis, space)));
-                joinString += self._sqlObject.fromAlias + "." + self._depluralize(table) + "_id = " + joinTable + ".Id";         //assumes the join will be on Id's
-            } else                                                                                                    //only 1 parameter. join on Id's is assumed
-            {
-                joinString += self._sqlObject.fromAlias + "." + self._depluralize(self._sqlObject.joins.join[i]) + "_id = " + joinTable + ".Id";
+                var table = self._sqlObject.joins.join[i].substring(index + 5,
+                                                                    Math.min(parenthesis,
+                                                                             (parenthesis > 0 && space > 0) ?
+                                                                                 Math.min(parenthesis, space)
+                                                                               : Math.max(parenthesis, space)
+                                                                    )
+                );
+                joinString += self._sqlObject.fromAlias + "." + self._depluralize(table) +                              //assumes the join will be on Id's
+                    "_id = " + joinTable + ".Id";
             }
+            else {                                                                                                      //only 1 parameter. join on Id's is assumed
+                joinString += self._sqlObject.fromAlias + "." + self._depluralize(self._sqlObject.joins.join[i]) +
+                    "_id = " + joinTable + ".Id";
+            }
 
-
             if (self._sqlObject.joins.joinWhere[i]) {
                 joinString += " AND " + self._sqlObject.joins.joinWhere[i];
             }
-
         }
@@ -269,0 +273,0 @@ return joinString;

lib/ordering.js

@@ -6,7 +6,37 @@ var _ = require('lodash');
     self.orderBy = function orderBy(orderByInput, direction, skipAlias) {
-        if(!orderByInput) return self;
+        if (!orderByInput) return self;
 
-        // TODO: This will break in scenarios with e.g. "order by SUBSTRING(x,0,10)" or "order by ISNULL(x,y)"
+        var parentheticals = [],
+            openParens = [];
+
+        for (var startIndex = 0; startIndex < orderByInput.length;) {
+            var parenIndex = orderByInput.regexIndexOf(/[()]/, startIndex);
+            if (parenIndex == -1) break;
+
+            if (orderByInput[parenIndex] == '(') {
+                openParens.push(parenIndex);
+            }
+            else {
+                var startParen = openParens.pop();
+                if (openParens.length === 0) {
+                    parentheticals.push(orderByInput.substring(startParen, parenIndex + 1));
+                    orderByInput = orderByInput.substring(0, startParen) + "{" + parentheticals.length + "}" + orderByInput.substring(parenIndex + 1);
+                }
+            }
+            startIndex = parenIndex + 1;
+        }
+
         var elements = orderByInput.split(',');
-        orderByInput = _.reduce(elements, function(fullString, element, eIndex) {
+
+        for (var p = 0; p < parentheticals.length; p++) {
+            var placeHolder = "{" + (p + 1) + "}";
+            for (var e = 0; e < elements.length; e++) {
+                if (elements[e].indexOf(placeHolder) != -1) {
+                    elements[e] = elements[e].replace(placeHolder, parentheticals[p]);
+                    break;
+                }
+            }
+        }
+
+        orderByInput = _.reduce(elements, function (fullString, element, eIndex) {
             element = element.trim();
@@ -22,3 +52,3 @@ if (element.indexOf('.DisplayName') !== -1) {
             var checkForAscDesc = spaceSplits.pop().toUpperCase();
-            if(checkForAscDesc == 'ASC' || checkForAscDesc == 'DESC')
+            if (checkForAscDesc == 'ASC' || checkForAscDesc == 'DESC')
                 element = spaceSplits.join(' ');
@@ -38,3 +68,3 @@ else
                     } else {
-                        alias = _.find(self._selectAliases, function(alias) {
+                        alias = _.find(self._selectAliases, function (alias) {
                             return alias.alias == element;
@@ -49,4 +79,4 @@ });
                 }
-            } else if(!skipAlias && element.indexOf('(') !== 0) {
-                alias = _.find(self._selectAliases, function(alias) {
+            } else if (!skipAlias && element.indexOf('(') !== 0) {
+                alias = _.find(self._selectAliases, function (alias) {
                     return alias.alias == element;
@@ -70,3 +100,3 @@ });
             return fullString + element;
-        },"");
+        }, "");
 
@@ -73,0 +103,0 @@ self._sqlObject.orderBy = orderByInput;

lib/selects.js

@@ -32,8 +32,8 @@ module.exports = function selects(self) {
             self._sqlObject.select += sel;
-        } else if (sel instanceof Array) {
-            self._sqlObject.select += "";
-            for (var a = 0; a < sel.length; a++) {
-                self._sqlObject.select += sel[a];
-                self._sqlObject.select += (a == sel.length - 1) ? "" : ", ";
-            }
+//        } else if (sel instanceof Array) {
+//            self._sqlObject.select += "";
+//            for (var a = 0; a < sel.length; a++) {
+//                self._sqlObject.select += sel[a];
+//                self._sqlObject.select += (a == sel.length - 1) ? "" : ", ";
+//            }
         } else if (sel instanceof Object) {
@@ -43,3 +43,8 @@ self._sqlObject.select += "";
             for (var i = 0; i < props.length; i++) {
-                self._sqlObject.select += props[i];
+                var selection = props[i];
+                var last4 = selection.slice(-4);
+                if ((last4 == "Date" || last4 == "Time") && selection.indexOf('=') == -1 && selection.indexOf('(') == -1)
+                    selection = "SUBSTRING(" + selection + ",6,2) + '/' + SUBSTRING(" + selection + ",9,2) + '/' + SUBSTRING(" + selection + ",1,4) + SUBSTRING(" + selection + ",11,LEN(" + selection + ")-10)";
+
+                self._sqlObject.select += selection;
                 self._selectProps.push(props[i]);
@@ -59,6 +64,12 @@ var aliases = sel[props[i]];
                         self._selectAliases.push({alias:alias.replace(/'/g, ""),selectProp:props[i]});
-                        if (j != aliases.length - 1) self._sqlObject.select += ", " + props[i];
+                        if (j != aliases.length - 1) self._sqlObject.select += ", " + selection;
                     }
                 } else {
                     self._selectAliases.push({alias:props[i],selectProp:props[i]});
+                    if (selection != props[i]) {
+                        var aka = props[i];
+                        if ((index = aka.indexOf('.')) != -1)
+                            aka = aka.substring(index+1);
+                        self._sqlObject.select += " " + aka;
+                    }
                 }
@@ -100,5 +111,8 @@ if (i != props.length - 1) self._sqlObject.select += ", ";
 
-                self._sqlObject.select += tableName;
+                var selection = tableName;
+                var last4 = selection.slice(-4);
+                if ((last4 == "Date" || last4 == "Time") && selection.indexOf('=') == -1 && selection.indexOf('(') == -1)
+                    selection = "SUBSTRING(" + selection + ",6,2) + '/' + SUBSTRING(" + selection + ",9,2) + '/' + SUBSTRING(" + selection + ",1,4) + SUBSTRING(" + selection + ",11,LEN(" + selection + ")-10)";
+                self._sqlObject.select += selection;
 
-
                 var aliases = selObj[props[i]];
@@ -111,6 +125,12 @@ if (aliases !== null && aliases !== "") {
                         self._selectAliases.push({alias:alias.replace(/'/g, ""),selectProp:tableName});
-                        if (j != aliases.length - 1) self._sqlObject.select += ", " + props[i];
+                        if (j != aliases.length - 1) self._sqlObject.select += ", " + tableName;
                     }
                 } else {
                     self._selectAliases.push({alias:props[i],selectProp:props[i]});
+                    if (selection != tableName) {
+                        var aka = tableName;
+                        if ((index = aka.indexOf('.')) != -1)
+                            aka = aka.substring(index+1);
+                        self._sqlObject.select += " " + aka;
+                    }
                 }
@@ -117,0 +137,0 @@ if (i != props.length - 1) self._sqlObject.select += ", ";

lib/where.js

@@ -33,6 +33,13 @@ var _ = require('lodash'),
                 var propertyName = objs[i];
-                if (propertyName.indexOf(".") == -1 && (self._sqlObject.select || self._sqlObject.fromAlias)) {
-                    propertyName = self._sqlObject.fromAlias + "." + objs[i];
+                if (propertyName.indexOf("(") == -1) {
+                    if (propertyName.indexOf(".") == -1 && self._sqlObject.fromAlias) {
+                        propertyName = self._sqlObject.fromAlias + "." + objs[i];
+                    }
+                    var last4 = objs[i].slice(-4);
+                    if (last4 == "Date" || last4 == "Time") {
+                        propertyName = "CONVERT(DATETIME," + propertyName + ",127)";
+                    }
                 }
                 self._sqlObject.where += propertyName;
+
                 if (comparator.indexOf("LIKE") != -1)
@@ -163,3 +170,3 @@ self._sqlObject.where += comparator + "'%" + where[objs[i]].substring(1, where[objs[i]].length - 1) + "%'";
                     reference = subFilter.substr(0, opIndex);
-                    whereString += self._whereJoin(reference) +
+                    whereString += "CONVERT(DATETIME," + self._whereJoin(reference) + ",127)" +
                         " >= '" +
@@ -174,3 +181,3 @@ sqlescape(subFilter.substr(opIndex + 2, subFilter.length - opIndex)) +
                     reference = subFilter.substr(0, opIndex);
-                    whereString += self._whereJoin(reference) +
+                    whereString += "CONVERT(DATETIME," + self._whereJoin(reference) + ",127)" +
                         " > '" +
@@ -185,3 +192,3 @@ sqlescape(subFilter.substr(opIndex + 1, subFilter.length - opIndex)) + //.replace(/-/g, "/") +
                     reference = subFilter.substr(0, opIndex);
-                    whereString += self._whereJoin(reference) +
+                    whereString += "CONVERT(DATETIME," + self._whereJoin(reference) + ",127)" +
                         " <= '" +
@@ -196,3 +203,3 @@ sqlescape(subFilter.substr(opIndex + 2, subFilter.length - opIndex - 1)) + //.replace(/-/g, "/") +
                     reference = subFilter.substr(0, opIndex);
-                    whereString += self._whereJoin(reference) +
+                    whereString += "CONVERT(DATETIME," + self._whereJoin(reference) + ",127)" +
                         " < '" +
@@ -199,0 +206,0 @@ sqlescape(subFilter.substr(opIndex + 1, subFilter.length - opIndex - 1)) +

package.json

 {
   "name": "sqlquerybuilder",
-  "version": "0.0.51",
+  "version": "0.0.54",
   "description": "Highly opinionated Sql Server Query Writer, mostly for internal use.",
@@ -5,0 +5,0 @@ "main": "./lib/index",

test/unit/insertTest.js

@@ -128,2 +128,24 @@ /**
     });
+
+    //INSERT w/ js date
+    it('it is called with a javascript date', function(done){
+        var obj = {Id: 'NewId()', IsActive: true, Date: '10/24/2014', Text: "test"};
+        (sqlBuilder()
+            .insertInto("aTable")
+            .values(obj)
+            .build())
+            .should.equal("INSERT INTO aTable (Id, IsActive, Date, Text) VALUES(NewId(), 1, '2014-10-24', 'test') ");
+        done();
+    });
+
+    //INSERT w/ js date and time
+    it('it is called with a javascript date and time', function(done){
+        var obj = {Id: 'NewId()', IsActive: true, Date: '11/10/2014 15:32:48.631 -06:00', Text: "test"};
+        (sqlBuilder()
+            .insertInto("aTable")
+            .values(obj)
+            .build())
+            .should.equal("INSERT INTO aTable (Id, IsActive, Date, Text) VALUES(NewId(), 1, '2014-11-10 15:32:48.631 -06:00', 'test') ");
+        done();
+    });
 });

test/unit/ordering/orderByTest.js

@@ -165,7 +165,7 @@ var sqlQueryBuilder = require('../../../lib/index'),
 
-        query.trim().should.equal("WITH SelectedItems AS (SELECT RemitTos.VendorName AS 'RemitTo.VendorName', COUNT(RemitTos.VendorName) AS 'Count', ROW_NUMBER() OVER (ORDER BY RemitTos.VendorName ASC) AS Position FROM AccountsPayableInvoices LEFT JOIN RemitTos ON AccountsPayableInvoices.RemitTo_id = RemitTos.Id WHERE AccountsPayableInvoices.IsActive = 1 AND AccountsPayableInvoices.DueDate <= '10/28/2014' AND (AccountsPayableInvoices.IsCreditCardReceipt = 0) AND (AccountsPayableInvoices.AccountsPayableInvoiceStatusValue = 3) AND (AccountsPayableInvoices.ApprovalStatusValue = 3) GROUP BY RemitTos.VendorName) SELECT *, (Select MAX(Position) From SelectedItems) as 'TotalRows' FROM SelectedItems WHERE Position > 0");
+        query.trim().should.equal("WITH SelectedItems AS (SELECT RemitTos.VendorName AS 'RemitTo.VendorName', COUNT(RemitTos.VendorName) AS 'Count', ROW_NUMBER() OVER (ORDER BY RemitTos.VendorName ASC) AS Position FROM AccountsPayableInvoices LEFT JOIN RemitTos ON AccountsPayableInvoices.RemitTo_id = RemitTos.Id WHERE AccountsPayableInvoices.IsActive = 1 AND CONVERT(DATETIME,AccountsPayableInvoices.DueDate,127) <= '10/28/2014' AND (AccountsPayableInvoices.IsCreditCardReceipt = 0) AND (AccountsPayableInvoices.AccountsPayableInvoiceStatusValue = 3) AND (AccountsPayableInvoices.ApprovalStatusValue = 3) GROUP BY RemitTos.VendorName) SELECT *, (Select MAX(Position) From SelectedItems) as 'TotalRows' FROM SelectedItems WHERE Position > 0");
         done();
     });
 
-    it('should work', function (done) {
+    it('should handle multiple ordering criteria', function (done) {
         (sqlBuilder()
@@ -182,2 +182,22 @@ .select("am.Id, am.MonthValue, ay.YearValue")
     });
+
+    it('should handle sql functions', function (done) {
+        (sqlBuilder()
+            .select("*")
+            .from("Reconciliations r")
+            .orderBy("CONVERT(DATETIME,r.StatementDate,127)", "DESC")
+            .build())
+            .should.equal("SELECT * FROM Reconciliations r ORDER BY CONVERT(DATETIME,r.StatementDate,127) DESC ");
+        done();
+    });
+
+    it('should handle multiple ordering criteria with sql functions and individual direction', function (done) {
+        (sqlBuilder()
+            .select("*")
+            .from("Reconciliations r")
+            .orderBy("ISNULL(r.Id,0) ASC, CONVERT(DATETIME,r.StatementDate,127)", "DESC")
+            .build())
+            .should.equal("SELECT * FROM Reconciliations r ORDER BY ISNULL(r.Id,0) ASC, CONVERT(DATETIME,r.StatementDate,127) DESC ");
+        done();
+    });
 });

test/unit/processListRequest/processListRequestTest.js

@@ -1,7 +0,4 @@
-/**
- * Created by jordan.cotter on 9/12/2014.
- */
-
 var sqlQueryBuilder = require('../../../lib/index'),
     sqlBuilder = sqlQueryBuilder.queryBuilder;
+
 String.prototype.replaceAll=function(find,replace){
@@ -12,2 +9,3 @@ var me = this.toString();
 };
+
 describe("when calling processListRequest, valid sql should be produced when", function(){
@@ -346,6 +344,6 @@ before(function () {
             .should.equal("WITH SelectedItems AS (" +
-                "SELECT y.Id AS 'Id', y.Name AS 'Name', a.City AS 'Address.City', a.State AS 'Address.State', (a.City+', '+a.State) AS 'Address.Description', y.InvoiceDate AS 'InvoiceDate', " +
+                "SELECT y.Id AS 'Id', y.Name AS 'Name', a.City AS 'Address.City', a.State AS 'Address.State', (a.City+', '+a.State) AS 'Address.Description', SUBSTRING(y.InvoiceDate,6,2) + '/' + SUBSTRING(y.InvoiceDate,9,2) + '/' + SUBSTRING(y.InvoiceDate,1,4) + SUBSTRING(y.InvoiceDate,11,LEN(y.InvoiceDate)-10) AS 'InvoiceDate', " +
                 "ROW_NUMBER() OVER (ORDER BY (a.City+', '+a.State) ASC) AS Position " +
                 "FROM Yards y JOIN Addresses a ON y.address_id = a.Id " +
-                "WHERE y.InvoiceDate >= '08-18-2014' AND y.InvoiceDate < '08-19-2014' AND y.IsActive = 1" +
+                "WHERE CONVERT(DATETIME,y.InvoiceDate,127) >= '08-18-2014' AND CONVERT(DATETIME,y.InvoiceDate,127) < '08-19-2014' AND y.IsActive = 1" +
                 ") " +
@@ -560,3 +558,3 @@ "SELECT *, (Select MAX(Position) From SelectedItems) as 'TotalRows' FROM SelectedItems WHERE Position > 900 AND Position <= 1000 ");
         query.should
-            .equal("WITH SelectedItems AS (SELECT f.Id, AssetNumber, ShortDescription, glc.Name AS 'GLCategory.Name', ac.Number AS 'AccountCode', oc.Name AS 'OperationalCategory.Name', f.InitialCost, f.AcquiredDate, y.Name AS 'Yard.Name', dep.Name AS 'Department.Name', f.FixedAssetTypeDisplayName AS 'FixedAssetType.DisplayName', f.VIN, po.Number AS 'PONumber', ROW_NUMBER() OVER (ORDER BY f.AcquiredDate DESC) AS Position FROM FixedAssets f JOIN AccountingProcessTypes glc ON glc.Id = f.GLCategory_id JOIN AccountCodes ac ON ac.Id = glc.AccountCode_id JOIN EquipmentTypes oc ON oc.Id = f.OperationalCategory_id JOIN Yards y ON f.Yard_id = y.Id JOIN Departments dep ON f.Department_id = dep.Id LEFT JOIN Receivers r ON f.Receiver_id = r.Id LEFT JOIN PurchaseOrders po ON po.Id = r.PurchaseOrder_id WHERE y.Name LIKE '%Okla%' AND f.IsActive = 1) SELECT *, (Select MAX(Position) From SelectedItems) as 'TotalRows' FROM SelectedItems WHERE Position > 0 AND Position <= 100 ")
+            .equal("WITH SelectedItems AS (SELECT f.Id, AssetNumber, ShortDescription, glc.Name AS 'GLCategory.Name', ac.Number AS 'AccountCode', oc.Name AS 'OperationalCategory.Name', f.InitialCost, SUBSTRING(f.AcquiredDate,6,2) + '/' + SUBSTRING(f.AcquiredDate,9,2) + '/' + SUBSTRING(f.AcquiredDate,1,4) + SUBSTRING(f.AcquiredDate,11,LEN(f.AcquiredDate)-10) AcquiredDate, y.Name AS 'Yard.Name', dep.Name AS 'Department.Name', f.FixedAssetTypeDisplayName AS 'FixedAssetType.DisplayName', f.VIN, po.Number AS 'PONumber', ROW_NUMBER() OVER (ORDER BY f.AcquiredDate DESC) AS Position FROM FixedAssets f JOIN AccountingProcessTypes glc ON glc.Id = f.GLCategory_id JOIN AccountCodes ac ON ac.Id = glc.AccountCode_id JOIN EquipmentTypes oc ON oc.Id = f.OperationalCategory_id JOIN Yards y ON f.Yard_id = y.Id JOIN Departments dep ON f.Department_id = dep.Id LEFT JOIN Receivers r ON f.Receiver_id = r.Id LEFT JOIN PurchaseOrders po ON po.Id = r.PurchaseOrder_id WHERE y.Name LIKE '%Okla%' AND f.IsActive = 1) SELECT *, (Select MAX(Position) From SelectedItems) as 'TotalRows' FROM SelectedItems WHERE Position > 0 AND Position <= 100 ")
 
@@ -595,3 +593,3 @@
         query.should
-            .equal("WITH SelectedItems AS (SELECT f.Id, AssetNumber, ShortDescription, glc.Name AS 'GLCategory.Name', ac.Number AS 'AccountCode', oc.Name AS 'OperationalCategory.Name', f.InitialCost, f.AcquiredDate, y.Name AS 'Yard.Name', dep.Name AS 'Department.Name', f.FixedAssetTypeDisplayName AS 'FixedAssetType.DisplayName', f.VIN, po.Number AS 'PONumber', ROW_NUMBER() OVER (ORDER BY f.AcquiredSource DESC) AS Position FROM FixedAssets f JOIN AccountingProcessTypes glc ON glc.Id = f.GLCategory_id JOIN AccountCodes ac ON ac.Id = glc.AccountCode_id JOIN EquipmentTypes oc ON oc.Id = f.OperationalCategory_id JOIN Yards y ON f.Yard_id = y.Id JOIN Departments dep ON f.Department_id = dep.Id LEFT JOIN Receivers r ON f.Receiver_id = r.Id LEFT JOIN PurchaseOrders po ON po.Id = r.PurchaseOrder_id WHERE (f.AcquiredSourceDisplayName = 'Purchased') AND f.IsActive = 1) SELECT *, (Select MAX(Position) From SelectedItems) as 'TotalRows' FROM SelectedItems WHERE Position > 0 AND Position <= 100 ")
+            .equal("WITH SelectedItems AS (SELECT f.Id, AssetNumber, ShortDescription, glc.Name AS 'GLCategory.Name', ac.Number AS 'AccountCode', oc.Name AS 'OperationalCategory.Name', f.InitialCost, SUBSTRING(f.AcquiredDate,6,2) + '/' + SUBSTRING(f.AcquiredDate,9,2) + '/' + SUBSTRING(f.AcquiredDate,1,4) + SUBSTRING(f.AcquiredDate,11,LEN(f.AcquiredDate)-10) AcquiredDate, y.Name AS 'Yard.Name', dep.Name AS 'Department.Name', f.FixedAssetTypeDisplayName AS 'FixedAssetType.DisplayName', f.VIN, po.Number AS 'PONumber', ROW_NUMBER() OVER (ORDER BY f.AcquiredSource DESC) AS Position FROM FixedAssets f JOIN AccountingProcessTypes glc ON glc.Id = f.GLCategory_id JOIN AccountCodes ac ON ac.Id = glc.AccountCode_id JOIN EquipmentTypes oc ON oc.Id = f.OperationalCategory_id JOIN Yards y ON f.Yard_id = y.Id JOIN Departments dep ON f.Department_id = dep.Id LEFT JOIN Receivers r ON f.Receiver_id = r.Id LEFT JOIN PurchaseOrders po ON po.Id = r.PurchaseOrder_id WHERE (f.AcquiredSourceDisplayName = 'Purchased') AND f.IsActive = 1) SELECT *, (Select MAX(Position) From SelectedItems) as 'TotalRows' FROM SelectedItems WHERE Position > 0 AND Position <= 100 ")
 
@@ -644,3 +642,3 @@
         query.should
-            .equal("WITH SelectedItems AS (SELECT Id, Created, LastModified, IsActive, ModifiedBy, TotalTax, Total, TaxableTotal, NonTaxableTotal, TaxRate, InvoiceNumber, InvoiceCustomerName, InvoiceYard, InvoiceDate, InvoiceStateDisplayName AS 'InvoiceState.DisplayName', InvoiceStateValue AS 'InvoiceState.Value', StatusDisplayName AS 'Status.DisplayName', StatusValue AS 'Status.Value', InvoiceIsProcessed, SalesTaxZoneState, SalesTaxZoneName, ROW_NUMBER() OVER (ORDER BY InvoiceNumber ASC) AS Position FROM InvoiceSalesTaxInfoView WHERE InvoiceCustomerName LIKE '%EOG Re%' AND InvoiceSalesTaxInfoView.IsActive = 1) SELECT *, (Select MAX(Position) From SelectedItems) as 'TotalRows' FROM SelectedItems WHERE Position > 0 AND Position <= 100 ")
+            .equal("WITH SelectedItems AS (SELECT Id, Created, LastModified, IsActive, ModifiedBy, TotalTax, Total, TaxableTotal, NonTaxableTotal, TaxRate, InvoiceNumber, InvoiceCustomerName, InvoiceYard, SUBSTRING(InvoiceDate,6,2) + '/' + SUBSTRING(InvoiceDate,9,2) + '/' + SUBSTRING(InvoiceDate,1,4) + SUBSTRING(InvoiceDate,11,LEN(InvoiceDate)-10) InvoiceDate, InvoiceStateDisplayName AS 'InvoiceState.DisplayName', InvoiceStateValue AS 'InvoiceState.Value', StatusDisplayName AS 'Status.DisplayName', StatusValue AS 'Status.Value', InvoiceIsProcessed, SalesTaxZoneState, SalesTaxZoneName, ROW_NUMBER() OVER (ORDER BY InvoiceNumber ASC) AS Position FROM InvoiceSalesTaxInfoView WHERE InvoiceCustomerName LIKE '%EOG Re%' AND InvoiceSalesTaxInfoView.IsActive = 1) SELECT *, (Select MAX(Position) From SelectedItems) as 'TotalRows' FROM SelectedItems WHERE Position > 0 AND Position <= 100 ")
 
@@ -784,5 +782,5 @@
         query.should.equal(
-            "WITH SelectedItems AS (SELECT Tickets.Id AS 'Id', Tickets.Total AS 'Total', Tickets.IsTurnedIn AS 'IsTurnedIn', Tickets.IsSigned AS 'IsSigned', Tickets.Date AS 'Date', CustomerShipTos.CustomerName AS 'CustomerShipTo.CustomerName', "
+            "WITH SelectedItems AS (SELECT Tickets.Id AS 'Id', Tickets.Total AS 'Total', Tickets.IsTurnedIn AS 'IsTurnedIn', Tickets.IsSigned AS 'IsSigned', SUBSTRING(Tickets.Date,6,2) + '/' + SUBSTRING(Tickets.Date,9,2) + '/' + SUBSTRING(Tickets.Date,1,4) + SUBSTRING(Tickets.Date,11,LEN(Tickets.Date)-10) AS 'Date', CustomerShipTos.CustomerName AS 'CustomerShipTo.CustomerName', "
             +"Leases.Name AS 'Lease.Name', Phases.Name AS 'Phase.Name', Yards.Name AS 'Yard.Name', ROW_NUMBER() OVER (ORDER BY Tickets.Number ASC) AS Position FROM Tickets LEFT JOIN CustomerShipTos ON Tickets.CustomerShipTo_id = CustomerShipTos.Id LEFT JOIN Leases ON Tickets.Lease_id = Leases.Id LEFT JOIN Phases ON Tickets.Phase_id = Phases.Id LEFT JOIN "
-            +"Yards ON Tickets.Yard_id = Yards.Id WHERE Tickets.IsActive = 1 AND Tickets.Created > '2014-10-15T10:29:38.000Z' AND Tickets.Date <= '2014-06-30T00:00:00.000Z' "
+            +"Yards ON Tickets.Yard_id = Yards.Id WHERE Tickets.IsActive = 1 AND CONVERT(DATETIME,Tickets.Created,127) > '2014-10-15T10:29:38.000Z' AND CONVERT(DATETIME,Tickets.Date,127) <= '2014-06-30T00:00:00.000Z' "
             +"AND (Tickets.StatusValue != 3) AND (Tickets.StatusValue != 4) AND (Tickets.StatusValue != 5)) SELECT *, (Select MAX(Position) From SelectedItems) as 'TotalRows' FROM SelectedItems WHERE Position > 0 AND Position <= 100 "
@@ -845,3 +843,3 @@ );
         query.should
-            .equal("WITH SelectedItems AS (SELECT fa.Id AS 'Id', fa.Created AS 'Created', fa.LastModified AS 'LastModified', fa.IsActive AS 'IsActive', fa.ModifiedBy AS 'ModifiedBy', fa.ShortDescription AS 'ShortDescription', fa.LongDescription AS 'LongDescription', fa.VIN AS 'VIN', RentalType = NULL, rst.Id AS 'CurrentRentalStatus.Id', rst.Created AS 'CurrentRentalStatus.Created', rst.LastModified AS 'CurrentRentalStatus.LastModified', rst.IsActive AS 'CurrentRentalStatus.IsActive', rst.ModifiedBy AS 'CurrentRentalStatus.ModifiedBy', rst.RentalStatusValue AS 'CurrentRentalStatus.RentalStatus.Value', rst.RentalStatusDisplayName AS 'CurrentRentalStatus.RentalStatus.DisplayName', 'CurrentRentalStatus.DisplayTime' = NULL, rst.Time AS 'CurrentRentalStatus.Time', 'CurrentRentalStatus.TimeParts' = NULL, rst.Notes AS 'CurrentRentalStatus.Notes', rst.TicketNumber AS 'CurrentRentalStatus.TicketNumber', 'CurrentRentalStatus.TicketId' = NULL, rst.Phase AS 'CurrentRentalStatus.Phase', l.Name AS 'CurrentRentalStatus.Lease', 'CurrentRentalStatus.PhaseType' = NULL, l.Id AS 'CurrentRentalStatus.LeaseId', RevenueAccount = NULL, ExpenseAccount = NULL, AssetAccount = NULL, UseParentsAccounts = 0, Vendor = NULL, Uom = NULL, Quantity = 0, ROW_NUMBER() OVER (ORDER BY fa.Created) AS Position FROM FixedAssets fa JOIN RentalStatusTracks rst ON fa.CurrentRentalStatus_id = rst.Id LEFT JOIN Leases l ON rst.Lease_id = l.Id WHERE (NOT fa.RentalSelectionValue = 1) AND (rst.RentalStatusValue = 1)) SELECT *, (Select MAX(Position) From SelectedItems) as 'TotalRows' FROM SelectedItems WHERE Position > 0 AND Position <= 100 ");
+            .equal("WITH SelectedItems AS (SELECT fa.Id AS 'Id', fa.Created AS 'Created', fa.LastModified AS 'LastModified', fa.IsActive AS 'IsActive', fa.ModifiedBy AS 'ModifiedBy', fa.ShortDescription AS 'ShortDescription', fa.LongDescription AS 'LongDescription', fa.VIN AS 'VIN', RentalType = NULL, rst.Id AS 'CurrentRentalStatus.Id', rst.Created AS 'CurrentRentalStatus.Created', rst.LastModified AS 'CurrentRentalStatus.LastModified', rst.IsActive AS 'CurrentRentalStatus.IsActive', rst.ModifiedBy AS 'CurrentRentalStatus.ModifiedBy', rst.RentalStatusValue AS 'CurrentRentalStatus.RentalStatus.Value', rst.RentalStatusDisplayName AS 'CurrentRentalStatus.RentalStatus.DisplayName', 'CurrentRentalStatus.DisplayTime' = NULL, SUBSTRING(rst.Time,6,2) + '/' + SUBSTRING(rst.Time,9,2) + '/' + SUBSTRING(rst.Time,1,4) + SUBSTRING(rst.Time,11,LEN(rst.Time)-10) AS 'CurrentRentalStatus.Time', 'CurrentRentalStatus.TimeParts' = NULL, rst.Notes AS 'CurrentRentalStatus.Notes', rst.TicketNumber AS 'CurrentRentalStatus.TicketNumber', 'CurrentRentalStatus.TicketId' = NULL, rst.Phase AS 'CurrentRentalStatus.Phase', l.Name AS 'CurrentRentalStatus.Lease', 'CurrentRentalStatus.PhaseType' = NULL, l.Id AS 'CurrentRentalStatus.LeaseId', RevenueAccount = NULL, ExpenseAccount = NULL, AssetAccount = NULL, UseParentsAccounts = 0, Vendor = NULL, Uom = NULL, Quantity = 0, ROW_NUMBER() OVER (ORDER BY fa.Created) AS Position FROM FixedAssets fa JOIN RentalStatusTracks rst ON fa.CurrentRentalStatus_id = rst.Id LEFT JOIN Leases l ON rst.Lease_id = l.Id WHERE (NOT fa.RentalSelectionValue = 1) AND (rst.RentalStatusValue = 1)) SELECT *, (Select MAX(Position) From SelectedItems) as 'TotalRows' FROM SelectedItems WHERE Position > 0 AND Position <= 100 ");
 
@@ -903,3 +901,3 @@ done();
         query.should
-            .equal("WITH SelectedItems AS (SELECT fa.Id AS 'Id', fa.Created AS 'Created', fa.LastModified AS 'LastModified', fa.IsActive AS 'IsActive', fa.ModifiedBy AS 'ModifiedBy', fa.ShortDescription AS 'ShortDescription', fa.LongDescription AS 'LongDescription', fa.VIN AS 'VIN', RentalType = NULL, rst.Id AS 'CurrentRentalStatus.Id', rst.Created AS 'CurrentRentalStatus.Created', rst.LastModified AS 'CurrentRentalStatus.LastModified', rst.IsActive AS 'CurrentRentalStatus.IsActive', rst.ModifiedBy AS 'CurrentRentalStatus.ModifiedBy', rst.RentalStatusValue AS 'CurrentRentalStatus.RentalStatus.Value', rst.RentalStatusValue AS 'RentalStatusValue', rst.RentalStatusDisplayName AS 'CurrentRentalStatus.RentalStatus.DisplayName', rst.RentalStatusDisplayName AS 'RentalStatusDisplayName', 'CurrentRentalStatus.DisplayTime' = NULL, rst.Time AS 'CurrentRentalStatus.Time', 'CurrentRentalStatus.TimeParts' = NULL, rst.Notes AS 'CurrentRentalStatus.Notes', rst.TicketNumber AS 'CurrentRentalStatus.TicketNumber', 'CurrentRentalStatus.TicketId' = NULL, rst.Phase AS 'CurrentRentalStatus.Phase', l.Name AS 'CurrentRentalStatus.Lease', 'CurrentRentalStatus.PhaseType' = NULL, l.Id AS 'CurrentRentalStatus.LeaseId', RevenueAccount = NULL, ExpenseAccount = NULL, AssetAccount = NULL, UseParentsAccounts = 0, Vendor = NULL, Uom = NULL, Quantity = 0, ROW_NUMBER() OVER (ORDER BY fa.Created) AS Position FROM FixedAssets fa JOIN RentalStatusTracks rst ON fa.CurrentRentalStatus_id = rst.Id LEFT JOIN Leases l ON rst.Lease_id = l.Id WHERE (NOT fa.RentalSelectionValue = 1) AND (rst.RentalStatusValue = 1)) SELECT *, (Select MAX(Position) From SelectedItems) as 'TotalRows' FROM SelectedItems WHERE Position > 0 AND Position <= 100 ");
+            .equal("WITH SelectedItems AS (SELECT fa.Id AS 'Id', fa.Created AS 'Created', fa.LastModified AS 'LastModified', fa.IsActive AS 'IsActive', fa.ModifiedBy AS 'ModifiedBy', fa.ShortDescription AS 'ShortDescription', fa.LongDescription AS 'LongDescription', fa.VIN AS 'VIN', RentalType = NULL, rst.Id AS 'CurrentRentalStatus.Id', rst.Created AS 'CurrentRentalStatus.Created', rst.LastModified AS 'CurrentRentalStatus.LastModified', rst.IsActive AS 'CurrentRentalStatus.IsActive', rst.ModifiedBy AS 'CurrentRentalStatus.ModifiedBy', rst.RentalStatusValue AS 'CurrentRentalStatus.RentalStatus.Value', rst.RentalStatusValue AS 'RentalStatusValue', rst.RentalStatusDisplayName AS 'CurrentRentalStatus.RentalStatus.DisplayName', rst.RentalStatusDisplayName AS 'RentalStatusDisplayName', 'CurrentRentalStatus.DisplayTime' = NULL, SUBSTRING(rst.Time,6,2) + '/' + SUBSTRING(rst.Time,9,2) + '/' + SUBSTRING(rst.Time,1,4) + SUBSTRING(rst.Time,11,LEN(rst.Time)-10) AS 'CurrentRentalStatus.Time', 'CurrentRentalStatus.TimeParts' = NULL, rst.Notes AS 'CurrentRentalStatus.Notes', rst.TicketNumber AS 'CurrentRentalStatus.TicketNumber', 'CurrentRentalStatus.TicketId' = NULL, rst.Phase AS 'CurrentRentalStatus.Phase', l.Name AS 'CurrentRentalStatus.Lease', 'CurrentRentalStatus.PhaseType' = NULL, l.Id AS 'CurrentRentalStatus.LeaseId', RevenueAccount = NULL, ExpenseAccount = NULL, AssetAccount = NULL, UseParentsAccounts = 0, Vendor = NULL, Uom = NULL, Quantity = 0, ROW_NUMBER() OVER (ORDER BY fa.Created) AS Position FROM FixedAssets fa JOIN RentalStatusTracks rst ON fa.CurrentRentalStatus_id = rst.Id LEFT JOIN Leases l ON rst.Lease_id = l.Id WHERE (NOT fa.RentalSelectionValue = 1) AND (rst.RentalStatusValue = 1)) SELECT *, (Select MAX(Position) From SelectedItems) as 'TotalRows' FROM SelectedItems WHERE Position > 0 AND Position <= 100 ");
 
@@ -961,3 +959,3 @@ done();
         query.should
-            .equal("WITH SelectedItems AS (SELECT fa.Id AS 'Id', fa.Created AS 'Created', fa.LastModified AS 'LastModified', fa.IsActive AS 'IsActive', fa.ModifiedBy AS 'ModifiedBy', fa.ShortDescription AS 'ShortDescription', fa.LongDescription AS 'LongDescription', fa.VIN AS 'VIN', RentalType = NULL, rst.Id AS 'CurrentRentalStatus.Id', rst.Created AS 'CurrentRentalStatus.Created', rst.LastModified AS 'CurrentRentalStatus.LastModified', rst.IsActive AS 'CurrentRentalStatus.IsActive', rst.ModifiedBy AS 'CurrentRentalStatus.ModifiedBy', rst.RentalStatusValue AS 'CurrentRentalStatus.RentalStatus.Value', rst.RentalStatusDisplayName AS 'CurrentRentalStatus.RentalStatus.DisplayName', 'CurrentRentalStatus.DisplayTime' = NULL, rst.Time AS 'CurrentRentalStatus.Time', 'CurrentRentalStatus.TimeParts' = NULL, rst.Notes AS 'CurrentRentalStatus.Notes', rst.TicketNumber AS 'CurrentRentalStatus.TicketNumber', 'CurrentRentalStatus.TicketId' = NULL, rst.Phase AS 'CurrentRentalStatus.Phase', l.Name AS 'CurrentRentalStatus.Lease', 'CurrentRentalStatus.PhaseType' = NULL, l.Id AS 'CurrentRentalStatus.LeaseId', RevenueAccount = NULL, ExpenseAccount = NULL, AssetAccount = NULL, UseParentsAccounts = 0, Vendor = NULL, Uom = NULL, Quantity = 0, ROW_NUMBER() OVER (ORDER BY fa.Created) AS Position FROM FixedAssets fa JOIN RentalStatusTracks rst ON fa.CurrentRentalStatus_id = rst.Id LEFT JOIN Leases l ON rst.Lease_id = l.Id WHERE (NOT fa.RentalSelectionValue = 1) AND (rst.RentalStatusValue = 1)) SELECT *, (Select MAX(Position) From SelectedItems) as 'TotalRows' FROM SelectedItems WHERE Position > 0 AND Position <= 100 ");
+            .equal("WITH SelectedItems AS (SELECT fa.Id AS 'Id', fa.Created AS 'Created', fa.LastModified AS 'LastModified', fa.IsActive AS 'IsActive', fa.ModifiedBy AS 'ModifiedBy', fa.ShortDescription AS 'ShortDescription', fa.LongDescription AS 'LongDescription', fa.VIN AS 'VIN', RentalType = NULL, rst.Id AS 'CurrentRentalStatus.Id', rst.Created AS 'CurrentRentalStatus.Created', rst.LastModified AS 'CurrentRentalStatus.LastModified', rst.IsActive AS 'CurrentRentalStatus.IsActive', rst.ModifiedBy AS 'CurrentRentalStatus.ModifiedBy', rst.RentalStatusValue AS 'CurrentRentalStatus.RentalStatus.Value', rst.RentalStatusDisplayName AS 'CurrentRentalStatus.RentalStatus.DisplayName', 'CurrentRentalStatus.DisplayTime' = NULL, SUBSTRING(rst.Time,6,2) + '/' + SUBSTRING(rst.Time,9,2) + '/' + SUBSTRING(rst.Time,1,4) + SUBSTRING(rst.Time,11,LEN(rst.Time)-10) AS 'CurrentRentalStatus.Time', 'CurrentRentalStatus.TimeParts' = NULL, rst.Notes AS 'CurrentRentalStatus.Notes', rst.TicketNumber AS 'CurrentRentalStatus.TicketNumber', 'CurrentRentalStatus.TicketId' = NULL, rst.Phase AS 'CurrentRentalStatus.Phase', l.Name AS 'CurrentRentalStatus.Lease', 'CurrentRentalStatus.PhaseType' = NULL, l.Id AS 'CurrentRentalStatus.LeaseId', RevenueAccount = NULL, ExpenseAccount = NULL, AssetAccount = NULL, UseParentsAccounts = 0, Vendor = NULL, Uom = NULL, Quantity = 0, ROW_NUMBER() OVER (ORDER BY fa.Created) AS Position FROM FixedAssets fa JOIN RentalStatusTracks rst ON fa.CurrentRentalStatus_id = rst.Id LEFT JOIN Leases l ON rst.Lease_id = l.Id WHERE (NOT fa.RentalSelectionValue = 1) AND (rst.RentalStatusValue = 1)) SELECT *, (Select MAX(Position) From SelectedItems) as 'TotalRows' FROM SelectedItems WHERE Position > 0 AND Position <= 100 ");
 
@@ -1019,3 +1017,3 @@ done();
         query.should
-            .equal("WITH SelectedItems AS (SELECT fa.Id AS 'Id', fa.Created AS 'Created', fa.LastModified AS 'LastModified', fa.IsActive AS 'IsActive', fa.ModifiedBy AS 'ModifiedBy', fa.ShortDescription AS 'ShortDescription', fa.LongDescription AS 'LongDescription', fa.VIN AS 'VIN', RentalType = NULL, rst.Id AS 'CurrentRentalStatus.Id', rst.Created AS 'CurrentRentalStatus.Created', rst.LastModified AS 'CurrentRentalStatus.LastModified', rst.IsActive AS 'CurrentRentalStatus.IsActive', rst.ModifiedBy AS 'CurrentRentalStatus.ModifiedBy', rst.RentalStatusValue AS 'CurrentRentalStatus.RentalStatus.Value', rst.RentalStatusDisplayName AS 'CurrentRentalStatus.RentalStatus.DisplayName', 'CurrentRentalStatus.DisplayTime' = NULL, rst.Time AS 'CurrentRentalStatus.Time', 'CurrentRentalStatus.TimeParts' = NULL, rst.Notes AS 'CurrentRentalStatus.Notes', rst.TicketNumber AS 'CurrentRentalStatus.TicketNumber', 'CurrentRentalStatus.TicketId' = NULL, rst.Phase AS 'CurrentRentalStatus.Phase', l.Name AS 'CurrentRentalStatus.Lease', 'CurrentRentalStatus.PhaseType' = NULL, l.Id AS 'CurrentRentalStatus.LeaseId', RevenueAccount = NULL, ExpenseAccount = NULL, AssetAccount = NULL, UseParentsAccounts = 0, Vendor = NULL, Uom = NULL, Quantity = 0, ROW_NUMBER() OVER (ORDER BY fa.Created) AS Position FROM FixedAssets fa JOIN RentalStatusTracks rst ON fa.CurrentRentalStatus_id = rst.Id LEFT JOIN Leases l ON rst.Lease_id = l.Id WHERE (NOT fa.RentalSelectionValue = 1) AND ((rst.RentalStatusValue = 1) OR (rst.RentalStatusValue = 2) OR (rst.RentalStatusValue = 3))) SELECT *, (Select MAX(Position) From SelectedItems) as 'TotalRows' FROM SelectedItems WHERE Position > 0 AND Position <= 100 ");
+            .equal("WITH SelectedItems AS (SELECT fa.Id AS 'Id', fa.Created AS 'Created', fa.LastModified AS 'LastModified', fa.IsActive AS 'IsActive', fa.ModifiedBy AS 'ModifiedBy', fa.ShortDescription AS 'ShortDescription', fa.LongDescription AS 'LongDescription', fa.VIN AS 'VIN', RentalType = NULL, rst.Id AS 'CurrentRentalStatus.Id', rst.Created AS 'CurrentRentalStatus.Created', rst.LastModified AS 'CurrentRentalStatus.LastModified', rst.IsActive AS 'CurrentRentalStatus.IsActive', rst.ModifiedBy AS 'CurrentRentalStatus.ModifiedBy', rst.RentalStatusValue AS 'CurrentRentalStatus.RentalStatus.Value', rst.RentalStatusDisplayName AS 'CurrentRentalStatus.RentalStatus.DisplayName', 'CurrentRentalStatus.DisplayTime' = NULL, SUBSTRING(rst.Time,6,2) + '/' + SUBSTRING(rst.Time,9,2) + '/' + SUBSTRING(rst.Time,1,4) + SUBSTRING(rst.Time,11,LEN(rst.Time)-10) AS 'CurrentRentalStatus.Time', 'CurrentRentalStatus.TimeParts' = NULL, rst.Notes AS 'CurrentRentalStatus.Notes', rst.TicketNumber AS 'CurrentRentalStatus.TicketNumber', 'CurrentRentalStatus.TicketId' = NULL, rst.Phase AS 'CurrentRentalStatus.Phase', l.Name AS 'CurrentRentalStatus.Lease', 'CurrentRentalStatus.PhaseType' = NULL, l.Id AS 'CurrentRentalStatus.LeaseId', RevenueAccount = NULL, ExpenseAccount = NULL, AssetAccount = NULL, UseParentsAccounts = 0, Vendor = NULL, Uom = NULL, Quantity = 0, ROW_NUMBER() OVER (ORDER BY fa.Created) AS Position FROM FixedAssets fa JOIN RentalStatusTracks rst ON fa.CurrentRentalStatus_id = rst.Id LEFT JOIN Leases l ON rst.Lease_id = l.Id WHERE (NOT fa.RentalSelectionValue = 1) AND ((rst.RentalStatusValue = 1) OR (rst.RentalStatusValue = 2) OR (rst.RentalStatusValue = 3))) SELECT *, (Select MAX(Position) From SelectedItems) as 'TotalRows' FROM SelectedItems WHERE Position > 0 AND Position <= 100 ");
 
@@ -1043,5 +1041,5 @@ done();
 
-        query.trim().should.equal("WITH SelectedItems AS (SELECT AccountsPayableInvoices.*, ROW_NUMBER() OVER (ORDER BY AccountsPayableInvoices.InvoiceNumber ASC) AS Position FROM AccountsPayableInvoices LEFT JOIN RemitTo ON AccountsPayableInvoices.RemitTo_id = RemitTo.Id WHERE AccountsPayableInvoices.IsActive = 1 AND (RemitTo.VendorName = 'AIRGAS USA, LLC' OR RemitTo.VendorName = 'ARCADIA OILFIELD SUPPLY, INC.' OR RemitTo.VendorName = 'ARROW PUMP & SUPPLY' OR RemitTo.VendorName = 'BEARING HEADQUARTERS COMPANY' OR RemitTo.VendorName = 'BELL SUPPLY' OR RemitTo.VendorName = 'BIG D''S RENT ALL' OR RemitTo.VendorName = 'BLUE BEACON INTERNATIONAL, INC' OR RemitTo.VendorName = 'BRAINERD CHEMICAL CO, INC.' OR RemitTo.VendorName = 'BRANDT''S INC.' OR RemitTo.VendorName = 'BRENNTAG SOUTHWEST, INC' OR RemitTo.VendorName = 'BROWN''S TRUE VALUE' OR RemitTo.VendorName = 'BRUCE DAVIS ALIGNMENT' OR RemitTo.VendorName = 'C & S SAFETY SYSTEMS OF LOUISIANA, LLC' OR RemitTo.VendorName = 'CHEMPLEX LOGISTICS SOLVAY GROUP' OR RemitTo.VendorName = 'CONTAINER SUPPLY, INC' OR RemitTo.VendorName = 'CRAWFORD SUPPLY CO., INC.' OR RemitTo.VendorName = 'ELITE AUCTION, LLC' OR RemitTo.VendorName = 'FISHER SCIENTIFIC' OR RemitTo.VendorName = 'FORMBY OIL CO.' OR RemitTo.VendorName = 'HARCROS CHEMICALS, INC.' OR RemitTo.VendorName = 'JET SPECIALTY AND SUPPLY, INC.' OR RemitTo.VendorName = 'JOHN L. LEWIS WELL SERVICE, LLC' OR RemitTo.VendorName = 'JONES TRUCK REPAIR, LLC' OR RemitTo.VendorName = 'KEYSTONE-BEARD EQUIPMENT CO.' OR RemitTo.VendorName = 'M & M SUPPLY COMPANY' OR RemitTo.VendorName = 'MACK TRUCK SALES OF TULSA, INC.' OR RemitTo.VendorName = 'MAJOR GAS COMPANY, INC' OR RemitTo.VendorName = 'MCMASTER-CARR SUPPLY' OR RemitTo.VendorName = 'Meacham Pump Repair Service' OR RemitTo.VendorName = 'MOORE''S IGA' OR RemitTo.VendorName = 'MORRIS PUMP & SUPPLY' OR RemitTo.VendorName = 'MURPHY BROS. QUICK LUBE' OR RemitTo.VendorName = 'OLG PROPANE' OR RemitTo.VendorName = 'SCOTT TIRES CENTER INC' OR RemitTo.VendorName = 'SCOTT-GREER SALES, INC.' OR RemitTo.VendorName = 'SKYE PETROLEUM, INC' OR RemitTo.VendorName = 'SOONER CONDITIONING' OR RemitTo.VendorName = 'SOONER LIFT,INC.' OR RemitTo.VendorName = 'SPECIALTY TIRE LLC' OR RemitTo.VendorName = 'V K ENTERPRISES, INC.' OR RemitTo.VendorName = 'WAYNE ENTERPRISES, INC.') AND AccountsPayableInvoices.DueDate <= '10/28/2014' AND (AccountsPayableInvoices.IsCreditCardReceipt = 0) AND (AccountsPayableInvoices.AccountsPayableInvoiceStatusValue = 3) AND (AccountsPayableInvoices.ApprovalStatusValue = 3)) SELECT *, (Select MAX(Position) From SelectedItems) as 'TotalRows' FROM SelectedItems WHERE Position > 0 AND Position <= 100");
+        query.trim().should.equal("WITH SelectedItems AS (SELECT AccountsPayableInvoices.*, ROW_NUMBER() OVER (ORDER BY AccountsPayableInvoices.InvoiceNumber ASC) AS Position FROM AccountsPayableInvoices LEFT JOIN RemitTo ON AccountsPayableInvoices.RemitTo_id = RemitTo.Id WHERE AccountsPayableInvoices.IsActive = 1 AND (RemitTo.VendorName = 'AIRGAS USA, LLC' OR RemitTo.VendorName = 'ARCADIA OILFIELD SUPPLY, INC.' OR RemitTo.VendorName = 'ARROW PUMP & SUPPLY' OR RemitTo.VendorName = 'BEARING HEADQUARTERS COMPANY' OR RemitTo.VendorName = 'BELL SUPPLY' OR RemitTo.VendorName = 'BIG D''S RENT ALL' OR RemitTo.VendorName = 'BLUE BEACON INTERNATIONAL, INC' OR RemitTo.VendorName = 'BRAINERD CHEMICAL CO, INC.' OR RemitTo.VendorName = 'BRANDT''S INC.' OR RemitTo.VendorName = 'BRENNTAG SOUTHWEST, INC' OR RemitTo.VendorName = 'BROWN''S TRUE VALUE' OR RemitTo.VendorName = 'BRUCE DAVIS ALIGNMENT' OR RemitTo.VendorName = 'C & S SAFETY SYSTEMS OF LOUISIANA, LLC' OR RemitTo.VendorName = 'CHEMPLEX LOGISTICS SOLVAY GROUP' OR RemitTo.VendorName = 'CONTAINER SUPPLY, INC' OR RemitTo.VendorName = 'CRAWFORD SUPPLY CO., INC.' OR RemitTo.VendorName = 'ELITE AUCTION, LLC' OR RemitTo.VendorName = 'FISHER SCIENTIFIC' OR RemitTo.VendorName = 'FORMBY OIL CO.' OR RemitTo.VendorName = 'HARCROS CHEMICALS, INC.' OR RemitTo.VendorName = 'JET SPECIALTY AND SUPPLY, INC.' OR RemitTo.VendorName = 'JOHN L. LEWIS WELL SERVICE, LLC' OR RemitTo.VendorName = 'JONES TRUCK REPAIR, LLC' OR RemitTo.VendorName = 'KEYSTONE-BEARD EQUIPMENT CO.' OR RemitTo.VendorName = 'M & M SUPPLY COMPANY' OR RemitTo.VendorName = 'MACK TRUCK SALES OF TULSA, INC.' OR RemitTo.VendorName = 'MAJOR GAS COMPANY, INC' OR RemitTo.VendorName = 'MCMASTER-CARR SUPPLY' OR RemitTo.VendorName = 'Meacham Pump Repair Service' OR RemitTo.VendorName = 'MOORE''S IGA' OR RemitTo.VendorName = 'MORRIS PUMP & SUPPLY' OR RemitTo.VendorName = 'MURPHY BROS. QUICK LUBE' OR RemitTo.VendorName = 'OLG PROPANE' OR RemitTo.VendorName = 'SCOTT TIRES CENTER INC' OR RemitTo.VendorName = 'SCOTT-GREER SALES, INC.' OR RemitTo.VendorName = 'SKYE PETROLEUM, INC' OR RemitTo.VendorName = 'SOONER CONDITIONING' OR RemitTo.VendorName = 'SOONER LIFT,INC.' OR RemitTo.VendorName = 'SPECIALTY TIRE LLC' OR RemitTo.VendorName = 'V K ENTERPRISES, INC.' OR RemitTo.VendorName = 'WAYNE ENTERPRISES, INC.') AND CONVERT(DATETIME,AccountsPayableInvoices.DueDate,127) <= '10/28/2014' AND (AccountsPayableInvoices.IsCreditCardReceipt = 0) AND (AccountsPayableInvoices.AccountsPayableInvoiceStatusValue = 3) AND (AccountsPayableInvoices.ApprovalStatusValue = 3)) SELECT *, (Select MAX(Position) From SelectedItems) as 'TotalRows' FROM SelectedItems WHERE Position > 0 AND Position <= 100");
         done();
     });
 });

test/unit/selectTest.js

@@ -27,9 +27,2 @@ /**
 
-    //SELECT ARRAY
-    it('passing an array into select should work', function(done){
-        var arr = ["Id", "Number", "Name"];
-        (sqlBuilder().select(arr).from("Tickets").build()).should.equal("SELECT Id, Number, Name FROM Tickets ");
-        done();
-    });
-
     //SELECT OBJ
@@ -36,0 +29,0 @@ it('passing an object into select should produce valid sql with aliases', function(done){

test/unit/updateTest.js

@@ -21,3 +21,3 @@ /**
     //UPDATE STRING
-    it('SET is called with a STRING and WHERE', function(done){
+    it('SET is called with a STRING', function(done){
         (sqlBuilder()
@@ -31,4 +31,24 @@ .update("Table")
 
+    //UPDATE NUMBER
+    it('SET is called with a NUMBER', function(done){
+        (sqlBuilder()
+            .update("Table")
+            .set("Phase = 2")
+            .build())
+            .should.equal("UPDATE Table SET Phase = 2 ");
+        done();
+    });
+
+    //UPDATE DATE
+    it('SET is called with a DATE', function(done){
+        (sqlBuilder()
+            .update("Table")
+            .set("PhaseDate = '2014-10-24'")
+            .build())
+            .should.equal("UPDATE Table SET PhaseDate = '2014-10-24' ");
+        done();
+    });
+
     //UPDATE OBJ INT
-    it('SET is called with an OBJECT with NUMBER and a WHERE', function(done){
+    it('SET is called with an OBJECT with NUMBER', function(done){
         (sqlBuilder()
@@ -43,3 +63,3 @@ .update("Table")
     //UPDATE OBJ STRING
-    it('SET is called with an OBJECT with STRING with a WHERE', function(done){
+    it('SET is called with an OBJECT with STRING', function(done){
         (sqlBuilder()
@@ -53,2 +73,22 @@ .update("Table")
 
+    //UPDATE OBJ DATE
+    it('SET is called with an OBJECT with DATE', function(done){
+        (sqlBuilder()
+            .update("Table")
+            .set({PhaseDate : '10/24/2014'})
+            .build())
+            .should.equal("UPDATE Table SET PhaseDate = '2014-10-24' ");
+        done();
+    });
+
+    //UPDATE OBJ DATE
+    it('SET is called with an OBJECT with DATE and TIME', function(done){
+        (sqlBuilder()
+            .update("Table")
+            .set({PhaseDate : '10/24/2014 15:32:48.631 -06:00'})
+            .build())
+            .should.equal("UPDATE Table SET PhaseDate = '2014-10-24 15:32:48.631 -06:00' ");
+        done();
+    });
+
     //UPDATE STRING w/ WHERE
@@ -55,0 +95,0 @@ it('SET is called with a STRING and WHERE', function(done){

New alerts

License Policy Violation

License

This package is not allowed per your license policy. Review the package's license to ensure compliance.

Found 1 instance in 1 package

Long strings

Supply chain risk

Contains long string literals, which may be a sign of obfuscated or packed code.

Found 1 instance in 1 package

Fixed alerts

License Policy Violation

License

This package is not allowed per your license policy. Review the package's license to ensure compliance.

Found 1 instance in 1 package

Long strings

Supply chain risk

Contains long string literals, which may be a sign of obfuscated or packed code.

Found 1 instance in 1 package

Improved metrics

Total package byte prevSize

235613

increased by4.99%

Lines of code

4753

increased by4.46%

Worsened metrics

Number of low supply chain risk alerts

14

increased by16.67%

No dependency changes