§
    dQ¶j¶$  ã                  ó˜   — d dl mZ d dlZd dlZd dlmZ d dlmZmZm	Z	m
Z
 d dlmZ d dlmZ  ej        d¦  «        Z G d„ d¦  «        ZdS )	é    )ÚannotationsN)ÚDecimal)ÚAnyÚDictÚListÚOptional)Údatetime)ÚProjectDataIntegrityPortÚDataIntegrityServicec                  ó0   — e Zd ZdZdZddd„Zdd	„Zdd„ZdS )r   uq   Dá»‹ch vá»¥ kiá»ƒm toÃ¡n tÃ­nh toÃ n váº¹n vÃ  phÃ¡t hiá»‡n dá»‹ thÆ°á»�ng dá»¯ liá»‡u dá»± Ã¡n ERP Ä�á»‹nh SÆ¡n.u?   C:/Projects/DSCons/CongTrinh/Báº£ng theo dÃµi cÃ´ng trÃ¬nh.xlsxNÚrepoúProjectDataIntegrityPort | Nonec                ó8   — |€ddl m}  |¦   «         }|| _        d S )Nr   )ÚErpDatabaseClient)Úapp.core.postgres.erp_clientr   Údb)Úselfr   r   s      úMC:\Projects\DSCons\app\modules\projects\application\data_integrity_service.pyÚ__init__zDataIntegrityService.__init__   s2   € Øˆ<ØFÐFÐFÐFÐFÐFØ$Ð$Ñ&Ô&ˆDØˆŒˆˆó    ÚreturnúDict[str, Any]c                óp  — g }| j                              ¦   «         5 }|                     ¦   «         5 }|                     d¦  «         |                     ¦   «         }|                     d¦  «         d„ |                     ¦   «         D ¦   «         }|                     d¦  «         d„ |                     ¦   «         D ¦   «         }ddd¦  «         n# 1 swxY w Y   ddd¦  «         n# 1 swxY w Y   |D �]ø}t          |d         ¦  «        }|d         }	|d	         }
|d
         �"t          t          |d
         ¦  «        ¦  «        nt          d¦  «        }||v r×||         }t          t          |d         pd¦  «        ¦  «        }|dk    rVt          ||z
  ¦  «        dk    r@|                     dd|	|
d|d›d|d›d�t          |¦  «        t          |¦  «        dœ¦  «         |d         dk    rC|d         |d         dz  k    r.|                     dd|	|
d|d         › d|d         › d�dœ¦  «         ||v r¸||         }|d         |d          k    r.|                     d!d"|	|
d#|d         › d$|d          › d%�dœ¦  «         t          t          |d&         pd¦  «        ¦  «        }|dk    rF||k    r@|                     d'd"|	|
d(|d›d)|d›d�t          |¦  «        t          |¦  «        dœ¦  «         �Œú|  
                    |¦  «        }|D ]}|                     |¦  «         Œt          |¦  «        }|t          d*„ |D ¦   «         ¦  «        z
  }|dk    rt          ||z  d+z  d,¦  «        nd-}t          |¦  «        dk    rd.nd/|||t          |¦  «        || j        t          j        ¦   «                              ¦   «         d0œS )1uZ  QuÃ©t vÃ  phÃ¢n tÃ­ch toÃ n diá»‡n 142 dá»± Ã¡n trong CSDL:
        - Kiá»ƒm tra Ä‘á»™ khá»›p giá»¯a GiÃ¡ trá»‹ HÄ� vÃ  CÃ¢y WBS
        - Kiá»ƒm tra tÃ­nh há»£p lá»‡ cá»§a Ä�á»£t thanh toÃ¡n (IPC)
        - Kiá»ƒm tra tÃ­nh duy nháº¥t vÃ  khÃ´ng bá»‹ duplicate
        - Ä�á»‘i soÃ¡t sai lá»‡ch vá»›i Báº£ng theo dÃµi cÃ´ng trÃ¬nh Excel
        zø
                    SELECT id, project_code, project_name, client_name, contract_number,
                           contract_value, budget_amount, status
                    FROM projects
                    ORDER BY project_code;
                aÅ  
                    SELECT project_id,
                           count(id) as total_wbs,
                           count(DISTINCT wbs_code) as distinct_codes,
                           sum(total_amount_vnd) FILTER (WHERE task_type = 'task') as task_sum,
                           sum(total_amount_vnd) FILTER (WHERE task_type = 'phase') as phase_sum
                    FROM erp_project_wbs
                    GROUP BY project_id;
                c                ó:   — i | ]}t          |d          ¦  «        |“ŒS ©Ú
