Imports Root.Reports
Imports System
Imports System.IO
Imports System.Data.OleDb
Imports System.Drawing

' Creation date: 26.09.2003
' Checked: 30.10.2004
' Author: Thomas Gemperle (gth@root.ch), Otto Mayer (mot@root.ch)
' Version: 1.03

' Report.NET copyright 2002-2004 root-software ag, Bürglen Switzerland - Otto Mayer, Stefan Spirig, all rights reserved
' This library is free software; you can redistribute it and/or modify it under the terms of the GNU Lesser General Public License

' as published by the Free Software Foundation, version 2.1 of the License.

' This library is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of

' MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more details. You

' should have received a copy of the GNU Lesser General Public License along with this library; if not, write to the Free Software

' Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA www.opensource.org/licenses/lgpl-license.html

' Table Layout Manager and ADO.NET Sample (VB Version)
Module AdoSampleVB
  ' Starts this sample.
  Sub Main()
    RT.ViewPDF(New AdoSample, "AdoSampleVB.pdf")
  End Sub
End Module

' ADO.NET Sample
' This sample creates a table with data from an ADO.NET data source (SQL-Server, sample database "Northwind").
' The table layout manager automatically builds the grid lines, the header of the table and the page breaks, etc.
' The event handler Tlm_NewContainer binds each table container to a new page.
' The first page has a caption. The following pages have no caption and therefore the table can be made higher.
Class AdoSample
  Inherits Report
  Private sConnectionString As String = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog=Northwind;Data Source=(local)"
  Private fd As FontDef
  Private rPosLeft As Double = 20
  Private rPosRight As Double = 195
  Private rPosTop As Double = 24
  Private rPosBottom As Double = 278

  ' Creates this report.
  Protected Overrides Sub Create()
    fd = New FontDef(Me, FontDef.StandardFont.Helvetica)
    Dim fp As FontProp = New FontPropMM(fd, 1.9)  ' standard font
    Dim fp_header As FontProp = New FontPropMM(fd, 1.9)  ' font of the table header
    fp_header.bBold = True

    Dim stream_Phone As Stream = Me.GetType().Assembly.GetManifestResourceStream("Phone.jpg")
    Dim random As Random = New Random(6)

    ' create table
    Dim tlm As TableLayoutManager
    Try
      tlm = New TableLayoutManager(fp_header)
      tlm.rContainerHeightMM = rPosBottom - rPosTop  ' set height of table
      tlm._headerCellDef.rAlignV = RepObj.rAlignCenter  ' set vertical alignment of all header cells
      tlm._cellDef.pp_LineBottom = New PenProp(Me, 0.05, Color.LightGray)
      AddHandler tlm.eNewContainer, AddressOf Tlm_NewContainer

      ' define columns
      Dim col As TableLayoutManager.Column
      col = New TableLayoutManager.ColumnMM(tlm, "ID", 13)

      col = New TableLayoutManager.ColumnMM(tlm, "Company Name", 40)
      col._cellDef.textMode = TableLayoutManager.TextMode.MultiLine

      col = New TableLayoutManager.ColumnMM(tlm, "Address", 36)

      col = New TableLayoutManager.ColumnMM(tlm, "City", 22)

      col = New TableLayoutManager.ColumnMM(tlm, "Postal Code", 16)

      col = New TableLayoutManager.ColumnMM(tlm, "Country", 18)

      Dim col_Phone As TableLayoutManager.Column = New TableLayoutManager.ColumnMM(tlm, "Phone", rPosRight - rPosLeft - tlm.rWidthMM)
      col_Phone.fp_Header = New FontPropMM(fd, 1.9, Color.Brown)
      col_Phone.headerCellDef.rAlignH = RepObj.rAlignRight
      col_Phone._cellDef.rAlignH = RepObj.rAlignRight
      Dim bp_Phone As BrushProp = New BrushProp(Me, Color.FromArgb(255, 255, 200))
      col_Phone._cellDef.bp_Back = bp_Phone

      ' open database
      Dim oleDbConnection As OleDbConnection
      Try
        oleDbConnection = New OleDbConnection(sConnectionString)
        oleDbConnection.Open()
        Dim oleDbCommand As OleDbCommand = New OleDbCommand("SELECT CustomerID, CompanyName, Address, City, PostalCode, Country, Phone " & _
           "FROM Customers ORDER BY CompanyName", oleDbConnection)
        ' read rows
        Dim oddr As OleDbDataReader
        Try
          oddr = oleDbCommand.ExecuteReader()
          Dim bp_USA As BrushProp = New BrushProp(Me, Color.FromArgb(255, 180, 180))
          While oddr.Read()
            If oddr.GetString(5) = "USA" Then
              tlm._cellDef.bp_Back = bp_USA
              col_Phone._cellDef.bp_Back = New BrushProp(Me, Color.FromArgb(255, 227, 50))
            Else
              tlm._cellDef.bp_Back = Nothing
              col_Phone._cellDef.bp_Back = bp_Phone
            End If
            tlm.NewRow()
            tlm.Add(0, New RepString(fp, oddr.GetString(0)))
            tlm.Add(1, New RepString(fp, oddr.GetString(1)))
            tlm.Add(2, New RepString(fp, oddr.GetString(2)))
            tlm.Add(3, New RepString(fp, oddr.GetString(3)))
            If Not oddr.IsDBNull(4) Then
              tlm.Add(4, New RepString(fp, oddr.GetString(4)))
            End If
            tlm.Add(5, New RepString(fp, oddr.GetString(5)))
            tlm.Add(6, New RepString(fp, oddr.GetString(6)))
            If random.NextDouble() < 0.2 Then  ' mark randomly selected row with a phone icon
              tlm.tlmRow_Cur.aTlmCell(col_Phone).AddMM(1, 0.25, New RepImageMM(stream_Phone, 2.1, 2.3))
            End If
          End While
        Finally
          oddr.Close()
        End Try

        ' print page number and current date/time
        ' VS 2003: For Each page As Page In enum_Page
        Dim rY As Double = rPosBottom + 1.5
        Dim page As Page
        For Each page In enum_Page
          page.AddLT_MM(rPosLeft, rY, New RepString(fp, DateTime.Now.ToShortDateString() + "  " + DateTime.Now.ToShortTimeString()))
          page.AddRT_MM(rPosRight, rY, New RepString(fp, page.iPageNo.ToString() + " / " + iPageCount.ToString()))
        Next
      Finally
        oleDbConnection.Close()
      End Try
    Finally
      tlm.Close()
    End Try
  End Sub

  ' Creates a new page.
  Private Sub Tlm_NewContainer(ByVal oSender As Object, ByVal ea As TableLayoutManager.NewContainerEventArgs)
    Dim page As New Page(Me)

    ' first page with caption
    If page_Cur.iPageNo = 1 Then
      Dim fp_Title As FontProp = New FontPropMM(fd, 7)
      fp_Title.bBold = True
      page_Cur.AddCT_MM(rPosLeft + (rPosRight - rPosLeft) / 2, rPosTop, New RepString(fp_Title, "Customer List"))
      ea.container.rHeightMM -= fp_Title.rLineFeedMM  ' reduce height of table container for the first page
    End If

    ' the new container must be added to the current page
    page_Cur.AddMM(rPosLeft, rPosBottom - ea.container.rHeightMM, ea.container)
  End Sub
End Class
