def receiver_plot(receiver_name):
import matplotlib.pyplot as plt
from io import BytesIO
receiver_query = session.query(func.floor(AircraftBeacon.phi),
func.max(AircraftBeacon.radius/1000)) \
.filter(AircraftBeacon.receiver_name == receiver_name) \
.filter(AircraftBeacon.radius is not None) \
.group_by(func.floor(AircraftBeacon.phi)) \
.order_by(func.floor(AircraftBeacon.phi))
rows = receiver_query.all()
xs, ys = zip(*rows)
xs = [x / 180 * 3.141592653 for x in xs]
ax = plt.axes(polar=True)
ax.set_theta_zero_location('N')
ax.set_theta_direction('clockwise')
plt.title('Maximum range')
plt.plot(xs, ys, 'r')
plt.ylim(0, 100)
img = BytesIO()
plt.savefig(img)
plt.close()
img.seek(0)
return send_file(img, mimetype='image/png')
def alchemy_expression(self):
dt = self.product
grid_spec = self.product.grid_spec
doc = _jsonb_doc_expression(dt.metadata_type)
projection_offset = _projection_doc_offset(dt.metadata_type)
# Calculate tile refs
geo_ref_points_offset = projection_offset + ['geo_ref_points']
center_point = func.ST_Centroid(
func.ST_Collect(
_gis_point(doc, geo_ref_points_offset + ['ll']),
_gis_point(doc, geo_ref_points_offset + ['ur']),
))
# todo: look at grid_spec crs. Use it for defaults, conversion.
size_x, size_y = (grid_spec.tile_size or (1000.0, 1000.0))
origin_x, origin_y = grid_spec.origin
return func.concat(
func.floor(
(func.ST_X(center_point) - origin_x) / size_x).cast(String),
'_',
func.floor(
(func.ST_Y(center_point) - origin_y) / size_y).cast(String),
)
def postgres_aggregates(self, resolution):
if isinstance(resolution, basestring):
try:
resolution = float(resolution)
except ValueError:
resolution = self.resolution
return [
label('cell_x', func.floor(ST_X(Column('cell')) / resolution) * resolution),
label('cell_y', func.floor(ST_Y(Column('cell')) / resolution) * resolution)]
def get_ten_minute_core_fv_series_in_period(db, start_time, end_time):
"""
Get the ten-minute open-price series in this period from our core fv datums.
Like most of our periods, this one is inclusive on the left but not the right. So
getting this series for a full day [Nov 1st, Nov 2nd) will give you 24*6 == 144
datapoints, but getting this series for a day and one minute will likely give you
25.
"""
fundamental_value_series = []
fv_series = db.query(
Datum.time_created,
Datum.numeric_value,
)\
.filter(Datum.time_created >= start_time)\
.filter(Datum.time_created < end_time)\
.filter(Datum.datum_type.like('%_CORE_FUNDAMENTAL_VALUE'))\
.group_by(
func.date(Datum.time_created),
func.hour(Datum.time_created),
func.floor((func.minute(Datum.time_created) / 10))*10
)\
.all()
# Do a teeny bit of sanity checking.
delta = end_time - start_time
ten_minutes = math.floor((delta.days * 86400 + delta.seconds) / 600)
assert (len(fv_series) == ten_minutes)
fundamental_value_series = convert_datum_series_to_time_series(fv_series)
return fundamental_value_series
def stalenessRating(self):
""" Return the Queryable staleness rating of the mastery """
return func.coalesce(
cast(
func.floor(
func.extract('epoch',
func.now() - self.lastCorrectAnswer) / 86400),
db.Integer) / StalenessPeriod.days, 0)
def device_plot2(address):
import matplotlib.pyplot as plt
import numpy as np
from matplotlib.mlab import griddata
from io import BytesIO
sq = session.query(func.floor(case([(AircraftBeacon.theta-AircraftBeacon.track+180 > 0,
AircraftBeacon.theta-AircraftBeacon.track+180),
(AircraftBeacon.theta-AircraftBeacon.track+180 < 0,
AircraftBeacon.theta-AircraftBeacon.track+180+360)])).label('direction'),
AircraftBeacon.radius,
AircraftBeacon.signal_strength) \
.filter(AircraftBeacon.receiver_name == 'Koenigsdf') \
.filter(AircraftBeacon.address == address) \
.filter(AircraftBeacon.radius is not None).subquery()
device_query = session.query(func.floor(sq.c.direction/15)*15,
func.floor(sq.c.radius/1000)*1000,
func.avg(sq.c.signal_strength)) \
.group_by(func.floor(sq.c.direction/15)*15,
func.floor(sq.c.radius/1000)*1000)
rows = device_query.all()
xs, ys, zs = zip(*rows)
xs = [x / 180 * 3.141592653 for x in xs]
xi = np.linspace(0, 2*np.pi, 360)
yi = np.linspace(0, 10000, 5)
zi = griddata(xs, ys, zs, xi, yi, interp='linear')
ax = plt.axes(polar=True)
ax.set_theta_zero_location('N')
ax.set_theta_direction('clockwise')
plt.title('Maximum range')
c = plt.contourf(xi, yi, zi, 5)
cb = plt.colorbar(c)
cb.set_label("Signal strength [db]")
img = BytesIO()
plt.savefig(img)
plt.close()
img.seek(0)
return send_file(img, mimetype='image/png')
def get(self):
'''방문시 연령대(10세 단위)별 방문 수'''
Json = dict()
q = db.session.query(
func.floor(
extract('year',
func.age(v.visit_start_datetime, p.birth_datetime)) /
10) * 10, func.count()
).join(p, p.person_id == v.person_id).group_by(
func.floor(
extract('year',
func.age(v.visit_start_datetime, p.birth_datetime)) /
10) *
10).order_by(
func.floor(
extract('year',
func.age(v.visit_start_datetime, p.birth_datetime))
/ 10) * 10)
for name, num in q:
Json[str(int(name)) + "대"] = num
return Json
def count_visit_by_age_group():
# select age_group, person_id
ages = db.session.query(
(func.floor(extract('year', (func.age(Person.birth_datetime))) / 10) * 10).label('age_group'),
Person.person_id.label('person_id')
).subquery()
# group by age_group
result = db.session \
.query(ages.c.age_group, func.count(ages.c.age_group)) \
.filter(VisitOccurrence.person_id == ages.c.person_id) \
.group_by(ages.c.age_group) \
.all()
return dict(result)
def coindays_destroyed(self, since=None, interval=None):
if interval is not None:
query = self.session.query(
sqlfunc.min(CoinDaysDestroyed.timestamp),
sqlfunc.max(CoinDaysDestroyed.timestamp),
sqlfunc.sum(CoinDaysDestroyed.coindays))
if since is not None:
query = query.filter(CoinDaysDestroyed.timestamp >= since)
query = query.group_by(
sqlfunc.floor(
sqlfunc.to_seconds(CoinDaysDestroyed.timestamp) /
interval)).order_by(CoinDaysDestroyed.timestamp.asc())
return [
dict(zip(('start', 'end', 'coindaysdestroyed'), period))
for period in query.all()
]
query = self.session.query(sqlfunc.sum(CoinDaysDestroyed.coindays))
if since is not None:
query = query.filter(CoinDaysDestroyed.timestamp >= since)
destroyed = query.first()[0]
return round(float(destroyed), 5) if destroyed != None else 0.0
def get_custom_channel_stats(db: Connection, channel_id: int, period_start: datetime, period_end: datetime,
average_interval: int = 60) -> List:
"""
Get channel's stats for specified period.
"""
query = select([ENTRIES.c.timestamp, func.avg(ENTRIES.c.value)]) \
.select_from(ENTRIES) \
.where(and_(ENTRIES.c.channel_id == channel_id,
between(ENTRIES.c.timestamp, period_start, period_end))) \
.group_by(func.date(ENTRIES.c.timestamp),
func.hour(ENTRIES.c.timestamp),
func.floor(func.minute(ENTRIES.c.timestamp) / average_interval)) \
.order_by(ENTRIES.c.timestamp.asc())
result = db.execute(query)
datetimes = datetimes_between(period_start, period_end, average_interval * 60)
average_by_datetime = OrderedDict.fromkeys(datetimes)
for time, value in result:
dt = time.replace(minute=0, second=0)
average_by_datetime[dt] = value
return average_by_datetime.items()
async def rand(self, ctx: commands.Context, *, args: PlaybackArgumentConverter() = _DEFAULT_PLAYBACK_ARGUMENTS):
"""
Play a random sound.
:param args: The volume/speed of playback, in format v[XX%] s[SS%]. e.g. v50 s100 for 50% sound, 100% speed.
"""
sound = await self.bot.db.fetch_one(
select([sound_names.c.sound_id, sound_names.c.name])
.where(
and_(
sound_names.c.guild_id == ctx.guild.id,
~sound_names.c.is_alias
))
.offset(
func.floor(
func.random() *
select([func.count()])
.select_from(sound_names)
.where(sound_names.c.guild_id == ctx.guild.id)))
.limit(1)
)
log.debug(f'Playing random sound {sound[sound_names.c.name]}.')
await ctx.invoke(self.play, sound, args=args)
def get_peaks_query_group_by_time_step(target_type, target_id, step,
metric_type, time_from, time_until):
time_until = round_time(time_from, time_until, step=step)
shift = get_shift(time_from, step)
group_time = (func.floor(
(func.extract("epoch", BatchStoryPeaks.time) - shift) / step)) * step
query_creator = MetricPeakGroupFilter(session=db.session)
(query_creator.filter_by_metric_type(metric_type).filter_by_time_period(
time_from=time_from, time_until=time_until).set_query_entities(
func.max(MetricPeak.value).label("peaks"),
group_time.label("step"),
func.min(func.extract("epoch",
BatchStoryPeaks.time)).label("time"),
MetricPeak.type.label("type"),
).group_by("step", "type").order_by("time"))
if target_type == "cluster":
query_creator.filter_by_cluster_id(target_id)
elif target_type == "server":
query_creator.filter_by_server_id(target_id)
elif target_type == "services_group":
query_creator.filter_by_service_group_id(target_id)
elif target_type == "service":
query_creator.filter_by_service_id(target_id)
return query_creator
def device_plot(address):
import matplotlib.pyplot as plt
from io import BytesIO
receiver_name = 'Koenigsdf'
device_query = session.query(func.floor(AircraftBeacon.radius/1000),
func.avg(AircraftBeacon.signal_strength)) \
.filter(AircraftBeacon.address == address) \
.filter(AircraftBeacon.receiver_name == receiver_name) \
.group_by(func.floor(AircraftBeacon.radius/1000)) \
.order_by(func.floor(AircraftBeacon.radius/1000))
rows = device_query.all()
xs, ys = zip(*rows)
plt.plot(xs, ys, 'b', marker='o', label=address)
receiver_query = session.query(func.floor(AircraftBeacon.radius/1000),
func.avg(AircraftBeacon.signal_strength)) \
.filter(AircraftBeacon.receiver_name == receiver_name) \
.group_by(func.floor(AircraftBeacon.radius/1000)) \
.order_by(func.floor(AircraftBeacon.radius/1000))
rows = receiver_query.all()
xs, ys = zip(*rows)
plt.plot(xs, ys, 'g--', label=receiver_name)
plt.title('Signal strength over distance')
plt.xlim(0, 40)
plt.xlabel('Distance [km]')
plt.ylabel('Signal strength [db]')
plt.legend(loc='upper right')
plt.grid(True, color='0.75')
img = BytesIO()
plt.savefig(img)
img.seek(0)
return send_file(img, mimetype='image/png')
def execute(self, message, user, params):
alliance=Alliance()
race=None
size_mod=None
size=None
value_mod=None
value=None
bash=False
attacker=user.planet
cluster=None
params=params.group(1).split()
for p in params:
m=self.bashre.match(p)
if m and not bash:
bash=True
continue
m=self.clusterre.match(p)
if m and not cluster:
cluster=int(m.group(1))
m=self.racere.match(p)
if m and not race:
race=m.group(1)
continue
m=self.rangere.match(p)
if m and not size and int(m.group(2)) < 32768:
size_mod=m.group(1) or '>'
size=m.group(2)
continue
m=self.rangere.match(p)
if m and not value:
value_mod=m.group(1) or '<'
value=m.group(2)
continue
m=self.alliancere.match(p)
if m and not alliance.name and not self.clusterre.match(p):
alliance = Alliance(name="Unknown") if m.group(1).lower() == "unknown" else Alliance.load(m.group(1))
if alliance is None:
message.reply("No alliance matching '%s' found" % (m.group(1),))
return
continue
maxcap = PA.getfloat("roids","maxcap")
mincap = PA.getfloat("roids","mincap")
modifier = (cast(Planet.value,Float).op("/")(float(attacker.value))).op("^")(0.5)
caprate = func.float8larger(mincap,func.float8smaller(modifier.op("*")(maxcap),maxcap))
maxcap = cast(func.floor(cast(Planet.size,Float).op("*")(caprate)),Integer)
bravery = (func.float8larger(0.0,( func.float8smaller(2.0, cast(Planet.value,Float).op("/")(float(attacker.value)))-0.1) * (func.float8smaller(2.0, cast(Planet.score,Float).op("/")(float(attacker.score)))-0.2))).op("*")(10.0)
xp_gain = cast(func.floor(maxcap.op("*")(bravery)),Integer)
Q = session.query(Planet, Intel, xp_gain.label("xp_gain"))
if alliance.id:
Q = Q.join(Planet.intel)
Q = Q.filter(Intel.alliance == alliance)
else:
Q = Q.outerjoin(Planet.intel)
if alliance.name:
Q = Q.filter(Intel.alliance == None)
Q = Q.filter(Planet.active == True)
if race:
Q = Q.filter(Planet.race.ilike(race))
if size:
Q = Q.filter(Planet.size.op(size_mod)(size))
if value:
Q = Q.filter(Planet.value.op(value_mod)(value))
if bash:
Q = Q.filter(or_(Planet.value.op(">")(attacker.value*PA.getfloat("bash","value")),
Planet.score.op(">")(attacker.score*PA.getfloat("bash","score"))))
if cluster:
Q = Q.filter(Planet.x == cluster)
Q = Q.order_by(desc("xp_gain"))
Q = Q.order_by(desc(Planet.idle))
Q = Q.order_by(desc(Planet.value))
result = Q[:6]
if len(result) < 1:
reply="No"
if race:
reply+=" %s"%(race,)
reply+=" planets"
if alliance.name:
reply+=" in intel matching Alliance: %s"%(alliance.name,)
else:
reply+=" matching"
if size:
reply+=" Size %s %s" % (size_mod,size)
if value:
reply+=" Value %s %s" % (value_mod,value)
message.reply(reply)
return
replies = []
for planet, intel, xp_gain in result[:5]:
reply="%s:%s:%s (%s)" % (planet.x,planet.y,planet.z,planet.race)
reply+=" Value: %s Size: %s Scoregain: %d" % (planet.value,planet.size, xp_gain*60)
if intel:
if intel.nick:
reply+=" Nick: %s" % (intel.nick,)
if not alliance.name and intel.alliance:
reply+=" Alliance: %s" % (intel.alliance.name,)
replies.append(reply)
if len(result) > 5:
replies[-1]+=" (Too many results to list, please refine your search)"
message.reply("\n".join(replies))
def execute(self, message, user, params):
alliance = Alliance()
race = None
size_mod = None
size = None
value_mod = None
value = None
bash = False
attacker = user.planet
cluster = None
params = params.group(1).split()
for p in params:
m = self.bashre.match(p)
if m and not bash:
bash = True
continue
m = self.clusterre.match(p)
if m and not cluster:
cluster = int(m.group(1))
m = self.racere.match(p)
if m and not race:
race = m.group(1)
continue
m = self.rangere.match(p)
if m and not size and int(m.group(2)) < 32768:
size_mod = m.group(1) or '>'
size = m.group(2)
continue
m = self.rangere.match(p)
if m and not value:
value_mod = m.group(1) or '<'
value = m.group(2)
continue
m = self.alliancere.match(p)
if m and not alliance.name and not self.clusterre.match(p):
alliance = Alliance(name="Unknown") if m.group(
1).lower() == "unknown" else Alliance.load(m.group(1))
if alliance is None:
message.reply("No alliance matching '%s' found" %
(m.group(1), ))
return
continue
maxcap = PA.getfloat("roids", "maxcap")
mincap = PA.getfloat("roids", "mincap")
modifier = (cast(Planet.value,
Float).op("/")(float(attacker.value))).op("^")(0.5)
caprate = func.float8larger(
mincap, func.float8smaller(modifier.op("*")(maxcap), maxcap))
maxcap = cast(func.floor(cast(Planet.size, Float).op("*")(caprate)),
Integer)
bravery = (func.float8larger(
0.0,
(func.float8smaller(
2.0,
cast(Planet.value, Float).op("/")(float(attacker.value))) -
0.1) * (func.float8smaller(
2.0,
cast(Planet.score, Float).op("/")(float(attacker.score))) -
0.2))).op("*")(10.0)
xp_gain = cast(func.floor(maxcap.op("*")(bravery)), Integer)
Q = session.query(Planet, Intel, xp_gain.label("xp_gain"))
if alliance.id:
Q = Q.join(Planet.intel)
Q = Q.filter(Intel.alliance == alliance)
else:
Q = Q.outerjoin(Planet.intel)
if alliance.name:
Q = Q.filter(Intel.alliance == None)
Q = Q.filter(Planet.active == True)
if race:
Q = Q.filter(Planet.race.ilike(race))
if size:
Q = Q.filter(Planet.size.op(size_mod)(size))
if value:
Q = Q.filter(Planet.value.op(value_mod)(value))
if bash:
Q = Q.filter(
or_(
Planet.value.op(">")(attacker.value *
PA.getfloat("bash", "value")),
Planet.score.op(">")(attacker.score *
PA.getfloat("bash", "score"))))
if cluster:
Q = Q.filter(Planet.x == cluster)
Q = Q.order_by(desc("xp_gain"))
Q = Q.order_by(desc(Planet.idle))
Q = Q.order_by(desc(Planet.value))
result = Q[:6]
if len(result) < 1:
reply = "No"
if race:
reply += " %s" % (race, )
reply += " planets"
if alliance.name:
reply += " in intel matching Alliance: %s" % (alliance.name, )
else:
reply += " matching"
if size:
reply += " Size %s %s" % (size_mod, size)
if value:
reply += " Value %s %s" % (value_mod, value)
message.reply(reply)
return
replies = []
for planet, intel, xp_gain in result[:5]:
reply = "%s:%s:%s (%s)" % (planet.x, planet.y, planet.z,
planet.race)
reply += " Value: %s Size: %s Scoregain: %d" % (
planet.value, planet.size,
xp_gain * PA.getint("numbers", "xp_value"))
if intel:
if intel.nick:
reply += " Nick: %s" % (intel.nick, )
if not alliance.name and intel.alliance:
reply += " Alliance: %s" % (intel.alliance.name, )
replies.append(reply)
if len(result) > 5:
replies[
-1] += " (Too many results to list, please refine your search)"
message.reply("\n".join(replies))
def get(self,
variable,
group_by,
start_date=None,
end_date=None,
only_loc=None,
use_ids=None,
date_variable=None,
additional_variables=None,
group_by_variables=None):
variable = str(variable)
if not only_loc:
if "only_loc" in request.args:
only_loc = request.args["only_loc"]
else:
only_loc = g.allowed_location
if not is_allowed_location(only_loc, g.allowed_location):
return {}
start_date, end_date = fix_dates(start_date, end_date)
if "use_ids" in request.args.keys() or use_ids:
use_ids = True
else:
use_ids = False
if date_variable:
date_conditions = [
func.to_date(Data.variables[date_variable].astext,
"YYYY-MM-DDTHH-MI-SS") >= start_date,
func.to_date(Data.variables[date_variable].astext,
"YYYY-MM-DDTHH-MI-SS") < end_date
]
else:
date_conditions = [Data.date >= start_date, Data.date < end_date]
if "location" in variable:
location_id = variable.split(":")[1]
conditions = date_conditions + [
or_(loc == location_id
for loc in (Data.country, Data.zone, Data.region,
Data.district, Data.clinic))
]
else:
conditions = [Data.variables.has_key(variable)] + date_conditions
if additional_variables:
# add additional variable filters if there are and
for i in additional_variables:
conditions.append(Data.variables.has_key(i))
if only_loc:
conditions += [
or_(loc == only_loc
for loc in (Data.country, Data.zone, Data.region,
Data.district, Data.clinic))
]
epi_year_start = meerkat_abacus.util.epi_week.epi_year_start_date(
start_date)
# Determine which columns we want to extract from the Data table
columns_to_extract = [func.count(Data.id).label('value')]
if date_variable:
columns_to_extract.append(
func.floor(
extract(
'days',
func.to_date(Data.variables[date_variable].astext,
"YYYY-MM-DDTHH-MI-SS") - epi_year_start) /
7 + 1).label("week"))
else:
columns_to_extract.append(
func.floor(
extract('days', Data.date - epi_year_start) / 7 +
1).label("week"))
# We want to add the columns to extract based on the group_by value
# in addition we create a names dict that translates ids to names
if "locations" in group_by:
# If we have locations in group_by we also specify the level at
# which we want to group the locations, clinic, district or region
if ":" in group_by:
level = group_by.split(":")[1]
else:
level = "clinic"
locations = abacus_util.get_locations(db.session)
ids = locations.keys()
names = get_locations_by_level(level, only_loc)
columns_to_extract += [getattr(Data, level, None)]
group_by_query = level
else:
if not group_by_variables:
names = get_variables(group_by)
else:
names = group_by_variables
if len(names) == 0:
return {}
ids = names.keys()
for i in ids:
columns_to_extract.append(
Data.variables.has_key(str(i)).label("id" + str(i)))
group_by_query = ",".join(["id" + str(i) for i in ids])
if use_ids:
names = {vid: vid for vid in names.keys()}
start_epi_week = abacus_util.epi_week.epi_week_for_date(start_date)[1]
end_epi_week = abacus_util.epi_week.epi_week_for_date(end_date)[1]
# How we deal with start and end dates in different years
if start_date.year != end_date.year:
end_epi_week += 53 * (end_date.year - start_date.year)
# DB Query
results = db.session.query(*tuple(columns_to_extract)).filter(
*conditions).group_by("week," + group_by_query)
# Assemble return dict
ret = {}
for n in names.values():
ret[n] = {
"total": 0,
"weeks":
{i: 0
for i in range(start_epi_week, end_epi_week + 1)}
}
for r in results:
# r = (number, week, other_columns_to_extract
if "locations" in group_by:
# r[2] = location
if r[2]:
ret[names[r[2]]]["total"] += r[0]
ret[names[r[2]]]["weeks"][int(r[1])] = int(r[0])
else:
# r[2:] are the ids that the record has
for i, i_d in enumerate(ids):
if r[i + 2]:
ret[names[i_d]]["total"] += r[0]
ret[names[i_d]]["weeks"][int(r[1])] = int(r[0])
return ret
def stalenessRating(self):
""" Return the Queryable staleness rating of the mastery """
return func.coalesce(cast(func.floor(func.extract('epoch', func.now()-self.lastCorrectAnswer)/86400), db.Integer)/StalenessPeriod.days, 0)
def stat_hourly(page, rows, offset, sidx, sord, date_filter, queues_filter):
# Hourly distribution (30 min sections)
if db_engine=='oracle':
xh = func.floor((sql.cast(func.to_char(Queue_log.timestamp, 'HH24'), types.INT) *60 + \
sql.cast(func.to_char(Queue_log.timestamp, 'MI'), types.INT) ) / 30)
else: # PostgreSql
xh = func.floor((extract('hour', Queue_log.timestamp) * 60 + \
extract('min', Queue_log.timestamp) ) / 30)
xh = xh.label('xhour')
# h_incoming = DBSession.query(xh, func.count('*').label('incoming')).\
# filter(Queue_log.queue_event_id==Queue_event.qe_id).\
# filter(Queue_event.event=='ENTERQUEUE').filter(queues_filter)
# if date_filter is not None:
# h_incoming = h_incoming.filter(date_filter)
# h_incoming = h_incoming.group_by(xh).order_by(xh).subquery()
h_connect = DBSession.query(
xh, func.count('*').label('count')).\
filter(queues_filter). \
filter(Queue_log.queue_event_id==Queue_event.qe_id).\
filter(Queue_event.event=='CONNECT').filter(queues_filter)
if date_filter is not None:
h_connect = h_connect.filter(date_filter)
h_connect = h_connect.group_by(xh).subquery()
h_abandon = DBSession.query(
xh, func.count('*').label('count')).\
filter(queues_filter). \
filter(Queue_log.queue_event_id==Queue_event.qe_id).\
filter(Queue_event.event=='ABANDON').filter(queues_filter)
if date_filter is not None:
h_abandon = h_abandon.filter(date_filter)
h_abandon = h_abandon.group_by(xh).subquery()
h_closed = DBSession.query(
xh, func.count('*').label('count')).\
filter(queues_filter). \
filter(Queue_log.queue_event_id==Queue_event.qe_id).\
filter(Queue_event.event=='CLOSED').filter(queues_filter)
if date_filter is not None:
h_closed = h_closed.filter(date_filter)
h_closed = h_closed.group_by(xh).subquery()
h_dissuasion = DBSession.query(
xh, func.count('*').label('count')).\
filter(queues_filter). \
filter(Queue_log.queue_event_id==Queue_event.qe_id).\
filter(Queue_event.event=='DISSUASION').filter(queues_filter)
if date_filter is not None:
h_dissuasion = h_dissuasion.filter(date_filter)
h_dissuasion = h_dissuasion.group_by(xh).subquery()
q = DBSession.query(xh, func.count('*').label('incoming'),
h_abandon.c.count.label('abandon'),
h_connect.c.count.label('connect'),
h_dissuasion.c.count.label('dissuasion'),
h_closed.c.count.label('closed')).\
filter(Queue_log.queue_event_id==Queue_event.qe_id). \
filter(Queue_event.event=='ENTERQUEUE').filter(queues_filter). \
filter(queues_filter). \
outerjoin((h_connect, xh==h_connect.c.xhour)). \
outerjoin((h_abandon, xh==h_abandon.c.xhour)). \
outerjoin((h_closed, xh==h_closed.c.xhour)). \
outerjoin((h_dissuasion, xh==h_dissuasion.c.xhour)). \
group_by(xh,h_abandon.c.count, h_connect.c.count,
h_dissuasion.c.count, h_closed.c.count)
if date_filter is not None:
q = q.filter(date_filter)
if sidx=='incoming':
q = q.order_by(desc(func.count('*'))) if sord=='desc' \
else q.order_by(func.count('*'))
elif sidx=='connect':
q = q.order_by(desc(h_connect.c.count)) if sord=='desc' \
else q.order_by(h_connect.c.count)
elif sidx=='abandon':
q = q.order_by(desc(h_abandon.c.count)) if sord=='desc' \
else q.order_by(h_abandon.c.count)
elif sidx=='dissuasion':
q = q.order_by(desc(h_dissuasion.c.count)) if sord=='desc' \
else q.order_by(h_dissuasion.c.count)
elif sidx=='closed':
q = q.order_by(desc(h_closed.c.count)) if sord=='desc' \
else q.order_by(h_closed.c.count)
else:
q = q.order_by(desc(xh)) if sord=='desc' \
else q.order_by(xh)
q = q.offset(offset).limit(rows)
total = q.count()/rows + 1
data = []
total_in = 0
for i, r in enumerate(q.all()):
total_in += r.incoming
data.append({ 'id' : i, 'cell': [
u'%dh30' % (r.xhour/2) if i%2 \
else u'%dh' % (r.xhour/2),
r.incoming, 0, r.closed, 0, r.dissuasion, 0,
r.abandon, 0, r.connect, 0]
})
for x in data:
x['cell'][2] = '%.1f %%' % (100.0 * x['cell'][1] / total_in) \
if x['cell'][1] else ''
x['cell'][4] = '%.1f %%' % (100.0 * x['cell'][3] / total_in) \
if x['cell'][3] else ''
x['cell'][6] = '%.1f %%' % (100.0 * x['cell'][5] / total_in) \
if x['cell'][5] else ''
x['cell'][8] = '%.1f %%' % (100.0 * x['cell'][7] / total_in) \
if x['cell'][7] else ''
x['cell'][10] = '%.1f %%' % (100.0 * x['cell'][9] / total_in) \
if x['cell'][9] else ''
log.debug(data)
return dict(page=page, total=total, rows=data)
def optimized_random(self, limit):
return self.query.offset(
func.floor(
func.random() *
db.session.query(func.count(self.id)))).limit(limit).all()
def latest_query(db,
var_id,
identifier_id,
start_date,
end_date,
location,
allowed_location=1,
level=None,
weeks=False,
date_variable=None,
week_offset=0):
"""
To query register like data where we want to get the latest value.
I.e If the value of the number of beds is updated each week and we want the latest number.
We take the latest value per clinic.
Args:
var_id: Variable id to get last of
identifier_id: Id to identify which records we should use
start_date: Start date
end_date: End date
location: Location to restrict to
date_variable: if None we use date from data otherwise we use the variable indicated
weeks: True if we want a breakdwon by weeks.
Returns:
result(dict): Dictionary with results. Always has total key, and if
level was given there is a level key with the data
breakdown
"""
if allowed_location == 1:
if g:
allowed_location = g.allowed_location
if not is_allowed_location(location, allowed_location):
return {}
location_condtion = [
or_(loc == location for loc in (Data.country, Data.zone, Data.region,
Data.district, Data.clinic))
]
if date_variable:
date_conditions = [
func.to_date(Data.variables[date_variable].astext,
"YYYY-MM-DDTHH-MI-SS") >= start_date,
func.to_date(Data.variables[date_variable].astext,
"YYYY-MM-DDTHH-MI-SS") < end_date
]
else:
date_conditions = [Data.date >= start_date, Data.date < end_date]
conditions = location_condtion + date_conditions + [
Data.variables.has_key(identifier_id)
]
if weeks:
epi_year_start = meerkat_abacus.util.epi_week.epi_year_start_date(
start_date)
if date_variable:
c = func.floor(
extract(
'days',
func.to_date(Data.variables[date_variable].astext,
"YYYY-MM-DDTHH-MI-SS") - epi_year_start) / 7 +
1).label("week")
else:
c = func.floor(
extract('days', Data.date - epi_year_start) / 7 +
1).label("week")
# This query selects that latest record for each clinic for each week
# that has the variable identifier_id
query = db.session.query(
Data.clinic, c, Data.date,
Data.region, Data.district, Data.variables).distinct(
Data.clinic, c).filter(*conditions).order_by(
Data.clinic).order_by(c).order_by(Data.date.desc())
ret = {
"total": 0,
"weeks": {},
"district": {},
"clinic": {},
"region": {}
}
for r in query:
val = r.variables.get(var_id, 0)
ret["total"] += val
week = int(r.week) - week_offset
ret["weeks"].setdefault(week, 0)
ret["weeks"][week] += val
ret["clinic"].setdefault(r.clinic, {"total": 0, "weeks": {}})
ret["clinic"][r.clinic]["total"] += val
ret["clinic"][r.clinic]["weeks"][week] = val
ret["district"].setdefault(r.district, {"total": 0, "weeks": {}})
ret["district"][r.district]["total"] += val
ret["district"][r.district]["weeks"][week] = +val
ret["region"].setdefault(r.region, {"total": 0, "weeks": {}})
ret["region"][r.region]["total"] += val
ret["region"][r.region]["weeks"][week] = +val
return ret
else:
# This query selects that latest record for each clinic
# that has the variable identifier_id
query = db.session.query(
Data.clinic, Data.date, Data.region, Data.district,
Data.variables).distinct(Data.clinic).filter(*conditions).order_by(
Data.clinic).order_by(Data.date.desc())
ret = {"total": 0, "clinic": {}, "district": {}, "region": {}}
for r in query:
val = r.variables.get(var_id, 0)
ret["total"] += val
ret["clinic"][r.clinic] = val
ret["district"].setdefault(r.district, 0)
ret["district"][r.district] += val
ret["region"].setdefault(r.region, 0)
ret["region"][r.region] += val
return ret
def get_progression_stats(self,
session,
id_,
time_unit: 'TimeUnit' = TimeUnit.MONTH,
start_date: datetime.datetime = None,
end_date: datetime.datetime = None,
playlist: int = 13):
if time_unit == TimeUnit.MONTH:
date = func.to_char(Game.match_date, 'YY-MM')
time_format = "%y-%m"
elif time_unit == TimeUnit.DAY:
date = func.to_char(Game.match_date, 'YY-MM-DD')
time_format = "%y-%m-%d"
elif time_unit == TimeUnit.YEAR:
date = func.to_char(Game.match_date, 'YY')
time_format = "%y"
elif time_unit == TimeUnit.QUARTER:
date = func.concat(
func.to_char(Game.match_date, 'YY'), '-',
func.floor(
cast(func.extract('quarter', Game.match_date),
sqlalchemy.Numeric)))
time_format = '%y-%m'
else:
# Mainly to help the linter know that time_unit is assigned.
logger.error(
f'Unknown time unit: {time_unit}. Falling back onto month.')
date = func.to_char(Game.match_date, 'YY-MM')
time_format = "%y-%m"
date = date.label('date')
mean_query = session.query(date, func.count(
Game.hash), *self.get_player_stat_query()).join(PlayerGame).filter(
PlayerGame.time_in_game > 0).filter(
PlayerGame.player == id_).group_by('date').order_by('date')
std_query = session.query(
date, func.count(Game.hash),
*self.get_player_stat_std_query()).join(PlayerGame).filter(
PlayerGame.time_in_game > 0).filter(
PlayerGame.player == id_).group_by('date').order_by('date')
if start_date is not None:
mean_query = mean_query.filter(Game.match_date > start_date)
std_query = std_query.filter(Game.match_date > start_date)
if end_date is not None:
mean_query = mean_query.filter(Game.match_date < end_date)
std_query = std_query.filter(Game.match_date < end_date)
if playlist is not None:
mean_query = mean_query.filter(Game.playlist == playlist)
std_query = std_query.filter(Game.playlist == playlist)
mean_query = mean_query.all()
std_query = std_query.all()
mean_query = [list(q) for q in mean_query]
std_query = [list(q) for q in std_query]
results = []
for q, s in zip(mean_query, std_query):
result = {
'name':
datetime.datetime.strptime(q[0], time_format),
'average':
self.get_wrapped_stats([self.float_maybe(qn) for qn in q[2:]],
self.player_stats),
'std_dev':
self.get_wrapped_stats([self.float_maybe(qn) for qn in s[2:]],
self.player_stats),
'count':
q[1]
}
if time_unit == 'quarter':
date = result['name']
result['name'] = datetime.datetime(date.year,
(date.month - 1) * 3 + 1, 1)
result['name'] = result['name'].isoformat()
results.append(result)
return results
def execute(self, message, user, params):
alliance = Alliance()
race = None
size_mod = None
size = None
value_mod = None
value = None
bash = False
attacker = user.planet
cluster = None
limit = 5
params = params.group(1).split()
for p in params:
m = self.bashre.match(p)
if m and not bash:
bash = True
continue
m = self.clusterre.match(p)
if m and not cluster:
cluster = int(m.group(1))
m = self.racere.match(p)
if m and not race:
race = m.group(1)
continue
m = self.rangere.match(p)
if m and not size and int(m.group(2)) < 32768:
size_mod = m.group(1) or '>'
size = m.group(2)
continue
m = self.rangere.match(p)
if m and not value:
value_mod = m.group(1) or '<'
value = m.group(2)
continue
m = self.alliancere.match(p)
if m and not alliance.name and not self.clusterre.match(p):
alliance = Alliance(name="Unknown") if m.group(
1).lower() == "unknown" else Alliance.load(m.group(1))
if alliance is None:
message.reply("No alliance matching '%s' found" %
(m.group(1), ))
return
continue
if p[:4] == "lots" and user.is_admin():
limit = int(p[4:])
maxcap = PA.getfloat("roids", "maxcap")
mincap = PA.getfloat("roids", "mincap")
modifier = (cast(Planet.value,
Float).op("/")(float(attacker.value))).op("^")(0.5)
caprate = func.greatest(mincap,
func.least(modifier.op("*")(maxcap), maxcap))
maxcap = cast(func.floor(cast(Planet.size, Float).op("*")(caprate)),
Integer)
Q = session.query(Planet, Intel, maxcap.label("maxcap"))
if alliance.id:
Q = Q.join(Planet.intel)
Q = Q.filter(Intel.alliance == alliance)
else:
Q = Q.outerjoin(Planet.intel)
if alliance.name:
Q = Q.filter(Intel.alliance == None)
Q = Q.filter(Planet.active == True)
if race:
Q = Q.filter(Planet.race.ilike(race))
if size:
Q = Q.filter(Planet.size.op(size_mod)(size))
if value:
Q = Q.filter(Planet.value.op(value_mod)(value))
if bash:
Q = Q.filter(
or_(
Planet.value.op(">")(attacker.value *
PA.getfloat("bash", "value")),
Planet.score.op(">")(attacker.score *
PA.getfloat("bash", "score"))))
if cluster:
Q = Q.filter(Planet.x == cluster)
Q = Q.order_by(desc("maxcap"))
Q = Q.order_by(desc(Planet.size))
Q = Q.order_by(desc(Planet.value))
result = Q[:(limit + 1)]
if len(result) < 1:
reply = "No"
if race:
reply += " %s" % (race, )
reply += " planets"
if alliance.name:
reply += " in intel matching Alliance: %s" % (alliance.name, )
else:
reply += " matching"
if size:
reply += " Size %s %s" % (size_mod, size)
if value:
reply += " Value %s %s" % (value_mod, value)
message.reply(reply)
return
replies = []
for planet, intel, maxcap in result[:limit]:
reply = "%s:%s:%s (%s)" % (planet.x, planet.y, planet.z,
planet.race)
reply += " Value: %s Size: %s MaxCap: %s" % (planet.value,
planet.size, maxcap)
if intel:
if intel.nick:
reply += " Nick: %s" % (intel.nick, )
if not alliance.name and intel.alliance:
reply += " Alliance: %s" % (intel.alliance.name, )
replies.append(reply)
print(len(replies))
if len(replies) == 5:
message.reply("\n".join(replies))
replies = []
sleep(3)
if len(result) > limit:
replies.append(
"(Too many results to list, please refine your search)")
message.reply("\n".join(replies))
def usage_data(hall_no, year, month, day):
# turn date info into a date object
# find start range by subtracting 30 days
now = datetime.date(year, month, day)
start = now - datetime.timedelta(days=30)
# get the current day of the week for today and tomorrow
# python dow is monday = 0, while sql dow is sunday = 0
dow = (now.weekday() + 1) % 7
tmw = (dow + 1) % 7
# some commands are different between mysql and sqlite
is_mysql = sqldb.engine.name == "mysql"
# get the laundry information for today based on the day
# of week (if today is tuesday, get all the tuesdays
# in the past 30 days), group them by time, and include
# the first 2 hours of the next day
data = sqldb.session.query(
LaundrySnapshot.date,
(func.floor(LaundrySnapshot.time / 60).label("time") if is_mysql else
cast(LaundrySnapshot.time / 60, Integer).label("time")),
func.avg(LaundrySnapshot.washers).label("all_washers"),
func.avg(LaundrySnapshot.dryers).label("all_dryers"),
func.avg(LaundrySnapshot.total_washers).label("all_total_washers"),
func.avg(LaundrySnapshot.total_dryers).label("all_total_dryers"),
).filter(((LaundrySnapshot.room == hall_no)
& ((func.dayofweek(LaundrySnapshot.date) == dow + 1 if is_mysql else
func.strftime("%w", LaundrySnapshot.date) == str(dow))
| ((LaundrySnapshot.time <= 180 - 1)
& (func.dayofweek(LaundrySnapshot.date) == tmw + 1 if is_mysql else
func.strftime("%w", LaundrySnapshot.date) == str(tmw))))
& (LaundrySnapshot.date >= start))) \
.group_by(LaundrySnapshot.date, "time") \
.order_by(LaundrySnapshot.date, "time").all()
data = [x._asdict() for x in data]
all_dryers = [int(x["all_total_dryers"]) for x in data]
all_washers = [int(x["all_total_washers"]) for x in data]
washer_points = {k: 0 for k in range(27)}
dryer_points = {k: 0 for k in range(27)}
washer_total = {k: 0 for k in range(27)}
dryer_total = {k: 0 for k in range(27)}
for x in data:
hour = int(x["time"])
# if the value is for tomorrow, add 24 hours
if x["date"].weekday() != now.weekday():
hour += 24
washer_points[hour] += int(x["all_washers"])
dryer_points[hour] += int(x["all_dryers"])
washer_total[hour] += 1
dryer_total[hour] += 1
dates = [x["date"] for x in data]
if not dates:
dates = [now]
return {
"hall_name":
laundry.id_to_hall[hall_no],
"location":
laundry.id_to_location[hall_no],
"day_of_week":
calendar.day_name[now.weekday()],
"start_date":
min(dates).strftime("%Y-%m-%d"),
"end_date":
max(dates).strftime("%Y-%m-%d"),
"total_number_of_dryers":
safe_division(sum(all_dryers), len(all_dryers)),
"total_number_of_washers":
safe_division(sum(all_washers), len(all_washers)),
"washer_data": {
x: safe_division(washer_points[x], washer_total[x])
for x in washer_points
},
"dryer_data": {
x: safe_division(dryer_points[x], dryer_total[x])
for x in dryer_points
}
}