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

Public Class PersonalInformation
    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
                    -- driver info
                    applicantNameFirst, applicantNameMiddle, applicantNameLast, applicantNameSuffix, 
                    applicantSocial, applicantDob, applicantAddress, applicantAddress2,
                    applicantCity, applicantState, applicantZip, applicantPhone, applicantEmail,
                    -- General info
                    desiredPosition, desiredSchedule, desiredPay, desiredPaySchedule, referredBy,
                    isFormerEmployee, formerEmployeeFrom, formerEmployeeTo, formerEmployeeLocation, 
                    formerLocationPosition, formerEmployeePay, formerEmployeePaySchedule, formerEmployeeReasonLeft, 
                    relativesEmployed, isCurrentlyEmployed, timeSinceEmployment, 
                    legallyEmployable, isFelon, felonExplanation, hasLivedAtAddress3Years
                FROM web_DriverApplication WHERE id=@appId",
                connection)
            cmd.Parameters.AddWithValue("appId", Master.app.id)
            Dim dReader = cmd.ExecuteReader()
            dReader.Read()

            ' driver info
            txtNameFirst.Text = Logic.Conversion.ToString(dReader("applicantNameFirst"))
            txtNameMiddle.Text = Logic.Conversion.ToString(dReader("applicantNameMiddle"))
            txtNameLast.Text = Logic.Conversion.ToString(dReader("applicantNameLast"))
            txtNameSuffix.Text = Logic.Conversion.ToString(dReader("applicantNameSuffix"))
            txtSocial.Value = Logic.Conversion.ToString(dReader("applicantSocial"))
            txtDateBirth.Text = Logic.Conversion.ToNullableDate(dReader("applicantDob")).FormatForHtml()
            txtPhone.Text = Logic.Conversion.ToString(dReader("applicantPhone"))
            txtEmail.Text = Logic.Conversion.ToString(dReader("applicantEmail"))




        End Using
    End Sub



    Private Sub _Prefill()
        Using connection As New SqlConnection(Logic.Database.ConnectionString)
            connection.Open()
            Dim cmd As New SqlCommand("
                UPDATE web_DriverApplication
                SET applicantNameFirst = @nameFirst,
                    applicantNameMiddle = @nameMiddle,
                    applicantNameLast = @nameLast,
                    applicantNameSuffix = @nameSuffix,
                    applicantSocial = @social,
                    applicantPhone = @phone,
                    applicantEmail = @email
                WHERE id=@appId",
                connection)
            cmd.Parameters.AddWithValue("appId", Master.app.id)
            cmd.Parameters.AddWithValue("nameFirst", Master.Master.CurrentDriver.nameFirst)
            cmd.Parameters.AddWithValue("nameMiddle", Master.Master.CurrentDriver.nameMiddle)
            cmd.Parameters.AddWithValue("nameLast", Master.Master.CurrentDriver.nameLast)
            cmd.Parameters.AddWithValue("nameSuffix", Master.Master.CurrentDriver.nameSuffix)
            cmd.Parameters.AddWithValue("social", Master.Master.CurrentDriver.social)
            cmd.Parameters.AddWithValue("phone", Master.Master.CurrentDriver.phone)
            cmd.Parameters.AddWithValue("email", Master.Master.CurrentDriver.email)
            cmd.ExecuteNonQuery()
        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 cmd As New SqlCommand("
            UPDATE web_DriverApplication SET
                -- driver info
                applicantNameFirst=@applicantNameFirst, applicantNameMiddle=@applicantNameMiddle, applicantNameLast=@applicantNameLast, applicantNameSuffix=@applicantNameSuffix,
                applicantSocial=@applicantSocial, applicantDob=@applicantDob,
                applicantPhone=@applicantPhone,
                applicantEmail=@applicantEmail
            WHERE id=@appId",
            connection, transaction)

        With cmd.Parameters
            .AddWithValue("appId", Master.app.id)

            ' driver info
            Dim dateBirth = Logic.Conversion.ToNullableDate(txtDateBirth.Text)
            .AddWithValue("applicantNameFirst", txtNameFirst.Text)
            .AddWithValue("applicantNameMiddle", txtNameMiddle.Text)
            .AddWithValue("applicantNameLast", txtNameLast.Text)
            .AddWithValue("applicantNameSuffix", txtNameSuffix.Text)
            .AddWithValue("applicantSocial", txtSocial.Value)
            .AddWithValue("applicantDob", If(dateBirth.HasValue, dateBirth.Value, DBNull.Value))

            .AddWithValue("applicantPhone", txtPhone.Text)
            .AddWithValue("applicantEmail", txtEmail.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 applicantNameFirst FROM web_DriverApplication WHERE id=@appId",
                connection)
            cmd.Parameters.AddWithValue("appId", Master.app.id)
            Return TypeOf cmd.ExecuteScalar() IsNot DBNull
        End Using
    End Function

    Private Sub _RemoveAddress(itemId As Integer)
        Using connection As New SqlConnection(Logic.Database.ConnectionString)
            connection.Open()
            Dim cmd As New SqlCommand(
                "DELETE FROM web_DriverApplicationAddresses WHERE id=@itemId",
                connection)
            cmd.Parameters.AddWithValue("itemId", itemId)
            cmd.ExecuteNonQuery()
        End Using
    End Sub

    Private Sub _RemoveSchool(itemId As Integer)
        Using connection As New SqlConnection(Logic.Database.ConnectionString)
            connection.Open()
            Dim cmd As New SqlCommand(
                "DELETE FROM web_DriverApplicationSchools WHERE id=@itemId",
                connection)
            cmd.Parameters.AddWithValue("itemId", itemId)
            cmd.ExecuteNonQuery()
        End Using
    End Sub

#End Region

End Class