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


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

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

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

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

            _RemoveTraining(CInt(hidId.Value))
            _LoadTrainingList()

        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 hasHadTraining
                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"))
            ynDriverTraining.Checked = Logic.Conversion.ToNullableBoolean(dReader("hasHadTraining"))
            'txtLicenseDeniedExplanation.Text = Logic.Conversion.ToString(dReader("licenseDeniedExplanation"))
            'txtLicenseRevokedExplanation.Text = Logic.Conversion.ToString(dReader("licenseRevokedExplanation"))

        End Using
    End Sub

    Private Sub _LoadLists()

        _LoadTrainingList()
    End Sub



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

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

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

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

            Dim dReader = cmdTraining.ExecuteReader()
            For Each item As RepeaterItem In rptTraining.Items
                Dim hidId As HiddenField = item.FindControl("hidTrainingId")
                Dim txtStartDate As TextBox = item.FindControl("txtStartDate")
                Dim txtEndDate As TextBox = item.FindControl("txtEndDate")
                Dim ddlTrainingState As StatePicker = item.FindControl("ddlTrainingState")
                Dim txtSchoolName As TextBox = item.FindControl("txtSchoolName")
                Dim txtTrainingCity As TextBox = item.FindControl("txtTrainingCity")
                Dim txtPhoneNumber As TextBox = item.FindControl("txtPhone")
                Dim ynGraduate As YesNoPicker = item.FindControl("ynGraduate")
                Dim ynSafetyReg As YesNoPicker = item.FindControl("ynSafetyReg")
                Dim ynSafetyFunctions As YesNoPicker = item.FindControl("ynSafetyFunctions")
                Dim txtGPA As TextBox = item.FindControl("txtGPA")
                Dim ddlHours As DropDownList = item.FindControl("ddlHours")
                Dim chkBorder As CheckBox = item.FindControl("chkBorderCrossing")
                Dim chkLog As CheckBox = item.FindControl("chkLogBooks")
                Dim chkFMCR As CheckBox = item.FindControl("chkFMCR")
                Dim chkHaz As CheckBox = item.FindControl("chkHaz")

                If dReader.Read() Then
                    hidId.Value = dReader("id")
                    txtStartDate.Text = Logic.Conversion.ToDate(dReader("StartDate")).FormatForHtml5()
                    txtEndDate.Text = Logic.Conversion.ToDate(dReader("EndDate")).FormatForHtml5()
                    ddlTrainingState.SelectedValue = dReader("TrainingState")
                    txtSchoolName.Text = dReader("SchoolName")
                    txtTrainingCity.Text = dReader("TrainingCity")
                    txtPhoneNumber.Text = dReader("Phone")
                    ynGraduate.Checked = Logic.Conversion.ToNullableBoolean(dReader("Graduated"))
                    ynSafetyReg.Checked = Logic.Conversion.ToNullableBoolean(dReader("SafetyReg"))
                    ynSafetyFunctions.Checked = Logic.Conversion.ToNullableBoolean(dReader("SafetyFunctions"))
                    txtGPA.Text = dReader("GPA")
                    ddlHours.SelectedValue = dReader("Hours")
                    chkBorder.Checked = dReader("BorderCrossing")
                    chkLog.Checked = dReader("LogBooks")
                    chkFMCR.Checked = dReader("FMCR")
                    chkHaz.Checked = dReader("HazardousMaterials")
                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 hadTraining = ynDriverTraining.Checked

        Dim cmdSave As New SqlCommand("
            UPDATE web_DriverApplication
            SET hasHadTraining=@hasHadTraining
            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("hasHadTraining", If(hadTraining.HasValue, hadTraining.Value, DBNull.Value))
        End With

        cmdSave.ExecuteNonQuery()

    End Sub

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


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

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

        Dim cmdSave As New SqlCommand("
            INSERT INTO web_DriverApplicationTraining
            (AppId, StartDate,EndDate, TrainingState, SchoolName,TrainingCity,Phone,Graduated,SafetyReg,SafetyFunctions,GPA,Hours,BorderCrossing,LogBooks,FMCR,HazardousMaterials)
            VALUES (@appId, @StartDate,@EndDate, @TrainingState, @SchoolName,@TrainingCity,@Phone,@Graduated,@SafetyReg,@SafetyFunctions,@GPA,@Hours,@BorderCrossing,@LogBooks,@FMCR,@HazardousMaterials);
            SELECT @@IDENTITY;",
            connection, transaction)
        With cmdSave.Parameters
            .AddWithValue("appId", Master.app.id)
            .Add(New SqlParameter("StartDate", SqlDbType.VarChar))
            .Add(New SqlParameter("EndDate", SqlDbType.VarChar))
            .Add(New SqlParameter("TrainingState", SqlDbType.VarChar))
            .Add(New SqlParameter("SchoolName", SqlDbType.VarChar))
            .Add(New SqlParameter("TrainingCity", SqlDbType.VarChar))
            .Add(New SqlParameter("Phone", SqlDbType.VarChar))
            .Add(New SqlParameter("Graduated", SqlDbType.VarChar))
            .Add(New SqlParameter("SafetyReg", SqlDbType.VarChar))
            .Add(New SqlParameter("SafetyFunctions", SqlDbType.VarChar))
            .Add(New SqlParameter("GPA", SqlDbType.VarChar))
            .Add(New SqlParameter("Hours", SqlDbType.VarChar))
            .Add(New SqlParameter("BorderCrossing", SqlDbType.VarChar))
            .Add(New SqlParameter("LogBooks", SqlDbType.VarChar))
            .Add(New SqlParameter("FMCR", SqlDbType.VarChar))
            .Add(New SqlParameter("HazardousMaterials", SqlDbType.VarChar))
        End With

        For Each item As RepeaterItem In rptTraining.Items
            Dim hidId As HiddenField = item.FindControl("hidTrainingId")
            Dim txtStartDate As TextBox = item.FindControl("txtStartDate")
            Dim txtEndDate As TextBox = item.FindControl("txtEndDate")
            Dim ddlTrainingState As StatePicker = item.FindControl("ddlTrainingState")
            Dim txtSchoolName As TextBox = item.FindControl("txtSchoolName")
            Dim txtTrainingCity As TextBox = item.FindControl("txtTrainingCity")
            Dim txtPhoneNumber As TextBox = item.FindControl("txtPhone")
            Dim ynGraduate As YesNoPicker = item.FindControl("ynGraduate")
            Dim ynSafetyReg As YesNoPicker = item.FindControl("ynSafetyReg")
            Dim ynSafetyFunctions As YesNoPicker = item.FindControl("ynSafetyFunctions")
            Dim txtGPA As TextBox = item.FindControl("txtGPA")
            Dim ddlHours As DropDownList = item.FindControl("ddlHours")
            Dim chkBorder As CheckBox = item.FindControl("chkBorderCrossing")
            Dim chkLog As CheckBox = item.FindControl("chkLogBooks")
            Dim chkFMCR As CheckBox = item.FindControl("chkFMCR")
            Dim chkHaz As CheckBox = item.FindControl("chkHaz")

            cmdSave.Parameters("StartDate").Value = Logic.Conversion.ToDate(txtStartDate.Text).Clip()
            cmdSave.Parameters("EndDate").Value = Logic.Conversion.ToDate(txtEndDate.Text).Clip()
            cmdSave.Parameters("TrainingState").Value = ddlTrainingState.SelectedValue
            cmdSave.Parameters("SchoolName").Value = txtSchoolName.Text
            cmdSave.Parameters("TrainingCity").Value = txtTrainingCity.Text
            cmdSave.Parameters("Phone").Value = txtPhoneNumber.Text
            cmdSave.Parameters("Graduated").Value = ynGraduate.Checked
            cmdSave.Parameters("SafetyReg").Value = ynSafetyReg.Checked
            cmdSave.Parameters("SafetyFunctions").Value = ynSafetyFunctions.Checked
            cmdSave.Parameters("GPA").Value = txtGPA.Text
            cmdSave.Parameters("Hours").Value = ddlHours.SelectedValue
            cmdSave.Parameters("BorderCrossing").Value = chkBorder.Checked
            cmdSave.Parameters("LogBooks").Value = chkLog.Checked
            cmdSave.Parameters("FMCR").Value = chkFMCR.Checked
            cmdSave.Parameters("HazardousMaterials").Value = chkHaz.Checked

            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 _HasEnteredLastTraining() As Boolean
        Dim count = rptTraining.Items.Count
        If count = 0 Then
            Return True
        Else
            Dim item As RepeaterItem = rptTraining.Items(count - 1)
            Dim txtStartDate As TextBox = item.FindControl("txtStartDate")
            Return txtStartDate.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 hasHadTraining 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 _RemoveTraining(itemId As Integer)
        Using connection As New SqlConnection(Logic.Database.ConnectionString)
            connection.Open()
            Dim cmd As New SqlCommand(
                "DELETE FROM web_DriverApplicationTraining WHERE id=@itemId",
                connection)
            cmd.Parameters.AddWithValue("itemId", itemId)
            cmd.ExecuteNonQuery()
        End Using
    End Sub

#End Region

End Class