import logging
import os
import sys
import json
import time
import datetime
import csv
import re
import traceback
from _decimal import Decimal
import boto3
import psycopg2
from psycopg2.extras import DictCursor
from psycopg2.pool import ThreadedConnectionPool
s3 = boto3.client('s3')
bucket = 'appnext-bidder'
placements = {}
def calculate():
date_from = (datetime.datetime.utcnow()-datetime.timedelta(hours=1)).strftime("%Y-%m-%d %H:00:00")
date_to = datetime.datetime.utcnow().strftime("%Y-%m-%d %H:00:00")
conn = psycopg2.connect(user=os.environ["POSTGRESQL_USER"], password=os.environ["POSTGRESQL_PASSWORD"],
host=os.environ["POSTGRESQL_HOST"], dbname=os.environ["POSTGRESQL_DB"],
cursor_factory=DictCursor)
try:
with conn.cursor() as cur:
cur.execute("""
SELECT
ssp, country, media_size, platform, media_type, adspace_type, pub_app, count(*) as requests
FROM
requests
WHERE
cre_date >= %s AND cre_date < %s
GROUP BY
ssp, country, media_size, platform, media_type, adspace_type, pub_app
""", (date_from, date_to))
rows = cur.fetchall()
for row in rows:
try:
key = "placement:" + ":".join([row["ssp"], row["country"], row["media_size"], row["platform"], row["media_type"], row["adspace_type"], row["pub_app"]])
if key not in placements:
placements[key] = {
"requests": 0,
"bids": 0,
"bidprices": Decimal(0),
"imps": 0,
"wins": 0,
"winprices": Decimal(0),
"clicks": 0,
"installs": 0,
"revenue": Decimal(0),
"expenses": Decimal(0),
}
placements[key]["requests"] += row["requests"]
except Exception as e:
logging.warning("Invalid data")
logging.warning(e)
with conn.cursor() as cur:
cur.execute("""
SELECT
ssp, country, media_size, platform, media_type, adspace_type, pub_app, count(*) as bids, sum(bid_price) as bidprices
FROM
bids_v3
WHERE
cre_date >= %s AND cre_date < %s
GROUP BY
ssp, country, media_size, platform, media_type, adspace_type, pub_app
""", (date_from, date_to))
rows = cur.fetchall()
for row in rows:
try:
key = "placement:" + ":".join(
[row["ssp"], row["country"], row["media_size"], row["platform"], row["media_type"],
row["adspace_type"], row["pub_app"]])
if key not in placements:
placements[key] = {
"requests": 0,
"bids": 0,
"bidprices": Decimal(0),
"imps": 0,
"wins": 0,
"winprices": Decimal(0),
"clicks": 0,
"installs": 0,
"revenue": Decimal(0),
"expenses": Decimal(0),
}
placements[key]["bids"] += row["bids"]
placements[key]["bidprices"] += Decimal(row["bidprices"])
except Exception as e:
logging.warning("Invalid data")
logging.warning(e)
with conn.cursor() as cur:
cur.execute("""
SELECT
ssp, country, media_size, platform, media_type, adspace_type, pub_app, count(*) as wins, sum(clear_price) as winprices
FROM
wins_v2
WHERE
cre_date >= %s AND cre_date < %s
GROUP BY
ssp, country, media_size, platform, media_type, adspace_type, pub_app
""", (date_from, date_to))
rows = cur.fetchall()
for row in rows:
try:
key = "placement:" + ":".join(
[row["ssp"], row["country"], row["media_size"], row["platform"], row["media_type"],
row["adspace_type"], row["pub_app"]])
if key not in placements:
placements[key] = {
"requests": 0,
"bids": 0,
"bidprices": Decimal(0),
"imps": 0,
"wins": 0,
"winprices": Decimal(0),
"clicks": 0,
"installs": 0,
"revenue": Decimal(0),
"expenses": Decimal(0),
}
placements[key]["wins"] += row["wins"]
placements[key]["winprices"] += Decimal(row["winprices"])
except Exception as e:
logging.warning("Invalid data")
logging.warning(e)
with conn.cursor() as cur:
cur.execute("""
SELECT
ssp, country, media_size, platform, media_type, adspace_type, pub_app, count(*) as imps, sum(clear_price) as expenses
FROM
imps_v2
WHERE
cre_date >= %s AND cre_date < %s
GROUP BY
ssp, country, media_size, platform, media_type, adspace_type, pub_app
""", (date_from, date_to))
rows = cur.fetchall()
for row in rows:
try:
key = "placement:" + ":".join(
[row["ssp"], row["country"], row["media_size"], row["platform"], row["media_type"],
row["adspace_type"], row["pub_app"]])
if key not in placements:
placements[key] = {
"requests": 0,
"bids": 0,
"bidprices": Decimal(0),
"imps": 0,
"wins": 0,
"winprices": Decimal(0),
"clicks": 0,
"installs": 0,
"revenue": Decimal(0),
"expenses": Decimal(0),
}
placements[key]["imps"] += row["imps"]
placements[key]["expenses"] += (Decimal(row["expenses"]) / Decimal(1000))
except Exception as e:
logging.warning("Invalid data")
logging.warning(e)
with conn.cursor() as cur:
cur.execute("""
SELECT
ssp, country, media_size, platform, media_type, adspace_type, pub_app, count(*) as clicks
FROM
clicks_v2
WHERE
cre_date >= %s AND cre_date < %s
GROUP BY
ssp, country, media_size, platform, media_type, adspace_type, pub_app
""", (date_from, date_to))
rows = cur.fetchall()
for row in rows:
try:
key = "placement:" + ":".join(
[row["ssp"], row["country"], row["media_size"], row["platform"], row["media_type"],
row["adspace_type"], row["pub_app"]])
if key not in placements:
placements[key] = {
"requests": 0,
"bids": 0,
"bidprices": Decimal(0),
"imps": 0,
"wins": 0,
"winprices": Decimal(0),
"clicks": 0,
"installs": 0,
"revenue": Decimal(0),
"expenses": Decimal(0),
}
placements[key]["clicks"] += row["clicks"]
except Exception as e:
logging.warning("Invalid data")
logging.warning(e)
with conn.cursor() as cur:
cur.execute("""
SELECT
ssp, country, media_size, platform, media_type, adspace_type, pub_app, count(*) as installs, sum(revenue) as revenue
FROM
installs_v3
WHERE
cre_date >= %s AND cre_date < %s
GROUP BY
ssp, country, media_size, platform, media_type, adspace_type, pub_app
""", (date_from, date_to))
rows = cur.fetchall()
for row in rows:
try:
key = "placement:" + ":".join(
[row["ssp"], row["country"], row["media_size"], row["platform"], row["media_type"],
row["adspace_type"], row["pub_app"]])
if key not in placements:
placements[key] = {
"requests": 0,
"bids": 0,
"bidprices": Decimal(0),
"imps": 0,
"wins": 0,
"winprices": Decimal(0),
"clicks": 0,
"installs": 0,
"revenue": Decimal(0),
"expenses": Decimal(0),
}
placements[key]["installs"] += row["installs"]
placements[key]["revenue"] += Decimal(row["revenue"])
except Exception as e:
logging.warning("Invalid data")
logging.warning(e)
except Exception as e:
logging.warn("Error Postgres")
logging.warn(e)
traceback.print_exc()
conn.close()
str_time = str(time.time())
with open('/tmp/report-%s.csv' % str_time, 'w', newline='\n') as csvfile:
spamwriter = csv.writer(csvfile, delimiter='|', quotechar='"', quoting=csv.QUOTE_MINIMAL)
date = (datetime.datetime.utcnow()-datetime.timedelta(hours=1)).strftime("%Y-%m-%d %H:00:00")
# print(placements)
for key, placement in placements.items():
try:
_, ssp, country, media_size, platform, media_type, adspace_type, pub_app = key.split(":")
spamwriter.writerow([
key,
date,
pub_app,
ssp,
country,
media_size,
media_type,
adspace_type,
platform,
placement["imps"],
placement["bids"],
str(placement["expenses"]),
str(placement["revenue"]),
placement["clicks"],
placement["requests"],
"learning",
str(placement["bidprices"]),
placement["installs"],
placement["wins"],
str(placement["winprices"]),
0,
0,
0,
0
])
except ValueError:
print(key)