sqlquerybuilder - npm Package Compare versions

Comparing version 0.0.54 to 0.0.55

lib/cudMethods.js

@@ -52,9 +52,4 @@ 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));
-                        }
+                        if (self._isDateTimeField(objs[i]) && (foundPattern = self.slashedDateRegex.exec(valuesInput[objs[i]])))
+                            valuesInput[objs[i]] = valuesInput[objs[i]].replace(foundPattern[0], RegExp.$3 + '-' + RegExp.$1.substring(0, 2) + '-' + RegExp.$2.substring(0, 2));
                         vals.push("'" + valuesInput[objs[i]].replace(/'/g, "''") + "'");  //makes it a string
@@ -61,0 +56,0 @@ }

lib/index.js

@@ -22,3 +22,5 @@ var uuid = require('node-uuid'), pluralizer = require('pluralizer'), _ = require('lodash');
         tables: opt.tables || [],
-        sqlFunctions: opt.sqlFunctions || ['count', 'sum', 'isnull', 'convert', 'cast']
+        sqlFunctions: opt.sqlFunctions || ['count', 'sum', 'isnull', 'convert', 'cast'],
+        dateTimeInclusions: opt.dateTimeInclusions || ['Created','LastModified'],
+        dateTimeExclusions: opt.dateTimeExclusions || ['GLEffectiveDate_id']
     };
@@ -70,2 +72,4 @@ };
 
+    self.slashedDateRegex = /(^[0-9]{1,2}\/)([0-9]{1,2}\/)([0-9]{4,})/;
+
     //region Common Functions
@@ -177,9 +181,4 @@ /**
                 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));
-                    }
+                    if (self._isDateTimeField(props[i]) && (foundPattern = self.slashedDateRegex.exec(values[props[i]])))
+                            values[props[i]] = values[props[i]].replace(foundPattern[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
@@ -197,2 +196,8 @@ }
 
+    self._isDateTimeField = function(fieldName) {
+        return ((fieldName.indexOf("Date") != -1 || fieldName.indexOf("Time") != -1 ||
+            options.dateTimeInclusions.indexOf(fieldName) != -1) &&
+            options.dateTimeExclusions.indexOf(fieldName) == -1);
+    };
+
     return self;
@@ -199,0 +204,0 @@ }

lib/selects.js

@@ -43,4 +43,3 @@ module.exports = function selects(self) {
                 var selection = props[i];
-                var last4 = selection.slice(-4);
-                if ((last4 == "Date" || last4 == "Time") && selection.indexOf('=') == -1 && selection.indexOf('(') == -1)
+                if (self._isDateTimeField(selection) && 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)";
@@ -110,4 +109,3 @@
                 var selection = tableName;
-                var last4 = selection.slice(-4);
-                if ((last4 == "Date" || last4 == "Time") && selection.indexOf('=') == -1 && selection.indexOf('(') == -1)
+                if (self._isDateTimeField(selection) && 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)";
@@ -114,0 +112,0 @@ self._sqlObject.select += selection;

lib/where.js

@@ -37,5 +37,4 @@ var _ = require('lodash'),
                     }
-                    var last4 = objs[i].slice(-4);
-                    if (last4 == "Date" || last4 == "Time") {
-                        propertyName = "CONVERT(DATETIME," + propertyName + ",127)";
+                    if (self._isDateTimeField(objs[i])) {
+                        propertyName = "CONVERT(DATETIMEOFFSET," + propertyName + ")";
                     }
@@ -170,3 +169,3 @@ }
                     reference = subFilter.substr(0, opIndex);
-                    whereString += "CONVERT(DATETIME," + self._whereJoin(reference) + ",127)" +
+                    whereString += "CONVERT(DATETIMEOFFSET," + self._whereJoin(reference) + ")" +
                         " >= '" +
@@ -181,3 +180,3 @@ sqlescape(subFilter.substr(opIndex + 2, subFilter.length - opIndex)) +
                     reference = subFilter.substr(0, opIndex);
-                    whereString += "CONVERT(DATETIME," + self._whereJoin(reference) + ",127)" +
+                    whereString += "CONVERT(DATETIMEOFFSET," + self._whereJoin(reference) + ")" +
                         " > '" +
@@ -192,3 +191,3 @@ sqlescape(subFilter.substr(opIndex + 1, subFilter.length - opIndex)) + //.replace(/-/g, "/") +
                     reference = subFilter.substr(0, opIndex);
-                    whereString += "CONVERT(DATETIME," + self._whereJoin(reference) + ",127)" +
+                    whereString += "CONVERT(DATETIMEOFFSET," + self._whereJoin(reference) + ")" +
                         " <= '" +
@@ -203,3 +202,3 @@ sqlescape(subFilter.substr(opIndex + 2, subFilter.length - opIndex - 1)) + //.replace(/-/g, "/") +
                     reference = subFilter.substr(0, opIndex);
-                    whereString += "CONVERT(DATETIME," + self._whereJoin(reference) + ",127)" +
+                    whereString += "CONVERT(DATETIMEOFFSET," + self._whereJoin(reference) + ")" +
                         " < '" +
@@ -206,0 +205,0 @@ sqlescape(subFilter.substr(opIndex + 1, subFilter.length - opIndex - 1)) +

package.json

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

test/unit/chainingQueriesTest.js

@@ -1,8 +0,8 @@
-/**
- * Created by jordan.cotter on 9/12/2014.
- */
+var sqlQueryBuilder = require('../../lib/index'),
+    sqlBuilder = sqlQueryBuilder.queryBuilder;
 
