<cfparam name="url.event_id" default="17">
<cfparam name="url.sess_id" default="0">
<cfquery name="getData" datasource="#application.dsn#">
    <!---
    SELECT S.Title, S.Session_ID, S.Start_Date, S.Start_Time, S.End_Date, S.End_Time, S.Room, S.Code, T.Title AS Track, S.Submitted_Description AS Description, S.Type
    FROM Sessions S
    LEFT OUTER JOIN Tracks T
    ON T.Track_ID = S.Track_ID
    WHERE S.Event_ID =  <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#url.event_id#">
    AND S.Session_ID =  <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#url.sess_id#">
    AND S.Status = 'Active'
    --->

    SELECT <!---'('+CAST(S.Session_ID AS VARCHAR(10))+') '+--->S.Title AS Title, S.Session_ID, S.Start_Date, S.Start_Time, S.End_Date, S.End_Time, S.Room_Location AS Room, S.Location, S.Session_Code AS Code,
          S.Agenda_Type, CAST(S.Description AS VARCHAR(4000)) AS Description, '' AS Type, T.Title AS Track, '' AS Affiliations, '' AS Points_Of_Interest, Co_Sponsor AS Sponsor, S.Venue_ID, S.Floor,
            S.Agenda_Type_Display AS Event_Type, S.Session_Level
    FROM v_Sessions_Expomap S   LEFT OUTER JOIN Tracks T ON T.Track_ID = S.Track_ID
    WHERE S.Event_ID =  <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#url.event_id#">
    AND S.Session_ID =  <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#url.sess_id#">
    <!---AND S.Agenda_Type IN ('Session','Default')--->
    AND S.Itin_Type = 'Post'
    AND S.Public_Private LIKE <cfqueryparam value="%Mobile App%" cfsqltype="CF_SQL_VARCHAR">
    AND S.Status = 'Active'
</cfquery>
<cfset delim = '","'>
<cfset outstr = '{"COLUMNS":["SESS_ID","TITLE","TRACK","ROOM","STARTDATE","STARTDATESORT","STARTTIME","STARTTIME24","ENDTIME","DESCRIPTION","PROGRAM_TYPE","AUDIENCE","INTEREST","SPONSOR","MAP_URL","EVENT_TYPE","CODE","SESSION_LEVEL"],"DATA":['>
<cfset n=0>
<cfloop query="getData">
    <cfset titlestr = rtrim(getData.title)>
    <cfif getData.Code EQ "0">
        <cfset getData.Code = "">
    </cfif>
    <!---
    <cfif len(getData.Code)>
        <cfset titlestr = rtrim(getData.Code) & ": " & titlestr>
    </cfif>
    --->
    <cfset roomstr = rtrim(getData.room)>
    <cfif len(getData.location)>
        <cfset roomstr = roomstr & ", " & rtrim(getData.location)>
    </cfif>

  <!--- data = replace(data, chr(10), '\n', 'all');
    data = replace(data, chr(9), '', 'all');
    data = replace(data, chr(13), '', 'all'); --->
    <cfset desc = replacenocase(getData.description,chr(10),'\n','all')>
    <cfset desc = replace(desc,chr(9),'','all')>
    <cfset desc = replace(desc,chr(10),'','all')>
  <!--- <cfset desc = reReplace(desc, "[^\x20-\x7E]", "", "ALL")> --->
  <!--- <cfset desc = reReplace(desc, "[^\x00-\x7F]", "", "ALL")> --->
  <!--- <cfset desc = paragraphFormat(desc)> --->

    <!--- <cfset desc = "<div style='font-family:arial,sans-serif'>#desc#</div>"> --->
    <cfset mapstr = "">
    <cfquery name="getMap" datasource="#application.dsn#">
        SELECT URL
        FROM ExpoToGo_Maps
        WHERE Event_ID = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#url.event_id#">
        AND Venue_ID = <cfqueryparam value="#getData.Venue_ID#" cfsqltype="CF_SQL_INTEGER">
    </cfquery>
    <cfif getMap.recordcount EQ 1>
        <cfset mapstr = getMap.URL>
    </cfif>
    <cfif getMap.recordcount GT 1>
        <cfquery name="getMap" datasource="#application.dsn#">
            SELECT TOP 1 URL
            FROM ExpoToGo_Maps
            WHERE Event_ID = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#url.event_id#">
            AND Venue_ID = <cfqueryparam value="#getData.Venue_ID#" cfsqltype="CF_SQL_INTEGER">
            AND Level = <cfqueryparam value="#getData.Floor#" cfsqltype="CF_SQL_VARCHAR">
        </cfquery>
        <cfset mapstr = getMap.URL>
    </cfif>
    <cfscript>
        n=n+1;
        if (n GT 1) {
            rdelim = ",";
        } else {
            rdelim = "";
        }
        outstr = outstr & rdelim
            & '["' & getData.session_id & delim
            & replacenocase(titlestr,'"','\"','all') & delim
            & replacenocase(getData.track,'"','\"','all') & delim
            & replacenocase(roomstr,'"','\"','all') & delim
            & replacenocase(dateformat(getData.start_date,"short"),'"','\"','all') & delim
            & replacenocase(dateformat(getData.start_date,"short"),'"','\"','all') & delim
            & replacenocase(timeformat(getData.start_time,"short"),'"','\"','all') & delim
            & replacenocase(timeformat(getData.start_time,"HHmm"),'"','\"','all') & delim
            & replacenocase(timeformat(getData.end_time,"short"),'"','\"','all') & delim
            & replacenocase(trim(desc),'"','\"','all') & delim
            & replacenocase(getData.type,'"','\"','all') & delim
            & replacenocase(getData.affiliations,'"','\"','all') & delim
            & replacenocase(getData.points_of_interest,'"','\"','all') & delim
            & replacenocase(getData.sponsor,'"','\"','all') & delim
            & replacenocase(mapstr,'"','\"','all') & delim
            & replacenocase(getData.Event_Type,'"','\"','all') & delim
            & replacenocase(getData.Code,'"','\"','all') & delim
            & replacenocase(getData.Session_Level,'"','\"','all') & '"]';
    </cfscript>
</cfloop>
<cfset outstr = outstr & ']}'>
<cfoutput>#outstr#</cfoutput>