
    |i%                     T    d Z ddlmZ ddlmZ ddlmZmZmZmZm	Z	 ddl
mZ 	 d	dZdS )
aE  
app/utils/excel_export.py
=========================
Genera file Excel per l'inventario con due fogli:

Foglio "Inventario" (prodotti rilevati):
1. Nome Sessione
2. Barcode
3. Nome Prodotto
4. Categoria
5. Giacenza Negozio
6. Giacenza Inventario
7. Differenza (inv-neg)
8. Prezzo Acquisto
9. Prezzo Vendita
10. Valorizzazione Acq. (Giacenza Inventario * Prezzo Acquisto)
11. Valorizzazione Vend. (Giacenza Inventario * Prezzo Vendita)

Foglio "Prodotti non presenti" (non rilevati):
1. Barcode
2. Nome Prodotto
3. Categoria
4. Giacenza Negozio
5. Prezzo Acquisto
6. Prezzo Vendita
    )datetime)Workbook)FontPatternFill	AlignmentBorderSide)BytesIO   c                    t                      }t          ddd          }t          ddd          }t          ddd          }t          d	d	d          }t          dd
          }	t          ddd          }
t          t	          d          t	          d          t	          d          t	          d                    }d |D             }d |D             }|j        }d|_        d|j        d         _        d|j        d         _        d|j        d         _        d|j        d         _        d|j        d         _        d|j        d         _        d|j        d         _        d|j        d         _        d|j        d         _        d|j        d         _        d|j        d          _        d!}|	                    |d!"          }d#| 
                                 |_        ||_        ||_        t          d$d%&          |_        |                    d| d'|            |d!z  }d(| |	                    |d!"          _        t          dd)*          |	                    |d!"          _        |d+z  }g d,}t#          |d!          D ]O\  }}|	                    ||"          }||_        ||_        ||_        t          d%d%d-          |_        ||_        P|d!z  }|}|D ]}t'          |                    d.          pd/          }t'          |                    d0          pd/          }t'          |                    d1          pd/          }t'          |                    d2          pd/          }|                    d3d4          |	                    |d!"          _        |                    d5d4          |	                    |d+"          _        |                    d6d4          |	                    |d7"          _        |                    d8d4          |	                    |d9"          _        ||	                    |d:"          _        ||	                    |d;"          _        d<| d=| |	                    |d>"          _        ||	                    |d?"          _        ||	                    |d@"          _        d<| dA| |	                    |d)"          _        d<| dB| |	                    |d"          _        t+          d!dC          D ]U}|	                    ||"          }||_        t          |d:k    rdDnd$dEF          |_        |dGv rdH|_        J|dIv rdJ|_        V|d!z  }|}dKdLdMdNd:dO| dP|d!z
   dQfd;dR| dS|d!z
   dQfd>dT| dU|d!z
   dQfdVdWd)dX| dY|d!z
   dQfddZ| d'|d!z
   dQfg}|D ]l\  }}|	                    ||"          }||_        |	|_        |
|_        ||_        t          |d:k    rdDnd$[          |_        |dGv rdH|_        a|d\v rdJ|_        m|d7z  }d]t/          |          fd^t/          |          fd_t/          |          fg}|D ][\  }}|	                    |d!"          }||_        t          d`          |_        |	                    |d+"          } || _        |d!z  }\|                    d_a          }!d|!j        d         _        d|!j        d         _        d|!j        d         _        d|!j        d         _        d|!j        d         _        d|!j        d         _        d!}|!	                    |d!"          }db| 
                                 |_        ||_        ||_        t          d$d%&          |_        |!                    d| dS|            |d!z  }d(| |!	                    |d!"          _        t          dd)*          |!	                    |d!"          _        |d+z  }g dc}"t#          |"d!          D ]O\  }}|!	                    ||"          }||_        ||_        ||_        t          d%d%d-          |_        ||_        P|d!z  }|D ]}t'          |                    d.          pd/          }t'          |                    d1          pd/          }t'          |                    d2          pd/          }|                    d5d4          |!	                    |d!"          _        |                    d6d4          |!	                    |d+"          _        |                    d8d4          |!	                    |d7"          _        ||!	                    |d9"          _        ||!	                    |d:"          _        ||!	                    |d;"          _        t+          d!d>          D ]W}|!	                    ||"          }||_        t          |d9k    rdDnd$dEF          |_        |d9k    rdH|_        L|ddv rdJ|_        X|d!z  }t3                      }#|                    |#           |#                    d/           |#S )ez-
    Genera file Excel per l'inventario.
    T   FFFFFF)boldsizecolor1F4E78solid)start_color	end_color	fill_type   4472C4)r   r   D9E1F2thin)style)leftrighttopbottomc                 <    g | ]}|                     d           |S rilevatoget.0ps     ?/var/www/tmov.alphamb/tmov_inventario/app/utils/excel_export.py
