f89e074dd1
Governed substrate for autonomous agents: scoped identity (passports), audited actions, MCP workspace. Infra IPs and secrets redacted for public release.
302 lines
8.6 KiB
JavaScript
302 lines
8.6 KiB
JavaScript
/**
|
|
* Formula Evaluator (ADR-026)
|
|
*
|
|
* Evaluates formulas with support for:
|
|
* - Basic arithmetic (+, -, *, /, parentheses)
|
|
* - Variable substitution ($variable_name)
|
|
* - Column references ({{column_name}})
|
|
* - Aggregation functions (SUM, AVG, MIN, MAX, COUNT, etc.)
|
|
* - Built-in functions (IF, ROUND, FLOOR, CEIL, ABS, IFERROR)
|
|
*/
|
|
|
|
import { parseFormulaDependencies } from './formulaParser.js';
|
|
|
|
// ============================================================
|
|
// Aggregation Functions
|
|
// ============================================================
|
|
|
|
/**
|
|
* Evaluate aggregation function on array of values
|
|
* @param {string} functionName - SUM, AVG, MIN, MAX, COUNT, etc.
|
|
* @param {Array} values - Array of numeric values
|
|
* @returns {number} Result of aggregation
|
|
*/
|
|
export function evaluateAggregation(functionName, values) {
|
|
// Filter out non-numeric values for numeric aggregations
|
|
const numericValues = values
|
|
.filter(v => v !== null && v !== undefined && v !== '')
|
|
.map(v => typeof v === 'number' ? v : parseFloat(v))
|
|
.filter(v => !isNaN(v));
|
|
|
|
switch (functionName.toUpperCase()) {
|
|
case 'SUM':
|
|
return numericValues.reduce((acc, val) => acc + val, 0);
|
|
|
|
case 'AVG':
|
|
return numericValues.length > 0
|
|
? numericValues.reduce((acc, val) => acc + val, 0) / numericValues.length
|
|
: 0;
|
|
|
|
case 'MIN':
|
|
return numericValues.length > 0
|
|
? Math.min(...numericValues)
|
|
: 0;
|
|
|
|
case 'MAX':
|
|
return numericValues.length > 0
|
|
? Math.max(...numericValues)
|
|
: 0;
|
|
|
|
case 'COUNT':
|
|
return values.length;
|
|
|
|
case 'COUNT_EMPTY':
|
|
return values.filter(v => v === null || v === undefined || v === '').length;
|
|
|
|
case 'COUNT_NOT_EMPTY':
|
|
return values.filter(v => v !== null && v !== undefined && v !== '').length;
|
|
|
|
case 'MEDIAN': {
|
|
if (numericValues.length === 0) return 0;
|
|
const sorted = [...numericValues].sort((a, b) => a - b);
|
|
const mid = Math.floor(sorted.length / 2);
|
|
return sorted.length % 2 !== 0
|
|
? sorted[mid]
|
|
: (sorted[mid - 1] + sorted[mid]) / 2;
|
|
}
|
|
|
|
default:
|
|
throw new Error(`Unknown aggregation function: ${functionName}`);
|
|
}
|
|
}
|
|
|
|
// ============================================================
|
|
// Built-in Functions
|
|
// ============================================================
|
|
|
|
/**
|
|
* Available formula functions
|
|
*/
|
|
export const FORMULA_FUNCTIONS = {
|
|
// Math functions
|
|
ROUND: (value, decimals = 0) => {
|
|
const factor = Math.pow(10, decimals);
|
|
return Math.round(value * factor) / factor;
|
|
},
|
|
FLOOR: (value) => Math.floor(value),
|
|
CEIL: (value) => Math.ceil(value),
|
|
ABS: (value) => Math.abs(value),
|
|
SQRT: (value) => Math.sqrt(value),
|
|
POW: (base, exp) => Math.pow(base, exp),
|
|
|
|
// Date functions
|
|
NOW: () => new Date().toISOString(),
|
|
TODAY: () => new Date().toISOString().split('T')[0],
|
|
YEAR: (date) => new Date(date).getFullYear(),
|
|
MONTH: (date) => new Date(date).getMonth() + 1,
|
|
DAY: (date) => new Date(date).getDate(),
|
|
|
|
// String functions
|
|
LEN: (str) => String(str).length,
|
|
UPPER: (str) => String(str).toUpperCase(),
|
|
LOWER: (str) => String(str).toLowerCase(),
|
|
CONCAT: (...args) => args.join(''),
|
|
TRIM: (str) => String(str).trim(),
|
|
};
|
|
|
|
// ============================================================
|
|
// Formula Evaluator
|
|
// ============================================================
|
|
|
|
/**
|
|
* Evaluate a formula with context
|
|
* @param {string} formula - Formula string
|
|
* @param {Object} context - Evaluation context
|
|
* @param {Object} context.variables - Map of $variable_name to value
|
|
* @param {Object} context.row - Current row data (for {{column}} references)
|
|
* @param {Array} context.rows - All rows (for aggregations)
|
|
* @returns {number|string|boolean} Evaluated result
|
|
*/
|
|
export function evaluateFormula(formula, context = {}) {
|
|
// Handle empty/whitespace formulas
|
|
if (!formula || typeof formula !== 'string' || formula.trim() === '') {
|
|
return 0;
|
|
}
|
|
|
|
const trimmed = formula.trim();
|
|
|
|
// Handle pure string literals
|
|
if (trimmed.startsWith('"') && trimmed.endsWith('"')) {
|
|
return trimmed.slice(1, -1);
|
|
}
|
|
if (trimmed.startsWith("'") && trimmed.endsWith("'")) {
|
|
return trimmed.slice(1, -1);
|
|
}
|
|
|
|
// Substitute variables ($variable_name)
|
|
let expression = trimmed;
|
|
const { variables = {}, row = {}, rows = [] } = context;
|
|
|
|
// Replace $variable references
|
|
expression = expression.replace(/\$([a-zA-Z_][a-zA-Z0-9_]*)/g, (match, varName) => {
|
|
const fullName = '$' + varName;
|
|
if (!(fullName in variables)) {
|
|
throw new Error(`Undefined variable: ${fullName}`);
|
|
}
|
|
const value = variables[fullName];
|
|
return typeof value === 'string' ? `"${value}"` : String(value);
|
|
});
|
|
|
|
// Replace {{column}} references with row values
|
|
expression = expression.replace(/\{\{([^}]+)\}\}/g, (match, columnName) => {
|
|
const colName = columnName.trim();
|
|
if (!(colName in row)) {
|
|
throw new Error(`Undefined column: ${colName}`);
|
|
}
|
|
const value = row[colName];
|
|
if (typeof value === 'string') {
|
|
return `"${value}"`;
|
|
}
|
|
return String(value);
|
|
});
|
|
|
|
// Handle IF function specially (ternary)
|
|
expression = expression.replace(
|
|
/IF\s*\(\s*([^,]+)\s*,\s*([^,]+)\s*,\s*([^)]+)\s*\)/gi,
|
|
(match, condition, trueVal, falseVal) => {
|
|
// Evaluate condition - convert == to ===
|
|
const evalCondition = condition.replace(/==/g, '===').replace(/!=/g, '!==');
|
|
try {
|
|
const result = safeEval(evalCondition);
|
|
return result ? trueVal.trim() : falseVal.trim();
|
|
} catch {
|
|
return falseVal.trim();
|
|
}
|
|
}
|
|
);
|
|
|
|
// Handle IFERROR function
|
|
expression = expression.replace(
|
|
/IFERROR\s*\(\s*([^,]+)\s*,\s*([^)]+)\s*\)/gi,
|
|
(match, expr, fallback) => {
|
|
try {
|
|
const result = safeEval(expr.trim());
|
|
if (!isFinite(result)) {
|
|
return fallback.trim();
|
|
}
|
|
return String(result);
|
|
} catch {
|
|
return fallback.trim();
|
|
}
|
|
}
|
|
);
|
|
|
|
// Handle built-in functions
|
|
for (const [funcName, func] of Object.entries(FORMULA_FUNCTIONS)) {
|
|
const regex = new RegExp(`${funcName}\\s*\\(([^)]*)\\)`, 'gi');
|
|
expression = expression.replace(regex, (match, args) => {
|
|
const parsedArgs = parseArguments(args);
|
|
const result = func(...parsedArgs);
|
|
return String(result);
|
|
});
|
|
}
|
|
|
|
// Finally evaluate the expression
|
|
try {
|
|
return safeEval(expression);
|
|
} catch (e) {
|
|
throw new Error(`Formula evaluation error: ${e.message}`);
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Parse function arguments string into array
|
|
* @param {string} argsString - Comma-separated arguments
|
|
* @returns {Array} Parsed arguments
|
|
*/
|
|
function parseArguments(argsString) {
|
|
if (!argsString.trim()) return [];
|
|
|
|
const args = [];
|
|
let current = '';
|
|
let depth = 0;
|
|
let inString = false;
|
|
let stringChar = '';
|
|
|
|
for (const char of argsString) {
|
|
if ((char === '"' || char === "'") && !inString) {
|
|
inString = true;
|
|
stringChar = char;
|
|
current += char;
|
|
} else if (char === stringChar && inString) {
|
|
inString = false;
|
|
current += char;
|
|
} else if (char === '(' && !inString) {
|
|
depth++;
|
|
current += char;
|
|
} else if (char === ')' && !inString) {
|
|
depth--;
|
|
current += char;
|
|
} else if (char === ',' && depth === 0 && !inString) {
|
|
args.push(parseArgValue(current.trim()));
|
|
current = '';
|
|
} else {
|
|
current += char;
|
|
}
|
|
}
|
|
|
|
if (current.trim()) {
|
|
args.push(parseArgValue(current.trim()));
|
|
}
|
|
|
|
return args;
|
|
}
|
|
|
|
/**
|
|
* Parse a single argument value
|
|
* @param {string} value - Argument string
|
|
* @returns {*} Parsed value
|
|
*/
|
|
function parseArgValue(value) {
|
|
if (value.startsWith('"') && value.endsWith('"')) {
|
|
return value.slice(1, -1);
|
|
}
|
|
if (value.startsWith("'") && value.endsWith("'")) {
|
|
return value.slice(1, -1);
|
|
}
|
|
const num = parseFloat(value);
|
|
if (!isNaN(num)) {
|
|
return num;
|
|
}
|
|
return value;
|
|
}
|
|
|
|
/**
|
|
* Safe evaluation of mathematical expressions
|
|
* Only allows: numbers, operators, parentheses, comparison, strings
|
|
* @param {string} expression - Expression to evaluate
|
|
* @returns {*} Result
|
|
*/
|
|
function safeEval(expression) {
|
|
// Security: Only allow safe characters
|
|
const sanitized = expression.trim();
|
|
|
|
// Allow: numbers, operators, parentheses, comparison, strings
|
|
if (!/^[\d\s+\-*/().=<>!&|"'a-zA-Z_]+$/.test(sanitized)) {
|
|
throw new Error('Invalid characters in expression');
|
|
}
|
|
|
|
// Create a limited scope for evaluation
|
|
const func = new Function(`
|
|
"use strict";
|
|
return (${sanitized});
|
|
`);
|
|
|
|
return func();
|
|
}
|
|
|
|
export default {
|
|
evaluateFormula,
|
|
evaluateAggregation,
|
|
FORMULA_FUNCTIONS
|
|
};
|