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)