§
    ÔŸ’j}  ã                  ób   — d dl mZ 	 d dlZd dlmZ  ej        e¦  «        Z G d„ d¦  «        ZdS )é    )ÚannotationsN)ÚAnyc                  ó"   — e Zd ZdZ	 	 	 d
dd	„ZdS )ÚInvoiceAnalyticsCostMixinz&Mixin for material and cost analytics.NÚ
project_idú
str | NoneÚ	from_dateÚto_dateÚreturnúdict[str, Any]c                óp  — |                       ¦   «         5 }|                     ¦   «         5 }ddg}g }|r*|                     d¦  «         |                     |¦  «         |r*|                     d¦  «         |                     |¦  «         |r*|                     d¦  «         |                     |¦  «         d                     |¦  «        }|                     d|› d�|¦  «         |                     ¦   «         }	d	d
ddddœ}
g }t          d„ |	D ¦   «         ¦  «        pd}|	D ]�}|d         }t          |d         ¦  «        }t          |d         ¦  «        }t          ||z  dz  d¦  «        }|                     ||
 	                    ||¦  «        t          |d         ¦  «        |||dœ¦  «         ŒŽ|                     d|› d�|¦  «         |                     ¦   «         }g }|D ]‰}t          |d         ¦  «        }t          |d         ¦  «        }|dk    r||z  nd}|                     |d         |d         |d         ||t          |d          ¦  «        t          |d¦  «        d!œ¦  «         ŒŠ||||dk    r|nd||d"œcd#d#d#¦  «         cd#d#d#¦  «         S # 1 swxY w Y   	 d#d#d#¦  «         d#S # 1 swxY w Y   d#S )$un   PhÃ¢n tÃ­ch phÃ¢n rÃ£ chi phÃ­ váº­t tÆ° chÃ­nh, ca mÃ¡y, tháº§u phá»¥ vÃ  Ä‘Æ¡n giÃ¡ bÃ¬nh quÃ¢n gia quyá»�n.zi.direction = 'input'zi.status = 'valid'zi.matched_project_id = %szi.issue_date >= %szi.issue_date <= %sz AND aã  
                    SELECT
                        COALESCE(item.cost_category, 'other') AS cost_category,
                        COUNT(item.id) AS item_count,
                        COALESCE(SUM(item.amount_before_vat_vnd), 0) AS total_subtotal_vnd,
                        COALESCE(SUM(item.total_item_amount_vnd), 0) AS total_amount_vnd
                    FROM erp_invoice_items item
                    JOIN erp_invoices i ON item.invoice_id = i.id
                    WHERE zˆ
                    GROUP BY COALESCE(item.cost_category, 'other')
                    ORDER BY total_amount_vnd DESC;
                u<   Váº­t liá»‡u chÃ­nh (ThÃ©p, Xi mÄƒng, BÃª tÃ´ng, CÃ¡t, Ä�Ã¡)u   Ca mÃ¡y & NhÃ¢n cÃ´ng thi cÃ´ngu)   Tháº§u phá»¥ & GÃ³i tháº§u chuyÃªn biá»‡tu   Chi phÃ­ chung & LÃ¡n tráº¡iu   Chi phÃ­ khÃ¡c)Úmaterial_mainÚmachinery_laborÚsubcontractorÚgeneral_expenseÚotherc              3  ó@   K  — | ]}t          |d          ¦  «        V — ŒdS )Útotal_amount_vndN)Úfloat)Ú.0Úrs     úDC:\Projects\DSCons\app\services\invoice_processing\analytics_cost.pyú	<genexpr>zLInvoiceAnalyticsCostMixin.get_material_and_cost_analytics.<locals>.<genexpr>C   s0   è è € ÐGÐG¸�˜aÐ 2Ô3Ñ4Ô4ÐGÐGÐGÐGÐGÐGó    g      ð?Úcost_categoryr   Útotal_subtotal_vndg      Y@é   Ú
