demmings / gsSQL / 3920713201

//  Remove comments for testing in NODE

/*  *** DEBUG START ***

export { DERIVEDTABLE, VirtualFields, VirtualField, SelectTables };

import { Table } from './Table.js';

import { Sql } from './Sql.js';

import { SqlParse } from './SimpleParser.js';

//  *** DEBUG END  ***/

const DERIVEDTABLE = "::DERIVEDTABLE::";

/** Perform SQL SELECT operations to retrieve requested data. */

class SelectTables {

    /**

     * @param {Object} ast - Abstract Syntax Tree

     * @param {Map<String,Table>} tableInfo - Map of table info.

     * @param {any[]} bindVariables - List of bind data.

     */

    constructor(ast, tableInfo, bindVariables) {

        /** @property {String} - primary table name. */

        this.primaryTable = ast.FROM[0].table;

        /** @property {Object} - AST of SELECT fields */

        this.astFields = ast.SELECT;

        /** @property {Map<String,Table>} tableInfo - Map of table info. */

        this.tableInfo = tableInfo;

        /** @property {any[]} - Bind variable data. */

        this.bindVariables = bindVariables;

        /** @property {JoinTables} - Join table object. */

        this.dataJoin = new JoinTables([]);

        /** @property {TableFields} */

        this.tableFields = new TableFields();

        if (!tableInfo.has(this.primaryTable.toUpperCase()))

            throw new Error(`Invalid table name: ${this.primaryTable}`);

        /** @property {Table} - Primary table info. */

        this.primaryTableInfo = tableInfo.get(this.primaryTable.toUpperCase());

        //  Keep a list of all possible fields from all tables.

        this.tableFields.loadVirtualFields(this.primaryTable, tableInfo);

        //  Expand any 'SELECT *' fields and add the actual field names into 'astFields'.

        this.astFields = VirtualFields.expandWildcardFields(this.primaryTableInfo, this.astFields);

        //  Define the data source of each field in SELECT field list.

        this.tableFields.updateSelectFieldList(this.astFields);

        //  These are fields REFERENCED, but not actually in the SELECT FIELDS.

        //  So columns referenced by GROUP BY, ORDER BY and not in SELECT.

        //  These temp columns need to be removed after processing.

        this.tableFields.addReferencedColumnstoSelectFieldList(ast);

    }

    /**

     * Process any JOIN condition.

     * @param {Object} ast - Abstract Syntax Tree

     */

    join(ast) {

        if (typeof ast.JOIN !== 'undefined')

            this.dataJoin = new JoinTables(ast.JOIN, this.tableFields);

    }

    /**

      * Retrieve filtered record ID's.

      * @param {Object} ast - Abstract Syntax Tree

      * @returns {Number[]} - Records ID's that match WHERE condition.

      */

    whereCondition(ast) {

        let sqlData = [];

        let conditions = {};

        if (typeof ast.WHERE !== 'undefined') {

            conditions = ast.WHERE;

        }

        else {

            //  Entire table is selected.  

            conditions = { operator: "=", left: "\"A\"", right: "\"A\"" };

        }

        if (typeof conditions.logic === 'undefined')

            sqlData = this.resolveCondition("OR", [conditions]);

        else

            sqlData = this.resolveCondition(conditions.logic, conditions.terms);

        return sqlData;

    }

    /**

    * Recursively resolve WHERE condition and then apply AND/OR logic to results.

    * @param {String} logic - logic condition (AND/OR) between terms

    * @param {Object} terms - terms of WHERE condition (value compared to value)

    * @returns {Number[]} - record ID's 

    */

