kas

Imports System.Data.OleDb
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared
Public Class FKas
    Dim laporan As New ReportDocument
    Dim TabelLogon As CrystalDecisions.Shared.TableLogOnInfo
    Dim val1 As Long
    Sub SetingLaporan()
        Dim BacaTabel As CrystalDecisions.CrystalReports.Engine.Table
        For Each BacaTabel In laporan.Database.Tables
            TabelLogon = BacaTabel.LogOnInfo
            With TabelLogon.ConnectionInfo
                .ServerName = Application.StartupPath & "\db_keuangan.accdb"
                .UserID = ""
                .Password = ""
                .DatabaseName = ""
            End With
            BacaTabel.ApplyLogOnInfo(TabelLogon)
        Next BacaTabel
    End Sub
    Sub NOTIS()
        Try
            CMD = New OleDbCommand("select IdKas from Kas order by IdKas desc", Conn)
            DR = CMD.ExecuteReader
            DR.Read()
            If Not DR.HasRows Then
                txtNomor.Text = "00001"
            Else
                txtNomor.Text = Format(Microsoft.VisualBasic.Right(DR.Item("IdKas"), 5) + 1, "00000")
            End If
        Catch ex As Exception

        End Try
    End Sub
    Sub jumlah()
        '  On Error Resume Next
        Try
            CMD = New OleDbCommand("select sum(JPemasukan) as [debet],sum(JPengeluaran) as [kredit],sum(Saldo) as [saldo] from Kas", Conn)
            DR = CMD.ExecuteReader
            DR.Read()
            txtJumlahDebet.Text = DR.Item("debet")
            txtJumlahKredit.Text = DR.Item("kredit")
            txtJumlahSaldo.Text = DR.Item("saldo")

        Catch ex As Exception

        End Try

    End Sub
    Sub Kosongkan()
        txtNomor.Clear()
        txtNomor.Clear()
        txtKet.Clear()
        txtKredit.Clear()
        txtDebet.Clear()
        txtCari.Clear()
        txtNomor.Focus()
        Call TampilGrid()
        Call NOTIS()
        Call jumlah()
        dtpTanggal.Focus()
    End Sub
    Sub DataBaru()
        txtNomor.Clear()
        txtKet.Clear()
        txtKredit.Clear()
        txtCari.Clear()
        txtNomor.Focus()
    End Sub



    Sub Ketemu()
        txtNomor.Text = DR.Item("IdKas")
        dtpTanggal.Text = DR.Item("TglKas")
        txtKet.Text = DR.Item("KetKas")
        txtKredit.Text = DR.Item("JPemasukan")
        txtNomor.Focus()
    End Sub



    Sub TampilGrid()
        DA = New OleDbDataAdapter("select * from Kas order by TglKas desc", Conn)
        DS = New DataSet
        DA.Fill(DS)
        dgv.DataSource = DS.Tables(0)
        dgv.ReadOnly = True
        dgv.Columns(0).Visible = False
        dgv.Columns(1).HeaderText = "Tanggal"
        dgv.Columns(2).HeaderText = "Keterangan"
        dgv.Columns(3).HeaderText = "Debet"
        dgv.Columns(4).HeaderText = "Kredit"
        dgv.Columns(5).HeaderText = "Saldo"
        dgv.Columns(3).DefaultCellStyle.Format = "0,00"
        dgv.Columns(4).DefaultCellStyle.Format = "0,00"
        dgv.Columns(5).DefaultCellStyle.Format = "0,00"
    End Sub
    Private Sub FKas_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Call Koneksi()
        Call Kosongkan()
        Call NOTIS()
        Call jumlah()
    End Sub

    Private Sub btnBatal_Click(sender As Object, e As EventArgs) Handles btnBatal.Click
        Call Kosongkan()


    End Sub

    Private Sub btnSimpan_Click(sender As Object, e As EventArgs) Handles btnSimpan.Click
        If txtDebet.Text = "" Then txtDebet.Text = 0
        If txtKredit.Text = "" Then txtKredit.Text = 0
        CMD = New OleDbCommand("select * from Kas where IdKas='" & txtNomor.Text & "'", Conn)
        DR = CMD.ExecuteReader
        DR.Read()

        Try
            If Not DR.HasRows Then
                Dim simpankas As String = "insert into Kas values ('" & txtNomor.Text & "','" & dtpTanggal.Text & "','" & txtKet.Text & "','" & txtDebet.Text & "','" & txtKredit.Text & "','" & txtDebet.Text - txtKredit.Text & "')"
                CMD = New OleDbCommand(simpankas, Conn)
                CMD.ExecuteNonQuery()
                If Val(txtDebet.Text) <> 0 Then
                    Dim simpandebet As String = "insert into Pemasukan values ('" & txtNomor.Text & "','" & dtpTanggal.Text & "','" & txtKet.Text & "','" & txtDebet.Text & "')"
                    CMD = New OleDbCommand(simpandebet, Conn)
                    CMD.ExecuteNonQuery()
                ElseIf Val(txtKredit.Text) <> 0 Then
                    Dim simpankredit As String = "insert into Pengeluaran values ('" & txtNomor.Text & "','" & dtpTanggal.Text & "','" & txtKet.Text & "','" & txtKredit.Text & "')"
                    CMD = New OleDbCommand(simpankredit, Conn)
                    CMD.ExecuteNonQuery()
                End If
            Else
                Dim ediKas As String = "update Kas set TglKas='" & dtpTanggal.Text & "',KetKas='" & txtKet.Text & "',JPemasukan='" & txtDebet.Text & "',JPengeluaran='" & txtKredit.Text & "',Saldo='" & txtDebet.Text - txtKredit.Text & "' where IdKas='" & txtNomor.Text & "'"
                CMD = New OleDbCommand(ediKas, Conn)
                CMD.ExecuteNonQuery()
                If Val(txtDebet.Text) <> 0 Then
                    Dim editdebet As String = "update Pemasukan set TglPemasukan='" & dtpTanggal.Text & "',KetPemasukan='" & txtKet.Text & "',JPemasukan='" & txtDebet.Text & "' where IdPemasukan='" & txtNomor.Text & "'"
                    CMD = New OleDbCommand(editdebet, Conn)
                    CMD.ExecuteNonQuery()
                ElseIf Val(txtKredit.Text) <> 0 Then
                    Dim editkredit As String = "update Pengeluaran set TglPengeluaran='" & dtpTanggal.Text & "',KetPengeluaran='" & txtKet.Text & "',JPengeluaran='" & txtKredit.Text & "' where IdPengeluaran='" & txtNomor.Text & "'"
                    CMD = New OleDbCommand(editkredit, Conn)
                    CMD.ExecuteNonQuery()

                End If
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        Call Kosongkan()
        Call jumlah()

    End Sub
    Private Sub dgv_CellMouseClick(sender As Object, e As DataGridViewCellMouseEventArgs) Handles dgv.CellMouseClick
        On Error Resume Next
        txtNomor.Text = dgv.Rows(e.RowIndex).Cells(0).Value
        dtpTanggal.Text = dgv.Rows(e.RowIndex).Cells(1).Value
        ' txtNomor.Text = dgv.Rows(e.RowIndex).Cells(2).Value
        txtKet.Text = dgv.Rows(e.RowIndex).Cells(2).Value
        txtDebet.Text = dgv.Rows(e.RowIndex).Cells(3).Value
        txtKredit.Text = dgv.Rows(e.RowIndex).Cells(4).Value
    End Sub

    Private Sub btnHapus_Click(sender As Object, e As EventArgs) Handles btnHapus.Click
        If txtNomor.Text = "" Then
            MsgBox("Kode Kas harus diisi")
            txtNomor.Focus()
            Exit Sub
        End If
        If MessageBox.Show("yakin akan dihapus..?", "", MessageBoxButtons.YesNo) = Windows.Forms.DialogResult.Yes Then

            Dim hapuskas As String = "delete * from Kas where IdKas='" & txtNomor.Text & "'"
            CMD = New OleDbCommand(hapuskas, Conn)
            CMD.ExecuteNonQuery()

            If Val(txtDebet.Text) <> 0 Then
                Dim hapusdebet As String = "delete * from Pemasukan where IdPemasukan='" & txtNomor.Text & "'"
                CMD = New OleDbCommand(hapusdebet, Conn)
                CMD.ExecuteNonQuery()
            ElseIf Val(txtKredit.Text) <> 0 Then
                Dim hapuskredit As String = "delete * from Pengeluaran where IdPengeluaran='" & txtNomor.Text & "'"
                CMD = New OleDbCommand(hapuskredit, Conn)
                CMD.ExecuteNonQuery()
            End If
            Call Kosongkan()
        Else
            Call Kosongkan()
        End If
    End Sub
    Private Sub txtNomor_LostFocus(sender As Object, e As EventArgs)
        CMD = New OleDbCommand("select * from Kas where IdKas='" & txtNomor.Text & "'", Conn)
        DR = CMD.ExecuteReader
        DR.Read()
        If Not DR.HasRows Then
            Call DataBaru()
        Else
            Call Ketemu()
        End If
    End Sub

    Private Sub txtCari_TextChanged(sender As Object, e As EventArgs) Handles txtCari.TextChanged
        Call Koneksi()
        DA = New OleDbDataAdapter("select * from Kas where IdKas like '%" & txtCari.Text & "%' or KetKas like '%" & txtCari.Text & "%'", Conn)
        DS = New DataSet
        DA.Fill(DS)
        dgv.DataSource = DS.Tables(0)
    End Sub

    Private Sub txtDebet_TextChanged(sender As Object, e As EventArgs) Handles txtDebet.TextChanged
        Try
            val1 = txtDebet.Text
            txtDebet.Text = Format(val1, "##,##0")
            txtDebet.SelectionStart = Len(txtDebet.Text)
        Catch ex As Exception

        End Try

    End Sub

    Private Sub txtKredit_TextChanged(sender As Object, e As EventArgs) Handles txtKredit.TextChanged
        Try
            val1 = txtKredit.Text
            txtKredit.Text = Format(val1, "##,##0")
            txtKredit.SelectionStart = Len(txtKredit.Text)
        Catch ex As Exception

        End Try

    End Sub

    Private Sub txtJumlahDebet_TextChanged(sender As Object, e As EventArgs) Handles txtJumlahDebet.TextChanged
        Try
            val1 = txtJumlahDebet.Text
            txtJumlahDebet.Text = Format(val1, "##,##0")
            txtJumlahDebet.SelectionStart = Len(txtJumlahDebet.Text)
        Catch ex As Exception

        End Try
    End Sub

    Private Sub txtJumlahKredit_TextChanged(sender As Object, e As EventArgs) Handles txtJumlahKredit.TextChanged
        Try
            val1 = txtJumlahKredit.Text
            txtJumlahKredit.Text = Format(val1, "##,##0")
            txtJumlahKredit.SelectionStart = Len(txtJumlahKredit.Text)
        Catch ex As Exception

        End Try
    End Sub

    Private Sub txtJumlahSaldo_TextChanged(sender As Object, e As EventArgs) Handles txtJumlahSaldo.TextChanged
        Try
            val1 = txtJumlahSaldo.Text
            txtJumlahSaldo.Text = Format(val1, "##,##0")
            txtJumlahSaldo.SelectionStart = Len(txtJumlahSaldo.Text)
        Catch ex As Exception

        End Try
    End Sub


    Private Sub txtJPengeluaraneyPress(sender As Object, e As KeyPressEventArgs) Handles txtKredit.KeyPress
        If Not ((e.KeyChar >= "0" And e.KeyChar <= "9") Or e.KeyChar = vbBack) Then e.Handled = True
    End Sub

    Private Sub btnLaporan_Click(sender As Object, e As EventArgs) Handles btnLaporan.Click
        Try

            FLaporan.Show()
            laporan.Load(Application.StartupPath & "\Kas.rpt")
            Call SetingLaporan()
            FLaporan.CRV.ReportSource = laporan
            FLaporan.CRV.RefreshReport()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub txtJPemasukaneyPress(sender As Object, e As KeyPressEventArgs) Handles txtDebet.KeyPress
        If Not ((e.KeyChar >= "0" And e.KeyChar <= "9") Or e.KeyChar = vbBack) Then e.Handled = True
    End Sub
End Class