Imports System.Data.SqlClient
Imports System.IO
Imports CrystalDecisions.CrystalReports.Engine
Imports ReportingClass
Imports Logic.DateExtensions
Imports Logic.NullableDateExtensions

Public Class MedicalRegistry
    Inherits System.Web.UI.Page

    Private Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        Try

            ' gotta set these here
            imgFront.Attributes.Add("data-for", uplFront.ClientID)

            ' load the page on entry, save on exit
            If Not Page.IsPostBack Then
                _LoadForm()
            Else
                _SaveForm()
                _SaveDocument()
            End If

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

    Private Sub _LoadForm()

        Dim cmdGet = "
            SELECT MedicalExaminerName, MedicalExaminerRegistryNumber, MedicalExaminerExpiration,
                MedicalRegistryCardFilename
            FROM web_DriverApplication
            WHERE id=@appId"

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

            Dim cmd As New SqlCommand(cmdGet, connection)
            cmd.Parameters.AddWithValue("appId", Master.app.id)

            Dim dReader = cmd.ExecuteReader()
            dReader.Read()

            txtName.Text = Logic.Conversion.ToString(dReader("MedicalExaminerName"))
            txtRegistryNumber.Text = Logic.Conversion.ToString(dReader("MedicalExaminerRegistryNumber"))
            txtExpiration.Text = Logic.Conversion.ToNullableDate(dReader("MedicalExaminerExpiration")).FormatForHtml()

            If TypeOf dReader("MedicalRegistryCardFilename") IsNot DBNull Then
                imgFront.ImageUrl = "/_assets/AttachmentImage.aspx?context=DPQ&name=MedicalRegistryCard&id=" & Master.app.id
            End If

        End Using

    End Sub

    Private Sub _SaveForm()

        Dim cmdSave = "
            UPDATE web_DriverApplication
            SET MedicalExaminerName=@ExaminerName, MedicalExaminerRegistryNumber=@RegistryNumber,
                MedicalExaminerExpiration=@ExaminerExpiration
            WHERE id=@appId"
        Dim cmdSaveCard = "
            UPDATE web_DriverApplication
            SET MedicalRegistryCard=@front, MedicalRegistryCardFilename=@frontName
            WHERE id=@appId"

        Dim Expiration = Logic.Conversion.ToNullableDate(txtExpiration.Text)

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

            Dim cmd As New SqlCommand(cmdSave, connection)
            With cmd.Parameters
                .AddWithValue("appId", Master.app.id)
                .AddWithValue("ExaminerName", txtName.Text)
                .AddWithValue("RegistryNumber", txtRegistryNumber.Text)
                .AddWithValue("ExaminerExpiration", If(Expiration.HasValue, Expiration.Value.Clip().Unclip(), DBNull.Value))
            End With
            cmd.ExecuteNonQuery()

            If uplFront.HasFile Then
                Dim filename = uplFront.FileName
                If filename.Length > 50 Then
                    filename = filename.Substring(filename.Length - 50)
                End If

                cmd.CommandText = cmdSaveCard
                cmd.Parameters.AddWithValue("front", uplFront.FileBytes)
                cmd.Parameters.AddWithValue("frontName", filename)
                cmd.ExecuteNonQuery()
            End If

        End Using

        Page.Validate()
        If Page.IsValid Then
            Master.EnableNextPage()
        End If

    End Sub

    Private Sub _SaveDocument()

        Dim PictureCard As Byte() = Nothing
        Using connection As New SqlConnection(Logic.Database.connectionString)
            connection.Open()
            Dim cmdGet = "SELECT MedicalRegistryCard FROM web_DriverApplication WHERE ID=@appId"
            Dim cmd As New SqlCommand(cmdGet, connection)
            cmd.Parameters.AddWithValue("appId", Master.app.id)
            Dim dReader = cmd.ExecuteReader()
            If dReader.Read() AndAlso TypeOf dReader(0) IsNot DBNull Then
                PictureCard = dReader(0)
            End If
        End Using
        If PictureCard Is Nothing Then
            Exit Sub
        End If

        Dim ReportDataSet As New dsAppMedicalCard
        Dim ReportTableAdapter As New dsAppMedicalCardTableAdapters.MedicalCardTableAdapter

        Using Report As New crptAppMedicalCard
            ReportTableAdapter.Fill(ReportDataSet.MedicalCard, Master.app.id)
            Report.SetDataSource(ReportDataSet)

            Dim PictureBox As PictureObject = Report.ReportDefinition.ReportObjects("picCard")
            Dim LinkedFormula As FormulaFieldDefinition = Report.DataDefinition.FormulaFields("pathCard")
            Dim PathCard = Reporting.SetPictureFromData(PictureBox, LinkedFormula, PictureCard, New Drawing.Size(11224, 4800))

            Dim rawData = Reporting.StreamToByteArray(Report)
            IF PathCard IsNot Nothing Then File.Delete(PathCard)

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

                Dim cmdSave = "
                    UPDATE web_DriverApplicationPdf SET pageTitle=@pageTitle, pdfData=@pdfData, pdfDataSize=@pdfDataSize
                    WHERE appId=@appId AND pageFileName=@pageFileName;
                    IF @@ROWCOUNT = 0 BEGIN
                        INSERT INTO web_DriverApplicationPdf (appId, pageTitle, pageFileName, pdfData, pdfDataSize)
                        VALUES (@appId, @pageTitle, @pageFileName, @pdfData, @pdfDataSize);
                    END"
                Dim cmd As New SqlCommand(cmdSave, connection)
                cmd.Parameters.AddWithValue("appId", Master.app.id)
                cmd.Parameters.AddWithValue("pageTitle", Master.CurrentTitle)
                cmd.Parameters.AddWithValue("pageFileName", Master.CurrentPage)
                cmd.Parameters.AddWithValue("pdfData", rawData)
                cmd.Parameters.AddWithValue("pdfDataSize", rawData.Length)
                cmd.ExecuteNonQuery()

            End Using

        End Using
    End Sub

End Class