TryCF Gist

trycf-gist-1531920902726-5ac21138-271f-76d8-a1b8-45bd67227165.cfm

<!--- 
    Simulate database queries 
--->
<cfscript>
suppNeg = queryNew("subID, companyID, suppName,O18,O19,O20,O22,WDPVC1,WDPVC2"
,"integer,integer,varchar,integer,integer,integer,integer,integer,integer"
,[ 	{"subID":1,"companyId":22,"suppName":"SuppNameA","O18":100,"O19":0,"O20": "-150","O22":0,"WDPVC1":"500","WDPVC2":"750"}
	,{"subID":1,"companyId":30,"suppName":"SuppNameA","O18":0,"O19":0,"O20": "0","O22":"0","WDPVC1":"0","WDPVC2":"0"}
]);

columntotals = queryNew("WDPVC1, WDPVC2,O18,O19,O20,O22"
   ,"integer,integer,integer,integer,integer,integer"
   ,[{"O18":100,"O19":0,"O20": "-150","O22":0,"WDPVC1":"500","WDPVC2":"750"}
	,{"O18":0,"O19":0,"O20": "0","O22":"0","WDPVC1":"0","WDPVC2":"0"}

]);
</cfscript>

<cfquery dbtype="query" name="subs">
    SELECT DISTINCT suppName, companyID, subID
    FROM suppNeg
    ORDER BY suppName ASC
</cfquery>
<br>

<!--- 
    Show Queries
--->
<cfset writeDump(suppNeg)><br>
<cfset writeDump(columntotals)>
<cfset writeDump(subs)>


<!--- 
    Calculate Totals 
--->
<cfset O18Value = valueList(columntotals.O18) />
<cfset O18total = listToArray(O18Value) />
<cfset O19Value = valueList(columntotals.O19) />
<cfset O19total = listToArray(O19Value) />
<cfset O20Value = valueList(columntotals.O20) />
<cfset O20total = listToArray(O20Value) />
<cfset O22Value = valueList(columntotals.O22) />
<cfset O22total = listToArray(O22Value) />
<cfset WDPVC1Value = valueList(columntotals.WDPVC1) />
<cfset WDPVC1total = listToArray(WDPVC1Value) />

<cfset WDPVC2Value = valueList(columntotals.WDPVC2) />
<cfset WDPVC2total = listToArray(WDPVC2Value) /> 

<!--- 
   Show totals used in CFIF/CFELSE 
--->
<cfset totals = {O18total = #ArraySum(O18total)#
, "O19total" = #ArraySum(O19total)#
, "O20total" = #ArraySum(O20total)#
, "O22total" = #ArraySum(O22total)#
, "WDPVC1total" = #ArraySum(WDPVC1total)#
, "WDPVC2total" = #ArraySum(WDPVC2total)#}>
<br>
<cfdump var="#totals#" label="Totals used in CFIF/CFELSE ">

<!--- 
   OUTPUT
--->
<br><br>Output :<hr>
<cfoutput query="subs">
     subID = #subID# companyID = #companyID# / 
     
     <cfif (arraysum(O18total)) LT 0>
          arraysum(O18total) is less than 0  
          (Value = #arraysum(O18total)#)
     <cfelseif (arraysum(O19total)) LT 0>
          arraysum(O19total) is less than 0 
          (Value = #arraysum(O19total)#)
     <cfelseif (arraysum(O20total)) LT 0>
          arraysum(O20total) is less than 0 
          (Value = #arraysum(O20total)#)
     <cfelseif (arraysum(O22total)) lt 0>
          arraysum(O22total) is less than 0 
          (Value = #arraysum(O22total)#)
     <cfelseif (arraysum(WDPVC1total)) LT 0>
          arraysum(WDPVC1total) is less than 0 
          (Value = #arraysum(WDPVC1total)#)
     <cfelseif (arraysum(WDPVC2total)) LT 0>
          arraysum(WDPVC2total) is less than 0 
          (Value = #arraysum(WDPVC2total)#)
          
     <cfelse>
         None of the totals are less than 0 
     </cfif>
   <br>
</cfoutput>