loans.receive_loan

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