Jacek Pyziak
fd0db9b145
- Implemented the main view for Supplemental Feeds, displaying clients with Merchant Account IDs and their associated feed files. - Added styling for the feeds page and its components, including headers, empty states, and dropdown menus for syncing actions. - Created backend logic to generate supplemental feeds for clients, including file handling and data sanitization. - Integrated new routes and views for managing feeds, ensuring proper data retrieval and display. - Updated navigation to include the new Supplemental Feeds section. - Added necessary documentation for CRON job management related to feed generation.
444 lines
13 KiB
PHP
444 lines
13 KiB
PHP
<?php
|
|
namespace controls;
|
|
|
|
class Clients
|
|
{
|
|
static private function get_facebook_conversion_window_days()
|
|
{
|
|
global $settings;
|
|
|
|
$settings_value = (int) \services\FacebookAdsApi::get_setting( 'facebook_ads_conversion_window_days' );
|
|
if ( $settings_value > 0 )
|
|
{
|
|
return min( 90, $settings_value );
|
|
}
|
|
|
|
$config_value = (int) ( $settings['facebook_ads_conversion_window_days'] ?? 30 );
|
|
if ( $config_value <= 0 )
|
|
{
|
|
$config_value = 30;
|
|
}
|
|
|
|
return min( 90, $config_value );
|
|
}
|
|
|
|
static private function normalize_facebook_ads_account_id( $value )
|
|
{
|
|
$value = trim( (string) $value );
|
|
if ( $value === '' )
|
|
{
|
|
return null;
|
|
}
|
|
|
|
if ( stripos( $value, 'act_' ) === 0 )
|
|
{
|
|
$digits = preg_replace( '/\D+/', '', substr( $value, 4 ) );
|
|
return $digits !== '' ? 'act_' . $digits : null;
|
|
}
|
|
|
|
$digits = preg_replace( '/\D+/', '', $value );
|
|
if ( $digits === '' )
|
|
{
|
|
return null;
|
|
}
|
|
|
|
return 'act_' . $digits;
|
|
}
|
|
|
|
static private function clients_has_deleted_column()
|
|
{
|
|
global $mdb;
|
|
|
|
static $has_deleted = null;
|
|
if ( $has_deleted !== null )
|
|
{
|
|
return (bool) $has_deleted;
|
|
}
|
|
|
|
try
|
|
{
|
|
$stmt = $mdb -> pdo -> prepare( 'SHOW COLUMNS FROM clients LIKE :column_name' );
|
|
if ( $stmt )
|
|
{
|
|
$stmt -> bindValue( ':column_name', 'deleted', \PDO::PARAM_STR );
|
|
$stmt -> execute();
|
|
$has_deleted = $stmt -> fetch( \PDO::FETCH_ASSOC ) ? 1 : 0;
|
|
}
|
|
else
|
|
{
|
|
$has_deleted = 0;
|
|
}
|
|
}
|
|
catch ( \Throwable $e )
|
|
{
|
|
$has_deleted = 0;
|
|
}
|
|
|
|
return (bool) $has_deleted;
|
|
}
|
|
|
|
static private function sql_clients_not_deleted( $alias = '' )
|
|
{
|
|
$alias = trim( (string) $alias );
|
|
$prefix = $alias !== '' ? $alias . '.' : '';
|
|
|
|
if ( self::clients_has_deleted_column() )
|
|
{
|
|
return 'COALESCE(' . $prefix . 'deleted, 0) = 0';
|
|
}
|
|
|
|
return '1=1';
|
|
}
|
|
|
|
static public function main_view()
|
|
{
|
|
return \view\Clients::main_view(
|
|
\factory\Clients::get_all()
|
|
);
|
|
}
|
|
|
|
static public function save()
|
|
{
|
|
$id = \S::get( 'id' );
|
|
$name = trim( \S::get( 'name' ) );
|
|
$google_ads_customer_id = trim( \S::get( 'google_ads_customer_id' ) );
|
|
$google_merchant_account_id = trim( \S::get( 'google_merchant_account_id' ) );
|
|
$facebook_ads_account_id = self::normalize_facebook_ads_account_id( \S::get( 'facebook_ads_account_id' ) );
|
|
$active_raw = \S::get( 'active' );
|
|
$active = (string) $active_raw === '0' ? 0 : 1;
|
|
|
|
if ( !$name )
|
|
{
|
|
\S::alert( 'Nazwa klienta jest wymagana.' );
|
|
header( 'Location: /clients' );
|
|
exit;
|
|
}
|
|
|
|
$google_ads_start_date = trim( \S::get( 'google_ads_start_date' ) );
|
|
|
|
$data = [
|
|
'name' => $name,
|
|
'google_ads_customer_id' => $google_ads_customer_id ?: null,
|
|
'google_merchant_account_id' => $google_merchant_account_id ?: null,
|
|
'facebook_ads_account_id' => $facebook_ads_account_id,
|
|
'google_ads_start_date' => $google_ads_start_date ?: null,
|
|
'active' => $active,
|
|
];
|
|
|
|
if ( $id )
|
|
{
|
|
\factory\Clients::update( $id, $data );
|
|
\S::alert( 'Klient został zaktualizowany.' );
|
|
}
|
|
else
|
|
{
|
|
\factory\Clients::create( $data );
|
|
\S::alert( 'Klient został dodany.' );
|
|
}
|
|
|
|
header( 'Location: /clients' );
|
|
exit;
|
|
}
|
|
|
|
static public function set_active()
|
|
{
|
|
$id = (int) \S::get( 'id' );
|
|
$active = (int) \S::get( 'active' ) === 1 ? 1 : 0;
|
|
|
|
if ( $id <= 0 )
|
|
{
|
|
echo json_encode( [ 'success' => false, 'message' => 'Brak ID klienta.' ] );
|
|
exit;
|
|
}
|
|
|
|
$client = \factory\Clients::get( $id );
|
|
if ( !$client )
|
|
{
|
|
echo json_encode( [ 'success' => false, 'message' => 'Nie znaleziono klienta.' ] );
|
|
exit;
|
|
}
|
|
|
|
if ( (int) ( $client['deleted'] ?? 0 ) === 1 )
|
|
{
|
|
echo json_encode( [ 'success' => false, 'message' => 'Nie mozna zmienic statusu usunietego klienta.' ] );
|
|
exit;
|
|
}
|
|
|
|
\factory\Clients::update( $id, [ 'active' => $active ] );
|
|
|
|
echo json_encode( [ 'success' => true, 'id' => $id, 'active' => $active ] );
|
|
exit;
|
|
}
|
|
|
|
static public function delete()
|
|
{
|
|
$id = \S::get( 'id' );
|
|
|
|
if ( $id )
|
|
{
|
|
\factory\Clients::delete( $id );
|
|
}
|
|
|
|
echo json_encode( [ 'success' => true ] );
|
|
exit;
|
|
}
|
|
|
|
static public function get()
|
|
{
|
|
$id = \S::get( 'id' );
|
|
$client = \factory\Clients::get( $id );
|
|
|
|
echo json_encode( $client ?: [] );
|
|
exit;
|
|
}
|
|
|
|
static public function sync_status()
|
|
{
|
|
global $mdb;
|
|
$clients_not_deleted_sql = self::sql_clients_not_deleted();
|
|
$clients_not_deleted_sql_c = self::sql_clients_not_deleted( 'c' );
|
|
|
|
// Kampanie: 1 work unit per row (pending=0, done=1)
|
|
$campaigns_raw = $mdb->query(
|
|
"SELECT client_id, COUNT(*) as total,
|
|
SUM(CASE WHEN phase='done' THEN 1 ELSE 0 END) as done
|
|
FROM cron_sync_status WHERE pipeline='campaigns' GROUP BY client_id"
|
|
)->fetchAll( \PDO::FETCH_ASSOC );
|
|
|
|
// Produkty: 3 work units per row (pending=0, fetch=1, aggregate_30=2, done=3)
|
|
$products_raw = $mdb->query(
|
|
"SELECT client_id, COUNT(*) * 3 as total,
|
|
SUM(CASE phase WHEN 'fetch' THEN 1 WHEN 'aggregate_30' THEN 2 WHEN 'done' THEN 3 ELSE 0 END) as done
|
|
FROM cron_sync_status WHERE pipeline='products' GROUP BY client_id"
|
|
)->fetchAll( \PDO::FETCH_ASSOC );
|
|
|
|
$data = [];
|
|
|
|
foreach ( $campaigns_raw as $row )
|
|
{
|
|
$data[ $row['client_id'] ]['campaigns'] = [ (int) $row['done'], (int) $row['total'] ];
|
|
}
|
|
|
|
foreach ( $products_raw as $row )
|
|
{
|
|
$data[ $row['client_id'] ]['products'] = [ (int) $row['done'], (int) $row['total'] ];
|
|
}
|
|
|
|
// Postep pobierania URL produktow z Merchant Center - per klient (przetworzone/wszystkie).
|
|
$merchant_clients_ids = $mdb -> query(
|
|
"SELECT id
|
|
FROM clients
|
|
WHERE " . $clients_not_deleted_sql . "
|
|
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 <> ''
|
|
ORDER BY id ASC"
|
|
) -> fetchAll( \PDO::FETCH_COLUMN );
|
|
|
|
if ( !empty( $merchant_clients_ids ) )
|
|
{
|
|
$merchant_clients_ids_int = array_values( array_map( 'intval', $merchant_clients_ids ) );
|
|
$merchant_progress_rows = $mdb -> query(
|
|
"SELECT
|
|
p.client_id,
|
|
COUNT(*) AS total,
|
|
SUM( CASE WHEN
|
|
( TRIM( COALESCE( p.product_url, '' ) ) <> '' AND LOWER( TRIM( p.product_url ) ) NOT IN ( '0', '-', 'null' ) )
|
|
OR COALESCE( p.merchant_url_not_found, 0 ) = 1
|
|
THEN 1 ELSE 0 END ) AS processed
|
|
FROM products p
|
|
WHERE p.client_id IN (" . implode( ',', $merchant_clients_ids_int ) . ")
|
|
AND TRIM( COALESCE( p.offer_id, '' ) ) <> ''
|
|
GROUP BY p.client_id"
|
|
) -> fetchAll( \PDO::FETCH_ASSOC );
|
|
|
|
foreach ( $merchant_progress_rows as $row )
|
|
{
|
|
$cid = (int) $row['client_id'];
|
|
$total = max( 1, (int) $row['total'] );
|
|
$processed = (int) $row['processed'];
|
|
$data[ $cid ]['merchant'] = [ $processed, $total ];
|
|
}
|
|
|
|
// Klienci bez zadnych produktow z offer_id - oznacz jako 1/1
|
|
$seen_merchant_ids = array_column( $merchant_progress_rows, 'client_id' );
|
|
foreach ( $merchant_clients_ids_int as $cid )
|
|
{
|
|
if ( !in_array( $cid, $seen_merchant_ids ) )
|
|
{
|
|
$data[ $cid ]['merchant'] = [ 1, 1 ];
|
|
}
|
|
}
|
|
}
|
|
|
|
$facebook_yesterday = date( 'Y-m-d', strtotime( '-1 day' ) );
|
|
|
|
try
|
|
{
|
|
$facebook_rows = $mdb -> query(
|
|
"SELECT
|
|
c.id AS client_id,
|
|
MAX( h.date_add ) AS last_synced_date
|
|
FROM clients c
|
|
LEFT JOIN facebook_campaigns fc
|
|
ON fc.client_id = c.id
|
|
LEFT JOIN facebook_campaigns_history h
|
|
ON h.facebook_campaign_id = fc.id
|
|
WHERE " . $clients_not_deleted_sql_c . "
|
|
AND COALESCE( c.active, 0 ) = 1
|
|
AND TRIM( COALESCE( c.facebook_ads_account_id, '' ) ) <> ''
|
|
GROUP BY c.id"
|
|
) -> fetchAll( \PDO::FETCH_ASSOC );
|
|
|
|
foreach ( (array) $facebook_rows as $row )
|
|
{
|
|
$facebook_client_id = (int) ( $row['client_id'] ?? 0 );
|
|
if ( $facebook_client_id <= 0 )
|
|
{
|
|
continue;
|
|
}
|
|
|
|
$last_synced = (string) ( $row['last_synced_date'] ?? '' );
|
|
$done = ( $last_synced === $facebook_yesterday ) ? 1 : 0;
|
|
$data[ $facebook_client_id ]['facebook_ads'] = [ $done, 1 ];
|
|
}
|
|
|
|
$facebook_force_client_id = (int) \services\FacebookAdsApi::get_setting( 'cron_facebook_ads_force_client_id' );
|
|
if ( $facebook_force_client_id > 0 )
|
|
{
|
|
$data[ $facebook_force_client_id ]['facebook_ads'] = [ 0, 1 ];
|
|
}
|
|
}
|
|
catch ( \Throwable $e )
|
|
{
|
|
// Tabele Facebook Ads mogly nie byc jeszcze zainstalowane.
|
|
}
|
|
|
|
// Supplemental Feed: binarny per klient (wygenerowany dzisiaj = 1/1, nie = 0/1)
|
|
$feed_today = date( 'Y-m-d' );
|
|
$feed_last_date = (string) \services\GoogleAdsApi::get_setting( 'cron_supplemental_feed_last_date' );
|
|
$feed_done_today = ( $feed_last_date === $feed_today );
|
|
|
|
if ( !empty( $merchant_clients_ids ) )
|
|
{
|
|
foreach ( $merchant_clients_ids as $cid )
|
|
{
|
|
$cid = (int) $cid;
|
|
$data[ $cid ]['feed'] = [ $feed_done_today ? 1 : 0, 1 ];
|
|
}
|
|
}
|
|
|
|
echo json_encode( [ 'status' => 'ok', 'data' => $data ] );
|
|
exit;
|
|
}
|
|
|
|
static public function force_sync()
|
|
{
|
|
global $mdb;
|
|
$clients_not_deleted_sql = self::sql_clients_not_deleted();
|
|
|
|
$id = (int) \S::get( 'id' );
|
|
$pipeline = trim( (string) \S::get( 'pipeline' ) );
|
|
|
|
if ( !$id )
|
|
{
|
|
echo json_encode( [ 'success' => false, 'message' => 'Brak ID klienta.' ] );
|
|
exit;
|
|
}
|
|
|
|
$deleted_select = self::clients_has_deleted_column() ? 'COALESCE(deleted, 0) AS deleted' : '0 AS deleted';
|
|
$client = $mdb -> query(
|
|
"SELECT id, COALESCE(active, 0) AS active, " . $deleted_select . ", google_ads_customer_id, google_merchant_account_id, facebook_ads_account_id
|
|
FROM clients
|
|
WHERE id = :id
|
|
LIMIT 1",
|
|
[ ':id' => $id ]
|
|
) -> fetch( \PDO::FETCH_ASSOC );
|
|
|
|
if ( !$client || (int) ( $client['deleted'] ?? 0 ) === 1 )
|
|
{
|
|
echo json_encode( [ 'success' => false, 'message' => 'Nie znaleziono klienta.' ] );
|
|
exit;
|
|
}
|
|
|
|
if ( (int) ( $client['active'] ?? 0 ) !== 1 )
|
|
{
|
|
echo json_encode( [ 'success' => false, 'message' => 'Klient jest nieaktywny. Aktywuj klienta przed synchronizacja.' ] );
|
|
exit;
|
|
}
|
|
|
|
if ( in_array( $pipeline, [ 'campaigns', 'products' ], true ) )
|
|
{
|
|
$where = [ 'client_id' => $id ];
|
|
$where['pipeline'] = $pipeline;
|
|
$mdb -> delete( 'cron_sync_status', $where );
|
|
}
|
|
else if ( $pipeline === 'campaigns_product_alerts_merchant' )
|
|
{
|
|
$has_ads_id = trim( (string) ( $client['google_ads_customer_id'] ?? '' ) ) !== '';
|
|
$has_merchant_id = trim( (string) ( $client['google_merchant_account_id'] ?? '' ) ) !== '';
|
|
if ( !$has_ads_id || !$has_merchant_id )
|
|
{
|
|
echo json_encode( [ 'success' => false, 'message' => 'Klient nie ma kompletnego Google Ads Customer ID i Merchant Account ID.' ] );
|
|
exit;
|
|
}
|
|
|
|
$previous_eligible_id = (int) $mdb -> query(
|
|
"SELECT MAX(id)
|
|
FROM clients
|
|
WHERE " . $clients_not_deleted_sql . "
|
|
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 <> ''
|
|
AND id < :client_id",
|
|
[ ':client_id' => $id ]
|
|
) -> fetchColumn();
|
|
|
|
\services\GoogleAdsApi::set_setting( 'cron_campaigns_product_alerts_last_client_id', (string) max( 0, $previous_eligible_id ) );
|
|
}
|
|
else if ( $pipeline === 'facebook_ads' )
|
|
{
|
|
$has_facebook_id = trim( (string) ( $client['facebook_ads_account_id'] ?? '' ) ) !== '';
|
|
if ( !$has_facebook_id )
|
|
{
|
|
echo json_encode( [ 'success' => false, 'message' => 'Klient nie ma ustawionego Facebook Ads Account ID.' ] );
|
|
exit;
|
|
}
|
|
|
|
\services\FacebookAdsApi::set_setting( 'cron_facebook_ads_force_client_id', (string) $id );
|
|
\services\FacebookAdsApi::set_setting( 'cron_facebook_ads_force_requested_at', date( 'Y-m-d H:i:s' ) );
|
|
}
|
|
else if ( $pipeline === 'supplemental_feed' )
|
|
{
|
|
$has_merchant_id = trim( (string) ( $client['google_merchant_account_id'] ?? '' ) ) !== '';
|
|
if ( !$has_merchant_id )
|
|
{
|
|
echo json_encode( [ 'success' => false, 'message' => 'Klient nie ma ustawionego Merchant Account ID.' ] );
|
|
exit;
|
|
}
|
|
|
|
try
|
|
{
|
|
\services\SupplementalFeed::generate_for_client( $id );
|
|
echo json_encode( [ 'success' => true, 'pipeline' => 'supplemental_feed', 'immediate' => true ] );
|
|
exit;
|
|
}
|
|
catch ( \Throwable $e )
|
|
{
|
|
echo json_encode( [ 'success' => false, 'message' => 'Blad generowania feedu: ' . $e -> getMessage() ] );
|
|
exit;
|
|
}
|
|
}
|
|
else
|
|
{
|
|
// Domyslny reset (wszystkie pipeline oparte o cron_sync_status).
|
|
$mdb -> delete( 'cron_sync_status', [ 'client_id' => $id ] );
|
|
}
|
|
|
|
echo json_encode( [ 'success' => true, 'pipeline' => $pipeline ?: 'all' ] );
|
|
exit;
|
|
}
|
|
}
|