def update_doctorprofile(request):
try:
doctors = request.json
speclaization = doctors['Specialization']
service = doctors['services']
doc_record = {
k: v
for k, v in doctors.items()
if k not in ('Specialization', 'services')
}
doctor = {k: v for k, v in doctors.items() if k in ('doctor_id')}
doctorid = json.loads(
dbget(
"select count(*) as doctor_id from new.doctor_profile where doctor_profile_id ='"
+ d['doctor_id'] + "'"))
businessid = json.loads(
dbget(
"select count(*) as business_id from new.business_profile where business_id ='"
+ str(d['business_id']) + "'"))
if doctorid[0]['doctor_id'] == 1 and businessid[0][
'business_id'] == 1 and len(speclaization) != 0 and len(
service) != 0:
doc = doctor.get('doctor_id')
doc_record['doctor_profile_id'] = doctors['doctor_name'][:4] + str(
doctor.get('doctor_id'))
gensql('update', 'new.doctor_profile', doc_record, doctor)
doc_profile_id = doc_record['doctor_profile_id']
d = {'doctor_id': doc_profile_id}
dbput("delete from new.doctor_specialization where doctor_id='" +
str(d) + "'")
for sep in speclaization:
i = {}
i['specialization_id'] = sep
i['doctor_id'] = doc_profile_id
gensql('insert', 'new.doctor_specialization', i)
dbput("delete from new.doctor_services where doctor_id='" +
str(d) + "'")
for ser in service:
i = {}
i['doctor_id'] = doc_profile_id
i['service_id'] = ser
gensql('insert', 'new.doctor_services', i)
return (json.dumps(
{
"Message": "Recored Updated Successfully",
"Message_Code": "RUS",
"Service_Status": "Success"
},
indent=4))
else:
return (json.dumps(
{
'Message': 'Invalid Data',
'Message_Code': 'ID',
'Status': 'Failure'
},
indent=4))
except:
return (json.dumps(
{
"Message": "Record Updated Unsuccessful",
"Message_Code": "RUUS",
"Service_Status": "Success"
},
indent=4))
def ratecodeselect(request):
s = json.loads(dbget("select * from revenue_management.ratecode"))
print(s)
return(json.dumps({"Return": s,"Status": "Success","StatusCode": "200"},indent=4))
def HOTEL_REM_POST_SELECT_UpdateRatecodeSetup(request):
d = request.json
print(d)
records = json.loads(
dbget(
"select ratecode_setup.rateheader_id, ratecode.ratecode_id,ratecode.rate_code,\
ratecode.rate_description,ratecategory.rate_category,ratecategory.rate_category_decription,\
ratecategory.ratecategory_id,ratecategory.rate_class,\
ratecode_setup.begin_sell_date,ratecode_setup.end_sell_date,market.*,\
res_source.*,\
ratecode_setup.display_sequence,sell_control.*,rate_components.*,\
ratecode_setup.rooms_id,ratecode_setup.packages_id,tranction_details.*\
from revenue_management.ratecode_setup\
join revenue_management.ratecode on ratecode_setup.ratecode_id = ratecode.ratecode_id \
join revenue_management.ratecategory on ratecode.rate_category_id = ratecategory.ratecategory_id \
join reservation.market on ratecode_setup.market_id = market.id \
join reservation.res_source on ratecode_setup.source_id = res_source.id \
join revenue_management.sell_control on ratecode_setup.sell_control_id = sell_control.sell_id \
join revenue_management.rate_components on ratecode_setup.rate_components_id = rate_components.components_id \
join revenue_management.tranction_details on ratecode_setup.transaction_details_id = \
tranction_details.tranction_detail_id where ratecode.ratecode_id='"
+ str(d['ratecode_id']) + "' "))
#print("records ",records,"len",len(records))
for cords in records:
cords['rooms'] = json.loads(
dbget(
"select rooms_selected.rooms_selected_id,rooms_selected.rooms_id,\
room_type.id,room_type.type\
from revenue_management.rooms_selected join room_management.room_type on \
rooms_selected.room_type_id = room_type.id where\
rooms_selected.rooms_id='" +
str(cords['rooms_id']) + "' "))
#print("records ",records,"len",len(records))
cords['packages'] = json.loads(
dbget(
"select packages_selected.packages_selected_id,packages_selected.packages_id,\
package_code.package_code,package_code.package_code_id\
from revenue_management.packages_selected\
join packages.package_code on packages_selected.package_code_id = \
package_code.package_code_id where packages_selected.packages_id='"
+ str(cords['packages_id']) + "'"))
rate_details = json.loads(
dbget("select * from revenue_management.rate_details \
where ratecode_id='" +
str(cords['ratecode_id']) + "'"))
#print("rate_details ",rate_details, len(rate_details))
no = 0
for rate in rate_details:
print("rate", rate)
get_detail_rate = json.loads(
dbget("SELECT rates_all.rates_id, rates_all.ratecode_id, \
rates_all.rate_details_id, season_code.*, rates_all.rate_date,\
rates_all.one_adult_rate, rates_all.two_adult_rate, rates_all.three_adult_rate,\
rates_all.four_adult_rate, rates_all.extra_adult_rate,rates_all.one_child_rate,rates_all.two_child_rate,\
rates_all.extra_child_rate, rates_all.rooms_id, rates_all.packages_id,\
rate_tier.* from revenue_management.rates_all join \
revenue_management.season_code on \
rates_all.season_code_id = season_code.season_code_id join \
revenue_management.rate_tier on \
rates_all.rate_tier_id = rate_tier.rate_tier_id \
where rates_all.ratecode_id='" +
str(rate['ratecode_id']) + "' and \
rates_all.rate_details_id='" +
str(rate['rate_details_id']) + "' "))
print("-----------------------------------")
#print("get_detail_rate", get_detail_rate,len(get_detail_rate))
if len(get_detail_rate) != 0:
detail_rate = get_detail_rate[no]
#print("detail_rate", detail_rate)
if detail_rate['rate_tier_id'] != 0:
detail_rate['packages_id'] = 0
#print("rateof", detail_rate,type(detail_rate))
x = json.loads(
dbget(
"select rooms_selected.rooms_selected_id,rooms_selected.rooms_id,\
room_type.id as roomid,room_type.type as roomstype\
from revenue_management.rooms_selected join room_management.room_type on \
rooms_selected.room_type_id = room_type.id \
where rooms_selected.rooms_id='"
+ str(detail_rate['rooms_id']) + "' "))
rate['rooms'] = x
y = json.loads(
dbget(
"select packages_selected.packages_selected_id,packages_selected.packages_id,\
package_code.package_code,package_code.package_code_id\
from revenue_management.packages_selected\
join packages.package_code on packages_selected.package_code_id = \
package_code.package_code_id where \
packages_selected.packages_id='" +
str(detail_rate['packages_id']) + "'"))
rate['packages'] = y
z = json.loads(
dbget("select * from revenue_management.rate_days where \
rate_details_id='" +
str(rate['rate_details_id']) + "' "))
detail_rate['rooms'] = x
detail_rate['packages'] = y
detail_rate['days'] = z
rate['advanced_details'] = detail_rate
#no=len(get_detail_rate)-1
cords['rate_details'] = rate_details
return (json.dumps(
{
"records": records,
"return_code": "RRS",
"message": "Record Retrived Successfully"
},
indent=4))
def stay_over_rooms(date1):
stay_over = json.loads(
dbget("select count(*) from reservation.res_reservation \
where res_guest_status in ('checkin','due out') and res_arrival>= '"
+ str(date1) + "' and res_depature< '" + str(date1) + "'"))
return (stay_over[0]['count'] if len(stay_over) != 0 else 0)
def Alexa_Notification(request):
d = request.json
get_id = json.loads(
dbget("select * from configure_alexa where room_id='" +
str(d['room_no']) + "'"))
current_datetime = application_datetime()
print(current_datetime[0])
print(current_datetime[1])
#print(d['user_Id'],type(d['user_Id']))
url = 'https://api.amazon.com/auth/O2/token'
headers = {
'Content-Type': 'application/x-www-form-urlencoded',
}
payload = {
'grant_type': 'client_credentials',
'client_id': get_id[0]['client_id'],
'client_secret': get_id[0]['secret_id'],
'scope': 'alexa::proactive_events'
}
s = requests.post(url, headers=headers, data=payload)
token_res = s.json()
print(token_res['access_token'])
request_header = {
'Authorization': 'Bearer {}'.format(token_res['access_token']),
"Content-Type": "application/json"
}
if d['room_no'] == '100':
api_url = 'https://api.amazonalexa.com/v1/proactiveEvents/stages/development'
else:
api_url = 'https://api.eu.amazonalexa.com/v1/proactiveEvents/stages/development'
notify_json = {
"timestamp": str(current_datetime[0]),
"referenceId": "orangetango2221800f44-436a-4c47-8d9f-e14356bb010c",
"expiryTime": str(current_datetime[0]),
"event": {
"name": "AMAZON.MessageAlert.Activated",
"payload": {
"state": {
"status": "UNREAD",
"freshness": "NEW"
},
"messageGroup": {
"creator": {
"name": str(d['Object']) + "request has been closed"
},
"count": 1,
"urgency": "URGENT"
}
}
},
"relevantAudience": {
"type": "Unicast",
"payload": {
"user": get_id[0]['alexa_app_id']
}
}
}
notifcation_send = requests.post(api_url,
headers=request_header,
json=notify_json)
print("notification_send:", notifcation_send)
return json.dumps({"Return": "Run Successfully"})
def index():
s = dbget("select * from cashiering.billing_currency")
print(s)
return s
def HOTEL_RES_POST_INSERT_AcceptWaitlistReservation(request):
d = request.json
#availablecount
RES_Arrival_date = datetime.datetime.strptime(d['RES_Arrival'],
'%Y-%m-%d').date()
initial = datetime.datetime.strptime(d['RES_Depature'], '%Y-%m-%d').date()
depature_minus = initial - datetime.timedelta(days=1)
delta = initial - RES_Arrival_date # timedelta
print(delta)
normal_count = json.loads(
dbget(
"select count(*) from room_management.room_available where rm_date between '"
+ str(d['RES_Arrival']) + "' \
and '" + str(depature_minus) +
"' and rm_room = '" + str(d['RES_Room_Type']) + "'"))
if normal_count[0]['count'] == delta.days:
pass
else:
return (json.dumps(
{
'Status': 'Failure',
'StatusCode': '200',
'Return': 'Roomtype or date is not Declare',
'ReturnCode': 'RODND'
},
sort_keys=True,
indent=4))
booking_count = json.loads(
dbget("select * from room_management.room_available where rm_room= '" +
str(d['RES_Room_Type']) + "'\
and rm_date between '" +
str(d['RES_Arrival']) + "' and '" + str(depature_minus) + "'"))
for check_booking in booking_count:
if check_booking['available_count'] < int(d['RES_Number_Of_Rooms']):
return (json.dumps(
{
'Status': 'Success',
'StatusCode': '200',
'Return': 'Booking is Not Available',
'ReturnCode': 'BNA'
},
sort_keys=True,
indent=4))
bookedcount = dbput(
"update room_management.room_available set available_count=available_count - \
'1', \
booked_count = booked_count + '1' where rm_room = \
'" + str(d['RES_Room_Type']) + "' and \
rm_date between '" + str(d['RES_Arrival']) +
"' and '" + str(depature_minus) + "' ")
id = d.get("Res_id")
pf_id = d.get("pf_id")
res_unique_id = d.get("Res_unique_id")
res_unique_id = res_unique_id.split(',')
res_unique_id = str(res_unique_id)[1:-1]
mobileno = dbget("select pf_mobileno from reservation.res_reservation \
where res_id = '" + id + "' and pf_id = '" + pf_id + "'")
mobileno = json.loads(mobileno)
print(mobileno[0]['pf_mobileno'], type(mobileno[0]['pf_mobileno']))
mobile = mobileno[0]['pf_mobileno']
mobile = str(mobile)
mobile = mobile[0:3]
mobile = str(mobile)
random_no = (random.randint(1000000000, 9999999999))
random_no = str(random_no)
random_no = random_no[0:4]
print(random_no)
conf = mobile + random_no
print(mobile)
RES_Confnumber = "PMS" + conf
print(RES_Confnumber)
res_confnumber = RES_Confnumber
res_guest_status = "reserved"
sql_value = dbput(
"update reservation.res_reservation set res_Confnumber = '" +
res_confnumber + "',res_guest_status = '" + res_guest_status + "' \
where res_id = '" + id + "' and pf_id = '" + pf_id +
"' and res_unique_id in(" + res_unique_id + ") ")
print(d)
Emp_Id = '121'
Emp_Firstname = "Admin"
s = {}
s['Emp_Id'] = Emp_Id
s['Emp_Firstname'] = Emp_Firstname
app_datetime = application_date()
s['RES_Log_Date'] = app_datetime[1]
s['RES_Log_Time'] = app_datetime[2]
s['RES_Action_Type'] = "New Reservation"
s['RES_Description'] = "Reservation for " + id + " " + "is changed from waitlist to reserved status and the confirmation number is " + res_confnumber
s['Res_id'] = id
sql_value = gensql('insert', 'reservation.res_activity_log', s)
return (json.dumps(
{
'Status': 'Success',
'StatusCode': '200',
'Return': 'Record Inserted Successfully',
'ConfirmationNumber': res_confnumber,
'ReturnCode': 'RIS'
},
sort_keys=True,
indent=4))
def tokengeneration(request):
try:
d = request.json
mob = json.loads(dbget("select count(*) as mobile from new.appointment where mobile ='"+d['mobile']+"' and business_date = '" + str(d['business_date']) + "' "))
if mob[0]['mobile'] == 1:
wt = json.loads(dbget("select average_waiting_time from new.doctorinbusiness where doctor_id = '"+str(d['doctor_id'])+"'\
and business_id = '"+str(d['business_id'])+"'"))
waittime = wt[0]['average_waiting_time']
tk_no = json.loads(dbget("select token_no from new.appointment where mobile='"+d['mobile']+"'\
and doctor_id = '"+str(d['doctor_id'])+"' and business_id = '"+str(d['business_id'])+"'"))
num = tk_no[0]['token_no']
if num == 1:
avg_wait = 0
else:
avg_wait = (num - 1) * waittime
appid = json.loads(dbget("select app_id from new.appointment where mobile = '"+d['mobile']+"'\
and doctor_id = '"+str(d['doctor_id'])+"' and business_id = '"+str(d['business_id'])+"'"))
app_id = appid[0]['app_id']
return(json.dumps({'Message': 'Token Already Generated', 'Message_Code': 'TAG','Token_No':num,'Status': 'Sucess','Waiting_Time':avg_wait,'Appointment_id':app_id},indent=4))
else:
doctorid = json.loads(dbget("select count(*) as doctor_id from new.doctor_profile where doctor_profile_id ='"+d['doctor_id']+"'"))
mobile = json.loads(dbget("select count(*) as mobile from new.user_profile where mobile ='"+d['mobile']+"'"))
businessid = json.loads(dbget("select count(*) as business_id from new.business_profile where business_id ='"+str(d['business_id'])+"'"))
if mobile[0]['mobile'] == 1 and doctorid[0]['doctor_id'] == 1 and businessid[0]['business_id'] == 1:
dt = datetime.datetime.strptime(d['business_date'], '%Y-%m-%d').date() # to convert string to date format
currenttime = datetime.datetime.now() # to get current datetime
todaydate = currenttime.date()
todaytime = currenttime.strftime('%H:%M') # to get current time
doc_id = {k: v for k, v in d.items() if k in ('doctor_id', 'business_id')}
docidval = doc_id.get("doctor_id")
bus_id = doc_id.get("business_id")
x = currenttime.strftime("%a")[:3].lower()
b = json.loads(dbget("select end_timing from new.timing where doctor_id = '" + str(docidval)+"' and business_id = '"+str(bus_id)+"'\
and day = '"+str(x)+"' and session='evening'"))
etime = b[0]['end_timing'] # to get value from list
future = todaydate+timedelta(days=6)
if dt <= future and etime > todaytime:
a = {k: v for k, v in d.items() if k in ('doctor_id', 'business_id', 'business_date')}
res = json.loads(gensql('select', 'new.token_no', 'count(*)', a))
if res[0]['count'] == 0:
a['token_no'] = 0
gensql('insert', 'new.token_no', a)
token = json.loads(gensql('select', 'new.token_no', 'token_no', a))
if token[0]['token_no'] ==0:
no = 1
else:
no = token[0]['token_no'] + 1
d['token_no'] = no
d['token_time'] = currenttime
app =json.loads(dbget("select app_id from new.app_id "))
d['app_id'] = app[0]['app_id']+1
appoint_id = d['app_id']
dbput("update new.app_id set app_id ='"+str(d['app_id'])+"'")
gensql('insert', 'new.appointment', d)
wt = json.loads(dbget("select average_waiting_time from new.doctorinbusiness where doctor_id = '"+str(docidval)+"' and business_id = '"+str(bus_id)+"'"))
waittime = wt[0]['average_waiting_time']
if no == 1:
avg_wait = 0
else:
avg_wait = (no - 1) * waittime
dbput("update new.token_no set token_no ='"+str(no)+"' where doctor_id='"+str(a['doctor_id'])+"' and business_id = '"+str(bus_id)+"'")
return (json.dumps({'Message': 'Token Generated', 'Message_Code': 'TGS', 'Status': 'success', 'Token_No': no,'Waiting_Time':avg_wait,'Appointment_id':appoint_id}, indent=4))
else:
return (json.dumps({"Message": "Token should be Generated only for This Week ", "Message_Code": "TGTW","Service_Status": "Failure"},
indent=4))
else:
return(json.dumps({'Message': 'Invalid Data', 'Message_Code': 'ID', 'Status': 'Failure'},indent=4))
except:
return (json.dumps({"Message": "Token Generation UnSuccessful", "Message_Code": "TGUS", "Service_Status": "Failure"},indent=4))
def HOTEL_AR_POST_SELECT_ReversePayment(request):
d = request.json
app_datetime = application_date()
Posting_date = app_datetime[1]
s = {}
account_invoie = json.loads(
dbget("select open_amount from account_receivable.accout_inivoice \
where account_number = '" +
str(d['account_no']) + "' and invoice_no = '" +
str(d['invoice_no']) + "'"))
acc_inv = account_invoie[0]['open_amount'] + d['posting_amount']
print(acc_inv)
acc_set = json.loads(
dbget("select account_balance from account_receivable.account_setup \
where account_number = '" +
str(d['account_no']) + "'"))
setupamount = acc_set[0]['account_balance'] + d['posting_amount']
print(setupamount)
a = {
k: v
for k, v in d.items() if v != ''
if k not in ('account_no', 'invoice_no', 'posting_payment_id')
}
print(a)
e = {
k: v
for k, v in d.items() if k != ''
if k in ('account_no', 'invoice_no', 'posting_payment_id')
}
print(e)
a['posting_date'] = Posting_date
a['posting_status'] = "Reverse Payment"
a['posting_amount'] = -(d['posting_amount'])
gensql('update', 'account_receivable.invoice_payment', a, e)
sql = dbput(
"update account_receivable.account_setup set account_balance = '" +
str(setupamount) + "' \
where account_number = '" + d['account_no'] + "'")
psql = dbput(
"update account_receivable.accout_inivoice set open_amount = '" +
str(acc_inv) + "' \
where account_number = '" + d['account_no'] +
"' and invoice_no = '" + str(d['invoice_no']) + "' ")
payment = json.loads(
dbget("select payment_code_id from account_receivable.invoice_payment \
where account_no = '" + d['account_no'] +
"' and posting_payment_id = '" + str(e['posting_payment_id']) +
"'"))
s['invoice_id'] = str(d['invoice_no'])
s['account_no'] = d['account_no']
s['payment_type_id'] = payment[0]['payment_code_id']
s['post_date'] = Posting_date
s['payment_amount'] = -(d['posting_amount'])
gensql('insert', 'account_receivable.account_pay_history', s)
return (json.dumps(
{
"Return": "Record Updated Successfully",
"ReturnCode": "RUS",
"Status": "Success",
"StatusCode": "200"
},
indent=4))
def HOTEL_FD_POST_INSERT_UpdateQueueRreservation(request):
d = request.json
status = json.loads(dbget("SELECT res_guest_status FROM reservation.res_reservation \
where res_unique_id='"+d['Res_unique_id']+"' and res_id='"+d['Res_id']+"'"))
if status[0]['res_guest_status'] in ('checkin','checkout','due out'):
return(json.dumps({'Status': 'Success', 'StatusCode': '200','Return': 'Can not Put in Queue. Reservation checked-in','ReturnCode':'CND'}, sort_keys=True, indent=4))
app_datetime = application_date()
totday_date = app_datetime[1]
sql_value = json.loads(dbget("select res_arrival from reservation.res_reservation where res_id = '"+str(d['Res_id'])+"' and res_unique_id = '"+str(d['Res_unique_id'])+"'"))
print(totday_date)
arrival = datetime.datetime.strptime(sql_value[0]['res_arrival'],'%Y-%m-%d').date()
totday_date = datetime.datetime.strptime(totday_date,'%Y-%m-%d').date()
if totday_date == arrival:
res_id = d.get("Res_id")
rm_room = d.get("rm_room")
unique_id = d.get("Res_unique_id")
#print(rm_room,type(rm_room))
rm_room = rm_room.split(',')
print(rm_room,type(rm_room))
rm_room = str(rm_room)[1:-1]
#print(rm_room)
#RES_Log_Time = datetime.datetime.utcnow()+datetime.timedelta(hours=5, minutes=30)
RES_Log_Time = app_datetime[0]
#print(RES_Log_Time)
RM_Queue_Date =app_datetime[1]
#print(RM_Queue_Date)
#RM_Queue_Date = str(RM_Queue_Date)
psql = dbget("select rm_room_type,rm_room_status,rm_fo_status,rm_room_class from room_management.rm_room_list \
where rm_room in ("+rm_room+")")
#print(psql,type(psql))
psql = json.loads(psql)
#print(psql)
room_type = psql[0]['rm_room_type']
#print(room_type)
room_status = psql[0]['rm_room_status']
fo_status = psql[0]['rm_fo_status']
room_class = psql[0]['rm_room_class']
sql = dbget("select count(*)from room_management.rm_queue_room")
sql = json.loads(sql)
i = str(sql[0]['count'])
#print(sql[0]['count'])
#print(i,type(i))
for number in i:
print(number,type(number))
number = int(number)
number += 1
#print(number)
s={}
s['rm_queue'] = number
s['res_id'] = res_id
s['rm_qtime'] = RES_Log_Time
s['res_unique_id'] = unique_id
sql = gensql('insert','room_management.rm_queue_room',s)
#print(sql)
return(json.dumps({'Status': 'Success', 'StatusCode': '200','Return': 'Record Updated Successfully','QueueNumber':number,'ReturnCode':'RUS'}, sort_keys=True, indent=4))
else:
return(json.dumps({'Status': 'Success', 'StatusCode': '200','Return': 'Place Queue for Today Reservation only','ReturnCode':'PQTR'}, sort_keys=True, indent=4))
def Select_BusinessandDoctors(request):
try:
st_time = time.time()
d = request.json
# Get all business profile from db dpends on county and city
business = json.loads(
dbget("SELECT * FROM new.business_profile where country='" +
d['country'] + "'"
" and city='" + d['city'] + "' "))
#print(business)
specialist_type = list(set(bus['specialist'] for bus in business))
print(specialist_type)
specialist = []
doc_only_specialist = {}
#This loop segregate business based on specialist
for bus in business:
#print(bus)
typeofspecialist = bus['specialist']
index_no = specialist_type.index(typeofspecialist)
try:
bus['cli_img'] = ""
bus.update({
"cli_subimages1": "",
"cli_subimages2": "",
"cli_subimages3": ""
})
bus['cli_feedback'] = json.loads(
dbget("select count(*) from new.feedback where "
"business_id='" + str(bus['business_id']) +
"'"))[0]['count']
bus['cli_doc_count'] = json.loads(
dbget("select count(*) from new.doctorinbusiness where "
"business_id='" + str(bus['business_id']) +
"'"))[0]['count']
specialist[index_no].append(bus)
except:
bus['cli_img'] = ""
bus.update({
"cli_subimages1": "",
"cli_subimages2": "",
"cli_subimages3": ""
})
bus['cli_feedback'] = json.loads(
dbget("select count(*) from new.feedback where "
"business_id='" + str(bus['business_id']) +
"'"))[0]['count']
bus['cli_doc_count'] = json.loads(
dbget("select count(*) from new.doctorinbusiness "
"where business_id='" + str(bus['business_id']) +
"'"))[0]['count']
specialist.append([bus])
#print("sp",specialist)
# Main loop
for i in specialist:
for a in i:
b_id = a['business_id']
#print("id",b_id)
new_dict = {
k: v
for k, v in a.items()
if k in ('business_name', 'area', 'address',
'location_lat', 'location_long')
}
# Get the timing based on business
timing = json.loads(
dbget("select day,start_timing,end_timing from new.timing "
"where business_id='" + str(a['business_id']) + "'"
"and doctor_id='0' "))
#print("timing",timing)
# Loop for format timing of business profile
for t in timing:
timing[timing.index(t)] = {
'day':
t['day'],
'time':
"" + datetime.strptime(t['start_timing'],
"%H:%M").strftime("%I:%M %p") +
"-"
"" + datetime.strptime(t['start_timing'],
"%H:%M").strftime("%I:%M %p") +
""
}
new_dict['clinic_images'] = [{
"img": ""
}, {
"img": ""
}, {
"img": ""
}]
new_dict['clinic_timings'] = timing
doctorinbusiness = json.loads(
dbget("select * from new.doctor_profile where "
"doctor_profile_id in (select doctor_id from "
"new.doctorinbusiness where business_id='" +
str(b_id) + "')"))
# Doctor details inside the business details
for docinbus in doctorinbusiness:
docinbus['doctor_details'] = [docinbus.copy()]
doc_timing = json.loads(
dbget(
"select day,start_timing,end_timing from new.timing "
"where business_id='" + str(a['business_id']) + "'"
"and doctor_id='" + docinbus['doctor_profile_id'] +
"' "))
#print("doc timing", doc_timing)
# Loop for format timing of doctor profile
for t in doc_timing:
#print("t",t,type(t))
doc_timing[doc_timing.index(t)] = {
'day':
t['day'],
'time':
"" +
datetime.strptime(t['start_timing'],
"%H:%M").strftime("%I:%M %p") +
"-"
"" +
datetime.strptime(t['start_timing'],
"%H:%M").strftime("%I:%M %p") +
""
}
#print("doc_timing", doc_timing,type(doc_timing))
docinbus['doctor_details'][0][
'doctorstimings'] = doc_timing
docinbus['doctor_details'][0]['doctor_clinic'] = json.loads(
dbget(
"select * from new.business_profile where "
" business_id in (SELECT business_id FROM new.doctorinbusiness "
" where doctor_id='" +
docinbus['doctor_profile_id'] + "')"))
docinbus['doctor_details'][0][
'doctor_feedback'] = json.loads(
dbget(
"select * from new.feedback where business_id"
"='" + str(a['business_id']) + "'"
" and doctor_id='" +
docinbus['doctor_profile_id'] + "'"))
docinbus['doctor_details'][0]['doctor_clinic_img'] = [{
"img":
""
}, {
"img":
""
}, {
"img":
""
}]
docinbus['doctor_details'][0][
'doctor_specialization'] = json.loads(
dbget(
"SELECT new.specialization.* "
" FROM new.doctor_profile join new.doctor_specialization on"
" doctor_profile.doctor_profile_id = doctor_specialization.doctor_id"
" join new.specialization on doctor_specialization.specialization_id = "
" specialization.specialization_id"
" where doctor_profile.doctor_profile_id='" +
str(a['business_id']) + "'"))
docinbus['doctor_details'][0]['doctor_services'] = json.loads(
dbget(
"SELECT new.services.* "
" FROM new.doctor_profile join new.doctor_services on"
" doctor_profile.doctor_profile_id = doctor_services.doctor_id"
" join new.services on doctor_services.service_id = services.service_id"
" where doctor_profile.doctor_profile_id='" +
str(a['business_id']) + "'"))
new_dict['clinic_doctor_list'] = doctorinbusiness
for doc in doctorinbusiness:
#if doc['specialist'] not in doc_only_specialist:
# doc_only.append(doc)
try:
print("try")
print(doc_only_specialist['' + doc['specialist'] + ''])
doc_only_specialist['' + doc['specialist'] +
''].append(doc)
except:
print("except")
doc_only_specialist['' + doc['specialist'] +
''] = [doc]
# Get service data for business profile
bus_service = json.loads(
dbget(
"select new.services.* from new.doctor_services"
" join new.services"
" on doctor_services.service_id = services.service_id where"
" new.doctor_services.doctor_id"
" in (select doctor_id from new.doctorinbusiness"
" where business_id='" + str(b_id) + "') "))
service = []
service_set = set()
# Loop for format service datas for business profile
for b_ser in bus_service:
if b_ser['service_name'] not in service_set:
service.append({"service": b_ser['service_name']})
service_set.add(b_ser['service_name'])
new_dict['clinic_services'] = service
new_dict['clinic_open'] = "Open Today"
a["clinic_details"] = [new_dict]
print("doc_only_specialist", doc_only_specialist)
if i[0]['specialist'] in doc_only_specialist:
doc_list = doc_only_specialist["" + i[0]['specialist'] + ""]
else:
doc_list = []
specialist[specialist.index(i)] = {
"name": i[0]['specialist'],
"icon": "",
"Listofdoctors": [{
"clinics": i,
"Doctors": doc_list
}]
}
print("sm_sp", specialist)
ed_time = time.time()
full_time = ed_time - st_time
print("spcialist", specialist)
print("Time Taken", full_time)
return (json.dumps(
{
"Message": "Records Selected Sucessfully",
"Message_Code": "RSS",
"Service_Status": "Success",
"specialist": specialist
},
indent=4))
except:
return (json.dumps(
{
"Message": "Records Un Selected Sucessfully",
"Message_Code": "RUS",
"Service_Status": "Success",
"specialist": specialist
},
indent=4))
def Hotel_RES_Get_Select_QueryRoomRouting(request):
d = request.json
sql_value = json.loads(dbget("select * from reservation.res_room_routing \
where res_id = "+str(d['res_id'])+" and res_unique_id = "+str(d['res_unique_id'])+""))
return(json.dumps({'Status': 'Success', 'StatusCode': '200','ReturnValue':sql_value ,'ReturnCode':'RRTS'},indent=4))
def tokengeneration(request):
try:
d = request.json
mob = json.loads(
dbget(
"select count(*) as mobile from new.appointment where mobile ='"
+ d['mobile'] + "'\
and business_date = '" +
str(d['business_date']) + "' and doctor_id ='" +
str(d['doctor_id']) + "'\
and business_id = '" + str(d['business_id']) +
"'"))
print(mob)
if mob[0]['mobile'] == 1:
wt = json.loads(
dbget(
"select average_waiting_time from new.doctorinbusiness where doctor_id = '"
+ str(d['doctor_id']) + "'\
and business_id = '" +
str(d['business_id']) + "'"))
waittime = wt[0]['average_waiting_time']
print(waittime)
tk_no = json.loads(
dbget("select token_no from new.appointment where mobile='" +
d['mobile'] + "'\
and doctor_id = '" + str(d['doctor_id']) +
"' and business_id = '" + str(d['business_id']) + "'"))
print(tk_no)
num = tk_no[0]['token_no']
print(num)
if num == 1:
avg_wait = 0
else:
avg_wait = (num - 1) * waittime
appid = json.loads(
dbget("select app_id from new.appointment where mobile = '" +
d['mobile'] + "'\
and doctor_id = '" +
str(d['doctor_id']) + "' and business_id = '" +
str(d['business_id']) + "'"))
print('moohan', appid)
app_id = appid[0]['app_id']
return (json.dumps(
{
'Message': 'Token Already Generated',
'Message_Code': 'TAG',
'Token_No': num,
'Status': 'Sucess',
'Waiting_Time': avg_wait,
'Appointment_id': app_id
},
indent=4))
else:
doctorid = json.loads(
dbget(
"select count(*) as doctor_id from new.doctor_profile where doctor_profile_id ='"
+ d['doctor_id'] + "'"))
mobile = json.loads(
dbget(
"select count(*) as mobile from new.user_profile where mobile ='"
+ d['mobile'] + "'"))
businessid = json.loads(
dbget(
"select count(*) as business_id from new.business_profile where business_id ='"
+ str(d['business_id']) + "'"))
if mobile[0]['mobile'] == 1 and doctorid[0][
'doctor_id'] == 1 and businessid[0]['business_id'] == 1:
dt = datetime.datetime.strptime(
d['business_date'],
'%Y-%m-%d').date() # to convert string to date format
currenttime = datetime.datetime.now(
) # to get current datetime
print("currenttime", currenttime)
todaydate = currenttime.date()
todaytime = currenttime.strftime(
'%H:%M') # to get current time
print("todaytime", todaytime)
doc_id = {
k: v
for k, v in d.items() if k in ('doctor_id', 'business_id')
}
docidval = doc_id.get("doctor_id")
bus_id = doc_id.get("business_id")
x = currenttime.strftime("%a")[:3].lower()
b = json.loads(
dbget("select * from new.timing where doctor_id = '" +
str(docidval) + "' and business_id = '" +
str(bus_id) + "'\
and day = '" + str(x) +
"' order by session"))
print('end_timing', b)
etime = b[0]['end_timing'] # to get value from list
print(etime)
future = todaydate + timedelta(days=6)
if dt <= future and dt >= todaydate:
#if dt == todaydate:
if etime <= todaytime:
print("etime", etime)
return ("token can not genarate After business time")
a = {
k: v
for k, v in d.items()
if k in ('doctor_id', 'business_id', 'business_date')
}
print('aaaaa', a)
res = json.loads(
gensql('select', 'new.token_no', 'count(*)', a))
if res[0]['count'] == 0:
a['token_no'] = 0
gensql('insert', 'new.token_no', a)
token = json.loads(
gensql('select', 'new.token_no', 'token_no', a))
if token[0]['token_no'] == 0:
no = 1
else:
no = token[0]['token_no'] + 1
d['token_no'] = no
print('tokennumber', no)
d['token_time'] = currenttime
app = json.loads(dbget("select app_id from new.app_id "))
d['app_id'] = app[0]['app_id'] + 1
appoint_id = d['app_id']
dbput("update new.app_id set app_id ='" +
str(d['app_id']) + "'")
gensql('insert', 'new.appointment', d)
wt = json.loads(
dbget(
"select average_waiting_time from new.doctorinbusiness where doctor_id = '"
+ str(docidval) + "' and business_id = '" +
str(bus_id) + "'"))
waittime = wt[0]['average_waiting_time']
if no == 1:
avg_wait = 0
else:
avg_wait = (no - 1) * waittime
dbput("update new.token_no set token_no ='" + str(no) +
"' where doctor_id='" + str(a['doctor_id']) +
"' and business_id = '" + str(bus_id) + "'")
#app_mail(appoint_id, docidval, bus_id,avg_wait )
return (json.dumps(
{
'Message': 'Token Generated',
'Message_Code': 'TGS',
'Status': 'success',
'Token_No': no,
'Waiting_Time': avg_wait,
'Appointment_id': appoint_id
},
indent=4))
else:
return ("Token genarate only future 6 days")
else:
return (json.dumps(
{
"Message": "Please Give a valid Data ",
"Message_Code": "PGVD",
"Service_Status": "Failure"
},
indent=4))
except:
return (json.dumps(
{
"Message": "Token Generation UnSuccessful",
"Message_Code": "TGUS",
"Service_Status": "Failure"
},
indent=4))
def insert_Doctorsprofile(request):
try:
doctors = request.json['doctors']
business = request.json['business_id']
password = request.json['password']
login_status = request.json['login_status']
wait_time = request.json['average_waiting_time']
doc_record = {
k: v
for k, v in doctors.items()
if k not in ('Specialization', 'services')
}
speclaization = doctors['Specialization']
service = doctors['services']
if len(speclaization) == 0 and len(service) == 0:
return (json.dumps(
{
"Message": "Specialization and Service are Empty",
"Message_Code": "SSE",
"Service_Status": "Failure"
},
indent=4))
doc_id = json.loads(
dbget("select max(doctor_id) as doc_id from new.doctor_profile"))
doctor_id = int(doc_id[0]['doc_id']) + 1
doc_record['doctor_profile_id'] = doctors['doctor_name'][:4] + str(
doctor_id)
gensql('insert', 'new.doctor_profile', doc_record)
for sep in speclaization:
i = {}
i['doctor_id'] = doctors['doctor_name'][:4] + str(doctor_id)
i['specialization_id'] = sep
gensql('insert', 'new.doctor_specialization', i)
for ser in service:
i = {}
i['doctor_id'] = doctors['doctor_name'][:4] + str(doctor_id)
i['service_id'] = ser
gensql('insert', 'new.doctor_services', i)
business = request.json['business_id']
doctor_id = doctors['doctor_name'][:4] + str(doctor_id)
docandbus = {
'business_id': business,
'doctor_id': doctor_id,
'password': password,
'login_status': login_status,
'average_waiting_time': wait_time
}
gensql('insert', 'new.doctorinbusiness', docandbus)
return (json.dumps(
{
"Message": "Record Inserted Successfully",
"Message_Code": "RIS",
"Service": "Success",
"doctor_id": doctor_id
},
indent=4))
except:
return (json.dumps(
{
"Message": "Record Inserted Unsuccessful",
"Message_Code": "RIUS",
"Service_Status": "Failure"
},
indent=4))
def QueryProfileAcitivitylog(request):
pf_id = request.json['pf_id']
sql_value = json.loads(dbget("select * from profile.pf_profile_activitylog join reservation.employee on profile.pf_profile_activitylog.emp_id = reservation.employee.emp_id where pf_id='"+pf_id+"' "))
print(sql_value)
return(json.dumps({'Status': 'Success', 'StatusCode': '200','ReturnValue':sql_value ,'ReturnCode':'RRTS'},indent=4))
def HOTEL_BBL_POST_INSERT_PayMasterReservation(request):
d = request.json
x, s = {}, {}
block_id = d.get("block_id")
app_datetime = application_date()
#RES_Log_Time = datetime.datetime.utcnow()+datetime.timedelta(hours=5, minutes=30)
RES_Log_Time = app_datetime[0]
print(RES_Log_Time)
RES_Log_Date = app_datetime[1]
print(RES_Log_Date)
psql = json.loads(
dbget(
"select count (*) from reservation.res_reservation where res_block_code='"
+ block_id + "' and res_room_type in('PM')"))
#print("sds",psql)
count = psql[0]['count']
#print("sfd",type(count),count)
if psql[0]['count'] > 0:
return (json.dumps(
{
'Status': 'Success',
'StatusCode': '200',
'Return': 'Reservation Already Exist',
'ReturnCode': 'RAE'
},
sort_keys=True,
indent=4))
sql = json.loads(
dbget(
"select start_date,end_date,block_name,nights,pf_id from business_block.business_block_definite where block_id = '"
+ block_id + "'"))
startdate = sql[0]['start_date']
enddate = sql[0]['end_date']
name = sql[0]['block_name']
x['res_arrival'] = startdate
x['res_depature'] = enddate
x['res_nights'] = sql[0]['nights']
x['pf_id'] = sql[0]['pf_id']
x['res_block_code'] = block_id
x['res_block'] = sql[0]['pf_id']
x['res_guest_status'] = "Definite Block"
x['res_room_type'] = "PM"
x['res_number_of_rooms'] = str(1)
x['created_by'] = "Admin"
x['created_on'] = RES_Log_Date
select = json.loads(dbget("select * from reservation.res_id"))
print(select, type(select), len(select))
print(select[0]['id'])
Res_id = (select[0]['id'] + 1)
print(Res_id)
update = dbput("update reservation.res_id set id = '" +
str(select[0]['id'] + 1) + "'")
x['Res_id'] = Res_id
sqlvalue = json.loads(
dbget("select confirmation_no from business_block.group_confirmation"))
print(sqlvalue, type(sqlvalue))
sqlvalue = int(sqlvalue[0]['confirmation_no'])
confirmation_no = sqlvalue + 1
print(confirmation_no, type(confirmation_no))
psql = dbput(
"update business_block.group_confirmation set confirmation_no = '" +
str(confirmation_no) + "'")
print(psql)
d['res_confnumber'] = confirmation_no
psqlvalue = gensql('insert', 'reservation.res_reservation', x)
print(psqlvalue)
s = {}
s['user_role'] = "Admin"
s['date'] = RES_Log_Date
s['time'] = RES_Log_Time
s['block_id'] = block_id
s['action_type_id'] = "PAY MASTER"
s['description'] = "Pay Master will be created" + " " + str(name)
gensql('insert', 'business_block.business_block_activity_log', s)
return (json.dumps(
{
"Return": "Record Inserted Successfully",
"ReturnCode": "RIS",
"Status": "Success",
"StatusCode": "200"
},
indent=4))
def hotel_rm_post_select_queryroomstatistics(request):
sql = 'select * from room_management.RM_Room_List order by rm_room'
#print(sql)
db_res = json.loads(dbget(sql))
#print(db_res,type(db_res),len(db_res))
Total_Rooms, Total_Clean, Total_Dirty, Total_Inspected, Total_oo, Total_os = len(
db_res), 0, 0, 0, 0, 0
nres_clean, nres_dirty, nres_insp, nres_os = 0, 0, 0, 0
res_clean, res_dirty, res_insp, res_os = 0, 0, 0, 0
for i in db_res:
status = i['rm_room_status']
res_status = i['rm_reservation_status']
if status == 'dirty':
Total_Dirty += 1
if res_status == 'not_reserved':
nres_dirty += 1
else:
res_dirty += 1
elif status == 'inspected':
Total_Inspected += 1
if res_status == 'not_reserved':
nres_insp += 1
else:
res_insp += 1
elif status == 'clean':
Total_Clean += 1
if res_status == 'not_reserved':
nres_clean += 1
else:
res_clean += 1
elif status == 'oo':
Total_oo += 1
if res_status == 'not_reserved':
nres_os += 1
else:
res_os += 1
elif status == 'os':
Total_os += 1
if res_status == 'not_reserved':
nres_os += 1
else:
res_os += 1
print(Total_Dirty, Total_Inspected, Total_Clean, Total_oo, Total_os)
print(nres_clean, nres_dirty, nres_insp, nres_os)
print(res_clean, res_dirty, res_insp, res_os)
d = {}
d['Total_Rooms'], d['Total_Dirty'], d['Total_Inspected'], d[
'Total_Clean'], d['Total_oo'], d[
'Total_os'] = Total_Rooms, Total_Dirty, Total_Inspected, Total_Clean, Total_oo, Total_os
d['nres_clean'], d['nres_dirty'], d['nres_insp'], d[
'nres_os'] = nres_clean, nres_dirty, nres_insp, nres_os
d['res_clean'], d['res_dirty'], d['res_insp'], d[
'res_os'] = res_clean, nres_dirty, res_insp, res_os
print(d)
return (json.dumps(
{
'Status': 'Success',
'StatusCode': '200',
'ReturnValue': d,
'ReturnCode': 'RRTS'
},
indent=4))
def HOTEL_REM_POST_INSERT_UpdateRatecodeSetup(request):
d = request.json
#print(d)
e = {
k: v
for k, v in d.items()
if k in ('begin_sell_date', 'end_sell_date', 'market_id', 'source_id',
'display_sequence')
}
print("0000", e)
gensql('insert', 'revenue_management.ratecode', d['rate_code_details'])
#chang to max
ratecode_id = json.loads(
gensql('select', 'revenue_management.ratecode',
'max(ratecode_id) as id1', d['rate_code_details']))
print(ratecode_id[0]['id1'])
e['ratecode_id'] = ratecode_id[0]['id1']
print("1111", e)
room_types = d['room_types']
room_id = json.loads(
dbget(
"select max(rooms_id) as number1 from revenue_management.rooms_selected"
))
print("room_id", room_id[0]['number1'])
for i in d['room_types']:
#pass
dbput(
"insert into revenue_management.rooms_selected (rooms_id,room_type_id) \
values ('" + str(room_id[0]['number1'] + 1) + "','" + str(i) +
"') ")
e['rooms_id'] = int(room_id[0]['number1'] + 1)
print("2222", e)
package_id = json.loads(
dbget(
"select max(packages_id) as number2 from revenue_management.packages_selected"
))
print("package_id", package_id[0]['number2'],
type(package_id[0]['number2']))
for j in d['package']:
#pass
dbput(
"insert into revenue_management.packages_selected (packages_id,package_code_id) \
values ('" + str(package_id[0]['number2'] + 1) + "','" +
str(j) + "')")
e['packages_id'] = int(package_id[0]['number2'] + 1)
print("3333", e)
gensql('insert', 'revenue_management.sell_control', d['sell_controls'])
# change to max
sell_id = json.loads(
gensql('select', 'revenue_management.sell_control',
'max(sell_id) as id2', d['sell_controls']))
print("sell_id", sell_id[0]['id2'])
e['sell_control_id'] = int(sell_id[0]['id2'])
print("4444", e)
gensql('insert', 'revenue_management.tranction_details',
d['transaction_details'])
# change to max
transaction_detail_id = json.loads(
gensql('select', 'revenue_management.tranction_details',
'max(tranction_detail_id) as id3', d['transaction_details']))
print("transaction_detail_id", transaction_detail_id[0]['id3'])
e['transaction_details_id'] = int(transaction_detail_id[0]['id3'])
print("5555", e)
gensql('insert', 'revenue_management.rate_components', d['components'])
# change to max
components_id = json.loads(
gensql('select', 'revenue_management.rate_components',
'max(components_id) as id4', d['components']))
print("components_id", components_id[0]['id4'])
e['rate_components_id'] = int(components_id[0]['id4'])
print("6666", e)
gensql('insert', 'revenue_management.ratecode_setup', e)
return (json.dumps(
{
'Status': 'Success',
'StatusCode': '200',
'Return': 'Record Inserted Successfully',
'ReturnCode': 'RIS'
},
sort_keys=True,
indent=4))
def HOTEL_RES_GET_SELECT_RoomUnassign(request):
Res_id = request.json['Res_id']
print(Res_id)
Res_id = Res_id.split(',')
Res_id = str(Res_id)[1:-1]
Res_unique_id = request.json['Res_unique_id']
Res_unique_id = Res_unique_id.split(',')
Res_unique_id = str(Res_unique_id)[1:-1]
sqlvalue = json.loads(dbget("select res_room,res_room_type,res_block_code from reservation.res_reservation where res_id in ("+Res_id+") and res_unique_id in ("+Res_unique_id+")"))
print("sqlval",sqlvalue)
if sqlvalue[0]['res_block_code'] is not None and sqlvalue[0]['res_block_code'] != 'PM':
if sqlvalue[0]['res_room_type']== 'Kngn':
sql = dbput("update business_block.current_grid set kngn = kngn -'1' where block_id='"+str(sqlvalue[0]['res_block_code'])+"' and grid_type =3")
print(sql)
elif sqlvalue[0]['res_room_type'] =='Kngs':
sql = dbput("update business_block.current_grid set kngs = kngs-'1' where block_id='"+str(sqlvalue[0]['res_block_code'])+"' and grid_type =3")
print(sql)
elif sqlvalue[0]['res_room_type'] =='Ksbn':
sql = dbput("update business_block.current_grid set Ksbn = Ksbn-'1' where block_id='"+str(sqlvalue[0]['res_block_code'])+"' and grid_type =3")
print(sql)
elif sqlvalue[0]['res_room_type'] == 'Ksbs':
print("workingits fine")
sql = dbput("update business_block.current_grid set ksbs = ksbs -'1' where block_id='"+str(sqlvalue[0]['res_block_code'])+"' and grid_type =3")
print(sql)
elif sqlvalue[0]['res_room_type'] =='Sjsn' :
sql = dbput("update business_block.current_grid set sjsn = sjsn-'1' where block_id='"+str(sqlvalue[0]['res_block_code'])+"' and grid_type =3")
print(sql)
elif sqlvalue[0]['res_room_type'] =='Sdbn':
sql = dbput("update business_block.current_grid set sdbn = sdbn -'1' where block_id='"+str(sqlvalue[0]['res_block_code'])+"' and grid_type =3")
print(sql)
elif sqlvalue[0]['res_room_type'] =='Sjss':
sql = dbput("update business_block.current_grid set sjss = sjss -'1' where block_id='"+str(sqlvalue[0]['res_block_code'])+"' and grid_type =3")
print(sql)
elif sqlvalue[0]['res_room_type'] =='Comp':
sql = dbput("update business_block.current_grid set comp = comp -'1' where block_id='"+str(sqlvalue[0]['res_block_code'])+"' and grid_type =3")
print(sql)
else:
pass
pickup = dbput("update business_block.room_revenue set room_nights_picked = room_nights_picked + '1' where block_id='"+str(sqlvalue[0]['res_block_code'])+"'")
print(pickup)
rooms = ''
for i in sqlvalue:
if len(rooms) == 0:
rooms += "'"+str(i['res_room'])+"'"
else:
rooms += ","+"'"+str(i['res_room'])+"'"
print(rooms)
fo_status = "vacant"
res_status = "not reserved"
psql = dbput("update room_management.rm_room_list set rm_fo_status = '"+fo_status+"',rm_reservation_status = '"+res_status+"',rm_fo_person = '0' where rm_room in ("+rooms+")")
print(psql)
data = '0'
sql = dbput("update reservation.res_reservation set res_room = "+data+" where res_id in ("+Res_id+") and res_unique_id in ("+Res_unique_id+") ")
print(sql)
return(json.dumps({'Status': 'Success', 'StatusCode': '200','ReturnValue':'Record Updated Successfully' ,'ReturnCode':'RUS'},indent=4))
def sendemailconfirmation(request):
#print(name,email,type(email),message,conf_no,arrival,depature, room_type)
sys.stdout.flush()
e = request.json
print(e)
Hotel_name = 'Kconnect24/7'
business_id = request.json['business_id']
con_no = request.json['customer_confirmation_number']
print(con_no, type(con_no))
rate_day = json.loads(
dbget("select * from customer_rate_detail where \
business_id='" + str(business_id) +
"' and customer_confirmation_number='" + con_no + "' "))
print("rate_day", rate_day)
d = json.loads(
dbget(
"SELECT ivr_room_customer_booked.*,ivr_hotel_list.* FROM public.ivr_room_customer_booked \
join ivr_hotel_list on \
ivr_room_customer_booked.business_id = ivr_hotel_list.business_id\
where ivr_room_customer_booked.business_id='" +
str(business_id) + "' \
and ivr_room_customer_booked.customer_confirmation_number='"
+ str(con_no) + "' "))
print("d", d)
#print(d[0]['customer_amount'],type(d[0]['customer_amount']))
email = ['*****@*****.**', '*****@*****.**']
#email.append(d[0]['email'])
#email.append(d[0]['customer_email'])
print(email)
on = d[0]['booked_date']
print(on[:11], type(on[:11]))
booked_on = parser.parse(on[:11]).date().strftime('%Y-%m-%d')
sender = "*****@*****.**"
t_body = """
<tr style="border:1px solid gray">
<th align="left">Date</th>
<th align="left">Price Per Night in $</th>
</tr>
"""
for rate in rate_day:
print(rate)
t_body += """
<tr style="border:1px solid gray">
<td>""" + rate['rate_date'] + """</td>
<td align="left">""" + str(rate['amount']) + """</td>
</tr>
"""
t_body += """
<tr style="border:1px solid gray">
<td><b>Total</b></td>
<td align="left">""" + str(
d[0]['customer_amount']) + """</td>
</tr>
"""
print("t_body-----------------------")
print(t_body)
for receiver in email:
#print(sender,type(sender),receiver,type(receiver))
subject = "Hotel Booking"
msg = MIMEMultipart()
msg['from'] = sender
msg['to'] = receiver
msg['subject'] = subject
html = """\
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Title of the document</title>
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
</head>
<body>
<div class="panel panel-primary">
<div class="panel-body">
<div class="row" style="border:1px solid lightgrey;margin:10px ">
<div class="col-md-4" style="padding:10px;">
<h1 align="center" style="color:#216CEC;border-bottom:1px solid grey">konnect 247</h1>
<br>
<p><b> Dear Customer,</b></p>
<p style="margin-left:100px">Thank you for Choosing our Hotel, It is our pleasure to confirm your reservation as follows. </p>
<p style="line-height:0.7;padding-top:20px;"> <span>Arrival</span> <span style="margin-left:25em">Guest Name:</span></p>
<p style="line-height:0.7"><span>""" + str(
d[0]['customer_arrival_date']
) + """</span> <span style="margin-left:23em">""" + str(
d[0]['customer_name']
) + """</span></p>
<p style="line-height:0.7;padding-top:10px;"><span>Departure</span> <span style="margin-left:23.5em">Preferred Language</span></p>
<p style="line-height:0.7"><span>""" + str(
d[0]['customer_depature_date']
) + """</span><span style="margin-left:23em">""" + str(
d[0]['ivr_language']
) + """</span></p>
<p style="line-height:0.7;padding-top:10px;"><span>Hotel Name:</span><span style="margin-left:22.5em">Channel</span></p>
<p style="line-height:0.7"><span>""" + str(
d[0]['hotel_name']
) + """</span><span style="margin-left:22.5em">""" + d[0][
'channel'] + """</span></p>
<p style="line-height:0.7;padding-top:10px;"><span>Total</span> Adult <span style="margin-left:23em">Confirmation Number</span></p>
<p style="line-height:0.7"><span>""" + str(
d[0]['customer_adult']
) + """</span><span style="margin-left:27.5em">""" + d[0][
'customer_confirmation_number'] + """</span></p>
<p style="line-height:0.7;padding-top:10px;"><span>Total Child</span><span style="margin-left:23em"> Booked On</span></p>
<p style="line-height:0.7"><span>""" + str(
d[0]['customer_child']
) + """</span><span style="margin-left:27.5em">""" + booked_on + """</span></p>
<p style="line-height:0.7;padding-top:10px;"><span>Total Price</span><span style="margin-left:23em"> No Of Rooms</span></p>
<p style="line-height:0.7"><span>""" + str(
d[0]['customer_amount']
) + """</span><span style="margin-left:25em">""" + str(
d[0]['customer_no_of_rooms']) + """</span></p>
<hr>
<p style="line-height:0.7">Room Type</p>
<p style="line-height:0.7">""" + d[0][
'customer_room_type'] + """</p>
<p style="line-height:0.7;padding-top:10px;">Guest Name</p>
<p style="line-height:0.7">""" + str(
d[0]['customer_name']) + """ </p>
<p style="line-height:0.7;padding-top:10px;">Max Guest</p>
<p style="line-height:0.7">""" + str(
d[0]['customer_adult'] +
d[0]['customer_child']) + """</p>
<p style="line-height:0.7;padding-top:10px;">Room Options</p>
<p style="float:left"> <img src="https://www.riversidehotel.com.au/wp-content/uploads/2016/01/RH-12.jpg" alt="" width="400px" height="250px">
<span style="float:left;"> <table style=" border:1px solid gray; width:400px;height:250px">
<tbody style="border:1px solid gray">
""" + t_body + """
</tbody>
</table>
</span>
</p>
<br>
<p><b>Address:</b></p>
<p style="margin-left:30px;line-height:0.7"> No,25, 1st cross street,</p>
<p style="margin-left:30px;line-height:0.7"> New Colony,</p>
<p style="margin-left:30px;line-height:0.7"> Chrompet,</p>
<p style="margin-left:30px;line-height:0.7"> Chennai</p>
<h3>Regards,</h3>
<p>Admin- Hotel Management</p>
<hr>
<h5 align="center" style="color:red"> This is an Auto generated Email, Please do not reply.</h5>
</div>
<br>
</div>
</div>
</div>
</body>
</html>
"""
msg.attach(MIMEText(html, 'html'))
gmailuser = '******'
password = '******'
server = smtplib.SMTP('smtp.gmail.com', 587)
server.starttls()
server.login(gmailuser, password)
text = msg.as_string()
server.sendmail(sender, receiver, text)
print("the message has been sent successfully")
server.quit()
return (json.dumps(
{
'Return': 'Message Send Successfully',
"Return_Code": "MSS",
"Status": "Success",
"Status_Code": "200"
},
sort_keys=True,
indent=4))
def Insert_Timing(request):
try:
timing = request.json
doc_timing = {
k: v
for k, v in timing.items() if k in ('doctor_id', 'business_id')
}
if doc_timing['doctor_id'] != 0:
doctor = json.loads(
dbget(
"select count(*) as doc_id from new.doctor_profile where doctor_profile_id ='"
+ doc_timing['doctor_id'] + "'"))
business = json.loads(
dbget(
"select count(*) as bus_id from new.business_profile where business_id ='"
+ str(doc_timing['business_id']) + "'"))
if doctor[0]['doc_id'] == 1 and business[0]['bus_id'] == 1:
days = timing['days']
for day in days:
i = {}
i['doctor_id'] = doc_timing['doctor_id']
i['business_id'] = doc_timing['business_id']
i['day'] = day['day']
i['start_timing'] = day['start_timing']
i['end_timing'] = day['end_timing']
i['session'] = day['session']
gensql('insert', 'new.timing', i)
return (json.dumps(
{
"Message": "Record Inserted Successfully",
"Message_Code": "RIS",
"Service_Status": "Success"
},
indent=4))
else:
return (json.dumps(
{
'Message': 'Invalid Data',
'Message_Code': 'ID',
'Status': 'Failure'
},
indent=4))
else:
days = timing['days']
for day in days:
i = {}
i['doctor_id'] = doc_timing['doctor_id']
i['business_id'] = doc_timing['business_id']
i['day'] = day['day']
i['start_timing'] = day['start_timing']
i['end_timing'] = day['end_timing']
i['session'] = day['session']
gensql('insert', 'new.timing', i)
return (json.dumps(
{
"Message": "Record Inserted Successfully",
"Message_Code": "RIS",
"Service_Status": "Success"
},
indent=4))
except:
return (json.dumps(
{
"Message": "Record Inserted UnSuccessfully",
"MessageCode": "RIUS",
"Service": "UnSuccess"
},
indent=4))
def Hotel_END_OF_Day_POST_Posting_Rooms_charges(request):
print("Hello world")
app_datetime = application_date()
run_charges, d, fixed_rate_id = [], {}, []
date = app_datetime[1]
print(date)
#****************************************Posting fixed rate****************************************************
sql_value = json.loads(
dbget(
"select res_arrival,res_depature,res_rate,res_id,fixed_rate from reservation.res_fixed_rate \
where res_arrival <= '" + str(date) +
"' and res_depature >='" + str(date) +
"' and res_room_type not in ('PM')"))
print(sql_value)
sql_count_value = json.loads(
dbget(
"select count(*) from reservation.res_fixed_rate where res_arrival <= '"
+ str(date) + "' and res_depature >='" + str(date) + "'"))
print(sql_count_value)
fixed_rate_id = ''
status = ['no show', 'cancel', 'waitlist', 'arrival']
if sql_count_value[0]['count'] != 0:
print("its cameeeee")
for i in sql_value:
if len(fixed_rate_id) == 0:
fixed_rate_id += "'" + str(i['res_id']) + "'"
else:
fixed_rate_id += "," + "'" + str(i['res_id']) + "'"
psql = json.loads(
dbget(
"select res_block,res_room,pf_firstname,res_guest_status from reservation.res_reservation \
where res_id = '" + str(i['res_id']) +
"' and res_room_type not in ('PM')"))
for s in psql:
if s['res_guest_status'] not in status:
if s['res_block'] is None:
d['res_room'] = s['res_room']
d['res_id'] = i['res_id']
d['posting_amount'] = i['res_rate']
d['posting_date'] = date
d['post_code_id'] = '33'
d['post_window'] = '1'
d['posting_supplement'] = 'Fixed rate Posting'
d['posting_reference'] = 'Fixed rate Posting room chrages'
d['posting_quantity'] = '1'
d['emp_id'] = 1
gensql('insert', 'cashiering.billing_post', d)
run_charges.append({
'room': s['res_room'],
'name': s['pf_firstname'],
'posting': 'posting room and tax'
})
else:
d['res_room'] = s['res_room']
d['res_id'] = i['res_id']
d['posting_amount'] = i['res_rate']
d['posting_date'] = date
d['post_code_id'] = '33'
d['post_window'] = '2'
d['posting_supplement'] = 'Fixed rate Posting'
d['posting_reference'] = 'Fixed rate Posting room chrages'
d['posting_quantity'] = '1'
d['emp_id'] = 1
gensql('insert', 'cashiering.billing_post', d)
run_charges.append({
'room': s['res_room'],
'name': s['pf_firstname'],
'posting': 'posting room and tax'
})
print("sdadsa", fixed_rate_id, type(fixed_rate_id))
if len(fixed_rate_id) > 0:
#****************************************Posting Rooms and tax charges **************************************************
psqlvalues = json.loads(
dbget(
"select * from reservation.res_reservation \
where res_arrival <= '" + str(date) +
"' and res_depature >='" + str(date) + "' \
and res_id not in (" + fixed_rate_id +
") and res_guest_status not in ('no show','cancel','waitlist','arrival','reserved') and res_room_type not in ('PM')"
))
else:
psqlvalues = json.loads(
dbget("select * from reservation.res_reservation \
where res_arrival <= '" + str(date) +
"' and res_depature >='" + str(date) + "' \
and res_guest_status not in ('no show','cancel','waitlist','arrival','reserved') and res_room_type not in ('PM')"
))
for i in psqlvalues:
data = HOTEL_REM_POST_SELECT_SelectRateForReservation(
date, i['res_rate_code'], i['res_room_type'], i['res_adults'])
if i['res_block'] is None:
d['res_room'] = i['res_room']
d['res_id'] = i['res_id']
d['posting_amount'] = data
d['posting_date'] = date
d['post_code_id'] = '33'
d['post_window'] = '1'
d['posting_supplement'] = 'Room charges Posting'
d['posting_reference'] = 'posting room chrages'
d['posting_quantity'] = '1'
d['emp_id'] = 1
gensql('insert', 'cashiering.billing_post', d)
run_charges.append({
'room': i['res_room'],
'name': i['pf_firstname'],
'posting': 'posting room and tax'
})
else:
d['res_room'] = i['res_room']
d['res_id'] = i['res_id']
d['posting_amount'] = data
d['posting_date'] = date
d['post_code_id'] = '33'
d['post_window'] = '2'
d['posting_supplement'] = 'Room charges Posting'
d['posting_reference'] = 'posting room chrages'
d['posting_quantity'] = '1'
d['emp_id'] = 1
gensql('insert', 'cashiering.billing_post', d)
run_charges.append({
'room': i['res_room'],
'name': i['pf_firstname'],
'posting': 'posting room and tax'
})
#****************************************Fixed Charges ********************************************************************
fixed_charge = json.loads(
dbget("select res_reservation.res_room, \
res_fixed_charges.res_id, fixed_charges_occurrence, fixed_charges_begin_date, \
fixed_charges_end_date, fixed_charges_transaction_code, fixed_charges_article_code, fixed_charges_amount, \
fixed_charges_quantity, fixed_charges_supplement, fixed_charges_id, res_fixed_charges.res_unique_id \
FROM reservation.res_fixed_charges \
left join reservation.res_reservation on res_reservation.res_unique_id = res_fixed_charges.res_unique_id \
where fixed_charges_begin_date <= '" + str(date) +
"' and fixed_charges_end_date >='" + str(date) + "'"))
for fix_cha in fixed_charge:
fixed_charge_code = json.loads(
dbget("select package_code_id from packages.package_code \
where package_code = '" +
fix_cha['fixed_charges_transaction_code'] + "'"))
d['res_room'] = fix_cha['res_room']
d['res_id'] = fix_cha['res_id']
d['posting_amount'] = fix_cha['fixed_charges_amount'] * fix_cha[
'fixed_charges_quantity']
d['posting_date'] = date
d['post_code_id'] = fixed_charge_code[0]['package_code_id']
d['post_window'] = '1'
d['posting_supplement'] = 'Night Audit Posting'
d['posting_reference'] = 'Night Audit Posting posting fixed chrages'
d['posting_quantity'] = fix_cha['fixed_charges_quantity']
d['emp_id'] = 1
gensql('insert', 'cashiering.billing_post', d)
#run_charges = [ x['fixed_charges']='fixed charges run' for x in run_charges if x['room'] == fix_cha['res_room'] ]
for x in run_charges:
if x['room'] == fix_cha['res_room']:
i = run_charges.index(x)
print("i", i)
run_charges[i]['fixed_charges'] = 'fixed charges run'
#***********************************************Posting Packages***********************************************************
print("packages is pending")
return (json.dumps(
{
'Status': 'Success',
'StatusCode': '200',
'ReturnValue': run_charges,
'ReturnCode': 'RRTS'
},
indent=4))
def Hotel_RM_Post_SELECT_FacilityForecast(request):
d = request.json
list1, faclity, list2, arrival = [], {}, [], []
date1 = datetime.datetime.strptime(d['start_date'], '%Y-%m-%d').date()
date2 = date1 + datetime.timedelta(days=6)
sql_value = json.loads(
dbget(
"select res_arrival,res_depature,res_adults,res_child,res_number_of_rooms from reservation.res_reservation \
where res_arrival between '" +
str(date1) + "' and '" + str(date2) + "' \
order by res_arrival "))
#print(sql_value)
pql_value = json.loads(
dbget(
"select res_arrival,res_depature,res_adults,res_child,res_number_of_rooms from reservation.res_reservation \
where res_depature between '" +
str(date1) + "' and '" + str(date2) + "' \
order by res_depature "))
arrival_count = 0
print(Counter([i['res_arrival'] for i in sql_value]))
def arrival_and_dep_rooms(date1, name):
#print('name',name)
arrival = [
v for k, v in Counter([
i["" + name + ""] for i in sql_value
if i["" + name + ""] == str(date1)
]).items()
]
return (arrival[0] if len(arrival) != 0 else 0)
def depature_rooms(date1, name):
arrival = [
v for k, v in Counter([
i["" + name + ""] for i in pql_value
if i["" + name + ""] == str(date1)
]).items()
]
return (arrival[0] if len(arrival) != 0 else 0)
def inhouse(date1, name):
inhouse_count = sum([
i["" + name + ""] for i in sql_value if datetime.datetime.strptime(
i['res_arrival'], '%Y-%m-%d').date() == date1
])
#or date1 in datetime.datetime.strptime(i['res_depature'], '%Y-%m-%d').date()])
return (inhouse_count)
def stay_over_rooms(date1):
stay_over = json.loads(
dbget("select count(*) from reservation.res_reservation \
where res_guest_status in ('checkin','due out') and res_arrival>= '"
+ str(date1) + "' and res_depature< '" + str(date1) + "'"))
return (stay_over[0]['count'] if len(stay_over) != 0 else 0)
while date1 <= date2:
list2.append({
'days': date1.strftime("%A"),
'date': str(date1),
'values': {
'arrival_rooms':
arrival_and_dep_rooms(date1, name='res_arrival'),
'depature_rooms':
depature_rooms(date1, name='res_depature'),
'adult_inhouse':
inhouse(date1, name='res_adults'),
'stay_over':
stay_over_rooms(date1),
'Total_cleaning':
stay_over_rooms(date1) +
depature_rooms(date1, name='res_depature'),
'child_inhouse':
inhouse(date1, name='res_child')
}
})
date1 = date1 + datetime.timedelta(days=1)
#print(list2)
'''
list1 = [{'date':k,'depature_rooms':v} for k,v in Counter([i['res_depature'] for i in sql_value]).items()]
print(list1)
list1_date = [i['date'] for i in list1]
while date1 <= date2:
if str(date1) not in list1_date:
list1.append({'date':str(date1),'depature_rooms':0})
date1 = date1 + datetime.timedelta(days=1)
final_value = sorted(list1,key = lambda x: x['date'] )
return(json.dumps({'Status': 'Success', 'StatusCode': '200','ReturnValue':final_value ,'ReturnCode':'RRTS'},indent=4))
'''
return (json.dumps(
{
'Status': 'Success',
'StatusCode': '200',
'ReturnValue': list2,
'ReturnCode': 'RRTS'
},
indent=4))
def Hotel_END_OF_Day_POST_Run_guestbalance(request):
res_id_lists, group_ids, final = [], [], []
'''
value1 = json.loads(dbget("SELECT pf_company_profile.pf_account,res_reservation.* FROM reservation.res_reservation \
left join profile.pf_company_profile on pf_company_profile.pf_id = res_reservation.res_block \
where res_reservation.res_guest_status in ('checkin','due out') "))
print(value1)
value2 = json.loads(dbget("select pf_company_profile.pf_account,res_reservation.* from reservation.res_reservation \
left join profile.pf_company_profile on pf_company_profile.pf_id = res_reservation.res_block \
where CURRENT_DATE between res_arrival and res_depature and res_guest_status='Check out' "))
print(value2)
sql_value = value1+value2
print(sql_value)
'''
sql_value = json.loads(
dbget(
"select * from reservation.res_reservation where res_arrival <= '2019-01-24' and res_depature >= '2019-01-24' \
AND res_guest_status not in ('no show','cancel') and res_room_type not in ('PM')"
))
for i in sql_value:
if i['res_block'] is None:
if i['res_id'] in res_id_lists:
pass
else:
res_id_lists.append(i['res_id'])
else:
if i['res_id'] in group_ids:
pass
else:
group_ids.append(i['res_id'])
print("individual profile", res_id_lists)
print("ind+company", group_ids)
for res_id_list in res_id_lists:
amount = json.loads(
dbget(
"select sum(posting_amount) FROM cashiering.billing_post where res_id='"
+ str(res_id_list) + "' \
and post_window in (1)"))
deposit = json.loads(
dbget(
"select sum(res_deposit_amount) from reservation.res_deposit where res_id='"
+ str(res_id_list) + "' "))
payment = json.loads(
dbget(
"select sum(postig_amount)FROM cashiering.posting_payment where res_id='"
+ str(res_id_list) + "' "))
if payment[0]['sum'] is None:
value = deposit[0]['sum'] - amount[0]['sum']
print(value)
final.append({"balance": value, 'res_id': res_id_list})
else:
value = amount[0]['sum'] - (deposit[0]['sum'] + payment[0]['sum'])
print(value)
final.append({"balance": value, 'res_id': res_id_list})
if len(group_ids) != 0:
for group_id in group_ids:
amount = json.loads(
dbget(
"select sum(posting_amount) FROM cashiering.billing_post where res_id='"
+ str(group_id) + "' \
and post_window in (1)"))
payment = json.loads(
dbget(
"select sum(postig_amount)FROM cashiering.posting_payment where res_id='"
+ str(group_id) + "' "))
if payment[0]['sum'] is None:
value = amount[0]['sum']
print(value)
final.append({"balance": value, 'res_id': group_id})
else:
value = amount[0]['sum'] - payment[0]['sum']
print(value)
final.append({"balance": value, 'res_id': group_id})
list1 = [
dict(s, balance=fin['balance']) for s in sql_value for fin in final
if s['res_id'] == fin['res_id']
]
print(list1)
'''
print("sql_value", sql_value)
for s in sql_value:
print("s",s)
for fin in final:
print("fin",fin)
print(s['res_id'],type(s['res_id']),fin['res_id'],type(fin['res_id']))
if s['res_id'] == fin['res_id']:
s['balance'] = fin['balance']
else:
pass
'''
return (json.dumps(
{
'Status': 'Success',
'StatusCode': '200',
'ReturnValue': list1,
'ReturnCode': 'RRTS'
},
indent=4))
def packages_revenue(request):
s = json.loads(dbget("SELECT currency.id,currency.currency,currency.currency_description, \
* FROM packages.package_code \
left join profile.currency on currency.id = package_code.currency_id"))
print(s)
return(json.dumps({"Return": s,"Status": "Success","StatusCode": "200"},indent=4))
def Hotel_END_OF_Day_POST_Run_Additional_procedures(request):
run_additional,list1,list2,no_show_count,list3,list4,dic_pancy,d = [],[],[],[],[],[],[],{}
#********************* night audit date******************
app_datetime = application_date()
date = app_datetime[1]
print(date)
no_show_date = (datetime.datetime.strptime(
date, '%Y-%m-%d').date()) - datetime.timedelta(days=1)
#******************** Reservation No show***********************
no_show = json.loads(
dbget(
"select res_id,res_unique_id,res_guest_status from reservation.res_reservation \
where res_arrival='" + str(no_show_date) + "' \
and res_guest_status = 'arrival'"))
#print(no_show)
no_show_start_time = datetime.datetime.now()
for no_show_report in no_show:
no_show_count.append(no_show_report['res_guest_status'])
no_show_update = dbput(
"update reservation.res_reservation set res_guest_status = 'no show' \
where res_id = '" +
str(no_show_report['res_id']) + "' \
and res_unique_id = '" +
str(no_show_report['res_unique_id']) + "'")
#print(no_show_update)
no_show_end_time = datetime.datetime.now()
run_additional.append({
"Run_additional_procedure":
"Reservation No Show",
"start_time":
str(no_show_start_time.strftime("%H:%M:%S")),
"end_time":
str(no_show_end_time.strftime("%H:%M:%S")),
"Iteration":
len(no_show_count),
"Status":
"Completed"
})
#*********************************Due in ************************************************
due_in_date = (datetime.datetime.strptime(
date, '%Y-%m-%d').date()) + datetime.timedelta(days=1)
due_in = json.loads(
dbget(
"select res_id,res_unique_id,res_guest_status from reservation.res_reservation \
where res_arrival='" + str(due_in_date) + "'"))
due_in_start_time = datetime.datetime.now()
for due_in_report in due_in:
list1.append(due_in_report['res_unique_id'])
due_in_update = dbput(
"update reservation.res_reservation set res_guest_status = 'due in' \
where res_id = '" +
str(due_in_report['res_id']) + "' \
and res_unique_id = '" +
str(due_in_report['res_unique_id']) + "' \
and res_guest_status not in ('cancel,no show','waitilist')"
)
due_in_end_time = datetime.datetime.now()
run_additional.append({
"Run_additional_procedure":
"Reservation Due In",
"start_time":
str(due_in_start_time.strftime("%H:%M:%S")),
"end_time":
str(due_in_end_time.strftime("%H:%M:%S")),
"Iteration":
len(list1),
"Status":
"Completed"
})
#************************************************* Due Out****************************************
due_out_date = (datetime.datetime.strptime(
date, '%Y-%m-%d').date()) + datetime.timedelta(days=1)
due_out = json.loads(
dbget(
"select res_id,res_unique_id,res_guest_status from reservation.res_reservation \
where res_depature='" + str(due_out_date) +
"'"))
due_out_start_time = datetime.datetime.now()
for due_out_report in due_out:
list2.append(due_out_report['res_unique_id'])
due_out_update = dbput(
"update reservation.res_reservation set res_guest_status = 'due out' \
where res_id = '" +
str(due_out_report['res_id']) + "' \
and res_unique_id = '" +
str(due_out_report['res_unique_id']) + "' \
and res_guest_status not in ('cancel,no show','waitilist','arrival')"
)
print(list2)
due_out_end_time = datetime.datetime.now()
run_additional.append({
"Run_additional_procedure":
"Reservation Due Out",
"start_time":
str(due_out_start_time.strftime("%H:%M:%S")),
"end_time":
due_out_end_time.strftime("%H:%M:%S"),
"Iteration":
len(list2),
"Status":
"Completed"
})
#*******************************arrival ****************************************************************
#arrival_date = (datetime.datetime.strptime(date[0]['roll_business_date'],'%Y-%m-%d').date()) + datetime.timedelta(days=1)
arrivals = json.loads(
dbget(
"select res_id,res_unique_id,res_guest_status from reservation.res_reservation \
where res_arrival='" + str(date) + "'"))
arrival_start_time = datetime.datetime.now()
for arrivals_report in arrivals:
list3.append(arrivals_report['res_unique_id'])
arrivals_report_update = dbput(
"update reservation.res_reservation set res_guest_status = 'arrival' \
where res_id = '" +
str(arrivals_report['res_id']) + "' \
and res_unique_id = '" +
str(arrivals_report['res_unique_id']) + "' \
and res_guest_status not in ('cancel,no show','waitlist') "
)
#print(list3)
arrival_end_time = datetime.datetime.now()
run_additional.append({
"Run_additional_procedure":
"Reservation Arrival",
"start_time":
str(arrival_start_time.strftime("%H:%M:%S")),
"end_time":
str(arrival_end_time.strftime("%H:%M:%S")),
"Iteration":
len(list3),
"Status":
"Completed"
})
#**********************************In-house guest**********************************************
in_house = json.loads(
dbget(
" select * from reservation.res_reservation where res_guest_status='checkin' or res_guest_status='due out'"
))
in_house_start_time = datetime.datetime.now()
for in_house_report in in_house:
list4.append(in_house_report['res_unique_id'])
in_house_end_time = datetime.datetime.now()
run_additional.append({
"Run_additional_procedure":
"In-House Guest",
"start_time":
str(in_house_start_time.strftime("%H:%M:%S")),
"end_time":
str(in_house_end_time.strftime("%H:%M:%S")),
"Iteration":
len(list4),
"Status":
"Completed"
})
#***************************************Room Discrepancy********************************
'''
room_discrepancy = json.loads(dbget("select * from room_management.rm_room_list \
where rm_fo_status = 'occupied' and rm_hk_status = 'vacant'"))
room_disc_start_time = datetime.datetime.now()
for discrepancy in room_discrepancy:
dic_pancy.append(discrepancy['rm_room'])
d['rm_room'] = discrepancy['rm_room']
d['rm_room_discrepancy'] = 'Skip'
skip_person = gensql('insert','room_management.rm_room_discrepancy',d)
sleep_details = json.loads(dbget("select * from room_management.rm_room_list \
where rm_fo_status = 'vacant' and rm_hk_status = 'occupied'"))
for sleep in sleep_details:
dic_pancy.append(discrepancy['rm_room'])
d['rm_room'] = sleep['rm_room']
d['rm_room_discrepancy'] = 'Sleep'
sleep_person = gensql('insert','room_management.rm_room_discrepancy',d)
person_details = json.loads(dbget("select * from room_management.rm_room_list"))
preson_differ = list(filter(lambda x:x['rm_hk_person']!=x['rm_fo_person'],person_details))
for preson in preson_differ:
d['rm_room'] = preson['rm_room']
d['rm_room_discrepancy'] = 'Person'
person_differ_details = gensql('insert','room_management.rm_room_discrepancy',d)
room_disc_end_time = datetime.datetime.now()
run_additional.append({"Run_additional_procedure":"Room Discrepancies",
"start_time":str(room_disc_start_time.strftime("%H:%M:%S")),"end_time":str(room_disc_end_time.strftime("%H:%M:%S")),"Iteration": len(dic_pancy),"Status":"Completed"})
'''
def serialize(obj):
if isinstance(obj, datetime.date):
return obj.__str__()
if isinstance(obj, datetime.time):
return obj.__str__()
return (json.dumps(
{
'Status': 'Success',
'StatusCode': '200',
'ReturnValue': run_additional,
'ReturnCode': 'RRTS'
},
indent=4,
default=serialize))
def calculatetotalcharges(request):
#try:
if request.method == 'GET':
d = {}
tfn = '+'+request.args['tfn_num']
customer_adult = request.args['adult']
customer_child = request.args['child']
customer_arrival_date = request.args['arrival_date']
customer_depature_date = request.args['depature_date']
customer_room_type = request.args["room_type"] # ROOM_ID
if request.method == 'POST':
tfn = request.json['tfn_num']
customer_adult = request.json["adult"]
customer_child = request.json["child"]
customer_arrival_date = request.json["arrival_date"]
customer_depature_date = request.json["depature_date"]
customer_room_type = request.json["room_type"] # ROOM_ID
dividen_list = []
last_list = []
sumval = 0
datelist_rate = []
datelist_amount = {}
rate_plan_list = []
b_id = json.loads(dbget("select id from ivr_dialed_number where dialed_number='"+tfn+"' "))
print(b_id[0]['id'])
bi_id = json.loads(dbget("select business_id from ivr_hotel_list where id='"+str(b_id[0]['id'])+"' "))
print(bi_id[0]['business_id'],type(bi_id[0]['business_id']))
#customer_arrival_date = request.json["arrival_date"]
#customer_depature_date = request.json["depature_date"]
#customer_room_type = request.json["room_type"] # ROOM_ID
print(customer_room_type,type(customer_room_type))
# customer_room_type = customer_room_type.title()
# print("roomtype",customer_room_type)
#customer_adult = request.json["adult"]
#customer_child = request.json["child"]
print("adults",customer_adult,type(customer_adult))
d,e,d1,d2 = {},[],{},{}
print(customer_arrival_date,customer_depature_date)
today_date = datetime.datetime.utcnow().date()
year = str(today_date.year)
if int(customer_arrival_date[0:2]) == today_date.month :
if int(customer_arrival_date[2:]) < today_date.day :
year = str(today_date.year+1)
print("year",year,type(year))
elif int(customer_arrival_date[0:2]) < today_date.month :
year = str(today_date.year+1)
customer_arrival_date = year+'-'+customer_arrival_date[0:2]+'-'+customer_arrival_date[2:]
if int(customer_depature_date[0:2]) == today_date.month :
if int(customer_depature_date[2:]) < today_date.day :
year = str(today_date.year+1)
print("year",year,type(year))
elif int(customer_depature_date[0:2]) < today_date.month :
year = str(today_date.year+1)
customer_depature_date = year+'-'+customer_depature_date[0:2]+'-'+customer_depature_date[2:]
print("arrival",customer_arrival_date,"depature",customer_depature_date,"roomid",customer_room_type,"businessid",bi_id[0]['business_id']) # CONFIGRATION
sql = json.loads(dbget("select max_extra_bed.extrabed,extranet_availableroom.extra_adult_rate,extranet_availableroom.rate_plan_id,extranet_availableroom.room_date,extranet_availableroom.room_rate,configration.max_adults \
from configration \
join extranet_availableroom on extranet_availableroom.room_id = configration.room_id \
join max_extra_bed on max_extra_bed.extrabed_id = configration.maximum_extrabed_id \
where configration.room_id = '"+str(customer_room_type)+"' and configration.business_id='"+bi_id[0]['business_id']+"'and extranet_availableroom.room_date between '"+str(customer_arrival_date)+"' and '"+str(customer_depature_date)+"'"))
#print("res",result)
#available_rate = json.loads(dbget("select room_id,room_rate,room_date,rate_plan_id from extranet_availableroom where room_date between '"+str(customer_arrival_date)+"' and '"+str(customer_depature_date)+"' and room_id='"+str(customer_room_type)+"'"))
#print(available_rate)
s = 0
total_adult = int(customer_adult)
max_adult = int(sql[0]['max_adults'])
extra_bed = int(sql[0]['extrabed'])
extra_adult_rate = sql[0]['extra_adult_rate']
rooms_rate = int(sql[0]['room_rate'])
plan_rate = int(sql[0]['rate_plan_id'])
print("plan_rate",plan_rate)
total_beds = max_adult + extra_bed
total_rooms = total_adult / total_beds
total_rooms_count = math.ceil(total_rooms)
print(total_rooms_count)
sumva = 0
for i in sql:
arrival_date = datetime.datetime.strptime(customer_arrival_date, '%Y-%m-%d')
depature_date = datetime.datetime.strptime(customer_depature_date, '%Y-%m-%d')
conf_date = datetime.datetime.strptime(i['room_date'], '%Y-%m-%d')
print("conf_date",conf_date)
deltadates = depature_date - arrival_date
for x in range(deltadates.days + 1):
#print(arrival_date + datetime.timedelta(i))
datebetween = arrival_date + datetime.timedelta(x)
if datebetween == conf_date and plan_rate == int(i['rate_plan_id']) :
print(i["extra_adult_rate"],i['room_rate'],i['room_rate'])
r1 = max_adult * total_rooms_count
extra_price = (int(customer_adult) - r1) * int(i["extra_adult_rate"])
price = total_rooms_count * int(i['room_rate'])
total = price + extra_price
sumva += total
##price1 = result1 * int(i['room_rate'])
#price2 = (total_adult - result1) * (int(i["extra_adult_rate"])+int(i['room_rate']))
#datebetween.strftime('%d %B')
datelist_rate.append({"day":datebetween.strftime('%Y-%m-%d'),"total":total})
#print(datelist_rate)
print(datelist_rate)
print("sumva",sumva)
def myconverter(o):
if isinstance(o, datetime.datetime):
return o.__str__()
return(json.dumps({"ServiceMessage":"Success","Total_Amount":sumva,"date_amount":datelist_rate},indent=2,default=myconverter))
def HOTEL_CAH_POST_INSERT_UPDATEGUESTBILLING(request):
to_amount = {}
result = request.json
#print(result)
d = result['bills']
#print(d,type(d))
res_id = result['Res_id']
res_room = result['res_room']
Total_amount = result['Total_amount']
Total_posting = result['Total_posting']
print(res_id, res_room, Total_amount, type(res_id), type(res_room),
type(Total_amount))
#print(d,type(d),len(d))
app_datetime = application_date()
Posting_date = app_datetime[1]
fo_count = json.loads(dbget('select * from cashiering.folio_number'))
print(fo_count[0]['folio_no'], type(fo_count[0]['folio_no']))
dbput("update cashiering.folio_number set folio_no = " +
str(fo_count[0]['folio_no'] + 1) + " ")
folio = {}
folio['res_id'], folio['total_posting'], folio['total_amount'], folio[
'folio_no'] = res_id, Total_posting, Total_amount, fo_count[0][
'folio_no'] + 1
gensql('insert', 'cashiering.reservation_folio', folio)
for i in range(len(d)):
total_revenue_count = json.loads(
dbget("select count(*) from cashiering.billing_total_revenue \
where res_id = '" + str(res_id) +
"'"))
print(total_revenue_count)
e = {
k: v
for k, v in d[i].items() if k not in ('editFlag', 'Post_des')
}
#print(i)
#print(d,type(d),len(d))
e['Posting_date'] = Posting_date
e['Res_id'] = res_id
e['res_room'] = res_room
e['folio_no'] = fo_count[0]['folio_no'] + 1
#print(d)
gensql('insert', 'cashiering.billing_post', e)
print(total_revenue_count[0]['count'])
print(e['Post_window'], type(e['Post_window']))
if total_revenue_count[0]['count'] != 0:
if e['Post_window'] == '1':
revenue_count = dbput(
"update cashiering.billing_total_revenue set total_revenue = total_revenue +'"
+ str(e['Posting_amount']) + "' \
where res_id = '" + str(res_id) +
"' and res_room = '" + str(res_room) + "'")
print(revenue_count)
else:
pass
else:
if e['Post_window'] == '1':
to_amount['res_id'] = res_id
to_amount['res_room'] = res_room
to_amount['total_revenue'] = e['Posting_amount']
revenue_repott = gensql('insert',
'cashiering.billing_total_revenue',
to_amount)
print(revenue_repott)
else:
pass
#dbput("update reservation.res_reservation set res_guest_balance = res_guest_balance + "+str(int(Total_amount))+" \
# where res_id='"+res_id+"' and res_room='"+res_room+"' ")
Revenue_date = app_datetime[1]
result = json.loads(
dbget(
"select log_link_id from cashiering.log_link where link_id='1' "))
log_id = result[0]['log_link_id'] + 1
print(log_id)
dbput("update cashiering.log_link set log_link_id=" + str(log_id) +
" where link_id='1'")
s = {}
s['Posting_date'] = Posting_date
s['Revenue_date'] = Revenue_date
s['User_role'] = "Admin"
s['User_name'] = "Admin"
s['Res_id'] = res_id
s['Posting_action'] = "General posting"
s['Posting_reason'] = "Payment posted for" + " " + res_id
s['Posting_description'] = "Payment posted "
s['log_link_id'] = log_id
gensql('insert', 'cashiering.posting_history_log', s)
gensql('insert', 'cashiering.posting_original_history_log', s)
return (json.dumps(
{
"Return": "Record Inserted Successfully",
"ReturnCode": "RIS",
"Status": "Success",
"StatusCode": "200"
},
indent=4))
def HOTEL_AR_POST_UPDATE_AdjustBillingpost(request):
d = request.json
print(d)
app_datetime = application_date()
Posting_date = app_datetime[1]
z = {}
s = {}
amount = json.loads(
dbget(
"select posting_amount from account_receivable.account_billing_post \
where account_bill='" + str(d['account_bill']) +
"' and account_no = '" + str(d['account_no']) + "'"))
a = {
k: v
for k, v in d.items() if v != ''
if k not in ('account_bill', 'invoice_no')
}
print(a)
if a['adjust_type'] == 1:
z['posting_amount'] = amount[0]['posting_amount'] + a['adjust_amount']
elif a['adjust_type'] == 2:
print("else")
z['posting_amount'] = amount[0]['posting_amount'] * a[
'adjust_amount'] / 100
e = {
k: v
for k, v in d.items() if k != '' if k in ('account_bill', 'invoice_no')
}
print(e)
print("adjust", z)
gensql('update', 'account_receivable.account_billing_post', z, e)
app_datetime = application_date()
s['invoice_id'] = e['invoice_no']
s['acc_action'] = a['reason_text']
s['acc_posting_reason'] = a['reason_code']
s['acc_post_date'] = Posting_date
s['acc_posting_time'] = app_datetime[0]
s['acc_user_id'] = d['emp_id']
history = gensql('insert', 'account_receivable.account_posting_history', s)
sql = json.loads(
dbget(
"select sum(posting_amount) from account_receivable.account_billing_post where account_no = '"
+ str(d['account_no']) + "' \
and invoice_no = '" +
d['invoice_no'] + "'"))
psql = dbput("update account_receivable.accout_inivoice \
set invoice_amount = '" + str(sql[0]['sum']) +
"', open_amount = '" + str(sql[0]['sum']) + "' \
where invoice_no='" + str(d['invoice_no']) + "'")
acc = json.loads(
dbget("select open_amount from account_receivable.accout_inivoice \
where account_number = '" + str(d['account_no']) +
"' and acc_invoice_satus not in ('Compress')"))
print(acc)
sumof = sum(item['open_amount'] for item in acc
if item['open_amount'] is not None)
acc_bala = dbput(
"update account_receivable.account_setup set account_balance = '" +
str(sumof) + "' \
where account_number = '" + str(d['account_no']) + "'")
return (json.dumps(
{
"Return": "Record Updated Successfully",
"ReturnCode": "RUS",
"Status": "Success",
"StatusCode": "200"
},
indent=4))
def ratesandavailability(request):
req = request.json
#print(req)
a = {k: v for k, v in req.items() if v == ''}
#print(len(a))
if len(a) != 0:
from_date = datetime.datetime.utcnow().date()
to_date = from_date + datetime.timedelta(days=14)
else:
from_date = datetime.datetime.strptime(req['from_date'],
'%Y-%m-%d').date()
to_date = datetime.datetime.strptime(req['to_date'], '%Y-%m-%d').date()
res_from_date = from_date
res_to_date = to_date
res = json.loads(
dbget(
"SELECT extranet_availableroom.business_id,configration.room_id, configration.room_name ,\
room_date, \
room_rate, room_open, s_no, rate_plan.rate_plan_id,rate_plan.rate_plan, \
min_stay, max_stay, close_arrival, close_departure, house_close, extra_adult_rate \
FROM public.extranet_availableroom join configration on extranet_availableroom.room_id = configration.room_id\
join rate_plan on extranet_availableroom.rate_plan_id = rate_plan.rate_plan_id \
where configration.business_id='" +
req['business_id'] + "' and room_date \
between '" + str(from_date) + "' and '" + str(to_date) +
"' \
order by room_id,rate_plan_id,room_date"))
#print(res,type(res),len(res))
room_id, colle_rooms, rate_plan_id = [], [], []
count_type, count_plan = 0, 0
for i in res:
#print(i,type(i),res.index(i))
#l={k:v for k,v in i.items() if k in('business_id','room_id','room_name','room_date','rate_plan','rate_plan_id','room_open','min_stay','max_stay','room_rate','extra_adult_rate','booked_count',
# 'close_arrival','close_departure','house_close') }
#print('lll',i['room_id'],i['rate_plan_id'])
if i['room_id'] in room_id:
pass
else:
rate_plan = []
rate_plan_id = []
count_plan = 0
count_type = count_type + 1
room_id.append(i['room_id'])
print("room_iddddd", room_id)
if i['rate_plan_id'] in rate_plan_id:
pass
else:
count_plan = count_plan + 1
rate_plan_id.append(i['rate_plan_id'])
print("plan_iddddd", rate_plan_id)
k = {}
k['room_plan' + str(count_plan)] = i
j = {}
j['room_type' + str(count_type)] = k
colle_rooms.append(j)
#print(i)
#print(room_name)
#print("colle_rooms",colle_rooms,len(colle_rooms))
r_key, p_key = [], []
total, room_total, plan_total, plan_total01 = [], [], [], []
room_to_sell = []
for i in colle_rooms:
#print("i",i,colle_rooms.index(i))
room_k = [k for k, v in i.items()]
rooms = i["" + room_k[0] + ""]
#print("rrrrrrrrrrrrrrrrrrrrrooms ",rooms)
plan_k = [k for k, v in rooms.items()]
plans = rooms["" + plan_k[0] + ""]
#print("ppppppppppppppppppppplans ",plans)
if room_k[0] not in r_key:
r_key.append(room_k[0])
plan_total = []
room_to_sell = json.loads(
dbget(
"select room_to_sell.*, configration.room_name as con_room_name from room_to_sell \
join configration on room_to_sell.room_id = configration.room_id where room_date between \
'" + str(from_date) + "' and '" + str(to_date) +
"' and \
room_to_sell.business_id='" + req['business_id'] +
"' and \
room_to_sell.room_id='" + str(plans['room_id']) +
"' order by room_date "))
total.append({
"" + room_k[0] + "": {
'room_name': plans['room_name'],
'room_to_sell': room_to_sell,
'plans': plan_total
}
})
else:
pass
plan_total.append(rooms)
#print(total)
Date = []
while from_date <= to_date:
Date.append(str(from_date))
from_date += datetime.timedelta(days=1)
#print(Date)
return (json.dumps(
{
"ServiceStatus": "Success",
"ServiceMessage": "Success",
"Result": total,
"from_date": str(res_from_date),
"to_date": str(res_to_date),
"Date": Date
},
indent=2))
