bookit / includes / classes / database / Categories.php

Categories.php in Bookit — Booking & Appointment Calendar 2.6.0.5, at includes/classes/database/Categories.php

178 lines 5.0 KB

Raw Download Zip

1	<?php
2
3	namespace Bookit\Classes\Database;
4
5	use Bookit\Classes\Vendor\DatabaseModel;
6
7	class Categories extends DatabaseModel {
8
9	/**
10	* Create Table
11	*/
12	public static function create_table() {
13	global $wpdb;
14	require_once ABSPATH . 'wp-admin/includes/upgrade.php';
15	$table_name = self::_table();
16	$primary_key = self::$primary_key;
17
18	$sql = "CREATE TABLE {$table_name} (
19	id INT UNSIGNED NOT NULL AUTO_INCREMENT,
20	name VARCHAR(255) NOT NULL,
21	PRIMARY KEY ({$primary_key})
22	) {$wpdb->get_charset_collate()};";
23
24	maybe_create_table( $table_name, $sql );
25	}
26
27	/**
28	* Get All Rows
29	* @return mixed
30	*/
31	public static function get_all() {
32	global $wpdb;
33	return $wpdb->get_results( sprintf( 'SELECT * FROM `%s` ORDER BY `%s` ASC', esc_sql( self::_table() ), esc_sql( static::$primary_key ) ), ARRAY_A );
34	}
35
36	/**
37	* Get Categories by ids
38	*/
39	public static function get_categories_by_ids( $ids = array() ) {
40	if ( empty( $ids ) ) {
41	return array();
42	}
43
44	global $wpdb;
45	$ids = implode( ',', array_map( 'intval', $ids ) );
46	$sql = sprintf(
47	'SELECT `%1$s`.*
48	FROM `%1$s`
49	WHERE `%1$s`.id IN ( %3$s )
50	ORDER BY `%1$s`.`%2$s` DESC',
51	esc_sql( self::_table() ),
52	esc_sql( static::$primary_key ),
53	$ids
54	);
55
56	return $wpdb->get_results( $sql, ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
57	}
58
59	/**
60	* Get Categories with exist Services
61	*/
62	public static function get_with_exist_services() {
63	global $wpdb;
64	$sql = sprintf(
65	'SELECT `%1$s`.*
66	FROM `%1$s`
67	INNER JOIN `%2$s` ON `%1$s`.id = `%2$s`.category_id
68	GROUP BY `%1$s`.id ORDER BY `%1$s`.`%3$s` DESC',
69	esc_sql( self::_table() ),
70	esc_sql( Services::_table() ),
71	esc_sql( static::$primary_key )
72	);
73
74	return $wpdb->get_results( $sql, ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
75	}
76
77	/**
78	* Get Category
79	* @param $category_id
80	* @return mixed
81	*/
82	public static function get_one( $category_id ) {
83	global $wpdb;
84	return $wpdb->get_results( sprintf( 'SELECT * FROM `%s` WHERE id = %d ORDER BY `%s` ASC', esc_sql( self::_table() ), intval( $category_id ), esc_sql( static::$primary_key ) ), ARRAY_A );
85	}
86
87	/**
88	* Get Category with services
89	* @param $category_id int
90	* @return mixed
91	*/
92	public static function category_with_services( $category_id ) {
93	global $wpdb;
94	$sql = sprintf(
95	'SELECT `%1$s`.*,
96	GROUP_CONCAT(`%2$s`.id, "::",`%2$s`.title) as services,
97	GROUP_CONCAT(`%2$s`.id) as service_ids
98	FROM `%1$s`
99	LEFT JOIN `%2$s` ON `%1$s`.id = `%2$s`.category_id
100	WHERE `%1$s`.id = %%d
101	GROUP BY `%1$s`.id',
102	esc_sql( self::_table() ),
103	esc_sql( Services::_table() )
104	);
105	return $wpdb->get_row( $wpdb->prepare( $sql, intval( $category_id ) ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
106	}
107
108	/**
109	* Get All Services assosiated to category
110	*/
111	public static function get_category_total_assosiated_data( $category_id ) {
112	global $wpdb;
113
114	$sql = sprintf(
115	'SELECT
116	GROUP_CONCAT(DISTINCT ( `%2$s`.id )) as service_ids,
117	COUNT(DISTINCT (`%2$s`.id )) as services,
118	COUNT(`%3$s`.id) as staff,
119	COUNT(`%4$s`.id) as appointments
120	FROM `%1$s`
121	LEFT JOIN `%2$s` ON `%1$s`.id = `%2$s`.category_id
122	LEFT JOIN `%3$s` ON `%3$s`.service_id = `%2$s`.id
123	LEFT JOIN `%4$s` ON `%4$s`.service_id = `%2$s`.id and `%4$s`.start_time > %%d
124	WHERE `%1$s`.id = %%d',
125	esc_sql( self::_table() ),
126	esc_sql( Services::_table() ),
127	esc_sql( Staff_Services::_table() ),
128	esc_sql( Appointments::_table() )
129	);
130	$now = current_time( 'timestamp' );
131	return $wpdb->get_row( $wpdb->prepare( $sql, intval( $now ), intval( $category_id ) ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
132	}
133
134	/**
135	* Delete Category
136	* set connected appointments status = delete
137	* delete connected services
138	* remove service connection from staff
139	* add notes about category and service to appointments
140	*/
141	public static function deleteCategory( $id ) {
142	global $wpdb;
143
144	$wpdb->query( 'START TRANSACTION' );
145
146	$category = self::category_with_services( $id );
147	$categoryAppointments = Appointments::category_appointments( $id );
148
149	foreach ( $categoryAppointments as $appointment ) {
150	$notes = unserialize( $appointment->notes );
151	$notes['delete_category'] = $category;
152
153	$wpdb->update(
154	Appointments::_table(),
155	array(
156	'notes' => serialize( $notes ),
157	'status' => Appointments::$delete,
158	),
159	array( 'id' => $appointment->id )
160	);
161	}
162	// delete services
163	Services::delete_where( 'category_id', $id );
164
165	// delete staff connection
166	if ( null != $category->service_ids ) {
167	$service_ids = implode( ',', array_map( 'intval', explode( ',', $category->service_ids ) ) );
168	$sql = sprintf( 'DELETE FROM `%s` WHERE service_id IN (%s)', esc_sql( Staff_Services::_table() ), $service_ids );
169	$wpdb->query( $sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
170	}
171
172	// delete category
173	self::delete( $id );
174
175	$wpdb->query( 'COMMIT' );
176	}
177	}
178