def get_candidate(self, args, page_num, per_page, candidate_id, committee_id):
        if candidate_id is not None:
            candidates = CandidateDetail.query
            candidates = candidates.filter_by(**{'candidate_id': candidate_id})

        if committee_id is not None:
            candidates = CandidateDetail.query.join(CandidateCommitteeLink).filter(CandidateCommitteeLink.committee_id==committee_id)

        for argname in ['candidate_id', 'candidate_status', 'district', 'incumbent_challenge', 'office', 'party', 'state']:
            if args.get(argname):
                # this is not working and doesn't look like it would work for _short
                if ',' in args[argname]:
                    candidates = candidates.filter(getattr(CandidateDetail, argname).in_(args[argname].split(',')))
                else:
                    candidates = candidates.filter_by(**{argname: args[argname]})

        if args.get('year') and args['year'] != '*':
            # before expiration
            candidates = candidates.filter(or_(extract('year', CandidateDetail.expire_date) >= int(args['year']), CandidateDetail.expire_date == None))
            # after origination
            candidates = candidates.filter(extract('year', CandidateDetail.load_date) <= int(args['year']))

        count = candidates.count()

        return count, candidates.order_by(CandidateDetail.expire_date.desc()).paginate(page_num, per_page, False).items

    def get_committee(self, args, page_num, per_page, committee_id, candidate_id):

        if committee_id is not None:
            committees = CommitteeDetail.query
            committees = committees.filter_by(**{'committee_id': committee_id})

        if candidate_id is not None:
            committees = CommitteeDetail.query.join(CandidateCommitteeLink).filter(CandidateCommitteeLink.candidate_id==candidate_id)

        for argname in ['designation', 'organization_type', 'committee_type']:
            if args.get(argname):
                if ',' in args[argname]:
                    committees = committees.filter(getattr(Committee, argname).in_(args[argname].split(',')))
                else:
                    committees = committees.filter(getattr(Committee, argname)==args[argname])

        # default year filtering
        if args.get('year') is None:
            earliest_year = int(sorted(default_year().split(','))[0])
            # still going or expired after the earliest year we are looking for
            committees = committees.filter(or_(extract('year', CommitteeDetail.expire_date) >= earliest_year, CommitteeDetail.expire_date == None))

        # Should this handle a list of years to make it consistent with /candidate ?
        elif args.get('year') and args['year'] != '*':
            # before expiration
            committees = committees.filter(or_(extract('year', CommitteeDetail.expire_date) >= int(args['year']), CommitteeDetail.expire_date == None))
            # after origination
            committees = committees.filter(extract('year', CommitteeDetail.original_registration_date) <= int(args['year']))

        count = committees.count()

        return count, committees.order_by(CommitteeDetail.name).paginate(page_num, per_page, False).items

def agg_week(crime, year):
    week_days = {
        0: 'Sun',
        1: 'Mon',
        2: 'Tue',
        3: 'Wed',
        4: 'Thu',
        5: 'Fri',
        6: 'Sat'
    }

    data = Crimes.query.with_entities(
        extract('dow', Crimes.datetime).label('day'),
        func.count(Crimes.cat)
        ).filter(Crimes.cat == crime
        ).filter(extract('year', Crimes.datetime) == year
        ).group_by('day'
        ).order_by('day'
        ).all()

    return jsonify({
        'crime': crime,
        'aggregates': [ {'day': week_days[day], 'occurrences': occurences}
            for day, occurences in data ]
    })

def send_profile_visitor_email(**kwargs):
    template = email_template_env.get_template('profile_visitors.html')
    senders = AccountUser.query.filter(
        extract('dow', AccountUser.date_added) == extract('dow', func.now()),
        extract('hour', AccountUser.date_added) == extract('hour', func.now()),
    ).join(
        AccountUserVisit,
        (AccountUserVisit.profile_user_id == AccountUser.id) &
        (AccountUserVisit.notified == False)
    )

    for sender in senders:
        visitors = AccountUserVisit.get_all_visits_in_last_7_days(sender, 5)

        visitors = [AccountUserVisit.visit_item(*v) for v in visitors.all()]
        if visitors:
            body = template.render(
                sender=sender,
                visitors=visitors,
                **kwargs
            )
            send_email(sender.username, body)

            try:
                db.session.query(AccountUserVisit).filter_by(profile_user_id=sender.id).update({"notified": True})
                db.session.commit()
            except:
                db.session.rollback()
                raise

