Imports System.Data.SqlClient
Imports System.Data.Common
Imports System.Configuration
Imports System.Data.SqlServerCe
Imports System
Imports System.Net
Imports System.IO
Imports System.Text
Imports System.Text.RegularExpressions

Public Class frmLocalJ

    Dim Cnxn As New SqlCeConnection(My.Settings.Database1ConnectionString)
    Private Sub frmDisplayJV_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Debug.Print(vbCrLf & "At head of Load..." & vbCrLf)
        cbJVDate.Items.Clear()
        Dim ADate, SQLStmt As String
        Dim ACounter As Integer
        Dim DateReader As SqlCeDataReader
        Cnxn.Open()
        SQLStmt = "select distinct CONVERT(nvarchar(10), AccountingDate, 101) AS ADate from Orders order by ADate DESC"
        Debug.Print(vbCrLf & " using '" & SQLStmt & "'" & vbCrLf)
        Dim DateCommand As New SqlCeCommand(SQLStmt, Cnxn)
        Try
            DateReader = DateCommand.ExecuteReader
        Catch ex As Exception
            MsgBox("ExecuteReader got " & ex.Message & " using '" & SQLStmt & "'")
            Exit Sub
        End Try
        ACounter = 0
        Do While DateReader.Read()
            ADate = DateReader.Item("ADate")
            cbJVDate.Items.Add(ADate)
            ACounter += 1
        Loop

        Cnxn.Close()
        lblAdvice.ForeColor = Color.Black
        lblAdvice.Text = "Select the date to journalize from the drop down box..."
        Me.cbJVDate.Focus()

    End Sub

    Private Sub cbJVDate_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbJVDate.SelectedIndexChanged
        Dim SQLStmt, NetDisp, JournalDate, LedgerSQLStmt As String
        Dim Net, JVNet As Decimal
        Dim NetCounter As Integer
        Cnxn.Open()
        SQLStmt = "SELECT LedgerId, sum(Qty * Each) as Net FROM Orders, Details where CONVERT(nvarchar(10), AccountingDate, 101) = '" & _
            cbJVDate.Text & _
            "' and Orders.Id = Details.OrderId group by LedgerId order by LedgerId"
        Dim JVCommand As New SqlCeCommand(SQLStmt, Cnxn)
        Dim JVReader As SqlCeDataReader
        lblAdvice.Text = ""
        Debug.Print(vbCrLf & "Got Here with '" & SQLStmt & "'" & vbCrLf)
        Try
            JVReader = JVCommand.ExecuteReader
        Catch ex As Exception
            MsgBox("Execute JVReader with '" & SQLStmt & "' got " & ex.Message)
            Exit Sub
        End Try
        JVNet = 0
        NetCounter = 0
        lbEntry.Text = ""
        lblValue.Text = ""
        Do While JVReader.Read()
            JournalDate = cbJVDate.Text
            LedgerSQLStmt = String.Format("Select Description from Ledger where Id ='" & _
            JVReader.Item("LedgerId") & _
            "' Order by Description")
            Dim JVCommand1 As New SqlCeCommand(LedgerSQLStmt, Cnxn)
            Dim JVReader1 As SqlCeDataReader
            JVReader1 = JVCommand1.ExecuteReader
            Do While JVReader1.Read()
                Net = JVReader.Item("Net")
                JVNet += Net
                NetCounter += 1
                NetDisp = Format(Net, "0.00;(0.00)")
                NetDisp = NetDisp.PadLeft(10, " ")
                If Net < 0 Then NetDisp = " " & NetDisp
                lbEntry.Text += JournalDate & "     " & JVReader1.Item("Description") & vbCrLf
                lblValue.Text += NetDisp & vbCrLf
            Loop
            lbTotal.Text = "Net Total = " & JVNet
        Loop
        If NetCounter = 0 Then
            lblAdvice.ForeColor = Color.DarkRed
            lblAdvice.Text = "There are no details posted for " & cbJVDate.Text & "..."
        ElseIf JVNet = 0 Then
            lblAdvice.ForeColor = Color.Black
            lblAdvice.Text = "The journal for this date nets zero."
        Else
            lblAdvice.ForeColor = Color.DarkRed
            lblAdvice.Text = "The journal for this date does not net zero." & vbCrLf & _
               vbCrLf & "It nets " & Format(JVNet, "0.00")
        End If
        Cnxn.Close()

    End Sub
End Class