?j fdZddlmZddlZddlZddlZddlZddlZddlZddl m Z ddl m Z ddl m Z ddlmZmZddlZddlZddlZddlmZdd lmZdd lmZdd lmZdd lmZejd Z ddl!m"Z"e"e#$j%dZ&ej'de(e&dz dz Z)ddgZ*ddddddddZ+dddd d d!Z,Gd"d#Z-dS)$a"Classifier and Router for Bank Statements and Payment Orders (UNC). Automatically detects if an uploaded document is a bank statement or UNC, classifies the issuing bank, safely routes the file to the dedicated Hot Watcher folder, and triggers automatic transaction parsing and ingestion. ) annotationsN)datetime)Decimal)Any)UUIDuuid4)BasePostgresClient)ACBExcelParser) TCBPdfParser)VCBExcelParser)VPBankExcelParserz(dscons.banking.unc_classifier_and_router)PathUNC_HOT_WATCHER_ROOTFinancezUNC-202639912136 0202111150zACB-2026zTechcombank-2026z VPBank 2026zVietcombank-2026zVietinbank-2026z BIDV-2026 Other-2026)ACB TECHCOMBANKVPBANK VIETCOMBANK VIETINBANKBIDVOTHER) 13456888888&CÔNG TY TNHH XÂY DỰNG ĐỊNH SƠN)19039912136018r) 8667898888rUNKNOWNr)rrrrrceZdZdZd"d#dZe d$d%dZe d"d&dZ d'd(dZd)dZ d)dZ ed*d!Z dS)+UncClassifierAndRouterzZDetects UNC / Statements, routes files to Hot Watcher directories, and triggers ingestion.Npostgres_client Any | NonereturnNonec0|p t|_dSN)r _client)selfr$s OC:\Projects\DSCons\app\modules\banking\application\unc_classifier_and_router.py__init__zUncClassifierAndRouter.__init__As&>*<*>*> Tfilenamestrtextcontent bytes | Noneenable_vision_ocrbool'tuple[bool, str | None, dict[str, Any]]c|d|gd}tfd|D}gd}tfd|D}|s|r|sdvrddnd}|d kr d d l} | |d } g} | d d D]@} | d} | r| | A| | rId | }d|tfd|D}n#t$r tj tj|}d|jd d D} t| rId | }d|tfd|D}n#t$rYnwxYwYnwxYwd }tfddDrd}ntfddDrd}nwtfddDrd}nYtfddDrd}n;tfdd Drd!}ntfd"d#Drd$}|s|rd%}t#|p|p|otfd&d'D}d(}d }|ot%|d)ko| o| }|r|s|r |r|d%kr |r dvrddnd}|d*vr d d+lm}|}||||}|r|d,s|d-r]|d,r|d,d krd.}d.}|}|d-r|d-d%kr|d-}n2#t$r%}t.d/|Yd }~nd }~wwxYw|||||||r|s|rd0n|rd1nd2d3}|||fS)4zDetermines if a document is an UNC / Bank Statement and identifies the bank. Returns: (is_unc, bank_code, details_dict)  )uncz uy nhiem chi uynhiemchi uy_nhiem_chisaokezsao kesao_ke statement accountstmt debitnote creditnote debit_note credit_notetransaction_historyb2bz pass.docxc3 K|]}|vV dSr)).0kfn_lowers r, z=UncClassifierAndRouter.detect_unc_and_bank..Ys'KKa8mKKKKKKr.)uỦY NHIỆM CHIz UY NHIEM CHIuLỆNH CHUYỂN TIỀNzLENH CHUYEN TIENuGIẤY BÁO NỢz GIAY BAO NOuGIẤY BÁO CÓz GIAY BAO COuSAO KÊ TÀI KHOẢNzSAO KE TAI KHOANzACCOUNT STATEMENTzTRANSACTION HISTORYuBẢNG KÊ GIAO DỊCHzBANG KE GIAO DICHuSỔ PHỤ TÀI KHOẢNzSO PHU TAI KHOANuTHÔNG BÁO GIAO DỊCHuTÀI KHOẢN TRÍCH NỢuĐƠN VỊ THỤ HƯỞNGuNGÂN HÀNG THỤ HƯỞNGuSỐ DƯ SAU GIAO DỊCHzRUNNING BALANCEz VALUE DATEc3 K|]}|vV dSr)rHrIrJ combined_texts r,rLz=UncClassifierAndRouter.detect_unc_and_bank..es(NNAQ-/NNNNNNr..r/pdfrN)streamfiletyper2 c3 K|]}|vV dSr)rHrNs r,rLz=UncClassifierAndRouter.detect_unc_and_bank..xs(,^,^AQ--?,^,^,^,^,^,^r.c:g|]}|pdS)r/) extract_text)rIpages r, z>UncClassifierAndRouter.detect_unc_and_bank..|s)*b*b*b4+<+<+>+>+D"*b*b*br.c3 K|]}|vV dSr)rHrNs r,rLz=UncClassifierAndRouter.detect_unc_and_bank..s(0b0bm1C0b0b0b0b0b0br.c3 K|]}|vV dSr)rHrNs r,rLz=UncClassifierAndRouter.detect_unc_and_bank..s@ccaqM!ccccccr.)ruVIỆT NAM THỊNH VƯỢNGzVIET NAM THINH VUONGr ACCOUNTSTMTPDLDLD25LD26rc3 K|]}|vV dSr)rHrNs r,rLz=UncClassifierAndRouter.detect_unc_and_bank..s@JJm#JJJJJJr.)ru KỸ THƯƠNGz KY THUONGTCB07898888XXXXXXXXXX8888TT261rc3 K|]}|vV dSr)rHrNs r,rLz=UncClassifierAndRouter.detect_unc_and_bank..s(bbm#bbbbbbr.)ruÁ CHÂUzA CHAUASCBrrc3 K|]}|vV dSr)rHrNs r,rLz=UncClassifierAndRouter.detect_unc_and_bank..s(hhm#hhhhhhr.)ruNGOẠI THƯƠNGz NGOAI THUONGVCBrc3 K|]}|vV dSr)rHrNs r,rLz=UncClassifierAndRouter.detect_unc_and_bank..s(ddm#ddddddr.)ruCÔNG THƯƠNGz CONG THUONGCTGrc3 K|]}|vV dSr)rHrNs r,rLz=UncClassifierAndRouter.detect_unc_and_bank..s(VVm#VVVVVVr.)ruĐẦU TƯ VÀ PHÁT TRIỂNrrc3 K|]}|vV dSr)rH)rIextrKs r,rLz=UncClassifierAndRouter.detect_unc_and_bank..sINDNDbecU]oNDNDNDNDNDNDr.).xls.xlsx.pdfFi)rRpngjpgjpegwebptifftifMultimodalOcrServiceamount bank_codeTz#OCR classification check failed: %sgffffff?g333333?g?)is_uncr|filename_matchedcontent_matched ocr_matchedocr_data confidence)lowerupperanysplitpymupdfopenget_textstripappendclosejoin Exceptionpypdf PdfReaderioBytesIOpagesr6len7app.modules.core.application.ocr.multimodal_ocr_servicerzextract_banking_unc_datagetloggerdebug)r0r2r3r5unc_filename_keywordsis_filename_uncunc_content_keywordsis_content_uncrordocextracted_pagesrZp_txtreaderr|r}r ocr_detailsis_large_non_bankrzocr_svcocr_resocr_edetailsrOrKs @@r,detect_unc_and_bankz*UncClassifierAndRouter.detect_unc_and_bankDs>>###,,d,,2244 ! ! !  KKKK5JKKKKK   NNNN9MNNNNN   -0H__(..%%b))"Ce||"NNN!,,g,FFC&(O #BQB:: $ f 5 5 ; ; = = :+225999IIKKK&_#yy99+3(<(>wRUVVG=GKK$9$9=W[[=U=U=";;x00*W[[5J5JQ5N5N%)F&* &- ";;{33= 8LPW8W8W(/ (>Q file_contentbytesr| str | Nonetuple[str, str]cJ|pd}t|d}tjt |}tj|dtj||}tj|rt|d5}tj |  }dddn #1swxYwYtj | } || krtd|||fStj|\} } t#jd} | d | | } tj|| }t|d 5}||dddn #1swxYwYtd |||fS) zuSaves file into the appropriate Hot Watcher folder. Returns: (target_folder_path, target_file_path) rrT)exist_okrbNz>* + + F&-- E 'qvvxx 8 8 B B D D  E E E E E E E E E E E E E E E#N<88BBDDM -- Z\lmmm!#333((22ID# //@@I"55Y555L!w||J EE  "D ) ) "Q GGL ! ! ! " " " " " " " " " " " " " " "  79IJJJ+++s$=9DD DG;;G?G? file_pathdict[str, Any]c|smtj|std|t |d5}|}dddn #1swxYwYtj|}|}|s|||\}}}|pd}| }g} |dksd|vr| drd } tD]Q} t| } | tj||} | rd } nB#t $rYNwxYw| sp| sn td } | tj||} n #t $r&} t"d | Yd} ~ nd} ~ wwxYwn| d r|||} n|dksd|vsd|vr| d r t)} | tj||} nM#t $r<} t"d| |||} Yd} ~ n d} ~ wwxYwn|dksd|vsd|vr| dr t+} | tj||} n#t $r;} t"d| |||} Yd} ~ ngd} ~ wwxYwn^ t/} | tj||} n&#t $r|||} YnwxYw| s ddlm}|}tj|\}}||||d}|r_|drIt=t?|d}|dd}|dkr|nt=d}|dkr|nt=d}|dp%tAj!"d }tAj#|d $}|d!r|d!dkr|d!}|d"p tJ|d#d}|d$p tJ|d%d&}| &||||||d'p(d(tAj!"d)||t=d|d*pd+||d,|d-|d.d/ t"'d0|d'|n2#t $r%}t"d1|Yd}~nd}~wwxYwd}g}tJ|d2\}}tQ|j)d3dptQ|j)d4d}|5} | *5}!|!+d5|!,}"|"s)|!+d6|!,}"|"r|"d7n t[}#| D]$}$|$d8}%t]|%t@r|%$}%|%sDt=t?|$d9pd}t=t?|$d:pd}|dkrd}|}&n |dkrd}|}&n|$d*pd;/}'|$d<pD|$d=p/d>t[j0dd? }(|$d@1t=t?|$d@pdnd})|$dAp|}*|$dBp|}+|1|'|},|!+dC|#||*|+|%|(||&|)|'|,|f |!,}-|-r |d&z }|&|-d7&| 2dddn #1swxYwYdddn #1swxYwYd}.|r ddDl3m4}/|/}0|D]+}1|05|1}2|2r|.tm|2z }.,|.dkrt"'dE|.|n2#t $r%}3t"dF|3Yd}3~3nd}3~3wwxYwt"'dG||tm| ||.dH||tm| ||tm| |z |.|dI S)JzQParses a bank statement file and inserts transactions into erp_bank_transactions.zFile not found: rN)r3rr techcombankrrF)passwordTz*TCB PDF parser failed without password: %s)rqrpracbrz%ACB parser failed, trying generic: %srvpbankr@)rprqz,VPBank parser failed, trying xlrd direct: %srryrPr{ directionoutflow0inflowtxn_datez%Y-%m-%dr|sender_account)r!r/ sender_name)r/rref_nozOCR-z %Y%m%d%H%M%S descriptionuThanh toán UNC receiver_account receiver_name receiver_bank) r|account_number account_nametransaction_date value_datereference_numberdebit_amount_vndcredit_amount_vndrunning_balance_vndrcounterparty_accountcounterparty_namecounterparty_bankzAMultimodal OCR successfully parsed UNC transaction: %s, amount=%sz)Multimodal OCR fallback parsing error: %sr get_connection_open_connectionz>SELECT id FROM companies WHERE tax_code = '0202111150' LIMIT 1z SELECT id FROM companies LIMIT 1idrrrr/transaction_referencerzTXN-rrra7 INSERT INTO erp_bank_transactions ( company_id, bank_name, account_number, account_name, transaction_date, reference_number, direction, amount, running_balance, description, transaction_category, source_file, created_at ) VALUES ( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, NOW() ) ON CONFLICT (bank_name, account_number, reference_number, transaction_date, amount, direction) DO NOTHING RETURNING id )SmartClearingServicez3Auto-cleared %d debt settlements for statement '%s'z/Smart Clearing trigger encountered an error: %szgIngested statement file '%s' (Bank: %s): parsed %d txs, newly saved %d txs, auto-cleared %d settlementssuccess) status file_namer| total_parsed newly_savedinsertedskippedcleared_settlementsr)7rrrFileNotFoundErrorrrbasenamerrrendswithKNOWN_PASSWORDSr parserrrrwarning_parse_generic_or_tcb_excelr r _parse_vpbank_directr rrzrrlstriprrr1rrrstrptimedateDEFAULT_ACCOUNTSrrgetattrr*cursorexecutefetchoner isinstancerhex_categorize_transactioncommit8app.modules.financial.application.smart_clearing_servicerreconcile_single_transactionr)4r+rr|rrr0rKr detected_bank parsed_txsrpwdparsererz ocr_servicerounc_dataamtrdebitcredit txn_date_strracc_noacc_nameocr_err saved_count saved_idsdefault_acc_nodefault_acc_nameget_connconncurrow company_idtxt_dater{descrefbalance tx_acc_no tx_acc_namecategoryres cleared_countr clearing_svctx_idsettledcl_errs4 r,ingest_statement_filez,UncClassifierAndRouter.ingest_statement_files  (7>>),, H'(F9(F(FGGGi&& (! vvxx  ( ( ( ( ( ( ( ( ( ( ( ( ( ( (7##I..>>## 1"&":":8\":"Z"Z A}a%0IOO%% +-   % %()B)B  (( V*!!C!!-s!;!;!;%+\\"*\2J2JH%U%U %"&*G!E"%!!! !XzXX!-t!W>W  !233 SS.00F!'bj.F.F!Q!QJJ SSSNN#QSTUUU!%!:!:<!R!RJJJJJJS  S V'))#\\"*\*B*BHMM  V V V!==lHUU  V9 U8 U3244 ))(333&?? (CJJsOO, X 6 6,!#hx&8"9"9::C ( [) D DI#, #9#9CCws||E$-$9$9SSws||F#+<< #;#;$x|~~?V?V"@@L (0zJJOOQQH ||K00:Xk5Jg5U5U$,[$9 %\\*:;;?O?S?S!?@@@F (||M:: >N>R>R!#Q???H%%)2.4,408*208 X0F0F1POhlnn&=&=n&M&MOO05173:3<<+3<< +F+F,=<(<<4>@@D677<66"455<;%''+bqb/"7"7"9"9;; SURXRXYnRoRoR{gc"&&1F*G*G*L1&M&MNNNBFG "'7 8 8 JNI"$&&"8"8"LY"" Z,Z  Z,g J)f6* g 6f: :g =f: >g  ggA h>> i-i((i-list[dict[str, Any]]c g} tjtj|d}|jD]!}||}|dD]}|rt |dkrt|d}tj d|sV tj |ddd } t |d kr|d rt|d nd} t |d kr|d rt|d nd } t |d kr>|d r6tt|d dd nd} t |dkr>|dr6tt|ddd nd} t |dkr>|dr6tt|ddd nd}|| | | | | |d#t $rYwxYw#n2#t $r%}t"d|Yd}~nd}~wwxYw|S)z-Parses generic or Techcombank XLSX statement.T) data_only) values_onlyrz\d{2}/\d{2}/\d{4}N %d/%m/%Yrr/rU,rrrrrrrzGeneric Excel parser error: %s)openpyxl load_workbookrr sheetnames iter_rowsrr1rrematchrrfloatreplacerrrr)r+r3r0 transactionsrsheetwsr$r0_strtx_dater)r(rrr*rs r,rz2UncClassifierAndRouter._parse_generic_or_tcb_excels  @' 7(;(;tLLLB ! !Y<rr9r?zVPBank direct parse failed: %s)pd read_excelrriterrowsr1ilocrisnarrrFrGrrrrr)r+r3r0rHdfis_datarr$sttdate_strrLr)rrr(r*rs r,rz+UncClassifierAndRouter._parse_vpbank_directs  @rz'2233BG++--  3#(1+&&,,..#::"G738A;''+?3sxPQ{CSCS+S+SEsx{++1133&/" zJJGchqk**CMOWUXU]^_U`MaMajU3sx{#3#3#;#;C#D#DEEEgjFLNGTWT\]^T_L`L`iE#chqk"2"2":":3"C"CDDDfiE35738A;3G3GO3sx{+++RDJMc((UV,,_a_fgjgopqgr_s_s,eC $4$4$<$./s'BBqG|BBBBBBr.)LUONGuLƯƠNGBHXHSALARYc3 K|]}|vV dSr)rHr[s r,rLzAUncClassifierAndRouter._categorize_transaction..1s'CCqG|CCCCCCr.)THUEuTHUẾzNTDT+KBTAXc3 K|]}|vV dSr)rHr[s r,rLzAUncClassifierAndRouter._categorize_transaction..3s'yyqG|yyyyyyr.)zNO GOCu NỢ GỐCzLAI VAYuLÃI VAYzTHANH TOAN LAIzLAI-LDr_LOAN_REPAYMENTc3 K|]}|vV dSr)rHr[s r,rLzAUncClassifierAndRouter._categorize_transaction..5s'KKqG|KKKKKKr.)zRUT TIENu RÚT TIỀNzQUY TMCASH_WITHDRAWALc3 K|]}|vV dSr)rHr[s r,rLzAUncClassifierAndRouter._categorize_transaction..7s'PPqG|PPPPPPr.)zCHUYEN SANG TKzNOI BOu NỘI BỘINTERNAL_TRANSFERrREVENUE_COLLECTIONSUPPLIER_PAYMENT)rr)r(rr\s @r,r z.UncClassifierAndRouter._categorize_transaction,s**,, BBBB%ABBB B B 8 CCCC%BCCC C C 5 yyyy%xyyy y y $## KKKK%JKKK K K %$$ PPPP%OPPP P P '&&  ''!!r.r))r$r%r&r')r/NT) r0r1r2r1r3r4r5r6r&r7)rrr0r1r|rr&r)NN)rr1r|rrr4r&r)r3rr0r1r&r5)r(r1rr1r&r1) __name__ __module__ __qualname____doc__r- staticmethodrrr4rrr rHr.r,r#r#>sdd????? $"& t*t*t*t*\t*l!%#,#,#,#,\#,P!%%) A A A A A F!!!!F####J"""\"""r.r#).rn __future__rrrloggingrrDshutilrdecimalrtypingruuidrrr@pandasrPr&app.core.postgres.base_pkg.base_clientr 8app.modules.banking.application.parsers.acb_excel_parserr 6app.modules.banking.application.parsers.tcb_pdf_parserr 8app.modules.banking.application.parsers.vcb_excel_parserr ;app.modules.banking.application.parsers.vpbank_excel_parserr getLoggerrpathlibr__file__resolveparents _UNC_BASE_DIRgetenvr1rrrrr#rHr.r,rs#"""""   EEEEEESSSSSSOOOOOOSSSSSSYYYYYY  E F FX&&((03 29C !J.// |, %%#   EOFH B }"}"}"}"}"}"}"}"}"}"r.