def get_record_by_log_id(log_id):
conn = create_connection("db.sqlite3")
(row,) = select_record(conn, "logbook", LOG_COLUMN, {"id": log_id})
conn.close()
return row
def get_record_by_log_ids(log_ids):
conn = create_connection("db.sqlite3")
rows = select_record(conn, "logbook", LOG_COLUMN, {}, f"id IN ({log_ids})")
conn.close()
return rows
def make_text_from_logs(logs, header="", footer=""):
text_message = header
chat_id = ""
for row in logs:
user_id = row[1]
first_name = row[2]
last_name = row[3]
work_content_id = row[-1]
if chat_id != user_id:
chat_id = user_id
text_message += f"\n\n*_{first_name} {last_name}_'s log as below*\n"
record = make_record_text(row)
if work_content_id:
conn = create_connection()
rows = select_record(
conn, "contents", ["work_content"], {"id": work_content_id}
)
work_content = rows[0][0].replace("\\n", "\n")
record += f" work content : {work_content} \n"
text_message += record
text_message += footer
return text_message
def post_remarks_by_log_ids(log_ids):
conn = create_connection("db.sqlite3")
(row,) = update_record(conn, "logbook", {"remarks": ""}, f"id IN ({log_ids})")
conn.close()
return row
def get_text_of_log_by_ids(log_ids):
conn = create_connection()
rows = select_record(conn, "logbook", LOG_COLUMN, {}, f"id IN ({log_ids})")
conn.close()
text_message = make_text_from_logs(rows)
return text_message
def get_text_of_log_by_id(log_id):
conn = create_connection()
rows = select_record(conn, "logbook", LOG_COLUMN, {"id": log_id})
conn.close()
text_message = make_text_from_logs(rows)
return text_message
def delete_content(update, context):
log_id = context.user_data.get("log_id")
conn = create_connection()
work_content_id = select_record(
conn, "logbook", ["work_content_id"], {"id": log_id}
)[0][0]
update_record(conn, "logbook", {"work_content_id": ""}, log_id)
delete_record(conn, "contents", {"id": work_content_id})
return log_id
def put_location(location, user_data):
"""
docstring
"""
if location:
conn = create_connection("db.sqlite3")
record = {"longitude": location.longitude, "latitude": location.latitude}
update_record(conn, "logbook", record, str(user_data.get("log_id")))
conn.close()
return True
return False
def get_today_log_of_chat_id_category(chat_id, category):
start_date = date.today()
end_date = start_date + timedelta(1)
conn = create_connection("db.sqlite3")
rows = select_record(
conn,
"logbook",
LOG_COLUMN,
{"chat_id": chat_id, "category": category},
f" AND timestamp > '{start_date}' AND timestamp < '{end_date}' ORDER BY timestamp",
)
conn.close()
return rows
def set_remarks(update, context):
log_ids = context.user_data.get("remarks_log_ids")
content = update.message.text
record = {"remarks": content}
conn = create_connection()
update_records(conn, "logbook", record, {}, f"id IN ({log_ids})")
conn.close()
context.user_data["log_id"] = log_ids
text_message = "remarks has been updated.\n"
text_message += get_text_of_log_by_ids(log_ids)
reply_markdown(update, context, text_message)
return ConversationHandler.END
def remove_log(update, context):
choices = {"YES": True, "NO": False}
answer = choices.get(update.message.text)
if answer:
log_id = context.user_data.get("remove_log_id")
conn = create_connection()
delete_record(conn, "logbook", {"id": log_id})
conn.close()
text_message = f"Log No.{log_id} has been Deleted\n"
reply_markdown(update, context, text_message)
context.user_data.clear()
else:
text_message = "process has been stoped. The log has not been deleted."
reply_markdown(update, context, text_message)
return ConversationHandler.END
def put_work_content(update, context, work_content, work_content_id):
user = update.message.from_user
record = {
"chat_id": user.id,
"first_name": user.first_name,
"last_name": user.last_name,
"timestamp": update.message.date.astimezone(pytz.timezone("Africa/Douala")),
"work_content": work_content,
}
conn = create_connection()
content_id = update_record(conn, "contents", record, work_content_id)
logbook_record = {"work_content_id": content_id}
update_record(conn, "logbook", logbook_record, context.user_data.get("log_id"))
conn.close()
def ask_confirmation_of_removal(update, context):
log_id = context.user_data.get("log_id")
if log_id:
conn = create_connection()
row = select_record(conn, "logbook", LOG_COLUMN, {"id": log_id})
conn.close()
header_message = f"Do you really want to do remove log No.{log_id}?\n"
text_message = make_text_from_logs(row, header_message)
keyboard = [["REMOVE GET BACK LOG", "NO"]]
reply_markdown(update, context, text_message, keyboard)
return ANSWER_LOG_DELETE
else:
text_message = "An Error has been made. Please try again."
reply_markdown(update, context, text_message)
return ConversationHandler.END
def get_work_content_file(update, context):
conn = create_connection()
cursor = conn.cursor()
cursor.execute("SELECT * FROM contents")
rows = cursor.fetchall()
conn.close()
record = [
list(map(lambda x: str(x).replace("\\n", "\n"), row)) for row in rows
]
write_csv(
record,
[
"id",
],
"work_content.csv",
)
update.message.reply_document(document=open("work_content.csv", "rb"))
def check_log(update, context):
user = update.message.from_user
conn = create_connection()
rows = select_record(
conn,
"logbook",
LOG_COLUMN,
{"chat_id": user.id},
"ORDER BY timestamp DESC LIMIT 6",
)
rows = rows[-1::-1]
conn.close()
header_message = "Here is your recent log info.\n"
text_message = make_text_from_logs(rows, header_message)
reply_markdown(update, context, text_message)
def get_logs_of_the_day(the_date):
start_date = the_date
end_date = start_date + timedelta(1)
conn = create_connection("db.sqlite3")
rows = select_record(
conn,
"logbook",
LOG_COLUMN,
{},
f"strftime('%s', timestamp) \
BETWEEN strftime('%s', '{start_date}') AND strftime('%s', '{end_date}')",
)
header_message = f"{start_date.isoformat()}'s Logging\n"
text_message = make_text_from_logs(rows, header_message)
return text_message
def get_logs_of_today():
start_date = date.today()
end_date = start_date + timedelta(1)
conn = create_connection("db.sqlite3")
rows = select_record(
conn,
"logbook",
LOG_COLUMN,
{},
f"strftime('%s', timestamp) \
BETWEEN strftime('%s', '{start_date}') AND strftime('%s', '{end_date}') ORDER BY first_name",
)
header_message = f"Today's Logging\n({date.today().isoformat()})"
text_message = make_text_from_logs(rows, header_message)
return text_message
def override_log_and_ask_work_type(update, context):
choices = {"REMOVE SIGN IN LOG": True, "NO": False}
answer = choices.get(update.message.text)
if answer:
log_id = context.user_data.get("log_id")
conn = create_connection()
delete_record(conn, "logbook", {"id": log_id})
conn.close()
text_message = f"Log No. {log_id} has been Deleted\n"
reply_markdown(update, context, text_message)
else:
text_message = "process has been stoped. The log has not been deleted."
reply_markdown(update, context, text_message)
return ConversationHandler.END
log_id = post_basic_user_data(update, context, "signing in")
context.user_data["log_id"] = log_id
return ask_sub_category(update, context)
def get_logbook(update, context):
""" Send a file when comamnd /signbook is issued"""
conn = create_connection()
record = select_record(conn, "logbook", ["*"], {})
conn.close()
header = [
"id",
"chat_id",
"first_name",
"last_name",
"datetime",
"category",
"sub_category",
"longitude",
"latitude",
"remarks",
"confirmation",
]
write_csv(record, header, "signing.csv")
update.message.reply_document(document=open("signing.csv", "rb"))
def ask_confirmation_of_removal(update, context):
log_id = update.message.text
try:
int(log_id)
context.user_data["remove_log_id"] = log_id
keyboard = [["YES", "NO"]]
conn = create_connection()
row = select_record(conn, "logbook", LOG_COLUMN, {"id": log_id})
conn.close()
header_message = f"Do you really want to do remove log No.{log_id}?\n"
text_message = make_text_from_logs(row, header_message)
reply_markdown(update, context, text_message, keyboard)
return HANDLE_LOG_DELETE
except ValueError:
text_message = "Please. Send me numbers only."
reply_markdown(update, context, text_message)
return HANDLE_DELETE_LOG_ID
def start_edit(update, context):
choices = {"YES": True, "NO": False}
answer = choices.get(update.message.text)
if answer:
log_id = context.user_data.get("log_id")
conn = create_connection()
rows = select_record(conn, "logbook", ("category", "history"),
{"id": log_id})
category = rows[0][0]
history = rows[0][1] if rows[0][1] else ""
history += f"Edited at {update.message.date} for {category}\n"
update_record(conn, "logbook", {"history": history}, log_id)
conn.close()
keyboard_dict = {
"signing in": [
["Office", "Home"],
],
"signing out": [[
"I worked at Office",
"I would like to report because I worked at home",
]],
"getting back": [
["Without any member of KOICA", "With KOICA Colleagues"],
],
}
status_dict = {
"signing in": "SIGN_IN",
"signing out": "SIGN_OUT",
"getting back": "GET_BACK",
}
context.user_data["status"] = status_dict.get(category)
text_message = f"start to edit Log No.{log_id} by press button\n"
reply_markdown(update, context, text_message,
keyboard_dict.get(category))
else:
text_message = "process has been stoped. The log has not been deleted."
reply_markdown(update, context, text_message)
context.user_data.clear()
return ConversationHandler.END
def post_basic_user_data(update, context, category):
"""
return: log_id
"""
user = update.message.from_user
basic_user_data = {
"chat_id": user.id,
"first_name": user.first_name,
"last_name": user.last_name,
"timestamp": update.message.date.astimezone(pytz.timezone("Africa/Douala")),
"category": category,
}
conn = create_connection("db.sqlite3")
log_id = insert_record(conn, "logbook", basic_user_data)
conn.close()
for key in basic_user_data:
context.user_data[key] = basic_user_data[key]
return log_id
def ask_confirmation_of_edit(update, context):
log_id = update.message.text
try:
int(log_id)
keyboard = [["YES", "NO"]]
conn = create_connection()
rows = select_record(conn, "logbook", LOG_COLUMN, {"id": log_id})
conn.close()
header_message = f"Do you really want to do edit log No.{log_id}?\n"
text_message = make_text_from_logs(rows, header_message)
reply_markdown(update, context, text_message, keyboard)
chat_id = rows[0][1]
set_context(update, context, {"log_id": log_id, "chat_id": chat_id})
return ANSWER_CONFIRMATION
except ValueError:
text_message = "Please. Send me numbers only."
reply_markdown(update, context, text_message)
return ANSWER_LOG_ID
def ask_log_id_to_edit(update, context):
""" """
user = update.message.from_user
conn = create_connection()
rows = select_record(
conn,
"logbook",
LOG_COLUMN,
{"chat_id": user.id},
"ORDER BY timestamp DESC LIMIT 3",
)
rows = rows[-1::-1]
conn.close()
text_message = "Which log do you want to edit?\nPlease send me the log number."
text_message = make_text_from_logs(rows, text_message)
reply_markdown(update, context, text_message)
context.user_data["status"] = "EDIT_LOG_ID"
return ANSWER_LOG_ID
def put_confirmation(update, context):
conn = create_connection()
record = {"confirmation": "user confirmed"}
update_record(conn, "logbook", record, context.user_data.get("log_id"))
def put_sub_category(log_id, sub_category):
conn = create_connection("db.sqlite3")
record = {"sub_category": sub_category}
update_record(conn, "logbook", record, log_id)
conn.close()