def detail(year, month, day, slug):
    year = int(year)
    month = int(month)
    day = int(day)
    with session_context() as session:
        post = session.query(Post).options(
            subqueryload(Post.tags)).filter(Post.slug == slug).filter(
                extract('year', Post.create_at) == year,
                extract('month', Post.create_at) == month,
                extract('day', Post.create_at) == day)
        try:
            if post.count() < 1:
                redirect('/blog')
        except:
            print "error occurred"
            redirect('/blog')
        post = post.first()
        post.view_count += 1
        # get related posts
        # dont forget to filter self
        tag_ids = [_t.id for _t in post.tags]
        related_posts = session.query(Post).filter(
            Post.tags.any(Tag.id.in_(tag_ids))
        ).filter(
            Post.id != post.id
        ).order_by(Post.view_count.desc())

        session.commit()
    return {'post': post, 'title': post.title, 'related_posts': related_posts}

 def getPostByMonth(self, year, month):
     posts = web.ctx.orm.query(Post).\
             filter(and_(Post.content_type=='post', Post.status=='publish')).\
             filter(extract('year', Post.created)==int(year)).\
             filter(extract('month', Post.created)==int(month)).\
             order_by('posts.created DESC')
     return posts

    def get_booked(self, date: datetime, is_after: bool, is_now: bool = False):
        order_func = None
        compare_op = None
        if is_after:
            compare_op = operator.ge
            if not is_now:
                date += timedelta(days=1)
            order_func = stub_func
        else:
            compare_op = operator.lt
            order_func = desc

        date = date.replace(hour=0, minute=0, second=0, microsecond=0)
        start_date = dataentities.BookedRange.start_date
        next_date = session.query(dataentities.BookedRange) \
            .filter(dataentities.BookedRange.start_date > datetime.now()) \
            .filter(compare_op(dataentities.BookedRange.start_date, date)) \
            .order_by(order_func(dataentities.BookedRange.start_date)) \
            .limit(1).first()

        if not next_date:
            return None

        return session.query(dataentities.BookedRange) \
            .filter(extract('day', start_date) == next_date.start_date.day) \
            .filter(extract('month', start_date) == next_date.start_date.month) \
            .filter(extract('year', start_date) == next_date.start_date.year) \
            .order_by(dataentities.BookedRange.start_date).all()

def get_model_changes(
    entity_type, year=None, month=None, day=None, hour=None, since=None
):
    # type: (Text, int, int, int, int, datetime) -> Query
    """Get models modified at the given date with the Audit service.

    :param entity_type: string like "extranet_medicen.apps.crm.models.Compte".
      Beware the typo, there won't be a warning message.
    :param since: datetime
    :param year: int
    :param month: int
    :param day: int
    :param hour: int

    :returns: a query object
    """
    query = AuditEntry.query

    if since:
        query = query.filter(AuditEntry.happened_at >= since)

    if year:
        query = query.filter(extract("year", AuditEntry.happened_at) == year)
    if month:
        query = query.filter(extract("month", AuditEntry.happened_at) == month)
    if day:
        query = query.filter(extract("day", AuditEntry.happened_at) == day)
    if hour:
        query = query.filter(extract("hour", AuditEntry.happened_at) == hour)

    query = query.filter(AuditEntry.entity_type.like(entity_type)).order_by(
        AuditEntry.happened_at
    )

    return query

