import os import mysql.connector import requests from datetime import datetime from dotenv import load_dotenv from pathlib import Path from typing import Dict, List, Set # Load environment variables from .env.local env_path = Path(__file__).parents[1] / '.env.local' load_dotenv(env_path) def convert_seconds_to_hours(seconds: int) -> float: if not seconds: return 0 return round(seconds / 3600, 2) def get_db_connection(): return mysql.connector.connect( host=os.getenv('DB_HOST'), user=os.getenv('DB_USER'), password=os.getenv('DB_PASSWORD'), database=os.getenv('DB_NAME') ) def sync_project_crew(project_id: int, rentman_id: int, cursor, sync_id: int, api_headers: Dict): print(f"Syncing crew for project {project_id} (Rentman ID: {rentman_id})") try: # First sync the function groups function_groups_response = requests.get( f"{os.getenv('RENTMAN_API_URL')}/projects/{rentman_id}/projectfunctiongroups", headers=api_headers ) if not function_groups_response.ok: raise Exception(f'Failed to fetch function groups from Rentman: {function_groups_response.status_code}') function_groups_data = function_groups_response.json() # Get existing groups cursor.execute( 'SELECT rentman_id FROM ProjectFunctionGroups WHERE project_id = %s', [project_id] ) existing_groups = {row['rentman_id'] for row in cursor.fetchall()} api_group_ids = {g['id'] for g in function_groups_data['data']} groups_to_delete = existing_groups - api_group_ids # Handle group deletions if groups_to_delete: placeholders = ','.join(['%s'] * len(groups_to_delete)) cursor.execute( f'''SELECT DISTINCT function_group_id FROM ProjectCrew WHERE function_group_id IN ( SELECT id FROM ProjectFunctionGroups WHERE rentman_id IN ({placeholders}) AND project_id = %s ) AND ( actual_hours > 0 OR actual_km > 0 OR had_breakfast = 1 OR had_lunch = 1 OR had_dinner = 1 OR declaration_notes IS NOT NULL )''', [*groups_to_delete, project_id] ) groups_in_use = {row['function_group_id'] for row in cursor.fetchall()} safe_to_delete_groups = [gid for gid in groups_to_delete if gid not in groups_in_use] if safe_to_delete_groups: placeholders = ','.join(['%s'] * len(safe_to_delete_groups)) cursor.execute( f'DELETE FROM ProjectFunctionGroups WHERE project_id = %s AND rentman_id IN ({placeholders})', [project_id, *safe_to_delete_groups] ) # Process function groups for group in function_groups_data['data']: cursor.execute( 'SELECT id FROM ProjectFunctionGroups WHERE rentman_id = %s AND project_id = %s', [group['id'], project_id] ) existing_group = cursor.fetchone() # Check if this is an "Offerte" function group is_offerte = False if group.get('subproject'): subproject_id = group['subproject'].split('/')[-1] if subproject_id: try: subproject_response = requests.get( f"{os.getenv('RENTMAN_API_URL')}/subprojects/{subproject_id}", headers=api_headers ) if subproject_response.ok: subproject_data = subproject_response.json() is_offerte = subproject_data['data']['displayname'].startswith('Offerte') except Exception as error: print(f'Error fetching subproject: {error}') if not existing_group: cursor.execute( '''INSERT INTO ProjectFunctionGroups (rentman_id, project_id, displayname, name, planperiod_start, planperiod_end, is_offerte, last_synced) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)''', [ group['id'], project_id, group['displayname'], group['name'], group['planperiod_start'], group['planperiod_end'], is_offerte, sync_id ] ) else: cursor.execute( '''UPDATE ProjectFunctionGroups SET displayname = %s, name = %s, planperiod_start = %s, planperiod_end = %s, is_offerte = %s, last_synced = %s WHERE rentman_id = %s AND project_id = %s''', [ group['displayname'], group['name'], group['planperiod_start'], group['planperiod_end'], is_offerte, sync_id, group['id'], project_id ] ) # Fetch project functions functions_response = requests.get( f"{os.getenv('RENTMAN_API_URL')}/projects/{rentman_id}/projectfunctions", params={'fields': 'id,displayname,cost_rate,price_rate,project,group,name,name_external,subproject,amount'}, headers=api_headers ) if not functions_response.ok: raise Exception(f'Failed to fetch functions from Rentman: {functions_response.status_code}') functions_data = functions_response.json() # Get existing functions cursor.execute( 'SELECT rentman_id FROM ProjectFunctions WHERE project_id = %s', [project_id] ) existing_functions = {row['rentman_id'] for row in cursor.fetchall()} api_function_ids = {f['id'] for f in functions_data['data']} functions_to_delete = existing_functions - api_function_ids # Handle function deletions if functions_to_delete: placeholders = ','.join(['%s'] * len(functions_to_delete)) cursor.execute( f'''SELECT DISTINCT function_id FROM ProjectCrew WHERE function_id IN ( SELECT id FROM ProjectFunctions WHERE rentman_id IN ({placeholders}) AND project_id = %s ) AND ( actual_hours > 0 OR actual_km > 0 OR had_breakfast = 1 OR had_lunch = 1 OR had_dinner = 1 OR declaration_notes IS NOT NULL )''', [*functions_to_delete, project_id] ) functions_in_use = {row['function_id'] for row in cursor.fetchall()} safe_to_delete_functions = [fid for fid in functions_to_delete if fid not in functions_in_use] if safe_to_delete_functions: placeholders = ','.join(['%s'] * len(safe_to_delete_functions)) cursor.execute( f'DELETE FROM ProjectFunctions WHERE project_id = %s AND rentman_id IN ({placeholders})', [project_id, *safe_to_delete_functions] ) # Process functions for func in functions_data['data']: cost_rate_id = int(func['cost_rate'].split('/')[-1]) if func['cost_rate'] else None price_rate_id = int(func['price_rate'].split('/')[-1]) if func['price_rate'] else None # Check if this is an "Offerte" function is_offerte = False if func.get('subproject'): subproject_id = func['subproject'].split('/')[-1] if subproject_id: try: subproject_response = requests.get( f"{os.getenv('RENTMAN_API_URL')}/subprojects/{subproject_id}", headers=api_headers ) if subproject_response.ok: subproject_data = subproject_response.json() is_offerte = subproject_data['data']['displayname'].startswith('Offerte') except Exception as error: print(f'Error fetching subproject: {error}') function_group_id = None if func['group']: group_id = int(func['group'].split('/')[-1]) cursor.execute( 'SELECT id FROM ProjectFunctionGroups WHERE rentman_id = %s AND project_id = %s', [group_id, project_id] ) group_record = cursor.fetchone() if group_record: function_group_id = group_record['id'] cursor.execute( 'SELECT id FROM ProjectFunctions WHERE rentman_id = %s AND project_id = %s', [func['id'], project_id] ) existing_function = cursor.fetchone() if not existing_function: cursor.execute( '''INSERT INTO ProjectFunctions (rentman_id, project_id, displayname, cost_rate_id, price_rate_id, amount, function_group_id, is_offerte, last_synced) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)''', [func['id'], project_id, func['displayname'], cost_rate_id, price_rate_id, func.get('amount', 1), function_group_id, is_offerte, sync_id] ) else: cursor.execute( '''UPDATE ProjectFunctions SET displayname = %s, cost_rate_id = %s, price_rate_id = %s, amount = %s, function_group_id = %s, is_offerte = %s, last_synced = %s WHERE rentman_id = %s AND project_id = %s''', [func['displayname'], cost_rate_id, price_rate_id, func.get('amount', 1), function_group_id, is_offerte, sync_id, func['id'], project_id] ) # Debug logging print(f"Processing function: {func['displayname']} (Offerte: {is_offerte})") print(f"Group data: {{'originalGroup': func.get('group'), 'parsedGroupId': function_group_id, 'projectId': {project_id}}}") # Fetch crew members crew_response = requests.get( f"{os.getenv('RENTMAN_API_URL')}/projects/{rentman_id}/projectcrew", params={'fields': 'id,displayname,crewmember,planperiod_start,planperiod_end,project_leader,hours_planned,hours_registered,function'}, headers=api_headers ) if not crew_response.ok: raise Exception(f'Failed to fetch crew from Rentman: {crew_response.status_code}') crew_data = crew_response.json() # Get existing crew cursor.execute( 'SELECT rentman_id FROM ProjectCrew WHERE project_id = %s', [project_id] ) existing_crew = {row['rentman_id'] for row in cursor.fetchall()} api_crew_ids = {c['id'] for c in crew_data['data']} crew_to_delete = existing_crew - api_crew_ids # Handle crew deletions if crew_to_delete: placeholders = ','.join(['%s'] * len(crew_to_delete)) cursor.execute( f'''DELETE FROM ProjectCrew WHERE project_id = %s AND rentman_id IN ({placeholders}) AND actual_hours = 0 AND actual_km = 0 AND had_breakfast = 0 AND had_lunch = 0 AND had_dinner = 0 AND (declaration_notes IS NULL OR declaration_notes = '')''', [project_id, *crew_to_delete] ) # Process crew members for crew in crew_data['data']: function_id = None function_group_id = None if crew['function']: rentman_function_id = int(crew['function'].split('/')[-1]) cursor.execute( 'SELECT id, function_group_id FROM ProjectFunctions WHERE rentman_id = %s AND project_id = %s', [rentman_function_id, project_id] ) function_record = cursor.fetchone() if function_record: function_id = function_record['id'] function_group_id = function_record['function_group_id'] crew_member_id = int(crew['crewmember'].split('/')[-1]) if crew['crewmember'] else None if not crew_member_id: print(f"Skipping invalid crew entry: {crew['id']}") continue cursor.execute( 'SELECT id FROM ProjectCrew WHERE rentman_id = %s AND project_id = %s', [crew['id'], project_id] ) existing_crew_member = cursor.fetchone() if not existing_crew_member: cursor.execute( '''INSERT INTO ProjectCrew (rentman_id, project_id, crew_rentman_id, function_id, function_group_id, planperiod_start, planperiod_end, hours_planned, last_synced) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)''', [ crew['id'], project_id, crew_member_id, function_id, function_group_id, crew['planperiod_start'], crew['planperiod_end'], convert_seconds_to_hours(crew['hours_planned']), sync_id ] ) else: cursor.execute( '''UPDATE ProjectCrew SET crew_rentman_id = %s, function_id = %s, function_group_id = %s, planperiod_start = %s, planperiod_end = %s, hours_planned = %s, last_synced = %s WHERE rentman_id = %s AND project_id = %s''', [ crew_member_id, function_id, function_group_id, crew['planperiod_start'], crew['planperiod_end'], convert_seconds_to_hours(crew['hours_planned']), sync_id, crew['id'], project_id ] ) return { 'groups_deleted': len(groups_to_delete), 'functions_deleted': len(functions_to_delete), 'crew_deleted': len(crew_to_delete), 'groups_processed': len(function_groups_data['data']), 'functions_processed': len(functions_data['data']), 'crew_processed': len(crew_data['data']) } except Exception as e: print(f"Error syncing project {project_id}: {str(e)}") raise def sync_rentman_crew(): connection = get_db_connection() cursor = connection.cursor(dictionary=True) sync_id = None try: if not os.getenv('RENTMAN_API_KEY'): raise Exception('RENTMAN_API_KEY is not configured') # Begin transaction connection.start_transaction() # Create sync history record cursor.execute( 'INSERT INTO SyncHistory (sync_status) VALUES (%s)', ['SUCCESS'] ) sync_id = cursor.lastrowid # Get all projects that are not "Afgerond" (completed) cursor.execute('SELECT id, rentman_id FROM Projects WHERE rentman_id IS NOT NULL AND status != %s', ['Afgerond']) projects = cursor.fetchall() api_headers = { 'Authorization': f"Bearer {os.getenv('RENTMAN_API_KEY').strip()}", 'Content-Type': 'application/json' } total_stats = { 'groups_deleted': 0, 'functions_deleted': 0, 'crew_deleted': 0, 'groups_processed': 0, 'functions_processed': 0, 'crew_processed': 0 } # Process each project for project in projects: try: stats = sync_project_crew(project['id'], project['rentman_id'], cursor, sync_id, api_headers) for key in total_stats: total_stats[key] += stats[key] except Exception as e: print(f"Failed to sync project {project['id']}: {str(e)}") continue # Update sync history with totals cursor.execute( '''UPDATE SyncHistory SET groups_deleted = %s, functions_deleted = %s, crew_deleted = %s, function_groups_added = %s, functions_added = %s, projects_added = %s WHERE id = %s''', [ total_stats['groups_deleted'], total_stats['functions_deleted'], total_stats['crew_deleted'], total_stats['groups_processed'], total_stats['functions_processed'], total_stats['crew_processed'], sync_id ] ) # Commit transaction connection.commit() print('Sync completed successfully') print('Total statistics:', total_stats) except Exception as error: if connection.in_transaction: connection.rollback() print(f'Sync Error: {str(error)}') raise finally: cursor.close() connection.close() if __name__ == '__main__': try: sync_rentman_crew() except Exception as e: print(f'Script failed: {str(e)}') exit(1)