def add_stop(bot, update):
logger.info("%s adds stop" % update.message.from_user.username)
data['chat_id'] = update.message.chat_id
conn = connect_to_database()
cur = conn.cursor()
SQL = """
WITH j_id AS (
SELECT journey_id
FROM travelers
WHERE user_id = %s
)
SELECT journey_name
FROM journey
NATURAL JOIN j_id;"""
query_data = (update.message.from_user.id, )
cur.execute(SQL, query_data)
journey_names = cur.fetchall()
cur.close()
disconnect_from_database(conn)
if len(journey_names) == 0:
update.message.reply_text(
'You have no journies to add stop to, first use /create_journey command'
)
return -1
reply_keyboard = [[]]
for journey in journey_names:
reply_keyboard[0].append(journey[0])
update.message.reply_text('What journey do you want to add stop to?',
reply_markup=ReplyKeyboardMarkup(
reply_keyboard, one_time_keyboard=True))
return ADD_STOP['JOURNEY_NAME']
def show_journies(update):
if data['offset'] == 0:
wholeSQL = data['wholeSQl'] + 'LIMIT 11;'
else:
wholeSQL = data['wholeSQl'] + 'OFFSET ' + data['offset'] + 'LIMIT 11;'
conn = connect_to_database()
cur = conn.cursor()
if len(data['query_data']) != 0:
cur.execute(wholeSQL, tuple(data['query_data']))
else:
cur.execute(wholeSQL)
journey_ids = cur.fetchall()
data['rows_amount'] = len(journey_ids)
if data['rows_amount'] == 0:
update.message.reply_text('There are no journies that would suit you')
return -1
journey_ids_list = []
for i in range(min(data['rows_amount'], 10)):
journey_ids_list.append(journey_ids[i][0])
SQL = """
SELECT journey_id, departure_point, destination, budget, journey_name, departure_date, arrival_date
FROM journey
WHERE journey_id in """ + str(
tuple(journey_ids_list))[:-2] + ')' + ';'
cur.execute(SQL)
journies = cur.fetchall()
cur.close()
disconnect_from_database(conn)
for row in journies:
keyboard = [[
InlineKeyboardButton("Show", callback_data='s' + str(row[0])),
InlineKeyboardButton("Join", callback_data='j' + str(row[0]))
]]
reply_markup = InlineKeyboardMarkup(keyboard)
message_text = row[4].upper() + '\ndeparture:\n place: ' + row[1]
if row[5] is not None:
message_text += ' date: ' + str(row[5])[:10]
message_text += '\narrival:\n place: ' + row[2]
if row[6] is not None:
message_text += ' date: ' + str(row[6])[:10]
if row[3] is not None:
message_text += '\nbudget: ' + row[3]
update.message.reply_text(text=message_text, reply_markup=reply_markup)
if data['rows_amount'] > 10:
keyboard = [[InlineKeyboardButton("others", callback_data='see')]]
reply_markup = InlineKeyboardMarkup(keyboard)
update.message.reply_text(text='to see other journies press',
reply_markup=reply_markup)
keyboard = [[InlineKeyboardButton("Cancel", callback_data='cancel')]]
reply_markup = InlineKeyboardMarkup(keyboard)
update.message.reply_text(text='to cancel press',
reply_markup=reply_markup)
data['cancel_been_pushed'] = False
def my_journies(bot, update):
data['no_stops_left'] = False
logger.info("Journies %s participates in" %
update.message.from_user.username)
data['chat_id'] = update.message.chat_id
user_id = update.message.from_user.id
data['user_id'] = user_id
conn = connect_to_database()
cur = conn.cursor()
SQL = """
WITH j_id AS (
SELECT journey_id, is_host
FROM travelers
WHERE user_id = %s
)
SELECT journey_id, departure_point, destination, budget, journey_name, departure_date, arrival_date, is_host
FROM journey
NATURAL JOIN j_id;"""
query_data = (user_id, )
cur.execute(SQL, query_data)
journies = cur.fetchall()
cur.close()
disconnect_from_database(conn)
if len(journies) == 0:
update.message.reply_text(
'You have no journies, first use /create_journey command')
return -1
for row in journies:
if row[7]:
keyboard = [[
InlineKeyboardButton("Show", callback_data='s' + str(row[0])),
InlineKeyboardButton("Delete", callback_data='d' + str(row[0]))
]]
else:
keyboard = [[
InlineKeyboardButton("Show", callback_data='s' + str(row[0]))
]]
reply_markup = InlineKeyboardMarkup(keyboard)
message_text = row[4].upper() + '\ndeparture:\n place: ' + row[1]
if row[5] is not None:
message_text += ' date: ' + str(row[5])[:10]
message_text += '\narrival:\n place: ' + row[2]
if row[6] is not None:
message_text += ' date: ' + str(row[6])[:10]
if row[3] is not None:
message_text += '\nbudget: ' + row[3]
update.message.reply_text(text=message_text, reply_markup=reply_markup)
keyboard = [[InlineKeyboardButton("Cancel", callback_data='cancel')]]
reply_markup = InlineKeyboardMarkup(keyboard)
update.message.reply_text(text='to cancel press',
reply_markup=reply_markup)
data['journey_been_pushed'] = False
return MY_JOURNEY['SHOW_OR_DELETE_JOURNEY']
def show(bot, journey_id):
conn = connect_to_database()
cur = conn.cursor()
SQL = "SELECT first_stop, journey_name FROM journey WHERE journey_id = %s"
query_data = (journey_id, )
cur.execute(SQL, query_data)
row = cur.fetchone()
journey_name = row[1]
first_stop_id = row[0]
if first_stop_id is None:
bot.sendMessage(chat_id=data['chat_id'],
text='Journey %s has no stops' % (journey_name, ))
return
bot.sendMessage(chat_id=data['chat_id'], text=journey_name.upper())
SQL = """
SELECT stop_id, transport_id_for_arrival, transport_id_for_department
FROM stops
WHERE journey_id = %s"""
query_data = (journey_id, )
cur.execute(SQL, query_data)
stops = cur.fetchall()
next_transport_id_for_arrival = -1
last = False
for row in stops:
if row[0] == first_stop_id:
next_transport_id_for_arrival = row[2]
if len(stops) == 1:
last = True
print_transport(cur, bot, row, last, journey_id)
stops.remove(row)
break
for i in range(len(stops)):
for row in stops:
if row[1] == next_transport_id_for_arrival:
next_transport_id_for_arrival = row[2]
if len(stops) == 1:
last = True
print_transport(cur, bot, row, last, journey_id)
stops.remove(row)
break
cur.close()
disconnect_from_database(conn)
def choosen(bot, update):
if data['been_pushed']:
return
data['been_pushed'] = True
choice = update.callback_query
conn = connect_to_database()
cur = conn.cursor()
if choice.data == 'cancel':
not_scheduled(cur)
else:
data['price'] = float(choice.data[choice.data.find('$') + 1:])
upload_new_stop_and_update_last(
cur, int(choice.data[:choice.data.find('$')]))
cur.close()
disconnect_from_database(conn)
logger.info("stop with scheduled transport has been added")
bot.sendMessage(chat_id=data['chat_id'], text="Stop has been added")
return -1
def join(journey_id, update, bot):
user_id = data['user_id']
conn = connect_to_database()
cur = conn.cursor()
SQL = 'SELECT journey_name FROM journey WHERE journey_id = %s'
query_data = (journey_id, )
cur.execute(SQL, query_data)
journey_name = cur.fetchone()[0]
SQL = 'SELECT is_host FROM travelers WHERE user_id = %s AND journey_id = %s;'
query_data = (
user_id,
journey_id,
)
cur.execute(SQL, query_data)
result = cur.fetchone()
if result is None:
SQL = 'INSERT INTO travelers (user_id, journey_id, is_host) VALUES (%s, %s, False);'
query_data = (
user_id,
journey_id,
)
cur.execute(SQL, query_data)
bot.sendMessage(chat_id=data['chat_id'],
text='You have joined journey %s' % (journey_name, ))
else:
is_host = result[0]
if is_host:
bot.sendMessage(chat_id=data['chat_id'],
text='Silly, journey %s is yours)' %
(journey_name, ))
else:
bot.sendMessage(chat_id=data['chat_id'],
text='You already participate in journey %s' %
(journey_name, ))
cur.close()
disconnect_from_database(conn)
def show_or_delete_journey(bot, update):
if data['journey_been_pushed']:
return
data['journey_been_pushed'] = True
choice = update.callback_query
if choice.data == 'cancel':
bot.sendMessage(chat_id=data['chat_id'],
text='continue interaction using commands')
logger.info("my_journies command canceled")
return -1
conn = connect_to_database()
cur = conn.cursor()
journey_id = int(choice.data[1:])
data['journey_id'] = journey_id
if choice.data[0] == 'd': #delete
SQL = "SELECT journey_name FROM journey WHERE journey_id = %s;"
query_data = (journey_id, )
cur.execute(SQL, query_data)
journey_name = cur.fetchone()[0]
logger.info("deleting of journey %s", journey_name)
SQL = "DELETE FROM journey WHERE journey_id = %s"
query_data = (journey_id, )
cur.execute(SQL, query_data)
cur.close()
disconnect_from_database(conn)
bot.sendMessage(chat_id=data['chat_id'],
text='Journey %s has been deleted' % (journey_name, ))
return
else:
SQL = "SELECT is_host FROM travelers WHERE user_id = %s AND journey_id = %s"
query_data = (data['user_id'], data['journey_id'])
cur.execute(SQL, query_data)
data['is_host'] = cur.fetchone()[0]
data['button_number'] = 0
show(cur, conn, bot, update)
return MY_JOURNEY['EDITING']
def add_journey_name(bot, update):
journey_name = update.message.text
conn = connect_to_database()
cur = conn.cursor()
SQL = """
SELECT journey_id, first_stop
FROM journey
NATURAL JOIN travelers
WHERE user_id = %s
AND journey_name = %s;"""
query_data = (update.message.from_user.id, journey_name)
cur.execute(SQL, query_data)
result = cur.fetchone()
cur.close()
disconnect_from_database(conn)
data['journey_id'] = result[0]
data['is_first'] = (result[1] is None)
update.message.reply_text('input place of departure')
logger.info("%s chooses the journey to add stop to" %
update.message.from_user.username)
return ADD_STOP['DEPARTURE_PLACE']
def start(bot, update):
conn = connect_to_database()
cur = conn.cursor()
SQL = "SELECT count(1) FROM users WHERE login = %s;"
query_data = (update.message.from_user.username, )
cur.execute(SQL, query_data)
result = cur.fetchone()[0]
if result == 0:
SQL = "INSERT INTO users (user_id, login) VALUES (%s, %s);"
query_data = (
update.message.from_user.id,
update.message.from_user.username,
)
cur.execute(SQL, query_data)
logging.info(
'user with user_id=%s, username=%s has been added to the database'
% (update.message.from_user.id, update.message.from_user.username))
cur.close()
disconnect_from_database(conn)
update.message.reply_text(
'Hello!\nUse this bot to plan your journeys and participate in journeys of others'
)
def put_in_database( bot, update):
try:
conn = connect_to_database()
cur = conn.cursor()
#new journey
SQL = "INSERT INTO journey (journey_name, departure_point, destination, departure_date, arrival_date, is_public) VALUES (%s, %s, %s, %s, %s, %s);"
query_data = (data['name'], data['departure_point'], data['destination'], data['departure_date'], data['arrival_date'], data['publicity'],)
cur.execute(SQL, query_data)
logger.info("%s's journey %s has been putted in database" % (update.message.from_user.username, data['name']))
#what journey_id does new journey have
SQL = "SELECT currval('journey_journey_id_seq');"
cur.execute(SQL)
journey_id = cur.fetchone()[0]
#accordance of user and journey
SQL = "INSERT INTO travelers (journey_id, user_id, is_host) VALUES (%s, %s, %s);"
query_data = (journey_id, update.message.from_user.id, True)
cur.execute(SQL, query_data)
cur.close()
disconnect_from_database(conn)
logger.info("%s participates in journey %s" % (update.message.from_user.username, data['name']))
update.message.reply_text('Congratulations, journey ' + data['name'] + ' has been created')
except Exception as e:
update.message.reply_text('wrong input, journey ' + data['name'] + ' has not been created')
logger.info("Exception %s has happened" % e)
def add_name(bot, update):
journey_name = update.message.text
conn = connect_to_database()
cur = conn.cursor()
SQL = """
SELECT count(1)
FROM journey
NATURAL JOIN travelers
WHERE user_id = %s
AND journey_name = %s;
"""
query_data = (update.message.from_user.id, journey_name,)
cur.execute(SQL, query_data)
result = cur.fetchone()[0]
cur.close()
disconnect_from_database(conn)
if result != 0:
update.message.reply_text('You already have journey named %s, choose another name' % journey_name)
return CREATE_JOURNEY['NAME']
data['name'] = journey_name
logger.info("%s named his journey %s" % (update.message.from_user.username, update.message.text))
update.message.reply_text('Input place of departure')
return CREATE_JOURNEY['DEPARTURE_PLACE']
def add_is_scheduled(bot, update):
logger.info("%s input yes or no" % update.message.from_user.username)
show_suitable_transport = (update.message.text.lower() == 'yes')
conn = connect_to_database()
cur = conn.cursor()
if show_suitable_transport:
transport = []
if data['price'] is None:
if data['type'] is None:
SQL = """
SELECT transport_id, departure_point, destination, departure_time, arrival_time, price, type
FROM transport WHERE departure_point = %s AND destination = %s AND departure_time >= %s AND arrival_time <= %s AND is_scheduled = %s;"""
query_data = (
data['departure_place'],
data['destination'],
data['departure_time'],
data['arrival_time'],
True,
)
cur.execute(SQL, query_data)
transport = cur.fetchall()
else:
SQL = """
SELECT transport_id, departure_point, destination, departure_time, arrival_time, price, type
FROM transport WHERE departure_point = %s AND destination = %s AND departure_time >= %s AND arrival_time <= %s AND type = %s AND is_scheduled = %s;"""
query_data = (
data['departure_place'],
data['destination'],
data['departure_time'],
data['arrival_time'],
data['type'],
True,
)
cur.execute(SQL, query_data)
transport = cur.fetchall()
else:
if data['type'] is None:
SQL = """
SELECT transport_id, departure_point, destination, departure_time, arrival_time, price, type
FROM transport WHERE departure_point = %s AND destination = %s AND departure_time >= %s AND arrival_time <= %s AND price <= %s AND is_scheduled = %s;"""
query_data = (
data['departure_place'],
data['destination'],
data['departure_time'],
data['arrival_time'],
data['price'],
True,
)
cur.execute(SQL, query_data)
transport = cur.fetchall()
else:
SQL = """
SELECT transport_id, departure_point, destination, departure_time, arrival_time, price, type
FROM transport WHERE departure_point = %s AND destination = %s AND departure_time >= %s AND arrival_time <= %s AND price <= %s AND type = %s AND is_scheduled = %s;"""
query_data = (
data['departure_place'],
data['destination'],
data['departure_time'],
data['arrival_time'],
data['price'],
data['type'],
True,
)
cur.execute(SQL, query_data)
transport = cur.fetchall()
logger.info("transport suitable for %s recieved" %
update.message.from_user.username)
if len(transport) == 0:
not_scheduled(cur)
update.message.reply_text(
'There is no suitable for you transport.\nStop has been added')
cur.close()
disconnect_from_database(conn)
return -1
cur.close()
disconnect_from_database(conn)
for row in transport:
keyboard = [[
InlineKeyboardButton("Select",
callback_data=str(row[0]) + str(row[5]))
]]
reply_markup = InlineKeyboardMarkup(keyboard)
update.message.reply_text(
'departure:\n place: ' + row[1] + ' time: ' +
str(row[3])[:16] + '\narrival:\n place: ' + row[2] +
' time: ' + str(row[4])[:16] + '\nprice: ' + row[5] +
'\ntype: ' + row[6],
reply_markup=reply_markup)
keyboard = [[InlineKeyboardButton("Cancel", callback_data='cancel')]]
reply_markup = InlineKeyboardMarkup(keyboard)
update.message.reply_text(text='to add not scheduled transport press',
reply_markup=reply_markup)
data['been_pushed'] = False
return ADD_STOP['CHOOSEN']
else:
not_scheduled(cur)
cur.close()
disconnect_from_database(conn)
update.message.reply_text('Stop has been added')
return -1
def edit_journey(bot, update):
choice = update.callback_query
delete_start = choice.data.find('delete')
leave_start = choice.data.find('leave')
if leave_start != -1:
number_of_button = int(choice.data[:leave_start])
if number_of_button != data['button_number']:
logger.info("leave already pushed")
return MY_JOURNEY['EDITING']
else:
logger.info("journey has been left as it was")
SQL = "SELECT journey_name FROM journey WHERE journey_id = %s;"
query_data = (data['journey_id'], )
conn = connect_to_database()
cur = conn.cursor()
cur.execute(SQL, query_data)
journey_name = cur.fetchone()[0]
disconnect_from_database(conn)
bot.sendMessage(chat_id=data['chat_id'],
text="Journey %s has been saved" %
(journey_name, ))
return -1
elif delete_start != -1:
number_of_button = int(choice.data[:delete_start])
if number_of_button != data['button_number']:
logger.info("delete already pushed")
return MY_JOURNEY['EDITING']
else:
bot.sendMessage(chat_id=data['chat_id'],
text='continue interaction using commands')
logger.info("my_journies command canceled")
return -1
logger.info("journey's last stop will be deleted")
conn = connect_to_database()
cur = conn.cursor()
last_stop_id = int(choice.data[delete_start + 6:])
SQL = 'SELECT transport_id_for_arrival FROM stops WHERE stop_id = %s;'
query_data = (last_stop_id, )
cur.execute(SQL, query_data)
deleted_transport_id_for_arrival = cur.fetchone()[0]
SQL = 'UPDATE stops SET transport_id_for_department = %s WHERE journey_id = %s AND transport_id_for_department = %s;'
query_data = (
None,
data['journey_id'],
deleted_transport_id_for_arrival,
)
cur.execute(SQL, query_data)
SQL = "SELECT first_stop FROM journey WHERE journey_id = %s"
query_data = (data['journey_id'], )
cur.execute(SQL, query_data)
first_stop_id = cur.fetchone()[0]
if first_stop_id == last_stop_id:
SQL = "UPDATE journey SET first_stop = %s WHERE journey_id = %s"
query_data = (None, data['journey_id'])
cur.execute(SQL, query_data)
data['no_stops_left'] = True
SQL = "DELETE FROM stops WHERE stop_id = %s;"
query_data = (last_stop_id, )
cur.execute(SQL, query_data)
bot.sendMessage(chat_id=data['chat_id'], text='Last stop has been deleted')
show(cur, conn, bot, update)
return MY_JOURNEY['EDITING']
def sendQueryResult():
query = request.get_json()['query']
conn = connect_to_database()
queryResult = get_query_result(conn, query)
return make_response(jsonify(queryResult), 200)