def achievements_handler(user: User, update: Update, context: CallbackContext):
session = database.get_session()
level, next_threshold = get_level(user.score)
update.message.reply_html(
user.language.get(Token.LEVEL_MESSAGE) %
(user.score, level, next_threshold))
today = datetime.now().date()
user_submission_count_today = session.query(Submission)\
.filter(and_(Submission.user == user, func.Date(Submission.created) == today))\
.count()
user_review_count_today = session.query(Review) \
.filter(and_(Review.user == user, func.Date(Review.created) == today)) \
.count()
update.message.reply_html(
user.language.get(Token.USER_DAILY_PLAY_DETAILS_MESSAGE) %
(user_submission_count_today, user_review_count_today))
user_achievements = get_user_achievements(user)
update.message.reply_html(
user.language.get(Token.UNLOCKED_ACHIEVEMENTS) + "\n" +
print_unlocked_achievements(user, user_achievements))
update.message.reply_html(
user.language.get(Token.LOCKED_ACHIEVEMENTS) + "\n" +
print_locked_achievements(user, user_achievements))
if user.became_champion and user.email is None:
update.message.reply_text(user.language.get(Token.CHAMP_BUT_NO_EMAIL))
def get_reminders_notification(current_user):
user_rem = User.query.filter_by(public_id=current_user.public_id).first()
today = datetime.datetime.today()
date3 = today + datetime.timedelta(days=3)
result = user_rem.reminders.filter(
func.Date(Reminder.due_date) <= date3,
func.Date(Reminder.due_date) > today,
Reminder.achieved == False).order_by(func.DATE(Reminder.due_date))
return reminders_schema.jsonify(result)
def GetMinMaxAvgTemTwo(start, end):
minn,avgg,maxx = session.query(func.min(Measurement.tobs), func.avg(Measurement.tobs), func.max(Measurement.tobs)).\
filter(func.Date(Measurement.date) >= start).filter(func.Date(Measurement.date) <= end).all()[0]
temp_info = {
"Min Temparature": minn,
"Avg Temperature": avgg,
"Max Temperature": maxx
}
return jsonify(temp_info)
def GetMinMaxAvgTem(start):
last_day = session.query(Measurement.date).order_by(
Measurement.date.desc()).first()[0]
minn,avgg,maxx = session.query(func.min(Measurement.tobs), func.avg(Measurement.tobs), func.max(Measurement.tobs)).\
filter(func.Date(Measurement.date) >= start).filter(func.Date(Measurement.date) <= last_day).all()[0]
temp_info = {
"Min Temparature": minn,
"Avg Temperature": avgg,
"Max Temperature": maxx
}
return jsonify(temp_info)
def get_day_entries(user_id, day=None, field_id=None):
day, tz = User.timezoned(date=day, user_id=user_id)
timezoned = func.Date(func.timezone(tz, FieldEntry.created_at))
q = db.session.query(FieldEntry)\
.filter(FieldEntry.user_id == user_id, timezoned == day.date())
if field_id:
q = q.filter(FieldEntry.field_id == field_id)
return q
def group_by_date(db: Session, datetime_column, limit=None):
# TODO: Add index on date received and only query the last 7 days
q = db.query(func.Date(datetime_column),
func.count(func.DATE(datetime_column))).group_by(
func.DATE(datetime_column))
q = q.order_by(asc(func.DATE(datetime_column))).limit(limit).all()
return {date: count for date, count in q}
def GetLastYearTemp():
last_day = session.query(Measurement.date).order_by(
Measurement.date.desc()).first()
one_year_ago = dp.parse(*last_day) - dt.timedelta(days=365)
one_year_tobs = pd.read_sql(
session.query(Measurement.date, Measurement.tobs).filter(
func.Date(Measurement.date) >= one_year_ago).statement,
session.bind)
one_year_tobs.set_index('date', inplace=True)
return jsonify(one_year_tobs.to_dict())
def get_day_entries(user_id, day=None, field_id=None):
tz_ = db.session.query(User.timezone).filter_by(id=user_id).scalar()
tz_ = tz_ or 'America/Los_Angeles'
timezoned = func.Date(func.timezone(tz_, FieldEntry.created_at))
day = day.astimezone(tz.gettz(tz_)) if day else nowtz(tz_)
q = db.session.query(FieldEntry)\
.filter(FieldEntry.user_id == user_id, timezoned == day.date())
if field_id:
q = q.filter(FieldEntry.field_id == field_id)
return q
def get_expenses_ratio(start_date, end_date):
'''
'''
try:
session = c_app.config.get('SESSION')
query = session.query(MonthlySalary)
query = session.query((MonthlySalary.salary).label('salary'))
if start_date and end_date:
time_data = dt.strptime(start_date, '%Y-%m-%d')
month = time_data.month
year = time_data.year
query = query.filter(
func.Date(MonthlySalary.timestamp) ==
f'{year}-{month}-01%')
monthly_salary = query.all()[0][0]
expenses, total = FlaskSqlAlchemy.group_expense_by_date(
start_date, end_date)
return monthly_salary, total
except Exception as e:
raise e
def find_recent_week(cls, user_id, today_date):
return cls.query.filter_by(user_id=user_id).filter(
func.Date(cls.created_at) >= today_date -
timedelta(days=6)).order_by(cls.created_at)
def notify_try_charge_unpaid(self):
try:
mongo_logger = get_mongo_logger()
userdao = UserDAO()
bookingdao = BookingDAO()
current_time = dt.datetime.now()
two_weeks_before = current_time - dt.timedelta(days=28)
two_weeks_before = two_weeks_before.date()
print '-' * 40
print 'try charge unpaid notification via alimtalk'
print 'cron_time :', current_time
print '-' * 40
session = Session()
result = session.query(Booking, User) \
.join(User, Booking.user_id == User.id) \
.filter(func.Date(Booking.start_time) >= two_weeks_before) \
.filter(Booking.cleaning_status == BC.BOOKING_COMPLETED) \
.filter(Booking.payment_status != BC.BOOKING_PAID) \
.filter(User.is_b2b == 0) \
.filter(Booking.source == 'hm') \
.order_by(Booking.start_time) \
.all()
for row in result:
if 'b2b.com' in row.User.email: # b2b 무시
continue
if row.User.is_b2b == 1: # b2b 무시
continue
if row.Booking.source != 'hm': # 11번가 무시
continue
key = userdao.get_user_salt_by_id(row.User.id)[:16]
crypto = aes.MyCrypto(key)
user_id = row.Booking.user_id
user_name = crypto.decodeAES(row.User.name)
phone = crypto.decodeAES(row.User.phone)
booking_id = row.Booking.id
appointment_type = row.Booking.appointment_type
cleaning_time = convert_datetime_format2(
row.Booking.start_time)
charge_price = row.Booking.price_with_task
price = '{:,}원'.format(charge_price)
print booking_id, user_id, user_name, cleaning_time, price
status, try_charge, remain_days = bookingdao.is_next_booking_left_over_2days(
booking_id)
print status, try_charge, remain_days
if status == 'FAILURE': # 다음 회차 예약 가져오기 실패. 건너 뜀
continue
if try_charge: # 결제를 시도해야함
ret, value = request_payment(user_id, user_name,
booking_id, charge_price,
appointment_type)
if ret: # 결제 성공
row.Booking.payment_status = BC.BOOKING_PAID
session.commit()
print '*** success ***'
send_alimtalk(phone,
'notify_try_charge_unpaid_success',
cleaning_time, price)
mongo_logger.debug('user try charge success',
extra={
'dt': current_time,
'user_id': user_id,
'booking_id': booking_id,
'price': price,
'cleaning_time': cleaning_time
})
else: # 결제 실패
print '*** failed ***'
if remain_days == -1:
mongo_logger.debug('no booking remains',
extra={
'dt': current_time,
'user_id': user_id,
'booking_id': booking_id,
'price': price,
'cleaning_time':
cleaning_time
})
elif remain_days > 3: # 3일 초과로 남았으면 실패 공지
send_alimtalk(phone, 'notify_charge_failure',
cleaning_time, price)
mongo_logger.debug('user try charge fail',
extra={
'dt': current_time,
'user_id': user_id,
'booking_id': booking_id,
'price': price,
'cleaning_time':
cleaning_time
})
elif remain_days >= 0: # 3일 이내면 전체 취소
print '*** cancel all ***'
bookingdao.cancel_all_upcomings(booking_id)
send_jandi(
'NEW_BOOKING', "미결제 전체 취소 알림",
user_name + ' 고객님 전체 취소됨',
'미결제 일시 : {}, 금액 : {}'.format(
cleaning_time, price))
send_alimtalk(
phone, 'notify_try_charge_unpaid_cancel_all',
cleaning_time, price)
mongo_logger.debug('user try charge cancel all',
extra={
'dt': current_time,
'user_id': user_id,
'booking_id': booking_id,
'price': price,
'cleaning_time':
cleaning_time
})
except Exception, e:
print_err_detail(e)
mongo_logger.error('failed to try charge failure',
extra={'err': str(e)})
def get_data():
dates_form = DatesForm(request.form)
# Если поля для ввода дат оставлены пустыми:
date_start = config.Config.DATE_START
date_end = config.Config.DATE_END
# Если поля для ввода дат заполнены:
if request.form['date_start'] and request.form['date_end']:
date_start = request.form['date_start']
date_end = request.form['date_end']
# Кол-во устройств за период --------------------------------------------------------
devices_query = None
try:
devices_query = db.session.query(func.Date(Event.timestamp), func.count(Device.name)). \
join(Device). \
filter(Event.timestamp >= date_start,
Event.timestamp <= date_end). \
group_by(func.Date(Event.timestamp)). \
order_by(func.Date(Event.timestamp))
except SQLAlchemyError as error:
db.session.rollback()
current_app.logger.error('Error during selection of device_query : %s',
error,
exc_info=True)
devices_data = list()
devices_data.append(['Date', "Devices"])
for i in devices_query:
dttm = datetime.strftime(i[0], "%Y-%m-%d")
devices_data.append([dttm, i[1]])
# Кол-во устройств, запускавшие уровни игры за период ----------------------------------------------------
levels_query = None
try:
levels_query = db.session.query(Event.level_name,
func.count(Device.name)). \
join(Device). \
filter(Event.timestamp >= date_start,
Event.timestamp <= date_end). \
group_by(Event.level_name). \
order_by(Event.level_name)
except SQLAlchemyError as error:
db.session.rollback()
current_app.logger.error('Error during selection of levels_query : %s',
error,
exc_info=True)
levels_data = list()
levels_data.append(['Levels', "Devices"])
for i in levels_query:
level = i[0]
if i[0] is None:
level = str(0)
levels_data.append([level, i[1]])
return render_template('index.html',
dates_form=dates_form,
data={
'devices_data': json.dumps(devices_data),
'levels_data': json.dumps(levels_data)
})
def stats(update: Update, context: CallbackContext):
user = get_user_from_update(update)
mwelog.info(f"Stats requested by {user.username}")
today = datetime.now().date()
if len(context.args) == 3:
day = int(context.args[0])
month = int(context.args[1])
year = int(context.args[2])
today = datetime(year, month, day).date()
update.message.reply_text(f'Stats for {today.strftime("%A, %B %d, %Y")}')
session = database.get_session()
all_users_count = session.query(User).filter(
func.Date(User.created) <= today).count()
new_users = session.query(User).filter(
func.Date(User.created) == today).all()
update.message.reply_text(
f'{all_users_count} users (+{len(new_users)} new)')
if len(new_users) > 0:
update.message.reply_text(
f'New users: {", ".join([x.username for x in new_users])}')
all_submissions_count = session.query(Submission).filter(
func.Date(Submission.created) <= today).count()
new_submissions_count = session.query(Submission).filter(
func.Date(Submission.created) == today).count()
update.message.reply_text(
f'{all_submissions_count} submissions (+{new_submissions_count} new)')
all_reviews_count = session.query(Review).filter(
func.Date(Review.created) <= today).count()
new_reviews_count = session.query(Review).filter(
func.Date(Review.created) == today).count()
update.message.reply_text(
f'{all_reviews_count} reviews (+{new_reviews_count} new)')
all_subs = session.query(Submission).all()
all_subs_today = session.query(Submission).filter(
func.Date(Submission.created) == today).all()
review_count_average = round(
statistics.mean([x.review_count for x in all_subs]), 2)
review_count_average_today = 0.0
if len(all_subs_today) > 0:
review_count_average_today = round(
statistics.mean([x.review_count for x in all_subs_today]), 2)
update.message.reply_text(
f'Review count average: {review_count_average}({review_count_average_today} today)'
)
all_reviews_today = session.query(Review).filter(
func.Date(Review.created) == today).all()
all_submitted_users_today = set([x.user.id for x in all_subs_today])
all_reviewed_users_today = set([x.user.id for x in all_reviews_today])
update.message.reply_text(
f'Users who only submitted: {len([x for x in all_submitted_users_today if x not in all_reviewed_users_today])}'
)
update.message.reply_text(
f'Users who only reviewed: {len([x for x in all_reviewed_users_today if x not in all_submitted_users_today])}'
)
update.message.reply_text(
f'Users who both submitted and reviewed: {len([x for x in all_submitted_users_today if x in all_reviewed_users_today])}'
)
positive_together_count = session.query(Submission)\
.filter(Submission.category == SubmissionCategory.POSITIVE_TOGETHER)\
.filter(func.Date(Submission.created) == today)\
.count()
positive_separated_count = session.query(Submission)\
.filter(Submission.category == SubmissionCategory.POSITIVE_SEPARATED)\
.filter(func.Date(Submission.created) == today)\
.count()
negative_together_count = session.query(Submission)\
.filter(Submission.category == SubmissionCategory.NEGATIVE_TOGETHER)\
.filter(func.Date(Submission.created) == today)\
.count()
negative_separated_count = session.query(Submission)\
.filter(Submission.category == SubmissionCategory.NEGATIVE_SEPARATED)\
.filter(func.Date(Submission.created) == today)\
.count()
update.message.reply_text(
f"Submission categories:\nPositive together: {positive_together_count}\n"
f"Positive separated: {positive_separated_count}\n"
f"Negative together: {negative_together_count}\n"
f"Negative separated: {negative_separated_count}")