#!/usr/bin/env python3
from flask import Flask, jsonify, request
from flask_cors import CORS
import psycopg2
from datetime import datetime, timedelta
app = Flask(__name__)
CORS(app)
DB_CONFIG = {
'host': 'localhost',
'database': 'spend_tracker',
'user': 'spend_user',
'password': 'a767fswd'
}
def get_db():
return psycopg2.connect(**DB_CONFIG)
@app.route('/api/spend', methods=['GET'])
def get_spend():
exec_name = request.args.get('exec', None)
start_date = request.args.get('start_date', None)
end_date = request.args.get('end_date', None)
# Default to month-to-date if no dates provided
today = datetime.now()
if not start_date:
start_date = today.replace(day=1).strftime('%Y-%m-%d')
if not end_date:
end_date = today.strftime('%Y-%m-%d')
# Calculate days for averages
start = datetime.strptime(start_date, '%Y-%m-%d')
end = datetime.strptime(end_date, '%Y-%m-%d')
days_elapsed = (end - start).days + 1
conn = get_db()
cur = conn.cursor()
query = """
SELECT
a.id,
a.account_name,
a.exec_name,
a.monthly_budget,
COALESCE(SUM(ds.cost), 0),
COALESCE(SUM(ds.conversions), 0),
COALESCE(SUM(ds.revenue), 0),
COUNT(ds.id)
FROM accounts a
LEFT JOIN daily_spend ds ON a.id = ds.account_id
AND ds.spend_date >= %s
AND ds.spend_date <= %s
WHERE 1=1
"""
params = [start_date, end_date]
if exec_name:
query += " AND a.exec_name = %s"
params.append(exec_name)
query += " GROUP BY a.id, a.account_name, a.exec_name, a.monthly_budget ORDER BY COALESCE(SUM(ds.cost), 0) DESC"
cur.execute(query, params)
results = []
for row in cur.fetchall():
s = float(row[4])
c = float(row[5])
r = float(row[6])
budget = float(row[3]) if row[3] else 0
da = s / days_elapsed if days_elapsed > 0 else 0
dr = r / days_elapsed if days_elapsed > 0 else 0
# Budget pacing calculations
projected_monthly = da * 30.4
budget_used_pct = (s / budget * 100) if budget > 0 else 0
days_in_month = 30.4
expected_spend = (budget / days_in_month) * days_elapsed if budget > 0 else 0
pacing = "on-track"
if budget > 0:
if s > expected_spend * 1.2:
pacing = "overspend"
elif s < expected_spend * 0.8:
pacing = "underspend"
results.append({
'account_id': row[0],
'account_name': row[1],
'exec': row[2],
'monthly_budget': round(budget, 2),
'spend': round(s, 2),
'revenue': round(r, 2),
'conversions': round(c, 1),
'cpa': round(s/c, 2) if c > 0 else 0,
'roas': round(r/s, 2) if s > 0 else 0,
'daily_avg_spend': round(da, 2),
'daily_avg_revenue': round(dr, 2),
'projected_monthly_spend': round(projected_monthly, 2),
'projected_monthly_revenue': round(dr * 30.4, 2),
'days_recorded': row[7],
'budget_used_pct': round(budget_used_pct, 1),
'pacing': pacing
})
cur.close()
conn.close()
return jsonify(results)
@app.route('/api/stats', methods=['GET'])
def get_stats():
start_date = request.args.get('start_date', None)
end_date = request.args.get('end_date', None)
today = datetime.now()
if not start_date:
start_date = today.replace(day=1).strftime('%Y-%m-%d')
if not end_date:
end_date = today.strftime('%Y-%m-%d')
start = datetime.strptime(start_date, '%Y-%m-%d')
end = datetime.strptime(end_date, '%Y-%m-%d')
days_elapsed = (end - start).days + 1
conn = get_db()
cur = conn.cursor()
cur.execute("""
SELECT
COUNT(DISTINCT a.id),
COALESCE(SUM(ds.cost), 0),
COALESCE(SUM(ds.conversions), 0),
COALESCE(SUM(ds.revenue), 0),
COALESCE(SUM(a.monthly_budget), 0)
FROM accounts a
LEFT JOIN daily_spend ds ON a.id = ds.account_id
AND ds.spend_date >= %s
AND ds.spend_date <= %s
""", (start_date, end_date))
row = cur.fetchone()
s = float(row[1])
c = float(row[2])
r = float(row[3])
total_budget = float(row[4])
da = s / days_elapsed if days_elapsed > 0 else 0
dr = r / days_elapsed if days_elapsed > 0 else 0
cur.close()
conn.close()
return jsonify({
'accounts': row[0],
'total_spend': round(s, 2),
'total_revenue': round(r, 2),
'total_conversions': round(c, 1),
'avg_cpa': round(s/c, 2) if c > 0 else 0,
'avg_roas': round(r/s, 2) if s > 0 else 0,
'daily_avg_spend': round(da, 2),
'daily_avg_revenue': round(dr, 2),
'projected_monthly_spend': round(da * 30.4, 2),
'projected_monthly_revenue': round(dr * 30.4, 2),
'days_in_range': days_elapsed,
'total_budget': round(total_budget, 2),
'budget_used_pct': round((s / total_budget * 100), 1) if total_budget > 0 else 0
})
@app.route('/api/budget/<int:account_id>', methods=['PUT'])
def update_budget(account_id):
data = request.json
budget = data.get('budget', 0)
conn = get_db()
cur = conn.cursor()
cur.execute("""
UPDATE accounts
SET monthly_budget = %s
WHERE id = %s
""", (budget, account_id))
conn.commit()
cur.close()
conn.close()
return jsonify({'success': True, 'account_id': account_id, 'budget': budget})
@app.route('/api/daily-trend', methods=['GET'])
def get_daily_trend():
start_date = request.args.get('start_date', None)
end_date = request.args.get('end_date', None)
today = datetime.now()
if not start_date:
start_date = today.replace(day=1).strftime('%Y-%m-%d')
if not end_date:
end_date = today.strftime('%Y-%m-%d')
conn = get_db()
cur = conn.cursor()
cur.execute("""
SELECT
ds.spend_date,
SUM(ds.cost),
SUM(ds.conversions),
SUM(ds.revenue)
FROM daily_spend ds
WHERE ds.spend_date >= %s AND ds.spend_date <= %s
GROUP BY ds.spend_date
ORDER BY ds.spend_date
""", (start_date, end_date))
results = []
for row in cur.fetchall():
results.append({
'date': row[0].strftime('%Y-%m-%d'),
'spend': float(row[1]),
'conversions': float(row[2]),
'revenue': float(row[3])
})
cur.close()
conn.close()
return jsonify(results)
@app.route('/api/exec-breakdown', methods=['GET'])
def get_exec_breakdown():
start_date = request.args.get('start_date', None)
end_date = request.args.get('end_date', None)
today = datetime.now()
if not start_date:
start_date = today.replace(day=1).strftime('%Y-%m-%d')
if not end_date:
end_date = today.strftime('%Y-%m-%d')
conn = get_db()
cur = conn.cursor()
cur.execute("""
SELECT
COALESCE(a.exec_name, 'Unassigned'),
COUNT(DISTINCT a.id),
SUM(ds.cost),
SUM(ds.revenue)
FROM accounts a
LEFT JOIN daily_spend ds ON a.id = ds.account_id
AND ds.spend_date >= %s AND ds.spend_date <= %s
GROUP BY a.exec_name
ORDER BY SUM(ds.cost) DESC
""", (start_date, end_date))
results = []
for row in cur.fetchall():
results.append({
'exec': row[0],
'accounts': row[1],
'spend': float(row[2] or 0),
'revenue': float(row[3] or 0)
})
cur.close()
conn.close()
return jsonify(results)
if __name__ == '__main__':
app.run(host='0.0.0.0', port=5000, debug=True)