Imports System.Data.SqlClient
Imports CrystalDecisions.CrystalReports.Engine
Imports Logic.NullableDateExtensions
Imports ReportingClass

Public Class ViolationsCertificate
    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()
                _LoadList()
                signature.DefaultSigUrl = "/_assets/DefaultAppSig.aspx?appId=" & Master.app.id
            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 rptViolations.AddClick
        Try
            If _HasEnteredLastItem() Then
                rptViolations.TargetCount = Math.Max(rptViolations.TargetCount, rptViolations.Items.Count) + 1
            End If
            _LoadList()
        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
            
            rptViolations.TargetCount = Math.Max(rptViolations.TargetCount, rptViolations.Items.Count) - 1

            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

#Region "Loading internals"

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

            Dim cmdGet As New SqlCommand("
                SELECT applicantNameLast + ', ' + applicantNameFirst + ISNULL(' ' + applicantNameMiddle, '') + ISNULL(', ' + applicantNameSuffix, '') AS applicantName,
                    applicantSocial, ViolationsCertLicenseNumber, ViolationsCertLicenseState, ViolationsCertSignatureId, ViolationsCertHasViolations
                FROM web_DriverApplication WHERE id=@appId",
                connection)
            cmdGet.Parameters.AddWithValue("appId", Master.app.id)

            Dim dReader = cmdGet.ExecuteReader()
            dReader.Read()
            
            txtName.Text = Logic.Conversion.ToString(dReader("applicantName"))
            txtLicenseNumber.Text = Logic.Conversion.ToString(dReader("ViolationsCertLicenseNumber"))
            ddlState.SelectedValue = Logic.Conversion.ToString(dReader("ViolationsCertLicenseState"))
            signature.SignatureId = Logic.Conversion.ToNullableInteger(dReader("ViolationsCertSignatureId"))

            ' weird code for violations radio button list
            Dim hasViolations = Logic.Conversion.ToNullableBoolean(dReader("ViolationsCertHasViolations"))
            If hasViolations.HasValue Then
                rbViolations.SelectedValue = If(hasViolations.Value, "true", "false")
            End If
            
        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_DriverApplicationViolations WHERE AppId=@appId",
                connection)
            cmdCount.Parameters.AddWithValue("appId", Master.app.id)
            Dim count = CInt(cmdCount.ExecuteScalar())
            count = Math.Max(count, rptViolations.TargetCount)

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

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

            Dim dReader = cmdViolations.ExecuteReader()
            For Each item As RepeaterItem In rptViolations.Items
                Dim hidId As HiddenField = item.FindControl("hidId")
                Dim txtViolationDate As TextBox = item.FindControl("txtViolationDate")
                Dim txtViolationOffense As TextBox = item.FindControl("txtViolationOffense")
                Dim txtViolationLocation As TextBox = item.FindControl("txtViolationLocation")
                Dim txtViolationVehicleType As TextBox = item.FindControl("txtViolationVehicleType")

                If dReader.Read() Then
                    hidId.Value = dReader("id")
                    txtViolationDate.Text = Logic.Conversion.ToNullableDate(dReader("ViolationDate")).FormatForHtml()
                    txtViolationOffense.Text = dReader("Offense")
                    txtViolationLocation.Text = dReader("Location")
                    txtViolationVehicleType.Text = dReader("VehicleType")
                End If
            Next

        End Using
    End Sub

    Private Structure License
        Property Number As String
        Property State As String
    End Structure
    Private Structure Conviction
        Property ConvictionDate As Date
        Property State As String
        Property Charge As String
        Property Penalty As String
    End Structure
    Private Sub _Prefill()
        Using connection As New SqlConnection(Logic.Database.connectionString)
            connection.Open()

            ' Load the license info that we're prefilling from
            Dim currentLicense As New License
            Dim cmdGetLicense As New SqlCommand(
                "SELECT TOP 1 * FROM web_DriverApplicationLicenses WHERE AppId=@appId",
                connection)
            cmdGetLicense.Parameters.AddWithValue("appId", Master.app.id)
            Using dReader = cmdGetLicense.ExecuteReader()
                If dReader.Read() Then
                    currentLicense.Number = dReader("Number")
                    currentLicense.State = dReader("State")
                Else
                    currentLicense.Number = ""
                    currentLicense.State = ""
                End If
            End Using

            ' Load the convictions that we're prefilling from
            Dim convictions As New List(Of Conviction)
            Dim cmdGetConvictions As New SqlCommand(
                "SELECT * FROM web_DriverApplicationConvictions WHERE AppId=@appId",
                connection)
            cmdGetConvictions.Parameters.AddWithValue("appId", Master.app.id)
            Using dReader = cmdGetConvictions.ExecuteReader()
                While dReader.Read()
                    convictions.Add(New Conviction With {
                        .ConvictionDate = dReader("Date"),
                        .State = dReader("State"),
                        .Charge = dReader("Charge"),
                        .Penalty = dReader("Penalty")
                    })
                End While
            End Using

            ' Now can insert the data.
            ' We do this separately to guarantee the order is the same.
            Using transaction = connection.BeginTransaction()

                Dim cmdPrefillLicense As New SqlCommand("
                    UPDATE web_DriverApplication
                    SET ViolationsCertLicenseNumber=@number, ViolationsCertLicenseState=@state
                    WHERE id=@appId",
                    connection, transaction)
                cmdPrefillLicense.Parameters.AddWithValue("appId", Master.app.id)
                cmdPrefillLicense.Parameters.AddWithValue("number", currentLicense.Number)
                cmdPrefillLicense.Parameters.AddWithValue("state", currentLicense.State)
                cmdPrefillLicense.ExecuteNonQuery()

                Dim cmdPrefillConvictions As New SqlCommand("
                    INSERT INTO web_DriverApplicationViolations
                    (AppId, ViolationDate, Offense, Location, VehicleType)
                    VALUES (@appId, @date, @offense, @location, '')",
                    connection, transaction)
                With cmdPrefillConvictions.Parameters
                    .AddWithValue("appId", Master.app.id)
                    .Add(New SqlParameter("date", SqlDbType.Date))
                    .Add(New SqlParameter("offense", SqlDbType.VarChar))
                    .Add(New SqlParameter("location", SqlDbType.VarChar))
                End With

                For Each item In convictions
                    cmdPrefillConvictions.Parameters("date").Value = item.ConvictionDate
                    cmdPrefillConvictions.Parameters("offense").Value = item.Charge
                    cmdPrefillConvictions.Parameters("location").Value = item.State
                    cmdPrefillConvictions.ExecuteNonQuery()
                Next

                transaction.Commit()
            End Using

        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 sigId = signature.SignatureId
        Dim hasViolations = Logic.Conversion.ToNullableBoolean(rbViolations.SelectedValue)

        Dim cmd As New SqlCommand("
            UPDATE web_DriverApplication
            SET ViolationsCertLicenseNumber=@licenseNumber,
                ViolationsCertLicenseState=@licenseState,
                ViolationsCertSignatureId=@sigId,
                ViolationsCertHasViolations=@hasViolations
            WHERE id=@appId",
            connection, transaction)
        With cmd.Parameters
            .AddWithValue("appId", Master.app.id)
            .AddWithValue("licenseNumber", txtLicenseNumber.Text)
            .AddWithValue("licenseState", ddlState.SelectedValue)
            .AddWithValue("sigId", If(sigId.HasValue, sigId.Value, DBNull.Value))
            .AddWithValue("hasViolations", If(hasViolations.HasValue, hasViolations.Value, DBNull.Value))
        End With
        cmd.ExecuteNonQuery()

    End Sub

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

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

        Dim cmdSave As New SqlCommand("
            INSERT INTO web_DriverApplicationViolations
            (AppId, ViolationDate, Offense, Location, VehicleType)
            VALUES (@appId, @violationDate, @offense, @location, @vehicleType);
            SELECT @@IDENTITY;",
            connection, transaction)
        With cmdSave.Parameters
            .AddWithValue("appId", Master.app.id)
            .Add(New SqlParameter("violationDate", SqlDbType.VarChar))
            .Add(New SqlParameter("offense", SqlDbType.VarChar))
            .Add(New SqlParameter("location", SqlDbType.VarChar))
            .Add(New SqlParameter("vehicleType", SqlDbType.VarChar))
        End With

        For Each item As RepeaterItem In rptViolations.Items
            Dim hidId As HiddenField = item.FindControl("hidId")
            Dim txtViolationDate As TextBox = item.FindControl("txtViolationDate")
            Dim txtViolationOffense As TextBox = item.FindControl("txtViolationOffense")
            Dim txtViolationLocation As TextBox = item.FindControl("txtViolationLocation")
            Dim txtViolationVehicleType As TextBox = item.FindControl("txtViolationVehicleType")

            Dim ViolationDate = Logic.Conversion.ToNullableDate(txtViolationDate.Text)
            
            cmdSave.Parameters("violationDate").Value = If(ViolationDate.HasValue, ViolationDate.Value, DBNull.Value)
            cmdSave.Parameters("offense").Value = Logic.Conversion.ToString(txtViolationOffense.Text)
            cmdSave.Parameters("location").Value = Logic.Conversion.ToString(txtViolationLocation.Text)
            cmdSave.Parameters("vehicleType").Value = Logic.Conversion.ToString(txtViolationVehicleType.Text)

            hidId.Value = cmdSave.ExecuteScalar()
        Next

    End Sub

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

        Dim rawData = If(rptViolations.Items.Count,
            _GetDocumentWithViolations(connection, transaction),
            _GetDocumentWithoutViolations(connection, transaction))

        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 Sub

    Private Function _GetDocumentWithoutViolations(connection, transaction) As Byte()
        Dim ReportDataSet As New dsAppViolationsCertificate
        Dim ReportTableAdapter As New TransactionalAppNoViolationsCertificateTableAdapter
        ReportTableAdapter.SetConnection(connection, transaction)
        ReportTableAdapter.Fill(ReportDataSet.AppNoViolationsCertificateDataTable, Master.app.id)

        Using Report As New crptAppViolationsCertificate_Empty
            Report.SetDataSource(ReportDataSet)

            Dim PictureBox As PictureObject = Report.ReportDefinition.ReportObjects("picSig")
            Dim LinkedFormula As FormulaFieldDefinition = Report.DataDefinition.FormulaFields("pathSig")
            Dim SignatureId = Logic.Conversion.ToInteger(ReportDataSet.AppNoViolationsCertificateDataTable.Rows(0)("sigId"))
            Reporting.SetPictureToSignature(PictureBox, LinkedFormula, SignatureId, 5600)

            Return Reporting.StreamToByteArray(Report)
        End Using
    End Function

    Private Function _GetDocumentWithViolations(connection, transaction) As Byte()
        Dim ReportDataSet As New dsAppViolationsCertificate
        Dim ReportTableAdapter As New TransactionalAppViolationsCertificateTableAdapter
        ReportTableAdapter.SetConnection(connection, transaction)
        ReportTableAdapter.Fill(ReportDataSet.AppViolationsCertificateDataTable, Master.app.id)

        Using Report As New crptAppViolationsCertificate
            Report.SetDataSource(ReportDataSet)

            Dim PictureBox As PictureObject = Report.ReportDefinition.ReportObjects("picSig")
            Dim LinkedFormula As FormulaFieldDefinition = Report.DataDefinition.FormulaFields("pathSig")
            Dim SignatureId = Logic.Conversion.ToInteger(ReportDataSet.AppViolationsCertificateDataTable.Rows(0)("sigId"))
            Reporting.SetPictureToSignature(PictureBox, LinkedFormula, SignatureId, 5600)

            Return Reporting.StreamToByteArray(Report)
        End Using
    End Function

#End Region

#Region "Other internals"

    Private Function _HasEnteredLastItem() As Boolean
        Dim count = rptViolations.Items.Count
        If count = 0 Then
            Return True
        Else
            Dim item As RepeaterItem = rptViolations.Items(count - 1)
            Dim txtViolationDate As TextBox = item.FindControl("txtViolationDate")
            Return txtLicenseNumber.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 ViolationsCertLicenseNumber 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 _Remove(itemId As Integer)
        Using connection As New SqlConnection(Logic.Database.connectionString)
            connection.Open()
            Dim cmd As New SqlCommand(
                "DELETE FROM web_DriverApplicationViolations WHERE id=@itemId",
                connection)
            cmd.Parameters.AddWithValue("itemId", itemId)
            cmd.ExecuteNonQuery()
        End Using
    End Sub

#End Region

End Class