reports.account_statement

  1from datetime import datetime
  2from dictionary.sql import user_current_accounts_query, expenses_statement_query, revenues_statement_query
  3from functions.login import Login
  4from dictionary.vars import to_remove_list, today, absolute_app_path
  5from dictionary.style import system_font
  6from fpdf import FPDF
  7from functions.get_actual_time import GetActualTime
  8from functions.query_executor import QueryExecutor
  9from functions.login import Login
 10from time import sleep
 11import pandas as pd
 12import streamlit as st
 13
 14
 15class AccountStatement:
 16    """
 17    Classe que representa os extratos bancários das contas do usuário.
 18    """
 19
 20    def consult_statement(self, statement_query_option: str, accounts: list, initial_data: str, final_data: str):
 21        """
 22        Realiza a consulta do extrato bancário de acordo com as consultas passadas como parâmetro.
 23
 24        Parameters
 25        ----------
 26        statement_query_option : str
 27            A opção de extrato selecionada pelo usuário.
 28        accounts : list
 29            Lista com as contas a serem consultadas.
 30        initial_data: str
 31            A data inicial da consulta.
 32        final_data: str
 33            A data final da consulta.
 34
 35        Returns
 36        -------
 37        value_list : list
 38            A lista com os valores do extrato.
 39        data_df_list : list
 40            A lista com os gráficos estruturados.
 41        """
 42        user_name, user_document = Login().get_user_data(return_option="user_doc_name")
 43
 44        value_list = []
 45        data_df_list = []
 46
 47        placeholders = ", ".join(["%s"] * len(accounts))
 48
 49        # Modificando a consulta para usar os placeholders corretamente
 50        revenues_query = revenues_statement_query.replace("IN %s", f"IN ({placeholders})")
 51        expenses_query = expenses_statement_query.replace("IN %s", f"IN ({placeholders})")
 52
 53        if statement_query_option == "Receitas e Despesas":
 54            empty_list = QueryExecutor().complex_compund_query(revenues_query, 6, params=(initial_data, final_data, *accounts, user_name, user_document))
 55            description, value, date, time, category, account = (empty_list)
 56
 57            if len(description) == 0 and len(value) == 0 and len(date) == 0 and len(time) == 0 and len(category) == 0 and len(account) == 0:
 58                with st.expander(label="Relatório de Receitas", expanded=True):
 59                    st.info(body="Nao há registros neste período.")
 60
 61            elif len(description) > 0 and len(value) > 0 and len(date) > 0 and len(time) > 0 and len(category) > 0 and len(account) > 0:
 62                aux_str = ""
 63
 64                for i in range(0, len(time)):
 65                    aux_str = str(time[i])
 66                    time[i] = aux_str
 67
 68                with st.expander(label="Relatório de Receitas", expanded=True):
 69
 70                    data_df = pd.DataFrame(
 71                        {
 72                            "Descrição": description,
 73                            "Valor": value,
 74                            "Data": date,
 75                            "Horário": time,
 76                            "Categoria": category,
 77                            "Conta": account,
 78                        }
 79                    )
 80
 81                    data_df["Valor"] = data_df["Valor"].apply(lambda x: f"R$ {x:.2f}".replace(".", ","))
 82                    data_df["Data"] = pd.to_datetime(data_df["Data"]).dt.strftime("%d/%m/%Y")
 83                    st.dataframe(data_df, hide_index=True, use_container_width=True)
 84
 85                value_list.append(value)
 86                data_df_list.append(data_df)
 87            
 88            empty_list = QueryExecutor().complex_compund_query(expenses_query, 6, params=(initial_data, final_data, *accounts, user_name, user_document))
 89            description, value, date, time, category, account = (empty_list)
 90
 91            if len(description) == 0 and len(value) == 0 and len(date) == 0 and len(time) == 0 and len(category) == 0 and len(account) == 0:
 92                with st.expander(label="Relatório de Despesas", expanded=True):
 93                    st.info(body="Nao há registros neste período.")
 94
 95            elif len(description) > 0 and len(value) > 0 and len(date) > 0 and len(time) > 0 and len(category) > 0 and len(account) > 0:
 96
 97                aux_str = ""
 98
 99                for i in range(0, len(time)):
