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