reports.receipts

  1import pandas as pd
  2import streamlit as st
  3from dictionary.style import system_font
  4from datetime import datetime
  5from dictionary.vars import (
  6    operational_system,
  7    today,
  8    actual_horary,
  9    to_remove_list,
 10    absolute_app_path,
 11    transfer_image,
 12    SAVE_FOLDER
 13)
 14from dictionary.sql import user_current_accounts_query, account_image_query
 15from functions.query_executor import QueryExecutor
 16from functions.login import Login
 17from functions.variable import Variable
 18from PIL import Image, ImageDraw, ImageFont
 19from time import sleep
 20
 21
 22class Receipts:
 23    """
 24    Classe responsável pela geração e consulta de comprovantes.
 25    """
 26
 27    def validate_query(self, table: str,
 28                       date: str, account: str, value: float):
 29        """
 30        Realiza a validação da consulta passada como consulta.
 31
 32        Parameters
 33        ----------
 34        table : str
 35            A tabela da consulta.
 36        date : str
 37            A data da consulta.
 38        account : str
 39            A conta da consulta.
 40        value : float
 41            O valor da consulta.
 42
 43        Returns
 44        -------
 45        ids_string : Any
 46            A string com os id's que correspondem a consulta,
 47            caso a mesma seja válida.
 48        boolean
 49            Se a consulta é válida.
 50        """
 51        user_name, user_document = Login().get_user_data(
 52            return_option="user_doc_name"
 53        )
 54
 55        if table == "despesas":
 56            id_query = """
 57            SELECT
 58                despesas.id_despesa
 59            FROM
 60                despesas
 61                INNER JOIN usuarios ON
 62                    despesas.proprietario_despesa = usuarios.nome
 63                AND
 64                despesas.documento_proprietario_despesa = usuarios.documento
 65            WHERE
 66                despesas.data = '{}'
 67            AND despesas.conta = '{}'
 68            AND despesas.valor = {}
 69            AND usuarios.nome = '{}'
 70            AND usuarios.documento = {};""".format(
 71                date.strftime("%Y-%m-%d"),
 72                account, value, user_name, user_document)
 73
 74        if table == "receitas":
 75            id_query = """
 76            SELECT
 77                receitas.id_receita
 78            FROM
 79                receitas
 80                INNER JOIN usuarios ON
 81                    receitas.proprietario_receita = usuarios.nome
 82                AND
 83                receitas.documento_proprietario_receita = usuarios.documento
 84            WHERE
 85                receitas.data = '{}'
 86            AND receitas.conta = '{}'
 87            AND receitas.valor = {}
 88            AND usuarios.nome = '{}'
 89            AND usuarios.documento = {};""".format(
 90                date.strftime("%Y-%m-%d"),
 91                account, value, user_name, user_document)
 92
 93        if table == "despesas_cartao_credito":
 94            id_query = """
 95            SELECT
 96                dpscc.id_despesa_cartao
 97            FROM
 98            despesas_cartao_credito AS dpscc
 99                INNER JOIN
100            usuarios AS users ON
101            despesas_cartao_credito.proprietario_despesa_cartao = users.nome
102            AND
103            dpscc.doc_proprietario_cartao = users.documento
104            WHERE
105                dpscc.data = '{}'
106            AND dpscc.cartao = '{}'
107            AND dpscc.valor = {}
108            AND users.nome = '{}'
109            AND users.documento = {};""".format(
110                date.strftime("%Y-%m-%d"),
111                account, value, user_name, user_document)
112
113        if table == "emprestimos":
114            id_query = """
115            SELECT
116                emprestimos.id_emprestimo
117            FROM
118                emprestimos
119                    INNER JOIN
120                usuarios ON emprestimos.credor = usuarios.nome
121                    AND emprestimos.documento_credor = usuarios.documento
122            WHERE
123                emprestimos.data = '{}'
124            AND emprestimos.conta = '{}'
125            AND emprestimos.valor = {}
126            AND usuarios.nome = '{}'
127            AND usuarios.documento = {};""".format(
128                date.strftime("%Y-%m-%d"),
129                account, value, user_name, user_document)
130
131        id = QueryExecutor().complex_consult_brute_query(id_query)
132        id = QueryExecutor().treat_numerous_simple_result(id, to_remove_list)
133
134        if len(id) >= 1:
135            return id, True
136
137        elif len(id) == 0:
138            return 0, False
139
140    def execute_query(self, table: str, id_list: list):
141        """
142        Realiza a consulta informada.
143
144        Parameters
145        ----------
146        table : str
147            A tabela da consulta.
148        id_list : list
149            A lista com os ids da consulta.
150        """
151        placeholders = ", ".join(["%s"] * len(id_list))
152
153        if table == "despesas_cartao_credito":
154            values_query = """
155            SELECT
156                descricao, valor, data, horario, categoria, cartao
157            FROM
158                despesas_cartao_credito WHERE id_despesa_cartao IN %s;"""
159            values_query = values_query.replace(
160                "IN %s", f"IN ({placeholders})"
161            )
162
163        elif table == "receitas":
164            values_query = """
165            SELECT
166                descricao, valor, data, horario, categoria, conta
167            FROM
168                receitas WHERE id_receita IN %s;"""
169            values_query = values_query.replace(
170                "IN %s", f"IN ({placeholders})"
171            )
172
173        elif table == "despesas":
174            values_query = """
175            SELECT
176                descricao, valor, data, horario, categoria, conta
177            FROM
178                despesas WHERE id_despesa IN %s;"""
179            values_query = values_query.replace(
180                "IN %s", f"IN ({placeholders})"
181            )
182
183        consult_values = QueryExecutor().complex_compund_query(
184            values_query, 6, params=(*id_list,)
185        )
186
187        return consult_values
188
189    def treat_receipt_values(self, receipt_list: list):
190        """
191        Realiza o tratamento dos valores do comprovante.
192
193        Parameters
194        ----------
195        receipt_list : list
196            A lista com os valores do comprovante.
197
198        Returns
199        -------
200        description_list : list
201            A lista com a descrição do comprovante.
202        value_list : list
203            A lista com o valor do comprovante.
204        date_list : list
205            A lista com as datas do comprovante.
206        time_list : list
207            A lista com os horários do comprovante.
208        category_list : list
209            A lista com as categorias do comprovante.
210        account_list : list
211            A lista com as contas do comprovante.
212        """
213
214        len_lists_receipt = 0
215        for i in range(0, len(receipt_list)):
216            len_lists_receipt += len(receipt_list[i])
217
218        if len(receipt_list) >= 5 and len_lists_receipt >= 5:
219
220            description = receipt_list[0]
221            description_list = []
222
223            for i in range(0, len(description)):
224                aux_description = QueryExecutor().treat_simple_result(
225                    description[i], to_remove_list)
226                description_list.append(aux_description)
227
228            value = receipt_list[1]
229            value_list = []
230
231            for i in range(0, len(value)):
232                aux_value = QueryExecutor().treat_simple_result(
233                    value[i], to_remove_list)
234                aux_value = float(aux_value)
235                value_list.append(aux_value)
236
237            date = receipt_list[2]
238            date_list = []
239
240            for i in range(0, len(date)):
241                aux_date = QueryExecutor().treat_simple_result(
242                    date[i], to_remove_list)
243                aux_date = aux_date.replace(" ", "-")
244                date_list.append(aux_date)
245
246            time = receipt_list[3]
247            time_list = []
248
249            for i in range(0, len(time)):
250                aux_time = QueryExecutor().treat_simple_result(
251                    time[i], to_remove_list)
252                aux_time = str(aux_time)
253                time_list.append(aux_time)
254
255            category = receipt_list[4]
256            category_list = []
257
258            for i in range(0, len(category)):
259                aux_category = QueryExecutor().treat_simple_result(
260                    category[i], to_remove_list)
261                category_list.append(aux_category)
262
263            account = receipt_list[5]
264            account_list = []
265
266            for i in range(0, len(account)):
267                aux_account = QueryExecutor().treat_simple_result(
268                    account[i], to_remove_list)
269                account_list.append(aux_account)
270
271            return description_list, value_list, date_list, time_list,
272            category_list, account_list
273
274    def generate_transfer_receipt(self, id: int, description: str,
275                                  value: float, date: str, category: str,
276                                  origin_account: str, destiny_account: str):
277        """
278        Gera o comprovante de transferência.
279
280        Parameters
281        ----------
282        id : int
283            O ID do registro do comprovante.
284        description : str
285            A descrição da transferência.
286        value : float
287            O valor da transferência.
288        date : str
289            A data da transferência.
290        category : str
291            A categoria da transferência.
292        origin_account : str
293            A conta de origem da transferência.
294        destiny_account : str
295            A conta de destino da transferência.
296        """
297        user_name, user_document = Login().get_user_data(
298            return_option="user_doc_name"
299        )
300        origin_account_image = QueryExecutor().simple_consult_query(
301            query=account_image_query,
302            params=(origin_account, user_name, user_document)
303        )
304        origin_account_image = QueryExecutor().treat_simple_result(
305            origin_account_image, to_remove_list
306        )
307        origin_account_image_path = SAVE_FOLDER + origin_account_image
308        origin_pasted_image = Image.open(origin_account_image_path)
309
310        destiny_account_image = QueryExecutor().simple_consult_query(
311            query=account_image_query,
312            params=(destiny_account, user_name, user_document)
313        )
314        destiny_account_image = QueryExecutor().treat_simple_result(
315            destiny_account_image, to_remove_list)
316        destiny_account_image_path = SAVE_FOLDER + destiny_account_image
317        destiny_pasted_image = Image.open(destiny_account_image_path)
318
319        loaded_transfer_image = Image.open(transfer_image)
320
321        float_value = round(value, 2)
322
323        str_value = Variable().treat_complex_string(float_value)
324
325        reference_number = ""
326        if id <= 9:
327            reference_number = """REF: 000{}""".format(id)
328        if id >= 10 and id <= 99:
329            reference_number = """REF: 00{}""".format(id)
330        if id >= 100 and id <= 999:
331            reference_number = """REF: 0{}""".format(id)
332
333        width, height = 900, 450
334        dpi = 300
335        image = Image.new("RGB", (width, height), "white")
336        draw = ImageDraw.Draw(image)
337        font_size = 20
338
339        if operational_system == "nt":
340            font = ImageFont.truetype("cour.ttf", font_size)
341        elif operational_system == "posix":
342            font = ImageFont.truetype("{}{}".format(
343                absolute_app_path, system_font), font_size,
344            )
345
346        border_color = "black"
347        border_width = 4
348        border_box = [
349            (border_width, border_width),
350            (width - border_width, height - border_width),]
351        draw.rectangle(border_box, outline=border_color, width=border_width)
352
353        header_font_size = 20
354
355        if operational_system == "nt":
356            header_font = ImageFont.truetype("cour.ttf", header_font_size)
357        elif operational_system == "posix":
358            header_font = ImageFont.truetype("{}{}".format(
359                absolute_app_path, system_font), font_size,)
360
361        header_text = "Comprovante de Transferência"
362        bbox = draw.textbbox((0, 0), header_text, font=header_font)
363        header_text_width = bbox[2] - bbox[0]
364        header_text_height = bbox[3] - bbox[1]
365        print(header_text_height)
366
367        header_position = ((width - header_text_width) / 2, 10)
368        draw.text(header_position, header_text, fill="black", font=header_font)
369
370        draw.line([(20, 40), (width - 20, 40)], fill="black", width=2)
371        draw.text((20, 60), f"Descrição: {description}",
372                  fill="black", font=font)
373        draw.text((20, 90), f"Valor: R$ {str_value}",
374                  fill="black", font=font)
375        draw.text((20, 120), f"Data: {date.strftime('%d/%m/%Y')}",
376                  fill="black", font=font)
377        draw.text((20, 150), f"Categoria: {category}",
378                  fill="black", font=font)
379        draw.text((20, 180), f"Conta de Origem: {origin_account}",
380                  fill="black", font=font)
381        draw.text((20, 210), f"Conta de Destino: {destiny_account}",
382                  fill="black", font=font)
383        draw.line([(20, 240), (width - 20, 240)], fill="black", width=2)
384        draw.line([(width - 400, height - 60),
385                  (width - 20, height - 60)], fill="black", width=2)
386        draw.text((520, 400), f"{user_name}", fill="black", font=font)
387        draw.text((20, height - 40), reference_number, fill="black", font=font)
388
389        image.paste(origin_pasted_image, (20, 250))
390        image.paste(loaded_transfer_image, (170, 250))
391        image.paste(destiny_pasted_image, (320, 250))
392
393        archive_path = "{}/data/transfers/Comprovante_transferencia_{}_{}.png"
394        archive_path = archive_path.format(
395            absolute_app_path, today, actual_horary
396        )
397
398        image.save(archive_path, dpi=(dpi, dpi))
399        st.image(archive_path, use_container_width=True)
400
401        with open(archive_path, "rb") as file:
402            download_button = st.download_button(
403                label=":floppy_disk: Baixar imagem",
404                data=file,
405                file_name=archive_path
406            )
407            print(download_button)
408
409    def generate_receipt(
410            self,
411            table: str,
412            id: int,
413            description: str,
414            value: float,
415            date,
416            category: str,
417            account: str
418    ):
419        """
420        Gera o comprovante de despesa/receita.
421
422        Parameters
423        ----------
424        id : int
425            O ID do registro do comprovante.
426        description : str
427            A descrição da despesa/receita.
428        value : float
429            O valor da despesa/receita.
430        date : str
431            A data da despesa/receita.
432        category : str
433            A categoria da despesa/receita.
434        account : str
435            A conta da despesa/receita.
436        """
437        user_name, user_document = Login().get_user_data(
438            return_option="user_doc_name"
439        )
440
441        account_image = QueryExecutor().simple_consult_query(
442            query=account_image_query,
443            params=(account, user_name, user_document))
444        account_image = QueryExecutor().treat_simple_result(
445            account_image,
446            to_remove_list
447        )
448        account_image_path = SAVE_FOLDER + account_image
449
450        table_dictionary = {
451            "receitas": "Receita",
452            "emprestimos": "Empréstimo",
453            "despesas": "Despesa",
454            "despesas_cartao_credito": "Despesa de Cartão"
455        }
456
457        table = table_dictionary[table]
458        value = round(value, 2)
459        value = Variable().treat_complex_string(value)
460
461        reference_number = ""
462        if id <= 9:
463            reference_number = """REF: 000{}""".format(id)
464        if id >= 10 and id <= 99:
465            reference_number = """REF: 00{}""".format(id)
466        if id >= 100 and id <= 999:
467            reference_number = """REF: 0{}""".format(id)
468
469        table = table.capitalize()
470        description = description.replace("'", "")
471        category = category.capitalize()
472        category = category.replace("'", "")
473        account = account.replace("'", "")
474
475        date = datetime.strptime(date, "%Y-%m-%d")
476        date = date.strftime("%d/%m/%Y")
477
478        width, height = 800, 400
479        dpi = 300
480        image = Image.new("RGB", (width, height), "white")
481        draw = ImageDraw.Draw(image)
482        font_size = 20
483
484        if operational_system == "nt":
485            font = ImageFont.truetype("cour.ttf", font_size)
486        elif operational_system == "posix":
487            font = ImageFont.truetype("{}{}".format(
488                absolute_app_path, system_font), font_size)
489
490        border_color = "black"
491        border_width = 4
492        border_box = [(border_width, border_width),
493                      (width - border_width, height - border_width)]
494        draw.rectangle(border_box, outline=border_color, width=border_width)
495
496        header_font_size = 20
497        print(header_font_size)
498
499        if operational_system == "nt":
500            header_font = ImageFont.truetype("cour.ttf", font_size)
501        elif operational_system == "posix":
502            header_font = ImageFont.truetype("{}{}".format(
503                absolute_app_path, system_font), font_size,)
504
505        header_text = "Comprovante de {}".format(table)
506        bbox = draw.textbbox((0, 0), header_text, font=header_font)
507        header_text_width = bbox[2] - bbox[0]
508        header_text_height = bbox[3] - bbox[1]
509        print(header_text_height)
510
511        header_position = ((width - header_text_width) / 2, 10)
512        draw.text(header_position, header_text, fill="black", font=header_font)
513
514        draw.line([(20, 40), (width - 20, 40)], fill="black", width=2)
515        draw.text(
516            (20, 60),
517            f"Descrição: {description}",
518            fill="black",
519            font=font
520        )
521        draw.text((20, 90), f"Valor: R$ {value}", fill="black", font=font)
522        draw.text((20, 120), f"Data: {date}", fill="black", font=font)
523        draw.text((20, 150), f"Categoria: {category}", fill="black", font=font)
524        draw.text((20, 180), f"Conta: {account}", fill="black", font=font)
525        draw.line([(20, 210), (width - 20, 210)], fill="black", width=2)
526        draw.line([(width - 400, height - 60),
527                  (width - 20, height - 60)], fill="black", width=2)
528        draw.text((400, 360), f"{user_name}", fill="black", font=font)
529        draw.text((20, height - 40), reference_number, fill="black", font=font)
530
531        pasted_image = Image.open(account_image_path)
532
533        image.paste(pasted_image, (20, 220))
534
535        archive_path = "{}/data/reports/Relatorio_{}_{}.png".format(
536            absolute_app_path,
537            today,
538            actual_horary
539        )
540        image.save(archive_path, dpi=(dpi, dpi))
541        st.image(archive_path, use_container_width=True)
542
543        with open(archive_path, "rb") as file:
544            download_button = st.download_button(
545                label=":floppy_disk: Baixar imagem",
546                data=file,
547                file_name="Relatorio_{}_{}.png".format(today, actual_horary),)
548
549            print(download_button)
550
551    def main_menu(self):
552        """
553        Coleta os dados da consulta do comprovante.
554        """
555        user_name, user_document = Login().get_user_data(
556            return_option="user_doc_name"
557        )
558        logged_user, logged_user_password = Login().get_user_data(
559            return_option="user_login_password")
560
561        col4, col5, col6 = st.columns(3)
562
563        user_current_accounts = QueryExecutor().complex_consult_query(
564            query=user_current_accounts_query,
565            params=(user_name, user_document)
566        )
567        user_current_accounts = QueryExecutor().treat_numerous_simple_result(
568            user_current_accounts, to_remove_list)
569
570        if len(user_current_accounts) > 0:
571
572            with col4:
573
574                receipt_options = {
575                    "Despesa": "despesas",
576                    "Despesa de Cartão": "despesas_cartao_credito",
577                    "Receita": "receitas",
578                    "Empréstimo": "emprestimos"
579                }
580
581                st.subheader(body=":computer: Entrada de Dados")
582
583                with st.expander(label="Filtros", expanded=True):
584                    report_type = st.selectbox(
585                        label="Relatório", options=receipt_options.keys())
586                    date = st.date_input(label="Data")
587                    account = st.selectbox(
588                        label="Conta", options=user_current_accounts)
589                    value = st.number_input(
590                        label="Valor",
591                        placeholder="Informe o valor",
592                        min_value=0.01,
593                        step=0.01
594                    )
595                    confirm_data = st.checkbox(label="Confirmar dados")
596
597                if confirm_data:
598
599                    table = receipt_options[report_type]
600
601                    with col5:
602                        with st.spinner(text="Aguarde..."):
603                            sleep(0.5)
604                        st.subheader(body=":page_facing_up: Resultados")
605
606                        query_data, is_query_valid = self.validate_query(
607                            table, date, account, value)
608
609                        if is_query_valid:
610
611                            with st.expander(label=":bar_chart: Resultados",
612                                             expanded=True):
613
614                                ids_string = ""
615                                for i in range(0, len(query_data)):
616                                    if i == (len(query_data) - 1):
617                                        ids_string += "ID " + \
618                                            str(query_data[i])
619                                    else:
620                                        ids_string += "ID " + \
621                                            str(query_data[i]) + ", "
622
623                                st.info(
624                                    "Registro(s) encontrado(s): {}.".format(
625                                        ids_string)
626                                )
627
628                                query = self.execute_query(table, query_data)
629                                (
630                                    description,
631                                    value,
632                                    date,
633                                    time,
634                                    category,
635                                    account) = self.treat_receipt_values(
636                                    query)
637
638                                str_value_list = []
639
640                                for i in range(0, len(value)):
641                                    aux_value = Variable(
642                                    ).treat_complex_string(value[i])
643                                    aux_value = 'R$ ' + aux_value
644                                    str_value_list.append(aux_value)
645
646                                formatted_date_list = []
647
648                                for i in range(0, len(date)):
649                                    aux_date = date[i]
650                                    aux_date = datetime.strptime(
651                                        aux_date, '%Y-%m-%d')
652                                    aux_date = aux_date.strftime('%d/%m/%Y')
653                                    formatted_date_list.append(aux_date)
654
655                                formatted_time_list = []
656
657                                for i in range(0, len(time)):
658                                    aux_time = time[i]
659                                    formatted_time_list.append(aux_time)
660
661                                str_ids_list = query_data
662
663                                ids_list = []
664                                for i in range(0, len(str_ids_list)):
665                                    aux_int = int(str_ids_list[i])
666                                    ids_list.append(aux_int)
667
668                                data_df = pd.DataFrame(
669                                    {
670                                        "ID": ids_list,
671                                        "Descrição": description,
672                                        "Valor": str_value_list,
673                                        "Data": formatted_date_list,
674                                        "Horário": formatted_time_list,
675                                        "Categoria": category,
676                                        "Conta": account
677                                    })
678
679                                st.dataframe(data_df, hide_index=True,
680                                             use_container_width=True)
681                                select_id_register = st.selectbox(
682                                    label="Selecione o ID do registro",
683                                    options=ids_list
684                                )
685                                id_list_index = ids_list.index(
686                                    select_id_register)
687
688                                confirm_register_selection = st.checkbox(
689                                    label="Confirmar seleção")
690
691                            receipt_button = st.button(
692                                label=":pencil: Gerar Comprovante")
693
694                            if (confirm_register_selection and receipt_button):
695                                with col6:
696                                    with st.spinner(text="Aguarde..."):
697                                        sleep(2.5)
698                                    st.subheader(body=":pencil: Comprovante")
699                                    self.generate_receipt(
700                                        table,
701                                        select_id_register,
702                                        description[id_list_index],
703                                        value[id_list_index],
704                                        date[id_list_index],
705                                        category[id_list_index],
706                                        account[id_list_index]
707                                    )
708
709                                log_query = '''
710                                    INSERT INTO
711                                        financas.logs_atividades
712                                        (usuario_log, tipo_log, conteudo_log)
713                                    VALUES ( %s, %s, %s);
714                                    '''
715                                log_values = (
716                                    logged_user,
717                                    "Consulta",
718                                    """Consultou comprovante de {} na data {},
719                                        associado a conta {}.""".format(
720                                        report_type,
721                                        date,
722                                        account
723                                    )
724                                )
725                                QueryExecutor().insert_query(
726                                    log_query,
727                                    log_values,
728                                    "Log gravado.",
729                                    "Erro ao gravar log:"
730                                )
731
732                        elif is_query_valid is False:
733                            with st.expander(
734                                label="Resultados",
735                                expanded=True
736                            ):
737                                st.info("Nenhum resultado Encontrado.")
738
739        elif len(user_current_accounts) == 0:
740            with col5:
741                st.warning(body="Você ainda não possui contas cadastradas.")