sqlquerybuilder - npm Package Compare versions

Comparing version 0.0.76 to 0.0.77

lib/index.js

@@ -22,2 +22,4 @@ var uuid = require('node-uuid'), pluralizer = require('pluralizer'), _ = require('lodash');
         tables: opt.tables || [],
+        useUtc: opt.useUtc || true,
+        tz: opt.tz ||"America/Chicago",
         sqlFunctions: opt.sqlFunctions || ['count', 'sum', 'isnull', 'convert', 'cast'],
@@ -24,0 +26,0 @@ dateTimeInclusions: opt.dateTimeInclusions || ['Created','LastModified'],

lib/where.js

 var _ = require('lodash'),
+    moment = require('moment-timezone'),
     sqlescape = require('../services/sqlescape');
@@ -165,2 +166,3 @@
                 // Date filters, strict inequalities need to come after their non strict versions
+                var f="YYYY-MM-DD HH:mm:ss.SSS";
                 opIndex = subFilter.indexOf(">=");
@@ -171,3 +173,5 @@ if (opIndex != -1) {
                         " >= '" +
-                        sqlescape(subFilter.substr(opIndex + 2, subFilter.length - opIndex)) +
+                    (self._isDateTimeField(reference) && self._options.useUtc?
+                        moment.tz(moment(new Date(sqlescape(subFilter.substr(opIndex + 2, subFilter.length - opIndex)))).format(f),f,self._options.tz ).utc().format(f)
+                        : sqlescape(subFilter.substr(opIndex + 2, subFilter.length - opIndex)) )+
                         "'";
@@ -182,3 +186,5 @@ continue;
                         " > '" +
-                        sqlescape(subFilter.substr(opIndex + 1, subFilter.length - opIndex)) + //.replace(/-/g, "/") +
+                    ( self._isDateTimeField(reference) && self._options.useUtc?
+                        moment.tz(moment(new Date(sqlescape(subFilter.substr(opIndex + 1, subFilter.length - opIndex)))).format(f),f,self._options.tz ).utc().format(f)
+                        : sqlescape(subFilter.substr(opIndex + 1, subFilter.length - opIndex))) + //.replace(/-/g, "/") +
                         "'";
@@ -193,3 +199,5 @@ continue;
                         " <= '" +
-                        sqlescape(subFilter.substr(opIndex + 2, subFilter.length - opIndex - 1)) + //.replace(/-/g, "/") +
+                    ( self._isDateTimeField(reference) && self._options.useUtc?
+                        moment.tz(moment(new Date(sqlescape(subFilter.substr(opIndex + 2, subFilter.length - opIndex - 1)))).format(f),f,self._options.tz ).utc().format(f)
+                        : sqlescape(subFilter.substr(opIndex + 2, subFilter.length - opIndex - 1))) + //.replace(/-/g, "/") +
                         "'";
@@ -204,3 +212,5 @@ continue;
                         " < '" +
-                        sqlescape(subFilter.substr(opIndex + 1, subFilter.length - opIndex - 1)) +
+                    ( self._isDateTimeField(reference) && self._options.useUtc?
+                        moment.tz(moment(new Date(sqlescape(subFilter.substr(opIndex + 1, subFilter.length - opIndex - 1)))).format(f),f,self._options.tz ).utc().format(f)
+                        : sqlescape(subFilter.substr(opIndex + 1, subFilter.length - opIndex - 1))) +
                         "'";
@@ -207,0 +217,0 @@ continue;

package.json

 {
   "name": "sqlquerybuilder",
-  "version": "0.0.76",
+  "version": "0.0.77",
   "description": "Highly opinionated Sql Server Query Writer, mostly for internal use.",
@@ -15,3 +15,4 @@ "main": "./lib/index",
     "lodash": "^2.4.1",
-    "pluralizer": "^1.0.1"
+    "pluralizer": "^1.0.1",
+    "moment-timezone": "^0.2.4"
   },
@@ -18,0 +19,0 @@ "devDependencies": {

test/unit/ordering/orderByTest.js

@@ -166,3 +166,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(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");
+        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) <= '2014-10-28 05:00:00.000' 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();
@@ -169,0 +169,0 @@ });

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(DATETIMEOFFSET,y.InvoiceDate) >= '08-18-2014' AND CONVERT(DATETIMEOFFSET,y.InvoiceDate) < '08-19-2014' AND y.IsActive = 1" +
+                "WHERE CONVERT(DATETIMEOFFSET,y.InvoiceDate) >= '2014-08-18 05:00:00.000' AND CONVERT(DATETIMEOFFSET,y.InvoiceDate) < '2014-08-19 05:00:00.000' AND y.IsActive = 1" +
                 ") " +
@@ -780,3 +780,3 @@ "SELECT *, (Select MAX(Position) From SelectedItems) as 'TotalRows' FROM SelectedItems WHERE Position > 900 AND Position <= 1000 ");
             +"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(DATETIMEOFFSET,Tickets.Created) > '2014-10-15T10:29:38.000Z' AND CONVERT(DATETIMEOFFSET,Tickets.Date) <= '2014-06-30T00:00:00.000Z' "
+            +"Yards ON Tickets.Yard_id = Yards.Id WHERE Tickets.IsActive = 1 AND CONVERT(DATETIMEOFFSET,Tickets.Created) > '2014-10-15 10:29:38.000' AND CONVERT(DATETIMEOFFSET,Tickets.Date) <= '2014-06-30 00:00:00.000' "
             +"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 "
@@ -1069,5 +1069,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(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");
+        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) <= '2014-10-28 05:00:00.000' 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();
     });
 });

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

243214

increased by0.53%

Lines of code

4878

increased by0.25%

Worsened metrics

Dependency count

3

increased by50%

Dependency changes

• + Addedmoment-timezone@^0.2.4

• + Addedmoment@2.30.1(transitive)

• + Addedmoment-timezone@0.2.5(transitive)