Imports System.Runtime.InteropServices
Imports System.Text.Json
Imports System.Xml

Module SQL_登入與系統權限
    Friend Sub SQL_版本號()
        ConnOpen_SQL() : SQL1 = "SELECT 系統認證版本號, 系統鎖定 FROM 版本號管理" : CmdSet_For_dr_SQL()
    End Sub
    Friend Sub SQL_登入驗證(帳號 As String)
        ConnOpen_SQL()
        SQL1 = "SELECT 姓名, 部門, CC01, CC02, CC03, CC04, CC05, CC06, CC07, CC08, CC09, CC10, CC11 
                FROM 使用者權限管理表 WHERE 帳號 LIKE N'" & 帳號 & "'"
        CmdSet_For_dr_SQL()
    End Sub
    Friend Sub SQL_OD人員表()
        ConnOpen()
        SQL1 = "SELECT   public.hr_employee.id AS 項, public.hr_department.name AS 部門, public.hr_employee.name AS 姓名, public.res_users.login AS 帳號, 
                         public.res_users.create_date AS 建立日期, public.res_users.write_date AS 登入日期
                FROM     public.hr_employee LEFT OUTER JOIN
                         public.res_users ON public.hr_employee.user_id = public.res_users.id  LEFT OUTER JOIN 
                         public.hr_department ON public.hr_employee.department_id = public.hr_department.id
                WHERE    public.hr_employee.active = true AND public.hr_employee.user_id IS NOT NULL
                ORDER BY public.hr_department.id, public.res_users.write_date"
        CmdSet_For_DGV()
    End Sub
    Friend Sub SQL_外掛使用者()
        ConnOpen_SQL()
        SQL1 = "SELECT  部門, 姓名, 帳號, CC01, CC02, CC03, CC04, CC05, CC06, CC07, CC08, CC09, CC10, CC11
                FROM    使用者權限管理表 ORDER BY 部門"
        CmdSet_For_DGV_SQL()
    End Sub
    Friend Sub SQL_可用外掛人員新增(部門 As String, 姓名 As String, 帳號 As String, CC01 As String, CC02 As String, CC03 As String, CC04 As String, CC05 As String,
                                    CC06 As String, CC07 As String, CC08 As String, CC09 As String, CC10 As String, CC11 As String)
        ConnOpen_SQL()
        SQL1 = "INSERT INTO 使用者權限管理表  (部門, 姓名, 帳號, CC01, CC02, CC03, CC04, CC05, CC06, CC07, CC08, CC09, CC10, CC11)
                VALUES (N'" & 部門 & "', N'" & 姓名 & "', N'" & 帳號 & "', N'" & CC01 & "', N'" & CC02 & "', N'" & CC03 & "', N'" & CC04 & "', N'" & CC05 & "', N'" & CC06 & "', 
                        N'" & CC07 & "', N'" & CC08 & "', N'" & CC09 & "', N'" & CC10 & "', N'" & CC11 & "')"
        CmdSet_For_dr_SQL()
    End Sub
    Friend Sub SQL_可用外掛人員修改(部門 As String, 姓名 As String, 帳號 As String, CC01 As String, CC02 As String, CC03 As String, CC04 As String, CC05 As String,
                                    CC06 As String, CC07 As String, CC08 As String, CC09 As String, CC10 As String, CC11 As String)
        ConnOpen_SQL()
        SQL1 = "UPDATE 使用者權限管理表 SET 部門 = N'" & 部門 & "', 姓名 = N'" & 姓名 & "', CC01 = N'" & CC01 & "', CC02 = N'" & CC02 & "', CC03 = N'" & CC03 & "', 
                                            CC04 = N'" & CC04 & "', CC05 = N'" & CC05 & "', CC06 = N'" & CC06 & "', CC07 = N'" & CC07 & "', CC08 = N'" & CC08 & "', 
                                            CC09 = N'" & CC09 & "', CC10 = N'" & CC10 & "', CC11 = N'" & CC11 & "'  WHERE 帳號 LIKE N'" & 帳號 & "'"
        CmdSet_For_dr_SQL()
    End Sub
    Friend Sub SQL_可用外掛人員刪除(帳號 As String)
        ConnOpen_SQL() : SQL1 = "DELETE 使用者權限管理表  WHERE 帳號 LIKE N'" & 帳號 & "'" : CmdSet_For_dr_SQL()
    End Sub
    Friend Sub SQL_電腦綁定(PA2 As String, PA3 As String, PA4 As String)
        ConnOpen_SQL()
        SQL1 = "SELECT MAC碼 FROM 系統綁定電腦資訊 WHERE 硬碟ID LIKE N'" & PA2 & "' AND 硬碟序列 LIKE N'" & PA3 & "' AND 磁盘型態 LIKE N'" & PA4 & "'"
        CmdSet_For_dr_SQL()
    End Sub
    Friend Sub SQL_電腦綁定_寫入(MAC碼 As String, 硬碟ID As String, 硬碟序列 As String, 磁盘型態 As String)
        ConnOpen_SQL()
        SQL1 = "INSERT INTO 系統綁定電腦資訊 (MAC碼, 硬碟ID, 硬碟序列, 磁盘型態) " &
                  "VALUES (N'" & MAC碼 & "',N'" & 硬碟ID & "',N'" & 硬碟序列 & "',N'" & 磁盘型態 & "')"
        CmdSet_For_dr_SQL()
    End Sub
End Module