<cfparam name="url.year" default="2020">
<a href="requestsreport.cfm?year=2017">2017</a> | 
<a href="requestsreport.cfm?year=2018">2018</a> | 
<a href="requestsreport.cfm?year=2019">2019</a> | 
<a href="requestsreport.cfm?year=2020">2020</a> 

<cfif parameterexists(url.id)>
	
<cfquery name="getrequests" dataSource="#ds#">
select ListingsRequests.platform,ListingsRequests.id,ParentsListings.email,ListingsRequests.firstname,ListingsRequests.zipcode,ListingsRequests.lastname,ListingsRequests.entrydate,ListingsRequests.contactemail,ListingsRequests.contactphone,ListingsRequests.contactquestion,ParentsListings.Name,ListingsRequests.itemid,ParentsListings.region,ListingsRequests.followup,ListingsRequests.followupdate,ListingsRequests.nymptag,ParentsListings.advertiser,ListingsRequests.articleid,ParentsListings.salesperson as rep
from ListingsRequests inner join ParentsListings on ListingsRequests.itemid = ParentsListings.id
where ListingsRequests.tablename = 'ParentsListings'
	and itemid = #url.id#
	and year(entrydate) = #url.year#
	order by entrydate desc
</cfquery>

<table border="1" cellpadding="4" cellspacing="0"><tr bgcolor="black">
<Td><font face=verdana size=-1 color="white"><b>Advertiser</b></font></Td>

<Td><font face=verdana size=-1 color="white"><b>Client</b></font></Td>
<Td><font face=verdana size=-1 color="white"><b>Client Email</b></font></Td>

<Td><font face=verdana size=-1 color="white"><b>Rep</b></font></Td>

<Td><font face=verdana size=-1 color="white"><b>Region</b></font></Td>
<Td><font face=verdana size=-1 color="white"><b>Interest?</b></font></Td>

<Td><font face=verdana size=-1 color="white"><b>First Name</b></font></Td>
<Td><font face=verdana size=-1 color="white"><b>Last Name</b></font></Td>
<Td><font face=verdana size=-1 color="white"><b>Date</b></font></Td>
<Td><font face=verdana size=-1 color="white"><b>Email</b></font></Td>
<Td><font face=verdana size=-1 color="white"><b>Phone</b></font></Td>
<Td><font face=verdana size=-1 color="white"><b>Zip</b></font></Td>

<Td><font face=verdana size=-1 color="white"><b>Question</b></font></Td>
<Td><font face=verdana size=-1 color="white"><b>Date responded</b></font></Td>
<Td><font face=verdana size=-1 color="white"><b>Inq. Response</b></font></Td>
<Td><font face=verdana size=-1 color="white"><b>Platform</b></font></Td>

</tr>
<Cfoutput query="Getrequests">
<Cfif #contactquestion# does not contain 'img' and #contactquestion# does not contain 'iframe'>
<tr>

<Td><font face=verdana size=-1>

<Cfif #advertiser# eq 1>
Premium Plus
</Cfif>
<Cfif #advertiser# eq 3>
Basic
</Cfif>
<Cfif #advertiser# eq 0>
Non
</Cfif></Td>

<Td><font face=verdana size=-1><a href="https://www.nymetroparents.com/bestbetsinfo.cfm?id=#itemid#">#name#</a></font></Td>
<Td><font face=verdana size=-2>#email#</a></Td>

<Td><font face=verdana size=-2>#rep#</a></Td>

<Td><font face=verdana size=-1>#region#</a></Td>
<Td><font face=verdana size=-1>#nymptag#</a></Td>

<Td><font face=verdana size=-1>#firstname#</Td>
<Td><font face=verdana size=-1>#lastname#</Td>
<Td><font face=verdana size=-1>#dateformat(entrydate,'m/d/yyyy')#</Td>
<Td><font face=verdana size=-1>#contactemail#</Td>
<Td><font face=verdana size=-1>#contactphone#</Td>
<Td><font face=verdana size=-1>#zipcode#</Td>

<Td>
<cfset Field = ReReplace(contactquestion, "<[^>]*>", "", "all")>
<font face=verdana size=-2>#Field#</Td>
<Td><font face=verdana size=-1><Cfif #followupdate# is not '2001-01-01'>#dateformat(followupdate,'m/d/yyyy')#</Cfif></Td>
<Td><font face=verdana size=-1>#followup#</Td>
<Td><Cfif #id# gt 31918>
	<cfif #articleid# gt 0><a href="https://www.nymetroparents.com/2011va.cfm?colid=#articleid#">Article</a><cfelse>
	<font face=verdana size=-1>#platform#</font></cfif></Cfif></Td>


