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

Public Class EditDriver
    Inherits System.Web.UI.Page

    Property contractId As Integer
    Property driverId As Integer

    Private Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        Try
                
            driverId = Logic.Conversion.ToInteger(Request.Params("id"))

            ' Set contractId
            If driverId = 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 Driver WHERE ID=@driverId", connection)
                    cmd.Parameters.AddWithValue("driverId", driverId)
                    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 Drivers 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

            dsTraining.ConnectionString = Logic.Database.connectionString
            dsTraining.SelectParameters("driverId").DefaultValue = driverId
            dsNotes.ConnectionString = Logic.Database.connectionString
            dsNotes.SelectParameters("driverId").DefaultValue = driverId

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


            If Not Page.IsPostBack Then
                Master.SetParentPage("Drivers.aspx?cid=" & contractId)
                _UpdatePermissions()
                _FillData(driverId)
            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(sender As Object, e As EventArgs) Handles btnCancel.Click
        Response.Redirect("Drivers.aspx?cid=" & contractId)
    End Sub

    Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
        If Not Page.IsValid Then
            Exit Sub
        End If

        Try

            Dim username = "[Web: " & Master.CurrentClient.username & "]"

            Dim driver = If(driverId, New Logic.Driver(driverId), New Logic.Driver())
            If driver.id = 0 Then
                driver.contractID = contractId
            Else If driver.contractID <> contractId Then
                Response.Redirect("Drivers.aspx?cid=" & contractId)
            End If

            _GetData(driver)
            driver.Save(modifiedBy:=username, modifiedById:=0)

            Response.Redirect("Drivers.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(driverId As Integer)
        Dim driver = If(driverId, New Logic.Driver(driverId), New Logic.Driver())
        With driver
            txtFirstName.Text = .firstName
            txtMiddleName.Text = .middleName
            txtLastName.Text = .lastName
            txtNameSuffix.Text = .suffix
            txtSocial.Value = .ssn
            dtBirth.Text = .dob.FormatForHtml5()
            dtHireDate.Text = .hireDate.FormatForHtml5()
            txtLicenseNumber.Text = .dlNumber
            ddlLicenseState.SelectedValue = .dlState
            txtLicenseClass.Text = .dlClass
            dtLicenseExpires.Text = .dlExpire.FormatForHtml5()
            ynCDL.Checked = .cdl
            ynLogs.Checked = .hosRequired
            ynInter.Checked = New Nullable(Of Boolean)(.intraInter = "Inter")
            txtMCExpires.Text = .mcExpire.FormatForHtml5()
            txtMCMVRExpires.Text = .MCMVRExpire.FormatForHtml5()
            txtAnnualMVRExpire.Text = .mvrExpire.FormatForHtml5()
            txtAnnualCOVExpire.Text = .covExpire.FormatForHtml5()
            chkTWIC.Checked = .twic
            dtTWIC.Text = .twicExpiration.FormatForHtml5()
            chkOtherDate.Checked = .otherDateEnabled
            txtOtherDate.Text = .otherDateDesc
            dtOtherDate.Text = .otherDate.FormatForHtml5()
            txtLocation.Text = .location
            txtAnnualDachExpire.Text = .annualDashExpire.FormatForHtml5()
            txtAddress1.Text = .address1
            txtAddress2.Text = .address2
            txtPhone1.Text = .phone1
            txtPhone2.Text = .phone2
            txtPhone3.Text = .phone3
            txtEmail.Text = .email
            txtCity.Text = .city
            ddlState.SelectedValue = .state
            txtZip.Text = .zip

            chkTerminated.Checked = .terminated
            dtTermDate.Text = .termDate.FormatForHtml5()
            txtTermReason.Text = .terminatedReason
        End With

        ' Set whether the default profile is used
        Dim profileIds = driver.profileIds
        Dim defaultProfileId = _GetDefaultPoolId()
        ynDrugPool.Checked = profileIds.Contains(defaultProfileId)
        phDrugPool.Visible = (defaultProfileId > 0)
    End Sub

    Private Sub _GetData(driver As Logic.Driver)
        Dim perms = Master.CurrentClient.Permissions
        With driver
            .firstName = txtFirstName.Text
            .middleName = txtMiddleName.Text
            .lastName = txtLastName.Text
            .suffix = txtNameSuffix.Text
            .ssn = txtSocial.Value
            .dob = Logic.Conversion.ToDate(dtBirth.Text).Clip()
            .hireDate = Logic.Conversion.ToDate(dtHireDate.Text).Clip()
            .dlNumber = txtLicenseNumber.Text
            .dlState = ddlLicenseState.SelectedValue
            .dlClass = txtLicenseClass.Text
            .dlExpire = Logic.Conversion.ToDate(dtLicenseExpires.Text).Clip()
            .cdl = ynCDL.Checked
            .hosRequired = ynLogs.Checked
            .intraInter = If(ynInter.Checked, "Inter", "Intra")
            .address1 = txtAddress1.Text
            .address2 = txtAddress2.Text
            .city = txtCity.Text
            .state = ddlState.SelectedValue
            .zip = txtZip.Text
            .phone1 = txtPhone1.Text
            .phone2 = txtPhone2.Text
            .phone3 = txtPhone3.Text
            .email = txtEmail.Text
            .location = txtLocation.Text

            If perms.Contains("Edit Driver Expirations") Then
                .mcExpire = Logic.Conversion.ToDate(txtMCExpires.Text).Clip()
                .MCMVRExpire = Logic.Conversion.ToDate(txtMCMVRExpires.Text).Clip()
                .mvrExpire = Logic.Conversion.ToDate(txtAnnualMVRExpire.Text).Clip()
                .covExpire = Logic.Conversion.ToDate(txtAnnualCOVExpire.Text).Clip()
                .annualDashExpire = Logic.Conversion.ToDate(txtAnnualDachExpire.Text).Clip()
            End If
            If perms.Contains("Edit TWIC Expirations") Then
                .twic = chkTWIC.Checked
                .twicExpiration = Logic.Conversion.ToDate(dtTWIC.Text).Clip()
            End If
            If perms.Contains("Edit Driver Other Date") Then
                .otherDateEnabled = chkOtherDate.Checked
                .otherDateDesc = txtOtherDate.Text
                .otherDate = Logic.Conversion.ToDate(txtOtherDate.Text).Clip()
            End If
            If perms.Contains("Edit Driver Term Date") Then
                .terminated = chkTerminated.Checked
                .termDate = Logic.Conversion.ToDate(dtTermDate.Text).Clip()
                .terminatedReason = txtTermReason.Text
            End If



        End With

        ' Set the default profile id for the driver
        Dim profileIds = driver.profileIds
        Dim defaultProfileId = _GetDefaultPoolId()
        If ynDrugPool.Checked Then
            If Not profileIds.Contains(defaultProfileId) Then
                profileIds.Add(defaultProfileId)
            End If
        Else
            If profileIds.Contains(defaultProfileId) Then
                profileIds.Remove(defaultProfileId)
            End If
        End If
        driver.profileIds = profileIds

    End Sub

    Private Sub _UpdatePermissions()
        Dim perms = Master.CurrentClient.Permissions

        Dim permExpire = perms.Contains("Edit Driver Expirations")
        txtMCExpires.Enabled = permExpire
        txtMCMVRExpires.Enabled = permExpire
        txtAnnualMVRExpire.Enabled = permExpire
        txtAnnualCOVExpire.Enabled = permExpire
        txtAnnualDachExpire.Enabled = permExpire

        phTWIC.Visible = perms.Contains("Edit TWIC Expirations")

        phOther.Visible = perms.Contains("Edit Driver Other Date")

        If Not perms.Contains("Edit Driver Term Date") Then
            chkTerminated.Enabled = False
            txtTermReason.Enabled = False
            dtTermDate.Enabled = False
        End If
    End Sub

    Private Function _GetDefaultPoolId() As Integer
        Using connection As New SqlConnection(Logic.Database.connectionString)
            connection.Open()
            Dim cmd As New SqlCommand(
                "SELECT randomDrugPoolProfileID FROM ServiceContract WHERE ID=@contractId",
                connection)
            cmd.Parameters.AddWithValue("contractId", contractId)
            Return Logic.Conversion.ToInteger(cmd.ExecuteScalar())
        End Using
    End Function

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

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