item_count)Úcategory_codeÚcategory_namer   Úsubtotal_vndr   Ú
percentagea  
                    SELECT
                        item.item_name,
                        item.unit,
                        COALESCE(item.cost_category, 'other') AS cost_category,
                        SUM(item.quantity) AS total_qty,
                        SUM(item.amount_before_vat_vnd) AS total_subtotal,
                        SUM(item.total_item_amount_vnd) AS total_spent
                    FROM erp_invoice_items item
                    JOIN erp_invoices i ON item.invoice_id = i.id
                    WHERE zÑ AND item.quantity > 0
                    GROUP BY item.item_name, item.unit, COALESCE(item.cost_category, 'other')
                    ORDER BY total_spent DESC
                    LIMIT 20;
                Ú	total_qtyÚtotal_subtotalr   g        Ú	item_nameÚunitÚtotal_spent)r%   r&   r   Útotal_quantityr   Útotal_spent_vndÚweighted_avg_unit_price_vnd)r   r	   r
   Útotal_spend_vndÚcategories_breakdownÚtop_materialsN)Úget_connectionÚcursorÚappendÚjoinÚexecuteÚfetchallÚsumr   ÚroundÚgetÚint)Úselfr   r	   r
   ÚconnÚcurÚwhere_clausesÚparamsÚ	where_sqlÚcat_rowsÚcategory_labelsÚcategories_dataÚtotal_all_spendr   Úcat_codeÚtot_vndÚsub_vndÚpctÚtop_items_rowsr-   ÚqtyÚsubÚ	avg_prices                          r   Úget_material_and_cost_analyticsz9InvoiceAnalyticsCostMixin.get_material_and_cost_analytics   s>  € ð × Ò Ñ"Ô"ð o	 dØ—’‘”ð n #Ø!8Ð:NÐ O�Ø$&�àð .Ø!×(Ò(Ð)DÑEÔEÐEØ—M’M *Ñ-Ô-Ð-Øð -Ø!×(Ò(Ð)=Ñ>Ô>Ð>Ø—M’M )Ñ,Ô,Ð,Øð +Ø!×(Ò(Ð)=Ñ>Ô>Ð>Ø—M’M 'Ñ*Ô*Ð*à#ŸLšL¨Ñ7Ô7�	ð —’ðð %ðð ð ð ñô ð ð Ÿ<š<™>œ>�ð &dØ'HØ%PØ'EØ-ð#ð #�ð #%�åÐGÐG¸hÐGÑGÔGÑGÔGÐNÈ3ð  ð "ð ð �AØ  Ô1�HÝ# AÐ&8Ô$9Ñ:Ô:�GÝ# AÐ&:Ô$;Ñ<Ô<�GÝ ¨?Ñ!:¸eÑ CÀQÑGÔG�CØ#×*Ò*à-5Ø-<×-@Ò-@ÀÈ8Ñ-TÔ-TÝ*-¨a°¬oÑ*>Ô*>Ø,3Ø07Ø*-ðð ñ	ô 	ð 	ð 	ð —’ðð %ðð ð ð ñ!ô ð ð$ "%§¢¡¤�à "�Ø'ð ð �AÝ  +¤Ñ/Ô/�CÝ Ð"2Ô 3Ñ4Ô4�CØ/2°Qªw¨w  s¡ ¸C�IØ!×(Ò(à)*¨;¬Ø$% f¤IØ-.¨Ô-?Ø.1Ø25Ý/4°Q°}Ô5EÑ/FÔ/FÝ;@ÀÈAÑ;NÔ;Nðð ñ
ô 
ð 
ð 
ð #-Ø!*Ø&à&¨#Ò-Ð-ð (7 àØ,;Ø%2ð	ð 	ðKnð nð nð nñ nô nð nðo	ð o	ð o	ð o	ñ o	ô o	ð o	ð o	ðnð nð nð nøøøð nð nð nð nð nðo	ð o	ð o	ñ o	ô o	ð o	ð o	ð o	ð o	ð o	ð o	ð o	øøøð o	ð o	ð o	ð o	ð o	ð o	s5   •J+ªIJÉ9J+ÊJ	ÊJ+ÊJ	ÊJ+Ê+J/Ê2J/)NNN)r   r   r	   r   r
   r   r   r   )Ú__name__Ú
__module__Ú__qualname__Ú__doc__rJ   © r   r   r   r      sE   € € € € € Ø0Ð0ð "&Ø $Ø"ð	vð vð vð vð vð vð vr   r   )	Ú
__future__r   ÚloggingÚtypingr   Ú	getLoggerrK   Úloggerr   rO   r   r   ú<module>rU      s…   ðØ "Ð "Ð "Ð "Ð "Ð "à 3ð €€€Ø Ð Ð Ð Ð Ð à	ˆÔ	˜8Ñ	$Ô	$€ðyð yð yð yð yñ yô yð yð yð yr   