apps/admin_portal.py

# -*- coding: utf-8 -*-
"""
Created on Tue Jan 25 10:35:00 2022

@author: bullm
"""
import streamlit as st
from datetime import datetime
from datetime import timedelta
import pandas as pd
import os
import boto3
import json
import io
from sqlalchemy import create_engine
import psycopg2
import hashlib
import numpy as np
from Data.credentials import credentials_s3 as creds3
from Data.credentials import credentials_postgresql as credpost


def run_query(query):
    url = credpost["POSTGRESQL"]
    conn = psycopg2.connect(url, sslmode='require')
    cur = conn.cursor()
    cur.execute(query)
    conn.commit()
    cur.close() 
    conn.close()

def hashing(passw):
    return hashlib.sha256(passw.encode()).hexdigest()



def style_table():
    style_table = """
                <style>
                tbody tr:hover {
                           color:#BB1114;}
                thead {
                      background-color:#BB1114 ;
                      color: #E8E8E8;
                    }
                tbody tr:nth-child(odd) {
                      background-color: #fff;
                    }
                    tbody tr:nth-child(even) {
                      background-color: #eee;
                    }
                tbody tr:nth-child(odd)
                stTable {
                    border-collapse: collapse;
                    margin: 25px 0;
                    font-size: 0.9em;
                    min-width: 400px;
                    box-shadow: 0 0 20px rgba(0, 0, 0, 0.15);
                    }
                </style>
                """
    st.markdown(style_table, unsafe_allow_html=True)


def read_excel_s3(key, secret_key, bucket, path):
    s3_client = boto3.client('s3', aws_access_key_id = key, aws_secret_access_key= secret_key)
    response =  s3_client.get_object(Bucket=bucket, Key=path)
    data = response["Body"].read()
    df = pd.read_excel(io.BytesIO(data))
    return df


def logs_portal():
    style_table()
    key = creds3["S3_KEY_ID"]
    secret_key = creds3["S3_SECRET_KEY"]
    bucket = creds3["S3_BUCKET"]
    path ='Logs.xlsx'
    
    df = read_excel_s3(key, secret_key, bucket, path)[["Analista", "Fecha",
                                                       "Vista", "Subvista"]]
    update_data = pd.read_excel('Data/update_data.xlsx', engine='openpyxl')
    update_data.index =  update_data["View"]
    
    col1, col2 = st.beta_columns((3,1))
    fecha_inicio = col1.date_input("Fecha inicio")
    st.write(fecha_inicio)
    fecha_inicio = datetime.combine(fecha_inicio, datetime.min.time())
    df=df[df["Fecha"]> fecha_inicio]
    # col2.table(update_data[["Last_Update"]])
    # col2.table(df.groupby("Analista").count()["Vista"])
    # col1.table(df.groupby(["Vista"]).count())
    # col2.table(df.groupby(["Vista", "Subvista"]).count())
    df["Count"] =1
    table = pd.pivot_table(df, values ='Count', index='Vista',
                    columns='Analista', aggfunc=np.sum)
    table.drop(columns=["bull"], inplace=True)
    col1.table(table.fillna(0))
    col2.table(table.sum(axis=1))
    col1.table(table.sum(axis=0).T)
    # table = pd.pivot_table(df, values ='Count', index='Subvista',
    #                 columns='Analista', aggfunc=np.sum)
    # col1.table(table.fillna(0))


def edit_credentials():
    url = credpost["POSTGRESQL"]
    engine = create_engine(url, echo=False)
    data = pd.read_sql_query("""select * from credenciales""", con=engine)
    col1, col2, col3 = st.beta_columns(3)
    with col1.form('New user'):
        user = st.text_input("Usuario")
        passw = st.text_input("Contraseña", type="password")
        passw2 = st.text_input("Repetir Contraseña", type="password")
        mail = st.text_input("Mail")
        nombre = st.text_input("Nombre")
        area = st.text_input("Area")
        cargo = st.text_input("Cargo")
        ingresar = st.form_submit_button(label='Ingresar')
        if ingresar:
            if passw == passw2:
                var = "(usuario, passw, area, cargo, mail, nombre)"
                varlist = [user, hashing(passw), area, cargo, mail, nombre]
                query = "INSERT INTO credenciales {Var}  VALUES %r; ".format(Var=var) % (tuple(varlist),)
                run_query(query)
                st.info("Usuario agregado")
    with col2.form('Edit User'):
        user = st.selectbox("Seleccionar un Usuario", list(data["usuario"]))
        passw = st.text_input("Nueva Contraseña", type="password")
        passw2 = st.text_input("Repetir Contraseña", type="password")
        area = st.text_input("Area")
        cambiar = st.form_submit_button(label='Ingresar')
        if cambiar:
            if ingresar:
                if passw == passw:
                    h_passw = hashing(passw)
                    query='''UPDATE credenciales
                            SET passw = '{}',
                            area = '{}',
                            WHERE user = '{}';
        
                        '''.format(h_passw, area, user)
                    run_query(query)
                    st.info("Usuario editado")
                else:
                    st.error("Las contraseñas no coinciden")
    with col3.form('Delete User'):
        user_d = st.selectbox("Seleccionar un Usuario", list(data["usuario"]))
        user_d2 = st.text_input("Confirmar usuario")
        delete = st.form_submit_button(label='Delete')
        if delete:
            if user_d == user_d2:
                query = """delete from credenciales where usuario='{}';""".format(user_d)
                run_query(query)
                st.info("Usuario elemindo")
            else:
                st.info("Usuarios no coinciden")
    st.table(data[["usuario", "nombre", "area", "cargo"]])