def write_states_dataset(fobj, source, slug,
                         start_year=None, end_year=None,
                         adjusted=True, delta=False):
    """
    Writes a geographic csv of the series to the open file-like object passed
    in as `fobj`. Each row is an individual state, and each column is the
    period for the series. You may also specify seasonally adjusted with the
    `adjusted` boolean.

    The source can be either "LAUS" or "CESSM". The slug should be the URL-safe
    slug that groups similar datasets by state/category.

    This will write in place to the fobj that is passed to it.
    """

    fields = ["fips", "State"]

    start_year = start_year or int(elmr.app.config['STARTYEAR'])
    end_year   = end_year or int(elmr.app.config['ENDYEAR'])

    ## TODO: Somehow get this from the database, not hardcoded logic.
    for year in xrange(start_year, end_year + 1):
        for month in xrange(1, 13):
            if year == 2015 and month > 3:
                break
            fields.append(date(year, month, 1).strftime("%b %Y"))

    # Create the CSV writer
    writer = csv.DictWriter(fobj, fieldnames=fields)
    writer.writeheader()

    # Create the database query - note, there is no checking
    for state in USAState.query.order_by('name'):
        ss = state.series.filter_by(adjusted=adjusted, source=source, slug=slug)

        ss = ss.first()  # TODO: Check to make sure this returns a single result
        if ss is None:
            ss = state.series.filter_by(source=source, slug=slug).first()

        series = ss.series.delta if delta else ss.series

        if ss is None:
            continue
        # TODO: above was just a temporary fix

        row = {
            "fips": state.fips,
            "State": state.name,
        }

        field   = SeriesRecord.period
        records = series.records

        records = records.filter(extract('year', field) >= start_year)
        records = records.filter(extract('year', field) <= end_year)
        for record in records:
            row[record.period.strftime("%b %Y")] = record.value

        writer.writerow(row)

def archive():
    archive = (db_session.query(extract("year", Post.pub_date).label("year"),
                                extract("month", Post.pub_date).label("month"))
                         .group_by("year", "month")
                         .order_by("-year", "-month"))

    return render_template(env.get_template("archive.html"),
                           archive=archive)

 def get_birthday(cls):
     today = date.today()
     tomorrow = today + timedelta(days=1)
     return cls.query.filter(
         or_(
             and_(extract('month', User.birth_date) == today.month, extract('day', User.birth_date) == today.day),
             and_(extract('month', User.birth_date) == tomorrow.month, extract('day', User.birth_date) == tomorrow.day)
         ), or_(User.status != User.STATUS_DELETED, User.status==None)).order_by(User.birth_date.desc(), User.full_name).all()

def view_post(year, month, day, slug):
    post = Post.query.filter(
        (extract('year', Post.pub_date) == year) &
        (extract('month', Post.pub_date) == month) &
        (extract('day', Post.pub_date) == day) &
        (Post.status == PostStatus.published) &
        (Post.slug == slug)).first_or_404()
    return render_template('blog/post_view.html', post=post)

def birthdays():
    today = datetime.datetime.now()
    session = DBSession()

    bday_members = session.query(Person).filter(
        extract('day', Person.dob) == today.day).filter(
        extract('month', Person.dob) == today.month).all()
    session.close()
    return bday_members

def index():
    filter_query = []
    hoje = date.today()
    session['tela'] = "relatorio"

    form = PesForm()

    categoria = []
    categoria.append(('0','Todos'))
    for h in Categoria.query.filter(Categoria.empresa_id == session['empresa']).all():
        categoria.append((h.id,str(h.id) + " " + h.titulo))

    form.categoria_id.choices = categoria

    conta =  []
    conta.append(('0', 'Todos'))
    for h in Conta.query.filter(Conta.empresa_id == session['empresa']).all():
        conta.append((h.id,h.tipo + '-' + h.conta))

    form.conta_id.choices = conta
   
        
    if request.method == "POST":
        if form.categoria_id.data != "0":
            filter_query.append(Movimentacao.categoria_id == form.categoria_id.data)
        
        if form.conta_id.data != "0":
            filter_query.append(Movimentacao.conta_id == form.conta_id.data)    

        if form.data.data:
            (data_inicio,data_fim) = form.data.data.replace(" ","").split("-")

            data_inicio  = datetime.strptime(data_inicio, '%m/%d/%Y') + timedelta(days=-1)
            data_fim     = datetime.strptime(data_fim, '%m/%d/%Y')

            filter_query.append(Movimentacao.data_v >= data_inicio)
            filter_query.append(Movimentacao.data_v <= data_fim   ) 

        else:
            filter_query.append(extract('month', Movimentacao.data_v) == hoje.month)
            filter_query.append(extract('year' , Movimentacao.data_v) == hoje.year )        


        todos = Movimentacao.query.filter(
                Movimentacao.empresa_id == session['empresa'],
                *filter_query,
                ).order_by('data_v').all() 
    
    else:
        todos = Movimentacao.query.filter(
                Movimentacao.empresa_id == session['empresa'],
                extract('month', Movimentacao.data_v) == hoje.month,
                extract('year', Movimentacao.data_v) == hoje.year).order_by('data_v').all() 

    credito = sum([item.valor  for item in todos if item.categoria.status == 0])
    debito = sum([item.valor  for item in todos if item.categoria.status == 1])
    return render_template('relatorio/index.html',title='Relatório de Contas',form=form, todos=todos,credito=credito,debito=debito)

    def get_busy_on_date(self, day, month, year):
        start_date = dataentities.BookedRange.start_date
        on_day = session.query(dataentities.BookedRange) \
            .filter(extract('day', start_date) == day) \
            .filter(extract('month', start_date) == month) \
            .filter(extract('year', start_date) == year) \
            .all()

        return list(map(lambda x: [x.start_date.hour, x.end_date.hour], on_day))

    def group_join_leave_events(self, group_id):
        aet = self.auditEventTable
