
    g18                         d dl m Z  d dlmZ d dlmZmZmZmZmZ d dl	m
Z
 d dlmZ d dlmZ d dlZd dlmZ d dlZd dlZ G d	 d
      Zy)    )datetime)Session)BankStatementModel(ComparationPendingDtesBankStatementModelDteModel,ComparationPendingDepositsBankStatementModelDepositModel)HelperClass)HTTPException)textN)BytesIOc                   :    e Zd ZdefdZd	dZd	dZd Zd Zd Z	y)
BankStatementClassdbc                     || _         y )N)r   )selfr   s     ]C:\Users\jesus\OneDrive\Desktop\escritorio\newerp\app\backend\classes\bank_statement_class.py__init__zBankStatementClass.__init__   s	        c                    	 |dk7  rf| j                   j                  t        j                  t        j                  t        j
                  t        j                  t        j                  t        j                  t        j                  t        j                  t        j                  t        j                  t        j                  t        j                  t        j                  t        j                         j#                  t        j                        }|j%                         }||z   dz
  |z  }|dk  s||kD  ry|j'                  |dz
  |z        j)                  |      j+                         }|sy|D cg c]  }|j                  |j                  |j
                  |j                  |j                  |j                  |j                  |j                  |j                  |j                  |j                  |j                  |j                  |j                   d }}| j                   j                  t              j-                  t        j                  dk(        j%                         }	||||||	dS | j                   j                  t        j                  t        j                  t        j
                  t        j                  t        j                  t        j                  t        j                  t        j                  t        j                  t        j                  t        j                  t        j                  t        j                  t        j                         j#                  t        j                        j+                         }D cg c]  }|j                  |j                  |j
                  |j                  |j                  |j                  |j                  |j                  |j                  |j                  |j                  |j                  |j                  |j                   d }}|S c c}w c c}w # t.        $ r}
t1        |
      }d| cY d }
