Imports System.Data.SqlClient
Imports CrystalDecisions.CrystalReports.Engine
Imports Logic.NullableDateExtensions
Imports Logic.NullableDoubleExtensions
Imports ReportingClass

Public Class OnDutyHours
    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()
                sigCompensatedWork.DefaultSigUrl = "/_assets/DefaultAppSig.aspx?appId=" & Master.app.id
                sigOnDutyHours.DefaultSigUrl = "/_assets/DefaultAppSig.aspx?appId=" & Master.app.id
            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

                applicantNameLast + ', ' + applicantNameFirst + ISNULL(' ' + applicantNameMiddle, '') + ISNULL(', ' + applicantNameSuffix, '') AS applicantName,
                applicantSocial,
                OnDutyHoursLicenseNumber, OnDutyHoursLicenseState, OnDutyHoursLicenseClass,
                OnDutyHoursLicenseEndorsements, OnDutyHoursLicenseRestrictions,
                OnDutyHoursWorkDate1, OnDutyHoursWorkDate2, OnDutyHoursWorkDate3, OnDutyHoursWorkDate4,
                OnDutyHoursWorkDate5, OnDutyHoursWorkDate6, OnDutyHoursWorkDate7,
                OnDutyHoursWorkHours1, OnDutyHoursWorkHours2, OnDutyHoursWorkHours3, OnDutyHoursWorkHours4,
                OnDutyHoursWorkHours5, OnDutyHoursWorkHours6, OnDutyHoursWorkHours7, OnDutyHoursWorkHoursTotal,
                OnDutyHoursRelievedDate, OnDutyHoursSignatureId,

                CurrentlyEmployed, MoonlightingIntended, CompensatedWorkSignatureId,

                license1Number, license1Class, license1State, license1Endorsements, license1Restrictions

            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()

            ' OnDutyHours

            txtName.Text = dReader("applicantName")
            txtLicenseNumber.Text = Logic.Conversion.ToString(dReader("OnDutyHoursLicenseNumber"))
            ddlLicenseState.SelectedValue = Logic.Conversion.ToString(dReader("OnDutyHoursLicenseState"))
            txtLicenseClass.Text = Logic.Conversion.ToString(dReader("OnDutyHoursLicenseClass"))
            txtLicenseEndorsements.Text = Logic.Conversion.ToString(dReader("OnDutyHoursLicenseEndorsements"))
            txtLicenseRestrictions.Text = Logic.Conversion.ToString(dReader("OnDutyHoursLicenseRestrictions"))
            txtWorkDate1.Text = Logic.Conversion.ToNullableDate(dReader("OnDutyHoursWorkDate1")).FormatForHtml()
            txtWorkDate2.Text = Logic.Conversion.ToNullableDate(dReader("OnDutyHoursWorkDate2")).FormatForHtml()
            txtWorkDate3.Text = Logic.Conversion.ToNullableDate(dReader("OnDutyHoursWorkDate3")).FormatForHtml()
            txtWorkDate4.Text = Logic.Conversion.ToNullableDate(dReader("OnDutyHoursWorkDate4")).FormatForHtml()
            txtWorkDate5.Text = Logic.Conversion.ToNullableDate(dReader("OnDutyHoursWorkDate5")).FormatForHtml()
            txtWorkDate6.Text = Logic.Conversion.ToNullableDate(dReader("OnDutyHoursWorkDate6")).FormatForHtml()
            txtWorkDate7.Text = Logic.Conversion.ToNullableDate(dReader("OnDutyHoursWorkDate7")).FormatForHtml()
            txtWorkHours1.Text = Logic.Conversion.ToNullableDouble(dReader("OnDutyHoursWorkHours1")).FormatForHtml()
            txtWorkHours2.Text = Logic.Conversion.ToNullableDouble(dReader("OnDutyHoursWorkHours2")).FormatForHtml()
            txtWorkHours3.Text = Logic.Conversion.ToNullableDouble(dReader("OnDutyHoursWorkHours3")).FormatForHtml()
            txtWorkHours4.Text = Logic.Conversion.ToNullableDouble(dReader("OnDutyHoursWorkHours4")).FormatForHtml()
            txtWorkHours5.Text = Logic.Conversion.ToNullableDouble(dReader("OnDutyHoursWorkHours5")).FormatForHtml()
            txtWorkHours6.Text = Logic.Conversion.ToNullableDouble(dReader("OnDutyHoursWorkHours6")).FormatForHtml()
            txtWorkHours7.Text = Logic.Conversion.ToNullableDouble(dReader("OnDutyHoursWorkHours7")).FormatForHtml()
            txtRelievedDate.Text = Logic.Conversion.ToNullableDate(dReader("OnDutyHoursRelievedDate")).FormatForHtml()
            txtRelievedTime.Text = Logic.Conversion.ToNullableDate(dReader("OnDutyHoursRelievedDate")).FormatForHtmlTime()
            sigOnDutyHours.SignatureId = Logic.Conversion.ToNullableInteger(dReader("OnDutyHoursSignatureId"))

            If txtWorkDate1.Text.Length = 0 AndAlso txtWorkDate2.Text.Length = 0 AndAlso
                txtWorkDate3.Text.Length = 0 AndAlso txtWorkDate4.Text.Length = 0 AndAlso
                txtWorkDate5.Text.Length = 0 AndAlso txtWorkDate6.Text.Length = 0 AndAlso
                txtWorkDate7.Text.Length = 0 Then
                txtWorkDate1.Text = Today.AddDays(-1).ToString("yyyy-MM-dd")
                txtWorkDate2.Text = Today.AddDays(-2).ToString("yyyy-MM-dd")
                txtWorkDate3.Text = Today.AddDays(-3).ToString("yyyy-MM-dd")
                txtWorkDate4.Text = Today.AddDays(-4).ToString("yyyy-MM-dd")
                txtWorkDate5.Text = Today.AddDays(-5).ToString("yyyy-MM-dd")
                txtWorkDate6.Text = Today.AddDays(-6).ToString("yyyy-MM-dd")
                txtWorkDate7.Text = Today.AddDays(-7).ToString("yyyy-MM-dd")
            End If

            ' Compensated Work

            ynCurrentlyEmployed.Checked = Logic.Conversion.ToNullableBoolean(dReader("CurrentlyEmployed"))
            ynMoonglightingIntended.Checked = Logic.Conversion.ToNullableBoolean(dReader("MoonlightingIntended"))
            sigCompensatedWork.SignatureId = Logic.Conversion.ToNullableInteger(dReader("CompensatedWorkSignatureId"))

            ' Prefill if needed
            If IsDBNull(dReader("OnDutyHoursLicenseNumber")) Then
                txtLicenseNumber.Text = Logic.Conversion.ToString(dReader("license1Number"))
                ddlLicenseState.SelectedValue = Logic.Conversion.ToString(dReader("license1State"))
                txtLicenseClass.Text = Logic.Conversion.ToString(dReader("license1Class"))
                txtLicenseEndorsements.Text = Logic.Conversion.ToString(dReader("license1Endorsements"))
                txtLicenseRestrictions.Text = Logic.Conversion.ToString(dReader("license1Restrictions"))
            End If

        End Using

    End Sub

    Private Sub _SaveForm()

        Dim cmdSave = "
            UPDATE web_DriverApplication
            SET 
                OnDutyHoursLicenseNumber=@LicenseNumber,  OnDutyHoursLicenseState=@LicenseState, 
                OnDutyHoursLicenseClass=@LicenseClass, OnDutyHoursLicenseEndorsements=@LicenseEndorsements, 
                OnDutyHoursLicenseRestrictions=@LicenseRestrictions, 
                OnDutyHoursWorkDate1=@WorkDate1, OnDutyHoursWorkDate2=@WorkDate2, OnDutyHoursWorkDate3=@WorkDate3, 
                OnDutyHoursWorkDate4=@WorkDate4, OnDutyHoursWorkDate5=@WorkDate5, OnDutyHoursWorkDate6=@WorkDate6, 
                OnDutyHoursWorkDate7=@WorkDate7, 
                OnDutyHoursWorkHours1=@WorkHours1, OnDutyHoursWorkHours2=@WorkHours2, 
                OnDutyHoursWorkHours3=@WorkHours3, OnDutyHoursWorkHours4=@WorkHours4, 
                OnDutyHoursWorkHours5=@WorkHours5, OnDutyHoursWorkHours6=@WorkHours6, 
                OnDutyHoursWorkHours7=@WorkHours7, OnDutyHoursWorkHoursTotal=@WorkHoursTotal, 
                OnDutyHoursRelievedDate=@RelievedDate, OnDutyHoursSignatureId=@SignatureId,

                CurrentlyEmployed=@employed, MoonlightingIntended=@moonlight, CompensatedWorkSignatureId=@sigId

            WHERE id=@appId"

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

            ' OnDutyHours
            Dim WorkDate1 = Logic.Conversion.ToNullableDate(txtWorkDate1.Text)
            Dim WorkDate2 = Logic.Conversion.ToNullableDate(txtWorkDate2.Text)
            Dim WorkDate3 = Logic.Conversion.ToNullableDate(txtWorkDate3.Text)
            Dim WorkDate4 = Logic.Conversion.ToNullableDate(txtWorkDate4.Text)
            Dim WorkDate5 = Logic.Conversion.ToNullableDate(txtWorkDate5.Text)
            Dim WorkDate6 = Logic.Conversion.ToNullableDate(txtWorkDate6.Text)
            Dim WorkDate7 = Logic.Conversion.ToNullableDate(txtWorkDate7.Text)
            Dim WorkHours1 = Logic.Conversion.ToNullableDouble(txtWorkHours1.Text)
            Dim WorkHours2 = Logic.Conversion.ToNullableDouble(txtWorkHours2.Text)
            Dim WorkHours3 = Logic.Conversion.ToNullableDouble(txtWorkHours3.Text)
            Dim WorkHours4 = Logic.Conversion.ToNullableDouble(txtWorkHours4.Text)
            Dim WorkHours5 = Logic.Conversion.ToNullableDouble(txtWorkHours5.Text)
            Dim WorkHours6 = Logic.Conversion.ToNullableDouble(txtWorkHours6.Text)
            Dim WorkHours7 = Logic.Conversion.ToNullableDouble(txtWorkHours7.Text)
            Dim WorkHoursTotal = WorkHours1 + WorkHours2 + WorkHours3 + WorkHours4 + WorkHours5 + WorkHours6 + WorkHours7
            Dim RelievedDate = Logic.Conversion.ToNullableDate(txtRelievedDate.Text)
            Dim RelievedTime = Logic.Conversion.ToNullableDate(txtRelievedTime.Text)
            Dim sigOnDutyHoursId = sigOnDutyHours.SignatureId
            Dim RelievedDateTime = If(RelievedDate.HasValue AndAlso RelievedTime.HasValue,
                RelievedDate.Value.AddHours(RelievedTime.Value.Hour).AddMinutes(RelievedTime.Value.Minute),
                CType(Nothing, Date?))

            ' CompensatedWork
            Dim employed = ynCurrentlyEmployed.Checked
            Dim moonlight = ynMoonglightingIntended.Checked
            Dim sigCompensatedWorkId = sigCompensatedWork.SignatureId

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

                ' OnDutyHours
                .AddWithValue("LicenseNumber", txtLicenseNumber.Text)
                .AddWithValue("LicenseState", ddlLicenseState.SelectedValue)
                .AddWithValue("LicenseClass", txtLicenseClass.Text)
                .AddWithValue("LicenseEndorsements", txtLicenseEndorsements.Text)
                .AddWithValue("LicenseRestrictions", txtLicenseRestrictions.Text)
                .AddWithValue("WorkDate1", If(WorkDate1.HasValue, WorkDate1.Value, DBNull.Value))
                .AddWithValue("WorkDate2", If(WorkDate2.HasValue, WorkDate2.Value, DBNull.Value))
                .AddWithValue("WorkDate3", If(WorkDate3.HasValue, WorkDate3.Value, DBNull.Value))
                .AddWithValue("WorkDate4", If(WorkDate4.HasValue, WorkDate4.Value, DBNull.Value))
                .AddWithValue("WorkDate5", If(WorkDate5.HasValue, WorkDate5.Value, DBNull.Value))
                .AddWithValue("WorkDate6", If(WorkDate6.HasValue, WorkDate6.Value, DBNull.Value))
                .AddWithValue("WorkDate7", If(WorkDate7.HasValue, WorkDate7.Value, DBNull.Value))
                .AddWithValue("WorkHours1", If(WorkHours1.HasValue, WorkHours1.Value, DBNull.Value))
                .AddWithValue("WorkHours2", If(WorkHours2.HasValue, WorkHours2.Value, DBNull.Value))
                .AddWithValue("WorkHours3", If(WorkHours3.HasValue, WorkHours3.Value, DBNull.Value))
                .AddWithValue("WorkHours4", If(WorkHours4.HasValue, WorkHours4.Value, DBNull.Value))
                .AddWithValue("WorkHours5", If(WorkHours5.HasValue, WorkHours5.Value, DBNull.Value))
                .AddWithValue("WorkHours6", If(WorkHours6.HasValue, WorkHours6.Value, DBNull.Value))
                .AddWithValue("WorkHours7", If(WorkHours7.HasValue, WorkHours7.Value, DBNull.Value))
                .AddWithValue("WorkHoursTotal", If(WorkHoursTotal.HasValue, WorkHoursTotal.Value, DBNull.Value))
                .AddWithValue("RelievedDate", If(RelievedDateTime.HasValue, RelievedDateTime.Value, DBNull.Value))
                .AddWithValue("SignatureId", If(sigOnDutyHoursId.HasValue, sigOnDutyHoursId.Value, DBNull.Value))

                ' CompensatedWork
                .AddWithValue("employed", If(employed.HasValue, employed.Value, DBNull.Value))
                .AddWithValue("moonlight", If(moonlight.HasValue, moonlight.Value, DBNull.Value))
                .AddWithValue("sigId", If(sigCompensatedWorkId.HasValue, sigCompensatedWorkId.Value, DBNull.Value))

            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 dsAppOnDutyHours
        Dim ReportTableAdapter As New dsAppOnDutyHoursTableAdapters.AppOnDutyHoursTableAdapter

        Using Report As New crptAppOnDutyHours
            ReportTableAdapter.Fill(ReportDataSet.AppOnDutyHours, Master.app.id)
            Report.SetDataSource(ReportDataSet)

            Dim PictureBox As PictureObject = Report.ReportDefinition.ReportObjects("picSigOnDutyHours")
            Dim LinkedFormula As FormulaFieldDefinition = Report.DataDefinition.FormulaFields("pathSigOnDutyHours")
            Dim SignatureId = Logic.Conversion.ToInteger(ReportDataSet.AppOnDutyHours.Rows(0)("sigOnDutyHoursId"))
            Reporting.SetPictureToSignature(PictureBox, LinkedFormula, SignatureId, 5600)
            
            PictureBox = Report.ReportDefinition.ReportObjects("picSigCompensatedWork")
            LinkedFormula = Report.DataDefinition.FormulaFields("pathSigCompensatedWork")
            SignatureId = Logic.Conversion.ToInteger(ReportDataSet.AppOnDutyHours.Rows(0)("sigCompensatedWorkId"))
            Reporting.SetPictureToSignature(PictureBox, LinkedFormula, SignatureId, 5600)

            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