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,
         })
Exemple #3
0
 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,
         })
Exemple #4
0
    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)
Exemple #5
0
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'])
Exemple #6
0
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')
Exemple #7
0
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']
Exemple #15
0
    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)
Exemple #16
0
    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']
Exemple #17
0
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()
Exemple #18
0
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,
         })
Exemple #21
0
 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,
         })
Exemple #22
0
 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()
Exemple #23
0
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)
Exemple #24
0
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')
Exemple #25
0
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)
Exemple #26
0
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()
Exemple #27
0
    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
Exemple #28
0
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()
Exemple #31
0
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']
Exemple #32
0
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)
Exemple #33
0
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)
Exemple #34
0
    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))
Exemple #35
0
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')
Exemple #36
0
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
Exemple #37
0
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()