5126 lines
175 KiB
PHP
5126 lines
175 KiB
PHP
<?php
|
|
namespace controls;
|
|
class Cron
|
|
{
|
|
static private $current_cron_action = 'cron';
|
|
|
|
// Uniwersalny CRON pipeline.
|
|
// Jedno wywolanie = jeden klient + jeden dzien: kampanie -> produkty.
|
|
static public function cron_universal()
|
|
{
|
|
global $mdb;
|
|
|
|
self::$current_cron_action = __FUNCTION__;
|
|
self::touch_cron_invocation( __FUNCTION__ );
|
|
|
|
$clients_not_deleted_sql = self::sql_clients_not_deleted();
|
|
$clients_not_deleted_sql_c = self::sql_clients_not_deleted( 'c' );
|
|
$clients_deleted_sql_c = self::sql_clients_deleted( 'c' );
|
|
|
|
$api = new \services\GoogleAdsApi();
|
|
if ( !$api -> is_configured() )
|
|
{
|
|
self::output_cron_response( [ 'result' => 'Google Ads API nie jest skonfigurowane. Uzupelnij dane w Ustawieniach.' ] );
|
|
}
|
|
|
|
$conversion_window_days = self::get_conversion_window_days( true );
|
|
$default_end_date = date( 'Y-m-d', strtotime( '-1 day' ) );
|
|
$requested_date_raw = trim( (string) \S::get( 'date' ) );
|
|
$requested_ts = $requested_date_raw !== '' ? strtotime( $requested_date_raw ) : false;
|
|
$sync_date_raw = $requested_ts ? date( 'Y-m-d', $requested_ts ) : $default_end_date;
|
|
$sync_date = ( strtotime( $sync_date_raw ) > strtotime( $default_end_date ) ) ? $default_end_date : $sync_date_raw;
|
|
if ( !$sync_date || strtotime( $sync_date ) === false )
|
|
{
|
|
$sync_date = $default_end_date;
|
|
}
|
|
|
|
$sync_dates = self::build_backfill_dates( $sync_date, $conversion_window_days );
|
|
$campaign_sync_dates = [ $sync_date ];
|
|
$client_id = (int) \S::get( 'client_id' );
|
|
|
|
if ( $client_id > 0 )
|
|
{
|
|
$client = $mdb -> query(
|
|
"SELECT *
|
|
FROM clients
|
|
WHERE id = :id
|
|
AND COALESCE(active, 0) = 1
|
|
AND " . $clients_not_deleted_sql . "
|
|
LIMIT 1",
|
|
[ ':id' => $client_id ]
|
|
) -> fetch( \PDO::FETCH_ASSOC );
|
|
|
|
if ( !$client || trim( (string) ( $client['google_ads_customer_id'] ?? '' ) ) === '' )
|
|
{
|
|
self::output_cron_response( [ 'result' => 'Nie znaleziono aktywnego klienta z poprawnym Google Ads Customer ID.', 'client_id' => $client_id ] );
|
|
}
|
|
|
|
$campaign_errors = [];
|
|
$products_errors = [];
|
|
$processed_dates = [ $sync_date ];
|
|
|
|
$processed_records_total = 0;
|
|
$ad_groups_synced_total = 0;
|
|
$search_terms_history_synced_total = 0;
|
|
$search_terms_aggregated_total = 0;
|
|
$keywords_synced_total = 0;
|
|
$negative_keywords_synced_total = 0;
|
|
|
|
$processed_products_total = 0;
|
|
$products_skipped_total = 0;
|
|
$history_30_products_total = 0;
|
|
$products_temp_rows_total = 0;
|
|
$products_fetch_skipped_reasons = [];
|
|
|
|
$sync = self::sync_campaigns_snapshot_for_client( $client, $api, $sync_date );
|
|
$processed_records_total += (int) ( $sync['processed_records'] ?? 0 );
|
|
$ad_groups_synced_total += (int) ( $sync['ad_groups_synced'] ?? 0 );
|
|
if ( !empty( $sync['errors'] ) )
|
|
{
|
|
$campaign_errors = array_merge( $campaign_errors, (array) $sync['errors'] );
|
|
}
|
|
|
|
if ( empty( $campaign_errors ) )
|
|
{
|
|
$terms_sync = self::sync_campaign_terms_backfill_for_client(
|
|
(int) $client['id'],
|
|
(string) ( $client['google_ads_customer_id'] ?? '' ),
|
|
$api,
|
|
$sync_dates
|
|
);
|
|
$search_terms_history_synced_total = (int) ( $terms_sync['history_synced'] ?? 0 );
|
|
$search_terms_aggregated_total = (int) ( $terms_sync['aggregated'] ?? 0 );
|
|
if ( !empty( $terms_sync['errors'] ) )
|
|
{
|
|
$campaign_errors = array_merge( $campaign_errors, (array) $terms_sync['errors'] );
|
|
}
|
|
}
|
|
|
|
$last_day_in_window = end( $sync_dates );
|
|
if ( empty( $campaign_errors ) && $last_day_in_window && $sync_date === $last_day_in_window )
|
|
{
|
|
$kw_sync = self::sync_campaign_keywords_and_negatives_for_client(
|
|
(int) $client['id'],
|
|
(string) ( $client['google_ads_customer_id'] ?? '' ),
|
|
$api,
|
|
$sync_date
|
|
);
|
|
$keywords_synced_total = (int) ( $kw_sync['keywords_synced'] ?? 0 );
|
|
$negative_keywords_synced_total = (int) ( $kw_sync['negative_keywords_synced'] ?? 0 );
|
|
if ( !empty( $kw_sync['errors'] ) )
|
|
{
|
|
$campaign_errors = array_merge( $campaign_errors, (array) $kw_sync['errors'] );
|
|
}
|
|
}
|
|
|
|
$products_sync = self::sync_products_fetch_for_client( $client, $api, $sync_date );
|
|
$processed_products_total += (int) ( $products_sync['processed_products'] ?? 0 );
|
|
$products_skipped_total += (int) ( $products_sync['skipped'] ?? 0 );
|
|
$products_fetch_skipped_reason = trim( (string) ( $products_sync['fetch_skipped_reason'] ?? '' ) );
|
|
if ( $products_fetch_skipped_reason !== '' )
|
|
{
|
|
$products_fetch_skipped_reasons[ $products_fetch_skipped_reason ] = true;
|
|
}
|
|
if ( !empty( $products_sync['errors'] ) )
|
|
{
|
|
$products_errors = array_merge( $products_errors, (array) $products_sync['errors'] );
|
|
}
|
|
|
|
if ( empty( $products_errors ) )
|
|
{
|
|
$history_30_products_total += (int) self::aggregate_products_history_30_for_client( (int) $client['id'], $sync_date );
|
|
$products_temp_rows_total += (int) self::rebuild_products_temp_for_client( (int) $client['id'] );
|
|
}
|
|
|
|
$errors = array_merge( $campaign_errors, $products_errors );
|
|
|
|
self::output_cron_response( [
|
|
'result' => empty( $errors ) ? 'Synchronizacja uniwersalna zakonczona.' : 'Synchronizacja uniwersalna zakonczona z bledami.',
|
|
'client_id' => (int) $client['id'],
|
|
'active_date' => $sync_date,
|
|
'conversion_window_days' => $conversion_window_days,
|
|
'dates_synced' => $sync_dates,
|
|
'processed_dates' => $processed_dates,
|
|
'processed_records' => $processed_records_total,
|
|
'ad_groups_synced' => $ad_groups_synced_total,
|
|
'search_terms_history_synced' => $search_terms_history_synced_total,
|
|
'search_terms_aggregated' => $search_terms_aggregated_total,
|
|
'keywords_synced' => $keywords_synced_total,
|
|
'negative_keywords_synced' => $negative_keywords_synced_total,
|
|
'processed_products' => $processed_products_total,
|
|
'products_skipped' => $products_skipped_total,
|
|
'products_fetch_skipped_reasons' => array_keys( $products_fetch_skipped_reasons ),
|
|
'history_30_products' => $history_30_products_total,
|
|
'products_temp_rows' => $products_temp_rows_total,
|
|
'errors' => $errors
|
|
] );
|
|
}
|
|
|
|
self::cleanup_old_sync_rows( 30 );
|
|
$mdb -> query(
|
|
"DELETE cs FROM cron_sync_status cs
|
|
LEFT JOIN clients c ON cs.client_id = c.id
|
|
WHERE cs.pipeline = 'campaigns'
|
|
AND ( c.id IS NULL OR " . $clients_deleted_sql_c . " OR COALESCE(c.active, 0) <> 1 )"
|
|
);
|
|
$mdb -> query(
|
|
"DELETE cs FROM cron_sync_status cs
|
|
LEFT JOIN clients c ON cs.client_id = c.id
|
|
WHERE cs.pipeline = 'products'
|
|
AND ( c.id IS NULL OR " . $clients_deleted_sql_c . " OR COALESCE(c.active, 0) <> 1 )"
|
|
);
|
|
|
|
$client_ids = $mdb -> query(
|
|
"SELECT id
|
|
FROM clients
|
|
WHERE " . $clients_not_deleted_sql . "
|
|
AND COALESCE(active, 0) = 1
|
|
AND TRIM(COALESCE(google_ads_customer_id, '')) <> ''
|
|
ORDER BY id ASC"
|
|
) -> fetchAll( \PDO::FETCH_COLUMN );
|
|
$client_ids = array_values( array_unique( array_map( 'intval', $client_ids ) ) );
|
|
|
|
if ( empty( $client_ids ) )
|
|
{
|
|
self::output_cron_response( [ 'result' => 'Brak aktywnych klientow z ustawionym Google Ads Customer ID.' ] );
|
|
}
|
|
|
|
self::ensure_sync_rows( 'campaigns', $campaign_sync_dates, $client_ids );
|
|
self::ensure_sync_rows( 'products', $sync_dates, $client_ids );
|
|
self::cleanup_pipeline_rows_outside_window( 'campaigns', $campaign_sync_dates );
|
|
self::cleanup_pipeline_rows_outside_window( 'products', $sync_dates );
|
|
|
|
$active_campaign_client_id = self::get_active_client( 'campaigns' );
|
|
$active_products_client_id = self::get_active_client( 'products' );
|
|
|
|
if ( !$active_campaign_client_id && !$active_products_client_id )
|
|
{
|
|
$merchant_client = $mdb -> query(
|
|
"SELECT c.*
|
|
FROM clients c
|
|
WHERE " . $clients_not_deleted_sql_c . "
|
|
AND COALESCE(c.active, 0) = 1
|
|
AND TRIM(COALESCE(c.google_ads_customer_id, '')) <> ''
|
|
AND TRIM(COALESCE(c.google_merchant_account_id, '')) <> ''
|
|
AND EXISTS (
|
|
SELECT 1
|
|
FROM products p
|
|
WHERE p.client_id = c.id
|
|
AND TRIM(COALESCE(p.offer_id, '')) <> ''
|
|
AND (
|
|
TRIM(COALESCE(p.product_url, '')) = ''
|
|
OR LOWER(TRIM(p.product_url)) IN ('0', '-', 'null')
|
|
)
|
|
AND (
|
|
COALESCE(p.merchant_url_not_found, 0) = 0
|
|
OR (
|
|
COALESCE(p.merchant_url_not_found, 0) = 1
|
|
AND (
|
|
p.merchant_url_last_check IS NULL
|
|
OR p.merchant_url_last_check < DATE_SUB(NOW(), INTERVAL 7 DAY)
|
|
)
|
|
)
|
|
)
|
|
)
|
|
ORDER BY c.id ASC
|
|
LIMIT 1"
|
|
) -> fetch( \PDO::FETCH_ASSOC );
|
|
|
|
if ( is_array( $merchant_client ) && !empty( $merchant_client ) )
|
|
{
|
|
$urls_sync = self::sync_products_urls_and_alerts_for_client( $merchant_client, $api, $sync_date );
|
|
$merchant_errors = (array) ( $urls_sync['errors'] ?? [] );
|
|
|
|
self::output_cron_response( [
|
|
'result' => empty( $merchant_errors ) ? 'Synchronizacja URL produktow (Merchant) zakonczona.' : 'Synchronizacja URL produktow (Merchant) zakonczona z bledami.',
|
|
'merchant_only' => 1,
|
|
'active_client_id' => (int) ( $merchant_client['id'] ?? 0 ),
|
|
'active_date' => $sync_date,
|
|
'conversion_window_days' => $conversion_window_days,
|
|
'dates_synced' => $sync_dates,
|
|
'merchant_urls_checked' => (int) ( $urls_sync['checked_products'] ?? 0 ),
|
|
'merchant_urls_updated' => (int) ( $urls_sync['updated_urls'] ?? 0 ),
|
|
'merchant_missing_in_mc_count' => (int) ( $urls_sync['missing_in_merchant_count'] ?? 0 ),
|
|
'merchant_missing_offer_ids' => array_values( array_unique( array_map( 'strval', (array) ( $urls_sync['missing_offer_ids'] ?? [] ) ) ) ),
|
|
'errors' => $merchant_errors
|
|
] );
|
|
}
|
|
|
|
// --- Supplemental Feed: generuj raz dziennie gdy pipeline'y ukonczone ---
|
|
$today = date( 'Y-m-d' );
|
|
$feed_last_date = (string) self::get_setting_value( 'cron_supplemental_feed_last_date', '' );
|
|
|
|
if ( $feed_last_date !== $today )
|
|
{
|
|
$feed_clients = $mdb -> query(
|
|
"SELECT id, name, google_merchant_account_id FROM clients
|
|
WHERE " . $clients_not_deleted_sql . "
|
|
AND COALESCE(active, 0) = 1
|
|
AND google_merchant_account_id IS NOT NULL
|
|
AND google_merchant_account_id <> ''
|
|
ORDER BY id ASC"
|
|
) -> fetchAll( \PDO::FETCH_ASSOC );
|
|
|
|
$feed_generated = 0;
|
|
$feed_products_total = 0;
|
|
$feed_errors = [];
|
|
$feed_details = [];
|
|
|
|
foreach ( $feed_clients as $fc )
|
|
{
|
|
try
|
|
{
|
|
$feed_result = \services\SupplementalFeed::generate_for_client( (int) $fc['id'] );
|
|
$feed_generated++;
|
|
$feed_products_total += (int) $feed_result['products_written'];
|
|
$feed_details[] = [
|
|
'client_id' => (int) $fc['id'],
|
|
'client_name' => (string) $fc['name'],
|
|
'products_written' => (int) $feed_result['products_written'],
|
|
'file' => (string) $feed_result['file']
|
|
];
|
|
}
|
|
catch ( \Throwable $e )
|
|
{
|
|
$feed_errors[] = 'Feed klient #' . $fc['id'] . ': ' . $e -> getMessage();
|
|
}
|
|
}
|
|
|
|
self::set_setting_value( 'cron_supplemental_feed_last_date', $today );
|
|
self::set_setting_value( 'cron_supplemental_feed_last_count', (string) $feed_products_total );
|
|
self::set_setting_value( 'cron_supplemental_feed_files', (string) $feed_generated );
|
|
|
|
self::output_cron_response( [
|
|
'result' => 'Supplemental feed: ' . $feed_generated . ' plikow, ' . $feed_products_total . ' produktow.',
|
|
'supplemental_feed' => 1,
|
|
'feed_generated' => $feed_generated,
|
|
'feed_products_total' => $feed_products_total,
|
|
'feed_details' => $feed_details,
|
|
'active_date' => $sync_date,
|
|
'errors' => $feed_errors
|
|
] );
|
|
}
|
|
|
|
self::output_cron_response( [
|
|
'result' => 'Wszyscy aktywni klienci zostali przetworzeni dla calego okna dat.',
|
|
'active_date' => $sync_date,
|
|
'conversion_window_days' => $conversion_window_days,
|
|
'dates_synced' => $sync_dates,
|
|
'processed_clients' => count( $client_ids ),
|
|
'total_clients' => count( $client_ids )
|
|
] );
|
|
}
|
|
|
|
$active_client_id = $active_campaign_client_id ?: $active_products_client_id;
|
|
$dates_per_run = 1;
|
|
$campaign_dates_batch = [];
|
|
$products_dates_batch = [];
|
|
|
|
if ( $active_campaign_client_id && $active_campaign_client_id === $active_client_id )
|
|
{
|
|
$campaign_dates_batch = self::get_pending_dates_for_client( 'campaigns', $active_client_id, 'pending', 1 );
|
|
}
|
|
if ( $active_products_client_id && $active_products_client_id === $active_client_id )
|
|
{
|
|
$products_dates_batch = self::get_pending_dates_for_client( 'products', $active_client_id, 'pending', 1 );
|
|
}
|
|
|
|
if ( empty( $campaign_dates_batch ) && empty( $products_dates_batch ) )
|
|
{
|
|
self::output_cron_response( [
|
|
'result' => 'Brak dat do przetworzenia dla aktywnego klienta. Kolejne wywolanie przejdzie dalej.',
|
|
'active_client_id' => $active_client_id
|
|
] );
|
|
}
|
|
|
|
$selected_client = $mdb -> query(
|
|
"SELECT *
|
|
FROM clients
|
|
WHERE id = :id
|
|
AND COALESCE(active, 0) = 1
|
|
AND " . $clients_not_deleted_sql . "
|
|
LIMIT 1",
|
|
[ ':id' => $active_client_id ]
|
|
) -> fetch( \PDO::FETCH_ASSOC );
|
|
if ( !$selected_client || trim( (string) ( $selected_client['google_ads_customer_id'] ?? '' ) ) === '' )
|
|
{
|
|
self::output_cron_response( [
|
|
'result' => 'Nie udalo sie znalezc aktywnego klienta do synchronizacji. ID: ' . $active_client_id,
|
|
'active_client_id' => $active_client_id,
|
|
'errors' => [ 'Klient ID ' . $active_client_id . ' nie znaleziony lub nieaktywny.' ]
|
|
] );
|
|
}
|
|
|
|
$active_date = (string) ( $campaign_dates_batch[0] ?? ( $products_dates_batch[0] ?? '' ) );
|
|
if ( $active_date === '' )
|
|
{
|
|
self::output_cron_response( [
|
|
'result' => 'Brak daty do przetworzenia.',
|
|
'active_client_id' => $active_client_id
|
|
] );
|
|
}
|
|
|
|
$run_campaigns = !empty( $campaign_dates_batch ) && (string) $campaign_dates_batch[0] === $active_date;
|
|
$run_products = !empty( $products_dates_batch ) && (string) $products_dates_batch[0] === $active_date;
|
|
|
|
$campaign_errors = [];
|
|
$products_errors = [];
|
|
$processed_records_total = 0;
|
|
$ad_groups_synced_total = 0;
|
|
$search_terms_history_synced_total = 0;
|
|
$search_terms_aggregated_total = 0;
|
|
$keywords_synced_total = 0;
|
|
$negative_keywords_synced_total = 0;
|
|
$processed_products_total = 0;
|
|
$products_skipped_total = 0;
|
|
$history_30_products_total = 0;
|
|
$products_temp_rows_total = 0;
|
|
$products_fetch_skipped_reasons = [];
|
|
|
|
$products_sync_skipped_reason = '';
|
|
|
|
if ( $run_campaigns )
|
|
{
|
|
$sync = self::sync_campaigns_snapshot_for_client( $selected_client, $api, $active_date );
|
|
$processed_records_total += (int) ( $sync['processed_records'] ?? 0 );
|
|
$ad_groups_synced_total += (int) ( $sync['ad_groups_synced'] ?? 0 );
|
|
if ( !empty( $sync['errors'] ) )
|
|
{
|
|
$campaign_errors = array_merge( $campaign_errors, (array) $sync['errors'] );
|
|
}
|
|
|
|
if ( empty( $campaign_errors ) )
|
|
{
|
|
$terms_sync = self::sync_campaign_terms_backfill_for_client(
|
|
(int) $selected_client['id'],
|
|
(string) ( $selected_client['google_ads_customer_id'] ?? '' ),
|
|
$api,
|
|
$sync_dates
|
|
);
|
|
$search_terms_history_synced_total = (int) ( $terms_sync['history_synced'] ?? 0 );
|
|
$search_terms_aggregated_total = (int) ( $terms_sync['aggregated'] ?? 0 );
|
|
if ( !empty( $terms_sync['errors'] ) )
|
|
{
|
|
$campaign_errors = array_merge( $campaign_errors, (array) $terms_sync['errors'] );
|
|
}
|
|
}
|
|
|
|
if ( empty( $campaign_errors ) )
|
|
{
|
|
$kw_sync = self::sync_campaign_keywords_and_negatives_for_client(
|
|
(int) $selected_client['id'],
|
|
(string) ( $selected_client['google_ads_customer_id'] ?? '' ),
|
|
$api,
|
|
$active_date
|
|
);
|
|
$keywords_synced_total = (int) ( $kw_sync['keywords_synced'] ?? 0 );
|
|
$negative_keywords_synced_total = (int) ( $kw_sync['negative_keywords_synced'] ?? 0 );
|
|
if ( !empty( $kw_sync['errors'] ) )
|
|
{
|
|
$campaign_errors = array_merge( $campaign_errors, (array) $kw_sync['errors'] );
|
|
}
|
|
}
|
|
}
|
|
|
|
if ( $run_products )
|
|
{
|
|
$products_sync = self::sync_products_fetch_for_client( $selected_client, $api, $active_date );
|
|
$processed_products_total += (int) ( $products_sync['processed_products'] ?? 0 );
|
|
$products_skipped_total += (int) ( $products_sync['skipped'] ?? 0 );
|
|
$products_fetch_skipped_reason = trim( (string) ( $products_sync['fetch_skipped_reason'] ?? '' ) );
|
|
if ( $products_fetch_skipped_reason !== '' )
|
|
{
|
|
$products_fetch_skipped_reasons[ $products_fetch_skipped_reason ] = true;
|
|
}
|
|
if ( !empty( $products_sync['errors'] ) )
|
|
{
|
|
$products_errors = array_merge( $products_errors, (array) $products_sync['errors'] );
|
|
}
|
|
|
|
if ( empty( $products_errors ) )
|
|
{
|
|
$history_30_products_total += (int) self::aggregate_products_history_30_for_client( $active_client_id, $active_date );
|
|
$products_temp_rows_total += (int) self::rebuild_products_temp_for_client( $active_client_id );
|
|
}
|
|
}
|
|
else
|
|
{
|
|
$products_sync_skipped_reason = 'already_done_for_day';
|
|
}
|
|
|
|
if ( $run_campaigns )
|
|
{
|
|
$campaign_phase = empty( $campaign_errors ) ? 'done' : 'pending';
|
|
$campaign_error_text = empty( $campaign_errors ) ? null : implode( '; ', array_values( array_unique( array_map( 'strval', $campaign_errors ) ) ) );
|
|
self::mark_sync_phase( 'campaigns', $active_date, $active_client_id, $campaign_phase, $campaign_error_text );
|
|
}
|
|
|
|
if ( $run_products )
|
|
{
|
|
$products_phase = empty( $products_errors ) ? 'done' : 'pending';
|
|
$products_error_text = empty( $products_errors ) ? null : implode( '; ', array_values( array_unique( array_map( 'strval', $products_errors ) ) ) );
|
|
self::mark_sync_phase( 'products', $active_date, $active_client_id, $products_phase, $products_error_text );
|
|
}
|
|
|
|
$errors = array_merge( $campaign_errors, $products_errors );
|
|
|
|
$campaign_done_count = (int) $mdb -> query(
|
|
"SELECT COUNT(*)
|
|
FROM cron_sync_status cs
|
|
INNER JOIN clients c ON cs.client_id = c.id
|
|
WHERE cs.pipeline = 'campaigns'
|
|
AND cs.client_id = :client_id
|
|
AND cs.phase = 'done'
|
|
AND " . $clients_not_deleted_sql_c . "
|
|
AND COALESCE(c.active, 0) = 1
|
|
AND cs.sync_date >= DATE_SUB(CURDATE(), INTERVAL 90 DAY)",
|
|
[ ':client_id' => $active_client_id ]
|
|
) -> fetchColumn();
|
|
$campaign_total_count = (int) $mdb -> query(
|
|
"SELECT COUNT(*)
|
|
FROM cron_sync_status cs
|
|
INNER JOIN clients c ON cs.client_id = c.id
|
|
WHERE cs.pipeline = 'campaigns'
|
|
AND cs.client_id = :client_id
|
|
AND " . $clients_not_deleted_sql_c . "
|
|
AND COALESCE(c.active, 0) = 1
|
|
AND cs.sync_date >= DATE_SUB(CURDATE(), INTERVAL 90 DAY)",
|
|
[ ':client_id' => $active_client_id ]
|
|
) -> fetchColumn();
|
|
|
|
$products_done_count = (int) $mdb -> query(
|
|
"SELECT COUNT(*)
|
|
FROM cron_sync_status cs
|
|
INNER JOIN clients c ON cs.client_id = c.id
|
|
WHERE cs.pipeline = 'products'
|
|
AND cs.client_id = :client_id
|
|
AND cs.phase = 'done'
|
|
AND " . $clients_not_deleted_sql_c . "
|
|
AND COALESCE(c.active, 0) = 1
|
|
AND cs.sync_date >= DATE_SUB(CURDATE(), INTERVAL 90 DAY)",
|
|
[ ':client_id' => $active_client_id ]
|
|
) -> fetchColumn();
|
|
$products_total_count = (int) $mdb -> query(
|
|
"SELECT COUNT(*)
|
|
FROM cron_sync_status cs
|
|
INNER JOIN clients c ON cs.client_id = c.id
|
|
WHERE cs.pipeline = 'products'
|
|
AND cs.client_id = :client_id
|
|
AND " . $clients_not_deleted_sql_c . "
|
|
AND COALESCE(c.active, 0) = 1
|
|
AND cs.sync_date >= DATE_SUB(CURDATE(), INTERVAL 90 DAY)",
|
|
[ ':client_id' => $active_client_id ]
|
|
) -> fetchColumn();
|
|
|
|
$campaign_remaining_dates = max( 0, $campaign_total_count - $campaign_done_count );
|
|
$products_remaining_dates = max( 0, $products_total_count - $products_done_count );
|
|
$remaining_dates = max( $campaign_remaining_dates, $products_remaining_dates );
|
|
$estimated_calls_remaining = (int) ceil( $remaining_dates / max( 1, $dates_per_run ) );
|
|
|
|
self::output_cron_response( [
|
|
'result' => empty( $errors ) ? 'Synchronizacja uniwersalna zakonczona.' : 'Synchronizacja uniwersalna zakonczona z bledami.',
|
|
'active_client_id' => $active_client_id,
|
|
'dates_per_run' => $dates_per_run,
|
|
'dates_processed_in_call' => 1,
|
|
'processed_dates' => [ $active_date ],
|
|
'campaigns_processed_in_call' => $run_campaigns ? 1 : 0,
|
|
'products_processed_in_call' => $run_products ? 1 : 0,
|
|
'remaining_dates' => $remaining_dates,
|
|
'campaigns_remaining_dates' => $campaign_remaining_dates,
|
|
'products_remaining_dates' => $products_remaining_dates,
|
|
'estimated_calls_remaining' => $estimated_calls_remaining,
|
|
'active_date' => $active_date,
|
|
'conversion_window_days' => $conversion_window_days,
|
|
'dates_synced' => $sync_dates,
|
|
'processed_records' => $processed_records_total,
|
|
'ad_groups_synced' => $ad_groups_synced_total,
|
|
'search_terms_history_synced' => $search_terms_history_synced_total,
|
|
'search_terms_aggregated' => $search_terms_aggregated_total,
|
|
'keywords_synced' => $keywords_synced_total,
|
|
'negative_keywords_synced' => $negative_keywords_synced_total,
|
|
'processed_products' => $processed_products_total,
|
|
'products_skipped' => $products_skipped_total,
|
|
'products_fetch_skipped_reasons' => array_keys( $products_fetch_skipped_reasons ),
|
|
'products_sync_skipped_reason' => $products_sync_skipped_reason,
|
|
'history_30_products' => $history_30_products_total,
|
|
'products_temp_rows' => $products_temp_rows_total,
|
|
'total_clients' => count( $client_ids ),
|
|
'errors' => $errors
|
|
] );
|
|
}
|
|
|
|
static public function cron_products_urls()
|
|
{
|
|
global $mdb, $settings;
|
|
self::$current_cron_action = __FUNCTION__;
|
|
self::touch_cron_invocation( __FUNCTION__ );
|
|
|
|
$api = new \services\GoogleAdsApi();
|
|
if ( !$api -> is_merchant_configured() )
|
|
{
|
|
echo json_encode( [
|
|
'result' => 'Merchant API nie jest skonfigurowane. Uzupelnij OAuth2 Client ID/Secret oraz Merchant Refresh Token w Ustawieniach.'
|
|
] );
|
|
exit;
|
|
}
|
|
|
|
$client_id = (int) \S::get( 'client_id' );
|
|
$batch_limit = (int) \S::get( 'limit' );
|
|
$debug_mode = (int) \S::get( 'debug' ) === 1;
|
|
if ( $batch_limit <= 0 )
|
|
{
|
|
$batch_limit = (int) ( $settings['cron_products_urls_limit_per_client'] ?? 100 );
|
|
}
|
|
if ( $batch_limit <= 0 )
|
|
{
|
|
$batch_limit = 100;
|
|
}
|
|
$batch_limit = min( 1000, $batch_limit );
|
|
|
|
$clients_per_run_default = (int) ( $settings['cron_products_urls_clients_per_run'] ?? ( $settings['cron_products_clients_per_run'] ?? 1 ) );
|
|
if ( $clients_per_run_default <= 0 )
|
|
{
|
|
$clients_per_run_default = 1;
|
|
}
|
|
|
|
$clients_per_run = (int) \S::get( 'clients_per_run' );
|
|
if ( $clients_per_run <= 0 )
|
|
{
|
|
$clients_per_run = (int) self::get_setting_value( 'cron_products_urls_clients_per_run', $clients_per_run_default );
|
|
}
|
|
if ( $clients_per_run <= 0 )
|
|
{
|
|
$clients_per_run = $clients_per_run_default;
|
|
}
|
|
$clients_per_run = min( 20, $clients_per_run );
|
|
|
|
$where = "deleted = 0 AND google_merchant_account_id IS NOT NULL AND google_merchant_account_id <> ''";
|
|
if ( $client_id > 0 )
|
|
{
|
|
$where .= ' AND id = ' . $client_id;
|
|
}
|
|
|
|
$clients = $mdb -> query( 'SELECT id, name, google_merchant_account_id FROM clients WHERE ' . $where . ' ORDER BY id ASC' ) -> fetchAll( \PDO::FETCH_ASSOC );
|
|
|
|
if ( !is_array( $clients ) || empty( $clients ) )
|
|
{
|
|
echo json_encode( [
|
|
'result' => 'Brak klientow z ustawionym Merchant Account ID.',
|
|
'processed_clients' => 0,
|
|
'checked_products' => 0,
|
|
'updated_urls' => 0,
|
|
'errors' => []
|
|
] );
|
|
exit;
|
|
}
|
|
|
|
$total_clients_available = count( $clients );
|
|
if ( $client_id <= 0 )
|
|
{
|
|
$last_client_cursor = (int) self::get_setting_value( 'cron_products_urls_last_client_id', 0 );
|
|
$clients = self::pick_clients_batch_by_cursor( $clients, $clients_per_run, $last_client_cursor );
|
|
}
|
|
else
|
|
{
|
|
$clients_per_run = 1;
|
|
}
|
|
|
|
$checked_products = 0;
|
|
$updated_urls = 0;
|
|
$unresolved_products = 0;
|
|
$processed_clients = 0;
|
|
$errors = [];
|
|
$details = [];
|
|
|
|
foreach ( $clients as $client )
|
|
{
|
|
$processed_clients++;
|
|
$selected_products = self::get_products_missing_url_for_client( (int) $client['id'], $batch_limit );
|
|
$product_count = count( $selected_products );
|
|
$diag = $debug_mode ? self::get_products_url_sync_diagnostics_for_client( (int) $client['id'] ) : null;
|
|
|
|
if ( $product_count === 0 )
|
|
{
|
|
$detail_row = [
|
|
'client_id' => (int) $client['id'],
|
|
'client_name' => (string) $client['name'],
|
|
'merchant_account_id' => (string) $client['google_merchant_account_id'],
|
|
'selected_products' => 0,
|
|
'updated_urls' => 0,
|
|
'unresolved_products' => 0
|
|
];
|
|
|
|
if ( $debug_mode )
|
|
{
|
|
$detail_row['diag'] = $diag;
|
|
}
|
|
|
|
$details[] = $detail_row;
|
|
continue;
|
|
}
|
|
$checked_products += $product_count;
|
|
|
|
$offer_ids = [];
|
|
foreach ( $selected_products as $row )
|
|
{
|
|
$offer_ids[] = (string) $row['offer_id'];
|
|
}
|
|
|
|
$links_map = $api -> get_merchant_product_links_for_offer_ids( (string) $client['google_merchant_account_id'], $offer_ids );
|
|
if ( $links_map === false )
|
|
{
|
|
$last_err = (string) \services\GoogleAdsApi::get_setting( 'google_merchant_last_error' );
|
|
$errors[] = 'Blad Merchant API dla klienta ' . $client['name'] . ' (ID: ' . $client['id'] . '): ' . $last_err;
|
|
$unresolved_products += $product_count;
|
|
|
|
$detail_row = [
|
|
'client_id' => (int) $client['id'],
|
|
'client_name' => (string) $client['name'],
|
|
'merchant_account_id' => (string) $client['google_merchant_account_id'],
|
|
'selected_products' => $product_count,
|
|
'updated_urls' => 0,
|
|
'unresolved_products' => $product_count
|
|
];
|
|
|
|
if ( $debug_mode )
|
|
{
|
|
$detail_row['diag'] = $diag;
|
|
}
|
|
|
|
$details[] = $detail_row;
|
|
continue;
|
|
}
|
|
|
|
$client_updated = 0;
|
|
foreach ( $selected_products as $row )
|
|
{
|
|
$offer_id = (string) $row['offer_id'];
|
|
if ( !isset( $links_map[ $offer_id ] ) )
|
|
{
|
|
continue;
|
|
}
|
|
|
|
\factory\Products::set_product_data( (int) $row['product_id'], 'product_url', (string) $links_map[ $offer_id ] );
|
|
$client_updated++;
|
|
}
|
|
|
|
$updated_urls += $client_updated;
|
|
$client_unresolved = max( 0, $product_count - $client_updated );
|
|
$unresolved_products += $client_unresolved;
|
|
|
|
$detail_row = [
|
|
'client_id' => (int) $client['id'],
|
|
'client_name' => (string) $client['name'],
|
|
'merchant_account_id' => (string) $client['google_merchant_account_id'],
|
|
'selected_products' => $product_count,
|
|
'updated_urls' => $client_updated,
|
|
'unresolved_products' => $client_unresolved
|
|
];
|
|
|
|
if ( $debug_mode )
|
|
{
|
|
$detail_row['diag'] = $diag;
|
|
}
|
|
|
|
$details[] = $detail_row;
|
|
}
|
|
|
|
if ( $client_id <= 0 && !empty( $clients ) )
|
|
{
|
|
$last_client = end( $clients );
|
|
$last_client_id = (int) ( $last_client['id'] ?? 0 );
|
|
if ( $last_client_id > 0 )
|
|
{
|
|
self::set_setting_value( 'cron_products_urls_last_client_id', (string) $last_client_id );
|
|
}
|
|
}
|
|
|
|
echo json_encode( [
|
|
'result' => empty( $errors ) ? 'Synchronizacja URL produktow zakonczona.' : 'Synchronizacja URL produktow zakonczona z bledami.',
|
|
'total_clients_available' => $total_clients_available,
|
|
'processed_clients' => $processed_clients,
|
|
'clients_per_run' => $clients_per_run,
|
|
'checked_products' => $checked_products,
|
|
'updated_urls' => $updated_urls,
|
|
'unresolved_products' => $unresolved_products,
|
|
'errors' => $errors,
|
|
'details' => $details
|
|
] );
|
|
exit;
|
|
}
|
|
|
|
static private function get_products_url_sync_diagnostics_for_client( $client_id )
|
|
{
|
|
global $mdb;
|
|
|
|
$client_id = (int) $client_id;
|
|
if ( $client_id <= 0 )
|
|
{
|
|
return [];
|
|
}
|
|
|
|
$diag = [];
|
|
|
|
$diag['products_total'] = (int) $mdb -> query( 'SELECT COUNT(*) FROM products WHERE client_id = ' . $client_id ) -> fetchColumn();
|
|
$diag['products_not_deleted'] = (int) $mdb -> query( 'SELECT COUNT(*) FROM products WHERE client_id = ' . $client_id ) -> fetchColumn();
|
|
$diag['products_with_offer_id'] = (int) $mdb -> query( 'SELECT COUNT(*) FROM products WHERE client_id = ' . $client_id . ' AND TRIM( COALESCE( offer_id, "" ) ) <> ""' ) -> fetchColumn();
|
|
$diag['products_with_pd_rows'] = 0;
|
|
$diag['products_with_real_url'] = (int) $mdb -> query( 'SELECT COUNT(*) FROM products p WHERE p.client_id = ' . $client_id . ' AND TRIM( COALESCE( p.offer_id, \"\" ) ) <> \"\" AND TRIM( COALESCE( p.product_url, \"\" ) ) <> \"\" AND LOWER( TRIM( p.product_url ) ) NOT IN ( \"0\", \"-\", \"null\" )' ) -> fetchColumn();
|
|
$diag['products_missing_url'] = (int) $mdb -> query( 'SELECT COUNT(*) FROM products p WHERE p.client_id = ' . $client_id . ' AND TRIM( COALESCE( p.offer_id, \"\" ) ) <> \"\" AND p.product_url IS NULL' ) -> fetchColumn();
|
|
$diag['products_missing_url_pending_check'] = (int) $mdb -> query( 'SELECT COUNT(*) FROM products p WHERE p.client_id = ' . $client_id . ' AND TRIM( COALESCE( p.offer_id, \"\" ) ) <> \"\" AND p.product_url IS NULL AND COALESCE( p.merchant_url_not_found, 0 ) = 0' ) -> fetchColumn();
|
|
$diag['products_missing_url_not_found'] = (int) $mdb -> query( 'SELECT COUNT(*) FROM products p WHERE p.client_id = ' . $client_id . ' AND TRIM( COALESCE( p.offer_id, \"\" ) ) <> \"\" AND p.product_url IS NULL AND COALESCE( p.merchant_url_not_found, 0 ) = 1' ) -> fetchColumn();
|
|
|
|
return $diag;
|
|
}
|
|
|
|
static private function get_products_missing_url_for_client( $client_id, $limit )
|
|
{
|
|
global $mdb;
|
|
|
|
$client_id = (int) $client_id;
|
|
$limit = max( 1, min( 1000, (int) $limit ) );
|
|
|
|
if ( $client_id <= 0 )
|
|
{
|
|
return [];
|
|
}
|
|
|
|
$retry_after_days = 7;
|
|
$sql = 'SELECT p.id AS product_id, p.offer_id '
|
|
. 'FROM products p '
|
|
. 'WHERE p.client_id = ' . $client_id . ' '
|
|
. 'AND TRIM( COALESCE( p.offer_id, \'\' ) ) <> \'\' '
|
|
. 'AND ( TRIM( COALESCE( p.product_url, \'\' ) ) = \'\' OR LOWER( TRIM( p.product_url ) ) IN ( \'0\', \'-\', \'null\' ) ) '
|
|
. 'AND ( '
|
|
. 'COALESCE( p.merchant_url_not_found, 0 ) = 0 '
|
|
. 'OR ( COALESCE( p.merchant_url_not_found, 0 ) = 1 '
|
|
. 'AND ( p.merchant_url_last_check IS NULL '
|
|
. 'OR p.merchant_url_last_check < DATE_SUB( NOW(), INTERVAL ' . $retry_after_days . ' DAY ) ) ) '
|
|
. ') '
|
|
. 'ORDER BY COALESCE( p.merchant_url_not_found, 0 ) ASC, p.id ASC '
|
|
. 'LIMIT ' . $limit;
|
|
|
|
$rows = $mdb -> query( $sql ) -> fetchAll( \PDO::FETCH_ASSOC );
|
|
return is_array( $rows ) ? $rows : [];
|
|
}
|
|
|
|
static private function sync_products_urls_and_alerts_for_client( $client, $api, $date_sync = null, $limit = null )
|
|
{
|
|
global $mdb, $settings;
|
|
|
|
$client_id = (int) ( $client['id'] ?? 0 );
|
|
$client_name = trim( (string) ( $client['name'] ?? '' ) );
|
|
$merchant_account_id = trim( (string) ( $client['google_merchant_account_id'] ?? '' ) );
|
|
$date_sync = $date_sync ? date( 'Y-m-d', strtotime( $date_sync ) ) : date( 'Y-m-d' );
|
|
$alert_type = 'products_missing_in_merchant_center';
|
|
|
|
if ( $client_id <= 0 )
|
|
{
|
|
return [
|
|
'checked_products' => 0,
|
|
'updated_urls' => 0,
|
|
'missing_in_merchant_count' => 0,
|
|
'missing_offer_ids' => [],
|
|
'missing_product_ids' => [],
|
|
'errors' => [ 'Brak poprawnego klienta do synchronizacji URL.' ]
|
|
];
|
|
}
|
|
|
|
if ( $merchant_account_id === '' )
|
|
{
|
|
return [
|
|
'checked_products' => 0,
|
|
'updated_urls' => 0,
|
|
'missing_in_merchant_count' => 0,
|
|
'missing_offer_ids' => [],
|
|
'missing_product_ids' => [],
|
|
'skipped_reason' => 'missing_merchant_account_id',
|
|
'errors' => []
|
|
];
|
|
}
|
|
|
|
if ( !$api -> is_merchant_configured() )
|
|
{
|
|
return [
|
|
'checked_products' => 0,
|
|
'updated_urls' => 0,
|
|
'missing_in_merchant_count' => 0,
|
|
'missing_offer_ids' => [],
|
|
'missing_product_ids' => [],
|
|
'errors' => [ 'Merchant API nie jest skonfigurowane.' ]
|
|
];
|
|
}
|
|
|
|
$limit = (int) $limit;
|
|
if ( $limit <= 0 )
|
|
{
|
|
$limit = (int) ( $settings['cron_products_urls_limit_per_client'] ?? 200 );
|
|
}
|
|
if ( $limit <= 0 )
|
|
{
|
|
$limit = 200;
|
|
}
|
|
$limit = min( 2000, $limit );
|
|
|
|
$checked_products = 0;
|
|
$updated_urls = 0;
|
|
$missing_offer_ids_from_run = [];
|
|
$missing_product_ids_from_run = [];
|
|
$sync_errors = [];
|
|
|
|
$selected_products = self::get_products_missing_url_for_client( $client_id, $limit );
|
|
$checked_products = count( (array) $selected_products );
|
|
|
|
if ( $checked_products > 0 )
|
|
{
|
|
$offer_ids = [];
|
|
foreach ( $selected_products as $row )
|
|
{
|
|
$offer_id = trim( (string) ( $row['offer_id'] ?? '' ) );
|
|
if ( $offer_id !== '' )
|
|
{
|
|
$offer_ids[] = $offer_id;
|
|
}
|
|
}
|
|
$offer_ids = array_values( array_unique( $offer_ids ) );
|
|
|
|
$links_map = $api -> get_merchant_product_links_for_offer_ids( $merchant_account_id, $offer_ids );
|
|
if ( $links_map === false )
|
|
{
|
|
$last_err = trim( (string) \services\GoogleAdsApi::get_setting( 'google_merchant_last_error' ) );
|
|
if ( $last_err === '' )
|
|
{
|
|
$last_err = 'Nie udalo sie pobrac danych z Merchant Center.';
|
|
}
|
|
$sync_errors[] = $last_err;
|
|
}
|
|
else
|
|
{
|
|
if ( !is_array( $links_map ) )
|
|
{
|
|
$links_map = [];
|
|
}
|
|
|
|
foreach ( $selected_products as $row )
|
|
{
|
|
$offer_id = trim( (string) ( $row['offer_id'] ?? '' ) );
|
|
$product_id = (int) ( $row['product_id'] ?? 0 );
|
|
if ( $offer_id === '' || $product_id <= 0 )
|
|
{
|
|
continue;
|
|
}
|
|
|
|
$product_url = trim( (string) ( $links_map[ $offer_id ] ?? '' ) );
|
|
if ( $product_url !== '' )
|
|
{
|
|
\factory\Products::set_product_data( $product_id, 'product_url', $product_url );
|
|
$updated_urls++;
|
|
continue;
|
|
}
|
|
|
|
$missing_offer_ids_from_run[] = $offer_id;
|
|
$missing_product_ids_from_run[] = $product_id;
|
|
}
|
|
|
|
$missing_product_ids_from_run = array_values( array_unique( array_map( 'intval', $missing_product_ids_from_run ) ) );
|
|
if ( !empty( $missing_product_ids_from_run ) )
|
|
{
|
|
$mdb -> update( 'products', [
|
|
'merchant_url_not_found' => 1,
|
|
'merchant_url_last_check' => date( 'Y-m-d H:i:s' )
|
|
], [ 'id' => $missing_product_ids_from_run ] );
|
|
}
|
|
}
|
|
}
|
|
|
|
$not_found_rows = $mdb -> query(
|
|
"SELECT id AS product_id, offer_id, name, title
|
|
FROM products
|
|
WHERE client_id = :client_id
|
|
AND TRIM( COALESCE( offer_id, '' ) ) <> ''
|
|
AND ( TRIM( COALESCE( product_url, '' ) ) = '' OR LOWER( TRIM( product_url ) ) IN ( '0', '-', 'null' ) )
|
|
AND COALESCE( merchant_url_not_found, 0 ) = 1",
|
|
[ ':client_id' => $client_id ]
|
|
) -> fetchAll( \PDO::FETCH_ASSOC );
|
|
|
|
$missing_offer_ids = [];
|
|
$missing_product_ids = [];
|
|
$missing_products = [];
|
|
foreach ( (array) $not_found_rows as $row )
|
|
{
|
|
$offer_id = trim( (string) ( $row['offer_id'] ?? '' ) );
|
|
$product_id = (int) ( $row['product_id'] ?? 0 );
|
|
$product_name = trim( (string) ( $row['title'] ?? '' ) );
|
|
if ( $product_name === '' )
|
|
{
|
|
$product_name = trim( (string) ( $row['name'] ?? '' ) );
|
|
}
|
|
if ( $product_name === '' )
|
|
{
|
|
$product_name = $offer_id !== '' ? $offer_id : ( 'Produkt #' . $product_id );
|
|
}
|
|
|
|
if ( $offer_id !== '' )
|
|
{
|
|
$missing_offer_ids[] = $offer_id;
|
|
}
|
|
if ( $product_id > 0 )
|
|
{
|
|
$missing_product_ids[] = $product_id;
|
|
$missing_products[] = [
|
|
'product_id' => $product_id,
|
|
'offer_id' => $offer_id,
|
|
'product_name' => $product_name
|
|
];
|
|
}
|
|
}
|
|
|
|
$missing_offer_ids = array_values( array_unique( array_map( 'strval', $missing_offer_ids ) ) );
|
|
$missing_product_ids = array_values( array_unique( array_map( 'intval', $missing_product_ids ) ) );
|
|
$missing_count = count( $missing_offer_ids );
|
|
|
|
// Odświeżamy dzienne alerty per produkt (1 alert = 1 produkt).
|
|
$mdb -> delete( 'campaign_alerts', [
|
|
'AND' => [
|
|
'client_id' => $client_id,
|
|
'alert_type' => $alert_type,
|
|
'date_detected' => $date_sync
|
|
]
|
|
] );
|
|
|
|
if ( $missing_count > 0 )
|
|
{
|
|
foreach ( $missing_products as $product_row )
|
|
{
|
|
$product_id = (int) ( $product_row['product_id'] ?? 0 );
|
|
if ( $product_id <= 0 )
|
|
{
|
|
continue;
|
|
}
|
|
|
|
$offer_id = trim( (string) ( $product_row['offer_id'] ?? '' ) );
|
|
$product_name = trim( (string) ( $product_row['product_name'] ?? '' ) );
|
|
if ( $product_name === '' )
|
|
{
|
|
$product_name = $offer_id !== '' ? $offer_id : ( 'Produkt #' . $product_id );
|
|
}
|
|
|
|
$message = 'Brak produktu w Merchant Center: "' . $product_name . '"';
|
|
if ( $offer_id !== '' )
|
|
{
|
|
$message .= ' (offer_id: ' . $offer_id . ')';
|
|
}
|
|
$message .= '. Klient: "' . ( $client_name !== '' ? $client_name : ( 'ID ' . $client_id ) ) . '".';
|
|
|
|
$meta = [
|
|
'merchant_account_id' => $merchant_account_id,
|
|
'product_id' => $product_id,
|
|
'product_name' => $product_name,
|
|
'offer_id' => $offer_id,
|
|
'checked_products' => $checked_products,
|
|
'updated_urls' => $updated_urls,
|
|
'source' => 'cron_universal_products_urls'
|
|
];
|
|
|
|
$mdb -> insert( 'campaign_alerts', [
|
|
'client_id' => $client_id,
|
|
'campaign_id' => null,
|
|
// Techniczny scope alertu: 1 alert = 1 produkt.
|
|
'campaign_external_id' => $product_id,
|
|
'ad_group_id' => null,
|
|
'ad_group_external_id' => 0,
|
|
'product_id' => $product_id,
|
|
'alert_type' => $alert_type,
|
|
'message' => $message,
|
|
'meta_json' => json_encode( $meta, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES ),
|
|
'date_detected' => $date_sync,
|
|
'date_add' => date( 'Y-m-d H:i:s' )
|
|
] );
|
|
}
|
|
}
|
|
|
|
return [
|
|
'checked_products' => $checked_products,
|
|
'updated_urls' => $updated_urls,
|
|
'missing_in_merchant_count' => $missing_count,
|
|
'missing_offer_ids' => $missing_offer_ids,
|
|
'missing_product_ids' => $missing_product_ids,
|
|
'errors' => $sync_errors
|
|
];
|
|
}
|
|
|
|
static private function sync_products_fetch_for_client( $client, $api, $date )
|
|
{
|
|
global $mdb;
|
|
|
|
$client_id = (int) $client['id'];
|
|
$customer_id = trim( (string) ( $client['google_ads_customer_id'] ?? '' ) );
|
|
$date = date( 'Y-m-d', strtotime( $date ) );
|
|
|
|
$known_campaign_types = $mdb -> query(
|
|
'SELECT DISTINCT UPPER( TRIM( advertising_channel_type ) ) AS channel_type
|
|
FROM campaigns
|
|
WHERE client_id = :client_id
|
|
AND campaign_id > 0
|
|
AND advertising_channel_type IS NOT NULL
|
|
AND TRIM( advertising_channel_type ) <> ""',
|
|
[ ':client_id' => $client_id ]
|
|
) -> fetchAll( \PDO::FETCH_COLUMN );
|
|
|
|
$known_campaign_types = array_values( array_unique( array_filter( array_map( function( $item )
|
|
{
|
|
return strtoupper( trim( (string) $item ) );
|
|
}, (array) $known_campaign_types ) ) ) );
|
|
|
|
$product_campaign_types = [ 'SHOPPING', 'PERFORMANCE_MAX' ];
|
|
$has_product_campaign_type = count( array_intersect( $known_campaign_types, $product_campaign_types ) ) > 0;
|
|
|
|
if ( !empty( $known_campaign_types ) && !$has_product_campaign_type )
|
|
{
|
|
return [
|
|
'date' => $date,
|
|
'processed_products' => 0,
|
|
'skipped' => 0,
|
|
'history_30_products' => 0,
|
|
'products_temp_rows' => 0,
|
|
'touched_products' => 0,
|
|
'fetch_skipped_reason' => 'non_product_campaign_types',
|
|
'errors' => []
|
|
];
|
|
}
|
|
|
|
$products = $api -> get_products_for_date( $customer_id, $date );
|
|
if ( $products === false )
|
|
{
|
|
$last_err = \services\GoogleAdsApi::get_setting( 'google_ads_last_error' );
|
|
return [
|
|
'date' => $date,
|
|
'processed_products' => 0,
|
|
'skipped' => 0,
|
|
'history_30_products' => 0,
|
|
'products_temp_rows' => 0,
|
|
'errors' => [ 'Blad API dla klienta ' . $client['name'] . ' (ID: ' . $customer_id . '): ' . $last_err ]
|
|
];
|
|
}
|
|
|
|
if ( !is_array( $products ) )
|
|
{
|
|
$products = [];
|
|
}
|
|
|
|
$existing_products_rows = $mdb -> query(
|
|
'SELECT id, offer_id, name, title, product_url
|
|
FROM products
|
|
WHERE client_id = :client_id
|
|
ORDER BY id ASC',
|
|
[ ':client_id' => $client_id ]
|
|
) -> fetchAll( \PDO::FETCH_ASSOC );
|
|
|
|
$products_by_offer_id = [];
|
|
foreach ( $existing_products_rows as $row )
|
|
{
|
|
$offer_id = trim( (string) ( $row['offer_id'] ?? '' ) );
|
|
if ( $offer_id === '' || isset( $products_by_offer_id[ $offer_id ] ) )
|
|
{
|
|
continue;
|
|
}
|
|
|
|
$products_by_offer_id[ $offer_id ] = [
|
|
'id' => (int) ( $row['id'] ?? 0 ),
|
|
'name' => (string) ( $row['name'] ?? '' ),
|
|
'title' => (string) ( $row['title'] ?? '' ),
|
|
'product_url' => (string) ( $row['product_url'] ?? '' )
|
|
];
|
|
}
|
|
|
|
$existing_campaigns_rows = $mdb -> query(
|
|
'SELECT id, campaign_id, campaign_name
|
|
FROM campaigns
|
|
WHERE client_id = :client_id
|
|
AND campaign_id > 0',
|
|
[ ':client_id' => $client_id ]
|
|
) -> fetchAll( \PDO::FETCH_ASSOC );
|
|
|
|
$campaigns_by_external_id = [];
|
|
$campaigns_by_db_id = [];
|
|
foreach ( $existing_campaigns_rows as $row )
|
|
{
|
|
$db_campaign_id = (int) ( $row['id'] ?? 0 );
|
|
$external_campaign_id = (int) ( $row['campaign_id'] ?? 0 );
|
|
|
|
if ( $db_campaign_id <= 0 )
|
|
{
|
|
continue;
|
|
}
|
|
|
|
$campaign_data = [
|
|
'id' => $db_campaign_id,
|
|
'campaign_name' => (string) ( $row['campaign_name'] ?? '' )
|
|
];
|
|
|
|
if ( !isset( $campaigns_by_external_id[ $external_campaign_id ] ) )
|
|
{
|
|
$campaigns_by_external_id[ $external_campaign_id ] = $campaign_data;
|
|
}
|
|
|
|
$campaigns_by_db_id[ $db_campaign_id ] = $campaign_data;
|
|
}
|
|
|
|
$existing_ad_groups_rows = $mdb -> query(
|
|
'SELECT ag.id, ag.campaign_id, ag.ad_group_id, ag.ad_group_name
|
|
FROM campaign_ad_groups AS ag
|
|
INNER JOIN campaigns AS c ON c.id = ag.campaign_id
|
|
WHERE c.client_id = :client_id',
|
|
[ ':client_id' => $client_id ]
|
|
) -> fetchAll( \PDO::FETCH_ASSOC );
|
|
|
|
$ad_groups_by_scope = [];
|
|
foreach ( $existing_ad_groups_rows as $row )
|
|
{
|
|
$db_campaign_id = (int) ( $row['campaign_id'] ?? 0 );
|
|
$external_ad_group_id = (int) ( $row['ad_group_id'] ?? 0 );
|
|
$db_ad_group_id = (int) ( $row['id'] ?? 0 );
|
|
|
|
if ( $db_campaign_id <= 0 || $db_ad_group_id <= 0 )
|
|
{
|
|
continue;
|
|
}
|
|
|
|
$scope_key = $db_campaign_id . '|' . $external_ad_group_id;
|
|
if ( isset( $ad_groups_by_scope[ $scope_key ] ) )
|
|
{
|
|
continue;
|
|
}
|
|
|
|
$ad_groups_by_scope[ $scope_key ] = [
|
|
'id' => $db_ad_group_id,
|
|
'ad_group_name' => (string) ( $row['ad_group_name'] ?? '' )
|
|
];
|
|
}
|
|
|
|
$existing_history_rows = $mdb -> query(
|
|
'SELECT ph.product_id, ph.campaign_id, ph.ad_group_id, ph.impressions, ph.clicks, ph.cost, ph.conversions, ph.conversions_value
|
|
FROM products_history AS ph
|
|
INNER JOIN products AS p ON p.id = ph.product_id
|
|
WHERE p.client_id = :client_id
|
|
AND ph.date_add = :date_add',
|
|
[
|
|
':client_id' => $client_id,
|
|
':date_add' => $date
|
|
]
|
|
) -> fetchAll( \PDO::FETCH_ASSOC );
|
|
|
|
$history_by_scope = [];
|
|
foreach ( $existing_history_rows as $row )
|
|
{
|
|
$history_key = (int) ( $row['product_id'] ?? 0 ) . '|' . (int) ( $row['campaign_id'] ?? 0 ) . '|' . (int) ( $row['ad_group_id'] ?? 0 );
|
|
$history_by_scope[ $history_key ] = [
|
|
'impressions' => (int) ( $row['impressions'] ?? 0 ),
|
|
'clicks' => (int) ( $row['clicks'] ?? 0 ),
|
|
'cost' => (float) ( $row['cost'] ?? 0 ),
|
|
'conversions' => (float) ( $row['conversions'] ?? 0 ),
|
|
'conversions_value' => (float) ( $row['conversions_value'] ?? 0 )
|
|
];
|
|
}
|
|
|
|
$resolve_scope_ids = function( $campaign_external_id, $campaign_name, $ad_group_external_id, $ad_group_name ) use ( &$campaigns_by_external_id, &$campaigns_by_db_id, &$ad_groups_by_scope, $client_id, $date, $mdb )
|
|
{
|
|
$campaign_external_id = (int) $campaign_external_id;
|
|
$campaign_name = trim( (string) $campaign_name );
|
|
$ad_group_external_id = (int) $ad_group_external_id;
|
|
$ad_group_name = trim( (string) $ad_group_name );
|
|
|
|
if ( $campaign_external_id <= 0 )
|
|
{
|
|
return [ 'campaign_id' => 0, 'ad_group_id' => 0 ];
|
|
}
|
|
|
|
$campaign_data = $campaigns_by_external_id[ $campaign_external_id ] ?? null;
|
|
if ( !$campaign_data )
|
|
{
|
|
$campaign_name_to_save = $campaign_name;
|
|
if ( $campaign_name_to_save === '' )
|
|
{
|
|
$campaign_name_to_save = $campaign_external_id > 0 ? 'Kampania #' . $campaign_external_id : '--- konto ---';
|
|
}
|
|
|
|
$mdb -> insert( 'campaigns', [
|
|
'client_id' => $client_id,
|
|
'campaign_id' => $campaign_external_id,
|
|
'campaign_name' => $campaign_name_to_save
|
|
] );
|
|
|
|
$db_campaign_id = (int) $mdb -> id();
|
|
|
|
$campaign_data = [
|
|
'id' => $db_campaign_id,
|
|
'campaign_name' => $campaign_name_to_save
|
|
];
|
|
|
|
$campaigns_by_external_id[ $campaign_external_id ] = $campaign_data;
|
|
$campaigns_by_db_id[ $db_campaign_id ] = $campaign_data;
|
|
}
|
|
else if ( $campaign_name !== '' && $campaign_name !== (string) ( $campaign_data['campaign_name'] ?? '' ) )
|
|
{
|
|
$mdb -> update( 'campaigns', [ 'campaign_name' => $campaign_name ], [ 'id' => (int) $campaign_data['id'] ] );
|
|
$campaign_data['campaign_name'] = $campaign_name;
|
|
$campaigns_by_external_id[ $campaign_external_id ] = $campaign_data;
|
|
$campaigns_by_db_id[ (int) $campaign_data['id'] ] = $campaign_data;
|
|
}
|
|
|
|
$db_campaign_id = (int) ( $campaign_data['id'] ?? 0 );
|
|
|
|
if ( $db_campaign_id <= 0 )
|
|
{
|
|
return [ 'campaign_id' => 0, 'ad_group_id' => 0 ];
|
|
}
|
|
|
|
if ( $ad_group_external_id <= 0 )
|
|
{
|
|
$scope_key = $db_campaign_id . '|0';
|
|
if ( !isset( $ad_groups_by_scope[ $scope_key ] ) )
|
|
{
|
|
$db_ad_group_id = (int) self::ensure_campaign_level_ad_group( $db_campaign_id, $date );
|
|
$ad_groups_by_scope[ $scope_key ] = [
|
|
'id' => $db_ad_group_id,
|
|
'ad_group_name' => '--- kampania (brak grupy reklam) ---'
|
|
];
|
|
}
|
|
|
|
return [
|
|
'campaign_id' => $db_campaign_id,
|
|
'ad_group_id' => (int) ( $ad_groups_by_scope[ $scope_key ]['id'] ?? 0 )
|
|
];
|
|
}
|
|
|
|
$scope_key = $db_campaign_id . '|' . $ad_group_external_id;
|
|
$ad_group_data = $ad_groups_by_scope[ $scope_key ] ?? null;
|
|
|
|
if ( !$ad_group_data )
|
|
{
|
|
$ad_group_name_to_save = $ad_group_name !== '' ? $ad_group_name : 'Ad group #' . $ad_group_external_id;
|
|
|
|
$mdb -> insert( 'campaign_ad_groups', [
|
|
'campaign_id' => $db_campaign_id,
|
|
'ad_group_id' => $ad_group_external_id,
|
|
'ad_group_name' => $ad_group_name_to_save,
|
|
'status' => 'paused',
|
|
'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
|
|
] );
|
|
|
|
$ad_group_data = [
|
|
'id' => (int) $mdb -> id(),
|
|
'ad_group_name' => $ad_group_name_to_save
|
|
];
|
|
|
|
$ad_groups_by_scope[ $scope_key ] = $ad_group_data;
|
|
}
|
|
else if ( $ad_group_name !== '' && $ad_group_name !== (string) ( $ad_group_data['ad_group_name'] ?? '' ) )
|
|
{
|
|
$mdb -> update( 'campaign_ad_groups', [ 'ad_group_name' => $ad_group_name ], [ 'id' => (int) $ad_group_data['id'] ] );
|
|
$ad_group_data['ad_group_name'] = $ad_group_name;
|
|
$ad_groups_by_scope[ $scope_key ] = $ad_group_data;
|
|
}
|
|
|
|
return [
|
|
'campaign_id' => $db_campaign_id,
|
|
'ad_group_id' => (int) ( $ad_group_data['id'] ?? 0 )
|
|
];
|
|
};
|
|
|
|
$processed = 0;
|
|
$skipped = 0;
|
|
$touched_product_ids = [];
|
|
|
|
foreach ( $products as $offer )
|
|
{
|
|
$offer_external_id = trim( (string) ( $offer['OfferId'] ?? '' ) );
|
|
if ( $offer_external_id === '' )
|
|
{
|
|
$skipped++;
|
|
continue;
|
|
}
|
|
|
|
$product_title = trim( (string) ( $offer['ProductTitle'] ?? '' ) );
|
|
if ( $product_title === '' )
|
|
{
|
|
$product_title = $offer_external_id;
|
|
}
|
|
|
|
$existing_product = $products_by_offer_id[ $offer_external_id ] ?? null;
|
|
|
|
if ( !$existing_product )
|
|
{
|
|
$mdb -> insert( 'products', [
|
|
'client_id' => $client_id,
|
|
'offer_id' => $offer_external_id,
|
|
'name' => $product_title
|
|
] );
|
|
|
|
$product_id = $mdb -> id();
|
|
|
|
$products_by_offer_id[ $offer_external_id ] = [
|
|
'id' => (int) $product_id,
|
|
'name' => $product_title,
|
|
'product_url' => ''
|
|
];
|
|
}
|
|
else
|
|
{
|
|
$product_id = (int) ( $existing_product['id'] ?? 0 );
|
|
}
|
|
|
|
if ( !$product_id )
|
|
{
|
|
$skipped++;
|
|
continue;
|
|
}
|
|
|
|
$product_url = trim( (string) ( $offer['ProductUrl'] ?? '' ) );
|
|
$product_url_path = strtolower( (string) parse_url( $product_url, PHP_URL_PATH ) );
|
|
$is_image_url = (bool) preg_match( '/\.(jpg|jpeg|png|gif|webp|bmp|svg|avif)$/i', $product_url_path );
|
|
|
|
if ( $product_url !== '' && filter_var( $product_url, FILTER_VALIDATE_URL ) && !$is_image_url )
|
|
{
|
|
$existing_product_url = trim( (string) ( $products_by_offer_id[ $offer_external_id ]['product_url'] ?? '' ) );
|
|
|
|
if ( $existing_product_url !== $product_url )
|
|
{
|
|
$mdb -> update( 'products', [
|
|
'product_url' => $product_url,
|
|
'merchant_url_not_found' => 0
|
|
], [ 'id' => $product_id ] );
|
|
$products_by_offer_id[ $offer_external_id ]['product_url'] = $product_url;
|
|
}
|
|
}
|
|
|
|
$campaign_external_id = (int) ( $offer['CampaignId'] ?? 0 );
|
|
if ( $campaign_external_id <= 0 )
|
|
{
|
|
$skipped++;
|
|
continue;
|
|
}
|
|
|
|
$campaign_name = trim( (string) ( $offer['CampaignName'] ?? '' ) );
|
|
$ad_group_external_id = (int) ( $offer['AdGroupId'] ?? 0 );
|
|
$ad_group_name = trim( (string) ( $offer['AdGroupName'] ?? '' ) );
|
|
|
|
$scope = $resolve_scope_ids( $campaign_external_id, $campaign_name, $ad_group_external_id, $ad_group_name );
|
|
|
|
$db_campaign_id = (int) ( $scope['campaign_id'] ?? 0 );
|
|
$db_ad_group_id = (int) ( $scope['ad_group_id'] ?? 0 );
|
|
|
|
$impressions = (int) round( (float) ( $offer['Impressions'] ?? 0 ) );
|
|
$clicks = (int) round( (float) ( $offer['Clicks'] ?? 0 ) );
|
|
$cost = (float) ( $offer['Cost'] ?? 0 );
|
|
$conversions = (float) ( $offer['Conversions'] ?? 0 );
|
|
$conversion_value = (float) ( $offer['ConversionValue'] ?? 0 );
|
|
$ctr = ( $impressions > 0 ) ? round( $clicks / $impressions, 4 ) * 100 : 0;
|
|
|
|
$offer_data = [
|
|
'impressions' => $impressions,
|
|
'clicks' => $clicks,
|
|
'ctr' => $ctr,
|
|
'cost' => $cost,
|
|
'conversions' => $conversions,
|
|
'conversions_value' => $conversion_value,
|
|
'updated' => 1,
|
|
'campaign_id' => $db_campaign_id,
|
|
'ad_group_id' => $db_ad_group_id
|
|
];
|
|
|
|
$history_scope_key = (int) $product_id . '|' . (int) $db_campaign_id . '|' . (int) $db_ad_group_id;
|
|
$offer_data_old = $history_by_scope[ $history_scope_key ] ?? null;
|
|
|
|
if ( $offer_data_old )
|
|
{
|
|
if (
|
|
$offer_data_old['impressions'] == $offer_data['impressions']
|
|
and $offer_data_old['clicks'] == $offer_data['clicks']
|
|
and number_format( (float) str_replace( ',', '.', $offer_data_old['cost'] ), 5 ) == number_format( (float) $offer_data['cost'], 5 )
|
|
and (float) $offer_data_old['conversions'] == (float) $offer_data['conversions']
|
|
and number_format( (float) str_replace( ',', '.', $offer_data_old['conversions_value'] ), 5 ) == number_format( (float) $offer_data['conversions_value'], 5 )
|
|
)
|
|
{
|
|
$touched_product_ids[ $product_id ] = true;
|
|
$processed++;
|
|
continue;
|
|
}
|
|
|
|
$mdb -> update( 'products_history', $offer_data, [
|
|
'AND' => [
|
|
'product_id' => $product_id,
|
|
'campaign_id' => $db_campaign_id,
|
|
'ad_group_id' => $db_ad_group_id,
|
|
'date_add' => $date
|
|
]
|
|
] );
|
|
|
|
$history_by_scope[ $history_scope_key ] = [
|
|
'impressions' => $offer_data['impressions'],
|
|
'clicks' => $offer_data['clicks'],
|
|
'cost' => $offer_data['cost'],
|
|
'conversions' => $offer_data['conversions'],
|
|
'conversions_value' => $offer_data['conversions_value']
|
|
];
|
|
}
|
|
else
|
|
{
|
|
$offer_data['product_id'] = $product_id;
|
|
$offer_data['date_add'] = $date;
|
|
$mdb -> insert( 'products_history', $offer_data );
|
|
|
|
$history_by_scope[ $history_scope_key ] = [
|
|
'impressions' => $offer_data['impressions'],
|
|
'clicks' => $offer_data['clicks'],
|
|
'cost' => $offer_data['cost'],
|
|
'conversions' => $offer_data['conversions'],
|
|
'conversions_value' => $offer_data['conversions_value']
|
|
];
|
|
}
|
|
|
|
$touched_product_ids[ $product_id ] = true;
|
|
$processed++;
|
|
}
|
|
|
|
return [
|
|
'date' => $date,
|
|
'processed_products' => $processed,
|
|
'skipped' => $skipped,
|
|
'touched_products' => count( $touched_product_ids ),
|
|
'errors' => []
|
|
];
|
|
}
|
|
|
|
static public function resolve_products_scope_ids( $client_id, $campaign_external_id, $campaign_name, $ad_group_external_id, $ad_group_name, $date_sync )
|
|
{
|
|
$client_id = (int) $client_id;
|
|
$campaign_external_id = (int) $campaign_external_id;
|
|
$ad_group_external_id = (int) $ad_group_external_id;
|
|
|
|
$db_campaign_id = self::ensure_products_campaign(
|
|
$client_id,
|
|
$campaign_external_id,
|
|
$campaign_name,
|
|
$date_sync
|
|
);
|
|
|
|
if ( $db_campaign_id <= 0 )
|
|
{
|
|
$db_campaign_id = self::ensure_products_campaign(
|
|
$client_id,
|
|
0,
|
|
'--- konto ---',
|
|
$date_sync
|
|
);
|
|
}
|
|
|
|
$db_ad_group_id = self::ensure_products_ad_group(
|
|
$db_campaign_id,
|
|
$ad_group_external_id,
|
|
$ad_group_name,
|
|
$date_sync
|
|
);
|
|
|
|
return [
|
|
'campaign_id' => (int) $db_campaign_id,
|
|
'ad_group_id' => (int) $db_ad_group_id
|
|
];
|
|
}
|
|
|
|
static private function ensure_products_campaign( $client_id, $campaign_external_id, $campaign_name, $date_sync )
|
|
{
|
|
global $mdb;
|
|
|
|
$client_id = (int) $client_id;
|
|
$campaign_external_id = (int) $campaign_external_id;
|
|
$campaign_name = trim( (string) $campaign_name );
|
|
|
|
if ( $client_id <= 0 )
|
|
{
|
|
return 0;
|
|
}
|
|
|
|
$db_campaign_id = (int) $mdb -> get( 'campaigns', 'id', [ 'AND' => [
|
|
'client_id' => $client_id,
|
|
'campaign_id' => $campaign_external_id
|
|
] ] );
|
|
|
|
if ( $db_campaign_id > 0 )
|
|
{
|
|
if ( $campaign_name !== '' )
|
|
{
|
|
$mdb -> update( 'campaigns', [ 'campaign_name' => $campaign_name ], [ 'id' => $db_campaign_id ] );
|
|
}
|
|
|
|
return $db_campaign_id;
|
|
}
|
|
|
|
if ( $campaign_name === '' )
|
|
{
|
|
$campaign_name = $campaign_external_id > 0 ? 'Kampania #' . $campaign_external_id : '--- konto ---';
|
|
}
|
|
|
|
$mdb -> insert( 'campaigns', [
|
|
'client_id' => $client_id,
|
|
'campaign_id' => $campaign_external_id,
|
|
'campaign_name' => $campaign_name
|
|
] );
|
|
|
|
$db_campaign_id = (int) $mdb -> id();
|
|
|
|
return $db_campaign_id;
|
|
}
|
|
|
|
static private function ensure_products_ad_group( $db_campaign_id, $ad_group_external_id, $ad_group_name, $date_sync )
|
|
{
|
|
global $mdb;
|
|
|
|
$db_campaign_id = (int) $db_campaign_id;
|
|
$ad_group_external_id = (int) $ad_group_external_id;
|
|
$ad_group_name = trim( (string) $ad_group_name );
|
|
|
|
if ( $db_campaign_id <= 0 )
|
|
{
|
|
return 0;
|
|
}
|
|
|
|
if ( $ad_group_external_id <= 0 )
|
|
{
|
|
return (int) self::ensure_campaign_level_ad_group( $db_campaign_id, $date_sync );
|
|
}
|
|
|
|
$db_ad_group_id = (int) $mdb -> get( 'campaign_ad_groups', 'id', [ 'AND' => [
|
|
'campaign_id' => $db_campaign_id,
|
|
'ad_group_id' => $ad_group_external_id
|
|
] ] );
|
|
|
|
if ( $db_ad_group_id > 0 )
|
|
{
|
|
if ( $ad_group_name !== '' )
|
|
{
|
|
$mdb -> update( 'campaign_ad_groups', [ 'ad_group_name' => $ad_group_name ], [ 'id' => $db_ad_group_id ] );
|
|
}
|
|
|
|
return $db_ad_group_id;
|
|
}
|
|
|
|
if ( $ad_group_name === '' )
|
|
{
|
|
$ad_group_name = 'Ad group #' . $ad_group_external_id;
|
|
}
|
|
|
|
$mdb -> insert( 'campaign_ad_groups', [
|
|
'campaign_id' => $db_campaign_id,
|
|
'ad_group_id' => $ad_group_external_id,
|
|
'ad_group_name' => $ad_group_name,
|
|
'status' => 'paused',
|
|
'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_sync
|
|
] );
|
|
|
|
return (int) $mdb -> id();
|
|
}
|
|
|
|
static private function aggregate_products_history_30_for_client( $client_id, $date = null )
|
|
{
|
|
global $mdb;
|
|
|
|
$client_id = (int) $client_id;
|
|
if ( $client_id <= 0 )
|
|
{
|
|
return 0;
|
|
}
|
|
|
|
$target_date = $date ? date( 'Y-m-d', strtotime( $date ) ) : date( 'Y-m-d', strtotime( '-1 days' ) );
|
|
$params = [ ':client_id' => $client_id ];
|
|
$sql = 'SELECT DISTINCT ph.product_id, ph.campaign_id, ph.ad_group_id, ph.date_add
|
|
FROM products_history AS ph
|
|
INNER JOIN products AS p ON p.id = ph.product_id
|
|
WHERE p.client_id = :client_id
|
|
AND ph.campaign_id > 0
|
|
AND ph.updated = 1';
|
|
|
|
if ( $date )
|
|
{
|
|
$params[':date_add'] = $target_date;
|
|
$sql .= ' AND ph.date_add = :date_add';
|
|
}
|
|
|
|
$sql .= ' ORDER BY ph.date_add ASC, ph.product_id ASC, ph.campaign_id ASC, ph.ad_group_id ASC';
|
|
|
|
$rows = $mdb -> query( $sql, $params ) -> fetchAll( \PDO::FETCH_ASSOC );
|
|
|
|
$processed = 0;
|
|
foreach ( $rows as $row )
|
|
{
|
|
$product_id = (int) $row['product_id'];
|
|
$campaign_id = (int) ( $row['campaign_id'] ?? 0 );
|
|
$ad_group_id = (int) ( $row['ad_group_id'] ?? 0 );
|
|
|
|
self::cron_product_history_30_save( $product_id, $row['date_add'], $campaign_id, $ad_group_id );
|
|
$mdb -> query(
|
|
'UPDATE products_history AS ph
|
|
INNER JOIN products AS p ON p.id = ph.product_id
|
|
SET ph.updated = 0
|
|
WHERE ph.product_id = :product_id
|
|
AND ph.campaign_id = :campaign_id
|
|
AND ph.ad_group_id = :ad_group_id
|
|
AND ph.date_add = :date_add
|
|
AND p.client_id = :client_id',
|
|
[
|
|
':product_id' => $product_id,
|
|
':campaign_id' => $campaign_id,
|
|
':ad_group_id' => $ad_group_id,
|
|
':date_add' => $row['date_add'],
|
|
':client_id' => $client_id
|
|
]
|
|
);
|
|
$processed++;
|
|
}
|
|
|
|
self::rebuild_products_aggregate_for_client( $client_id, $target_date );
|
|
|
|
return $processed;
|
|
}
|
|
|
|
static private function rebuild_products_aggregate_for_client( $client_id, $date_sync = null )
|
|
{
|
|
global $mdb;
|
|
|
|
$client_id = (int) $client_id;
|
|
if ( $client_id <= 0 )
|
|
{
|
|
return 0;
|
|
}
|
|
|
|
$date_sync = $date_sync ? date( 'Y-m-d', strtotime( $date_sync ) ) : date( 'Y-m-d', strtotime( '-1 days' ) );
|
|
$date_from_30 = date( 'Y-m-d', strtotime( '-29 days', strtotime( $date_sync ) ) );
|
|
|
|
$product_ids = $mdb -> select( 'products', 'id', [ 'client_id' => $client_id ] );
|
|
$product_ids = array_values( array_unique( array_map( 'intval', (array) $product_ids ) ) );
|
|
if ( empty( $product_ids ) )
|
|
{
|
|
return 0;
|
|
}
|
|
|
|
$mdb -> delete( 'products_aggregate', [ 'product_id' => $product_ids ] );
|
|
|
|
$rows = $mdb -> query(
|
|
'SELECT
|
|
ph.product_id,
|
|
ph.campaign_id,
|
|
ph.ad_group_id,
|
|
SUM( CASE WHEN ph.date_add BETWEEN :date_from_30 AND :date_sync THEN ph.impressions ELSE 0 END ) AS impressions_30,
|
|
SUM( CASE WHEN ph.date_add BETWEEN :date_from_30 AND :date_sync THEN ph.clicks ELSE 0 END ) AS clicks_30,
|
|
SUM( CASE WHEN ph.date_add BETWEEN :date_from_30 AND :date_sync THEN ph.cost ELSE 0 END ) AS cost_30,
|
|
SUM( CASE WHEN ph.date_add BETWEEN :date_from_30 AND :date_sync THEN ph.conversions ELSE 0 END ) AS conversions_30,
|
|
SUM( CASE WHEN ph.date_add BETWEEN :date_from_30 AND :date_sync THEN ph.conversions_value ELSE 0 END ) AS conversion_value_30,
|
|
SUM( CASE WHEN ph.date_add <= :date_sync THEN ph.impressions ELSE 0 END ) AS impressions_all_time,
|
|
SUM( CASE WHEN ph.date_add <= :date_sync THEN ph.clicks ELSE 0 END ) AS clicks_all_time,
|
|
SUM( CASE WHEN ph.date_add <= :date_sync THEN ph.cost ELSE 0 END ) AS cost_all_time,
|
|
SUM( CASE WHEN ph.date_add <= :date_sync THEN ph.conversions ELSE 0 END ) AS conversions_all_time,
|
|
SUM( CASE WHEN ph.date_add <= :date_sync THEN ph.conversions_value ELSE 0 END ) AS conversion_value_all_time
|
|
FROM products_history AS ph
|
|
INNER JOIN products AS p ON p.id = ph.product_id
|
|
WHERE p.client_id = :client_id
|
|
AND ph.campaign_id > 0
|
|
AND ph.date_add <= :date_sync
|
|
GROUP BY ph.product_id, ph.campaign_id, ph.ad_group_id
|
|
HAVING
|
|
impressions_30 > 0 OR clicks_30 > 0 OR cost_30 > 0 OR conversions_30 > 0 OR conversion_value_30 > 0
|
|
OR impressions_all_time > 0 OR clicks_all_time > 0 OR cost_all_time > 0 OR conversions_all_time > 0 OR conversion_value_all_time > 0',
|
|
[
|
|
':client_id' => $client_id,
|
|
':date_from_30' => $date_from_30,
|
|
':date_sync' => $date_sync
|
|
]
|
|
) -> fetchAll( \PDO::FETCH_ASSOC );
|
|
|
|
$processed = 0;
|
|
foreach ( (array) $rows as $row )
|
|
{
|
|
$impressions_30 = (int) ( $row['impressions_30'] ?? 0 );
|
|
$clicks_30 = (int) ( $row['clicks_30'] ?? 0 );
|
|
$cost_30 = (float) ( $row['cost_30'] ?? 0 );
|
|
$conversions_30 = (float) ( $row['conversions_30'] ?? 0 );
|
|
$conversion_value_30 = (float) ( $row['conversion_value_30'] ?? 0 );
|
|
$ctr_30 = $impressions_30 > 0 ? round( $clicks_30 / $impressions_30 * 100, 6 ) : 0;
|
|
$roas_30 = $cost_30 > 0 ? round( $conversion_value_30 / $cost_30 * 100, 6 ) : 0;
|
|
|
|
$impressions_all = (int) ( $row['impressions_all_time'] ?? 0 );
|
|
$clicks_all = (int) ( $row['clicks_all_time'] ?? 0 );
|
|
$cost_all = (float) ( $row['cost_all_time'] ?? 0 );
|
|
$conversions_all = (float) ( $row['conversions_all_time'] ?? 0 );
|
|
$conversion_value_all = (float) ( $row['conversion_value_all_time'] ?? 0 );
|
|
$ctr_all = $impressions_all > 0 ? round( $clicks_all / $impressions_all * 100, 6 ) : 0;
|
|
$roas_all = $cost_all > 0 ? round( $conversion_value_all / $cost_all * 100, 6 ) : 0;
|
|
|
|
$mdb -> insert( 'products_aggregate', [
|
|
'product_id' => (int) ( $row['product_id'] ?? 0 ),
|
|
'campaign_id' => (int) ( $row['campaign_id'] ?? 0 ),
|
|
'ad_group_id' => (int) ( $row['ad_group_id'] ?? 0 ),
|
|
'impressions_30' => $impressions_30,
|
|
'clicks_30' => $clicks_30,
|
|
'ctr_30' => $ctr_30,
|
|
'cost_30' => $cost_30,
|
|
'conversions_30' => $conversions_30,
|
|
'conversion_value_30' => $conversion_value_30,
|
|
'roas_30' => $roas_30,
|
|
'impressions_all_time' => $impressions_all,
|
|
'clicks_all_time' => $clicks_all,
|
|
'ctr_all_time' => $ctr_all,
|
|
'cost_all_time' => $cost_all,
|
|
'conversions_all_time' => $conversions_all,
|
|
'conversion_value_all_time' => $conversion_value_all,
|
|
'roas_all_time' => $roas_all,
|
|
'date_sync' => $date_sync
|
|
] );
|
|
|
|
$processed++;
|
|
}
|
|
|
|
return $processed;
|
|
}
|
|
|
|
static public function rebuild_products_temp_for_client( $client_id )
|
|
{
|
|
global $mdb;
|
|
|
|
$client_id = (int) $client_id;
|
|
if ( $client_id <= 0 )
|
|
{
|
|
return 0;
|
|
}
|
|
|
|
// products_temp zostalo wycofane.
|
|
// Zwracamy liczbe scope z tabeli products_aggregate (dla diagnostyki odpowiedzi cron).
|
|
return (int) $mdb -> query(
|
|
'SELECT COUNT(*)
|
|
FROM products_aggregate pa
|
|
INNER JOIN products p ON p.id = pa.product_id
|
|
WHERE p.client_id = :client_id',
|
|
[ ':client_id' => $client_id ]
|
|
) -> fetchColumn();
|
|
}
|
|
|
|
static public function get_roas_all_time( $product_id, $date_to, $campaign_id = 0, $ad_group_id = 0 )
|
|
{
|
|
global $mdb;
|
|
|
|
$product_id = (int) $product_id;
|
|
$campaign_id = (int) $campaign_id;
|
|
$ad_group_id = (int) $ad_group_id;
|
|
|
|
$sql = 'SELECT SUM(conversions_value) / SUM(cost) * 100 AS roas_all_time
|
|
FROM products_history
|
|
WHERE product_id = :product_id
|
|
AND date_add <= :date_to
|
|
AND campaign_id = :campaign_id
|
|
AND ad_group_id = :ad_group_id';
|
|
|
|
$roas_all_time = $mdb -> query( $sql, [
|
|
':product_id' => $product_id,
|
|
':date_to' => $date_to,
|
|
':campaign_id' => $campaign_id,
|
|
':ad_group_id' => $ad_group_id
|
|
] ) -> fetchColumn();
|
|
return round( $roas_all_time, 2 );
|
|
}
|
|
|
|
static public function cron_product_history_30_save( $product_id, $date_to, $campaign_id = 0, $ad_group_id = 0 )
|
|
{
|
|
global $mdb;
|
|
|
|
$product_id = (int) $product_id;
|
|
$campaign_id = (int) $campaign_id;
|
|
$ad_group_id = (int) $ad_group_id;
|
|
|
|
if ( $campaign_id <= 0 )
|
|
{
|
|
return;
|
|
}
|
|
|
|
$data = $mdb -> query(
|
|
'SELECT
|
|
date_add,
|
|
SUM( impressions ) AS impressions,
|
|
SUM( clicks ) AS clicks,
|
|
SUM( cost ) AS cost,
|
|
SUM( conversions ) AS conversions,
|
|
SUM( conversions_value ) AS conversions_value
|
|
FROM products_history
|
|
WHERE product_id = :product_id
|
|
AND campaign_id = :campaign_id
|
|
AND ad_group_id = :ad_group_id
|
|
AND date_add <= :date_to
|
|
GROUP BY date_add
|
|
ORDER BY date_add DESC
|
|
LIMIT 30',
|
|
[
|
|
':product_id' => $product_id,
|
|
':campaign_id' => $campaign_id,
|
|
':ad_group_id' => $ad_group_id,
|
|
':date_to' => $date_to
|
|
]
|
|
) -> fetchAll( \PDO::FETCH_ASSOC );
|
|
|
|
$day_count = count( $data );
|
|
if ( $day_count <= 0 )
|
|
{
|
|
return;
|
|
}
|
|
|
|
$days_count_for_product = (int) $mdb -> query(
|
|
'SELECT COUNT( DISTINCT date_add )
|
|
FROM products_history
|
|
WHERE product_id = :product_id
|
|
AND campaign_id = :campaign_id
|
|
AND ad_group_id = :ad_group_id
|
|
AND date_add <= :date_to',
|
|
[
|
|
':product_id' => $product_id,
|
|
':campaign_id' => $campaign_id,
|
|
':ad_group_id' => $ad_group_id,
|
|
':date_to' => $date_to
|
|
]
|
|
) -> fetchColumn();
|
|
|
|
if ( $days_count_for_product < 14 )
|
|
{
|
|
return;
|
|
}
|
|
|
|
$impressions_sum = 0;
|
|
$clicks_sum = 0;
|
|
$cost_sum = 0.0;
|
|
$conversions_sum = 0.0;
|
|
$conversions_value_sum = 0.0;
|
|
|
|
foreach ( $data as $entry )
|
|
{
|
|
$impressions_sum += (int) ( $entry['impressions'] ?? 0 );
|
|
$clicks_sum += (int) ( $entry['clicks'] ?? 0 );
|
|
$cost_sum += (float) ( $entry['cost'] ?? 0 );
|
|
$conversions_sum += (float) ( $entry['conversions'] ?? 0 );
|
|
$conversions_value_sum += (float) ( $entry['conversions_value'] ?? 0 );
|
|
}
|
|
|
|
// products_history_30 przechowuje srednie dzienne dla okna do 30 dni.
|
|
$impressions = (int) round( $impressions_sum / $day_count );
|
|
$clicks = (int) round( $clicks_sum / $day_count );
|
|
$cost = round( $cost_sum / $day_count, 6 );
|
|
$conversions = round( $conversions_sum / $day_count, 6 );
|
|
$conversions_value = round( $conversions_value_sum / $day_count, 6 );
|
|
$ctr = ( $impressions > 0 ) ? round( $clicks / $impressions, 6 ) * 100 : 0;
|
|
$roas = ( $cost > 0 ) ? round( $conversions_value / $cost, 6 ) * 100 : 0;
|
|
|
|
if ( $mdb -> count( 'products_history_30', [ 'AND' => [
|
|
'product_id' => $product_id,
|
|
'campaign_id' => $campaign_id,
|
|
'ad_group_id' => $ad_group_id,
|
|
'date_add' => $date_to
|
|
] ] ) > 0 )
|
|
{
|
|
$mdb -> update( 'products_history_30', [
|
|
'impressions' => $impressions,
|
|
'clicks' => $clicks,
|
|
'ctr' => $ctr,
|
|
'cost' => $cost,
|
|
'conversions' => $conversions,
|
|
'conversions_value' => $conversions_value,
|
|
'roas' => $roas,
|
|
'roas_all_time' => self::get_roas_all_time( $product_id, $date_to, $campaign_id, $ad_group_id )
|
|
], [ 'AND' => [
|
|
'product_id' => $product_id,
|
|
'campaign_id' => $campaign_id,
|
|
'ad_group_id' => $ad_group_id,
|
|
'date_add' => $date_to
|
|
] ] );
|
|
}
|
|
else
|
|
{
|
|
$mdb -> insert( 'products_history_30', [
|
|
'product_id' => $product_id,
|
|
'campaign_id' => $campaign_id,
|
|
'ad_group_id' => $ad_group_id,
|
|
'impressions' => $impressions,
|
|
'clicks' => $clicks,
|
|
'ctr' => $ctr,
|
|
'cost' => $cost,
|
|
'conversions' => $conversions,
|
|
'conversions_value' => $conversions_value,
|
|
'roas' => $roas,
|
|
'roas_all_time' => self::get_roas_all_time( $product_id, $date_to, $campaign_id, $ad_group_id ),
|
|
'date_add' => $date_to
|
|
] );
|
|
}
|
|
}
|
|
|
|
|
|
static public function cron_campaigns_product_alerts_merchant()
|
|
{
|
|
global $mdb, $settings;
|
|
self::$current_cron_action = __FUNCTION__;
|
|
self::touch_cron_invocation( __FUNCTION__ );
|
|
|
|
$api = new \services\GoogleAdsApi();
|
|
if ( !$api -> is_configured() )
|
|
{
|
|
echo json_encode( [ 'result' => 'Google Ads API nie jest skonfigurowane. Uzupelnij dane w Ustawieniach.' ] );
|
|
exit;
|
|
}
|
|
|
|
if ( !$api -> is_merchant_configured() )
|
|
{
|
|
echo json_encode( [ 'result' => 'Merchant API nie jest skonfigurowane. Uzupelnij dane w Ustawieniach.' ] );
|
|
exit;
|
|
}
|
|
|
|
$sync_date = \S::get( 'date' ) ? date( 'Y-m-d', strtotime( \S::get( 'date' ) ) ) : date( 'Y-m-d' );
|
|
$client_id = (int) \S::get( 'client_id' );
|
|
|
|
$where = "deleted = 0
|
|
AND google_ads_customer_id IS NOT NULL AND google_ads_customer_id <> ''
|
|
AND google_merchant_account_id IS NOT NULL AND google_merchant_account_id <> ''";
|
|
|
|
if ( $client_id > 0 )
|
|
{
|
|
$where .= ' AND id = ' . $client_id;
|
|
}
|
|
|
|
$clients = $mdb -> query(
|
|
"SELECT id, name, google_ads_customer_id, google_merchant_account_id
|
|
FROM clients
|
|
WHERE " . $where . "
|
|
ORDER BY id ASC"
|
|
) -> fetchAll( \PDO::FETCH_ASSOC );
|
|
|
|
if ( !is_array( $clients ) || empty( $clients ) )
|
|
{
|
|
echo json_encode( [
|
|
'result' => 'Brak klientow z Google Ads Customer ID i Merchant Account ID.',
|
|
'processed_clients' => 0,
|
|
'alerts_synced' => 0,
|
|
'errors' => []
|
|
] );
|
|
exit;
|
|
}
|
|
|
|
$clients_per_run_default = (int) ( $settings['cron_campaigns_product_alerts_clients_per_run'] ?? ( $settings['cron_campaigns_clients_per_run'] ?? 2 ) );
|
|
if ( $clients_per_run_default <= 0 )
|
|
{
|
|
$clients_per_run_default = 2;
|
|
}
|
|
|
|
$clients_per_run = (int) \S::get( 'clients_per_run' );
|
|
if ( $clients_per_run <= 0 )
|
|
{
|
|
$clients_per_run = (int) self::get_setting_value( 'cron_campaigns_product_alerts_clients_per_run', $clients_per_run_default );
|
|
}
|
|
if ( $clients_per_run <= 0 )
|
|
{
|
|
$clients_per_run = $clients_per_run_default;
|
|
}
|
|
$clients_per_run = min( 20, $clients_per_run );
|
|
|
|
$total_clients_available = count( $clients );
|
|
if ( $client_id <= 0 )
|
|
{
|
|
$last_client_cursor = (int) self::get_setting_value( 'cron_campaigns_product_alerts_last_client_id', 0 );
|
|
$clients = self::pick_clients_batch_by_cursor( $clients, $clients_per_run, $last_client_cursor );
|
|
}
|
|
else
|
|
{
|
|
$clients_per_run = 1;
|
|
}
|
|
|
|
$processed_clients = 0;
|
|
$alerts_synced = 0;
|
|
$errors = [];
|
|
$details = [];
|
|
$last_client_id = 0;
|
|
|
|
foreach ( (array) $clients as $client )
|
|
{
|
|
$processed_clients++;
|
|
$last_client_id = (int) ( $client['id'] ?? 0 );
|
|
|
|
$campaigns_db_map = self::build_campaigns_db_map_for_client( (int) ( $client['id'] ?? 0 ) );
|
|
if ( empty( $campaigns_db_map ) )
|
|
{
|
|
$details[] = [
|
|
'client_id' => (int) ( $client['id'] ?? 0 ),
|
|
'client_name' => (string) ( $client['name'] ?? '' ),
|
|
'alerts_synced' => 0,
|
|
'note' => 'Brak kampanii w bazie dla klienta.'
|
|
];
|
|
continue;
|
|
}
|
|
|
|
$ad_group_db_map = self::build_ad_group_db_map_from_db( $campaigns_db_map );
|
|
|
|
$sync = self::sync_product_campaign_alerts_for_client(
|
|
$client,
|
|
$campaigns_db_map,
|
|
$ad_group_db_map,
|
|
(string) ( $client['google_ads_customer_id'] ?? '' ),
|
|
$api,
|
|
$sync_date,
|
|
[
|
|
'run_missing_mapping_alerts' => false,
|
|
'run_merchant_validation_alerts' => true
|
|
]
|
|
);
|
|
|
|
$alerts_synced += (int) ( $sync['count'] ?? 0 );
|
|
if ( !empty( $sync['errors'] ) )
|
|
{
|
|
$errors = array_merge( $errors, (array) $sync['errors'] );
|
|
}
|
|
|
|
$details[] = [
|
|
'client_id' => (int) ( $client['id'] ?? 0 ),
|
|
'client_name' => (string) ( $client['name'] ?? '' ),
|
|
'alerts_synced' => (int) ( $sync['count'] ?? 0 ),
|
|
'errors_count' => count( (array) ( $sync['errors'] ?? [] ) )
|
|
];
|
|
}
|
|
|
|
if ( $client_id <= 0 && $last_client_id > 0 )
|
|
{
|
|
self::set_setting_value( 'cron_campaigns_product_alerts_last_client_id', (string) $last_client_id );
|
|
}
|
|
|
|
echo json_encode( [
|
|
'result' => empty( $errors ) ? 'Walidacja Merchant dla alertow kampanii zakonczona.' : 'Walidacja Merchant zakonczona z bledami.',
|
|
'date' => $sync_date,
|
|
'processed_clients' => $processed_clients,
|
|
'clients_per_run' => $clients_per_run,
|
|
'total_clients_available' => $total_clients_available,
|
|
'alerts_synced' => $alerts_synced,
|
|
'errors' => $errors,
|
|
'details' => $details
|
|
] );
|
|
exit;
|
|
}
|
|
|
|
static private function build_campaigns_db_map_for_client( $client_id )
|
|
{
|
|
global $mdb;
|
|
|
|
$client_id = (int) $client_id;
|
|
if ( $client_id <= 0 )
|
|
{
|
|
return [];
|
|
}
|
|
|
|
$rows = $mdb -> query(
|
|
"SELECT id, campaign_id
|
|
FROM campaigns
|
|
WHERE client_id = :client_id
|
|
AND campaign_id > 0",
|
|
[ ':client_id' => $client_id ]
|
|
) -> fetchAll( \PDO::FETCH_ASSOC );
|
|
|
|
$map = [];
|
|
foreach ( (array) $rows as $row )
|
|
{
|
|
$campaign_external_id = (string) ( (int) ( $row['campaign_id'] ?? 0 ) );
|
|
$db_campaign_id = (int) ( $row['id'] ?? 0 );
|
|
if ( $campaign_external_id === '0' || $db_campaign_id <= 0 )
|
|
{
|
|
continue;
|
|
}
|
|
$map[ $campaign_external_id ] = $db_campaign_id;
|
|
}
|
|
|
|
return $map;
|
|
}
|
|
|
|
static private function get_sync_phase_for_client_date( $pipeline, $sync_date, $client_id )
|
|
{
|
|
global $mdb;
|
|
|
|
$row = $mdb -> get( 'cron_sync_status', [ 'phase' ], [
|
|
'AND' => [
|
|
'pipeline' => $pipeline,
|
|
'sync_date' => $sync_date,
|
|
'client_id' => (int) $client_id
|
|
]
|
|
] );
|
|
|
|
if ( !is_array( $row ) || !isset( $row['phase'] ) )
|
|
{
|
|
return null;
|
|
}
|
|
|
|
return (string) $row['phase'];
|
|
}
|
|
|
|
static private function get_next_client_id_in_order( $client_ids, $current_client_id )
|
|
{
|
|
$client_ids = array_values( array_filter( array_map( 'intval', (array) $client_ids ) ) );
|
|
$current_client_id = (int) $current_client_id;
|
|
|
|
if ( empty( $client_ids ) )
|
|
{
|
|
return 0;
|
|
}
|
|
|
|
$index = array_search( $current_client_id, $client_ids, true );
|
|
if ( $index === false )
|
|
{
|
|
return (int) $client_ids[0];
|
|
}
|
|
|
|
$next_index = ( $index + 1 ) % count( $client_ids );
|
|
return (int) $client_ids[ $next_index ];
|
|
}
|
|
|
|
static private function sync_campaigns_snapshot_for_client( $client, $api, $as_of_date )
|
|
{
|
|
global $mdb;
|
|
|
|
$as_of_date = $as_of_date ? date( 'Y-m-d', strtotime( $as_of_date ) ) : date( 'Y-m-d' );
|
|
$processed = 0;
|
|
$errors = [];
|
|
$customer_id = trim( (string) ( $client['google_ads_customer_id'] ?? '' ) );
|
|
|
|
if ( $customer_id === '' )
|
|
{
|
|
return [
|
|
'processed_records' => 0,
|
|
'ad_groups_synced' => 0,
|
|
'errors' => [ 'Brak Google Ads Customer ID dla klienta ID ' . (int) ( $client['id'] ?? 0 ) . '.' ]
|
|
];
|
|
}
|
|
|
|
$campaigns_30 = $api -> get_campaigns_30_days( $customer_id, $as_of_date );
|
|
if ( $campaigns_30 === false )
|
|
{
|
|
$last_err = \services\GoogleAdsApi::get_setting( 'google_ads_last_error' );
|
|
return [
|
|
'processed_records' => 0,
|
|
'ad_groups_synced' => 0,
|
|
'errors' => [ 'Blad API kampanii (30 dni) dla klienta ' . (string) ( $client['name'] ?? '' ) . ': ' . $last_err ]
|
|
];
|
|
}
|
|
|
|
$campaigns_all_time = $api -> get_campaigns_all_time( $customer_id, $as_of_date );
|
|
if ( $campaigns_all_time === false )
|
|
{
|
|
$last_err = \services\GoogleAdsApi::get_setting( 'google_ads_last_error' );
|
|
return [
|
|
'processed_records' => 0,
|
|
'ad_groups_synced' => 0,
|
|
'errors' => [ 'Blad API kampanii (all time) dla klienta ' . (string) ( $client['name'] ?? '' ) . ': ' . $last_err ]
|
|
];
|
|
}
|
|
|
|
if ( !is_array( $campaigns_30 ) )
|
|
{
|
|
$campaigns_30 = [];
|
|
}
|
|
if ( !is_array( $campaigns_all_time ) )
|
|
{
|
|
$campaigns_all_time = [];
|
|
}
|
|
|
|
// Brak danych kampanii dla dnia: nie zapisujemy zerowego snapshotu historii.
|
|
if ( empty( $campaigns_30 ) )
|
|
{
|
|
return [
|
|
'processed_records' => 0,
|
|
'ad_groups_synced' => 0,
|
|
'errors' => []
|
|
];
|
|
}
|
|
|
|
$all_time_map = [];
|
|
$all_time_totals = [ 'cost' => 0.0, 'conversion_value' => 0.0 ];
|
|
foreach ( $campaigns_all_time as $cat )
|
|
{
|
|
$cid = (string) ( $cat['campaign_id'] ?? '' );
|
|
if ( $cid === '' )
|
|
{
|
|
continue;
|
|
}
|
|
|
|
$all_time_map[ $cid ] = [
|
|
'roas_all_time' => (float) ( $cat['roas_all_time'] ?? 0 ),
|
|
'cost_all_time' => (float) ( $cat['cost_all_time'] ?? 0 ),
|
|
'conversion_value_all_time' => (float) ( $cat['conversion_value_all_time'] ?? 0 )
|
|
];
|
|
|
|
$all_time_totals['cost'] += (float) ( $cat['cost_all_time'] ?? 0 );
|
|
$all_time_totals['conversion_value'] += (float) ( $cat['conversion_value_all_time'] ?? 0 );
|
|
}
|
|
|
|
$account_30_totals = [ 'budget' => 0.0, 'money_spent' => 0.0, 'conversion_value' => 0.0 ];
|
|
$campaigns_db_map = [];
|
|
|
|
foreach ( $campaigns_30 as $campaign )
|
|
{
|
|
$external_campaign_id = isset( $campaign['campaign_id'] ) ? (string) $campaign['campaign_id'] : '';
|
|
if ( $external_campaign_id === '' )
|
|
{
|
|
continue;
|
|
}
|
|
|
|
$campaign_name = trim( (string) ( $campaign['campaign_name'] ?? '' ) );
|
|
if ( $campaign_name === '' )
|
|
{
|
|
$campaign_name = 'Campaign #' . $external_campaign_id;
|
|
}
|
|
|
|
$advertising_channel_type = strtoupper( trim( (string) ( $campaign['advertising_channel_type'] ?? '' ) ) );
|
|
|
|
$account_30_totals['budget'] += (float) ( $campaign['budget'] ?? 0 );
|
|
$account_30_totals['money_spent'] += (float) ( $campaign['money_spent'] ?? 0 );
|
|
$account_30_totals['conversion_value'] += (float) ( $campaign['conversion_value'] ?? 0 );
|
|
|
|
$db_campaign_id = (int) $mdb -> get( 'campaigns', 'id', [ 'AND' => [
|
|
'client_id' => (int) $client['id'],
|
|
'campaign_id' => $external_campaign_id
|
|
] ] );
|
|
|
|
if ( $db_campaign_id > 0 )
|
|
{
|
|
$mdb -> update( 'campaigns', [
|
|
'campaign_name' => $campaign_name,
|
|
'advertising_channel_type' => $advertising_channel_type !== '' ? $advertising_channel_type : null
|
|
], [ 'id' => $db_campaign_id ] );
|
|
}
|
|
else
|
|
{
|
|
$mdb -> insert( 'campaigns', [
|
|
'client_id' => (int) $client['id'],
|
|
'campaign_id' => $external_campaign_id,
|
|
'campaign_name' => $campaign_name,
|
|
'advertising_channel_type' => $advertising_channel_type !== '' ? $advertising_channel_type : null
|
|
] );
|
|
$db_campaign_id = (int) $mdb -> id();
|
|
}
|
|
|
|
if ( $db_campaign_id <= 0 )
|
|
{
|
|
continue;
|
|
}
|
|
|
|
$all_time_for_campaign = $all_time_map[ $external_campaign_id ] ?? [ 'roas_all_time' => 0 ];
|
|
$bidding_strategy = self::format_bidding_strategy(
|
|
$campaign['bidding_strategy'] ?? '',
|
|
$campaign['target_roas'] ?? 0
|
|
);
|
|
|
|
$history_data = [
|
|
'roas_30_days' => (float) ( $campaign['roas_30_days'] ?? 0 ),
|
|
'roas_all_time' => (float) ( $all_time_for_campaign['roas_all_time'] ?? 0 ),
|
|
'budget' => (float) ( $campaign['budget'] ?? 0 ),
|
|
'money_spent' => (float) ( $campaign['money_spent'] ?? 0 ),
|
|
'conversion_value' => (float) ( $campaign['conversion_value'] ?? 0 ),
|
|
'bidding_strategy' => $bidding_strategy
|
|
];
|
|
|
|
if ( $mdb -> count( 'campaigns_history', [ 'AND' => [
|
|
'campaign_id' => $db_campaign_id,
|
|
'date_add' => $as_of_date
|
|
] ] ) )
|
|
{
|
|
$mdb -> update( 'campaigns_history', $history_data, [ 'AND' => [
|
|
'campaign_id' => $db_campaign_id,
|
|
'date_add' => $as_of_date
|
|
] ] );
|
|
}
|
|
else
|
|
{
|
|
$history_data['campaign_id'] = $db_campaign_id;
|
|
$history_data['date_add'] = $as_of_date;
|
|
$mdb -> insert( 'campaigns_history', $history_data );
|
|
}
|
|
|
|
$campaigns_db_map[ $external_campaign_id ] = $db_campaign_id;
|
|
$processed++;
|
|
}
|
|
|
|
$db_account_campaign_id = (int) $mdb -> get( 'campaigns', 'id', [ 'AND' => [
|
|
'client_id' => (int) $client['id'],
|
|
'campaign_id' => 0
|
|
] ] );
|
|
|
|
if ( $db_account_campaign_id > 0 )
|
|
{
|
|
$mdb -> update( 'campaigns', [
|
|
'campaign_name' => '--- konto ---',
|
|
'advertising_channel_type' => null
|
|
], [ 'id' => $db_account_campaign_id ] );
|
|
}
|
|
else
|
|
{
|
|
$mdb -> insert( 'campaigns', [
|
|
'client_id' => (int) $client['id'],
|
|
'campaign_id' => 0,
|
|
'campaign_name' => '--- konto ---',
|
|
'advertising_channel_type' => null
|
|
] );
|
|
$db_account_campaign_id = (int) $mdb -> id();
|
|
}
|
|
|
|
if ( $db_account_campaign_id > 0 )
|
|
{
|
|
$account_roas_30 = ( $account_30_totals['money_spent'] > 0 )
|
|
? round( ( $account_30_totals['conversion_value'] / $account_30_totals['money_spent'] ) * 100, 2 )
|
|
: 0;
|
|
$account_roas_all_time = ( $all_time_totals['cost'] > 0 )
|
|
? round( ( $all_time_totals['conversion_value'] / $all_time_totals['cost'] ) * 100, 2 )
|
|
: 0;
|
|
|
|
$account_history_data = [
|
|
'roas_30_days' => $account_roas_30,
|
|
'roas_all_time' => $account_roas_all_time,
|
|
'budget' => (float) $account_30_totals['budget'],
|
|
'money_spent' => (float) $account_30_totals['money_spent'],
|
|
'conversion_value' => (float) $account_30_totals['conversion_value'],
|
|
'bidding_strategy' => 'Konto (agregacja wszystkich kampanii)'
|
|
];
|
|
|
|
if ( $mdb -> count( 'campaigns_history', [ 'AND' => [
|
|
'campaign_id' => $db_account_campaign_id,
|
|
'date_add' => $as_of_date
|
|
] ] ) )
|
|
{
|
|
$mdb -> update( 'campaigns_history', $account_history_data, [ 'AND' => [
|
|
'campaign_id' => $db_account_campaign_id,
|
|
'date_add' => $as_of_date
|
|
] ] );
|
|
}
|
|
else
|
|
{
|
|
$account_history_data['campaign_id'] = $db_account_campaign_id;
|
|
$account_history_data['date_add'] = $as_of_date;
|
|
$mdb -> insert( 'campaigns_history', $account_history_data );
|
|
}
|
|
|
|
$processed++;
|
|
}
|
|
|
|
// Grupy reklam / grupy plikow PMAX zapisujemy do campaign_ad_groups.
|
|
$ad_groups_sync = self::sync_campaign_ad_groups_for_client( $campaigns_db_map, $customer_id, $api, $as_of_date, $as_of_date );
|
|
$errors = array_merge( $errors, (array) ( $ad_groups_sync['errors'] ?? [] ) );
|
|
|
|
return [
|
|
'processed_records' => $processed,
|
|
'ad_groups_synced' => (int) ( $ad_groups_sync['count'] ?? 0 ),
|
|
'errors' => $errors
|
|
];
|
|
}
|
|
|
|
static private function sync_campaign_terms_backfill_for_client( $client_id, $customer_id, $api, $sync_dates )
|
|
{
|
|
$client_id = (int) $client_id;
|
|
$customer_id = trim( (string) $customer_id );
|
|
$sync_dates = array_values( array_filter( array_map( function( $date_item )
|
|
{
|
|
$ts = strtotime( (string) $date_item );
|
|
return $ts ? date( 'Y-m-d', $ts ) : null;
|
|
}, (array) $sync_dates ) ) );
|
|
|
|
if ( $client_id <= 0 || $customer_id === '' || empty( $sync_dates ) )
|
|
{
|
|
return [ 'history_synced' => 0, 'aggregated' => 0, 'errors' => [] ];
|
|
}
|
|
|
|
$campaigns_db_map = self::build_campaigns_db_map_for_client( $client_id );
|
|
if ( empty( $campaigns_db_map ) )
|
|
{
|
|
return [ 'history_synced' => 0, 'aggregated' => 0, 'errors' => [] ];
|
|
}
|
|
|
|
$ad_group_db_map = self::build_ad_group_db_map_from_db( $campaigns_db_map );
|
|
|
|
$history_synced = 0;
|
|
$errors = [];
|
|
|
|
foreach ( $sync_dates as $sync_date_item )
|
|
{
|
|
$daily = self::sync_campaign_search_terms_daily( $campaigns_db_map, $ad_group_db_map, $customer_id, $api, $sync_date_item );
|
|
$history_synced += (int) ( $daily['count'] ?? 0 );
|
|
if ( !empty( $daily['errors'] ) )
|
|
{
|
|
$errors = array_merge( $errors, (array) $daily['errors'] );
|
|
}
|
|
}
|
|
|
|
$aggregate_date = end( $sync_dates );
|
|
if ( !$aggregate_date )
|
|
{
|
|
$aggregate_date = date( 'Y-m-d' );
|
|
}
|
|
|
|
$aggregated = self::aggregate_campaign_search_terms_for_client( $client_id, $aggregate_date );
|
|
|
|
return [
|
|
'history_synced' => (int) $history_synced,
|
|
'aggregated' => (int) $aggregated,
|
|
'errors' => $errors
|
|
];
|
|
}
|
|
|
|
static private function sync_campaign_keywords_and_negatives_for_client( $client_id, $customer_id, $api, $date_sync )
|
|
{
|
|
$client_id = (int) $client_id;
|
|
$customer_id = trim( (string) $customer_id );
|
|
$date_sync = $date_sync ? date( 'Y-m-d', strtotime( $date_sync ) ) : date( 'Y-m-d' );
|
|
|
|
if ( $client_id <= 0 || $customer_id === '' )
|
|
{
|
|
return [ 'keywords_synced' => 0, 'negative_keywords_synced' => 0, 'errors' => [] ];
|
|
}
|
|
|
|
$campaigns_db_map = self::build_campaigns_db_map_for_client( $client_id );
|
|
if ( empty( $campaigns_db_map ) )
|
|
{
|
|
return [ 'keywords_synced' => 0, 'negative_keywords_synced' => 0, 'errors' => [] ];
|
|
}
|
|
|
|
$ad_group_db_map = self::build_ad_group_db_map_from_db( $campaigns_db_map );
|
|
|
|
$keywords_sync = self::sync_campaign_keywords_for_client( $campaigns_db_map, $ad_group_db_map, $customer_id, $api, $date_sync );
|
|
$negative_sync = self::sync_campaign_negative_keywords_for_client( $campaigns_db_map, $ad_group_db_map, $customer_id, $api, $date_sync );
|
|
|
|
return [
|
|
'keywords_synced' => (int) ( $keywords_sync['count'] ?? 0 ),
|
|
'negative_keywords_synced' => (int) ( $negative_sync['count'] ?? 0 ),
|
|
'errors' => array_merge(
|
|
(array) ( $keywords_sync['errors'] ?? [] ),
|
|
(array) ( $negative_sync['errors'] ?? [] )
|
|
)
|
|
];
|
|
}
|
|
|
|
|
|
|
|
static private function sync_product_campaign_alerts_for_client( $client, $campaigns_db_map, $ad_group_db_map, $customer_id, $api, $date_sync, $options = [] )
|
|
{
|
|
global $mdb;
|
|
|
|
$run_missing_mapping_alerts = array_key_exists( 'run_missing_mapping_alerts', (array) $options )
|
|
? (bool) $options['run_missing_mapping_alerts']
|
|
: true;
|
|
$run_merchant_validation_alerts = array_key_exists( 'run_merchant_validation_alerts', (array) $options )
|
|
? (bool) $options['run_merchant_validation_alerts']
|
|
: true;
|
|
|
|
if ( !$run_missing_mapping_alerts && !$run_merchant_validation_alerts )
|
|
{
|
|
return [ 'count' => 0, 'errors' => [] ];
|
|
}
|
|
|
|
self::log_product_alerts_debug( 'sync_product_campaign_alerts_for_client:start', [
|
|
'client_id' => (int) ( $client['id'] ?? 0 ),
|
|
'customer_id' => (string) $customer_id,
|
|
'date_sync' => (string) $date_sync,
|
|
'campaigns_db_map_count' => count( (array) $campaigns_db_map ),
|
|
'ad_group_db_map_count' => count( (array) $ad_group_db_map ),
|
|
'run_missing_mapping_alerts' => $run_missing_mapping_alerts ? 1 : 0,
|
|
'run_merchant_validation_alerts' => $run_merchant_validation_alerts ? 1 : 0
|
|
] );
|
|
|
|
$campaign_db_ids = array_values( array_unique( array_map( 'intval', array_values( $campaigns_db_map ) ) ) );
|
|
if ( empty( $campaign_db_ids ) )
|
|
{
|
|
self::log_product_alerts_debug( 'sync_product_campaign_alerts_for_client:skip_no_campaigns', [] );
|
|
return [ 'count' => 0, 'errors' => [] ];
|
|
}
|
|
|
|
$date_sync = date( 'Y-m-d', strtotime( $date_sync ) );
|
|
$client_id = (int) ( $client['id'] ?? 0 );
|
|
$client_name = trim( (string) ( $client['name'] ?? '' ) );
|
|
$merchant_account_id = preg_replace( '/\D+/', '', (string) ( $client['google_merchant_account_id'] ?? '' ) );
|
|
|
|
if ( $run_merchant_validation_alerts && $merchant_account_id === '' )
|
|
{
|
|
self::log_product_alerts_debug( 'sync_product_campaign_alerts_for_client:skip_no_merchant_account', [
|
|
'client_id' => $client_id
|
|
] );
|
|
return [ 'count' => 0, 'errors' => [] ];
|
|
}
|
|
|
|
$shopping_campaigns = $mdb -> query(
|
|
"SELECT id, campaign_id, campaign_name
|
|
FROM campaigns
|
|
WHERE id IN (" . implode( ',', $campaign_db_ids ) . ")
|
|
AND UPPER( TRIM( COALESCE( advertising_channel_type, '' ) ) ) = 'SHOPPING'"
|
|
) -> fetchAll( \PDO::FETCH_ASSOC );
|
|
|
|
if ( !is_array( $shopping_campaigns ) || empty( $shopping_campaigns ) )
|
|
{
|
|
self::log_product_alerts_debug( 'sync_product_campaign_alerts_for_client:skip_no_shopping_campaigns', [
|
|
'client_id' => $client_id,
|
|
'campaign_db_ids' => $campaign_db_ids
|
|
] );
|
|
return [ 'count' => 0, 'errors' => [] ];
|
|
}
|
|
|
|
$shopping_campaign_external_ids = [];
|
|
$shopping_campaign_names_by_db_id = [];
|
|
|
|
foreach ( $shopping_campaigns as $campaign_row )
|
|
{
|
|
$db_campaign_id = (int) ( $campaign_row['id'] ?? 0 );
|
|
$external_campaign_id = (int) ( $campaign_row['campaign_id'] ?? 0 );
|
|
|
|
if ( $db_campaign_id <= 0 || $external_campaign_id <= 0 )
|
|
{
|
|
continue;
|
|
}
|
|
|
|
$shopping_campaign_external_ids[ (string) $external_campaign_id ] = true;
|
|
$shopping_campaign_names_by_db_id[ $db_campaign_id ] = trim( (string) ( $campaign_row['campaign_name'] ?? '' ) );
|
|
}
|
|
|
|
if ( empty( $shopping_campaign_external_ids ) )
|
|
{
|
|
self::log_product_alerts_debug( 'sync_product_campaign_alerts_for_client:skip_no_external_campaign_ids', [] );
|
|
return [ 'count' => 0, 'errors' => [] ];
|
|
}
|
|
|
|
$shopping_ad_groups_rows = $mdb -> query(
|
|
"SELECT
|
|
c.id AS campaign_db_id,
|
|
c.campaign_id AS campaign_external_id,
|
|
c.campaign_name,
|
|
ag.id AS ad_group_db_id,
|
|
ag.ad_group_id AS ad_group_external_id,
|
|
ag.ad_group_name,
|
|
ag.clicks_30,
|
|
ag.clicks_all_time
|
|
FROM campaign_ad_groups AS ag
|
|
INNER JOIN campaigns AS c ON c.id = ag.campaign_id
|
|
WHERE c.id IN (" . implode( ',', array_keys( $shopping_campaign_names_by_db_id ) ) . ")
|
|
AND ag.ad_group_id > 0"
|
|
) -> fetchAll( \PDO::FETCH_ASSOC );
|
|
|
|
$shopping_ad_groups_by_scope = [];
|
|
foreach ( (array) $shopping_ad_groups_rows as $ag_row )
|
|
{
|
|
$campaign_external_id = (int) ( $ag_row['campaign_external_id'] ?? 0 );
|
|
$ad_group_external_id = (int) ( $ag_row['ad_group_external_id'] ?? 0 );
|
|
if ( $campaign_external_id <= 0 || $ad_group_external_id <= 0 )
|
|
{
|
|
continue;
|
|
}
|
|
|
|
$scope_key = $campaign_external_id . '|' . $ad_group_external_id;
|
|
$shopping_ad_groups_by_scope[ $scope_key ] = $ag_row;
|
|
}
|
|
|
|
$ad_groups_offer_ids = [];
|
|
$offer_mapping_sources = [];
|
|
$missing_mapping_source_reliable = false;
|
|
|
|
$ad_groups_offer_ids_listing = $api -> get_shopping_ad_group_offer_ids( $customer_id );
|
|
self::log_product_alerts_debug( 'google_ads:get_shopping_ad_group_offer_ids', [
|
|
'result_type' => is_array( $ad_groups_offer_ids_listing ) ? 'array' : ( $ad_groups_offer_ids_listing === false ? 'false' : gettype( $ad_groups_offer_ids_listing ) ),
|
|
'rows_count' => is_array( $ad_groups_offer_ids_listing ) ? count( $ad_groups_offer_ids_listing ) : 0,
|
|
'sample' => is_array( $ad_groups_offer_ids_listing ) ? array_slice( $ad_groups_offer_ids_listing, 0, 3 ) : []
|
|
] );
|
|
|
|
if ( is_array( $ad_groups_offer_ids_listing ) )
|
|
{
|
|
$ad_groups_offer_ids = self::merge_shopping_ad_group_offer_rows( $ad_groups_offer_ids, $ad_groups_offer_ids_listing );
|
|
$offer_mapping_sources['listing_group'] = true;
|
|
$missing_mapping_source_reliable = true;
|
|
}
|
|
|
|
$ad_groups_offer_ids_shopping_product = $api -> get_shopping_ad_group_offer_ids_from_shopping_product( $customer_id );
|
|
self::log_product_alerts_debug( 'google_ads:get_shopping_ad_group_offer_ids_from_shopping_product', [
|
|
'result_type' => is_array( $ad_groups_offer_ids_shopping_product ) ? 'array' : ( $ad_groups_offer_ids_shopping_product === false ? 'false' : gettype( $ad_groups_offer_ids_shopping_product ) ),
|
|
'rows_count' => is_array( $ad_groups_offer_ids_shopping_product ) ? count( $ad_groups_offer_ids_shopping_product ) : 0,
|
|
'sample' => is_array( $ad_groups_offer_ids_shopping_product ) ? array_slice( $ad_groups_offer_ids_shopping_product, 0, 3 ) : []
|
|
] );
|
|
|
|
if ( is_array( $ad_groups_offer_ids_shopping_product ) )
|
|
{
|
|
$ad_groups_offer_ids = self::merge_shopping_ad_group_offer_rows( $ad_groups_offer_ids, $ad_groups_offer_ids_shopping_product );
|
|
$offer_mapping_sources['shopping_product'] = true;
|
|
}
|
|
|
|
if ( empty( $ad_groups_offer_ids ) )
|
|
{
|
|
$ad_groups_offer_ids_performance = $api -> get_shopping_ad_group_offer_ids_from_performance( $customer_id );
|
|
self::log_product_alerts_debug( 'google_ads:get_shopping_ad_group_offer_ids_from_performance', [
|
|
'result_type' => is_array( $ad_groups_offer_ids_performance ) ? 'array' : ( $ad_groups_offer_ids_performance === false ? 'false' : gettype( $ad_groups_offer_ids_performance ) ),
|
|
'rows_count' => is_array( $ad_groups_offer_ids_performance ) ? count( $ad_groups_offer_ids_performance ) : 0,
|
|
'sample' => is_array( $ad_groups_offer_ids_performance ) ? array_slice( $ad_groups_offer_ids_performance, 0, 3 ) : []
|
|
] );
|
|
|
|
if ( is_array( $ad_groups_offer_ids_performance ) )
|
|
{
|
|
$ad_groups_offer_ids = self::merge_shopping_ad_group_offer_rows( $ad_groups_offer_ids, $ad_groups_offer_ids_performance );
|
|
$offer_mapping_sources['performance'] = true;
|
|
}
|
|
}
|
|
|
|
if ( empty( $ad_groups_offer_ids ) )
|
|
{
|
|
$ad_groups_offer_ids_history = self::get_shopping_ad_group_offer_ids_from_history( $client_id, array_keys( $shopping_campaign_names_by_db_id ) );
|
|
self::log_product_alerts_debug( 'db:get_shopping_ad_group_offer_ids_from_history', [
|
|
'rows_count' => is_array( $ad_groups_offer_ids_history ) ? count( $ad_groups_offer_ids_history ) : 0,
|
|
'sample' => is_array( $ad_groups_offer_ids_history ) ? array_slice( $ad_groups_offer_ids_history, 0, 3 ) : []
|
|
] );
|
|
|
|
if ( is_array( $ad_groups_offer_ids_history ) )
|
|
{
|
|
$ad_groups_offer_ids = self::merge_shopping_ad_group_offer_rows( $ad_groups_offer_ids, $ad_groups_offer_ids_history );
|
|
$offer_mapping_sources['history'] = true;
|
|
}
|
|
}
|
|
|
|
self::log_product_alerts_debug( 'google_ads:offer_id_sources_summary', [
|
|
'sources' => array_values( array_keys( $offer_mapping_sources ) ),
|
|
'rows_count' => count( (array) $ad_groups_offer_ids ),
|
|
'missing_mapping_source_reliable' => $missing_mapping_source_reliable ? 1 : 0
|
|
] );
|
|
|
|
if ( !is_array( $ad_groups_offer_ids ) || empty( $ad_groups_offer_ids ) )
|
|
{
|
|
self::log_product_alerts_debug( 'sync_product_campaign_alerts_for_client:skip_no_ad_group_offer_rows', [] );
|
|
return [ 'count' => 0, 'errors' => [] ];
|
|
}
|
|
|
|
$offer_ids_to_verify = [];
|
|
$candidate_rows = [];
|
|
|
|
foreach ( $ad_groups_offer_ids as $row )
|
|
{
|
|
$campaign_external_id = (string) ( (int) ( $row['campaign_id'] ?? 0 ) );
|
|
$ad_group_external_id = (string) ( (int) ( $row['ad_group_id'] ?? 0 ) );
|
|
$offer_ids = array_values( array_unique( array_filter( array_map( function( $item )
|
|
{
|
|
return self::normalize_offer_id_for_lookup( $item );
|
|
}, (array) ( $row['offer_ids'] ?? [] ) ) ) ) );
|
|
|
|
if ( $campaign_external_id === '0' || $ad_group_external_id === '0' || empty( $offer_ids ) )
|
|
{
|
|
continue;
|
|
}
|
|
|
|
if ( !isset( $shopping_campaign_external_ids[ $campaign_external_id ] ) )
|
|
{
|
|
continue;
|
|
}
|
|
|
|
$scope_key = $campaign_external_id . '|' . $ad_group_external_id;
|
|
$candidate_rows[ $scope_key ] = [
|
|
'campaign_external_id' => (int) $campaign_external_id,
|
|
'ad_group_external_id' => (int) $ad_group_external_id,
|
|
'campaign_name' => trim( (string) ( $row['campaign_name'] ?? '' ) ),
|
|
'ad_group_name' => trim( (string) ( $row['ad_group_name'] ?? '' ) ),
|
|
'offer_ids' => $offer_ids
|
|
];
|
|
|
|
if ( !$run_merchant_validation_alerts )
|
|
{
|
|
continue;
|
|
}
|
|
|
|
foreach ( $offer_ids as $offer_id )
|
|
{
|
|
foreach ( self::build_offer_id_lookup_variants( $offer_id ) as $offer_id_variant )
|
|
{
|
|
$offer_ids_to_verify[ $offer_id_variant ] = true;
|
|
}
|
|
}
|
|
}
|
|
|
|
if ( $run_merchant_validation_alerts )
|
|
{
|
|
self::log_product_alerts_debug( 'merchant:offer_ids_to_verify', [
|
|
'count' => count( $offer_ids_to_verify ),
|
|
'sample' => array_slice( array_values( array_keys( $offer_ids_to_verify ) ), 0, 30 )
|
|
] );
|
|
}
|
|
|
|
$merchant_items_map = [];
|
|
if ( $run_merchant_validation_alerts && !empty( $offer_ids_to_verify ) )
|
|
{
|
|
$merchant_items_map = $api -> get_merchant_products_for_offer_ids( $merchant_account_id, array_keys( $offer_ids_to_verify ) );
|
|
self::log_product_alerts_debug( 'merchant:get_merchant_products_for_offer_ids', [
|
|
'merchant_account_id' => $merchant_account_id,
|
|
'request_count' => count( $offer_ids_to_verify ),
|
|
'response_type' => is_array( $merchant_items_map ) ? 'array' : ( $merchant_items_map === false ? 'false' : gettype( $merchant_items_map ) ),
|
|
'response_count' => is_array( $merchant_items_map ) ? count( $merchant_items_map ) : 0,
|
|
'response_sample_keys' => is_array( $merchant_items_map ) ? array_slice( array_values( array_keys( $merchant_items_map ) ), 0, 30 ) : []
|
|
] );
|
|
|
|
if ( $merchant_items_map === false )
|
|
{
|
|
$merchant_error = trim( (string) \services\GoogleAdsApi::get_setting( 'google_merchant_last_error' ) );
|
|
|
|
if ( $merchant_error === '' )
|
|
{
|
|
$merchant_error = 'Nie udalo sie pobrac produktow z Merchant Center.';
|
|
}
|
|
|
|
return [
|
|
'count' => 0,
|
|
'errors' => [ 'Pominieto alerty produktowe: ' . $merchant_error ]
|
|
];
|
|
}
|
|
}
|
|
|
|
if ( $run_merchant_validation_alerts && !is_array( $merchant_items_map ) )
|
|
{
|
|
$merchant_items_map = [];
|
|
}
|
|
|
|
$local_offer_ids_map = [];
|
|
if ( $run_merchant_validation_alerts )
|
|
{
|
|
$local_offer_ids_rows = $mdb -> query(
|
|
"SELECT offer_id
|
|
FROM products
|
|
WHERE client_id = :client_id
|
|
AND TRIM( COALESCE( offer_id, '' ) ) <> ''",
|
|
[ ':client_id' => $client_id ]
|
|
) -> fetchAll( \PDO::FETCH_ASSOC );
|
|
|
|
foreach ( (array) $local_offer_ids_rows as $local_row )
|
|
{
|
|
$local_offer_norm = self::normalize_offer_id_for_compare( $local_row['offer_id'] ?? '' );
|
|
if ( $local_offer_norm !== '' )
|
|
{
|
|
$local_offer_ids_map[ $local_offer_norm ] = true;
|
|
}
|
|
}
|
|
}
|
|
|
|
if ( $run_merchant_validation_alerts )
|
|
{
|
|
self::log_product_alerts_debug( 'db:local_offer_ids', [
|
|
'count' => count( $local_offer_ids_map ),
|
|
'sample' => array_slice( array_values( array_keys( $local_offer_ids_map ) ), 0, 30 )
|
|
] );
|
|
}
|
|
|
|
$merchant_items_map_normalized = [];
|
|
if ( $run_merchant_validation_alerts )
|
|
{
|
|
foreach ( $merchant_items_map as $merchant_offer_id => $merchant_item )
|
|
{
|
|
$offer_id_norm = self::normalize_offer_id_for_compare( $merchant_offer_id );
|
|
if ( $offer_id_norm === '' || isset( $merchant_items_map_normalized[ $offer_id_norm ] ) )
|
|
{
|
|
continue;
|
|
}
|
|
|
|
$merchant_items_map_normalized[ $offer_id_norm ] = $merchant_item;
|
|
}
|
|
}
|
|
|
|
$inserted = 0;
|
|
$problematic_scopes_by_type = [];
|
|
if ( $run_merchant_validation_alerts )
|
|
{
|
|
$problematic_scopes_by_type['ad_group_without_active_product'] = [];
|
|
$problematic_scopes_by_type['ad_group_with_orphaned_offers'] = [];
|
|
}
|
|
if ( $run_missing_mapping_alerts )
|
|
{
|
|
$problematic_scopes_by_type['ad_group_without_detected_product'] = [];
|
|
}
|
|
|
|
$insert_alert = function( $alert_type, $campaign_external_id, $ad_group_external_id, $db_campaign_id, $db_ad_group_id, $message, $meta ) use ( $mdb, $client_id, $date_sync )
|
|
{
|
|
$existing_id = (int) $mdb -> get( 'campaign_alerts', 'id', [
|
|
'AND' => [
|
|
'client_id' => $client_id,
|
|
'campaign_external_id' => (int) $campaign_external_id,
|
|
'ad_group_external_id' => (int) $ad_group_external_id,
|
|
'alert_type' => (string) $alert_type,
|
|
'date_detected' => $date_sync
|
|
]
|
|
] );
|
|
|
|
if ( $existing_id > 0 )
|
|
{
|
|
return false;
|
|
}
|
|
|
|
$mdb -> insert( 'campaign_alerts', [
|
|
'client_id' => $client_id,
|
|
'campaign_id' => (int) $db_campaign_id > 0 ? (int) $db_campaign_id : null,
|
|
'campaign_external_id' => (int) $campaign_external_id,
|
|
'ad_group_id' => (int) $db_ad_group_id > 0 ? (int) $db_ad_group_id : null,
|
|
'ad_group_external_id' => (int) $ad_group_external_id,
|
|
'alert_type' => (string) $alert_type,
|
|
'message' => (string) $message,
|
|
'meta_json' => json_encode( (array) $meta, JSON_UNESCAPED_UNICODE ),
|
|
'date_detected' => $date_sync,
|
|
'date_add' => date( 'Y-m-d H:i:s' )
|
|
] );
|
|
|
|
return true;
|
|
};
|
|
|
|
if ( $run_merchant_validation_alerts )
|
|
{
|
|
foreach ( $candidate_rows as $scope_key => $row )
|
|
{
|
|
$campaign_external_id = (int) $row['campaign_external_id'];
|
|
$ad_group_external_id = (int) $row['ad_group_external_id'];
|
|
$offer_ids = (array) $row['offer_ids'];
|
|
|
|
if ( empty( $offer_ids ) )
|
|
{
|
|
continue;
|
|
}
|
|
|
|
$active_offer_count = 0;
|
|
$orphaned_offer_ids = [];
|
|
$local_known_offer_count = 0;
|
|
foreach ( $offer_ids as $offer_id )
|
|
{
|
|
$matched = false;
|
|
foreach ( self::build_offer_id_lookup_variants( $offer_id ) as $offer_id_variant )
|
|
{
|
|
if ( isset( $merchant_items_map[ $offer_id_variant ] ) )
|
|
{
|
|
$matched = true;
|
|
break;
|
|
}
|
|
}
|
|
|
|
$offer_id_norm = self::normalize_offer_id_for_compare( $offer_id );
|
|
|
|
if ( $matched || ( $offer_id_norm !== '' && isset( $merchant_items_map_normalized[ $offer_id_norm ] ) ) )
|
|
{
|
|
$active_offer_count++;
|
|
}
|
|
else
|
|
{
|
|
$orphaned_offer_ids[] = $offer_id;
|
|
}
|
|
|
|
if ( $offer_id_norm !== '' && isset( $local_offer_ids_map[ $offer_id_norm ] ) )
|
|
{
|
|
$local_known_offer_count++;
|
|
}
|
|
}
|
|
|
|
self::log_product_alerts_debug( 'scope:verification', [
|
|
'scope_key' => $scope_key,
|
|
'campaign_external_id' => $campaign_external_id,
|
|
'ad_group_external_id' => $ad_group_external_id,
|
|
'offer_ids' => $offer_ids,
|
|
'active_offer_count' => $active_offer_count,
|
|
'orphaned_offer_ids' => $orphaned_offer_ids,
|
|
'local_known_offer_count' => $local_known_offer_count
|
|
] );
|
|
|
|
if ( $active_offer_count > 0 && empty( $orphaned_offer_ids ) )
|
|
{
|
|
continue;
|
|
}
|
|
|
|
$db_campaign_id = (int) ( $campaigns_db_map[ (string) $campaign_external_id ] ?? 0 );
|
|
$db_ad_group_id = (int) ( $ad_group_db_map[ (string) $campaign_external_id . '|' . (string) $ad_group_external_id ] ?? 0 );
|
|
|
|
$campaign_name = trim( (string) ( $row['campaign_name'] ?? '' ) );
|
|
if ( $campaign_name === '' )
|
|
{
|
|
$campaign_name = trim( (string) ( $shopping_campaign_names_by_db_id[ $db_campaign_id ] ?? '' ) );
|
|
}
|
|
if ( $campaign_name === '' )
|
|
{
|
|
$campaign_name = 'Kampania #' . $campaign_external_id;
|
|
}
|
|
|
|
$ad_group_name = trim( (string) ( $row['ad_group_name'] ?? '' ) );
|
|
if ( $ad_group_name === '' )
|
|
{
|
|
$ad_group_name = 'Grupa reklam #' . $ad_group_external_id;
|
|
}
|
|
|
|
if ( $active_offer_count === 0 )
|
|
{
|
|
if ( $local_known_offer_count > 0 )
|
|
{
|
|
self::log_product_alerts_debug( 'scope:skip_alert_local_offer_match', [
|
|
'scope_key' => $scope_key,
|
|
'campaign_external_id' => $campaign_external_id,
|
|
'ad_group_external_id' => $ad_group_external_id,
|
|
'local_known_offer_count' => $local_known_offer_count
|
|
] );
|
|
continue;
|
|
}
|
|
|
|
$message = 'Brak aktywnych produktów w Merchant Center. Grupa reklam to "' . $ad_group_name . '" w kampanii "' . $campaign_name . '" na koncie klienta "' . $client_name . '".';
|
|
|
|
if ( $insert_alert(
|
|
'ad_group_without_active_product',
|
|
$campaign_external_id,
|
|
$ad_group_external_id,
|
|
$db_campaign_id,
|
|
$db_ad_group_id,
|
|
$message,
|
|
[
|
|
'offer_ids' => $offer_ids,
|
|
'merchant_account_id' => $merchant_account_id,
|
|
'source' => 'cron_campaigns_sync_merchant'
|
|
]
|
|
) )
|
|
{
|
|
$inserted++;
|
|
}
|
|
|
|
$problematic_scopes_by_type['ad_group_without_active_product'][ $scope_key ] = true;
|
|
}
|
|
|
|
if ( !empty( $orphaned_offer_ids ) && $active_offer_count > 0 )
|
|
{
|
|
$orphaned_list = implode( ', ', array_slice( $orphaned_offer_ids, 0, 10 ) );
|
|
if ( count( $orphaned_offer_ids ) > 10 )
|
|
{
|
|
$orphaned_list .= ' (i ' . ( count( $orphaned_offer_ids ) - 10 ) . ' więcej)';
|
|
}
|
|
|
|
$message = count( $orphaned_offer_ids ) . ' osieroconych produktów (brak w MC), aktywnych: ' . $active_offer_count . '. Grupa reklam to "' . $ad_group_name . '" w kampanii "' . $campaign_name . '" na koncie klienta "' . $client_name . '". Osierocone ID: ' . $orphaned_list . '.';
|
|
|
|
if ( $insert_alert(
|
|
'ad_group_with_orphaned_offers',
|
|
$campaign_external_id,
|
|
$ad_group_external_id,
|
|
$db_campaign_id,
|
|
$db_ad_group_id,
|
|
$message,
|
|
[
|
|
'orphaned_offer_ids' => $orphaned_offer_ids,
|
|
'active_offer_count' => $active_offer_count,
|
|
'total_offer_count' => count( $offer_ids ),
|
|
'merchant_account_id' => $merchant_account_id,
|
|
'source' => 'cron_campaigns_sync_merchant'
|
|
]
|
|
) )
|
|
{
|
|
$inserted++;
|
|
}
|
|
|
|
$problematic_scopes_by_type['ad_group_with_orphaned_offers'][ $scope_key ] = true;
|
|
}
|
|
}
|
|
}
|
|
|
|
if ( $run_missing_mapping_alerts && $missing_mapping_source_reliable )
|
|
{
|
|
foreach ( $shopping_ad_groups_by_scope as $scope_key => $ag_row )
|
|
{
|
|
if ( isset( $candidate_rows[ $scope_key ] ) )
|
|
{
|
|
continue;
|
|
}
|
|
|
|
$campaign_external_id = (int) ( $ag_row['campaign_external_id'] ?? 0 );
|
|
$ad_group_external_id = (int) ( $ag_row['ad_group_external_id'] ?? 0 );
|
|
$db_campaign_id = (int) ( $ag_row['campaign_db_id'] ?? 0 );
|
|
$db_ad_group_id = (int) ( $ag_row['ad_group_db_id'] ?? 0 );
|
|
|
|
if ( $campaign_external_id <= 0 || $ad_group_external_id <= 0 )
|
|
{
|
|
continue;
|
|
}
|
|
|
|
$campaign_name = trim( (string) ( $ag_row['campaign_name'] ?? '' ) );
|
|
if ( $campaign_name === '' )
|
|
{
|
|
$campaign_name = 'Kampania #' . $campaign_external_id;
|
|
}
|
|
|
|
$ad_group_name = trim( (string) ( $ag_row['ad_group_name'] ?? '' ) );
|
|
if ( $ad_group_name === '' )
|
|
{
|
|
$ad_group_name = 'Grupa reklam #' . $ad_group_external_id;
|
|
}
|
|
|
|
$message = 'Brak wykrytego przypisanego produktu. Grupa reklam to "' . $ad_group_name . '" w kampanii "' . $campaign_name . '" na koncie klienta "' . $client_name . '".';
|
|
|
|
if ( $insert_alert(
|
|
'ad_group_without_detected_product',
|
|
$campaign_external_id,
|
|
$ad_group_external_id,
|
|
$db_campaign_id,
|
|
$db_ad_group_id,
|
|
$message,
|
|
[
|
|
'merchant_account_id' => $merchant_account_id,
|
|
'clicks_30' => (int) ( $ag_row['clicks_30'] ?? 0 ),
|
|
'clicks_all_time' => (int) ( $ag_row['clicks_all_time'] ?? 0 ),
|
|
'offer_mapping_sources' => array_values( array_keys( $offer_mapping_sources ) ),
|
|
'source' => 'cron_campaigns_sync_missing_mapping'
|
|
]
|
|
) )
|
|
{
|
|
$inserted++;
|
|
}
|
|
|
|
$problematic_scopes_by_type['ad_group_without_detected_product'][ $scope_key ] = true;
|
|
}
|
|
}
|
|
else if ( $run_missing_mapping_alerts )
|
|
{
|
|
self::log_product_alerts_debug( 'scope:skip_without_detected_product_unreliable_source', [
|
|
'sources' => array_values( array_keys( $offer_mapping_sources ) ),
|
|
'missing_mapping_source_reliable' => $missing_mapping_source_reliable ? 1 : 0
|
|
] );
|
|
}
|
|
|
|
// Czysci alerty, ktore byly prawdziwe wczesniej, ale w aktualnym syncu
|
|
// nie sa juz problematyczne albo grupa nie jest juz aktywna/pobrana.
|
|
$active_scope_map = [];
|
|
foreach ( $shopping_ad_groups_by_scope as $scope_key => $ag_row )
|
|
{
|
|
$active_scope_map[ $scope_key ] = true;
|
|
}
|
|
|
|
$alert_types_for_cleanup = array_keys( $problematic_scopes_by_type );
|
|
$existing_product_alerts = [];
|
|
if ( !empty( $alert_types_for_cleanup ) )
|
|
{
|
|
$existing_product_alerts = $mdb -> select( 'campaign_alerts', [
|
|
'id',
|
|
'campaign_external_id',
|
|
'ad_group_external_id',
|
|
'alert_type'
|
|
], [
|
|
'AND' => [
|
|
'client_id' => $client_id,
|
|
'alert_type' => $alert_types_for_cleanup
|
|
]
|
|
] );
|
|
}
|
|
|
|
$cleaned = 0;
|
|
foreach ( (array) $existing_product_alerts as $existing_alert )
|
|
{
|
|
$alert_id = (int) ( $existing_alert['id'] ?? 0 );
|
|
if ( $alert_id <= 0 )
|
|
{
|
|
continue;
|
|
}
|
|
|
|
$alert_type = (string) ( $existing_alert['alert_type'] ?? '' );
|
|
if ( $alert_type === '' || !isset( $problematic_scopes_by_type[ $alert_type ] ) )
|
|
{
|
|
continue;
|
|
}
|
|
|
|
$scope_key = (string) ( (int) ( $existing_alert['campaign_external_id'] ?? 0 ) ) . '|' . (string) ( (int) ( $existing_alert['ad_group_external_id'] ?? 0 ) );
|
|
|
|
if ( !isset( $active_scope_map[ $scope_key ] ) )
|
|
{
|
|
$mdb -> delete( 'campaign_alerts', [ 'id' => $alert_id ] );
|
|
$cleaned++;
|
|
continue;
|
|
}
|
|
|
|
if ( !isset( $problematic_scopes_by_type[ $alert_type ][ $scope_key ] ) )
|
|
{
|
|
$mdb -> delete( 'campaign_alerts', [ 'id' => $alert_id ] );
|
|
$cleaned++;
|
|
}
|
|
}
|
|
|
|
self::log_product_alerts_debug( 'alerts:cleanup', [
|
|
'existing_count' => count( (array) $existing_product_alerts ),
|
|
'cleaned_count' => $cleaned,
|
|
'run_missing_mapping_alerts' => $run_missing_mapping_alerts ? 1 : 0,
|
|
'run_merchant_validation_alerts' => $run_merchant_validation_alerts ? 1 : 0,
|
|
'problematic_without_active_product' => count( (array) ( $problematic_scopes_by_type['ad_group_without_active_product'] ?? [] ) ),
|
|
'problematic_with_orphaned_offers' => count( (array) ( $problematic_scopes_by_type['ad_group_with_orphaned_offers'] ?? [] ) ),
|
|
'problematic_without_detected_product' => count( (array) ( $problematic_scopes_by_type['ad_group_without_detected_product'] ?? [] ) )
|
|
] );
|
|
|
|
return [
|
|
'count' => $inserted,
|
|
'errors' => []
|
|
];
|
|
}
|
|
|
|
static private function log_product_alerts_debug( $stage, $context = [] )
|
|
{
|
|
$stage = trim( (string) $stage );
|
|
if ( $stage === '' )
|
|
{
|
|
$stage = 'unknown_stage';
|
|
}
|
|
|
|
$log_file = dirname( __DIR__, 2 ) . '/tmp/campaign_alerts_debug.log';
|
|
|
|
$payload = [
|
|
'ts' => date( 'Y-m-d H:i:s' ),
|
|
'stage' => $stage,
|
|
'context' => is_array( $context ) ? $context : [ 'value' => $context ]
|
|
];
|
|
|
|
$json = json_encode( $payload, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES );
|
|
if ( $json === false )
|
|
{
|
|
$json = '{"ts":"' . date( 'Y-m-d H:i:s' ) . '","stage":"json_encode_failed"}';
|
|
}
|
|
|
|
if ( strlen( $json ) > 30000 )
|
|
{
|
|
$json = substr( $json, 0, 30000 ) . '...TRUNCATED';
|
|
}
|
|
|
|
@file_put_contents( $log_file, $json . PHP_EOL, FILE_APPEND | LOCK_EX );
|
|
}
|
|
|
|
static private function merge_shopping_ad_group_offer_rows( $base_rows, $rows_to_merge )
|
|
{
|
|
$scope_map = [];
|
|
|
|
foreach ( (array) $base_rows as $row )
|
|
{
|
|
$campaign_id = (int) ( $row['campaign_id'] ?? 0 );
|
|
$ad_group_id = (int) ( $row['ad_group_id'] ?? 0 );
|
|
if ( $campaign_id <= 0 || $ad_group_id <= 0 )
|
|
{
|
|
continue;
|
|
}
|
|
|
|
$scope_key = $campaign_id . '|' . $ad_group_id;
|
|
$scope_map[ $scope_key ] = [
|
|
'campaign_id' => $campaign_id,
|
|
'campaign_name' => trim( (string) ( $row['campaign_name'] ?? '' ) ),
|
|
'ad_group_id' => $ad_group_id,
|
|
'ad_group_name' => trim( (string) ( $row['ad_group_name'] ?? '' ) ),
|
|
'offer_ids' => []
|
|
];
|
|
|
|
foreach ( (array) ( $row['offer_ids'] ?? [] ) as $offer_id )
|
|
{
|
|
$offer_id_norm = self::normalize_offer_id_for_lookup( $offer_id );
|
|
if ( $offer_id_norm === '' )
|
|
{
|
|
continue;
|
|
}
|
|
$scope_map[ $scope_key ]['offer_ids'][ $offer_id_norm ] = true;
|
|
}
|
|
}
|
|
|
|
foreach ( (array) $rows_to_merge as $row )
|
|
{
|
|
$campaign_id = (int) ( $row['campaign_id'] ?? 0 );
|
|
$ad_group_id = (int) ( $row['ad_group_id'] ?? 0 );
|
|
if ( $campaign_id <= 0 || $ad_group_id <= 0 )
|
|
{
|
|
continue;
|
|
}
|
|
|
|
$scope_key = $campaign_id . '|' . $ad_group_id;
|
|
if ( !isset( $scope_map[ $scope_key ] ) )
|
|
{
|
|
$scope_map[ $scope_key ] = [
|
|
'campaign_id' => $campaign_id,
|
|
'campaign_name' => trim( (string) ( $row['campaign_name'] ?? '' ) ),
|
|
'ad_group_id' => $ad_group_id,
|
|
'ad_group_name' => trim( (string) ( $row['ad_group_name'] ?? '' ) ),
|
|
'offer_ids' => []
|
|
];
|
|
}
|
|
|
|
if ( trim( (string) $scope_map[ $scope_key ]['campaign_name'] ) === '' )
|
|
{
|
|
$scope_map[ $scope_key ]['campaign_name'] = trim( (string) ( $row['campaign_name'] ?? '' ) );
|
|
}
|
|
if ( trim( (string) $scope_map[ $scope_key ]['ad_group_name'] ) === '' )
|
|
{
|
|
$scope_map[ $scope_key ]['ad_group_name'] = trim( (string) ( $row['ad_group_name'] ?? '' ) );
|
|
}
|
|
|
|
foreach ( (array) ( $row['offer_ids'] ?? [] ) as $offer_id )
|
|
{
|
|
$offer_id_norm = self::normalize_offer_id_for_lookup( $offer_id );
|
|
if ( $offer_id_norm === '' )
|
|
{
|
|
continue;
|
|
}
|
|
$scope_map[ $scope_key ]['offer_ids'][ $offer_id_norm ] = true;
|
|
}
|
|
}
|
|
|
|
$rows = [];
|
|
foreach ( $scope_map as $scope_row )
|
|
{
|
|
$offer_ids = array_values( array_keys( (array) ( $scope_row['offer_ids'] ?? [] ) ) );
|
|
if ( empty( $offer_ids ) )
|
|
{
|
|
continue;
|
|
}
|
|
|
|
$scope_row['offer_ids'] = $offer_ids;
|
|
$rows[] = $scope_row;
|
|
}
|
|
|
|
return $rows;
|
|
}
|
|
|
|
static private function normalize_offer_id_for_lookup( $offer_id )
|
|
{
|
|
$offer_id = trim( (string) $offer_id );
|
|
|
|
if ( $offer_id === '' )
|
|
{
|
|
return '';
|
|
}
|
|
|
|
$offer_id = trim( $offer_id, " \t\n\r\0\x0B'\"" );
|
|
|
|
// Google Ads moze zwracac format channel:language:country:offer_id.
|
|
// W Merchant API do products.list kluczem jest offer_id.
|
|
if ( preg_match( '/^[a-z_]+:[a-z]{2,8}:[a-z]{2,8}:(.+)$/i', $offer_id, $matches ) )
|
|
{
|
|
$offer_id = trim( (string) ( $matches[1] ?? '' ) );
|
|
}
|
|
|
|
return $offer_id;
|
|
}
|
|
|
|
static private function normalize_offer_id_for_compare( $offer_id )
|
|
{
|
|
$offer_id = self::normalize_offer_id_for_lookup( $offer_id );
|
|
|
|
if ( $offer_id === '' )
|
|
{
|
|
return '';
|
|
}
|
|
|
|
$offer_id = strtolower( $offer_id );
|
|
|
|
return $offer_id;
|
|
}
|
|
|
|
static private function build_offer_id_lookup_variants( $offer_id )
|
|
{
|
|
$offer_id = self::normalize_offer_id_for_lookup( $offer_id );
|
|
if ( $offer_id === '' )
|
|
{
|
|
return [];
|
|
}
|
|
|
|
$variants = [ $offer_id ];
|
|
|
|
// Shopify: spotykane roznice zapisu kraju w prefiksie (pl vs PL).
|
|
if ( preg_match( '/^shopify_([a-z]{2})_(.+)$/i', $offer_id, $matches ) )
|
|
{
|
|
$country = (string) ( $matches[1] ?? '' );
|
|
$rest = (string) ( $matches[2] ?? '' );
|
|
|
|
if ( $rest !== '' )
|
|
{
|
|
$variants[] = 'shopify_' . strtolower( $country ) . '_' . $rest;
|
|
$variants[] = 'shopify_' . strtoupper( $country ) . '_' . $rest;
|
|
}
|
|
}
|
|
|
|
return array_values( array_unique( array_filter( $variants ) ) );
|
|
}
|
|
|
|
static private function get_shopping_ad_group_offer_ids_from_history( $client_id, $shopping_campaign_db_ids )
|
|
{
|
|
global $mdb;
|
|
|
|
$client_id = (int) $client_id;
|
|
$shopping_campaign_db_ids = array_values( array_unique( array_map( 'intval', (array) $shopping_campaign_db_ids ) ) );
|
|
|
|
if ( $client_id <= 0 || empty( $shopping_campaign_db_ids ) )
|
|
{
|
|
return [];
|
|
}
|
|
|
|
$campaign_ids_sql = implode( ',', $shopping_campaign_db_ids );
|
|
|
|
$rows = $mdb -> query(
|
|
"SELECT
|
|
c.id AS campaign_db_id,
|
|
c.campaign_id AS campaign_external_id,
|
|
c.campaign_name,
|
|
ag.id AS ad_group_db_id,
|
|
ag.ad_group_id AS ad_group_external_id,
|
|
ag.ad_group_name,
|
|
p.offer_id
|
|
FROM campaign_ad_groups AS ag
|
|
INNER JOIN campaigns AS c ON c.id = ag.campaign_id
|
|
INNER JOIN products_history AS ph ON ph.campaign_id = c.id AND ph.ad_group_id = ag.id
|
|
INNER JOIN products AS p ON p.id = ph.product_id
|
|
WHERE c.client_id = :client_id
|
|
AND c.id IN (" . $campaign_ids_sql . ")
|
|
AND ag.ad_group_id > 0
|
|
AND TRIM( COALESCE( p.offer_id, '' ) ) <> ''",
|
|
[ ':client_id' => $client_id ]
|
|
) -> fetchAll( \PDO::FETCH_ASSOC );
|
|
|
|
if ( !is_array( $rows ) || empty( $rows ) )
|
|
{
|
|
return [];
|
|
}
|
|
|
|
$scopes = [];
|
|
|
|
foreach ( $rows as $row )
|
|
{
|
|
$campaign_external_id = (int) ( $row['campaign_external_id'] ?? 0 );
|
|
$ad_group_external_id = (int) ( $row['ad_group_external_id'] ?? 0 );
|
|
$offer_id = trim( (string) ( $row['offer_id'] ?? '' ) );
|
|
|
|
if ( $campaign_external_id <= 0 || $ad_group_external_id <= 0 || $offer_id === '' )
|
|
{
|
|
continue;
|
|
}
|
|
|
|
$scope_key = $campaign_external_id . '|' . $ad_group_external_id;
|
|
|
|
if ( !isset( $scopes[ $scope_key ] ) )
|
|
{
|
|
$scopes[ $scope_key ] = [
|
|
'campaign_id' => $campaign_external_id,
|
|
'campaign_name' => trim( (string) ( $row['campaign_name'] ?? '' ) ),
|
|
'ad_group_id' => $ad_group_external_id,
|
|
'ad_group_name' => trim( (string) ( $row['ad_group_name'] ?? '' ) ),
|
|
'offer_ids' => []
|
|
];
|
|
}
|
|
|
|
$scopes[ $scope_key ]['offer_ids'][ $offer_id ] = true;
|
|
}
|
|
|
|
foreach ( $scopes as &$scope )
|
|
{
|
|
$scope['offer_ids'] = array_values( array_keys( (array) $scope['offer_ids'] ) );
|
|
}
|
|
unset( $scope );
|
|
|
|
return array_values( $scopes );
|
|
}
|
|
|
|
static private function sync_campaign_ad_groups_for_client( $campaigns_db_map, $customer_id, $api, $date_sync, $as_of_date = null )
|
|
{
|
|
global $mdb;
|
|
|
|
$campaign_db_ids = array_values( array_unique( array_map( 'intval', array_values( $campaigns_db_map ) ) ) );
|
|
if ( empty( $campaign_db_ids ) )
|
|
{
|
|
return [ 'count' => 0, 'ad_group_map' => [], 'errors' => [] ];
|
|
}
|
|
|
|
$as_of_date = $as_of_date ? date( 'Y-m-d', strtotime( $as_of_date ) ) : date( 'Y-m-d', strtotime( $date_sync ) );
|
|
$ad_groups_30 = $api -> get_ad_groups_30_days( $customer_id, $as_of_date );
|
|
if ( $ad_groups_30 === false )
|
|
{
|
|
$last_err = \services\GoogleAdsApi::get_setting( 'google_ads_last_error' );
|
|
return [ 'count' => 0, 'ad_group_map' => [], 'errors' => [ 'Blad pobierania grup reklam (30 dni): ' . $last_err ] ];
|
|
}
|
|
|
|
$ad_groups_all_time = $api -> get_ad_groups_all_time( $customer_id, $as_of_date );
|
|
if ( $ad_groups_all_time === false )
|
|
{
|
|
$last_err = \services\GoogleAdsApi::get_setting( 'google_ads_last_error' );
|
|
return [ 'count' => 0, 'ad_group_map' => [], 'errors' => [ 'Blad pobierania grup reklam (all time): ' . $last_err ] ];
|
|
}
|
|
|
|
if ( !is_array( $ad_groups_30 ) )
|
|
{
|
|
$ad_groups_30 = [];
|
|
}
|
|
|
|
if ( !is_array( $ad_groups_all_time ) )
|
|
{
|
|
$ad_groups_all_time = [];
|
|
}
|
|
|
|
$map_30 = [];
|
|
foreach ( $ad_groups_30 as $row )
|
|
{
|
|
$campaign_external_id = isset( $row['campaign_id'] ) ? (string) $row['campaign_id'] : '';
|
|
$ad_group_external_id = isset( $row['ad_group_id'] ) ? (string) $row['ad_group_id'] : '';
|
|
if ( $campaign_external_id === '' || $ad_group_external_id === '' )
|
|
{
|
|
continue;
|
|
}
|
|
$map_30[ $campaign_external_id . '|' . $ad_group_external_id ] = $row;
|
|
}
|
|
|
|
$map_all_time = [];
|
|
foreach ( $ad_groups_all_time as $row )
|
|
{
|
|
$campaign_external_id = isset( $row['campaign_id'] ) ? (string) $row['campaign_id'] : '';
|
|
$ad_group_external_id = isset( $row['ad_group_id'] ) ? (string) $row['ad_group_id'] : '';
|
|
if ( $campaign_external_id === '' || $ad_group_external_id === '' )
|
|
{
|
|
continue;
|
|
}
|
|
$map_all_time[ $campaign_external_id . '|' . $ad_group_external_id ] = $row;
|
|
}
|
|
|
|
$keys = array_values( array_unique( array_merge( array_keys( $map_30 ), array_keys( $map_all_time ) ) ) );
|
|
$ad_group_db_map = [];
|
|
$seen_db_ids = [];
|
|
$count = 0;
|
|
|
|
foreach ( $keys as $key )
|
|
{
|
|
$parts = explode( '|', $key, 2 );
|
|
$campaign_external_id = $parts[0] ?? '';
|
|
$ad_group_external_id = $parts[1] ?? '';
|
|
|
|
$db_campaign_id = (int) ( $campaigns_db_map[ $campaign_external_id ] ?? 0 );
|
|
if ( $db_campaign_id <= 0 || $ad_group_external_id === '' )
|
|
{
|
|
continue;
|
|
}
|
|
|
|
$row_30 = $map_30[ $key ] ?? [];
|
|
$row_all_time = $map_all_time[ $key ] ?? [];
|
|
|
|
$ad_group_name = trim( (string) ( $row_30['ad_group_name'] ?? ( $row_all_time['ad_group_name'] ?? '' ) ) );
|
|
if ( $ad_group_name === '' )
|
|
{
|
|
$ad_group_name = 'Ad group #' . $ad_group_external_id;
|
|
}
|
|
|
|
$data = [
|
|
'ad_group_name' => $ad_group_name,
|
|
'impressions_30' => (int) ( $row_30['impressions'] ?? 0 ),
|
|
'clicks_30' => (int) ( $row_30['clicks'] ?? 0 ),
|
|
'cost_30' => (float) ( $row_30['cost'] ?? 0 ),
|
|
'conversions_30' => (float) ( $row_30['conversions'] ?? 0 ),
|
|
'conversion_value_30' => (float) ( $row_30['conversion_value'] ?? 0 ),
|
|
'roas_30' => (float) ( $row_30['roas'] ?? 0 ),
|
|
'impressions_all_time' => (int) ( $row_all_time['impressions'] ?? 0 ),
|
|
'clicks_all_time' => (int) ( $row_all_time['clicks'] ?? 0 ),
|
|
'cost_all_time' => (float) ( $row_all_time['cost'] ?? 0 ),
|
|
'conversions_all_time' => (float) ( $row_all_time['conversions'] ?? 0 ),
|
|
'conversion_value_all_time' => (float) ( $row_all_time['conversion_value'] ?? 0 ),
|
|
'roas_all_time' => (float) ( $row_all_time['roas'] ?? 0 ),
|
|
'date_sync' => $date_sync
|
|
];
|
|
|
|
// Upsert: zachowaj istniejace DB ID (nie kasuj, bo CASCADE usunalby historie)
|
|
$existing_id = (int) $mdb -> get( 'campaign_ad_groups', 'id', [ 'AND' => [
|
|
'campaign_id' => $db_campaign_id,
|
|
'ad_group_id' => (int) $ad_group_external_id
|
|
] ] );
|
|
|
|
$data['status'] = 'active';
|
|
|
|
if ( $existing_id > 0 )
|
|
{
|
|
$mdb -> update( 'campaign_ad_groups', $data, [ 'id' => $existing_id ] );
|
|
$db_ad_group_id = $existing_id;
|
|
}
|
|
else
|
|
{
|
|
$data['campaign_id'] = $db_campaign_id;
|
|
$data['ad_group_id'] = (int) $ad_group_external_id;
|
|
$mdb -> insert( 'campaign_ad_groups', $data );
|
|
$db_ad_group_id = (int) $mdb -> id();
|
|
}
|
|
if ( $db_ad_group_id > 0 )
|
|
{
|
|
$ad_group_db_map[ $key ] = $db_ad_group_id;
|
|
$seen_db_ids[] = $db_ad_group_id;
|
|
$count++;
|
|
}
|
|
}
|
|
|
|
// Oznacz ad_groups ktore nie pojawiaja sie juz w API jako paused (zachowaj PMax placeholder ad_group_id=0).
|
|
if ( !empty( $campaign_db_ids ) )
|
|
{
|
|
$pause_where = [
|
|
'campaign_id' => $campaign_db_ids,
|
|
'ad_group_id[!]' => 0
|
|
];
|
|
|
|
if ( !empty( $seen_db_ids ) )
|
|
{
|
|
$pause_where['id[!]'] = $seen_db_ids;
|
|
}
|
|
|
|
$mdb -> update( 'campaign_ad_groups', [ 'status' => 'paused' ], $pause_where );
|
|
}
|
|
|
|
return [ 'count' => $count, 'ad_group_map' => $ad_group_db_map, 'errors' => [] ];
|
|
}
|
|
|
|
static private function sync_campaign_search_terms_for_client( $campaigns_db_map, $ad_group_db_map, $customer_id, $api, $date_sync )
|
|
{
|
|
global $mdb;
|
|
|
|
$campaign_db_ids = array_values( array_unique( array_map( 'intval', array_values( $campaigns_db_map ) ) ) );
|
|
if ( empty( $campaign_db_ids ) )
|
|
{
|
|
return [ 'count' => 0, 'errors' => [] ];
|
|
}
|
|
|
|
$search_terms_30 = $api -> get_search_terms_30_days( $customer_id );
|
|
if ( $search_terms_30 === false )
|
|
{
|
|
$last_err = \services\GoogleAdsApi::get_setting( 'google_ads_last_error' );
|
|
return [ 'count' => 0, 'errors' => [ 'Blad pobierania fraz wyszukiwanych (30 dni): ' . $last_err ] ];
|
|
}
|
|
|
|
$search_terms_all_time = $api -> get_search_terms_all_time( $customer_id );
|
|
if ( $search_terms_all_time === false )
|
|
{
|
|
$last_err = \services\GoogleAdsApi::get_setting( 'google_ads_last_error' );
|
|
return [ 'count' => 0, 'errors' => [ 'Blad pobierania fraz wyszukiwanych (all time): ' . $last_err ] ];
|
|
}
|
|
|
|
if ( !is_array( $search_terms_30 ) )
|
|
{
|
|
$search_terms_30 = [];
|
|
}
|
|
|
|
if ( !is_array( $search_terms_all_time ) )
|
|
{
|
|
$search_terms_all_time = [];
|
|
}
|
|
|
|
$map_30 = [];
|
|
foreach ( $search_terms_30 as $row )
|
|
{
|
|
$campaign_external_id = isset( $row['campaign_id'] ) ? (string) $row['campaign_id'] : '';
|
|
$ad_group_external_id = isset( $row['ad_group_id'] ) ? (string) $row['ad_group_id'] : '';
|
|
$search_term = trim( (string) ( $row['search_term'] ?? '' ) );
|
|
if ( $campaign_external_id === '' || $ad_group_external_id === '' || $search_term === '' )
|
|
{
|
|
continue;
|
|
}
|
|
$map_30[ $campaign_external_id . '|' . $ad_group_external_id . '|' . strtolower( $search_term ) ] = $row;
|
|
}
|
|
|
|
$map_all_time = [];
|
|
foreach ( $search_terms_all_time as $row )
|
|
{
|
|
$campaign_external_id = isset( $row['campaign_id'] ) ? (string) $row['campaign_id'] : '';
|
|
$ad_group_external_id = isset( $row['ad_group_id'] ) ? (string) $row['ad_group_id'] : '';
|
|
$search_term = trim( (string) ( $row['search_term'] ?? '' ) );
|
|
if ( $campaign_external_id === '' || $ad_group_external_id === '' || $search_term === '' )
|
|
{
|
|
continue;
|
|
}
|
|
$map_all_time[ $campaign_external_id . '|' . $ad_group_external_id . '|' . strtolower( $search_term ) ] = $row;
|
|
}
|
|
|
|
$mdb -> delete( 'campaign_search_terms', [ 'campaign_id' => $campaign_db_ids ] );
|
|
|
|
$keys = array_values( array_unique( array_merge( array_keys( $map_30 ), array_keys( $map_all_time ) ) ) );
|
|
$count = 0;
|
|
|
|
foreach ( $keys as $key )
|
|
{
|
|
$parts = explode( '|', $key, 3 );
|
|
$campaign_external_id = $parts[0] ?? '';
|
|
$ad_group_external_id = $parts[1] ?? '';
|
|
$db_campaign_id = (int) ( $campaigns_db_map[ $campaign_external_id ] ?? 0 );
|
|
$db_ad_group_id = (int) ( $ad_group_db_map[ $campaign_external_id . '|' . $ad_group_external_id ] ?? 0 );
|
|
|
|
if ( $db_campaign_id > 0 && $db_ad_group_id <= 0 && $ad_group_external_id === '0' )
|
|
{
|
|
$db_ad_group_id = self::ensure_campaign_level_ad_group( $db_campaign_id, $date_sync );
|
|
if ( $db_ad_group_id > 0 )
|
|
{
|
|
$ad_group_db_map[ $campaign_external_id . '|0' ] = $db_ad_group_id;
|
|
}
|
|
}
|
|
|
|
if ( $db_campaign_id <= 0 || $db_ad_group_id <= 0 )
|
|
{
|
|
continue;
|
|
}
|
|
|
|
$row_30 = $map_30[ $key ] ?? [];
|
|
$row_all_time = $map_all_time[ $key ] ?? [];
|
|
|
|
$search_term = trim( (string) ( $row_30['search_term'] ?? ( $row_all_time['search_term'] ?? '' ) ) );
|
|
if ( $search_term === '' )
|
|
{
|
|
continue;
|
|
}
|
|
|
|
$clicks_30 = (int) ( $row_30['clicks'] ?? 0 );
|
|
$clicks_all_time = (int) ( $row_all_time['clicks'] ?? 0 );
|
|
if ( $clicks_30 <= 0 && $clicks_all_time <= 0 )
|
|
{
|
|
continue;
|
|
}
|
|
|
|
$mdb -> insert( 'campaign_search_terms', [
|
|
'campaign_id' => $db_campaign_id,
|
|
'ad_group_id' => $db_ad_group_id,
|
|
'search_term' => $search_term,
|
|
'impressions_30' => (int) ( $row_30['impressions'] ?? 0 ),
|
|
'clicks_30' => $clicks_30,
|
|
'cost_30' => (float) ( $row_30['cost'] ?? 0 ),
|
|
'conversions_30' => (float) ( $row_30['conversions'] ?? 0 ),
|
|
'conversion_value_30' => (float) ( $row_30['conversion_value'] ?? 0 ),
|
|
'roas_30' => (float) ( $row_30['roas'] ?? 0 ),
|
|
'impressions_all_time' => (int) ( $row_all_time['impressions'] ?? 0 ),
|
|
'clicks_all_time' => $clicks_all_time,
|
|
'cost_all_time' => (float) ( $row_all_time['cost'] ?? 0 ),
|
|
'conversions_all_time' => (float) ( $row_all_time['conversions'] ?? 0 ),
|
|
'conversion_value_all_time' => (float) ( $row_all_time['conversion_value'] ?? 0 ),
|
|
'roas_all_time' => (float) ( $row_all_time['roas'] ?? 0 ),
|
|
'date_sync' => $date_sync
|
|
] );
|
|
|
|
$count++;
|
|
}
|
|
|
|
return [ 'count' => $count, 'errors' => [] ];
|
|
}
|
|
|
|
static private function build_ad_group_db_map_from_db( $campaigns_db_map )
|
|
{
|
|
global $mdb;
|
|
|
|
$ad_group_db_map = [];
|
|
$campaign_db_ids = array_values( array_unique( array_map( 'intval', array_values( $campaigns_db_map ) ) ) );
|
|
|
|
if ( empty( $campaign_db_ids ) )
|
|
{
|
|
return $ad_group_db_map;
|
|
}
|
|
|
|
$ag_rows = $mdb -> query(
|
|
"SELECT id, campaign_id, ad_group_id FROM campaign_ad_groups WHERE campaign_id IN (" . implode( ',', $campaign_db_ids ) . ")"
|
|
) -> fetchAll( \PDO::FETCH_ASSOC );
|
|
|
|
$reverse_campaign_map = array_flip( $campaigns_db_map );
|
|
|
|
if ( is_array( $ag_rows ) )
|
|
{
|
|
foreach ( $ag_rows as $ag_row )
|
|
{
|
|
$ext_campaign_id = $reverse_campaign_map[ (int) $ag_row['campaign_id'] ] ?? '';
|
|
if ( $ext_campaign_id !== '' )
|
|
{
|
|
$ad_group_db_map[ $ext_campaign_id . '|' . $ag_row['ad_group_id'] ] = (int) $ag_row['id'];
|
|
}
|
|
}
|
|
}
|
|
|
|
return $ad_group_db_map;
|
|
}
|
|
|
|
static private function sync_campaign_search_terms_daily( $campaigns_db_map, $ad_group_db_map, $customer_id, $api, $date )
|
|
{
|
|
global $mdb;
|
|
|
|
$date = date( 'Y-m-d', strtotime( $date ) );
|
|
|
|
$campaign_db_ids = array_values( array_unique( array_map( 'intval', array_values( $campaigns_db_map ) ) ) );
|
|
if ( empty( $campaign_db_ids ) )
|
|
{
|
|
return [ 'count' => 0, 'errors' => [] ];
|
|
}
|
|
|
|
$terms = $api -> get_search_terms_for_date( $customer_id, $date );
|
|
if ( $terms === false )
|
|
{
|
|
$last_err = \services\GoogleAdsApi::get_setting( 'google_ads_last_error' );
|
|
return [ 'count' => 0, 'errors' => [ 'Blad pobierania fraz wyszukiwanych za ' . $date . ': ' . $last_err ] ];
|
|
}
|
|
|
|
if ( !is_array( $terms ) )
|
|
{
|
|
$terms = [];
|
|
}
|
|
|
|
$count = 0;
|
|
|
|
foreach ( $terms as $row )
|
|
{
|
|
$campaign_external_id = isset( $row['campaign_id'] ) ? (string) $row['campaign_id'] : '';
|
|
$ad_group_external_id = isset( $row['ad_group_id'] ) ? (string) $row['ad_group_id'] : '';
|
|
$search_term = trim( (string) ( $row['search_term'] ?? '' ) );
|
|
|
|
if ( $campaign_external_id === '' || $search_term === '' )
|
|
{
|
|
continue;
|
|
}
|
|
|
|
$clicks = (int) ( $row['clicks'] ?? 0 );
|
|
if ( $clicks <= 0 )
|
|
{
|
|
continue;
|
|
}
|
|
|
|
$db_campaign_id = (int) ( $campaigns_db_map[ $campaign_external_id ] ?? 0 );
|
|
$db_ad_group_id = (int) ( $ad_group_db_map[ $campaign_external_id . '|' . $ad_group_external_id ] ?? 0 );
|
|
|
|
if ( $db_campaign_id > 0 && $db_ad_group_id <= 0 && $ad_group_external_id === '0' )
|
|
{
|
|
$db_ad_group_id = self::ensure_campaign_level_ad_group( $db_campaign_id, $date );
|
|
if ( $db_ad_group_id > 0 )
|
|
{
|
|
$ad_group_db_map[ $campaign_external_id . '|0' ] = $db_ad_group_id;
|
|
}
|
|
}
|
|
|
|
if ( $db_campaign_id <= 0 || $db_ad_group_id <= 0 )
|
|
{
|
|
continue;
|
|
}
|
|
|
|
$data = [
|
|
'impressions' => (int) ( $row['impressions'] ?? 0 ),
|
|
'clicks' => $clicks,
|
|
'cost' => (float) ( $row['cost'] ?? 0 ),
|
|
'conversions' => (float) ( $row['conversions'] ?? 0 ),
|
|
'conversion_value' => (float) ( $row['conversion_value'] ?? 0 ),
|
|
];
|
|
|
|
$existing_id = (int) $mdb -> get( 'campaign_search_terms_history', 'id', [ 'AND' => [
|
|
'campaign_id' => $db_campaign_id,
|
|
'ad_group_id' => $db_ad_group_id,
|
|
'search_term' => $search_term,
|
|
'date_add' => $date
|
|
] ] );
|
|
|
|
if ( $existing_id > 0 )
|
|
{
|
|
$mdb -> update( 'campaign_search_terms_history', $data, [ 'id' => $existing_id ] );
|
|
}
|
|
else
|
|
{
|
|
$data['campaign_id'] = $db_campaign_id;
|
|
$data['ad_group_id'] = $db_ad_group_id;
|
|
$data['search_term'] = $search_term;
|
|
$data['date_add'] = $date;
|
|
$mdb -> insert( 'campaign_search_terms_history', $data );
|
|
}
|
|
|
|
$count++;
|
|
}
|
|
|
|
return [ 'count' => $count, 'errors' => [] ];
|
|
}
|
|
|
|
static private function aggregate_campaign_search_terms_for_client( $client_id, $date_sync )
|
|
{
|
|
global $mdb;
|
|
|
|
$client_id = (int) $client_id;
|
|
$date_sync = date( 'Y-m-d', strtotime( $date_sync ) );
|
|
$date_30_ago = date( 'Y-m-d', strtotime( $date_sync . ' -30 days' ) );
|
|
|
|
$campaign_db_ids = $mdb -> select( 'campaigns', 'id', [ 'client_id' => $client_id ] );
|
|
if ( empty( $campaign_db_ids ) )
|
|
{
|
|
return 0;
|
|
}
|
|
|
|
$ids_list = implode( ',', array_map( 'intval', $campaign_db_ids ) );
|
|
|
|
$rows_30 = $mdb -> query(
|
|
"SELECT campaign_id, ad_group_id, search_term,
|
|
SUM(impressions) AS impressions,
|
|
SUM(clicks) AS clicks,
|
|
SUM(cost) AS cost,
|
|
SUM(conversions) AS conversions,
|
|
SUM(conversion_value) AS conversion_value
|
|
FROM campaign_search_terms_history
|
|
WHERE campaign_id IN ({$ids_list})
|
|
AND date_add > :date_30_ago
|
|
AND date_add <= :date_sync
|
|
GROUP BY campaign_id, ad_group_id, search_term
|
|
HAVING SUM(clicks) > 0",
|
|
[ ':date_30_ago' => $date_30_ago, ':date_sync' => $date_sync ]
|
|
) -> fetchAll( \PDO::FETCH_ASSOC );
|
|
|
|
$rows_all_time = $mdb -> query(
|
|
"SELECT campaign_id, ad_group_id, search_term,
|
|
SUM(impressions) AS impressions,
|
|
SUM(clicks) AS clicks,
|
|
SUM(cost) AS cost,
|
|
SUM(conversions) AS conversions,
|
|
SUM(conversion_value) AS conversion_value
|
|
FROM campaign_search_terms_history
|
|
WHERE campaign_id IN ({$ids_list})
|
|
GROUP BY campaign_id, ad_group_id, search_term
|
|
HAVING SUM(clicks) > 0",
|
|
[]
|
|
) -> fetchAll( \PDO::FETCH_ASSOC );
|
|
|
|
$map_30 = [];
|
|
if ( is_array( $rows_30 ) )
|
|
{
|
|
foreach ( $rows_30 as $r )
|
|
{
|
|
$key = $r['campaign_id'] . '|' . $r['ad_group_id'] . '|' . strtolower( $r['search_term'] );
|
|
$map_30[ $key ] = $r;
|
|
}
|
|
}
|
|
|
|
$map_all_time = [];
|
|
if ( is_array( $rows_all_time ) )
|
|
{
|
|
foreach ( $rows_all_time as $r )
|
|
{
|
|
$key = $r['campaign_id'] . '|' . $r['ad_group_id'] . '|' . strtolower( $r['search_term'] );
|
|
$map_all_time[ $key ] = $r;
|
|
}
|
|
}
|
|
|
|
$mdb -> delete( 'campaign_search_terms', [ 'campaign_id' => array_map( 'intval', $campaign_db_ids ) ] );
|
|
|
|
$keys = array_values( array_unique( array_merge( array_keys( $map_30 ), array_keys( $map_all_time ) ) ) );
|
|
$count = 0;
|
|
|
|
foreach ( $keys as $key )
|
|
{
|
|
$r30 = $map_30[ $key ] ?? null;
|
|
$rall = $map_all_time[ $key ] ?? null;
|
|
$ref = $r30 ?? $rall;
|
|
|
|
$cost_30 = (float) ( $r30['cost'] ?? 0 );
|
|
$cv_30 = (float) ( $r30['conversion_value'] ?? 0 );
|
|
$roas_30 = ( $cost_30 > 0 ) ? round( ( $cv_30 / $cost_30 ) * 100, 2 ) : 0;
|
|
|
|
$cost_all = (float) ( $rall['cost'] ?? 0 );
|
|
$cv_all = (float) ( $rall['conversion_value'] ?? 0 );
|
|
$roas_all = ( $cost_all > 0 ) ? round( ( $cv_all / $cost_all ) * 100, 2 ) : 0;
|
|
|
|
$mdb -> insert( 'campaign_search_terms', [
|
|
'campaign_id' => (int) $ref['campaign_id'],
|
|
'ad_group_id' => (int) $ref['ad_group_id'],
|
|
'search_term' => $ref['search_term'],
|
|
'impressions_30' => (int) ( $r30['impressions'] ?? 0 ),
|
|
'clicks_30' => (int) ( $r30['clicks'] ?? 0 ),
|
|
'cost_30' => $cost_30,
|
|
'conversions_30' => (float) ( $r30['conversions'] ?? 0 ),
|
|
'conversion_value_30' => $cv_30,
|
|
'roas_30' => $roas_30,
|
|
'impressions_all_time' => (int) ( $rall['impressions'] ?? 0 ),
|
|
'clicks_all_time' => (int) ( $rall['clicks'] ?? 0 ),
|
|
'cost_all_time' => $cost_all,
|
|
'conversions_all_time' => (float) ( $rall['conversions'] ?? 0 ),
|
|
'conversion_value_all_time' => $cv_all,
|
|
'roas_all_time' => $roas_all,
|
|
'date_sync' => $date_sync
|
|
] );
|
|
|
|
$count++;
|
|
}
|
|
|
|
return $count;
|
|
}
|
|
|
|
static private function ensure_campaign_level_ad_group( $db_campaign_id, $date_sync )
|
|
{
|
|
global $mdb;
|
|
|
|
$db_campaign_id = (int) $db_campaign_id;
|
|
if ( $db_campaign_id <= 0 )
|
|
{
|
|
return 0;
|
|
}
|
|
|
|
$existing_id = (int) $mdb -> get( 'campaign_ad_groups', 'id', [
|
|
'AND' => [
|
|
'campaign_id' => $db_campaign_id,
|
|
'ad_group_id' => 0
|
|
]
|
|
] );
|
|
|
|
if ( $existing_id > 0 )
|
|
{
|
|
return $existing_id;
|
|
}
|
|
|
|
$mdb -> insert( 'campaign_ad_groups', [
|
|
'campaign_id' => $db_campaign_id,
|
|
'ad_group_id' => 0,
|
|
'ad_group_name' => 'PMax (bez grup reklam)',
|
|
'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_sync
|
|
] );
|
|
|
|
return (int) $mdb -> id();
|
|
}
|
|
|
|
static private function sync_campaign_keywords_for_client( $campaigns_db_map, $ad_group_db_map, $customer_id, $api, $date_sync )
|
|
{
|
|
global $mdb;
|
|
|
|
$campaign_db_ids = array_values( array_unique( array_map( 'intval', array_values( $campaigns_db_map ) ) ) );
|
|
if ( empty( $campaign_db_ids ) )
|
|
{
|
|
return [ 'count' => 0, 'errors' => [] ];
|
|
}
|
|
|
|
$keywords_30 = $api -> get_ad_keywords_30_days( $customer_id );
|
|
if ( $keywords_30 === false )
|
|
{
|
|
$last_err = \services\GoogleAdsApi::get_setting( 'google_ads_last_error' );
|
|
return [ 'count' => 0, 'errors' => [ 'Blad pobierania slow kluczowych (30 dni): ' . $last_err ] ];
|
|
}
|
|
|
|
$keywords_all_time = $api -> get_ad_keywords_all_time( $customer_id );
|
|
if ( $keywords_all_time === false )
|
|
{
|
|
$last_err = \services\GoogleAdsApi::get_setting( 'google_ads_last_error' );
|
|
return [ 'count' => 0, 'errors' => [ 'Blad pobierania slow kluczowych (all time): ' . $last_err ] ];
|
|
}
|
|
|
|
if ( !is_array( $keywords_30 ) )
|
|
{
|
|
$keywords_30 = [];
|
|
}
|
|
|
|
if ( !is_array( $keywords_all_time ) )
|
|
{
|
|
$keywords_all_time = [];
|
|
}
|
|
|
|
$map_30 = [];
|
|
foreach ( $keywords_30 as $row )
|
|
{
|
|
$campaign_external_id = isset( $row['campaign_id'] ) ? (string) $row['campaign_id'] : '';
|
|
$ad_group_external_id = isset( $row['ad_group_id'] ) ? (string) $row['ad_group_id'] : '';
|
|
$keyword_text = trim( (string) ( $row['keyword_text'] ?? '' ) );
|
|
$match_type = trim( (string) ( $row['match_type'] ?? '' ) );
|
|
|
|
if ( $campaign_external_id === '' || $ad_group_external_id === '' || $keyword_text === '' )
|
|
{
|
|
continue;
|
|
}
|
|
|
|
$map_30[ $campaign_external_id . '|' . $ad_group_external_id . '|' . strtolower( $keyword_text ) . '|' . strtolower( $match_type ) ] = $row;
|
|
}
|
|
|
|
$map_all_time = [];
|
|
foreach ( $keywords_all_time as $row )
|
|
{
|
|
$campaign_external_id = isset( $row['campaign_id'] ) ? (string) $row['campaign_id'] : '';
|
|
$ad_group_external_id = isset( $row['ad_group_id'] ) ? (string) $row['ad_group_id'] : '';
|
|
$keyword_text = trim( (string) ( $row['keyword_text'] ?? '' ) );
|
|
$match_type = trim( (string) ( $row['match_type'] ?? '' ) );
|
|
|
|
if ( $campaign_external_id === '' || $ad_group_external_id === '' || $keyword_text === '' )
|
|
{
|
|
continue;
|
|
}
|
|
|
|
$map_all_time[ $campaign_external_id . '|' . $ad_group_external_id . '|' . strtolower( $keyword_text ) . '|' . strtolower( $match_type ) ] = $row;
|
|
}
|
|
|
|
$mdb -> delete( 'campaign_keywords', [ 'campaign_id' => $campaign_db_ids ] );
|
|
|
|
$keys = array_values( array_unique( array_merge( array_keys( $map_30 ), array_keys( $map_all_time ) ) ) );
|
|
$count = 0;
|
|
|
|
foreach ( $keys as $key )
|
|
{
|
|
$parts = explode( '|', $key, 4 );
|
|
$campaign_external_id = $parts[0] ?? '';
|
|
$ad_group_external_id = $parts[1] ?? '';
|
|
|
|
$db_campaign_id = (int) ( $campaigns_db_map[ $campaign_external_id ] ?? 0 );
|
|
$db_ad_group_id = (int) ( $ad_group_db_map[ $campaign_external_id . '|' . $ad_group_external_id ] ?? 0 );
|
|
|
|
if ( $db_campaign_id <= 0 || $db_ad_group_id <= 0 )
|
|
{
|
|
continue;
|
|
}
|
|
|
|
$row_30 = $map_30[ $key ] ?? [];
|
|
$row_all_time = $map_all_time[ $key ] ?? [];
|
|
|
|
$keyword_text = trim( (string) ( $row_30['keyword_text'] ?? ( $row_all_time['keyword_text'] ?? '' ) ) );
|
|
if ( $keyword_text === '' )
|
|
{
|
|
continue;
|
|
}
|
|
|
|
$match_type = trim( (string) ( $row_30['match_type'] ?? ( $row_all_time['match_type'] ?? '' ) ) );
|
|
$status = trim( (string) ( $row_all_time['status'] ?? ( $row_30['status'] ?? 'ENABLED' ) ) );
|
|
$clicks_30 = (int) ( $row_30['clicks'] ?? 0 );
|
|
$clicks_all_time = (int) ( $row_all_time['clicks'] ?? 0 );
|
|
|
|
if ( $clicks_30 <= 0 && $clicks_all_time <= 0 )
|
|
{
|
|
continue;
|
|
}
|
|
|
|
$mdb -> insert( 'campaign_keywords', [
|
|
'campaign_id' => $db_campaign_id,
|
|
'ad_group_id' => $db_ad_group_id,
|
|
'keyword_text' => $keyword_text,
|
|
'match_type' => $match_type,
|
|
'status' => $status,
|
|
'impressions_30' => (int) ( $row_30['impressions'] ?? 0 ),
|
|
'clicks_30' => $clicks_30,
|
|
'cost_30' => (float) ( $row_30['cost'] ?? 0 ),
|
|
'conversions_30' => (float) ( $row_30['conversions'] ?? 0 ),
|
|
'conversion_value_30' => (float) ( $row_30['conversion_value'] ?? 0 ),
|
|
'roas_30' => (float) ( $row_30['roas'] ?? 0 ),
|
|
'impressions_all_time' => (int) ( $row_all_time['impressions'] ?? 0 ),
|
|
'clicks_all_time' => $clicks_all_time,
|
|
'cost_all_time' => (float) ( $row_all_time['cost'] ?? 0 ),
|
|
'conversions_all_time' => (float) ( $row_all_time['conversions'] ?? 0 ),
|
|
'conversion_value_all_time' => (float) ( $row_all_time['conversion_value'] ?? 0 ),
|
|
'roas_all_time' => (float) ( $row_all_time['roas'] ?? 0 ),
|
|
'date_sync' => $date_sync
|
|
] );
|
|
|
|
$count++;
|
|
}
|
|
|
|
return [ 'count' => $count, 'errors' => [] ];
|
|
}
|
|
|
|
static private function sync_campaign_negative_keywords_for_client( $campaigns_db_map, $ad_group_db_map, $customer_id, $api, $date_sync )
|
|
{
|
|
global $mdb;
|
|
|
|
$campaign_db_ids = array_values( array_unique( array_map( 'intval', array_values( $campaigns_db_map ) ) ) );
|
|
if ( empty( $campaign_db_ids ) )
|
|
{
|
|
return [ 'count' => 0, 'errors' => [] ];
|
|
}
|
|
|
|
$negatives = $api -> get_negative_keywords( $customer_id );
|
|
if ( $negatives === false )
|
|
{
|
|
$last_err = \services\GoogleAdsApi::get_setting( 'google_ads_last_error' );
|
|
return [ 'count' => 0, 'errors' => [ 'Blad pobierania fraz wykluczajacych: ' . $last_err ] ];
|
|
}
|
|
|
|
if ( !is_array( $negatives ) )
|
|
{
|
|
$negatives = [];
|
|
}
|
|
|
|
$mdb -> delete( 'campaign_negative_keywords', [ 'campaign_id' => $campaign_db_ids ] );
|
|
|
|
$count = 0;
|
|
$seen = [];
|
|
|
|
foreach ( $negatives as $row )
|
|
{
|
|
$campaign_external_id = isset( $row['campaign_id'] ) ? (string) $row['campaign_id'] : '';
|
|
$db_campaign_id = (int) ( $campaigns_db_map[ $campaign_external_id ] ?? 0 );
|
|
if ( $db_campaign_id <= 0 )
|
|
{
|
|
continue;
|
|
}
|
|
|
|
$scope = ( $row['scope'] ?? '' ) === 'ad_group' ? 'ad_group' : 'campaign';
|
|
$db_ad_group_id = null;
|
|
|
|
if ( $scope === 'ad_group' )
|
|
{
|
|
$ad_group_external_id = isset( $row['ad_group_id'] ) ? (string) $row['ad_group_id'] : '';
|
|
$mapped_ad_group_id = (int) ( $ad_group_db_map[ $campaign_external_id . '|' . $ad_group_external_id ] ?? 0 );
|
|
if ( $mapped_ad_group_id <= 0 )
|
|
{
|
|
continue;
|
|
}
|
|
$db_ad_group_id = $mapped_ad_group_id;
|
|
}
|
|
|
|
$keyword_text = trim( (string) ( $row['keyword_text'] ?? '' ) );
|
|
if ( $keyword_text === '' )
|
|
{
|
|
continue;
|
|
}
|
|
|
|
$match_type = trim( (string) ( $row['match_type'] ?? '' ) );
|
|
$uniq_key = $db_campaign_id . '|' . (int) $db_ad_group_id . '|' . $scope . '|' . strtolower( $keyword_text ) . '|' . strtolower( $match_type );
|
|
|
|
if ( isset( $seen[ $uniq_key ] ) )
|
|
{
|
|
continue;
|
|
}
|
|
$seen[ $uniq_key ] = true;
|
|
|
|
$mdb -> insert( 'campaign_negative_keywords', [
|
|
'campaign_id' => $db_campaign_id,
|
|
'ad_group_id' => $db_ad_group_id,
|
|
'scope' => $scope,
|
|
'keyword_text' => $keyword_text,
|
|
'match_type' => $match_type,
|
|
'date_sync' => $date_sync
|
|
] );
|
|
|
|
$count++;
|
|
}
|
|
|
|
return [ 'count' => $count, 'errors' => [] ];
|
|
}
|
|
|
|
static private function pick_clients_batch_by_cursor( $clients, $limit, $cursor_client_id = 0 )
|
|
{
|
|
$clients = is_array( $clients ) ? array_values( $clients ) : [];
|
|
if ( empty( $clients ) )
|
|
{
|
|
return [];
|
|
}
|
|
|
|
$limit = max( 1, (int) $limit );
|
|
$total = count( $clients );
|
|
if ( $limit >= $total )
|
|
{
|
|
return $clients;
|
|
}
|
|
|
|
$start_index = 0;
|
|
$cursor_client_id = (int) $cursor_client_id;
|
|
if ( $cursor_client_id > 0 )
|
|
{
|
|
$found_next = false;
|
|
foreach ( $clients as $idx => $client )
|
|
{
|
|
$current_id = (int) ( $client['id'] ?? 0 );
|
|
if ( $current_id > $cursor_client_id )
|
|
{
|
|
$start_index = $idx;
|
|
$found_next = true;
|
|
break;
|
|
}
|
|
}
|
|
|
|
if ( !$found_next )
|
|
{
|
|
$start_index = 0;
|
|
}
|
|
}
|
|
|
|
$batch = [];
|
|
for ( $i = 0; $i < $limit; $i++ )
|
|
{
|
|
$batch[] = $clients[( $start_index + $i ) % $total];
|
|
}
|
|
|
|
return $batch;
|
|
}
|
|
|
|
// ===========================
|
|
// CRON SYNC STATUS HELPERS
|
|
// ===========================
|
|
|
|
static private function ensure_sync_rows( $pipeline, $sync_dates, $client_ids )
|
|
{
|
|
global $mdb;
|
|
|
|
if ( empty( $client_ids ) || empty( $sync_dates ) )
|
|
{
|
|
return;
|
|
}
|
|
|
|
$stmt = $mdb -> pdo -> prepare(
|
|
"INSERT INTO cron_sync_status (client_id, pipeline, sync_date, phase)
|
|
VALUES (:client_id, :pipeline, :sync_date, 'pending')
|
|
ON DUPLICATE KEY UPDATE
|
|
phase = IF(phase = 'done' AND completed_at < CURDATE(), 'pending', phase),
|
|
started_at = IF(phase = 'done' AND completed_at < CURDATE(), NULL, started_at),
|
|
completed_at = IF(phase = 'done' AND completed_at < CURDATE(), NULL, completed_at),
|
|
error_message = IF(phase = 'done' AND completed_at < CURDATE(), NULL, error_message)"
|
|
);
|
|
|
|
foreach ( $sync_dates as $date )
|
|
{
|
|
foreach ( $client_ids as $cid )
|
|
{
|
|
$stmt -> execute( [
|
|
':client_id' => (int) $cid,
|
|
':pipeline' => $pipeline,
|
|
':sync_date' => $date
|
|
] );
|
|
}
|
|
}
|
|
}
|
|
|
|
static private function get_active_sync_date( $pipeline )
|
|
{
|
|
global $mdb;
|
|
|
|
$row = $mdb -> query(
|
|
"SELECT sync_date FROM cron_sync_status WHERE pipeline = :pipeline AND phase != 'done' AND sync_date >= DATE_SUB(CURDATE(), INTERVAL 90 DAY) ORDER BY sync_date ASC LIMIT 1",
|
|
[ ':pipeline' => $pipeline ]
|
|
) -> fetch( \PDO::FETCH_ASSOC );
|
|
|
|
return $row ? $row['sync_date'] : null;
|
|
}
|
|
|
|
static private function get_pending_clients( $pipeline, $sync_date, $source_phase, $limit )
|
|
{
|
|
global $mdb;
|
|
|
|
$rows = $mdb -> query(
|
|
"SELECT cs.client_id FROM cron_sync_status cs INNER JOIN clients c ON cs.client_id = c.id AND c.deleted = 0 WHERE cs.pipeline = :pipeline AND cs.sync_date = :sync_date AND cs.phase = :phase ORDER BY cs.client_id ASC LIMIT " . (int) $limit,
|
|
[ ':pipeline' => $pipeline, ':sync_date' => $sync_date, ':phase' => $source_phase ]
|
|
) -> fetchAll( \PDO::FETCH_COLUMN );
|
|
|
|
return is_array( $rows ) ? array_map( 'intval', $rows ) : [];
|
|
}
|
|
|
|
static private function mark_sync_phase( $pipeline, $sync_date, $client_id, $new_phase, $error_msg = null )
|
|
{
|
|
global $mdb;
|
|
|
|
$update_data = [ 'phase' => $new_phase ];
|
|
|
|
if ( $new_phase === 'done' )
|
|
{
|
|
$update_data['completed_at'] = date( 'Y-m-d H:i:s' );
|
|
}
|
|
else if ( $new_phase !== 'pending' )
|
|
{
|
|
$update_data['started_at'] = date( 'Y-m-d H:i:s' );
|
|
}
|
|
|
|
if ( $error_msg !== null )
|
|
{
|
|
$update_data['error_message'] = $error_msg;
|
|
}
|
|
|
|
$mdb -> update( 'cron_sync_status', $update_data, [
|
|
'AND' => [
|
|
'client_id' => (int) $client_id,
|
|
'pipeline' => $pipeline,
|
|
'sync_date' => $sync_date
|
|
]
|
|
] );
|
|
}
|
|
|
|
static private function determine_products_phase( $sync_date )
|
|
{
|
|
global $mdb;
|
|
|
|
$phases_order = [ 'pending', 'fetch', 'aggregate_30' ];
|
|
|
|
foreach ( $phases_order as $phase )
|
|
{
|
|
$count = (int) $mdb -> query(
|
|
"SELECT COUNT(*) FROM cron_sync_status cs INNER JOIN clients c ON cs.client_id = c.id AND c.deleted = 0 WHERE cs.pipeline = 'products' AND cs.sync_date = :sync_date AND cs.phase = :phase",
|
|
[ ':sync_date' => $sync_date, ':phase' => $phase ]
|
|
) -> fetchColumn();
|
|
|
|
if ( $count > 0 )
|
|
{
|
|
return $phase;
|
|
}
|
|
}
|
|
|
|
return null;
|
|
}
|
|
|
|
static private function get_active_client( $pipeline )
|
|
{
|
|
global $mdb;
|
|
$clients_not_deleted_sql_c = self::sql_clients_not_deleted( 'c' );
|
|
|
|
$row = $mdb -> query(
|
|
"SELECT DISTINCT cs.client_id
|
|
FROM cron_sync_status cs
|
|
INNER JOIN clients c ON cs.client_id = c.id AND " . $clients_not_deleted_sql_c . "
|
|
WHERE cs.pipeline = :pipeline
|
|
AND cs.phase != 'done'
|
|
AND cs.sync_date >= DATE_SUB(CURDATE(), INTERVAL 90 DAY)
|
|
ORDER BY cs.client_id ASC
|
|
LIMIT 1",
|
|
[ ':pipeline' => $pipeline ]
|
|
) -> fetch( \PDO::FETCH_ASSOC );
|
|
|
|
return $row ? (int) $row['client_id'] : null;
|
|
}
|
|
|
|
static private function get_pending_dates_for_client( $pipeline, $client_id, $phase, $limit )
|
|
{
|
|
global $mdb;
|
|
|
|
$rows = $mdb -> query(
|
|
"SELECT cs.sync_date
|
|
FROM cron_sync_status cs
|
|
WHERE cs.pipeline = :pipeline
|
|
AND cs.client_id = :client_id
|
|
AND cs.phase = :phase
|
|
AND cs.sync_date >= DATE_SUB(CURDATE(), INTERVAL 90 DAY)
|
|
ORDER BY cs.sync_date ASC
|
|
LIMIT " . (int) $limit,
|
|
[ ':pipeline' => $pipeline, ':client_id' => (int) $client_id, ':phase' => $phase ]
|
|
) -> fetchAll( \PDO::FETCH_COLUMN );
|
|
|
|
return is_array( $rows ) ? $rows : [];
|
|
}
|
|
|
|
static private function determine_client_products_phase( $client_id )
|
|
{
|
|
global $mdb;
|
|
|
|
$phases_order = [ 'pending', 'fetch', 'aggregate_30' ];
|
|
|
|
foreach ( $phases_order as $phase )
|
|
{
|
|
$count = (int) $mdb -> query(
|
|
"SELECT COUNT(*) FROM cron_sync_status cs
|
|
INNER JOIN clients c ON cs.client_id = c.id AND c.deleted = 0
|
|
WHERE cs.pipeline = 'products'
|
|
AND cs.client_id = :client_id
|
|
AND cs.phase = :phase
|
|
AND cs.sync_date >= DATE_SUB(CURDATE(), INTERVAL 90 DAY)",
|
|
[ ':client_id' => (int) $client_id, ':phase' => $phase ]
|
|
) -> fetchColumn();
|
|
|
|
if ( $count > 0 )
|
|
{
|
|
return $phase;
|
|
}
|
|
}
|
|
|
|
return null;
|
|
}
|
|
|
|
static public function cron_facebook_ads()
|
|
{
|
|
self::$current_cron_action = __FUNCTION__;
|
|
self::touch_cron_invocation( __FUNCTION__ );
|
|
self::output_cron_response( self::run_facebook_ads_sync_payload( (int) \S::get( 'client_id' ) ) );
|
|
}
|
|
|
|
static private function run_facebook_ads_sync_payload( $requested_client_id = 0 )
|
|
{
|
|
global $settings;
|
|
|
|
$token_db = trim( (string) \services\FacebookAdsApi::get_setting( 'facebook_ads_token' ) );
|
|
$token = $token_db !== '' ? $token_db : trim( (string) ( $settings['facebook_ads_token'] ?? '' ) );
|
|
$api_version = trim( (string) ( $settings['facebook_ads_api_version'] ?? 'v25.0' ) ) ?: 'v25.0';
|
|
$requested_client_id = (int) $requested_client_id;
|
|
$client_id = $requested_client_id;
|
|
$forced_client_id = 0;
|
|
$is_forced_refresh = false;
|
|
$days = 30;
|
|
$until = date( 'Y-m-d', strtotime( '-1 day' ) );
|
|
$since = date( 'Y-m-d', strtotime( $until . ' -29 days' ) );
|
|
|
|
if ( $client_id <= 0 )
|
|
{
|
|
$forced_client_id = (int) \services\FacebookAdsApi::get_setting( 'cron_facebook_ads_force_client_id' );
|
|
if ( $forced_client_id > 0 )
|
|
{
|
|
$client_id = $forced_client_id;
|
|
$is_forced_refresh = true;
|
|
}
|
|
}
|
|
|
|
if ( $token === '' )
|
|
{
|
|
return [
|
|
'result' => 'Brak tokenu Facebook Ads. Skonfiguruj go w Ustawieniach.',
|
|
'success' => false
|
|
];
|
|
}
|
|
|
|
$clients = \factory\FacebookAds::get_clients_for_sync( $client_id );
|
|
if ( empty( $clients ) )
|
|
{
|
|
if ( $is_forced_refresh )
|
|
{
|
|
\services\FacebookAdsApi::set_setting( 'facebook_ads_last_error', 'Wymuszone odswiezenie FB nie moze wystartowac: klient nie jest aktywny lub nie ma Facebook Ads Account ID.' );
|
|
|
|
return [
|
|
'result' => 'Wymuszone odswiezenie Facebook Ads nie zostalo wykonane (sprawdz aktywnosc klienta i Facebook Ads Account ID).',
|
|
'success' => false,
|
|
'processed_clients' => 0,
|
|
'failed_clients' => 1,
|
|
'days' => $days,
|
|
'errors' => [ 'Brak aktywnego klienta z ustawionym facebook_ads_account_id dla wymuszonego odswiezenia.' ]
|
|
];
|
|
}
|
|
|
|
if ( $requested_client_id > 0 )
|
|
{
|
|
return [
|
|
'result' => 'Nie znaleziono aktywnego klienta z ustawionym facebook_ads_account_id dla podanego ID.',
|
|
'success' => false,
|
|
'processed_clients' => 0,
|
|
'failed_clients' => 1,
|
|
'days' => $days,
|
|
'errors' => [ 'Brak aktywnego klienta z facebook_ads_account_id dla ID=' . $requested_client_id . '.' ]
|
|
];
|
|
}
|
|
|
|
return [
|
|
'result' => 'Brak aktywnych klientow z ustawionym facebook_ads_account_id.',
|
|
'success' => true,
|
|
'processed_clients' => 0,
|
|
'days' => $days
|
|
];
|
|
}
|
|
|
|
// Blokada ponownego pobrania w tym samym dniu (chyba ze wymuszone lub konkretny klient)
|
|
$is_incremental_sync = false;
|
|
if ( !$is_forced_refresh && $requested_client_id <= 0 )
|
|
{
|
|
$last_synced_date = (string) \services\FacebookAdsApi::get_setting( 'cron_facebook_ads_last_active_date' );
|
|
if ( $last_synced_date === $until )
|
|
{
|
|
// Sprawdz czy pojawili sie nowi klienci nieobecni w ostatniej synchronizacji
|
|
$last_ids_str = (string) \services\FacebookAdsApi::get_setting( 'cron_facebook_ads_last_synced_client_ids' );
|
|
$last_ids = array_filter( array_map( 'intval', explode( ',', $last_ids_str ) ) );
|
|
$current_ids = array_map( function ( $c ) { return (int) ( $c['id'] ?? 0 ); }, $clients );
|
|
$new_ids = array_values( array_diff( $current_ids, $last_ids ) );
|
|
|
|
if ( empty( $new_ids ) )
|
|
{
|
|
return [
|
|
'result' => 'Synchronizacja Facebook Ads juz wykonana dzisiaj (' . $since . ' - ' . $until . ').',
|
|
'success' => true,
|
|
'skipped' => true,
|
|
'days' => $days,
|
|
'since' => $since,
|
|
'until' => $until,
|
|
'last_synced_date' => $last_synced_date
|
|
];
|
|
}
|
|
|
|
// Sa nowi klienci - ogranicz synchronizacje tylko do nich
|
|
$clients = array_values( array_filter( $clients, function ( $c ) use ( $new_ids )
|
|
{
|
|
return in_array( (int) ( $c['id'] ?? 0 ), $new_ids );
|
|
} ) );
|
|
$is_incremental_sync = true;
|
|
}
|
|
}
|
|
|
|
$api = new \services\FacebookAdsApi( $token, $api_version );
|
|
if ( !$api -> is_configured() )
|
|
{
|
|
return [
|
|
'result' => 'Facebook Ads API nie jest skonfigurowane.',
|
|
'success' => false
|
|
];
|
|
}
|
|
|
|
$items = [];
|
|
$processed = 0;
|
|
$success_count = 0;
|
|
$failed_count = 0;
|
|
|
|
$totals = [
|
|
'campaigns_synced' => 0,
|
|
'campaign_history_synced' => 0,
|
|
'ad_sets_synced' => 0,
|
|
'ad_set_history_synced' => 0,
|
|
'ads_synced' => 0,
|
|
'ad_history_synced' => 0
|
|
];
|
|
|
|
$errors = [];
|
|
|
|
foreach ( $clients as $client )
|
|
{
|
|
$sync = \factory\FacebookAds::sync_date_range_for_client( $client, $api, $since, $until, true );
|
|
$processed++;
|
|
|
|
if ( !empty( $sync['success'] ) )
|
|
{
|
|
$success_count++;
|
|
}
|
|
else
|
|
{
|
|
$failed_count++;
|
|
}
|
|
|
|
foreach ( array_keys( $totals ) as $key )
|
|
{
|
|
$totals[ $key ] += (int) ( $sync[ $key ] ?? 0 );
|
|
}
|
|
|
|
$client_errors = (array) ( $sync['errors'] ?? [] );
|
|
foreach ( $client_errors as $error_text )
|
|
{
|
|
$error_text = trim( (string) $error_text );
|
|
if ( $error_text === '' )
|
|
{
|
|
continue;
|
|
}
|
|
|
|
$errors[] = 'Client ID ' . (int) ( $client['id'] ?? 0 ) . ': ' . $error_text;
|
|
}
|
|
|
|
$items[] = [
|
|
'client_id' => (int) ( $client['id'] ?? 0 ),
|
|
'client_name' => (string) ( $client['name'] ?? '' ),
|
|
'account_id' => (string) ( $sync['account_id'] ?? ( $client['facebook_ads_account_id'] ?? '' ) ),
|
|
'success' => !empty( $sync['success'] ),
|
|
'campaigns_synced' => (int) ( $sync['campaigns_synced'] ?? 0 ),
|
|
'campaign_history_synced' => (int) ( $sync['campaign_history_synced'] ?? 0 ),
|
|
'ad_sets_synced' => (int) ( $sync['ad_sets_synced'] ?? 0 ),
|
|
'ad_set_history_synced' => (int) ( $sync['ad_set_history_synced'] ?? 0 ),
|
|
'ads_synced' => (int) ( $sync['ads_synced'] ?? 0 ),
|
|
'ad_history_synced' => (int) ( $sync['ad_history_synced'] ?? 0 ),
|
|
'all_time_debug' => $sync['all_time_debug'] ?? null,
|
|
'errors' => $client_errors
|
|
];
|
|
}
|
|
|
|
\services\FacebookAdsApi::set_setting( 'cron_facebook_ads_last_processed_clients', (string) $processed );
|
|
\services\FacebookAdsApi::set_setting( 'cron_facebook_ads_last_success_clients', (string) $success_count );
|
|
\services\FacebookAdsApi::set_setting( 'cron_facebook_ads_last_failed_clients', (string) $failed_count );
|
|
\services\FacebookAdsApi::set_setting( 'cron_facebook_ads_last_days', (string) $days );
|
|
\services\FacebookAdsApi::set_setting( 'cron_facebook_ads_last_run_at', date( 'Y-m-d H:i:s' ) );
|
|
\services\FacebookAdsApi::set_setting( 'cron_facebook_ads_last_totals_json', json_encode( $totals, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES ) );
|
|
if ( $failed_count === 0 )
|
|
{
|
|
\services\FacebookAdsApi::set_setting( 'cron_facebook_ads_last_success_at', date( 'Y-m-d H:i:s' ) );
|
|
}
|
|
if ( $is_incremental_sync )
|
|
{
|
|
$prev_ids_str = (string) \services\FacebookAdsApi::get_setting( 'cron_facebook_ads_last_synced_client_ids' );
|
|
$prev_ids = array_filter( array_map( 'intval', explode( ',', $prev_ids_str ) ) );
|
|
$synced_ids = array_map( function ( $item ) { return (int) ( $item['client_id'] ?? 0 ); }, $items );
|
|
$all_ids = array_values( array_unique( array_merge( $prev_ids, $synced_ids ) ) );
|
|
\services\FacebookAdsApi::set_setting( 'cron_facebook_ads_last_synced_client_ids', implode( ',', $all_ids ) );
|
|
}
|
|
else
|
|
{
|
|
$synced_ids = array_map( function ( $item ) { return (int) ( $item['client_id'] ?? 0 ); }, $items );
|
|
\services\FacebookAdsApi::set_setting( 'cron_facebook_ads_last_synced_client_ids', implode( ',', $synced_ids ) );
|
|
}
|
|
\services\FacebookAdsApi::set_setting( 'cron_facebook_ads_last_active_date', $until );
|
|
|
|
if ( $forced_client_id > 0 )
|
|
{
|
|
if ( $failed_count === 0 )
|
|
{
|
|
\services\FacebookAdsApi::set_setting( 'cron_facebook_ads_force_client_id', '0' );
|
|
\services\FacebookAdsApi::set_setting( 'cron_facebook_ads_force_requested_at', '' );
|
|
\services\FacebookAdsApi::set_setting( 'cron_facebook_ads_last_forced_client_id', (string) $forced_client_id );
|
|
}
|
|
else
|
|
{
|
|
\services\FacebookAdsApi::set_setting( 'facebook_ads_last_error', 'Wymuszone odswiezenie FB nie zakonczone: brak pobranych aktywnych danych.' );
|
|
}
|
|
}
|
|
|
|
$result_text = $failed_count > 0
|
|
? 'Synchronizacja Facebook Ads zakonczona z bledami.'
|
|
: 'Synchronizacja Facebook Ads zakonczona (' . $since . ' - ' . $until . ').';
|
|
|
|
return [
|
|
'result' => $result_text,
|
|
'success' => $failed_count === 0,
|
|
'days' => $days,
|
|
'since' => $since,
|
|
'until' => $until,
|
|
'active_only' => 1,
|
|
'api_version' => $api -> get_api_version(),
|
|
'window_completed' => 1,
|
|
'processed_clients' => $processed,
|
|
'success_clients' => $success_count,
|
|
'failed_clients' => $failed_count,
|
|
'totals' => $totals,
|
|
'clients' => $items,
|
|
'errors' => $errors
|
|
];
|
|
}
|
|
|
|
static private function cleanup_old_sync_rows( $days = 30 )
|
|
{
|
|
global $mdb;
|
|
$clients_deleted_sql_c = self::sql_clients_deleted( 'c' );
|
|
|
|
$mdb -> query(
|
|
"DELETE FROM cron_sync_status WHERE phase = 'done' AND completed_at < DATE_SUB(NOW(), INTERVAL :days DAY)",
|
|
[ ':days' => (int) $days ]
|
|
);
|
|
|
|
$mdb -> query(
|
|
"DELETE cs
|
|
FROM cron_sync_status cs
|
|
LEFT JOIN clients c ON cs.client_id = c.id
|
|
WHERE c.id IS NULL OR " . $clients_deleted_sql_c
|
|
);
|
|
}
|
|
|
|
static private function get_conversion_window_days( $prefer_config = false )
|
|
{
|
|
global $settings;
|
|
|
|
$request_value = (int) \S::get( 'conversion_window_days' );
|
|
if ( $request_value > 0 )
|
|
{
|
|
return min( 90, $request_value );
|
|
}
|
|
|
|
if ( $prefer_config )
|
|
{
|
|
$config_value = (int) ( $settings['google_ads_conversion_window_days'] ?? 0 );
|
|
if ( $config_value > 0 )
|
|
{
|
|
return min( 90, $config_value );
|
|
}
|
|
}
|
|
|
|
$setting_value = (int) self::get_setting_value( 'google_ads_conversion_window_days', 7 );
|
|
if ( $setting_value <= 0 )
|
|
{
|
|
$config_value = (int) ( $settings['google_ads_conversion_window_days'] ?? 7 );
|
|
if ( $config_value <= 0 )
|
|
{
|
|
$config_value = 7;
|
|
}
|
|
return min( 90, $config_value );
|
|
}
|
|
|
|
return min( 90, $setting_value );
|
|
}
|
|
|
|
static private function get_facebook_conversion_window_days( $prefer_config = false )
|
|
{
|
|
global $settings;
|
|
|
|
$request_value = (int) \S::get( 'facebook_conversion_window_days' );
|
|
if ( $request_value <= 0 )
|
|
{
|
|
$request_value = (int) \S::get( 'days' );
|
|
}
|
|
if ( $request_value <= 0 )
|
|
{
|
|
$request_value = (int) \S::get( 'conversion_window_days' );
|
|
}
|
|
|
|
if ( $request_value > 0 )
|
|
{
|
|
return min( 90, $request_value );
|
|
}
|
|
|
|
if ( $prefer_config )
|
|
{
|
|
$config_value = (int) ( $settings['facebook_ads_conversion_window_days'] ?? 0 );
|
|
if ( $config_value > 0 )
|
|
{
|
|
return min( 90, $config_value );
|
|
}
|
|
}
|
|
|
|
$setting_value = (int) self::get_setting_value( 'facebook_ads_conversion_window_days', 30 );
|
|
if ( $setting_value <= 0 )
|
|
{
|
|
$config_value = (int) ( $settings['facebook_ads_conversion_window_days'] ?? 30 );
|
|
if ( $config_value <= 0 )
|
|
{
|
|
$config_value = 30;
|
|
}
|
|
|
|
return min( 90, $config_value );
|
|
}
|
|
|
|
return min( 90, $setting_value );
|
|
}
|
|
|
|
static private function cleanup_pipeline_rows_outside_window( $pipeline, $sync_dates )
|
|
{
|
|
global $mdb;
|
|
|
|
$pipeline = trim( (string) $pipeline );
|
|
if ( $pipeline === '' )
|
|
{
|
|
return;
|
|
}
|
|
|
|
$sync_dates = array_values( array_filter( array_map( function( $item )
|
|
{
|
|
$date = date( 'Y-m-d', strtotime( (string) $item ) );
|
|
return $date ?: null;
|
|
}, (array) $sync_dates ) ) );
|
|
|
|
if ( empty( $sync_dates ) )
|
|
{
|
|
return;
|
|
}
|
|
|
|
$quoted_dates = array_map( function( $d ) use ( $mdb )
|
|
{
|
|
return $mdb -> pdo -> quote( $d );
|
|
}, $sync_dates );
|
|
|
|
$mdb -> query(
|
|
"DELETE FROM cron_sync_status
|
|
WHERE pipeline = :pipeline
|
|
AND sync_date NOT IN (" . implode( ',', $quoted_dates ) . ")",
|
|
[ ':pipeline' => $pipeline ]
|
|
);
|
|
}
|
|
|
|
static private function build_backfill_dates( $end_date, $window_days )
|
|
{
|
|
$end_timestamp = strtotime( $end_date );
|
|
if ( !$end_timestamp )
|
|
{
|
|
$end_timestamp = strtotime( date( 'Y-m-d' ) );
|
|
}
|
|
|
|
$window_days = max( 1, min( 90, (int) $window_days ) );
|
|
$dates = [];
|
|
|
|
for ( $i = $window_days - 1; $i >= 0; $i-- )
|
|
{
|
|
$dates[] = date( 'Y-m-d', strtotime( '-' . $i . ' days', $end_timestamp ) );
|
|
}
|
|
|
|
return $dates;
|
|
}
|
|
|
|
static private function is_debug_requested()
|
|
{
|
|
$raw = \S::get( 'debug' );
|
|
|
|
if ( is_bool( $raw ) )
|
|
{
|
|
return $raw;
|
|
}
|
|
|
|
$value = strtolower( trim( (string) $raw ) );
|
|
return in_array( $value, [ '1', 'true', 'yes', 'on' ], true );
|
|
}
|
|
|
|
static private function output_cron_response( $payload )
|
|
{
|
|
$payload = is_array( $payload ) ? $payload : [ 'result' => (string) $payload ];
|
|
|
|
// --- Logowanie do tabeli logs ---
|
|
try
|
|
{
|
|
$has_errors = !empty( $payload['errors'] );
|
|
$log_level = $has_errors ? 'error' : 'info';
|
|
$log_source = self::$current_cron_action ?? 'cron';
|
|
$log_client_id = $payload['active_client_id'] ?? $payload['client_id'] ?? null;
|
|
$log_message = (string) ( $payload['result'] ?? 'Brak komunikatu' );
|
|
|
|
\factory\Logs::add( $log_level, $log_source, $log_message, $payload, $log_client_id );
|
|
\factory\Logs::cleanup_old( 30 );
|
|
}
|
|
catch ( \Throwable $e )
|
|
{
|
|
$payload['_log_error'] = $e -> getMessage();
|
|
}
|
|
|
|
if ( self::is_debug_requested() )
|
|
{
|
|
header( 'Content-Type: text/html; charset=utf-8' );
|
|
$pretty = json_encode( $payload, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES | JSON_PRETTY_PRINT );
|
|
if ( $pretty === false )
|
|
{
|
|
$pretty = '{}';
|
|
}
|
|
|
|
$result_text = htmlspecialchars( (string) ( $payload['result'] ?? 'Brak komunikatu' ), ENT_QUOTES, 'UTF-8' );
|
|
$active_client_id = isset( $payload['active_client_id'] ) ? (string) $payload['active_client_id'] : ( isset( $payload['client_id'] ) ? (string) $payload['client_id'] : '-' );
|
|
$active_date = htmlspecialchars( (string) ( $payload['active_date'] ?? ( $payload['date'] ?? '-' ) ), ENT_QUOTES, 'UTF-8' );
|
|
$errors_count = is_array( $payload['errors'] ?? null ) ? count( $payload['errors'] ) : 0;
|
|
|
|
echo '<!doctype html><html lang="pl"><head><meta charset="utf-8"><title>CRON debug</title>';
|
|
echo '<style>body{font-family:Arial,sans-serif;background:#f6f8fb;color:#1f2937;margin:0;padding:24px;}';
|
|
echo '.card{background:#fff;border:1px solid #dbe3ee;border-radius:10px;padding:16px;margin-bottom:14px;}';
|
|
echo 'h1{font-size:20px;margin:0 0 12px 0;} .meta{display:grid;grid-template-columns:180px 1fr;gap:8px 12px;font-size:14px;}';
|
|
echo 'code,pre{font-family:Consolas,Monaco,monospace;} pre{background:#0b1020;color:#e5e7eb;padding:14px;border-radius:8px;overflow:auto;font-size:12px;}';
|
|
echo '.ok{color:#0f766e;font-weight:700;} .err{color:#b91c1c;font-weight:700;}</style></head><body>';
|
|
echo '<div class="card"><h1>CRON debug</h1><div class="meta">';
|
|
echo '<div>Wynik</div><div>' . $result_text . '</div>';
|
|
echo '<div>Klient ID</div><div>' . htmlspecialchars( $active_client_id, ENT_QUOTES, 'UTF-8' ) . '</div>';
|
|
echo '<div>Data aktywna</div><div>' . $active_date . '</div>';
|
|
echo '<div>Bledy</div><div class="' . ( $errors_count > 0 ? 'err' : 'ok' ) . '">' . $errors_count . '</div>';
|
|
echo '</div></div>';
|
|
echo '<div class="card"><pre>' . htmlspecialchars( $pretty, ENT_QUOTES, 'UTF-8' ) . '</pre></div>';
|
|
echo '</body></html>';
|
|
exit;
|
|
}
|
|
|
|
header( 'Content-Type: application/json; charset=utf-8' );
|
|
echo json_encode( $payload, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES );
|
|
exit;
|
|
}
|
|
|
|
static private function clients_has_column( $column_name )
|
|
{
|
|
global $mdb;
|
|
|
|
static $cache = [];
|
|
$column_name = trim( (string) $column_name );
|
|
if ( $column_name === '' )
|
|
{
|
|
return false;
|
|
}
|
|
|
|
if ( isset( $cache[ $column_name ] ) )
|
|
{
|
|
return (bool) $cache[ $column_name ];
|
|
}
|
|
|
|
$exists = false;
|
|
try
|
|
{
|
|
$stmt = $mdb -> pdo -> prepare( 'SHOW COLUMNS FROM clients LIKE :column_name' );
|
|
if ( $stmt )
|
|
{
|
|
$stmt -> bindValue( ':column_name', $column_name, \PDO::PARAM_STR );
|
|
if ( $stmt -> execute() )
|
|
{
|
|
$exists = (bool) $stmt -> fetch( \PDO::FETCH_ASSOC );
|
|
}
|
|
}
|
|
}
|
|
catch ( \Throwable $e )
|
|
{
|
|
$exists = false;
|
|
}
|
|
|
|
$cache[ $column_name ] = $exists ? 1 : 0;
|
|
return $exists;
|
|
}
|
|
|
|
static private function sql_clients_not_deleted( $alias = '' )
|
|
{
|
|
$alias = trim( (string) $alias );
|
|
$prefix = $alias !== '' ? $alias . '.' : '';
|
|
|
|
if ( self::clients_has_column( 'deleted' ) )
|
|
{
|
|
return 'COALESCE(' . $prefix . 'deleted, 0) = 0';
|
|
}
|
|
|
|
return '1=1';
|
|
}
|
|
|
|
static private function sql_clients_deleted( $alias = '' )
|
|
{
|
|
$alias = trim( (string) $alias );
|
|
$prefix = $alias !== '' ? $alias . '.' : '';
|
|
|
|
if ( self::clients_has_column( 'deleted' ) )
|
|
{
|
|
return 'COALESCE(' . $prefix . 'deleted, 0) = 1';
|
|
}
|
|
|
|
return '0=1';
|
|
}
|
|
|
|
static private function get_setting_value( $setting_key, $default = null )
|
|
{
|
|
global $mdb;
|
|
|
|
$value = $mdb -> get( 'settings', 'setting_value', [ 'setting_key' => $setting_key ] );
|
|
|
|
if ( $value === null || $value === false )
|
|
{
|
|
return $default;
|
|
}
|
|
|
|
return $value;
|
|
}
|
|
|
|
static private function set_setting_value( $setting_key, $setting_value )
|
|
{
|
|
global $mdb;
|
|
|
|
if ( $mdb -> count( 'settings', [ 'setting_key' => $setting_key ] ) )
|
|
{
|
|
$mdb -> update( 'settings', [ 'setting_value' => $setting_value ], [ 'setting_key' => $setting_key ] );
|
|
return;
|
|
}
|
|
|
|
$mdb -> insert( 'settings', [
|
|
'setting_key' => $setting_key,
|
|
'setting_value' => $setting_value
|
|
] );
|
|
}
|
|
|
|
static private function touch_cron_invocation( $action_name )
|
|
{
|
|
$now_timestamp = time();
|
|
$now = date( 'Y-m-d H:i:s', $now_timestamp );
|
|
$last_action_invoked_at = self::get_setting_value( 'cron_last_invoked_' . $action_name . '_at', '' );
|
|
$last_action_timestamp = strtotime( (string) $last_action_invoked_at );
|
|
|
|
if ( $last_action_timestamp )
|
|
{
|
|
$interval_seconds = $now_timestamp - $last_action_timestamp;
|
|
|
|
// Pomijamy skrajne wartosci (np. pierwsze uruchomienie po dluzszej przerwie).
|
|
if ( $interval_seconds >= 1 && $interval_seconds <= 21600 )
|
|
{
|
|
$avg_key = 'cron_avg_interval_' . $action_name . '_sec';
|
|
$samples_key = 'cron_avg_interval_' . $action_name . '_samples';
|
|
|
|
$avg_interval = (float) self::get_setting_value( $avg_key, 0 );
|
|
$samples = (int) self::get_setting_value( $samples_key, 0 );
|
|
|
|
$weight = min( 99, max( 0, $samples ) );
|
|
if ( $weight <= 0 || $avg_interval <= 0 )
|
|
{
|
|
$new_avg = (float) $interval_seconds;
|
|
$new_samples = 1;
|
|
}
|
|
else
|
|
{
|
|
$new_avg = ( ( $avg_interval * $weight ) + $interval_seconds ) / ( $weight + 1 );
|
|
$new_samples = min( 100, $weight + 1 );
|
|
}
|
|
|
|
self::set_setting_value( $avg_key, (string) round( $new_avg, 2 ) );
|
|
self::set_setting_value( $samples_key, (string) $new_samples );
|
|
self::set_setting_value( 'cron_last_interval_' . $action_name . '_sec', (string) (int) $interval_seconds );
|
|
}
|
|
}
|
|
|
|
self::set_setting_value( 'cron_last_invoked_at', $now );
|
|
self::set_setting_value( 'cron_last_invoked_' . $action_name . '_at', $now );
|
|
}
|
|
|
|
static private function format_bidding_strategy( $strategy_type, $target_roas = 0 )
|
|
{
|
|
$map = [
|
|
'MAXIMIZE_CONVERSIONS' => 'Maksymalizacja liczby konwersji',
|
|
'MAXIMIZE_CONVERSION_VALUE' => 'Maksymalizacja wartosci konwersji',
|
|
'TARGET_ROAS' => 'Docelowy ROAS',
|
|
'TARGET_CPA' => 'Docelowy CPA',
|
|
'MANUAL_CPC' => 'Reczny CPC',
|
|
'MANUAL_CPM' => 'Reczny CPM',
|
|
'TARGET_IMPRESSION_SHARE' => 'Docelowy udzial w wyswietleniach',
|
|
];
|
|
|
|
$label = $map[ $strategy_type ] ?? $strategy_type ?? 'brak';
|
|
|
|
if ( $target_roas > 0 )
|
|
{
|
|
$label .= ' | docelowy ROAS: ' . round( $target_roas * 100 ) . '%';
|
|
}
|
|
|
|
return $label;
|
|
}
|
|
|
|
|
|
// ===========================
|
|
// FRAZY - history 30
|
|
// ===========================
|
|
|
|
static public function cron_phrase_history_30_save( $phrase_id, $date_from, $date_to )
|
|
{
|
|
global $mdb;
|
|
|
|
$data = $mdb -> query( 'SELECT * FROM phrases_history WHERE phrase_id = ' . $phrase_id . ' AND date_add >= \'' . $date_from . '\' AND date_add <= \'' . $date_to . '\' ORDER BY date_add ASC' ) -> fetchAll( \PDO::FETCH_ASSOC );
|
|
|
|
// Inicjalizacja tablic do przechowywania danych
|
|
$phrases_data = [];
|
|
|
|
// Grupowanie danych wedug fraz
|
|
foreach ( $data as $entry )
|
|
{
|
|
$phrase_id = $entry['phrase_id'];
|
|
|
|
if ( !isset( $phrases_data[$phrase_id] ) )
|
|
{
|
|
$phrases_data[$phrase_id] = [
|
|
'impressions' => 0,
|
|
'clicks' => 0,
|
|
'cost' => 0.0,
|
|
'conversions' => 0,
|
|
'conversions_value' => 0.0,
|
|
'roas' => 0,
|
|
'days_counted' => []
|
|
];
|
|
}
|
|
|
|
// Sumowanie danych wedug fraz
|
|
$phrases_data[$phrase_id]['impressions'] += $entry['impressions'];
|
|
$phrases_data[$phrase_id]['clicks'] += $entry['clicks'];
|
|
$phrases_data[$phrase_id]['cost'] += $entry['cost'];
|
|
$phrases_data[$phrase_id]['conversions'] += $entry['conversions'];
|
|
$phrases_data[$phrase_id]['conversions_value'] += $entry['conversions_value'];
|
|
$phrases_data[$phrase_id]['days_counted'][] = $entry['date_add'];
|
|
}
|
|
|
|
foreach ( $phrases_data as $phrase )
|
|
{
|
|
$day_count = count( $phrase['days_counted'] );
|
|
|
|
$impressions = $phrase['impressions'] / $day_count;
|
|
$clicks = $phrase['clicks'] / $day_count;
|
|
$cost = $phrase['cost'] / $day_count;
|
|
$conversions = $phrase['conversions'] / $day_count;
|
|
$conversions_value = $phrase['conversions_value'] / $day_count;
|
|
$roas = ( $conversions_value > 0 and $cost ) ? round( $conversions_value / $cost, 2 ) * 100 : 0;
|
|
|
|
if ( $day_count > 14 )
|
|
{
|
|
if ( $mdb -> count( 'phrases_history_30', [ 'AND' => [ 'phrase_id' => $phrase_id, 'date_add' => $date_to ] ] ) > 0 )
|
|
{
|
|
$mdb -> update( 'phrases_history_30', [
|
|
'impressions' => $impressions,
|
|
'clicks' => $clicks,
|
|
'cost' => $cost,
|
|
'conversions' => $conversions,
|
|
'conversions_value' => $conversions_value,
|
|
'roas' => $roas
|
|
], [ 'AND' => [ 'phrase_id' => $phrase_id, 'date_add' => $date_to ] ] );
|
|
}
|
|
else
|
|
{
|
|
$mdb -> insert( 'phrases_history_30', [
|
|
'phrase_id' => $phrase_id,
|
|
'impressions' => $impressions,
|
|
'clicks' => $clicks,
|
|
'cost' => $cost,
|
|
'conversions' => $conversions,
|
|
'conversions_value' => $conversions_value,
|
|
'roas' => $roas,
|
|
'date_add' => $date_to
|
|
] );
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|