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