VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
  Persistable = 0  'NotPersistable
  DataBindingBehavior = 0  'vbNone
  DataSourceBehavior  = 0  'vbNone
  MTSTransactionMode  = 0  'NotAnMTSObject
END
Attribute VB_Name = "Staff"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
'1 Staff_Id  Int 員工編號
'2 Staff_Name  Varchar 50  姓名
'3 Photo Image 照片
'4 Sex Char 2  性別
'5 Nationality Varchar 40  民族
'6 Birth Varchar 20  生日
'7 Political_Party Varchar 40  政治面貌
'8 Family_Place  Varchar 60  籍貫
'9 Id_Card Varchar 20  身份證號
'10  WorkID Varchar 20  員工ID
'11  Home_phone  Varchar 30  家庭電話
'12  Residence Varchar 100 居住地址
'13  Postcode  Varchar 10  郵政編碼
'14  InDate  Varchar 20  工作日期
'15  Depart_Id  Smallint  所在部門編號
'16  Title Varchar 20  職務
'17  Memo  Varchar 200 備注
'18  Fillin_Time Varchar 20  填表時間（日期+時間）

Public Staff_Id As Long
Public Staff_Name As String
Public Sex As String
Public Nationality As String
Public Birth As String
Public Political_Party As String
Public Family_Place As String
Public Id_Card As String
Public WorkID As String
Public Home_phone As String
Public Residence As String
Public Postcode As String
Public InDate As String
Public Depart_Id As Integer
Public Title As String
Public Memo As String
Public Fillin_Time As String

Public Sub Init()
  Staff_Id = 0
  Staff_Name = ""
  Sex = ""
  Nationality = ""
  Birth = ""
  Political_Party = ""
  Family_Place = ""
  Id_Card = ""
  WorkID = ""
  Home_phone = ""
  Residence = ""
  Postcode = ""
  InDate = ""
  Depart_Id = -1
  Title = ""
  Memo = ""
  Fillin_Time = ""
End Sub

'刪除數據
Public Sub Delete(ByVal TmpStaffId As Long)
  DB_Connect
  
  SqlStmt = "Delete FROM Staff WHERE Staff_Id=" + Trim(Str(TmpStaffId))
  OdbcExt (SqlStmt)
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  
  DB_Disconnect
   
  MyChange.DeleteByStaff (TmpStaffId)
  
End Sub

Public Function GetName(ByVal TmpStaffId As Long) As String
  If TmpStaffId <= 0 Then
    GetName = ""
    Exit Function
  End If
  
  DB_Connect
  
  SqlStmt = "SELECT Staff_Name FROM Staff WHERE Staff_Id=" + Trim(Str(TmpStaffId))
  OdbcExt (SqlStmt)
  If SQLFetch(Hstmt) = SQL_NO_DATA_FOUND Then
    GetName = ""
    Exit Function
  Else
    ColVal = String(100, 0)
    Rc = SQLGetData(Hstmt, 1, 1, ColVal, Lench(ColVal), pcblench)
    GetName = TrimStr(ColVal)
  End If
    
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  DB_Disconnect
End Function

Public Function GetInfo(ByVal TmpStaffId As Long) As Boolean
  If TmpStaffId <= 0 Then
    Init
    GetInfo = False
    Exit Function
  End If
  
  Staff_Id = TmpStaffId
  
  DB_Connect
  
  SqlStmt = "SELECT * FROM Staff WHERE Staff_Id=" + Trim(Str(TmpStaffId))
  OdbcExt (SqlStmt)
  If SQLFetch(Hstmt) = SQL_NO_DATA_FOUND Then
    GetInfo = False
    Exit Function
  Else
    ColVal = String(400, 0)
    Rc = SQLGetData(Hstmt, 2, 1, ColVal, Lench(ColVal), pcblench)
    Staff_Name = TrimStr(ColVal)

    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 4, 1, ColVal, Lench(ColVal), pcblench)
    Sex = TrimStr(ColVal)
    
    ColVal = String(400, 0)
    Rc = SQLGetData(Hstmt, 5, 1, ColVal, Lench(ColVal), pcblench)
    Nationality = TrimStr(ColVal)

    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 6, 1, ColVal, Lench(ColVal), pcblench)
    Birth = TrimStr(ColVal)
    
    ColVal = String(400, 0)
    Rc = SQLGetData(Hstmt, 7, 1, ColVal, Lench(ColVal), pcblench)
    Political_Party = TrimStr(ColVal)
    
    ColVal = String(400, 0)
    Rc = SQLGetData(Hstmt, 8, 1, ColVal, Lench(ColVal), pcblench)
    Family_Place = TrimStr(ColVal)
    
    ColVal = String(400, 0)
    Rc = SQLGetData(Hstmt, 9, 1, ColVal, Lench(ColVal), pcblench)
    Id_Card = TrimStr(ColVal)
    
    ColVal = String(400, 0)
    Rc = SQLGetData(Hstmt, 10, 1, ColVal, Lench(ColVal), pcblench)
    WorkID = TrimStr(ColVal)
    
    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 11, 1, ColVal, Lench(ColVal), pcblench)
    Home_phone = TrimStr(ColVal)
    
    ColVal = String(400, 0)
    Rc = SQLGetData(Hstmt, 12, 1, ColVal, Lench(ColVal), pcblench)
    Residence = TrimStr(ColVal)

    ColVal = String(4000, 0)
    Rc = SQLGetData(Hstmt, 13, 1, ColVal, Lench(ColVal), pcblench)
    Postcode = TrimStr(ColVal)

    ColVal = String(4000, 0)
    Rc = SQLGetData(Hstmt, 14, 1, ColVal, Lench(ColVal), pcblench)
    InDate = TrimStr(ColVal)

    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 15, 1, ColVal, Lench(ColVal), pcblench)
    Depart_Id = Val(ColVal)

    ColVal = String(400, 0)
    Rc = SQLGetData(Hstmt, 16, 1, ColVal, Lench(ColVal), pcblench)
    Title = TrimStr(ColVal)

    ColVal = String(500, 0)
    Rc = SQLGetData(Hstmt, 17, 1, ColVal, Lench(ColVal), pcblench)
    Memo = TrimStr(ColVal)

    ColVal = String(400, 0)
    Rc = SQLGetData(Hstmt, 18, 1, ColVal, Lench(ColVal), pcblench)
    Fillin_Time = TrimStr(ColVal)

  End If
    
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  
  GetInfo = True
  DB_Disconnect
