<%
ShopCheckAdmin "shopa_reports.asp"
'***********************************************************
' Version 5.00 Feb 4, 2003
' Sales Reports
' Modified to display summary report table
'*************************************************************
Dim dbtable
Dim fields(10)
Dim CapFields(10)
dim fieldcount
Dim File
Dim ScriptResponder
Dim Dbc
dim Action
dim fromdate
dim todate
dim datedelim
Dim rs
Dim Limit
Dim Month
Dim Year
dim monthnames(12)
'dim database
dim xdecimalpoint
xdecimalpoint=getconfig("xdecimalpoint")
scriptresponder="shopa_formatorder.asp"
mypagesize=GetSess("ReportLimit")
mypage=request("Page")
if mypage<>"" then
sql=GetSess("Sqlquery") ' on recursive calls we stored sql in sessikon variable
fromdate=GetSess("Fromdate")
todate=GetSess("Todate")
WriteReport
else
GetInput
If Action = "" or Serror<>"" Then
DisplayForm
Else
GenerateSQL
WriteReport
end if
End If
'********************************
Sub GetInput
Serror=""
dbtable="Orders"
limit=request("Limit")
if limit="" then
limit=100
end if
SetSess "ReportLimit",limit
mypagesize=limit
action=""
GetActionValues
If action<> "" then
GetDateValues
end if
end sub
'
Sub GetActionValues
action=request("summary")
if action<>"" then
action="SUMMARY"
else
action=request("Details")
if action<>"" then
action="DETAILS"
end if
end if
end Sub
'
Sub GetDateValues
dim datevalue
fromdate=request("fromdate")
todate=request("todate")
If fromdate<>"" then
if todate="" then
todate=date()
end if
ValidateDates
exit sub
end if
' Try month
Month=Request("month")
Year=Request("Year")
If month>"0" then
FormatMonth
exit sub
end if
If Year>"0" then
FormatYear
exit sub
end if
' Try Radio Buttons
datevalue=request("DateSelect")
datevalue=ucase(datevalue)
'debugwrite "datevalue=" & datevalue
if datevalue="" then
Serror=getlang("langReportDate")
exit sub
end if
If datevalue="TODAY" then
fromdate=date()
todate=date()
exit sub
end if
If datevalue="YESTERDAY" then
fromdate=dateadd("d",-1, date())
todate=fromdate
exit sub
end if
end sub
Sub FormatMonth
If lcase(getconfig("xenvironment"))="chillisoft" then
formatmonthchilli
exit sub
end if
month=cint(month)
if Year="0" then
Year=Datepart("yyyy",date())
else
Year=clng(year)
end if
fromdate= month & "/" & Year
month=month+1
if month = 13 then
month=1
Year=Year+1
end if
Todate= month & "/" & Year
fromdate=cdate(fromdate)
todate=cdate(todate)
todate = dateadd("d",-1, todate)
end sub
'
Sub formatYear
If lcase(getconfig("xenvironment"))="chillisoft" then
formatyearchilli
exit sub
end if
Year=clng(year)
month=1
fromdate= month & "/" & Year
Year=Year+1
Todate= month & "/" & Year
fromdate=cdate(fromdate)
todate=cdate(todate)
todate=dateadd("d",-1,todate)
end sub
'
Sub ValidateDates
If not Isdate(fromdate) then
serror=getlang("langReportInvalidDate") & fromdate
end if
If not Isdate(todate) then
Serror="
" & getlang("langReportInvalidDate") & todate
end if
end sub
Sub GenerateSQL
dim datesql
Dim newsql
mypage=1 ' first time through
Sql="select * from orders where "
datesql = " odate>= " & DateDelimit(fromdate)
datesql = datesql & " AND odate<= " & datedelimit(todate)
'Added for summary reports to make cycling through records easier
If Action="SUMMARY" then
datesql=datesql & " order by odate"
end if
sql = sql & datesql
newSql= replace (SQL, ".", "/")
Sql=NewSql
SetSess "Fromdate",fromdate
SetSess "todate",Todate
if Getconfig("xdebug")="Yes" then
debugwrite sql
end if
end sub
'
Sub WriteReport
' Generate table
AdminPageHeader
GetMonthnames monthnames
OpenOrderDB dbc
ShopOpenRecordSet SQL,RS, mypagesize, mypage
Response.write largeInfofont & getlang("langReport01") & fromdate & " to " & todate & Largeinfoend & "
" & reportinfofontend
if rs.eof then
Response.write "
"
shopwriteerror getlang("langReportNoSales")
else
If Action="SUMMARY" then
ProduceSummary
else
ProduceDetail
end if
end if
AdMinPageTrailer
end sub
'
Sub ProduceSummary
'Based on original summary report and modified
'to calculate monthly reports including
'number of orders and monthly sales total
dim count
dim total
dim nopayment
dim monthtotal,monthorders
dim curyear,curmonth
dim tmpmonth
dim bigdate,littledate
if datepart("m",fromdate)=datepart("m",todate) and datepart("yyyy",fromdate)=datepart("yyyy",todate) then
bigdate="m"
littledate="d"
displaysummaryheader "Month", "Day"
else
bigdate="yyyy"
littledate="m"
displaysummaryheader "Year", "Month"
end if
count=0
total=0
nopayment=0
While Not rs.EOF
tmpmonth=datepart(littledate,rs("odate"))
curyear=datepart(bigdate,rs("odate"))
curmonth=datepart(littledate,rs("odate"))
monthtotal=0
monthorders=0
while tmpmonth=curmonth
count=count+1
monthorders=monthorders+1
if not isnull(rs("orderamount")) then
total=total+rs("orderamount")
monthtotal=monthtotal+rs("orderamount")
end if
rs.movenext
if Not rs.eof then
tmpmonth=datepart(littledate,rs("odate"))
else
tmpmonth=999 'end loop
end if
wend
displaysummaryrow curyear,curmonth,monthorders,monthtotal
wend
formattableend
rs.close
set rs=nothing
shopclosedatabase dbc
response.write "" & largeinfofont
Response.write "
" & getlang("langReportTotalsales") & shopformatcurrency(total,xdecimalpoint)
Response.write "
" & getlang("langReportNumSales") & count
response.write largeinfoend & "
"
end sub
'
Sub ProduceDetail
dim count
dim total
dim nopayment
'modification
dim taxes,shippingcost
count=0
total=0
nopayment=0
FormatTableStart
limit=clng(mypagesize)
While Not rs.EOF and count" & largeinfofont
Response.write "
" & "Total Taxes: " & shopformatcurrency(taxes,xdecimalpoint)
Response.write "
" & "Total Shipping: " & shopformatcurrency(shippingcost,xdecimalpoint)
Response.write "
" & getlang("langReportTotalSales") & shopformatcurrency(total,xdecimalpoint)
Response.write "
" & getlang("langReportNumSales") & count
response.write largeinfoend & ""
end sub
Sub FormatRow
dim i
dim fieldvalue
response.write ReportDetailRow
for i = 0 to fieldcount
fieldvalue=rs(fields(i))
if fields(i)="ORDERAMOUNT" then
fieldvalue=shopformatcurrency(fieldvalue,Getconfig("xdecimalpoint"))
end if
If fields(i)="ORDERID" then
my_link=scriptresponder & "?oid=" & rs("orderid") & "&idfield=" & "orderid"
fieldvalue="" & rs("orderid") & ""
end if
response.write ReportDetailColumn & fieldvalue & ReportDetailColumnEnd
next
response.write ReportRowEnd
end sub
'
'
Sub Displayform
AdminPageHeader
if sError<> "" then
shopwriteerror sError
Serror=""
end if
Response.write ""
Response.write largeinfofont & getlang("langReport03") & largeinfoend
response.write "
"
%>
"
AdminPageTrailer
end sub
Sub FormattableStart
dim i
Fields(0)="ORDERID"
Fields(1)="ORDERAMOUNT"
Fields(2)="Odate"
Fields(3)="ofirstname"
Fields(4)="olastname"
Fields(5)="oaddress"
Fields(6)="oCity"
Fields(7)="ostate"
Fields(8)="ocountry"
fieldcount=8
CapFields(0)=getlang("langProductOrderNumber")
CapFields(1)=getlang("langProductTotal")
CapFields(2)=getlang("langStatusDate")
CapFields(3)=getlang("langCustFirstName")
CapFields(4)=getlang("langCustLastName")
CapFields(5)=getlang("langCustAddress")
CapFields(6)=getlang("langCustCity")
capFields(7)=getlang("langCustState")
capFields(8)=getlang("langCustCountry")
response.write REportTableDEf
response.write ReportHeadRow
for i = 0 to fieldcount
response.write ReportHeadColumn & capfields(i) & ReportHeadColumnEnd
next
response.write ReportRowEnd
end sub
Sub FormatTableEnd
response.write ReportTableEnd
end sub
sub displaysummaryheader(bigdatename,littledatename)
response.write REportTableDEf
response.write ReportHeadRow
response.write ReportHeadColumn & bigdatename & ReportHeadColumnEnd
response.write ReportHeadColumn & littledatename & ReportHeadColumnEnd
response.write ReportHeadColumn & "Orders" & ReportHeadColumnEnd
response.write ReportHeadColumn & "Total" & ReportHeadColumnEnd
response.write ReportRowEnd
end sub
sub displaysummaryrow(y,m,numorders,monthtotal)
dim monthenum,monthname
'if request("month")<>"0" then
if datepart("m",fromdate)=datepart("m",todate) and datepart("yyyy",fromdate)=datepart("yyyy",todate) then
monthname=m
else
monthname=monthnames(m-1)
end if
response.write ReportDetailRow
response.write ReportDetailColumn & y & ReportDetailColumnEnd
response.write ReportDetailColumn & monthname & ReportDetailColumnEnd
response.write ReportDetailColumn & numorders & ReportDetailColumnEnd
response.write ReportDetailColumn & shopformatcurrency(monthtotal,Getconfig("xdecimalpoint")) & ReportDetailColumnEnd
response.write ReportRowEnd
end sub
Sub Getmonthnames (monthnames)
dim i , name
If getconfig("xlcid")<>"" then
session.lcid=getconfig("xlcid")
end if
for i=1 to 12
Monthindex=I
name=monthname(monthindex)
monthnames(i-1)=name
next
end sub
%>