Tree Maker Menu

Imports System.Data

Imports System.Data.SqlClient

Public Class Form_TreeMaker

#Region "deklarasi"

Dim DataNamaRanting(1000) As String

Dim JumlahRanting As Integer

Dim DataNama(100000) As String

Dim DataNik(100000) As String

Dim DataUpNik(100000) As String

Dim DataNamaleader(100000) As String

Dim DataNikleader(100000) As String

Dim DataUpNikleader(100000) As String

Dim Namaleader As String

Dim Nikleader As String

Dim UpNikleader As String

Dim Nama(1000) As String

Dim Nik(1000) As String

Dim UpNik(1000) As String

Dim JumlahData As Integer

#End Region

Sub Clear_Control()

cmbLeader.Text = ""

cmbGrade.Text = ""

cmbSite.Text = ""

cmbPosisi.Text = ""

cmbLokasi.Text = ""

cmbDepartment.Text = ""

cmbLeader.Items.Clear()

cmbGrade.Items.Clear()

cmbSite.Items.Clear()

cmbPosisi.Items.Clear()

cmbLokasi.Items.Clear()

cmbDepartment.Items.Clear()

lBoxNama.Items.Clear()

lboxJumlah.Items.Clear()

lboxLeader.Items.Clear()

ListBox1.Items.Clear()

tv1.Nodes.Clear()

End Sub

Sub VisibleBoxTrue()

lboxLeader.Visible = True

lBoxNama.Visible = True

lboxJumlah.Visible = True

tv1.Visible = False

End Sub

Sub VisibleBoxFalse()

lboxLeader.Visible = False

lBoxNama.Visible = False

lboxJumlah.Visible = False

tv1.Visible = True

End Sub

Private Sub Form_TreeMaker_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

' posisi_control()

ViewComboDepartment()

ViewComboSiteCard()

ViewComboPosisi()

ViewComboLokasi()

ViewComboGrade()

VisibleBoxTrue()

' ViewComboLeader()

End Sub

Function space(ByVal x As String, ByVal n As Integer) As String

While Len(x) <>

x = x + " "

End While

Return x

End Function

Function Rspace(ByVal x As String, ByVal n As Integer) As String

While Len(x) <>

x = " " + x

End While

Return x

End Function

Sub ViewComboDepartment()

Dim MyConnection As New DataAcces.DatabaseConnection

Dim objCommand As SqlCommand

Dim strSQL As String

Dim objDataSet As New DataSet

Dim objDataTable As New DataTable

Try

strSQL = "SELECT department FROM TB_Karyawan GROUP BY department ORDER BY department "

objCommand = New SqlCommand

objCommand.Connection = MyConnection.open

objCommand.CommandType = CommandType.Text

objCommand.CommandText = strSQL

Dim objDataAdapter1 As SqlClient.SqlDataAdapter

objDataAdapter1 = New SqlDataAdapter(objCommand)

objDataAdapter1.Fill(objDataSet, "MDT_Data")

MyConnection.close()

objDataTable = objDataSet.Tables("MDT_Data")

Dim i As Integer

i = 0

While objDataTable.Rows(i).Item("department").ToString <> ""

With objDataTable.Rows(i)

cmbDepartment.Items.Add(.Item("department").ToString)

i = i + 1

End With

End While

Catch When Err.Number <> 0

' MsgBox("Tidak dapat membuat hubungan ke database2" & vbCrLf & Err.Description)

MyConnection.close()

End Try

End Sub

Sub ViewComboSiteCard()

Dim MyConnection As New DataAcces.DatabaseConnection

Dim objCommand As SqlCommand

Dim strSQL As String

Dim objDataSet As New DataSet

Dim objDataTable As New DataTable

Try

strSQL = "SELECT siteCard FROM TB_Karyawan GROUP BY siteCard ORDER BY siteCard "

objCommand = New SqlCommand

objCommand.Connection = MyConnection.open

objCommand.CommandType = CommandType.Text

objCommand.CommandText = strSQL

Dim objDataAdapter1 As SqlClient.SqlDataAdapter

objDataAdapter1 = New SqlDataAdapter(objCommand)

objDataAdapter1.Fill(objDataSet, "MDT_Data")

MyConnection.close()

objDataTable = objDataSet.Tables("MDT_Data")

Dim i As Integer

i = 0

While objDataTable.Rows(i).Item("siteCard").ToString <> ""

With objDataTable.Rows(i)

cmbSite.Items.Add(.Item("siteCard").ToString)

i = i + 1

End With

End While

Catch When Err.Number <> 0

MsgBox("Eror Site card" & vbCrLf & Err.Description)

