def check_abandon_cont(max):
"""Check changes with an "Abandoned" but continuing with activity.
Parameters
----------
max: int
Max number of cases to show among those violating the check.
"""
q_abandons = session.query(
label("id", DB.Change.uid),
label("date", func.min(DB.Message.date)),
label("num", DB.Change.number)
) \
.select_from(DB.Change) \
.join(DB.Message) \
.filter (or_ (DB.Message.header == "Abandoned",
DB.Message.header.like ("Patch%Abandoned"))) \
.group_by(DB.Change.uid) \
.subquery()
q = session.query(
label("num", q_abandons.c.num)
) \
.join(DB.Message,
DB.Message.change_id == q_abandons.c.id) \
.filter(DB.Message.date > q_abandons.c.date) \
.group_by(q_abandons.c.id)
changes = q.count()
print "Changes abandoned, with activity after abandon (" \
+ str(changes) + "): ",
for change in q.limit(max).all():
print change.num
print
def select_listpersons(self, kind = "all"):
"""Select a list of persons (authors, committers)
- kind: kind of person to select
authors: authors of commits
committers: committers of commits
all: authors and committers
Returns a SCMQuery object, with (id, name, email) selected.
"""
query = self.add_columns (label("id", func.distinct(People.id)),
label("name", People.name),
label('email', People.email))
if kind == "authors":
return query.join (SCMLog, People.id == SCMLog.author_id)
elif kind == "committers":
return query.join (SCMLog, People.id == SCMLog.committer_id)
elif kind == "all":
return query.join (SCMLog,
People.id == SCMLog.author_id or
People.id == SCMLog.committer_id)
else:
raise Exception ("select_listpersons: Unknown kind %s." \
% kind)
def pending_requests_for_user(self, user):
# type: (str) -> List[UserGroupRequest]
requester = aliased(User)
on_behalf_of = aliased(User)
sql_requests = self.session.query(
Request.id,
Request.status,
label("requester", requester.username),
Group.groupname,
label("on_behalf_of", on_behalf_of.username),
).filter(
Request.on_behalf_obj_type == OBJ_TYPES["User"],
Request.on_behalf_obj_pk == on_behalf_of.id,
Request.requester_id == requester.id,
Request.requesting_id == Group.id,
Request.status == "pending",
)
requests = []
for sql_request in sql_requests:
request = UserGroupRequest(
id=sql_request.id,
user=sql_request.on_behalf_of,
group=sql_request.groupname,
requester=sql_request.requester,
status=sql_request.status,
)
requests.append(request)
return requests
def query_start (changes = None):
"""Produce a query for selecting chnage start events.
The query will select "date" as the date for the event, and
"change" for the change number. The date is calculated as
the date of the first revision.
Parameters
----------
changes: list of int
List of change numbers to consider.
Returns
-------
query_gerrit.query: produced query
"""
q = session.query(
label ("date", func.min(DB.Revision.date)),
label ("change", DB.Change.number),
) \
.join(DB.Change)
q = q.group_by(DB.Change.uid)
if changes is not None:
q = q.filter(DB.Change.number.in_(changes))
return q
def select_personsdata(self, kind):
"""Adds columns with persons data to select clause.
Adds people.name, people.email to the select clause of query.
Does not join new tables.
Parameters
----------
kind: {"authors", "committers"}
Kind of person to select
Returns
-------
SCMObject: Result query, with new fields: id, name, email
"""
query = self.add_columns (label("person_id", DB.People.id),
label("name", DB.People.name),
label('email', DB.People.email))
if kind == "authors":
person = DB.SCMLog.author_id
elif kind == "committers":
person = DB.SCMLog.committer_id
else:
raise Exception ("select_personsdata: Unknown kind %s." \
% kind)
if DB.SCMLog in self.joined:
query = query.filter (DB.People.id == person)
else:
self.joined.append (DB.SCMLog)
query = query.join (DB.SCMLog, DB.People.id == person)
return query
def group_by_period (self):
"""Group by time period (per month)"""
return self \
.add_columns (label("month", func.month(SCMLog.date)),
label("year", func.year(SCMLog.date))) \
.group_by("month", "year").order_by("year", "month")
def get_query(qtype = 'none', qobject = 'none'):
if qtype != 'none' and qobject != 'none':
# built queries for specified subset of patients
query = db.session.query(label('sid', qobject.c.patient_sid),
label('value_d', qobject.c.double_value),
label('value_s', qobject.c.string_value),
label('attribute', qobject.c.attribute_value))
elif qtype == 'count' and qobject == 'none':
# count of patients
query = db.session.query(distinct(Clinical.patient_sid).label('sid'))
else:
# entire population
query = db.session.query(distinct(Clinical.patient_sid).label('sid'),
literal_column("'complement'").label('attribute'),
literal_column("'0'").label('value_d'),
literal_column("'null'").label('value_s'))
db.session.commit()
db.session.close()
return query
def check_newer_dates(max):
"""Check that dates related to a change are newer than creation date.
This will print sumary stats about dates that are not correct,
and will show at most max cases.
Parameters
----------
max: int
Max number of cases to show among those violating the check.
"""
res = session.query(
label ("number",
DB.Change.number),
label ("created",
DB.Change.created),
label ("updated",
DB.Change.updated)
) \
.filter (DB.Change.created > DB.Change.updated) \
.order_by (desc (func.datediff(DB.Change.created,
DB.Change.updated)))
cases = res.limit(max).all()
for case in cases:
print str(case.number) + ": " + str(case.created) + \
" (created), " + str(case.updated) + " (updated) Mismatch: " + \
str(case.created - case.updated) + ")"
print "Total number of mismatchs: " + str(res.count())
def check_first_revision(max):
"""Check that changes have a first revision.
Parameters
----------
max: int
Max number of cases to show among those violating the check.
"""
q = session.query(
label ("revision", DB.Revision.uid),
) \
.join (DB.Change) \
.filter (DB.Revision.number == 1) \
.group_by (DB.Change.uid)
print "Changes with first revision: " + str(q.count())
first = session.query(
label ("change", DB.Revision.change_id),
) \
.filter (DB.Revision.number == 1) \
.subquery()
q = session.query(
label ("change", DB.Change.number),
) \
.filter (~DB.Change.uid.in_(first))
for change in q.limit(max).all():
print change.change
print "Changes with no first revision: " + str(q.count())
def weekList():
if not redis.llen('rank:week'):
rows = db_session.query(
User.id,
User.username,
label('number', func.count(Investment.amount)),
label('total_amount', func.sum(Investment.amount))
).filter(
Investment.user_id == User.id,
cast(Investment.added_at, Date) <= datetime.datetime.today(),
cast(Investment.added_at, Date) >= datetime.datetime.today() -
datetime.timedelta(weeks=1)
).group_by(User.id).order_by(
func.sum(Investment.amount).desc()
).limit(15).all()
rank_list = []
for i in rows:
i = dict(zip(i.keys(), i))
data = {
'id': i['id'],
'username': i['username'],
'total_amount': float(i['total_amount']),
'number': i['number']
}
rank_list.append(data)
redis.rpush('rank:week', json.dumps(data))
redis.expire('rank:week', 3600)
else:
rank_list = [json.loads(i.decode()) for i in redis.lrange('rank:week', 0, redis.llen('rank:week'))]
return rank_list
def totalList():
if not redis.llen('rank:total'):
total_row = db_session.query(
User.id,
User.username,
label('number', func.count(Investment.amount)),
label('total_amount', func.sum(Investment.amount))
).filter(
Investment.user_id == User.id,
).group_by(User.id).order_by(
func.sum(Investment.amount).desc()
).limit(15).all()
total_list = []
for i in total_row:
i = dict(zip(i.keys(), i))
data = {
'id': i['id'],
'username': i['username'],
'total_amount': float(i['total_amount']),
'number': i['number']
}
total_list.append(data)
redis.rpush('rank:total', json.dumps(data))
redis.expire('rank:total', 3600)
else:
total_list = [json.loads(i.decode()) for i in redis.lrange('rank:total', 0, redis.llen('rank:total'))]
return total_list
def select_tz (self):
"""Select time zones and other related fields from database.
Selects count of messages, count of distinct senders,
time zone.
Returns
-------
Query object
"""
query = self.add_columns(
label("tz",
((DB.Messages.first_date_tz.op('div')(3600) + 36) % 24) - 12),
label("messages",
func.count(func.distinct(DB.Messages.message_ID))),
label("authors",
func.count(func.distinct(DB.MessagesPeople.email_address))))
self.joined.append (DB.Messages)
if DB.MessagesPeople not in self.joined:
query = query.join (DB.MessagesPeople)
self.joined.append (DB.MessagesPeople)
query = query.filter (DB.MessagesPeople.type_of_recipient == "From")
return query
def select_listpersons_uid(self, kind = "all"):
"""Select a list of persons (authors, committers), using uids
- kind: kind of person to select
authors: authors of commits
committers: committers of commits
all: authors and committers
Returns a SCMQuery object, with (id, name, email) selected.
"""
query = self.add_columns (label("id", func.distinct(UPeople.id)),
label("name", UPeople.identifier)) \
.join (PeopleUPeople, UPeople.id == PeopleUPeople.upeople_id)
if kind == "authors":
return query.join (SCMLog,
PeopleUPeople.people_id == SCMLog.author_id)
elif kind == "committers":
return query.join (SCMLog,
PeopleUPeople.people_id == SCMLog.committer_id)
elif kind == "all":
return query.join (SCMLog,
PeopleUPeople.people_id == SCMLog.author_id or
PeopleUPeople.people_id == SCMLog.committer_id)
else:
raise Exception ("select_listpersons_uid: Unknown kind %s." \
% kind)
def calc_duration_changes(max):
"""Calculate duration of changes (time from created to updated).
This will print sumary stats about the duration of the
changes in the review system, and will show some of them.
Parameters
----------
max: int
Max number of changes to show.
"""
res = session.query(
label ("number",
DB.Change.number),
label ("start",
DB.Change.created),
label ("finish",
DB.Change.updated),
) \
.filter (DB.Change.created < DB.Change.updated) \
.order_by (desc (func.datediff(DB.Change.updated,
DB.Change.created)))
cases = res.limit(max).all()
for case in cases:
print str(case.number) + ": " + str(case.start) + \
" (start), " + str(case.finish) + " (finish) Duration: " + \
str(case.finish - case.start)
def select_listcommits(self):
"""Select a list of commits"""
if DB.SCMLog not in self.joined:
self.joined.append(DB.SCMLog)
return self \
.add_columns (label("id", func.distinct(DB.SCMLog.id)),
label("date", DB.SCMLog.date))
def repostat(repoid = -1):
if repoid == -1:
return ses.query(Kuyruk.repository, Kuyruk.branch, Kuyruk.durum, label('sayi', func.count(Kuyruk.id))).group_by(Kuyruk.repository, Kuyruk.branch, Kuyruk.durum).all()
else:
cevap = ses.query(Repo.repo, Repo.branch).filter_by(id=repoid).one()
c2 = ses.query(Kuyruk.repository, Kuyruk.branch, Kuyruk.durum,
label('sayi', func.count(Kuyruk.id))).filter_by(repository=cevap.repo , branch=cevap.branch).group_by(
Kuyruk.repository, Kuyruk.branch, Kuyruk.durum).all()
return c2
def show_summary_projects ():
"""Summary of main stats by project (and name of projects).
"""
q = session.query (label ("project", DB.Change.project),
label ("changes", func.count(DB.Change.uid))
) \
.group_by(DB.Change.project) \
.order_by(desc("changes"))
for project, changes in q.all():
print project + ": " + str(changes)
def _get_nodes_from_db(session):
return session.query(
label("type", literal("User")),
label("name", User.username)
).filter(
User.enabled == True
).union(session.query(
label("type", literal("Group")),
label("name", Group.groupname))
).filter(
Group.enabled == True
).all()
def get_results(self, start_date, end_date):
query = select([
label('user_id', self.sql_table.c.user_id),
label('count', func.count(self.sql_table.c.doc_id))
]).where(and_(
operators.ge(self.sql_table.c.time_end, start_date),
operators.lt(self.sql_table.c.time_end, end_date),
operators.in_op(self.sql_table.c.user_id, self.users_needing_data)
)).group_by(
self.sql_table.c.user_id
)
return self._run_query(query)
def get_results(self):
query = select([
label('user_id', self.sql_table.c.owner_id),
label('count', func.count(self.sql_table.c.doc_id))
]).where(and_(
self.sql_table.c.type != self.cc_case_type,
self.sql_table.c.closed == 0,
operators.in_op(self.sql_table.c.owner_id, self.users_needing_data),
)).group_by(
self.sql_table.c.owner_id
)
return self._run_query(query)
def select_listbranches(self):
"""Select list of branches in repo
Returns a list with a tuple (id, name) per branch
The Actions table is used, instead of the Branches table,
so that some filters, such as filter_period() can be used
"""
query = self.add_columns (label("id",
func.distinct(Actions.branch_id)),
label("name",
Branches.name))
query = query.join(Branches)
return query
def query_revisions ():
"""Produce a query for selecting new revision events.
The query will select "date" in revision record as the date
for the event, and "change" for the change number.
"""
q = session.query(
label ("date", DB.Revision.date),
label ("change", DB.Change.number),
)
q = q.select_from(DB.Revision) \
.join(DB.Change)
return q
def get_all_months_as_dictionary():
# get all months a user have done exercises
all_months = db.session.query(label('year', extract('year', Exercise.date)), label('month', extract('month', Exercise.date)))\
.group_by('year', 'month')\
.order_by('year desc, month desc')\
.filter(Exercise.user_id == g.user.id)\
.all()
# convert list result to list dates
all_months_as_date = [DateHelper.string_to_date(('%i/%i/1' % (year, month))) for (year, month) in all_months]
# convert list to dictionary
return [(DateHelper.generate_id_by_month_year(item),
DateHelper.date_to_year_month_string(item))
for item in all_months_as_date]
def columns(self, include_type_in_result, distinct_docs=False):
doc_id = self.sql_table.c.doc_id
if distinct_docs:
doc_id = distinct(doc_id)
columns = [
label('owner_id', self.owner_column),
label('count', func.count(doc_id)),
]
if include_type_in_result:
columns.append(
label('type', self.type_column)
)
return columns
def get(self):
query = self.get_argument("query", "")
offset = int(self.get_argument("offset", 0))
limit = int(self.get_argument("limit", 100))
if limit > 9000:
limit = 9000
groups = (
self.session.query(
label("type", literal("Group")),
label("id", Group.id),
label("name", Group.groupname),
)
.filter(Group.enabled == True, Group.groupname.like("%{}%".format(query)))
.subquery()
)
permissions = (
self.session.query(
label("type", literal("Permission")),
label("id", Permission.id),
label("name", Permission.name),
)
.filter(Permission.enabled == True, Permission.name.like("%{}%".format(query)))
.subquery()
)
users = (
self.session.query(
label("type", literal("User")), label("id", User.id), label("name", User.username)
)
.filter(User.enabled == True, User.username.like("%{}%".format(query)))
.subquery()
)
results_query = self.session.query("type", "id", "name").select_entity_from(
union_all(users.select(), permissions.select(), groups.select())
)
total = results_query.count()
results = results_query.offset(offset).limit(limit).all()
if len(results) == 1:
result = results[0]
return self.redirect("/{}s/{}".format(result.type.lower(), result.name))
self.render(
"search.html",
results=results,
search_query=query,
offset=offset,
limit=limit,
total=total,
)
def collection_timestamp(self, collection_id, parent_id, auth=None):
"""Get the highest timestamp of every objects in this `collection_id` for
this `parent_id`.
.. note::
This should take deleted objects into account.
:param str collection_id: the collection id.
:param str parent_id: the collection parent.
:returns: the latest timestamp of the collection.
:rtype: int
"""
tb = Timestamps.__table__
qry = select([label('last_modified', func.max(tb.c.last_modified))]).where(and_(
tb.c.parent_id == parent_id,
tb.c.collection_id == collection_id))
last_modified, = Session.execute(qry).fetchone()
if last_modified is None:
last_modified = datetime.datetime.utcnow()
with transaction.manager:
Session.add(Timestamps(parent_id=parent_id, collection_id=collection_id,
last_modified=last_modified))
return last_modified.replace(tzinfo=datetime.timezone.utc).timestamp()
def mg_quantities(self):
#Filter to include sales with same brand, mg, md
query = db.session.query(SalesBySize.size, label('quantity', func.sum(SalesBySize.quantity))). \
filter_by(brand_name=self.brand_name, merch_group_name=self.taxonomy['merch_group_name'],
merch_div_name=self.taxonomy['merch_div_name']). \
group_by(SalesBySize.size)
return {result.size: result.quantity for result in query.all()}
def checkout_counts(self, out, session):
out.writerow([
'Game Code',
'Game Name',
'# Checkouts',
])
tt_games_and_counts = session.query(
TabletopGame, label('checkout_count', func.count(TabletopCheckout.id)),
).outerjoin(TabletopGame.checkouts).group_by(TabletopGame.id).all()
all_checkouts_count = 0
for result in tt_games_and_counts:
game = result[0]
all_checkouts_count += result.checkout_count
out.writerow([
game.code,
game.name,
result.checkout_count,
])
out.writerow([
'N/A',
'All Games',
all_checkouts_count,
])
def group_by_repo (self, names = False):
"""Group by repository
- names: include names of repositories as a column
Returns a SCMQuery with new columns (repository id,
and repository name, if names is True), grouped by
repository id."""
query = self.add_columns (label("repo", SCMLog.repository_id))
if names:
query = query.add_columns (label("name", Repositories.name)) \
.join (Repositories,
SCMLog.repository_id == Repositories.id)
query = query.group_by("repo").order_by("repo")
return query
def select_nscmlog(self, variables):
"""Select a variable which is a field in Scmlog.
- variables (list): variables to select
Currently supported: "commits", "authors", "committers"
"""
if not isinstance(variables, (list, tuple)):
raise Exception ("select_nscmlog: Argument is not list or tuple")
elif len (variables) == 0:
raise Exception ("select_nscmlog: No variables")
fields = []
for variable in variables:
if variable == "commits":
name = "nocommits"
field = SCMLog.id
elif variable == "authors":
name = "nauthors"
field = SCMLog.author_id
elif variable == "committers":
name = "ncommitters"
field = SCMLog.committer_id
else:
raise Exception ("select_nscmlog: Unknown variable %s." \
% variable)
fields.append (label (name,
func.count(func.distinct(field))))
return self.add_columns (*fields)
def signup_process():
"""Add Event signup order to our database."""
# Decoding the JSON object getting from the
parent_id = request.form['userid']
event_id = request.form['eventid']
op = request.form['opcode']
# print "Activity id i populated below"
# print event_id
# print op
if op == "register":
#updating - increment the no_of_reg_spots by 1 in the database column no_of_reg_spots
register_event = Registration(parent_id=parent_id,
event_id=event_id,
slot_id=1,
registration_date=datetime.now(),
status='Registered',
showup="")
db.session.add(register_event)
update_no_of_reg_spots = Event.query.get(event_id)
update_no_of_reg_spots.no_of_reg_spots += 1
db.session.commit()
# Integrating the Gmail API and making a call to the mailer
# print session["email"]
# print ('before sending Register email')
eventmessage = {}
eventmessage['eventname'] = update_no_of_reg_spots.event_name
eventmessage['eventdate'] = update_no_of_reg_spots.event_date.strftime(
"%B %d, %Y")
eventmessage['eventdesc'] = update_no_of_reg_spots.event_description
# print eventmessage
# print ('before sending email template email')
templateobj = EmailTemplate(template_name='registration.txt',
values=eventmessage)
message = templateobj.render()
print('before sending before actual email')
print registerSub
print message
send_notification(session["email"], registerSub, message)
print('before sending Register email')
# user_registration = Registration.query.filter_by(parent_id=parent).subquery()
# signupObj = {}
# sign_up = db.session.query(Event.event_id,Event.event_name,Event.event_description,Event.event_date,Event.event_status,label('no_of_remaining_spots',Event.no_of_spots - Event.no_of_reg_spots),user_registration.c.parent_id,user_registration.c.status).outerjoin(user_registration,Event.event_id==user_registration.c.event_id).filter(Event.event_date >= date.today(), Event.event_id == event_id ).all()
# for item in sign_up:
# # signupObj["event_id"] = item.event_id
# # signupObj["event_name"] = item.event_name
# # signupObj["event_description"] = item.event_description
# # signupObj["event_date"] = item.event_date
# # signupObj["no_of_remaining_spots"] = item.no_of_remaining_spots
# signupObj["status"] = item.status
#print signupObj
#print jsonify(register_event)
print "jsonyfy works"
user_registration = Registration.query.filter_by(
parent_id=session["user_id"]).subquery()
sign_up = db.session.query(
Event.event_id, Event.no_of_waitlist_spots, Event.event_name,
Event.event_description, Event.event_status,
label('no_of_remaining_spots',
Event.no_of_spots - Event.no_of_reg_spots),
user_registration.c.parent_id,
user_registration.c.status).outerjoin(
user_registration,
Event.event_id == user_registration.c.event_id).filter(
Event.event_date >= date.today(),
Event.event_id == event_id).all()
#print jsonify(dumps(sign_up))
for i in sign_up:
x = i
print x.no_of_waitlist_spots
return jsonify(json_list=x)
#return jsonify(register_event)
# print "Shilpa updating count"
# print update_no_of_reg_spots.no_of_reg_spots
elif op == "waitlist":
#updating - increment the no_of_waitlisted_spots by 1 in the database column no_of_reg_spots
register_event = Registration(parent_id=parent_id,
event_id=event_id,
slot_id=1,
registration_date=datetime.now(),
status='Waitlisted',
showup="")
db.session.add(register_event)
update_no_of_waitlist_spots = Event.query.get(event_id)
update_no_of_waitlist_spots.no_of_waitlist_spots += 1
db.session.commit()
# print "I am in waitlist"
# print session["email"]
# print ('before sending Register email')
eventmessage = {}
eventmessage['eventname'] = update_no_of_waitlist_spots.event_name
eventmessage[
'eventdate'] = update_no_of_waitlist_spots.event_date.strftime(
"%B %d, %Y")
eventmessage[
'eventdesc'] = update_no_of_waitlist_spots.event_description
# print eventmessage
# print ('before sending email template email')
templateobj = EmailTemplate(template_name='waitlist.txt',
values=eventmessage)
message = templateobj.render()
# print ('before sending before actual email')
# print registerSub
# print message
# Integrating the Gmail API and making a call to the mailer
send_notification(session["email"], registerSub, message)
print('before sending Register email')
# user_registration = Registration.query.filter_by(parent_id=parent).subquery()
# signupObj = {}
# sign_up = db.session.query(Event.event_id,Event.event_name,Event.event_description,Event.event_date,Event.event_status,label('no_of_remaining_spots',Event.no_of_spots - Event.no_of_reg_spots),user_registration.c.parent_id,user_registration.c.status).outerjoin(user_registration,Event.event_id==user_registration.c.event_id).filter(Event.event_date >= date.today(), Event.event_id == event_id ).all()
# for item in sign_up:
# # signupObj["event_id"] = item.event_id
# # signupObj["event_name"] = item.event_name
# # signupObj["event_description"] = item.event_description
# # signupObj["event_date"] = item.event_date
# # signupObj["no_of_remaining_spots"] = item.no_of_remaining_spots
# signupObj["status"] = item.status
#print signupObj
#print jsonify(register_event)
# print "jsonyfy works"
user_registration = Registration.query.filter_by(
parent_id=session["user_id"]).subquery()
sign_up = db.session.query(
Event.event_id, Event.no_of_waitlist_spots, Event.event_name,
Event.event_description, Event.event_status,
label('no_of_remaining_spots',
Event.no_of_spots - Event.no_of_reg_spots),
user_registration.c.parent_id,
user_registration.c.status).outerjoin(
user_registration,
Event.event_id == user_registration.c.event_id).filter(
Event.event_date >= date.today(),
Event.event_id == event_id).all()
#print jsonify(dumps(sign_up))
for i in sign_up:
x = i
# print x.no_of_waitlist_spots
return jsonify(json_list=x)
#return jsonify(register_event)
# print "Shilpa updating count"
# print update_no_of_reg_spots.no_of_reg_spots
else:
#Checking if the op = "cancel"
print "Inside else block"
print event_id
print parent_id
update_reg_status = Registration.query.filter_by(
event_id=event_id, parent_id=parent_id).one()
update_reg_status.status = 'Cancelled'
# Finding the minimum reistration for an event id with waitlisted status
waitlisted_regid = db.session.query(
func.min(Registration.registration_id).label('min_reg_id')).filter(
event_id == event_id,
Registration.status == 'Waitlisted').one()
# Bringing the whole record(object)
# When the cancel is happening
# update the database column no_of_spots decrement by 1
# If a user is not in the waitlist
if waitlisted_regid:
print "I am in null"
update_no_of_spots = Event.query.get(event_id)
update_no_of_spots.no_of_reg_spots -= 1
else:
#moving from waitlisted to registration action
#decrement waitlist spots by 1
#increment no of registered spots by 1
who_is_waitlisted = Registration.query.filter(
Registration.registration_id ==
waitlisted_regid.min_reg_id).one()
who_is_waitlisted.status = 'Registered'
update_no_of_waitspots = Event.query.get(event_id)
update_no_of_waitspots.no_of_waitlist_spots -= 1
who_is_waitlisted_user = User.query.filter_by(
user_id=who_is_waitlisted.parent_id).one()
sms_message = "Dear " + who_is_waitlisted_user.first_name + " " + who_is_waitlisted_user.last_name + " Your reservaton for event " + update_no_of_waitspots.event_name + " on " + update_no_of_waitspots.event_date.strftime(
"%B %d, %Y"
) + " has bee confirmed. Please make changes by signing to MySignUp application. Enjoy and Engage with kids - MySignUp Team"
send_twillio_sms(sms_message, who_is_waitlisted_user.phone_number)
db.session.commit()
# Integrating the Gmail API and making a call to the mailer
# print session["email"]
# print ('before sending cancelling ****** email')
eventmessage = {}
eventmessage['eventname'] = update_no_of_spots.event_name
eventmessage['eventdate'] = update_no_of_spots.event_date.strftime(
"%B %d, %Y")
eventmessage['eventdesc'] = update_no_of_spots.event_description
# print eventmessage
# print ('before sending email template email')
templateobj = EmailTemplate(template_name='cancellation.txt',
values=eventmessage)
message = templateobj.render()
# print ('before sending before actual email')
# Integrating the Gmail API and making a call to the mailer
send_notification(session["email"], registerCancel, message)
#send_notification(session["email"],'You are all set for baking','Test the function')
print "jsonyfy works"
user_registration = Registration.query.filter_by(
parent_id=session["user_id"]).subquery()
sign_up = db.session.query(
Event.event_id, Event.no_of_waitlist_spots, Event.event_name,
Event.event_description, Event.event_status,
label('no_of_remaining_spots',
Event.no_of_spots - Event.no_of_reg_spots),
user_registration.c.parent_id,
user_registration.c.status).outerjoin(
user_registration,
Event.event_id == user_registration.c.event_id).filter(
Event.event_date >= date.today(),
Event.event_id == event_id).all()
#print jsonify(dumps(sign_up))
#
for i in sign_up:
x = i
# print x
# print x.no_of_waitlist_spots
# x is the tuple that needs to be jsonified(as key-value pairs) to give to javascript
return jsonify(json_list=x)
def make_atomic_query(key_type, key, comparator, value, comparator_date, value_date):
a = [] # atomic array of query elements
date = []
whole = [] # entire data set with no constraints
transform = ['medications', 'demographics']# data need to have special characters removed for querying
numeric = ['int', 'float', 'double']
char = ['string']
# initialize lists
for i in xrange(0, 2):
a.append('')
whole.append('')
if comparator[i] == 'between':
arg = value[i].split(',', 2)
if comparator_date[i]:
if comparator_date[i] == 'between':
date = value_date[i].split(',', 2)
# create queries
for i in xrange(0, 2):
# assemble base query
if i == 0:
a[i] = db.session.query(Clinical.patient_sid,
Clinical.lft,
Clinical.rgt,
Clinical.attribute_id)
else:
a[i] = db.session.query(Clinical.patient_sid,
Clinical.lft,
Clinical.rgt,
label('attribute_value', Clinical.attribute_id),
Clinical.double_value,
Clinical.string_value)
'''
equivalent to:
select patient_sid, lft, rgt
from clinical_data
'''
# grab the desired bucket
if i == 0:
# grab bucket by attribute
a[i] = a[i].filter(Clinical.attribute_id == int(key[i]))
'''
equivalent to:
select patient_sid, lft, rgt
from clinical_data
where attribute_id = '12345'
'''
# NB: these are special characters for building the parse tree -> clean them
if key_type[i] in transform:
name = value[i].replace('_', ' ').\
replace('{', '('). \
replace('}', ')')
else: name = value[i]
# grab specific bucket
a[i] = a[i].filter(Clinical.string_value.op(comparator[i])(name)).subquery()
'''
equivalent to:
select patient_sid, lft, rgt
from clinical_data
where string_value = '13457-7' and attribute_id = '12345'
'''
# pull item from bucket by attribute name with criterion value
elif i == 1:
# grab attribute of interest by name
'''
a[i] = a[i].join(a[i-1],
and_(Clinical.patient_sid == a[i-1].c.patient_sid,
Clinical.lft >= a[i-1].c.lft,
Clinical.rgt <= a[i-1].c.rgt)).\
filter(Clinical.attribute_id == key[i])
'''
a[i] = a[i].join(a[i-1],
and_(Clinical.patient_sid == a[i-1].c.patient_sid,
Clinical.attribute_id == int(key[i]))). \
filter(Clinical.lft >= a[i-1].c.lft,
Clinical.rgt <= a[i-1].c.rgt)
# unconstrained data set for printing all records
whole[i] = a[i]
'''
equivalent to:
select patient_sid, lft, rgt
from
clinical_data cd inner join
(select patient_sid, lft, rgt
from clinical_data
where string_value = '13457-7' and attribute_id = '12345') ldl
on
cd.patient_sid = ldl.patient_sid
and
cd.lft >= ldl.lft
and
cd.rgt <= ldl.rgt
where attribute_id = '34567';
'''
# flag to control output of all data for desired bucket
print_all = False
# for all data for bucket, no filtering is necessary
if 'OUT' in comparator[i]:
print_all = True
if not 'OUT' in comparator[i]:
qstring = "/attribute{data_type.name}?id='" + key[i] + "'"
data_type = hsql.get_data(qstring)
# first: convert to correct data type for utilization of proper covering index
# NB: default is string
if data_type in numeric:
if comparator[i] != 'between':
a[i] = a[i].filter(Clinical.double_value.op(comparator[i])((float(value[i]))))
else:
a[i] = a[i].filter(between(Clinical.double_value,
float(arg[0]),
float(arg[1])))
elif data_type in char:
# clean up incoming string values representative of specific criterion value
if key_type[i] in transform:
name = value[i].replace('_', ' ').\
replace('{', '('). \
replace('}', ')')
else: name = value[i]
a[i] = a[i].filter(Clinical.string_value.op(comparator[i])(name))
'''
equivalent to:
select patient_sid, lft, rgt
from
clinical_data cd inner join
(select attribute_id, patient_sid, lft, rgt
from clinical_data
where string_value = '13457-7' and attribute_id = '12345') ldl
on
cd.patient_sid = ldl.patient_sid
and
cd.lft >= ldl.lft
and
cd.rgt <= ldl.rgt
where double_value >= 160 and attribute_id = '34567';
'''
# query by date
if comparator_date[i]:
if comparator_date[i] == 'between':
a[i] = a[i].filter(between(Clinical.event_date,
date[0],
date[1]))
else:
a[i] = a[i].filter(Clinical.event_date.op(comparator_date[i])([value_date[i]]))
'''
equivalent to:
select patient_sid, lft, rgt
from
clinical_data cd inner join
(select attribute_id, patient_sid, lft, rgt
from clinical_data
where string_value = '13457-7' and attribute_id = '12345') ldl
on
cd.patient_sid = ldl.patient_sid
and
cd.lft >= ldl.lft
and
cd.rgt <= ldl.rgt
where double_value >= 160 and attribute_id = '34567'
and cd.event_date >= '1/1/1970';
'''
# construct final subquery
a[i] = a[i].subquery()
else:
print 'ERROR'
return a[1], whole[1], print_all
def login_process():
"""Process login."""
# Get login.html form variables
email = request.form.get("email")
# print email
passwd = request.form.get("password")
# Query the database to find if the user sign in email exists
user_count = User.query.filter_by(email_address=email).count()
# Sign in and if that email address doesn't exist in the database
if user_count == 0:
flash("No such user")
return render_template("signup.html")
else:
# If the email address existed in the database,query to filter the User model class for all the records for that email address and assign to an object called user
user = User.query.filter_by(email_address=email).one()
# print user
# print user.user_id
# Verify if the password stored in the database matches the one provided in from the login form, if yes then add the email_address and password to the session
if user.password == passwd:
session["user_id"] = user.user_id
session["email"] = email
session["first_name"] = user.first_name
session["last_name"] = user.last_name
#flash("Logged in")
# print "Logged in"
# print session["user_id"]
# print user.user_type
# Checking if the user is Admin or a User
if user.user_type == 'Admin':
sign_up = db.session.query(
Event.event_id, Event.event_name, Event.event_description,
Event.event_date, Event.event_status, Event.no_of_spots,
Event.no_of_reg_spots, Event.no_of_waitlist_spots,
label(
'filluppercent',
((Event.no_of_reg_spots + Event.no_of_waitlist_spots) *
100) / Event.no_of_spots)).order_by(
asc(Event.event_date)).filter(
Event.event_date >= date.today()).all()
user_registration = Registration.query.filter_by(
showup="Yes").subquery()
past_sign_up = db.session.query(
Event.event_id, Event.event_name, Event.event_description,
Event.event_date, Event.event_status, Event.no_of_spots,
Event.no_of_reg_spots, Event.no_of_waitlist_spots,
func.sum(user_registration.c.slot_id).label("Attended")
).order_by(asc(Event.event_date)).outerjoin(
user_registration,
Event.event_id == user_registration.c.event_id).filter(
Event.event_date < date.today()).all()
return render_template("admin.html",
user=user,
sign_up=sign_up,
past_sign_up=past_sign_up)
else:
# Get the user_id(parent_id with the user object)
parent = user.user_id
# why are we querying the mandated and how
mandated = db.session.query(
Parent_Child.parent_id,
label('children',
func.count(Parent_Child.student_id))).group_by(
Parent_Child.parent_id).filter_by(
parent_id=parent).first()
print mandated
#querying for children
children = db.session.query(
Student.student_id, Student.first_name, Student.last_name,
Student.grade, Student.year_joined,
Student.status).join(Parent_Child).filter(
Parent_Child.parent_id == parent).all()
#if there is None for the mandated from the database
if mandated is not None:
total_hours = mandated.children * 10
else:
total_hours = 0
#print total_hours
#querying for completed hours
completed = db.session.query(
Registration.parent_id,
label('slots', func.count(Registration.slot_id))).group_by(
Registration.parent_id).filter_by(
parent_id=parent, showup='Yes').first()
# print completed
#if there is None from the database for completed hours
if completed is not None:
completed_hours = completed.slots * 2
else:
completed_hours = 0
# print completed_hours
# Calculating the percentage of completion of volunteering
if completed_hours != 0 and total_hours != 0:
# print ("I am in if for percentcomplete")
percentcomplete = float(completed_hours) / float(
total_hours) * 100
elif completed_hours == 0 and total_hours != 0:
# print ("I am in elseif for percentcomplete")
percentcomplete = 0
else:
percentcomplete = 0
# print "Below "
# print int(percentcomplete)
# Calculating the remaining hours of volunteering
remaining_hours = total_hours - completed_hours
# what is this doing?
user_registration = Registration.query.filter_by(
parent_id=parent).subquery()
print user_registration
sign_up = db.session.query(
Event.event_id, Event.event_name, Event.event_description,
Event.event_date, Event.event_status,
label('no_of_remaining_spots',
Event.no_of_spots - Event.no_of_reg_spots),
user_registration.c.parent_id,
user_registration.c.status).order_by(asc(
Event.event_date)).outerjoin(
user_registration, Event.event_id ==
user_registration.c.event_id).filter(
Event.event_date >= date.today()).all()
print sign_up
past_sign_up = db.session.query(
Event.event_id, Event.event_name, Event.event_description,
Event.event_date, Event.event_status, Event.no_of_spots,
Event.no_of_reg_spots, Event.no_of_waitlist_spots,
Registration.showup).join(Registration).order_by(
asc(Event.event_date)).outerjoin(
user_registration, Event.event_id ==
user_registration.c.event_id).filter(
Event.event_date < date.today()).all()
# print "before render_template"
# print children
# print total_hours
# print completed_hours
# print remaining_hours
# print percentcomplete
# print past_sign_up
# print sign_up
# print user
return render_template("welcome.html",
first_name=session["first_name"],
last_name=session["last_name"],
user=user,
children=children,
mandated=total_hours,
completed=completed_hours,
remaining_hours=remaining_hours,
percentcomplete=int(percentcomplete),
sign_up=sign_up,
past_sign_up=past_sign_up)
#if the password stored in the database doesn't matches the one provided in from the login form for that particular email address. Flash wrong message.
else:
flash("The password is incorrect. Please try again")
return render_template("login.html")
