bookit / includes / classes / database / Services.php

Services.php in Bookit — Booking & Appointment Calendar trunk, at includes/classes/database/Services.php

157 lines 4.2 KB

Raw Download Zip

1	<?php
2
3	namespace Bookit\Classes\Database;
4
5	use Bookit\Classes\Vendor\DatabaseModel;
6
7	class Services 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	category_id INT UNSIGNED,
21	title VARCHAR(255),
22	duration INT NOT NULL DEFAULT 900,
23	price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
24	icon_id INT UNSIGNED,
25	PRIMARY KEY ({$primary_key}),
26	INDEX `idx_category_id` (`category_id`)
27	) {$wpdb->get_charset_collate()};";
28
29	maybe_create_table( $table_name, $sql );
30	}
31
32	/**
33	* Get Short Format
34	*/
35	public static function get_all_short() {
36	global $wpdb;
37	return $wpdb->get_results( sprintf( 'SELECT %1$s.id, %1$s.title, %1$s.price FROM `%1$s` ORDER BY `%2$s` DESC', esc_sql( self::_table() ), esc_sql( static::$primary_key ) ), ARRAY_A );
38	}
39
40	/**
41	* Get services assigned to staff
42	*/
43	public static function get_staff_services( $staffId ) {
44	global $wpdb;
45	$sql = sprintf(
46	'SELECT `%1$s`.*
47	FROM `%1$s`
48	INNER JOIN `%2$s` ON `%1$s`.id = `%2$s`.service_id AND `%2$s`.staff_id=%%d
49	ORDER BY `%1$s`.`%3$s` DESC',
50	esc_sql( self::_table() ),
51	esc_sql( Staff_Services::_table() ),
52	esc_sql( static::$primary_key )
53	);
54	return $wpdb->get_results( $wpdb->prepare( $sql, intval( $staffId ) ), ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
55	}
56
57	/**
58	* Get All Services which was assigned to staff
59	*/
60	public static function get_assigned_to_staff() {
61	global $wpdb;
62	$sql = sprintf(
63	'SELECT `%1$s`.*
64	FROM `%1$s`
65	INNER JOIN `%2$s` ON `%1$s`.id = `%2$s`.service_id
66	GROUP BY `%1$s`.id ORDER BY `%1$s`.`%3$s` DESC',
67	esc_sql( self::_table() ),
68	esc_sql( Staff_Services::_table() ),
69	esc_sql( static::$primary_key )
70	);
71
72	return $wpdb->get_results( $sql, ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
73	}
74
75	/**
76	* Get All Rows with category
77	*/
78	public static function get_all_with_category() {
79	global $wpdb;
80	$sql = sprintf(
81	'SELECT `%1$s`.*,
82	`%2$s`.name as category
83	FROM `%1$s`
84	LEFT JOIN `%2$s` ON `%1$s`.category_id = `%2$s`.id
85	ORDER BY `%2$s`.name, `%1$s`.title',
86	esc_sql( self::_table() ),
87	esc_sql( Categories::_table() )
88	);
89	return $wpdb->get_results( $sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
90	}
91
92	/**
93	* Get All Staff assosiated to service
94	*/
95	public static function get_service_total_staff( $service_id ) {
96	global $wpdb;
97	$sql = sprintf(
98	'SELECT COUNT(`%2$s`.id)
99	FROM `%1$s`
100	LEFT JOIN `%2$s` ON `%1$s`.id = `%2$s`.service_id
101	WHERE `%1$s`.id = %%d',
102	esc_sql( self::_table() ),
103	esc_sql( Staff_Services::_table() )
104	);
105	return $wpdb->get_var( $wpdb->prepare( $sql, intval( $service_id ) ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
106	}
107
108	/**
109	* Get total Services assosiated to category
110	* @return mixed
111	*/
112	public static function get_total_services_for_category( $category_id ) {
113	global $wpdb;
114	$sql = sprintf(
115	'SELECT COUNT(*) FROM `%s` WHERE category_id = %%d',
116	esc_sql( self::_table() )
117	);
118	return $wpdb->get_var( $wpdb->prepare( $sql, intval( $category_id ) ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
119	}
120	/**
121	* Delete Service
122	* set service appointments status = delete
123	* remove service connection from staff
124	* add notes about service to appointments
125	*/
126	public static function deleteService( $id ) {
127	global $wpdb;
128
129	$wpdb->query( 'START TRANSACTION' );
130
131	$service = self::get( 'id', $id );
132
133	$serviceAppointments = Appointments::service_appointments( $id );
134
135	foreach ( $serviceAppointments as $appointment ) {
136	$notes = unserialize( $appointment->notes );
137	$notes['delete_service'] = $service;
138
139	$wpdb->update(
140	Appointments::_table(),
141	array(
142	'notes' => serialize( $notes ),
143	'status' => Appointments::$delete,
144	),
145	array( 'id' => $appointment->id )
146	);
147	}
148	// delete staff connection
149	Staff_Services::delete_where( 'service_id', $id );
150
151	// delete service
152	self::delete( $id );
153
154	$wpdb->query( 'COMMIT' );
155	}
156	}
157