CREATE TABLE `def_calculations` (
`CalculationName` varchar(255) NOT NULL DEFAULT '',
`CalculationGroup` varchar(255) NOT NULL DEFAULT '',
`Package` varchar(100) NOT NULL DEFAULT '',
`Module` VARCHAR(255) DEFAULT NULL,
`Rank` int DEFAULT 0,
`Active` char(1) DEFAULT 'Y',
`CalculationSql` text DEFAULT NULL,
`SimulationSql` text DEFAULT NULL,
`PreSql` text DEFAULT NULL,
`PostSql` text DEFAULT NULL,
`TargetTable` varchar(255) DEFAULT NULL,
`HasResult` char(1) DEFAULT NULL,
`MapGuid` varchar(255) DEFAULT NULL,
`MapParam2` varchar(255) DEFAULT NULL,
`MapParam3` varchar(255) DEFAULT NULL,
`MapParam4` varchar(255) DEFAULT NULL,
`MapParam5` varchar(255) DEFAULT NULL,
`MapParam6` varchar(255) DEFAULT NULL,
`MapParam7` varchar(255) DEFAULT NULL,
`MapParam8` varchar(255) DEFAULT NULL,
`MapParam9` varchar(255) DEFAULT NULL,
`SetSql` text DEFAULT NULL,
`SourceTable` varchar(255) DEFAULT NULL,
`Operation` varchar(255) DEFAULT NULL,
`SourceGuid` varchar(255) DEFAULT NULL,
`TargetGuid` varchar(255) DEFAULT NULL,
`MapNames` text DEFAULT NULL,
`Description` text DEFAULT NULL,
`version` varchar(50) DEFAULT NULL,
`lastUpdate` datetime DEFAULT NULL,
`calculationGuid` varchar(255) DEFAULT NULL,
PRIMARY KEY (`CalculationName`,`CalculationGroup`,`Package`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
CREATE TABLE `def_calculation` (
`CalculationGroup` varchar(255) NOT NULL DEFAULT '',
`Package` varchar(100) NOT NULL DEFAULT '',
`Module` VARCHAR(255) DEFAULT NULL,
`Threewords` varchar(255) DEFAULT NULL,
`Presql` text DEFAULT NULL,
`Postsql` text DEFAULT NULL,
`SecurityReview` text DEFAULT NULL,
`SecurityReviewDate` datetime DEFAULT NULL,
`EfficiencyReview` text DEFAULT NULL,
`EfficiencyReviewDate` datetime DEFAULT NULL,
`RaciSimulation` text DEFAULT NULL,
`RaciProduction` text DEFAULT NULL,
`RaciOutcome` text DEFAULT NULL,
`RaciDevelopment` text DEFAULT NULL,
PRIMARY KEY (`CalculationGroup`,`Package`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
CREATE TABLE `track_calculations` (
`TrackId` int NOT NULL AUTO_INCREMENT,
`CalculationGroup` varchar(255) DEFAULT NULL,
`CalculationName` varchar(255) DEFAULT NULL,
`Package` varchar(100) DEFAULT NULL,
`CalculationGuid` varchar(255) DEFAULT NULL,
`Guid` varchar(255) DEFAULT NULL,
`CalculationSql` text DEFAULT NULL,
`CalculationError` text DEFAULT NULL,
`CalculationReturn` text DEFAULT NULL,
`EndTime` datetime DEFAULT NULL,
`ElapsedTime` decimal(10,4) DEFAULT NULL,
PRIMARY KEY (`TrackId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
CREATE TABLE IF NOT EXISTS `def_calculation_history` (
`Guid` VARCHAR(64) NOT NULL,
`CalculationName` VARCHAR(255) NOT NULL,
`Package` VARCHAR(50) NOT NULL,
`Module` VARCHAR(255) DEFAULT 'ObjCalculation',
`Version` VARCHAR(50) NOT NULL,
`ThreeWords` VARCHAR(255) DEFAULT NULL,
`VersionDate` DATETIME DEFAULT NULL,
`NodeCount` INT DEFAULT 0,
`ChangedBy` VARCHAR(255) DEFAULT NULL,
`ChangeType` VARCHAR(50) DEFAULT 'IMPORT',
`ChangeNotes` TEXT DEFAULT NULL,
`CreatedAt` DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`CalculationName`, `Package`, `Version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
{select_clause},
PreSql, PostSql, TargetTable, HasResult,
MapGuid, MapParam2, MapParam3, MapParam4, MapParam5, MapParam6, MapParam7, MapParam8, MapParam9,
SetSql, SourceTable, Operation, SourceGuid, TargetGuid, MapNames
FROM `{table_name}`
WHERE CalculationName = '{calculation_name}'
AND `CalculationGroup` = '{group_name}'
AND `Package` IN ('{package}', 'CORE', 'SYSTEM', '')
ORDER BY FIELD(`Package`, '{package}', 'CORE', 'SYSTEM', '')
LIMIT 1
SELECT
CalculationName,
RANK,
COALESCE(HasResult, ''),
COALESCE(SetSql, ''),
COALESCE(PreSql, ''),
COALESCE(PostSql, ''),
CalculationSql
FROM
def_calculations
WHERE
`CalculationGroup` = '{group_name}' AND Active = 'Y'
ORDER BY
`Rank`
SELECT
CalculationName
FROM
def_calculations
WHERE
`CalculationGroup` = '{group_name}'
AND Active = 'Y'
AND Package IN ('{package}', 'CORE', '')
ORDER BY
`Rank`
SELECT
*
FROM
def_calculation
WHERE
`CalculationGroup` = '{group_name}'
AND Package IN ('{package}', 'CORE', '')
INSERT INTO def_calculation (CalculationGroup, Package, Threewords, Presql, Postsql)
VALUES ('{group_name}', '{package}', '', '', '')
UPDATE
def_calculation
SET
Threewords = '{threewords}'
WHERE
`CalculationGroup` = '{group_name}'
AND Package IN ('{package}', 'CORE', '')
SELECT
CalculationName,
COALESCE(Description, ''),
COALESCE(HasResult, '')
FROM
def_calculations
WHERE
CalculationGroup LIKE '{calculation_group}'
ORDER BY
Rank
SELECT
CONCAT(`CalculationGroup`, ' : ', Description) AS Name
FROM
def_calculations
WHERE
Package = '{package}'
AND Rank = 1
INSERT INTO {target_table} ({target_guid})
VALUES ('{source_value}')
SELECT * FROM {target_table}
WHERE {target_guid} = '{source_value}'
UPDATE {target_table}
SET {set_clause}
WHERE {target_guid} = '{source_value}'
SELECT COUNT(*)
FROM def_notify
WHERE NotifyCode = 'CALC_FAIL'
INSERT INTO def_notify
(NotifyCode, NotifySlackSend, Usergroups, Package, NotifySmsConnection, NotifySmsList)
VALUES ('CALC_FAIL', 'Y', 'SYSADMIN', 'ALL', '', '')
INSERT INTO track_calculations (
CalculationGroup, CalculationName, Package, CalculationGuid, Guid,
CalculationSql, CalculationError, CalculationReturn, EndTime, ElapsedTime
)
VALUES (
'{group_name}', '{calculation_name}', '{package}', '{calc_guid}', '{guid}',
'{calculation_sql}',
'{calculation_error}',
'{calculation_return}',
NOW(), '{elapsed_time}'
)