<%
Dim ClientID
Dim DivisionID
Dim strStartDate
Dim strEndDate
Dim i
Dim s
Dim R
Dim arrType(6,4)
ClientID = request.querystring("CID")
DivisionID = request.querystring("DID")
strStartDate = request.querystring("strStartDate")
strEndDate = request.querystring("strEndDate")
strSort = request.querystring("strSort")
strStaff = request.querystring("strStaff")
chkFD = request.querystring("chkFD")
if len(strStartDate) > 0 then
strWhere = "WHERE tblClientPublication.strDate >= '" & dbdate(strStartDate) & "' AND tblClientPublication.strDate <= '" & strEndDate & "'"
if chkFD = "1" then
strWhere = strWhere + " AND tblClientPublication.blnFD = '1' "
end if
'response.write request.querystring("strStaff")
if strStaff > 0 then
strWhere = strWhere + " AND tblStaff.StaffID = " & request.querystring("strStaff") & " "
end if
select case strSort
case "0"
strOrder = " ORDER BY tblPublication.strType, tblPublication.strPublicationName;"
case "Publication"
strOrder = " ORDER BY tblPublication.strPublicationName;"
case "Date"
strOrder = " ORDER BY tblClientPublication.strPubDate;"
case "Type"
strOrder = " ORDER BY tblPublication.strType;"
case "Division"
strOrder = " ORDER BY tblClientDivision.strClientDivision;"
end select
if ClientID > 0 then strWhere = strWhere & " AND tblClient.ClientID=" & ClientID
if DivisionID > 0 then strWhere = strWhere & " AND tblClientDivision.ClientDivisionID=" & DivisionID
s = "SELECT tblPublication.strPublicationName, tblClientPublication.Vaule1, tblClientPublication.Vaule2, tblClientPublication.Vaule3, tblClientPublication.Vaule4, tblClientPublication.Vaule5, tblClientPublication.Vaule6, tblClientPublication.Vaule7, tblClientPublication.Vaule8, tblClientPublication.refSubPubID, tblClientPublication.strSelctedField, tblPublication.strType, tblClientPublication.blnFD, tblClient.ClientID, tblClient.strClient, tblClientDivision.ClientDivisionID, tblClientDivision.strClientDivision, tblStaff.StaffID, tblStaff.strStaff, tblPublication.PublicationID, tblClientPublication.strClientPublication, tblClientPublication.strPubDate, tblClientPublication.blnPicture, tblClientPublication.blnFD " & _
"FROM (((tblClientPublication LEFT JOIN tblClientDivision ON tblClientPublication.refClientDivisionID = tblClientDivision.ClientDivisionID) INNER JOIN tblClient ON tblClientPublication.refClientID = tblClient.ClientID) LEFT JOIN tblPublication ON tblClientPublication.refPublicationID = tblPublication.PublicationID) LEFT OUTER JOIN tblStaff ON tblClientPublication.refStaffID = tblStaff.StaffID " & _
strWhere & strOrder
'response.write s
set rst = cn.execute(s)
if not rst.eof then
c = rst("strClient")
d = rst("strClientDivision")
end if
if request.querystring("DID") > 0 then
showDiv = d & " - "
else
showDiv = ""
end if
R = R & "
"
'Loop Results
while not rst.eof
's = "SELECT strPublication FROM tblPublication_" & rst("strType") & " WHERE SubPubID = " & rst("refSubPubID")
'response.write s
'set rstSub = cn.execute(s)
if NOT pTYPE = rst("strType") then
'write Type heading
'R = R & "
Category: " & rst("strType") & "
"
pTYPE = rst("strType")
end if
x = swapIt(x)
intCirculation = 0
intReaders = 0
select case rst("strType")
case "Magazine"
if rst(2) <> 0 then
intCirculation = rst(2)
else
ss = "SELECT dblImpression FROM tblPublication_Magazine WHERE SubPubID = " & rst("refSubPubID")
set rstSub = cn.execute(ss)
intCirculation = cdbl(rstSub("dblImpression"))
end if
if rst(1) <> 0 then
intReaders = rst(1)
else
ss = "SELECT dblImpression FROM tblPublication_Magazine WHERE SubPubID = " & rst("refSubPubID")
set rstSub = cn.execute(ss)
intReaders = cdbl(rstSub("dblImpression"))
end if
if rst(1) = 0 AND rst(2) = 0 then
ss = "SELECT dblImpression FROM tblPublication_Magazine WHERE SubPubID = " & rst("refSubPubID")
set rstSub = cn.execute(ss)
intCirculation = cdbl(rstSub("dblImpression"))
intReaders = cdbl(rstSub("dblImpression"))
end if
case "Newspaper"
if rst(2) <> 0 then
intCirculation = rst(2)
else
ss = "SELECT intCirculation, dblImpression FROM tblPublication_Newspaper WHERE SubPubID = " & rst("refSubPubID")
set rstSub = cn.execute(ss)
intCirculation = cdbl(rstSub("intCirculation"))
end if
if rst(1) <> 0 then
intReaders = rst(1)
else
ss = "SELECT intCirculation, dblImpression FROM tblPublication_Newspaper WHERE SubPubID = " & rst("refSubPubID")
set rstSub = cn.execute(ss)
intReaders = cdbl(rstSub("dblImpression"))
end if
if rst(1) = 0 AND rst(2) = 0 then
ss = "SELECT intCirculation, dblImpression FROM tblPublication_Newspaper WHERE SubPubID = " & rst("refSubPubID")
set rstSub = cn.execute(ss)
intCirculation = cdbl(rstSub("intCirculation"))
intReaders = cdbl(rstSub("dblImpression"))
end if
case "Tv"
if rst(2) = 0 then
intCirculation = rst(1)
intReaders = rst(1)
end if
if rst(1) = 0 AND rst(2) = 0 then
ss = "SELECT dblImpression FROM tblPublication_TV WHERE SubPubID = " & rst("refSubPubID")
set rstSub = cn.execute(ss)
intCirculation = cdbl(rstSub("dblImpression"))
intReaders = cdbl(rstSub("dblImpression"))
end if
case "Radio"
if rst(2) = 0 then
intCirculation = rst(1)
intReaders = rst(1)
end if
if rst(1) = 0 AND rst(2) = 0 then
ss = "SELECT dblImpression FROM tblPublication_Radio WHERE SubPubID = " & rst("refSubPubID")
set rstSub = cn.execute(ss)
intCirculation = cdbl(rstSub("dblImpression"))
intReaders = cdbl(rstSub("dblImpression"))
end if
case "Web"
if rst(2) = 0 then
intCirculation = rst(1)
intReaders = rst(1)
end if
if rst(1) = 0 AND rst(2) = 0 then
ss = "SELECT dblImpression FROM tblPublication_Web WHERE SubPubID = " & rst("refSubPubID")
set rstSub = cn.execute(ss)
intCirculation = cdbl(rstSub("dblImpression"))
intReaders = cdbl(rstSub("dblImpression"))
end if
end select
'response.write rst(22) & "/"
if len(rst(22)) > 0 then
if rst(22) <> "1" then
blnPicture = "No"
else
blnPicture = "Yes"
end if
end if
'response.write(intCirculation)
if intCirculation = 0 then
displayintCirculation = "Unavailable"
else
displayintCirculation = formatnumber(intCirculation,0)
end if
if intReaders = 0 then
displayreaders = "Unavailable"
else
displayreaders = formatnumber(intReaders,0)
end if
if rst(6) = 0 then
displayrate = "Unavailable"
else
if rst("strType") = "Web" then
displayrate = "R " & formatnumber(rst(6),3)
else
displayrate = "R " & formatnumber(rst(6),2)
end if
end if
if rst(7) = 0 then
displayadvalue = "Unavailable"
else
displayadvalue = "R " & formatnumber(rst(7),2)
end if
if rst(8) = 0 then
displayprvalue = "Unavailable"
else
displayprvalue = "R " & formatnumber(rst(8),2)
end if
if rst("blnFD") then blnFD = "Yes" else blnFD = "No" ' formatnumber(rst(1),0)
'added border right - pj - 20100317
R = R & "
" & rst(0) & "
" & apdate(rst("strPubDate")) & "
" & displayintCirculation & "
" & displayreaders & "
" & _
"
" & rst("strClientPublication") & "
" & rst(4) & "
" & rst(5) & "
" & rst(3) & "
" & _
"
" & displayrate & "
" & displayadvalue & "
" & displayprvalue & "
" & rst("strClientDivision") & "
" & blnPicture & "
" & blnFD & "
"
'inc totals
rst.movenext
j = j + 1
wend
'Totals..1
ss = "SELECT tblPublication.strPublicationName, tblPublication.strType, tblClientPublication.refSubPubID, tblClientPublication.Vaule1 as Vaule1, tblClientPublication.Vaule2 AS Vaule2,tblClientPublication.Vaule8 AS Vaule8,tblClientPublication.Vaule7 AS Vaule7 " & _
"FROM (((tblClientPublication LEFT JOIN tblClientDivision ON tblClientPublication.refClientDivisionID = tblClientDivision.ClientDivisionID) INNER JOIN tblClient ON tblClientPublication.refClientID = tblClient.ClientID) LEFT JOIN tblPublication ON tblClientPublication.refPublicationID = tblPublication.PublicationID) LEFT OUTER JOIN tblStaff ON tblClientPublication.refStaffID = tblStaff.StaffID " & _
strWhere & "ORDER BY tblPublication.strType, tblPublication.strPublicationName, tblClientPublication.refSubPubID, tblClientPublication.strDate;"
'response.write ss
set rst = cn.execute(ss)
totalValue1 = 0
totalValue2 = 0
prevRow=""
while not rst.eof
select case rst("strType")
case "Magazine"
i=0
case "Newspaper"
i=1
case "Radio"
i=2
case "Tv"
i=3
case "Web"
i=4
end select
arrType(i,1) = arrType(i,1) + 1
arrType(5,1) = arrType(5,1) + rst("Vaule7")
arrType(i,2) = arrType(i,2) + rst("Vaule8")
arrType(i,3) = rst("strType")
arrType(5,2) = arrType(5,2) + rst("Vaule8")
arrType(5,3) = arrType(5,3) + intCirculation
CirculationValue = rst("Vaule2")
ReadershipValue = rst("Vaule1")
select case rst("strType")
case "Magazine"
if rst("Vaule2") <> 0 then
CirculationValue = rst("Vaule2")
else
ss = "SELECT dblImpression FROM tblPublication_Magazine WHERE SubPubID = " & rst("refSubPubID")
set rstSub = cn.execute(ss)
CirculationValue = cdbl(rstSub("dblImpression"))
end if
if rst("Vaule1") <> 0 then
intReaders = rst("Vaule1")
else
ss = "SELECT dblImpression FROM tblPublication_Magazine WHERE SubPubID = " & rst("refSubPubID")
set rstSub = cn.execute(ss)
ReadershipValue = cdbl(rstSub("dblImpression"))
end if
if rst("Vaule1") = 0 AND rst("Vaule2") = 0 then
ss = "SELECT dblImpression FROM tblPublication_Magazine WHERE SubPubID = " & rst("refSubPubID")
set rstSub = cn.execute(ss)
CirculationValue = cdbl(rstSub("dblImpression"))
ReadershipValue = cdbl(rstSub("dblImpression"))
end if
case "Newspaper"
if rst("Vaule2") <> 0 then
CirculationValue = rst("Vaule2")
else
ss = "SELECT intCirculation, dblImpression FROM tblPublication_Newspaper WHERE SubPubID = " & rst("refSubPubID")
set rstSub = cn.execute(ss)
CirculationValue = cdbl(rstSub("intCirculation"))
end if
if rst("Vaule1") <> 0 then
ReadershipValue = rst("Vaule1")
else
ss = "SELECT intCirculation, dblImpression FROM tblPublication_Newspaper WHERE SubPubID = " & rst("refSubPubID")
set rstSub = cn.execute(ss)
ReadershipValue = cdbl(rstSub("dblImpression"))
end if
if rst("Vaule1") = 0 AND rst("Vaule2") = 0 then
ss = "SELECT intCirculation, dblImpression FROM tblPublication_Newspaper WHERE SubPubID = " & rst("refSubPubID")
set rstSub = cn.execute(ss)
CirculationValue = cdbl(rstSub("intCirculation"))
ReadershipValue = cdbl(rstSub("dblImpression"))
end if
case "Tv"
if rst("Vaule2") = 0 then
CirculationValue = rst("Vaule1")
ReadershipValue = rst("Vaule1")
end if
if rst("Vaule1") = 0 AND rst("Vaule2") = 0 then
ss = "SELECT dblImpression FROM tblPublication_TV WHERE SubPubID = " & rst("refSubPubID")
set rstSub = cn.execute(ss)
CirculationValue = cdbl(rstSub("dblImpression"))
ReadershipValue = cdbl(rstSub("dblImpression"))
end if
case "Radio"
if rst("Vaule2") = 0 then
CirculationValue = rst("Vaule1")
ReadershipValue = rst("Vaule1")
end if
if rst("Vaule1") = 0 AND rst("Vaule2") = 0 then
ss = "SELECT dblImpression FROM tblPublication_Radio WHERE SubPubID = " & rst("refSubPubID")
set rstSub = cn.execute(ss)
CirculationValue = cdbl(rstSub("dblImpression"))
ReadershipValue = cdbl(rstSub("dblImpression"))
end if
case "Web"
if rst("Vaule2") = 0 then
CirculationValue = rst("Vaule1")
ReadershipValue = rst("Vaule1")
end if
if rst("Vaule1") = 0 AND rst("Vaule2") = 0 then
ss = "SELECT dblImpression FROM tblPublication_Web WHERE SubPubID = " & rst("refSubPubID")
set rstSub = cn.execute(ss)
CirculationValue = cdbl(rstSub("dblImpression"))
ReadershipValue = cdbl(rstSub("dblImpression"))
end if
end select
if rst("refSubPubID") = prevRow THEN
'if prevValue2 = 0 Then
'totalValue2 = totalValue2 + CirculationValue
'end if
if CirculationValue = 0 Then
CirculationValue = ReadershipValue
end if
else
if CirculationValue = 0 Then
CirculationValue = ReadershipValue
end if
arrType(i,0) = arrType(i,0) + ReadershipValue
arrType(5,0) = arrType(5,0) + ReadershipValue
totalValue1 = totalValue1 + ReadershipValue
totalValue2 = totalValue2 + CirculationValue
'response.write " " & CirculationValue
end if
'response.write " "&totalValue2
prevRow = rst("refSubPubID")
prevValue2 = rst("Vaule2")
rst.movenext
wend
'response.write "
|"&totalValue2
x = swapIt(x)
R = R & "
Totals:
" & formatnumber(totalValue2,0) & "
" & formatnumber(totalValue1,0) & "
R " & formatnumber(arrType(5,1),2) & "
R " & formatnumber(arrType(5,2),2) & "
"
R = R & "
Number of Articles:
" & j & "
"
R = R & "
"
'Totals..2
x = swapIt(x)
R = R & "
"
x = swapIt(x)
R = R & "
Summary
"
R = R & "
Media Type
Exposure
PR Value
Number of Articles
"
for i = 0 to 4
if len(arrType(i,3)) > 0 then
x = swapIt(x)
R = R & "
" & arrType(i,3) & "
" & formatnumber(arrType(i,0),0) & "
R " & formatnumber(arrType(i,2),2) & "
" & arrType(i,1) & "
"
end if
next
x = swapIt(x)
R = R & "
" & formatnumber(arrType(5,0),0) & "
R " & formatnumber(arrType(5,2),2) & "
" & j & "
"
R = R & "
"
x = swapIt(x)
R = R & "
"
R = R & "
Notes to the table: Equivalent adspend If the above media coverage had been purchased, the equivalent total advertising spend would have been R" & formatnumber(arrType(5,1),2) & " This is calculated based on the current advertising rates of relevant daily, weekly, trade and electronic media. Circulation and Reader Numbers The totals for the exposure have been calculated by including each publication's circulation and readership numbers only once in the overall total, no matter how many times articles have appeared in a specific publication. "
R = R & " The credibility factor of editorial As editorial is universally regarded as more credible - and therefore more valuable - than advertising, the above figure is multiplied by a conservative factor of 3. This provides an estimate of the PR value of the publicity received. "
R = R & " Reach Sources: Circulation : The Audit Bureau of Circulations Readership : All Media & Product Survey, The SA Advertising Research Foundation Radio Listenership : Radio Audience Measurement Survey, The SA Advertising Research Foundation Television audience : Television Audience Measurement Survey for the date and time of the broadcast. The SA Advertising Research Foundation Average page impressions per day : Top sites, local and international readership, Online Publishers Association"
R = R & " All the above figures subject to audit before publication.
"
'R = Replace(chr(34),chr(39),R)
response.write R
'response.write ""
'Report END
'rst = nothing
'rstSub = nothing
end if
%>