Posted By

burnsra on 08/17/09


Tagged

ColdFusion


Versions (?)

familytree.cfc


 / Published in: ColdFusion
 

  1. <cfcomponent output="false">
  2.  
  3. <cfscript>
  4. if (NOT structKeyExists(VARIABLES,'dsn')) {
  5. init();
  6. }
  7. </cfscript>
  8.  
  9. <cffunction name="init" access="public" returntype="any" output="false" hint="Initializes the Service">
  10. <cfargument name="datasource" type="string" required="false" default="dsnCommon" hint="Datasource name for the queries" />
  11. <cfset VARIABLES.dsn = ARGUMENTS.datasource />
  12. <cfreturn this />
  13. </cffunction>
  14.  
  15. <cffunction name="getPrimaryDepartments" access="remote" returntype="query">
  16. <cfargument name="dsn" required="true" default="#VARIABLES.dsn#">
  17. <cfargument name="pri_dpt" type="string" required="false" default="">
  18. <cfset var qryGetPrimaryDepartments="" />
  19. <cfquery name="qryGetPrimaryDepartments" datasource="#ARGUMENTS.dsn#" cachedwithin="#CreateTimeSpan(1,0,0,0)#">
  20. SELECT
  21. pri_dpt_cd
  22. , pri_dpt_tx
  23. , pri_dpt_cd + ' - ' + pri_dpt_tx AS pri_dpt_cd_tx
  24. FROM
  25. common.dbo.familyTree_primary_dept_tbl
  26. WHERE
  27. 1=1
  28. <cfif len(trim(ARGUMENTS.pri_dpt))>
  29. AND
  30. cast(pri_dpt_cd as integer) IN (<cfqueryparam cfsqltype="cf_sql_char" value="#ARGUMENTS.pri_dpt#" list="true">)
  31. </cfif>
  32. ORDER BY
  33. pri_dpt_cd
  34. </cfquery>
  35. <cfreturn qryGetPrimaryDepartments />
  36. </cffunction>
  37. <cffunction name="getRecapDepartments" access="remote" returntype="query">
  38. <cfargument name="dsn" required="true" default="#VARIABLES.dsn#">
  39. <cfargument name="pri_dpt" type="string" required="false" default="">
  40. <cfargument name="rcp_dpt" type="string" required="false" default="">
  41. <cfquery name="qryGetRecapDepartments" datasource="#ARGUMENTS.dsn#" cachedwithin="#CreateTimeSpan(1,0,0,0)#">
  42. SELECT
  43. rcp_dpt_cd
  44. , rcp_dpt_tx
  45. , rcp_dpt_cd + ' - ' + rcp_dpt_tx AS rcp_dpt_cd_tx
  46. FROM
  47. common.dbo.familyTree_recap_dept_tbl
  48. WHERE
  49. 1=1
  50. <cfif len(trim(ARGUMENTS.pri_dpt))>
  51. AND
  52. cast(pri_dpt_cd as integer) IN (<cfqueryparam cfsqltype="cf_sql_char" value="#ARGUMENTS.pri_dpt#" list="true">)
  53. </cfif>
  54. <cfif len(trim(ARGUMENTS.rcp_dpt))>
  55. AND
  56. cast(rcp_dpt_cd as integer) IN (<cfqueryparam cfsqltype="cf_sql_char" value="#ARGUMENTS.rcp_dpt#" list="true">)
  57. </cfif>
  58. ORDER BY
  59. rcp_dpt_cd
  60. </cfquery>
  61. <cfreturn qryGetRecapDepartments />
  62. </cffunction>
  63. <cffunction name="getSubDepartments" access="remote" returntype="query">
  64. <cfargument name="dsn" required="true" default="#VARIABLES.dsn#">
  65. <cfargument name="pri_dpt" type="string" required="false" default="">
  66. <cfargument name="rcp_dpt" type="string" required="false" default="">
  67. <cfargument name="scn_dpt" type="string" required="false" default="">
  68. <cfquery name="qryGetSubDepartments" datasource="#ARGUMENTS.dsn#" cachedwithin="#CreateTimeSpan(1,0,0,0)#">
  69. SELECT
  70. scn_dpt_cd
  71. , scn_dpt_tx
  72. , scn_dpt_cd + ' - ' + scn_dpt_tx AS scn_dpt_cd_tx
  73. FROM
  74. common.dbo.familyTree_scan_dept_tbl
  75. WHERE
  76. 1=1
  77. <cfif len(trim(ARGUMENTS.pri_dpt))>
  78. AND
  79. cast(pri_dpt_cd as integer) IN (<cfqueryparam cfsqltype="cf_sql_char" value="#ARGUMENTS.pri_dpt#" list="true">)
  80. </cfif>
  81. <cfif len(trim(ARGUMENTS.rcp_dpt))>
  82. AND
  83. cast(rcp_dpt_cd as integer) IN (<cfqueryparam cfsqltype="cf_sql_char" value="#ARGUMENTS.rcp_dpt#" list="true">)
  84. </cfif>
  85. <cfif len(trim(ARGUMENTS.scn_dpt))>
  86. AND
  87. cast(scn_dpt_cd as integer) IN (<cfqueryparam cfsqltype="cf_sql_char" value="#ARGUMENTS.scn_dpt#" list="true">)
  88. </cfif>
  89. ORDER BY
  90. scn_dpt_cd
  91. </cfquery>
  92. <cfreturn qryGetSubDepartments />
  93. </cffunction>
  94. <cffunction name="getCommodities" access="remote" returntype="query">
  95. <cfargument name="dsn" required="true" default="#VARIABLES.dsn#">
  96. <cfargument name="pri_dpt" type="string" required="false" default="">
  97. <cfargument name="rcp_dpt" type="string" required="false" default="">
  98. <cfargument name="scn_dpt" type="string" required="false" default="">
  99. <cfargument name="com" type="string" required="false" default="">
  100. <cfquery name="qryGetCommodities" datasource="#ARGUMENTS.dsn#" cachedwithin="#CreateTimeSpan(1,0,0,0)#">
  101. SELECT
  102. commodity.cpt_com_cd
  103. , commodity.com_nam_tx
  104. , commodity.cpt_com_cd + ' - ' + commodity.com_nam_tx AS cpt_com_cd_tx
  105. FROM
  106. common.dbo.familyTree_commodity_tbl commodity
  107. INNER JOIN
  108. common.dbo.familyTree_scan_dept_tbl scan
  109. ON
  110. commodity.cpt_dpt_cd = scan.cpt_dpt_cd
  111. WHERE
  112. 1=1
  113. <cfif len(trim(ARGUMENTS.rcp_dpt))>
  114. AND
  115. cast(scan.rcp_dpt_cd as integer) IN (<cfqueryparam cfsqltype="cf_sql_char" value="#ARGUMENTS.rcp_dpt#" list="true">)
  116. </cfif>
  117. <cfif len(trim(ARGUMENTS.scn_dpt))>
  118. AND
  119. cast(commodity.cpt_dpt_cd as integer) IN (<cfqueryparam cfsqltype="cf_sql_char" value="#ARGUMENTS.scn_dpt#" list="true">)
  120. </cfif>
  121. <cfif len(trim(ARGUMENTS.com))>
  122. AND
  123. cast(commodity.cpt_com_cd as integer) IN (<cfqueryparam cfsqltype="cf_sql_char" value="#ARGUMENTS.com#" list="true">)
  124. </cfif>
  125. </cfquery>
  126. <cfreturn qryGetCommodities />
  127. </cffunction>
  128. <cffunction name="getSubCommodities" access="remote" returntype="query">
  129. <cfargument name="dsn" required="true" default="#VARIABLES.dsn#">
  130. <cfargument name="pri_dpt" type="string" required="false" default="">
  131. <cfargument name="rcp_dpt" type="string" required="false" default="">
  132. <cfargument name="scn_dpt" type="string" required="false" default="">
  133. <cfargument name="com" type="string" required="false" default="">
  134. <cfargument name="sub_com" type="string" required="false" default="">
  135. <cfquery name="qryGetSubCommodities" datasource="#ARGUMENTS.dsn#" cachedwithin="#CreateTimeSpan(1,0,0,0)#">
  136. SELECT
  137. sub_commodity.sub_com_cd
  138. , sub_commodity.sub_com_nam_tx
  139. , sub_commodity.sub_com_cd + ' - ' + sub_commodity.sub_com_nam_tx AS sub_com_cd_tx
  140. FROM
  141. common.dbo.familyTree_sub_commodity_tbl sub_commodity
  142. INNER JOIN
  143. common.dbo.familyTree_scan_dept_tbl scan
  144. ON
  145. sub_commodity.cpt_dpt_cd = scan.cpt_dpt_cd
  146. WHERE
  147. 1=1
  148. <cfif len(trim(ARGUMENTS.rcp_dpt))>
  149. AND
  150. cast(scan.rcp_dpt_cd as integer) IN (<cfqueryparam cfsqltype="cf_sql_char" value="#ARGUMENTS.rcp_dpt#" list="true">)
  151. </cfif>
  152. <cfif len(trim(ARGUMENTS.scn_dpt))>
  153. AND
  154. cast(sub_commodity.cpt_dpt_cd as integer) IN (<cfqueryparam cfsqltype="cf_sql_char" value="#ARGUMENTS.scn_dpt#" list="true">)
  155. </cfif>
  156. <cfif len(trim(ARGUMENTS.com))>
  157. AND
  158. cast(sub_commodity.cpt_com_cd as integer) IN (<cfqueryparam cfsqltype="cf_sql_char" value="#ARGUMENTS.com#" list="true">)
  159. </cfif>
  160. <cfif len(trim(ARGUMENTS.sub_com))>
  161. AND
  162. cast(sub_commodity.sub_com_cd as integer) IN (<cfqueryparam cfsqltype="cf_sql_char" value="#ARGUMENTS.sub_com#" list="true">)
  163. </cfif>
  164. </cfquery>
  165. <cfreturn qryGetSubCommodities />
  166. </cffunction>
  167. </cfcomponent>

Report this snippet  

You need to login to post a comment.