-var sqlBuilder = require('../../lib/index').queryBuilder;
-
 describe("chaining queries should work", function(){
+    before(function () {
+        sqlQueryBuilder.init({});
+    });
 
@@ -9,0 +9,0 @@ it("with select", function(done){

test/unit/ordering/orderByTest.js

@@ -165,3 +165,3 @@ 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 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");
+        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(DATETIMEOFFSET,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");
         done();
@@ -187,5 +187,5 @@ });
             .from("Reconciliations r")
-            .orderBy("CONVERT(DATETIME,r.StatementDate,127)", "DESC")
+            .orderBy("CONVERT(DATETIMEOFFSET,r.StatementDate)", "DESC")
             .build())
-            .should.equal("SELECT * FROM Reconciliations r ORDER BY CONVERT(DATETIME,r.StatementDate,127) DESC ");
+            .should.equal("SELECT * FROM Reconciliations r ORDER BY CONVERT(DATETIMEOFFSET,r.StatementDate) DESC ");
         done();
@@ -198,7 +198,7 @@ });
             .from("Reconciliations r")
-            .orderBy("ISNULL(r.Id,0) ASC, CONVERT(DATETIME,r.StatementDate,127)", "DESC")
+            .orderBy("ISNULL(r.Id,0) ASC, CONVERT(DATETIMEOFFSET,r.StatementDate)", "DESC")
             .build())
-            .should.equal("SELECT * FROM Reconciliations r ORDER BY ISNULL(r.Id,0) ASC, CONVERT(DATETIME,r.StatementDate,127) DESC ");
+            .should.equal("SELECT * FROM Reconciliations r ORDER BY ISNULL(r.Id,0) ASC, CONVERT(DATETIMEOFFSET,r.StatementDate) DESC ");
         done();
     });
 });

test/unit/processListRequest/processListRequestTest.js

@@ -346,3 +346,3 @@ var sqlQueryBuilder = require('../../../lib/index'),
                 "FROM Yards y JOIN Addresses a ON y.address_id = a.Id " +
-                "WHERE CONVERT(DATETIME,y.InvoiceDate,127) >= '08-18-2014' AND CONVERT(DATETIME,y.InvoiceDate,127) < '08-19-2014' AND y.IsActive = 1" +
+                "WHERE CONVERT(DATETIMEOFFSET,y.InvoiceDate) >= '08-18-2014' AND CONVERT(DATETIMEOFFSET,y.InvoiceDate) < '08-19-2014' AND y.IsActive = 1" +
                 ") " +
@@ -639,3 +639,3 @@ "SELECT *, (Select MAX(Position) From SelectedItems) as 'TotalRows' FROM SelectedItems WHERE Position > 900 AND Position <= 1000 ");
         query.should
-            .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 ")
+            .equal("WITH SelectedItems AS (SELECT Id, SUBSTRING(Created,6,2) + '/' + SUBSTRING(Created,9,2) + '/' + SUBSTRING(Created,1,4) + SUBSTRING(Created,11,LEN(Created)-10) Created, SUBSTRING(LastModified,6,2) + '/' + SUBSTRING(LastModified,9,2) + '/' + SUBSTRING(LastModified,1,4) + SUBSTRING(LastModified,11,LEN(LastModified)-10) 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 ")
 
@@ -682,3 +682,3 @@
         query.should
