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')