EALA FREYA FRESENA

EALA FREYA FRESENA

EALA FREYA FRESENA

Public Class frmRelation
 
    Dim ds As New DataSet
    Dim dtPersonal As New DataTable
    Dim dtAbsent As New DataTable
    Dim dtPlanung As New DataTable
 
    Dim dgv As New DataGridView With {.Parent = Me}
 
 
    Private Sub frmRelation_Load(sender As System.Object, _
        e As System.EventArgs) Handles MyBase.Load
 
        'Testdaten erzeugen
        '==================================
        'Abgefragte Personalliste
        With dtPersonal
            .Columns.Add("PersonID", GetType(Integer))
            For i As Integer = 0 To 19
                .Rows.Add(i)
            Next i
 
            Dim pk() As DataColumn = {.Columns(0)}
            .PrimaryKey = pk
            .TableName = "Personal"
            ds.Tables.Add(dtPersonal)
        End With
 
        Dim monday As Date = CDate("9.3.2015") 'Der Wochenstart
 
        'Abgefragte Abwesenheitsliste
        With dtAbsent
            With .Columns
                .Add("ID", GetType(Integer))
                .Add("PersonalID", GetType(Integer))
                .Add("Datum", GetType(Date))
                .Add("AbsentID", GetType(Integer))
            End With
            .PrimaryKey = {.Columns(0)}
 
            Dim rndm As New Random, z As Integer
            For i As Integer = 0 To dtPersonal.Rows.Count - 1
                z += 1
                .Rows.Add(z, i, monday.AddDays(rndm.Next(0, 7)).Date, _
                          rndm.Next(0, 5))
                z += 1
                .Rows.Add(z, i, monday.AddDays(rndm.Next(0, 7)).Date, _
                          rndm.Next(0, 5))
            Next i
 
            .TableName = "Absent"
            ds.Tables.Add(dtAbsent)
        End With
 
 
        'Relation erstellen 
        '=====================================================================
        ds.Relations.Add(New DataRelation _
            ("PersonalAbsent", dtPersonal.Columns("PersonID"), _
                               dtAbsent.Columns("PersonalID")))
 
 
        'Planungsliste per DataRelation aufbauen (Spaltenumsetzung)
        '====================================================================
        With dtPlanung
            With .Columns
                .Add("PersonalID", GetType(Integer))
 
                'Datums-Spalten erstellen
                For i As Integer = 0 To 6
                    .Add(Format(monday.AddDays(i), "Short Date"), _
                         GetType(Integer))
                Next i
            End With
 
            'Personal-Zeilen  
            For i As Integer = 0 To dtPersonal.Rows.Count - 1
                dtPlanung.Rows.Add(i)
            Next i
 
            With dtPersonal
                For i As Integer = 0 To .Rows.Count - 1
 
                    'Childrows für Person abfragen 
                    Dim cr() As DataRow = _
                        .Rows(i).GetChildRows("PersonalAbsent")
 
                    If Not cr Is Nothing Then    
                        For k As Integer = 0 To cr.Length - 1
 
                            'Datumspalte ermitteln
                            Dim coldatum As String = _
                                CType(cr(k)("Datum"), String)
 
                            'Zeile für Person finden (optional, Index = i)
                            Dim r() As DataRow = _
                            dtPlanung.Select _
                            ("PersonalID=" & CType(cr(k)("PersonalID"), String))
 
                            'Abwesenheitsgrund in die Datumspalte eintragen
                            r(0)(coldatum) = cr(k)("AbsentID")
                        Next k
                    End If
                Next i
            End With
        End With
 
        dgv.DataSource = dtPlanung
    End Sub
End Class

EALA FREYA FRESENA