1jdZddlmZddlZddlZddlmZddlmZddl m Z ej dZ Gdd Z dS) u2AEC 3-Way Matching Service (Hóa Đơn Mua Vào <-> Dự Án WBS <-> Đơn Giá Sở Xây Dựng Hải Phòng). COMPLIANT WITH: Luật Xây dựng 2025, Quy chuẩn 4 Trụ Cột Định Sơn (MST 0202111150) & SOP-DSCONS-AEC-BOQ-ALLOC-2026. Strict Zero-Tolerance Synthetic Data & Decimal(18,4) COMPLIANT. ) annotationsN)Decimal)Any)ErpDatabaseClientzdscons.aec_3way_matchingceZdZdZd-d.dZd/d Z d0d1dZd2dZd3dZd4dZ d5d6d#Z d4d$Z d7d8d(Z d4d)Z d9d:d,ZdS);Aec3WayMatchingServiceuXDịch vụ đối soát 3 chiều AEC cho hóa đơn đầu vào Công ty Định Sơn.N erp_clientErpDatabaseClient | NonereturnNonec0|p t|_dSN)rdb)selfr s PC:\Projects\DSCons\app\modules\invoices\application\aec_3way_matching_service.py__init__zAec3WayMatchingService.__init__s3 1 3 3dict[str, Any]c|j5}|5}|d|pi}|d|pi}t |dpd}t |dpd}|dkrt||z dzdnd}|t|d pd|t|d pdt |d pdt|d pd|t |d pdt |dpdt |dpdt|dpdddcdddcdddS#1swxYwYddddS#1swxYwYdS)u>Tổng hợp chỉ số KPI 3-Way Matching toàn hệ thống.a SELECT count(*) as total_invoices, coalesce(sum(total_amount_vnd), 0) as total_amount, count(*) FILTER (WHERE matched_project_id IS NOT NULL) as matched_invoices, coalesce(sum(total_amount_vnd) FILTER (WHERE matched_project_id IS NOT NULL), 0) as matched_amount, count(*) FILTER (WHERE matched_project_id IS NULL) as unmatched_invoices, coalesce(sum(total_amount_vnd) FILTER (WHERE matched_project_id IS NULL), 0) as unmatched_amount FROM erp_invoices WHERE direction = 'input'; a WITH sample_items AS ( SELECT it.id, it.item_name, it.unit_price_vnd, it.quantity FROM erp_invoice_items it JOIN erp_invoices i ON it.invoice_id = i.id WHERE i.direction = 'input' AND it.unit_price_vnd > 0 LIMIT 400 ), benchmark_items AS ( SELECT it.id, it.unit_price_vnd, it.quantity, sp.state_unit_price_vnd FROM sample_items it JOIN LATERAL ( SELECT state_unit_price_vnd FROM erp_state_published_prices sp WHERE it.item_name ILIKE ('%%' || split_part(sp.material_name, ' ', 1) || '%%') LIMIT 1 ) sp ON TRUE WHERE sp.state_unit_price_vnd > 0 ) SELECT count(*) as audited_count, count(*) FILTER (WHERE unit_price_vnd <= state_unit_price_vnd) as safe_count, count(*) FILTER (WHERE unit_price_vnd > state_unit_price_vnd) as alert_count, coalesce(sum(GREATEST(0, (unit_price_vnd - state_unit_price_vnd) * quantity)), 0) as total_risk_vnd FROM benchmark_items; total_invoicesrmatched_invoicesd total_amountmatched_amountunmatched_invoicesunmatched_amount audited_count safe_count alert_counttotal_risk_vnd)audited_items_countsafe_items_countalert_items_counttotal_risk_amount_vnd)rtotal_amount_vndrmatched_amount_vndrunmatched_amount_vndmatched_rate_pctprice_benchmarkN) rget_connectioncursorexecutefetchoneintgetroundfloat)rconncurinv_stat bench_stat total_inv matched_inv matched_rates rget_matching_summaryz+Aec3WayMatchingService.get_matching_summarys W # # % %B t{{}}B  KK    ||~~+H KK   <-2JHLL)9::?a@@Ihll+=>>C!DDKHQTU 5+ "9C"?!DDD[^L#,$)(,,~*F*F*K!$L$L$/&+HLL9I,J,J,Oa&P&P&)(,,7K*L*L*QPQ&R&R(-hll;M.N.N.SRS(T(T$0+.z~~o/N/N/SRS+T+T(+JNN<,H,H,MA(N(N),Z^^M-J-J-Oa)P)P-2:>>BR3S3S3XWX-Y-Y $$iB B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B B s5H<GH$ H<$H( (H<+H( ,H<<IIALL2rstatusstr project_id str | Nonesearchlimitr0offsetct|j5}|5}dg}g} |dkr|dn-|dkr|d|r*|d| ||rE|dd|d} | | | | gd |} d | d } || | | d } d | d}||| ||gz| }g}|D]n}t|}|ds| ||}||d<nd|d<| |d||d<||o| |||dcdddcdddS#1swxYwYddddS#1swxYwYdS)uBDanh sách hóa đơn mua vào phục vụ đối soát 3 chiều.zi.direction = 'input'MATCHEDz i.matched_project_id IS NOT NULL UNMATCHEDzi.matched_project_id IS NULLzi.matched_project_id::text = %szI(i.invoice_number ILIKE %s OR i.seller_name ILIKE %s OR i.notes ILIKE %s)%z AND z3SELECT count(*) as total FROM erp_invoices i WHERE ;totala^ SELECT i.id, i.invoice_number, i.invoice_series, i.issue_date, i.seller_name, i.seller_tax_code, i.total_amount_vnd, i.subtotal_amount_vnd, i.vat_amount_vnd, i.reconciliation_status, i.matched_project_id, i.matched_wbs_id, i.notes, p.project_code as matched_project_code, p.project_name as matched_project_name, ( SELECT count(*) FROM erp_invoice_items it WHERE it.invoice_id = i.id ) as item_count, ( SELECT string_agg(it.item_name, '; ') FROM ( SELECT item_name FROM erp_invoice_items WHERE invoice_id = i.id LIMIT 3 ) it ) as sample_items FROM erp_invoices i LEFT JOIN projects p ON i.matched_project_id = p.id WHERE zu ORDER BY i.issue_date DESC, i.total_amount_vnd DESC LIMIT %s OFFSET %s; matched_project_id suggestionNid sxd_benchmark)rJrCrDinvoices)rr,r-upperappendstripextendjoinr.r/fetchalldict_suggest_project_for_invoice_quick_check_sxd_benchmark)rr>r@rBrCrDr4r5 where_clausesparamss_param where_str count_queryrJqueryrowsresultsrinv_dictrLs rlist_matching_invoicesz-Aec3WayMatchingService.list_matching_invoices^s>W # # % %G t{{}}G 45M "F||~~**$$%GHHHH;..$$%CDDD *$$%FGGG j))) ;$$%pqqq/fllnn/// w9::: ]33I\PY\\\K KK V , , ,LLNN7+E.!/E6 KKv7 8 8 8<<>>DG ) )77 452!%!B!B8S!Q!QJ-7H\**-1H\*,0,K,KHUYN\_,`,`)x(((( # EG G G G G G G G G G G G G G G G G G G G G G G G G G G G G G G G G G G G G G G G G s5H-G H< H-H H-H H--H14H1 invoice_idr5rc|d|f|}|sddddStd|D}|rqt|d}t t |d t |d z d z d zd }d d|d|dkrdnd|d|ddSddddS)uLĐánh giá nhanh trạng thái so với giá Sở Xây dựng Hải Phòng.ab SELECT it.item_name, it.unit_price_vnd, sp.state_unit_price_vnd, sp.material_name as sxd_name FROM erp_invoice_items it JOIN LATERAL ( SELECT state_unit_price_vnd, material_name FROM erp_state_published_prices sp WHERE it.item_name ILIKE ('%%' || split_part(sp.material_name, ' ', 1) || '%%') ORDER BY sp.effective_date DESC NULLS LAST LIMIT 1 ) sp ON TRUE WHERE it.invoice_id = %s AND it.unit_price_vnd > 0 AND sp.state_unit_price_vnd > 0 LIMIT 5; UNASSESSEDuChưa có định mức SXDgray)r>labelcolorc3tK|]3}t|dt|ddzkV4dS)unit_price_vndstate_unit_price_vndg?Nr3.0its r zDAec3WayMatchingService._quick_check_sxd_benchmark..sJooceb!1233eB?UzCAec3WayMatchingService._quick_check_sxd_benchmark..s(a8H6I0J0JUSTUkSlMmMm0mr)keyrkrlrrALERTuVượt SXD (+z%)amberrose item_namesxd_name)r>rhrir{r|SAFEuĐạt chuẩn Sở XDgreen)r.rUanymaxr2r3)rrdr5items has_alertmax_itempct_overs rrXz1Aec3WayMatchingService._quick_check_sxd_benchmarks  ]     e*5R]cdd dooinooooo  5&m&mnnnHeH-=$>??%QgHhBiBiilmmqttvwxxH!6(666$,rMMv%k2$Z0  !+CgVVVrinvdict[str, Any] | Nonec 0 t|dpd}t|dpd}t|dpd}t|dpd}|d|d| d|vsd vrd d d d d dSd vs d vsd vsd vrd ddd ddSd vs d vsd vsd vrd ddd ddSd vsd|vrdd d!d d"dSd# vsd$ vrdd%d&d d'dSgd(}|D]]\}} } | vrS|d)| f|} | rt| d*nd+} d,| | | d d-|d.d/cS^t fd0d1DrS|d2|} | r(d,t| d*| d3| d4d5d6d/Sd+S)7uWGợi ý dự án & WBS thông minh dựa trên 4 Trụ Cột và Phân rã Từ Khóa.seller_tax_code seller_namenotes sample_items 0200149705thoát nướcz TC2-LOGISTICSuJHợp Đồng Vận Chuyển Bùn Đất - Cty Thoát Nước Hải PhònghighuFNhà thầu phụ/Khách hàng Trụ Cột 2 (Vận tải & Logistics))pillar project_code project_name confidencereason petrolimexutân thế huynhdầu dodầu điêzenzFLEET-FUEL-POOLu>Nhiên Liệu Dầu DO Đoàn Xe Ben Howo & Ca Máy Cơ Giớiu@Hóa đơn cấp dầu DO vận hành Đoàn Xe Ben & Máy Đàouô tô tải tự đổhowocnhtcutập đoàn thiên khz FLEET-ASSETu*Tài Sản Cơ Giới - Đoàn Xe Ben Howou>Hóa đơn đầu tư phương tiện cơ giới Đoàn Xe Benu loan khải 0201889988z TC3-MACHINERYu@Hợp Đồng Cho Thuê Máy Đào Bánh Xích - Cty Loan Khảiu4Khách hàng Trụ Cột 3 (Cho thuê ca máy đào)umáy đào bánh xíchumáy đào bánh lốpz TC3-EQUIPMENTu9Đội Máy Đào Bánh Xích & Máy Thi Công Cơ Giớiu3Thiết bị máy thi công đào đắp cơ giới) )đại thắng DA-2608281122#Nhà văn hóa thôn Đại Thắng)uthôn đại thắngrr)u mạnh hùngruJNhà văn hóa thôn Đại Thắng (Vật tư xà gồ thép & mái tôn))vinhomesz DA-2026-46u,KĐT Mới Vinhomes Golden City Dương Kinh) bến kem DA-2026-02,Cống Bến Kem & Chiếu sáng Kiến Minh)u kiến minhrr) đa độz DA-2026-01u+Kè Sông Đa Độ - Huyện Kiến Thụy)u cổ tiểuz DA-2026-32u&Cống Cổ Tiểu 2, 3 xã Đoàn Xá) tân tràoz DA-2026-03uTrường Mầm Non Tân Trào)u ông đènz DA-2026-05uKênh Ông Đèn)u rạng đôngz DA-2026-10u'Hạ Tầng Chiếu Sáng Rạng Đông8SELECT id FROM projects WHERE project_code = %s LIMIT 1;rMNru Khớp từ khóa công trình 'u' trên hóa đơn)rr@rrrrc3 K|]}|vV dSrrow full_texts rrqzFAec3WayMatchingService._suggest_project_for_invoice.."s']]!qI~]]]]]]r)thépusắtxi măng bê tônggạchzaSELECT id, project_code, project_name FROM projects WHERE project_code = 'DA-2608281122' LIMIT 1;rrmediumuPVật tư xây lắp chính (Ưu tiên dự án đang thi công trọng điểm))r?r1rRlowerr.r/r)rrr5 seller_taxrr items_textproject_keywordskwp_codep_namep_rowp_idrs @rrWz3Aec3WayMatchingService._suggest_project_for_invoicesW!2339r::@@BB #''-006B77==?? CGGG$$*++1133006B77==?? "99U99Z99  : % %)9Y)F)F / l$b   9 $ $(:i(G(G:YbKbKbfv{DgDgD 1 `$\   % 1 1Vy5H5HGW`L`L`d|AJeJeJ - L$Z   I % %)C)C / b$P   $y 0 04LPY4Y4Y / [$O      #3   BY VY_Xabbb +0:s5;'''d"&$*$*"(WWWW   ]]]]'\]]] ] ]  KK{ | | |LLNNE "%eDk"2"2$).$9$).$9"*p trc ~|j5}|5}|d|f|}|st d|t |}|d|f|}g}td}|D]_} t | } tt| dpd} tt| dpd} |d| d | d f|} | rtt| d pd}| |z }|dkrt||z d zd nd }|dkr||| zz }d| d| dt|t|t|| dp| d|dkrdndd| d<n dddd| d<| | ad}|dsBd d|dd D}||d!<|||}|||t||dkd"cdddcdddS#1swxYwYddddS#1swxYwYdS)#ucChi tiết đối soát 3 chiều cho 1 hóa đơn: Hóa đơn vs WBS vs Giá Sở XD Hải Phòng.z SELECT i.*, p.project_code, p.project_name FROM erp_invoices i LEFT JOIN projects p ON i.matched_project_id = p.id WHERE i.id = %s; "Không tìm thấy hóa đơn ID: a SELECT it.id, it.item_order, it.item_name, it.unit, it.quantity, it.unit_price_vnd, it.total_item_amount_vnd, it.vat_rate_percent, it.cost_category, it.matched_wbs_code FROM erp_invoice_items it WHERE it.invoice_id = %s ORDER BY it.item_order, it.id; 0rkrquantitya SELECT material_code, material_name, specifications, unit, state_unit_price_vnd, document_reference, publish_period FROM erp_state_published_prices WHERE material_name ILIKE ('%%' || split_part(%s, ' ', 1) || '%%') OR specifications ILIKE ('%%' || split_part(%s, ' ', 1) || '%%') ORDER BY effective_date DESC NULLS LAST LIMIT 1; r{rlrrrT material_nameunitdocument_referencepublish_periodrwr})matchedrrrldiff_vnddiff_pct document_refr>rNFu<Không tìm thấy định mức công bố tương đươngrf)rmessager>NrK; c3&K|] }|dV dS)r{Nrrns rrqzBAec3WayMatchingService.get_invoice_audit_detail..{s&&T&T2r+&T&T&T&T&T&Trrr)invoicerLrtotal_price_risk_vnd is_compliant)rr,r-r.r/ ValueErrorrVrUrr?r2r3rQrTrW)rrdr4r5rrbrenriched_itemstotal_variance_vndrarpu_priceqty sxd_match sxd_pricerrrL sample_strs rget_invoice_audit_detailz/Aec3WayMatchingService.get_invoice_audit_detail1s W # # % %R t{{}}R  KK     ,,..C T !Rj!R!RSSSCyyH KK   LLNNEN!( ) *) *!WW!#b)9&:&?a"@"@AAc"Z."5A6677 or+7999 LLNN  'I6L,M,RQR(S(S T TI&2HIRUVuh&:S&@1EEE\_H!||*x#~>*$()2?)C )& 105i0@0@$)(OO$)(OO(12F(G(f9UeKf-5\\''v + +B''$)#a".++B' %%b))))J01 N!YY&T&TPRQRPRAS&T&T&TTT +5(!>>xMM $('(-.@(A(A!3q!8 YR 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 R R R R R R s5J2IJ J2J J2!J "J22J69J6Tdry_runbool max_itemsc|j5}|5}|d|f|}d}t d}ddddd}g} |D]} |t| |} | r| ddvr| d} | d} | sD| rB|d | f| }|rt|d } t t| d pd}|d z }||z }| d d }||dd z||<| t| d | d| dt||| | d| d| ot| d |s5| r3|d| | dd| d f|s||t||t||| dddcdddcdddS#1swxYwYddddS#1swxYwYdS)ugTự động phân bổ hàng loạt các hóa đơn mua vào chưa khớp vào Dự án / Trụ Cột.a SELECT i.id, i.invoice_number, i.seller_name, i.seller_tax_code, i.notes, i.total_amount_vnd, (SELECT string_agg(it.item_name, '; ') FROM erp_invoice_items it WHERE it.invoice_id = i.id) as sample_items FROM erp_invoices i WHERE i.direction = 'input' AND i.matched_project_id IS NULL ORDER BY i.total_amount_vnd DESC LIMIT %s; rr)rrrr)rrr@rrrMr'rrinvoice_numberrrr) rdrr amount_vndsuggested_pillarsuggested_project_codesuggested_project_namerapplieda UPDATE erp_invoices SET matched_project_id = %s, reconciliation_status = '3WAY_MATCHED', notes = COALESCE(notes, '') || ' [AI 3-Way Matched: ' || %s || ']', updated_at = CURRENT_TIMESTAMP WHERE id = %s; zAI AutoNr=)rtotal_unmatched_scanned matched_countr(pillar_breakdownmatched_records)rr,r-r.rUrrWrVr1r/r?rQr3rcommitlen)rrrr4r5unmatched_rowsrr pillar_countsactionsrasugrrfound inv_amountrs rbatch_auto_matchz'Aec3WayMatchingService.batch_auto_matchs W # # % %C t{{}}C  KK   !\\^^NM$S\\N !aA!44MG#& L& L77QEE$L377<004FFF77<00D WW^44F4F4 $^ag`ijjj #  4#&uT{#3#3D!(Q/A-B-Ga)H)H!I!IJ!Q&M"j0N WWXq11F,9,=,=fa,H,H1,LM&)NN&)!D'll*+,<*='('7&+J&7&7,22825''.2I2I"%''("3"3'.;#=4:: $ $   #LtL %#CGGHi$@$@!D'JLLL  '*>':':*"'"7"7 -&ss|   yC C C C C C C C C C C C C C C C C C C C C C C C C C C C C C C C C C C C C C C C C s5J3IJ J3J J3"J #J33J7:J7c |j5}|5}|d|f|}|st d||d|f|}|dpd}|dpd}|dpd}dd |D} |d|d|d| tfd d D} tfd d D} tfddD} tfddD} |d|}g}|D]y}t|d}|dpd}|dpd}|dpd}|dpd}|d|d|d|d}g}|dr3t|d|kr|dz }| d| rxdvsdvr|dz }tfdd Dr|d!z }| d"n5tfd#d$Dr|d%z }| d&| rtd'vr|d%z }tfd(d)Dr|d*z }| d+n5tfd,d-Dr|dz }| d.| rTtfd/d0Dr|d1z }| d2nd3vr|d!z }| d4d5vrd5vr|d6z }| d7d8vrd8vr|d6z }| d9d:|vr|d;z }|dkrB| |||||td<||rd=|nd>d?{| d@dAB|dCdD}|sU|dCdED]J}| t|d|d|d|d|ddFdGd?K||dH|d| dCdI|dJcdCdCdCcdCdCdCS#1swxYwYdCdCdCdCS#1swxYwYdCS)KudTính toán và xếp hạng top dự án thông minh phù hợp nhất cho 1 hóa đơn cụ thể.a SELECT i.id, i.invoice_number, i.seller_name, i.seller_tax_code, i.seller_address, i.notes, i.total_amount_vnd, p.id as current_project_id, p.project_code as current_project_code, p.project_name as current_project_name FROM erp_invoices i LEFT JOIN projects p ON i.matched_project_id = p.id WHERE i.id = %s; rz SELECT item_name, unit, quantity, unit_price_vnd, total_item_amount_vnd FROM erp_invoice_items WHERE invoice_id = %s; rrseller_addressrrc3NK|] }|dpdV!dS)r{rN)rrns rrqzGAec3WayMatchingService.get_smart_project_suggestions..s7 Q QR"[/"7R!>!>!@!@ Q Q Q Q Q Qrc3 K|]}|vV dSrrrs rrqzGAec3WayMatchingService.get_smart_project_suggestions..s?$L$LqAN$L$L$L$L$L$Lr) u cọc treucọcucừuphênurơm đắp bờubờ kèu nạo vétubơmkênhumươngcống thủy lợic3 K|]}|vV dSrrrs rrqzGAec3WayMatchingService.get_smart_project_suggestions..s? ] ]1Y ] ] ] ] ] ]r) utônruxà gồusơnrungóiucửaruvữarc3 K|]}|vV dSrrrs rrqzGAec3WayMatchingService.get_smart_project_suggestions..s?$`$`qAN$`$`$`$`$`$`r)rrzxe benruvận chuyểnucướcucátu san lấpc3 K|]}|vV dSrrrs rrqzGAec3WayMatchingService.get_smart_project_suggestions..s'ss!qI~ssssssr)u máy đàou máy ủiuca máyyanmarkomatsuz SELECT id, project_code, project_name, project_type, location, status, contract_value FROM projects ORDER BY project_code DESC; rMrr project_typelocationrcurrent_project_idru#Dự án hiện đang được gánrukè đê#c3 K|]}|vV dSrrrorp_fulls rrqzGAec3WayMatchingService.get_smart_project_suggestions..s'oo11;oooooor)rusông đa độru ngăn mặn-uGCông trình Đắp bờ kè sông Đa Độ (dùng cọc tre gia cố)c3 K|]}|vV dSrrrs rrqzGAec3WayMatchingService.get_smart_project_suggestions..s'bbQQ&[bbbbbbr)rrru trạm bơmu=Hạng mục Thủy lợi / Cống kênh tiêu thoát nướcu dân dụngc3 K|]}|vV dSrrrs rrqzGAec3WayMatchingService.get_smart_project_suggestions..s'UU11;UUUUUUr)runhà văn hóa7uQCông trình Nhà văn hóa thôn Đại Thắng (Vật tư mái tôn & xà gồ)c3 K|]}|vV dSrrrs rrqzGAec3WayMatchingService.get_smart_project_suggestions..s'NNQQ&[NNNNNNr)ru trườngu6Công trình Xây lắp Trường Mầm Non Tân Tràoc3 K|]}|vV dSrrrs rrqzGAec3WayMatchingService.get_smart_project_suggestions..#s'MM11;MMMMMMr)ru dương kinhr=uFDịch vụ vận chuyển cát KĐT Vinhomes Golden City Dương Kinhru:Hợp đồng nạo vét vận chuyển Cty Thoát Nướcuan lãorxu#Đồng địa bàn Huyện An Lãou kiến thụyu)Đồng địa bàn Huyện Kiến Thụy2026 bru<Tương đồng danh mục vật tư & loại hình dự án)rMrrrrscorerc|dS)Nrrrss rruzFAec3WayMatchingService.get_smart_project_suggestions..Bs !G*rT)rvreverseNrr<u2Dự án trọng điểm đang thi công năm 2026rx)rdrr items_summary suggestions)rr,r-r.r/rrUrrTrr?rQminsort)rrdr4r5rrrrr items_stris_bamboo_or_dyke is_steel_roofis_logistics_fuel is_excavator all_projectsscored_suggestionsprrrp_typep_locrreasonstop_suggestionsrrs @@rget_smart_project_suggestionsz4Aec3WayMatchingService.get_smart_project_suggestionss W # # % %H t{{}}H  KK   ,,..C T !Rj!R!RSSS KK    LLNNE}-3::<>L!# !D D 1T7||>*0b>*0b>*0b* +">>V>>f>>u>>DDFF+,JS9M5N1O1OSW1W1WSLENN#HIII%h%//:3G3G oooo0noooooh 'pqqqqbbbb2abbbbbh 'fggg!a$.. UUUU0TUUUUUa 'z{{{{NNNN2MNNNNNa '_```%eMMMM0LMMMMMe 'opppp)V33 'cddd ))i6.A.ARKENN#HIII"i//Ov4M4MRKENN#NOOOV##RKE199&--"(.(.(.$)!$R8?#D$))G"4"4"4FD//  # #(<(rrr'N) rr,r-r.r/rjsondumpsrr3)rrdr@r%r&r4r5rs rconfirm_matchingz'Aec3WayMatchingService.confirm_matchingZsW # # % % t{{}}  KKfj1 3 3 3,,..C T !Rj!R!RSSS KK *GGV[\\\    KKMMM${L\H]{{{"%&6"7$)#.@*A*FQ$G$G 1                                         s5DB1C9 D9C= =DC= DDDc|j5}|5}|d|f|}|st d||d|fd|D}|d|fd|D}td|D}tt|dpd }||d |d t|t|tttd ||z t|d |Dd|Dd cdddcdddS#1swxYwYddddS#1swxYwYdS)uZLấy danh sách các dòng hàng và chi tiết phân bổ đa dự án của hóa đơn.z SELECT id, invoice_number, seller_name, total_amount_vnd, matched_project_id, reconciliation_status FROM erp_invoices WHERE id = %s; rz SELECT id, item_order, item_name, unit, quantity, unit_price_vnd, total_item_amount_vnd FROM erp_invoice_items WHERE invoice_id = %s ORDER BY item_order, id; c,g|]}t|SrrVroras r zBAec3WayMatchingService.get_invoice_allocations..s555T!WW555ra# SELECT a.id, a.invoice_item_id, a.project_id, p.project_code, p.project_name, it.item_name, a.allocated_quantity, a.allocated_total_amount_vnd, a.debit_account, a.credit_account, a.notes FROM erp_invoice_item_allocations a JOIN projects p ON a.project_id = p.id LEFT JOIN erp_invoice_items it ON a.invoice_item_id = it.id WHERE a.invoice_id = %s ORDER BY a.created_at; c,g|]}t|Srr0r1s rr2zBAec3WayMatchingService.get_invoice_allocations..s;;;q477;;;rc3^K|](}tt|dpdV)dS)allocated_total_amount_vndrN)rr?roas rrqzAAec3WayMatchingService.get_invoice_allocations..s?!j!jYZ'#a0L.M.RQR*S*S"T"T!j!j!j!j!j!jrr'rrrrc g|]p}t|d|d|d|dt|dpdt|dpdt|dpddqS) rM item_orderr{rrrrktotal_item_amount_vnd)rMr9r{rrrkr:r?r3rns rr2zBAec3WayMatchingService.get_invoice_allocations..s   ""T(mm&(&6%' _ "6 $)"Z.*=A$>$>*/3C0D0I*J*J16r:Q7R7WVW1X1X   rcng|]}t|d|drt|dnd|dpdt|d|d|dt|dpd t|d pd |d p d |d|d pd|dpdd S)rMinvoice_item_idNr{uToàn bộ hóa đơnr@rrallocated_quantityrr5 debit_account621_credit_account331rr) rMr=r{r@rrr>r5r?rArr;r6s rr2zBAec3WayMatchingService.get_invoice_allocations..s   "!D'llHIJ[H\+f3q1B/C+D+D+Dbf%&{^%N7N&)!L/&:&:().(9().(9.3A6J4K4Pq.Q.Q6;A>Z<[<`_`6a6a)*?);)Y?YaFW?Y?Y*+,<*=*F!"7!1r     r) rdrrr'total_allocated_vndremaining_unallocated_vndallocation_countr allocationsN) rr,r-r.r/rrUsumrr?r3rr) rrdr4r5rrrFtotal_allocatedr8s rget_invoice_allocationsz.Aec3WayMatchingService.get_invoice_allocationss W # # % %I t{{}}I  KK     ,,..C T !Rj!R!RSSS KK     65cllnn555E KK  <;CLLNN;;;K!!j!j^i!j!j!jjjOC(:$;$@q A ABBI)"%&6"7"=1$))$4$4',_'='=-23ws||YQ`E`3a3a-b-b$' $4$4  $     )   )$$KI I I I I I I I I I I I I I I I I I I I I I I I I I I I I I I I I I I I I I I I I s5GE$F, G,F0 0G3F0 4GG GrFlist[dict[str, Any]]c|std|j5}|5}|d|f|}|std|t t|dpd}|d|ft}t d} |D]} | d} | s| | |d | f|} | r| d nd } | d }|s|d |f|}|rt|d}n@|d|||ft|d}t t| dpd}t t| dpd}| dpd| }| dpd| }| dpd}|t dz}||z }|d||r|nd| |||||||f | |z } |rt|dnd}t|dkrdnd}|d|||f|d||tjt|t| t|d d!"f|d#d$t|d%t| d&d't|t| d(cdddcdddS#1swxYwYddddS#1swxYwYdS))u`Lưu bảng phân bổ đa dự án cho 1 hóa đơn (nhiều công trình/nhiều dòng hàng).u=Danh sách phân bổ dự án không được để trống.z SELECT id, invoice_number, total_amount_vnd FROM erp_invoices WHERE id = %s; rr'rz?DELETE FROM erp_invoice_item_allocations WHERE invoice_id = %s;rr@z0SELECT project_code FROM projects WHERE id = %s;rDAr=zWSELECT id FROM erp_invoice_items WHERE invoice_id = %s ORDER BY item_order, id LIMIT 1;rMu* INSERT INTO erp_invoice_items (invoice_id, item_order, item_name, quantity, unit_price_vnd, total_item_amount_vnd) VALUES (%s, 1, 'Hàng hóa / Dịch vụ chung', 1, %s, %s) RETURNING id; r>r5ruPhân bổ cho công trình r?r@rArBz0.08a INSERT INTO erp_invoice_item_allocations ( invoice_id, invoice_item_id, project_id, allocated_quantity, allocated_amount_before_vat_vnd, allocated_vat_amount_vnd, allocated_total_amount_vnd, allocation_method, debit_account, credit_account, confidence_score, notes ) VALUES ( %s, %s, %s, %s, %s, %s, %s, 'MANUAL_SPLIT', %s, %s, 100.0, %s ); Nr reconciledmulti_project_allocatedz UPDATE erp_invoices SET matched_project_id = %s, reconciliation_status = %s, updated_at = CURRENT_TIMESTAMP WHERE id = %s; z INSERT INTO erp_invoice_audit_logs (invoice_id, action_type, performed_by, details_json) VALUES (%s, 'MULTI_PROJECT_ALLOCATED', %s, %s); )projects_countrCallocations_countFr(r*u&Đã lưu thành công phân bổ cho u" công trình (Tổng phân bổ: z,.0fu VNĐ))r>rrOrC)rrr,r-r.r/rr?setr1addlistrr+r,r3r)rrdrFr&r4r5rtotal_inv_amountdistinct_projectsrHallocproj_idrritem_idfirst_it alloc_qty alloc_amount alloc_notes debit_acc credit_acc vat_amountsubtotal first_proj_id recon_statuss rsave_invoice_multi_allocationsz5Aec3WayMatchingService.save_invoice_multi_allocationss ^\]] ] W # # % %t t{{}}t  KK    ,,..C T !Rj!R!RSSS&s3/A+B+Ga'H'HII  KKY\f[h i i i!$ %cllO$= 0= 0))L11!%%g... NQXPZ[[[ 27A~..T))$566 <KK y}G|IJJJ"||~~H<"%htn"5"5 %)*: