def __init__(self, course_hedu_id, bra_id):
        self._hedu = None
        self._hedu_sorted_by_enrolled = None
        self._hedu_sorted_by_entrants = None
        self._hedu_sorted_by_graduates = None
        self._hedu_major_rank = None

        self.course_hedu_id = course_hedu_id
        self.bra_id = bra_id

        if course_hedu_id is None and bra_id is None:
           self.max_year_query = db.session.query(func.max(Yc_hedu.year))
           self.hedu_query = Ybc_hedu.query.filter(Ybc_hedu.year == self.max_year_query)
        else:
            self.max_year_query = db.session.query(
                func.max(Yc_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.bra_id == self.bra_id,
                    Ybc_hedu.year == self.max_year_query)
            else:
                self.hedu_query = Yc_hedu.query.filter(
                    Yc_hedu.course_hedu_id == self.course_hedu_id,
                    Yc_hedu.year == self.max_year_query)

def main():
    # First create the SQL db that we will dump to
    engine, table = init_db()
    connection = engine.connect()

    # Load up all this stuff - crappy code but it works (clean up if time but this whole script is a shoddy hack)
    clear_mappers()
    session = loadTables()
    session2 = loadOutput()

    # create a connection to the mongo DB
    client = MongoClient()
    db = client.dealtrader
    collection = db.raw_tweets

    while True:
        # get number of deals in the table
        cnttot = session.query(func.max(Deals.deal_id))
        num_deals = cnttot[0][0]
        #print num_deals

        cntdone = session2.query(func.max(Output.deal_id))
        min_deal = cntdone[0][0] or 0
        #print min_deal

        res = session.query(Deals).all()

        for i in range(min_deal, num_deals):
            tweetid = int(res[i].tweet_id)
            q =  session.query(Matches)
            mchres = q.filter(Matches.tweet_id == tweetid).all()
            tweet = collection.find_one( { 'id': tweetid } )
            try:
                deal_id = res[i].deal_id
                origtext = tweet['text']
                tweetts = str(tweet['created_at'])
                itemdescr = res[i].description
                itemprice = res[i].price
                itemurl = res[i].url
                lowest_price = min(list(map(lambda x : x.merchant_price, mchres)))
                best_listings = list(filter(lambda x : x.merchant_price==lowest_price, mchres))
                best_listing = best_listings[0]
                bestprice = str(best_listing.merchant_price)
                bestlink = str(best_listing.url)

                ins = insert(table).values(
                                deal_id = deal_id,
                                tweet_id = tweetid,
                                orig_text = origtext,
                                tweet_ts = tweetts,
                                description = itemdescr,
                                price = itemprice,
                                url = itemurl,
                                best_price = bestprice,
                                best_url = bestlink
                                )
                result = connection.execute(ins)
            except:
                pass

def current_predictions(session, station_id):
    predictions = session.query(
        db.PredictionRecord.trip_id, db.PredictionRecord.seconds_away_from_stop,
        func.max(db.PredictionRecord.stamp)).filter(db.PredictionRecord.station_id == station_id).group_by(
        db.PredictionRecord.trip_id, db.PredictionRecord.seconds_away_from_stop).order_by(
        func.max(db.PredictionRecord.stamp).desc()).all()

    return predictions

def stock_checkpullthru(stockid, maxtime):
    """Did this stock item require pulling through?"""
    return s.execute(
        select([func.now() - func.max(StockOut.time) > maxtime]).\
            where(StockOut.stockid == stockid).\
            where(StockOut.removecode_id.in_(['sold', 'pullthru']))
        ).scalar()

def index(university_id):

    university = UniversityModel.query.filter_by(id=university_id).first_or_404()

    university_service = University(university.id)
    majors_service = UniversityMajors(university.id)

    header = {
        'year': university_service.year(),
        'type': university_service.university_type(),
        'enrolled': university_service.enrolled(),
        'entrants': university_service.entrants(),
        'graduates': university_service.graduates()
    }

    body = {
        'major_with_more_enrollments': majors_service.major_with_more_enrollments(),
        'highest_enrollment_number_by_major': majors_service.highest_enrolled_number(),
        'major_with_more_entrants': majors_service.major_with_more_entrants(),
        'highest_entrant_number_by_major': majors_service.highest_entrants_number(),
        'major_with_more_graduates': majors_service.major_with_more_graduates(),
        'highest_graduate_number_by_major': majors_service.highest_graduates_number(),
        'year': majors_service.year(),
    }

    hedu_max_year = db.session.query(func.max(Yu.year)).first()[0]

    if header['enrolled'] is None or hedu_max_year != body['year']:
        abort(404)
    else:
        return render_template('university/index.html', university=university, header=header, body=body)

def showPost(tag_id, post_id):

    recruitcycle = db.session.query(func.max(models.Member.cycle).label("cycle")).first().cycle
    manager = models.Member.query.filter(or_(models.Member.cycle==recruitcycle, models.Member.cycle==recruitcycle-1)).filter(or_(models.Member.stem_dept_id==5,models.Member.stem_dept_id==6)).all()

    try:
        tag = models.Tag.query.get(tag_id)
        post = models.Post.query.get(post_id)

        if not post in tag.posts:
            abort(404)

        if not (tag and post):
            abort(404)

        post.hitCount = post.hitCount + 1
        db.session.commit()

        return render_template(
            'post_view.html', member=current_user.member, nav_id=6,
            tag=tag, post=post,
            notifications=notification.Generate(current_user.member),
            boards=models.Tag.query.filter_by(special=1).all(), manager=manager)
    except TemplateNotFound:
        abort(404)

 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_current_structure_states(self):
        """Get current structure states of the scenario.

        :returns: sqlalchemy Query object.
        """
        subquery = Query([
            ScenarioStructureState.scenario_id,
            ScenarioStructureState.tree_path,
            func.max(ScenarioStructureState.changed).label('newest_change_date')
            ]).filter_by(
                scenario_id=self.__id
            ).group_by(
                ScenarioStructureState.scenario_id,
                ScenarioStructureState.tree_path
            ).subquery()

        return Query([
            ScenarioStructureState
            ]).join(
                subquery,
                and_(
                    ScenarioStructureState.scenario_id == subquery.columns.scenario_id,
                    ScenarioStructureState.tree_path == subquery.columns.tree_path,
                    ScenarioStructureState.changed == subquery.columns.newest_change_date
                )
            ).filter(
                ScenarioStructureState.enabled == True  # pylint: disable=singleton-comparison
            )

 def get(self, min_rqst, max_rqst):
     session = self.loadSession()
     cntres = session.query(func.max(OutputTable.output_id))
     num_deals = cntres[0][0]
     min_rqst = max(min_rqst,0)
     max_rqst = max(min_rqst, max_rqst)
     min_slct = num_deals-max_rqst
     max_slct = num_deals-min_rqst
     res = session.query(OutputTable).all()
     deal_list = []
     for i in range(max_slct*(-1), min_slct*(-1)):
         j=i*(-1)-1
         try:
             deal_list.append({
             'tweet_ts': res[j].tweet_ts,
             'tweet_text': res[j].orig_text,
             'desc': res[j].description,
             'price': res[j].price,
             'url': res[j].url,
             'best_price': res[j].best_price,
             'best_link': res[j].best_url
             })
         except:
             pass
         
     return [num_deals, deal_list]

def has_exceeded_traffic(user):
    """
    The function calculates the balance of the users traffic.
    :param user: The user object which has to be checked.
    :return: True if the user has more traffic than allowed and false if he
    did not exceed the limit.
    """
    result = session.session.query(
        User.id,
        (func.max(TrafficGroup.traffic_limit) * 1.10) < func.sum(TrafficVolume.size).label("has_exceeded_traffic")
    ).join(
        User.active_traffic_groups
    ).join(
        User.user_hosts
    ).join(
        Host.ips
    ).join(
        Ip.traffic_volumes
    ).filter(
        User.id == user.id
    ).group_by(
        User.id
    ).first()

    if result is not None:
        return result.has_exceeded_traffic
    else:
        return False

    def add_transformed(self, csvfile, name=None, param=None):
        """
            Import deformed point coordinates from ANTs to image
            Assumes LPS->RAS conversion. 
        """
        self.init_db()

        new_trans = Transform(name=name, params=param)
        self.session.add(new_trans)
        self.session.commit()

        trans_id = new_trans.id

        id_delta = self.session.query(func.max(Point.id)).first()[0]

        if id_delta is None:
            id_delta = 0
        else:
            id_delta += 1

        print 'insert new points'
        point_queue = []
        mapping_queue = []
        with open(csvfile, 'rb') as fp:
            preader = csv.DictReader(fp)
            for i,r in enumerate(preader):
                pid = i+id_delta
                point_queue.append({'id':pid, 'x':float(r['x'])*-1, 'y':float(r['y'])*-1, 'z':float(r['z'])})
                mapping_queue.append({'orig_id':r['label'], 'result_id':pid, 'transform_id':trans_id})

        self.engine.execute(Point.__table__.insert(), point_queue)
        self.engine.execute(PointMapping.__table__.insert(), mapping_queue)

    def _get_latest(self, session):
        """Get the time for the latest entry in this Sink."""
        latest = session.query(func.max(self.table.columns.time)).scalar()

        _LOG.debug("Latest entry in %s %s", self.table, latest)

        return latest

def get_session_id():
    query = db.session.query(func.max(MeasurementData.session).label("max_id"))
    res = query.one()
    if res.max_id is None:
        return 1
    else:
        return res.max_id + 1

 def save(self):
     dbconfig = DBConfig()
     db = dbconfig.get_db()
     
     table = db.get_hosts()
     session = db.get_session()
     
     
     qry = session.query(func.max(table.c.id).label("max_id"))
     res = qry.one()
     oid = res.max_id
     
     print "oid: ", oid
     
     if oid > -1:
         oid = oid + 1
     else:
         oid = 1
     
     i = table.insert()
     i.execute(id=oid,
               host_template_id=self.host.host_template_id,
               node_uri=self.host.node_uri,
               node_cluster_uri=self.host.node_cluster_uri)
     
     return oid

def _update_categories(db_session, message, synced_categories):
    now = datetime.utcnow()

    # We make the simplifying assumption that only the latest syncback action
    # matters, since it reflects the current local state.
    actionlog_id = (
        db_session.query(func.max(ActionLog.id))
        .filter(
            ActionLog.namespace_id == message.namespace_id,
            ActionLog.table_name == "message",
            ActionLog.record_id == message.id,
            ActionLog.action.in_(["change_labels", "move"]),
        )
        .scalar()
    )
    actionlog = db_session.query(ActionLog).get(actionlog_id)

    # We completed the syncback action /long enough ago/ (on average and
    # with an error margin) that:
    # - if it completed successfully, sync has picked it up; so, safe to
    # overwrite message.categories
    # - if syncback failed, the local changes made can be overwritten
    # without confusing the API user.
    # TODO[k]/(emfree): Implement proper rollback of local state in this case.
    # This is needed in order to pick up future changes to the message,
    # the local_changes counter is reset as well.
    if actionlog.status in ("successful", "failed") and (now - actionlog.updated_at).seconds >= 90:
        message.categories = synced_categories
        message.categories_changes = False

 def recent(session, limit=5):
     sq = session.query(Rating.user_id, func.max(Rating.rated).label('max_rated'))\
                 .group_by(Rating.user_id).subquery()
     res = session.query(User)\
                  .join((sq, sq.c.user_id==User.id))\
                  .order_by(sq.c.max_rated.desc()).limit(limit).all()
     return res

    def null_out_last_stop_departures(cls, db):
        ''' delete all 'depature_time' values that appear for the last stop
            time of a given trip (e.g., the trip ends there, so there isn't a
            further vehicle departure / customer pickup for that stop time / trip pair)...

            -- query below shows null'd out stop times
            select * from ott.stop_times
            where COALESCE(arrival_time,'')='' or COALESCE(departure_time,'')=''

            NOTE: we know this breaks the current GTFS spec, which states that departure &
                  arrival times must both exist for every stop time.  Sadly, GTFS is kinda wrong...
        '''
        # step 1: remove the departure times at the end of a trip
        log.info("QUERY StopTime for all trip end times")
        sq = db.session.query(StopTime.trip_id, func.max(StopTime.stop_sequence).label('end_sequence'))
        sq = sq.group_by(StopTime.trip_id).subquery()
        q = db.session.query(StopTime)
        q = q.filter_by(trip_id=sq.c.trip_id, stop_sequence=sq.c.end_sequence)
        for st in q:
            if st.pickup_type == 1:
                st.departure_time = None

        # remove the arrival times at the start of a trip
        log.info("QUERY StopTime for all trip start times")
        sq = db.session.query(StopTime.trip_id, func.min(StopTime.stop_sequence).label('start_sequence'))
        sq = sq.group_by(StopTime.trip_id).subquery()
        q = db.session.query(StopTime)
        q = q.filter_by(trip_id=sq.c.trip_id, stop_sequence=sq.c.start_sequence)
        for st in q:
            if st.drop_off_type == 1:
                st.arrival_time = None

        db.session.flush()
        db.session.commit()
        db.session.close()

 def save(self):
     dbconfig = DBConfig()
     db = dbconfig.get_db()
     
     table = db.get_containers()
     session = db.get_session()
     
     
     qry = session.query(func.max(table.c.id).label("max_id"))
     res = qry.one()
     oid = res.max_id
     
     print "oid: ", oid
     
     if oid > -1:
         oid = oid + 1
     else:
         oid = 1
     
     i = table.insert()
     i.execute(id=oid,
               host_id=self.container.host_id,
               cpu=self.container.cpu,
               memory=self.container.memory,
               disk_size=self.container.disk_size,
               request_id=self.container.request_id,
               service_uri=self.container.service_uri,
               container_uri=self.container.container_uri)
     
     return oid

 def get_number_comments(self, status=None):
     if not status:
         return Session.query(sa.func.count(Comment.id)).filter_by(change_id=self.id).first()[0]
     
     
     date = Session.query(func.max(CommentStatus.created_date).label('date'), Comment.id)
     date = date.filter(CommentStatus.comment_id==Comment.id).filter(Comment.change_id==self.id)
     date = date.group_by(CommentStatus.comment_id, Comment.id)
     subq = date.subquery()
     
     q = Session.query(func.count(Comment.id)).outerjoin((subq, subq.c.id==Comment.id))
     q = q.outerjoin((CommentStatus, CommentStatus.comment_id==Comment.id))
     q = q.filter(Comment.change_id==self.id).filter(Comment.status!=STATUS_REMOVED)
     q = q.filter(Comment.in_reply_to_id==None)
     
     if status == STATUS_OPEN:
         q = q.filter(sa.or_(
             CommentStatus.id==None,
             sa.and_(CommentStatus.created_date==subq.columns.date, CommentStatus.status==status)
         ))
         return q.scalar()
     else:
         q = q.filter(
             sa.and_(CommentStatus.created_date==subq.columns.date, CommentStatus.status==status)
         )
         return q.scalar()

def showTask(id):

    recruitcycle = db.session.query(func.max(models.Member.cycle).label("cycle")).first().cycle
    manager = models.Member.query.filter(or_(models.Member.cycle==recruitcycle, models.Member.cycle==recruitcycle-1)).filter(or_(models.Member.stem_dept_id==5,models.Member.stem_dept_id==6)).all()

    try:
        task = models.Task.query.get(id)
        if not task:
            abort(404)
        if task.level == 0:
            return render_template(
                'milestone.html', member=current_user.member,
                milestone=task, task=task, nav_id=5,
                notifications=notification.Generate(current_user.member),
                boards=models.Tag.query.filter_by(special=1).all(), manager=manager)
        if task.level == 1:
            return render_template(
                'issue.html', member=current_user.member,
                issue=task, task=task, nav_id=6,
                notifications=notification.Generate(current_user.member),
                boards=models.Tag.query.filter_by(special=1).all(), manager=manager)
        if task.level == 2:
            return render_template(
                'subtask.html', member=current_user.member,
                task=task, nav_id=6,
                notifications=notification.Generate(current_user.member),
                boards=models.Tag.query.filter_by(special=1).all(), manager=manager)
    except TemplateNotFound:
        abort(404)