loans.new_loan

  1import streamlit as st
  2from dictionary.vars import expense_categories, to_remove_list
  3from dictionary.sql import last_loan_id_query, creditor_doc_name_query, user_current_accounts_query, creditors_query, total_account_revenue_query, total_account_expense_query
  4from functions.get_actual_time import GetActualTime
  5from functions.query_executor import QueryExecutor
  6from functions.variable import Variable
  7from functions.login import Login
  8from screens.reports.receipts import Receipts
  9from time import sleep
 10
 11
 12class TakeNewLoan:
 13    def get_user_current_accounts(self):
 14        """
 15        Consulta as contas correntes do usuário.
 16
 17        Returns
 18        -------
 19        user_current_accounts: list = A lista com as contas correntes do usuário.
 20        """
 21        user_name, user_document = Login().get_user_data(return_option="user_doc_name")
 22
 23        user_current_accounts = QueryExecutor().complex_consult_query(query=user_current_accounts_query, params=(user_name, user_document))
 24        user_current_accounts = QueryExecutor().treat_numerous_simple_result(user_current_accounts, to_remove_list)
 25
 26        return user_current_accounts
 27
 28    def main_menu(self):
 29        """
 30        Coleta os dados do novo empréstimo tomado pelo cliente.
 31        """
 32        user_name, user_document = Login().get_user_data(return_option="user_doc_name")
 33        logged_user, logged_user_password = Login().get_user_data(return_option="user_login_password")
 34
 35        col1, col2, col3 = st.columns(3)
 36
 37        user_current_accounts = self.get_user_current_accounts()
 38
 39        if len(user_current_accounts) == 0:
 40            with col2:
 41                st.info(body="Você ainda não possui contas ou cartões cadastrados.")
 42
 43        elif len(user_current_accounts) >= 1 and user_current_accounts[0] != "Selecione uma opção":
 44
 45            creditors_quantity_query = '''SELECT COUNT(id_credor) FROM credores WHERE credores.documento <> %s OR credores.nome <> %s;'''
 46
 47            creditors_quantity = QueryExecutor().simple_consult_query(query=creditors_quantity_query, params=(user_document, user_name))
 48            creditors_quantity = int(QueryExecutor().treat_simple_result(creditors_quantity, to_remove_list))
 49
 50            if creditors_quantity == 0:
 51                with col2:
 52                    st.warning(
 53                        body="Você ainda não cadastrou credores.")
 54
 55            elif creditors_quantity >= 1:
 56
 57                with col1:
 58                    st.subheader(body=":computer: Entrada de Dados")
 59
 60                    with st.expander(label="Dados", expanded=True):
 61                        id = QueryExecutor().simple_consult_brute_query(last_loan_id_query)
 62                        id = QueryExecutor().treat_simple_result(id, to_remove_list)
 63                        id = int(id) + 1
 64
 65                        description = st.text_input(label=":lower_left_ballpoint_pen: Descrição", placeholder="Informe uma descrição", help="Descrição do empréstimo tomado.", max_chars=25)
 66                        value = st.number_input(label=":dollar: Valor", step=0.01, min_value=0.01)
 67                        date = st.date_input(label=":date: Data")
 68                        category = st.selectbox(label=":card_index_dividers: Categoria", options=expense_categories,)
 69                        account = st.selectbox(label=":bank: Conta", options=user_current_accounts)
 70
 71                        creditors = QueryExecutor().complex_consult_query(creditors_query, params=(user_name, user_document))
 72                        creditors = QueryExecutor().treat_numerous_simple_result(creditors, to_remove_list)
 73                        creditor = st.selectbox(label="Credor", options=creditors)
 74
 75                        creditor_doc_name_query = """
 76                                SELECT 
 77                                    credores.nome,
 78                                    credores.documento
 79                                FROM
 80                                    credores
 81                                WHERE
 82                                    credores.nome = %s;"""
 83
 84                        creditor_name_document = QueryExecutor().complex_consult_query(creditor_doc_name_query, params=(creditor, ))
 85                        creditor_name_document = QueryExecutor().treat_complex_result(creditor_name_document, to_remove_list)
 86                        creditor_name = creditor_name_document[0]
 87                        creditor_document = creditor_name_document[1]
 88                        benefited_name, benefited_document = Login().get_user_data(return_option="user_doc_name")
 89                        confirm_values_check_box = st.checkbox(label="Confirmar Dados")
 90
 91                    generate_receipt_button = st.button(label=":pencil: Gerar Comprovante", key="generate_receipt_button",)
 92
 93                with col3:
 94                    if confirm_values_check_box and generate_receipt_button:
 95
 96                        actual_horary = GetActualTime().get_actual_time()
 97
 98                        with col2:
 99                            with st.spinner("Aguarde..."):
