def get(self):
self.set_header("Content-Type", "application/json")
ret = {}
try:
session = Session()
unmatched = []
result = session.query(Booking).filter(
Booking.master_id == None).order_by(Booking.id,
Booking.start_time).all()
for row in result:
unmatched.append(row.id)
ret['response'] = unmatched
self.set_status(Response.RESULT_OK)
except Exception, e:
session.rollback()
print_err_detail(e)
self.set_status(Response.RESULT_SERVERERROR)
add_err_message_to_response(ret, err_dict['err_mysql'])
def get(self):
self.set_header("Content-Type", "application/json")
self.set_header('Access-Control-Allow-Origin', '*')
user_id = self.get_argument('user_id', '')
ret = {}
try:
addresses = []
session = Session()
userdao = UserDAO()
addresses = userdao.get_all_user_addresses(user_id)
name = userdao.get_user_name(user_id)
default_index = userdao.get_user_default_address_index(user_id)
ret['response'] = {
'name': name,
'addresses': addresses,
'user_id': user_id,
'default_index': default_index
}
self.set_status(Response.RESULT_OK)
except Exception, e:
session.rollback()
print_err_detail(e)
self.set_status(Response.RESULT_SERVERERROR)
add_err_message_to_response(ret, err_dict['err_mysql'])
def get_users_who_register_only(self, start_date):
session = Session()
try:
params = {}
params['start_date'] = start_date
query = '''select u.id, aes_decrypt(from_base64(name), substr(salt, 1, 16)) as name,
u.devicetype,
aes_decrypt(from_base64(phone), substr(salt, 1, 16)) as phone, dateofreg,
w.sigungu_id
from users u
left join waiting_users w
on u.id = w.user_id
left join user_addresses a
on u.id = a.user_id
where date(u.dateofreg) >= :start_date
and sigungu_id is null
and address is null
order by dateofreg
'''
result = session.execute(query, params).fetchall()
users = []
for row in result:
user = dict(row)
user['dateofreg'] = user['dateofreg'].strftime(
'%Y-%m-%d %H:%M')
users.append(user)
return users
except Exception, e:
print_err_detail(e)
def get(self):
self.set_header("Content-Type", "application/json")
ret = {}
try:
session = Session()
descriptions = []
result = session.query(MasterPointDescription) \
.filter(MasterPointDescription.index >= 10) \
.all()
for row in result:
descriptions.append({'index' : row.index, 'description' : row.description})
ret['response'] = descriptions
self.set_status(Response.RESULT_OK)
except Exception, e:
session.rollback()
print_err_detail(e)
self.set_status(Response.RESULT_SERVERERROR)
add_err_message_to_response(ret, err_dict['err_mysql'])
def get_leave(self, start_date, end_date):
query = '''select count(*) as cnt from (
select user_id as uid from (
select b.*, u.email, c.booking_id, c.reason_id, c.kind
from bookings b
join users u
on b.user_id = u.id
join cancel_reasons c
on b.id = c.booking_id
where (b.appointment_type = 1 or b.appointment_type = 2 or b.appointment_type = 4) and c.kind = 1
and date(c.cancel_time) >= :start_date and date(c.cancel_time) <= :end_date
group by user_id) t
where user_id not in (select id as uid from (
select u.*, b.cleaning_status, b.appointment_type
from users u
join bookings b
on u.id = b.user_id
where b.cleaning_status = 0
group by u.id) t2))t3'''
query_param = {'start_date': start_date, 'end_date': end_date}
session = Session()
result = session.execute(query, query_param).fetchall()
print result[0].items()[0][1]
return int(result[0].items()[0][1])
def notify_24hours_ahead(self):
try:
print 'master notifier started...'
current_date = dt.datetime.now()
print current_date
tomorrow = current_date + dt.timedelta(days=1)
tomorrow = tomorrow.date()
print tomorrow
session = Session()
result = session.query(Booking.master_id, Master.phone, Master.name, func.count(Booking.master_id)) \
.join(Master, Booking.master_id == Master.id) \
.filter(func.date(Booking.start_time) == tomorrow) \
.group_by(Booking.master_id) \
.all()
for row in result:
master_phone = str(row[1])
master_name = str(row[2])
no_jobs = str(int(row[3]))
print master_phone, master_name, no_jobs
send_24hours_ahead_reminder(master_phone, master_name, no_jobs)
print 'notified to masters successfully...'
except Exception, e:
print_err_detail(e)
def send_survey_link():
update_time = dt.datetime(2016, 9, 27)
session = Session()
result = session.query(User) \
.filter(func.length(func.aes_decrypt(func.from_base64(User.phone), func.substring(User.salt, 1, 16))) < 12) \
.filter(User.phone != 'out') \
.filter(User.active == 1) \
.filter(not_(User.email.op('regexp')(r'._$'))) \
.all()
sms_sender = Message_Sender()
for row in result:
key = row.salt[:16]
crypto = aes.MyCrypto(key)
name = crypto.decodeAES(row.name)
phone = crypto.decodeAES(row.phone)
print name, phone
text = '''(광고) 홈마스터 설문조사참여하고 신세계 상품권 받으세요^^
https://goo.gl/kYNti3
~12.31'''
print sms_sender.send(sender=MAIN_CALL,
mtype='lms',
to=str(phone),
text=str(text))
def get(self):
self.set_header("Content-Type", "application/json")
ret = {}
try:
session = Session()
userdao = UserDAO()
bookingdao = BookingDAO()
# get last saturday
now = dt.datetime.now()
if week != 0:
offset = ((now.weekday() - FRIDAY) % 7) + (week - 1) * 7
now -= dt.timedelta(days=offset)
offset = (now.weekday() - SATURDAY) % 7
last_saturday = (now - dt.timedelta(days=offset)).date()
now = now.date()
# rating
#
ret['response'] = Response.SUCCESS
self.set_status(Response.RESULT_OK)
except Exception, e:
session.rollback()
print_err_detail(e)
self.set_status(Response.RESULT_SERVERERROR)
add_err_message_to_response(ret, err_dict['err_mysql'])
def is_next_booking_left_over_2days(self, booking_id):
try:
session = Session()
stmt = session.query(Booking) \
.filter(Booking.id == booking_id) \
.one()
booking_request_id = stmt.request_id
booking_start_time = stmt.start_time
next_booking = session.query(Booking) \
.filter(Booking.request_id == booking_request_id) \
.filter(Booking.start_time > booking_start_time) \
.filter(Booking.cleaning_status == BC.BOOKING_UPCOMMING) \
.order_by(Booking.start_time) \
.first()
print next_booking
if next_booking == None:
return 'SUCCESS', True, -1
now = dt.datetime.now().date()
upcoming_booking_time = next_booking.start_time.date()
diff = (upcoming_booking_time - now).days
if diff >= 3:
return 'SUCCESS', True, diff
return 'SUCCESS', True, diff
except Exception, e:
print e
return 'FAILURE', '', 0
def get_availability(self, sido, sigungu):
available = 1
try:
session = Session()
row = session.query(Sido).filter(Sido.name == sido).one()
sido_id = row.id
row = session.query(Sigungu).filter(
Sigungu.sido_id == sido_id).filter(
Sigungu.name == sigungu).one()
sigungu_id = row.id
prefered_gu = session.query(MasterPreferedArea, Master) \
.join(Master, MasterPreferedArea.master_id == Master.id) \
.filter(MasterPreferedArea.prefered_gu == sigungu_id) \
.filter(Master.active == 1) \
.count()
print prefered_gu
if prefered_gu == 0:
available = 0
except NoResultFound, e:
available = 0
def get(self):
self.set_header("Content-Type", "application/json")
self.set_header('Access-Control-Allow-Origin', '*')
user_id = self.get_argument('user_id', '')
ret = {}
try:
session = Session()
result = session.query(RejectRelation, Master) \
.join(Master, RejectRelation.master_id == Master.id) \
.filter(RejectRelation.user_id == user_id) \
.group_by(RejectRelation.master_id) \
.order_by(Master.name) \
.all()
master_names = []
for row in result:
master_names.append(row.Master.name)
ret['response'] = master_names
self.set_status(Response.RESULT_OK)
except Exception, e:
session.rollback()
print_err_detail(e)
self.set_status(Response.RESULT_SERVERERROR)
add_err_message_to_response(ret, err_dict['err_mysql'])
def is_cancelled_all(self, booking_id):
canceled_all = True
try:
session = Session()
current_book_start_time = session.query(Booking) \
.filter(Booking.id == booking_id) \
.one() \
.start_time
stmt = session.query(Booking.request_id).filter(
Booking.id == booking_id).subquery()
booking_group = session.query(Booking) \
.filter(Booking.request_id == stmt) \
.filter(Booking.start_time > current_book_start_time) \
.order_by(Booking.start_time) \
.all()
for booking in booking_group:
if not booking.cleaning_status == BC.BOOKING_CANCELED:
canceled_all = False
break
except Exception, e:
print_err_detail(e)
def get(self):
self.set_header("Content-Type", "application/json")
self.set_header('Access-Control-Allow-Origin', '*')
master_id = self.get_argument('master_id', '')
month = self.get_argument('month', 0)
print master_id, month
month = int(month)
if month == 0:
month = dt.datetime.now().month
ret = {}
try:
session = Session()
row = session.query(func.count(MasterBookingModifyRequest)) \
.filter(MasterBookingModifyRequest.master_id == master_id) \
.filter(func.month(MasterBookingModifyRequest.request_time) == month ) \
.one()
count = row[0]
ret['response'] = count
self.set_status(Response.RESULT_OK)
except Exception, e:
session.rollback()
print_err_detail(e)
self.set_status(Response.RESULT_SERVERERROR)
add_err_message_to_response(ret, err_dict['err_mysql'])
def get(self, **params):
self.set_header("Content-Type", "application/json")
user_id = self.get_argument('id', '')
mode = params['mode']
ret = {}
mongo_logger = get_mongo_logger()
mix = get_mixpanel()
try:
session = Session()
bookings = self.get_mybookings(session, user_id, mode)
ret['response'] = bookings
self.set_status(Response.RESULT_OK)
mix.track(user_id, 'got booking info', {'time' : dt.datetime.now(), 'mode' : mode})
mongo_logger.debug('got booking info', extra = {'uid' : user_id})
print user_id, 'successfully retrieved upcomming events...', dt.datetime.now()
except Exception, e:
session.rollback()
print_err_detail(e)
self.set_status(Response.RESULT_SERVERERROR)
add_err_message_to_response(ret, err_dict['err_mysql'])
def notify_all_active_homemaster(title='알려드립니다.',
content='홈마스터님 공지사항을 확인해주세요^^'):
ret = True
session = Session()
result = session.query(Master, MasterTimeSlot, MasterPushKey) \
.join(MasterTimeSlot, Master.id == MasterTimeSlot.master_id) \
.join(MasterPushKey, Master.id == MasterPushKey.master_id) \
.filter(Master.active == 1) \
.all()
# for test purpoese of server
'''result = session.query(Master, MasterPushKey) \
.join(MasterPushKey, Master.id == MasterPushKey.master_id) \
.filter(Master.id == '336c6743-0601-4bcc-97f5-a2c23567a4dc') \
.all()'''
for row in result:
name = row.Master.name
push_key = row.MasterPushKey.pushkey
try:
send_homemaster_notification([push_key], title, content)
except Exception, e:
print e
ret = False
continue
def get_users_who_add_address_only(self, start_date):
session = Session()
try:
params = {}
params['start_date'] = start_date
query = '''select id, aes_decrypt(from_base64(name), substr(salt, 1, 16)) as name,
devicetype,
aes_decrypt(from_base64(phone), substr(salt, 1, 16)) as phone ,
aes_decrypt(from_base64(address), substr(salt, 1, 16)) as address ,
dateofreg
from (
select u.id, u.devicetype, name, phone, salt, address, request_id, dateofreg
from users u
join user_addresses a
on u.id = a.user_id
join user_default_addresses da
on a.user_id = da.user_id and da.address_idx = 0
left join bookings b
on b.user_id = u.id
where date(u.dateofreg) >= :start_date)t
where t.request_id is NULL
order by dateofreg
'''
result = session.execute(query, params).fetchall()
users = []
for row in result:
user = dict(row)
user['dateofreg'] = user['dateofreg'].strftime(
'%Y-%m-%d %H:%M')
users.append(user)
return users
except Exception, e:
print_err_detail(e)
def get_user_auth_source(self, uid):
authsource = 'None'
try:
session = Session()
row = session.query(User).filter(User.id == uid).one()
authsource = row.authsource
except NoResultFound, e:
print_err_detail(e)
def get(self):
self.set_header("Content-Type", "application/json")
self.set_header('Access-Control-Allow-Origin', '*')
user_id = self.get_argument('user_id', '')
ret = {}
mongo_logger = get_mongo_logger()
try:
now = dt.datetime.now()
session = Session()
row = session.query(Booking, UserCard) \
.outerjoin(UserCard, and_(Booking.user_id == UserCard.user_id, Booking.card_idx == UserCard.user_card_index)) \
.filter(Booking.user_id == user_id) \
.filter(Booking.start_time < now) \
.filter(Booking.cleaning_status == BC.BOOKING_COMPLETED) \
.filter(Booking.payment_status != BC.BOOKING_PAID) \
.order_by(desc(Booking.start_time)) \
.first()
if row:
booking_id = row.Booking.id
cleaning_price = row.Booking.price_with_task
cleaning_time = convert_datetime_format2(row.Booking.start_time)
card_alias = row.UserCard.card_alias if row.UserCard != None else ''
ret['response'] = {'unpaid_booking' : True,
'card_alias' : card_alias,
'booking_id' : booking_id,
'cleaning_price' : cleaning_price,
'cleaning_time' : cleaning_time}
mongo_logger.debug('check unpaid booking', extra = {'user_id' : user_id,
'dt' : now,
'unpaid_booking' : True,
'booking_id' : booking_id,
'cleaning_price' : cleaning_price,
'cleaning_time' : cleaning_time})
else:
ret['response'] = {'unpaid_booking' : False}
mongo_logger.debug('check unpaid booking', extra = {'user_id' : user_id,
'dt' : now,
'unpaid_booking' : False})
self.set_status(Response.RESULT_OK)
except Exception, e:
session.rollback()
print_err_detail(e)
self.set_status(Response.RESULT_SERVERERROR)
add_err_message_to_response(ret, err_dict['err_mysql'])
mongo_logger.error('failed to check unpaid booking', extra = {'err' : str(e)})
def get_user_salt(self, email):
salt = ''
try:
session = Session()
row = session.query(User).filter(User.email == email).one()
salt = row.salt
except NoResultFound, e:
print_err_detail(e)
def get_user_device_type(self, uid):
devicetype = ''
try:
session = Session()
row = session.query(User).filter(User.id == uid).one()
devicetype = row.devicetype
except NoResultFound, e:
print_err_detail(e)
def get_user_salt_by_id(self, uid):
salt = ''
try:
session = Session()
row = session.query(User).filter(User.id == uid).one()
salt = row.salt
except NoResultFound, e:
print_err_detail(e)
def is_b2b(self, uid):
is_b2b = 0
try:
session = Session()
row = session.query(User).filter(User.id == uid).one()
is_b2b = row.is_b2b
except NoResultFound, e:
print_err_detail(e)
def post(self):
self.set_header("Content-Type", "application/json")
ret = {}
email = self.get_argument('email', '')
pwd = self.get_argument('pwd', '')
print 'login', email, pwd
mongo_logger = get_mongo_logger()
mix = get_mixpanel()
try:
session = Session()
userdao = UserDAO()
try:
row = session.query(User) \
.filter(User.email == email, User.password == pwd, User.active == 1) \
.one()
except NoResultFound, e:
try:
if len(email) == 11 and email.startswith('010'):
row = session.query(User) \
.filter(func.aes_decrypt(func.from_base64(User.phone), \
func.substr(User.salt, 1, 16)) == email, \
User.password == pwd, \
User.active == 1) \
.one()
else:
session.close()
self.set_status(Response.RESULT_OK)
add_err_message_to_response(
ret, err_dict['err_login_no_match'])
mongo_logger.error('%s failed to logined' % email,
extra={'err': str(e)})
return
except NoResultFound, e:
session.close()
self.set_status(Response.RESULT_OK)
add_err_message_to_response(ret,
err_dict['err_login_no_match'])
mongo_logger.error('%s failed to logined' % email,
extra={'err': str(e)})
return
except MultipleResultsFound, e:
session.close()
self.set_status(Response.RESULT_OK)
add_err_message_to_response(ret,
err_dict['err_dup_phone2'])
mongo_logger.error('%s failed to logined' % email,
extra={'err': str(e)})
return
def set_user_device_type(self, uid, new_device_type):
devicetype = ''
try:
session = Session()
row = session.query(User).filter(User.id == uid).one()
row.devicetype = new_device_type
session.commit()
except NoResultFound, e:
print_err_detail(e)
def get_user_default_address_index(self, uid):
addr_idx = 0
try:
session = Session()
row = session.query(UserDefaultAddress).filter(
UserDefaultAddress.user_id == uid).one()
addr_idx = row.address_idx
except NoResultFound, e:
print_err_detail(e)
def post(self):
self.set_header("Content-Type", "application/json")
ret = {}
booking_id = self.get_argument('booking_id', '')
caller_mdn = self.get_argument('caller_mdn', '')
callee_mdn = self.get_argument('callee_mdn', '')
mongo_logger = get_mongo_logger()
mix = get_mixpanel()
try:
session = Session()
row = session.query(Booking).filter(Booking.id == booking_id).one()
data = {}
data_header = {}
data_body = {}
text = convert_datetime_format3(row.start_time)
if row.cleaning_status == 0:
text += ' 예정'
# data header
data_header['VERSION'] = '1.0.0'
data_header['CALLER_ID'] = PKG_MASTER
# data body
data_body['SERVICE_KEY'] = T_SERVICE_KEY
data_body['SERVICE_NAME'] = "홈마스터"
data_body['CALLER_MDN'] = caller_mdn
data_body['CALLEE_MDN'] = callee_mdn
data_body['TITLE'] = '[홈마스터] 담당 홈마스터입니다'
data_body['TEXT'] = text
data_body['APP_URL'] = 'homemaster://HMBookingList'
data_body['WEB_URL'] = 'https://homemaster.co.kr'
data_body['REQUEST_TYPE'] = '1'
data_body['API_TYPE'] = '1'
# post data
data['HEADER'] = data_header
data['BODY'] = data_body
http_headers = {'Content-Type' : 'application/json; charset=utf-8', 'CPID' : CPID}
response = requests.post(T_BETA_SERVER, data=json.dumps(data), headers=http_headers)
print response.text
ret['response'] = Response.SUCCESS
self.set_status(Response.RESULT_OK)
except Exception, e:
print_err_detail(e)
self.set_status(Response.RESULT_SERVERERROR)
add_err_message_to_response(ret, err_dict['err_mysql'])
def get(self):
self.set_header("Content-Type", "application/json")
self.set_header('Access-Control-Allow-Origin', '*')
ret = {}
user_id = self.get_argument('user_id', '')
mongo_logger = get_mongo_logger()
now = dt.datetime.now()
try:
session = Session()
results = session.query(UserCoupon) \
.filter(UserCoupon.user_id == user_id) \
.filter(func.date(UserCoupon.expire_date) >= now.date()) \
.filter(UserCoupon.used == 0) \
.order_by(UserCoupon.expire_date) \
.all()
print "request user coupon sql query"
user_coupons = []
for result in results:
user_coupon = {}
user_coupon['id'] = result.id
user_coupon['issue_date'] = dt.datetime.strftime(
result.issue_date, '%Y.%m.%d')
user_coupon['expire_date'] = dt.datetime.strftime(
result.expire_date, '%Y.%m.%d')
user_coupon['discount_price'] = result.discount_price
user_coupon['description'] = result.description
user_coupon['title'] = result.title
user_coupons.append(user_coupon)
ret['response'] = user_coupons
self.set_status(Response.RESULT_OK)
mongo_logger.debug('request user coupon',
extra={'user_id': user_id})
except Exception, e:
session.rollback()
add_err_message_to_response(ret, err_dict['err_mysql'])
self.set_status(Response.RESULT_SERVERERROR)
print_err_detail(e)
mongo_logger.error('failed to request user coupon',
extra={
'user_id': user_id,
'err': str(e)
})
def cancelall_coupon_usage(self, booking_id):
session = Session()
try:
booking_record = session.query(Booking).filter(
Booking.id == booking_id).one()
request_id = booking_record.request_id
records = session.query(Booking) \
.filter(Booking.request_id == request_id) \
.filter(Booking.cleaning_status == BC.BOOKING_UPCOMMING) \
.all()
for record in records:
bid = record.id
coupon_record = session.query(UserCoupon).filter(
UserCoupon.booking_id == bid).first()
if coupon_record:
coupon_record.used = 0
coupon_record.booking_id = None
coupon_record.service_price = 0
session.commit()
except Exception, e:
session.rollback()
print_err_detail(e)
def post(self):
self.set_header("Content-Type", "application/json")
manager_id = self.get_argument('manager_id', '')
name = self.get_argument('name', '')
phone = self.get_argument('phone', '')
ret = {}
try:
session = Session()
row = session.query(Manager).filter(
Manager.id == manager_id).scalar()
if row == None: # no entry
session.close()
add_err_message_to_response(ret,
err_dict['err_no_entry_to_cancel'])
self.write(json.dumps(ret))
return
row.name = name
row.phone = phone
session.commit()
ret['response'] = Response.SUCCESS
self.set_status(Response.RESULT_OK)
except Exception, e:
session.rollback()
print_err_detail(e)
self.set_status(Response.RESULT_SERVERERROR)
add_err_message_to_response(ret, err_dict['err_mysql'])
def get_discount_price(self, promotion_code):
discount_price = 0
try:
session = Session()
row = session.query(Promotion).filter(
Promotion.promotion_code == promotion_code).one()
discount_price = row.discount_price
except Exception, e:
print_err_detail(e)
