-
SQL Help - Tough One!?!
Ok, im looking at some problems with coldfusion / SQL statements. The code was NOT written by me but ive been asked to look at the problem. (Code is at end of post)
There are two table in question - tbl_delivery_report & tbl_client_clip.
The tables cant be linked eg there is no feasable join. the code uses DISTINCT but it means that we are unable to run the delivery reports unless a client clip has been uploaded. I understand the point of the distinct because the drop down lost doesnt want to show (example) 11/03 23 times. Id like to combine a field (Month_NR - contains date in a mm/yy format) from BOTH tables, then use the distinct statement. that way, it would read in the dates from the delivery table as well, the major problem at the moment is the drop down doesnt show a delivery date option unless there was a clip (hope im making sense)
I dont know why the code was written this way, maybe someone could shed some logical reason lol
Code is as folows:
<cf_mod_login>
<cfparam name="form.Report_Type" default="delivery">
<!---<cfparam name="form.Month_NR" default="#dateformat(dateadd("m", -1, now()), "mm/yy")#">--->
<cfparam name="form.Month_NR" default="#dateformat(dateadd("m", 0, now()), "mm/yy")#">
<!--- if url variables passed, set form variables equal to url --->
<cfif isdefined("url.Month_NR")>
<cfset form.Month_NR = url.Month_NR>
</cfif>
<!--- pull out client name to filter database --->
<cfquery name="rs_client_name" datasource="#request.dsn#" username="#request.uid#" password="#request.pwd#">
SELECT Client_Name FROM Tbl_Client WHERE Client_ID = #session.sv_client#
</cfquery>
<!--- list all clips for client --->
<cfquery name="rs_report" datasource="#request.dsn#" username="#request.uid#" password="#request.pwd#">
SELECT * FROM Tbl_Delivery_Report
WHERE Client_Name LIKE '#rs_client_name.Client_Name#%' AND Month_NR LIKE '#form.Month_NR#%'
</cfquery>
<!--- HERE IS THE PROBLEM ---!>
<!--- pull out unique month numbers to filter in dropdown --->
<cfquery name="rs_filter" datasource="#request.dsn#" username="#request.uid#" password="#request.pwd#">
SELECT distinct Month_NR
FROM Tbl_Client_Clip
WHERE Client_Name LIKE '#rs_client_name.Client_Name#%'
</cfquery>
<!-----------------------------!>
<!--- pull out min ID to pass to placement report page --->
<cfquery name="rs_min" datasource="#request.dsn#" username="#request.uid#" password="#request.pwd#">
SELECT MIN(Client_Clip_ID) AS varMin FROM Tbl_Client_Clip WHERE Client_Name LIKE
'#rs_client_name.Client_Name#'<cfif isdefined("form.month_nr") AND #form.month_nr# gt "0"> AND Month_NR = '#form.Month_NR#'</cfif>
</cfquery>
<!--- send to placement report page if selected. --->
<cfif form.Report_Type EQ "placement">
<cflocation url="report.cfm?Client_Clip_ID=#rs_min.varMin#&Mon th_NR=#form.Month_NR#&new=1" addtoken="no">
</cfif>
<!--- send to valuation report page if selected --->
<cfif form.Report_Type EQ "valuation">
<cflocation url="report_valuation.cfm?Month_NR=#form.Month_NR# " addtoken="no">
</cfif>
<cfquery name="rs_user" datasource="#request.dsn#" username="#request.uid#" password="#request.pwd#">
SELECT #request.uid#.Tbl_Client_Contact.Client_Contact_ID , #request.uid#.Tbl_Client_Contact.First_Name, #request.uid#.Tbl_Client_Contact.Last_Name, #request.uid#.Tbl_Client.Client_Name, #request.uid#.Tbl_Client.Client_Logo
FROM #request.uid#.Tbl_Client_Contact INNER JOIN #request.uid#.Tbl_Client ON #request.uid#.Tbl_Client_Contact.Client_ID = #request.uid#.Tbl_Client.Client_ID
WHERE #request.uid#.Tbl_Client_Contact.Client_Contact_ID = #session.sv_login#
</cfquery>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Product Placement - 1st Place - Premium Product Placement</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="stylesheets/client_main.css" rel="stylesheet" type="text/css">
<script language="javascript" src="scripts/pop.js"></script>
</head>
<body>
<!-- Global div tags start begin -->
<div id="global">
<div id="container">
<!-- Global div tags start end -->
<!-- Start of pageheader and navigation -->
<div class="pageheader">
<cfinclude template="navbar_clients.cfm">
<div class="breadtext">
delivery report
</div>
</div>
<!-- End of pageheader and navigation -->
<div class="maincontentarea">
<cfoutput>
<div class="clipuserimage2">
<img src="images/company_logos/#rs_user.Client_Logo#" alt="#rs_user.Client_Name#" style="padding-bottom: 20px;"><br />
</div>
</cfoutput>
<a name="top"></a>
<div class="reportbox">
<table width="669" border="0" cellpadding="3" cellspacing="1">
<form name="frm_filter" action="report_delivery.cfm" method="post">
<tr class="textbold">
<td colspan="12">
<table width="100%" border="0" cellspacing="0" cellpadding="3">
<tr class="bodytextsmallbold">
<td>Filter by Month (mm/yy)</td>
<td>
<select name="Month_NR" class="bodytextsmall">
<option value="0">Select All...</option>
<cfoutput query="rs_filter">
<option <cfif Month_NR EQ form.Month_NR>selected</cfif>>#Month_NR#</option>
</cfoutput> </select>
</td>
<td>
Report Type
</td>
<td>
<select name="Report_Type" class="bodytextsmall">
<option value="valuation" <cfif form.Report_Type EQ "valuation">selected</cfif>>Valuation Report</option>
<option value="placement" <cfif form.Report_Type EQ "placement">selected</cfif>>Placement Report</option>
<option value="delivery" <cfif form.Report_Type EQ "delivery">selected</cfif>>Delivery Report</option>
</select>
<input name="Submit" type="submit" class="text" value="Filter">
</td>
</tr>
</table>
</td>
</tr>
<tr class="textbold">
<td height="5" colspan="12">
<img src="nav/shim.gif" width="10" height="5"><br />
</td>
</tr>
</form>
<tr>
<td align="center" bgcolor="#C9CDD3" class="bodytextwhitebold" style="padding-top: 10px; padding-bottom: 10px;">
Title
</td>
<td align="center" bgcolor="#C9CDD3" class="bodytextwhitebold">
Entry No
</td>
<td align="center" bgcolor="#C9CDD3" class="bodytextwhitebold">
Channel
</td>
<td align="center" bgcolor="#C9CDD3" class="bodytextwhitebold">
Programme Type
</td>
<td align="center" bgcolor="#C9CDD3" class="bodytextwhitebold">
Delivery
</td>
<td align="center" bgcolor="#C9CDD3" class="bodytextwhitebold">
Transmission Date
</td>
<td align="center" bgcolor="#C9CDD3" class="bodytextwhitebold">
Product
</td>
</tr>
<cfoutput query="rs_report">
<tr class="bodytextbold">
<td align="center" style="padding-top: 20px;">
#Programme#<br />
</td>
<td align="center" style="padding-top: 20px;">
#Delivery_No#<br />
</td>
<td align="center" style="padding-top: 20px;">
#Channel#<br />
</td>
<td align="center" style="padding-top: 20px;">
#Programme_Type#<br />
</td>
<td align="center" style="padding-top: 20px;">
#Delivery_Date#<br />
</td>
<td align="center" style="padding-top: 20px;">
#Bullshit#<br />
</td>
<td align="center" style="padding-top: 20px;">
#Branded_Prop#<br />
</td>
</tr>
<tr>
<td height="24" colspan="7" class="bodytext">
<p>#paragraphformat(Description_1)#</p>
<p>#paragraphformat(Description_2)#</p>
</td>
</tr>
<tr>
<td align="right" colspan="7" class="bodytext" style="margin-bottom: 10px; padding-bottom: 10px; border-bottom: 1px dashed ##C9CDD3;">
<a href="##top">back to top</a><br />
</td>
</tr>
</cfoutput>
</table>
</div>
</div>
<!-- Footer Start -->
<cfinclude template="footerbar.cfm">
<!-- Footer End -->
<!-- Global div tags end begin -->
</div>
</div>
<!-- Global div tags finish end -->
<script src="http://www.google-analytics.com/urchin.js" type="text/javascript">
</script>
<script type="text/javascript">
_uacct = "UA-377381-1";
urchinTracker();
</script>
</body>
</html>
