def post(self):
checked_request = self.__check_request(request.form)
timer = Timer.find(checked_request['id'])
if not timer:
abort(404)
res = datetime.strptime(checked_request['end_at'],
'%Y-%m-%d %H:%M:%S') - timer.start_at
[tmp_minutes, seconds] = divmod(res.total_seconds(), 60)
[hours, minutes] = divmod(tmp_minutes, 60)
result_time = '%02d:%02d:%02d' % (hours, minutes, seconds)
with Timer.transaction():
timer.update(end_at=checked_request['end_at'],
result_time=result_time)
# raking update
session = Timer._get_session()
sum_label = func.sec_to_time(
func.sum(func.time_to_sec(Timer.result_time))).label('sum')
ranking_data = (session.query(Timer, sum_label).filter(
Timer.end_at != None, Timer.spot_id == timer.spot.id).group_by(
Timer.user_id).order_by(sum_label.desc()).all())
n = 0
ranking = []
res = {}
for row in ranking_data:
n += 1
if row[0].user.id == g.user.id:
res['rank'] = n
res['user_name'] = row[0].user.name
res['spot_name'] = row[0].spot.name
res['result_time'] = result_time
ranking.append({
'rank': n,
'user': {
'id': row[0].user.id,
'name': row[0].user.name
},
'sum': str(row.sum)
})
[lng, lat] = re.findall('[0-9]*\.[0-9]*', timer.spot.latlng)
doc = g.mongo.focus
rank = doc.ranking
rank.update({'spot_id': timer.spot.id}, {
'$set': {
'data': ranking,
'spot': {
'name': timer.spot.name,
'lat': lat,
'lng': lng,
}
}
},
upsert=True,
multi=False)
# end ranking update
return jsonify(status=200,
message='ok',
request=request.form,
response=res)
def post(self):
checked_request = self.__check_request(request.form)
timer = Timer.find(checked_request['id'])
if not timer:
abort(404)
res = datetime.strptime(checked_request['end_at'],'%Y-%m-%d %H:%M:%S') - timer.start_at
[tmp_minutes, seconds] = divmod(res.total_seconds(), 60)
[hours, minutes] = divmod(tmp_minutes,60)
result_time = '%02d:%02d:%02d' % (hours, minutes, seconds)
with Timer.transaction():
timer.update(
end_at=checked_request['end_at'],
result_time=result_time
)
# raking update
session = Timer._get_session()
sum_label = func.sec_to_time(func.sum(func.time_to_sec(Timer.result_time))).label('sum')
ranking_data = (session
.query(Timer, sum_label)
.filter(Timer.end_at!=None, Timer.spot_id==timer.spot.id)
.group_by(Timer.user_id)
.order_by(sum_label.desc())
.all())
n = 0
ranking = []
res={}
for row in ranking_data:
n += 1;
if row[0].user.id == g.user.id:
res['rank'] = n
res['user_name'] = row[0].user.name
res['spot_name'] = row[0].spot.name
res['result_time'] = result_time
ranking.append(
{
'rank':n,
'user':{
'id':row[0].user.id,
'name':row[0].user.name
},
'sum':str(row.sum)
}
)
[lng,lat] = re.findall('[0-9]*\.[0-9]*', timer.spot.latlng)
doc = g.mongo.focus
rank = doc.ranking
rank.update( {'spot_id': timer.spot.id},
{'$set':{'data':ranking,'spot':{'name':timer.spot.name,
'lat':lat,
'lng':lng,
}
}},
upsert=True,
multi=False)
# end ranking update
return jsonify(status=200, message='ok', request=request.form, response=res)
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 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')
