def main(): session = Salesforce_API("*****@*****.**") query = """ SELECT Id, CreatedDate, CreatedBy.Name, SourceSystemId__c FROM {} WHERE IsTouringApp__c = True AND Event__c = NULL AND CreatedDate >= 2020-09-01T00:00:00Z ORDER BY CreatedDate DESC """ objects = [ 'EventDateTime__c', 'Deal__c', 'TicketScale__c', 'Deduction__c', 'LedgerEntry__c', 'LedgerEntryBreakout__c' ] data = {} session.bulk for o in objects: data[o] = threading.new(session.select, query.format(o)) for key, val in data.items(): val = val.result() prefix = session.get_object_description(key).keyPrefix for row in val: if str(row.SourceSystemId__c)[:3] == prefix: row.IsLikelyClone = True data[key] = val pdh.to_excel(data, 'Orphaned child records {}.xlsx'.format(session.instance)) return
def example9(): import json gb_settings = uat.select("SELECT * FROM GBLite__GridBuddySettings__c") gb_meta = uat.select("SELECT * FROM GBLite__GB_Global_Meta__c") for item in gb_meta: try: item.meta = json.loads(item.GBLite__Picklist_Meta__c) item.update(item.meta) except: item.meta = None actions = [ item for item in gb_meta if item.get('actionLabel', None) is not None ] pdh.to_excel( { 'GBLite__GridBuddySettings__c': gb_settings, 'GBLite__GB_Global_Meta__c': gb_meta, }, 'GBLite Data.xlsx') # monitor = DataChangeMonitor() # monitor.set_frequency(5) # monitor.add_job('GBLite__GridBuddySettings__c', "Id", uat.select, "SELECT * FROM GBLite__GridBuddySettings__c", mute=True) # monitor.set_log_destination(None, 'GBLite__GridBuddySettings__c Changes') # monitor.start() # print('Monitor started') return # example9()
def find_artist_dupes(): rome_artists = sf.select(""" SELECT Id, EOSId__c, Name FROM Account WHERE EOSId__c <> NULL AND RecordType.Name = 'Artist' """, mode='bulk') eos_artists = sql.query(""" SELECT 'Artist-' + CAST(Id AS VARCHAR) AS EOSId__c, Name FROM Artist """) eos_artists_by_name = {item['Name']: item for item in eos_artists} issues = [] for artist in rome_artists: issue = eos_artists_by_name.get(artist.Name, None) if issue is not None and issue['EOSId__c'] != artist['EOSId__c']: issues.append({ 'RomeId': artist.Id, 'RomeLink': f'https://{sf.simple.sf_instance}/{artist.Id}', 'Name': artist.Name, 'CurrRomeEOSId': artist.EOSId__c, 'ChangeRomeEOSIdTo': issue['EOSId__c'], }) if issues: pdh.to_excel(issues, f'{sf.instance} Rome EOS Artist Issues.xlsx') return
def fix(sf=None, sql=None, vars=None, data=None): import functions.pandas_helpers as pdh import pandas as pd venueownership = vars.get('venueownership') officename = vars.get('officename') primaryvenueoffice = vars.get('primaryvenueoffice') division = vars.get('division') geography = vars.get('geography') pdh.to_excel( { 'VenueOwnership__c': venueownership, 'OfficeName__c': officename, 'PrimaryVenueOffice__c': primaryvenueoffice, 'Division__c': division, 'Geography__c': geography, 'All': pd.concat([ venueownership, officename, primaryvenueoffice, division, geography ])[['Id']].drop_duplicates('Id') }, 'Events Need Security Update.xlsx') sf.bypass_prod_operation_approval() with sf.bypass_settings(): sf.update_event_security_fields(data.to_dict('records'))
def main(sessions, do_fix=False): import functions.pandas_helpers as pdh from functions.prompt import prompt from classes.py_threading import ThreadPool threading = ThreadPool() sessions = { username: sf for username, sf in sessions.items() if username in instances_to_run } return_string = "" objects = [ 'EventDateTime__c', 'Deal__c', 'TicketScale__c', 'Deduction__c', 'LedgerEntry__c' ] if do_fix is True and prompt("Delete orphan records?", boolean=True) is False: do_fix = False @threading.run_async def inner(sf): if do_fix: sf.bypass_prod_operation_approval() results = {} for obj in objects: obj_fields = { f.name: f for f in sf.get_object_description(obj).fields } fields = ['Name', 'EventName__c'] fields = [f for f in fields if f in obj_fields] fields_str = ', '.join(fields) query = f""" SELECT Id, {fields_str}, Event__c, CreatedBy.Name, CreatedDate, LastModifiedBy.Name, LastModifiedDate FROM {obj} WHERE IsTouringApp__c = True AND Event__c = NULL AND CreatedDate >= THIS_YEAR """ results[obj] = threading.new(sf.select, query, mode='bulk') for obj in objects: records = results[obj].result() if len(records) > 0: if do_fix: sf.add_bypass_settings() sf.delete(records, mode='bulk') sf.remove_bypass_settings() return results # Run for each Salesforce instance results = {sf.instance: inner(sf) for sf in sessions.values()} for sf in sessions.values(): result = results[sf.instance].result() for obj in objects: records = result[obj].result() return_string += f"\n{sf.instance.upper()} has {len(records)} {obj} orphaned records with IsTouringApp__c == True" pdh.to_excel(results[sf.instance], f'({sf.instance}) Orphaned Event Child Records.xlsx') return return_string
def main(): tours = sf.select(""" SELECT Id, TourName__c, TourBooker__r.Name, TourHeadliner__r.Name, TourCoHeadliner__r.Name, CreatedDate FROM Tour__c WHERE AppScope__c = 'UK' """, return_type='flat') events = sf.select(""" SELECT Id, TourLeg__r.Tour__c, EventFirstDate__c FROM Event__c WHERE TourLeg__r.Tour__c IN @tours.Id AND EventCancelled__c = False AND BusinessPlanOption__c = False """, return_type='flat') events_by_tour = pdh.groupby_unsorted(events, lambda x: x['TourLeg__r.Tour__c']) for tour in tours: tour['FirstEventDate'] = min([ item.EventFirstDate__c for item in events_by_tour.get(tour.Id, []) if item.EventFirstDate__c is not None ] or ['']) tour['LastEventDate'] = max([ item.EventFirstDate__c for item in events_by_tour.get(tour.Id, []) if item.EventFirstDate__c is not None ] or ['']) pdh.to_excel(tours, 'Tour Renaming Workbook.xlsx') return
def main(): ts = sf.select(""" SELECT Id, Event__r.TourLeg__r.Tour__r.EOSId__c, Event__r.EOSId__c, Type__c, LastModifiedDate, LastModifiedBy.Name FROM TicketScale__c WHERE StageType__c IN ('Ticket Band','Ticket Hold','Ticket Upgrade') AND IsTouringApp__c = True AND Event__r.TourLeg__r.Tour__r.AppScope__c = 'UK' AND LastModifiedDate = TODAY ORDER BY LastModifiedDate DESC """, return_type='flat') event_ids = [t['Event__r.EOSId__c'] for t in ts] all_events = sf.select(""" SELECT Id, EOSId__c, TourLeg__r.Tour__r.EOSId__c FROM Event__c WHERE EOSId__c IN @event_ids """, return_type='flat') last = None groups = [] curr_group = [] groups.append(curr_group) for t in ts: s = t.LastModifiedDate[11:19] time = int(s[0:2]) * 60 * 60 + int(s[3:5]) * 60 + int(s[6:8]) t.time = time if last is None or last.time - 60 <= time: curr_group.append(t) else: curr_group = [] curr_group.append(t) groups.append(curr_group) t.group_index = len(groups) - 1 last = t out = [] for i, group in enumerate(groups): events = {ts['Event__r.EOSId__c'] for ts in group} tours = {ts['Event__r.TourLeg__r.Tour__r.EOSId__c'] for ts in group} all_possible_events = { e.EOSId__c for e in all_events if e['TourLeg__r.Tour__r.EOSId__c'] in tours } missing_events = {e for e in all_possible_events if e not in events} num_events = len({ts['Event__r.EOSId__c'] for ts in group}) num_tours = len( {ts['Event__r.TourLeg__r.Tour__r.EOSId__c'] for ts in group}) out.append({ 'index': i, 'events': num_events, 'tours': num_tours, 'missing_events': missing_events }) pdh.to_html(ts, 'TS Data') pdh.to_excel([ts, out], 'TS Data') return
def main(): do_fix = False venues = sf.select(""" SELECT Id, Name, PrimaryOffice__r.Name, LocalTerms__c FROM Account WHERE RecordType.Name = 'Venue' """, return_type='flat') lookfor1 = 'the night of the show' lookfor2 = '' reg1 = re.compile( r'(([\n\r]*.*?)If the net gross ticket revenue on night of show is lower than the all-in talent fee, then the talent fee will be adjusted to the net gross ticket revenue. Net gross ticket revenue is after deducting taxes, fees, FMF, bundles and parking \(where applicable\)\.)' ) def output(): for item in venues: if item.LocalTerms__c is None: continue item.Result = None res = re.search(reg1, item.LocalTerms__c) if res is not None: item.Result = 'Matched local terms to be removed' item.PrefixCharsToRemove = res.group(2) elif lookfor1 in item.LocalTerms__c: item.Result = 'Matched local terms to be removed' # elif lookfor2 in item.LocalTerms__c: # item.Result = 'Matched outlier text in local terms' else: continue if do_fix: del item['Name'] del item['PrimaryOffice__r.Name'] if res: item.LocalTerms__c = item.LocalTerms__c.replace( res.group(0), '') else: item.LocalTerms__c = item.LocalTerms__c.replace( lookfor1, '') # if item.Id in [ # '0011Q00002NSRJfQAP', # # '0013600001YSvCUAA1', # # '0013600001YSvK7AAL', # # '0011Q000021frucQAA', # ]: yield item results = list(output()) pdh.to_excel( results, f'{sf.instance} 2021-11-10 with "the night of the show".xlsx') # write_pickle(f'{sf.instance} 2021-10-05 Venues to Update Local Terms.pickle', results) # if do_fix: # with sf.bypass_settings(): # sf.update(results) return
def main(): ev = pd.read_excel(data_path, 'Evenko') rome = pd.read_excel(data_path, 'Rome') # diff = pdh.diff_and_fuzzy_match(rome, ev, 'Id', 'Type__c', None, None, None, False) diff = pdh.compare_datasets_for_merge(rome, ev, 'Type__c', None, 'Type__c') pdh.to_excel(diff, 'Evenko-Rome Expenses Diff Result.xlsx') return
def write_missing_eos_ids_file(offer_ids=None): offer_ids = offer_ids or [] onsale_offer_ids = get_onsale_data()['Offer']['OfferId'].tolist() all_offer_ids = onsale_offer_ids + offer_ids missing, missing_by_tour = find_missing_eos_ids(offer_ids=all_offer_ids) missing_records = uk.query_by_eos_ids(sql, missing) pdh.to_excel(missing_records, loc.uk_onsale_migration_missing_eos_ids_output) return missing, missing_by_tour
def main(): usernames = [ None # , "[email protected]" # , "[email protected]" # , "[email protected]" # , "*****@*****.**" # , "*****@*****.**" # , "*****@*****.**" , "*****@*****.**" ] sessions = [Salesforce_API(item) for item in usernames if item is not None] for session in sessions: session.save_record_snapshot_on_select = True events = session.select(""" SELECT Id , ShowCount__c , (SELECT GrossSales__c, ProjectedGrossSales__c FROM TicketScales__r WHERE StageType__c = 'Plan') FROM Event__c WHERE IsTouringApp__c = True AND TourLeg__c <> NULL """, mode='simple') for event in events: event.SelloutGrossSales__c = event.ShowCount__c * sum([item.GrossSales__c for item in event.TicketScales__r]) event.ProjectedGrossSales__c = event.ShowCount__c * sum([item.GrossSales__c for item in event.TicketScales__r]) deals = session.select(""" SELECT Id, Event__r.TourLeg__r.Tour__r.ArtistAgency__c, Event__r.TourLeg__r.Tour__r.ArtistAgent__c, Type__c, DealType__c, BackendPercent__c, SplitPercentage__c, SplitBackendPercent__c FROM Deal__c WHERE Event__c <> NULL AND Event__r.IsTouringApp__c = True AND Event__r.TourLeg__c <> NULL AND RecordType.Name = 'Artist' """, return_type='dataframe', mode='simple') pdh.to_excel({ 'Events': events, 'Deals': deals }, 'Inspect Deal data backfill BEFORE.xlsx') deals['IncludeToBeSharedAmount__c'] = True deals.loc[deals['Type__c'] == 'Support', 'DealType__c'] = 'Flat' deals.loc[deals['Type__c'] == 'Primary Headliner', 'SplitPercentage__c'] = 100 deals.loc[deals['Type__c'] == 'Primary Headliner', 'SplitBackendPercent__c'] = deals[deals['Type__c'] == 'Primary Headliner']['BackendPercent__c'] deals.fillna('', inplace=True) pdh.to_excel({ 'Events': events, 'Deals': deals }, 'Inspect Deal data backfill.xlsx') session.add_bypass_settings() # job1 = session.update(events, mode='bulk') job2 = session.update(deals, mode='bulk', batch_size=1000) threading.wait() session.remove_bypass_settings() return
def main(sessions, do_fix=False): import pandas as pd import functions.pandas_helpers as pdh from classes.py_threading import ThreadPool, Thread threading = ThreadPool() sessions = { username: sf for username, sf in sessions.items() if username in instances_to_run } return_string = "" fields_to_match = [ 'Venue__r.Name', 'Office__r.Name', 'PrimaryHeadlinerArtist__c', # 'EventFirstDate__c', ] @threading.run_async def inner(sf): fields_to_match_str = ', '.join(f'{f}, BusinessPlanEvent__r.{f}' for f in fields_to_match) query = f""" SELECT Id, Tour__r.TourTitle__c, BusinessPlanEvent__c, EventTitle__c, {fields_to_match_str} FROM Event__c WHERE BusinessPlanEvent__c <> NULL """ records = sf.select(query, mode='simple', return_type='dataframe').fillna('') # pdquery = ' or '.join(f'(`{f}` != `BusinessPlanEvent__r.{f}`)' for f in fields_to_match) def diff(row): differences = [ f for f in fields_to_match if row[f] != row['BusinessPlanEvent__r.' + f] ] return ', '.join(differences) # mismatched = records.query(pdquery) records['Diff'] = records.apply(diff, axis=1) return records.query('Diff != ""') # Run for each Salesforce instance results = {sf.instance: inner(sf) for sf in sessions.values()} for sf in sessions.values(): result = results[sf.instance].result() for f in fields_to_match: mismatches_for_field = result.query( f"`{f}` != `BusinessPlanEvent__r.{f}`") results[f'{f} Mismatches'] = mismatches_for_field return_string += f'\n{sf.instance.upper()} has {len(mismatches_for_field)} mismatches on the {f} field between BP and Local Events' if do_fix: print(return_string) pdh.to_excel(results, 'Local Events with Field Mismatches.xlsx') return return_string
def main(): usernames = [ # "[email protected]", # "[email protected]", # "[email protected]", # "*****@*****.**", # "*****@*****.**", # "*****@*****.**", "*****@*****.**", ] sessions = [Salesforce_API(item) for item in usernames if item is not None] for session in sessions: accounts = session.select(""" SELECT Id, RecordType.Name, Name, VenueName__c, BillingCity, BillingCountry, SourceSystemId__c, EOSId__c, CreatedBy.Name FROM Account """, return_type='dataframe') output = {} recordtypes = [ 'Venue', # 'Co-Promoter', # 'Artist', # 'Agency', # 'Ticket Agency', ] def wipe_same_record(x): for i in range(1, 4): if x[f'm{i}_Id'] == x['Id']: for key in x.keys(): if key.startswith(f'm{i}_'): x[key] = '' return x for r in recordtypes: filtered_accounts = accounts.query("`RecordType.Name` == @r") print(f'Processing {len(filtered_accounts)} {r} records') matching_fields = ['VenueName__c', 'BillingCity' ] if r == 'Venue' else ['Name'] df = pdh.compare_datasets_for_merge( filtered_accounts, filtered_accounts, matching_fields, fields_to_return=[ 'Id', 'BillingCity', 'EOSId__c', 'SourceSystemId__c' ] + matching_fields, num_matches=4) df = df.apply(wipe_same_record, axis=1) df.sort_values(by=['m1_%', 'm2_%', 'm3_%', 'm4_%'], ascending=False, inplace=True) # df.drop(columns=[c for c in df.columns.values if c.startswith('m1_')], inplace=True) output[r] = df pdh.to_excel(output, 'Rome Duplicate Analysis.xlsx') return
def fix_venue_names(): rome_venues = sf.select(""" SELECT Id, VenueName__c, BillingCity, EOSId__c FROM Account WHERE RecordType.Name = 'Venue' AND EOSId__c <> NULL """) rome_venues_by_id = {item.EOSId__c: item for item in rome_venues} eos_venues = sql.query(""" SELECT Id, 'Venue-' + CAST(Id AS VARCHAR) AS EOSId__c, Name FROM Venue WHERE RomeId IS NOT NULL """) toupdate = [] # issue_rome_venues = [item for item in rome_venues if f'({item.BillingCity})' in item.VenueName__c] # for item in issue_rome_venues: # item.NewName = item.VenueName__c.replace(f'({item.BillingCity})', '').strip() # issue_rome_venues = [item for item in rome_venues if item.VenueName__c.endswith(str(item.BillingCity))] # for item in issue_rome_venues: # item.NewName = item.VenueName__c.replace(f', {item.BillingCity}', '').replace(f',{item.BillingCity}', '').replace(f'{item.BillingCity}', '').strip() for venue in eos_venues: rome_venue = rome_venues_by_id.get(venue['EOSId__c'], None) # if rome_venue is None: continue venue['RomeName'] = rome_venue.VenueName__c venue['RomeCity'] = rome_venue.BillingCity venue['OldName'] = venue['Name'] venue['Name'] = f'{rome_venue.VenueName__c} ({rome_venue.BillingCity})' if rome_venue.BillingCity is not None and venue['OldName'] != venue[ 'Name']: toupdate.append(venue) # pdh.to_excel(issue_rome_venues, 'issue_rome_venues.xlsx') pdh.to_excel(toupdate, 'EOS Venues Update.xlsx') update_records = [{ key: val for key, val in record.items() if key in {'Id', 'Name'} } for record in toupdate] success = [] failure = [] for i, chunk in enumerate(chunks(update_records, 10)): try: print(f'updating {i} chunk') sql.update('Venue', 'Id', chunk) success.extend(chunk) except: failure.extend(chunk) # To review # 0:{'Id': 1043, 'Name': 'Hartwall Arena (Helsinki)'} # 1:{'Id': 1306, 'Name': 'Telenor Arena (Oslo)'} return
def run_job(file_name, data_function, fix_function, usernames, do_fix=False, download_result=False, sf_sessions=None, sql_sessions=None): sessions = get_sessions(usernames, sf_sessions, sql_sessions) job_name = os.path.split(file_name)[1].replace('.py', '') return_strings = [] threads = [] results = [] total_len = 0 for sf, sql in sessions: thread = threading.new(data_function, sf, sql) threads.append((sf, sql, thread)) for sf, sql, thread in threads: data, vars, error_message = thread.result() vars = vars or {} if isinstance(data, dict) and not error_message: error_message = '\n'.join( (f'{sf.instance.upper()} {k}: ' + (error_message or f'{len(v)} problem records') for k, v in data.items())) total_len = sum((len(v) for v in data.values())) else: error_message = f'{sf.instance.upper()}: ' + ( error_message or f'{len(data)} problem records') total_len += len(data) if len(data) > 0: results.append((sf, sql, data, vars, error_message)) return_strings.append(error_message) if download_result: timenow = datetime.now().strftime("%Y-%m-%d %H.%M") output = {} for sf, sql, data, vars, error_message in results: if isinstance(data, dict): for k, v in data.items(): output[f'{sf.instance.upper()} {k} ({len(v)})'] = v else: output[f'{sf.instance.upper()} ({len(data)})'] = data to_excel(output, f'{timenow} {job_name} ({total_len}).xlsx') if do_fix and total_len > 0: print(job_name) for sf, sql, data, vars, error_message in results: if len(data) > 0: print(error_message) fix_function(sf, sql, vars, data) return '\n'.join(return_strings)
def to_excel(self, file_path=None, additional_sheets=None): file_path = file_path or self.excel_file_path flat = SalesforceMetadataFile.flatten_dict(self.tree) dir.make_dir(self.excel_files_folder_path, False) output = {self.metadata_type(): flat} if additional_sheets is not None: output.update(additional_sheets) pdh.to_excel(output, self.excel_file_path) print( f'Open report at the following location:\n{os.path.abspath(self.excel_file_path)}' )
def main(): issues = sf.select(""" select Event__c, Type__c, count(Id) cnt, sum(OfferRate__c) OfferRate, sum(InHouseRate__c) InHouseRate from ledgerentrybreakout__c where type__c IN('TSO','TSO Adjustment') and event__r.istouringapp__c = true and createddate >= LAST_YEAR and Event__c != NULL group by event__c, type__c having count(id) > 1 """) records = sf.select(""" SELECT Id, Event__r.TourLeg__r.Tour__c, Event__c, Event__r.EventTitle__c, Type__c, OfferRate__c, InHouseRate__c, CreatedDate FROM LedgerEntryBreakout__c WHERE Event__c IN @issues.Event__c AND Event__r.IsTouringApp__c = True AND type__c IN('TSO','TSO Adjustment') and createddate >= LAST_YEAR and Event__c != NULL ORDER BY Event__c, CreatedDate ASC """, return_type='flat') to_update = [] to_delete = [] for issue in issues: evt = issue['Event__c'] typ = issue['Type__c'] evt_records = [ i for i in records if i['Event__c'] == evt and i['Type__c'] == typ ] first = evt_records[0].copy() remaining = evt_records[1:] first['PriorOfferRate'] = first['OfferRate__c'] first['PriorInHouseRate'] = first['InHouseRate__c'] first['OfferRate__c'] = issue['OfferRate'] first['InHouseRate__c'] = issue['InHouseRate'] to_update.append(first) to_delete.extend(remaining) pdh.to_excel( { 'Issues': issues, 'Rome Records': records, 'To Update': to_update, 'To Delete': to_delete, }, 'TSO Dupes') if (to_update or to_delete) and prompt('Ready?', boolean=True): sf.delete(to_delete, batch_size=5) sf.update(to_update, batch_size=5) return
def main(): package_licenses = session.select( "SELECT Id, NamespacePrefix FROM PackageLicense WHERE PackageLicense.NamespacePrefix IN('Loop','SLCA2','GBLite')" ) users = session.select(""" SELECT Id, Name, Email, UserRole.Name, Profile.Name FROM User WHERE IsActive = True AND Profile.Name LIKE '%Internal%' """) existing_user_package_licenses = session.select( "SELECT UserId, PackageLicenseId FROM UserPackageLicense") df1 = pd.DataFrame(package_licenses) df2 = pd.DataFrame(users) df4 = df2[df2['Profile.Name'] == 'LNE Internal User'] df5 = df2[df2['Profile.Name'] == 'LNE Personnel Community'] df3 = pd.DataFrame(existing_user_package_licenses) df1.rename(columns={'Id': 'PackageLicenseId'}, inplace=True) df2.rename(columns={'Id': 'UserId'}, inplace=True) output2 = (pd.merge(df1, df2, how='cross').merge(df3, how='outer', on=['UserId', 'PackageLicenseId'], indicator=True)) output2 = (output2.loc[output2['_merge'] == 'left_only'].drop( columns='_merge')) output = pd.merge(left=df1, right=df2, how='cross') output = pd.merge(output, df3, how='outer', on=['UserId', 'PackageLicenseId'], indicator=True) output = output[output['_merge'] == 'left_only'].copy() output.drop(columns='_merge', inplace=True) if len(output) > 0: pdh.to_excel(output, 'Users that need Package Licenses.xlsx') # session.insert('UserPackageLicense', output.to_dict('records')) # records = session.select(""" # SELECT Id, Event__r.EventTitle__c, PublicOnSale__c # FROM EventDateTime__c # WHERE Event__r.BusinessPlanEvent__c <> NULL # AND Event__c NOT IN (SELECT Event__c FROM AdPlan__c) # AND PublicOnSale__c <> NULL # """, mode='simple') return
def main(sessions, do_fix=False): import pandas as pd import functions.pandas_helpers as pdh from classes.py_threading import ThreadPool, Thread threading = ThreadPool() sessions = { username: sf for username, sf in sessions.items() if username in instances_to_run } return_string = "" @threading.run_async def inner(sf): query = """ SELECT Id, Event__r.EventTitle__c, Type__c, Label__c, Capacity__c, ProjectedPaidTickets__c, Price__c FROM TicketScale__c WHERE CreatedDate >= THIS_YEAR AND IsTouringApp__c = True AND StageType__c IN ('Plan','Projection') AND (Capacity__c = NULL OR ProjectedPaidTickets__c = NULL OR Price__c = NULL) """ records = sf.select(query, mode='simple', return_type='dataframe') return records # Run for each Salesforce instance results = {sf.instance: inner(sf) for sf in sessions.values()} for sf in sessions.values(): result = results[sf.instance].result() if len(result) > 0: return_string += f"\n{sf.instance.upper()} has {len(result)} Ticket Scales with blank Capacity, ProjectedPaidTickets, or Price__c" if do_fix: if len(result) > 0: fix = lambda x: 0 if pd.isnull(x) else x for sf in sessions.values(): result = results[sf.instance].result() fixed = result.copy() fixed['Capacity__c'] = fixed['Capacity__c'].apply(fix) fixed['ProjectedPaidTickets__c'] = fixed[ 'ProjectedPaidTickets__c'].apply(fix) fixed['Price__c'] = fixed['Price__c'].apply(fix) pdh.to_excel({ 'Original': result, 'Fixed': fixed }, f'({sf.instance}) Ticket Scales with blank Capacity ProjectedPaidTickets or Price__c.xlsx' ) sf.add_bypass_settings() sf.update(fixed[[ 'Id', 'Capacity__c', 'ProjectedPaidTickets__c', 'Price__c' ]]) sf.remove_bypass_settings() return return_string
def example1(): events = uat.select( "SELECT Id, EventTitle__c, PrimaryHeadlinerArtist__c FROM Event__c LIMIT 100", return_type='flat') deals = uat.select( "SELECT Id, Artist__r.Name, GuaranteeAmount__c FROM Deal__c WHERE RecordType.Name = 'Artist' LIMIT 1000", return_type='flat') ledgerentries = uat.select( "SELECT Id, GLCode__c, CurrentFlash__c FROM LedgerEntry__c LIMIT 3000", mode='bulk', return_type='flat') pdh.to_excel([events, deals, ledgerentries], 'example1.xlsx')
def main(): session = Salesforce_API("*****@*****.**") records = session.select(""" SELECT Id, Event__r.EventTitle__c, Capacity__c, SellableCapacity__c, ProjectedPaidTickets__c, LastModifiedDate FROM TicketScale__c WHERE LastModifiedDate >= THIS_YEAR ORDER BY Event__r.EventYear__c, Event__c """, return_type='dataframe') issues = records.query("SellableCapacity__c < ProjectedPaidTickets__c") pdh.to_excel(issues, 'Ticket Scales with Projected greater than Sellable Capacity.xlsx') return
def main(): sf = Salesforce_API('*****@*****.**') sql = SQL_Server_API('EOS-prod') # sql = SQL_Server_API('EOS-pre-prod') offer_ids = sf.select('SELECT EOSId__c FROM Tour__c WHERE EOSId__c <> NULL', return_type='dataframe').EOSId__c.astype('int').tolist() # offer_ids = [54441, 57933, 57965, 58400, 58737, 58050, 57930, 57263, 53728, 55808] result, bycolumn = uk.diff_eos_offers_from_backup(sql, 'Prod 2021-08-16', offer_ids, True) result = {f'{key} ({len(val)})': val for key,val in result.items() if len(val) > 0} bycolumn = {key: {f'{f} ({len(df)})': df for f, df in dfs.items() if len(df) > 0} for key,dfs in bycolumn.items()} pdh.to_excel(result, 'UK Tour Data Changes.xlsx') for key, val in bycolumn.items(): if len(val) > 0: pdh.to_excel(val, f'UK Tour Data Changes By Column - {key}.xlsx') return
def find_venue_dupes(): rome_venues = sf.select(""" SELECT Id, EOSId__c, Name, VenueName__c, BillingCity FROM Account WHERE EOSId__c <> NULL AND RecordType.Name = 'Venue' """, mode='bulk') eos_venues = sql.query(""" SELECT 'Venue-' + CAST(Id AS VARCHAR) AS EOSId__c, Name FROM Venue """) eos_venues_by_name = {item['Name']: item for item in eos_venues} issues = [] for venue in rome_venues: issue1 = eos_venues_by_name.get(venue.VenueName__c, None) issue2 = eos_venues_by_name.get(venue.Name, None) if issue1 is not None and issue1['EOSId__c'] != venue['EOSId__c']: issues.append({ 'RomeId': venue.Id, 'RomeLink': f'https://{sf.simple.sf_instance}/{venue.Id}', 'Name': venue.Name, 'CurrRomeEOSId': venue.EOSId__c, 'ChangeRomeEOSIdTo': issue1['EOSId__c'], }) if issue2 is not None and issue2['EOSId__c'] != venue['EOSId__c']: issues.append({ 'RomeId': venue.Id, 'RomeLink': f'https://{sf.simple.sf_instance}/{venue.Id}', 'Name': venue.Name, 'CurrRomeEOSId': venue.EOSId__c, 'ChangeRomeEOSIdTo': issue2['EOSId__c'], }) if issues: pdh.to_excel(issues, f'{sf.instance} Rome EOS Venue Issues.xlsx') do_fix = len(issues) > 0 and prompt('Fix the venues?', boolean=True) if do_fix: update_records = pd.DataFrame(issues).rename( columns={ 'RomeId': 'Id', 'ChangeRomeEOSIdTo': 'EOSId__c' }).drop(columns=['Name']) sf.update(update_records) return
def example8(): xlsx = pd.ExcelFile( '/Users/daniel.hicks_1/Downloads/ExampleEventImportData.xlsx') import_data = { 'Event__c': pd.read_excel(xlsx, 'Event__c'), 'EventDateTime__c': pd.read_excel(xlsx, 'EventDateTime__c'), 'Deal__c': pd.read_excel(xlsx, 'Deal__c'), 'TicketScale__c': pd.read_excel(xlsx, 'TicketScale__c'), 'LedgerEntry__c': pd.read_excel(xlsx, 'LedgerEntry__c'), } model = sfc.SalesforceLiveNationModelCompute( uat, **import_data, set_null_datasets_to_empty_list=True) computed = model.compute_all() data = {key: val for key, val in computed.data2.items() if val is not None} pdh.to_excel(data, 'COMPUTED - ExampleEventImportData.xlsx') print('')
def touring_app_analysis(): gh.pull_remote(repo_path, ignore_errors=True) files = dir.get_files_list(f'{repo_path}/resources/react-sfdc') args = [(f, ) for f in files if f.endswith('.tsx') if 'SettlementExpenseGridRow' in f] results = mp.starmap(get_function_definitions, args, multi=False) all = [f._asdict() for f in itertools.chain.from_iterable(results)] pdh.to_excel(all, 'Touring App Functions') dupes = pdh.find_duplicates(all, 'body_nowhitespace', ['name'], ['name', 'file_path'], 5, False, True) out = { 'All': all, } for i, df in enumerate(dupes): out[f'List {i}'] = df pdh.to_excel(dupes, 'Touring App Functions Duplicate Analysis') return results
def inner(sf): lebs = threading.new(sf.select, """ SELECT Id, Event__r.EventTitle__c, LedgerEntry__r.Event__r.EventTitle__c, Event__r.IsTouringApp__c , LedgerEntry__r.Event__r.IsTouringApp__c, Event__c, GLCodePicklist__c, LedgerEntry__c, LedgerEntry__r.Event__c , CreatedDate, CreatedBy.Name, LastModifiedDate, LastModifiedBy.Name FROM LedgerEntryBreakout__c WHERE CreatedDate >= YESTERDAY AND ( Event__c = NULL OR Event__r.BusinessPlanEvent__c <> NULL OR IsTouringApp__c = TRUE ) """, mode='bulk', return_type='dataframe').fillna('') les = threading.new(sf.select, """ SELECT Id, Event__c, GLCode__c FROM LedgerEntry__c WHERE CreatedDate >= THIS_MONTH AND Event__c <> NULL AND ( Event__r.BusinessPlanEvent__c <> NULL OR IsTouringApp__c = TRUE) """, mode='bulk', return_type='dataframe') issues = lebs[lebs['Event__c'] != lebs['LedgerEntry__r.Event__c']] # les_map = les.set_index(['Event__c', 'GLCode__c']).to_dict('index') # def get_corrected_le(row): # tup = (row['Event__c'], row['GLCodePicklist__c']) # return les_map[tup]['Id'] if tup in les_map else None # if len(issues) > 0: # issues['Corrected LedgerEntry__c'] = issues.apply(get_corrected_le, axis=1) print(len(issues)) pdh.to_excel( issues, f'{sf.instance} LEBs with Event__c different than LE ({len(issues)}).xlsx' ) # sf.add_bypass_settings() # sf.delete(issues[issues['Event__c'] == ''].copy(), mode='bulk') # sf.remove_bypass_settings() return issues
def main(): pass events = sf.select(""" SELECT Id, Office__r.Name, Venue__r.Name, EventName__c, Status__c, Venue__r.EvenkoAACodePrefix__c, ShowCount__c, EventFirstDate__c, EvenkoAACode__c, InitialConfirmedDate__c FROM Event__c WHERE EvenkoAACode__c = NULL AND Venue__r.EvenkoAACodePrefix__c != NULL AND Status__c IN ('Confirmed','Flash Started','Flash Completed') AND Division__c = 'Evenko' """, return_type='flat') for item in events: s = item['Venue__r.EvenkoAACodePrefix__c'] + item['EventFirstDate__c'][2:].replace('-','') item['EvenkoAACode__c'] = s pdh.to_html(pd.DataFrame(events), 'New Evenko Codes') pdh.to_excel(pd.DataFrame(events), 'New Evenko Codes') with sf.bypass_settings(): sf.update(events) return
def main(username): session = Salesforce_API(username) query = """ SELECT Id FROM Account """ result = session.select(query) # For a bulk query, use: # result = session.select(query, mode='bulk') sheets_data = { 'Sheet1': result, 'Sheet2': pd.DataFrame() # can export multiple sheets in the same excel file } pdh.to_excel(sheets_data, 'Query Result.xlsx')
def rome_accounts(): match_threshold = 60 accounts = sf.select(""" SELECT Id, Name, VenueName__c, BillingCity, RecordType.Name FROM Account -- WHERE RecordType.Name IN ('Venue','Ticket Agency') """, return_type='dataframe') grouped = accounts.groupby('RecordType.Name') match_fields = {'Venue': ['VenueName__c', 'BillingCity']} @threading.run_async def get_dupe_analysis(recordtype, *args, **kwargs): print(f'Running duplicate check for {recordtype}') result = pdh.find_duplicates(*args, **kwargs) output = {} for i, df in enumerate(result): if len(df) > 0: df = df[df['m_%'] >= match_threshold].copy() df['MergeLink'] = df.apply(lambda row: ( f'https://{sf.simple.sf_instance}/merge/accmergewizard.jsp?goNext=+Next+&cid={row["Id"]}&cid={row["m_Id"]}' ), axis=1) output[f'{recordtype} {i+1}'] = df return output results = {} for recordtype, indexes in grouped.groups.items(): match_on = match_fields.get(recordtype, 'Name') results.update( get_dupe_analysis(recordtype, accounts.iloc[indexes], match_on, None, 'Id', num_matches=3)) pdh.to_excel(results, f'({sf.instance}) Rome Account Duplicate Analysis.xlsx') return
def venue_aa_code_update(): staged = sf.fupdate( f""" UPDATE Event__c SET EvenkoAACode__c = Venue__r.EvenkoAACodePrefix__c + format_date_yymmdd(EventFirstDate__c) + IF( Venue__r.EvenkoAACodePrefix__c == 'WE' , REPLACE(Venue__r.LNEMDID__c, 'AC', '') , '') WHERE Office__r.Division__c = 'Evenko' AND EvenkoAACode__c = NULL AND EventFirstDate__c != NULL AND Status__c = 'Confirmed' AND Venue__r.EvenkoAACodePrefix__c != NULL """, show_columns=['EventTitle__c', 'EventFirstDate__c', 'OfficeName__c']) pdh.to_excel(staged, 'Staged Evenko updates') if prompt('Update?', boolean=True): with sf.bypass_settings(): sf.update(staged)