1399 lines
40 KiB
PHP
1399 lines
40 KiB
PHP
<?
|
|
namespace factory;
|
|
class Products
|
|
{
|
|
static private function is_product_core_field( $field )
|
|
{
|
|
return in_array( (string) $field, [
|
|
'custom_label_4',
|
|
'custom_label_3',
|
|
'title',
|
|
'description',
|
|
'google_product_category',
|
|
'product_url'
|
|
], true );
|
|
}
|
|
|
|
static public function delete_product( $product_id ) {
|
|
global $mdb;
|
|
$mdb -> delete( 'products', [ 'id' => $product_id ] );
|
|
return true;
|
|
}
|
|
|
|
static public function delete_products( $product_ids ) {
|
|
global $mdb;
|
|
if ( empty( $product_ids ) || !is_array( $product_ids ) ) {
|
|
return false;
|
|
}
|
|
foreach ( $product_ids as $product_id ) {
|
|
$mdb -> delete( 'products', [ 'id' => $product_id ] );
|
|
}
|
|
return true;
|
|
}
|
|
|
|
static public function get_product_comments( $product_id )
|
|
{
|
|
global $mdb;
|
|
return $mdb -> query( 'SELECT id, comment, date_add FROM products_comments WHERE product_id = \'' . $product_id . '\' ORDER BY date_add DESC' ) -> fetchAll( \PDO::FETCH_ASSOC );
|
|
}
|
|
|
|
static public function delete_product_comment( $comment_id )
|
|
{
|
|
global $mdb;
|
|
return $mdb -> delete( 'products_comments', [ 'id' => $comment_id ] );
|
|
}
|
|
|
|
static public function get_ad_group_delete_context( $ad_group_id )
|
|
{
|
|
global $mdb;
|
|
|
|
return $mdb -> query(
|
|
'SELECT
|
|
ag.id AS local_ad_group_id,
|
|
ag.campaign_id AS local_campaign_id,
|
|
ag.ad_group_id AS external_ad_group_id,
|
|
ag.ad_group_name,
|
|
c.client_id,
|
|
c.campaign_id AS external_campaign_id,
|
|
c.campaign_name,
|
|
c.advertising_channel_type,
|
|
cl.google_ads_customer_id
|
|
FROM campaign_ad_groups AS ag
|
|
INNER JOIN campaigns AS c ON c.id = ag.campaign_id
|
|
INNER JOIN clients AS cl ON cl.id = c.client_id
|
|
WHERE ag.id = :ad_group_id
|
|
LIMIT 1',
|
|
[ ':ad_group_id' => (int) $ad_group_id ]
|
|
) -> fetch( \PDO::FETCH_ASSOC );
|
|
}
|
|
|
|
static public function delete_ad_group_local( $ad_group_id )
|
|
{
|
|
global $mdb;
|
|
|
|
$ad_group_id = (int) $ad_group_id;
|
|
if ( $ad_group_id <= 0 )
|
|
{
|
|
return false;
|
|
}
|
|
|
|
$mdb -> update( 'campaign_ad_groups', [ 'status' => 'paused' ], [ 'id' => $ad_group_id ] );
|
|
|
|
return true;
|
|
}
|
|
|
|
static public function get_product_comment_by_date( $product_id, $date )
|
|
{
|
|
global $mdb;
|
|
return $mdb -> get( 'products_comments', [ 'id', 'comment' ], [ 'AND' => [ 'product_id' => $product_id, 'date_add' => $date ] ] );
|
|
}
|
|
|
|
static public function get_scope_alerts( $client_id, $campaign_id, $ad_group_id, $limit = 50 )
|
|
{
|
|
global $mdb;
|
|
|
|
$client_id = (int) $client_id;
|
|
$campaign_id = (int) $campaign_id;
|
|
$ad_group_id = (int) $ad_group_id;
|
|
$limit = max( 1, (int) $limit );
|
|
|
|
if ( $client_id <= 0 || $campaign_id <= 0 )
|
|
{
|
|
return [];
|
|
}
|
|
|
|
$where = [
|
|
'client_id' => $client_id,
|
|
'campaign_id' => $campaign_id
|
|
];
|
|
|
|
if ( $ad_group_id > 0 )
|
|
{
|
|
$where['ad_group_id'] = $ad_group_id;
|
|
}
|
|
|
|
$rows = $mdb -> select( 'campaign_alerts', [
|
|
'id',
|
|
'alert_type',
|
|
'message',
|
|
'date_detected',
|
|
'date_add'
|
|
], [
|
|
'AND' => $where,
|
|
'ORDER' => [
|
|
'date_detected' => 'DESC',
|
|
'id' => 'DESC'
|
|
],
|
|
'LIMIT' => $limit
|
|
] );
|
|
|
|
return is_array( $rows ) ? $rows : [];
|
|
}
|
|
|
|
static public function get_products_without_impressions_30( $client_id, $campaign_id, $ad_group_id = 0, $limit = 500 )
|
|
{
|
|
global $mdb;
|
|
|
|
$client_id = (int) $client_id;
|
|
$campaign_id = (int) $campaign_id;
|
|
$ad_group_id = (int) $ad_group_id;
|
|
$limit = max( 1, (int) $limit );
|
|
$limit = min( 2000, $limit );
|
|
|
|
if ( $client_id <= 0 || $campaign_id <= 0 )
|
|
{
|
|
return [];
|
|
}
|
|
|
|
$params = [
|
|
':client_id' => $client_id,
|
|
':campaign_id' => $campaign_id
|
|
];
|
|
|
|
$sql = 'SELECT
|
|
p.id AS product_id,
|
|
p.offer_id,
|
|
p.name,
|
|
p.title,
|
|
SUM( pa.impressions_30 ) AS impressions_30
|
|
FROM products_aggregate AS pa
|
|
INNER JOIN products AS p ON p.id = pa.product_id
|
|
INNER JOIN campaign_ad_groups AS ag ON ag.id = pa.ad_group_id
|
|
WHERE p.client_id = :client_id
|
|
AND pa.campaign_id = :campaign_id
|
|
AND ag.status = \'active\'';
|
|
|
|
if ( $ad_group_id > 0 )
|
|
{
|
|
$sql .= ' AND pa.ad_group_id = :ad_group_id';
|
|
$params[':ad_group_id'] = $ad_group_id;
|
|
}
|
|
|
|
$sql .= '
|
|
GROUP BY p.id, p.offer_id, p.name, p.title
|
|
HAVING COALESCE( SUM( pa.impressions_30 ), 0 ) = 0
|
|
ORDER BY COALESCE( NULLIF( TRIM( p.title ), \'\' ), NULLIF( TRIM( p.name ), \'\' ), p.offer_id ) ASC, p.id ASC
|
|
LIMIT ' . $limit;
|
|
|
|
try
|
|
{
|
|
$statement = $mdb -> query( $sql, $params );
|
|
if ( !$statement )
|
|
{
|
|
error_log( '[products] get_products_without_impressions_30 query returned no statement.' );
|
|
return [];
|
|
}
|
|
|
|
$rows = $statement -> fetchAll( \PDO::FETCH_ASSOC );
|
|
return is_array( $rows ) ? $rows : [];
|
|
}
|
|
catch ( \Throwable $e )
|
|
{
|
|
error_log( '[products] get_products_without_impressions_30 query error: ' . $e -> getMessage() );
|
|
return [];
|
|
}
|
|
}
|
|
|
|
static public function get_min_roas( $product_id )
|
|
{
|
|
global $mdb;
|
|
return $mdb -> get( 'products', 'min_roas', [ 'id' => $product_id ] );
|
|
}
|
|
|
|
static public function save_min_roas( $product_id, $min_roas )
|
|
{
|
|
global $mdb;
|
|
return $mdb -> update( 'products', [ 'min_roas' => $min_roas ], [ 'id' => $product_id ] );
|
|
}
|
|
|
|
static public function get_client_bestseller_settings( $client_id )
|
|
{
|
|
global $mdb;
|
|
|
|
$client_id = (int) $client_id;
|
|
if ( $client_id <= 0 )
|
|
{
|
|
return [
|
|
'bestseller_roas_entry' => null,
|
|
'bestseller_roas_exit' => null,
|
|
'min_conversions' => 10,
|
|
'cooldown_period' => 14
|
|
];
|
|
}
|
|
|
|
$row = $mdb -> query(
|
|
'SELECT
|
|
bestseller_roas_entry,
|
|
bestseller_roas_exit,
|
|
min_conversions,
|
|
cooldown_period
|
|
FROM clients
|
|
WHERE id = :client_id
|
|
LIMIT 1',
|
|
[ ':client_id' => $client_id ]
|
|
) -> fetch( \PDO::FETCH_ASSOC );
|
|
|
|
if ( !is_array( $row ) )
|
|
{
|
|
return [
|
|
'bestseller_roas_entry' => null,
|
|
'bestseller_roas_exit' => null,
|
|
'min_conversions' => 10,
|
|
'cooldown_period' => 14
|
|
];
|
|
}
|
|
|
|
$entry = isset( $row['bestseller_roas_entry'] ) ? trim( (string) $row['bestseller_roas_entry'] ) : '';
|
|
$exit = isset( $row['bestseller_roas_exit'] ) ? trim( (string) $row['bestseller_roas_exit'] ) : '';
|
|
$min_conversions = (int) ( $row['min_conversions'] ?? 10 );
|
|
$cooldown_period = (int) ( $row['cooldown_period'] ?? 14 );
|
|
|
|
return [
|
|
'bestseller_roas_entry' => $entry !== '' ? (float) $entry : null,
|
|
'bestseller_roas_exit' => $exit !== '' ? (float) $exit : null,
|
|
'min_conversions' => $min_conversions > 0 ? $min_conversions : 10,
|
|
'cooldown_period' => $cooldown_period > 0 ? $cooldown_period : 14
|
|
];
|
|
}
|
|
|
|
static public function save_client_bestseller_settings( $client_id, $settings )
|
|
{
|
|
global $mdb;
|
|
|
|
$client_id = (int) $client_id;
|
|
if ( $client_id <= 0 || !is_array( $settings ) )
|
|
{
|
|
return false;
|
|
}
|
|
|
|
$entry_raw = trim( (string) ( $settings['bestseller_roas_entry'] ?? '' ) );
|
|
$exit_raw = trim( (string) ( $settings['bestseller_roas_exit'] ?? '' ) );
|
|
|
|
$entry = is_numeric( $entry_raw ) ? round( (float) $entry_raw, 6 ) : null;
|
|
$exit = is_numeric( $exit_raw ) ? round( (float) $exit_raw, 6 ) : null;
|
|
$min_conversions = max( 0, (int) ( $settings['min_conversions'] ?? 10 ) );
|
|
$cooldown_period = max( 1, (int) ( $settings['cooldown_period'] ?? 14 ) );
|
|
|
|
return $mdb -> update( 'clients', [
|
|
'bestseller_roas_entry' => $entry,
|
|
'bestseller_roas_exit' => $exit,
|
|
'min_conversions' => $min_conversions,
|
|
'cooldown_period' => $cooldown_period
|
|
], [ 'id' => $client_id ] );
|
|
}
|
|
|
|
static public function get_client_bestseller_preview_stats( $client_id, $settings )
|
|
{
|
|
global $mdb;
|
|
|
|
$client_id = (int) $client_id;
|
|
if ( $client_id <= 0 || !is_array( $settings ) )
|
|
{
|
|
return [
|
|
'entry_count' => 0,
|
|
'cooldown_count' => 0,
|
|
'total_count' => 0
|
|
];
|
|
}
|
|
|
|
$entry_raw = trim( (string) ( $settings['bestseller_roas_entry'] ?? '' ) );
|
|
$exit_raw = trim( (string) ( $settings['bestseller_roas_exit'] ?? '' ) );
|
|
|
|
if ( !is_numeric( $entry_raw ) || !is_numeric( $exit_raw ) )
|
|
{
|
|
return [
|
|
'entry_count' => 0,
|
|
'cooldown_count' => 0,
|
|
'total_count' => 0
|
|
];
|
|
}
|
|
|
|
$entry = (float) $entry_raw;
|
|
$exit = (float) $exit_raw;
|
|
$min_conversions = max( 0, (float) ( $settings['min_conversions'] ?? 10 ) );
|
|
$cooldown_period = max( 1, (int) ( $settings['cooldown_period'] ?? 14 ) );
|
|
|
|
$rows = $mdb -> query(
|
|
'SELECT
|
|
p.id,
|
|
p.custom_label_4,
|
|
COALESCE( SUM( pa.cost_30 ), 0 ) AS cost_30,
|
|
COALESCE( SUM( pa.conversion_value_30 ), 0 ) AS conversion_value_30,
|
|
COALESCE( SUM( pa.conversions_30 ), 0 ) AS conversions_30
|
|
FROM products p
|
|
LEFT JOIN products_aggregate pa ON pa.product_id = p.id
|
|
WHERE p.client_id = :client_id
|
|
GROUP BY p.id, p.custom_label_4',
|
|
[ ':client_id' => $client_id ]
|
|
) -> fetchAll( \PDO::FETCH_ASSOC );
|
|
|
|
if ( empty( $rows ) )
|
|
{
|
|
return [
|
|
'entry_count' => 0,
|
|
'cooldown_count' => 0,
|
|
'total_count' => 0
|
|
];
|
|
}
|
|
|
|
$product_ids = [];
|
|
foreach ( $rows as $row )
|
|
{
|
|
$pid = (int) ( $row['id'] ?? 0 );
|
|
if ( $pid > 0 )
|
|
{
|
|
$product_ids[] = $pid;
|
|
}
|
|
}
|
|
|
|
if ( empty( $product_ids ) )
|
|
{
|
|
return [
|
|
'entry_count' => 0,
|
|
'cooldown_count' => 0,
|
|
'total_count' => 0
|
|
];
|
|
}
|
|
|
|
$history_by_product = [];
|
|
$history_rows = $mdb -> query(
|
|
'SELECT
|
|
h30.product_id,
|
|
h30.date_add,
|
|
CASE
|
|
WHEN SUM( h30.cost ) > 0 THEN ( SUM( h30.conversions_value ) / SUM( h30.cost ) ) * 100
|
|
ELSE 0
|
|
END AS roas
|
|
FROM products_history_30 h30
|
|
WHERE h30.product_id IN (' . implode( ',', array_map( 'intval', $product_ids ) ) . ')
|
|
GROUP BY h30.product_id, h30.date_add
|
|
ORDER BY h30.product_id ASC, h30.date_add DESC'
|
|
) -> fetchAll( \PDO::FETCH_ASSOC );
|
|
|
|
foreach ( (array) $history_rows as $history_row )
|
|
{
|
|
$pid = (int) ( $history_row['product_id'] ?? 0 );
|
|
if ( $pid <= 0 )
|
|
{
|
|
continue;
|
|
}
|
|
|
|
if ( !isset( $history_by_product[ $pid ] ) )
|
|
{
|
|
$history_by_product[ $pid ] = [];
|
|
}
|
|
|
|
$history_by_product[ $pid ][] = (float) ( $history_row['roas'] ?? 0 );
|
|
}
|
|
|
|
$entry_count = 0;
|
|
$cooldown_count = 0;
|
|
$total_count = 0;
|
|
foreach ( $rows as $row )
|
|
{
|
|
$product_id = (int) ( $row['id'] ?? 0 );
|
|
if ( $product_id <= 0 )
|
|
{
|
|
continue;
|
|
}
|
|
|
|
$cost_30 = (float) ( $row['cost_30'] ?? 0 );
|
|
$conversion_value_30 = (float) ( $row['conversion_value_30'] ?? 0 );
|
|
$conversions_30 = (float) ( $row['conversions_30'] ?? 0 );
|
|
$roas_30 = $cost_30 > 0 ? ( $conversion_value_30 / $cost_30 ) * 100 : 0;
|
|
$current_label = trim( (string) ( $row['custom_label_4'] ?? '' ) );
|
|
if ( $current_label !== '' && $current_label !== 'bestseller' )
|
|
{
|
|
continue;
|
|
}
|
|
|
|
$entry_met = $roas_30 >= $entry && $conversions_30 >= $min_conversions;
|
|
$is_bestseller = false;
|
|
$is_kept_by_cooldown = false;
|
|
|
|
if ( $entry_met )
|
|
{
|
|
$entry_count++;
|
|
$is_bestseller = true;
|
|
}
|
|
else if ( $current_label === 'bestseller' )
|
|
{
|
|
$roas_history = $history_by_product[ $product_id ] ?? [];
|
|
$has_full_window = count( $roas_history ) >= $cooldown_period;
|
|
$below_exit_all_days = true;
|
|
|
|
if ( !$has_full_window )
|
|
{
|
|
$below_exit_all_days = false;
|
|
}
|
|
else
|
|
{
|
|
for ( $i = 0; $i < $cooldown_period; $i++ )
|
|
{
|
|
if ( (float) $roas_history[ $i ] >= $exit )
|
|
{
|
|
$below_exit_all_days = false;
|
|
break;
|
|
}
|
|
}
|
|
}
|
|
|
|
$is_bestseller = !$below_exit_all_days;
|
|
$is_kept_by_cooldown = $is_bestseller;
|
|
}
|
|
|
|
if ( $is_bestseller )
|
|
{
|
|
$total_count++;
|
|
}
|
|
|
|
if ( $is_kept_by_cooldown )
|
|
{
|
|
$cooldown_count++;
|
|
}
|
|
}
|
|
|
|
return [
|
|
'entry_count' => $entry_count,
|
|
'cooldown_count' => $cooldown_count,
|
|
'total_count' => $total_count
|
|
];
|
|
}
|
|
|
|
static public function get_client_bestseller_preview_count( $client_id, $settings )
|
|
{
|
|
$stats = self::get_client_bestseller_preview_stats( $client_id, $settings );
|
|
return (int) ( $stats['total_count'] ?? 0 );
|
|
}
|
|
|
|
static private function build_scope_filters( &$sql, &$params, $campaign_id, $ad_group_id )
|
|
{
|
|
$campaign_id = (int) $campaign_id;
|
|
$ad_group_id = (int) $ad_group_id;
|
|
|
|
if ( $campaign_id > 0 )
|
|
{
|
|
$sql .= ' AND pa.campaign_id = :campaign_id';
|
|
$params[':campaign_id'] = $campaign_id;
|
|
}
|
|
|
|
if ( $ad_group_id > 0 )
|
|
{
|
|
$sql .= ' AND pa.ad_group_id = :ad_group_id';
|
|
$params[':ad_group_id'] = $ad_group_id;
|
|
}
|
|
|
|
$sql .= ' AND ag.status = \'active\'';
|
|
}
|
|
|
|
static public function get_products( $client_id, $search, $limit, $start, $order_name, $order_dir, $campaign_id = 0, $ad_group_id = 0, $custom_label_4 = '' )
|
|
{
|
|
global $mdb;
|
|
|
|
$limit = max( 1, (int) $limit );
|
|
$start = max( 0, (int) $start );
|
|
$order_dir = strtoupper( (string) $order_dir ) === 'ASC' ? 'ASC' : 'DESC';
|
|
|
|
$order_map = [
|
|
'offer_id' => 'offer_id',
|
|
'campaign_name' => 'campaign_name',
|
|
'ad_group_name' => 'ad_group_name',
|
|
'name' => 'name',
|
|
'custom_label_3' => 'custom_label_3',
|
|
'impressions' => 'impressions',
|
|
'impressions_30' => 'impressions_30',
|
|
'clicks' => 'clicks',
|
|
'clicks_30' => 'clicks_30',
|
|
'ctr' => 'ctr',
|
|
'cost' => 'cost',
|
|
'cpc' => 'cpc',
|
|
'conversions' => 'conversions',
|
|
'conversions_value' => 'conversions_value',
|
|
'roas' => 'roas',
|
|
'min_roas' => 'min_roas'
|
|
];
|
|
|
|
$order_sql = $order_map[ $order_name ] ?? 'clicks';
|
|
|
|
$params = [ ':client_id' => (int) $client_id ];
|
|
$sql = 'SELECT
|
|
p.id AS product_id,
|
|
p.offer_id,
|
|
p.min_roas,
|
|
COALESCE( NULLIF( TRIM( p.custom_label_3 ), \'\' ), \'\' ) AS custom_label_3,
|
|
pa.campaign_id AS campaign_id,
|
|
COALESCE( NULLIF( TRIM( c.campaign_name ), \'\' ), \'--- brak kampanii ---\' ) AS campaign_name,
|
|
CASE
|
|
WHEN pa.ad_group_id = 0 THEN \'PMax (bez grup reklam)\'
|
|
ELSE COALESCE( NULLIF( TRIM( ag.ad_group_name ), \'\' ), \'--- brak grupy reklam ---\' )
|
|
END AS ad_group_name,
|
|
pa.ad_group_id AS ad_group_id,
|
|
pa.campaign_id AS history_campaign_id,
|
|
pa.ad_group_id AS history_ad_group_id,
|
|
COALESCE( NULLIF( TRIM( p.title ), \'\' ), NULLIF( TRIM( p.name ), \'\' ), p.offer_id ) AS name,
|
|
SUM( pa.impressions_all_time ) AS impressions,
|
|
SUM( pa.impressions_30 ) AS impressions_30,
|
|
SUM( pa.clicks_all_time ) AS clicks,
|
|
SUM( pa.clicks_30 ) AS clicks_30,
|
|
CASE
|
|
WHEN SUM( pa.impressions_all_time ) > 0 THEN ROUND( SUM( pa.clicks_all_time ) / SUM( pa.impressions_all_time ) * 100, 2 )
|
|
ELSE 0
|
|
END AS ctr,
|
|
SUM( pa.cost_all_time ) AS cost,
|
|
CASE
|
|
WHEN SUM( pa.clicks_all_time ) > 0 THEN ROUND( SUM( pa.cost_all_time ) / SUM( pa.clicks_all_time ), 6 )
|
|
ELSE 0
|
|
END AS cpc,
|
|
SUM( pa.conversions_all_time ) AS conversions,
|
|
SUM( pa.conversion_value_all_time ) AS conversions_value,
|
|
CASE
|
|
WHEN SUM( pa.cost_all_time ) > 0 THEN ROUND( SUM( pa.conversion_value_all_time ) / SUM( pa.cost_all_time ) * 100, 2 )
|
|
ELSE 0
|
|
END AS roas
|
|
FROM products_aggregate AS pa
|
|
INNER JOIN products AS p ON p.id = pa.product_id
|
|
LEFT JOIN campaigns AS c ON c.id = pa.campaign_id
|
|
LEFT JOIN campaign_ad_groups AS ag ON ag.id = pa.ad_group_id
|
|
WHERE p.client_id = :client_id';
|
|
|
|
self::build_scope_filters( $sql, $params, $campaign_id, $ad_group_id );
|
|
|
|
if ( $search )
|
|
{
|
|
$sql .= ' AND (
|
|
p.name LIKE :search
|
|
OR p.title LIKE :search
|
|
OR p.offer_id LIKE :search
|
|
OR p.custom_label_4 LIKE :search
|
|
OR c.campaign_name LIKE :search
|
|
OR ag.ad_group_name LIKE :search
|
|
)';
|
|
$params[':search'] = '%' . $search . '%';
|
|
}
|
|
|
|
if ( $custom_label_4 !== '' )
|
|
{
|
|
$sql .= ' AND p.custom_label_4 LIKE :custom_label_4';
|
|
$params[':custom_label_4'] = '%' . $custom_label_4 . '%';
|
|
}
|
|
|
|
$sql .= ' GROUP BY p.id, p.offer_id, p.min_roas, p.custom_label_3, p.name, p.title, pa.campaign_id, c.campaign_name, pa.ad_group_id, ag.ad_group_name';
|
|
$sql .= ' ORDER BY ' . $order_sql . ' ' . $order_dir . ', product_id DESC LIMIT ' . $start . ', ' . $limit;
|
|
|
|
return $mdb -> query( $sql, $params ) -> fetchAll( \PDO::FETCH_ASSOC );
|
|
}
|
|
|
|
public static function get_roas_bounds( int $client_id, ?string $search = null, int $campaign_id = 0, int $ad_group_id = 0, string $custom_label_4 = '' ): array
|
|
{
|
|
global $mdb;
|
|
|
|
$params = [ ':client_id' => $client_id ];
|
|
|
|
$sql = 'SELECT MIN( p.min_roas ) AS min_roas,
|
|
MAX(
|
|
CASE
|
|
WHEN COALESCE( pa.cost_all_time, 0 ) > 0 THEN ROUND( COALESCE( pa.conversion_value_all_time, 0 ) / pa.cost_all_time * 100, 2 )
|
|
ELSE 0
|
|
END
|
|
) AS max_roas
|
|
FROM products_aggregate AS pa
|
|
INNER JOIN products AS p ON p.id = pa.product_id
|
|
LEFT JOIN campaigns AS c ON c.id = pa.campaign_id
|
|
LEFT JOIN campaign_ad_groups AS ag ON ag.id = pa.ad_group_id
|
|
WHERE p.client_id = :client_id
|
|
AND pa.conversions_all_time > 10';
|
|
|
|
self::build_scope_filters( $sql, $params, $campaign_id, $ad_group_id );
|
|
|
|
if ( $search )
|
|
{
|
|
$sql .= ' AND (
|
|
p.name LIKE :search
|
|
OR p.title LIKE :search
|
|
OR p.offer_id LIKE :search
|
|
OR p.custom_label_4 LIKE :search
|
|
OR c.campaign_name LIKE :search
|
|
OR ag.ad_group_name LIKE :search
|
|
)';
|
|
$params[':search'] = '%' . $search . '%';
|
|
}
|
|
|
|
if ( $custom_label_4 !== '' )
|
|
{
|
|
$sql .= ' AND p.custom_label_4 LIKE :custom_label_4';
|
|
$params[':custom_label_4'] = '%' . $custom_label_4 . '%';
|
|
}
|
|
|
|
$row = $mdb -> query( $sql, $params ) -> fetch( \PDO::FETCH_ASSOC );
|
|
|
|
return [
|
|
'min' => isset( $row['min_roas'] ) ? (float) $row['min_roas'] : 0.0,
|
|
'max' => isset( $row['max_roas'] ) ? (float) $row['max_roas'] : 0.0,
|
|
];
|
|
}
|
|
|
|
static public function get_account_conversion_rate( $client_id )
|
|
{
|
|
global $mdb;
|
|
|
|
$row = $mdb -> query(
|
|
'SELECT SUM( pa.clicks_all_time ) AS total_clicks,
|
|
SUM( pa.conversions_all_time ) AS total_conversions
|
|
FROM products_aggregate AS pa
|
|
INNER JOIN products AS p ON p.id = pa.product_id
|
|
INNER JOIN campaign_ad_groups AS ag ON ag.id = pa.ad_group_id
|
|
WHERE p.client_id = :client_id
|
|
AND ag.status = \'active\'
|
|
AND pa.clicks_all_time > 0',
|
|
[ ':client_id' => (int) $client_id ]
|
|
) -> fetch( \PDO::FETCH_ASSOC );
|
|
|
|
$total_clicks = (float) ( $row['total_clicks'] ?? 0 );
|
|
$total_conversions = (float) ( $row['total_conversions'] ?? 0 );
|
|
|
|
if ( $total_clicks <= 0 ) return 0;
|
|
|
|
return $total_conversions / $total_clicks;
|
|
}
|
|
|
|
static public function get_records_total_products( $client_id, $search, $campaign_id = 0, $ad_group_id = 0, $custom_label_4 = '' )
|
|
{
|
|
global $mdb;
|
|
|
|
$params = [ ':client_id' => (int) $client_id ];
|
|
$sql = 'SELECT COUNT(0)
|
|
FROM (
|
|
SELECT p.id
|
|
FROM products_aggregate AS pa
|
|
INNER JOIN products AS p ON p.id = pa.product_id
|
|
LEFT JOIN campaigns AS c ON c.id = pa.campaign_id
|
|
LEFT JOIN campaign_ad_groups AS ag ON ag.id = pa.ad_group_id
|
|
WHERE p.client_id = :client_id';
|
|
|
|
self::build_scope_filters( $sql, $params, $campaign_id, $ad_group_id );
|
|
|
|
if ( $search )
|
|
{
|
|
$sql .= ' AND (
|
|
p.name LIKE :search
|
|
OR p.title LIKE :search
|
|
OR p.offer_id LIKE :search
|
|
OR p.custom_label_4 LIKE :search
|
|
OR c.campaign_name LIKE :search
|
|
OR ag.ad_group_name LIKE :search
|
|
)';
|
|
$params[':search'] = '%' . $search . '%';
|
|
}
|
|
|
|
if ( $custom_label_4 !== '' )
|
|
{
|
|
$sql .= ' AND p.custom_label_4 LIKE :custom_label_4';
|
|
$params[':custom_label_4'] = '%' . $custom_label_4 . '%';
|
|
}
|
|
|
|
$sql .= ' GROUP BY p.id, pa.campaign_id, pa.ad_group_id
|
|
) AS grouped_rows';
|
|
|
|
return $mdb -> query( $sql, $params ) -> fetchColumn();
|
|
}
|
|
|
|
static public function get_product_full_context( $product_id )
|
|
{
|
|
global $mdb;
|
|
return $mdb -> query(
|
|
'SELECT
|
|
p.id,
|
|
p.offer_id,
|
|
p.name,
|
|
p.min_roas,
|
|
COALESCE( SUM( pa.impressions_all_time ), 0 ) AS impressions,
|
|
COALESCE( SUM( pa.impressions_30 ), 0 ) AS impressions_30,
|
|
COALESCE( SUM( pa.clicks_all_time ), 0 ) AS clicks,
|
|
COALESCE( SUM( pa.clicks_30 ), 0 ) AS clicks_30,
|
|
CASE
|
|
WHEN COALESCE( SUM( pa.impressions_all_time ), 0 ) > 0
|
|
THEN ROUND( COALESCE( SUM( pa.clicks_all_time ), 0 ) / COALESCE( SUM( pa.impressions_all_time ), 0 ) * 100, 2 )
|
|
ELSE 0
|
|
END AS ctr,
|
|
COALESCE( SUM( pa.cost_all_time ), 0 ) AS cost,
|
|
CASE
|
|
WHEN COALESCE( SUM( pa.clicks_all_time ), 0 ) > 0
|
|
THEN ROUND( COALESCE( SUM( pa.cost_all_time ), 0 ) / COALESCE( SUM( pa.clicks_all_time ), 0 ), 6 )
|
|
ELSE 0
|
|
END AS cpc,
|
|
COALESCE( SUM( pa.conversions_all_time ), 0 ) AS conversions,
|
|
COALESCE( SUM( pa.conversion_value_all_time ), 0 ) AS conversions_value,
|
|
CASE
|
|
WHEN COALESCE( SUM( pa.cost_all_time ), 0 ) > 0
|
|
THEN ROUND( COALESCE( SUM( pa.conversion_value_all_time ), 0 ) / COALESCE( SUM( pa.cost_all_time ), 0 ) * 100, 2 )
|
|
ELSE 0
|
|
END AS roas
|
|
FROM products AS p
|
|
LEFT JOIN products_aggregate AS pa ON pa.product_id = p.id
|
|
WHERE p.id = :pid
|
|
GROUP BY p.id, p.offer_id, p.name, p.min_roas',
|
|
[ ':pid' => $product_id ]
|
|
) -> fetch( \PDO::FETCH_ASSOC );
|
|
}
|
|
|
|
static public function get_distinct_custom_label_4( $client_id )
|
|
{
|
|
global $mdb;
|
|
|
|
$client_id = (int) $client_id;
|
|
|
|
if ( $client_id <= 0 )
|
|
{
|
|
return [];
|
|
}
|
|
|
|
$rows = $mdb -> query(
|
|
"SELECT DISTINCT p.custom_label_4
|
|
FROM products p
|
|
WHERE p.client_id = :client_id
|
|
AND p.custom_label_4 IS NOT NULL
|
|
AND p.custom_label_4 != ''
|
|
ORDER BY p.custom_label_4 ASC",
|
|
[ ':client_id' => $client_id ]
|
|
) -> fetchAll( \PDO::FETCH_COLUMN );
|
|
|
|
return $rows ?: [];
|
|
}
|
|
|
|
static public function get_product_data( $product_id, $field )
|
|
{
|
|
global $mdb;
|
|
|
|
$product_id = (int) $product_id;
|
|
$field = trim( (string) $field );
|
|
|
|
if ( $product_id <= 0 || $field === '' )
|
|
{
|
|
return null;
|
|
}
|
|
|
|
if ( !self::is_product_core_field( $field ) )
|
|
{
|
|
return null;
|
|
}
|
|
|
|
return $mdb -> get( 'products', $field, [ 'id' => $product_id ] );
|
|
}
|
|
|
|
static public function get_product_name( $product_id )
|
|
{
|
|
global $mdb;
|
|
|
|
$product_id = (int) $product_id;
|
|
if ( $product_id <= 0 )
|
|
{
|
|
return null;
|
|
}
|
|
|
|
return $mdb -> get( 'products', 'name', [ 'id' => $product_id ] );
|
|
}
|
|
|
|
static public function get_product_merchant_context( $product_id )
|
|
{
|
|
global $mdb;
|
|
|
|
return $mdb -> query(
|
|
'SELECT
|
|
p.id AS product_id,
|
|
p.client_id,
|
|
p.offer_id,
|
|
cl.google_merchant_account_id
|
|
FROM products AS p
|
|
INNER JOIN clients AS cl ON cl.id = p.client_id
|
|
WHERE p.id = :product_id
|
|
LIMIT 1',
|
|
[ ':product_id' => (int) $product_id ]
|
|
) -> fetch( \PDO::FETCH_ASSOC );
|
|
}
|
|
|
|
static public function add_product_merchant_sync_log( $row )
|
|
{
|
|
global $mdb;
|
|
|
|
$data = [
|
|
'product_id' => (int) ( $row['product_id'] ?? 0 ),
|
|
'field_name' => (string) ( $row['field_name'] ?? '' ),
|
|
'old_value' => isset( $row['old_value'] ) ? (string) $row['old_value'] : null,
|
|
'new_value' => isset( $row['new_value'] ) ? (string) $row['new_value'] : null,
|
|
'sync_status' => (string) ( $row['sync_status'] ?? 'pending' ),
|
|
'sync_source' => (string) ( $row['sync_source'] ?? 'products_ui' ),
|
|
'merchant_account_id' => isset( $row['merchant_account_id'] ) ? (string) $row['merchant_account_id'] : null,
|
|
'merchant_product_id' => isset( $row['merchant_product_id'] ) ? (string) $row['merchant_product_id'] : null,
|
|
'offer_id' => isset( $row['offer_id'] ) ? (string) $row['offer_id'] : null,
|
|
'api_response' => isset( $row['api_response'] ) ? (string) $row['api_response'] : null,
|
|
'error_message' => isset( $row['error_message'] ) ? (string) $row['error_message'] : null,
|
|
'date_add' => date( 'Y-m-d H:i:s' )
|
|
];
|
|
|
|
if ( $data['product_id'] <= 0 || trim( $data['field_name'] ) === '' )
|
|
{
|
|
return false;
|
|
}
|
|
|
|
return $mdb -> insert( 'products_merchant_sync_log', $data );
|
|
}
|
|
|
|
static public function get_product_merchant_sync_logs( $product_id, $limit = 50 )
|
|
{
|
|
global $mdb;
|
|
|
|
$product_id = (int) $product_id;
|
|
$limit = max( 1, (int) $limit );
|
|
|
|
if ( $product_id <= 0 )
|
|
{
|
|
return [];
|
|
}
|
|
|
|
return $mdb -> query(
|
|
'SELECT
|
|
id,
|
|
field_name,
|
|
old_value,
|
|
new_value,
|
|
sync_status,
|
|
sync_source,
|
|
merchant_account_id,
|
|
merchant_product_id,
|
|
offer_id,
|
|
error_message,
|
|
date_add
|
|
FROM products_merchant_sync_log
|
|
WHERE product_id = :product_id
|
|
ORDER BY id DESC
|
|
LIMIT ' . $limit,
|
|
[ ':product_id' => $product_id ]
|
|
) -> fetchAll( \PDO::FETCH_ASSOC );
|
|
}
|
|
|
|
static public function delete_product_merchant_sync_log( $log_id )
|
|
{
|
|
global $mdb;
|
|
|
|
$log_id = (int) $log_id;
|
|
|
|
if ( $log_id <= 0 )
|
|
{
|
|
return false;
|
|
}
|
|
|
|
$pdo = $mdb -> delete( 'products_merchant_sync_log', [ 'id' => $log_id ] );
|
|
|
|
return $pdo -> rowCount() > 0;
|
|
}
|
|
|
|
static public function get_product_ads_keyword_context( $product_id )
|
|
{
|
|
global $mdb;
|
|
|
|
return $mdb -> query(
|
|
'SELECT
|
|
p.id AS product_id,
|
|
p.client_id,
|
|
cl.google_ads_customer_id
|
|
FROM products AS p
|
|
INNER JOIN clients AS cl ON cl.id = p.client_id
|
|
WHERE p.id = :product_id
|
|
LIMIT 1',
|
|
[ ':product_id' => (int) $product_id ]
|
|
) -> fetch( \PDO::FETCH_ASSOC );
|
|
}
|
|
|
|
static public function get_cached_keyword_planner_terms( $product_id, $source_url, $limit = 15 )
|
|
{
|
|
global $mdb;
|
|
|
|
$product_id = (int) $product_id;
|
|
$source_url = trim( (string) $source_url );
|
|
$limit = max( 1, (int) $limit );
|
|
|
|
if ( $product_id <= 0 || $source_url === '' )
|
|
{
|
|
return [];
|
|
}
|
|
|
|
return $mdb -> query(
|
|
'SELECT keyword_text, avg_monthly_searches, competition, competition_index
|
|
FROM products_keyword_planner_terms
|
|
WHERE product_id = :product_id
|
|
AND source_url = :source_url
|
|
ORDER BY avg_monthly_searches DESC, keyword_text ASC
|
|
LIMIT ' . $limit,
|
|
[
|
|
':product_id' => $product_id,
|
|
':source_url' => $source_url
|
|
]
|
|
) -> fetchAll( \PDO::FETCH_ASSOC );
|
|
}
|
|
|
|
static public function replace_keyword_planner_terms( $product_id, $source_url, $customer_id, $terms )
|
|
{
|
|
global $mdb;
|
|
|
|
$product_id = (int) $product_id;
|
|
$source_url = trim( (string) $source_url );
|
|
$customer_id = trim( (string) $customer_id );
|
|
$terms = is_array( $terms ) ? $terms : [];
|
|
|
|
if ( $product_id <= 0 || $source_url === '' )
|
|
{
|
|
return false;
|
|
}
|
|
|
|
$mdb -> delete( 'products_keyword_planner_terms', [
|
|
'AND' => [
|
|
'product_id' => $product_id,
|
|
'source_url' => $source_url
|
|
]
|
|
] );
|
|
|
|
if ( empty( $terms ) )
|
|
{
|
|
return true;
|
|
}
|
|
|
|
foreach ( $terms as $term )
|
|
{
|
|
$keyword_text = trim( (string) ( $term['keyword'] ?? '' ) );
|
|
if ( $keyword_text === '' )
|
|
{
|
|
continue;
|
|
}
|
|
|
|
$mdb -> insert( 'products_keyword_planner_terms', [
|
|
'product_id' => $product_id,
|
|
'source_url' => $source_url,
|
|
'keyword_text' => mb_substr( $keyword_text, 0, 255 ),
|
|
'avg_monthly_searches' => (int) ( $term['avg_monthly_searches'] ?? 0 ),
|
|
'competition' => $term['competition'] ?? null,
|
|
'competition_index' => isset( $term['competition_index'] ) ? (int) $term['competition_index'] : null,
|
|
'source_customer_id' => $customer_id !== '' ? $customer_id : null,
|
|
'date_add' => date( 'Y-m-d H:i:s' )
|
|
] );
|
|
}
|
|
|
|
return true;
|
|
}
|
|
|
|
static public function set_product_data( $product_id, $field, $value )
|
|
{
|
|
global $mdb;
|
|
|
|
$product_id = (int) $product_id;
|
|
$field = trim( (string) $field );
|
|
|
|
if ( $product_id <= 0 || $field === '' )
|
|
{
|
|
return false;
|
|
}
|
|
|
|
$result = false;
|
|
if ( self::is_product_core_field( $field ) )
|
|
{
|
|
$update_data = [ $field => $value ];
|
|
if ( $field === 'product_url' )
|
|
{
|
|
$product_url = trim( (string) $value );
|
|
if ( $product_url === '' || in_array( strtolower( $product_url ), [ '0', '-', 'null' ], true ) )
|
|
{
|
|
$update_data['product_url'] = null;
|
|
}
|
|
else
|
|
{
|
|
$update_data['merchant_url_not_found'] = 0;
|
|
}
|
|
|
|
$update_data['merchant_url_last_check'] = date( 'Y-m-d H:i:s' );
|
|
}
|
|
|
|
$result = $mdb -> update( 'products', $update_data, [ 'id' => $product_id ] );
|
|
}
|
|
|
|
return $result;
|
|
}
|
|
|
|
static public function get_product_history( $client_id, $product_id, $start, $limit, $campaign_id = 0, $ad_group_id = 0 )
|
|
{
|
|
global $mdb;
|
|
|
|
$limit = max( 1, (int) $limit );
|
|
$start = max( 0, (int) $start );
|
|
|
|
return $mdb -> query(
|
|
'SELECT
|
|
MAX( ph.id ) AS id,
|
|
SUM( ph.impressions ) AS impressions,
|
|
SUM( ph.clicks ) AS clicks,
|
|
CASE WHEN SUM( ph.impressions ) > 0 THEN ROUND( SUM( ph.clicks ) / SUM( ph.impressions ) * 100, 2 ) ELSE 0 END AS ctr,
|
|
SUM( ph.cost ) AS cost,
|
|
SUM( ph.conversions ) AS conversions,
|
|
SUM( ph.conversions_value ) AS conversions_value,
|
|
ph.date_add
|
|
FROM products_history AS ph
|
|
INNER JOIN products AS p ON p.id = ph.product_id
|
|
WHERE ph.product_id = :product_id
|
|
AND p.client_id = :client_id
|
|
AND ph.campaign_id = :campaign_id
|
|
AND ph.ad_group_id = :ad_group_id
|
|
GROUP BY ph.date_add
|
|
ORDER BY ph.date_add DESC
|
|
LIMIT ' . $start . ', ' . $limit,
|
|
[
|
|
':product_id' => (int) $product_id,
|
|
':client_id' => (int) $client_id,
|
|
':campaign_id' => (int) $campaign_id,
|
|
':ad_group_id' => (int) $ad_group_id
|
|
]
|
|
) -> fetchAll( \PDO::FETCH_ASSOC );
|
|
}
|
|
|
|
static public function get_records_total_product_history( $client_id, $product_id, $campaign_id = 0, $ad_group_id = 0 )
|
|
{
|
|
global $mdb;
|
|
return $mdb -> query(
|
|
'SELECT COUNT( DISTINCT ph.date_add )
|
|
FROM products_history AS ph
|
|
INNER JOIN products AS p ON p.id = ph.product_id
|
|
WHERE ph.product_id = :product_id
|
|
AND p.client_id = :client_id
|
|
AND ph.campaign_id = :campaign_id
|
|
AND ph.ad_group_id = :ad_group_id',
|
|
[
|
|
':product_id' => (int) $product_id,
|
|
':client_id' => (int) $client_id,
|
|
':campaign_id' => (int) $campaign_id,
|
|
':ad_group_id' => (int) $ad_group_id
|
|
]
|
|
) -> fetchColumn();
|
|
}
|
|
|
|
static public function get_product_history_30( $client_id, $product_id, $start, $limit, $campaign_id = 0, $ad_group_id = 0 )
|
|
{
|
|
global $mdb;
|
|
return $mdb -> query(
|
|
'SELECT ph3.*
|
|
FROM products_history_30 AS ph3
|
|
INNER JOIN products AS p ON p.id = ph3.product_id
|
|
WHERE ph3.product_id = :product_id
|
|
AND p.client_id = :client_id
|
|
AND ph3.campaign_id = :campaign_id
|
|
AND ph3.ad_group_id = :ad_group_id
|
|
ORDER BY ph3.date_add ASC
|
|
LIMIT ' . (int) $start . ', ' . (int) $limit,
|
|
[
|
|
':product_id' => (int) $product_id,
|
|
':client_id' => (int) $client_id,
|
|
':campaign_id' => (int) $campaign_id,
|
|
':ad_group_id' => (int) $ad_group_id
|
|
]
|
|
) -> fetchAll( \PDO::FETCH_ASSOC );
|
|
}
|
|
|
|
static public function get_impressions_30( $product_id, $campaign_id = null, $ad_group_id = null )
|
|
{
|
|
global $mdb;
|
|
|
|
$sql = 'SELECT COALESCE( SUM( impressions ), 0 ) AS total
|
|
FROM products_history
|
|
WHERE product_id = :product_id
|
|
AND date_add >= :date_from';
|
|
|
|
$params = [
|
|
':product_id' => (int) $product_id,
|
|
':date_from' => date( 'Y-m-d', strtotime( '-30 days', time() ) )
|
|
];
|
|
|
|
if ( $campaign_id !== null )
|
|
{
|
|
$sql .= ' AND campaign_id = :campaign_id';
|
|
$params[':campaign_id'] = (int) $campaign_id;
|
|
}
|
|
|
|
if ( $ad_group_id !== null )
|
|
{
|
|
$sql .= ' AND ad_group_id = :ad_group_id';
|
|
$params[':ad_group_id'] = (int) $ad_group_id;
|
|
}
|
|
|
|
return $mdb -> query( $sql, $params ) -> fetchColumn();
|
|
}
|
|
|
|
static public function get_clicks_30( $product_id, $campaign_id = null, $ad_group_id = null )
|
|
{
|
|
global $mdb;
|
|
|
|
$sql = 'SELECT COALESCE( SUM( clicks ), 0 ) AS total
|
|
FROM products_history
|
|
WHERE product_id = :product_id
|
|
AND date_add >= :date_from';
|
|
|
|
$params = [
|
|
':product_id' => (int) $product_id,
|
|
':date_from' => date( 'Y-m-d', strtotime( '-30 days', time() ) )
|
|
];
|
|
|
|
if ( $campaign_id !== null )
|
|
{
|
|
$sql .= ' AND campaign_id = :campaign_id';
|
|
$params[':campaign_id'] = (int) $campaign_id;
|
|
}
|
|
|
|
if ( $ad_group_id !== null )
|
|
{
|
|
$sql .= ' AND ad_group_id = :ad_group_id';
|
|
$params[':ad_group_id'] = (int) $ad_group_id;
|
|
}
|
|
|
|
return $mdb -> query( $sql, $params ) -> fetchColumn();
|
|
}
|
|
|
|
static public function add_product_comment( $product_id, $comment, $date = null )
|
|
{
|
|
global $mdb;
|
|
|
|
if ( !$date )
|
|
$date = date( 'Y-m-d' );
|
|
else
|
|
$date = date( 'Y-m-d', strtotime( $date ) );
|
|
|
|
if ( $mdb -> count( 'products_comments', [ 'AND' => [ 'product_id' => $product_id, 'date_add' => $date ] ] ) )
|
|
return $mdb -> update( 'products_comments', [ 'comment' => $comment ], [ 'AND' => [ 'product_id' => $product_id, 'date_add' => $date ] ] );
|
|
else
|
|
return $mdb -> insert( 'products_comments', [ 'product_id' => $product_id, 'comment' => $comment, 'date_add' => $date ] );
|
|
}
|
|
|
|
static public function get_product_scope_context( $product_id )
|
|
{
|
|
global $mdb;
|
|
|
|
return $mdb -> query(
|
|
'SELECT
|
|
p.id,
|
|
p.client_id,
|
|
p.offer_id,
|
|
p.name,
|
|
cl.google_ads_customer_id,
|
|
cl.google_merchant_account_id
|
|
FROM products AS p
|
|
INNER JOIN clients AS cl ON cl.id = p.client_id
|
|
WHERE p.id = :product_id
|
|
LIMIT 1',
|
|
[ ':product_id' => (int) $product_id ]
|
|
) -> fetch( \PDO::FETCH_ASSOC );
|
|
}
|
|
|
|
static public function get_campaign_scope_context( $campaign_id )
|
|
{
|
|
global $mdb;
|
|
|
|
return $mdb -> query(
|
|
'SELECT id, client_id, campaign_id, campaign_name
|
|
FROM campaigns
|
|
WHERE id = :campaign_id
|
|
LIMIT 1',
|
|
[ ':campaign_id' => (int) $campaign_id ]
|
|
) -> fetch( \PDO::FETCH_ASSOC );
|
|
}
|
|
|
|
static public function get_ad_group_scope_context( $ad_group_id )
|
|
{
|
|
global $mdb;
|
|
|
|
return $mdb -> query(
|
|
'SELECT id, campaign_id, ad_group_id, ad_group_name
|
|
FROM campaign_ad_groups
|
|
WHERE id = :ad_group_id
|
|
LIMIT 1',
|
|
[ ':ad_group_id' => (int) $ad_group_id ]
|
|
) -> fetch( \PDO::FETCH_ASSOC );
|
|
}
|
|
|
|
static private function get_next_local_campaign_external_id( $client_id )
|
|
{
|
|
global $mdb;
|
|
|
|
$min_external_id = (int) $mdb -> query(
|
|
'SELECT MIN( campaign_id )
|
|
FROM campaigns
|
|
WHERE client_id = :client_id',
|
|
[ ':client_id' => (int) $client_id ]
|
|
) -> fetchColumn();
|
|
|
|
if ( $min_external_id < 0 )
|
|
{
|
|
return $min_external_id - 1;
|
|
}
|
|
|
|
return -1;
|
|
}
|
|
|
|
static private function get_next_local_ad_group_external_id( $campaign_id )
|
|
{
|
|
global $mdb;
|
|
|
|
$min_external_id = (int) $mdb -> query(
|
|
'SELECT MIN( ad_group_id )
|
|
FROM campaign_ad_groups
|
|
WHERE campaign_id = :campaign_id',
|
|
[ ':campaign_id' => (int) $campaign_id ]
|
|
) -> fetchColumn();
|
|
|
|
if ( $min_external_id < 0 )
|
|
{
|
|
return $min_external_id - 1;
|
|
}
|
|
|
|
return -1;
|
|
}
|
|
|
|
static public function create_local_campaign( $client_id, $campaign_name )
|
|
{
|
|
global $mdb;
|
|
|
|
$client_id = (int) $client_id;
|
|
$campaign_name = trim( (string) $campaign_name );
|
|
|
|
if ( $client_id <= 0 || $campaign_name === '' )
|
|
{
|
|
return 0;
|
|
}
|
|
|
|
$existing_campaign_id = (int) $mdb -> get( 'campaigns', 'id', [
|
|
'AND' => [
|
|
'client_id' => $client_id,
|
|
'campaign_name' => $campaign_name
|
|
]
|
|
] );
|
|
|
|
if ( $existing_campaign_id > 0 )
|
|
{
|
|
return $existing_campaign_id;
|
|
}
|
|
|
|
$mdb -> insert( 'campaigns', [
|
|
'client_id' => $client_id,
|
|
'campaign_id' => self::get_next_local_campaign_external_id( $client_id ),
|
|
'campaign_name' => $campaign_name
|
|
] );
|
|
|
|
return (int) $mdb -> id();
|
|
}
|
|
|
|
static public function create_local_ad_group( $campaign_id, $ad_group_name )
|
|
{
|
|
global $mdb;
|
|
|
|
$campaign_id = (int) $campaign_id;
|
|
$ad_group_name = trim( (string) $ad_group_name );
|
|
|
|
if ( $campaign_id <= 0 || $ad_group_name === '' )
|
|
{
|
|
return 0;
|
|
}
|
|
|
|
$existing_ad_group_id = (int) $mdb -> get( 'campaign_ad_groups', 'id', [
|
|
'AND' => [
|
|
'campaign_id' => $campaign_id,
|
|
'ad_group_name' => $ad_group_name
|
|
]
|
|
] );
|
|
|
|
if ( $existing_ad_group_id > 0 )
|
|
{
|
|
return $existing_ad_group_id;
|
|
}
|
|
|
|
$mdb -> insert( 'campaign_ad_groups', [
|
|
'campaign_id' => $campaign_id,
|
|
'ad_group_id' => self::get_next_local_ad_group_external_id( $campaign_id ),
|
|
'ad_group_name' => $ad_group_name,
|
|
'impressions_30' => 0,
|
|
'clicks_30' => 0,
|
|
'cost_30' => 0,
|
|
'conversions_30' => 0,
|
|
'conversion_value_30' => 0,
|
|
'roas_30' => 0,
|
|
'impressions_all_time' => 0,
|
|
'clicks_all_time' => 0,
|
|
'cost_all_time' => 0,
|
|
'conversions_all_time' => 0,
|
|
'conversion_value_all_time' => 0,
|
|
'roas_all_time' => 0,
|
|
'date_sync' => date( 'Y-m-d' )
|
|
] );
|
|
|
|
return (int) $mdb -> id();
|
|
}
|
|
|
|
static public function assign_product_scope( $product_id, $campaign_id, $ad_group_id )
|
|
{
|
|
global $mdb;
|
|
|
|
$product_id = (int) $product_id;
|
|
$campaign_id = (int) $campaign_id;
|
|
$ad_group_id = (int) $ad_group_id;
|
|
|
|
if ( $product_id <= 0 || $campaign_id <= 0 || $ad_group_id <= 0 )
|
|
{
|
|
return false;
|
|
}
|
|
|
|
$product = self::get_product_scope_context( $product_id );
|
|
if ( !$product )
|
|
{
|
|
return false;
|
|
}
|
|
|
|
$campaign_client_id = (int) $mdb -> get( 'campaigns', 'client_id', [ 'id' => $campaign_id ] );
|
|
if ( $campaign_client_id <= 0 || $campaign_client_id !== (int) $product['client_id'] )
|
|
{
|
|
return false;
|
|
}
|
|
|
|
$ad_group_campaign_id = (int) $mdb -> get( 'campaign_ad_groups', 'campaign_id', [ 'id' => $ad_group_id ] );
|
|
if ( $ad_group_campaign_id <= 0 || $ad_group_campaign_id !== $campaign_id )
|
|
{
|
|
return false;
|
|
}
|
|
|
|
$scope_exists = (int) $mdb -> count( 'products_aggregate', [
|
|
'AND' => [
|
|
'product_id' => $product_id,
|
|
'campaign_id' => $campaign_id,
|
|
'ad_group_id' => $ad_group_id
|
|
]
|
|
] ) > 0;
|
|
|
|
if ( $scope_exists )
|
|
{
|
|
return true;
|
|
}
|
|
|
|
return $mdb -> insert( 'products_aggregate', [
|
|
'product_id' => $product_id,
|
|
'campaign_id' => $campaign_id,
|
|
'ad_group_id' => $ad_group_id,
|
|
'impressions_30' => 0,
|
|
'clicks_30' => 0,
|
|
'ctr_30' => 0,
|
|
'cost_30' => 0,
|
|
'conversions_30' => 0,
|
|
'conversion_value_30' => 0,
|
|
'roas_30' => 0,
|
|
'impressions_all_time' => 0,
|
|
'clicks_all_time' => 0,
|
|
'ctr_all_time' => 0,
|
|
'cost_all_time' => 0,
|
|
'conversions_all_time' => 0,
|
|
'conversion_value_all_time' => 0,
|
|
'roas_all_time' => 0,
|
|
'date_sync' => date( 'Y-m-d' )
|
|
] );
|
|
}
|
|
}
|