| 1 |
<?php |
| 2 |
|
| 3 |
namespace BetterLinks\Traits; |
| 4 |
if ( ! defined( 'ABSPATH' ) ) { exit; } |
| 5 |
|
| 6 |
use BetterLinks\Admin\Cache; |
| 7 |
|
| 8 |
// phpcs:disable PluginCheck.Security.DirectDB, WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL, WordPress.DB.SlowDBQuery |
| 9 |
|
| 10 |
trait Query { |
| 11 |
|
| 12 |
|
| 13 |
public static function insert_link( $item, $is_update = false ) { |
| 14 |
global $wpdb; |
| 15 |
if ( $is_update ) { |
| 16 |
// An update without an ID cannot do anything — bail before we |
| 17 |
// generate PHP warnings by reading a missing 'ID' key four times. |
| 18 |
$id = isset( $item['ID'] ) ? $item['ID'] : null; |
| 19 |
if ( null === $id || '' === $id ) { |
| 20 |
return; |
| 21 |
} |
| 22 |
$item['ID'] = $id; |
| 23 |
// get_link_by_ID() returns an empty array for an ID that is no longer |
| 24 |
// in the table (migrations, stale caches), and current( array() ) is |
| 25 |
// false — wp_parse_args( $item, false ) is deprecated on PHP 8.1+ and |
| 26 |
// becomes a TypeError later. Fall back to the incoming item instead. |
| 27 |
$defaults = self::get_link_by_ID( $id ); |
| 28 |
$defaults = is_array( $defaults ) && ! empty( $defaults ) ? current( $defaults ) : array(); |
| 29 |
$item = is_array( $defaults ) ? wp_parse_args( $item, $defaults ) : $item; |
| 30 |
$link_data_array = array( |
| 31 |
'link_author' => $item['link_author'] ?? '', |
| 32 |
'link_date' => $item['link_date'] ?? '', |
| 33 |
'link_date_gmt' => $item['link_date_gmt'] ?? '', |
| 34 |
'link_title' => $item['link_title'] ?? '', |
| 35 |
'link_slug' => $item['link_slug'] ?? '', |
| 36 |
'link_note' => $item['link_note'] ?? '', |
| 37 |
'link_status' => $item['link_status'] ?? '', |
| 38 |
'nofollow' => $item['nofollow'] ?? '', |
| 39 |
'sponsored' => $item['sponsored'] ?? '', |
| 40 |
'track_me' => $item['track_me'] ?? '', |
| 41 |
'param_forwarding' => $item['param_forwarding'] ?? '', |
| 42 |
'param_struct' => $item['param_struct'] ?? '', |
| 43 |
'redirect_type' => $item['redirect_type'] ?? '', |
| 44 |
'target_url' => $item['target_url'] ?? '', |
| 45 |
'short_url' => $item['short_url'] ?? '', |
| 46 |
'link_order' => $item['link_order'] ?? '', |
| 47 |
'link_modified' => $item['link_modified'] ?? '', |
| 48 |
'link_modified_gmt' => $item['link_modified_gmt'] ?? '', |
| 49 |
'wildcards' => $item['wildcards'] ?? '', |
| 50 |
'expire' => $item['expire'] ?? '', |
| 51 |
'dynamic_redirect' => $item['dynamic_redirect'] ?? '', |
| 52 |
); |
| 53 |
$link_data_place_array = array( |
| 54 |
'%d', |
| 55 |
'%s', |
| 56 |
'%s', |
| 57 |
'%s', |
| 58 |
'%s', |
| 59 |
'%s', |
| 60 |
'%s', |
| 61 |
'%s', |
| 62 |
'%s', |
| 63 |
'%s', |
| 64 |
'%s', |
| 65 |
'%s', |
| 66 |
'%s', |
| 67 |
'%s', |
| 68 |
'%s', |
| 69 |
'%s', |
| 70 |
'%s', |
| 71 |
'%s', |
| 72 |
'%d', |
| 73 |
'%s', |
| 74 |
'%s', |
| 75 |
); |
| 76 |
if ( isset( $item['favorite'] ) ) { |
| 77 |
$link_data_array['favorite'] = $item['favorite']; |
| 78 |
$link_data_place_array[] = '%s'; |
| 79 |
} |
| 80 |
if ( isset( $item['uncloaked'] ) ) { |
| 81 |
$link_data_array['uncloaked'] = $item['uncloaked']; |
| 82 |
$link_data_place_array[] = '%s'; |
| 83 |
} |
| 84 |
$wpdb->update( |
| 85 |
"{$wpdb->prefix}betterlinks", |
| 86 |
$link_data_array, |
| 87 |
array( 'ID' => $item['ID'] ), |
| 88 |
$link_data_place_array, |
| 89 |
array( '%d' ) |
| 90 |
); |
| 91 |
do_action( 'betterlinks/after_update_link', $item['ID'], $item ); |
| 92 |
return $item['ID']; |
| 93 |
} else { |
| 94 |
$betterlinks = self::get_link_by_short_url( $item['short_url'] ); |
| 95 |
if ( count( $betterlinks ) === 0 ) { |
| 96 |
$initial_defaults_arr = array( |
| 97 |
'link_author' => get_current_user_id(), |
| 98 |
'link_date' => current_time( 'mysql' ), |
| 99 |
'link_date_gmt' => current_time( 'mysql', 1 ), |
| 100 |
'link_title' => '', |
| 101 |
'link_slug' => '', |
| 102 |
'link_note' => '', |
| 103 |
'link_status' => 'publish', |
| 104 |
'nofollow' => '', |
| 105 |
'sponsored' => '', |
| 106 |
'track_me' => '', |
| 107 |
'param_forwarding' => '', |
| 108 |
'param_struct' => '', |
| 109 |
'redirect_type' => '', |
| 110 |
'target_url' => '', |
| 111 |
'short_url' => '', |
| 112 |
'link_order' => '', |
| 113 |
'link_modified' => current_time( 'mysql' ), |
| 114 |
'link_modified_gmt' => current_time( 'mysql', 1 ), |
| 115 |
'wildcards' => '', |
| 116 |
'expire' => '', |
| 117 |
'dynamic_redirect' => '', |
| 118 |
); |
| 119 |
if ( isset( $item['favorite'] ) ) { |
| 120 |
$initial_defaults_arr['favorite'] = ''; |
| 121 |
} |
| 122 |
$defaults = apply_filters( 'betterlinks/insert_link_default_args', $initial_defaults_arr ); |
| 123 |
$item = wp_parse_args( $item, $defaults ); |
| 124 |
$column_names = 'link_author,link_date,link_date_gmt,link_title,link_slug,link_note,link_status,nofollow,sponsored,track_me,param_forwarding,param_struct,redirect_type,target_url,short_url,link_order,link_modified,link_modified_gmt,wildcards,expire,dynamic_redirect'; |
| 125 |
$column_placeholders = '%d, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %d, %s, %s'; |
| 126 |
$query_value_array = array( |
| 127 |
$item['link_author'], |
| 128 |
$item['link_date'], |
| 129 |
$item['link_date_gmt'], |
| 130 |
$item['link_title'], |
| 131 |
$item['link_slug'], |
| 132 |
$item['link_note'], |
| 133 |
$item['link_status'], |
| 134 |
$item['nofollow'], |
| 135 |
$item['sponsored'], |
| 136 |
$item['track_me'], |
| 137 |
$item['param_forwarding'], |
| 138 |
$item['param_struct'], |
| 139 |
$item['redirect_type'], |
| 140 |
$item['target_url'], |
| 141 |
$item['short_url'], |
| 142 |
$item['link_order'], |
| 143 |
$item['link_modified'], |
| 144 |
$item['link_modified_gmt'], |
| 145 |
$item['wildcards'], |
| 146 |
$item['expire'], |
| 147 |
$item['dynamic_redirect'], |
| 148 |
); |
| 149 |
if ( isset( $item['favorite'] ) ) { |
| 150 |
$column_names .= ',favorite'; |
| 151 |
$column_placeholders .= ', %s'; |
| 152 |
$query_value_array[] = $item['favorite']; |
| 153 |
} |
| 154 |
if ( isset( $item['uncloaked'] ) ) { |
| 155 |
$column_names .= ',uncloaked'; |
| 156 |
$column_placeholders .= ', %s'; |
| 157 |
$query_value_array[] = $item['uncloaked']; |
| 158 |
} |
| 159 |
$query_string = "INSERT INTO {$wpdb->prefix}betterlinks ( {$column_names} ) VALUES ( {$column_placeholders} )"; |
| 160 |
$wpdb->query( $wpdb->prepare( $query_string, $query_value_array ) ); |
| 161 |
do_action( 'betterlinks/after_insert_link', $wpdb->insert_id, $item ); |
| 162 |
return $wpdb->insert_id; |
| 163 |
} |
| 164 |
} |
| 165 |
return; |
| 166 |
} |
| 167 |
public static function delete_link( $ID ) { |
| 168 |
global $wpdb; |
| 169 |
$wpdb->delete( "{$wpdb->prefix}betterlinks", array( 'ID' => $ID ), array( '%d' ) ); |
| 170 |
$wpdb->delete( "{$wpdb->prefix}betterlinks_clicks", array( 'link_id' => $ID ), array( '%d' ) ); |
| 171 |
$wpdb->delete( "{$wpdb->prefix}betterlinks_terms_relationships", array( 'link_id' => $ID ), array( '%d' ) ); |
| 172 |
|
| 173 |
/** |
| 174 |
* Fires after a link and its owned rows are removed. |
| 175 |
* |
| 176 |
* Extensions that store their own references to a link id clean them up here — |
| 177 |
* Pro's Promo Cards use it in place of the FOREIGN KEY constraints its tables |
| 178 |
* used to declare. |
| 179 |
* |
| 180 |
* @param int $ID Deleted link ID. |
| 181 |
*/ |
| 182 |
do_action( 'betterlinks/link/after_delete', $ID ); |
| 183 |
} |
| 184 |
public static function remove_terms_relationships_by_link_ID( $ID ) { |
| 185 |
global $wpdb; |
| 186 |
$wpdb->delete( "{$wpdb->prefix}betterlinks_terms_relationships", array( 'link_id' => $ID ), array( '%d' ) ); |
| 187 |
} |
| 188 |
public static function get_prepare_all_links() { |
| 189 |
global $wpdb; |
| 190 |
$prefix = $wpdb->prefix; |
| 191 |
$analytic = get_option( 'betterlinks_analytics_data' ); |
| 192 |
$analytic = $analytic ? json_decode( $analytic, true ) : array(); |
| 193 |
|
| 194 |
// pull all broken links logs |
| 195 |
$broken_links = get_option( 'betterlinkspro_broken_links_logs' ); |
| 196 |
$broken_links = $broken_links ? json_decode( $broken_links, true ) : array(); |
| 197 |
|
| 198 |
$settings = Cache::get_json_settings(); |
| 199 |
|
| 200 |
// Categories a feature owns but does not want on the dashboard (Fluent |
| 201 |
// Boards' task category, the bio pages' "Link in Bio" category). Each |
| 202 |
// feature contributes its own term IDs and they are combined into one |
| 203 |
// exclusion, so adding a second one no longer overwrites the first. |
| 204 |
$hidden_term_ids = apply_filters( 'betterlinks/dashboard_hidden_term_ids', array(), $settings ); |
| 205 |
$hidden_term_ids = array_unique( array_filter( array_map( 'intval', (array) $hidden_term_ids ) ) ); |
| 206 |
|
| 207 |
// Back-compat: the original Fluent Boards filter returns a whole WHERE |
| 208 |
// clause rather than IDs. Keep honouring it and AND the ID list onto it. |
| 209 |
$fbs_category_query = apply_filters( 'betterlinks__intlfbs_filter_category_from_dashboard', '', $settings ); |
| 210 |
|
| 211 |
if ( ! empty( $hidden_term_ids ) ) { |
| 212 |
$hidden_clause = sprintf( 'bt.ID NOT IN (%s)', implode( ',', $hidden_term_ids ) ); |
| 213 |
$fbs_category_query = empty( $fbs_category_query ) |
| 214 |
? 'WHERE ' . $hidden_clause |
| 215 |
: $fbs_category_query . ' AND ' . $hidden_clause; |
| 216 |
} |
| 217 |
|
| 218 |
$query = "SELECT |
| 219 |
bt.ID as cat_id, |
| 220 |
bt.term_name, |
| 221 |
bt.term_slug, |
| 222 |
bt.term_type, |
| 223 |
bl.ID, |
| 224 |
bl.link_title, |
| 225 |
bl.link_slug, |
| 226 |
bl.link_note, |
| 227 |
bl.link_status, |
| 228 |
bl.nofollow, |
| 229 |
bl.sponsored, |
| 230 |
bl.track_me, |
| 231 |
bl.param_forwarding, |
| 232 |
bl.param_struct, |
| 233 |
bl.redirect_type, |
| 234 |
bl.target_url, |
| 235 |
bl.short_url, |
| 236 |
bl.link_date, |
| 237 |
bl.wildcards, |
| 238 |
bl.expire, |
| 239 |
bl.favorite, |
| 240 |
bl.dynamic_redirect, |
| 241 |
bl.uncloaked |
| 242 |
FROM {$prefix}betterlinks_terms as bt |
| 243 |
LEFT JOIN {$prefix}betterlinks_terms_relationships as btr ON bt.ID = btr.term_id |
| 244 |
LEFT JOIN {$prefix}betterlinks as bl ON bl.ID = btr.link_id |
| 245 |
-- WHERE bt.term_type = 'category' |
| 246 |
{$fbs_category_query} |
| 247 |
ORDER BY bl.link_order ASC;"; |
| 248 |
|
| 249 |
$results = $wpdb->get_results( |
| 250 |
$query, |
| 251 |
OBJECT |
| 252 |
); |
| 253 |
$results = \BetterLinks\Helper::parse_link_response( $results, $analytic, $broken_links ); |
| 254 |
return $results; |
| 255 |
} |
| 256 |
public static function get_link_by_short_url( $short_url, $is_case_sensitive = false ) { |
| 257 |
global $wpdb; |
| 258 |
$link = $wpdb->get_results( |
| 259 |
$wpdb->prepare( "SELECT * FROM {$wpdb->prefix}betterlinks WHERE short_url=%s", $short_url ), |
| 260 |
ARRAY_A |
| 261 |
); |
| 262 |
if ( isset( $link[0]['short_url'] ) && $is_case_sensitive && $link[0]['short_url'] != $short_url ) { |
| 263 |
return array(); |
| 264 |
} |
| 265 |
return $link; |
| 266 |
} |
| 267 |
public static function get_link_by_permalink( $target_url, $fields = '*' ) { |
| 268 |
global $wpdb; |
| 269 |
$link = $wpdb->get_row( |
| 270 |
$wpdb->prepare( "SELECT {$fields} FROM {$wpdb->prefix}betterlinks WHERE target_url=%s", $target_url ), |
| 271 |
ARRAY_A |
| 272 |
); |
| 273 |
return ! empty( $link ) ? $link : array(); |
| 274 |
} |
| 275 |
public static function get_link_by_wildcards( $wildcards ) { |
| 276 |
global $wpdb; |
| 277 |
$link = $wpdb->get_results( |
| 278 |
$wpdb->prepare( "SELECT * FROM {$wpdb->prefix}betterlinks WHERE wildcards=%d", $wildcards ), |
| 279 |
ARRAY_A |
| 280 |
); |
| 281 |
return $link; |
| 282 |
} |
| 283 |
public static function get_link_by_ID( $ID ) { |
| 284 |
global $wpdb; |
| 285 |
$link = $wpdb->get_results( |
| 286 |
$wpdb->prepare( "SELECT * FROM {$wpdb->prefix}betterlinks WHERE ID=%d", $ID ), |
| 287 |
ARRAY_A |
| 288 |
); |
| 289 |
return $link; |
| 290 |
} |
| 291 |
public static function get_link_data_with_cat_id_by_link_id( $ID ) { |
| 292 |
global $wpdb; |
| 293 |
$link = $wpdb->get_results( |
| 294 |
$wpdb->prepare( |
| 295 |
"SELECT |
| 296 |
bt.ID as cat_id, |
| 297 |
bl.ID, |
| 298 |
bl.target_url, |
| 299 |
bl.short_url, |
| 300 |
bl.uncloaked |
| 301 |
FROM {$wpdb->prefix}betterlinks as bl |
| 302 |
INNER JOIN {$wpdb->prefix}betterlinks_terms_relationships as btr ON bl.ID = btr.link_id AND bl.ID=%d |
| 303 |
INNER JOIN {$wpdb->prefix}betterlinks_terms as bt ON bt.ID = btr.term_id AND bt.term_type = 'category' |
| 304 |
", |
| 305 |
$ID |
| 306 |
), |
| 307 |
ARRAY_A |
| 308 |
); |
| 309 |
return $link; |
| 310 |
} |
| 311 |
|
| 312 |
/** |
| 313 |
* Get All BetterLinks Uploads Links JSON File |
| 314 |
* |
| 315 |
* @return array |
| 316 |
*/ |
| 317 |
public static function get_links_for_json() { |
| 318 |
global $wpdb; |
| 319 |
$prefix = $wpdb->prefix; |
| 320 |
$formattedArray = array(); |
| 321 |
// Changed from INNER JOIN to LEFT JOIN to include links without category assignments |
| 322 |
// This prevents links from disappearing when category relationships are delayed |
| 323 |
$items = $wpdb->get_results( |
| 324 |
"SELECT |
| 325 |
bl.ID, |
| 326 |
bl.redirect_type, |
| 327 |
bl.short_url, |
| 328 |
bl.link_slug, |
| 329 |
bl.link_status, |
| 330 |
bl.target_url, |
| 331 |
bl.nofollow, |
| 332 |
bl.sponsored, |
| 333 |
bl.param_forwarding, |
| 334 |
bl.track_me, |
| 335 |
bl.wildcards, |
| 336 |
bl.expire, |
| 337 |
bl.dynamic_redirect, |
| 338 |
bl.uncloaked, |
| 339 |
br.term_id as cat_id |
| 340 |
FROM {$prefix}betterlinks as bl |
| 341 |
LEFT JOIN {$prefix}betterlinks_terms_relationships as br ON bl.ID = br.link_id |
| 342 |
LEFT JOIN {$prefix}betterlinks_terms as bt ON br.term_id = bt.ID AND bt.term_type = 'category' |
| 343 |
GROUP BY bl.ID |
| 344 |
ORDER BY bl.ID DESC |
| 345 |
" |
| 346 |
); |
| 347 |
$options = json_decode( get_option( BETTERLINKS_LINKS_OPTION_NAME ), true ); |
| 348 |
$formattedArray['is_case_sensitive'] = isset( $options['is_case_sensitive'] ) ? $options['is_case_sensitive'] : false; |
| 349 |
$formattedArray['is_disable_analytics_ip'] = isset( $options['is_disable_analytics_ip'] ) ? $options['is_disable_analytics_ip'] : false; |
| 350 |
$formattedArray['excluded_ips'] = isset( $options['excluded_ips'] ) ? $options['excluded_ips'] : array(); |
| 351 |
$is_links_case_sensitive = $formattedArray['is_case_sensitive']; |
| 352 |
if ( ! empty( $options ) ) { |
| 353 |
$formattedArray['wildcards_is_active'] = isset( $options['wildcards'] ) ? $options['wildcards'] : false; |
| 354 |
$formattedArray['disablebotclicks'] = isset( $options['disablebotclicks'] ) ? $options['disablebotclicks'] : false; |
| 355 |
$formattedArray['force_https'] = isset( $options['force_https'] ) ? $options['force_https'] : false; |
| 356 |
$formattedArray['autolink_disable_post_types'] = isset( $options['autolink_disable_post_types'] ) ? $options['autolink_disable_post_types'] : array(); |
| 357 |
$formattedArray['is_autolink_icon'] = isset( $options['is_autolink_icon'] ) ? $options['is_autolink_icon'] : false; |
| 358 |
$formattedArray['is_autolink_headings'] = isset( $options['is_autolink_headings'] ) ? $options['is_autolink_headings'] : false; |
| 359 |
$formattedArray['uncloaked_categories'] = isset( $options['uncloaked_categories'] ) ? $options['uncloaked_categories'] : array(); |
| 360 |
} |
| 361 |
if ( is_array( $items ) && count( $items ) > 0 ) { |
| 362 |
foreach ( $items as $item ) { |
| 363 |
$short_url = $is_links_case_sensitive ? $item->short_url : strtolower( $item->short_url ); |
| 364 |
if ( $item->wildcards == true ) { |
| 365 |
$formattedArray['wildcards'][ $short_url ] = $item; |
| 366 |
} else { |
| 367 |
$formattedArray['links'][ $short_url ] = $item; |
| 368 |
} |
| 369 |
} |
| 370 |
} |
| 371 |
if ( defined( 'BETTERLINKS_PRO_EXTERNAL_ANALYTICS_OPTION_NAME' ) && BETTERLINKS_PRO_EXTERNAL_ANALYTICS_OPTION_NAME ) { |
| 372 |
$analytic_data = get_option( BETTERLINKS_PRO_EXTERNAL_ANALYTICS_OPTION_NAME, array() ); |
| 373 |
if ( is_array( $analytic_data ) ) { |
| 374 |
$formattedArray = array_merge( $analytic_data, $formattedArray ); |
| 375 |
} else { |
| 376 |
$analytic_data = is_string( $analytic_data ) ? json_decode( $analytic_data, true ) : array(); |
| 377 |
$formattedArray = array_merge( $analytic_data, $formattedArray ); |
| 378 |
} |
| 379 |
} |
| 380 |
return $formattedArray; |
| 381 |
} |
| 382 |
|
| 383 |
public static function insert_term( $item, $is_update = false ) { |
| 384 |
global $wpdb; |
| 385 |
if ( $is_update ) { |
| 386 |
$wpdb->update( |
| 387 |
"{$wpdb->prefix}betterlinks_terms", |
| 388 |
array( |
| 389 |
'term_name' => $item['term_name'], |
| 390 |
'term_slug' => $item['term_slug'], |
| 391 |
'term_type' => $item['term_type'], |
| 392 |
), |
| 393 |
array( 'ID' => $item['ID'] ), |
| 394 |
array( |
| 395 |
'%s', |
| 396 |
'%s', |
| 397 |
'%s', |
| 398 |
), |
| 399 |
array( '%d' ) |
| 400 |
); |
| 401 |
return $item['ID']; |
| 402 |
} else { |
| 403 |
$terms = self::get_term_by_slug( $item['term_slug'], $item['term_type'] ); |
| 404 |
if ( count( $terms ) === 0 ) { |
| 405 |
$wpdb->query( |
| 406 |
$wpdb->prepare( |
| 407 |
"INSERT INTO {$wpdb->prefix}betterlinks_terms ( term_name, term_slug, term_type ) VALUES ( %s, %s, %s )", |
| 408 |
array( $item['term_name'], $item['term_slug'], $item['term_type'] ) |
| 409 |
) |
| 410 |
); |
| 411 |
return $wpdb->insert_id; |
| 412 |
} elseif ( isset( current( $terms )['ID'] ) ) { |
| 413 |
return current( $terms )['ID']; |
| 414 |
} |
| 415 |
} |
| 416 |
return; |
| 417 |
} |
| 418 |
public static function insert_tags_terms( $tags ) { |
| 419 |
$terms_ids = array(); |
| 420 |
if ( is_array( $tags ) && count( $tags ) > 0 ) { |
| 421 |
foreach ( $tags as $tag ) { |
| 422 |
$insert_id = self::insert_term( |
| 423 |
array( |
| 424 |
'term_name' => $tag, |
| 425 |
'term_slug' => \BetterLinks\Helper::make_slug( $tag ), |
| 426 |
'term_type' => 'tags', |
| 427 |
) |
| 428 |
); |
| 429 |
if ( $insert_id ) { |
| 430 |
$terms_ids[] = $insert_id; |
| 431 |
} |
| 432 |
} |
| 433 |
} |
| 434 |
return $terms_ids; |
| 435 |
} |
| 436 |
|
| 437 |
public static function insert_category_terms( $categories ) { |
| 438 |
$terms_ids = array(); |
| 439 |
if ( is_array( $categories ) && count( $categories ) > 0 ) { |
| 440 |
foreach ( $categories as $category ) { |
| 441 |
$insert_id = self::insert_term( |
| 442 |
array( |
| 443 |
'term_name' => $category, |
| 444 |
'term_slug' => \BetterLinks\Helper::make_slug( $category ), |
| 445 |
'term_type' => 'category', |
| 446 |
) |
| 447 |
); |
| 448 |
if ( $insert_id ) { |
| 449 |
$terms_ids[] = $insert_id; |
| 450 |
} |
| 451 |
} |
| 452 |
} |
| 453 |
return $terms_ids; |
| 454 |
} |
| 455 |
public static function insert_terms_relationships( $term_id, $link_id ) { |
| 456 |
global $wpdb; |
| 457 |
$wpdb->query( |
| 458 |
$wpdb->prepare( |
| 459 |
"INSERT INTO {$wpdb->prefix}betterlinks_terms_relationships ( term_id, link_id ) VALUES ( %d, %d )", |
| 460 |
array( $term_id, $link_id ) |
| 461 |
) |
| 462 |
); |
| 463 |
return $wpdb->insert_id; |
| 464 |
} |
| 465 |
|
| 466 |
/** |
| 467 |
* Delete term and update Term relationship to uncategorized |
| 468 |
* |
| 469 |
* @param term_id |
| 470 |
* @return boolean |
| 471 |
*/ |
| 472 |
public static function delete_term_and_update_term_relationships( $term_id ) { |
| 473 |
global $wpdb; |
| 474 |
$wpdb->query( 'START TRANSACTION' ); |
| 475 |
$is_delete = $wpdb->delete( $wpdb->prefix . 'betterlinks_terms', array( 'ID' => $term_id ), array( '%d' ) ); |
| 476 |
if ( $is_delete ) { |
| 477 |
$term = self::get_term_by_slug( 'uncategorized' ); |
| 478 |
if ( count( $term ) > 0 ) { |
| 479 |
$wpdb->update( |
| 480 |
"{$wpdb->prefix}betterlinks_terms_relationships", |
| 481 |
array( |
| 482 |
'term_id' => current( $term )['ID'], |
| 483 |
), |
| 484 |
array( 'term_id' => $term_id ), |
| 485 |
array( |
| 486 |
'%d', |
| 487 |
), |
| 488 |
array( '%d' ) |
| 489 |
); |
| 490 |
} |
| 491 |
} |
| 492 |
$wpdb->query( 'COMMIT' ); |
| 493 |
return $is_delete; |
| 494 |
} |
| 495 |
|
| 496 |
public static function insert_terms_and_terms_relationship( $link_id, $request ) { |
| 497 |
global $wpdb; |
| 498 |
$term_data = array(); |
| 499 |
$newTermList = array(); |
| 500 |
|
| 501 |
// If no category is provided, check for default category setting. |
| 502 |
// |
| 503 |
// The stored value must be a real term ID. A non-numeric value falls |
| 504 |
// through to the "new category" branch below, which treats cat_id as a |
| 505 |
// NAME and creates a term called after it — so a boolean `true` in |
| 506 |
// settings (which is what some saves write) minted a junk category |
| 507 |
// literally named "1" on every link created without a category. |
| 508 |
if ( empty( $request['cat_id'] ) ) { |
| 509 |
$settings = json_decode( get_option( BETTERLINKS_LINKS_OPTION_NAME ), true ); |
| 510 |
$default = isset( $settings['default_category'] ) ? $settings['default_category'] : null; |
| 511 |
|
| 512 |
if ( is_numeric( $default ) && (int) $default > 0 ) { |
| 513 |
$request['cat_id'] = (int) $default; |
| 514 |
} else { |
| 515 |
// Fallback to Uncategorized category (ID 1) |
| 516 |
$request['cat_id'] = 1; |
| 517 |
} |
| 518 |
} |
| 519 |
|
| 520 |
// store tags relation data |
| 521 |
if ( ! empty( $request['cat_id'] ) ) { |
| 522 |
$is_new_cat = true; |
| 523 |
if ( is_numeric( $request['cat_id'] ) ) { |
| 524 |
$query = $wpdb->prepare( |
| 525 |
"SELECT * FROM {$wpdb->prefix}betterlinks_terms WHERE id = %d ", |
| 526 |
$request['cat_id'] |
| 527 |
); |
| 528 |
$result = $wpdb->get_row( $query, 'ARRAY_A' ); |
| 529 |
if ( isset( $result['term_slug'] ) ) { |
| 530 |
$is_new_cat = false; |
| 531 |
$term_data[] = array( |
| 532 |
'term_id' => $request['cat_id'], |
| 533 |
'link_id' => $link_id, |
| 534 |
'term_slug' => $result['term_slug'], |
| 535 |
'term_name' => $result['term_name'], |
| 536 |
'term_type' => 'category', |
| 537 |
); |
| 538 |
} |
| 539 |
} |
| 540 |
// A NUMERIC cat_id is an ID, never a name. If it did not resolve above |
| 541 |
// the term is gone (a stale default_category, say) — fall back to |
| 542 |
// Uncategorized rather than minting a category literally named "42". |
| 543 |
// Non-numeric values are genuine "user typed a new category" input and |
| 544 |
// still create a term. |
| 545 |
if ( $is_new_cat && is_numeric( $request['cat_id'] ) ) { |
| 546 |
$is_new_cat = false; |
| 547 |
$fallback = self::get_term_by_slug( 'uncategorized' ); |
| 548 |
if ( count( $fallback ) > 0 ) { |
| 549 |
$fallback = current( $fallback ); |
| 550 |
$term_data[] = array( |
| 551 |
'term_id' => $fallback['ID'], |
| 552 |
'link_id' => $link_id, |
| 553 |
'term_slug' => $fallback['term_slug'], |
| 554 |
'term_name' => $fallback['term_name'], |
| 555 |
'term_type' => 'category', |
| 556 |
); |
| 557 |
} |
| 558 |
} |
| 559 |
if ( $is_new_cat ) { |
| 560 |
$newTermList[] = array( |
| 561 |
'term_name' => $request['cat_id'], |
| 562 |
'term_slug' => isset( $request['cat_slug'] ) ? $request['cat_slug'] : $request['cat_id'], |
| 563 |
'term_type' => 'category', |
| 564 |
); |
| 565 |
} |
| 566 |
} |
| 567 |
if ( isset( $request['tags_id'] ) && is_array( $request['tags_id'] ) ) { |
| 568 |
foreach ( $request['tags_id'] as $key => $value ) { |
| 569 |
$is_new_tag = true; |
| 570 |
if ( is_numeric( $value ) ) { |
| 571 |
$query = $wpdb->prepare( |
| 572 |
"SELECT * FROM {$wpdb->prefix}betterlinks_terms WHERE id = %d ", |
| 573 |
$value |
| 574 |
); |
| 575 |
$result = $wpdb->get_row( $query, 'ARRAY_A' ); |
| 576 |
if ( isset( $result['term_slug'] ) ) { |
| 577 |
$term_data[] = array( |
| 578 |
'link_id' => $link_id, |
| 579 |
'term_id' => $value, |
| 580 |
'term_slug' => $result['term_slug'], |
| 581 |
'term_name' => $result['term_name'], |
| 582 |
'term_type' => 'tags', |
| 583 |
); |
| 584 |
$is_new_tag = false; |
| 585 |
} |
| 586 |
} |
| 587 |
if ( $is_new_tag ) { |
| 588 |
$newTermList[] = array( |
| 589 |
'term_name' => $value, |
| 590 |
'term_slug' => $value, |
| 591 |
'term_type' => 'tags', |
| 592 |
); |
| 593 |
} |
| 594 |
} |
| 595 |
} |
| 596 |
|
| 597 |
// insert new tags or category |
| 598 |
if ( count( $newTermList ) > 0 ) { |
| 599 |
foreach ( $newTermList as $item ) { |
| 600 |
$term_id = \BetterLinks\Helper::insert_term( $item ); |
| 601 |
$term_data[] = array( |
| 602 |
'link_id' => $link_id, |
| 603 |
'term_id' => $term_id, |
| 604 |
'term_type' => $item['term_type'], |
| 605 |
'term_name' => $item['term_name'], |
| 606 |
'term_slug' => $item['term_slug'], |
| 607 |
'is_newly_created' => true, |
| 608 |
); |
| 609 |
} |
| 610 |
} |
| 611 |
// make term and link relation |
| 612 |
if ( count( $term_data ) > 0 ) { |
| 613 |
$is_delete = $wpdb->delete( $wpdb->prefix . 'betterlinks_terms_relationships', array( 'link_id' => $link_id ), array( '%d' ) ); |
| 614 |
if ( $is_delete || $is_delete === 0 ) { |
| 615 |
foreach ( $term_data as $term ) { |
| 616 |
\BetterLinks\Helper::insert_terms_relationships( $term['term_id'], $term['link_id'] ); |
| 617 |
} |
| 618 |
} |
| 619 |
} |
| 620 |
return $term_data; |
| 621 |
} |
| 622 |
|
| 623 |
public static function is_term_exists( $term_id, $type = 'category' ) { |
| 624 |
global $wpdb; |
| 625 |
$result = $wpdb->get_results( |
| 626 |
$wpdb->prepare( "SELECT * FROM {$wpdb->prefix}betterlinks_terms WHERE ID=%s AND term_type=%s", $term_id, $type ), |
| 627 |
ARRAY_A |
| 628 |
); |
| 629 |
return count( $result ) === 1; |
| 630 |
} |
| 631 |
|
| 632 |
public static function get_term_by_slug( $slug, $type = 'category' ) { |
| 633 |
global $wpdb; |
| 634 |
$result = $wpdb->get_results( |
| 635 |
$wpdb->prepare( "SELECT * FROM {$wpdb->prefix}betterlinks_terms WHERE term_slug=%s AND term_type=%s", $slug, $type ), |
| 636 |
ARRAY_A |
| 637 |
); |
| 638 |
return $result; |
| 639 |
} |
| 640 |
|
| 641 |
// Get term by ID for AI |
| 642 |
public static function get_term_by_id( $term_id, $type = 'category' ) { |
| 643 |
global $wpdb; |
| 644 |
$result = $wpdb->get_results( |
| 645 |
$wpdb->prepare( "SELECT * FROM {$wpdb->prefix}betterlinks_terms WHERE ID=%d AND term_type=%s", $term_id, $type ), |
| 646 |
ARRAY_A |
| 647 |
); |
| 648 |
return $result; |
| 649 |
} |
| 650 |
|
| 651 |
public static function get_terms_by_link_ID_and_term_type( $link_ID, $term_type = 'categroy' ) { |
| 652 |
global $wpdb; |
| 653 |
$prefix = $wpdb->prefix; |
| 654 |
$link = $wpdb->get_results( |
| 655 |
$wpdb->prepare( |
| 656 |
"SELECT |
| 657 |
{$prefix}betterlinks_terms.ID as term_id, |
| 658 |
{$prefix}betterlinks_terms.term_name, |
| 659 |
{$prefix}betterlinks_terms.term_slug, |
| 660 |
{$prefix}betterlinks_terms.term_type |
| 661 |
FROM {$prefix}betterlinks_terms |
| 662 |
LEFT JOIN {$prefix}betterlinks_terms_relationships ON {$prefix}betterlinks_terms.ID = {$prefix}betterlinks_terms_relationships.term_id |
| 663 |
LEFT JOIN {$prefix}betterlinks ON {$prefix}betterlinks.ID = {$prefix}betterlinks_terms_relationships.link_id |
| 664 |
WHERE {$prefix}betterlinks_terms_relationships.link_id = %d |
| 665 |
AND {$prefix}betterlinks_terms.term_type = %s", |
| 666 |
$link_ID, |
| 667 |
$term_type |
| 668 |
), |
| 669 |
ARRAY_A |
| 670 |
); |
| 671 |
return $link; |
| 672 |
} |
| 673 |
|
| 674 |
public static function get_terms_all_data() { |
| 675 |
global $wpdb; |
| 676 |
$query = "SELECT t.ID, t.term_name, t.term_slug, t.term_type, t.term_order, COALESCE(tr.link_count, 0) as link_count FROM {$wpdb->prefix}betterlinks_terms AS t LEFT JOIN (SELECT term_id, COUNT(term_id) AS link_count FROM {$wpdb->prefix}betterlinks_terms_relationships GROUP BY term_id) AS tr ON t.ID=tr.term_id ORDER BY t.term_order ASC, t.term_name ASC"; |
| 677 |
$link = $wpdb->get_results( $query, ARRAY_A ); |
| 678 |
return $link; |
| 679 |
} |
| 680 |
|
| 681 |
public static function insert_click( $item ) { |
| 682 |
global $wpdb; |
| 683 |
$betterlinks = array(); |
| 684 |
$is_extra_data_tracking_compatible = apply_filters( 'betterlinks/is_extra_data_tracking_compatible', false ); |
| 685 |
if ( isset( $item['short_url'] ) ) { |
| 686 |
$betterlinks = self::get_link_by_short_url( $item['short_url'] ); |
| 687 |
} elseif ( isset( $item['link_id'] ) ) { |
| 688 |
$betterlinks = self::get_link_by_ID( $item['link_id'] ); |
| 689 |
} |
| 690 |
if( empty( $betterlinks ) ){ |
| 691 |
return; |
| 692 |
} |
| 693 |
$is_analytics_ip_enabled = isset( $item['ip'] ) && isset( $item['host'] ); |
| 694 |
$is_country_enabled = isset( $item['country_code'] ) && isset( $item['country_name'] ); |
| 695 |
|
| 696 |
$addedPlaceholderString = $is_analytics_ip_enabled ? ' created_at_gmt, rotation_target_url, ip, host ' : ' created_at_gmt, rotation_target_url '; |
| 697 |
$addedDbColumnsString = $is_analytics_ip_enabled ? ' %s, %s, %s, %s ' : ' %s, %s '; |
| 698 |
|
| 699 |
// Use country_id for normalized schema |
| 700 |
$country_id = null; |
| 701 |
if ( $is_country_enabled ) { |
| 702 |
// Get or create country record |
| 703 |
$country_id = \BetterLinks\Services\CountryDetectionService::get_or_create_country_id( |
| 704 |
$item['country_code'], |
| 705 |
$item['country_name'] |
| 706 |
); |
| 707 |
|
| 708 |
if ( $country_id ) { |
| 709 |
$addedPlaceholderString .= ', country_id'; |
| 710 |
$addedDbColumnsString .= ', %d'; |
| 711 |
} |
| 712 |
} |
| 713 |
// Check if user agent tracking is enabled AND column exists |
| 714 |
$settings = get_option( BETTERLINKS_LINKS_OPTION_NAME, '[]' ); |
| 715 |
if ( is_string( $settings ) ) { |
| 716 |
$settings = json_decode( $settings, true ); |
| 717 |
} |
| 718 |
$is_user_agent_tracking_enabled = ! empty( $settings['enable_user_agent_tracking'] ); |
| 719 |
|
| 720 |
// Cache the column existence check to avoid querying information_schema on every redirect |
| 721 |
$user_agent_column_exists = self::get_user_agent_column_exists(); |
| 722 |
|
| 723 |
// Handle user agent if tracking is enabled, column exists, and user agent is provided |
| 724 |
$user_agent_id = null; |
| 725 |
$should_include_user_agent = $is_user_agent_tracking_enabled && $user_agent_column_exists && isset( $item['user_agent'] ); |
| 726 |
if ( $should_include_user_agent ) { |
| 727 |
$user_agent_id = self::get_or_insert_user_agent_id( $item['user_agent'] ); |
| 728 |
} // Pro's extra-data tracking already carries bot_name in its column block; |
| 729 |
// on free, write it on its own so the human-vs-bot split has data there too. |
| 730 |
$should_include_bot_name = ! $is_extra_data_tracking_compatible && \BetterLinks\Helper::has_bot_name_column(); |
| 731 |
|
| 732 |
if ( $is_extra_data_tracking_compatible ) { |
| 733 |
$addedPlaceholderString .= ', brand_name, model, bot_name, browser_type, os_version, browser_version, language, query_params'; |
| 734 |
$addedDbColumnsString .= ', %s, %s, %s, %s, %s, %s, %s, %s'; |
| 735 |
} elseif ( $should_include_bot_name ) { |
| 736 |
$addedPlaceholderString .= ', bot_name'; |
| 737 |
$addedDbColumnsString .= ', %s'; |
| 738 |
} |
| 739 |
|
| 740 |
// Add user agent ID to the query only if column exists |
| 741 |
if ( $should_include_user_agent ) { |
| 742 |
$addedPlaceholderString .= ', user_agent_id'; |
| 743 |
$addedDbColumnsString .= ', %s'; |
| 744 |
} |
| 745 |
|
| 746 |
if( empty($betterlinks) || empty( current( $betterlinks )['ID'] ) ) return; |
| 747 |
$query = "INSERT INTO {$wpdb->prefix}betterlinks_clicks ( link_id, browser, os,device, referer, uri, click_count, visitor_id, click_order, created_at, $addedPlaceholderString ) VALUES ( %d, %s, %s, %s, %s, %s, %d, %s, %d, %s, $addedDbColumnsString )"; |
| 748 |
$db_data_array = array( |
| 749 |
current( $betterlinks )['ID'], |
| 750 |
isset( $item['browser'] ) ? $item['browser'] : '', |
| 751 |
isset( $item['os'] ) ? $item['os'] : '', |
| 752 |
isset( $item['device'] ) ? $item['device'] : '', |
| 753 |
isset( $item['referer'] ) ? $item['referer'] : '', |
| 754 |
isset( $item['uri'] ) ? $item['uri'] : '', |
| 755 |
isset( $item['click_count'] ) ? $item['click_count'] : 0, |
| 756 |
isset( $item['visitor_id'] ) ? $item['visitor_id'] : '', |
| 757 |
isset( $item['click_order'] ) ? $item['click_order'] : '', |
| 758 |
isset( $item['created_at']) ? $item['created_at'] : '', |
| 759 |
isset( $item['created_at_gmt']) ? $item['created_at_gmt'] : '', |
| 760 |
isset( $item['rotation_target_url']) ? $item['rotation_target_url'] : '', |
| 761 |
); |
| 762 |
if ( $is_analytics_ip_enabled ) { |
| 763 |
$db_data_array[] = isset( $item['ip'] ) ? $item['ip'] : ''; |
| 764 |
$db_data_array[] = isset( $item['host'] ) ? $item['host'] : ''; |
| 765 |
} |
| 766 |
|
| 767 |
if ( $is_country_enabled && $country_id ) { |
| 768 |
$db_data_array[] = $country_id; |
| 769 |
} |
| 770 |
// $db_data_array[] = isset($item['device']) ? $item['device'] : ''; |
| 771 |
if ( $is_extra_data_tracking_compatible ) { |
| 772 |
$db_data_array[] = isset( $item['brand_name'] ) ? $item['brand_name'] : ''; |
| 773 |
$db_data_array[] = isset( $item['model'] ) ? $item['model'] : ''; |
| 774 |
$db_data_array[] = isset( $item['bot_name'] ) ? $item['bot_name'] : ''; |
| 775 |
$db_data_array[] = isset( $item['browser_type'] ) ? $item['browser_type'] : ''; |
| 776 |
$db_data_array[] = isset( $item['os_version'] ) ? $item['os_version'] : ''; |
| 777 |
$db_data_array[] = isset( $item['browser_version'] ) ? $item['browser_version'] : ''; |
| 778 |
$db_data_array[] = isset( $item['language'] ) ? $item['language'] : ''; |
| 779 |
$db_data_array[] = isset( $item['query_params'] ) ? $item['query_params'] : ''; |
| 780 |
} elseif ( $should_include_bot_name ) { |
| 781 |
$db_data_array[] = isset( $item['bot_name'] ) ? $item['bot_name'] : ''; |
| 782 |
} |
| 783 |
|
| 784 |
// Add user agent ID to data array only if column exists |
| 785 |
if ( $should_include_user_agent ) { |
| 786 |
$db_data_array[] = $user_agent_id; |
| 787 |
} |
| 788 |
|
| 789 |
if ( isset( current( $betterlinks )['ID'] ) ) { |
| 790 |
$wpdb->query( |
| 791 |
$wpdb->prepare( $query, $db_data_array ) |
| 792 |
); |
| 793 |
return $wpdb->insert_id; |
| 794 |
} |
| 795 |
return; |
| 796 |
} |
| 797 |
|
| 798 |
public static function get_or_insert_user_agent_id( $user_agent ) { |
| 799 |
global $wpdb; |
| 800 |
|
| 801 |
if ( empty( $user_agent ) ) { |
| 802 |
return null; |
| 803 |
} |
| 804 |
|
| 805 |
// Check if user_agents table exists first |
| 806 |
$user_agents_table_exists = $wpdb->get_var( |
| 807 |
$wpdb->prepare( |
| 808 |
"SHOW TABLES LIKE %s", |
| 809 |
$wpdb->prefix . 'betterlinks_user_agents' |
| 810 |
) |
| 811 |
); |
| 812 |
|
| 813 |
if ( ! $user_agents_table_exists ) { |
| 814 |
return null; // Table doesn't exist, return null gracefully |
| 815 |
} |
| 816 |
|
| 817 |
// First try to get existing user agent ID |
| 818 |
$existing_id = $wpdb->get_var( |
| 819 |
$wpdb->prepare( |
| 820 |
"SELECT id FROM {$wpdb->prefix}betterlinks_user_agents WHERE user_agent = %s LIMIT 1", |
| 821 |
$user_agent |
| 822 |
) |
| 823 |
); |
| 824 |
|
| 825 |
if ( $existing_id ) { |
| 826 |
return (int) $existing_id; |
| 827 |
} |
| 828 |
|
| 829 |
// Insert new user agent if not exists |
| 830 |
$result = $wpdb->query( |
| 831 |
$wpdb->prepare( |
| 832 |
"INSERT IGNORE INTO {$wpdb->prefix}betterlinks_user_agents (user_agent) VALUES (%s)", |
| 833 |
$user_agent |
| 834 |
) |
| 835 |
); |
| 836 |
|
| 837 |
if ( $result ) { |
| 838 |
return (int) $wpdb->insert_id; |
| 839 |
} |
| 840 |
|
| 841 |
// If INSERT IGNORE failed due to race condition, try to get ID again |
| 842 |
$existing_id = $wpdb->get_var( |
| 843 |
$wpdb->prepare( |
| 844 |
"SELECT id FROM {$wpdb->prefix}betterlinks_user_agents WHERE user_agent = %s LIMIT 1", |
| 845 |
$user_agent |
| 846 |
) |
| 847 |
); |
| 848 |
|
| 849 |
return $existing_id ? (int) $existing_id : null; |
| 850 |
} |
| 851 |
|
| 852 |
public static function get_linksNips_count() { |
| 853 |
global $wpdb; |
| 854 |
|
| 855 |
$query = "select link_id, ip, ipc, t2.lidc from ( select ip, link_id, count(ip) as ipc from {$wpdb->prefix}betterlinks_clicks group by ip, link_id ) as t1 |
| 856 |
left join ( select link_id as lid, sum(ipc) as lidc from ( select ip, link_id, count(uri) as ipc from {$wpdb->prefix}betterlinks_clicks group by ip,uri, link_id ) as t3 group by link_id ) as t2 |
| 857 |
on t1.link_id = t2.lid"; |
| 858 |
|
| 859 |
$results = $wpdb->get_results( $query, ARRAY_A ); |
| 860 |
return $results; |
| 861 |
} |
| 862 |
|
| 863 |
public static function get_clicks_count($from = '', $to = '') { |
| 864 |
global $wpdb; |
| 865 |
|
| 866 |
// Get excluded IPs and build condition safely |
| 867 |
$options = json_decode( get_option( BETTERLINKS_LINKS_OPTION_NAME ), true ); |
| 868 |
$excluded_ips = isset( $options['excluded_ips'] ) && is_array( $options['excluded_ips'] ) ? $options['excluded_ips'] : array(); |
| 869 |
|
| 870 |
$where_conditions = array(); |
| 871 |
$query_params = array(); |
| 872 |
|
| 873 |
// Add date range condition |
| 874 |
if ( '' !== $from && '' !== $to ) { |
| 875 |
$where_conditions[] = 'created_at BETWEEN %s AND %s'; |
| 876 |
$query_params[] = $from . ' 00:00:00'; |
| 877 |
$query_params[] = $to . ' 23:59:59'; |
| 878 |
} |
| 879 |
|
| 880 |
// Add excluded IPs condition |
| 881 |
if ( ! empty( $excluded_ips ) ) { |
| 882 |
$placeholders = implode( ', ', array_fill( 0, count( $excluded_ips ), '%s' ) ); |
| 883 |
$where_conditions[] = "ip NOT IN ({$placeholders})"; |
| 884 |
$query_params = array_merge( $query_params, $excluded_ips ); |
| 885 |
} |
| 886 |
|
| 887 |
$where_clause = ! empty( $where_conditions ) ? 'WHERE ' . implode( ' AND ', $where_conditions ) : ''; |
| 888 |
|
| 889 |
// Total clicks query. ORDER BY keeps this aligned with the unique query below; |
| 890 |
// callers must still merge the two sets on link_id, never on row position. |
| 891 |
$total_query = "SELECT link_id, count(id) as total_clicks from {$wpdb->prefix}betterlinks_clicks {$where_clause} group by link_id ORDER BY link_id"; |
| 892 |
$total_clicks = ! empty( $query_params ) ? $wpdb->get_results( $wpdb->prepare( $total_query, $query_params ), ARRAY_A ) : $wpdb->get_results( $total_query, ARRAY_A ); |
| 893 |
|
| 894 |
// Unique clicks query |
| 895 |
$unique_query = "SELECT T1.link_id, count(ip) as unique_clicks from ( SELECT ip, link_id FROM {$wpdb->prefix}betterlinks_clicks {$where_clause} GROUP BY `ip`, `link_id` ) as T1 GROUP BY T1.link_id ORDER BY T1.link_id"; |
| 896 |
$unique_clicks = ! empty( $query_params ) ? $wpdb->get_results( $wpdb->prepare( $unique_query, $query_params ), ARRAY_A ) : $wpdb->get_results( $unique_query, ARRAY_A ); |
| 897 |
|
| 898 |
return array( |
| 899 |
'total_clicks' => $total_clicks, |
| 900 |
'unique_clicks' => $unique_clicks, |
| 901 |
); |
| 902 |
} |
| 903 |
|
| 904 |
public static function get_links_analytics() { |
| 905 |
global $wpdb; |
| 906 |
$prefix = $wpdb->prefix; |
| 907 |
$results = $wpdb->get_results( |
| 908 |
"SELECT DISTINCT link_id, ip, |
| 909 |
(select count(ip) from {$prefix}betterlinks_clicks WHERE CLICKS.ip = {$prefix}betterlinks_clicks.ip group by ip) as IPCOUNT, |
| 910 |
(select count(link_id) from {$prefix}betterlinks_clicks WHERE CLICKS.link_id = {$prefix}betterlinks_clicks.link_id group by link_id) as LINKCOUNT |
| 911 |
from {$prefix}betterlinks_clicks as CLICKS group by CLICKS.id", |
| 912 |
ARRAY_A |
| 913 |
); |
| 914 |
return $results; |
| 915 |
} |
| 916 |
|
| 917 |
public static function clear_analytics_cache() { |
| 918 |
global $wpdb; |
| 919 |
$prefix = $wpdb->prefix; |
| 920 |
$individual_analytics_cache_keys = 'btl_individual_analytics_clicks_|btl_individual_graph_data_'; |
| 921 |
// Every btl_analytics_* transient belongs here — one left out keeps serving |
| 922 |
// figures from before the clicks changed until its own 30-minute TTL runs |
| 923 |
// out, which reads as "the report is broken". |
| 924 |
$all_analytics_cache_keys = 'betterlinks_analytics_data|btl_analytics_unique_list_|btl_analytics_unique_list_by_tag_|btl_analytics_graph_|btl_analytics_graph_by_tag_|btl_analytics_audience_|btl_analytics_timing_|btl_top_referer_|btl_click_stats_|btl_top_os_|btl_top_browser_|btl_all_referer_|btl_tags_analytics|btl_categories_analytics|btl_analytics_data_|btl_unique_clicks_count_'; |
| 925 |
$query = "DELETE FROM {$prefix}options WHERE option_name regexp '{$individual_analytics_cache_keys}|{$all_analytics_cache_keys}'"; |
| 926 |
|
| 927 |
$result = $wpdb->query( $query ); |
| 928 |
return $result; |
| 929 |
} |
| 930 |
|
| 931 |
public static function search_clicks_data( $keyword ) { |
| 932 |
global $wpdb; |
| 933 |
$prefix = $wpdb->prefix; |
| 934 |
$is_extra_data_tracking_compatible = apply_filters( 'betterlinks/is_extra_data_tracking_compatible', false ); |
| 935 |
$extra_data_tracking_columns = $is_extra_data_tracking_compatible ? 'os, device, brand_name, ' : ''; |
| 936 |
$results = $wpdb->get_results( |
| 937 |
$wpdb->prepare( |
| 938 |
"SELECT CLICKS.ID as |
| 939 |
click_ID, link_id, browser, {$extra_data_tracking_columns} created_at, referer, SUBSTRING_INDEX(SUBSTRING_INDEX(referer, '/', 3), '/', -1) AS domain, short_url, target_url, ip, {$prefix}betterlinks.link_title, |
| 940 |
(select count(id) from {$prefix}betterlinks_clicks where CLICKS.ip = {$prefix}betterlinks_clicks.ip group by ip) as IPCOUNT |
| 941 |
from {$prefix}betterlinks_clicks as CLICKS left join {$prefix}betterlinks on {$prefix}betterlinks.id = CLICKS.link_id WHERE {$prefix}betterlinks.link_title LIKE %s |
| 942 |
or {$prefix}betterlinks.short_url like %s |
| 943 |
or {$prefix}betterlinks.target_url like %s |
| 944 |
or CLICKS.browser like %s |
| 945 |
or CLICKS.ip like %s |
| 946 |
or CLICKS.referer like %s |
| 947 |
group by CLICKS.id ORDER BY CLICKS.created_at DESC", |
| 948 |
'%' . $keyword . '%', |
| 949 |
'%' . $keyword . '%', |
| 950 |
'%' . $keyword . '%', |
| 951 |
'%' . $keyword . '%', |
| 952 |
'%' . $keyword . '%', |
| 953 |
'%' . $keyword . '%' |
| 954 |
), |
| 955 |
ARRAY_A |
| 956 |
); |
| 957 |
return $results; |
| 958 |
} |
| 959 |
|
| 960 |
public static function get_clicks_by_date( $from, $to ) { |
| 961 |
global $wpdb; |
| 962 |
$prefix = $wpdb->prefix; |
| 963 |
$is_extra_data_tracking_compatible = apply_filters( 'betterlinks/is_extra_data_tracking_compatible', false ); |
| 964 |
$extra_data_tracking_columns = $is_extra_data_tracking_compatible ? 'CLICKS.os, CLICKS.device, CLICKS.brand_name, ' : ''; |
| 965 |
|
| 966 |
// Get excluded IPs and build condition safely |
| 967 |
$options = json_decode( get_option( BETTERLINKS_LINKS_OPTION_NAME ), true ); |
| 968 |
$excluded_ips = isset( $options['excluded_ips'] ) && is_array( $options['excluded_ips'] ) ? $options['excluded_ips'] : array(); |
| 969 |
|
| 970 |
$query_params = array( $from . ' 00:00:00', $to . ' 23:59:00' ); |
| 971 |
$where_conditions = array( 'CLICKS.created_at BETWEEN %s AND %s' ); |
| 972 |
|
| 973 |
if ( ! empty( $excluded_ips ) ) { |
| 974 |
$placeholders = implode( ', ', array_fill( 0, count( $excluded_ips ), '%s' ) ); |
| 975 |
$where_conditions[] = "CLICKS.ip NOT IN ({$placeholders})"; |
| 976 |
$query_params = array_merge( $query_params, $excluded_ips ); |
| 977 |
} |
| 978 |
|
| 979 |
$where_clause = implode( ' AND ', $where_conditions ); |
| 980 |
|
| 981 |
$query = "SELECT |
| 982 |
CLICKS.ID AS click_ID, |
| 983 |
CLICKS.link_id, |
| 984 |
CLICKS.browser, |
| 985 |
{$extra_data_tracking_columns} |
| 986 |
CLICKS.created_at, |
| 987 |
CLICKS.referer, |
| 988 |
SUBSTRING_INDEX(SUBSTRING_INDEX(CLICKS.referer, '/', 3), '/', -1) AS domain, |
| 989 |
{$prefix}betterlinks.short_url, |
| 990 |
{$prefix}betterlinks.target_url, |
| 991 |
CLICKS.ip, |
| 992 |
{$prefix}betterlinks.link_title |
| 993 |
FROM |
| 994 |
{$prefix}betterlinks_clicks AS CLICKS |
| 995 |
LEFT JOIN {$prefix}betterlinks ON {$prefix}betterlinks.id = CLICKS.link_id |
| 996 |
WHERE |
| 997 |
{$where_clause} |
| 998 |
GROUP BY |
| 999 |
CLICKS.id |
| 1000 |
ORDER BY |
| 1001 |
CLICKS.created_at DESC limit 100000"; |
| 1002 |
|
| 1003 |
$results = $wpdb->get_results( $wpdb->prepare( $query, $query_params ), ARRAY_A ); |
| 1004 |
return $results; |
| 1005 |
} |
| 1006 |
|
| 1007 |
|
| 1008 |
public static function get_thirstyaffiliates_links() { |
| 1009 |
$thirstylinks = get_posts( |
| 1010 |
array( |
| 1011 |
'posts_per_page' => -1, |
| 1012 |
'post_type' => 'thirstylink', |
| 1013 |
'post_status' => 'publish', |
| 1014 |
) |
| 1015 |
); |
| 1016 |
$response = array(); |
| 1017 |
$betterlinks_links = json_decode( get_option( 'betterlinks_links', '{}' ), true ); |
| 1018 |
foreach ( $thirstylinks as $thirstylink ) { |
| 1019 |
$term = wp_get_post_terms( $thirstylink->ID, 'thirstylink-category', array( 'fields' => 'names' ) ); |
| 1020 |
$nofollow = get_post_meta( $thirstylink->ID, '_ta_no_follow', true ); |
| 1021 |
$nofollow = ( $nofollow == 'global' ? get_option( 'ta_no_follow', true ) : $nofollow ); |
| 1022 |
$redirect_type = get_post_meta( $thirstylink->ID, '_ta_redirect_type', true ); |
| 1023 |
$redirect_type = ( $redirect_type == 'global' ? get_option( 'ta_link_redirect_type', true ) : $redirect_type ); |
| 1024 |
$param_forwarding = get_post_meta( $thirstylink->ID, '_ta_pass_query_str', true ); |
| 1025 |
$param_forwarding = ( $param_forwarding == 'global' ? get_option( 'ta_pass_query_str', true ) : $param_forwarding ); |
| 1026 |
$dynamic_redirect = array(); |
| 1027 |
$geolocation_links = get_post_meta( $thirstylink->ID, '_ta_geolocation_links', true ); |
| 1028 |
if ( $geolocation_links && is_array( $geolocation_links ) ) { |
| 1029 |
$dynamic_redirect_value = array(); |
| 1030 |
foreach ( $geolocation_links as $key => $geolocation_link ) { |
| 1031 |
$dynamic_redirect_value[] = array( |
| 1032 |
'link' => $geolocation_link, |
| 1033 |
'country' => explode( ',', $key ), |
| 1034 |
); |
| 1035 |
} |
| 1036 |
$dynamic_redirect = array( |
| 1037 |
'type' => 'geographic', |
| 1038 |
'value' => $dynamic_redirect_value, |
| 1039 |
'extra' => array(), |
| 1040 |
); |
| 1041 |
} |
| 1042 |
$link_date = get_post_meta( $thirstylink->ID, '_ta_link_start_date', true ); |
| 1043 |
// expire |
| 1044 |
$expire = array(); |
| 1045 |
$expire_date = get_post_meta( $thirstylink->ID, '_ta_link_expire_date', true ); |
| 1046 |
$expire_redirect_url = get_post_meta( $thirstylink->ID, '_ta_after_expire_redirect', true ); |
| 1047 |
if ( ! empty( $expire_date ) ) { |
| 1048 |
$expire = array( |
| 1049 |
'status' => 1, |
| 1050 |
'type' => 'date', |
| 1051 |
'date' => $expire_date, |
| 1052 |
); |
| 1053 |
} |
| 1054 |
if ( ! empty( $expire_redirect_url ) ) { |
| 1055 |
$expire['redirect_status'] = 1; |
| 1056 |
$expire['redirect_url'] = $expire_redirect_url; |
| 1057 |
} |
| 1058 |
// link status |
| 1059 |
$link_status = 'publish'; |
| 1060 |
$now = time(); |
| 1061 |
if ( ! empty( $link_date ) && $now < strtotime( $link_date ) ) { |
| 1062 |
$link_status = 'scheduled'; |
| 1063 |
} |
| 1064 |
if ( ! empty( $expire_date ) && $now > strtotime( $expire_date ) ) { |
| 1065 |
$link_status = 'draft'; |
| 1066 |
} |
| 1067 |
// keywords |
| 1068 |
$keywords = get_post_meta( $thirstylink->ID, '_ta_autolink_keyword_list', true ); |
| 1069 |
$limit = get_post_meta( $thirstylink->ID, '_ta_autolink_keyword_limit', true ); |
| 1070 |
$response[] = array( |
| 1071 |
'link_title' => $thirstylink->post_title, |
| 1072 |
'link_slug' => $thirstylink->post_name, |
| 1073 |
'link_date' => $link_date ? $link_date : '', |
| 1074 |
'link_date_gmt' => $link_date ? $link_date : '', |
| 1075 |
'link_status' => $link_status, |
| 1076 |
'short_url' => trim( \BetterLinks\Helper::force_relative_url( get_the_permalink( $thirstylink->ID ) ), '/' ), |
| 1077 |
'link_author' => $thirstylink->post_author, |
| 1078 |
'link_date' => $thirstylink->post_date, |
| 1079 |
'link_date_gmt' => $thirstylink->post_date_gmt, |
| 1080 |
'nofollow' => ( $nofollow == 'yes' ? 1 : 0 ), |
| 1081 |
'sponsored' => $betterlinks_links['sponsored'], |
| 1082 |
'track_me' => $betterlinks_links['track_me'], |
| 1083 |
'redirect_type' => $redirect_type, |
| 1084 |
'param_forwarding' => ( $param_forwarding == 'yes' ? 1 : 0 ), |
| 1085 |
'target_url' => get_post_meta( $thirstylink->ID, '_ta_destination_url', true ), |
| 1086 |
'link_modified' => $thirstylink->post_modified, |
| 1087 |
'link_modified_gmt' => $thirstylink->post_modified_gmt, |
| 1088 |
'terms' => $term, |
| 1089 |
'expire' => json_encode( $expire ), |
| 1090 |
'dynamic_redirect' => json_encode( $dynamic_redirect ), |
| 1091 |
'keywords' => $keywords, |
| 1092 |
'limit' => $limit, |
| 1093 |
); |
| 1094 |
} |
| 1095 |
return $response; |
| 1096 |
} |
| 1097 |
|
| 1098 |
public static function get_prettylinks_links_count() { |
| 1099 |
global $wpdb; |
| 1100 |
$links = $wpdb->get_var( "SELECT COUNT(id) FROM {$wpdb->prefix}prli_links" ); |
| 1101 |
return $links; |
| 1102 |
} |
| 1103 |
public static function get_prettylinks_clicks_count() { |
| 1104 |
global $wpdb; |
| 1105 |
$clicks = $wpdb->get_var( "SELECT COUNT(id) FROM {$wpdb->prefix}prli_clicks" ); |
| 1106 |
return $clicks; |
| 1107 |
} |
| 1108 |
|
| 1109 |
public static function get_link_meta( $link_id, $meta_key ) { |
| 1110 |
global $wpdb; |
| 1111 |
$table = $wpdb->prefix . 'betterlinkmeta'; |
| 1112 |
if ( empty( $link_id ) || empty( $meta_key ) ) { |
| 1113 |
return false; |
| 1114 |
} |
| 1115 |
$query = $wpdb->prepare( "SELECT meta_value FROM $table WHERE meta_key=%s AND link_id=%d", $meta_key, $link_id ); |
| 1116 |
$results = $wpdb->get_results( $query ); |
| 1117 |
if ( ! empty( $results ) ) { |
| 1118 |
if ( is_serialized( current( $results )->meta_value, true ) ) { |
| 1119 |
return current( $results )->meta_value; |
| 1120 |
} |
| 1121 |
if ( is_string( current( $results )->meta_value ) ) { |
| 1122 |
return json_decode( current( $results )->meta_value ); |
| 1123 |
} |
| 1124 |
|
| 1125 |
return json_decode( current( $results )->meta_value ); |
| 1126 |
} |
| 1127 |
return false; |
| 1128 |
} |
| 1129 |
|
| 1130 |
public static function add_link_meta( $link_id, $meta_key, $meta_value ) { |
| 1131 |
global $wpdb; |
| 1132 |
$meta_key = wp_unslash( $meta_key ); |
| 1133 |
$meta_value = wp_unslash( $meta_value ); |
| 1134 |
|
| 1135 |
if ( isset( $meta_value['keywords'] ) ) { |
| 1136 |
$meta_value['keywords'] = preg_replace( '/\’|\'|\‘/', "'", $meta_value['keywords'] ); |
| 1137 |
} |
| 1138 |
$meta_value = \BetterLinks\Helper::maybe_json( $meta_value, false ); |
| 1139 |
if ( empty( $link_id ) || empty( $meta_key ) ) { |
| 1140 |
return false; |
| 1141 |
} |
| 1142 |
$result = $wpdb->insert( |
| 1143 |
$wpdb->prefix . 'betterlinkmeta', |
| 1144 |
array( |
| 1145 |
'link_id' => $link_id, |
| 1146 |
'meta_key' => $meta_key, |
| 1147 |
'meta_value' => $meta_value, |
| 1148 |
) // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_key |
| 1149 |
); // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_value |
| 1150 |
if ( ! $result ) { |
| 1151 |
return false; |
| 1152 |
} |
| 1153 |
return (int) $wpdb->insert_id; |
| 1154 |
} |
| 1155 |
public static function update_link_meta( $link_id, $meta_key, $meta_value, $old_keywords = false, $old_link_id = false ) { |
| 1156 |
global $wpdb; |
| 1157 |
$table = $wpdb->prefix . 'betterlinkmeta'; |
| 1158 |
$link_id = absint( $link_id ); |
| 1159 |
$meta_key = wp_unslash( $meta_key ); |
| 1160 |
$meta_value = wp_unslash( $meta_value ); |
| 1161 |
if ( isset( $meta_value['keywords'] ) ) { |
| 1162 |
$meta_value['keywords'] = preg_replace( '/\’|\'|\‘/', "'", $meta_value['keywords'] ); |
| 1163 |
} |
| 1164 |
$meta_value = \BetterLinks\Helper::maybe_json( $meta_value, false ); |
| 1165 |
if ( empty( $link_id ) || empty( $meta_key ) ) { |
| 1166 |
return false; |
| 1167 |
} |
| 1168 |
$result = false; |
| 1169 |
if ( $old_keywords && $old_link_id ) { |
| 1170 |
$keywordPattern = wp_slash( '%"keywords":' . wp_json_encode( wp_unslash( $old_keywords ) ) . ',"link_id":%' ); |
| 1171 |
$result = $wpdb->query( |
| 1172 |
$wpdb->prepare( |
| 1173 |
"UPDATE $table |
| 1174 |
SET meta_value = %s, link_id = %d |
| 1175 |
WHERE link_id = %d AND meta_key=%s AND meta_value LIKE %s LIMIT 1", |
| 1176 |
$meta_value, |
| 1177 |
$link_id, |
| 1178 |
$old_link_id, |
| 1179 |
$meta_key, |
| 1180 |
$keywordPattern |
| 1181 |
) |
| 1182 |
); |
| 1183 |
} else { |
| 1184 |
$result = $wpdb->query( |
| 1185 |
$wpdb->prepare( |
| 1186 |
"UPDATE $table |
| 1187 |
SET meta_value = %s |
| 1188 |
WHERE link_id = %d AND meta_key=%s", |
| 1189 |
$meta_value, |
| 1190 |
$link_id, |
| 1191 |
$meta_key |
| 1192 |
) |
| 1193 |
); |
| 1194 |
} |
| 1195 |
return ! ! $result; |
| 1196 |
} |
| 1197 |
|
| 1198 |
public static function delete_link_meta( $link_id, $meta_key, $meta_value = '', $keywords = false ) { |
| 1199 |
global $wpdb; |
| 1200 |
$table = $wpdb->prefix . 'betterlinkmeta'; |
| 1201 |
if ( empty( $link_id ) || empty( $meta_key ) ) { |
| 1202 |
return false; |
| 1203 |
} |
| 1204 |
$query = $wpdb->prepare( "SELECT link_id FROM $table WHERE meta_key = %s AND link_id = %d", $meta_key, $link_id ); |
| 1205 |
if ( ! empty( $keywords ) ) { |
| 1206 |
$keywordPattern = wp_slash( '%"keywords":' . wp_json_encode( wp_unslash( $keywords ) ) . ',"link_id":%' ); |
| 1207 |
$query = $wpdb->prepare( |
| 1208 |
"SELECT meta_id FROM $table WHERE meta_key = %s AND link_id = %d AND meta_value LIKE %s LIMIT 1", |
| 1209 |
$meta_key, |
| 1210 |
$link_id, |
| 1211 |
$keywordPattern |
| 1212 |
); |
| 1213 |
} |
| 1214 |
if ( ! empty( $meta_value ) ) { |
| 1215 |
$query .= $wpdb->prepare( ' AND meta_value = %s', $meta_value ); |
| 1216 |
} |
| 1217 |
$meta_ids = $wpdb->get_col( $query ); |
| 1218 |
if ( ! count( $meta_ids ) ) { |
| 1219 |
return false; |
| 1220 |
} |
| 1221 |
$query = "DELETE FROM $table WHERE meta_id IN( " . implode( ',', $meta_ids ) . ' )'; |
| 1222 |
$count = $wpdb->query( $query ); |
| 1223 |
return ! ! $count; |
| 1224 |
} |
| 1225 |
|
| 1226 |
public static function get_keywords() { |
| 1227 |
global $wpdb; |
| 1228 |
$results = $wpdb->get_results( |
| 1229 |
$wpdb->prepare( "SELECT meta_value FROM {$wpdb->prefix}betterlinkmeta WHERE meta_key=%s ORDER BY meta_id DESC", 'keywords' ), |
| 1230 |
ARRAY_A |
| 1231 |
); |
| 1232 |
$results = array_column( $results, 'meta_value' ); |
| 1233 |
return $results; |
| 1234 |
} |
| 1235 |
|
| 1236 |
public static function get_keywords_for_export() { |
| 1237 |
global $wpdb; |
| 1238 |
$results = $wpdb->get_results( |
| 1239 |
$wpdb->prepare( "SELECT meta_id, meta_value FROM {$wpdb->prefix}betterlinkmeta WHERE meta_key=%s ORDER BY meta_id DESC", 'keywords' ), |
| 1240 |
ARRAY_A |
| 1241 |
); |
| 1242 |
return $results; |
| 1243 |
} |
| 1244 |
|
| 1245 |
public static function update_link_meta_by_meta_id( $meta_id, $link_id, $meta_key, $meta_value ) { |
| 1246 |
global $wpdb; |
| 1247 |
$table = $wpdb->prefix . 'betterlinkmeta'; |
| 1248 |
$meta_id = absint( $meta_id ); |
| 1249 |
$link_id = absint( $link_id ); |
| 1250 |
$meta_key = wp_unslash( $meta_key ); |
| 1251 |
$meta_value = wp_unslash( $meta_value ); |
| 1252 |
if ( isset( $meta_value['keywords'] ) ) { |
| 1253 |
$meta_value['keywords'] = preg_replace( '/\'|\'|\'/', "'", $meta_value['keywords'] ); |
| 1254 |
} |
| 1255 |
$meta_value = \BetterLinks\Helper::maybe_json( $meta_value, false ); |
| 1256 |
if ( empty( $meta_id ) || empty( $link_id ) || empty( $meta_key ) ) { |
| 1257 |
return false; |
| 1258 |
} |
| 1259 |
$result = $wpdb->update( |
| 1260 |
$table, |
| 1261 |
array( |
| 1262 |
'meta_value' => $meta_value, |
| 1263 |
'link_id' => $link_id, |
| 1264 |
), // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_value |
| 1265 |
array( |
| 1266 |
'meta_id' => $meta_id, |
| 1267 |
'meta_key' => $meta_key, |
| 1268 |
) |
| 1269 |
); // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_key |
| 1270 |
return $result !== false; |
| 1271 |
} |
| 1272 |
|
| 1273 |
public static function get_link_data_by_id( $id, $fields ) { |
| 1274 |
global $wpdb; |
| 1275 |
$query = $wpdb->prepare( "SELECT `{$fields}` from {$wpdb->prefix}betterlinks WHERE id=%d", array( $id ) ); |
| 1276 |
$result = $wpdb->get_var( $query ); |
| 1277 |
return $result; |
| 1278 |
} |
| 1279 |
|
| 1280 |
public static function get_link_count(){ |
| 1281 |
global $wpdb; |
| 1282 |
$query = "SELECT COUNT(*) AS total_link, |
| 1283 |
SUM(wildcards) AS wildcards, |
| 1284 |
SUM(expire != '' and expire != '{}') AS link_expire, |
| 1285 |
SUM(dynamic_redirect != '' and dynamic_redirect != '{}') AS dynamic_redirect, |
| 1286 |
SUM(id=link_id and meta_key='keywords') as auto_link_keyword |
| 1287 |
FROM {$wpdb->prefix}betterlinks as links left join {$wpdb->prefix}betterlinkmeta as meta on links.id=meta.link_id;"; |
| 1288 |
|
| 1289 |
$count = $wpdb->get_row( $query, ARRAY_A ); |
| 1290 |
return is_array( $count ) ? $count : []; |
| 1291 |
} |
| 1292 |
public static function get_password_protected_link_count(){ |
| 1293 |
if( ! apply_filters('betterlinks/pro_enabled', false) ){ |
| 1294 |
return [ |
| 1295 |
'password_protected' => 0, |
| 1296 |
'active_password_protected' => 0 |
| 1297 |
]; |
| 1298 |
} |
| 1299 |
global $wpdb; |
| 1300 |
$query = "SELECT COUNT(*) AS password_protected, SUM(status) AS active_password_protected FROM {$wpdb->prefix}betterlinks_password;"; |
| 1301 |
$count = $wpdb->get_row( $query, ARRAY_A ); |
| 1302 |
return is_array( $count ) ? $count : []; |
| 1303 |
} |
| 1304 |
|
| 1305 |
public static function get_prettylinks_data() { |
| 1306 |
$links_count = self::get_prettylinks_links_count(); |
| 1307 |
$clicks_count = self::get_prettylinks_clicks_count(); |
| 1308 |
set_transient( |
| 1309 |
'betterlinks_migration_data_prettylinks', |
| 1310 |
array( |
| 1311 |
'links_count' => $links_count, |
| 1312 |
'clicks_count' => $clicks_count, |
| 1313 |
), |
| 1314 |
60 * 5 |
| 1315 |
); |
| 1316 |
return array( |
| 1317 |
'links_count' => $links_count, |
| 1318 |
'clicks_count' => $clicks_count, |
| 1319 |
); |
| 1320 |
} |
| 1321 |
|
| 1322 |
public static function used_features_by_client() { |
| 1323 |
// Pull free settings (betterlinks_links holds force_https, affiliate_link_disclosure, |
| 1324 |
// excluded_ips, custom_domain.*) and Pro option blobs in one batch — all reads are |
| 1325 |
// cheap option lookups. Pro options return defaults on free-only installs. |
| 1326 |
$links_options = json_decode( get_option( BETTERLINKS_LINKS_OPTION_NAME, '{}' ), true ); |
| 1327 |
$links_options = is_array( $links_options ) ? $links_options : array(); |
| 1328 |
$pro_external_anal = get_option( 'betterlinkspro_ga', array() ); |
| 1329 |
$pro_external_anal = is_array( $pro_external_anal ) ? $pro_external_anal : array(); |
| 1330 |
$pro_auto_link_raw = get_option( 'betterlinkspro_auto_link_create', '' ); |
| 1331 |
$pro_auto_link = is_string( $pro_auto_link_raw ) && '' !== $pro_auto_link_raw ? json_decode( $pro_auto_link_raw, true ) : array(); |
| 1332 |
$pro_auto_link = is_array( $pro_auto_link ) ? $pro_auto_link : array(); |
| 1333 |
$pro_reporting_raw = get_option( 'betterlinkspro_reporting', '' ); |
| 1334 |
$pro_reporting = is_string( $pro_reporting_raw ) && '' !== $pro_reporting_raw ? json_decode( $pro_reporting_raw, true ) : array(); |
| 1335 |
$pro_reporting = is_array( $pro_reporting ) ? $pro_reporting : array(); |
| 1336 |
$pro_broken_cfg_raw = get_option( 'betterlinkspro_broken_link', '' ); |
| 1337 |
$pro_broken_cfg = is_string( $pro_broken_cfg_raw ) && '' !== $pro_broken_cfg_raw ? json_decode( $pro_broken_cfg_raw, true ) : array(); |
| 1338 |
$pro_broken_cfg = is_array( $pro_broken_cfg ) ? $pro_broken_cfg : array(); |
| 1339 |
|
| 1340 |
// AI Link Assistant ships in Pro 2.8.0+ and its toggles (in betterlinks_links) |
| 1341 |
// default ON, so a missing key looks "enabled" on every install. Gate on the |
| 1342 |
// stored Pro version to avoid over-reporting on free / older-Pro sites. |
| 1343 |
$pro_version = get_option( 'betterlinks_pro_version', '' ); |
| 1344 |
$is_link_assistant_live = ! empty( $pro_version ) && version_compare( $pro_version, '2.8.0', '>=' ); |
| 1345 |
|
| 1346 |
return array( |
| 1347 |
// existing — kept for backward compatibility with WPInsights dashboards |
| 1348 |
'betterlinks_broken_link_scanner' => !empty( get_option( 'betterlinkspro_broken_links_logs', [] ) ), |
| 1349 |
'fullsite_link_scanner' => !empty( get_option( 'betterlinkspro_fullsite_broken_links_logs_cleared', 0 ) ) || !empty( get_option( 'betterlinkspro_fullsite_broken_links_logs', [] ) ), |
| 1350 |
'ai_link_generator' => !empty( get_option( 'betterlinks_ai_generator_used', false ) ), |
| 1351 |
'utm_builder' => !empty( get_option( 'betterlinks_utm_builder_used', false ) ), |
| 1352 |
// new — settings toggles (Pro feature adoption) |
| 1353 |
'is_ga_enabled' => ! empty( $pro_external_anal['is_enable_ga'] ), |
| 1354 |
'is_pixel_enabled' => ! empty( $pro_external_anal['is_enable_pixel'] ), |
| 1355 |
'is_custom_scripts_enabled' => ! empty( $pro_external_anal['is_enable_custom_scripts'] ), |
| 1356 |
'is_custom_domain_configured' => ! empty( $links_options['custom_domain']['enable_shortlink_custom_domain'] ) && ! empty( $links_options['custom_domain']['shortlink_custom_domain'] ), |
| 1357 |
'is_force_https_enabled' => ! empty( $links_options['force_https'] ), |
| 1358 |
'is_affiliate_disclosure_enabled' => ! empty( $links_options['affiliate_link_disclosure'] ), |
| 1359 |
'is_exclude_ips_configured' => isset( $links_options['excluded_ips'] ) && is_array( $links_options['excluded_ips'] ) && ! empty( $links_options['excluded_ips'] ), |
| 1360 |
'is_auto_create_links_enabled' => ! empty( $pro_auto_link['post_shortlinks'] ) || ! empty( $pro_auto_link['page_shortlinks'] ), |
| 1361 |
'is_email_reports_enabled' => ! empty( $pro_reporting['enable_reporting'] ), |
| 1362 |
'is_broken_link_scan_enabled' => ! empty( $pro_broken_cfg['enable_scan'] ), |
| 1363 |
// AI Link Assistant — actual usage markers (set by Pro when the feature runs), |
| 1364 |
// the reliable signal for adoption since the settings toggles default ON. |
| 1365 |
'ai_link_assistant_used' => !empty( get_option( 'betterlinks_link_genius_used', false ) ), |
| 1366 |
'link_suggestion_used' => !empty( get_option( 'betterlinks_raw_link_rescue_used', false ) ), |
| 1367 |
); |
| 1368 |
} |
| 1369 |
|
| 1370 |
/** |
| 1371 |
* Per-redirect-type adoption rollup, sent to WPInsights so product can see |
| 1372 |
* which redirect modes users actually create. |
| 1373 |
* |
| 1374 |
* Counts: |
| 1375 |
* - `cloak_redirect_count` rows where redirect_type='cloak' (Pro) |
| 1376 |
* - `dynamic_redirect_rotation_count` dynamic_redirect.type='rotation' (split test) |
| 1377 |
* - `dynamic_redirect_geographic_count` dynamic_redirect.type='geographic' |
| 1378 |
* - `dynamic_redirect_device_count` dynamic_redirect.type='device' |
| 1379 |
* |
| 1380 |
* LIKE patterns are used instead of MySQL JSON functions for compatibility |
| 1381 |
* with MySQL 5.6 / MariaDB 10.1. |
| 1382 |
*/ |
| 1383 |
public static function get_redirect_type_breakdown() { |
| 1384 |
global $wpdb; |
| 1385 |
// COALESCE wraps each SUM so a links table with zero matching rows |
| 1386 |
// reports 0 instead of NULL in the WPInsights payload. |
| 1387 |
$query = "SELECT |
| 1388 |
COALESCE(SUM(redirect_type='cloak'), 0) AS cloak_redirect_count, |
| 1389 |
COALESCE(SUM(dynamic_redirect LIKE '%\"type\":\"rotation\"%'), 0) AS dynamic_redirect_rotation_count, |
| 1390 |
COALESCE(SUM(dynamic_redirect LIKE '%\"type\":\"geographic\"%'), 0) AS dynamic_redirect_geographic_count, |
| 1391 |
COALESCE(SUM(dynamic_redirect LIKE '%\"type\":\"device\"%'), 0) AS dynamic_redirect_device_count |
| 1392 |
FROM {$wpdb->prefix}betterlinks;"; |
| 1393 |
|
| 1394 |
$count = $wpdb->get_row( $query, ARRAY_A ); |
| 1395 |
return is_array( $count ) ? $count : array(); |
| 1396 |
} |
| 1397 |
|
| 1398 |
/** |
| 1399 |
* Cached check for user_agent_id column existence to avoid hitting information_schema on every redirect |
| 1400 |
* |
| 1401 |
* @return bool |
| 1402 |
*/ |
| 1403 |
private static function get_user_agent_column_exists() { |
| 1404 |
global $wpdb; |
| 1405 |
|
| 1406 |
$transient_key = 'betterlinks_user_agent_column_exists'; |
| 1407 |
$column_exists = get_transient( $transient_key ); |
| 1408 |
|
| 1409 |
if ( $column_exists === false ) { |
| 1410 |
// Only hit information_schema when not cached |
| 1411 |
$column_exists = $wpdb->get_var( |
| 1412 |
$wpdb->prepare( |
| 1413 |
'SELECT `column_name` FROM information_schema.columns WHERE table_schema=%s AND table_name=%s AND column_name="user_agent_id"', |
| 1414 |
DB_NAME, |
| 1415 |
$wpdb->prefix . 'betterlinks_clicks' |
| 1416 |
) |
| 1417 |
); |
| 1418 |
|
| 1419 |
// Cache for 1 hour - column structure doesn't change often |
| 1420 |
set_transient( $transient_key, $column_exists ? 'yes' : 'no', HOUR_IN_SECONDS ); |
| 1421 |
} |
| 1422 |
|
| 1423 |
return $column_exists === 'yes'; |
| 1424 |
} |
| 1425 |
} |
| 1426 |
|
