# 功能權限架構
Last Review: 2024.07.16 by Taiwei Ciou
## ER Diagram
```mermaid
---
title: Functional Permission ER Diagram
---
erDiagram
s_user }o--|| s_usertype : ""
s_usertype ||--o{ s_usertypefunction : ""
s_usertypefunction }o--|| s_function : ""
s_user{
%% User Information
varchar(36) acctId PK "Guid"
varchar(36) usertypeId FK
}
s_usertype{
varchar(36) usertypeId PK
nvarchar(100) usertypeName
nvarchar(250) description
}
s_function{
varchar(36) functionId PK
nvarchar(100) functionName
nvarchar(250) description
int isActive "1:是/2:否; Default:1"
}
s_usertypefunction{
varchar(36) usertypefunctionId PK
varchar(36) usertypeId FK
varchar(36) functionId FK
}
```
* key值按往例使用varchar(36)
* description 使用nvarchar 以儲存中文；長度按前例設為(250)
* isActive 按往例以int儲存。
* 出於簡潔考量，上圖省略`createdBy`(varchar36, FK), `createdOn`(DateTime), `modifiedBy`(varchar36, FK), `modifiedOn`(DateTime)。
* `s_user`僅標示相關欄位。
## MSSQL DDL
Define `s_usertype`, `s_function`, and `s_usertypefunction`.
```sql=
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[s_usertype](
[usertypeId] [varchar](36) NOT NULL,
[usertypeName] [nvarchar](100) NULL,
[description] [nvarchar](250) NULL,
[createdOn] [datetime] NULL,
[createdBy] [varchar](36) NULL,
[modifiedOn] [datetime] NULL,
[modifiedBy] [varchar](36) NULL,
CONSTRAINT PK_s_usertype PRIMARY KEY CLUSTERED (usertypeId)
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[s_function](
[functionId] [varchar](36) NOT NULL,
[functionName] [nvarchar](100) NULL,
[description] [nvarchar](250) NULL,
[isActive][int] DEFAULT 1,
[createdOn] [datetime] NULL,
[createdBy] [varchar](36) NULL,
[modifiedOn] [datetime] NULL,
[modifiedBy] [varchar](36) NULL,
CONSTRAINT PK_s_function PRIMARY KEY CLUSTERED (functionId)
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[s_usertypefunction](
[usertypefunctionId] [varchar](36) NOT NULL,
[usertypeId] [varchar](36) NOT NULL,
[functionId] [varchar](36) NOT NULL,
[createdOn] [datetime] NULL,
[createdBy] [varchar](36) NULL,
[modifiedOn] [datetime] NULL,
[modifiedBy] [varchar](36) NULL,
CONSTRAINT PK_s_usertypefunction PRIMARY KEY CLUSTERED (usertpefunctionId),
CONSTRAINT FK_s_usertypefunction_usertype FOREIGN KEY (usertypeId) REFERENCES [dbo].[s_usertype](usertypeId),
CONSTRAINT FK_s_usertypefunction_function FOREIGN KEY (functionId) REFERENCES [dbo].[s_function](functionId)
) ON [PRIMARY]
GO
```
Add new columns `usertypeId` to emtity `s_user` for associating the users to function permissions.
```sql=
ALTER TABLE [dbo].[s_user]
ADD [usertypeId] [varchar](36) NULL,
CONSTRAINT FK_s_user_usertype FOREIGN KEY (usertypeId) REFERENCES [dbo].[s_usertype](usertypeId)
GO
```
## 功能設計
| | 平台管理者 | 資料維護者 - 全部 | 資料維護者 - 活動數據 | 專案管理者 |
| --- | --- | --- | --- | --- |
| 使用情境 | 管理整個平台包含使用者權限等 | 匯入並維護全部資料，包含活動數據、係數等 | 僅匯入並維護活動數據 | 管理者被視設定目標與分析同家減量方向 |
|使用者管理(User Management)|V|V|||
| 技術支援 (Technical Support) | V | V | V | V |
| 權限設定 (Data Permission & Function Permission) | V | | | |
| 專案列表 (Project List) | V | V|V |V |
| 公司/組織資料維護 (Organizational Profile) | V | V | | |
| 設定檔維護 (Reference Data) | V | V | | |
| 係數管理 (Emission Factor) | V | V | | |
| 儀表板 (Dashboard) | V | V | | V |
| 標的產品專案管理 (Project Profile) | V | V | V | V |
| 製程地圖 (Processing Map) | V | V |V | V |
| 數據匯入 (Data Import) | V | V | V | |
| 碳排計算 (Emission Reporting) | V | V | | V |
## 使用者類型-功能資料
**待更新項目**
- [ ] `s_usertypefunction`添加`isRead`,`isCreate`,`isUpdate`,`isDelete`四個欄位
### s_usertype
```sql=
INSERT INTO [dbo].[s_usertype]
([usertypeId]
,[usertypeName]
,[description]
,[createdOn]
,[createdBy]
,[modifiedOn]
,[modifiedBy])
VALUES
('Default-usertype-platformadministrat','Platform_Administrator','平台管理者:管理整個平台，包含使用者權限等。', GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default', GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default'),
('Default-usertype-datamaintainer-all-','Data_Maintainer_All','資料維護者-全部：匯入並維護全部資料，包含活動數據、係數等', GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default', GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default'),
('Default-usertype-datamaintainer-acti','Data_Maintainer_Activity_Data','資料維護者-活動數據：僅匯入並維護活動數據。', GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default', GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default'),
('Default-usertype-projectmanager-0000','Project_Manager','專案管理者:檢視碳足跡與分析洞察減量方向。', GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default', GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default')
GO
```
### s_function
```sql=
INSERT INTO [dbo].[s_function]
([functionId]
,[functionName]
,[description]
,[isActive]
,[createdOn]
,[createdBy]
,[modifiedOn]
,[modifiedBy])
VALUES
('Default-function-technicalsupport-00', 'Technical Support', NULL, 1, GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default', GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default'),
('Default-function-Datafunctionpermiss', 'Data & Function Permission', NULL, 1, GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default', GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default'),
('Default-function-projectlist-0000000', 'Project List', NULL, 1, GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default', GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default'),
('Default-function-organizationprofile', 'Organizational Profile', NULL, 1, GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default', GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default'),
('Default-function-referencedata-00000', 'Reference Data', NULL, 1, GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default', GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default'),
('Default-function-emissionfactor-0000', 'Emission Factor', NULL, 1, GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default', GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default'),
('Default-function-dashboard-000000000', 'Dashboard', NULL, 1, GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default', GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default'),
('Default-function-projectprofile-0000', 'Project profile', NULL, 1, GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default', GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default'),
('Default-function-processingmap-00000', 'Processing map', NULL, 1, GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default', GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default'),
('Default-function-dataimport-00000000', 'Data import', NULL, 1, GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default', GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default'),
('Default-function-emissionreporting-0', 'Emission Reporting', NULL, 1, GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default', GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default'),
('Default-function-usersetting-0000000', 'User Setting', NULL, 1, GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default', GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default')
```
### usertypefunction
```sql=
INSERT INTO [dbo].[s_usertypefunction]
([usertypefunctionId]
,[usertypeId]
,[functionId]
,[createdOn]
,[createdBy]
,[modifiedOn]
,[modifiedBy])
VALUES
-- Platform Administrator's function authorizations
('Default-usertypefunction-PA-00000012','Default-usertype-platformadministrat','Default-function-usersetting-0000000',GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default', GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default'),
-- Data Maintainer-All's function authorizations
('Default-usertypefunction-DM1-0000012','Default-usertype-datamaintainer-all-','Default-function-usersetting-0000000',GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default', GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Taipei Standard Time', 'Default')
```
Sign in with Wallet
Connect another wallet