← All changes | includes/models/class-wpsc-ticket.php +132 -0 3.5.0 → 3.5.4 View file →

		@@ -1014,8 +1014,140 @@
1014	1014	$sql = $sql . $joins . $where;
1015	1015
1016	1016	return (int) $wpdb->get_var( $sql );
1017	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;
	1149	+ }
1018	1150	}
1019	1151	endif;
1020	1152
1021	1153	WPSC_Ticket::init();