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

Public Class SPHSubmittalForm
    Inherits System.Web.UI.Page

    Property driverId As Int64
    Property prevEmpID As Int64

    Property Complete As Boolean = False

    Private Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        Try
            driverId = Logic.Conversion.ToInteger(Request.Params("did"))
            prevEmpID = Logic.Conversion.ToInteger(Request.Params("pid"))

            If Not Page.IsPostBack Then
                If driverId > 0 And prevEmpID > 0 Then
                    _LoadForm()
                    'Signature.DefaultSigUrl = "/_assets/DefaultAppSig.aspx?appId=" & Master.app.id
                Else
                    Response.Redirect("/Error.aspx")
                End If
            Else
                '_SaveForm()
                'Response.Redirect("/SPHSubmittalComplete.aspx?pid=" & prevEmpID)
                ''_SaveDocument()
            End If


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

        If Complete Then
            Response.Redirect("SPHSubmittalComplete.aspx?pid=" & prevEmpID)
        End If
    End Sub

    Private Sub _LoadForm()

        Dim cmdGet = "
            SELECT 
			Driver.LastName + ', ' + Driver.FirstName + ' ' + driver.MiddleName + ' ' + driver.Suffix AS DriverName,
			Driver.SSN,
			DriverEmploymentHistory.EmployedFrom,
			DriverEmploymentHistory.EmployedTo
            ,ISNULL(DriverEmploymentHistory.ResultReceived,0) AS ResultReceived

            FROM DriverEmploymentHistory LEFT JOIN Driver ON DriverEmploymentHistory.DriverID=Driver.ID
            WHERE DriverEmploymentHistory.DriverID=@DriverId AND DriverEmploymentHistory.ID=@ID"

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

            Dim cmd As New SqlCommand(cmdGet, connection)
            cmd.Parameters.AddWithValue("DriverId", driverId)
            cmd.Parameters.AddWithValue("ID", prevEmpID)

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

                If dReader("ResultReceived") = False Then
                    txtName.Text = Logic.Conversion.ToString(dReader("DriverName"))
                    txtSSN.Text = "XXX-XX-" & Right(Logic.Conversion.ToString(dReader("SSN")), 4)
                    txtEmploymentDates.Text = Logic.Conversion.ToString(dReader("EmployedFrom")) & " - " & Logic.Conversion.ToString(dReader("EmployedTo"))

                    'ynTestPositive.Checked = Logic.Conversion.ToNullableBoolean(dReader("ControlledSubstancesTestPositive"))
                    'txtProfessional.Text = Logic.Conversion.ToString(dReader("ControlledSubstancesProfessional"))
                    'txtProfessionalPhone.Text = Logic.Conversion.ToString(dReader("ControlledSubstancesProfessionalPhone"))
                    'txtReturnToDutyTest.Text = Logic.Conversion.ToString(dReader("ControlledSubstancesReturnToDutyTest"))
                    'Signature.SignatureId = Logic.Conversion.ToNullableInteger(dReader("ControlledSubstancesSignatureId"))
                    Complete = False
                Else
                    Complete = True
                End If
            End If

        End Using

    End Sub

    Private Sub _SaveForm()

    End Sub

    Protected Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
        Try

            Dim cmdSave = "UPDATE DriverEmploymentHistory SET 
                        ResultReceived=1, ResultReceivedDate=GETDATE(), ResultReceivedMethod=@ResultReceivedMethod, ResultDateCorrect=@ResultDateCorrect, ResultDateComment=@ResultDateComment, 
                        ResultDriverCommercialVehicle=@ResultDriverCommercialVehicle, ResultWasSafe=@ResultWasSafe, ResultAccidents=@ResultAccidents, ResultReasonLeaving=@ResultReasonLeaving, ResultPositiveControlledSubstance=@ResultPositiveControlledSubstance, 
                        ResultAlcohol=@ResultAlcohol, ResultRefusedTest=@ResultRefusedTest, ResultRehab=@ResultRehab, ResultViolatePolicy=@ResultViolatePolicy, ResultComments=@ResultComments, ResultName=@ResultName, ResultTitle=@ResultTitle
                        WHERE ID=@ID;INSERT INTO DriverEmploymentHistoryWebLinkLog (DriverEmploymentHistoryID,SubmitDate,SubmitBy,SubmitTitle,SubmitIP) VALUES (@ID,GETDATE(),@ResultName,@ResultTitle,@IP);"

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

                'Dim testPositive = ynTestPositive.Checked
                'Dim sigId = Signature.SignatureId

                Dim cmd As New SqlCommand(cmdSave, connection)
                With cmd.Parameters
                    .AddWithValue("ID", prevEmpID)
                    .AddWithValue("ResultReceivedMethod", "Email")
                    .AddWithValue("ResultDateCorrect", IIf(yn1.Checked, "Yes", "No"))
                    .AddWithValue("ResultDateComment", txtEmploymentDatesCorrected.Text)
                    .AddWithValue("ResultDriverCommercialVehicle", IIf(yn2.Checked, "Yes", "No"))
                    .AddWithValue("ResultWasSafe", IIf(yn3.Checked, "Yes", "No"))
                    .AddWithValue("ResultAccidents", IIf(yn4.Checked, "Yes", "No"))
                    .AddWithValue("ResultReasonLeaving", ddlReasonLeft.SelectedValue)
                    .AddWithValue("ResultPositiveControlledSubstance", IIf(yn6.Checked, "Yes", "No"))
                    .AddWithValue("ResultAlcohol", IIf(yn7.Checked, "Yes", "No"))
                    .AddWithValue("ResultRefusedTest", IIf(yn8.Checked, "Yes", "No"))
                    .AddWithValue("ResultRehab", ddl9.SelectedValue)
                    .AddWithValue("ResultViolatePolicy", IIf(yn10.Checked, "Yes", "No"))

                    .AddWithValue("ResultComments", txtComments.Text)
                    .AddWithValue("ResultName", txtByName.Text)
                    .AddWithValue("ResultTitle", txtByTitle.Text)
                    .AddWithValue("IP", Request.UserHostAddress)

                End With

                cmd.ExecuteNonQuery()

                If yn1.Checked = False Or yn3.Checked = False Or yn4.Checked Or yn6.Checked Or yn7.Checked Or yn8.Checked Or yn10.Checked Or txtComments.Text <> "" Then
                    SendEmail()
                End If

                Response.Redirect("SPHSubmittalComplete.aspx?pid=" & prevEmpID)

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

    Private Sub SendEmail()
        Dim Connection As New SqlConnection(Logic.Database.ConnectionString)
        Dim cmd As New System.Data.SqlClient.SqlCommand
        Dim DR As System.Data.SqlClient.SqlDataReader
        Dim EmailTo As String = "support@trucksafety.com"
        Dim EmailToContractEmail As String = ""
        Dim EmailToContractName As String = ""

        ' Get email template
        Dim sender As String
        Dim subject As String
        Dim template As String
        Dim cmdCompany As New SqlCommand(
            "SELECT e.Address,c.emailSPHResponseReviewSubject,emailSPHResponseReviewBody
            FROM Company c LEFT JOIN CompanyEmails e ON e.id=c.emailSPHResponseReviewFromId",
            Connection)
        Connection.Open()

        Using dReader = cmdCompany.ExecuteReader()
            If Not dReader.Read() OrElse String.IsNullOrEmpty(dReader(0)) Then
                Exit Sub
            End If
            sender = dReader(0)
            subject = dReader(1)
            template = dReader(2)
        End Using

        ' Get info and email recipients
        Dim ApplicantName As String = ""
        Dim PrevEmployer As String = ""
        Dim ProspEmployer As String = ""
        Dim RequestMade As Date = Now.Date
        Dim SigID As Int64 = 0
        Dim cmdContract As New SqlCommand(
            "SELECT 
			Driver.LastName + ', ' + Driver.FirstName + ' ' + driver.MiddleName + ' ' + driver.Suffix AS DriverName,
			Driver.SSN,
			DriverEmploymentHistory.EmployedFrom,
			DriverEmploymentHistory.EmployedTo,
			DriverEmploymentHistory.CompanyName,DriverEmploymentHistory.Address,DriverEmploymentHistory.City,DriverEmploymentHistory.State,DriverEmploymentHistory.zip,DriverEmploymentHistory.Phone,DriverEmploymentHistory.fax
			,web_DriverApplication.CheckDrivingRecordSignatureId,web_DriverApplication.dateSubmitted
			,Customer.Name AS ProsName
            ,ISNULL(DriverEmploymentHistory.webAppSafetyHistoryReleaseSignatureID,0) AS webAppSafetyHistoryReleaseSignatureID
            ,ISNULL(ServiceContract.emailSPHResponsesEmail,'') AS emailSPHResponsesEmail, ISNULL(ServiceContract.emailSPHResponsesName,'') AS emailSPHResponsesName
            FROM DriverEmploymentHistory LEFT JOIN Driver ON DriverEmploymentHistory.DriverID=Driver.ID LEFT JOIN Employer ON DriverEmploymentHistory.EmployerID=Employer.ID
			LEFT JOIN web_DriverApplication ON Driver.WebAppId=web_DriverApplication.ID LEFT JOIN ServiceContract ON Driver.ContractID=ServiceContract.ID LEFT JOIN Customer ON ServiceContract.CustomerID=Customer.ID
            WHERE DriverEmploymentHistory.ID=@ID",
            Connection)
        cmdContract.Parameters.AddWithValue("ID", prevEmpID)
        Using dReader = cmdContract.ExecuteReader()
            If Not dReader.Read() OrElse IsDBNull(dReader(1)) Then
                Exit Sub
            End If
            ApplicantName = dReader("DriverName")
            PrevEmployer = dReader("CompanyName")
            ProspEmployer = dReader("ProsName")
            SigID = dReader("webAppSafetyHistoryReleaseSignatureID")
            EmailToContractEmail = dReader("emailSPHResponsesEmail")
            EmailToContractName = dReader("emailSPHResponsesName")
        End Using


        ' Parameters for our template
        Dim body As String = template
        'Dim body = template _
        '    .Replace("#APPLICANTNAME#", CStr(ApplicantName)) _
        '    .Replace("#PREVEMPLOYERNAME#", CStr(PrevEmployer)) _
        '    .Replace("#PROSEMPLOYERNAME#", CStr(ProspEmployer)) _
        '    .Replace("#REQUESTDATE#", RequestMade) _
        '    .Replace("#LINK#", Link)


        subject += " - " & ApplicantName

        Dim ReportingSetup As SetupClass
        Dim cmd2 As New SqlCommand("SELECT name,address1,city,state,zip,phone FROM Company", Connection)
        Dim dReader2 = cmd2.ExecuteReader()
        dReader2.Read()
        ReportingSetup = New SetupClass(
                Logic.Database.ConnectionString,
                dReader2("name"), dReader2("address1"),
                CStr(dReader2("city")) & ", " & dReader2("state") & CStr(dReader2("zip")).TrimEnd({"-"c, " "c}),
                dReader2("phone"))
        dReader2.Close()

        Dim docData = ReportingClass.Reporting.SPHResponseReportSave(ReportingSetup, prevEmpID, SigID, False)
        Dim email As New Logic.AutomationServer_EmailsSent() With {
                .EmailTo = EmailTo,
                .EmailSubject = subject,
                .EmailFrom = sender,
                .EmailFromName = sender,
                .EmailBody = body,
                .KeepSubject = True,
                .EmailAttachmentName = "SPHResponse.pdf",
                .EmailAttachmentData = docData.Item2,
                .EmailAttachmentLength = docData.Item1
            }
        email.Create(Connection)

        If EmailToContractEmail <> "" Then
            Dim email2 As New Logic.AutomationServer_EmailsSent() With {
                .EmailTo = EmailToContractEmail,
                .EmailSubject = subject,
                .EmailFrom = sender,
                .EmailFromName = sender,
                .EmailBody = body,
                .KeepSubject = True,
                .EmailAttachmentName = "SPHResponse.pdf",
                .EmailAttachmentData = docData.Item2,
                .EmailAttachmentLength = docData.Item1
            }
            email2.Create(Connection)

        End If


    End Sub

End Class