CREATE TABLE IF NOT EXISTS `def_report` (
`Package` char(36) NOT NULL DEFAULT 'ADHOC',
`ReportCode` char(255) NOT NULL,
`Module` varchar(255) DEFAULT NULL,
`Active` char(1) NOT NULL DEFAULT 'Y',
`Title` longtext DEFAULT NULL COMMENT 'Report heading',
`Glyph` char(255) DEFAULT NULL,
`ReportType` char(255) DEFAULT NULL,
`Type` char(255) DEFAULT NULL,
`Notes` longtext DEFAULT NULL,
`ReportNotes` longtext DEFAULT NULL COMMENT 'Supplementary text block',
`NotesFooter` longtext DEFAULT NULL,
`Xtitle` longtext DEFAULT NULL COMMENT 'Label for X-axis in graph',
`YTitle` char(255) DEFAULT NULL COMMENT 'Label for Y-axis in graph',
`Colour` char(50) DEFAULT NULL,
`ReportFields` longtext DEFAULT NULL COMMENT 'Will replace $reportfields$ in query',
`Query` longtext DEFAULT NULL COMMENT 'The main query to run',
`AltQuery` longtext DEFAULT NULL,
`InlineLayout` longtext DEFAULT NULL,
`InlineStyle` longtext DEFAULT NULL,
`InlineJS` longtext DEFAULT NULL,
`ReportStyle` longtext DEFAULT NULL,
`RemoteConnection` char(255) DEFAULT NULL,
`Template` char(255) DEFAULT NULL,
`Options` longtext DEFAULT NULL,
`Level` int(11) DEFAULT 2,
`UserGroup` mediumtext DEFAULT NULL COMMENT 'Visibility filter',
`Width` int(11) DEFAULT 50 COMMENT 'Fixed width for charts and tables',
`Height` int(11) DEFAULT 50,
`MinVal` int(11) DEFAULT 0,
`MaxVal` int(11) DEFAULT 0,
`MaxRows` int(11) DEFAULT NULL,
`Cycle` int(11) DEFAULT 1 COMMENT 'Change report outcome based on cycle',
`CycleMax` int(11) DEFAULT 10,
`ReportClass` char(255) DEFAULT NULL COMMENT 'CSS class applied to the report container',
`AsWidget` char(1) DEFAULT 'N',
`IsModal` char(1) DEFAULT 'N',
`UseExport` char(1) DEFAULT 'N',
`DoPublish` char(1) DEFAULT 'N' COMMENT 'Publish the report to a file daily',
`LoadLazy` char(1) DEFAULT '0',
`LoadOnClick` char(1) DEFAULT '0',
`ActionName` char(255) DEFAULT NULL,
`ActionTarget` char(255) DEFAULT NULL,
`ActionGlyph` char(255) DEFAULT NULL,
`ActionParameters` longtext DEFAULT NULL,
`DismissName` char(255) DEFAULT NULL,
`DismissPath` char(255) DEFAULT NULL,
`DismissGlyph` char(55) DEFAULT NULL,
`DismissJs` longtext DEFAULT NULL,
`DetailUrl` longtext DEFAULT NULL,
`RedirectSql` longtext DEFAULT NULL,
`GateSql` longtext DEFAULT NULL COMMENT 'If this result equals 1 - run the main sql',
`PreSql` longtext DEFAULT NULL,
`PostSql` longtext DEFAULT NULL,
`SourceQuery` longtext DEFAULT NULL,
`SourceTtl` int(11) DEFAULT NULL,
`SourceTime` datetime DEFAULT NULL,
`ClientTtl` int(11) DEFAULT 0,
`ClientRefresh` int(11) DEFAULT 0,
`ServerCache` char(255) DEFAULT NULL,
`ServerTtl` int(11) DEFAULT NULL,
`NextReportSql` mediumtext DEFAULT NULL,
`Ribbon` char(255) DEFAULT NULL,
`RibbonColour` char(255) DEFAULT NULL,
`ModalTitle` char(255) DEFAULT NULL,
`ModalSize` char(255) DEFAULT NULL,
`ModalButton` char(255) DEFAULT NULL,
`MenuTitle` char(255) DEFAULT NULL,
`MenuGlyph` char(255) DEFAULT NULL,
`MenuGroup` char(255) DEFAULT '',
`MenuBlockRank` int(11) DEFAULT NULL,
`MenuRank` int(11) DEFAULT NULL,
`Param1Name` char(255) DEFAULT NULL COMMENT 'Display name for filter options',
`Param2Name` longtext DEFAULT NULL COMMENT 'Display name for filter options',
`Param3Name` longtext DEFAULT NULL COMMENT 'Display name for filter options',
`Param1Sql` longtext DEFAULT NULL COMMENT 'List for filter options',
`Param2Sql` longtext DEFAULT NULL COMMENT 'List for filter options',
`Param3Sql` longtext DEFAULT NULL COMMENT 'List for filter options',
`UseTime` char(1) DEFAULT 'N' COMMENT 'Time filter',
`QCDev` char(255) DEFAULT NULL,
`QCClient` char(255) DEFAULT NULL,
`FileFormat` char(255) DEFAULT NULL,
`MimeType` char(255) DEFAULT NULL,
`Document_pdf` char(255) DEFAULT NULL,
`Document_xml` char(255) DEFAULT NULL,
`Publishdir` char(255) DEFAULT NULL,
`Publishhost` char(255) DEFAULT NULL,
`PublishRemote` char(50) DEFAULT NULL,
`ShowOnMobile` int(11) DEFAULT NULL,
`Company` char(255) DEFAULT NULL,
`Version` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY (`ReportCode`, `Package`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
CREATE TABLE `def_report_review` (
`ReportCode` char(255) NOT NULL,
`Block` char(36) NOT NULL DEFAULT 'ADHOC',
`Package` char(36) DEFAULT NULL,
`Module` VARCHAR(255) DEFAULT NULL,
`ActionName` char(255) DEFAULT NULL,
`Title` mediumtext DEFAULT NULL,
`DismissPath` mediumtext DEFAULT NULL,
`XTitle` char(255) DEFAULT NULL,
`Width` int(11) DEFAULT 50,
`QCAnalist` char(255) DEFAULT NULL,
`Template` char(255) DEFAULT NULL,
`Validation` mediumtext DEFAULT NULL,
`Parent` char(255) DEFAULT NULL,
`Company` char(255) DEFAULT NULL,
`Validationjs` mediumtext DEFAULT NULL,
`LastDuration` datetime DEFAULT NULL,
`ActionParameters` char(255) DEFAULT NULL,
`alertsql` mediumtext DEFAULT NULL,
`Level` int(11) DEFAULT 2,
`Param2Sql` mediumtext DEFAULT NULL,
`Colour` char(50) DEFAULT NULL,
`TTL` int(11) DEFAULT 0,
`Document_pdf` char(255) DEFAULT NULL,
`FlowNotes` mediumtext DEFAULT NULL,
`Notes` mediumtext DEFAULT NULL,
`Param1Name` char(255) DEFAULT NULL,
`AsWidget` TEXT DEFAULT 'N',
`UserGroups` text DEFAULT NULL,
`alertresettime` int(11) DEFAULT NULL,
`DoMenu` char(1) DEFAULT 'N',
`Param1Sql` mediumtext DEFAULT NULL,
`ParameterBlock` char(255) DEFAULT NULL,
`Document_wav` char(255) DEFAULT NULL,
`Glyph` char(255) DEFAULT NULL,
`RemoteConnection` char(255) DEFAULT NULL,
`DismissJs` mediumtext DEFAULT NULL,
`Version` timestamp NULL DEFAULT
current_timestamp()
ON UPDATE current_timestamp(),
`Type` TEXT DEFAULT NULL,
`ReportFields` mediumtext DEFAULT NULL,
`Widgetcolour` char(255) DEFAULT NULL,
`InteractiveElement` char(55) DEFAULT NULL,
`DetailUrl` char(255) DEFAULT NULL,
`ReportClass` char(255) DEFAULT NULL,
`ChildReport` char(55) DEFAULT NULL,
`Cycle` int(11) DEFAULT 1,
`infrastructure` char(1) DEFAULT NULL,
`Publishdir` char(255) DEFAULT NULL,
`Strategy_id` char(25) DEFAULT NULL,
`Publishhost` char(255) DEFAULT NULL,
`Subquery` char(255) DEFAULT NULL,
`ReportType` char(50) DEFAULT NULL,
`UserGroup` char(255) DEFAULT NULL,
`LoadLazy` char(1) DEFAULT '0',
`Options` mediumtext DEFAULT NULL,
`YTitle` char(255) DEFAULT NULL,
`NotesFooter` mediumtext DEFAULT NULL,
`Groupkey` char(255) DEFAULT NULL,
`Valdationjs` text DEFAULT NULL,
`Query` mediumtext DEFAULT NULL,
`LoadOnClick` char(1) DEFAULT '0',
`Category` mediumtext DEFAULT NULL,
`EditableColumns` char(1) DEFAULT 'N',
`Group` char(255) DEFAULT NULL,
`ActionTarget` char(255) DEFAULT NULL,
`Team` char(255) DEFAULT NULL,
`MenuBlockRank` int(11) DEFAULT NULL,
`InlineStyle` mediumtext DEFAULT NULL,
`QCClient` char(255) DEFAULT 'N',
`AlertChannel` char(255) DEFAULT '0',
`DismissName` char(255) DEFAULT NULL,
`Groups` TEXT NOT NULL
DEFAULT 'SYSADMIN',
`Companies` text DEFAULT NULL,
`Client` char(255) DEFAULT 'N',
`IsModal` char(1) DEFAULT 'N',
`MinVal` int(11) DEFAULT 0,
`CycleMax` int(11) DEFAULT 10,
`Height` int(11) DEFAULT 50,
`Subblock` char(255) DEFAULT 'MAIN',
`PublishRemote` char(50) DEFAULT NULL,
`Layout` mediumtext DEFAULT NULL,
`UseExport` char(1) DEFAULT 'N',
`RedirectSql` mediumtext DEFAULT NULL,
`Param3Name` mediumtext DEFAULT NULL,
`Subkey` char(255) DEFAULT NULL,
`Valdation` text DEFAULT NULL,
`ReportNotes` mediumtext DEFAULT NULL,
`MenuRank` int(11) DEFAULT NULL,
`InlineLayout` mediumtext DEFAULT NULL,
`MenuTitle` char(255) DEFAULT NULL,
`DoPublish` char(1) DEFAULT 'N',
`QCDev` char(255) DEFAULT NULL,
`FileFormat` char(255) DEFAULT NULL,
`Param2Name` mediumtext DEFAULT NULL,
`Refresh` int(11) DEFAULT 0,
`Document_xml` char(255) DEFAULT NULL,
`UseTime` char(1) DEFAULT 'N',
`ActionGlyph` char(55) DEFAULT NULL,
`gatesql` mediumtext DEFAULT NULL,
`InlineJS` mediumtext DEFAULT NULL,
`LastRun` datetime DEFAULT NULL,
`MaxVal` int(11) DEFAULT 0,
`UseSummary` char(1) DEFAULT 'N',
`alertlasttrigger` datetime DEFAULT NULL,
`AlertVal` int(11) DEFAULT 0,
`DismissGlyph` char(55) DEFAULT NULL,
`postsql` mediumtext DEFAULT NULL,
`AltQuery` mediumtext DEFAULT NULL,
`presql` mediumtext DEFAULT NULL,
`Param3Sql` mediumtext DEFAULT NULL,
`blockrank` int(11) DEFAULT NULL,
`Column 2` char(50) DEFAULT NULL,
`MaxRows` int(11) DEFAULT NULL,
`AllowCache` char(1) DEFAULT 'N',
PRIMARY KEY (`ReportCode`, `Block`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE={collation};
CREATE TABLE IF NOT EXISTS `Def_reporttype` (
`Superclass` varchar(255) NOT NULL DEFAULT '',
`Class` varchar(255) NOT NULL DEFAULT '',
`Module` varchar(255) DEFAULT NULL,
`Options` longtext DEFAULT NULL COMMENT 'JSON options merged with parent class options',
PRIMARY KEY (`Superclass`, `Class`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
SELECT * FROM Def_reporttype WHERE Superclass LIKE '{superclass}'
AND Class LIKE '{class_name}'
SELECT Options FROM def_report WHERE ReportCode LIKE '{instance}'
INSERT INTO Def_reporttype (Superclass, Class) VALUES ('{superclass}', '{class_name}')
INSERT INTO def_report (ReportCode) VALUES ('{report_code}')
SELECT COALESCE(Template, 'basic') AS Template FROM def_form WHERE formname = '{param1}'
AND Package IN ('{package}', '{archetype}', 'ALL', 'SYSTEM', 'CORE')
SELECT (COALESCE(ClientTtl, 0) + COALESCE(ClientRefresh, 0)) AS Dynamic FROM def_report WHERE COALESCE(Package, '{package}') IN ('{package}', '{archetype}', 'ALL', 'SYSTEM', 'CORE')
AND ReportCode = '{report_code}'
SELECT RedirectSql FROM def_report WHERE COALESCE(Package, '{package}') IN ('{package}', '{archetype}', 'ALL', 'SYSTEM', 'CORE')
AND ReportCode = '{report_code}'
SELECT * FROM def_report
WHERE COALESCE(Package, '{package}') IN ('{package}', '{archetype}')
AND ReportCode = '{report_code}'
UNION
SELECT * FROM def_report
WHERE Package IN ('ALL', 'SYSTEM', 'CORE')
AND ReportCode = '{report_code}'
LIMIT 1