    resolveCondition(logic, terms) {

        const recordIDs = [];

        for (const cond of terms) {

            if (typeof cond.logic === 'undefined') {

                recordIDs.push(this.getRecordIDs(cond));

            }

            else {

                recordIDs.push(this.resolveCondition(cond.logic, cond.terms));

            }

        }

        let result = [];

        if (logic === "AND") {

            result = recordIDs.reduce((a, b) => a.filter(c => b.includes(c)));

        }

        if (logic === "OR") {

            //  OR Logic

            let tempArr = [];

            for (const arr of recordIDs) {

                tempArr = tempArr.concat(arr);

            }

            result = Array.from(new Set(tempArr));

        }

        return result;

    }

    /**

    * Find record ID's where condition is TRUE.

    * @param {Object} condition - WHERE test condition

    * @returns {Number[]} - record ID's which are true.

    */

    getRecordIDs(condition) {

        /** @type {Number[]} */

        const recordIDs = [];

        const leftFieldConditions = this.resolveFieldCondition(condition.left);

        const rightFieldConditions = this.resolveFieldCondition(condition.right);

        /** @type {Table} */

        this.masterTable = this.dataJoin.isDerivedTable() ? this.dataJoin.getJoinedTableInfo() : this.primaryTableInfo;

        const calcSqlField = new CalculatedField(this.masterTable, this.primaryTableInfo, this.tableFields);

        for (let masterRecordID = 1; masterRecordID < this.masterTable.tableData.length; masterRecordID++) {

            let leftValue = SelectTables.getConditionValue(leftFieldConditions, calcSqlField, masterRecordID);

            let rightValue = SelectTables.getConditionValue(rightFieldConditions, calcSqlField, masterRecordID);

            if (leftValue instanceof Date || rightValue instanceof Date) {

                leftValue = SelectTables.dateToMs(leftValue);

                rightValue = SelectTables.dateToMs(rightValue);

            }

            if (SelectTables.isConditionTrue(leftValue, condition.operator, rightValue))

                recordIDs.push(masterRecordID);

        }

        return recordIDs;

    }

    /**

     * Evaulate value on left/right side of condition

     * @param {ResolvedFieldCondition} fieldConditions - the value to be found will come from:

     * * constant data

     * * field data

     * * calculated field

     * * sub-query 

     * @param {CalculatedField} calcSqlField - data to resolve the calculated field.

     * @param {Number} masterRecordID - current record in table to grab field data from

     * @returns {any} - resolve value.

     */

    static getConditionValue(fieldConditions, calcSqlField, masterRecordID) {

        let leftValue = fieldConditions.constantData;

        if (fieldConditions.columnNumber >= 0) {

            leftValue = fieldConditions.fieldConditionTableInfo.tableData[masterRecordID][fieldConditions.columnNumber];

        }

        else if (fieldConditions.calculatedField !== "") {

            if (fieldConditions.calculatedField.toUpperCase() === "NULL") {

                leftValue = "NULL";

            }

            else {

                leftValue = calcSqlField.evaluateCalculatedField(fieldConditions.calculatedField, masterRecordID);

            }

        }

        else if (fieldConditions.subQuery !== null) {

            const arrayResult = fieldConditions.subQuery.select(masterRecordID, calcSqlField);

            if (typeof arrayResult !== 'undefined' && arrayResult !== null && arrayResult.length > 0)

                leftValue = arrayResult[0][0];

        }

        return leftValue;

    }

    /**

     * Compare where term values using operator and see if comparision is true.

     * @param {any} leftValue - left value of condition

     * @param {String} operator - operator for comparision

     * @param {any} rightValue  - right value of condition

     * @returns {Boolean} - is comparison true.

     */

