prefix . 'acquis_funnels'; } public static function objects_table() { global $wpdb; return $wpdb->prefix . 'acquis_funnel_objects'; } public static function maybe_install() { if ( self::DB_VERSION === get_option( self::DB_OPTION ) ) { return; } global $wpdb; require_once ABSPATH . 'wp-admin/includes/upgrade.php'; $c = $wpdb->get_charset_collate(); dbDelta( "CREATE TABLE {$wpdb->prefix}acquis_funnels ( id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(191) NOT NULL, template VARCHAR(40) NOT NULL DEFAULT '', status VARCHAR(20) NOT NULL DEFAULT 'draft', config LONGTEXT, form_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0, file_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0, synced_at DATETIME NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, PRIMARY KEY (id), KEY status (status), KEY form_id (form_id) ) $c;" ); dbDelta( "CREATE TABLE {$wpdb->prefix}acquis_funnel_objects ( id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, funnel_id BIGINT(20) UNSIGNED NOT NULL, object_type VARCHAR(30) NOT NULL, object_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0, owned TINYINT(1) NOT NULL DEFAULT 0, role VARCHAR(40) NOT NULL, created_at DATETIME NOT NULL, PRIMARY KEY (id), UNIQUE KEY funnel_role (funnel_id, role), KEY object (object_type, object_id) ) $c;" ); update_option( self::DB_OPTION, self::DB_VERSION, false ); } // ── Funnels ──────────────────────────────────────────────────────────── private static function hydrate( $row ) { if ( ! $row ) { return null; } $cfg = json_decode( (string) $row->config, true ); $row->config = is_array( $cfg ) ? $cfg : array(); return $row; } public static function get( $id ) { global $wpdb; self::maybe_install(); return self::hydrate( $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE id = %d', self::table(), absint( $id ) ) ) ); } /** @return object[] */ public static function all( $include_drafts = false ) { global $wpdb; self::maybe_install(); $sql = $include_drafts ? 'SELECT * FROM %i ORDER BY id DESC' : "SELECT * FROM %i WHERE status <> 'draft' ORDER BY id DESC"; $rows = (array) $wpdb->get_results( $wpdb->prepare( $sql, self::table() ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- fixed fragment, table via %i return array_map( array( __CLASS__, 'hydrate' ), $rows ); } /** @return int The new id, or 0. */ public static function insert( array $data ) { global $wpdb; self::maybe_install(); $now = current_time( 'mysql' ); $wpdb->insert( self::table(), array( 'name' => substr( sanitize_text_field( (string) ( $data['name'] ?? '' ) ), 0, 191 ), 'template' => sanitize_key( (string) ( $data['template'] ?? '' ) ), 'status' => 'draft', 'config' => wp_json_encode( (array) ( $data['config'] ?? array() ) ), 'form_id' => absint( $data['form_id'] ?? 0 ), 'file_id' => absint( $data['file_id'] ?? 0 ), 'synced_at' => $now, 'created_at' => $now, 'updated_at' => $now, ) ); return (int) $wpdb->insert_id; } public static function update( $id, array $data ) { global $wpdb; $set = array(); foreach ( array( 'name' => 191, 'template' => 40, 'status' => 20 ) as $k => $max ) { if ( isset( $data[ $k ] ) ) { $set[ $k ] = substr( 'template' === $k || 'status' === $k ? sanitize_key( (string) $data[ $k ] ) : sanitize_text_field( (string) $data[ $k ] ), 0, $max ); } } if ( isset( $data['config'] ) ) { $set['config'] = wp_json_encode( (array) $data['config'] ); } foreach ( array( 'form_id', 'file_id' ) as $k ) { if ( isset( $data[ $k ] ) ) { $set[ $k ] = absint( $data[ $k ] ); } } if ( isset( $data['synced_at'] ) ) { // The form's own updated_at, copied exactly, so "changed since the funnel last wrote to it" is a plain comparison. $set['synced_at'] = preg_match( '/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/', (string) $data['synced_at'] ) ? (string) $data['synced_at'] : null; } $set['updated_at'] = current_time( 'mysql' ); return false !== $wpdb->update( self::table(), $set, array( 'id' => absint( $id ) ) ); } /** Removes the funnel and its links. Never touches what the links point to. */ public static function delete( $id ) { global $wpdb; $wpdb->delete( self::objects_table(), array( 'funnel_id' => absint( $id ) ) ); $wpdb->delete( self::table(), array( 'id' => absint( $id ) ) ); } // ── Linked objects ───────────────────────────────────────────────────── public static function link( $funnel_id, $type, $object_id, $owned, $role ) { global $wpdb; self::maybe_install(); $wpdb->delete( self::objects_table(), array( 'funnel_id' => absint( $funnel_id ), 'role' => sanitize_key( $role ) ) ); $wpdb->insert( self::objects_table(), array( 'funnel_id' => absint( $funnel_id ), 'object_type' => sanitize_key( $type ), 'object_id' => absint( $object_id ), 'owned' => $owned ? 1 : 0, 'role' => sanitize_key( $role ), 'created_at' => current_time( 'mysql' ), ) ); } /** Forget a link (the object itself is not touched). */ public static function unlink( $funnel_id, $role ) { global $wpdb; $wpdb->delete( self::objects_table(), array( 'funnel_id' => absint( $funnel_id ), 'role' => sanitize_key( $role ) ) ); } /** @return object[] */ public static function objects( $funnel_id ) { global $wpdb; self::maybe_install(); return (array) $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i WHERE funnel_id = %d ORDER BY id ASC', self::objects_table(), absint( $funnel_id ) ) ); } public static function object( $funnel_id, $role ) { global $wpdb; self::maybe_install(); return $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE funnel_id = %d AND role = %s', self::objects_table(), absint( $funnel_id ), sanitize_key( $role ) ) ); } /** The funnel (any status except draft) that uses this form, or null. */ public static function for_form( $form_id ) { global $wpdb; $form_id = absint( $form_id ); if ( ! $form_id || self::DB_VERSION !== get_option( self::DB_OPTION ) ) { return null; } return self::hydrate( $wpdb->get_row( $wpdb->prepare( "SELECT * FROM %i WHERE form_id = %d AND status <> 'draft' ORDER BY id DESC LIMIT 1", self::table(), $form_id ) ) ); } // ── Health ───────────────────────────────────────────────────────────── /** * What is wrong with this funnel, if anything. A problem makes the funnel "needs repair" in the list. * * @return string[] codes: form_missing | file_missing */ public static function problems( $funnel ) { $out = array(); if ( ! $funnel ) { return $out; } if ( (int) $funnel->form_id && ! ACQUIS_P3_DB::get_form( $funnel->form_id ) ) { $out[] = 'form_missing'; } $tpl = ACQUIS_Funnel_Templates::get( $funnel->template ); if ( $tpl && ! empty( $tpl['needs_file'] ) && ( ! (int) $funnel->file_id || ! ACQUIS_File_Manager::get_file_data( (int) $funnel->file_id ) ) ) { $out[] = 'file_missing'; } /** * Lets Pro report its own missing objects (for example a deleted automation rule). * * @param string[] $out * @param object $funnel */ return (array) apply_filters( 'acquis_funnel_problems', $out, $funnel ); } /** Re-checks a funnel and keeps its stored status in step with what exists. */ public static function sync_status( $funnel ) { if ( ! $funnel || 'draft' === $funnel->status ) { return $funnel; } $want = self::problems( $funnel ) ? 'needs_repair' : 'active'; if ( $want !== $funnel->status ) { self::update( $funnel->id, array( 'status' => $want ) ); $funnel->status = $want; } return $funnel; } // ── Numbers for the list and the report ──────────────────────────────── /** Leads (submissions) a form has collected. */ public static function submission_count( $form_id ) { global $wpdb; return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE form_id = %d', ACQUIS_P3_DB::submissions_table(), absint( $form_id ) ) ); } /** * Report numbers for one form: views, submissions, conversion, downloads and emails, with a count per day. * * @return array */ public static function stats( $form_id, $file_id = 0, $days = 30 ) { global $wpdb; $form_id = absint( $form_id ); $days = max( 1, min( 365, absint( $days ) ) ); $since = gmdate( 'Y-m-d H:i:s', time() - $days * DAY_IN_SECONDS ); $at = ACQUIS_P3_DB::analytics_table(); $count = function ( $type ) use ( $wpdb, $at, $form_id, $since ) { return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE form_id = %d AND event_type = %s AND created_at >= %s', $at, $form_id, $type, $since ) ); }; $views = $count( 'form_view' ); $submits = $count( 'form_submit' ); $downloads = 0; if ( absint( $file_id ) ) { // A download event carries the file, not the form: count this file's downloads by leads who came through this form. $downloads = (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i d WHERE d.event_type = %s AND d.file_id = %d AND d.created_at >= %s AND d.lead_id IN ( SELECT lead_id FROM %i WHERE form_id = %d )', $at, 'download', absint( $file_id ), $since, ACQUIS_P3_DB::submissions_table(), $form_id ) ); } $daily = (array) $wpdb->get_results( $wpdb->prepare( 'SELECT DATE(created_at) AS day, COUNT(*) AS n FROM %i WHERE form_id = %d AND event_type = %s AND created_at >= %s GROUP BY DATE(created_at) ORDER BY day ASC', $at, $form_id, 'form_submit', $since ) ); return array( 'days' => $days, 'views' => $views, 'submissions' => $submits, 'conversion' => $views > 0 ? round( ( $submits / $views ) * 100, 1 ) : 0, 'downloads' => $downloads, 'daily' => $daily, 'total_leads' => self::submission_count( $form_id ), ); } }