|null */ private static ?array $saleStatuses = null; /** * Is the module on? A flow asks before trusting the numbers. */ public static function enabled(): bool { return Manifest::isEnabled( self::SLUG ); } /** * Has the first fill finished, so every customer has a row? */ public static function ready(): bool { $fill = get_option( self::FILL_OPTION, [] ); return self::enabled() && is_array( $fill ) && 'done' === ( $fill['state'] ?? '' ); } /** * The statuses that count as a sale, without `wc-`. * * @return list */ public static function saleStatuses(): array { if ( null === self::$saleStatuses ) { $raw = (string) Settings::get( self::SLUG . '.sale_statuses', self::DEFAULT_SALE_STATUSES ); $parsed = []; foreach ( preg_split( '/[\s,،]+/u', $raw ) ?: [] as $status ) { $status = sanitize_key( str_starts_with( $status, 'wc-' ) ? substr( $status, 3 ) : $status ); if ( '' !== $status ) { $parsed[ $status ] = true; } } self::$saleStatuses = [] === $parsed ? [ 'processing', 'completed' ] : array_keys( $parsed ); } return self::$saleStatuses; } /** * A customer's row. * * @param string $rawPhone Number in any form. * * @return array|null Null for a number with no orders, or one that is not a mobile. */ public static function get( string $rawPhone ): ?array { $phone = PhoneNumber::parse( $rawPhone ); if ( null === $phone ) { return null; } global $wpdb; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Own table, primary key. $row = $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE phone_hash = %s', Schema::table( 'cust_stats' ), SiteKey::phoneHash( $phone->canonical() ) ), ARRAY_A ); return is_array( $row ) ? $row : null; } /** * Did this number place a sale order in the last few days? * * @param string $canonical Number in canonical form. * @param int $days Days to look back. */ public static function boughtWithin( string $canonical, int $days ): bool { if ( $days < 1 || ! self::enabled() ) { return false; } global $wpdb; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Own table, primary key; once per flow entry. $last = $wpdb->get_var( $wpdb->prepare( 'SELECT last_order_at FROM %i WHERE phone_hash = %s', Schema::table( 'cust_stats' ), SiteKey::phoneHash( $canonical ) ) ); return is_string( $last ) && strtotime( $last . ' UTC' ) > time() - $days * DAY_IN_SECONDS; } /** * Record one order, and return whose rows need rebuilding: its owner now, and * the owner it had before if its number changed. * * @param WC_Order $order The order. * * @return list Phone hashes. */ public static function record( WC_Order $order ): array { global $wpdb; $table = Schema::table( 'cust_orders' ); $orderId = $order->get_id(); $previous = self::ownerOf( $orderId ); $phone = self::phoneOf( $order ); $created = $order->get_date_created(); if ( null === $phone || null === $created || 'checkout-draft' === $order->get_status() ) { self::forget( $orderId ); return null === $previous ? [] : [ $previous ]; } $hash = SiteKey::phoneHash( $phone ); $net = (float) $order->get_subtotal() - (float) $order->get_discount_total() - (float) $order->get_total_refunded(); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Own table. $wpdb->query( $wpdb->prepare( 'REPLACE INTO %i (order_id, phone_hash, is_sale, created_at, local_date, net_sales) VALUES (%d, %s, %d, %s, %s, %s)', $table, $orderId, $hash, in_array( $order->get_status(), self::saleStatuses(), true ) ? 1 : 0, gmdate( 'Y-m-d H:i:s', $created->getTimestamp() ), Cycle::localDate( $created->getTimestamp() ), number_format( $net, 4, '.', '' ) ) ); return array_values( array_unique( array_filter( [ $hash, $previous ] ) ) ); } /** * Drop an order that was deleted, trashed or lost its mobile. * * @param int $orderId Order id. * * @return string|null The owner it had, whose row needs rebuilding. */ public static function forget( int $orderId ): ?string { $owner = self::ownerOf( $orderId ); if ( null !== $owner ) { global $wpdb; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Own table. $wpdb->delete( Schema::table( 'cust_orders' ), [ 'order_id' => $orderId ], [ '%d' ] ); } return $owner; } /** * Rebuild one customer's row from all of their orders. * * @param string $hash Phone hash. */ public static function rebuild( string $hash ): void { global $wpdb; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Own table, indexed by phone_hash. $orders = $wpdb->get_results( $wpdb->prepare( 'SELECT order_id, created_at, local_date, net_sales FROM %i WHERE phone_hash = %s AND is_sale = 1 ORDER BY created_at, order_id', Schema::table( 'cust_orders' ), $hash ), ARRAY_A ); $stats = Schema::table( 'cust_stats' ); if ( ! is_array( $orders ) || [] === $orders ) { // No sale left: the customer has nothing a flow could use. // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Own table. $wpdb->delete( $stats, [ 'phone_hash' => $hash ], [ '%s' ] ); return; } $days = Cycle::purchaseDays( array_map( static fn( array $o ): string => (string) $o['local_date'], $orders ) ); $first = $orders[0]; $last = $orders[ count( $orders ) - 1 ]; $net = 0.0; foreach ( $orders as $order ) { $net += (float) $order['net_sales']; } $average = Cycle::averageInterval( $days ); $cycle = Cycle::personal( $days ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Own table. $wpdb->replace( $stats, [ 'phone_hash' => $hash, 'orders_count' => count( $orders ), 'purchase_days' => count( $days ), 'first_order_at' => (string) $first['created_at'], 'last_order_at' => (string) $last['created_at'], 'first_date' => $days[0], 'last_date' => $days[ count( $days ) - 1 ], 'avg_interval_days' => null === $average ? null : number_format( $average, 2, '.', '' ), 'cycle_days' => null === $cycle ? null : number_format( $cycle, 2, '.', '' ), 'net_spent' => number_format( $net, 4, '.', '' ), 'last_order_id' => (int) $last['order_id'], 'computed_at' => gmdate( 'Y-m-d H:i:s' ), ] ); } /** * What the dashboard heartbeat reports, so the hub can check the two agree. * * @return array{ready: bool, sale_statuses: list, buyers: int, cycle_buyers: int} */ public static function summary(): array { global $wpdb; $table = Schema::table( 'cust_stats' ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Own table; once a day, in the heartbeat job. $row = $wpdb->get_row( $wpdb->prepare( 'SELECT COUNT(*) AS buyers, SUM(purchase_days >= %d) AS cycle_buyers FROM %i', Cycle::PERSONAL_FROM, $table ), ARRAY_A ); return [ 'ready' => self::ready(), 'sale_statuses' => self::saleStatuses(), 'buyers' => (int) ( $row['buyers'] ?? 0 ), 'cycle_buyers' => (int) ( $row['cycle_buyers'] ?? 0 ), ]; } /** * Forget a number, for a privacy erasure. * * @param string $canonical Number in canonical form. * * @return int Rows removed. */ public static function forgetPhone( string $canonical ): int { global $wpdb; $hash = SiteKey::phoneHash( $canonical ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Own tables. $removed = (int) $wpdb->delete( Schema::table( 'cust_orders' ), [ 'phone_hash' => $hash ], [ '%s' ] ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Own tables. $removed += (int) $wpdb->delete( Schema::table( 'cust_stats' ), [ 'phone_hash' => $hash ], [ '%s' ] ); return $removed; } /** * The customer's number for an order, as the dashboard picks it: the * account's verified mobile, else the billing one. * * @param WC_Order $order The order. */ public static function phoneOf( WC_Order $order ): ?string { $userId = $order->get_customer_id(); $verified = $userId > 0 ? PhoneNumber::parse( (string) get_user_meta( $userId, 'rahkar_phone', true ) ) : null; $phone = $verified ?? PhoneNumber::parse( (string) $order->get_billing_phone() ); return null === $phone ? null : $phone->canonical(); } /** * Forget the sale statuses read so far. For tests and after a settings change. */ public static function flush(): void { self::$saleStatuses = null; } /** * Whose an order row is now. * * @param int $orderId Order id. */ private static function ownerOf( int $orderId ): ?string { global $wpdb; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Own table, primary key. $hash = $wpdb->get_var( $wpdb->prepare( 'SELECT phone_hash FROM %i WHERE order_id = %d', Schema::table( 'cust_orders' ), $orderId ) ); return is_string( $hash ) && '' !== $hash ? $hash : null; } }