__( 'Unread', 'fieldwren' ), 'read' => __( 'Read', 'fieldwren' ), 'pending' => __( 'Awaiting approval', 'fieldwren' ), 'unconfirmed' => __( 'Unconfirmed', 'fieldwren' ), 'declined' => __( 'Declined', 'fieldwren' ), 'partial' => __( 'Partial', 'fieldwren' ), 'spam' => __( 'Spam', 'fieldwren' ), 'trash' => __( 'Trash', 'fieldwren' ), ); } /** * Insert an entry and its values. * * @param array $entry form_id, status, user_id, ip, user_agent, source_url (+ payment cols). * @param array $values Clean values. * @param array $schema Schema. * @return int Entry id. */ public static function insert( array $entry, array $values, array $schema ) { global $wpdb; $form_id = (int) $entry['form_id']; $store = $values; foreach ( $store as $k => $v ) { if ( ! empty( $schema['fields'][ $k ]['sensitive'] ) ) { unset( $store[ $k ] ); } } $now = current_time( 'mysql', true ); $serial = (int) $wpdb->get_var( $wpdb->prepare( 'SELECT MAX(serial) FROM ' . Install::table( 'entries' ) . ' WHERE form_id = %d', $form_id ) ) + 1; // phpcs:ignore $wpdb->insert( // phpcs:ignore WordPress.DB.DirectDatabaseQuery Install::table( 'entries' ), array( 'form_id' => $form_id, 'serial' => $serial, 'status' => (string) ( $entry['status'] ?? 'unread' ), 'user_id' => (int) ( $entry['user_id'] ?? 0 ), 'ip' => (string) ( $entry['ip'] ?? '' ), 'user_agent' => (string) ( $entry['user_agent'] ?? '' ), 'source_url' => substr( (string) ( $entry['source_url'] ?? '' ), 0, 500 ), 'payment_status' => (string) ( $entry['payment_status'] ?? '' ), 'payment_total' => (int) ( $entry['payment_total'] ?? 0 ), 'currency' => (string) ( $entry['currency'] ?? '' ), 'response' => wp_json_encode( $store ), 'created_at' => $now, 'updated_at' => $now, ) ); $id = (int) $wpdb->insert_id; if ( $id ) { self::write_values( $id, $form_id, $store ); } return $id; } /** (Re)write the per-field value rows. */ public static function write_values( $entry_id, $form_id, array $values ) { global $wpdb; $t = Install::table( 'entry_values' ); $wpdb->delete( $t, array( 'entry_id' => $entry_id ) ); // phpcs:ignore foreach ( $values as $key => $v ) { if ( is_array( $v ) && self::is_assoc( $v ) ) { foreach ( $v as $sub => $sv ) { $wpdb->insert( $t, array( 'entry_id' => $entry_id, 'form_id' => $form_id, 'field_name' => substr( $key, 0, 100 ), 'sub_field' => substr( (string) $sub, 0, 100 ), 'value' => is_scalar( $sv ) ? (string) $sv : wp_json_encode( $sv ) ) ); // phpcs:ignore } continue; } $wpdb->insert( $t, array( 'entry_id' => $entry_id, 'form_id' => $form_id, 'field_name' => substr( $key, 0, 100 ), 'value' => is_scalar( $v ) ? (string) $v : wp_json_encode( $v ) ) ); // phpcs:ignore } } /** @return bool */ private static function is_assoc( array $a ) { return array() !== $a && array_keys( $a ) !== range( 0, count( $a ) - 1 ); } /** @return array|null Entry row with response decoded. */ public static function get( $id ) { global $wpdb; $row = $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM ' . Install::table( 'entries' ) . ' WHERE id = %d', $id ), ARRAY_A ); // phpcs:ignore if ( ! $row ) { return null; } $row['response'] = json_decode( (string) $row['response'], true ); $row['response'] = is_array( $row['response'] ) ? $row['response'] : array(); return $row; } /** Update columns; 'response' (array) also rewrites the value rows. */ public static function update( $id, array $data ) { global $wpdb; $row = $wpdb->get_row( $wpdb->prepare( 'SELECT form_id FROM ' . Install::table( 'entries' ) . ' WHERE id = %d', $id ) ); // phpcs:ignore if ( ! $row ) { return false; } if ( isset( $data['response'] ) && is_array( $data['response'] ) ) { self::write_values( $id, (int) $row->form_id, $data['response'] ); $data['response'] = wp_json_encode( $data['response'] ); } $data['updated_at'] = current_time( 'mysql', true ); $ok = $wpdb->update( Install::table( 'entries' ), $data, array( 'id' => $id ) ); // phpcs:ignore do_action( 'fwren/entry_updated', $id, $data ); return false !== $ok; } /** Delete entries permanently (files, values, meta). */ public static function delete( array $ids ) { global $wpdb; $ids = array_filter( array_map( 'absint', $ids ) ); if ( ! $ids ) { return 0; } foreach ( $ids as $id ) { $e = self::get( $id ); if ( $e ) { $s = Form::settings( $e['form_id'] ); if ( ! empty( $s['entries']['delete_uploads'] ) ) { Uploads::delete_files( $e['response'] ); } do_action( 'fwren/entry_deleted', $id, $e ); } } $in = implode( ',', $ids ); $wpdb->query( 'DELETE FROM ' . Install::table( 'entry_values' ) . " WHERE entry_id IN ($in)" ); // phpcs:ignore $wpdb->query( 'DELETE FROM ' . Install::table( 'entry_meta' ) . " WHERE entry_id IN ($in)" ); // phpcs:ignore return (int) $wpdb->query( 'DELETE FROM ' . Install::table( 'entries' ) . " WHERE id IN ($in)" ); // phpcs:ignore } /** Delete everything of a form. */ public static function delete_form_entries( $form_id ) { global $wpdb; $ids = $wpdb->get_col( $wpdb->prepare( 'SELECT id FROM ' . Install::table( 'entries' ) . ' WHERE form_id = %d', $form_id ) ); // phpcs:ignore foreach ( array_chunk( $ids, 200 ) as $chunk ) { self::delete( $chunk ); } $wpdb->delete( Install::table( 'stats' ), array( 'form_id' => $form_id ) ); // phpcs:ignore } /** * Count entries. * * @param int $form_id Form (0 = all). * @param string $status Status ('' = all but trash/spam/partial). * @param string $since UTC datetime lower bound. * @return int */ public static function count( $form_id = 0, $status = '', $since = '' ) { global $wpdb; $where = array( '1=1' ); if ( $form_id ) { $where[] = $wpdb->prepare( 'form_id = %d', $form_id ); } $where[] = $status ? $wpdb->prepare( 'status = %s', $status ) : "status NOT IN ('trash','spam','partial')"; if ( $since ) { $where[] = $wpdb->prepare( 'created_at >= %s', $since ); } return (int) $wpdb->get_var( 'SELECT COUNT(*) FROM ' . Install::table( 'entries' ) . ' WHERE ' . implode( ' AND ', $where ) ); // phpcs:ignore } /** @return string UTC start of a period (total | day | week | month | year). */ public static function period_start( $period ) { $map = array( 'day' => 'today', 'week' => 'monday this week', 'month' => 'first day of this month', 'year' => 'first day of january this year' ); if ( ! isset( $map[ $period ] ) ) { return ''; } $ts = strtotime( $map[ $period ] . ' 00:00:00', current_time( 'timestamp' ) ); // phpcs:ignore return gmdate( 'Y-m-d H:i:s', $ts - (int) ( get_option( 'gmt_offset' ) * HOUR_IN_SECONDS ) ); } /** Entries by the current submitter (ip | user | cookie). */ public static function count_by_submitter( $form_id, $by ) { global $wpdb; $t = Install::table( 'entries' ); if ( 'user' === $by ) { $uid = get_current_user_id(); return $uid ? (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM $t WHERE form_id=%d AND user_id=%d AND status NOT IN ('trash','spam','partial')", $form_id, $uid ) ) : 0; // phpcs:ignore } if ( 'cookie' === $by ) { return isset( $_COOKIE[ 'fwren_done_' . $form_id ] ) ? 99 : 0; } return (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM $t WHERE form_id=%d AND ip=%s AND status NOT IN ('trash','spam','partial')", $form_id, Submission::ip() ) ); // phpcs:ignore } /** @return bool Whether a value was already submitted (unique validation). */ public static function value_exists( $form_id, $key, $value ) { global $wpdb; $e = Install::table( 'entries' ); $v = Install::table( 'entry_values' ); return (bool) $wpdb->get_var( $wpdb->prepare( "SELECT v.id FROM $v v INNER JOIN $e e ON e.id = v.entry_id WHERE v.form_id = %d AND v.field_name = %s AND v.value = %s AND e.status NOT IN ('trash','spam','partial') LIMIT 1", $form_id, $key, $value ) ); // phpcs:ignore } /** * Query entries. * * @param array $args form_id, status, search, favorite, date_from, date_to, orderby, order, per_page, page, ids. * @return array [ rows, total ] */ public static function query( array $args ) { global $wpdb; $a = wp_parse_args( $args, array( 'form_id' => 0, 'status' => '', 'search' => '', 'favorite' => false, 'date_from' => '', 'date_to' => '', 'orderby' => 'id', 'order' => 'DESC', 'per_page' => 20, 'page' => 1, 'ids' => array(), 'payment_status' => '' ) ); $e = Install::table( 'entries' ); $where = array( '1=1' ); if ( $a['form_id'] ) { $where[] = $wpdb->prepare( 'e.form_id = %d', $a['form_id'] ); } if ( 'all' === $a['status'] || '' === $a['status'] ) { $where[] = "e.status NOT IN ('trash','spam','partial','declined')"; } elseif ( 'favorite' === $a['status'] ) { $where[] = "e.is_favorite = 1 AND e.status NOT IN ('trash','spam')"; } else { $where[] = $wpdb->prepare( 'e.status = %s', $a['status'] ); } if ( $a['payment_status'] ) { $where[] = $wpdb->prepare( 'e.payment_status = %s', $a['payment_status'] ); } if ( $a['ids'] ) { $where[] = 'e.id IN (' . implode( ',', array_map( 'absint', (array) $a['ids'] ) ) . ')'; } if ( $a['date_from'] ) { $where[] = $wpdb->prepare( 'e.created_at >= %s', get_gmt_from_date( $a['date_from'] . ' 00:00:00' ) ); } if ( $a['date_to'] ) { $where[] = $wpdb->prepare( 'e.created_at <= %s', get_gmt_from_date( $a['date_to'] . ' 23:59:59' ) ); } if ( '' !== $a['search'] ) { $like = '%' . $wpdb->esc_like( $a['search'] ) . '%'; $where[] = $wpdb->prepare( '(e.response LIKE %s OR e.id = %d OR e.ip LIKE %s)', $like, (int) $a['search'], $like ); } $orderby = in_array( $a['orderby'], array( 'id', 'created_at', 'status', 'serial', 'payment_total' ), true ) ? $a['orderby'] : 'id'; $order = 'ASC' === strtoupper( $a['order'] ) ? 'ASC' : 'DESC'; $sql = implode( ' AND ', $where ); $total = (int) $wpdb->get_var( "SELECT COUNT(*) FROM $e e WHERE $sql" ); // phpcs:ignore $limit = ''; if ( $a['per_page'] > 0 ) { $limit = $wpdb->prepare( 'LIMIT %d OFFSET %d', $a['per_page'], max( 0, ( $a['page'] - 1 ) * $a['per_page'] ) ); } $rows = $wpdb->get_results( "SELECT e.* FROM $e e WHERE $sql ORDER BY e.$orderby $order $limit", ARRAY_A ); // phpcs:ignore foreach ( $rows as &$r ) { $r['response'] = json_decode( (string) $r['response'], true ); $r['response'] = is_array( $r['response'] ) ? $r['response'] : array(); } return array( $rows, $total ); } /** @return array Status counts of a form. */ public static function status_counts( $form_id = 0 ) { global $wpdb; $w = $form_id ? $wpdb->prepare( 'WHERE form_id = %d', $form_id ) : ''; $rows = $wpdb->get_results( 'SELECT status, COUNT(*) n FROM ' . Install::table( 'entries' ) . " $w GROUP BY status", ARRAY_A ); // phpcs:ignore $out = array(); foreach ( $rows as $r ) { $out[ $r['status'] ] = (int) $r['n']; } $fav = $wpdb->get_var( 'SELECT COUNT(*) FROM ' . Install::table( 'entries' ) . ( $w ? " $w AND" : ' WHERE' ) . " is_favorite = 1 AND status NOT IN ('trash','spam')" ); // phpcs:ignore $out['favorite'] = (int) $fav; return $out; } /* --------------------------- meta -------------------------------- */ /** Add meta. */ public static function add_meta( $entry_id, $key, $value, $form_id = 0 ) { global $wpdb; if ( ! $form_id ) { $form_id = (int) $wpdb->get_var( $wpdb->prepare( 'SELECT form_id FROM ' . Install::table( 'entries' ) . ' WHERE id = %d', $entry_id ) ); // phpcs:ignore } $wpdb->insert( Install::table( 'entry_meta' ), array( 'entry_id' => $entry_id, 'form_id' => $form_id, 'meta_key' => $key, 'meta_value' => is_scalar( $value ) ? (string) $value : wp_json_encode( $value ), 'user_id' => get_current_user_id(), 'created_at' => current_time( 'mysql', true ) ) ); // phpcs:ignore return (int) $wpdb->insert_id; } /** Set meta (replaces). */ public static function set_meta( $entry_id, $key, $value ) { self::delete_meta( $entry_id, $key ); return self::add_meta( $entry_id, $key, $value ); } /** @return mixed Last meta value (JSON decoded when possible). */ public static function get_meta( $entry_id, $key ) { global $wpdb; $v = $wpdb->get_var( $wpdb->prepare( 'SELECT meta_value FROM ' . Install::table( 'entry_meta' ) . ' WHERE entry_id = %d AND meta_key = %s ORDER BY id DESC LIMIT 1', $entry_id, $key ) ); // phpcs:ignore if ( null === $v ) { return null; } $d = json_decode( $v, true ); return null !== $d && ( is_array( $d ) ) ? $d : $v; } /** @return array Meta rows of a key. */ public static function meta_rows( $entry_id, $key ) { global $wpdb; return $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM ' . Install::table( 'entry_meta' ) . ' WHERE entry_id = %d AND meta_key = %s ORDER BY id DESC', $entry_id, $key ), ARRAY_A ); // phpcs:ignore } /** Delete meta. */ public static function delete_meta( $entry_id, $key ) { global $wpdb; $wpdb->delete( Install::table( 'entry_meta' ), array( 'entry_id' => $entry_id, 'meta_key' => $key ) ); // phpcs:ignore } /** @return int Entry id of a meta value (tokens). */ public static function find_by_meta( $key, $value ) { global $wpdb; return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT entry_id FROM ' . Install::table( 'entry_meta' ) . ' WHERE meta_key = %s AND meta_value = %s LIMIT 1', $key, $value ) ); // phpcs:ignore } /* --------------------------- misc -------------------------------- */ /** @return string Public view link (front-end entry view module). */ public static function public_url( $entry_id ) { $token = self::get_meta( $entry_id, '_view_token' ); if ( ! $token ) { $token = wp_generate_password( 32, false ); self::set_meta( $entry_id, '_view_token', $token ); } return add_query_arg( array( 'fwren_entry' => $entry_id, 'token' => $token ), home_url( '/' ) ); } /** Money in minor units → display. */ public static function money( $cents, $currency = '', $form_id = 0 ) { $p = $form_id ? Form::settings( $form_id )['payment'] : Form::defaults()['payment']; $cur = $currency ? $currency : $p['currency']; $amt = number_format( $cents / 100, (int) $p['decimals'], $p['decimal_sep'], $p['thousand_sep'] ); $sym = self::currency_symbol( $cur ); return 'right' === $p['currency_position'] ? $amt . ' ' . $sym : $sym . $amt; } /** @return string Currency symbol. */ public static function currency_symbol( $c ) { $s = array( 'USD' => '$', 'EUR' => '€', 'GBP' => '£', 'BDT' => '৳', 'INR' => '₹', 'AUD' => 'A$', 'CAD' => 'C$', 'JPY' => '¥', 'CNY' => '¥', 'SGD' => 'S$', 'AED' => 'د.إ', 'SAR' => '﷼', 'CHF' => 'CHF ', 'SEK' => 'kr ', 'NOK' => 'kr ', 'DKK' => 'kr ', 'BRL' => 'R$', 'MXN' => 'MX$', 'NGN' => '₦', 'ZAR' => 'R', 'KRW' => '₩', 'MYR' => 'RM', 'PKR' => '₨', 'NZD' => 'NZ$' ); return $s[ strtoupper( $c ) ] ?? strtoupper( $c ) . ' '; } /** Delete entries older than the retention period (form setting, else global). */ public static function apply_retention() { global $wpdb; $forms = get_posts( array( 'post_type' => Plugin::POST_TYPE, 'numberposts' => -1, 'post_status' => 'any', 'fields' => 'ids' ) ); foreach ( $forms as $id ) { $days = (int) Form::settings( $id )['entries']['retention_days']; if ( ! $days ) { $days = (int) Settings::get( 'retention_days' ); } if ( $days > 0 ) { $ids = $wpdb->get_col( $wpdb->prepare( 'SELECT id FROM ' . Install::table( 'entries' ) . ' WHERE form_id = %d AND created_at < %s LIMIT 500', $id, gmdate( 'Y-m-d H:i:s', time() - $days * DAY_IN_SECONDS ) ) ); // phpcs:ignore self::delete( $ids ); } } // Unconfirmed double opt-in entries. foreach ( $forms as $id ) { $s = Form::settings( $id )['double_optin']; if ( ! empty( $s['enabled'] ) && (int) $s['delete_after_days'] > 0 ) { $ids = $wpdb->get_col( $wpdb->prepare( 'SELECT id FROM ' . Install::table( 'entries' ) . " WHERE form_id = %d AND status = 'unconfirmed' AND created_at < %s LIMIT 500", $id, gmdate( 'Y-m-d H:i:s', time() - (int) $s['delete_after_days'] * DAY_IN_SECONDS ) ) ); // phpcs:ignore self::delete( $ids ); } } } }