;mj|/`ddlmZddlZddlmZejeZGddZdS)) annotationsN)AnycPeZdZ ddd Zddd Zdd ZddZ dddZddZdS)ErpFinancialMixinN2 project_id str | Nonelimitintreturnlist[dict[str, Any]]cVd}g}|r|dz }|||d|dz }|5}|5}||||}|r|cdddcdddSd}g} |r|dz }| ||d|dz }||| |cdddcdddS#1swxYwY ddddS#1swxYwYdS) uuLấy danh sách các giao dịch dòng tiền thu/chi thực tế từ sổ cái hoặc hóa đơn điện tử GDT.z SELECT f.*, p.project_name as project_name FROM erp_financial_transactions f LEFT JOIN projects p ON f.project_id = p.id WHERE 1=1 z AND f.project_id = %sz; ORDER BY f.transaction_date DESC, f.created_at DESC LIMIT ;Nug SELECT i.id::text as id, '0202111150' as company_id, i.matched_project_id as project_id, COALESCE(i.invoice_number, 'HD-' || SUBSTRING(i.id::text, 1, 8)) as transaction_code, CASE WHEN i.direction = 'input' THEN 'CHI_PHI_MUA_VAO' WHEN i.direction = 'output' THEN 'DOANH_THU_XUAT_RA' ELSE 'HOA_DON' END as transaction_type, CASE WHEN i.direction = 'input' THEN 'out' ELSE 'in' END as direction, i.total_amount_vnd as amount, COALESCE(i.issue_date, i.created_at::date) as transaction_date, CASE WHEN i.direction = 'input' THEN i.seller_name ELSE i.buyer_name END as beneficiary_or_payer, i.invoice_number, 'Chuyển khoản' as payment_method, NULL as dossier_reference_id, COALESCE(i.reconciliation_status, i.status, 'da_ghi_so') as accounting_status, 0.0 as ai_risk_score, COALESCE(i.notes, i.seller_name) as notes, NULL as created_by_employee_id, COALESCE(p.project_name, 'HĐ-2026 Công trình chung') as project_name FROM erp_invoices i LEFT JOIN projects p ON i.matched_project_id = p.id WHERE i.total_amount_vnd > 0 z AND i.matched_project_id = %szD ORDER BY transaction_date DESC NULLS LAST, i.created_at DESC LIMIT appendget_connectioncursorexecutefetchall) selfrr sql_manual params_manualconncurrows sql_invoices params_invs @C:\Projects\DSCons\app\core\postgres\clients\financial_client.pylist_financial_transactionsz-ErpFinancialMixin.list_financial_transactions s4 $&  - 2 2J   , , , R% R R R  " "* &d) &# J 666||~~  ) &) &) &) &) &) &) &* &* &* &* &* &* &* &* &  6)+ 2 $DDL%%j111 ogl o o oo  L*555||~~S) &) &) &) &) &) &) &* &* &* &* &* &* &* &* &) &) &) &) &) &) &) &) &) &* &* &* &* &* &* &* &* &* &* &* &* &* &* &* &* &* &* &sBD.D DAD, DD D D DD"%D" c.d|d}|5}|5}|||cdddcdddS#1swxYwYddddS#1swxYwYdS)u|Lấy danh sách các hoạt động vận hành và kiểm toán thực tế từ AI, Cổng Thuế GDT và Tự Trị DHS.uI SELECT * FROM ( SELECT 'ai_agent' as activity_source, COALESCE(agent_code, 'system') as actor_code, CASE WHEN agent_code = 'minh' THEN 'Minh (Pháp lý)' WHEN agent_code = 'thao' THEN 'Thảo (Dự toán)' WHEN agent_code = 'tung' THEN 'Tùng (Kiểm toán)' WHEN agent_code = 'quynh' THEN 'Quỳnh (Kế toán)' WHEN agent_code = 'nam' THEN 'Nam (Tài chính)' WHEN agent_code = 'hung' THEN 'Hùng (Hiện trường)' WHEN agent_code = 'lan' THEN 'Lan (Khách hàng)' WHEN agent_code = 'phuc' THEN 'Phúc (Tiến độ)' WHEN agent_code = 'thuy' THEN 'Thủy (Trợ lý)' ELSE 'Hệ thống AI' END as actor_name, 'Tra cứu & xử lý tri thức: ' || SUBSTRING(COALESCE(query_preview, 'Hoàn thành tác vụ RAG'), 1, 80) as description, created_at FROM erp_ai_agent_usage_logs UNION ALL SELECT 'invoice_audit' as activity_source, 'bot_tax' as actor_code, 'Cổng Thuế GDT' as actor_name, 'Đồng bộ hóa đơn điện tử GDT (' || COALESCE(action_type, 'SYNC') || ')' as description, created_at FROM erp_invoice_audit_logs UNION ALL SELECT 'autonomous_healing' as activity_source, COALESCE(agent_code, 'dhs') as actor_code, 'Hệ Thống Tự Trị DHS' as actor_name, 'Chuẩn hóa dữ liệu: ' || SUBSTRING(COALESCE(reason, 'Audit bản ghi'), 1, 80) as description, created_at FROM erp_autonomous_healing_logs ) combined_activities ORDER BY created_at DESC LIMIT z ; Nrrrr)rr sqlrrs rlist_system_activitiesz(ErpFinancialMixin.list_system_activitiesHs9' LM' ' ' P " " "dDKKMM "S KK   <<>> " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " "s4B )A2 B 2A6 6B 9A6 :B  BBdict[str, Any]c |5}|5}|d|}|r|dnd}|r|dnd}|dd|D}|d|}|r|dnd}|r|dnd} |d|} | r| dnd} | rt | d nd } ||||| | | d cd d d cd d d S#1swxYwY d d d d S#1swxYwYd S) uMTổng hợp chỉ số KPI vận hành thực tế cho Bảng Điều Hành.zaSELECT count(*) as total, count(*) FILTER (WHERE status = 'in_progress') as active FROM projects;totalractivez`SELECT project_name FROM projects WHERE status = 'in_progress' ORDER BY created_at DESC LIMIT 3;cg|] }|d S) project_name).0rs r z5ErpFinancialMixin.get_kpi_summary..sGGG1Q~.GGGzySELECT count(*) as total, count(*) FILTER (WHERE status = 'active' OR status = 'available') as active FROM erp_equipment;z]SELECT count(*) as total, COALESCE(sum(total_amount_vnd), 0) as sum_amount FROM erp_invoices; sum_amountg)total_projectsactive_projectsactive_project_namestotal_equipmentactive_equipmenttotal_invoicestotal_invoices_amount_vndN)rrrfetchonerfloat) rrrproj_rowr1r2 top_projs equip_rowr4r5inv_rowr6total_invoices_amounts rget_kpi_summaryz!ErpFinancialMixin.get_kpi_summaryvs\  " "' d& # w<<>>6>!E'!2!2A8@"G(8"4"4a vHG GGG  P LLNN 8A"H)G"4"4q:C#J9X#6#6  s,,..5> " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " "s4B>B* BB B B BB"Bd bank_nameaccount_numbercategoryc&d}g}|r|dz }|d|d|r|dz }|||r|dz }|||r|dz }|||d|dz }|5}|5} | ||| cd d d cd d d S#1swxYwYd d d d S#1swxYwYd S) uhTra cứu danh sách giao dịch sao kê từ 4 tài khoản ngân hàng thực tế của Định Sơn.a SELECT b.*, p.project_name, i.invoice_number as matched_invoice_number FROM erp_bank_transactions b LEFT JOIN projects p ON b.matched_project_id = p.id LEFT JOIN erp_invoices i ON b.matched_invoice_id = i.id WHERE 1=1 z AND b.bank_name ILIKE %s%z AND b.account_number = %sz AND b.matched_project_id = %sz AND b.transaction_category = %sz; ORDER BY b.transaction_date DESC, b.created_at DESC LIMIT rNr) rrErFrrGr r#paramsrrs rlist_bank_transactionsz(ErpFinancialMixin.list_bank_transactionss   , . .C MM*i*** + + +  * / /C MM. ) ) )  & 3 3C MM* % % %  $ 5 5C MM( # # # UUUUUU  " " "dDKKMM "S KKV $ $ $<<>> " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " " "s6D+*C. D.C2 2D5C2 6DD  D c~|5}|5}|d|}|d|}||dcdddcdddS#1swxYwYddddS#1swxYwYdS)uaBáo cáo tổng hợp số dư và đối soát dòng tiền sao kê ngân hàng toàn công ty.a( SELECT bank_name, account_number, count(*) as txn_count, sum(case when direction = 'inflow' then amount else 0 end) as total_in, sum(case when direction = 'outflow' then amount else 0 end) as total_out, min(transaction_date) as start_date, max(transaction_date) as end_date FROM erp_bank_transactions GROUP BY bank_name, account_number ORDER BY total_in DESC; a SELECT transaction_category, count(*) as txn_count, sum(case when direction = 'inflow' then amount else 0 end) as total_in, sum(case when direction = 'outflow' then amount else 0 end) as total_out FROM erp_bank_transactions GROUP BY transaction_category ORDER BY sum(amount) DESC; )banks categoriesNr")rrrrMrNs rget_bank_reconciliation_summaryz1ErpFinancialMixin.get_bank_reconciliation_summarysb  " " dDKKMM S KK    LLNNE KK   J(3                                         s5B2AB B2B B2!B "B22B69B6)Nr)rr r r r r )r )r r r r )r r%)r@r%r r%)NNNNrD) rEr rFr rr rGr r r r r ) __name__ __module__ __qualname__rr$r?rCrKrOr+r/rrr s:<<&<&<&<&<&|,",",",","\))))V"""",!%%)!%# """"""""""Hr/r) __future__rloggingtypingr getLoggerrPloggerrr+r/rrXs""""""  8 $ $nnnnnnnnnnr/