Review my Code

Report · Feb 03, 2010

Good everyone,

Can someone look at my code to tell me if there is a way make it more compact. I want to stop users from entering the same data over and over.

<cfif 'FORM.StartUnits' eq 0>
<cflocation url="../errorPages/startunitsGTendunits.cfm">
<cfelseif FORM.StartUnits GT FORM.EndUnits>
<cflocation url="../errorPages/startunitsGTendunits.cfm">
<cfelseif IsDefined('FORM.Workorder')>
<cfelse>
<cflocation url="../Production/workordersearch.cfm">
</cfif>
<cfset FORM.DelayTime = (FORM.DelayTimeHours + (FORM.DelayTimeMinutes/60)) >
<cfset FORM.ProdTime = (FORM.ProTimeHours + (FORM.ProTimeMinutes/60)) >
<cfset FORM.CoTime = (FORM.CoTimeHours + (FORM.CoTimeMinutes/60)) >
<cfset FORM.UnitsProd = FORM.EndUnits - (FORM.StartUnits) + 1 >
<cfparam name="FORM.WorkOrder" default="1">
<cftransaction>
<cftry>
<cfquery name="insertProduction" datasource="#REQUEST.datasource#">
IF NOT EXISTS
(
select WorkOrder, StartUnits
from tbl_Assembly_Production
where workorder = '#FORM.Workorder#' and StartUnits = '#FORM.StartUnits#'
)
BEGIN
INSERT INTO tbl_Assembly_Production (dateProd, Shift, Area, Jig, EmpNo, WorkOrder, Item, Model, ProdTime, CoTime, startUnits, endUnits, NpTime, UnitsProd, ProdDelayTime, CoDelayTime, Comment)
VALUES ('#Trim(FORM.dateProd)#',
        '#Trim(FORM.Shift)#',
        '#Trim(FORM.Area)#',
        '#Trim(FORM.jig)#',
        '#Trim(FORM.EmpNo)#',
        '#Trim(FORM.WorkOrder)#',
        '#Trim(FORM.Item)#',
        '#Trim(FORM.Model)#',
        '#Trim(FORM.ProdTime)#',
        '#Trim(FORM.CoTime)#',
        '#Trim(FORM.startUnits)#',
        '#Trim(FORM.endUnits)#',
        '#Trim(FORM.NpTime)#',
        '#Trim(FORM.UnitsProd)#',
        '#Trim(FORM.ProdDelayTime)#',
        '#Trim(FORM.CoDelayTime)#',
        '#Trim(FORM.Comment)#')
END
</cfquery>
Thank You. Work Order <cfoutput>#FORM.WORKORDER#</cfoutput> with Starting Unit <cfoutput>#FORM.StartUnits#</cfoutput> has already been entered. You can enter this unit only once!!!.
<cfcatch type="database">
Work Order <cfoutput>#FORM.WORKORDER#</cfoutput> with Starting Unit <cfoutput>#FORM.StartUnits#</cfoutput> is already entered. Record Not Inserted!!!.
</cfcatch>
</cftry>
<cfquery name="insertAssemblyDelay" datasource="#REQUEST.datasource#">
INSERT INTO tbl_Assembly_Delay(ID, Code, DelayTime)
SELECT ID , '#Trim(FORM.Code)#', '#Trim(FORM.DelayTime)#'
FROM tbl_Assembly_Production
WHERE tbl_Assembly_Production.WorkOrder = '#FORM.WorkOrder#'
</cfquery>
<cftry>
<cfset list1 = #FORM.Unit# >
<cfset list2 = #FORM.WorkOrder#>
<cfset list3 = #FORM.YearAssy#>
<cfloop list="#list1#" index="j">
<cfloop list="#list2#" index="k">
<cfloop list="#list3#" index="m">
<cfquery name="insertunits" datasource="#REQUEST.datasource#">
IF NOT EXISTS
(
select ID, Workorder, Unit
from tbl_Assembly_Unit
where Workorder = '#k#' and Unit = '#m#'
)
BEGIN
Insert into tbl_assembly_Unit(ID, YearAssy, WorkOrder, Unit)
SELECT ID, '#Trim(FORM.YearAssy)#', '#Trim(k)#', '#Trim(j)#'
FROM tbl_Assembly_Production
WHERE tbl_Assembly_Production.Workorder = '#FORM.WorkOrder#'
END
</cfquery>
</cfloop>
</cfloop>
</cfloop>
Your Unit(s) have been added.
<cfcatch type="database">
The Unit(s) you listed cannot be more than once.
</cfcatch>
</cftry>
</cftransaction>

Report · Feb 03, 2010

With the nested loops you are inserting three times as many records as you should. The ListGetAt() function will help you solve this.

Report · Feb 03, 2010

Dan,

Thanks for ListGetAt(), I need an example to make it work. Also, I see ListFirst() and ListLast() that will resolve another issue. The ListGetAt() will be populated from a cfselect multiple and I need the ID to loop with every unit. I want to through an error when users try to enter workorder = '#FORM.Workorder#' and Unit = '#FORM.Unit#' in duplicates.

Report · Feb 04, 2010

It's probably simpler than you think. For the sake of this demo, I'll assume your 3 lists have the same number of elements. You can simply do this.

insert into your table (field1, field2, field3)

values

(ListGetAt(List1, ii)

, ListGetAt(List1, ii)

, ListGetAt(List1, ii) )

closing tags, proper syntax, etc.