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 EditTraining
    Inherits System.Web.UI.Page

    Property contractId As Integer
    Property trainingId As Integer

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

            If trainingId = 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 Training WHERE ID=@trainingId", connection)
                    cmd.Parameters.AddWithValue("trainingId", trainingId)
                    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 Training 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 trainingId > 0 Then
                dsDocs.SelectParameters("ItemId").DefaultValue = trainingId
            Else
                dsDocs.SelectParameters("ItemId").DefaultValue = -1
            End If
            dsDocs.SelectParameters("CatID").DefaultValue = 8

            If Not Page.IsPostBack Then
                Master.SetParentPage("Training.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("Training.aspx?cid=" & contractId)
    End Sub

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

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

            Response.Redirect("Training.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(trainingId, New Logic.Training(trainingId), New Logic.Training())
            ddlDriver.SelectedValue = .driverID
            txtDriverName.Text = .driverName
            ddlTopic.SelectedValue = .topicID
            txtSpecificTopic.Text = .specificTopic
            txtDate.Text = Logic.Conversion.ToNullableDate(.trainingDate).FormatForHtml()
            chkExpires.Checked = .expires
            txtExpires.Text = Logic.Conversion.ToNullableDate(.expireDate).FormatForHtml()
            txtExpires.Enabled = .expires
            chkQuiz.Checked = .trainingQuiz
            txtQuiz.Text = Logic.Conversion.ToNullableDate(.trainingQuizDate).FormatForHtml()
            txtQuiz.Enabled = .trainingQuiz
            txtNotes.Text = .notes
        End With
    End Sub

    Private Sub _FillUI()
        ddlDriver.Items.AddRange({New ListItem()}.Concat(
            From driver In Logic.Driver.ListForContract(contractId)
            Select New ListItem(driver.Value, driver.Key)).ToArray())
        ddlTopic.Items.AddRange({New ListItem()}.Concat(
            From topic In Logic.TrainingTopic.List()
            Select New ListItem(topic.Value, topic.Key)).ToArray())
    End Sub

    Private Sub _GetData(training As Logic.Training)
        With training
            .driverID = ddlDriver.SelectedValue
            .driverName = txtDriverName.Text
            .topicID = ddlTopic.SelectedValue
            .specificTopic = txtSpecificTopic.Text
            .trainingDate = Logic.Conversion.ToDate(txtDate.Text).Clip()
            .expires = chkExpires.Checked
            .expireDate = Logic.Conversion.ToDate(txtExpires.Text).Clip()
            .trainingQuiz = chkQuiz.Checked
            .trainingQuizDate = Logic.Conversion.ToDate(txtQuiz.Text).Clip()
            .notes = txtNotes.Text
        End With
    End Sub
    Protected Sub btnUploadDoc_Click(sender As Object, e As EventArgs) Handles btnUploadDoc.Click

        Try
            If trainingId <> 0 Then
                Response.Redirect("DocumentsUploads.aspx?cid=" & contractId & "&ItemID=" & trainingId & "&ctx=Training")
            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, 8, "Training", trainingId)
    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