need to convert this to cfc component

[cfnew]

Hi,

I need help from you guys, I have a query like this which returns some columns and from loop we are getting some values from other queries depending up on first query values like joins.

I need to make this in to CFC and need to return results from first query as well as from the loop queries.

Is there is any way to do this to convert in component.

I need to return result which is shown in cfoutput below.

Thanks

<cfquery name="myquery" datasource="scott">
                    select empno,ename,deptno,job,mgr,sal from emp

</cfquery>
                               
        <cfloop  query="myquery">           
                <cfquery name="test1" datasource="scott">
                     select dname,loc from dept where deptno =#myquery.deptno#
                </cfquery>
               
                <cfquery name="test2" datasource="coldfusion">
                  select job,hiredate from emp where deptno=#myquery.deptno#               
                </cfquery>               
                   
                <cfoutput>
                    <table border="1" width="75%">
                        <tr >
                            <td >
                            <b>First Query</b>#myquery.empno#,#myquery.ename#,#myquery.deptno#  
                            <b>Second Query</b>#test1.dname#,#test1.loc#  
                            <b>Third Query</b>#test2.job#,#DateFormat(test2.hiredate,"mm/dd/yyyy")#
                            </td>
                        </tr>
                    </table>
                </cfoutput>       
        </cfloop>          

[Owainnorth]

To keep it simple I'd create a cfc with three functions:

1 - getEmployees() - returns the first queryset to loop on

2 - getDepartmentByNumber() - returns a queryset or struct when given a deptno

3 - getEmployeesByDepartmentNumber() - performs your last lookup and returns a queryset

Therefore your pseudocode would be:

<cfset qEmployees = mycfc.getEmployees() />

<cfloop query="qEmployees">

  <cfset qThisDept = mycfc.getDepartmentByNumber() />

  <cfset qTheseEmployees = mycfc.getEmployeesByDepartmentNumber() />

  Output stuff here.

</cfloop>

Hope that helps.

O.

[Dan_Bracuk]

Since you are re-writing it anyway, you might want to make it more efficient.

You don't need query test1.  Since it has the same datasource as myquery, you can combine them.

For test2, you may be able to combine this, or you may not.  It depends on database stuff.  Assuming you can't, you can put this query outside the loop so that you only have to run it once.  Pass it a value list from the first query.  Then use Query of Queries to join everything together and output the result.

[cfnew]

Hi Dan,

Thanks for ur reply . My intension is that we have this code incoldfusoon forms that display reports from this code , but now we are moving the same code to flash front end to display reports on flash screen. So now I need to made this to component and return results from component to flash remoting. So I need this code to be converted to component and returns results from all like first query and the queries in loop as a single return like array or structure. Any help in this.

Thanks

[BKBK]

General rules of thumb:

1) No HTML in component;

2) A function should specialize, returning one specific resultset upon request;

3) Use var to make function variables local to the function (especially relevant for garbage-collection of large queries!);

4) Use remote access for functions you intend to call with Flash.

<cfcomponent>
<cffunction name="getEmployee" access="remote" returntype="query">
     <cfset var employee = queryNew("","")>
     <cfquery name="employee" datasource="scott">
         select empno,ename,deptno,job,mgr,sal from emp
     </cfquery>
     <cfreturn employee>
</cffunction>

<cffunction name="getName_loc" access="remote" returntype="query">                             
<cfargument name="deptNo" required="yes" type="numeric">
     <cfset var name_loc = queryNew("","")>          
     <cfquery name="name_loc" datasource="scott">
          select dname,loc from dept where deptno =#arguments.deptno#
     </cfquery>
     <cfreturn name_loc>
</cffunction>   

<cffunction name="getJob_hiredate" access="remote" returntype="query">                            

<cfargument name="deptNo" required="yes" type="numeric">

    <cfset var job_hiredate = queryNew("","")>         

    <cfquery name="job_hiredate" datasource="coldfusion">

         select job,hiredate from emp where deptno = #arguments.deptno#

    </cfquery>

    <cfreturn job_hiredate>

</cffunction>

</cfcomponent>

[cfnew]

HI BKBK,

thanks for ur reply i made finally some structure that i want is to return from cfc and i made this remote for flash remoting.

i have no idea how we can refer this structure to display in flash for example in flash datagrid.

here i copy my code and dump please have a look, now i need to display this structure in flash as datagrid

thanks

<cfcomponent output="false">
    <cffunction name="myquery" access="remote"  returntype="any" output="false">
         <cfsetting requesttimeout="3600"> 
           <cfset mystr= structNew()>
                <cfquery name="myquery" datasource="scott">
                    select empno,ename,deptno,job,mgr,sal from emp
                </cfquery>               
                   
         <cfloop  query="myquery">   
             <cfset mystr[myquery.empno]=structNew()>                       
                <cfquery name="test1" datasource="scott">
                     select dname,loc from dept where deptno =#myquery.deptno#
                </cfquery>               
                <cfquery name="test2" datasource="coldfusion">
                  select hiredate from emp where deptno=#myquery.deptno#               
                </cfquery>                                   
                <cfset mystr[myquery.empno].empno=myquery.empno>
                <cfset mystr[myquery.empno].ename=myquery.ename>
                <cfset mystr[myquery.empno].deptno=myquery.deptno>
                <cfset mystr[myquery.empno].job=myquery.job>
                <cfset mystr[myquery.empno].mgr=myquery.mgr>
                <cfset mystr[myquery.empno].sal=myquery.sal>
                <cfset mystr[myquery.empno].dname=test1.dname>

                 <cfset mystr[myquery.empno].loc=test1.loc>

                <cfset mystr[myquery.empno].hiredate=test2.hiredate>                   

        </cfloop>       
         <cfreturn mystr>
     </cffunction>