#        SELECT EXTRACT(year FROM event_date) AS year,
#          EXTRACT(month FROM event_date) AS month,
#          subsystem, event_date, instance_user_id, user_id
#        FROM audit_event
#        WHERE
#          ((subsystem = 'gs.group.member.join' AND event_code = '1')
#           OR
#           (subsystem = 'gs.group.member.leave' AND event_code = '1'))
#          AND group_id = 'example_group';
        s = sa.select([
            sa.extract('year', aet.c.event_date).label('year'),
            sa.extract('month', aet.c.event_date).label('month'),
            aet.c.subsystem,
            aet.c.event_date,
            aet.c.instance_user_id,
            aet.c.user_id
        ])
        joinClauses = ((aet.c.subsystem == JOIN_SUBSYSTEM)
                       & (aet.c.event_code == JOIN))
        leaveClauses = ((aet.c.subsystem == LEAVE_SUBSYSTEM)
                        & (aet.c.event_code == LEAVE))
        s.append_whereclause(joinClauses | leaveClauses)
        s.append_whereclause(aet.c.group_id == group_id)

        session = getSession()
        r = session.execute(s)
        rows = []
        if r.rowcount:
            rows = [{
                'year': int(row['year']),
                'month': int(row['month']),
                'date': row['event_date'],
                'subsystem': row['subsystem'],
                'user_id': row['instance_user_id'],
                'admin_id': row['user_id']
            } for row in r]
        years = {}
        for row in rows:
            if row['year'] not in years:
                years[row['year']] = {}
        for row in rows:
            if row['month'] not in years[row['year']]:
                years[row['year']][row['month']] = {
                    JOIN_SUBSYSTEM: [],
                    LEAVE_SUBSYSTEM: []
                }
        for row in rows:
            years[row['year']][row['month']][row['subsystem']].append({
                'date': row['date'],
                'user_id': row['user_id'],
                'admin_id': row['admin_id']
            })
        retval = years
        assert type(retval) == dict
        return retval

def csr_table(filter_val):

    _csr_type = sort_tuple(csr_types)
    _status = sort_tuple(csr_status)

    # Joins CSR and VENDORS


    if filter_val is not None:

        results = results = Csr.query.join(Vendor).filter(Csr.status==1)

        _v = filter_val.split("-")
        if _v[0] == 'date':
            results = results.filter(extract('year', Csr.report_date) == int(_v[1]))
            results = results.filter(extract('month', Csr.report_date) == int(_v[2]))
        elif _v[0] == 'vendor':
            results = results.filter(Vendor.short_hand==_v[1])
        elif _v[0] == 'duration':
            date_max = dt.now() - timedelta(int(_v[1]))
            date_min = dt.now() - timedelta(int(_v[2]))
            results = results.filter(Csr.report_date.between(date_min, date_max))

    else:
        results = Csr.query.join(Vendor)
    # Specify Column Data
    results = results.values(Vendor.name,
                       Csr.id,
                       Csr.csr_ref,
                       Csr.contract_no,
                       Csr.description,
                       Csr.csr_type,
                       Csr.status,
                       Csr.incident_date,
                       Csr.report_date,
                       Csr.close_date,
                       Csr.remarks,
                       )

    resultlist = []
    for v_name, csr_id, ref, contract_no, description, csr_type,\
            status, incident, report, close, remarks in results:

        resultlist.append({ 'vendor_name': v_name,
                            'id': csr_id,
                            'csr_reference' : ref,
                            'contract_no': contract_no,
                            'description': description,
                            'csr_type': _csr_type[csr_type-1],
                            'status': _status[status-1],
                            'incident_date': get_date(incident),
                            'report_date': get_date(report),
                            'close_date': get_date(close),
                            'remarks': remarks,
                           })
    return resultlist

