Data Source - Get via Script
Overview
For complex data acquisition scenarios, such as obtaining data from associated systems through interfaces or after performing calculations on system data, you can use Get via Script to implement it. After enabling it, you need to set the path of the data calculation script, and the following functions need to be exported from the script
javascript
/**
* Query record details by ID; the return value is an object.
*/
export function executeQueryById(ctx) {
//TableFromScriptQueryContext
const connection = informat.jdbc.tableConnection();
const list = [];
console.log(ctx);
connection.select(
`select * from script_and_view_source_dep_data where id = ? limit 1;`,
(resultSet) => {
list.push(
handleData({
id: resultSet.getString("id"),
name: resultSet.getString("name"),
sex: resultSet.getString("sex"),
post: resultSet.getString("post"),
score: resultSet.getDouble("score"),
mobileNo: resultSet.getString("mobile_no"),
createTime: resultSet.getString("create_time"),
})
);
},
ctx.id
);
if (list.length === 0) {
informat.app.abort("Data does not exist or has been deleted");
return;
}
return list[0];
}
/**
* Query record list by conditions; the return value is an array.
*/
export function executeQueryList(ctx) {
const { pageIndex, pageSize } = ctx.query;
const connection = informat.jdbc.tableConnection();
const list = [];
let pageStart = (pageIndex - 1) * pageSize;
if (Number.isNaN(pageStart) || pageStart < 0) {
pageStart = 0;
}
connection.select(
`select * from script_and_view_source_dep_data limit ? offset ?;`,
(resultSet) => {
list.push(
handleData({
id: resultSet.getString("id"),
name: resultSet.getString("name"),
sex: resultSet.getString("sex"),
post: resultSet.getString("post"),
score: resultSet.getDouble("score"),
mobileNo: resultSet.getString("mobile_no"),
createTime: resultSet.getString("create_time"),
})
);
},
pageSize,
pageStart
);
return list;
}
/**
* Query the number of records by conditions; the return value is an integer.
If the value is less than 0, the page will render the data without pagination.
*/
export function executeQueryListCount(ctx) {
const connection = informat.jdbc.tableConnection();
let count = 0;
connection.select(`select count(1) as counts from script_and_view_source_dep_data;`, (resultSet) => {
count = resultSet.getLong("counts");
});
return count;
}
/**
* Create a record; the return value is an object.
*/
export function executeInsert(ctx) {
//TableFromScriptInsertContext
console.log("executeInsert", ctx);
return {
id: id,
};
}
/**
* Update a record; the return value is an object.
*/
export function executeUpdate(ctx) {
//TableFromScriptUpdateContext
console.log("executeUpdate", ctx);
return {
rowCount: 1,
};
}
/**
* Delete a record; the return value is an object.
*/
export function executeDelete(ctx) {
//TableFromScriptDeleteContext
console.log("executeDelete", ctx);
return {
rowCount: 1,
};
}typescript
interface OrderBy {
field: string; // Field name
order: "ASC" | "DESC"; // Sort order, ascending or descending
}
interface Filter {
field: string; // Field name
condition: string; // Condition
value: any; // Condition value
}
interface Query {
id: string; // Record ID
pageIndex: number; // Start page number, the first page is 1
pageSize: number; // Pagination size
includeFields: Array<string>; // Query field list
orderByList: Array<OrderBy>; // Sort list
childrenFieldId?: string; // Identifier of the attribute structure field, can be empty
childrenShowParent?: boolean; // Whether to include the parent node in the query results of the tree structure
childrenRootRecordId?: string; // ID of the root node in the tree structure query
filter?: Filter; // Filter condition
filterId?: string; // Composite filter ID, used to query records that meet the filter conditions
pathFilterId?: string; // Address bar filter ID
pathFilterQuery?: object; // Address bar filter condition
}
interface TableFromScriptQueryContext {
appId: string; // Application ID
tableId: string; // Data table identifier
id: string; // Record ID, used by the executeQueryById method
query: TableFromScriptQuery;
}
interface TableFromScriptInsertContext {
appId: string; // Application ID
tableId: string; // Data table identifier
rowData: object; // Record
}
interface TableFromScriptUpdateContext {
appId: string; // Application ID
tableId: string; // Data table identifier
rowData: object; // Record
updateFields: Array<string>; // List of fields to be updated
}
interface TableFromScriptDeleteContext {
appId: string; // Application ID
tableId: string; // Data table identifier
id: string; // Record ID to be deleted
}For the definitions of OrderBy and Filter, please refer to informat.table
Complete Example of Getting Data via Script
javascript
const dburl = "jdbc:postgresql://127.0.0.1:5432/db_xxx";
const dbuser = "root";
const dbpassword = "xxxxxxxx";
const driverClassName = "org.postgresql.Driver";
export const typeMapping = {
UUID: "String",
SingleText: "String",
Date: "Timestamp",
Integer: "Int",
Double: "Double",
RelationRecord: "String",
};
export function executeQueryById(ctx) {
console.log("executeQueryById ctx", ctx);
let tableName = ctx.tableId;
const connection = informat.jdbc.createConnection({
dburl,
dbuser,
dbpassword,
driverClassName,
});
const tableInfo = informat.table.getTableInfo(tableName);
let selectSql = `select * from ${tableName} where id=?`;
let bean = {};
connection.select(
selectSql,
(row) => {
bean = rowHandler(tableInfo.tableFieldList, row);
},
ctx.id
);
return bean;
}
/**
* Query record list by conditions; the return value is an array.
*/
export function executeQueryList(ctx) {
console.log("executeQueryList ctx", ctx);
let tableName = ctx.tableId;
const connection = informat.jdbc.createConnection({
dburl,
dbuser,
dbpassword,
driverClassName,
});
if (ctx.query.orderByList == null || ctx.query.orderByList.length == 0) {
// Default sort field
ctx.query.orderByList = [{ field: "create_time", order: "DESC" }];
}
const tableInfo = informat.table.getTableInfo(tableName);
console.log("tableInfo---->", tableInfo);
let args = [];
let sql = `select * from ${tableName}`;
sql = sql + getSelectSql(tableInfo, ctx, args);
console.log("select sql---->", sql, args);
const list = [];
connection.select(
sql,
(row) => {
list.push(rowHandler(tableInfo.tableFieldList, row));
},
args
);
return list;
}
/**
* Query the number of records by conditions; the return value is an integer.
If the value is less than 0, the page will render the data without pagination.
*/
export function executeQueryListCount(ctx) {
console.log("executeQueryListCount ctx", ctx);
let tableName = ctx.tableId;
const connection = informat.jdbc.createConnection({
dburl,
dbuser,
dbpassword,
driverClassName,
});
let count = 0;
connection.select(`select count(*) as count from ${tableName}`, (row) => {
count = row.getInt("count");
});
return count;
}
const excludeFieldTypeList = ["Seq"];
function rowHandler(tableField, row) {
const rs = {};
tableField.forEach((field) => {
const { type, key } = field;
if (excludeFieldTypeList.includes(type)) {
return;
}
rs[key] = row[`get${typeMapping[type]}`](`${key}`);
if (type === "RelationRecord") {
rs[`${key}_name`] = row.getString(`${key}_name`);
}
});
return rs;
}
function getField(tableInfo, fieldId) {
let field = tableInfo.tableFieldList.find((item) => item.key == fieldId);
if (field == null) {
informat.app.abort("Field does not exist" + fieldId);
}
return field;
}
function getValue(field, value) {
if (value == null) {
return null;
}
let type = field.type;
if (type == "Integer" || type == "Rate") {
return parseInt(value);
} else if (type == "Double") {
return parseFloat(value);
} else if (type == "Date" || type == "CreateTime" || type == "LastModifyTime") {
return informat.date.parseDate(value);
} else {
return value;
}
}
function getWhereSql(tableInfo, whereSql, filter, args) {
if (filter == null) {
return;
}
if (filter.conditionList != null && filter.conditionList.length > 0) {
if (filter.opt == null || filter.opt == "and") {
whereSql += `and ( 1=1 `;
} else {
whereSql += `or ( 1=2 `;
}
filter.conditionList.forEach((c) => {
if (c.fieldId != null) {
let field = getField(tableInfo, c.fieldId);
let column = field.key;
let value = getValue(field, c.value);
if (c.opt == "eq" && c.value != null) {
whereSql += `and ${column} = ? `;
args.push(value);
}
if (c.opt == "ne" && c.value != null) {
whereSql += `and ${column} != ? `;
args.push(value);
}
if (c.opt == "isnull") {
whereSql += `and ${column} is null `;
}
if (c.opt == "isnotnull") {
whereSql += `and ${column} is not null `;
}
if (c.opt == "gt" && c.value != null) {
whereSql += `and ${column} > ? `;
args.push(value);
}
if (c.opt == "ge" && c.value != null) {
whereSql += `and ${column} >= ? `;
args.push(value);
}
if (c.opt == "lt" && c.value != null) {
whereSql += `and ${column} < ? `;
args.push(value);
}
if (c.opt == "le" && c.value != null) {
whereSql += `and ${column} <= ? `;
args.push(value);
}
if (c.opt == "contains" && c.value != null) {
whereSql += `and ${column} like concat('%',?,'%') `;
args.push(value);
}
if (c.opt == "notcontains" && c.value != null) {
whereSql += `and ${column} not like concat('%',?,'%') `;
args.push(value);
}
if (c.opt == "startswith" && c.value != null) {
whereSql += `and ${column} not like concat(?,'%') `;
args.push(value);
}
if (c.opt == "endswith" && c.value != null) {
whereSql += `and ${column} not like concat('%',?) `;
args.push(value);
}
if (c.opt == "in" && c.value != null && c.value.length > 0) {
whereSql += `and ${column} in ( `;
c.value.forEach((item) => {
whereSql += `?,`;
args.push(getValue(field, item));
});
whereSql = whereSql.substr(0, whereSql.length - 1);
}
if (c.opt == "notin" && c.value != null && c.value.length > 0) {
whereSql += `and ${column} not in ( `;
c.value.forEach((item) => {
whereSql += `?,`;
args.push(getValue(field, item));
});
whereSql = whereSql.substr(0, whereSql.length - 1);
}
}
});
whereSql += `)`;
}
if (filter.children != null && filter.children.length > 0) {
filter.children.forEach((item) => {
whereSql = getWhereSql(tableInfo, whereSql, item, args);
});
}
return whereSql;
}
function getSelectSql(tableInfo, ctx, args) {
let whereSql = "where 1=1 ";
if (ctx.query.filter != null) {
whereSql = getWhereSql(tableInfo, whereSql, ctx.query.filter, args);
}
const pageSize = ctx.query.pageSize || 50;
const offset = (ctx.query.pageIndex - 1) * pageSize;
let orderBy = (ctx.query.orderByList || []).map((item) => `${item.field} ${item.type}`).join(",");
if (orderBy) {
orderBy = "order by " + orderBy;
}
const sql = ` ${whereSql} ${orderBy} limit ${pageSize} offset ${offset}`;
return sql;
}