b1nary0mega
12/1/2014 - 10:38 PM
This file will pull the needed staff information that is required to be sent to FillShift for use with their system. It includes a robust er
This file will pull the needed staff information that is required to be sent to FillShift for use with their system. It includes a robust error reporting email should something fail.
<!---
NAME: sendStaffFeed.cfm
AUTH: James R. Aylesworth
DATE: 12-1-2014
REQS: fillshift.cfc
DESC: This file will pull the needed staff information that is required
to be sent to FillShift for use with their system.
Initial pilot to begin with 334.
--->
<!--- set the timeout to 2min --->
<cfsetting requesttimeout = "120">
<!--- the path for the server folder to save --->
<cfset folderPath = "C:\Inetpub\wwwroot\medsched\reports\fillshift\files\">
<!--- who should the file(s) be emailed to --->
<cfset mailRecipients = "support@fillshift.com">
<cftry>
<!--- List of units to loop through --->
<cfloop index="unitID" delimiters="," list="334">
<cfquery name="getEmployeeTimes"
datasource="#application.datasrc_mss#"
dbtype="#application.dbtype_mss#" >
SELECT TRIM(DEPT) AS DEPT,
TRIM(UNIT_ID) AS UNIT_ID,
TRIM(EMPLOYEE_ID) AS UNIQUE_ID,
TRIM(DOMAIN_ID) AS USER_ID,
TRIM(FIRST_NAME) AS FIRST_NAME,
TRIM(LAST_NAME) AS LAST_NAME,
TRIM(POS_CODE) AS POS_CODE,
TRIM(TITLE) AS GRADING,
TRIM(MIN_WKLY_HRS) AS MIN_WKLY_HRS,
TRIM(EMAIL) AS EMAIL,
DATE_LAST_MODIFIED,
DATE_CREATED
FROM EMPLOYEE_PROFILE
WHERE UNIT_ID = '<cfoutput>#unitID#</cfoutput>'
AND EMPLOYEE_ID IN
( SELECT DISTINCT employee_id
FROM employee_profile
WHERE unit_id = '<cfoutput>#unitID#</cfoutput>'
AND ( mss_unit_term_dt IS NULL
OR mss_unit_term_dt > SYSDATE )
)
AND pos_code NOT IN ('0','1','2','573','642',2310) <!--- exclude all but the primary nursing persons --->
ORDER BY UNIT_ID,
GRADING,
LAST_NAME,
FIRST_NAME,
MIN_WKLY_HRS DESC
</cfquery>
<cfinvoke component="fillshift"
method="QueryToCSV"
returnvariable="csvOutput">
<cfinvokeargument name="Query" value="#getEmployeeTimes#" />
<cfinvokeargument name="Fields" value="DEPT,UNIT_ID,UNIQUE_ID,USER_ID,FIRST_NAME,LAST_NAME,POS_CODE,GRADING,MIN_WKLY_HRS,EMAIL,DATE_LAST_MODIFIED,DATE_CREATED" />
<cfinvokeargument name="CreateHeaderRow" value="TRUE" />
</cfinvoke>
<!--- create csv file and force download
<cfheader name="Content-disposition" value="attachment;filename=URMC-SMH-Employee_Feed(#DateFormat(Now(), 'dd-MMM-yyyy')#).csv">
<cfcontent type="text/csv">
<cfoutput>#csvOutput#</cfoutput>
--->
<!--- Output data into a variable for saving --->
<cfsavecontent variable="saveContent">
<cfoutput>#csvOutput#</cfoutput>
</cfsavecontent>
<!--- Write out the file it it doesn't already exist --->
<cfoutput>
<cfset fileName = "#folderPath#URMC-SMH-Employee_Feed-#unitID#-(#DateFormat(Now(), 'dd-MMM-yyyy')#).csv">
<cfif FileExists(#fileName#)>
<p>#fileName# already exists...writing over it</p>
<cffile action="delete" file="#fileName#">
<cffile action="write" file="#fileName#" charset="utf-8" output="#saveContent#" >
<cfelse>
<cffile action="write" file="#fileName#" charset="utf-8" output="#saveContent#" >
<p>#fileName# was written successfully....</p>
</cfif>
<!--- mail out the csv file --->
<cfmail to="#mailRecipients#"
cc="james_aylesworth@urmc.rochester.edu"
from="donotreply@medinfo.rochester.edu"
subject="URMC #unitID# Staffing Sheet">
<cfmailparam file="#fileName#">
Attached please find the employee roster for #unitID# in csv format.
</cfmail>
</cfoutput>
</cfloop>
<!--- something went wrong - email the programmer --->
<cfcatch type="any">
<cfmail to="james_aylesworth@urmc.rochester.edu"
from="donotreply@medinfo.rochester.edu"
subject="URMC Staffing Sheet FAILED"
type="html">
Generation and mailing of URMC SMH Staffing sheet for FillShift Failed.
<p>An error occurred on #DateFormat( Now(), "mmm d, yyyy" )# at #TimeFormat( Now(), "hh:mm TT" )#</p>
page - #CGI.SCRIPT_NAME#
<p>
<h3>ERROR</h3>
<cfdump var="#VARIABLES.Error#" label="Error object." />
<h3>CGI</h3>
<cfdump var="#CGI#" label="CGI object" />
<h3>REQUEST</h3>
<cfdump var="#REQUEST#" label="REQUEST object" top="5" />
<h3>FORM</h3>
<cfdump var="#FORM#" label="FORM object" top="5" />
<h3>URL</h3>
<cfdump var="#URL#" label="URL object" top="5" />
<h3>SESSION</h3>
<cfdump var="#SESSION#" label="SESSION object" top="5" />
</p>
</cfmail>
</cfcatch>
</cftry>
<!---
NAME: fillshift.cfc
AUTH: Ben Nadel
James R. Aylesworth
DATE: November 6th, 2014
DESC: This component will take a query and header values and return a csv
--->
<cfcomponent>
<!--- From Ben Nadel @ http://www.bennadel.com/blog/1239-updated-converting-a-coldfusion-query-to-csv-using-querytocsv.htm --->
<cffunction
name="QueryToCSV"
access="public"
returntype="string"
output="false"
hint="I take a query and convert it to a comma separated value string.">
<!--- Define arguments. --->
<cfargument
name="Query"
type="query"
required="true"
hint="I am the query being converted to CSV."
/>
<cfargument
name="Fields"
type="string"
required="true"
hint="I am the list of query fields to be used when creating the CSV value."
/>
<cfargument
name="CreateHeaderRow"
type="boolean"
required="false"
default="true"
hint="I flag whether or not to create a row of header values."
/>
<cfargument
name="Delimiter"
type="string"
required="false"
default=","
hint="I am the field delimiter in the CSV value."
/>
<!--- Define the local scope. --->
<cfset var LOCAL = {} />
<!---
First, we want to set up a column index so that we can
iterate over the column names faster than if we used a
standard list loop on the passed-in list.
--->
<cfset LOCAL.ColumnNames = [] />
<!---
Loop over column names and index them numerically. We
are working with an array since I believe its loop times
are faster than that of a list.
--->
<cfloop
index="LOCAL.ColumnName"
list="#ARGUMENTS.Fields#"
delimiters=",">
<!--- Store the current column name. --->
<cfset ArrayAppend(
LOCAL.ColumnNames,
Trim( LOCAL.ColumnName )
) />
</cfloop>
<!--- Store the column count. --->
<cfset LOCAL.ColumnCount = ArrayLen( LOCAL.ColumnNames ) />
<!---
Now that we have our index in place, let's create
a string buffer to help us build the CSV value more
effiently.
--->
<cfset LOCAL.Buffer = CreateObject( "java", "java.lang.StringBuffer" ).Init() />
<!--- Create a short hand for the new line characters. --->
<cfset LOCAL.NewLine = (Chr( 13 ) & Chr( 10 )) />
<!--- Check to see if we need to add a header row. --->
<cfif ARGUMENTS.CreateHeaderRow>
<!--- Create array to hold row data. --->
<cfset LOCAL.RowData = [] />
<!--- Loop over the column names. --->
<cfloop
index="LOCAL.ColumnIndex"
from="1"
to="#LOCAL.ColumnCount#"
step="1">
<!--- Add the field name to the row data. --->
<cfset LOCAL.RowData[ LOCAL.ColumnIndex ] = """#LOCAL.ColumnNames[ LOCAL.ColumnIndex ]#""" />
</cfloop>
<!--- Append the row data to the string buffer. --->
<cfset LOCAL.Buffer.Append(
JavaCast(
"string",
(
ArrayToList(
LOCAL.RowData,
ARGUMENTS.Delimiter
) &
LOCAL.NewLine
))
) />
</cfif>
<!---
Now that we have dealt with any header value, let's
convert the query body to CSV. When doing this, we are
going to qualify each field value. This is done be
default since it will be much faster than actually
checking to see if a field needs to be qualified.
--->
<!--- Loop over the query. --->
<cfloop query="ARGUMENTS.Query">
<!--- Create array to hold row data. --->
<cfset LOCAL.RowData = [] />
<!--- Loop over the columns. --->
<cfloop
index="LOCAL.ColumnIndex"
from="1"
to="#LOCAL.ColumnCount#"
step="1">
<!--- Add the field to the row data. --->
<cfset LOCAL.RowData[ LOCAL.ColumnIndex ] = """#Replace( ARGUMENTS.Query[ LOCAL.ColumnNames[ LOCAL.ColumnIndex ] ][ ARGUMENTS.Query.CurrentRow ], """", """""", "all" )#""" />
</cfloop>
<!--- Append the row data to the string buffer. --->
<cfset LOCAL.Buffer.Append(
JavaCast(
"string",
(
ArrayToList(
LOCAL.RowData,
ARGUMENTS.Delimiter
) &
LOCAL.NewLine
))
) />
</cfloop>
<!--- Return the CSV value. --->
<cfreturn LOCAL.Buffer.ToString() />
</cffunction>
</cfcomponent>