Creating excel report by manipulating the Query result set

There was a situation where in I had to generate a report of team members of different teams, where in a single record should be there for each team with team member names in the same row. But then the database has 4 records for each team.

It was a bit of a challenge for me. Usually in these situations I would use the group attribute to loop through, but I used this query of queries to solve this.

<cfset filename = expandPath("./teamsReport.xls")>
  
 
  <cfset s = spreadsheetNew()>
  <cfset spreadsheetAddRow(s, "Team Id, Member 1,  Member 2,  Member 3 ,  Member 4 ")>
   
   <!--- Loop through the Query result set--->
  <!--- -Create a query header-->
    <cfset qryListTeam = QueryNew("Team_Id,Member_1,Member_2, Member_3,Member_4","Integer,VarChar, VarChar, VarChar,VarChar") />
    <cfset i=0 />
    <cfset j=1 />

Make a Query Of Queries call which would fetch the  4 records of the team.

      <cfoutput query="teamList" group="TEAM_ID">
      
            <cfset queryAddRow(qryListTeam,1) />
            <cfset querySetCell(qryListTeam,"Team_Id",#TEAM_ID#) />
                <cfinvoke component="tag_report" method="listTeamMembers" returnvariable="teamMemberList">
                      <cfinvokeargument name="team_id" value="#TEAM_ID#" >
                      <cfinvokeargument name="teamList" value="#teamList#">
             </cfinvoke>
      
 Build the  qryListTeam which we had already declared.

        <cfset i = 1 />
      
  <cfloop query="#teamMemberList#" startrow="1" endRow="#teamMemberList.RecordCount#">
                <cfif i eq 1>
                    <cfset querySetCell(qryListTeam,"Member_1",#MEMBER_NAME#) />
                    </cfif>
                <cfif i eq 2>
                    <cfset querySetCell(qryListTeam,"Member_2",#MEMBER_NAME#) /> 
                 </cfif>
                <cfif i eq 3>
                    <cfset querySetCell(qryListTeam,"Member_3",#MEMBER_NAME#) />
                 </cfif>
                <cfif i eq 4>
                    <cfset querySetCell(qryListTeam,"Member_4",#TA_MEMBER_NAME#) />
                    </cfif>
                <cfset i++ />
        </cfloop>
    </cfoutput>

    <!--- format header --->   
     <cfset spreadsheetFormatRow(s,
            {
                bold=true,
                fgcolor="lemon_chiffon",
                fontsize=14
            },
            1)>

Now using the custom built Query result set is ready to be used in our spreadsheet.

  <cfset spreadsheetAddRows(s, qryListTeam)>
  <cfset spreadsheetWrite(s, filename, true)>
    <cfheader name="content-disposition" value="attachment; filename=TeamsReport.xls">
   <cfcontent type="application/msexcel" variable="#spreadsheetReadBinary(s)#" reset="true">

Escape pound sign(#) in Coldfusion

There came a scenario, where in the query string which carried the address of the users to another application had a # sign, which broke the system. Pound sign(#), having a special meaning in coldfusion, I had pretty tough time solving it. The solution is just simple.

eg If the address goes like this

    <cfset var address1 = #447, North Avenue />(Probably this is coming from the database)

    Doubling the hash would work if it's displayed in the cfm, wrapped in <cfoutput></cfoutput>. In this case I had to use this

    <cfset queryString = queryString & Replace(address1,"##","CHR(35)","all") />

•   ## - Here pound sign is escaped by doubling it.
• CHR(35) is the ASCII equivalent of  pound(#) sign.

New line string not appearing in Javascript(Jquery) alert

The error message had multiple lines. But the new line character I had inserted in the string was not recognized.

Eg.
To have a successful authentication, please ensure you have fulfilled the requisites.\nBecome a member by paying the membership amount.\nNot all services come free.

But the newline character was not at all recognized. Instead it alerted the string along with \n.

The reason being while the string reaches ajax from the controller, '\n' becomes '\\n'. The solution for this issue is this...

alert(msg.replace(/\\n/g,"\n"));

This would alert the message as intended.

Renewal of session and the expected behaviour of CFLOGIN

Lately I used CFLOGIN to manage user's session and its so powerful. It comes up with so many luxury of setting up the user roles, and at any point of time, IsUserLoggedIn(), GetAuthUser() comes in handy.

CFLOGOUT makes life much more easier to logout a user. But then it doesn't do everything like clearing session values, etc. In fact, clearing the session values have to be handled by us for the right behaviour of the application.

The real glitch is the way cflogin and session handles the timeout. There are two rules.

1. If the session expires, cflogin expires.
2. If the cflogin expires, session doesn't have to expire and  rather it doesn't expire.

So careful steps have to be taken while setting the session time out in app.cfc and idletimeout in cflogin. 

Now, if a user is given an option to renew a session, a soft refresh using ajax post would do. ie an ajax post to the server would be considered as a call to the server and the session would be considered as renewed.

But cflogin doesn't recognize that. In that case, a hard refresh of the page is needed to make a call to the application/web server. That would let the cflogin renew its session too.