Private Sub FilldgvStaff(ByVal tmpAction As Integer)
Dim tmpSQL As String = String.Empty
Dim tmpRowData(dgvStaff.ColumnCount - 1) As String
With dgvStaff
Select Case tmpAction
Case 0
tmpSQL = "select * from staff where deleted=0 order by code"
.Rows.Clear()
Case 1
tmpSQL = "* from staff where deleted=0 order by staffid desc"
tmpSQL = AdjustLastSQL(tmpSQL)
End Select
If CreateDBReader(tmpSQL, grsOpen) = True Then
While grsOpen.Read
tmpRowData(cntSFCode) = grsOpen("code").ToString
tmpRowData(cntSFAlias) = grsOpen("alias").ToString
tmpRowData(cntSFDescription) = grsOpen("description").ToString
tmpRowData(cntSFIDName) = grsOpen("idname").ToString
tmpRowData(cntSFIDNo) = grsOpen("idno").ToString
tmpRowData(cntSFTel) = grsOpen("tel").ToString
tmpRowData(cntSFMobile) = grsOpen("mobile").ToString
tmpRowData(cntSFTypeID) = grsOpen("stafftypeid").ToString
tmpRowData(cntSFCreateDate) = cuFromOADate(grsOpen("createdate"))
tmpRowData(cntSFAddress1) = grsOpen("address1").ToString
tmpRowData(cntSFAddress2) = grsOpen("address2").ToString
tmpRowData(cntSFAllowUsed) = grsOpen("allowused")
tmpRowData(cntSFSex) = grsOpen("sex").ToString
tmpRowData(cntSFCardCode) = grsOpen("cardcode").ToString
tmpRowData(cntStaffID) = grsOpen("staffid")
.Rows.Add(tmpRowData)
End While
grsOpen.Close()
End If
End With
End Sub
Private Sub SaveNewStaff()
Dim tmpTransaction As Object = Nothing
Dim tmpCommand As Object = Nothing
If CreateDBTransaction(gdbConnection, tmpTransaction) = True Then
Call CreateDBCommand(gdbConnection, tmpCommand)
Else
MsgBox("SaveNewStaff Module Error ! " & Err.Number & " # " & Err.Description, vbCritical, "System Error Message")
Exit Sub
End If
Try
tmpCommand.Transaction = tmpTransaction
tmpCommand.CommandText = "insert into staff (code,alias,description,sex,idname,idno,stafftypeid,tel,mobile,cardcode,address1,address2,passwd,createdate,allowused) " + _
"values(@code,@alias,@description,@sex,@idname,@idno,@stafftypeid,@tel,@mobile,@cardcode,@address1," + _
"@address2,@passwd,@createdate,@allowused)"
tmpCommand.Parameters.AddWithValue("@code", atxtStaff(cntSFCode).Text)
tmpCommand.Parameters.AddWithValue("@alias", atxtStaff(cntSFAlias).Text)
tmpCommand.Parameters.AddWithValue("@description", atxtStaff(cntSFDescription).Text)
tmpCommand.Parameters.AddWithValue("@passwd", Encrypt_Hex(gcntSecurityKey, "1234"))
If rbnSex1.Checked = True Then
tmpCommand.Parameters.AddWithValue("@sex", 0)
Else
tmpCommand.Parameters.AddWithValue("@sex", 1)
End If
tmpCommand.Parameters.AddWithValue("@idname", atxtStaff(cntSFIDName).Text)
tmpCommand.Parameters.AddWithValue("@idno", atxtStaff(cntSFIDNo).Text)
tmpCommand.Parameters.AddWithValue("@tel", atxtStaff(cntSFTel).Text)
tmpCommand.Parameters.AddWithValue("@mobile", atxtStaff(cntSFMobile).Text)
tmpCommand.Parameters.AddWithValue("@address1", atxtStaff(cntSFAddress1).Text)
tmpCommand.Parameters.AddWithValue("@address2", atxtStaff(cntSFAddress2).Text)
tmpCommand.Parameters.AddWithValue("@cardcode", atxtStaff(cntSFCardCode).Text)
tmpCommand.Parameters.AddWithValue("@createdate", DateTime.Now.ToOADate)
tmpCommand.Parameters.AddWithValue("@stafftypeid", CType(cboStaff0.SelectedItem, ComboBoxItem).ItemValue)
If chkStaff1.Checked = True Then
tmpCommand.Parameters.AddWithValue("@allowused", 1)
Else
tmpCommand.Parameters.AddWithValue("@allowused", 0)
End If
tmpCommand.ExecuteNonQuery()
tmpTransaction.Commit()
tmpCommand.Dispose()
Catch Ex As Exception
tmpTransaction.Rollback()
MsgBox("SaveNewStaff Module Error ! " & Err.Number & " # " & Err.Description, vbCritical, "System Error Message")
Finally
tmpTransaction.Dispose()
End Try
End Sub
Private Sub SaveEditStaff()
Dim tmpTransaction As Object = Nothing
Dim tmpCommand As Object = Nothing
If CreateDBTransaction(gdbConnection, tmpTransaction) = True Then
Call CreateDBCommand(gdbConnection, tmpCommand)
Else
MsgBox("SaveEditStaff Module Error ! " & Err.Number & " # " & Err.Description, vbCritical, "System Error Message")
Exit Sub
End If
Try
tmpCommand.Transaction = tmpTransaction
tmpCommand.CommandText = "update staff set code=@code,alias=@alias,description=@description," + _
"sex=@sex,idname=@idname,idno=@idno,stafftypeid=@stafftypeid," + _
"tel=@tel,mobile=@mobile,cardcode=@cardcode,address1=@address1," + _
"address2=@address2,createdate=@createdate,allowused=@allowused"
If chkStaff2.Checked = True Or chkStaff3.Checked = True Then
tmpCommand.CommandText = tmpCommand.CommandText + ",passwd=@passwd"
End If
tmpCommand.CommandText = tmpCommand.CommandText & " where staffid=" + dgvStaff.Rows(dgvStaff.CurrentRow.Index).Cells(cntStaffID).Value.ToString
If chkStaff2.Checked = True Then
tmpCommand.Parameters.AddWithValue("@passwd", Encrypt_Hex(gcntSecurityKey, "1234"))
ElseIf chkStaff3.Checked = True Then
tmpCommand.Parameters.AddWithValue("@passwd", "0000")
End If
tmpCommand.Parameters.AddWithValue("@code", atxtStaff(cntSFCode).Text)
tmpCommand.Parameters.AddWithValue("@alias", atxtStaff(cntSFAlias).Text)
tmpCommand.Parameters.AddWithValue("@description", atxtStaff(cntSFDescription).Text)
If rbnSex1.Checked = True Then
tmpCommand.Parameters.AddWithValue("@sex", 0)
Else
tmpCommand.Parameters.AddWithValue("@sex", 1)
End If
tmpCommand.Parameters.AddWithValue("@idname", atxtStaff(cntSFIDName).Text)
tmpCommand.Parameters.AddWithValue("@idno", atxtStaff(cntSFIDNo).Text)
tmpCommand.Parameters.AddWithValue("@tel", atxtStaff(cntSFTel).Text)
tmpCommand.Parameters.AddWithValue("@mobile", atxtStaff(cntSFMobile).Text)
tmpCommand.Parameters.AddWithValue("@address1", atxtStaff(cntSFAddress1).Text)
tmpCommand.Parameters.AddWithValue("@address2", atxtStaff(cntSFAddress2).Text)
tmpCommand.Parameters.AddWithValue("@cardcode", atxtStaff(cntSFCardCode).Text)
tmpCommand.Parameters.AddWithValue("@createdate", cuStrtoDateTimeDbl(atxtStaff(cntSFCreateDate).Text))
tmpCommand.Parameters.AddWithValue("@stafftypeid", CType(cboStaff0.SelectedItem, ComboBoxItem).ItemValue)
If chkStaff1.Checked = True Then
tmpCommand.Parameters.AddWithValue("@allowused", 1)
Else
tmpCommand.Parameters.AddWithValue("@allowused", 0)
End If
tmpCommand.ExecuteNonQuery()
tmpTransaction.Commit()
tmpCommand.Dispose()
Catch Ex As Exception
tmpTransaction.Rollback()
MsgBox("SaveEditStaff Module Error ! " & Err.Number & " # " & Err.Description, vbCritical, "System Error Message")
Finally
tmpTransaction.Dispose()
End Try
End Sub
Private Sub DeleteStaff()
Dim tmpTransaction As Object = Nothing
Dim tmpCommand As Object = Nothing
If CreateDBTransaction(gdbConnection, tmpTransaction) = True Then
Call CreateDBCommand(gdbConnection, tmpCommand)
Else
MsgBox("DeleteStaff Module Error ! " & Err.Number & " # " & Err.Description, vbCritical, "System Error Message")
Exit Sub
End If
Try
tmpCommand.Transaction = tmpTransaction
tmpCommand.CommandText = "update staff set deleted=1 where staffid=" & dgvStaff.Rows(dgvStaff.CurrentRow.Index).Cells(cntStaffID).Value.ToString
tmpCommand.ExecuteNonQuery()
tmpTransaction.Commit()
tmpCommand.Dispose()
dgvStaff.Rows.Remove(dgvStaff.CurrentRow)
If dgvStaff.RowCount = 0 Then
Call ClearStaff()
Else
dgvStaff.Focus()
ShowStaff()
End If
Catch Ex As Exception
tmpTransaction.Rollback()
MsgBox("DeleteStaff Module Error ! " & Err.Number & " # " & Err.Description, vbCritical, "System Error Message")
Finally
tmpTransaction.Dispose()
End Try
End Sub
