wp-data-access / WPDataProjects / Project / WPDP_Project_Design_Table_Model.php

WPDP_Project_Design_Table_Model.php in WP Data Access – App Builder for Tables, Forms, Charts, Maps & Dashboards 2.0.13, at WPDataProjects/Project/WPDP_Project_Design_Table_Model.php

444 lines 13.8 KB

Raw Download Zip

1	<?php
2	/**
3	* Suppress "error - 0 - No summary was found for this file" on phpdoc generation
4	*
5	* @package WPDataProjects
6	*/
7
8	namespace WPDataProjects\Project {
9
10	use WPDataAccess\Design_Table\WPDA_Design_Table_Model;
11	use WPDataAccess\Utilities\WPDA_Message_Box;
12
13	/**
14	* Class WPDP_Project_Design_Table_Model
15	*
16	* @package WPDataProjects\Project
17	* @author Peter Schulz
18	* @since 2.0.0
19	*/
20	class WPDP_Project_Design_Table_Model extends WPDA_Design_Table_Model {
21
22	/**
23	* @var
24	*/
25	protected $action2;
26
27	/**
28	* WPDP_Project_Design_Table_Model constructor.
29	*/
30	public function __construct() {
31	parent::__construct();
32
33	$this->wpda_table_name_original = $this->wpda_table_name;
34	if ( isset( $_REQUEST['action2'] ) ) {
35	$this->action2 = sanitize_text_field( wp_unslash( $_REQUEST['action2'] ) );
36	}
37	}
38
39	/**
40	* @return array
41	*/
42	public function validate() {
43	$structure_messages = parent::validate();
44
45	if ( isset( $this->wpda_table_design->tableform_column_options ) ) {
46	if ( count( $this->wpda_table_design->table ) !== count( $this->wpda_table_design->tableform_column_options ) ) {
47	$structure_messages[] = [ 'ERR', 'Invalid structure (run reconcile)' ];
48	}
49	}
50
51	return $structure_messages;
52	}
53
54	/**
55	*
56	*/
57	public function prepare_insert() {
58	parent::prepare_insert();
59	}
60
61	/**
62	*
63	*/
64	public function prepare_update() {
65	parent::prepare_update();
66
67	switch ( $this->action2 ) {
68	case 'relation':
69	$this->prepare_update_relation();
70	break;
71	case 'listtable':
72	$this->prepare_update_listtable();
73	break;
74	case 'tableform':
75	$this->prepare_update_tableform();
76	break;
77	case 'tableinfo':
78	$this->prepare_update_tableinfo();
79	}
80	}
81
82	/**
83	*
84	*/
85	protected function prepare_update_relation() {
86	unset( $this->wpda_table_design->relationships );
87	if ( isset( $_REQUEST['row_num'] ) ) {
88	$no_columns = count( $_REQUEST['row_num'] );
89	if (
90	isset( $_REQUEST['relation_type'] ) &&
91	$no_columns === count( $_REQUEST['relation_type'] ) &&
92	isset( $_REQUEST['source_column_name'] ) &&
93	$no_columns === count( $_REQUEST['source_column_name'] ) &&
94	isset( $_REQUEST['target_table_name'] ) &&
95	$no_columns === count( $_REQUEST['target_table_name'] ) &&
96	isset( $_REQUEST['target_column_name'] ) &&
97	$no_columns === count( $_REQUEST['target_column_name'] )
98	) {
99	for ( $i = 0; $i < $no_columns; $i++ ) {
100	$relation_type = sanitize_text_field( wp_unslash( $_REQUEST['relation_type'][ $i ] ) );
101	$source_column_name = sanitize_text_field( wp_unslash( $_REQUEST['source_column_name'][ $i ] ) );
102	$target_table_name = sanitize_text_field( wp_unslash( $_REQUEST['target_table_name'][ $i ] ) );
103	$target_column_name = sanitize_text_field( wp_unslash( $_REQUEST['target_column_name'][ $i ] ) );
104
105	if ( 'nm' === $relation_type ) {
106	$relation_table_name = sanitize_text_field( wp_unslash( $_REQUEST[ 'relation_table_name_' . $i ] ) );
107	if ( trim( $relation_table_name ) === '' ) {
108	$msg = new WPDA_Message_Box(
109	[
110	'message_text' => __( 'Invalid array: missing required fields', 'wp-data-access' ),
111	'message_type' => 'error',
112	'message_is_dismissible' => false,
113	]
114	);
115	$msg->box();
116	return;
117	}
118	}
119
120	if (
121	trim( $relation_type ) !== '' &&
122	trim( $source_column_name ) !== '' &&
123	trim( $target_table_name ) !== '' &&
124	trim( $target_column_name ) !== ''
125	) {
126	$source_column_name_array = [];
127	$target_column_name_array = [];
128
129	array_push( $source_column_name_array, $source_column_name );
130	array_push( $target_column_name_array, $target_column_name );
131
132	if ( isset( $_REQUEST['num_source_column_name'][ $i ] ) ) {
133	$num_source_column_name = sanitize_text_field( wp_unslash( $_REQUEST['num_source_column_name'][ $i ] ) );
134	if ( is_numeric( $num_source_column_name ) ) {
135	for ( $j = 1; $j <= $num_source_column_name; $j++ ) {
136	if (
137	isset( $_REQUEST[ 'source_column_name_' . $i . '_' . $j ] ) &&
138	isset( $_REQUEST[ 'target_column_name_' . $i . '_' . $j ] )
139	) {
140	array_push( $source_column_name_array, sanitize_text_field( wp_unslash( $_REQUEST[ 'source_column_name_' . $i . '_' . $j ] ) ) );
141	array_push( $target_column_name_array, sanitize_text_field( wp_unslash( $_REQUEST[ 'target_column_name_' . $i . '_' . $j ] ) ) );
142	}
143	}
144	}
145	}
146
147	$this->wpda_table_design->relationships[ $i ]['relation_type'] = $relation_type;
148	$this->wpda_table_design->relationships[ $i ]['source_column_name'] = $source_column_name_array;
149	$this->wpda_table_design->relationships[ $i ]['target_table_name'] = $target_table_name;
150	$this->wpda_table_design->relationships[ $i ]['target_column_name'] = $target_column_name_array;
151	if ( 'nm' === $relation_type ) {
152	$this->wpda_table_design->relationships[ $i ]['relation_table_name'] = $relation_table_name;
153	}
154	}
155	}
156	} else {
157	$msg = new WPDA_Message_Box(
158	[
159	'message_text' => __( 'Invalid array: missing required fields', 'wp-data-access' ),
160	'message_type' => 'error',
161	'message_is_dismissible' => false,
162	]
163	);
164	$msg->box();
165	}
166	}
167	}
168
169	/**
170	*
171	*/
172	protected function prepare_update_listtable() {
173	$column_options = $this->get_column_options_from_request();
174	if ( null !== $column_options ) {
175	$this->wpda_table_design->listtable_column_options = $column_options;
176	}
177	}
178
179	/**
180	*
181	*/
182	protected function prepare_update_tableform() {
183	$column_options = $this->get_column_options_from_request();
184	if ( null !== $column_options ) {
185	$this->wpda_table_design->tableform_column_options = $column_options;
186	}
187	}
188
189	/**
190	*
191	*/
192	protected function prepare_update_tableinfo() {
193	if ( isset( $_REQUEST['tab_label'] ) ) {
194	$this->wpda_table_design->tableinfo = [
195	'tab_label' => sanitize_text_field( wp_unslash( $_REQUEST['tab_label'] ) ),
196	];
197	}
198	}
199
200	/**
201	* @return array\|null
202	*/
203	protected function get_column_options_from_request() {
204	if ( isset( $_REQUEST['list_item_name'] ) ) {
205	$tableform_column_options = [];
206	foreach ( $_REQUEST['list_item_name'] as $column_name ) {
207	$tableform_column_options[] = [
208	'column_name' => $column_name,
209	'label' => isset( $_REQUEST[ $column_name ] ) ?
210	sanitize_text_field( wp_unslash( $_REQUEST[ $column_name ] ) ) :
211	ucfirst( str_replace( '_', ' ', $column_name ) ),
212	'show' => isset( $_REQUEST["{$column_name}_show"] ) ? 'on' : 'off',
213	'lookup' => isset( $_REQUEST["{$column_name}_lookup"] ) ?
214	sanitize_text_field( wp_unslash( $_REQUEST["{$column_name}_lookup" ] ) ) :
215	false,
216	];
217	}
218	return $tableform_column_options;
219	} else {
220	return null;
221	}
222	}
223
224	/**
225	* @param $table_structure
226	* @param $param_keep_options
227	* @return mixed
228	*/
229	public function reconcile(
230	$table_structure,
231	$param_keep_options
232	) {
233	if ( 1 === $this->query( $this->wpda_table_name_original === $this->wpda_table_name ? null : $this->wpda_table_name_original ) ) {
234	// Table definition.
235	$this->wpda_table_design->design_mode = $table_structure['design_mode'];
236	$this->wpda_table_design->engine = $table_structure['engine'];
237	$this->wpda_table_design->collation = $table_structure['collation'];
238	// Table columns.
239	$this->wpda_table_design->table = $table_structure['table'];
240	// Table indexes.
241	$this->wpda_table_design->indexes = $table_structure['indexes'];
242
243	if ( 'on' !== $param_keep_options ) {
244	// Clear arrays.
245	$this->wpda_table_design->listtable_column_options = [];
246	$this->wpda_table_design->tableform_column_options = [];
247	$this->wpda_table_design->tableinfo = [];
248	} else {
249	// Remove non existing columns from arrays.
250	$new_listtable_column_options = [];
251	foreach ( $this->wpda_table_design->listtable_column_options as $listtable_column_option ) {
252	$column_found = false;
253	foreach ( $this->wpda_table_design->table as $table_column ) {
254	if ( $table_column->column_name === $listtable_column_option->column_name ) {
255	$column_found = true;
256	}
257	}
258	if ( $column_found ) {
259	// Add only column to array that were found in the table definition.
260	array_push( $new_listtable_column_options, $listtable_column_option );
261	}
262	}
263	$this->wpda_table_design->listtable_column_options = $new_listtable_column_options;
264	$new_tableform_column_options = [];
265	foreach ( $this->wpda_table_design->tableform_column_options as $tableform_column_option ) {
266	$column_found = false;
267	foreach ( $this->wpda_table_design->table as $table_column ) {
268	if ( $table_column->column_name === $tableform_column_option->column_name ) {
269	$column_found = true;
270	}
271	}
272	if ( $column_found ) {
273	// Add only column to array that were found in the table definition.
274	array_push( $new_tableform_column_options, $tableform_column_option );
275	}
276	}
277	$this->wpda_table_design->tableform_column_options = $new_tableform_column_options;
278	}
279
280	foreach ( $this->wpda_table_design->table as $column ) {
281	if ( 'on' !== $param_keep_options ) {
282	// Every column must be added to array.
283	$this->reconcile_add_column( $column );
284	} else {
285	// Add only new columns to array.
286	$column_found = false;
287	foreach ( $this->wpda_table_design->listtable_column_options as $listtable_column_option ) {
288	if ( isset( $column->column_name ) && isset( $listtable_column_option->column_name ) ) {
289	if ( $column->column_name === $listtable_column_option->column_name ) {
290	$column_found = true;
291	break;
292	}
293	}
294	}
295	if ( ! $column_found ) {
296	// Add column to both arrays.
297	$this->reconcile_add_column( $column );
298	}
299	}
300	}
301	}
302	global $wpdb;
303	return
304	$wpdb->update(
305	$this->table_name,
306	[
307	'wpda_table_name' => $this->wpda_table_name,
308	'wpda_table_design' => json_encode( $this->wpda_table_design ),
309	],
310	[
311	'wpda_table_name' => $this->wpda_table_name_original === $this->wpda_table_name ? $this->wpda_table_name : $this->wpda_table_name_original,
312	]
313	);
314	}
315
316	/**
317	* @param $column
318	*/
319	private function reconcile_add_column( $column ) {
320	$this->wpda_table_design->listtable_column_options[] =
321	[
322	"column_name" => $column->column_name,
323	"label" => static::get_column_label( $column->column_name ),
324	"show" => "on",
325	];
326	$this->wpda_table_design->tableform_column_options[] =
327	[
328	"column_name" => $column->column_name,
329	"label" => static::get_column_label( $column->column_name ),
330	"show" => "on",
331	];
332	}
333
334	/**
335	* @return string
336	*/
337	public static function get_design_table_name() {
338	global $wpdb;
339
340	return $wpdb->prefix . 'wpdp_table';
341	}
342
343	/**
344	* @param $table_name
345	* @param $label_type
346	* @return array\|null
347	*/
348	public static function get_column_options( $table_name, $label_type ) {
349	global $wpdb;
350
351	$query = $wpdb->prepare(
352	'
353	SELECT wpda_table_design
354	FROM ' . static::get_design_table_name() . '
355	WHERE wpda_table_name = %s
356	',
357	[
358	$table_name,
359	]
360	);
361	$table_json = $wpdb->get_results( $query, 'ARRAY_A' ); // WPCS: unprepared SQL OK; db call ok; no-cache ok.
362	if ( 1 === $wpdb->num_rows ) {
363	if ( isset( $table_json[0]['wpda_table_design'] ) ) {
364	$table_obj = json_decode( $table_json[0]['wpda_table_design'] );
365	if ( 'tableform' === $label_type && isset( $table_obj->tableform_column_options ) ) {
366	return $table_obj->tableform_column_options;
367	}
368	if ( 'listtable' === $label_type && isset( $table_obj->listtable_column_options ) ) {
369	return $table_obj->listtable_column_options;
370	}
371	if ( 'relationships' === $label_type && isset( $table_obj->relationships ) ) {
372	return [
373	'table' => $table_obj->table,
374	'tableinfo' => isset( $table_obj->tableinfo ) ? $table_obj->tableinfo : null,
375	'relationships' => $table_obj->relationships,
376	];
377	}
378	if ( 'tableinfo' === $label_type ) {
379	return isset( $table_obj->tableinfo ) ? $table_obj->tableinfo : null;
380	}
381	return null;
382	} else {
383	return null;
384	}
385	} else {
386	return null;
387	}
388
389	}
390
391	/**
392	* @param $wpda_table_name
393	* @param $wpda_table_design
394	* @return bool
395	*/
396	public static function insert_reverse_engineered( $wpda_table_name, $wpda_table_design ) {
397	global $wpdb;
398
399	$table_name = static::get_design_table_name();
400
401	$wpda_table_design['table_type'] = SELF::get_table_type( $wpda_table_name );
402
403	$wpda_table_design['listtable_column_options'] = [];
404	$wpda_table_design['tableform_column_options'] = [];
405
406	foreach ( $wpda_table_design['table'] as $column ) {
407	$wpda_table_design['listtable_column_options'][] =
408	[
409	"column_name" => $column->column_name,
410	"label" => static::get_column_label( $column->column_name ),
411	"show" => "on",
412	];
413	$wpda_table_design['tableform_column_options'][] =
414	[
415	"column_name" => $column->column_name,
416	"label" => static::get_column_label( $column->column_name ),
417	"show" => "on",
418	];
419	}
420
421	return
422	(
423	1 === $wpdb->insert(
424	$table_name,
425	[
426	'wpda_table_name' => $wpda_table_name,
427	'wpda_table_design' => json_encode( $wpda_table_design ),
428	]
429	)
430	);
431	}
432
433	/**
434	* @param $column_name
435	* @return string
436	*/
437	protected static function get_column_label( $column_name ) {
438	return ucfirst( str_replace( '_', ' ', $column_name ) );
439	}
440
441	}
442
443	}
444