    static isConditionTrue(leftValue, operator, rightValue) {

        let keep = false;

        switch (operator.toUpperCase()) {

            case "=":

                keep = leftValue == rightValue;         // skipcq: JS-0050

                break;

            case ">":

                keep = leftValue > rightValue;

                break;

            case "<":

                keep = leftValue < rightValue;

                break;

            case ">=":

                keep = leftValue >= rightValue;

                break;

            case "<=":

                keep = leftValue <= rightValue;

                break;

            case "<>":

                keep = leftValue != rightValue;         // skipcq: JS-0050

                break;

            case "!=":

                keep = leftValue != rightValue;         // skipcq: JS-0050

                break;

            case "LIKE":

                keep = SelectTables.likeCondition(leftValue, rightValue);

                break;

            case "NOT LIKE":

                keep = !(SelectTables.likeCondition(leftValue, rightValue));

                break;

            case "IN":

                keep = SelectTables.inCondition(leftValue, rightValue);

                break;

            case "NOT IN":

                keep = !(SelectTables.inCondition(leftValue, rightValue));

                break;

            case "IS NOT":

                keep = !(SelectTables.isCondition(leftValue, rightValue));

                break;

            case "IS":

                keep = SelectTables.isCondition(leftValue, rightValue);

                break;

            case "EXISTS":

                keep = SelectTables.existsCondition(rightValue);

                break;

            case "NOT EXISTS":

                keep = !(SelectTables.existsCondition(rightValue));

                break;

            default:

                throw new Error(`Invalid Operator: ${operator}`);

        }

        return keep;

    }

    /**

     * Retrieve the data for the record ID's specified for ALL SELECT fields.

     * @param {Number[]} recordIDs - record ID's which are SELECTed.

     * @returns {any[][]} - double array of select data.  No column title is included here.

     */

    getViewData(recordIDs) {

        const virtualData = [];

        const calcSqlField = new CalculatedField(this.masterTable, this.primaryTableInfo, this.tableFields);

        const subQuery = new CorrelatedSubQuery(this.tableInfo, this.tableFields);

        for (const masterRecordID of recordIDs) {

            const newRow = [];

            for (const field of this.tableFields.getSelectFields()) {

                if (field.tableInfo !== null)

                    newRow.push(field.getData(masterRecordID));

                else if (field.subQueryAst !== null) {

                    const result = subQuery.select(masterRecordID, calcSqlField, field.subQueryAst);

                    newRow.push(result[0][0]);

                }

                else if (field.calculatedFormula !== "") {

                    const result = calcSqlField.evaluateCalculatedField(field.calculatedFormula, masterRecordID);

                    newRow.push(result);

                }

            }

            virtualData.push(newRow);

        }

        return virtualData;

    }

    /**

     * Returns the entire string in UPPER CASE - except for anything between quotes.

     * @param {String} srcString - source string to convert.

     * @returns {String} - converted string.

     */

    static toUpperCaseExceptQuoted(srcString) {

        let finalString = "";

        let inQuotes = "";

        for (let i = 0; i < srcString.length; i++) {

            let ch = srcString.charAt(i);

            if (inQuotes === "") {

                if (ch === '"' || ch === "'")

                    inQuotes = ch;

                ch = ch.toUpperCase();

            }

            else {

                if (ch === inQuotes)

                    inQuotes = "";

            }

            finalString += ch;

        }

        return finalString;

    }

    /**

     * Parse input string for 'func' and then parse if found.

     * @param {String} functionString - Select field which may contain a function.

     * @param {String} func - Function name to parse for.

     * @returns {String[]} - Parsed function string.

     *   * null if function not found, 

     *   * string array[0] - original string, e.g. **sum(quantity)**

     *   * string array[1] - function parameter, e.g. **quantity**

     */

    static parseForFunctions(functionString, func) {

        const args = [];

        const expMatch = "%1\\s*\\(";

        const matchStr = new RegExp(expMatch.replace("%1", func));

        const startMatchPos = functionString.search(matchStr);

        if (startMatchPos !== -1) {

            const searchStr = functionString.substring(startMatchPos);

            let i = searchStr.indexOf("(");

            const startLeft = i;

            let leftBracket = 1;

            for (i = i + 1; i < searchStr.length; i++) {

                const ch = searchStr.charAt(i);

                if (ch === "(") leftBracket++;

                if (ch === ")") leftBracket--;

                if (leftBracket === 0) {

                    args.push(searchStr.substring(0, i + 1));

                    args.push(searchStr.substring(startLeft + 1, i));

                    return args;

                }

            }

        }

        return null;

    }

