def customer_core(self): query = """SELECT * FROM customer_core(?)""" data, cursor = db.execute(query, True, (self.customer_id,)) data = cursor.fetchone() if data != None: return data else: return self.populate_null()
def get_tests(self): query = """SELECT * FROM get_tests(?)""" data, cursor = db.execute(query, True, (self.customer_id,)) data = cursor.fetchall() columns = [ 'views', 'conversion', 'variant', 'best_worst_binary', 'hypothesis' ] return_data = UserService.parseCursor(data, columns) return return_data
def get(self): print('invoked') query = "exec get_tactics @customer_id = ?" data, cursor = db.execute(query, True, (self.customer_id, )) data = cursor.fetchall() columns = ['title', 'description', 'tactic_id'] returned = UserService.parseCursor(data, columns) return returned
def get(self) -> dict: query = f"""exec fetch_notifications @customer_id = {self.customer_id}""" data, cursor = db.execute(query, True, ()) data = cursor.fetchall() columns = [ 'message_string', 'task_title', 'insight_body', 'message_from' ] return_data = UserService.parseCursor(data, columns) return return_data
def Insight(self, insight): query = "SELECT email FROM customer_basic WHERE id = ?" data, cursor = db.execute(query, True, (self.customer_id, )) data = cursor.fetchone() mailman = EmailService(to=data[0]) subject = "You have a new insight in Market(r)!" message = "Login here to view the message: https://marketr.life/home?view=campaigns" mailman.send(subject=subject, message=message)
def is_due(self): result, cursor = db.execute("SELECT * FROM run_listener(?)", True, (self.customer_id, )) result = cursor.fetchall() if len(result) > 0: result = result[0][1] return False, result else: return True, None
def compile(self): query = "SELECT title, description, tactic_id FROM tactics WHERE tactic_id = ?" data, cursor = db.execute(query, True, (self.tactic_id,)) data = cursor.fetchone() self.data = { 'title': data[0], 'description': data[1], 'id': data[2] }
def compile_core(self) -> dict: query = "select * from compile_home_page(?)" core_data, cursor = db.execute(query, True, (self.customer_id,)) core_data = cursor.fetchone() return_dict = {} #anchor2 for i in range(len(core_data)): return_dict[i] = core_data[i] return return_dict
def compile_splash(self): tup = (self.next_step,) query = "SELECT heading, paragraph FROM dbo.splash WHERE after_page = ?" data, cursor = db.execute(query, True, tup) del data heading, paragraph = cursor.fetchone() self.heading = heading.replace("`", "'") self.paragraph = paragraph.replace("`", "'") cursor.close()
def get_persona(self): query = """ EXEC init_audience @customer_id = %s; """ % (self.id, ) result, cursor = db.execute(query, True, ()) result = cursor.fetchone() cursor.close() return str(result[0])
def get(self): query = """EXEC get_tactics @customer_id = ?""" data, cursor = db.execute(query, True, (self.customer_id,)) data = cursor.fetchone() returned = { 'title': data[0] if data else None, 'description': data[1] if data else None, 'id': data[2] if data else None } return returned
def get_messages(self) -> dict: query = """SELECT * FROM prep_messages(?)""" result, cursor = db.execute(query, True, (self.customer_id)) # columns = ['message_string', 'admin_id', 'customer_id', 'from', 'timestamp'] result = cursor.fetchone() # result = UserService.parseCursor(result, columns) try: return eval(result[0]) except: return None
def get_all(self): campaigns, cursor = db.execute( "SELECT * FROM user_campaigns WHERE customer_id = ?", True, (self.customer_id, )) campaigns = cursor.fetchall() campaign_list = list() for campaign in campaigns: campaign_list.append(self.struct(campaign)) return campaign_list
def get_tasks(self) -> dict: query = """SELECT * FROM prep_tasks(?)""" result, cursor = db.execute(query, True, (self.customer_id)) # columns = ['task_title', 'complete_binary'] result = cursor.fetchone() # result = UserService.parseCursor(result, columns) try: return eval(result[0]) except: return None
def get_platforms(): tup = (session['user'], ) query = """ SELECT facebook, google, bing, twitter, instagram, yelp, linkedin, amazon, snapchat, youtube FROM history WHERE customer_id = ? """ data, cursor = db.execute(query, True, tup) data = cursor.fetchone() cursor.close() return json.dumps(list(data))
def count_unclaimed(self): query = "select * from raw_achievements(?)" data, cursor = db.execute(query, True, (self.customer_id, )) data = cursor.fetchall() count = 0 for row in data: cid = row[4] claimed = row[5] if cid and not claimed: count += 1 return count
def campaigns(self): query = "select facebook_id, google_id, twitter_id from customer_basic where id = ?" data, cursor = db.execute(query, True, (self.user,)) data = cursor.fetchone() meta = self.admin_dashboard() self.accounts = { 'facebook_id': data[0], 'google_id': data[1], 'twitter_id': data[2], 'meta': self.admin_dashboard() }
def validate_view(self, admin_id, customer_id) -> bool: validate_tup = (admin_id, customer_id) query = """ SELECT * FROM validate_admin_view(?, ?) """ returned, cursor = db.execute(query, True, validate_tup) returned = cursor.fetchone() cursor.close() if returned != None: return True else: return False
def get_admins(self): query = "select (first_name + ' ' + last_name) as name, id from admins" data, cursor = db.execute(query, True, ()) data = cursor.fetchall() return_data = [] for row in data: to_append = {"name": row[0], "id": row[1]} return_data.append(to_append) cursor.close() return return_data
def login(self, email, password, form=None): try: tup = (email, ) query = "SELECT email, password, ID, first_name, last_name, permissions FROM dbo.admins WHERE email = ?" data, cursor = db.execute(query, True, tup) data = cursor.fetchall() pw = data[0][1] uid = data[0][2] admin_first = data[0][3] admin_last = data[0][4] permissions = data[0][5] cursor.close() if sha256_crypt.verify(password, pw): session['logged_in'] = False session['customer'] = False session['admin'] = int(uid) session['email'] = data[0][0] session['admin_first'] = admin_first session['admin_last'] = admin_last if permissions == 'owner': session['owner_logged_in'] = True session['admin_logged_in'] = True session['manager_logged_in'] = True session['permissions'] = 'owner' elif permissions == 'admin': session['owner_logged_in'] = False session['admin_logged_in'] = True session['manager_logged_in'] = True session['permissions'] = 'admin' elif permissions == 'manager': session['owner_logged_in'] = False session['admin_logged_in'] = False session['manager_logged_in'] = True session['permissions'] = 'manager' session.permanent = True session.remember = True return redirect(url_for('customers')) else: error = "Invalid credentials. Try again!" return render_template("admin_view/login.html", error=error, form=form) except: # except AssertionError: error = "Invalid credentials. Try again!" return render_template("admin_view/login.html", error=error, form=form)
def get_first_audience(user): try: tup = (session['user'], ) query = "SELECT persona_name, audience_id FROM dbo.audience WHERE customer_id = ?" names_and_ids, cursor = db.execute(query, True, tup) names_and_ids = cursor.fetchall() first_id = names_and_ids[0][1] session['first_id'] = first_id cursor.close() return names_and_ids except: session['first_id'] = None return False
def get_meta(self): query = "SELECT comp_1_name, comp_1_website, comp_1_type, comp_2_name, comp_2_website, comp_2_type FROM competitors WHERE customer_id = ?" data, cursor = db.execute(query, True, (self.customer_id,)) data = cursor.fetchone() returned = { 'comp_1_name': data[0], 'comp_1_website': data[1], 'comp_1_type': data[2], 'comp_2_name': data[3], 'comp_2_website': data[4], 'comp_2_type': data[5] } return returned
def post_task(self, task, tactic_id = None) -> None: tup = (task, self.customer_id, task, 0, self.admin_id, self.customer_id, tactic_id) query = """ IF NOT EXISTS (SELECT task_title FROM to_do WHERE task_title = ? and customer_id = ?) INSERT INTO to_do (task_title, completed_binary, admin_assigned, customer_id, tactic_id) VALUES (?,?,?,?,?) """ db.execute(query, False, tup, commit=True) if self.user == 'customer': email = 'select top 1 email from customer_basic where id = ?' data, cursor = db.execute(email, True, (self.customer_id,)) elif self.user == 'admin': email = 'select top 1 email from admins where id = ?' data, cursor = db.execute(email, True, (self.admin_id,)) email = cursor.fetchone() email = email[0] notification = NotificationsService(self.customer_id) notification.TaskNotification(email)
def tactics_rewards(self, amount): query = """ SET NOCOUNT ON exec get_tactics @customer_id = ?; """ data, cursor = db.execute(query, True, (self.customer_id, ), commit=True) data = cursor.fetchone() tactic = { 'title': data[0] if data else None, 'description': data[1] if data else None, 'id': data[2] if data else None } cursor.close() update_query = "insert into tactics_log (customer_id, tactic_id) values (?,?)" db.execute(update_query, False, (self.customer_id, tactic['id']), commit=True) return tactic
def GetData(self): if self.table == 'audience': params = ('persona_name', 'audience_id', self.table, self.user) elif self.table == 'product_list': params = ('name', 'p_id', self.table, self.user) query = "SELECT %s, %s FROM %s WHERE customer_id = %s" % params data, cursor = db.execute(query, True, ()) data = cursor.fetchall() column_list = ['name', 'id'] data = UserService.parseCursor(data, column_list) return data
def get_customer_data(self): query = "SELECT * FROM settings_view(?)" data, cursor = db.execute(query, True, (self.customer_id,)) data = cursor.fetchone() self.customer = { 'first_name': data[0], 'current_plan': False if not data[1] else True, 'almost_free': False if not data[2] else True, 'ad_mid': False if not data[3] else True, 'ad_premium': False if not data[4] else True, 'funds_remaining': data[5], 'spend_rate': data[6], 'analytics': data[7] }
def delete_subscriptions(self, sub_id=None, customer_id=None): stripe.api_key = self.sk cancelled_plan = stripe.Subscription.delete(sub_id) plan_id = cancelled_plan['items']['data'][0]['plan']['id'] plan_table = { # live mode 'plan_FfI9OI02wob7Wl': 'ab_binary', 'plan_FxJImVg8UME2BU': 'ad_binary', 'plan_FfIAIrHBJ78YpY': 'almost_free_binary', 'plan_GDRUIQvA5OEUgK': 'ad_binary', 'plan_FxJJZ1sUDZ0550': 'ad_premium', 'plan_GiHRR96eXXfxQM': 'analytics', # test mode 'plan_Fed1YzQtnto2mT': 'ab_binary', 'plan_FecAlOmYSmeDK3': 'ad_binary', 'plan_FeZoBcEgfD35he': 'almost_free_binary' } db.execute( f'UPDATE customer_basic SET {plan_table[plan_id]} = null, current_plan = null WHERE stripe_id = ?', False, (customer_id, ), commit=True)
def update_admin(a_id): POST_first_name = clean(request.form['first_name']) POST_last_name = clean(request.form['last_name']) POST_USERNAME = clean(request.form['email']) POST_PERMISSIONS = clean(request.form['permissions']) POST_PHONE = request.form['phone'] POST_POSITION = clean(request.form['position']) tup = (POST_first_name, POST_last_name, POST_USERNAME, POST_PERMISSIONS, POST_PHONE, POST_POSITION, a_id) query = """ UPDATE dbo.admins SET first_name = ?, last_name = ?, email = ?, permissions = ?, phone_num = ?, position = ? WHERE id = ?; commit; """ db.execute(query, False, tup) return redirect(url_for('personnel'))
def availability(): email = request.args.get('email') tup = (email, ) query = """ SELECT email FROM customer_basic WHERE email = ? """ data, cursor = db.execute(query, True, tup) data = cursor.fetchall() cursor.close() if data == []: return 'True' else: return 'False'
def add_rep(self, form, customer_id): acct_mgr: str = form['account_mgr'] am_tup = (customer_id, acct_mgr) am_query = """EXEC update_account_mgr @customer_id = ?, @admin_id = ?""" if not self.debug and acct_mgr != "": db.execute(am_query, False, am_tup, commit=True) elif self.debug: print(am_query) print(am_tup) rep_query = """EXEC update_account_rep @customer_id = ?, @admin_id = ?""" for item in form: if len(item) > 8 and item[:9] == 'add_admin' and form[ item] != acct_mgr and form[item] != "": rep = form[item] rep_tup = (customer_id, rep) if not self.debug: db.execute(rep_query, False, rep_tup, commit=True) else: print(rep_query)