def reliability(zone):
if zone == 'sf':
data = data = Realtime.select(
Realtime.agency, Realtime.mode,
fn.AVG(Realtime.otp).alias('otp'), fn.DATE(Realtime.timestamp).alias('timestamp')
).where(
Realtime.region == zone,
Realtime.otp > 0,
Realtime.agency.in_(['AC TRANSIT', 'Bay Area Rapid Transit', 'San Francisco Municipal Transportation Agency'])
).group_by(Realtime.agency, Realtime.mode, fn.DATE(Realtime.timestamp))
data = [model_to_dict(r) for r in data]
else:
data = Realtime.select(
Realtime.agency, Realtime.mode,
fn.AVG(Realtime.otp).alias('otp'), fn.DATE(Realtime.timestamp).alias('timestamp')
).where(
Realtime.region == zone,
Realtime.otp > 0
).group_by(Realtime.agency, Realtime.mode, fn.DATE(Realtime.timestamp))
data = [model_to_dict(r) for r in data]
for entry in data:
del(entry['region'])
del(entry['delay_abs'])
del(entry['delay_late'])
del(entry['delay_early'])
del(entry['fraction'])
del(entry['id'])
return jsonify(data)
fn.COUNT(operational.Order.id).alias(Aliases.number_of),
operational.Customer.JMBG,
operational.Customer.name.alias(Aliases.customer_name),
operational.Customer.age,
operational.Customer.gender,
operational.Seller.PIB,
operational.Seller.name.alias(Aliases.seller_name),
operational.Seller.street.alias(Aliases.seller_street),
operational.Seller.number.alias(Aliases.seller_number),
operational.District.name.alias(Aliases.district),
operational.City.name.alias(Aliases.city),
operational.Article.name.alias(Aliases.article_name),
operational.Type.name.alias(Aliases.type),
operational.Size.name.alias(Aliases.size),
operational.Color.name.alias(Aliases.color),
fn.DATE(operational.Order.created_at).alias(Aliases.date),
operational.Order.created_at.hour.alias(Aliases.hour),
fn.MIN(operational.Offer.price).alias(Aliases.lowest_price),
fn.MAX(operational.Offer.price).alias(Aliases.highest_price))\
.join(operational.Customer)\
.join(operational.OfferOrder, on=(operational.OfferOrder.order_id == operational.Order.id))\
.join(operational.Offer)\
.join(operational.Article)\
.join(operational.Seller, on=(operational.Offer.seller_id == operational.Seller.PIB)) \
.join(operational.District, on=(operational.Seller.district_id == operational.District.id))\
.join(operational.City, on=(operational.District.city_id == operational.City.id))\
.join(operational.Type, on=(operational.Type.id == operational.Article.type_id))\
.join(operational.Color, on=(operational.Color.id == operational.Article.color_id))\
.join(operational.Size, on=(operational.Size.id == operational.Article.size_id))\
.group_by(operational.Customer.JMBG,
operational.Customer.name,
def getEventRange():
# asumming 5 min interval
clientID = request.args['clientID']
rangeFrom = int(request.args['from'])
if rangeFrom == -1:
rangeFrom = getMinEventTimestamp(clientID)
rangeTo = int(request.args['to'])
client = Client.get(Client.id == clientID)
dateFrom = fromTimestamp(rangeFrom)
dateTo = fromTimestamp(rangeTo)
delta = dateTo - dateFrom
if client.private and (client.owner != g.user):
return "", 403
if (delta.days <= 1):
# range <= 1 day, return all
# 288 points
print("1 day query")
events = (Event.select().where((Event.client_id == client)
& (Event.timestamp >= dateFrom)
& (Event.timestamp <= dateTo)).order_by(
Event.timestamp))
elif (delta.days <= 3):
# range <= 3 days, 10 minutes period
# 432 points
print("3 days query")
events = (Event.select(
SQL("(timestamp - interval (MINUTE(timestamp) mod 10) MINUTE - interval SECOND(timestamp) SECOND) AS timestamp"
),
fn.AVG(Event.temperature).alias("temperature"),
fn.AVG(Event.humidity).alias("humidity"),
fn.AVG(Event.dustlevel).alias("dustlevel"),
fn.AVG(Event.colevel).alias("colevel")).where(
(Event.client_id == client) & (Event.timestamp >= dateFrom)
& (Event.timestamp <= dateTo)).group_by(
fn.DATE(Event.timestamp), fn.HOUR(Event.timestamp),
SQL("MINUTE(timestamp) div 10")).order_by(Event.timestamp))
elif (delta.days <= 7):
# range <= 1 week, 20 minutes period
# 504 points
print("7 days query")
events = (Event.select(
SQL("(timestamp - interval (MINUTE(timestamp) mod 20) MINUTE - interval SECOND(timestamp) SECOND) AS timestamp"
),
fn.AVG(Event.temperature).alias("temperature"),
fn.AVG(Event.humidity).alias("humidity"),
fn.AVG(Event.dustlevel).alias("dustlevel"),
fn.AVG(Event.colevel).alias("colevel")).where(
(Event.client_id == client) & (Event.timestamp >= dateFrom)
& (Event.timestamp <= dateTo)).group_by(
fn.DATE(Event.timestamp), fn.HOUR(Event.timestamp),
SQL("MINUTE(timestamp) div 20")).order_by(Event.timestamp))
elif (delta.days <= 30):
# range <= 1 month, 2 hour period, average
# 372 points per type
print("30 days query")
events = (Event.select(
SQL("(timestamp - interval MINUTE(timestamp) MINUTE - interval SECOND(timestamp) SECOND) AS timestamp"
),
fn.AVG(Event.temperature).alias("temperature"),
fn.AVG(Event.humidity).alias("humidity"),
fn.AVG(Event.dustlevel).alias("dustlevel"),
fn.AVG(Event.colevel).alias("colevel")).where(
(Event.client_id == client) & (Event.timestamp >= dateFrom)
& (Event.timestamp <= dateTo)).group_by(
fn.DATE(Event.timestamp),
SQL("HOUR(timestamp) div 2")).order_by(Event.timestamp))
else:
print(">1 month query")
# range >=1 month, 1 day period, average
# min 365/366 datapoints per type
events = (Event.select(
fn.DATE(Event.timestamp).alias("timestamp"),
fn.AVG(Event.temperature).alias("temperature"),
fn.AVG(Event.humidity).alias("humidity"),
fn.AVG(Event.dustlevel).alias("dustlevel"),
fn.AVG(Event.colevel).alias("colevel")).where(
(Event.client_id == client) & (Event.timestamp >= dateFrom)
& (Event.timestamp <= dateTo)).group_by(
fn.DATE(Event.timestamp)).order_by(Event.timestamp))
return json.jsonify(
[event.toFrontendObject(include_id=False) for event in events])
def fetch_by_symbol_field_date(symbol, field, date):
query = QuoteModel\
.select()\
.where((QuoteModel.symbol == symbol) & (QuoteModel.field == field) & (fn.DATE(QuoteModel.date) == date))
return [item for item in query]
async def _get_compare_img(self, guild_id: int, expression1: str,
expression2: str, periode: int) -> Any:
jour_debut = date.today() - timedelta(days=periode)
jour_fin = date.today() - timedelta(days=1)
tracking_cog = get_tracking_cog(self.bot)
db = tracking_cog.tracked_guilds[guild_id]
guild_name = self.bot.get_guild(guild_id)
with db:
with db.bind_ctx([Message]):
# Messages de l'utilisateur dans la période
query = (Message.select(
fn.DATE(Message.timestamp).alias("date"),
(fn.SUM(Message.content.contains(expression1)) /
fn.COUNT(Message.message_id)).alias("expression1"),
(fn.SUM(Message.content.contains(expression2)) /
fn.COUNT(Message.message_id)).alias("expression2"),
).where(fn.DATE(Message.timestamp) >= jour_debut).where(
fn.DATE(Message.timestamp) <= jour_fin).group_by(
fn.DATE(Message.timestamp)))
cur = db.cursor()
query_sql = cur.mogrify(*query.sql())
df = pandas.read_sql(query_sql, db.connection())
# Si emote custom : simplifier le nom pour titre DW
custom_emoji_str = emoji_to_str(expression1)
if custom_emoji_str:
expression1 = custom_emoji_str
custom_emoji_str = emoji_to_str(expression2)
if custom_emoji_str:
expression2 = custom_emoji_str
# Renommage des colonnes
df = df.rename(columns={
"expression1": expression1,
"expression2": expression2
})
# Remplir les dates manquantes
df = df.set_index("date")
df.index = pandas.DatetimeIndex(df.index)
df.reset_index(level=0, inplace=True)
df = df.rename(columns={"index": "date"})
# Rolling average
df[expression1] = df.get(expression1).rolling(ROLLING_AVERAGE).mean()
df[expression2] = df.get(expression2).rolling(ROLLING_AVERAGE).mean()
title_lines = textwrap.wrap(
f"<b>'{expression1}'</b> vs <b>'{expression2}'</b>")
title_lines.append(f"<i style='font-size: 10px'>Sur {guild_name}.</i>")
title = "<br>".join(title_lines)
fig: go.Figure = px.line(
df,
x="date",
y=[expression1, expression2],
color_discrete_sequence=["yellow", "#4585e6"],
template="plotly_dark",
title=title,
render_mode="svg",
labels={
"date": "",
"variable": ""
},
)
# Hide y-axis
fig.update_yaxes(visible=False, fixedrange=True)
# Legend position
fig.update_layout(legend=dict(
title=None,
orientation="h",
y=1,
yanchor="bottom",
x=0.5,
xanchor="center",
))
fig.add_layout_image(
dict(
source="https://i.imgur.com/Eqy58rg.png",
xref="paper",
yref="paper",
x=1.1,
y=-0.22,
sizex=0.25,
sizey=0.25,
xanchor="right",
yanchor="bottom",
opacity=0.8,
))
return fig.to_image(format="png", scale=2)
async def _comparedw(self, ctx: commands.Context, guild_id: int,
terme1: str, terme2: str):
"""Compare deux tendances"""
if False in (str_input_ok(terme1), str_input_ok(terme2)):
await ctx.send(
"Je ne peux pas faire de tendance avec une expression vide.")
return
# Si les deux mêmes, faire un _trend
if terme1 == terme2:
return await self._trenddw(ctx, guild_id, terme1)
temp_msg: discord.Message = await ctx.send(
f"Je génère les tendances comparées de **{terme1}** et **{terme2}**... 🐝"
)
jour_debut = date.today() - timedelta(days=PERIODE)
jour_fin = date.today() - timedelta(days=1)
tracking_cog = get_tracking_cog(self.bot)
db = tracking_cog.tracked_guilds[guild_id]
guild_name = self.bot.get_guild(guild_id)
with db:
with db.bind_ctx([Message]):
# Messages de l'utilisateur dans la période
query = (Message.select(
fn.DATE(Message.timestamp).alias("date"),
(fn.SUM(Message.content.contains(terme1)) /
fn.COUNT(Message.message_id)).alias("terme1"),
(fn.SUM(Message.content.contains(terme2)) /
fn.COUNT(Message.message_id)).alias("terme2"),
).where(fn.DATE(Message.timestamp) >= jour_debut).where(
fn.DATE(Message.timestamp) <= jour_fin).group_by(
fn.DATE(Message.timestamp)))
cur = db.cursor()
query_sql = cur.mogrify(*query.sql())
msg_par_jour = pandas.read_sql(query_sql, db.connection())
# Si emote custom : simplifier le nom pour titre DW
custom_emoji_str = emoji_to_str(terme1)
if custom_emoji_str:
terme1 = custom_emoji_str
custom_emoji_str = emoji_to_str(terme2)
if custom_emoji_str:
terme2 = custom_emoji_str
# Renommage des colonnes
msg_par_jour = msg_par_jour.rename(columns={
"terme1": terme1,
"terme2": terme2
})
# Remplir les dates manquantes
msg_par_jour = msg_par_jour.set_index("date")
msg_par_jour.index = pandas.DatetimeIndex(msg_par_jour.index)
msg_par_jour.reset_index(level=0, inplace=True)
msg_par_jour = msg_par_jour.rename(columns={"index": "date"})
# Rolling average
msg_par_jour[terme1] = msg_par_jour.get(terme1).rolling(
ROLLING_AVERAGE).mean()
msg_par_jour[terme2] = msg_par_jour.get(terme2).rolling(
ROLLING_AVERAGE).mean()
properties = {
"annotate": {
"notes":
f"Moyenne mobile sur les {ROLLING_AVERAGE} derniers jours. Insensible à la casse et aux accents."
},
"visualize": {
"labeling": "top",
"base-color": "#DFC833",
"line-widths": {
terme1: 1,
terme2: 1
},
"custom-colors": {
terme1: "#DFC833",
terme2: 0
},
"y-grid": "off",
},
}
# Send chart
await self.__send_chart(
ctx,
f"'{terme1}' vs '{terme2}'",
f"Tendances dans les messages postés sur {guild_name}",
"d3-lines",
msg_par_jour,
properties,
)
await temp_msg.delete()
async def _get_trend_img(self, guild_id: int, terme: str,
periode: int) -> Any:
jour_debut = date.today() - timedelta(days=periode)
jour_fin = date.today() - timedelta(days=1)
tracking_cog = get_tracking_cog(self.bot)
db = tracking_cog.tracked_guilds[guild_id]
guild_name = self.bot.get_guild(guild_id)
with db:
with db.bind_ctx([Message]):
# Messages de l'utilisateur dans la période
query = (Message.select(
fn.DATE(Message.timestamp).alias("date"),
(fn.SUM(Message.content.contains(terme)) /
fn.COUNT(Message.message_id)).alias("messages"),
).where(fn.DATE(Message.timestamp) >= jour_debut).where(
fn.DATE(Message.timestamp) <= jour_fin).group_by(
fn.DATE(Message.timestamp)))
# Exécution requête SQL
cur = db.cursor()
query_sql = cur.mogrify(*query.sql())
df = pandas.read_sql(query_sql, db.connection())
# Remplir les dates manquantes
df = df.set_index("date")
df.index = pandas.DatetimeIndex(df.index)
df.reset_index(level=0, inplace=True)
df = df.rename(columns={"index": "date"})
# Rolling average
df["messages"] = df.rolling(ROLLING_AVERAGE).mean()
# Si emote custom : simplifier le nom pour titre DW
custom_emoji_str = emoji_to_str(terme)
if custom_emoji_str:
terme = custom_emoji_str
title_lines = textwrap.wrap(f"Tendances de <b>'{terme}'</b>")
title_lines.append(f"<i style='font-size: 10px'>Sur {guild_name}.</i>")
title = "<br>".join(title_lines)
fig: go.Figure = px.area(
df,
x="date",
y="messages",
color_discrete_sequence=["yellow"],
# line_shape="spline",
template="plotly_dark",
title=title,
labels={
"date": "",
"messages": ""
},
)
fig.add_layout_image(
dict(
source="https://i.imgur.com/Eqy58rg.png",
xref="paper",
yref="paper",
x=1.1,
y=-0.22,
sizex=0.25,
sizey=0.25,
xanchor="right",
yanchor="bottom",
opacity=0.8,
))
return fig.to_image(format="png", scale=2)
