loans.pay_loan

  1import streamlit as st
  2import pandas as pd
  3from dictionary.sql import not_payed_loans_query, user_current_accounts_query, last_expense_id_query
  4from dictionary.vars import to_remove_list, today
  5from functions.query_executor import QueryExecutor
  6from functions.get_actual_time import GetActualTime
  7from functions.login import Login
  8from functions.variable import Variable
  9from screens.reports.receipts import Receipts
 10from time import sleep
 11
 12
 13class PayLoan:
 14    """
 15    Classe que representa o pagamento dos empréstimos tomados pelo usuário.
 16    """
 17
 18    def main_menu(self):
 19        """
 20        Exibe os empréstimos tomados pelo usuário.
 21        """
 22        user_name, user_document = Login().get_user_data(return_option="user_doc_name")
 23        logged_user, logged_user_password = Login().get_user_data(return_option="user_login_password")
 24
 25        not_payed_loans = QueryExecutor().complex_compund_query(query=not_payed_loans_query, list_quantity=9, params=(user_name, user_document))
 26
 27        if len(not_payed_loans[0]) >= 1:
 28
 29            col4, col5, col6 = st.columns(3)
 30
 31            with col4:
 32
 33                id, description, total_value, payed_value, remaining_value, date, category, account, creditor = not_payed_loans
 34
 35                loan_data_df = pd.DataFrame(
 36                    {
 37                        "Descrição": description,
 38                        "Valor Total": total_value,
 39                        "Valor Pago": payed_value,
 40                        "Valor a Pagar": remaining_value,
 41                        "Data": date,
 42                        "Categoria": category,
 43                        "Conta": account,
 44                        "Credor": creditor
 45                    }
 46                )
 47
 48                loan_data_df["Valor Total"] = loan_data_df["Valor Total"].apply(lambda x: f"R$ {x:.2f}".replace(".", ","))
 49                loan_data_df["Valor Pago"] = loan_data_df["Valor Pago"].apply(lambda x: f"R$ {x:.2f}".replace(".", ","))
 50                loan_data_df["Valor a Pagar"] = loan_data_df["Valor a Pagar"].apply(lambda x: f"R$ {x:.2f}".replace(".", ","))
 51                loan_data_df["Data"] = pd.to_datetime(loan_data_df["Data"]).dt.strftime("%d/%m/%Y")
 52
 53                st.subheader(body=":computer: Entrada de Dados")
 54
 55                with st.expander(label="Valores", expanded=True):
 56
 57                    st.dataframe(loan_data_df, hide_index=True,use_container_width=True)
 58
 59                    total_loan_value = 0
 60                    for i in range(0, len(remaining_value)):
 61                        total_loan_value += remaining_value[i]
 62
 63                    total_loan_value = str(total_loan_value)
 64                    total_loan_value = total_loan_value.replace(".", ",")
 65
 66                    st.info(body="Valor total: :heavy_dollar_sign: {}".format(total_loan_value))
 67
 68                    user_accounts = QueryExecutor().complex_consult_query(user_current_accounts_query, params=(user_name, user_document))
 69                    user_accounts = QueryExecutor().treat_numerous_simple_result(user_accounts, to_remove_list)
 70
 71                    debt = st.selectbox(label="Selecionar dívida", options=description)
 72
 73                    paying_max_value_query = '''
 74                        SELECT
 75                            DISTINCT(emprestimos.valor - \
 76                                        emprestimos.valor_pago)
 77                        FROM
 78                            emprestimos
 79                                INNER JOIN
 80                            contas ON contas.proprietario_conta = emprestimos.devedor
 81                                AND contas.documento_proprietario_conta = emprestimos.documento_devedor
 82                                INNER JOIN
 83                            usuarios ON usuarios.nome = emprestimos.devedor
 84                                AND usuarios.documento = emprestimos.documento_devedor
 85                        WHERE
 86                            emprestimos.pago = 'N'
 87                                AND emprestimos.descricao = '{}'
 88                    '''.format(debt)
 89
 90                    payed_actual_value_query = '''
 91                        SELECT
 92                            DISTINCT(emprestimos.valor_pago)
 93                        FROM
 94                            emprestimos
 95                                INNER JOIN
 96                            contas ON contas.proprietario_conta = emprestimos.devedor
 97                                AND contas.documento_proprietario_conta = emprestimos.documento_devedor
 98                                INNER JOIN
 99                            usuarios ON usuarios.nome = emprestimos.devedor
100                                AND usuarios.documento = emprestimos.documento_devedor
101                        WHERE
102                            emprestimos.pago = 'N'
103                                AND emprestimos.descricao = '{}'
104                    '''.format(debt)
105
106                    total_actual_value_query = '''
107                        SELECT
108                            DISTINCT(emprestimos.valor)
109                        FROM
110                            emprestimos
111                                INNER JOIN
112                            contas ON contas.proprietario_conta = emprestimos.devedor
113                                AND contas.documento_proprietario_conta = emprestimos.documento_devedor
114                                INNER JOIN
115                            usuarios ON usuarios.nome = emprestimos.devedor
116                                AND usuarios.documento = emprestimos.documento_devedor
117                        WHERE
118                            emprestimos.pago = 'N'
119                                AND emprestimos.descricao = '{}'
120                    '''.format(debt)
121
122                    benefited_name, benefited_document = Login().get_user_data(return_option="user_doc_name")
123
124                    paying_max_value = QueryExecutor().simple_consult_brute_query(paying_max_value_query)
125                    paying_max_value = QueryExecutor().treat_simple_result(paying_max_value, to_remove_list)
126                    paying_max_value = float(paying_max_value)
127
128                    payed_actual_value = QueryExecutor().simple_consult_brute_query(payed_actual_value_query)
129                    payed_actual_value = QueryExecutor().treat_simple_result(payed_actual_value, to_remove_list)
130                    payed_actual_value = float(payed_actual_value)
131
132                    total_actual_value = QueryExecutor().simple_consult_brute_query(total_actual_value_query)
133                    total_actual_value = QueryExecutor().treat_simple_result(total_actual_value, to_remove_list)
134                    total_actual_value = float(total_actual_value)
135
136                    paying_value = st.number_input(label="Valor", min_value=0.00, max_value=paying_max_value, step=0.01)
137                    selected_account = st.selectbox(label="Conta", options=user_accounts)
138
139                    confirm_values = st.checkbox(label="Confirmar valores")
140
141                pay_button = st.button(label=":floppy_disk: Pagar valor de empréstimo")
142
143            with col5:
144
145                if confirm_values:
146
147                    with st.spinner(text="Aguarde..."):
148                        sleep(2.5)
149
150                    st.subheader(body=":white_check_mark: Validação de Dados")
151
152                    if paying_value > 0:
153
154                        with col5:
155
156                            with st.expander(label="Dados", expanded=True):
157
158                                to_pay_value = (paying_value + payed_actual_value)
159                                str_paying_value = Variable().treat_complex_string(paying_value)
160
161                                st.info(body="Valor sendo pago: :heavy_dollar_sign: {}".format(str_paying_value))
162
163                                str_to_pay_value = Variable().treat_complex_string(to_pay_value)
164
165                                st.info(body="Valor pago atualizado: :heavy_dollar_sign: {}".format(str_to_pay_value))
166
167                                remaining_to_pay_value = total_actual_value - \
168                                    (paying_value + payed_actual_value)
169                                str_remaining_to_pay_value = Variable().treat_complex_string(remaining_to_pay_value)
170
171                                st.info('Valor restante a pagar: :heavy_dollar_sign: {}'.format(str_remaining_to_pay_value))
172
173                        loan_payed = 'N'
174
175                        if remaining_to_pay_value == 0:
176                            loan_payed = 'S'
177
178                    elif paying_value == 0:
179
180                        with st.spinner(text="Aguarde..."):
181                            sleep(2.5)
182
183                        with col5:
184                            with st.expander(label="Aviso", expanded=True):
185                                st.warning(body="O valor pago precisa ser maior do que 0.")
186
187                if confirm_values and pay_button:
188
189                    actual_horary = GetActualTime().get_actual_time()
190
191                    if paying_value > 0:
192
193                        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)'''
194                        values = (
195                            'Pagamento de empréstimo - {}'.format(debt),
196                            paying_value,
197                            today,
198                            actual_horary,
199                            'Pagamento de Empréstimo',
200                            selected_account,
201                            benefited_name,
202                            benefited_document,
203                            'S'
204                        )
205
206                        QueryExecutor().insert_query(expense_query, values, "Valor de empréstimo pago com sucesso!", "Erro ao pagar valor do empréstimo:")
207
208                        update_loan_query = '''UPDATE emprestimos SET valor_pago = {}, pago = "{}" WHERE descricao = "{}" AND pago = "{}" AND devedor = "{}" AND documento_devedor = {}'''.format(to_pay_value, loan_payed, debt, 'N', benefited_name, benefited_document)
209                        QueryExecutor().update_table_unique_register(update_loan_query, "Empréstimo atualizado com sucesso!", "Erro ao atualizar valores do empréstimo:")
210
211                        last_expense_id = QueryExecutor().simple_consult_brute_query(last_expense_id_query)
212                        last_expense_id = QueryExecutor().treat_simple_result(last_expense_id, to_remove_list)
213                        last_expense_id = int(last_expense_id)
214
215                        with col6:
216                            with st.spinner(text="Aguarde..."):
217                                sleep(2.5)
218
219                            st.subheader(body=":pencil: Comprovante de Pagamento de Empréstimo")
220
221                            Receipts().generate_receipt(table="despesas", id=last_expense_id, description=debt, value=paying_value, date=today, category='Pagamento de Empréstimo', account=selected_account)
222
223                            log_query = '''INSERT INTO financas.logs_atividades (usuario_log, tipo_log, conteudo_log) VALUES ( %s, %s, %s);'''
224                            log_values = (logged_user, "Registro", "Pagou R$ {} de um empréstimo tomado na conta {}.".format(str_paying_value, account))
225                            QueryExecutor().insert_query(log_query, log_values, "Log gravado.", "Erro ao gravar log:")
226
227        elif len(not_payed_loans[0]) == 0:
228
229            col4, col5, col6 = st.columns(3)
230
231            with col5:
232
233                st.info(body="Você não tem valores a pagar.")