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