def status():
record = inventory.query.add_columns(
func.SUM(inventory.in_stock).label('in_stock'),
func.SUM(inventory.given).label('given'),
func.SUM(inventory.expired).label('expired')).all()
return record
def type():
record = inventory.query.add_columns(
inventory.type_id,
func.SUM(inventory.in_stock).label('in_stock'),
func.SUM(inventory.given).label('given'),
func.SUM(inventory.expired).label('expired')).group_by(
inventory.type_id).all()
return record
def relatValorDia(self):
try:
# Abrindo Sessao
conecta = Conexao()
sessao = conecta.Session()
# Query
row = (sessao.query(
func.COALESCE(func.SUM(Venda.valor_recebido),
0).label('vendido'),
func.COUNT(distinct(
Venda.id_cliente)).label('cliente')).filter(
Venda.data_emissao.between(self.dataEmissao,
self.dataFim)))
row.all()
# salvando resultado
for query in row:
self.valorRecebido = str(query.vendido).replace('.', ',')
self.idCliente = query.cliente
# Fechando a COnexao
sessao.close()
except IntegrityError as err:
print(err)
def detalheEntrada(self):
try:
# Abrindo Sessao
conecta = Conexao()
sessao = conecta.Session()
# Query
self.query = (sessao.query(
func.SUM(ContaAReceber.valor_recebido).label('entrada'),
CatAReceber.categoria_a_receber,
FormaPagamento.forma_pagamento).join(CatAReceber).join(
FormaPagamento).filter(
ContaAReceber.data_recebimento.between(
self.dataRecebimento, self.dataFim)).group_by(
ContaAReceber.forma_pagamento,
ContaAReceber.categoria))
# Convertendo variaveis em lista
self.valorRecebido = []
self.categoria = []
self.formaPagamento = []
# Salvando resultado em suas listas
for row in self.query:
self.categoria.append(row.categoria_a_receber)
self.valorRecebido.append(row.entrada)
self.formaPagamento.append(row.forma_pagamento)
# Fechando a Conexao
sessao.close()
except IntegrityError as err:
print(err)
def __call__(self, user_ids, session):
"""
Parameters:
user_ids : list of mediawiki user ids to restrict computation to
session : sqlalchemy session open on a mediawiki database
Returns:
{
user id: 1 if they're a rolling new active editor, 0 otherwise
for all cohort users, or all users that have edits in the time period
}
"""
number_of_edits = int(self.number_of_edits.data)
rolling_days = int(self.rolling_days.data)
end_date = self.end_date.data
start_date = end_date - timedelta(days=rolling_days)
newly_registered = session.query(Logging.log_user) \
.filter(Logging.log_type == 'newusers') \
.filter(Logging.log_action == 'create') \
.filter(between(Logging.log_timestamp, start_date, end_date))
filtered_new = self.filter(newly_registered,
user_ids,
column=Logging.log_user).subquery()
rev_user = label('user_id', Revision.rev_user)
ar_user = label('user_id', Archive.ar_user)
count = label('count', func.count())
revisions = session.query(rev_user, count)\
.filter(between(Revision.rev_timestamp, start_date, end_date))\
.filter(Revision.rev_user.in_(filtered_new))\
.group_by(Revision.rev_user)
archived = session.query(ar_user, count)\
.filter(between(Archive.ar_timestamp, start_date, end_date))\
.filter(Archive.ar_user.in_(filtered_new))\
.group_by(Archive.ar_user)
bot_user_ids = session.query(MediawikiUserGroups.ug_user)\
.filter(MediawikiUserGroups.ug_group == 'bot')\
.subquery()
new_edits = revisions.union_all(archived).subquery()
new_edits_by_user = session.query(new_edits.c.user_id)\
.filter(new_edits.c.user_id.notin_(bot_user_ids))\
.group_by(new_edits.c.user_id)\
.having(func.SUM(new_edits.c.count) >= number_of_edits)
metric_results = {r[0]: {self.id: 1} for r in new_edits_by_user.all()}
if user_ids is None:
return metric_results
else:
return {
uid: metric_results.get(uid, self.default_result)
for uid in user_ids
}
def get_total_amount_staked(self):
try:
query_response = self.session.query(
func.SUM(StakeHolderDBModel.amount_approved).label(
"total_amount_approved"),
func.SUM(StakeHolderDBModel.amount_pending_for_approval).label(
"total_amount_pending_for_approval")).one()
self.session.commit()
except Exception as e:
self.session.rollback()
raise e
total_amount_approved = 0
total_amount_pending_for_approval = 0
if query_response.total_amount_approved or query_response.total_amount_pending_for_approval:
total_amount_approved = int(query_response.total_amount_approved)
total_amount_pending_for_approval = int(
query_response.total_amount_pending_for_approval)
total_amount_staked = total_amount_approved + total_amount_pending_for_approval
return total_amount_staked
def get_aum_from_user_sta_table(hold_date):
"""
从用户统计表里获取某天总的在管资产
:return: list
"""
t = Table('user_statistics', metadata, autoload=True)
# Session = sessionmaker(bind=db)
# session = Session()
rst = session.query(func.SUM(t.c.us_holding_amount)).filter( \
t.c.us_date == hold_date)
return rst.all()
def movDespesa(self):
try:
# Abrindo Sessao
conecta = Conexao()
sessao = conecta.Session()
# Query
row = (sessao.query(func.COALESCE(
func.SUM(ContaAPagar.valor_pago), 0
).label('valorPago'))
.filter(ContaAPagar.data_pagamento.between(
self.dataPagamento, self.dataFim))
)
row.all()
# Salvando resultado
for row in row:
self.valorPago = row.valorPago
# Query
row = (sessao.query(func.COALESCE(
func.SUM(ContaAPagar.valor), 0
).label('valorAPagar'))
.filter(ContaAPagar.data_vencimento.between(
self.dataPagamento, self.dataFim))
)
row.all()
# Salvando resultado
for row in row:
self.valorAPagar = row.valorAPagar
# Fechando a Conexao
sessao.close
except IntegrityError as err:
print(err)
def get_specific_month_amount(hold_date):
"""
获取某天用户总持仓金额
:return: list
"""
# db = database.connection('portfolio_sta')
# metadata = MetaData(bind=db)
t = Table('ds_share', metadata, autoload=True)
# Session = sessionmaker(bind=db)
# session = Session()
rst = session.query(func.SUM(t.c.ds_amount)).filter( \
t.c.ds_date == hold_date)
return rst.all()
def movEntrada(self):
try:
# Abrindo Sessao
conecta = Conexao()
sessao = conecta.Session()
# Query
row = (sessao.query(
func.COALESCE(func.SUM(ContaAReceber.valor_recebido),
0).label('valorRecebido')).filter(
ContaAReceber.data_recebimento.between(
self.dataRecebimento, self.dataFim)))
row.all()
# Salvando resultado
for row in row:
self.valorRecebido = row.valorRecebido
# Query
row = (sessao.query(
func.COALESCE(func.SUM(ContaAReceber.valor),
0).label('valorAReceber')).filter(
ContaAReceber.data_vencimento.between(
self.dataRecebimento, self.dataFim)))
row.all()
# Salvando resultado
for row in row:
self.valorAReceber = row.valorAReceber
# Fechando a Conexao
sessao.close
except IntegrityError as err:
print(err)
pass
def get_specific_date_amount(hold_date, uids):
"""
获取某一天ds_uid in uids的用户持仓金额
:param hold_date:持仓日期
:param uids:用户id 列表
:return: list
"""
# db = database.connection('portfolio_sta')
# metadata = MetaData(bind=db)
t = Table('ds_share', metadata, autoload=True)
# Session = sessionmaker(bind=db)
# session = Session()
rst = session.query(func.SUM(t.c.ds_amount)).filter(t.c.ds_date == hold_date, \
t.c.ds_uid.in_(uids))
return rst.all()
def __call__(self, user_ids, session):
"""
Parameters:
user_ids : list of mediawiki user ids to restrict computation to
session : sqlalchemy session open on a mediawiki database
Returns:
dictionary from user ids to: 1 if they're a rolling active editor, 0 if not
"""
number_of_edits = int(self.number_of_edits.data)
rolling_days = int(self.rolling_days.data)
end_date = self.end_date.data
start_date = end_date - timedelta(days=rolling_days)
rev_user = label('user_id', Revision.rev_user)
ar_user = label('user_id', Archive.ar_user)
count = label('count', func.count())
revisions = session.query(rev_user, count)\
.filter(between(Revision.rev_timestamp, start_date, end_date))\
.group_by(Revision.rev_user)
revisions = self.filter(revisions, user_ids, column=Revision.rev_user)
archived = session.query(ar_user, count)\
.filter(between(Archive.ar_timestamp, start_date, end_date))\
.group_by(Archive.ar_user)
archived = self.filter(archived, user_ids, column=Archive.ar_user)
bot_user_ids = session.query(MediawikiUserGroups.ug_user)\
.filter(MediawikiUserGroups.ug_group == 'bot')\
.subquery()
edits = revisions.union_all(archived).subquery()
edits_by_user = session.query(edits.c.user_id)\
.filter(edits.c.user_id.notin_(bot_user_ids))\
.group_by(edits.c.user_id)\
.having(func.SUM(edits.c.count) >= number_of_edits)
metric_results = {r[0]: {self.id: 1} for r in edits_by_user.all()}
if user_ids is None:
return metric_results
else:
return {
uid: metric_results.get(uid, self.default_result)
for uid in user_ids
}
def get_specific_day_amount(date, t_type):
"""
获取某个时间段内ds_trade_type=t_type的总金额
:param date: string, 日期
:param t_type: list, 交易类型
:return: list
"""
# db = database.connection('portfolio_sta')
# metadata = MetaData(bind=db)
t = Table('ds_order_pdate', metadata, autoload=True)
# Session = sessionmaker(bind=db)
# session = Session()
rst = session.query(func.SUM(t.c.ds_amount)).filter( \
t.c.ds_placed_date == date, \
t.c.ds_trade_type.in_(t_type))
# session.close()
return rst.all()
def get_specific_month_amount(s_date, e_date, t_type, uids):
"""
获取某个时间段内ds_trade_type=t_type的复购总金额
:param s_date: string, 开始日期
:param e_date: string, 结束日期
:param t_type: list, 交易类型
:return: list
"""
# db = database.connection('portfolio_sta')
# metadata = MetaData(bind=db)
t = Table('ds_order', metadata, autoload=True)
# Session = sessionmaker(bind=db)
# session = Session()
rst = session.query(func.SUM(t.c.ds_amount)).filter( \
t.c.ds_trade_date >= s_date, \
t.c.ds_trade_date <= e_date, \
t.c.ds_trade_type.in_(t_type), \
t.c.ds_uid.in_(uids))
# session.close()
return rst.all()
def get_workinhand_summary():
filter_data = {}
if 'filter' in request.args:
filter_data = json.loads(request.args.get('filter'))
query = g.s\
.query(
DO.OrgId
,DO.OrgName
,DPT.ProjectTypeDescription
,DPST.ProjectSubTypeDescription
,DED.EmployeeName.label("Director")
,func.SUM(FPF.AmountYetToInvoice).label("AmountYetToInvoice")
)\
.outerjoin(DD , FPF.DimDateKey == DD.DimDateKey)\
.outerjoin(DO , FPF.DimOrganisationKey == DO.DimOrganisationKey)\
.outerjoin(DED , FPF.DimEmployeeDirectorKey == DED.DimEmployeeKey)\
.outerjoin(DPT , FPF.DimProjectTypeKey == DPT.DimProjectTypeKey)\
.outerjoin(DPST , FPF.DimProjectSubTypeKey == DPST.DimProjectSubTypeKey)
if 'DimDateKey' in filter_data:
query = query.filter(DD.DK.in_(filter_data['DimDateKey']))
queryResult = query.group_by(DO.OrgId, DO.OrgName,
DPT.ProjectTypeDescription,
DPST.ProjectSubTypeDescription,
DED.EmployeeName).all()
summarys = []
for row in queryResult:
item = {}
item["OrgId"] = row[0]
item["OrgName"] = row[1]
item["Project Type"] = row[2]
item["Project Sub Type"] = row[3]
item["Director"] = row[4]
item["AmountYetToInvoice"] = row[5]
summarys.append(item)
return summarys
def aPagarHoje(self):
try:
# Abrindo Sessao
conecta = Conexao()
sessao = conecta.Session()
# Query
row = (sessao.query(func.COALESCE(
func.SUM(ContaAPagar.valor), 0).label('total'))
.filter(ContaAPagar.data_vencimento == date.today(), ContaAPagar.pagamento == 2))
# Salvando Resultado
for row in row:
self.valorAPagar = row.total
except IntegrityError as err:
print(err)
return self.valorAPagar
def _get_count(text, if_clause):
"""helper to create query below"""
return label(text, func.SUM(func.IF(if_clause, 1, 0)))
def get_hosps_status_counts(self, start_dt, end_dt, history, **kwargs):
self.start_dt = start_dt
self.end_dt = end_dt
self.history = history
self.hosp_status = safe_int(kwargs.get('hosp_status'))
self.flt_org_struct_id = safe_int(kwargs.get('org_struct_id'))
self.flt_client_id = safe_int(kwargs.get('client_id'))
self.flt_exec_person_id = safe_int(kwargs.get('exec_person_id'))
self.flt_external_id = kwargs.get('external_id')
statuses = set(kwargs.get('statuses') or HospStateStatus.get_values())
self.set_base_query()
self._join_latest_location()
self._join_location_org_structure()
self._join_moving_os_transfer()
self._join_movings_transfered_through()
self._join_leaved()
self.query = self.query.filter(
or_(self.MovingAction.id.isnot(None),
self.ReceivedAction.id.isnot(None)), ).with_entities()
if HospStateStatus.current[0] in statuses:
# кол-во текущих
self.query = self.query.add_column(
func.SUM(
func.IF(
func.IF(
self.MovingAction.id.isnot(None),
and_(
self.MovingAction_begDate < self.end_dt,
or_(self.MovingAction_endDate.is_(None),
self.end_dt <= self.MovingAction_endDate),
func.IF(
self.flt_org_struct_id is not None,
self.LocationOSfromMoving.id ==
self.flt_org_struct_id, 1)),
and_(
self.ReceivedAction_begDate < self.end_dt,
or_(self.ReceivedAction_endDate.is_(None),
self.end_dt <=
self.ReceivedAction_endDate),
func.IF(
self.flt_org_struct_id is not None,
self.LocationOSfromReceived.id ==
self.flt_org_struct_id, 1))), 1,
0)).label('count_current'))
if HospStateStatus.received[0] in statuses:
# кол-во поступивших
self.query = self.query.add_column(
func.SUM(
func.IF(
func.IF(
self.MovingAction.id.isnot(None),
and_(
self.MovingAction_begDate < self.end_dt,
self.start_dt <= self.MovingAction_begDate,
or_(self.MovingAction_endDate.is_(None),
self.start_dt <=
self.MovingAction_endDate),
func.IF(
self.flt_org_struct_id is not None,
self.LocationOSfromMoving.id ==
self.flt_org_struct_id, 1)),
and_(
self.ReceivedAction_begDate < self.end_dt,
self.start_dt <= self.ReceivedAction_begDate,
or_(
self.ReceivedAction_endDate.is_(None),
self.start_dt <=
self.ReceivedAction_endDate),
func.IF(
self.flt_org_struct_id is not None,
self.LocationOSfromReceived.id ==
self.flt_org_struct_id, 1))), 1,
0)).label('count_received'))
if HospStateStatus.transferred[0] in statuses:
# кол-во переведенных
self.query = self.query.add_column(
func.SUM(
func.IF(
self.q_movings_transfered_through.c.event_id.isnot(
None), 1, 0)).label('count_transferred'))
if HospStateStatus.leaved[0] in statuses:
# кол-во выписанных
self.query = self.query.add_column(
func.SUM(
func.IF(
and_(self.LeavedAction.id.isnot(None),
self.MovingOrgStructTransfer.id.is_(None),
self.MovingAction_begDate < self.end_dt,
self.MovingAction_endDate >= self.start_dt,
self.MovingAction_endDate < self.end_dt), 1,
0)).label('count_leaved'))
return self.get_one()
def observation(request_type):
request_datetime = datetime.strptime(
request.args.get("datetime", 0000000000), "%Y%m%d%H")
if request_type == 'station':
# TODO: filter germany
stations = db.session.query(Station.dwd_id, Station.name, Station.altitude,
Station.region.ST_AsGeoJSON().label('geometry'), Observation.date,
Observation.rainfall, Observation.temperature) \
.filter(Observation.station_id == Station.id, Observation.date == request_datetime) \
.all()
stations = [station._asdict() for station in stations]
feature_collection = to_feature_collection(stations)
return json.jsonify(feature_collection)
elif request_type == "district":
q = db.session.query(District.id,
District.name,
District.admin_level,
District.geometry.ST_AsGeoJSON().label("geometry"),
(func1.SUM(Observation.temperature * ContribDistrict.area)
/ func1.SUM(ContribDistrict.area))
.label("temperature"),
(func1.SUM(Observation.rainfall * ContribDistrict.area)
/ func1.SUM(ContribDistrict.area))
.label("rainfall"),
)\
.filter(Observation.date == request_datetime,
#District.geometry.ST_Intersects('SRID=4326;POINT(%s %s)' % (lon, lat)),
#State.geometry.ST_Intersects(Station.geometry),
District.id == ContribDistrict.district_id,
Station.id == ContribDistrict.station_id,
Station.id == Observation.station_id,
Observation.rainfall != -999,
Observation.temperature != -999
)\
.group_by(District.id)
districts = q.all()
districts = [district._asdict() for district in districts]
feature_collection = to_feature_collection(districts)
return json.jsonify(feature_collection)
elif request_type == "state":
q = db.session.query(State.id,
State.name,
State.admin_level,
State.geometry.ST_AsGeoJSON().label("geometry"),
(func1.SUM(Observation.temperature
* ContribState.area)
/ func1.SUM(ContribState.area))
.label("temperature"),
(func1.SUM(Observation.rainfall
* ContribState.area)
/ func1.SUM(ContribState.area))
.label("rainfall"),
)\
.filter(Observation.date == request_datetime,
#State.geometry.ST_Intersects('SRID=4326;POINT(%s %s)' % (lon, lat)),
#State.geometry.ST_Intersects(Station.geometry),
State.id == ContribState.state_id,
Station.id == ContribState.station_id,
Station.id == Observation.station_id,
Observation.rainfall != -999,
Observation.temperature != -999
)\
.group_by(State.id)
states = q.all()
states = [state._asdict() for state in states]
feature_collection = to_feature_collection(states)
return json.jsonify(feature_collection)
abort(404)
def info(request_type):
""" example queries:
http://127.0.0.1:5000/info/station.json?lon=8.237&lat=52.9335&forecast=False&datetime=2014120100
http://127.0.0.1:5000/info/district.json?lon=10.237&lat=52.9335&forecast=False&datetime=2014120100
http://127.0.0.1:5000/info/district.json?lon=9.237&lat=52.9335&forecast=False&datetime=2014120100 (example for no stations in district)
http://127.0.0.1:5000/info/state.json?lon=13.237&lat=52.435&forecast=False&datetime=201420100
"""
# todo handle dates without observations properly
lat = float(request.args.get("lat"))
lon = float(request.args.get("lon"))
forecast = request.args.get("forecast") in ['true', 'True']
# datetime format JJJJMMTTHH (Observation: JJJJMMMTT00)
request_datetime = datetime.strptime(
request.args.get("datetime", 0000000000), "%Y%m%d%H")
hours = request.args.get(
"hours",
None) # the number of hours into the future a forecast was made
# If the data isn't a forecast it should ignore the hours of the datetime (set to 00).
# TODO: discuss if this should handled at the client
if not forecast:
request_datetime = request_datetime.replace(hour=0, minute=0)
if request_type == 'station' and not forecast:
station = db.session.query(Station.dwd_id,
Station.name,
Station.altitude,
Station.geometry.ST_AsGeoJSON().label('geometry'),
Observation.date,
Observation.rainfall,
Observation.temperature) \
.filter(Observation.station_id == Station.id,
Observation.date == request_datetime,
func.ST_Intersects(Station.region, 'SRID=4326;POINT(%s %s)' % (lon, lat))) \
.first()
if station == None:
abort(404)
feature = to_feature(station._asdict())
return json.jsonify(feature)
elif request_type == "district":
q = db.session.query(District.id,
District.name,
District.admin_level,
District.geometry.ST_AsGeoJSON().label("geometry"),
(func1.SUM(Observation.temperature * ContribDistrict.area)
/ func1.SUM(ContribDistrict.area))
.label("mean_temperature"),
(func1.SUM(Observation.rainfall * ContribDistrict.area)
/ func1.SUM(ContribDistrict.area))
.label("mean_rainfall"),
)\
.filter(Observation.date == request_datetime,
District.geometry.ST_Intersects('SRID=4326;POINT(%s %s)' % (lon, lat)),
#State.geometry.ST_Intersects(Station.geometry),
District.id == ContribDistrict.district_id,
Station.id == ContribDistrict.station_id,
Station.id == Observation.station_id,
Observation.rainfall != -999,
Observation.temperature != -999
)\
.group_by(District.id)
response = q.first()
response = to_feature(response._asdict())
return json.jsonify(response)
elif request_type == "state":
q = db.session.query(State.id,
State.name,
State.admin_level,
State.geometry.ST_AsGeoJSON().label("geometry"),
(func1.SUM(Observation.temperature
* ContribState.area)
/ func1.SUM(ContribState.area))
.label("mean_temperature"),
(func1.SUM(Observation.rainfall
* ContribState.area)
/ func1.SUM(ContribState.area))
.label("mean_rainfall"),
)\
.filter(Observation.date == request_datetime,
State.geometry.ST_Intersects('SRID=4326;POINT(%s %s)' % (lon, lat)),
#State.geometry.ST_Intersects(Station.geometry),
State.id == ContribState.state_id,
Station.id == ContribState.station_id,
Station.id == Observation.station_id,
Observation.rainfall != -999,
Observation.temperature != -999
)\
.group_by(State.id)
response = q.first()
response = to_feature(response._asdict())
return json.jsonify(response)
else:
abort(404)
def compute_features_from_osm(config):
osm_tables = config['OSM']
bounding_box = WKTElement(config['BOUNDING_BOX'], srid=4326)
grid_obj = config['GRID_OBJ']
geo_feature_obj = config['GEO_FEATURE_OBJ']
try:
for feature_name, osm_table in osm_tables.items():
geo_feature_type = osm_table.wkb_geometry.type.geometry_type
cropped_osm = crop_osm(
osm_table,
bounding_box) # crop the OSM data with a bounding box
sub_query = session.query(grid_obj.gid, cropped_osm.c.fclass,
func.ST_GeogFromWKB(
func.ST_Intersection(grid_obj.geom, cropped_osm.c.wkb_geometry))
.label('intersection')) \
.filter(func.ST_Intersects(grid_obj.geom, cropped_osm.c.wkb_geometry)).subquery()
results = []
if geo_feature_type == 'MULTIPOLYGON':
results = session.query(sub_query.c.gid.label('gid'),
sub_query.c.fclass.label('feature_type'),
literal(feature_name).label('geo_feature'),
func.SUM(func.ST_AREA(sub_query.c.intersection)).label('value'),
literal('area').label('measurement')) \
.group_by(sub_query.c.gid, sub_query.c.fclass).all()
elif geo_feature_type == 'MULTILINESTRING':
results = session.query(sub_query.c.gid.label('gid'),
sub_query.c.fclass.label('feature_type'),
literal(feature_name).label('geo_feature'),
func.SUM(func.ST_LENGTH(sub_query.c.intersection)).label('value'),
literal('length').label('measurement')) \
.group_by(sub_query.c.gid, sub_query.c.fclass).all()
elif geo_feature_type == 'POINT':
results = session.query(sub_query.c.gid.label('gid'),
sub_query.c.fclass.label('feature_type'),
literal(feature_name).label('geo_feature'),
func.COUNT(sub_query.c.intersection).label('value'),
literal('count').label('measurement')) \
.group_by(sub_query.c.gid, sub_query.c.fclass).all()
else:
pass
obj_results = []
for res in results:
obj_results.append(
geo_feature_obj(gid=res[0],
feature_type=res[1],
geo_feature=res[2],
value=res[3],
measurement=res[4]))
# session.add_all(obj_results)
# session.commit()
print('{} has finished'.format(feature_name))
return
except Exception as e:
print(e)
exit(-1)
def __call__(self, user_ids, session):
"""
Parameters:
user_ids : list of mediawiki user ids to restrict computation to
session : sqlalchemy session open on a mediawiki database
Returns:
{
user id: 1 if they're a rolling surviving new active editor, 0 otherwise
for all cohort users, or all users that have edits in the time period
}
"""
number_of_edits = int(self.number_of_edits.data)
rolling_days = int(self.rolling_days.data)
end_date = self.end_date.data
mid_date = end_date - timedelta(days=rolling_days)
start_date = end_date - timedelta(days=rolling_days * 2)
newly_registered = session.query(Logging.log_user) \
.filter(Logging.log_type == 'newusers') \
.filter(Logging.log_action == 'create') \
.filter(between(Logging.log_timestamp, start_date, mid_date))
# subquery to select only the users registered between start and mid date
filtered_new = self.filter(newly_registered,
user_ids,
column=Logging.log_user).subquery()
rev_user = label('user_id', Revision.rev_user)
ar_user = label('user_id', Archive.ar_user)
# count edits between start and mid date, for Revision
rev_count_one = _get_count('count_one',
Revision.rev_timestamp <= mid_date)
# count edits between start and mid date, for Archive
ar_count_one = _get_count('count_one',
Archive.ar_timestamp <= mid_date)
# count edits between mid and end date, for Revision
rev_count_two = _get_count('count_two',
Revision.rev_timestamp > mid_date)
# count edits between mid and end date, for Archive
ar_count_two = _get_count('count_two', Archive.ar_timestamp > mid_date)
# get both counts by user for Revision
revisions = session.query(rev_user, rev_count_one, rev_count_two)\
.filter(between(Revision.rev_timestamp, start_date, end_date))\
.filter(Revision.rev_user.in_(filtered_new))\
.group_by(Revision.rev_user)
# get both counts by user for Archive
archived = session.query(ar_user, ar_count_one, ar_count_two)\
.filter(between(Archive.ar_timestamp, start_date, end_date))\
.filter(Archive.ar_user.in_(filtered_new))\
.group_by(Archive.ar_user)
bot_user_ids = session.query(MediawikiUserGroups.ug_user)\
.filter(MediawikiUserGroups.ug_group == 'bot')\
.subquery()
# For each user, with both counts from both tables,
# sum the count_one values together, check it's >= number_of_edits
# sum the count_two values together, check it's >= number_of_edits
new_edits = revisions.union_all(archived).subquery()
new_edits_by_user = session.query(new_edits.c.user_id)\
.filter(new_edits.c.user_id.notin_(bot_user_ids))\
.group_by(new_edits.c.user_id)\
.having(and_(
func.SUM(new_edits.c.count_one) >= number_of_edits,
func.SUM(new_edits.c.count_two) >= number_of_edits,
))
metric_results = {r[0]: {self.id: 1} for r in new_edits_by_user.all()}
if user_ids is None:
return metric_results
else:
return {
uid: metric_results.get(uid, self.default_result)
for uid in user_ids
}