    /**

     * Parse the input for a calculated field.

     * String split on comma, EXCEPT if comma is within brackets (i.e. within an inner function)

     * @param {String} paramString - Search and parse this string for parameters.

     * @returns {String[]} - List of function parameters.

     */

    static parseForParams(paramString, startBracket = "(", endBracket = ")") {

        const args = [];

        let bracketCount = 0;

        let start = 0;

        for (let i = 0; i < paramString.length; i++) {

            const ch = paramString.charAt(i);

            if (ch === "," && bracketCount === 0) {

                args.push(paramString.substring(start, i));

                start = i + 1;

            }

            else if (ch === startBracket)

                bracketCount++;

            else if (ch === endBracket)

                bracketCount--;

        }

        const lastStr = paramString.substring(start);

        if (lastStr !== "")

            args.push(lastStr);

        return args;

    }

    /**

     * Compress the table data so there is one record per group (fields in GROUP BY).

     * The other fields MUST be aggregate calculated fields that works on the data in that group.

     * @param {Object} ast - Abstract Syntax Tree

     * @param {any[][]} viewTableData - Table data.

     * @returns {any[][]} - Aggregated table data.

     */

    groupBy(ast, viewTableData) {

        let groupedTableData = viewTableData;

        if (typeof ast['GROUP BY'] !== 'undefined') {

            groupedTableData = this.groupByFields(ast['GROUP BY'], viewTableData);

            if (typeof ast.HAVING !== 'undefined') {

                groupedTableData = this.having(ast.HAVING, groupedTableData);

            }

        }

        else {

            //  If any conglomerate field functions (SUM, COUNT,...)

            //  we summarize all records into ONE.

            if (this.tableFields.getConglomerateFieldCount() > 0) {

                const compressedData = [];

                const conglomerate = new ConglomerateRecord(this.tableFields.getSelectFields());

                compressedData.push(conglomerate.squish(viewTableData));

                groupedTableData = compressedData;

            }

        }

        return groupedTableData;

    }

    /**

    * Group table data by group fields.

    * @param {any[]} astGroupBy - AST group by fields.

    * @param {any[][]} selectedData - table data

    * @returns {any[][]} - compressed table data

    */

    groupByFields(astGroupBy, selectedData) {

        if (selectedData.length === 0)

            return selectedData;

        //  Sort the least important first, and most important last.

        astGroupBy.reverse();

        for (const orderField of astGroupBy) {

            const selectColumn = this.tableFields.getSelectFieldColumn(orderField.column);

            if (selectColumn !== -1) {

                SelectTables.sortByColumnASC(selectedData, selectColumn);

            }

        }

        const groupedData = [];

        let groupRecords = [];

        const conglomerate = new ConglomerateRecord(this.tableFields.getSelectFields());

        let lastKey = this.createGroupByKey(selectedData[0], astGroupBy);

        for (const row of selectedData) {

            const newKey = this.createGroupByKey(row, astGroupBy);

            if (newKey !== lastKey) {

                groupedData.push(conglomerate.squish(groupRecords));

                lastKey = newKey;

                groupRecords = [];

            }

            groupRecords.push(row);

        }

        if (groupRecords.length > 0)

            groupedData.push(conglomerate.squish(groupRecords));

        return groupedData;

    }

    /**

     * Create a composite key that is comprised from all field data in group by clause.

     * @param {any[]} row  - current row of data.

     * @param {any[]} astGroupBy - group by fields

     * @returns {String} - group key

     */

    createGroupByKey(row, astGroupBy) {

        let key = "";

        for (const orderField of astGroupBy) {

            const selectColumn = this.tableFields.getSelectFieldColumn(orderField.column);

            if (selectColumn !== -1)

                key += row[selectColumn].toString();

        }

        return key;

    }

