def _perform_sql(self):
filter = {}
filter["last_check"] = "now()"
query = (
self.session.query(self.tables.monitor_obs)
.add_column((func.timediff(self.tables.monitor_obs.last_check, func.now())).label("test"))
.filter(self.tables.monitor_obs.locked == False)
.filter(
func.timestampdiff(MINUTE, self.tables.monitor_obs.last_check, func.now())
<= func.TIMESTAMP(text("0000-00-00 00:") + self.tables.monitor_obs.frequency + ":0")
)
.order_by("last_check")
)
logger.debug(query)
def by_day():
form = DateForm()
day = None
if request.method == 'GET':
lday = db.session.query(Kontrol.date).order_by(Kontrol.date.desc()).limit(1).scalar()
day = day_interval(str(lday.date()))
if request.method == 'POST':
if form.validate():
sday = request.form['date']
day = day_interval(sday)
entries = None
if not day:
flash(u'Ошибка в дате.', category="error")
else:
form.date.data = day[0]
entries = db.session.query(Kontrol.id
, Kontrol.date
# , Kontrol.date2
, func.timediff(Kontrol.date2, Kontrol.date).label("dt")
, Kontrol.category
, Kontrol.comment
, Kontrol.message
, Kontrol.object
, Kontrol.obj_id
, Kontrol.prim) \
.filter(Kontrol.date > day[0]) \
.filter(Kontrol.date < day[1]) \
.filter(Kontrol.category!=2) \
.order_by(Kontrol.date.desc()).all()
return render_template('kontrol/kontrol_by_day.html', form=form, day=day, entries=entries)
def now_playing():
if engine.name == 'sqlite':
query = session.query(User).join(Track).filter(
func.strftime('%s', now()) -
func.strftime('%s', User.last_play_date) < Track.duration * 2)
elif engine.name == 'postgresql':
query = session.query(User).join(Track).filter(
func.date_part('epoch',
func.now() - User.last_play_date) < Track.duration *
2)
else:
query = session.query(User).join(Track).filter(
func.timediff(func.now(), User.last_play_date) < Track.duration *
2)
return request.formatter({
'nowPlaying': {
'entry': [
dict(
u.last_play.as_subsonic_child(request.user).items() + {
'username': u.name,
'minutesAgo': (now() - u.last_play_date).seconds / 60,
'playerId': 0
}.items()) for u in query
]
}
})
def check_upload (diff):
"""Check upload time of first revision with created time for change.
For each change, the upload time of the first revision (patchset) is
matched against the created time for the change. Those changes with
more than diff mins. of difference are shown.
Parameters
----------
diff: int
Minutes of difference considered.
"""
revs = session.query(label ("daterev",
func.min(DB.Revision.date)),
label ("change_id",
DB.Revision.change_id),
label ("number",
DB.Change.number)) \
.filter (DB.Revision.change_id == DB.Change.uid) \
.group_by("change_id") \
.subquery()
res = session.query(
label ("number",
revs.c.number),
label ("created",
DB.Change.created),
label ("daterev",
revs.c.daterev)
) \
.filter(and_(
func.abs(func.timediff(
DB.Change.created,
revs.c.daterev) > timedelta (minutes = diff)),
DB.Change.uid == revs.c.change_id)) \
.order_by (func.datediff(DB.Change.created, revs.c.daterev),
func.timediff(DB.Change.created, revs.c.daterev))
messages = res.all()
for message in messages:
print "Change " + str(message.number) + ": " + \
str(message.created - message.daterev) + \
" -- " + str(message.created) + " (created), " + \
str(message.daterev) + " (first revision)"
print "Total changes with discrepancy: " + str (len(messages))
def obj_id(id):
rec = db.session.query(Kontrol.id
, Kontrol.date
, func.timediff(Kontrol.date2, Kontrol.date).label("dt")
, Kontrol.category
, Kontrol.comment
, Kontrol.message
, Kontrol.object
, Kontrol.obj_id
, Kontrol.prim) \
.filter(Kontrol.obj_id == id) \
.order_by(Kontrol.date.desc()).limit(100).all()
return render_template('kontrol/obj_id.html', ent=rec)
def last():
curr = Kontrol.query.filter(or_(Kontrol.date2 == None, Kontrol.date2 == 0)).order_by(Kontrol.date.desc()).all()
last = db.session.query(Kontrol.id
, Kontrol.date
# , Kontrol.date2
, func.timediff(Kontrol.date2, Kontrol.date).label("dt")
, Kontrol.category
, Kontrol.comment
, Kontrol.message
, Kontrol.object
, Kontrol.obj_id
, Kontrol.prim) \
.filter(Kontrol.category!=2) \
.order_by(Kontrol.date.desc()).limit(32).all()
return render_template('kontrol/kontrol_last.html', ent1=curr, ent2=last)
def now_playing():
if engine.name == 'sqlite':
query = User.query.join(Track).filter(func.strftime('%s', now()) - func.strftime('%s', User.last_play_date) < Track.duration * 2)
elif engine.name == 'postgresql':
query = User.query.join(Track).filter(func.date_part('epoch', func.now() - User.last_play_date) < Track.duration * 2)
else:
query = User.query.join(Track).filter(func.timediff(func.now(), User.last_play_date) < Track.duration * 2)
return request.formatter({
'nowPlaying': {
'entry': [ dict(
u.last_play.as_subsonic_child(request.user).items() +
{ 'username': u.name, 'minutesAgo': (now() - u.last_play_date).seconds / 60, 'playerId': 0 }.items()
) for u in query ]
}
})
def print_body_for_user(self, authenticated_user):
now = datetime.now()
end_date = now - timedelta(hours=now.hour, minutes=now.minute, seconds=now.second, microseconds=now.microsecond)
start_date = end_date - timedelta(days=self.num_days)
db_session = DB_Session_Factory.get_db_session()
time_spent = {}
interviewer_emails = []
coding_interview_documented_time = 7200
coding_interview_actual_time = 1800
interview_documented_length = func.time_to_sec(func.timediff(Interview.end_time, Interview.start_time))
for time_spent_info in db_session.query(func.date(Interview.start_time), Interview.interviewer_email, func.sum(func.IF(interview_documented_length == coding_interview_documented_time, coding_interview_actual_time, interview_documented_length))).group_by(func.date(Interview.start_time), Interview.interviewer_email).filter(Interview.start_time > start_date, Interview.end_time < end_date).all():
[date, email, secs_spent] = time_spent_info
date_str = date.isoformat()
if time_spent.get(date_str) is None:
time_spent[date_str] = {}
time_spent[date_str][email] = int(secs_spent)
interviewer_emails.append(email)
interviewers = {}
for interviewer in db_session.query(Interviewer).filter(Interviewer.email.in_(interviewer_emails)).all():
interviewers[interviewer.email] = interviewer.dict_representation()
print json.dumps({
'time_spent' : time_spent,
'interviewers' : interviewers
})
def watchWhale(self):
@retry
def requestWhaleBalance():
r = requests.get('https://verge-blockchain.info/ext/getbalance/DQkwDpRYUyNNnoEZDf5Cb3QVazh4FuPRs9',verify=False)
return(r)
r = requestWhaleBalance()
while True:
sleep(0.5)
## Old text is the text before a new query
old_text = r.text
## If server error, text contains html. Condition stops exception from
## failure to convert error message to decimal
if old_text.find('<') == -1:
old_balance = Decimal(old_text)
r = requestWhaleBalance()
transaction_time = datetime.now()
## If text is number and not error message, which contains HTML
if r.text.find('<') == -1 and old_text.find('<') == -1:
new_balance = Decimal(r.text)
## If the text retrieved is new...
if r.text != old_text:
new_balance = Decimal(r.text)
if (new_balance - old_balance) < 0:
action= 'sold'
else:
action = 'bought'
quantity = new_balance - old_balance
## Prevents errors from being registered as transactions
if quantity != 0:
change = abs(quantity)
percent = change / old_balance
## REPLACE WITH API CALL!
## Gets the aggregated price nearest to the time associated with the trade
result = self.session.query(Aggregated_prices).filter(Aggregated_prices.price != None).order_by(func.abs( func.timediff(transaction_time,Aggregated_prices.endTime) ).asc() )[0]
approx_price = result.price
# self.cmd(('''select * from binance.aggregated_prices order by ABS(TIMEDIFF(%(transaction_time)s, endTime )) LIMIT 1;'''),params=params)[0]
## Percentile (may be move to separate thread)
pctl = percentile(quantity)
message = 'Whale {0} {1} XVG ( {2}% of holdings) \nTime: {3}\nApprox_price: {4} \nNormed: {5}'.format(action,int(change),
percent,transaction_time,approx_price,pctl)
# if pctl > 50
try:
sendTelegram(message)
except:
print("sendTelegram failed at " + str(datetime.now()))
## Saves API data and nearest known price
params = {'new_balance':new_balance,
'old_balance':old_balance,
'quantity':quantity,
'transaction_time':transaction_time,'approx_price':approx_price}
self.session.add(Whaletrades(**params))
self.session.commit()
def reports(self):
c.title = "Sales Dashboard"
c.items = c.user.programs
if len(c.items) > 0:
nullToken = c.nullToken
"""Each row is (program_id, date, numPurchases, numAffiliatePurchases, affiliateTotal, purchaseTotal """
#JOIN was unnecessary
#c.orders = Session_.query(Order.product_id, func.datediff(Order.date, c.lowerDate), func.count('*').label('total'), func.sum(Order.isReturned, type_=Integer).label('numReturns'), func.count(Order.affiliate_user_id).label('numAffiliatePurchases')).join((Product, and_(Order.seller_user_id==c.user.id, Order.product_id==Product.id))).filter(Order.date >= c.lowerDate).filter(Order.date < c.upperDate).group_by(Order.product_id).group_by(Order.date).all()
c.orders = Session_.query(Order.program_id,
func.datediff(Order.date, c.lowerDate),
func.count('*').label('numPurchases'),
func.count(Order.affiliate_user_id).label('numAffiliatePurchases'),
func.sum(case([(Order.affiliate_user_id != None, Order.amount)], else_=0)).label('affiliateTotal'),
func.sum(Order.amount).label('purchaseTotal'),
).filter(Order.merchant_user_id==c.user.id).filter(Order.date >= c.lowerDate).filter(Order.date <= c.upperDate).group_by(Order.program_id).group_by(Order.date).all()
#c.days = Session_.query(func.datediff(Transaction.date, c.lowerDate), func.sum(Transaction.amount)).filter(Transaction.user_id==c.user.id).filter(Transaction.date >= c.lowerDate).filter(Transaction.date < c.upperDate).group_by(Transaction.date).all()
"""SINGLE PRODUCT FOR OWNER"""
#c.orders = Session_.query(Order.product_id, func.datediff(Order.date, c.lowerDate), func.count('*').label('total'), func.sum(Order.isReturned, type_=Integer).label('numReturns'), func.count(Order.affiliate_user_id).label('numAffiliatePurchases')).filter(Order.product_id==c.product.id).filter(Order.date >= c.lowerDate).filter(Order.date < c.upperDate).group_by(Order.product_id).group_by(Order.date).all()
#c.impressions = Session_.query(Impression.product_id, func.datediff(Impression.date, c.lowerDate), func.count('*').label('total'), func.sum(case([(Impression.affiliate_ts != nullToken, 1)],else_=0)), func.sum(case([(Impression.order_ts != nullToken, 1)],else_=0)), func.sum(case([(and_(Impression.affiliate_ts != nullToken, Impression.order_ts != nullToken), 1)],else_=0)).label('buyConversions'), func.sum(case([(and_(Impression.purchase_ts != nullToken, Impression.order_ts != nullToken), 1)],else_=0)).label('purchaseConversions')).filter(Impression.product_id==c.product.id).filter(Impression.date >= c.lowerDate).filter(Impression.date < c.upperDate).group_by(Impression.product_id).group_by(Impression.date).all()
"""Each row is (product_id, date, affiliateViews, conversionTime)"""
c.impressions = Session_.query(Impression.program_id,
func.datediff(Impression.date, c.lowerDate),
func.sum(case([(Impression.affiliate_ts != nullToken, 1)],else_=0)),
func.avg(case([(and_(Impression.purchase_ts != nullToken, Impression.affiliate_ts != nullToken), func.time_to_sec(func.timediff(Impression.purchase_ts,Impression.affiliate_ts)))],else_=0),
).label('purchaseConversions')).join((Program, and_(c.user.id==Program.merchant_user_id, Impression.program_id==Program.id))).filter(Impression.date >= c.lowerDate).filter(Impression.date <= c.upperDate).group_by(Impression.program_id).group_by(Impression.date).all()
logging.info(c.impressions)
self.__Temp(c)
return render('/account/accountSummary.mak')
