setup.php

<?php
/*
 +-------------------------------------------------------------------------+
 | Copyright (C) 2024-2026 Urban-Software.de / Thomas Urban               |
 |                                                                         |
 | This program is free software; you can redistribute it and/or           |
 | modify it under the terms of the GNU General Public License             |
 | as published by the Free Software Foundation; either version 2          |
 | of the License, or (at your option) any later version.                  |
 +-------------------------------------------------------------------------+
 | Cereus Data Export - Plugin Setup                                       |
 +-------------------------------------------------------------------------+
*/

function plugin_cereus_dataexport_install() {
	/* UI hooks */
	api_plugin_register_hook('cereus_dataexport', 'config_arrays',        'cereus_dataexport_config_arrays',   'includes/arrays.php');
	api_plugin_register_hook('cereus_dataexport', 'config_settings',      'cereus_dataexport_config_settings', 'includes/settings.php');
	api_plugin_register_hook('cereus_dataexport', 'draw_navigation_text', 'cereus_dataexport_draw_navigation', 'setup.php');

	/* graph management hooks */
	api_plugin_register_hook('cereus_dataexport', 'graphs_action_array',   'cereus_dataexport_graphs_action_array',   'setup.php');
	api_plugin_register_hook('cereus_dataexport', 'graphs_action_prepare', 'cereus_dataexport_graphs_action_prepare', 'setup.php');
	api_plugin_register_hook('cereus_dataexport', 'graphs_action_execute', 'cereus_dataexport_graphs_action_execute', 'setup.php');

	/* poller hook */
	api_plugin_register_hook('cereus_dataexport', 'poller_bottom', 'cereus_dataexport_poller_bottom', 'setup.php');

	/* realms */
	api_plugin_register_realm('cereus_dataexport', 'cereus_dataexport.php', __('Plugin: Cereus Data Export - Configure', 'cereus_dataexport'), 1);
	api_plugin_register_realm('cereus_dataexport', 'cereus_dataexport_log.php', __('Plugin: Cereus Data Export - View Log', 'cereus_dataexport'), 1);

	/* tables */
	cereus_dataexport_setup_tables();
}

function plugin_cereus_dataexport_uninstall() {
	db_execute("DROP TABLE IF EXISTS plugin_cereus_dataexport_definitions");
	db_execute("DROP TABLE IF EXISTS plugin_cereus_dataexport_items");
	db_execute("DROP TABLE IF EXISTS plugin_cereus_dataexport_log");
}

function plugin_cereus_dataexport_version() {
	return array(
		'name'     => 'cereus_dataexport',
		'version'  => '1.0.0',
		'longname' => 'Cereus Data Export',
		'author'   => 'Urban-Software.de / Thomas Urban',
		'homepage' => 'https://www.urban-software.com',
		'email'    => 'info@urban-software.de',
		'url'      => 'https://www.urban-software.com',
	);
}

function plugin_cereus_dataexport_check_config() {
	return true;
}

function plugin_cereus_dataexport_upgrade($info) {
	return false;
}

/* ==================== Table Creation ==================== */

