| 1 |
<?php |
| 2 |
/** |
| 3 |
* Tables — visual database table manager |
| 4 |
* |
| 5 |
* @package WPbot_Automator |
| 6 |
*/ |
| 7 |
|
| 8 |
namespace WPbot_Automator\Core; |
| 9 |
|
| 10 |
if ( ! defined( 'ABSPATH' ) ) { |
| 11 |
exit; |
| 12 |
} |
| 13 |
|
| 14 |
/** |
| 15 |
* Class Tables |
| 16 |
*/ |
| 17 |
class Tables { |
| 18 |
|
| 19 |
const TABLE_META = 'wpbot_automator_tables'; |
| 20 |
|
| 21 |
// ── Bootstrap ──────────────────────────────────────────────────────────── |
| 22 |
|
| 23 |
public static function init() { |
| 24 |
add_action( 'rest_api_init', array( __CLASS__, 'register_routes' ) ); |
| 25 |
add_shortcode( 'wpbot_table', array( __CLASS__, 'render_shortcode' ) ); |
| 26 |
} |
| 27 |
|
| 28 |
public static function get_meta_table() { |
| 29 |
global $wpdb; |
| 30 |
return $wpdb->prefix . self::TABLE_META; |
| 31 |
} |
| 32 |
|
| 33 |
/** Prefixed name of the user-data table for a given slug. */ |
| 34 |
public static function get_user_table( $slug ) { |
| 35 |
global $wpdb; |
| 36 |
return $wpdb->prefix . 'wpbot_ut_' . $slug; |
| 37 |
} |
| 38 |
|
| 39 |
// ── REST routes ────────────────────────────────────────────────────────── |
| 40 |
|
| 41 |
public static function register_routes() { |
| 42 |
$perm = array( Plugin::class, 'check_permission' ); |
| 43 |
$perm_public = '__return_true'; // nonce verified inside callback |
| 44 |
|
| 45 |
// Table list + create. |
| 46 |
register_rest_route( 'wpbot-automator/v1', '/tables', array( |
| 47 |
array( 'methods' => 'GET', 'callback' => array( __CLASS__, 'list_tables' ), 'permission_callback' => $perm ), |
| 48 |
array( 'methods' => 'POST', 'callback' => array( __CLASS__, 'create_table' ), 'permission_callback' => $perm ), |
| 49 |
) ); |
| 50 |
|
| 51 |
// Single table delete. |
| 52 |
register_rest_route( 'wpbot-automator/v1', '/tables/(?P<id>\d+)', array( |
| 53 |
array( 'methods' => 'DELETE', 'callback' => array( __CLASS__, 'delete_table' ), 'permission_callback' => $perm ), |
| 54 |
) ); |
| 55 |
|
| 56 |
// Records list + create. |
| 57 |
register_rest_route( 'wpbot-automator/v1', '/tables/(?P<id>\d+)/records', array( |
| 58 |
array( 'methods' => 'GET', 'callback' => array( __CLASS__, 'get_records' ), 'permission_callback' => $perm ), |
| 59 |
array( 'methods' => 'POST', 'callback' => array( __CLASS__, 'create_record' ), 'permission_callback' => $perm ), |
| 60 |
) ); |
| 61 |
|
| 62 |
// Single record update + delete. |
| 63 |
register_rest_route( 'wpbot-automator/v1', '/tables/(?P<id>\d+)/records/(?P<row_id>\d+)', array( |
| 64 |
array( 'methods' => 'PUT', 'callback' => array( __CLASS__, 'update_record' ), 'permission_callback' => $perm ), |
| 65 |
array( 'methods' => 'DELETE', 'callback' => array( __CLASS__, 'delete_record' ), 'permission_callback' => $perm ), |
| 66 |
) ); |
| 67 |
|
| 68 |
// CSV import. |
| 69 |
register_rest_route( 'wpbot-automator/v1', '/tables/(?P<id>\d+)/import', array( |
| 70 |
'methods' => 'POST', |
| 71 |
'callback' => array( __CLASS__, 'import_csv' ), |
| 72 |
'permission_callback' => $perm, |
| 73 |
) ); |
| 74 |
|
| 75 |
// CSV export. |
| 76 |
register_rest_route( 'wpbot-automator/v1', '/tables/(?P<id>\d+)/export', array( |
| 77 |
'methods' => 'GET', |
| 78 |
'callback' => array( __CLASS__, 'export_csv' ), |
| 79 |
'permission_callback' => $perm, |
| 80 |
) ); |
| 81 |
|
| 82 |
// Trigger-workflow button (public — nonce verified inside). |
| 83 |
register_rest_route( 'wpbot-automator/v1', '/tables/(?P<id>\d+)/records/(?P<row_id>\d+)/trigger-workflow', array( |
| 84 |
'methods' => 'POST', |
| 85 |
'callback' => array( __CLASS__, 'handle_trigger_button' ), |
| 86 |
'permission_callback' => $perm_public, |
| 87 |
) ); |
| 88 |
} |
| 89 |
|
| 90 |
// ── REST handlers ──────────────────────────────────────────────────────── |
| 91 |
|
| 92 |
/** GET /tables — list all user-defined tables with record counts. */ |
| 93 |
public static function list_tables() { |
| 94 |
global $wpdb; |
| 95 |
$meta = self::get_meta_table(); |
| 96 |
$rows = $wpdb->get_results( "SELECT * FROM {$meta} ORDER BY created_at DESC", ARRAY_A ); |
| 97 |
|
| 98 |
foreach ( $rows as &$row ) { |
| 99 |
$row['columns'] = json_decode( $row['columns_json'], true ) ?: array(); |
| 100 |
$ut = self::get_user_table( $row['slug'] ); |
| 101 |
$row['record_count'] = (int) $wpdb->get_var( "SELECT COUNT(*) FROM `{$ut}`" ); |
| 102 |
unset( $row['columns_json'] ); |
| 103 |
} |
| 104 |
unset( $row ); |
| 105 |
|
| 106 |
return rest_ensure_response( $rows ); |
| 107 |
} |
| 108 |
|
| 109 |
/** POST /tables — create a new table definition + MySQL table. */ |
| 110 |
public static function create_table( \WP_REST_Request $request ) { |
| 111 |
global $wpdb; |
| 112 |
|
| 113 |
$name = sanitize_text_field( $request->get_param( 'name' ) ); |
| 114 |
$columns = $request->get_param( 'columns' ); |
| 115 |
|
| 116 |
if ( empty( $name ) ) { |
| 117 |
return new \WP_Error( 'missing_name', 'Table name is required.', array( 'status' => 400 ) ); |
| 118 |
} |
| 119 |
if ( empty( $columns ) || ! is_array( $columns ) ) { |
| 120 |
return new \WP_Error( 'missing_columns', 'At least one column is required.', array( 'status' => 400 ) ); |
| 121 |
} |
| 122 |
|
| 123 |
// Sanitize and deduplicate column slugs. |
| 124 |
$clean_cols = array(); |
| 125 |
$seen_slugs = array(); |
| 126 |
foreach ( $columns as $col ) { |
| 127 |
$col_name = sanitize_text_field( $col['name'] ?? '' ); |
| 128 |
if ( empty( $col_name ) ) { |
| 129 |
continue; |
| 130 |
} |
| 131 |
$slug = self::to_column_slug( $col_name ); |
| 132 |
$base = $slug; |
| 133 |
$n = 1; |
| 134 |
while ( in_array( $slug, $seen_slugs, true ) ) { |
| 135 |
$slug = $base . '_' . ( ++$n ); |
| 136 |
} |
| 137 |
$seen_slugs[] = $slug; |
| 138 |
$clean_cols[] = array( |
| 139 |
'slug' => $slug, |
| 140 |
'name' => $col_name, |
| 141 |
'type' => self::sanitize_col_type( $col['type'] ?? 'text' ), |
| 142 |
'required' => ! empty( $col['required'] ), |
| 143 |
); |
| 144 |
} |
| 145 |
|
| 146 |
if ( empty( $clean_cols ) ) { |
| 147 |
return new \WP_Error( 'invalid_columns', 'No valid columns provided.', array( 'status' => 400 ) ); |
| 148 |
} |
| 149 |
|
| 150 |
// Generate unique table slug. |
| 151 |
$base_slug = self::to_table_slug( $name ); |
| 152 |
$table_slug = $base_slug; |
| 153 |
$n = 1; |
| 154 |
$meta = self::get_meta_table(); |
| 155 |
while ( $wpdb->get_var( $wpdb->prepare( "SELECT id FROM {$meta} WHERE slug = %s", $table_slug ) ) ) { |
| 156 |
$table_slug = $base_slug . '_' . ( ++$n ); |
| 157 |
} |
| 158 |
|
| 159 |
// Create the MySQL data table. |
| 160 |
$result = self::create_mysql_table( $table_slug, $clean_cols ); |
| 161 |
if ( is_wp_error( $result ) ) { |
| 162 |
return $result; |
| 163 |
} |
| 164 |
|
| 165 |
// Store meta. |
| 166 |
$inserted = $wpdb->insert( $meta, array( |
| 167 |
'name' => $name, |
| 168 |
'slug' => $table_slug, |
| 169 |
'columns_json' => wp_json_encode( $clean_cols ), |
| 170 |
'created_at' => current_time( 'mysql' ), |
| 171 |
) ); |
| 172 |
|
| 173 |
if ( ! $inserted ) { |
| 174 |
self::drop_mysql_table( $table_slug ); |
| 175 |
return new \WP_Error( 'db_error', 'Failed to save table definition.', array( 'status' => 500 ) ); |
| 176 |
} |
| 177 |
|
| 178 |
return rest_ensure_response( array( |
| 179 |
'success' => true, |
| 180 |
'id' => $wpdb->insert_id, |
| 181 |
'slug' => $table_slug, |
| 182 |
'columns' => $clean_cols, |
| 183 |
) ); |
| 184 |
} |
| 185 |
|
| 186 |
/** DELETE /tables/{id} — remove table definition + MySQL table. */ |
| 187 |
public static function delete_table( \WP_REST_Request $request ) { |
| 188 |
global $wpdb; |
| 189 |
$id = absint( $request->get_param( 'id' ) ); |
| 190 |
$meta = self::get_meta_table(); |
| 191 |
$row = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$meta} WHERE id = %d", $id ), ARRAY_A ); |
| 192 |
|
| 193 |
if ( ! $row ) { |
| 194 |
return new \WP_Error( 'not_found', 'Table not found.', array( 'status' => 404 ) ); |
| 195 |
} |
| 196 |
|
| 197 |
self::drop_mysql_table( $row['slug'] ); |
| 198 |
$wpdb->delete( $meta, array( 'id' => $id ) ); |
| 199 |
|
| 200 |
return rest_ensure_response( array( 'success' => true ) ); |
| 201 |
} |
| 202 |
|
| 203 |
/** GET /tables/{id}/records — paginated record list with optional column filter. */ |
| 204 |
public static function get_records( \WP_REST_Request $request ) { |
| 205 |
global $wpdb; |
| 206 |
$table = self::get_table_or_error( absint( $request->get_param( 'id' ) ) ); |
| 207 |
if ( is_wp_error( $table ) ) { |
| 208 |
return $table; |
| 209 |
} |
| 210 |
|
| 211 |
$ut = self::get_user_table( $table['slug'] ); |
| 212 |
$per_page = min( max( absint( $request->get_param( 'per_page' ) ?: 50 ), 1 ), 500 ); |
| 213 |
$page = max( absint( $request->get_param( 'page' ) ?: 1 ), 1 ); |
| 214 |
$offset = ( $page - 1 ) * $per_page; |
| 215 |
$filter_col = sanitize_key( $request->get_param( 'filter_col' ) ?: '' ); |
| 216 |
$filter_val = sanitize_text_field( $request->get_param( 'filter_val' ) ?: '' ); |
| 217 |
|
| 218 |
$where = ''; |
| 219 |
$query_params = array(); |
| 220 |
|
| 221 |
if ( $filter_col && $filter_val !== '' ) { |
| 222 |
$valid_cols = wp_list_pluck( $table['columns'], 'slug' ); |
| 223 |
if ( in_array( $filter_col, $valid_cols, true ) ) { |
| 224 |
$where = 'WHERE `' . esc_sql( $filter_col ) . '` LIKE %s'; |
| 225 |
$query_params[] = '%' . $wpdb->esc_like( $filter_val ) . '%'; |
| 226 |
} |
| 227 |
} |
| 228 |
|
| 229 |
$count_sql = "SELECT COUNT(*) FROM `{$ut}` {$where}"; |
| 230 |
$total = (int) ( $query_params |
| 231 |
? $wpdb->get_var( $wpdb->prepare( $count_sql, ...$query_params ) ) |
| 232 |
: $wpdb->get_var( $count_sql ) ); |
| 233 |
|
| 234 |
$list_params = array_merge( $query_params, array( $per_page, $offset ) ); |
| 235 |
$list_sql = "SELECT * FROM `{$ut}` {$where} ORDER BY id DESC LIMIT %d OFFSET %d"; |
| 236 |
$records = $wpdb->get_results( $wpdb->prepare( $list_sql, ...$list_params ), ARRAY_A ); |
| 237 |
|
| 238 |
return rest_ensure_response( array( |
| 239 |
'records' => $records ?: array(), |
| 240 |
'total' => $total, |
| 241 |
'page' => $page, |
| 242 |
'per_page' => $per_page, |
| 243 |
'total_pages' => $total > 0 ? (int) ceil( $total / $per_page ) : 0, |
| 244 |
) ); |
| 245 |
} |
| 246 |
|
| 247 |
/** POST /tables/{id}/records — insert a new record. */ |
| 248 |
public static function create_record( \WP_REST_Request $request ) { |
| 249 |
global $wpdb; |
| 250 |
$table = self::get_table_or_error( absint( $request->get_param( 'id' ) ) ); |
| 251 |
if ( is_wp_error( $table ) ) { |
| 252 |
return $table; |
| 253 |
} |
| 254 |
|
| 255 |
$ut = self::get_user_table( $table['slug'] ); |
| 256 |
$data = self::extract_record_data( $request->get_param( 'data' ) ?: array(), $table['columns'] ); |
| 257 |
|
| 258 |
if ( empty( $data ) ) { |
| 259 |
return new \WP_Error( 'empty_data', 'No valid fields provided.', array( 'status' => 400 ) ); |
| 260 |
} |
| 261 |
|
| 262 |
$data['created_at'] = current_time( 'mysql' ); |
| 263 |
$data['updated_at'] = current_time( 'mysql' ); |
| 264 |
|
| 265 |
$wpdb->insert( $ut, $data ); |
| 266 |
$new_id = (int) $wpdb->insert_id; |
| 267 |
|
| 268 |
do_action( 'wpbot_table_new_record', absint( $request->get_param( 'id' ) ), $table, $data, $new_id ); |
| 269 |
|
| 270 |
return rest_ensure_response( array( 'success' => true, 'id' => $new_id ) ); |
| 271 |
} |
| 272 |
|
| 273 |
/** PUT /tables/{id}/records/{row_id} — update an existing record. */ |
| 274 |
public static function update_record( \WP_REST_Request $request ) { |
| 275 |
global $wpdb; |
| 276 |
$table = self::get_table_or_error( absint( $request->get_param( 'id' ) ) ); |
| 277 |
if ( is_wp_error( $table ) ) { |
| 278 |
return $table; |
| 279 |
} |
| 280 |
|
| 281 |
$ut = self::get_user_table( $table['slug'] ); |
| 282 |
$row_id = absint( $request->get_param( 'row_id' ) ); |
| 283 |
$data = self::extract_record_data( $request->get_param( 'data' ) ?: array(), $table['columns'] ); |
| 284 |
|
| 285 |
if ( empty( $data ) ) { |
| 286 |
return new \WP_Error( 'empty_data', 'No valid fields provided.', array( 'status' => 400 ) ); |
| 287 |
} |
| 288 |
|
| 289 |
$data['updated_at'] = current_time( 'mysql' ); |
| 290 |
|
| 291 |
$old_record = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM `{$ut}` WHERE id = %d", $row_id ), ARRAY_A ); |
| 292 |
$wpdb->update( $ut, $data, array( 'id' => $row_id ) ); |
| 293 |
|
| 294 |
$table_id = absint( $request->get_param( 'id' ) ); |
| 295 |
|
| 296 |
// Fire per-cell triggers for each changed column. |
| 297 |
if ( is_array( $old_record ) ) { |
| 298 |
foreach ( $data as $col_slug => $new_val ) { |
| 299 |
if ( in_array( $col_slug, array( 'updated_at' ), true ) ) { |
| 300 |
continue; |
| 301 |
} |
| 302 |
$old_val = isset( $old_record[ $col_slug ] ) ? $old_record[ $col_slug ] : null; |
| 303 |
if ( (string) $old_val !== (string) $new_val ) { |
| 304 |
do_action( 'wpbot_table_updated_cell', $table_id, $table, $row_id, $col_slug, $old_val, $new_val ); |
| 305 |
} |
| 306 |
} |
| 307 |
} |
| 308 |
|
| 309 |
do_action( 'wpbot_table_updated_record', $table_id, $table, $data, $row_id, $old_record ); |
| 310 |
|
| 311 |
return rest_ensure_response( array( 'success' => true ) ); |
| 312 |
} |
| 313 |
|
| 314 |
/** DELETE /tables/{id}/records/{row_id} — delete a record. */ |
| 315 |
public static function delete_record( \WP_REST_Request $request ) { |
| 316 |
global $wpdb; |
| 317 |
$table = self::get_table_or_error( absint( $request->get_param( 'id' ) ) ); |
| 318 |
if ( is_wp_error( $table ) ) { |
| 319 |
return $table; |
| 320 |
} |
| 321 |
|
| 322 |
$ut = self::get_user_table( $table['slug'] ); |
| 323 |
$row_id = absint( $request->get_param( 'row_id' ) ); |
| 324 |
$table_id = absint( $request->get_param( 'id' ) ); |
| 325 |
|
| 326 |
$old_record = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM `{$ut}` WHERE id = %d", $row_id ), ARRAY_A ); |
| 327 |
$wpdb->delete( $ut, array( 'id' => $row_id ) ); |
| 328 |
|
| 329 |
do_action( 'wpbot_table_deleted_record', $table_id, $table, $row_id, $old_record ); |
| 330 |
|
| 331 |
return rest_ensure_response( array( 'success' => true ) ); |
| 332 |
} |
| 333 |
|
| 334 |
/** POST /tables/{id}/records/{row_id}/trigger-workflow — fire from a shortcode button. */ |
| 335 |
public static function handle_trigger_button( \WP_REST_Request $request ) { |
| 336 |
$table_id = absint( $request->get_param( 'id' ) ); |
| 337 |
$row_id = absint( $request->get_param( 'row_id' ) ); |
| 338 |
|
| 339 |
// Verify nonce (embedded by the shortcode; valid for both logged-in and guest visitors). |
| 340 |
$nonce = $request->get_param( 'nonce' ); |
| 341 |
if ( ! $nonce || ! wp_verify_nonce( $nonce, 'wpbot_table_trigger_' . $table_id ) ) { |
| 342 |
return new \WP_Error( 'invalid_nonce', 'Security check failed.', array( 'status' => 403 ) ); |
| 343 |
} |
| 344 |
|
| 345 |
$table = self::get_table_or_error( $table_id ); |
| 346 |
if ( is_wp_error( $table ) ) { |
| 347 |
return $table; |
| 348 |
} |
| 349 |
|
| 350 |
global $wpdb; |
| 351 |
$ut = self::get_user_table( $table['slug'] ); |
| 352 |
$row_data = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM `{$ut}` WHERE id = %d", $row_id ), ARRAY_A ); |
| 353 |
|
| 354 |
if ( ! $row_data ) { |
| 355 |
return new \WP_Error( 'not_found', 'Record not found.', array( 'status' => 404 ) ); |
| 356 |
} |
| 357 |
|
| 358 |
do_action( 'wpbot_table_trigger_button', $table_id, $table, $row_id, $row_data ); |
| 359 |
|
| 360 |
return rest_ensure_response( array( 'success' => true ) ); |
| 361 |
} |
| 362 |
|
| 363 |
/** |
| 364 |
* POST /tables/{id}/import — bulk insert rows from a CSV. |
| 365 |
* |
| 366 |
* Expects JSON body: { rows: [["val1","val2",...], ...], headers: ["col1","col2",...] } |
| 367 |
* Headers are matched to column slugs by name (case-insensitive). |
| 368 |
*/ |
| 369 |
public static function import_csv( \WP_REST_Request $request ) { |
| 370 |
global $wpdb; |
| 371 |
$table = self::get_table_or_error( absint( $request->get_param( 'id' ) ) ); |
| 372 |
if ( is_wp_error( $table ) ) { |
| 373 |
return $table; |
| 374 |
} |
| 375 |
|
| 376 |
$headers = $request->get_param( 'headers' ); |
| 377 |
$rows = $request->get_param( 'rows' ); |
| 378 |
|
| 379 |
if ( ! is_array( $headers ) || ! is_array( $rows ) || empty( $rows ) ) { |
| 380 |
return new \WP_Error( 'invalid_data', 'headers and rows arrays are required.', array( 'status' => 400 ) ); |
| 381 |
} |
| 382 |
|
| 383 |
// Build header → column slug mapping (case-insensitive, also match on slug). |
| 384 |
$col_map = array(); |
| 385 |
foreach ( $table['columns'] as $col ) { |
| 386 |
$col_map[ strtolower( $col['name'] ) ] = $col['slug']; |
| 387 |
$col_map[ strtolower( $col['slug'] ) ] = $col['slug']; |
| 388 |
} |
| 389 |
|
| 390 |
$header_to_slug = array(); |
| 391 |
foreach ( $headers as $idx => $h ) { |
| 392 |
$key = strtolower( trim( $h ) ); |
| 393 |
if ( isset( $col_map[ $key ] ) ) { |
| 394 |
$header_to_slug[ $idx ] = $col_map[ $key ]; |
| 395 |
} |
| 396 |
} |
| 397 |
|
| 398 |
if ( empty( $header_to_slug ) ) { |
| 399 |
return new \WP_Error( 'no_match', 'No CSV headers matched table columns.', array( 'status' => 400 ) ); |
| 400 |
} |
| 401 |
|
| 402 |
$ut = self::get_user_table( $table['slug'] ); |
| 403 |
$now = current_time( 'mysql' ); |
| 404 |
$count = 0; |
| 405 |
|
| 406 |
foreach ( $rows as $row ) { |
| 407 |
if ( ! is_array( $row ) ) { |
| 408 |
continue; |
| 409 |
} |
| 410 |
$data = array( 'created_at' => $now, 'updated_at' => $now ); |
| 411 |
foreach ( $header_to_slug as $idx => $slug ) { |
| 412 |
$data[ $slug ] = isset( $row[ $idx ] ) ? sanitize_text_field( $row[ $idx ] ) : ''; |
| 413 |
} |
| 414 |
if ( $wpdb->insert( $ut, $data ) ) { |
| 415 |
++$count; |
| 416 |
} |
| 417 |
} |
| 418 |
|
| 419 |
return rest_ensure_response( array( 'success' => true, 'imported' => $count ) ); |
| 420 |
} |
| 421 |
|
| 422 |
/** GET /tables/{id}/export — return CSV as a download. */ |
| 423 |
public static function export_csv( \WP_REST_Request $request ) { |
| 424 |
global $wpdb; |
| 425 |
$table = self::get_table_or_error( absint( $request->get_param( 'id' ) ) ); |
| 426 |
if ( is_wp_error( $table ) ) { |
| 427 |
return $table; |
| 428 |
} |
| 429 |
|
| 430 |
$ut = self::get_user_table( $table['slug'] ); |
| 431 |
$records = $wpdb->get_results( "SELECT * FROM `{$ut}` ORDER BY id ASC", ARRAY_A ); |
| 432 |
|
| 433 |
// Build headers from column definitions. |
| 434 |
$headers = array( 'id' ); |
| 435 |
foreach ( $table['columns'] as $col ) { |
| 436 |
$headers[] = $col['name']; |
| 437 |
} |
| 438 |
$headers[] = 'created_at'; |
| 439 |
|
| 440 |
// Build CSV rows. |
| 441 |
$csv_rows = array( $headers ); |
| 442 |
foreach ( $records as $rec ) { |
| 443 |
$row = array( $rec['id'] ?? '' ); |
| 444 |
foreach ( $table['columns'] as $col ) { |
| 445 |
$row[] = $rec[ $col['slug'] ] ?? ''; |
| 446 |
} |
| 447 |
$row[] = $rec['created_at'] ?? ''; |
| 448 |
$csv_rows[] = $row; |
| 449 |
} |
| 450 |
|
| 451 |
// Encode to CSV string. |
| 452 |
ob_start(); |
| 453 |
$fh = fopen( 'php://output', 'w' ); |
| 454 |
foreach ( $csv_rows as $r ) { |
| 455 |
fputcsv( $fh, $r ); |
| 456 |
} |
| 457 |
fclose( $fh ); |
| 458 |
$csv = ob_get_clean(); |
| 459 |
|
| 460 |
return rest_ensure_response( array( 'csv' => $csv, 'filename' => sanitize_title( $table['name'] ) . '.csv' ) ); |
| 461 |
} |
| 462 |
|
| 463 |
// ── Shortcode ──────────────────────────────────────────────────────────── |
| 464 |
|
| 465 |
/** |
| 466 |
* [wpbot_table id="1" filter_col="status" filter_val="active" limit="50"] |
| 467 |
*/ |
| 468 |
public static function render_shortcode( $atts ) { |
| 469 |
global $wpdb; |
| 470 |
|
| 471 |
$atts = shortcode_atts( array( |
| 472 |
'id' => 0, |
| 473 |
'filter_col' => '', |
| 474 |
'filter_val' => '', |
| 475 |
'limit' => 100, |
| 476 |
'title' => 'yes', |
| 477 |
'trigger_button' => 'no', |
| 478 |
'button_label' => 'Trigger Workflow', |
| 479 |
), $atts, 'wpbot_table' ); |
| 480 |
|
| 481 |
$id = absint( $atts['id'] ); |
| 482 |
if ( ! $id ) { |
| 483 |
return '<p style="color:red;">[wpbot_table] error: id is required.</p>'; |
| 484 |
} |
| 485 |
|
| 486 |
$table = self::get_table_or_error( $id ); |
| 487 |
if ( is_wp_error( $table ) ) { |
| 488 |
return '<p style="color:red;">[wpbot_table] error: Table not found.</p>'; |
| 489 |
} |
| 490 |
|
| 491 |
$ut = self::get_user_table( $table['slug'] ); |
| 492 |
$filter_col = sanitize_key( $atts['filter_col'] ); |
| 493 |
$filter_val = sanitize_text_field( $atts['filter_val'] ); |
| 494 |
$limit = min( max( absint( $atts['limit'] ), 1 ), 1000 ); |
| 495 |
$valid_slugs = wp_list_pluck( $table['columns'], 'slug' ); |
| 496 |
|
| 497 |
$where = ''; |
| 498 |
$query_params = array(); |
| 499 |
if ( $filter_col && $filter_val !== '' && in_array( $filter_col, $valid_slugs, true ) ) { |
| 500 |
$where = 'WHERE `' . esc_sql( $filter_col ) . '` LIKE %s'; |
| 501 |
$query_params[] = '%' . $wpdb->esc_like( $filter_val ) . '%'; |
| 502 |
} |
| 503 |
|
| 504 |
$sql = "SELECT * FROM `{$ut}` {$where} ORDER BY id DESC LIMIT %d"; |
| 505 |
$params = array_merge( $query_params, array( $limit ) ); |
| 506 |
$records = $wpdb->get_results( $wpdb->prepare( $sql, ...$params ), ARRAY_A ); |
| 507 |
|
| 508 |
$show_btn = ( 'yes' === $atts['trigger_button'] ); |
| 509 |
$btn_label = esc_html( $atts['button_label'] ); |
| 510 |
$rest_base = esc_url( rest_url( 'wpbot-automator/v1/tables/' . $id . '/records' ) ); |
| 511 |
$trigger_nonce = $show_btn ? wp_create_nonce( 'wpbot_table_trigger_' . $id ) : ''; |
| 512 |
|
| 513 |
// Build HTML. |
| 514 |
ob_start(); |
| 515 |
?> |
| 516 |
<div class="wpbot-table-wrap" style="overflow-x:auto;font-family:inherit;"> |
| 517 |
<?php if ( 'yes' === $atts['title'] ) : ?> |
| 518 |
<h3 style="margin-bottom:10px;"><?php echo esc_html( $table['name'] ); ?></h3> |
| 519 |
<?php endif; ?> |
| 520 |
<?php if ( $show_btn ) : ?> |
| 521 |
<script> |
| 522 |
function wpbotTrigger(rowId, btn) { |
| 523 |
btn.disabled = true; |
| 524 |
var orig = btn.textContent; |
| 525 |
btn.textContent = '…'; |
| 526 |
fetch('<?php echo $rest_base; ?>/' + rowId + '/trigger-workflow', { |
| 527 |
method: 'POST', |
| 528 |
headers: { 'Content-Type': 'application/json' }, |
| 529 |
body: JSON.stringify({ nonce: '<?php echo esc_js( $trigger_nonce ); ?>' }) |
| 530 |
}).then(function(r){ return r.json(); }).then(function(d){ |
| 531 |
btn.textContent = d.success ? '✓' : '✗'; |
| 532 |
setTimeout(function(){ btn.disabled = false; btn.textContent = orig; }, 2500); |
| 533 |
}).catch(function(){ |
| 534 |
btn.textContent = '✗'; |
| 535 |
setTimeout(function(){ btn.disabled = false; btn.textContent = orig; }, 2500); |
| 536 |
}); |
| 537 |
} |
| 538 |
</script> |
| 539 |
<?php endif; ?> |
| 540 |
<?php if ( empty( $records ) ) : ?> |
| 541 |
<p style="color:#666;">No records found.</p> |
| 542 |
<?php else : ?> |
| 543 |
<table style="width:100%;border-collapse:collapse;font-size:14px;"> |
| 544 |
<thead> |
| 545 |
<tr> |
| 546 |
<?php foreach ( $table['columns'] as $col ) : ?> |
| 547 |
<th style="text-align:left;padding:8px 12px;background:#f5f5f5;border:1px solid #ddd;"> |
| 548 |
<?php echo esc_html( $col['name'] ); ?> |
| 549 |
</th> |
| 550 |
<?php endforeach; ?> |
| 551 |
<?php if ( $show_btn ) : ?> |
| 552 |
<th style="text-align:left;padding:8px 12px;background:#f5f5f5;border:1px solid #ddd;"></th> |
| 553 |
<?php endif; ?> |
| 554 |
</tr> |
| 555 |
</thead> |
| 556 |
<tbody> |
| 557 |
<?php foreach ( $records as $rec ) : ?> |
| 558 |
<tr> |
| 559 |
<?php foreach ( $table['columns'] as $col ) : ?> |
| 560 |
<td style="padding:8px 12px;border:1px solid #eee;vertical-align:top;"> |
| 561 |
<?php echo esc_html( $rec[ $col['slug'] ] ?? '' ); ?> |
| 562 |
</td> |
| 563 |
<?php endforeach; ?> |
| 564 |
<?php if ( $show_btn ) : ?> |
| 565 |
<td style="padding:6px 12px;border:1px solid #eee;text-align:center;"> |
| 566 |
<button |
| 567 |
onclick="wpbotTrigger(<?php echo (int) $rec['id']; ?>, this)" |
| 568 |
style="background:#3b82f6;color:#fff;border:none;padding:5px 12px;border-radius:4px;cursor:pointer;font-size:12px;"> |
| 569 |
<?php echo $btn_label; ?> |
| 570 |
</button> |
| 571 |
</td> |
| 572 |
<?php endif; ?> |
| 573 |
</tr> |
| 574 |
<?php endforeach; ?> |
| 575 |
</tbody> |
| 576 |
</table> |
| 577 |
<?php endif; ?> |
| 578 |
</div> |
| 579 |
<?php |
| 580 |
return ob_get_clean(); |
| 581 |
} |
| 582 |
|
| 583 |
// ── Public helpers (used by Tables_Actions) ───────────────────────────── |
| 584 |
|
| 585 |
/** |
| 586 |
* Load table meta by ID, returning decoded array or null. |
| 587 |
* Used by Tables_Actions and shortcode. |
| 588 |
*/ |
| 589 |
public static function get_table_meta_by_id( $id ) { |
| 590 |
global $wpdb; |
| 591 |
$meta = self::get_meta_table(); |
| 592 |
$row = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$meta} WHERE id = %d", absint( $id ) ), ARRAY_A ); |
| 593 |
if ( ! $row ) { |
| 594 |
return null; |
| 595 |
} |
| 596 |
$row['columns'] = json_decode( $row['columns_json'], true ) ?: array(); |
| 597 |
unset( $row['columns_json'] ); |
| 598 |
return $row; |
| 599 |
} |
| 600 |
|
| 601 |
/** |
| 602 |
* Public alias for extract_record_data — called by Tables_Actions. |
| 603 |
*/ |
| 604 |
public static function extract_record_data_public( array $raw, array $columns ) { |
| 605 |
return self::extract_record_data( $raw, $columns ); |
| 606 |
} |
| 607 |
|
| 608 |
// ── Private helpers ────────────────────────────────────────────────────── |
| 609 |
|
| 610 |
/** Load table meta (with decoded columns) or return WP_Error. */ |
| 611 |
private static function get_table_or_error( $id ) { |
| 612 |
global $wpdb; |
| 613 |
$meta = self::get_meta_table(); |
| 614 |
$row = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$meta} WHERE id = %d", $id ), ARRAY_A ); |
| 615 |
|
| 616 |
if ( ! $row ) { |
| 617 |
return new \WP_Error( 'not_found', 'Table not found.', array( 'status' => 404 ) ); |
| 618 |
} |
| 619 |
|
| 620 |
$row['columns'] = json_decode( $row['columns_json'], true ) ?: array(); |
| 621 |
unset( $row['columns_json'] ); |
| 622 |
return $row; |
| 623 |
} |
| 624 |
|
| 625 |
/** Filter incoming record data to only valid column slugs, sanitizing values. */ |
| 626 |
private static function extract_record_data( array $raw, array $columns ) { |
| 627 |
$valid = array(); |
| 628 |
foreach ( $columns as $col ) { |
| 629 |
if ( array_key_exists( $col['slug'], $raw ) ) { |
| 630 |
$val = $raw[ $col['slug'] ]; |
| 631 |
switch ( $col['type'] ) { |
| 632 |
case 'number': |
| 633 |
$valid[ $col['slug'] ] = is_numeric( $val ) ? (float) $val : null; |
| 634 |
break; |
| 635 |
case 'boolean': |
| 636 |
$valid[ $col['slug'] ] = ( $val === true || $val === '1' || $val === 1 ) ? 1 : 0; |
| 637 |
break; |
| 638 |
default: |
| 639 |
$valid[ $col['slug'] ] = sanitize_textarea_field( (string) $val ); |
| 640 |
} |
| 641 |
} |
| 642 |
} |
| 643 |
return $valid; |
| 644 |
} |
| 645 |
|
| 646 |
/** Create the MySQL data table using dbDelta. */ |
| 647 |
private static function create_mysql_table( $slug, array $columns ) { |
| 648 |
global $wpdb; |
| 649 |
$charset_collate = $wpdb->get_charset_collate(); |
| 650 |
$table = self::get_user_table( $slug ); |
| 651 |
|
| 652 |
$col_defs = array(); |
| 653 |
foreach ( $columns as $col ) { |
| 654 |
$sql_type = self::column_type_to_sql( $col['type'] ); |
| 655 |
$not_null = $col['required'] ? 'NOT NULL' : 'NULL'; |
| 656 |
$default = ( 'boolean' === $col['type'] ) ? '' : "DEFAULT NULL"; |
| 657 |
$col_defs[] = "`{$col['slug']}` {$sql_type} {$not_null} {$default}"; |
| 658 |
} |
| 659 |
|
| 660 |
$cols_sql = implode( ",\n\t\t\t", $col_defs ); |
| 661 |
$sql = "CREATE TABLE IF NOT EXISTS `{$table}` ( |
| 662 |
id bigint(20) unsigned NOT NULL AUTO_INCREMENT, |
| 663 |
{$cols_sql}, |
| 664 |
created_at datetime DEFAULT CURRENT_TIMESTAMP, |
| 665 |
updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, |
| 666 |
PRIMARY KEY (id) |
| 667 |
) {$charset_collate};"; |
| 668 |
|
| 669 |
require_once ABSPATH . 'wp-admin/includes/upgrade.php'; |
| 670 |
$result = dbDelta( $sql ); |
| 671 |
|
| 672 |
// Verify the table was created. |
| 673 |
if ( ! $wpdb->get_var( "SHOW TABLES LIKE '{$table}'" ) ) { |
| 674 |
return new \WP_Error( 'create_failed', 'Could not create database table: ' . $wpdb->last_error, array( 'status' => 500 ) ); |
| 675 |
} |
| 676 |
|
| 677 |
return true; |
| 678 |
} |
| 679 |
|
| 680 |
/** DROP the MySQL data table. */ |
| 681 |
private static function drop_mysql_table( $slug ) { |
| 682 |
global $wpdb; |
| 683 |
$table = self::get_user_table( $slug ); |
| 684 |
$wpdb->query( "DROP TABLE IF EXISTS `{$table}`" ); |
| 685 |
} |
| 686 |
|
| 687 |
/** Map a UI column type to a MySQL column type. */ |
| 688 |
private static function column_type_to_sql( $type ) { |
| 689 |
$map = array( |
| 690 |
'text' => 'VARCHAR(500)', |
| 691 |
'textarea' => 'TEXT', |
| 692 |
'number' => 'DECIMAL(18,4)', |
| 693 |
'email' => 'VARCHAR(255)', |
| 694 |
'url' => 'VARCHAR(500)', |
| 695 |
'date' => 'DATE', |
| 696 |
'datetime' => 'DATETIME', |
| 697 |
'boolean' => 'TINYINT(1) NOT NULL DEFAULT 0', |
| 698 |
); |
| 699 |
return $map[ $type ] ?? 'VARCHAR(500)'; |
| 700 |
} |
| 701 |
|
| 702 |
/** Sanitize column type to one of the known values. */ |
| 703 |
private static function sanitize_col_type( $type ) { |
| 704 |
$valid = array( 'text', 'textarea', 'number', 'email', 'url', 'date', 'datetime', 'boolean' ); |
| 705 |
return in_array( $type, $valid, true ) ? $type : 'text'; |
| 706 |
} |
| 707 |
|
| 708 |
/** Convert a human name to a safe SQL column slug. */ |
| 709 |
private static function to_column_slug( $name ) { |
| 710 |
$slug = strtolower( trim( $name ) ); |
| 711 |
$slug = preg_replace( '/[^a-z0-9]+/', '_', $slug ); |
| 712 |
$slug = trim( $slug, '_' ); |
| 713 |
$slug = substr( $slug, 0, 50 ); |
| 714 |
if ( empty( $slug ) || is_numeric( $slug[0] ) ) { |
| 715 |
$slug = 'col_' . $slug; |
| 716 |
} |
| 717 |
return $slug ?: 'col_' . uniqid(); |
| 718 |
} |
| 719 |
|
| 720 |
/** Convert a human name to a safe SQL table slug. */ |
| 721 |
private static function to_table_slug( $name ) { |
| 722 |
$slug = strtolower( trim( $name ) ); |
| 723 |
$slug = preg_replace( '/[^a-z0-9]+/', '_', $slug ); |
| 724 |
$slug = trim( $slug, '_' ); |
| 725 |
$slug = substr( $slug, 0, 30 ); |
| 726 |
return $slug ?: 'table_' . time(); |
| 727 |
} |
| 728 |
} |
| 729 |
|
