def get_leaderboard():
query = """
SELECT updated_by,count(*) as cnt
FROM coa.volunteer_info
where updated_by<>"" and event_code like "COA%"
group by updated_by order by count(*) desc
"""
lb=db.fetch_data(query)
return lb
def get_leaderboard():
query = """
SELECT updated_by,count(*) as cnt
FROM coa.volunteer_info
where updated_by<>"" and event_code like "COA%"
group by updated_by order by count(*) desc
"""
lb = db.fetch_data(query)
return lb
def get_sites():
"""
Get all the possible possible inputs for pull down
"""
query = """
SELECT site_id, CONCAT(county, ', ', town, ', ', COALESCE(street, '')) AS site_name
FROM coa.site ORDER BY county, town, street;
"""
sites = db.fetch_data(query)
return sites
def get_sites():
"""
Get all the possible possible inputs for pull down
"""
query = """
SELECT site_id, CONCAT(county, ', ', town, ', ', COALESCE(street, '')) AS site_name
FROM coa.site ORDER BY county, town, street;
"""
sites = db.fetch_data(query)
return sites
def get_tls():
query = """
SELECT DISTINCT team_captain
FROM coa.team_info
ORDER BY team_captain;
"""
tls = [tl[0] for tl in db.fetch_data(query)]
print tls
return tls
def get_totals_per_county_by_year():
"""
Returns the total debris collected in each county by year.
Return format <county>, <county--year \t total debris>.
"""
query = """
select county, concat(county, '--', cast(year(volunteer_date) as char), '\t', cast(sum(quantity) as char)) as count
from coa_summary_view
group by year(volunteer_date), county;
"""
result = db.fetch_data(query)
return result
def get_tls():
query = """
SELECT DISTINCT team_captain
FROM coa.team_info
ORDER BY team_captain;
"""
tls = [tl[0] for tl in db.fetch_data(query)]
print tls
return tls
def get_itemcategory():
"""
Get all the possible possible inputs for pull down
"""
query = """
select concat(material,'--',category
,'\t',cast(round(sum(quantity)/(select sum(quantity) from volunteer_info)*100,2) as char)
,'%')as cnt
from volunteer_info a
join item b
on a.item_id=b.item_id
group by material,b.category
"""
itemcategory = db.fetch_data(query)
return itemcategory
def get_itemcategory():
"""
Get all the possible possible inputs for pull down
"""
query = """
select concat(material,'--',category
,'\t',cast(round(sum(quantity)/(select sum(quantity) from volunteer_info)*100,2) as char)
,'%')as cnt
from volunteer_info a
join item b
on a.item_id=b.item_id
group by material,b.category
"""
itemcategory = db.fetch_data(query)
return itemcategory
def get_item_categories_for_all_years():
"""
Returns the data for materials and categories for all years as a list of '--' delimited strings.
:return list of tuples:
"""
query = """
select a.yr, concat(a.yr,'--',a.material,'--',a.category,'\t',cast(round(a.total/b.yr_total*100,2) as char),'%') as cnt
from (
select year(volunteer_date) as yr, material, category, sum(quantity) as total
from coa_summary_view
group by year(volunteer_date), category
) as a
left join (
select year(volunteer_date) as yr, county, sum(quantity) as yr_total
from coa_summary_view
group by yr
) as b
on a.yr = b.yr
"""
result = db.fetch_data(query)
return result
def get_trash_items():
query = """
SELECT
DISTINCT item_id,
material,
concat(category,', ', ifnull(item_name,'') ,'[',item_id,']') as category
FROM coa.item;
"""
items = db.fetch_data(query);
trash_items = {}
for item in items:
item_id, parent, child = item
if parent in trash_items:
trash_items[parent].append(child)
trash_items[parent] = sorted(trash_items[parent])
else:
trash_items[parent]=[child]
return trash_items
def get_trash_items():
query = """
SELECT
DISTINCT item_id,
material,
concat(category,', ', ifnull(item_name,'') ,'[',item_id,']') as category
FROM coa.item;
"""
items = db.fetch_data(query)
trash_items = {}
for item in items:
item_id, parent, child = item
if parent in trash_items:
trash_items[parent].append(child)
trash_items[parent] = sorted(trash_items[parent])
else:
trash_items[parent] = [child]
return trash_items
