'#Reference {00020813-0000-0000-C000-000000000046}#1.2#0#C:\Program Files\MSOffice\Office\EXCEL8.OLB#Microsoft Excel 8.0 Object Library
Sub Main

    Dim App As Object
    Set App = CreateObject("TimeLog.Application")
	
	Dim OriginalSQL As Variant
	Dim WhereCommand As String
	Dim NewSQL As String

	OriginalSQL = App.GetSQL()
	OriginalSQL = LCase(OriginalSQL)
	WhereStart = InStr(OriginalSQL, "where")
	OrderStart = InStr(OriginalSQL, "order by")
	
	If (WhereStart = 0) Then
		WhereCommand=" "
	ElseIf (OrderStart=0) Then
		WhereCommand = Mid$(OriginalSQL, WhereStart)
	Else
		WhereCommand = Mid$(OriginalSQL, WhereStart, OrderStart - WhereStart)
	End If


	NewSQL = "select billable, duration, key from timerecords " + WhereCommand
	'MsgBox(NewSQL)
	App.SetSQL(NewSQL)

	NumRecords = App.GetNumRecords()

	Dim Billable As Variant
	Dim BillableDuration As Variant
	Dim Duration As Variant
	Dim TotalDuration As Variant

	Dim ResultCode As Long

	NumBillable=0
	Duration=0
	TotalDuration=0

	For i=1 To NumRecords
		If i=1 Then
			App.MoveFirst()
		Else 
			App.MoveNext()
		End If
		
		Billable = App.GetFieldValue(0, ResultCode)	' Field 0 = Billable
		Duration = App.GetFieldValue(1, ResultCode)
		TotalDuration = TotalDuration + Duration
		If Billable Then 
			NumBillable=NumBillable+1 
			BillableDuration = BillableDuration + Duration
		End If

	Next i
	
	App.SetSQL(OriginalSQL)
	App.RefreshDisplay()

	'MsgBox(Str$(BillableDuration/3600) + " Hours were billable, out of a total of " + Str$(TotalDuration/3600) + " Hours.  Percent Billable Hours: " + Str$(BillableDuration*100/TotalDuration))
	'MsgBox(NumBillable/NumRecords)

	Dim ExcelApp As Object
    Set ExcelApp = CreateObject("Excel.Application")
	ExcelApp.Visible=True


	Set xl = CreateObject("Excel.Sheet")
	xl.Application.Workbooks.Add

	xl.Application.Range("A1").Select
	xl.Application.ActiveCell.FormulaR1C1 = "Billable Hours"
	xl.Application.Range("A2").Select
	xl.Application.ActiveCell.FormulaR1C1 = "Non-Billable Hours"
	xl.Application.Range("B1").Select
	xl.Application.ActiveCell.FormulaR1C1 = BillableDuration/3600.0
	xl.Application.Range("B2").Select
	xl.Application.ActiveCell.FormulaR1C1 = (TotalDuration-BillableDuration)/3600.0

    xl.Application.Range("A1:B2").Select

    xl.Application.Charts.Add
    xl.Application.ActiveChart.ChartType = xlPieExploded
    'xl.Application.ActiveChart.SetSourceData Source:=ActiveSheet.Range("A1:B2"), PlotBy:= xlColumns
    'xl.Application.ActiveChart.SeriesCollection(1).Name = "=""Billable vs. Non-Billable Hours"""
    xl.Application.ActiveChart.Location Where:=xlLocationAsNewSheet
    With xl.Application.ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "Billable vs. Non-Billable Hours"
    End With


End Sub