Imports System.Data.SqlClient
Imports System.IO
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared
Imports Logic.NullableDateExtensions
Imports PdfSharp.Pdf
Imports PdfSharp.Pdf.IO
Imports ReportingClass

Public Class SafetyHistory
    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()
            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 rptHistory.AddClick
        Try
            If _HasEnteredLastItem() Then
                rptHistory.TargetCount = Math.Max(rptHistory.TargetCount, rptHistory.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
            
            rptHistory.TargetCount = Math.Max(rptHistory.TargetCount, rptHistory.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()
    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_DriverApplicationSafetyHistory WHERE AppId=@appId",
                connection)
            cmdCount.Parameters.AddWithValue("appId", Master.app.id)
            Dim count = CInt(cmdCount.ExecuteScalar())
            count = Math.Max(count, rptHistory.TargetCount)

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

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

            Dim dReader = cmdHistory.ExecuteReader()
            For Each item As RepeaterItem In rptHistory.Items
                Dim hidId As HiddenField = item.FindControl("hidId")
                Dim txtName As TextBox = item.FindControl("txtName")
                Dim txtSocial As TextBox = item.FindControl("txtSocial")
                Dim txtCompany As TextBox = item.FindControl("txtCompany")
                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 txtPhone As TextBox = item.FindControl("txtPhone")
                Dim txtFax As TextBox = item.FindControl("txtFax")
                Dim txtEmploymentStart As TextBox = item.FindControl("txtEmploymentStart")
                Dim txtEmploymentEnd As TextBox = item.FindControl("txtEmploymentEnd")
                Dim sig As Signature = item.FindControl("signature")
                '  Dim hidHistoryID As HiddenField = item.FindControl("hidHistoryID")

                sig.ContextId = "history" & CStr(item.ItemIndex + 1)
                sig.DefaultSigUrl = "/_assets/DefaultAppSig.aspx?appId=" & Master.app.id
                txtName.Text = Master.app.applicantName
                txtSocial.Text = Master.app.applicantSocialMasked

                If dReader.Read() Then
                    hidId.Value = dReader("id")
                    txtCompany.Text = dReader("Company")
                    txtAddress.Text = dReader("Address")
                    txtCity.Text = dReader("City")
                    ddlState.SelectedValue = dReader("State")
                    txtZip.Text = dReader("Zip")
                    txtPhone.Text = dReader("Phone")
                    txtFax.Text = dReader("Fax")
                    txtEmploymentStart.Text = Logic.Conversion.ToNullableDate(dReader("EmploymentStart")).FormatForHtml()
                    txtEmploymentEnd.Text = Logic.Conversion.ToNullableDate(dReader("EmploymentEnd")).FormatForHtml()
                    sig.SignatureId = Logic.Conversion.ToNullableInteger(dReader("SignatureId"))
                    '    hidHistoryID.Value = Logic.Conversion.ToInteger(dReader("HistoryID"))
                End If
            Next

        End Using
    End Sub
    
    ' Used to temporary store employment history while prefilling safety history
    Private Structure EmploymentHistory
        Property CompanyName As String
        Property Address As String
        Property City As String
        Property State As String
        Property Zip As String
        Property Phone As String
        Property Fax As String
        Property EmploymentStart As Date
        Property EmploymentEnd As Date
        Property HistID As Int64
    End Structure

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

            ' Load all of the employment history data that we're prefilling from
            Dim history As New List(Of EmploymentHistory)
            Dim cmdGet As New SqlCommand(
                "SELECT * FROM web_DriverApplicationEmploymentHistory WHERE AppId=@appId AND CompanyName<>'Unemployed'",
                connection)
            cmdGet.Parameters.AddWithValue("appId", Master.app.id)
            Using dReader = cmdGet.ExecuteReader()
                While dReader.Read()
                    history.Add(New EmploymentHistory With {
                        .CompanyName = Logic.Conversion.ToString(dReader("CompanyName")),
                        .Address = Logic.Conversion.ToString(dReader("Address")),
                        .City = Logic.Conversion.ToString(dReader("City")),
                        .State = Logic.Conversion.ToString(dReader("State")),
                        .Zip = Logic.Conversion.ToString(dReader("Zip")),
                        .Phone = Logic.Conversion.ToString(dReader("Phone")),
                        .Fax = Logic.Conversion.ToString(dReader("Fax")),
                        .EmploymentStart = Logic.Conversion.ToDate(dReader("EmployedFrom")),
                        .EmploymentEnd = Logic.Conversion.ToDate(dReader("EmployedTo"))',       .HistID = Logic.Conversion.ToInteger(dReader("ID"))
                       })
                End While
            End Using
            
            ' Now we can insert the history into the safety history.
            ' We do this separately since it's the easiest way to guarantee the order is the same.
            Using transaction = connection.BeginTransaction()
            
                Dim cmdPrefill As New SqlCommand("
                    INSERT INTO web_DriverApplicationSafetyHistory
                    (AppId, Company, Address, City, State, Zip, Phone, Fax, EmploymentStart, EmploymentEnd)
                    VALUES (@AppId, @Company, @Address, @City, @State, @Zip, @Phone, @Fax, @EmploymentStart, @EmploymentEnd)
                    ", connection, transaction)
                'Dim cmdPrefill As New SqlCommand("
                '    INSERT INTO web_DriverApplicationSafetyHistory
                '    (AppId, Company, Address, City, State, Zip, Phone, Fax, EmploymentStart, EmploymentEnd,HistoryID)
                '    VALUES (@AppId, @Company, @Address, @City, @State, @Zip, @Phone, @Fax, @EmploymentStart, @EmploymentEnd,@HistoryID)
                '    ", connection, transaction)
                cmdPrefill.Parameters.AddWithValue("AppId", Master.app.id)
                cmdPrefill.Parameters.Add(New SqlParameter("Company", SqlDbType.VarChar))
                cmdPrefill.Parameters.Add(New SqlParameter("Address", SqlDbType.VarChar))
                cmdPrefill.Parameters.Add(New SqlParameter("City", SqlDbType.VarChar))
                cmdPrefill.Parameters.Add(New SqlParameter("State", SqlDbType.VarChar))
                cmdPrefill.Parameters.Add(New SqlParameter("Zip", SqlDbType.VarChar))
                cmdPrefill.Parameters.Add(New SqlParameter("Phone", SqlDbType.VarChar))
                cmdPrefill.Parameters.Add(New SqlParameter("Fax", SqlDbType.VarChar))
                cmdPrefill.Parameters.Add(New SqlParameter("EmploymentStart", SqlDbType.Date))
                cmdPrefill.Parameters.Add(New SqlParameter("EmploymentEnd", SqlDbType.Date))
                'cmdPrefill.Parameters.Add(New SqlParameter("HistoryID", SqlDbType.BigInt))

                For Each item In history
                    cmdPrefill.Parameters("Company").Value = item.CompanyName
                    cmdPrefill.Parameters("Address").Value = item.Address
                    cmdPrefill.Parameters("City").Value = item.City
                    cmdPrefill.Parameters("State").Value = item.State
                    cmdPrefill.Parameters("Zip").Value = item.Zip
                    cmdPrefill.Parameters("Phone").Value = item.Phone
                    cmdPrefill.Parameters("Fax").Value = item.Fax
                    cmdPrefill.Parameters("EmploymentStart").Value = item.EmploymentStart
                    cmdPrefill.Parameters("EmploymentEnd").Value = item.EmploymentEnd
                    'cmdPrefill.Parameters("HistoryID").Value = item.HistID
                    cmdPrefill.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 cmd As New SqlCommand(
            "UPDATE web_DriverApplication SET VisitedSafetyHistoryAspx=1 WHERE id=@appId",
            connection, transaction)
        cmd.Parameters.AddWithValue("appId", Master.app.id)
        cmd.ExecuteNonQuery()
    End Sub

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

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

        Dim cmdSave As New SqlCommand("
            INSERT INTO web_DriverApplicationSafetyHistory
            (AppId, Company, Address, City, State, Zip, Phone, Fax, EmploymentStart, EmploymentEnd, SignatureId)
            VALUES (@appId, @company, @address, @city, @state, @zip, @phone, @fax, @employmentStart, @employmentEnd, @signatureId);
            SELECT @@IDENTITY;",
            connection, transaction)
        'Dim cmdSave As New SqlCommand("
        '    INSERT INTO web_DriverApplicationSafetyHistory
        '    (AppId, Company, Address, City, State, Zip, Phone, Fax, EmploymentStart, EmploymentEnd, SignatureId,HistoryID)
        '    VALUES (@appId, @company, @address, @city, @state, @zip, @phone, @fax, @employmentStart, @employmentEnd, @signatureId,@HistoryID);
        '    SELECT @@IDENTITY;",
        '    connection, transaction)
        With cmdSave.Parameters
            .AddWithValue("appId", Master.app.id)
            .Add(New SqlParameter("company", SqlDbType.VarChar))
            .Add(New SqlParameter("address", SqlDbType.VarChar))
            .Add(New SqlParameter("city", SqlDbType.VarChar))
            .Add(New SqlParameter("state", SqlDbType.VarChar))
            .Add(New SqlParameter("zip", SqlDbType.VarChar))
            .Add(New SqlParameter("phone", SqlDbType.VarChar))
            .Add(New SqlParameter("fax", SqlDbType.VarChar))
            .Add(New SqlParameter("employmentStart", SqlDbType.Date))
            .Add(New SqlParameter("employmentEnd", SqlDbType.Date))
            .Add(New SqlParameter("signatureId", SqlDbType.BigInt))
            '.Add(New SqlParameter("HistoryID", SqlDbType.BigInt))
        End With

        For Each item As RepeaterItem In rptHistory.Items
            Dim hidId As HiddenField = item.FindControl("hidId")
            Dim txtCompany As TextBox = item.FindControl("txtCompany")
            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 txtPhone As TextBox = item.FindControl("txtPhone")
            Dim txtFax As TextBox = item.FindControl("txtFax")
            Dim txtEmploymentStart As TextBox = item.FindControl("txtEmploymentStart")
            Dim txtEmploymentEnd As TextBox = item.FindControl("txtEmploymentEnd")
            Dim sig As Signature = item.FindControl("signature")
            'Dim hidHistoryID As HiddenField = item.FindControl("hidHistoryID")

            Dim EmploymentStart = Logic.Conversion.ToNullableDate(txtEmploymentStart.Text)
            Dim EmploymentEnd = Logic.Conversion.ToNullableDate(txtEmploymentEnd.Text)
            Dim sigId = sig.SignatureId
            
            cmdSave.Parameters("company").Value = Logic.Conversion.ToString(txtCompany.Text)
            cmdSave.Parameters("address").Value = Logic.Conversion.ToString(txtAddress.Text)
            cmdSave.Parameters("city").Value = Logic.Conversion.ToString(txtCity.Text)
            cmdSave.Parameters("state").Value = Logic.Conversion.ToString(ddlState.SelectedValue)
            cmdSave.Parameters("zip").Value = Logic.Conversion.ToString(txtZip.Text)
            cmdSave.Parameters("phone").Value = Logic.Conversion.ToString(txtPhone.Text)
            cmdSave.Parameters("fax").Value = Logic.Conversion.ToString(txtFax.Text)
            cmdSave.Parameters("employmentStart").Value = If(EmploymentStart.HasValue, EmploymentStart.Value, DBNull.Value)
            cmdSave.Parameters("employmentEnd").Value = If(EmploymentEnd.HasValue, EmploymentEnd.Value, DBNull.Value)
            cmdSave.Parameters("signatureId").Value = If(sigId.HasValue, sigId.Value, DBNull.Value)
            'cmdSave.Parameters("HistoryID").Value = Logic.Conversion.ToNullableInteger(hidHistoryID.Value)

            hidId.Value = cmdSave.ExecuteScalar()
        Next

    End Sub

    Private Sub _SaveDocument(connection As SqlConnection, transaction As SqlTransaction)
        
        Dim sigIds As New List(Of Integer)
        Dim cmdSigs As New SqlCommand(
            "SELECT SignatureId FROM web_DriverApplicationSafetyHistory WHERE AppId=@appId", 
            connection, transaction)
        cmdSigs.Parameters.AddWithValue("appId", Master.app.id)
        Using dReader = cmdSigs.ExecuteReader()
            While dReader.Read()
                Dim sigId = Logic.Conversion.ToInteger(dReader(0))
                If sigId > 0 Then
                    sigIds.Add(Logic.Conversion.ToInteger(dReader(0)))
                End If
            End While
        End Using
        
        Dim ReportsToCombine As New List(Of Stream)
        If sigIds.Count = 0 Then
            Dim page = _GenerateEmptyDocumentPage(connection, transaction)
            ReportsToCombine.Add(page)
        Else
            For Each sigId In sigIds
                Dim page = _GenerateDocumentPage(sigId, connection, transaction)
                ReportsToCombine.Add(page)
            Next
        End If

        Dim outPdf As New PdfDocument()
        For Each report In ReportsToCombine
            Dim inPdf = PdfReader.Open(report, PdfDocumentOpenMode.Import)
            For i = 0 To inPdf.PageCount - 1
                outPdf.AddPage(inPdf.Pages(i))
            Next
        Next

        Dim output As New MemoryStream()
        outPdf.Save(output, False)
        Dim bytes(CInt(output.Length)) as Byte
        output.Read(bytes, 0, CInt(output.Length) - 1)
        
        Dim cmdSave 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, transaction)
        cmdSave.Parameters.AddWithValue("appId", Master.app.id)
        cmdSave.Parameters.AddWithValue("pageTitle", Master.CurrentTitle)
        cmdSave.Parameters.AddWithValue("pageFileName", Master.CurrentPage)
        cmdSave.Parameters.AddWithValue("pdfData", bytes)
        cmdSave.Parameters.AddWithValue("pdfDataSize", bytes.Length)
        cmdSave.ExecuteNonQuery()

        output.Close()

    End Sub

    Private Function _GenerateDocumentPage(sigId As Integer, connection As SqlConnection, transaction As SqlTransaction) As Stream
        Dim ReportDataSet As New dsAppSafetyHistory
        Dim ReportTableAdapter As New TransactionalAppSafetyHistoryTableAdapter
        ReportTableAdapter.SetConnection(connection, transaction)
        ReportTableAdapter.FillBySigId(ReportDataSet.AppSafetyHistoryDataTable, Master.app.id, sigId)

        Using Report As New crptAppSafetyHistory

            Dim LinkedFormula = Report.DataDefinition.FormulaFields("pathSig")
            Dim PictureBox As PictureObject = Report.ReportDefinition.ReportObjects("picSig")
            Dim SignatureId = ReportDataSet.AppSafetyHistoryDataTable.Rows(0)("SignatureId")
            Reporting.SetPictureToSignature(PictureBox, LinkedFormula, SignatureId, 5600)

            Report.SetDataSource(ReportDataSet)
            Return Report.ExportToStream(ExportFormatType.PortableDocFormat)

        End Using
    End Function

    Private Function _GenerateEmptyDocumentPage(connection As SqlConnection, transaction As SqlTransaction) As Stream
        Dim ReportDataSet As New dsAppGeneric
        Dim ReportTableAdapter As New TransactionalAppGenericTableAdapter
        ReportTableAdapter.SetConnection(connection, transaction)
        ReportTableAdapter.Fill(ReportDataSet.AppGenericDataTable, Master.app.id)

        Using Report As New crptAppSafetyHistory_Empty
            Report.SetDataSource(ReportDataSet)
            Return Report.ExportToStream(ExportFormatType.PortableDocFormat)
        End Using
    End Function

#End Region

#Region "Other internals"

    Private Function _HasEnteredLastItem() As Boolean
        Dim count = rptHistory.Items.Count
        If count = 0 Then
            Return True
        Else
            Dim item As RepeaterItem = rptHistory.Items(count - 1)
            Dim txtCompany As TextBox = item.FindControl("txtCompany")
            Return txtCompany.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 ISNULL(VisitedSafetyHistoryAspx, 0) 
                FROM web_DriverApplication WHERE id=@appId",
                connection)
            cmd.Parameters.AddWithValue("appId", Master.app.id)
            Return Logic.Conversion.ToBoolean(cmd.ExecuteScalar())
        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_DriverApplicationSafetyHistory WHERE id=@itemId", 
                connection)
            cmd.Parameters.AddWithValue("itemId", itemId)
            cmd.ExecuteNonQuery()

        End Using
    End Sub

#End Region

End Class