def resource_query(engine):
data = {}
q = """
SELECT
src.*,
COUNT(spe.id) AS num_entries,
SUM(spe."AmountFormatted"::float) AS total,
MAX(spe."DateFormatted") AS latest,
MIN(spe."DateFormatted") AS oldest
FROM source src LEFT OUTER JOIN spending spe
ON src.resource_id = spe.resource_id
AND spe.valid = true
GROUP BY src.id
"""
r = engine.execute(q)
for res in sl.resultiter(r):
issues = list(sl.resultiter(engine.execute(
""" SELECT message, data, stage FROM issue WHERE resource_id = '%s' AND stage = 'retrieve'
ORDER BY timestamp DESC """ % (res['resource_id']))))
issues += list(sl.resultiter(engine.execute(
""" SELECT message, data, stage FROM issue WHERE resource_id = '%s' AND resource_hash = '%s'
ORDER BY timestamp DESC """ % (res['resource_id'], res['retrieve_hash']))))
issues = set([(i['stage'], i['message'], i['data']) for i in issues])
res['issues'] = issues
pn = res['publisher_name']
if pn is None:
continue
if not pn in data:
data[pn] = []
data[pn].append(res)
return data
def group_query(engine):
stats = {}
q = """
SELECT
src.publisher_name AS name,
MAX(src.last_modified) AS last_modified,
COUNT(DISTINCT src.id) AS num_sources,
COUNT(spe.id) AS num_entries,
SUM(spe."AmountFormatted"::float) AS total,
MAX(spe."DateFormatted") AS latest,
MIN(spe."DateFormatted") AS oldest
FROM source src LEFT OUTER JOIN spending spe
ON spe.resource_id = src.resource_id
AND spe.valid = true
GROUP BY src.publisher_name;
"""
r = engine.execute(q)
for res in sl.resultiter(r):
res['top_class'] = False
if res['latest']:
dt = datetime.datetime.strptime(res['latest'], "%Y-%m-%d")
ref = datetime.datetime.now() - datetime.timedelta(days=62)
res['top_class'] = dt > ref
stats[res['name']] = res
return stats
def group_query(engine):
stats = {}
q = """
SELECT
src.publisher_name AS name,
MAX(src.last_modified) AS last_modified,
COUNT(DISTINCT src.id) AS num_sources,
COUNT(spe.id) AS num_entries,
SUM(spe."AmountFormatted"::float) AS total,
MAX(spe."DateFormatted") AS latest,
MIN(spe."DateFormatted") AS oldest
FROM source src LEFT OUTER JOIN spending spe
ON spe.resource_id = src.resource_id
AND spe.valid = true
GROUP BY src.publisher_name;
"""
r = engine.execute(q)
for res in sl.resultiter(r):
res['top_class'] = False
if res['latest']:
dt = datetime.datetime.strptime(res['latest'], "%Y-%m-%d")
ref = datetime.datetime.now() - datetime.timedelta(days=62)
res['top_class'] = dt > ref
stats[res['name']] = res
return stats
def resource_query(engine):
data = {}
q = """
SELECT
src.*,
COUNT(spe.id) AS num_entries,
SUM(spe."AmountFormatted"::float) AS total,
MAX(spe."DateFormatted") AS latest,
MIN(spe."DateFormatted") AS oldest
FROM source src LEFT OUTER JOIN spending spe
ON src.resource_id = spe.resource_id
AND spe.valid = true
GROUP BY src.id
"""
r = engine.execute(q)
for res in sl.resultiter(r):
issues = list(
sl.resultiter(
engine.execute(
""" SELECT message, data, stage FROM issue WHERE resource_id = '%s' AND stage = 'retrieve'
ORDER BY timestamp DESC """ % (res['resource_id']))))
issues += list(
sl.resultiter(
engine.execute(
""" SELECT message, data, stage FROM issue WHERE resource_id = '%s' AND resource_hash = '%s'
ORDER BY timestamp DESC """ %
(res['resource_id'], res['retrieve_hash']))))
issues = set([(i['stage'], i['message'], i['data']) for i in issues])
res['issues'] = issues
pn = res['publisher_name']
if pn is None:
continue
if not pn in data:
data[pn] = []
data[pn].append(res)
return data
def dump_query(engine, q, file_name):
fh = open(file_name, 'wb')
rp = engine.execute(q)
sl.dump_csv(sl.resultiter(rp), fh)
