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

Public Class FMCSR
    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
                    '_Prefill()
                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 FMCSR_Disqualified, 
                         FMCSR_DisqualifiedText, FMCSR_Revoked, FMCSR_RevokedText, FMCSR_Denied, FMCSR_DeniedText, FMCSR_Positive, FMCSR_PositiveText, FMCSR_PositiveDate, FMCSR_Convicted, FMCSR_Convicted2, 
                         FMCSR_Convicted2Date, FMCSR_Convicted2Text
                FROM web_DriverApplication WHERE id=@appId",
                connection)
            cmd.Parameters.AddWithValue("appId", Master.app.id)
            Dim dReader = cmd.ExecuteReader()
            dReader.Read()



            ' general info
            ynDisqualified.Checked = Logic.Conversion.ToNullableBoolean(dReader("FMCSR_Disqualified"))
            txtDisqualified.Text = Logic.Conversion.ToString(dReader("FMCSR_DisqualifiedText"))
            ynRevoked.Checked = Logic.Conversion.ToNullableBoolean(dReader("FMCSR_Revoked"))
            txtRevoked.Text = Logic.Conversion.ToString(dReader("FMCSR_RevokedText"))
            ynDenied.Checked = Logic.Conversion.ToNullableBoolean(dReader("FMCSR_Denied"))
            txtDenied.Text = Logic.Conversion.ToString(dReader("FMCSR_DeniedText"))
            ynPositive.Checked = Logic.Conversion.ToNullableBoolean(dReader("FMCSR_Positive"))
            txtPositive.Text = Logic.Conversion.ToString(dReader("FMCSR_PositiveText"))
            txtPostiveDate.Text = Logic.Conversion.ToString(dReader("FMCSR_PositiveDate"))
            ynConvicted.Checked = Logic.Conversion.ToNullableBoolean(dReader("FMCSR_Convicted"))
            ynConvicted2.Checked = Logic.Conversion.ToNullableBoolean(dReader("FMCSR_Convicted2"))
            txtConvicted2Date.Text = Logic.Conversion.ToString(dReader("FMCSR_Convicted2Date"))
            txtConvicted2.Text = Logic.Conversion.ToString(dReader("FMCSR_Convicted2Text"))

        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 Disqualified = ynDisqualified.Checked
        Dim Revoked = ynRevoked.Checked
        Dim Denied = ynDenied.Checked
        Dim Positive = ynPositive.Checked
        Dim Convicted = ynConvicted.Checked
        Dim Convicted2 = ynConvicted2.Checked

        Dim cmd As New SqlCommand("
            UPDATE web_DriverApplication SET
                FMCSR_Disqualified=@FMCSR_Disqualified, FMCSR_DisqualifiedText=@FMCSR_DisqualifiedText, 
                FMCSR_Revoked=@FMCSR_Revoked, FMCSR_RevokedText=@FMCSR_RevokedText, 
                FMCSR_Denied=@FMCSR_Denied, FMCSR_DeniedText=@FMCSR_DeniedText, 
                FMCSR_Positive=@FMCSR_Positive, FMCSR_PositiveText=@FMCSR_PositiveText, FMCSR_PositiveDate=@FMCSR_PositiveDate, 
                FMCSR_Convicted=@FMCSR_Convicted, FMCSR_Convicted2=@FMCSR_Convicted2, FMCSR_Convicted2Date=@FMCSR_Convicted2Date, FMCSR_Convicted2Text=@FMCSR_Convicted2Text 
            WHERE id=@appId",
            connection, transaction)

        With cmd.Parameters
            .AddWithValue("appId", Master.app.id)
            .AddWithValue("FMCSR_Disqualified", If(Disqualified.HasValue, Disqualified.Value, DBNull.Value))
            .AddWithValue("FMCSR_DisqualifiedText", txtDisqualified.Text)
            .AddWithValue("FMCSR_Revoked", If(Revoked.HasValue, Revoked.Value, DBNull.Value))
            .AddWithValue("FMCSR_RevokedText", txtRevoked.Text)
            .AddWithValue("FMCSR_Denied", If(Denied.HasValue, Denied.Value, DBNull.Value))
            .AddWithValue("FMCSR_DeniedText", txtDenied.Text)
            .AddWithValue("FMCSR_Positive", If(Positive.HasValue, Positive.Value, DBNull.Value))
            .AddWithValue("FMCSR_PositiveText", txtPositive.Text)
            .AddWithValue("FMCSR_PositiveDate", txtPostiveDate.Text)
            .AddWithValue("FMCSR_Convicted", If(Convicted.HasValue, Convicted.Value, DBNull.Value))
            .AddWithValue("FMCSR_Convicted2", If(Convicted2.HasValue, Convicted2.Value, DBNull.Value))
            .AddWithValue("FMCSR_Convicted2Date", txtConvicted2Date.Text)
            .AddWithValue("FMCSR_Convicted2Text", txtConvicted2.Text)
        End With

        cmd.ExecuteNonQuery()

    End Sub

    Private Sub _SaveDocument(connection As SqlConnection, transaction As SqlTransaction)
        Dim ReportDataSet As New dsAppGeneralQuestions
        Dim ReportTableAdapter As New TransactionalAppGeneralQuestionsTableAdapter
        ReportTableAdapter.SetConnection(connection, transaction)
        ReportTableAdapter.Fill(ReportDataSet.AppGeneralQuestionsDataTable, Master.app.id)

        Using Report As New crptAppGeneralQuestions
            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 FMCSR_Disqualified 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