Imports Mediatheek.Data
Imports Mediatheek.DataAcces
Imports Microsoft.Office.Interop

Public Class frmStatestiek2
    Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "

    Public Sub New()
        MyBase.New()

        'This call is required by the Windows Form Designer.
        InitializeComponent()

        'Add any initialization after the InitializeComponent() call

    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    Friend WithEvents picGrafiek As System.Windows.Forms.PictureBox
    Friend WithEvents PictureBox1 As System.Windows.Forms.PictureBox
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Dim resources As System.Resources.ResourceManager = New System.Resources.ResourceManager(GetType(frmStatestiek2))
        Me.picGrafiek = New System.Windows.Forms.PictureBox
        Me.PictureBox1 = New System.Windows.Forms.PictureBox
        Me.SuspendLayout()
        '
        'picGrafiek
        '
        Me.picGrafiek.Location = New System.Drawing.Point(0, 72)
        Me.picGrafiek.Name = "picGrafiek"
        Me.picGrafiek.Size = New System.Drawing.Size(792, 496)
        Me.picGrafiek.TabIndex = 3
        Me.picGrafiek.TabStop = False
        '
        'PictureBox1
        '
        Me.PictureBox1.Image = CType(resources.GetObject("PictureBox1.Image"), System.Drawing.Image)
        Me.PictureBox1.Location = New System.Drawing.Point(0, 0)
        Me.PictureBox1.Name = "PictureBox1"
        Me.PictureBox1.Size = New System.Drawing.Size(792, 70)
        Me.PictureBox1.TabIndex = 2
        Me.PictureBox1.TabStop = False
        '
        'frmStatestiek2
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(794, 591)
        Me.Controls.Add(Me.picGrafiek)
        Me.Controls.Add(Me.PictureBox1)
        Me.FormBorderStyle = System.Windows.Forms.FormBorderStyle.FixedSingle
        Me.Icon = CType(resources.GetObject("$this.Icon"), System.Drawing.Icon)
        Me.MaximizeBox = False
        Me.Name = "frmStatestiek2"
        Me.Text = "Statestiek"
        Me.ResumeLayout(False)

    End Sub

#End Region

    Private moExcellApp As Excel.Application


    Private Sub frmStatestiek2_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
        System.Threading.Thread.CurrentThread.CurrentCulture = New System.Globalization.CultureInfo("en-US")

        'excel opstarten
        moExcellApp = New Excel.Application
        moExcellApp.Visible = False

        'een nieuw excel werkboek openen
        Dim oWBoek As Excel.Workbook
        oWBoek = moExcellApp.Workbooks.Add

        'een werkblad overlopen
        Dim oWBlad As Excel.Worksheet

        'alle werkbladen verwijderen behalve het eerste
        Dim iLaatste As Integer
        iLaatste = oWBoek.Worksheets.Count
        Do While iLaatste > 1
            oWBlad = oWBoek.Worksheets.Item(iLaatste)
            oWBlad.Delete()
            iLaatste = oWBoek.Worksheets.Count
        Loop

        'de naam van het eerste werkblad wijzigen
        oWBlad = oWBoek.Worksheets.Item(1)
        oWBlad.Name = "Liedjes per Genre"

        'cellen opvullen
        Dim oRange As Excel.Range
        Dim iActRij As Integer = 1

        For Each oGenre As clsGenre In clsAlbumDA.GetAlbumsPerGenre()
            oRange = oWBlad.Cells
            oRange = oRange.Item(iActRij, 1)
            oRange.Value = clsGenreDA.GetGenreByGenreID(oGenre.GenreNaam).GenreNaam
            iActRij += 1
        Next

        Dim iAantalRij As Integer = 1
        For Each oAantal As clsGenre In clsAlbumDA.GetAlbumsPerGenre()
            oRange = oWBlad.Cells
            oRange = oRange.Item(iAantalRij, 2)
            oRange.Value = oAantal.GenreID
            iAantalRij += 1
        Next

        'opslaan in de bin-map met de naam activiteiten.xls
        moExcellApp.DisplayAlerts = False
        oWBoek.SaveAs(Application.StartupPath & "\files\LiedjesPerGenre.xls")
        moExcellApp.DisplayAlerts = True

        'grafiek
        Dim iLaatsteRij As Integer = 1
        Dim Range As Excel.Range
        Range = oWBlad.Range("A1")
        Do Until Range.Value Is Nothing
            Range = Range.Offset(1, 0)
            iLaatsteRij += 1
        Loop
        iLaatsteRij -= 1

        oWBoek.Charts.Add()

        oWBoek.ActiveChart.ChartType = Excel.XlChartType.xlColumnClustered
        oWBoek.ActiveChart.SetSourceData(Source:=oWBlad.Range("A1:B" & iLaatsteRij), PlotBy:=Excel.XlRowCol.xlColumns)
        oWBoek.ActiveChart.Location(Where:=Excel.XlChartLocation.xlLocationAsObject, Name:="Liedjes per Genre")
        With oWBoek.ActiveChart
            .HasTitle = True
            .ChartTitle.Characters.Text = "Aantal Liedjes per Genre"
            .Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary).MajorUnit = 1
            .Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary).HasTitle = True
            .Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary).AxisTitle.Characters.Text = "Genre"
            .Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary).HasTitle = True
            .Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary).AxisTitle.Characters.Text = "aantal"
        End With
        oWBoek.ActiveChart.HasLegend = False
        oWBoek.ActiveChart.HasDataTable = False

        'grafiek naar een gif file exporteren
        Dim sPath As String = Application.StartupPath & "\images\LiedjesPerGenre.gif"
        If Dir$(sPath) = "" Then
            oWBoek.ActiveChart.Export(Application.StartupPath & "\images\LiedjesPerGenre.gif")
        End If


        'de excel file sluiten zonder op te slaan
        oWBoek.Close(False)
        'de fig file in de pictureBox laden
        If Not picGrafiek.Image Is Nothing Then
            picGrafiek.Image.Dispose()
            picGrafiek.Image = Nothing
        End If
        picGrafiek.Image = Image.FromFile(Application.StartupPath & "\images\LiedjesPerGenre.gif")

        'excel afsluiten
        moExcellApp.Quit()
        Process.GetProcessesByName("EXCEL")(0).Kill()
    End Sub
End Class
