def populate_new_db():
    from flask_security.utils import encrypt_password
    #init_db()
    #user_client = db_session.query(Client).filter_by(abbreviation='MCE').first()
    #print(user_client.name)
    mce = Client(name='Marin Clean Energy',
                 domain='mcecleanenergy.org',
                 abbreviation='MCE')
    svce = Client(name='Silicon Valley Clean Energy',
                  domain='svcleanenergy.org',
                  abbreviation='SVCE')
    scp = Client(name='Sonoma Clean Power',
                 domain='sonomacleanpower.org',
                 abbreviation='SCP')
    pce = Client(name='Peninsula Clean Energy',
                 domain='peninsulacleanenergy.com',
                 abbreviation='PCE')
    #maher = Client(name='Maher Accountancy', domain='mahercpa.com',abbreviation='MAHER')
    for c in [mce, scp, pce, svce]:
        db_session.add(c)

    user_datastore.create_user(email='*****@*****.**',
                               password=encrypt_password('password'),
                               confirmed_at=datetime(2017, 1, 1))
    user_datastore.create_user(email='*****@*****.**',
                               password=encrypt_password('password'),
                               confirmed_at=datetime(2017, 1, 1))

    for r in ['admin', 'mce', 'scp', 'pce', 'svce']:
        user_datastore.create_role(name=r)

    db_session.commit()

    #Assing Default Roles
    for r in ['admin', 'mce', 'scp', 'pce', 'svce']:
        user_datastore.add_role_to_user(
            user_datastore.get_user('*****@*****.**'), r)

    user_datastore.add_role_to_user(
        user_datastore.get_user('*****@*****.**'), 'scp')

    #Assing Default Clients
    user = user_datastore.get_user('*****@*****.**')
    for c in [mce, scp, pce, svce]:
        c.users.append(user)

    scp.users.append(user_datastore.get_user('*****@*****.**'))

    db_session.commit()
    return Response('OK!')
def dash_redirect():
    user = user_datastore.get_user(current_user.email)
    if client is None:
        client = user.client[0].abbreviation

    if not current_user.has_role(client):
        abort(403)
    re_url = "/client/{}/dash".format(client.lower())
    return redirect((re_url))
def budget_redirect():
    user = user_datastore.get_user(current_user.email)
    if user is not None:
        client = user.client[0].abbreviation

    if user is None:
        abort(403)

    re_url = "/client/{}/budget".format(client.lower())
    return redirect((re_url))
def add_user_to_client(client, user_name):
    client = db_session.query(Client).filter_by(
        abbreviation=client.upper()).first()
    user = user_datastore.get_user(user_name)
    print(client.name)
    print(user.email)
    if client is not None and user is not None:
        print(client.name, user.email)
        client.users.append(user)
        db_session.commit()
    return Response(user.email)
def show_client_data(client_abbrev):
    user = user_datastore.get_user(current_user.email)
    if not current_user.has_role(client_abbrev):
        abort(403)

    client = client_abbrev
    cdata = data.__dict__[client]

    #print(type(current_user.clients))
    #client_list = "client(s): " + ','.join([i.domain for i in user.client])
    #role_list = "roles(s): " + ','.join([i.name for i in user.roles])
    #return render_template('blank.html', content=cdata.table_statistics.to_html())
    return redirect()
