asked on
Need to dynamically append query values to params in Coldfusion
I'm creating a web app that will allow the users to allocate assigned forecast data to people who report to them However, they can only submit data if certain conditions are met. These validation rules were created in JavaScript.
The developer who originally wrote the code did not account for the primary key in the query to ever change. If it does then I would have to manually go into the code and make changes every time.
When the forecast ID's have certain case values certain cfparams are created. However like I said eariler. If these ID's ever change then I will have a lot of problems manually creating new code. How do I account for this? Also will I need to change the JavaScript code? If so how because I am very new to JavaScript and I know very little
I have attached the code snippet and I have included commented code that points out in more detail what I'm referring too. Especially if you turn word wrap on in Dreamweaver
The developer who originally wrote the code did not account for the primary key in the query to ever change. If it does then I would have to manually go into the code and make changes every time.
When the forecast ID's have certain case values certain cfparams are created. However like I said eariler. If these ID's ever change then I will have a lot of problems manually creating new code. How do I account for this? Also will I need to change the JavaScript code? If so how because I am very new to JavaScript and I know very little
I have attached the code snippet and I have included commented code that points out in more detail what I'm referring too. Especially if you turn word wrap on in Dreamweaver
<!--- Here is the component used for this page --->
<cfcomponent>
<cffunction name="QuotaData" access="remote" returntype="any" output="true">
<cfargument name="VPPosnbr" required="true">
<cfargument name="CurrentDate" required="true">
<!--- This query is created inside a component. The forecast ID is the primary key. --->
<cfquery name="GetQuotaInfo" datasource="#request.datasource#">
select a.forecast_ID, sum(a.Forecast) as Forecast, a.VPPOSNBR, a.CHANNEL, a.status, b.PRODUCT, b.Description
from QA_FORECAST_INPUT a, QA_FORECAST_QUOTA_TYPE b
where b.FORECAST_ID = a.FORECAST_ID
and a.VPPOSNBR = '#arguments.VPPosnbr#'
AND CycleDate = LAST_DAY(TO_DATE('#arguments.CurrentDate#','mm/dd/yyyy'))
and a.Status = 'A'
group by a.forecast_id, b.PRODUCT, a.VPPOSNBR, a.CHANNEL, a.status, b.Description
ORDER BY Forecast_ID
</cfquery>
</cffunction>
</cfcomponent>
<!--- Here the queryinside the component is invoked --->
<cfinvoke component="Components.VP" method="QuotaData" returnvariable="rtnQuotaData">
<cfinvokeargument name="VPPosnbr" value="#VPPositionNumber#">
<cfinvokeargument name="CurrentDate" value="#CycleDate#">
</cfinvoke>
<!--- Here the component query is looped through --->
<cfloop query="rtnQuotaData">
<!--- Here is where the forecast_ID needs to be dynamically appended to the forecast value. The reason is because IF the forecast
ID is ever edited or changed in the database then I would have to manually insert all the new forecast IDs and create new cfparams as well as edit the JavaScript functions --->
<cfswitch expression="#Forecast_ID#">
<cfcase value="3">
<cfparam name="assigned_postpay_quota" default="#Forecast#">
<cfparam name="assigned_postpay_percentage" default="0">
<cfparam name="assigned_postpay_quota_total" default="0">
<cfparam name="assigned_postpay_percentage_total" default="0">
</cfcase>
<cfcase value="4">
<cfparam name="assigned_prepay_quota" default="#Forecast#">
<cfparam name="assigned_prepay_percentage" default="0">
<cfparam name="assigned_prepay_quota_total" default="0">
<cfparam name="assigned_prepay_percentage_total" default="0">
</cfcase>
<cfcase value="5">
<cfparam name="assigned_cr_quota" default="#Forecast#">
<cfparam name="assigned_cr_percentage" default="0">
<cfparam name="assigned_cr_quota_total" default="0">
<cfparam name="assigned_cr_percentage_total" default="0">
</cfcase>
</cfswitch>
</cfloop>
</cfif>
<!--- The JavaScript below checks to make sure the forecast assigned to the VP are allocated to all sales managers and the total allocated equals 100% of the forecast assigned but doesn't exceed 105%--->
<script>
function check_totals(){
var check1 = document.getElementById("assigned_postpay_quota_total").value;
var check2 = document.getElementById("assigned_postpay_percentage_total").value;
var check3 = document.getElementById("assigned_prepay_quota_total").value;
var check4 = document.getElementById("assigned_prepay_percentage_total").value;
var check5 = document.getElementById("assigned_cr_quota_total").value;
var check6 = document.getElementById("assigned_cr_percentage_total").value;
var pass1 = false;
var pass2 = false;
//Check that % values are between 100 - 105
if(check1 > 0 && check3 > 0 && check5 > 0){
if(check2 >= 100 && check2 <= 105 && check4 >= 100 && check4 <= 105 && check6 >= 100 && check6 <= 105){
pass1 = true;
}else{
pass1 = false;
}
}else{
pass1 = false;
}
var check_mgr_count = document.getElementById("total_mgrs").value;
//Check that each Manager has a vaule greater then 0 assigned for each column
for(i=1;i<=check_mgr_count;i++){
var tempElementName1 = i+"_postpay";
var tempElementName2 = i+"_prepay";
var tempElementName3 = i+"_cr";
//alert(document.getElementById(tempElementName1).value.length+" : "+document.getElementById(tempElementName2).value.length+" : "+document.getElementById(tempElementName3).value.length);
if(document.getElementById(tempElementName1).value != 0 && document.getElementById(tempElementName1).value.length != 0){
pass2 = true;
}else{
pass2 = false;
break;
}
if(document.getElementById(tempElementName2).value != 0 && document.getElementById(tempElementName2).value.length != 0){
pass2 = true;
}else{
pass2 = false;
break;
}
if(document.getElementById(tempElementName3).value != 0 && document.getElementById(tempElementName3).value.length != 0){
pass2 = true;
}else{
pass2 = false;
break;
}
}
if(pass1 == true && pass2 == true){
return true;
}else if(pass1 == false && pass2 == false){
//Quota assigned has not been completely dispursed
alert("One or more of your quota components does not sum to be equal to or greater than the assigned forecast amount. Please allocate so that the total assigned to all managers equals or exceeds the forecast value.");
//One or more managers have a zero assigned quota
alert("You have a location with a zero assigned forecast value. Please enter a value greater than zero.");
return false;
}else if(pass1 == false){
//Quota assigned has not been completely dispursed
alert("One or more of your quota components does not sum to be equal to or greater than the assigned forecast amount. Please allocate so that the total assigned to all managers equals or exceeds the forecast value.");
return false;
}else if(pass2 == false){
//One or more managers have a zero assigned quota
alert("You have a location with a zero assigned forecast value. Please enter a value greater than zero.");
return false;
}
}
</script>
<form action="dsp_vp_retail.cfm" method="post" name="VPQuotaForm" onSubmit="return check_totals()">
<table cellpadding="4" cellspacing="0" border="0" width="98%">
<tr>
<td align="left"><strong>Vice President Retail Input Screen</strong></td>
</tr>
<tr>
<td>
<cfif errorFlag EQ 1>
<font color="red">
<p>The insert function for this data has failed.<br> There was an error while attempting to insert the data into the datebase.<br> Please try again.</p>
</font>
<cfelseif errorFlag EQ 2>
<font color="blue">
<p>You have already entered data for this cycle. The data you attempted to insert has been ignored.</p>
</font>
</cfif>
</td>
</tr>
<tr>
<td>
<table cellpadding="4" cellspacing="0" border="0" width="98%">
<cfloop query="rtnVPInfo">
<tr>
<td width="20%">Vice President Retail</td>
<td width="20%">#LName#, #FName#</td>
<td width="28%"> </td>
<td width="15%"> </td>
<td width="15%">Status</td>
</tr>
<tr>
<td>Position</td>
<td>#PosNbr#</td>
<td> </td>
<td>VP Assignment</td>
<td>[Complete or Incomplete]</td>
</tr>
<tr>
<td> </td>
<td> </td>
<td> </td>
<td>Manager Assignment</td>
<td>[Complete or Incomplete]</td>
</tr>
<tr>
<td> </td>
<td> </td>
<td> </td>
<td>VP Approval</td>
<td>[Complete or Incomplete]</td>
</tr>
<tr>
<td>Rollup Cycle</td>
<td>[April 08]</td>
<td colspan="3"> </td>
</tr>
</cfloop>
</table>
</td>
</tr>
<tr>
<td>
<table cellpadding="4" cellspacing="0" border="0" width="98%">
<tr>
<td colspan="11" align="center"><strong>Quota Component</strong></td>
</tr>
<tr>
<td> </td>
<cfloop from="1" to="#rtnQuotaData.recordcount#" index="i">
<td>#rtnQuotaData.Product[i]#</td>
<td> </td>
</cfloop>
<!--- <td> </td>
<td>Post Pay</td>
<td> </td>
<td>Pre Pay</td>
<td> </td>
<td>CR</td>
<td> </td>
<td>Vacant</td>
<td> </td>
<td>Vacant</td>
<td> </td> --->
</tr>
<cfif isQuery(rtnQuotaData)>
<tr>
<td>Assigned to VP</td>
<!---
Below the TD cells are repeated for the number of Forecast_ID's there are in the report.
The TD outdie the cloop balance out the table. If the finial to qouta types are added
the 5 sets of <td> </td> can be deleted and the cfloop will account for the full
row of <td> cells
--->
<cfloop query="rtnQuotaData">
<td>
#Forecast# <!--- <input type="hidden" name="InputCheck_#Forecast_ID#" value="#VP_Input#"> --->
</td> <input type="hidden" name="InputCheck_#Forecast_ID#" value="InputCheck_#Forecast_ID#">
<td> </td>
</cfloop>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
</tr>
<cfelse>
<tr>
<td>Assigned to VP</td>
<td colspan="10">There is no new data to display at this time.</td>
</tr>
</cfif>
<tr>
<td colspan="11"><hr></td>
</tr>
<tr>
<td colspan="11"><strong>Assigned by VP to Sales Manager</strong></td>
</tr>
<!--- Heere is where the JavaScript functions are called. The user must meet the following criteria before submitting
If True If False
Assigned quotas must equal or exceed the assigned target for each component category Allow Submit Deny Submit-Show Message
One or more of your quota components does not sum to be equal to or greater than the assigned forecastamount.
Please allocate so that
the total assigned to all
managers equals or
exceeds the forecast
value.
2. Assigned component sums for are less than or equal to 105% of assigned forecast Allow Submit Notify Manager with option
to go back or submit. "One or more of your quota
components exceed 105% of
the required amount."
Please adjust your quota
assignments to be as close to
but no less than 100%.
3. Individual Sales Managers have assigned values greater than 0 Allow Submit Notify Manager with option to
go back or submit.
You have a location with a
zero assigned forecast value"
Please enter a value greater
than zero.
--->
<cfif isQuery(rtnSaleMgrLs)>
<cfset tempRowCount = rtnSaleMgrLs.RecordCount>
<script>
function postpay_func(x){
var ID_Element = x.name;
var InputRows = #tempRowCount#;
var tempQuota = #assigned_postpay_quota#;
var tempQuotaTotal = 0;
var tempQuotaTotalPercent = 0;
var percentQuota = (x.value/tempQuota)*100;
percentQuota = percentQuota.toFixed(2);
document.getElementById(ID_Element+"_precent").innerText = percentQuota+"%";
for(i=1;i<=InputRows;i++){
var tempElementName1 = i+"_postpay";
var tempElementName2 = i+"_postpay_precent";
if(document.getElementById(tempElementName1).value != null && document.getElementById(tempElementName1).value != 0 && document.getElementById(tempElementName1).value != ""){
tempQuotaTotal = parseFloat(document.getElementById(tempElementName1).value)+ parseFloat(tempQuotaTotal);
tempQuotaTotalPercent = parseFloat(document.getElementById(tempElementName2).innerText) + parseFloat(tempQuotaTotalPercent);
tempQuotaTotalPercent = tempQuotaTotalPercent.toFixed(2);
}
}
if(tempQuotaTotalPercent >= 105){
alert("One or more of your quota components exceed 105% of the required amount.\n Please adjust your quota assignments to be as close to but no less than 100%.");
document.getElementById("postpay_quota_total_percent").style.color = "red";
}
document.getElementById("postpay_quota_total").innerText = tempQuotaTotal;
document.getElementById("postpay_quota_total_percent").innerText = tempQuotaTotalPercent+"%";
document.getElementById("assigned_postpay_quota_total").value = tempQuotaTotal;
document.getElementById("assigned_postpay_percentage_total").value = tempQuotaTotalPercent;
//alert("Total Hidden Value: "+document.VPQuotaForm.assigned_postpay_quota_total.value+"\n Total Hidden % Value: "+document.VPQuotaForm.assigned_postpay_percentage_total.value);
}
function prepay_func(x){
var ID_Element = x.name;
var InputRows = #tempRowCount#;
var tempQuota = #assigned_prepay_quota#;
var tempQuotaTotal = 0;
var tempQuotaTotalPercent = 0;
var percentQuota = (x.value/tempQuota)*100;
percentQuota = percentQuota.toFixed(2);
document.getElementById(ID_Element+"_precent").innerText = percentQuota+"%";
for(i=1;i<=InputRows;i++){
var tempElementName1 = i+"_prepay";
var tempElementName2 = i+"_prepay_precent";
if(document.getElementById(tempElementName1).value != null && document.getElementById(tempElementName1).value != 0 && document.getElementById(tempElementName1).value != ""){
tempQuotaTotal = parseFloat(document.getElementById(tempElementName1).value)+ parseFloat(tempQuotaTotal);
tempQuotaTotalPercent = parseFloat(document.getElementById(tempElementName2).innerText) + parseFloat(tempQuotaTotalPercent);
tempQuotaTotalPercent = tempQuotaTotalPercent.toFixed(2);
}
}
if(tempQuotaTotalPercent >= 105){
alert("One or more of your quota components exceed 105% of the required amount.\n Please adjust your quota assignments to be as close to but no less than 100%.");
document.getElementById("prepay_quota_total_percent").style.color = "red";
}
document.getElementById("prepay_quota_total").innerText = tempQuotaTotal;
document.getElementById("prepay_quota_total_percent").innerText = tempQuotaTotalPercent+"%";
document.getElementById("assigned_prepay_quota_total").value = tempQuotaTotal;
document.getElementById("assigned_prepay_percentage_total").value = tempQuotaTotalPercent;
//alert("Total Hidden Value: "+document.VPQuotaForm.assigned_prepay_quota_total.value+"\n Total Hidden % Value: "+document.VPQuotaForm.assigned_prepay_percentage_total.value);
}
function cr_func(x){
var ID_Element = x.name;
var InputRows = #tempRowCount#;
var tempQuota = #assigned_cr_quota#;
var tempQuotaTotal = 0;
var tempQuotaTotalPercent = 0;
var percentQuota = (x.value/tempQuota)*100;
percentQuota = percentQuota.toFixed(2);
document.getElementById(ID_Element+"_precent").innerText = percentQuota+"%";
for(i=1;i<=InputRows;i++){
var tempElementName1 = i+"_cr";
var tempElementName2 = i+"_cr_precent";
if(document.getElementById(tempElementName1).value != null && document.getElementById(tempElementName1).value != 0 && document.getElementById(tempElementName1).value != ""){
tempQuotaTotal = parseFloat(document.getElementById(tempElementName1).value)+ parseFloat(tempQuotaTotal);
tempQuotaTotalPercent = parseFloat(document.getElementById(tempElementName2).innerText) + parseFloat(tempQuotaTotalPercent);
tempQuotaTotalPercent = tempQuotaTotalPercent.toFixed(2);
}
}
if(tempQuotaTotalPercent >= 105){
alert("One or more of your quota components exceed 105% of the required amount.\n Please adjust your quota assignments to be as close to but no less than 100%.");
document.getElementById("cr_quota_total_percent").style.color = "red";
}
document.getElementById("cr_quota_total").innerText = tempQuotaTotal;
document.getElementById("cr_quota_total_percent").innerText = tempQuotaTotalPercent+"%";
document.getElementById("assigned_cr_quota_total").value = tempQuotaTotal;
document.getElementById("assigned_cr_percentage_total").value = tempQuotaTotalPercent;
//alert("Total Hidden Value: "+document.VPQuotaForm.assigned_cr_quota_total.value+"\n Total Hidden % Value: "+document.VPQuotaForm.assigned_cr_percentage_total.value);
}
</script>
<cfloop query="rtnSaleMgrLs">
<tr>
<td><!--- Here is where the JavaScript Function is called --->
#LName#, #FName# : #PosNbr#<input type="hidden" name="#currentRow#_RowKey" value="#currentRow#_#Emplid#_#PosNbr#">
</td>
<td>
<input type="text" name="#currentRow#_postpay" value="" onChange="postpay_func(this)" size="10">
</td>
<td id="#currentRow#_postpay_precent">0.0%</td>
<td>
<input type="text" name="#currentRow#_prepay" value="" onChange="prepay_func(this)" size="10">
</td>
<td id="#currentRow#_prepay_precent">0.0%</td>
<td>
<input type="text" name="#currentRow#_cr" value="" onChange="cr_func(this)" size="10">
</td>
<td id="#currentRow#_cr_precent">0.0%</td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
</tr>
</cfloop>
<cfelse>
<tr>
<td colspan="11">There are no Sales Managers listed as direct reports for this VP position.</td>
</tr>
</cfif>
<tr><!--- He useds the td id to call certain JavaScript functions --->
<td>Total</td>
<td id="postpay_quota_total">00.00</td>
<td id="postpay_quota_total_percent">00.00%</td>
<td id="prepay_quota_total">00.00</td>
<td id="prepay_quota_total_percent">00.00%</td>
<td id="cr_quota_total">00.00</td>
<td id="cr_quota_total_percent">00.00%</td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
</tr>
<tr>
<td colspan="11"><!--- Hidden values of the cfParam --->
<input type="Hidden" name="assigned_postpay_quota_total" value="">
<input type="hidden" name="assigned_postpay_percentage_total" value="">
<input type="Hidden" name="assigned_prepay_quota_total" value="">
<input type="hidden" name="assigned_prepay_percentage_total" value="">
<input type="Hidden" name="assigned_cr_quota_total" value="">
<input type="hidden" name="assigned_cr_percentage_total" value="">
<input type="hidden" name="total_mgrs" value="#tempRowCount#">
<input type="hidden" name="CycleDate" value="#CycleDate#">
<input type="hidden" name="VPPosNbr" value="#VPPositionNumber#">
</td>
</tr>
<tr>
<td colspan="11" align="center">
<br>
<input type="submit" name="Submit" value="Submit">
</td>
</tr>
</table>
</form>
ColdFusion LanguageJavaScriptWeb Languages and Standards
Last Comment
rudodoo