~
S d }
~
ww xY w)Nr      Invalid page numberNo data found)idbranch_office_idpayment_type_idcollection_idbranch_office	status_idpayment_numbercollection_amountcollection_datedeposited_amountbank_statement_type_idbank_statement_amountbank_statement_rutdeposit_numbertotal_itemstotal_pagescurrent_pageitems_per_pagedatatotal_available_receiptsError: )r   queryr   r   r   r   r   r   r   r    r!   r"   r#   r$   r%   r&   r'   order_bycountoffsetlimitallfilter	Exceptionstrr   pager,   
data_queryr)   r*   r-   bank_statementserialized_datar.   eerror_messages               r   0get_comparation_pending_deposits_bank_statementszCBankStatementClass.get_comparation_pending_deposits_bank_statements   s   `	-qy!WW]]+W+Z+Z+W+h+h+W+g+g+W+e+e+W+e+e+W+a+a+W+f+f+W+i+i+W+g+g+W+h+h+W+n+n+W+m+m+W+j+j+W+f+fh !!M!P!PQ   )..0*^;a?NR!8tk10!(($(n)DEKKN[__a*" -1#2 ) -//,:,K,K+9+I+I)7)E)E)7)E)E%3%=%=*8*G*G-;-M-M+9+I+I,:,K,K2@2W2W1?1U1U.<.O.O*8*G*G$ #2 #2" ,077==9e+f+m+m  o[  or  or  vw  ow  ,x  ,~  ,~  ,@( $/#.$(&4+0H  "WW]],X,[,[+W+h+h+W+g+g+W+e+e+W+e+e+W+a+a+W+f+f+W+i+i+W+g+g+W+h+h+W+n+n+W+m+m+W+j+j+W+f+f*  !!M!P!PQRURURW! B -1#2 ) -//,:,K,K+9+I+I)7)E)E)7)E)E%3%=%=*8*G*G-;-M-M+9+I+I,:,K,K2@2W2W1?1U1U.<.O.O*8*G*G$ #2 #2" '&#2\#2&  	-FM]O,,	-sR   D4P; 76P; .P; 2B$P1AP; .DP; 	B$P6-P; 1
P; ;	QQQQc                 "   	 |dk7  r| j                   j                  t        j                  t        j                  t        j
                  t        j                  t        j                  t        j                  t        j                  t        j                  t        j                  t        j                  
      j                  t        j                        }|j                         }||z   dz
  |z  }|dk  s||kD  ry|j                  |dz
  |z        j!                  |      j#                         }|sy|D cg c]s  }|j                  |j                  |j
                  |j                  |j                  |j                  |j                  |j                  |j                  |j                  d
u }}| j                   j                  t              j%                  t        j&                  dk(        j                         }	||||||	dS | j                   j                  t        j                  t        j                  t        j
                  t        j                  t        j                  t        j                  t        j                  t        j                  t        j                  t        j                  
      j                  t        j                        j#                         }D cg c]s  }|j                  |j                  |j
                  |j                  |j                  |j                  |j                  |j                  |j                  |j                  d
u }}|S c c}w c c}w # t(        $ r}
t+        |
      }d| cY d }
~
S d }
~
ww xY w)Nr   r   r   r   )
r   rutcustomerfolior   amountbank_statement_periodr%   r&   r'   r(   r/   )r   r0   r   r   rB   rC   rD   r   rE   rF   r%   r&   r'   r1   r2   r3   r4   r5   r6   r$   r7   r8   r9   s               r   ,get_comparation_pending_dtes_bank_statementsz?BankStatementClass.get_comparation_pending_dtes_bank_statementss   s   <	-qy!WW]]+S+V+V  YA  YE  YE  Go  Gx  Gx  zb  zh  zh  jR  j`  j`  bJ  bQ  bQ  S{  SQ  SQ  S{  SQ  SQ  S{  SN  SN  Px  PG	  PG	  H	 !I!L!LM  )..0*^;a?NR!8tk10!(($(n)DEKKN[__a* -1#2 ) -//-11$2$;$;!/!5!5)7)E)E"0"7"71?1U1U1?1U1U.<.O.O*8*G*G$ #2 #2 ,077==9a+b+i+i  kS  kj  kj  no  ko  ,p  ,v  ,v  ,x( $/#.$(&4+0H  "WW]]+S+V+V  YA  YE  YE  Go  Gx  Gx  zb  zh  zh  jR  j`  j`  bJ  bQ  bQ  S{  SQ  SQ  S{  SQ  SQ  S{  SN  SN  Px  PG	  PG	  H	 !I!L!LMcce  -1#2 ) -//-11$2$;$;!/!5!5)7)E)E"0"7"71?1U1U1?1U1U.<.O.O*8*G*G$ #2 #2 '&Q#26#2  	-FM]O,,	-sR   C8M+ ;6M+ 2M+ 6A8M!.AM+ CM+ %A8M&M+ !
M+ +	N4N	N	Nc                 f   	 | j                   j                  t        d             | j                   j                          t	        j
                  |      }|j                          t        |j                        }t        j                  |d      }|j                  }|st        dd      t        j                  ||d   d      }|j                  d	      }g }|j                         D ]  \  }	}
i }|j                   D ]  }|d
k(  r|
|   }|dk(  r|
|   }|dk(  sg d}dj#                  |      }t%        j&                  ||
|         rd}d}Pd}t%        j&                  d|
|         }|sn|j)                  d      }|dd  d|d    } t+        j,                  |      }t/               }|_        |_        |_        |_        ||_        | j                   j;                  |       | j                   j                           S # t<        $ r}t        ddt?        |             d }~ww xY w)NzTRUNCATE TABLE bank_statementsopenpyxl)enginei  z El archivo Excel no tiene hojas.)status_codedetailr   )
sheet_namerJ    u   N° DOCUMENTOMONTOu   DESCRIPCIÓN MOVIMIENTO)DeposDoctoMBancoDeposDoctoOBancosu	   DepósitoDepzPago RemuneracionesTrabajo|r   z
76063822-6   z\d{10}	   -zError al leer el Excel: ) r   executer   commitrequestsgetraise_for_statusr   contentpd	ExcelFilesheet_namesr   
read_excelfillnaiterrowscolumnsjoinresearchgroupr
   fix_current_dte_periodr   r$   rB   r'   rE   periodaddr7   r8   )r   file_urlrj   response
excel_filexlsr`   dfr-   indexrowrow_datacolr'   rE   wordspatternr$   rB   matchnumberfixed_periodr<   r>   s                           r   read_store_bank_statementz,BankStatementClass.read_store_bank_statement   s   8	]GGOOD!ABCGGNN||H-H%%' !1!12J,,z*=C//K#<^__s{1~jQB2BD kkm  !
s:: CCo-),Sg~!$S77 "%((5/99Wc#h7562".C562$&IIiS$BE$).Q)/!Qvayk&B%C(  +AA&I!3!58N5%("0>-(.%(4%N+ A !D "! 	]C:RSVWXSYRZ8[\\	]s&   DH	 AH	 -BH	 		H0H++H0c                    | j                   j                  t              j                  t        j                  |k(        j                         }d|_        | j                   j                  |       | j                   j                          y )N   )	r   r0   r   r6   r   firstr   rk   rY   r   r   dtes      r   customer_acceptz"BankStatementClass.customer_accept   sV    ggmmH%,,X[[B->?EEGCr   c                    | j                   j                  t              j                  t        j                  |k(        j                         }d|_        | j                   j                  |       | j                   j                          y )N   )	r   r0   r	   r6   r   r}   r   rk   rY   r~   s      r   deposit_acceptz!BankStatementClass.deposit_accept   sV    ggmmL)00B1FGMMOCr   N)r   i)
__name__
__module____qualname__r   r   r@   rG   rz   r   r    r   r   r   r      s,    7 a-F=-~9]vr   r   )r   sqlalchemy.ormr   app.backend.db.modelsr   r   r   r   r	    app.backend.classes.helper_classr
   fastapir   
sqlalchemyr   rZ   ior   pandasr^   rf   r   r   r   r   <module>r      s8     " e  e 8 !     	m mr   