def validate_data_using_union_view(mail: str, pwd: str) -> (bool, int, int): """ :param pwd The given password. This funtion verifies it using the sotred hash-value :param mail The input mail with name=mail of the sign-up-form :rtype: (bool, int, int) True: Login-Success The first int is the id of the company/influencer, the second int specifies the type: 0: Influencer, 1: company OR returns a (bool, str). The str is the error-string which tells the reason why the login wasnt successful """ sql_parameterized_statement = """SELECT IDENTIFIER, PWD_HASH, KIND, CONFIRMED FROM sign_up_view WHERE MAIL=%s;""" dbconnection = get_database_connection() cursor = dbconnection.cursor() cursor.execute(sql_parameterized_statement, (mail, )) try: result = cursor.fetchall() cursor.close() dbconnection.close() try: if verify_password(pwd, result[0][1]): if result[0][3] == 1: return True, result[0][0], result[0][2] else: return False, "EMail-Addresse noch nicht bestätigt." # ToDo: Language-specific-error-message-implementation is missing >> Add it here using the lang_abbr as third param else: return False, "Passwort ist nicht korrekt." # ToDo: see above (lang-branching in error-msg) except IndexError: return False, "Es exisitert kein Konto für die gegebene EMail-Adresse." # ToDo: see above (lang-branching in error-msg) except mysql.connector.errors.DataError: return False, "Error: Kontaktieren Sie bitte den technischen Support." # ToDo: see above (lang-branching in error-msg)
def get_data_of_invoice_with_uuid(uuid: str) -> dict: """ :param uuid: :return: """ dbconnection = get_database_connection() cursor = dbconnection.cursor() print("The UUID is. >>" + uuid) cursor.execute( """ SELECT invoice_number_asc, DATE(booked_date), booked_package_description, booked_package_duration_in_month, booked_package_total_amount, company_name, contact_person, street_house_number, postcode, place, language_abbr FROM invoice_data JOIN company c on invoice_data.company_identifier = c.company_identifier WHERE token = %s """, (uuid, )) print(cursor.statement) keys = cursor.column_names result = cursor.fetchall()[0] if result: return_dict = {} for i in range(0, len(keys)): return_dict[keys[i]] = result[i] else: print(result) cursor.close() dbconnection.close() return return_dict
def create_invoice_db_entry(customer_id: int, booked_package_description: str, booked_package_duration_in_months: int, booked_total_amount: float, token: str) -> None: """ This function creates an db-entry that contains all neceesary information about the package-booking to render the invoice in future. :param customer_id: required to join the company table with the invoice-data (to get all information for pdf-rendering like the address or USt.-Id of the customer (in this case only companies)) :param booked_package_description: This is a part of the description which is shown on the invoice :param booked_package_duration_in_months: the description on the invoice is composed out od this field-entry and the package name :param booked_total_amount: the total_amount is also used to compute the amount excluding taxes and for computing the tax-amount :return: / """ dbconnection = get_database_connection() cursor = dbconnection.cursor() print(token) cursor.execute( """ INSERT INTO invoice_data(token, company_identifier, booked_package_description, booked_package_duration_in_month, booked_package_total_amount) VALUES(%s, %s, %s, %s, %s) """, (token, customer_id, booked_package_description, booked_package_duration_in_months, booked_total_amount)) dbconnection.commit() cursor.close() dbconnection.close()
def get_all_reported_profiles() -> list: """ :return: List that contains dictionaries as datastructures for each report-entry od the db """ dbconnection = get_database_connection() cursor = dbconnection.cursor() cursor.execute(""" SELECT * FROM reported_influencers JOIN report_reasons rr on reported_influencers.report_reason_identifier = rr.report_reason_identifier JOIN influencer i on reported_influencers.influencer_identifier = i.influencer_identifier WHERE ignored = 0;""") entries = cursor.fetchall() keys = cursor.column_names cursor.close() dbconnection.close() return_list = [] for entry in entries: temp_dict = {} for index in range(0, len(keys)): temp_dict[keys[index]] = entry[index] return_list.append(temp_dict) return return_list
def get_signup_data_today_influencer(): dbconnection = get_database_connection() cursor = dbconnection.cursor() cursor.execute(""" SELECT HOUR(created_on), COUNT(*) FROM influencer WHERE DATE(created_on) = DATE(NOW()) GROUP BY HOUR(created_on); """) results = cursor.fetchall() if len(results) == 0 or results is None: return_dict = {} for i in range(0, 24): return_dict[i] = 0 temp_dict = {} for entry in results: temp_dict[str(entry[0])] = entry[1] return_dict = {} for i in range(0, 24): return_dict[i] = temp_dict.get(str(i), 0) return return_dict
def report_influencer(influencer_identifier: int, contact_mail: str, reason_identifier: str, remark: str) -> None: """ :param influencer_identifier: :param contact_mail: :param reason_identifier: :param remark: :return: """ dbconnection = get_database_connection() cursor = dbconnection.cursor() try: cursor.execute( """ INSERT INTO reported_influencers(influencer_identifier, report_reason_identifier, contact_mail, remark) VALUES (%s, %s, %s, %s); """, (influencer_identifier, reason_identifier, contact_mail, remark)) # ToDo: Alter the catching-conditions to catch just MySQL-Errors except: print("ERROR DURING INFLUENCER_PROFILE_REPORT") dbconnection.commit() cursor.close() dbconnection.close()
def set_new_password(pwd, token): """ :param pwd: :param token: :return: """ dbconnection = get_database_connection() cursor = dbconnection.cursor() cursor.execute( """ UPDATE influencer SET pwd_hash = %s WHERE email IN (SELECT email FROM pwd_reset_tokens WHERE token = %s);""", (hash_password(pwd), token)) print(cursor.statement) dbconnection.commit() cursor.close() cursor = dbconnection.cursor() cursor.execute( """ UPDATE company SET pwd_hash = %s WHERE contact_email IN (SELECT email FROM pwd_reset_tokens WHERE token = %s);""", (hash_password(pwd), token)) dbconnection.commit() cursor.close() dbconnection.close() return True
def set_confirm_status_to_true(key: str) -> bool: """ :param key: :return: """ dbconnection = get_database_connection() cursor = dbconnection.cursor() cursor.execute( """ UPDATE influencer SET confirmed = True WHERE email IN (SELECT email FROM confirm_keys WHERE token = %s );""", (key, )) print(cursor.statement) cursor.execute( """ UPDATE company SET confirmed = True WHERE contact_email IN (SELECT email FROM confirm_keys WHERE token = %s );""", (key, )) print(cursor.statement) dbconnection.commit() cursor.close() dbconnection.close() return True
def get_campaigns_of_company_with_identifier(company_identifier: int) -> list: dbconn = get_database_connection() cursor = dbconn.cursor() cursor.execute( """ SELECT campaign_identifier, campaign_title FROM company_offers_campaign WHERE company_identifier = %s""", (company_identifier, )) entries = cursor.fetchall() keys = cursor.column_names return_list = [] for entry in entries: tmp_dict = {} for index in range(0, len(keys)): tmp_dict[keys[index]] = entry[index] return_list.append(tmp_dict) cursor.close() return return_list
def get_all_public_campaigns_that_fit_with(topics: list, channels: list) -> list: dbconnection = get_database_connection() cursor = dbconnection.cursor() sql_stmt = """ SELECT DISTINCT company_offers_campaign.campaign_identifier, campaign_title, campaign_description, company_offers_campaign.company_identifier, topic_identifier, company_name, contact_email, contact_person, published_on FROM company_offers_campaign JOIN topics on topic_identifier = topic LEFT OUTER JOIN public_campaign_channels pcc on company_offers_campaign.campaign_identifier = pcc.campaign_identifier LEFT OUTER JOIN company c on company_offers_campaign.company_identifier = c.company_identifier WHERE confirmed = 1 """ if topics is not None and len(topics) > 0: sql_stmt += """ and topic_identifier in """ + tuple_of_list( topics) # TODO: WARNING: RISKY! if channels is not None and len(channels) > 0: sql_stmt += """ and channel_identifier in """ + tuple_of_list( channels) # TODO: WARNING: RISKY! sql_stmt += " ORDER BY published_on DESC;" print(sql_stmt) cursor.execute(sql_stmt) keys = cursor.column_names entries = cursor.fetchall() results = [] if len(entries) > 0: for entry in entries: temp_dict = {} for index in range(0, len(keys)): temp_dict[keys[index]] = entry[index] temp_dict["logo"] = get_logo_path_of_company_with_identifier( temp_dict["company_identifier"]) results.append(temp_dict) else: results = [] cursor.close() dbconnection.close() print(results) return results
def delete_stored_query(company_identifier: int, search_identifier: str): dbconnection = get_database_connection() cursor = dbconnection.cursor() cursor.execute( """DELETE FROM company_stores_search WHERE search_identifier = %s and company_identifier = %s;""", (search_identifier, company_identifier)) dbconnection.commit() cursor.close() dbconnection.close()
def delete_campaign_db(campaign_identifier: int) -> bool: dbconnection = get_database_connection() cursor = dbconnection.cursor() cursor.execute("""DELETE FROM campaign WHERE campaign_identifier=%s;""", (campaign_identifier, )) dbconnection.commit() cursor.close() dbconnection.close() return True
def edit_campaign(internal_identifier: int, name: str, description: str, owner: int) -> bool: sql = """UPDATE campaign SET description = %s, name = %s WHERE campaign_identifier= %s;""" dbconnection = get_database_connection() cursor = dbconnection.cursor() cursor.execute(sql, (description, name, internal_identifier)) dbconnection.commit() cursor.close() dbconnection.close() return True
def add_campaign(name: str, description: str, owner: int) -> bool: sql = """INSERT INTO campaign(company_identifier, name, description) VALUES (%s, %s, %s);""" dbconnection = get_database_connection() cursor = dbconnection.cursor() cursor.execute(sql, (owner, name, description)) dbconnection.commit() cursor.close() dbconnection.close() return True
def get_stored_queries_of_company(company_identifier: int) -> list: dbconnection = get_database_connection() cursor = dbconnection.cursor() cursor.execute( """SELECT search_identifier, timestamp, search_href, title FROM company_stores_search WHERE company_identifier = %s ORDER BY timestamp DESC;""", (company_identifier, )) result = cursor.fetchall() cursor.close() dbconnection.close() return result
def track_search(channel: str, package: str): try: dbconnection = get_database_connection() cursor = dbconnection.cursor() cursor.execute( """INSERT INTO searches(package, channel) VALUES (%s, %s);""", (package, channel)) dbconnection.commit() cursor.close() dbconnection.close() except: pass
def get_campaigns(user_id: int) -> list: dbconnection = get_database_connection() cursor = dbconnection.cursor() cursor.execute( """ SELECT campaign.campaign_identifier, company_identifier, name, description FROM campaign WHERE company_identifier = %s;""", (user_id, )) cursor.close() dbconnection.close() return parse_result(cursor.column_names, cursor.fetchall())
def ignore_reported_influencer_db_execution(report_identifier) -> bool: dbconnection = get_database_connection() cursor = dbconnection.cursor() cursor.execute( """ UPDATE reported_influencers SET ignored = 1 WHERE report_identifier = %s; """, (report_identifier, )) dbconnection.commit() cursor.close() dbconnection.close() return True
def delete_influencer_with_id(influencerIdentifier: int) -> bool: dbconnection = get_database_connection() cursor = dbconnection.cursor() cursor.execute( """ DELETE FROM influencer WHERE influencer_identifier = %s""", (influencerIdentifier, )) dbconnection.commit() cursor.close() dbconnection.close() return True
def delete_public_campaign_with_identifier(campaign_identifier: int, company_identifier: int) -> bool: dbconnection = get_database_connection() cursor = dbconnection.cursor() stmt = """DELETE FROM company_offers_campaign WHERE campaign_identifier = %s AND company_identifier = %s""" cursor.execute(stmt, (campaign_identifier, company_identifier)) dbconnection.commit() cursor.close() dbconnection.close() return True # How to check without any affected_rows method ?!
def create_user(lastName: str, firstName: str, mail: str, telephoneNumber: str, gender: int, birthyear: str, pwd: str, language_abbr: str) -> bool: """ This function creates user (only influencer, no companies) by validating the input and translate ids to texts (for example: gender ID) :param lastName the input of the sign-Up-form with name=last_name (required) :param firstName the input of the sign-Up-form with name=first_name (required) :param email the input of the sign-Up-form with name=mail (required, Primary Key) :param telephoneNumber the input of the sign-Up-form with name=phone_number :param gender the input of the sign-Up-form with name=gender (select) TODO: Check if the option-values of the select-picker are equal to this internal identifiers :param birthyear the input of the sign-Up-form with name=birthyear :param pwd the input of the sign-Up-form with name=pwd or name=pwd-confirm (confirm them before). The pwd-param mustn't be hashed before. :rtype: bool, that confirms if the user was successfully added to the database """ pwd_hash = hash_password(pwd) if check_if_email_already_taken(mail): raise Exception("email-address already taken") dbconnection = get_database_connection() cursor = dbconnection.cursor() sql_parameterized_query = """ INSERT INTO influencer(last_name, first_name, email, phone_number, gender, birthyear, pwd_hash, language_abbr) VALUES(%s, %s, %s, %s, %s, %s, %s, %s);""" gen = 'male' if gender == "1": gen = 'male' elif gender == "2": gen = 'female' elif gender == "3": gen = "d" else: raise IndexError("Unknown gender-index: " + gender) cursor.execute(sql_parameterized_query, (lastName, firstName, mail, telephoneNumber, gen, int(birthyear + "", 10), pwd_hash, language_abbr)) dbconnection.commit() cursor.close() dbconnection.close() send_double_opt_in_request(mail, firstName, 1, language=language_abbr) return True # TODO: check if the user really added successful!
def get_signup_data_sevendays_company(): dbconnection = get_database_connection() cursor = dbconnection.cursor() cursor.execute(""" SELECT DATE(created_on), COUNT(*) AS amount FROM company WHERE DATE(created_on) >= (DATE(NOW()) - INTERVAL 7 DAY) GROUP BY DATE(created_on) ORDER BY DATE(created_on) ASC; """) results = cursor.fetchall() keys = cursor.column_names cursor.close() dbconnection.close() if len(results) == 0: return None if len(results) == 0 or results is None: return [0, 0, 0, 0, 0, 0, 0] # Does this fix the issue? # temp = [] # # for i in range(0, len(results)): # temp_dict = {} # for k in range(0, len(keys)): # temp_dict[keys[k]] = results[i][k] # # temp.append(temp_dict) temp = {} for i in range(0, len(results)): temp[results[i][0]] = results[i][1] dates = [] for i in [7, 6, 5, 4, 3, 2, 1, 0]: dates.append(get_date_of_timedelta(i)) return_dict = {} for date in dates: return_dict[date] = temp.get(date, 0) return return_dict
def get_kpis() -> dict: dbconnection = get_database_connection() cursor = dbconnection.cursor() cursor.execute("""SELECT * FROM kpi_dashboard;""") entries = cursor.fetchall(); cursor.close() dbconnection.close() return_dict = {} for entry in entries: return_dict[entry[0]] = entry[1] return return_dict
def inner(mail, placeholder=None): """ :param mail: :param placeholder: :return: """ dbconnection = get_database_connection() cursor = dbconnection.cursor() function(mail, cursor) dbconnection.commit() cursor.close() dbconnection.close()
def unpin_user_from_campaign(influencer_identifier: int, campaign_identifier: int) -> bool: dbconnection = get_database_connection() cursor = dbconnection.cursor() cursor.execute( """DELETE FROM is_pinned_on_campaign WHERE campaign_identifier=%s and influencer_identifier = %s;""", (campaign_identifier, influencer_identifier)) dbconnection.commit() cursor.close() dbconnection.close() return True
def pin_influencer(campaign_identifier: int, influencer_identifier: int, remark: str) -> bool: sql = """ INSERT INTO is_pinned_on_campaign(influencer_identifier, campaign_identifier, remark) VALUES (%s, %s, %s) """ dbconnection = get_database_connection() cursor = dbconnection.cursor() cursor.execute(sql, (influencer_identifier, campaign_identifier, remark)) dbconnection.commit() cursor.close() dbconnection.close() return True
def store_search(search: str, title: str, company_identifier: int) -> bool: dbconnection = get_database_connection() cursor = dbconnection.cursor() try: cursor.execute( """INSERT INTO company_stores_search(company_identifier, search_href, title) VALUES (%s, %s, %s);""", (company_identifier, search.split("?")[1], title)) print("EXECUTEED") except IndexError: return False dbconnection.commit() cursor.close() dbconnection.close() return True
def delete_influencer_account_with_mail_address_from_database( mailaddress: str): """ :param mailaddress: :return: """ dbconnection = get_database_connection() cursor = dbconnection.cursor() cursor.execute("""DELETE FROM influencer WHERE email=%s""", (mailaddress, )) dbconnection.commit() cursor.close() dbconnection.close()
def create_company_profile(companyName: str, contact: str, contactMail: str, streetWithHouseNumber: str, postcode: str, place: str, pwd: str, ust_id: str, language_abbr: str) -> bool: """ This functions creates a profile for a company (specified by the passed params. :param companyName the input of the sign-Up-form with name=companyName :param contact the input of the sign-Up-form with name=contact :param contactMail the input of the sign-Up-form with name=contactMail (must be unique in the database [view=mail_addresses]) :param streetWithHouseNumber the input of the sign-Up-form with name=streetWithHouseNumber :param postcode the input of the sign-Up-form with name=postcode :param place the input of the sign-Up-form with name=place :param pwd the input of the sign-Up-form with name=pwd or pwd-confirm (Mustn't be hashed, but should be validated before profile-creation-call) :param ust_id 'Umsatzsteueridentifikationsnummer' for receipt and internal usage (not required) :rtype: bool, that confirms the sign-up (if it was successful) """ pwd_hash = hash_password(pwd) if check_if_email_already_taken(contactMail): raise Exception("email-address already taken") dbconnection = get_database_connection() cursor = dbconnection.cursor() sql_parameterized_query = """ INSERT INTO company(company_name, contact_person, contact_email, street_house_number, postcode, place, ust_id, pwd_hash, language_abbr) VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s);""" try: cursor.execute( sql_parameterized_query, (companyName, contact, contactMail, streetWithHouseNumber, postcode, place, ust_id, pwd_hash, language_abbr)) except (mysql.connector.errors.DataError): raise Exception("Invalid UST-ID") dbconnection.commit() cursor.close() dbconnection.close() send_double_opt_in_request(contactMail, contact, 2, language=language_abbr) return True # TODO: check if this is the correct way
def book_package(mail, package, duration=None): dbconnection = get_database_connection() cursor = dbconnection.cursor() if duration is not None: expr = calculate_expiration(duration) else: expr = None cursor.execute( """UPDATE company SET booked_package = %s, expire_date = %s WHERE contact_email = %s;""", (package, expr, mail), ) dbconnection.commit() cursor.close() dbconnection.close()