Cacher is the code snippet organizer for pro developers

<!---
FILE: insert_profile.cfm
AUTH: James R. Aylesworth
DATE: 30-APR-2014
DESC: This file will pull profiles from the GET_EMPL_LIST view
that started (SMH_STRT_DT) within the last 30 days and
allow the user an opportunity to change/update any of the
columns data and then insert it (using Submit button) into
the EMPLOYEE_PROFILES table.
--->
<!---//INITIALIZE VARIABLES//--->
<!--- List of column headers that we need to read/insert when performing actions with employee profiles --->
<cfset columnHdrs = "UNIT_ID,DEPT,UR_ID,EMPLOYEE_ID,LAST_NAME,FIRST_NAME,POS_CODE,TITLE,EVAL_ID,EVAL_ME,SIGN_OFF_ME,MIN_WKLY_HRS,SMH_STRT_DT,MSS_UNIT_STRT_DT,POS_STRT_DATE,UNIT_STRT_DATE,DOMAIN_ID,EMAIL,DAY_BREAK,SECURITY_LEVEL,MS_ALIAS,CONFLICTS,REDSHIFTS,WEEKEND_HRS,WEEKEND_CONTRACT,MINIMUM_NIGHT_SHIFT,MAXIMUM_DAY_SHIFT,MINIMUM_EVENING_SHIFT,MAX_WKLY_HRS,SCHOOL,MIN_TB_HRS,MIN_HRS_PP,MAX_HRS_PP,MAX_TB_HRS,MIN_DAY_SHIFT,CH_WK,CH_PP,CH_TB,CH_WE,PH_CELL,PH_HOME">
<!--- List of column headers to actually display --->
<cfset visibleData = 'UNIT_ID,DEPT,UR_ID,EMPLOYEE_ID,LAST_NAME,FIRST_NAME,POS_CODE,TITLE,EVAL_ID,EVAL_ME,SIGN_OFF_ME,MIN_WKLY_HRS,SMH_STRT_DT,MSS_UNIT_STRT_DT,POS_STRT_DATE,UNIT_STRT_DATE,DOMAIN_ID,EMAIL,DAY_BREAK,SECURITY_LEVEL,MS_ALIAS'>
<!--- if the form is defined, then a user submitted something...update database --->