def archive(year, month):
    entries = Posts.query.filter(
        (extract('month', Posts.created) == month) &
        (extract('year', Posts.created) == year)
    ).all()
    if not entries:
        abort(404)
    for entry in entries:
        entry.content = markdown(entry.content)
    return render_template('archive.html', entries=entries)

def review_kpi(filename=None):
    """
    Review Details of the KPI per section by month and year
    """

    section_id = current_user.get_id()
    section = User.query.filter(User.id == section_id).first_or_404()
    specs = SpecSectionKpi.query.filter(SpecSectionKpi.filename == filename).first()
    if specs is None:
        return render_template(
            "section_kpi_mgt/coming-soon.html", msg="Specification for {} doesn't exist yet".format(filename)
        )

    spec = specs.doc[section.full_name]
    classes_description = spec["_classes_description"]
    targets = spec["_target"]
    data = {}

    record = SectionRawScore.query.filter(
        and_(
            SectionRawScore.section_id == section_id,
            extract("month", SectionRawScore.score_date) == filename.split("-")[1],
            extract("year", SectionRawScore.score_date) == filename.split("-")[0],
        )
    ).all()

    if record is None:
        return render_template("section_kpi_mgt/coming-soon.html")

    df = query_to_df(record)
    df = df.set_index("score_class")

    temp_list = []
    for i in ["a11", "a12", "a13", "a2", "a3"]:
        temp_list.append(
            {"class": classes_description[i], "target": targets[i], "actual": float(df.loc[i, "raw_score"])}
        )
    data["capex"] = temp_list

    temp_list = []
    for i in ["b1", "b2"]:
        temp_list.append(
            {"class": classes_description[i], "target": targets[i], "actual": float(df.loc[i, "raw_score"])}
        )
    data["opex"] = temp_list

    temp_list = []
    for i in ["c1", "c2"]:
        temp_list.append(
            {"class": classes_description[i], "target": targets[i], "actual": float(df.loc[i, "raw_score"])}
        )
    data["initiatives"] = temp_list

    _template = "section_kpi_mgt/detailed-dashboard.html"
    return render_template(_template, data=data)

def update_kpi_dashboard(section_id=None, date=None):
    mn = date.month
    yr = date.year
    if section_id is None:
        return (False, 'Section ID must not be None')

    record = SectionWeightedScore.query.\
        filter(and_(SectionWeightedScore.section_id==section_id,
                    extract('month', SectionWeightedScore.weighted_score_date) == mn,
                    extract('year', SectionWeightedScore.weighted_score_date) == yr
                    )).\
        first()

    if record is not None:
        return (False, "Can't Update, Updated Record Already Exist")

    else:
        section = 'BP&TE'
        scores = SectionRawScore.query.\
        filter(and_(SectionRawScore.section_id==section_id,
                    extract('month', SectionRawScore.score_date) == mn,
                    extract('year', SectionRawScore.score_date) == yr
                    )).\
        all()
        df_score = query_to_df(scores)
        df_score = df_score[['score_class', 'raw_score']]
        score_dict = df_score.set_index('score_class').to_dict()

        score_from_raw = cal_raw_scores(raw_scores=score_dict['raw_score'],
                                        targets=targets[section],
                                        classes=classes
                                        )
        score_eqvs = get_score_equivalent(score_from_raw, criteria)

        weighted_each_class = get_weighted_each_class(score_eqvs,
                                                      weightage_class[section],
                                                      classes)
        weighted_each_category = get_weighted_each_category(weighted_each_class,
                                                            weightage_category[section],
                                                            categories)

        for k, v in weighted_each_category.items():

            record = SectionWeightedScore(
                section_id=section_id,
                weighted_score_date=scores[0].score_date,
                weighted_score=v,
                category=k
            )
            db.session.add(record)
        db.session.commit()

        return (True, 'Success')