示例#1
0
    def party_counts(self):
        # if the bill cosponsorships have party_ids, then use it.
        party_counts = db_session.query(func.count(distinct(Cosponsorship.person_id)))\
                                 .join(Cosponsorship.bill)\
                                 .filter(Bill.id == self.id)\
                                 .outerjoin(Cosponsorship.party)\
                                 .add_columns(Party.name, Party.color)\
                                 .group_by(Party.id)

        # Otherwise, use the most recent party affiliation of candidacy info.
        if any(party is None for _, party, _ in party_counts):
            party_counts = db_session.query(Party.name,
                                            func.count(distinct(Person.id)),
                                            Party.color)\
                                     .join(Candidacy)\
                                     .join(Election)\
                                     .filter(Election.assembly_id == self.assembly_id)\
                                     .join(Person)\
                                     .join(Cosponsorship)\
                                     .join(Bill)\
                                     .filter(Bill.id == self.id)\
                                     .group_by(Party.id)
        else:
            party_counts = ((party, count, color)
                            for count, party, color in party_counts)

        return list(party_counts)
示例#2
0
文件: bill.py 项目: mkim0710/pokr.kr
    def party_counts(self):
        # if the bill cosponsorships have party_ids, then use it.
        party_counts = db_session.query(func.count(distinct(Cosponsorship.person_id)))\
                                 .join(Cosponsorship.bill)\
                                 .filter(Bill.id == self.id)\
                                 .outerjoin(Cosponsorship.party)\
                                 .add_columns(Party.name, Party.color)\
                                 .group_by(Party.id)

        # Otherwise, use the most recent party affiliation of candidacy info.
        if any(party is None for _, party, _ in party_counts):
            party_counts = db_session.query(Party.name,
                                            func.count(distinct(Person.id)),
                                            Party.color)\
                                     .join(Candidacy)\
                                     .join(Election)\
                                     .filter(Election.assembly_id == self.assembly_id)\
                                     .join(Person)\
                                     .join(Cosponsorship)\
                                     .join(Bill)\
                                     .filter(Bill.id == self.id)\
                                     .group_by(Party.id)
        else:
            party_counts = ((party, count, color)
                            for count, party, color in party_counts)

        return list(party_counts)
示例#3
0
文件: queries.py 项目: pypingou/faf
def components_list(db, opsysrelease_ids, associate_ids=None):
    '''
    Returns a list of tuples consisting from component's id
    and component's name
    '''
    sub = db.session.query(distinct(Report.component_id)).subquery()
    components_query = (db.session.query(
        OpSysComponent.id, OpSysComponent.name).filter(
            OpSysComponent.id.in_(sub)).order_by(OpSysComponent.name))

    if opsysrelease_ids:
        fsub = (db.session.query(distinct(
            OpSysReleaseComponent.components_id)).filter(
                OpSysReleaseComponent.opsysreleases_id.in_(opsysrelease_ids)))

        components_query = (components_query.filter(
            OpSysComponent.id.in_(fsub)))

    if associate_ids:
        fsub = (db.session.query(distinct(
            OpSysReleaseComponent.components_id)).filter(
                OpSysReleaseComponent.id.in_(
                    db.session.query(
                        OpSysReleaseComponentAssociate.opsysreleasecompoents_id
                    ).filter(
                        OpSysReleaseComponentAssociate.associatepeople_id.in_(
                            associate_ids)))))

        components_query = (components_query.filter(
            OpSysComponent.id.in_(fsub)))

    return components_query.all()
示例#4
0
文件: purge.py 项目: jbouwh/core
def _purge_filtered_data(instance: Recorder, session: Session) -> bool:
    """Remove filtered states and events that shouldn't be in the database."""
    _LOGGER.debug("Cleanup filtered data")
    using_sqlite = instance.dialect_name == SupportedDialect.SQLITE

    # Check if excluded entity_ids are in database
    excluded_entity_ids: list[str] = [
        entity_id
        for (entity_id, ) in session.query(distinct(States.entity_id)).all()
        if not instance.entity_filter(entity_id)
    ]
    if len(excluded_entity_ids) > 0:
        _purge_filtered_states(instance, session, excluded_entity_ids,
                               using_sqlite)
        return False

    # Check if excluded event_types are in database
    excluded_event_types: list[str] = [
        event_type
        for (event_type, ) in session.query(distinct(Events.event_type)).all()
        if event_type in instance.exclude_t
    ]
    if len(excluded_event_types) > 0:
        _purge_filtered_events(instance, session, excluded_event_types)
        return False

    return True
示例#5
0
文件: queries.py 项目: rtnpro/faf
def components_list(db, opsysrelease_ids, associate_ids=None):
    '''
    Returns a list of tuples consisting from component's id
    and component's name
    '''
    sub = db.session.query(distinct(Report.component_id)).subquery()
    components_query = (db.session.query(OpSysComponent.id,
                        OpSysComponent.name)
                    .filter(OpSysComponent.id.in_(sub))
                    .order_by(OpSysComponent.name))

    if opsysrelease_ids:
        fsub = (db.session.query(
                distinct(OpSysReleaseComponent.components_id))
                .filter(OpSysReleaseComponent.opsysreleases_id.in_(
                    opsysrelease_ids)))

        components_query = (components_query
            .filter(OpSysComponent.id.in_(fsub)))

    if associate_ids:
        fsub = (db.session.query(
                distinct(OpSysReleaseComponent.components_id))
                .filter(OpSysReleaseComponent.id.in_(
                    db.session.query(OpSysReleaseComponentAssociate.opsysreleasecompoents_id)
                    .filter(OpSysReleaseComponentAssociate.associatepeople_id.in_(associate_ids)))))

        components_query = (components_query
            .filter(OpSysComponent.id.in_(fsub)))

    return components_query.all()
