import pandas as pd import os import logging from datetime import datetime, date logger = logging.getLogger(__name__) def calculate_passenger_type(dob_str): """ Calculate passenger type based on age from date of birth. Standard Airline Age groups: - Infants (INF): Under 2 years (0-23 months) - Children (CHD): 2-11 years - Adults (ADT): 12+ years """ if not dob_str or "•" in dob_str: return "Adult", "ADT" try: # Support multiple formats for parsing dob = None for fmt in ('%d/%m/%Y', '%d/%m/%y', '%d%b%y'): try: dob = datetime.strptime(dob_str, fmt).date() break except: continue if not dob: return "Adult", "ADT" today = date.today() age_years = today.year - dob.year if today.month < dob.month or (today.month == dob.month and today.day < dob.day): age_years -= 1 if age_years < 2: return "Infant", "INF" elif age_years < 12: return "Child", "CHD" else: return "Adult", "ADT" except: return "Adult", "ADT" def _format_mrz_date(raw_date, year_digits=4): """Converts MRZ date (YYMMDD) to DD/MM/YYYY or DD/MM/YY.""" if not raw_date or len(raw_date) != 6: return raw_date try: dt = datetime.strptime(raw_date, '%y%m%d').date() fmt = '%d/%m/%y' if year_digits == 2 else '%d/%m/%Y' return dt.strftime(fmt) except: return raw_date def _to_ddmmmyy(date_str): """Converts various date strings to DDMMMYY format, e.g., 18NOV26.""" if not date_str or "•" in date_str: return date_str try: dt_obj = None for fmt in ('%d/%m/%Y', '%d/%m/%y'): try: dt_obj = datetime.strptime(date_str, fmt) break except: continue if not dt_obj: return date_str day = dt_obj.strftime('%d') month = dt_obj.strftime('%b').upper() year = dt_obj.strftime('%y') return f"{day}{month}{year}" except: return date_str def format_fly_dubai(data_list): """ Formats data for Fly Dubai template (Full details). - Dates: 18NOV26 (DDMMMYY) """ formatted_rows = [] for item in data_list: sex = item.get('sex', '').upper() title = "MR" if sex == 'M' else "MRS" first_middle = item.get('name', '') # Dubai uses DDMMMYY (2-digit year) dob_dubai = _to_ddmmmyy(_format_mrz_date(item.get('date_of_birth', ''), year_digits=2)) expiry_dubai = _to_ddmmmyy(_format_mrz_date(item.get('expiration_date', ''), year_digits=2)) _, ptc_code = calculate_passenger_type(_format_mrz_date(item.get('date_of_birth', ''))) row = { "Last Name": item.get('surname', ''), "First Name and Middle Name": first_middle, "Title": title, "PTC": ptc_code, "Gender": sex, "Date of Birth": dob_dubai, "Passport Last Name": item.get('surname', ''), "Passport First Name": first_middle, "Passport Middle Name": "", "Passport Number": item.get('passport_number', ''), "Passport Nationality": (item.get('nationality') or '')[:3].upper(), "Passport Issue Country": (item.get('country') or '')[:3].upper(), "Passport Expiry Date": expiry_dubai, "Visa Number": "", "Visa Type": "", "Visa Issue Date": "", "Place of Birth": "", "Visa Place of Issue": "", "Visa Country of Application": "", "Address Type": "", "Address Country": "", "Address Details": "", "Address City": "", "Address State": "", "Address Zip Code": "" } formatted_rows.append(row) return pd.DataFrame(formatted_rows) def format_iraqi(data_list): """ Formats data for Iraqi template. Columns: TYPE, TITLE, FIRST NAME, LAST NAME, DOB (DD/MM/YY), GENDER Example: Adult MR FirstNameOne LastNameOne 12/03/26 Male """ formatted_rows = [] for item in data_list: sex = item.get('sex', '').upper() gender_full = "Male" if sex == 'M' else "Female" title = "MR" if sex == 'M' else "MRS" # Iraqi uses DD/MM/YY (2-digit year) dob_iraqi = _format_mrz_date(item.get('date_of_birth', ''), year_digits=2) passenger_type, _ = calculate_passenger_type(_format_mrz_date(item.get('date_of_birth', ''))) row = { "TYPE": passenger_type, "TITLE": title, "FIRST NAME": item.get('name', ''), "LAST NAME": item.get('surname', ''), "DOB (DD/MM/YY)": dob_iraqi, "GENDER": gender_full } formatted_rows.append(row) return pd.DataFrame(formatted_rows) def format_fly_baghdad(data_list): """ Formats data for Fly Baghdad template. - Dates: DD/MM/YYYY """ from config.settings import COUNTRY_CODES country_map = {c['alpha-3']: c['name'].upper() for c in COUNTRY_CODES} formatted_rows = [] for i, item in enumerate(data_list): sex = item.get('sex', '').upper() gender_full = "MALE" if sex == 'M' else "FEMALE" title = "MR" if sex == 'M' else "MRS" dob = _format_mrz_date(item.get('date_of_birth', ''), year_digits=4) expiry = _format_mrz_date(item.get('expiration_date', ''), year_digits=4) _, ptc_code = calculate_passenger_type(dob) nat_code = (item.get('nationality') or item.get('country') or '')[:3].upper() country_code = (item.get('country') or item.get('nationality') or '')[:3].upper() row = { "Sequence": i + 1, "Traveling With": "", "Pax Type": ptc_code, "Title": title, "First Name": item.get('name', ''), "Last Name": item.get('surname', ''), "Gender": gender_full, "DOB (dd/mm/yyyy)": dob, "Nationality": country_map.get(nat_code, nat_code), "Passport Number": item.get('passport_number', ''), "Passport Expiry (dd/mm/yyyy)": expiry, "Passport Issued Country": country_map.get(country_code, country_code) } formatted_rows.append(row) return pd.DataFrame(formatted_rows) def export_to_spreadsheet(data_list, output_file, format='excel'): if not data_list: return False try: df = pd.DataFrame(data_list) os.makedirs(os.path.dirname(output_file), exist_ok=True) if format.lower() == 'excel' or output_file.endswith('.xlsx'): df.to_excel(output_file if output_file.endswith('.xlsx') else output_file + '.xlsx', index=False) else: df.to_csv(output_file if output_file.endswith('.csv') else output_file + '.csv', index=False) return True except: return False