def execute_get_missing_initial_alerts(itgs):
endpoint_users = Table('endpoint_users')
endpoint_alerts = Table('endpoint_alerts')
users = Table('users')
usage_after_filters = Table('usage_after_filters')
query = (Query.with_(
Query.from_(endpoint_users).where(
Not(
Exists(
Query.from_(endpoint_alerts).where(
endpoint_alerts.endpoint_id ==
endpoint_users.endpoint_id).where(
endpoint_alerts.user_id == endpoint_users.user_id))
)).select(endpoint_users.endpoint_id.as_('endpoint_id'),
endpoint_users.user_id.as_('user_id'),
Min(endpoint_users.created_at).as_('first_usage'),
Max(endpoint_users.created_at).as_('last_usage'),
Count(endpoint_users.id).as_('count_usage')).groupby(
endpoint_users.endpoint_id, endpoint_users.user_id),
'usage_after_filters').from_(usage_after_filters).join(users).on(
users.id == usage_after_filters.user_id).select(
usage_after_filters.user_id, users.username,
usage_after_filters.endpoint_id,
usage_after_filters.first_usage,
usage_after_filters.last_usage,
usage_after_filters.count_usage).orderby(
usage_after_filters.user_id))
sql = query.get_sql()
itgs.read_cursor.execute(sql)
def get_query_by_user_filter(
query: PostgreSQLQuery, filters: List[str],
inhibit_filters: List[str]) -> PostgreSQLQuery:
try:
logging.debug('Получение запроса по критериям пользователя')
t = Table('an_alias')
test_query = (PostgreSQLQuery.with_(query, "an_alias").from_(
AliasedQuery("an_alias")).select('*'))
for filter in filters:
test_query = test_query.where(
t.object_name.like(Parameter('%s')))
for filter in inhibit_filters:
test_query = test_query.where(
t.object_name.not_like(Parameter('%s')))
except Exception as e:
logging.error("Error is ", e)
return test_query
def get_basic_loan_info_query():
"""Get the basic query that we use for fetching a loans information"""
loans = Table('loans')
usrs = Table('users')
moneys = Table('moneys')
lenders = usrs.as_('lenders')
borrowers = usrs.as_('borrowers')
principals = moneys.as_('principals')
principal_currencies = Table('currencies').as_('principal_currencies')
principal_repayments = moneys.as_('principal_repayments')
repayment_events = Table('loan_repayment_events')
latest_repayments = Table('latest_repayments')
query = (Query.with_(
Query.from_(repayment_events).select(
repayment_events.loan_id,
ppfns.Max(
repayment_events.created_at).as_('latest_created_at')).groupby(
repayment_events.loan_id),
'latest_repayments').from_(loans).select(
lenders.username, borrowers.username, principal_currencies.code,
principal_currencies.symbol, principal_currencies.symbol_on_left,
principal_currencies.exponent, principals.amount,
principal_repayments.amount, loans.created_at,
latest_repayments.latest_created_at, loans.repaid_at,
loans.unpaid_at, loans.deleted_at).join(lenders).on(
lenders.id == loans.lender_id).join(borrowers).on(
borrowers.id == loans.borrower_id).join(principals).on(
principals.id ==
loans.principal_id).join(principal_currencies).on(
principal_currencies.id == principals.currency_id).
join(principal_repayments).on(
principal_repayments.id ==
loans.principal_repayment_id).left_join(latest_repayments).on(
latest_repayments.loan_id == loans.id))
return query
SELECT
0 AS permissions,
allow,
deny
FROM page_permissions
WHERE entity = ANY ($2) OR page_id = $1)
SELECT bit_or(permissions) | bit_or(allow) | (coalesce((SELECT * FROM everyone_perms), $3)) & ~bit_or(deny)
FROM all_permissions
"""
"""pypika attempt:"""
q = (Query.with_(
Query.select(rp.permissions).from_(rp).where(rp.entity == entity_id),
'everyone_perms').with_(
Query.select(rp.permissions,
Term(0).as_('allow'),
Term(0).as_('deny')).from_(rp).where(
rp.entity.isin(role_ids)).union_all(
Query.select(
Term(0).as_('permissions'), pp.allow,
pp.deny).from_(pp).where(
pp.entity.isin(role_ids)
| pp.page_id == entity_id)),
'all_permissions').select(
bit_or('permissions') | bit_or('allow') | bit_or('deny')
| Coalesce(
Query.select(pypika.Table('everyone_perms').star).from_(
pypika.Table('everyone_perms')), default_permissions)
& ~bit_or('deny')).from_('all_permissions'))
print(q)
def execute_get_missing_alerts(itgs, bonus_filters):
"""Executes the read to get all alerts which should have been sent out already
for endpoint usage. The endpoint users is filtered using `bonus_filters`.
If `bonus_filters` is a no-op then this function will return one row
for each use of an endpoint by any user for which there is no later alert.
The result is sorted by user id.
Arguments:
- `itgs (LazyIntegrations)`: The integrations to use for sending alerts.
- `bonus_filters (callable)`: A callable which accepts the query, a
callable which accepts an argument and returns the Parameter which will
refer to that argment, and keyword arguments for each Table reference we
have. This should return the new Query to use after filtering the results.
Returns (via `itgs.read_cursor.fetchall()`):
- `rows (list)`: A list of lists, where each inner list has the following
elements:
- `user_id (int)`: The id of the user which should be sent an alert.
- `username (str)`: The username of the user with id `user_id`.
- `endpoint_id (int)`: The id of the endpoint the user used
- `endpoint_slug (str)`: The slug of the endpoint with id `endpoint_id`
- `first_use_in_interval (datetime)`: The earliest time within the
interval that the user used the endpoint.
- `last_use_in_interval (datetime)`: The latest time within the interval
that the user used the endpoint.
- `count_in_interval (int)`: The number of times the user used the endpoint
within the interval.
"""
endpoint_users = Table('endpoint_users')
endpoint_alerts = Table('endpoint_alerts')
most_recent_alerts = Table('most_recent_alerts')
usage_after_filters = Table('usage_after_filters')
users = Table('users')
args = []
def add_param(arg):
args.append(arg)
return Parameter(f'${len(args)}')
query = (Query.with_(
Query.from_(endpoint_alerts).select(
endpoint_alerts.endpoint_id.as_('endpoint_id'),
endpoint_alerts.user_id.as_('user_id'),
Max(endpoint_alerts.sent_at).as_('max_sent_at')).groupby(
endpoint_alerts.endpoint_id,
endpoint_alerts.user_id), 'most_recent_alerts'
).with_(
bonus_filters(Query.from_(endpoint_users).join(most_recent_alerts).on(
(most_recent_alerts.endpoint_id == endpoint_users.endpoint_id)
& (most_recent_alerts.user_id == endpoint_users.user_id)).select(
endpoint_users.endpoint_id.as_('endpoint_id'),
endpoint_users.user_id.as_('user_id'),
Min(endpoint_users.created_at).as_('first_usage'),
Max(endpoint_users.created_at).as_('last_usage'),
Count(endpoint_users.id).as_('count_usage')).where(
endpoint_users.user_id.notnull()).where(
endpoint_users.created_at >
most_recent_alerts.max_sent_at).groupby(
endpoint_users.endpoint_id,
endpoint_users.user_id),
add_param,
endpoint_users=endpoint_users,
endpoint_alerts=endpoint_alerts,
most_recent_alerts=most_recent_alerts),
'usage_after_filters').from_(usage_after_filters).join(users).on(
users.id == usage_after_filters.user_id).select(
usage_after_filters.user_id, users.username,
usage_after_filters.endpoint_id,
usage_after_filters.first_usage,
usage_after_filters.last_usage,
usage_after_filters.count_usage).orderby(
usage_after_filters.user_id))
(sql, ordered_args) = convert_numbered_args(query.get_sql(), args)
itgs.read_cursor.execute(sql, ordered_args)