示例#6
0
def get_tips(session, tid):
    tips = []

    comments_by_itip = {}
    messages_by_itip = {}
    files_by_itip = {}

    # Fetch comments count
    for itip_id, count in session.query(models.InternalTip.id,
                                        func.count(distinct(models.Comment.id))) \
                                 .filter(models.Comment.internaltip_id == models.InternalTip.id,
                                         models.InternalTip.tid == tid) \
                                 .group_by(models.InternalTip.id):
        comments_by_itip[itip_id] = count

    # Fetch messages count
    for itip_id, count in session.query(models.InternalTip.id,
                                        func.count(distinct(models.Message.id))) \
                                 .filter(models.Message.receivertip_id == models.ReceiverTip.id,
                                         models.ReceiverTip.internaltip_id == models.InternalTip.id,
                                         models.InternalTip.tid == tid) \
                                 .group_by(models.InternalTip.id):
        messages_by_itip[itip_id] = count

    # Fetch files count
    for itip_id, count in session.query(models.InternalTip.id,
                                        func.count(distinct(models.InternalFile.id))) \
                                 .filter(models.InternalFile.internaltip_id == models.InternalTip.id,
                                         models.InternalTip.tid == tid) \
                                 .group_by(models.InternalTip.id):
        files_by_itip[itip_id] = count

    for itip in session.query(
            models.InternalTip).filter(models.InternalTip.tid == tid):
        tips.append({
            'id': itip.id,
            'creation_date': itip.creation_date,
            'last_update': itip.update_date,
            'expiration_date': itip.expiration_date,
            'context_id': itip.context_id,
            'status': itip.status,
            'substatus': itip.substatus,
            'tor': not itip.https,
            'comments': comments_by_itip.get(itip.id, 0),
            'messages': messages_by_itip.get(itip.id, 0),
            'files': files_by_itip.get(itip.id, 0),
            'wb_last_access': itip.wb_last_access
        })

    return tips
示例#7
0
    def getrequests(cls):
        _session = db.session
        _archivedrequestids = _session.query(distinct(
            FOIRawRequest.requestid)).filter(
                FOIRawRequest.status.in_(['Archived'])).all()
        _requestids = _session.query(distinct(FOIRawRequest.requestid)).filter(
            FOIRawRequest.requestid.notin_(_archivedrequestids)).all()
        requests = []
        for _requestid in _requestids:
            request = _session.query(FOIRawRequest).filter(
                FOIRawRequest.requestid == _requestid).order_by(
                    FOIRawRequest.version.desc()).first()
            requests.append(request)

        return requests
示例#8
0
    def get_recipient_users(self):
        groups = []

        if self.daily_schedule_subscribers.data:
            log.info("Email recipients includes daily schedule subscribers")
            groups.append(User.query
                    .options(
                        lazyload(User.organisation),
                        lazyload(User.committee_alerts),
                    )\
                    .filter(User.subscribe_daily_schedule == True)
                    .filter(User.confirmed_at != None)
                    .all())

        if self.committee_ids.data:
            log.info("Email recipients includes subscribers for these committees: %s" % self.committee_ids.data)
            user_ids = db.session\
                    .query(distinct(user_committee_alerts.c.user_id))\
                    .filter(user_committee_alerts.c.committee_id.in_(self.committee_ids.data))\
                    .all()
            user_ids = [u[0] for u in user_ids]

            groups.append(User.query
                    .options(
                        lazyload(User.organisation),
                        lazyload(User.committee_alerts),
                    )\
                    .filter(User.id.in_(user_ids))
                    .filter(User.confirmed_at != None)
                    .all())

        return set(u for u in chain(*groups))
def get_allowed_hosts(session):
    result = (session
              .query(distinct(AccessWebService.host))
              .filter(and_(AccessWebService.host != None,
                           AccessWebService.disable == 0)).all())
    result = [item[0].encode('utf-8', 'ignore') for item in result]
    return result
示例#10
0
def get_synthese_stat():
    params = request.args
    q = DB.session.query(
        label("year", func.date_part("year", VSynthese.date_min)),
        func.count(VSynthese.id_synthese),
        func.count(distinct(VSynthese.cd_ref)),
    ).group_by("year")
    if ("selectedRegne" in params) and (params["selectedRegne"] != ""):
        q = q.filter(VSynthese.regne == params["selectedRegne"])
    if ("selectedPhylum" in params) and (params["selectedPhylum"] != ""):
        q = q.filter(VSynthese.phylum == params["selectedPhylum"])
    if "selectedClasse" in params and (params["selectedClasse"] != ""):
        q = q.filter(VSynthese.classe == params["selectedClasse"])
    if "selectedOrdre" in params and (params["selectedOrdre"] != ""):
        q = q.filter(VSynthese.ordre == params["selectedOrdre"])
    if "selectedFamille" in params and (params["selectedFamille"] != ""):
        q = q.filter(VSynthese.famille == params["selectedFamille"])
    if ("selectedGroup2INPN"
            in params) and (params["selectedGroup2INPN"] != ""):
        q = q.filter(VSynthese.group2_inpn == params["selectedGroup2INPN"])
    if ("selectedGroup1INPN"
            in params) and (params["selectedGroup1INPN"] != ""):
        q = q.filter(VSynthese.group1_inpn == params["selectedGroup1INPN"])
    if ("taxon" in params) and (params["taxon"] != ""):
        q = q.filter(VSynthese.cd_ref == params["taxon"])
    return q.all()
示例#11
0
 def get_irradiation_names(self):
     with self.session_ctx() as sess:
         q = sess.query(distinct(IrradiationLevelTable.IrradBaseID))
         vs = q.all()
         if vs:
             vs = sorted([vi[0] for vi in vs], reverse=True)
         return vs
示例#12
0
def prepare_pre_pdf(tenant, state_code, batch_guid):
    '''
    prepare which state and district are pre-cached

    :param string tenant: name of the tenant
    :param string state_code: stateCode representing the state
    :param string batch_guid: batch GUID
    :rType: list
    :return:  list of results containing student information used to generate pdf
    '''
    with EdCoreDBConnection(tenant=tenant) as connector:
        fact_asmt_outcome_vw = connector.get_table(Constants.FACT_ASMT_OUTCOME_VW)
        dim_asmt = connector.get_table(Constants.DIM_ASMT)
        query = select([distinct(fact_asmt_outcome_vw.c.student_id).label(Constants.STUDENT_ID),
                        dim_asmt.c.asmt_period_year.label(Constants.ASMT_PERIOD_YEAR),
                        fact_asmt_outcome_vw.c.date_taken.label(Constants.DATETAKEN),
                        dim_asmt.c.asmt_type.label(Constants.ASMT_TYPE),
                        fact_asmt_outcome_vw.c.district_id.label(Constants.DISTRICT_ID),
                        fact_asmt_outcome_vw.c.school_id.label(Constants.SCHOOL_ID),
                        fact_asmt_outcome_vw.c.asmt_grade.label(Constants.ASMT_GRADE)],
                       from_obj=[fact_asmt_outcome_vw
                                 .join(dim_asmt, and_(dim_asmt.c.asmt_rec_id == fact_asmt_outcome_vw.c.asmt_rec_id,
                                                      dim_asmt.c.rec_status == Constants.CURRENT))])
        query = query.where(fact_asmt_outcome_vw.c.state_code == state_code)
        query = query.where(and_(fact_asmt_outcome_vw.c.batch_guid == batch_guid))
        query = query.where(and_(fact_asmt_outcome_vw.c.rec_status == Constants.CURRENT))
        results = connector.get_result(query)
        return results
