prefix . 'sg_' . $name; } public static function goals(): GoalRepository { return new GoalRepository(); } public static function donations(): DonationRepository { return new DonationRepository(); } /** @return array[] all rules (enabled or not) with decoded config. */ public static function rules( int $goalId ): array { global $wpdb; $rows = $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM ' . self::t( 'rules' ) . ' WHERE goal_id = %d ORDER BY sort ASC, id ASC', $goalId ), ARRAY_A ); $rows = (array) $rows; // Iterate $rows itself by reference: `foreach ( (array) $rows as &$r )` would modify a temporary copy and discard the decoding. foreach ( $rows as &$r ) { $cfg = json_decode( (string) $r['config'], true ); $r['config'] = is_array( $cfg ) ? $cfg : array(); } unset( $r ); return $rows; } public static function rule( int $ruleId, int $goalId ): ?array { foreach ( self::rules( $goalId ) as $r ) { if ( (int) $r['id'] === $ruleId ) { return $r; } } return null; } public static function saveRule( int $goalId, string $type, array $config, ?int $ruleId = null ): void { global $wpdb; $json = wp_json_encode( $config ); if ( null === $ruleId ) { $sort = (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COALESCE(MAX(sort),0)+1 FROM ' . self::t( 'rules' ) . ' WHERE goal_id = %d', $goalId ) ); $wpdb->insert( self::t( 'rules' ), array( 'goal_id' => $goalId, 'type' => $type, 'config' => $json, 'enabled' => 1, 'sort' => $sort ), array( '%d', '%s', '%s', '%d', '%d' ) ); } else { $wpdb->update( self::t( 'rules' ), array( 'config' => $json ), array( 'id' => $ruleId, 'goal_id' => $goalId ), array( '%s' ), array( '%d', '%d' ) ); } } public static function setRuleEnabled( int $ruleId, int $goalId, bool $on ): void { global $wpdb; $wpdb->update( self::t( 'rules' ), array( 'enabled' => $on ? 1 : 0 ), array( 'id' => $ruleId, 'goal_id' => $goalId ), array( '%d' ), array( '%d', '%d' ) ); } /** Deleting a rule keeps its event history rows out of the way (they are only idempotency markers). */ public static function deleteRule( int $ruleId, int $goalId ): void { global $wpdb; if ( ! self::rule( $ruleId, $goalId ) ) { return; } $wpdb->delete( self::t( 'rules' ), array( 'id' => $ruleId, 'goal_id' => $goalId ), array( '%d', '%d' ) ); $wpdb->delete( self::t( 'rule_events' ), array( 'rule_id' => $ruleId ), array( '%d' ) ); $wpdb->query( $wpdb->prepare( 'DELETE FROM ' . self::t( 'state' ) . ' WHERE goal_id = %d AND skey LIKE %s', $goalId, '%:' . $ruleId ) ); } public static function donationCountForGoal( int $goalId ): int { global $wpdb; return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM ' . self::t( 'donations' ) . ' WHERE goal_id = %d', $goalId ) ); } /** Only possible when the goal has no donations (otherwise donations would be orphaned). */ public static function deleteGoal( int $goalId ): bool { global $wpdb; if ( self::donationCountForGoal( $goalId ) > 0 ) { return false; } foreach ( self::rules( $goalId ) as $r ) { self::deleteRule( (int) $r['id'], $goalId ); } $wpdb->delete( self::t( 'state' ), array( 'goal_id' => $goalId ), array( '%d' ) ); $wpdb->delete( self::t( 'goal_tiers' ), array( 'goal_id' => $goalId ), array( '%d' ) ); return self::goals()->delete( $goalId ); } /** * @param array{status?:string,search?:string,tests?:string,moderation?:string} $f * @return array{rows:array[],total:int} */ public static function searchDonations( array $f, int $page, int $perPage ): array { global $wpdb; [ $where, $args ] = self::donationWhere( $f ); $t = self::t( 'donations' ); $total = (int) $wpdb->get_var( $args ? $wpdb->prepare( "SELECT COUNT(*) FROM {$t} WHERE {$where}", ...$args ) : "SELECT COUNT(*) FROM {$t} WHERE {$where}" ); $a2 = array_merge( $args, array( $perPage, max( 0, ( $page - 1 ) * $perPage ) ) ); $rows = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$t} WHERE {$where} ORDER BY id DESC LIMIT %d OFFSET %d", ...$a2 ), ARRAY_A ); return array( 'rows' => (array) $rows, 'total' => $total ); } /** @return array{0:string,1:array} */ private static function donationWhere( array $f ): array { global $wpdb; $w = array( '1=1' ); $args = array(); $st = $f['status'] ?? ''; if ( in_array( $st, array( 'pending', 'completed' ), true ) ) { $w[] = 'status = %s'; $args[] = $st; } if ( in_array( $f['moderation'] ?? '', array( 'pending', 'approved', 'rejected' ), true ) ) { $w[] = 'moderation = %s'; $args[] = $f['moderation']; } if ( 'hide' === ( $f['tests'] ?? '' ) ) { $w[] = 'paypal_order_id NOT LIKE %s'; $args[] = $wpdb->esc_like( Format::TEST_PREFIX ) . '%'; } $s = trim( (string) ( $f['search'] ?? '' ) ); if ( '' !== $s ) { $like = '%' . $wpdb->esc_like( $s ) . '%'; // Names of anonymous donors are never stored, and are never searchable. $w[] = '( ( is_anonymous = 0 AND donor_name LIKE %s ) OR message LIKE %s OR paypal_order_id LIKE %s OR paypal_capture_id LIKE %s )'; array_push( $args, $like, $like, $like, $like ); } return array( implode( ' AND ', $w ), $args ); } /** Real donations only (test donations excluded). Streams in batches to bound memory. */ public static function exportBatch( int $afterId, int $limit ): array { global $wpdb; return (array) $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM ' . self::t( 'donations' ) . ' WHERE id > %d AND status = \'completed\' AND paypal_order_id NOT LIKE %s AND goal_id > 0 ORDER BY id ASC LIMIT %d', $afterId, $wpdb->esc_like( Format::TEST_PREFIX ) . '%', $limit ), ARRAY_A ); } public static function pendingModerationCount(): int { global $wpdb; return (int) $wpdb->get_var( 'SELECT COUNT(*) FROM ' . self::t( 'donations' ) . " WHERE status = 'completed' AND moderation = 'pending'" ); } public static function setModeration( int $id, string $to ): bool { global $wpdb; if ( ! in_array( $to, array( 'approved', 'rejected' ), true ) ) { return false; } $n = $wpdb->query( $wpdb->prepare( 'UPDATE ' . self::t( 'donations' ) . " SET moderation = %s WHERE id = %d AND moderation = 'pending'", $to, $id ) ); return 1 === $n; } /** * Test donations: goal_id = 0 (so every per-goal SUM and the rules engine ignore them), * paypal_order_id/capture id start with TEST-, and the message is flagged. Excluded from CSV. */ public static function insertTestDonation( int $amountMinor, string $currency ): ?int { return self::donations()->insert( array( 'goal_id' => 0, 'paypal_order_id' => Format::TEST_PREFIX . bin2hex( random_bytes( 6 ) ), 'paypal_capture_id' => Format::TEST_PREFIX . bin2hex( random_bytes( 8 ) ), 'base_minor' => $amountMinor, 'fee_minor' => 0, 'total_minor' => $amountMinor, 'currency' => $currency, 'donor_name' => 'Test Donor', 'is_anonymous' => 0, 'message' => '[TEST] This is a test alert from StreamGoals.', 'status' => 'completed', 'moderation' => 'approved', ) ); } public static function deleteTestDonations(): int { global $wpdb; return (int) $wpdb->query( $wpdb->prepare( 'DELETE FROM ' . self::t( 'donations' ) . ' WHERE goal_id = 0 AND paypal_order_id LIKE %s', $wpdb->esc_like( Format::TEST_PREFIX ) . '%' ) ); } }