TransJual V2.0

Imports MySql.Data.MySqlClient
Imports System.Data.DataTable
Public Class TransJualV2
    Dim Value1 As Long
    Dim TABLE As New DataTable("Table")
    Dim cSQL As String
    Dim ToTal As Long

   
    Private Sub NoNotaOtomatis()
        KONEKSIS()
        CMDD = New MySqlCommand("Select * from transjualheader where NoNota in (select max(NoNota) from transjualheader)", CONN)
        DR = CMDD.ExecuteReader
        DR.Read()
        Dim URUTAN As String
        Dim HITUNG As Integer
        Dim TAHUN As String
        Dim BULAN As String
        Dim DEFUSER As String
        Dim HasilUSER As String


        TAHUN = Format(Now, "yyyy")
        BULAN = Format(Now, "MM")
        If Not DR.HasRows Then
            DR.Close()
            KONEKSIS()
            DEFUSER = "Select Kode, Alias from userprimary where alias= '" & Label4.Text & "'"
            CMDD = New MySqlCommand(DEFUSER, CONN)
            DR = CMDD.ExecuteReader
            DR.Read()
            HasilUSER = DR.Item("Kode")
            URUTAN = "TJ" + TAHUN + BULAN + "-" + "000001" + "-" + HasilUSER
            Label2.Text = URUTAN
        Else
            HITUNG = Microsoft.VisualBasic.Mid(DR.Item("NoNota"), 10, 6) + 1
            Label2.Text = HITUNG

            If Len(Label2.Text) = 1 Then
                DR.Close()
                KONEKSIS()
                DEFUSER = "Select Kode, Alias from userprimary where alias= '" & Label4.Text & "'"
                CMDD = New MySqlCommand(DEFUSER, CONN)
                DR = CMDD.ExecuteReader
                DR.Read()
                HasilUSER = DR.Item("Kode")
                Label2.Text = "TJ" & TAHUN & BULAN & "-" & "00000" & HITUNG & "-" & HasilUSER

            ElseIf Len(Label2.Text) = 2 Then
                DR.Close()
                KONEKSIS()
                DEFUSER = "Select Kode, Alias from userprimary where alias= '" & Label4.Text & "'"
                CMDD = New MySqlCommand(DEFUSER, CONN)
                DR = CMDD.ExecuteReader
                DR.Read()
                HasilUSER = DR.Item("Kode")
                Label2.Text = "TJ" & TAHUN & BULAN & "-" & "0000" & HITUNG & "-" & HasilUSER

            ElseIf Len(Label2.Text) = 3 Then
                DR.Close()
                KONEKSIS()
                DEFUSER = "Select Kode, Alias from userprimary where alias= '" & Label4.Text & "'"
                CMDD = New MySqlCommand(DEFUSER, CONN)
                DR = CMDD.ExecuteReader
                DR.Read()
                HasilUSER = DR.Item("Kode")
                Label2.Text = "TJ" & TAHUN & BULAN & "-" & "000" & HITUNG & "-" & HasilUSER

            ElseIf Len(Label2.Text) = 4 Then
                DR.Close()
                KONEKSIS()
                DEFUSER = "Select Kode, Alias from userprimary where alias= '" & Label4.Text & "'"
                CMDD = New MySqlCommand(DEFUSER, CONN)
                DR = CMDD.ExecuteReader
                DR.Read()
                HasilUSER = DR.Item("Kode")
                Label2.Text = "TJ" & TAHUN & BULAN & "-" & "00" & HITUNG & "-" & HasilUSER

            ElseIf Len(Label2.Text) = 5 Then
                DR.Close()
                KONEKSIS()
                DEFUSER = "Select Kode, Alias from userprimary where alias= '" & Label4.Text & "'"
                CMDD = New MySqlCommand(DEFUSER, CONN)
                DR = CMDD.ExecuteReader
                DR.Read()
                HasilUSER = DR.Item("Kode")
                Label2.Text = "TJ" & TAHUN & BULAN & "-" & "0" & HITUNG & "-" & HasilUSER

            ElseIf Len(Label2.Text) = 6 Then
                DR.Close()
                KONEKSIS()
                DEFUSER = "Select Kode, Alias from userprimary where alias= '" & Label4.Text & "'"
                CMDD = New MySqlCommand(DEFUSER, CONN)
                DR = CMDD.ExecuteReader
                DR.Read()
                HasilUSER = DR.Item("Kode")
                Label2.Text = "TJ" & TAHUN & BULAN & "-" & HITUNG & "-" & HasilUSER

            End If


        End If

    End Sub
    Private Sub CTable()
        TABLE.Columns.Add("Kode Barang", Type.GetType("System.String"))
        TABLE.Columns.Add("Nama Barang", Type.GetType("System.String"))
        TABLE.Columns.Add("QTY", Type.GetType("System.String"))
        TABLE.Columns.Add("Harga Jual", Type.GetType("System.String"))
        TABLE.Columns.Add("Discount", Type.GetType("System.String"))
        TABLE.Columns.Add("Total Item", Type.GetType("System.String"))

        DGV.DataSource = TABLE
        DGV.Columns(0).Width = 10
        DGV.Columns(0).Visible = False
        DGV.Columns(1).Width = 200
        DGV.Columns(2).Width = 75
        DGV.Columns(3).Width = 100
        DGV.Columns(4).Width = 75
        DGV.Columns(5).Width = 150
        DGV.Columns(1).HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleLeft

    End Sub
    Private Sub TransJualV2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Label6.Text = Now
        Label4.Text = Depan.Label2.Text
        Label5.Text = "0"
        CTable()
        NoNotaOtomatis()
        Timer1.Start()
        TextBox2.Enabled = False
    End Sub

    Private Sub TextBox1_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles TextBox1.KeyDown
        Dim x As Integer
        Dim ketemu As Boolean
        If e.KeyCode = Keys.Down Then
            DGV.Focus()
        End If
        If e.KeyCode = Keys.Enter Then
            KONEKSIS()
            cSQL = "select KodeBarang, NamaBarang, HargaEceran, HargaMember, HargaGrosir1, HargaGrosir2, Discount from stokbarang where KodeBarang like '" & TextBox1.Text & "' or NamaBarang like '" & TextBox1.Text & "' order by NamaBarang"
            CMDD = New MySqlCommand(cSQL, CONN)
            DR = CMDD.ExecuteReader
            DR.Read()
            ToTal = 0
            ketemu = False
            If DR.HasRows Then
                For x = 0 To DGV.Rows.Count - 2
                    If DGV.Rows(x).Cells(0).Value = TextBox1.Text Then
                        ketemu = True
                        Exit For
                    End If
                Next x
                If ketemu Then
                    DGV.Rows(x).Cells(2).Value = DGV.Rows(x).Cells(2).Value + 1
                    DGV.Rows(x).Cells(5).Value = ((DGV.Rows(x).Cells(2).Value * DGV.Rows(x).Cells(3).Value)) - ((DGV.Rows(x).Cells(2).Value * DGV.Rows(x).Cells(3).Value) * (DGV.Rows(x).Cells(4).Value / 100))
                Else
                    TABLE.Rows.Add(DR.Item("KodeBarang"), DR.Item("NamaBarang"), 1, DR.Item("HargaEceran"), DR.Item("Discount"), (1 * DR.Item("HargaEceran")) - (1 * DR.Item("HargaEceran") * DR.Item("Discount") / 100))
                End If
                For x = 0 To DGV.Rows.Count - 1
                    ToTal = ToTal + ((DGV.Rows(x).Cells(2).Value * DGV.Rows(x).Cells(3).Value)) - ((DGV.Rows(x).Cells(2).Value * DGV.Rows(x).Cells(3).Value) * (DGV.Rows(x).Cells(4).Value / 100))
                Next
                Label5.Text = CStr(ToTal)
                TextBox1.Text = ""
            Else
                If MsgBox("Mau Di Ketik Manual?!!!", MsgBoxStyle.YesNo, "Pilihan!") = MsgBoxResult.Yes Then
                    'TABLE.Rows.Add(1, TextBox1.Text, 1, 0, 0, 0)
                    'TextBox1.Text = ""
                    TextBox2.Enabled = True
                    TextBox2.Focus()
                Else
                    MsgBox("Cari Kode Yang Lain!!!!!", MsgBoxStyle.Information, "Info!")
                End If

            End If
            'TextBox1.Text = ""
        End If


        If e.KeyCode = Keys.F4 Then
            If MsgBox("Silahkan Pilih 'Ya' untuk menyimpan transaksi ini!!!", MsgBoxStyle.YesNo, "Pilihan!") = MsgBoxResult.Yes Then
                Call Button2_Click(sender, e)
            Else
                MsgBox("Kembali Ke transaksi Sebelumnya", MsgBoxStyle.Information, "Info!")
            End If
        End If

        If e.KeyCode = Keys.F3 Then
            If MsgBox("Apakah akan Membatalkan Transaksi Ini?!!!", MsgBoxStyle.YesNo, "Pilihan!") = MsgBoxResult.Yes Then
                TABLE.Clear()
            Else
                MsgBox("Kembali Ke transaksi Sebelumnya", MsgBoxStyle.Information, "Info!")
            End If
        End If

        If e.KeyCode = Keys.Insert Then
            CariTransPenjualanV2.Show()
        End If

    End Sub

    Private Sub DGV_CellEnter(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DGV.CellEnter

    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Try
            Dim x As Integer, hBeli As Integer, disc As Integer, stok As Integer
            DR.Close()
            KONEKSIS()

            CMD.Connection = CONN
            CMD.CommandType = CommandType.Text
            CMD.CommandText = "insert into transjualheader(TglTrans, NoNota, Netto, Operator) values('" & Label6.Text & "','" & Label2.Text & "','" & Label5.Text & "','" & Label4.Text & "')"
            CMD.ExecuteNonQuery()
            CONN.Close()
            'MsgBox("masuk ke header")

            For x = 0 To DGV.Rows.Count - 2

                DR.Close()
                KONEKSIS()
                cSQL = "Select * from stokbarang where KodeBarang='" & DGV.Rows(x).Cells(0).Value & "'"
                CMDD = New MySqlCommand(cSQL, CONN)
                DR = CMDD.ExecuteReader
                DR.Read()
                If DR.HasRows Then
                    hBeli = DR.Item("hargabeli")
                    disc = DR.Item("discount")
                    stok = DR.Item("QtyToko") - DGV.Rows(x).Cells(2).Value
                    KONEKSIS()
                    CMD.Connection = CONN
                    CMD.CommandType = CommandType.Text
                    cSQL = "update stokbarang set QtyToko = " & CStr(stok) & " where kodebarang = '" & DGV.Rows(x).Cells(0).Value & "'"
                    CMD.CommandText = cSQL
                    CMD.ExecuteNonQuery()
                    CONN.Close()
                    'MsgBox("masuk ke kurangi stok")
                End If
                DR.Close()
                KONEKSIS()
                CMD.Connection = CONN
                CMD.CommandType = CommandType.Text
                cSQL = "insert into transjualisi(NoKodeUrut, NoNota, KodeBarang, Qty, HargaJual, HargaBeli, Discount, TotalJual) values('" & x & "','" & Label2.Text & "','" & DGV.Rows(x).Cells(0).Value & "','" & DGV.Rows(x).Cells(2).Value & "','" & DGV.Rows(x).Cells(3).Value & "','" & hBeli & "','" & disc & "','" & DGV.Rows(x).Cells(5).Value & "')"
                CMD.CommandText = cSQL
                CMD.ExecuteNonQuery()
                CONN.Close()
                'MsgBox("masuk ke ISI")
            Next
            MsgBox("Data Berhasil Disimpan")
            Label5.Text = "0"
            TextBox1.Focus()
            NoNotaOtomatis()
            TABLE.Clear()
        Catch ex As Exception
            MsgBox("Data Gagal")
        End Try
    End Sub

    Private Sub DGV_CellValidating(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellValidatingEventArgs) Handles DGV.CellValidating
     
    End Sub

    Private Sub DGV_CellValueChanged(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DGV.CellValueChanged

        Dim x As Integer
        ToTal = 0
        For x = 0 To DGV.Rows.Count - 1
            If IsDBNull(DGV.Rows(x).Cells(2).Value) Then
                'MsgBox("Bekerja boss yang cell Quantity")
                DGV.Rows(x).Cells(2).Value = "1"
            ElseIf IsDBNull(DGV.Rows(x).Cells(3).Value) Then
                'MsgBox("Bekerja boss yang HARGA JUAL")
                DGV.Rows(x).Cells(3).Value = "0"
            ElseIf IsDBNull(DGV.Rows(x).Cells(0).Value) Then
                'MsgBox("Bekerja boss Kode Barangnya")
                DGV.Rows(x).Cells(0).Value = "0"
            ElseIf IsDBNull(DGV.Rows(x).Cells(1).Value) Then
                'MsgBox("Bekerja boss Kode Barangnya")
                DGV.Rows(x).Cells(1).Value = "BARANG KOSONG"
            ElseIf IsDBNull(DGV.Rows(x).Cells(4).Value) Then
                'MsgBox("Bekerja boss Kode Barangnya")
                DGV.Rows(x).Cells(4).Value = "0"
            ElseIf IsDBNull(DGV.Rows(x).Cells(5).Value) Then
                'MsgBox("Bekerja boss Kode Barangnya")
                DGV.Rows(x).Cells(5).Value = "0"
            Else
                ToTal = ToTal + ((DGV.Rows(x).Cells(2).Value * DGV.Rows(x).Cells(3).Value)) - ((DGV.Rows(x).Cells(2).Value * DGV.Rows(x).Cells(3).Value) * (DGV.Rows(x).Cells(4).Value / 100))
            End If
        Next

        If DGV.Rows.Count > 0 Then
            If e.ColumnIndex = 2 Then
                Dim TotalIsi As Integer = CInt(DGV.Rows(e.RowIndex).Cells(5).Value)
                Dim Discount As Integer = CInt(DGV.Rows(e.RowIndex).Cells(4).Value)
                Dim QTYY As Integer = CInt(DGV.Rows(e.RowIndex).Cells(2).Value)
                Dim HJual As Integer = CInt(DGV.Rows(e.RowIndex).Cells(3).Value)
                Dim TotalAkhir As Integer = QTYY * HJual - (QTYY * HJual * Discount / 100)
                DGV.Rows(e.RowIndex).Cells(5).Value = TotalAkhir
            ElseIf e.ColumnIndex = 3 Then
                Dim TotalIsi As Integer = CInt(DGV.Rows(e.RowIndex).Cells(5).Value)
                Dim Discount As Integer = CInt(DGV.Rows(e.RowIndex).Cells(4).Value)
                Dim QTYY As Integer = CInt(DGV.Rows(e.RowIndex).Cells(2).Value)
                Dim HJual As Integer = CInt(DGV.Rows(e.RowIndex).Cells(3).Value)
                Dim TotalAkhir As Integer = QTYY * HJual - (QTYY * HJual * Discount / 100)
                DGV.Rows(e.RowIndex).Cells(5).Value = TotalAkhir
            ElseIf e.ColumnIndex = 4 Then
                Dim TotalIsi As Integer = CInt(DGV.Rows(e.RowIndex).Cells(5).Value)
                Dim Discount As Integer = CInt(DGV.Rows(e.RowIndex).Cells(4).Value)
                Dim QTYY As Integer = CInt(DGV.Rows(e.RowIndex).Cells(2).Value)
                Dim HJual As Integer = CInt(DGV.Rows(e.RowIndex).Cells(3).Value)
                Dim TotalAkhir As Integer = QTYY * HJual - (QTYY * HJual * Discount / 100)
                DGV.Rows(e.RowIndex).Cells(5).Value = TotalAkhir
            End If
        End If
        Label5.Text = CStr(ToTal)
        'End If


    End Sub

   
    Private Sub DGV_GotFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles DGV.GotFocus
     

    End Sub

    Private Sub DGV_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles DGV.KeyDown
        If e.KeyCode = Keys.Delete Then
            DGV.Rows.RemoveAt(DGV.CurrentCell.RowIndex)
            ToTal = 0
            For x = 0 To DGV.Rows.Count - 1
                ToTal = ToTal + ((DGV.Rows(x).Cells(2).Value * DGV.Rows(x).Cells(3).Value)) - ((DGV.Rows(x).Cells(2).Value * DGV.Rows(x).Cells(3).Value) * (DGV.Rows(x).Cells(4).Value / 100))
            Next
            Label5.Text = CStr(ToTal)
        End If
        If e.KeyCode = Keys.ShiftKey Then
            TextBox1.Focus()
        End If
        If e.KeyCode = Keys.Insert Then
            CariTransPenjualanV2.Show()
        End If
        If e.KeyCode = Keys.F4 Then
            If MsgBox("Silahkan Pilih 'Ya' untuk menyimpan transaksi ini!!!", MsgBoxStyle.YesNo, "Pilihan!") = MsgBoxResult.Yes Then
                Call Button2_Click(sender, e)
            Else
                MsgBox("Kembali Ke transaksi Sebelumnya", MsgBoxStyle.Information, "Info!")
            End If
        End If

        If e.KeyCode = Keys.F3 Then
            If MsgBox("Apakah akan Membatalkan Transaksi Ini?!!!", MsgBoxStyle.YesNo, "Pilihan!") = MsgBoxResult.Yes Then
                TABLE.Clear()
            Else
                MsgBox("Kembali Ke transaksi Sebelumnya", MsgBoxStyle.Information, "Info!")
            End If
        End If
    End Sub

    Private Sub DGV_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DGV.CellContentClick

    End Sub

    Private Sub DGV_LocationChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DGV.LocationChanged
     
    End Sub

    Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox1.TextChanged

    End Sub

    Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick
        Label6.Text = Now
    End Sub

    Private Sub TextBox2_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles TextBox2.KeyDown
        If e.KeyCode = Keys.Enter Then
            TABLE.Rows.Add(1, TextBox1.Text, 1, TextBox2.Text, 0, TextBox2.Text)
            TextBox1.Text = ""
            TextBox1.Focus()
            TextBox2.Text = ""
            TextBox2.Enabled = False
        End If
    End Sub

    Private Sub TextBox2_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles TextBox2.KeyPress

    End Sub

    Private Sub TextBox2_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox2.TextChanged

    End Sub
End Class