def budget(client=None, dept_id=None):
    ## SETUP CLIENT
    user = user_datastore.get_user(current_user.email)
    if client is None:
        client = user.client[0].abbreviation

    if not current_user.has_role(client):
        abort(403)

    ##Get Data for Menu (budget departments)
    departments = []
    [
        departments.append({i.id: i.name}) for i in (
            db_session.query(HeirarchyNode).join(Client, HeirarchyVersion).
            filter(Client.abbreviation.ilike(client), HeirarchyVersion.name ==
                   'Budget', HeirarchyNode.parent_id == None).order_by(
                       HeirarchyNode.name))
    ]

    sidebar = {}
    sidebar['client'] = client
    sidebar['departments'] = departments

    #get current budget department
    dept = (db_session.query(HeirarchyNode).join(
        Client, HeirarchyVersion).filter(Client.abbreviation.ilike(client),
                                         HeirarchyVersion.name == 'Budget',
                                         HeirarchyNode.id == dept_id)).first()

    cdata = data.__dict__[client.lower()]
    rba = rpt_budget_dept(cdata)
    rbp = rpt_present(rba.get_budget_actual_bullet_charts(dept.name))
    blt = rbp.html()
    vendor_detail = rpt_present(rba.get_vendor_detail(dept.name))

    print(vendor_detail.df.info())

    vendor_tbl = rpt_present(rba.get_vendor_monthly_spend(dept.name)).html(
        html_id="vendors", escape=False, index=False)

    return render_template('budget_expense.html',
                           budget_line_table=blt,
                           vendor_tbl=vendor_tbl,
                           vendor_detail=vendor_detail.html(html_id="vendors",
                                                            index=False),
                           sb=sidebar,
                           dept=dept)
def get_vendor_json(client, dept_id):
    dept = (db_session.query(HeirarchyNode).join(
        Client, HeirarchyVersion).filter(Client.abbreviation.ilike(client),
                                         HeirarchyVersion.name == 'Budget',
                                         HeirarchyNode.id == dept_id)).first()

    ## SETUP CLIENT
    user = user_datastore.get_user(current_user.email)
    if client is None:
        client = user.client[0].abbreviation

    if not current_user.has_role(client):
        abort(403)

    cdata = data.__dict__[client.lower()]
    rba = rpt_budget_dept(cdata)
    #vendor_detail = rpt_present(rba.get_vendor_detail('Outreach and communications','wpONcall'))
    vendor_tbl = rpt_present(
        rba.get_vendor_detail(dept.name, 'wpONcall')
        #rba.get_vendor_monthly_spend('Outreach and communications')
    ).df.to_json(orient='records')  #.datatables()

    return jsonify(rba.budget_spend_detail(dept.name))  #Response(vendor_tbl)
def dash(client=None):
    ## SETUP CLIENT
    print(client)
    user = user_datastore.get_user(current_user.email)
    if client is None:
        client = user.client[0].abbreviation

    if not current_user.has_role(client):
        abort(403)

    departments = []
    [
        departments.append({i.id: i.name}) for i in (
            db_session.query(HeirarchyNode).join(Client, HeirarchyVersion).
            filter(Client.abbreviation.ilike(client), HeirarchyVersion.name ==
                   'Budget', HeirarchyNode.parent_id == None).order_by(
                       HeirarchyNode.name))
    ]

    sidebar = {}
    sidebar['client'] = client
    sidebar['departments'] = departments

    ##Prep Dashboard
    #file = open('data/usage_comparison.csv','r')
    #usage_comparison = file.read()
    dt, col = datasources.query_usage_table()
    invoice_matrix = invoice_usage_matrix('pce','usage_total_kwh', 20161101, 20171101) \
                        .to_html(classes = 'table table-hover table-small-row" id="tblUsageInvoice', border = 0, \
                        float_format=lambda x: '{:,.0f}'.format(x))
    role_list = [i.name for i in core.current_user.roles]
    role_list = ','.join(role_list)
    print(role_list)
    return render_template('dash_content_test.html', \
        usage_comparison=usage_comparison, \
        dt_data = (dt), dt_cols = (col), client=client, role_names = role_list, invoice_matrix = invoice_matrix, sb=sidebar)
def whoami():
    user = user_datastore.get_user(current_user.email)
    client_list = "client(s): " + ','.join([i.domain for i in user.client])
    role_list = "roles(s): " + ','.join([i.name for i in user.roles])
    return Response(user.email + ": " + client_list + "; " + role_list)
def delete_user(user_name):
    user = user_datastore.get_user(user_name)
    user_datastore.delete_user(user)
    db_session.commit()
    return Response(user_name)