172 lines
6.6 KiB
Python
172 lines
6.6 KiB
Python
#!/usr/bin/env python3
|
|
"""Import CX3 active offers + suppression lists into WEVADS"""
|
|
import urllib.request, xml.etree.ElementTree as ET, psycopg2, re, json
|
|
|
|
API = 'https://publisher.cx3ads.com/affiliates/api/2/'
|
|
KEY = 'y2vBu0MHJ9MIGsz7DR0Bg'
|
|
AFF = '10805'
|
|
NS = {'c': 'http://cakemarketing.com/affiliates/api/2/'}
|
|
DB = dict(host='127.0.0.1', port=5432, dbname='adx_system', user='postgres', password='wevads2026')
|
|
|
|
# CAKE tracking URL format: publisher.cx3ads.com/aff_c?offer_id=X&aff_id=10805&aff_sub=wevads&aff_sub2=TRACKID|ISP&aff_sub3=wevads|TRACKID
|
|
TRACK_BASE = 'https://publisher.cx3ads.com/aff_c?offer_id={oid}&aff_id=' + AFF
|
|
|
|
print("=== STEP 1: Fetch active CX3 offers ===")
|
|
feed_url = (API + 'offers.asmx/OfferFeed?api_key=' + KEY + '&affiliate_id=' + AFF +
|
|
'&campaign_name=&media_type_category_id=0&vertical_category_id=0&vertical_id=0' +
|
|
'&offer_status_id=1&tag_id=0&start_at_row=1&row_limit=100')
|
|
root = ET.fromstring(urllib.request.urlopen(feed_url, timeout=30).read().decode('utf-8'))
|
|
|
|
offers = []
|
|
for o in root.findall('.//c:offer', NS):
|
|
oid = o.findtext('c:offer_id', '', NS)
|
|
cid = o.findtext('c:campaign_id', '', NS)
|
|
name = o.findtext('c:offer_name', '', NS).strip()
|
|
payout = o.findtext('c:payout', '', NS) or '0'
|
|
preview = o.findtext('c:preview_link', '', NS) or ''
|
|
desc = o.findtext('c:description', '', NS) or ''
|
|
restrictions = o.findtext('c:restrictions', '', NS) or ''
|
|
|
|
# Detect country
|
|
text = (name + ' ' + desc).upper()
|
|
countries = []
|
|
mapping = {
|
|
'DE': ['GERMANY', ' DE ', 'DE |', ' GERMAN'],
|
|
'FR': ['FRANCE', ' FR '],
|
|
'UK': [' UK ', 'UNITED KINGDOM'],
|
|
'IT': ['ITALY', ' IT '],
|
|
'ES': ['SPAIN', ' ES '],
|
|
'US': [' US ', 'US ONLY'],
|
|
'CA': ['CANADA', ' CA '],
|
|
'AU': ['AUSTRALIA', ' AU '],
|
|
}
|
|
# Handle combined like AU/US
|
|
if 'AU/US' in name.upper() or 'US/AU' in name.upper():
|
|
countries.extend(['AU', 'US'])
|
|
if 'INTL' in name.upper():
|
|
countries.extend(['DE', 'UK', 'US', 'AU'])
|
|
for code, pats in mapping.items():
|
|
if code not in countries:
|
|
for p in pats:
|
|
if p in text or p in name.upper():
|
|
countries.append(code)
|
|
break
|
|
if not countries:
|
|
countries = ['US']
|
|
countries = list(set(countries))
|
|
|
|
tracking_url = TRACK_BASE.format(oid=oid) + '&aff_sub='
|
|
|
|
offers.append({
|
|
'cx3_id': oid, 'campaign_id': cid, 'name': name, 'payout': payout,
|
|
'preview': preview, 'countries': countries, 'tracking_url': tracking_url
|
|
})
|
|
|
|
print(f"Found {len(offers)} active offers")
|
|
|
|
print("\n=== STEP 2: Get suppression list URLs ===")
|
|
supp_urls = {}
|
|
for o in offers:
|
|
try:
|
|
surl = API + 'offers.asmx/GetSuppressionList?api_key=' + KEY + '&affiliate_id=' + AFF + '&offer_id=' + o['cx3_id']
|
|
sroot = ET.fromstring(urllib.request.urlopen(surl, timeout=10).read().decode('utf-8'))
|
|
dl_url = sroot.findtext('.//c:download_url', '', NS)
|
|
if dl_url:
|
|
supp_urls[o['cx3_id']] = dl_url
|
|
except:
|
|
pass
|
|
print(f"Got {len(supp_urls)} suppression list URLs")
|
|
|
|
print("\n=== STEP 3: Import to database ===")
|
|
conn = psycopg2.connect(**DB)
|
|
conn.autocommit = True
|
|
cur = conn.cursor()
|
|
|
|
# Get CX3 network ID
|
|
cur.execute("SELECT id FROM affiliate.affiliate_networks WHERE name LIKE '%CX3%'")
|
|
row = cur.fetchone()
|
|
network_id = row[0] if row else None
|
|
if not network_id:
|
|
cur.execute("INSERT INTO affiliate.affiliate_networks(name, status) VALUES ('CX3 Ads', 'Active') RETURNING id")
|
|
network_id = cur.fetchone()[0]
|
|
|
|
imported = 0
|
|
updated = 0
|
|
|
|
for o in offers:
|
|
cx3_tag = f"({o['cx3_id']})"
|
|
|
|
# Check existing
|
|
cur.execute("SELECT id FROM affiliate.offers WHERE name LIKE %s AND affiliate_network_id = %s",
|
|
(f"%{cx3_tag}%", network_id))
|
|
existing = cur.fetchone()
|
|
|
|
payout_clean = o['payout'].replace('$', '').replace('%', '').strip()
|
|
try:
|
|
payout_num = float(payout_clean)
|
|
except:
|
|
payout_num = 0
|
|
|
|
country_str = ','.join(o['countries'])
|
|
|
|
if existing:
|
|
cur.execute(
|
|
"UPDATE affiliate.offers SET offer_url=%s, status='Activated', countries=%s, payout=%s WHERE id=%s",
|
|
(o['tracking_url'], country_str, payout_num, existing[0])
|
|
)
|
|
offer_db_id = existing[0]
|
|
updated += 1
|
|
else:
|
|
cur.execute(
|
|
"INSERT INTO affiliate.offers(name, offer_url, status, affiliate_network_id, "
|
|
"affiliate_network_name, countries, payout) "
|
|
"VALUES (%s, %s, 'Activated', %s, 'CX3 Ads', %s, %s) RETURNING id",
|
|
(f"{o['name']} {cx3_tag}", o['tracking_url'], network_id, country_str, payout_num)
|
|
)
|
|
offer_db_id = cur.fetchone()[0]
|
|
imported += 1
|
|
|
|
# Upsert brain_offer_config
|
|
priority = 8 if payout_num >= 50 else 5 if payout_num >= 20 else 3
|
|
cur.execute(
|
|
"INSERT INTO admin.brain_offer_config(offer_id, offer_name, is_active, is_approved, link_status, priority, "
|
|
"target_countries, target_isps) "
|
|
"VALUES (%s, %s, true, true, 'live', %s, %s, '*') "
|
|
"ON CONFLICT (offer_id) DO UPDATE SET is_active=true, is_approved=true, link_status='live', "
|
|
"priority=%s, target_countries=%s",
|
|
(offer_db_id, o['name'], priority, country_str, priority, country_str)
|
|
)
|
|
|
|
# Store suppression URL
|
|
if o['cx3_id'] in supp_urls:
|
|
cur.execute(
|
|
"UPDATE admin.brain_offer_config SET notes = %s WHERE offer_id = %s",
|
|
(f"CX3_SUPP_URL:{supp_urls[o['cx3_id']]}", offer_db_id)
|
|
)
|
|
|
|
action = 'NEW' if not existing else 'UPD'
|
|
clist = ','.join(o['countries'])
|
|
print(f" {action} DB#{offer_db_id} CX3#{o['cx3_id']} P={priority} | {clist} | {o['payout']} | {o['name'][:45]}")
|
|
|
|
# Summary
|
|
cur.execute("SELECT COUNT(*) FROM affiliate.offers WHERE status='Activated'")
|
|
total_active = cur.fetchone()[0]
|
|
cur.execute("SELECT COUNT(*) FROM admin.brain_offer_config WHERE is_active=true AND is_approved=true AND link_status='live'")
|
|
total_approved = cur.fetchone()[0]
|
|
cur.execute("""SELECT target_countries, COUNT(*)
|
|
FROM admin.brain_offer_config WHERE is_active=true AND is_approved=true AND link_status='live'
|
|
GROUP BY target_countries ORDER BY COUNT(*) DESC""")
|
|
by_country = cur.fetchall()
|
|
|
|
print(f"\n{'='*60}")
|
|
print(f"RESULTS: {imported} new + {updated} updated = {imported+updated} total CX3 offers")
|
|
print(f"Total active offers in DB: {total_active}")
|
|
print(f"Total approved+live: {total_approved}")
|
|
print(f"\nBy country:")
|
|
for c, n in by_country:
|
|
print(f" {c}: {n} offers")
|
|
print(f"Suppression list URLs stored: {len(supp_urls)}")
|
|
|
|
cur.close()
|
|
conn.close()
|