def hedu_api(**kwargs):
tables = [hedu.Yb_hedu, hedu.Yc_hedu, hedu.Yu, hedu.Ybc_hedu, hedu.Ybu, hedu.Yuc, hedu.Ybuc]
idonly = request.args.get('id', False) is not False
limit = int(request.args.get('limit', 0) or kwargs.pop('limit', 0))
order = request.args.get('order', None) or kwargs.pop('order', None)
sort = request.args.get('sort', None) or kwargs.pop('sort', 'desc')
offset = request.args.get('offset', None) or kwargs.pop('offset', None)
if order and "." in order:
order, sort = order.split(".")
serialize = request.args.get('serialize', None) or kwargs.pop('serialize', True)
exclude = request.args.get('exclude', None) or kwargs.pop('exclude', None)
download = request.args.get('download', None) or kwargs.pop('download', None)
if "university_id" in kwargs:
# -- there is no nesting for university ids
kwargs["university_id"] = kwargs["university_id"].replace("show.5", "show")
allowed_when_not, possible_tables = table_helper.prepare(['bra_id', 'university_id', 'course_hedu_id'], tables)
table = table_helper.select_best_table(kwargs, allowed_when_not, possible_tables)
filters, groups, show_column = query_helper.build_filters_and_groups(table, kwargs, exclude=exclude)
results = query_helper.query_table(table, filters=filters, groups=groups, limit=limit, order=order, sort=sort, offset=offset, serialize=serialize)
if serialize or download:
response = jsonify(results)
if download:
return gen_csv(results, "hedu")
return gzip_response(response)
return results
def secex_api(**kwargs):
limit = int(kwargs.pop("limit", 0)) or int(request.args.get("limit", 0))
order = request.args.get("order", None) or kwargs.pop("order", None)
sort = request.args.get("sort", None) or kwargs.pop("sort", "desc")
offset = request.args.get("offset", None) or kwargs.pop("offset", None)
if order and "." in order:
order, sort = order.split(".")
ignore_zeros = request.args.get("zeros", False) or kwargs.pop("zeros", False)
serialize = request.args.get("serialize", None) or kwargs.pop("serialize", True)
exclude = request.args.get("exclude", None) or kwargs.pop("exclude", None)
download = request.args.get("download", None) or kwargs.pop("download", None)
if not "month" in kwargs:
kwargs["month"] = query_helper.ALL
if exclude and "," in exclude:
exclude = exclude.split(",")
tables = [Ymw, Ymp, Ymb, Ympw, Ymbw, Ymbp, Ymbpw]
allowed_when_not, possible_tables = table_helper.prepare(["bra_id", "hs_id", "wld_id"], tables)
table = table_helper.select_best_table(kwargs, allowed_when_not, possible_tables)
filters, groups, show_column = query_helper.build_filters_and_groups(table, kwargs, exclude=exclude)
# if ignore_zeros:
# filters.append( getattr(table, "val_usd") > 0 )
results = query_helper.query_table(
table, filters=filters, groups=groups, limit=limit, order=order, sort=sort, offset=offset, serialize=serialize
)
if table is Ymbp:
stripped_filters, stripped_groups, show_column2 = query_helper.convert_filters(
Ymp, kwargs, remove=["bra_id", "month"]
)
stripped_columns = [Ymp.year, Ymp.hs_id, Ymp.pci]
stripped_filters.append(Ymp.month == 0)
tmp = query_helper.query_table(
Ymp, columns=stripped_columns, filters=stripped_filters, groups=stripped_groups, serialize=serialize
)
results["pci"] = tmp
stripped_filters, stripped_groups, show_column2 = query_helper.convert_filters(
Ymb, kwargs, remove=["hs_id", "month"]
)
stripped_columns = [Ymb.year, Ymb.bra_id, Ymb.eci]
stripped_filters.append(Ymb.month == 0)
tmp = query_helper.query_table(
Ymp, columns=stripped_columns, filters=stripped_filters, groups=stripped_groups, serialize=serialize
)
tmp = {d[0]: d[2] for d in tmp["data"]}
results["eci"] = tmp
if serialize or download:
response = jsonify(results)
if download:
return gen_csv(results, "secex")
return gzip_response(response)
return results
def rais_api(**kwargs):
limit = int(kwargs.pop('limit', 0)) or int(request.args.get('limit', 0) )
order = request.args.get('order', None) or kwargs.pop('order', None)
sort = request.args.get('sort', None) or kwargs.pop('sort', 'desc')
offset = request.args.get('offset', None) or kwargs.pop('offset', None)
if order and "." in order:
order, sort = order.split(".")
# ignore_zeros = request.args.get('zeros', True) or kwargs.pop('zeros', True)
serialize = request.args.get('serialize', None) or kwargs.pop('serialize', True)
exclude = request.args.get('exclude', None) or kwargs.pop('exclude', None)
download = request.args.get('download', None) or kwargs.pop('download', None)
required_bras = request.args.get('required_bras', False) or kwargs.pop('required_bras', False)
if required_bras:
bra_id = kwargs.get("bra_id")
cnae_id = kwargs.get("cnae_id").split(".")[0]
reqs = Ybi_reqs.query.filter(Ybi_reqs.bra_id == bra_id).filter(Ybi_reqs.cnae_id == cnae_id)
year = kwargs.get("year")
if year != "all":
reqs = reqs.filter(Ybi_reqs.year == year)
reqs = reqs.all()
results = {}
for req in reqs:
bras = [Bra.query.get(b).serialize() for b in req.required_bras.split(",")]
results[req.year] = bras
return jsonify(data=results)
if exclude and "," in exclude:
exclude = exclude.split(",")
allowed_when_not, possible_tables = table_helper.prepare(['bra_id', 'cnae_id', 'cbo_id'], [ Yb_rais, Yi, Yo, Ybi, Ybo, Yio, Ybio ] )
table = table_helper.select_best_table(kwargs, allowed_when_not, possible_tables)
filters, groups, show_column = query_helper.build_filters_and_groups(table, kwargs, exclude=exclude)
results = query_helper.query_table(table, filters=filters, groups=groups, limit=limit, order=order, sort=sort, offset=offset, serialize=serialize)
if table is Ybi:
stripped_filters, stripped_groups, show_column2 = query_helper.convert_filters(Yi, kwargs, remove=['bra_id'])
stripped_columns = [Yi.year, Yi.cnae_id, Yi.cbo_diversity, Yi.cbo_diversity_eff]
diversity_results = query_helper.query_table(Yi, columns=stripped_columns, filters=stripped_filters, groups=stripped_groups, limit=limit, order=order, sort=sort, serialize=serialize)
results["diversity"] = diversity_results
elif table is Ybo:
stripped_filters, stripped_groups, show_column2 = query_helper.convert_filters(Yo, kwargs, remove=['bra_id'])
stripped_columns = [Yo.year, Yo.cbo_id, Yo.cnae_diversity, Yo.cnae_diversity_eff]
diversity_results = query_helper.query_table(Yo, columns=stripped_columns, filters=stripped_filters, groups=stripped_groups, limit=limit, order=order, sort=sort, serialize=serialize)
results["diversity"] = diversity_results
if serialize or download:
response = jsonify(results)
if download:
return gen_csv(results, "rais")
return gzip_response(response)
return results
def rais_api(**kwargs):
limit = int(kwargs.pop('limit', 0)) or int(request.args.get('limit', 0) )
order = request.args.get('order', None) or kwargs.pop('order', None)
sort = request.args.get('sort', None) or kwargs.pop('sort', 'desc')
offset = request.args.get('offset', None) or kwargs.pop('offset', None)
if order and "." in order:
order, sort = order.split(".")
# ignore_zeros = request.args.get('zeros', True) or kwargs.pop('zeros', True)
serialize = request.args.get('serialize', None) or kwargs.pop('serialize', True)
exclude = request.args.get('exclude', None) or kwargs.pop('exclude', None)
download = request.args.get('download', None) or kwargs.pop('download', None)
required_bras = request.args.get('required_bras', False) or kwargs.pop('required_bras', False)
if required_bras:
bra_id = kwargs.get("bra_id")
cnae_id = kwargs.get("cnae_id").split(".")[0]
reqs = Ybi_reqs.query.filter(Ybi_reqs.bra_id == bra_id).filter(Ybi_reqs.cnae_id == cnae_id)
year = kwargs.get("year")
if year != "all":
reqs = reqs.filter(Ybi_reqs.year == year)
reqs = reqs.all()
results = {}
for req in reqs:
bras = [Bra.query.get(b).serialize() for b in req.required_bras.split(",")]
results[req.year] = bras
return jsonify(data=results)
if exclude and "," in exclude:
exclude = exclude.split(",")
allowed_when_not, possible_tables = table_helper.prepare(['bra_id', 'cnae_id', 'cbo_id'], [ Yb_rais, Yi, Yo, Ybi, Ybo, Yio, Ybio ] )
table = table_helper.select_best_table(kwargs, allowed_when_not, possible_tables)
filters, groups, show_column = query_helper.build_filters_and_groups(table, kwargs, exclude=exclude)
results = query_helper.query_table(table, filters=filters, groups=groups, limit=limit, order=order, sort=sort, offset=offset, serialize=serialize)
if table is Ybi:
stripped_filters, stripped_groups, show_column2 = query_helper.convert_filters(Yi, kwargs, remove=['bra_id'])
stripped_columns = [Yi.year, Yi.cnae_id, Yi.cbo_diversity, Yi.cbo_diversity_eff]
diversity_results = query_helper.query_table(Yi, columns=stripped_columns, filters=stripped_filters, groups=stripped_groups, limit=limit, order=order, sort=sort, serialize=serialize)
results["diversity"] = diversity_results
elif table is Ybo:
stripped_filters, stripped_groups, show_column2 = query_helper.convert_filters(Yo, kwargs, remove=['bra_id'])
stripped_columns = [Yo.year, Yo.cbo_id, Yo.cnae_diversity, Yo.cnae_diversity_eff]
diversity_results = query_helper.query_table(Yo, columns=stripped_columns, filters=stripped_filters, groups=stripped_groups, limit=limit, order=order, sort=sort, serialize=serialize)
results["diversity"] = diversity_results
if serialize or download:
response = jsonify(results)
if download:
return gen_csv(results, "rais")
return gzip_response(response)
return results
def hedu_api(**kwargs):
tables = [
hedu.Yb_hedu, hedu.Yc_hedu, hedu.Yu, hedu.Ybc_hedu, hedu.Ybu, hedu.Yuc,
hedu.Ybuc
]
idonly = request.args.get('id', False) is not False
limit = int(request.args.get('limit', 0) or kwargs.pop('limit', 0))
order = request.args.get('order', None) or kwargs.pop('order', None)
sort = request.args.get('sort', None) or kwargs.pop('sort', 'desc')
offset = request.args.get('offset', None) or kwargs.pop('offset', None)
if order and "." in order:
order, sort = order.split(".")
serialize = request.args.get('serialize', None) or kwargs.pop(
'serialize', True)
exclude = request.args.get('exclude', None) or kwargs.pop('exclude', None)
download = request.args.get('download', None) or kwargs.pop(
'download', None)
if "university_id" in kwargs:
# -- there is no nesting for university ids
kwargs["university_id"] = kwargs["university_id"].replace(
"show.5", "show")
allowed_when_not, possible_tables = table_helper.prepare(
['bra_id', 'university_id', 'course_hedu_id'], tables)
table = table_helper.select_best_table(kwargs, allowed_when_not,
possible_tables)
filters, groups, show_column = query_helper.build_filters_and_groups(
table, kwargs, exclude=exclude)
results = query_helper.query_table(table,
filters=filters,
groups=groups,
limit=limit,
order=order,
sort=sort,
offset=offset,
serialize=serialize)
if serialize or download:
response = jsonify(results)
if download:
return gen_csv(results, "hedu")
return gzip_response(response)
return results
def sc_api(**kwargs):
tables = [Yc_sc, Yb_sc, Ys, Ybc_sc, Ybs, Ysc, Ybsc]
idonly = request.args.get('id', False) is not False
limit = int(request.args.get('limit', 0) or kwargs.pop('limit', 0))
order = request.args.get('order', None) or kwargs.pop('order', None)
sort = request.args.get('sort', None) or kwargs.pop('sort', 'desc')
offset = request.args.get('offset', None) or kwargs.pop('offset', None)
if order and "." in order:
order, sort = order.split(".")
serialize = request.args.get('serialize', None) or kwargs.pop('serialize', True)
exclude = request.args.get('exclude', None) or kwargs.pop('exclude', None)
download = request.args.get('download', None) or kwargs.pop('download', None)
only_vocational = False
if "school_id" in kwargs:
# -- there is no nesting for school ids
kwargs["school_id"] = kwargs["school_id"].replace("show.8", "show")
only_vocational = "show" in kwargs["school_id"] and not "xx" in kwargs["course_sc_id"] and exclude == "xx%"
if only_vocational:
exclude = None
# -- 2. select table
allowed_when_not, possible_tables = table_helper.prepare(['bra_id', 'school_id', 'course_sc_id'], tables)
table = table_helper.select_best_table(kwargs, allowed_when_not, possible_tables)
if not table:
raise Exception("No table!")
filters, groups, show_column = query_helper.build_filters_and_groups(table, kwargs, exclude=exclude)
if only_vocational:
vschools = School.query.with_entities(School.id).filter(School.is_vocational == 1).all()
vschools = [v.id for v in vschools]
filters.append(table.school_id.in_(vschools))
results = query_helper.query_table(table, filters=filters, groups=groups, limit=limit, order=order, sort=sort, offset=offset, serialize=serialize)
if serialize or download:
response = jsonify(results)
if download:
return gen_csv(results, "sc")
return gzip_response(response)
return results
def secex_api(**kwargs):
limit = int(kwargs.pop('limit', 0)) or int(request.args.get('limit', 0))
order = request.args.get('order', None) or kwargs.pop('order', None)
sort = request.args.get('sort', None) or kwargs.pop('sort', 'desc')
offset = request.args.get('offset', None) or kwargs.pop('offset', None)
if order and "." in order:
order, sort = order.split(".")
ignore_zeros = request.args.get('zeros', False) or kwargs.pop(
'zeros', False)
serialize = request.args.get('serialize', None) or kwargs.pop(
'serialize', True)
exclude = request.args.get('exclude', None) or kwargs.pop('exclude', None)
download = request.args.get('download', None) or kwargs.pop(
'download', None)
if not "month" in kwargs:
kwargs["month"] = query_helper.ALL
if exclude and "," in exclude:
exclude = exclude.split(",")
tables = [Ymw, Ymp, Ymb, Ympw, Ymbw, Ymbp, Ymbpw]
allowed_when_not, possible_tables = table_helper.prepare(
['bra_id', 'hs_id', 'wld_id'], tables)
table = table_helper.select_best_table(kwargs, allowed_when_not,
possible_tables)
filters, groups, show_column = query_helper.build_filters_and_groups(
table, kwargs, exclude=exclude)
# if ignore_zeros:
# filters.append( getattr(table, "val_usd") > 0 )
results = query_helper.query_table(table,
filters=filters,
groups=groups,
limit=limit,
order=order,
sort=sort,
offset=offset,
serialize=serialize)
if table is Ymbp:
stripped_filters, stripped_groups, show_column2 = query_helper.convert_filters(
Ymp, kwargs, remove=['bra_id', 'month'])
stripped_columns = [Ymp.year, Ymp.hs_id, Ymp.pci]
stripped_filters.append(Ymp.month == 0)
tmp = query_helper.query_table(Ymp,
columns=stripped_columns,
filters=stripped_filters,
groups=stripped_groups,
serialize=serialize)
results["pci"] = tmp
stripped_filters, stripped_groups, show_column2 = query_helper.convert_filters(
Ymb, kwargs, remove=['hs_id', 'month'])
stripped_columns = [Ymb.year, Ymb.bra_id, Ymb.eci]
stripped_filters.append(Ymb.month == 0)
tmp = query_helper.query_table(Ymp,
columns=stripped_columns,
filters=stripped_filters,
groups=stripped_groups,
serialize=serialize)
tmp = {d[0]: d[2] for d in tmp["data"]}
results["eci"] = tmp
if serialize or download:
response = jsonify(results)
if download:
return gen_csv(results, "secex")
return gzip_response(response)
return results
def profiles(category=None, id=None):
if category == "bra" and id == "all":
item = Wld.query.get("sabra")
else:
item = globals()[category.capitalize()].query.get_or_404(id)
profile = getattr(profileModels, category.capitalize())(category, item)
builds = profile.builds()
start = request.args.get("app", 1)
stats = compute_stats(item)
stat_groups = {}
for s in stats:
if s["key"] not in stat_groups:
stat_groups[s["key"]] = []
stat_groups[s["key"]].append(s)
related = []
def get_related(table, attr, title):
if attr == "bra":
data_attr = category
else:
data_attr = attr
if data_attr == "hs" or data_attr == "wld":
dataset = "secex"
elif data_attr == "cnae" or data_attr == "cbo":
dataset = "rais"
elif data_attr == "university" or data_attr == "course_hedu":
dataset = "hedu"
elif data_attr == "course_sc":
dataset = "sc"
else:
return
q = table.query.filter(getattr(table, "{}_id".format(category)) == id) \
.filter(getattr(table, "year") == parse_year(__year_range__[dataset][1]))
if category in ("bra"):
q = q.filter(
not_(
getattr(
table,
"{}_id_target".format(category)).startswith("0xx")))
elif category in ("cnae", "cbo", "wld"):
q = q.filter(
not_(
getattr(table,
"{}_id_target".format(category)).startswith("xx")))
elif category in ("hs"):
q = q.filter(
not_(
getattr(table,
"{}_id_target".format(category)).startswith("22")))
elif category in ("course_hedu", "course_sc"):
q = q.filter(
not_(
getattr(table,
"{}_id_target".format(category)).startswith("00")))
q = q.order_by(getattr(table,
"prox_{}".format(attr)).desc()).limit(40).all()
if len(q) > 0:
m = globals()[category.capitalize()]
q = [(m.query.get(getattr(a, "{}_id_target".format(category))),
getattr(a, "prox_{}".format(attr))) for a in q]
related.append({"title": title, "pages": q})
if category == "bra":
get_related(Ybb, "hs",
gettext("Locations with Similar Product Exports"))
get_related(Ybb, "wld",
gettext("Locations with Similar Trade Partners"))
get_related(Ybb, "cnae", gettext("Locations with Similar Industries"))
get_related(Ybb, "cbo", gettext("Locations with Similar Occupations"))
if category == "cnae":
get_related(Yii, "bra", gettext("Similar Industries by Co-location"))
if category == "cbo":
get_related(Yoo, "bra", gettext("Similar Occupations by Co-location"))
if category == "hs":
get_related(Ypp, "bra", gettext("Similar Products by Co-location"))
if category == "wld":
get_related(Yww, "bra",
gettext("Destinations with Similar Brazilian Origins"))
if category == "university":
get_related(Yuu, "course_hedu",
gettext("Universities with Similar Course Offerings"))
response = make_response(
render_template("profiles/profile.html",
profile=profile,
category=category,
item=item,
stats=stat_groups,
starting_app=start,
builds=builds,
related=related))
return gzip_response(response)
def profiles(category = None, id = None):
if category == "bra" and id == "all":
item = Wld.query.get("sabra")
else:
item = globals()[category.capitalize()].query.get_or_404(id)
profile = getattr(profileModels, category.capitalize())(category, item)
builds = profile.builds()
start = request.args.get("app", 1)
stats = compute_stats(item)
stat_groups = {}
for s in stats:
if s["key"] not in stat_groups:
stat_groups[s["key"]] = []
stat_groups[s["key"]].append(s)
related = []
def get_related(table, attr, title):
if attr == "bra":
data_attr = category
else:
data_attr = attr
if data_attr == "hs" or data_attr == "wld":
dataset = "secex"
elif data_attr == "cnae" or data_attr == "cbo":
dataset = "rais"
elif data_attr == "university" or data_attr == "course_hedu":
dataset = "hedu"
elif data_attr == "course_sc":
dataset = "sc"
else:
return
q = table.query.filter(getattr(table, "{}_id".format(category)) == id) \
.filter(getattr(table, "year") == parse_year(__year_range__[dataset][1]))
if category in ("bra"):
q = q.filter(not_(getattr(table, "{}_id_target".format(category)).startswith("0xx")))
elif category in ("cnae", "cbo", "wld"):
q = q.filter(not_(getattr(table, "{}_id_target".format(category)).startswith("xx")))
elif category in ("hs"):
q = q.filter(not_(getattr(table, "{}_id_target".format(category)).startswith("22")))
elif category in ("course_hedu", "course_sc"):
q = q.filter(not_(getattr(table, "{}_id_target".format(category)).startswith("00")))
q = q.order_by(getattr(table, "prox_{}".format(attr)).desc()).limit(40).all()
if len(q) > 0:
m = globals()[category.capitalize()]
q = [(m.query.get(getattr(a, "{}_id_target".format(category))), getattr(a, "prox_{}".format(attr))) for a in q]
related.append({"title": title, "pages": q})
if category == "bra":
get_related(Ybb, "hs", gettext("Locations with Similar Product Exports"))
get_related(Ybb, "wld", gettext("Locations with Similar Trade Partners"))
get_related(Ybb, "cnae", gettext("Locations with Similar Industries"))
get_related(Ybb, "cbo", gettext("Locations with Similar Occupations"))
if category == "cnae":
get_related(Yii, "bra", gettext("Similar Industries by Co-location"))
if category == "cbo":
get_related(Yoo, "bra", gettext("Similar Occupations by Co-location"))
if category == "hs":
get_related(Ypp, "bra", gettext("Similar Products by Co-location"))
if category == "wld":
get_related(Yww, "bra", gettext("Destinations with Similar Brazilian Origins"))
if category == "university":
get_related(Yuu, "course_hedu", gettext("Universities with Similar Course Offerings"))
response = make_response(render_template("profiles/profile.html", profile=profile,
category=category, item=item, stats=stat_groups,
starting_app = start, builds=builds, related=related))
return gzip_response(response)
