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

Public Class Document
    Inherits System.Web.UI.Page

    ' On init, make sure we are on HTTPS (if set in config)
    Private Sub Page_Init(sender As Object, e As EventArgs) Handles Me.Init
        If My.Settings.httpsOnly Then
            _SecurePage()
        End If
    End Sub

    ' On load, process query params to return what we want
    Private Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load

        Dim context = Logic.Conversion.ToString(Request.Params("context"))
        Dim contextId = Logic.Conversion.ToInteger(Request.Params("id"))

        If context = "" And contextId = 0 Then
            Exit Sub
        End If

        Try
            Dim doc = LoadDocument(context, contextId)
            doc.Filename = Regex.Replace(doc.Filename, "[^\w\.]", "")

            With Response
                .Buffer = True
                .Charset = ""
                .Cache.SetCacheability(HttpCacheability.NoCache)
                .ContentType = MimeMapping.GetMimeMapping(doc.Filename)
                .AddHeader("content-disposition", "inline;filename=" & doc.Filename)
                .BinaryWrite(doc.Data)
                .Flush()
                .End()
            End With

        Catch ex As Threading.ThreadAbortException
        Catch ex As Exception
            ' For almost all errors, we just want 404 so they can't query our db info
            Response.StatusCode = HttpStatusCode.NotFound
            Response.End()
        End Try
    End Sub