100                    aux_str = str(time[i])
101                    time[i] = aux_str
102
103                with st.expander(label="Relatório de Despesas", expanded=True):
104
105                    data_df = pd.DataFrame(
106                        {
107                            "Descrição": description,
108                            "Valor": value,
109                            "Data": date,
110                            "Horário": time,
111                            "Categoria": category,
112                            "Conta": account,
113                        }
114                    )
115
116                    data_df["Valor"] = data_df["Valor"].apply(lambda x: f"R$ {x:.2f}".replace(".", ","))
117                    data_df["Data"] = pd.to_datetime(data_df["Data"]).dt.strftime("%d/%m/%Y")
118                    st.dataframe(data_df, hide_index=True, use_container_width=True)
119
120                value_list.append(value)
121                data_df_list.append(data_df)
122
123        if statement_query_option == "Receitas":
124            empty_list = QueryExecutor().complex_compund_query(revenues_query, 6, params=(initial_data, final_data, *accounts, user_name, user_document))
125            description, value, date, time, category, account = (empty_list)
126
127            if len(description) == 0 and len(value) == 0 and len(date) == 0 and len(time) == 0 and len(category) == 0 and len(account) == 0:
128                with st.expander(label="Relatório de Receitas", expanded=True):
129                    st.info(body="Nao há registros neste período.")
130
131            elif len(description) > 0 and len(value) > 0 and len(date) > 0 and len(time) > 0 and len(category) > 0 and len(account) > 0:
132                aux_str = ""
133
134                for i in range(0, len(time)):
135                    aux_str = str(time[i])
136                    time[i] = aux_str
137
138                with st.expander(label="Relatório de Receitas", expanded=True):
139
140                    data_df = pd.DataFrame(
141                        {
142                            "Descrição": description,
143                            "Valor": value,
144                            "Data": date,
145                            "Horário": time,
146                            "Categoria": category,
147                            "Conta": account,
148                        }
149                    )
150
151                    data_df["Valor"] = data_df["Valor"].apply(lambda x: f"R$ {x:.2f}".replace(".", ","))
152                    data_df["Data"] = pd.to_datetime(data_df["Data"]).dt.strftime("%d/%m/%Y")
153                    st.dataframe(data_df, hide_index=True, use_container_width=True)
154
155                value_list.append(value)
156                data_df_list.append(data_df)
157        
158        if statement_query_option == "Despesas":
159            empty_list = QueryExecutor().complex_compund_query(expenses_query, 6, params=(initial_data, final_data, *accounts, user_name, user_document))
160            description, value, date, time, category, account = (empty_list)
161
162            if len(description) == 0 and len(value) == 0 and len(date) == 0 and len(time) == 0 and len(category) == 0 and len(account) == 0:
163                with st.expander(label="Relatório de Despesas", expanded=True):
164                    st.info(body="Nao há registros neste período.")
165
166            elif len(description) > 0 and len(value) > 0 and len(date) > 0 and len(time) > 0 and len(category) > 0 and len(account) > 0:
167                aux_str = ""
168
169                for i in range(0, len(time)):
170                    aux_str = str(time[i])
171                    time[i] = aux_str
172
173                with st.expander(label="Relatório de Despesas", expanded=True):
174
175                    data_df = pd.DataFrame(
176                        {
177                            "Descrição": description,
178                            "Valor": value,
179                            "Data": date,
180                            "Horário": time,
181                            "Categoria": category,
182                            "Conta": account,
183                        }
184                    )
185
186                    data_df["Valor"] = data_df["Valor"].apply(lambda x: f"R$ {x:.2f}".replace(".", ","))
187                    data_df["Data"] = pd.to_datetime(data_df["Data"]).dt.strftime("%d/%m/%Y")
188                    st.dataframe(data_df, hide_index=True, use_container_width=True)
189
190                value_list.append(value)
191                data_df_list.append(data_df)
192        
193        return value_list, data_df_list
194
195    def generate_pdf(self, df: list, statement_type: str, initial_data: str, final_data: str, accounts: list):
196        """
197        Gera o PDF do extrato bancário.
198
199        Parameters
200        ----------
201        df : list
202            A lista com os gráficos.
203        statement_type : str
204            O tipo de extrato bancário.
205        initial_data : str
206            A data inicial do extrato.
207        final_data : str
208            A data final do extrato.
209        accounts : list
210            Lista com as contas consultadas.
211
212        Returns
213        -------
214        pdf: O PDF gerado pela função.
215        """
216
217        user_name, user_document = Login().get_user_data(return_option="user_doc_name")
218
219        unformatted_today = datetime.strptime(today, '%Y-%m-%d')
220        formatted_today = unformatted_today.strftime('%d/%m/%Y')
221
222        time = GetActualTime().get_actual_time()
223
224        accounts_str = ""
225        for item in range(0, len(accounts)):
226            if item < (len(accounts) - 1):
227                accounts_str = accounts_str + accounts[item] + ", "
228            if item == len(accounts) - 1:
229                accounts_str = accounts_str + accounts[item] + "."
230
231        pdf = FPDF(orientation='L', unit="mm", format="A4")
232        pdf.add_page()
233        pdf.add_font("SystemFont", "", "{}{}".format(absolute_app_path, system_font), uni=True)
234
235        pdf.set_font("SystemFont", size=16)
236        pdf.cell(0, 10, "Relatório de {}".format(statement_type), ln=True, align="C")
237        pdf.ln(10)
238
239        pdf.set_font("SystemFont", size=12)
240        pdf.cell(0, 5, "Período de consulta: {} à {}".format(initial_data, final_data), ln=True)
241        pdf.set_font("SystemFont", size=12)
242        pdf.cell(0, 10, "Contas consultadas: {}".format(accounts_str), ln=True)
243        pdf.ln(5)
244
245        statement_types = ["Receitas", "Despesas"]
246
247        for i in range(0, len(df)):
248
249            pdf.set_font("SystemFont", size=14)
250            pdf.cell(0, 10, "{}".format(statement_types[i]), ln=True)
251            pdf.ln(5)
252
253            col_width = (pdf.w / len(df[i].columns)) * 0.85
254
255            pdf.set_font("SystemFont", size=10)
256
257            for col in df[i].columns:
258                pdf.cell(col_width, 5, col, border=1, align="C")
259            pdf.ln()
260            pdf.set_font("SystemFont", size=10)
261            for _, row in df[i].iterrows():
262                for cell in row:
263                    pdf.cell(col_width, 10, str(cell), border=1, align="C")
264                pdf.ln()
265            pdf.ln(10)
266
267        # pdf.cell(0, 10, "{}".format(generated_description), align="L", ln=True)
268
269        pdf.cell(0, 10, "Horário da consulta: {}, às {}.".format(formatted_today, time), align="R", ln=True)
270        pdf.cell(0, 10, "Nome do usuário: {}.".format(user_name), align="R", ln=True)
271        pdf.ln(5)
272
273        return pdf
274
275    def main_menu(self):
276        """
277        Menu principal.
278        """
279        user_name, user_document = Login().get_user_data(return_option="user_doc_name")
280        logged_user, logged_user_password = Login().get_user_data(return_option="user_login_password")
281
282        user_current_accounts = QueryExecutor().complex_consult_query(query=user_current_accounts_query, params=(user_name, user_document))
283        user_current_accounts = QueryExecutor().treat_numerous_simple_result(user_current_accounts, to_remove_list)
284
285        col4, col5, col6 = st.columns(3)
286
287        if len(user_current_accounts) > 0:
288
289            with col4:
290                st.subheader(body=":computer: Entrada de Dados")
291                with st.expander(label="Dados", expanded=True):
292                    statement_option = st.selectbox(label="Tipos de extrato", options=["Selecione uma opção", "Receitas", "Despesas", "Receitas e Despesas"])
293                    selected_accounts = st.multiselect(label="Contas", options=user_current_accounts, placeholder="Escolha a(s) conta(s)")
294                    initial_data = st.date_input(label="Data de início")
295                    final_data = st.date_input(label="Data de fim")
296                    confirm_choice = st.checkbox(label="Confirmar dados")
297
298                consult_tables = st.button(label=":chart: Gerar Relatórios")
299
300            if confirm_choice and consult_tables:
301                with col5:
302                    with st.spinner(text="Aguarde..."):
303                        sleep(2.5)
304
305                    st.subheader(body=":bar_chart: Resultados")
306
307                    if statement_option != "Selecione uma opção" and len(selected_accounts) > 0 and (initial_data <= final_data):
308                        value_list, dataframes = self.consult_statement(statement_option, selected_accounts, initial_data, final_data)
309
310                        with col6:
311                            with st.spinner(text="Aguarde..."):
312                                sleep(2.5)
313                            st.subheader(body=":information_source: Informações")
314
315                            total_value = 0
316                            counter = 0
317
318                            for i in range(0, len(value_list)):
319                                for j in range(0, len(value_list[i])):
320                                    total_value += value_list[i][j]
321                                    counter += 1
322                                medium_value = round((total_value / len(value_list[i])), 2)
323
324                            medium_value = str(medium_value)
325                            medium_value = medium_value.replace(".", ",")
326                            total_value = str(total_value)
327                            total_value = total_value.replace(".", ",")
328
329                            with st.expander(label="Dados", expanded=True):
330                                st.info(body="Quantidade de {}: {}.".format(statement_option.lower(), counter))
331                                st.info(body="Valor total das {}: R$ {}.".format(statement_option.lower(), total_value))
332                                st.info(body="Valor médio das {}: R$ {}.".format(statement_option.lower(), medium_value))
333
334                            formatted_initial_data = initial_data.strftime("%d/%m/%Y")
335                            formatted_final_data = final_data.strftime("%d/%m/%Y")
336
337                            log_query = '''INSERT INTO financas.logs_atividades (usuario_log, tipo_log, conteudo_log) VALUES ( %s, %s, %s);'''
338                            log_values = (logged_user, "Consulta", "Consultou o relatório de {} entre o período de {} a {}.".format(statement_option, formatted_initial_data, formatted_final_data))
339                            QueryExecutor().insert_query(log_query, log_values, "Log gravado.", "Erro ao gravar log:")
340
341                            time = GetActualTime().get_actual_time()
342
343                            pdf = self.generate_pdf(dataframes, statement_option, formatted_initial_data, formatted_final_data, selected_accounts)
344                            pdf_bytes = pdf.output(dest='S').encode('latin1')
345
346                            st.download_button(
347                                label=":floppy_disk: Baixar PDF",
348                                data=pdf_bytes,
349                                file_name="extrato_bancario_{}_{}_a_{}_{}.pdf".format(statement_option.replace(" ", "_").lower(), initial_data, final_data, time),
350                                mime="application/pdf",
351                            )
352
353                    elif (len(selected_accounts) == 0) or (initial_data > final_data) or (statement_option == "Selecione uma opção"):
354                        with st.expander(label="Avisos", expanded=True):
355                            if statement_option == "Selecione uma opção":
356                                st.error(body="Selecione um tipo de extrato.")
357                            if len(selected_accounts) == 0:
358                                st.error(body="Nenhuma conta selecionada.")
359                            if initial_data > final_data:
360                                st.error(body="A data inicial não pode ser maior que a final.")
361
362        elif len(user_current_accounts) == 0:
363            with col5:
364                st.warning(body="Você ainda não possui contas cadastradas.")