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)