
    |i)#                         d Z ddlmZmZmZmZ ddlmZ ddlmZ ddl	Z	ddl
Z
ddlZddlmZ  e	j        e          Z eded	          Zd
 Zd Ze                    ddg          ed                         ZdS )a  
app/routes/export.py
====================
Export DEFINITIVO: Source of truth = inv_risultati_inventario (scansioni)

Query semplici, no JSON:
1. Scansionati (inv_risultati_inventario)
2. LEFT JOIN cache_export per nome/categoria/prezzi
3. LEFT JOIN giacenze
4. Catalogo non scansionato
    )	Blueprintrequestjsonify	send_filewraps)datetimeN)generate_inventory_excelexportz/api/v1/export)
url_prefixc                 <     t                      fd            }|S )Nc                  6    dddddt           _         | i |S )N   zdemo@studium.itadmin)id	tenant_idemailrole)r   user)argskwargsfs     :/var/www/tmov.alphamb/tmov_inventario/app/routes/export.py	decoratedzrequire_auth.<locals>.decorated   s6     &	
 
 q$!&!!!    r   )r   r   s   ` r   require_authr      s4    
1XX" " " " X" r   c                      t          j        dd          t          j        dd          t          j        dd          t          j        dd          d	} t          j        di | d
t          j        j        dS )NDB_HOST	localhostDB_USERenigma_userDB_PASSWORD
arcana2026DB_NAMEenigma)hostr   passworddatabaseutf8mb4)charsetcursorclass )osgetenvpymysqlconnectcursors
DictCursor)	db_configs    r   get_db_connectionr4   (   st    	)[11	)]33Im\::Ii22	 I ?bbYb	wGabbbbbr   z/excel/inventoryPOST)methodsc                     	 t          j        d          pi } |                     d          }|                     d          }|r|st          ddd          dfS 	 t	          j        |d	           n$# t          $ r t          dd
d          dfcY S w xY wt           j        }|d         }t                      }|	                                }|
                    d||f           |                                }|s<|                                 |                                 t          ddd          dfS |d         }t                              d| d           |
                    d||f           |                                }	t                              dt!          |	                      |
                    d||f           |                                }
t                              dt!          |
                      |                                 |                                 g }|	D ]}|d         pd|d         |d         p	|d         pd|d         pdt#          |d         pd          t#          |d         pd          t#          |d          pd          t#          |d!         pd          dd"	}|                    |           |
D ]x}d|d#         |d         pd|d         pdt#          |d         pd          dt#          |d          pd          t#          |d!         pd          dd"	}|                    |           yt                              d$t!          |                      |                    d% &           t)          ||||'          }t	          j        |d	          }|                    d(          }|                    d)d*          }d+| d*| d,}t/          |d-d|.          S # t0          $ r`}t                              d/|            dd0l}|                                 t          dt9          |          d          d1fcY d0}~S d0}~ww xY w)2z,Export: scansioni + cache_export + giacenze.T)silentsede_iddata_valorizzazioneFz'sede_id e data_valorizzazione richiesti)successerrori  z%Y-%m-%dz(Formato data non valido (usa YYYY-MM-DD)r   za
            SELECT id, nome FROM inv_sedi 
            WHERE id = %s AND id_tenant = %s
        zFiliale non trovatai  nomezInizio export sede z...aV  
            SELECT
              r.barcode,
              r.nome_prodotto as nome_scan,
              r.categoria as categoria_scan,
              SUM(r.quantita_fisica) as quantita_fisica,
              GROUP_CONCAT(DISTINCT s.note) as sessioni,
              COALESCE(e.descrizione, r.nome_prodotto) as descrizione,
              COALESCE(e.categoria, r.categoria) as categoria,
              COALESCE(e.costo, 0) as costo,
              COALESCE(e.prezzo_pubblico, 0) as prezzo_pubblico,
              COALESCE(e.id_prodotto, '') as id_prodotto,
              COALESCE(g.quantita, 0) as quantita_magazzino
            FROM inv_risultati_inventario r
            LEFT JOIN inv_sessioni_inventario s ON r.id_sessione = s.id
            LEFT JOIN inv_cache_export e
              ON e.codice_barcode = r.barcode
              AND e.id_tenant = r.id_tenant
            LEFT JOIN (
                SELECT id_prodotto, id_sede, id_tenant,
                    COALESCE(
                        MAX(CASE WHEN id_variante IS NULL THEN quantita END),
                        SUM(CASE WHEN id_variante IS NOT NULL THEN quantita ELSE 0 END)
                    ) as quantita
                FROM inv_giacenze
                GROUP BY id_prodotto, id_sede, id_tenant
            ) g ON r.codice_prodotto = g.id_prodotto
              AND g.id_sede = r.id_sede
              AND g.id_tenant = r.id_tenant
            WHERE r.id_tenant = %s AND r.id_sede = %s
            GROUP BY r.barcode, r.nome_prodotto, r.categoria, e.descrizione,
                     e.categoria, e.costo, e.prezzo_pubblico, e.id_prodotto, g.quantita
        zScansionati: a  
            SELECT
              e.codice_barcode,
              e.descrizione,
              e.categoria,
              e.costo,
              e.prezzo_pubblico,
              e.id_prodotto,
              COALESCE(g.quantita, 0) as quantita_magazzino
            FROM inv_cache_export e
            INNER JOIN (
                SELECT id_prodotto, id_sede, id_tenant,
                    COALESCE(
                        MAX(CASE WHEN id_variante IS NULL THEN quantita END),
                        SUM(CASE WHEN id_variante IS NOT NULL THEN quantita ELSE 0 END)
                    ) as quantita
                FROM inv_giacenze
                GROUP BY id_prodotto, id_sede, id_tenant
            ) g ON e.id_prodotto = g.id_prodotto
              AND g.id_sede = %s
              AND g.id_tenant = e.id_tenant
            WHERE e.id_tenant = %s
            AND NOT EXISTS (
              SELECT 1 FROM inv_risultati_inventario r
              WHERE r.barcode = e.codice_barcode
              AND r.id_tenant = e.id_tenant
            )
        zCatalogo non scansionato: sessioni barcodedescrizione	nome_scanzN/A	categoriaquantita_magazzinor   quantita_fisicacostoprezzo_pubblico)	nome_sessioner@   nome_prodottorC   quantita_sistemarE   rF   rG   rilevatocodice_barcodezTotale: c                 Z    |                      dd          |                      dd          fS )NrH   r?   r@   )get)ps    r   <lambda>z(export_inventory_excel.<locals>.<lambda>   s)    or0J0JAEER[]_L`L`/a r   )key)	sede_nomer:   products_datar   z%d-%m-%Y _Inventario_z.xlsxzAapplication/vnd.openxmlformats-officedocument.spreadsheetml.sheet)mimetypeas_attachmentdownload_namezErrore: Ni  )r   get_jsonrN   r   r	   strptime