<listcomp>z,generate_inventory_excel.<locals>.<listcomp>@   s)    >>>aAEE*,=,=>>>>    c                 <    g | ]}|                     d           |S r!   r#   r%   s     r(   r)   z,generate_inventory_excel.<locals>.<listcomp>A   s)    FFF!AEE*4E4EFAFFFr*   
Inventario   A   B(   CD   EFGHIJKr   )rowcolumnzINVENTARIO r   center)
horizontalverticalz:KzData valorizzazione: 
   )italicr      )zNome SessioneBarcodeNome Prodotto	CategoriaGiacenza NegoziozGiacenza InventariozDifferenza (inv-neg)Prezzo AcquistoPrezzo VenditazValorizzazione Acq.zValorizzazione Vend.)r?   r@   	wrap_textquantita_sistemar   quantita_fisicacostoprezzo_pubbliconome_sessione barcodenome_prodotto   	categoria         z=Fz-E      	   z*Hz*I   r   F)r?   rJ   )rV   rW   rX   0)rY   rZ   rA   r   u   € #,##0.00)r   TOTALI)rC   rP   )rS   rP   )rU   rP   z=SUM(Ez:E)z=SUM(Fz:Fz=SUM(Gz:G)rY   rP   )rZ   rP   z=SUM(Jz:Jz=SUM(K)r?   )rA   r   zNumero prodotti totalizProdotti rilevatizProdotti non presenti)r   )titlezPRODOTTI NON PRESENTI - )rD   rE   rF   rG   rH   rI   )rV   rW   )r   r   r   r   r	   activer_   column_dimensionswidthcelluppervaluefontfillr   	alignmentmerge_cells	enumerateborderfloatr$   rangenumber_formatlencreate_sheetr
   saveseek)$	sede_nomedata_valorizzazioneproducts_data	tenant_idworkbookstyle_headerfill_headerstyle_colonnafill_colonnastyle_totalefill_totalerk   rilevatinon_rilevatiws_invr<   rc   headers_invcolheaderrow_data_startproduct   qtà_sistema   qtà_fisicarM   prezzo
row_totalecells_totalere   statslabelcell1cell2ws_np
headers_np
excel_files$                                       r(   generate_inventory_excelr   "   sV    zzH T(;;;L(hRYZZZKd8<<<M8xSZ[[[LT+++L(hRYZZZK   v&!!!	  F ?>=>>>HFF}FFFL _FFL +-FS!'*,FS!'*,FS!'*,FS!'*,FS!'*,FS!'*,FS!'*,FS!'*,FS!'*,FS!'*,FS!' C;;3q;))D2y0022DJDIDI&8DDDDN
'3''#''(((1HC+XCV+X+XFKKCK""(*.d*D*D*DFKKCK""'1HC  K !a00  V{{s3{//
!	 	"hUYZZZ1HCN  $ $W[[);<<ABBGKK(9::?a@@gkk'**/a00w{{#455:;;/6{{?B/O/OA&&,/6{{9b/I/IA&&,/6{{?B/O/OA&&,/6{{;/K/KA&&,/;A&&,/:A&&, 0AC/@/@3/@/@A&&,/4A&&,/5A&&, 1BS0A0AC0A0AB''- 1BS0A0AC0A0AB''- B<< 	4 	4C;;3s;33D DK&SAXX''6]bcccDNi%(""&&%3"q J 		
/^//s1u///0	
/^//s1u///0	
/^//s1u///0	0n00A0001	0n00A0001L # 0 0
U{{z#{66
 		"ggvNNN)!$DH__!/D 1HC 
"3}#5#56	c(mm,	 #l"3"34E   uA..t___
A..q !!(?!@@E *,EC &)+EC &)+EC &)+EC &)+EC &)+EC & C::#a:((D?IOO,=,=??DJDIDI&8DDDDN	&#&&&&'''1HC*WBU*W*WEJJ3qJ!!')-T)C)C)CEJJ3qJ!!&1HC  J !Q//  Vzzc#z..
!	 	"hUYZZZ1HC    W[[);<<ABBgkk'**/a00w{{#455:;;.5kk)R.H.H

s1
%%+.5kk/2.N.N

s1
%%+.5kk+r.J.J

s1
%%+.:

s1
%%+.3

s1
%%+.4

s1
%%+ A;; 	4 	4C::#c:22D DK&SAXX''6]bcccDNaxx%(""%3"q JMM*OOAr*   N)r   )__doc__r   openpyxlr   openpyxl.stylesr   r   r   r   r	   ior
   r    r*   r(   <module>r      s    6             F F F F F F F F F F F F F F       	I I I I I Ir*   