function cereus_dataexport_setup_tables() {
	db_execute("CREATE TABLE IF NOT EXISTS plugin_cereus_dataexport_definitions (
		id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
		name VARCHAR(128) NOT NULL DEFAULT '',
		enabled TINYINT UNSIGNED NOT NULL DEFAULT 1,
		export_format VARCHAR(10) NOT NULL DEFAULT 'csv',
		time_range INT UNSIGNED NOT NULL DEFAULT 3600,
		schedule_type TINYINT UNSIGNED NOT NULL DEFAULT 0,
		delivery_method TINYINT UNSIGNED NOT NULL DEFAULT 0,
		column_filter VARCHAR(1024) NOT NULL DEFAULT '',
		email_recipients VARCHAR(512) NOT NULL DEFAULT '',
		sftp_host VARCHAR(255) NOT NULL DEFAULT '',
		sftp_port INT UNSIGNED NOT NULL DEFAULT 22,
		sftp_user VARCHAR(128) NOT NULL DEFAULT '',
		sftp_pass VARCHAR(255) NOT NULL DEFAULT '',
		sftp_path VARCHAR(512) NOT NULL DEFAULT '',
		last_run TIMESTAMP NULL DEFAULT NULL,
		created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
		modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
		PRIMARY KEY (id),
		KEY idx_enabled (enabled),
		KEY idx_schedule (schedule_type)
	) ENGINE=InnoDB ROW_FORMAT=Dynamic DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

	db_execute("CREATE TABLE IF NOT EXISTS plugin_cereus_dataexport_items (
		id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
		definition_id BIGINT UNSIGNED NOT NULL,
		local_graph_id INT UNSIGNED NOT NULL DEFAULT 0,
		rra_id INT UNSIGNED NOT NULL DEFAULT 0,
		created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
		PRIMARY KEY (id),
		UNIQUE KEY idx_def_graph (definition_id, local_graph_id),
		KEY idx_definition (definition_id),
		KEY idx_graph (local_graph_id)
	) ENGINE=InnoDB ROW_FORMAT=Dynamic DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

	db_execute("CREATE TABLE IF NOT EXISTS plugin_cereus_dataexport_log (
		id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
		definition_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
		definition_name VARCHAR(128) NOT NULL DEFAULT '',
		success TINYINT UNSIGNED NOT NULL DEFAULT 0,
		file_path VARCHAR(512) NOT NULL DEFAULT '',
		rows_exported INT UNSIGNED NOT NULL DEFAULT 0,
		error_message VARCHAR(1024) NOT NULL DEFAULT '',
		duration_ms INT UNSIGNED NOT NULL DEFAULT 0,
		created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
		PRIMARY KEY (id),
		KEY idx_definition (definition_id),
		KEY idx_created (created),
		KEY idx_success (success)
	) ENGINE=InnoDB ROW_FORMAT=Dynamic DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
}

/* ==================== Navigation ==================== */

function cereus_dataexport_draw_navigation($nav) {
	$nav['cereus_dataexport.php:']     = array('title' => __('Data Exports', 'cereus_dataexport'),     'mapping' => 'index.php:', 'url' => 'cereus_dataexport.php',     'level' => '1');
	$nav['cereus_dataexport.php:edit'] = array('title' => __('(Edit)', 'cereus_dataexport'),           'mapping' => 'index.php:,cereus_dataexport.php:', 'url' => '', 'level' => '2');
	$nav['cereus_dataexport_log.php:'] = array('title' => __('Export Log', 'cereus_dataexport'),       'mapping' => 'index.php:', 'url' => 'cereus_dataexport_log.php', 'level' => '1');
	return $nav;
}

/* ==================== Graph Bulk Actions ==================== */

function cereus_dataexport_graphs_action_array($action) {
	$action['cereus_dataexport_add']    = __('Data Export - Add to Export Definition', 'cereus_dataexport');
	$action['cereus_dataexport_remove'] = __('Data Export - Remove from Export', 'cereus_dataexport');
	return $action;
}

function cereus_dataexport_graphs_action_prepare($save) {
	if (preg_match('/cereus_dataexport_add/', $save['drp_action'])) {
		if (isset($save['graph_array'])) {
			/* show a dropdown of existing definitions */
			$defs = db_fetch_assoc("SELECT id, name FROM plugin_cereus_dataexport_definitions ORDER BY name");
			$def_array = array();
			foreach ($defs as $d) {
				$def_array[$d['id']] = $d['name'];
			}

			print "<tr><td class='textArea'>\n";
			print '<p>' . __('Select the export definition to add these graphs to:', 'cereus_dataexport') . '</p>';
			if (cacti_sizeof($def_array)) {
				print '<p>';
				form_dropdown('cereus_de_definition_id', $def_array, '', '', '', '', '', '');
				print '</p>';
			} else {
				print '<p><b>' . __('No export definitions exist. Create one first.', 'cereus_dataexport') . '</b></p>';
			}
			print '<p>' . __('Graphs to add:', 'cereus_dataexport') . '</p>';
			print '<ul>' . $save['graph_list'] . '</ul>';
			print "</td></tr>\n";
		}
	}

	if (preg_match('/cereus_dataexport_remove/', $save['drp_action'])) {
		if (isset($save['graph_array'])) {
			print "<tr><td class='textArea'>\n";
			print '<p>' . __('Are you sure you want to remove the following graphs from all export definitions?', 'cereus_dataexport') . '</p>';
			print '<ul>' . $save['graph_list'] . '</ul>';
			print "</td></tr>\n";
		}
	}

	return $save;
}