</tr>
</cfif>
</Cfoutput>
</table>

<cfelse>



<h1><cfoutput>#url.year#</cfoutput></h1>
<cfquery name="geter" dataSource="#ds#">
	select nymptag, count(nymptag) as counter 
	from ListingsRequests
	where year(entrydate) = #url.year#
	and nymptag is not NULL
	and nymptag <> 'I am interested in:'
	and nymptag <> ''

	group by nymptag
	order by counter desc 
</cfquery>
<cfquery name="getertotal" dataSource="#ds#">
	select count(nymptag) as counter 
	from ListingsRequests
	where year(entrydate) = #url.year#
		and nymptag is not NULL
		and nymptag <> ''

	and nymptag <> 'I am interested in:'

</cfquery>
<table><Tr><td valign="top"> 
<table border="1" cellspacing="0" cellpadding="4"> 
<tr style="background-color: black;">
<Td><Font color="white"><b>Category</b></Font></Td>
<Td><Font color="white"><b>%</b></Font></Td>
</tr>
<Cfoutput query="geter">
<tr>
<Td><b>#nymptag#</b></Td>
<td>
	<cfset newperc = #geter.counter# / #getertotal.counter#>
<cfset newperc = newperc * 100>
#round(newperc)#%			
</td></tr></Cfoutput>	
</table>
</td>
<Td valign="top">

<cfquery name="geter" dataSource="#ds#">
	select itemid, count(itemid) as counter, ParentsListings.name, ParentsListings.seo_friendly
	from ListingsRequests inner join ParentsListings on ListingsRequests.itemid = ParentsListings.id 
	where year(entrydate) = #url.year#
	group by itemid
	order by counter desc 
limit 0,15
</cfquery>
<table border="1" cellpadding="4" cellspacing="0">
<cfoutput query="Geter">
<tr><Td><a href="Https://www.nymetroparents.com/listing/#seo_friendly#">#geter.name#</a></Td><td>#geter.counter#</td>
<td><a href="requestsreport.cfm?id=#itemid#&year=#url.year#">view</a></td>
</tr>
	
</cfoutput>	
</table>	
</Td></tr></table>	


<p>
<Table border="1" cellpadding="8" cellspacing="0">
<cfloop from="1" to="12" index="i">
<td style="vertical-align: top">
	<cfset dater = '2017-#i#-01'>
<cfoutput><h2>#dateformat(dater,'mmm')# #url.year#</h2></cfoutput>

<cfquery name="geter" dataSource="#ds#">
	select nymptag, count(nymptag) as counter 
	from ListingsRequests
	where year(entrydate) = #url.year#
	and month(entrydate) = #i#
		and nymptag is not NULL
	and nymptag <> 'I am interested in:'
and nymptag <> ''
	group by nymptag
	order by counter desc 
</cfquery>
<cfquery name="getertotal" dataSource="#ds#">
	select count(nymptag) as counter 
	from ListingsRequests
	where year(entrydate) = #url.year#
		and month(entrydate) = #i#
		and nymptag <> ''

			and nymptag is not NULL
	and nymptag <> 'I am interested in:'


</cfquery>

<table border="1" cellspacing="0" cellpadding="4"> 
<tr style="background-color: black;">
<Td><Font color="white"><b>Category</b></Font></Td>
<Td><Font color="white"><b>%</b></Font></Td>
</tr>
<Cfoutput query="geter">
<tr>
<Td><b>#nymptag#</b></Td>
<td>
	<cfset newperc = #geter.counter# / #getertotal.counter#>
<cfset newperc = newperc * 100>
#round(newperc)#%			
</td></tr></Cfoutput>	
</table>

<cfquery name="geter" dataSource="#ds#">
	select itemid, count(itemid) as counter, ParentsListings.name, ParentsListings.seo_friendly
	from ListingsRequests inner join ParentsListings on ListingsRequests.itemid = ParentsListings.id 
	where year(entrydate) = #url.year#
			and month(entrydate) = #i#

	group by itemid
	order by counter desc 
limit 0,15
</cfquery>
<br>
<table border="1" cellpadding="4" cellspacing="0">
<cfoutput query="Geter">
<tr><Td><a href="Https://www.nymetroparents.com/listing/#seo_friendly#">#geter.name#</a></Td><td>#geter.counter#</td>
<td><a href="requestsreport.cfm?id=#itemid#&year=#url.year#">view</a></td>

</tr>
	
</cfoutput>	
</table>	



</td>		
	
</cfloop>	
</Table>

</cfif>