← All changes | includes/Illuminate/Stats.php +266 -1 1.11.2 → 2.3.0 View file →

		@@ -46,11 +46,27 @@
46	46	Installer::maybe_upgrade();
47	47
48	48	add_action( 'subscrpt_hourly_cron', array( $this, 'maybe_take_daily_snapshot' ) );
49	49	add_action( 'admin_init', array( $this, 'maybe_take_daily_snapshot' ) );
	50	+
	51	+ // A cached monthly total that ignores the sale that just happened is
	52	+ // worse than no cache: the figure is wrong and nothing says so. Any
	53	+ // order changing status can move a month's revenue in or out.
	54	+ add_action( 'woocommerce_order_status_changed', array( __CLASS__, 'flush_monthly_revenue' ) );
50	55	}
51	56
52	57	/**
	58	+ * Drop the cached monthly revenue.
	59	+ *
	60	+ * @return void
	61	+ */
	62	+ public static function flush_monthly_revenue() {
	63	+ for ( $months = 1; $months <= 24; $months++ ) {
	64	+ delete_transient( 'subscrpt_monthly_revenue_' . $months );
	65	+ }
	66	+ }
	67	+
	68	+ /**
53	69	* Normalize a recurring amount to a monthly figure (MRR).
54	70	*
55	71	* mrr = amount / (interval * months_per_unit[period]). Unknown periods are
56	72	* treated as monthly; a zero cycle returns 0 to avoid division by zero.
		@@ -115,14 +131,263 @@
115	131	* @return array<string,int> Keyed by status (active, pending, ...).
116	132	*/
117	133	public static function get_status_counts() {
118	134	$counts = wp_count_posts( 'subscrpt_order' );
119		- $statuses = array( 'active', 'pending', 'on_hold', 'cancelled', 'expired', 'pe_cancelled' );
	135	+ $statuses = array( 'active', 'pending', 'on_hold', 'cancelled', 'expired', 'completed', 'pe_cancelled' );
120	136	$out = array();
121	137
122	138	foreach ( $statuses as $status ) {
123	139	$out[ $status ] = isset( $counts->$status ) ? (int) $counts->$status : 0;
124	140	}
	141	+
	142	+ return $out;
	143	+ }
	144	+
	145	+ /**
	146	+ * Query arguments for active subscriptions whose next payment falls within
	147	+ * the next N days.
	148	+ *
	149	+ * The one definition of "renewals due": the Overview counts with it and the
	150	+ * subscriptions list filters with it, so the figure and the rows it opens
	151	+ * cannot disagree. The meta clause is named so the list can sort by it.
	152	+ *
	153	+ * `_subscrpt_next_date` holds a Unix timestamp, so the window is compared
	154	+ * numerically rather than as a date string.
	155	+ *
	156	+ * @param int $days Number of days ahead to look.
	157	+ * @return array<string,mixed> WP_Query arguments.
	158	+ */
	159	+ public static function renewals_due_args( int $days = 7 ): array {
	160	+ $now = time();
	161	+
	162	+ return array(
	163	+ 'post_type' => 'subscrpt_order',
	164	+ 'post_status' => 'active',
	165	+ 'meta_query' => array( // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_query
	166	+ 'subscrpt_next_date' => array(
	167	+ 'key' => '_subscrpt_next_date',
	168	+ 'value' => array( $now, $now + ( max( 1, $days ) * DAY_IN_SECONDS ) ),
	169	+ 'compare' => 'BETWEEN',
	170	+ 'type' => 'NUMERIC',
	171	+ ),
	172	+ ),
	173	+ );
	174	+ }
	175	+
	176	+ /**
	177	+ * Count active subscriptions whose next payment falls inside a window.
	178	+ *
	179	+ * @param int $days Number of days ahead to look.
	180	+ * @return int
	181	+ */
	182	+ public static function count_renewals_due_within( int $days = 7 ): int {
	183	+ $query = new \WP_Query(
	184	+ array_merge(
	185	+ self::renewals_due_args( $days ),
	186	+ array(
	187	+ 'fields' => 'ids',
	188	+ 'posts_per_page' => 1,
	189	+ )
	190	+ )
	191	+ );
	192	+
	193	+ return (int) $query->found_posts;
	194	+ }
	195	+
	196	+ /**
	197	+ * Count renewal orders that failed recently.
	198	+ *
	199	+ * Renewal orders are identified from the subscription relation table rather
	200	+ * than from order meta, then looked up through `wc_get_orders()` — reading
	201	+ * the posts table directly would return nothing on a store using HPOS.
	202	+ *
	203	+ * @param int $hours How far back to look.
	204	+ * @return int
	205	+ */
	206	+ public static function count_failed_renewals_since( int $hours = 24 ): int {
	207	+ global $wpdb;
	208	+
	209	+ if ( ! function_exists( 'wc_get_orders' ) ) {
	210	+ return 0;
	211	+ }
	212	+
	213	+ $since = time() - ( max( 1, $hours ) * HOUR_IN_SECONDS );
	214	+
	215	+ /*
	216	+ * Ask WooCommerce first, not the relation table.
	217	+ *
	218	+ * The relation table holds every renewal order ever created, so starting
	219	+ * there means pulling an unbounded id list out of a store's whole
	220	+ * history and handing it to wc_get_orders(). Starting from the orders
	221	+ * side bounds the set by the time window before anything else runs —
	222	+ * usually a handful of rows — and only those ids reach the second query.
	223	+ *
	224	+ * wc_get_orders() rather than SQL against posts, because a store on HPOS
	225	+ * keeps orders in their own tables and a posts query returns nothing.
	226	+ */
	227	+ $failed = wc_get_orders(
	228	+ array(
	229	+ 'status' => array( 'failed' ),
	230	+ 'date_modified' => '>' . $since,
	231	+ 'limit' => -1,
	232	+ 'return' => 'ids',
	233	+ )
	234	+ );
	235	+
	236	+ $failed = array_filter( array_map( 'intval', (array) $failed ) );
	237	+
	238	+ if ( empty( $failed ) ) {
	239	+ return 0;
	240	+ }
	241	+
	242	+ $table = $wpdb->prefix . 'subscrpt_order_relation';
	243	+ $placeholders = implode( ',', array_fill( 0, count( $failed ), '%d' ) );
	244	+
	245	+ // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name from the prefix; ids are placeheld below.
	246	+ $sql = "SELECT COUNT( DISTINCT order_id ) FROM {$table} WHERE type = 'renew' AND order_id IN ( {$placeholders} )";
	247	+
	248	+ // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- prepared immediately above.
	249	+ return (int) $wpdb->get_var( $wpdb->prepare( $sql, $failed ) );
	250	+ }
	251	+
	252	+ /**
	253	+ * Count subscriptions created within the last N days.
	254	+ *
	255	+ * @param int $days Number of days back to look.
	256	+ * @return int
	257	+ */
	258	+ public static function count_new_since( int $days = 7 ): int {
	259	+ global $wpdb;
	260	+
	261	+ $since = gmdate( 'Y-m-d H:i:s', time() - ( max( 1, $days ) * DAY_IN_SECONDS ) );
	262	+
	263	+ return (int) $wpdb->get_var(
	264	+ $wpdb->prepare(
	265	+ "SELECT COUNT(1) FROM {$wpdb->posts}
	266	+ WHERE post_type = 'subscrpt_order'
	267	+ AND post_status NOT IN ( 'trash', 'auto-draft' )
	268	+ AND post_date_gmt >= %s",
	269	+ $since
	270	+ )
	271	+ );
	272	+ }
	273	+
	274	+ /**
	275	+ * Count subscriptions created in one calendar month.
	276	+ *
	277	+ * Asks exactly what the subscriptions list asks when filtered to that month
	278	+ * — the same statuses, the same local post date — so a figure that links to
	279	+ * the filtered list always matches the rows it opens.
	280	+ *
	281	+ * @param \DateTimeInterface $month Any moment in the month, in the store's timezone.
	282	+ * @return int
	283	+ */
	284	+ public static function count_new_in_month( \DateTimeInterface $month ): int {
	285	+ $query = new \WP_Query(
	286	+ array(
	287	+ 'post_type' => 'subscrpt_order',
	288	+ 'post_status' => 'any',
	289	+ 'date_query' => array(
	290	+ array(
	291	+ 'year' => (int) $month->format( 'Y' ),
	292	+ 'month' => (int) $month->format( 'n' ),
	293	+ ),
	294	+ ),
	295	+ 'fields' => 'ids',
	296	+ 'posts_per_page' => 1,
	297	+ )
	298	+ );
	299	+
	300	+ return (int) $query->found_posts;
	301	+ }
	302	+
	303	+ /**
	304	+ * Revenue from subscription orders, grouped by month.
	305	+ *
	306	+ * Read from real orders rather than the snapshot table: snapshots record
	307	+ * counts and MRR from the day this plugin started taking them, so a store
	308	+ * that installed last week has no history to chart. Orders go back as far
	309	+ * as the store does.
	310	+ *
	311	+ * Cached, because this is the one figure on the dashboard that does not
	312	+ * change minute to minute and the only one whose cost grows with the size
	313	+ * of the store.
	314	+ *
	315	+ * @param int $months How many months to return, including the current one.
	316	+ * @return array<int,array{label:string,month:string,total:float}> Oldest first.
	317	+ */
	318	+ public static function get_monthly_revenue( int $months = 6 ): array {
	319	+ global $wpdb;
	320	+
	321	+ $months = max( 1, min( 24, $months ) );
	322	+ $key = 'subscrpt_monthly_revenue_' . $months;
	323	+ $cached = get_transient( $key );
	324	+
	325	+ if ( is_array( $cached ) ) {
	326	+ return $cached;
	327	+ }
	328	+
	329	+ // Every month in the window, so a month with no sales is a gap in the
	330	+ // chart rather than a missing bar that shifts everything along.
	331	+ //
	332	+ // The store's months, not UTC's. WooCommerce dates an order in the store
	333	+ // timezone, so UTC buckets have no bar for an order placed between UTC
	334	+ // and local midnight on the 1st, and it silently drops out of the chart.
	335	+ $this_month = ( new \DateTimeImmutable( 'now', wp_timezone() ) )->modify( 'first day of this month' )->setTime( 0, 0 );
	336	+ $first = $this_month->modify( '-' . ( $months - 1 ) . ' months' );
	337	+
	338	+ $buckets = array();
	339	+ for ( $i = $months - 1; $i >= 0; $i-- ) {
	340	+ $month = $this_month->modify( "-{$i} months" );
	341	+
	342	+ $buckets[ $month->format( 'Y-m' ) ] = array(
	343	+ 'label' => wp_date( 'M', $month->getTimestamp() ),
	344	+ 'month' => $month->format( 'Y-m' ),
	345	+ 'total' => 0.0,
	346	+ );
	347	+ }
	348	+
	349	+ if ( ! function_exists( 'wc_get_orders' ) ) {
	350	+ return array_values( $buckets );
	351	+ }
	352	+
	353	+ $table = $wpdb->prefix . 'subscrpt_order_relation';
	354	+
	355	+ // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name from the prefix.
	356	+ $order_ids = $wpdb->get_col( "SELECT DISTINCT order_id FROM {$table}" );
	357	+ $order_ids = array_filter( array_map( 'intval', (array) $order_ids ) );
	358	+
	359	+ if ( ! empty( $order_ids ) ) {
	360	+ $orders = wc_get_orders(
	361	+ array(
	362	+ 'post__in' => $order_ids,
	363	+ 'status' => array( 'completed', 'processing' ),
	364	+ // A timestamp, not a date string: WooCommerce keeps only the day
	365	+ // of a string and reads it in the store timezone, while a
	366	+ // timestamp is compared to the second.
	367	+ 'date_created' => '>=' . $first->getTimestamp(),
	368	+ 'limit' => -1,
	369	+ )
	370	+ );
	371	+
	372	+ foreach ( (array) $orders as $order ) {
	373	+ $created = $order->get_date_created();
	374	+
	375	+ if ( ! $created ) {
	376	+ continue;
	377	+ }
	378	+
	379	+ $bucket = $created->date( 'Y-m' );
	380	+
	381	+ if ( isset( $buckets[ $bucket ] ) ) {
	382	+ $buckets[ $bucket ]['total'] += (float) $order->get_total();
	383	+ }
	384	+ }
	385	+ }
	386	+
	387	+ $out = array_values( $buckets );
	388	+
	389	+ set_transient( $key, $out, 6 * HOUR_IN_SECONDS );
125	390
126	391	return $out;
127	392	}
128	393