3 Replies Latest reply on Feb 4, 2010 5:21 AM by Dan Bracuk

    Review my Code

    djkhalif Level 1

      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>

        • 1. Re: Review my Code
          Dan Bracuk Level 5

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

          • 2. Re: Review my Code
            djkhalif Level 1

            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.

            • 3. Re: Review my Code
              Dan Bracuk Level 5

              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.

               

              <cfloop from = "1" to = ListLen(List1), index = "ii">

              insert into your table (field1, field2, field3)

              values

              (ListGetAt(List1, ii)

              , ListGetAt(List1, ii)

              , ListGetAt(List1, ii) )

               

              closing tags, proper syntax, etc.