<?xml version="1.0"?>
<component>

<?component error="true" debug="true"?>

<registration
	description="totalsales"
	progid="totalsales.WSC"
	version="1.00"
	classid="{86608635-7b55-4b45-8490-b550dfc38111}"
>
</registration>

<public>
	<property name="DBConn">
		<put/>
	</property>
	<method name="GetTotalSales">
		<PARAMETER name="catid"/>
	</method>
	<method name="GetTopSales">
		<PARAMETER name="top"/>
		<PARAMETER name="sortby"/>
	</method>
	
</public>

<implements type="Behavior" id="Behavior"/>

<script language="VBScript">
<![CDATA[

dim DBConn


function put_DBConn(newValue)
	DBConn = newValue
end function

function GetTotalSales(catid)
	
	Dim cnn
	Dim rst
	Dim strSQL
	
	Set cnn = CreateObject("ADODB.Connection")
	cnn.Open  DBConn
	
	strSQL = "SELECT Sum(ORDERS.UnitPrice*ORDERS.Qty) AS SumOfUnitPrice "
	strSQL = strSQL & "FROM ORDERS INNER JOIN "
	strSQL = strSQL & "(Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID) "
	strSQL = strSQL & "ON ORDERS.Product = Products.ProductName "
	strSQL = strSQL & "GROUP BY Categories.CategoryID "
	strSQL = strSQL & "HAVING (((Categories.CategoryID)=" & catid & "));"
	
	Set rst = CreateObject("ADODB.Recordset")
	rst.Open strSQL, cnn

	GetTotalSales = rst.Fields("SumOfUnitPrice")
	
	rst.Close
	Set rst = Nothing
	Set cnn = Nothing

end function

function GetTopSales(top, sortby)
	
	Dim cnn
	Dim rst
	Dim strSQL
	Dim strTemp
	
	Set cnn = CreateObject("ADODB.Connection")
	cnn.Open  DBConn
	
	strSQL = "SELECT TOP " & top & " Product, SUM(Qty) AS SumQty, SUM([Qty]*[UnitPrice]) AS Total "
	strSQL = strSQL  & "FROM ORDERS "
	strSQL = strSQL  & "GROUP BY Product "
	strSQL = strSQL  & "ORDER BY " & sortby & " DESC"
	
	Set rst = CreateObject("ADODB.Recordset")
	rst.Open strSQL, cnn

	If Not (rst.EOF and rst.BOF) Then
		rst.MoveFirst
		strTemp = "<table border='0' cellspacing='5'>" & Chr(13)
		strTemp = strTemp & "<tr class='pageintro'><td>Product</td><td>Qty Sold</td><td>Value</td></tr>" & Chr(13)
		Do Until rst.EOF
			strTemp = strTemp & "<tr><td>" & rst.Fields("Product").Value & "</td><td align='center'>" & _
				rst.Fields("SumQty").Value & "</td><td>" & _
				FormatNumber(rst.Fields("Total").Value, 2) & "</td></tr>" & Chr(13)
			rst.MoveNext  
		Loop
		strTemp = strTemp & "</table>"
	End If
	rst.Close

	GetTopSales = strTemp

	Set rst = Nothing
	Set cnn = Nothing

end function


]]>
</script>

</component>

