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

Public Class PositionApplyingFor
    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()
                _LoadLists()
            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

    Private Sub valLegallyEmployable_ServerValidate(sender As Object, e As ServerValidateEventArgs) Handles valLegallyEmployable.ServerValidate
        e.IsValid = e.Value
    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()



            ' general info
            txtPosition.Text = Logic.Conversion.ToString(dReader("desiredPosition"))
            ddlSchedule.SelectedValue = Logic.Conversion.ToString(dReader("desiredSchedule"))
            txtPayExpected.Text = Logic.Conversion.ToNullableDouble(dReader("desiredPay")).FormatForHtml()
            ddlPaySchedule.SelectedValue = Logic.Conversion.ToString(dReader("desiredPaySchedule"))
            txtReferredBy.Text = Logic.Conversion.ToString(dReader("referredBy"))
            ynPreviousEmployee.Checked = Logic.Conversion.ToNullableBoolean(dReader("isFormerEmployee"))
            txtPreviousEmployeeDateStart.Text = Logic.Conversion.ToNullableDate(dReader("formerEmployeeFrom")).FormatForHtml()
            txtPreviousEmployeeDateEnd.Text = Logic.Conversion.ToNullableDate(dReader("formerEmployeeTo")).FormatForHtml()
            txtPreviousEmployeeLocation.Text = Logic.Conversion.ToString(dReader("formerEmployeeLocation"))
            txtPreviousEmployeePosition.Text = Logic.Conversion.ToString(dReader("formerLocationPosition"))
            txtPreviousEmployeePay.Text = Logic.Conversion.ToNullableDouble(dReader("formerEmployeePay")).FormatForHtml()
            ddlPreviousEmployeePaySchedule.SelectedValue = Logic.Conversion.ToString(dReader("formerEmployeePaySchedule"))
            txtPreviousEmployeeReasonLeft.Text = Logic.Conversion.ToString(dReader("formerEmployeeReasonLeft"))
            txtRelativesEmployed.Text = Logic.Conversion.ToString(dReader("relativesEmployed"))
            ynCurrentlyEmployed.Checked = Logic.Conversion.ToNullableBoolean(dReader("isCurrentlyEmployed"))
            txtTimeSinceEmployed.Text = Logic.Conversion.ToString(dReader("timeSinceEmployment"))
            ynLegallyEmployable.Checked = Logic.Conversion.ToNullableBoolean(dReader("legallyEmployable"))
            ynFelonyConviction.Checked = Logic.Conversion.ToNullableBoolean(dReader("isFelon"))
            txtFelonyConvictionExplanation.Text = Logic.Conversion.ToString(dReader("felonExplanation"))

        End Using
    End Sub

    Private Sub _LoadLists()
    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)
                _SaveLists(connection, transaction)
                _SaveDocument(connection, transaction)
                transaction.Commit()
            End Using
        End Using
    End Sub

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

        Dim isCurrentlyEmployed = ynCurrentlyEmployed.Checked
        Dim isFelon = ynFelonyConviction.Checked
        Dim isFormerEmployee = ynPreviousEmployee.Checked
        Dim legallyEmployable = ynLegallyEmployable.Checked
        Dim formerEmployeeFrom = Logic.Conversion.ToNullableDate(txtPreviousEmployeeDateStart.Text)
        Dim formerEmployeeTo = Logic.Conversion.ToNullableDate(txtPreviousEmployeeDateEnd.Text)

        Dim cmd As New SqlCommand("
            UPDATE web_DriverApplication SET
                -- driver info
             
                -- General info
                desiredPosition=@desiredPosition, desiredSchedule=@desiredSchedule, 
                desiredPay=@desiredPay, desiredPaySchedule=@desiredPaySchedule, referredBy=@referredBy, 
                isFormerEmployee=@isFormerEmployee, formerEmployeeFrom=@formerEmployeeFrom, 
                formerEmployeeTo=@formerEmployeeTo, formerEmployeeLocation=@formerEmployeeLocation, 
                formerLocationPosition=@formerLocationPosition, formerEmployeePay=@formerEmployeePay, 
                formerEmployeePaySchedule=@formerEmployeePaySchedule,
                formerEmployeeReasonLeft=@formerEmployeeReasonLeft, 
                relativesEmployed=@relativesEmployed, isCurrentlyEmployed=@isCurrentlyEmployed, 
                timeSinceEmployment=@timeSinceEmployment, legallyEmployable=@legallyEmployable, 
                isFelon=@isFelon, felonExplanation=@felonExplanation
            WHERE id=@appId",
            connection, transaction)

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

            ' driver info


            ' general info
            .AddWithValue("desiredPosition", txtPosition.Text)
            .AddWithValue("desiredSchedule", ddlSchedule.SelectedValue)
            .AddWithValue("desiredPay", txtPayExpected.Text)
            .AddWithValue("desiredPaySchedule", ddlPaySchedule.SelectedValue)
            .AddWithValue("referredBy", txtReferredBy.Text)
            .AddWithValue("isFormerEmployee", If(isFormerEmployee.HasValue, isFormerEmployee.Value, DBNull.Value))
            .AddWithValue("formerEmployeeFrom", If(formerEmployeeFrom.HasValue, formerEmployeeFrom.Value, DBNull.Value))
            .AddWithValue("formerEmployeeTo", If(formerEmployeeTo.HasValue, formerEmployeeTo.Value, DBNull.Value))
            .AddWithValue("formerEmployeeLocation", txtPreviousEmployeeLocation.Text)
            .AddWithValue("formerLocationPosition", txtPreviousEmployeePosition.Text)
            .AddWithValue("formerEmployeePay", txtPreviousEmployeePay.Text)
            .AddWithValue("formerEmployeePaySchedule", ddlPreviousEmployeePaySchedule.SelectedValue)
            .AddWithValue("formerEmployeeReasonLeft", txtPreviousEmployeeReasonLeft.Text)
            .AddWithValue("relativesEmployed", txtRelativesEmployed.Text)
            .AddWithValue("isCurrentlyEmployed", If(isCurrentlyEmployed.HasValue, isCurrentlyEmployed.Value, DBNull.Value))
            .AddWithValue("timeSinceEmployment", txtTimeSinceEmployed.Text)
            .AddWithValue("legallyEmployable", If(legallyEmployable.HasValue, legallyEmployable.Value, DBNull.Value))
            .AddWithValue("isFelon", If(isFelon.HasValue, isFelon.Value, DBNull.Value))
            .AddWithValue("felonExplanation", txtFelonyConvictionExplanation.Text)
        End With

        cmd.ExecuteNonQuery()

    End Sub

    Private Sub _SaveLists(connection As SqlConnection, transaction As SqlTransaction)
    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