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")