sqlquerybuilder - npm Package Compare versions

Comparing version 0.0.37 to 0.0.38

lib/where.js

@@ -72,8 +72,8 @@ function convertTrueFalse(input) {
         for (var i = 0; i < filters.length; i++) {
+            var filter = filters[i];
+            if(filter === "") continue;
+
             if (whereString !== "")
                 whereString += " AND ";
 
-            var filter = filters[i];
-
-
             if (filter.indexOf('.DisplayName') !== -1) {
@@ -163,7 +163,7 @@ filter = filter.replace(/\b.DisplayName/ig, "DisplayName");
 
-            // Date filters
+            // Date filters, strict inequalities need to come after their non strict versions
             var dateComparer = filter.indexOf(">=");
             if (dateComparer != -1) {
-                var greatAlias = self._selectProps[self._selectAliases.indexOf(filter.substr(0, dateComparer))];
-                whereString += (greatAlias ? greatAlias : (filter.substr(0, dateComparer).charAt(0) === "(" ? filter.substr(0, dateComparer) : self._sqlObject.fromAlias + "." + filter.substr(0, dateComparer))) +
+                var greatEqualAlias = self._selectProps[self._selectAliases.indexOf(filter.substr(0, dateComparer))];
+                whereString += (greatEqualAlias ? greatEqualAlias : (filter.substr(0, dateComparer).charAt(0) === "(" ? filter.substr(0, dateComparer) : self._sqlObject.fromAlias + "." + filter.substr(0, dateComparer))) +
                     " >= '" +
@@ -175,2 +175,22 @@ filter.substr(dateComparer + 2, filter.length - dateComparer).replace(/-/g, "/") +
 
+            dateComparer = filter.indexOf(">");
+            if (dateComparer != -1) {
+                var greatAlias = self._selectProps[self._selectAliases.indexOf(filter.substr(0, dateComparer))];
+                whereString += (greatAlias ? greatAlias : (filter.substr(0, dateComparer).charAt(0) === "(" ? filter.substr(0, dateComparer) : self._sqlObject.fromAlias + "." + filter.substr(0, dateComparer))) +
+                    " > '" +
+                    filter.substr(dateComparer + 1, filter.length - dateComparer)+ //.replace(/-/g, "/") +
+                    "'";
+                continue;
+            }
+
+            dateComparer = filter.indexOf("<=");
+            if (dateComparer != -1) {
+                var lessEqualAlias = self._selectProps[self._selectAliases.indexOf(filter.substr(0, dateComparer))];
+                whereString += (lessEqualAlias ? lessEqualAlias : ( filter.substr(0, dateComparer).charAt(0) === "(" ? filter.substr(0, dateComparer) : self._sqlObject.fromAlias + "." + filter.substr(0, dateComparer))) +
+                    " <= '" +
+                    filter.substr(dateComparer + 2, filter.length - dateComparer - 1)+ //.replace(/-/g, "/") +
+                    "'";
+                continue;
+            }
+
             dateComparer = filter.indexOf("<");
@@ -197,5 +217,23 @@ if (dateComparer != -1) {
 
+            equalComparer = filter.indexOf('!=');
+            var pars;
+            if(equalComparer != - 1){
+                pars = filter.split('!=');
+
+                var notEqualAlias = self._selectProps[self._selectAliases.indexOf(pars[0])];
+                whereString += "(" + (notEqualAlias ? notEqualAlias : (self._sqlObject.fromAlias + "." + pars[0]));
+                whereString += pars[1].toUpperCase() === "NULL" ? " IS NOT " : " != ";
+                if (isNaN(pars[1]) && pars[1].toUpperCase() !== "NULL") {
+                    whereString += "'";
+                    whereString += convertTrueFalse(pars[1]) + "'" + ")";
+                } else {
+                    whereString += convertTrueFalse(pars[1]) +
+                        ")";
+                }
+                continue;
+            }
+
             equalComparer = filter.indexOf("=");
             if (equalComparer != -1) {
-                var pars = filter.split('=');
+                pars = filter.split('=');
 
@@ -202,0 +240,0 @@ var dropAlias2 = self._selectProps[self._selectAliases.indexOf(pars[0])];

package.json

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

test/unit/processListRequestTest.js

@@ -717,3 +717,3 @@ /**
         query.should
-            .equal("WITH SelectedItems AS (SELECT OperationalCategoryName AS 'OperationalCategoryName', count(OperationalCategoryName) AS 'Count', ROW_NUMBER() OVER (ORDER BY OperationalCategoryName ASC) AS Position FROM ServiceTicketView WHERE ServiceTicketView.IsActive = 1 AND (ServiceTicketView.StatusValue = 3) AND (ServiceTicketView.IsApproved = 0) GROUP BY OperationalCategoryName) SELECT *, (Select MAX(Position) From SelectedItems) as 'TotalRows' FROM SelectedItems WHERE Position > 0 AND Position <= 100 ")
+            .equal("WITH SelectedItems AS (SELECT OperationalCategoryName AS 'OperationalCategoryName', count(OperationalCategoryName) AS 'Count', ROW_NUMBER() OVER (ORDER BY OperationalCategoryName ASC) AS Position FROM ServiceTicketView WHERE ServiceTicketView.IsActive = 1 AND (ServiceTicketView.StatusValue = 3) AND (ServiceTicketView.IsApproved = 0) GROUP BY OperationalCategoryName) SELECT *, (Select MAX(Position) From SelectedItems) as 'TotalRows' FROM SelectedItems WHERE Position > 0 AND Position <= 100 ");
 
@@ -723,2 +723,36 @@
     });
+
+    it('should work for tickets with additional filters from monthly closing page', function(done){
+       var reqquery = {
+           filters: 'IsActive=true&&&&Created>2014-10-15T10:29:38.000Z&&Date<=2014-06-30T00:00:00.000Z&&StatusValue!=3&&StatusValue!=4&&StatusValue!=5',
+           sidx: 'Number',
+           sord: 'asc',
+           RenderFormat: 'paged',
+           page: '',
+           pagePercent: '0',
+           total: '',
+           rows: '100',
+           totalPages: '' };
+
+        var query = sqlBuilder().from("Tickets").selectJoin({
+            'Tickets.Id': 'Id',
+            'Tickets.Total': 'Total',
+            'Tickets.IsTurnedIn': 'IsTurnedIn',
+            'Tickets.IsSigned': 'IsSigned',
+            'Tickets.Date': 'Date',
+            'CustomerShipTos.CustomerName': 'CustomerShipTo.CustomerName',
+            'Leases.Name': 'Lease.Name',
+            'Phases.Name': 'Phase.Name',
+            'Yards.Name': 'Yard.Name'
+        }).processListRequest(reqquery).build();
+
+        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', "
+            +"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' "
+            +"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 "
+        );
+
+        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

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

Improved metrics

Total package byte prevSize

194579

increased by2.27%

Lines of code

4079

increased by1.64%

No dependency changes