CREATE TABLE `def_ftp` (
`ServiceCode` varchar(100) NOT NULL,
`FtpConnection` varchar(100) DEFAULT NULL,
`Zipremote_connection` varchar(100) DEFAULT NULL,
`SourceFolder` varchar(255) DEFAULT NULL,
`TargetFolder` varchar(255) DEFAULT NULL,
`FileFilter` varchar(100) DEFAULT NULL,
`UnzipDownloads` char(1) DEFAULT 'N',
`ZipUploads` char(1) DEFAULT 'N',
`Direction` varchar(10) DEFAULT NULL,
`WorkFolder` varchar(255) DEFAULT NULL,
`BackupUploads` char(1) DEFAULT 'Y',
`Platform` varchar(10) DEFAULT NULL,
PRIMARY KEY (`ServiceCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
CREATE TABLE `track_ftp` (
`Guid` char(36) NOT NULL,
`SetGuid` char(36) DEFAULT NULL,
`Agent` varchar(100) DEFAULT NULL,
`Direction` varchar(10) DEFAULT NULL,
`FileFolder` varchar(255) DEFAULT NULL,
`FileName` varchar(255) DEFAULT NULL,
`RemoteFileSize` bigint(20) DEFAULT NULL,
`RemoteDate` date DEFAULT NULL,
`TimeStarted` datetime DEFAULT NULL,
`LocalDriveFree` bigint(20) DEFAULT NULL,
`LocalDriveSize` bigint(20) DEFAULT NULL,
`LocalDrivePercent` decimal(5,2) DEFAULT NULL,
`PublicIp` varchar(100) DEFAULT NULL,
`Successful` char(1) DEFAULT 'N',
`Hostname` varchar(100) DEFAULT NULL,
`Package` varchar(100) DEFAULT NULL,
`LocalFileSize` bigint(20) DEFAULT NULL,
`TimeCompleted` datetime DEFAULT NULL,
`FileHash` varchar(64) DEFAULT NULL,
`ProcessedAt` datetime DEFAULT NULL,
`ProcessStatus` varchar(20) DEFAULT 'PENDING',
`ProcessError` text DEFAULT NULL,
`BackoffMinutes` int(11) DEFAULT 1,
PRIMARY KEY (`Guid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
SELECT
RemoteIp, RemoteUrl, Username, RemotePassword, SshPort,
RemoteType
FROM
def_remoteconnections
WHERE
Remote like '{remote}' AND RemoteType in ('FTP','SFTP')
AND Package IN('CORE', 'ADHOC','{package}')
UPDATE
def_remoteconnections
SET
RemotePassword = '{encrypted_password}',
LastLogin = now()
WHERE
Remote like '{remote}'
AND RemoteType in ('FTP','SFTP')
AND Package IN('CORE', 'ADHOC','{package}')
UPDATE
def_remoteconnections
SET
LastLogin = now()
WHERE
Remote like '{remote}'
AND RemoteType in ('FTP','SFTP')
AND Package IN('CORE', 'ADHOC','{package}')
SELECT
count(*)
FROM
track_ftp
WHERE
RemoteFileSize = '{file_size}'
AND RemoteDate = '{file_date}'
AND FileFolder = '{file_folder}'
AND FileName = '{file_name}'
AND Successful = 'Y'
AND Agent = '{agent_id}'
INSERT INTO track_ftp
(Guid, SetGuid, Agent, Direction, FileFolder, FileName,
RemoteFileSize,RemoteDate, TimeStarted, LocalDriveFree,
LocalDriveSize,LocalDrivePercent, Package)
VALUES
('{guid}','{set_guid}','{agent_id}','DOWN',
'{file_folder}', '{file_name}','{file_size}',
'{file_date}',now(), '{drive_free}','{drive_total}',
'{drive_percent}', '{package}')
UPDATE
Track_Ftp
SET
LocalFileSize = '{local_file_size}',
Successful = '{successful}',
TimeCompleted = now()
WHERE
Guid = '{guid}'
INSERT INTO track_ftp (
Guid, SetGuid, Agent, Direction, FileFolder, FileName, LocalFileSize,
RemoteDate, TimeStarted, LocalDriveFree, LocalDriveSize,
LocalDrivePercent, PublicIp, Successful, Hostname, Package
) VALUES (
'{guid}', '{guid}', '{agent_id}', 'SCAN', '{directory}',
'{folder}', '{local_file_size}', now(), now(), '{drive_free}',
'{drive_total}', '{drive_percent}',
'{public_ip}', 'Y', '{hostname}', '{package}'
);
SELECT SourceDirectory FROM def_remoteagent WHERE Action = 'SCAN'
SELECT
count(*)
FROM
track_ftp
WHERE
LocalFileSize = '{local_file_size}'
AND FileFolder = '{directory}' AND FileName = '{file_filter}'
AND Successful = 'Y' AND Agent = '{agent_id}'
INSERT INTO track_ftp (
Guid, SetGuid, Agent, Direction, FileFolder, FileName,
LocalFileSize,RemoteDate, TimeStarted, LocalDriveFree,
LocalDriveSize,LocalDrivePercent, PublicIp,Hostname, Package
) VALUES (
'{guid}','{guid}','{agent_id}','UP','{directory}',
'{folder}','{local_file_size}',now(),now(), '{drive_free}',
'{drive_total}','{drive_percent}',
'{public_ip}','{hostname}', '{package}'
)
UPDATE
track_ftp
SET
Successful = 'Y',
TimeCompleted = now()
WHERE
Guid = '{guid}'
SELECT
FtpConnection, Zipremote_connection, SourceFolder, TargetFolder,
FileFilter, UnzipDownloads, ZipUploads, Direction, WorkFolder,
BackupUploads,Platform
FROM
def_Ftp
WHERE
ServiceCode = '{service_code}'
SELECT
CONCAT(REMOTE, ': ', REMOTEURL) as option
FROM
def_remoteconnections
WHERE
Package IN('CORE', 'ADHOC','{package}')
AND RemoteType IN('FTP', 'SFTP', 'FTPS')
AND NOT RemoteUrl IS NULL
SELECT
coalesce(Remotedir,".")
FROM
def_remoteconnections
WHERE
Package IN('CORE', 'ADHOC','{package}')
AND Remote = '{option}'
AND RemoteType IN('FTP', 'SFTP', 'FTPS')
INSERT IGNORE INTO def_notify
(NotifyCode, package, Description, Active,
NotifyMqttSend, Module)
VALUES
('{code}', '{package}', '{message}', 'Y',
'Y', 'ObjFtp')
SELECT TimeStarted, BackoffMinutes
FROM `{tracking_table}`
WHERE Direction = 'IN'
ORDER BY TimeStarted DESC
LIMIT 1
SELECT FileHash, ProcessStatus
FROM `{tracking_table}`
WHERE FileName = '{file_name}'
AND RemoteFileSize = {remote_file_size}
AND RemoteDate = '{remote_date}'
AND ProcessStatus IN ('DONE', 'DOWNLOADED')
ORDER BY TimeStarted DESC
LIMIT 1
SELECT COUNT(*)
FROM `{tracking_table}`
WHERE FileHash = '{file_hash}'
AND ProcessStatus IN ('DONE', 'DOWNLOADED')
INSERT INTO `{tracking_table}`
(SetGuid, Guid, Agent, Direction, FileFolder, FileName,
RemoteFileSize, RemoteDate,
TimeStarted, FileHash,
ProcessStatus, BackoffMinutes,
Package, AlertSent)
VALUES (
'{set_guid}',
'{guid}',
'{agent_id}',
'IN',
'{remote_path}',
'{file_name}',
{remote_file_size},
'{remote_date}',
NOW(),
'{file_hash}',
'DOWNLOADED',
{backoff_min},
'{package}',
'N'
)
UPDATE `{tracking_table}`
SET BackoffMinutes = {backoff_min}
WHERE ProcessStatus = 'DOWNLOADED'
ORDER BY TimeStarted DESC
LIMIT 1
UPDATE `{tracking_table}`
SET BackoffMinutes = {next_backoff}
WHERE Direction = 'IN'
ORDER BY TimeStarted DESC
LIMIT 1
SELECT MAX(TimeCompleted)
FROM `{tracking_table}`
WHERE ProcessStatus IN ('DONE', 'DOWNLOADED')
SELECT COUNT(*)
FROM `{tracking_table}`
WHERE Direction = 'IN'
AND DATE(TimeStarted) = '{today}'
AND ProcessStatus IN ('DONE', 'DOWNLOADED')
SELECT AlertSent
FROM `{tracking_table}`
WHERE Direction = 'IN'
ORDER BY TimeStarted DESC
LIMIT 1
UPDATE `{tracking_table}`
SET AlertSent = 'Y'
WHERE Direction = 'IN'
ORDER BY TimeStarted DESC
LIMIT 1
UPDATE `{tracking_table}`
SET ProcessStatus = '{status}',
ProcessedAt = NOW(),
TimeCompleted = NOW(){error_clause}
WHERE FileName = '{file_name}'
AND ProcessStatus = 'DOWNLOADED'
ORDER BY TimeStarted DESC
LIMIT 1