familytree.cfc

<cfcomponent output="false">
 
	<cfscript>
		if (NOT structKeyExists(VARIABLES,'dsn')) {
			init();
		}
	</cfscript>
 
	<cffunction name="init" access="public" returntype="any" output="false" hint="Initializes the Service">
		<cfargument name="datasource" type="string" required="false" default="dsnCommon" hint="Datasource name for the queries" />
		<cfset VARIABLES.dsn = ARGUMENTS.datasource />
		<cfreturn this />
	</cffunction>
 
	<cffunction name="getPrimaryDepartments" access="remote" returntype="query">
		<cfargument name="dsn" required="true" default="#VARIABLES.dsn#">
		<cfargument name="pri_dpt" type="string" required="false" default="">
		<cfset var qryGetPrimaryDepartments="" />
		<cfquery name="qryGetPrimaryDepartments" datasource="#ARGUMENTS.dsn#" cachedwithin="#CreateTimeSpan(1,0,0,0)#">
			SELECT
				pri_dpt_cd
				, pri_dpt_tx
				, pri_dpt_cd + ' - ' + pri_dpt_tx AS pri_dpt_cd_tx
			FROM
				common.dbo.familyTree_primary_dept_tbl
			WHERE
				1=1
			<cfif len(trim(ARGUMENTS.pri_dpt))>
			AND
				cast(pri_dpt_cd as integer) IN (<cfqueryparam cfsqltype="cf_sql_char" value="#ARGUMENTS.pri_dpt#" list="true">)
			</cfif>
			ORDER BY
				pri_dpt_cd
		</cfquery>
		<cfreturn qryGetPrimaryDepartments />
	</cffunction>	
	<cffunction name="getRecapDepartments" access="remote" returntype="query">
		<cfargument name="dsn" required="true" default="#VARIABLES.dsn#">
		<cfargument name="pri_dpt" type="string" required="false" default="">
		<cfargument name="rcp_dpt" type="string" required="false" default="">
		<cfquery name="qryGetRecapDepartments" datasource="#ARGUMENTS.dsn#" cachedwithin="#CreateTimeSpan(1,0,0,0)#">
			SELECT
				rcp_dpt_cd
				, rcp_dpt_tx
				, rcp_dpt_cd + ' - ' + rcp_dpt_tx AS rcp_dpt_cd_tx
			FROM
				common.dbo.familyTree_recap_dept_tbl
			WHERE
				1=1
			<cfif len(trim(ARGUMENTS.pri_dpt))>
			AND
				cast(pri_dpt_cd as integer) IN (<cfqueryparam cfsqltype="cf_sql_char" value="#ARGUMENTS.pri_dpt#" list="true">)
			</cfif>
			<cfif len(trim(ARGUMENTS.rcp_dpt))>
			AND
				cast(rcp_dpt_cd as integer) IN (<cfqueryparam cfsqltype="cf_sql_char" value="#ARGUMENTS.rcp_dpt#" list="true">)
			</cfif>
			ORDER BY
				rcp_dpt_cd
		</cfquery>
		<cfreturn qryGetRecapDepartments />
	</cffunction>
	<cffunction name="getSubDepartments" access="remote" returntype="query">
		<cfargument name="dsn" required="true" default="#VARIABLES.dsn#">
		<cfargument name="pri_dpt" type="string" required="false" default="">
		<cfargument name="rcp_dpt" type="string" required="false" default="">
		<cfargument name="scn_dpt" type="string" required="false" default="">
		<cfquery name="qryGetSubDepartments" datasource="#ARGUMENTS.dsn#" cachedwithin="#CreateTimeSpan(1,0,0,0)#">
			SELECT
				scn_dpt_cd
				, scn_dpt_tx
				, scn_dpt_cd + ' - ' + scn_dpt_tx AS scn_dpt_cd_tx
			FROM
				common.dbo.familyTree_scan_dept_tbl
			WHERE
				1=1
			<cfif len(trim(ARGUMENTS.pri_dpt))>
			AND
				cast(pri_dpt_cd as integer) IN (<cfqueryparam cfsqltype="cf_sql_char" value="#ARGUMENTS.pri_dpt#" list="true">) 
			</cfif>
			<cfif len(trim(ARGUMENTS.rcp_dpt))>
			AND
				cast(rcp_dpt_cd as integer) IN (<cfqueryparam cfsqltype="cf_sql_char" value="#ARGUMENTS.rcp_dpt#" list="true">)
			</cfif>
			<cfif len(trim(ARGUMENTS.scn_dpt))>
			AND
				cast(scn_dpt_cd as integer) IN (<cfqueryparam cfsqltype="cf_sql_char" value="#ARGUMENTS.scn_dpt#" list="true">)
			</cfif>
			ORDER BY
				scn_dpt_cd
		</cfquery>
		<cfreturn qryGetSubDepartments />
	</cffunction>
	<cffunction name="getCommodities" access="remote" returntype="query">
		<cfargument name="dsn" required="true" default="#VARIABLES.dsn#">
		<cfargument name="pri_dpt" type="string" required="false" default="">
		<cfargument name="rcp_dpt" type="string" required="false" default="">
		<cfargument name="scn_dpt" type="string" required="false" default="">
		<cfargument name="com" type="string" required="false" default="">
		<cfquery name="qryGetCommodities" datasource="#ARGUMENTS.dsn#" cachedwithin="#CreateTimeSpan(1,0,0,0)#">
			SELECT
				commodity.cpt_com_cd
				, commodity.com_nam_tx
				, commodity.cpt_com_cd + ' - ' + commodity.com_nam_tx AS cpt_com_cd_tx
			FROM
				common.dbo.familyTree_commodity_tbl commodity
			INNER JOIN
				common.dbo.familyTree_scan_dept_tbl scan
			ON
				commodity.cpt_dpt_cd = scan.cpt_dpt_cd
			WHERE
				1=1
			<cfif len(trim(ARGUMENTS.rcp_dpt))>
			AND
				cast(scan.rcp_dpt_cd as integer) IN (<cfqueryparam cfsqltype="cf_sql_char" value="#ARGUMENTS.rcp_dpt#" list="true">)
			</cfif>
			<cfif len(trim(ARGUMENTS.scn_dpt))>
			AND
				cast(commodity.cpt_dpt_cd as integer) IN (<cfqueryparam cfsqltype="cf_sql_char" value="#ARGUMENTS.scn_dpt#" list="true">)
			</cfif>
			<cfif len(trim(ARGUMENTS.com))>
			AND
				cast(commodity.cpt_com_cd as integer) IN (<cfqueryparam cfsqltype="cf_sql_char" value="#ARGUMENTS.com#" list="true">)
			</cfif>
		</cfquery>
		<cfreturn qryGetCommodities />
	</cffunction>
	<cffunction name="getSubCommodities" access="remote" returntype="query">
		<cfargument name="dsn" required="true" default="#VARIABLES.dsn#">
		<cfargument name="pri_dpt" type="string" required="false" default="">
		<cfargument name="rcp_dpt" type="string" required="false" default="">
		<cfargument name="scn_dpt" type="string" required="false" default="">
		<cfargument name="com" type="string" required="false" default="">
		<cfargument name="sub_com" type="string" required="false" default="">
		<cfquery name="qryGetSubCommodities" datasource="#ARGUMENTS.dsn#" cachedwithin="#CreateTimeSpan(1,0,0,0)#">
			SELECT
				sub_commodity.sub_com_cd
				, sub_commodity.sub_com_nam_tx
				, sub_commodity.sub_com_cd + ' - ' + sub_commodity.sub_com_nam_tx AS sub_com_cd_tx
			FROM
				common.dbo.familyTree_sub_commodity_tbl sub_commodity
			INNER JOIN
				common.dbo.familyTree_scan_dept_tbl scan
			ON
				sub_commodity.cpt_dpt_cd = scan.cpt_dpt_cd
			WHERE
				1=1
			<cfif len(trim(ARGUMENTS.rcp_dpt))>
			AND
				cast(scan.rcp_dpt_cd as integer) IN (<cfqueryparam cfsqltype="cf_sql_char" value="#ARGUMENTS.rcp_dpt#" list="true">)
			</cfif>
			<cfif len(trim(ARGUMENTS.scn_dpt))>
			AND
				cast(sub_commodity.cpt_dpt_cd as integer) IN (<cfqueryparam cfsqltype="cf_sql_char" value="#ARGUMENTS.scn_dpt#" list="true">)
			</cfif>
			<cfif len(trim(ARGUMENTS.com))>
			AND
				cast(sub_commodity.cpt_com_cd as integer) IN (<cfqueryparam cfsqltype="cf_sql_char" value="#ARGUMENTS.com#" list="true">)
			</cfif>
			<cfif len(trim(ARGUMENTS.sub_com))>
			AND
				cast(sub_commodity.sub_com_cd as integer) IN (<cfqueryparam cfsqltype="cf_sql_char" value="#ARGUMENTS.sub_com#" list="true">)
			</cfif>
		</cfquery>
		<cfreturn qryGetSubCommodities />
	</cffunction>
</cfcomponent>