-            .equal("WITH SelectedItems AS (SELECT Status, Description AS 'Description', CreatorFirstName AS 'CreatorFirstName', TerritoryId AS 'TerritoryId', ServiceTicketId AS 'ServiceTicketId', ServiceTicketNumber AS 'ServiceTicketNumber', YardId AS 'YardId', Yard AS 'Yard', Equipment AS 'Equipment', JobInstructions AS 'JobInstructions', Categories AS 'Categories', Created AS 'Created', Id AS 'Id', (CreatorFirstName + ' ' + CreatorLastName) AS 'CreatorName', (CASE WHEN Status = 'Rejected' Then 1 else 1 END) AS 'IsRejected', ROW_NUMBER() OVER (ORDER BY Created ASC) AS Position FROM MaintenanceAssignmentView WHERE MaintenanceAssignmentView.Status = 'Complete' AND MaintenanceAssignmentView.IsActive = 1 AND (TerritoryId IS null)) SELECT *, (Select MAX(Position) From SelectedItems) as 'TotalRows' FROM SelectedItems WHERE Position > 0 AND Position <= 100 ")
+            .equal("WITH SelectedItems AS (SELECT Status, Description AS 'Description', CreatorFirstName AS 'CreatorFirstName', TerritoryId AS 'TerritoryId', ServiceTicketId AS 'ServiceTicketId', ServiceTicketNumber AS 'ServiceTicketNumber', YardId AS 'YardId', Yard AS 'Yard', Equipment AS 'Equipment', JobInstructions AS 'JobInstructions', Categories AS 'Categories', SUBSTRING(Created,6,2) + '/' + SUBSTRING(Created,9,2) + '/' + SUBSTRING(Created,1,4) + SUBSTRING(Created,11,LEN(Created)-10) AS 'Created', Id AS 'Id', (CreatorFirstName + ' ' + CreatorLastName) AS 'CreatorName', (CASE WHEN Status = 'Rejected' Then 1 else 1 END) AS 'IsRejected', ROW_NUMBER() OVER (ORDER BY Created ASC) AS Position FROM MaintenanceAssignmentView WHERE MaintenanceAssignmentView.Status = 'Complete' AND MaintenanceAssignmentView.IsActive = 1 AND (TerritoryId IS null)) SELECT *, (Select MAX(Position) From SelectedItems) as 'TotalRows' FROM SelectedItems WHERE Position > 0 AND Position <= 100 ")
 
@@ -723,3 +723,3 @@
         query.should
-            .equal("WITH SelectedItems AS (SELECT Status, Description AS 'Description', CreatorFirstName AS 'CreatorFirstName', TerritoryId AS 'TerritoryId', ServiceTicketId AS 'ServiceTicketId', ServiceTicketNumber AS 'ServiceTicketNumber', YardId AS 'YardId', Yard AS 'Yard', Equipment AS 'Equipment', JobInstructions AS 'JobInstructions', Categories AS 'Categories', Created AS 'Created', Id AS 'Id', (CreatorFirstName + ' ' + CreatorLastName) AS 'CreatorName', (CASE WHEN Status = 'Rejected' Then 1 else 1 END) AS 'IsRejected', ROW_NUMBER() OVER (ORDER BY Created DESC) AS Position FROM MaintenanceAssignmentView WHERE MaintenanceAssignmentView.Status = 'Complete' AND MaintenanceAssignmentView.IsActive = 1 AND (TerritoryId = 'A1139D28-CE9E-4EF9-A595-A31401026A65')) SELECT *, (Select MAX(Position) From SelectedItems) as 'TotalRows' FROM SelectedItems WHERE Position > 0 AND Position <= 100 ")
+            .equal("WITH SelectedItems AS (SELECT Status, Description AS 'Description', CreatorFirstName AS 'CreatorFirstName', TerritoryId AS 'TerritoryId', ServiceTicketId AS 'ServiceTicketId', ServiceTicketNumber AS 'ServiceTicketNumber', YardId AS 'YardId', Yard AS 'Yard', Equipment AS 'Equipment', JobInstructions AS 'JobInstructions', Categories AS 'Categories', SUBSTRING(Created,6,2) + '/' + SUBSTRING(Created,9,2) + '/' + SUBSTRING(Created,1,4) + SUBSTRING(Created,11,LEN(Created)-10) AS 'Created', Id AS 'Id', (CreatorFirstName + ' ' + CreatorLastName) AS 'CreatorName', (CASE WHEN Status = 'Rejected' Then 1 else 1 END) AS 'IsRejected', ROW_NUMBER() OVER (ORDER BY Created DESC) AS Position FROM MaintenanceAssignmentView WHERE MaintenanceAssignmentView.Status = 'Complete' AND MaintenanceAssignmentView.IsActive = 1 AND (TerritoryId = 'A1139D28-CE9E-4EF9-A595-A31401026A65')) SELECT *, (Select MAX(Position) From SelectedItems) as 'TotalRows' FROM SelectedItems WHERE Position > 0 AND Position <= 100 ")
 