<cfif IsDefined("insertEmployeeProfile")>
  <cfoutput>
    <cftry>
      <cfquery name="insertEmployeeProfile" datasource="#application.datasrc_mss#" dbtype="#application.dbtype_mss#">
      <!--- remove the last item from the list (form name which is not a sql field)--->
      INSERT INTO EMPLOYEE_PROFILE
      (#columnHdrs#)
      VALUES (
      <cfloop list="#columnHdrs#" index="field">
        <cfset field = trim(field)>
        <cfset data = Evaluate(#field#)>
        <!--- if the rematch found non-digit characters, then field needs to be quoted --->
        <cfif ArrayLen(REMatch("\D*(?!\.)\D",data)) GTE 1 OR UCASE("#field#") EQ "UNIT_ID">
          '#Trim(data)#'
          <cfif ListFind(columnHdrs,field) LT ListLen(columnHdrs)>
            ,
          </cfif>
        <cfelseif ArrayLen(REMatch("\d\d-\D*-\d\d",data)) GTE 1>
          #CreateODBCDate(Trim(data))#
          <cfif ListFind(columnHdrs,field) LT ListLen(columnHdrs)>
            ,
          </cfif>
        <cfelse>
          <!--- insert NULL for empty values--->
          <cfif #Trim(data)# EQ "">
            NULL
            <cfif ListFind(columnHdrs,field) LT ListLen(columnHdrs)>
              ,
            </cfif>
          <cfelse>
            #Trim(data)#
            <cfif ListFind(columnHdrs,field) LT ListLen(columnHdrs)>
              ,
            </cfif>
          </cfif>
        </cfif>
      </cfloop>
      )
    </cfquery>
      <!--- inform the user that the employee was added successfully --->
      <h3>User #FORM.FIRST_NAME# #FORM.LAST_NAME# was successfully added to #FORM.UNIT_ID#</h3>
      <cfcatch>
        <h1>An error occured</h1>
        <h2>attempted query below</h2>
        <!--- remove the last item from the list (form name which is not a sql field)---> 
        INSERT INTO EMPLOYEE_PROFILE
        (#columnHdrs#)
        VALUES (
        <cfloop list="#columnHdrs#" index="field">
          <cfset field = trim(field)>
          <cfset data = Evaluate(#field#)>
          <!--- if the rematch found non-digit characters, then field needs to be quoted --->
          <cfif ArrayLen(REMatch("\D*(?!\.)\D",data)) GTE 1 OR UCASE("#field#") EQ "UNIT_ID">
            '#Trim(data)#'
            <cfif ListFind(columnHdrs,field) LT ListLen(columnHdrs)>
              ,
            </cfif>
            <cfelseif ArrayLen(REMatch("\d\d-\D*-\d\d",data)) GTE 1>
            #CreateODBCDate(Trim(data))#
            <cfif ListFind(columnHdrs,field) LT ListLen(columnHdrs)>
              ,
            </cfif>
            <cfelse>
            <!--- insert NULL for empty values--->
            <cfif #Trim(data)# EQ "">
              NULL
              <cfif ListFind(columnHdrs,field) LT ListLen(columnHdrs)>
                ,
              </cfif>
              <cfelse>
              #Trim(data)#
              <cfif ListFind(columnHdrs,field) LT ListLen(columnHdrs)>
                ,
              </cfif>
            </cfif>
          </cfif>
        </cfloop>
        )
        <cfdump var="#form#">
        <h3>User #FORM.FIRST_NAME# #FORM.LAST_NAME# was NOT added to #FORM.UNIT_ID#</h3>
        <cfabort>
      </cfcatch>
    </cftry>
  </cfoutput>
</cfif>
<!--- query to get users that started within the last month --->
<cfquery name="vGET_EMPL_LIST" datasource="#application.datasrc_mss#" dbtype="#application.dbtype_mss#">
  SELECT UNIT_ID,
  DEPT,
  UR_ID,
  EMPLOYEE_ID,
  LAST_NAME,
  FIRST_NAME,
  POS_CODE,
  TITLE,
  SMH_TERM_DT,
  MSS_UNIT_TERM_DT,
  EVAL_ID,
  EVAL_ME,
  SIGN_OFF_ME,
  MIN_WKLY_HRS,
  SUBSTR(SMH_STRT_DT,1,9) AS SMH_STRT_DT,
  SUBSTR(MSS_UNIT_STRT_DT,1,9) AS MSS_UNIT_STRT_DT,
  SUBSTR(POS_STRT_DATE,1,9) AS POS_STRT_DATE,
  SUBSTR(UNIT_STRT_DATE,1,9) AS UNIT_STRT_DATE,
  DOMAIN_ID,
  EMAIL,
  DAY_BREAK,
  SECURITY_LEVEL,
  MS_ALIAS,
  PERSONAL_MESSAGE,
  CONFLICTS,
  REDSHIFTS,
  WEEKEND_HRS,
  WEEKEND_CONTRACT,
  MINIMUM_NIGHT_SHIFT,
  MAXIMUM_DAY_SHIFT,
  MINIMUM_EVENING_SHIFT,
  GROUP_CHOICE_ID,
  SG_CHOICE_ID,
  MAX_WKLY_HRS,
  SCHOOL,
  MIN_TB_HRS,
  MIN_HRS_PP,
  MAX_HRS_PP,
  MAX_TB_HRS,
  PR_UNITS,
  MIN_DAY_SHIFT,
  CH_WK,
  CH_PP,
  CH_TB,
  CH_WE,
  PH_CELL,
  PH_HOME
  FROM GET_EMPL_LIST
  WHERE SMH_STRT_DT >= (SYSDATE - 31) --go back 1 month
  AND EMPLOYEE_ID NOT IN(
  SELECT DISTINCT EMPLOYEE_ID
  FROM EMPLOYEE_PROFILE
  WHERE SMH_TERM_DT IS NULL
  )
  ORDER BY LAST_NAME ASC
</cfquery>
<!---//START BODY//---> 
<cfoutput>
  <table style="border: 1px solid black; padding:1 1 1 1;">
    <tr>
      <cfloop list="#visibleData#" index="columnName">
        <th> <cfoutput>#columnName#</cfoutput> </th>
      </cfloop>
      <th> SUBMIT </th>
    </tr>
    <!--- Loop through the query results and build a form for each employee --->
    <cfloop query="vGET_EMPL_LIST">
      <cfform name="insertEmployeeProfile" action="#CGI.SCRIPT_NAME#" method="post">
        <tr>
          <cfloop list="#columnHdrs#" index="columnName">
            <!--- clean up possible pre/post spaces of list item --->
            <cfset columnName = trim(columnName)>
            <cfset columnData = "vGET_EMPL_LIST.#columnName#">
            <!--- make sure to do all work instide try catch to avoid problems --->
            <cftry>
              <cfset columnData = trim(Evaluate(#columnData#))>
              <!--- make boxes containing no data or generated (via this code) a color that stands out --->
              <cfset bgcolor = "white">
              <cfif #columnData# EQ "">
                <cfset bgcolor = "cyan">
              </cfif>
              <!--- get the department evaluator --->
              <cfif UCase(#columnName#) EQ 'SIGN_OFF_ME'>
                <cfquery name="getDeptEvaluator" datasource="#application.datasrc_mss#" dbtype="#application.dbtype_mss#">
              select eval_id 
              from (
              select eval_id
              from employee_profile
              where dept = #vGET_EMPL_LIST.DEPT#
              and SMH_TERM_DT IS NULL
              and EVAL_ID IS NOT NULL
              ORDER BY EVAL_ID ASC
              )
              where rownum = 1
            </cfquery>
                <!--- if we found an evaluator, assign them to the current column's (SIGN_OFF_ME) data --->
                <cfif getDeptEvaluator.RecordCount EQ 1>
                  <cfset columnData = #getDeptEvaluator.EVAL_ID#>
                </cfif>
              </cfif>
              <!--- generate an alias for the user --->
              <cfif UCase(#columnName#) EQ 'MS_ALIAS'>
                <cfset columnData = REReplaceNoCase(MID(#vGET_EMPL_LIST.FIRST_NAME#,1,1) & MID(#vGET_EMPL_LIST.LAST_NAME#,1,5), "[^a-zA-Z]", "")>
              </cfif>
              <!--- generate an domain_id for the user --->
              <cfif UCase(#columnName#) EQ 'DOMAIN_ID'>
                <cfset columnData = REReplaceNoCase(LCASE((MID(#vGET_EMPL_LIST.FIRST_NAME#,1,1) & #vGET_EMPL_LIST.LAST_NAME#)), "[^a-zA-Z]","")>
              </cfif>
              <!--- generate an email for the user --->
              <cfif UCase(#columnName#) EQ 'EMAIL' AND columnData EQ "">
                <cfset columnData = LCASE((REReplaceNoCase(#vGET_EMPL_LIST.FIRST_NAME#, "[^a-zA-Z]","") & "_" & REReplace(#vGET_EMPL_LIST.LAST_NAME#, "[^a-zA-Z]",""))) & "@urmc.rochester.edu">
              </cfif>
              <!--- output the column data --->
              <cfset visible = FALSE>
              <cfif ListFind(visibleData, '#columnName#')>
                <cfset visible = TRUE>
              </cfif>
              <cfif visible>
                <td style="background-color:#bgcolor#;">
                  <cfinput type="text" name="#columnName#" id="#columnName#" value="#columnData#" width="Len(#columnData#)">
                </td>
                <cfelse>
                <cfinput type="hidden" name="#columnName#" id="#columnName#" value="#columnData#">
              </cfif>
              <cfcatch>
                <td>
                  <h1>There was an error... </h1>
                  <h2>query dump is below</h2>
                  <cfdump var="#vGET_EMPL_LIST#">
                </td>
                <cfabort>
              </cfcatch>
            </cftry>
          </cfloop>
          <!--- give the user a SUBMIT button --->
          <td>
            <cfinput type="submit" name="insertEmployeeProfile" value="Add #vGET_EMPL_LIST.LAST_NAME#" style="width: 200px; height: 2em;">
          </td>
        </tr>
      </cfform>
    </cfloop>
  </table>
</cfoutput> 
<!---//END BODY//--->