class Short(db.Model):
__tablename__ = 'explore_short'
slug = db.Column(db.String(30), unique=True, primary_key=True)
long_url = db.Column(db.String(255), unique=True)
created = db.Column(db.DateTime, default=datetime.now)
clicks = db.Column(db.Integer, default=0)
last_accessed = db.Column(db.DateTime)
@staticmethod
def make_unique_slug(long_url):
# Helper to generate random URL string
# Thx EJF: https://github.com/ericjohnf/urlshort
def id_generator(size=6, chars=string.ascii_lowercase + string.digits):
return ''.join(random.choice(chars) for x in range(size))
# test if it already exists
short = Short.query.filter_by(long_url=long_url).first()
if short:
return short.slug
else:
while True:
new_slug = id_generator()
if Short.query.filter_by(slug=new_slug).first() == None:
break
return new_slug
def __repr__(self):
return "<ShortURL: '%s'>" % self.long_url
class SitcTopTrade(object):
top_export_dest = db.Column(db.String(5))
top_import_dest = db.Column(db.String(5))
@declared_attr
def top_export(cls):
return db.Column(db.String(6), db.ForeignKey(Sitc.id))
@declared_attr
def top_import(cls):
return db.Column(db.String(6), db.ForeignKey(Sitc.id))
class Yd(db.Model, AutoSerialize):
__tablename__ = 'sitc_yd'
year = db.Column(db.Integer, primary_key=True)
dest_id = db.Column(db.String(5),
db.ForeignKey(Country.id),
primary_key=True)
export_val = db.Column(db.Numeric(16, 2))
import_val = db.Column(db.Numeric(16, 2))
def __repr__(self):
return '<Yd %d.%s>' % (self.year, self.dest_id)
class Sitc_name(db.Model, AutoSerialize, ProdNameAttr):
__tablename__ = 'attr_sitc_name'
sitc_id = db.Column(db.String(8), db.ForeignKey(Sitc.id), primary_key=True)
@hybrid_property
def id(self):
return self.sitc_id
class SitcId(object):
sitc_id_len = db.Column(db.Integer())
@declared_attr
def sitc_id(cls):
return db.Column(db.String(8),
db.ForeignKey(Sitc.id),
primary_key=True)
class Hs07Id(object):
hs07_id_len = db.Column(db.Integer())
@declared_attr
def hs07_id(cls):
return db.Column(db.String(8),
db.ForeignKey(Hs07.id),
primary_key=True)
class Sitc(ProdAttr):
__tablename__ = 'attr_sitc'
sitc = db.Column(db.String(6))
pini = db.Column(db.Float())
pini_class = db.Column(db.Integer)
name = db.relationship("Sitc_name", backref="sitc", lazy="dynamic")
yodp_product = db.relationship("db_data.sitc_models.Yodp",
backref='product',
lazy='dynamic')
yop_product = db.relationship("db_data.sitc_models.Yop",
backref='product',
lazy='dynamic')
yp_product = db.relationship("db_data.sitc_models.Yp",
backref='product',
lazy='dynamic')
classification = "sitc"
class App(db.Model, AutoSerialize):
__tablename__ = 'explore_app'
id = db.Column(db.Integer, primary_key=True)
type = db.Column(db.String(20))
name = db.Column(db.String(20))
d3plus = db.Column(db.String(20))
color = db.Column(db.String(7))
def get_name(self):
# lang = getattr(g, "locale", "en")
# return getattr(self,"name_"+lang)
return self.name
def __repr__(self):
return '<App %r>' % (self.type)
class HsTopTrade(object):
top_export_dest = db.Column(db.String(5))
top_import_dest = db.Column(db.String(5))
@declared_attr
def top_export_hs4(cls):
return db.Column(db.String(6), db.ForeignKey(Hs92.id))
@declared_attr
def top_export_hs6(cls):
return db.Column(db.String(8), db.ForeignKey(Hs92.id))
@declared_attr
def top_import_hs4(cls):
return db.Column(db.String(6), db.ForeignKey(Hs92.id))
@declared_attr
def top_import_hs6(cls):
return db.Column(db.String(8), db.ForeignKey(Hs92.id))
class Yp(db.Model, AutoSerialize):
__tablename__ = 'sitc_yp'
year = db.Column(db.Integer, primary_key=True)
sitc_id = db.Column(db.String(8), db.ForeignKey(Sitc.id), primary_key=True)
export_val = db.Column(db.Numeric(16, 2))
import_val = db.Column(db.Numeric(16, 2))
pci = db.Column(db.Float())
pci_rank = db.Column(db.Integer)
pci_rank_delta = db.Column(db.Integer)
top_exporter = db.Column(db.String(5), db.ForeignKey(Country.id))
top_importer = db.Column(db.String(5), db.ForeignKey(Country.id))
def __repr__(self):
return '<Yp %d.%s>' % (self.year, self.sitc_id)
class Hs92(ProdAttr):
__tablename__ = 'attr_hs92'
hs92 = db.Column(db.String(6))
name = db.relationship("Hs92_name", backref="hs", lazy="dynamic")
yodp_product = db.relationship("db_data.hs92_models.Yodp",
backref='product',
lazy='dynamic')
yop_product = db.relationship("db_data.hs92_models.Yop",
backref='product',
lazy='dynamic')
yp_product = db.relationship("db_data.hs92_models.Yp",
backref='product',
lazy='dynamic')
classification = "hs92"
image_author = db.Column(db.String(200))
image_link = db.Column(db.String(200))
palette = db.Column(db.String(200))
class ProdNameAttr(object):
lang = db.Column(db.String(5), primary_key=True)
name = db.Column(db.String(255))
keywords = db.Column(db.String(255))
desc = db.Column(db.Text())
gender = db.Column(db.String(1))
plural = db.Column(db.Boolean())
article = db.Column(db.Boolean())
def __repr__(self):
return '<Prod %r>' % (self.name)
class Sitc_name(db.Model, AutoSerialize):
__tablename__ = 'attr_sitc_name'
sitc_id = db.Column(db.String(8), db.ForeignKey(Sitc.id), primary_key=True)
lang = db.Column(db.String(5), primary_key=True)
name = db.Column(db.String(255))
keywords = db.Column(db.String(255))
desc = db.Column(db.Text())
gender = db.Column(db.String(1))
plural = db.Column(db.Boolean())
article = db.Column(db.Boolean())
def __repr__(self):
return '<Sitc Name %r:%r>' % (self.sitc_id, self.lang)
class Yo(db.Model, AutoSerialize):
__tablename__ = 'sitc_yo'
year = db.Column(db.Integer, primary_key=True)
origin_id = db.Column(db.String(5),
db.ForeignKey(Country.id),
primary_key=True)
export_val = db.Column(db.Numeric(16, 2))
import_val = db.Column(db.Numeric(16, 2))
top_export = db.Column(db.String(8), db.ForeignKey(Sitc.id))
top_import = db.Column(db.String(8), db.ForeignKey(Sitc.id))
top_export_dest = db.Column(db.String(5))
top_import_dest = db.Column(db.String(5))
def __repr__(self):
return '<Yo %d.%s>' % (self.year, self.origin_id)
class Hs07(ProdAttr):
__tablename__ = 'attr_hs07'
hs07 = db.Column(db.String(6))
name = db.relationship("Hs07_name", backref="hs", lazy="dynamic")
yodp_product = db.relationship("db_data.hs07_models.Yodp",
backref='product',
lazy='dynamic')
yop_product = db.relationship("db_data.hs07_models.Yop",
backref='product',
lazy='dynamic')
yp_product = db.relationship("db_data.hs07_models.Yp",
backref='product',
lazy='dynamic')
classification = "hs07"
class Build_name(db.Model, AutoSerialize):
__tablename__ = 'explore_build_name'
# build_id = db.Column(db.Integer, db.ForeignKey(Build.id), primary_key = True)
name_id = db.Column(db.Integer, primary_key=True)
lang = db.Column(db.String(5), primary_key=True)
name = db.Column(db.String(255))
short_name = db.Column(db.String(30))
question = db.Column(db.String(255))
category = db.Column(db.String(30))
def __repr__(self):
return '<Build Name %r:%r>' % (self.name_id, self.lang)
class Country_name(db.Model, AutoSerialize):
__tablename__ = 'attr_country_name'
origin_id = db.Column(db.String(5),
db.ForeignKey(Country.id),
primary_key=True)
lang = db.Column(db.String(5), primary_key=True)
name = db.Column(db.String(255))
gender = db.Column(db.String(1))
plural = db.Column(db.Boolean())
article = db.Column(db.Boolean())
def __repr__(self):
return '<Country Name %r:%r>' % (self.origin_id, self.lang)
class Build(db.Model, AutoSerialize):
__tablename__ = 'explore_build'
app_id = db.Column(db.Integer, db.ForeignKey(App.id), primary_key=True)
name_id = db.Column(db.Integer,
db.ForeignKey(Build_name.name_id),
primary_key=True)
trade_flow = db.Column(db.String(20))
origin = db.Column(db.String(20))
dest = db.Column(db.String(20))
product = db.Column(db.String(20))
defaults = {"hs": "0101", "sitc": "5722", "country": "pry"}
app = db.relationship('App', backref=db.backref('Builds', lazy='dynamic'))
# name = db.relationship("Build_name", backref="build", lazy="joined")
def get_year(self):
if not self.year:
return available_years["hs"][-1]
elif "." in self.year:
years = self.year.split(".")
return "{0} - {1}".format(years[0], years[1])
else:
return self.year
def get_short_name(self, lang=None):
lang = lang or getattr(g, "locale", "en")
build_name = Build_name.query.filter_by(name_id=self.name_id,
lang=lang).first()
if build_name:
return build_name.short_name
else:
return ""
def get_category(self, lang=None):
lang = lang or getattr(g, "locale", "en")
build_name = Build_name.query.filter_by(name_id=self.name_id,
lang=lang).first()
if build_name:
return build_name.category
else:
return ""
def l18n_name(self, name, replace_term, item, lang):
item_w_article = item.get_name(lang, article=True)
attr_name = item.get_attr_name(lang)
name_as_words = name.split(" ")
replace_term_word = [s for s in name_as_words if replace_term in s][0]
prev_word_index = name_as_words.index(replace_term_word) - 1
prev_word = name_as_words[prev_word_index]
''' French! '''
if lang == "fr":
if prev_word == "<de>":
prev_word = "de"
if attr_name.article:
if item_w_article.startswith("le"):
prev_word = "du"
item_w_article = item_w_article.replace("le ", "")
if item_w_article.startswith("les"):
prev_word = "des"
item_w_article = item_w_article.replace("les ", "")
if prev_word == u"<à>":
prev_word = u"à"
if attr_name.article:
if item_w_article.startswith("le"):
prev_word = "au"
item_w_article = item_w_article.replace("le ", "")
if item_w_article.startswith("les"):
prev_word = "aux"
item_w_article = item_w_article.replace("les ", "")
''' Spanish! '''
if lang == "es":
if prev_word == "<de>":
prev_word = "de"
if attr_name.article:
if item_w_article.startswith("el"):
prev_word = "del"
item_w_article = item_w_article.replace("el ", "")
if prev_word == "<a>":
prev_word = "a"
if attr_name.article:
if item_w_article.startswith("el"):
prev_word = "al"
item_w_article = item_w_article.replace("el ", "")
''' Italian! '''
if lang == "it":
if attr_name.article:
if item_w_article.startswith("il "): ending = "l"
if item_w_article.startswith("lo "): ending = "llo"
if item_w_article.startswith("l'"): ending = "ll'"
if item_w_article.startswith("i "): ending = "i"
if item_w_article.startswith("gli "): ending = "gli"
if item_w_article.startswith("la "): ending = "lla"
if item_w_article.startswith("le "): ending = "lle"
item_w_article = item_w_article.replace("il ", "").replace("lo ", "").replace("l'", "") \
.replace("i ", "").replace("gli ", "").replace("la ", "").replace("le ", "")
if prev_word == "<da>":
prev_word = "da" + ending
if prev_word == "<di>":
prev_word = "de" + ending
if prev_word == "<in>":
prev_word = "ne" + ending
else:
prev_word = prev_word.replace("<", "").replace(">", "")
name_as_words[prev_word_index] = prev_word
# raise Exception(name_as_words)
name = " ".join(name_as_words)
# if replace_term == "<dest>":
# raise Exception(name.replace(replace_term, unicode(item_w_article)))
return name.replace(replace_term,
u"<strong>{0}</strong>".format(item_w_article))
def get_name(self, lang=None):
lang = lang or getattr(g, "locale", "en")
build_name = Build_name.query.filter_by(name_id=self.name_id,
lang=lang).first()
if build_name:
name = build_name.name
else:
return ""
if "<origin>" in name:
name = self.l18n_name(name, "<origin>", self.origin, lang)
# name = name.replace("<origin>", self.origin.get_name(lang, article=True))
if "<dest>" in name:
name = self.l18n_name(name, "<dest>", self.dest, lang)
# name = name.replace("<dest>", self.dest.get_name(lang, article=True))
if "<product>" in name:
name = name.replace(
"<product>", u"<strong>{0}</strong>".format(
self.product.get_name(lang, article=True)))
return name
def get_question(self, lang=None):
lang = lang or getattr(g, "locale", "en")
build_q = Build_name.query.filter_by(name_id=self.name_id,
lang=lang).first()
if build_q:
q = build_q.question
else:
return ""
if "<origin>" in q:
q = q.replace("<origin>", self.origin.get_name(lang))
if "<dest>" in q:
q = q.replace("<dest>", self.dest.get_name(lang))
if "<product>" in q:
q = q.replace("<product>", self.product.get_name(lang))
return q
def get_ui(self, ui_type):
return self.ui.filter(UI.type == ui_type).first()
def set_options(self,
origin=None,
dest=None,
product=None,
classification="hs",
year=2010):
if year:
self.year = year
if self.origin != "show" and self.origin != "all":
if isinstance(origin, Country):
self.origin = origin
else:
self.origin = Country.query.filter_by(
id_3char=origin).first_or_404()
if self.dest != "show" and self.dest != "all":
if isinstance(dest, Country):
self.dest = dest
else:
self.dest = Country.query.filter_by(
id_3char=dest).first_or_404()
if self.product != "show" and self.product != "all":
tbl = Sitc if classification == "sitc" else Hs
if isinstance(product, (Sitc, Hs)):
self.product = product
else:
self.product = tbl.query.filter(
getattr(tbl, classification) == product).first()
if classification:
self.classification = classification
'''Returns the URL for the specific build.'''
def url(self, year=None):
year = year or self.year
if not year:
year = available_years[self.classification][-1]
if "." in str(year) and self.app.type != "stacked":
year = year.split(".")[1]
if "." not in str(year) and self.app.type == "stacked":
year = "{0}.{1}".format(available_years[self.classification][0],
available_years[self.classification][-1])
origin, dest, product = [self.origin, self.dest, self.product]
if isinstance(origin, Country):
origin = origin.id_3char
if isinstance(dest, Country):
dest = dest.id_3char
if isinstance(product, Hs):
product = product.hs
if isinstance(product, Sitc):
product = product.sitc
url = '{0}/{1}/{2}/{3}/{4}/{5}/{6}/'.format(self.app.type,
self.classification,
self.trade_flow, origin,
dest, product, year)
return url
'''Returns the data URL for the specific build.'''
def data_url(self, year=None):
year = year or self.year
if not year:
year = available_years[self.classification][-1]
origin, dest, product = [self.origin, self.dest, self.product]
if (isinstance(product, Hs) and dest == "all" and isinstance(origin, Country)) or \
(isinstance(product, Sitc) and dest == "all" and isinstance(origin, Country)):
product = "show"
elif isinstance(product, Hs):
product = product.hs
elif isinstance(product, Sitc):
product = product.sitc
if isinstance(origin, Country):
origin = origin.id_3char
if isinstance(dest, Country):
dest = dest.id_3char
url = '/{0}/{1}/{2}/{3}/{4}/{5}/'.format(self.classification,
self.trade_flow, year, origin,
dest, product)
return url
def attr_type(self):
if self.origin == "show":
return "origin"
if self.dest == "show":
return "dest"
if self.classification == "sitc":
return "sitc"
return "hs"
def attr_url(self):
if self.origin == "show" or self.dest == "show":
return url_for('attr.attrs', attr='country')
if self.classification == "sitc":
return url_for('attr.attrs', attr='sitc')
return url_for('attr.attrs', attr='hs')
def get_tbl(self):
if self.classification == "hs":
models = hs_models
else:
models = sitc_models
if isinstance(self.origin, Country) and isinstance(self.dest, Country):
return getattr(models, "Yodp")
if isinstance(self.origin, Country) and isinstance(
self.product, (Sitc, Hs)):
return getattr(models, "Yodp")
if isinstance(self.origin, Country) and self.product == "show":
return getattr(models, "Yop")
if isinstance(self.origin, Country) and self.dest == "show":
return getattr(models, "Yod")
if isinstance(self.product, (Sitc, Hs)) and self.origin == "show":
return getattr(models, "Yop")
def top_stats(self, entities=5):
tbl = self.get_tbl()
query = tbl.query
if self.trade_flow == "export":
query = query.order_by(
tbl.export_val.desc()).filter(tbl.export_val != None)
sum_query = db.session.query(db.func.sum(tbl.export_val))
elif self.trade_flow == "import":
query = query.order_by(
tbl.import_val.desc()).filter(tbl.import_val != None)
sum_query = db.session.query(db.func.sum(tbl.import_val))
elif self.trade_flow == "net_export":
query = db.session \
.query(tbl, tbl.export_val - tbl.import_val) \
.filter((tbl.export_val - tbl.import_val) > 0) \
.order_by(desc(tbl.export_val - tbl.import_val))
sum_query = db.session.query(
db.func.sum(tbl.export_val - tbl.import_val)).filter(
(tbl.export_val - tbl.import_val) > 0)
elif self.trade_flow == "net_import":
query = db.session \
.query(tbl, tbl.import_val - tbl.export_val) \
.filter((tbl.import_val - tbl.export_val) > 0) \
.order_by(desc(tbl.import_val - tbl.export_val))
sum_query = db.session.query(
db.func.sum(tbl.import_val - tbl.export_val)).filter(
(tbl.import_val - tbl.export_val) > 0)
year = self.year
if "." in str(year):
year = str(year).split(".")[1]
sum_query = sum_query.filter_by(year=year)
query = query.filter_by(year=year)
if isinstance(self.origin, Country):
query = query.filter_by(origin_id=self.origin.id)
sum_query = sum_query.filter_by(origin_id=self.origin.id)
if isinstance(self.dest, Country):
query = query.filter_by(dest_id=self.dest.id)
sum_query = sum_query.filter_by(dest_id=self.dest.id)
if isinstance(self.product, Sitc) and self.app.type != "rings":
query = query.filter_by(sitc_id=self.product.id)
sum_query = sum_query.filter_by(sitc_id=self.product.id)
if isinstance(self.product, Hs) and self.app.type != "rings":
query = query.filter_by(hs_id=self.product.id)
sum_query = sum_query.filter_by(hs_id=self.product.id)
sum = sum_query.first()[0]
show_attr = {
self.origin: "origin",
self.dest: "dest",
self.product: "product"
}
if show_attr.get("show", "product") == "origin":
attr_name = _("Origin Country")
attr_id_name = _("ID")
elif show_attr.get("show", "product") == "dest":
attr_name = _("Destination Country")
attr_id_name = _("ID")
elif show_attr.get("show", "product") == "product":
attr_name = _("Product Name")
attr_id_name = "SITC" if self.classification == "sitc" else "HS"
header = [
"Rank", attr_id_name, attr_name,
self.trade_flow.title() + " Value", "Share"
]
stats = []
for s in query.limit(entities).all():
if self.trade_flow == "export":
val = s.export_val
attr = getattr(s, show_attr.get("show", "product"))
if self.trade_flow == "import":
val = s.import_val
attr = getattr(s, show_attr.get("show", "product"))
if self.trade_flow == "net_export":
attr = getattr(s[0], show_attr.get("show", "product"))
val = s[1]
if self.trade_flow == "net_import":
attr = getattr(s[0], show_attr.get("show", "product"))
val = s[1]
stat = {"attr": attr, "value": val, "share": 0}
if (sum):
stat["share"] = (val / sum) * 100
stats.append(stat)
return {"total": sum, "entries": stats, "header": header}
def get_ui(self):
ui = []
if isinstance(self.origin, Country):
# country_list = Country.query \
# .filter(not_(Country.id.in_(excluded_countries))) \
# .filter(Country.id_3char != None)
# country_list = [c.serialize() for c in country_list]
# country_list = sorted(country_list, key=lambda k: k['name'])
country = {
"id": "origin",
"name": _("Origin"),
"current": self.origin.serialize(),
"url": url_for('attr.attrs', attr='country')
# "data": country_list
}
ui.append(country)
if isinstance(self.dest, Country):
# country_list = Country.query \
# .filter(not_(Country.id.in_(excluded_countries))) \
# .filter(Country.id_3char != None)
# country_list = [c.serialize() for c in country_list]
# country_list = sorted(country_list, key=lambda k: k['name'])
country = {
"id": "destination",
"name": _("Destination"),
"current": self.dest.serialize(),
# "data": country_list,
"url": url_for('attr.attrs', attr='country')
}
ui.append(country)
if isinstance(self.product, (Sitc, Hs)):
# if self.classification == "sitc":
# product_list = Sitc.query \
# .filter(func.char_length(Sitc.id)==6).all()
# else:
# product_list = Hs.query \
# .filter(func.char_length(Hs.id)==6).all()
# product_list = [p.serialize() for p in product_list]
# product_list = sorted(product_list, key=lambda k: k['name'])
product = {
"id": "product",
"name": _("Product"),
"current": self.product.serialize(),
# "data": product_list,
"url": url_for('attr.attrs', attr=self.classification)
}
ui.append(product)
trade_flow = {
"id":
"trade_flow",
"name":
_("Trade Flow"),
"current":
self.trade_flow,
"data":
[{
"name": _("Export"),
"display_id": "export"
}, {
"name": _("Import"),
"display_id": "import"
}
# {"name":_("Net Export"), "display_id":"net_export"},
# {"name":_("Net Import"), "display_id":"net_import"}
]
}
classification = {
"id": "classification",
"name": _("Classification"),
"current": self.classification,
"data": ["HS", "SITC"]
}
ui.append(trade_flow)
if "." in self.year:
year_parts = [int(y) for y in self.year.split(".")]
if len(year_parts) == 2:
years = range(year_parts[0], year_parts[1] + 1)
else:
years = range(year_parts[0], year_parts[1] + 1, year_parts[2])
start_year = {
"id": "start_year",
"name": _("Start Year"),
"current": years[0],
"data": available_years[self.classification][::-1]
}
end_year = {
"id": "end_year",
"name": _("End Year"),
"current": years[-1],
"data": available_years[self.classification][::-1]
}
ui = ui + [start_year, end_year]
else:
year = {
"id": "year",
"name": _("Year"),
"current": int(self.year),
"data": available_years[self.classification][::-1]
}
ui.append(year)
ui.append(classification)
return ui
def __repr__(self):
return '<Build %d:%s>' % (self.name_id, self.app.type)
def sitc_id(cls):
return db.Column(db.String(8),
db.ForeignKey(Sitc.id),
primary_key=True)
def top_importer(cls):
return db.Column(db.String(5), db.ForeignKey(Country.id))
def top_import_hs6(cls):
return db.Column(db.String(8), db.ForeignKey(Hs92.id))
class Rca(object):
export_rca = db.Column(db.Float())
import_rca = db.Column(db.Float())
class Yo(db.Model, AutoSerialize):
__tablename__ = 'attr_yo'
year = db.Column(db.Integer, primary_key=True)
origin_id = db.Column(db.String(5),
db.ForeignKey(Country.id),
primary_key=True)
eci = db.Column(db.Float())
neci = db.Column(db.Float())
eci_rank = db.Column(db.Integer)
eci_rank_delta = db.Column(db.Integer)
opp_value = db.Column(db.Float())
population = db.Column(db.Integer)
gdp = db.Column(db.Numeric(16, 2))
gdp_pc_constant = db.Column(db.Numeric(16, 2))
gdp_pc_current = db.Column(db.Numeric(16, 2))
gdp_pc_constant_ppp = db.Column(db.Numeric(16, 2))
gdp_pc_current_ppp = db.Column(db.Numeric(16, 2))
leader = db.Column(db.String(100))
magic = db.Column(db.Float())
pc_constant = db.Column(db.Float())
pc_current = db.Column(db.Float())
notpc_constant = db.Column(db.Float())
def __repr__(self):
return '<Yo %d.%s>' % (self.year, self.origin_id)
def dest_id(cls):
return db.Column(db.String(5),
db.ForeignKey(Country.id),
primary_key=True)
class Pci(object):
pci = db.Column(db.Float())
pci_rank = db.Column(db.Integer)
pci_rank_delta = db.Column(db.Integer)
class BaseProd(db.Model, AutoSerialize):
__abstract__ = True
year = db.Column(db.Integer(), primary_key=True)
import_val = db.Column(db.Numeric(16, 2))
export_val = db.Column(db.Numeric(16, 2))
export_val_growth_pct = db.Column(db.Float())
export_val_growth_pct_5 = db.Column(db.Float())
export_val_growth_val = db.Column(db.Numeric(16, 2))
export_val_growth_val_5 = db.Column(db.Numeric(16, 2))
import_val_growth_pct = db.Column(db.Float())
import_val_growth_pct_5 = db.Column(db.Float())
import_val_growth_val = db.Column(db.Numeric(16, 2))
import_val_growth_val_5 = db.Column(db.Numeric(16, 2))
class Country(db.Model, AutoSerialize):
__tablename__ = 'attr_country'
id = db.Column(db.String(5), primary_key=True)
id_2char = db.Column(db.String(2))
id_3char = db.Column(db.String(3))
id_num = db.Column(db.String(20))
color = db.Column(db.String(7))
comtrade_name = db.Column(db.String(255))
borders_land = db.Column(db.String(255))
borders_maritime = db.Column(db.String(255))
image_author = db.Column(db.String(200))
image_link = db.Column(db.String(200))
palette = db.Column(db.String(200))
name = db.relationship("Country_name", backref="country", lazy="dynamic")
# attr_yo_origin = db.relationship("db_attr.models.Yo", backref = 'origin', lazy = 'dynamic')
attr_yo = db.relationship("db_attr.models.Yo",
backref='country',
lazy='dynamic')
hs92_yo = db.relationship("db_data.hs92_models.Yo",
backref='country',
lazy='dynamic')
hs96_yo = db.relationship("db_data.hs96_models.Yo",
backref='country',
lazy='dynamic')
hs02_yo = db.relationship("db_data.hs02_models.Yo",
backref='country',
lazy='dynamic')
hs07_yo = db.relationship("db_data.hs07_models.Yo",
backref='country',
lazy='dynamic')
sitc_yo = db.relationship("db_data.sitc_models.Yo",
backref='country',
lazy='dynamic')
hs92_yodp_origin = db.relationship(
"db_data.hs92_models.Yodp",
primaryjoin=('db_data.hs92_models.Yodp.origin_id == Country.id'),
backref='origin',
lazy='dynamic')
hs92_yodp_dest = db.relationship(
"db_data.hs92_models.Yodp",
primaryjoin=('db_data.hs92_models.Yodp.dest_id == Country.id'),
backref='dest',
lazy='dynamic')
hs92_yod_dest = db.relationship(
"db_data.hs92_models.Yod",
primaryjoin=('db_data.hs92_models.Yod.dest_id == Country.id'),
backref='dest',
lazy='dynamic')
hs92_yod_origin = db.relationship(
"db_data.hs92_models.Yod",
primaryjoin=('db_data.hs92_models.Yod.origin_id == Country.id'),
backref='origin',
lazy='dynamic')
hs92_yop_origin = db.relationship(
"db_data.hs92_models.Yop",
primaryjoin=('db_data.hs92_models.Yop.origin_id == Country.id'),
backref='origin',
lazy='dynamic')
hs96_yodp_origin = db.relationship(
"db_data.hs96_models.Yodp",
primaryjoin=('db_data.hs96_models.Yodp.origin_id == Country.id'),
backref='origin',
lazy='dynamic')
hs96_yodp_dest = db.relationship(
"db_data.hs96_models.Yodp",
primaryjoin=('db_data.hs96_models.Yodp.dest_id == Country.id'),
backref='dest',
lazy='dynamic')
hs96_yod_dest = db.relationship(
"db_data.hs96_models.Yod",
primaryjoin=('db_data.hs96_models.Yod.dest_id == Country.id'),
backref='dest',
lazy='dynamic')
hs96_yod_origin = db.relationship(
"db_data.hs96_models.Yod",
primaryjoin=('db_data.hs96_models.Yod.origin_id == Country.id'),
backref='origin',
lazy='dynamic')
hs96_yop_origin = db.relationship(
"db_data.hs96_models.Yop",
primaryjoin=('db_data.hs96_models.Yop.origin_id == Country.id'),
backref='origin',
lazy='dynamic')
hs02_yodp_origin = db.relationship(
"db_data.hs02_models.Yodp",
primaryjoin=('db_data.hs02_models.Yodp.origin_id == Country.id'),
backref='origin',
lazy='dynamic')
hs02_yodp_dest = db.relationship(
"db_data.hs02_models.Yodp",
primaryjoin=('db_data.hs02_models.Yodp.dest_id == Country.id'),
backref='dest',
lazy='dynamic')
hs02_yod_dest = db.relationship(
"db_data.hs02_models.Yod",
primaryjoin=('db_data.hs02_models.Yod.dest_id == Country.id'),
backref='dest',
lazy='dynamic')
hs02_yod_origin = db.relationship(
"db_data.hs02_models.Yod",
primaryjoin=('db_data.hs02_models.Yod.origin_id == Country.id'),
backref='origin',
lazy='dynamic')
hs02_yop_origin = db.relationship(
"db_data.hs02_models.Yop",
primaryjoin=('db_data.hs02_models.Yop.origin_id == Country.id'),
backref='origin',
lazy='dynamic')
hs07_yodp_origin = db.relationship(
"db_data.hs07_models.Yodp",
primaryjoin=('db_data.hs07_models.Yodp.origin_id == Country.id'),
backref='origin',
lazy='dynamic')
hs07_yodp_dest = db.relationship(
"db_data.hs07_models.Yodp",
primaryjoin=('db_data.hs07_models.Yodp.dest_id == Country.id'),
backref='dest',
lazy='dynamic')
hs07_yod_dest = db.relationship(
"db_data.hs07_models.Yod",
primaryjoin=('db_data.hs07_models.Yod.dest_id == Country.id'),
backref='dest',
lazy='dynamic')
hs07_yod_origin = db.relationship(
"db_data.hs07_models.Yod",
primaryjoin=('db_data.hs07_models.Yod.origin_id == Country.id'),
backref='origin',
lazy='dynamic')
hs07_yop_origin = db.relationship(
"db_data.hs07_models.Yop",
primaryjoin=('db_data.hs07_models.Yop.origin_id == Country.id'),
backref='origin',
lazy='dynamic')
sitc_yodp_origin = db.relationship(
"db_data.sitc_models.Yodp",
primaryjoin=('db_data.sitc_models.Yodp.origin_id == Country.id'),
backref='origin',
lazy='dynamic')
sitc_yodp_dest = db.relationship(
"db_data.sitc_models.Yodp",
primaryjoin=('db_data.sitc_models.Yodp.dest_id == Country.id'),
backref='dest',
lazy='dynamic')
sitc_yod_dest = db.relationship(
"db_data.sitc_models.Yod",
primaryjoin=('db_data.sitc_models.Yod.dest_id == Country.id'),
backref='dest',
lazy='dynamic')
sitc_yod_origin = db.relationship(
"db_data.sitc_models.Yod",
primaryjoin=('db_data.sitc_models.Yod.origin_id == Country.id'),
backref='origin',
lazy='dynamic')
sitc_yop_origin = db.relationship(
"db_data.sitc_models.Yop",
primaryjoin=('db_data.sitc_models.Yop.origin_id == Country.id'),
backref='origin',
lazy='dynamic')
# sitc_yodp_origin = db.relationship("db_sitc.models.Yodp", primaryjoin = ('db_sitc.models.Yodp.origin_id == Country.id'), backref = 'origin', lazy = 'dynamic')
# sitc_yodp_dest = db.relationship("db_sitc.models.Yodp", primaryjoin = ('db_sitc.models.Yodp.dest_id == Country.id'), backref = 'dest', lazy = 'dynamic')
# sitc_yod_dest = db.relationship("db_sitc.models.Yod", primaryjoin = ('db_sitc.models.Yod.dest_id == Country.id'), backref = 'dest', lazy = 'dynamic')
# sitc_yod_origin = db.relationship("db_sitc.models.Yod", primaryjoin = ('db_sitc.models.Yod.origin_id == Country.id'), backref = 'origin', lazy = 'dynamic')
# sitc_yop_origin = db.relationship("db_sitc.models.Yop", primaryjoin = ('db_sitc.models.Yop.origin_id == Country.id'), backref = 'origin', lazy = 'dynamic')
def next(self):
c = self.__class__
return self.query.filter(c.id > self.id) \
.filter(~c.id.in_(excluded_countries)) \
.filter(c.id_3char != None) \
.filter(func.char_length(c.id)==len(self.id)) \
.order_by(c.id).first()
def prev(self):
c = self.__class__
return self.query.filter(c.id < self.id) \
.filter(~c.id.in_(excluded_countries)) \
.filter(c.id_3char != None) \
.filter(func.char_length(c.id)==len(self.id)) \
.order_by(c.id.desc()).first()
def get_attr_name(self, lang=None):
lang = lang or getattr(g, "locale", "en")
return self.name.filter_by(lang=lang).first().name
def get_name(self, lang=None, article=None, verb=None):
lang = lang or getattr(g, "locale", "en")
name = self.name.filter_by(lang=lang).first()
vowels = ['a', 'e', 'i', 'o', 'u', 'y']
if name:
txt = name.name
plural = getattr(name, "plural", 0)
gender = getattr(name, "gender", "m")
needed = getattr(name, "article", 0)
# Romance Langs
if lang in ('es', 'pt', 'fr', 'nl', 'it', 'de'):
if article == "the" or article is True:
if gender == "m" and needed:
article = _("article_the_m_p") if plural else _(
"article_the_m")
elif gender == "f" and needed:
article = _("article_the_f_p") if plural else _(
"article_the_f")
else:
article = ""
if article == "of":
if not needed:
article = _("article_of")
elif gender == "m":
article = _("article_of_m_p") if plural else _(
"article_of_m")
elif gender == "f":
article = _("article_of_f_p") if plural else _(
"article_of_f")
if article:
if lang == "fr":
if article.lower()[-1] in vowels and name.name.lower(
)[0] in vowels:
txt = u"{}'{}".format("".join(article[:-1]),
name.name)
else:
txt = u"{} {}".format(article, name.name)
# Turkish
elif lang == "tr":
if article == "of":
if name.name[-1] in vowels:
txt = u"{}'nın".format(name.name)
txt = u"{}'ın".format(name.name)
# English
elif lang == "en":
if needed and (article is True or article == "the"):
txt = u"{} {}".format("the", name.name)
elif needed and article:
txt = u"{} {} {}".format(article, "the", name.name)
elif article and (article is not True and article != "the"):
txt = u"{} {}".format(article, name.name)
if verb == "is":
if lang != "en":
verb = _("verb_is_p") if plural else _("verb_is")
txt = u"{} {}".format(txt, verb)
return txt
# ''' French '''
# if lang == "fr" and name.article and article=="the":
# if name.plural:
# return u"les {0}".format(name.name)
# elif any(vowel == name.name[0].lower() for vowel in ['a', 'e', 'i', 'o', 'u', 'y']):
# return u"l'{0}".format(name.name)
# elif name.gender == "m":
# return u"le {0}".format(name.name)
# elif name.gender == "f":
# return u"la {0}".format(name.name)
#
# if lang == "fr" and name.article and article=="of":
# if name.plural:
# return u"des {0}".format(name.name)
# elif any(vowel == name.name[0].lower() for vowel in ['a', 'e', 'i', 'o', 'u', 'y']):
# return u"d'{0}".format(name.name)
# elif name.gender == "m":
# return u"du {0}".format(name.name)
# elif name.gender == "f":
# return u"de {0}".format(name.name)
#
# ''' Spanish '''
# if lang == "es" and name.article and article:
# if name.gender == "m":
# if name.plural:
# return u"los {0}".format(name.name)
# return u"el {0}".format(name.name)
# elif name.gender == "f":
# if name.plural:
# return u"las {0}".format(name.name)
# return u"la {0}".format(name.name)
#
# ''' Italian '''
# if lang == "it" and name.article and article:
# if name.gender == "m":
# if name.plural:
# if any(vowel == name.name[0].lower() for vowel in ['a', 'e', 'i', 'o', 'u', 'y']):
# return u"gli {0}".format(name.name)
# if (name.name[0].lower() == "s"
# and any(vowel == name.name[0].lower() for vowel in ['a', 'e', 'i', 'o', 'u', 'y'])) \
# or name.name[0].lower() == "z":
# return u"gli {0}".format(name.name)
# return u"i {0}".format(name.name)
# else:
# if (name.name[0].lower() == "s"
# and any(vowel == name.name[0].lower() for vowel in ['a', 'e', 'i', 'o', 'u', 'y'])) \
# or name.name[0].lower() == "z":
# return u"lo {0}".format(name.name)
# if any(vowel == name.name[0].lower() for vowel in ['a', 'e', 'i', 'o', 'u', 'y']):
# return u"l'{0}".format(name.name)
# return u"il {0}".format(name.name)
#
# elif name.gender == "f":
# if name.plural:
# if any(vowel == name.name[0].lower() for vowel in ['a', 'e', 'i', 'o', 'u', 'y']):
# return u"le {0}".format(name.name)
# return u"le {0}".format(name.name)
# else:
# if any(vowel == name.name[0].lower() for vowel in ['a', 'e', 'i', 'o', 'u', 'y']):
# return u"l'{0}".format(name.name)
# return u"la {0}".format(name.name)
#
# return name.name
# return ""
def get_display_id(self):
return self.id_3char
def get_attr_yo(self, year=None):
year = year or available_years["country"][-1]
yo = filter(lambda yo: yo.year == year, self.attr_yo)
if len(yo): return yo[0]
return None
def get_abbrv(self, lang=None):
return self.id_3char if self.id_3char else ""
def get_icon(self):
return "/static/img/icons/country/country_%s.png" % (self.id)
def get_image(self):
if self.image_link:
return "/static/img/headers/country/{}.jpg".format(self.id)
else:
return "/static/img/headers/country/{}.jpg".format(self.id[:2])
def get_author(self):
if self.image_link:
return {"link": self.image_link, "name": self.image_author}
else:
parent = self.__class__.query.get(self.id[:2])
return {"link": parent.image_link, "name": parent.image_author}
def get_top(self, limit=10, year=None):
from oec.db_data.hs92_models import Yp
year = year or available_years["country"][-1]
return Yp.query.filter_by(year=year, top_exporter=self.id)\
.order_by(Yp.export_val.desc()).limit(limit).all()
def get_profile_url(self):
if self.id_3char:
return u"/{}/profile/country/{}/".format(g.locale, self.id_3char)
else:
return u"/{}/profile/country/".format(g.locale)
def serialize(self, lang="en"):
auto_serialized = super(Country, self).serialize()
auto_serialized["icon"] = self.get_icon()
auto_serialized["image"] = self.get_image()
try:
auto_serialized["display_id"] = auto_serialized.pop("id_3char")
except KeyError:
auto_serialized["display_id"] = None
return auto_serialized
def borders(self, maritime=False):
if maritime:
if not self.borders_maritime: return None
border_countries = ast.literal_eval(self.borders_maritime)
else:
if not self.borders_land: return None
border_countries = ast.literal_eval(self.borders_land)
border_countries = self.query.filter(
self.__class__.id.in_(border_countries)).all()
return border_countries
def __repr__(self):
return '<Country %s>' % (self.id)
class ProdAttr(db.Model, AutoSerialize):
__abstract__ = True
id = db.Column(db.String(8), primary_key=True)
conversion = db.Column(db.String(6))
color = db.Column(db.String(7))
def next(self):
c = self.__class__
return self.query.filter(c.id > self.id).filter(
func.char_length(c.id) == len(self.id)).order_by(c.id).first()
def prev(self):
c = self.__class__
return self.query.filter(c.id < self.id).filter(
func.char_length(c.id) == len(self.id)).order_by(
c.id.desc()).first()
def get_attr_name(self, lang=None):
lang = lang or getattr(g, "locale", "en")
attr_name = self.name.filter_by(lang=lang).first()
if attr_name:
return attr_name.name
return u""
def get_name(self, lang=None, article=None, verb=None):
lang = lang or getattr(g, "locale", "en")
name = self.name.filter_by(lang=lang).first()
if name:
if lang == "en" and name.article and article:
return u"The {}".format(name.name)
if lang == "en" and verb:
verb = "are" if name.plural else "is"
return u"{} {}".format(name.name, verb)
return name.name
return ""
def get_keywords(self, lang=None):
lang = lang or getattr(g, "locale", "en")
name = self.name.filter_by(lang=lang).first()
if name:
return name.keywords
return ""
def get_author(self):
if hasattr(self, "image_link"):
obj = None
if self.image_link:
obj = self
if obj == None and len(self.id) >= 8:
parent = self.__class__.query.get(self.id[:6])
if parent.image_link:
obj = parent
if obj == None and len(self.id) >= 4:
parent = self.__class__.query.get(self.id[:2])
if parent.image_link:
obj = parent
if obj:
obj = {"link": obj.image_link, "name": obj.image_author}
return obj
else:
return None
def get_image(self):
if hasattr(self, "image_link"):
if self.image_link:
return "/static/img/headers/hs/{}.jpg".format(self.id)
if len(self.id) >= 8:
parent = self.__class__.query.get(self.id[:6])
if parent.image_link:
return "/static/img/headers/hs/{}.jpg".format(self.id[:6])
if len(self.id) >= 4:
parent = self.__class__.query.get(self.id[:2])
if parent.image_link:
return "/static/img/headers/hs/{}.jpg".format(self.id[:2])
else:
return None
def get_display_id(self):
if len(self.id) == 2:
return self.id
else:
return getattr(self, self.classification)
def get_top(self, limit=10, year=None):
from oec import db_data
year = year or available_years[self.classification][-1]
Yo = getattr(db_data, "{}_models".format(self.classification)).Yo
return Yo.query.filter_by(year=year, top_export=self.id)\
.order_by(Yo.export_val.desc()).limit(limit)
def get_yp(self, year=None):
year = year or available_years[self.classification][-1]
yp = filter(lambda yp: yp.year == year, self.yp_product)
if len(yp): return yp[0]
return None
def get_abbrv(self, lang=None):
return getattr(self, self.classification) if getattr(
self, self.classification) else ""
def get_icon(self):
stem = "hs" if "hs" in self.classification else "sitc"
return "/static/img/icons/{0}/{0}_{1}.png".format(stem, self.id[:2])
def get_profile_url(self):
return "/{}/profile/{}/{}/".format(g.locale, self.classification,
getattr(self, self.classification))
def get_profile_link(self):
url = self.get_profile_url()
name = self.get_name()
return u'<a href="{}">{}</a>'.format(url, name)
def serialize(self, lang="en"):
auto_serialized = super(ProdAttr, self).serialize()
auto_serialized["icon"] = self.get_icon()
auto_serialized["image"] = self.get_image()
try:
auto_serialized["display_id"] = auto_serialized.pop(
self.classification)
except KeyError:
auto_serialized["display_id"] = None
return auto_serialized
def __repr__(self):
return '<Hs %r>' % (self.id)
def hs07_id(cls):
return db.Column(db.String(8),
db.ForeignKey(Hs07.id),
primary_key=True)
def top_export(cls):
return db.Column(db.String(6), db.ForeignKey(Sitc.id))