示例#13
0
def generate_query_for_summative_or_interim(connection, asmt_type, student_ids,
                                            asmt_year, date_taken):
    fact_table = connection.get_table(Constants.FACT_ASMT_OUTCOME_VW)
    dim_asmt = connection.get_table(Constants.DIM_ASMT)
    query = Select(
        [
            distinct(fact_table.c.student_id).label(Constants.STUDENT_ID),
            fact_table.c.state_code.label(Constants.STATE_CODE),
            dim_asmt.c.asmt_period_year.label(Constants.ASMT_PERIOD_YEAR),
            fact_table.c.date_taken.label(Constants.DATETAKEN),
            fact_table.c.district_id.label(Constants.DISTRICT_ID),
            fact_table.c.school_id.label(Constants.SCHOOL_ID),
            fact_table.c.asmt_grade.label(Constants.ASMT_GRADE)
        ],
        from_obj=[
            fact_table.join(
                dim_asmt,
                and_(dim_asmt.c.asmt_rec_id == fact_table.c.asmt_rec_id,
                     dim_asmt.c.rec_status == Constants.CURRENT,
                     dim_asmt.c.asmt_type == asmt_type,
                     dim_asmt.c.asmt_period_year == asmt_year))
        ])
    query = query.where(
        and_(fact_table.c.rec_status == Constants.CURRENT,
             fact_table.c.student_id.in_(student_ids)))
    query = query.order_by(fact_table.c.student_id, fact_table.c.date_taken)
    if date_taken is not None:
        query = query.where(and_(fact_table.c.date_taken == date_taken))
    return query
示例#14
0
def generate_query_for_iab(connection, student_ids, asmt_year):
    fact_table = connection.get_table(Constants.FACT_BLOCK_ASMT_OUTCOME)
    dim_asmt = connection.get_table(Constants.DIM_ASMT)
    query = Select(
        [
            distinct(fact_table.c.student_id).label(Constants.STUDENT_ID),
            fact_table.c.state_code.label(Constants.STATE_CODE),
            dim_asmt.c.asmt_period_year.label(Constants.ASMT_PERIOD_YEAR),
            fact_table.c.district_id.label(Constants.DISTRICT_ID),
            fact_table.c.school_id.label(Constants.SCHOOL_ID)
        ],
        from_obj=[
            fact_table.join(
                dim_asmt,
                and_(
                    dim_asmt.c.asmt_rec_id == fact_table.c.asmt_rec_id,
                    dim_asmt.c.rec_status == Constants.CURRENT,
                    dim_asmt.c.asmt_type ==
                    AssessmentType.INTERIM_ASSESSMENT_BLOCKS,
                    dim_asmt.c.asmt_period_year == asmt_year))
        ])
    query = query.where(
        and_(fact_table.c.rec_status == Constants.CURRENT,
             fact_table.c.student_id.in_(student_ids)))
    return query
示例#15
0
def get_student_reg_academic_years(state_code, tenant=None):
    with EdCoreDBConnection(tenant=tenant, state_code=state_code) as connection:
        student_reg = connection.get_table(Constants.STUDENT_REG)
        query = select([distinct(student_reg.c.academic_year).label(Constants.ACADEMIC_YEAR)])\
            .where(student_reg.c.state_code == state_code).order_by(student_reg.c.academic_year.desc())
        results = connection.get_result(query)
    return list(result[Constants.ACADEMIC_YEAR] for result in results)
示例#16
0
文件: Model.py 项目: jroose/notochord
 def count_predicts_features(self):
     from sqlalchemy.sql.expression import func, distinct
     t_f = schema.feature
     t_fs = schema.feature_set
     return self.session.query(func.count(distinct(t_f.idfeature))) \
         .join(t_fs, t_fs.idfeature_set == t_f.idfeature_set) \
         .filter(t_fs.idmodel == self.idmodel)
 def get_irradiation_names(self):
     with self.session_ctx() as sess:
         q = sess.query(distinct(IrradiationLevelTable.IrradBaseID))
         vs = q.all()
         if vs:
             vs = sorted([vi[0] for vi in vs], reverse=True)
         return vs
示例#18
0
def filter_gene_names_present_in_database(gene_names_of_interest):
    _session = db.create_scoped_session()
    _log.info(
        "Filtering gene names that are already present in the database ...")

    try:
        # Make sure the gene names are a set, so we can pop them
        gene_names_of_interest = set(gene_names_of_interest)

        # check which gene names are already present in the database
        n_gene_names = len(gene_names_of_interest)
        n_filtered_gene_names = 0
        for gene_name in _session.query(distinct(Gene.gene_name)).filter(
                Gene.gene_name.in_(gene_names_of_interest)).all():
            gene_names_of_interest.remove(gene_name[0])
            n_filtered_gene_names += 1

        _log.info("Filtered '" + str(n_filtered_gene_names) + "' out of '" +
                  str(n_gene_names) +
                  "' gene names that are already present in the database ...")

        return list(gene_names_of_interest)
    except:
        _log.error(traceback.format_exc())
        raise
    finally:
        # Close this session, thus all items are cleared and memory usage is kept at a minimum
        _session.remove()
示例#19
0
    def get_recipient_users(self):
        groups = []

        if self.daily_schedule_subscribers.data:
            log.info("Email recipients includes daily schedule subscribers")
            groups.append(User.query
                    .options(
                        lazyload(User.organisation),
                        lazyload(User.committee_alerts),
                    )\
                    .filter(User.subscribe_daily_schedule == True)
                    .all())

        if self.committee_ids.data:
            log.info(
                "Email recipients includes subscribers for these committees: %s"
                % self.committee_ids.data)
            user_ids = db.session\
                    .query(distinct(user_committee_alerts.c.user_id))\
                    .filter(user_committee_alerts.c.committee_id.in_(self.committee_ids.data))\
                    .all()
            user_ids = [u[0] for u in user_ids]

            groups.append(User.query
                    .options(
                        lazyload(User.organisation),
                        lazyload(User.committee_alerts),
                    )\
                    .filter(User.id.in_(user_ids))
                    .all())

        return set(u for u in chain(*groups))
def get_select_for_state_view(dim_inst_hier, state_code):
    return select([
        distinct(dim_inst_hier.c.district_id).label(Constants.ID),
        dim_inst_hier.c.district_name.label(Constants.NAME)
    ],
                  from_obj=[dim_inst_hier
                            ]).where(dim_inst_hier.c.state_code == state_code)
示例#21
0
def request_db():
    #queries for 2 random actor names in the database and saves them as session
    result_list = [
        p for (p, ) in app.session.query(distinct(Movies.actor_name)).order_by(
            func.rand()).limit(2)
    ]
    session['result_list'] = result_list
    app.session.close()
