def signup(email, password, verification):
connection = get_app_connection()
cursor = connection.cursor()
sql = u"select * from Verification where `Email` = '{}' and `Code` = '{}'".format(
email, verification)
print sql
cursor.execute(sql)
res = cursor.fetchall()
if res:
pass
else:
raise Exception('Incorrect verification code')
token = '{}:{}'.format(email, uuid.uuid4())
md5 = hashlib.md5()
md5.update(password)
sql = u"insert into User (`Email`, `Name`, `Gender`, `Type`, `Password`, `Token`) " \
u"VALUES ('{}', '{}', '{}', '{}', '{}', '{}')".format(email, email, 'Male', 1, md5.hexdigest(), token)
cursor.execute(sql)
connection.commit()
cursor.execute("select * from User where email = '{}'".format(email))
users = cursor.fetchall()
user = dict(zip(cursor.column_names, users[0])) if len(users) > 0 else None
cursor.close()
connection.close()
return user
def parse_token(token):
connection = get_app_connection()
cursor = connection.cursor()
cursor.execute("select * from User where token = '{}'".format(token))
users = cursor.fetchall()
user = dict(zip(cursor.column_names, users[0])) if len(users) > 0 else None
cursor.close()
connection.close()
return user['ID'] if user else None
def company_like_unlike(user_id, company_id):
connection = get_app_connection()
cursor = connection.cursor()
sql = u'DELETE FROM `CompanyLike` WHERE `UserID`= {} and `CompanyID` = {}'.format(
user_id, company_id)
cursor.execute(sql)
cursor.close()
connection.commit()
connection.close()
return ''
def company_like_like(user_id, company_id):
connection = get_app_connection()
cursor = connection.cursor()
sql = u'insert into `CompanyLike` (`UserID`, `CompanyID`) values ({}, {})'.format(
user_id, company_id)
cursor.execute(sql)
cursor.close()
connection.commit()
connection.close()
return ''
def company_like_query(user_id, company_id):
connection = get_app_connection()
cursor = connection.cursor()
sql = u'select * from CompanyLike where UserID = {} and CompanyID = {}'.format(
user_id, company_id)
cursor.execute(sql)
liked = True if cursor.fetchall() else False
sql = u'select count(*) from CompanyLike where CompanyID = {}'.format(
company_id)
cursor.execute(sql)
number = cursor.fetchone()
cursor.close()
connection.close()
return {'liked': liked, 'number': number}
def verify_email(email):
connection = get_app_connection()
cursor = connection.cursor()
sql = u"select * from Verification where `email` = '{}'".format(email)
cursor.execute(sql)
res = cursor.fetchone()
if res:
code = res[2]
else:
code = randint(0, 999999)
code = str(code)
code = '0' * (6 - len(code)) + code
sql = u"insert into Verification (`Email`, `Code`) values ('{}', '{}')".format(
email, code)
cursor.execute(sql)
connection.commit()
send_verification_code(email, code)
cursor.close()
connection.close()
return ''
def user_login(email, password):
m = hashlib.md5()
m.update(password)
hashed = m.hexdigest()
connection = get_app_connection()
cursor = connection.cursor()
cursor.execute(
"select * from User where email = '{}' and password = '******'".format(
email, hashed))
users = cursor.fetchall()
user = dict(zip(cursor.column_names, users[0])) if len(users) > 0 else None
if user:
token = '{}:{}'.format(user['Email'], uuid.uuid4())
user['Token'] = token
cursor.execute("update User set Token = '{}' where ID = {}".format(
token, user['ID']))
connection.commit()
cursor.close()
connection.close()
if user:
user.pop('Password', None)
return user
def company_query(user_id,
offset=0,
page_size=10,
orders='',
name='',
people='',
address='',
constructor='',
executive='',
manager='',
supervisor='',
type='',
level='',
location='',
funding=''):
where = []
if name:
where.append(u"CompanyName like '%{}%'".format(name))
if people:
where.append(u"LegalRepresentative like '%{}%'".format(people))
if address:
where.append(u"OperatingLocation like '%{}%'".format(address))
if type:
where.append(u"CompanyType like '%{}%'".format(type))
if location:
where.append(u"RegisterLocation like '%{}%'".format(location))
where = u'where {}'.format(' and '.join(where)) if where else ''
order = [
transform(o[1:]) + " desc" if o.startswith('~') else transform(o)
for o in orders.split(',')
] if orders else None
order = 'order by {}'.format(', '.join(order)) if order else ''
offset = int(offset)
page_size = int(page_size)
connection = get_data_connection()
cursor = connection.cursor()
sql = u'select * from CompanyInfo {} {} limit {} offset {}'.format(
where, order, page_size, offset)
print sql
cursor.execute(sql)
app_connection = get_app_connection()
app_cursor = app_connection.cursor()
results = []
for data in cursor:
results.append(dict(zip(cursor.column_names, data)))
for company in results:
sql = u'select * from CompanyCert where CompanyID = {}'.format(
company['ID'])
print sql
cursor.execute(sql)
company['CompanyCerts'] = []
for cert in cursor.fetchall():
company['CompanyCerts'].append(dict(zip(cursor.column_names,
cert)))
sql = u'select * from RegisteredStaff where CompanyID = {}'.format(
company['ID'])
print sql
cursor.execute(sql)
company['Staffs'] = []
for data in cursor.fetchall():
company['Staffs'].append(dict(zip(cursor.column_names, data)))
sql = u'select * from Project where CompanyID = {}'.format(
company['ID'])
print sql
cursor.execute(sql)
company['Projects'] = []
for data in cursor.fetchall():
company['Projects'].append(dict(zip(cursor.column_names, data)))
sql = u'select * from CompanyLike where UserID = {} and CompanyID = {}'.format(
user_id, company['ID'])
app_cursor.execute(sql)
liked = True if app_cursor.fetchall() else False
company['Liked'] = liked
sql = u'select count(*) from CompanyLike where CompanyID = {}'.format(
company['ID'])
app_cursor.execute(sql)
number = app_cursor.fetchone()[0]
company['LikedNum'] = number
cursor.close()
connection.close()
app_cursor.close()
app_connection.close()
return results