Du lieu co ban nguoi dung

Thong tin co ban

Truong phan hoi

Truong ERP	Truong MES	Mo ta
employee_no	MV001	Ma nhan vien (khoa chinh)
employee_name	MV002	Ten nhan vien
department_no	MV004	Ma phong ban
email	MV020	Email
resignation_date	MV022	Ngay nghi viec
tran_status	TRAN_STATUS	Trang thai giao dich

Vi du phan hoi

{
  "user_data": [
    {
      "employee_no": "EMP001",
      "employee_name": "Nguyen Van A",
      "department_no": "DEPT01",
      "email": "emp001@company.com",
      "resignation_date": "",
      "tran_status": "AU"
    }
  ]
}

SQL cap nhat co so du lieu

Xac dinh them moi hoac cap nhat

SELECT COUNT(1) FROM ERP_CMSMV WHERE MV001 = N'{employee_no}'
-- count > 0 → UPDATE, count = 0 → INSERT

INSERT (Them moi nhan vien)

INSERT INTO ERP_CMSMV (MV001, MV002, MV004, MV020, MV022)
VALUES (
    :userId,
    :userName,
    '',             -- Phong ban mac dinh rong
    '',             -- Email mac dinh rong
    ''              -- Ngay nghi viec mac dinh rong
)

UPDATE (Cap nhat nhan vien)

UPDATE ERP_CMSMV SET
    MV002 = :userName
WHERE MV001 = :userId

SFT_PRINCIPAL (Lien dong tai khoan he thong)

-- Them moi tai khoan he thong
INSERT INTO {EMESSYS}..SFT_PRINCIPAL (
    AUTH_ID, AUTH_NAME, AUTH_PASSWORD, USEDSTATE,
    AUTH_DESCRIPTION, Creator, CreateDate, PHONE, MAIL, WECHAT
) VALUES (
    :userId, :userName, :encryptPassWord, :status,
    :description, 'ESB', GETDATE(), :phone, :mail, :weChat
)

-- Cap nhat tai khoan he thong
UPDATE {EMESSYS}..SFT_PRINCIPAL SET
    AUTH_NAME = :userName,
    AUTH_PASSWORD = :encryptPassWord,
    USEDSTATE = :status,
    AUTH_DESCRIPTION = :description,
    PHONE = :phone,
    MAIL = :mail,
    WECHAT = :weChat
WHERE AUTH_ID = :userId