0 || $has_email ) ) { $same_offer = null === $access ? '' : $wpdb->prepare( ' AND offer_id=%d', $access['offer_id'] ); $g = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM `{$t}` WHERE source=%s AND wp_user_id=%d AND offer_type=%s AND offer_value=%f AND email=%s{$same_offer} AND status IN ('granted','sent','clicked') AND (expires_at IS NULL OR expires_at > UTC_TIMESTAMP()) ORDER BY id DESC LIMIT 1", $source, $uid, $type, $value, $email ), ARRAY_A ); if ( $g && (int) $g['coupon_id'] > 0 && 'publish' === get_post_status( (int) $g['coupon_id'] ) ) { return self::grant_return( $g ); } } $code = self::unique_code( $source ); $coupon = new WC_Coupon(); $coupon->set_code( $code ); $offered = null !== $access && $access['offer_id'] > 0 && $access['access_id'] > 0; $coupon->set_discount_type( 'percent' === $type ? 'percent' : ( $offered && 'fixed_product' === ( $args['discount_type'] ?? '' ) ? 'fixed_product' : 'fixed_cart' ) ); $coupon->set_amount( $value ); $orders = $offered ? self::orders_of( (int) $access['access_id'] ) : 1; $coupon->set_usage_limit( $orders ); $coupon->set_usage_limit_per_user( $orders ); $coupon->set_individual_use( ! empty( $args['individual_use'] ) ); if ( '' !== $email && is_email( $email ) && (bool) apply_filters( 'aieo_coupon_lock_to_email', true, $email, $source ) ) { $coupon->set_email_restrictions( array( strtolower( $email ) ) ); } if ( ! empty( $args['min_spend'] ) ) { $coupon->set_minimum_amount( (float) $args['min_spend'] ); } if ( ! empty( $args['product_ids'] ) && is_array( $args['product_ids'] ) ) { $coupon->set_product_ids( array_map( 'intval', $args['product_ids'] ) ); } $expires_ts = time() + $ttl * DAY_IN_SECONDS; $coupon->set_date_expires( $expires_ts ); $coupon->set_description( 'AIEO offer — ' . $source ); $coupon->update_meta_data( '_aieo_coupon', 1 ); $coupon->update_meta_data( '_aieo_source', $source ); if ( $offered ) { $coupon->update_meta_data( AIEO_DMM_Offer_Access::COUPON_OFFER, $access['offer_id'] ); $coupon->update_meta_data( AIEO_DMM_Offer_Access::COUPON_ACCESS, $access['access_id'] ); if ( self::charged_on_price( (int) $access['offer_id'] ) ) { $coupon->update_meta_data( AIEO_DMM_Offer_Access::COUPON_MOVED, gmdate( 'Y-m-d H:i:s' ) ); } } $coupon->save(); $cid = (int) $coupon->get_id(); if ( $cid <= 0 ) { return null; } $now = gmdate( 'Y-m-d H:i:s' ); $exp = gmdate( 'Y-m-d H:i:s', $expires_ts ); $row = array( 'code' => $code, 'coupon_id' => $cid, 'source' => $source, 'subject_id' => (int) ( $args['subject_id'] ?? 0 ), 'wp_user_id' => $uid, 'email' => $email, 'channel' => substr( (string) ( $args['channel'] ?? '' ), 0, 10 ), 'offer_type' => $type, 'offer_value' => $value, 'status' => 'granted', 'minted_at' => $now, 'expires_at' => $exp, ); if ( null !== $access ) { $row += $access; } $wpdb->insert( $t, $row ); $gid = (int) $wpdb->insert_id; if ( $gid <= 0 ) { wp_trash_post( $cid ); return null; } self::grants_changed(); return self::grant_return( array( 'id' => $gid ) + $row ); } public static function withdraw( $grant_id ) { global $wpdb; $t = aieo_coupon_grants_table(); if ( (int) $grant_id <= 0 || ! self::table( $t ) ) { return false; } $g = $wpdb->get_row( $wpdb->prepare( "SELECT id, coupon_id, status FROM `{$t}` WHERE id = %d", (int) $grant_id ), ARRAY_A ); if ( ! $g || 'granted' !== (string) $g['status'] ) { return false; } $cid = (int) $g['coupon_id']; if ( $cid > 0 && 'shop_coupon' === get_post_type( $cid ) && get_post_meta( $cid, '_aieo_coupon', true ) ) { wp_delete_post( $cid, true ); } $gone = (bool) $wpdb->delete( $t, array( 'id' => (int) $g['id'], 'status' => 'granted' ), array( '%d', '%s' ) ); self::grants_changed(); return $gone; } public static function mark_sent( $grant_id, $channel = '' ) { global $wpdb; $t = aieo_coupon_grants_table(); if ( ! self::table( $t ) || (int) $grant_id <= 0 ) { return; } $wpdb->query( $wpdb->prepare( "UPDATE `{$t}` SET status='sent', sent_at=%s WHERE id=%d AND status='granted'", gmdate( 'Y-m-d H:i:s' ), (int) $grant_id ) ); $wpdb->query( $wpdb->prepare( "UPDATE `{$t}` SET sent_at=%s WHERE id=%d AND sent_at IS NULL", gmdate( 'Y-m-d H:i:s' ), (int) $grant_id ) ); $channel = substr( sanitize_key( (string) $channel ), 0, 10 ); if ( '' !== $channel ) { $wpdb->query( $wpdb->prepare( "UPDATE `{$t}` SET channel=%s WHERE id=%d AND status <> 'redeemed'", $channel, (int) $grant_id ) ); } } public static function mark_clicked( $grant_id ) { self::set_status( (int) $grant_id, 'clicked', 'clicked_at' ); } public static function on_order_status( $order_id, $from, $to, $order = null ) { if ( in_array( $to, array( 'cancelled', 'failed' ), true ) ) { self::unredeem( (int) $order_id ); return; } if ( ! in_array( $to, array( 'processing', 'completed', 'on-hold' ), true ) ) { return; } if ( ! $order && function_exists( 'wc_get_order' ) ) { $order = wc_get_order( $order_id ); } if ( ! $order || ! method_exists( $order, 'get_coupon_codes' ) ) { return; } $codes = (array) $order->get_coupon_codes(); $granted = self::granted_lines( $order ); if ( empty( $codes ) && empty( $granted ) ) { return; } global $wpdb; $t = aieo_coupon_grants_table(); if ( ! self::table( $t ) ) { return; } $now = gmdate( 'Y-m-d H:i:s' ); foreach ( $codes as $code ) { $code = strtolower( (string) $code ); $wpdb->query( $wpdb->prepare( "UPDATE `{$t}` SET status='redeemed', redeemed_order_id=%d, redeemed_at=%s WHERE code=%s AND status <> 'redeemed'", (int) $order_id, $now, $code ) ); } if ( $granted ) { $wpdb->query( $wpdb->prepare( "UPDATE `{$t}` SET status='redeemed', redeemed_order_id=%d, redeemed_at=%s WHERE id IN (" . implode( ',', array_fill( 0, count( $granted ), '%d' ) ) . ") AND status IN ('granted','sent','clicked')", array_merge( array( (int) $order_id, $now ), $granted ) ) ); } self::grants_changed(); } private static function granted_lines( $order ): array { if ( ! self::phase_3_on() || ! class_exists( 'AIEO_DMM_Offer_Stamp' ) || ! method_exists( $order, 'get_items' ) ) { return array(); } $grants = array(); foreach ( $order->get_items() as $item ) { $stamp = AIEO_DMM_Offer_Stamp::read( $item ); foreach ( (array) ( $stamp['offers'] ?? array() ) as $offer ) { if ( is_array( $offer ) && 'applied' === (string) ( $offer['role'] ?? '' ) && (int) ( $offer['grant'] ?? 0 ) > 0 ) { $grants[ (int) $offer['grant'] ] = (int) $offer['grant']; } } } return array_values( $grants ); } public static function on_order_trashed( $order_id ) { self::unredeem( (int) $order_id ); } private static function unredeem( int $order_id ) { if ( $order_id <= 0 || ! self::phase_3_on() ) { return; } global $wpdb; $t = aieo_coupon_grants_table(); if ( ! self::table( $t ) ) { return; } $wpdb->query( $wpdb->prepare( "UPDATE `{$t}` SET status = CASE WHEN clicked_at IS NOT NULL THEN 'clicked' WHEN sent_at IS NOT NULL THEN 'sent' ELSE 'granted' END, redeemed_order_id = 0, redeemed_at = NULL WHERE redeemed_order_id = %d AND status = 'redeemed'", $order_id ) ); self::grants_changed(); } private static function orders_of( int $access_id ): int { $access = class_exists( 'AIEO_DMM_Offer_Access' ) ? AIEO_DMM_Offer_Access::get( $access_id ) : null; if ( ! is_array( $access ) || 'personal' !== (string) ( $access['kind'] ?? '' ) ) { return 1; } return max( 1, (int) ( $access['max_uses_per_customer'] ?? 0 ) ); } private static function grants_changed() { if ( class_exists( 'AIEO_DMM_Visit_Grants' ) ) { AIEO_DMM_Visit_Grants::changed(); } } public static function cleanup() { global $wpdb; $t = aieo_coupon_grants_table(); if ( ! self::table( $t ) ) { return; } $rows = $wpdb->get_results( "SELECT id, coupon_id FROM `{$t}` WHERE status IN ('granted','sent','clicked') AND source <> 'aw_import' AND expires_at IS NOT NULL AND expires_at < UTC_TIMESTAMP() LIMIT 500", ARRAY_A ); foreach ( (array) $rows as $r ) { self::trash_owned_coupon( (int) $r['coupon_id'] ); $wpdb->update( $t, array( 'status' => 'expired', 'coupon_id' => 0 ), array( 'id' => (int) $r['id'] ) ); } $done = $wpdb->get_results( "SELECT id, coupon_id FROM `{$t}` WHERE status = 'redeemed' AND coupon_id > 0 AND source <> 'aw_import' AND expires_at IS NOT NULL AND expires_at < UTC_TIMESTAMP() LIMIT 500", ARRAY_A ); foreach ( (array) $done as $r ) { self::trash_owned_coupon( (int) $r['coupon_id'] ); $wpdb->update( $t, array( 'coupon_id' => 0 ), array( 'id' => (int) $r['id'] ) ); } if ( apply_filters( 'aieo_cleanup_adopt_aw_coupons', (bool) get_option( 'aieo_aw_coupon_import' ) ) ) { $cut = time() - 14 * DAY_IN_SECONDS; $aw = $wpdb->get_col( $wpdb->prepare( "SELECT p.ID FROM {$wpdb->posts} p JOIN {$wpdb->postmeta} a ON a.post_id = p.ID AND a.meta_key = '_is_aw_coupon' JOIN {$wpdb->postmeta} e ON e.post_id = p.ID AND e.meta_key = 'date_expires' LEFT JOIN {$wpdb->postmeta} u ON u.post_id = p.ID AND u.meta_key = 'usage_count' WHERE p.post_type = 'shop_coupon' AND p.post_status = 'publish' AND e.meta_value <> '' AND CAST(e.meta_value AS UNSIGNED) < %d AND COALESCE(u.meta_value,0) + 0 = 0 LIMIT 200", $cut ) ); foreach ( (array) $aw as $id ) { if ( 'shop_coupon' === get_post_type( (int) $id ) ) { wp_trash_post( (int) $id ); } } $wpdb->query( "UPDATE `{$t}` SET status = 'expired' WHERE source = 'aw_import' AND status = 'granted' AND expires_at IS NOT NULL AND expires_at < UTC_TIMESTAMP()" ); } } private static function trash_owned_coupon( $cid ) { if ( $cid > 0 && 'shop_coupon' === get_post_type( $cid ) && get_post_meta( $cid, '_aieo_coupon', true ) ) { wp_trash_post( $cid ); } } public static function label( $type, $value ) { $dp = ( 'percent' !== $type && function_exists( 'wc_get_price_decimals' ) ) ? max( 0, (int) wc_get_price_decimals() ) : 2; $v = round( (float) $value, $dp ); $dec = ( abs( $v - round( $v ) ) < 0.5 / ( 10 ** $dp ) ) ? 0 : $dp; $amount = ( 'percent' === $type ) ? ( function_exists( 'number_format_i18n' ) ? number_format_i18n( $v, $dec ) : number_format( $v, $dec ) ) . '%' : ( function_exists( 'wc_price' ) ? html_entity_decode( wp_strip_all_tags( wc_price( $v, array( 'decimals' => $dec ) ) ), ENT_QUOTES, 'UTF-8' ) : '€' . number_format( $v, $dec ) ); return sprintf( __( '%s off', 'ai-eshop-optimizer' ), $amount ); } const CODE_PREFIX = 'rooster-'; private static function unique_code( $source ) { $slug = sanitize_title( $source ) ?: 'offer'; $slug = substr( $slug, 0, 20 ); do { $code = strtolower( self::CODE_PREFIX . $slug . '-' . wp_generate_password( 12, false, false ) ); $code = function_exists( 'wc_format_coupon_code' ) ? wc_format_coupon_code( $code ) : $code; $exists = function_exists( 'wc_get_coupon_id_by_code' ) ? (int) wc_get_coupon_id_by_code( $code ) : 0; } while ( $exists > 0 ); return $code; } private static function set_status( $grant_id, $status, $ts_col ) { global $wpdb; $t = aieo_coupon_grants_table(); if ( ! self::table( $t ) || $grant_id <= 0 ) { return; } $wpdb->query( $wpdb->prepare( "UPDATE `{$t}` SET status=%s, {$ts_col}=%s WHERE id=%d AND status <> 'redeemed'", $status, gmdate( 'Y-m-d H:i:s' ), (int) $grant_id ) ); } private static function grant_return( $g ) { $answer = array( 'grant_id' => (int) $g['id'], 'code' => (string) $g['code'], 'coupon_id' => (int) $g['coupon_id'], 'offer_type' => (string) $g['offer_type'], 'offer_value' => (float) $g['offer_value'], 'expires_at' => (string) $g['expires_at'], 'label' => self::label( (string) $g['offer_type'], (float) $g['offer_value'] ), ); if ( self::phase_3_on() ) { $answer['offer_id'] = (int) ( $g['offer_id'] ?? 0 ); $answer['access_id'] = (int) ( $g['access_id'] ?? 0 ); $answer['placement_id'] = (int) ( $g['placement_id'] ?? 0 ); $answer['kind'] = (string) ( $g['kind'] ?? 'code' ); } return $answer; } private static function phase_3_on() { return class_exists( 'AIEO_DMM_Offer_Access' ) && AIEO_DMM_Offer_Access::is_on(); } private static function charged_on_price( $offer_id ) { global $wpdb; if ( $offer_id <= 0 || ! defined( 'AIEO_DMM_Offer_Access::GROUP_ON_PRICE' ) ) { return false; } return AIEO_DMM_Offer_Access::GROUP_ON_PRICE === (string) $wpdb->get_var( $wpdb->prepare( "SELECT stack_group FROM {$wpdb->prefix}aieo_dmm_offers WHERE id = %d", (int) $offer_id ) ); } private static function access_columns( array $args ) { $kind = (string) ( $args['kind'] ?? 'code' ); $terms = isset( $args['terms'] ) && is_array( $args['terms'] ) && $args['terms'] ? wp_json_encode( $args['terms'], JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE ) : null; return array( 'offer_id' => max( 0, (int) ( $args['offer_id'] ?? 0 ) ), 'access_id' => max( 0, (int) ( $args['access_id'] ?? 0 ) ), 'placement_id' => max( 0, (int) ( $args['placement_id'] ?? 0 ) ), 'kind' => in_array( $kind, array( 'code', 'link', 'entry' ), true ) ? $kind : 'code', 'terms' => false === $terms ? null : $terms, 'visit_id' => substr( (string) preg_replace( '/[^A-Za-z0-9._-]/', '', (string) ( $args['visit_id'] ?? '' ) ), 0, 40 ), ); } private static function table( $t ) { global $wpdb; return (string) $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $t ) ) === $t; } }