Skip to content

Instantly share code, notes, and snippets.

@trycf

trycf/trycf-gist-1771970947819-d3c339e3-5521-4350-b390-160ff73241f6.cfm

Created February 25, 2026 19:07
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save trycf/6b8f378251fb9b7db5216ff59e890578 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save trycf/6b8f378251fb9b7db5216ff59e890578 to your computer and use it in GitHub Desktop.
Download ZIP
TryCF Gist
Raw
trycf-gist-1771970947819-d3c339e3-5521-4350-b390-160ff73241f6.cfm
<cfcomponent>
<cfset PEDIGREE_TECH_API = 'https://oneview2.pedigreetechnologies.com/api/inspectionData.json?'>
<cfset PEDIGREE_TECH_API_SERVICES = 'https://oneview2.pedigreetechnologies.com/services/api/'>
<cfset hascURL ="https://www.hacsc.com/API/DataBatch/TrainingHistory/GetHistory">
<cfcookie name="PEDIGREE_TECH_DATA">
<cffunction name="getHASCTrainingRecords" access="remote" returntype="any" output="yes">
<cfargument name="company_code" type="string" required="yes">
<cfargument name="start_date" type="string" required="yes">
<cfargument name="end_date" type="string" required="yes">
<cfquery name="CompanyAPIKey" datasource="mssqlcf_locstatt_2">
select api_company_settings_id, api_name, api_key,c.company_code, company_name
from Locstatt_API_Company_Settings s, Company c
where s.company_code = #Arguments.company_code# and active= 1
and api_name = 'HASC'
and c.company_code =s.company_code
</cfquery>
<cfif #CompanyAPIKey.recordcount# EQ 0>
<cfscript>
JsonResult=SerializeJSON("false","struct");
writeOutput(JsonResult);
abort;
</cfscript>
</cfif>
<cfset stFields = {
StartDate: '#Arguments.start_date#', EndDate: '#Arguments.end_date#'
}>
<cfhttp method="Post" url="#hascURL#">
<cfhttpparam type="header" name="X-ApiKey" value="#CompanyAPIKey.API_KEY[1]#">
<cfhttpparam type="header" name="Content-Type" value="application/json" >
<cfhttpparam type="body" value="#serializeJSON(stFields)#">
</cfhttp>
<cfset cfData=DeserializeJSON(#cfhttp.filecontent#)>
<cfif #arraylen(cfData.History)# EQ 0>
<!--- No records found --->
<cfelse>
<!--- record found/ insert the header --->
<cfstoredproc procedure="setNewAPITrainingLogHeader" datasource="mssqlcf_locstatt_2">
<cfprocparam type="in" dbvarname="@api_company_settings_id" value="#CompanyAPIKey.API_COMPANY_SETTINGS_ID[1]#" cfsqltype="cf_sql_integer">
<cfprocparam type="in" dbvarname="@start_date" value="#Arguments.start_date#" cfsqltype="cf_sql_varchar">
<cfprocparam type="in" dbvarname="@end_date" value="#Arguments.end_date#" cfsqltype="cf_sql_varchar">
<cfprocresult name="theResult">
</cfstoredproc>
<cfset cont_records_added = 0>
<cfset cont_employees_without_integration = 0>
<cfset cont_new_courses = 0>
<cfset duplicated_records = 0>
<cfset cont_fail_records = 0>
<!--- email vars --->
<cfset new_courses_detail = '<br>'>
<cfset new_employees_detail = '<br>'>
<cfset employees_updated_detail = '<br>'>
<cfset employees_fail_detail = '<br>'>
<cfloop from="1" to="#arraylen(cfData.History)#" index="x">
<cfinvoke method="verifyTheTrainingRecordAndSaveIt" returnvariable="theRecordStatus">
<cfinvokeargument name="api_training_log_header_id" value="#theResult.API_TRAINING_LOG_HEADER_ID[1]#">
<cfinvokeargument name="trainee_id" value="#cfData.History[x].TraineeId#">
<cfinvokeargument name="expiration_date" value="#cfData.History[x].ExpiryDate#">
<cfinvokeargument name="taken_date" value="#cfData.History[x].CompletedDate#">
<cfinvokeargument name="course_code" value="#cfData.History[x].CourseCode#">
<cfinvokeargument name="score" value="#cfData.History[x].Score#">
<cfinvokeargument name="status" value="#cfData.History[x].Status#">
<cfinvokeargument name="company_code" value="#CompanyAPIKey.COMPANY_CODE[1]#">
<cfinvokeargument name="api_company_settings_id" value="#CompanyAPIKey.API_COMPANY_SETTINGS_ID[1]#">
</cfinvoke>
<cfswitch expression="#theRecordStatus#">
<cfcase value= 1>
<cfset cont_records_added = cont_records_added + 1>
<cfset employees_updated_detail = employees_updated_detail & '<br> #cfData.History[x].TraineeId# - #cfData.History[x].CourseCode# '>
</cfcase>
<cfcase value= -501>
<cfset cont_employees_without_integration = cont_employees_without_integration + 1>
<cfset new_employees_detail = new_employees_detail & '<br> #cfData.History[x].TraineeId# '>
</cfcase>
<cfcase value= -502>
<cfset cont_new_courses = cont_new_courses + 1>
<cfset new_courses_detail = new_courses_detail & '<br> #cfData.History[x].CourseCode# - #cfData.History[x].CourseDescription#'>
</cfcase>
<cfcase value= -505>
<cfset cont_fail_records = cont_fail_records + 1>
<cfset employees_fail_detail = employees_fail_detail & '<br> #cfData.History[x].TraineeId# - #cfData.History[x].CourseCode# (#cfData.History[x].Score#%)'>
</cfcase>
<cfdefaultcase><!--- -503 --->
<cfset duplicated_records = duplicated_records + 1>
</cfdefaultcase>
</cfswitch>
</cfloop>
<!--- update the log header --->
<cfstoredproc procedure="setUpdateAPITrainingLogHeader" datasource="mssqlcf_locstatt_2">
<cfprocparam type="in" dbvarname="@api_training_log_header_id" value="#theResult.API_TRAINING_LOG_HEADER_ID[1]#" cfsqltype="cf_sql_integer">
<cfprocparam type="in" dbvarname="@employees_not_found" value="#cont_employees_without_integration#" cfsqltype="cf_sql_integer">
<cfprocparam type="in" dbvarname="@total_records_in_api_call" value="#arraylen(cfData.History)#" cfsqltype="cf_sql_integer">
<cfprocparam type="in" dbvarname="@new_courses" value="#cont_new_courses#" cfsqltype="cf_sql_integer">
<cfprocparam type="in" dbvarname="@records_duplicated" value="#duplicated_records#" cfsqltype="cf_sql_integer">
<cfprocparam type="in" dbvarname="@records_updated" value="#cont_records_added#" cfsqltype="cf_sql_integer">
</cfstoredproc>
<!--- Email summarty to support team --->
<cfmail type="html" to="contact@locstatt.com" from="locstatt@locstatt.com (New API Transaction Summary - #CompanyAPIKey.API_NAME[1]#)"
subject="Weekly API/ Log (#CompanyAPIKey.COMPANY_NAME[1]#) - #Arguments.end_date# ">
<b> Date Range </b>: #Arguments.start_date# - #Arguments.end_date# <br>
<b> Total Training Records in the API Call </b> : #arraylen(cfData.History)# <br>
<b> Total Training Records Added </b> : #cont_records_added# <br>
#employees_updated_detail# <br>
<b> New Courses </b> : #new_courses_detail# <br>
<b> Employees without Integration </b> : #new_employees_detail# <br>
<b> Employees with Fail Scores </b> : #employees_fail_detail# <br>
</cfmail>
<cfscript>
JsonResult=SerializeJSON("#cfData.History#","struct");
writeOutput(JsonResult);
abort;
</cfscript>
</cfif>
<cfmail type="html" to="contact@locstatt.com" from="locstatt@locstatt.com (New API Transaction Summary - #CompanyAPIKey.API_NAME[1]#)"
subject="Weekly API/ Log (#CompanyAPIKey.COMPANY_NAME[1]#) - #Arguments.end_date# ">
<b> Date Range </b>: #Arguments.start_date# - #Arguments.end_date# <br>
<b> No records Found with the Date Range Criteria</b>
</cfmail>
<cfscript>
JsonResult=SerializeJSON("false","struct");
writeOutput(JsonResult);
abort;
</cfscript>
<cfreturn cfData>
</cffunction>
<cffunction name="getAPITrainingLogHistoryByAPI" returntype="any" access="remote" output="true">
<cfargument name="api_company_settings_id" type="numeric" required="yes">
<cfargument name="company_code" type="numeric" required="yes">
<cfargument name="start_date" type="string" required="yes">
<cfargument name="end_date" type="string" required="yes">
<cfstoredproc procedure="getAPITrainingLogHistoryByAPI" datasource="mssqlcf_locstatt_2">
<cfprocparam type="in" dbvarname="@api_company_settings_id" value="#Arguments.api_company_settings_id#" cfsqltype="cf_sql_integer">
<cfprocparam type="in" dbvarname="@company_code" value="#Arguments.company_code#" cfsqltype="cf_sql_integer">
<cfprocparam type="in" dbvarname="@start_date" value="#Arguments.start_date#" cfsqltype="cf_sql_varchar">
<cfprocparam type="in" dbvarname="@end_date" value="#Arguments.end_date#" cfsqltype="cf_sql_varchar">
<cfprocresult name="theResult">
</cfstoredproc>
<cfscript>
JsonResult=SerializeJSON(#theResult#,"struct");
writeOutput(JsonResult);
abort;
</cfscript>
<cfreturn true>
</cffunction>
<cffunction name="getEmployeeTrainingRecordAPILogDetail" returntype="any" access="remote" output="true">
<cfargument name="api_training_log_header_id" type="numeric" required="yes">
<cfstoredproc procedure="getEmployeeTrainingRecordAPILogDetail" datasource="mssqlcf_locstatt_2">
<cfprocparam type="in" dbvarname="@api_training_log_header_id" value="#Arguments.api_training_log_header_id#" cfsqltype="cf_sql_integer">
<cfprocresult name="theResult">
</cfstoredproc>
<cfscript>
JsonResult=SerializeJSON(#theResult#,"struct");
writeOutput(JsonResult);
abort;
</cfscript>
<cfreturn true>
</cffunction>
<cffunction name="verifyTheTrainingRecordAndSaveIt" access="remote" returntype="any" output="yes">
<cfargument name="api_training_log_header_id" type="numeric" required="yes">
<cfargument name="trainee_id" type="numeric" required="yes">
<cfargument name="expiration_date" type="string" required="yes">
<cfargument name="taken_date" type="string" required="yes">
<cfargument name="course_code" type="string" required="yes">
<cfargument name="score" type="numeric" required="yes">
<cfargument name="status" type="string" required="yes">
<cfargument name="company_code" type="numeric" required="yes">
<cfargument name="api_company_settings_id" type="numeric" required="yes">
<cfset expiration_date_with_format = "#Arguments.expiration_date#">
<cfset taken_date_with_format = "#Arguments.taken_date#">
<cfset record_status = 0>
<cfif '#Arguments.status#' EQ 'Fail'>
<cfset record_status = -505>
<cfreturn record_status>
</cfif>
<cfstoredproc procedure="getEmployeeTrainingRecordFromAPIValitation" datasource="mssqlcf_locstatt_2">
<cfprocparam type="in" dbvarname="@company_code" value="#Arguments.company_code#" cfsqltype="cf_sql_integer">
<cfprocparam type="in" dbvarname="@trainee_id" value="#Arguments.trainee_id#" cfsqltype="cf_sql_varchar">
<cfprocparam type="in" dbvarname="@training_code" value="#Arguments.course_code#" cfsqltype="cf_sql_varchar">
<cfprocparam type="in" dbvarname="@expiration_date" value="#expiration_date_with_format#" cfsqltype="cf_sql_varchar">
<cfprocparam type="in" dbvarname="@api_company_settings_id" value="#Arguments.api_company_settings_id#" cfsqltype="cf_sql_integer">
<cfprocresult name="theValitationResult">
</cfstoredproc>
<cfif #theValitationResult.MESSAGE_CODE[1]# EQ 0> <!--- Employees ready to be added --->
<cfstoredproc procedure="setEmployeeTrainingRecordFromAPICall" datasource="mssqlcf_locstatt_2">
<cfprocparam type="in" dbvarname="@training_code" value="#Arguments.course_code#" cfsqltype="cf_sql_varchar">
<cfprocparam type="in" dbvarname="@trainee_id" value="#Arguments.trainee_id#" cfsqltype="cf_sql_varchar">
<cfprocparam type="in" dbvarname="@expiration_date" value="#Arguments.expiration_date#" cfsqltype="cf_sql_varchar">
<cfprocparam type="in" dbvarname="@taken_date" value="#Arguments.taken_date#" cfsqltype="cf_sql_varchar">
<cfprocparam type="in" dbvarname="@score" value="#Arguments.score#" cfsqltype="cf_sql_integer">
<cfprocparam type="in" dbvarname="@company_code" value="#Arguments.company_code#" cfsqltype="cf_sql_integer">
<cfprocparam type="in" dbvarname="@api_company_settings_id" value="#Arguments.api_company_settings_id#" cfsqltype="cf_sql_integer">
<cfprocresult name="theEmployeeRecordResult">
</cfstoredproc>
<!--- insert the record in the API log detail --->
<cfstoredproc procedure="setNewEmployeeTrainingRecordAPILogDetail" datasource="mssqlcf_locstatt_2">
<cfprocparam type="in" dbvarname="@api_training_log_header_id" value="#Arguments.api_training_log_header_id#" cfsqltype="cf_sql_integer">
<cfprocparam type="in" dbvarname="@employee_training_id" value="#theEmployeeRecordResult.EMPLOYEE_TRAINING_ID[1]#" cfsqltype="cf_sql_varchar">
</cfstoredproc>
<cfset record_status = 1><!--- success --->
<cfelseif #theValitationResult.MESSAGE_CODE[1]# EQ -501> <!--- Employee without training integration -hasc --->
<cfset record_status = -501>
<cfelseif #theValitationResult.MESSAGE_CODE[1]# EQ -502> <!--- Course code doesnt exist --->
<cfset record_status = -502>
<cfelse> <!--- the record already exists in the database --->
<cfset record_status = -503>
</cfif>
<cfreturn record_status>
</cffunction>
<cffunction name="setUserPedigreeTechSession" access="remote" returntype="any" output="yes">
<cfargument name="user_code" type="numeric" required="yes">
<cfstoredproc procedure="getLocstattExternalIntegrationsAccessByUserCode" datasource="mssqlcf_locstatt_2">
<cfprocparam type="in" dbvarname="@user_code" value="#Arguments.user_code#" cfsqltype="cf_sql_integer">
<cfprocresult name="userIntegrationResult">
</cfstoredproc>
<cfif #userIntegrationResult.recordCount# NEQ 0>
<cfset Cookie.PEDIGREE_TECH_DATA.user_x = "#userIntegrationResult.USER_NAME[1]#">
<cfset Cookie.PEDIGREE_TECH_DATA.password_x = "#userIntegrationResult.PASSWORD[1]#">
<cfscript>
JsonResult=SerializeJSON("TRUE","struct");
writeOutput(JsonResult);
abort;
</cfscript>
</cfif>
<cfscript>
JsonResult=SerializeJSON("FALSE","struct");
writeOutput(JsonResult);
abort;
</cfscript>
</cffunction>
<cffunction name="getVehicleInspectionsFailed" access="remote" returntype="any" output="yes">
<cfargument name="includePassedInspections" type="string" required="yes">
<cfargument name="includePassedItems" type="string" required="yes">
<cfargument name="modifiedSince" type="string" required="yes">
<cfargument name="timeRange" type="string" required="yes">
<cfset my_url_request='#PEDIGREE_TECH_API#modifiedSince=#Arguments.modifiedSince#&timeRange=#Arguments.timeRange#&includePassedInspections=#Arguments.includePassedInspections#&includePassedItems=#Arguments.includePassedItems#'>
<cfhttp username="#Cookie.PEDIGREE_TECH_DATA.user_x#" password="#Cookie.PEDIGREE_TECH_DATA.password_x#" method="get" url="#my_url_request#">
</cfhttp>
<cfset cfData=DeserializeJSON(#cfhttp.filecontent#)>
<cfscript>
JsonResult=SerializeJSON(#cfData#,"struct");
writeOutput(JsonResult);
abort;
</cfscript>
</cffunction>
<cffunction name="getEmployeesAndDrivers" access="remote" returntype="any" output="yes">
<cfset my_url_request='#PEDIGREE_TECH_API_SERVICES#users/groups/users'>
<cfhttp username="#Cookie.PEDIGREE_TECH_DATA.user_x#" password="#Cookie.PEDIGREE_TECH_DATA.password_x#" method="get" url="#my_url_request#">
</cfhttp>
<cfset cfData=DeserializeJSON(#cfhttp.filecontent#)>
<cfscript>
JsonResult=SerializeJSON(#cfData#,"struct");
writeOutput(JsonResult);
abort;
</cfscript>
</cffunction>
<cffunction name="getTractorsBYCompany" access="remote" returntype="any" output="yes">
<cfset my_url_request='#PEDIGREE_TECH_API_SERVICES#tractors'>
<cfhttp username="#Cookie.PEDIGREE_TECH_DATA.user_x#" password="#Cookie.PEDIGREE_TECH_DATA.password_x#" method="get" url="#my_url_request#">
</cfhttp>
<cfset cfData=DeserializeJSON(#cfhttp.filecontent#)>
<cfscript>
JsonResult=SerializeJSON(#cfData#,"struct");
writeOutput(JsonResult);
abort;
</cfscript>
</cffunction>
<cffunction name="getVehicleEventHistory" access="remote" returntype="any" output="yes">
<cfargument name="vehicle_id" type="string" required="yes">
<cfargument name="endTime" type="string" required="yes">
<cfargument name="startTime" type="string" required="yes">
<cfargument name="eventName" type="string" required="yes">
<cfset my_url_request='#PEDIGREE_TECH_API_SERVICES#assets/#Arguments.vehicle_id#/events/range?&startTime=#Arguments.startTime#&endTime=#Arguments.endTime#&eventName=#Arguments.eventName#'>
<cfhttp username="#Cookie.PEDIGREE_TECH_DATA.user_x#" password="#Cookie.PEDIGREE_TECH_DATA.password_x#" method="get" url="#my_url_request#">
</cfhttp>
<cfset cfData=DeserializeJSON(#cfhttp.filecontent#)>
<cfscript>
JsonResult=SerializeJSON(#cfData#,"struct");
writeOutput(JsonResult);
abort;
</cfscript>
</cffunction>
<cffunction name="getVehicleSummary" access="remote" returntype="any" output="yes">
<cfargument name="vehicle_id" type="string" required="yes">
<cfset my_url_request='#PEDIGREE_TECH_API_SERVICES#assets/#Arguments.vehicle_id#/readings?&returnUiName=true'>
<cfhttp username="#Cookie.PEDIGREE_TECH_DATA.user_x#"
password="#Cookie.PEDIGREE_TECH_DATA.password_x#" method="get" url="#my_url_request#">
</cfhttp>
<cfset cfData=DeserializeJSON(#cfhttp.filecontent#)>
<cfscript>
JsonResult=SerializeJSON(#cfData#,"struct");
writeOutput(JsonResult);
abort;
</cfscript>
</cffunction>
<cffunction name="getAPIListByCompany" access="remote" returntype="any" output="true">
<cfargument name="company_code" type="numeric" required="yes">
<cfstoredproc procedure="getAPIListByCompany" datasource="mssqlcf_locstatt_2">
<cfprocparam type="in" dbvarname="@company_code" value="#Arguments.company_code#" cfsqltype="cf_sql_integer">
<cfprocresult name="theResult">
</cfstoredproc>
<cfscript>
JsonResult=SerializeJSON(#theResult#,"struct");
writeOutput(JsonResult);
abort;
</cfscript>
</cffunction>
<cffunction name="getCompanyEmployeeListByAPI" access="remote" returntype="any" output="true">
<cfargument name="api_company_settings_id" type="numeric" required="yes">
<cfargument name="company_code" type="numeric" required="yes">
<cfargument name="option" type="numeric" required="no" default="0">
<cfif #Arguments.option# EQ 0>
<cfstoredproc procedure="getCompanyEmployeeListByAPI" datasource="mssqlcf_locstatt_2">
<cfprocparam type="in" dbvarname="@api_company_settings_id" value="#Arguments.api_company_settings_id#" cfsqltype="cf_sql_integer">
<cfprocparam type="in" dbvarname="@company_code" value="#Arguments.company_code#" cfsqltype="cf_sql_integer">
<cfprocresult name="theResult">
</cfstoredproc>
<cfelse>
<cfstoredproc procedure="getCompanyEmployeeListByAPIWithOrWithout" datasource="mssqlcf_locstatt_2">
<cfprocparam type="in" dbvarname="@api_company_settings_id" value="#Arguments.api_company_settings_id#" cfsqltype="cf_sql_integer">
<cfprocparam type="in" dbvarname="@company_code" value="#Arguments.company_code#" cfsqltype="cf_sql_integer">
<cfprocparam type="in" dbvarname="@option" value="#Arguments.option#" cfsqltype="cf_sql_integer">
<cfprocresult name="theResult">
</cfstoredproc>
</cfif>
<cfscript>
JsonResult=SerializeJSON(#theResult#,"struct");
writeOutput(JsonResult);
abort;
</cfscript>
</cffunction>
<cffunction name="setNewEmployeeAPIIntegration" access="remote" returntype="any" output="true">
<cfargument name="emp_code" type="numeric" required="yes">
<cfargument name="api_employee_id" type="string" required="yes">
<cfargument name="api_company_settings_id" type="numeric" required="yes">
<cfargument name="company_code" type="numeric" required="yes">
<cfquery name="verifyEmployee" datasource="mssqlcf_locstatt_2">
select t.emp_code , ce.active, (emp_first_name + ' ' + emp_last_name)as emp_name
from Locstatt_API_Employee_Integration t, company_employee ce,
Employee e
where t.api_employee_id ='#Arguments.api_employee_id#' and t.api_company_settings_id= #Arguments.api_company_settings_id#
and t.emp_code <> #Arguments.emp_code# and t.active= 1
and ce.emp_code = e.emp_code
and t.emp_code = ce.emp_code
and ce.company_code = #Arguments.company_code#
</cfquery>
<cfif #verifyEmployee.recordcount# NEQ 0>
<cfscript>
JsonResult=SerializeJSON(#verifyEmployee#,"struct");
writeOutput(JsonResult);
abort;
</cfscript>
</cfif>
<cfquery name="inactiveOldIntegrations" datasource="mssqlcf_locstatt_2">
update Locstatt_API_Employee_Integration set active= 0
where api_company_settings_id= #Arguments.api_company_settings_id#
and emp_code = #Arguments.emp_code# and active= 1
</cfquery>
<cfstoredproc procedure="setNewEmployeeAPIIntegration" datasource="mssqlcf_locstatt_2">
<cfprocparam type="in" dbvarname="@emp_code" value="#Arguments.emp_code#" cfsqltype="cf_sql_integer">
<cfprocparam type="in" dbvarname="@api_employee_id" value="#Arguments.api_employee_id#" cfsqltype="cf_sql_varchar">
<cfprocparam type="in" dbvarname="@api_company_settings_id" value="#Arguments.api_company_settings_id#" cfsqltype="cf_sql_integer">
</cfstoredproc>
<cfscript>
JsonResult=SerializeJSON("true","struct");
writeOutput(JsonResult);
abort;
</cfscript>
</cffunction>
<cffunction name="setDeleteEmployeeAPIIntegration" access="remote" returntype="any" output="true">
<cfargument name="emp_code" type="numeric" required="yes">
<cfargument name="api_company_settings_id" type="numeric" required="yes">
<cfquery name="inactiveIntegrationsQuery" datasource="mssqlcf_locstatt_2">
update Locstatt_API_Employee_Integration set active= 0
where api_company_settings_id= #Arguments.api_company_settings_id#
and emp_code = #Arguments.emp_code# and active= 1
</cfquery>
<cfscript>
JsonResult=SerializeJSON("true","struct");
writeOutput(JsonResult);
abort;
</cfscript>
</cffunction>
</cfcomponent>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment