กานส้างสิดทิ form Admin เพื่อนำไปส้างสิดทิให้กับ Admin

'======================== กานส้าง SQLSER 2008 ================================
/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 1000 [UserName]
      ,[Password]
      ,[Auth]
      ,[IsNormal]
  FROM [daofa].[dbo].[UserName]
'========================= กานส้างVB.net==========================
Option Explicit On
Option Strict On
Imports System.Text
Imports System.Data
Imports System.Data.SqlClient
Imports System.Drawing.Drawing2D
Imports System.IO
Imports System.Security.Cryptography

Public Class frmAdmin

    Dim Conn As SqlConnection
    Dim com As SqlCommand
    Dim dr As SqlDataReader
    Dim tr As SqlTransaction
    Dim sb As StringBuilder

    Dim CUserName As String = ""

    Dim strConn As String = "Data Source=.\SQLExpress;Initial Catalog=daofa;Integrated Security=True"
    'Dim strConn As String = "Data Source=ATHLON2500;Initial Catalog=HRM;User ID=vs2005;Password=1234"

    Private Sub frmAdmin_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        lblUserName.BackColor = Color.FromArgb(0, 0, 0, 0)
        lblPassword.BackColor = Color.FromArgb(0, 0, 0, 0)
        lblPasswordAgain.BackColor = Color.FromArgb(0, 0, 0, 0)

        Conn = New SqlConnection()
        With Conn
            If .State = ConnectionState.Open Then .Close()
            .ConnectionString = strConn
            .Open()
        End With

        ShowAllUserName()
    End Sub

    Private Sub ShowAllUserName()
        sb = New StringBuilder()
        sb.Append("SELECT UserName FROM UserName")
        sb.Append(" WHERE (IsNormal='1')")
        Dim sqlShow As String
        sqlShow = sb.ToString()

        com = New SqlCommand()
        With com
            .CommandText = sqlShow
            .CommandType = CommandType.Text
            .Connection = Conn
            dr = .ExecuteReader()
            If dr.HasRows Then
                Dim dtUserName As DataTable
                dtUserName = New DataTable
                dtUserName.Load(dr)

                dgvUserNameList.DataSource = dtUserName
                dgvUserNameList.Columns(0).Width = 115

                cmdAdjust.Enabled = True
            Else
                CUserName = ""
                dgvUserNameList.DataSource = Nothing
                cmdAdjust.Enabled = False
            End If
        End With
        dr.Close()
    End Sub

    Private Sub cmdSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSave.Click
        If txtUserName.Text.Trim() = "" Then
            MessageBox.Show("ກະລຸນາປ້ອນ UserName ກ່ອນ !!!", "ຜົນການທົດສອບ", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            txtUserName.Focus()
            Exit Sub
        End If

        If txtPassword.Text.Trim() = "" Then
            MessageBox.Show("ກະລຸນາປ້ອນລະຫັດ Password !!!", "ຜົນການທົດສອບ", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            txtPassword.Focus()
            Exit Sub
        End If

        If txtPasswordAgain.Text.Trim() = "" Then
            MessageBox.Show("ກະລຸນາປ້ອນລະຫັດ Password ຊ້ຳອີກຄັ້ງ !!!", "ຜົນການທົດສອບ", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            txtPasswordAgain.Focus()
            Exit Sub
        End If

        If txtUserName.Text.Length < 4 Then
            MessageBox.Show("UserName ຕ້ອງມີຈຳນວນຕົວອັກສອນລະຫ່ວາງ 4-16 ຕົວອັກສອນ !!!", "ຜົນການທົດສອບ", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            txtUserName.Focus()
            txtUserName.SelectAll()
            Exit Sub
        End If

        If txtPassword.Text.Length < 4 Then
            MessageBox.Show("ລະຫັດ Password ຕ້ອງມີຈຳນວນເຕັມຕົວອັກສອນ 4-16 ຕົວອັກສອນ !!!", "ຜົນການທົດສອບ", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            txtPassword.Focus()
            txtPassword.SelectAll()
            Exit Sub
        End If

        If txtPasswordAgain.Text.Length < 4 Then
            MessageBox.Show("ລະຫັດ Password ຕ້ອງມີຈຳນວນຕົວອັກສອນຈຳນວນເຕັມ 4-16 ອັກສອນ !!!", "ຜົນການທົດສອບ", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            txtPasswordAgain.Focus()
            txtPasswordAgain.SelectAll()
            Exit Sub
        End If

        If txtUserName.Text.Trim() = txtPassword.Text.Trim() Then
            MessageBox.Show("UserName ກັບລະຫັດ Password ຫ້າມເໝືອນກັນ !!!", "ຜົນການທົດສອບ", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            txtPassword.Focus()
            txtPassword.SelectAll()
            Exit Sub
        End If

        If txtPassword.Text.Trim() <> txtPasswordAgain.Text.Trim() Then
            MessageBox.Show("ທ່ານປ້ອນລະຫັດ Password ບໍ່ເໝືອນກັນ ກະລຸນາປ້ອນ !!!", "ຜົນການທົດສອບ", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            txtPasswordAgain.Focus()
            txtPasswordAgain.SelectAll()
            Exit Sub
        End If

        If MessageBox.Show("ທ່ານຕ້ອງການເພີ້ມ UserName ໃໝ່ແມ່ນຫລືບໍ່?", "ຄຳຢືນຢັ້ນ", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
            tr = Conn.BeginTransaction()

            Dim CurrentIV As Byte() = New Byte() {51, 52, 53, 54, 55, 56, 57, 58}
            Dim CurrentKey As Byte() = {}

            If txtUserName.Text.Length = 8 Then
                CurrentKey = Encoding.ASCII.GetBytes(txtUserName.Text)
            ElseIf txtUserName.Text.Length > 8 Then
                CurrentKey = Encoding.ASCII.GetBytes(txtUserName.Text.Substring(0, 8))
            Else
                Dim AddString As String = txtUserName.Text.Substring(0, 1)
                Dim TotalLoop As Integer = 8 - CInt(txtUserName.Text.Length)
                Dim tmpKey As String = txtUserName.Text

                Dim i As Integer
                For i = 1 To TotalLoop
                    tmpKey = tmpKey & AddString
                Next

                CurrentKey = Encoding.ASCII.GetBytes(tmpKey)
            End If

            Dim desCrypt As DESCryptoServiceProvider
            desCrypt = New DESCryptoServiceProvider
            With desCrypt
                .IV = CurrentIV
                .Key = CurrentKey
            End With

            Dim ms As MemoryStream
            ms = New MemoryStream
            ms.Position = 0

            Dim cs As CryptoStream
            cs = New CryptoStream(ms, desCrypt.CreateEncryptor, CryptoStreamMode.Write)
            Dim arrByte As Byte() = Encoding.ASCII.GetBytes(txtPassword.Text)
            cs.Write(arrByte, 0, arrByte.Length)
            cs.FlushFinalBlock()
            cs.Close()

            Dim PwdWithEncrypt As String
            PwdWithEncrypt = Convert.ToBase64String(ms.ToArray())

            Try
                sb = New StringBuilder()
                sb.Append("INSERT INTO UserName (UserName,Password,Auth,IsNormal)")
                sb.Append(" VALUES (@UserName,@Password,@Auth,@IsNormal)")
                Dim sqlAdd As String
                sqlAdd = sb.ToString()

                com = New SqlCommand()
                With com
                    .CommandText = sqlAdd
                    .CommandType = CommandType.Text
                    .Connection = Conn
                    .Transaction = tr
                    .Parameters.Clear()
                    .Parameters.Add("@UserName", SqlDbType.NVarChar).Value = txtUserName.Text.Trim()
                    .Parameters.Add("@Password", SqlDbType.NVarChar).Value = PwdWithEncrypt
                    .Parameters.Add("@Auth", SqlDbType.NVarChar).Value = ""
                    .Parameters.Add("@IsNormal", SqlDbType.NVarChar).Value = "1"
                    .ExecuteNonQuery()
                End With
                tr.Commit()
                ShowAllUserName()

                MessageBox.Show("ເພີ້ມ UserNameໃໝ່ ຮຽບຮ້ອຍແລ້ວว !!!", "ຜົນການທຳງານ", MessageBoxButtons.OK, MessageBoxIcon.Information)
                ClearAllData()
            Catch
                MessageBox.Show("ທ່ານປ້ອນ UserName ຊ້ຳກັບຂອງເດີ້ມທີ່ມີຢູ່ !!!", "ຜົນການທົດສອບ", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
                tr.Rollback()
            End Try
        End If
        txtUserName.Focus()
    End Sub

    Private Sub cmdClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdClear.Click
        ClearAllData()
    End Sub

    Private Sub ClearAllData()
        txtUserName.Text = ""
        txtPassword.Text = ""
        txtPasswordAgain.Text = ""
    End Sub

    Private Sub cmdEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdEdit.Click
        If txtEditUserName.Text.Trim() = "" Then
            MessageBox.Show("ກະລຸນາປ້ອນ UserName ກ່ອນ !!!", "ຜົນການທົດສອບ", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            txtEditUserName.Focus()
            Exit Sub
        End If

        If MessageBox.Show("ທ່າຕ້ອງການແກ້ໄຂຖານະ UserName ແມ່ນຫລືບໍ່?", "ຄຳຢືນຢັ້ນ", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
            tr = Conn.BeginTransaction()

            Try
                sb = New StringBuilder()
                sb.Append("UPDATE UserName")
                sb.Append(" SET IsNormal=@IsNormal")
                sb.Append(" WHERE (UserName=@UserName)")
                Dim sqlEdit As String
                sqlEdit = sb.ToString()

                com = New SqlCommand()
                With com
                    .CommandText = sqlEdit
                    .CommandType = CommandType.Text
                    .Connection = Conn
                    .Transaction = tr
                    .Parameters.Clear()
                    If chkStatus.Checked = True Then
                        .Parameters.Add("@IsNormal", SqlDbType.NVarChar).Value = "0"
                    Else
                        .Parameters.Add("@IsNormal", SqlDbType.NVarChar).Value = "1"
                    End If
                    .Parameters.Add("@UserName", SqlDbType.NVarChar).Value = txtEditUserName.Text.Trim()

                    Dim result As Integer
                    result = .ExecuteNonQuery()
                    If result = 0 Then
                        tr.Rollback()
                        MessageBox.Show("UserName ທີ່ທ່ານປ້ອນ ບໍ່ຖຶກຕ້ອງ !!!", "ຜົນການທຳງານ", MessageBoxButtons.OK, MessageBoxIcon.Information)
                        txtEditUserName.Focus()
                        txtEditUserName.SelectAll()
                    Else
                        tr.Commit()
                        txtEditUserName.Text = ""
                        chkStatus.Checked = False
                        MessageBox.Show("ແກ້ໄຂ້ສະຖານະ UserName ຮຽບຮ້ອຍແລ້ວ !!!", "ຜົນການທຳງານ", MessageBoxButtons.OK, MessageBoxIcon.Information)
                    End If
                End With
            Catch ex As Exception
                tr.Rollback()
                MessageBox.Show("ເກີດຂໍ້ຜິດພາດ ເນື່ອງຈາກ " & ex.Message, "ຜົນການທົດສອບ", MessageBoxButtons.OK, MessageBoxIcon.Information)
            End Try
        End If
        txtEditUserName.Focus()
    End Sub

    Private Sub cmdSelectAll_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSelectAll.Click
        SelectAll()
    End Sub

    Private Sub cmdClearAll_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdClearAll.Click
        ClearAll()
    End Sub

    Private Sub SelectAll()
        chkPO.Checked = True
        chkProvince.Checked = True
        chkBranch.Checked = True
        chkSupplier.Checked = True
        chkBankList.Checked = True
        Chknumber.Checked = True
        Chkemployee.Checked = True

        chkAdmitMember.Checked = True
        chkMemberContinue.Checked = True
        chkEditCustomer.Checked = True
        chkPrivilege.Checked = True


        chkPromotion.Checked = True
        chkPaidSalary.Checked = True

        chkBonusRate.Checked = True
        chkPaidBonus.Checked = True

        chkHumanDetail.Checked = True
    End Sub

    Private Sub ClearAll()
        chkPO.Checked = False
        chkProvince.Checked = False
        chkBranch.Checked = False
        chkSupplier.Checked = False
        chkBankList.Checked = False
        Chknumber.Checked = False
        Chkemployee.Checked = False

        chkAdmitMember.Checked = False
        chkMemberContinue.Checked = False
        chkEditCustomer.Checked = False
        chkPrivilege.Checked = False
       
        ' chkPromotion.Checked = False
        chkPaidSalary.Checked = False

        chkBonusRate.Checked = False
        chkPaidBonus.Checked = False

        chkHumanDetail.Checked = False
    End Sub

    Private Sub dgvUserNameList_CellMouseUp(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles dgvUserNameList.CellMouseUp
        If e.RowIndex = -1 Then Exit Sub

        CUserName = dgvUserNameList.Rows.Item(e.RowIndex).Cells(0).Value.ToString()

        sb.Remove(0, sb.Length)
        sb.Append("SELECT Auth FROM UserName")
        sb.Append(" WHERE (UserName=@UserName)")
        Dim sqlAuth As String
        sqlAuth = sb.ToString()

        Dim CAuth As String
        With com
            .CommandText = sqlAuth
            .CommandType = CommandType.Text
            .Connection = Conn
            .Parameters.Clear()
            .Parameters.Add("@UserName", SqlDbType.NVarChar).Value = CUserName
            dr = .ExecuteReader()
            If dr.HasRows Then
                With dr
                    .Read()
                    CAuth = .GetString(.GetOrdinal("Auth"))
                End With
            Else
                CAuth = ""
                ClearAll()
            End If
        End With
        dr.Close()

        If CAuth = "" Then Exit Sub

        Dim PO As String = "F"
        Dim Province As String = "F"
        Dim Branch As String = "F"
        Dim Supplier As String = "F"
        Dim BankList As String = "F"
        Dim number As String = "F"
        Dim employee As String = "F"

        Dim AdmitMember As String = "F"
        Dim MemberContinue As String = "F"
        Dim EditCustomer As String = "F"
        Dim Privilege As String = "F"

        '  Dim Promotion As String = "F"
        Dim PaidSalary As String = "F"
        Dim BonusRate As String = "F"
        Dim PaidBonus As String = "F"
        Dim HumanDetail As String = "F"

        PO = CAuth.Substring(0, 1)
        Province = CAuth.Substring(1, 1)
        Branch = CAuth.Substring(2, 1)
        Supplier = CAuth.Substring(3, 1)
        BankList = CAuth.Substring(4, 1)
        number = CAuth.Substring(5, 1)
        employee = CAuth.Substring(6, 1)

        AdmitMember = CAuth.Substring(7, 1)
        MemberContinue = CAuth.Substring(8, 1)
        EditCustomer = CAuth.Substring(9, 1)
        Privilege = CAuth.Substring(10, 1)
        PaidSalary = CAuth.Substring(11, 1)
        BonusRate = CAuth.Substring(12, 1)
        PaidBonus = CAuth.Substring(13, 1)
        HumanDetail = CAuth.Substring(14, 1)

        If PO = "T" Then
            chkPO.Checked = True
        Else
            chkPO.Checked = False
        End If

        If Province = "T" Then
            chkProvince.Checked = True
        Else
            chkProvince.Checked = False
        End If

        If Branch = "T" Then
            chkBranch.Checked = True
        Else
            chkBranch.Checked = False
        End If

        If Supplier = "T" Then
            chkSupplier.Checked = True
        Else
            chkSupplier.Checked = False
        End If

        If BankList = "T" Then
            chkBankList.Checked = True
        Else
            chkBankList.Checked = False
        End If

        If number = "T" Then
            Chknumber.Checked = True
        Else
            Chknumber.Checked = False
        End If

        If employee = "T" Then
            Chkemployee.Checked = True
        Else
            Chkemployee.Checked = False
        End If

        If AdmitMember = "T" Then
            chkAdmitMember.Checked = True
        Else
            chkAdmitMember.Checked = False
        End If

        If MemberContinue = "T" Then
            chkMemberContinue.Checked = True
        Else
            chkMemberContinue.Checked = False
        End If

        If EditCustomer = "T" Then
            chkEditCustomer.Checked = True
        Else
            chkEditCustomer.Checked = False
        End If

        If Privilege = "T" Then
            chkPrivilege.Checked = True
        Else
            chkPrivilege.Checked = False
        End If

        If PaidSalary = "T" Then
            chkPaidSalary.Checked = True
        Else
            chkPaidSalary.Checked = False
        End If

        If BonusRate = "T" Then
            chkBonusRate.Checked = True
        Else
            chkBonusRate.Checked = False
        End If

        If PaidBonus = "T" Then
            chkPaidBonus.Checked = True
        Else
            chkPaidBonus.Checked = False
        End If

        If HumanDetail = "T" Then
            chkHumanDetail.Checked = True
        Else
            chkHumanDetail.Checked = False
        End If
    End Sub

    Private Sub cmdAdjust_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdAdjust.Click
        If CUserName = "" Then
            MessageBox.Show("ກະລຸນາຄິກເລືອກ UserName ກ່ອນ !!!", "ຜົນການທົດສອບ", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Exit Sub
        End If

        Dim PO As String = "F"
        Dim Province As String = "F"
        Dim Branch As String = "F"
        Dim Supplier As String = "F"
        Dim BankList As String = "F"
        Dim number As String = "F"
        Dim employee As String = "F"
        Dim AdmitMember As String = "F"
        Dim MemberContinue As String = "F"

        Dim EditCustomer As String = "F"
        Dim Privilege As String = "F"
        Dim PaidSalary As String = "F"
        Dim BonusRate As String = "F"
        Dim PaidBonus As String = "F"
        Dim HumanDetail As String = "F"

        If chkPO.Checked = True Then PO = "T"
        If chkProvince.Checked = True Then Province = "T"
        If chkBranch.Checked = True Then Branch = "T"
        If chkSupplier.Checked = True Then Supplier = "T"
        If chkBankList.Checked = True Then BankList = "T"
        If Chknumber.Checked = True Then number = "T"
        If Chkemployee.Checked = True Then employee = "T"

        If chkAdmitMember.Checked = True Then AdmitMember = "T"
        If chkMemberContinue.Checked = True Then MemberContinue = "T"
        If chkEditCustomer.Checked = True Then EditCustomer = "T"
        If chkPrivilege.Checked = True Then Privilege = "T"
        If chkPaidSalary.Checked = True Then PaidSalary = "T"
        If chkBonusRate.Checked = True Then BonusRate = "T"
        If chkPaidBonus.Checked = True Then PaidBonus = "T"
        If chkHumanDetail.Checked = True Then HumanDetail = "T"

        Dim FinalAuth As String
        FinalAuth = PO & Province & Branch & Supplier & BankList & _
                number & employee & AdmitMember & MemberContinue & _
                    EditCustomer & Privilege & PaidSalary & BonusRate & PaidBonus & HumanDetail

        If MessageBox.Show("ທ່ານຕ້ອງການແກ້ໄຂສິດທິ ແມ່ນຫລືບໍ່?", "ຄຳຢືນຢັ້ນ", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
            tr = Conn.BeginTransaction()

            Try
                sb.Remove(0, sb.Length)
                sb.Append("UPDATE UserName")
                sb.Append(" SET Auth=@Auth")
                sb.Append(" WHERE (UserName=@UserName)")
                Dim sqlAdjust As String
                sqlAdjust = sb.ToString()

                With com
                    .CommandText = sqlAdjust
                    .CommandType = CommandType.Text
                    .Connection = Conn
                    .Transaction = tr
                    .Parameters.Clear()
                    .Parameters.Add("@Auth", SqlDbType.NVarChar).Value = FinalAuth
                    .Parameters.Add("@UserName", SqlDbType.NVarChar).Value = CUserName
                    .ExecuteNonQuery()
                End With
                tr.Commit()

                CUserName = ""
                ClearAll()
                MessageBox.Show("ແກ້ໄຂສິດທິ ຮຽບຮ້ອຍແລ້ວ !!!", "ຜົນການທຳງານ", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Catch ex As Exception
                tr.Rollback()
                MessageBox.Show("ເກີດຂໍ້ຜິດພາດ ເນື່ອງຈາກ " & ex.Message, "ຜົນການທົດສອບ", MessageBoxButtons.OK, MessageBoxIcon.Information)
            End Try
        End If
    End Sub

    Protected Overrides Sub OnPaint(ByVal e As System.Windows.Forms.PaintEventArgs)
        Dim lgb As New LinearGradientBrush(ClientRectangle, Color.Yellow, Color.Orange, LinearGradientMode.Vertical)
        e.Graphics.FillRectangle(lgb, ClientRectangle)

        lgb.Dispose()
    End Sub

   
End Class