示例#22
0
 def find_existing(
     cls,
     *,
     session: Session,
     server: Server,
     channel_xid: int,
     size: int,
     seats: int,
     tags: List[Tag],
     system: str,
     power: Optional[int],
 ) -> Optional[Game]:
     guild_xid = server.guild_xid
     required_tag_ids = set(tag.id for tag in tags)
     select_filters = [
         Game.status == "pending",
         Game.guild_xid == guild_xid,
         Game.size == size,
         Game.channel_xid == channel_xid,
         Game.system == system,
     ]
     having_filters = [
         func.count(distinct(games_tags.c.tag_id)) == len(required_tag_ids),
         func.count(distinct(User.xid)) <= size - seats,
     ]
     if power:
         having_filters.append(
             between(func.avg(User.power), power - 1, power + 1))
         if power >= 7:
             having_filters.append(func.avg(User.power) >= 7)
         else:
             having_filters.append(func.avg(User.power) < 7)
     else:
         select_filters.append(User.power == None)
     inner = (session.query(Game.id).join(User, isouter=True).join(
         games_tags, isouter=True).filter(and_(*select_filters)).group_by(
             Game.id).having(and_(*having_filters)))
     tag_filters = []
     for tid in required_tag_ids:
         tag_filters.append(games_tags.c.tag_id == tid)
     outer = (session.query(Game).join(games_tags, isouter=True).filter(
         and_(Game.id.in_(inner),
              or_(*tag_filters))).group_by(Game.id).having(
                  having_filters[0]).order_by(desc(Game.updated_at)))
     game: Optional[Game] = outer.first()
     return game
def get_student_reg_academic_years(state_code, tenant=None):
    with EdCoreDBConnection(tenant=tenant,
                            state_code=state_code) as connection:
        student_reg = connection.get_table(Constants.STUDENT_REG)
        query = select([distinct(student_reg.c.academic_year).label(Constants.ACADEMIC_YEAR)])\
            .where(student_reg.c.state_code == state_code).order_by(student_reg.c.academic_year.desc())
        results = connection.get_result(query)
    return list(result[Constants.ACADEMIC_YEAR] for result in results)
示例#24
0
def get_select_for_school_view(fact_table, state_code, district_id, school_id, asmtYear, asmtType, subject):
    return select([distinct(fact_table.c.asmt_grade).label(Constants.ID), fact_table.c.asmt_grade.label(Constants.NAME)], from_obj=[fact_table])\
        .where(and_(fact_table.c.state_code == state_code,
                    fact_table.c.district_id == district_id,
                    fact_table.c.school_id == school_id,
                    fact_table.c.asmt_year == asmtYear,
                    fact_table.c.rec_status == 'C',
                    fact_table.c.asmt_type == asmtType,
                    fact_table.c.asmt_subject == subject))
示例#25
0
文件: views.py 项目: nextgis/vote2map
def data(request):

    dbsession = DBSession()

    geom = request.params['geom'] if 'geom' in request.params else 'polygon'
    srs = int(request.params['srs']) if 'srs' in request.params else 4326
    root = int(request.params['root']) if 'root' in request.params else None
    depth = int(request.params['depth']) if 'depth' in request.params else 1
    level = int(request.params['level']) if 'level' in request.params else None

    filter = []

    # отбор по иерархии
    p_coalesce = []
    t_child = []
    for l in range(depth):
        t_tab = aliased(Unit)
        p_coalesce.insert(0, t_tab.id)
        t_child.append(t_tab)

    q_child = dbsession.query(distinct(func.coalesce(*p_coalesce)).label('child_id')) \
        .filter(t_child[0].parent_id == root)

    for l in range(depth):
        if l == 0: pass
        else: q_child = q_child.join((t_child[l], t_child[l - 1].child))

    child_id = [r.child_id for r in q_child.all()] 

    # геометрия
    if geom == 'polygon':
        t_geom = UnitPolygon
    elif geom == 'point':
        t_geom = UnitPoint
    
    e_geom = func.st_astext(func.st_transform(func.st_setsrid(t_geom.geom, 4326), srs))

    q_unit = dbsession.query(Unit, e_geom.label('geom')).options(joinedload(Unit.protocol_o), joinedload(Unit.protocol_i)).filter(Unit.id.in_(child_id)) \
        .outerjoin((t_geom, t_geom.unit_id == Unit.id))

    if level:
        q_unit = q_unit.filter(Unit.level == level)

    features = []

    for record in q_unit.all():
        properties = dict(protocol_o=record.Unit.protocol_o.as_dict() if record.Unit.protocol_o else None,
                          protocol_i=record.Unit.protocol_i.as_dict() if record.Unit.protocol_i else None,
                          unit=record.Unit.as_dict())
        features.append(Feature(id=record.Unit.id, geometry=loads_wkt(record.geom) if record.geom else None, properties=properties))

    fcoll = dumps_geojson(FeatureCollection(features))
    if 'callback' in request.params:
        return Response('%s(%s)' % (request.params['callback'], fcoll), content_type="text/javascript")
    else:
        return Response(fcoll, content_type="application/json")
示例#26
0
def get_user_languages(user_id):
    return ([
        p for p, in (
            db.session.query(distinct(Phrase.language))
            .filter(
                Phrase.user_id == user_id,
                Phrase.status == Phrase.Status.visible.value,
            )
        )
    ])
def get_select_for_school_view(fact_table, state_code, district_id, school_id,
                               asmtYear, asmtType, subject):
    return select([distinct(fact_table.c.asmt_grade).label(Constants.ID), fact_table.c.asmt_grade.label(Constants.NAME)], from_obj=[fact_table])\
        .where(and_(fact_table.c.state_code == state_code,
                    fact_table.c.district_id == district_id,
                    fact_table.c.school_id == school_id,
                    fact_table.c.asmt_year == asmtYear,
                    fact_table.c.rec_status == 'C',
                    fact_table.c.asmt_type == asmtType,
                    fact_table.c.asmt_subject == subject))
def get_years(model):
    if model == "distinct":
        q = DB.session.query(
            label("year", distinct(func.date_part("year", VSynthese.date_min)))
        ).order_by("year")
    if model == "min-max":
        q = DB.session.query(
            func.min(func.date_part("year", VSynthese.date_min)),
            func.max(func.date_part("year", VSynthese.date_min)),
        )
    return q.all()
示例#29
0
 def getEntriesFiltered(self, identifier, accessId, **options):
     '''
     @see: IACLPrototype.getEntriesFiltered
     '''
     assert isinstance(accessId, int), 'Invalid access id %s' % accessId
     
     sql = self.session().query(distinct(EntryMapped.Position))
     sql = sql.join(self.EntryFilter).join(self.AclAccess).join(self.Acl)
     sql = sql.filter(self.AclAccess.accessId == accessId).filter(self.AclIdentifier == identifier)
     sql = sql.order_by(EntryMapped.Position)
     return iterateCollection(sql, **options)
