sqlquerybuilder - npm Package Compare versions

Comparing version 0.0.82 to 0.0.83

lib/cudMethods.js

@@ -49,19 +49,29 @@ var moment = require('moment-timezone');
                     valuesInput[objs[i]] = moment(valuesInput[objs[i]]).format("YYYY-MM-DD HH:mm:ss.SSS");
-                }else if (typeof(valuesInput[objs[i]]) == 'object') {
+                }else if (typeof(valuesInput[objs[i]]) == 'object' && valuesInput[objs[i]] !== null) {
                     continue;
                 }
                 if (valuesInput[objs[i]] !== undefined) {
-
                     colNames.push(objs[i]);
                     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
-                        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
+                        if (self._isDateTimeField(objs[i])) {
+                            if (valuesInput[objs[i]] === "")
+                                valuesInput[objs[i]] = null;
+                            else {
+                                var foundPattern = self.slashedDateRegex.exec(valuesInput[objs[i]]);
+                                if (foundPattern)
+                                    valuesInput[objs[i]] = valuesInput[objs[i]].replace(foundPattern[0], RegExp.$3 + '-' + RegExp.$1.substring(0, 2) + '-' + RegExp.$2.substring(0, 2));
+                            }
+                        }
+
+                        if (valuesInput[objs[i]] !== null)
+                            valuesInput[objs[i]] = "'" + valuesInput[objs[i]].replace(/'/g, "''") + "'";  //makes it a string
                     }
                     else if (typeof(valuesInput[objs[i]]) == 'boolean') {
-                        vals.push(valuesInput[objs[i]] ? 1 : 0);
+                        valuesInput[objs[i]] = valuesInput[objs[i]] ? 1 : 0;
                     }
-                    else {
-                        vals.push(valuesInput[objs[i]]);
-                    }
+
+                    if (valuesInput[objs[i]] === null)
+                        valuesInput[objs[i]] = "NULL";
+
+                    vals.push(valuesInput[objs[i]]);
                 }
@@ -72,3 +82,2 @@ }
             self._sqlObject.values += vals.join(', ') + ')';
-
         }
@@ -75,0 +84,0 @@ else {

lib/where.js

@@ -204,9 +204,24 @@ var _ = require('lodash'),
                     }
+                    var sym = IsNotBetween ? " < '" : " > '";
+                    var blankFilter = false;
                     reference = subFilter.substr(0, opIndex);
-                    whereString += (self._isDateTimeField(reference) ? ("CONVERT(DATETIMEOFFSET," + self._whereJoin(reference) + ")" ) :  self._whereJoin(reference))+
-                        (IsNotBetween ? " < '" : " > '") +
-                    ( 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, "/") +
-                        "'";
+                    //checking for blanks filter
+                    if (reference.indexOf('Length') != -1) {
+                        blankFilter = true;
+                        reference = reference.substring(0, reference.indexOf('.'));
+                        compareTo = "''";//compare to an empty string (a blank)
+                        sym = " != '";
+                    }
+                    if(self._isDateTimeField(reference)){
+                        whereString += "CONVERT(DATETIMEOFFSET," + self._whereJoin(reference) + ")";
+                    }else{
+                        whereString += self._whereJoin(reference);
+                    }
+                    whereString += sym;
+                    if(self._isDateTimeField(reference) && self._options.useUtc){
+                        whereString += moment.tz(moment(new Date(sqlescape(subFilter.substr(opIndex + 1, subFilter.length - opIndex)))).format(f),f,self._options.tz ).utc().format(f);
+                    }else if(!blankFilter){
+                        whereString += sqlescape(subFilter.substr(opIndex + 1, subFilter.length - opIndex));
+                    }
+                    whereString += "'";
                     continue;
@@ -256,5 +271,6 @@ }
                     }
+                    //checking for blanks filter
                     if(reference.indexOf('Length') != -1){
                         reference = reference.substring(0, reference.indexOf('.'));
-                        compareTo = "''";
+                        compareTo = "''";//compare to an empty string (a blank)
                     }
@@ -261,0 +277,0 @@ whereString += "(" + self._whereJoin(reference) +

package.json

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

test/unit/where/whereTest.js

@@ -466,2 +466,22 @@ var sqlQueryBuilder = require('../../../lib/index'),
     });
+    it('No blanks filter should work', function(done){
+        sqlBuilder()
+            .select("*")
+            .from("Employees")
+            .processListRequest({filters:"SkillTwo.Length>0&&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 != '' 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){
@@ -468,0 +488,0 @@ sqlBuilder()

No alert changes

Improved metrics

Total package byte prevSize

251444

increased by0.98%

Lines of code

5050

increased by1.08%

No dependency changes