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

Public Class Licenses
    Inherits System.Web.UI.Page

    Private Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        Try
            ' gotta set these here
            imgBack.Attributes.Add("data-for", uplBack.ClientID)
            imgFront.Attributes.Add("data-for", uplFront.ClientID)

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

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

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

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

            _RemoveLicense(CInt(hidId.Value))
            _LoadLicenseList()

        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,
                    CurrentLicenseFrontFilename, CurrentLicenseBackFilename
                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"))

            If TypeOf dReader(0) IsNot DBNull Then
                imgFront.ImageUrl = "/_assets/AttachmentImage.aspx?context=DPQ&name=CurrentLicenseFront&id=" & Master.app.id
            End If
            If TypeOf dReader(1) IsNot DBNull Then
                imgBack.ImageUrl = "/_assets/AttachmentImage.aspx?context=DPQ&name=CurrentLicenseBack&id=" & Master.app.id
            End If
        End Using
    End Sub

    Private Sub _LoadLists()
        _LoadLicenseList()
    End Sub


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

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

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

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

            Dim dReader = cmdLicenses.ExecuteReader()
            For Each item As RepeaterItem In rptLicenses.Items
                Dim hidId As HiddenField = item.FindControl("hidLicenseId")
                Dim txtLicenseNumber As TextBox = item.FindControl("txtLicenseNumber")
                Dim ddlLicenseState As StatePicker = item.FindControl("ddlLicenseState")
                Dim txtLicenseType As TextBox = item.FindControl("txtLicenseType")
                Dim txtLicenseExpires As TextBox = item.FindControl("txtLicenseExpires")
                Dim ynCDL As YesNoPicker = item.FindControl("ynCDL")
                Dim chkN As CheckBox = item.FindControl("chkN")
                Dim chkS As CheckBox = item.FindControl("chkS")
                Dim chkH As CheckBox = item.FindControl("chkH")
                Dim chkX As CheckBox = item.FindControl("chkX")
                Dim chkT As CheckBox = item.FindControl("chkT")
                Dim chkP As CheckBox = item.FindControl("chkP")
                Dim txtLicenseRestrictions As TextBox = item.FindControl("txtLicenseRestrictions")

                If dReader.Read() Then
                    hidId.Value = dReader("id")
                    txtLicenseNumber.Text = dReader("Number")
                    ddlLicenseState.SelectedValue = dReader("State")
                    txtLicenseType.Text = dReader("Type")
                    txtLicenseExpires.Text = Logic.Conversion.ToDate(dReader("Expires")).FormatForHtml5()
                    txtLicenseRestrictions.Text = dReader("Restrictions")

                    Dim endorsements = CStr(dReader("Endorsements"))
                    ynCDL.Checked = endorsements.Contains("/CDL/")
                    chkN.Checked = endorsements.Contains("/N/")
                    chkS.Checked = endorsements.Contains("/S/")
                    chkH.Checked = endorsements.Contains("/H/")
                    chkX.Checked = endorsements.Contains("/X/")
                    chkT.Checked = endorsements.Contains("/T/")
                    chkP.Checked = endorsements.Contains("/P/")

                    If Logic.Conversion.ToBoolean(dReader("isCDLEmpty")) = True Then
                        ynCDL.Checked = Nothing
                    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
            _SaveDocument2()
        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
                
            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()


        Dim cmdSaveFront = "
            UPDATE web_DriverApplication
            SET CurrentLicenseFront=@front, CurrentLicenseFrontFilename=@frontName
            WHERE id=@appId"
        Dim cmdSaveBack = "
            UPDATE web_DriverApplication
            SET CurrentLicenseBack=@back, CurrentLicenseBackFilename=@backName
            WHERE id=@appId"


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

        If uplFront.HasFile Then
                Dim filename = uplFront.FileName
                If filename.Length > 50 Then
                    filename = filename.Substring(filename.Length - 50)
                End If

                cmd.CommandText = cmdSaveFront
                cmd.Parameters.AddWithValue("front", uplFront.FileBytes)
                cmd.Parameters.AddWithValue("frontName", filename)
                cmd.ExecuteNonQuery()
            End If

            If uplBack.HasFile Then
                Dim filename = uplBack.FileName
                If filename.Length > 50 Then
                    filename = filename.Substring(filename.Length - 50)
                End If

                cmd.CommandText = cmdSaveBack
                cmd.Parameters.AddWithValue("back", uplBack.FileBytes)
                cmd.Parameters.AddWithValue("backName", filename)
                cmd.ExecuteNonQuery()
            End If

    End Sub

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

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

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

        Dim cmdSave As New SqlCommand("
            INSERT INTO web_DriverApplicationLicenses
            (AppId, Number, State, Type, Expires, Endorsements, Restrictions, isCDLEmpty)
            VALUES (@appId, @number, @state, @type, @expires, @endorsements, @restrictions, @isCDLEmpty);
            SELECT @@IDENTITY;",
            connection, transaction)
        With cmdSave.Parameters
            .AddWithValue("appId", Master.app.id)
            .Add(New SqlParameter("number", SqlDbType.VarChar))
            .Add(New SqlParameter("state", SqlDbType.VarChar))
            .Add(New SqlParameter("type", SqlDbType.VarChar))
            .Add(New SqlParameter("expires", SqlDbType.Date))
            .Add(New SqlParameter("endorsements", SqlDbType.VarChar))
            .Add(New SqlParameter("restrictions", SqlDbType.VarChar))
            .Add(New SqlParameter("isCDLEmpty", SqlDbType.Bit))
        End With

        For Each item As RepeaterItem In rptLicenses.Items
            Dim hidId As HiddenField = item.FindControl("hidLicenseId")
            Dim txtLicenseNumber As TextBox = item.FindControl("txtLicenseNumber")
            Dim ddlLicenseState As StatePicker = item.FindControl("ddlLicenseState")
            Dim txtLicenseType As TextBox = item.FindControl("txtLicenseType")
            Dim txtLicenseExpires As TextBox = item.FindControl("txtLicenseExpires")
            Dim ynCDL As YesNoPicker = item.FindControl("ynCDL")
            Dim chkN As CheckBox = item.FindControl("chkN")
            Dim chkS As CheckBox = item.FindControl("chkS")
            Dim chkH As CheckBox = item.FindControl("chkH")
            Dim chkX As CheckBox = item.FindControl("chkX")
            Dim chkT As CheckBox = item.FindControl("chkT")
            Dim chkP As CheckBox = item.FindControl("chkP")
            Dim txtLicenseRestrictions As TextBox = item.FindControl("txtLicenseRestrictions")

            cmdSave.Parameters("number").Value = txtLicenseNumber.Text
            cmdSave.Parameters("state").Value = ddlLicenseState.SelectedValue
            cmdSave.Parameters("type").Value = txtLicenseType.Text
            cmdSave.Parameters("expires").Value = Logic.Conversion.ToDate(txtLicenseExpires.Text).Clip()
            cmdSave.Parameters("endorsements").Value = If(ynCDL.Checked, "/CDL", "") & If(chkN.Checked, "/N", "") & If(chkS.Checked, "/S", "") & If(chkH.Checked, "/H", "") & If(chkX.Checked, "/X", "") & If(chkT.Checked, "/T", "") & If(chkP.Checked, "/P", "") & "/"
            cmdSave.Parameters("restrictions").Value = txtLicenseRestrictions.Text
            cmdSave.Parameters("isCDLEmpty").Value = Not ynCDL.Checked.HasValue
            hidId.Value = cmdSave.ExecuteScalar()
        Next

        ' We want to do another save for the stuff that pulls from first license
        If rptLicenses.Items.Count > 0 Then
            Dim item As RepeaterItem = rptLicenses.Items(0)
            Dim hidId As HiddenField = item.FindControl("hidLicenseId")
            Dim txtLicenseNumber As TextBox = item.FindControl("txtLicenseNumber")
            Dim ddlLicenseState As StatePicker = item.FindControl("ddlLicenseState")
            Dim txtLicenseType As TextBox = item.FindControl("txtLicenseType")
            Dim txtLicenseExpires As TextBox = item.FindControl("txtLicenseExpires")
            Dim ynCDL As YesNoPicker = item.FindControl("ynCDL")
            Dim chkN As CheckBox = item.FindControl("chkN")
            Dim chkS As CheckBox = item.FindControl("chkS")
            Dim chkH As CheckBox = item.FindControl("chkH")
            Dim chkX As CheckBox = item.FindControl("chkX")
            Dim chkT As CheckBox = item.FindControl("chkT")
            Dim chkP As CheckBox = item.FindControl("chkP")
            Dim txtLicenseRestrictions As TextBox = item.FindControl("txtLicenseRestrictions")

            Dim cmdSaveFirst As New SqlCommand("
                UPDATE web_DriverApplication
                SET license1Number=@number, license1State=@state, 
                    license1Endorsements=@endorsements, license1Restrictions=@restrictions,
                    license1Expiration=@expiration, license1Class=@class
                WHERE id=@appId",
                connection, transaction)
            With cmdSaveFirst.Parameters
                .AddWithValue("appId", Master.app.id)
                .AddWithValue("number", txtLicenseNumber.Text)
                .AddWithValue("state", ddlLicenseState.SelectedValue)
                .AddWithValue("class", txtLicenseType.Text)
                .AddWithValue("expiration", Logic.Conversion.ToDate(txtLicenseExpires.Text).Clip())
                .AddWithValue("endorsements", If(ynCDL.Checked, "/CDL", "") & If(chkN.Checked, "/N", "") & If(chkS.Checked, "/S", "") & If(chkH.Checked, "/H", "") & If(chkX.Checked, "/X", "") & If(chkT.Checked, "/T", "") & If(chkP.Checked, "/P", "") & "/")
                .AddWithValue("restrictions", txtLicenseRestrictions.Text)
            End With
            cmdSaveFirst.ExecuteNonQuery()

        End If

    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

    Private Sub _SaveDocument2()

        Dim PictureFront As Byte() = Nothing
        Dim PictureBack As Byte() = Nothing
        Using connection As New SqlConnection(Logic.Database.ConnectionString)
            connection.Open()
            Dim cmdGet = "SELECT CurrentLicenseFront, CurrentLicenseBack FROM web_DriverApplication WHERE Id=@appId"
            Dim cmd As New SqlCommand(cmdGet, connection)
            cmd.Parameters.AddWithValue("appId", Master.app.id)
            Dim dReader = cmd.ExecuteReader()
            If dReader.Read() Then
                If TypeOf dReader(0) IsNot DBNull Then PictureFront = dReader(0)
                If TypeOf dReader(1) IsNot DBNull Then PictureBack = dReader(1)
            End If
        End Using

        Dim ReportDataSet As New dsAppGeneric
        Dim ReportTableAdapter As New dsAppGenericTableAdapters.AppGenericTableAdapter

        Using Report As New crptAppCurrentLicense
            ReportTableAdapter.Fill(ReportDataSet.AppGenericDataTable, Master.app.id)
            Report.SetDataSource(ReportDataSet)

            Dim PictureBox As PictureObject = Report.ReportDefinition.ReportObjects("picFront")
            Dim LinkedFormula As FormulaFieldDefinition = Report.DataDefinition.FormulaFields("pathFront")
            Dim PathFront = Reporting.SetPictureFromData(PictureBox, LinkedFormula, PictureFront, New Drawing.Size(11224, 4800))

            PictureBox = Report.ReportDefinition.ReportObjects("picBack")
            LinkedFormula = Report.DataDefinition.FormulaFields("pathBack")
            Dim PathBack = Reporting.SetPictureFromData(PictureBox, LinkedFormula, PictureBack, New Drawing.Size(11224, 4800))

            Dim rawData = Reporting.StreamToByteArray(Report)

            If PathFront IsNot Nothing Then File.Delete(PathFront)
            If PathBack IsNot Nothing Then File.Delete(PathBack)

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

                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)
                cmd.Parameters.AddWithValue("appId", Master.app.id)
                cmd.Parameters.AddWithValue("pageTitle", Master.CurrentTitle & "2")
                cmd.Parameters.AddWithValue("pageFileName", Master.CurrentPage & "2")
                cmd.Parameters.AddWithValue("pdfData", rawData)
                cmd.Parameters.AddWithValue("pdfDataSize", rawData.Length)
                cmd.ExecuteNonQuery()

            End Using

        End Using
    End Sub


#End Region

#Region "Other internals"


    Private Function _HasEnteredLastLicense() As Boolean
        Dim count = rptLicenses.Items.Count
        If count = 0 Then
            Return True
        Else
            Dim item As RepeaterItem = rptLicenses.Items(count - 1)
            If CType(item.FindControl("txtLicenseNumber"), TextBox).Text.Length = 0 Then Return False
            If CType(item.FindControl("ynCDL"), YesNoPicker).Checked.HasValue = False Then Return False
            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 _RemoveLicense(itemId As Integer)
        Using connection As New SqlConnection(Logic.Database.ConnectionString)
            connection.Open()
            Dim cmd As New SqlCommand(
                "DELETE FROM web_DriverApplicationLicenses WHERE id=@itemId",
                connection)
            cmd.Parameters.AddWithValue("itemId", itemId)
            cmd.ExecuteNonQuery()
        End Using
    End Sub


#End Region

End Class