Michael, Have you used CFTIMER to find out where you are bogging down? Is it the calls to the DB or is it your CF processing? Dave
_____ From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Michael Sumner Sent: Friday, June 15, 2007 8:04 AM To: [email protected] Subject: [DFW CFUG] Is there a better way Due to the nature of our business we end up with a lot of stranded open drop ship orders in our ERP system. There is no process provided in the base software to delete more than one order at a time. And in this case it is deleting the order and closing the corresponding PO. We can access the applications underlying DB via ODBC connection (Progress is the DB). I put together this process to delete all open orders based on customer number and cancel date. I will preface this that no matter what we use Access / Crystal Reports / this code, everything runs really slow through this connection and bogs down the server. I would appreciate any comments on how I am using my CFC. I would really like to hear from anyone or anyone who knows about Progress DB or its programming language 4GL now called ABL. I would probably be better off if I could set this up as a stored procedure and just call it. Thanks, Mike Sumner Here is the action page to handle the orders selected. The prior page allows you to view the line detail and check mark that order for deletion. I submit the form to this page. Page Name: d_member_open_orders_action.cfm Date: 005-02-07 Author: Mike Sumner ---> Find out how many orders to loop through <cfset i = ArrayLen(orders.FaxInvc)> <html> <body> <!--- Set podet.LineItemSts to C ---> <cfdump var="#i#"> <cfdump var="#FORM#"> With this I can delete only open orders and not the POs if true <cfif FORM.order eq false > <!------<cfif reset EQ "">---> <cfloop from="1" to="#i#" index="i" > <cfif orders.FaxInvc[i] eq true > <!--- Close the PO Header and PO Detail files - pass the PO Number ---> <!--- Set podet.LineItemSts to C ---> <cfinvoke component="terms.CFC.delete" method="closePOdet" > <cfinvokeargument name="OurPurchOrdNum" value="#orders.OurPurchOrdNum[i]#"> </cfinvoke> <!--- Set pohed.POSts to C ---> <cfinvoke component="terms.CFC.delete" method="closePOhed"> <cfinvokeargument name="OurPurchOrdNum" value="#orders.OurPurchOrdNum[i]#"> </cfinvoke> <!--- Delete ordet record ---> <cfinvoke component="terms.CFC.delete" method="deleteORDet"> <cfinvokeargument name="OurPurchOrdNum" value="#orders.OurPurchOrdNum[i]#"> <cfinvokeargument name="OrdNum" value="#orders.OrdNum[i]#"> <cfinvokeargument name="OrdGenNum" value="#orders.OrdGenNum[i]#"> </cfinvoke> <!--- Delete ordhed record ---> <cfinvoke component="terms.CFC.delete" method="deleteORHed"> <cfinvokeargument name="OrdNum" value="#orders.OrdNum[i]#"> <cfinvokeargument name="OrdGenNum" value="#orders.OrdGenNum[i]#"> </cfinvoke> <!--- Delete the OHHE1 record ---> <cfinvoke component="terms.CFC.delete" method="deleteOHHE1"> <cfinvokeargument name="OrdNum" value="#orders.OrdNum[i]#"> <cfinvokeargument name="OrdGenNum" value="#orders.OrdGenNum[i]#"> <cfinvokeargument name="CustNum" value="#orders.CustNum[i]#"> </cfinvoke> </cfif> </cfloop> <cfelse > <cfloop from="1" to="#i#" index="i" > <cfif orders.FaxInvc[i] eq true > <!--- Delete ordet record ---> <cfinvoke component="terms.CFC.delete" method="deleteORDet"> <cfinvokeargument name="OurPurchOrdNum" value="#orders.OurPurchOrdNum[i]#"> <cfinvokeargument name="OrdNum" value="#orders.OrdNum[i]#"> <cfinvokeargument name="OrdGenNum" value="#orders.OrdGenNum[i]#"> </cfinvoke> <!--- Delete ordhed record ---> <cfinvoke component="terms.CFC.delete" method="deleteORHed"> <cfinvokeargument name="OrdNum" value="#orders.OrdNum[i]#"> <cfinvokeargument name="OrdGenNum" value="#orders.OrdGenNum[i]#"> </cfinvoke> <!--- Delete the OHHE1 record ---> <cfinvoke component="terms.CFC.delete" method="deleteOHHE1"> <cfinvokeargument name="OrdNum" value="#orders.OrdNum[i]#"> <cfinvokeargument name="OrdGenNum" value="#orders.OrdGenNum[i]#"> <cfinvokeargument name="CustNum" value="#orders.CustNum[i]#"> </cfinvoke> </cfif> </cfloop> </cfif> <cflocation url="index.cfm"> HERE IS part of my CFC <cfcomponent> <cffunction name="OpenOrders" access="public" returntype="query" output="true" > <cfargument name="CustNum" type="numeric" required="yes" default="2"> <cfargument name="CncDate" type="string" default="2007/02/02"> <cfset var getOpenOrders = "" > <cfquery name="getOpenOrders" datasource="commerce"> SELECT PUB.orhed.CustNum, PUB.orhed.EntDate, PUB.orhed.CncDate, PUB.orhed.OrdNum, PUB.orhed.OrdGenNum, PUB.orhed.TotOrdVal, PUB.orhed.FaxInvc, PUB.ordet.ItemNum, PUB.ordet.OurPurchOrdNum FROM PUB.orhed INNER JOIN PUB.ordet ON PUB.orhed.CustNum = PUB.ordet.CustNum AND PUB.orhed.OrdNum = PUB.ordet.OrdNum AND PUB.orhed.OrdGenNum = PUB.ordet.OrdGenNum WHERE PUB.orhed.CustNum = <cfqueryparam value="#Arguments.CustNum#" cfsqltype="cf_sql_numeric"> AND Pub.orhed.CncDate < '#ARGUMENTS.CncDate#' </cfquery> <cfreturn getOpenOrders> </cffunction> <!--- Set PODet to C ---> <cffunction name="closePOdet" output="true" access="public" > <cfargument name="OurPurchOrdNum" type="string"> <cfquery name="cPOdet" datasource="terms" > UPDATE PUB.podet SET LineItemSts = 'C' WHERE PurchOrdNum = '#ARGUMENTS.OurPurchOrdNum#' </cfquery> <cfreturn> </cffunction> <!--- Set POHed to C ---> <cffunction name="closePOhed" output="true" access="public" > <cfargument name="OurPurchOrdNum" type="string"> <cfquery name="cPOhed" datasource="terms" > UPDATE PUB.pohed SET POSts = 'C' WHERE PurchOrdNum = '#ARGUMENTS.OurPurchOrdNum#' </cfquery> <cfreturn> </cffunction> <!--- Delete ORdet ---> <cffunction name="deleteORDet" output="true" access="public" > <cfargument name="OurPurchOrdNum" type="string"> <cfargument name="OrdNum" type="string"> <cfargument name="OrdGenNum" type="numeric"> <cfquery name="cPOhed" datasource="terms" > Delete From Pub.ORdet WHERE OrdNum = '#ARGUMENTS.OrdNum#' AND OrdGenNum = #ARGUMENTS.OrdGenNum# AND OurPurchOrdNum = '#ARGUMENTS.OurPurchOrdNum#' </cfquery> <cfreturn> </cffunction> Michael Sumner Nations Best Sports 817-788-0034 ext 244 817-788-8542 fax
_______________________________________________ Reply to DFWCFUG: [email protected] Subscribe/Unsubscribe: http://lists1.safesecureweb.com/mailman/listinfo/list List Archives: http://www.mail-archive.com/list%40list.dfwcfug.org/ http://www.mail-archive.com/list%40dfwcfug.org/ DFWCFUG Sponsors: www.instantspot.com/ www.teksystems.com/