    /**

    * Take the compressed data from GROUP BY and then filter those records using HAVING conditions.

    * @param {Object} astHaving - AST HAVING conditons

    * @param {any[][]} selectedData - compressed table data (from group by)

    * @returns {any[][]} - filtered data using HAVING conditions.

    */

    having(astHaving, selectedData) {

        //  Add in the title row for now

        selectedData.unshift(this.tableFields.getColumnNames());

        //  Create our virtual GROUP table with data already selected.

        const groupTable = new Table(this.primaryTable).loadArrayData(selectedData);

        const tableMapping = new Map();

        tableMapping.set(this.primaryTable.toUpperCase(), groupTable);

        //  Set up for our SQL.

        const inSQL = new Sql().setTables(tableMapping);

        //  Fudge the HAVING to look like a SELECT.

        const astSelect = {};

        astSelect.FROM = [{ table: this.primaryTable }];

        astSelect.SELECT = [{ name: "*" }];

        astSelect.WHERE = astHaving;

        return inSQL.select(astSelect);

    }

    /**

     * Take select data and sort by columns specified in ORDER BY clause.

     * @param {Object} ast - Abstract Syntax Tree for SELECT

     * @param {any[][]} selectedData - Table data to sort.  On function return, this array is sorted.

     */

    orderBy(ast, selectedData) {

        if (typeof ast['ORDER BY'] === 'undefined')

            return;

        const astOrderby = ast['ORDER BY']

        //  Sort the least important first, and most important last.

        const reverseOrderBy = astOrderby.reverse();

        for (const orderField of reverseOrderBy) {

            const selectColumn = this.tableFields.getSelectFieldColumn(orderField.column);

            if (orderField.order.toUpperCase() === "DESC") {

                SelectTables.sortByColumnDESC(selectedData, selectColumn);

            }

            else {

                SelectTables.sortByColumnASC(selectedData, selectColumn);

            }

        }

    }

    /**

     * Removes temporary fields from return data.  These temporary fields were needed to generate

     * the final table data, but are not included in the SELECT fields for final output.

     * @param {any[][]} viewTableData - table data that may contain temporary columns.

     * @returns {any[][]} - table data with temporary columns removed.

     */

    removeTempColumns(viewTableData) {

        const tempColumns = this.tableFields.getTempSelectedColumnNumbers();

        if (tempColumns.length === 0)

            return viewTableData;

        for (const row of viewTableData) {

            for (const col of tempColumns) {

                row.splice(col, 1);

            }

        }

        return viewTableData;

    }

    /**

     * Sort the table data from lowest to highest using the data in colIndex for sorting.

     * @param {any[][]} tableData - table data to sort.

     * @param {Number} colIndex - column index which indicates which column to use for sorting.

     * @returns {any[][]} - sorted table data.

     */

    static sortByColumnASC(tableData, colIndex) {

        tableData.sort(sortFunction);

        /**

         * 

         * @param {any} a 

         * @param {any} b 

         * @returns {Number}

         */

        function sortFunction(a, b) {

            if (a[colIndex] === b[colIndex]) {

                return 0;

            }

            return (a[colIndex] < b[colIndex]) ? -1 : 1;

        }

        return tableData;

    }

    /**

     * Sort the table data from highest to lowest using the data in colIndex for sorting.

     * @param {any[][]} tableData - table data to sort.

     * @param {Number} colIndex - column index which indicates which column to use for sorting.

     * @returns {any[][]} - sorted table data.

     */

    static sortByColumnDESC(tableData, colIndex) {

        tableData.sort(sortFunction);

        /**

         * 

         * @param {any} a 

         * @param {any} b 

         * @returns {Number}

         */

        function sortFunction(a, b) {

            if (a[colIndex] === b[colIndex]) {

                return 0;

            }

            return (a[colIndex] > b[colIndex]) ? -1 : 1;

        }

        return tableData;

    }

