def get_all_waiting_requests(): check_authentication() sqlite_connection = sqlite3.connect("MAIL_DB.db") select_query = """SELECT waiting_id FROM waiting_list;""" db_all_requests = sqlite_connection.execute(select_query) counter = 0 for req in db_all_requests: counter += 1 sqlite_connection.close() return counter
def outbox_to_trash(mail_id): if check_authentication() == False: return redirect(url_for('forms_bp.login_form_page')) sqlite_connection = sqlite3.connect("MAIL_DB.db") check_query = """SELECT multiple_receivers FROM mail WHERE mail_id = ?;""" db_check_result = sqlite_connection.execute(check_query, (mail_id,)) check_multiple = False for check in db_check_result: check_multiple = check[0] break if check_multiple: select_mail_date = """SELECT mail_date FROM mail WHERE mail_id = ?;""" db_mail_date = sqlite_connection.execute(select_mail_date, (mail_id,)) mail_date = 0 for date in db_mail_date: mail_date = date[0] break select_sender_all_mails = """SELECT mail_id FROM mail WHERE sender_id = ? AND mail_date = ?;""" db_no_of_mails = sqlite_connection.execute( select_sender_all_mails, (get_user_id(session["email"]), mail_date)).fetchall() update_mail_to_trash_query = """UPDATE mail SET sender_trashed = ? WHERE sender_id = ? AND mail_date = ?;""" for i in range(len(db_no_of_mails)): sqlite_connection.execute( update_mail_to_trash_query, (1, get_user_id(session["email"]), mail_date)) else: update_mail_query = """UPDATE mail SET sender_trashed = ? WHERE mail_id = ?;""" sqlite_connection.execute(update_mail_query, (1, mail_id)) sqlite_connection.commit() sqlite_connection.close() return redirect(url_for("user_mail_bp.see_outbox"))
def admin_page(): if check_authentication() == False: return redirect(url_for('forms_bp.login_form_page')) sqlite_connection = sqlite3.connect("MAIL_DB.db") check_unauthorized_query = """SELECT user_email FROM unauthorize;""" db_unauthorized_mails = sqlite_connection.execute(check_unauthorized_query) un_authorized_mails = [] for mail in db_unauthorized_mails: un_authorized_mails.append(mail[0]) select_all_users = """SELECT user_id, user_name, user_email, user_active FROM user;""" db_all_users = sqlite_connection.execute(select_all_users) user_data = {} all_users = [] for user in db_all_users: user_data["user_id"] = user[0] user_data["user_name"] = user[1] user_data["user_email"] = user[2] user_data["user_status"] = user[3] user_data["user_authorized"] = True for mail in un_authorized_mails: if mail == user[2]: user_data["user_authorized"] = False break all_users.append(user_data) user_data = {} waiting_requests = get_all_waiting_requests() return render_template("admin_templates/admin.html", all_users=all_users, waiting_requests=waiting_requests)
def recycle_trash(mail_id, mail_identity, outbox_status): if check_authentication() == False: return redirect(url_for('forms_bp.login_form_page')) sqlite_connection = sqlite3.connect("MAIL_DB.db") if mail_identity: recycle_mail_query = """UPDATE mail SET receiver_trashed = ? WHERE mail_id = ?;""" sqlite_connection.execute(recycle_mail_query, (0, mail_id)) else: if outbox_status == True: recycle_mail_query = """UPDATE mail SET sender_trashed = ? WHERE mail_id = ?;""" sqlite_connection.execute(recycle_mail_query, (0, mail_id)) else: select_mail_date = """SELECT mail_date FROM mail WHERE mail_id = ?;""" db_mail_date = sqlite_connection.execute( select_mail_date, (mail_id,)) mail_date = 0 for date in db_mail_date: mail_date = date[0] break recycle_mails_query = """UPDATE mail SET sender_trashed = ? WHERE mail_date = ?;""" sqlite_connection.execute(recycle_mails_query, (0, mail_date)) sqlite_connection.commit() sqlite_connection.close() return redirect(url_for("user_mail_bp.trash"))
def view_profile(): if check_authentication() == False: return redirect(url_for('forms_bp.login_form_page')) user_email = session['email'] sqlite_connection = sqlite3.connect('Mail_DB.db') select_user_query = """SELECT user_id, user_name, user_email, user_gender ,user_date_of_birth, user_city, user_country, user_contact , user_account_creation_date FROM user WHERE user_email = ?;""" select_user_query_data = (user_email,) user_data = sqlite_connection.execute( select_user_query, select_user_query_data) user_record = {} for data in user_data: user_record['ID'] = data[0] user_record['NAME'] = data[1] user_record['EMAIL'] = data[2] user_record['GENDER'] = data[3] user_record['DATE_OF_BIRTH'] = data[4] user_record['CITY'] = data[5] user_record['COUNTRY'] = data[6] user_record['CONTACT'] = data[7] user_record['ACCOUNT_CREATION_DATE'] = data[8] sqlite_connection.close() return render_template("User_Profile/ViewProfile.html", user=user_record, user_mail=session["email"])
def view_mail(mail_id): if check_authentication() == False: return redirect(url_for('forms_bp.login_form_page')) sqlite_connection = sqlite3.connect('MAIL_DB.db') select_mail_query = """SELECT sender_id, mail_subject, mail_body, mail_date, mail_id FROM mail WHERE mail_id = ?;""" mail_record = sqlite_connection.execute(select_mail_query, (mail_id,)) mail_data = {} for data in mail_record: mail_data['sender_mail'] = get_user_email(data[0]) mail_data['mail_subject'] = data[1] mail_data['mail_body'] = data[2] mail_data['mail_date'] = remove_milli_seconds(data[3]) mail_data['mail_id'] = data[4] if check_attachment_exist(mail_id): select_attachment_query = """SELECT attachment_file FROM attachment WHERE mail_id = ?;""" attach_record = sqlite_connection.execute( select_attachment_query, (mail_id,)) attachments = [] for file in attach_record: attachments.append(file[0]) mail_data['attachments'] = attachments update_query = """UPDATE mail SET mail_seen = ? WHERE mail_id = ?;""" update_query_data = (1, mail_id) sqlite_connection.execute(update_query, update_query_data) sqlite_connection.commit() sqlite_connection.close() return render_template('User_Mail/view_mail.html', mail=mail_data, user_mail=session["email"])
def view_file(file_name): if check_authentication() == False: return redirect(url_for('forms_bp.login_form_page')) try: return send_from_directory(current_app.config['USER_ATTACHMENTS'], filename=secure_filename(file_name), as_attachment=False) except FileNotFoundError: abort(404)
def see_all_mail(): if check_authentication() == False: return redirect(url_for('forms_bp.login_form_page')) inbox = return_inbox(get_user_id(session["email"])) outbox = return_outbox(get_user_id(session["email"])) all_mail = sort_mails(inbox + outbox) return render_template("User_Mail/view_all_mail.html", all_mail=all_mail, user_email=session["email"])
def send_multi_users(): if check_authentication() == False: return redirect(url_for('forms_bp.login_form_page')) allReceiversMails = json.loads(request.form["allMail"]) if(len(allReceiversMails) == 1): return redirect(url_for("user_mail_bp.compose_to_email", user_mail=allReceiversMails[0])) return render_template("User_Mail/composeEmail.html", userName=session['email'], to=allReceiversMails, max_length=current_app.config["MAX_CONTENT_LENGTH"])
def remove_request(request_id): if check_authentication() == False: return redirect(url_for('forms_bp.login_form_page')) sqlite_connection = sqlite3.connect("MAIL_DB.db") delete_query = "DELETE FROM waiting_list WHERE waiting_id = ?;" sqlite_connection.execute(delete_query, request_id) sqlite_connection.commit() sqlite_connection.close() return redirect(url_for('admin_bp.waiting_requests'))
def view_all_users(): if check_authentication() == False: return redirect(url_for('forms_bp.login_form_page')) sqlite_connection = sqlite3.connect('Mail_DB.db') select_allusers_query = """SELECT user_id , user_name, user_email FROM user WHERE user_id != ?;""" allUsers = sqlite_connection.execute( select_allusers_query, (get_user_id(session["email"]),)).fetchall() sqlite_connection.close() return render_template("User_Profile/allUsers.html", allUsers=allUsers, user_mail=session["email"])
def inbox_to_trash(mail_id): if check_authentication() == False: return redirect(url_for('forms_bp.login_form_page')) sqlite_connection = sqlite3.connect("MAIL_DB.db") update_query = """UPDATE mail SET receiver_trashed = ? WHERE mail_id = ?;""" sqlite_connection.execute(update_query, (1, mail_id)) sqlite_connection.commit() sqlite_connection.close() return redirect(url_for("user_mail_bp.see_inbox"))
def user_activity(): if check_authentication() == False: return redirect(url_for('forms_bp.login_form_page')) user_id = get_user_id(session["email"]) sqlite_connection = sqlite3.connect("MAIL_DB.db") update_active_query = """UPDATE user SET user_active = ? WHERE user_id = ?;""" sqlite_connection.execute(update_active_query, (1, user_id)) sqlite_connection.commit() sqlite_connection.close() return jsonify({"active": True})
def admin_authorize(user_mail): if check_authentication() == False: return redirect(url_for('forms_bp.login_form_page')) sqlite_connection = sqlite3.connect("MAIL_DB.db") remove_mail_query = """DELETE FROM unauthorize WHERE user_email = ?;""" sqlite_connection.execute(remove_mail_query, (user_mail, )) sqlite_connection.commit() sqlite_connection.close() flash("authorized successfully", "authorize") return redirect(url_for("admin_bp.admin_page"))
def admin_unauthorize(user_mail): if check_authentication() == False: return redirect(url_for('forms_bp.login_form_page')) sqlite_connection = sqlite3.connect("MAIL_DB.db") insert_mail_query = """INSERT INTO unauthorize (user_email) VALUES(?);""" sqlite_connection.execute(insert_mail_query, (user_mail, )) sqlite_connection.commit() sqlite_connection.close() flash("unauthorized successfully", "unauthorize") return redirect(url_for("admin_bp.admin_page"))
def logout(): if check_authentication() == False: return redirect(url_for('forms_bp.login_form_page')) sqlite_connection = sqlite3.connect('Mail_DB.db') user_update_query = """UPDATE user SET user_active = ? WHERE user_email = ?;""" user_update_query_data = (0, session["email"]) sqlite_connection.execute(user_update_query, user_update_query_data) sqlite_connection.commit() sqlite_connection.close() session.pop('email', None) return redirect(url_for("forms_bp.login_form_page"))
def get_mail_body(mail_id): if check_authentication() == False: return redirect(url_for('forms_bp.login_form_page')) sqlite_connection = sqlite3.connect("MAIL_DB.db") select_mail_query = "SELECT mail_body FROM mail WHERE mail_id = ?;" db_output = sqlite_connection.execute(select_mail_query, (mail_id,)) mail_body = "" for mail in db_output: mail_body = mail[0] sqlite_connection.close() return jsonify(mail_body)
def unseen_inbox(user_mail): if check_authentication() == False: return redirect(url_for('forms_bp.login_form_page')) sqlite_connection = sqlite3.connect("MAIL_DB.db") select_unseen_mail = """SELECT mail_id FROM mail WHERE receiver_id = ? AND mail_seen = ? AND receiver_trashed = ?;""" values = (get_user_id(user_mail), 0, 0) db_unseen_mails = sqlite_connection.execute(select_unseen_mail, values) counter = 0 for mail in db_unseen_mails: counter += 1 sqlite_connection.close() return jsonify({"unseen_mails": counter})
def see_inbox(): if check_authentication() == False: return redirect(url_for('forms_bp.login_form_page')) inbox = return_inbox(get_user_id(session["email"])) sqlite_connection = sqlite3.connect("MAIL_DB.db") select_name = "SELECT user_name FROM user WHERE user_email = ?;" db_user_name = sqlite_connection.execute(select_name, (session["email"],)) user_name = "" for user in db_user_name: user_name = user[0] sqlite_connection.close() return render_template("User_Mail/inbox.html", inbox_mails=inbox, user_mail=session["email"], user_name=user_name)
def delete_account(): if check_authentication() == False: return redirect(url_for('forms_bp.login_form_page')) sqlite_connection = sqlite3.connect('Mail_DB.db') user_delete_query = """DELETE FROM user WHERE user_email = ? AND user_password = ?;""" sqlite_connection.execute( user_delete_query, (session['email'], session['password'])) sqlite_connection.commit() sqlite_connection.close() session.pop('email', None) session.pop('password', None) return "<h1>Your Account is Deleted successfully</h1>"
def contact_us(): if check_authentication() == False: return redirect(url_for('forms_bp.login_form_page')) sqlite_connection = sqlite3.connect("MAIL_DB.db") select_query = """SELECT admin_email, admin_phone FROM admin ORDER BY admin_id ASC;""" db_admins = sqlite_connection.execute(select_query) all_admins = [] admin_data = {} for admin in db_admins: admin_data["admin_email"] = admin[0] admin_data["admin_phone"] = admin[1] all_admins.append(admin_data) admin_data = {} return render_template("User_Profile/contactUs.html", all_admins=all_admins, user_mail=session["email"])
def file_extensions_validation(): if check_authentication() == False: return redirect(url_for('forms_bp.login_form_page')) file_extensions = json.loads(request.args.get("fileExtensions")) all_files = [] file_data = {} for file in file_extensions: file_data["file_name"] = file if validate_file_extension(file) == False: file_data["valid"] = False else: file_data["valid"] = True all_files.append(file_data) file_data = {} return jsonify(all_files)
def all_mail_to_trash(mail_id, trash_identity): if check_authentication() == False: return redirect(url_for('forms_bp.login_form_page')) sqlite_connection = sqlite3.connect('MAIL_DB.db') update_query = "" if trash_identity == 1: update_query = """UPDATE mail SET receiver_trashed = ? WHERE mail_id = ?;""" else: update_query = """UPDATE mail SET sender_trashed = ? WHERE mail_id = ?;""" sqlite_connection.execute(update_query, (1, mail_id)) sqlite_connection.commit() sqlite_connection.close() return redirect(url_for("user_mail_bp.see_all_mail"))
def sending_email(): if check_authentication() == False: return redirect(url_for('forms_bp.login_form_page')) sender_mail = session['email'] receiver_mail = request.form['recievers'] all_receivers = receiver_mail.split(" ") mail_subject = request.form['subject'] mail_body = request.form['mailInfo'].strip() mail_date = datetime.now() sender_id = get_user_id(session['email']) sqlite_connection = sqlite3.connect('MAIL_DB.db') insert_mail_query = """INSERT INTO mail (sender_id, receiver_id, mail_subject, mail_body, mail_date, multiple_receivers) VALUES(?, ?, ?, ?, ?, ?);""" multiple_receivers = 1 if len(all_receivers) == 1: multiple_receivers = 0 for receiver in all_receivers: insert_mail_query_data = (sender_id, get_user_id( receiver), mail_subject, mail_body, mail_date, multiple_receivers) sqlite_connection.execute(insert_mail_query, insert_mail_query_data) uploaded_files = request.files.getlist("user_files") if check_files_numbers(uploaded_files): if check_file_extension(uploaded_files) == False: flash('Not Allowed Extension', "danger") return redirect(url_for('user_mail_bp.compose_email')) os.makedirs(current_app.config['USER_ATTACHMENTS'], exist_ok=True) for user_file in uploaded_files: file_name = secure_filename(user_file.filename) user_file.save(os.path.join( current_app.config['USER_ATTACHMENTS'], file_name)) save_attachment_query = """INSERT INTO attachment(mail_id, attachment_file) VALUES(?, ?);""" sqlite_select_query = """SELECT mail_id FROM mail WHERE sender_id = ? AND mail_date = ?;""" sqlite_select_query_data = (sender_id, mail_date) mail_ids = sqlite_connection.execute( sqlite_select_query, sqlite_select_query_data) for mail_id in mail_ids: sqlite_connection.execute( save_attachment_query, (mail_id[0], user_file.filename)) sqlite_connection.commit() sqlite_connection.close() return redirect(url_for('user_mail_bp.compose_email'))
def email_validator(input_mail): if check_authentication() == False: return redirect(url_for('forms_bp.login_form_page')) if "@" not in input_mail: input_mail = input_mail + "@" sqlite_connection = sqlite3.connect("MAIL_DB.db") select_all_mails_query = """SELECT admin_email FROM admin;""" db_all_mails = sqlite_connection.execute(select_all_mails_query) for mail in db_all_mails: if input_mail.split("@")[0] == mail[0].split("@")[0]: response_message = { "message": "This mail is taken", "valid": False } return jsonify(response_message) response_message = {"message": "valid mail name", "valid": True} return jsonify(response_message)
def edit_profile(): if check_authentication() == False: return redirect(url_for('forms_bp.login_form_page')) new_name = request.form['user_name'] new_dateOfBirth = request.form['user_birthDate'] new_city = request.form['user_city'] new_country = request.form['user_country'] sqlite_connection = sqlite3.connect('Mail_DB.db') user_update_query = """UPDATE user SET user_name = ?, user_date_of_birth = ?, user_city = ?, user_country = ? WHERE user_email = ?;""" user_update_query_data = ( new_name, new_dateOfBirth, new_city, new_country, session['email']) sqlite_connection.execute(user_update_query, user_update_query_data) sqlite_connection.commit() sqlite_connection.close() flash("Successfully!!!", "success") return redirect(url_for("user_profile_bp.edit_profile_form"))
def edit_profile_form(): if check_authentication() == False: return redirect(url_for('forms_bp.login_form_page')) sqlite_connection = sqlite3.connect('Mail_DB.db') select_user_query = """SELECT * FROM user WHERE user_email = ?;""" select_user_query_data = (session['email'],) user_data = sqlite_connection.execute( select_user_query, select_user_query_data) user_record = {} for data in user_data: user_record['NAME'] = data[1] user_record['EMAIL'] = data[2] user_record['DATE_OF_BIRTH'] = data[5] user_record['CITY'] = data[6] user_record['COUNTRY'] = data[7] user_record['CONTACT'] = data[8] user_record['ACCOUNT_CREATRION_DATE'] = data[9] sqlite_connection.close() return render_template('User_Profile/editProfileForm.html', user=user_record, user_mail=session["email"])
def add_admin(): if check_authentication() == False: return redirect(url_for('forms_bp.login_form_page')) admin_mail = request.form.get("adminMail") admin_password = request.form.get("adminPassword") admin_phone = request.form.get("adminPhone") admin_password = bcrypt.generate_password_hash(admin_password).decode( "UTF-8") sqlite_connection = sqlite3.connect("MAIL_DB.db") add_admin_query = """INSERT INTO admin (admin_email, admin_password, admin_phone) VALUES(?, ?, ?);""" sqlite_connection.execute(add_admin_query, (admin_mail, admin_password, admin_phone)) add_user_query = """INSERT INTO user (user_name, user_email, user_password, user_gender, user_date_of_birth, user_contact, user_account_creation_date, user_city, user_country) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?);""" add_user_values = ("admin", admin_mail, admin_password, "other", "none", admin_phone, datetime.now(), "none", "none") sqlite_connection.execute(add_user_query, add_user_values) sqlite_connection.commit() sqlite_connection.close() return redirect(url_for("admin_bp.admin_page"))
def waiting_requests(): if check_authentication() == False: return redirect(url_for('forms_bp.login_form_page')) sqlite_connection = sqlite3.connect("MAIL_DB.db") select_requests_query = """SELECT * FROM waiting_list;""" db_waiting_requests = sqlite_connection.execute(select_requests_query) all_requests = [] request_data = {} for request in db_waiting_requests: request_data["user_id"] = request[0] request_data["user_name"] = request[1] request_data["user_email"] = request[2] request_data["user_gender"] = request[4] request_data["user_date_of_birth"] = request[5] request_data["user_city"] = request[6] request_data["user_country"] = request[7] request_data["user_contact"] = request[8] all_requests.append(request_data) request_data = {} sqlite_connection.close() return render_template("admin_templates/waitingRequests.html", all_requests=all_requests)
def trash(): if check_authentication() == False: return redirect(url_for('forms_bp.login_form_page')) sqlite_connection = sqlite3.connect("MAIL_DB.db") user_id = get_user_id(session["email"]) select_inbox_trash = """SELECT mail_id, sender_id, mail_subject, mail_date FROM mail WHERE receiver_id = ? AND receiver_trashed = ?;""" db_inbox_trash = sqlite_connection.execute( select_inbox_trash, (user_id, 1)) mail_data = {} inbox_trash = [] for mail in db_inbox_trash: mail_data["mail_id"] = mail[0] mail_data["sender_mail"] = get_user_email(mail[1]) mail_data["mail_subject"] = mail[2] mail_data["mail_date"] = remove_milli_seconds(mail[3]) mail_data["mail_identity"] = True mail_data["outbox_status"] = False inbox_trash.append(mail_data) mail_data = {} select_outbox_single_trash = """SELECT mail_id, sender_id, mail_subject, mail_date FROM mail WHERE sender_id = ? AND sender_trashed = ? AND multiple_receivers = ?;""" db_outbox_single_trash = sqlite_connection.execute( select_outbox_single_trash, (user_id, 1, 0)) outbox_single_trash = [] for mail in db_outbox_single_trash: mail_data["mail_id"] = mail[0] mail_data["sender_mail"] = get_user_email(mail[1]) mail_data["mail_subject"] = mail[2] mail_data["mail_date"] = remove_milli_seconds(mail[3]) mail_data["mail_identity"] = False mail_data["outbox_status"] = True outbox_single_trash.append(mail_data) mail_data = {} select_distinct_mail_dates = """SELECT DISTINCT mail_date FROM mail WHERE sender_id = ? AND sender_trashed = ? AND multiple_receivers = ?;""" db_distinct_dates = sqlite_connection.execute( select_distinct_mail_dates, (user_id, 1, 1)) outbox_multiple_trash = [] for mail_date in db_distinct_dates: select_multiple_trash_mail_query = """SELECT mail_id, sender_id, mail_subject, mail_date FROM mail WHERE sender_id = ? AND sender_trashed = ? AND multiple_receivers = ? AND mail_date = ?;""" db_multiple_trash_mail = sqlite_connection.execute( select_multiple_trash_mail_query, (user_id, 1, 1, mail_date[0])) for mail in db_multiple_trash_mail: mail_data["mail_id"] = mail[0] mail_data["sender_mail"] = "Multiple Receivers" mail_data["mail_subject"] = mail[2] mail_data["mail_date"] = remove_milli_seconds(mail[3]) mail_data["mail_identity"] = False mail_data["outbox_status"] = True + True outbox_multiple_trash.append(mail_data) mail_data = {} break all_mail = sort_mails_by_date( inbox_trash + outbox_single_trash + outbox_multiple_trash) sqlite_connection.close() return render_template("User_Mail/view_trash.html", all_mail=all_mail, user_mail=session["email"])