def get_chapter(self, screen_gid):
"""获取chapter4"""
# import datetime
# t1 = datetime.datetime.now()
# print(datetime.datetime.now())
chapter_list = []
q = (
db.session.query(Chapter4).filter(
Chapter4.screen_gid == screen_gid)
# .options(subqueryload('displays'), # 多的时候可以提高性能，少的时候没有效果
# subqueryload('conditions'),
# subqueryload('opetypes'))
)
q = q.order_by(Chapter4.hk_chapter,
Chapter4.hk_sub_chapter, Chapter4.hk_dev_name,
func.length(Chapter4.hk_no), Chapter4.hk_no,
func.length(Chapter4.hk_state_no), Chapter4.hk_state_no,
Chapter4.hk_condition_branch).all()
for chapter_info in q:
chapter = chapter_info.to_dict()
chapter["hk_condition_phrase"] = chapter_info.conditions.condition
chapter["hk_condition_code"] = chapter_info.conditions.view_model
chapter["hk_ope_type"] = chapter_info.opetypes.ope_type
chapter["hk_event"] = chapter_info.opetypes.ope_event
chapter["hk_action"] = chapter_info.displays.display
chapter["hk_action_model"] = chapter_info.displays.fun_of_model
chapter_list.append(chapter)
# t2 = datetime.datetime.now()
# print(t2)
# print(t2 - t1)
return chapter_list
def downgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.drop_column('crest_fleets', 'registration_time')
op.drop_table('fleet_time_last_tracked')
op.drop_table('fleet_time_by_hull')
op.drop_table('fleet_time')
connection = op.get_bind()
t_tokens = sa.Table(
'ssotoken',
sa.MetaData(),
sa.Column('access_token', sa.Text()),
sa.Column('access_token_expires', sa.DateTime(), default=datetime.utcnow),
sa.Column('refresh_token', sa.Text()),
)
connection.execute(
t_tokens.delete()\
.where(func.length(t_tokens.c.refresh_token) > 128)\
)
op.alter_column('ssotoken', 'refresh_token',
existing_type=sa.Text(),
type_=sa.VARCHAR(length=128),
existing_nullable=True)
connection.execute(
t_tokens.update()\
.where(func.length(t_tokens.c.access_token) > 128)\
.values(access_token=None, access_token_expires=datetime.utcnow())\
)
op.alter_column('ssotoken', 'access_token',
existing_type=sa.Text(),
type_=sa.VARCHAR(length=128),
existing_nullable=True)
def conv_cizu2zi():
words = {}
for w in Word.query.filter(func.length(Word.word) <= 3).all():
words.setdefault((w.user_id, w.word, w.book, w.chapter), w)
print(f"read {len(words)} cizu")
del_ids = []
for w in Word.query.filter(func.length(Word.word) > 3).all():
cizu = w.word
can_delete = True
for zi in cizu:
k = (w.user_id, zi, w.book, w.chapter)
if k in words:
print(f"skip {zi}")
else:
print("adding ", zi, w.book, w.chapter)
w2 = Word(user_id=w.user_id, word = zi, book = w.book,
chapter = w.chapter, study_date = w.study_date,
cur_xpoints = w.cur_xpoints,
tot_xpoints = w.tot_xpoints,
num_pass = w.num_pass,
num_fail = w.num_fail,
streak = w.streak,
istep = w.istep,
next_study = w.next_study)
db.session.add(w2)
can_delete = False
words[k] = w2
db.session.commit()
if can_delete:
print(f"delete {cizu}, {w.id}")
del_ids.append(w.id)
db.session.delete(w)
db.session.commit()
def analyzeShortPages(maxLength, sampleSize=None, rseed=6778):
'''
Print (a sample of) pages with tokenized length less than maxLength.
'''
from sqlalchemy.sql.expression import func, and_
from random import sample, seed
session = Session()
if isinstance(maxLength, tuple): mn, mx = maxLength
else:
mn = 0
mx = maxLength
res = [
o for o in session.query(PageExtTok).filter(
and_(
func.length(PageExtTok.text) >= mn,
func.length(PageExtTok.text) <= mx))
]
print len(res)
if sampleSize and sampleSize < len(res):
seed(rseed)
res = sample(res, sampleSize)
ids = set(pet.id for pet in res)
origTxt = [o for o in session.query(PageExt).filter(PageExt.id.in_(ids))]
oId2Txt = {o.id: o for o in origTxt}
for t in res:
orig = oId2Txt[t.id]
print t.id, orig.name, len(orig.plainText)
print u'text = [%s]' % orig.plainText.strip()
def classify(bnm, sentiment, length):
short = True if length < 3000 else False
get_length_q = {
True: func.length(Content.content) <= 3000,
False: func.length(Content.content) > 3000
}
bad_count = (session.query(Content).join(
Review, Review.reviewid == Content.reviewid).filter(
get_length_q[short], Review.best_new_music == bnm,
Content.sentiment == sentiment, Review.score <= 3).count())
mid_count = (session.query(Content).join(
Review, Review.reviewid == Content.reviewid).filter(
get_length_q[short],
Review.best_new_music == bnm,
Content.sentiment == sentiment,
Review.score > 3,
Review.score <= 7,
).count())
good_count = (session.query(Content).join(
Review, Review.reviewid == Content.reviewid).filter(
get_length_q[short], Review.best_new_music == bnm,
Content.sentiment == sentiment, Review.score > 7).count())
klass = max(bad_count, mid_count, good_count)
if klass == bad_count:
return "Bad (0-3)"
elif klass == mid_count:
return "Mid (4-7)"
else:
return "Good (8-10)"
def allUpdatesSource(series=None):
A = db.sequence_series2_updates.alias(name="A")
#B = db.sequence_series2_updates.alias(name="B")
maxIndex = None
if series is None:
maxIndex = db.connection.execute( sql.select((func.max(A.c.dummy_id),)) ).fetchone()[0]
else:
maxIndex = db.connection.execute( sql.select((func.max(A.c.dummy_id),)).where(A.c.source==series) ).fetchone()[0]
if maxIndex is None:
print("No updates found")
return
print("Total update records: %d" % maxIndex)
#maxIndex = 4949
groupSize = 5000
if args.stop_at_id > 0:
maxIndex = args.stop_at_id
total = 0
for fromId in range(args.start_from_id, maxIndex, groupSize):
q = None
if series is None:
q = sql.select(( A.c.dummy_id, A.c.sequence_id, func.length(A.c.content))).where(
sql.and_(
# A.c.dummy_id < B.c.dummy_id,
# A.c.sequence_id == B.c.sequence_id,
# A.c.source == B.c.source,
sql.between(A.c.dummy_id, fromId, min(fromId+groupSize-1, maxIndex))
)).order_by(A.c.dummy_id)
else:
q = sql.select(( A.c.dummy_id, A.c.sequence_id, func.length(A.c.content))).where(
sql.and_(
# A.c.dummy_id < B.c.dummy_id,
# A.c.sequence_id == B.c.sequence_id,
A.c.source == series,
sql.between(A.c.dummy_id, fromId, min(fromId+groupSize-1, maxIndex))
)).order_by(A.c.dummy_id)
#print("Executing query for range(%d, %d)..." % (fromId, fromId+groupSize))
result = db.connection.execute( q )
pairRecords = result.fetchall()
for r in pairRecords:
yield r
total += len(pairRecords)
print("Total: %d" % total)
def __init__(self, wld_id, bra_id):
TradePartner.__init__(self, wld_id, bra_id)
self.max_year_query = db.session.query(func.max(Ymbw.year)).filter_by(
wld_id=wld_id, month=12)
if bra_id is not None:
self.secex_query = Ymbw.query.join(Wld).join(Bra).filter(
Ymbw.wld_id == self.wld_id,
Ymbw.bra_id.like(self.bra_id + '%'), Ymbw.month == 0,
Ymbw.year == self.max_year_query,
func.length(Ymbw.bra_id) == 9)
else:
self.secex_query = Ymbw.query.join(Wld).join(Bra).filter(
Ymbw.wld_id == self.wld_id, Ymbw.month == 0,
Ymbw.year == self.max_year_query,
func.length(Ymbw.bra_id) == 9)
def upgrade():
"""
Sets all depot files for file typed revisions.
Until now, files are both in database and, for the newly created
ones, on disk. In order to simplify the migration, this procedure
will:
- delete the few files on disk,
- create all files on disk from database.
"""
# Creates files depot used in this migration
configure_depot()
connection = op.get_bind()
delete_files_on_disk(connection=connection)
select_query = revision_helper.select() \
.where(revision_helper.c.type == 'file') \
.where(revision_helper.c.depot_file.is_(None)) \
.where(func.length(revision_helper.c.file_content) > 0)
files = connection.execute(select_query).fetchall()
for file in files:
file_filename = '{0}{1}'.format(
file.label,
file.file_extension,
)
depot_file_intent = FileIntent(
file.file_content,
file_filename,
file.file_mimetype,
)
depot_file_field = UploadedFile(depot_file_intent, 'tracim')
update_query = revision_helper.update() \
.where(revision_helper.c.revision_id == file.revision_id) \
.values(depot_file=depot_file_field)
connection.execute(update_query)
def get_open_tasks():
try:
page = int(request.args.get("page",1))
except ValueError:
page = 1
with db.session_scope() as sess:
per_page = 10
task_count = sess.query(db.OpenTask)\
.filter(db.OpenTask.solved == False).count()
open_tasks = sess.query(db.OpenTask)\
.order_by(db.OpenTask.datetime.asc(), func.substr(db.OpenTask.id, 0, func.length(db.OpenTask.id) - 2))\
.filter(db.OpenTask.solved == False)\
.offset((page-1) * per_page).limit(per_page)\
.all()
pagination = Pagination(page=page,
total=task_count,
search=False,
per_page=per_page,
bs_version=3)
return render_template("task_list.html",
tasks = open_tasks,
pagination = pagination)
def __init__(self, university_id):
University.__init__(self, university_id)
self.max_year_query = db.session.query(func.max(Yuc.year))
self.hedu_query = Yuc.query.filter(
Yuc.university_id == self.university_id,
Yuc.year == self.max_year_query,
func.length(Yuc.course_hedu_id) == 6)
def get_chapter(self, screen_gid):
"""获取chapter1"""
chapter_list = []
q = db.session.query(Chapter1).filter(
Chapter1.screen_gid == screen_gid)
q = q.order_by(Chapter1.display_chapter, Chapter1.display_sub_chapter,
Chapter1.parts_number.ilike("S").desc(),
Chapter1.parts_number.ilike("P").desc(),
func.length(Chapter1.parts_number),
Chapter1.parts_number, Chapter1.parts_name,
Chapter1.display_condition_branch).all()
for chapter_info in q:
chapter = chapter_info.to_dict()
chapter[
"display_condition_phrase"] = chapter_info.conditions.condition
chapter[
"display_condition_code"] = chapter_info.conditions.view_model
chapter[
"display_property_type"] = chapter_info.properties.property_type
chapter["display_property"] = chapter_info.properties.property
chapter["display_action"] = chapter_info.displays.display
chapter[
"display_action_model"] = chapter_info.displays.fun_of_model
chapter_list.append(chapter)
return chapter_list
def get(self, kind, code):
""" Returns supports for kind and code prefix. """
return paginate(
page_args,
Support.query.filter(Support.kind == kind,
Support.code.like(code + '%'),
func.length(Support.code) == len(code) + 2))
def upgrade():
"""
Sets all depot files for file typed revisions.
Until now, files are both in database and, for the newly created
ones, on disk. In order to simplify the migration, this procedure
will:
- delete the few files on disk,
- create all files on disk from database.
"""
# Creates files depot used in this migration
configure_depot()
connection = op.get_bind()
delete_files_on_disk(connection=connection)
select_query = revision_helper.select() \
.where(revision_helper.c.type == 'file') \
.where(revision_helper.c.depot_file.is_(None)) \
.where(func.length(revision_helper.c.file_content) > 0)
files = connection.execute(select_query).fetchall()
for file in files:
file_filename = '{0}{1}'.format(
file.label,
file.file_extension,
)
depot_file_intent = FileIntent(
file.file_content,
file_filename,
file.file_mimetype,
)
depot_file_field = UploadedFile(depot_file_intent, 'tracim')
update_query = revision_helper.update() \
.where(revision_helper.c.revision_id == file.revision_id) \
.values(depot_file=depot_file_field)
connection.execute(update_query)
def sessionmaker(dbconfig):
dbconfig = dbconfig.copy()
conn_str = dbconfig.pop('url')
if 'schema' in dbconfig:
schema = dbconfig.pop('schema')
else:
schema = None
engine = create_engine(conn_str, **dbconfig)
mappers, tables, Session = reflect(engine, models, schema)
# add mapper relationships
mappers['Data'].add_properties({
'versions': relationship(models.Version,
lazy='dynamic',
backref=backref('ref',
lazy='joined'))
})
mappers['Version'].add_properties({
'data': deferred(tables['version'].c['data']),
'size': column_property(func.length(tables['version'].c['data']))
})
Session.class_.mappers = mappers
return Session
def __init__(self, university_id):
University.__init__(self, university_id)
self.max_year_query = db.session.query(func.max(Yuc.year))
self.hedu_query = Yuc.query.filter(
Yuc.university_id == self.university_id,
Yuc.year == self.max_year_query,
func.length(Yuc.course_hedu_id) == 6)
def visualize_redirect(app_name='tree_map'):
'''fetch random country'''
c = Country.query.get(choice(random_countries))
latest_hs_year = [available_years['hs92'][-1]]
if app_name in ["tree_map", "stacked", "network"]:
year = [available_years['hs92'][0], available_years['hs92'][-1]
] if app_name == "stacked" else latest_hs_year
redirect_url = url_for('.visualize', lang=g.locale, app_name=app_name, \
classification="hs92", trade_flow="export", \
origin_id=c.id_3char, dest_id="all", prod_id="show", year=year)
elif app_name in ["geo_map", "rings"]:
'''fetch random product'''
p = Hs92.query.filter(Hs92.hs92 != None).filter(func.length(Hs92.hs92) == 4) \
.order_by(func.random()).limit(1).first()
origin = "show"
if app_name == "rings":
origin = c.id_3char
redirect_url = url_for('.visualize', lang=g.locale, app_name=app_name, \
classification="hs92", trade_flow="export", \
origin_id=origin, dest_id="all", prod_id=p.hs92, year=latest_hs_year)
elif app_name in ["scatter"]:
redirect_url = url_for('.visualize', lang=g.locale, app_name=app_name, \
classification="hs92", trade_flow="gdp", \
origin_id="show", dest_id="all", prod_id="all", year=latest_hs_year)
else:
abort(404)
return redirect(redirect_url)
def _load_preview(self, version):
if version.metadata['file_type'] not in ('csv', 'text'):
return None, None
preview_data = g.dbsession.query(func.substring(Version.data, 1, 18000),
func.length(Version.data))\
.filter(Version.id==version.id).one()
preview_data, version_length = preview_data
preview_data = str(preview_data)
is_preview_full = ( len(preview_data) == version_length )
if not is_preview_full:
try:
last_return = preview_data.rindex('\n')
except ValueError:
# too long preview
return None, None
preview_data = preview_data[:last_return]
encoding = chardet.detect(preview_data)['encoding']
if encoding is None:
return None, None
try:
preview_data = preview_data.decode(encoding)
except UnicodeDecodeError:
return None, None
preview_data = preview_data.replace(',', ', ').replace(';', '; ')
preview_data = preview_data.strip().split('\n')
return preview_data, is_preview_full
def visualize_redirect(app_name='tree_map'):
'''fetch random country'''
c = Country.query.get(choice(random_countries))
latest_hs_year = [available_years['hs92'][-1]]
if app_name in ["tree_map", "stacked", "network"]:
year = [available_years['hs92'][0], available_years['hs92'][-1]] if app_name == "stacked" else latest_hs_year
redirect_url = url_for('.visualize', lang=g.locale, app_name=app_name, \
classification="hs92", trade_flow="export", \
origin_id=c.id_3char, dest_id="all", prod_id="show", year=year)
elif app_name in ["geo_map", "rings"]:
'''fetch random product'''
p = Hs92.query.filter(Hs92.hs92 != None).filter(func.length(Hs92.hs92) == 4) \
.order_by(func.random()).limit(1).first()
origin = "show"
if app_name == "rings":
origin = c.id_3char
redirect_url = url_for('.visualize', lang=g.locale, app_name=app_name, \
classification="hs92", trade_flow="export", \
origin_id=origin, dest_id="all", prod_id=p.hs92, year=latest_hs_year)
elif app_name in ["scatter"]:
redirect_url = url_for('.visualize', lang=g.locale, app_name=app_name, \
classification="hs92", trade_flow="gdp", \
origin_id="show", dest_id="all", prod_id="all", year=latest_hs_year)
else:
abort(404)
return redirect(redirect_url)
def stocktype_completemanufacturer(m):
result = s.execute(
select([StockType.manufacturer]).\
where(StockType.manufacturer.ilike(m + '%')).\
group_by(StockType.manufacturer).\
order_by(func.length(StockType.manufacturer), StockType.manufacturer)
)
return [x[0] for x in result]
def db_get_all_translations():
"""
function that queries the database for all the translations, and order them by descending order of length(translated_string).
:return translations: Array with the ordered translations
"""
translations = Translation.query.order_by(
desc(func.length(Translation.translated_string))).all()
return translations
def get_by_length(self, type, column, value, limit):
# Open the connection
self.connect()
result = self.session.query(type).filter(
or_(and_(func.length(KataDasar.kata) > value))).limit(limit).all()
# Close the connection
self.disconnect()
return result
async def fix_address(startBlockNumber: int, endBlockNumber: int,
batchSize: int):
databaseConnectionString = Database.create_psql_connection_string(
username=os.environ["DB_USERNAME"],
password=os.environ["DB_PASSWORD"],
host=os.environ["DB_HOST"],
port=os.environ["DB_PORT"],
name=os.environ["DB_NAME"])
database = Database(connectionString=databaseConnectionString)
await database.connect()
currentBlockNumber = startBlockNumber
while currentBlockNumber < endBlockNumber:
start = currentBlockNumber
end = min(currentBlockNumber + batchSize, endBlockNumber)
logging.info(f'Working on {start} to {end}...')
async with database.transaction():
query = TokenTransfersTable.select()
query = query.where(TokenTransfersTable.c.blockNumber >= start)
query = query.where(TokenTransfersTable.c.blockNumber < end)
query = query.where(
or_(
sqlalchemyfunc.length(
TokenTransfersTable.c.toAddress) != 42,
sqlalchemyfunc.length(TokenTransfersTable.c.toAddress) !=
42,
))
tokenTransfersToChange = [
token_transfer_from_row(row)
async for row in database.iterate(query=query)
]
logging.info(
f'Updating {len(tokenTransfersToChange)} transfers...')
for tokenTransfer in tokenTransfersToChange:
query = TokenTransfersTable.update(
TokenTransfersTable.c.tokenTransferId ==
tokenTransfer.tokenTransferId)
values = {
TokenTransfersTable.c.toAddress.key:
normalize_address(tokenTransfer.toAddress),
TokenTransfersTable.c.fromAddress.key:
normalize_address(tokenTransfer.fromAddress),
}
await database.execute(query=query, values=values)
currentBlockNumber = end
await database.disconnect()
def getCommunesSearch(session, search, limit=50):
req = session.query(distinct(VmCommunes.commune_maj), VmCommunes.insee,
func.length(VmCommunes.commune_maj)).filter(
VmCommunes.commune_maj.ilike('%' + search + '%'))
if (current_app.config['ORDER_COMMUNES_BYLENGTH']):
req = req.order_by(func.length(VmCommunes.commune_maj))
else:
req = req.order_by(VmCommunes.commune_maj)
req = req.limit(limit).all()
communeList = list()
for r in req:
temp = {'label': r[0], 'value': r[1]}
communeList.append(temp)
return communeList
def stocktype_completemanufacturer(m):
result = s.execute(
select([StockType.manufacturer]).\
where(StockType.manufacturer.ilike(m + '%')).\
group_by(StockType.manufacturer).\
order_by(func.length(StockType.manufacturer), StockType.manufacturer)
)
return [x[0] for x in result]
def __init__(self, wld_id, bra_id):
TradePartner.__init__(self, wld_id, bra_id)
self.max_year_query = db.session.query(
func.max(Ymbw.year)).filter_by(wld_id=wld_id, month=12)
if bra_id is not None:
self.secex_query = Ymbw.query.join(Wld).join(Bra).filter(
Ymbw.wld_id == self.wld_id,
Ymbw.bra_id.like(self.bra_id + '%'),
Ymbw.month == 0,
Ymbw.year == self.max_year_query,
func.length(Ymbw.bra_id) == 9)
else:
self.secex_query = Ymbw.query.join(Wld).join(Bra).filter(
Ymbw.wld_id == self.wld_id,
Ymbw.month == 0,
Ymbw.year == self.max_year_query,
func.length(Ymbw.bra_id) == 9)
def __init__(self, wld_id):
TradePartner.__init__(self, wld_id)
self.max_year_query = db.session.query(func.max(Ymbw.year)).filter_by(wld_id=wld_id)
self.secex_query = Ymbw.query.join(Wld).join(Bra).filter(
Ymbw.wld_id == self.wld_id,
Ymbw.month == 0,
Ymbw.year == self.max_year_query,
func.length(Ymbw.bra_id) == 9)
def admin(self, id=None):
if not h.auth.is_logged_in():
abort(401)
if not h.auth.is_admin():
abort(403)
c.sepdirnew = False
c.alreadysepdir = False
redirect = request.params.get('redirect', False)
add = request.params.get('add', False)
limit = request.params.get('limit', None)
entity_q = Session.query(Entity)
c.found = False
c.custom = False
c.new = False
if request.params.get('q'):
q = request.params['q']
o = Entity.label.like(q)
entity_q = entity_q.filter(o).order_by(func.length(Entity.label))
# if only 1 result, go ahead and view that thinker
if redirect and entity_q.count() == 1:
print "have a q, entityq count = 1"
c.thinker = h.fetch_obj(Thinker, entity_q.first().ID)
c.found = True
id = c.thinker.ID
c.message = 'Entity edit page for thinker ' + c.thinker.name
if request.params.get('entry_sep_dir'):
entry_sep_dir = request.params['entry_sep_dir']
if not (c.thinker.sep_dir):
c.thinker.sep_dir = request.params['entry_sep_dir']
c.sepdirnew = True
else:
c.alreadysepdir = True
c.entry_sep_dir = request.params['entry_sep_dir']
return render('admin/thinker-edit.html')
else:
print "That didn't work."
if id is None:
print "I am here"
c.message = "Please input an entity label using the search bar to the left."
return render ('admin/thinker-edit.html')
else:
c.thinker = h.fetch_obj(Thinker, id)
c.found = True
c.message = 'Entity edit page for thinker ' + c.thinker.name
if request.params.get('entry_sep_dir'):
entry_sep_dir = request.params['entry_sep_dir']
if not (c.thinker.sep_dir):
c.thinker.sep_dir = request.params['entry_sep_dir']
c.sepdirnew = True
else:
c.alreadysepdir = True
c.entry_sep_dir = request.params['entry_sep_dir']
return render ('admin/thinker-edit.html')
def admin(self, id=None):
if not h.auth.is_logged_in():
abort(401)
if not h.auth.is_admin():
abort(403)
c.sepdirnew = False
c.alreadysepdir = False
redirect = request.params.get('redirect', False)
add = request.params.get('add', False)
limit = request.params.get('limit', None)
entity_q = Session.query(Entity)
c.found = False
c.custom = False
c.new = False
if request.params.get('q'):
q = request.params['q']
o = Entity.label.like(q)
entity_q = entity_q.filter(o).order_by(func.length(Entity.label))
# if only 1 result, go ahead and view that thinker
if redirect and entity_q.count() == 1:
print "have a q, entityq count = 1"
c.thinker = h.fetch_obj(Thinker, entity_q.first().ID)
c.found = True
id = c.thinker.ID
c.message = 'Entity edit page for thinker ' + c.thinker.name
if request.params.get('entry_sep_dir'):
entry_sep_dir = request.params['entry_sep_dir']
if not (c.thinker.sep_dir):
c.thinker.sep_dir = request.params['entry_sep_dir']
c.sepdirnew = True
else:
c.alreadysepdir = True
c.entry_sep_dir = request.params['entry_sep_dir']
return render('admin/thinker-edit.html')
else:
print "That didn't work."
if id is None:
print "I am here"
c.message = "Please input an entity label using the search bar to the left."
return render('admin/thinker-edit.html')
else:
c.thinker = h.fetch_obj(Thinker, id)
c.found = True
c.message = 'Entity edit page for thinker ' + c.thinker.name
if request.params.get('entry_sep_dir'):
entry_sep_dir = request.params['entry_sep_dir']
if not (c.thinker.sep_dir):
c.thinker.sep_dir = request.params['entry_sep_dir']
c.sepdirnew = True
else:
c.alreadysepdir = True
c.entry_sep_dir = request.params['entry_sep_dir']
return render('admin/thinker-edit.html')
def list(self, filetype='html'):
entity_q = Session.query(self._type)
#TODO: Remove the following line when Nodes are eliminated
entity_q = entity_q.filter(Entity.typeID != 2)
c.missing_entity = 0
# get the list of entities
#c.entities = entity_q.all()
c.nodes = Session.query(Node).filter(Node.parent_id == None)
c.nodes = c.nodes.order_by("name").all()
c.query = request.params.get('q', '')
c.query = c.query.strip()
c.sep = request.params.get('sep', '')
c.wiki = request.params.get('wiki', '')
if request.params.get('sep_filter', False):
entity_q = entity_q.filter(Entity.sep_dir != '')
if c.sep:
entity_q = entity_q.filter(Entity.sep_dir == c.sep)
if c.wiki:
entity_q = entity_q.filter(Entity.wiki == c.wiki)
if c.query:
o = or_(Entity.label.like(c.query + '%'),
Entity.label.like('% ' + c.query + '%'),
Entity.label.like('%-' + c.query + '%'))
entity_q = entity_q.filter(o).order_by(func.length(Entity.label))
c.total = entity_q.count()
# limit must be the last thing applied to the query
entity_q = entity_q.limit(request.params.get('limit', None))
c.entities = entity_q.all()
if filetype == 'json':
response.content_type = 'application/json'
if request.params.get('redirect', False) and len(c.entities) == 1:
h.redirect(h.url(controller=self._controller,
action='view',
filetype=filetype,
id=c.entities[0].ID),
code=302)
else:
#if there are no results, show the related SEP results
if not c.entities:
c.entities = self.missing_entity_search(c.query)
if c.entities:
c.missing_entity = 1
#raise Exception
#render the page
return render('{type}/{type}-list.'.format(type=self._controller) +
filetype)
def get_chapter(self, screen_gid):
"""获取chapter5"""
chapter_list = []
q = db.session.query(Chapter5).filter(
Chapter5.screen_gid == screen_gid)
q = q.order_by(Chapter5.init_chapter, func.length(Chapter5.init_no),
Chapter5.init_no, func.length(Chapter5.init_state_no),
Chapter5.init_state_no,
Chapter5.init_condition_branch).all()
for chapter_info in q:
chapter = chapter_info.to_dict()
chapter[
"init_condition_phrase"] = chapter_info.conditions.condition
chapter["init_condition_code"] = chapter_info.conditions.view_model
chapter["init_action"] = chapter_info.displays.display
chapter["init_action_model"] = chapter_info.displays.fun_of_model
chapter_list.append(chapter)
return chapter_list
def list(self, filetype="html"):
entity_q = Session.query(self._type)
# TODO: Remove the following line when Nodes are eliminated
entity_q = entity_q.filter(Entity.typeID != 2)
c.missing_entity = 0
# get the list of entities
# c.entities = entity_q.all()
c.nodes = Session.query(Node).filter(Node.parent_id == None)
c.nodes = c.nodes.order_by("name").all()
c.query = request.params.get("q", "")
c.query = c.query.strip()
c.sep = request.params.get("sep", "")
c.wiki = request.params.get("wiki", "")
if request.params.get("sep_filter", False):
entity_q = entity_q.filter(Entity.sep_dir != "")
if c.sep:
entity_q = entity_q.filter(Entity.sep_dir == c.sep)
if c.wiki:
entity_q = entity_q.filter(Entity.wiki == c.wiki)
if c.query:
o = or_(
Entity.label.like(c.query + "%"),
Entity.label.like("% " + c.query + "%"),
Entity.label.like("%-" + c.query + "%"),
)
entity_q = entity_q.filter(o).order_by(func.length(Entity.label))
c.total = entity_q.count()
# limit must be the last thing applied to the query
entity_q = entity_q.limit(request.params.get("limit", None))
c.entities = entity_q.all()
if filetype == "json":
response.content_type = "application/json"
if request.params.get("redirect", False) and len(c.entities) == 1:
h.redirect(
h.url(controller=self._controller, action="view", filetype=filetype, id=c.entities[0].ID), code=302
)
else:
# if there are no results, show the related SEP results
if not c.entities:
c.entities = self.missing_entity_search(c.query)
if c.entities:
c.missing_entity = 1
# raise Exception
# render the page
return render("{type}/{type}-list.".format(type=self._controller) + filetype)
def test_update_task(c):
task = Task.query.filter(func.length(Task.offices) == 1).first()
old_name = task.name
new_name = f'{uuid4()}'.replace('-', '')
c.post(f'/task/{task.id}', data={'name': new_name}, follow_redirects=True)
assert Task.query.filter_by(name=old_name).first() is None
assert Task.query.filter_by(name=new_name).first() is not None
def stocktype_completename(m, n):
result = s.execute(
select([StockType.name]).\
where(StockType.manufacturer == m).\
where(StockType.name.ilike(n + '%')).\
group_by(StockType.name).\
order_by(func.length(StockType.name), StockType.name)
)
return [x[0] for x in result]
def __init__(self, bra_id):
self._secex = None
self._secex_sorted_by_eci = None
self.bra_id = bra_id
self.max_year_query = db.session.query(func.max(Ymb.year)).filter_by(
bra_id=self.bra_id, month=12)
self.secex_query = Ymb.query.filter(Ymb.year == self.max_year_query,
Ymb.month == 0,
func.length(Ymb.bra_id) == 5)
def stocktype_completename(m, n):
result = s.execute(
select([StockType.name]).\
where(StockType.manufacturer == m).\
where(StockType.name.ilike(n + '%')).\
group_by(StockType.name).\
order_by(func.length(StockType.name), StockType.name)
)
return [x[0] for x in result]
def create_node_definitions() -> dict:
regexes_and_corresponding_ids = dict()
# Apply longest term first: Prefer "world spirit" over "spirit".
for node in get_all_nodes().order_by(desc(func.length(BlogPost.name))):
regex_source = rf"\[.*?\]|({re.escape(node.name)})"
regex = re.compile(regex_source, flags=re.IGNORECASE)
regexes_and_corresponding_ids[node.name] = (regex, node.id)
return regexes_and_corresponding_ids
def __init__(self, course_hedu_id):
Major.__init__(self, course_hedu_id)
self.course_hedu_id = course_hedu_id
self.max_year_query = db.session.query(func.max(Ybc_hedu.year)).filter_by(course_hedu_id=course_hedu_id)
self.hedu_query = Ybc_hedu.query.filter(
Ybc_hedu.course_hedu_id == self.course_hedu_id,
Ybc_hedu.year == self.max_year_query,
func.length(Ybc_hedu.bra_id) == 9)
def __init__(self, bra_id):
self._secex = None
self._secex_sorted_by_eci = None
self.bra_id = bra_id
self.max_year_query = db.session.query(
func.max(Ymb.year)).filter_by(bra_id=self.bra_id, month=12)
self.secex_query = Ymb.query.filter(
Ymb.year == self.max_year_query,
Ymb.month == 0,
func.length(Ymb.bra_id) == 5)
def obter_documentos_externos():
query = session().query(
TB_DOCUMENTO.c.docu_nr_externo, TB_DOCUMENTO.c.docu_dk,
TB_PROCESSO.c.prtj_dt_ultima_vista).outerjoin(
TB_PROCESSO, TB_DOCUMENTO.c.docu_nr_externo ==
TB_PROCESSO.c.prtj_cd_numero_processo
).filter(TB_DOCUMENTO.c.docu_mate_dk == 4).filter(
func.length(TB_DOCUMENTO.c.docu_nr_externo) == 20).order_by(
nullsfirst(TB_PROCESSO.c.prtj_dt_ultima_vista), )
return [(doc[0], doc[1]) for doc in query]
def __init__(self, course_hedu_id, bra_id):
Major.__init__(self, course_hedu_id, bra_id)
self.course_hedu_id = course_hedu_id
self.max_year_query = db.session.query(
func.max(Ybc_hedu.year)).filter_by(course_hedu_id=course_hedu_id)
if bra_id == '':
self.hedu_query = Ybc_hedu.query.filter(
Ybc_hedu.course_hedu_id == self.course_hedu_id,
Ybc_hedu.year == self.max_year_query,
not_(Ybc_hedu.bra_id.like('0xx%')),
func.length(Ybc_hedu.bra_id) == 9)
else:
self.hedu_query = Ybc_hedu.query.filter(
Ybc_hedu.course_hedu_id == self.course_hedu_id,
Ybc_hedu.year == self.max_year_query,
Ybc_hedu.bra_id.like(self.bra_id+'%'),
not_(Ybc_hedu.bra_id.like('0xx%')),
func.length(Ybc_hedu.bra_id) == 9)
def __init__(self, course_hedu_id, bra_id):
Major.__init__(self, course_hedu_id, bra_id)
self.course_hedu_id = course_hedu_id
self.max_year_query = db.session.query(func.max(
Ybc_hedu.year)).filter_by(course_hedu_id=course_hedu_id)
if bra_id == '':
self.hedu_query = Ybc_hedu.query.filter(
Ybc_hedu.course_hedu_id == self.course_hedu_id,
Ybc_hedu.year == self.max_year_query,
not_(Ybc_hedu.bra_id.like('0xx%')),
func.length(Ybc_hedu.bra_id) == 9)
else:
self.hedu_query = Ybc_hedu.query.filter(
Ybc_hedu.course_hedu_id == self.course_hedu_id,
Ybc_hedu.year == self.max_year_query,
Ybc_hedu.bra_id.like(self.bra_id + '%'),
not_(Ybc_hedu.bra_id.like('0xx%')),
func.length(Ybc_hedu.bra_id) == 9)
def compute_statistics():
n_all = Question.query.count()
# Partial questions
n_partial_query = Question.query.filter(
db.and_(Question.n_answers > 0, Question.n_answers < MAX_ANSWERS))
n_partial = n_partial_query.count()
partial_questions_array = [0, 0, 0]
all_partial = n_partial_query.all()
for question in all_partial:
partial_questions_array[question.n_answers - 1] += 1
n_full_questions = Question.query.filter(
Question.n_answers == MAX_ANSWERS).count()
# Partial examples
n_full_examples, partial_examples = count_full_examples()
partial_examples_array = [0, 0, 0, 0, 0]
for example in partial_examples:
partial_examples_array[5 - example[1]] += 1
# Non-empty and non-full examples:
questions_not_empty = Question.query.filter(Question.n_answers > 0).all()
examples_not_empty = set()
for q in questions_not_empty:
examples_not_empty.add(q.example)
examples_not_empty = list(examples_not_empty)
examples_n_answers = []
for example in examples_not_empty:
sum_answers = Question.query.filter(
Question.example == example).with_entities(
func.sum(Question.n_answers)).scalar()
# Do not add examples that have been fully answered
if not sum_answers == MAX_ANSWERS * (N_MODELS * (N_MODELS - 1) / 2):
examples_n_answers += [[example, sum_answers]]
n_answers = Answer.query.count()
n_participants = User.query.count()
non_zero_participants = User.query.join(Answer).filter(
func.length(User.answers) > 0).all()
print "Total", n_all
print "Full examples:", n_full_examples
print "Partial examples:", len(partial_examples), ', distribution:', zip([
'1 question:', '2 questions:', '3 questions:', '4 questions:',
'5 questions:'
], partial_examples_array)
print "Non-empty examples:", len(examples_n_answers)
print "Full questions: ", n_full_questions
print "Partial questions:", n_partial, ', distribution:', zip(
['1 answer:', '2 answers:', '3 answers:'], partial_questions_array)
print 'Total answers:', n_answers
print 'Total participants:', n_participants, "(non-zero:", len(
non_zero_participants), ")"
def upgrade():
session = Session(bind=op.get_bind())
session.query(RequestsLog).filter(
func.length(RequestsLog.message) > MAX_LEN_MSG_REQUESTS_LOG).delete(
synchronize_session=False)
session.query(RequestsLog).filter_by(tag=None).update({"tag": ""})
# ### commands auto generated by Alembic - please adjust! ###
op.alter_column("requests_log",
"tag",
existing_type=sa.TEXT(),
nullable=False)
def test_incident_detail_display_read_more_button_for_descriptions_over_300_chars(
mockdata, browser):
# Navigate to profile page for officer with short and long incident descriptions
browser.get("http://localhost:5000/officer/1")
incident_long_descrip = Incident.query.filter(
func.length(Incident.description) > 300).one_or_none()
incident_id = str(incident_long_descrip.id)
result = browser.find_element_by_id("description-overflow-row_" +
incident_id)
assert result.is_displayed()
def get_chapter(self, screen_gid):
"""获取chapter8"""
chapter_list = []
q = db.session.query(Chapter8).filter(
Chapter8.screen_gid == screen_gid)
q = q.order_by(Chapter8.trig_chapter, Chapter8.trig_sub_chapter,
func.length(Chapter8.trig_no), Chapter8.trig_no,
func.length(Chapter8.trig_state_no),
Chapter8.trig_state_no,
Chapter8.trig_condition_branch).all()
for chapter_info in q:
chapter = chapter_info.to_dict()
chapter[
"trig_condition_phrase"] = chapter_info.conditions.condition
chapter["trig_condition_code"] = chapter_info.conditions.view_model
chapter["trig_name"] = chapter_info.events.event
chapter["trig_trig"] = chapter_info.events.trigger
chapter["trig_action"] = chapter_info.displays.display
chapter["trig_action_model"] = chapter_info.displays.fun_of_model
chapter_list.append(chapter)
return chapter_list
def index():
course_hedu_id = "523E04"
major_service = Major(course_hedu_id=course_hedu_id)
universities_service = MajorUniversities(course_hedu_id=course_hedu_id)
municipalities_service = MajorMunicipalities(course_hedu_id=course_hedu_id)
max_year_query = db.session.query(func.max(Yc_hedu.year)).filter_by(course_hedu_id=course_hedu_id)
rank_query = Yc_hedu.query.filter(
Yc_hedu.year == max_year_query, func.length(Yc_hedu.course_hedu_id) == 6
).order_by(Yc_hedu.course_hedu_id.desc())
rank = rank_query.all()
header = {
"name": major_service.name(),
"enrolled": major_service.enrolled(),
"entrants": major_service.entrants(),
"graduates": major_service.graduates(),
"profile": major_service.profile(),
"year": major_service.year(),
"portrait_id": course_hedu_id[:2],
}
content = {
"university_with_more_enrolled": universities_service.university_with_more_enrolled(),
"highest_enrolled_number_by_university": universities_service.highest_enrolled_number(),
"municipality_with_more_enrolled": municipalities_service.municipality_with_more_enrolled(),
"highest_enrolled_number_by_municipality": municipalities_service.highest_enrolled_number(),
"university_with_more_entrants": universities_service.university_with_more_entrants(),
"highest_entrant_number_by_university": universities_service.highest_entrants_number(),
"municipality_with_more_entrants": municipalities_service.municipality_with_more_entrants(),
"highest_entrant_number_by_municipality": municipalities_service.highest_entrants_number(),
"university_with_more_graduates": universities_service.university_with_more_graduates(),
"highest_graduate_number_by_university": universities_service.highest_graduates_number(),
"municipality_with_more_graduates": municipalities_service.municipality_with_more_graduates(),
"highest_graduate_number_by_municipality": municipalities_service.highest_graduates_number(),
}
for index, maj in enumerate(rank):
if rank[index].course_hedu_id == course_hedu_id:
header["rank"] = index
break
return render_template(
"major/index.html", header=header, content=content, body_class="perfil-estado", static_folder=static_folder
)
def query_by_attrs(model, attrs):
filter_args = []
records = model.query
for attr in attrs:
str_len = False
op = json.loads(attrs[attr])
if attr.endswith('_len'):
str_len = True
attr = attr[:-4]
#try:
if 'gte' in op.keys():
if str_len:
records = records.filter(func.length(getattr(model, attr)) >=op['gte'])
else:
records = records.filter(getattr(model, attr) >= op['gte'])
if 'lte' in op.keys():
if str_len:
records = records.filter(func.length(getattr(model, attr)) <= op['lte'])
else:
records = records.filter(getattr(model, attr) <= op['lte'])
if 'gt' in op.keys():
if str_len:
records = records.filter(func.length(getattr(model, attr)) > op['gt'])
else:
records = records.filter(getattr(model, attr) > op['gt'])
if 'lt' in op.keys():
if str_len:
records = records.filter(func.length(getattr(model, attr)) < op['lt'])
else:
records = records.filter(getattr(model, attr) < op['lt'])
if 'eq' in op.keys():
if str_len:
records = records.filter(func.length(getattr(model, attr)) == op['eq'])
else:
records = records.filter(getattr(model, attr) == op['eq'])
if 'ne' in op.keys():
if str_len:
records = records.filter(func.length(getattr(model, attr)) <> op['ne'])
else:
records = records.filter(getattr(model, attr) <> op['ne'])
#except:
# raise ValueError("gt, lt, gte, lte values must be in a json format e.g., {'lt':'5', 'gt':'3'}")
return records
def list(self, filetype='html'):
redirect = request.params.get('redirect', False)
limit = request.params.get('limit', None)
entity_q = model.meta.Session.query(model.Entity)
entity_q = entity_q.filter(model.Entity.typeID != 2)
c.nodes = model.meta.Session.query(model.Node).filter(model.Node.parent_id == None).order_by("name").all()
c.query = ''
c.sep = ''
if filetype=='json':
response.content_type = 'application/json'
if request.params.get('sep_filter'):
entity_q = entity_q.filter(model.Entity.sep_dir != '')
if request.params.get('sep'):
entity_q = entity_q.filter(model.Entity.sep_dir == request.params['sep'])
c.sep = request.params['sep']
# if only 1 result, go ahead and view that entity
if redirect and entity_q.count() == 1:
h.redirect(h.url(controller='entity', action='view',
filetype=filetype, id=entity_q.first().ID), code=302)
# Check for query
if request.params.get('q'):
q = request.params['q']
c.query = q
o = or_(model.Entity.label.like(q+'%'), model.Entity.label.like('% '+q+'%'))
entity_q = entity_q.filter(o).order_by(func.length(model.Entity.label))
# if only 1 result, go ahead and view that idea
if redirect and entity_q.count() == 1:
return self.view(entity_q.first().ID, filetype)
else:
c.entities = entity_q.limit(limit)
return render('entity/entity-list.' + filetype)
c.entities = entity_q.limit(limit)
return render('entity/entity-list.' + filetype)
def index(course_hedu_id, tab):
bra_id = request.args.get('bra_id')
menu = request.args.get('menu')
url = request.args.get('url')
graph = {}
if menu:
graph['menu'] = menu
if url:
graph['url'] = url
max_year_query = db.session.query(
func.max(Yc_hedu.year)).filter_by(course_hedu_id=course_hedu_id)
if bra_id:
major_service = Major(course_hedu_id, bra_id)
universities_service = MajorUniversities(course_hedu_id, bra_id)
municipalities_service = MajorMunicipalities(course_hedu_id, bra_id)
rank_query = Ybc_hedu.query.filter(
Ybc_hedu.year == max_year_query,
Ybc_hedu.bra_id == bra_id,
func.length(Ybc_hedu.course_hedu_id) == len(course_hedu_id))\
.order_by(Ybc_hedu.enrolled.desc())
else:
major_service = Major(course_hedu_id, '')
universities_service = MajorUniversities(course_hedu_id, '')
municipalities_service = MajorMunicipalities(course_hedu_id, '')
rank_query = Yc_hedu.query.filter(
Yc_hedu.year == max_year_query,
func.length(Yc_hedu.course_hedu_id) == len(course_hedu_id))\
.order_by(Yc_hedu.enrolled.desc())
rank = rank_query.all()
hedu_max_year = db.session.query(func.max(Yc_hedu.year)).first()[0]
if not bra_id:
header = {
'name': major_service.name(),
'enrolled': major_service.enrolled(),
'entrants': major_service.entrants(),
'graduates': major_service.graduates(),
'profile': major_service.profile(),
'year': major_service.year(),
'field_id': course_hedu_id[:2]
}
else:
header = {
'name': major_service.name(),
'enrolled': major_service.enrolled(),
'entrants': major_service.entrants(),
'graduates': major_service.graduates(),
'profile': major_service.profile(),
'year': major_service.year(),
'field_id': course_hedu_id[:2],
'id': course_hedu_id,
'bra_id': bra_id,
'location_name': major_service.location_name()
}
body = {
'university_with_more_enrolled': universities_service.university_with_more_enrolled(),
'highest_enrolled_number_by_university': universities_service.highest_enrolled_number(),
'municipality_with_more_enrolled': municipalities_service.municipality_with_more_enrolled(),
'municipality_with_more_enrolled_state': municipalities_service.municipality_with_more_enrolled_state(),
'highest_enrolled_number_by_municipality': municipalities_service.highest_enrolled_number(),
'university_with_more_entrants': universities_service.university_with_more_entrants(),
'highest_entrant_number_by_university': universities_service.highest_entrants_number(),
'municipality_with_more_entrants': municipalities_service.municipality_with_more_entrants(),
'municipality_with_more_entrants_state': municipalities_service.municipality_with_more_entrants_state(),
'highest_entrant_number_by_municipality': municipalities_service.highest_entrants_number(),
'university_with_more_graduates': universities_service.university_with_more_graduates(),
'highest_graduate_number_by_university': universities_service.highest_graduates_number(),
'municipality_with_more_graduates': municipalities_service.municipality_with_more_graduates(),
'municipality_with_more_graduates_state': municipalities_service.municipality_with_more_graduates_state(),
'highest_graduate_number_by_municipality': municipalities_service.highest_graduates_number()
}
tabs = {
'general': [],
'enrollments': [
'enrollments-university-tree_map',
'enrollments-municipality-geo_map',
'enrollments-municipality-stacked',
'enrollments-municipality-tree_map',
'enrollments-status-line',
'enrollments-shift-stacked',
],
}
for index, maj in enumerate(rank):
if rank[index].course_hedu_id == course_hedu_id:
header['rank'] = index + 1
break
location = Bra.query.filter(Bra.id == bra_id).first()
major = Course_hedu.query.filter(Course_hedu.id == course_hedu_id).first()
if tab not in tabs:
abort(404)
if menu and menu not in tabs[tab]:
abort(404)
if header['enrolled'] is None or hedu_max_year != header['year']:
abort(404)
else:
return render_template('major/index.html', header=header, body=body, location=location, major=major, tab=tab, graph=graph)
def db_fix_users(session):
items = session.query(User).filter(func.length(User.password) == 47)
for item in items:
if(item.password[0] == 'b' and item.password[1] == '\'' and item.password[len(item.password) - 1] == '\''):
item.password = item.password[2: -1]
def index(course_hedu_id, tab):
bra_id = request.args.get('bra_id')
bra_id = bra_id if bra_id != 'all' else None
menu = request.args.get('menu')
url = request.args.get('url')
graph = {}
if menu:
graph['menu'] = menu
if url:
url_prefix = menu.split('-')[-1] + '/' if menu and menu.startswith('new-api-') else 'embed/'
graph['url'] = url_prefix + url
max_year_query = db.session.query(
func.max(Yc_hedu.year)).filter_by(course_hedu_id=course_hedu_id)
if bra_id:
major_service = Major(course_hedu_id, bra_id)
universities_service = MajorUniversities(course_hedu_id, bra_id)
municipalities_service = MajorMunicipalities(course_hedu_id, bra_id)
rank_query = Ybc_hedu.query.filter(
Ybc_hedu.year == max_year_query,
Ybc_hedu.bra_id == bra_id,
func.length(Ybc_hedu.course_hedu_id) == len(course_hedu_id))\
.order_by(Ybc_hedu.enrolled.desc())
else:
major_service = Major(course_hedu_id, '')
universities_service = MajorUniversities(course_hedu_id, '')
municipalities_service = MajorMunicipalities(course_hedu_id, '')
rank_query = Yc_hedu.query.filter(
Yc_hedu.year == max_year_query,
func.length(Yc_hedu.course_hedu_id) == len(course_hedu_id))\
.order_by(Yc_hedu.enrolled.desc())
rank = rank_query.all()
hedu_max_year = db.session.query(func.max(Yc_hedu.year)).first()[0]
if not bra_id:
header = {
'name': major_service.name(),
'enrolled': major_service.enrolled(),
'year': major_service.year(),
}
else:
header = {
'name': major_service.name(),
'enrolled': major_service.enrolled(),
'year': major_service.year(),
'bra_id': bra_id,
'location_name': major_service.location_name()
}
body = {
'university_with_more_enrolled': universities_service.university_with_more_enrolled(),
'highest_enrolled_number_by_university': universities_service.highest_enrolled_number(),
}
tabs = {
'general': [],
'enrollments': [
'enrollments-university-tree_map',
'new-api-enrollments-university-tree_map',
'enrollments-municipality-geo_map',
'new-api-enrollments-municipality-geo_map',
'enrollments-municipality-stacked',
'new-api-enrollments-municipality-stacked',
'enrollments-municipality-tree_map',
'new-api-enrollments-municipality-tree_map',
'enrollments-status-line',
'new-api-enrollments-status-line',
'enrollments-shift-stacked',
'new-api-enrollments-shift-stacked',
],
}
id_ibge = None
for index, maj in enumerate(rank):
if rank[index].course_hedu_id == course_hedu_id:
header['rank'] = index + 1
break
location = Bra.query.filter(Bra.id == bra_id).first()
if bra_id:
depth = location_depth(bra_id)
id_ibge = _location_service(depth, location)
is_municipality = True if depth == 'municipality' else False
major = Course_hedu.query.filter(Course_hedu.id == course_hedu_id).first()
if tab not in tabs:
abort(404)
if menu and menu not in tabs[tab]:
abort(404)
if header['enrolled'] is None or hedu_max_year != header['year']:
abort(404)
else:
return render_template('major/index.html', header=header, body=body, id_ibge=id_ibge, location=location, major=major, tab=tab, graph=graph)
def process(self, id=None):
if not h.auth.is_logged_in():
abort(401)
if not h.auth.is_admin():
abort(403)
c.sepdirnew = False
c.alreadysepdir = False
label = request.params.get('label', None)
id = request.params.get('ID', id)
sep_dir = request.params.get('sep_dir', None)
action = request.params.get('action', None)
action2 = request.params.get('action2', None)
if action2:
action = action2
values = dict(request.params)
if action=="Add":
school_of_thought_add = model.SchoolOfThought(label)
#school_of_thought_add.label = label
#setup search string and search pattern
school_of_thoughtname = school_of_thought_add.label
school_of_thoughtname_q = model.meta.Session.query(model.Entity)
o = model.Entity.label.like('( '+ school_of_thoughtname + ' )')
school_of_thoughtname_q = school_of_thoughtname_q.filter(o).order_by(func.length(model.Entity.label))
if school_of_thoughtname_q.count() == 0:
school_of_thought_add.searchpattern = "( " + school_of_thoughtname + " )"
school_of_thought_add.searchstring = school_of_thoughtname
else:
school_of_thought_add.searchpattern = "( " + label + " )"
school_of_thought_add.searchcstring = label
#reset old school_of_thought pattern to whole name too to avoid conflict
oldschool_of_thought = h.fetch_obj(model.SchoolOfThought, school_of_thoughtname_q.first().ID)
oldschool_of_thought.searchpattern = "( " + oldschool_of_thought.label + " )"
oldschool_of_thought.searchstring = oldschool_of_thought.label
meta.Session.add(oldschool_of_thought)
if sep_dir:
school_of_thought_add.sep_dir = sep_dir
c.school_of_thought = school_of_thought_add
meta.Session.add(school_of_thought_add)
meta.Session.flush()
meta.Session.commit()
c.found = True
c.message = "SchoolOfThought " + c.school_of_thought.label + " added successfully."
return render ('admin/school_of_thought-edit.html')
elif action=="Modify":
c.school_of_thought = h.fetch_obj(model.SchoolOfThought, id)
c.found = True
changed = False
#set values from form
if c.school_of_thought.label != label:
c.school_of_thought.label = label
changed = True
if c.school_of_thought.sep_dir != sep_dir:
c.school_of_thought.sep_dir = sep_dir
changed = True
#commit changes
meta.Session.flush()
meta.Session.commit()
if changed:
c.message = "SchoolOfThought " + c.school_of_thought.label + " modified successfully."
else:
c.message = "No change needed; SchoolOfThought " + c.school_of_thought.label + " not modified."
return render ('admin/school_of_thought-edit.html')
elif action == "Delete":
c.school_of_thought = h.fetch_obj(model.SchoolOfThought, values['ID'])
c.message = "SchoolOfThought # " + values['ID'] + " ("+ c.school_of_thought.label + ") deleted; please search for a new entity label on the left."
h.delete_obj(c.school_of_thought)
meta.Session.flush()
meta.Session.commit()
c.found = False
return render('admin/school_of_thought-edit.html')
def index(course_hedu_id):
bra_id = request.args.get('bra_id')
max_year_query = db.session.query(
func.max(Yc_hedu.year)).filter_by(course_hedu_id=course_hedu_id)
if bra_id:
major_service = Major(course_hedu_id, bra_id)
universities_service = MajorUniversities(course_hedu_id, bra_id)
municipalities_service = MajorMunicipalities(course_hedu_id, bra_id)
rank_query = Ybc_hedu.query.filter(
Ybc_hedu.year == max_year_query,
Ybc_hedu.bra_id == bra_id,
func.length(Ybc_hedu.course_hedu_id) == len(course_hedu_id))\
.order_by(Ybc_hedu.enrolled.desc())
else:
major_service = Major(course_hedu_id, '')
universities_service = MajorUniversities(course_hedu_id, '')
municipalities_service = MajorMunicipalities(course_hedu_id, '')
rank_query = Yc_hedu.query.filter(
Yc_hedu.year == max_year_query,
func.length(Yc_hedu.course_hedu_id) == len(course_hedu_id))\
.order_by(Yc_hedu.enrolled.desc())
rank = rank_query.all()
hedu_max_year = db.session.query(func.max(Yc_hedu.year)).first()[0]
if not bra_id:
header = {
'name': major_service.name(),
'enrolled': major_service.enrolled(),
'entrants': major_service.entrants(),
'graduates': major_service.graduates(),
'profile': major_service.profile(),
'year': major_service.year(),
'field_id': course_hedu_id[:2]
}
else:
header = {
'name': major_service.name(),
'enrolled': major_service.enrolled(),
'entrants': major_service.entrants(),
'graduates': major_service.graduates(),
'profile': major_service.profile(),
'year': major_service.year(),
'field_id': course_hedu_id[:2],
'id': course_hedu_id,
'bra_id': bra_id,
'location_name': major_service.location_name()
}
body = {
'university_with_more_enrolled': universities_service.university_with_more_enrolled(),
'highest_enrolled_number_by_university': universities_service.highest_enrolled_number(),
'municipality_with_more_enrolled': municipalities_service.municipality_with_more_enrolled(),
'municipality_with_more_enrolled_state': municipalities_service.municipality_with_more_enrolled_state(),
'highest_enrolled_number_by_municipality': municipalities_service.highest_enrolled_number(),
'university_with_more_entrants': universities_service.university_with_more_entrants(),
'highest_entrant_number_by_university': universities_service.highest_entrants_number(),
'municipality_with_more_entrants': municipalities_service.municipality_with_more_entrants(),
'municipality_with_more_entrants_state': municipalities_service.municipality_with_more_entrants_state(),
'highest_entrant_number_by_municipality': municipalities_service.highest_entrants_number(),
'university_with_more_graduates': universities_service.university_with_more_graduates(),
'highest_graduate_number_by_university': universities_service.highest_graduates_number(),
'municipality_with_more_graduates': municipalities_service.municipality_with_more_graduates(),
'municipality_with_more_graduates_state': municipalities_service.municipality_with_more_graduates_state(),
'highest_graduate_number_by_municipality': municipalities_service.highest_graduates_number()
}
for index, maj in enumerate(rank):
if rank[index].course_hedu_id == course_hedu_id:
header['rank'] = index + 1
break
location = Bra.query.filter(Bra.id == bra_id).first()
major = Course_hedu.query.filter(Course_hedu.id == course_hedu_id).first()
if header['enrolled'] is None or hedu_max_year != header['year']:
abort(404)
else:
return render_template('major/index.html', header=header, body=body, location=location, major=major)
def admin(self, id=None):
if not h.auth.is_logged_in():
abort(401)
if not h.auth.is_admin():
abort(403)
redirect = request.params.get('redirect', False)
add = request.params.get('add', False)
limit = request.params.get('limit', None)
sep_dir = request.params.get('sep_dir', "")
entity_q = Session.query(model.Entity)
c.found = False
c.custom = False
c.new = False
c.sep_dir = sep_dir
c.sepdirnew = False
c.alreadysepdir = False
if request.params.get('q'):
q = request.params['q']
o = model.Entity.label.like(q)
entity_q = entity_q.filter(o).order_by(func.length(model.Entity.label))
# if only 1 result, go ahead and view that entity
if redirect and entity_q.count() == 1:
c.entity = h.fetch_obj(model.Entity, entity_q.first().ID)
#now get type and route to correct edit page
#first, if it is an idea, typeID = 1
if c.entity.typeID == 1:
print "have an idea, q, entityq count = 1"
c.idea = h.fetch_obj(model.Idea, entity_q.first().ID)
c.found = True
id = c.idea.ID
c.message = 'Entity edit page for idea ' + c.idea.label
#set up c.search_string_list which will be used to structure intersection/union search pattern option
if request.params.get('sep_dir'):
sep_dir = request.params['sep_dir']
if not (c.idea.sep_dir):
c.idea.sep_dir = request.params['sep_dir']
c.sepdirnew = True
else:
c.alreadysepdir = True
c.entry_sep_dir = request.params['sep_dir']
c.search_string_list = c.idea.setup_SSL()
if re.search(' and ', c.idea.label):
c.search_pattern_list = ['union', 'intersection']
return render ('admin/idea-edit.html')
#thinkers
elif c.entity.typeID == 3:
print "have a thinker, q, entityq count = 1"
c.thinker = h.fetch_obj(model.Thinker, entity_q.first().ID)
c.found = True
id = c.thinker.ID
c.message = 'Entity edit page for thinker ' + c.thinker.label
if request.params.get('sep_dir'):
sep_dir = request.params['sep_dir']
if not (c.thinker.sep_dir):
c.thinker.sep_dir = request.params['sep_dir']
c.sepdirnew = True
else:
c.alreadysepdir = True
c.entry_sep_dir = request.params['sep_dir']
return render ('admin/thinker-edit.html')
elif c.entity.typeID == 4:
print "have a journal, q, entityq count = 1"
c.journal = h.fetch_obj(model.Journal, entity_q.first().ID)
c.found = True
id = c.journal.ID
c.message = 'Entity edit page for journal ' + c.journal.label
if request.params.get('sep_dir'):
sep_dir = request.params['sep_dir']
if not (c.journal.sep_dir):
c.journal.sep_dir = request.params['sep_dir']
c.sepdirnew = True
else:
c.alreadysepdir = True
c.entry_sep_dir = request.params['sep_dir']
return render ('admin/journal-edit.html')
elif c.entity.typeID == 5:
print "have a work, q, entityq count = 1"
c.work = h.fetch_obj(model.Work, entity_q.first().ID)
c.found = True
id = c.work.ID
c.message = 'Entity edit page for work ' + c.work.label
if request.params.get('sep_dir'):
sep_dir = request.params['sep_dir']
if not (c.work.sep_dir):
c.work.sep_dir = request.params['sep_dir']
c.sepdirnew = True
else:
c.alreadysepdir = True
c.entry_sep_dir = request.params['sep_dir']
return render ('admin/work-edit.html')
elif c.entity.typeID == 6:
print "have a school_of_thought, q, entityq count = 1"
c.school_of_thought = h.fetch_obj(model.SchoolOfThought, entity_q.first().ID)
c.found = True
id = c.school_of_thought.ID
c.message = 'Entity edit page for school_of_thought ' + c.school_of_thought.label
if request.params.get('sep_dir'):
sep_dir = request.params['sep_dir']
if not (c.school_of_thought.sep_dir):
c.school_of_thought.sep_dir = request.params['sep_dir']
c.sepdirnew = True
else:
c.alreadysepdir = True
c.entry_sep_dir = request.params['sep_dir']
return render ('admin/school_of_thought-edit.html')
elif redirect and entity_q.count() == 0:
c.message = "No match found for your search; if you would like to add your idea, please enter its label and sepdir into the field below."
c.new = True
c.prevvalue = q
return render ('admin/entity-add.html')
else:
return ('No exact match for your search; please click "back" and try again.')
if id is None:
print "I am here"
c.message = "Please input an idea using the search bar to the left."
return render ('admin/idea-edit.html')
else:
c.entity = h.fetch_obj(model.Entity, id)
c.found = True
c.message = 'Entity edit page for entity ' + c.entity.label
#get sep_dir if present--from admin.py action addentry
if request.params.get('sep_dir'):
sep_dir = request.params['sep_dir']
if not (c.entity.sep_dir):
c.entity.sep_dir = sep_dir
else:
c.message = c.message + "WARNING: entity already has a sep_dir [" + c.entity.sep_dir + "]. Not replacing with [" + sep_dir + "]. If you would like to do so, please do so manually in the form below."
if request.params.get('entry_sep_dir'):
entry_sep_dir = request.params['entry_sep_dir']
if not (c.entity.sep_dir):
c.entity.sep_dir = entry_sep_dir
else:
c.message = c.message + "WARNING: entity already has a sep_dir [" + c.entity.sep_dir + "]. Not replacing with [" + sep_dir + "]. If you would like to do so, please do so manually in the form below."
#set up c.search_string_list which will be used to structure intersection/union search pattern option
if c.entity.typeID == 1:
c.idea = h.fetch_obj(model.Idea, c.entity.ID)
c.search_string_list = c.idea.setup_SSL()
return render ('admin/idea-edit.html')
elif c.entity.typeID == 3:
c.thinker = h.fetch_obj(model.Thinker, c.entity.ID)
return render('admin/thinker-edit.html')
elif c.entity.typeID == 4:
c.journal = h.fetch_obj(model.Journal, c.entity.ID)
return render('admin/journal-edit.html')
elif c.entity.typeID == 5:
c.work = h.fetch_obj(model.Work, c.entity.ID)
return render('admin/work-edit.html')
elif c.entity.typeID == 6:
c.school_of_thought = h.fetch_obj(model.SchoolOfThought, c.entity.ID)
return render('admin/school_of_thought-edit.html')
c.entity = h.fetch_obj(model.Entity, id, new_id=True)
redirect(c.entity.url(action='admin'), code=303)
def admin(self, id=None):
if not h.auth.is_logged_in():
abort(401)
if not h.auth.is_admin():
abort(403)
c.sepdirnew = False
c.alreadysepdir = False
redirect = request.params.get('redirect', False)
add = request.params.get('add', False)
limit = request.params.get('limit', None)
entity_q = Session.query(Entity)
c.found = False
c.custom = False
c.new = False
if request.params.get('q'):
q = request.params['q']
o = Entity.label.like(q)
entity_q = entity_q.filter(o).order_by(func.length(Entity.label))
# if only 1 result, go ahead and view that idea
if redirect and entity_q.count() == 1:
print "have a q, entityq count = 1"
c.idea = h.fetch_obj(Idea, entity_q.first().ID)
c.found = True
id = c.idea.ID
c.message = 'Entity edit page for idea ' + c.idea.label
if request.params.get('entry_sep_dir'):
entry_sep_dir = request.params['entry_sep_dir']
if not (c.idea.sep_dir):
c.idea.sep_dir = request.params['entry_sep_dir']
c.sepdirnew = True
else:
c.alreadysepdir = True
c.entry_sep_dir = request.params['entry_sep_dir']
#set up c.search_string_list which will be used to structure intersection/union search pattern option
c.search_string_list = c.idea.setup_SSL()
if re.search(' and ', c.idea.label):
c.search_pattern_list = ['union', 'intersection']
return render ('admin/idea-edit.html')
elif redirect and entity_q.count() == 0:
c.message = "No match found for your search; if you would like to add your idea, please enter its label and sepdir into the field below."
c.new = True
c.prevvalue = q
return render ('admin/idea-edit.html')
else:
return ('No exact match for your search; please click "back" and try again.')
if id is None:
print "I am here"
c.message = "Please input an idea using the search bar to the left."
return render ('admin/idea-edit.html')
else:
c.idea = h.fetch_obj(Idea, id)
c.found = True
c.message = 'Entity edit page for idea ' + c.idea.label
if request.params.get('entry_sep_dir'):
entry_sep_dir = request.params['entry_sep_dir']
if not (c.idea.sep_dir):
c.idea.sep_dir = request.params['entry_sep_dir']
c.sepdirnew = True
else:
c.alreadysepdir = True
c.entry_sep_dir = request.params['entry_sep_dir']
#set up c.search_string_list which will be used to structure intersection/union search pattern option
c.search_string_list = c.idea.setup_SSL()
print "c.idea.searchpattern is " + c.idea.searchpattern
return render ('admin/idea-edit.html')
def process(self, id=None):
if not h.auth.is_logged_in():
abort(401)
if not h.auth.is_admin():
abort(403)
c.sepdirnew = False
c.alreadysepdir = False
label = request.params.get('label', None)
id = request.params.get('ID', id)
sep_dir = request.params.get('sep_dir', None)
action = request.params.get('action', None)
action2 = request.params.get('action2', None)
if action2:
action = action2
values = dict(request.params)
if action=="Add":
thinker_add = Thinker(label)
thinker_add.label = label
#setup search string and search pattern
lastname = thinker_add.label.split(' ').pop()
lastname_q = Session.query(Entity)
o = Entity.searchstring.like(lastname)
lastname_q = lastname_q.filter(o).order_by(func.length(Entity.label))
if lastname_q.count() == 0:
#if there's no match currently to last name, can use last name alone as searchpattern/searchstring
thinker_add.searchpatterns.append(lastname)
thinker_add.searchstring = lastname
else:
#otherwise, we need to use the whole name for both, and reset the other pattern to full name too
thinker_add.searchpatterns.append(label)
thinker_add.searchstring = label
#reset old thinker pattern to whole name too to avoid conflict
oldthinker = h.fetch_obj(Thinker, lastname_q.first().ID)
oldthinker.searchpatterns = [oldthinker.label]
oldthinker.searchstring = oldthinker.label
Session.add(oldthinker)
if sep_dir:
thinker_add.sep_dir = sep_dir
c.thinker = thinker_add
Session.add(thinker_add)
Session.flush()
Session.commit()
c.found = True
c.message = "Thinker " + c.thinker.label + " added successfully."
return render ('admin/thinker-edit.html')
elif action=="Modify":
c.thinker = h.fetch_obj(Thinker, id)
c.found = True
changed = False
searchpatterns = []
for k, v in values.items():
key = ""
if k.startswith('searchpatterns'):
varname, num = k.split('.')
key = 'delsearchpattern.%s'%(num)
keyval = request.params.get(key, False)
if not keyval:
searchpatterns.append(v)
if values['newsearchpattern']:
searchpatterns.append(values['newsearchpattern'])
changed = True
#do manually edited searchpatterns first, so we don't write over them with the new default ones if the searchstring has been changed
if c.thinker.searchpatterns != searchpatterns:
c.thinker.searchpatterns = searchpatterns
changed = True
#set values from form
if c.thinker.name != values['name']:
c.thinker.name = values['name']
changed = True
if c.thinker.label != values['label']:
c.thinker.name = values['label']
changed = True
if c.thinker.searchstring != values['searchstring']:
c.thinker.searchstring = values['searchstring']
changed = True
if c.thinker.sep_dir != values['sep_dir']:
c.thinker.sep_dir = values['sep_dir']
changed = True
if c.thinker.wiki != values['wiki']:
c.thinker.wiki = values['wiki']
changed = True
if c.thinker.birth_day != values['birth_day']:
c.thinker.birth_day = values['birth_day']
changed = True
c.thinker.birth_day = values['birth_day']
if c.thinker.death_day != values['death_day']:
c.thinker.death_day = values['death_day']
changed = True
if c.thinker.birth_month != values['birth_month']:
c.thinker.birth_month = values['birth_month']
changed = True
if c.thinker.death_month != values['death_month']:
c.thinker.death_month = values['death_month']
changed = True
if not (c.thinker.birth_year == values['birth_year'] + " " + values['bornbc']):
if c.thinker.birth_year != values['birth_year']:
c.thinker.birth_year = values['birth_year']
changed = True
if c.thinker.birth_year and values['bornbc'] and not re.search("(BC)|(AD)",c.thinker.birth_year):
c.thinker.birth_year = c.thinker.birth_year + " " + values['bornbc']
changed = True
if not (c.thinker.death_year == values['death_year'] + " " + values['diedbc']):
if c.thinker.death_year != values['death_year']:
c.thinker.death_year = values['death_year']
changed = True
if c.thinker.death_year and values['diedbc']and not re.search("(BC)|(AD)",c.thinker.death_year):
c.thinker.death_year = c.thinker.death_year + " " + values['diedbc']
changed = True
#commit changes
Session.flush()
Session.commit()
if changed:
c.message = "Thinker " + c.thinker.label + " modified successfully."
else:
c.message = "No changes detected. Thinker " + c.thinker.label + " not modified."
return render ('admin/thinker-edit.html')
elif action == "Delete":
c.thinker = h.fetch_obj(Thinker, values['ID'])
c.message = "Thinker # " + values['ID'] + " ("+ c.thinker.label + ") deleted; please search for a new entity label on the left."
h.delete_obj(c.thinker)
Session.flush()
Session.commit()
c.found = False
return render('admin/thinker-edit.html')
def process(self, id=None):
if not h.auth.is_logged_in():
abort(401)
if not h.auth.is_admin():
abort(403)
c.sepdirnew = False
c.alreadysepdir = False
label = request.params.get('label', None)
id = request.params.get('ID', id)
sep_dir = request.params.get('sep_dir', None)
action = request.params.get('action', None)
action2 = request.params.get('action2', None)
if action2:
action = action2
values = dict(request.params)
if action=="Add":
work_add = model.Work(label)
#work_add.label = label
#setup search string and search pattern
workname = work_add.label
workname_q = model.meta.Session.query(model.Entity)
o = model.Entity.label.like('( '+ workname + ' )')
workname_q = workname_q.filter(o).order_by(func.length(model.Entity.label))
if workname_q.count() == 0:
work_add.searchpattern = "( " + workname + " )"
work_add.searchstring = workname
else:
work_add.searchpattern = "( " + label + " )"
work_add.searchstring = label
#reset old work pattern to whole name too to avoid conflict
oldwork = h.fetch_obj(model.Work, workname_q.first().ID)
oldwork.searchpattern = "( " + oldwork.label + " )"
oldwork.searchstring = oldwork.label
meta.Session.add(oldwork)
if sep_dir:
work_add.sep_dir = sep_dir
c.work = work_add
meta.Session.add(work_add)
meta.Session.flush()
meta.Session.commit()
c.found = True
c.message = "Work " + c.work.label + " added successfully."
return render ('admin/work-edit.html')
elif action=="Modify":
c.work = h.fetch_obj(model.Work, id)
c.found = True
changed = False
#set values from form
if c.work.label != label:
c.work.label = label
changed = True
if c.work.sep_dir != sep_dir:
c.work.sep_dir = sep_dir
changed = True
#commit changes
meta.Session.flush()
meta.Session.commit()
if changed:
c.message = "Work " + c.work.label + " modified successfully."
else:
c.message = "No change required; Work " + c.work.label + " not modified."
return render ('admin/work-edit.html')
elif action == "Delete":
c.work = h.fetch_obj(model.Work, values['ID'])
c.message = "Work # " + values['ID'] + " ("+ c.work.label + ") deleted; please search for a new entity label on the left."
h.delete_obj(c.work)
meta.Session.flush()
meta.Session.commit()
c.found = False
return render('admin/work-edit.html')
def get_records_len_str_greater_or_less_than(args, model, attribute):
attr_pos = request.args.getlist('field_len').index(attribute)
attr_op = request.args
try:
if 'gte' in request.args:
gte = int(request.args.get('gte'))
if 'lte' in request.args:
lte = int(request.args.get('lte'))
if 'gt' in request.args:
gt = int(request.args.get('gt'))
if 'lt' in request.args:
lt = int(request.args.get('lt'))
except:
raise ValueError("gt, lt, gte, lte values must be integer")
if 'gte' in locals() and 'lte' in locals():
records = model.query.filter(func.length(attribute) >= gte, func.length(attribute) <= lte)
elif 'gt' in locals() and 'lte' in locals():
records = model.query.filter(func.length(attribute) > gt, func.length(attribute) <= lte)
elif 'gte' in locals() and 'lt' in locals():
records = model.query.filter(func.length(attribute) >= gte, func.length(attribute) < lt)
elif 'gt' in locals() and 'lt' in locals():
records = model.query.filter(func.length(attribute) > gt, func.length(attribute) < lt)
elif 'gte' in locals():
records = model.query.filter(func.length(attribute) >= gte)
elif 'lte' in locals():
records = model.query.filter(func.length(attribute) <= lte)
elif 'gt' in locals():
records = model.query.filter(func.length(attribute) > gt)
elif 'lt' in locals():
records = model.query.filter(func.length(attribute) < lt)
return records
