Dim baris, kolom, Hargasatuan As Integer
Dim kodebrg, namabrg, statusheader As String
Private Sub Invoice_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Call koneksi()
Call isicombo1()
Call isicombo2()
Call settingdatagrid()
Call tampilkan_detail_Inv()
'Call tampil_dInvoice()
End Sub
Private Sub simpandata()
If Trim(TextBox1.Text) = "" Then Exit Sub
If Trim(TextBox2.Text) = "" Then Exit Sub
If Trim(ComboBox1.Text) = "" Then Exit Sub
If Trim(ComboBox2.Text) = "" Then Exit Sub
If Trim(TextBox3.Text) = "" Then Exit Sub
If Trim(TextBox5.Text) = "" Then Exit Sub
If Trim(TextBox6.Text) = "" Then Exit Sub
If Add.Text = "Simpan" Then
Tampil.Connection = Dtbase
Tampil.CommandType = CommandType.Text
Tampil.CommandText = "insert into Invoice_H(noInvo,tglInvo,noSO,kdcust,kdcarabyr,jnstransaksi,ppn,total,tglentry,useridd) values ('" & Trim(TextBox1.Text) & "', '" & DateTimePicker1.Value & "','" & Microsoft.VisualBasic.Left(ComboBox1.Text, 4) & "','" & TextBox2.Text & "','" & Microsoft.VisualBasic.Left(ComboBox2.Text, 2) & "','" & TextBox3.Text & "'," & Val(TextBox7.Text) & "," & Val(TextBox5.Text) & ",'" & tglskg & "', '" & nmorang & "' )"
Tampil.ExecuteNonQuery()
Else
If Edit.Text = "Simpan" Then
Tampil.Connection = Dtbase
Tampil.CommandType = CommandType.Text
Tampil.CommandText = "update Invoice_H set tglInvo='" & DateTimePicker1.Value & "', noSO='" & Microsoft.VisualBasic.Left(ComboBox1.Text, 4) & "', kdcust='" & TextBox2.Text & "',tglEntry='" & tglskg & "', useridd='" & nmorang & "' where noInvo='" & Trim(TextBox1.Text) & "'"
Tampil.ExecuteNonQuery()
'ppn=" & TextBox7.Text & ",total=" & TextBox5.Text & ",
Else
If Delete.Text = "Ok" Then
'Untuk menghapus detail SO
Tampil.Connection = Dtbase
Tampil.CommandType = CommandType.Text
Tampil.CommandText = "Delete from Invoice_D where noInvo_H='" & Trim(TextBox1.Text) & "'"
Tampil.ExecuteNonQuery()
'Untuk menghapus header SO
Tampil.Connection = Dtbase
Tampil.CommandType = CommandType.Text
Tampil.CommandText = "Delete from Invoice_H where noInvo='" & Trim(TextBox1.Text) & "'"
Tampil.ExecuteNonQuery()
Call settingdatagrid()
Else
End If
End If
End If
End Sub
Private Sub isicombo1()
Call koneksi()
Tampil2.Connection = Dtbase
Tampil2.CommandType = CommandType.Text
Tampil2.CommandText = "select * from SalesOrder "
Tampilkan = Tampil2.ExecuteReader 'tampilkan=menyimpan hasil pembacaan data
If Tampilkan.HasRows = True Then 'klw ada record(data)maka akan bertipe true
ComboBox1.Items.Clear()
While Tampilkan.Read() 'membaca data 1 per 1
ComboBox1.Items.Add(Trim(Tampilkan("NoSO"))) '& " " & Trim(Tampilkan("nmcarabyr")))
End While
End If
End Sub
Private Sub isicombo2()
Call koneksi()
Tampil2.Connection = Dtbase
Tampil2.CommandType = CommandType.Text
Tampil2.CommandText = "select * from Carabayar "
Tampilkan = Tampil2.ExecuteReader 'tampilkan=menyimpan hasil pembacaan data
If Tampilkan.HasRows = True Then 'klw ada record(data)maka akan bertipe true
ComboBox2.Items.Clear()
While Tampilkan.Read() 'membaca data 1 per 1
ComboBox2.Items.Add(Trim(Tampilkan("kdcarabyr")) & " " & Trim(Tampilkan("nmcarabyr")))
End While
End If
End Sub
Private Sub posisiList()
ListBox1.Top = TextBox3.Top + TextBox4.Height
ListBox1.Left = TextBox3.Left
End Sub
Private Sub caritransaksi()
Call koneksi()
Tampil2.Connection = Dtbase
Tampil2.CommandType = CommandType.Text
Tampil2.CommandText = "select * from Jenistransaksi where nmjnstrans like '%" & Trim(TextBox3.Text) & "%' "
Tampilkan = Tampil2.ExecuteReader 'tampilkan=menyimpan hasil pembacaan data
If Tampilkan.HasRows = True Then 'klw ada record(data)maka akan bertipe true
ListBox1.Items.Clear()
ListBox1.Visible = True
Call posisiList()
While Tampilkan.Read() 'membaca data 1 per 1
ListBox1.Items.Add(Trim(Tampilkan("kdjnstrans")) & " " & Trim(Tampilkan("nmjnstrans")))
End While
End If
End Sub
Private Sub carinamatransaksi()
Call koneksi()
Tampil.Connection = Dtbase
Tampil.CommandType = CommandType.Text
Tampil.CommandText = "select * from Jenistransaksi where kdjnstrans='" & Trim(TextBox3.Text) & "'"
Tampilkan = Tampil.ExecuteReader 'tampilkan=menyimpan hasil pembacaan data
If Tampilkan.HasRows = True Then
'klw ada record(data)maka akan bertipe true
While Tampilkan.Read()
TextBox4.Text = Tampilkan("nmjnstrans")
End While
End If
End Sub
Private Sub caristatusheader()
Call koneksi()
Tampil2.Connection = Dtbase
Tampil2.CommandType = CommandType.Text
Tampil2.CommandText = "select * from Invoice_H where noInvo='" & Trim(TextBox1.Text) & "'"
Tampilkan = Tampil2.ExecuteReader
If Tampilkan.HasRows = True Then
statusheader = "T"
Else
statusheader = "F"
End If
End Sub
Private Sub carinamacustomer()
Call koneksi()
Tampil2.Connection = Dtbase
Tampil2.CommandType = CommandType.Text
Tampil2.CommandText = "select * from Customer where kdcust ='" & Trim(TextBox2.Text) & "' "
Tampilkan = Tampil2.ExecuteReader 'tampilkan=menyimpan hasil pembacaan data
If Tampilkan.HasRows = True Then 'klw ada record(data)maka akan bertipe true
While Tampilkan.Read() 'membaca data 1 per 1
TextBox8.Text = Trim(Tampilkan("nmcust"))
End While
End If
End Sub
Private Sub bersih()
TextBox1.Text = ""
TextBox2.Text = ""
ComboBox1.Text = ""
ComboBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
TextBox6.Text = ""
TextBox7.Text = ""
TextBox8.Text = ""
End Sub
Private Sub normal()
Add.Text = "Add"
Edit.Text = "Edit"
Delete.Text = "Delete"
Keluar.Text = "Exit"
Add.Enabled = True
Edit.Enabled = True
Delete.Enabled = True
Keluar.Text = "Exit"
End Sub
Private Sub nomorOtomatis()
If DG1 IsNot Nothing Then
Dim count As Integer = 0
While (count <= (DG1.Rows.Count - 2))
DG1.Rows(count).HeaderCell.Value = String.Format((count + 1).ToString(), "0")
count += 1
End While
End If
End Sub
Private Sub ListBox1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ListBox1.Click
ListBox1.Visible = False
ListBox1.Top = 12
TextBox3.Text = Microsoft.VisualBasic.Left(ListBox1.Text, 4)
End Sub
Private Sub TextBox3_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox3.TextChanged
If Len(TextBox3.Text) > 0 Then
Call caritransaksi()
Call carinamatransaksi()
End If
End Sub
Private Sub caridata_H()
Call koneksi()
Tampil.Connection = Dtbase
Tampil.CommandType = CommandType.Text
Tampil.CommandText = "select * from Invoice_H where noInvo='" & Trim(TextBox1.Text) & "'"
Tampilkan = Tampil.ExecuteReader 'tampilkan=menyimpan hasil pembacaan data
If Tampilkan.HasRows = True Then 'klw ada record(data)maka akan bertipe true
While Tampilkan.Read() 'membaca data 1 per 1
If IsDBNull(Tampilkan("noInvo")) Then '
TextBox1.Focus()
Else
DateTimePicker1.Value = Tampilkan("tglInvo")
TextBox2.Text = Tampilkan("kdcust")
ComboBox1.Text = Tampilkan("noSO")
ComboBox2.Text = Tampilkan("kdcarabyr")
TextBox3.Text = Tampilkan("jnstransaksi")
'TextBox5.Text = Tampilkan("total")
'TextBox7.Text = Tampilkan("ppn")
TextBox2.Focus()
End If
End While
End If
End Sub
Private Sub settingdatagrid()
'untuk menentukan jumlah kolom
DG1.ColumnCount = 5
DG1.RowCount = 5
'untuk menentukan nama header kolom
DG1.Columns(0).Name = "Kode Barang"
DG1.Columns(1).Name = "Nama Barang"
DG1.Columns(2).Name = "Qty"
DG1.Columns(3).Name = "Satuan Harga"
DG1.Columns(4).Name = "Total Harga"
'menentukan lebar kolom
DG1.Columns(0).Width = 120
DG1.Columns(1).Width = 250
DG1.Columns(2).Width = 100
DG1.Columns(3).Width = 100
DG1.Columns(4).Width = 100
Call AddButtonColumn()
'format kolom sesuai dengan keingninan
DG1.Columns(3).DefaultCellStyle.Alignment = DataGridViewContentAlignment.BottomRight
DG1.Columns(4).DefaultCellStyle.Alignment = DataGridViewContentAlignment.BottomRight
End Sub
Private Sub AddButtonColumn()
'untuk membuat tombol button di datagridview
Dim btn = New DataGridViewButtonColumn()
DG1.Columns.Add(btn)
btn.HeaderText = "Simpan"
btn.Text = "Simpan"
btn.Name = "btnsimpan"
btn.UseColumnTextForButtonValue = True
End Sub
Private Sub bersihbaris()
DG1.Rows.Item(baris).Cells(0).Value = ""
DG1.Rows.Item(baris).Cells(1).Value = ""
DG1.Rows.Item(baris).Cells(2).Value = ""
End Sub
Private Sub tampilkan_detail_Inv()
Call koneksi()
Dim n As Integer
DG1.RowCount = 1
DG1.RowCount = 5
Tabel = "SELECT SODetail.kdbrg, Barang.nmbrg, SODetail.qty,SODetail.hargasatuan, SODetail.totalharga"
Tabel = Tabel + " FROM SODetail INNER JOIN Barang ON SODetail.kdbrg = Barang.kdbrg WHERE SODetail.nosoH= '" & Trim(ComboBox1.Text) & "'"
Tampil4.Connection = Dtbase
Tampil4.CommandType = CommandType.Text
'MsgBox(Tabel)
Tampil4.CommandText = Tabel
Tampilkan = Tampil4.ExecuteReader 'tampilkan=menyimpan hasil pembacaan data
If Tampilkan.HasRows = True Then 'klw ada record(data)maka akan bertipe true
n = 0
While Tampilkan.Read() 'membaca data 1 per 1
DG1.Rows.Item(n).Cells(0).Value = Tampilkan("kdbrg")
DG1.Rows.Item(n).Cells(1).Value = Trim(Tampilkan("nmbrg"))
DG1.Rows.Item(n).Cells(2).Value = Tampilkan("qty")
DG1.Rows.Item(n).Cells(3).Value = Tampilkan("hargasatuan")
DG1.Rows.Item(n).Cells(4).Value = Tampilkan("totalharga")
n = n + 1
End While
DG1.RowCount = n + 1
'Dim nn As Integer = dgv1.Rows.Add()
End If
Dim nn As Integer = DG1.Rows.Add()
'Dim nn As Integer = dgv1.Rows.Add()
Tampilkan.Close()
Tampil4.ExecuteReader.Close()
End Sub
Private Sub tampil_dInvoice()
Call koneksi()
Dim n As Integer
DG1.RowCount = 1
DG1.RowCount = 5
Tabel = "SELECT Invoice_D.kdbrg, Barang.nmbrg, Invoice_D.qty,Invoice_D.hargasatuan, Invoice_D.totalharga"
Tabel = Tabel + " FROM Invoice_D INNER JOIN Barang ON Invoice_D.kdbrg = Barang.kdbrg WHERE Invoice_D.noInvo_H= '" & Trim(TextBox1.Text) & "'"
Tampil4.Connection = Dtbase
Tampil4.CommandType = CommandType.Text
'MsgBox(Tabel)
Tampil4.CommandText = Tabel
Tampilkan = Tampil4.ExecuteReader 'tampilkan=menyimpan hasil pembacaan data
If Tampilkan.HasRows = True Then 'klw ada record(data)maka akan bertipe true
n = 0
While Tampilkan.Read() 'membaca data 1 per 1
DG1.Rows.Item(n).Cells(0).Value = Tampilkan("kdbrg")
DG1.Rows.Item(n).Cells(1).Value = Trim(Tampilkan("nmbrg"))
DG1.Rows.Item(n).Cells(2).Value = Tampilkan("qty")
DG1.Rows.Item(n).Cells(3).Value = Tampilkan("hargasatuan")
DG1.Rows.Item(n).Cells(4).Value = Tampilkan("totalharga")
n = n + 1
End While
DG1.RowCount = n + 1
'Dim nn As Integer = DG1.Rows.Add()
End If
Dim nn As Integer = DG1.Rows.Add()
Tampilkan.Close()
Tampil4.ExecuteReader.Close()
End Sub
Private Sub bersih_datagrid()
DG1.RowCount = 1
DG1.Refresh()
DG1.RowCount = 5
End Sub
Private Sub simpan_datagridview()
'cek sudah terisi semua belum
If DG1.Rows.Item(baris).Cells(0).Value = "" Then Exit Sub
If DG1.Rows.Item(baris).Cells(1).Value = "" Then Exit Sub
If Trim(DG1.Rows.Item(baris).Cells(2).Value) = "" Then Exit Sub
If Trim(DG1.Rows.Item(baris).Cells(3).Value) = "" Then Exit Sub
If Trim(DG1.Rows.Item(baris).Cells(4).Value) = "" Then Exit Sub
If Add.Text = "Simpan" Then
Tampil.Connection = Dtbase
Tampil.CommandType = CommandType.Text
Tampil.CommandText = "insert into Invoice_D(noInvo_H,kdbrg,qty,hargasatuan,totalharga,tglEntry,useridd) values ('" & Trim(TextBox1.Text) & "', '" & Trim(DG1.Rows.Item(baris).Cells(0).Value) & "'," & Val(DG1.Rows.Item(baris).Cells(2).Value) & "," & Val(DG1.Rows.Item(baris).Cells(3).Value) & "," & Val(DG1.Rows.Item(baris).Cells(4).Value) & ",'" & tglskg & "', '" & nmorang & "')"
Tampil.ExecuteNonQuery()
' Call tampilkan_detail_SO()
Else
If Edit.Text = "Simpan" Then
Tampil.Connection = Dtbase
Tampil.CommandType = CommandType.Text
Tampil.CommandText = "update Invoice_D set qty = " & Val(DG1.Rows.Item(baris).Cells(2).Value) & ",hargasatuan=" & Val(DG1.Rows.Item(baris).Cells(3).Value) & ",totalharga =" & Val(DG1.Rows.Item(baris).Cells(4).Value) & " where noInvo_H='" & Trim(TextBox1.Text) & "' and kdbrg= '" & Trim(DG1.Rows.Item(baris).Cells(0).Value) & "'"
Tampil.ExecuteNonQuery()
'Call tampilkan_detail_SO()
Else
If Delete.Text = "Ok" Then
Tampil.Connection = Dtbase
Tampil.CommandType = CommandType.Text
Tampil.CommandText = "Delete from Invoice_D where noInvo_H='" & Trim(TextBox1.Text) & "' and kdbrg= '" & Trim(DG1.Rows.Item(baris).Cells(0).Value) & "'"
Tampil.ExecuteNonQuery()
Call bersihbaris()
End If
End If
End If
End Sub
Private Sub TextBox1_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles TextBox1.KeyPress
If e.KeyChar = Chr(Keys.Enter) Then
Call caridata_H()
Call carinamacustomer()
Call tampilkan_detail_Inv()
'Call tampil_dInvoice()
End If
End Sub
Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox1.TextChanged
If Len(TextBox1.Text) > 0 Then
Call tampilkan_detail_Inv()
'Call tampil_dInvoice()
Call penjumlahan()
End If
End Sub
Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
Call koneksi()
Call tampilkan_detail_Inv()
Tampil2.Connection = Dtbase
Tampil2.CommandType = CommandType.Text
Tampil2.CommandText = "select * from SalesOrder where NoSO='" & Trim(ComboBox1.Text) & "'"
Tampilkan2 = Tampil2.ExecuteReader 'tampilkan=menyimpan hasil pembacaan data
If Tampilkan2.HasRows = True Then 'klw ada record(data)maka akan bertipe true
While Tampilkan2.Read() 'membaca data 1 per 1
If IsDBNull(Tampilkan2("NoSO")) Then '
TextBox1.Focus()
'Call tampilkan_detail_SJ()
Else
TextBox2.Text = Tampilkan2("kdcust")
TextBox2.Focus()
End If
End While
End If
Call carinamacustomer()
End Sub
Private Sub Add_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Add.Click
If Add.Text = "Add" Then 'jk button1 d klik,mka button 1 berubah jd simpan & button4 jd batal
TextBox1.Focus()
Add.Text = "Simpan"
Edit.Enabled = False
Delete.Enabled = False
Keluar.Text = "Batal"
Else
' Call simpandata() 'jk tdak maka call simpan data, call bersih,call normal
Call bersih()
Call normal()
Call bersih_datagrid()
End If
End Sub
Private Sub Edit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Edit.Click
If Edit.Text = "Edit" Then 'jk button1 d klik,mka button 1 berubah jd simpan & button4 jd batal
TextBox1.Focus()
Edit.Text = "Simpan"
Add.Enabled = False
Delete.Enabled = False
Keluar.Text = "Batal"
Else
Call simpandata() 'jk tdak maka call simpan data, call bersih,call normal
Call bersih()
Call normal()
Call bersih_datagrid()
End If
End Sub
Private Sub Delete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Delete.Click
If Delete.Text = "Delete" Then 'jk button1 d klik,mka button 1 berubah jd simpan & button4 jd batal
TextBox1.Focus()
Delete.Text = "Ok"
Add.Enabled = False
Edit.Enabled = False
Keluar.Text = "Batal"
Else
Call simpandata() 'jk tdak maka call simpan data, call bersih,call normal
Call bersih()
Call normal()
Call bersih_datagrid()
End If
End Sub
Private Sub Keluar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Keluar.Click
If Keluar.Text = "Batal" Then
Call normal()
Call bersih()
Call bersih_datagrid()
TextBox1.Focus()
Else
Me.Close() 'untuk menutup form diri sendiri'
End If
End Sub
Private Sub DG1_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DG1.CellClick
baris = DG1.CurrentRow.Index
'untuk menentukan kolom yang mana
If DG1.Columns(e.ColumnIndex).Name = "Kode Barang" Then
kolom = 1
Else
If DG1.Columns(e.ColumnIndex).Name = "Nama Barang" Then
kolom = 2
Else
If DG1.Columns(e.ColumnIndex).Name = "Harga" Then
kolom = 3
Else
If DG1.Columns(e.ColumnIndex).Name = "Qty" Then
kolom = 4
Else
kolom = 5
End If
End If
End If
End If
If kolom = 5 Then
If Add.Text = "Simpan" Then
'dgv1.Rows.Add()
Call caristatusheader()
If statusheader = "T" Then
Call simpan_datagridview()
Call penjumlahan()
Call updateTOTAL()
Else
Call simpandata()
Call simpan_datagridview()
Call penjumlahan()
Call updateTOTAL()
End If
Else
If Edit.Text = "Simpan" Then
Call simpan_datagridview()
Call penjumlahan()
Else
If Delete.Text = "Ok" Then
Call simpan_datagridview()
Call penjumlahan()
End If
End If
End If
End If
End Sub
Private Sub DG1_CellEnter(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DG1.CellEnter
' untuk menunjukan kolom yang mana
If kolom = 1 Then
'untuk mengambil nilai dari cell datagridview
kodebrg = DG1.Rows.Item(baris).Cells(0).Value
' memanggil method untuk menampilkan data barang
' Call tampilkan_data_barang()
'menentukan posisi listbox
'Call posisilist2()
End If
'If kolom = 2 Then
'End If
If kolom = 3 Then
End If
If kolom = 4 Then
'isi total harga
DG1.Rows.Item(baris).Cells(4).Value = Val(DG1.Rows.Item(baris).Cells(3).Value) * Val(DG1.Rows.Item(baris).Cells(2).Value)
End If
Call nomorOtomatis()
'isi total harga
'DataGridView1.Rows.Item(baris).Cells(4).Value = Val(DataGridView1.Rows.Item(baris).Cells(3).Value) * Val(DataGridView1.Rows.Item(baris).Cells(2).Value)
End Sub
Private Sub penjumlahan()
Call koneksi()
Tampil5.Connection = Dtbase
Tampil5.CommandType = CommandType.Text
Tampil5.CommandText = "SELECT SUM(totalharga) AS subtotal FROM Invoice_D WHERE noInvo_H = '" & Trim(TextBox1.Text) & "'"
Tampilkan = Tampil5.ExecuteReader
If Tampilkan.HasRows = True Then
Do While Tampilkan.Read()
If IsDBNull(Tampilkan("subtotal")) Then
Else
TextBox6.Text = Format(Tampilkan("subtotal"), "###,###,###")
TextBox7.Text = Format(Tampilkan("subtotal"), "###,###,###") * 0.1
TextBox5.Text = Format(Tampilkan("subtotal"), "###,###,###") + Val(TextBox7.Text)
End If
Loop
End If
'Tampil5.Connection = Database
'Tampil5.CommandType = CommandType.Text
'Tampil5.CommandText = "SELECT SUM(totalharga) AS subtotal FROM Detailinvoice WHERE NoinvoiceH = '" & Trim(TextBox1.Text) & "'"
'Tampilkan3 = Tampil5.ExecuteReader
'If Tampilkan3.HasRows = True Then
' Do While Tampilkan3.Read()
' If IsDBNull(Tampilkan3("subtotal")) Then
' Else
' TextBox7.Text = Format(Tampilkan3("subtotal"), "###,###,###")
End Sub
Private Sub updateTOTAL()
Tampil.Connection = Dtbase
Tampil.CommandType = CommandType.Text
Tampil.CommandText = "update Invoice_H set total= " & Val(TextBox5.Text) & " where noInvo='" & Trim(TextBox1.Text) & "'"
Tampil.ExecuteNonQuery()
End Sub
Private Sub Print_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Print.Click
noprint = TextBox1.Text
lap_in.Show()
End Sub
Private Sub DG1_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DG1.CellContentClick
End Sub
End Class
Tidak ada komentar:
Posting Komentar