@@ -783,3 +783,3 @@
             +"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 CONVERT(DATETIME,Tickets.Created,127) > '2014-10-15T10:29:38.000Z' AND CONVERT(DATETIME,Tickets.Date,127) <= '2014-06-30T00:00:00.000Z' "
+            +"Yards ON Tickets.Yard_id = Yards.Id WHERE Tickets.IsActive = 1 AND CONVERT(DATETIMEOFFSET,Tickets.Created) > '2014-10-15T10:29:38.000Z' AND CONVERT(DATETIMEOFFSET,Tickets.Date) <= '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 "
@@ -1036,5 +1036,5 @@ );
 
-        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");
+        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(DATETIMEOFFSET,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");
         done();
     });
 });

test/unit/where/whereTest.js

@@ -355,5 +355,5 @@ /**
             .from("Reconciliations")
-            .where({"CONVERT(DATETIME,StatementDate,127)": '11/10/2014'}, "<")
+            .where({"CONVERT(DATETIMEOFFSET,StatementDate)": '11/10/2014'}, "<")
             .where({StatusValue: 2})
-            .build()).should.equal("SELECT Id, GLAccount_id FROM Reconciliations WHERE CONVERT(DATETIME,StatementDate,127) < '11/10/2014' AND Reconciliations.StatusValue = 2 ");
+            .build()).should.equal("SELECT Id, GLAccount_id FROM Reconciliations WHERE CONVERT(DATETIMEOFFSET,StatementDate) < '11/10/2014' AND Reconciliations.StatusValue = 2 ");
         done();
@@ -365,5 +365,5 @@ });
             .from("Reconciliations r")
-            .where({"CONVERT(DATETIME,StatementDate,127)": '11/10/2014'}, "<")
+            .where({"CONVERT(DATETIMEOFFSET,StatementDate)": '11/10/2014'}, "<")
             .where({StatusValue: 2})
-            .build()).should.equal("SELECT Id, GLAccount_id FROM Reconciliations r WHERE CONVERT(DATETIME,StatementDate,127) < '11/10/2014' AND r.StatusValue = 2 ");
+            .build()).should.equal("SELECT Id, GLAccount_id FROM Reconciliations r WHERE CONVERT(DATETIMEOFFSET,StatementDate) < '11/10/2014' AND r.StatusValue = 2 ");
         done();
@@ -377,3 +377,3 @@ });
             .where({StatusValue: 2})
-            .build()).should.equal("SELECT Id, GLAccount_id FROM Reconciliations WHERE CONVERT(DATETIME,Reconciliations.StatementDate,127) < '11/10/2014' AND Reconciliations.StatusValue = 2 ");
+            .build()).should.equal("SELECT Id, GLAccount_id FROM Reconciliations WHERE CONVERT(DATETIMEOFFSET,Reconciliations.StatementDate) < '11/10/2014' AND Reconciliations.StatusValue = 2 ");
         done();
@@ -387,3 +387,3 @@ });
             .where({StatusValue: 2})
-            .build()).should.equal("SELECT Id, GLAccount_id FROM Reconciliations r WHERE CONVERT(DATETIME,r.StatementDate,127) < '11/10/2014' AND r.StatusValue = 2 ");
+            .build()).should.equal("SELECT Id, GLAccount_id FROM Reconciliations r WHERE CONVERT(DATETIMEOFFSET,r.StatementDate) < '11/10/2014' AND r.StatusValue = 2 ");
         done();
@@ -397,5 +397,5 @@ });
             .where({StatusValue: 2})
-            .build()).should.equal("SELECT Id, GLAccount_id FROM Reconciliations r WHERE CONVERT(DATETIME,r.StatementDate,127) < '11/10/2014' AND r.StatusValue = 2 ");
+            .build()).should.equal("SELECT Id, GLAccount_id FROM Reconciliations r WHERE CONVERT(DATETIMEOFFSET,r.StatementDate) < '11/10/2014' AND r.StatusValue = 2 ");
         done();
     });
 });

New alerts

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

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

236080

increased by0.2%

Worsened metrics

Lines of code

4751

decreased by-0.04%

Number of low supply chain risk alerts

15

increased by7.14%

No dependency changes