示例#30
0
    def autocomplete_tags(self, all=False):
        text = request.GET.get('val', None)

        # filter warnings
        import warnings
        warnings.filterwarnings('ignore', module='pylons.decorators')

        if text:
            if all:
                query = meta.Session.query(expression.distinct(expression.func.lower(Tag.title)))
            else:
                query = meta.Session.query(expression.distinct(expression.func.lower(SimpleTag.title)))

            query = query.filter(or_(Tag.title_short.op('ILIKE')('%s%%' % text),
                                     Tag.title.op('ILIKE')('%s%%' % text)))

            results = [title for title in query.all()]
            return dict(values = results)

        return None
示例#31
0
    def mspectra(self, scanid, mslevel=None):
        """Returns dict with peaks of a scan

        Also returns the cutoff applied to the scan
        and mslevel, precursor.id (parent scan id) and precursor.mz

        scanid
            Scan identifier of scan of which to return the mspectra

        mslevel
            Ms level on which the scan must be. Optional.
            If scanid not on mslevel raises ScanNotFound

        """
        scanq = self.session.query(Scan).filter(Scan.scanid == scanid)
        if (mslevel is not None):
            scanq = scanq.filter(Scan.mslevel == mslevel)

        try:
            scan = scanq.one()
        except NoResultFound:
            raise ScanNotFound()

        # lvl1 scans use absolute cutoff, lvl>1 use ratio of basepeak as cutoff
        if (scan.mslevel == 1):
            cutoff = self.session.query(Run.ms_intensity_cutoff).scalar()
        else:
            rel_cutoff = Scan.basepeakintensity * Run.msms_intensity_cutoff
            q = self.session.query(rel_cutoff)
            cutoff = q.filter(Scan.scanid == scanid).scalar()

        peaks = []
        for peak in self.session.query(Peak).filter_by(scanid=scanid):
            peaks.append({
                'mz': peak.mz,
                'intensity': peak.intensity,
                'assigned_molid': peak.assigned_molid,
            })

        precursor = {'id': scan.precursorscanid, 'mz': scan.precursormz}
        response = {
            'peaks': peaks,
            'cutoff': cutoff,
            'mslevel': scan.mslevel,
            'precursor': precursor,
        }
        if (scan.mslevel == 1):
            fragments = []
            fragq = self.session.query(distinct(Fragment.mz).label('mz'))
            fragq = fragq.filter_by(scanid=scanid)
            for fragment in fragq:
                fragments.append({'mz': fragment.mz})
            response['fragments'] = fragments
        return response
示例#32
0
    def recent_codenames(cls, fullname):
        """Get a list of recent codenames used for 300x100 ads.

        The 300x100 ads get a codename that looks like "fullname_campaign".
        This function gets a list of recent campaigns.
        """
        time_points = get_time_points('day')
        query = (Session.query(distinct(cls.codename).label("codename"))
                        .filter(cls.date.in_(time_points))
                        .filter(cls.codename.startswith(fullname)))
        return [row.codename for row in query]
示例#33
0
文件: db.py 项目: kuhout/agi
def GetSquads(run_id=None):
  conditions = (Team.table.c.present > 0)
  if run_id:
    run = Run.get_by(id=run_id)
    conditions = conditions & (Team.table.c.size==run.size) & (Team.table.c.present.op('&')(1 << (run.day - 1)))
  res = session.execute(select([distinct(Team.table.c.squad)], conditions)).fetchall()
  squads = [item for sublist in res for item in sublist]
  for s in squads[:]:
    if not s:
      squads.remove(s)
  return squads
示例#34
0
    def getActionsRoot(self, identifier, **options):
        '''
        @see: IUserRbacService.getActions
        '''
        rbacId = self.findRbacId(identifier)
        if rbacId is None: return emptyCollection(**options)

        sql = self.session().query(distinct(RightAction.actionPath))
        sql = sql.filter(RightAction.categoryId.in_(self.sqlRights(rbacId)))  # @UndefinedVariable

        return processCollection(listRootPaths(path for path, in sql.all()), **options)
示例#35
0
    def getEntriesFiltered(self, identifier, accessId, **options):
        '''
        @see: IACLPrototype.getEntriesFiltered
        '''
        assert isinstance(accessId, int), 'Invalid access id %s' % accessId

        sql = self.session().query(distinct(EntryMapped.Position))
        sql = sql.join(self.EntryFilter).join(self.AclAccess).join(self.Acl)
        sql = sql.filter(self.AclAccess.accessId == accessId).filter(
            self.AclIdentifier == identifier)
        sql = sql.order_by(EntryMapped.Position)
        return iterateCollection(sql, **options)
    def _setup_defaults(self):
        """setting up the defaults
        """
        # fill the asset_types_comboBox with all the asset types from the db
        all_types = map(lambda x: x[0], db.query(distinct(Asset.type)).all())

        if conf.default_asset_type_name not in all_types:
            all_types.append(conf.default_asset_type_name)
        
        logger.debug('all_types: %s' % all_types)
         
        self.asset_types_comboBox.addItems(all_types)
示例#37
0
文件: views.py 项目: sdoom/shapps
 def org_roles(self, org_id):
     SchemaModel = get_model('rbacscheme')
     #----check if the organization is a global organization
     Org = self.model.get(org_id)
     RoleModel = get_model('role')
     RPRModel = get_model('Role_Perm_Rel')
     if Org.rbacscheme:
         query = select([self.model.c.id, self.model.c.name, self.model.c.rbacscheme, SchemaModel.c.id.label('schema_id'), SchemaModel.c.name.label('schema_name')]).select_from(join(self.model.table, SchemaModel.table, self.model.c.rbacscheme == SchemaModel.c.id)).where(self.model.c.id == org_id)
         OrgObj = do_(query).fetchone()
         query = select([distinct(RoleModel.c.id), RoleModel.c.name]).select_from(join(RoleModel.table, RPRModel.table, RoleModel.c.id == RPRModel.c.role)).where(RPRModel.c.scheme == OrgObj.rbacscheme)
     else:
         #----global organization
         query = select([self.model.c.id, self.model.c.name, self.model.c.rbacscheme, SchemaModel.c.id.label('schema_id'), SchemaModel.c.name.label('schema_name')]).select_from(join(self.model.table, SchemaModel.table, self.model.c.id == SchemaModel.c.gorg)).where(self.model.c.id == org_id)
         OrgObj = do_(query).fetchone()
         query = select([distinct(RoleModel.c.id), RoleModel.c.name]).select_from(join(RoleModel.table, RPRModel.table, RoleModel.c.id == RPRModel.c.role)).where(RPRModel.c.scheme == OrgObj.schema_id)
     #----need to filter the rols which belone to this schema
     roleList = do_(query)
     roleDict = {}
     for s in roleList:
         roleDict[s.id] = s.name
     return {'orgid':org_id, 'orgname':OrgObj.name, 'schemaname':OrgObj.schema_name, 'schema':OrgObj.rbacscheme , 'schema_id':OrgObj.schema_id, 'roleDict':roleDict}
