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

Public Class EmploymentHistory
    Inherits System.Web.UI.Page

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

            If Not Page.IsPostBack Then
                _LoadForm()
                _LoadList()
            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 btnAdd_Click(sender As Object, e As EventArgs) Handles rptHistory.AddClick
        Try
            If _HasEnteredLastItem() Then
                rptHistory.TargetCount = Math.Max(rptHistory.TargetCount, rptHistory.Items.Count) + 1
                ynComplete.Checked = Nothing
            End If
            _LoadList()
        Catch ex As Exception
            Dim errorId = Logic.Error.Log(ex)
            Response.Redirect("/Error.aspx?id=" & errorId)
        End Try
    End Sub

    Private Sub btnConfirmGaps_Click(sender As Object, e As EventArgs) Handles btnConfirmGaps.Click
        Try
            valGaps.Enabled = False
            phConfirmGaps.Visible = False
            _InsertUnemployment()
            _LoadList()
        Catch ex As ThreadAbortException
            Throw
        Catch ex As Exception
            Dim errorId = Logic.Error.Log(ex)
            Response.Redirect("/Error.aspx?id=" & errorId)
        End Try
    End Sub

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

            rptHistory.TargetCount = Math.Max(rptHistory.TargetCount, rptHistory.Items.Count) - 1
            ynComplete.Checked = Nothing

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

            _Remove(CInt(hidId.Value))
            _LoadList()

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

    Private Sub valComplete_ServerValidate(sender As Object, e As ServerValidateEventArgs) Handles valComplete.ServerValidate
        If ynComplete.Checked.HasValue AndAlso ynComplete.Checked.Value = True
            e.IsValid = True
        Else
            e.IsValid = False
        End If
    End Sub

    Private Sub valGaps_ServerValidate(sender As Object, e As ServerValidateEventArgs) Handles valGaps.ServerValidate
        Dim hasGaps = _HasGaps()
        e.IsValid = Not hasGaps
        phConfirmGaps.Visible = hasGaps
    End Sub

