<% response.buffer=True %>
<!--#include file="inc-adovbs.asp"-->
<!--#include file="DataStore.asp"-->
<!--#include file="inc-nocache.asp"-->
<!--#include file="func-conversions.asp"-->
<!--#include file="error-catcher.asp"-->
<!--#include file="func-getvars.asp"-->
<%
if userrights < 5 then
response.redirect("default.asp")
end if
sort=wclng(request("sort"))
delete=nonull(request.form("delete"))
' Set up the database connection
Set ddConn=Server.CreateObject("ADODB.Connection")
ddConn.Open strConnect
Set ddRs=Server.CreateObject("ADODB.RecordSet")
startdate=wcdate(request("sd"))
enddate=wcdate(request("ed"))
if startdate="" then
startdate="09/01/2001"
end if
with response
.write "<html><head><title>Report: Reordering Statistics by Month</title></head>"
.write "<body bgcolor=white text=black link=#0000ff vlink=#0000ff alink=#ff0000>"
.write "<a href=default.asp>Main Menu</a><br><br>"
.write "<form action=report-reordersbymonth.asp>"
.write "<table border=0 cellspacing=0>"
.write "<tr><td align=right>Start date:</td><td><input type=text size=20 name=sd value=""" & htmlenc(startdate) & """></td></tr>"
.write "<tr><td align=right>End date:</td><td><input type=text size=20 name=ed value=""" & htmlenc(enddate) & """></td></tr>"
.write "<tr><td align=center colspan=2><input type=submit value=""Generate Report""></td></tr>"
.write "</table>"
.write "</form>"
.write "<hr noshade><font size=+2>MJ version:</font>"
.write "<table width=100% border=1 cellspacing=0>"
.write "<tr>"
.write "<th>Month</th>"
.write "<th>Orders</th>"
.write "<th>Sales</th>"
.write "<th># from Multiple Order Customers</th>"
.write "<th>As a % of Orders</th>"
.write "<th>Avg Order</th>"
.write "<th># from Single Order Customers</th>"
.write "<th>As a % of Orders</th>"
.write "<th>Avg Order</th>"
.write "</tr>"
ddsql = "select min(receipttime) as receipttime from orders with (NOLOCK) where clientid !=23 and receipttime >= " & sqltext(startdate)
if enddate > "" then
ddsql = ddsql & " and receipttime <= '" & enddate & "'"
end if
set ddRs=ddConn.execute(ddsql, , adCmdText)
if not ddRs.eof then
lastmonth=formatdate(ddRs("receipttime"), "%Y-%M")
else
lastmonth="2001-09"
end if
orders=0
torders=0
sales=0
tsales=0
reorders=0
treorders=0
treordersum=0
neworders=0
tneworders=0
tnewordersum=0
ddsql="select subtotal, receipttime, (select count(o2.orderid) from orders o2 with (NOLOCK) where o2.clientid=orders.clientid and o2.receipttime > '9/1/01') as custorders from orders with (NOLOCK) where receipttime >= " & sqltext(startdate)
if enddate > "" then
ddsql = ddsql & " and receipttime < " & sqltext(enddate)
end if
ddsql = ddsql & " and clientid != 23 order by receipttime"
set ddRs=ddConn.execute(ddsql, , adCmdText)
do while not ddRs.eof
thismonth=formatdate(ddRs("receipttime"), "%Y-%M")
if thismonth <> lastmonth then
' A month's gone by. Roll out the totals...
if orders=0 then
preorders=0
pneworders=0
else
preorders=reorders / orders
pneworders=neworders / orders
end if
if reorders=0 then
avgreorders=0
else
avgreorders=reordersum/reorders
end if
if neworders=0 then
avgneworders=0
else
avgneworders=newordersum/neworders
end if
.write("<tr>")
.write("<td align=center>" & lastmonth & "</td>")
.write("<td align=center>" & orders & "</td>")
.write("<td align=right>" & formatcurrency(sales) & "</td>")
.write("<td align=right>" & reorders & "</td>")
.write("<td align=right>" & formatpercent(preorders, 0) & "</td>")
.write("<td align=right>" & formatcurrency(avgreorders) & "</td>")
.write("<td align=right>" & neworders & "</td>")
.write("<td align=right>" & formatpercent(pneworders, 0) & "</td>")
.write("<td align=right>" & formatcurrency(avgneworders) & "</td>")
.write("</tr>")
torders=torders+orders
tsales=tsales+sales
treorders=treorders+reorders
tneworders=tneworders+neworders
treordersum=treordersum+reordersum
tnewordersum=tnewordersum+newordersum
orders=0
sales=0
reorders=0
neworders=0
reordersum=0
newordersum=0
lastmonth=thismonth
end if
orders=orders + 1
subtotal=wcdbl(ddRs("subtotal"))
sales=sales + subtotal
if wclng(ddRs("custorders")) > 1 then
reorders = reorders + 1
reordersum = reordersum + subtotal
else
neworders = neworders + 1
newordersum = newordersum + subtotal
end if
ddRs.movenext
loop
if orders > 0 then
preorders=reorders / orders
pneworders=neworders / orders
if reorders=0 then
avgreorders=0
else
avgreorders=reordersum/reorders
end if
if neworders=0 then
avgneworders=0
else
avgneworders=newordersum/neworders
end if
.write("<tr>")
.write("<td align=center>" & lastmonth & "</td>")
.write("<td align=center>" & orders & "</td>")
.write("<td align=right>" & formatcurrency(sales) & "</td>")
.write("<td align=right>" & reorders & "</td>")
.write("<td align=right>" & formatpercent(preorders, 0) & "</td>")
.write("<td align=right>" & formatcurrency(avgreorders) & "</td>")
.write("<td align=right>" & neworders & "</td>")
.write("<td align=right>" & formatpercent(pneworders, 0) & "</td>")
.write("<td align=right>" & formatcurrency(avgneworders) & "</td>")
.write("</tr>")
torders=torders+orders
tsales=tsales+sales
treorders=treorders+reorders
tneworders=tneworders+neworders
treordersum=treordersum+reordersum
tnewordersum=tnewordersum+newordersum
orders=0
sales=0
reorders=0
neworders=0
reordersum=0
newordersum=0
end if
if torders > 0 then
preorders=treorders/torders
pneworders=tneworders/torders
if treorders=0 then
avgreorders=0
else
avgreorders=treordersum/treorders
end if
if tneworders=0 then
avgneworders=0
else
avgneworders=tnewordersum/tneworders
end if
.write("<tr>")
.write("<td align=center>Totals</td>")
.write("<td align=center>" & torders & "</td>")
.write("<td align=right>" & formatcurrency(tsales) & "</td>")
.write("<td align=right>" & treorders & "</td>")
.write("<td align=right>" & formatpercent(preorders, 0) & "</td>")
.write("<td align=right>" & formatcurrency(avgreorders) & "</td>")
.write("<td align=right>" & tneworders & "</td>")
.write("<td align=right>" & formatpercent(pneworders, 0) & "</td>")
.write("<td align=right>" & formatcurrency(avgneworders) & "</td>")
.write("</tr>")
end if
.write("</table>")
.write "<hr noshade><font size=+2>RP/RG version:</font>"
.write "<table width=100% border=1 cellspacing=0>"
.write "<tr>"
.write "<th>Month</th>"
.write "<th>Orders</th>"
.write "<th>Sales</th>"
.write "<th># from Multiple Order Customers</th>"
.write "<th>As a % of Orders</th>"
.write "<th>Avg Order</th>"
.write "<th># from Single Order Customers</th>"
.write "<th>As a % of Orders</th>"
.write "<th>Avg Order</th>"
.write "</tr>"
ddsql = "select min(receipttime) as receipttime from orders with (NOLOCK) where clientid !=23 and receipttime >= " & sqltext(startdate)
if enddate > "" then
ddsql = ddsql & " and receipttime <= '" & enddate & "'"
end if
set ddRs=ddConn.execute(ddsql, , adCmdText)
if not ddRs.eof then
lastmonth=formatdate(ddRs("receipttime"), "%Y-%M")
else
lastmonth="2001-09"
end if
orders=0
torders=0
sales=0
tsales=0
reorders=0
treorders=0
treordersum=0
neworders=0
tneworders=0
tnewordersum=0
ddsql="select subtotal, receipttime, (select count(o2.orderid) from orders o2 with (NOLOCK) where o2.clientid=orders.clientid and o2.receipttime > '9/1/01' and o2.receipttime < orders.receipttime) as custorders from orders with (NOLOCK) where receipttime >= " & sqltext(startdate)
if enddate > "" then
ddsql = ddsql & " and receipttime < " & sqltext(enddate)
end if
ddsql = ddsql & " and clientid != 23 order by receipttime"
set ddRs=ddConn.execute(ddsql, , adCmdText)
do while not ddRs.eof
thismonth=formatdate(ddRs("receipttime"), "%Y-%M")
if thismonth <> lastmonth then
' A month's gone by. Roll out the totals...
if orders=0 then
preorders=0
pneworders=0
else
preorders=reorders / orders
pneworders=neworders / orders
end if
if reorders=0 then
avgreorders=0
else
avgreorders=reordersum/reorders
end if
if neworders=0 then
avgneworders=0
else
avgneworders=newordersum/neworders
end if
.write("<tr>")
.write("<td align=center>" & lastmonth & "</td>")
.write("<td align=center>" & orders & "</td>")
.write("<td align=right>" & formatcurrency(sales) & "</td>")
.write("<td align=right>" & reorders & "</td>")
.write("<td align=right>" & formatpercent(preorders, 0) & "</td>")
.write("<td align=right>" & formatcurrency(avgreorders) & "</td>")
.write("<td align=right>" & neworders & "</td>")
.write("<td align=right>" & formatpercent(pneworders, 0) & "</td>")
.write("<td align=right>" & formatcurrency(avgneworders) & "</td>")
.write("</tr>")
torders=torders+orders
tsales=tsales+sales
treorders=treorders+reorders
tneworders=tneworders+neworders
treordersum=treordersum+reordersum
tnewordersum=tnewordersum+newordersum
orders=0
sales=0
reorders=0
neworders=0
reordersum=0
newordersum=0
lastmonth=thismonth
end if
orders=orders + 1
subtotal=wcdbl(ddRs("subtotal"))
sales=sales + subtotal
if wclng(ddRs("custorders")) > 0 then
reorders = reorders + 1
reordersum = reordersum + subtotal
else
neworders = neworders + 1
newordersum = newordersum + subtotal
end if
ddRs.movenext
loop
if orders > 0 then
preorders=reorders / orders
pneworders=neworders / orders
if reorders=0 then
avgreorders=0
else
avgreorders=reordersum/reorders
end if
if neworders=0 then
avgneworders=0
else
avgneworders=newordersum/neworders
end if
.write("<tr>")
.write("<td align=center>" & lastmonth & "</td>")
.write("<td align=center>" & orders & "</td>")
.write("<td align=right>" & formatcurrency(sales) & "</td>")
.write("<td align=right>" & reorders & "</td>")
.write("<td align=right>" & formatpercent(preorders, 0) & "</td>")
.write("<td align=right>" & formatcurrency(avgreorders) & "</td>")
.write("<td align=right>" & neworders & "</td>")
.write("<td align=right>" & formatpercent(pneworders, 0) & "</td>")
.write("<td align=right>" & formatcurrency(avgneworders) & "</td>")
.write("</tr>")
torders=torders+orders
tsales=tsales+sales
treorders=treorders+reorders
tneworders=tneworders+neworders
treordersum=treordersum+reordersum
tnewordersum=tnewordersum+newordersum
orders=0
sales=0
reorders=0
neworders=0
reordersum=0
newordersum=0
end if
if torders > 0 then
preorders=treorders/torders
pneworders=tneworders/torders
if treorders=0 then
avgreorders=0
else
avgreorders=treordersum/treorders
end if
if tneworders=0 then
avgneworders=0
else
avgneworders=tnewordersum/tneworders
end if
.write("<tr>")
.write("<td align=center>Totals</td>")
.write("<td align=center>" & torders & "</td>")
.write("<td align=right>" & formatcurrency(tsales) & "</td>")
.write("<td align=right>" & treorders & "</td>")
.write("<td align=right>" & formatpercent(preorders, 0) & "</td>")
.write("<td align=right>" & formatcurrency(avgreorders) & "</td>")
.write("<td align=right>" & tneworders & "</td>")
.write("<td align=right>" & formatpercent(pneworders, 0) & "</td>")
.write("<td align=right>" & formatcurrency(avgneworders) & "</td>")
.write("</tr>")
end if
.write("</table>")
.write("</body></html>")
end with
set ddRs=nothing
if isobject(ddConn) then
if ddConn.state=adStateOpen then
ddConn.close
end if
end if
set ddConn=nothing
%>
murtaza1234 -2 Newbie Poster
kvprajapati commented: Don't ask trivial questions. -2
Be a part of the DaniWeb community
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.