300 lines
10 KiB
PHP
300 lines
10 KiB
PHP
<?php
|
|
namespace services;
|
|
|
|
class SupplementalFeed
|
|
{
|
|
static private function get_client_bestseller_settings( $client_id )
|
|
{
|
|
global $mdb;
|
|
|
|
$row = $mdb -> query(
|
|
'SELECT
|
|
bestseller_roas_entry,
|
|
bestseller_roas_exit,
|
|
min_conversions,
|
|
cooldown_period
|
|
FROM clients
|
|
WHERE id = :client_id
|
|
LIMIT 1',
|
|
[ ':client_id' => (int) $client_id ]
|
|
) -> fetch( \PDO::FETCH_ASSOC );
|
|
|
|
if ( !is_array( $row ) )
|
|
{
|
|
return [
|
|
'bestseller_roas_entry' => null,
|
|
'bestseller_roas_exit' => null,
|
|
'min_conversions' => 10,
|
|
'cooldown_period' => 14
|
|
];
|
|
}
|
|
|
|
$entry_raw = trim( (string) ( $row['bestseller_roas_entry'] ?? '' ) );
|
|
$exit_raw = trim( (string) ( $row['bestseller_roas_exit'] ?? '' ) );
|
|
$min_conversions = (int) ( $row['min_conversions'] ?? 10 );
|
|
$cooldown_period = (int) ( $row['cooldown_period'] ?? 14 );
|
|
|
|
return [
|
|
'bestseller_roas_entry' => $entry_raw !== '' ? (float) $entry_raw : null,
|
|
'bestseller_roas_exit' => $exit_raw !== '' ? (float) $exit_raw : null,
|
|
'min_conversions' => $min_conversions > 0 ? $min_conversions : 10,
|
|
'cooldown_period' => $cooldown_period > 0 ? $cooldown_period : 14
|
|
];
|
|
}
|
|
|
|
static private function is_below_exit_for_cooldown( $product_id, $roas_exit, $cooldown_period )
|
|
{
|
|
global $mdb;
|
|
|
|
$cooldown_period = max( 1, (int) $cooldown_period );
|
|
$rows = $mdb -> query(
|
|
'SELECT
|
|
h30.date_add,
|
|
CASE
|
|
WHEN SUM( h30.cost ) > 0 THEN ( SUM( h30.conversions_value ) / SUM( h30.cost ) ) * 100
|
|
ELSE 0
|
|
END AS roas
|
|
FROM products_history_30 h30
|
|
WHERE h30.product_id = :product_id
|
|
GROUP BY h30.date_add
|
|
ORDER BY h30.date_add DESC
|
|
LIMIT ' . $cooldown_period,
|
|
[ ':product_id' => (int) $product_id ]
|
|
) -> fetchAll( \PDO::FETCH_ASSOC );
|
|
|
|
if ( count( (array) $rows ) < $cooldown_period )
|
|
{
|
|
return false;
|
|
}
|
|
|
|
foreach ( $rows as $row )
|
|
{
|
|
$roas = (float) ( $row['roas'] ?? 0 );
|
|
if ( $roas >= (float) $roas_exit )
|
|
{
|
|
return false;
|
|
}
|
|
}
|
|
|
|
return true;
|
|
}
|
|
|
|
static private function refresh_bestseller_labels_for_client( $client_id )
|
|
{
|
|
global $mdb;
|
|
|
|
$settings = self::get_client_bestseller_settings( $client_id );
|
|
$entry = $settings['bestseller_roas_entry'];
|
|
$exit = $settings['bestseller_roas_exit'];
|
|
$min_conversions = (float) $settings['min_conversions'];
|
|
$cooldown_period = (int) $settings['cooldown_period'];
|
|
|
|
if ( $entry === null || $exit === null )
|
|
{
|
|
return 0;
|
|
}
|
|
|
|
$products = $mdb -> query(
|
|
'SELECT
|
|
p.id,
|
|
p.custom_label_4,
|
|
COALESCE( SUM( pa.cost_30 ), 0 ) AS cost_30,
|
|
COALESCE( SUM( pa.conversion_value_30 ), 0 ) AS conversion_value_30,
|
|
COALESCE( SUM( pa.conversions_30 ), 0 ) AS conversions_30
|
|
FROM products p
|
|
LEFT JOIN products_aggregate pa ON pa.product_id = p.id
|
|
WHERE p.client_id = :client_id
|
|
GROUP BY p.id, p.custom_label_4',
|
|
[ ':client_id' => (int) $client_id ]
|
|
) -> fetchAll( \PDO::FETCH_ASSOC );
|
|
|
|
$updated_count = 0;
|
|
foreach ( (array) $products as $row )
|
|
{
|
|
$product_id = (int) ( $row['id'] ?? 0 );
|
|
if ( $product_id <= 0 )
|
|
{
|
|
continue;
|
|
}
|
|
|
|
$cost_30 = (float) ( $row['cost_30'] ?? 0 );
|
|
$conversion_value_30 = (float) ( $row['conversion_value_30'] ?? 0 );
|
|
$conversions_30 = (float) ( $row['conversions_30'] ?? 0 );
|
|
$roas_30 = $cost_30 > 0 ? ( $conversion_value_30 / $cost_30 ) * 100 : 0;
|
|
|
|
$current_label = trim( (string) ( $row['custom_label_4'] ?? '' ) );
|
|
if ( $current_label !== '' && $current_label !== 'bestseller' )
|
|
{
|
|
continue;
|
|
}
|
|
|
|
$entry_met = $roas_30 >= (float) $entry && $conversions_30 >= $min_conversions;
|
|
$target_label = '';
|
|
|
|
if ( $entry_met )
|
|
{
|
|
$target_label = 'bestseller';
|
|
}
|
|
else if ( $current_label === 'bestseller' )
|
|
{
|
|
$can_exit = self::is_below_exit_for_cooldown( $product_id, (float) $exit, $cooldown_period );
|
|
$target_label = $can_exit ? '' : 'bestseller';
|
|
}
|
|
|
|
if ( $target_label !== $current_label )
|
|
{
|
|
$mdb -> update( 'products', [ 'custom_label_4' => $target_label ], [ 'id' => $product_id ] );
|
|
$updated_count++;
|
|
}
|
|
}
|
|
|
|
return $updated_count;
|
|
}
|
|
|
|
/**
|
|
* Generuje supplemental feed TSV dla klienta.
|
|
* Zwraca tablice ze statystykami: products_total, products_written, file.
|
|
*/
|
|
static public function generate_for_client( $client_id )
|
|
{
|
|
global $mdb;
|
|
|
|
$client_id = (int) $client_id;
|
|
$labels_updated = self::refresh_bestseller_labels_for_client( $client_id );
|
|
|
|
$products = $mdb -> query(
|
|
"SELECT p.offer_id, p.title, p.description, p.google_product_category, p.custom_label_3, p.custom_label_4
|
|
FROM products p
|
|
WHERE p.client_id = :client_id
|
|
AND p.offer_id IS NOT NULL
|
|
AND p.offer_id <> ''
|
|
AND ( p.title IS NOT NULL OR p.description IS NOT NULL OR p.google_product_category IS NOT NULL OR p.custom_label_3 IS NOT NULL OR p.custom_label_4 IS NOT NULL )",
|
|
[ ':client_id' => $client_id ]
|
|
) -> fetchAll( \PDO::FETCH_ASSOC );
|
|
|
|
$feeds_dir = __DIR__ . '/../../feeds';
|
|
if ( !is_dir( $feeds_dir ) )
|
|
{
|
|
mkdir( $feeds_dir, 0755, true );
|
|
}
|
|
|
|
$filename = 'supplemental_' . $client_id . '.tsv';
|
|
$file_path = $feeds_dir . '/' . $filename;
|
|
$fp = fopen( $file_path, 'w' );
|
|
|
|
if ( $fp === false )
|
|
{
|
|
throw new \RuntimeException( 'Nie mozna otworzyc pliku: ' . $file_path );
|
|
}
|
|
|
|
fwrite( $fp, "id\ttitle\tdescription\tgoogle_product_category\tcustom_label_3\tcustom_label_4\n" );
|
|
|
|
$written = 0;
|
|
foreach ( $products as $row )
|
|
{
|
|
$offer_id = self::normalize_feed_offer_id( $row['offer_id'] ?? '' );
|
|
$title = self::sanitize_for_tsv( $row['title'] ?? '' );
|
|
$description = self::sanitize_for_tsv( $row['description'] ?? '' );
|
|
$category = trim( (string) ( $row['google_product_category'] ?? '' ) );
|
|
$custom_label_3 = trim( (string) ( $row['custom_label_3'] ?? '' ) );
|
|
$custom_label_4 = trim( (string) ( $row['custom_label_4'] ?? '' ) );
|
|
|
|
if ( $offer_id === '' || ( $title === '' && $description === '' && $category === '' && $custom_label_3 === '' && $custom_label_4 === '' ) )
|
|
{
|
|
continue;
|
|
}
|
|
|
|
fwrite( $fp, implode( "\t", [
|
|
$offer_id,
|
|
$title,
|
|
$description,
|
|
$category,
|
|
$custom_label_3,
|
|
$custom_label_4
|
|
] ) . "\n" );
|
|
|
|
$written++;
|
|
}
|
|
|
|
fclose( $fp );
|
|
|
|
return [
|
|
'products_total' => count( $products ),
|
|
'products_written' => $written,
|
|
'file' => $filename,
|
|
'labels_updated' => $labels_updated
|
|
];
|
|
}
|
|
|
|
/**
|
|
* Czyści HTML z tekstu na potrzeby TSV.
|
|
* Zachowuje strukturę (akapity, listy) jako escaped \n.
|
|
* GMC interpretuje literalny \n jako nowa linia w opisie.
|
|
*/
|
|
static private function sanitize_for_tsv( $value )
|
|
{
|
|
$value = (string) $value;
|
|
if ( $value === '' ) return '';
|
|
|
|
// <li> → newline + punktor
|
|
$value = preg_replace( '#<li[^>]*>#i', "\n- ", $value );
|
|
|
|
// Blokowe tagi zamykajace → newline (akapity, divy, listy, naglowki)
|
|
$value = preg_replace( '#</(?:p|div|h[1-6]|tr|ul|ol)>#i', "\n", $value );
|
|
$value = preg_replace( '#<br\s*/?>#i', "\n", $value );
|
|
|
|
// Usun pozostale tagi
|
|
$value = strip_tags( $value );
|
|
|
|
// Dekoduj encje HTML
|
|
$value = html_entity_decode( $value, ENT_QUOTES | ENT_HTML5, 'UTF-8' );
|
|
|
|
// Taby → spacja (tab lamie TSV)
|
|
$value = str_replace( "\t", ' ', $value );
|
|
|
|
// Normalizuj newline
|
|
$value = str_replace( "\r\n", "\n", $value );
|
|
$value = str_replace( "\r", "\n", $value );
|
|
|
|
// Wielokrotne puste linie → max 1
|
|
$value = preg_replace( "/\n{3,}/", "\n\n", $value );
|
|
|
|
// Spacje wielokrotne w ramach linii → jedna
|
|
$value = preg_replace( '/ {2,}/', ' ', $value );
|
|
|
|
// Trim kazdej linii
|
|
$value = implode( "\n", array_map( 'trim', explode( "\n", $value ) ) );
|
|
$value = trim( $value );
|
|
|
|
// Escape newline jako literalny \n dla TSV
|
|
$value = str_replace( "\n", '\\n', $value );
|
|
|
|
return $value;
|
|
}
|
|
|
|
/**
|
|
* Normalizuje offer_id do formatu oczekiwanego przez feed supplemental.
|
|
* - usuwa ewentualny prefix channel:lang:country:
|
|
* - dla Shopify wymusza wielkie litery kodu kraju: shopify_PL_...
|
|
*/
|
|
static private function normalize_feed_offer_id( $offer_id )
|
|
{
|
|
$offer_id = trim( (string) $offer_id );
|
|
if ( $offer_id === '' ) return '';
|
|
|
|
$offer_id = trim( $offer_id, " \t\n\r\0\x0B'\"" );
|
|
|
|
if ( preg_match( '/^[a-z_]+:[a-z]{2,8}:[a-z]{2,8}:(.+)$/i', $offer_id, $matches ) )
|
|
{
|
|
$offer_id = trim( (string) ( $matches[1] ?? '' ) );
|
|
}
|
|
|
|
if ( preg_match( '/^shopify_([a-z]{2})_(.+)$/i', $offer_id, $matches ) )
|
|
{
|
|
$offer_id = 'shopify_' . strtoupper( (string) $matches[1] ) . '_' . (string) $matches[2];
|
|
}
|
|
|
|
return $offer_id;
|
|
}
|
|
}
|