MyConnection.close()

End Try

End Sub

Sub ViewComboPosisi()

Dim MyConnection As New DataAcces.DatabaseConnection

Dim objCommand As SqlCommand

Dim strSQL As String

Dim objDataSet As New DataSet

Dim objDataTable As New DataTable

Try

strSQL = "SELECT posisi FROM TB_Karyawan GROUP BY posisi ORDER BY posisi"

objCommand = New SqlCommand

objCommand.Connection = MyConnection.open

objCommand.CommandType = CommandType.Text

objCommand.CommandText = strSQL

Dim objDataAdapter1 As SqlClient.SqlDataAdapter

objDataAdapter1 = New SqlDataAdapter(objCommand)

objDataAdapter1.Fill(objDataSet, "MDT_Data")

MyConnection.close()

objDataTable = objDataSet.Tables("MDT_Data")

Dim i As Integer

i = 0

While objDataTable.Rows(i).Item("posisi").ToString <> ""

With objDataTable.Rows(i)

cmbPosisi.Items.Add(.Item("posisi").ToString)

i = i + 1

End With

End While

Catch When Err.Number <> 0

' MsgBox("Tidak dapat membuat hubungan ke database2" & vbCrLf & Err.Description)

MyConnection.close()

End Try

End Sub

Sub ViewComboGrade()

Dim MyConnection As New DataAcces.DatabaseConnection

Dim objCommand As SqlCommand

Dim strSQL As String

Dim objDataSet As New DataSet

Dim objDataTable As New DataTable

Try

strSQL = "SELECT grade FROM TB_Karyawan GROUP BY grade ORDER BY grade"

objCommand = New SqlCommand

objCommand.Connection = MyConnection.open

objCommand.CommandType = CommandType.Text

objCommand.CommandText = strSQL

Dim objDataAdapter1 As SqlClient.SqlDataAdapter

objDataAdapter1 = New SqlDataAdapter(objCommand)

objDataAdapter1.Fill(objDataSet, "MDT_Data")

MyConnection.close()

objDataTable = objDataSet.Tables("MDT_Data")

Dim i As Integer

i = 0

While objDataTable.Rows(i).Item("grade").ToString <> ""

With objDataTable.Rows(i)

cmbGrade.Items.Add(.Item("grade").ToString)

i = i + 1

End With

End While

Catch When Err.Number <> 0

' MsgBox("Tidak dapat membuat hubungan ke database2" & vbCrLf & Err.Description)

MyConnection.close()

End Try

End Sub

Sub ViewComboLokasi()

Dim MyConnection As New DataAcces.DatabaseConnection

Dim objCommand As SqlCommand

Dim strSQL As String

Dim objDataSet As New DataSet

Dim objDataTable As New DataTable

Try

strSQL = "SELECT lokasi FROM TB_Karyawan GROUP BY lokasi ORDER BY lokasi"

objCommand = New SqlCommand

objCommand.Connection = MyConnection.open

objCommand.CommandType = CommandType.Text

objCommand.CommandText = strSQL

Dim objDataAdapter1 As SqlClient.SqlDataAdapter

objDataAdapter1 = New SqlDataAdapter(objCommand)

objDataAdapter1.Fill(objDataSet, "MDT_Data")

MyConnection.close()

objDataTable = objDataSet.Tables("MDT_Data")

Dim i As Integer

i = 0

While objDataTable.Rows(i).Item("lokasi").ToString <> ""

With objDataTable.Rows(i)

cmbLokasi.Items.Add(.Item("lokasi").ToString)

i = i + 1

End With

End While

Catch When Err.Number <> 0

' MsgBox("Tidak dapat membuat hubungan ke database2" & vbCrLf & Err.Description)

MyConnection.close()

End Try

End Sub

Sub ViewComboLeader()

Dim MyConnection As New DataAcces.DatabaseConnection

Dim objCommand As SqlCommand

Dim strSQL As String

Dim objDataSet As New DataSet

Dim objDataTable As New DataTable

Dim i As Integer

Try

strSQL = "SELECT nama, nik,upNik FROM TB_Karyawan WHERE grade like '" + cmbGrade.Text + "%' AND posisi like '" + cmbPosisi.Text + "%' AND lokasi like '" + cmbLokasi.Text + "%' AND siteCard like '" + cmbSite.Text + "%' AND department like '" + cmbDepartment.Text + "%' ORDER by grade desc "

' rtb1.Text = (strSQL)

objCommand = New SqlCommand

objCommand.Connection = MyConnection.open

objCommand.CommandType = CommandType.Text

