Untitled
unknown
plain_text
a year ago
37 kB
1
Indexable
import os
import textwrap
from datetime import datetime, timedelta
from itertools import cycle
from typing import List, Tuple
from uuid import uuid4
import psycopg2
import structlog
from dateutil.relativedelta import relativedelta
from jinja2 import Template
from matplotlib import font_manager as fm
from matplotlib import pyplot as plt
from matplotlib.patches import FancyBboxPatch
from pkg_resources import resource_filename
from nydus.client.chat_service import ChatService
from nydus.client.google_drive import DriveService
from nydus.client.impala import ImpalaClient
from nydus.data import Partition
from nydus.storage import Connection
from nydus.util import is_weekend, transaction
log = structlog.get_logger(__name__)
WEEKEND_COLOR = "#97130F"
class TextBlock:
def __init__(self, text):
self.text = text
def serialize(self):
return {"type": "section", "text": {"type": "mrkdwn", "text": self.text}}
class ImageBlock:
def __init__(self, image_url, title):
self.image_url = image_url
self.title = title
def serialize(self):
return {
"type": "image",
"title": {"type": "plain_text", "text": self.title, "emoji": True},
"image_url": self.image_url,
"alt_text": self.title,
}
def build_popular_directions_block(impala, params):
impala.execute(
f"""
WITH searches as (
SELECT origin, destination, count(distinct search_id) as searches
FROM avia.searches
WHERE pdate = to_date(date_sub(now(), 1))
GROUP BY origin, destination
),
bookings as (
SELECT origin, destination, count(distinct booking_id) as bookings
FROM avia.bookings
WHERE to_date(booked_at) = to_date(date_sub(now(), 1))
AND state = 'paid'
GROUP BY origin, destination
)
SELECT coalesce(co.ru_name, co.iata) as origin, coalesce(cd.ru_name, cd.iata) as destination, searches, bookings AS paid_bookings
FROM searches AS s
JOIN bookings AS b ON b.origin = s.origin AND b.destination = s.destination
JOIN dictionary.cities AS co ON co.iata = s.origin
JOIN dictionary.cities AS cd ON cd.iata = s.destination
ORDER BY bookings DESC
LIMIT 5
"""
)
with open(
resource_filename("nydus", "resources/templates/general_bot/directions.txt")
) as f:
template = Template(f.read())
text = template.render(
booking_name=booking_name,
search_name=search_name,
directions=impala.fetchall(),
direction_type="",
pretty_int=pretty_int,
)
return TextBlock(text)
def build_popular_international_directions_block(impala, params):
"""Popular international directions by searches and bookings."""
impala.execute(
f"""
WITH searches as (
SELECT origin, destination, count(distinct search_id) as searches
FROM avia.searches
WHERE pdate = '{params['date']}'
AND if(origin_country_iata in ('KX', 'RU'), 'RU', origin_country_iata) !=
if(destination_country_iata in ('KX', 'RU'), 'RU', destination_country_iata)
GROUP BY origin, destination
),
bookings as (
SELECT origin, destination, count(distinct booking_id) as bookings
FROM avia.bookings
WHERE to_date(booked_at) = '{params['date']}'
AND state = 'paid'
AND if(origin_country_iata in ('KX', 'RU'), 'RU', origin_country_iata) !=
if(destination_country_iata in ('KX', 'RU'), 'RU', destination_country_iata)
GROUP BY origin, destination
)
SELECT coalesce(co.ru_name, co.iata) as origin, coalesce(cd.ru_name, cd.iata) as destination, searches, bookings AS paid_bookings
FROM searches AS s
JOIN bookings AS b ON b.origin = s.origin AND b.destination = s.destination
JOIN dictionary.cities AS co ON co.iata = s.origin
JOIN dictionary.cities AS cd ON cd.iata = s.destination
ORDER BY bookings DESC
LIMIT 5
"""
)
with open(
resource_filename("nydus", "resources/templates/general_bot/directions.txt")
) as f:
template = Template(f.read())
text = template.render(
booking_name=booking_name,
search_name=search_name,
directions=impala.fetchall(),
direction_type="международные ",
pretty_int=pretty_int,
)
return TextBlock(text)
def pretty_int(n: int) -> str:
return f"{n:,.0f}".replace(",", " ")
def build_bookings_by_market_block(impala, yesterday_pt):
impala.execute(
rf"""
SELECT
CASE WHEN market in ("ru", "kz", "ua", "uz", "by", "az", "kg", "us", "ge", "tj") THEN upper(market)
ELSE "OTHER"
END AS market,
coalesce(count(booking_id), 0) AS paid_bookings,
coalesce(sum(cast(profit*cur.rate AS INT)), 0) AS profit_usd
FROM avia.bookings
LEFT JOIN (
select *
from dictionary.currency_rates
where src = 'RUB' and dst = 'USD'
) cur ON cur.pdate = to_date(booked_at)
WHERE to_date(booked_at) = '{yesterday_pt.pdate}'
AND state = 'paid'
GROUP BY 1
ORDER BY 2 DESC
"""
)
markets = ["RU", "KZ", "UA", "UZ", "BY", "AZ", "KG", "US", "GE", "TJ", "OTHER"]
bookings_by_market = {
market: {"paid_bookings": 0, "profit_usd": 0} for market in markets
}
bookings_by_market.update(
{json_obj["market"]: json_obj for json_obj in impala.fetchall()}
)
with open(
resource_filename(
"nydus", "resources/templates/general_bot/bookings_by_market.txt"
)
) as f:
template = Template(f.read())
text = template.render(
booking_name=booking_name,
pretty_int=pretty_int,
bookings_by_market=bookings_by_market,
flag_by_market=flag_by_market,
)
return TextBlock(text)
def send_general_bot_report(
impala: ImpalaClient,
partition: Partition,
conn: Connection,
chat_service: ChatService,
drive: DriveService,
channels: List[str],
):
impala.cursor_options = {"dictify": True}
def diff(value1, value2):
try:
return "%.2f" % round(
(float(value2) - float(value1)) * 100 / float(value1), 2
)
except:
return "?"
today = partition.to_timestamp().date()
yesterday_pt = partition.day_before()
params = {
"today": today,
"date": today - timedelta(days=1),
"date_prev": today - timedelta(days=8),
"date_prev_comparison": today - timedelta(days=7),
"date_start_chart": today - timedelta(days=14),
"last_year_week_start": today - timedelta(days=7) - relativedelta(years=1),
"last_year_week_end": today - timedelta(days=1) - relativedelta(years=1),
"before_last_year_week_start": today
- timedelta(days=7)
- relativedelta(years=2),
"before_last_year_week_end": today - timedelta(days=1) - relativedelta(years=2),
"this_year": (today - timedelta(days=1)).year,
"last_year": (today - timedelta(days=1) - relativedelta(years=1)).year,
"before_last_year": (today - timedelta(days=1) - relativedelta(years=2)).year,
}
def save_stats(data):
"""Save current values to calculate the difference properly."""
log.info("Save stats")
with transaction(conn, as_dict=True) as cursor:
cursor.execute(
"""
INSERT INTO search_source_statistic_history
(date, searches, bookings, profit, profit_usd, turnover,
turnover_usd, cpc_clicks, cpc_profit, cpc_profit_usd, bookings_domestic, bookings_international)
VALUES (%(date)s, %(searches)s, %(paid_bookings)s, %(profit)s, %(profit_usd)s, %(turnover)s, %(turnover_usd)s,
%(cpc_clicks)s, %(cpc_profit)s, %(cpc_profit_usd)s, %(bookings_domestic)s, %(bookings_international)s)
""",
data,
)
def get_data():
data = {"date": params["date"]}
impala.execute(
f"""
SELECT count(*) AS searches
FROM avia.searches
WHERE pdate = '{params['date']}'
"""
)
data.update(impala.fetchall()[0])
impala.execute(
f"""
with
b_raw as (
select
booking_id,
ifnull(if(origin_country_iata in ('KX', 'RU'), 'RU', origin_country_iata), '') as origin_country_iata,
ifnull(if(destination_country_iata in ('KX', 'RU'), 'RU', destination_country_iata), '') as destination_country_iata,
profit,
price
from avia.bookings
where to_date(booked_at) = '{params['date']}'
and to_date(booked_at) < '{params['today']}'
and state = 'paid'
),
b as (
select
count(*) AS paid_bookings,
sum(if(origin_country_iata = destination_country_iata, 1, 0)) as bookings_domestic,
sum(if(origin_country_iata != destination_country_iata, 1, 0)) as bookings_international,
cast(sum(profit) as int) as profit,
sum(price) as turnover
from b_raw
),
sc as (
select click_id, sum(if(is_bot, 1, 0)) as bot_suspicions
from avia.scaleo_clicks
where pdate = '{params['date']}'
group by 1
),
c as (
select
count(distinct cc.click_id) cpc_clicks,
sum(cpc_profit) as cpc_profit
from avia.clicks as cc
inner join sc on cc.click_id = sc.click_id
where cc.pdate = '{params['date']}'
and cc.cpc_profit is not null
and sc.bot_suspicions = 0
)
select paid_bookings
, bookings_domestic
, bookings_international
, profit
, turnover
, cast(profit * rate as int) as profit_usd
, cast(turnover * rate as int) as turnover_usd
, cpc_clicks
, cpc_profit
, cpc_profit * rate as cpc_profit_usd
from b
inner join c
inner join dictionary.currency_rates as r on r.pdate = '{params['date']}'
and r.src = 'RUB' and r.dst = 'USD'
"""
)
data.update(impala.fetchall()[0])
try:
save_stats(data)
except psycopg2.IntegrityError:
pass
with transaction(conn, as_dict=True) as cursor:
cursor.execute(
"""
SELECT searches, bookings, profit, profit_usd, turnover, turnover_usd,
cpc_clicks, cpc_profit, cpc_profit_usd
FROM search_source_statistic_history
WHERE date = %(date_prev)s
""",
params,
)
prev_stats = cursor.fetchone()
impala.execute(
f"""
SELECT
count(distinct booking_id) bookings,
coalesce(cast(sum(profit*cur.rate) as INT), 0) profit
FROM avia.bookings
left join (select * from dictionary.currency_rates where src = 'RUB' and dst = 'USD') as cur on cur.pdate = to_date(booked_at)
WHERE date_trunc('day', booked_at) between '{params['date_prev_comparison']}' and '{params['date']}'
and state = 'paid'
"""
)
this_year_stats = impala.fetchall()[0]
# расчет отельных бронирований и полученного с них профита с учетом ожидаемых отмен
impala.execute(
f"""with bookings_by_day as (
-- все бронирования за указанную дату, с делением на аффилятов и не аффлятов для более точного расчтеа прогноза
select
to_date(b.sale_date) as pdate,
case when b.affiliate_marker regexp '^[0-9]{{5,6}}.*' or c.marker regexp '^[0-9]{{5,6}}.*' then 'AF' else 'Not AF'
end as af,
count(distinct b.order_id) as bookings,
sum(commission) as profit
from hotel.stat_sales b
left join (
select id, marker
from hotel.clicks
where to_date(created_at) = cast('{params['date']}' as TIMESTAMP)
) as c on cast(c.id as string) = b.marker
left join (
-- для всех броней возьмем последние статусы и максимальный расмер комиссии, который мы могли бы получить
-- в случае отсутствия отмен
select
order_id,
max(coalesce(b.comission_summ * cur1.rate, 0)) AS commission
from hotel.stat_sales b
left join (
select *
from dictionary.currency_rates
where dst = 'USD'
) cur1 ON cur1.pdate = to_date(b.sale_date) and cur1.src = b.comission_currency
left join(
select *
from dictionary.currency_rates
where dst = 'USD'
) cur2 ON cur2.pdate = to_date(b.sale_date) and cur2.src = b.total_currency
where to_date(b.sale_date) = cast('{params['date']}' as TIMESTAMP)
-- для избежания ошибок, связанных с указанием гейтами валюты бронирования
and comission_summ * cur1.rate < total_summ * cur2.rate
group by 1
) as bb on bb.order_id = b.order_id
where to_date(b.sale_date) = cast('{params['date']}' as TIMESTAMP)
-- последний статус брони
and flags & 1 = 1
group by 1, 2
),
total_bookings_last_year as (
-- все бронирования, сделанные год назад в том же месяце что и bookings_by_day
select
case when b.affiliate_marker regexp '^[0-9]{{5,6}}.*' or c.marker regexp '^[0-9]{{5,6}}.*' then 'AF' else 'Not AF'
end as af,
count(distinct b.order_id) as total_bookings,
sum(commission) as total_profit
from hotel.stat_sales b
left join (
select id, marker
from hotel.clicks
where to_date(created_at) >= to_date(
case
when year('{params['date']}') = 2021 and month('{params['date']}') < 8
then concat(from_timestamp(cast('{params['date']}' as TIMESTAMP) - interval 2 years, 'yyyy-MM'), '-01')
else concat(from_timestamp(cast('{params['date']}' as TIMESTAMP) - interval 1 years, 'yyyy-MM'), '-01')
end)
and to_date(created_at) < to_date(
case
when year('{params['date']}') = 2021 and month('{params['date']}') < 8
then concat(from_timestamp(cast('{params['date']}' as TIMESTAMP) - interval 2 years + interval 1 month, 'yyyy-MM'), '-01')
else concat(from_timestamp(cast('{params['date']}' as TIMESTAMP) - interval 1 years + interval 1 month, 'yyyy-MM'), '-01')
end)
) as c on cast(c.id as string) = b.marker
left join (
select
order_id,
max(coalesce(b.comission_summ * cur1.rate, 0)) AS commission
from hotel.stat_sales b
left join(
select *
from dictionary.currency_rates
where dst = 'USD'
) cur1 ON cur1.pdate = to_date(b.sale_date) and cur1.src = b.comission_currency
left join(
select *
from dictionary.currency_rates
where dst = 'USD'
) cur2 ON cur2.pdate = to_date(b.sale_date) and cur2.src = b.total_currency
where to_date(b.sale_date) >= to_date(
case
when year('{params['date']}') = 2021 and month('{params['date']}') < 8
then concat(from_timestamp(cast('{params['date']}' as TIMESTAMP) - interval 2 years, 'yyyy-MM'), '-01')
else concat(from_timestamp(cast('{params['date']}' as TIMESTAMP) - interval 1 years, 'yyyy-MM'), '-01')
end)
and to_date(b.sale_date) < to_date(
case
when year('{params['date']}') = 2021 and month('{params['date']}') < 8
then concat(from_timestamp(cast('{params['date']}' as TIMESTAMP) - interval 2 years + interval 1 month, 'yyyy-MM'), '-01')
else concat(from_timestamp(cast('{params['date']}' as TIMESTAMP) - interval 1 years + interval 1 month, 'yyyy-MM'), '-01')
end)
and comission_summ * cur1.rate < total_summ * cur2.rate
group by 1
) as bb on bb.order_id = b.order_id
where to_date(b.sale_date) >= to_date(
case
when year('{params['date']}') = 2021 and month('{params['date']}') < 8
then concat(from_timestamp(cast('{params['date']}' as TIMESTAMP) - interval 2 years, 'yyyy-MM'), '-01')
else concat(from_timestamp(cast('{params['date']}' as TIMESTAMP) - interval 1 years, 'yyyy-MM'), '-01')
end)
and to_date(b.sale_date) < to_date(
case
when year('{params['date']}') = 2021 and month('{params['date']}') < 8
then concat(from_timestamp(cast('{params['date']}' as TIMESTAMP) - interval 2 years + interval 1 month, 'yyyy-MM'), '-01')
else concat(from_timestamp(cast('{params['date']}' as TIMESTAMP) - interval 1 years + interval 1 month, 'yyyy-MM'), '-01')
end)
and flags & 1 = 1
group by 1
),
paid_bookings_last_year as (
-- только оплаченные бронирования, сделанные год назад в том же месяце что и bookings_by_day
select
case when b.affiliate_marker regexp '^[0-9]{{5,6}}.*' or c.marker regexp '^[0-9]{{5,6}}.*' then 'AF' else 'Not AF'
end as af,
count(distinct b.order_id) as paid_bookings,
sum(commission) as paid_profit
from hotel.stat_sales b
left join (
select id, host, utm_campaign, marker
from hotel.clicks
where to_date(created_at) >= to_date(
case
when year('{params['date']}') = 2021 and month('{params['date']}') < 8
then concat(from_timestamp(cast('{params['date']}' as TIMESTAMP) - interval 2 years, 'yyyy-MM'), '-01')
else concat(from_timestamp(cast('{params['date']}' as TIMESTAMP) - interval 1 years, 'yyyy-MM'), '-01')
end)
and to_date(created_at) < to_date(
case
when year('{params['date']}') = 2021 and month('{params['date']}') < 8
then concat(from_timestamp(cast('{params['date']}' as TIMESTAMP) - interval 2 years + interval 1 month, 'yyyy-MM'), '-01')
else concat(from_timestamp(cast('{params['date']}' as TIMESTAMP) - interval 1 years + interval 1 month, 'yyyy-MM'), '-01')
end)
) as c on cast(c.id as string) = b.marker
left join (
select
order_id,
max(coalesce(b.comission_summ * cur1.rate, 0)) AS commission
from hotel.stat_sales b
left join(
select *
from dictionary.currency_rates
where dst = 'USD'
) cur1 ON cur1.pdate = to_date(b.sale_date) and cur1.src = b.comission_currency
left join(
select *
from dictionary.currency_rates
where dst = 'USD'
) cur2 ON cur2.pdate = to_date(b.sale_date) and cur2.src = b.total_currency
where to_date(b.sale_date) >= to_date(
case
when year('{params['date']}') = 2021 and month('{params['date']}') < 8
then concat(from_timestamp(cast('{params['date']}' as TIMESTAMP) - interval 2 years, 'yyyy-MM'), '-01')
else concat(from_timestamp(cast('{params['date']}' as TIMESTAMP) - interval 1 years, 'yyyy-MM'), '-01')
end)
and to_date(b.sale_date) < to_date(
case
when year('{params['date']}') = 2021 and month('{params['date']}') < 8
then concat(from_timestamp(cast('{params['date']}' as TIMESTAMP) - interval 2 years + interval 1 month, 'yyyy-MM'), '-01')
else concat(from_timestamp(cast('{params['date']}' as TIMESTAMP) - interval 1 years + interval 1 month, 'yyyy-MM'), '-01')
end)
and comission_summ * cur1.rate < total_summ * cur2.rate
group by 1
) as bb on bb.order_id = b.order_id
where to_date(b.sale_date) >= to_date(
case
when year('{params['date']}') = 2021 and month('{params['date']}') < 8
then concat(from_timestamp(cast('{params['date']}' as TIMESTAMP) - interval 2 years, 'yyyy-MM'), '-01')
else concat(from_timestamp(cast('{params['date']}' as TIMESTAMP) - interval 1 years, 'yyyy-MM'), '-01')
end)
and to_date(b.sale_date) < to_date(
case
when year('{params['date']}') = 2021 and month('{params['date']}') < 8
then concat(from_timestamp(cast('{params['date']}' as TIMESTAMP) - interval 2 years + interval 1 month, 'yyyy-MM'), '-01')
else concat(from_timestamp(cast('{params['date']}' as TIMESTAMP) - interval 1 years + interval 1 month, 'yyyy-MM'), '-01')
end)
and flags & 1 = 1
-- статус 3 означает опалченное бронирование
and status = 3
group by 1
),
booking_share as (
-- отношение оплаченных бронирований к общему кол-ву броней, на который мы умножим данные за конкретный день
-- чтобы получить примерное ожидаемое кол-во бронирований и профита
select
t.af,
if(t.total_bookings != 0, p.paid_bookings/t.total_bookings, 0) as bookings_share,
if(t.total_profit != 0, p.paid_profit/t.total_profit, 0) as profit_share
from total_bookings_last_year as t
left join paid_bookings_last_year as p on t.af = p.af
),
agg as (
-- расчет ожиддаемого количества бронирований и профита с делением на фааилятов/не аффилятов
select b.pdate, b.af, b.bookings, b.bookings*s.bookings_share as corr_bookings, b.profit*s.profit_share as corr_profit
from bookings_by_day as b
left join booking_share as s on s.af = b.af
)
-- расчет итогового количества бронирований и профита
select pdate, sum(corr_bookings) as bookings, sum(corr_profit) as profit
from agg
group by 1
"""
)
hotels = impala.fetchone() or {}
impala.execute(
f"""
SELECT
count(distinct booking_id) bookings,
coalesce(cast(sum(profit*cur.rate) as INT), 0) profit
FROM avia.bookings
left join (
select *
from dictionary.currency_rates
where src = 'RUB' and dst = 'USD'
) cur on cur.pdate = to_date(booked_at)
WHERE date_trunc('day', booked_at) between '{params['last_year_week_start']}' and '{params['last_year_week_end']}'
and state = 'paid'
"""
)
prev_year_stats = impala.fetchall()[0]
impala.execute(
f"""
SELECT
count(distinct booking_id) bookings,
coalesce(cast(sum(profit*cur.rate) as INT), 0) profit
FROM avia.bookings
left join (
select *
from dictionary.currency_rates
where src = 'RUB' and dst = 'USD'
) cur on cur.pdate = to_date(booked_at)
WHERE date_trunc('day', booked_at) between '{params['before_last_year_week_start']}' and '{params['before_last_year_week_end']}'
and state = 'paid'
"""
)
before_prev_year_stats = impala.fetchall()[0]
data["searches_diff"] = diff(prev_stats["searches"], data["searches"])
data["paid_bookings_diff"] = diff(prev_stats["bookings"], data["paid_bookings"])
data["profit_diff"] = diff(prev_stats["profit"], data["profit"])
data["turnover_diff"] = diff(prev_stats["turnover"], data["turnover"])
data["profit_usd_diff"] = diff(prev_stats["profit_usd"], data["profit_usd"])
data["turnover_usd_diff"] = diff(
prev_stats["turnover_usd"], data["turnover_usd"]
)
data["cpc_clicks_diff"] = diff(prev_stats["cpc_clicks"], data["cpc_clicks"])
data["cpc_profit_diff"] = diff(prev_stats["cpc_profit"], data["cpc_profit"])
data["cpc_profit_usd_diff"] = diff(
prev_stats["cpc_profit_usd"], data["cpc_profit_usd"]
)
data["prev_year_bookings_diff"] = diff(
prev_year_stats["bookings"], this_year_stats["bookings"]
)
data["prev_year_profit_diff"] = diff(
prev_year_stats["profit"], this_year_stats["profit"]
)
data["hotel_bookings"] = hotels.get("bookings", 0)
data["hotel_profit"] = hotels.get("profit", 0)
data["this_year_bookings"] = this_year_stats["bookings"]
data["this_year_profit"] = this_year_stats["profit"]
data["before_prev_year_bookings_diff"] = diff(
before_prev_year_stats["bookings"], this_year_stats["bookings"]
)
data["before_prev_year_profit_diff"] = diff(
before_prev_year_stats["profit"], this_year_stats["profit"]
)
with transaction(conn, as_dict=True) as cursor:
cursor.execute(
f"""
SELECT bookings, bookings_domestic, bookings_international
FROM search_source_statistic_history
WHERE date >= %(date_start_chart)s AND date < %(today)s
ORDER BY date
""",
params,
)
chart_values = cursor.fetchall()
cursor.execute(
f"""
SELECT date
FROM search_source_statistic_history
WHERE date >= %(date_start_chart)s AND date < %(today)s
ORDER BY date
""",
params,
)
chart_labels = cursor.fetchall()
data["chart_values"] = [
(v["bookings_domestic"], v["bookings_international"]) for v in chart_values
]
data["chart_labels"] = [v["date"].strftime("%Y-%m-%d") for v in chart_labels]
for field in [
"searches",
"paid_bookings",
"bookings_international",
"bookings_domestic",
"turnover",
"turnover_usd",
"profit",
"profit_usd",
"cpc_clicks",
"cpc_profit",
"cpc_profit_usd",
"hotel_bookings",
"hotel_profit",
"this_year_bookings",
"this_year_profit",
]:
try:
data[field] = "{:,.0f}".format(data[field]).replace(",", " ")
except TypeError:
pass
return data
data = get_data()
TEXT = f"""
Вчера *{data['date']}* было сделано
*{data['searches']}* поисков *({data['searches_diff']}%)*
*{data['paid_bookings']}* букингов *({data['paid_bookings_diff']}%)*
на сумму *{data['turnover']} ₽* *({data['turnover_diff']}%)* или *${data['turnover_usd']}* *({data['turnover_usd_diff']}%)*
полученный профит – *{data['profit']} ₽* *({data['profit_diff']}%)* или *${data['profit_usd']}* *({data['profit_usd_diff']}%)*
полученный CPC профит – *{data['cpc_profit']} ₽* *({data['cpc_profit_diff']}%)* или *${data['cpc_profit_usd']}* *({data['cpc_profit_usd_diff']}%)*
Отельных бронирований – *{data['hotel_bookings']}* на сумму *${data['hotel_profit']}* с учетом ожидаемых отмен
Букингов за прошедшую неделю *{data['this_year_bookings']}*, *{data['prev_year_bookings_diff']}%* к {params['last_year']}, *{data['before_prev_year_bookings_diff']}%* к {params['before_last_year']} году
Денег за прошедшую неделю *${data['this_year_profit']}*, *{data['prev_year_profit_diff']}%* к {params['last_year']}, *{data['before_prev_year_profit_diff']}%* к {params['before_last_year']} году
"""
filename = draw_picture(data["chart_labels"], data["chart_values"])
image_url = drive.upload(filename)
os.remove(filename)
blocks = [
TextBlock(textwrap.dedent(TEXT)),
build_bookings_by_market_block(impala, yesterday_pt),
build_popular_directions_block(impala, params),
build_popular_international_directions_block(impala, params),
ImageBlock(image_url, title="Paid Bookings"),
]
for channel in channels:
chat_service.send_msg(
channel=channel, blocks=[block.serialize() for block in blocks]
)
def flag_by_market(market):
if market == "RU":
return ":ru:"
elif market == "KZ":
return ":flag-kz:"
elif market == "UA":
return ":flag-ua:"
elif market == "UZ":
return ":flag-uz:"
elif market == "BY":
return ":flag-by:"
elif market == "AZ":
return ":flag-az:"
elif market == "KG":
return ":flag-kg:"
elif market == "US":
return ":flag-us:"
elif market == "GE":
return ":flag-ge:"
elif market == "TJ":
return ":flag-tj:"
else:
return ":earth_americas:"
def booking_name(bookings_cnt):
if bookings_cnt % 100 in [11, 12, 13, 14]:
return "букингов"
elif bookings_cnt % 10 == 1:
return "букинг"
elif bookings_cnt % 10 in [2, 3, 4]:
return "букингa"
else:
return "букингов"
def search_name(search_cnt):
if search_cnt % 10 == 1:
return "поиск"
elif search_cnt % 10 in [2, 3, 4]:
return "поиска"
else:
return "поисков"
def draw_picture(dates: List[str], bookings: List[Tuple[int]]) -> str:
prop = fm.FontProperties(
fname=resource_filename("nydus", "resources/Rubik/Rubik-Regular.ttf"), size=15
)
prop_bold = fm.FontProperties(
fname=resource_filename("nydus", "resources/Rubik/Rubik-Bold.ttf"), size=15
)
assert len(dates) == len(bookings), "dates and bookings should have same length"
def label(xy, text, color="black", above=False):
shift = 1.0 if above else -3
y = xy[1] + shift
p = prop_bold if above else prop
plt.text(xy[0], y, text, ha="center", color=color, fontproperties=p)
scale = 50 / (max(sum(b) for b in bookings) * 1.2)
width = 7
labels = [
{
"text_below": datetime.strptime(dt, "%Y-%m-%d").strftime("%d %b"),
"text_above": f"{sum(b):,}".replace(",", " "),
"color": WEEKEND_COLOR if is_weekend(dt) else "black",
}
for dt, b in zip(dates, bookings)
]
col_cnt = len(dates)
xs = [1.5 * width * a for a in range(col_cnt)]
fig, ax = plt.subplots(figsize=(14, 5))
def patch_factory(xy, height, width, color):
if height < 1:
height = max(height, 0.5)
return FancyBboxPatch(
xy,
width=width,
height=height,
boxstyle="round,rounding_size=0.2,pad=-0.2",
facecolor=color,
edgecolor="none",
)
else:
return FancyBboxPatch(
xy,
width=width,
height=height,
boxstyle=f"round,rounding_size=0.6,pad=-0.2",
facecolor=color,
edgecolor="none",
)
def get_patches(xs, ys, labels=None):
labels = labels or []
patches = []
for x_, y_stacked in zip(xs, ys):
from itertools import accumulate
colors = ["#FF7238", "#0971D8"]
for bottom_y, y_, color in zip(
accumulate([0, *y_stacked[:-1]]), y_stacked, cycle(colors)
):
patches.append(
patch_factory(
xy=(x_ - width / 2, bottom_y * scale),
width=width,
height=y_ * scale,
color=color,
)
)
if labels:
for x_, y_stacked, lbl in zip(xs, ys, labels):
y_ = sum(y_stacked)
label((x_, 0), lbl["text_below"], color=lbl["color"])
label((x_, y_ * scale), lbl["text_above"], above=True)
return patches
patches = get_patches(xs, bookings, labels)
for patch in patches:
ax.add_patch(patch)
plt.axis("equal")
plt.axis("off")
plt.tight_layout()
filename = f"{uuid4().hex}.png"
fig.savefig(filename)
plt.close(fig)
return filename