#!/usr/bin/env python3
"""
Comprehensive CSV to Odoo field mapping for employee import
"""
from datetime import datetime
import re
# CSV Headers from your file:
# Joining Date,Name,City,BARQ ID,Status,mobile_number,id_number,Jahez Driver ID,Hunger Rider ID,Mrsool iCourierID,Project,Sponsorshipstatus,Car,plate,last working day,IBAN,Bank Certificate,Supervisor,Iqama copy,Email,Date Of Birth,Nationality,Profession,Iqama Expiry Date,Tshirts recieved,WPS,sponsorship transferred in,Sponsorship Transferred Count,Bank Name,Transfer Fee's,Date Of Transfer Request,Own Car,Own Car Plate,Own Car Make,Own Car Model,Own Car Registration,Passport Number,Passport Expiry Date,National Location Attachment,Contract Expire Date,Official Profile Pic,License Number,License Expiry Date,Name in Arabic,Keeta Courier Id,Hungerstation Courier Id,Promissory Note
# Comprehensive field mapping from CSV to Odoo hr.employee
CSV_TO_ODOO_MAPPING = {
# ===== BASIC INFORMATION =====
'Name': 'name', # Employee Name
'Name in Arabic': None, # Store in notes or custom field
'BARQ ID': 'employee_id', # Employee ID (if exists) or use identification_id
'Status': None, # Used for filtering, not stored in employee record
# ===== PERSONAL INFORMATION =====
'Date Of Birth': 'birthday', # Birthday
'Nationality': 'country_of_birth', # Country of Birth (need to map to country ID)
'id_number': 'identification_id', # Identification No (IQAMA/National ID)
'Passport Number': 'passport_id', # Passport No
'Passport Expiry Date': 'visa_expire', # Visa Expiration Date (closest match)
'Iqama Expiry Date': 'work_permit_expiration_date', # Work Permit Expiration Date
# ===== CONTACT INFORMATION =====
'mobile_number': 'work_phone', # Work Phone
'Email': 'work_email', # Work Email
'City': 'private_city', # Private City
# ===== WORK INFORMATION =====
'Joining Date': 'first_contract_date', # First Contract Date
'Project': 'department_id', # Department (need to map to department ID)
'Profession': 'job_title', # Job Title
'Supervisor': 'parent_id', # Manager (need to map to employee ID)
'last working day': None, # Could be stored in contract end date
'Contract Expire Date': None, # Contract expiration (contract-related)
# ===== VEHICLE INFORMATION =====
'Car': 'vehicle', # Company Vehicle
'plate': 'license_plate', # License Plate
'Own Car': None, # Boolean field for private car ownership
'Own Car Plate': 'private_car_plate', # Private Car Plate
'Own Car Make': None, # Car make (could be stored in notes)
'Own Car Model': None, # Car model (could be stored in notes)
'Own Car Registration': None, # Car registration (could be stored in notes)
'License Number': 'driving_license', # Driving License
'License Expiry Date': None, # License expiry (could be stored in notes)
# ===== FINANCIAL INFORMATION =====
'IBAN': 'bank_account_id', # Bank Account (need to create bank account record)
'Bank Name': None, # Bank name (part of bank account)
'WPS': None, # Wage Protection System (could be stored in notes)
'Transfer Fee\'s': None, # Transfer fees (could be stored in notes)
'Tshirts recieved': None, # T-shirts received (could be stored in notes)
# ===== SPONSORSHIP INFORMATION =====
'Sponsorshipstatus': None, # Sponsorship status (could be stored in notes)
'sponsorship transferred in': None, # Sponsorship transfer date (could be stored in notes)
'Sponsorship Transferred Count': None, # Transfer count (could be stored in notes)
'Date Of Transfer Request': None, # Transfer request date (could be stored in notes)
'Work Permit No': 'permit_no', # Work Permit No
# ===== EXTERNAL PLATFORM IDs =====
'Jahez Driver ID': None, # External platform ID (could be stored in notes)
'Hunger Rider ID': None, # External platform ID (could be stored in notes)
'Mrsool iCourierID': None, # External platform ID (could be stored in notes)
'Keeta Courier Id': None, # External platform ID (could be stored in notes)
'Hungerstation Courier Id': None, # External platform ID (could be stored in notes)
# ===== DOCUMENT ATTACHMENTS =====
'Bank Certificate': None, # File attachment (could be stored as attachment)
'Iqama copy': 'id_card', # ID Card Copy
'National Location Attachment': None, # File attachment
'Official Profile Pic': None, # File attachment (could be stored as image)
'Promissory Note': None, # File attachment
}
def parse_date(date_str):
"""Parse date string in M/D/YYYY format to Odoo format YYYY-MM-DD"""
if not date_str or date_str.strip() == '':
return None
try:
# Handle M/D/YYYY format
dt = datetime.strptime(date_str.strip(), '%m/%d/%Y')
return dt.strftime('%Y-%m-%d')
except ValueError:
try:
# Handle MM/DD/YYYY format
dt = datetime.strptime(date_str.strip(), '%m/%d/%Y')
return dt.strftime('%Y-%m-%d')
except ValueError:
print(f"Warning: Could not parse date '{date_str}'")
return None
def clean_phone(phone):
"""Clean phone number format"""
if not phone:
return None
# Remove any spaces and ensure it starts with +966
phone = phone.strip().replace(' ', '')
if phone.startswith('966'):
phone = '+' + phone
return phone
def clean_identification_id(id_str):
"""Clean identification ID"""
if not id_str:
return None
return id_str.strip()
def map_csv_row_to_odoo_employee(row, client=None):
"""
Map a CSV row to Odoo employee fields
Args:
row: Dictionary containing CSV row data
client: Odoo client for lookups (optional)
Returns:
Dictionary with Odoo field mappings
"""
employee_data = {}
notes_data = [] # For fields that don't have direct mapping
for csv_field, value in row.items():
if not value or value.strip() == '':
continue
odoo_field = CSV_TO_ODOO_MAPPING.get(csv_field)
if odoo_field:
# Direct mapping fields
if csv_field in ['Date Of Birth', 'Joining Date', 'Passport Expiry Date', 'Iqama Expiry Date']:
parsed_date = parse_date(value)
if parsed_date:
employee_data[odoo_field] = parsed_date
elif csv_field == 'mobile_number':
employee_data[odoo_field] = clean_phone(value)
elif csv_field == 'id_number':
employee_data[odoo_field] = clean_identification_id(value)
elif csv_field == 'BARQ ID':
# Store BARQ ID in identification_id if no other ID exists
if 'identification_id' not in employee_data:
employee_data['identification_id'] = value.strip()
else:
employee_data[odoo_field] = value.strip()
else:
# Fields without direct mapping - store in notes
if csv_field not in ['Status']: # Skip Status field
notes_data.append(f"{csv_field}: {value.strip()}")
# Add notes if there are unmapped fields
if notes_data:
employee_data['notes'] = '\n'.join(notes_data)
return employee_data
def get_department_mapping(client):
"""Get mapping of project names to department IDs"""
departments = client.search_read('hr.department', [], ['name'])
dept_mapping = {}
for dept in departments:
dept_mapping[dept['name'].lower()] = dept['id']
return dept_mapping
def get_job_mapping(client):
"""Get mapping of job titles to job position IDs"""
jobs = client.search_read('hr.job', [], ['name'])
job_mapping = {}
for job in jobs:
job_mapping[job['name'].lower()] = job['id']
return job_mapping
def get_country_mapping(client):
"""Get mapping of country names to country IDs"""
countries = client.search_read('res.country', [], ['name', 'code'])
country_mapping = {}
for country in countries:
country_mapping[country['name'].lower()] = country['id']
country_mapping[country['code'].lower()] = country['id']
return country_mapping
# Example usage
if __name__ == '__main__':
# Print the mapping for reference
print("=== CSV TO ODOO FIELD MAPPING ===")
print(f"{'CSV Field':<30} {'Odoo Field':<30} {'Notes'}")
print("=" * 80)
for csv_field, odoo_field in CSV_TO_ODOO_MAPPING.items():
notes = ""
if odoo_field is None:
notes = "No direct mapping - stored in notes"
elif csv_field in ['Project', 'Supervisor', 'Nationality']:
notes = "Requires ID lookup"
elif csv_field in ['IBAN', 'Bank Certificate']:
notes = "Requires related record creation"
print(f"{csv_field:<30} {odoo_field or 'notes':<30} {notes}")
