Imports System.Data.SqlClient
Imports Logic.DateExtensions
Imports System.Web.UI


Public Class DocumentsUploads
    Inherits System.Web.UI.Page

    Property contractId As Integer
    Property itemContext As String
    Property itemId As Integer

    Dim FileCount As Int16 = 25

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        contractId = Logic.Conversion.ToInteger(Request.Params("cid"))
        Master.CurrentClient.VerifyContractAccess(contractId)
        'see if loaded from another page
        itemContext = Logic.Conversion.ToString(Request.Params("ctx"))
        itemId = Logic.Conversion.ToInteger(Request.Params("itemid"))

        If Not Page.IsPostBack Then
            System.Web.HttpContext.Current.Session.Add("FileUploadCount", 0)
            For i = 0 To FileCount
                System.Web.HttpContext.Current.Session.Add("FileUpload" & i, "")
            Next

            LoadCatagories()

            Select Case itemContext
                Case "Driver"
                    ddlCategory.SelectedValue = 3
                Case "Accident"
                    ddlCategory.SelectedValue = 1
                Case "RoadsideInspection"
                    ddlCategory.SelectedValue = 7
                Case "Training"
                    ddlCategory.SelectedValue = 8
                Case "Vehicle"
                    ddlCategory.SelectedValue = 9
            End Select

            If ddlCategory.SelectedItem.Value > 0 Then
                LoadSubCategorys(ddlCategory.SelectedItem.Value)
                Select Case ddlCategory.SelectedItem.Text

                    Case "Accident"
                        LoadAccidents(contractId)
                    Case "Driver"
                        LoadDrivers(contractId)
                    Case "Drug And Alcohol"
                        LoadDrugTest(contractId)
                    Case "Carrier Registration & Permits"
                        LoadRegistrationDocs(contractId)
                    Case "Roadside Inspection"
                        LoadRoadSideInspections(contractId)
                    Case "Training"
                        LoadTraining(contractId)
                    Case "Vehicle"
                        LoadVehicles(contractId)
                    Case "Client Contact"
                        LoadClientContact(contractId)
                    Case "Contract Note"
                        LoadContractNote(contractId)

                End Select

                If itemId <> 0 Then
                    ddlExtra.SelectedValue = itemId
                End If
            End If
        End If

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

        If perms.Contains("Document Upload - Mark as Reviewed") Then
            phMarkDocumentsAsReviewed.Visible = True
        Else
            phMarkDocumentsAsReviewed.Visible = False
        End If

        If ddlCategory.SelectedIndex > 0 Then
            phDocUploader.Visible = True
        Else
            phDocUploader.Visible = False
        End If



    End Sub

    Private Sub LoadCatagories()
        Dim cmd As New System.Data.SqlClient.SqlCommand
        Dim DR As System.Data.SqlClient.SqlDataReader
        Dim connection As New SqlConnection(Logic.Database.connectionString)
        Dim perms = Master.CurrentClient.Permissions

        Try
            ddlCategory.Items.Clear()
            ddlCategory.Items.Add(New ListItem("", 0))
            connection.Open()
            cmd.Connection = connection
            cmd.CommandText = "SELECT id,Name FROM DocumentCatagoriesNew ORDER BY Name"
            DR = cmd.ExecuteReader
            While DR.Read
                If perms.Contains("Upload Documents - " & DR("Name")) Then
                    ddlCategory.Items.Add(New ListItem(DR("Name"), DR("ID")))
                End If
            End While
            DR.Close()
        Catch ex As Exception
            'add an error redirect here. 
        Finally
            connection.Close()
        End Try
    End Sub


    Private Sub LoadSubCategorys(CatagoryId As Int64)

        'Load all sub catagories listed in DocumentManagerSubCatagories
        Dim cmd As New System.Data.SqlClient.SqlCommand
        Dim DR As System.Data.SqlClient.SqlDataReader
        Dim connection As New SqlConnection(Logic.Database.connectionString)

        If Not ddlCategory.SelectedItem.Value = "" Then
            Try
                ddlSubCategory.Items.Clear()
                connection.Open()

                ddlSubCategory.Items.Add(New ListItem("", 0))

                cmd.Connection = connection
                cmd.CommandText = "SELECT Id , Name FROM DocumentsSubCategory Where CategoryId = @CatagoryId ORDER BY Name,ID"
                cmd.Parameters.Add(New SqlClient.SqlParameter("@CatagoryId", SqlDbType.BigInt)).Value = CatagoryId
                DR = cmd.ExecuteReader
                While DR.Read
                    ddlSubCategory.Items.Add(New ListItem(DR("Name"), DR("ID")))
                End While
                DR.Close()

            Catch ex As Exception
                'Write in an catch exception here

            Finally
                connection.Close()
            End Try
        Else
            'Un hide the message please select a catagory firs warning message'
            'Or unhide the DDLSubCategory'
        End If

    End Sub

    Private Sub LoadAccidents(ContractID As Int64) ' going to setup to be done with active clients only.
        Dim cmd As New System.Data.SqlClient.SqlCommand
        Dim connection As New SqlConnection(Logic.Database.ConnectionString)
        Dim DR As System.Data.SqlClient.SqlDataReader
        Try
            ddlExtra.Items.Add(New ListItem("", 0))

            connection.Open()
            cmd.Connection = connection
            cmd.CommandText = "SELECT AccidentDate, DriverName,CityState,ID FROM Accident where ContractID = @ContractId   ORDER BY AccidentDate DESC,DriverName,ID"
            cmd.Parameters.Add(New SqlParameter("@ContractId", SqlDbType.BigInt)).Value = ContractID
            DR = cmd.ExecuteReader()

            While DR.Read
                ddlExtra.Items.Add(New ListItem(Format(DR("AccidentDate"), "MM/dd/yyyy") + ", " + DR("DriverName") + ", " + DR("CityState"), DR("ID")))
            End While
            DR.Close()
        Catch ex As Exception
            'Me.HandleException("An Error Occurred while Loading Accidents", ex)
        Finally
            connection.Close()
        End Try


    End Sub
    Private Sub LoadDrugTest(ContractID As Int64) ' going to setup to be done with active clients only.
        Dim cmd As New System.Data.SqlClient.SqlCommand
        Dim connection As New SqlConnection(Logic.Database.ConnectionString)
        Dim DR As System.Data.SqlClient.SqlDataReader
        Try
            ddlExtra.Items.Add(New ListItem("", 0))

            connection.Open()
            cmd.Connection = connection
            cmd.CommandText = "  SELECT TestDate ,DriverName , DrugAlcoholTestType.Name as TestTypeName, DrugAlcoholTest.ID as TestID FROM DrugAlcoholTest left join DrugAlcoholTestType on TestTypeID = DrugAlcoholTestType.ID where ContractID = @ContractId  ORDER BY TestDate DESC,DriverName,DrugAlcoholTest.ID"
            cmd.Parameters.Add(New SqlParameter("@ContractId", SqlDbType.BigInt)).Value = ContractID
            DR = cmd.ExecuteReader()
            While DR.Read
                ddlExtra.Items.Add(New ListItem(Format(DR("TestDate"), "MM/dd/yyyy") + ", " + DR("DriverName") + ", " + DR("TestTypeName"), DR("TestID")))
            End While
            DR.Close()
        Catch ex As Exception
            'Me.HandleException("An Error Occurred while Loading DrugTest", ex)
        Finally
            connection.Close()
        End Try
    End Sub
    Private Sub LoadRoadSideInspections(ContractID As Int64) ' going to setup to be done with active clients only.
        Dim cmd As New System.Data.SqlClient.SqlCommand
        Dim connection As New SqlConnection(Logic.Database.ConnectionString)
        Dim DR As System.Data.SqlClient.SqlDataReader
        Try
            ddlExtra.Items.Add(New ListItem("", 0))

            connection.Open()
            cmd.Connection = connection
            cmd.CommandText = "SELECT InspectionDate, DriverName , Vehicle1Desc , RoadsideInspection.ID as RoadSideInspectionID, Jurisdiction FROM RoadsideInspection where RoadsideInspection.ContractID = @ContractId ORDER BY InspectionDate DESC,DriverName,RoadsideInspection.ID"
            cmd.Parameters.Add(New SqlParameter("@ContractId", SqlDbType.BigInt)).Value = ContractID
            DR = cmd.ExecuteReader()
            While DR.Read
                ddlExtra.Items.Add(New ListItem(Format(DR("InspectionDate"), "MM/dd/yyyy") + ",  " + DR("DriverName") + ", " + DR("Vehicle1Desc") + ", " + DR("RoadSideInspectionID").ToString() + ", " + DR("Jurisdiction"), DR("RoadSideInspectionID")))
            End While
            DR.Close()
        Catch ex As Exception
            'Me.HandleException("An Error Occurred while Loading DrugTest", ex)
        Finally
            connection.Close()
        End Try
    End Sub

    Private Sub LoadRegistrationDocs(ContractID As Int64) ' going to setup to be done with active clients only.
        Dim cmd As New System.Data.SqlClient.SqlCommand
        Dim connection As New SqlConnection(Logic.Database.ConnectionString)
        Dim DR As System.Data.SqlClient.SqlDataReader
        Try
            ddlExtra.Items.Add(New ListItem("", 0))
            connection.Open()
            cmd.Connection = connection
            cmd.CommandText = "  select RegistrationDoc.ID as RegistrationDocID, RegistrationDocType.Name as RegistrationDocName from RegistrationDoc left join RegistrationDocType on TypeID = RegistrationDocType.ID where ContractID = @ContractId ORDER BY RegistrationDocType.Name,RegistrationDoc.ID"
            cmd.Parameters.Add(New SqlParameter("@ContractId", SqlDbType.BigInt)).Value = ContractID
            DR = cmd.ExecuteReader()
            While DR.Read
                ddlExtra.Items.Add(New ListItem(DR("RegistrationDocName") + ", " + DR("RegistrationDocID").ToString(), DR("RegistrationDocID")))
            End While
            DR.Close()
        Catch ex As Exception
            'Me.HandleException("An Error Occurred while Loading RegistrationDocs", ex)
        Finally
            connection.Close()
        End Try

    End Sub
    Private Sub LoadDrivers(ContractId As Int64)
        'Load all of the drivers by the Contract Id
        Dim cmd As New System.Data.SqlClient.SqlCommand
        Dim DR As System.Data.SqlClient.SqlDataReader
        Dim connection As New SqlConnection(Logic.Database.ConnectionString)
        Dim sql As String = ""
        Try
            ddlExtra.Items.Add(New ListItem("", 0))

            connection.Open()
            cmd.Connection = connection
            If chkActiveItemOnly.Checked Then
                sql = "SELECT ID, LastName, FirstName, MiddleName FROM Driver where ContractID = @contractID AND ISNULL(Driver.Terminated,0)=0 Order by LastName,FIrstName,MiddleName,Driver.ID"
            Else
                sql = "SELECT ID, LastName, FirstName, MiddleName FROM Driver where ContractID = @contractID Order by LastName,FIrstName,MiddleName,Driver.ID"
            End If
            cmd.CommandText = sql
            cmd.Parameters.Add(New SqlClient.SqlParameter("@contractID", SqlDbType.BigInt)).Value = ContractId
            DR = cmd.ExecuteReader
            While DR.Read
                ddlExtra.Items.Add(New ListItem(DR("LastName") + ", " + DR("FirstName") + " " + DR("MiddleName"), DR("ID")))
            End While
            DR.Close()

        Catch ex As Exception
            'Write in an catch exception here
            ' Me.HandleException("An Error Occurred while Loading Drivers", ex)
        Finally
            connection.Close()
        End Try

    End Sub

    Private Sub LoadTraining(ContractId As Int64)
        'Load all of the drivers by the Contract Id
        Dim cmd As New System.Data.SqlClient.SqlCommand
        Dim DR As System.Data.SqlClient.SqlDataReader
        Dim connection As New SqlConnection(Logic.Database.ConnectionString)

        Try
            ddlExtra.Items.Add(New ListItem("", 0))

            connection.Open()
            cmd.Connection = connection
            cmd.CommandText = "  SELECT DriverName, TrainingTopic.Name as TrainingTopic, SpecificTopic , TrainingDate, Training.ID  FROM Training left join TrainingTopic on TopicID = TrainingTopic.ID where ContractID = @contractID ORDER BY TrainingTopic.Name,DriverName"
            cmd.Parameters.Add(New SqlClient.SqlParameter("@contractID", SqlDbType.BigInt)).Value = ContractId
            DR = cmd.ExecuteReader
            While DR.Read
                ddlExtra.Items.Add(New ListItem(DR("TrainingTopic") + " - " + DR("DriverName") + " - " + DR("SpecificTopic") + ", " + Format(DR("TrainingDate"), "MM/dd/yyyy") + " " + DR("ID").ToString(), DR("ID")))
            End While
            DR.Close()

        Catch ex As Exception
            'Write in an catch exception here
            ' Me.HandleException("An Error Occurred while Loading Drivers", ex)
        Finally
            connection.Close()
        End Try

    End Sub

    Private Sub LoadVehicles(ContractId As Int64)
        'Load all of the Vehicals by the Contract Id
        Dim cmd As New System.Data.SqlClient.SqlCommand
        Dim DR As System.Data.SqlClient.SqlDataReader
        Dim connection As New SqlConnection(Logic.Database.ConnectionString)
        Dim sql As String = ""

        Try
            ddlExtra.Items.Add(New ListItem("", 0))

            connection.Open()
            cmd.Connection = connection

            If chkActiveItemOnly.Checked Then
                sql = "SELECT ID ,ContractID ,Unit,UnitYear,Make,Model,VIN FROM Vehicle Where ContractID=@contractId AND ISNULL(Vehicle.NotInService,0)=0 ORDER BY Unit"
            Else
                sql = "SELECT ID ,ContractID ,Unit,UnitYear,Make,Model,VIN FROM Vehicle Where ContractID=@contractId ORDER BY Unit"
            End If

            cmd.CommandText = sql
            cmd.Parameters.Add(New SqlClient.SqlParameter("@contractID", SqlDbType.BigInt)).Value = ContractId
            DR = cmd.ExecuteReader
            While DR.Read
                ddlExtra.Items.Add(New ListItem(DR("Unit") & " - " & DR("UnitYear") & " (Make: " & DR("Make") & ") (Model:" & DR("Model") & ")", DR("ID")))
            End While
            DR.Close()

        Catch ex As Exception
            'Write in an catch exception here
            'Me.HandleException("An Error Occurred while Loading vehicals", ex)
        Finally
            connection.Close()
        End Try

    End Sub


    Private Sub LoadClientContact(ContractId As Int64)
        'Load all of the Vehicals by the Contract Id
        Dim cmd As New System.Data.SqlClient.SqlCommand
        Dim DR As System.Data.SqlClient.SqlDataReader
        Dim connection As New SqlConnection(Logic.Database.ConnectionString)

        Try
            ddlExtra.Items.Add(New ListItem("", 0))

            connection.Open()
            cmd.Connection = connection
            cmd.CommandText = "SELECT CustomerContact.ID ,CustomerContact.ContractID,CustomerContactType.Name AS ContactType,ISNULL(Employee.LastName,'')+','+ISNULL(Employee.FirstName,'') AS EmployeeName,CustomerContact.StartTime,CustomerContact.EndTime,CustomerContact.ShortDescription FROM CustomerContact LEFT JOIN CustomerContactType ON CustomerContact.ContactTypeID=CustomerContactType.ID LEFT JOIN Employee ON CustomerContact.EmployeeID=Employee.ID Where ContractID=@contractId ORDER BY CustomerContactType.Name,CustomerContact.StartTime,ISNULL(Employee.LastName,'')+','+ISNULL(Employee.FirstName,''),CustomerContact.ID"
            cmd.Parameters.Add(New SqlClient.SqlParameter("@contractID", SqlDbType.BigInt)).Value = ContractId
            DR = cmd.ExecuteReader
            While DR.Read
                ddlExtra.Items.Add(New ListItem(DR("ContactType") & " - " & DR("EmployeeName") & " - " & Format(DR("StartTime"), "MM/dd/yyyy") & " - " & DR("ShortDescription"), DR("ID")))
            End While
            DR.Close()

        Catch ex As Exception
            'Write in an catch exception here
            'Me.HandleException("An Error Occurred while Loading Client Contacts", ex)
        Finally
            connection.Close()
        End Try

    End Sub

    Private Sub LoadContractNote(ContractId As Int64)
        'Load all of the Vehicals by the Contract Id
        Dim cmd As New System.Data.SqlClient.SqlCommand
        Dim DR As System.Data.SqlClient.SqlDataReader
        Dim connection As New SqlConnection(Logic.Database.ConnectionString)

        Try
            ddlExtra.Items.Add(New ListItem("", 0))

            connection.Open()
            cmd.Connection = connection
            cmd.CommandText = "SELECT ContractNote.ID ,ContractNote.ContractID,NoteDate,LEFT(CAST(Note AS VARCHAR),50) AS Note FROM ContractNote Where ContractID=@contractId ORDER BY NoteDate,ID"
            cmd.Parameters.Add(New SqlClient.SqlParameter("@contractID", SqlDbType.BigInt)).Value = ContractId
            DR = cmd.ExecuteReader
            While DR.Read
                ddlExtra.Items.Add(New ListItem(Format(DR("NoteDate"), "MM/dd/yyyy") & " - " & DR("Note"), DR("ID")))
            End While
            DR.Close()

        Catch ex As Exception
            'Write in an catch exception here
            'Me.HandleException("An Error Occurred while Loading Contract Notes", ex)
        Finally
            connection.Close()
        End Try

    End Sub

    Protected Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
        Dim connection As New SqlConnection(Logic.Database.ConnectionString)
        Dim Sql As String
        Dim SqlDoc As String
        Dim sqlLog As String = ""
        Dim cmd As New SqlCommand
        Dim cmdDoc As New SqlCommand
        Dim cmdLog As New SqlCommand
        Dim bData As Byte()
        Dim DR As SqlDataReader
        Dim FileName As String = ""
        Dim FullFileName As String = ""
        Dim DocID As Int64 = 0

        Dim FilesReady As Boolean = False
        Try
            btnSave.Enabled = False

            For i = 0 To FileCount
                If System.Web.HttpContext.Current.Session("FileUpload" & i) <> "" Then
                    FilesReady = True
                    Exit For
                End If
            Next

            If contractId > 0 And ddlCategory.SelectedIndex > 0 And FilesReady Then
                connection.Open()
                cmd.Connection = connection
                If chkMarkDocumentAsReviewed.checked = False Then
                    Sql = "Insert Into DocumentsNew (ContractId,ItemID,CategoryID,SubCategoryId,FileType,DocName,DocSize,EnteredDate,EnteredBy,EditedDate,EditedBy,UploadNotes) "
                    Sql += " VALUES(@ContractId,@ItemID,@CategoryID,@SubCategoryId,@FileType,@DocName,@DocSize,GETDATE(),@EnteredBy,GETDATE(),@EnteredBy,@UploadNotes);SELECT scope_identity();"
                Else
                    Sql = "Insert Into DocumentsNew (ContractId,ItemID,CategoryID,SubCategoryId,FileType,DocName,DocSize,EnteredDate,EnteredBy,EditedDate,EditedBy,UploadNotes,DocReviewed,DocReviewedDate,DocReviewedBy) "
                    Sql += " VALUES(@ContractId,@ItemID,@CategoryID,@SubCategoryId,@FileType,@DocName,@DocSize,GETDATE(),@EnteredBy,GETDATE(),@EnteredBy,@UploadNotes,1,GETDATE(),@EnteredBy);SELECT scope_identity();"
                End If
                cmd.CommandText = Sql

                    cmd.Parameters.Add(New SqlParameter("@ContractId", SqlDbType.BigInt)).Value = contractId
                    cmd.Parameters.Add(New SqlParameter("@CategoryID", SqlDbType.BigInt)).Value = ddlCategory.SelectedItem.Value
                    If ddlSubCategory.SelectedIndex >= 0 Then
                        cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@SubCategoryId", SqlDbType.BigInt)).Value = ddlSubCategory.SelectedItem.Value
                    Else
                        cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@SubCategoryId", SqlDbType.BigInt)).Value = 0 'DBNull.Value
                    End If
                    If ddlExtra.SelectedIndex >= 0 Then
                        cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@ItemID", SqlDbType.BigInt)).Value = ddlExtra.SelectedItem.Value
                    Else
                        cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@ItemID", SqlDbType.BigInt)).Value = 0
                    End If
                    cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@DocName", SqlDbType.VarChar)).Value = ""
                    cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@FileType", SqlDbType.VarChar)).Value = ""
                    cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@EnteredBy", SqlDbType.VarChar)).Value = Master.CurrentClient.username
                    cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@UploadNotes", SqlDbType.VarChar)).Value = txtNotes.Text
                    cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Deleted", SqlDbType.Bit)).Value = False
                    cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@DocSize", SqlDbType.BigInt)).Value = 0

                    cmdDoc.Parameters.Add(New System.Data.SqlClient.SqlParameter("@DocBlob", SqlDbType.VarBinary)).Value = DBNull.Value
                    cmdDoc.Parameters.Add(New System.Data.SqlClient.SqlParameter("@DocSize", SqlDbType.BigInt)).Value = 0
                    cmdDoc.Parameters.Add(New System.Data.SqlClient.SqlParameter("@DocumentID", SqlDbType.BigInt)).Value = 0
                    cmdDoc.Parameters.Add(New System.Data.SqlClient.SqlParameter("@FileType", SqlDbType.VarChar)).Value = ""


                    'log
                    Dim Edits As String = ""
                    cmdLog.Connection = connection
                    sqlLog = "INSERT INTO DocumentsNewLog (ContractId,DocumentID,EditDate,EditBy,EditSource,EditAction,OrigCategory,OrigSubCategory,OrigItem,OrigNotes,OrigDocName) "
                    sqlLog += " VALUES (@ContractId,@DocumentID,GETDATE(),@EditBy,@EditSource,@EditAction,@OrigCategory,@OrigSubCategory,@OrigItem,@OrigNotes,@OrigDocName)"
                    cmdLog.CommandText = sqlLog
                    cmdLog.Parameters.Add(New System.Data.SqlClient.SqlParameter("@ContractId", SqlDbType.BigInt)).Value = contractId
                    cmdLog.Parameters.Add(New System.Data.SqlClient.SqlParameter("@DocumentID", SqlDbType.BigInt)).Value = 0
                    cmdLog.Parameters.Add(New System.Data.SqlClient.SqlParameter("@EditBy", SqlDbType.VarChar)).Value = Master.CurrentClient.username
                    cmdLog.Parameters.Add(New System.Data.SqlClient.SqlParameter("@EditSource", SqlDbType.VarChar)).Value = "PORTAL"
                    cmdLog.Parameters.Add(New System.Data.SqlClient.SqlParameter("@EditAction", SqlDbType.VarChar)).Value = "ADD"
                    cmdLog.Parameters.Add(New System.Data.SqlClient.SqlParameter("@OrigCategory", SqlDbType.VarChar)).Value = ddlCategory.SelectedItem.Text
                cmdLog.Parameters.Add(New System.Data.SqlClient.SqlParameter("@OrigSubCategory", SqlDbType.VarChar)).Value = ddlSubCategory.SelectedItem.Text
                If ddlExtra.SelectedItem Is Nothing Then
                    cmdLog.Parameters.Add(New System.Data.SqlClient.SqlParameter("@OrigItem", SqlDbType.VarChar)).Value = ""
                Else
                    cmdLog.Parameters.Add(New System.Data.SqlClient.SqlParameter("@OrigItem", SqlDbType.VarChar)).Value = ddlExtra.SelectedItem.Text
                End If
                cmdLog.Parameters.Add(New System.Data.SqlClient.SqlParameter("@OrigNotes", SqlDbType.VarChar)).Value = txtNotes.Text
                cmdLog.Parameters.Add(New System.Data.SqlClient.SqlParameter("@OrigDocName", SqlDbType.VarChar)).Value = ""

                    For i = 0 To FileCount - 1
                        FileName = System.Web.HttpContext.Current.Session("FileUpload" & i)
                        Dim t As String() = Split(FileName, ".")

                        If System.IO.File.Exists(HttpContext.Current.Server.MapPath("~/DocUploads/" & FileName)) Then
                            If FileName <> "" Then
                                cmd.Parameters("@DocName").Value = FileName
                                cmd.Parameters("@FileType").Value = t(UBound(t))

                                FullFileName = HttpContext.Current.Server.MapPath("~/DocUploads/" & FileName)

                                Dim br As System.IO.BinaryReader = New System.IO.BinaryReader(System.IO.File.OpenRead(FullFileName))
                                bData = br.ReadBytes(br.BaseStream.Length)
                                Dim ms As System.IO.MemoryStream = New System.IO.MemoryStream(bData, 0, bData.Length)
                                ms.Write(bData, 0, bData.Length)
                                cmd.Parameters("@DocSize").Value = bData.Length
                                ms.Close()
                                br.Close()
                                ms = Nothing
                                br = Nothing

                                DocID = cmd.ExecuteScalar

                                'save to specific database
                                Dim connectionDoc As New SqlConnection(Logic.Database.DocumentMannagerConnectionString(ddlCategory.SelectedItem.Text))
                                connectionDoc.Open()
                                cmdDoc.Connection = connectionDoc
                                cmdDoc.Parameters("@DocBlob").Value = bData
                                cmdDoc.Parameters("@DocSize").Value = bData.Length
                                cmdDoc.Parameters("@DocumentID").Value = DocID
                                cmdDoc.Parameters("@FileType").Value = t(UBound(t))
                                SqlDoc = "INSERT INTO Documents (DocumentID,DocBlob,DocSize,FileType) VALUES (@DocumentID,@DocBlob,@DocSize,@FileType)"
                                cmdDoc.CommandText = SqlDoc
                                cmdDoc.ExecuteNonQuery()
                                connectionDoc.Close()

                                bData = Nothing

                                cmdLog.Parameters("@DocumentID").Value = DocID
                                cmdLog.Parameters("@OrigDocName").Value = FileName
                                cmdLog.ExecuteNonQuery()

                            End If

                            If System.IO.File.Exists(FullFileName) Then
                                System.IO.File.Delete(FullFileName)
                            End If
                        End If
                    Next

                    System.Web.HttpContext.Current.Session("FileUploadCount") = 0
                    For i = 0 To FileCount
                        System.Web.HttpContext.Current.Session("FileUpload" & i) = ""
                    Next

                    'clear the form
                    ddlCategory.SelectedIndex = -1
                    ddlSubCategory.Items.Clear()
                    ddlExtra.Items.Clear()
                    txtNotes.Text = ""
                lblError.Text = ""
                chkMarkDocumentAsReviewed.Checked = False
            Else
                    lblError.Text = "You have a Contract Selected, Category Selected, and 1 or more files selected before saving."
                'MessageBox.Show("Please select a Contract, Category, And file before the documents can be saved.")
            End If
        Catch ex As Exception
            'Me.HandleException("An Error Occurred while saving ", ex)
        Finally
            connection.Close()
            btnSave.Enabled = True
        End Try
    End Sub

    Private Sub ddlCategory_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ddlCategory.SelectedIndexChanged
        ddlExtra.SelectedIndex = -1
        ddlExtra.Items.Clear()

        If ddlCategory.SelectedIndex >= 0 Then
            LoadSubCategorys(ddlCategory.SelectedItem.Value)

            Select Case ddlCategory.SelectedItem.Text

                Case "Accident"
                    LoadAccidents(contractId)
                Case "Driver"
                    LoadDrivers(contractId)
                Case "Drug And Alcohol"
                    LoadDrugTest(contractId)
                Case "Carrier Registration & Permits"
                    LoadRegistrationDocs(contractId)
                Case "Roadside Inspection"
                    LoadRoadSideInspections(contractId)
                Case "Training"
                    LoadTraining(contractId)
                Case "Vehicle"
                    LoadVehicles(contractId)
                Case "Client Contact"
                    LoadClientContact(contractId)
                Case "Contract Note"
                    LoadContractNote(contractId)
            End Select
        End If
    End Sub

    Protected Sub btnUncategorizedDocs_Click(sender As Object, e As EventArgs) Handles btnUncategorizedDocs.Click
        Response.Redirect("DocumentsNew.aspx?ctx=uncat&id=0&cid=" & contractId)
        'DocumentsNew.aspx?ctx=Driver&id={0}&cid={1}
    End Sub

    Protected Sub btnOtherDocs_Click(sender As Object, e As EventArgs) Handles btnOtherDocs.Click
        Response.Redirect("DocumentsNew.aspx?ctx=other&id=0&cid=" & contractId)
        'DocumentsNew.aspx?ctx=Driver&id={0}&cid={1}
    End Sub

End Class