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()