Cruddata Approval | Smartchart开发手册

Introduction #

SmartChart CRUD supports complete workflow approval capabilities, including approval flow definitions (multi-node process configuration) and data change approval (insert/update/delete requires approval before taking effect). Approval flows are implemented through three system tables (flowinfo, flowinstances, flowtasks), while data approval uses the modify_request table to record changes.

Approval Type	Description
Approval Flow	Multi-node process definition for work orders, contracts, etc.
Data Change Approval	Insert/update data enters approval queue, takes effect only after approval

Approval Flow #

MySQL Table Creation #

drop table if exists oa_flowinfo;
CREATE TABLE oa_flowinfo(
    id int PRIMARY KEY auto_increment,
    tid smallint default 0 comment 'Tenant ID',
    uuid varchar(20),
    name varchar(50),
    description varchar(200),
    category varchar(50),
    flow TEXT,
    status varchar(20),
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    updater varchar(50),
    UNIQUE uc(tid,name)
) comment 'Approval Flow Definition';

drop table if exists oa_flowinstances;
CREATE TABLE oa_flowinstances(
    id int PRIMARY KEY auto_increment,
    tid smallint default 0 comment 'Tenant ID',
    uuid varchar(50),
    title varchar(100),
    remark varchar(500),
    formId varchar(50),
    variables varchar(500),
    definitionId varchar(50),
    applicant varchar(50),
    status varchar(50),
    currentNodeId varchar(50),
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    updater varchar(50)
) comment 'Approval Instance';

drop table if exists oa_flowtasks;
CREATE TABLE oa_flowtasks(
    id int PRIMARY KEY auto_increment,
    tid smallint default 0 comment 'Tenant ID',
    uuid varchar(50),
    instanceId varchar(50),
    nodeId varchar(50),
    nodeName varchar(50),
    assignee varchar(50),
    status varchar(50),
    instructions varchar(200),
    variables varchar(500),
    comment varchar(100),
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    updater varchar(50)
) comment 'Approval Task';

SQLite Table Creation #

DROP TABLE IF EXISTS oa_flowinfo;
CREATE TABLE oa_flowinfo(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    tid INTEGER DEFAULT 0,
    uuid TEXT, name TEXT, description TEXT, category TEXT,
    flow TEXT, status TEXT,
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    updater TEXT,
    UNIQUE(tid, name)
);
-- Similar for oa_flowinstances and oa_flowtasks

Import Approval Flow Page #

Available in Professional edition — contact support.

Data Change Approval #

Configuration method is the same for insert, update, approval, and import datasets — see Data Update
Create datasets and specify the corresponding dataset IDs to enable
For approval DS, create the approval table first, then define the write configuration:

create table modify_request(
    id INT NOT NULL AUTO_INCREMENT,
    tablename VARCHAR(50) NOT NULL,
    codename VARCHAR(50) NOT NULL,
    code VARCHAR(50) NOT NULL,
    columnname varchar(50),
    oldvalue varchar(100),
    newvalue varchar(255),
    requester varchar(50),
    checker varchar(50),
    flag int default 0,
    request_remark varchar(200),
    check_remark varchar(200),
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
) comment 'Change Log';

dataset={
    "table": "modify_request",
    "sFields": [],
    "id": "id",
    "fDict": {},
    "autoDict": {"tablename": "table_name", "codename": "pk_name", "requester": "$username"}
}

Two approval modes: open a separate window to select fields for modification (enable approval), or edits don’t take effect and flow to approval (enable approval editing)
The approval interface is also generated via CRUD, using two action buttons:

import json
action = '$action'
updatelist = "$updatelist"
sql = ''
if action == 'check':
    contents = ds_sql('datasource', f'select tablename,columnname,newvalue,codename,code from modify_request where id in ({updatelist})')
    sql = []
    for content in contents[1:]:
        if content[1]:
            sql.append(f"update {content[0]} set {content[1]}='{content[2]}' where {content[3]}='{content[4]}'")
        else:
            changeList = []
            changeDict = json.loads(content[2])
            for k, v in changeDict.items():
                changeList.append(f"{k}='{v}'")
            changeList = ','.join(changeList)
            if changeList:
                sql.append(f"update {content[0]} set {changeList} where {content[3]}='{content[4]}'")
    sql.append(f"update modify_request set flag=2, checker='$username' where id in ({updatelist})")
    sql = ';'.join(sql)
elif action == 'reject':
    sql = f"update modify_request set flag=1, checker='$username' where id in ({updatelist})"
if sql:
    ds = ds_sql('datasource', sql)