Imports System.Data.SqlClient
Imports Logic.NullableDateExtensions
Imports Logic.DateExtensions
Imports ReportingClass

Public Class ConvictionRecord
    Inherits System.Web.UI.Page

    Private Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        Try

            If Not Page.IsPostBack Then
                If Not _HasVisited() Then
                End If
                _LoadForm()
            Else
                _SaveAll()
                Page.Validate()
                If Page.IsValid Then
                    Master.EnableNextPage()
                End If
            End If

        Catch ex As Exception
            Dim errorId = Logic.Error.Log(ex)
            Response.Redirect("/Error.aspx?id=" & errorId)
        End Try
    End Sub


#Region "Loading internals"

    Private Sub _LoadForm()
        Using connection As New SqlConnection(Logic.Database.ConnectionString)
            connection.Open()

            Dim cmd As New SqlCommand("
                SELECT CriminalRecord_ConvictedCrime, CriminalRecord_DeferredProsecution, CriminalRecord_ChargesPending, CriminalRecord_FelonyPled, CriminalRecord_FelonyMinister, 
                         CriminalRecord_Misdemeanor

                FROM web_DriverApplication WHERE id=@appId",
                connection)
            cmd.Parameters.AddWithValue("appId", Master.app.id)
            Dim dReader = cmd.ExecuteReader()
            dReader.Read()

            ynConvictedCrime.Checked = Logic.Conversion.ToNullableBoolean(dReader("CriminalRecord_ConvictedCrime"))
            ynDeferredProsecutions.Checked = Logic.Conversion.ToNullableBoolean(dReader("CriminalRecord_DeferredProsecution"))
            ynCriminalChargesPending.Checked = Logic.Conversion.ToNullableBoolean(dReader("CriminalRecord_ChargesPending"))
            ynFelonyPled.Checked = Logic.Conversion.ToNullableBoolean(dReader("CriminalRecord_FelonyPled"))
            ynFelonyMinister.Checked = Logic.Conversion.ToNullableBoolean(dReader("CriminalRecord_FelonyMinister"))
            ynMisdemeanor.Checked = Logic.Conversion.ToNullableBoolean(dReader("CriminalRecord_Misdemeanor"))

        End Using
    End Sub

#End Region

#Region "Saving internals"

    Private Sub _SaveAll()
        Using connection As New SqlConnection(Logic.Database.ConnectionString)
            connection.Open()
            Using transaction = connection.BeginTransaction()
                _SaveForm(connection, transaction)
                _SaveDocument(connection, transaction)
                transaction.Commit()
            End Using
        End Using
    End Sub

    Private Sub _SaveForm(connection As SqlConnection, transaction As SqlTransaction)

        Dim ConvictedCrime = ynConvictedCrime.Checked
        Dim Deferred = ynDeferredProsecutions.Checked
        Dim Pending = ynCriminalChargesPending.Checked
        Dim Pled = ynFelonyPled.Checked
        Dim Minister = ynFelonyMinister.Checked
        Dim Misdemeanor = ynMisdemeanor.Checked

        Dim cmdSave As New SqlCommand("
            UPDATE web_DriverApplication
            SET CriminalRecord_ConvictedCrime=@CriminalRecord_ConvictedCrime,CriminalRecord_DeferredProsecution=@CriminalRecord_DeferredProsecution,CriminalRecord_ChargesPending=@CriminalRecord_ChargesPending,
            CriminalRecord_FelonyPled=@CriminalRecord_FelonyPled,CriminalRecord_FelonyMinister=@CriminalRecord_FelonyMinister,CriminalRecord_Misdemeanor=@CriminalRecord_Misdemeanor
            WHERE id=@appId",
            connection, transaction)

        With cmdSave.Parameters
            .AddWithValue("appId", Master.app.id)
            .AddWithValue("CriminalRecord_ConvictedCrime", If(ConvictedCrime.HasValue, ConvictedCrime.Value, DBNull.Value))
            .AddWithValue("CriminalRecord_DeferredProsecution", If(Deferred.HasValue, Deferred.Value, DBNull.Value))
            .AddWithValue("CriminalRecord_ChargesPending", If(Pending.HasValue, Pending.Value, DBNull.Value))
            .AddWithValue("CriminalRecord_FelonyPled", If(Pled.HasValue, Pled.Value, DBNull.Value))
            .AddWithValue("CriminalRecord_FelonyMinister", If(Minister.HasValue, Minister.Value, DBNull.Value))
            .AddWithValue("CriminalRecord_Misdemeanor", If(Misdemeanor.HasValue, Misdemeanor.Value, DBNull.Value))
        End With

        cmdSave.ExecuteNonQuery()

    End Sub



    Private Sub _SaveDocument(connection As SqlConnection, transaction As SqlTransaction)
        Dim ReportDataSet As New dsAppDrivingExperience
        Dim ReportTableAdapter As New TransactionalAppDrivingExperienceTableAdapter
        ReportTableAdapter.SetConnection(connection, transaction)
        ReportTableAdapter.Fill(ReportDataSet.AppDrivingExperienceDataTable, Master.app.id)

        Using Report As New crptAppDrivingExperience
            Report.SetDataSource(ReportDataSet)
            Dim rawData = Reporting.StreamToByteArray(Report)

            Dim cmdSave = "
                UPDATE web_DriverApplicationPdf SET pageTitle=@pageTitle, pdfData=@pdfData, pdfDataSize=@pdfDataSize
                WHERE appId=@appId AND pageFileName=@pageFileName;
                IF @@ROWCOUNT = 0 BEGIN
                    INSERT INTO web_DriverApplicationPdf (appId, pageTitle, pageFileName, pdfData, pdfDataSize)
                    VALUES (@appId, @pageTitle, @pageFileName, @pdfData, @pdfDataSize);
                END"
            Dim cmd As New SqlCommand(cmdSave, connection, transaction)
            cmd.Parameters.AddWithValue("appId", Master.app.id)
            cmd.Parameters.AddWithValue("pageTitle", Master.CurrentTitle)
            cmd.Parameters.AddWithValue("pageFileName", Master.CurrentPage)
            cmd.Parameters.AddWithValue("pdfData", rawData)
            cmd.Parameters.AddWithValue("pdfDataSize", rawData.Length)
            cmd.ExecuteNonQuery()
        End Using
    End Sub

#End Region

#Region "Other internals"



    Private Function _HasVisited() As Boolean
        Using connection As New SqlConnection(Logic.Database.ConnectionString)
            connection.Open()
            Dim cmd As New SqlCommand(
                "SELECT CriminalRecord_ConvictedCrime FROM web_DriverApplication WHERE id=@appId",
                connection)
            cmd.Parameters.AddWithValue("appId", Master.app.id)
            Return TypeOf cmd.ExecuteScalar() IsNot DBNull
        End Using
    End Function

#End Region

End Class