#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
Created on Tue Nov 9 15:17:01 2021
@author: benjaminull
"""
import streamlit as st
import pandas as pd
from PIL import Image
import base64
import streamlit.components.v1 as components
from pathlib import Path
# from modules.quill.streamlit_quill import quill
from streamlit_quill import st_quill
import smtplib
import email.message
from streamlit_tags import st_tags
from st_aggrid import AgGrid
from st_aggrid.shared import GridUpdateMode
from st_aggrid.grid_options_builder import GridOptionsBuilder
from sqlalchemy import create_engine
from datetime import datetime
from datetime import date
import json
from streamlit_timeline import timeline
import ast
from logs_portal import log
import boto3
from Data.credentials import credentials_s3 as creds3
from Data.credentials import credentials_postgresql as credpost
from Data.credentials import credentials_mailer as credmail
from streamlit_lottie import st_lottie
def leer_ultima_nota(empresa):
url = credpost["POSTGRESQL"]
engine = create_engine(url, echo=False)
data = pd.read_sql_query("""SELECT Nota FROM notas_analistas
where empresa ='{Empresa}'
order by Date_nota Desc""".format(
Empresa=empresa), con=engine)
if len(data) > 0:
nota = data.iloc[0]["nota"]
else:
nota = 0
return nota
def style_title():
style = """
"""
st.markdown(style, unsafe_allow_html=True)
@st.experimental_memo
def leer_notas():
url = credpost["POSTGRESQL"]
engine = create_engine(url, echo=False)
data = pd.read_sql_query(
"Select Id_quant, Nota from notas_analistas ORDER BY date_nota desc",
con=engine)
data.columns = ["Id_Quant", "Nota"]
data = data.drop_duplicates("Id_Quant")
return dict(zip(list(data["Id_Quant"]), list(data["Nota"])))
def load_image(image_file):
img = Image.open(image_file)
return img
def img_to_bytes(img_path):
img_bytes = Path(img_path).read_bytes()
encoded = base64.b64encode(img_bytes).decode()
return encoded
def display_table(df: pd.DataFrame):
# Configure AgGrid options
gb = GridOptionsBuilder.from_dataframe(df)
gb.configure_selection('single')
return AgGrid(
df, gridOptions=gb.build(),
update_mode=GridUpdateMode.MODEL_CHANGED,
enable_enterprise_modules=True,
height=400)
def display_table2(df: pd.DataFrame):
# Configure AgGrid options
gb = GridOptionsBuilder.from_dataframe(df)
gb.configure_selection(selection_mode="single", use_checkbox=True,)
return AgGrid(
df, gridOptions=gb.build(),
update_mode=GridUpdateMode.SELECTION_CHANGED,
enable_enterprise_modules=True)
def write_s3(key, secret_key, new_file, bucket, path):
s3 = boto3.resource('s3', aws_access_key_id=key,
aws_secret_access_key=secret_key)
s3object = s3.Object(bucket, path)
s3object.put(Body=(bytes(json.dumps(new_file).encode('UTF-8'))))
# @st.experimental_memo
def read_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)
data2 = response["Body"].read().decode()
return data2
import streamlit_modal as modal
import streamlit.components.v1 as components
import extra_streamlit_components as stx
def is_open():
return st.session_state.get('modal_is_open', False)
def open_():
st.session_state.modal_is_open = True
st.experimental_rerun()
def close():
st.session_state.modal_is_open = False
st.experimental_rerun()
@st.experimental_memo
def get_noticias_df():
key = creds3["S3_KEY_ID"]
secret_key = creds3["S3_SECRET_KEY"]
bucket = creds3["S3_BUCKET"]
path = 'noticias.json'
data2 = read_s3(key, secret_key, bucket, path)
eventos = list(ast.literal_eval(data2).values())
eventos2 = []
empresas = []
paises = []
lv1s = []
analistas =[]
text=[]
titulo = []
fecha = []
indice = []
notas = []
noticias = pd.DataFrame()
for i, eve in enumerate(eventos):
try:
year = int(eve["start_date"]["year"])
month = int(eve["start_date"]["month"])
day = int(eve["start_date"]["day"])
indice.append(i)
fecha.append(date(day=day, month=month, year=year))
analistas.append(eve["analista"])
text.append(eve["text"]["text"])
titulo.append(eve["text"]["headline"])
notas.append(eve["notas"])
except Exception as exc:
st.write(exc)
pass
id_text_dict = dict(zip(indice, text))
id_notas_dict = dict(zip(indice, notas))
noticias["Fecha"] = fecha
noticias["Analista"]= analistas
noticias["Titulo"] = titulo
noticias["ID"] = indice
return noticias, id_text_dict
# def
# @log
def ver_mails():
chosen_id = stx.tab_bar(data=[
stx.TabBarItemData(id=1, title="Tabla de noticias", description=""),
stx.TabBarItemData(id=2, title="Linea del tiempo", description=""),
], default=1)
# timeline2 = st.checkbox("Linea de tiempo")
data = pd.pandas.read_excel("Data/Company_Base_Definitivo.xlsx",
sheet_name="Compilado", engine="openpyxl")
if chosen_id =='2':
key = creds3["S3_KEY_ID"]
secret_key = creds3["S3_SECRET_KEY"]
bucket = creds3["S3_BUCKET"]
path = 'noticias.json'
data2 = read_s3(key, secret_key, bucket, path)
eventos = list(ast.literal_eval(data2).values())
eventos2 = []
empresas = []
paises = []
lv1s = []
analistas = []
for eve in eventos:
try:
empresas = empresas + list(eve["notas"].keys())
paises = paises + list(eve["tags"]["paises"])
lv1s = lv1s + list(eve["tags"]["LV1"])
analistas.append(eve["analista"])
eventos2.append(eve)
except Exception as exc:
st.write(exc)
pass
empresas = sorted(list(set(empresas)))
analistas = sorted(list(set(analistas)))
empresas_dict =dict(zip(data['ID_Quant'], data["Short_Name"]))
empresas = sorted([empresas_dict[int(i)] for i in empresas])
paises = sorted(list(set(paises)))
lv1s = sorted(list(set(lv1s)))
col1, col2 = st.columns(2)
Dict = {
"title": {
"media": {
},
"text": {
"headline": "Noticias Equity Latam",
"text": " "
}
}}
filtro = col1.selectbox("Filtrar por",
["-", "Analista", "Empresa", "Pais", "LV1"])
if filtro != "-":
eventos2 = []
if filtro == "Empresa":
emp = col2.selectbox("", empresas)
for eve in eventos:
if emp in list(eve["notas"].keys()):
eventos2.append(eve)
elif filtro == "Analista":
an = col2.selectbox("", analistas)
for eve in eventos:
if eve["analista"] == an:
eventos2.append(eve)
elif filtro == "Pais":
pais = col2.selectbox("", paises)
for eve in eventos:
if pais in eve["tags"]["paises"]:
eventos2.append(eve)
elif filtro == "LV1":
lv1 = col2.selectbox("", lv1s)
for eve in eventos:
if lv1 in eve["tags"]["LV1"]:
eventos2.append(eve)
Dict["events"] = eventos2
timeline(Dict, height=800)
else:
noticias, id_text_dict = get_noticias_df()
placeh = st.empty()
empresas_dict =dict(zip(data['ID_Quant'], data["Short_Name"]))
col1, col2, col3= st.columns((3,0.5,5))
with col1:
select = display_table2(noticias)
a = select["selected_rows"].copy()
if len(a)==1:
# ver = col2.button('Ver mail')
# col3.title(" ")
# col3.write(" ")
# emp_nota_dict = id_notas_dict[select["selected_rows"][0]["ID"]]
# for emp in emp_nota_dict.keys():
# col2.write(empresas_dict[int(emp)])
# col3.write(str(emp_nota_dict[emp]))
# # if ver:
# # modal.open()
# if modal.is_open():
with col3:
st.title(select["selected_rows"][0]["Titulo"])
text = id_text_dict[select["selected_rows"][0]["ID"]]
components.html(text, height=900, scrolling=True)
# else:
# s = 0
# col1, col2, col3, col4 = st.columns(4)
# an = col1.selectbox("Analista", ["-"] + analistas)
# emp = col2.selectbox("Empresa", ["-"] + empresas)
# pais = col3.selectbox("Pais", ["-"] + paises)
# lv1 = col4.selectbox("LV1", ["-"] + lv1s)
# for evento in eventos:
# if emp in list(evento["notas"].keys()) or emp == '-':
# if evento["analista"] == an or an == '-':
# if lv1 in evento["tags"]["LV1"] or lv1 == '-':
# if pais in evento["tags"]["paises"] or pais == '-':
# col1, col2, col3 = st.columns((1, 1, 3))
# col1.write(evento["start_date"]["year"] + "-"
# + evento["start_date"]
# ["month"] + "-"
# + evento["start_date"]["day"])
# col2.write(evento["analista"])
# with col3.expander(evento["text"]["headline"]):
# st.title(evento["text"]["headline"])
# components.html(evento["text"]["text"],
# height=800, scrolling=True)
# s += 1
@st.experimental_memo
def read_cb():
cb = pd.pandas.read_excel("Data/Company_Base_Definitivo.xlsx",
sheet_name="Compilado", engine="openpyxl")
return cb
# @log
def escribir_mails2():
if "value" not in st.session_state:
st.session_state.value = ""
cols1, cols2= st.sidebar.columns((3,1))
name = st.session_state["name"]
cargo = st.session_state["cargo"]
place = cols2.empty()
notas_d = leer_notas()
key = creds3["S3_KEY_ID"]
secret_key = creds3["S3_SECRET_KEY"]
bucket = creds3["S3_BUCKET"]
path = 'noticias.json'
# st.sidebar.subheader("Borradores")
cols1, cols2, cols3 = st.columns((5,5,5))
data2 = read_cb()
dic_emp_ticker = dict(zip(list(data2["Short_Name"]), list(data2["Ticker"])))
dic_emp_pais = dict(zip(list(data2["Short_Name"]), list(data2["Country"])))
dic_emp_lv1 = dict(zip(list(data2["Short_Name"]), list(data2["LV1"])))
industrias = []
for a in list(set(data2["LV1"])):
for b in list(set(data2["Country"])):
industrias.append(a + " - " + b)
empresa = cols1.multiselect("Seleccione empresas o industrias",
sorted(list(data2["Short_Name"]) + industrias))
w = 0
notas = {}
to_tags = []
placeholder = ""
dic_emp = dict(zip(list(data2["Short_Name"]), list(data2["ID_Quant"])))
value = ""
if st.sidebar.checkbox("Ver Borradores"):
with st.sidebar:
json_file = open('Data/borradores.json','r')
json_obj = json.load(json_file)
eventos = json_obj[st.session_state.key]
borr = pd.DataFrame.from_dict(eventos, orient='index').reset_index()
borr.columns = ["Borradores", "html"]
dict_html = dict(zip(list(borr["Borradores"]), list(borr["html"])))
a = display_table(borr[["Borradores"]].iloc[::-1])
b = a["selected_rows"]
if len(b) > 0:
b = b[0]
st.session_state.value = dict_html[b["Borradores"]]
col1, col2, col3, col4 = st.columns((3, 1, 1, 10))
col2.markdown('
', unsafe_allow_html=True)
col3.markdown('
', unsafe_allow_html=True)
for emp in empresa:
try:
tick = dic_emp_ticker[emp]
except:
tick = emp
pass
col1.markdown(
'Empresa
',
unsafe_allow_html=True)
col1.markdown('' + tick + "
",
unsafe_allow_html=True)
try:
ult_nota = notas_d[dic_emp[emp]]
except Exception:
ult_nota = 0
col2.markdown(
'Ultima nota
',
unsafe_allow_html=True)
col2.markdown(f'{ult_nota}
',
unsafe_allow_html=True)
if emp in industrias:
notas[emp] = col3.selectbox("Nota de la noticia" + " " * w,
[1, 2, 3, 4, 5])
to_tags.append(emp)
else:
notas[dic_emp[emp]] = col3.selectbox("Nota noticia" +
" "*w,
[1, 2, 3, 4, 5])
to_tags.append(dic_emp_pais[emp])
to_tags.append(dic_emp_lv1[emp])
placeholder = placeholder + "" + emp + ":
Nota Empresa " + \
str(ult_nota) + "
Nota noticia:" + str(notas[dic_emp[emp]])+ "
"
w += 1
placeholder = '' + name+'
' + placeholder
mail_destino = cols2.selectbox("To:", ['equitylatam@larrainvial.com',
'bullm@larrainvial.com',
'lvamlatam@larrainvial.com'
])
cc = cols3.text_input("CC")
with col4:
asunto = st.text_input("Asunto")
st.write("")
st.markdown(placeholder, unsafe_allow_html=True)
# if value == "" or len(b) == 0:
# content = quill.st_quill(html=True, key="quill")
# else:
content = st_quill(html=True, value=st.session_state["value"])
content = content.replace("\\", "")
# attach = st.file_uploader("Añade archivos",
# accept_multiple_files=True)
st.write(len(content))
tags = st_tags(
value=to_tags,
label='Tags:',
text='Añadir Tags',
suggestions=sorted(
list(data2["Short_Name"])+list(set(data2["Country"])) +
list(set(data2["LV1"]))),
key='1')
button = st.button("Enviar")
button2 = st.button("Guardar borrador")
if button:
if content[0] == '"':
content = content[1:]
if content[-1] == '"':
content = content[:-1]
empresas = list(notas.keys())
paises = [dic_emp_pais.get(key) for key in empresas]
lv1s = [dic_emp_lv1.get(key) for key in empresas]
analista = st.session_state.key
server = smtplib.SMTP('smtp.gmail.com:587')
email_content = placeholder + content
msg = email.message.Message()
msg['Subject'] = asunto
rcpt = cc.split(",") + [mail_destino]
msg['To'] = mail_destino
msg['Cc'] = cc
msg.add_header('Content-Type', 'text/html')
msg.set_payload(email_content)
s = smtplib.SMTP('smtp.gmail.com: 587')
s.starttls()
a_file = read_s3(key, secret_key, bucket, path)
json_object = json.loads(a_file)
eventos = list(json_object.values())
fecha = {
"year": str(date.today().year),
"month": str(date.today().month),
"day": str(date.today().day)
}
contenido = {
"headline": asunto,
"text": email_content
}
otros_tags = []
tags_pais = []
tags_lv1 = []
if type(tags) is str:
tags = ast.literal_eval(tags)
for i in tags:
if i in list(set(dic_emp_pais.values())):
tags_pais.append(i)
elif i in list(set(dic_emp_lv1.values())):
tags_lv1.append(i)
else:
otros_tags.append(i)
nueva_not = {
"start_date": fecha,
"text": contenido,
"notas": notas,
"analista": analista,
"tags": {"LV1": tags_lv1,
"paises": tags_pais,
"otros": otros_tags}
}
indice =int(list(json_object.keys())[-1])+1
json_object[indice] = nueva_not
write_s3(key, secret_key, json_object, bucket, path)
msg['From'] = credmail['EMAILSENDER']
password = credmail['EMAILPASS']
s.login(msg['From'], password)
s.sendmail(msg['From'], rcpt,
msg.as_string().encode('utf-8'))
# st.markdown(placeholder + content, unsafe_allow_html=True)
st.experimental_memo.clear()
st.success("Mail enviado correctamente")
if len(content)>0:
with open('Data/borradores.json', 'r') as json_file:
json_object = json.load(json_file)
if content[0] == '"':
content = content[1:]
if content[-1] == '"':
content = content[:-1]
event = json_object[st.session_state.key]
title = date.today().strftime('%Y-%m-%d ' + asunto)
event[title] = content
json_object[st.session_state.key] = event
path_b='borradores.json'
with open('Data/borradores.json', 'w') as outfile:
json.dump(json_object, outfile)
outfile.close()
# st.write(attach)
# st.write(type(attach[0]))
st.info("borrador guardado")
place.empty()
# src='https://app.powerbi.com/reportEmbed?reportId=296fa839-a397-4238-bf82-c0729edb1bde&autoAuth=true&ctid=c174949a-fefa-49fe-842c-3f378af3a546&config=eyJjbHVzdGVyVXJsIjoiaHR0cHM6Ly93YWJpLXNvdXRoLWNlbnRyYWwtdXMtcmVkaXJlY3QuYW5hbHlzaXMud2luZG93cy5uZXQvIn0%3D'
# st.components.v1.iframe(src, width=None, height=700, scrolling=False)