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)