def main():
sf.default_mode = 'bulk'
exp_record_type_id = sf.get_recordtype_map()[('LedgerEntry__c',
'Expenses')].Id
records = sf.select("""
SELECT Id, GLCode__c, RecordTypeId, RecordType.Name, HighLevelType__c
FROM LedgerEntry__c
WHERE GLCode__c = '50282'
AND (RecordType.Name != 'Expenses' OR HighLevelType__c != 'Expenses')
""",
return_type='generator')
def updated_records():
for record in records:
snapshot = record.copy()
record['RecordTypeId'] = exp_record_type_id
record['HighLevelType__c'] = 'Expenses'
if snapshot != record:
yield record
updated = list(updated_records())
prompt('Ready?')
with sf.bypass_settings():
sf.update(updated)
return
def clone_user(self, source_sf, target_sf, user, inactive_email=True):
source_id = user['Id']
del user['Id']
if inactive_email and not user['Email'].endswith('.inactive'):
user['Email'] = user['Email'] + '.inactive'
elif not inactive_email and user['Email'].endswith('.inactive'):
user['Email'] = user['Email'].replace('.inactive', '')
old_suffix = '.' + source_sf.instance
new_suffix = '.' + target_sf.instance
if user['Username'].endswith(old_suffix):
user['Username'] = user['Username'].replace(old_suffix, new_suffix)
if user['SourceSystemId__c'] in ('', None):
user['SourceSystemId__c'] = prompt(f'Set SourceSystemId for user: {user["Username"]}')
if prompt('Update Source system?', boolean=True):
source_sf.update([{'Id': source_id, 'SourceSystemId__c': user['SourceSystemId__c']}])
source_object_desc = source_sf.get_object_description('User')
target_object_desc = target_sf.get_object_description('User')
target_fields_map = {item.name: item for item in target_object_desc.fields}
source_fields = {item.name for item in source_object_desc.fields
if (item.name in target_fields_map
and target_fields_map[item.name]["createable"] == True
and (target_fields_map[item.name]["updateable"] == True
or (target_fields_map[item.name]["nillable"] == False and target_fields_map[item.name]["defaultedOnCreate"] == False))
)
or item.name == "Id"
}
user = {k:v for k,v in user.items() if k in source_fields}
permission_sets = source_sf.select("SELECT *, PermissionSet.Label FROM PermissionSetAssignment WHERE AssigneeId IN @source_id", return_type='flat')
perm_labels = [item['PermissionSet.Label'] for item in permission_sets]
target_permission_set_map = {
item.Label: item.Id
for item in
target_sf.select("SELECT Id, Label FROM PermissionSet WHERE Label IN @perm_labels")
}
for item in permission_sets:
item['PermissionSetId'] = target_permission_set_map[item['PermissionSet.Label']]
groups = source_sf.select("SELECT * FROM GroupMember WHERE UserOrGroupId IN @source_id", return_type='flat')
new_user = target_sf.upsert('User', [user], 'SourceSystemId__c')[0]
new_user_id = new_user['Id']
for item in permission_sets:
item['AssigneeId'] = new_user_id
for item in groups:
item['GroupOrUserId'] = new_user_id
if new_user.sf_operation == 'updated':
self.sf.delete(self.sf.select("SELECT Id FROM GroupMember WHERE UserOrGroupId = @new_user_id"))
self.sf.delete(self.sf.select("SELECT Id FROM PermissionSetAssignment WHERE AssigneeId = @new_user_id"))
target_sf.insert('PermissionSetAssignment', permission_sets)
target_sf.insert('GroupMember', groups)
return new_user
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 sosl_prompt(self, query, object_name, search_in_fields=None, return_fields=None):
records = self.sf.sosl(query, object_name, search_in_fields, return_fields)
selected_record_ids = []
if len(records) == 1:
selected_record_ids.append(records[0].Id)
elif len(records) > 1:
options = {record['Name']: record for record in records[0:9]}
options['None of the above'] = None
record_to_add = prompt(f'SOSL Search performed for "{query}". Which record would you like to clone?', options=options)
if record_to_add is not None:
selected_record_ids.append(record_to_add.Id)
return selected_record_ids
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():
# Picklist Error handling: {'statusCode': 'INVALID_OR_NULL_FOR_RESTRICTED_PICKLIST', 'message': 'Language: bad value for restricted picklist field: en_AU', 'fields': ['LanguageLocaleKey']}
usernames = [
# "[email protected]",
# "[email protected]",
# "[email protected]",
# "*****@*****.**",
# "*****@*****.**",
"*****@*****.**",
# "*****@*****.**",
]
sessions = [Salesforce_API(item) for item in usernames if item is not None]
# users = pd.read_excel('/Users/daniel.hicks_1/Documents/Rome/Rome Downloads/Import Sandbox Test Users.xlsx').query("Filter == True")
# users = pd.read_excel('/Users/daniel.hicks_1/Documents/Rome/Rome Downloads/Australia Master Data/PROD Release Users.xlsx').query("Filter == True")
users = pd.read_excel(
'/Users/daniel.hicks_1/Documents/Rome/Rome Downloads/UK Master Data/Rome UK Users.xlsx'
).query("Filter == True")
if prompt("Look up EOS Ids for these users?", boolean=True):
sql = SQL_Server_API(sql_creds)
emails_str = "', '".join(
users['Email'].str.strip().str.lower().tolist())
eos_users = (pd.DataFrame(
sql.query(
f"SELECT Id, Name, Email FROM IEmsUser WHERE Email IS NULL OR LOWER(Email) IN ('{emails_str}')"
)).assign(Email=lambda df: df['Email'].str.strip().str.lower()))
eos_email_map = eos_users.dropna(
subset=['Email']).set_index('Email').to_dict('index')
eos_name_map = eos_users.fillna('').query(
"Email == ''").drop_duplicates(
subset=['Name'], keep=False).set_index('Name').to_dict('index')
def get_eos_id(row):
email, name = str(row['Email']).strip().lower(), str(
row['Name']).strip()
matching_record = eos_email_map.get(email,
eos_name_map.get(name, None))
if matching_record:
return matching_record['Id']
return None
if len(eos_users) > 0:
users['EOSId__c'] = users.apply(get_eos_id, axis=1)
for session in sessions:
session.create_users(
users,
defaults={
# 'Country': 'United Kingdom'
# , 'DEFAULTCURRENCYISOCODE': 'GBP'
# , 'TIMEZONESIDKEY': 'GMT'
})
def set_sourcesystemid(row, counter=None):
val = '{}{}'.format(row.FirstName[0], row.LastName).replace(' ','').replace("'",'') + ('' if counter is None else str(counter))
if val in usermap_sourcesystemid:
sf_email = usermap_sourcesystemid[val].Email
if sf_email == row.Email or sf_email.replace('.invalid','') == row.Email.replace('.invalid','') or sf_email.replace(f'.{self.sf.instance}','') == row.Email.replace(f'.{self.sf.instance}',''):
return val
else:
resp = prompt(f'({self.sf.instance}) For user {row.FirstName} {row.LastName}, found a User record with matching SourceSystemId__c, but a different Email. Should this be matched?\nFile: {row.Email}\nSalesforce: {sf_email}', boolean=True)
if resp:
return val
else:
return set_sourcesystemid(row, (2 if counter is None else counter + 1))
else:
return val
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 main():
# a = Cache.set('abc', {'a':1})
# b = Cache.set(('abc', 123), {'a':1})
def func_value():
print('waiting...')
time.sleep(5)
print('done!')
return 12
# c = Cache.define('defg', func_value, calc_on_func_change=func_value)
d = Cache.define(
'defg',
func_value,
calc_if_true=lambda: prompt('Do you want to re-calculate the value?',
boolean=True))
return
def main(can_terminate=False):
folders = get_folder_info()
# deployment_folders = list(folders.keys()) + (['Exit'] if can_terminate else [])
folder_options = {
f'{f.name} ({len(f.scripts)})': f
for f in folders.values()
}
if can_terminate:
folder_options['Exit'] = 'Exit'
while True:
folder = prompt(
'Please select the script set you would like to execute:',
options=folder_options)
if folder == 'Exit': break
username_group = select_username_group()
for script in folder.scripts:
console.new_line(f'Executing {script.name}...')
run_job(script.path, script.task, username_group)
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)
def main():
# clone_emails = [
# '*****@*****.**',
# '*****@*****.**',
# '*****@*****.**',
# ]
# users = src.select("""
# SELECT *.writable
# FROM User
# WHERE Email IN @clone_emails
# """, return_type='flat')
users = src.select("""
SELECT *.writable
FROM User
WHERE CreatedDate = TODAY
-- WHERE UserRole.Name = 'System Administration'
-- AND (Name LIKE '%Monica Serrato%'
-- OR Name LIKE '%Diaz%'
-- OR Name LIKE '%Oscar%'
-- OR Name LIKE '%Carlos%'
-- )
""", return_type='flat')
new_users = []
for user in users:
new_user = tar.clone_user(src, tar, user, inactive_email=False)
new_users.append(new_user)
if prompt('Reset user passwords?', boolean=True):
for user in new_user:
tar.reset_user_password(user)
return
def select_username_group(username_group=None):
if not username_group:
username_group = prompt('Which sandbox group should be deployed to?',
options=list(username_groups.keys()))
return username_group
def main():
sf = Salesforce_API('*****@*****.**')
events = sf.select(f"""
SELECT Id, TourLeg__r.Tour__r.TourHeadliner__c, TourLeg__r.Tour__r.CurrencyIsoCode, CurrencyIsoCode, TourExchangeRate__c, RecordType.Name, EventTitle__c, PooledHeadlinerInclusiveGuarantees__c
FROM Event__c
WHERE IsTouringApp__c = True
AND TourLeg__r.Tour__r.RecordType.Name = 'Booking'
AND RecordType.Name = 'Sell Off'
""",
return_type='dataframe')
existing_deals = sf.select(f"""
SELECT Id, Event__c, RecordType.Name, Artist__c, CurrencyIsoCode, DealExchangeRate__c, RecordType.Name, Type__c, DealType__c, DealCalculation__c, GuaranteeAmount__c
FROM Deal__c
WHERE Event__r.IsTouringApp__c = True
AND Event__r.TourLeg__r.Tour__r.RecordType.Name = 'Booking'
AND Event__r.RecordType.Name = 'Sell Off'
""",
return_type='dataframe')
new_deals = (
events[[
'Id', 'PooledHeadlinerInclusiveGuarantees__c',
'TourLeg__r.Tour__r.CurrencyIsoCode', 'CurrencyIsoCode',
'TourExchangeRate__c', 'TourLeg__r.Tour__r.TourHeadliner__c'
]].rename(
columns={
'Id': 'Event__c',
# 'PooledHeadlinerInclusiveGuarantees__c': 'GuaranteeAmount__c',
'CurrencyIsoCode': 'EventCurrencyIsoCode',
'TourLeg__r.Tour__r.CurrencyIsoCode': 'CurrencyIsoCode',
'TourExchangeRate__c': 'EventTourExchangeRate__c',
'TourLeg__r.Tour__r.TourHeadliner__c': 'Artist__c',
}).assign(
**{
'RecordType.Name': 'Artist',
'Type__c': 'Primary Headliner',
'DealType__c': 'Flat',
'DealCalculation__c': 'Guarantee Only',
}))
new_deals[
'DealExchangeRate__c'] = 1 / new_deals['EventTourExchangeRate__c']
new_deals['GuaranteeAmount__c'] = (
new_deals['PooledHeadlinerInclusiveGuarantees__c'] /
new_deals['DealExchangeRate__c']).astype(float).round(2)
new_deals['RecordTypeId'] = '01236000000nyfQAAQ'
if len(existing_deals) > 0:
diff = pdh.diff(
existing_deals,
new_deals,
on=['Event__c'],
)
# changed = new_deals[(new_deals['Event__c'] in diff['Event__c'].tolist())]
print('')
# merged = existing_deals.merge(new_deals, on='Event__c', indicator=True)
# diff = pdh.diff()
pdh.to_excel(
{
'eventsbackup': events,
'existing_deals': existing_deals,
'new_deals': new_deals
# , 'merged': merged
},
f'({sf.instance}) Inspect Artist Deals Backfill.xlsx')
if prompt("Update?", boolean=True):
sf.add_bypass_settings()
sf.insert('Deal__c', new_deals)
sf.remove_bypass_settings()
return
def main(sessions, do_fix=False):
first_session = sessions[0]
for s in sessions:
s.print_messages = False
session = first_session
files_list = [
item for item in listdir("./resources/sf_update_files")
if isdir("./resources/sf_update_files/" + item) == False
and item != ".DS_Store" and not item.startswith("~$")
]
files_list = sorted(files_list,
key=lambda item: -(path.getmtime(
"./resources/sf_update_files/" + item)))
selected_file = prompt('\nThe following update files are available:',
files_list)
rows = []
# object_name = lastSettings["objectName"] if file_selection_input == "0" else None
data_sheet_name = None
# Settings defaults
settings = ObjDict({
"BATCH_SIZE": 2000,
"OPERATION": None,
"DO_UPSERT": None # Should be deprecated... use OPERATION instead
,
"BYPASS_AUTOMATION": None,
"EXT_ID": None,
"PARALLEL_CONCURRENCY": True,
"BULK_API_MODE": True
})
# Get data and settings from file
if selected_file.endswith(".csv"):
with open("./resources/sf_update_files/" + selected_file,
'r',
encoding='utf-8-sig') as file:
reader = csv.DictReader(file)
for row in reader:
rows.append(row)
elif selected_file.endswith(".xlsx"):
file_path = './resources/sf_update_files/{}'.format(selected_file)
xlsx_file = pd.ExcelFile(file_path)
sheets = xlsx_file.sheet_names
data_sheet_name = sheets[0]
datadf = pd.read_excel(xlsx_file, data_sheet_name)
settingsdf = pd.read_excel(
xlsx_file, 'Settings') if 'Settings' in sheets else None
# Fill nulls with blank strings
datadf = datadf.fillna(value='')
# Set column names to strings
datadf.columns = datadf.columns.astype(str)
# Set timestamp columns to string
for col in datadf.select_dtypes(include=['datetime64']).columns.values:
datadf[col] = datadf[col].astype(str)
# Set numeric columns to zero-trimmed string
for col in datadf.select_dtypes(
include=['int64', 'float64']).columns.values:
datadf[col] = datadf[col].astype(float).astype(str)
datadf[col] = datadf[col].str.replace('.0', '', regex=False)
rows = datadf.to_dict('records')
if settingsdf is not None:
inputsettings = settingsdf.set_index('Field').to_dict('index')
inputsettings = {
key: val['Value']
for key, val in inputsettings.items()
}
settings.update(inputsettings)
# xlsx_file = xlrd.open_workbook("./resources/sf_update_files/" + selected_file)
# sheets = xlsx_file.sheet_names()
# data_sheet = xlsx_file.sheet_by_index(0)
# data_sheet_name = sheets[0]
# settings_sheet = xlsx_file.sheet_by_name("Settings") if "Settings" in sheets else None
# headers = [str(v) for v in data_sheet.row_values(0)]
# for row_num in range(1,data_sheet.nrows):
# new_row = dict()
# src_row = data_sheet.row_values(row_num)
# for col_num in range(0,len(headers)):
# new_row[headers[col_num]] = src_row[col_num]
# rows.append(new_row)
# if settings_sheet is not None:
# for row_num in range(0, settings_sheet.nrows):
# src_row = settings_sheet.row_values(row_num)
# settings[src_row[0]] = True if src_row[1] == 1 else False if src_row[1] == 0 else src_row[1]
pass
else:
print("No file")
# Handle for deprecated DO_UPSERT setting
if settings.DO_UPSERT is True and settings.OPERATION is None:
settings.OPERATION = 'UPSERT'
operation = str(
settings.OPERATION).lower() if settings.OPERATION is not None else None
# Try to detect Object name from record Ids in file
# If no record Ids are present, try to use the name of the tab in the file we are loading
# If no match is found, prompt the user for the object name
rows_with_id = [r for r in rows if "Id" in r and r["Id"] != ""]
source_field_names = {key for key in rows[0].keys()}
if len(rows_with_id) > 0:
object_name = session.get_object_name(rows_with_id[0]["Id"])
else:
all_object_names = [
item["name"] for item in session.get_org_description()["sobjects"]
]
object_names_in_file_name = [
item for item in all_object_names
if " " + item + " " in selected_file
]
if data_sheet_name in all_object_names:
object_name = data_sheet_name
elif len(object_names_in_file_name) == 1:
object_name = object_names_in_file_name[0]
else:
object_name = prompt(
"\nWhat object are the records in this file for?")
# lastSettings["objectName"] = object_name
# with open(settingsLoc, 'w') as outfile:
# json.dump(lastSettings, outfile)
try:
object_desc = session.get_object_description(object_name)
except:
raise
if operation is None or operation == 'upsert':
source_fields_relationship_names = {
f[0:f.find('.')]
for f in source_field_names if '.' in f
}
upsert_matches = [{
"field": item["name"]
} for item in object_desc.fields if item["externalId"] == True
and item["name"] in source_field_names]
possible_reference_upsert_matches = [
{
"referenceTo":
item.referenceTo[0],
"match_string":
item.relationshipName + ".",
"reference_object_descs": [
threading.new(session.get_object_description, r)
for r in item.referenceTo
]
} for item in object_desc.fields if len(item.referenceTo) > 0
and item.relationshipName in source_fields_relationship_names
]
reference_upsert_matches = []
for field in source_field_names:
for match in possible_reference_upsert_matches:
if field.startswith(match["match_string"]):
upsert_match_object_desc = session.get_object_description(
match["referenceTo"])
reference_upsert_matches.extend(
[{
"field": field,
"matching_object": match["referenceTo"],
"matching_field": item["name"]
} for item in upsert_match_object_desc["fields"]
if item["externalId"] == True
and item["name"] == field[field.find(".") + 1:]])
if len(upsert_matches) > 0:
print(
"\nFound the following External ID references for this object: {}"
.format(", ".join([item["field"] for item in upsert_matches])))
if len(reference_upsert_matches) > 0:
print(
"Found the following External ID references for a lookup object: {}"
.format(", ".join(
[item["field"] for item in reference_upsert_matches])))
if len(upsert_matches) + len(
reference_upsert_matches) > 0 and settings.OPERATION is None:
if prompt("\nWould you like to upsert?", boolean=True):
operation = 'upsert'
if operation is None:
if ((len(rows_with_id) > 0) == False # If true, cannot do insert
and (len(rows_with_id) != len(rows))
== False): # If true, cannot do update
operation = prompt("\nWhat operation would you like to perform?",
options={
'Insert': 'insert',
'Update': 'update'
})
# lastSettings["doUpsert"] = do_upsert
# with open(settingsLoc, 'w') as outfile:
# json.dump(lastSettings, outfile)
if operation == 'upsert':
upsert_matches.insert(0, {"field": "Id"})
self_external_id = settings.EXT_ID
if self_external_id is None:
if len(upsert_matches) == 1:
self_external_id = upsert_matches[0]["field"]
else:
self_external_id = prompt(
"\nWhat ID field would you like to use for upsert?",
options=[item['field'] for item in upsert_matches])
# print("\nWhat ID field would you like to use for upsert?")
# counter = 1
# print_str = ""
# for item in upsert_matches:
# print_str += "{}) {} \n".format(counter, item["field"])
# counter += 1
# print(print_str)
# self_external_id = upsert_matches[int(input())-1]["field"]
if len([
item for item in upsert_matches
if item["field"] == self_external_id
]) == 0:
print(
"External ID field '{}' does not appear in the selected file name."
.format(self_external_id))
raise
fields_to_update = [
item["name"] for item in object_desc["fields"]
if item["name"] in rows[0] and item["updateable"] == True
and item["calculated"] == False and item["autoNumber"] == False
]
fields_to_update.extend(
[item["field"] for item in reference_upsert_matches])
fields_to_ignore = [
item for item in rows[0] if item not in fields_to_update
]
rows_to_update = [{
f: v
for (f, v) in r.items() if f == "Id" or f in fields_to_update
or f in [mat["field"] for mat in upsert_matches]
} for r in rows]
mode = 'bulk' if settings.BULK_API_MODE == True else 'simple'
if settings.BYPASS_AUTOMATION is None:
settings.BYPASS_AUTOMATION = prompt(
f"\nDo you need to bypass automation for this {operation}?",
boolean=True)
print("Selected file: {}".format(selected_file))
print("Operation: {}".format(operation.title()) +
(" (on {})".format(self_external_id
) if self_external_id is not None else ""))
print("Table: {}".format(object_name))
print("Bypass automation: {}".format(settings.BYPASS_AUTOMATION))
print("Fields to update: {}".format(", ".join(fields_to_update)))
print("Fields to ignore: {}".format(", ".join(fields_to_ignore)))
do_operation_confirmation = prompt(
f"\nWill {operation} {len(rows)} records. Are you sure?", boolean=True)
# Now that all settings have been determined, perform the insert/update/delete in ALL sessions that were passed into the process
# It is assumed that the system metadata that was queried for the 1st session is the same in the other sessions
concurrency = "Parallel" if settings.PARALLEL_CONCURRENCY else "Serial"
if do_operation_confirmation:
settings.BYPASS_AUTOMATION = settings.BYPASS_AUTOMATION
def perform_crud_operation(session):
if settings.BYPASS_AUTOMATION:
session.add_bypass_settings()
else:
session.remove_bypass_settings()
if operation == "insert":
job_result = session.insert_records(object_name,
rows_to_update,
concurrency=concurrency)
elif operation == "update":
job_result = session.update_records(rows_to_update,
concurrency=concurrency)
elif operation == "upsert":
job_result = session.upsert_records(object_name,
rows_to_update,
self_external_id,
concurrency=concurrency,
mode=mode)
else:
pass
if job_result is not None and "status" in job_result and job_result[
"status"]["numberRecordsFailed"] != "0":
print("{} records failed.".format(
job_result["status"]["numberRecordsFailed"]))
# results = session.get_job_results(job_result)
# session.write_file("./resources/sf_update_files/error_logs/error_{}".format(selected_file.replace(".xlsx", ".csv")), results)
if settings.BYPASS_AUTOMATION:
session.remove_bypass_settings()
for session in sessions:
threading.new(perform_crud_operation, session)
threading.wait()
print("\nOperation complete!")
else:
print("\nTerminated.")
pass
def hourly():
jobs = get_folder_jobs('hourly')
return threading.new(run, "HOURLY", jobs)
def daily():
jobs = get_folder_jobs('daily')
return threading.new(run, "DAILY", jobs)
get_new_sessions()
p = prompt(
"Run jobs now?",
multiselect=True,
options={
'No': lambda: 0,
'Every 10 Minutes': every10min,
# 'Hourly': hourly,
'Daily': daily,
})
for f in p:
f()
def start_10_min_jobs():
schedule.every().hour.at(':00').until('17:30').do(every10min)
schedule.every().hour.at(':10').until('17:30').do(every10min)
schedule.every().hour.at(':20').until('17:30').do(every10min)
schedule.every().hour.at(':30').until('17:30').do(every10min)
schedule.every().hour.at(':40').until('17:30').do(every10min)
schedule.every().hour.at(':50').until('17:30').do(every10min)
def main():
sf = Salesforce_API('*****@*****.**')
prod = Salesforce_API('*****@*****.**')
reports_path = './resources/report_type_editor/'
report_type_name = 'Ticket_Scales_Extended'
# file_path = f'{reports_path}{report_type_name}.reportType.xlsx'
report_types = {report_type_name: ReportTypeFile(sf, report_type_name)}
current_report_type = report_types[report_type_name]
# current_report_type.sf_retrieve()
# current_report_type.to_excel()
while True:
options = [
'SF Download ReportType',
'SF Upload ReportType' if current_report_type.tree is not None else '',
'Write ReportType to Excel' if current_report_type.tree is not None else '',
'Read ReportType from Excel' if os.path.exists(current_report_type.excel_file_path) else '',
'Command Line Editor' if current_report_type.tree is not None else '',
'Remove Fields not in Production' if current_report_type.tree is not None else '',
'Change Report to Edit',
'Alphabetize Fields' if current_report_type.tree is not None else '',
]
next_action = prompt(f"\nCurrent Report Type: {report_type_name}", options=[s for s in options if s != ''])
if next_action == 'SF Download ReportType':
print('Downloading...')
current_report_type.sf_retrieve()
if next_action == 'SF Upload ReportType':
current_report_type.sf_deploy()
if next_action == 'Write ReportType to Excel':
current_report_type.to_excel()
if next_action == 'Read ReportType from Excel':
current_report_type.from_excel()
if next_action == 'Change Report to Edit':
name = prompt('What Report Type needs to be edited?')
if name not in report_types:
report_types[name] = ReportTypeFile(sf, name)
current_report_type = report_types[name]
# file_path = f'{reports_path}{report_type_name}.reportType.xlsx'
if next_action == 'Remove Fields not in Production':
for obj in current_report_type.meta().join_objects:
prod_fields = {item.name: item for item in prod.get_object_description(obj.name).fields}
for field in obj.fields:
if field.name not in prod_fields:
print(f'Removed {obj.name}.{field.name}')
current_report_type.remove_field(obj.relationship, field.name)
for obj in current_report_type.meta().lookup_objects:
prod_fields = {item.name: item for item in prod.get_object_description(obj.object_name).fields}
for section, f in current_report_type.iter_tree_fields():
lookup_name, field_name = f.field[:f.field.rfind('.')], f.field[f.field.rfind('.')+1:]
if lookup_name == obj.lookup_name and field_name not in prod_fields:
print(f'Removed {f.field}')
current_report_type.remove_field(f.table, f.field)
current_report_type.meta()
if next_action == 'Command Line Editor':
while True:
options2 = [
'Add fields',
'Add a section',
'Exit Command Line Editor',
]
next_action2 = prompt(f"\nEditing {report_type_name}:", options=[s for s in options2 if s != ''])
if next_action2 == 'Add fields':
threading.new(current_report_type.meta)
obj = prompt('Which Object?', options={
item['relationship']: item for item in current_report_type.meta().join_objects
})
fields = prompt('Which fields?', multiselect=True, options=[
item.name for item in obj['unused_fields'].values()
])
section_name = prompt(f'{len(fields)} field(s) selected. Add to which section?', options=[s['masterLabel'] for s in current_report_type.meta().sections])
# print("DEV: obj['name'] needs to be modified to be the full reference (i.e. 'Tour__c.TourLegs__r.Events__r.LedgerEntries__r')")
new_fields = [
(section_name, obj['relationship'], f, None)
for f in fields
]
current_report_type.append_fields(new_fields)
if next_action2 == 'Add a section':
section_name = prompt('What would you like to name the section?')
current_report_type.append_section(section_name)
if next_action2 == 'Exit Command Line Editor':
break
if next_action == 'Alphabetize Fields':
current_report_type.alphabetize_fields()
import json
import re
import pandas as pd
import numpy as np
from functions.prompt import prompt
from classes.salesforce_api import Salesforce_API
from classes.py_threading import ThreadPool
threading = ThreadPool()
from classes.obj_dict import ObjDict
script, instance = ["", ""] if len(argv) == 1 else argv
if instance == "":
instance = prompt("What Salesforce instance do you want to use?")
settingsLoc = "./resources/sf_CRUD_Operation_Memory.json"
instances = [s.strip() for s in str(instance).split(',')]
usernames = [
"*****@*****.**" +
("" if instance == "lne" or instance == "prod" else "." + instance)
for instance in instances
]
sessions = [Salesforce_API(username) for username in usernames]
# lastSettings = json.load(open(settingsLoc, "r"))
def main(sessions, do_fix=False):
def dataload_file(self, file_path):
object_name = None
external_id_field_name = None
# Settings defaults
settings = ObjDict({
"BATCH_SIZE": 2000
, "OPERATION": None
, "BYPASS_AUTOMATION": None
, "CONCURRENCY": 'Parallel'
, "MODE": 'simple'
, "DO_BACKUP": True
, "DISABLE_ACCOUNT_DUPLICATE_RULE": False
})
if file_path.endswith('.csv'):
df = pd.read_csv(file_path)
data_sheet_name = None
elif file_path.endswith('.xlsx'):
xlsx = pd.ExcelFile(file_path)
all_sheets = xlsx.sheet_names
option_sheets = [s for s in all_sheets if s != 'Settings']
data_sheet_name = option_sheets[0] if len(option_sheets) == 1 else prompt('Which sheet should be loaded?', options=option_sheets)
df = pd.read_excel(xlsx, data_sheet_name)
df.columns = df.columns.astype(str)
if 'Settings' in all_sheets:
df_settings = pd.read_excel(xlsx, 'Settings')
inputsettings = {
key:val['Value']
for key,val in df_settings.set_index('Field').to_dict('index').items()
}
settings.update(inputsettings)
assert type(settings.BATCH_SIZE) is int
assert settings.OPERATION.lower() in (None,'insert','update','upsert','delete','undelete')
assert settings.BYPASS_AUTOMATION in (True,False)
assert settings.CONCURRENCY in ('Parallel','Serial')
assert settings.MODE in ('simple','bulk')
else:
raise Exception('Can only parse files with a .csv or .xlsx extension')
instances = prompt('For which instance(s) should this file be loaded?', multiselect=True, options={
(key[key.rindex('.')+1:] + (' prod' if val['sandbox'] == 'False' else '')).strip(): val
for key, val in self.sf.instance_credentials.items() if 'security_token' in val
})
sessions = [Salesforce_API(c) for c in instances]
# Try to detect Object based on records in the file
# If no record Ids are present, try to use the name of the tab in the file
if 'Id' in df:
id_list = df[['Id']].fillna('').query("Id != ''")['Id'].to_list()
if len(id_list) > 0:
try:
object_name = sessions[0].get_object_name(id_list)
except: pass
if object_name is None:
all_object_names = {item.name for item in sessions[0].get_org_description().sobjects}
if data_sheet_name is not None and data_sheet_name in all_object_names:
object_name = data_sheet_name
if object_name is None:
object_name = prompt("\nWhat object are the records in this file for?", expected=all_object_names)
if 'RecordType.Name' in df and 'RecordTypeId' not in df:
df['RecordTypeId'] = df['RecordType.Name'].apply(lambda x: self.get_recordtype_map()[(object_name, x)].Id if (object_name, x) in self.get_recordtype_map() else None)
object_desc = threading.new(sessions[0].get_object_description, object_name)
operation = str(settings.OPERATION).lower() if settings.OPERATION is not None else None
if operation is None:
all_records_have_id = 'Id' in df and len(df) == len(df.fillna('').query("Id != ''"))
operation_options = [
'insert',
'upsert',
'update' if all_records_have_id else None,
'delete' if all_records_have_id else None,
'undelete' if all_records_have_id else None,
]
operation = prompt('What operation needs to be performed?', options=[o for o in operation_options if o is not None])
if operation == 'upsert':
external_id_fields = [f.name for f in object_desc.result().fields if f.externalId is True and f.name in df.columns.values]
if len(external_id_fields) == 1:
external_id_field_name = external_id_fields[0]
elif len(external_id_fields) == 0:
external_id_field_name = 'Id'
else:
external_id_field_name = prompt('Which field would you like to use as the External ID?', options=external_id_fields)
if 'RecordTypeId' not in df and operation in ('insert','upsert') and len(object_desc.result().recordTypeInfos) > 1:
default_record_type = [item.name for item in object_desc.result().recordTypeInfos if item.defaultRecordTypeMapping is True][0]
if prompt(f'No RecordTypeId was specified. New records will be defaulted to RecordType = "{default_record_type}". Proceed?', boolean=True) is False:
raise Exception('Terminated - No RecordTypeId specified')
if settings.DO_BACKUP and len(df) > 5000:
if not prompt(f"Large record count: {len(df)} records. Do you want to run a backup? (Normally automatic)", boolean=True):
settings.DO_BACKUP=False
valid_fields = sessions[0]._get_valid_fields_for_operation(operation, object_name, first_row=df.iloc[0].to_dict(), external_id_field_name=external_id_field_name)
fields_to_update = [f for f in df.columns.values if f.lower() in valid_fields]
fields_to_ignore = [f for f in df.columns.values if f.lower() not in valid_fields]
custom_settings_options = {
'Yes': True,
'No': False,
'Set Data Admin Only': {'dataadmin': True, 'automation': False, 'pbflow': False, 'fc': False, 'multicurrency': False},
'Custom': self.sf.lndatautil._configure_GeneralSettings__c
}
settings.BYPASS_AUTOMATION = settings.BYPASS_AUTOMATION if settings.BYPASS_AUTOMATION is not None else prompt(f"\nDo you need to bypass automation for this {operation}?", options=custom_settings_options, call_options=True)
while True:
external_id_lookups_text = ', '.join([f for f in fields_to_update if '.' in f])
operation_message_end_text = f" on {external_id_field_name}" if operation == 'upsert' else ''
operation_message_end_text += f" and External Id lookup(s) for: {external_id_lookups_text}" if len(external_id_lookups_text) > 0 else ''
print(f"Selected file: {file_path[file_path.rindex('/')+1:]}")
print(f"Operation: {operation.title()}{operation_message_end_text}")
print(f"Table: {object_name}")
print(f"Bypass Settings: {settings.BYPASS_AUTOMATION}")
print(f"Job Settings: Mode: {settings.MODE}, Batch size: {settings.BATCH_SIZE}, Concurrency: {settings.CONCURRENCY}")
print(f"Fields to update: {', '.join(fields_to_update)}")
print(f"Fields to ignore: {', '.join(fields_to_ignore)}")
operation_confirmation = prompt(f"\nWill {operation} {len(df)} records. Are you sure? Press 'M' to modify settings. (Y/N/M)", expected=['Y','N','M'])
if operation_confirmation == 'M':
options = {
"BYPASS_AUTOMATION": {'options':custom_settings_options, 'call_options': True},
"BATCH_SIZE": {'expected_type': int},
"CONCURRENCY": {'expected': ['Parallel','Serial','']},
"MODE": {'expected': ['simple','bulk','']},
"DISABLE_ACCOUNT_DUPLICATE_RULE": {'options': [True, False]},
}
while True:
modify_options = ['All'] + list(options.keys()) + ['Done']
selected_option = prompt('Modify:', modify_options)
if selected_option == 'All':
for option, val in options.items():
new = prompt(f'Set setting {option} (currently {settings[option]}): ', **val)
settings[option] = new if new != '' else settings[option]
break
elif selected_option == 'Done':
break
else:
option, val = selected_option, options[selected_option]
new = prompt(f'Set setting {option} (currently {settings[option]}): ', **val)
settings[option] = new if new != '' else settings[option]
continue
break
# Now that all settings have been determined, perform the insert/update/delete in ALL sessions that were passed into the process
# It is assumed that the system metadata that was queried for the 1st sf is the same in the other sessions
if operation_confirmation == 'Y':
def perform_operation(sf):
params = {key.lower(): val for key,val in settings.items() if key not in ('BYPASS_AUTOMATION')}
if settings.BYPASS_AUTOMATION is True:
sf.add_bypass_settings()
elif settings.BYPASS_AUTOMATION is False:
sf.remove_bypass_settings()
else:
sf.add_bypass_settings(**settings.BYPASS_AUTOMATION)
if settings.DISABLE_ACCOUNT_DUPLICATE_RULE is True:
self.toggle_duplicate_rule('Account.', False)
if operation == "insert":
job_result = sf.insert(object_name, df, **params)
elif operation == "update":
job_result = sf.update(df, **params)
elif operation == "upsert":
job_result = sf.upsert(object_name, df, external_id_field_name, **params)
elif operation == "delete":
job_result = sf.delete(df, **params)
elif operation == "undelete":
job_result = sf.undelete(df, **params)
num_failed_records = (
len([item for item in job_result if 'Error' in item.sf_result]) if settings.MODE == 'simple'
else int(job_result["status"]["numberRecordsFailed"]) if job_result is not None and "status" in job_result
else 0
)
if num_failed_records > 0:
print(f"({sf.instance}) {num_failed_records} records failed.")
if settings.BYPASS_AUTOMATION is not False:
sf.remove_bypass_settings()
if settings.DISABLE_ACCOUNT_DUPLICATE_RULE is True:
self.toggle_duplicate_rule('Account.', True)
return job_result
def get_backup(sf, id_field):
if not (settings.DO_BACKUP and operation in {'upsert','update','delete'}):
return None
try:
backup_ids = df[id_field]
mode = 'simple' if object_name != 'Event__c' else 'bulk'
sf.console.new_line('Running Backup...')
return sf.select(f"SELECT * FROM {object_name} WHERE {id_field} != NULL AND {id_field} IN @backup_ids", mode=mode, return_type='dataframe')
except:
return None
id_field = external_id_field_name if operation == 'upsert' else 'Id'
results = ObjDict()
for sf in sessions:
backup = get_backup(sf, id_field)
job_thread = threading.new(perform_operation, sf)
results[sf.instance] = ObjDict({'backup': backup, 'job': job_thread})
for result in results.values():
result.job = result.job.result()
if result.backup is not None:
ordered_cols = list({f:f for f in (['Id', id_field] + result.job.sent_fields + list(result.backup.columns.values))})
result.backup = result.backup[ordered_cols]
print("\nAll operations complete!")
return results
else:
print("\nTerminated.")
def main(can_quit=False):
dataloader_files_path = os.path.abspath('./resources/dataloader')
print(f'\nListening... drop files in this directory to dataload or query:\n{dataloader_files_path}')
print(f'\nFor query text files, include the prefix "--bulk" at the beginning of the file if you would like to use the Bulk API')
time.sleep(0.2)
dir.make_dir(dataloader_files_path, delete_first=False)
def get_file_name(path):
return os.path.split(path)[1]
def move(file_path, to_folder):
timenow = datetime.datetime.now().strftime("%Y-%m-%d %H.%M")
dir.make_dir(f'{dataloader_files_path}/{to_folder}', delete_first=False)
file_name = get_file_name(file_path)
new_path = f'{dataloader_files_path}/{to_folder}/{timenow} {file_name}'
os.rename(file_path, new_path)
def write_result(to_folder, file_name, results):
timenow = datetime.datetime.now().strftime("%Y-%m-%d %H.%M")
output_file_path = f'{dataloader_files_path}/{to_folder}/{timenow} {file_name}.csv'
dir.make_dir(f'{dataloader_files_path}/{to_folder}', delete_first=False)
pd.DataFrame(results).to_csv(output_file_path, index=False)
return output_file_path
while True:
files = [f for f in os.listdir(dataloader_files_path) if os.path.isfile(f'{dataloader_files_path}/{f}') and not f.startswith('.')]
if len(files) > 0:
options = {**{f: f'{dataloader_files_path}/{f}' for f in files if not f.startswith('~')}, **{'Refresh': None}}
if can_quit: options.update({'Quit': 'QUIT'})
file_path = prompt('\nWhich file would you like to load?', options)
if file_path == 'QUIT':
break
if file_path is None:
continue
file_name = get_file_name(file_path)
if file_name.endswith('.csv') or file_name.endswith('.xlsx') or file_name.endswith('.xls'):
results = None
try:
results = sf.dataload_file(file_path)
sf.console.clear()
except Exception as e:
traceback.print_exc()
next_options = ['Keep in working folder', 'Move to Archive',]
if results is not None:
for instance, result in results.items():
if result.backup is not None:
threading.new(write_result, 'backups', f'({instance}) {result.job.object_name} BACKUP ({len(result.backup)})', result.backup)
if result is not None and hasattr(result.job, 'errors') and len(result.job.errors) > 0:
next_options.append('View errors')
threading.new(write_result, 'errors', f'({instance}) {file_name} ({len(result.job.errors)})', result.job.errors)
print(f'({instance}) Errors have been written to the /errors folder.')
next_action = prompt(f'What would you like to do with the file? ({file_name})', options=next_options)
if next_action == 'Move to Archive':
move(file_path, 'archive')
elif next_action == 'View errors':
for instance, result in results.items():
df = pd.DataFrame().from_records(result.job.errors)
cols = list({**{'sf_result':None}, **{c:c for c in df.columns.values}})[0:5]
print(f'### {instance} ###')
print(df[cols].to_string())
else:
# Is query
query_text = open(file_path, 'r').read()
parsed_query = sf.parse_query(query_text)
if parsed_query is not None:
try:
instances = prompt('Which instance(s) would you like to query?', multiselect=True, options={
(key[key.rindex('.')+1:] + (' prod' if val['sandbox'] == 'False' else '')).strip(): val
for key, val in sf.instance_credentials.items() if 'security_token' in val
})
sessions = [Salesforce_API(c) for c in instances]
mode = 'bulk' if re.match(r'^\s*--\s*bulk', query_text) else 'simple'
queries = {
session.instance: threading.new(session.select, query_text, mode=mode, return_type='flat')
for session in sessions
}
threading.wait(queries.values())
print('\nResult file(s) written to:')
for instance, result in queries.items():
result = result.result()
output_file_path = write_result('query_results', f'{instance} {parsed_query.object_name} ({len(result)})', result)
print(f'/query_results/{os.path.split(output_file_path)[1]}')
sf.console.clear()
except Exception as e:
print(e)
else:
print('Could not parse query file. Please update the file and try again.')
time.sleep(1)
def eos_venues():
def get_eos_dupes_analysis():
records = sql.query("""
SELECT v.Id, v.Name, v.RomeId, v.IsActive
FROM Venue v
LEFT JOIN TownCity t
ON v.TownCityId = t.Id
LEFT JOIN Region r
ON t.RegionId = r.Id
LEFT JOIN Country c
ON r.CountryId = c.Id
WHERE c.Name IN ('United Kingdom')
""")
return pdh.find_duplicates(records, 'Name', None,
['Id', 'RomeId', 'IsActive'])
def get_deletion_log():
return pd.DataFrame(
sql.query("""
SELECT l.DeletedRecordId__c
, l.MergedAccount__c
, l.RecordTypeName__c
, v1.Id AS EOSIdToDelete
, v1.Name AS EOSNameToDelete
, v2.Id AS EOSIdToMergeInto
, v2.Name AS EOSNameToMergeInto
, CASE WHEN v1.Id < v2.Id THEN 'Double check this - older ID being deleted' ELSE NULL END AS SpotCheck
FROM RomeDeletionLog l
LEFT JOIN Venue v1
ON v1.RomeId = l.DeletedRecordId__c
AND l.RecordTypeName__c = 'Venue'
LEFT JOIN Venue v2
ON v2.RomeId = l.MergedAccount__c
AND l.RecordTypeName__c = 'Venue'WHERE v1.Id IS NOT NULL
"""))
eos_dupes = Cache.get('eosdupes', None)
if eos_dupes is None or prompt('Re-run EOS Venue Dupe Analysis?',
boolean=True):
eos_dupes = get_eos_dupes_analysis()
Cache.set('eosdupes', eos_dupes)
deletion_log = get_deletion_log()
deleted_ids = set(deletion_log.DeletedRecordId__c)
def note(row):
r1, r2 = row['RomeId'], row['m_RomeId']
if pdh.isnull(r1) and pdh.isnull(r2):
return 'EOS Merge - No Rome links exist'
if r1 in deleted_ids and r2 in deleted_ids:
return 'Both RomeIds are deleted in Rome'
elif r2 in deleted_ids:
return 'EOS Merge RIGHT into LEFT - RIGHT Id is deleted in Rome'
elif r1 in deleted_ids:
return 'EOS Merge LEFT into RIGHT - LEFT Id is deleted in Rome'
if pdh.isnull(r1) and pdh.notnull(r2):
return 'EOS Merge LEFT into RIGHT'
if pdh.isnull(r2) and pdh.notnull(r1):
return 'EOS Merge RIGHT into LEFT'
if r1 == r2:
return 'EOS Merge - Both EOS Venues have the same RomeId... merge one into the other'
if r1 not in deleted_ids and r2 not in deleted_ids:
return 'ROME Merge first, then EOS can be merged'
eos_dupes.query("`m_%` > 70", inplace=True)
eos_dupes['Note'] = eos_dupes.apply(note, axis=1)
eos_dupes[
'RomeMergeLink'] = 'https://lne.my.salesforce.com/merge/accmergewizard.jsp?goNext=+Next+&cid=' + eos_dupes[
'RomeId'] + '&cid=' + eos_dupes['m_RomeId']
output = {
'EOS Dupe Analysis': eos_dupes,
'RomeDeletionLog': deletion_log,
}
file_name = loc.uk_folder_path + '/Master Data - Output/Venue Dupe Analysis/EOS Venue Duplicate Analysis ' + datetime.now(
).strftime('%Y-%m-%d %H.%M') + '.xlsx'
pdh.to_excel(output, file_name)
return
def main():
source_is_modified = ' (Files have been modified)' if source_files_have_been_modified(
) else ''
re_run = prompt(f'Re-run Historical Extract? {source_is_modified}',
boolean=True)
if re_run:
uk.extract_historical_file_data(sf,
ask_to_regenerate_files=False,
multi=True)
file_data = uk.read_pickle(loc.uk_historical_migration_combined_pickle)
grouped_file_data = threading.new(get_file_data_by_tour_and_event,
file_data)
offer_ids = file_data.Tour__c.EOSId__c.tolist()
file_offers = file_data.Tour__c[['EOSId__c']]
offers_to_migrate_eos = pd.DataFrame(
sql.query(f"""
SELECT CAST(OfferId AS VARCHAR) AS EOSId__c, ArtistName, OfferStatusName, Company, OracleCode, PromoterName
, FORMAT(MIN(ShowDate), 'yyyy-MM-dd') AS FirstDate
, FORMAT(MAX(ShowDate), 'yyyy-MM-dd') AS LastDate
, COUNT(DISTINCT ShowId) AS ShowCountNotCancelled
-- , COUNT(DISTINCT (CASE WHEN PostponedDateTBC = 1 THEN ShowId ELSE NULL END)) AS PostponedDateTBCShows
FROM vwEOSData
WHERE OfferId IN ({','.join(offer_ids)})
GROUP BY OfferId, ArtistName, OfferStatusName, CountryName, Company, OracleCode, PromoterName
ORDER BY MAX(ShowDate) ASC
"""))
offers_to_migrate = file_offers.merge(offers_to_migrate_eos,
on='EOSId__c',
how='left').to_dict('records')
shows_to_migrate = uk.query_Event__c(sql, offer_ids)
shows_by_offer_id = pdh.groupby_unsorted(shows_to_migrate,
lambda x: x['TourEOSId'])
migrated_offers = lne.select("""
SELECT Id, Status__c, EOSId__c, OracleProjectCode__c, TourName__c, ShowCount__c, CreatedBy.Name
FROM Tour__c
WHERE EOSId__c IN @offer_ids
""",
return_type='flat')
migrated_offers_uat = uat.select("""
SELECT Id, Status__c, EOSId__c, OracleProjectCode__c, TourName__c, ShowCount__c, CreatedBy.Name
FROM Tour__c
WHERE EOSId__c IN @offer_ids
""",
return_type='flat')
migrated_offer_ids = {item['EOSId__c'] for item in migrated_offers}
migrated_offer_ids_uat = {item['EOSId__c'] for item in migrated_offers_uat}
remaining_to_migrate = [
item for item in offers_to_migrate
if item['EOSId__c'] not in migrated_offer_ids
]
offer_ids_remaining_to_migrate = [
item['EOSId__c'] for item in remaining_to_migrate
]
analysis_missing_eos_ids_by_tour = threading.new(
uk.analysis_missing_eos_ids_by_tour, sql, sf,
offer_ids_remaining_to_migrate)
all_missing_eos_ids = set(
itertools.chain.from_iterable(
analysis_missing_eos_ids_by_tour.values()))
missing_rome_master_data_table = threading.new(uk.query_by_eos_ids,
sql,
all_missing_eos_ids,
combined_result=True)
costing_show_filenames = set(os.listdir(loc.uk_historical_data_cache_path))
for item in migrated_offers + remaining_to_migrate:
shows = shows_by_offer_id.get(item['EOSId__c'], [])
shows_with_costing = [
item for item in shows
if f"{item['EOSId__c']}.pickle" in costing_show_filenames
]
item['ShowCountInclCancelled'] = len(shows)
item['# Shows w Excel Costing'] = len(shows_with_costing)
item['Migrated in UAT?'] = item['EOSId__c'] in migrated_offer_ids_uat
for item in remaining_to_migrate:
item['Missing Master Data'] = ', '.join(
analysis_missing_eos_ids_by_tour.get(item['EOSId__c'], []))
def get_multiple_file_issues(df):
if len(df) == 0:
return []
groupby = df.groupby(['EventEOSId', 'Source_File'])
df2 = pd.DataFrame(groupby.groups.keys())
duplicated = df2.duplicated(subset=[0], keep=False)
df3 = df2[duplicated]
chunks = []
if len(df3) > 0:
groupby2 = df3.groupby(0)
for eosid in groupby2.groups:
files = df2.groupby(0).get_group(eosid)[1].tolist()
chunks.append(f'Show {eosid} in {", ".join(files)}')
return chunks
def tour_events_analysis(tour_costing):
event_ids = tour_costing.Event__c.EOSId__c.tolist()
output = {}
events_missing_lebs = []
for evt in event_ids:
leb = file_data_by_event.get(evt, {}).get('LedgerEntryBreakout__c',
None)
if leb is None or len(leb) == 0:
events_missing_lebs.append(evt)
output['Err_EventsMissingLEBs'] = ', '.join(events_missing_lebs)
return output
file_data_by_tour, file_data_by_event = grouped_file_data.result()
for item in remaining_to_migrate:
costing = file_data_by_tour.get(item['EOSId__c'], None)
if costing:
tour = costing.Tour__c.iloc[0].to_dict()
lebs = costing.LedgerEntryBreakout__c
les = costing.LedgerEntry__c
item.update(tour_events_analysis(costing))
item['Err_HasNegativeLEBs'] = True if (
lebs.OfferRate__c < 0).any() == True else None
# item['Err_TourHasNoShows'] = item['EOSId__c'] not in file_events_by_tour.groups
item['Err_OracleCodeMismatch'] = (
None if
(tour['OracleProjectCode__c'] == item['OracleCode']) else
f"{item['OracleCode']} in EOS, {tour['OracleProjectCode__c']} in file"
)
item['Err_MultipleFileSources'] = ' | '.join(
get_multiple_file_issues(les) + get_multiple_file_issues(lebs))
remaining_to_migrate = pd.DataFrame(remaining_to_migrate)
for col in remaining_to_migrate.columns.values:
vals = remaining_to_migrate[col]
if col.startswith('Err') and ((vals.isna()) | (vals == '')).all():
del remaining_to_migrate[col]
file_name = 'UK On-Sale Migration Status ' + datetime.now().strftime(
'%Y-%m-%d') + '.xlsx'
file_name_2 = loc.uk_historical_migration_status + 'UK On-Sale Migration Status ' + datetime.now(
).strftime('%Y-%m-%d %H.%M') + '.xlsx'
output = {
'Remaining to Migrate': remaining_to_migrate,
'Migrated Offers': migrated_offers,
}
missing_md = missing_rome_master_data_table.result()
if len(missing_md) > 0:
output[f'Missing Master Data'] = missing_md
output = {f'{k} ({len(v)})': v for k, v in output.items()}
threading.new(pdh.to_excel, output, file_name)
threading.new(pdh.to_excel, output, file_name_2)
threading.wait()
return
def main():
session = Salesforce_API("*****@*****.**")
sql = SQL_Server_API('EOS-stage')
accounts = session.select("SELECT Id, EOSId__c FROM Account",
return_type='dataframe')
account_ids = set(accounts.set_index('Id').to_dict('index'))
account_eos_ids = set(
accounts.fillna('').query("EOSId__c != ''").set_index(
'EOSId__c').to_dict('index'))
venue = sql.query(
f"SELECT *, 'Venue-'+CAST(Id AS VARCHAR(10)) AS EOSId__c FROM Venue")
artist = sql.query(
f"SELECT *, 'Artist-'+CAST(Id AS VARCHAR(10)) AS EOSId__c FROM Artist")
copromoter = sql.query(
f"SELECT *, 'Copromoter-'+CAST(Id AS VARCHAR(10)) AS EOSId__c FROM Copromoter"
)
artistagency = sql.query(
f"SELECT *, 'ArtistAgency-'+CAST(Id AS VARCHAR(10)) AS EOSId__c FROM ArtistAgency"
)
ticketagency = sql.query(
f"SELECT *, 'TicketAgent-'+CAST(Id AS VARCHAR(10)) AS EOSId__c FROM TicketAgent"
)
output = {
'Venue': venue,
'Artist': artist,
'Copromoter': copromoter,
'ArtistAgency': artistagency,
'TicketAgent': ticketagency,
}
to_wipe_RomeId = {obj: [] for obj in output}
to_delete = {obj: [] for obj in output}
for obj, data in output.items():
for record in data:
if record['EOSId__c'] in account_eos_ids or record['EOSId__c'] in (
'ArtistAgency-1', 'ArtistAgency-149', 'ArtistAgency-158'):
to_wipe_RomeId[obj].append(record)
elif record['RomeId'] not in (
None, '',
'None') and record['RomeId'] not in account_ids and record[
'CreatedBy'] == 'Rome_EOS_Integration':
to_delete[obj].append(record)
pdh.to_excel(to_wipe_RomeId, 'EOSDataToWipeRomeId.xlsx')
pdh.to_excel(to_delete, 'EOSDataToDelete.xlsx')
def delete(table, data):
if len(data) > 0:
ids = pd.DataFrame(data).Id.tolist()
ids_text = ','.join([str(i) for i in ids])
s = f'DELETE FROM {table} WHERE Id IN({ids_text})'
print(s)
sql.execute(s, commit=True)
def blank_romeid(table, df):
if len(data) > 0:
ids = pd.DataFrame(data).Id.tolist()
ids_text = ','.join([str(i) for i in ids])
s = f'UPDATE {table} SET RomeId = NULL WHERE Id IN({ids_text})'
print(s)
sql.execute(s, commit=True)
if prompt('Ready to update EOS?', boolean=True):
for obj, data in to_delete.items():
delete(obj, data)
for obj, data in to_wipe_RomeId.items():
blank_romeid(obj, data)
return
def main():
records = sf.fupdate("""
UPDATE LedgerEntryBreakout__c
SET CoPromoterPlanRate__c = CoPromoterReimbursementRate__c
, CoPromoterPlanRateText__c = CoPromoterReimbursementRateText__c
WHERE CoPromoterReimbursementRate__c != NULL
AND CoPromoterPlanRate__c != CoPromoterReimbursementRate__c
-- LIMIT 20
""", return_staged=False)
print('')
# with sf.bypass_settings():
# res1 = sf.fupdate("""
# UPDATE TourOnSale__c
# SET Type__c = 'Promoter'
# WHERE Type__c IN ('Cuffe & Taylor','Live Nation','Metropolis Music')
# """, return_staged=False)
# res2 = sf.fupdate("""
# UPDATE EventOnSale__c
# SET Type__c = 'Promoter'
# WHERE Type__c IN ('Cuffe & Taylor','Live Nation','Metropolis Music')
# """, return_staged=False)
# pdh.to_excel(df, 'Planned TourOnSale Update')
# touronsales = sf.fselect("""
# SELECT Id
# , Type__c AS OldType
# , 'Promoter' AS Type__c
# FROM TourOnSale__c
# WHERE Type__c IN ('Cuffe & Taylor','Live Nation','Metropolis Music')
# """)
# eventonsales = sf.fselect("""
# SELECT Id
# , Type__c AS OldType
# , 'Promoter' AS Type__c
# FROM EventOnSale__c
# WHERE Type__c IN ('Cuffe & Taylor','Live Nation','Metropolis Music')
# """)
# touronsales = sf.select("""
# SELECT Id
# , Type__c
# FROM TourOnSale__c
# WHERE Type__c IN ('Cuffe & Taylor','Live Nation','Metropolis Music')
# """, return_type='dataframe')
# eventonsales = sf.select("""
# SELECT Id
# , Type__c
# FROM EventOnSale__c
# WHERE Type__c IN ('Cuffe & Taylor','Live Nation','Metropolis Music')
# """, return_type='dataframe')
# touronsales['Type__c'] = 'Promoter'
# eventonsales['Type__c'] = 'Promoter'
prompt('Press any key to continue')
sf.bypass_prod_operation_approval()
with sf.bypass_settings():
sf.update(touronsales, mode='bulk')
sf.update(eventonsales, mode='bulk')
return
def main():
print('####### CLONE UTILITY #######')
while True:
source_sf = Salesforce_API(prompt(f'What SF Instance would you like to clone from?', options={
(key[key.rindex('.')+1:] + (' prod' if val['sandbox'] == 'False' else '')).strip(): val
for key, val in Salesforce_API.instance_credentials.items() if 'security_token' in val
}))
target_sf = Salesforce_API(prompt(f'What other SF Instance would you like to clone to?', options={
(key[key.rindex('.')+1:] + (' prod' if val['sandbox'] == 'False' else '')).strip(): val
for key, val in Salesforce_API.instance_credentials.items() if 'security_token' in val and val['sandbox'] == 'True' and val != source_sf
}))
clone_methods = {
'Tour__c': target_sf.clone_tours,
'Event__c': target_sf.clone_events,
'AdPlan__c': target_sf.clone_adplans,
}
clone_object = prompt(f'What would you like to clone?', options={
'Tours': 'Tour__c',
'Events': 'Event__c',
'Ad Plans': 'AdPlan__c',
})
title_field = clone_object.replace('__c', 'Title__c')
selected_record_ids = []
while True:
selection = prompt(f'What record(s) would you like to clone? Multiple can be selected by a comma-separated entry.\n(You can enter record IDs for an exact match, or enter any other text for SOSL Search)')
split_selections = selection.split(',')
for s in split_selections:
match = re.match(id_regex, s)
if match:
selected_record_ids.append(match.group(1))
else:
records = source_sf.sosl(s, clone_object, None, ['Id', title_field])
if len(records) == 1:
selected_record_ids.append(records[0].Id)
elif len(records) > 1:
options = {record[title_field]: record for record in records[0:9]}
options['None of the above'] = None
record_to_add = prompt(f'SOSL Search performed for "{s}". Which record would you like to clone?', options=options)
if record_to_add is not None:
selected_record_ids.append(record_to_add.Id)
else:
print(f'No results found for "{s}"')
selected_records = source_sf.select(f"SELECT Id, {title_field} FROM {clone_object} WHERE Id IN @selected_record_ids", mute=True)
if not selected_records:
continue
titles = "\n".join([item[title_field] for item in selected_records])
message = f'\n\nSELECTED:\n{titles}\n'
message += f'\nThe above {clone_object} record{"(s) have" if len(selected_records) > 1 else " has"} been selected for clone.'
message += f'\nSOURCE: {source_sf.instance} | TARGET: {target_sf.instance}'
resp = prompt(message, options={
f'Ready to clone?': 1,
f'Search for more {clone_object} records to clone?': 2,
f'Cancel and restart?': 3
})
if resp == 1:
clone_method = clone_methods[clone_object]
clone_result = clone_method(
[item.Id for item in selected_records]
, source_session=source_sf
, target_session=target_sf
)
source_sf.console.clear()
target_sf.cloneutil.clone_records_ids_map.clear()
target_sf.cloneutil.clone_records_ids_processed.clear()
target_sf.cloneutil.clone_records_mapping_log.clear()
print("\n\nCLONE COMPLETE\n\n")
print('\n'.join(pd.DataFrame(clone_result)['Id'].apply(lambda x: f'https://{target_sf.simple.sf_instance}/{x}').tolist()[:10]))
break
if resp == 2:
continue
if resp == 3:
break
def venue_primary_office_update():
venues_to_update_primary_office = [
'0011Q00002NR3byQAD', '0011Q00002L0CUFQA3', '0013600001YSupjAAD',
'0013600001YStyOAAT', '0013600001YSv2HAAT', '0011Q00002NR3vwQAD',
'0013600001YSuq2AAD', '0013600001YSuq8AAD', '0013600001YSuq9AAD',
'0013600001YSuqAAAT', '0013600001YSv8iAAD', '0013600001YSu3XAAT',
'0013600001YSup7AAD', '0013600001YSuqYAAT', '0011Q00002NR3guQAD',
'0011Q00002NR3v7QAD', '0011Q00002NR3gzQAD', '0013600001YT3q5AAD',
'0011Q00002NR3nYQAT', '0011Q00002NR3fHQAT', '0011Q00002D9VFNQA3',
'0013600001YSvHoAAL', '0013600001YSvGUAA1', '0011Q00002NR3vfQAD',
'0013600001YT3x5AAD', '0013600001YSv2EAAT', '0011Q00002NR41EQAT',
'0013600001YSu7zAAD', '0011Q00002NR3rmQAD', '0011Q00002NR3qDQAT',
'0013600001YSvRkAAL', '0011Q00002NR40hQAD', '0011Q00002NR3nsQAD',
'0013600001YT3x1AAD', '0011Q00002NR3jaQAD', '0013600001YSuy2AAD',
'0013600001YSuy3AAD', '0013600001YSv7hAAD', '0011Q00002NR3fIQAT',
'0013600001YSulUAAT', '0013600001YSuzgAAD', '0011Q00002L2LWeQAN',
'0013600001YSu1VAAT', '0011Q00002JMyN7QAL', '0011Q00002NR3fxQAD',
'0013600001YT3qWAAT', '0011Q00002NR3ftQAD', '0011Q00002NR3g3QAD',
'0011Q00002NR48wQAD', '0011Q00002NR3cxQAD', '0011Q00002NR3mzQAD',
'0011Q00002NR46CQAT', '0011Q00002NR3q6QAD', '0013600001YSvLGAA1',
'0013600001YSv84AAD', '0013600001YSv1UAAT', '0011Q00002NR3hAQAT',
'0011Q00002NR3cmQAD', '0013600001YSv1SAAT', '0013600001YSv6CAAT',
'0013600001YSvCyAAL', '0013600001YSv6FAAT', '0013600001YSv6EAAT',
'0013600001YSv6GAAT', '0011Q00002NR3uzQAD', '0011Q00002NR42gQAD',
'0013600001YSv6DAAT', '0011Q00002L0CUJQA3', '0013600001YSv25AAD',
'0013600001YSv2dAAD', '0011Q00002NR41hQAD', '0011Q00002NR47oQAD',
'0011Q00002NR44VQAT', '0011Q00002NR43zQAD', '0013600001YSu3XAAT',
'0011Q00002NR3xLQAT', '0011Q00002NR43LQAT', '0011Q00002NR3t0QAD',
'0011Q00002NR3jsQAD', '0011Q00002NR3hBQAT', '0011Q00002JMyPcQAL',
'0011Q00002NR3hCQAT', '0013600001YSv7BAAT', '0011Q00002NR45IQAT',
'0013600001iqVT4AAM', '0013600001YSv6GAAT', '0011Q00002NR427QAD',
'0011Q00002NR3oSQAT', '0011Q00002NR3k3QAD', '0011Q00002NR3vnQAD',
'0013600001YSvEtAAL', '0013600001YSvGhAAL', '0011Q00002NR45JQAT',
'0011Q00002NR44wQAD', '0013600001YSvLuAAL', '0011Q00002NR3zyQAD',
'0011Q00002NR40oQAD', '0011Q00002NR45nQAD', '0011Q00002NR45oQAD',
'0011Q00002NR3zWQAT', '0011Q00002NR440QAD', '0011Q00002NR48UQAT',
'0011Q00002NR43dQAD', '0011Q00002NR3xwQAD', '0011Q00002NR438QAD',
'0011Q00002NR43sQAD', '0011Q00002NR3uBQAT', '0011Q00002NR44KQAT',
'0011Q00002NR429QAD', '0011Q00002NR46ZQAT', '0011Q00002NR3ueQAD',
'0013600001YSvEcAAL', '0011Q00002NR3xNQAT', '0011Q00002NR46nQAD',
'0013600001YSvPUAA1', '0011Q00002NR42AQAT', '0011Q00002NR40IQAT',
'0011Q00002NR47ZQAT', '0011Q00002NR48QQAT', '0011Q00002NR413QAD',
'0011Q00002NR48pQAD', '0011Q00002NR3zqQAD', '0011Q00002NR485QAD',
'0011Q00002NR3sPQAT', '0011Q00002NR41XQAT', '0011Q00002NR41YQAT',
'0011Q00002NR3lsQAD', '0011Q00002NR3wVQAT', '0013600001YSv1RAAT',
'0011Q00002NR47lQAD', '0011Q00002NR43eQAD', '0011Q00002NR43MQAT',
'0011Q00002NR46rQAD', '0011Q00002NR441QAD', '0011Q00002NR3oxQAD',
'0011Q00002NR3cyQAD', '0013600001YSvLIAA1', '0011Q00002NR46TQAT',
'0011Q00002NR42qQAD', '0011Q00002NR43WQAT', '0011Q00002NR3oTQAT',
'0013600001YStvqAAD', '0011Q00002NR42mQAD', '0011Q00002NR405QAD',
'0011Q00002NR48sQAD', '0011Q00002NR45DQAT', '0011Q00002NR3kKQAT',
'0011Q00002NR3kzQAD', '0011Q00002NR3rrQAD', '0011Q00002NR46sQAD',
'0011Q00002NR42BQAT', '0011Q00002NR48UQAT', '0011Q00002NR47OQAT',
'0013600001YSvESAA1', '0013600001YSvFoAAL', '0011Q00002NR3wWQAT',
'0013600001YSvBQAA1', '0011Q00002NR3moQAD', '0011Q00002NR41ZQAT',
'0013600001YSvDCAA1', '0011Q00002NR46fQAD', '0011Q00002NR44NQAT',
'0011Q00002NR3rsQAD', '0011Q00002NR47kQAD', '0011Q00002NR3k5QAD',
'0011Q00002NR3cLQAT', '0011Q00002NR42CQAT', '0011Q00002NR48FQAT',
'0013600001YSvEPAA1', '0011Q00002NR3csQAD', '0013600001YSvJ1AAL',
'0013600001YSvEQAA1', '0013600001YSvItAAL', '0011Q00002NR3fKQAT',
'0011Q00002NR3ufQAD', '0011Q00002NR3izQAD', '0011Q00002NR3ypQAD',
'0011Q00002NR3rtQAD', '0011Q00002NR432QAD', '0011Q00002NR43gQAD',
'0011Q00002NR45KQAT', '0011Q00002NR46OQAT', '0011Q00002NR3mJQAT',
'0011Q00002NR44mQAD', '0011Q00002NR439QAD', '0011Q00002NR44YQAT',
'0011Q00002NR48IQAT', '0011Q00002NR44yQAD', '0011Q00002NR41aQAD',
'0011Q00002NR44EQAT', '0011Q00002NR3k6QAD', '0011Q00002NR3mhQAD',
'0011Q00002NR42uQAD', '0011Q00002NR42EQAT', '0011Q00002NR3xxQAD',
'0011Q00002NR3srQAD', '0011Q00002NR43XQAT', '0011Q00002NR456QAD',
'0011Q00002NR3xyQAD', '0011Q00002NR42aQAD', '0011Q00002NR43iQAD',
'0011Q00002NR43jQAD', '0011Q00002NR486QAD', '0011Q00002NR3xzQAD',
'0011Q00002NR47gQAD', '0011Q00002NR43DQAT', '0011Q00002NR45MQAT',
'0011Q00002NR401QAD', '0011Q00002NR3ruQAD', '0011Q00002NR42hQAD',
'0011Q00002NR3xbQAD', '0011Q00002NR43OQAT', '0011Q00002NR3w4QAD',
'0013600001YT3wEAAT', '0011Q00002NR479QAD', '0011Q00002NR45rQAD',
'0011Q00002NR3dxQAD', '0011Q00002NR41mQAD', '0013600001YSvHsAAL',
'0011Q00002NR419QAD', '0011Q00002NR46uQAD', '0011Q00002NR3slQAD',
'0011Q00002NR42bQAD', '0011Q00002NR44kQAD', '0011Q00002NR488QAD',
'0011Q00002NR3hDQAT', '0011Q00002NR3y0QAD', '0013600001YSvLBAA1',
'0011Q00002NR3rvQAD', '0011Q00002NR46KQAT', '0011Q00002NR40WQAT',
'0011Q00002NR3y1QAD', '0011Q00002NR3oDQAT', '0011Q00002NR46aQAD',
'0011Q00002NR3n7QAD', '0011Q00002NR3bwQAD', '0011Q00002NR43jQAD',
'0011Q00002NR3zlQAD', '0011Q00002NR3yqQAD', '0011Q00002NR3vhQAD',
'0011Q00002NR40tQAD', '0011Q00002NR42GQAT', '0011Q00002NR472QAD',
'0011Q00002NR44ZQAT', '0011Q00002NR42rQAD', '0011Q00002NR41LQAT',
'0011Q00002NR3kUQAT', '0013600001YSvSiAAL', '0011Q00002NR43lQAD',
'0011Q00002NR42HQAT', '0011Q00002NR3y2QAD', '0011Q00002NR42IQAT',
'0011Q00002NR408QAD', '0011Q00002NR3w6QAD', '0011Q00002NR42sQAD',
'0011Q00002NR3tkQAD', '0011Q00002NR45SQAT', '0011Q00002NR41RQAT',
'0011Q00002NR46bQAD', '0011Q00002NR3eYQAT', '0011Q00002NR3c8QAD',
'0013600001YSvCzAAL', '0011Q00002NR48WQAT', '0011Q00002NR46RQAT',
'0011Q00002NR42JQAT', '0011Q00002NR41SQAT', '0011Q00002NR3yNQAT',
'0013600001YSvIIAA1', '0011Q00002NR3hOQAT', '0011Q00002NR3eZQAT',
'0011Q00002NR45XQAT', '0011Q00002NR3vcQAD', '0011Q00002NR3zZQAT',
'0011Q00002NR48RQAT', '0011Q00002NR468QAD', '0011Q00002NR43mQAD',
'0011Q00002NR45tQAD', '0011Q00002NR3iDQAT', '0011Q00002NR44jQAD',
'0011Q00002NR44aQAD', '0011Q00002NR474QAD', '0011Q00002NR44bQAD',
'0011Q00002NR44oQAD', '0011Q00002NR3yJQAT', '0011Q00002NR40xQAD',
'0011Q00002NR3eaQAD', '0011Q00002NR41GQAT', '0011Q00002NR433QAD',
'0011Q00002NR3m9QAD', '0011Q00002NR3z1QAD', '0011Q00002NR450QAD',
'0011Q00002NR3wFQAT', '0011Q00002NR45YQAT', '0011Q00002NR3hEQAT',
'0011Q00002NR46PQAT', '0011Q00002NR48hQAD', '0011Q00002NR3rwQAD',
'0011Q00002NR3iiQAD', '0011Q00002JMyM4QAL', '0011Q00002NR46hQAD',
'0011Q00002NR47UQAT', '0011Q00002NR44FQAT', '0011Q00002NR3ogQAD',
'0013600001YSvLIAA1', '0011Q00002JMyQVQA1', '0011Q00002NR45ZQAT',
'0013600001juDRFAA2', '0011Q00002NR443QAD', '0011Q00002NR3wsQAD',
'0011Q00002NR3y3QAD', '0011Q00002NR3dOQAT', '0011Q00002NR3hFQAT',
'0011Q00002NR47AQAT', '0011Q00002NR3xXQAT', '0011Q00002NR46vQAD',
'0011Q00002NR3wXQAT', '0011Q00002NR40KQAT', '0011Q00002NR3cWQAT',
'0011Q00002NR489QAD', '0011Q00002NR45vQAD', '0011Q00002NR46wQAD',
'0011Q00002NR44qQAD', '0011Q00002NR469QAD', '0011Q00002NR458QAD',
'0011Q00002NR41MQAT', '0011Q00002NR46iQAD', '0011Q00002NR40MQAT',
'0011Q00002NR47CQAT', '0011Q00002NR3luQAD', '0011Q00002NR42dQAD',
'0011Q00002NR44cQAD', '0011Q00002NR426QAD', '0011Q00002NR43BQAT',
'0011Q00002NR3fMQAT', '0011Q00002NR3v9QAD', '0011Q00002NR459QAD',
'0011Q00002NR3dPQAT', '0011Q00002NR46xQAD', '0011Q00002NR3zaQAD',
'0011Q00002NR444QAD', '0011Q00002NR47SQAT', '0011Q00002NR48JQAT',
'0011Q00002NR3v1QAD', '0013600001YSvHoAAL', '0011Q00002NR473QAD',
'0011Q00002NR40uQAD', '0011Q00002NR46IQAT', '0011Q00002NR3vWQAT',
'0011Q00002NR3ewQAD', '0011Q00002NR42LQAT', '0011Q00002NR41bQAD',
'0011Q00002NR3ozQAD', '0011Q00002NR3v2QAD', '0011Q00002NR3wtQAD',
'0011Q00002NR46EQAT', '0011Q00002NR3cnQAD', '0011Q00002NR43PQAT',
'0011Q00002NR46kQAD', '0011Q00002NR44dQAD', '0011Q00002NR42MQAT',
'0011Q00002NR43qQAD', '0011Q00002NR45AQAT', '0011Q00002NR445QAD',
'0011Q00002NR43vQAD', '0011Q00002NR48fQAD', '0011Q00002NR43YQAT',
'0011Q00002NR3yOQAT', '0011Q00002NR45aQAD', '0011Q00002NR3mAQAT',
'0011Q00002NR45bQAD', '0011Q00002NR3mBQAT', '0011Q00002NR47EQAT',
'0011Q00002NR3jcQAD', '0011Q00002NR435QAD', '0011Q00002NR3nZQAT',
'0011Q00002NR48qQAD', '0011Q00002NR45dQAD', '0011Q00002NR3hsQAD',
'0011Q00002NR48gQAD', '0011Q00002NR3s0QAD', '0011Q00002NR40iQAD',
'0011Q00002NR47VQAT', '0011Q00002NR46QQAT', '0011Q00002NR47IQAT',
'0011Q00002NR45eQAD', '0011Q00002NR3naQAD', '0011Q00002NR3t9QAD',
'0011Q00002NR3ysQAD', '0011Q00002NR41HQAT', '0011Q00002NR3y5QAD',
'0011Q00002NR3x3QAD', '0011Q00002NR48DQAT', '0011Q00002NR42OQAT',
'0011Q00002NR44BQAT', '0011Q00002NR3pjQAD', '0011Q00002NR3ihQAD',
'0011Q00002NR47PQAT', '0011Q00002NR3ugQAD', '0011Q00002NR3ykQAD',
'0011Q00002NR46FQAT', '0011Q00002NR45PQAT', '0011Q00002NR3p0QAD',
'0011Q00002NR3i3QAD', '0011Q00002NR43QQAT', '0011Q00002NR48SQAT',
'0011Q00002NR3x4QAD', '0011Q00002NR415QAD', '0011Q00002NR41TQAT',
'0011Q00002NR42iQAD', '0011Q00002NR3p1QAD', '0011Q00002NR3ylQAD',
'0011Q00002NR42PQAT', '0011Q00002NR44eQAD', '0011Q00002NR45kQAD',
'0011Q00002NR48TQAT', '0011Q00002NR48HQAT', '0011Q00002NR46yQAD',
'0011Q00002NR47FQAT', '0011Q00002NR45QQAT', '0011Q00002NR3cMQAT',
'0011Q00002NR42QQAT', '0011Q00002NR45xQAD', '0011Q00002NR41iQAD',
'0011Q00002NR3y6QAD', '0011Q00002NR44lQAD', '0011Q00002NR47JQAT',
'0011Q00002NR47GQAT', '0011Q00002NR4G8QAL', '0011Q00002BvTViQAN',
'0011Q00002NR45lQAD', '0011Q00002MLNs6QAH', '0011Q00002NR3dbQAD',
'0011Q00002NR43nQAD', '0011Q00002NR3gHQAT', '0011Q00002NR47HQAT',
'0011Q00002NR3kQQAT', '0011Q00002NR46zQAD', '0011Q00002JMyN8QAL',
'0011Q00002NR3cuQAD', '0011Q00002NR3tRQAT', '0011Q00002NR3iEQAT',
'0011Q00002NR462QAD', '0011Q00002NR3s1QAD', '0013600001baFYcAAM',
'0011Q00002NR43aQAD', '0011Q00002NR3s2QAD', '0011Q00002NR47iQAD',
'0011Q00002QhgOIQAZ', '0011Q00002NR3pXQAT', '0011Q00002NR3eFQAT',
'0011Q00002NR40NQAT', '0011Q00002NR3xcQAD', '0011Q00002NR43wQAD',
'0011Q00002NR42RQAT', '0011Q00002NR3lrQAD', '0011Q00002NR41IQAT',
'0011Q00002NR3faQAD', '0011Q00002NR3vqQAD', '0011Q00002NR3kVQAT',
'0011Q00002NR3twQAD', '0011Q00002NR3qEQAT', '0011Q00002NR41JQAT',
'0011Q00002QhgZJQAZ', '0011Q00002NR41qQAD', '0011Q00002NR3wuQAD',
'0011Q00002NR3t2QAD', '0013600001YSvECAA1', '0011Q00002NR3ytQAD',
'0011Q00002NR48XQAT', '0011Q00002NR3v3QAD', '0011Q00002NR3cXQAT',
'0011Q00002NR42SQAT', '0011Q00002NR42TQAT', '0011Q00002NR44rQAD',
'0011Q00002NR416QAD', '0011Q00002NR3oUQAT', '0011Q00002NR3uDQAT',
'0011Q00002NR3s3QAD', '0011Q00002NR47KQAT', '0011Q00002NR44fQAD',
'0011Q00002NR3xRQAT', '0011Q00002NR3wZQAT', '0011Q00002NR45BQAT',
'0011Q00002NR3jtQAD', '0011Q00002NR48VQAT', '0011Q00002NR43bQAD',
'0011Q00002NR44LQAT', '0011Q00002NR48tQAD', '0011Q00002NR40OQAT',
'0011Q00002NR42nQAD', '0011Q00002NR42UQAT', '0011Q00002NR42XQAT',
'0011Q00002NR48nQAD', '0011Q00002NR47LQAT', '0011Q00002NR43pQAD',
'0011Q00002NR48vQAD', '0011Q00002NR477QAD', '0011Q00002NR44PQAT',
'0011Q00002NR3v4QAD', '0011Q00002NR46YQAT', '0011Q00002NR42wQAD',
'0011Q00002NR3gIQAT', '0011Q00002NR3gTQAT', '0011Q00002NR40jQAD',
'0011Q00002NR3wGQAT', '0011Q00002NR46AQAT', '0011Q00002NR44gQAD',
'0011Q00002NR451QAD', '0011Q00002NR47NQAT', '0011Q00002NR44CQAT',
'0011Q00002NR40kQAD', '0011Q00002NR3iWQAT', '0011Q00002QhgXSQAZ',
'0011Q00002NR48AQAT', '0011Q00002NR42jQAD', '0011Q00002NR48BQAT',
'0011Q00002NR41nQAD', '0011Q00002NR48PQAT', '0011Q00002NR48aQAD',
'0011Q00002NR3ukQAD', '0011Q00002NR48uQAD', '0011Q00002NR3bjQAD',
'0011Q00002NR46BQAT', '0011Q00002NR3u4QAD', '0011Q00002NR41jQAD',
'0011Q00002NR40lQAD', '0011Q00002NR3o6QAD', '0011Q00002NR40AQAT',
'0011Q00002NR3miQAD', '0011Q00002NR45cQAD', '0011Q00002NR47jQAD',
'0011Q000028NZEAQA4', '0011Q00002NR3w8QAD', '0011Q00002NR3psQAD',
'0011Q00002NR417QAD', '0011Q00002NR3s4QAD', '0011Q00002NR48kQAD',
'0011Q00002NR3dyQAD', '0011Q00002NR3hGQAT', '0011Q00002NR41kQAD',
'0011Q00002NR40PQAT', '0011Q00002NR47WQAT', '0011Q00002NR48EQAT',
'0011Q00002NR44sQAD', '0011Q00002NR44IQAT', '0011Q00002NR3wvQAD',
'0011Q00002NR48CQAT', '0011Q00002NR43cQAD', '0011Q00002NR46GQAT',
'0011Q00002NR3dQQAT', '0011Q00002NR3hYQAT', '0011Q00002NR3hHQAT',
'0011Q00002NR3fNQAT', '0011Q00002NR3l2QAD', '0011Q00002NR3xEQAT',
'0011Q00002NR3vXQAT', '0011Q00002NR3lNQAT', '0011Q00002NR3ceQAD',
'0011Q00002NR3s5QAD', '0011Q00002NR3s6QAD', '0011Q00002NR3nbQAD',
'0011Q00002Qf05SQAR', '0011Q00002Qh2fLQAR', '0011Q00002QhgX9QAJ',
'0011Q00002QhgOHQAZ', '0013600001YSv27AAD', '0013600001YT3qYAAT',
'0011Q00002NR3fpQAD', '0011Q00002NR3tLQAT', '0013600001YSuotAAD',
'0011Q00002NR3hIQAT', '0013600001YSvHgAAL', '0013600001YSvGSAA1',
'0011Q00002NR3eLQAT', '0011Q00002NR3cxQAD', '0011Q00002NR3fqQAD',
'0011Q00002NR41FQAT', '0011Q00002NR48KQAT', '0011Q00002NR3k4QAD',
'0011Q00002NR3fJQAT', '0011Q00002NR48cQAD', '0011Q00002NR43VQAT',
'0013600001YSvBPAA1', '0011Q00002NR48MQAT', '0011Q00002NR3vbQAD',
'0011Q00002NR471QAD', '0011Q00002NR3gXQAT', '0011Q00002NR3zXQAT',
'0011Q00002NR414QAD', '0011Q00002NR47QQAT', '0011Q00002NR3sqQAD',
'0011Q00002NR47RQAT', '0011Q00002NR45WQAT', '0011Q00002NR3yDQAT',
'0011Q00002NR442QAD', '0011Q00002NR487QAD', '0011Q00002NR3x2QAD',
'0011Q00002NR43AQAT', '0011Q00002NR3w3QAD', '0011Q00002NR465QAD',
'0011Q00002NR3vFQAT', '0011Q00002NR43kQAD', '0011Q00002NR407QAD',
'0011Q00002NR48rQAD', '0011Q00002NR45TQAT', '0011Q00002NR48mQAD',
'0011Q00002NR43uQAD', '0011Q00002NR3ltQAD', '0011Q00002NR48NQAT',
'0011Q00002NR3v0QAD', '0011Q00002NR473QAD', '0011Q00002NR475QAD',
'0011Q00002NR3fLQAT', '0011Q00002NR3yrQAD', '0011Q00002NR3hiQAD',
'0011Q00002QhgXSQAZ', '0011Q00002NR3tOQAT', '0011Q00002NR3jbQAD',
'0011Q00002NR45uQAD', '0011Q00002NR3tPQAT', '0013600001YSvKtAAL',
'0011Q00002NR42KQAT', '0011Q00002NR3wYQAT', '0011Q00002NR40LQAT',
'0011Q00002NR45UQAT', '0011Q00002NR46jQAD', '0011Q00002NR47DQAT',
'0011Q00002NR3jAQAT', '0011Q00002NR3ukQAD', '0011Q00002NR42NQAT',
'0011Q00002NR3wMQAT', '0011Q00002NR3vGQAT', '0011Q00002NR3yEQAT',
'0011Q00002NR48ZQAT', '0011Q00002NR43RQAT'
]
ids_text = "'" + "','".join(venues_to_update_primary_office) + "'"
staged = sf.fupdate(f"""
UPDATE Account
SET PrimaryOffice__c = '0011Q00002NPfYwQAL'
WHERE (PrimaryOffice__r.Name = 'Toronto' OR PrimaryOffice__r.Name = 'Vancouver')
--WHERE (PrimaryOffice__r.Name != 'Toronto' AND PrimaryOffice__r.Name != 'Vancouver')
AND PrimaryOffice__r.Name != 'Evenko'
AND Id IN ({ids_text})
""",
show_columns=['Name', 'PrimaryOffice__r.Name'])
not_to_update = sf.fupdate(f"""
UPDATE Account
SET PrimaryOffice__c = '0011Q00002NPfYwQAL'
--WHERE (PrimaryOffice__r.Name = 'Toronto' OR PrimaryOffice__r.Name = 'Vancouver')
WHERE (PrimaryOffice__r.Name != 'Toronto' AND PrimaryOffice__r.Name != 'Vancouver')
AND PrimaryOffice__r.Name != 'Evenko'
AND Id IN ({ids_text})
""",
show_columns=['Name', 'PrimaryOffice__r.Name'])
pdh.to_excel({
'To Update': staged,
'NOT UPDATING': not_to_update
}, 'Staged Evenko venue updates')
if prompt('Update?', boolean=True):
sf.update(staged, batch_size=10)
return
def main():
re_run = prompt('Re-run On-Sale?', boolean=True)
costing_data_by_tour = threading.new(get_costing_data_by_tour, re_run)
offers_to_migrate = sql.query("""
SELECT CAST(OfferId AS VARCHAR) AS EOSId__c, ArtistName, OfferStatusName, Company, OracleCode, PromoterName, USVenueType, (SELECT CreatedDateTime FROM Offer WHERE Id = OfferId) AS OfferCreatedDate
, FORMAT(MIN(ShowDate), 'yyyy-MM-dd') AS FirstDate
, FORMAT(MAX(ShowDate), 'yyyy-MM-dd') AS LastDate
, COUNT(DISTINCT ShowId) AS ShowCount
, COUNT(DISTINCT (CASE WHEN PostponedDateTBC = 1 THEN ShowId ELSE NULL END)) AS PostponedDateTBCShows
FROM vwEOSData
WHERE (ShowDate>=GetDate() OR PostponedDateTBC=1)
AND CountryId = 1
AND OfferStatusName IN ('Confirmed','On Sale','Settled','Draft')
AND PromoterId NOT IN (1286)
GROUP BY OfferId, ArtistName, OfferStatusName, CountryName, Company, OracleCode, PromoterName, USVenueType
ORDER BY MAX(ShowDate) ASC
""")
offer_ids = [item['EOSId__c'] for item in offers_to_migrate]
shows_to_migrate = uk.query_Event__c(sql, offer_ids)
shows_by_offer_id = pdh.groupby_unsorted(shows_to_migrate,
lambda x: x['TourEOSId'])
migrated_offers = lne.select("""
SELECT Id, Status__c, EOSId__c, OracleProjectCode__c, TourName__c, ShowCount__c, CreatedBy.Name
FROM Tour__c
WHERE EOSId__c <> NULL
""",
return_type='flat')
migrated_offers_uat = uat.select("""
SELECT Id, Status__c, EOSId__c, OracleProjectCode__c, TourName__c, ShowCount__c, CreatedBy.Name
FROM Tour__c
WHERE EOSId__c <> NULL
""",
return_type='flat')
migrated_offer_ids = {item['EOSId__c'] for item in migrated_offers}
migrated_offer_ids_uat = {item['EOSId__c'] for item in migrated_offers_uat}
remaining_to_migrate = [
item for item in offers_to_migrate
if item['EOSId__c'] not in migrated_offer_ids
]
offer_ids_remaining_to_migrate = [
item['EOSId__c'] for item in remaining_to_migrate
]
analysis_missing_eos_ids_by_tour = threading.new(
uk.analysis_missing_eos_ids_by_tour, sql, sf,
offer_ids_remaining_to_migrate)
costing_show_filenames = set(os.listdir(loc.uk_onsale_data_cache_path))
for item in migrated_offers + remaining_to_migrate:
shows = shows_by_offer_id.get(item['EOSId__c'], [])
shows_with_costing = [
item for item in shows
if f"{item['EOSId__c']}.pickle" in costing_show_filenames
]
item['Migrated in UAT?'] = item['EOSId__c'] in migrated_offer_ids_uat
item['# EOS EVT (w cancelled)'] = len(shows)
item['# Shows w Excel Costing'] = len(shows_with_costing)
for item in remaining_to_migrate:
item['Missing Master Data'] = ', '.join(
analysis_missing_eos_ids_by_tour.get(item['EOSId__c'], []))
def get_multiple_file_issues(df):
if len(df) == 0:
return []
groupby = df.groupby(['EventEOSId', 'Source_File'])
df2 = pd.DataFrame(groupby.groups.keys())
duplicated = df2.duplicated(subset=[0], keep=False)
df3 = df2[duplicated]
chunks = []
if len(df3) > 0:
groupby2 = df3.groupby(0)
for eosid in groupby2.groups:
files = df2.groupby(0).get_group(eosid)[1].tolist()
chunks.append(f'Show {eosid} in {", ".join(files)}')
return chunks
for item in remaining_to_migrate:
costing = costing_data_by_tour.get(item['EOSId__c'], None)
if costing:
tour = costing.Tour__c.iloc[0].to_dict()
lebs = costing.LedgerEntryBreakout__c
les = costing.LedgerEntry__c
ts = costing.TicketScale__c
item['Err_HasNegativeLEBs'] = True if (
lebs.OfferRate__c < 0).any() == True else None
# item['Err_TourHasNoShows'] = item['EOSId__c'] not in file_events_by_tour.groups
item['Err_OracleCodeMismatch'] = (
None if
(tour['OracleProjectCode__c'] == item['OracleCode']) else
f"{item['OracleCode']} in EOS, {tour['OracleProjectCode__c']} in file"
)
item['Err_DupePriceLevels'] = True if ts.duplicated(
subset=['SourceSystemId__c']).any() == True else None
item['Err_MultipleFileSources'] = ' | '.join(
get_multiple_file_issues(les) +
get_multiple_file_issues(lebs) + get_multiple_file_issues(ts))
remaining_to_migrate = pd.DataFrame(remaining_to_migrate)
for col in remaining_to_migrate.columns.values:
vals = remaining_to_migrate[col]
if col.startswith('Err') and ((vals.isna()) | (vals == '')).all():
del remaining_to_migrate[col]
file_name = 'UK On-Sale Migration Status ' + datetime.now().strftime(
'%Y-%m-%d') + '.xlsx'
file_name_2 = loc.uk_onsale_migration_status + 'UK On-Sale Migration Status ' + datetime.now(
).strftime('%Y-%m-%d %H.%M') + '.xlsx'
threading.new(
pdh.to_excel, {
'Remaining to Migrate': remaining_to_migrate,
'Migrated Offers': migrated_offers,
}, file_name)
threading.new(
pdh.to_excel, {
'Remaining to Migrate': remaining_to_migrate,
'Migrated Offers': migrated_offers,
}, file_name_2)
threading.wait()
return
def main():
dist_path = 'output/python-salesforce/'
dist_zip_path = 'output/python-salesforce.zip'
inclusions = [
r'example-credentials.json',
r'.vscode/launch.json',
r'.vscode/settings.json',
r'locations.py',
r'classes/',
r'functions/',
r'dataloader.py',
r'clone_utility.py',
r'utility_sf_tasks.py',
r'requirements.txt',
r'resources/ant/(?:build|fullpullpackage).xml',
# r'resources/chromedriver'
]
exclusions = [
r'.*?__pycache__',
r'.*?.DS_Store',
r'classes/gui.py',
r'classes/concept_parsing_sql_command',
r'classes/salesforce_api_helpers.py',
r'classes/csv_file.py',
r'classes/formula_parser.py',
r'classes/table_file.py',
]
inclusions = ['^' + s for s in inclusions]
exclusions = ['^' + s for s in exclusions]
all_files = [
f[f.find('python-salesforce') + len('python-salesforce') + 1:]
for f in dir.get_files_list('./', True)
]
filtered_files = []
for f in all_files:
matches_a_path = any([re.match(reg, f) != None for reg in inclusions])
matches_no_exclusion = all(
[re.match(reg, f) == None for reg in exclusions])
if matches_a_path and matches_no_exclusion:
filtered_files.append(f)
files = [(f, os.path.join(dist_path, f)) for f in filtered_files]
dir.del_dir(dist_path)
for src, tar in files:
s = dir.read_file(src)
if src.endswith('.py'):
s = s.replace('*****@*****.**', new_user_email)
if src.endswith('.py'):
s = s.replace('daniel.hicks_1', new_user_folder_name)
if 'example-credentials' in src:
tar = tar.replace('example-credentials', 'credentials')
dir.create_file(tar, s)
prompt(
f'\nFiles have been written to {dist_path}. When ready, press Enter to zip the folder'
)
# writing files to a zipfile
with zipfile.ZipFile(dist_zip_path, 'w') as zip:
files_to_zip = dir.listdir(dist_path, recursive=True)
for f in files_to_zip:
zip.write(f, f.replace('output/python-salesforce/', ''))
dir.del_dir(dist_path)
print(f'Zip file created:\n{os.path.abspath(dist_zip_path)}')
return
def migrate_uk_data(filepaths, multi=False):
issues = []
filedata = get_file_data(filepaths, multi)
offer_ids_list = filedata.Tour__c1.EOSId__c.tolist()
data = ObjDict(get_eos_data(offer_ids_list))
duplicate_tourids = filedata.Tour__c1[filedata.Tour__c1.duplicated('EOSId__c', False)][['EOSId__c', 'Source_File']]
duplicate_tourids['Issue'] = 'Tour EOS Id in >1 Source File'
issues.append(duplicate_tourids)
filedata.Tour__c1.drop_duplicates(subset='EOSId__c', inplace=True)
data.update(filedata)
data.Tour__c = data.Tour__c.merge(data.Tour__c1, on=['EOSId__c'], how='outer', suffixes=['_EOS',None], indicator=True)
data.Tour__c['RecordTypeId'] = data.Tour__c['RecordType.Name'].apply(lambda x: session.record_type_id_mapper()('Tour__c', x).Id)
data.TourDeal__c = (
data.TourDeal__c.merge(data.TourDeal__c1, on=['SourceSystemId__c'], how='outer', suffixes=['_EOS',None], indicator=True)
.query("_merge != 'left_only'")
.drop(columns='_merge')
)
data.TourLeg__c = (
data.TourLeg__c.merge(data.TourLeg__c1, on=['SourceSystemId__c'], how='outer', suffixes=['_EOS',None], indicator=True)
.query("_merge != 'left_only'")
.drop(columns='_merge')
)
data.Event__c = data.Event__c.merge(data.Event__c1, on='EOSId__c', suffixes=['_EOS',None], indicator=True)
data.Deal__c = data.Deal__c.merge(data.Deal__c1, on='Event__r.EOSId__c', suffixes=['_EOS',None], indicator=True)
del data['Tour__c1']
del data['TourDeal__c1']
del data['TourLeg__c1']
del data['Event__c1']
del data['Deal__c1']
set_computed_fields(data)
data.Issues = pd.concat(data.Issues + issues + migration_data_issues(data))
pdh.to_excel(data, f'{folder}OUTPUT - UK Data to Migrate.xlsx')
# Questions:
# Add Deductions?
if True or prompt('Ready to migrate data to Rome?', boolean=True):
session.add_bypass_settings()
results = {}
def upsert(func, *args, **kwargs):
results[args[0]] = func(*args, **kwargs)
upsert(session.upsert, 'Tour__c', data.Tour__c, 'EOSId__c')
upsert(session.upsert, 'TourDeal__c', data.TourDeal__c, 'SourceSystemId__c')
upsert(session.upsert, 'TourLeg__c', data.TourLeg__c, 'SourceSystemId__c')
upsert(session.upsert, 'TourOnSale__c', data.TourOnSale__c, 'SourceSystemId__c')
event_results = session.create_events(data, upsert_field_names={
'Event__c': 'EOSId__c'
, 'EventDateTime__c': 'SourceSystemId__c'
, 'Deal__c': 'SourceSystemId__c'
, 'TicketScale__c': 'EOSId__c'
# , 'Deduction__c': 'SourceSystemId__c'
, 'LedgerEntry__c': 'SourceSystemId__c'
, 'LedgerEntryBreakout__c': 'SourceSystemId__c'
})
upsert(session.upsert, 'EventOnSale__c', data.EventOnSale__c, 'SourceSystemId__c')
session.remove_bypass_settings()
success_results = {obj: result.success for obj, result in results.items()}
success_results.update(event_results.success)
error_results = {obj: result.errors for obj, result in results.items()}
error_results.update(event_results.errors)
pdh.to_excel({f'{key} ({len(val)})':val for key,val in success_results.items()}, f'{folder}OUTPUT - UK Data Migration Results - SUCCESS.xlsx')
pdh.to_excel({f'{key} ({len(val)})':val for key,val in error_results.items()}, f'{folder}OUTPUT - UK Data Migration Results - ERROR.xlsx')
print('')
return
