def tag_many(contacts, tag):
sets = [ "('civicrm_contact', {contact_id}, {tag_id})".format(contact_id=contact_id, tag_id=tag.id)
for contact_id in contacts ]
values = ", ".join(sets)
db.get_db(config.civicrm_schema).execute("""
INSERT IGNORE INTO civicrm_entity_tag
(entity_table, entity_id, tag_id)
VALUES
%s
""" % values)
def tag_single(contact_id, tag):
db.get_db(config.civicrm_schema).execute("""
INSERT IGNORE INTO civicrm_entity_tag
SET
entity_table = 'civicrm_contact',
entity_id = %(contact_id)s,
tag_id = %(tag_id)s
""", {
'contact_id': contact_id,
'tag_id': tag.id,
})
def tag_single(contact_id, tag):
db.get_db(config.civicrm_schema).execute(
"""
INSERT IGNORE INTO civicrm_entity_tag
SET
entity_table = 'civicrm_contact',
entity_id = %(contact_id)s,
tag_id = %(tag_id)s
""", {
'contact_id': contact_id,
'tag_id': tag.id,
})
def tag_many(contacts, tag):
sets = [
"('civicrm_contact', {contact_id}, {tag_id})".format(
contact_id=contact_id, tag_id=tag.id)
for contact_id in contacts
]
values = ", ".join(sets)
db.get_db(config.civicrm_schema).execute("""
INSERT IGNORE INTO civicrm_entity_tag
(entity_table, entity_id, tag_id)
VALUES
%s
""" % values)
def load_user(id):
db = get_db()
user = db.execute(
'SELECT * FROM user WHERE id = ?', (id,)
).fetchone()
return Logger(user['firstname'],user['id'],user['active'])
def register():
if request.method == 'POST':
firstname = request.form['firstname']
lastname = request.form['lastname']
email = request.form['email']
password = request.form['password']
db = get_db()
error = None
if not firstname:
error = 'First name is required.'
elif not lastname:
error = 'Last name is required.'
elif not email:
error = 'Email address is required.'
elif not password:
error = 'Password is required.'
elif db.execute(
'SELECT id FROM user WHERE email = ?', (email,)
).fetchone() is not None:
error = 'User {} is already registered.'.format(email)
if error is None:
db.execute(
'INSERT INTO user (firstname, lastname, email, password, active) VALUES (?, ?, ?, ?, ?)',
(firstname, lastname, email, generate_password_hash(password), 1)
)
db.commit()
return redirect(url_for('blueprint.login'))
flash(error)
return render_template('register.html')
def login():
if request.method == 'POST':
email = request.form['email']
password = request.form['password']
db = get_db()
error = None
user = db.execute(
'SELECT * FROM user WHERE email = ?', (email,)
).fetchone()
if user is None:
error = 'Incorrect email.'
elif not check_password_hash(user['password'], password):
error = 'Incorrect password.'
if error is None:
logger_user = load_user(user['id'])
login_user(logger_user)
return redirect(url_for('blueprint.index'))
flash(error)
return render_template('login.html')
def init_db():
with app.app_context():
d = db.get_db()
with app.open_resource('database/schema.sql', mode='r') as f:
d.cursor().executescript(f.read())
d.commit()
return 'database initialized correctly!'
def get_totals(wheres=None,
query=None,
banner=None,
campaign=None,
country=None,
start=None,
end=None,
**ignore):
'''
Note that the column names must match a heading in the results spreadsheet.
'''
if not query:
query = db.Query()
query.columns.append('SUM(total_amount) AS total')
query.columns.append('AVG(total_amount) AS mean')
query.columns.append(
'AVG( IF(total_amount > 20, 20, total_amount) ) AS mean20')
query.columns.append('STD(total_amount) AS sd')
query.columns.append('COUNT(ct.id) AS clicks')
query.columns.append('COUNT(cc.id) AS donations')
query.tables.append(config.contribution_tracking_prefix +
'contribution_tracking ct')
query.tables.append(
"civicrm_contribution cc ON cc.id = ct.contribution_id")
if wheres:
query.where.extend(wheres)
if campaign:
query.columns.append("utm_campaign AS campaign")
query.where.append("utm_campaign = %(campaign)s")
query.params['campaign'] = campaign
if banner:
query.columns.append(ct_banner_clause + " AS banner")
query.where.append(ct_banner_clause + " = %(banner)s")
query.params['banner'] = banner
if country:
query.where.append("country = %(country)s")
query.params['country'] = country
if start and end:
query.where.append("ts BETWEEN %(start)s AND %(end)s")
query.params['start'] = start
query.params['end'] = end
if not query.where:
raise Exception("Don't try this query without a where clause.")
result = list(db.get_db().execute(query))
row = result.pop()
# nasty hack for json encoding snafu:
for k, v in row.items():
if isinstance(v, decimal.Decimal):
row[k] = str(v)
# add some goodies:
row['sql'] = str(query)
row['updated'] = time_util.str_now()
return row
def fetch(self):
'''Load a batch of contacts into the cache'''
query = self.buildQuery()
self.contacts = []
result = db.get_db().execute(query)
for row in result:
name_components = []
keys = [
'first_name', 'middle_name', 'last_name', 'organization_name'
]
for key in keys:
if key in row and row[key]:
name_components.append(row[key])
#TODO: consider some flatter structure:
#self.contacts.append([
# row['id'],
# " ".join(name_components),
# row['email'],
#])
self.contacts.append({
'id': row['id'],
'name': " ".join(name_components),
'email': row['email'],
})
def __init__(self, name):
self.name = name
sql = "INSERT INTO donor_autoreview_job SET name = %s"
dbc = db.get_db(config.drupal_schema)
dbc.execute(sql, (name, ))
self.id = dbc.last_insert_id()
def fetch(self):
'''Load a batch of contacts into the cache'''
query = self.buildQuery()
self.contacts = []
result = db.get_db().execute(query)
for row in result:
name_components = []
keys = ['first_name', 'middle_name', 'last_name', 'organization_name']
for key in keys:
if key in row and row[key]:
name_components.append(row[key])
#TODO: consider some flatter structure:
#self.contacts.append([
# row['id'],
# " ".join(name_components),
# row['email'],
#])
self.contacts.append({
'id': row['id'],
'name': " ".join(name_components),
'email': row['email'],
})
def __init__(self, name):
self.name = name
sql = "SELECT id FROM civicrm_tag WHERE name = %s"
results = list(db.get_db(config.civicrm_schema).execute(sql, (name,)))
if not results:
raise RuntimeError("Db schema missing tag: " + name)
self.id = results[0]["id"]
def __init__(self, name):
self.name = name
sql = "SELECT id FROM donor_review_action WHERE name = %s"
results = list(db.get_db(config.drupal_schema).execute(sql, (name, )))
if not results:
raise RuntimeError("Db schema missing action: " + name)
self.id = results[0]['id']
def __init__(self, name):
self.name = name
sql = "INSERT INTO donor_autoreview_job SET name = %s"
dbc = db.get_db(config.drupal_schema)
dbc.execute(sql, (name, ))
self.id = dbc.last_insert_id()
log.info("This job has ID %d" % self.id)
def __init__(self, name):
self.name = name
sql = "SELECT id FROM donor_review_action WHERE name = %s"
results = list(db.get_db(config.drupal_schema).execute(sql, (name, )))
if not results:
raise RuntimeError("Db schema missing action: " + name)
self.id = results[0]['id']
def create(id_, name, email, profile_pic):
db = get_db()
db.execute(
"INSERT INTO user (googleID, name, email, profile_pic) "
"VALUES (?, ?, ?, ?)",
(id_, name, email, profile_pic),
)
db.commit()
def load_logged_in_user():
user_id = session.get('user_id')
if user_id is None:
g.user = None
else:
g.user = get_db().execute('SELECT * FROM user WHERE id = ?',
(user_id, )).fetchone()
def __init__(self, name):
self.name = name
sql = "SELECT id FROM civicrm_tag WHERE name = %s"
results = list(db.get_db(config.civicrm_schema).execute(sql, (name, )))
if not results:
raise RuntimeError("Db schema missing tag: " + name)
self.id = results[0]['id']
def addMatch(job_id, oldId, newId, action, match):
#log.info("Found a match: {old} -> {new} : {match}".format(old=oldId, new=newId, match=match))
db.get_db(config.drupal_schema).execute("""
INSERT INTO donor_review_queue
SET
job_id = %(job_id)s,
old_id = %(old_id)s,
new_id = %(new_id)s,
action_id = %(action_id)s,
match_description = %(match)s
""", {
'job_id': job_id,
'old_id': oldId,
'new_id': newId,
'action_id': action.id,
'match': match,
})
def addMatch(job_id, oldId, newId, action, match):
#log.info("Found a match: {old} -> {new} : {match}".format(old=oldId, new=newId, match=match))
db.get_db(config.drupal_schema).execute(
"""
INSERT INTO donor_review_queue
SET
job_id = %(job_id)s,
old_id = %(old_id)s,
new_id = %(new_id)s,
action_id = %(action_id)s,
match_description = %(match)s
""", {
'job_id': job_id,
'old_id': oldId,
'new_id': newId,
'action_id': action.id,
'match': match,
})
def __init__(self, user_id=''):
self.user_id = user_id
self.statement = ''
self.arguments = []
self.table = ''
self.set_clause = ''
self.predicate = ''
self.command = ''
self.db = get_db()
def budget():
db = get_db()
user_id = session['user_id']
budget_period = '-'.join((MONTH_STRING[datetime.now().month - 1], str(datetime.now().year)))
print(budget_period)
# Get current month and year and add as parameter for getting budget data
monthly_budget = db.execute("SELECT * FROM budget WHERE user_id = ? AND period = ?", (user_id, budget_period,)).fetchall()
print(len(monthly_budget))
if len(monthly_budget) == 0:
monthly_budget = new_budget_sheet(user_id, budget_period)
return render_template('budget/index.html', budget_sheet=monthly_budget)
def save_item_accounts(access_token):
db = get_db()
try:
account_list = CLIENT.Accounts.get(access_token)
for account in account_list['accounts']:
db.execute("INSERT INTO account VALUES (?, ?, ?, ?, ?, ?, ?)", (account['account_id'], account['mask'], account['name'], account['official_name'], account['type'], account['subtype'], access_token,))
db.commit()
except ItemError as e:
print(e)
save_transactions(access_token, start_date='2019-01-01')
def update_budget():
user_id = session['user_id']
new_value = request.form['new_value']
budget_period = request.form['budget_period']
category = request.form['category']
print(category)
db = get_db()
db.execute("UPDATE budget SET planned = ? WHERE user_id = ? AND period = ? AND category = ?", (new_value, user_id, budget_period, category, ))
db.commit()
return jsonify(new_value)
def seed_db():
from database.seeders import user, items, accounts, budget, categories
db = get_db()
db.execute("INSERT INTO user (name, password) "
"VALUES (?,?)", (
user['user'],
user['password'],
))
db.commit()
for item in items:
db.execute(
"INSERT INTO item (user_id, access_token, id, item_mask, institution) "
"VALUES (?, ?, ?, ?, ?)", (
item['user_id'],
item['access_token'],
item['id'],
item['item_mask'],
item['institution'],
))
db.commit()
for account in accounts:
db.execute(
"INSERT INTO account (id, mask, name, official_name, type, subtype, access_token) "
"VALUES (?, ?, ?, ?, ?, ?, ?)", (
account['id'],
account['mask'],
account['name'],
account['official_name'],
account['type'],
account['subtype'],
account['access_token'],
))
db.commit()
for sheet in budget:
db.execute(
"INSERT INTO budget (user_id, category, planned, actual, period)"
"VALUES (?, ?, ?, ?, ?)", (
sheet['user_id'],
sheet['category'],
sheet['planned'],
sheet['actual'],
sheet['period'],
))
db.commit()
for category in categories:
db.execute("INSERT INTO category (name) VALUES (?)", (category, ))
db.commit()
def get(user_id):
db = get_db()
user = db.execute("SELECT * FROM user WHERE googleID = ?",
(user_id, )).fetchone()
if not user:
return None
user = User(id_=user[0],
name=user[1],
email=user[2],
profile_pic=user[3])
return user
def create_new_category():
user_id = session['user_id']
# Get new category name and budget
new_category = request.form['name']
planned_budget = request.form['planned']
budget_period = '-'.join((MONTH_STRING[datetime.now().month - 1], str(datetime.now().year)))
try:
db = get_db()
db.execute('INSERT INTO budget (user_id, category, planned, actual, period) VALUES (?,?,?,?,?)', (user_id, new_category, planned_budget, 0, budget_period,))
db.commit()
except Exception as e:
print(e)
return jsonify(category=new_category, planned=planned_budget)
def get_totals(wheres=None, query=None, banner=None, campaign=None, country=None, start=None, end=None, **ignore):
'''
Note that the column names must match a heading in the results spreadsheet.
'''
config = get_config()
if not query:
query = db.Query()
query.columns.append('SUM(total_amount) AS total')
query.columns.append('AVG(total_amount) AS mean')
query.columns.append('AVG( IF(total_amount > 20, 20, total_amount) ) AS mean20')
query.columns.append('STD(total_amount) AS sd')
query.columns.append('COUNT(ct.id) AS clicks')
query.columns.append('COUNT(cc.id) AS donations')
query.tables.append(config.contribution_tracking_prefix + 'contribution_tracking ct')
query.tables.append("civicrm_contribution cc ON cc.id = ct.contribution_id")
if wheres:
query.where.extend(wheres)
if campaign:
query.columns.append("utm_campaign AS campaign")
query.where.append("utm_campaign = %(campaign)s")
query.params['campaign'] = campaign
if banner:
query.columns.append(ct_banner_clause + " AS banner")
query.where.append(ct_banner_clause + " = %(banner)s")
query.params['banner'] = banner
if country:
query.where.append("country = %(country)s")
query.params['country'] = country
if start and end:
query.where.append("ts BETWEEN %(start)s AND %(end)s")
query.params['start'] = start
query.params['end'] = end
if not query.where:
raise Exception("Don't try this query without a where clause.")
result = list(db.get_db().execute(query))
row = result.pop()
# nasty hack for json encoding snafu:
for k, v in row.items():
if isinstance(v, decimal.Decimal):
row[k] = str(v)
# add some goodies:
row['sql'] = str(query)
row['updated'] = time_util.str_now()
return row
def delete_extra():
db = database.get_db()
extr = [
'36806', '1241244', '1010452', '2175049', '2318427', '2291823',
'617150', '29082', '9028', '2588349', '20444', '2084359', '8463',
'19209', '2508155', '2951758', '108073', '34590', '2986', '2490247',
'2949307', '3467', '681231', '1291013', '28496', '3028', '1861131',
'34448', '1141633', '17781', '660', '27675', '2495945', '24689',
'19145', '2134654', '31720', '2538987', '28987', '4466'
]
for e in extr:
db.execute("DELETE FROM places WHERE id={}".format(e))
db.execute("DELETE FROM records WHERE published_in={}".format(e))
db.commit()
def get_impressions(campaign=None, banner=None, **ignore):
query = db.Query()
query.columns.append("SUM(count) AS count")
query.tables.append("{impressions_db}bannerimpressions".format(impressions_db=config.impressions_prefix))
if campaign:
query.where.append("campaign = %(campaign)s")
query.params['campaign'] = campaign
if banner:
query.where.append("banner = %(banner)s")
query.params['banner'] = banner
result = list(db.get_db().execute(query))
row = result.pop()
return row['count']
def login():
db = get_db()
username = request.form['username']
password = request.form['password']
user = db.execute('SELECT * FROM user WHERE name = ?',
(username, )).fetchone()
error = None
if user is None:
error = 'Incorrect username.'
elif not check_password_hash(user['password'], password):
error = 'Incorrect password.'
if error is None:
session.clear()
session['user_id'] = user['id']
return redirect(url_for('dashboard.home'))
flash(error)
def save_transactions(access_token, start_date='{:%Y-%m-%d}'.format(datetime.date.today() - datetime.timedelta(days=7)), end_date='{:%Y-%m-%d}'.format(datetime.date.today())):
db = get_db()
start = start_date
end = end_date
# Check if data has already been saved in DB
check_response = db.execute('SELECT id FROM activity WHERE date >= ? AND date <= ?',
(start, end,)).fetchall()
existing = []
for check in check_response:
existing.append(check['id'])
if len(check_response) is None:
print("empty")
try:
transaction_details = CLIENT.Transactions.get(access_token, start_date=start, end_date=end)
# print(response['accounts'])
# print(response['item'])
transactions = format_category(transaction_details['transactions'])
for data in transactions:
print(data['transaction_id'])
if data['transaction_id'] not in existing:
params = (data['account_id'],
data['amount'],
str(data['category']),
data['category_id'],
data['date'],
data['iso_currency_code'],
data['name'],
data['pending'],
data['pending_transaction_id'],
data['transaction_id'],
data['transaction_type'],
data['category_type'],
data['category_name'],
data['sub_category'],
'')
db.execute("INSERT INTO activity VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", params)
db.commit()
except plaid.errors.PlaidError as e:
print(e)
def reviewBatch(self):
'''For each new contact, find the oldest contact with the same email address.'''
matchDescription = EmailMatch("Exact match").json()
self.contactCache.fetch()
for contact in self.contactCache.contacts:
if contact['email']:
query = db.Query()
query.columns = [
'MIN(contact_id) AS contact_id',
]
query.tables = [
'civicrm_email',
]
query.where.extend([
'email = %(email)s',
'contact_id < %(new_id)s',
])
query.group_by.extend([
'email',
])
query.params = {
'new_id': contact['id'],
'email': contact['email'],
}
result = db.get_db().execute(query)
if result:
for row in result:
ReviewQueue.addMatch(self.job_id, row['contact_id'], contact['id'], Autoreview.REC_DUP, matchDescription)
ReviewQueue.tag(contact['id'], QuickAutoreview.QUICK_REVIEWED)
if not self.contactCache.contacts:
log.warn("Searched an empty batch of contacts!")
else:
last_seen = self.contactCache.contacts[-1]['id']
log.info("End of batch. Last contact scanned was ID {id}".format(id=last_seen))
def register():
if request.method == 'POST':
username = request.form['username']
password = request.form['password']
db = get_db()
error = None
if not username:
error = 'Username is required'
elif not password:
error = 'Password is required'
elif db.execute('SELECT id FROM user WHERE name = ?',
(username, )).fetchone() is not None:
error = 'User () is already registered'.format(username)
if error is None:
db.execute('INSERT INTO user (name, password) VALUES (?,?)',
(username, generate_password_hash(password)))
db.commit()
return redirect(url_for('dashboard.home'))
flash(error)
return render_template('auth/register.html')
def db_assist(command, table, query=None, args=None, opts=None):
response = None
if command == 'select':
s = select(table, query)
print(s)
if query is not None:
response = get_db().execute(
s,
args,
).fetchall()
else:
response = get_db().execute(s)
elif command == 'insert':
x = insert(table, query, args)
get_db().execute(x, query)
get_db().commit()
return
return response
def new_budget_sheet(user_id, budget_period):
db = get_db()
print(budget_period)
prev_budget_period = '-'.join(
(MONTH_STRING[datetime.now().month - 2], str(datetime.now().year - 1)))
print(prev_budget_period)
new_sheet = db.execute(
"SELECT * FROM budget WHERE user_id = ? AND period = ?", (
user_id,
prev_budget_period,
)).fetchall()
print(new_sheet[0])
monthly_budget = []
for item in new_sheet:
budget_item = {
'user_id': user_id,
'category': item['category'],
'planned': item['planned'],
'actual': 0,
'period': budget_period
}
monthly_budget.append(budget_item)
print(budget_item)
db.execute(
"INSERT INTO budget (user_id, category, planned, actual, period) VALUES (?, ?, ?, ?, ?)",
(
budget_item['user_id'],
budget_item['category'],
budget_item['planned'],
budget_item['actual'],
budget_item['period'],
))
db.commit()
return monthly_budget
def UpServices(self):
self.app = app.test_client()
self.db = db.get_db()