function cereus_dataexport_graphs_action_execute($action) {
	if (preg_match('/cereus_dataexport_add/', $action)) {
		$selected_items = sanitize_unserialize_selected_items(get_nfilter_request_var('selected_items'));
		$definition_id  = get_filter_request_var('cereus_de_definition_id');

		if ($selected_items !== false && $definition_id > 0) {
			$max_graphs = cereus_de_license_max_graphs();
			$current_count = db_fetch_cell_prepared("SELECT COUNT(*) FROM plugin_cereus_dataexport_items WHERE definition_id = ?", array($definition_id));
			$added = 0;

			foreach ($selected_items as $graph_id) {
				if (is_numeric($graph_id) && $graph_id > 0) {
					if ($max_graphs > 0 && ($current_count + $added) >= $max_graphs) {
						raise_message('cereus_de_graph_limit', __('Graph limit reached (%d). Upgrade to Professional or Enterprise for unlimited graphs.', $max_graphs, 'cereus_dataexport'), MESSAGE_LEVEL_ERROR);
						break;
					}

					db_execute_prepared("INSERT IGNORE INTO plugin_cereus_dataexport_items
						(definition_id, local_graph_id, rra_id)
						VALUES (?, ?, 0)",
						array($definition_id, $graph_id));
					$added++;
				}
			}

			if ($added > 0) {
				raise_message('cereus_de_added', __('Graphs added to export definition (%d added).', $added, 'cereus_dataexport'), MESSAGE_LEVEL_INFO);
			}
		}
	}

	if (preg_match('/cereus_dataexport_remove/', $action)) {
		$selected_items = sanitize_unserialize_selected_items(get_nfilter_request_var('selected_items'));

		if ($selected_items !== false) {
			foreach ($selected_items as $graph_id) {
				if (is_numeric($graph_id) && $graph_id > 0) {
					db_execute_prepared("DELETE FROM plugin_cereus_dataexport_items
						WHERE local_graph_id = ?",
						array($graph_id));
				}
			}

			raise_message('cereus_de_removed', __('Graphs removed from export definitions.', 'cereus_dataexport'), MESSAGE_LEVEL_INFO);
		}
	}

	return $action;
}

/* ==================== Poller Hook ==================== */

function cereus_dataexport_poller_bottom() {
	global $config;

	/* only run on primary poller */
	if (isset($config['poller_id']) && $config['poller_id'] > 1) {
		return;
	}

	include_once(__DIR__ . '/includes/constants.php');
	include_once(__DIR__ . '/lib/license_check.php');
	include_once(__DIR__ . '/lib/functions.php');

	$start = microtime(true);

	/* run exports */
	cereus_de_run_exports();

	/* housekeeping: purge old log entries */
	$retention = intval(read_config_option('cereus_de_log_retention'));
	if ($retention <= 0) {
		$retention = 90;
	}

	db_execute_prepared("DELETE FROM plugin_cereus_dataexport_log
		WHERE created < DATE_SUB(NOW(), INTERVAL ? DAY)",
		array($retention));

	$end = microtime(true);
	$runtime = round(($end - $start) * 1000);

	set_config_option('cereus_dataexport_stats', "time:$runtime");
}