def get_permissions(usernames):
conn = connection()
cursor = conn.cursor()
if len(usernames) == 0:
sql_query = " SELECT USERID, NAME, COMPANY, PERMISSIONS FROM CONTACTS ORDER BY COMPANY ASC; "
try:
cursor.execute(sql_query)
conn.close()
except Exception as e:
conn.close()
print(e)
ids = []
names = []
companies = []
permissions = []
for USERID, NAME, COMPANY, PERMISSIONS in cursor.fetchall():
ids.append(USERID)
names.append(NAME)
companies.append(COMPANY)
permissions.append(PERMISSIONS)
return zip(ids, names, companies, permissions)
def get_team_members(ed):
conn = connection()
cursor = conn.cursor()
sql_query = "SELECT USERNAME FROM USERS WHERE TEAM = \"{TEAM}\" ORDER BY USERNAME ASC; ".format(
TEAM=ed)
if ed == "All":
sql_query = "SELECT USERNAME FROM USERS; "
try:
cursor.execute(sql_query)
conn.close()
except Exception as e:
conn.close()
print(e)
members = []
for USERNAME in cursor.fetchall():
members.append(USERNAME[0])
return members
def get_usernames(admins=True, second_years=True):
conn = connection()
cursor = conn.cursor()
if admins == False and second_years == True:
sql_query = " SELECT USERNAME FROM USERS WHERE ADMIN&1=0 ORDER BY USERNAME; "
elif admins == True and second_years == False:
sql_query = " SELECT USERNAME FROM USERS WHERE ADMIN&1=1 ORDER BY USERNAME; "
else:
sql_query = " SELECT USERNAME FROM USERS ORDER BY USERNAME; "
try:
cursor.execute(sql_query)
conn.close()
except Exception as e:
conn.close()
print(e)
usernames = []
for USERNAME in cursor.fetchall():
usernames.append(USERNAME[0])
return usernames
def set_autoincrement(num):
conn = connection()
cursor = conn.cursor()
sql_query = "ALTER TABLE USERS AUTO_INCREMENT = {NUM}; ".format(NUM=num)
try:
cursor.execute(sql_query)
conn.commit()
except Exception as e:
conn.rollback()
print(e)
conn.close()
def delete_existing_users():
conn = connection()
cursor = conn.cursor()
sql_query = " DELETE FROM USERS; "
try:
cursor.execute(sql_query)
conn.commit()
except Exception as e:
conn.rollback()
print(e)
conn.close()
def update_permissions(userid, permissions):
conn = connection()
cursor = conn.cursor()
sql_query = " UPDATE CONTACTS SET PERMISSIONS='{PERMISSIONS}' WHERE USERID={USERID}; ".format(
PERMISSIONS=permissions, USERID=userid)
try:
cursor.execute(sql_query)
conn.commit()
except Exception as e:
conn.rollback()
print(e)
conn.close()
def email_exists(email):
conn = connection()
cursor = conn.cursor()
sql_query = " SELECT EMAIL FROM CONTACTS WHERE EMAIL='{EMAIL}'; ".format(
EMAIL=email)
try:
cursor.execute(sql_query)
conn.close()
except:
conn.close()
for EMAIL in cursor.fetchall():
if EMAIL[0] == email:
return True
return False
def number_exists(number):
conn = connection()
cursor = conn.cursor()
sql_query = " SELECT MOBILE FROM CONTACTS WHERE MOBILE='{NUMBER}'; ".format(
NUMBER=number)
try:
cursor.execute(sql_query)
conn.close()
except:
conn.close()
for MOBILE in cursor.fetchall():
if MOBILE[0] == number:
return True
return False
def users_insert(name, admin, username, password, team):
conn = connection()
cursor = conn.cursor()
sql_query = " INSERT INTO USERS VALUES ( USERID, \"{NAME}\", {ADMIN}, \"{USERNAME}\", \"{PASSWORD}\", \"{TEAM}\" ); ".format(
NAME=name,
ADMIN=admin,
USERNAME=username,
PASSWORD=password,
TEAM=team)
try:
cursor.execute(sql_query)
conn.commit()
except:
conn.rollback()
conn.close()
def login(username, password):
conn = connection()
cursor = conn.cursor()
sql_query = " SELECT USERNAME FROM USERS WHERE USERNAME=\"{U}\" AND PASSWORD=\"{P}\"; ".format(
U=username, P=password)
try:
cursor.execute(sql_query)
conn.close()
except:
conn.close()
for USERNAME in cursor.fetchall():
if USERNAME[0] == username:
return True
return False
def is_admin(username):
conn = connection()
cursor = conn.cursor()
sql_query = " SELECT USERNAME FROM USERS WHERE ADMIN&1=1; "
try:
cursor.execute(sql_query)
conn.close()
except Exception as e:
conn.close()
print(e)
for USERNAME in cursor.fetchall():
if USERNAME[0] == username:
return True
return False
def update_users(userid, name, admin, username, password):
conn = connection()
cursor = conn.cursor()
sql_query = " UPDATE USERS SET NAME=\"{NAME}\", ADMIN={ADMIN}, USERNAME=\"{USERNAME}\", PASSWORD=\"{PASSWORD}\" WHERE USERID={USERID}; ".format(
NAME=name,
ADMIN=admin,
USERNAME=username,
PASSWORD=password,
USERID=userid)
try:
cursor.execute(sql_query)
conn.commit()
except Exception as e:
conn.rollback()
print(e)
conn.close()
def get_email(userid):
conn = connection()
cursor = conn.cursor()
sql_query = " SELECT EMAIL FROM CONTACTS WHERE USERID={ID}; ".format(
ID=userid)
try:
cursor.execute(sql_query)
conn.close()
except Exception as e:
conn.close()
print(e)
for EMAIL in cursor.fetchall():
return EMAIL[0]
#If nothing is gotten from the DB
return ""
def contacts_insert(name, company, mobile, email, address, current_user):
conn = connection()
cursor = conn.cursor()
sql_query = " INSERT INTO CONTACTS VALUES ( USERID, \"{NAME}\", \"{COMPANY}\", \"{MOBILE}\", \"{EMAIL}\", \"{ADDRESS}\", STATUS, HRCOUNT, \"{CURRENTUSER}\" ); ".format(
NAME=name,
COMPANY=company,
MOBILE=mobile,
EMAIL=email,
ADDRESS=address,
CURRENTUSER=current_user)
try:
cursor.execute(sql_query)
conn.commit()
except Exception as e:
conn.rollback()
print(e)
conn.close()
def get_mobile_number(userid):
conn = connection()
cursor = conn.cursor()
sql_query = " SELECT MOBILE FROM CONTACTS WHERE USERID={ID}; ".format(
ID=userid)
try:
cursor.execute(sql_query)
conn.close()
except Exception as e:
conn.close()
print(e)
for MOBILE in cursor.fetchall():
return MOBILE[0]
#If nothing is gotten from the DB
return 0
def get_statistics():
conn = connection()
cursor = conn.cursor()
sql_query = " SELECT STATUS, SUM(HRCOUNT) AS STATS FROM CONTACTS GROUP BY STATUS ORDER BY STATUS ASC; "
try:
cursor.execute(sql_query)
conn.close()
except Exception as e:
conn.close()
print(e)
statuses = []
statistics = []
for STATUS, STATS in cursor.fetchall():
statuses.append(STATUS)
statistics.append(STATS)
return zip(statuses, statistics)
def get_contacts(username):
conn = connection()
cursor = conn.cursor()
sql_query = " SELECT USERID, NAME, COMPANY, MOBILE, EMAIL, ADDRESS, STATUS, HRCOUNT FROM CONTACTS WHERE PERMISSIONS LIKE \"%{USER}%\"; ".format(
USER=username)
try:
cursor.execute(sql_query)
conn.close()
except Exception as e:
conn.close()
print(e)
ids = []
names = []
companies = []
mobiles = []
emails = []
addresses = []
statuses = []
hrcounts = []
for USERID, NAME, COMPANY, MOBILE, EMAIL, ADDRESS, STATUS, HRCOUNT in cursor.fetchall(
):
ids.append(USERID)
names.append(NAME)
companies.append(COMPANY)
mobiles.append(MOBILE)
emails.append(EMAIL)
addresses.append(ADDRESS)
statuses.append(STATUS)
hrcounts.append(HRCOUNT)
return zip(ids, names, companies, mobiles, emails, addresses, statuses,
hrcounts)
def update_contacts(userid, name, company, mobile, email, address, status,
hrcount):
conn = connection()
cursor = conn.cursor()
sql_query = " UPDATE CONTACTS SET NAME=\"{NAME}\", COMPANY=\"{COMPANY}\", MOBILE=\"{MOBILE}\", EMAIL=\"{EMAIL}\", ADDRESS=\"{ADDRESS}\", STATUS=\"{STATUS}\", HRCOUNT=\"{HRCOUNT}\" WHERE USERID={USERID}; ".format(
NAME=name,
COMPANY=company,
MOBILE=mobile,
EMAIL=email,
ADDRESS=address,
STATUS=status,
HRCOUNT=hrcount,
USERID=userid)
try:
cursor.execute(sql_query)
conn.commit()
except Exception as e:
conn.rollback()
print(e)
conn.close()
def generate_contacts( username=None, status=None, number=None, company=None ):
sql_query = " SELECT * FROM CONTACTS "
is_none = 0
# When there are username filters
if username != None and username!= "Username":
is_none += 1
sql_query += "WHERE PERMISSIONS LIKE '%{USERNAME}%' ".format(USERNAME=username)
# When there are status filters
if status != None and status!= "All":
is_none += 1
if is_none == 1:
sql_query += "WHERE "
else:
sql_query += " AND "
sql_query += "STATUS LIKE '{STATUS}' ".format(STATUS=status)
# When there are mobile number filters.
if number != None:
is_none += 1
if is_none == 1:
sql_query += "WHERE "
else:
sql_query += " AND "
sql_query += "MOBILE LIKE '%{MOBILE}%' ".format(MOBILE=number)
# When there are company name filters.
if company != None:
is_none += 1
if is_none == 1:
sql_query += "WHERE "
else:
sql_query += " AND "
sql_query += "COMPANY LIKE \"%{COMPANY}%\" ".format(COMPANY=company)
# We add the final ordering and semicolon.
sql_query += "ORDER BY PERMISSIONS;"
#We now execute the query in thr SQL server.
conn = connection()
cursor = conn.cursor()
try:
cursor.execute(sql_query)
conn.close()
except Exception as e:
conn.close()
print(e)
#We write to the CSV file here.
f = open("/root/Oauthkeeper/static/csv/database-contacts.csv", "w")
f.write("HR Name,Company,Mobile,Email,Address,Status,HR Count, Second-Year\n");
#All the results are written to the file here.
for USERID, NAME, COMPANY, MOBILE, EMAIL, ADDRESS, STATUS, PERMISSIONS, HRCOUNT in cursor.fetchall():
str = "\"{NAME}\",\"{COMPANY}\",{MOBILE},{EMAIL},\"{ADDRESS}\",{STATUS},\"{PERMISSIONS}\",{HRCOUNT}\n".format( NAME=NAME, COMPANY=COMPANY, MOBILE=MOBILE, EMAIL=EMAIL, ADDRESS=ADDRESS, STATUS=STATUS, PERMISSIONS=PERMISSIONS, HRCOUNT=HRCOUNT )
f.write(str)
def get_all_contacts(username=None, status=None, number=None, company=None):
sql_query = " SELECT USERID, NAME, COMPANY, MOBILE, EMAIL, ADDRESS, STATUS, PERMISSIONS, HRCOUNT FROM CONTACTS "
is_none = 0
# When there are username filters
if username != None and username != "Username":
is_none += 1
sql_query += "WHERE PERMISSIONS LIKE '%{USERNAME}%' ".format(
USERNAME=username)
# When there are status filters
if status != None and status != "All":
is_none += 1
if is_none == 1:
sql_query += "WHERE "
else:
sql_query += " AND "
sql_query += "STATUS LIKE '{STATUS}' ".format(STATUS=status)
# When there are mobile number filters.
if number != None:
is_none += 1
if is_none == 1:
sql_query += "WHERE "
else:
sql_query += " AND "
sql_query += "MOBILE LIKE '%{MOBILE}%' ".format(MOBILE=number)
# When there are company name filters.
if company != None:
is_none += 1
if is_none == 1:
sql_query += "WHERE "
else:
sql_query += " AND "
sql_query += "COMPANY LIKE \"%{COMPANY}%\" ".format(COMPANY=company)
# We add the final semicolon.
sql_query += ";"
conn = connection()
cursor = conn.cursor()
try:
cursor.execute(sql_query)
conn.close()
except Exception as e:
conn.close()
print(e)
ids = []
names = []
companies = []
numbers = []
emails = []
addresses = []
statuses = []
permissions = []
hrcounts = []
for USERID, NAME, COMPANY, MOBILE, EMAIL, ADDRESS, STATUS, PERMISSIONS, HRCOUNT in cursor.fetchall(
):
ids.append(USERID)
names.append(NAME)
companies.append(COMPANY)
numbers.append(MOBILE)
emails.append(EMAIL)
addresses.append(ADDRESS)
statuses.append(STATUS)
permissions.append(PERMISSIONS)
hrcounts.append(HRCOUNT)
return zip(ids, names, companies, numbers, emails, addresses, statuses,
permissions, hrcounts)
