336 lines
14 KiB
PHP
Executable File
336 lines
14 KiB
PHP
Executable File
|
|
<?php
|
|
/**
|
|
* SPONSOR & OFFER MANAGER
|
|
* Connect to affiliate networks, manage offers, track conversions
|
|
*/
|
|
header('Content-Type: application/json');
|
|
$pdo = new PDO("pgsql:host=localhost;dbname=adx_system", "admin", "admin123", [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
|
|
|
|
$pdo->exec("
|
|
CREATE TABLE IF NOT EXISTS admin.sponsors (
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(255),
|
|
api_url TEXT,
|
|
api_key TEXT,
|
|
api_type VARCHAR(50) DEFAULT 'hasoffers',
|
|
is_active BOOLEAN DEFAULT true,
|
|
last_sync TIMESTAMP,
|
|
offers_count INTEGER DEFAULT 0,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS admin.offers (
|
|
id SERIAL PRIMARY KEY,
|
|
sponsor_id INTEGER,
|
|
external_id VARCHAR(100),
|
|
name VARCHAR(255),
|
|
description TEXT,
|
|
vertical VARCHAR(100),
|
|
payout FLOAT DEFAULT 0,
|
|
payout_type VARCHAR(50) DEFAULT 'cpa',
|
|
geo_targets TEXT,
|
|
allowed_traffic TEXT,
|
|
tracking_url TEXT,
|
|
preview_url TEXT,
|
|
creatives TEXT,
|
|
status VARCHAR(50) DEFAULT 'active',
|
|
epc FLOAT DEFAULT 0,
|
|
conversion_rate FLOAT DEFAULT 0,
|
|
total_clicks INTEGER DEFAULT 0,
|
|
total_conversions INTEGER DEFAULT 0,
|
|
last_conversion TIMESTAMP,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE(sponsor_id, external_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS admin.conversions (
|
|
id SERIAL PRIMARY KEY,
|
|
offer_id INTEGER,
|
|
campaign_id INTEGER,
|
|
click_id VARCHAR(255),
|
|
payout FLOAT,
|
|
ip_address VARCHAR(50),
|
|
country VARCHAR(10),
|
|
converted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS admin.creatives (
|
|
id SERIAL PRIMARY KEY,
|
|
offer_id INTEGER,
|
|
type VARCHAR(50),
|
|
name VARCHAR(255),
|
|
url TEXT,
|
|
size VARCHAR(50),
|
|
preview TEXT,
|
|
performance_score FLOAT DEFAULT 0,
|
|
clicks INTEGER DEFAULT 0,
|
|
conversions INTEGER DEFAULT 0,
|
|
is_active BOOLEAN DEFAULT true
|
|
);
|
|
");
|
|
|
|
class SponsorManager {
|
|
private $pdo;
|
|
|
|
public function __construct($pdo) {
|
|
$this->pdo = $pdo;
|
|
}
|
|
|
|
// ============================================
|
|
// SPONSORS
|
|
// ============================================
|
|
|
|
public function addSponsor($data) {
|
|
$this->pdo->prepare("INSERT INTO admin.sponsors (name, api_url, api_key, api_type) VALUES (?, ?, ?, ?)")
|
|
->execute([$data['name'], $data['api_url'] ?? '', $data['api_key'] ?? '', $data['api_type'] ?? 'hasoffers']);
|
|
return ['success' => true, 'id' => $this->pdo->lastInsertId()];
|
|
}
|
|
|
|
public function syncOffers($sponsorId) {
|
|
$sponsor = $this->pdo->query("SELECT * FROM admin.sponsors WHERE id = $sponsorId")->fetch(PDO::FETCH_ASSOC);
|
|
if (!$sponsor) return ['error' => 'Sponsor not found'];
|
|
|
|
// Call sponsor API based on type
|
|
$offers = $this->fetchOffersFromAPI($sponsor);
|
|
|
|
$synced = 0;
|
|
foreach ($offers as $offer) {
|
|
$this->pdo->prepare("INSERT INTO admin.offers (sponsor_id, external_id, name, description, vertical, payout, geo_targets, tracking_url, preview_url)
|
|
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
|
|
ON CONFLICT (sponsor_id, external_id) DO UPDATE SET name = ?, payout = ?, status = 'active'")
|
|
->execute([
|
|
$sponsorId, $offer['id'], $offer['name'], $offer['description'] ?? '',
|
|
$offer['vertical'] ?? 'general', $offer['payout'] ?? 0,
|
|
json_encode($offer['geo'] ?? []), $offer['tracking_url'] ?? '', $offer['preview_url'] ?? '',
|
|
$offer['name'], $offer['payout'] ?? 0
|
|
]);
|
|
$synced++;
|
|
}
|
|
|
|
$this->pdo->exec("UPDATE admin.sponsors SET last_sync = NOW(), offers_count = $synced WHERE id = $sponsorId");
|
|
|
|
return ['success' => true, 'synced' => $synced];
|
|
}
|
|
|
|
private function fetchOffersFromAPI($sponsor) {
|
|
$type = $sponsor['api_type'];
|
|
$url = $sponsor['api_url'];
|
|
$key = $sponsor['api_key'];
|
|
|
|
switch ($type) {
|
|
case 'hasoffers':
|
|
$endpoint = "$url/Apiv3/json?api_key=$key&Target=Affiliate_Offer&Method=findAll";
|
|
break;
|
|
case 'cake':
|
|
$endpoint = "$url/api/1/get.asmx/Offers?api_key=$key";
|
|
break;
|
|
case 'everflow':
|
|
$endpoint = "$url/v1/affiliates/offers";
|
|
break;
|
|
default:
|
|
$endpoint = $url;
|
|
}
|
|
|
|
$ch = curl_init($endpoint);
|
|
curl_setopt_array($ch, [
|
|
CURLOPT_RETURNTRANSFER => true,
|
|
CURLOPT_TIMEOUT => 30,
|
|
CURLOPT_HTTPHEADER => ['Authorization: Bearer ' . $key]
|
|
]);
|
|
$response = curl_exec($ch);
|
|
curl_close($ch);
|
|
|
|
$data = json_decode($response, true);
|
|
|
|
// Parse based on API type
|
|
return $this->parseOfferResponse($type, $data);
|
|
}
|
|
|
|
private function parseOfferResponse($type, $data) {
|
|
$offers = [];
|
|
|
|
switch ($type) {
|
|
case 'hasoffers':
|
|
foreach ($data['response']['data'] ?? [] as $o) {
|
|
$offers[] = [
|
|
'id' => $o['Offer']['id'],
|
|
'name' => $o['Offer']['name'],
|
|
'payout' => $o['Offer']['default_payout'],
|
|
'tracking_url' => $o['Offer']['offer_url']
|
|
];
|
|
}
|
|
break;
|
|
default:
|
|
// Generic parsing
|
|
if (is_array($data)) {
|
|
foreach ($data as $o) {
|
|
$offers[] = [
|
|
'id' => $o['id'] ?? uniqid(),
|
|
'name' => $o['name'] ?? $o['title'] ?? 'Unknown',
|
|
'payout' => $o['payout'] ?? 0
|
|
];
|
|
}
|
|
}
|
|
}
|
|
|
|
return $offers;
|
|
}
|
|
|
|
// ============================================
|
|
// OFFERS
|
|
// ============================================
|
|
|
|
public function addOffer($data) {
|
|
$this->pdo->prepare("INSERT INTO admin.offers (sponsor_id, external_id, name, description, vertical, payout, payout_type, geo_targets, tracking_url, preview_url) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")
|
|
->execute([
|
|
$data['sponsor_id'] ?? null, $data['external_id'] ?? uniqid(),
|
|
$data['name'], $data['description'] ?? '', $data['vertical'] ?? 'general',
|
|
$data['payout'] ?? 0, $data['payout_type'] ?? 'cpa',
|
|
json_encode($data['geo_targets'] ?? []), $data['tracking_url'] ?? '', $data['preview_url'] ?? ''
|
|
]);
|
|
return ['success' => true, 'id' => $this->pdo->lastInsertId()];
|
|
}
|
|
|
|
public function getOffersByVertical($vertical) {
|
|
return $this->pdo->query("SELECT * FROM admin.offers WHERE vertical = '$vertical' AND status = 'active' ORDER BY epc DESC")->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getBestOffers($limit = 10) {
|
|
return $this->pdo->query("SELECT o.*, s.name as sponsor_name FROM admin.offers o LEFT JOIN admin.sponsors s ON o.sponsor_id = s.id WHERE o.status = 'active' ORDER BY o.epc DESC, o.conversion_rate DESC LIMIT $limit")->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function matchOfferToData($offerId, $segmentCriteria) {
|
|
$offer = $this->pdo->query("SELECT * FROM admin.offers WHERE id = $offerId")->fetch(PDO::FETCH_ASSOC);
|
|
if (!$offer) return ['error' => 'Offer not found'];
|
|
|
|
$geoTargets = json_decode($offer['geo_targets'] ?? '[]', true);
|
|
|
|
// Find matching data segments
|
|
$matchScore = 0;
|
|
$recommendations = [];
|
|
|
|
// Check geo match
|
|
if (!empty($geoTargets)) {
|
|
$recommendations[] = "Target countries: " . implode(', ', $geoTargets);
|
|
}
|
|
|
|
// Check vertical match
|
|
$recommendations[] = "Best for vertical: " . $offer['vertical'];
|
|
|
|
// EPC-based recommendation
|
|
if ($offer['epc'] > 0.5) {
|
|
$recommendations[] = "High EPC - prioritize quality traffic";
|
|
}
|
|
|
|
return [
|
|
'offer' => $offer,
|
|
'match_score' => $matchScore,
|
|
'recommendations' => $recommendations
|
|
];
|
|
}
|
|
|
|
// ============================================
|
|
// CONVERSIONS
|
|
// ============================================
|
|
|
|
public function recordConversion($data) {
|
|
$this->pdo->prepare("INSERT INTO admin.conversions (offer_id, campaign_id, click_id, payout, ip_address, country) VALUES (?, ?, ?, ?, ?, ?)")
|
|
->execute([$data['offer_id'], $data['campaign_id'] ?? null, $data['click_id'] ?? '', $data['payout'] ?? 0, $data['ip'] ?? '', $data['country'] ?? '']);
|
|
|
|
// Update offer stats
|
|
$this->pdo->exec("UPDATE admin.offers SET total_conversions = total_conversions + 1, last_conversion = NOW() WHERE id = {$data['offer_id']}");
|
|
|
|
return ['success' => true];
|
|
}
|
|
|
|
public function getConversionStats($offerId = null, $days = 30) {
|
|
$where = $offerId ? "WHERE offer_id = $offerId" : "";
|
|
$dateFilter = "AND converted_at > NOW() - INTERVAL '$days days'";
|
|
if ($where) {
|
|
$where .= " $dateFilter";
|
|
} else {
|
|
$where = "WHERE converted_at > NOW() - INTERVAL '$days days'";
|
|
}
|
|
|
|
return [
|
|
'total_conversions' => $this->pdo->query("SELECT COUNT(*) FROM admin.conversions $where")->fetchColumn(),
|
|
'total_revenue' => $this->pdo->query("SELECT COALESCE(SUM(payout), 0) FROM admin.conversions $where")->fetchColumn(),
|
|
'by_country' => $this->pdo->query("SELECT country, COUNT(*) as count, SUM(payout) as revenue FROM admin.conversions $where GROUP BY country ORDER BY count DESC LIMIT 10")->fetchAll(PDO::FETCH_ASSOC),
|
|
'by_day' => $this->pdo->query("SELECT DATE(converted_at) as date, COUNT(*) as count, SUM(payout) as revenue FROM admin.conversions $where GROUP BY DATE(converted_at) ORDER BY date DESC LIMIT $days")->fetchAll(PDO::FETCH_ASSOC)
|
|
];
|
|
}
|
|
|
|
// ============================================
|
|
// CREATIVES
|
|
// ============================================
|
|
|
|
public function addCreative($data) {
|
|
$this->pdo->prepare("INSERT INTO admin.creatives (offer_id, type, name, url, size, preview) VALUES (?, ?, ?, ?, ?, ?)")
|
|
->execute([$data['offer_id'], $data['type'] ?? 'banner', $data['name'], $data['url'], $data['size'] ?? '', $data['preview'] ?? '']);
|
|
return ['success' => true, 'id' => $this->pdo->lastInsertId()];
|
|
}
|
|
|
|
public function getBestCreatives($offerId) {
|
|
return $this->pdo->query("SELECT * FROM admin.creatives WHERE offer_id = $offerId AND is_active = true ORDER BY performance_score DESC, conversions DESC")->fetchAll(PDO::FETCH_ASSOC);
|
|
}
|
|
|
|
public function getStats() {
|
|
return [
|
|
'sponsors' => $this->pdo->query("SELECT COUNT(*) FROM admin.sponsors WHERE is_active = true")->fetchColumn(),
|
|
'offers' => $this->pdo->query("SELECT COUNT(*) FROM admin.offers WHERE status = 'active'")->fetchColumn(),
|
|
'conversions_today' => $this->pdo->query("SELECT COUNT(*) FROM admin.conversions WHERE DATE(converted_at) = CURRENT_DATE")->fetchColumn(),
|
|
'revenue_today' => $this->pdo->query("SELECT COALESCE(SUM(payout), 0) FROM admin.conversions WHERE DATE(converted_at) = CURRENT_DATE")->fetchColumn(),
|
|
'top_verticals' => $this->pdo->query("SELECT vertical, COUNT(*) as count FROM admin.offers GROUP BY vertical ORDER BY count DESC LIMIT 5")->fetchAll(PDO::FETCH_ASSOC)
|
|
];
|
|
}
|
|
}
|
|
|
|
$manager = new SponsorManager($pdo);
|
|
$action = $_POST['action'] ?? $_GET['action'] ?? '';
|
|
|
|
switch ($action) {
|
|
case 'add_sponsor':
|
|
echo json_encode($manager->addSponsor($_POST));
|
|
break;
|
|
case 'sync':
|
|
echo json_encode($manager->syncOffers($_POST['sponsor_id']));
|
|
break;
|
|
case 'sponsors':
|
|
echo json_encode(['sponsors' => $pdo->query("SELECT * FROM admin.sponsors ORDER BY id")->fetchAll(PDO::FETCH_ASSOC)]);
|
|
break;
|
|
case 'add_offer':
|
|
echo json_encode($manager->addOffer($_POST));
|
|
break;
|
|
case 'offers':
|
|
echo json_encode(['offers' => $pdo->query("SELECT o.*, s.name as sponsor_name FROM admin.offers o LEFT JOIN admin.sponsors s ON o.sponsor_id = s.id ORDER BY o.epc DESC")->fetchAll(PDO::FETCH_ASSOC)]);
|
|
break;
|
|
case 'best_offers':
|
|
echo json_encode(['offers' => $manager->getBestOffers($_GET['limit'] ?? 10)]);
|
|
break;
|
|
case 'by_vertical':
|
|
echo json_encode(['offers' => $manager->getOffersByVertical($_GET['vertical'])]);
|
|
break;
|
|
case 'match':
|
|
echo json_encode($manager->matchOfferToData($_GET['offer_id'], $_GET['criteria'] ?? ''));
|
|
break;
|
|
case 'conversion':
|
|
echo json_encode($manager->recordConversion($_POST));
|
|
break;
|
|
case 'conversion_stats':
|
|
echo json_encode($manager->getConversionStats($_GET['offer_id'] ?? null, $_GET['days'] ?? 30));
|
|
break;
|
|
case 'add_creative':
|
|
echo json_encode($manager->addCreative($_POST));
|
|
break;
|
|
case 'creatives':
|
|
echo json_encode(['creatives' => $manager->getBestCreatives($_GET['offer_id'])]);
|
|
break;
|
|
case 'stats':
|
|
echo json_encode($manager->getStats());
|
|
break;
|
|
default:
|
|
echo json_encode(['name' => 'Sponsor Manager', 'actions' => ['add_sponsor','sync','sponsors','add_offer','offers','best_offers','by_vertical','match','conversion','conversion_stats','add_creative','creatives','stats']]);
|
|
}
|
|
|