示例#38
0
    def mspectra(self, scanid, mslevel=None):
        """Returns dict with peaks of a scan

        Also returns the cutoff applied to the scan
        and mslevel, precursor.id (parent scan id) and precursor.mz

        scanid
            Scan identifier of scan of which to return the mspectra

        mslevel
            Ms level on which the scan must be. Optional.
            If scanid not on mslevel raises ScanNotFound

        """
        scanq = self.session.query(Scan).filter(Scan.scanid == scanid)
        if (mslevel is not None):
            scanq = scanq.filter(Scan.mslevel == mslevel)

        try:
            scan = scanq.one()
        except NoResultFound:
            raise ScanNotFound()

        # lvl1 scans use absolute cutoff, lvl>1 use ratio of basepeak as cutoff
        if (scan.mslevel == 1):
            cutoff = self.session.query(Run.ms_intensity_cutoff).scalar()
        else:
            rel_cutoff = Scan.basepeakintensity * Run.msms_intensity_cutoff
            q = self.session.query(rel_cutoff)
            cutoff = q.filter(Scan.scanid == scanid).scalar()

        peaks = []
        for peak in self.session.query(Peak).filter_by(scanid=scanid):
            peaks.append({
                'mz': peak.mz,
                'intensity': peak.intensity,
                'assigned_molid': peak.assigned_molid,
            })

        precursor = {'id': scan.precursorscanid, 'mz': scan.precursormz}
        response = {'peaks': peaks,
                    'cutoff': cutoff,
                    'mslevel': scan.mslevel,
                    'precursor': precursor,
                    }
        if (scan.mslevel == 1):
            fragments = []
            fragq = self.session.query(distinct(Fragment.mz).label('mz'))
            fragq = fragq.filter_by(scanid=scanid)
            for fragment in fragq:
                fragments.append({'mz': fragment.mz})
            response['fragments'] = fragments
        return response
示例#39
0
 def get_students(self, tenant, student_ids):
     '''
     Returns a query that gives a list of distinct student guids given that a list of student guids are supplied
     '''
     queries = []
     for fact_table_name in [Constants.FACT_ASMT_OUTCOME_VW, Constants.FACT_BLOCK_ASMT_OUTCOME]:
         fact_table = self.connector.get_table(fact_table_name)
         query = select([distinct(fact_table.c.student_id)],
                        from_obj=[fact_table])
         query = query.where(and_(fact_table.c.rec_status == Constants.CURRENT, fact_table.c.student_id.in_(student_ids)))
         queries.append(query)
     return queries
示例#40
0
 def party_counts(self):
     party_counts = db_session.query(Party.name,
                                     func.count(distinct(Person.id)))\
                              .join(Candidacy)\
                              .join(Election)\
                              .filter(Election.age == self.age)\
                              .join(Person)\
                              .join(cosponsorship)\
                              .join(Bill)\
                              .filter(Bill.id == self.id)\
                              .group_by(Party.id)
     return [(party, int(count)) for party, count in party_counts]
示例#41
0
 def getANDPosts(self, tags, limit=100, extra_items=None):
     q = self.DBsession().query(Post).join(Post.tags)
     if extra_items:
         q = self._dict2ToQuery(q, extra_items)
     if self.board:
         q = q.filter(Post.board == self.board)
     q = q.filter(Tag.name.in_(tags)).group_by(Post.image_id)
     q = q.having(func.count(distinct(Tag.name)) == len(tags))
     q = q.order_by(Post.post_id.desc())
     if limit:
         q = q.limit(limit)
     return q.all()
示例#42
0
    def _setup_defaults(self):
        """setting up the defaults
        """
        # fill the asset_types_comboBox with all the asset types from the db
        all_types = map(lambda x: x[0], db.query(distinct(Asset.type)).all())

        if conf.default_asset_type_name not in all_types:
            all_types.append(conf.default_asset_type_name)

        logger.debug('all_types: %s' % all_types)

        self.asset_types_comboBox.addItems(all_types)
示例#43
0
 def party_counts(self):
     party_counts = db_session.query(Party.name,
                                     func.count(distinct(Person.id)))\
                              .join(Candidacy)\
                              .join(Election)\
                              .filter(Election.assembly_id == self.assembly_id)\
                              .join(Person)\
                              .join(cosponsorship)\
                              .join(Bill)\
                              .filter(Bill.id == self.id)\
                              .group_by(Party.id)
     return [(party, int(count)) for party, count in party_counts]
示例#44
0
 def count_post_viewers(
         self, start_date=None, end_date=None, as_agent=True):
     from .post import Post
     from .action import ViewPost
     query = self.db.query(
         func.count(distinct(ViewPost.actor_id))).join(Post).filter(
             Post.discussion_id == self.id)
     if start_date:
         query = query.filter(ViewPost.creation_date >= start_date)
     if end_date:
         query = query.filter(ViewPost.creation_date < end_date)
     return query.first()[0]
def prepare_pre_cache(tenant, state_code, batch_guid):
    '''
    prepare which state and district are pre-cached

    :param string tenant:  name of the tenant
    :param string state_code:  stateCode representing the state
    :param last_pre_cached:  dateTime of the last precached
    :rType: list
    :return:  list of results containing district guids
    '''
    with EdCoreDBConnection(tenant=tenant) as connector:
        fact_asmt_outcome_vw = connector.get_table(
            Constants.FACT_ASMT_OUTCOME_VW)
        fact_block_asmt_outcome = connector.get_table(
            Constants.FACT_BLOCK_ASMT_OUTCOME)
        query_fao = select([
            distinct(fact_asmt_outcome_vw.c.district_id).label(
                Constants.DISTRICT_ID)
        ],
                           from_obj=[fact_asmt_outcome_vw])
        query_fao = query_fao.where(
            fact_asmt_outcome_vw.c.state_code == state_code)
        query_fao = query_fao.where(
            and_(fact_asmt_outcome_vw.c.batch_guid == batch_guid))
        query_fao = query_fao.where(
            and_(fact_asmt_outcome_vw.c.rec_status == Constants.CURRENT))
        query_fbao = select([
            distinct(fact_block_asmt_outcome.c.district_id).label(
                Constants.DISTRICT_ID)
        ],
                            from_obj=[fact_block_asmt_outcome])
        query_fbao = query_fbao.where(
            fact_block_asmt_outcome.c.state_code == state_code)
        query_fbao = query_fbao.where(
            and_(fact_block_asmt_outcome.c.batch_guid == batch_guid))
        query_fbao = query_fbao.where(
            and_(fact_block_asmt_outcome.c.rec_status == Constants.CURRENT))
        results = connector.get_result(query_fao.union(query_fbao))
        return results
