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

Public Class AgreementCBC
    Inherits System.Web.UI.Page

    Private Sub Page_Load() Handles Me.Load
        Try
            If Not Page.IsPostBack Then
                _LoadForm()
                _LoadAddressList()
                _LoadNameList()

                '     sigConsent.DefaultSigUrl = "/_assets/DefaultAppSig.aspx?appId=" & Master.app.id
                '     sigBackground.DefaultSigUrl = "/_assets/DefaultAppSig.aspx?appId=" & Master.app.id

            Else
                _SaveForm()
                _SaveAddressList()
                _SaveNameList()

                Page.Validate()
                If Page.IsValid Then
                    Master.EnableNextPage()
                End If

                _SaveDocument()
            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() Handles btnAddAddress.Click
        Try
            If _HasEnteredLastAddress() Then
                _LoadAddressList(rptAddresses.Items.Count + 1)
            Else
                _LoadAddressList()
            End If
        Catch ex As Exception
            Dim errorId = Logic.Error.Log(ex)
            Response.Redirect("/Error.aspx?id=" & errorId)
        End Try
    End Sub

    Private Sub btnAddName_Click() Handles btnAddName.Click
        Try
            If _HasEnteredLastName() Then
                _LoadNameList(rptFormerNames.Items.Count + 1)
            Else
                _LoadNameList()
            End If
        Catch ex As Exception
            Dim errorId = Logic.Error.Log(ex)
            Response.Redirect("/Error.aspx?id=" & errorId)
        End Try
    End Sub

    Protected Sub btnRemoveName_Click(sender As Object, e As EventArgs)
        Try
            Dim btnRemove = DirectCast(sender, LinkButton)
            Dim item = DirectCast(btnRemove.Parent, RepeaterItem)
            Dim hidId = DirectCast(item.FindControl("hidNameId"), HiddenField)

            Using connection As New SqlConnection(Logic.Database.ConnectionString)
                connection.Open()
                Dim cmd As New SqlCommand("DELETE FROM web_driverApplicationCBCNames WHERE id=@itemId", connection)
                cmd.Parameters.AddWithValue("itemId", hidId.Value)
                cmd.ExecuteNonQuery()
            End Using

            _LoadNameList()

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

    Protected Sub btnRemoveAddress_Click(sender As Object, e As EventArgs)
        Try
            Dim btnRemove = DirectCast(sender, LinkButton)
            Dim item = DirectCast(btnRemove.Parent, RepeaterItem)
            Dim hidId = DirectCast(item.FindControl("hidAddressId"), HiddenField)

            Using connection As New SqlConnection(Logic.Database.ConnectionString)
                connection.Open()
                Dim cmd As New SqlCommand("DELETE FROM web_driverApplicationCBCAddresses WHERE id=@itemId", connection)
                cmd.Parameters.AddWithValue("itemId", hidId.Value)
                cmd.ExecuteNonQuery()
            End Using

            _LoadAddressList()

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


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

            Dim cmd As New SqlCommand(
                "SELECT applicantNameLast + ', ' + applicantNameFirst + ISNULL(' ' + applicantNameMiddle, '') + ISNULL(', ' + applicantNameSuffix, '') AS applicantName,
                    CBCSocial,CBCBirth,CBCGender,CBCLicenseNumber,CBCLicenseState,
                    applicantSocial defSocial, applicantDob defDob, license1Number defLicenceNumber, license1State defLicenseState
                    ,Agreement_CBC1,Agreement_CBC2
                FROM web_DriverApplication WHERE id=@appId", connection)
            cmd.Parameters.AddWithValue("appId", Master.app.id)

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

            txtName.Text = Logic.Conversion.ToString(dReader("applicantName"))
            If TypeOf dReader("CBCSocial") Is DBNull Then
                txtSocial.Value = Logic.Conversion.ToString(dReader("defSocial"))
                dtBirth.Text = Logic.Conversion.ToDate(dReader("defDob")).FormatForHtml5()
                txtLicenseNumber.Text = Logic.Conversion.ToString(dReader("defLicenceNumber"))
                ddlLicenseState.SelectedValue = Logic.Conversion.ToString(dReader("defLicenseState"))
            Else
                txtSocial.Value = Logic.Conversion.ToString(dReader("CBCSocial"))
                dtBirth.Text = Logic.Conversion.ToDate(dReader("CBCBirth")).FormatForHtml5()
                ddlGender.SelectedValue = Logic.Conversion.ToString(dReader("CBCGender"))
                txtLicenseNumber.Text = Logic.Conversion.ToString(dReader("CBCLicenseNumber"))
                ddlLicenseState.SelectedValue = Logic.Conversion.ToString(dReader("CBCLicenseState"))
                'sigConsent.SignatureId = Logic.Conversion.ToNullableInteger(dReader("CBCSigConsentId"))
                'sigBackground.SignatureId = Logic.Conversion.ToNullableInteger(dReader("CBCSigBackgroundId"))
                ynAgree.Checked = Logic.Conversion.ToNullableBoolean(dReader("Agreement_CBC1"))
                ynAgree2.Checked = Logic.Conversion.ToNullableBoolean(dReader("Agreement_CBC2"))

            End If

        End Using
    End Sub

    Private Sub _LoadAddressList(Optional minCount As Integer = 1)
        Using connection As New SqlConnection(Logic.Database.ConnectionString)
            connection.Open()
            Dim cmd As New SqlCommand("", connection)
            cmd.Parameters.AddWithValue("appId", Master.app.id)

            cmd.CommandText = "SELECT COUNT(id) FROM web_driverApplicationCBCAddresses WHERE appId=@appId"
            Dim count = Math.Max(minCount, CInt(cmd.ExecuteScalar()))

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

            cmd.CommandText =
                "SELECT * FROM web_driverApplicationCBCAddresses
                WHERE appId=@appId ORDER BY ISNULL(dateUntil,'9/9/2999') DESC"
            Dim dReader = cmd.ExecuteReader()
            For Each item As RepeaterItem In rptAddresses.Items
                Dim hidId As HiddenField = item.FindControl("hidAddressId")
                Dim txtAddress As TextBox = item.FindControl("txtAddress")
                Dim txtCity As TextBox = item.FindControl("txtCity")
                Dim ddlState As StatePicker = item.FindControl("ddlState")
                Dim txtZip As TextBox = item.FindControl("txtZip")
                Dim dtUntil As TextBox = item.FindControl("dtUntil")

                ddlState.Rebind()

                If dReader.Read() Then
                    Dim dateUntil = Logic.Conversion.ToNullableDate(dReader("dateUntil"))

                    hidId.Value = dReader("id")
                    txtAddress.Text = dReader("address")
                    txtCity.Text = dReader("city")
                    ddlState.SelectedValue = dReader("state")
                    txtZip.Text = dReader("zip")
                    dtUntil.Text = dateUntil.FormatForHtml()

                ElseIf rptAddresses.Items.Count = 1 Then
                    dReader.Close()
                    cmd.CommandText =
                        "SELECT applicantAddress,applicantCity,applicantState,applicantZip 
                        FROM web_driverApplication WHERE id=@appId"
                    dReader = cmd.ExecuteReader()
                    dReader.Read()
                    txtAddress.Text = dReader("applicantAddress")
                    txtCity.Text = dReader("applicantCity")
                    ddlState.SelectedValue = dReader("applicantState")
                    txtZip.Text = dReader("applicantZip")
                End If
            Next

            If rptAddresses.Items.Count > 0 Then
                Dim item = rptAddresses.Items(0)
                Dim valUntil As RequiredFieldValidator = item.FindControl("valUntil")
                valUntil.Enabled = False
            End If

        End Using
    End Sub

    Private Sub _LoadNameList(Optional minCount As Integer = 0)
        Using connection As New SqlConnection(Logic.Database.ConnectionString)
            connection.Open()
            Dim cmd As New SqlCommand("", connection)
            cmd.Parameters.AddWithValue("appId", Master.app.id)

            cmd.CommandText = "SELECT COUNT(id) FROM web_driverApplicationCBCNames WHERE appId=@appId"
            Dim count = Math.Max(minCount, CInt(cmd.ExecuteScalar()))

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

            cmd.CommandText =
                "SELECT * FROM web_driverApplicationCBCNames
                WHERE appId=@appId ORDER BY dateChanged DESC"
            Dim dReader = cmd.ExecuteReader()
            For Each item As RepeaterItem In rptFormerNames.Items
                Dim hidId As HiddenField = item.FindControl("hidNameId")
                Dim txtName As TextBox = item.FindControl("txtName")
                Dim dtName As TextBox = item.FindControl("dtName")

                If dReader.Read() Then
                    Dim dateChanged = Logic.Conversion.ToNullableDate(dReader("dateChanged"))

                    hidId.Value = dReader("id")
                    txtName.Text = dReader("name")
                    dtName.Text = dateChanged.FormatForHtml()
                End If
            Next

        End Using
    End Sub

    Private Sub _SaveForm()
        'Dim sigConsentId = If(sigConsent.SignatureId.HasValue, sigConsent.SignatureId.Value, DBNull.Value)
        'Dim sigBackgroundId = If(sigBackground.SignatureId.HasValue, sigBackground.SignatureId.Value, DBNull.Value)

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

            Dim Agree = ynAgree.Checked
            Dim Agree2 = ynAgree2.Checked


            Dim cmd As New SqlCommand(
                "UPDATE web_DriverApplication
                SET CBCSocial=@social, CBCBirth=@birth, CBCGender=@gender,
                    CBCLicenseNumber=@licenseNumber, CBCLicenseState=@licenseState,
                    Agreement_CBC1=@Agreement_CBC1, Agreement_CBC2=@Agreement_CBC2
                WHERE id=@appId", connection)
            cmd.Parameters.AddRange({
                New SqlParameter("appId", Master.app.id),
                New SqlParameter("social", txtSocial.Value),
                New SqlParameter("birth", Logic.Conversion.ToDate(dtBirth.Text)),
                New SqlParameter("gender", ddlGender.SelectedValue),
                New SqlParameter("licenseNumber", txtLicenseNumber.Text),
                New SqlParameter("licenseState", ddlLicenseState.SelectedValue),
                New SqlParameter("Agreement_CBC1", If(Agree.HasValue, Agree.Value, DBNull.Value)),
                New SqlParameter("Agreement_CBC2", If(Agree.HasValue, Agree.Value, DBNull.Value))
                })
            cmd.ExecuteNonQuery()
            'New SqlParameter("sigConsentId", sigConsentId),
            'New SqlParameter("sigBackgroundId", sigBackgroundId)

        End Using
    End Sub

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

            Dim cmd As New SqlCommand("", connection)
            cmd.Parameters.AddWithValue("appId", Master.app.id)

            cmd.CommandText = "DELETE FROM web_driverApplicationCBCAddresses WHERE appId=@appId"
            cmd.ExecuteNonQuery()

            cmd.CommandText =
                "INSERT INTO web_driverApplicationCBCAddresses (appId, address, city, state, zip, dateUntil)
                VALUES (@appId, @address, @city, @state, @zip, @dateUntil);
                SELECT @@IDENTITY;"
            cmd.Parameters.AddRange({
                New SqlParameter("address", SqlDbType.VarChar),
                New SqlParameter("city", SqlDbType.VarChar),
                New SqlParameter("state", SqlDbType.VarChar),
                New SqlParameter("zip", SqlDbType.VarChar),
                New SqlParameter("dateUntil", SqlDbType.Date)
                })
            For Each item As RepeaterItem In rptAddresses.Items
                Dim hidId As HiddenField = item.FindControl("hidAddressId")
                Dim txtAddress As TextBox = item.FindControl("txtAddress")
                Dim txtCity As TextBox = item.FindControl("txtCity")
                Dim ddlState As StatePicker = item.FindControl("ddlState")
                Dim txtZip As TextBox = item.FindControl("txtZip")
                Dim dtUntil As TextBox = item.FindControl("dtUntil")

                Dim dateUntil = Logic.Conversion.ToNullableDate(dtUntil.Text)

                cmd.Parameters("address").Value = txtAddress.Text
                cmd.Parameters("city").Value = txtCity.Text
                cmd.Parameters("state").Value = ddlState.SelectedValue
                cmd.Parameters("zip").Value = txtZip.Text
                cmd.Parameters("dateUntil").Value = If(dateUntil.HasValue, dateUntil.Value, DBNull.Value)

                hidId.Value = cmd.ExecuteScalar()
            Next
        End Using
    End Sub

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

            Dim cmd As New SqlCommand("", connection)
            cmd.Parameters.AddWithValue("appId", Master.app.id)

            cmd.CommandText = "DELETE FROM web_driverApplicationCBCNames WHERE appId=@appId"
            cmd.ExecuteNonQuery()

            cmd.CommandText =
                "INSERT INTO web_driverApplicationCBCNames (appId, name, dateChanged)
                VALUES (@appId, @name, @dateChanged);
                SELECT @@IDENTITY;"
            cmd.Parameters.AddRange({
                New SqlParameter("name", SqlDbType.VarChar),
                New SqlParameter("dateChanged", SqlDbType.Date)
                })
            For Each item As RepeaterItem In rptFormerNames.Items
                Dim hidId As HiddenField = item.FindControl("hidNameId")
                Dim txtName As TextBox = item.FindControl("txtName")
                Dim dtName As TextBox = item.FindControl("dtName")

                Dim dateChanged = Logic.Conversion.ToNullableDate(dtName.Text)

                cmd.Parameters("name").Value = txtName.Text
                cmd.Parameters("dateChanged").Value = If(dateChanged.HasValue, dateChanged.Value, DBNull.Value)

                hidId.Value = cmd.ExecuteScalar()
            Next
        End Using
    End Sub

    Private Sub _SaveDocument()

        Dim datasource As New dsCBCRelease
        datasource.EnforceConstraints = False

        With New dsCBCReleaseTableAdapters.ApplicationTableAdapter
            .Connection.ConnectionString = Logic.Database.ConnectionString
            .Fill(datasource.Application, Master.app.id)
        End With
        With New dsCBCReleaseTableAdapters.web_driverApplicationCBCAddressesTableAdapter
            .Connection.ConnectionString = Logic.Database.ConnectionString
            .Fill(datasource.web_driverApplicationCBCAddresses, Master.app.id)
        End With
        With New dsCBCReleaseTableAdapters.web_DriverApplicationCBCNamesTableAdapter
            .Connection.ConnectionString = Logic.Database.ConnectionString
            .Fill(datasource.web_DriverApplicationCBCNames, Master.app.id)
        End With

        Dim report As New crptAppCBCRelease
        report.SetDataSource(datasource)

        'Reporting.SetPictureToSignature(
        '    report.ReportDefinition.ReportObjects("picSigConsent"),
        '    report.DataDefinition.FormulaFields("pathSigConsent"),
        '    If(sigConsent.SignatureId.HasValue, sigConsent.SignatureId, 0),
        '    5600)
        'Reporting.SetPictureToSignature(
        '    report.ReportDefinition.ReportObjects("picSigBackground"),
        '    report.DataDefinition.FormulaFields("pathSigBackground"),
        '    If(sigBackground.SignatureId.HasValue, sigBackground.SignatureId, 0),
        '    5600)

        Dim raw = Reporting.StreamToByteArray(report)
        Using connection As New SqlConnection(Logic.Database.ConnectionString)
            connection.Open()
            Dim cmd As New SqlCommand(
                "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", connection)
            cmd.Parameters.AddRange({
                New SqlParameter("appId", Master.app.id),
                New SqlParameter("pageTitle", Master.CurrentTitle),
                New SqlParameter("pageFileName", Master.CurrentPage),
                New SqlParameter("pdfData", raw),
                New SqlParameter("pdfDataSize", raw.Length)
                })
            cmd.ExecuteNonQuery()
        End Using

    End Sub

    Private Function _HasEnteredLastAddress() As Boolean
        Dim count = rptAddresses.Items.Count
        If count = 0 Then Return True

        Dim item As RepeaterItem = rptAddresses.Items(count - 1)
        Dim dtUntil As TextBox = item.FindControl("dtUntil")
        Dim txtAddress As TextBox = item.FindControl("txtAddress")

        If count = 1 Then
            Return Not String.IsNullOrWhiteSpace(txtAddress.Text)
        Else
            Return IsDate(dtUntil.Text)
        End If
    End Function

    Private Function _HasEnteredLastName() As Boolean
        Dim count = rptFormerNames.Items.Count
        If count = 0 Then Return True

        Dim item As RepeaterItem = rptFormerNames.Items(count - 1)
        Dim dtName As TextBox = item.FindControl("dtName")
        Return IsDate(dtName.Text)
    End Function

End Class