project_id©Ústr©Ú.0Úrs     r   ú
<dictcomp>zEDataIntegrityService.audit_all_projects_integrity.<locals>.<dictcomp>7   ó&   € ÐMÐMÐM¸�S  <¤Ñ1Ô1°1ÐMÐMÐMr   aš  
                    SELECT project_id,
                           count(id) as total_ipcs,
                           count(DISTINCT ipc_number) as distinct_ipcs,
                           sum(net_certified_amount_vnd) as certified_sum,
                           sum(gross_claimed_amount_vnd) as claimed_sum
                    FROM erp_project_ipcs
                    GROUP BY project_id;
                c                ó:   — i | ]}t          |d          ¦  «        |“ŒS r   r   r   s     r   r"   zEDataIntegrityService.audit_all_projects_integrity.<locals>.<dictcomp>C   r#   r   NÚidÚproject_codeÚproject_nameÚcontract_valueÚ0Útask_sumr   i † ÚWBS_VALUE_MISMATCHÚhighu   Tá»•ng WBS (ú,.0fu%    Ä‘) lá»‡ch so vá»›i GiÃ¡ trá»‹ HÄ� (õ    Ä‘))ÚtypeÚseverityr&   r'   ÚmessageÚexpectedÚactualÚ	total_wbsÚdistinct_codesé   ÚWBS_DUPLICATE_SUSPECTÚmediumu   Cáº£nh bÃ¡o trÃ¹ng mÃ£ WBS (u    dÃ²ng nhÆ°ng chá»‰ cÃ³ u    mÃ£))r/   r0   r&   r'   r1   Ú
