Imports BDNotasAlumnos
Imports System.ComponentModel
Imports System.Data.Objects
Imports System.Data

Class MainWindow
  Private asigs As Object

  Private Sub dgAlumnos_SelectionChanged(sender As System.Object, e As System.Windows.Controls.SelectionChangedEventArgs)
    If e.AddedItems.Count = 0 Then Return
    Dim alum As alumno = TryCast(e.AddedItems(0), alumno)
    If alum Is Nothing Then Return
    Dim pAsigsNotas As New CProveedorAsignaturasNotas()
    asigs = pAsigsNotas.ObtenerAsignaturasNotas(alum.id_alumno)
    spAsignaturas.DataContext = asigs
  End Sub

  Private Sub boTodas_Checked(sender As System.Object, e As System.Windows.RoutedEventArgs)
    Dim vista As ICollectionView = CollectionViewSource.GetDefaultView(asigs)
    If vista Is Nothing Then Return
    vista.Filter = Nothing
  End Sub

  Private Sub boSuspensas_Checked(sender As System.Object, e As System.Windows.RoutedEventArgs)
    Dim vista As ICollectionView = CollectionViewSource.GetDefaultView(asigs)
    If vista Is Nothing Then Return
    vista.Filter = Function(elemento As Object) elemento.nota < 5
  End Sub

  Private Sub boAprobadas_Checked(sender As System.Object, e As System.Windows.RoutedEventArgs)
    Dim vista As ICollectionView = CollectionViewSource.GetDefaultView(asigs)
    If vista Is Nothing Then Return
    vista.Filter = Function(elemento As Object) elemento.nota >= 5
  End Sub

  Private Sub ArchivoSalir_Click(sender As Object, e As RoutedEventArgs)
    Close()
  End Sub

  Private Sub CambiosBDModificarNota_Click(sender As Object, e As RoutedEventArgs)
    Using contextoDeObjs As New bd_notasAlumnosEntities()
      ' Datos a obtener del diálogo 
      Dim idAlum As Integer
      Dim idAsig As Integer
      Dim notaAlum As Single

      ' Mostrar el diálogo
      Dim dlgModNota As New ModificarNota()
      If dlgModNota.ShowDialog() = True Then
        ' Obtener los datos
        idAlum = dlgModNota.IdAlumno
        idAsig = dlgModNota.IdAsignatura
        notaAlum = dlgModNota.Nota
      Else
        Return
      End If

      ' Consulta para obtener la fila a modificar
      Dim consulta = _
        From al_as In contextoDeObjs.alums_asigs _
        Where al_as.id_alumno = idAlum AndAlso al_as.id_asignatura = idAsig _
        Select al_as

      If consulta.Count() = 0 Then
        MessageBox.Show("La consulta no contiene elementos")
        Return
      End If

      ' Ejecutar la consulta y cambiar los valores
      ' de las columnas implicadas
      For Each al_as As alum_asig In consulta
        al_as.nota = notaAlum
      Next

      ' Enviar los cambios a la base de datos
      Try
        contextoDeObjs.SaveChanges()
        MessageBox.Show("Cambios realizados")
        ' Refrescar dgAsignaturas
        RefrescarListaAsigs(idAlum)
      Catch ex As Exception
        MessageBox.Show(ex.InnerException.Message)
      End Try
    End Using
  End Sub

  Private Sub RefrescarListaAsigs(idAlum As Integer)
    Dim indice As Integer = dgAsignaturas.SelectedIndex
    Dim pAsigsNotas As New CProveedorAsignaturasNotas()
    asigs = pAsigsNotas.ObtenerAsignaturasNotas(idAlum)
    spAsignaturas.DataContext = asigs
    dgAsignaturas.SelectedIndex = indice
  End Sub

  Private Sub CambiosBDInsertarAlumno_Click(sender As Object, e As RoutedEventArgs)
    Using contextoDeObjs As New bd_notasAlumnosEntities()
      ' Obtener los datos
      Dim idAlum As Integer = 1234560
      Dim nomAlum As String = "Alberto García Sánchez"

      ' Crear un nuevo objeto alumno
      Dim alum As alumno = alumno.Createalumno(idAlum, nomAlum)

      ' Añadirlo al conjunto de entidades alumnos
      contextoDeObjs.AddToalumnos(alum)
      ' O bien:
      'contextoDeObjs.AddObject("alumnos", alum);

      ' Enviar los cambios a la base de datos
      Try
        contextoDeObjs.SaveChanges()
        MessageBox.Show("Cambios realizados")
      Catch ex As Exception
        MessageBox.Show(ex.InnerException.Message)
      End Try
    End Using
  End Sub

  Private Sub CambiosBDInsertarAsignatura_Click(sender As Object, e As RoutedEventArgs)
    Using contextoDeObjs As New bd_notasAlumnosEntities()
      ' Obtener los datos
      Dim idAsig As Integer = 34680
      Dim nomAsig As String = "PROGRAMACIÓN CONCURRENTE"

      ' Crear una nueva asignatura
      Dim asig As asignatura = asignatura.Createasignatura(idAsig, nomAsig)

      ' Añadirla al conjunto de entidades asignaturas
      contextoDeObjs.AddToasignaturas(asig)

      ' Enviar los cambios a la base de datos
      Try
        contextoDeObjs.SaveChanges()
        MessageBox.Show("Cambios realizados")
      Catch ex As Exception
        MessageBox.Show(ex.InnerException.Message)
      End Try
    End Using
  End Sub

  Private Sub CambiosBDMatricular_Click(sender As Object, e As RoutedEventArgs)
    Using contextoDeObjs As New bd_notasAlumnosEntities()
      ' Obtener los datos
      Dim idAlum As Integer = 1234560
      Dim idAsig As Integer = 34680

      ' Consulta para obtener el alumno a matricular
      Dim consulta1 = _
        From alum In contextoDeObjs.alumnos _
        Where alum.id_alumno = idAlum _
        Select alum

      ' Consulta para obtener la asignatura de la que se va a matricular
      Dim consulta2 = _
        From asig In contextoDeObjs.asignaturas _
        Where asig.id_asignatura = idAsig _
        Select asig

      If consulta1.Count() = 0 OrElse consulta2.Count() = 0 Then
        MessageBox.Show("El alumno o la asignatura no existen")
        Return
      End If
      Dim alumno As alumno = consulta1.First()
      Dim asignatura As asignatura = consulta2.First()

      ' Crear un nuevo objeto alums_asigs
      Dim al_as = alum_asig.Createalum_asig(idAlum, idAsig, 0.0F)

      ' Establecer las relaciones
      al_as.alumnoReference.Value = alumno ' N:1
      al_as.asignaturaReference.Value = asignatura ' N:1
      ' O bien:
      'alumno.alums_asigs.Add(al_as);     ' 1:N
      'asignatura.alums_asigs.Add(al_as); ' 1:N
      ' Añadir el nuevo objeto al conjunto de entidades alums_asigs
      contextoDeObjs.AddToalums_asigs(al_as)

      ' Enviar los cambios a la base de datos
      Try
        contextoDeObjs.SaveChanges()
        MessageBox.Show("Cambios realizados")
      Catch ex As Exception
        MessageBox.Show(ex.InnerException.Message)
      End Try
    End Using
  End Sub

  Private Sub CambiosBDBorrarAlumno_Click(sender As Object, e As RoutedEventArgs)
    Using contextoDeObjs As New bd_notasAlumnosEntities()
      ' Obtener los datos
      Dim idAlum As Integer = 1234560

      ' Consulta para obtener los objetos secundarios de idAlum
      Dim consulta1 = _
        From alum In contextoDeObjs.alums_asigs _
        Where alum.id_alumno = idAlum _
        Select alum

      If consulta1.Count() <> 0 Then
        For Each al_as In consulta1
          ' Borrar los objetos secundarios
          contextoDeObjs.DeleteObject(al_as)
        Next
      End If

      ' Consulta para obtener el objeto primario idAlum
      Dim consulta2 = _
        From alum In contextoDeObjs.alumnos _
        Where alum.id_alumno = idAlum _
        Select alum

      If consulta2.Count() <> 0 Then
        For Each alum In consulta2
          ' Borrar el objeto primario
          contextoDeObjs.DeleteObject(alum)
        Next
      End If

      If consulta1.Count() = 0 AndAlso consulta2.Count() = 0 Then
        MessageBox.Show("La consulta no contiene elementos")
        Return
      End If

      ' Enviar los cambios a la base de datos
      Try
        contextoDeObjs.SaveChanges()
        MessageBox.Show("Cambios realizados")
      Catch ex As Exception
        MessageBox.Show(ex.InnerException.Message)
      End Try
    End Using
  End Sub

  Private Sub CambiosBDBorrarAsignatura_Click(sender As Object, e As RoutedEventArgs)
    Using contextoDeObjs As New bd_notasAlumnosEntities()
      ' Obtener los datos
      Dim idAsig As Integer = 34680

      ' Consulta para obtener los objetos secundarios de idAsig
      Dim consulta1 = _
        From asig In contextoDeObjs.alums_asigs _
        Where asig.id_asignatura = idAsig _
        Select asig

      If consulta1.Count() <> 0 Then
        For Each al_as In consulta1
          ' Borrar los objetos secundarios
          contextoDeObjs.DeleteObject(al_as)
        Next
      End If

      ' Consulta para obtener el objeto primario idAsig
      Dim consulta2 = _
        From asig In contextoDeObjs.asignaturas _
        Where asig.id_asignatura = idAsig _
        Select asig

      If consulta2.Count() <> 0 Then
        For Each asig In consulta2
          ' Borrar el objeto primario
          contextoDeObjs.DeleteObject(asig)
        Next
      End If

      If consulta1.Count() = 0 AndAlso consulta2.Count() = 0 Then
        MessageBox.Show("La consulta no contiene elementos")
        Return
      End If

      ' Enviar los cambios a la base de datos
      Try
        contextoDeObjs.SaveChanges()
        MessageBox.Show("Cambios realizados")
      Catch ex As Exception
        MessageBox.Show(ex.InnerException.Message)
      End Try
    End Using
  End Sub

  Private Sub CambiosBDConcurrencia_Click(sender As Object, e As RoutedEventArgs)
    Using contexto1DeObjs As New bd_notasAlumnosEntities(),
          contexto2DeObjs As New bd_notasAlumnosEntities()
      ' SESIÓN 1: contexto1DeObjs
      ' Obtener los datos
      Dim idAlum As Integer = 1234567
      Dim idAsig As Integer = 20590

      ' Consulta para obtener la fila a modificar
      Dim consulta1 = _
        From al_as In contexto1DeObjs.alums_asigs _
        Where al_as.id_alumno = idAlum AndAlso al_as.id_asignatura = idAsig _
        Select al_as

      ' Ver el código T-SQL
      MessageBox.Show(DirectCast(consulta1, ObjectQuery).ToTraceString())

      ' Ejecutar la consulta y cambiar la nota
      consulta1.First().nota = consulta1.First().nota + 0.5F

      ' SESIÓN 2: contexto2DeObjs
      ' Obtener los datos: mismo alumno y misma asignatura
      ' pero diferente nota
      idAlum = 1234567
      idAsig = 20590

      ' Consulta para obtener la fila a modificar
      Dim consulta2 = _
        From al_as In contexto2DeObjs.alums_asigs _
        Where al_as.id_alumno = idAlum AndAlso al_as.id_asignatura = idAsig _
        Select al_as

      ' Ejecutar la consulta y cambiar la nota
      consulta2.First().nota = consulta2.First().nota + 0.75F

      ' SESIÓN 1: enviar los cambios a la base de datos
      Try
        contexto1DeObjs.SaveChanges()
      Catch ex As Exception
        MessageBox.Show(ex.InnerException.Message)
      End Try

      ' SESIÓN 2: enviar los cambios a la base de datos
      Try
        contexto2DeObjs.SaveChanges()
      Catch ex As Exception
        MessageBox.Show(ex.InnerException.Message)
      End Try
    End Using
  End Sub

  Private Sub CambiosBDEstado_Click(sender As Object, e As RoutedEventArgs)
    Using contextoDeObjs As New bd_notasAlumnosEntities()
      ' Obtener los datos
      Dim idAlum As Integer = 1234567
      Dim idAsig As Integer = 20590

      ' Consulta para obtener la fila a modificar
      Dim consulta1 = From al_as In contextoDeObjs.alums_asigs _
          Where al_as.id_alumno = idAlum And al_as.id_asignatura = idAsig _
          Select al_as

      ' Ejecutar la consulta y cambiar la nota
      consulta1.First().nota = consulta1.First().nota + 0.5F

      Dim admin As ObjectStateManager = _
          contextoDeObjs.ObjectStateManager
      Dim entidadesModificadas = _
          admin.GetObjectStateEntries(EntityState.Modified)

      For Each OSEntry In entidadesModificadas
        Dim info As String = _
             "Nombre de la entidad contenedora: " & _
             OSEntry.EntityKey.EntityContainerName & vbLf & _
             "Nombre del conjunto de entidades: " & _
             OSEntry.EntityKey.EntitySetName.ToString() & vbLf & _
             "Clave: " & _
             OSEntry.EntityKey.EntityKeyValues.First().Key & "=" & _
             OSEntry.EntityKey.EntityKeyValues.First().Value & vbLf & _
             "Valores originales -> actuales:" & vbLf

        For i As Integer = 0 To OSEntry.OriginalValues.FieldCount - 1
          info += vbTab & OSEntry.OriginalValues(i) & " -> " & _
          OSEntry.CurrentValues(i) & vbLf
        Next
        MessageBox.Show(info)
      Next

      Try
        contextoDeObjs.SaveChanges()
      Catch ex As Exception
        MessageBox.Show(ex.Message)
      End Try
    End Using
  End Sub
End Class
