Imports System.Data.SqlClient
Imports ReportingClass

Public Class Education
    Inherits System.Web.UI.Page

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

            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
                education1Type, education1Institution, education1City, education1State, education1HighestDegree,
                education2Type, education2Institution, education2City, education2State, education2HighestDegree,
                education3Type, education3Institution, education3City, education3State, education3HighestDegree
            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()

            txtSchool1Type.Text = Logic.Conversion.ToString(dReader("education1Type"))
            txtSchool1Name.Text = Logic.Conversion.ToString(dReader("education1Institution"))
            txtSchool1City.Text = Logic.Conversion.ToString(dReader("education1City"))
            ddlSchool1State.SelectedValue = Logic.Conversion.ToString(dReader("education1State"))
            txtSchool1HighestLevel.Text = Logic.Conversion.ToString(dReader("education1HighestDegree"))
            txtSchool2Type.Text = Logic.Conversion.ToString(dReader("education2Type"))
            txtSchool2Name.Text = Logic.Conversion.ToString(dReader("education2Institution"))
            txtSchool2City.Text = Logic.Conversion.ToString(dReader("education2City"))
            ddlSchool2State.SelectedValue = Logic.Conversion.ToString(dReader("education2State"))
            txtSchool2HighestLevel.Text = Logic.Conversion.ToString(dReader("education2HighestDegree"))
            txtSchool3Type.Text = Logic.Conversion.ToString(dReader("education3Type"))
            txtSchool3Name.Text = Logic.Conversion.ToString(dReader("education3Institution"))
            txtSchool3City.Text = Logic.Conversion.ToString(dReader("education3City"))
            ddlSchool3State.SelectedValue = Logic.Conversion.ToString(dReader("education3State"))
            txtSchool3HighestLevel.Text = Logic.Conversion.ToString(dReader("education3HighestDegree"))

        End Using

    End Sub

    Private Sub _SaveForm()

        Dim cmdSave = "
            UPDATE web_DriverApplication
            SET education1Type=@education1Type, education1Institution=@education1Institution, 
                education1City=@education1City, education1State=@education1State, 
                education1HighestDegree=@education1HighestDegree, 
                education2Type=@education2Type, education2Institution=@education2Institution, 
                education2City=@education2City, education2State=@education2State, 
                education2HighestDegree=@education2HighestDegree, 
                education3Type=@education3Type, education3Institution=@education3Institution, 
                education3City=@education3City, education3State=@education3State, 
                education3HighestDegree=@education3HighestDegree
            WHERE id=@appId"

        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("education1Type", txtSchool1Type.Text)
                .AddWithValue("education1Institution", txtSchool1Name.Text)
                .AddWithValue("education1City", txtSchool1City.Text)
                .AddWithValue("education1State", ddlSchool1State.SelectedValue)
                .AddWithValue("education1HighestDegree", txtSchool1HighestLevel.Text)
                .AddWithValue("education2Type", txtSchool2Type.Text)
                .AddWithValue("education2Institution", txtSchool2Name.Text)
                .AddWithValue("education2City", txtSchool2City.Text)
                .AddWithValue("education2State", ddlSchool2State.SelectedValue)
                .AddWithValue("education2HighestDegree", txtSchool2HighestLevel.Text)
                .AddWithValue("education3Type", txtSchool3Type.Text)
                .AddWithValue("education3Institution", txtSchool3Name.Text)
                .AddWithValue("education3City", txtSchool3City.Text)
                .AddWithValue("education3State", ddlSchool3State.SelectedValue)
                .AddWithValue("education3HighestDegree", txtSchool3HighestLevel.Text)
            End With

            cmd.ExecuteNonQuery()

        End Using

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

    End Sub

    Private Sub _SaveDocument()
        Dim ReportDataSet As New dsAppEducation
        Dim ReportTableAdapter As New dsAppEducationTableAdapters.EducationTableAdapter

        Using Report As New crptAppEducation
            ReportTableAdapter.Fill(ReportDataSet.Education, Master.app.id)
            Report.SetDataSource(ReportDataSet)
            Dim rawData = Reporting.StreamToByteArray(Report)

            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