total_ipcsÚdistinct_ipcsÚIPC_DUPLICATE_NUMBERÚcriticaluC   PhÃ¡t hiá»‡n Ä‘á»£t thanh toÃ¡n IPC bá»‹ trÃ¹ng láº·p sá»‘ Ä‘á»£t (u    báº£n ghi nhÆ°ng chá»‰ cÃ³ u    Ä‘á»£t)Úcertified_sumÚIPC_OVERPAYMENTu!   Tá»•ng nghiá»‡m thu thanh toÃ¡n (u,    Ä‘) vÆ°á»£t quÃ¡ giÃ¡ trá»‹ Há»£p Ä‘á»“ng (c                ó&   — h | ]}d |v ¯|d          ’ŒS )r&   © )r    Úas     r   ú	<setcomp>zDDataIntegrityService.audit_all_projects_integrity.<locals>.<setcomp>�   s(   € Ð"_Ð"_Ð"_¸È>Ð]^ÐK^ÐK^ 1 ^Ô#4ÐK^ÐK^ÐK^r   éd   é   g      Y@ÚhealthyÚwarning)ÚstatusÚtotal_projectsÚhealthy_projectsÚintegrity_score_percentÚanomaly_countÚ	anomaliesÚ
excel_pathÚ
audited_at)r   Úget_connectionÚcursorÚexecuteÚfetchallr   r   ÚabsÚappendÚfloatÚ_compare_with_excelÚlenÚroundÚ
EXCEL_PATHr	   ÚnowÚ	isoformat)r   rL   ÚconnÚcurÚprojectsÚ	wbs_statsÚ	ipc_statsÚpÚpidÚpcodeÚpnameÚc_valÚw_statr*   Úi_statÚcert_sumÚexcel_driftÚdriftÚtotal_pÚ	healthy_pÚintegrity_scores                        r   Úaudit_all_projects_integrityz1DataIntegrityService.audit_all_projects_integrity   sq  € ð +-ˆ	àŒW×#Ò#Ñ%Ô%ð !	N¨Ø—’‘”ð  N #à—’ð ñ ô ð ð Ÿ<š<™>œ>�ð —’ð ñ ô ð ð NÐM¸c¿lºl¹n¼nÐMÑMÔM�	ð —’ð ñ ô ð ð NÐM¸c¿lºl¹n¼nÐMÑMÔM�	ðA Nð  Nð  Nñ  Nô  Nð  Nð  Nð  Nð  Nð  Nð  Nøøøð  Nð  Nð  Nð  Nð!	Nð !	Nð !	Nñ !	Nô !	Nð !	Nð !	Nð !	Nð !	Nð !	Nð !	Nøøøð !	Nð !	Nð !	Nð !	NðH ð 3	ñ 3	ˆAÝ�a˜”g‘,”,ˆCØ�nÔ%ˆEØ�nÔ%ˆEØ9:Ð;KÔ9LÐ9X•G�C Ð"2Ô 3Ñ4Ô4Ñ5Ô5Ð5Õ^eÐfiÑ^jÔ^jˆEð �iÐÐØ" 3œ�Ý"¥3 v¨jÔ'9Ð'>¸QÑ#?Ô#?Ñ@Ô@�à˜a’<�<¥C¨°5Ñ(8Ñ$9Ô$9¸FÒ$BÐ$BØ×$Ò$Ø 4Ø$*Ø(-Ø(-Ø#v°(Ð#vÐ#vÐ#vÐfkÐ#vÐ#vÐ#vÐ#vÝ$)¨%¡L¤LÝ"'¨¡/¤/ð&ð &ñ ô ð ð ˜+Ô&¨Ò*Ð*¨v°kÔ/BÀVÐL\ÔE]Ð`aÑEaÒ/aÐ/aØ×$Ò$Ø 7Ø$,Ø(-Ø(-ð $OÀ&ÈÔBUð  $Oð  $OÐouð  wGô  pHð  $Oð  $Oð  $Oð&ð &ñ ô ð ð �iÐÐØ" 3œ�Ø˜,Ô'¨&°Ô*AÒAÐAØ×$Ò$Ø 6Ø$.Ø(-Ø(-ð $}ÐioÐp|Ôi}ð  $}ð  $}ð  \bð  crô  \sð  $}ð  $}ð  $}ð&ð &ñ ô ð õ #¥3 v¨oÔ'>Ð'CÀ!Ñ#DÔ#DÑEÔE�Ø˜1’9�9 ¨EÒ!1Ð!1Ø×$Ò$Ø 1Ø$.Ø(-Ø(-ð $SÀxð  $Sð  $Sð  $Sð  CHð  $Sð  $Sð  $Sð  $SÝ$)¨%¡L¤LÝ"'¨¡/¤/ð&ð &ñ ô ð ùð ×.Ò.¨xÑ8Ô8ˆØ ð 	$ð 	$ˆEØ×Ò˜UÑ#Ô#Ð#Ð#å�h‘-”-ˆØ�cÐ"_Ð"_¸iÐ"_Ñ"_Ô"_Ñ`Ô`Ñ`ˆ	ØCJÈQÂ;À;�% ¨WÑ!4¸Ñ ;¸QÑ?Ô?Ð?ÐTYˆõ $' y¡>¤>°QÒ#6Ð#6�i�i¸IØ%Ø )Ø'6Ý  ™^œ^Ø"Øœ/Ý"œ,™.œ.×2Ò2Ñ4Ô4ð	
ð 	
ð 		
s5   œC$±BCÃC$ÃC	ÃC$ÃC	ÃC$Ã$C(Ã+C(Údb_projectsúList[Dict[str, Any]]c                óº  — g }	 t          j        | j        d¬¦  «        }n6# t          $ r)}t                               d|› �¦  «         |cY d}~S d}~ww xY wi }dD �]U}||j        vrŒ||         }|dk    rdnd}t          d	|j        d
z   ¦  «        D �]}	| 	                    |	d
¦  «        j
        }
| 	                    |	d¦  «        j
        }|
