def add_router(user_data):
"""
Метод проверяет пользователя и добавляет маршрут в базу
:param user_data: dict хранит информацию о маршруте
:return: router_id - идентификатор маршрута
"""
user_data[names.ID_USER] = auth.session_verification(user_data[names.UUID])
#pprint(user_data)
sql = """INSERT INTO routes_gis (id_user, is_private, score, name, route) VALUES
({id_user}, {is_private}, {score}, '{name}', '{route}') RETURNING id_route
""".format(id_user=user_data[names.ID_USER],
is_private=user_data[names.IS_PRIVATE],
score=user_data[names.SCORE],
name=user_data[names.NAME],
route=gs.converter(user_data[names.ROUTE]))
#print(sql)
try:
result = gs.SqlQuery(sql)
print(result)
except:
return {names.ANSWER: names.ERROR}
if result is None:
return {names.ANSWER: names.ERROR}
return {names.ANSWER: names.SUCCESS, names.DATA: result[0]}
def add_new_point(new_point):
sql = " INSERT INTO Geo (Name, X, Y, Type, Descript, Rating, Time) VALUES (\'{}\', {}, {}, \'{}\', \'{}\', {}, {})".format(
new_point["name"],
float(new_point["x"]),
float(new_point["y"]),
new_point["type"],
new_point["description"],
int(new_point["rating"]),
int(new_point["time"]))
gs.SqlQuery(sql)
sql = "SELECT id FROM Geo WHERE X={} AND Y={}".format(new_point["x"], new_point["y"])
new_point["id"] = gs.SqlQuery(sql)
new_point["id"] = int(new_point["id"][0]['id'])
points = gs.SqlQuery("SELECT id, x, y FROM Geo WHERE id <> (SELECT last_value from geo_id_seq)")
for i in range(len(points)):
data = []
disti = str(points[i]['x']) + "," + str(points[i]['y'])
distj = str(new_point['x']) + "," + str(new_point['y'])
data.append(disti)
data.append(distj)
answer = get_google(data)
print(points[i]['id'], new_point['id'], answer)
sql = "INSERT INTO geo_distance (point_1, point_2, distance) VALUES ({}, {}, {})".format(
points[i]['id'],
new_point['id'],
answer)
gs.SqlQuery(sql)
gs.SqlQuery("INSERT INTO geo_distance (point_1, point_2, distance) VALUES ({}, {}, {})".format(
new_point['id'],
points[i]['id'],
answer))
def get_from_google(query):
"""
:param query: достопримечательности+город
:return: list с точками
"""
key = "AIzaSyDMIfc6_9K7574xu18dG6ayTuAWsZtEOgE"
s = req.Session()
url = "https://maps.googleapis.com/maps/api/place/textsearch/json?query={}&key={}&language=ru".format(query,
key)
answer = s.get(url)
answer = gs.converter(answer.text)['results']
result = []
for res in answer:
js = {"name": res["name"],
"description": res["place_id"],
"x": None,
"y": None,
"rating": 0,
"time": 0}
if js["rating"]:
js["rating"] = res["rating"]
else:
js["rating"] = 0
js["x"] = res["geometry"]["location"]["lat"]
js["y"] = res["geometry"]["location"]["lng"]
js["type"] = res["types"][0]
result.append(js)
return result
def auth_user(user_data):
"""
Метод авторизирует пользователя, присваивая ему UUID сессии
:param user_data: dict хранит информацию о пользователе
:return: UUID сессии
"""
password_hash = hashlib.md5()
password_hash.update(user_data[names.PASSWORD].encode())
user_data[names.PASSWORD] = password_hash.hexdigest()
try:
sql = "SELECT id_user FROM Auth_gis WHERE Login = '******' and Password = '******'".format(
user_data[names.LOGIN], user_data[names.PASSWORD])
result = gs.SqlQuery(sql)
except:
return {names.ANSWER: "Ошибка запроса к базе данных"}
try:
if len(result) == 0:
return {
names.ANSWER: names.WARNING,
names.DATA: "Данного пользователя нет в базе данных"
}
except:
return {
names.ANSWER: names.WARNING,
names.DATA: "Логин или пароль не правильные"
}
answer = input_session_table(result[0].get(names.ID_USER))
if answer.get(names.ANSWER) is not names.SUCCESS:
return {
names.ANSWER: names.WARNING,
names.DATA: "Ошибка запроса к базе данных. Неудача"
}
return answer
def get_fast(self, start, finish, list_coord):
str_destinations = ""
for coord in list_coord:
str_destinations += str(coord['x']) + ", " + str(coord['y']) + "|"
str_destinations += str(finish[0]) + ", " + str(finish[1])
str_origin = str(start[0]) + ", " + str(start[1]) + "|" + str(
finish[0]) + ", " + str(finish[1])
s = req.Session()
for k in key:
try:
url = "https://maps.googleapis.com/maps/api/distancematrix/json?origins={}&destinations={}&key={}&mode=walking".format(
str_origin, str_destinations, k)
answer = None
answer = s.get(url)
answer = gs.converter(answer.text)['rows']
for dist in answer[0]['elements']:
self.record['s'].append(dist['duration']['value'] // 60)
break
except:
self.key = Google.set_google_key()
for dist in answer[0]['elements']:
self.record['f'].append(dist['duration']['value'] // 60)
record_o = answer[0]['elements'][len(answer[0]['elements']) -
1]['duration']['value']
self.record['s'] = self.record['s']
self.record['f'] = self.record['f']
self.record['o'] = record_o
return self.record
def input_auth_table(user_data):
"""
Метод начинает цепочку регистрации в бд
Заносится изменение в таблицу auth
:param user_data: dict данные пользователя
:return: UUID сессии
"""
password_hash = hashlib.md5()
password_hash.update(user_data[names.PASSWORD].encode())
user_data[names.PASSWORD] = password_hash.hexdigest()
sql = """INSERT INTO Auth_gis (login, password)
VALUES ('{Login}','{Password}') RETURNING id_user""".format(
Login=user_data.get(names.LOGIN),
Password=user_data.get(names.PASSWORD))
try:
id_user = gs.SqlQuery(sql)[0]['id_user']
except:
logging.error(
'error: Ошибка запроса к базе данных. Возможно такой пользователь уже есть'
)
return {
names.ANSWER:
names.WARNING,
names.DATA:
"Ошибка запроса к базе данных. Возможно такой пользователь уже есть"
}
return input_user_table(id_user, user_data)
def get_router(param=None, data=None):
if data is None:
sql = """SELECT users_gis.name as username, routes_gis.id_route, routes_gis.route, routes_gis.name, routes_gis.score
FROM routes_gis INNER JOIN users_gis
ON routes_gis.id_user = users_gis.id_user where routes_gis.is_private=false"""
result = gs.SqlQuery(sql)
return {names.ANSWER: names.SUCCESS, names.DATA: result}
if param == "get_route":
sql = """select route from routes_gis where id_route={}""".format(
data["id_route"])
result = gs.SqlQuery(sql)
return {names.ANSWER: names.SUCCESS, names.DATA: result}
if param == "get_usr":
sql = "select route from routes_gis where id_user={}".format(
data[names.ID])
result = gs.SqlQuery(sql)
return {names.ANSWER: names.SUCCESS, names.DATA: result}
def parse_data(self):
self.data = self.__args.get('data', None)
self.param = self.__args.get('param', None)
print("param:", self.param)
print("not conv data: ", self.data)
if self.data is not None:
self.data = gs.converter(self.data)
print("data: ", self.data)
return
def get_filter(self):
get_sql = """select distinct type FROM geo where type!='point_of_interest' and type!='church' and type !='park' and
type!='museum' and type!='zoo' and type!='funeral_home' and type!='premise' and type!='art_gallery'
"""
dict_type = gs.SqlQuery(get_sql)
result = []
for d in dict_type:
result.append(d["type"])
return {"data": result}
def parse_data(self):
data = self.__args.get('data', None)
#print(data)
data = gs.converter(data)
self.__data_origin_X = data["origin"]["X"]
self.__data_origin_Y = data["origin"]['Y']
self.__data_destination_X = data["destination"]["X"]
self.__data_destination_Y = data["destination"]["Y"]
self.__time = int(data["time"])
self.__priority = data["priority"]
def _get_distance(self):
result = []
distance = gs.converter(self.distance)
for i in range(len(distance["rows"][0]["elements"])):
times = distance["rows"][0]["elements"][i]['duration'][
'text'].split()
if len(times) > 2:
result.append(int(times[0]) * 60 + int(times[2]))
else:
result.append(int(times[0]))
return result
def get(self):
"""
parser = reqparse.RequestParser()
parser.add_argument('data')
args = parser.parse_args()
print('GET /')
print(request.headers)
print('cookies = ', request.cookies)
print('ARGS = ', args)
data = args.get('data', None)
print(data)
data = json.loads(data)
print(data["origin"])
data_origin = []
data_destination = []
data_origin.append(float(data["origin"]["X"]))
data_origin.append(float(data["origin"]['Y']))
data_destination.append(float(data["destination"]["X"]))
data_destination.append(float(data["destination"]["Y"]))
data_origin = tuple(data_origin)
data_destination = tuple(data_destination)
#datas = (data_origin, data_destination)
priority = []
data_origin.append(float(data["priority"]))
#print("datas", datas)
#print("user_time", int(data["time"]))
#answer = get_many(datas, int(data["time"]))
#print("OK SEND")
#answer = json.dumps(answer)
"""
answer = gs.converter({
"route": [
{
"name": ["Lenina", "duck", "f**k", "ducken"],
"time": [20, 30, 40, 35],
"descr": ["descr_lenina", "discr_duck", "discr_fuck", "duckduck"],
"Y": [56.845229, 56.839619, 56.840200, 56.841996],
"type": ["park", "galery", "park", "park"],
"X": [60.645281, 60.647116, 60.654428, 60.658903]
},
{
"name": ["Lenina", "duck", "ducken", "f**k"],
"time": [20, 30, 35, 40],
"descr": ["descr_lenina", "discr_duck", "duckduck", "discr_fuck"],
"Y": [56.845229, 56.839619, 56.841996, 56.840200],
"type": ["park", "galery", "park", "park"],
"X": [60.645281, 60.647116, 60.658903, 60.654428]
}
]
})
return answer, 200, {'Access-Control-Allow-Origin': '*'}
def get_user_name(id_user):
"""
Метод возвращает имя пользователя
:param id_user: int, id пользователя
:return:
"""
sql = """Select name from users_gis where id_user = {id_user}""".format(
id_user=id_user)
try:
result = gs.SqlQuery(sql)
except:
return {names.ANSWER: names.ERROR}
return {names.ANSWER: names.SUCCESS, names.DATA: result[0]}
def set_google_key():
url = "https://maps.googleapis.com/maps/api/distancematrix/json?units=imperial&origins=40.6655101," \
"-73.89188969999998&destinations=40.6905615%2C-73.9976592%7C40.6905615%2C-73.9976592%7C40.6905615%2C" \
"-73.9976592%7C40.6905615%2C-73.9976592%7C40.6905615%2C-73.9976592%7C40.6905615%2C" \
"-73.9976592%7C40.659569%2C-73.933783%7C40.729029%2C-73.851524%7C40.6860072%2C" \
"-73.6334271%7C40.598566%2C-73.7527626%7C40.659569%2C-73.933783%7C40.729029%2C" \
"-73.851524%7C40.6860072%2C-73.6334271%7C40.598566%2C-73.7527626&key={}"
s = req.Session()
for k in key:
answer = s.get(url.format(k))
answer = gs.converter(answer.text)
if answer["status"] == "OK":
return k
return "key not found"
def get_login(id_user):
"""
Метод получает логин по ID пользователя
:param id_user: ID пользователя
:return: str Логин пользователя
"""
try:
sql = "SELECT Login FROM Auth_gis WHERE User = '******'".format(id_user)
result = gs.SqlQuery(sql)
except:
return None
try:
if len(result) == 0:
return None
except:
return None
return result[names.LOGIN]
def logout_user(session):
"""
Метод закрывает сессию пользователя
:param session: UUID сессии, которую нужно закрыть
"""
try:
sql = "DELETE FROM Session_gis WHERE UUID = '{}'".format(session)
result = gs.SqlQuery(sql)
except:
return {names.ANSWER: "Ошибка запроса к базе данных"}
try:
if len(result) == 0:
return {
names.ANSWER: names.WARNING,
names.DATA: "Такой сессии нет в базе"
}
except:
return {names.ANSWER: names.WARNING, names.DATA: "Сессия неверная"}
def get_one_to_one(self, start, finish, op=None, record=None):
start = '{},{}'.format(start[0], start[1])
finish = '{},{}'.format(finish[0], finish[1])
s = req.Session()
url = "https://maps.googleapis.com/maps/api/distancematrix/json?units=metric&mode=walking&origins={}&destinations={}&key={}".format(
start, finish, self.key)
answer = s.get(url)
answer = gs.converter(
answer.text)['rows'][0]['elements'][0]['duration']['text'].split()
if len(answer) > 2:
if op:
record[op] = int(answer[0]) * 60 + int(answer[2])
return int(answer[0]) * 60 + int(answer[2])
else:
if op:
record[op] = int(answer[0])
return int(answer[0])
def input_session_table(id_user):
"""
Конечный метод, регистрирует данные в таблицы "Сессии пользователей"
:param id_user: int ID пользователя
:return: UUID сессии
"""
UUID = uuid.uuid4()
sql = """INSERT INTO Session_gis (id_user, uuid)
VALUES ({id}, '{UUID}')""".format(id=id_user, UUID=UUID)
try:
gs.SqlQuery(sql)
except:
logging.error('error: Ошибка запроса к базе данных')
return {
names.ANSWER: names.WARNING,
names.DATA: "Ошибка запроса к базе данных"
}
return {names.ANSWER: names.SUCCESS, names.DATA: {"UUID": str(UUID)}}
def session_verification(session):
"""
Метод проверяет, существует ли пользовательская сессия
:param session: UUID сессии
:return: int Возвращает ID пользователя
"""
try:
sql = "SELECT id_user FROM Session_gis WHERE UUID = '{}'".format(
session)
result = gs.SqlQuery(sql)
except:
return None
try:
if len(result) == 0:
return None
except:
return None
return result[0]["id_user"]
def input_user_table(id_user, user_data):
"""
Промежуточный метод, добавляет данные в таблицу "Информация о пользователе"
:param id_user: int ID пользователя
:param user_data: dict Данные пользователя
:return: UUID сессии
"""
user_data[names.ID_USER] = id_user
sql = """INSERT INTO Users_gis
VALUES ({id_user},'{Name}','','{Email}',
'{Sex}','{City}')"""\
.format(**user_data)
try:
gs.SqlQuery(sql)
except:
logging.error('error: Ошибка запроса к базе данных')
return {
names.ANSWER: names.WARNING,
names.DATA: "Ошибка запроса к базе данных"
}
return input_session_table(id_user)
def parse_data(self):
self.data = self.__args.get('data', None)
self.data = gs.converter(self.data)
print("data: ", self.data)
return