objCommand.CommandText = strSQL

Dim objDataAdapter1 As SqlClient.SqlDataAdapter

objDataAdapter1 = New SqlDataAdapter(objCommand)

objDataAdapter1.Fill(objDataSet, "MDT_Data")

MyConnection.close()

objDataTable = objDataSet.Tables("MDT_Data")

i = 0

While objDataTable.Rows(i).Item("nama").ToString <> ""

With objDataTable.Rows(i)

DataNamaleader(i) = (.Item("nama").ToString)

DataNikleader(i) = (.Item("nik").ToString)

DataUpNikleader(i) = (.Item("upNik").ToString)

i = i + 1

End With

End While

JumlahData = i

Catch When Err.Number <> 0

MyConnection.close()

End Try

JumlahData = i

cmbLeader.Items.Clear()

cmbLeader.Text = DataNamaleader(0)

For i = 0 To JumlahData - 1

cmbLeader.Items.Add(DataNamaleader(i))

Next

End Sub

Sub ViewComboNama()

Dim MyConnection As New DataAcces.DatabaseConnection

Dim objCommand As SqlCommand

Dim strSQL As String

Dim objDataSet As New DataSet

Dim objDataTable As New DataTable

Dim i As Integer

Try

strSQL = "SELECT nama, nik,upNik FROM TB_Karyawan WHERE grade like '" + cmbGrade.Text + "%' AND posisi like '" + cmbPosisi.Text + "%' AND lokasi like '" + cmbLokasi.Text + "%' AND siteCard like '" + cmbSite.Text + "%' AND department like '" + cmbDepartment.Text + "%' ORDER by grade desc"

' rtb1.Text = (strSQL)

objCommand = New SqlCommand

objCommand.Connection = MyConnection.open

objCommand.CommandType = CommandType.Text

objCommand.CommandText = strSQL

Dim objDataAdapter1 As SqlClient.SqlDataAdapter

objDataAdapter1 = New SqlDataAdapter(objCommand)

objDataAdapter1.Fill(objDataSet, "MDT_Data")

MyConnection.close()

objDataTable = objDataSet.Tables("MDT_Data")

' cmbLeader.Items.Clear()

' cmbNama.Items.Clear()

i = 0

While objDataTable.Rows(i).Item("nama").ToString <> ""

With objDataTable.Rows(i)

DataNama(i) = (.Item("nama").ToString)

DataNik(i) = (.Item("nik").ToString)

DataUpNik(i) = (.Item("upNik").ToString)

i = i + 1

End With

End While

JumlahData = i

Catch When Err.Number <> 0

' MsgBox(strSQL + vbCrLf & Err.Description)

MyConnection.close()

End Try

JumlahData = i

ListBox1.Items.Clear()

For i = 0 To JumlahData - 1

ListBox1.Items.Add(space(DataNama(i), 30) + space(DataNik(i), 20) + space(DataUpNik(i), 20))

Next

End Sub

Private Sub btnFilter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFilter.Click

ViewComboLeader()

Label3.Text = CStr(JumlahData)

ViewComboNama()

listbox1.Visible = True

End Sub

Private Sub btnLeader_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLeader.Click

If (Not Cek_Nama_Double((space(cmbLeader.Text, 30) + space(DataNik(cmbLeader.SelectedIndex), 20) + space(DataUpNik(cmbLeader.SelectedIndex), 20)))) Then

Exit Sub

End If

If btnLeader.Text = "Change" Then

lboxLeader.Items.Clear()

btnLeader.Text = "Leader"

End If

If lboxLeader.Items.Count < color="blue">Then

lboxLeader.Items.Add(space(cmbLeader.Text, 30) + space(DataNikleader(cmbLeader.SelectedIndex), 20) + space(DataUpNikleader(cmbLeader.SelectedIndex), 20))

btnLeader.Text = "Change"

End If

End Sub

Function Cek_Nama_Double(ByVal nama As String) As Boolean

Dim i, n As Integer

n = lBoxNama.Items.Count - 1

For i = 0 To n

If nama = lBoxNama.Items(i) Then

Return False

Exit For

End If

Next

Return True

End Function

Function Cek_Nama_sama_dengan_leader(ByVal nama As String) As Boolean

If nama = lboxLeader.Items(0) Then

Return False

Exit Function

End If

Return True

End Function

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click

Dim n As Integer

Dim cek As Boolean = True

Dim Item As String

Dim x As String

For Each Item In ListBox1.SelectedItems

cek = True

x = Item.ToString()

n = lboxLeader.Items.Count

If (n > 0) Then

