TryCF Gist

trycf-gist-1568936820309-c04a0575-ae82-2406-815e-cd1e3d8ba7e0.cfm

<cfscript>
  IT_PROJECTS = queryNew( 
    "id,department,status" , 
    "integer,varchar,varchar" ,
    [ 
		{
		  "id": 1,
		  "department": "Accounting",
		  "status": "Cancelled"
		}, {
		  "id": 2,
		  "department": "HR",
		  "status": "Cancelled"
		}, {
		  "id": 3,
		  "department": "IT",
		  "status": "Cancelled"
		}, {
		  "id": 4,
		  "department": "Health",
		  "status": "Cancelled"
		}, {
		  "id": 5,
		  "department": "HR",
		  "status": "Active"
		}, {
		  "id": 6,
		  "department": "Accounting",
		  "status": "Active"
		}, {
		  "id": 7,
		  "department": "HR",
		  "status": "Cancelled"
		}, {
		  "id": 8,
		  "department": "IT",
		  "status": "Active"
		}, {
		  "id": 9,
		  "department": "Health",
		  "status": "Cancelled"
		}, {
		  "id": 10,
		  "department": "IT",
		  "status": "Cancelled"
		}, {
		  "id": 11,
		  "department": "IT",
		  "status": "Cancelled"
		}, {
		  "id": 12,
		  "department": "IT",
		  "status": "Cancelled"
		}, {
		  "id": 13,
		  "department": "HR",
		  "status": "Cancelled"
		}, {
		  "id": 14,
		  "department": "Accounting",
		  "status": "Cancelled"
		}, {
		  "id": 15,
		  "department": "Health",
		  "status": "Active"
		}, {
		  "id": 16,
		  "department": "Accounting",
		  "status": "Active"
		}, {
		  "id": 17,
		  "department": "Accounting",
		  "status": "Active"
		}, {
		  "id": 18,
		  "department": "HR",
		  "status": "Cancelled"
		}, {
		  "id": 19,
		  "department": "IT",
		  "status": "Cancelled"
		}, {
		  "id": 20,
		  "department": "Health",
		  "status": "Cancelled"
		}
	]
  ) ;

  //writeDump(IT_PROJECTS);

  ///////////////////////////////////////////////////////////////////
  // Setup the wrkDept variable. Where does this come from?
  wrkDept = "Health" ; 
  // Setup the dept array. Where does this come from? 
  dept[1] = ["IT","Jay"] ;
  dept[2] = ["Health","Bob"] ;

  //writedump(dept);

  ///////////////////////////////////////////////////////////////////

  // Base query.
  qry = "SELECT count(*) AS theCount FROM IT_PROJECTS WHERE 1=1 " ;
  // This is our dynamic filter that we build below.
  qfilter = {} ;
  // Query options. https://helpx.adobe.com/coldfusion/cfml-reference/coldfusion-functions/functions-m-r/queryexecute.html
  opts =  { "dbtype":"query" } ;
  
  
  //////////// BUILD DYNAMIC FILTER ////////////
  
  qdept = ( wrkDept == dept[2][1] ) ? 'Health' : wrkDept ;
  /// This one is an included filter:
  qry &= " AND department = :dpt AND status = :sts " ;
  qfilter.dpt = {"value":qdept,"cfsqltype":"CFSQLVARCHAR"} ;
  qfilter.sts = {"value":"Cancelled","cfsqltype":"CFSQLVARCHAR"} ;
  
  /// Adding Dynamic ORs
  
  // Dynamically set status based on even/odd seconds.
  qStatus = ( now().second()%2==0) ? "Cancelled" : "Active" ;
  qry &= " OR ( department = :dpt2 AND status = :sts2 ) " ;
  qfilter.dpt2 = {value:"IT",cfsqltype:"CFSQLVARCHAR"} ;
  qfilter.sts2 = {value:qStatus,cfsqltype:"CFSQLVARCHAR"} ;
  
  
  /// What does the query string look like?
  // writedump(qry);
  
  //////////////////////////////////////////////
  
  // Run the query to get our final result.
  result = queryExecute( qry , qfilter , opts ) ;
  
  // The entire result object.
  //writeDump( result ) ;
  
  // Just the data.
  writeOutput("There are " & result.theCount & " records." ) ;

</cfscript>