</cfcomponent>

CFDUMP HERE:

STRUCTURE TEST - struct
7369	STRUCTURE TEST - struct DEPTNO 20 DNAME RESEARCH EMPNO 7369 ENAME SMITH HIREDATE 1980-12-17 00:00:00.0 JOB CLERK LOC DALLAS MGR 7902 SAL 800.00
7499	STRUCTURE TEST - struct DEPTNO 30 DNAME SALES EMPNO 7499 ENAME ALLEN HIREDATE 1981-02-20 00:00:00.0 JOB SALESMAN LOC CHICAGO MGR 7698 SAL 1600.00
7521	STRUCTURE TEST - struct DEPTNO 30 DNAME SALES EMPNO 7521 ENAME WARD HIREDATE 1981-02-20 00:00:00.0 JOB SALESMAN LOC CHICAGO MGR 7698 SAL 1250.00
7566	STRUCTURE TEST - struct DEPTNO 20 DNAME RESEARCH EMPNO 7566 ENAME JONES HIREDATE 1980-12-17 00:00:00.0 JOB MANAGER LOC DALLAS MGR 7839 SAL 2975.00
7654	STRUCTURE TEST - struct DEPTNO 30 DNAME SALES EMPNO 7654 ENAME MARTIN HIREDATE 1981-02-20 00:00:00.0 JOB SALESMAN LOC CHICAGO MGR 7698 SAL 1250.00
7698	STRUCTURE TEST - struct DEPTNO 30 DNAME SALES EMPNO 7698 ENAME BLAKE HIREDATE 1981-02-20 00:00:00.0 JOB MANAGER LOC CHICAGO MGR 7839 SAL 2850.00
7782	STRUCTURE TEST - struct DEPTNO 10 DNAME ACCOUNTING EMPNO 7782 ENAME CLARK HIREDATE 1981-06-09 00:00:00.0 JOB MANAGER LOC NEW YORK MGR 7839 SAL 2450.00
7788	STRUCTURE TEST - struct DEPTNO 20 DNAME RESEARCH EMPNO 7788 ENAME SCOTT HIREDATE 1980-12-17 00:00:00.0 JOB ANALYST LOC DALLAS MGR 7566 SAL 3000.00
7839	STRUCTURE TEST - struct DEPTNO 10 DNAME ACCOUNTING EMPNO 7839 ENAME KING HIREDATE 1981-06-09 00:00:00.0 JOB PRESIDENT LOC NEW YORK MGR [empty string] SAL 5000.00
7844	STRUCTURE TEST - struct DEPTNO 30 DNAME SALES EMPNO 7844 ENAME TURNER HIREDATE 1981-02-20 00:00:00.0 JOB SALESMAN LOC CHICAGO MGR 7698 SAL 1500.00
7876	STRUCTURE TEST - struct DEPTNO 20 DNAME RESEARCH EMPNO 7876 ENAME ADAMS HIREDATE 1980-12-17 00:00:00.0 JOB CLERK LOC DALLAS MGR 7788 SAL 1100.00
7900	STRUCTURE TEST - struct DEPTNO 30 DNAME SALES EMPNO 7900 ENAME JAMES HIREDATE 1981-02-20 00:00:00.0 JOB CLERK LOC CHICAGO MGR 7698 SAL 950.00
7902	STRUCTURE TEST - struct DEPTNO 20 DNAME RESEARCH EMPNO 7902 ENAME FORD HIREDATE 1980-12-17 00:00:00.0 JOB ANALYST LOC DALLAS MGR 7566 SAL 3000.00
7934	STRUCTURE TEST - struct DEPTNO 10 DNAME ACCOUNTING EMPNO 7934 ENAME MILLER HIREDATE 1981-06-09 00:00:00.0 JOB CLERK LOC NEW YORK MGR 7782 SAL 1300.00

[BKBK]

To avoid ambiguity, rename the function to something like getEmployees. I shall also suppose that your component is saved as Employee.cfc and that gridPage.cfm is in the same directory as the component.

gridPage.cfm

<cfset employeeObject = createobject("component", "Employee")>

<cfset employeeStruct = employeeObject .getEmployees()>

<cfform name = "employeeForm">
    <cfgrid name="employeeGrid" format="flash">
        <cfgridcolumn name="deptno" header="Dept No">
        <cfgridcolumn name="dname" header="Dept name">
        <cfgridcolumn name="empno" header="Emp No">
        <cfgridcolumn name="ename" header="Ename">      
        <cfgridcolumn name="hiredate" header="Hire Date">
        <cfgridcolumn name="job" header="Job">
        <cfgridcolumn name="loc" header="Location">
        <cfgridcolumn name="mgr" header="Mgr">
        <cfgridcolumn name="sal" header="Salary">
      
        <!--- Loop through struct to populate the grid ---> 
        <cfloop list="#structKeyList(employeeStruct)#" index="empNo">
                <cfgridrow data ="#empNo#"><!--- the first column --->
                <cfloop list="#structKeyList(employeeStruct[empNo])#" index="empAttribute">
                       <cfgridrow data ="#employeeStruct[empNo][empAttribute]#">
                </cfloop>
        </cfloop>

    </cfgrid>
</cfform>