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 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 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_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 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 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 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 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 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