#Region "Document loading"

    Structure DocumentData
        Property Filename As String
        Property Data As Byte()
    End Structure

    ' Call this
    Function LoadDocument(context As String, contextId As Integer) As DocumentData
        Select Case context
            Case "Application" : Return _LoadApplicationDocument(contextId)
            Case "Document" : Return _LoadDocument(contextId)
            Case "DocumentNew" : Return _LoadDocumentNew(contextId)
            Case "FullApplication" : Return _LoadFullApplicationDocument(contextId)
            Case Else : Throw New ArgumentException(context)
        End Select
    End Function

    Private Function _LoadApplicationDocument(docId As Integer) As DocumentData
        Using connection As New SqlConnection(Logic.Database.connectionString)
            connection.Open()

            Dim cmd As New SqlCommand("", connection)
            cmd.Parameters.AddWithValue("docId", docId)
            cmd.Parameters.AddWithValue("driverId", SessionVars.DriverId(Session))
            cmd.Parameters.AddWithValue("clientId", SessionVars.ClientId(Session))

            cmd.CommandText = 
                "SELECT COUNT(id) FROM web_ClientPermsMap
                WHERE clientId=@clientId
                AND permId = (SELECT id FROM web_ClientPerms WHERE [name]='View Applications Page')"
            If cmd.ExecuteScalar() = 0 Then Throw New HttpException(403, "Forbidden")

            cmd.CommandText =
                "SELECT pdf.pageTitle, pdf.pdfData
                FROM web_DriverApplicationPdf pdf
                LEFT JOIN web_DriverApplication app ON app.id=pdf.appId
                WHERE pdf.id=@docId AND (app.driverAccountId=@driverId 
                OR app.clientId IN (
                    SELECT contractId FROM web_ClientContractsMap
                        WHERE clientId=@clientId
                    UNION ALL SELECT ca.agencyId
                        FROM ContractAgencyMap ca
                        LEFT JOIN web_ClientContractsMap cc
                            ON cc.contractId=ca.contractId
                        WHERE cc.clientId=@clientId
                ))"

            Dim dReader = cmd.ExecuteReader()
            If Not dReader.Read() Then
                Throw New HttpException(404, "Not Found")
            End If

            Return New DocumentData With {
                .Filename = dReader(0) & ".pdf",
                .Data = dReader(1)}

        End Using
    End Function

    Private Function _LoadDocument(docId As Integer) As DocumentData
        Using connection As New SqlConnection(Logic.Database.connectionString)
            connection.Open()

            Dim cmd As New SqlCommand("", connection)
            cmd.Parameters.Add("docId", SqlDbType.BigInt).Value = docId

            ' Figure out the associated contract
            cmd.CommandText = "SELECT MapType,MapId FROM DocumentMap WHERE ImageId=@docId"
            Using dReader = cmd.ExecuteReader()
                If Not dReader.Read() Then Throw New HttpException(404, "Not Found")
                cmd.Parameters.Add("itemId", SqlDbType.BigInt).Value = dReader(1)
                Select Case CStr(dReader(0))
                    Case "Accident" : cmd.CommandText = "SELECT ContractID FROM Accident WHERE ID=@itemId"
                    Case "Driver" : cmd.CommandText = "SELECT ContractID FROM Driver WHERE ID=@itemId"
                    Case "Drug & Alcohol Test" : cmd.CommandText = "SELECT ContractID FROM DrugAlcoholTest WHERE ID=@itemId"
                    Case "Registration Document" : cmd.CommandText = "SELECT ContractID FROM RegistrationDoc WHERE ID=@itemId"
                    Case "Roadside Inspection" : cmd.CommandText = "SELECT ContractID FROM RoadsideInspection WHERE ID=@itemId"
                    Case "Safety Score" : cmd.CommandText = "SELECT ContractID FROM SafetyScore WHERE ID=@itemId"
                    Case "Training" : cmd.CommandText = "SELECT ContractID FROM Training WHERE ID=@itemId"
                    Case "Vehicle" : cmd.CommandText = "SELECT ContractID FROM Vehicle WHERE ID=@itemId"
                End Select
            End Using
            Dim contractId As Integer = cmd.ExecuteScalar()

            ' Make sure the current client has perms for the contract
            cmd.CommandText = "SELECT COUNT(*) FROM Web_ClientContractsMap WHERE clientId=@clientId AND contractId=@contractId"
            cmd.Parameters.Add("clientId", SqlDbType.BigInt).Value = SessionVars.ClientId(Session)
            cmd.Parameters.Add("contractId", SqlDbType.BigInt).Value = contractId
            If CInt(cmd.ExecuteScalar()) = 0 Then
                Throw New HttpException(403, "Forbidden")
            End If

            ' Now get and return the document
            cmd.CommandText = "SELECT Name,DocBlob FROM Documents WHERE ID=@docId"
            Using dReader = cmd.ExecuteReader()
                If Not dReader.Read() Then Throw New HttpException(404, "Not Found")
                Return New DocumentData With {
                    .Filename = dReader(0),
                    .Data = dReader(1)}
            End Using

        End Using
    End Function

    Private Function _LoadDocumentNew(docId As Integer) As DocumentData
        Using connection As New SqlConnection(Logic.Database.ConnectionString)
            connection.Open()
            Dim contractId As Int64 = 0
            Dim FileName As String = ""
            Dim CatName As String = ""

            Dim cmd As New SqlCommand("", connection)
            cmd.Parameters.Add("docId", SqlDbType.BigInt).Value = docId

            ' Figure out the associated contract
            cmd.CommandText = "SELECT ISNULL(DocumentsNew.ContractID,0) AS ContractID,ISNULL(DocumentsNew.DocName,'') AS DocName,ISNULL(DocumentCatagoriesNew.Name,'') AS CatName FROM DocumentsNew LEFT JOIN DocumentCatagoriesNew ON DocumentsNew.CategoryID=DocumentCatagoriesNew.ID WHERE DocumentsNew.ID=@docId"
            Using dReader = cmd.ExecuteReader()
                If dReader.Read Then
                    contractId = dReader("ContractID")
                    FileName = dReader("DocName")
                    CatName = dReader("CatName")
                Else
                    Throw New HttpException(404, "Not Found")
                End If
            End Using

            ' Make sure the current client has perms for the contract
            cmd.CommandText = "SELECT COUNT(*) FROM Web_ClientContractsMap WHERE clientId=@clientId AND contractId=@contractId"
            cmd.Parameters.Add("clientId", SqlDbType.BigInt).Value = SessionVars.ClientId(Session)
            cmd.Parameters.Add("contractId", SqlDbType.BigInt).Value = contractId
            If CInt(cmd.ExecuteScalar()) = 0 Then
                Throw New HttpException(403, "Forbidden")
            End If

            ' Now get and return the document
            'look in appropriate DB
            Dim connectionDoc As New SqlConnection(Logic.Database.DocumentMannagerConnectionString(CatName))
            Dim cmdDoc As New SqlCommand

            connectionDoc.Open()
            cmdDoc.Connection = connectionDoc
            cmdDoc.Parameters.Add("docId", SqlDbType.BigInt).Value = docId

            cmdDoc.CommandText = "SELECT DocBlob FROM Documents WHERE DocumentID=@docId"
            Using dReader = cmdDoc.ExecuteReader()
                If Not dReader.Read() Then Throw New HttpException(404, "Not Found")
                Return New DocumentData With {
                    .Filename = FileName,
                    .Data = dReader(0)}
            End Using

        End Using
    End Function
    Private Function _LoadFullApplicationDocument(appId As Integer) As DocumentData

        Using outPdf As New PdfDocument()

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

                Dim cmd As New SqlCommand("", connection)
                cmd.Parameters.AddWithValue("appId", appId)
                cmd.Parameters.AddWithValue("driverId", SessionVars.DriverId(Session))
                cmd.Parameters.AddWithValue("clientId", SessionVars.ClientId(Session))

                cmd.CommandText = 
                    "SELECT COUNT(id) FROM web_ClientPermsMap
                    WHERE clientId=@clientId
                    AND permId = (SELECT id FROM web_ClientPerms WHERE [name]='View Applications Page')"
                If cmd.ExecuteScalar() = 0 Then Throw New HttpException(403, "Forbidden")

                cmd.CommandText =
                    "SELECT pdf.pdfData FROM web_DriverApplicationPDF pdf
                    LEFT JOIN web_DriverApplication app ON app.id=pdf.appId
                    WHERE pdf.appId=@appId AND NOT pageFileName LIKE '%/%'
                     AND (app.driverAccountId=@driverId
                      OR app.clientId IN (
                        SELECT contractId FROM web_ClientContractsMap
                            WHERE clientId=@clientId
                        UNION ALL SELECT ca.agencyId
                            FROM ContractAgencyMap ca
                            LEFT JOIN web_ClientContractsMap cc
                                ON cc.contractId=ca.contractId
                            WHERE cc.clientId=@clientId))
                    ORDER BY pdf.id"

                Dim dReader = cmd.ExecuteReader()
                While dReader.Read()
                    Using ms As New MemoryStream(DirectCast(dReader(0), Byte()))
                        Using inPdf = PdfReader.Open(ms, PdfDocumentOpenMode.Import)
                            For i = 0 To inPdf.PageCount - 1
                                outPdf.AddPage(inPdf.Pages(i))
                            Next
                        End Using
                    End Using
                End While

            End Using

            Dim bytes As Byte()
            Using outStream As New MemoryStream()
                outPdf.Save(outStream, False)
                ReDim bytes(outStream.Length)
                outStream.Read(bytes, 0, outStream.Length - 1)
            End Using

            Return New DocumentData With {
                .Filename = "Application.pdf",
                .Data = bytes}

        End Using

    End Function

#End Region

#Region "Other internals"

    ' Make us use https
    Private Sub _SecurePage()
        Dim ServerName As String = Server.UrlEncode(Request.ServerVariables.Item("SERVER_NAME"))
        Dim FilePath As String = Request.Url.PathAndQuery
        If Not Request.IsSecureConnection Then
            Response.Redirect("https://" & ServerName & FilePath)
        End If
    End Sub

#End Region

End Class