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

Public Class AddressInfo
    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 btnAddAddress_Click(sender As Object, e As EventArgs) Handles rptAddresses.AddClick
        Try
            If _HasEnteredLastAddress() Then
                rptAddresses.TargetCount = Math.Max(rptAddresses.TargetCount, rptAddresses.Items.Count) + 1
            End If
            _LoadAddressList()
        Catch ex As Exception
            Dim errorId = Logic.Error.Log(ex)
            Response.Redirect("/Error.aspx?id=" & errorId)
        End Try
    End Sub

    Public Sub btnRemoveAddress_Click(sender As Object, e As EventArgs)
        Try

            rptAddresses.TargetCount = Math.Max(rptAddresses.TargetCount, rptAddresses.Items.Count) - 1

            Dim btnRemove = DirectCast(sender, Button)
            Dim item = DirectCast(btnRemove.Parent, RepeaterItem)
            Dim hidId = DirectCast(item.FindControl("hidAddressId"), HiddenField)

            _RemoveAddress(CInt(hidId.Value))
            _LoadAddressList()

        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()

            txtAddress.Text = Logic.Conversion.ToString(dReader("applicantAddress"))
            txtAddress2.Text = Logic.Conversion.ToString(dReader("applicantAddress2"))
            txtCity.Text = Logic.Conversion.ToString(dReader("applicantCity"))
            ddlState.SelectedValue = Logic.Conversion.ToString(dReader("applicantState"))
            txtZip.Text = Logic.Conversion.ToString(dReader("applicantZip"))
            ynCurrentAddress3Years.Checked = Logic.Conversion.ToNullableBoolean(dReader("hasLivedAtAddress3Years"))


        End Using
    End Sub

    Private Sub _LoadLists()
        _LoadAddressList()
    End Sub

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

            Dim cmdCount As New SqlCommand(
                "SELECT COUNT(id) FROM web_DriverApplicationAddresses WHERE AppId=@appId",
                connection)
            cmdCount.Parameters.AddWithValue("appId", Master.app.id)
            Dim count = CInt(cmdCount.ExecuteScalar())
            count = Math.Max(count, rptAddresses.TargetCount)

            Dim cmdAddresses As New SqlCommand("
                SELECT * FROM web_DriverApplicationAddresses
                WHERE AppId=@appId",
                connection)
            cmdAddresses.Parameters.AddWithValue("appId", Master.app.id)

            Dim dummy As New List(Of Integer)
            For i = 1 To count : dummy.Add(i) : Next
            rptAddresses.DataSource = dummy
            rptAddresses.DataBind()

            ' We lose list of states after repeater rebind.
            ' This refills the states so that we can set them back.
            For Each item As RepeaterItem In rptAddresses.Items
                Dim ddlState As StatePicker = item.FindControl("ddlPreviousState")
                ddlState.Rebind()
            Next

            Dim dReader = cmdAddresses.ExecuteReader()
            For Each item As RepeaterItem In rptAddresses.Items
                Dim hidId As HiddenField = item.FindControl("hidAddressId")
                Dim txtPreviousStreet As TextBox = item.FindControl("txtPreviousStreet")
                Dim txtPreviousStreet2 As TextBox = item.FindControl("txtPreviousStreet2")
                Dim txtPreviousCity As TextBox = item.FindControl("txtPreviousCity")
                Dim ddlPreviousState As StatePicker = item.FindControl("ddlPreviousState")
                Dim txtPreviousZip As TextBox = item.FindControl("txtPreviousZip")
                Dim txtPreviousDuration As TextBox = item.FindControl("txtPreviousDuration")

                If dReader.Read() Then
                    hidId.Value = dReader("id")
                    txtPreviousStreet.Text = dReader("Street")
                    txtPreviousStreet2.Text = dReader("Street2")
                    txtPreviousCity.Text = dReader("City")
                    ddlPreviousState.SelectedValue = dReader("State")
                    txtPreviousZip.Text = dReader("Zip")
                    txtPreviousDuration.Text = dReader("Duration")
                End If
            Next

        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.CurrentDriver.nameFirst)
            cmd.Parameters.AddWithValue("nameMiddle", Master.CurrentDriver.nameMiddle)
            cmd.Parameters.AddWithValue("nameLast", Master.CurrentDriver.nameLast)
            cmd.Parameters.AddWithValue("nameSuffix", Master.CurrentDriver.nameSuffix)
            cmd.Parameters.AddWithValue("social", Master.CurrentDriver.social)
            cmd.Parameters.AddWithValue("phone", Master.CurrentDriver.phone)
            cmd.Parameters.AddWithValue("email", 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 cmd As New SqlCommand("
            UPDATE web_DriverApplication SET
                -- driver info
                applicantAddress=@applicantAddress, applicantAddress2=@applicantAddress2,
                applicantCity=@applicantCity, applicantState=@applicantState,
                applicantZip=@applicantZip,hasLivedAtAddress3years=@address3Years
            WHERE id=@appId",
            connection, transaction)

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

            ' driver info
            .AddWithValue("applicantAddress", txtAddress.Text)
            .AddWithValue("applicantAddress2", txtAddress2.Text)
            .AddWithValue("applicantCity", txtCity.Text)
            .AddWithValue("applicantState", ddlState.SelectedValue)
            .AddWithValue("applicantZip", txtZip.Text)
            .AddWithValue("address3Years", ynCurrentAddress3Years.Checked)


        End With

        cmd.ExecuteNonQuery()

    End Sub

    Private Sub _SaveLists(connection As SqlConnection, transaction As SqlTransaction)
        _SaveAddressList(connection, transaction)
    End Sub

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

        Dim cmdDelete As New SqlCommand(
            "DELETE FROM web_DriverApplicationAddresses WHERE AppId=@appId",
            connection, transaction)
        cmdDelete.Parameters.AddWithValue("appId", Master.app.id)
        cmdDelete.ExecuteNonQuery()

        Dim cmdSave As New SqlCommand("
            INSERT INTO web_DriverApplicationaddresses
            (AppId, Street, Street2, City, State, Zip, Duration)
            VALUES (@appId, @street, @street2, @city, @state, @zip, @duration);
            SELECT @@IDENTITY;",
            connection, transaction)
        With cmdSave.Parameters
            .AddWithValue("appId", Master.app.id)
            .Add(New SqlParameter("street", SqlDbType.VarChar))
            .Add(New SqlParameter("street2", SqlDbType.VarChar))
            .Add(New SqlParameter("city", SqlDbType.VarChar))
            .Add(New SqlParameter("state", SqlDbType.VarChar))
            .Add(New SqlParameter("zip", SqlDbType.VarChar))
            .Add(New SqlParameter("duration", SqlDbType.VarChar))
        End With

        For Each item As RepeaterItem In rptAddresses.Items
            Dim hidId As HiddenField = item.FindControl("hidAddressId")
            Dim txtPreviousStreet As TextBox = item.FindControl("txtPreviousStreet")
            Dim txtPreviousStreet2 As TextBox = item.FindControl("txtPreviousStreet2")
            Dim txtPreviousCity As TextBox = item.FindControl("txtPreviousCity")
            Dim ddlPreviousState As StatePicker = item.FindControl("ddlPreviousState")
            Dim txtPreviousZip As TextBox = item.FindControl("txtPreviousZip")
            Dim txtPreviousDuration As TextBox = item.FindControl("txtPreviousDuration")

            cmdSave.Parameters("street").Value = txtPreviousStreet.Text
            cmdSave.Parameters("street2").Value = txtPreviousStreet2.Text
            cmdSave.Parameters("city").Value = txtPreviousCity.Text
            cmdSave.Parameters("state").Value = ddlPreviousState.SelectedValue
            cmdSave.Parameters("zip").Value = txtPreviousZip.Text
            cmdSave.Parameters("duration").Value = txtPreviousDuration.Text

            hidId.Value = cmdSave.ExecuteScalar()
        Next

    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 _HasEnteredLastAddress() As Boolean
        Dim count = rptAddresses.Items.Count
        If count = 0 Then
            Return True
        Else
            Dim item As RepeaterItem = rptAddresses.Items(count - 1)
            Dim txtPreviousStreet As TextBox = item.FindControl("txtPreviousStreet")
            Return txtPreviousStreet.Text.Length > 0
        End If
    End Function

    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