Imports System.Data.SqlClient
Imports System.IO.Compression
Imports PdfSharp.Pdf
Imports PdfSharp.Pdf.IO
Imports System.IO
Imports System.Threading
Imports Logic.DateExtensions
Imports Logic.NullableDateExtensions

Public Class EditAccident
    Inherits System.Web.UI.Page

    Property contractId As Integer
    Property accidentId As Integer

    Protected Sub Page_Load() Handles Me.Load
        Try
            accidentId = Logic.Conversion.ToInteger(Request.Params("id"))

            If accidentId = 0 Then
                contractId = Logic.Conversion.ToInteger(Request.Params("cid"))
            Else
                Using connection As New SqlConnection(Logic.Database.connectionString)
                    connection.Open()
                    Dim cmd As New SqlCommand("SELECT ContractID FROM Accident WHERE ID=@accidentId", connection)
                    cmd.Parameters.AddWithValue("accidentId", accidentId)
                    contractId = cmd.ExecuteScalar()
                End Using
            End If
            Master.CurrentClient.VerifyContractAccess(contractId)

            Dim perms = Master.CurrentClient.Permissions
            If perms.Contains("Document Export") Then
                btnDownloadAll.Visible = True
            Else
                btnDownloadAll.Visible = False
            End If
            If Not perms.Contains("View Accidents Page") Then Throw New HttpException(403, "Forbidden")
            If Not perms.Contains("View Document Edit Page") Then
                Dim colCount = gvDocs.Columns.Count
                gvDocs.Columns(colCount - 2).Visible = False
            End If
            If Not perms.Contains("View Document Upload Page") Then
                btnUploadDoc.Visible = False
            End If

            dsDocs.ConnectionString = Logic.Database.ConnectionString
            If accidentId > 0 Then
                dsDocs.SelectParameters("ItemId").DefaultValue = accidentId
            Else
                dsDocs.SelectParameters("ItemId").DefaultValue = -1
            End If
            dsDocs.SelectParameters("CatID").DefaultValue = 1

            If Not Page.IsPostBack Then
                Master.SetParentPage("Accidents.aspx?cid=" & contractId)
                _FillUI()
                _FillData()
            End If

        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 btnCancel_Click() Handles btnCancel.Click
        Response.Redirect("Accidents.aspx?cid=" & contractId)
    End Sub

    Private Sub btnSave_Click() Handles btnSave.Click
        If Not Page.IsValid Then Exit Sub
        Try
            Dim accident = If(accidentId, New Logic.Accident(accidentId), New Logic.Accident())
            If accident.id = 0 Then
                accident.contractID = contractId
            Else If accident.contractID <> contractId Then
                Response.Redirect("Accidents.aspx?cid=" & contractId)
            End If

            _GetData(accident)
            accident.Save("[Web: " & Master.CurrentClient.username & "]")

            Response.Redirect("Accidents.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 _FillData()
        With If(accidentId, New Logic.Accident(accidentId), New Logic.Accident())
            txtDate.Text = Logic.Conversion.ToNullableDate(.accidentDate).FormatForHtml()
            ddlDriver.SelectedValue = .driverID
            ddlVehicle1.SelectedValue = .vehicleID
            ddlVehicle2.SelectedValue = .vehicle2ID
            ddlVehicle3.SelectedValue = .vehicle3ID
            txtCityState.Text = .cityState
            txtInjured.Text = .numberInjured
            txtFatal.Text = .numberFatal
            chkTowed.Checked = .vehicleTowed
            chkHazmat.Checked = .hazMatRelease
            chkAccident.Checked = .accidentReport
            chkCited.Checked = .cited
            chkPostAccident.Checked = .postAccidentDocumentation
            chkPostAccident.Enabled = .cited
            txtDescription.Text = .description
        End With
    End Sub

    Private Sub _FillUI()
        ddlDriver.Items.AddRange({New ListItem("", 0)}.Concat(
            From driver In Logic.Driver.ListForContract(contractId)
            Select New ListItem(driver.Value, driver.Key)).ToArray())
        
        ddlVehicle1.Items.AddRange({New ListItem("", 0)}.Concat(
            From vehicle In Logic.Vehicle.ListForContract(contractId)
            Select New ListItem(vehicle.Value.unit, vehicle.Key)).ToArray())
        ddlVehicle2.Items.AddRange({New ListItem("", 0)}.Concat(
            From vehicle In Logic.Vehicle.ListForContract(contractId)
            Select New ListItem(vehicle.Value.unit, vehicle.Key)).ToArray())
        ddlVehicle3.Items.AddRange({New ListItem("", 0)}.Concat(
            From vehicle In Logic.Vehicle.ListForContract(contractId)
            Select New ListItem(vehicle.Value.unit, vehicle.Key)).ToArray())
    End Sub

    Private Sub _GetData(accident As Logic.Accident)
        With accident
            .accidentDate = Logic.Conversion.ToDate(txtDate.Text).Clip()
            .driverID = ddlDriver.SelectedValue
            .driverName = ddlDriver.SelectedItem.Text
            .vehicleID = ddlVehicle1.SelectedValue
            .vehicleDesc = ddlVehicle1.SelectedItem.Text
            .vehicle2ID = ddlVehicle2.SelectedValue
            .vehicle2Desc = ddlVehicle2.SelectedItem.Text
            .vehicle3ID = ddlVehicle3.SelectedValue
            .vehicle3Desc = ddlVehicle3.SelectedItem.Text
            .cityState = txtCityState.Text
            .numberInjured = txtInjured.Text
            .numberFatal = txtFatal.Text
            .vehicleTowed = chkTowed.Checked
            .hazMatRelease = chkHazmat.Checked
            .accidentReport = chkAccident.Checked
            .cited = chkCited.Checked
            .postAccidentDocumentation = chkPostAccident.Checked
            .description = txtDescription.Text
        End With
    End Sub

    Protected Sub btnUploadDoc_Click(sender As Object, e As EventArgs) Handles btnUploadDoc.Click

        Try
            If accidentId <> 0 Then
                Response.Redirect("DocumentsUploads.aspx?cid=" & contractId & "&ItemID=" & accidentId & "&ctx=Accident")
            End If


        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

    Protected Sub btnDownloadAll_Click(sender As Object, e As EventArgs) Handles btnDownloadAll.Click
        DownloadFiles(contractId, 1, "Accident", accidentId)
    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

End Class