CREATE TABLE `def_dataexport` (
`Dataexportcode` char(255) NOT NULL DEFAULT '',
`Package` char(36) NOT NULL,
`Module` VARCHAR(255) DEFAULT NULL,
`ReportCode` char(255) DEFAULT NULL,
`Description` mediumtext DEFAULT NULL,
`ExportType` char(50) DEFAULT NULL,
`Query` mediumtext DEFAULT NULL,
`TableName` char(255) DEFAULT NULL,
`Filename` char(100) DEFAULT NULL,
`Delimiter` char(50) DEFAULT NULL,
`Param1` char(255) DEFAULT NULL,
`Param2` char(255) DEFAULT NULL,
`Param3` char(255) DEFAULT NULL,
`Directory` char(100) DEFAULT '',
`QuoteChar` char(50) DEFAULT NULL,
`Overwrite` char(1) DEFAULT 'N',
`Result1Col` char(255) DEFAULT '',
`Result2Col` char(255) DEFAULT '',
`Result3Col` char(255) DEFAULT '',
`Result4Col` char(255) DEFAULT '',
`Result5Col` char(255) DEFAULT '',
`Result6Col` char(255) DEFAULT '',
`Result7Col` char(255) DEFAULT '',
`Result8Col` char(255) DEFAULT '',
`Result9Col` char(255) DEFAULT '',
`Remote` char(255) DEFAULT NULL,
`RemoteFTP` char(255) DEFAULT NULL,
`Compress` char(5) DEFAULT NULL,
`RemoteFolder` char(255) DEFAULT NULL,
PRIMARY KEY (`Dataexportcode`,`Package`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
CREATE TABLE `def_Report` ( `ReportCode` varchar(255) NOT NULL, `ReportName` varchar(255) DEFAULT NULL, `ReportQuery` longtext, `Package` varchar(255) DEFAULT NULL, `Module` VARCHAR(255) DEFAULT NULL, PRIMARY KEY (`ReportCode`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
CREATE TABLE IF NOT EXISTS `stage_dataexport` (
`Guid` char(255) NOT NULL,
`DataexportCode` char(255) DEFAULT NULL,
`Strategy_id` char(25) DEFAULT NULL,
`Status` char(50) DEFAULT NULL,
`StatusError` char(255) DEFAULT NULL,
`ReportCode` char(50) DEFAULT '',
`Description` longtext DEFAULT NULL,
`ExportType` char(50) DEFAULT NULL,
`Query` longtext DEFAULT NULL,
`TableName` char(255) DEFAULT NULL,
`Company` char(255) DEFAULT NULL,
`Filename` char(100) DEFAULT NULL,
`Directory` char(100) DEFAULT '',
`Delimiter` char(50) DEFAULT NULL,
`QuoteChar` char(50) DEFAULT NULL,
`Overwrite` char(1) DEFAULT 'N',
`Param1` char(255) DEFAULT NULL,
`Param2` char(255) DEFAULT NULL,
`Param3` char(255) DEFAULT NULL,
`ReturnPid` int(11) DEFAULT 0,
`ReturnGuid` char(255) DEFAULT '',
`RDG` int(11) DEFAULT NULL,
`Thread` int(11) DEFAULT 0,
`Nodename` char(255) DEFAULT NULL,
`Checkoutname` char(255) DEFAULT NULL,
`TimeScheduled` datetime DEFAULT NULL,
`TimeAdded` timestamp NULL DEFAULT current_timestamp(),
`TimeThreaded` datetime DEFAULT NULL,
`TimeStarted` datetime DEFAULT NULL,
`TimeCompleted` datetime DEFAULT NULL,
PRIMARY KEY (`Guid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
SELECT *
FROM def_Report
WHERE ReportCode = '{report_code}'
AND COALESCE(Package, '{package}') IN ('ALL', 'SYSTEM', 'CORE', '{package}', '{archetype}')
LIMIT 1
SELECT
DataexportCode, Directory, Filename, ReportCode,
OverWrite, ExportType,
Param1, Param2, Param3, Delimiter, QuoteChar, TableName,
Query, Remote, RemoteFTP, Compress, RemoteFolder
FROM `{def_table}`
WHERE DataexportCode = '{data_export_code}'
LIMIT 1
SELECT
tablename, reportcode, query
FROM
stage_dataexport
WHERE
guid = '{guid}'
UPDATE
stage_dataexport
SET
TimeCompleted = NOW(),
Status = '{status}',
Filename = '{filename}'
WHERE
guid = '{stage_guid}'
SELECT
S.Guid, S.Dataexportcode, S.Checkoutname, S.ReturnPid, S.ReturnGuid
FROM
stage_dataexport AS S
INNER JOIN
`{def_table}` AS D ON S.Dataexportcode = D.Dataexportcode
WHERE
S.Guid = '{guid}'