Imports System.Data.SqlClient
Imports System.IO.Compression
Imports PdfSharp.Pdf
Imports PdfSharp.Pdf.IO
Imports System.IO

Public Class DocumentsNew
    Inherits System.Web.UI.Page

    Property contractId As Integer
    Property itemContext As String
    Property itemId As Integer
    Property appId As Int64

    Protected Sub Page_Load() Handles Me.Load
        Try
            Dim cid As Int64 = Logic.Conversion.ToInteger(Request.Params("cid"))
            itemContext = Logic.Conversion.ToString(Request.Params("ctx"))
            itemId = Logic.Conversion.ToInteger(Request.Params("id"))
            appId = Logic.Conversion.ToInteger(Request.Params("appid"))
            contractId = _GetContractId(itemContext, itemId)
            If cid <> 0 Then
                contractId = cid
            End If

            Master.CurrentClient.VerifyContractAccess(contractId)
            If Not _HasPerms(itemContext) Then Throw New HttpException(403, "Forbidden")

            Dim perms = Master.CurrentClient.Permissions
            If perms.Contains("Document Export") Then
                btnDownload.Visible = True
            Else
                btnDownload.Visible = False
            End If
            If perms.Contains("View Document Edit Page") Then
                'gvDocs.Attributes.Add("data-fixed-right-number", "3")
            Else
                Dim colCount = gvDocs.Columns.Count
                gvDocs.Columns(colCount - 2).Visible = False
                'gvDocs.Attributes.Add("data-fixed-right-number", "2")
            End If

            If Not Page.IsPostBack Then
                _FillData(itemContext, itemId, appId)
            End If

            dsDocs.ConnectionString = Logic.Database.ConnectionString
            dsDocs.SelectParameters("CatID").DefaultValue = _GetContextMapString(itemContext)
            dsDocs.SelectParameters("itemId").DefaultValue = itemId
            dsDocs.SelectParameters("ContractID").DefaultValue = contractId
            dsDocs.SelectParameters("ctx").DefaultValue = itemContext
            dsDocs.SelectParameters("appid").DefaultValue = appId

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

    Private Sub btnDownload_Click() Handles btnDownload.Click
        Dim CatID As Int64 = 0
        Select Case itemContext
            Case "Accident"
                CatID = 1
            Case "Driver"
                CatID = 3
            Case "DrugAlcoholTest"
                CatID = 4
            Case "RoadsideInspection"
                CatID = 7
            Case "Training"
                CatID = 8
            Case "Vehicle"
                CatID = 9
            Case "RegistrationDoc"
                CatID = 6
        End Select

        If CatID > 0 Then
            DownloadFiles(contractId, CatID, itemContext, itemId)
        End If

    End Sub

    Private Sub btnUploadDoc_Click() Handles btnUploadDoc.Click
        If itemId <> 0 Then
            Response.Redirect("DocumentsUploads.aspx?cid=" & contractId & "&ItemID=" & itemId & "&ctx=" & itemContext)
        End If
    End Sub

    Private Sub DownloadFiles(ByVal ContractID As String, ByVal CatID As String, ByVal CategoryName As String, ByVal ItemID As String)
        Dim cmd As New System.Data.SqlClient.SqlCommand
        Dim cmd2 As New System.Data.SqlClient.SqlCommand
        Dim DR As System.Data.SqlClient.SqlDataReader
        Dim DR2 As System.Data.SqlClient.SqlDataReader
        Dim connection As New SqlConnection(Logic.Database.ConnectionString)
        Dim Docconnection As New SqlConnection(Logic.Database.DocumentMannagerConnectionString(""))
        Dim FileName As String
        Dim sql As String = ""
        Dim FolderSubCatName As String

        Try
            Dim SourceFolder As String = HttpContext.Current.Server.MapPath("~/Exports/" & ContractID & "/" & CategoryName & "/" & ItemID)
            Dim DestFile As String = HttpContext.Current.Server.MapPath("~/Exports/" & CategoryName & "-" & ItemID & ".zip")

            If System.IO.File.Exists(DestFile) Then
                System.IO.File.Delete(DestFile)
            End If
            If System.IO.Directory.Exists(SourceFolder) Then
                System.IO.Directory.Delete(SourceFolder, True)
            End If
            If System.IO.File.Exists(DestFile) Then
                System.IO.File.Delete(DestFile)
            End If

            connection.Open()
            cmd.Connection = connection

            sql = "SELECT DocumentsNew.ID AS DocID,DocumentsNew.DocName,ISNULL(DocumentCatagoriesNew.Name,'No Category') AS Cat,ISNULL(DocumentsSubCategory.Name,'No Category') AS SubCat,DocumentsNew.FileType, 
                ISNULL(CASE 
                WHEN DocumentsNew.CategoryId=1 AND ISNULL(DocumentsNew.ItemID,0)>0 THEN ISNULL(Accident.DriverName,'') + '  -  ' + FORMAT(Accident.AccidentDate,'yyyy/MM/dd')
                When DocumentsNew.CategoryId=2 Then 'NO ITEMS'
                WHEN DocumentsNew.CategoryId=3 AND ISNULL(DocumentsNew.ItemID,0)>0 THEN Driver.LastName + ', ' + Driver.FirstName + ' ' + Driver.MiddleName + ' ' + Driver.Suffix 
                When DocumentsNew.CategoryId=4 AND ISNULL(DocumentsNew.ItemID,0)>0 Then ISNULL(DrugAlcoholTest.DriverName,'') + ' - ' + FORMAT(DrugAlcoholTest.TestDate,'yyyy/MM/dd') + ' - ' + ISNULL(DrugAlcoholTestType.Name,'')
                WHEN DocumentsNew.CategoryId=5 THEN 'NO ITEMS'
                When DocumentsNew.CategoryId=6 AND ISNULL(DocumentsNew.ItemID,0)>0 Then RegistrationDocType.Name + ' - ' + CAST(RegistrationDoc.ID AS VARCHAR)
                WHEN DocumentsNew.CategoryId=7 AND ISNULL(DocumentsNew.ItemID,0)>0 THEN RoadsideInspection.DriverName + ' - ' + FORMAT(RoadsideInspection.InspectionDate,'yyyy/MM/dd') + ' - ' + RoadsideInspection.Vehicle1Desc 
                When DocumentsNew.CategoryId=8 AND ISNULL(DocumentsNew.ItemID,0)>0 Then ISNULL(TrainingTopic.Name,'') + ' - ' + ISNULL(Training.DriverName,'')
                WHEN DocumentsNew.CategoryId=9 AND ISNULL(DocumentsNew.ItemID,0)>0 THEN Vehicle.Unit + ' - ' + Vehicle.UnitYear + ' - ' + Vehicle.Make + ' - ' + Vehicle.Model
                Else 'NO ITEMS'
                END,'') AS ItemDesc 
                FROM DocumentsNew 
                LEFT JOIN DocumentCatagoriesNew ON DocumentsNew.CategoryID=DocumentCatagoriesNew.id
                LEFT JOIN DocumentsSubCategory ON DocumentsNew.SubCategoryID=DocumentsSubCategory.ID 
                LEFT JOIN Accident ON DocumentsNew.ItemID=Accident.ID AND DocumentsNew.CategoryID=1
                LEFT JOIN Driver On DocumentsNew.ItemID=Driver.ID And DocumentsNew.CategoryID=3
                LEFT JOIN DrugAlcoholTest ON DocumentsNew.ItemID=DrugAlcoholTest.ID AND DocumentsNew.CategoryID=4 LEFT JOIN DrugAlcoholTestType ON DrugAlcoholTest.TestTypeID=DrugAlcoholTestType.ID
                LEFT JOIN RegistrationDoc On DocumentsNew.ItemID=RegistrationDoc.ID And DocumentsNew.CategoryID=6 LEFT JOIN RegistrationDocType On TypeID = RegistrationDocType.ID
                LEFT JOIN RoadsideInspection ON DocumentsNew.ItemID=RoadsideInspection.ID AND DocumentsNew.CategoryID=7
                LEFT JOIN Training On DocumentsNew.ItemID=Training.ID And DocumentsNew.CategoryID=8 LEFT JOIN TrainingTopic On TopicID = TrainingTopic.ID
                LEFT JOIN Vehicle ON DocumentsNew.ItemID=Vehicle.ID AND DocumentsNew.CategoryID=9

                WHERE DocumentsNew.ContractID=@ContractID
                AND (@CatID=0 OR DocumentsNew.CategoryID=@CatID)
                AND (@ItemID=0 OR DocumentsNew.ItemID=@ItemID)

                ORDER BY ISNULL(DocumentCatagoriesNew.Name,'No Category'),ISNULL(DocumentsSubCategory.Name,'No Category'),DocName,DocumentsNew.ID"

            cmd.CommandText = sql
            cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@ContractID", SqlDbType.BigInt)).Value = ContractID
            cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@CatID", SqlDbType.BigInt)).Value = CatID
            cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@ItemID", SqlDbType.BigInt)).Value = ItemID

            cmd2.CommandText = "Select * FROM Documents WHERE DocumentID=@DocID"
            cmd2.Parameters.Add(New System.Data.SqlClient.SqlParameter("@DocID", SqlDbType.BigInt)).Value = 0

            DR = cmd.ExecuteReader
            While DR.Read
                'FolderCatName = Trim(DR("Cat").Replace("&", "-").Replace(",", "_").Replace(".", "_").Replace("/", "_").Replace("\", "_"))
                'FolderItemName = Trim(DR("ItemDesc").Replace("&", "-").Replace(",", "_").Replace(".", "_").Replace("/", "_").Replace("\", "_"))
                FolderSubCatName = Trim(DR("SubCat").Replace("&", "-").Replace(",", "_").Replace(".", "_").Replace("/", "_").Replace("\", "_").Replace("'", ""))

                'see if sub folder exists
                If Not System.IO.Directory.Exists(HttpContext.Current.Server.MapPath("~/Exports/" & ContractID & "/" & CategoryName & "/" & ItemID & "/" & FolderSubCatName)) Then
                    System.IO.Directory.CreateDirectory(HttpContext.Current.Server.MapPath("~/Exports/" & ContractID & "/" & CategoryName & "/" & ItemID & "/" & FolderSubCatName))
                End If

                'save each file
                If InStr(DR("DocName"), ".") > 1 Then
                    FileName = HttpContext.Current.Server.MapPath("~/Exports/" & ContractID & "/" & CategoryName & "/" & ItemID & "/" & FolderSubCatName & "/" & DR("DocName"))
                Else
                    FileName = HttpContext.Current.Server.MapPath("~/Exports/" & ContractID & "/" & CategoryName & "/" & ItemID & "/" & FolderSubCatName & "/" & DR("DocName") & "." & DR("FileType"))
                End If
                Docconnection = New SqlConnection(Logic.Database.DocumentMannagerConnectionString(DR("Cat")))
                Docconnection.Open()
                cmd2.Connection = Docconnection

                cmd2.Parameters("@DocID").Value = (DR("DocID"))
                DR2 = cmd2.ExecuteReader
                If DR2.Read Then
                    'load from correct db
                    Dim buffer() As Byte = DR2("DocBlob")
                    Dim newStream As New System.IO.MemoryStream(buffer)
                    Dim f As New System.IO.FileStream(FileName, System.IO.FileMode.Create, System.IO.FileAccess.Write)
                    newStream.WriteTo(f)
                    f.Close()
                    newStream.Close()
                    buffer = Nothing
                    newStream = Nothing
                End If
                DR2.Close()
                Docconnection.Close()
            End While
            DR.Close()

            ''save application if exists
            If (CatID = 3) Then
                Using outPdf As New PdfDocument()
                    cmd.CommandText = " Select Driver.ID, Driver.LastName + ', ' + Driver.FirstName + ' ' + Driver.MiddleName + ' ' + Driver.Suffix AS ItemName,pdf.pdfData 
                        FROM Driver LEFT JOIN web_DriverApplication app On Driver.webAppID=app.ID LEFT JOIN web_DriverApplicationPDF pdf On app.id=pdf.appId 
                        WHERE Not pageFileName Like '%/%' AND Driver.ContractID=@ContractID AND (@ItemID=0 OR Driver.ID=@ItemID) ORDER BY Driver.ID,pdf.id"
                    DR = cmd.ExecuteReader()
                    While DR.Read()
                        Using ms As New MemoryStream(DirectCast(DR("pdfData"), Byte()))
                            Using inPdf = PdfReader.Open(ms, PdfDocumentOpenMode.Import)
                                For i = 0 To inPdf.PageCount - 1
                                    outPdf.AddPage(inPdf.Pages(i))
                                Next
                                'inPdf.Close()
                            End Using
                        End Using

                    End While
                    DR.Close()
                    If outPdf.PageCount > 0 Then
                        'see if sub folder exists
                        If Not System.IO.Directory.Exists(HttpContext.Current.Server.MapPath("~/Exports/" & ContractID & "/" & CategoryName & "/" & ItemID & "/" & "Web Application")) Then
                            System.IO.Directory.CreateDirectory(HttpContext.Current.Server.MapPath("~/Exports/" & ContractID & "/" & CategoryName & "/" & ItemID & "/" & "Web Application"))
                        End If
                        FileName = HttpContext.Current.Server.MapPath("~/Exports/" & ContractID & "/" & CategoryName & "/" & ItemID & "/Web Application/AllAppDocs-" & ItemID & ".pdf")
                        outPdf.Save(FileName)
                    End If
                End Using
            End If

            ZipFile.CreateFromDirectory(SourceFolder, DestFile, CompressionLevel.Optimal, True)

            With Response
                .Buffer = True
                .Charset = ""
                .Cache.SetCacheability(HttpCacheability.NoCache)
                .ContentType = "application/zip"
                .AddHeader("content-disposition", "inline;filename=CAMS_" & CategoryName & "_" & ItemID & ".zip")
                '.BinaryWrite(doc.Data)
                .WriteFile(DestFile)
                .Flush()
                .End()
            End With
        Catch ex As Exception
            '            Me.HandleException("An Error Occurred while Exporting Documents", ex)
        Finally
            connection.Close()
            Docconnection.Close()

        End Try
    End Sub

    Private Sub btnCancel_Click() Handles btnCancel.Click
        Select Case itemContext
            Case "Accident"
                Response.Redirect("Accidents.aspx?cid=" & contractId)
            Case "Driver"
                Response.Redirect("Drivers.aspx?cid=" & contractId)
            Case "DriverDPQ"
                Response.Redirect("Recruiting.aspx?cid=" & contractId)
            Case "DrugAlcoholTest"
                Response.Redirect("DrugAlcoholTests.aspx?cid=" & contractId)
            Case "RegistrationDoc"
                Response.Redirect("Dashboard.aspx?cid=" & contractId)
            Case "CompanyPolicies"
                Response.Redirect("Dashboard.aspx?cid=" & contractId)
            Case "RoadsideInspection"
                Response.Redirect("RoadsideInspections.aspx?cid=" & contractId)
            Case "SafetyScore"
                Response.Redirect("Dashboard.aspx")
            Case "Training"
                Response.Redirect("Training.aspx?cid=" & contractId)
            Case "Vehicle"
                Response.Redirect("Vehicles.aspx?cid=" & contractId)
            Case "uncat"
                Response.Redirect("DocumentsUploads.aspx?cid=" & contractId)
            Case "other"
                Response.Redirect("DocumentsUploads.aspx?cid=" & contractId)
        End Select
    End Sub

    Private Function _GetContextMapString(itemContext As String) As String
        Select Case itemContext
            Case "Accident"
                Return "1"
            Case "Driver"
                Return "3"
            Case "DriverDPQ"
                Return 3
            Case "DrugAlcoholTest"
                Return "4"
            Case "RegistrationDoc"
                Return "6"
            Case "RoadsideInspection"
                Return "7"
            Case "Training"
                Return "8"
            Case "Vehicle"
                Return "9"
            Case "uncat"
                Return "12"
            Case "other"
                Return "13"
            Case "CompanyPolicies"
                Return "14"
            Case Else
                Return itemContext
        End Select
    End Function

    Private Sub _FillData(itemContext As String, itemId As Integer, appID As Int64)
        Using connection As New SqlConnection(Logic.Database.ConnectionString)
            connection.Open()
            Dim cmd As New SqlCommand("", connection)
            cmd.Parameters.Add("itemId", SqlDbType.BigInt).Value = itemId
            Select Case itemContext
                Case "Accident"
                    Master.SetParentPage("Accidents.aspx?cid=" & contractId)
                    litContext.Text = "Accident"
                    cmd.CommandText = "SELECT DriverName+' - '+FORMAT(AccidentDate,'MM/dd/yyyy') FROM Accident WHERE ID=@itemId"
                Case "Driver"
                    Master.SetParentPage("Drivers.aspx?cid=" & contractId)
                    litContext.Text = "Driver"
                    cmd.CommandText = "SELECT LastName+', '+FirstName+' '+MiddleName+(CASE WHEN Suffix='' THEN '' ELSE ', ' END + Suffix) FROM Driver WHERE ID=@itemId"
                Case "DriverDPQ"
                    If itemId > 0 Then
                        Master.SetParentPage("Recruiting.aspx?cid=" & contractId)
                        litContext.Text = "Driver"
                        cmd.CommandText = "SELECT LastName+', '+FirstName+' '+MiddleName+(CASE WHEN Suffix='' THEN '' ELSE ', ' END + Suffix) FROM Driver WHERE ID=@itemId"
                    Else
                        Master.SetParentPage("Recruiting.aspx?cid=" & contractId)
                        litContext.Text = "Driver"
                        cmd.CommandText = "SELECT applicantNameLast + ', ' + applicantNameFirst + CASE WHEN NOT NULLIF(applicantNameMiddle, '') IS NULL THEN ' ' + applicantNameMiddle ELSE '' END + CASE WHEN NOT NULLIF(applicantNameSuffix, '') IS NULL THEN ', ' + applicantNameSuffix ELSE '' END FROM web_DriverApplication WHERE ID=@appid"
                        cmd.Parameters.Add("appid", SqlDbType.BigInt).Value = appID
                    End If
                Case "DrugAlcoholTest"
                    Master.SetParentPage("DrugAlcoholTests.aspx?cid=" & contractId)
                    litContext.Text = "Drug &amp; Alcohol Test"
                    cmd.CommandText = "SELECT DriverName+' - '+FORMAT(TestDate,'MM/dd/yyyy') FROM DrugAlcoholTest WHERE ID=@itemId"
                Case "RegistrationDoc"
                    Master.SetParentPage("Dashboard.aspx?cid=" & contractId)
                    litContext.Text = "Carrier Registration & Permits"
                    cmd.CommandText = "SELECT Customer.Name FROM ServiceContract LEFT JOIN Customer ON ServiceContract.CustomerID=Customer.ID WHERE ServiceContract.ID=" & contractId
                Case "CompanyPolicies"
                    Master.SetParentPage("Dashboard.aspx?cid=" & contractId)
                    litContext.Text = "Company Policies & Forms"
                    cmd.CommandText = "SELECT Customer.Name FROM ServiceContract LEFT JOIN Customer ON ServiceContract.CustomerID=Customer.ID WHERE ServiceContract.ID=" & contractId
                Case "RoadsideInspection"
                    Master.SetParentPage("RoadsideInspections.aspx?cid=" & contractId)
                    litContext.Text = "Roadside Inspection"
                    cmd.CommandText = "SELECT DriverName+' - '+FORMAT(InspectionDate,'MM/dd/yyyy') FROM RoadsideInspection WHERE ID=@itemId"
                Case "SafetyScore"
                    litContext.Text = "Safety Score"
                    cmd.CommandText = "SELECT FORMAT(ScoreDate,'MM/dd/yyyy') FROM SafetyScore WHERE ID=@itemId"
                Case "Training"
                    Master.SetParentPage("Training.aspx?cid=" & contractId)
                    litContext.Text = "Training"
                    cmd.CommandText = "SELECT tt.Name+' - '+d.LastName+', '+d.FirstName+' '+d.MiddleName+(CASE WHEN Suffix='' THEN '' ELSE ', ' END + Suffix) FROM Training t LEFT JOIN TrainingTopic tt ON t.TopicID=tt.ID LEFT JOIN Driver d ON t.DriverID=d.ID WHERE t.ID=@itemId"
                Case "Vehicle"
                    Master.SetParentPage("Vehicles.aspx?cid=" & contractId)
                    litContext.Text = "Vehicle"
                    cmd.CommandText = "SELECT Unit FROM Vehicle WHERE ID=@itemId"
                Case "uncat"
                    Master.SetParentPage("DocumentsUploads.aspx?cid=" & contractId)
                    litContext.Text = "Uncategorized "
                    cmd.CommandText = "SELECT 'Uncategorized Documents'"
                Case "other"
                    Master.SetParentPage("DocumentsUploads.aspx?cid=" & contractId)
                    litContext.Text = "Other "
                    cmd.CommandText = "SELECT 'Other Documents'"

            End Select
            litItemName.Text = HttpUtility.HtmlEncode(cmd.ExecuteScalar())
        End Using
    End Sub

    Private Function _HasPerms(context As String) As Boolean
        Dim perms = Master.CurrentClient.Permissions
        Select Case itemContext
            Case "Accident"
                Return perms.Contains("View Accidents Page")
            Case "Driver"
                Return perms.Contains("View Drivers Page")
            Case "DriverDPQ"
                Return perms.Contains("View Drivers Page")
            Case "DrugAlcoholTest"
                Return perms.Contains("View Tests Page")
            Case "RegistrationDoc"
                Return perms.Contains("View Carrier Registration & Permits Page")
            Case "CompanyPolicies"
                Return perms.Contains("View Company Policies & Forms Page")
            Case "RoadsideInspection"
                Return perms.Contains("View Roadside Inspections Page")
            Case "SafetyScore"
                Return perms.Contains("View Safety Scores")
            Case "Training"
                Return perms.Contains("View Training Page")
            Case "Vehicle"
                Return perms.Contains("View Vehicles Page")
            Case "uncat"
                Return perms.Contains("Upload Documents - TCS Staff Review")
            Case "other"
                Return perms.Contains("Upload Documents - Other")
        End Select
        Return False
    End Function

    Private Function _GetContractId(context As String, itemId As Integer) As Integer

        ' prevents SQL injection via the context param
        Dim whitelist = {
            "Accident", "Driver", "DriverDPQ", "DrugAlcoholTest", "RegistrationDoc",
            "RoadsideInspection", "SafetyScore", "Training", "Vehicle"}
        If Not whitelist.Contains(context) Then Return 0

        Using connection As New SqlConnection(Logic.Database.ConnectionString)
            connection.Open()
            Dim mcontext As String = context
            If mcontext = "DriverDPQ" Then mcontext = "Driver"
            Dim cmd As New SqlCommand(
                "SELECT ContractID FROM " & mcontext & " WHERE ID=@itemId",
                connection)
            cmd.Parameters.Add("itemId", SqlDbType.BigInt).Value = itemId
            Return cmd.ExecuteScalar()
        End Using
    End Function

End Class