100                                sleep(2.5)
101                            st.subheader(body=":white_check_mark: Validação de dados")
102
103                            with st.expander(label="Informações", expanded=True):
104                                str_selected_account_revenues = (QueryExecutor().simple_consult_query(query=total_account_revenue_query, params=(account, user_name, user_document)))
105                                str_selected_account_revenues = (QueryExecutor().treat_simple_result(str_selected_account_revenues, to_remove_list))
106                                selected_account_revenues = float(str_selected_account_revenues)
107
108                                str_selected_account_expenses = (QueryExecutor().simple_consult_query(query=total_account_expense_query, params=(account, user_name, user_document)))
109                                str_selected_account_expenses = (QueryExecutor().treat_simple_result(str_selected_account_expenses, to_remove_list))
110                                selected_account_expenses = float(str_selected_account_expenses)
111
112                                account_available_value = round(
113                                    selected_account_revenues - selected_account_expenses, 2)
114
115                        if description != "" and value >= 0.01 and date and category != "Selecione uma opção" and account != "Selecione uma opção":
116                            with col2:
117                                description = "Empréstimo - " + description
118
119                                with st.expander(label="Informações", expanded=True):
120                                    st.success(body="Dados válidos.")
121
122                            expense_query = '''INSERT INTO despesas (descricao, valor, data, horario, categoria, conta, proprietario_despesa, documento_proprietario_despesa, pago) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)'''
123                            expense_values = (description, value, date, actual_horary, category, account, creditor_name, creditor_document, "S")
124                            QueryExecutor().insert_query(expense_query, expense_values, "Despesa registrada com sucesso!", "Erro ao registrar despesa:")
125
126                            revenue_query = '''INSERT INTO receitas (descricao, valor, data, horario, categoria, conta, proprietario_receita, documento_proprietario_receita, recebido) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)'''
127                            revenue_values = (description, value, date, actual_horary, category, account, user_name, user_document, "S")
128                            QueryExecutor().insert_query(revenue_query, revenue_values, "Receita registrada com sucesso!", "Erro ao registrar receita:")
129
130                            loan_query = '''INSERT INTO emprestimos(descricao,valor,valor_pago,data,horario,categoria,conta,devedor,documento_devedor,credor,documento_credor,pago) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'''
131                            loan_values = (description, value, 0, date, actual_horary, category, account, benefited_name, benefited_document, creditor_name, creditor_document, "N")
132                            QueryExecutor().insert_query(loan_query, loan_values, "Empréstimo registrado com sucesso!", "Erro ao registrar empréstimo:")
133
134                            str_value = Variable().treat_complex_string(value)
135
136                            log_query = '''INSERT INTO financas.logs_atividades (usuario_log, tipo_log, conteudo_log) VALUES ( %s, %s, %s);'''
137                            log_values = (logged_user, "Registro", "Tomou um empréstimo no valor de R$ {} associado a conta {}.".format(str_value, account))
138                            QueryExecutor().insert_query(log_query, log_values, "Log gravado.", "Erro ao gravar log:")
139
140                            st.subheader(body=":pencil: Comprovante de Empréstimo")
141
142                            with st.spinner("Aguarde..."):
143                                sleep(1)
144
145                            Receipts().generate_receipt('emprestimos', id, description, value, str(date), category, account)
146
147                        else:
148                            with st.expander(label="Informações", expanded=True):
149                                if description == "":
150                                    st.error(body="A descrição está vazia.")
151                                if category == "Selecione uma opção":
152                                    st.error(body="Selecione uma categoria.")
153                                if value > account_available_value:
154                                    st.error(body="O valor do empréstimo não pode ser maior que o valor disponível em conta.")
155
156    def make_new_loan(self):
157        """
158        Coleta os dados do empréstimo que está sendo concedido pelo usuário.
159        """
160        user_name, user_document = Login().get_user_data(return_option="user_doc_name")
161        logged_user, logged_user_password = Login().get_user_data(return_option="user_login_password")
162
163        col1, col2, col3 = st.columns(3)
164
165        user_current_accounts = self.get_user_current_accounts()
166
167        if len(user_current_accounts) == 0:
168            with col2:
169                st.info(body="Você ainda não possui contas ou cartões cadastrados.")
170
171        elif len(user_current_accounts) >= 1 and user_current_accounts[0] != "Selecione uma opção":
172
173            benefited_quantity_query = '''SELECT COUNT(id_beneficiado) FROM beneficiados WHERE beneficiados.documento <> %s OR beneficiados.nome <> %s;'''
174
175            benefited_quantity = QueryExecutor().simple_consult_query(query=benefited_quantity_query, params=(user_name, user_document))
176            benefited_quantity = int(QueryExecutor().treat_simple_result(benefited_quantity, to_remove_list))
177
178            if benefited_quantity == 0:
179                with col2:
180                    st.warning(body="Você ainda não cadastrou beneficiados.")
181
182            elif benefited_quantity >= 1:
183
184                with col1:
185                    st.subheader(body=":computer: Entrada de Dados")
186
187                    with st.expander(label="Dados", expanded=True):
188
189                        id = QueryExecutor().simple_consult_brute_query(last_loan_id_query)
190                        id = QueryExecutor().treat_simple_result(id, to_remove_list)
191                        id = int(id) + 1
192
193                        description = st.text_input(label=":lower_left_ballpoint_pen: Descrição", placeholder="Informe uma descrição", help="Descrição breve do empréstimo.", max_chars=25)
194                        value = st.number_input(label=":dollar: Valor", step=0.01, min_value=0.01)
195                        date = st.date_input(label=":date: Data")
196                        category = st.selectbox(label=":card_index_dividers: Categoria", options=expense_categories,)
197                        account = st.selectbox(label=":bank: Conta", options=user_current_accounts)
198
199                        beneficiaries_query = '''SELECT nome FROM beneficiados WHERE beneficiados.documento <> {} OR beneficiados.nome <> '{}';'''.format(user_document, user_name)
200                        beneficiaries = QueryExecutor().complex_consult_query(beneficiaries_query)
201                        beneficiaries = QueryExecutor().treat_numerous_simple_result(beneficiaries, to_remove_list)
202                        benefited = st.selectbox(label="Beneficiado", options=beneficiaries)
203
204                        creditor_name_document = QueryExecutor().complex_consult_query(creditor_doc_name_query)
205                        creditor_name_document = QueryExecutor().treat_complex_result(creditor_name_document, to_remove_list)
206                        creditor_name = creditor_name_document[0]
207                        creditor_document = creditor_name_document[1]
208
209                        benefited_doc_name_query = """
210                                        SELECT 
211                                            beneficiados.nome,
212                                            beneficiados.documento
213                                        FROM
214                                            beneficiados
215                                        WHERE
216                                            beneficiados.nome = %s;"""
217                        benefited_doc_name = QueryExecutor().complex_consult_query(benefited_doc_name_query, params=(benefited, ))
218                        benefited_doc_name = QueryExecutor().treat_complex_result(benefited_doc_name, to_remove_list)
219                        benefited_name = benefited_doc_name[0]
220                        benefited_document = benefited_doc_name[1]
221
222                        confirm_values_check_box = st.checkbox(label="Confirmar Dados")
223
224                    generate_receipt_button = st.button(label=":pencil: Gerar Comprovante", key="generate_receipt_button",)
225
226                with col3:
227                    if confirm_values_check_box and generate_receipt_button:
228
229                        actual_horary = GetActualTime().get_actual_time()
230
231                        with col2:
232
233                            with st.spinner("Aguarde..."):
234                                sleep(2.5)
235
236                            st.subheader(body=":white_check_mark: Validação de dados")
237
238                            with st.expander(label="Informações", expanded=True):
239
240                                str_selected_account_revenues = (QueryExecutor().simple_consult_query(query=total_account_revenue_query, params=(account, user_name, user_document)))
241                                str_selected_account_revenues = (QueryExecutor().treat_simple_result(str_selected_account_revenues, to_remove_list))
242                                selected_account_revenues = float(str_selected_account_revenues)
243
244                                str_selected_account_expenses = (QueryExecutor().simple_consult_query(query=total_account_expense_query, params=(account, user_name, user_document)))
245                                str_selected_account_expenses = (QueryExecutor().treat_simple_result(str_selected_account_expenses, to_remove_list))
246                                selected_account_expenses = float(str_selected_account_expenses)
247
248                                account_available_value = round(selected_account_revenues - selected_account_expenses, 2)
249
250                        if description != "" and ((value >= 0.01) and (value <= account_available_value)) and date and category != "Selecione uma opção" and account != "Selecione uma opção":
251
252                            with col2:
253                                with st.expander(label="Informações", expanded=True):
254                                    st.success(body="Dados válidos.")
255
256                            expense_query = '''INSERT INTO despesas (descricao,valor,data,horario,categoria,conta,proprietario_despesa,documento_proprietario_despesa,pago) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)'''
257                            expense_values = (description, value, date, actual_horary,category, account, creditor_name, creditor_document, "S")
258                            QueryExecutor().insert_query(expense_query, expense_values, "Despesa registrado com sucesso!", "Erro ao registrar despesa:")
259
260                            loan_query = '''INSERT INTO emprestimos (descricao,valor,valor_pago,data,horario,categoria,conta,devedor,documento_devedor,credor,documento_credor, pago) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'''
261                            loan_values = (description, value, 0, date, actual_horary, category, account,benefited_name, benefited_document, creditor_name, creditor_document, "N")
262                            QueryExecutor().insert_query(loan_query, loan_values, "Empréstimo registrado com sucesso!", "Erro ao registrar empréstimo:")
263
264                            str_value = Variable().treat_complex_string(value)
265
266                            log_query = '''INSERT INTO financas.logs_atividades (usuario_log, tipo_log, conteudo_log) VALUES ( %s, %s, %s);'''
267                            log_values = (logged_user, "Registro", "Registrou um empréstimo no valor de R$ {} a partir da conta {}.".format(str_value, account))
268                            QueryExecutor().insert_query(log_query, log_values, "Log gravado.", "Erro ao gravar log:")
269
270                            st.subheader(
271                                body=":pencil: Comprovante de Empréstimo")
272
273                            with st.spinner("Aguarde..."):
274                                sleep(1)
275
276                            Receipts().generate_receipt('emprestimos', id, description, value, str(date), category, account)
277
278                        else:
279                            with col2:
280                                with st.expander(label="Informações", expanded=True):
281                                    if description == "":
282                                        st.error(body="A descrição está vazia.")
283                                    if category == "Selecione uma opção":
284                                        st.error(body="Selecione uma categoria.")
285                                    if value > account_available_value:
286                                        account_available_value = str(account_available_value)
287                                        account_available_value = account_available_value.replace(".", ",")
288                                        last_two_values = account_available_value[-2:]
289                                        if last_two_values == ",0":
290                                            account_available_value = account_available_value + "0"
291                                        st.error(body="O valor do empréstimo não pode ser maior que o valor disponível em conta.")
292                                        st.info(body="Valor disponível para a conta {}: R$ {}.".format(account, account_available_value))
293                                        
294class MakeNewLoan:
295    """
296    Classe que representa a realização de novos empréstimos.
297    """
298    def get_user_current_accounts(self):
299        """
300        Consulta as contas correntes do usuário.
301
302        Returns
303        -------
304        user_current_accounts : list
305            A lista com as contas correntes do usuário.
306        """
307        user_name, user_document = Login().get_user_data(return_option="user_doc_name")
308
309        user_current_accounts = QueryExecutor().complex_consult_query(query=user_current_accounts_query, params=(user_name, user_document))
310        user_current_accounts = QueryExecutor().treat_numerous_simple_result(user_current_accounts, to_remove_list)
311
312        return user_current_accounts
313
314    def main_menu(self):
315        """
316        Coleta os dados do empréstimo que está sendo concedido pelo usuário.
317        """
318        user_name, user_document = Login().get_user_data(return_option="user_doc_name")
319        logged_user, logged_user_password = Login().get_user_data(return_option="user_login_password")
320
321        col1, col2, col3 = st.columns(3)
322
323        user_current_accounts = self.get_user_current_accounts()
324
325        if len(user_current_accounts) == 0:
326            with col2:
327                st.info(body="Você ainda não possui contas ou cartões cadastrados.")
328
329        elif len(user_current_accounts) >= 1 and user_current_accounts[0] != "Selecione uma opção":
330
331            benefited_quantity_query = '''SELECT COUNT(id_beneficiado) FROM beneficiados WHERE beneficiados.documento <> %s OR beneficiados.nome <> %s;'''
332
333            benefited_quantity = QueryExecutor().simple_consult_query(query=benefited_quantity_query, params=(user_name, user_document))
334            benefited_quantity = int(QueryExecutor().treat_simple_result(benefited_quantity, to_remove_list))
335
336            if benefited_quantity == 0:
337                with col2:
338                    st.warning(body="Você ainda não cadastrou beneficiados.")
339
340            elif benefited_quantity >= 1:
341
342                with col1:
343                    st.subheader(body=":computer: Entrada de Dados")
344
345                    with st.expander(label="Dados", expanded=True):
346
347                        id = QueryExecutor().simple_consult_brute_query(last_loan_id_query)
348                        id = QueryExecutor().treat_simple_result(id, to_remove_list)
349                        id = int(id) + 1
350
351                        description = st.text_input(label=":lower_left_ballpoint_pen: Descrição", placeholder="Informe uma descrição", help="Descrição breve do empréstimo.", max_chars=25)
352                        value = st.number_input(label=":dollar: Valor", step=0.01, min_value=0.01)
353                        date = st.date_input(label=":date: Data")
354                        category = st.selectbox(label=":card_index_dividers: Categoria", options=expense_categories,)
355                        account = st.selectbox(label=":bank: Conta", options=user_current_accounts)
356
357                        beneficiaries_query = '''SELECT nome FROM beneficiados WHERE beneficiados.documento <> %s OR beneficiados.nome <> %s;'''
358                        beneficiaries = QueryExecutor().complex_consult_query(query=beneficiaries_query, params=(user_document, user_name))
359                        beneficiaries = QueryExecutor().treat_numerous_simple_result(beneficiaries, to_remove_list)
360                        benefited = st.selectbox(label="Beneficiado", options=beneficiaries)
361
362                        creditor_name_document = QueryExecutor().complex_consult_query(creditor_doc_name_query, params=(user_name, user_document))
363                        creditor_name_document = QueryExecutor().treat_complex_result(creditor_name_document, to_remove_list)
364                        creditor_name = creditor_name_document[0]
365                        creditor_document = creditor_name_document[1]
366
367                        benefited_doc_name_query = """
368                                        SELECT 
369                                            beneficiados.nome,
370                                            beneficiados.documento
371                                        FROM
372                                            beneficiados
373                                        WHERE
374                                            beneficiados.nome = %s;"""
375                        benefited_doc_name = QueryExecutor().complex_consult_query(query=benefited_doc_name_query, params=(benefited,))
376                        benefited_doc_name = QueryExecutor().treat_complex_result(benefited_doc_name, to_remove_list)
377                        benefited_name = benefited_doc_name[0]
378                        benefited_document = benefited_doc_name[1]
379
380                        confirm_values_check_box = st.checkbox(label="Confirmar Dados")
381
382                    generate_receipt_button = st.button(label=":pencil: Gerar Comprovante", key="generate_receipt_button",)
383
384                with col3:
385                    if confirm_values_check_box and generate_receipt_button:
386
387                        actual_horary = GetActualTime().get_actual_time()
388
389                        with col2:
390
391                            with st.spinner("Aguarde..."):
392                                sleep(2.5)
393
394                            st.subheader(body=":white_check_mark: Validação de dados")
395
396                            with st.expander(label="Informações", expanded=True):
397
398                                str_selected_account_revenues = (QueryExecutor().simple_consult_query(query=total_account_revenue_query, params=(account, user_name, user_document)))
399                                str_selected_account_revenues = (QueryExecutor().treat_simple_result(str_selected_account_revenues, to_remove_list))
400                                selected_account_revenues = float(str_selected_account_revenues)
401
402                                str_selected_account_expenses = (QueryExecutor().simple_consult_query(query=total_account_expense_query, params=(account, user_name, user_document)))
403                                str_selected_account_expenses = (QueryExecutor().treat_simple_result(str_selected_account_expenses, to_remove_list))
404                                selected_account_expenses = float(str_selected_account_expenses)
405
406                                account_available_value = round(selected_account_revenues - selected_account_expenses, 2)
407
408                        if description != "" and ((value >= 0.01) and (value <= account_available_value)) and date and category != "Selecione uma opção" and account != "Selecione uma opção":
409
410                            with col2:
411                                with st.expander(label="Informações", expanded=True):
412                                    st.success(body="Dados válidos.")
413
414                            expense_query = '''INSERT INTO despesas (descricao,valor,data,horario,categoria,conta,proprietario_despesa,documento_proprietario_despesa,pago) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)'''
415                            expense_values = (description, value, date, actual_horary, category, account, creditor_name, creditor_document, "S")
416                            QueryExecutor().insert_query(expense_query, expense_values, "Despesa registrado com sucesso!", "Erro ao registrar despesa:")
417
418                            loan_query = '''INSERT INTO emprestimos (descricao,valor,valor_pago,data,horario,categoria,conta,devedor,documento_devedor,credor,documento_credor, pago) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'''
419                            loan_values = (description, value, 0, date, actual_horary, category, account, benefited_name, benefited_document, creditor_name, creditor_document, "N")
420                            QueryExecutor().insert_query(loan_query, loan_values, "Empréstimo registrado com sucesso!", "Erro ao registrar empréstimo:")
421
422                            str_value = Variable().treat_complex_string(value)
423
424                            log_query = '''INSERT INTO financas.logs_atividades (usuario_log, tipo_log, conteudo_log) VALUES ( %s, %s, %s);'''
425                            log_values = (logged_user, "Registro", "Registrou um empréstimo no valor de R$ {} a partir da conta {}.".format(str_value, account))
426                            QueryExecutor().insert_query(log_query, log_values, "Log gravado.", "Erro ao gravar log:")
427
428                            st.subheader(body=":pencil: Comprovante de Empréstimo")
429
430                            with st.spinner("Aguarde..."):
431                                sleep(1)
432
433                            Receipts().generate_receipt('emprestimos', id, description, value, str(date), category, account)
434
435                        else:
436                            with col2:
437                                with st.expander(label="Informações", expanded=True):
438                                    if description == "":
439                                        st.error(body="A descrição está vazia.")
440                                    if category == "Selecione uma opção":
441                                        st.error(body="Selecione uma categoria.")
442                                    if value > account_available_value:
443                                        account_available_value = str(account_available_value)
444                                        account_available_value = account_available_value.replace(".", ",")
445                                        last_two_values = account_available_value[-2:]
446                                        if last_two_values == ",0":
447                                            account_available_value = account_available_value + "0"
448                                        st.error(body="O valor do empréstimo não pode ser maior que o valor disponível em conta.")
449                                        st.info(body="Valor disponível para a conta {}: R$ {}.".format(account, account_available_value))