s|sŒ>	 |
rt          |
¦  «        nd}| 	                    |	|¦  «        j
        }t          |t          t          f¦  «        rt          |¦  «        nd}d|› d|d›�}|r!t          |¦  «                             ¦   «         ndt          | 	                    |	d	¦  «        j
        pd¦  «                             ¦   «         ||	|dœ||<   �Œ# t          $ r Y �Œw xY w�ŒW|D ]§}|d         }||v r™||         }|d         �‰|d         ��t          |d         ¦  «        }t          |d         ¦  «        }t!          ||z
  ¦  «        }|dk    r?|                     dd||d         d|d›d|d         › d|d         › d|d›d�	|||d œ¦  «         Œ¨|S )!ua   So khá»›p dá»¯ liá»‡u CSDL vá»›i Báº£ng theo dÃµi cÃ´ng trÃ¬nh.xlsx Ä‘á»ƒ phÃ¡t hiá»‡n row-shift.T)Ú	data_onlyu1   KhÃ´ng thá»ƒ má»Ÿ file Excel Ä‘á»ƒ Ä‘á»‘i soÃ¡t: N)Ú2026Ú2025Ú2024Ú2023Ú2022ru   é   é   é   rD   r6   r   zDA-ú-Ú02dÚ )ÚnameÚcontract_numr(   ÚrowÚsheetr&   r(   iè  ÚEXCEL_DB_ROW_SHIFT_DRIFTr<   r'   u    Lá»‡ch sá»‘ liá»‡u giá»¯a CSDL (r-   u    Ä‘) vÃ  Excel Sheet r�   u    DÃ²ng r€   z (r.   )r/   r0   r&   r'   r1   Údb_valueÚexcel_valueÚ
difference)ÚopenpyxlÚload_workbookrY   Ú	ExceptionÚloggerrF   Ú
sheetnamesÚrangeÚmax_rowÚcellÚvalueÚintÚ
isinstancerU   r   ÚstriprS   rT   )r   ro   ÚdriftsÚwbÚeÚexcel_projects_by_codeÚsnameÚwsÚgt_colr!   Ústtr~   Ústt_intre   Ú	val_floatÚcode_candidatera   ÚcodeÚx_itemÚdb_vÚex_vÚdiffs                         r   rV   z(DataIntegrityService._compare_with_excel�   s8  € àˆð	ÝÔ'¨¬À4ÐHÑHÔHˆBˆBøÝð 	ð 	ð 	Ý�NŠNÐRÈqÐRÐRÑSÔSÐSØˆMˆMˆMˆMˆMˆMøøøøð	øøøð =?ÐØ=ð 	ñ 	ˆEØ˜BœMÐ)Ð)ØØ�E”ˆBØ  Fš?˜?�R�R°ˆFÝ˜1˜bœj¨1™nÑ-Ô-ð ñ �Ø—g’g˜a ‘m”mÔ)�Ø—w’w˜q !‘}”}Ô*�Øð  4ð ØðØ*-Ð4�c #™hœh˜h°1�GØŸGšG A vÑ.Ô.Ô4�EÝ0:¸5Å3ÍÀ,Ñ0OÔ0OÐ Y¥ e¡¤ ÐUY�IØ%@¨5Ð%@Ð%@°7Ð%@Ð%@Ð%@�Nà59Ð A¥ D¡	¤	§¢Ñ 1Ô 1Ð 1¸rÝ(+¨B¯GªG°A°q©M¬MÔ,?Ð,EÀ2Ñ(FÔ(F×(LÒ(LÑ(NÔ(NØ*3Ø Ø!&ð>ð >Ð*¨>Ñ:Ñ:øõ !ð ð ð Ø‘Hðøøøñ#ð( ð 	ð 	ˆAØ�^Ô$ˆDØÐ-Ð-Ð-Ø/°Ô5�ØÐ*Ô+Ð7¸AÐ>NÔ<OÐ<[Ý  Ð#3Ô!4Ñ5Ô5�DÝ  Ð(8Ô!9Ñ:Ô:�DÝ˜t d™{Ñ+Ô+�Dà˜d’{�{ØŸšØ$>Ø(2Ø,0Ø,-¨nÔ,=ð (cÈ$ð  (cð  (cð  (cÐjpÐqxÔjyð  (cð  (cð  CIð  JOô  CPð  (cð  (cð  TXð  (cð  (cð  (cð  (cØ(,Ø+/Ø*.ð	'ð 	'ñ 	ô 	ð 	øð ˆs-   „   
AªAÁAÁAÃCFÆ
F,Æ+F,)N)r   r   )r   r   )ro   rp   r   rp   )Ú__name__Ú
__module__Ú__qualname__Ú__doc__rY   r   rn   rV   r@   r   r   r   r      se   € € € € € Ø{Ð{àR€Jðð ð ð ð ðt
ð t
ð t
ð t
ðl8ð 8ð 8ð 8ð 8ð 8r   )Ú
__future__r   Úloggingr†   Údecimalr   Útypingr   r   r   r   r	   Ú9app.modules.projects.domain.ports.project_repository_portr
   Ú	getLoggerr‰   r   r@   r   r   ú<module>r¬      sÒ   ðØ "Ð "Ð "Ð "Ð "Ð "à €€€Ø €€€Ø Ð Ð Ð Ð Ð Ø ,Ð ,Ð ,Ð ,Ð ,Ð ,Ð ,Ð ,Ð ,Ð ,Ð ,Ð ,Ø Ð Ð Ð Ð Ð à ^Ð ^Ð ^Ð ^Ð ^Ð ^à	ˆÔ	Ð1Ñ	2Ô	2€ðyð yð yð yð yñ yô yð yð yð yr   