def stats(cls):
q = cls.select(Origin.code, Origin.title, Origin.tick_size,
fn.MIN(cls.ts).alias('min_ts'),
fn.MAX(cls.ts).alias('max_ts'),
fn.COUNT(cls.id).alias('num_marks'),
fn.MIN(cls.dts).alias('min_dts'),
fn.MAX(cls.dts).alias('max_dts'),
fn.AVG(cls.dts).alias('avg_dts'),
fn.STDDEV_POP(
cls.dts).alias('std_dts')).join(Origin).group_by(
Origin.code)
return q.namedtuples()
def get_smallest_match_id() -> Optional[int]:
subq = (MatchId.select(fn.MIN(MatchId.match_id)))
try:
match_id = (MatchId.select().where(
MatchId.match_id == subq)).get().match_id
except MatchId.DoesNotExist:
match_id = None
return match_id
def print_stats():
db = TleDb()
first = db.model.select(fn.MIN(TleModel.insert_date)).scalar()
last = db.model.select(fn.MAX(TleModel.insert_date)).scalar()
print("Objects {}".format(db.model.select().group_by(
TleModel.norad_id).count()))
print("TLE {}".format(db.model.select().count()))
print("First fetch {}".format(first))
print("Last fetch {}".format(last))
def multi_reduce() -> 'ModelSelect':
"""多个聚集函数.
SELECT COUNT(*) AS count,
MIN(prod_price) AS min,
MAX(prod_price) AS max,
AVG(prod_price) AS avg,
SUM(prod_price) AS sum
FROM product;
"""
return (Product.select(count().alias('count'),
fn.MIN(Product.prod_price).alias('min'),
fn.MAX(Product.prod_price).alias('max'),
fn.AVG(Product.prod_price).alias('avg'),
fn.SUM(Product.prod_price).alias('sum')))
def saveProcessedFile(file, path):
tmp = Mad_ToBeProcessedFile(
path=path,
status=False
)
tmp.save()
count = Mad_ProcessedFile.select().count() # pylint: disable=E1120
minimum = Mad_ProcessedFile.select(fn.MIN(Mad_ProcessedFile.id)).scalar() # pylint: disable=E1120
while count >= 600:
Mad_ProcessedFile.delete().where(Mad_ProcessedFile.id == minimum).execute()
minimum += 1
count = Mad_ProcessedFile.select().count() # pylint: disable=E1120
tmp = Mad_ProcessedFile(
name=file
)
tmp.save()
def per_house_overlap(hour_per_step):
time_range = (Task.select(
fn.MAX(Task.created_at).alias('max_created_at'),
fn.MIN(Task.created_at).alias('min_created_at'),
).get())
time_cursor = time_range.min_created_at.replace(minute=0,
second=0,
microsecond=0)
one_step = timedelta(hours=hour_per_step)
start_time = time_cursor
end_time = time_range.max_created_at
time_duration = end_time - start_time
time_cursor += one_step
per_test_coverage = {}
overall_coverage = []
print(f'=== Every {hour_per_step} Hours Coverage ===')
print('progress: 0%', end='\r')
while time_cursor < end_time:
progress = (time_cursor - start_time) / time_duration
print(f'progress: {progress*100:5.1f}%', end='\r')
stats = compare_step_hour_data(time_cursor, hour_per_step)
for minute_ago in stats['tests'].keys():
if minute_ago not in per_test_coverage:
per_test_coverage[minute_ago] = []
per_test_coverage[minute_ago].append(stats['tests'][minute_ago])
overall_coverage += stats['overall']
time_cursor += one_step
for minute_ago in sorted(per_test_coverage.keys()):
mean = statistics.mean(per_test_coverage[minute_ago])
stdev = statistics.stdev(per_test_coverage[minute_ago])
print(
f'Test[{minute_ago:02}] coverage avg: {mean:5.1f}%, std: {stdev:5.1f}%'
)
mean = statistics.mean(overall_coverage)
stdev = statistics.stdev(overall_coverage)
print(f'Overall coverage avg: {mean:5.1f}%, std: {stdev:5.1f}%')
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,
operational.Customer.gender,
operational.Customer.age,
def search_min(cls):
data = cls.select(cls.name ,cls.website ,cls.productid,cls.price,cls.time)\
.where(cls.time == cls.select(fn.MAX(cls.time)))\
.where(cls.price == cls.select(fn.MIN(cls.price))).limit(1).dicts()
return [i for i in data]
def process_comments(appcfg):
# Get newest comments with two week overlap
print(' PROCESSING NEWEST PUSHSHIFT.IO COMMENTS FOR', appcfg.subreddit)
try:
newest_utc = int(
Comment.select(fn.MAX(Comment.created_utc)).scalar().timestamp())
except (TypeError, AttributeError):
newest_utc = None
if newest_utc is not None:
oldestdate = newest_utc # - 1209600 # two weeks overlap, in seconds
else:
oldestdate = appcfg.oldestdate
try:
comment_id_set = get_push_comments(appcfg, appcfg.newestdate,
oldestdate)
except (ConnectionError, SSLError, ChunkedEncodingError):
comment_id_set = None
print(" Connection Error for Pushshift API. Quitting...")
# quit()
return comment_id_set
# Get oldest comments in case progress was interrupted, with two week overlap
try:
oldest_utc = int(
Comment.select(fn.MIN(Comment.created_utc)).scalar().timestamp())
except (TypeError, AttributeError):
oldest_utc = None
if oldest_utc is not None:
newestdate = oldest_utc # + 1209600 # two weeks overlap, in seconds
else:
newestdate = appcfg.newestdate
print(' PROCESSING OLDEST PUSHSHIFT.IO COMMENTS FOR', appcfg.subreddit)
try:
old_comment_id_set = get_push_comments(appcfg, newestdate,
appcfg.oldestdate)
except (ConnectionError, SSLError, ChunkedEncodingError):
old_comment_id_set = None
print(" Connection Error for Pushshift API. Quitting...")
# quit()
return old_comment_id_set
comment_id_set |= old_comment_id_set
filedate = arrow.now().timestamp
basedir = "/rpa" if os.environ.get('DOCKER', '0') == '1' else '.'
coutput_file_path = "{basedir}/{subreddit}_comments_{timestamp}.txt".format(
basedir=basedir, subreddit=appcfg.subreddit, timestamp=filedate)
# with open(coutput_file_path, 'w', encoding='UTF-8') as comment_file:
# comment_file.writelines(comment_id_set)
print(" Total comments submitted to", appcfg.subreddit, "in set:",
len(comment_id_set))
deleted = Author.get_or_none(name='[deleted]')
if deleted is not None:
cupdatet = Comment.update(deleted=True).where(
(Comment.author == deleted.id)
& (Comment.deleted.is_null() or Comment.deleted == 0)).execute()
print(
' Updated deleted field in comments. Set deleted = True for',
cupdatet, 'records.')
cupdatef = Comment.update(
deleted=False).where((Comment.author != deleted.id)
& (Comment.deleted.is_null())).execute()
print(
' Updated deleted field in comments. Set deleted = False for',
cupdatef, 'records.')
def build_query(self, version_id, query_region=None):
c = Catalog.alias()
c2t = CatalogToBHM_CSC.alias()
t = BHM_CSC.alias()
self.alias_t = t
# c2s16 = CatalogToSDSS_DR16_SpecObj.alias()
# s16 = SDSS_DR16_SpecObj.alias()
# s2020 = BHM_eFEDS_Veto.alias()
# sV = SDSSV_BOSS_SPALL.alias()
# ph = SDSSV_Plateholes.alias()
# phm = SDSSV_Plateholes_Meta.alias()
# set the Carton priority+values here - read from yaml
value = peewee.Value(self.parameters.get('value', 1.0)).cast('float')
instrument = peewee.Value(self.instrument)
cadence = peewee.Value(self.this_cadence)
# opt_prov = peewee.Value('ps1_psfmag')
if (self.instrument == 'BOSS'):
# #########################################################################
# prepare the spectroscopy catalogues
match_radius_spectro = self.parameters['spec_join_radius'] / 3600.0
spec_sn_thresh = self.parameters['spec_sn_thresh']
spec_z_err_thresh = self.parameters['spec_z_err_thresh']
dpriority_has_spec = self.parameters['dpriority_has_spec']
# SDSS DR16
c2s16 = CatalogToSDSS_DR16_SpecObj.alias()
ss16 = SDSS_DR16_SpecObj.alias()
s16 = (
ss16.select(
ss16.specobjid.alias('specobjid'),
)
.where(
ss16.snmedian >= spec_sn_thresh,
ss16.zwarning == 0,
ss16.zerr <= spec_z_err_thresh,
ss16.zerr > 0.0,
ss16.scienceprimary > 0,
)
.alias('s16')
)
# SDSS-IV/eFEDS March2020
c2s2020 = CatalogToBHM_eFEDS_Veto.alias()
ss2020 = BHM_eFEDS_Veto.alias()
s2020 = (
ss2020.select(
ss2020.pk.alias('pk'),
)
.where(
ss2020.sn_median_all >= spec_sn_thresh,
ss2020.zwarning == 0,
ss2020.z_err <= spec_z_err_thresh,
ss2020.z_err > 0.0,
)
.alias('s2020')
)
# SDSS-V spAll
ssV = SDSSV_BOSS_SPALL.alias()
sV = (
ssV.select(
ssV.specobjid.alias('specobjid'),
ssV.plug_ra.alias('plug_ra'),
ssV.plug_dec.alias('plug_dec'),
)
.where(
ssV.sn_median_all >= spec_sn_thresh,
ssV.zwarning == 0,
ssV.z_err <= spec_z_err_thresh,
ssV.z_err > 0.0,
ssV.specprimary > 0,
)
.alias('sV')
)
# SDSS-V plateholes - only consider plateholes that
# were drilled+shipped but that were not yet observed
ssph = SDSSV_Plateholes.alias()
ssphm = SDSSV_Plateholes_Meta.alias()
ssconf = SDSSV_BOSS_Conflist.alias()
sph = (
ssph.select(
ssph.pkey.alias('pkey'),
ssph.target_ra.alias('target_ra'),
ssph.target_dec.alias('target_dec'),
)
.join(
ssphm,
on=(ssph.yanny_uid == ssphm.yanny_uid)
)
.join(
ssconf, JOIN.LEFT_OUTER,
on=(ssphm.plateid == ssconf.plate)
)
.where(
(ssph.holetype == 'BOSS_SHARED'),
(ssph.sourcetype == 'SCI') | (ssph.sourcetype == 'STA'),
ssphm.isvalid > 0,
ssconf.plate.is_null(),
)
.alias('sph')
)
# adjust priority if target aleady has an SDSS spectrum
priority_1 = peewee.Case(
None,
(
(s16.c.specobjid.is_null(False), 1), # any of these can be satisfied
(s2020.c.pk.is_null(False), 1),
(sV.c.specobjid.is_null(False), 1),
(sph.c.pkey.is_null(False), 1),
),
0)
#
# Compute net priority
priority = (
peewee.Value(self.parameters['priority_floor']) +
priority_1 * dpriority_has_spec
)
else:
priority = peewee.Value(self.parameters['priority_floor'])
# compute transformed SDSS mags for pointlike and extended sources separately
# transform the csc (panstarrs1-dr1) griz into sdss psfmag griz
# extract coeffs from fit logs via:
# awk 'BEGIN {print("coeffs = {")} /POLYFIT/{ if($3~/sdss_psfmag/){pe="p"} else if ($3~/sdss_fiber2mag/){pe="e"} else{pe="error"}; printf("\"%s%d_%s\": %s,\n", substr($3,length($3)), $8, pe, $10)} END {print("}")}' bhm_csc_boss/ts_mag_to_sdss_psfmag_?_results.log # noqa
coeffs = {
"g2_p": 0.087878,
"g1_p": 0.063329,
"g0_p": 0.021488,
"i2_p": -0.011220,
"i1_p": 0.020782,
"i0_p": 0.000154,
"r2_p": -0.093371,
"r1_p": 0.136032,
"r0_p": -0.011477,
"z2_p": -0.180526,
"z1_p": 0.007284,
"z0_p": -0.037933,
}
# Note that the corrections for r and i are very small,
# however g+z both have non-negligible colour terms
g0 = peewee.Case(None, ((t.mag_g <= 0.0, None),), t.mag_g)
r0 = peewee.Case(None, ((t.mag_r <= 0.0, None),), t.mag_r)
i0 = peewee.Case(None, ((t.mag_i <= 0.0, None),), t.mag_i)
z0 = peewee.Case(None, ((t.mag_z <= 0.0, None),), t.mag_z)
g_r = g0 - r0
r_i = r0 - i0
i_z = i0 - z0
# use single set of transforms because we do not have any info in csc parent table to
# differentiate between pointlike and extended sources)
g = (g0 + coeffs['g0_p'] + coeffs['g1_p'] * g_r + coeffs['g2_p'] * g_r * g_r)
r = (r0 + coeffs['r0_p'] + coeffs['r1_p'] * g_r + coeffs['r2_p'] * g_r * g_r)
i = (i0 + coeffs['i0_p'] + coeffs['i1_p'] * r_i + coeffs['i2_p'] * r_i * r_i)
z = (z0 + coeffs['z0_p'] + coeffs['z1_p'] * i_z + coeffs['z2_p'] * i_z * i_z)
# validity checks (only griz) - set limits semi-manually
g_r_min = -0.3
g_r_max = 1.7
r_i_min = -0.5
r_i_max = 2.5
i_z_min = -0.3
i_z_max = 1.25
valid = (g0.between(0.1, 29.9) &
r0.between(0.1, 29.9) &
i0.between(0.1, 29.9) &
z0.between(0.1, 29.9) &
g_r.between(g_r_min, g_r_max) &
r_i.between(r_i_min, r_i_max) &
i_z.between(i_z_min, i_z_max))
opt_prov = peewee.Case(None, ((valid, 'sdss_psfmag_from_csc'),), 'undefined')
magnitude_g = peewee.Case(None, ((valid, g),), 'NaN')
magnitude_r = peewee.Case(None, ((valid, r),), 'NaN')
magnitude_i = peewee.Case(None, ((valid, i),), 'NaN')
magnitude_z = peewee.Case(None, ((valid, z),), 'NaN')
magnitude_h = peewee.Case(None, ((t.mag_h <= 0.0, None),), t.mag_h).cast('float')
# # Process the bhm_csc.[g,r,i,z,h] magnitudes to deal with zeros
# magnitude_g = peewee.Case(None, ((t.mag_g <= 0.0, None),), t.mag_g).cast('float')
# magnitude_r = peewee.Case(None, ((t.mag_r <= 0.0, None),), t.mag_r).cast('float')
# magnitude_i = peewee.Case(None, ((t.mag_i <= 0.0, None),), t.mag_i).cast('float')
# magnitude_z = peewee.Case(None, ((t.mag_z <= 0.0, None),), t.mag_z).cast('float')
# magnitude_h = peewee.Case(None, ((t.mag_h <= 0.0, None),), t.mag_h).cast('float')
# Create a subquery that will calculate the minimum catalog_to_bhm_csc.distance for each
# csc candidate target
subq = (
c2t
.select(
c2t.target_id,
fn.MIN(c2t.distance).alias('min_distance'))
.where(
c2t.version_id == version_id,
c2t.best >> True
)
.group_by(c2t.target_id)
.alias('min_dist_subq')
)
query = (
c.select(
c.catalogid,
t.cxo_name, # extra
t.pk.alias('csc_pk'), # extra
c.ra, # extra
c.dec, # extra
priority.alias('priority'),
value.alias('value'),
cadence.alias('cadence'),
instrument.alias('instrument'),
opt_prov.alias('optical_prov'),
magnitude_g.alias('g'),
magnitude_r.alias('r'),
magnitude_i.alias('i'),
magnitude_z.alias('z'),
magnitude_h.alias('h'),
t.mag_g.alias('csc_mag_g'), # extra
t.mag_r.alias('csc_mag_r'), # extra
t.mag_i.alias('csc_mag_i'), # extra
t.mag_z.alias('csc_mag_z'), # extra
t.oir_ra.alias('csc_ra'), # extra
t.oir_dec.alias('csc_dec'), # extra
)
.join(c2t)
.join(t)
.join(
subq,
on=(
(c2t.target_id == subq.c.target_id) &
(
(c2t.distance == subq.c.min_distance) |
(c2t.distance.is_null() & subq.c.min_distance.is_null())
)
),
)
.where(
c.version_id == version_id,
c2t.version_id == version_id,
c2t.best >> True
)
# .distinct([c2t.target_id]) # avoid duplicates - trust the CSC parent sample,
# .distinct([c.catalogid]) # avoid duplicates - trust the catalogid,
# avoid duplicates - trust uniquness in both CSC name and catalogid
.distinct([c.catalogid])
# .distinct([t.cxo_name])
.where
(
t.spectrograph == self.instrument
)
)
if (self.instrument == 'BOSS'):
# Append the spectro query
query = (
query
.switch(c)
.join(c2s16, JOIN.LEFT_OUTER)
.join(
s16, JOIN.LEFT_OUTER,
on=(
(c2s16.target_id == s16.c.specobjid) &
(c2s16.version_id == version_id)
)
)
.switch(c)
.join(c2s2020, JOIN.LEFT_OUTER)
.join(
s2020, JOIN.LEFT_OUTER,
on=(
(c2s2020.target_id == s2020.c.pk) &
(c2s2020.version_id == version_id)
)
)
.join(
sV, JOIN.LEFT_OUTER,
on=(
fn.q3c_join(sV.c.plug_ra, sV.c.plug_dec,
c.ra, c.dec,
match_radius_spectro)
)
)
.join(
sph, JOIN.LEFT_OUTER,
on=(
fn.q3c_join(sph.c.target_ra, sph.c.target_dec,
c.ra, c.dec,
match_radius_spectro)
)
)
)
if query_region:
query = query.where(peewee.fn.q3c_radial_query(c.ra, c.dec,
query_region[0],
query_region[1],
query_region[2]))
return query
def span(cls):
return (
cls.select(fn.MIN(cls.id)).scalar(),
cls.select(fn.MAX(cls.id)).scalar(),
)
def getMinEventTimestamp(clientID):
query = (Event.select(fn.MIN(Event.timestamp).alias("timestamp")).where(
(Event.client_id == clientID)).get())
return query.timestamp.timestamp()
def info(self, event, user=None):
if not user:
user = event.author
else:
if not isinstance(user, DiscoUser):
try:
user = self.state.guilds[event.guild.id].members[user].user
except KeyError:
try:
user = self.state.users[user]
except KeyError:
try:
user = self.bot.client.api.users_get(user)
except APIException:
return event.msg.reply(
'User not found :eyes:').after(3).delete()
self.client.api.channels_typing(event.channel.id)
content = []
content.append('**\u276F User Information**')
content.append('Profile: <@{}>'.format(user.id))
created_dt = to_datetime(user.id)
content.append('Created: {} ({})'.format(
humanize.naturaltime(datetime.utcnow() - created_dt),
created_dt.strftime("%b %d %Y %H:%M:%S")))
member = event.guild.get_member(user.id) if event.guild else None
if user.presence: #I couldn't get this to work w/o it lol
emoji, status = get_status_emoji(user.presence)
content.append('Status: <{}> {}'.format(emoji, status))
if user.presence.game and user.presence.game.name:
if user.presence.game.type == ActivityTypes.DEFAULT:
content.append('{}'.format(user.presence.game.name))
if user.presence.game.type == ActivityTypes.CUSTOM:
content.append('Custom Status: {}'.format(
user.presence.game.state))
if user.presence.game.type == ActivityTypes.LISTENING:
content.append('Listening to {} on Spotify'.format(
user.presence.game.details)
) #In the embed, details is the songname.
if user.presence.game.type == ActivityTypes.STREAMING:
content.append('Streaming: [{}]({})'.format(
user.presence.game.name, user.presence.game.url))
if user.public_flags:
badges = ''
user_badges = list(UserFlags(user.public_flags))
for badge in user_badges:
badges += '<{}> '.format(BADGE_EMOJI[badge])
content.append('Badges: {}'.format(badges))
if member:
content.append('\n**\u276F Member Information**')
if member.nick:
content.append('Nickname: {}'.format(member.nick))
content.append('Joined: {} ago ({})'.format(
humanize.naturaldelta(datetime.utcnow() - member.joined_at),
member.joined_at.strftime("%b %d %Y %H:%M:%S"),
))
if member.roles:
content.append('Roles: {}'.format(', '.join(
('<@&{}>'.format(member.guild.roles.get(r).id)
for r in member.roles))))
# Execute a bunch of queries
newest_msg = Message.select(fn.MAX(Message.id)).where(
(Message.author_id == user.id)
& (Message.guild_id == event.guild.id)).tuples()[0][0]
oldest_msg = Message.select(fn.MIN(Message.id)).where(
(Message.author_id == user.id)
& (Message.guild_id == event.guild.id)).tuples()[0][0] #Slow Query
voice = GuildVoiceSession.select(
fn.COUNT(GuildVoiceSession.user_id),
fn.SUM(GuildVoiceSession.ended_at - GuildVoiceSession.started_at)
).where((GuildVoiceSession.user_id == user.id)
& (~(GuildVoiceSession.ended_at >> None))
& (GuildVoiceSession.guild_id == event.guild.id)).tuples()[0]
infractions = Infraction.select(Infraction.id).where(
(Infraction.user_id == user.id)
& (Infraction.guild_id == event.guild.id)).tuples()
if newest_msg and oldest_msg:
content.append('\n **\u276F Activity**')
content.append('Last Message: {} ({})'.format(
humanize.naturaltime(datetime.utcnow() -
to_datetime(newest_msg)),
to_datetime(newest_msg).strftime("%b %d %Y %H:%M:%S"),
))
content.append('First Message: {} ({})'.format(
humanize.naturaltime(datetime.utcnow() -
to_datetime(oldest_msg)),
to_datetime(oldest_msg).strftime("%b %d %Y %H:%M:%S"),
))
if len(infractions) > 0:
content.append('\n**\u276F Infractions**')
total = len(infractions)
content.append('Total Infractions: **{:,}**'.format(total))
if voice[0]:
content.append('\n**\u276F Voice**')
content.append('Sessions: `{:,}`'.format(voice[0]))
content.append('Time: `{}`'.format(
str(humanize.naturaldelta(voice[1])).title()))
embed = MessageEmbed()
try:
avatar = User.with_id(user.id).get_avatar_url()
except:
avatar = user.get_avatar_url(
) # This fails if the user has never been seen by speedboat.
embed.set_author(name='{}#{} ({})'.format(
user.username,
user.discriminator,
user.id,
),
icon_url=avatar)
embed.set_thumbnail(url=avatar)
embed.description = '\n'.join(content)
embed.color = get_dominant_colors_user(user, avatar)
event.msg.reply('', embed=embed)
def process_submissions(appcfg):
# Get newest submissions with two week overlap
print(' PROCESSING NEWEST PUSHSHIFT.IO SUBMISSIONS FOR',
appcfg.subreddit)
try:
newest_utc = int(
Submission.select(fn.MAX(
Submission.created_utc)).scalar().timestamp())
except (TypeError, AttributeError):
newest_utc = None
if newest_utc is not None:
oldestdate = newest_utc # - 1209600 # two weeks overlap, in seconds
else:
oldestdate = appcfg.oldestdate
try:
post_id_set = get_push_submissions(appcfg, appcfg.newestdate,
oldestdate)
except (ConnectionError, SSLError, ChunkedEncodingError):
post_id_set = None
print(" Connection Error for Pushshift API. Quitting...")
# quit()
return post_id_set
# Get oldest submissions in case progress was interrupted, with four week overlap
try:
oldest_utc = int(
Submission.select(fn.MIN(
Submission.created_utc)).scalar().timestamp())
except (TypeError, AttributeError):
oldest_utc = None
if oldest_utc is not None:
newestdate = oldest_utc # + 2400000 # four week overlap, in seconds
else:
newestdate = appcfg.newestdate
print(' PROCESSING OLDEST PUSHSHIFT.IO SUBMISSIONS FOR',
appcfg.subreddit)
try:
old_post_id_set = get_push_submissions(appcfg, newestdate,
appcfg.oldestdate)
except (ConnectionError, SSLError, ChunkedEncodingError):
old_post_id_set = None
print(" Connection Error for Pushshift API. Quitting...")
# quit()
return old_post_id_set
post_id_set |= old_post_id_set
filedate = arrow.now().timestamp
output_file_path = "{subreddit}_{timestamp}.csv".format(
subreddit=appcfg.subreddit, timestamp=filedate)
# with open(output_file_path, 'w', encoding='UTF-8') as post_file:
# post_file.writelines(post_id_set)
print(" Total posts submitted to", appcfg.subreddit, "in set:",
len(post_id_set))
deleted = Author.get_or_none(name='[deleted]')
if deleted is not None:
supdatet = Submission.update(
deleted=True).where((Submission.author == deleted.id)
& (Submission.deleted.is_null()
or Submission.deleted == 0)).execute()
print(
' Updated deleted field in submissions. Set deleted = True for ',
supdatet, ' records.')
supdatef = Submission.update(
deleted=False).where((Submission.author != deleted.id)
& (Submission.deleted.is_null())).execute()
print(
' Updated deleted field in submissions. Set deleted = False for ',
supdatef, ' records.')
def get_earliest_datetime():
'''Finds the minimum datetime in the orders database'''
earliestDatetime = Tabs.select(fn.MIN(Tabs.timestamp)).scalar(convert=True)
return earliestDatetime
def user_summary(): # noqa: D103
form = DateRangeForm(request.args)
sort = request.args.get("sort")
if sort:
try:
sort = int(sort)
except:
sort = None
desc = request.args.get("desc")
if desc:
try:
desc = int(desc)
except:
desc = None
if not (form.from_date.data and form.to_date.data):
date_range = User.select(
fn.MIN(User.created_at).alias('from_date'),
fn.MAX(User.created_at).alias('to_date')).first()
return redirect(
url_for(
"user_summary",
from_date=date_range.from_date.date().isoformat(),
to_date=date_range.to_date.date().isoformat(),
sort=sort, desc=desc))
user_counts = (User.select(
User.organisation.alias("org_id"),
fn.COUNT(fn.DISTINCT(User.id)).alias("user_count")).where(
User.created_at.between(form.from_date.data, form.to_date.data)).join(
UserOrg, JOIN.LEFT_OUTER, on=(UserOrg.org_id == User.id)).group_by(
User.organisation)).alias("user_counts")
linked_counts = (OrcidToken.select(
OrcidToken.org.alias("org_id"),
fn.COUNT(fn.DISTINCT(OrcidToken.user)).alias("linked_user_count")).where(
OrcidToken.created_at.between(form.from_date.data, form.to_date.data)).group_by(
OrcidToken.org).alias("linked_counts"))
query = (Organisation.select(
Organisation.name,
fn.COALESCE(user_counts.c.user_count, 0).alias("user_count"),
fn.COALESCE(linked_counts.c.linked_user_count, 0).alias("linked_user_count")).join(
user_counts, on=(Organisation.id == user_counts.c.org_id)).join(
linked_counts, JOIN.LEFT_OUTER,
on=(Organisation.id == linked_counts.c.org_id)))
if sort == 1:
order_fields = [SQL("user_count"), SQL("linked_user_count"), ]
else:
order_fields = [Organisation.name, ]
if desc:
order_fields = [f.desc() for f in order_fields]
query = query.order_by(*order_fields)
total_user_count = sum(r.user_count for r in query if r.user_count)
total_linked_user_count = sum(r.linked_user_count for r in query if r.linked_user_count)
headers = [(h,
url_for(
"user_summary",
from_date=form.from_date.data,
to_date=form.to_date.data,
sort=i,
desc=1 if sort == i and not desc else 0))
for i, h in enumerate(["Name", "Linked User Count / User Count (%)"])]
return render_template(
"user_summary.html",
form=form,
query=query,
total_user_count=total_user_count,
total_linked_user_count=total_linked_user_count,
sort=sort, desc=desc,
headers=headers)
def open_positions_report():
markets = [
lp.market
for lp in LongPosition.select(LongPosition.market).distinct()
]
results = []
result_str = "Open Positions:\n"
total_net = Decimal('0.0')
total_spent = Decimal('0.0')
for market in markets:
current_price = Candle.select().where(
Candle.market == market).order_by(
Candle.timestamp.desc()).limit(1)[0].close
(num_positions, quantity, spent, min, avg, max,
min_sell_price) = LongPosition.select(
fn.COUNT(LongPosition.id), fn.SUM(LongPosition.buy_quantity),
fn.SUM(LongPosition.buy_quantity * LongPosition.purchase_price),
fn.MIN(LongPosition.purchase_price),
fn.AVG(LongPosition.purchase_price),
fn.MAX(LongPosition.purchase_price),
fn.MIN(LongPosition.sell_price)).where(
LongPosition.market == market,
LongPosition.sell_timestamp.is_null(True)).scalar(
as_tuple=True)
if not num_positions:
continue
quantity = Decimal(quantity).quantize(Decimal('0.00000001'))
spent = Decimal(spent)
current_value = quantity * current_price
profit = (current_value - spent).quantize(Decimal('0.00000001'))
total_net += profit
total_spent += spent
current_profit_percentage = (current_value /
spent * Decimal('100.0')).quantize(
Decimal('0.01'))
results.append({
"market":
market,
"num_positions":
num_positions,
"min_position":
min.quantize(Decimal('0.00000001')),
"avg_position":
avg.quantize(Decimal('0.00000001')),
"max_position":
max.quantize(Decimal('0.00000001')),
"min_sell_price":
min_sell_price.quantize(Decimal('0.00000001')),
"min_profit_percentage":
(min_sell_price / min * Decimal('100.00')).quantize(
Decimal('0.01')),
"profit":
profit,
"current_profit_percentage":
current_profit_percentage,
"quantity":
quantity.normalize()
})
if total_spent > Decimal('0.0'):
total_percentage = (total_net / total_spent *
Decimal('100.0')).quantize(Decimal('0.01'))
else:
total_percentage = Decimal('0.0')
for result in sorted(results, key=lambda i: i['profit'], reverse=True):
result_str += f"{'{:>8}'.format(result['market'])}: {result['min_position']:0.8f} | {result['min_sell_price']:0.8f} ({'{:>6}'.format(str(result['min_profit_percentage']))}%) | {'{:>2}'.format(str(result['num_positions']))} | {'{:>6}'.format(str(result['current_profit_percentage']))}%\n"
result_str += f"{'-' * 53}\n"
result_str += f" total: {'{:>11}'.format(str(total_net))} | {'{:>6}'.format(str(total_percentage))}%\n"
return result_str