Imports System.Data.SqlClient
Imports System.IO
Imports CrystalDecisions.CrystalReports.Engine
Imports ReportingClass

Public Class Submit1
    Inherits System.Web.UI.Page

    Private _application As Logic.DriverApplicationSummary

    Protected Sub Page_Load() Handles Me.Load
        lblCompany1.Text = Master.transfer.sourceName
        lblCompany2.Text = Master.transfer.targetName

        If Page.IsPostBack Then Exit Sub

        Try
            _application = New Logic.DriverApplicationSummary(Master.transfer.appId)
            Using connection As New SqlConnection(Logic.Database.connectionString)
                connection.Open()
                Using transaction = connection.BeginTransaction()
                    _UpdateDocuments(connection, transaction)
                    _UpdateApplication(connection, transaction)
                    _UpdateTransfer(connection, transaction)
                    _EmailUpdate(connection, transaction)
                    transaction.Commit()
                End Using
            End Using
        Catch ex As Exception
            Dim errorId = Logic.Error.Log(ex)
            Response.Redirect("/Error.aspx?id=" & errorId)
        End Try
    End Sub

    Private Sub _UpdateApplication(connection As SqlConnection, transaction As SqlTransaction)
        Dim cmd As New SqlCommand(
            "UPDATE app SET app.status='in review'
            FROM web_DriverMoveApplication xfer
            LEFT JOIN web_DriverApplication app ON app.id=xfer.appId
            WHERE xfer.id=@xferId", connection, transaction)
        cmd.Parameters.AddWithValue("xferId", Master.transfer.id)
        cmd.ExecuteNonQuery()
    End Sub

    Private Sub _UpdateTransfer(connection As SqlConnection, transaction As SqlTransaction)
        Dim cmd As New SqlCommand(
            "UPDATE web_DriverMoveApplication 
            SET transferCompleteDate=GETDATE()
            WHERE id=@xferId", connection, transaction)
        cmd.Parameters.AddWithValue("xferId", Master.transfer.id)
        cmd.ExecuteNonQuery()
    End Sub

    Private Sub _EmailUpdate(connection As SqlConnection, transaction As SqlTransaction)
        Dim cmd As New SqlCommand("", connection, transaction)
        cmd.Parameters.AddWithValue("xferId", Master.transfer.id)

        Dim emails As New List(Of String)
        cmd.CommandText =
            "SELECT ISNULL(sc1.EmailDriver,''), ISNULL(sc2.EmailDriver,''), c.Email
            FROM web_DriverMoveApplication xfer
            LEFT JOIN web_DriverApplication app ON app.id=xfer.appId
            LEFT JOIN ServiceContract sc1 ON sc1.id=xfer.previousContractId
            LEFT JOIN ServiceContract sc2 ON sc2.id=app.clientId
            LEFT JOIN Company c ON 1=1
            WHERE xfer.id=@xferId"
        Using dReader = cmd.ExecuteReader()
            dReader.Read()
            For i = 0 To 2 : emails.Add(dReader(i)) : Next
        End Using

        Dim emailAddress = ""
        Dim emailSubject = ""
        Dim emailTemplate = ""
        cmd.CommandText =
            "SELECT TOP 1 e.Address,c.EmailReassignedDriverSubject,c.EmailReassignedDriverBody,0
            FROM Company c LEFT JOIN CompanyEmails e ON e.id=c.EmailReassignedDriverFromId"
        Using dReader = cmd.ExecuteReader()
            dReader.Read()
            emailAddress = dReader(0)
            emailSubject = dReader(1)
            emailTemplate = dReader(2)
        End Using

        Dim params = New Dictionary(Of String, String) From {
            {"OLD_CUSTOMERNAME", Master.transfer.sourceName},
            {"NEW_CUSTOMERNAME", Master.transfer.targetName},
            {"DRIVERNAME", _application.applicantName}}
        For Each param In params
            emailTemplate = emailTemplate.Replace("#" + param.Key & "#", param.Value)
        Next

        cmd.CommandText =
            "INSERT INTO AutomationServer_EmailsSent (EmailFrom,EmailFromName,EmailTo,EmailSubject,EmailBody,EmailSent)
            VALUES (@emailFrom,@emailFrom,@emailTo,@emailSubject,@emailBody,0)"
        cmd.Parameters.AddWithValue("emailFrom", emailAddress)
        cmd.Parameters.AddWithValue("emailTo", "")
        cmd.Parameters.AddWithValue("emailSubject", emailSubject)
        cmd.Parameters.AddWithValue("emailBody", emailTemplate)
        For Each email In emails
            cmd.Parameters("emailTo").Value = email
            cmd.ExecuteNonQuery()
        Next
    End Sub

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

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

        Using report As New crptAppGeneralQuestions
            Dim ds As New dsAppGeneralQuestions
            Dim adapter As New TransactionalAppGeneralQuestionsTableAdapter
            adapter.SetConnection(connection, transaction)
            adapter.Fill(ds.AppGeneralQuestionsDataTable, Master.app.id)
            report.SetDataSource(ds)
            _UpdateDocument("General Questions", "GeneralQuestions.aspx", Reporting.StreamToByteArray(report), connection, transaction)
        End Using

        Using report As New crptAppDrivingExperience
            Dim ds As New dsAppDrivingExperience
            Dim adapter As New TransactionalAppDrivingExperienceTableAdapter
            adapter.SetConnection(connection, transaction)
            adapter.Fill(ds.AppDrivingExperienceDataTable, Master.app.id)
            report.SetDataSource(ds)
            _UpdateDocument("Driving Experience", "DrivingExperience.aspx", Reporting.StreamToByteArray(report), connection, transaction)
        End Using

        Using report As New crptAppEmploymentHistory
            Dim ds As New dsAppEmploymentHistory
            Dim adapter As New TransactionalAppEmploymentHistoryTableAdapter
            adapter.SetConnection(connection, transaction)
            adapter.Fill(ds.AppEmploymentHistoryDataTable, Master.app.id)
            report.SetDataSource(ds)
            _UpdateDocument("Employment History", "EmploymentHistory.aspx", Reporting.StreamToByteArray(report), connection, transaction)
        End Using

        Using report As New crptAppNotificationRights
            Dim ds As New dsAppGeneric
            Dim adapter As New TransactionalAppGenericTableAdapter
            adapter.SetConnection(connection, transaction)
            adapter.Fill(ds.AppGenericDataTable, Master.app.id)
            report.SetDataSource(ds)
            _UpdateDocument("Notification of Rights Regarding Safety Performance History Investigations", "NotificationRights.aspx", Reporting.StreamToByteArray(report), connection, transaction)
        End Using

        cmd.CommandText = "SELECT COUNT(*) FROM web_DriverApplicationViolations WHERE AppId=@appId"
        If CInt(cmd.ExecuteScalar()) > 0 Then
            Using report As New crptAppViolationsCertificate
                Dim ds As New dsAppViolationsCertificate
                Dim adapter As New TransactionalAppViolationsCertificateTableAdapter
                adapter.SetConnection(connection, transaction)
                adapter.Fill(ds.AppViolationsCertificateDataTable, Master.app.id)
                report.SetDataSource(ds)
                Reporting.SetPictureToSignature(
                    report.ReportDefinition.ReportObjects("picSig"),
                    report.DataDefinition.FormulaFields("pathSig"),
                    Logic.Conversion.ToInteger(ds.AppViolationsCertificateDataTable.Rows(0)("sigId")),
                    5600)
                _UpdateDocument("Certificate of Violations", "ViolationsCertificate.aspx", Reporting.StreamToByteArray(report), connection, transaction)
            End Using
        Else
            Using report As New crptAppViolationsCertificate_Empty
                Dim ds As New dsAppViolationsCertificate
                Dim adapter As New TransactionalAppNoViolationsCertificateTableAdapter
                adapter.SetConnection(connection, transaction)
                adapter.Fill(ds.AppNoViolationsCertificateDataTable, Master.app.id)
                report.SetDataSource(ds)
                Reporting.SetPictureToSignature(
                    report.ReportDefinition.ReportObjects("picSig"),
                    report.DataDefinition.FormulaFields("pathSig"),
                    Logic.Conversion.ToInteger(ds.AppNoViolationsCertificateDataTable.Rows(0)("sigId")),
                    5600)
                _UpdateDocument("Certificate of Violations", "ViolationsCertificate.aspx", Reporting.StreamToByteArray(report), connection, transaction)
            End Using
        End If

        Using report As New crptAppComplianceCertificate
            Dim ds As New dsAppComplianceCertificate
            Dim adapter As New TransactionalAppComplianceCertificateTableAdapter
            adapter.SetConnection(connection, transaction)
            adapter.Fill(ds.AppComplianceCertificateDataTable, Master.app.id)
            report.SetDataSource(ds)
            Reporting.SetPictureToSignature(
                report.ReportDefinition.ReportObjects("picSig"),
                report.DataDefinition.FormulaFields("pathSig"),
                Logic.Conversion.ToInteger(ds.AppComplianceCertificateDataTable.Rows(0)("sigId")),
                5600)
            _UpdateDocument("Certificate of Compliance with Driver License Requirements", "ComplianceCertificate.aspx", Reporting.StreamToByteArray(report), connection, transaction)
        End Using

        Using report As New crptAppOnDutyHours
            Dim ds As New dsAppOnDutyHours
            Dim adapter As New TransactionalAppOnDutyHoursTableAdapter
            adapter.SetConnection(connection, transaction)
            adapter.Fill(ds.AppOnDutyHours, Master.app.id)
            report.SetDataSource(ds)
            Reporting.SetPictureToSignature(
                report.ReportDefinition.ReportObjects("picSigOnDutyHours"),
                report.DataDefinition.FormulaFields("pathSigOnDutyHours"),
                Logic.Conversion.ToInteger(ds.AppOnDutyHours.Rows(0)("sigOnDutyHoursId")),
                5600)
            Reporting.SetPictureToSignature(
                report.ReportDefinition.ReportObjects("picSigCompensatedWork"),
                report.DataDefinition.FormulaFields("pathSigCompensatedWork"),
                Logic.Conversion.ToInteger(ds.AppOnDutyHours.Rows(0)("sigCompensatedWorkId")),
                5600)
            _UpdateDocument("Driver Statement of On-Duty Hours", "OnDutyHours.aspx", Reporting.StreamToByteArray(report), connection, transaction)
        End Using

        Using report As New crptAppControlledSubstances
            Dim ds As New dsAppControlledSubstances
            Dim adapter As New TransactionalAppControlledSubstancesTableAdapter
            adapter.SetConnection(connection, transaction)
            adapter.Fill(ds.ControlledSubstances, Master.app.id)
            report.SetDataSource(ds)
            Reporting.SetPictureToSignature(
                report.ReportDefinition.ReportObjects("picSig"),
                report.DataDefinition.FormulaFields("pathSig"),
                Logic.Conversion.ToInteger(ds.ControlledSubstances.Rows(0)("sigId")),
                5600)
            _UpdateDocument("Previous Pre-Employment Controlled Substances or Alcohol Test Disclosure", "ControlledSubstances.aspx", Reporting.StreamToByteArray(report), connection, transaction)
        End Using

        Using report As New crptAppControlledSubstancesTestConsent
            Dim ds As New dsAppControlledSubstancesTestConsent
            Dim adapter As New TransactionalControlledSubstancesTestConsentTableAdapter
            adapter.SetConnection(connection, transaction)
            adapter.Fill(ds.ControlledSubstancesTestConsent, Master.app.id)
            report.SetDataSource(ds)
            Reporting.SetPictureToSignature(
                report.ReportDefinition.ReportObjects("picSig1"),
                report.DataDefinition.FormulaFields("pathSig1"),
                Logic.Conversion.ToInteger(ds.ControlledSubstancesTestConsent.Rows(0)("TestingConsentSignatureId")),
                5600)
            Reporting.SetPictureToSignature(
                report.ReportDefinition.ReportObjects("picSig2"),
                report.DataDefinition.FormulaFields("pathSig2"),
                Logic.Conversion.ToInteger(ds.ControlledSubstancesTestConsent.Rows(0)("TestingPolicyReceivedSignatureId")),
                5600)
            _UpdateDocument("Controlled Substances & Alcohol Testing Consent Form and Application Certification", "ControlledSubstancesTestConsent.aspx", Reporting.StreamToByteArray(report), connection, transaction)
        End Using

        Using report As New crptAppDisclosure
            Dim ds As New dsAppDisclosure
            Dim adapter As New TransactionalAppDisclosureTableAdapter
            adapter.SetConnection(connection, transaction)
            adapter.Fill(ds.Disclosure, Master.app.id)
            report.SetDataSource(ds)
            Reporting.SetPictureToSignature(
                report.ReportDefinition.ReportObjects("picSig"),
                report.DataDefinition.FormulaFields("pathSig"),
                Logic.Conversion.ToInteger(ds.Disclosure.Rows(0)("sigId")),
                5600)
            _UpdateDocument("Important Disclosure Regarding Background Reports from the PSP Online Service", "Disclosure.aspx", Reporting.StreamToByteArray(report), connection, transaction)
        End Using

        Using report As New crptAppCurrentLicense
            Dim PictureFront As Byte() = Nothing
            Dim PictureBack As Byte() = Nothing
            cmd.CommandText = "SELECT CurrentLicenseFront,CurrentLicenseBack FROM web_DriverApplication WHERE Id=@appId"
            Using 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 ds As New dsAppGeneric
            Dim adapter As New TransactionalAppGenericTableAdapter
            adapter.SetConnection(connection, transaction)
            adapter.Fill(ds.AppGenericDataTable, Master.app.id)
            report.SetDataSource(ds)

            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))

            _UpdateDocument("Current Driver's License", "CurrentLicense.aspx", Reporting.StreamToByteArray(report), connection, transaction)

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

        Using report As New crptAppMedicalCard
            Dim PictureCard As Byte() = Nothing
            cmd.CommandText = "SELECT MedicalRegistryCard FROM web_DriverApplication WHERE ID=@appId"
            Using dReader = cmd.ExecuteReader()
                If dReader.Read() AndAlso TypeOf dReader(0) IsNot DBNull Then
                    PictureCard = dReader(0)
                End If
            End Using

            If PictureCard IsNot Nothing Then
                Dim ds As New dsAppMedicalCard
                Dim adapter As New TransactionalAppMedicalCardTableAdapter
                adapter.SetConnection(connection, transaction)
                adapter.Fill(ds.MedicalCard, Master.app.id)
                report.SetDataSource(ds)

                Dim PictureBox As PictureObject = Report.ReportDefinition.ReportObjects("picCard")
                Dim LinkedFormula As FormulaFieldDefinition = Report.DataDefinition.FormulaFields("pathCard")
                Dim PathCard = Reporting.SetPictureFromData(PictureBox, LinkedFormula, PictureCard, New Drawing.Size(11224, 4800))
                
                _UpdateDocument("Current Medical Certificate", "MedicalRegistry.aspx", Reporting.StreamToByteArray(report), connection, transaction)

                IF PathCard IsNot Nothing Then File.Delete(PathCard)
            End If
        End Using

        Using report As New crptAppApplicationCertification
            Dim ds As New dsAppApplicationCertification
            Dim adapter As New TransactionalAppApplicationCertificationTableAdapter
            adapter.SetConnection(connection, transaction)
            adapter.Fill(ds.ApplicationCertification, Master.app.id)
            report.SetDataSource(ds)

            Dim PictureBox As PictureObject = Report.ReportDefinition.ReportObjects("picSig")
            Dim LinkedFormula As FormulaFieldDefinition = Report.DataDefinition.FormulaFields("pathSig")
            Dim SignatureId = Logic.Conversion.ToInteger(ds.ApplicationCertification.Rows(0)("ApplicationCertificationSignatureId"))
            Reporting.SetPictureToSignature(PictureBox, LinkedFormula, SignatureId, 6222)

            _UpdateDocument("Application Certification", "ApplicationCertification.aspx", Reporting.StreamToByteArray(report), connection, transaction)
        End Using

    End Sub

    Private Sub _UpdateDocument(title As String, filename As String, data As Byte(), connection As SqlConnection, transaction As SqlTransaction)
        Dim cmd As New SqlCommand(
            "UPDATE web_DriverApplicationPdf SET pageTitle=@pageTitle, pdfData=@pdfData, pdfDataSize=@pdfDataSize
            WHERE appId=@appId AND pageFileName=@pageFileName;", connection, transaction)
        cmd.Parameters.AddWithValue("appId", Master.app.id)
        cmd.Parameters.AddWithValue("pageTitle", title)
        cmd.Parameters.AddWithValue("pageFileName", filename)
        cmd.Parameters.AddWithValue("pdfData", data)
        cmd.Parameters.AddWithValue("pdfDataSize", data.Length)
        cmd.ExecuteNonQuery()
    End Sub

End Class