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

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

    Protected Sub btnRemoveVehicle_Click(sender As Object, e As EventArgs)
        Try

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

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

            _RemoveVehicle(CInt(hidId.Value))
            _LoadVehicleList()

        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 hasBeenDeniedLicense, licenseDeniedExplanation, 
                    hasHadLicenseRevoked, licenseRevokedExplanation,
                    hasHadAccidents, hasHadConvictions
                FROM web_DriverApplication WHERE id=@appId",
                connection)
            cmd.Parameters.AddWithValue("appId", Master.app.id)
            Dim dReader = cmd.ExecuteReader()
            dReader.Read()

            'ynLicenseDenied.Checked = Logic.Conversion.ToNullableBoolean(dReader("hasBeenDeniedLicense"))
            'ynLicenseRevoked.Checked = Logic.Conversion.ToNullableBoolean(dReader("hasHadLicenseRevoked"))
            'ynThreeYearsAccidents.Checked = Logic.Conversion.ToNullableBoolean(dReader("hasHadAccidents"))
            'ynThreeYearsConvictions.Checked = Logic.Conversion.ToNullableBoolean(dReader("hasHadConvictions"))
            'txtLicenseDeniedExplanation.Text = Logic.Conversion.ToString(dReader("licenseDeniedExplanation"))
            'txtLicenseRevokedExplanation.Text = Logic.Conversion.ToString(dReader("licenseRevokedExplanation"))

        End Using
    End Sub

    Private Sub _LoadLists()
        _LoadVehicleList()
    End Sub



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

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

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

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

            Dim dReader = cmdVehicles.ExecuteReader()
            For Each item As RepeaterItem In rptVehicles.Items
                Dim hidId As HiddenField = item.FindControl("hidVehicleId")
                Dim ddlEquipmentType As DropDownList = item.FindControl("ddlEquipmentType")
                Dim txtEquipmentType As TextBox = item.FindControl("txtEquipmentType")
                Dim txtEquipmentYears As TextBox = item.FindControl("txtEquipmentYears")
                Dim txtEquipmentMiles As TextBox = item.FindControl("txtEquipmentMiles")
                Dim ynRequiredCDL As YesNoPicker = item.FindControl("ynRequiredCDL")
                Dim litId_ddlEquipmentType As Literal = item.FindControl("litId_ddlEquipmentType")

                litId_ddlEquipmentType.Text = ddlEquipmentType.ClientID

                If dReader.Read() Then

                    hidId.Value = dReader("id")
                    txtEquipmentYears.Text = dReader("Years")
                    txtEquipmentMiles.Text = dReader("Miles")
                    ynRequiredCDL.Checked = Logic.Conversion.ToNullableBoolean(dReader("RequiredCDL"))

                    Dim EquipmentType = Logic.Conversion.ToString(dReader("Type"))
                    If EquipmentType.Length Then
                        If ddlEquipmentType.Items.FindByValue(EquipmentType) IsNot Nothing Then
                            ddlEquipmentType.SelectedValue = EquipmentType
                        Else
                            ddlEquipmentType.SelectedValue = "Other"
                            txtEquipmentType.Text = EquipmentType
                        End If
                    End If

                End If
            Next

        End Using
    End Sub

    Private Sub _Prefill()
        '
    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 licenseDenied = ynLicenseDenied.Checked
        'Dim licenseRevoked = ynLicenseRevoked.Checked
        'Dim hadAccidents = ynThreeYearsAccidents.Checked
        'Dim hadConvictions = ynThreeYearsConvictions.Checked

        'Dim cmdSave As New SqlCommand("
        '    UPDATE web_DriverApplication
        '    SET hasBeenDeniedLicense=@hasBeenDeniedLicense, licenseDeniedExplanation=@licenseDeniedExplanation,
        '        hasHadLicenseRevoked=@hasHadLicenseRevoked, licenseRevokedExplanation=@licenseRevokedExplanation,
        '        hasHadConvictions=@hasHadConvictions, hasHadAccidents=@hasHadAccidents
        '    WHERE id=@appId",
        '    connection, transaction)

        'With cmdSave.Parameters
        '    .AddWithValue("appId", Master.app.id)
        '    .AddWithValue("hasBeenDeniedLicense", If(licenseDenied.HasValue, licenseDenied.Value, DBNull.Value))
        '    .AddWithValue("licenseDeniedExplanation", If(licenseDenied.HasValue, txtLicenseDeniedExplanation.Text, DBNull.Value))
        '    .AddWithValue("hasHadLicenseRevoked", If(licenseRevoked.HasValue, licenseRevoked.Value, DBNull.Value))
        '    .AddWithValue("licenseRevokedExplanation", If(licenseRevoked.HasValue, txtLicenseRevokedExplanation.Text, DBNull.Value))
        '    .AddWithValue("hasHadAccidents", If(hadAccidents.HasValue, hadAccidents.Value, DBNull.Value))
        '    .AddWithValue("hasHadConvictions", If(hadConvictions.HasValue, hadConvictions.Value, DBNull.Value))
        'End With

        'cmdSave.ExecuteNonQuery()

    End Sub

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


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

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

        Dim cmdSave As New SqlCommand("
            INSERT INTO web_DriverApplicationVehicles
            (AppId, Type, Years, Miles, RequiredCDL)
            VALUES (@appId, @type, @years, @miles, @cdl);
            SELECT @@IDENTITY;",
            connection, transaction)
        With cmdSave.Parameters
            .AddWithValue("appId", Master.app.id)
            .Add(New SqlParameter("type", SqlDbType.VarChar))
            .Add(New SqlParameter("years", SqlDbType.Int))
            .Add(New SqlParameter("miles", SqlDbType.Int))
            .Add(New SqlParameter("cdl", SqlDbType.Bit)).IsNullable = True
        End With

        For Each item As RepeaterItem In rptVehicles.Items
            Dim hidId As HiddenField = item.FindControl("hidVehicleId")
            Dim ddlEquipmentType As DropDownList = item.FindControl("ddlEquipmentType")
            Dim txtEquipmentType As TextBox = item.FindControl("txtEquipmentType")
            Dim txtEquipmentYears As TextBox = item.FindControl("txtEquipmentYears")
            Dim txtEquipmentMiles As TextBox = item.FindControl("txtEquipmentMiles")
            Dim ynRequiredCDL As YesNoPicker = item.FindControl("ynRequiredCDL")

            Dim EquipmentType = ddlEquipmentType.SelectedValue
            If EquipmentType = "Other" Then
                EquipmentType = txtEquipmentType.Text
            End If

            Dim cdl = ynRequiredCDL.Checked

            cmdSave.Parameters("type").Value = EquipmentType
            cmdSave.Parameters("years").Value = Logic.Conversion.ToInteger(txtEquipmentYears.Text)
            cmdSave.Parameters("miles").Value = Logic.Conversion.ToInteger(txtEquipmentMiles.Text)
            cmdSave.Parameters("cdl").Value = If(cdl.HasValue, cdl.Value, DBNull.Value)

            hidId.Value = cmdSave.ExecuteScalar()
        Next

    End Sub

    Private Sub _SaveDocument(connection As SqlConnection, transaction As SqlTransaction)
        Dim ReportDataSet As New dsAppDrivingExperience
        Dim ReportTableAdapter As New TransactionalAppDrivingExperienceTableAdapter
        ReportTableAdapter.SetConnection(connection, transaction)
        ReportTableAdapter.Fill(ReportDataSet.AppDrivingExperienceDataTable, Master.app.id)

        Using Report As New crptAppDrivingExperience
            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 _HasEnteredLastVehicle() As Boolean
        Dim count = rptVehicles.Items.Count
        If count = 0 Then
            Return True
        Else
            Dim item As RepeaterItem = rptVehicles.Items(count - 1)
            Dim ddlEquipmentType As DropDownList = item.FindControl("ddlEquipmentType")
            Dim txtEquipmentType As TextBox = item.FindControl("txtEquipmentType")
            If ddlEquipmentType.Text = "Other" And txtEquipmentType.Text.Length = 0 Then
                Return False
            End If
            Return True
        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 hasHadConvictions 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 _RemoveVehicle(itemId As Integer)
        Using connection As New SqlConnection(Logic.Database.ConnectionString)
            connection.Open()
            Dim cmd As New SqlCommand(
                "DELETE FROM web_DriverApplicationVehicles WHERE id=@itemId",
                connection)
            cmd.Parameters.AddWithValue("itemId", itemId)
            cmd.ExecuteNonQuery()
        End Using
    End Sub


#End Region

End Class