<% 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
%>
Ketsuekiame commented: Words cannot describe my disappointment in some people +0

Recommended Answers

All 2 Replies

How about you don't take jobs you can't do?

Put some effort in, then come back with a specific problem. Or, for a small fee, I'll do it for you. =)

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.