← All changes | includes/models/class-wpsc-ticket.php +161 -5 3.3.7 → 3.5.4 View file →

		@@ -310,22 +310,22 @@
310	310	array( 'ticket_id' => $ticket->id )
311	311	);
312	312
313	313	// Delete threads for the ticket.
314		- $success = $wpdb->delete(
	314	+ $deleted_threads = $wpdb->delete(
315	315	$wpdb->prefix . 'psmsc_threads',
316	316	array( 'ticket' => $ticket->id )
317	317	);
318		- if ( ! $success ) {
	318	+ if ( false === $deleted_threads ) {
319	319	return false;
320	320	}
321	321
322	322	// Finally delete ticket.
323		- $success = $wpdb->delete(
	323	+ $deleted_ticket = $wpdb->delete(
324	324	$wpdb->prefix . 'psmsc_tickets',
325	325	array( 'id' => $ticket->id )
326	326	);
327		- if ( ! $success ) {
	327	+ if ( false === $deleted_ticket ) {
328	328	return false;
329	329	}
330	330
331	331	return true;
		@@ -546,9 +546,11 @@
546	546	} else {
547	547
548	548	// Get custom field object for the slug.
549	549	$cf = WPSC_Custom_Field::get_cf_by_slug( $slug );
550		- $filter_str[] = $cf ? $cf->type::parse_filter( $cf, $compare, $val ) : '1=1';
	550	+ if ( $cf ) {
	551	+ $filter_str[] = $cf->type::parse_filter( $cf, $compare, $val );
	552	+ }
551	553	}
552	554	}
553	555
554	556	return count( $filter_str ) > 1 ?
		@@ -989,8 +991,162 @@
989	991	)
990	992	)['results'];
991	993
992	994	return $results ? $results[0] : false;
	995	+ }
	996	+
	997	+ /**
	998	+ * Count tickets based on given filters
	999	+ *
	1000	+ * @param array $filter - array containing array items like search, where, etc.
	1001	+ * @return int
	1002	+ */
	1003	+ public static function count( $filter = array() ) {
	1004	+
	1005	+ global $wpdb;
	1006	+
	1007	+ $filter['is_active'] = isset( $filter['is_active'] ) ? $filter['is_active'] : 1;
	1008	+ $filter['orderby_slug'] = isset( $filter['orderby'] ) ? $filter['orderby'] : '';
	1009	+
	1010	+ $sql = 'SELECT COUNT(DISTINCT t.id) FROM ' . $wpdb->prefix . 'psmsc_tickets t ';
	1011	+ $joins = self::get_joins( $filter );
	1012	+ $where = self::get_where( $filter );
	1013	+
	1014	+ $sql = $sql . $joins . $where;
	1015	+
	1016	+ return (int) $wpdb->get_var( $sql );
	1017	+ }
	1018	+
	1019	+ /**
	1020	+ * Count tickets grouped by a raw SQL expression, e.g. day of week or month. Reuses the same
	1021	+ * joins/where building as find()/count(), so all the usual filters (including system_query)
	1022	+ * still apply - this is a single aggregate query instead of fetching every matching row just
	1023	+ * to bucket it in PHP.
	1024	+ *
	1025	+ * $group_by_sql must be a trusted, hard-coded SQL expression (e.g. 'WEEKDAY(t.date_created)') -
	1026	+ * never build it from user input, it is not escaped.
	1027	+ *
	1028	+ * @param array $filter - array containing array items like search, where, etc.
	1029	+ * @param string $group_by_sql - raw SQL expression to group by.
	1030	+ * @return array Map of the raw group value (as returned by MySQL) to ticket count.
	1031	+ */
	1032	+ public static function count_grouped_by( $filter, $group_by_sql ) {
	1033	+
	1034	+ global $wpdb;
	1035	+
	1036	+ $filter['is_active'] = isset( $filter['is_active'] ) ? $filter['is_active'] : 1;
	1037	+ $filter['orderby_slug'] = isset( $filter['orderby'] ) ? $filter['orderby'] : '';
	1038	+
	1039	+ $joins = self::get_joins( $filter );
	1040	+ $where = self::get_where( $filter );
	1041	+
	1042	+ $sql = 'SELECT ' . $group_by_sql . ' AS wpsc_grp, COUNT(DISTINCT t.id) AS wpsc_cnt FROM ' . $wpdb->prefix . 'psmsc_tickets t ';
	1043	+ $sql = $sql . $joins . $where . 'GROUP BY wpsc_grp';
	1044	+
	1045	+ $rows = $wpdb->get_results( $sql, ARRAY_A );
	1046	+
	1047	+ $counts = array();
	1048	+ foreach ( $rows as $row ) {
	1049	+ // A NULL group (e.g. an unset custom field) can't be used as an array key directly.
	1050	+ $key = $row['wpsc_grp'] === null ? '' : $row['wpsc_grp'];
	1051	+ $counts[ $key ] = (int) $row['wpsc_cnt'];
	1052	+ }
	1053	+
	1054	+ return $counts;
	1055	+ }
	1056	+
	1057	+ /**
	1058	+ * Compute SQL aggregates (SUM/AVG/MAX/MIN/COUNT, etc.) over tickets matching a filter, in a
	1059	+ * single query. Reuses the same joins/where building as find()/count(), so all the usual
	1060	+ * filters (including system_query) still apply - this replaces the "fetch every matching row
	1061	+ * just to sum/average a column in PHP" pattern.
	1062	+ *
	1063	+ * Every value in $aggregates must be a trusted, hard-coded SQL expression (e.g. 'SUM(t.cd)') -
	1064	+ * never build it from user input, it is not escaped.
	1065	+ *
	1066	+ * @param array $filter - array containing array items like search, where, etc.
	1067	+ * @param array $aggregates - map of result key => raw SQL aggregate expression.
	1068	+ * @return array Map of the same keys to their computed values (numeric, or null if there were
	1069	+ * no matching rows at all).
	1070	+ */
	1071	+ public static function aggregate( $filter, $aggregates ) {
	1072	+
	1073	+ global $wpdb;
	1074	+
	1075	+ $filter['is_active'] = isset( $filter['is_active'] ) ? $filter['is_active'] : 1;
	1076	+ $filter['orderby_slug'] = isset( $filter['orderby'] ) ? $filter['orderby'] : '';
	1077	+
	1078	+ $joins = self::get_joins( $filter );
	1079	+ $where = self::get_where( $filter );
	1080	+
	1081	+ $select = array();
	1082	+ foreach ( $aggregates as $key => $expr ) {
	1083	+ $select[] = $expr . ' AS ' . $key;
	1084	+ }
	1085	+
	1086	+ $sql = 'SELECT ' . implode( ', ', $select ) . ' FROM ' . $wpdb->prefix . 'psmsc_tickets t ';
	1087	+ $sql = $sql . $joins . $where;
	1088	+
	1089	+ $row = $wpdb->get_row( $sql, ARRAY_A );
	1090	+
	1091	+ $result = array();
	1092	+ foreach ( $aggregates as $key => $expr ) {
	1093	+ $result[ $key ] = isset( $row[ $key ] ) ? $row[ $key ] : null;
	1094	+ }
	1095	+
	1096	+ return $result;
	1097	+ }
	1098	+
	1099	+ /**
	1100	+ * Aggregate a per-ticket COUNT of related thread rows (e.g. average number of reply/report
	1101	+ * threads per matching ticket), in a single query - avoids running one thread-count query per
	1102	+ * ticket in PHP. Reuses the same joins/where building as find()/count() for the ticket side of
	1103	+ * the query, so all the usual filters (including system_query) still apply.
	1104	+ *
	1105	+ * Every value in $aggregates must be a trusted, hard-coded SQL expression evaluated over the
	1106	+ * per-ticket thread_count (e.g. 'AVG(thread_count)') - never build it from user input.
	1107	+ *
	1108	+ * @param array $filter - array containing array items like search, where, etc. (applies to
	1109	+ * which tickets are included).
	1110	+ * @param array $thread_types - thread 'type' values to count, e.g. array( 'report', 'reply' ).
	1111	+ * @param array $aggregates - map of result key => raw SQL aggregate expression.
	1112	+ * @return array Map of the same keys to their computed values (numeric, or null if there were
	1113	+ * no matching tickets at all).
	1114	+ */
	1115	+ public static function aggregate_thread_counts( $filter, $thread_types, $aggregates ) {
	1116	+
	1117	+ global $wpdb;
	1118	+
	1119	+ $filter['is_active'] = isset( $filter['is_active'] ) ? $filter['is_active'] : 1;
	1120	+ $filter['orderby_slug'] = isset( $filter['orderby'] ) ? $filter['orderby'] : '';
	1121	+
	1122	+ $joins = self::get_joins( $filter );
	1123	+ $where = self::get_where( $filter );
	1124	+
	1125	+ $type_placeholders = implode( ', ', array_fill( 0, count( $thread_types ), '%s' ) );
	1126	+ $thread_join = $wpdb->prepare(
	1127	+ "LEFT JOIN {$wpdb->prefix}psmsc_threads th ON th.ticket = t.id AND th.is_active = 1 AND th.type IN ( {$type_placeholders} )", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
	1128	+ $thread_types
	1129	+ );
	1130	+
	1131	+ $select = array();
	1132	+ foreach ( $aggregates as $key => $expr ) {
	1133	+ $select[] = $expr . ' AS ' . $key;
	1134	+ }
	1135	+
	1136	+ $sql = 'SELECT ' . implode( ', ', $select ) . ' FROM (';
	1137	+ $sql .= 'SELECT t.id, COUNT(th.id) AS thread_count FROM ' . $wpdb->prefix . 'psmsc_tickets t ';
	1138	+ $sql .= $thread_join . ' ' . $joins . $where . 'GROUP BY t.id';
	1139	+ $sql .= ') AS wpsc_per_ticket_thread_counts';
	1140	+
	1141	+ $row = $wpdb->get_row( $sql, ARRAY_A );
	1142	+
	1143	+ $result = array();
	1144	+ foreach ( $aggregates as $key => $expr ) {
	1145	+ $result[ $key ] = isset( $row[ $key ] ) ? $row[ $key ] : null;
	1146	+ }
	1147	+
	1148	+ return $result;
993	1149	}
994	1150	}
995	1151	endif;
996	1152