End Function

Public Function GetNewId() As Long
  Dim TmpId As Long
  
  DB_Connect
    
  SqlStmt = "SELECT Staff_Id FROM Staff ORDER BY Staff_Id"
  OdbcExt (SqlStmt)
  i = 1
  Do While SQLFetch(Hstmt) <> SQL_NO_DATA_FOUND
    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 1, 1, ColVal, Len(ColVal), pcblen)
    TmpId = Val(ColVal)
    If TmpId = i Then
      i = i + 1
    Else
      GetNewId = i
      Rc = SQLFreeStmt(Hstmt, SQL_DROP)
      DB_Disconnect
      Exit Function
    End If
  Loop
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  
  DB_Disconnect
  
  GetNewId = i
End Function

Public Sub Insert()
  Staff_Id = GetNewId
  
  DB_Connect
  
  SqlStmt = "INSERT INTO Staff (Staff_Id, Staff_Name, Sex, Nationality," _
     + " Birth, Political_Party, Family_Place, Id_Card, WorkID, Home_Phone," _
     + " Residence, Postcode, InDate, Depart_Id, Title, Memo, Fillin_Time)" _
     + " Values(" + Trim(Str(Staff_Id)) + ",'" + Trim(Staff_Name) _
     + "','" + Trim(Sex) + "','" + Trim(Nationality) + "','" + Trim(Birth) _
     + "','" + Trim(Political_Party) + "','" + Trim(Family_Place) + "','" _
     + Trim(Id_Card) + "','" + Trim(WorkID) + "','" + Trim(Home_phone) _
     + "','" + Trim(Residence) + "','" + Trim(Postcode) + "','" + Trim(InDate) _
     + "'," + Trim(Str(Depart_Id)) + ",'" + Trim(Title) + "','" + Trim(Memo) _
     + "','" + Trim(Fillin_Time) + "')"
  OdbcExt (SqlStmt)
    
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  DB_Disconnect
End Sub

Public Sub Load_By_Depart(ByVal TmpDepartId As Integer)
  Dim i As Integer
  '初始化部門數組
  Erase Arr_StaffId
  ReDim Arr_StaffId(0)
  Erase Arr_StaffName
  ReDim Arr_StaffName(0)
  '連接數據庫
  DB_Connect
  '要執行的SQL語句
  SqlStmt = "SELECT Staff_Id, Staff_Name FROM Staff WHERE Depart_Id=" _
          + Trim(Str(TmpDepartId)) + " ORDER BY Staff_Id"
  '執行SQL語句
  OdbcExt (SqlStmt)
  i = 0
  '依次讀取滿足條件的員工編號讀取到數組中
  Do Until SQLFetch(Hstmt) = SQL_NO_DATA_FOUND
    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 1, 1, ColVal, Len(ColVal), pcblen)
    ReDim Preserve Arr_StaffId(i + 1)
    Arr_StaffId(i) = Val(ColVal)
    
    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 2, 1, ColVal, Len(ColVal), pcblen)
    ReDim Preserve Arr_StaffName(i + 1)
    Arr_StaffName(i) = TrimStr(ColVal)
    
    i = i + 1
  Loop
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  '斷開與數據庫的連接
  DB_Disconnect
End Sub

Public Sub Update(ByVal OriStaffId As Integer)
  DB_Connect
    
  SqlStmt = "Update Staff Set Staff_Name='" + Trim(Staff_Name) _
          + "',Sex='" + Trim(Sex) + "',Nationality='" + Trim(Nationality) _
          + "',Birth='" + Trim(Birth) + "',Political_Party='" + Trim(Political_Party) _
          + "',Family_Place='" + Trim(Family_Place) + "',Id_Card='" + Trim(Id_Card) _
          + "',WorkID='" + Trim(WorkID) + "',Home_phone='" + Trim(Home_phone) _
          + "',Residence='" + Trim(Residence) + "',Postcode='" + Trim(Postcode) _
          + "',InDate='" + Trim(InDate) + "',Depart_Id=" + Trim(Str(Depart_Id)) _
          + ",Title='" + Trim(Title) + "',Memo='" + Trim(Memo) + "',Fillin_Time='" _
          + Trim(Fillin_Time) + "' WHERE Staff_Id=" + Trim(Str(OriStaffId))
  OdbcExt (SqlStmt)
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  DB_Disconnect
End Sub

'更新部門信息
Public Sub Update_Transfer_Depart(ByVal OriStaffId As Integer)
  DB_Connect
    
  SqlStmt = "Update Staff Set Depart_Id=" + Trim(Depart_Id) _
          + " WHERE Staff_id=" + Trim(OriStaffId)
  OdbcExt (SqlStmt)
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  DB_Disconnect
End Sub

'更新職務信息
Public Sub Update_Transfer_Title(ByVal OriStaffId As Integer)
  DB_Connect
    
  SqlStmt = "Update Staff Set Title ='" + Trim(Title) _
          + "' WHERE Staff_id=" + Trim(OriStaffId)
  OdbcExt (SqlStmt)
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  DB_Disconnect
End Sub