    /**

     * @typedef {Object} ResolvedFieldCondition

     * @property {Table} fieldConditionTableInfo

     * @property {Number} columnNumber - use column data from this column, unless -1.

     * @property {String} constantData - constant data used for column, unless null.

     * @property {String} calculatedField - calculation of data for column, unless empty.

     * @property {CorrelatedSubQuery} subQuery - use this correlated subquery object if not null. 

     * 

     */

    /**

     * Determine what the source of value is for the current field condition.

     * @param {Object} fieldCondition - left or right portion of condition

     * @returns {ResolvedFieldCondition}

     */

    resolveFieldCondition(fieldCondition) {

        /** @type {String} */

        let constantData = null;

        /** @type {Number} */

        let columnNumber = -1;

        /** @type {Table} */

        let fieldConditionTableInfo = null;

        /** @type {String} */

        let calculatedField = "";

        /** @type {CorrelatedSubQuery} */

        let subQuery = null;

        //  Maybe a SELECT within...

        if (typeof fieldCondition.SELECT !== 'undefined') {

            const subQueryTableInfo = SelectTables.getSubQueryTableSet(fieldCondition, this.tableInfo);

            const inSQL = new Sql().setTables(subQueryTableInfo);

            inSQL.setBindValues(this.bindVariables);

            let inData = null;

            try {

                inData = inSQL.select(fieldCondition);

                constantData = inData.join(",");

            }

            catch (ex) {

                // IF (big if) a correlated sub-query, it will fail trying to reference a field

                // from the outer query.

                subQuery = new CorrelatedSubQuery(this.tableInfo, this.tableFields, fieldCondition);

            }

        }

        else if (SelectTables.isStringConstant(fieldCondition))

            constantData = SelectTables.extractStringConstant(fieldCondition);

        else if (fieldCondition === '?') {

            //  Bind variable data.

            if (this.bindVariables.length === 0)

                throw new Error("Bind variable mismatch");

            constantData = this.bindVariables.shift();

        }

        else {

            if (isNaN(fieldCondition)) {

                if (this.tableFields.hasField(fieldCondition)) {

                    columnNumber = this.tableFields.getFieldColumn(fieldCondition)

                    fieldConditionTableInfo = this.tableFields.getTableInfo(fieldCondition)

                }

                else {

                    //  Calculated field?

                    calculatedField = fieldCondition;

                }

            }

            else

                constantData = fieldCondition;

        }

        return { fieldConditionTableInfo, columnNumber, constantData, calculatedField, subQuery };

    }

    /**

     * Create a set of tables that are used in sub-query.

     * @param {Object} ast - Sub-query AST.

     * @param {Map<String,Table>} tableInfo - Master set of tables used for entire select.

     * @returns {Map<String,Table>} - table set for sub-query.

     */

    static getSubQueryTableSet(ast, tableInfo) {

        const tableSubSet = new Map();

        const selectTables = Sql.getReferencedTableNamesFromAst(ast);

        for (const found of selectTables) {

            if (found[0] !== "" && !tableSubSet.has(found[0])) {

                tableSubSet.set(found[0], tableInfo.get(found[0]));

            }

        }

        return tableSubSet;

    }

    /**

     * Is the string a constant in the SELECT condition.  

     * @param {String} value - condition to test

     * @returns {Boolean} - Is this string a constant.

     */

    static isStringConstant(value) {

        return value.startsWith('"') && value.endsWith('"') || value.startsWith("'") && value.endsWith("'");

    }

    /**

     * Extract the string literal out of condition.  This removes surrounding quotes.

     * @param {String} value - String that encloses literal string data.

     * @returns {String} - String with quotes removed.

     */

