Imports System.Data.SqlClient
Imports System.Threading

Public Class GetDriver
    Inherits System.Web.UI.Page

    Property contractId As Integer
    Property agencyId As Integer
    Property driverId As Integer

    Protected Sub Page_Load() Handles Me.Load
        contractId = Logic.Conversion.ToInteger(Request.Params("contractId"))
        agencyId = Logic.Conversion.ToInteger(Request.Params("agencyId"))
        driverId = Logic.Conversion.ToInteger(Request.Params("driverId"))
        Try
            Master.Master.CurrentClient.VerifyContractAccess(contractId)

            Dim perms = Master.Master.CurrentClient.Permissions
            If Not perms.Contains("View Qualified Drivers") Then Throw New HttpException(403, "Forbidden")

            Using connection As New SqlConnection(Logic.Database.connectionString)
                connection.Open()
                Using transaction = connection.BeginTransaction()
                    _VerifyAccess(connection, transaction)
                    _MoveDriver(connection, transaction)
                    transaction.Commit()
                End Using
            End Using

            Response.Redirect("Recruiting.aspx?cid=" & contractId)

        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 _MoveDriver(connection As SqlConnection, transaction As SqlTransaction)
        Dim cmd As New SqlCommand("", connection, transaction)
        cmd.Parameters.AddWithValue("driverId", driverId)
        cmd.Parameters.AddWithValue("oldContractId", agencyId)
        cmd.Parameters.AddWithValue("newContractId", contractId)
        cmd.Parameters.AddWithValue("modifiedBy", "[Web: " + Master.Master.CurrentClient.username + "]")

        ' This does the actual move
        cmd.CommandText = "
            UPDATE DriverNote SET ServiceContractID=@newContractId WHERE DriverID=@driverId;
            UPDATE Training SET ContractID=@newContractId WHERE DriverID=@driverId;
            INSERT INTO EditLog (EditDate,EditBy,Item,FieldName,ItemId,OldValue,NewValue)
                VALUES (GETDATE(),@modifiedBy,'Driver','ContractID',@driverId,@oldContractId,@newContractId);
            UPDATE Driver SET ContractID=@newContractId WHERE id=@driverId;"
        cmd.ExecuteNonQuery()

        ' We need the app id
        cmd.CommandText = "SELECT WebAppId FROM Driver WHERE id=@driverId"
        Dim appId = Logic.Conversion.ToNullableInteger(cmd.ExecuteScalar())

        ' Move the application if it has one
        If appId.HasValue Then
            cmd.Parameters.AddWithValue("appId", appId.Value)
            cmd.CommandText = 
                "UPDATE web_DriverApplication SET clientId=@newContractId WHERE id=@appId;
                INSERT INTO EditLog (EditDate,EditBy,Item,FieldName,ItemId,OldValue,NewValue)
                    VALUES (GETDATE(),@modifiedBy,'WebApp','clientId',@appId,@oldContractId,@newContractId);"
            cmd.ExecuteNonQuery()
        End If

        If appId.HasValue Then 'chkSendEmail.Checked Then

            ' Create the "Move Application"
            cmd.CommandText = 
                "INSERT INTO web_DriverMoveApplication (appId,driverId,previousStatus,previousContractId,transferStartDate)
                    SELECT @appId,@driverId,status,@oldContractId,GETDATE() FROM web_DriverApplication WHERE id=@appId;
                SELECT @@IDENTITY;"
            Dim transferId As Integer = cmd.ExecuteScalar()

            ' Update application status to "transfer pending"
            cmd.CommandText = 
                "INSERT INTO EditLog (EditDate,EditBy,Item,FieldName,ItemId,OldValue,NewValue)
                    SELECT GETDATE(),@modifiedBy,'WebApp','status',@appId,status,'transfer pending'
                    FROM web_DriverApplication WHERE id=@appId;
                UPDATE web_DriverApplication SET status='transfer pending' WHERE id=@appId;"
            cmd.ExecuteNonQuery()

            ' Get the info needed for the email
            Dim driverEmail = "", driverName = ""
            cmd.CommandText = 
                "SELECT acct.email, acct.nameFirst,acct.nameMiddle,acct.nameLast,acct.nameSuffix
                FROM web_DriverApplication app LEFT JOIN web_DriverAccount acct ON acct.id=app.driverAccountId
                WHERE app.id = @appId"
            Using dReader = cmd.ExecuteReader()
                dReader.Read()
                driverEmail = dReader(0)
                driverName = RTrim(dReader(3) & ", " & dReader(1) & " " & dReader(2) & " " & dReader(4))
            End Using
            
            cmd.CommandText = "SELECT c.Name FROM ServiceContract sc LEFT JOIN Customer c ON c.ID=sc.CustomerID WHERE sc.ID=@oldContractId"
            Dim oldCustomerName As String = cmd.ExecuteScalar()
            cmd.CommandText = "SELECT c.Name FROM ServiceContract sc LEFT JOIN Customer c ON c.ID=sc.CustomerID WHERE sc.ID=@newContractId"
            Dim newCustomerName As String = cmd.ExecuteScalar()

            ' Build the email
            Dim settings = Logic.CompanySettings.LoadCached()
            Dim senderId = settings.emailReassignDriverFromId
            Dim subject = settings.emailReassignDriverSubject
            Dim template = settings.emailReassignDriverBody
            Dim params = New Dictionary(Of String, String) From {
                {"OLD_CUSTOMERNAME", oldCustomerName},
                {"NEW_CUSTOMERNAME", newCustomerName},
                {"DRIVERNAME", driverName},
                {"LINK", settings.WebAppBaseUrl & "Driver/TransferApplication.aspx?xferId=" & transferId}}
            For Each param In params
                template = template.Replace("#" & param.Key & "#", param.Value)
            Next
            template = template.Replace("'", "") ' in case other software doesn't sanitize
                        
            ' Send it
            Dim email As New Logic.AutomationServer_EmailsSent() With {
                .EmailFromId = senderId,
                .EmailTo = driverEmail,
                .EmailSubject = subject,
                .EmailBody = template}
            email.Create(connection, transaction)
                        
        End If

    End Sub

    Private Sub _VerifyAccess(connection As SqlConnection, transaction As SqlTransaction)
        Dim cmd As New SqlCommand("", connection, transaction)
        cmd.Parameters.AddWithValue("contractId", contractId)
        cmd.Parameters.AddWithValue("agencyId", agencyId)
        cmd.Parameters.AddWithValue("driverId", driverId)

        ' We can move from agency to contract
        cmd.CommandText = "SELECT COUNT(*) FROM ContractAgencyMap WHERE contractId=@contractId AND agencyId=@agencyId"
        If CInt(cmd.ExecuteScalar()) = 0 Then Throw New HttpException(403, "Forbidden")

        ' The driver is under that agency
        cmd.CommandText = "SELECT COUNT(*) FROM Driver WHERE ContractID=@agencyId AND ID=@driverId"
        If CInt(cmd.ExecuteScalar()) = 0 Then Throw New HttpException(403, "Forbidden")
    End Sub

End Class