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 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)