Copy link to clipboard
Copied
The database queries I had at the top of the page were moved into a CFC and now it is taking noticeable amount of time in displaying the data. how to make data entry validation that does not happen inconsistency.
Ex :
1. AddItem.cfm
<cfinvoke component="#Application.ComponentPath#.inventory.Stock"
method="qtymin"
datasource ="#iif(isDefined('DSN'),'DSN','Attributes.DSN')#"
itemcode="#varItemCode#"
trans_date="#createodbcdate(qhead.whtreqdate)#"
trans_type="STF"
qty="#QTY#"
qtyrr="#QTY#"
qtysn="#QTY#"
qtyprod="#QTY#"
unitprice="0"
currencyunit="#COOKIE.currencyid#"
docno="#whtreqnum#"
lstbin="#evaluate('lstBinQty#idx#')#"
whid="#COOKIE.LOCATION_ID#"
companyid="#cookie.companyID#"
>
2. Stock.cfc
<cffunction name="qtymin" access="public" output="yes">
<cfargument name="datasource" type ="string" required="true" default="#REQUEST.DSN#" />
<cfargument name="itemcode" type="string" required="true" default=""/>
<cfargument name="trans_date" type="date" required="true" default=""/>
<cfargument name="trans_type" type="string" required="true" default=""/>
<cfargument name="qty" type="numeric" required="true" default=0/>
<cfargument name="qtyrr" type="numeric" required="true" default=0/>
<cfargument name="qtysn" type="numeric" required="true" default=0/>
<cfargument name="qtyprod" type="numeric" required="true" default=0/>
<cfargument name="currencyunit" type="string" required="true" default=""/>
<cfargument name="docno" type="string" required="true" default=""/>
<cfargument name="lstbin" type="string" required="true" default=""/>
<cfargument name="whid" type="string" required="true" default=""/>
<cfargument name="companyid" type="string" required="true" default=""/>
<cfargument name="reason" type="string" required="true" default=""/>
<cfquery name="qavailableitem" datasource="#datasource#">
--------------SELECT-------------------------------
</cfquery>
<cfif qavailableitem.recordcount gt 1 and qavailableitem.item_qty gt 0>
<cfif qavailableitem.item_qty lt qty>
<cfquery name="qUpdateavailableitem1" datasource="#datasource#">
--------------------------UPDATE--------------------------------
</cfquery>
<cfquery name="qUpdateavailableitem2" datasource="#datasource#">
------------------------------------------UPDATE-------------------------------
</cfquery>
</cfif>
<cfquery name="qavailableitem" datasource="#datasource#">
----------------------------------SELECT-----------------------------
</cfquery>
<cfquery name="qavailableitem" dbtype="query">
------------------------INSERT---------------------
</cfquery>
</cfif>
</cffunction>
Copy link to clipboard
Copied
There is something odd about the line <cfif qavailableitem.recordcount gt 1 and qavailableitem.item_qty gt 0>. If the recordcount is greater than 1 then there will be more than 1 value of qavailableitem.item_qty. Which one will you then be comparing with 0?
Perhaps even more importantly, your component is doing too much. Functions should in general not display anything. Also, you could improve the design of your code by dividing the responsibility among several functions.
Something like this:
1. AddItem.cfm
<cfinvoke component="#Application.ComponentPath#.inventory.Stock"
method="qtymin"
returnVariable="qavailableitem"
datasource="#iif(isDefined('DSN'),'DSN','Attributes.DSN')#"
itemcode="#varItemCode#"
trans_date="#createodbcdate(qhead.whtreqdate)#"
trans_type="STF"
qty="#QTY#"
qtyrr="#QTY#"
qtysn="#QTY#"
qtyprod="#QTY#"
unitprice="0"
currencyunit="#COOKIE.currencyid#"
docno="#whtreqnum#"
lstbin="#evaluate('lstBinQty#idx#')#"
whid="#COOKIE.LOCATION_ID#"
companyid="#cookie.companyID#"
>
<cfif qavailableitem.recordcount gt 1 and qavailableitem.item_qty gt 0>
<!--- perform update query --->
<cfif qavailableitem.item_qty lt qty>
<cfinvoke component="#Application.ComponentPath#.inventory.Stock"
method="doUpdate"
...
>
</cfif>
<!--- perform another select query --->
<cfinvoke component="#Application.ComponentPath#.inventory.Stock"
method="qtymin"
...
>
<!--- perform insert query --->
<cfinvoke component="#Application.ComponentPath#.inventory.Stock"
method="doInsert"
...
>
</cfif>
2. Stock.cfc
<cffunction name="qtymin" access="public" output="no" returntype="query">
<cfargument name="datasource" type ="string" required="true" default="#REQUEST.DSN#" />
<cfargument name="itemcode" type="string" required="true" default=""/>
<cfargument name="trans_date" type="date" required="true" default=""/>
<cfargument name="trans_type" type="string" required="true" default=""/>
<cfargument name="qty" type="numeric" required="true" default=0/>
<cfargument name="qtyrr" type="numeric" required="true" default=0/>
<cfargument name="qtysn" type="numeric" required="true" default=0/>
<cfargument name="qtyprod" type="numeric" required="true" default=0/>
<cfargument name="currencyunit" type="string" required="true" default=""/>
<cfargument name="docno" type="string" required="true" default=""/>
<cfargument name="lstbin" type="string" required="true" default=""/>
<cfargument name="whid" type="string" required="true" default=""/>
<cfargument name="companyid" type="string" required="true" default=""/>
<cfargument name="reason" type="string" required="true" default=""/>
<!--- Result-set to be returned defined as function-local variable
<cfset var localResultSet = "">
<cfquery name="localResultSet" datasource="#datasource#">
--------------SELECT-------------------------------
</cfquery>
<cfreturn localResultSet>
</cffunction>
<cffunction name="doUpdate" access="public" output="no" returntype="void">
<!--- more code --->
<cfquery name="qUpdateavailableitem2" datasource="#datasource#">
------------------------------------------UPDATE-------------------------------
</cfquery>
</cffunction>
<cffunction name="doInsert" access="public" output="no" returntype="void">
<!--- more code --->
<cfquery name="qavailableitem" dbtype="query">
------------------------INSERT---------------------
</cfquery>
</cffunction>
Copy link to clipboard
Copied
Thx BKBK..
to use <cftry> <cfcatch> whether it can be used here, and if in use how do I apply..?
Copy link to clipboard
Copied
For example:
addItem.cfm
<!--- At very top of page --->
<cftry>
...
...
<!--- At very bottom of page --->
<cfcatch type="any">
<cfdump var="#cfcatch#">
</cfcatch>
</cftry>
You would, of course, do this in development code, not in production code.
Copy link to clipboard
Copied
1. Form.cfm
<table width="100%" class="formtext" border="1" bordercolor="##c0c0c0">
<tr class="formtitle">
<td align="center">#DO_VAR['ItemCode']#</td>
<td align="center">#DO_VAR['ItemName']#</td>
<td align="center">#DO_VAR['eHRMAvailable']# #DO_VAR['Quantity']#</td>
</tr>
<tr class="formtitle">
<td align="center"><input type = "text" name="txtitemcode" value=""></td>
<td align="center"><input type = "text" name="txtitemNme" value=""></td>
<td align="center"><input type = "text" name="txtitemQty" value=""></td>
</tr>
<tr>
<td><input align="left" type="Button" name="btnConfirm" value="#DO_VAR['eHRMconfirm']#" onClick="simpan();" style="width:100px"></td>
</tr>
</table>
<script>
function simpan(isConfirm) {
document.forms[0].action = '#Application.stApp.web_path[vst_idx]#/#Application.stApp.home_url[vst_idx]#/#form/qInsert.cfm#'
document.forms[0].submit();
}
</script>
2. qInsert.cfm
<cfloop from="1" to="#rowcount#" index="idx">
<cfset ItemCode =evaluate("txtitemcode#idx#")>
<cfset qty = val(evaluate("txtitemNme#idx#"))>
<cfset ItemName=evaluate("txtitemQty#idx#")>
<cfquery name="qCheckItem" datasource="#iif(isDefined('DSN'),'DSN','Attributes.DSN')#">
------------------select-------------------
</cfquery>
<cfif listfindnocase(qCheckItem.viewcategory,"3",";")>
<!--- HK, need to be tested --->
<cfinvoke component="#Application.ComponentPath#.sunfisherp.inventory.Stock"
method="qtymin"
datasource ="#iif(isDefined('DSN'),'DSN','Attributes.DSN')#"
itemcode="#varItemCode#"
trans_date="#createodbcdate(qhead.whtreqdate)#"
trans_type="STF"
qty="#QTY#"
qtyrr="#QTY#"
qtysn="#QTY#"
qtyprod="#QTY#"
unitprice="0"
currencyunit="#COOKIE.currencyid#"
docno="#whtreqnum#"
lstbin="#evaluate('lstBinQty#idx#')#"
whid="#COOKIE.LOCATION_ID#"
companyid="#cookie.companyID#">
</cfif>
</cfloop>
3. Stock.cfc
<cfcomponent name="stock" output ="false">
<cffunction name="qtymin" access="public" output="no" returntype="query">
<cfargument name="datasource" type ="string" required="true" default="#REQUEST.DSN#" />
<cfargument name="itemcode" type="string" required="true" default=""/>
<cfargument name="trans_date" type="date" required="true" default=""/>
<cfargument name="trans_type" type="string" required="true" default=""/>
<cfargument name="qty" type="numeric" required="true" default=0/>
<cfargument name="qtyrr" type="numeric" required="true" default=0/>
<cfargument name="qtysn" type="numeric" required="true" default=0/>
<cfargument name="qtyprod" type="numeric" required="true" default=0/>
<cfargument name="currencyunit" type="string" required="true" default=""/>
<cfargument name="docno" type="string" required="true" default=""/>
<cfargument name="lstbin" type="string" required="true" default=""/>
<cfargument name="whid" type="string" required="true" default=""/>
<cfargument name="companyid" type="string" required="true" default=""/>
<cfargument name="reason" type="string" required="true" default=""/>
<cfloop from="1" to="#rowcount#" index="idx">
<cfset varItemCode =evaluate("txtitemcode#idx#")>
<cfset qty = val(evaluate("TXTAPPROVEDQTY#idx#"))>
<cfset memo =evaluate("TXTDESC#idx#")>
<cfquery name="qcek" datasource="#datasource#">
--------------select---------
</cfquery>
<cfif qcek.recordcount gt 0>
<cfquery name="qupd_TaccAvailableItem" datasource="#datasource#">
---------UPDATE--------------
</cfquery>
</cfif>
</cfllop>
<cffuntion>
</cfcomponent>
Copy link to clipboard
Copied
MY CODE PROCESS SO LESS IS MORE LIKE THIS.