def selectables(cls, bag, agg_spec):
""" Create a list of statements from spec
:type bag: mongosql.bag.ModelPropertyBags
:rtype: list[sqlalchemy.sql.elements.ColumnElement]
"""
# TODO: calculation expressions for selection: http://docs.mongodb.org/manual/meta/aggregation-quick-reference/
selectables = []
for comp_field, comp_expression in agg_spec.items():
# Column reference
if isinstance(comp_expression, basestring):
selectables.append(bag.columns[comp_expression].label(comp_field))
continue
# Computed expression
assert isinstance(comp_expression, dict), 'Aggregate: Expression should be either a column name, or an object'
assert len(comp_expression) == 1, 'Aggregate: expression can only contain a single operator'
operator, expression = comp_expression.popitem()
# Expression statement
if isinstance(expression, int) and operator == '$sum':
# Special case for count
expression_stmt = expression
elif isinstance(expression, basestring):
# Column name
expression_stmt = bag.columns[expression]
# Json column?
if bag.columns.is_column_json(expression):
# PostgreSQL always returns text values from it, and for aggregation we usually need numbers :)
expression_stmt = cast(expression_stmt, Float)
elif isinstance(expression, dict):
# Boolean expression
expression_stmt = MongoCriteria.statement(bag, expression)
# Need to cast it to int
expression_stmt = cast(expression_stmt, Integer)
else:
raise AssertionError('Aggregate: expression should be either a column name, or an object')
# Operator
if operator == '$max':
comp_stmt = func.max(expression_stmt)
elif operator == '$min':
comp_stmt = func.min(expression_stmt)
elif operator == '$avg':
comp_stmt = func.avg(expression_stmt)
elif operator == '$sum':
if isinstance(expression_stmt, int):
# Special case for count
comp_stmt = func.count()
if expression_stmt != 1:
comp_stmt *= expression_stmt
else:
comp_stmt = func.sum(expression_stmt)
else:
raise AssertionError('Aggregate: unsupported operator "{}"'.format(operator))
# Append
selectables.append(comp_stmt.label(comp_field))
return selectables
def get_results(cls, offset_id=0, limit=1000, project_id=None):
with new_session() as session:
if int(offset_id) == 0:
offset_id = session.query(func.max(Result.id)).scalar() or 0
rows = session.query(
Result.id, Result.project_id, Result.shortcode,
Result.url, Result.encoding, Result.datetime
) \
.filter(Result.id <= int(offset_id))
if project_id is not None and project_id != 'None':
rows = rows.filter(Result.project_id == project_id)
alphabet = Project.get_plain(project_id).alphabet
else:
alphabet = None
rows = rows.order_by(Result.id.desc()).limit(int(limit))
for row in rows:
ans = {
'id': row[0],
'project_id': row[1],
'shortcode': row[2],
'url': row[3],
'encoding': row[4],
'datetime': row[5]
}
if alphabet:
ans['seq_num'] = str_to_int(row[2], alphabet)
yield ans
def _build_query(self, table, filter_values):
having = []
filter_cols = []
external_cols = _get_grouping(filter_values)
for fil in self.filters:
if isinstance(fil, ANDFilter):
filter_cols.append(fil.filters[0].column_name)
having.append(fil)
elif isinstance(fil, RawFilter):
having.append(fil)
elif fil.column_name not in ['group', 'gender', 'group_leadership', 'disaggregate_by',
'table_card_group_by']:
if fil.column_name not in external_cols and fil.column_name != 'maxmin':
filter_cols.append(fil.column_name)
having.append(fil)
group_having = ''
having_group_by = []
if ('disaggregate_by' in filter_values and filter_values['disaggregate_by'] == 'group') or \
(filter_values.get('table_card_group_by') == 'group_leadership'):
having_group_by.append('group_leadership')
elif 'group_leadership' in filter_values and filter_values['group_leadership']:
group_having = "(MAX(CAST(gender as int4)) + MIN(CAST(gender as int4))) " \
"= :group_leadership and group_leadership=\'Y\'"
having_group_by.append('group_leadership')
filter_cols.append('group_leadership')
elif 'gender' in filter_values and filter_values['gender']:
group_having = "(MAX(CAST(gender as int4)) + MIN(CAST(gender as int4))) = :gender"
table_card_group = []
if 'group_name' in self.group_by:
table_card_group.append('group_name')
s1 = alias(select([table.c.doc_id, table.c.group_case_id, table.c.group_name, table.c.group_id,
(sqlalchemy.func.max(table.c.prop_value) +
sqlalchemy.func.min(table.c.prop_value)).label('maxmin')] + filter_cols +
external_cols, from_obj=table,
group_by=([table.c.doc_id, table.c.group_case_id, table.c.group_name, table.c.group_id] +
filter_cols + external_cols)), name='x')
s2 = alias(
select(
[table.c.group_case_id,
sqlalchemy.cast(
cast(func.max(table.c.gender), Integer) + cast(func.min(table.c.gender), Integer), VARCHAR
).label('gender')] + table_card_group,
from_obj=table,
group_by=[table.c.group_case_id] + table_card_group + having_group_by, having=group_having
), name='y'
)
group_by = list(self.group_by)
if 'group_case_id' in group_by:
group_by[group_by.index('group_case_id')] = s1.c.group_case_id
group_by[group_by.index('group_name')] = s1.c.group_name
return select(
[sqlalchemy.func.count(s1.c.doc_id).label(self.key)] + group_by,
group_by=[s1.c.maxmin] + filter_cols + group_by,
having=AND(having).build_expression(s1),
from_obj=join(s1, s2, s1.c.group_case_id == s2.c.group_case_id)
).params(filter_values)
def generate_new_address(index=None):
if index is None:
result = db.session.query(func.max(Guest.address_index).label("max_index")).one()
if result and result.max_index:
index = result.max_index + 1
else:
index = 0
return receiving_account.subkey(RECEIVING).subkey(index).address()
def get_execution_tables():
"""
Return all the IODbPut objects found in model IODbPut.
:return: ResultSet IODbPut objects
"""
session = SQLManager.instance().get_session()
execution_id = session.query(func.max(ToolWrapper.execution_id))
return session.query(IODbPut).filter(IODbPut.rule_id == ToolWrapper.id).filter(ToolWrapper.execution_id == execution_id).all()
def get_set_toolwrappers():
"""
Ask the database for toolwrappers of the current execution.
The current execution is defined as the one with the highest id (it is auto_incrementing)
:return: Set([ToolWrapper]) the set of toolwrappers of the current execution.
"""
session = SQLManager.instance().get_session()
set_toolwrappers = set()
try:
# query asking the db for the highest execution id
execution_id = session.query(func.max(ToolWrapper.execution_id))
Logger.instance().debug("Getting toolwrappers of the current execution. id = " + str(execution_id.one()[0]))
set_toolwrappers = set(session.query(ToolWrapper).filter(ToolWrapper.execution_id == execution_id).all())
except NoResultFound as e:
raise e
return set_toolwrappers
def dag_next_execution(args):
"""
Returns the next execution datetime of a DAG at the command line.
>>> airflow dags next-execution tutorial
2018-08-31 10:38:00
"""
dag = get_dag(args.subdir, args.dag_id)
if dag.get_is_paused():
print("[INFO] Please be reminded this DAG is PAUSED now.", file=sys.stderr)
with create_session() as session:
max_date_subq = (
session.query(func.max(DagRun.execution_date).label("max_date"))
.filter(DagRun.dag_id == dag.dag_id)
.subquery()
)
max_date_run: Optional[DagRun] = (
session.query(DagRun)
.filter(DagRun.dag_id == dag.dag_id, DagRun.execution_date == max_date_subq.c.max_date)
.one_or_none()
)
if max_date_run is None:
print("[WARN] Only applicable when there is execution record found for the DAG.", file=sys.stderr)
print(None)
return
next_info = dag.next_dagrun_info(dag.get_run_data_interval(max_date_run), restricted=False)
if next_info is None:
print(
"[WARN] No following schedule can be found. "
"This DAG may have schedule interval '@once' or `None`.",
file=sys.stderr,
)
print(None)
return
print(next_info.logical_date.isoformat())
for _ in range(1, args.num_executions):
next_info = dag.next_dagrun_info(next_info.data_interval, restricted=False)
print(next_info.logical_date.isoformat())
def getLatestPappVersionInfos(name=None):
from peek_server.storage import dbConn
session = dbConn.ormSession
maxGroupedIds = (session.query(func.max(
PeekAppInfo.id).label('maxId')).group_by(
PeekAppInfo.name).subquery('maxGroupedIds'))
qry = (session.query(PeekAppInfo).filter(
PeekAppInfo.id == maxGroupedIds.c.maxId).order_by(
PeekAppInfo.name, PeekAppInfo.id))
if name:
qry = qry.filter(PeekAppInfo.name == name)
tuples = qry.all()
session.expunge_all()
return tuples
def save_with_next_available_id(self,
req: "CamcopsRequest",
device_id: int,
era: str = ERA_NOW) -> None:
"""
Save a record with the next available client pk in sequence.
This is of use when creating patients and ID numbers on the server
to ensure uniqueness, or when fixing up a missing ID number for
a patient created on a device.
"""
cls = self.__class__
saved_ok = False
# MySql doesn't support "select for update" so we have to keep
# trying the next available ID and checking for an integrity
# error in case another user has grabbed it by the time we have
# committed
# noinspection PyProtectedMember
last_id = (
req.dbsession
# func.max(cls.id) + 1 here will do the right thing for
# backends that support select for update (maybe not for no rows)
.query(func.max(
cls.id)).filter(cls._device_id == device_id).filter(
cls._era == era).scalar()) or 0
next_id = last_id + 1
while not saved_ok:
self.id = next_id
req.dbsession.add(self)
try:
req.dbsession.flush()
saved_ok = True
except IntegrityError:
req.dbsession.rollback()
next_id += 1
def compile(self):
# Json column?
if self.is_column_json:
# PostgreSQL always returns text values from it, and for aggregation we usually need numbers :)
column = cast(self.column, Float)
else:
# Simply use
column = self.column
# Now, handle the operator, and apply it to the expression
if self.operator == '$max':
stmt = func.max(column)
elif self.operator == '$min':
stmt = func.min(column)
elif self.operator == '$avg':
stmt = func.avg(column)
elif self.operator == '$sum':
stmt = func.sum(column)
else:
raise InvalidQueryError(
'Aggregate: unsupported operator "{}"'.format(self.operator))
return self.labeled_expression(stmt)
def get_set_toolwrappers():
"""
Ask the database for toolwrappers of the current execution.
The current execution is defined as the one with the highest id (it is auto_incrementing)
:return: Set([ToolWrapper]) the set of toolwrappers of the current execution.
"""
session = SQLManager.instance().get_session()
set_toolwrappers = set()
try:
# query asking the db for the highest execution id
execution_id = session.query(func.max(ToolWrapper.execution_id))
Logger.instance().debug(
"Getting toolwrappers of the current execution. id = " +
str(execution_id.one()[0]))
set_toolwrappers = set(
session.query(ToolWrapper).filter(
ToolWrapper.execution_id == execution_id).all())
except NoResultFound as e:
raise e
return set_toolwrappers
def save_with_next_available_id(obj: Base, dbsession: SqlASession) -> None:
"""
Deliberately copied from cc_db.py and maintained separately
Save a record with the next available client pk in sequence.
"""
cls = obj.__class__
saved_ok = False
# MySql doesn't support "select for update" so we have to keep
# trying the next available ID and checking for an integrity
# error in case another user has grabbed it by the time we have
# committed
# noinspection PyProtectedMember
last_id = (
dbsession
# func.max(cls.id) + 1 here will do the right thing for
# backends that support select for update (maybe not for no rows)
.query(func.max(cls.id))
.filter(cls._device_id == obj._device_id)
.filter(cls._era == ERA_NOW)
.scalar()
) or 0
next_id = last_id + 1
while not saved_ok:
obj.id = next_id
dbsession.add(obj)
try:
dbsession.flush()
saved_ok = True
except IntegrityError:
dbsession.rollback()
next_id += 1
async def get_chats(
pagination: Pagination = Depends(get_pagination),
current_user: UserResponse = Depends(get_current_verified_user),
db: Session = Depends(get_db),
):
"""Get a list of all chats the user has."""
chats_with_message = (db.query(
models.Chat,
models.Connector,
models.Message,
func.max(models.Message.sent_datetime),
).join(models.Connector).join(models.Message).group_by(
models.Message.chat_id).filter(
models.Connector.user_id == current_user.user_id).order_by(
desc_op(models.Message.sent_datetime)).limit(
pagination.count).offset(pagination.page *
pagination.count).all())
if not chats_with_message:
return []
return [
ChatResponse(
chat_id=chat_with_message[0].chat_id,
connector_id=chat_with_message[0].connector_id,
name=chat_with_message[0].name,
is_muted=chat_with_message[0].is_muted,
is_archived=chat_with_message[0].is_archived,
pin_position=chat_with_message[0].pin_position,
last_message=schemas.MessageResponse(
message_id=chat_with_message[2].message_id,
contact_id=chat_with_message[2].contact_id,
message=json.loads(chat_with_message[2].message),
sent_datetime=chat_with_message[2].sent_datetime,
) if chat_with_message[2] else None,
) for chat_with_message in chats_with_message
]
def user_locked_out_until(cls, req: "CamcopsRequest",
username: str) -> Optional[Pendulum]:
"""
When is the user locked out until?
Args:
req: :class:`camcops_server.cc_modules.cc_request.CamcopsRequest`
username: the user's username
Returns:
Pendulum datetime in local timezone (or ``None`` if not
locked out).
"""
dbsession = req.dbsession
now = req.now_utc
locked_until_utc = dbsession.query(func.max(cls.locked_until))\
.filter(cls.username == username)\
.filter(cls.locked_until > now)\
.scalar() # type: Optional[Pendulum]
# ... NOT first(), which returns (result,); we want just result
if not locked_until_utc:
return None
return convert_datetime_to_local(locked_until_utc)
def create_demo_database( nb_orders=50):
mainlog.setLevel(logging.DEBUG)
create_blank_database(configuration.get("Database", "admin_url"),
configuration.get("Database", "url"))
dao = DAO()
dao.set_session(session())
random.seed(42)
employees_texts = ["Alfred Hitchcok", "Rocky Balboa", "Donald Knuth", "Ray Parker Junior", "Henry Mancini", "Nivek Ogre",
"Johnny Cash", "Sarah Connor"]
nb_employees = len(employees_texts)
for name in employees_texts:
e = dao.employee_dao.make(name)
e.login = (name.split(' ')[0][0:2] + name.split(' ')[1][0:2]).lower()
e.set_encrypted_password(e.login)
e.roles = RoleType.symbols()
dao.employee_dao.save(e)
for name in customers_texts:
customer = dao.customer_dao.make(name)
customer.address1 = u"Square Niklaus Wirth" + chr(233)
customer.country = u"Pakistan" + chr(233)
customer.phone = u"+494 0412 32 32 6654"
customer.email = u"*****@*****.**"
dao.customer_dao.save(customer)
for name, short_name in operations_texts:
opdef_op = dao.operation_definition_dao.make()
opdef_op.short_id = short_name
opdef_op.description = name
opdef_op.imputable = True
opdef_op.on_order = False
opdef_op.on_operation = True
opdef_op.XXXcost = random.random() * 50 + 50.0
period = OperationDefinitionPeriod()
period.start_date = date(2010, 1, 1)
period.cost = random.randint(30, 60)
dao.operation_definition_dao.add_period(period, opdef_op)
dao.operation_definition_dao.save(opdef_op)
customers = session().query(Customer).all()
for i in range(nb_orders):
order = dao.order_dao.make(u"Test order", customer)
order.state = OrderStatusType.preorder_definition # OrderStatusType.order_ready_for_production
order.customer = customers[random.randint(0, len(customers) - 1)]
order.creation_date = (world_begin + timedelta(days=random.randint(0, max_past_days))).date()
dao.order_dao.save(order)
for order in session().query(Order).all():
position = 1
for i in range(random.randint(3, 10)):
order_part = dao.order_part_dao.make(order)
texts = ["For part {}".format(random.randint(100, 999)),
"As plan {}".format(str(uuid.uuid4()).upper()[0:6]),
"Customer ref #{}".format(str(uuid.uuid4()).upper()[0:6]),
"#1 Bare Bright Copper Wire",
"#1 Copper Tubing",
"#1 Flashing Copper",
"#2 Copper Tubing",
"#2/3 Mix Copper",
"#3 Copper with Tar",
"#3 Roofing Copper",
"17-4 Stainless Steel",
"300 Series Stainless Steel",
"400 Series Stainless Steel",
"500/750 Insulated Cable",
"ACR",
"ACR Ends",
"AL Extrusion",
"AL Thermopane",
"AL/ Copper Rads w/Iron",
"AL/Copper Cutoffs",
"Alternators",
"Aluminum #3",
"Aluminum 6061",
"Aluminum 6063",
"Aluminum Boat",
"Aluminum Breakage",
"Aluminum Bumpers",
"Aluminum Cans",
"Aluminum Clips",
"Aluminum Copper Coil",
"Aluminum Copper Radiators",
"Aluminum Diesel Tank",
"Aluminum Engine Block",
"Aluminum Litho",
"Aluminum Radiators",
"Aluminum Rims",
"Aluminum Scrap",
"Aluminum Siding",
"Aluminum Thermo-Pane/Break",
"Aluminum Transformers",
"Aluminum Turnings",
"Aluminum Wire w/Steel",
"Ballasts",
"Bare Bright Copper",
"Brass Hair Wire",
"Brass Heater Cores",
"Brass Pipe",
"Brass Radiators",
"Brass Scrap",
"Brass Shells",
"Brass Turnings",
"Bronze",
"Bronze Turnings",
"Burnt Copper",
"Car/Truck Batteries",
"Carbide",
"Cast Aluminum",
"Catalytic Converters",
"CATV Wire",
"Christmas Lights",
"Circuit Breakers",
"Clean ACR",
"Clean AL Wire",
"Clean AL/Copper Fin",
"Clean Brass Radiators",
"Clean Brass Turnings",
"Clean Roofing Copper",
"Cobalt",
"Communications Wire",
"Composition Scrap",
"Compressors",
"Copper Scrap",
"Copper Transformers",
"Copper Turnings",
"Copper Yokes",
"Die Cast",
"Dirty ACR",
"Dirty AL Extrusion",
"Dirty AL Radiators",
"Dirty AL/Copper Fin",
"Dirty Aluminum Turnings",
"Dirty Brass",
"Dirty Brass Radiators",
"Dirty Roofing Copper",
"Double Insulated Cable",
"EC Wire",
"Electric Motors (Aluminum)",
"Electric Motors (Copper)",
"Elevator Wire",
"Enameled Copper",
"F 75",
"Fire Wire",
"Forktruck Battery",
"FSX 414",
"Fuses",
"Gold",
"Hastelloy Solids",
"Hastelloy Turnings",
"Heliax Wire",
"High Speed Steel",
"Housewire",
"Inconel",
"Inconel 792",
"Inconel 800",
"Inconel 825",
"Insulated Aluminum Wire",
"Insulated Copper Cable",
"Insulated Copper Wire",
"Insulated Steel BX",
"Invar",
"Junkshop Extrusion",
"Kovar",
"Lead",
"Lead Batteries",
"Lead Coated Copper",
"Lead Shot",
"Lead Wheel Weights",
"Light Copper",
"MarM247",
"Meatballs (Electric Motors)",
"Monel",
"Ni-Cad Batteries",
"Nickel",
"Non Magnetic Stainless Steel",
"Old Sheet Aluminum",
"Painted Aluminum",
"Pewter",
"Platinum",
"Plumbers Brass",
"Prepared Aluminum",
"Red Brass",
"Refined Rebrass & Copper",
"Rod Brass",
"Rod Brass Turnings",
"Romex® Wire",
"Sealed Units",
"Semi-Red Brass",
"Sheet Aluminum",
"Silver",
"Silver Plated Copper",
"Solid Core Heliax",
"Stainless Steel",
"Stainless Steel Breakage",
"Stainless Steel Heatsinks",
"Stainless Steel Kegs",
"Stainless Steel Sinks",
"Stainless Turnings",
"Starters",
"Steel BX",
"Steel Case Batteries",
"THHN Wire",
"Tin Babbit",
"Tin Coated Copper",
"Tin Insulated Copper Wire",
"Unclean Brass Radiators",
"Wire Scrap",
"Wiring Harness",
"Yellow Brass",
"Zinc",
"Zorba",
"#1 Heavy Melting Steel",
"#1 HMS",
"#1 Prepared",
"#1 Steel",
"#2 Heavy Melting Steel",
"#2 HMS",
"#2 Prepared",
"Automobiles",
"Busheling",
"Car w/Tires",
"Cast Iron",
"Complete Car",
"Crushed Cars",
"Dishwashers",
"Dry Automobile",
"Dryers",
"Incomplete Car",
"Light Iron",
"Machine Shop Turning/Iron Borings",
"Plate & Structural Steel",
"Refrigerators",
"Scrap Iron",
"Sheet Iron",
"Shreddable Steel",
"Steel Shavings",
"Tin",
"Uncleaned Auto Cast",
"Unprepared Cast Iron",
"Unprepared HMS",
"Unprepared P&S",
"Washing Machines",
"Water Heaters",
"Wet Automobile",
"Back Panels",
"Backup Batteries",
"Cellphones",
"Computer Wire",
"CPU Chips",
"CRT",
"Empty PC Servers",
"Hard Drive Boards",
"Hard Drives",
"Hard Drives without Boards",
"Ink Cartridges",
"Keyboards",
"Laptops",
"LCD Monitors (not working)",
"LCD Monitors (working)",
"Low Grade Boards",
"Mainframes",
"Memory",
"Mice",
"Motherboards",
"Non-Green PC Board",
"PC Board with Steel",
"PC Boards",
"PC Tower",
"Power Supplies",
"Printers/Fax Machines",
"Servers",
"Speakers",
"Telecom Equipment"]
order_part.description = random.choice(texts)
order_part.position = position
order_part.priority = random.randint(1, 5)
position += 1
order_part.qty = random.randint(4, 4+10)
order_part.sell_price = random.randint(100, 200)
dao.order_part_dao.save(order_part)
pf = dao.production_file_dao.make()
pf.order_part = order_part
order_part.production_file = [pf]
session().add(pf)
session().flush()
operation_definitions = session().query(OperationDefinition).all()
for pf in session().query(ProductionFile).all():
for i in range(random.randint(3, 10)):
operation = dao.operation_dao.make()
operation.production_file = pf
begin = random.randint(0, len(lorem) - 5)
end = begin + min(6, random.randint(begin, len(lorem) - 1))
operation.description = " ".join(lorem[begin:end])
operation.operation_model = random.choice(operation_definitions)
operation.planned_hours = float(random.randint(1, 20)) / 5 # per unit
session().add(operation)
session().commit()
for order in session().query(Order).all():
dao.order_dao.recompute_position_labels(order)
session().commit()
tasks = []
for operation in session().query(Operation).all():
task = TaskOnOperation()
task.operation_id = operation.operation_id
session().add(task)
session().flush()
tasks.append(task)
order_schedules = dict()
for order in session().query(Order).all():
mainlog.info("populating order")
order_start = order.creation_date
central_clock.set_now_function(lambda: datetime( order_start.year, order_start.month, order_start.day))
if True or random.randint(0,10) > 1:
# a production order
order_end = order_start + timedelta(days=(30 + order.order_id % 20))
mainlog.debug("Interval {} {}".format( order_start, order_end))
dao.order_dao.change_order_state(order.order_id, OrderStatusType.preorder_definition)
dao.order_dao.change_order_state(order.order_id, OrderStatusType.order_ready_for_production)
order_schedules[order.order_id] = (order_start, order_end)
else:
# a preorder
dao.order_dao.change_order_state(order.order_id, OrderStatusType.preorder_definition)
dao.order_dao.change_order_state(order.order_id, OrderStatusType.preorder_sent)
mainlog.info("There are {} tasks".format(len(tasks)))
mainlog.info("There are {} order scheduled for work".format(len(order_schedules)))
# _make_tar(TaskActionReportType.start_task, datetime.now(), e, task)
employees = session().query(Employee).all()
# Buld the list of tasks available on each day
tasks_on_day = dict()
for task in tasks:
order = task.operation.production_file.order_part.order
if order.order_id in order_schedules:
order_start, order_end = order_schedules[order.order_id]
for d in daterange( order_start, order_end):
if d not in tasks_on_day:
tasks_on_day[d] = []
tasks_on_day[d].append(task)
for day in range( int(max_past_days)):
d = world_begin + timedelta(days=2 + day)
d = date( d.year, d.month, d.day)
if d.weekday() not in (5, 6) and d in tasks_on_day:
employees_with_work = []
central_clock.set_now_function(lambda: datetime(d.year,d.month,d.day))
# tasks we can work on
workable_tasks = tasks_on_day[d]
mainlog.debug("{} workable tasks".format(len(workable_tasks)))
if workable_tasks:
# Now put actual work on those tasks
for employee in employees:
# Each employee may or may not work
if random.randint(0,10) > 2:
total_duration = 0
while total_duration < 8:
task = random.choice(workable_tasks)
duration = float(random.randint(1,4)) + float(random.randint(0,4)) / 4.0
tt = _make_timetrack( task.task_id, employee.employee_id,
d,
duration)
session().add(tt)
total_duration += duration
dts = DayTimeSynthesis()
dts.employee_id = tt.employee_id
dts.day = d
dts.presence_time = total_duration
session().add( dts)
else:
from koi.people_admin.people_admin_mapping import DayEventType, DayEvent
from koi.people_admin.people_admin_service import people_admin_service
de = DayEvent()
de.employee_id = employee.employee_id
de.event_type = random.choice(DayEventType.symbols())
people_admin_service.set_event_on_days( de, [ (d, 1) ])
for i in range(3):
for order in session().query(Order).filter(Order.state == OrderStatusType.order_ready_for_production).all():
parts_ids_quantities = dict()
for order_part in order.parts:
mainlog.debug("qex = {} / {}".format(order_part.tex2, order_part.qty))
if order_part.tex2 < order_part.qty and order_part.total_hours:
parts_ids_quantities[order_part.order_part_id] = random.randint(1, order_part.qty - order_part.tex2)
if parts_ids_quantities:
mainlog.debug("Creating delivery slip")
order_start, order_end = order_schedules[order.order_id]
for dsp in session().query(DeliverySlipPart).filter(DeliverySlipPart.order_part_id.in_(parts_ids_quantities.keys())).all():
if dsp.delivery_slip.creation > datetime( order_start.year, order_start.month, order_start.day):
order_start = dsp.delivery_slip.creation.date()
mainlog.debug("{} {}".format( type(order_end), type(order_start)))
mainlog.debug("{} {}".format( order_start, order_end))
days_between = (order_end - order_start).days
if days_between > 0:
the_now = order_start + timedelta( days=random.randint(1, 1 + int(days_between / 2)))
mainlog.debug("Adding slips to an order on {}".format(the_now))
the_now = datetime( the_now.year, the_now.month, the_now.day) + timedelta(seconds=random.randint(1,10000))
central_clock.set_now_function( lambda : the_now)
dao.delivery_slip_part_dao.make_delivery_slip_for_order( order.order_id, parts_ids_quantities,
the_now, False)
session().commit()
for order in session().query(Order).filter(Order.state == OrderStatusType.order_ready_for_production).all():
parts_ids_quantities = dict()
for order_part in order.parts:
mainlog.debug("qex = {} / {}".format(order_part.tex2, order_part.qty))
if order_part.tex2 < order_part.qty and order_part.total_hours:
parts_ids_quantities[order_part.order_part_id] = order_part.qty - order_part.tex2
if parts_ids_quantities:
mainlog.debug("Creating last delivery slip")
the_now = world_begin + timedelta(days=max_past_days + random.randint(1,grace_period))
mainlog.debug("Adding slips to an order on {}".format(the_now))
the_now = datetime( the_now.year, the_now.month, the_now.day) + timedelta(seconds=random.randint(1,10000))
central_clock.set_now_function( lambda : the_now)
dao.delivery_slip_part_dao.make_delivery_slip_for_order( order.order_id, parts_ids_quantities,
the_now, False)
# Now we adapt the sell price to match the costs
TWO_PLACES = decimal.Decimal(10) ** -2
for order_part in session().query(OrderPart).all():
order_part.sell_price = decimal.Decimal(
(1.0 + random.random()) * dao.order_part_dao.value_work_on_order_part_up_to_date(
order_part.order_part_id, date.today())).quantize(TWO_PLACES)
mainlog.info("Not completed orders = {}".format(
session().query(Order).filter(Order.state != OrderStatusType.order_completed).count()))
mainlog.info("Not completed parts = {}".format(
session().query(OrderPart).filter(OrderPart.state != OrderPartStateType.completed).count()))
mainlog.info("Maximum completion date for order parts = {}".format(
session().query( func.max( OrderPart.completed_date)).scalar()))
def get_rows_for_wide_csv(session_code):
if session_code:
sessions = [Session.objects_get(code=session_code)]
else:
sessions = dbq(Session).order_by('id').all()
session_fields = get_fields_for_csv(Session)
participant_fields = get_fields_for_csv(Participant)
session_ids = [session.id for session in sessions]
pps = (Participant.objects_filter(
Participant.session_id.in_(session_ids)).order_by(
Participant.id).all())
session_cache = {row.id: row for row in sessions}
session_config_fields = set()
for session in sessions:
for field_name in SessionConfig(session.config).editable_fields():
session_config_fields.add(field_name)
session_config_fields = list(session_config_fields)
if not pps:
# 1 empty row
return [[]]
header_row = [f'participant.{fname}' for fname in participant_fields]
header_row += [f'session.{fname}' for fname in session_fields]
header_row += [
f'session.config.{fname}' for fname in session_config_fields
]
rows = [header_row]
for pp in pps:
session = session_cache[pp.session_id]
row = [getattr(pp, fname) for fname in participant_fields]
row += [getattr(session, fname) for fname in session_fields]
row += [session.config.get(fname) for fname in session_config_fields]
rows.append(row)
order_of_apps = _get_best_app_order(sessions)
rounds_per_app = OrderedDict()
for app_name in order_of_apps:
try:
models_module = get_models_module(app_name)
except ModuleNotFoundError:
# this should only happen with devserver because on production server,
# you would need to resetdb after renaming an app.
logger.warning(
f'Cannot export data for app {app_name}, which existed when the session was run '
f'but no longer exists.')
continue
highest_round_number = dbq(
func.max(models_module.Subsession.round_number)).scalar()
if highest_round_number is not None:
rounds_per_app[app_name] = highest_round_number
for app_name in rounds_per_app:
for round_number in range(1, rounds_per_app[app_name] + 1):
new_rows = get_rows_for_wide_csv_round(app_name, round_number,
sessions)
for i in range(len(rows)):
rows[i].extend(new_rows[i])
return [[sanitize_for_csv(v) for v in row] for row in rows]
def home():
total_texts = db_session.query(func.count(AllTweets.id).label('total_texts')).filter(AllTweets.context.is_(CONTEXT)).first().total_texts
total_terms = db_session.query(func.count(Termos.id).label('total_terms')).filter(Termos.context.is_(CONTEXT)).first().total_terms
total_processed = db_session.query(func.count(AllTweets.id).label("total_processed")).filter(AllTweets.context.is_(CONTEXT)).filter(AllTweets.processed==1).first().total_processed
date_max = db_session.query(AllTweets.id, func.max(AllTweets.date).label('last_date')).filter(AllTweets.context.is_(CONTEXT)).first().last_date
date_min = db_session.query(AllTweets.id, func.min(AllTweets.date).label('last_date')).filter(AllTweets.context.is_(CONTEXT)).first().last_date
termos, hashtags, usuarios_rt, usuarios_citados, bigram_trigram = load_from_db(10)
if HASHTAG == "True":
query_a = Hashtags.query.filter(and_(Hashtags.hashtag.is_(SIDE_A),Hashtags.context.is_(CONTEXT))).first()
query_b = Hashtags.query.filter(and_(Hashtags.hashtag.is_(SIDE_B),Hashtags.context.is_(CONTEXT))).first()
else:
query_a = Termos.query.filter(and_(Termos.termo.is_(SIDE_A),Termos.context.is_(CONTEXT))).first()
query_b = Termos.query.filter(and_(Termos.termo.is_(SIDE_B),Termos.context.is_(CONTEXT))).first()
total_a = 0
total_b = 0
percent_a = 0
percent_b = 0
total = 0
if query_a and query_b:
total_a = float(query_a.frequencia)
total_b = float(query_b.frequencia)
total = total_a + total_b
percent_a = (total_a / total) * 100
percent_b = (total_b / total) * 100
profiles_info = get_profile()
query_texts = db_session.query(AllTweets)
all_ = []
for q in query_texts:
teste = q.text.decode('UTF-8')
t = {}
t['tweet_id'] = q.tweet_id
t['user'] = q.user
t['text'] = teste
t['date'] = q.date
all_.append(t)
tweets = jsonify(**{'list': all_})
dict_values = {
'total_texts': total_texts,
'total_terms': total_terms,
'total_processed': total_processed,
'date_max': date_max,
'date_min': date_min,
'side_a': SIDE_A,
'side_b': SIDE_B,
'termos': termos,
'hashtags': hashtags,
'usuarios_rt': usuarios_rt,
'usuarios_citados': usuarios_citados,
'total': (percent_a, percent_b),
'total_value': (int(total_a), int(total_b)),
'bigram_trigram': bigram_trigram,
'context': CONTEXT,
'profile_a': PROFILE_A,
'profile_b': PROFILE_B,
'dict_profile': profiles_info
}
"""
------------------Paginação---------------------
"""
current_page = request.args.get('page', 1, type=int)
num_posts = total_texts
total_num_pages = int(math.ceil(num_posts / items_per_page))
iter_pages = list(range(1, total_num_pages + 1))
"""
------------------Paginação---------------------
"""
return render_template("home.html",values=dict_values, tweets=all_, iter_pages=iter_pages,
current_page=current_page, total_pages=total_num_pages)
def map():
total_texts = db_session.query(func.count(AllTweets.id).label('total_texts')).filter(AllTweets.context.is_(CONTEXT)).first().total_texts
total_terms = db_session.query(func.count(Termos.id).label('total_terms')).filter(Termos.context.is_(CONTEXT)).first().total_terms
total_processed = db_session.query(func.count(AllTweets.id).label("total_processed")).filter(AllTweets.context.is_(CONTEXT)).filter(AllTweets.processed==1).first().total_processed
date_max = db_session.query(AllTweets.id, func.max(AllTweets.date).label('last_date')).filter(AllTweets.context.is_(CONTEXT)).first().last_date
date_min = db_session.query(AllTweets.id, func.min(AllTweets.date).label('last_date')).filter(AllTweets.context.is_(CONTEXT)).first().last_date
termos, hashtags, usuarios_rt, usuarios_citados, bigram_trigram = load_from_db(10)
if HASHTAG == "True":
query_a = Hashtags.query.filter(and_(Hashtags.hashtag.is_(SIDE_A),Hashtags.context.is_(CONTEXT))).first()
query_b = Hashtags.query.filter(and_(Hashtags.hashtag.is_(SIDE_B),Hashtags.context.is_(CONTEXT))).first()
else:
query_a = Termos.query.filter(and_(Termos.termo.is_(SIDE_A),Termos.context.is_(CONTEXT))).first()
query_b = Termos.query.filter(and_(Termos.termo.is_(SIDE_B),Termos.context.is_(CONTEXT))).first()
total_a = 0
total_b = 0
percent_a = 0
percent_b = 0
total = 0
if query_a and query_b:
total_a = float(query_a.frequencia)
total_b = float(query_b.frequencia)
total = total_a + total_b
percent_a = (total_a / total) * 100
percent_b = (total_b / total) * 100
profiles_info = get_profile()
query_texts = db_session.query(AllTweets)
all_ = []
for q in query_texts:
teste = q.text.decode('UTF-8')
t = {}
t['tweet_id'] = q.tweet_id
t['user'] = q.user
t['text'] = teste
t['date'] = q.date
all_.append(t)
tweets = jsonify(**{'list': all_})
dict_values = {
'total_texts': total_texts,
'total_terms': total_terms,
'total_processed': total_processed,
'date_max': date_max,
'date_min': date_min,
'side_a': SIDE_A,
'side_b': SIDE_B,
'termos': termos,
'hashtags': hashtags,
'usuarios_rt': usuarios_rt,
'usuarios_citados': usuarios_citados,
'total': (percent_a, percent_b),
'total_value': (int(total_a), int(total_b)),
'bigram_trigram': bigram_trigram,
'context': CONTEXT,
'profile_a': PROFILE_A,
'profile_b': PROFILE_B,
'dict_profile': profiles_info
}
def push_github():
data = []
geoms = []
tweet_features = []
with open('raw.json') as twtr_hamdata:
for satir in twtr_hamdata:
data.append(json.loads(satir))
for i in range(0,len(data)):
geoms.append(data[i]["geo"]["coordinates"])
#print (geoms[i][0], geoms[i][1])
my_feature = Feature(geometry=Point((float(geoms[i][1]),float(geoms[i][0]))),\
properties={"user_location":data[i]["user"]["location"],\
"user_id": data[i]["id"],\
"user_name":data[i]["user"]["name"],\
"screen_name":data[i]["user"]["screen_name"],\
"followers_count":data[i]["user"]["followers_count"],\
"tweet":data[i]["text"],\
"tweet_time":data[i]["created_at"]})
tweet_features.append(my_feature)
#print tweet_features
tweet_FeatureCollection = FeatureCollection(tweet_features[:])
#print tweet_FeatureCollection["type"]
try:
#saveFile = open('tweets.geojson','a')
data = json.dumps(tweet_FeatureCollection)
#saveFile.close()
except Exception as error:
print ("Unable to write %s error"%error)
g = Github('grandslav', '5kslj8130614')
repo = g.get_user().get_repo("GeoJSONTweets")
contents = repo.get_contents("/tweets.geojson")
new_content = data
# TODO: acrescentar arquivo em vez de substituir ("crimes01112018.geojson")
repo.update_file("/tweets.geojson", "Updating geojson data", new_content, contents.sha)
print("Arquivo .geojson atualizado")
update = True
if update:
print("Atualizando geojson...")
push_github() # faz um push no repositorio com arquivo geojson atualizado
return render_template('map.html', values=dict_values, title='Map')
def _maxid(self):
return db.session.query(func.max(Question.id)).first()
mod_chk = [mod for mod in dir(adm_mods) if mod[0].isupper()]
def input_model():
while True:
mod = input('Model name? ')
if mod in mod_chk:
return mod
else:
print('%s is not an admin Model' % repr(mod))
mod_name = input_model()
table_object_id = sess.query(TableObject.id). \
filter_by(name=eval(mod_name).__tablename__).one()[0]
id_nbr = int(sess.query(func.max(Field.name)).one()[0][1:]) + 1
insp = inspect(eval(mod_name))
for aa, col in insp.columns.items():
field = Field(active=True,
name='F%06d' % (id_nbr),
display_name=col.name,
table_object_id=table_object_id,
primary_key=col.primary_key,
organization_id=1,
order=1,
description=col.name,
data_type_id=type_map[col.type.__visit_name__][0])
length = getattr(col.type, 'length', None)
def selectables(cls, bag, agg_spec):
""" Create a list of statements from spec
:type bag: mongosql.bag.ModelPropertyBags
:rtype: list[sqlalchemy.sql.elements.ColumnElement]
"""
# TODO: calculation expressions for selection: http://docs.mongodb.org/manual/meta/aggregation-quick-reference/
selectables = []
for comp_field, comp_expression in agg_spec.items():
# Column reference
if isinstance(comp_expression, string_types):
selectables.append(
bag.columns[comp_expression].label(comp_field))
continue
# Computed expression
assert isinstance(
comp_expression, dict
), 'Aggregate: Expression should be either a column name, or an object'
assert len(
comp_expression
) == 1, 'Aggregate: expression can only contain a single operator'
operator, expression = comp_expression.popitem()
# Expression statement
if isinstance(expression, int) and operator == '$sum':
# Special case for count
expression_stmt = expression
elif isinstance(expression, string_types):
# Column name
expression_stmt = bag.columns[expression]
# Json column?
if bag.columns.is_column_json(expression):
# PostgreSQL always returns text values from it, and for aggregation we usually need numbers :)
expression_stmt = cast(expression_stmt, Float)
elif isinstance(expression, dict):
# Boolean expression
expression_stmt = MongoCriteria.statement(bag, expression)
# Need to cast it to int
expression_stmt = cast(expression_stmt, Integer)
else:
raise AssertionError(
'Aggregate: expression should be either a column name, or an object'
)
# Operator
if operator == '$max':
comp_stmt = func.max(expression_stmt)
elif operator == '$min':
comp_stmt = func.min(expression_stmt)
elif operator == '$avg':
comp_stmt = func.avg(expression_stmt)
elif operator == '$sum':
if isinstance(expression_stmt, int):
# Special case for count
comp_stmt = func.count()
if expression_stmt != 1:
comp_stmt *= expression_stmt
else:
comp_stmt = func.sum(expression_stmt)
else:
raise AssertionError(
'Aggregate: unsupported operator "{}"'.format(operator))
# Append
selectables.append(comp_stmt.label(comp_field))
return selectables
def get_count(cls):
with new_session() as session:
min_id = session.query(func.min(ErrorReport.id)).scalar() or 0
max_id = session.query(func.max(ErrorReport.id)).scalar() or 0
return max_id - min_id
def get_count(cls):
with new_session() as session:
return (session.query(func.max(Result.id)).scalar() or 0) \
- (session.query(func.min(Result.id)).scalar() or 0)
def home():
total_texts = db_session.query(
func.count(AllTweets.id).label('total_texts')).filter(
AllTweets.context.is_(CONTEXT)).first().total_texts
if total_texts == 0:
return render_template("out.html")
total_terms = db_session.query(
func.count(Termos.id).label('total_terms')).filter(
Termos.context.is_(CONTEXT)).first().total_terms
total_processed = db_session.query(
func.count(AllTweets.id).label("total_processed")).filter(
AllTweets.context.is_(CONTEXT)).filter(
AllTweets.processed == 1).first().total_processed
date_max = db_session.query(
AllTweets.id,
func.max(AllTweets.date).label('last_date')).filter(
AllTweets.context.is_(CONTEXT)).first().last_date
date_min = db_session.query(
AllTweets.id,
func.min(AllTweets.date).label('last_date')).filter(
AllTweets.context.is_(CONTEXT)).first().last_date
termos, hashtags, usuarios_rt, usuarios_citados, bigram_trigram = load_from_db(
10)
if HASHTAG == "True":
query_a = Hashtags.query.filter(
and_(Hashtags.hashtag.is_(SIDE_A),
Hashtags.context.is_(CONTEXT))).first()
query_b = Hashtags.query.filter(
and_(Hashtags.hashtag.is_(SIDE_B),
Hashtags.context.is_(CONTEXT))).first()
else:
query_a = Termos.query.filter(
and_(Termos.termo.is_(SIDE_A),
Termos.context.is_(CONTEXT))).first()
query_b = Termos.query.filter(
and_(Termos.termo.is_(SIDE_B),
Termos.context.is_(CONTEXT))).first()
total_a = 0
total_b = 0
percent_a = 0
percent_b = 0
total = 0
if query_a and query_b:
total_a = float(query_a.frequencia)
total_b = float(query_b.frequencia)
total = total_a + total_b
percent_a = (total_a / total) * 100
percent_b = (total_b / total) * 100
profiles_info = get_profile()
dict_values = {
'total_texts': total_texts,
'total_terms': total_terms,
'total_processed': total_processed,
'date_max': date_max,
'date_min': date_min,
'side_a': SIDE_A,
'side_b': SIDE_B,
'termos': termos,
'hashtags': hashtags,
'usuarios_rt': usuarios_rt,
'usuarios_citados': usuarios_citados,
'total': (percent_a, percent_b),
'total_value': (int(total_a), int(total_b)),
'bigram_trigram': bigram_trigram,
'context': CONTEXT,
'profile_a': PROFILE_A,
'profile_b': PROFILE_B,
'dict_profile': profiles_info
}
return render_template("index.html", values=dict_values)
roles.remove(from_role) # Remove from_role from choices.
to_role = input_role('TO')
do_you_want_message(from_role, to_role, role_mods)
for model in role_mods:
recs = sess.query(eval(model)).filter_by(role_id=from_role.id). \
filter_by(active=True).order_by('id')
if recs.count() == 0:
print('%s has 0 records. Nothing to dupe.' % model)
continue
initials = ''.join([c for c in model if c.isupper()]) # prepended to rec name.
# The base number must guarantee uniqueness
sequence = sess.query(func.max(eval(model).id)).one()[0] + 3001
for indx, rec in enumerate(recs):
new_name = '%s%08d' % (initials, sequence+indx)
if new_name == rec.name:
print("name mis-match, new_name == rec.name, %s" % new_name)
new_rec = eval(model)() # A new model instance.
for k,v in new_record(model, rec, name=new_name).items():
setattr(new_rec, k, v) # Loading the new instance.
sess.add(new_rec) # New object pending commit.
print()
print("%d records were created for %s" % (recs.count(), model))
ans = input("Do you want to commit them? (y or n)? ")
if ans == 'y':
sess.commit() # Commit new records by model.
def get_periods_max_cost(self, session):
return session.query(Period.cost, func.max(UserTotalNum.total_num).label("max_num")).\
filter(Period.pid == UserTotalNum.period_id).\
group_by(Period.pid).all()
def add_many(self, new_urls, **kwargs):
assert not isinstance(new_urls, (str, bytes)), \
'Expected a list-like. Got {}'.format(new_urls)
referrer = kwargs.pop('referrer', None)
top_url = kwargs.pop('top_url', None)
new_urls = tuple(new_urls)
if not new_urls:
return ()
assert isinstance(new_urls[0], dict), type(new_urls[0])
url_strings = list(item['url'] for item in new_urls)
if referrer:
url_strings.append(referrer)
if top_url:
url_strings.append(top_url)
with self._session() as session:
query = insert(URLString).prefix_with('OR IGNORE')
session.execute(query, [{'url': url} for url in url_strings])
bind_values = dict(status=Status.todo)
bind_values.update(**kwargs)
bind_values['url_str_id'] = select([URLString.id])\
.where(URLString.url == bindparam('url'))
if referrer:
bind_values['referrer_id'] = select([URLString.id])\
.where(URLString.url == bindparam('referrer'))
if top_url:
bind_values['top_url_str_id'] = select([URLString.id])\
.where(URLString.url == bindparam('top_url'))
query = insert(URL).prefix_with('OR IGNORE').values(bind_values)
all_row_values = []
for item in new_urls:
assert 'url' in item
assert 'referrer' not in item
assert 'top_url' not in item
row_values = item
if referrer:
row_values['referrer'] = referrer
if top_url:
row_values['top_url'] = top_url
all_row_values.append(row_values)
last_primary_key = session.query(func.max(URL.id)).scalar() or 0
session.execute(query, all_row_values)
query = select([URLString.url]).where(
and_(URL.id > last_primary_key,
URL.url_str_id == URLString.id)
)
added_urls = [row[0] for row in session.execute(query)]
return added_urls
def init_static() -> Static:
names = [
name for name, in cast(
Iterator[str],
training_db.query(MemeCorrectTrain.name).distinct(
MemeCorrectTrain.name),
)
]
names_to_shuffle = deepcopy(names)
name_num = {name: idx for idx, name in enumerate(names)}
num_name = {str(v): k for k, v in name_num.items()}
max_name_idx: TestTrainToMax = {
"train": {
"not_a_meme":
cast(
int,
training_db.query(func.max(NotAMemeTrain.name_idx)).scalar(),
),
"not_a_template":
cast(
int,
training_db.query(func.max(
NotATemplateTrain.name_idx)).scalar(),
),
"correct": {
name: cast(
int,
training_db.query(func.max(
MemeCorrectTrain.name_idx)).filter(
cast(ClauseElement,
MemeCorrectTrain.name == name)).scalar(),
)
for name in names
if name not in ["not_a_meme", "not_a_template"]
},
"incorrect": {
name: cast(
int,
training_db.query(func.max(
MemeIncorrectTrain.name_idx)).filter(
cast(ClauseElement,
MemeIncorrectTrain.name == name)).scalar(),
)
for name in names
if name not in ["not_a_meme", "not_a_template"]
},
},
"test": {
"not_a_meme":
cast(
int,
training_db.query(func.max(NotAMemeTest.name_idx)).scalar(),
),
"not_a_template":
cast(
int,
training_db.query(func.max(
NotATemplateTest.name_idx)).scalar(),
),
"correct": {
name: cast(
int,
training_db.query(func.max(
MemeCorrectTest.name_idx)).filter(
cast(ClauseElement,
MemeCorrectTest.name == name)).scalar(),
)
for name in names
if name not in ["not_a_meme", "not_a_template"]
},
"incorrect": {
name: cast(
int,
training_db.query(func.max(
MemeIncorrectTest.name_idx)).filter(
cast(ClauseElement,
MemeIncorrectTest.name == name)).scalar(),
)
for name in names
if name not in ["not_a_meme", "not_a_template"]
},
},
}
static: Static = {
"names": names,
"names_to_shuffle": names_to_shuffle,
"name_num": name_num,
"num_name": num_name,
"folder_count": {
"not_a_meme": len(os.listdir(NOT_MEME_REPO)),
"not_a_template": len(os.listdir(NOT_TEMPLATE_REPO)),
**{
name: len(os.listdir(MEMES_REPO + name))
for name in os.listdir(MEMES_REPO)
},
},
"max_name_idx": max_name_idx,
}
return static
def latest_for_season(self, name, season):
q = (self._db.query(func.max(Release.episode)).filter_by(
name=name, season=season).join(ReleaseMonitor))
return Maybe(*q.first())
def get_count(cls):
with new_session() as session:
return (session.query(func.max(Result.id)).scalar() or 0) \
- (session.query(func.min(Result.id)).scalar() or 0)
def add_many(self, new_urls, **kwargs):
assert not isinstance(new_urls, (str, bytes)), \
'Expected a list-like. Got {}'.format(new_urls)
referrer = kwargs.pop('referrer', None)
top_url = kwargs.pop('top_url', None)
new_urls = tuple(new_urls)
if not new_urls:
return ()
assert isinstance(new_urls[0], dict), type(new_urls[0])
url_strings = list(item['url'] for item in new_urls)
if referrer:
url_strings.append(referrer)
if top_url:
url_strings.append(top_url)
with self._session() as session:
query = insert(URLString).prefix_with('OR IGNORE')
session.execute(query, [{'url': url} for url in url_strings])
bind_values = dict(status=Status.todo)
bind_values.update(**kwargs)
bind_values['url_str_id'] = select([URLString.id])\
.where(URLString.url == bindparam('url'))
if referrer:
bind_values['referrer_id'] = select([URLString.id])\
.where(URLString.url == bindparam('referrer'))
if top_url:
bind_values['top_url_str_id'] = select([URLString.id])\
.where(URLString.url == bindparam('top_url'))
query = insert(URL).prefix_with('OR IGNORE').values(bind_values)
all_row_values = []
for item in new_urls:
assert 'url' in item
assert 'referrer' not in item
assert 'top_url' not in item
row_values = item
if referrer:
row_values['referrer'] = referrer
if top_url:
row_values['top_url'] = top_url
all_row_values.append(row_values)
last_primary_key = session.query(func.max(URL.id)).scalar() or 0
session.execute(query, all_row_values)
query = select([URLString.url]).where(
and_(URL.id > last_primary_key,
URL.url_str_id == URLString.id))
added_urls = [row[0] for row in session.execute(query)]
return added_urls
def _build_query(self, table, filter_values):
having = []
filter_cols = []
external_cols = _get_grouping(filter_values)
for fil in self.filters:
if isinstance(fil, ANDFilter):
filter_cols.append(fil.filters[0].column_name)
having.append(fil)
elif isinstance(fil, RawFilter):
having.append(fil)
elif fil.column_name not in [
'group', 'gender', 'group_leadership', 'disaggregate_by',
'table_card_group_by'
]:
if fil.column_name not in external_cols and fil.column_name != 'maxmin':
filter_cols.append(fil.column_name)
having.append(fil)
group_having = ''
having_group_by = []
if ('disaggregate_by' in filter_values and filter_values['disaggregate_by'] == 'group') or \
(filter_values.get('table_card_group_by') == 'group_leadership'):
having_group_by.append('group_leadership')
elif 'group_leadership' in filter_values and filter_values[
'group_leadership']:
group_having = "(MAX(CAST(gender as int4)) + MIN(CAST(gender as int4))) " \
"= :group_leadership and group_leadership=\'Y\'"
having_group_by.append('group_leadership')
filter_cols.append('group_leadership')
elif 'gender' in filter_values and filter_values['gender']:
group_having = "(MAX(CAST(gender as int4)) + MIN(CAST(gender as int4))) = :gender"
table_card_group = []
if 'group_name' in self.group_by:
table_card_group.append('group_name')
s1 = alias(select([
table.c.doc_id, table.c.group_case_id, table.c.group_name,
table.c.group_id,
(sqlalchemy.func.max(table.c.prop_value) +
sqlalchemy.func.min(table.c.prop_value)).label('maxmin')
] + filter_cols + external_cols,
from_obj=table,
group_by=([
table.c.doc_id, table.c.group_case_id,
table.c.group_name, table.c.group_id
] + filter_cols + external_cols)),
name='x')
s2 = alias(select([
table.c.group_case_id,
sqlalchemy.cast(
cast(func.max(table.c.gender), Integer) +
cast(func.min(table.c.gender), Integer),
VARCHAR).label('gender')
] + table_card_group,
from_obj=table,
group_by=[table.c.group_case_id] + table_card_group +
having_group_by,
having=group_having),
name='y')
group_by = list(self.group_by)
if 'group_case_id' in group_by:
group_by[group_by.index('group_case_id')] = s1.c.group_case_id
group_by[group_by.index('group_name')] = s1.c.group_name
return select([sqlalchemy.func.count(s1.c.doc_id).label(self.key)] +
group_by,
group_by=[s1.c.maxmin] + filter_cols + group_by,
having=AND(having).build_expression(s1),
from_obj=join(s1, s2, s1.c.group_case_id ==
s2.c.group_case_id)).params(filter_values)
def get_count(cls):
with new_session() as session:
min_id = session.query(func.min(ErrorReport.id)).scalar() or 0
max_id = session.query(func.max(ErrorReport.id)).scalar() or 0
return max_id - min_id
