""" Labels Module - Import Labels Data Functions Handles CSV/Excel upload and processing for order label data """ import csv import json import os import tempfile import logging from datetime import datetime from app.database import get_db logger = logging.getLogger(__name__) def validate_order_row(row_data): """ Validate a single order row for required fields and data types Required fields: comanda_productie, cantitate, descr_com_prod """ errors = [] warnings = [] # Check required fields if not row_data.get('comanda_productie', '').strip(): errors.append("Comanda Productie is required") if not row_data.get('descr_com_prod', '').strip(): errors.append("Descr. Com. Prod is required") # Validate Cantitate (quantity) - must be integer cantitate_str = row_data.get('cantitate', '').strip() if not cantitate_str: errors.append("Cantitate is required") else: try: cantitate = int(float(cantitate_str)) if cantitate <= 0: errors.append("Cantitate must be a positive number") elif cantitate > 999: warnings.append("Cantitate exceeds 999 (will be truncated)") except ValueError: errors.append("Cantitate must be a valid number") # Validate numeric fields (optional but must be valid if provided) for field in ['nr_linie_com_client', 'line_number']: value = row_data.get(field, '').strip() if value: try: num_val = int(value) if num_val < 0: warnings.append(f"{field} should be positive") except ValueError: errors.append(f"{field} must be a valid number") # Validate data_livrare (optional date field) data_livrare = row_data.get('data_livrare', '').strip() if data_livrare: try: date_formats = [ '%Y-%m-%d', # 2024-03-12 '%Y-%m-%d %H:%M:%S', # 2024-03-12 00:00:00 (Excel format) '%d/%m/%Y', # 12/03/2024 '%m/%d/%Y', # 03/12/2024 '%d.%m.%Y' # 12.03.2024 ] for date_format in date_formats: try: datetime.strptime(data_livrare, date_format) break except ValueError: continue else: errors.append("data_livrare must be a valid date") except Exception: errors.append("data_livrare date format error") return errors, warnings def process_csv_file(file_path): """ Process a CSV file and return parsed orders data """ try: orders_data = [] errors = [] warnings = [] with open(file_path, 'r', encoding='utf-8') as csvfile: csv_reader = csv.DictReader(csvfile) for row_num, row in enumerate(csv_reader, start=1): # Normalize column names (remove spaces and special characters) normalized_row = {} for key, value in row.items(): normalized_key = key.strip().lower().replace(' ', '_').replace('.', '') normalized_row[normalized_key] = value # Validate row row_errors, row_warnings = validate_order_row(normalized_row) if row_errors: errors.extend([f"Row {row_num}: {err}" for err in row_errors]) continue if row_warnings: warnings.extend([f"Row {row_num}: {warn}" for warn in row_warnings]) # Extract and clean data try: cantitate = int(float(normalized_row.get('cantitate', 0))) nr_linie = normalized_row.get('nr_linie_com_client', '') nr_linie = int(nr_linie) if nr_linie.strip() else None line_num = normalized_row.get('line_number', '') line_num = int(line_num) if line_num.strip() else None data_livrare = normalized_row.get('data_livrare', '').strip() if data_livrare: # Parse and reformat date date_formats = [ ('%Y-%m-%d', '%Y-%m-%d'), ('%Y-%m-%d %H:%M:%S', '%Y-%m-%d'), ('%d/%m/%Y', '%Y-%m-%d'), ('%m/%d/%Y', '%Y-%m-%d'), ('%d.%m.%Y', '%Y-%m-%d') ] for fmt_in, fmt_out in date_formats: try: parsed_date = datetime.strptime(data_livrare, fmt_in) data_livrare = parsed_date.strftime(fmt_out) break except ValueError: continue else: data_livrare = None order = { 'comanda_productie': normalized_row.get('comanda_productie', '').strip(), 'cod_articol': normalized_row.get('cod_articol', '').strip(), 'descr_com_prod': normalized_row.get('descr_com_prod', '').strip(), 'cantitate': cantitate, 'com_achiz_client': normalized_row.get('com_achiz_client', '').strip(), 'nr_linie_com_client': nr_linie, 'customer_name': normalized_row.get('customer_name', '').strip(), 'customer_article_number': normalized_row.get('customer_article_number', '').strip(), 'open_for_order': normalized_row.get('open_for_order', '').strip(), 'line_number': line_num, 'data_livrare': data_livrare, 'dimensiune': normalized_row.get('dimensiune', '').strip() } orders_data.append(order) except Exception as e: errors.append(f"Row {row_num}: Error processing row - {str(e)}") return orders_data, errors, warnings except Exception as e: logger.error(f"Error processing CSV file: {e}") return [], [f"Error reading CSV file: {str(e)}"], [] def process_excel_file(file_path): """ Process an Excel file and return parsed orders data """ try: import openpyxl orders_data = [] errors = [] warnings = [] workbook = openpyxl.load_workbook(file_path, data_only=True) worksheet = workbook.active # Get headers from first row headers = [] for cell in worksheet[1]: if cell.value: headers.append(str(cell.value).strip().lower().replace(' ', '_').replace('.', '')) else: headers.append('') # Process data rows for row_num, row in enumerate(worksheet.iter_rows(min_row=2, values_only=True), start=2): # Create dictionary for this row row_dict = {} for col_idx, value in enumerate(row): if col_idx < len(headers) and headers[col_idx]: row_dict[headers[col_idx]] = str(value) if value is not None else '' if not row_dict or not any(row_dict.values()): # Skip empty rows continue # Validate row row_errors, row_warnings = validate_order_row(row_dict) if row_errors: errors.extend([f"Row {row_num}: {err}" for err in row_errors]) continue if row_warnings: warnings.extend([f"Row {row_num}: {warn}" for warn in row_warnings]) # Extract and clean data try: cantitate = int(float(row_dict.get('cantitate', 0))) nr_linie = row_dict.get('nr_linie_com_client', '') nr_linie = int(nr_linie) if nr_linie.strip() else None line_num = row_dict.get('line_number', '') line_num = int(line_num) if line_num.strip() else None data_livrare = row_dict.get('data_livrare', '').strip() if data_livrare: # Parse and reformat date date_formats = [ ('%Y-%m-%d', '%Y-%m-%d'), ('%Y-%m-%d %H:%M:%S', '%Y-%m-%d'), ('%d/%m/%Y', '%Y-%m-%d'), ('%m/%d/%Y', '%Y-%m-%d'), ('%d.%m.%Y', '%Y-%m-%d') ] for fmt_in, fmt_out in date_formats: try: parsed_date = datetime.strptime(data_livrare, fmt_in) data_livrare = parsed_date.strftime(fmt_out) break except ValueError: continue else: data_livrare = None order = { 'comanda_productie': row_dict.get('comanda_productie', '').strip(), 'cod_articol': row_dict.get('cod_articol', '').strip(), 'descr_com_prod': row_dict.get('descr_com_prod', '').strip(), 'cantitate': cantitate, 'com_achiz_client': row_dict.get('com_achiz_client', '').strip(), 'nr_linie_com_client': nr_linie, 'customer_name': row_dict.get('customer_name', '').strip(), 'customer_article_number': row_dict.get('customer_article_number', '').strip(), 'open_for_order': row_dict.get('open_for_order', '').strip(), 'line_number': line_num, 'data_livrare': data_livrare, 'dimensiune': row_dict.get('dimensiune', '').strip() } orders_data.append(order) except Exception as e: errors.append(f"Row {row_num}: Error processing row - {str(e)}") workbook.close() return orders_data, errors, warnings except ImportError: return [], ["openpyxl is required for Excel file processing"], [] except Exception as e: logger.error(f"Error processing Excel file: {e}") return [], [f"Error reading Excel file: {str(e)}"], [] def save_orders_to_database(orders_list): """ Save orders to the order_for_labels table Returns tuple of (inserted_count, error_messages) """ try: conn = get_db() cursor = conn.cursor() inserted_count = 0 errors = [] for index, order in enumerate(orders_list): try: cursor.execute(""" INSERT INTO order_for_labels ( comanda_productie, cod_articol, descr_com_prod, cantitate, com_achiz_client, nr_linie_com_client, customer_name, customer_article_number, open_for_order, line_number, data_livrare, dimensiune, printed_labels ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, 0) """, ( order.get('comanda_productie'), order.get('cod_articol'), order.get('descr_com_prod'), order.get('cantitate'), order.get('com_achiz_client'), order.get('nr_linie_com_client'), order.get('customer_name'), order.get('customer_article_number'), order.get('open_for_order'), order.get('line_number'), order.get('data_livrare'), order.get('dimensiune') )) inserted_count += 1 except Exception as e: errors.append(f"Order {order.get('comanda_productie', 'UNKNOWN')}: {str(e)}") conn.commit() cursor.close() logger.info(f"Inserted {inserted_count} orders successfully") return inserted_count, errors except Exception as e: logger.error(f"Error saving orders to database: {e}") return 0, [f"Database error: {str(e)}"]