def __init__(self, app, db):
self.app = app
self.postgresql = PSQLConnector(app, db)
self.app.config['UPLOAD_FOLDER'] = 'static/uploads/'
self.stylesheet = getSampleStyleSheet()
self.attendees = Attendees(app, db)
self.alphanum_regex = re.compile(r'\w+')
class Instructors(object):
def __init__(self, app, db):
self.postgresql = PSQLConnector(app, db)
def add(self, instructor_list, class_id):
#instructor name is unique constriant, which we are not handling here! should do upsert
instructor_query = "INSERT INTO instructors (name, created_at)\
VALUES (:name, NOW())\
ON CONFLICT (name)\
DO UPDATE SET updated_at=NOW()\
RETURNING id"
all_instructor_ids = []
for name in instructor_list:
instructor = {"name": name}
instructor_id = self.postgresql.query_db(instructor_query, instructor)
all_instructor_ids.append(instructor_id)
return all_instructor_ids
def add_class_instructors(self, class_id, instructor_ids):
instructor_query = "INSERT INTO class_instructor (instructor_id, class_id)\
VALUES (:instructor_id, :class_id)\
RETURNING instructor_id"
for id in instructor_ids:
instructor = {
"instructor_id": id,
"class_id": class_id
}
self.postgresql.query_db(instructor_query, instructor)
def findAll(self, business_id):
if business_id == 23:
query = "SELECT * FROM instructors"
instructors = self.postgresql.query_db(query)
else:
query = "SELECT i.id, i.name FROM instructors AS i LEFT JOIN class_instructor AS ci ON i.id=ci.instructor_id LEFT JOIN classes AS c ON ci.class_id=c.id WHERE business_id=:business_id"
values = {
"business_id": business_id
}
instructors = self.postgresql.query_db(query, values)
return instructors
def find_all_class_instructors(self, class_id):
query = "SELECT * FROM class_instructor AS ci LEFT JOIN instructors AS i ON ci.instructor_id=i.id WHERE ci.class_id=:id"
values = {
"id": class_id
}
class_instructors = self.postgresql.query_db(query, values)
# print class_instructors
return class_instructors
def find_all_other(self, class_id):
# print "class id: ", class_id
query = "SELECT * FROM class_instructor LEFT JOIN instructors ON class_instructor.instructor_id=instructors.id WHERE class_instructor.class_id!=:id"
values = {
"id": class_id
}
all_other_instructors = self.postgresql.query_db(query, values)
# print "all other instructors: ", all_other_instructors
return all_other_instructors
def delete_class_relationship(self, class_id, instructor_ids):
query = "DELETE FROM class_instructor WHERE instructor_id=:instructor_id AND class_id=:class_id"
for id in instructor_ids:
instructor = {
"instructor_id": id,
"class_id": class_id
}
self.postgresql.query_db(query, instructor)
def update(self, instructors):
query = "UPDATE instructors SET name=:name WHERE id=:id"
for id in instructors:
values = {
"id": id,
"name": instructors[id]
}
self.postgresql.query_db(query, values)
def __init__(self, app, db):
self.classes = Classes(app, db)
self.postgresql = PSQLConnector(app, db)
class Attendees(object):
#######################################################################
# CONSTUCTORS
#######################################################################
def __init__(self, app, db):
self.classes = Classes(app, db)
self.postgresql = PSQLConnector(app, db)
#######################################################################
# SELECT METHODS
#######################################################################
def findOne(self, id):
query = "SELECT * FROM attendees WHERE id=:id"
values = {
"id": id
}
student = self.postgresql.query_db(query, values)
return student[0]
def get_cert_data(self, class_id):
query = "SELECT a.id AS attendee_id, a.name AS name, a.email AS email, ac.minutes AS minutes, c.name AS class_name, c.duration AS duration, c.email_text AS email_text, c.date AS class_date, c.race_verbiage AS race_verbiage, c.cvpm_verbiage AS cvpm_verbiage, c.race_course_num AS course_num\
FROM attendees AS a\
LEFT JOIN attended_classes AS ac\
ON a.id=ac.attendee_id\
LEFT JOIN classes AS c\
ON ac.class_id=c.id\
WHERE c.id=:class_id AND a.status=:status"
values = {
"class_id": class_id,
"status": "in_db"
}
cert_data = self.postgresql.query_db(query, values)
return cert_data
def find_all_in_class(self, class_id):
query = "SELECT a.id AS attendee_id, a.name AS name, a.email AS email, a.status AS status, ac.minutes AS minutes, c.duration AS duration, c.email_text AS email_text\
FROM attendees AS a\
LEFT JOIN attended_classes AS ac\
ON a.id=ac.attendee_id\
LEFT JOIN classes AS c\
ON ac.class_id=c.id\
WHERE c.id=:class_id AND ac.minutes >= c.duration AND a.email!= ''\
ORDER BY a.created_at DESC"
values = {
"class_id": class_id
}
students = self.postgresql.query_db(query, values)
return students
#######################################################################
# INSERT METHODS
#######################################################################
#what are expected column names?
def add_attendees(self, contents, class_id):
upsert_query = "INSERT INTO attendees (name, email, status, created_at)\
VALUES (:name, :email, :status, NOW())\
ON CONFLICT (email)\
DO UPDATE SET name=:name, status=:status, updated_at=NOW()\
RETURNING id"
attendee_info = []
i = 0
min_check = {}
# rel_info = []
header_marker = None;
for row in contents:
if row:
if isinstance(row[1],basestring):
row[1].encode('utf8')
else:
unicode(row[1]).encode('utf8')
if row[1].lower() == "email":
header_marker = i
if header_marker and i > header_marker and row[1]:
if row[1] not in min_check:
row_data = {
'name': row[0],
'email': row[1],
'min': row[2]
}
attendee_info.append(row_data)
if row[1] in min_check:
min_check[row[1]] += int(row[2])
else:
min_check[row[1]] = int(row[2])
i += 1
for info in attendee_info:
if info['email'] in min_check:
info['min'] = min_check[info['email']]
values = {
'name': info['name'],
'email': info['email'],
'status': 'in_db'
}
#UPSERT STUDENT ACCORDING TO DATA EXTRACTED FROM CSV, EMAIL IS UNIQUE CONSTRAINT
student_id = self.postgresql.query_db(upsert_query, values)
ac_data = {
"id": student_id,
"minutes": info['min'],
"class_id": class_id
}
self.add_attended_classes(ac_data)
return attendee_info
def add_attended_classes(self, data):
upsert_query = "INSERT into attended_classes (attendee_id, class_id, minutes)\
VALUES (:attendee_id, :class_id, :minutes)\
ON CONFLICT (attendee_id, class_id)\
DO UPDATE SET minutes = :minutes\
RETURNING attendee_id"
values = {
"attendee_id": data['id'],
"class_id": data['class_id'],
"minutes": data['minutes']
}
self.postgresql.query_db(upsert_query, values)
#######################################################################
# UPDATE METHODS
#######################################################################
def update_status(self, id, status):
query = "UPDATE attendees SET status=:status WHERE id=:id"
values = {
'status': status,
'id': id
}
self.postgresql.query_db(query, values)
def get_status(self, id):
query = "SELECT status FROM attendees WHERE id=:id"
values = {'id':id}
return self.postgresql.query_db(query, values)
class Classes(object):
def __init__(self, app, db):
self.postgresql = PSQLConnector(app, db)
self.instructors = Instructors(app, db)
def add(self, form_data):
valid = True
messages = {}
data = {}
course_regex = re.compile(r'^(\d+)-(\d+)')
match_obj = re.match(course_regex, form_data['course_num'])
instructor_list = []
for k, v in form_data.iteritems():
#check every key to see if it begins with new instructor
if k.startswith('new_instructor'):
#if it does push value to a list
instructor_list.append(v)
if len(form_data['name']) < 6:
messages['name_error'] = "name must be 6 characters or more"
valid = False
if not match_obj:
messages['course_num_error'] = "Expected digits-digits format, please check course number"
if not form_data['duration'].isdigit():
messages['duration_error']= "Please enter a whole number"
valid = False
if len(form_data['date']) < 10:
messages['date_error']= "Please enter correct date format"
valid = False
if form_data['existing_instructor'] == "" and len(instructor_list) < 1:
messages['instructor_error']= "Please choose existing instructor or add at least one new instructor"
valid = False
#make this requirement longer in deployment, length requirement shortened for testing
if len(form_data['email_text']) < 4:
messages['email_error']= "Email must be at least 4 characters in length"
valid = False
if len(form_data['race_verbiage']) < 4:
messages['race_verbiage']= "Race verbiage must be at least 4 characters in length"
valid = False
if valid:
query = "INSERT INTO classes (name, duration, business_id, email_text, date, created_at, race_verbiage, cvpm_verbiage, race_course_num) VALUES (:name, :duration, :business_id, :email_text, :date, NOW(), :race_verbiage, :cvpm_verbiage, :race_course_num) RETURNING id"
values = {
"name": form_data['name'],
"duration": form_data['duration'],
"business_id": session['business_id'],
"email_text": form_data['email_text'],
"date": form_data['date'],
"race_verbiage": form_data['race_verbiage'],
"cvpm_verbiage": form_data['cvpm_verbiage'],
"race_course_num": form_data['course_num']
}
# add class, return id
class_id = self.postgresql.query_db(query, values)
# add instructor names from list items to db as a group, return ids
instructor_ids = self.instructors.add(instructor_list, class_id)
if 'existing_instructor' in form_data and len(form_data['existing_instructor']) > 0:
instructor_ids.append(form_data['existing_instructor'])
# with returned ids, we then go and add entries to relational table
self.instructors.add_class_instructors(class_id, instructor_ids)
messages['id'] = class_id
return messages
def update(self, form_data):
# update class
query = "UPDATE classes SET name=:name, duration=:duration, email_text=:email_text, date=:date, updated_at=NOW(), race_verbiage=:race_verbiage, cvpm_verbiage=cvpm_verbiage WHERE id=:id"
values = {
"name": form_data['name'],
"duration": form_data['duration'],
"email_text": form_data['email_text'],
"date": form_data['date'],
"race_verbiage": form_data['race_verbiage'],
"cvpm_verbiage": form_data['cvpm_verbiage'],
"id": form_data['id']
}
class_id = form_data['id']
self.postgresql.query_db(query, values)
# collect all instructors to be removed, pass to instructors remove function
remove_instructors = {}
# collect all instructors to be updated, pass to instructors update function
update_instructors = {}
# collect all instructors to be added, pass to add instructors function
add_instructor = []
for key in form_data:
if key.startswith('remove'):
id = key[6:]
# print id
remove_instructors[id] = form_data[key]
elif key.startswith('instructor') and len(form_data[key]) > 0:
id = key[10:]
# print id
update_instructors[id] = form_data[key]
elif key.startswith('new_instructor') and len(form_data[key]) > 0:
add_instructor.append(form_data[key])
self.instructors.delete_class_relationship(class_id, remove_instructors)
added_instructors=[]
print "{}, line {}".format(getframeinfo(currentframe()).filename, getframeinfo(currentframe()).lineno), "add instructor array:", add_instructor
if add_instructor:
added_instructors = self.instructors.add(add_instructor, class_id)
if len(form_data['existing_instructor']) > 0:
added_instructors.append(form_data['existing_instructor'])
self.instructors.add_class_instructors(class_id, added_instructors)
self.instructors.update(update_instructors)
def findAll(self, business_id):
if session['permission'] == "super-admin":
query = "SELECT * FROM classes";
classes = self.postgresql.query_db(query)
else:
query = "SELECT * FROM classes WHERE business_id=:biz_id"
values = {
"biz_id": business_id
}
classes = self.postgresql.query_db(query, values)
return classes
def find_all_for_biz(self, business_id):
query = "SELECT * FROM classes WHERE business_id=:biz_id"
values = {
"biz_id": business_id
}
classes = self.postgresql.query_db(query, values)
return classes
def findOne(self, class_id):
query = "SELECT * FROM classes WHERE id=:class_id";
values = {
"class_id": class_id
}
one_class = self.postgresql.query_db(query, values)
return one_class[0]
def required_miutes(self, class_id):
query = "SELECT duration FROM classes WHERE id=:id"
values = {
"id": class_id
}
return self.postgresql.query_db(query, values)
def update_email(self, email_text, class_id):
query = "UPDATE classes SET email_text=:email_text WHERE id=:class_id"
values = {
"email_text": email_text,
"class_id": class_id
}
self.postgresql.query_db(query, values)
def get_email_text(self, class_id):
query = "SELECT email_text FROM classes WHERE id=:class_id"
values = {
"class_id": class_id
}
email_text = self.postgresql.query_db(query, values)
return email_text[0]['email_text']
def __init__(self, app, db):
self.postgresql = PSQLConnector(app, db)
self.instructors = Instructors(app, db)
def __init__(self, app, db):
self.postgresql = PSQLConnector(app, db)
self.businesses = Businesses(app, db)
def __init__(self, app, db):
self.postgresql = PSQLConnector(app, db)
self.street_addr_regex = re.compile(r'^\d+\s[a-zA-Z]+')
self.zip_addr_regex = re.compile(r'^[0-9]{5}')
self.email_regex = re.compile(
r'^[a-zA-Z0-9\.\+_-]+@[a-zA-Z0-9\._-]+\.[a-zA-Z]*$')
def __init__(self, app, db):
self.postgresql = PSQLConnector(app, db)
class Instructors(object):
def __init__(self, app, db):
self.postgresql = PSQLConnector(app, db)
def add(self, instructor_list, class_id):
#instructor name is unique constriant, which we are not handling here! should do upsert
instructor_query = "INSERT INTO instructors (name, created_at)\
VALUES (:name, NOW())\
ON CONFLICT (name)\
DO UPDATE SET updated_at=NOW()\
RETURNING id"
all_instructor_ids = []
for name in instructor_list:
instructor = {"name": name}
instructor_id = self.postgresql.query_db(instructor_query,
instructor)
all_instructor_ids.append(instructor_id)
return all_instructor_ids
def add_class_instructors(self, class_id, instructor_ids):
instructor_query = "INSERT INTO class_instructor (instructor_id, class_id)\
VALUES (:instructor_id, :class_id)\
RETURNING instructor_id"
for id in instructor_ids:
instructor = {"instructor_id": id, "class_id": class_id}
self.postgresql.query_db(instructor_query, instructor)
def findAll(self, business_id):
if business_id == 23:
query = "SELECT * FROM instructors"
instructors = self.postgresql.query_db(query)
else:
query = "SELECT i.id, i.name FROM instructors AS i LEFT JOIN class_instructor AS ci ON i.id=ci.instructor_id LEFT JOIN classes AS c ON ci.class_id=c.id WHERE business_id=:business_id"
values = {"business_id": business_id}
instructors = self.postgresql.query_db(query, values)
return instructors
def find_all_class_instructors(self, class_id):
query = "SELECT * FROM class_instructor AS ci LEFT JOIN instructors AS i ON ci.instructor_id=i.id WHERE ci.class_id=:id"
values = {"id": class_id}
class_instructors = self.postgresql.query_db(query, values)
# print class_instructors
return class_instructors
def find_all_other(self, class_id):
# print "class id: ", class_id
query = "SELECT * FROM class_instructor LEFT JOIN instructors ON class_instructor.instructor_id=instructors.id WHERE class_instructor.class_id!=:id"
values = {"id": class_id}
all_other_instructors = self.postgresql.query_db(query, values)
# print "all other instructors: ", all_other_instructors
return all_other_instructors
def delete_class_relationship(self, class_id, instructor_ids):
query = "DELETE FROM class_instructor WHERE instructor_id=:instructor_id AND class_id=:class_id"
for id in instructor_ids:
instructor = {"instructor_id": id, "class_id": class_id}
self.postgresql.query_db(query, instructor)
def update(self, instructors):
query = "UPDATE instructors SET name=:name WHERE id=:id"
for id in instructors:
values = {"id": id, "name": instructors[id]}
self.postgresql.query_db(query, values)
def __init__(self, app, db):
self.postgresql = PSQLConnector(app, db)
self.bcrypt = Bcrypt(app)
self.email_regex = re.compile(
r'^[a-zA-Z0-9\.\+_-]+@[a-zA-Z0-9\._-]+\.[a-zA-Z]*$')
class Users(object):
def __init__(self, app, db):
self.postgresql = PSQLConnector(app, db)
self.bcrypt = Bcrypt(app)
self.email_regex = re.compile(
r'^[a-zA-Z0-9\.\+_-]+@[a-zA-Z0-9\._-]+\.[a-zA-Z]*$')
def add(self, form_data):
message = {}
# check if email already exists in the database
email_dupe_query = "SELECT * FROM users WHERE (email = :email)"
email_dupe_values = {"email": form_data['email']}
email_dupe = self.postgresql.query_db(email_dupe_query,
email_dupe_values)
#validation check should return an empty array if email is unique
if not email_dupe:
password = form_data['password']
hashed_password = self.bcrypt.generate_password_hash(password)
valid = True
if len(form_data['name']) < 5:
message['name_error'] = 'name must be 4 characters or more'
valid = False
if not self.email_regex.match(form_data['email']):
message[
'email_error'] = 'email entered is not a valid email format'
valid = False
if len(password) < 8:
message[
'password_error'] = 'password must be 8 characters or more'
valid = False
if not valid:
return message
if form_data['permission'] == "super-admin":
business_id = 23
else:
business_id = form_data['business']
query_users = "INSERT INTO users (email, password, created_at, permission, name, business_id) VALUES (:email, :password, NOW(), :permission, :name, :business_id) RETURNING id"
values_users = {
"email": form_data['email'],
"password": hashed_password,
"permission": form_data['permission'],
"name": form_data['name'],
"business_id": business_id
}
id = self.postgresql.query_db(query_users, values_users)
# print id
message['id'] = id
else:
message[
'dupe_error'] = "user email already in database, please enter unique email"
return message
def login(self, form_data):
if not form_data['password'] or not form_data['email']:
error = {'error': 'please fill out login form'}
return error
else:
user = self.findOneFromEmail(form_data['email'])
#did db query return
if user == []:
error = {'error': 'user cannot be found in the database'}
return error
else:
if self.bcrypt.check_password_hash(user[0]['password'],
form_data['password']):
session['user_id'] = user[0]['id']
session['business_id'] = user[0]['business_id']
session['permission'] = user[0]['permission']
session['logged'] = True
success = {
"success": user[0]['id'],
"permission": user[0]['permission']
}
return success
else:
error = {
"error":
"email or password did not match an existing user in the database"
}
return error
def update(self, form_data):
valid = True
message = {}
if len(form_data['name']) < 5:
message['name_error'] = 'name must be 4 characters or more'
valid = False
if not self.email_regex.match(form_data['email']):
message[
'email_error'] = 'email entered is not a valid email format'
valid = False
if 'permission' not in form_data:
permission = "super-admin"
else:
permission = form_data['permission']
if valid:
query = "UPDATE users SET name=:name, email=:email, permission=:permission, updated_at=NOW() WHERE id=:id"
values = {
"name": form_data['name'],
"email": form_data['email'],
"permission": permission,
"id": form_data['id']
}
self.postgresql.query_db(query, values)
message['success'] = "user updated in database"
return message
def update_password(self, form_data, user_id):
message = {}
user = self.findOne(user_id)
if len(form_data['newpword']) < 8:
message[
'validation_err'] = "new password must be at least 8 characters long"
elif not self.bcrypt.check_password_hash(user['password'],
form_data['oldpword']):
message['validation_err'] = "password entered is incorrect"
else:
password = form_data['newpword']
hashed_password = self.bcrypt.generate_password_hash(password)
query = "UPDATE users SET password=:password WHERE id=:id"
values = {'password': hashed_password, 'id': user_id}
self.postgresql.query_db(query, values)
return message
def findAll(self):
query = "SELECT * FROM users"
users = self.postgresql.query_db(query)
return users
def findOne(self, id):
query = "SELECT * FROM users WHERE id=:id"
values = {"id": id}
user = self.postgresql.query_db(query, values)
return user[0]
def findOneFromEmail(self, email):
query = "SELECT * FROM users WHERE email=:email"
value = {"email": email}
return self.postgresql.query_db(query, value)
def destroy(self, id):
query = "DELETE FROM users WHERE id=:id"
values = {"id": id}
self.postgresql.query_db(query, values)
def __init__(self, app, db):
self.postgresql = PSQLConnector(app, db)
self.street_addr_regex = re.compile(r'^\d+\s[a-zA-Z]+')
self.zip_addr_regex = re.compile(r'^[0-9]{5}')
self.email_regex = re.compile(r'^[a-zA-Z0-9\.\+_-]+@[a-zA-Z0-9\._-]+\.[a-zA-Z]*$')
class Businesses(object):
def __init__(self, app, db):
self.postgresql = PSQLConnector(app, db)
self.street_addr_regex = re.compile(r'^\d+\s[a-zA-Z]+')
self.zip_addr_regex = re.compile(r'^[0-9]{5}')
self.email_regex = re.compile(r'^[a-zA-Z0-9\.\+_-]+@[a-zA-Z0-9\._-]+\.[a-zA-Z]*$')
def add(self, form_data):
valid = True
message = {}
data = {}
if len(form_data['business_name']) < 4:
message['biz_name_error'] = "business name must be 4 charcters or more"
valid = False
if len(form_data['street_addr']) < 7:
message['street_addr_error'] = "street address must be 6 characters or more"
valid = False
elif not self.street_addr_regex.match(form_data['street_addr']):
message['street_addr_error'] = "street address not formatted correctly"
valid = False
if len(form_data['city_addr']) < 3:
message['city_addr_error'] = "city name must be 3 characters or longer"
valid = False
if len(form_data['state_addr']) != 2:
message['state_addr_error'] = "please select a state"
valid = False
if not self.zip_addr_regex.match(form_data['zip_addr']):
message['zip_addr_error'] = "zipcode is not valid, should be only numeric and contain 5 digits"
valid = False
if not self.email_regex.match(form_data['email']):
message['email_error'] = "email format is not valid"
valid = False
if valid:
biz_query = "INSERT INTO businesses (name, street, city, state, zip, website,\
email, social_media_1, social_media_2, social_media_3, created_at)\
VALUES (:name, :street, :city, :state, :zip, :website, :email, :facebook, :twitter, :instagram, NOW())\
RETURNING id;"
biz_values = {
"name": form_data['business_name'],
"street": form_data['street_addr'],
"city": form_data['city_addr'],
"state": form_data['state_addr'],
"zip": form_data['zip_addr'],
"website": form_data['url'],
"email": form_data['email'],
"facebook": form_data['facebook'],
"twitter": form_data['twitter'],
"instagram": form_data['instagram']
}
biz_id = self.postgresql.query_db(biz_query, biz_values)
data['biz_id'] = biz_id
else:
data['message'] = message
return data
def update(self, form_data):
# print form_data
query = "UPDATE businesses\
SET name=:name, street=:street, city=:city, state=:state, zip=:zip, website=:website,\
social_media_1=:facebook, social_media_2=:twitter, social_media_3=:instagram, updated_at=NOW()\
WHERE id=:id"
values = {
"name": form_data['business_name'],
"street": form_data['street_addr'],
"city": form_data['city_addr'],
"state": form_data['state_addr'],
"zip": form_data['zip_addr'],
"website": form_data['website'],
"facebook": form_data['facebook'],
"twitter": form_data['twitter'],
"instagram": form_data['instagram'],
"id": form_data['business_id']
}
self.postgresql.query_db(query, values)
def findOne(self, biz_id):
query = "SELECT * FROM businesses WHERE id=:biz_id"
values = {
"biz_id": biz_id
}
biz_info = self.postgresql.query_db(query, values)
return biz_info[0]
def findAll(self):
query = "SELECT * FROM businesses WHERE name!=:name"
values = {
"name": "dummy"
}
businesses = self.postgresql.query_db(query, values)
return businesses
def add_pdf_url(self, business_data):
query = "UPDATE businesses SET pdf_url=:pdf_url, updated_at=NOW() WHERE id=:id"
values = {
"pdf_url": business_data['pdf'],
"id": business_data['id']
}
self.postgresql.query_db(query, values)
def check_pdf_url(self, business_id):
query = "SELECT pdf_url FROM businesses WHERE id=:business_id"
values = {
"business_id": business_id
}
url = self.postgresql.query_db(query, values)
return url
def add_dropbox_api_key(self, key, client_id):
with open('venv/bin/activate', 'a') as file:
export_string = "export " + str(client_id) + "ACCESS_KEY = " + str(key) + "\n"
file.write(str(export_string))
def __init__(self, app, db):
self.postgresql = PSQLConnector(app, db)
class Clients(object):
def __init__(self, app, db):
self.postgresql = PSQLConnector(app, db)
self.businesses = Businesses(app, db)
def add(self, id, form_data):
valid = True
message = {}
if len(form_data['name']) < 5:
message['name_error'] = "name must be 4 characters or more"
valid = False
else:
name_query = "SELECT * FROM clients\
WHERE name=:name\
AND business_id=:business_id"
name_data = {'name': form_data['name'], 'business_id': id}
name = self.postgresql.query_db(name_query, name_data)
if name:
message[
'name_error'] = "a client with this name is already associated with this business"
valid = False
if valid:
client_query = "INSERT INTO clients (name, title, business_id, created_at)\
VALUES (:name, :title, :business_id, NOW()) RETURNING id;"
client_values = {
"name": form_data['name'],
"title": form_data['title'],
"business_id": id
}
self.postgresql.query_db(client_query, client_values)
message['success'] = "client added to database"
print "message in add", message
return message
def update(self, form_data):
# print form_data
query = "UPDATE clients SET name=:name, title=:title, updated_at=NOW() WHERE id=:id"
client_ids = []
for client_info in form_data:
# print client_info
if client_info.startswith('id'):
client_ids.append(form_data[client_info])
print client_ids
for id in client_ids:
values = {
"name": form_data['name' + id],
"title": form_data['title' + id],
"id": id
}
print "values: ", values
self.postgresql.query_db(query, values)
def findAll(self):
query = "SELECT * FROM clients"
clients = self.postgresql.query_db(query)
return clients
def find_biz_owners(self, biz_id):
query = "SELECT * FROM clients WHERE business_id=:biz_id"
values = {"biz_id": biz_id}
clients = self.postgresql.query_db(query, values)
return clients
def destroy(self, id):
query = "DELETE FROM clients WHERE id=:id"
values = {"id": id}
self.postgresql.query_db(query, values)
class Businesses(object):
def __init__(self, app, db):
self.postgresql = PSQLConnector(app, db)
self.street_addr_regex = re.compile(r'^\d+\s[a-zA-Z]+')
self.zip_addr_regex = re.compile(r'^[0-9]{5}')
self.email_regex = re.compile(
r'^[a-zA-Z0-9\.\+_-]+@[a-zA-Z0-9\._-]+\.[a-zA-Z]*$')
def add(self, form_data):
valid = True
message = {}
data = {}
if len(form_data['business_name']) < 4:
message[
'biz_name_error'] = "business name must be 4 charcters or more"
valid = False
if len(form_data['street_addr']) < 7:
message[
'street_addr_error'] = "street address must be 6 characters or more"
valid = False
elif not self.street_addr_regex.match(form_data['street_addr']):
message[
'street_addr_error'] = "street address not formatted correctly"
valid = False
if len(form_data['city_addr']) < 3:
message[
'city_addr_error'] = "city name must be 3 characters or longer"
valid = False
if len(form_data['state_addr']) != 2:
message['state_addr_error'] = "please select a state"
valid = False
if not self.zip_addr_regex.match(form_data['zip_addr']):
message[
'zip_addr_error'] = "zipcode is not valid, should be only numeric and contain 5 digits"
valid = False
if not self.email_regex.match(form_data['email']):
message['email_error'] = "email format is not valid"
valid = False
if valid:
biz_query = "INSERT INTO businesses (name, street, city, state, zip, website,\
email, social_media_1, social_media_2, social_media_3, created_at)\
VALUES (:name, :street, :city, :state, :zip, :website, :email, :facebook, :twitter, :instagram, NOW())\
RETURNING id;"
biz_values = {
"name": form_data['business_name'],
"street": form_data['street_addr'],
"city": form_data['city_addr'],
"state": form_data['state_addr'],
"zip": form_data['zip_addr'],
"website": form_data['url'],
"email": form_data['email'],
"facebook": form_data['facebook'],
"twitter": form_data['twitter'],
"instagram": form_data['instagram']
}
biz_id = self.postgresql.query_db(biz_query, biz_values)
data['biz_id'] = biz_id
else:
data['message'] = message
return data
def update(self, form_data):
# print form_data
query = "UPDATE businesses\
SET name=:name, street=:street, city=:city, state=:state, zip=:zip, website=:website,\
social_media_1=:facebook, social_media_2=:twitter, social_media_3=:instagram, updated_at=NOW()\
WHERE id=:id"
values = {
"name": form_data['business_name'],
"street": form_data['street_addr'],
"city": form_data['city_addr'],
"state": form_data['state_addr'],
"zip": form_data['zip_addr'],
"website": form_data['website'],
"facebook": form_data['facebook'],
"twitter": form_data['twitter'],
"instagram": form_data['instagram'],
"id": form_data['business_id']
}
self.postgresql.query_db(query, values)
def findOne(self, biz_id):
query = "SELECT * FROM businesses WHERE id=:biz_id"
values = {"biz_id": biz_id}
biz_info = self.postgresql.query_db(query, values)
return biz_info[0]
def findAll(self):
query = "SELECT * FROM businesses WHERE name!=:name"
values = {"name": "dummy"}
businesses = self.postgresql.query_db(query, values)
return businesses
def add_pdf_url(self, business_data):
query = "UPDATE businesses SET pdf_url=:pdf_url, updated_at=NOW() WHERE id=:id"
values = {"pdf_url": business_data['pdf'], "id": business_data['id']}
self.postgresql.query_db(query, values)
def check_pdf_url(self, business_id):
query = "SELECT pdf_url FROM businesses WHERE id=:business_id"
values = {"business_id": business_id}
url = self.postgresql.query_db(query, values)
return url
def add_dropbox_api_key(self, key, client_id):
with open('venv/bin/activate', 'a') as file:
export_string = "export " + str(client_id) + "ACCESS_KEY = " + str(
key) + "\n"
file.write(str(export_string))