/**
* Test the MySQL connection settings
*/
function wpdatatables_test_mysql_settings()
{
$return_array = array('success' => array(), 'errors' => array());
$mysql_connection_settings = $_POST['mysql_settings'];
try {
$Sql = new PDTSql($mysql_connection_settings['host'], $mysql_connection_settings['db'], $mysql_connection_settings['user'], $mysql_connection_settings['password'], $mysql_connection_settings['port']);
if ($Sql->isConnected()) {
$return_array['success'][] = __('Successfully connected to the MySQL server', 'wpdatatables');
} else {
$return_array['errors'][] = __('wpDataTables could not connect to MySQL server.', 'wpdatatables');
}
} catch (Exception $e) {
$return_array['errors'][] = __('wpDataTables could not connect to MySQL server. MySQL said: ', 'wpdatatables') . $e->getMessage();
}
echo json_encode($return_array);
exit;
}
/**
* Handle table row delete
*/
function wdt_delete_table_row()
{
global $wpdb;
$table_id = filter_var($_POST['table_id'], FILTER_SANITIZE_NUMBER_INT);
$id_key = filter_var($_POST['id_key'], FILTER_SANITIZE_STRING);
$id_val = filter_var($_POST['id_val'], FILTER_SANITIZE_NUMBER_INT);
$table_data = wdt_get_table_by_id($table_id);
$mysql_table_name = $table_data['mysql_table_name'];
// If current user cannot edit - do nothing
if (!wdt_current_user_can_edit($table_data['editor_roles'], $table_id)) {
exit;
}
do_action('wpdatatables_before_delete_row', $id_val, $table_id);
// If the plugin is using WP DB
if (!get_option('wdtUseSeparateCon')) {
$wpdb->delete($mysql_table_name, array($id_key => $id_val));
} else {
$sql = new PDTSql(WDT_MYSQL_HOST, WDT_MYSQL_DB, WDT_MYSQL_USER, WDT_MYSQL_PASSWORD);
$query = "DELETE FROM " . $mysql_table_name . " WHERE `" . $id_key . "`='" . $id_val . "'";
$sql->doQuery($query);
}
exit;
}
/**
* Handle uploaded file delete
*/
function wdt_delete_uploaded_file()
{
global $wpdb;
$table_id = filter_var($_POST['table_id'], FILTER_SANITIZE_NUMBER_INT);
$id_key = filter_var($_POST['id_key'], FILTER_SANITIZE_STRING);
$id_val = filter_var($_POST['id_val'], FILTER_SANITIZE_STRING);
$key = $_POST['key'];
$table_data = wdt_get_table_by_id($table_id);
$mysql_table_name = $table_data['mysql_table_name'];
do_action('wpdatatables_before_delete_file', $id_val, $table_id);
// First selecting and unlinking the exiting file (if exists);
$rows = $wpdb->get_results("SELECT {$key} FROM {$mysql_table_name} WHERE {$id_key} = '{$id_val}'", ARRAY_A);
if (!empty($rows)) {
$filename = $rows[0][$key];
if (!empty($filename)) {
$filename = urldecode($filename);
if (strtoupper(substr(PHP_OS, 0, 3)) === 'WIN') {
$filename = str_replace(site_url(), str_replace('\\', '/', ABSPATH), $filename);
} else {
$filename = str_replace(site_url(), ABSPATH, $filename);
}
if (file_exists($filename)) {
unlink($filename);
}
}
}
// Updating the value in DB
// If the plugin is using WP DB
if (!get_option('wdtUseSeparateCon')) {
$wpdb->update($mysql_table_name, array($key => ''), array($id_key => $id_val));
} else {
$sql = new PDTSql(WDT_MYSQL_HOST, WDT_MYSQL_DB, WDT_MYSQL_USER, WDT_MYSQL_PASSWORD);
$query = "UPDATE " . $mysql_table_name . "\n\t \t\t\t\t\tSET `" . $key . "`=''\n\t \t\t\t\t\tWHERE `" . $id_key . "`='" . $id_val . "'";
$sql->doQuery($query);
}
echo '';
exit;
}
/**
* Add a new column to manually generated table
*/
public static function addNewManualColumn($table_id, $column_data)
{
global $wpdb;
$table_data = wdt_get_table_by_id($table_id);
$existing_columns = wdt_get_columns_by_table_id($table_id);
$existing_headers = array();
$column_index = 0;
foreach ($existing_columns as $existing_column) {
$existing_headers[] = $existing_column->orig_header;
if ($existing_column->orig_header == $column_data['insert_after']) {
$column_index = $existing_column->pos + 1;
}
}
$new_column_mysql_name = self::generateMySQLColumnName($column_data['name'], $existing_headers);
$columnProperties = self::defineColumnProperties($new_column_mysql_name, $column_data['type']);
// Add the column to MySQL table
$alter_table_statement = "ALTER TABLE {$table_data['mysql_table_name']} \n ADD COLUMN {$columnProperties['create_block']} ";
if ($column_data['insert_after'] == '%%beginning%%') {
$alter_table_statement .= " FIRST";
} else {
if ($column_data['insert_after'] != '%%end%%') {
$alter_table_statement .= " AFTER `{$column_data['insert_after']}`";
}
}
// Call the create statement on WPDB or on external DB if it is defined
if (get_option('wdtUseSeparateCon')) {
// External DB
$Sql = new PDTSql(WDT_MYSQL_HOST, WDT_MYSQL_DB, WDT_MYSQL_USER, WDT_MYSQL_PASSWORD);
$Sql->doQuery($alter_table_statement, array());
} else {
$wpdb->query($alter_table_statement);
}
// Fill in with default value if requested
if ($column_data['fill_default'] == 1) {
$update_fill_default = "UPDATE {$table_data['mysql_table_name']} \n SET `{$new_column_mysql_name}` = '{$column_data['default_value']}' \n WHERE 1";
if (get_option('wdtUseSeparateCon')) {
// External DB
$this->_db->doQuery($update_fill_default, array());
} else {
$wpdb->query($update_fill_default);
}
}
// Move the existing columns if necessary
if ($column_data['insert_after'] == '%%end%%') {
$column_index = count($existing_columns);
} else {
$update_statement = "UPDATE " . $wpdb->prefix . "wpdatatables_columns \n SET pos = pos + 1 \n WHERE table_id = {$table_id} \n AND pos >= " . (int) $column_index;
$wpdb->query($update_statement);
}
// Add the column to wp_wpdatatables_columns
$wpdb->insert($wpdb->prefix . "wpdatatables_columns", array('table_id' => $table_id, 'orig_header' => $new_column_mysql_name, 'display_header' => $column_data['name'], 'filter_type' => $columnProperties['filter_type'], 'column_type' => $columnProperties['column_type'], 'pos' => $column_index, 'possible_values' => str_replace(',,;,|', '|', $column_data['possible_values']), 'default_value' => $column_data['default_value'], 'input_type' => $columnProperties['editor_type']));
}
