sqlquerybuilder - npm Package Compare versions

Comparing version 0.0.80 to 0.0.81

lib/where.js

@@ -117,4 +117,11 @@ var _ = require('lodash'),
                 if (opIndex !== -1) {
+                    var operator = "LIKE";
+                    if (subFilter.charAt(0) === '!') {
+                        operator = "NOT LIKE";
+                        subFilter = subFilter.substr(1);
+                        opIndex = subFilter.indexOf(".Contains");
+                        shouldAppendParenth = false;
+                    }
                     reference = subFilter.substr(0, opIndex);
-                    whereString += self._whereJoin(reference) + " LIKE '%" +
+                    whereString += self._whereJoin(reference) + " " + operator + " '%" +
                         sqlescape(subFilter.substr(opIndex + 11, subFilter.lastIndexOf("\"") - (opIndex + 11))) +
@@ -148,3 +155,3 @@ "%'";
                 if (opIndex !== -1) {
-                    var operator = '=';
+                    var comparator = '=';
                     if (subFilter.charAt(0) === '(') {
@@ -158,3 +165,3 @@ whereString += '(';
                     if (subFilter.charAt(0) === '!') {
-                        operator = '!=';
+                        comparator = '!=';
                         subFilter = subFilter.substr(1);
@@ -167,3 +174,3 @@ opIndex = subFilter.indexOf(".Equals");
                     whereString += self._whereJoin(reference) +
-                        " " + operator + " '" +
+                        " " + comparator + " '" +
                         //" = '" +
@@ -231,2 +238,3 @@ sqlescape(subFilter.substr(opIndex + 9, subFilter.lastIndexOf("\"") - (opIndex + 9))) +
                 if (opIndex != -1) {
+                    var op = " = ";
                     var data = subFilter.match(/(?:\(|)([^=]*)==([^\)]*)/);
@@ -236,4 +244,11 @@ if(data === null) continue;
                     var compareTo = data[2];
+                    if(compareTo.toLowerCase() == 'null'){
+                        op = " IS ";
+                    }
+                    if(reference.indexOf('Length') != -1){
+                        reference = reference.substring(0, reference.indexOf('.'));
+                        compareTo = "''";
+                    }
                     whereString += "(" + self._whereJoin(reference) +
-                        " = " +
+                        op +
                         convertTrueFalse(compareTo) +
@@ -240,0 +255,0 @@ ")";

package.json

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

test/unit/where/whereTest.js

@@ -419,8 +419,69 @@ var sqlQueryBuilder = require('../../../lib/index'),
             .build().should.equal("WITH SelectedItems AS (SELECT TicketView.*, "+
-            "ROW_NUMBER() OVER (ORDER BY CONVERT(DATETIMEOFFSET,TicketView.Date) DESC) "+
-            "AS Position FROM TicketView LEFT JOIN TicketFields on TicketFields.Id=TicketView.TicketField "+
-            "WHERE TicketView.Total > '0' AND TicketView.Total < '100' AND TicketView.IsActive = 1) "+
-            "SELECT *, (Select MAX(Position) From SelectedItems) as 'TotalRows' FROM SelectedItems WHERE Position > 0 AND Position <= 100 ");
+                "ROW_NUMBER() OVER (ORDER BY CONVERT(DATETIMEOFFSET,TicketView.Date) DESC) "+
+                "AS Position FROM TicketView LEFT JOIN TicketFields on TicketFields.Id=TicketView.TicketField "+
+                "WHERE TicketView.Total > '0' AND TicketView.Total < '100' AND TicketView.IsActive = 1) "+
+                "SELECT *, (Select MAX(Position) From SelectedItems) as 'TotalRows' FROM SelectedItems WHERE Position > 0 AND Position <= 100 ");
         done();
     });
+
+    it('Does not contain should work', function(done){
+        sqlBuilder()
+            .select("*")
+            .from("Employees")
+            .processListRequest({filters:"!EmployeeNumber.Contains(\"M\")&&IsActive=true",
+                sidx:"",
+                sord:"desc",
+                RenderFormat:"paged",
+                page:1,
+                pagePercent:0.0024691358024691358,
+                total:40417,
+                rows:100,
+                totalPages:405})
+            .build().should.equal("WITH SelectedItems AS (SELECT *, "+
+                                "ROW_NUMBER() OVER (ORDER BY Employees.Created) "+
+                                "AS Position FROM Employees "+
+                                "WHERE Employees.EmployeeNumber NOT LIKE '%M%' AND Employees.IsActive = 1) "+
+                                "SELECT *, (Select MAX(Position) From SelectedItems) as 'TotalRows' FROM SelectedItems WHERE Position > 0 AND Position <= 100 ");
+        done();
+    });
+    it('only blanks filter should work', function(done){
+        sqlBuilder()
+            .select("*")
+            .from("Employees")
+            .processListRequest({filters:"(SkillTwo.Length==0||SkillTwo==null)&&IsActive=true",
+                sidx:"",
+                sord:"desc",
+                RenderFormat:"paged",
+                page:1,
+                pagePercent:0.0024691358024691358,
+                total:40417,
+                rows:100,
+                totalPages:405})
+            .build().should.equal("WITH SelectedItems AS (SELECT *, "+
+                "ROW_NUMBER() OVER (ORDER BY Employees.Created) "+
+                "AS Position FROM Employees "+
+                "WHERE ((Employees.SkillTwo = '') OR (Employees.SkillTwo IS null)) AND Employees.IsActive = 1) "+
+                "SELECT *, (Select MAX(Position) From SelectedItems) as 'TotalRows' FROM SelectedItems WHERE Position > 0 AND Position <= 100 ");
+        done();
+    });
+    it('Does not equal filter should work', function(done){
+        sqlBuilder()
+            .select("*")
+            .from("Employees")
+            .processListRequest({filters:"!LastName.Equals(\"ROMAN\")&&IsActive=true",
+                sidx:"",
+                sord:"desc",
+                RenderFormat:"paged",
+                page:1,
+                pagePercent:0.0024691358024691358,
+                total:40417,
+                rows:100,
+                totalPages:405})
+            .build().should.equal("WITH SelectedItems AS (SELECT *, "+
+                "ROW_NUMBER() OVER (ORDER BY Employees.Created) "+
+                "AS Position FROM Employees "+
+                "WHERE Employees.LastName != 'ROMAN' AND Employees.IsActive = 1) "+
+                "SELECT *, (Select MAX(Position) From SelectedItems) as 'TotalRows' FROM SelectedItems WHERE Position > 0 AND Position <= 100 ");
+        done();
+    });
 });

No alert changes

Improved metrics

Total package byte prevSize

247438

increased by1.48%

Lines of code

4964

increased by1.53%

No dependency changes