def get_user_by_name(name):
query = '''SELECT * FROM user
WHERE user_name = '{}' '''.format(name)
res = connection.do_query(query)
if res:
return get_user(res[0])
return None
def get_id_groups():
id_list = []
query = '''SELECT id FROM `groups`'''
res = connection.do_query(query)
for id_ in res:
id_list.append(id_.get("id"))
return id_list
def get_count_member_by_group_id(id_):
query = '''SELECT count(*) FROM purchaser
WHERE group_id = {}'''.format(id_)
res = connection.do_query(query)
return res[0].get('count(*)')
def authenticate(user_name, password):
query = '''SELECT count(*) FROM user
WHERE user_name = '{}' and user_password = '******' '''.format(
user_name, password)
res = connection.do_query(query)
if res[0].get('count(*)') > 0:
return True
return False
def exist(user):
query = '''SELECT count(*) FROM user
WHERE user_name = '{}' or user_mail = '{}' '''.format(
user.user_name, user.user_mail)
res = connection.do_query(query)
if res[0].get('count(*)') > 0:
return True
return False
def get_id_group(group_):
query = '''SELECT id FROM `groups`
WHERE group_name = '{}' and manager = '{}' '''.format(
group_.group_name, group_.manager)
res = connection.do_query(query)
if res:
return res[0].get("id", 0)
return None
def get_all_users():
users = []
query = '''SELECT * FROM user'''
res = connection.do_query(query)
for user in res:
users.append(get_user(user))
return users
def get_all_user_name_by_group_id(group_id):
query = '''select user_name, user_mail
from (select id, manager from groups) as g
natural join (select user_name, user_mail from user) as u
where g.id = {} and u.user_name = g.manager'''.format(group_id)
res = connection.do_query(query)
return list(res)
def get_all_groups():
groups = []
query = '''SELECT * FROM `groups` NATURAL JOIN count_members_group WHERE groups.id = count_members_group.group_id'''
res = connection.do_query(query)
for group in res:
count = group.get("count")
del group["count"]
groups.append((get_group(group), count))
return groups
def get_all_message_by_group_id_order(group_id):
forums = []
query = '''SELECT * FROM forums
WHERE group_id = {}
ORDER BY end_date, end_time DESC'''.format(group_id)
res = connection.do_query(query)
for forum in res:
forums.append(get_forum(forum))
return forums
def get_all_groups_by_categoty_name(name):
groups = []
id_category = category_module.get_id_from_name(name)
query = '''SELECT * FROM `groups`
WHERE category_id = {} '''.format(id_category)
res = connection.do_query(query)
for group in res:
groups.append(get_group(group))
return groups
def get_all_groups_of_user_name(name):
groups = []
all_id_groups = purchaser_module.get_id_group_by_name(name)
for id_ in all_id_groups:
query = '''SELECT * FROM `groups`
WHERE id = {} '''.format(id_)
res = connection.do_query(query)
if res:
groups.append(get_group(res[0]))
return groups
def send_message():
now = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
n = now.split(" ")
query = ''' select user_name, user_mail, group_name
from purchaser natural join groups natural join user
where id = group_id and user_name = manager and manager = user_name
and end_date = '{}' '''.format(n[0])
res = connection.do_query(query)
names, emails, messages = get_contacts(
res) # read contacts 'mycontacts.txt'
#message_template = read_template() #'message.txt'
# set up the SMTP server
s = smtplib.SMTP(host='smtp.gmail.com', port=587)
s.starttls()
s.login(MY_ADDRESS, PASSWORD)
# For each contact, send the email:
for name, email, message in zip(names, emails, messages):
msg = MIMEMultipart() # create a message
# add in the actual person name to the message template
message = message.substitute(PERSON_NAME=name.title())
# Prints out the message body for our sake
print(message)
# setup the parameters of the message
msg['From'] = MY_ADDRESS
msg['To'] = email
msg['Subject'] = "Group Buy"
# add in the message body
msg.attach(MIMEText(message, 'plain'))
# send the message via the server set up earlier.
s.send_message(msg)
del msg
# Terminate the SMTP session and close the connection
s.quit()
def get_all_gruops_without_preproccess():
query = '''SELECT * FROM `groups` NATURAL JOIN count_members_group WHERE groups.id = count_members_group.group_id'''
res = connection.do_query(query)
return list(res)
