def ROS_post():
recv = str(request.data, encoding="utf-8") # 获取ROS客户端post的数据
recv_data = json.loads(recv)
# 取出要存入car表的值
car_key = {
"car_id", "current_position_x", "current_position_y",
"current_velocity", "tire_pressure_left_front",
"tire_pressure_right_front", "tire_pressure_left_behind",
"tire_pressure_right_behind", "camera_status", "lidar_status",
"ibeo_status", "route"
}
dict_data = {
key: value
for key, value in recv_data.items() if key in car_key
}
# 取出要存入order表的值
order_key = {
"car_id", "is_car_arrive_start", "is_car_reach_target",
"is_car_start_to_start_point"
}
order_data = {
key: value
for key, value in recv_data.items() if key in order_key
}
# print(order_data)
# print(dict_data)
# 判断是否有这辆车的信息，若有则更新这car在数据库中的信息，
if database.read_sql("car_id", dict_data["car_id"], "taxitest_car") == None:
return "数据库中没有此车信息，请注册"
else:
# 更新这car在数据库中的信息
if "car_status" in recv_data.keys():
with car_lock: #获取car锁
database.update_sql("car_id", recv_data["car_id"], "taxitest_car",
recv_data)
return "跟新车辆状态成功"
else:
with car_lock: #获取car锁
database.update_sql("car_id", dict_data["car_id"], "taxitest_car",
dict_data)
# 查询当前车辆状态
car_data = database.read_sql("car_id", dict_data["car_id"], "taxitest_car")
if car_data["car_status"] == "idle":
return "当前没有订单"
elif car_data["car_status"] == "proc":
with order_lock: # 获取order锁
# 跟新order表数据
database.update_sql("car_id", order_data["car_id"], "taxitest_order",
order_data)
else:
return "Error occurs" # 车辆既没有在空闲状态，也不在订单状态
# 得到客户端需要返回的数据
orderid = database.find_sql("car_id", dict_data["car_id"], "order_status",
"proc", "taxitest_order", "order_id")
# print(orderid)
orderid0 = orderid[0][0]
order = database.read_sql("order_id", orderid0, "taxitest_order")
order["order_creation_time"] = str(order["order_creation_time"])
order["order_end_time"] = str(order["order_end_time"])
return json.dumps(order)
def client_login():
client = client_login_data(request.form) # 获取客户端post的数据
if client.login_or_create == True:
dict_account = database.read_sql(db, "account", client.account,
"ACCOUNT")
# 查询数据库中有无此账号
if dict_account == None:
return "无此账户"
# 检查数据库中账号密码是否正确
if dict_account["password"] == client.password:
return "Login in success"
else:
return "账号或密码不正确"
elif client.login_or_create == False:
# 检查数据库中是否有账户
print(client.account)
dict_account = database.read_sql(db, "account", client.account,
"ACCOUNT")
if dict_account != None:
return "已存在此账号，请登录"
else:
print(client.data2dist())
print(dict_account)
a = database.write_sql(db, "ACCOUNT",
client.data2dist()) # 写client进入数据库
if a == True:
return "Create account success"
else:
return "error occures"
else:
print(client.login_or_create + "?")
return "Error occurs"
def insert_data(df):
test_df_total = read_sql("select * from film_session_result")
test_df = read_sql(
"select * from film_session_result where fetch_date = '%s'" % today)
#今天无数值或初始值为空
if len(test_df) == 0 or len(test_df_total) == 0:
to_sql(df, "film_session_result")
def client_login():
recv = str(request.data, encoding="utf-8") # 获取客户端post的数据
dict_data = json.loads(recv)
# print(type(dict_data))
# print(dict_data["login_or_create"])
if dict_data["login_or_create"] == True:
dict_account = database.read_sql("phone_number",
dict_data["phone_number"], "taxitest_client")
# 查询数据库中有无此账号
if dict_account == None:
return "无此账户"
# 检查数据库中账号密码是否正确
elif dict_account["password"] == dict_data["password"]:
# 检查是否该账户已在别的设备登录
if dict_account["is_client_login"] == True:
return "此账号已在别的设备登录"
# 登陆成功时将数据库中对应该账号的标志位is_client_login置为True
else:
dict_account["is_client_login"] = True
with client_lock: # 获取client锁
database.update_sql("phone_number", dict_account["phone_number"],
"taxitest_client", dict_account)
return json.dumps(dict_account) # 账号登陆成功，返回账号信息
elif dict_account["password"] != dict_data["password"]:
return "账号或密码不正确"
elif dict_data["login_or_create"] == False:
# 检查数据库中是否有账户
# print(client.account)
dict_account = database.read_sql("phone_number",
dict_data["phone_number"], "taxitest_client")
if dict_account != None:
return "已存在此账号，请登录"
else:
if len(dict_data["username"]) == 0:
return "用户名不能为空"
else:
flag = True
for s in dict_data["username"]:
if (s >= u'\u4e00' and s<=u'\u9fff') or ((s >= u'\u0041' and s <= u'\u005a')
or (s >= u'\u0061' and s <= u'\u007a')) or s.isdigit():
flag = True
else:
flag = False
break
if flag == True:
dict_data["is_client_login"] = True
a = database.write_sql("taxitest_client", dict_data) # 写client进入数据库
if a == True:
dict_account = database.read_sql("phone_number",
dict_data["phone_number"], "taxitest_client")
return json.dumps(dict_account) # 账号注册并登陆成功，返回账号信息
else:
return "error occures"
else:
return "用户名输入错误，请重新输入"
else:
# print(client.login_or_create+"?")
return "Error occurs"
def ROS_register():
recv = str(request.data, encoding="utf-8")
print(recv)
recv_data = json.loads(recv)
# 取出要存入car表的值
car_key = {
"car_id", "current_position_x", "current_position_y",
"current_velocity", "tire_pressure_left_front",
"tire_pressure_right_front", "tire_pressure_left_behind",
"tire_pressure_right_behind", "camera_status", "lidar_status",
"ibeo_status", "route"
}
dict_data = {
key: value
for key, value in recv_data.items() if key in car_key
}
# ROS=data_define.ROS_data(dict_data)
# 查询数据库中是否有ROS.car_id
database_ROS = database.read_sql("car_id", dict_data["car_id"], "taxitest_car")
if database_ROS != None:
return "数据库中已有此车信息，请更新"
else:
with car_lock:
resulte = database.write_sql("taxitest_car", dict_data)
if resulte:
return "车辆注册成功"
else:
return "Error occurs！"
def send_mail(file_name):
#邮箱列表，需要处理成发送需要的字符串格式
mail_addr_sql = "select mail_addr from mail_addr"
mail_addr_list = read_sql(mail_addr_sql)["mail_addr"].tolist()
mail_addr_str = ""
for each_addr in mail_addr_list:
mail_addr_str += each_addr + ","
#登录邮箱
smtp, sender = mail_login()
receiver = mail_addr_str.rstrip(",")
#编辑邮件内容
msg = MIMEMultipart()
msg["Subject"] = file_name.rstrip(".xlsx")
msg["From"] = formataddr(["信息数据分析研究中心", sender])
msg["To"] = receiver
#添加主题和附件
text = MIMEText(file_name.rstrip(".xlsx"))
msg.attach(text)
attach_xlsx(file_name, msg)
#执行邮件发送
try:
smtp.sendmail(sender, receiver.split(","), msg.as_string())
smtp.quit()
logger.info("send mail success")
except smtplib.SMTPException as e:
logger.info("send mail failed,causing details : %s" % e)
#发送后清理文件
remove(file_name)
def client_post():
recv = str(request.data, encoding="utf-8") # 获取客户端post的数据
dict_data = json.loads(recv)
# 更新order数据表
with order_lock:
database.update_sql("order_id", dict_data["order_id"], "taxitest_order",
dict_data)
order_data = database.read_sql("order_id", dict_data["order_id"], "taxitest_order")
# TODO: 判断车辆是否到达接客点，若到达则给客户端推送到达信息 设置account为客户端的手机号
# push=xinge.XingeApp("a0381c28541e4","570812dd57c4d94451b1e36766db6697")
# if order_data["is_car_arrive_start"]==Ture and order_data["is_car_reach_target"]==False :
# msg=xinge.MessageIOS()
# msg.alert="json"
# msg.expireTime = 3600
# msg.custom={}
# account = order_data["client_id"]
# ret=push.PushSingleAccount(0,account,msg,1)
# elif order_data["is_car_reach_target"]==True:
# msg=xinge.MessageIOS()
# msg.alert="json"
# account = order_data["client_id"]
# ret=push.PushSingleAccount(0,account,msg,1)
if order_data["order_status"] == "comp": # 当乘客下车后，设置car_status为idle
with car_lock:
car_data = {}
car_data["car_id"] = order_data["car_id"]
car_data["car_status"] = "idle"
database.update_sql("car_id", car_data["car_id"], "taxitest_car", car_data)
with order_lock:
order_data["order_end_time"] = time.strftime(
"%Y-%m-%d %H:%M:%S", time.localtime())
database.update_sql("order_id", order_data["order_id"], "taxitest_order",
order_data)
order_data["order_creation_time"] = str(order_data["order_creation_time"])
order_data["order_end_time"] = str(order_data["order_end_time"])
# 若需要传送回car的数据,则把car的dict和进order_data
data = {}
car = database.read_sql("car_id", order_data["car_id"], "taxitest_car")
for k, v in order_data.items():
data[k] = v
for k, v in car.items():
data[k] = v
return json.dumps(data)
def compete_data():
df_compete_list = []
film_date_list = date_rules()
for each_date in film_date_list:
df_compete_cinema_film = read_sql(
"select compete_cinema_code,film_name,bo,session,people,occupancy \
from compete_cinema_film where film_date = '%s' and fetch_date = '%s'"
% (each_date, today))
df_compete_list.append(df_compete_cinema_film)
logger.info("compete data process complete")
return df_compete_list
def client_check():
recv = str(request.data, encoding="utf-8") # 获取客户端post的数据
dict_data = json.loads(recv)
# print(dict_data)
# 检查是否在排队，若在排队，则返回wait_client的信息。
wait_data=database.read_sql("phone_number",dict_data["phone_number"],"wait_client")
if wait_data != None:
wait_data.pop("creation_time")
return json.dumps(wait_data)
# 查询是否有订单正在进行
order_id = database.find_sql("phone_number", dict_data["phone_number"],
"order_status", "proc", "taxitest_order", "order_id")
# print(order_id)
if len(order_id) != 0: # 如果存在未完成订单，则读取订单数据发送回去
order_data = database.read_sql("order_id", order_id[0][0], "taxitest_order")
# json格式没法存储datetime类型的数据，要进行转化
# print(order_data["order_creation_time"])
order_data["order_creation_time"] = str(
order_data["order_creation_time"])
order_data["order_end_time"] = str(order_data["order_end_time"])
return json.dumps(order_data)
else:
return "当前没有订单"
def df_filter(df, field_dict, table_name):
compete_cinema_list = read_sql(
"select compete_cinema_code from compete_cinema_info"
)["compete_cinema_code"].tolist()
df = df[df["影院编码"].isin(compete_cinema_list)]
df = df[list(field_dict.keys())]
df.rename(columns=field_dict, inplace=True)
if table_name == "compete_cinema_film":
df["avg_price"].replace({"-": 0.00}, inplace=True)
df["occupancy"].replace({"-": 0.00}, inplace=True)
df["bo"] = df["bo"] * 10000
df["occupancy"] = df["occupancy"] * 100
df["film_date"] = pd.to_datetime(df["film_date"])
df["film_date"] = df["film_date"].apply(lambda x: x.strftime("%Y-%m-%d"))
df["fetch_date"] = pd.to_datetime(df["fetch_date"])
df["fetch_date"] = df["fetch_date"].apply(lambda x: x.strftime("%Y-%m-%d"))
to_sql(df, table_name)
logger.info("import data successfully inserted into database")
def client_logout():
recv = str(request.data, encoding="utf-8") # 获取客户端post的数据
dict_data = json.loads(recv)
dict_account = database.read_sql("phone_number",
dict_data["phone_number"], "taxitest_client")
order_id = database.find_sql("phone_number", dict_data["phone_number"], "order_status",
"proc", "taxitest_order", "order_id")
# 检查该账号是否存在
if dict_account == None:
return "无此账户"
# 检查盖该账户是否处于登录状态
elif dict_account["is_client_login"] == False:
return "该账号并未登录"
# 检查该账户是否有订单正在进行
elif len(order_id) != 0:
return "当前有订单正在进行，无法退出登录"
# 退出登录，设置标志位is_client_login为False
else:
dict_account["is_client_login"] = False
with client_lock: # 获取client锁
database.update_sql("phone_number", dict_account["phone_number"],
"taxitest_client", dict_account)
return "当前账号已成功退出"
dict_data = json.loads(recv)
# 判断是否要取消排队等候
if dict_data.has_key('queue_cancel'):
if dict_data['queue_cancel']==True:
if database.delete_line("phone_number",dict_data["phone_number"],"wait_client")：
return "cancel queue success"
else:
return "cancel queue fail"
# 检查是否在排队列表中，
# 如果是，则判断排队中的位置，
# 若为第一位，则判断有没有车辆空闲出来
# 若不为第一位，返回当前排队的还有多少人
rank=database.find_row("phone_number",dict_data["phone_number"],"wait_client")
if rank != "Error":
if rank == 1:
car = database.read_sql("car_status", "idle", "taxitest_car")
if car == None:
return "当前有1人在排队"
else:
# 创建新的一个订单
order_data = {}
for item in dict_data.keys():
order_data[item] = dict_data[item]
# 生成一个19位订单编号
order_data["order_id"] = createid.get_id(dict_data["phone_number"])
order_data["order_status"] = "proc"
order_data["car_id"] = car["car_id"]
# 更改车辆的状态为proc
car["car_status"] = "proc"
with car_lock:
database.update_sql("car_id", car["car_id"], "taxitest_car", car)
def history_data():
df_history_data = read_sql(
"select film_date,fetch_date,cinema_code,cinema_name,compete_cinema_code,compete_cinema_name,film_name,bo,\
people,session,occupancy,avg_price,cinema_session,session_percent,avg_session from film_session_result"
)
return df_history_data
def date_rules():
date_rules = read_sql(
"select film_date from date_rules where fetch_date = '%s'" %
today)["film_date"].tolist()
return date_rules
def get_avg_session():
df_avg_session = read_sql(
"select cinema_code,avg_session from avg_session")
return df_avg_session
def combine_data(df_table_list, df_compete_list, film_name):
df_cinema_total = pd.DataFrame(columns = ["cinema_code","cinema_name","compete_cinema_code","compete_cinema_name",\
"film_name","bo","people","session","occupancy","cinema_session"])
#直营影院列表
df_jycinema = read_sql(
"select cinema_code,cinema_name,vista_cinema_name from jycinema_info where op_status = 1"
)
df_compete_cinema = read_sql(
"select jycinema_code,jycinema_name,compete_cinema_code,compete_cinema_name from compete_cinema_info"
)
jycinema_list = df_jycinema["cinema_code"].tolist()
#利用film_date_list,df_table_list,df_compete_list对应性进行合并
#注意，此时film_name只有1个，若为film_list则要稍微调整一下逻辑
df_avg_session = get_avg_session()
film_date_list = date_rules()
for i in range(len(film_date_list)):
df_jycinema_combine = pd.merge(left=df_jycinema,
right=df_table_list[i][0],
left_on="vista_cinema_name",
right_on="影院",
how="left")
df_jycinema_combine = pd.merge(left=df_jycinema_combine,
right=df_table_list[i][1],
left_on="vista_cinema_name",
right_on="影院",
how="left")
df_jycinema_combine["影片"] = film_name
df_jycinema_combine["occupancy"] = np.round(
df_jycinema_combine["人数"] / df_jycinema_combine["总座位数"], 4) * 100
df_jycinema_combine.drop(
columns=["影院_x", "影院_y", "总座位数", "vista_cinema_name"],
axis=1,
inplace=True)
df_jycinema_combine.rename(columns = {"影片":"film_name","场次":"session","人数":"people","票房":"bo","影院总场次":"cinema_session"},\
inplace = True)
df_jycinema_combine = pd.merge(left=df_jycinema_combine,
right=df_avg_session,
on="cinema_code",
how="left")
df_compete_cinema_combine = pd.merge(left=df_compete_cinema,
right=df_compete_list[i],
on="compete_cinema_code",
how="left")
df_compete_cinema_combine["film_name"] = film_name
#把竞对影院总场次匹配上
df_compete_cinema_session = read_sql(
"select compete_cinema_code,session \
from compete_cinema_session where film_date = '%s' and fetch_date = '%s'"
% (film_date_list[i], today))
df_compete_cinema_session.rename(columns={"session": "cinema_session"},
inplace=True)
df_compete_cinema_combine = pd.merge(left = df_compete_cinema_combine,right = df_compete_cinema_session,\
on = "compete_cinema_code",how = "left")
df_compete_cinema_combine = pd.merge(left = df_compete_cinema_combine,right = df_avg_session,left_on = "compete_cinema_code",right_on = \
"cinema_code",how = "left")
df_compete_cinema_combine.drop(columns=["cinema_code"],
axis=1,
inplace=True)
df_compete_cinema_combine.rename(columns={
"jycinema_code": "cinema_code",
"jycinema_name": "cinema_name"
},
inplace=True)
#拼接自营及竞对
for each_code in jycinema_list:
each_df_jycinema = df_jycinema_combine[
df_jycinema_combine["cinema_code"] == each_code]
each_df_compete_cinema = df_compete_cinema_combine[
df_compete_cinema_combine["cinema_code"] == each_code]
each_cinema_total = pd.concat(
[each_df_jycinema, each_df_compete_cinema], ignore_index=True)
df_cinema_total = pd.concat([df_cinema_total, each_cinema_total],
ignore_index=True)
df_cinema_total["fetch_date"] = today
df_cinema_total["film_date"] = film_date_list[i]
#处理字段
for each_field in [
"bo", "people", "session", "cinema_session", "occupancy"
]:
df_cinema_total[each_field].replace("", 0, inplace=True)
df_cinema_total.fillna(0, inplace=True)
df_cinema_total["compete_cinema_code"].replace(0, "", inplace=True)
df_cinema_total["compete_cinema_name"].replace(0, "", inplace=True)
df_cinema_total["cinema_session"] = df_cinema_total[
"cinema_session"].astype(int)
df_cinema_total["occupancy"] = df_cinema_total["occupancy"].astype(float)
#计算两列
df_cinema_total["avg_price"] = np.round(np.divide(df_cinema_total["bo"],df_cinema_total["people"],out = np.zeros_like(df_cinema_total["bo"]),\
where = df_cinema_total["people"] != 0),2)
df_cinema_total["session_percent"] = np.round(np.divide(df_cinema_total["session"],df_cinema_total["cinema_session"],\
out = np.zeros_like(df_cinema_total["session"]),where = df_cinema_total["cinema_session"] != 0),4) * 100
logger.info("combine data process complete")
return df_cinema_total