Spaces:
Sleeping
Sleeping
| import streamlit as st | |
| import pandas as pd | |
| import json | |
| from datetime import datetime | |
| import io | |
| import os | |
| import pytz # Assicurati che pytz sia nel requirements.txt | |
| # --- Inizializzazione Session State --- | |
| if 'file_uploader_key_json' not in st.session_state: | |
| st.session_state.file_uploader_key_json = 0 | |
| if 'file_uploader_key_customers' not in st.session_state: | |
| st.session_state.file_uploader_key_customers = 0 | |
| # --- CSS personalizzato --- | |
| st.markdown( | |
| """ | |
| <style> | |
| .stApp { | |
| background-color: #FFFFCC; /* Giallo pastello */ | |
| background-attachment: fixed; | |
| } | |
| h1, h2 { | |
| color: #1E90FF; /* Blu acceso */ | |
| } | |
| p, label { | |
| color: #000080; /* Blu scuro */ | |
| } | |
| .stButton>button { | |
| color: white; | |
| background-color: #1E90FF; /* Blu acceso */ | |
| border: none; | |
| border-radius: 5px; | |
| padding: 10px 20px; | |
| font-size: 16px; | |
| } | |
| .stButton>button:hover { | |
| background-color: #4169E1; /* Blu medio al passaggio del mouse */ | |
| } | |
| </style> | |
| """, | |
| unsafe_allow_html=True | |
| ) | |
| # Definizione del fuso orario per l'Italia (per coerenza, anche se le date Outbound sembrano solo data) | |
| ITALY_TIMEZONE = pytz.timezone("Europe/Rome") | |
| UTC_TIMEZONE = pytz.utc | |
| # Funzione di conversione per Outbound | |
| def convert_json_to_excel_data_outbound(json_file_content: str, customers_df: pd.DataFrame = None): | |
| try: | |
| json_data = json.loads(json_file_content) | |
| # Campi a livello radice | |
| site_code_json = json_data.get('siteCode') | |
| company_code_json = json_data.get('companyCode') | |
| # Campi sotto 'outbound' | |
| outbound_info = json_data.get('outbound', {}) | |
| order_number = outbound_info.get('orderNumber') | |
| order_type_json = outbound_info.get('orderType') | |
| delivery_type_fixed = "DELIVERY" # Valore fisso | |
| order_doc_date_str = outbound_info.get('orderDocDate') | |
| order_date_detail = outbound_info.get('orderDateDetail') | |
| due_date_str = outbound_info.get('orderDate') # Corrisponde a 'dueDate' | |
| reason_code = outbound_info.get('reasonCode') | |
| service_fixed = "PDB" # Valore fisso | |
| service_type_fixed = "Standard" # Valore fisso | |
| payment_info_value = outbound_info.get('paymentInfo', {}).get('value') | |
| delivery_customer = outbound_info.get('deliveryCustomer', {}) | |
| customer_code = delivery_customer.get('code') | |
| customer_mail = delivery_customer.get('refMail') # customer.mail dal JSON | |
| # Consignee data (from JSON) | |
| consignee_name = delivery_customer.get('name') | |
| consignee_address = delivery_customer.get('address') | |
| consignee_city = delivery_customer.get('city') | |
| consignee_province = delivery_customer.get('province') | |
| consignee_zip_code = delivery_customer.get('zipCode') | |
| consignee_country = delivery_customer.get('country') | |
| # Customer data (from Excel lookup) | |
| customer_name = "" | |
| customer_address = "" | |
| customer_city = "" | |
| customer_province = "" | |
| customer_zip_code = "" | |
| customer_country = "" | |
| # Esegui la lookup solo se customers_df è fornito e non vuoto | |
| if customers_df is not None and not customers_df.empty and customer_code and company_code_json: | |
| # Assicurati che le colonne di join siano dello stesso tipo | |
| # customers_df['Code'] = customers_df['Code'].astype(str) | |
| # customers_df['company.code'] = customers_df['company.code'].astype(str) | |
| # Filtra per Code e company.code | |
| customer_data = customers_df[ | |
| (customers_df['Code'].astype(str) == str(customer_code)) & | |
| (customers_df['company.code'].astype(str) == str(company_code_json)) | |
| ] | |
| if not customer_data.empty: | |
| # Prendiamo la prima riga se ce ne sono più di una | |
| customer_row = customer_data.iloc[0] | |
| customer_name = customer_row.get('partner.name', '') | |
| customer_address = customer_row.get('partner.address', '') | |
| customer_city = customer_row.get('partner.address.city', '') | |
| customer_province = customer_row.get('partner.address.province', '') | |
| customer_zip_code = customer_row.get('partner.address.zipCode', '') | |
| customer_country = customer_row.get('partner.address.country', '') | |
| else: | |
| st.warning(f"Attenzione: Dettagli cliente non trovati nell'Excel per Code: '{customer_code}' e Company: '{company_code_json}'.") | |
| # Note fields | |
| note_ddt = outbound_info.get('noteDdt') | |
| note_warehouse = outbound_info.get('noteWarehouse') | |
| note_delivery = outbound_info.get('noteDelivery') | |
| note_shipment = outbound_info.get('noteShipment') | |
| reason_code_shp = outbound_info.get('reasonCodeShp') | |
| info_delivery = outbound_info.get('infoDelivery') | |
| booking_info = outbound_info.get('bookingInfo') | |
| # Formattazione delle date (Outbound sembra avere solo date, senza ora) | |
| order_doc_date_formatted = None | |
| if order_doc_date_str: | |
| try: | |
| # Rimuovi 'Z' se presente e converti a datetime senza info fuso orario, poi formatta | |
| dt_object = datetime.fromisoformat(order_doc_date_str.replace('Z', '')) | |
| order_doc_date_formatted = dt_object.strftime('%d/%m/%Y') | |
| except ValueError: | |
| order_doc_date_formatted = order_doc_date_str | |
| st.warning(f"Attenzione: Impossibile parsare 'orderDocDate' '{order_doc_date_str}'. Verrà usata la stringa originale.") | |
| due_date_formatted = None | |
| if due_date_str: # Questo è il campo 'orderDate' del JSON che diventa 'dueDate' | |
| try: | |
| dt_object = datetime.fromisoformat(due_date_str.replace('Z', '')) | |
| due_date_formatted = dt_object.strftime('%d/%m/%Y') | |
| except ValueError: | |
| due_date_formatted = due_date_str | |
| st.warning(f"Attenzione: Impossibile parsare 'orderDate' (dueDate) '{due_date_str}'. Verrà usata la stringa originale.") | |
| details = outbound_info.get('details', []) | |
| processed_data_for_this_file = [] | |
| for item in details: | |
| row = { | |
| 'siteCode': site_code_json, | |
| 'companyCode': company_code_json, | |
| 'orderNumber': order_number, | |
| 'orderType': order_type_json, | |
| 'deliveryType': delivery_type_fixed, | |
| 'orderDocDate': order_doc_date_formatted, | |
| 'orderDateDetail': order_date_detail, | |
| 'dueDate': due_date_formatted, | |
| 'reasonCode': reason_code, | |
| 'service': service_fixed, | |
| 'serviceType': service_type_fixed, | |
| 'paymentInfo.value': payment_info_value, | |
| 'customer.code': customer_code, | |
| 'customer.name': customer_name, # Da Excel | |
| 'customer.address': customer_address, # Da Excel | |
| 'customer.city': customer_city, # Da Excel | |
| 'customer.province': customer_province, # Da Excel | |
| 'customer.zipCode': customer_zip_code, # Da Excel | |
| 'customer.country': customer_country, # Da Excel | |
| 'consignee.name': consignee_name, # Dal JSON | |
| 'consignee.address': consignee_address, # Dal JSON | |
| 'consignee.city': consignee_city, # Dal JSON | |
| 'consignee.province': consignee_province, # Dal JSON | |
| 'consignee.zipCode': consignee_zip_code, # Dal JSON | |
| 'consignee.country': consignee_country, # Dal JSON | |
| 'customer.mail': customer_mail, # Dal JSON | |
| 'details.positionId': item.get('positionId'), | |
| 'details.itemNumber': item.get('itemCode'), # Rinominato da itemCode | |
| 'details.requestedQt': item.get('quantity'), # Rinominato da quantity | |
| 'details.lotId': item.get('lotId'), | |
| 'details.stockOwner': item.get('stockOwner'), | |
| 'details.magCli': item.get('warehouse'), # Rinominato da warehouse | |
| 'details.availability': item.get('availability'), | |
| 'details.accountingStatus': item.get('accountingStatus'), | |
| 'details.refOrder': item.get('refOrder'), | |
| 'noteDdt': note_ddt, | |
| 'noteWarehouse': note_warehouse, | |
| 'noteDelivery': note_delivery, | |
| 'noteShipment': note_shipment, | |
| 'reasonCodeShp': reason_code_shp, | |
| 'infoDelivery': info_delivery, | |
| 'bookingInfo': booking_info | |
| } | |
| processed_data_for_this_file.append(row) | |
| if not processed_data_for_this_file: | |
| return pd.DataFrame() | |
| return pd.DataFrame(processed_data_for_this_file) | |
| except json.JSONDecodeError as e: | |
| st.error(f"Errore nel file JSON: {e}. Assicurati che il contenuto sia un JSON valido.") | |
| return None | |
| except KeyError as ke: | |
| st.error(f"Errore: Chiave JSON mancante o struttura inattesa: {ke}. Assicurati che il JSON abbia la struttura attesa.") | |
| return None | |
| except Exception as e: | |
| st.error(f"Si è verificato un errore generico durante la conversione di un file JSON: {e}") | |
| return None | |
| # --- Interfaccia Streamlit --- | |
| st.title("Convertitore JSON Outbound a Template Excel Outbound") | |
| st.write("Carica i tuoi file JSON degli ordini Outbound e un file Excel con i dati dei clienti.") | |
| st.header("1. Carica il file Excel dei Clienti") | |
| st.info("Questo file Excel deve contenere le colonne: `Code`, `company.code`, `partner.name`, `partner.address`, `partner.address.city`, `partner.address.province`, `partner.address.zipCode`, `partner.address.country`.") | |
| uploaded_customers_file = st.file_uploader( | |
| "Scegli il file Excel con i dettagli dei Clienti", | |
| type=["xlsx", "xls"], | |
| key=st.session_state.file_uploader_key_customers | |
| ) | |
| customers_df = pd.DataFrame() | |
| if uploaded_customers_file: | |
| try: | |
| customers_df = pd.read_excel(uploaded_customers_file) | |
| # Rinomina le colonne per facilitare l'accesso, se necessario, o assicurati che i nomi corrispondano | |
| # Es: customers_df.rename(columns={'Codice': 'Code', 'Nome': 'partner.name'}, inplace=True) | |
| # Assicurati che le colonne per il merge siano stringhe per evitare problemi di tipo | |
| customers_df['Code'] = customers_df['Code'].astype(str) | |
| customers_df['company.code'] = customers_df['company.code'].astype(str) | |
| st.success("File Excel Clienti caricato con successo.") | |
| # Debug: st.dataframe(customers_df.head()) | |
| except Exception as e: | |
| st.error(f"Errore nel caricamento del file Excel Clienti: {e}. Assicurati che sia un file Excel valido e che le colonne siano corrette.") | |
| customers_df = pd.DataFrame() # Reset in caso di errore | |
| st.header("2. Carica i tuoi file JSON degli Ordini Outbound") | |
| uploaded_json_files = st.file_uploader( | |
| "Scegli uno o più file JSON di tipo 'Ordine_Outbound'", | |
| type=["json"], | |
| accept_multiple_files=True, | |
| key=st.session_state.file_uploader_key_json | |
| ) | |
| all_processed_data = [] | |
| if uploaded_json_files and not customers_df.empty: | |
| st.info(f"Caricati {len(uploaded_json_files)} file JSON. Elaborazione in corso...") | |
| conversion_successful = True | |
| for uploaded_file in uploaded_json_files: | |
| file_name = uploaded_file.name | |
| if not file_name.lower().startswith("ordine_outbound"): | |
| st.error(f"Errore: Il file '{file_name}' non inizia con 'Ordine_Outbound'. Verrà ignorato.") | |
| conversion_successful = False | |
| continue | |
| try: | |
| json_content_bytes = uploaded_file.read() | |
| json_content_string = json_content_bytes.decode('utf-8') | |
| # Passa customers_df alla funzione di conversione | |
| df_this_file = convert_json_to_excel_data_outbound(json_content_string, customers_df) | |
| if df_this_file is not None and not df_this_file.empty: | |
| all_processed_data.append(df_this_file) | |
| elif df_this_file is None: | |
| conversion_successful = False | |
| st.error(f"Impossibile convertire il file '{file_name}'. Controlla i log per i dettagli.") | |
| except Exception as e: | |
| st.error(f"Si è verificato un errore imprevisto durante la lettura o l'elaborazione del file '{file_name}': {e}") | |
| conversion_successful = False | |
| if all_processed_data: | |
| final_df = pd.concat(all_processed_data, ignore_index=True) | |
| st.success(f"Elaborazione di {len(all_processed_data)} file JSON completata! Pronto per il download.") | |
| # --- Post-processing per la formattazione Excel --- | |
| # Definizione delle colonne di testata | |
| header_columns = [ | |
| 'siteCode', 'companyCode', 'orderNumber', 'orderType', 'deliveryType', | |
| 'orderDocDate', 'orderDateDetail', 'dueDate', 'reasonCode', | |
| 'service', 'serviceType', 'paymentInfo.value', | |
| 'customer.code', 'customer.name', 'customer.address', 'customer.city', | |
| 'customer.province', 'customer.zipCode', 'customer.country', | |
| 'consignee.name', 'consignee.address', 'consignee.city', 'consignee.province', | |
| 'consignee.zipCode', 'consignee.country', 'customer.mail', | |
| 'noteDdt', 'noteWarehouse', 'noteDelivery', 'noteShipment', | |
| 'reasonCodeShp', 'infoDelivery', 'bookingInfo' | |
| ] | |
| # Ordina il DataFrame per orderNumber e details.positionId per raggruppare gli ordini | |
| final_df = final_df.sort_values(by=['orderNumber', 'details.positionId']).reset_index(drop=True) | |
| # Crea una maschera booleana per identificare le righe duplicate per orderNumber | |
| rows_to_clear_headers = final_df.duplicated(subset=['orderNumber'], keep='first') | |
| # Azzerare le colonne di testata per le righe identificate | |
| final_df.loc[rows_to_clear_headers, header_columns] = "" | |
| # --- Creazione file Excel --- | |
| output = io.BytesIO() | |
| writer = pd.ExcelWriter(output, engine='xlsxwriter') | |
| sheet_name_excel = 'OutboundOrder' | |
| workbook = writer.book | |
| worksheet = writer.sheets.get(sheet_name_excel) | |
| if worksheet is None: | |
| worksheet = workbook.add_worksheet(sheet_name_excel) | |
| # Lista delle colonne finali nell'ordine desiderato | |
| final_columns_order = [ | |
| 'siteCode', 'companyCode', 'orderNumber', 'orderType', 'deliveryType', | |
| 'orderDocDate', 'orderDateDetail', 'dueDate', 'reasonCode', | |
| 'service', 'serviceType', 'paymentInfo.value', | |
| 'customer.code', 'customer.name', 'customer.address', 'customer.city', | |
| 'customer.province', 'customer.zipCode', 'customer.country', | |
| 'consignee.name', 'consignee.address', 'consignee.city', 'consignee.province', | |
| 'consignee.zipCode', 'consignee.country', 'customer.mail', | |
| 'details.positionId', 'details.itemNumber', 'details.requestedQt', | |
| 'details.lotId', 'details.stockOwner', 'details.magCli', 'details.availability', | |
| 'details.accountingStatus', 'details.refOrder', | |
| 'noteDdt', 'noteWarehouse', 'noteDelivery', 'noteShipment', | |
| 'reasonCodeShp', 'infoDelivery', 'bookingInfo' | |
| ] | |
| # Assicurati che il DataFrame abbia tutte le colonne nell'ordine desiderato | |
| for col in final_columns_order: | |
| if col not in final_df.columns: | |
| final_df[col] = "" | |
| final_df = final_df[final_columns_order] | |
| # Larghezze delle colonne aggiornate (adattate per Outbound) | |
| col_widths = { | |
| 'siteCode': 10, 'companyCode': 15, 'orderNumber': 15, 'orderType': 10, 'deliveryType': 12, | |
| 'orderDocDate': 12, 'orderDateDetail': 15, 'dueDate': 12, 'reasonCode': 12, # Date solo gg/mm/aaaa | |
| 'service': 10, 'serviceType': 12, 'paymentInfo.value': 15, | |
| 'customer.code': 15, 'customer.name': 30, 'customer.address': 30, 'customer.city': 15, | |
| 'customer.province': 10, 'customer.zipCode': 10, 'customer.country': 10, | |
| 'consignee.name': 30, 'consignee.address': 30, 'consignee.city': 15, 'consignee.province': 10, | |
| 'consignee.zipCode': 10, 'consignee.country': 10, 'customer.mail': 25, | |
| 'details.positionId': 12, 'details.itemNumber': 20, 'details.requestedQt': 15, | |
| 'details.lotId': 10, 'details.stockOwner': 15, 'details.magCli': 15, 'details.availability': 15, | |
| 'details.accountingStatus': 18, 'details.refOrder': 15, | |
| 'noteDdt': 20, 'noteWarehouse': 20, 'noteDelivery': 20, 'noteShipment': 20, | |
| 'reasonCodeShp': 15, 'infoDelivery': 20, 'bookingInfo': 20 | |
| } | |
| # Formato data per le colonne di data (solo data, senza ora) | |
| date_format = workbook.add_format({'num_format': 'dd/mm/yyyy'}) | |
| # Scrivi i nomi delle colonne nella riga 2 (indice 1 in xlsxwriter) | |
| for col_num, col_name in enumerate(final_df.columns.values): | |
| worksheet.write(1, col_num, col_name) | |
| # Imposta le larghezze delle colonne | |
| if col_name in col_widths: | |
| worksheet.set_column(col_num, col_num, col_widths[col_name]) | |
| else: | |
| worksheet.set_column(col_num, col_num, 15) | |
| # Applica il formato data alle colonne 'orderDocDate' e 'dueDate' | |
| if col_name in ['orderDocDate', 'dueDate']: | |
| worksheet.set_column(col_num, col_num, col_widths[col_name], date_format) | |
| # Scrivi i dati a partire dalla riga 3 (indice 2 in xlsxwriter) | |
| final_df.to_excel(writer, sheet_name=sheet_name_excel, index=False, header=False, startrow=2) | |
| writer.close() | |
| output.seek(0) | |
| timestamp = datetime.now().strftime("%Y%m%d_%H%M%S") | |
| output_file_name = f"OutboundOrders_{timestamp}.xlsx" | |
| st.download_button( | |
| label="Scarica file Excel Unificato", | |
| data=output.getvalue(), | |
| file_name=output_file_name, | |
| mime="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" | |
| ) | |
| elif uploaded_json_files and customers_df.empty: | |
| st.warning("Per elaborare i file JSON, devi prima caricare un file Excel con i dettagli dei clienti.") | |
| elif not conversion_successful: | |
| st.error("Nessun file valido è stato elaborato con successo. Controlla i messaggi di errore sopra.") | |
| else: | |
| st.info("Carica i tuoi file JSON per iniziare.") | |
| # --- Funzione per il reset --- | |
| def clear_inputs(): | |
| st.session_state.file_uploader_key_json += 1 | |
| st.session_state.file_uploader_key_customers += 1 | |
| st.markdown("---") | |
| st.button("Pulisci Input (RESET)", on_click=clear_inputs) | |
| st.markdown("Sviluppato con Streamlit e Python.") |