    static extractStringConstant(value) {

        if (value.startsWith('"') && value.endsWith('"'))

            return value.replace(/"/g, '');

        if (value.startsWith("'") && value.endsWith("'"))

            return value.replace(/'/g, '');

    }

    /**

     * Convert input into milliseconds.

     * @param {any} value - date as as Date or String.

     * @returns {Number} - date as ms.

     */

    static dateToMs(value) {

        let year = 0;

        let month = 0;

        let dayNum = 0;

        if (value instanceof Date) {

            year = value.getFullYear();

            month = value.getMonth();

            dayNum = value.getDate();

        }

        else if (typeof value === "string") {

            const dateParts = value.split("/");

            if (dateParts.length === 3) {

                year = parseInt(dateParts[2], 10);

                month = parseInt(dateParts[0], 10) - 1;

                dayNum = parseInt(dateParts[1], 10);

            }

        }

        const newDate = new Date(Date.UTC(year, month, dayNum, 12, 0, 0, 0));

        return newDate.getTime();

    }

    /**

     * Compare strings in LIKE condition

     * @param {String} leftValue - string for comparison

     * @param {String} rightValue - string with wildcard

     * @returns {Boolean} - Do strings match?

     */

    static likeCondition(leftValue, rightValue) {

        // @ts-ignore

        const expanded = rightValue.replace(/%/g, ".*").replace(/_/g, ".");

        const result = leftValue.search(expanded);

        return result !== -1;

    }

    /**

     * Check if leftValue is contained in list in rightValue

     * @param {any} leftValue - value to find in right value

     * @param {String} rightValue - list of comma separated values

     * @returns {Boolean} - Is contained IN list.

     */

    static inCondition(leftValue, rightValue) {

        const items = rightValue.split(",");

        for (let i = 0; i < items.length; i++)

            items[i] = items[i].trimStart().trimEnd();

        let index = items.indexOf(leftValue);

        return index !== -1;

    }

    /**

     * If leftValue is empty (we will consider that as NULL), condition will be true

     * @param {any} leftValue - test this value for NULL

     * @param {any} rightValue - 'NULL' considered as NULL.

     * @returns {Boolean} - Is leftValue NULL (like).

     */

    static isCondition(leftValue, rightValue) {

    }

    /**

     * Test if input is not empty

     * @param {*} rightValue - value to check if empty

     * @returns - true if NOT empty

     */

    static existsCondition(rightValue) {

        return rightValue !== '';

    }

    /**

     * Return a list of column titles for this table.

     * @returns {String[]} - column titles

     */

    getColumnTitles() {

        return this.tableFields.getColumnTitles();

    }

}

/** Evaulate calculated fields in SELECT statement.  This is achieved by converting the request 

 * into javascript and then using 'Function' to evaulate it.  

 */

class CalculatedField {

    /**

     * 

     * @param {Table} masterTable - JOINed table (unless not joined, then primary table)

     * @param {Table} primaryTable - First table in SELECT

     * @param {TableFields} tableFields - All fields from all tables

     */

    constructor(masterTable, primaryTable, tableFields) {

        /** @property {Table} */

        this.masterTable = masterTable;

        /** @property {Table} */

        this.primaryTable = primaryTable;

        /** @property {Map<String,String>} - Map key=calculated field in SELECT, value=javascript equivalent code */ 

        this.sqlServerFunctionCache = new Map();

        /** @property {TableField[]} */

        this.masterFields = tableFields.allFields.filter((vField) => this.masterTable === vField.tableInfo);

    }

    /**

     * Evaluate the calculated field for the current table record and return a value.

     * @param {String} calculatedFormula - calculation from SELECT statement

     * @param {Number} masterRecordID - current record ID.

     * @returns {any} - Evaluated data from calculation.

     */

    evaluateCalculatedField(calculatedFormula, masterRecordID) {

        let result = "";

        // e.g.  special case.  count(*)

        if (calculatedFormula === "*") {