示例#46
0
 def count_post_viewers(
         self, start_date=None, end_date=None, as_agent=True):
     from .post import Post
     from .action import ViewPost
     from sqlalchemy.sql.expression import distinct
     query = self.db.query(
         func.count(distinct(ViewPost.actor_id))).join(Post).filter(
             Post.discussion_id == self.id)
     if start_date:
         query = query.filter(ViewPost.creation_date >= start_date)
     if end_date:
         query = query.filter(ViewPost.creation_date < end_date)
     return query.first()[0]
示例#47
0
def prepare_pre_cache(public_tenant):
    '''
    prepare which state and district are pre-cached

    :param string tenant:  name of the tenant
    :rType: list
    :return:  list of results containing state_code
    '''
    with EdMigratePublicConnection(public_tenant) as connector:
        fact_asmt_outcome_vw = connector.get_table(Constants.FACT_ASMT_OUTCOME_VW)
        fact_block_asmt_outcome = connector.get_table(Constants.FACT_BLOCK_ASMT_OUTCOME)
        query_fao = select([distinct(fact_asmt_outcome_vw.c.state_code).label(Constants.STATE_CODE),
                            fact_asmt_outcome_vw.c.district_id.label(Constants.DISTRICT_ID),
                            fact_asmt_outcome_vw.c.school_id.label(Constants.SCHOOL_ID)], from_obj=[fact_asmt_outcome_vw])
        query_fao = query_fao.where(and_(fact_asmt_outcome_vw.c.rec_status == Constants.CURRENT))
        query_fbao = select([distinct(fact_block_asmt_outcome.c.state_code).label(Constants.STATE_CODE),
                             fact_block_asmt_outcome.c.district_id.label(Constants.DISTRICT_ID),
                             fact_block_asmt_outcome.c.school_id.label(Constants.SCHOOL_ID)], from_obj=[fact_block_asmt_outcome])
        query_fbao = query_fbao.where(and_(fact_block_asmt_outcome.c.rec_status == Constants.CURRENT))
        query = query_fao.union(query_fbao).order_by(Constants.STATE_CODE, Constants.DISTRICT_ID, Constants.SCHOOL_ID)
        results = connector.get_result(query)
        return results
示例#48
0
def prepare_pre_cache(tenant, state_code, batch_guid):
    '''
    prepare which state and district are pre-cached

    :param string tenant:  name of the tenant
    :param string state_code:  stateCode representing the state
    :param last_pre_cached:  dateTime of the last precached
    :rType: list
    :return:  list of results containing district guids
    '''
    with EdCoreDBConnection(tenant=tenant) as connector:
        fact_asmt_outcome_vw = connector.get_table(Constants.FACT_ASMT_OUTCOME_VW)
        fact_block_asmt_outcome = connector.get_table(Constants.FACT_BLOCK_ASMT_OUTCOME)
        query_fao = select([distinct(fact_asmt_outcome_vw.c.district_id).label(Constants.DISTRICT_ID)], from_obj=[fact_asmt_outcome_vw])
        query_fao = query_fao.where(fact_asmt_outcome_vw.c.state_code == state_code)
        query_fao = query_fao.where(and_(fact_asmt_outcome_vw.c.batch_guid == batch_guid))
        query_fao = query_fao.where(and_(fact_asmt_outcome_vw.c.rec_status == Constants.CURRENT))
        query_fbao = select([distinct(fact_block_asmt_outcome.c.district_id).label(Constants.DISTRICT_ID)], from_obj=[fact_block_asmt_outcome])
        query_fbao = query_fbao.where(fact_block_asmt_outcome.c.state_code == state_code)
        query_fbao = query_fbao.where(and_(fact_block_asmt_outcome.c.batch_guid == batch_guid))
        query_fbao = query_fbao.where(and_(fact_block_asmt_outcome.c.rec_status == Constants.CURRENT))
        results = connector.get_result(query_fao.union(query_fbao))
        return results
示例#49
0
def gold_buyers_on(date):
    start_date = datetime.datetime.combine(date, datetime.time.min)
    end_date = datetime.datetime.combine(date, datetime.time.max)

    NON_REVENUE_STATUSES = ("declined", "chargeback", "fudge")
    date_expr = func.timezone(TIMEZONE.zone, gold_table.c.date)
    query = (select([distinct(gold_table.c.account_id)])
                .where(~ gold_table.c.status.in_(NON_REVENUE_STATUSES))
                .where(date_expr >= start_date)
                .where(date_expr <= end_date)
                .where(gold_table.c.pennies > 0)
            )
    rows = ENGINE.execute(query)
    return [int(account_id) for (account_id,) in rows.fetchall() if account_id]
示例#50
0
def gold_buyers_on(date):
    start_date = datetime.datetime.combine(date, datetime.time.min)
    end_date = datetime.datetime.combine(date, datetime.time.max)

    NON_REVENUE_STATUSES = ("declined", "chargeback", "fudge")
    date_expr = func.timezone(TIMEZONE.zone, gold_table.c.date)
    query = (select([distinct(gold_table.c.account_id)])
                .where(~ gold_table.c.status.in_(NON_REVENUE_STATUSES))
                .where(date_expr >= start_date)
                .where(date_expr <= end_date)
                .where(gold_table.c.pennies > 0)
            )
    rows = ENGINE.execute(query)
    return [int(account_id) for (account_id,) in rows.fetchall() if account_id]
示例#51
0
 def getSubActions(self, identifier, parentPath, **options):
     '''
     @see: IUserRbacService.getSubActions
     '''
     assert isinstance(parentPath, str), 'Invalid parent path %s' % parentPath
     
     rbacId = self.findRbacId(identifier)
     if rbacId is None: return emptyCollection(**options)
     
     sql = self.session().query(distinct(RightAction.actionPath))
     sql = sql.filter(RightAction.categoryId.in_(self.sqlRights(rbacId)))  # @UndefinedVariable
     sql = sql.filter(RightAction.actionPath.like('%s.%%' % parentPath))  # @UndefinedVariable
     
     return processCollection(listRootPaths((path for path, in sql.all()), len(parentPath) + 1), **options)