ValueErrorr   r4   cursorexecutefetchonecloseloggerinfofetchalllenfloatappendsortr
   strftimereplacer   	Exceptionr<   	traceback	print_excstr)datar9   r:   r   r   connr]   sederR   scansionaticatalogoproducts_for_exportrowproduct_row
excel_filedata_objdata_formattata	sede_slugfilenameerk   s                        r   export_inventory_excelr|   2   s]   
|At,,,2((9%%"hh'<== 	1 	 B     
	1:>>>> 	 	 	 C       	 |%	 ""  y!	# 	# 	#
    	TLLNNNJJLLLu7LMMNNPSSSL	6'666777 	  @ !A 	#  	#  	#D oo''6C$4$466777 	 6 y!7	# 	# 	#: ??$$@X@@AAA

 !  	4 	4C!$Z!6By>!$]!3!Ps;7G!P5 -6$)#.B*C*Hq$I$I#(->)?)D1#E#Es7|0q11#(->)?)D1#E#E 
 
K  &&{3333  	4 	4C!#/0!$]!3!<u -6$)#.B*C*Hq$I$I#$s7|0q11#(->)?)D1#E#E!
 
K  &&{33339s#67799::: 	  %a%a bbb . 3-	
 
 

 $%8*EE"++J77%%c3//	CCC_CCCX"	
 
 
 	
  A A A^^^$$$53q66::;;S@@@@@@@	AsK   AO= A2 1O= 2BO= BB"O= 6KO= =
Q'AQ"Q'"Q')__doc__flaskr   r   r   r   	functoolsr   r	   loggingr-   r/   app.utils.excel_exportr
   	getLogger__name__ra   	export_bpr   r4   router|   r,   r   r   <module>r      s  
 
 9 8 8 8 8 8 8 8 8 8 8 8              				  ; ; ; ; ; ;		8	$	$Ih5EFFF	
 
 
c c c #fX66A A  76A A Ar   