Scott.Idem/OSIT-AE-API-FastAPI
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
development
OSIT-AE-API-FastAPI/app/routers/event_importing.py
Scott Idem c64c3bc55a fix(importing): normalize non-breaking spaces in CSV datetime fields 
Google Sheets embeds \xa0 (non-breaking space) in 12-hour time values
(e.g. "3:00\xa0PM") and when date/time columns are combined. This caused
MariaDB datetime INSERTs to fail with an OperationalError.

Adds _clean_datetime() which strips \xa0, normalizes whitespace, and
parses common import formats (M/D/YYYY H:MM AM/PM, etc.) into
YYYY-MM-DD HH:MM:SS before the DB write. Applied to all four datetime
fields: session and presentation start/end.

Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
2026-05-15 10:15:13 -04:00

1415 lines
69 KiB
Python
Raw Permalink Blame History

import datetime, json, os, pathlib, pytz, secrets, shutil, time
import pandas, xlrd # qrcode
from fastapi import APIRouter, Body, Depends, File, Header, HTTPException, Path, Query, Response, status, UploadFile
from pydantic import BaseModel, EmailStr, Field
from typing import Dict, List, Optional, Set, Union
from app.lib_general import log, logging, secure_hash_string, common_route_params, Common_Route_Params
from app.config import settings
from app.db_sql import sql_insert, sql_update, sql_insert_or_update, sql_select, sql_delete, redis_lookup_id_random
from app.routers.api_crud import delete_obj_template, get_obj_template, get_obj_li_template, patch_obj_template, post_obj_template
# from app.methods.event_session_methods import create_update_event_session_obj_v4, get_event_session_rec_list, load_event_session_obj, update_event_session_obj
# from app.methods.event_presentation_methods import create_update_event_presentation_obj_v4, get_event_presentation_rec_list, load_event_presentation_obj
# from app.methods.event_presenter_methods import create_update_event_presenter_obj_v4, get_event_presenter_rec_list, load_event_presenter_obj
from app.methods.hosted_file_methods import load_hosted_file_obj, save_file
from app.models.event_models import Event_Base
from app.models.event_location_models import Event_Location_Base
from app.models.event_person_models import Event_Person_Base
from app.models.event_presentation_models import Event_Presentation_Base
from app.models.event_presenter_models import Event_Presenter_Base
from app.models.event_session_models import Event_Session_Base
from app.models.response_models import Resp_Body_Base, mk_resp
router = APIRouter()
def _clean_datetime(value) -> str | None:
"""Normalize datetime strings from CSV imports (handles \xa0 from Excel, 12-hour format)."""
if not value:
return None
cleaned = str(value).replace('\xa0', ' ').strip()
if not cleaned:
return None
for fmt in ('%m/%d/%Y %I:%M %p', '%m/%d/%Y %H:%M', '%Y-%m-%d %H:%M:%S', '%Y-%m-%d %H:%M'):
try:
return datetime.datetime.strptime(cleaned, fmt).strftime('%Y-%m-%d %H:%M:%S')
except ValueError:
continue
return cleaned
# No longer needed? 2024-08-15
# Based on the program import template the clients are given.
# Ideally the import file should only contain records with new External IDs. Old records will be checked and only updated if needed.
# Updated 2021-10-19
# @router.get('/event/importing/create_update_w_external_id', response_model=Resp_Body_Base)
# async def event_importing_create_update_w_external_id(
# response: Response = Response,
# x_account_id: str = Header(...),
# ):
# log.setLevel(logging.DEBUG) # DEBUG, INFO, WARNING, ERROR, EXCEPTION, CRITICAL
# log.debug(locals())
# allow_inserts = False
# allow_updates = False
# account_id = 2 # CMSC 2 8Gfxbxr19Nw
# event_id = 1438 # CMSC 2021 1438 8nMUlA93Ybw
# full_file_path = 'admin/temp/import_event_program_external_id.csv'
# df = pandas.read_csv(full_file_path, na_filter=False, dtype={'external_id': str, 'External ID': str, 'event_presentation_external_id': str, 'event_presentation_code': str, 'event_presentation_sort': int, 'event_presenter_external_id': str, 'event_presenter_code': str, 'event_presenter_designations': str, 'event_presenter_sort': int, 'event_session_external_id': str, 'event_session_code': str, 'event_session_sort': int, 'source_code': str, 'Source ID': str, 'email': str, 'Email Address': str})
# log.debug(df)
# df_dict = df.to_dict(orient='records')
# event_session_data_li = []
# for record in df_dict:
# if event_location_external_id := record.get('event_location_external_id', None): pass
# elif event_location_external_id := record.get('event_location_code', None):
# log.info('No event location external ID was found. Creating one with the location code...')
# else:
# log.info('No event location external ID was found. Creating one with the name...')
# event_location_name = record.get('event_location_name', '')
# event_location_external_id = (str(event_id) + ':' + event_location_name).strip()
# log.debug(f'Event Location External ID: {event_location_external_id}')
# if event_session_external_id := record.get('event_session_external_id', None): pass
# else:
# log.error('No event session external ID was found.')
# continue
# if event_presentation_external_id := record.get('event_presentation_external_id', None): pass
# else:
# log.error('No event presentation external ID was found.')
# continue
# if event_presenter_external_id := record.get('event_presenter_external_id', None): pass
# else:
# log.info('No event presenter external ID was found. Creating one...')
# last_first_name = ', '.join( [record.get('event_presenter_family_name', ''), record.get('event_presenter_given_name', '')] )
# event_presenter_external_id = (event_presentation_external_id + ':' + last_first_name).strip()
# log.debug(f'Event Presenter External ID: {event_presenter_external_id}')
# data = {}
# data['account_id'] = account_id
# data['event_id'] = event_id
# # INSERT or UPDATE event_location
# data['event_location_external_id'] = event_location_external_id
# sql = f"""
# SELECT *
# FROM `v_event_location` AS `event_location`
# WHERE /*event_location.account_id = :account_id
# AND*/ event_location.event_id = :event_id
# AND event_location.external_id = :event_location_external_id
# LIMIT 1;
# """
# event_location_data = {}
# event_location_data['code'] = record.get('event_location_code', '')
# event_location_data['event_id'] = event_id # INSERT and UPDATE the event_id just in case
# event_location_data['name'] = record.get('event_location_name', '')
# event_location_id = None
# if event_location_rec_result := sql_select(data=data, sql=sql):
# # Pull out IDs and UPDATE existing event_location record
# event_location_rec = event_location_rec_result
# event_location_id = event_location_rec.get('event_location_id', None)
# event_location_id_random = event_location_rec.get('event_location_id_random', None)
# event_location_name = event_location_rec.get('name', None)
# log.info(f'Found one record. Event Location ID: {event_location_id_random} Event Location Name: {event_location_name}')
# event_location_data['id'] = event_location_id
# log.debug(event_location_data)
# if allow_updates:
# if event_location_obj_up_result := sql_update(data=event_location_data, table_name='event_location'):
# log.debug(event_location_obj_up_result)
# else:
# log.warning(event_location_obj_up_result)
# pass # Probably nothing to update
# else:
# log.info(f'No record found that needs to be updated. Creating a new event location. Event Location External ID: {event_location_external_id}')
# event_location_data['external_id'] = event_location_external_id # NOTE: This should not change
# log.debug(event_location_data)
# if allow_inserts:
# if event_location_obj_in_result := sql_insert(data=event_location_data, table_name='event_location'):
# log.debug(event_location_obj_in_result)
# event_location_id = event_location_obj_in_result
# else:
# log.warning(event_location_obj_in_result)
# continue # Something unexpected may have happened
# # INSERT or UPDATE event_session
# data['event_session_external_id'] = event_session_external_id
# sql = f"""
# SELECT *
# FROM `v_event_session` AS `event_session`
# WHERE event_session.account_id = :account_id
# AND event_session.event_id = :event_id
# AND event_session.external_id = :event_session_external_id
# LIMIT 1;
# """
# event_session_data = {}
# event_session_data['code'] = record.get('event_session_code', '')
# event_session_data['event_id'] = event_id # INSERT and UPDATE the event_id just in case
# event_session_data['event_location_id'] = event_location_id # INSERT and UPDATE the event_location_id in case the session was moved
# event_session_data['name'] = record.get('event_session_name', '')
# event_session_start_datetime = record.get('event_session_start_date', '') + ' ' + record.get('event_session_start_time', '')
# event_session_data['start_datetime'] = event_session_start_datetime
# event_session_end_datetime = record.get('event_session_end_date', '') + ' ' + record.get('event_session_end_time', '')
# event_session_data['end_datetime'] = event_session_end_datetime
# if event_session_rec_result := sql_select(data=data, sql=sql):
# # Pull out IDs and UPDATE existing event_session record
# event_session_rec = event_session_rec_result
# event_session_id = event_session_rec.get('event_session_id', None)
# event_session_id_random = event_session_rec.get('event_session_id_random', None)
# event_session_name = event_session_rec.get('name', None)
# log.info(f'Found one record. Event Session ID: {event_session_id_random} Event Session Name: {event_session_name}')
# event_session_data['id'] = event_session_id
# log.debug(event_session_data)
# if allow_updates:
# if event_session_obj_up_result := sql_update(data=event_session_data, table_name='event_session'):
# log.debug(event_session_obj_up_result)
# else:
# log.warning(event_session_obj_up_result)
# pass # Probably nothing to update
# else:
# log.info(f'No record found that needs to be updated. Creating a new event session. Event Session External ID: {event_session_external_id}')
# event_session_data['external_id'] = event_session_external_id # NOTE: This should not change
# log.debug(event_session_data)
# if allow_insert:
# if event_session_obj_in_result := sql_insert(data=event_session_data, table_name='event_session'):
# log.debug(event_session_obj_in_result)
# event_session_id = event_session_obj_in_result
# else:
# log.warning(event_session_obj_in_result)
# continue # Something unexpected may have happened
# # INSERT or UPDATE event_presentation
# data['event_presentation_external_id'] = event_presentation_external_id
# sql = f"""
# SELECT *
# FROM `v_event_presentation` AS `event_presentation`
# WHERE event_presentation.event_id = :event_id
# AND event_presentation.external_id = :event_presentation_external_id
# LIMIT 1;
# """
# event_presentation_data = {}
# event_presentation_data['code'] = record.get('event_presentation_code', '')
# event_presentation_data['event_id'] = event_id # INSERT and UPDATE the event_id just in case
# event_presentation_data['event_session_id'] = event_session_id # UPDATE the event_session_id in case the presentation was moved
# event_presentation_data['name'] = record.get('event_presentation_name', '')
# event_presentation_start_datetime = record.get('event_presentation_start_date', '') + ' ' + record.get('event_presentation_start_time', '')
# event_presentation_data['start_datetime'] = event_presentation_start_datetime
# event_presentation_end_datetime = record.get('event_presentation_end_date', '') + ' ' + record.get('event_presentation_end_time', '')
# event_presentation_data['end_datetime'] = event_presentation_end_datetime
# event_presentation_data['sort'] = record.get('event_presentation_sort', '')
# event_presentation_data['notes'] = record.get('event_presentation_notes', '')
# if event_presentation_rec_result := sql_select(data=data, sql=sql):
# # Pull out IDs and UPDATE existing event_presentation record
# event_presentation_rec = event_presentation_rec_result
# event_presentation_id = event_presentation_rec.get('event_presentation_id', None)
# event_presentation_id_random = event_presentation_rec.get('event_presentation_id_random', None)
# event_presentation_name = event_presentation_rec.get('name', None)
# log.info(f'Found one record. Event Presentation ID: {event_presentation_id_random} Event Presentation Name: {event_presentation_name}')
# event_presentation_data['id'] = event_presentation_id
# log.debug(event_presentation_data)
# if allow_updates:
# if event_presentation_obj_up_result := sql_update(data=event_presentation_data, table_name='event_presentation'):
# log.debug(event_presentation_obj_up_result)
# else:
# log.warning(event_presentation_obj_up_result)
# pass # Probably nothing to update
# else:
# log.info(f'No record found that needs to be updated. Creating a new event presentation. Event Presentation External ID: {event_presentation_external_id}')
# event_presentation_data['external_id'] = event_presentation_external_id # NOTE: This should not change
# log.debug(event_presentation_data)
# if allow_inserts:
# if event_presentation_obj_in_result := sql_insert(data=event_presentation_data, table_name='event_presentation'):
# log.debug(event_presentation_obj_in_result)
# event_presentation_id = event_presentation_obj_in_result
# else:
# log.warning(event_presentation_obj_in_result)
# continue # Something unexpected may have happened
# # INSERT or UPDATE event_presenter
# data['event_presenter_external_id'] = event_presenter_external_id
# sql = f"""
# SELECT *
# FROM `v_event_presenter` AS `event_presenter`
# WHERE event_presenter.event_id = :event_id
# AND event_presenter.external_id = :event_presenter_external_id
# LIMIT 1;
# """
# event_presenter_data = {}
# event_presenter_data['event_id'] = event_id # INSERT and UPDATE the event_id just in case
# event_presenter_data['event_session_id'] = event_session_id # UPDATE the event_session_id in case the presentation was moved
# event_presenter_data['event_presentation_id'] = event_presentation_id # UPDATE the event_presentation_id in case the presenter was moved
# event_presenter_data['given_name'] = record.get('event_presenter_given_name', '')
# event_presenter_data['family_name'] = record.get('event_presenter_family_name', '')
# event_presenter_full_name = (event_presenter_data['given_name'] + ' ' + event_presenter_data['family_name']).strip()
# event_presenter_data['designations'] = record.get('event_presenter_designations', '')
# event_presenter_data['email'] = record.get('event_presenter_email', '')
# event_presenter_data['notes'] = record.get('event_presenter_notes', '')
# if event_presenter_rec_result := sql_select(data=data, sql=sql):
# # Pull out IDs and UPDATE existing event_presenter record
# event_presenter_rec = event_presenter_rec_result
# event_presenter_id = event_presenter_rec.get('event_presenter_id', None)
# event_presenter_id_random = event_presenter_rec.get('event_presenter_id_random', None)
# event_presenter_given_name = event_presenter_rec.get('given_name', None)
# log.info(f'Found one record. Event Presenter ID: {event_presenter_id_random} Event Presenter Given Name: {event_presenter_given_name}')
# event_presenter_data['id'] = event_presenter_id
# log.debug(event_presenter_data)
# if allow_updates:
# if event_presenter_obj_up_result := sql_update(data=event_presenter_data, table_name='event_presenter'):
# log.debug(event_presenter_obj_up_result)
# else:
# log.warning(event_presenter_obj_up_result)
# pass # Probably nothing to update
# else:
# log.info(f'No record found that needs to be updated. Creating a new event presenter. Event Presenter External ID: {event_presenter_external_id}')
# event_presenter_data['external_id'] = event_presenter_external_id # NOTE: This should not change
# log.debug(event_presenter_data)
# if allow_inserts:
# if event_presenter_obj_in_result := sql_insert(data=event_presenter_data, table_name='event_presenter'):
# log.debug(event_presenter_obj_in_result)
# event_presenter_id = event_presenter_obj_in_result
# else:
# log.warning(event_presenter_obj_in_result)
# continue # Something unexpected may have happened
# event_session_data_li.append(data)
# log.debug(f'Record processed: {event_session_id} {event_presentation_id} {event_presenter_full_name}')
# # End of loop
# return mk_resp(data=event_session_data_li, response=response)
# ### BEGIN ### Event Importing ### event_importing_program_data() ###
# Based on the program import template the clients are given.
# Create and update locations, sessions, presentations, and presenters as needed.
# Careful with how date and time fields are combined
# This should work: =TEXT(G2,"M/D/YYYY")&" "&TEXT(H2,"H:MM AM/PM")
# Simply adding the fields (=D264+E264) sort of works. This produces non breaking spaces but clean up on import.
# Updated 2026-05-15
@router.post('/event/{event_id}/importing/program_data', response_model=Resp_Body_Base)
async def event_importing_program_data(
event_id: str = Path(min_length=11, max_length=22),
file: UploadFile = File(...),
begin_at: int = 0,
end_at: int = 20000,
allow_update_event_session: bool = True,
link_sync_person: bool = False,
commons: Common_Route_Params = Depends(common_route_params),
):
log.setLevel(logging.INFO) # DEBUG, INFO, WARNING, ERROR, EXCEPTION, CRITICAL
log.debug(locals())
allow_inserts = True
allow_updates = True
account_id = commons.x_account_id
event_location_id = None
event_session_id = None
event_presentation_id = None
event_presenter_id = None
if event_id := redis_lookup_id_random(record_id_random=event_id, table_name='event'): pass
else: return mk_resp(data=None, status_code=404, response=commons.response)
link_to_type = 'event'
link_to_id = event_id
file_info = await save_file(
file = file,
account_id = account_id,
# account_id_random = account_id_random,
link_to_type = link_to_type,
link_to_id = link_to_id,
# link_to_id_random = link_to_id_random,
# check_allowed_extension = check_allowed_extension,
)
if file_info['saved']:
log.info('File saved')
log.debug(file_info)
else:
log.error('Something may have gone wrong while saving the uploaded file?')
return mk_resp(data=None, status_code=500, response=commons.response)
# log.setLevel(logging.DEBUG) # DEBUG, INFO, WARNING, ERROR, EXCEPTION, CRITICAL
hosted_files_path = settings.FILES_PATH['hosted_files_root']
# hosted_files_path = '/home/scott/tmp/hosted_files_dev/'
log.info(f'Hosted Files Path: {hosted_files_path}')
log.debug(shutil.disk_usage(hosted_files_path))
# full_file_path = 'admin/temp/import_event_program_external_id.csv'
subdirectory_dest = os.path.join(hosted_files_path, file_info.get('subdirectory_path'))
log.debug(subdirectory_dest)
hash_filename = file_info.get('hash_sha256')+'.file'
full_file_path = pathlib.Path( os.path.join(subdirectory_dest, hash_filename) ) # NOTE: Must use pathlib.Path to use .exists()
log.debug(full_file_path)
if full_file_path.exists():
log.info(f'Full File Path: {full_file_path}')
else:
log.warning(f'Not found at full File Path: {full_file_path}')
return mk_resp(data=None, status_code=500, response=commons.response)
# return mk_resp(data=file_info, response=commons.response)
df = pandas.read_csv(
full_file_path,
na_filter=False,
dtype={
# Location related fields:
'event_location_external_id': str, 'event_location_code': str, 'event_location_sort': int,
'location_external_id': str, 'location_code': str, 'location_code_(id)': str, 'location_sort': int,
# Presentation related fields:
'event_presentation_external_id': str, 'event_presentation_code': str, 'event_presentation_sort': int,
'presentation_external_id': str, 'presentation_code': str, 'presentation_code_(id)': str, 'presentation_sort': int,
# Presenter related fields:
'event_presenter_external_id': str, 'event_presenter_code': str, 'event_presenter_number': int, 'event_presenter_designations': str, 'event_presenter_sort': int,
'presenter_external_id': str, 'presenter_code': str, 'presenter_code_(id)': str, 'presenter_number': int, 'presenter_designations': str, 'presenter_sort': int, 'presenter_given_name': str,
'email': str, 'Email Address': str,
# Session related fields:
'event_session_external_id': str, 'event_session_code': str, 'event_session_sort': int,
'session_external_id': str, 'session_code': str, 'session_sort': int,
# 'external_id': str, 'External ID': str,
# 'source_code': str, 'Source ID': str,
}
)
df.rename(columns={
'Title': 'presenter_title_names',
'Title Names': 'presenter_title_names',
'Prefix': 'presenter_title_names',
'Informal_name': 'presenter_informal_name',
'Nickname': 'presenter_informal_name',
'given_name': 'presenter_given_name',
'given_name_(first)': 'presenter_given_name',
'given_name (first)': 'presenter_given_name',
'Given Name': 'presenter_given_name',
'First Name': 'presenter_given_name',
'Middle Name': 'presenter_middle_name',
'family_name': 'presenter_family_name',
'family_name (last)': 'presenter_family_name',
'Family Name': 'presenter_family_name',
'Last Name': 'presenter_family_name',
'family_name_(last)': 'presenter_family_name',
'Designations': 'presenter_designations',
'Suffix': 'presenter_designations',
'Professional Title': 'presenter_professional_title',
'affiliations': 'presenter_presenter_affiliations',
'Affiliations': 'presenter_presenter_affiliations',
'Company': 'presenter_presenter_affiliations',
'email': 'presenter_email',
'Email': 'presenter_email',
'Email Address': 'presenter_email',
'CC Email Address': 'presenter_cc_email',
'Location Code': 'location_code',
'session_location': 'location_code',
'Session Location': 'location_code',
'location_title': 'location_name',
'Location Name': 'location_name',
'location_name_(room)': 'location_name',
'session_location_name_(room)': 'location_name',
# 'session_location': 'location_name',
'session_name_(title)': 'session_name',
'session_title': 'session_name',
'Session Code': 'session_code',
'session_code_(id)': 'session_code',
'Session Name': 'session_name',
'session_type_code_(id)': 'session_type_code',
'Session Type': 'session_type',
'Session Hide': 'hide_session',
'presentation_code_(id)': 'presentation_code',
'abstract_code': 'presentation_abstract_code',
'presentation_abstract_code_(id)': 'presentation_abstract_code',
'presentation_title': 'presentation_name',
'presentation_name_(title)': 'presentation_name',
'Presentation Code': 'presentation_code',
'presenter_code_(id)': 'presenter_code',
'Presentation Name': 'presentation_name',
'Presenter Code': 'presenter_code',
'Presenter Number': 'presenter_number', # for sorting
'Presenter Name': 'presenter_name',
'Presenter Notes': 'presenter_notes',
'Notes': 'presenter_notes',
# 'Source ID': 'source_code',
# 'External ID': 'external_id',
},
inplace = True)
# log.setLevel(logging.DEBUG) # DEBUG, INFO, WARNING, ERROR, EXCEPTION, CRITICAL
log.debug(df)
df_dict = df.to_dict(orient='records')
loop_count = 0
event_program_data_li = []
# log.setLevel(logging.DEBUG) # DEBUG, INFO, WARNING, ERROR, EXCEPTION, CRITICAL
for record in df_dict:
# ### Figure out the external IDs
log.info(f'Loop Count: {loop_count}')
loop_count = loop_count + 1
if loop_count <= begin_at: continue
if loop_count > end_at: break
if event_location_external_id := record.get('location_external_id'):
log.info('Using location_external_id for event_location_external_id')
elif event_location_external_id := record.get('location_code', '').strip():
log.info('Using location_code for event_location_external_id')
else:
log.warning('No location external ID or code was found. Creating one with the name...')
event_location_name = record.get('location_name', '').strip()
# event_location_code = event_location_name
event_location_external_id = f'{event_location_name}' # .strip()
log.debug(f'Event Location External ID: {event_location_external_id}')
if event_session_external_id := record.get('session_external_id'):
log.info('Using session_external_id for event_session_external_id')
elif event_session_external_id := record.get('session_code').strip():
log.info('Using session_code for event_session_external_id')
else:
log.warning('No session external ID or code was found. Creating one with the name...')
event_session_name = record.get('session_name', '').strip()
event_session_external_id = f'{event_session_name}' # .strip()
log.debug(f'Event Session External ID: {event_session_external_id}')
if event_presentation_external_id := record.get('presentation_external_id'):
log.info('Using presentation_external_id for event_presentation_external_id')
elif event_presentation_external_id := record.get('presentation_code', '').strip():
log.info('Using presentation_code for event_presentation_external_id')
else:
log.warning('No presentation external ID or code was found. Creating one with the name...')
event_presentation_name = record.get('presentation_name', '').strip()
event_presentation_external_id = f'{event_session_external_id}:{event_presentation_name}' # .strip()
log.debug(f'Event Presentation External ID: {event_presentation_external_id}')
if event_presenter_external_id := record.get('presenter_external_id'):
log.info('Using presenter_external_id for event_presenter_external_id')
elif event_presenter_external_id := record.get('presenter_code', '').strip():
log.info('Using presenter_code for event_presenter_external_id')
elif event_presenter_external_id := record.get('presenter_number'):
log.info('Using presenter_number for event_presenter_external_id')
else:
log.warning('No presenter external ID or code was found. Creating one with their family and given names...')
event_presenter_last_first_name = ', '.join( [record.get('presenter_family_name', '').strip(), record.get('presenter_given_name', '').strip()] )
# event_presenter_name = record.get('presenter_name', '').strip()
event_presenter_external_id = f'{event_presentation_external_id}:{event_presenter_last_first_name}' # .strip()
log.debug(f'Event Presenter External ID: {event_presenter_external_id}')
log.info(f'Event Location External ID: {event_location_external_id} | Event Session External ID: {event_session_external_id} | Event Presentation External ID: {event_presentation_external_id} | Event Presenter External ID: {event_presenter_external_id}')
# continue
data = {}
data['account_id'] = account_id
data['event_id'] = event_id
# ### INSERT or UPDATE event_location
# NOTE: In the future it may make more sense to pull this location part out and or add a checked ID array so we can avoid SQL queries.
data['event_location_external_id'] = event_location_external_id
sql = f"""
SELECT *
FROM `v_event_location` AS `event_location`
WHERE event_location.event_id = :event_id
AND event_location.external_id = :event_location_external_id
LIMIT 1;
"""
event_location_data = {}
if record.get('location_code'):
event_location_data['code'] = record.get('location_code', '').strip()
elif record.get('location_name'):
event_location_data['code'] = record.get('location_name', '').strip()
event_location_data['event_id'] = event_id # INSERT and UPDATE the event_id just in case
if record.get('location_name'):
event_location_data['name'] = record.get('location_name', '').strip()
elif record.get('location_code'):
event_location_data['name'] = record.get('location_code', '').strip()
event_location_data['enable'] = True
event_location_id = None
if event_location_rec_result := sql_select(data=data, sql=sql):
# Pull out IDs and UPDATE existing event_location record
event_location_rec = event_location_rec_result
event_location_id = event_location_rec.get('event_location_id')
event_location_id_random = event_location_rec.get('event_location_id_random')
event_location_name = event_location_data.get('name')
log.info(f'Found one record. Event Location ID: {event_location_id_random} Event Location Name: {event_location_name}')
event_location_data['id'] = event_location_id
log.debug(event_location_data)
if allow_updates:
if event_location_obj_up_result := sql_update(data=event_location_data, table_name='event_location'):
log.debug(event_location_obj_up_result)
else:
log.warning(event_location_obj_up_result)
pass # Probably nothing to update
else:
log.info(f'No record found that needs to be updated. Creating a new event location. Event Location External ID: {event_location_external_id}')
event_location_data['external_id'] = event_location_external_id # NOTE: This should never change
log.debug(event_location_data)
if allow_inserts:
if event_location_obj_in_result := sql_insert(data=event_location_data, table_name='event_location'):
log.debug(event_location_obj_in_result)
event_location_id = event_location_obj_in_result
else:
log.warning(event_location_obj_in_result)
continue # Something unexpected may have happened
data['event_location_id'] = event_location_id
# ### INSERT or UPDATE event_session
data['event_session_external_id'] = event_session_external_id
sql = f"""
SELECT *
FROM `v_event_session` AS `event_session`
WHERE event_session.event_id = :event_id
AND event_session.external_id = :event_session_external_id
LIMIT 1;
"""
event_session_data = {}
if record.get('session_code'):
event_session_data['code'] = record.get('session_code', '').strip()
else:
event_session_data['code'] = None
event_session_data['event_id'] = event_id # INSERT and UPDATE the event_id just in case
event_session_data['event_location_id'] = event_location_id # INSERT and UPDATE the event_location_id in case the session was moved
if record.get('session_name'):
event_session_data['name'] = record.get('session_name', '').strip()
else:
event_session_data['name'] = None
# Max length of type_code is 25 characters. We must truncate it.
if record.get('session_type_code'):
event_session_data['type_code'] = record.get('session_type_code', '').strip()[:25]
elif record.get('session_type'):
event_session_data['type_code'] = record.get('session_type', '').strip()[:25]
if record.get('session_description'):
event_session_data['description'] = record.get('session_description', '').strip()
event_session_data['start_datetime'] = _clean_datetime(record.get('session_start_datetime'))
event_session_data['end_datetime'] = _clean_datetime(record.get('session_end_datetime'))
event_session_data['sort'] = record.get('session_sort')
if record.get('hide_session') in (True, 1, 'yes', 'Yes', 'YES'):
event_session_data['hide'] = True
event_session_data['enable'] = True
if record.get('session_notes'):
event_session_data['notes'] = record.get('session_notes', '').strip()
event_session_id = None
if event_session_rec_result := sql_select(data=data, sql=sql):
# Pull out IDs and UPDATE existing event_session record
event_session_rec = event_session_rec_result
event_session_id = event_session_rec.get('event_session_id')
event_session_id_random = event_session_rec.get('event_session_id_random')
event_session_name = event_session_rec.get('name')
log.info(f'Found one record. Event Session ID: {event_session_id_random} Event Session Name: {event_session_name}')
event_session_data['id'] = event_session_id
log.debug(event_session_data)
if allow_updates and allow_update_event_session:
if event_session_obj_up_result := sql_update(data=event_session_data, table_name='event_session'):
log.debug(event_session_obj_up_result)
else:
log.warning(event_session_obj_up_result)
pass # Probably nothing to update
else:
log.info(f'No record found that needs to be updated. Creating a new event session. Event Session External ID: {event_session_external_id}')
event_session_data['external_id'] = event_session_external_id # NOTE: This should never change
log.debug(event_session_data)
if allow_inserts:
if event_session_obj_in_result := sql_insert(data=event_session_data, table_name='event_session'):
log.debug(event_session_obj_in_result)
event_session_id = event_session_obj_in_result
else:
log.warning(event_session_obj_in_result)
continue # Something unexpected may have happened
data['event_session_id'] = event_session_id
# INSERT or UPDATE event_presentation
data['event_presentation_external_id'] = event_presentation_external_id
sql = f"""
SELECT *
FROM `v_event_presentation` AS `event_presentation`
WHERE event_presentation.event_session_id = :event_session_id
AND event_presentation.external_id = :event_presentation_external_id
LIMIT 1;
"""
event_presentation_data = {}
if record.get('presentation_code'):
event_presentation_data['code'] = record.get('presentation_code', '').strip()
else:
event_presentation_data['code'] = None
event_presentation_data['event_id'] = event_id # INSERT and UPDATE the event_id just in case
event_presentation_data['event_session_id'] = event_session_id # UPDATE the event_session_id in case the presentation was moved
if record.get('presentation_name'):
event_presentation_data['name'] = record.get('presentation_name', '').strip()
else:
event_presentation_data['name'] = None
if record.get('presentation_description'):
event_presentation_data['description'] = record.get('presentation_description', '').strip()
event_presentation_data['start_datetime'] = _clean_datetime(record.get('presentation_start_datetime'))
data['presentation_start_datetime'] = event_presentation_data['start_datetime']
event_presentation_data['end_datetime'] = _clean_datetime(record.get('presentation_end_datetime'))
data['presentation_end_datetime'] = event_presentation_data['end_datetime']
if record.get('presentation_abstract_code'):
event_presentation_data['abstract_code'] = record.get('presentation_abstract_code', '').strip()
else:
event_presentation_data['abstract_code'] = None
event_presentation_data['sort'] = record.get('presentation_sort')
event_presentation_data['enable'] = True
if record.get('presentation_notes'):
event_presentation_data['notes'] = record.get('presentation_notes', '').strip()
event_presentation_id = None
if (record.get('presentation_external_id') or record.get('presentation_code')) and record.get('presentation_name'):
if event_presentation_rec_result := sql_select(data=data, sql=sql):
# Pull out IDs and UPDATE existing event_presentation record
event_presentation_rec = event_presentation_rec_result
event_presentation_id = event_presentation_rec.get('event_presentation_id', None)
event_presentation_id_random = event_presentation_rec.get('event_presentation_id_random', None)
event_presentation_name = event_presentation_rec.get('name', None)
log.info(f'Found one record. Event Presentation ID: {event_presentation_id_random} Event Presentation Name: {event_presentation_name}')
event_presentation_data['id'] = event_presentation_id
log.debug(event_presentation_data)
if allow_updates:
if event_presentation_obj_up_result := sql_update(data=event_presentation_data, table_name='event_presentation'):
log.debug(event_presentation_obj_up_result)
else:
log.warning(event_presentation_obj_up_result)
pass # Probably nothing to update
else:
log.info(f'No record found that needs to be updated. Creating a new event presentation. Event Presentation External ID: {event_presentation_external_id}')
event_presentation_data['external_id'] = event_presentation_external_id # NOTE: This should never change
log.debug(event_presentation_data)
if allow_inserts:
if event_presentation_obj_in_result := sql_insert(data=event_presentation_data, table_name='event_presentation'):
log.debug(event_presentation_obj_in_result)
event_presentation_id = event_presentation_obj_in_result
else:
log.warning(event_presentation_obj_in_result)
continue # Something unexpected may have happened
data['event_presentation_id'] = event_presentation_id
# INSERT or UPDATE event_presenter
# Handle a semicolon delimited list of presenters in a single cell
if presenter_full_name_li := record.get('presenter_full_name_li', None):
log.setLevel(logging.INFO) # DEBUG, INFO, WARNING, ERROR, EXCEPTION, CRITICAL
log.debug(f'Found presenter list {presenter_full_name_li}')
# Split the list and trim whitespace
presenter_full_name_li = [x.strip() for x in presenter_full_name_li.split(';')] # Split on semicolon
event_presenter_li = []
for presenter_full_name in presenter_full_name_li:
event_presenter_record_results = process_event_presenter_w_full_name(
account_id = account_id,
event_id = event_id,
event_session_id = event_session_id,
event_presentation_id = event_presentation_id, presenter_full_name = presenter_full_name
)
presentation_summary_data = event_presenter_record_results
event_presenter_li.append(presentation_summary_data)
data['event_presenter_li'] = event_presenter_li
# data['event_presenter_full_name_li'] = presenter_full_name_li
# End of the loop. Append to program list data results.
event_program_data_li.append(data)
log.info(f'Record processed: Session: {event_session_id} Presentation: {event_presentation_id} Presenter list: {presenter_full_name_li}')
# Handle a single presenter per row
else:
data['event_presenter_external_id'] = event_presenter_external_id
sql = f"""
SELECT *
FROM `v_event_presenter` AS `event_presenter`
WHERE event_presenter.event_presentation_id = :event_presentation_id
AND event_presenter.external_id = :event_presenter_external_id
LIMIT 1;
"""
event_presenter_data = {}
event_presenter_data['event_id'] = event_id # INSERT and UPDATE the event_id just in case
event_presenter_data['event_session_id'] = event_session_id # UPDATE the event_session_id in case the presentation was moved
event_presenter_data['event_presentation_id'] = event_presentation_id # UPDATE the event_presentation_id in case the presenter was moved
if event_presenter_code := record.get('presenter_code', '').strip():
event_presenter_data['code'] = event_presenter_code
elif event_presenter_code := record.get('presenter_number'):
event_presenter_data['code'] = event_presenter_code
if event_presenter_number := record.get('presenter_number'):
event_presenter_data['sort'] = event_presenter_number
elif event_presenter_number := isinstance(record.get('presenter_code'), int):
event_presenter_data['sort'] = event_presenter_number
event_presenter_data['title_names'] = record.get('presenter_title_names', '').strip()
event_presenter_data['informal_name'] = record.get('presenter_informal_name', '').strip()
event_presenter_data['given_name'] = record.get('presenter_given_name', '').strip()
event_presenter_data['middle_name'] = record.get('presenter_middle_name', '').strip()
event_presenter_data['family_name'] = record.get('presenter_family_name', '').strip()
event_presenter_full_name = (event_presenter_data['given_name'] + ' ' + event_presenter_data['family_name']).strip()
event_presenter_data['designations'] = record.get('presenter_designations', '').strip()
event_presenter_data['professional_title'] = record.get('presenter_professional_title', '').strip()
event_presenter_data['affiliations'] = record.get('presenter_affiliations', '').strip()
event_presenter_data['email'] = record.get('presenter_email', '').strip()
event_presenter_data['enable'] = True
event_presenter_data['notes'] = record.get('presenter_notes', '').strip()
event_presenter_id = None
if (record.get('presenter_code') or record.get('presenter_number')) and record.get('presenter_given_name'):
if event_presenter_rec_result := sql_select(data=data, sql=sql):
# Pull out IDs and UPDATE existing event_presenter record
event_presenter_rec = event_presenter_rec_result
event_presenter_id = event_presenter_rec.get('event_presenter_id')
event_presenter_id_random = event_presenter_rec.get('event_presenter_id_random')
event_presenter_given_name = event_presenter_rec.get('given_name')
log.info(f'Found one record. Event Presenter ID: {event_presenter_id_random} Event Presenter Given Name: {event_presenter_given_name}')
event_presenter_data['id'] = event_presenter_id
log.debug(event_presenter_data)
if allow_updates:
if event_presenter_obj_up_result := sql_update(data=event_presenter_data, table_name='event_presenter'):
log.debug(event_presenter_obj_up_result)
else:
log.warning(event_presenter_obj_up_result)
pass # Probably nothing to update
else:
log.info(f'No record found that needs to be updated. Creating a new event presenter. Event Presenter External ID: {event_presenter_external_id}')
event_presenter_data['external_id'] = event_presenter_external_id # NOTE: This should never change
log.debug(event_presenter_data)
if allow_inserts:
if event_presenter_obj_in_result := sql_insert(data=event_presenter_data, table_name='event_presenter'):
log.debug(event_presenter_obj_in_result)
event_presenter_id = event_presenter_obj_in_result
else:
log.warning(event_presenter_obj_in_result)
continue # Something unexpected may have happened
data['event_presenter_id'] = event_presenter_id
data['event_presenter_code'] = event_presenter_code
data['event_presenter_number'] = event_presenter_number
data['event_presenter_full_name'] = event_presenter_full_name
if link_sync_person:
event_presenter_record_results = process_event_presenter_w_full_name(
account_id = account_id,
event_id = event_id,
event_session_id = event_session_id,
event_presentation_id = event_presentation_id, presenter_full_name = event_presenter_full_name,
event_presenter_id = event_presenter_id,
# sync_external_id = False, # WARNING: True can break future import updates.
)
presentation_summary_data = event_presenter_record_results
data['person_link_sync_summary'] = presentation_summary_data
# event_presenter_li.append(presentation_summary_data)
# End of the loop. Append to program list data results.
event_program_data_li.append(data)
log.info(f'Record processed: Session: {event_session_id} Presentation: {event_presentation_id} Presenter: {event_presenter_full_name}')
return mk_resp(data=event_program_data_li, response=commons.response)
# ### END ### Event Importing ### event_importing_program_data() ###
# ### BEGIN ### Event Importing ### pre_program_person_data_import() ###
# This is the new import endpoint for pre-program person data. Currently this is intended for use with LCI.
# Required fields: account_id (from commons header), event_id, file
# Updated 2025-06-13
@router.post('/event/{event_id}/importing/pre_program_person_data', response_model=Resp_Body_Base)
async def pre_program_data_import(
event_id: str = Path(min_length=11, max_length=22),
file: UploadFile = File(...),
begin_at: int = 0,
end_at: int = 20000,
commons: Common_Route_Params = Depends(common_route_params),
):
log.setLevel(logging.INFO)
account_id = commons.x_account_id
file_info = await save_file(
file = file,
account_id = account_id,
link_to_type = 'event',
link_to_id = event_id,
)
if file_info['saved']:
log.info('File saved')
log.debug(file_info)
else:
log.error('Something may have gone wrong while saving the uploaded file?')
return mk_resp(data=None, status_code=500, response=commons.response)
hosted_files_path = settings.FILES_PATH['hosted_files_root']
log.info(f'Hosted Files Path: {hosted_files_path}')
log.debug(shutil.disk_usage(hosted_files_path))
subdirectory_dest = os.path.join(hosted_files_path, file_info.get('subdirectory_path'))
log.debug(subdirectory_dest)
hash_filename = file_info.get('hash_sha256')+'.file'
full_file_path = pathlib.Path( os.path.join(subdirectory_dest, hash_filename) )
log.debug(full_file_path)
if full_file_path.exists():
log.info(f'Full File Path: {full_file_path}')
else:
log.warning(f'Not found at full File Path: {full_file_path}')
return mk_resp(data=None, status_code=500, response=commons.response)
df = pandas.read_csv(
full_file_path,
na_filter=False,
dtype={
'external_id': str, 'External ID': str,
'source_code': str, 'Source ID': str,
'email': str, 'Email Address': str,
'given_name': str, 'Given Name': str, 'First Name': str,
'given_name_[first]' : str,
'given_name_(first)': str,
'middle_name': str, 'Middle Name': str,
'family_name': str, 'Family Name': str, 'Last Name': str,
'family_name_[last]': str,
'family_name_(last)': str,
'title_names': str, 'Title Names': str, 'Prefix': str,
'informal_name': str, 'Nickname': str,
'professional_title': str, 'Professional Title': str,
'affiliations': str, 'Affiliations': str, 'Company': str,
'affiliation': str, 'Affiliation': str,
'passcode': str, 'Passcode': str,
'notes': str, 'Notes': str,
'data_json': str,
}
)
df.rename(columns={
'Source ID': 'source_code',
'External ID': 'external_id',
'Email': 'email',
'Email Address': 'email',
'Given Name': 'given_name',
'given_name_[first]': 'given_name',
'given_name_(first)': 'given_name',
'First Name': 'given_name',
'Middle Name': 'middle_name',
'Family Name': 'family_name',
'Last Name': 'family_name',
'family_name_[last]': 'family_name',
'family_name_(last)': 'family_name',
'Title Names': 'title_names',
'Prefix': 'title_names',
'Nickname': 'informal_name',
'Professional Title': 'professional_title',
'Affiliations': 'affiliations',
'Affiliation': 'affiliations',
'affiliation': 'affiliations',
'Company': 'affiliations',
'Passcode': 'passcode',
'Notes': 'notes',
},
inplace = True)
log.debug(df)
df_dict = df.to_dict(orient='records')
loop_count = 0
person_data_li = []
for record in df_dict:
log.info(f'Loop Count: {loop_count}')
loop_count = loop_count + 1
if loop_count <= begin_at: continue
if loop_count > end_at: break
log.debug(record)
source_code = 'iMIS:CSV'
person_record_results = process_person_data(account_id, source_code, record)
log.info(f'Record processed: {person_record_results}')
person_data_li.append(person_record_results)
return mk_resp(data=person_data_li, response=commons.response)
# ### END ### Event Importing ### pre_program_person_data_import() ###
# ### BEGIN ### Event Presenter Person Link ### presenter_person_link() ###
# This is the new import endpoint to run through the presenters for an event, check if they have a person_id, and if not then try to find a person to link to based on their full_name. Currently this is intended for use with LCI to link their presenters with their iMIS IDs.
# Required fields: account_id (from commons header), event_id, begin_at, end_at, link_person, sync_person, sync_external_id
# Updated 2025-06-24
@router.get('/event/{event_id}/presenter/link_sync_person', response_model=Resp_Body_Base)
async def presenter_person_link(
event_id: str = Path(min_length=11, max_length=22),
begin_at: int = 0,
end_at: int = 20000,
link_person: bool = True, # At least one of these should be True
sync_person: bool = True, # At least one of these should be True
sync_external_id: bool = False, # Changing the external_id can break future imports
commons: Common_Route_Params = Depends(common_route_params),
):
log.setLevel(logging.INFO)
log.debug(locals())
account_id = commons.x_account_id
if event_id := redis_lookup_id_random(record_id_random=event_id, table_name='event'): pass
else: return mk_resp(data=None, status_code=404, response=commons.response)
if not link_person and not sync_person:
log.error('At least one of link_person or sync_person must be True.')
return mk_resp(data=None, status_code=400, response=commons.response)
# Get the event presenter list
event_presenter_li_data = sql_select(table_name='v_event_presenter', field_name='event_id', field_value=event_id)
if not isinstance(event_presenter_li_data, list):
log.error('Something unexpected may have happened while getting the event presenter list.')
log.error(event_presenter_li_data)
return mk_resp(data=None, status_code=500, response=commons.response)
if not event_presenter_li_data:
log.info('No event presenters found for this event.')
return mk_resp(data=None, status_code=404, response=commons.response)
log.info(f'Found {len(event_presenter_li_data)} event presenters for this event.')
log.debug(event_presenter_li_data)
loop_count = 0
event_presenter_summary_data_li = []
for event_presenter_data in event_presenter_li_data:
log.info(f'Loop Count: {loop_count}')
loop_count = loop_count + 1
if loop_count <= begin_at: continue
if loop_count > end_at: break
log.debug(event_presenter_data)
event_presenter_id = event_presenter_data.get('event_presenter_id', None)
event_session_id = event_presenter_data.get('event_session_id', None)
event_presentation_id = event_presenter_data.get('event_presentation_id', None)
presenter_full_name = event_presenter_data.get('full_name', None)
person_id = event_presenter_data.get('person_id', None)
# Check if the event presenter has a person_id
if not person_id and link_person:
log.warning(f'Event Presenter ID: {event_presenter_id} does not have a person_id. Attempting to link person.')
else:
log.info(f'Event Presenter ID: {event_presenter_id} already has a person_id: {person_id}. Skipping.')
continue
if not presenter_full_name:
log.warning(f'No presenter full name found for event presenter ID: {event_presenter_id}. Skipping.')
continue
log.info(f'Processing Event Presenter ID: {event_presenter_id} | Full Name: {presenter_full_name}')
# Process the event presenter with the full name
event_presenter_record_results = process_event_presenter_w_full_name(
account_id = account_id,
event_id = event_id,
event_session_id = event_session_id,
event_presentation_id = event_presentation_id,
presenter_full_name = presenter_full_name,
event_presenter_id = event_presenter_id,
link_person = link_person,
sync_person = sync_person,
sync_external_id = sync_external_id,
)
if not isinstance(event_presenter_record_results, dict):
# log.error('Something unexpected may have happened while processing the event presenter with the full name.')
# log.error(event_presenter_record_results)
# return mk_resp(data=None, status_code=500, response=commons.response)
log.warning(f'A matching person full_name was not found.')
# Append the summary data to the list
event_presenter_summary_data_li.append(event_presenter_record_results)
log.info(f'Processed {len(event_presenter_summary_data_li)} event presenters.')
return mk_resp(data=event_presenter_summary_data_li, response=commons.response)
# ### END ### Event Presenter Person Link ### presenter_person_link() ###
# ### BEGIN ### Event Importing ### process_person_data() ###
# Updated 2025-06-13
def process_person_data(account_id, source_code, record):
log.setLevel(logging.INFO) # DEBUG, INFO, WARNING, ERROR, EXCEPTION, CRITICAL
log.debug(locals())
data = {}
data['account_id'] = account_id
data['source_code'] = source_code
data['external_id'] = record.get('external_id', None) # joined data fields like name and email
data['external_sys_id'] = record.get('external_sys_id', None) # UUID or auto-incremented ID
if not data['external_id'] and data['external_sys_id']:
data['external_id'] = data['external_sys_id']
if not data['external_sys_id'] and data['external_id']:
data['external_sys_id'] = data['external_id']
data['informal_name'] = record.get('informal_name', None)
data['title_names'] = record.get('title_names', None)
data['given_name'] = record.get('given_name', None)
data['middle_name'] = record.get('middle_name', None)
data['family_name'] = record.get('family_name', None)
data['designations'] = record.get('designations', None)
data['professional_title'] = record.get('professional_title', None)
data['affiliations'] = record.get('affiliations', None)
data['primary_email'] = record.get('email', None)
data['passcode'] = record.get('passcode', None)
data['notes'] = record.get('notes', None)
data['data_json'] = record.get('data_json', None)
# Field names that are prefixed with json__ will be added to the data_json field as a key value pair.
json_kv = {}
for key in record:
if key.startswith('json__'):
json_kv[key[6:]] = record[key]
# data['data_json'] = json.dumps({key[6:]: record[key]})
data['data_json'] = json.dumps(json_kv)
log.debug(data['data_json'])
log.info(f'Person - External ID: {data["external_id"]} | Email: {data["primary_email"]}')
# Check if the person record already exists to UPDATE, else INSERT
person_rec = None
person_id = None
person_id_random = None
if data['external_sys_id'] and data['external_id']:
if person_rec_result := sql_select(table_name='v_person', field_name='external_sys_id', field_value=data['external_sys_id']):
if not isinstance(person_rec_result, list):
person_rec = person_rec_result
person_id = person_rec.get('person_id', None)
person_id_random = person_rec.get('person_id_random', None)
else:
log.warning('Found more than one record')
log.warning(person_rec_result)
# Do nothing
return False # Something unexpected may have happened
elif person_rec_result := sql_select(table_name='v_person', field_name='external_id', field_value=data['external_id']):
if not isinstance(person_rec_result, list):
person_rec = person_rec_result
person_id = person_rec.get('person_id', None)
person_id_random = person_rec.get('person_id_random', None)
else:
log.warning('Found more than one record')
log.warning(person_rec_result)
# Do nothing
return False
else:
log.info('No record found that needs to be updated. Creating a new person.')
if person_id:
data['id'] = person_id
log.debug(data)
if person_obj_up_result := sql_update(data=data, table_name='person'):
log.debug(person_obj_up_result)
else:
log.warning(person_obj_up_result)
return False
else:
if person_obj_in_result := sql_insert(data=data, table_name='person'):
log.debug(person_obj_in_result)
person_id = person_obj_in_result
else:
log.warning(person_obj_in_result)
return False
data['id_random'] = person_id_random
log.debug(data)
summary_data = {
'id': person_id,
'id_random': person_id_random,
'external_id': data['external_id'],
'external_sys_id': data['external_sys_id'],
'given_name': data['given_name'],
'primary_email': data['primary_email'],
}
return summary_data
# ### END ### Event Importing ### process_person_data() ###
# This will try to look up the person record based on their full name and then update the event presenter record. If the person record is not found, it will return False. If the person record is found, it will return the person record data.
# Should we also pass event_id and event_session_id?
def process_event_presenter_w_full_name(
account_id, event_id,
event_session_id,
event_presentation_id,
presenter_full_name,
event_presenter_id = None,
link_person = True, # At least one of these should be True
sync_person = True, # At least one of these should be True
sync_external_id = False, # Changing the external_id can break future imports
):
log.setLevel(logging.INFO) # DEBUG, INFO, WARNING, ERROR, EXCEPTION, CRITICAL
log.debug(locals())
# First - SQL SELECT to find the person record based on the full name
# If the person record is found, use the person_id to UPDATE the event_presenter record. In the future this will likely be a new table named person_profile. It will likely be a combination of the person, event_badge, and event_presenter tables.
# if person_rec := sql_select(table_name='v_person', field_name='full_name', field_value=presenter_full_name):
qry_data = {
'account_id': account_id,
'full_name': presenter_full_name,
}
if person_rec := sql_select(table_name='v_person', data=qry_data, log_lvl=logging.INFO):
if not isinstance(person_rec, list):
person_id = person_rec.get('person_id', None)
person_id_random = person_rec.get('person_id_random', None)
log.info(f'Found one record. Person ID: {person_id_random} Person Full Name: {presenter_full_name}')
log.debug(person_rec)
summary_data = {}
summary_data['full_name'] = person_rec.get('full_name', None)
summary_data['email'] = person_rec.get('primary_email', None)
# return summary_data
else:
log.warning('Found more than one record')
log.warning(person_rec)
return False
# If the person record is not found, return False
else:
log.warning('Person record not found')
return False
# Second - SQL SELECT to find the event presenter record based on the event_presentation_id and the person_id returned from the first SQL SELECT
# If the event presenter record is found, UPDATE the record with the person_id
# if event_presenter_rec := sql_select(table_name='v_event_presenter', field_name='event_presentation_id', field_value=event_presentation_id):
# First try the easy option by using the known presenter ID
if event_presenter_id:
data = {}
# NOTE: If the external_id changes from what was originally imported, it will no longer import that record correctly. A new one will be created.
if sync_external_id:
data['external_id'] = person_rec['external_id']
data['person_id'] = person_id
data['informal_name'] = person_rec['informal_name']
data['title_names'] = person_rec['title_names']
data['given_name'] = person_rec['given_name']
data['middle_name'] = person_rec['middle_name']
data['family_name'] = person_rec['family_name']
data['designations'] = person_rec['designations']
data['professional_title'] = person_rec['professional_title']
data['affiliations'] = person_rec['affiliations']
data['email'] = person_rec['primary_email']
summary_data['person_id'] = person_id
summary_data['event_presenter_id'] = event_presenter_id
if sync_person:
if event_presenter_obj_up_result := sql_update(data=data, table_name='event_presenter', record_id=event_presenter_id):
log.debug(event_presenter_obj_up_result)
else:
log.warning(event_presenter_obj_up_result)
return False
else:
log.info('Skipping update of event_presenter record')
# Second try looking up with the known presentation ID and found person ID
else:
# Check if the presenter is already linked to a person record. UPDATE if found.
qry_data = {
'event_presentation_id': event_presentation_id,
'person_id': person_id,
}
if event_presenter_rec := sql_select(table_name='v_event_presenter', data=qry_data, log_lvl=logging.INFO):
if not isinstance(event_presenter_rec, list):
event_presenter_id = event_presenter_rec.get('event_presenter_id', None)
event_presenter_id_random = event_presenter_rec.get('event_presenter_id_random', None)
else:
log.warning('Found more than one record')
log.warning(event_presenter_rec)
return False
data = {}
data['full_name'] = person_rec['full_name']
data['email'] = person_rec['primary_email']
summary_data['event_presenter_id'] = event_presenter_id
if sync_person:
if event_presenter_obj_up_result := sql_update(data=data, table_name='event_presenter', record_id=event_presenter_id):
log.debug(event_presenter_obj_up_result)
else:
log.warning(event_presenter_obj_up_result)
return False
else:
log.info('Skipping update of event_presenter record')
# If the event presenter record is not found, INSERT a new record with the person_id to link them.
else:
data = {}
data['event_id'] = event_id
data['event_session_id'] = event_session_id
data['event_presentation_id'] = event_presentation_id
# WARNING: person_id does not exist in the event_presenter table. This is a new field that will be added. It will make more sense to have a person_profile table that combines the person, event_badge, and event_presenter tables.
data['person_id'] = person_id # WARNING!
# This should fill out the event_presenter record based on the person record. This will likely be a new table named person_profile. It will likely be a combination of the person, event_badge, and event_presenter tables.
data['external_id'] = person_rec['external_id']
# Do we know the presenter code (or number)?
# Do we know for_type or for_id?
data['informal_name'] = person_rec['informal_name']
data['title_names'] = person_rec['title_names']
data['given_name'] = person_rec['given_name']
data['middle_name'] = person_rec['middle_name']
data['family_name'] = person_rec['family_name']
data['full_name'] = person_rec['full_name']
data['designations'] = person_rec['designations']
data['professional_title'] = person_rec['professional_title']
data['affiliations'] = person_rec['affiliations']
data['email'] = person_rec['primary_email']
if link_person:
if event_presenter_obj_in_result := sql_insert(data=data, table_name='event_presenter'):
log.debug(event_presenter_obj_in_result)
event_presenter_id = event_presenter_obj_in_result
summary_data['event_presenter_id'] = event_presenter_id
else:
log.warning(event_presenter_obj_in_result)
return summary_data
Reference in New Issue View Git Blame Copy Permalink