示例#52
0
def event_notification(event_id):
    """Sends a notification mail informing all attendees to bring their tickets
    with them."""
    blogger.info('sending notifications for event %s' % event_id)
    users = Session.query(distinct(User.id))\
                   .join(Order, TicketOrder, TicketType, TicketTypeEventPartAssociation, EventPart, Event)\
                   .filter(Event.id==event_id)\
                   .filter(Order.status==PURCHASED)\
                   .all()
    count = 0
    for user_id, in users:
        batchnr = count / 20  # twenty mails per batch = 2 minute bursts
        single_notification_mail.apply_async(args=[event_id, user_id],
                                             countdown=batchnr * 5 * 60)
        count += 1
示例#53
0
def prepare_pre_cache(public_tenant):
    '''
    prepare which state and district are pre-cached

    :param string tenant:  name of the tenant
    :rType: list
    :return:  list of results containing state_code
    '''
    with EdMigratePublicConnection(public_tenant) as connector:
        fact_asmt_outcome_vw = connector.get_table(
            Constants.FACT_ASMT_OUTCOME_VW)
        fact_block_asmt_outcome = connector.get_table(
            Constants.FACT_BLOCK_ASMT_OUTCOME)
        query_fao = select([
            distinct(fact_asmt_outcome_vw.c.state_code).label(
                Constants.STATE_CODE),
            fact_asmt_outcome_vw.c.district_id.label(Constants.DISTRICT_ID),
            fact_asmt_outcome_vw.c.school_id.label(Constants.SCHOOL_ID)
        ],
                           from_obj=[fact_asmt_outcome_vw])
        query_fao = query_fao.where(
            and_(fact_asmt_outcome_vw.c.rec_status == Constants.CURRENT))
        query_fbao = select([
            distinct(fact_block_asmt_outcome.c.state_code).label(
                Constants.STATE_CODE),
            fact_block_asmt_outcome.c.district_id.label(Constants.DISTRICT_ID),
            fact_block_asmt_outcome.c.school_id.label(Constants.SCHOOL_ID)
        ],
                            from_obj=[fact_block_asmt_outcome])
        query_fbao = query_fbao.where(
            and_(fact_block_asmt_outcome.c.rec_status == Constants.CURRENT))
        query = query_fao.union(query_fbao).order_by(Constants.STATE_CODE,
                                                     Constants.DISTRICT_ID,
                                                     Constants.SCHOOL_ID)
        results = connector.get_result(query)
        return results
示例#54
0
def generate_query_for_iab(connection, student_ids, asmt_year):
    fact_table = connection.get_table(Constants.FACT_BLOCK_ASMT_OUTCOME)
    dim_asmt = connection.get_table(Constants.DIM_ASMT)
    query = Select([distinct(fact_table.c.student_id).label(Constants.STUDENT_ID),
                    fact_table.c.state_code.label(Constants.STATE_CODE),
                    dim_asmt.c.asmt_period_year.label(Constants.ASMT_PERIOD_YEAR),
                    fact_table.c.district_id.label(Constants.DISTRICT_ID),
                    fact_table.c.school_id.label(Constants.SCHOOL_ID)],
                   from_obj=[fact_table
                             .join(dim_asmt, and_(dim_asmt.c.asmt_rec_id == fact_table.c.asmt_rec_id,
                                                  dim_asmt.c.rec_status == Constants.CURRENT,
                                                  dim_asmt.c.asmt_type == AssessmentType.INTERIM_ASSESSMENT_BLOCKS,
                                                  dim_asmt.c.asmt_period_year == asmt_year))])
    query = query.where(and_(fact_table.c.rec_status == Constants.CURRENT, fact_table.c.student_id.in_(student_ids)))
    return query
示例#55
0
def purge_entity_data(instance: Recorder, entity_filter: Callable[[str], bool]) -> bool:
    """Purge states and events of specified entities."""
    with session_scope(session=instance.get_session()) as session:  # type: ignore[misc]
        selected_entity_ids: list[str] = [
            entity_id
            for (entity_id,) in session.query(distinct(States.entity_id)).all()
            if entity_filter(entity_id)
        ]
        _LOGGER.debug("Purging entity data for %s", selected_entity_ids)
        if len(selected_entity_ids) > 0:
            # Purge a max of MAX_ROWS_TO_PURGE, based on the oldest states or events record
            _purge_filtered_states(instance, session, selected_entity_ids)
            _LOGGER.debug("Purging entity data hasn't fully completed yet")
            return False

    return True
示例#56
0
def generate_query_for_summative_or_interim(connection, asmt_type, student_ids, asmt_year, date_taken):
    fact_table = connection.get_table(Constants.FACT_ASMT_OUTCOME_VW)
    dim_asmt = connection.get_table(Constants.DIM_ASMT)
    query = Select([distinct(fact_table.c.student_id).label(Constants.STUDENT_ID),
                    fact_table.c.state_code.label(Constants.STATE_CODE),
                    dim_asmt.c.asmt_period_year.label(Constants.ASMT_PERIOD_YEAR),
                    fact_table.c.date_taken.label(Constants.DATETAKEN),
                    fact_table.c.district_id.label(Constants.DISTRICT_ID),
                    fact_table.c.school_id.label(Constants.SCHOOL_ID),
                    fact_table.c.asmt_grade.label(Constants.ASMT_GRADE)],
                   from_obj=[fact_table
                             .join(dim_asmt, and_(dim_asmt.c.asmt_rec_id == fact_table.c.asmt_rec_id,
                                                  dim_asmt.c.rec_status == Constants.CURRENT,
                                                  dim_asmt.c.asmt_type == asmt_type,
                                                  dim_asmt.c.asmt_period_year == asmt_year))])
    query = query.where(and_(fact_table.c.rec_status == Constants.CURRENT, fact_table.c.student_id.in_(student_ids)))
    query = query.order_by(fact_table.c.student_id, fact_table.c.date_taken)
    if date_taken is not None:
        query = query.where(and_(fact_table.c.date_taken == date_taken))
    return query
示例#57
0
def get_select_for_state_view(dim_inst_hier, state_code):
    return select([distinct(dim_inst_hier.c.district_id).label(Constants.ID), dim_inst_hier.c.district_name.label(Constants.NAME)], from_obj=[dim_inst_hier]).where(dim_inst_hier.c.state_code == state_code)
示例#58
0
def get_select_for_district_view(dim_inst_hier, state_code, district_id):
    return select([distinct(dim_inst_hier.c.school_id).label(Constants.ID), dim_inst_hier.c.school_name.label(Constants.NAME)], from_obj=[dim_inst_hier])\
        .where(and_(dim_inst_hier.c.state_code == state_code, dim_inst_hier.c.district_id == district_id))