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
def apply_ordering(
cls,
statement: Select,
ordering: Optional["OrderingQuery"],
) -> Select:
if ordering is None or not ordering.orderings:
return statement
order_by_clause = []
for x in ordering.orderings:
asc: Optional[bool] = None
if x.startswith("-"):
asc = False
field = x[1:]
elif x.startswith("+"):
asc = True
field = x[1:]
else:
asc = None
field = x
if field.startswith("_"):
continue
sa_column = getattr(cls, field, None)
if sa_column is not None and isinstance(sa_column,
InstrumentedAttribute):
if asc is None:
order_by_clause.append(sa_column)
elif asc:
order_by_clause.append(sa_column.asc())
else:
order_by_clause.append(sa_column.desc())
if len(order_by_clause) > 0:
statement = statement.order_by(*order_by_clause)
return statement
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