#Region "Loading internals"

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

            Dim cmd As New SqlCommand("
                SELECT license1Endorsements + ISNULL(license2Endorsements,'') + ISNULL(license3Endorsements,'') AS endorsements,
                    EmploymentHistoryEntered
                FROM web_DriverApplication WHERE id=@appId",
                connection)
            cmd.Parameters.AddWithValue("appId", Master.app.id)

            Dim dReader = cmd.ExecuteReader()
            dReader.Read()

            Dim endorsements = Logic.Conversion.ToString(dReader(0))
            If endorsements.Contains("/CDL/") Then
                lblComplete.Text = "Have you entered at least 10 years of employment history?"
            Else
                lblComplete.Text = "Have you entered at least 3 years of employment history?"
            End If

            ynComplete.Checked = Logic.Conversion.ToNullableBoolean(dReader(1))

        End Using
    End Sub

    Private Sub _LoadList()
        Using connection As New SqlConnection(Logic.Database.connectionString)
            connection.Open()

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

            Dim cmdHistory As New SqlCommand("
                SELECT * FROM web_DriverApplicationEmploymentHistory
                WHERE AppId=@appId
                ORDER BY EmployedTo DESC, id DESC",
                connection)
            cmdHistory.Parameters.AddWithValue("appId", Master.app.id)

            Dim dummy As New List(Of Integer)
            For i = 1 To count : dummy.Add(i) : Next
            rptHistory.DataSource = dummy
            rptHistory.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 rptHistory.Items
                Dim ddlState As StatePicker = item.FindControl("ddlState")
                ddlState.Rebind()
            Next

            Dim dReader = cmdHistory.ExecuteReader()
            For Each item As RepeaterItem In rptHistory.Items
                Dim hidId As HiddenField = item.FindControl("hidId")
                Dim txtName As TextBox = item.FindControl("txtName")
                Dim txtStreet As TextBox = item.FindControl("txtStreet")
                Dim txtCity As TextBox = item.FindControl("txtCity")
                Dim ddlState As StatePicker = item.FindControl("ddlState")
                Dim txtZip As TextBox = item.FindControl("txtZip")
                Dim txtContact As TextBox = item.FindControl("txtContact")
                Dim txtPhone As TextBox = item.FindControl("txtPhone")
                Dim txtFax As TextBox = item.FindControl("txtFax")
                Dim txtFrom As TextBox = item.FindControl("txtFrom")
                Dim txtTo As TextBox = item.FindControl("txtTo")
                Dim txtMonths As TextBox = item.FindControl("txtMonths")
                Dim txtPosition As TextBox = item.FindControl("txtPosition")
                Dim ynSubjectFMCSR As YesNoPicker = item.FindControl("ynSubjectFMCSR")
                Dim ynSafetySensitive As YesNoPicker = item.FindControl("ynSafetySensitive")
                Dim ddlReasonLeft As DropDownList = item.FindControl("ddlReasonLeft")
                Dim txtReasonLeft As TextBox = item.FindControl("txtReasonLeft")
                Dim txtExplanation As TextBox = item.FindControl("txtExplanation")

                Dim litId_txtFrom As Literal = item.FindControl("litId_txtFrom")
                Dim litId_txtTo As Literal = item.FindControl("litId_txtTo")
                Dim litId_txtMonths As Literal = item.FindControl("litId_txtMonths")
                litId_txtFrom.Text = txtFrom.ClientID
                litId_txtTo.Text = txtTo.ClientID
                litId_txtMonths.Text = txtMonths.ClientID

                If dReader.Read() Then
                    Dim TotalMonths = Logic.Conversion.ToNullableInteger(dReader("TotalMonths"))
                    Dim ReasonForLeaving As String = dReader("ReasonForLeaving")

                    Dim IsOtherReasonForLeaving = If(Left(ReasonForLeaving, 7) = "Other: ", True, False)
                    If IsOtherReasonForLeaving Then
                        ReasonForLeaving = ReasonForLeaving.Substring(7)
                    End If

                    hidId.Value = dReader("id")
                    txtName.Text = dReader("CompanyName")
                    txtStreet.Text = dReader("Address")
                    txtCity.Text = dReader("City")
                    ddlState.SelectedValue = dReader("State")
                    txtZip.Text = dReader("Zip")
                    txtContact.Text = dReader("ContactName")
                    txtPhone.Text = dReader("Phone")
                    txtFax.Text = Logic.Conversion.ToString(dReader("Fax"))
                    txtFrom.Text = Logic.Conversion.ToNullableDate(dReader("EmployedFrom")).FormatForHtml()
                    txtTo.Text = Logic.Conversion.ToNullableDate(dReader("EmployedTo")).FormatForHtml()
                    txtMonths.Text = If(TotalMonths Is Nothing, "", TotalMonths)
                    txtPosition.Text = dReader("PositionHeld")
                    ynSubjectFMCSR.Checked = Logic.Conversion.ToNullableBoolean(dReader("WasSubjectToFMCSR"))
                    ynSafetySensitive.Checked = Logic.Conversion.ToNullableBoolean(dReader("WasSafetySensitive"))
                    ddlReasonLeft.SelectedValue = If(IsOtherReasonForLeaving, "Other", ReasonForLeaving)
                    txtReasonLeft.Text = If(IsOtherReasonForLeaving, ReasonForLeaving, "")
                    txtExplanation.Text = dReader("LeavingExplanation")
                End If
            Next

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

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

        Dim isComplete = ynComplete.Checked

        Dim cmd As New SqlCommand(
            "UPDATE web_DriverApplication SET EmploymentHistoryEntered=@entered WHERE id=@appId",
            connection, transaction)
        cmd.Parameters.AddWithValue("appId", Master.app.id)
        cmd.Parameters.AddWithValue("entered", If(isComplete.HasValue, isComplete.Value, DBNull.Value))
        cmd.ExecuteNonQuery()

    End Sub

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

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

        Dim cmdSave As New SqlCommand("
            INSERT INTO web_DriverApplicationEmploymentHistory
            (AppId, CompanyName, Address, City, State, Zip, ContactName, Phone, Fax, EmployedFrom, EmployedTo, TotalMonths, PositionHeld, WasSubjectToFMCSR, WasSafetySensitive, ReasonForLeaving, LeavingExplanation)
            VALUES (@appId, @CompanyName, @Address, @City, @State, @Zip, @ContactName, @Phone, @Fax, @EmployedFrom, @EmployedTo, @TotalMonths, @PositionHeld, @WasSubjectToFMCSR, @WasSafetySensitive, @ReasonForLeaving, @LeavingExplanation);
            SELECT @@IDENTITY;",
            connection, transaction)
        With cmdSave.Parameters
            .AddWithValue("appId", Master.app.id)
            .Add(New SqlParameter("CompanyName", SqlDbType.VarChar))
            .Add(New SqlParameter("Address", SqlDbType.VarChar))
            .Add(New SqlParameter("City", SqlDbType.VarChar))
            .Add(New SqlParameter("State", SqlDbType.VarChar))
            .Add(New SqlParameter("Zip", SqlDbType.VarChar))
            .Add(New SqlParameter("ContactName", SqlDbType.VarChar))
            .Add(New SqlParameter("Phone", SqlDbType.VarChar))
            .Add(New SqlParameter("Fax", SqlDbType.VarChar))
            .Add(New SqlParameter("EmployedFrom", SqlDbType.Date))
            .Add(New SqlParameter("EmployedTo", SqlDbType.Date))
            .Add(New SqlParameter("TotalMonths", SqlDbType.Int))
            .Add(New SqlParameter("PositionHeld", SqlDbType.VarChar))
            .Add(New SqlParameter("WasSubjectToFMCSR", SqlDbType.Bit))
            .Add(New SqlParameter("WasSafetySensitive", SqlDbType.Bit))
            .Add(New SqlParameter("ReasonForLeaving", SqlDbType.VarChar))
            .Add(New SqlParameter("LeavingExplanation", SqlDbType.VarChar))
        End With

        For Each item As RepeaterItem In rptHistory.Items
            Dim hidId As HiddenField = item.FindControl("hidId")
            Dim txtName As TextBox = item.FindControl("txtName")
            Dim txtStreet As TextBox = item.FindControl("txtStreet")
            Dim txtCity As TextBox = item.FindControl("txtCity")
            Dim ddlState As StatePicker = item.FindControl("ddlState")
            Dim txtZip As TextBox = item.FindControl("txtZip")
            Dim txtContact As TextBox = item.FindControl("txtContact")
            Dim txtPhone As TextBox = item.FindControl("txtPhone")
            Dim txtFax As TextBox = item.FindControl("txtFax")
            Dim txtFrom As TextBox = item.FindControl("txtFrom")
            Dim txtTo As TextBox = item.FindControl("txtTo")
            Dim txtMonths As TextBox = item.FindControl("txtMonths")
            Dim txtPosition As TextBox = item.FindControl("txtPosition")
            Dim ynSubjectFMCSR As YesNoPicker = item.FindControl("ynSubjectFMCSR")
            Dim ynSafetySensitive As YesNoPicker = item.FindControl("ynSafetySensitive")
            Dim ddlReasonLeft As DropDownList = item.FindControl("ddlReasonLeft")
            Dim txtReasonLeft As TextBox = item.FindControl("txtReasonLeft")
            Dim txtExplanation As TextBox = item.FindControl("txtExplanation")

            Dim EmployedFrom = Logic.Conversion.ToNullableDate(txtFrom.Text)
            Dim EmployedTo = Logic.Conversion.ToNullableDate(txtTo.Text)
            Dim TotalMonths = Logic.Conversion.ToNullableInteger(txtMonths.Text)
            Dim ReasonForLeaving = If(ddlReasonLeft.Text = "Other", "Other: " & txtReasonLeft.Text, ddlReasonLeft.Text)

            cmdSave.Parameters("CompanyName").Value = Logic.Conversion.ToString(txtName.Text)
            cmdSave.Parameters("Address").Value = Logic.Conversion.ToString(txtStreet.Text)
            cmdSave.Parameters("City").Value = Logic.Conversion.ToString(txtCity.Text)
            cmdSave.Parameters("State").Value = Logic.Conversion.ToString(ddlState.SelectedValue)
            cmdSave.Parameters("Zip").Value = Logic.Conversion.ToString(txtZip.Text)
            cmdSave.Parameters("ContactName").Value = Logic.Conversion.ToString(txtContact.Text)
            cmdSave.Parameters("Phone").Value = Logic.Conversion.ToString(txtPhone.Text)
            cmdSave.Parameters("Fax").Value = Logic.Conversion.ToString(txtFax.Text)
            cmdSave.Parameters("EmployedFrom").Value = If(EmployedFrom.HasValue, EmployedFrom.Value, DBNull.Value)
            cmdSave.Parameters("EmployedTo").Value = If(EmployedTo.HasValue, EmployedTo.Value, DBNull.Value)
            cmdSave.Parameters("TotalMonths").Value = If(TotalMonths.HasValue, TotalMonths.Value, DBNull.Value)
            cmdSave.Parameters("PositionHeld").Value = Logic.Conversion.ToString(txtPosition.Text)
            cmdSave.Parameters("WasSubjectToFMCSR").Value = Logic.Conversion.ToBoolean(ynSubjectFMCSR.Checked)
            cmdSave.Parameters("WasSafetySensitive").Value = Logic.Conversion.ToBoolean(ynSafetySensitive.Checked)
            cmdSave.Parameters("ReasonForLeaving").Value = ReasonForLeaving
            cmdSave.Parameters("LeavingExplanation").Value = Logic.Conversion.ToString(txtExplanation.Text)

            hidId.Value = cmdSave.ExecuteScalar()
        Next

    End Sub

    Private Sub _SaveDocument(connection As SqlConnection, transaction As SqlTransaction)
        Dim ReportDataSet As New dsAppEmploymentHistory
        Dim ReportTableAdapter As New TransactionalAppEmploymentHistoryTableAdapter
        ReportTableAdapter.SetConnection(connection, transaction)
        ReportTableAdapter.Fill(ReportDataSet.AppEmploymentHistoryDataTable, Master.app.id)

        Using Report As New crptAppEmploymentHistory
            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 "Gaps algorithm"

    Private Const GapThreshold = 30

    Private Structure DateRange
        Property StartDate As Date
        Property EndDate As Date
    End Structure

    Private Function _AreDatesClose(first As Date, second As Date)
        Return Math.Abs(DateDiff(DateInterval.Day, first, second)) < GapThreshold
    End Function

    Private Function _IsInRange(test As Date, range As DateRange)
        If _AreDatesClose(test, range.StartDate) Then Return True
        If _AreDatesClose(test, range.EndDate) Then Return True
        Return False
    End Function

    Private Function _Expand(ByRef contiguous As DateRange, test As DateRange) As Boolean

        Dim shouldExpand = False

        ' date are close
        If _IsInRange(test.StartDate, contiguous) OrElse _IsInRange(test.EndDate, contiguous) Then
            shouldExpand = True
            ' startdate is in the middle of contiguous
        ElseIf test.StartDate >= contiguous.StartDate AndAlso test.StartDate <= contiguous.EndDate Then
            shouldExpand = True
            ' enddate is in the middle of contiguous
        ElseIf test.EndDate <= contiguous.EndDate AndAlso test.EndDate >= contiguous.StartDate Then
            shouldExpand = True
        End If

        If shouldExpand Then
            contiguous.StartDate = _MinDate(test.StartDate, contiguous.StartDate)
            contiguous.EndDate = _MaxDate(test.EndDate, contiguous.EndDate)
            Return True
        End If
        Return False

    End Function

    Private Function _MaxDate(first As Date, second As Date) As Date
        If first < second Then Return second
        Return first
    End Function

    Private Function _MinDate(first As Date, second As Date) As Date
        If first < second Then Return first
        Return second
    End Function

    Private Function _GetRanges() As List(Of DateRange)
        Dim ranges As New List(Of DateRange)
        For Each item As RepeaterItem In rptHistory.Items
            Dim txtFrom As TextBox = item.FindControl("txtFrom")
            Dim txtTo As TextBox = item.FindControl("txtTo")

            Dim EmployedFrom = Logic.Conversion.ToDate(txtFrom.Text)
            Dim EmployedTo = Logic.Conversion.ToDate(txtTo.Text)

            Dim range As New DateRange
            range.StartDate = EmployedFrom
            range.EndDate = EmployedTo
            ranges.Add(range)
        Next
        Return ranges
    End Function

    ' Removes from [ranges] and returns an expanded date range
    Private Function _GetExpanded(ranges As List(Of DateRange)) As DateRange
        
        Dim contiguous As New DateRange
        contiguous.StartDate = ranges(0).StartDate
        contiguous.EndDate = ranges(0).EndDate
        ranges.RemoveAt(0)
        
        RangeLoopStart:
        For i = 0 To ranges.Count - 1
            Dim range = ranges(i)
            If _Expand(contiguous, range) Then
                ranges.RemoveAt(i)
                GoTo RangeLoopStart
            End If
        Next

        Return contiguous

    End Function

    ' Returns true if employment history has gaps
    Private Function _HasGaps() As Boolean

        Dim ranges = _GetRanges()
        If ranges.Count = 0 Then
            Return False
        End If

        Dim contiguous = _GetExpanded(ranges)
        Return ranges.Count > 0

    End Function

    ' Inserts "Unemployed" into the employment gaps
    Private Sub _InsertUnemployment()

        Dim ranges = _GetRanges()

        ' Combined employment history into chunks
        Dim contiguous As New List(Of DateRange)
        While ranges.Count
            contiguous.Add(_GetExpanded(ranges))
        End While
        If contiguous.Count = 0 Then
            Exit Sub
        End If

        ' Sort by start date
        contiguous.Sort(Function(a, b) a.StartDate.CompareTo(b.StartDate))

        ' Build unemployment ranges
        Dim unemployment = New List(Of DateRange)
        While contiguous.Count > 1
            unemployment.Add(New DateRange() With {
                .StartDate = contiguous(0).EndDate,
                .EndDate = contiguous(1).StartDate})
            contiguous.RemoveAt(0)
        End While
        If contiguous.Last().EndDate <> NullDate Then
            unemployment.Add(New DateRange() With {
                .StartDate = contiguous.Last().EndDate,
                .EndDate = NullDate})
        End If

        ' Now save it
        Using connection As New SqlConnection(Logic.Database.connectionString)
            connection.Open()
            Using transaction = connection.BeginTransaction()

                Dim cmdSave As New SqlCommand("
                    INSERT INTO web_DriverApplicationEmploymentHistory
                    (AppId, CompanyName, Address, City, State, Zip, ContactName, Phone, Fax, EmployedFrom, EmployedTo, TotalMonths, PositionHeld, WasSubjectToFMCSR, WasSafetySensitive, ReasonForLeaving, LeavingExplanation)
                    VALUES (@appId, 'Unemployed', '', '', '', '', '', '', '', @EmployedFrom, @EmployedTo, @TotalMonths, '', 0, 0, '', '');",
                    connection, transaction)
                With cmdSave.Parameters
                    .AddWithValue("appId", Master.app.id)
                    .Add(New SqlParameter("EmployedFrom", SqlDbType.Date))
                    .Add(New SqlParameter("EmployedTo", SqlDbType.Date))
                    .Add(New SqlParameter("TotalMonths", SqlDbType.Int))
                End With

                For Each range In unemployment
                    cmdSave.Parameters("EmployedFrom").Value = range.StartDate
                    cmdSave.Parameters("EmployedTo").Value = range.EndDate
                    cmdSave.Parameters("TotalMonths").Value = DateDiff(DateInterval.Month, range.StartDate, If(range.EndDate = NullDate, Today, range.EndDate))
                    cmdSave.ExecuteNonQuery()
                Next

                transaction.Commit()
            End Using
        End Using

    End Sub

#End Region

#Region "Other internals"

    Private Function _HasEnteredLastItem() As Boolean
        Dim count = rptHistory.Items.Count
        If count = 0 Then
            Return True
        Else
            Dim item As RepeaterItem = rptHistory.Items(count - 1)
            Dim txtName As TextBox = item.FindControl("txtName")
            Return txtName.Text.Length > 0
        End If
    End Function

    Private Sub _Remove(itemId As Integer)
        Using connection As New SqlConnection(Logic.Database.connectionString)
            connection.Open()

            Dim cmd As New SqlCommand(
                "DELETE FROM web_DriverApplicationEmploymentHistory WHERE id=@itemId",
                connection)
            cmd.Parameters.AddWithValue("itemId", itemId)
            cmd.ExecuteNonQuery()

        End Using
    End Sub

#End Region

End Class