If (Not Cek_Nama_sama_dengan_leader(x)) Then

cek = False

End If

End If

n = lBoxNama.Items.Count

If (n > 0) Then

If (Not Cek_Nama_Double(x)) Then

cek = False

End If

End If

If cek = True Then

lBoxNama.Items.Add(x)

End If

Next

lbox_jumlah()

End Sub

Private Sub btnClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClear.Click

If lBoxNama.SelectedItems.Count > 0 Then

Dim index, i, j As Integer

j = 0

For Each index In lBoxNama.SelectedIndices

j = j + 1

Next

index = index - j + 1

For i = 1 To j

lBoxNama.Items.RemoveAt(index)

Next

lBoxNama.Focus()

lbox_jumlah()

End If

End Sub

Sub lbox_jumlah()

Dim n As Integer

n = lBoxNama.Items.Count

Dim t As String

t = space("Total line :", 18)

lboxJumlah.Items.Clear()

lboxJumlah.Items.Add(t + space(Rspace(CStr(n), 6), 10))

End Sub

Private Sub btnInf_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnInf.Click

If lboxLeader.SelectedItems.Count > 0 Then

Form_Informasi_leader.Close()

Form_Informasi_leader.Visible = True

End If

If lBoxNama.SelectedItems.Count > 0 Then

Form_Informasi_leader.Close()

Form_Informasi_nama.Close()

Form_Informasi_nama.Visible = True

End If

End Sub

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click

If btnSave.Text = "SAVE" Then

Dim mYes_No As String = MsgBox("Are you sure ?", vbYesNo)

If mYes_No = vbYes Then

Dim i, n As Integer

n = lBoxNama.Items.Count

SimpanBoxLeader()

SimpanBoxStaf()

Dim MyConnection_Karyawan As New DataAcces.Access_TB_Karyawan

For i = 1 To n

MyConnection_Karyawan.Karyawan_Update_Nik(Nik(i), Nikleader)

Next

ViewDataRanting(Nikleader)

VisibleBoxFalse()

Clear_Control()

Dim ranting As TreeNode

tv1.Nodes.Add(Namaleader)

ranting = tv1.Nodes(0)

For i = 1 To JumlahRanting

ranting.Nodes.Add(DataNamaRanting(i - 1))

Next

btnSave.Text = "CLEAR"

End If

Else

Clear_Control()

btnSave.Text = "SAVE"

ViewComboDepartment()

ViewComboSiteCard()

ViewComboPosisi()

ViewComboLokasi()

ViewComboGrade()

VisibleBoxTrue()

End If

End Sub

Sub SimpanBoxLeader()

Namaleader = Trim(Mid(lboxLeader.Items(0), 1, 30))

Nikleader = Trim(Mid(lboxLeader.Items(0), 31, 20))

UpNikleader = Trim(Mid(lboxLeader.Items(0), 51, 20))

End Sub

Sub SimpanBoxStaf()

Dim i, n As Integer

n = lBoxNama.Items.Count

For i = 0 To n - 1

Nama(i + 1) = Trim(Mid(lBoxNama.Items(i), 1, 30))

Nik(i + 1) = Trim(Mid(lBoxNama.Items(i), 31, 20))

UpNik(i + 1) = Trim(Mid(lBoxNama.Items(i), 51, 20))

Next

End Sub

Sub ViewDataRanting(ByVal x As String)

Dim MyConnection As New DataAcces.DatabaseConnection

Dim objCommand As SqlClient.SqlCommand

Dim strSQL As String

Dim objDataSet As New DataSet

Dim objDataTable As New DataTable

Dim i As Integer

Try

strSQL = "SELECT nama FROM TB_Karyawan WHERE upNik = '" + x + "'"

' MsgBox(strSQL)

objCommand = New SqlClient.SqlCommand

objCommand.Connection = MyConnection.open

objCommand.CommandType = CommandType.Text

objCommand.CommandText = strSQL

Dim objDataAdapter1 As SqlClient.SqlDataAdapter

objDataAdapter1 = New SqlClient.SqlDataAdapter(objCommand)

objDataAdapter1.Fill(objDataSet, "MDT_Data")

MyConnection.close()

objDataTable = objDataSet.Tables("MDT_Data")

i = 0

While objDataTable.Rows(i).Item("nama").ToString <> ""

With objDataTable.Rows(i)

DataNamaRanting(i) = (.Item("nama").ToString)

i = i + 1

End With

End While

Catch When Err.Number <> 0

' MsgBox(strSQL + vbCrLf & Err.Description)

MyConnection.close()

End Try

JumlahRanting = i

End Sub

End Class