def main():
downloads_folder = dir.get_download_folder()
files = [(f, os.path.getctime(f))
for f in dir.listdir(downloads_folder, False, True)
if f.endswith('.zip') and 'OneDrive_' in f]
files.sort(key=lambda item: item[1], reverse=True)
source_zip_file_path = files[0][0]
timenow = datetime.datetime.now().strftime('%Y-%m-%d %H.%M')
# evenko_folder_path = '/Users/daniel.hicks_1/Documents/Rome/Rome Downloads/Evenko/'
# import_zip_path = f'{evenko_folder_path}Templates.zip'
# import_folder_path = f'{evenko_folder_path}Templates/'
migration_folder_path = f'/Users/daniel.hicks_1/Documents/Rome/Rome Downloads/Evenko/Migration Source/Evenko Migration {timenow}/'
with zipfile.ZipFile(source_zip_file_path, "r") as zip_ref:
zip_ref.extractall(migration_folder_path)
import_data_path = f'{migration_folder_path}/Historical data'
# import_folder_path = '/Users/daniel.hicks_1/Documents/Rome/Rome Downloads/Historical data Evenko 2021-08-11'
def map_sheet_name(v):
return ('EventDateTime__c' if 'EventDateTime' in v else
'Event__c' if 'Event' in v else 'Deal__c' if 'Deal' in v else
'TicketScale__c' if 'TicketScale' in v else 'Deduction__c'
if 'Deduction' in v else 'LedgerEntry__c' if 'LedgerEntry_' in
v else 'LedgerEntryBreakout__c' if 'LedgerEntryActive' in
v else None)
data = ObjDict({
# map_sheet_name(f): pd.read_excel(import_data_path+'/'+f)
map_sheet_name(f): pd.read_csv(import_data_path + '/' + f,
encoding='ISO-8859-1')
for f in os.listdir(import_data_path) if map_sheet_name(f) is not None
})
for obj, df in data.items():
df.replace('#COMA#', ',', inplace=True, regex=True)
df.replace('#CHAR13#', '\n', inplace=True, regex=True)
df.replace('#CHAR10#', '\r', inplace=True, regex=True)
df.replace('NULL', np.nan, inplace=True)
data.update(
{key: pd.DataFrame()
for key in objects_to_import if key not in data})
lneaccounts = threading.new(lne.select,
"SELECT Id, SourceSystemId__c FROM Account",
mute=True)
uataccounts = threading.new(uat.select,
"SELECT Id, SourceSystemId__c FROM Account",
mute=True)
evenkoofficeid = uat.select(
"SELECT Id FROM Account WHERE RecordType.Name = 'Office' AND Name = 'Evenko'",
mute=True,
mode='simple',
cache_duration=24 * 60)[0].Id
user_email_mapper = uat.user_mapper(
'Email',
return_field='Id',
where_clause="WHERE UserRole.Name LIKE '%Evenko%'")
# glaccountsmap = {
# item.GLCode__c: item for item in
# uat.select("SELECT Id, Name, Type__c, GLCode__c, Category__c FROM GLAccount__c WHERE ActiveFlag__c = True", mute=True, mode='simple', cache_duration=24*60)
# }
glaccountsmap = (threading.new(
uat.select,
"SELECT Id, Name, Type__c, GLCode__c, Category__c FROM GLAccount__c WHERE ActiveFlag__c = True",
mute=True,
mode='simple',
cache_duration=24 *
60).then(lambda result: {item.GLCode__c: item
for item in result}))
# FIXES
data.Deal__c.Artist__c.replace('Coheadlner', np.nan, inplace=True)
data.Event__c.rename({'MDAID': 'EvenkoAACode__c'}, inplace=True)
evt = data.Event__c
edt = data.EventDateTime__c
deal = data.Deal__c
ts = data.TicketScale__c
ded = data.Deduction__c
le = data.LedgerEntry__c
leb = data.LedgerEntryBreakout__c
# leb = leb[leb['OfferRate__c'].str.isnumeric().fillna(False)]
evt['Office__c'] = evenkoofficeid
# evt['Promoter__c'] = evt['Promoter__r'].apply(lambda x: user_email_mapper(x.lower()) if type(x) is str else None)
# evt['ProductionManager__c'] = evt['ProductionManager__r'].apply(lambda x: user_email_mapper(x.lower()) if type(x) is str else None)
# evt['TicketingManager__c'] = evt['TicketingManager__r'].apply(lambda x: user_email_mapper(x.lower()) if type(x) is str else None)
# pdh.to_excel(data, import_folder_path+'/'+'Combined.xlsx')
# # If only Plan or only Projection Ticket Scale records were provided for an event, then copy the Ticket Scales so that both Plan and Projection are inserted
# copied_ts_dfs = []
# for event_id in evt['SourceSystemId__c'].tolist():
# evt_ts_df = ts[ts['Event__r.SourceSystemId__c'] == event_id]
# plan = evt_ts_df[evt_ts_df['StageType__c'] == 'Plan']
# projection = evt_ts_df[evt_ts_df['StageType__c'] == 'Projection']
# if len(plan) > 0 and len(projection) == 0:
# new = plan.copy()
# new['StageType__c'] = 'Projection'
# copied_ts_dfs.append(new)
# if len(projection) > 0 and len(plan) == 0:
# new = projection.copy()
# new['StageType__c'] = 'Plan'
# copied_ts_dfs.append(new)
# ts = pd.concat([ts] + copied_ts_dfs)
if le is not None:
if 'GLAccount__r.GLCode__c' in le.columns:
le['GLCode'] = le['GLAccount__r.GLCode__c']
le['RecordType.Name'] = le['GLAccount__r.GLCode__c'].apply(
lambda x: glaccountsmap[x].Type__c)
le['BookingSettlement__c'] = True
data.LedgerEntry__c = le
# artistnamesmap = {item.Id:item.Name for item in artistnames.result()}
# primaryheadlinerdeals = deal.copy()[deal['Type__c'] == 'Primary Headliner']
# primaryheadlinerdeals['SourceSystemId__c'] = primaryheadlinerdeals['Event__r.SourceSystemId__c']
# primaryheadlinerdeals['PrimaryHeadlinerArtist__c'] = primaryheadlinerdeals['Artist__c'].apply(lambda x: artistnamesmap[x])
# primaryheadlinerdeals = primaryheadlinerdeals[['SourceSystemId__c', 'PrimaryHeadlinerArtist__c']]
# evt = evt.merge(right=primaryheadlinerdeals, how='left', on='SourceSystemId__c')
# Data fixes
# evt.rename(columns={'Event__c.Venue__c': 'Venue__c'}, inplace=True)
ded.rename(
columns={'Event__r.SourceSystemId__c ': 'Event__r.SourceSystemId__c'},
inplace=True)
# deal['RecordType.Name'] = 'Artist'
# deal['Type__c'] = 'Primary Headliner'
deal.rename(columns={
'Deal__c.Agency__c': 'Agency__c',
'Deal__c.Agent__c': 'Agent__c'
},
inplace=True)
if le is not None and len(le) > 0:
# le.rename(columns={'GLCode__c': 'GLAccount__r.GLCode__c', 'SourceSystemId__c': 'Event__r.SourceSystemId__c'}, inplace=True)
le.fillna('', inplace=True)
le.query("`GLCode__c` != ''", inplace=True)
event_lookup = {
item['SourceSystemId__c']: item
for item in evt.to_dict('records')
}
edt['Venue__c'] = edt['Event__r.SourceSystemId__c'].apply(
lambda x: event_lookup[x]['Venue__c'] if x in event_lookup else '')
# del data['LedgerEntry__c']
# event_filter = 'EvenkoDeal-12986'
# # uat.delete_events(f"SELECT Id FROM Event__c WHERE SourceSystemId__c = '{event_filter}'")
# evt.query("SourceSystemId__c == @event_filter", inplace=True)
# edt.query("`Event__r.SourceSystemId__c` == @event_filter", inplace=True)
# ded.query("`Event__r.SourceSystemId__c` == @event_filter", inplace=True)
# le.query("`Event__r.SourceSystemId__c` == @event_filter", inplace=True)
# ts.query("`Event__r.SourceSystemId__c` == @event_filter", inplace=True)
# deal.query("`Event__r.SourceSystemId__c` == @event_filter", inplace=True)
# Re-names in order to insert records to UAT instead of PROD
# if uat.instance == 'uat':
# uat_ids_map = {item.Id: item for item in uataccounts.result()}
# uat_src_ids_map = {item.SourceSystemId__c: item for item in uataccounts.result()}
# def get_uat_id(id):
# if id not in uat_ids_map:
# if id in uat_src_ids_map:
# return uat_src_ids_map[id].Id
# else:
# return ''
# return id
# evt['Venue__c'] = evt['Venue__c'].apply(get_uat_id)
# evt['Office__c'] = evt['Office__c'].apply(get_uat_id)
# deal['Artist__c'] = deal['Artist__c'].apply(get_uat_id)
# deal['CoPromoter__c'] = deal['CoPromoter__c'].apply(get_uat_id)
# deal['Agency__c'] = deal['Agency__c'].apply(get_uat_id)
# deal['Agent__c'] = deal['Agent__c'].apply(get_uat_id)
del data['LedgerEntry__c']
# Model to populate all remaining fields
model = SalesforceLiveNationModelCompute(
uat,
**data,
set_null_datasets_to_empty_list=True,
)
computed = model.compute_all(
keep_exception_columns=True,
to_compute={
'Event__c': {
'RecordTypeId', 'ShowCount__c', 'ShowCountWithDates__c',
'EventFirstDate__c', 'EventLastDate__c', 'EventYear__c',
'PrimaryHeadlinerArtist__c', 'Artists__c', 'IsTouringApp__c',
'OfficeName__c', 'Division__c', 'Geography__c',
'PrimaryVenueOffice__c', 'VenueOwnership__c',
'HiddenVenueOffice__c'
}
})
print(f'Failed: {computed.failed}')
# if 'LedgerEntry__c' in computed.data2:
# del computed.data2.LedgerEntry__c['GLAccount__c']
pdh.to_excel(computed.data2,
migration_folder_path + '/' + 'Combined Import Data.xlsx')
os.remove(source_zip_file_path)
# Filters for testing purposes
# data.Event__c = evt[pd.notnull(evt['Venue__c'])].copy()
# event_src_ids = set(data.Event__c['SourceSystemId__c'].tolist())
# for obj, df in data.items():
# if 'Event__r.SourceSystemId__c' in df.columns:
# data[obj] = df[df['Event__r.SourceSystemId__c'].apply(lambda x: x in event_src_ids) == True].copy()
# pdh.to_excel(data, import_folder_path+'/'+'CombinedFiltered.xlsx')
# # Deletion for testing purposes, to have a fresh slate
# uat.delete_events("SELECT Id FROM Event__c WHERE SourceSystemId__c IN ('{}')".format("','".join(evt['SourceSystemId__c'].tolist())))
upsert_event_data_to_rome(uat, computed.data2, True)
# result = uat.create_events(
# computed.data2
# , {
# 'Event__c': 'SourceSystemId__c'
# , 'EventDateTime__c': 'SourceSystemId__c'
# }
# , delete_old_child_records=True
# , run_fc=False
# )
# pdh.to_excel({key:val for key,val in result.errors.items() if len(val) > 0}, import_folder_path+'/'+'Migration Results - ERRORS.xlsx')
# pdh.to_excel(result.success, import_folder_path+'/'+'Migration Results - SUCCESS.xlsx')
return
def get_file_data_inner(path, expenses_custom_metadata, copromotersmap):
print(f'Reading Excel data: {path}')
data = ObjDict()
try:
xlsx_file = pd.ExcelFile(path)
sheets = xlsx_file.sheet_names
le_sheet = [s for s in sheets if s in ('Ledger Entry Export', 'Ledger Entry Report', 'Ledger entry export')][0]
leb_sheet = [s for s in sheets if s in ('Ledger Entry Breakout Export', 'Breakout export')][0]
df1 = pd.read_excel(xlsx_file, sheet_name=le_sheet)
df2 = pd.read_excel(xlsx_file, sheet_name=leb_sheet)
df1['Source_File'] = os.path.split(path)[-1]
df2['Source_File'] = os.path.split(path)[-1]
except Exception as e:
print(f'\n## Could not parse {path}:\n{e}\nIf list index out of range, then a sheet is missing')
return None
df1 = df1.fillna('').query("`LedgerEntry__c.SourceSystemId__c` != ''")
df2 = df2.fillna('')
df2['TouringCategory__c'] = df2['UKCategory__c']
expense_labels = {item.Label: item.GLCode__c for item in expenses_custom_metadata}
df2['Type__c'] = df2.apply(lambda x: (
x['Label__c'] if (x['Label__c'] in expense_labels and x['GLCodePicklist__c'] == expense_labels[x['Label__c']]) else x['Type__c']
), axis=1)
df1['Tour__c.RecordType.Name'] = 'UK Booking'
df1['Event__c.RecordType.Name'] = 'Business Plan'
df1['LedgerEntry__c.GLAccount__r.GLCode__c'] = int_to_str(df1['LedgerEntry__c.GLAccount__r.GLCode__c'])
# Data fix invalid values in dataframe
df1['TourLeg__c.Crossed__c'] = df1['TourLeg__c.Crossed__c'].apply(lambda x: x if x in (1,0) else 1)
df2['Label__c'] = df2.apply(lambda row: row.Label__c if str(row.Label__c) not in ('0','0.0') else row.Type__c, axis=1)
cols_to_string = ['Tour__c.OracleProjectCode__c'] + [c for c in list(df1.columns)+list(df2.columns) if 'EOSId' in c or 'SourceSystemId' in c]
for col in cols_to_string:
if col in df1:
df1[col] = df1[col].astype(str).replace('\.0','',regex=True)
if col in df2:
df2[col] = df2[col].astype(str).replace('\.0','',regex=True)
df1['TourLeg__c.Tour__r.EOSId__c'] = df1['Tour__c.EOSId__c']
data.Tour__c1 = (
df1[[c for c in df1 if 'Tour__c.' in c or c == 'Source_File']]
.query("`Tour__c.EOSId__c` != ''")
.drop_duplicates(subset='Tour__c.EOSId__c')
.copy()
)
data.Tour__c1['IsHistoricalTour__c'] = True
data.TourLeg__c1 = (
df1[[c for c in df1 if 'TourLeg__c.' in c or c == 'Source_File']]
.query("`TourLeg__c.Tour__r.EOSId__c` != ''")
.drop_duplicates(subset=['TourLeg__c.Tour__r.EOSId__c', 'TourLeg__c.Crossed__c'])
.copy()
)
data.TourLeg__c1[['LegName__c', 'SourceSystemId__c']] = data.TourLeg__c1.apply(
lambda x: (
'Crossed' if x['TourLeg__c.Crossed__c'] else 'Uncrossed'
, ('Crossed-' if x['TourLeg__c.Crossed__c'] else 'Uncrossed-') + x['TourLeg__c.Tour__r.EOSId__c']
)
, axis=1
, result_type='expand'
)
data.TourDeal__c1 = (
df1[['Tour__c.EOSId__c', 'TourLeg__c.Crossed__c', 'Tour__c.BackendPercent__c', 'Tour__c.UncrossedBackendPercent__c', 'Source_File']]
.query("`Tour__c.EOSId__c` != ''")
.drop_duplicates(subset=['Tour__c.EOSId__c', 'TourLeg__c.Crossed__c'])
.rename(columns={
'Tour__c.EOSId__c': 'Tour__r.EOSId__c'
})
)
data.TourDeal__c1[['DealType__c', 'BackendPercent__c', 'TourLegsCrossed__c', 'EventsCrossed__c', 'SourceSystemId__c']] = data.TourDeal__c1.apply(
lambda x: (
'Percentage of Net'
, x['Tour__c.BackendPercent__c'] if x['TourLeg__c.Crossed__c'] else x['Tour__c.UncrossedBackendPercent__c']
, True if x['TourLeg__c.Crossed__c'] else False
, True if x['TourLeg__c.Crossed__c'] else False
, ('Crossed-' if x['TourLeg__c.Crossed__c'] else 'Uncrossed-') + x['Tour__r.EOSId__c']
)
, axis=1
, result_type='expand'
)
data.Event__c1 = (
df1[[c for c in df1 if 'Event__c.' in c or 'TourLeg__c.' in c or c == 'Source_File']]
.query("`Event__c.EOSId__c` != ''")
.copy()
)
data.Event__c1['TourLeg__r.SourceSystemId__c'] = data.Event__c1['TourLeg__c.Crossed__c'].apply(lambda x: 'Crossed' if x is True else 'Uncrossed') + '-' + data.Event__c1['TourLeg__c.Tour__r.EOSId__c']
data.LedgerEntry__c = (
df1[[c for c in df1 if 'LedgerEntry__c.' in c or c == 'Source_File']]
.copy()
)
fields_to_aggregate = ['Settlement__c', 'OfferRate__c', 'OfferAtProjection__c', 'OfferAtSellout__c', 'InHouseRate__c', 'InHouseProjection__c', 'InHouseatSellout__c', 'ExpenseAdjustment__c', 'ExpenseAdjustmentatSellout__c']
data.LedgerEntryBreakout__c = (
df2.groupby(by=[c for c in df2.columns if c not in fields_to_aggregate], sort=False)
.sum()
.reset_index()
)
ledgerentries_map = data.LedgerEntry__c[['LedgerEntry__c.GLAccount__r.GLCode__c','LedgerEntry__c.Event__r.EOSId__c','LedgerEntry__c.CurrentFlash__c']].groupby(by=['LedgerEntry__c.Event__r.EOSId__c', 'LedgerEntry__c.GLAccount__r.GLCode__c']).sum().to_dict('index')
data.Deal__c1 = data.Event__c1[['Event__c.EOSId__c']].copy().rename(columns={'Event__c.EOSId__c': 'Event__r.EOSId__c'})
# data.Deal__c1['GuaranteeAmount__c'] = data.Deal__c1['Event__c.EOSId__c'].apply(lambda x: sum(data.LedgerEntry__c[data.LedgerEntry__c['LedgerEntry__c.GLAccount__r.GLCode__c'] == '50002']['LedgerEntry__c.CurrentFlash__c'].tolist()))
data.Deal__c1['GuaranteeAmount__c'] = data.Deal__c1['Event__r.EOSId__c'].apply(lambda x: ledgerentries_map[(x, '50002')]['LedgerEntry__c.CurrentFlash__c'] if (x, '50002') in ledgerentries_map else 0)
tourcopromoterdeals = (
df1[['Tour__c.EOSId__c', 'TourCoPromoter1Name', 'TourCoPromoter1Percent', 'TourCoPromoter2Name', 'TourCoPromoter2Percent']]
.query("`Tour__c.EOSId__c` != ''")
.drop_duplicates(subset='Tour__c.EOSId__c')
.rename(columns={
'Tour__c.EOSId__c': 'Tour__r.EOSId__c'
})
)
data.TourCoProDeals = pd.concat([
tourcopromoterdeals[['Tour__r.EOSId__c', 'TourCoPromoter1Name', 'TourCoPromoter1Percent']]
.query("TourCoPromoter1Name != ''")
.rename(columns={'TourCoPromoter1Name': 'CoPromoter__r.Name', 'TourCoPromoter1Percent': 'EventPL__c'})
, tourcopromoterdeals[['Tour__r.EOSId__c', 'TourCoPromoter2Name', 'TourCoPromoter2Percent']]
.query("TourCoPromoter2Name != ''")
.rename(columns={'TourCoPromoter2Name': 'CoPromoter__r.Name', 'TourCoPromoter2Percent': 'EventPL__c'})
])
data.TourCoProDeals['CoPromoter__c'] = data.TourCoProDeals['CoPromoter__r.Name'].apply(lambda x: '' if x not in copromotersmap else copromotersmap[x].Id)
rename(data.Tour__c1, 'Tour__c')
rename(data.TourLeg__c1, 'TourLeg__c')
rename(data.Event__c1, 'Event__c')
rename(data.LedgerEntry__c, 'LedgerEntry__c')
return data
