
    hB                         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 Zd
dZd
dZd Zd Z	d Z
y	)BankStatementClassdbc                     || _         y )N)r   )selfr   s     T/var/www/intrajisbackend.com/public_html/app/backend/classes/bank_statement_class.py__init__zBankStatementClass.__init__   s	        c                 N   	 | 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                   t        j"                        j%                  t        j                        j'                         }d}|t)        |      k  rA||   }|j                  r| j+                  |j                         |dz  }|t)        |      k  r@y y # t,        $ r!}t/        dt1        |              Y d }~y d }~ww xY w)Nr      Error: )r   queryr   idbranch_office_idpayment_type_idcollection_idbranch_office	status_id
deposit_idpayment_numbercollection_amountcollection_datedeposited_amountbank_statement_type_idbank_statement_amountbank_statement_rutdeposit_numberorder_byalllendeposit_accept	Exceptionprintstr)r   dataibank_statementes        r   compare_update_depositsz*BankStatementClass.compare_update_deposits   s2   	&77==<??<MM<LL<JJ<JJ<FF<GG<KK<NN<LL<MM<SS<RR<OO<KK  hCFFG! $ Ac$i-!%a!,,''(A(ABQ c$i-  	&GCF8$%%	&s   E5E: :	F$FF$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                  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                   |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 # t0        $ r}
t3        |
      }d| cY d }
~
S d }
~
ww xY w)	Nr   r   Invalid page numberNo data found)r   r   r   r   r   r   r    r!   r"   r#   r$   r%   r&   r'   r(   total_itemstotal_pagescurrent_pageitems_per_pager0   total_available_receipts)r   r   r   r   r   r   r!   r"   r#   r$   r%   r&   r'   r(   r   )r   r   r   r   r   r   r   r   r   r    r!   r"   r#   r$   r%   r&   r'   r(   r)   countoffsetlimitr*   filterr-   r/   r   pager<   
data_queryr9   r:   r0   r2   serialized_datar=   r3   error_messages               r   0get_comparation_pending_deposits_bank_statementszCBankStatementClass.get_comparation_pending_deposits_bank_statements1   s   b	-qy!WW]]+W+Z+Z+W+h+h+W+g+g+W+e+e+W+e+e+W+a+a+W+b+b+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%=%=&4&?&?*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" '&A#2^#2&  	-FM]O,,	-sR   EQ 6Q =Q B/Q0AQ DQ #B$QQ 
Q 	Q8Q3-Q83Q8c                 V   	 |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                  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                  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                  
      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   r6   r7   )r   rutcustomerfolior   amountbank_statement_periodr&   r'   r(   deposit_dater8   )
r   rI   rJ   rK   r   rL   rM   r&   r'   r(   r   )r   r   r   r   rN   rI   rJ   rK   r   rL   rM   r&   r'   r(   r)   r>   r?   r@   r*   rA   r%   r-   r/   rB   s               r   ,get_comparation_pending_dtes_bank_statementsz?BankStatementClass.get_comparation_pending_dtes_bank_statements   s    =	-qy!WW]]+S+V+V  YA  YN  YN  Px  P|  P|  ~f  ~o  ~o  qY  q_  q_  aI  aW  aW  YA  YH  YH  Jr  JH  JH  Jr  JH  JH  Jr  JE	  JE	  G	o	  G	~	  G	~	  	 !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(6(C(C$ #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 '&S#28#2  	-FM]O,,	-sR   DN 
6N N BM;AN  CN ?A8N 7N ;
N 	N(N#N(#N(c                 V   	 | 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(  r*|
|   }t#        j$                  |d      j'                  d      }|dk(  sLg d}dj)                  |      }t+        j,                  ||
|         rd}d}d}|
|   }t+        j.                  dd	|      }t+        j0                  d|      }|r;|d d j3                  d      }|d   j5                         }| d| }t7        d|       d}t7        d        t9        j:                  |      }t=               }|_        |_         |_!        |_"        ||_#        |_$        | j                   jK                  |       | j                   j                           S # tL        $ r}t        ddtO        |             d }~ww xY w)NzTRUNCATE TABLE bank_statementsopenpyxl)enginei  z El archivo Excel no tiene hojas.)status_codedetailr   )
sheet_namerR    u   N° DOCUMENTOMONTOFECHAz%d/%m/%Yz%Y-%m-%du   DESCRIPCIÓN MOVIMIENTO)DeposDoctoMBancoDeposDoctoOBancosu	   DepósitoDepzPago RemuneracionesTrabajo|r   z
76063822-6   z[^\dkK]z\d{8,9}[\dkK]0-zMatch:u   No es un RUT válido en formatozError al leer el Excel: )(r   executer   commitrequestsgetraise_for_statusr   contentpd	ExcelFilesheet_namesr   
read_excelfillnaiterrowscolumnsr   strptimestrftimejoinresearchsub	fullmatchlstripupperr.   r
   fix_current_dte_periodr   r%   rI   r(   rL   periodrN   addr-   r/   )r   file_urlry   response
excel_filexlsrj   dfr0   indexrowrow_datacolr(   rL   rN   wordspatternr%   rI   rawcleanedmatchcuerpodvfixed_periodr2   r3   s                               r   read_store_bank_statementz,BankStatementClass.read_store_bank_statement   s   G	]GGOOD!ABCGGNN||H-H%%' !1!12J,,z*=C//K#<^__s{1~jQB2BD kkm /!
s::  ICo-),Sg~!$Sg~'*3x'/'8'8z'R'['[\f'g77 "%((5/99Wc#h7562".C562"%c(C&(ffZS&AG$&LL1A7$KE$)0")<)<S)A%,R[%6%6%8)/"&6 %h 4&' %&G HA ID  +AA&I!3!58N5%("0>-(.%(4%.:+N+ _/!b "! 	]C:RSVWXSYRZ8[\\	]s   EJ D5J 	J(
J##J(c                 d   | j                   j                  t              j                  t        j                  |k(        j                  t        j
                  dk(        j                         }d|_        ||_        | j                   j                  |       | j                   j                          y )Nr      )r   r   r   rA   rK   dte_version_idfirstr   payment_daterz   rc   )r   r   r   dtes       r   customer_acceptz"BankStatementClass.customer_accept   su    ggmmH%,,X^^r-ABII(JaJaefJfgmmo'Cr   c                 ,   t        |       | j                  j                  t              j	                  t        j
                  |k(        j                         }d|_        | j                  j                  |       | j                  j                          y )N   )
r.   r   r   r	   rA   r   r   r   rz   rc   )r   r   r   s      r   r,   z!BankStatementClass.deposit_accept(  s]    b	ggmmL)00B1FGMMOCr   N)r   i)__name__
__module____qualname__r   r   r4   rG   rO   r   r   r,    r   r   r   r      s3    7 &Bc-J>-@H]Tr   r   )r   sqlalchemy.ormr   app.backend.db.modelsr   r   r   r   r	    app.backend.classes.helper_classr
   fastapir   
sqlalchemyr   rd   ior   pandasrh   rr   r   r   r   r   <module>r      s8     " e  e 8 !     	b br   