def inner(path, node):
for item in os.listdir(path):
itempath = path + '/' + item
if os.path.isdir(itempath):
newnode = ObjDict()
node[item] = newnode
inner(itempath, newnode)
else:
if item == 'package.xml':
continue
name = itempath.split('/')[-1]
if trim_suffixes:
name = name[:name.find('.')]
try:
if return_type == 'binary':
node[name] = open(itempath, 'rb').read()
elif return_type == 'dict':
node[name] = ObjDict.deepclone(
xmltodict.parse(
open(itempath, 'rb').read()))
elif return_type == 'records':
node[name] = flatten_dict(
xmltodict(open(itempath, 'rb').read()))
elif return_type == 'parsed':
node[name] = ObjDict.deepclone(
XmlParser(file_path=itempath))
except:
node[name] = open(itempath, 'rb').read()
def _get_opener(self, sheet_name=None, convert_to_text=True):
def conv_excel_date(tup):
t = [str(s) for s in tup]
if tup[3] + tup[4] + tup[5] == 0:
return "{}-{}-{}".format(t[0].zfill(4), t[1].zfill(2),
t[2].zfill(2))
else:
return "{}-{}-{}T{}:{}.{}Z".format(
t[0].zfill(4), t[1].zfill(2), t[2].zfill(2), t[3].zfill(2),
t[4].zfill(2), t[5].zfill(3))
if self.file_location.endswith(".csv"):
with open(self.file_location, 'r', encoding='utf-8-sig') as file:
csv_reader = csv.DictReader(file)
for row in csv_reader:
yield ObjDict(row)
elif self.file_location.endswith(
".xlsx") or self.file_location.endswith(
".xls") or self.file_location.endswith(".xlsm"):
xlsx_file = xlrd.open_workbook(self.file_location)
sheets = xlsx_file.sheet_names()
if len(sheets) > 1 and sheet_name == None:
print("\nWhat sheet would you like to read?")
print(self.file_location)
print("\n".join([
"\t{}: {}".format(i + 1, sheet)
for i, sheet in enumerate(sheets)
]))
sheet_name = sheets[int(input()) - 1]
elif sheet_name == None:
sheet_name = sheets[0]
sheet_to_open = xlsx_file.sheet_by_name(sheet_name)
headers = sheet_to_open.row_values(0)
for row_num in range(1, sheet_to_open.nrows):
new_row = dict()
src_row = sheet_to_open.row(row_num)
for col_num, field in enumerate(headers):
src_cell = src_row[col_num]
if src_cell.ctype == 1: # text
new_row[field] = src_cell.value
elif src_cell.ctype == 2 and convert_to_text: # number
new_row[field] = str(
src_cell.value).rstrip("0").rstrip(".")
elif src_cell.ctype == 3 and convert_to_text: # date
new_row[field] = conv_excel_date(
xlrd.xldate_as_tuple(src_cell.value,
xlsx_file.datemode))
if src_cell.ctype == 4 and convert_to_text: # bool
new_row[
field] = "true" if src_cell.value == 1 else "false"
else:
new_row[field] = src_cell.value
yield ObjDict(new_row)
def elem_passes_filter(curr_path_depth, elem):
if filter is None: return True
local_tree = ObjDict()
node = local_tree
for s in reversed(curr_path_depth[:-1]):
local_tree[s] = ObjDict()
node = local_tree[s]
node[curr_path_depth[-1]] = elem
try:
return eval(filter, {}, local_tree) is True
except AttributeError as e:
return True
except:
return False
def unflatten_records(records):
if type(records) is pd.DataFrame:
records = records.to_dict('records')
output = ObjDict()
for row in records:
field_values = ObjDict({
key[key.find('.') + 1:]: val
for key, val in row.items()
if key not in ('Location', 'Type') and pd.notnull(val)
})
field_values = ObjDict({
key:
([] if val == '#list#' else {} if val == '#dict#' else val)
for key, val in field_values.items()
})
node = output
for loc in row['Location'].split('|')[1:]:
if isinstance(node, dict):
if loc not in node:
node[loc] = ObjDict(
) if row['Type'] == 'property' else []
node = node[loc]
elif isinstance(node, list):
if loc not in node[-1]:
node[-1][loc] = ObjDict(
) if row['Type'] == 'property' else []
node = node[-1][loc]
if row['Type'] == 'property':
node.update(field_values)
elif row['Type'] == 'listitem':
node.append(field_values)
elif row['Type'] == 'listvalue':
node.append(field_values['value'])
return output
def get_file_data_by_tour_and_event(file_data):
offer_ids = file_data.Tour__c['EOSId__c'].tolist()
event_ids = file_data.Event__c['EOSId__c'].tolist()
file_data_tour_groups_by_table = {
k: v.groupby('TourEOSId')
for k, v in file_data.items() if len(v) > 0
}
file_data_by_tour = {
eosid: ObjDict({
k: file_data_tour_groups_by_table[k].get_group(eosid) if eosid
in file_data_tour_groups_by_table[k].groups else pd.DataFrame()
for k in file_data_tour_groups_by_table
})
for eosid in offer_ids
}
file_data_event_groups_by_table = {
k: v.groupby('EventEOSId')
for k, v in file_data.items() if 'EventEOSId' in v
}
file_data_by_event = {
eosid: {
k: file_data_event_groups_by_table[k].get_group(eosid) if eosid
in file_data_event_groups_by_table[k].groups else pd.DataFrame()
for k in file_data_event_groups_by_table
}
for eosid in event_ids
}
return file_data_by_tour, file_data_by_event
def get_populated_fields(data):
output = ObjDict()
for p, d in data.items():
if len(d) == 0:
continue
output[p] = set()
object_name = session.get_object_name(d[0].Id)
editable_fields = [
item['name']
for item in session.get_object_description(object_name).fields
if item['calculated'] is False and item['nillable'] is True
]
default_false_fields = [
item['name']
for item in session.get_object_description(object_name).fields
if item['type'] == 'boolean' and item['defaultValue'] is False
]
default_true_fields = [
item['name']
for item in session.get_object_description(object_name).fields
if item['type'] == 'boolean' and item['defaultValue'] is True
]
for r in d:
for f in editable_fields:
if f in r and r[f] is not None:
output[p].add(f)
for f in default_false_fields:
if f in r and r[f] is not False:
output[p].add(f)
for f in default_true_fields:
if f in r and r[f] is not True:
output[p].add(f)
return output
def get_costing_data_by_tour(re_run):
if re_run:
uk.extract_onsale_file_data(sf,
multi=True,
create_combined_file=True,
create_event_files=True,
ask_to_regenerate_files=re_run)
onsale_data = uk.get_cached_combined_file(
loc.uk_onsale_migration_combined_pickle)
offer_ids = onsale_data.Tour__c['EOSId__c'].tolist()
onsale_data_tour_groups_by_table = {
k: v.groupby('TourEOSId')
for k, v in onsale_data.items()
}
costing_data_by_tour = {
eosid: ObjDict({
k: onsale_data_tour_groups_by_table[k].get_group(eosid) if eosid
in onsale_data_tour_groups_by_table[k].groups else pd.DataFrame()
for k in onsale_data
})
for eosid in offer_ids
}
# onsale_data_event_groups_by_table = {
# k: v.groupby('EventEOSId')
# for k,v in onsale_data.items()
# }
# costing_data_by_event = {
# eosid: {
# k: onsale_data_event_groups_by_table[k].get_group(eosid) if eosid in onsale_data_event_groups_by_table[k].groups else pd.DataFrame()
# for k in onsale_data
# }
# for eosid in event_ids
# }
return costing_data_by_tour
def from_xml(self, file_path=None):
file_path = file_path or (self.source_file_path
if self.source_file_type == 'xml' else None)
self.xml_file_path = os.path.abspath(file_path)
with open(file_path, 'r') as f:
self.tree = ObjDict.deepclone(xmltodict.parse(f.read()))
self._meta_is_dirty
return self.tree
def get_recordtype_map(self, key=('SobjectType', 'Name')):
cname = 'get_recordtype_map'
assert isinstance(key, tuple), "Key parameter must be a tuple"
if cname not in self.cache:
self.cache[cname] = self.sf.select("SELECT * FROM RecordType", mute=True, mode='simple')
return ObjDict({
tuple([item[s] for s in key]): item
for item in self.cache[cname]
})
def finalize(output):
threading.wait()
for obj, records in output.items():
if len(records) == 0:
continue
object_name = session.get_object_name(records[0].Id)
object_desc = session.get_object_description(object_name)
for record in records:
for child_relationship in object_desc['childRelationships']:
record[child_relationship['relationshipName']] = []
for obj, records in output.items():
if len(records) == 0:
continue
object_name = session.get_object_name(records[0].Id)
object_desc = session.get_object_description(object_name)
lookup_fields = [
ObjDict(field) for field in object_desc['fields']
if field["type"] == "reference"
]
for field in lookup_fields:
for record in records:
if record[field.name] is not None and record[
field.name] in record_map:
parent_record = record_map[record[field.name]]
# Link parent record to child record
record[field.relationshipName] = parent_record
# Link child record to parent record
parent_record_object_name = session.get_object_name(
parent_record.Id)
child_relationship_name = [
item for item in session.get_object_description(
parent_record_object_name)['childRelationships']
if 'childSObject' in item and item['childSObject'] ==
object_name and item['field'] == field.name
]
if len(child_relationship_name) == 1:
parent_record[child_relationship_name[0]
['relationshipName']].append(record)
for event in output.events:
event.deals = event.Deals__r
# event.bonusdetails = event.Deals__r.BonusDetails__r
# event.stepups = event.Deals__r.ArtistRetroStepUpDetails__r
event.ticketscales = event.TicketScales__r
event.deductions = event.Deductions__r
event.ledgerentrybreakouts = event.LedgerEntryBreakouts__r
event.expenses = [
item for item in event.ledgerentrybreakouts
if item.LedgerEntry__r.RecordType.Name == 'Expenses'
]
event.ancillaries = [
item for item in event.ledgerentrybreakouts
if item in event.expenses
]
def main():
psdev = Salesforce_API('*****@*****.**')
sit = Salesforce_API('*****@*****.**')
lne = Salesforce_API('*****@*****.**')
session = lne
session.save_record_snapshot_on_select = True
tours = session.select("SELECT Id, TourName__c FROM Tour__c WHERE LastModifiedDate >= LAST_WEEK", contentType='JSON')
tourlegs = session.select("""
SELECT Id, Tour__c, LegName__c, Order__c, TicketScalePriceLevels__c
FROM TourLeg__c
WHERE Tour__c IN ('{}')
ORDER BY Tour__c, Order__c ASC
""".format("','".join([item.Id for item in tours])), contentType='JSON')
ticketscales = session.select("""
SELECT Id, Event__r.TourLeg__r.Tour__r.TourTitle__c, Event__r.TourLeg__r.LegName__c, Event__r.EventName__c, Event__r.TourLeg__c, Event__c, Type__c, Label__c
FROM TicketScale__c
WHERE Event__r.TourLeg__r.Tour__c IN ('{}')
""".format("','".join([item.Id for item in tours])), contentType='JSON')
issues = []
for tourleg in tourlegs:
tourleg.ticketscales = [item for item in ticketscales if item.Event__r.TourLeg__c == tourleg.Id]
labels = None if tourleg.TicketScalePriceLevels__c is None else ObjDict.deepclone(json.loads(tourleg.TicketScalePriceLevels__c))
label_map = {item['type']: item for item in labels} if labels is not None else None
for ts in tourleg.ticketscales:
config = label_map[ts.Type__c] if labels is not None and ts.Type__c in label_map else None
ts.tsOriginalLabel = ts.Label__c
ts.tourLegLabel = config.label if config is not None else None
if config is not None and config.type != config.label and ts.Label__c is None:
issues.append({'ts':ts, 'issue': "Ticket Scale missing label when custom label is set"})
ts.Label__c = config.label
elif config is not None and ts.Label__c != config.label and ts.Label__c is not None:
issues.append({'ts':ts, 'issue': "Ticket Scale label doesn't match Tour Leg Type-Label"})
ts.Label__c = config.label
elif config is None and ts.Label__c is not None:
if ts.Label__c == ts.Type__c:
issues.append({'ts':ts, 'issue': "Tour Leg has no Type-Label maps, but Ticket Scale has a value in Label__c that matches Type__c"})
else:
issues.append({'ts':ts, 'issue': "Tour Leg has no Type-Label maps, but Ticket Scale has a custom value in Label__c that is different than Type__c"})
# ts.Label__c = None # Need to use simple api
tourleg.labels = labels
pass
print('\n'.join([item['issue'] for item in issues]))
if len(issues) > 0:
pdh.multiple_df_to_excel({'Sheet1': pd.DataFrame(pdh.flatten(issues))}, 'Data Issues - TS Label__c.xlsx')
session.add_bypass_settings()
session.update(ticketscales)
session.remove_bypass_settings()
return
def get_file_data(filepaths, multi=False):
copromotersmap = {item.Name: item for item in copromoters.result()}
# dfs_ledgerentry = []
# dfs_ledgerentrybreakout = []
# for path in filepaths:
# try:
# xlsx_file = pd.ExcelFile(path)
# sheets = xlsx_file.sheet_names
# le_sheet = [s for s in sheets if s in ('Ledger Entry Export', 'Ledger Entry Report', 'Ledger entry export')][0]
# leb_sheet = [s for s in sheets if s in ('Ledger Entry Breakout Export', 'Breakout export')][0]
# le = pd.read_excel(xlsx_file, sheet_name=le_sheet)
# leb = pd.read_excel(xlsx_file, sheet_name=leb_sheet)
# le['Source_File'] = os.path.split(path)[-1]
# leb['Source_File'] = os.path.split(path)[-1]
# dfs_ledgerentry.append(le)
# dfs_ledgerentrybreakout.append(leb)
# except Exception as e:
# print(f'\n## Could not parse {path}:\n{e}\nIf list index out of range, then a sheet is missing')
all_data = ObjDict()
if multi:
params = [
(
path
, expenses_custom_metadata.result()
, copromotersmap
)
for path in filepaths
]
# params = [
# (
# dfs_ledgerentry[i].fillna('').query("`LedgerEntry__c.SourceSystemId__c` != ''")
# , dfs_ledgerentrybreakout[i].fillna('')
# , expenses_custom_metadata.result()
# , copromotersmap
# )
# for i in range(len(dfs_ledgerentry))
# ]
pool = multiprocessing.Pool()
results = pool.starmap(get_file_data_inner, params)
pool.close()
pool.join()
else:
results = [
get_file_data_inner(path, expenses_custom_metadata.result(), copromotersmap)
for path in filepaths
]
# df1 = pd.concat(dfs_ledgerentry).fillna('').query("`LedgerEntry__c.SourceSystemId__c` != ''")
# df2 = pd.concat(dfs_ledgerentrybreakout).fillna('')
# return get_file_data_inner(df1, df2, expenses_custom_metadata.result(), copromotersmap)
for obj in results[0]:
all_data[obj] = pd.concat([res[obj] for res in results if res is not None])
return all_data
def get_datatype(field):
output = ObjDict({'datatype': field.type, 'info': ''})
type = field.type
if type in ['string', 'textarea']:
output.datatype = field.extraTypeInfo if field.extraTypeInfo == 'richtextarea' else type
output.info = field.length
elif type in ['double', 'currency', 'percent']:
output.info = '({},{})'.format(field.precision - field.scale,
field.scale)
elif type in ['reference']:
output.info = '({})'.format(', '.join(field.referenceTo))
if field.calculated is True and field.calculatedFormula is None:
output.info += ' Rollup field'
# if type in ['string','textarea']:
# type = field.extraTypeInfo if field.extraTypeInfo == 'richtextarea' else type
# return '{} ({})'.format(type, field.length)
# elif type in ['double','currency','percent']:
# return '{} ({},{})'.format(type, field.precision - field.scale, field.scale)
# elif type in ['reference']:
# return '{} ({})'.format(type, ', '.join(field.referenceTo))
# elif type in ['boolean','picklist','multipicklist']:
# return '{}'.format(type)
# else:
# return type
return output
def read_excel(file_path, sheets=None, **kwargs):
xlsx = pd.ExcelFile(file_path)
if sheets is None:
sheets = xlsx.sheet_names
elif isinstance(sheets, str):
sheets = [sheets]
missing_sheets = [
sheet for sheet in sheets if sheet not in xlsx.sheet_names
]
assert not missing_sheets, f'The following sheets are missing from XLSX file "{file_path}"\n{missing_sheets}'
return ObjDict(
{sheet: pd.read_excel(xlsx, sheet, **kwargs)
for sheet in sheets})
def to_xml(self, file_path=None):
file_path = file_path or self.xml_file_path
copy = ObjDict.deepclone(self.tree)
self.fix_quotes(copy, 'GlobalValueSet.description')
self.fix_quotes(copy, 'GlobalValueSet.customValue.fullName')
self.fix_quotes(copy, 'GlobalValueSet.customValue.label')
s = xmltodict.unparse(copy,
encoding='UTF-8',
short_empty_elements=True,
pretty=True,
indent=' ') + '\n'
s = s.replace('"', '"').replace(''', ''')
if file_path is not None:
dir.create_file(file_path, s)
return s
def get_object_fields(session, metadata):
objects = ObjDict.deepclone(session.get_org_description()['sobjects'])
object_descs = {
obj.name: threading.new(session.get_object_description, obj.name)
for obj in objects
if obj.custom is True
or obj.name in ['Account','Contact']}
all_fields = []
for object_name, desc in object_descs.items():
# desc = session.get_object_description(object_name)
fields = pdh.flatten(desc.result().fields)
for f in fields:
f['ObjectName'] = object_name
all_fields.extend(fields)
df = pd.DataFrame(all_fields)
get_object_fields.dataset_name = 'Object Fields'
get_object_fields.merge_fields = ['ObjectName', 'name']
return df
def get_object_picklist_options(session, metadata):
objects = ObjDict.deepclone(session.get_org_description()['sobjects'])
object_descs = {
obj.name: threading.new(session.get_object_description, obj.name)
for obj in objects
if obj.custom is True
or obj.name in ['Account','Contact']}
all_picklist_options = []
for object_name, desc in object_descs.items():
# desc = session.get_object_description(object_name)
fields = [f for f in desc.result().fields if f.type in ['picklist','multipicklist']]
for f in fields:
for p in f.picklistValues:
p['ObjectName'] = object_name
p['Field Name'] = f.name
all_picklist_options.extend(f.picklistValues)
df = pd.DataFrame(all_picklist_options)
get_object_picklist_options.dataset_name = 'Object Picklist Options'
get_object_picklist_options.merge_fields = ['ObjectName', 'Field Name', 'label']
return df
def get_multilookup_object_fields(self, object_name, fields_to_lookup):
result = ObjDict()
root_fields = self.sf.get_object_fields(object_name)
root_relationships = self.sf.get_object_fields(object_name, 'lookupFieldsRelationshipName')
for field_name in fields_to_lookup:
if field_name in root_fields:
result[field_name] = root_fields[field_name]
continue
curr_object = object_name
curr_fields = root_fields
curr_relationships = root_relationships
for snippet in field_name.split('.'):
relation = curr_relationships.get(snippet, None)
rel_field = curr_fields.get(snippet, None)
if relation:
curr_object = relation.referenceTo[0]
curr_fields = self.sf.get_object_fields(curr_object)
curr_relationships = self.sf.get_object_fields(curr_object, 'lookupFieldsRelationshipName')
elif rel_field:
result[field_name] = rel_field
return result
def parse_query(query):
query = str(query).replace(u'\xa0', u' ') #Clean up bad space character
query = query.strip()
splitlines = query.split('\n')
query = '\n'.join(s for s in splitlines
if s.strip().startswith('--') is False)
regex = re.compile(
r'SELECT|[A-Z]+\([A-Z\d_]+\)\s+FROM|(\([^)]+\)|([A-Z]+)\([A-Z\d_]+\)(?:\s+([A-Z\d_]+))?|[A-Z\d_\.]+|\*(?:.([A-Z]+))?)',
re.IGNORECASE)
where_clause_regex = re.compile(
r'\([^)]+\)[\s\S]|FROM\s[A-Z\d_]+|(WHERE[\s\S]+)', re.IGNORECASE)
fields = []
unique_fields = {}
subqueries = []
where_clause = None
for res in re.finditer(regex, query):
group0, group1, group2, group3, group4 = res.group(0), res.group(
1), res.group(2), res.group(3), res.group(4)
if group1 == 'FROM' or group0.upper().endswith('FROM'): break
if group1 is None: continue
out = ObjDict({
'name': group1,
'is_star': group1 == '*',
'star_option': group4,
'is_subquery': group1.startswith('('),
'is_aggregate_function': group2 is not None,
'aggregate_function': group2,
'aggregate_label': group3,
})
fields.append(out)
if out.is_subquery: subqueries.append(out)
if not out.is_star: unique_fields[group1] = None
for res in re.finditer(where_clause_regex, query):
group1 = res.group(1)
if group1 is None: continue
where_clause = group1
return
def get_onsale_data():
data = {
'Offer':
sql.query("""
SELECT OfferId, ArtistName, OfferStatusName, Company, OracleCode
, FORMAT(MIN(ShowDate), 'yyyy-MM-dd') AS FirstDate
, FORMAT(MAX(ShowDate), 'yyyy-MM-dd') AS LastDate
, COUNT(*) AS ShowCount
, SUM(CAST(PostponedDateTBC AS INT)) AS PostponedDateTBCShows
FROM vwEOSShow
WHERE (ShowDate>=GetDate() OR PostponedDateTBC=1)
AND CountryId = 1
AND OfferStatusName IN ('Confirmed','On Sale','Settled','Draft')
GROUP BY OfferId, ArtistName, OfferStatusName, CountryName, Company, OracleCode
ORDER BY MAX(ShowDate) ASC
"""),
'ItineraryShow':
sql.query("""
SELECT DISTINCT OfferId, ShowId, ShowDate, VenueName, ArtistName, OfferStatusName
FROM vwEOSShow
WHERE ShowDate>=GetDate() OR PostponedDateTBC=1
"""),
}
return ObjDict({key: pd.DataFrame(val) for key, val in data.items()})
def get_metadata_reference_records():
metadata_reference_url = 'https://lneallaccess-my.sharepoint.com/:x:/g/personal/mike_wishner_lyv_livenation_com/EfcTIOXoayhCvDFq3t-kGEwB4aQc20-iALSiKqu-uLAxqw?e=0JJnGu&download=1'
# url = 'https://lneallaccess-my.sharepoint.com/personal/mike_wishner_lyv_livenation_com/_layouts/15/download.aspx?UniqueId=e52013f7%2D6be8%2D4228%2Dbc31%2D6adedfa4184c'
r = requests.get(metadata_reference_url, allow_redirects=True)
file_name = "./resources/Rome Touring Object Model.xlsx"
write_file = open(file_name, 'wb')
write_file.write(bytearray(r.content))
write_file.close()
xlsx_file = xlrd.open_workbook(file_name)
sheets = xlsx_file.sheet_names()
output = ObjDict()
for object_name, tab_name in metadata_tables.items():
excel_ref_sheet = xlsx_file.sheet_by_name(
tab_name) if tab_name in sheets else None
file_records = []
headers = []
if excel_ref_sheet is not None:
headers = excel_ref_sheet.row_values(0)
for row_num in range(1, excel_ref_sheet.nrows):
new_row = ObjDict()
src_row = excel_ref_sheet.row_values(row_num)
for col_num in range(0, len(headers)):
new_row[headers[col_num]] = src_row[col_num]
new_row.sObject = object_name
new_row.full_name = new_row.sObject + '.' + new_row.DeveloperName
file_records.append(ObjDict(new_row))
if object_name == 'PicklistOption__mdt':
for item in file_records:
item.Default__c = "true" if item.Default__c == 1 else "false"
item.AlwaysShown__c = "true" if item.AlwaysShown__c == 1 else "false"
item.AllowMultiple__c = "true" if item.AllowMultiple__c == 1 else "false"
item.GLCode__c = str(item.GLCode__c)[0:5]
output[object_name] = file_records
return output
def main(dataset_functions, multi, latest_from_cache=False):
sessions = ObjDict()
# sessions.dev1 = Salesforce_API('[email protected]')
# sessions.qa1 = Salesforce_API('[email protected]')
# sessions.psdev = Salesforce_API('*****@*****.**')
# sessions.sit = Salesforce_API('*****@*****.**')
sessions.uat = Salesforce_API('*****@*****.**')
sessions.lne = Salesforce_API('*****@*****.**')
# dataset_functions = {
# "Global Value Sets": get_global_value_set_options
# # , "Object Fields": get_object_fields
# # , "Object Picklists": get_object_picklist_options
# , 'Metadata Files': get_filebinary
# , 'Object Fields': get_object_fields
# , 'Object Field Picklists': get_object_field_picklistvalues
# # , 'Object List Views': get_object_listviews
# , 'Object Validation Rules': get_object_validationrules
# , 'Object Record Types': get_object_recordtypes
# , 'Object RecType Picklists': get_object_recordtype_picklistvalues
# , 'Roles': get_roles
# , 'Groups': get_groups
# , 'Profiles': get_profiles
# , 'Permission Sets': get_permissionsets
# , "Sharing Rules": get_sharingrules
# , "Custom Metadata": get_custom_metadata
# # , ""
# }
if latest_from_cache:
def last_saved(instance):
print(f'Using last-cached data for {instance}')
root_path = './resources/ant/'
folder_path = sorted([f for f in os.listdir(root_path) if f'{instance} retrieve' in f], reverse=True)[0]
return AntMetadataFolder(root_path + folder_path)
metadata = {
instance: threading.new(last_saved, instance)
for instance, session in sessions.items()
}
else:
metadata = {
instance: threading.new(metadata_full_pull, session)
for instance, session in sessions.items()
}
# datasets = {
# dataset_name: {
# instance: (function, session, metadata[instance])
# for instance, session in sessions.items()
# }
# for dataset_name, function in dataset_functions.items()
# }
# dataset_key_fields = {
# "Global Value Sets": ['GlobalValueSetName', 'valueName']
# , 'Metadata Files': ['FileName']
# , "Object Fields": ['ObjectName', 'name']
# , "Object Picklists": ['ObjectName', 'Field Name', 'label']
# , 'Object Fields': ['ObjectName', 'fullName']
# , 'Object Field Picklists': ['ObjectName', 'FieldName', 'fullName']
# , 'Object List Views': ['ObjectName', 'fullName']
# , 'Object Validation Rules': ['ObjectName', 'fullName']
# , 'Object Record Types': ['ObjectName', 'fullName']
# , 'Object RecType Picklists': ['ObjectName', 'RecordType', 'Picklist', 'fullName']
# , 'Roles': ['FileName','name']
# , 'Groups': ['FileName','name']
# , 'Profiles': ['ProfileName','Attribute','field','application','apexClass','layout','object','apexPage','recordType','tab','name']
# , 'Permission Sets': ['PermissionSetName','Attribute','field','application','apexClass','object','apexPage','recordType','tab','name']
# , "Sharing Rules": ['RuleType', 'ObjectName.Name', 'fullName']
# , "Custom Metadata": ['ObjectName.Name', 'ObjectName', 'DeveloperName']
# }
# diff_instances(metadata, sessions, dataset_functions, multi, 'uat', 'psdev')
diff_instances(metadata, sessions, dataset_functions, multi, 'uat', 'lne')
# # diff_instances(datasets, dataset_key_fields, 'dev1', 'psdev')
# diff_instances(datasets, dataset_key_fields, 'dev1', 'qa1')
# diff_instances(datasets, dataset_key_fields, 'dev1', 'uat')
# diff_instances(datasets, dataset_key_fields, 'psdev', 'sit')
# diff_instances(datasets, dataset_key_fields, 'psdev', 'uat')
# diff_instances(datasets, dataset_key_fields, 'psdev', 'lne')
threading.wait()
return
def main():
downloads_folder = dir.get_download_folder()
files = [(f, os.path.getctime(f))
for f in dir.listdir(downloads_folder, False, True)
if f.endswith('.zip') and 'OneDrive_' in f]
files.sort(key=lambda item: item[1], reverse=True)
source_zip_file_path = files[0][0]
timenow = datetime.datetime.now().strftime('%Y-%m-%d %H.%M')
# evenko_folder_path = '/Users/daniel.hicks_1/Documents/Rome/Rome Downloads/Evenko/'
# import_zip_path = f'{evenko_folder_path}Templates.zip'
# import_folder_path = f'{evenko_folder_path}Templates/'
migration_folder_path = f'/Users/daniel.hicks_1/Documents/Rome/Rome Downloads/Evenko/Migration Source/Evenko Migration {timenow}/'
with zipfile.ZipFile(source_zip_file_path, "r") as zip_ref:
zip_ref.extractall(migration_folder_path)
import_data_path = f'{migration_folder_path}/Historical data'
# import_folder_path = '/Users/daniel.hicks_1/Documents/Rome/Rome Downloads/Historical data Evenko 2021-08-11'
def map_sheet_name(v):
return ('EventDateTime__c' if 'EventDateTime' in v else
'Event__c' if 'Event' in v else 'Deal__c' if 'Deal' in v else
'TicketScale__c' if 'TicketScale' in v else 'Deduction__c'
if 'Deduction' in v else 'LedgerEntry__c' if 'LedgerEntry_' in
v else 'LedgerEntryBreakout__c' if 'LedgerEntryActive' in
v else None)
data = ObjDict({
# map_sheet_name(f): pd.read_excel(import_data_path+'/'+f)
map_sheet_name(f): pd.read_csv(import_data_path + '/' + f,
encoding='ISO-8859-1')
for f in os.listdir(import_data_path) if map_sheet_name(f) is not None
})
for obj, df in data.items():
df.replace('#COMA#', ',', inplace=True, regex=True)
df.replace('#CHAR13#', '\n', inplace=True, regex=True)
df.replace('#CHAR10#', '\r', inplace=True, regex=True)
df.replace('NULL', np.nan, inplace=True)
data.update(
{key: pd.DataFrame()
for key in objects_to_import if key not in data})
lneaccounts = threading.new(lne.select,
"SELECT Id, SourceSystemId__c FROM Account",
mute=True)
uataccounts = threading.new(uat.select,
"SELECT Id, SourceSystemId__c FROM Account",
mute=True)
evenkoofficeid = uat.select(
"SELECT Id FROM Account WHERE RecordType.Name = 'Office' AND Name = 'Evenko'",
mute=True,
mode='simple',
cache_duration=24 * 60)[0].Id
user_email_mapper = uat.user_mapper(
'Email',
return_field='Id',
where_clause="WHERE UserRole.Name LIKE '%Evenko%'")
# glaccountsmap = {
# item.GLCode__c: item for item in
# uat.select("SELECT Id, Name, Type__c, GLCode__c, Category__c FROM GLAccount__c WHERE ActiveFlag__c = True", mute=True, mode='simple', cache_duration=24*60)
# }
glaccountsmap = (threading.new(
uat.select,
"SELECT Id, Name, Type__c, GLCode__c, Category__c FROM GLAccount__c WHERE ActiveFlag__c = True",
mute=True,
mode='simple',
cache_duration=24 *
60).then(lambda result: {item.GLCode__c: item
for item in result}))
# FIXES
data.Deal__c.Artist__c.replace('Coheadlner', np.nan, inplace=True)
data.Event__c.rename({'MDAID': 'EvenkoAACode__c'}, inplace=True)
evt = data.Event__c
edt = data.EventDateTime__c
deal = data.Deal__c
ts = data.TicketScale__c
ded = data.Deduction__c
le = data.LedgerEntry__c
leb = data.LedgerEntryBreakout__c
# leb = leb[leb['OfferRate__c'].str.isnumeric().fillna(False)]
evt['Office__c'] = evenkoofficeid
# evt['Promoter__c'] = evt['Promoter__r'].apply(lambda x: user_email_mapper(x.lower()) if type(x) is str else None)
# evt['ProductionManager__c'] = evt['ProductionManager__r'].apply(lambda x: user_email_mapper(x.lower()) if type(x) is str else None)
# evt['TicketingManager__c'] = evt['TicketingManager__r'].apply(lambda x: user_email_mapper(x.lower()) if type(x) is str else None)
# pdh.to_excel(data, import_folder_path+'/'+'Combined.xlsx')
# # If only Plan or only Projection Ticket Scale records were provided for an event, then copy the Ticket Scales so that both Plan and Projection are inserted
# copied_ts_dfs = []
# for event_id in evt['SourceSystemId__c'].tolist():
# evt_ts_df = ts[ts['Event__r.SourceSystemId__c'] == event_id]
# plan = evt_ts_df[evt_ts_df['StageType__c'] == 'Plan']
# projection = evt_ts_df[evt_ts_df['StageType__c'] == 'Projection']
# if len(plan) > 0 and len(projection) == 0:
# new = plan.copy()
# new['StageType__c'] = 'Projection'
# copied_ts_dfs.append(new)
# if len(projection) > 0 and len(plan) == 0:
# new = projection.copy()
# new['StageType__c'] = 'Plan'
# copied_ts_dfs.append(new)
# ts = pd.concat([ts] + copied_ts_dfs)
if le is not None:
if 'GLAccount__r.GLCode__c' in le.columns:
le['GLCode'] = le['GLAccount__r.GLCode__c']
le['RecordType.Name'] = le['GLAccount__r.GLCode__c'].apply(
lambda x: glaccountsmap[x].Type__c)
le['BookingSettlement__c'] = True
data.LedgerEntry__c = le
# artistnamesmap = {item.Id:item.Name for item in artistnames.result()}
# primaryheadlinerdeals = deal.copy()[deal['Type__c'] == 'Primary Headliner']
# primaryheadlinerdeals['SourceSystemId__c'] = primaryheadlinerdeals['Event__r.SourceSystemId__c']
# primaryheadlinerdeals['PrimaryHeadlinerArtist__c'] = primaryheadlinerdeals['Artist__c'].apply(lambda x: artistnamesmap[x])
# primaryheadlinerdeals = primaryheadlinerdeals[['SourceSystemId__c', 'PrimaryHeadlinerArtist__c']]
# evt = evt.merge(right=primaryheadlinerdeals, how='left', on='SourceSystemId__c')
# Data fixes
# evt.rename(columns={'Event__c.Venue__c': 'Venue__c'}, inplace=True)
ded.rename(
columns={'Event__r.SourceSystemId__c ': 'Event__r.SourceSystemId__c'},
inplace=True)
# deal['RecordType.Name'] = 'Artist'
# deal['Type__c'] = 'Primary Headliner'
deal.rename(columns={
'Deal__c.Agency__c': 'Agency__c',
'Deal__c.Agent__c': 'Agent__c'
},
inplace=True)
if le is not None and len(le) > 0:
# le.rename(columns={'GLCode__c': 'GLAccount__r.GLCode__c', 'SourceSystemId__c': 'Event__r.SourceSystemId__c'}, inplace=True)
le.fillna('', inplace=True)
le.query("`GLCode__c` != ''", inplace=True)
event_lookup = {
item['SourceSystemId__c']: item
for item in evt.to_dict('records')
}
edt['Venue__c'] = edt['Event__r.SourceSystemId__c'].apply(
lambda x: event_lookup[x]['Venue__c'] if x in event_lookup else '')
# del data['LedgerEntry__c']
# event_filter = 'EvenkoDeal-12986'
# # uat.delete_events(f"SELECT Id FROM Event__c WHERE SourceSystemId__c = '{event_filter}'")
# evt.query("SourceSystemId__c == @event_filter", inplace=True)
# edt.query("`Event__r.SourceSystemId__c` == @event_filter", inplace=True)
# ded.query("`Event__r.SourceSystemId__c` == @event_filter", inplace=True)
# le.query("`Event__r.SourceSystemId__c` == @event_filter", inplace=True)
# ts.query("`Event__r.SourceSystemId__c` == @event_filter", inplace=True)
# deal.query("`Event__r.SourceSystemId__c` == @event_filter", inplace=True)
# Re-names in order to insert records to UAT instead of PROD
# if uat.instance == 'uat':
# uat_ids_map = {item.Id: item for item in uataccounts.result()}
# uat_src_ids_map = {item.SourceSystemId__c: item for item in uataccounts.result()}
# def get_uat_id(id):
# if id not in uat_ids_map:
# if id in uat_src_ids_map:
# return uat_src_ids_map[id].Id
# else:
# return ''
# return id
# evt['Venue__c'] = evt['Venue__c'].apply(get_uat_id)
# evt['Office__c'] = evt['Office__c'].apply(get_uat_id)
# deal['Artist__c'] = deal['Artist__c'].apply(get_uat_id)
# deal['CoPromoter__c'] = deal['CoPromoter__c'].apply(get_uat_id)
# deal['Agency__c'] = deal['Agency__c'].apply(get_uat_id)
# deal['Agent__c'] = deal['Agent__c'].apply(get_uat_id)
del data['LedgerEntry__c']
# Model to populate all remaining fields
model = SalesforceLiveNationModelCompute(
uat,
**data,
set_null_datasets_to_empty_list=True,
)
computed = model.compute_all(
keep_exception_columns=True,
to_compute={
'Event__c': {
'RecordTypeId', 'ShowCount__c', 'ShowCountWithDates__c',
'EventFirstDate__c', 'EventLastDate__c', 'EventYear__c',
'PrimaryHeadlinerArtist__c', 'Artists__c', 'IsTouringApp__c',
'OfficeName__c', 'Division__c', 'Geography__c',
'PrimaryVenueOffice__c', 'VenueOwnership__c',
'HiddenVenueOffice__c'
}
})
print(f'Failed: {computed.failed}')
# if 'LedgerEntry__c' in computed.data2:
# del computed.data2.LedgerEntry__c['GLAccount__c']
pdh.to_excel(computed.data2,
migration_folder_path + '/' + 'Combined Import Data.xlsx')
os.remove(source_zip_file_path)
# Filters for testing purposes
# data.Event__c = evt[pd.notnull(evt['Venue__c'])].copy()
# event_src_ids = set(data.Event__c['SourceSystemId__c'].tolist())
# for obj, df in data.items():
# if 'Event__r.SourceSystemId__c' in df.columns:
# data[obj] = df[df['Event__r.SourceSystemId__c'].apply(lambda x: x in event_src_ids) == True].copy()
# pdh.to_excel(data, import_folder_path+'/'+'CombinedFiltered.xlsx')
# # Deletion for testing purposes, to have a fresh slate
# uat.delete_events("SELECT Id FROM Event__c WHERE SourceSystemId__c IN ('{}')".format("','".join(evt['SourceSystemId__c'].tolist())))
upsert_event_data_to_rome(uat, computed.data2, True)
# result = uat.create_events(
# computed.data2
# , {
# 'Event__c': 'SourceSystemId__c'
# , 'EventDateTime__c': 'SourceSystemId__c'
# }
# , delete_old_child_records=True
# , run_fc=False
# )
# pdh.to_excel({key:val for key,val in result.errors.items() if len(val) > 0}, import_folder_path+'/'+'Migration Results - ERRORS.xlsx')
# pdh.to_excel(result.success, import_folder_path+'/'+'Migration Results - SUCCESS.xlsx')
return
def upsert_event_data_to_rome(sf,
all_data,
delete_events_first=False,
**kwargs):
all_data = ObjDict(all_data)
all_data.setdefault(None)
sf.default_mode = 'bulk'
if delete_events_first:
event_ids_to_delete = all_data.Event__c['SourceSystemId__c']
sf.delete_events(
"SELECT Id FROM Event__c WHERE SourceSystemId__c IN @event_ids_to_delete"
)
with sf.bypass_settings():
internalcopro_deal_data = all_data.Deal__c.query(
'`RecordType.Name` == "Co-Promoter" and Type__c == "Internal"')
other_deal_data = all_data.Deal__c.query(
'`RecordType.Name` != "Co-Promoter" or Type__c != "Internal"')
tou = threading.new(sf.upsert, 'Tour__c', all_data.Tour__c,
'SourceSystemId__c').wait()
tde = threading.new(sf.upsert, 'TourDeal__c', all_data.TourDeal__c,
'SourceSystemId__c').wait()
leg = threading.new(sf.upsert, 'TourLeg__c', all_data.TourLeg__c,
'SourceSystemId__c').wait()
leg = threading.new(sf.upsert, 'TourOnSale__c', all_data.TourOnSale__c,
'SourceSystemId__c')
evt = threading.new(sf.upsert, 'Event__c', all_data.Event__c,
'SourceSystemId__c').wait()
edt = threading.new(sf.upsert, 'EventDateTime__c',
all_data.EventDateTime__c, 'SourceSystemId__c')
eos = threading.new(sf.upsert, 'EventOnSale__c',
all_data.EventOnSale__c, 'SourceSystemId__c')
de1 = threading.new(sf.upsert, 'Deal__c', internalcopro_deal_data,
'SourceSystemId__c').wait()
de2 = threading.new(sf.upsert, 'Deal__c', other_deal_data, 'Id')
led = threading.new(sf.upsert_ledgerentries, 'LedgerEntry__c',
all_data.LedgerEntry__c, 'SourceSystemId__c', evt,
'SourceSystemId__c')
led = threading.new(sf.upsert_deductions, 'Deduction__c',
all_data.Deduction__c, 'SourceSystemId__c', evt,
'SourceSystemId__c')
ts1 = threading.new(sf.upsert_ticketscales, 'TicketScale__c',
all_data.TicketScale__c, 'Id', evt,
'SourceSystemId__c')
led.wait()
leb = threading.new(sf.upsert, 'LedgerEntryBreakout__c',
all_data.LedgerEntryBreakout__c, 'Id')
sf.run_fc_async([item['Id'] for item in evt.result() if 'Id' in item])
threads = [var for var in locals().values() if type(var) is Thread]
jobs = ObjDict({th.result().object_name: th for th in threads})
for job in jobs.values():
if len(job.errors) > 0:
errors_df = pd.DataFrame(job.errors)[['sf_result']]
print(f'{job.object_name} errors:\n{errors_df}')
if len(jobs.Event__c.results) < 10:
print('New Events:')
print('\n'.join(
pd.DataFrame(jobs.Event__c.results)['Id'].apply(
lambda x: f'https://{sf.simple.sf_instance}/{x}').tolist()
[:10]))
sf.default_mode = 'simple'
return jobs
def main():
# sql = SQL_Server_API(eos_prod_creds)
# sf = Salesforce_API('*****@*****.**')
sql = SQL_Server_API(eos_stage_creds)
sf = Salesforce_API('*****@*****.**')
sf.bypass_prod_operation_approval()
delete_tours_first = False
skip_already_created_tours = False
# all_21_sept = [37225,31270,31270,22916,22695,53206,52018,52018,54352,52872,52090,51322,51322,50940,42648,20125,22958,29920,23463,22398,19370,19370,22884,28579,26342,35517,38949,38949,38586,35627,33668,28753,31358,35591,39302,35581,22495,52850,44233,44265,44265,51030,51028,43995,41284,41284,43884,42729,53383,49144,33642,40596,38839,41018,41018,44235,44235,37310,48932,50809,49168,39253,39253,39874,39874,46689,42649,42649,40842,51665,42653,49209,50770,40975,51555,51555,51560,35693,31353,31220,31220,37306,30794,23619,23619,38877,38877,30697,31576,37381,38894,32096,32096,33484,33484,30044,34283,23121,19405,22356,22356,23668,24095,25145,28657,19175,23122,23152,20541,20367,23745,23744,22532,23024,23505,25144,20225,20225,22689,20448,20448,20374,20374,30189,54035,54544,40974,41007,31962,33940,33940,34009,39123,22324,22835,22835,22825,22825,22870,23123,23123,23155,32282,52020,37205,37205,43740,53714,48756,50791,50791,39719,52059,52428,51243,51243,51589,50762,50762,47739,38843,51102,45676,45676,49117,48857,48857,51261,48847,41321,40872,50865,43765,41338,38841,44251,41404,41404,45686,44488,44488,35803,40531,41303,40578,42612,40965,43873,43873,40866,38790,38790,40986,40986,40867,40867,40748,40031,40397,39421,37335,37335,40453,37140,37140,22832,22832,39128,37509,34220,39715,38839,37174,37006,39178,31041,34156,39736,37426,37426,35557,35721,35721,31854,37251,37251,32278,38549,38540,39186,38959,32052,37453,40102,38976,37029,37029,39020,31054,31558,37485,31844,35620,33628,33966,30910,30910,30697,31664,31664,30811,30811,31542,31542,31029,33684,31974,31309,31345,31001,30696,30583,28593,30315,23619,23619,24113,24113,28769,30172,30172,28622,23622,23622,23795,23795,23597,23849,23849,30209,25157,30684,23869,23869,23882,30731,23845,23845,23845,25178,25178,24021,24021,22914,25117,22945,23690,25163,29923,25179,25179,28701,28701,30036,28684,28571,24100,23657,23656,23791,26332,22829,30244,28582,24098,24098,23713,23319,23319,28699,23366,24108,23500,22045,22045,22452,22831,22831,22836,22836,20881,22029,23498,22446,20440,20440,22425,19446,19446,20882,20773,20773,22436,19396,20637,20373,20373,20484,20484,22596,20451,19343,19343,18763,18633,20830,22256,54015,51782,53686,51104,51352,40287,51534,48753,51404,51404,48899,49137,49137,40952,45535,49190,49190,51624,51624,40860,42670,44103,52470,45678,40258,43800,43800,41446,39966,39638,40229,40235,37514,39713,37435,38633,37128,40997,39935,39935,37027,37047,34385,38921,37342,39112,38922,35684,35684,30310,30501,30501,30246,30947,30947,31113,31055,22918,30268,28575,24101,24101,25126,23938,23938,23919,22535,22524,22524,22524,20237,20565,28635,28635,29885,18623,31737,39346,39346,39263,52027,52028,41009,44119,44373,49243,50779,51153,51153,37515,31116,22312,24037,24037,29836,29836,22261,52324,40252,40252,39707,39707,31374,45657,49321,49321,53613,54619,40132,41317,49210,35543,51594,53300,24022,35585,39184,20954,49573,48970,48970,39304,39304,23871,45685,43742,43742,20844,20844,20844,34376,23523,23523,22553,22553,23323,28576,22438,39113,30360,30360,34232,34232,50646,51002,39728,22993,22448,22448,26195,20713,22192,20478,52718,53284,54553,39945,40425,40518,48880,39566,40218,51159,50625,38981,43950,51469,52362,52362,41037,41104,44505,49143,51780,52383,50918,40457,40457,39629,39629,38983,38811,43818,51511,49244,49244,43903,40984,41008,41008,35839,35839,49520,49520,49441,49441,32090,31636,35889,38829,33860,31949,33996,28616,28616,31368,31935,30848,33680,35780,37309,37370,30507,30507,31075,34289,37167,33505,34028,34028,40579,34309,38694,38701,39648,31959,33484,33484,35659,34065,40127,32376,39745,23488,23873,23873,29829,26200,22449,24106,24106,19990,19990,22040,22550,22662,23522,23522,30175,30375,25124,28637,29956,29956,20304,20294,22454,20685,20685,24040,23653,26354,30177,24093,24093,19752,22264,38575,38575,40385,40385,40894,51911,51911,41194,44374,44374,44402,50807,50998,52686,53459,40699,40987,41001,49335,52160,52422,52348,31551,31544,31545,31546,31547,31548,37463,34399,33616,33616,39571,39125,23642,22271,38840,37431,22660,22660,24022,33642,28635,28635,38973,49322,49322,46730,23622,23622,52851,31711,20480,22406,38702,31947,33723,33723,32150,51045,51045,51359,51359,53542,38853,35592,22612,20210,52242,52242,28770,30537,30537,52721,53925,51266,43845,43845,53076,40290,41340,49533,40540,40958,44177,44177,33679,36979,35939,37130,37130,13218,13218,23297,23911,22147,23145,23145,51011,51011,39678,39678,28821,28821,31531,30487,51651,51651,30476,24067,24067,20194,52018,52018,30190,32059,32061,49450,51208,51208,30851,30851,34043,51504,41002,51384,51384,18201,20155,52210,52445,52637,52322,52322,52322,53888,40261,40261,44248,44526,51448,49534,40135,43763,43763,40549,40833,40833,39727,30854,30854,39173,36996,32398,32398,30835,30835,34423,34423,30950,30950,39391,33965,39514,33702,31945,34244,19855,19855,22816,20709,20350,20350,23788,20599,52832,50870,53129,53129,46730,53516,51653,51750,52357,52470,50792,50792,48980,50827,50827,39043,39043,45569,41467,51509,51405,51591,53245,51756,50637,50951,41206,48894,40239,40239,44382,38833,41493,41493,40538,32283,39515,39515,39730,38545,39615,39615,37432,38673,38673,39288,39489,35943,33674,34405,35948,34007,32395,32395,33757,35683,33955,31061,33781,30961,31110,31552,31312,31831,31831,30493,30293,30293,30276,30547,24008,24008,24009,23291,24015,23157,24011,19446,19446,23189,22830,21986,22834,22834,22038,22273,18889,19659,20566,20573,20573,19413,30530]
# all_22 = [37225,31270,22916,22695,53206,52018,54352,52872,52090,51322,50940,42648,20125,22958,29920,23463,22398,19370,22884,28579,26342,35517,38949,38586,35627,33668,28753,31358,35591,39302,35581,22495,52850,44233,44265,51030,51028,43995,41284,43884,42729,53383,49144,33642,40596,38839,41018,44235,37310,48932,34107,50809,49168,39253,39874,46689,42649,40842,51665,41576,42653,49209,50770,40975,51555,51560,35693,31353,31220,37306,30794,23619,38877,30697,31576,40326,40323,40179,37381,38894,32096,33484,30044,34283,23121,19405,22356,23668,24095,25145,28657,19175,23122,23152,20541,20367,23745,23744,23480,22532,23024,23505,25144,20225,22689,20448,20374,30189,19750,54035,54544,40974,41007,31962,33940,34009,30882,39123,22324,23670,41563,22835,22825,22870,23123,23155,32282,38815,52020,39743,41085,37205,43740,53714,48756,50791,39719,52059,52428,51243,51589,50762,47739,38843,51102,45676,49117,48857,51261,48847,41321,40872,50865,43765,41338,38841,44251,41404,45686,44488,35803,40531,41303,40578,42612,40965,43873,40866,38790,40986,40867,40748,40031,40397,39421,37335,40453,37140,22832,39128,37509,34220,39715,38839,37174,37006,39178,31041,34156,39736,37426,35557,35721,31854,37251,32278,38549,38540,39186,38959,32052,37453,30106,40102,38976,37029,39943,39020,31054,31558,37485,31844,35620,33628,33966,30910,30697,31664,30811,31542,31029,33684,31974,31309,31345,31001,30696,30583,28593,30315,23619,24113,28769,30172,28683,28622,23622,23795,23597,23849,30209,25157,30684,23869,23882,23129,30731,23845,25178,24021,22118,22914,25117,22945,23690,25163,23337,29923,25179,19750,28701,30036,28684,28571,24100,23657,23656,23791,26332,22829,30244,28582,24112,24098,23713,23319,28699,23366,24108,23500,22045,22452,22831,22836,20881,22029,23498,22260,22446,20440,22425,19446,20882,20773,22436,20447,19560,19396,20637,20373,19392,20484,22596,20451,19343,18763,18633,20830,22256,54015,51782,53686,51104,51352,40287,51534,48753,51404,48899,49137,40952,45535,49190,51624,40860,42670,44103,52470,45678,40258,43800,41446,39966,39638,40229,40235,37514,39713,37435,38633,37128,40997,39935,37027,37047,34385,38921,37342,39112,38922,35684,30310,30501,30246,30947,31113,31055,22918,30268,28575,24101,25126,23938,23919,22535,22524,20237,20565,28635,29885,20777,18623,31737,39346,39263,30882,52027,52028,41009,44119,44373,49243,50779,51153,37515,31116,22312,24037,29836,22261,52324,40252,39707,31374,45657,49321,53613,54619,40132,41317,34107,49210,35543,51594,53300,24022,35585,39184,20954,49573,48970,39304,23871,45685,43742,20844,20764,34376,23523,22553,23323,28576,22438,39113,30360,34232,50646,51002,39728,22993,22448,26195,20713,22192,28564,20478,52718,53284,54553,39945,40425,39700,40518,48880,39566,40218,51159,50625,38981,43950,51469,52362,41037,41104,44505,49143,51780,34107,52383,50918,40457,39629,38983,38811,43818,51511,49244,43903,40984,41008,35839,49520,49441,32090,31636,35889,38829,33860,31949,33996,28616,31368,31935,30848,32065,33680,35780,37309,37370,30507,31075,34289,37167,33505,34028,40579,34309,38694,38701,39648,31959,33484,35659,34065,40127,32376,39745,23488,23873,29829,26200,22449,24106,19990,22040,20925,22550,22662,23522,30175,30375,25124,28637,29956,20304,20294,22454,20685,24040,23653,26354,30177,24093,19752,22264,38575,40385,40894,51911,41194,44374,44402,50807,50998,52686,53459,40699,40987,41001,49335,52160,52422,52348,31551,31544,31545,31546,31547,31548,37463,34399,33616,39571,39125,23642,23595,22271,38840,37431,22660,24022,33642,28635,38973,49322,39961,46730,23622,52851,31711,20480,22406,38702,51354,39424,31947,33723,32150,51045,51359,53542,38853,35592,22612,20210,52242,28770,30537,41248,52721,53925,51266,43845,53076,40290,41340,49533,40540,40958,44177,33679,36979,35939,37130,13218,23297,23911,22147,23145,51011,39678,28821,31531,30487,51651,30476,24067,20194,52018,30190,32059,32061,49450,51208,30851,34043,41468,51504,41002,51384,18201,20155,52210,52445,52637,52322,53888,40261,44248,44526,51448,49534,40135,43763,40549,40833,39727,30854,39173,36996,32398,30835,34423,30950,39391,33965,39514,33702,31945,34244,19855,22816,20709,20350,23788,20599,52832,50870,53129,46730,53516,51653,51131,51750,52357,52470,50792,48980,50827,39043,45569,48942,41467,51509,51405,51591,53245,51756,50637,50951,41206,48894,39216,40239,44382,38833,41493,40538,32283,39515,39730,40125,38545,39615,37432,38673,39288,39489,35943,33674,31566,34405,35948,34007,32395,33757,35683,33955,31061,33781,30961,31110,31552,31312,31831,30493,30293,30276,30547,22088,24008,24009,23291,24015,23157,24011,19446,23189,22830,19750,21986,22834,19555,22038,22273,18889,19659,20566,20573,19413,39193,30530,20474,30573,49546]
# lewis_1102 = [22829,22831,22660,22662,22830,22695,22816,22495,22524,22596,22535,30177,30189,30268,30293,30310,30315,30360,30476,30487,30501,30530,30696,30583,30276,30684,30493,30209,30507,30244,30375,30547,30246,28657,28701,28753,28821,29829,29920,30172,30175,29923,29836,28684,29885,28699,22832,19659,23463,23505,23522,20155,19370,19396,19413,23323,23498,20194,23488,19446,19405,19752,20125,22449,22452,22454,23157,23189,23795,23882,23919,23938,24015,24021,23668,23690,24022,30794,30811,23713,23791,23291,23319,24067,30854,30848,22045,22264,22356,22398,22406,22446,24008,30731,23744,23745,23788,24037,24040,30910,22448,23845,23656,23657,23297,22261,22436,23849,24009,22273,23911,22438,24011,22192,22312,22324,24093,20350,20367,13218,18633,18763,18201,18623,20374,20882,22029,33781,33860,34007,40952,34043,41009,41018,34309,20713,20830,20881,33684,33940,41007,35581,34220,34232,20954,41194,34244,34283,33668,34009,41037,41284,34385,34399,21986,34289,34405,33674,34423,22038,40958,40965,40975,33955,34028,35543,28616,28622,33679,35557,33680,33757,33965,40974,40986,41206,40987,40997,33966,41001,33996,18889,20448,20451,20478,20484,20541,20480,20565,20440,32283,23873,32395,33616,33628,31962,31974,32096,32278,32376,23869,32398,41303,31959,32090,32052,39874,52362,52445,52637,52686,52718,52721,52832,52357,52850,52383,52422,39745,52851,39935,52428,38877,38894,51624,51665,51780,51782,51911,38540,38545,38586,38673,38701,38790,38811,38829,38833,38840,38843,38853,52242,52028,38694,51651,52027,52059,52090,52160,52322,52210,38633,38839,38549,38841,38921,38959,38575,38922,51589,51750,52348,51756,51594,51653,52324,39253,36996,39186,50791,50792,50865,50870,50951,50998,51002,51045,51102,51159,39288,37027,37029,37128,39302,37130,37140,39304,37205,51011,51153,51243,51266,39263,39346,51104,51208,37167,37174,39391,39489,51384,50625,50807,51352,50918,51322,50646,50809,50940,50779,40833,40842,40866,40867,40872,40860,40894,39966,40031,40132,40218,40235,40239,39945,40102,40135,40229,53284,53459,53542,53686,53888,54015,54035,54553,54619,40252,40261,40287,53300,53516,41321,41446,53925,54352,43742,52872,43763,43903,43884,43873,44233,54544,31636,44265,53129,53383,31711,44374,53714,45535,45685,48880,53613,38983,37431,48980,49143,48894,53206,49209,53245,39184,28575,28576,25126,25145,25163,25179,26195,26200,26342,28579,26354,28582,24113,25144,25178,26332,28593,25117,28571,23622,23642,23653,23024,22993,23121,46689,37306,37310,43765,43818,43845,37370,37426,44505,37251,43740,37335,44526,45686,37309,45657,37342,37381,45676,43995,44177,44235,44382,44488,30950,42729,48847,48932,48970,44103,31029,42649,43950,41317,41404,41493,30961,48857,44119,44248,42653,48753,30947,44251,42612,48756,44373,48899,41338,42670,41340,23597,22914,22918,22884,20210,20237,35693,35721,38981,35780,35803,35889,51504,35683,51405,51509,51511,51534,20294,35684,39020,49190,49244,49322,20304,35627,51448,51555,51560,35839,51469,51404,38976,39112,39113,39125,39128,39173,39123,35939,35943,35948,36979,31220,31309,24100,24106,31353,31358,40290,40385,40425,31552,31558,31664,24108,40453,40457,31374,31075,31110,31116,24101,31737,31368,24098,40397,40578,35591,35592,31854,24095,40258,40518,31531,40531,40538,40549,40596,35620,40748,31054,35585,31844,31542,31547,31551,31055,31544,31545,31548,40699,49117,49137,49168,49321,49450,49520,49534,49335,49533,49144,49441,20685,20709,39515,39566,39571,39615,39629,20573,20599,39678,39736,39707,39713,39715,39719,39727,39728,39730,39638,39648,37435,37485,37509,37515,37432,37453,37463,37514]
all_1104 = [
37225, 31270, 22916, 52018, 42648, 22958, 35517, 38949, 51030, 51028,
33642, 34107, 41576, 50770, 23619, 30697, 31576, 40326, 40323, 40179,
33484, 30044, 19175, 23122, 23152, 23480, 22532, 20225, 22689, 19750,
30882, 23670, 41563, 22835, 22825, 22870, 23123, 23155, 32282, 38815,
52020, 39743, 41085, 50762, 47739, 51261, 39421, 37006, 39178, 31041,
34156, 30106, 39943, 30697, 31345, 31001, 23619, 28769, 28683, 25157,
23129, 22118, 22945, 23337, 19750, 30036, 24112, 23366, 23500, 22836,
22260, 22425, 20773, 20447, 19560, 20637, 20373, 19392, 19343, 22256,
52470, 45678, 43800, 37047, 31113, 28635, 20777, 30882, 49243, 34107,
49210, 49573, 23871, 20844, 20764, 34376, 23523, 22553, 28564, 39700,
41104, 34107, 40984, 41008, 31949, 31935, 32065, 33505, 40579, 35659,
34065, 40127, 19990, 22040, 20925, 22550, 25124, 28637, 29956, 44402,
31546, 23595, 22271, 33642, 28635, 38973, 39961, 46730, 38702, 51354,
39424, 31947, 33723, 32150, 51359, 22612, 28770, 30537, 41248, 53076,
40540, 22147, 23145, 30190, 32059, 32061, 30851, 41468, 41002, 30835,
39514, 33702, 31945, 19855, 46730, 51131, 50827, 39043, 45569, 48942,
41467, 51591, 50637, 39216, 40125, 31566, 31061, 31312, 31831, 22088,
19750, 22834, 19555, 20566, 39193, 20474, 30573, 49546
]
offer_ids = all_1104
# offer_ids = [37225]
if sf.instance == 'lne' or skip_already_created_tours:
current_tours = set([
item.EOSId__c for item in sf.select(
"""SELECT EOSId__c FROM Tour__c WHERE EOSId__c <> NULL
AND IsHistoricalTour__c = False""")
])
tours_to_not_import = [
item for item in offer_ids if str(item) in current_tours
]
if len(tours_to_not_import) > 0:
print(
f'Skipping the following tours because they are already in Production: {tours_to_not_import}'
)
offer_ids = [
item for item in offer_ids if str(item) not in current_tours
]
assert len(offer_ids) > 0
eos_data = uk.query_tours(sql, offer_ids, is_onsale=False)
if len(eos_data.Tour__c) == 0: raise Exception('No Offers to migrate')
eos_data_with_remapped_eos_ids, remapped_eos_ids = uk.replace_duplicate_eos_ids(
eos_data)
eos_data_with_split_headliners, artist_ids_missing_in_rome_by_tour = uk.split_headliner_and_coheadliner(
sf, eos_data_with_remapped_eos_ids)
eos_data_with_missing_ids_removed, eos_ids_missing_in_rome, removed_eos_ids_by_tour = uk.remove_eos_ids_missing_in_rome(
sf, eos_data_with_split_headliners)
all_missing_eos_ids_by_tour = combine_missing_ids_dicts(
removed_eos_ids_by_tour, artist_ids_missing_in_rome_by_tour)
eos_ids_missing_in_rome.update(
itertools.chain.from_iterable(
artist_ids_missing_in_rome_by_tour.values()))
assert len(
eos_ids_missing_in_rome
) == 0 or sf.instance != 'lne', f'Some EOS Ids are missing: {eos_ids_missing_in_rome}\nThe following tours have missing data: {[int(s) for s in all_missing_eos_ids_by_tour]}'
eos_data_dfs = ObjDict({
obj: pd.DataFrame(data)
for obj, data in eos_data_with_missing_ids_removed.items()
})
eos_data_with_file_data = uk.merge_eos_data_with_file_data(eos_data_dfs,
is_onsale=False)
eos_data_computed = uk.add_computed_fields(sf, eos_data_with_file_data)
validations(eos_data_computed, eos_ids_missing_in_rome,
sf.credentials['sandbox'] == 'False')
threading.new(pdh.to_excel, eos_data_computed.data2,
'Migrate EOS Historical Tours.xlsx')
sf.bypass_prod_operation_approval()
rome_results = uk.upsert_eos_data_to_rome(
sf,
eos_data_computed.data2,
is_onsale=False,
delete_tours_first=delete_tours_first)
tour_results = itertools.chain.from_iterable(
[job.results for job in rome_results if job.object_name == 'Tour__c'])
event_results = itertools.chain.from_iterable(
[job.results for job in rome_results if job.object_name == 'Event__c'])
# Do NOT Update RomeIds for Offers in EOS, for historical Tours: uk.update_romeids_in_eos
# Do NOT set Tour Personnel for Historical Tours
if eos_ids_missing_in_rome:
missing_eos_id_info = uk.query_by_eos_ids(
sql, eos_ids_missing_in_rome,
['Name', 'FirstName', 'LastName', 'Email', 'EmailAddress'])
pdh.to_excel(missing_eos_id_info, 'Missing EOS Data.xlsx')
print(f'Missing EOS Ids in Rome: {eos_ids_missing_in_rome}')
# tourlegs = sf.select("""
# SELECT Id
# FROM TourLeg__c
# WHERE Tour__r.AppScope__c = 'UK'
# AND CreatedBy.Name = 'DataMigration User'
# AND Id NOT IN (SELECT TourLeg__c FROM Event__c)
# """)
# sf.delete(tourlegs)
return
def main(sessions, do_fix=False):
sessions = {
username: sf
for username, sf in sessions.items() if username in instances_to_run
}
return_list = []
return_string = ""
source_control_directory = r'/Users/daniel.hicks_1/Documents/Tower/liveNationSFDC UK/src/customMetadata/'
url = 'https://lneallaccess-my.sharepoint.com/:x:/g/personal/mike_wishner_lyv_livenation_com/EfcTIOXoayhCvDFq3t-kGEwB4aQc20-iALSiKqu-uLAxqw?e=0JJnGu&download=1'
r = requests.get(url, allow_redirects=True)
file_name = "./../Rome Touring Object Model.xlsx"
write_file = open(file_name, 'wb')
write_file.write(bytearray(r.content))
write_file.close()
xlsx_file = xlrd.open_workbook(file_name)
sheets = xlsx_file.sheet_names()
picklist_ref_sheet = xlsx_file.sheet_by_name(
"Picklist Ref") if "Picklist Ref" in sheets else None
file_records = []
headers = []
if picklist_ref_sheet is not None:
headers = picklist_ref_sheet.row_values(0)
for row_num in range(1, picklist_ref_sheet.nrows):
new_row = dict()
src_row = picklist_ref_sheet.row_values(row_num)
for col_num in range(0, len(headers)):
new_row[headers[col_num]] = src_row[col_num]
file_records.append(ObjDict(new_row))
for item in file_records:
item.Default__c = "true" if item.Default__c == 1 else "false"
item.AlwaysShown__c = "true" if item.AlwaysShown__c == 1 else "false"
item.AllowMultiple__c = "true" if item.AllowMultiple__c == 1 else "false"
item.GLCode__c = str(item.GLCode__c)[0:5]
# Run for each Salesforce instance
for username, sf in sessions.items():
instance = re.search("(.+\\.rome)(?:\\.(.+))?", username,
re.IGNORECASE).group(2)
instance = "lne" if instance == None else instance
picklist_option_desc = sf.get_object_description("PicklistOption__mdt")
rome_fields = [item["name"] for item in picklist_option_desc["fields"]]
rome_records = sf.select_records("""
SELECT {}
FROM PicklistOption__mdt
""".format(",".join(rome_fields)),
mode='simple')
for item in rome_records:
item.GLCode__c = str(item.GLCode__c)[0:5]
new_records = [
item for item in file_records
if item["Ignore?"] not in ['Yes', 'Delete'] and item.DeveloperName
not in [item2.DeveloperName
for item2 in rome_records] and item.DeveloperName != ""
]
deleted_records = [
item for item in rome_records if item.DeveloperName not in [
item2.DeveloperName for item2 in file_records
if item2["Ignore?"] != 'Delete'
]
]
updated_records = []
changes = []
for rome_record in rome_records:
matching_file_records = [
item for item in file_records
if item.DeveloperName == rome_record.DeveloperName
]
if len(matching_file_records) == 0:
continue
file_record = matching_file_records[0]
numChanges = 0
for field in rome_record:
if field not in file_record or file_record["Ignore?"] == "Yes":
continue
v1 = str(rome_record[field]).strip()
v2 = str(file_record[field]).strip()
if field in file_record and v1 != v2:
numChanges += 1
changes.append({
"DeveloperName": rome_record.DeveloperName,
"field": field,
"old": v1,
"new": v2
})
if numChanges == 1:
updated_records.append(file_record)
return_list.extend(deleted_records)
if len(return_list) > 0:
return_string = "Custom Metadata needs to be deleted in some sandboxes:\n"
return_string += "\n".join([
"https://lne{}.lightning.force.com/lightning/setup/CustomMetadata/page?address=%2F{}"
.format("" if instance == "lne" else "--" + instance, item.Id)
for item in deleted_records
])
return return_string
def get_cached_historical_data():
xlsx = pd.ExcelFile(f'{folder}Output/HistoricalFileData.xlsx')
return ObjDict({
'Tour__c': pd.read_excel(xlsx, 'Tour__c'),
'Event__c': pd.read_excel(xlsx, 'Event__c'),
})
def dfconcerts_get_file_data(top=None):
xlsx = pd.ExcelFile('/Users/daniel.hicks_1/Documents/Rome/Rome Downloads/UK Migration/DF/DFC Actuals 2019 w DH notes on GLAccount.xlsx')
df = pd.read_excel(xlsx, 'Data', header=2)
if top:
df = df.iloc[:top]
df = df[~df['Tour__c.EOSId__c'].isna()]
df['Event__c.EOSId__c'] = pdh.int_to_str(df['Event__c.EOSId__c'])
df['Tour__c.EOSId__c'] = pdh.int_to_str(df['Tour__c.EOSId__c'])
glaccounts = pd.read_excel(xlsx, 'GLAccounts').set_index('Column').to_dict('index')
# tours = df[[f for f in df.columns.values if str(f).startswith('Tour__c.')]]
# events = df[[f for f in df.columns.values if str(f).startswith('Event__c.')]]
leb_dfs = []
le_dfs = []
event_map = df.set_index('Event__c.EOSId__c').to_dict('index')
for col, val in glaccounts.items():
meta = ObjDict(val)
if meta.IsExpenseLEB:
leb_df = (
df[['Event__c.EOSId__c', col]]
.copy()
.rename(columns={
'Event__c.EOSId__c': 'Event__r.EOSId__c',
})
)
leb_df = leb_df[(~leb_df[col].isna()) & (leb_df[col] != 0)]
leb_df['LedgerEntry__r.SourceSystemId__c'] = leb_df["Event__r.EOSId__c"].apply(lambda x: f'DF-Historical-{x}-{meta.GLCode}')
leb_df['SourceSystemId__c'] = leb_df["Event__r.EOSId__c"].apply(lambda x: f'DF-Historical-{x}-{meta.GLCode}-{meta.Type__c}')
leb_df['GLCodePicklist__c'] = meta.GLCode
leb_df['TouringCategory__c'] = meta.TouringCategory__c
leb_df['Type__c'] = meta.Type__c
leb_df['Label__c'] = meta.Type__c
leb_df['OfferRateType__c'] = 'Flat'
leb_df['InHouseRateType__c'] = 'Flat'
leb_df['SettlementRateType__c'] = 'Flat'
leb_df['OfferRate__c'] = leb_df[col]
leb_df['InHouseRate__c'] = leb_df[col]
leb_df['Settlement__c'] = leb_df[col]
leb_df['SettlementOnly__c'] = False
leb_df['CurrencyIsoCode'] = 'GBP'
leb_df.drop(columns=col, inplace=True)
leb_dfs.append(leb_df)
if pd.to_numeric(df[col].dropna(), errors='coerce').notnull().all() == False:
raise Exception(f'Column {col} contains non-numeric data')
if True:
le_df = df[['Event__c.EOSId__c', col]].copy().rename(columns={
'Event__c.EOSId__c': 'Event__r.EOSId__c',
col: 'CurrentFlash__c'
})
le_df['SourceSystemId__c'] = le_df["Event__r.EOSId__c"].apply(lambda x: f'DF-Historical-{x}-{meta.GLCode}')
le_df['GLAccount__r.GLCode__c'] = meta.GLCode
le_df['CurrencyIsoCode'] = 'GBP'
le_dfs.append(le_df)
if pd.to_numeric(df[col].dropna(), errors='coerce').notnull().all() == False:
raise Exception(f'Column {col} contains non-numeric data')
leb_df = pd.concat(leb_dfs)
le_df = pd.concat(le_dfs)
le_df = (
le_df.groupby(by=['Event__r.EOSId__c', 'GLAccount__r.GLCode__c', 'SourceSystemId__c'], sort=False)
.sum()
.reset_index()
)
return ObjDict({
'All': df,
'LedgerEntryBreakout__c': leb_df,
'LedgerEntry__c': le_df,
'EventMap': event_map,
})
def main():
# CURRENT KNOWN ISSUES
# If an event is not marked as a roll date in EOS, but it has different onsales than other events, we are going to mark it a roll date
# We have no handling for the "ITB" OfferType. What to do?
# What to do with Offers which do not have a tour currency of GBP? These are broken. Out of scope?
# Changed fields: ArtistGuaranteeCurrencyId, LastUpdatedBy, LastUpdatedDateTime, OfferTypeId, OfficeId, OwnerId, PromoterProfitCurrencyId, UpdateId
# TODO: Map the Artist Guarantee amounts (and artist currency?)
# POTENTIAL ISSUE: Offer Artist Payments @ 1.00 Exch Rate, for artist currency other than GBP?
# TODO: Identify problematic tours which are on the list to migrate:
# Tours with EOS Ids missing in prod
# Tours with tour currency that is not GBP
# Tours with all past dates? Finance - need to identify if we will be treating past dates in an onsale tour as historical
# username = "******"
# username = '******'
# sql = SQL_Server_API(eos_prod_creds)
# sf = Salesforce_API('*****@*****.**')
sql = SQL_Server_API(eos_stage_creds)
sf = Salesforce_API('*****@*****.**')
keep_connection_alive(sf)
# recs = sf.select("SELECT Id, EOSLastModifiedDate__c, EOSIntegrationSyncDate__c FROM Tour__c WHERE EOSId__c <> NULL", return_type='dataframe')
# recs['EOSIntegrationSyncDate__c'] = recs['EOSLastModifiedDate__c']
# sf.update(recs)
# {'ArtistAgent-974', '2469', 'Artist-22297', 'Artist-22296', 'ArtistAgent-748', 'nan', 'ArtistAgency-2774', 'Artist-19279', '1299'}
delete_tours_first = True
skip_already_created_tours = False
# offer_ids = [54766]
# offer_ids = [44189]
# offer_ids = [52474]
# offer_ids = [54441, 57933, 57965, 58400, 58737, 58050, 57930, 57263, 53728, 55808]
# all_offer_ids_with_costings = [53981,57933,56534,56602,57906,58892,53556,57278,52720,58451,58490,55051,59093,58432,58803,55163,57965,58400,58737,54711,54297,52356,54441,57424,53846,57707,57276,55166,54006,58070,58406,57406,56517,58029,58073,58114,57273,57256,57337,53176,58802,54555,54980,57350,59121,53943,57996,56116,59140,58492,53909,52453,56561,56469,52474,58741,57923,58404,56813,53911,52477,58824,56527,58115,58050,52693,56321,55513,54326,58875,58121,57013,43748,57404,56537,58032,53890,57242,57843,54573,57788,58839,57894,57165,57992,53449,57006,57957,54134,53617,55138,44189,58560,57930,57469,57263,55224,56487,58842,51264,54069,55469,56957,58820,53816,53870,39597,56518,58821,58905,58739,58430,57279,52382,54888,57713,57030,58757,57403,55486,57847,58039,58505,56449,57773,56117,53683,58762,57086,58813,57943,57000,54932,56525,56998,53612,55222,54639,53722,53847,57037,57878,58691,53568,52697,57417,53944,54554,58770,57338,51663,54039,53728,52605,54206,58405,54195,53739,57972,55808,57291,54020,58606,58953,45648,57206,55741,57255,53602,53986,52427,54847,57709,56381,53623,57223,58086,58493,53812,55823,57011,55139,52419,53500,54443,52021,58804,53163,59151,56567,57269,51776,56173,55745,57024,53825,56440,54255,57167,56169,55405,52109,55466,58504,54208,56412,57959,58777,58423,58055,57186,54256,53182,54181,54633,53824,57418,57792,58794,58812,54634]
# offer_ids = all_offer_ids_with_costings
# safe_offer_ids_with_costings = [53981,57933,56534,56602,57906,58892,53556,57278,52720,58451,58490,55051,59093,58803,57965,58400,58737,54711,52356,54441,57424,53846,57276,58070,58406,57406,56517,58029,58073,58114,57273,57337,53176,58802,52453,56561,56469,52474,58741,57923,58404,56813,53911,52477,58824,58050,52693,56321,58121,53617,58560,57930,57469,57263,55224,56487,58842,54069,55469,56957,58820,53816,53870,56518,58905,58430,57279,52382,54888,57713,57030,58757,57403,55486,57847,58039,56449,56117,53683,58762,57086,57943,57000,54932,56525,53612,55222,54639,53722,53847,57037,57878,53568,52697,57417,53944,54554,58770,57338,51663,53728,52605,54206,57972,55808,57291,54020,58953,57206,55741,53986,54847,53825,56440,54255,56169,52109,55466,58504,54208,56412,57959,58777,58423,58055,57186,54256,54181,54633,53824,57418,58794,58812,54634]
# offer_ids = safe_offer_ids_with_costings
# offer_ids_with_no_costings = [52015,55146,55487,54925,54837,54766,54561,55451,54838,55458,54382,55134,55033,54743,55624,53591,55538,52725,54501,41498,53204,54815,57025,55588,57253,53328,53864,54926,57145,56889,56399,54243,55131,54662,56909,55164,55796,58088,58041,55161,58508,57354,57292,57863,56871,58455,58487,58417,58522,58523,59133,58998,57970,57852,56885,56869,58445,58447,59085,58868,55589,58774,59114,57886,53410,57887,58890,58823,58756,57939,56886,56874,57940,58510,58518,58519,59079,58450,57873,54249,53680,54313,57941,57911,58898,58771,58107,58858,54965,58833,58834,58017,52629,55168,58056,58798,56867,56959,57014,57034,53631,55313,54530,54307,57384,58427,54581,54593,54216,58092,57197,58089,59003,58091,55816,53530,56454,58809,58151,57527,57919,57907,57849,58801,58743,55599,53904,57739,58825,57869,54874,57741,58165,59127,57711,57039,52852,54975,56588,56436,54226,58429,58955,58395,58081,58064,58805,57925,53923,54386,57736,58537,54897,54200,58876,56106,54833,55440,53858,58191,58639,58909,57720,58118,58399,57968,58065,58456,58796,54203,55590,58453,58420,58443,56523,56425,55148,55123,54209,55477,57366,58002,58066,58896,56560,54350,57860,58507,56450,54199,56470,58817,59112,58431,54883,54611,56815,58144,58521,56509,55557,58440,58036,56862,56411,55402,59002,59158,59104,59142,56533,56566,57920,58410,55586,54198,59030,54207,57910,57271,58067,58108,57867,56400,58845,58763,58828,54080,57875,57876,53201,56520,57227,57184,54800,54221,57423,57201,57408,54522,55912,57230,57264,57457,57786,58690,58085,53815,54337,53203,58018,57331,59144,59083,57493,58793,56589,58035,54817,54161,54401,54622,58844,58530,57035,59152,56505,53202,55159,55584,57433,54769,54879,59162,58598,57884,57311,56519,56260,57730,56834,58123,58038,57169,57723,57877,58996,55119,53632,54759,57218,59080,59089,57989,55943,55806,56579,54540,58075,59134,58448,57297,57732,56906,58811,56424,57444,57127,58444,58795,53494,54556,57915,55735,58991,57848,54409,57528,59090,59081,58994,57936,53457,59136,59109,58877,58879,54830,53492,59153,57842,58037,58641,58826,58854,58259,59115,57855,58841,45568,59150,58740,54954,59157,55467,58850,57846,56456,55544,55457,57974,58764,52804,54803,58077,58019,57232,57219,52676,58843,58829,58558,53134,53538,53855,57021,56577,52962,57850,55760,58766,53687,55819,58792,57355,53881,52957,56865,53158,58765,58761,57987,54310,56866,55231,59145,57262,55117,54786,55797,55798,55800,57053,56868,55799,52902,56863,57328,57329,53826,51729,57185,55661,57892,53998,54818,58001,58779,57284,53892,58871,58884,58889,58886,58872,58900]
# offer_ids_no_costings_18 = [54895,54638,55461,54840,55160,52733,54375,54240,54964,56595,53616,54779,58479,58480,58495,58496,58473,58895,54499,58497,58435,59177,59233,58498,58499,58753,58117,59163,58754,56383,57675,58109,55515,57529,57893,57369,58442,58768,52584,56582,54642,54315,58744,57198,59201,59286,54887,59278,59269,59231,58989,59254,59274,55313,54664,54307,57018,57859,58776,57293,58993,53358,57772,57414,56310,56219,59222,58475,54826,57714,53021,55587,56864,57916,57162,54377,54359,59197,56256,57845,57865,57415,57243,56455,55944,59168,54517,57719,56526,56596,54604,59271,59261,59221,57928,56903,56557,57674,58008,58897,58723,55046,56538,55764,56604,54493,58952,58903,56468,57270,58667,58424,58456,57922,57826,56591,58005,54589,56430,55583,57089,57771,57856,57888,58083,56446,54636,57728,56524,59284,54230,57166,57984,55053,54967,58848,58506,58282,56114,56441,56431,58816,59132,58507,56416,54785,54620,53867,54282,55807,57800,57912,57742,57839,56442,54641,54277,56594,58469,57981,59225,59226,59253,57333,56218,54426,53482,54909,56531,56968,57150,57726,56162,54541,57426,57917,57287,58501,57737,56504,56405,59165,52462,58663,56216,58470,58840,57298,56513,57471,59227,59161,59148,58525,58126,59199,59203,54800,54212,57056,57216,57201,58031,58869,59189,56414,57782,57182,59264,59171,55598,58742,58491,57207,57882,57990,54388,54911,56444,56472,59234,56574,54254,57718,59220,59232,56828,58822,53692,56593,56420,56206,59244,59257,59267,59194,59159,58095,58142,58425,54373,56543,59172,57299,55776,56522,57971,58967,58902,59178,57991,57724,56217,54876,55047,54612,58116,58411,58167,58520,59170,59241,59169,59218,57456,57260,54371,56254,56255,59242,59146,57978,57356,58815,57787,55349,55911,59182,57163,57073,58051,58472,58030,57412,57191,59116,58082,58090,57840,54423,57738,59166,57999,56541,56178,59117,59249,58494,53496,52982,53312,59175,57187,57889,56423,54719,58068,55961,58441,57442,56506,58152,57500,59237,56996,57202,59111,59243,57960,54765,58867,53177,57007,58020,59282,53519,59108,54717,55082,58760,58797,54204,57962,59262,57909,54801,54867,55453,57862,59107,59235,54592,54777,59193,59245,55198,58028,59135,56486,54609,54809,59216,58166,59185,58052,58800,59105,49139,56395,57927,59200,58788,58345,57857,53735,59276,55008,54570,54799,56170,56528,57967,58883,56438,58906,59275,53466,55763,56516,58736,57979,58143,59122,59154,57286,55552,54534,58990,58827,58449,58113,58488,57793,59123,49521,58418,58511,58799,56153,56422,59103,57866,59095,53715,55347,57272,57161,57986,56421,56094,58080,53733,59266,56572,59155,52083,59259,58759,54335,52986,57851,53286,54211,57790,53433,53737,59260,59240,53167,53743,55414,56510,52459,56558,59209,52896,56445,58076,58806,56121,52884,53823,58901,54961,59191,59187,57020,54848,54413,59213,59212,59224,59211,59214]
# offer_ids = offer_ids_no_costings_18
# for_prod = [58504,58794,56469,57959,58842,57713,54441,54206,57186,55741,58050,53728,56449,53617,54069,55466,56169,56517,57418,54639,58802,53816,53870,58905,57206,58820,58406,58404,57000,53568,57037,53176,57273,53846,56602,57291,57847,57923,55486,58073,58812,52453,59093,53722,57906,58741,53847,54847,57030,56534,58953,58560,55469,53986,52605,56957,53911,57424,58029,57263,52356,57933,52474,54208,53825,53612,57086,58423,52382,58451,55222,58892,54554]
# offer_ids = for_prod[0:5]
# offer_ids = for_prod
# dawn_list = [52382,53176,53556,53612,53825,53846,56412,57206,57273,57276,57278,58406,58423,58777]
# dawn_list_17 = [55138,54039,58821,57269,58430,57037,57279,57792,58839,58820,58406,57206,45648,58050,58953,53825,53612,52382,58423,52021,57086,56567,53846,56412,57403,53500,59140,57273,53176,57242,57276,58777,57923,56813,53556,57278,52477,52697,53182,55486,57957,57878,58073,57256,57337,57338,53683,56487]
# dawn_list_18 = [53568,53722,53722,53824,53824,52419,54633,54181,53449,55139,54573,57000,54932,57424,54441,56537,56998,56525,56537,56998,54441,57424,57424,56169,53890,54639,57709,54555,56169,52109,56169,57843,53943,52109,54208,54980,57709,54888,54888,55222,57959,55222,56381,57713,57713,57223,57350,57350,57223,57165,57350,57011,57992,59121,58070,59121,57186,58606,57186,56116,58493,54256,57011,57418,54443,56517,56517,57417,58794,58794,53944,53944,56518,58812,54554,54554,58770,57847,57847,58812,58039,58842,58842,54006,54195,58804,58804,53163,53163,59151,56117,54634,58762,58762,58405,53909,56440,56440,51663,53847,57404,57404,54255,56173,57788,58504,56173,54255,57788,58504,57707,57707,57469,57030,55166,53617,57030,58055,57030,57894,57406,57006,57006,58691,58691,58029,58114,54206,54206,53870,54020,58802,58802,53816,54134,52427]
# dawn_list_19_uat = [58032,58560,57996,54069,58824,58492,58505,57263,55405,44189,53623]
# dawn_list_19 = [52720,58905,57291,57291,58739,57965,53739,58400,57930,58737,56561,56561,55469,56957,56957,56602,56534,56602,57906,57906,52109,52109,56469,52474,54354,58757,58741,58741,58757,55224,58404,58892,58892,53911,57894,53911,56527,54297,54711,58451,58490,59093,39597,57013,52693,56321,55051,57013,58432,58432,53986,53986,58115,58813,58813,58813,58803,54326,58875,58121,58121,55163]
# dawn_list_20 = [58032,58560,57996,54069,58824,58492,58505,57263,55405,44189,53623]
# lewis_list_01 = [59196,59198,59094,59291,59251,59238,59268,59246,59270,58449]
# dawn_list_02 = [55454,55014,59184,59286,52629,59379,59205,58093,59368,59328,59358,58032,57167,59326,59332,59256,59333,59287,59384,59281,59366,59315,57127,59292,53457,59295,59329,58492,59228,59371,53158,55117,59272,52034,54510,54508]
# dawn_red_list_02 = [54847,51776,58086,59268,59246,53602,55513]
# all_remaining_02 = [54625,52936,52019,53423,54341,54605,54845,54760,55157,55137,55357,54631,55353,54590,55616,55124,54889,54107,54381,53874,58087,52592,52922,59286,59294,56539,59278,59231,59274,56814,53875,52874,57368,59205,59379,57585,55352,59222,56568,57028,58810,57254,53757,56453,52825,57002,57295,59358,59328,58428,57958,54846,58421,51541,57870,56221,55147,57400,53446,54000,59321,53619,58610,57727,57108,56564,57982,57938,56447,59293,53866,59219,57275,56583,55165,57881,56172,59223,57179,57708,59323,58419,54376,59196,54623,59326,59336,52819,57734,53357,59332,57282,55464,59318,59291,59322,59251,59385,55455,59283,59256,58885,59333,59287,57334,58561,58855,57783,56545,57041,59303,59384,57985,55350,59281,59364,55783,53227,58069,55528,59366,58584,53900,55189,59380,57175,57858,54576,58458,58426,57983,59315,59252,58159,54802,54385,58457,56140,57087,59327,57178,55759,52478,59279,59280,57924,53729,55404,57729,59306,57854,59390,57785,56275,59305,59143,57386,59292,59388,58838,59324,54797,59361,59268,57966,59190,59250,58836,56448,57419,57716,56508,59317,59393,59295,59330,58481,59314,59300,53910,57770,59110,54822,59329,58927,59391,55140,58071,53605,59082,59285,59215,59087,59141,57721,57733,58778,55474,54721,59316,59228,59360,59290,59248,59246,57861,54197,58856,58156,52376,53332,53614,57413,59311,59230,58482,54038,59091,58559,53157,59371,57769,55480,54446,54003,54910,59386,59265,52695,57045,59301,59374,58808,59369,58010,52042,58141,57791,58446,59398,59270,59359,58775,55532,58531,56515,45559,58738,59195,59247,59363,52985,58503,53882,54149,58852,58468,57170,59375,52726,59272,54868,56443,53744,54311,54899,54900,54898,54903,54904,55143,59149,59387,57280,59373,54338,57407,59210]
# club_shows_03 = [55160,59286,59294,59231,59278,59274,55352,59222,59358,59328,58897,59321,59293,59326,59336,59291,59318,59283,59256,59333,59094,59303,59384,59281,59366,57787,59315,59327,55513,59279,59305,54717,59324,59317,57857,59316,59360,59311,59374,59398,59359,59270,59363,58806,59387,59373,52356,58032,54847,59322,59287,59292,59295,59332,59385,59306]
# lewis_list_03 = [51541,55466,55823,53812,59190,59250,59215,59141,59290,59369,55143,52453]
# all_remaining_03 = [59231,59278,59274,59379,59205,59222,59219,59223,59323,59196,59251,59364,59380,59252,59280,59390,59388,59361,59268,59190,59250,59393,59330,59300,59329,59391,59285,59228,59290,59248,59246,59230,59371,59265,59369,59195,59247,59375,59272,59210]
# lewis_list_07 = [51541,55466,55823,53812,59190,59215,59290,55143,52453]
# dawn_list_09 = [55454,55014,59198,52629,58093,53812,51541,57400,55466,55823,52034,51776,43748,53728,55745,57127,55741,53457,58492,59141,53602,57773,54510,53158,56449,55117,51264,52605,55143,54508,59272,59371,59228,59379,59205,59190,59329,59290]
df_list_09 = [
55353, 55160, 54107, 54381, 53874, 52922, 59231, 59278, 59274, 53875,
52874, 57368, 57585, 59222, 56568, 57028, 58810, 57254, 53757, 52825,
57002, 57295, 58897, 57958, 54846, 58421, 56221, 53446, 54000, 53619,
57727, 57108, 56564, 57938, 56447, 53866, 59219, 57275, 55165, 57881,
56172, 59223, 57179, 57708, 59323, 58419, 54376, 54623, 57734, 53357,
57282, 55464, 55455, 58885, 57334, 58561, 58855, 57783, 56545, 57041,
55350, 59364, 55783, 53227, 58069, 55528, 58584, 57787, 53900, 59380,
57218, 57175, 57858, 54576, 58458, 58426, 57983, 59252, 58159, 54802,
58457, 56140, 53729, 57087, 57178, 55759, 52478, 59280, 57924, 55404,
53729, 57729, 59188, 57854, 59390, 57785, 56275, 59388, 54717, 58838,
54797, 59361, 57966, 58836, 56448, 57419, 59393, 56508, 59330, 58481,
57857, 59300, 53910, 57770, 59110, 54822, 58927, 59391, 55140, 58071,
53605, 59082, 59285, 59087, 57721, 57733, 58778, 55474, 54721, 59248,
57861, 54197, 58856, 58156, 52376, 53614, 53332, 57413, 59230, 58482,
54038, 59091, 58559, 53157, 57769, 55480, 54446, 54003, 54910, 59265,
57045, 52695, 59301, 58808, 58010, 52042, 58141, 57791, 58446, 58775,
55532, 58531, 56515, 45559, 58738, 59247, 52985, 58503, 53882, 58806,
54149, 58852, 58468, 57170, 59375, 52726, 54868, 56443, 53744, 54311,
54899, 54900, 54898, 54903, 54904, 59149, 57280, 54338, 57407
]
# all_remaining_09 = [55160,59231,59278,59274,59222,53812,58897,57400,55466,55823,52034,59196,59251,43748,57787,53728,55745,55741,43748,55741,53812,55745,54717,59268,59250,57857,59141,59246,53602,53602,57773,57773,59369,59195,56449,58806,51264,52605,59479,55143,51264,52453,59210,57255,57255,57024,57024]
# all_remaining_10 = [55160,59274,59592,59222,53812,58897,57400,55466,55823,52034,59196,59251,43748,57787,53728,55745,55741,43748,55741,55745,53812,54717,59268,59250,57857,59141,59246,53602,53602,57773,57773,59369,59195,56449,58806,59523,59549,59589,59590,51264,52605,59479,55143,51264,52453,59210,57255,57255,57024,57024]
# all_remaining_13 = [55160,59222,53812,58897,57400,55466,55823,52034,59196,59251,43748,57787,53728,55745,55741,43748,55741,55745,53812,54717,59268,59250,57857,59141,59246,53602,53602,57773,57773,59369,59195,56449,58806,59523,59549,59589,59590,59592,51264,52605,59479,55143,51264,52453,59210,57255,57255,57024,57024]
# dawn_list_13 = [53812,55466,55823,52034,55745,55741,51264,52605,55143,52453,57255,57024] # TO DO
dawn_list_14 = [59196, 59251, 59268, 59250, 59246, 59369]
all_nov_19 = [
60219, 60203, 60183, 60088, 59772, 59604, 59592, 59590, 59589, 59549,
59523, 59386, 59314, 59210, 59195, 59143, 59141, 58806, 57857, 57787,
57773, 57773, 57716, 56449, 55189, 55160, 54717, 53602, 53602
]
offer_ids = [56449, 53602]
# sf.delete_tours("""SELECT Id FROM Tour__c WHERE EOSId__c IN @offer_ids""")
# new_tours = pd.DataFrame(sf.select("SELECT EOSId__c, LastModifiedDate FROM Tour__c WHERE EOSId__c IN @offer_ids", mode='simple'))
# tours_to_update = new_tours.assign(
# EOSIntegrationSyncDate__c = lambda df: df['LastModifiedDate'],
# EOSLastModifiedDate__c = lambda df: df['LastModifiedDate'],
# IsHistoricalTour__c = False,
# ).drop(columns=['LastModifiedDate'])
# sf.upsert('Tour__c', tours_to_update, 'EOSId__c', mode='simple')
# tours_with_leb_0_issue = [54847]
tours_with_null_venue_event = [] # [57894]
tours_with_missing_booker_eos_id = [
] # [54382, 53158, 52629, 53457, 52109]
offer_ids = [
item for item in offer_ids
if item not in tours_with_missing_booker_eos_id
and item not in tours_with_null_venue_event
]
# 2469 Josh Casey DF ignore
# 1299 Chris Loomes
missing_1299_booker = [
54717, 57787, 57857, 58473, 58480, 58496, 58499, 58753, 58754, 58806,
58897, 59222, 59231, 59274, 59278
]
missing_2469_booker = [55160]
missing_974_artist_agent = [] # [59286]
offer_ids = [
item for item in offer_ids
if item not in missing_1299_booker and item not in missing_2469_booker
and item not in missing_974_artist_agent
]
# temporary_tours_with_missing_eos_ids = [54307, 55313, 58456, 54382, 58507, 54243, 55588, 54800, 57127, 53158, 52629, 53457, 55117, 57218, 57201] + [59286, 58499, 58897, 58496, 58480, 58473, 57787, 57857, 58753, 58754, 59278, 59222, 59231, 59274, 58806, 55160, 54717, 59282, 59235]
# offer_ids = [item for item in offer_ids if item not in temporary_tours_with_missing_eos_ids]
# offer_ids = [59111,54840]
# # To re-migrate all tours
# offer_ids = [int(item.EOSId__c) for item in sf.select("""
# SELECT Id, EOSId__c
# FROM Tour__c
# WHERE IsHistoricalTour__c = False
# AND EOSId__c != NULL
# AND SourceSystemId__c LIKE '%Offer%'
# """)]
# sample_eos_offers = sql.query("""
# SELECT TOP 1 o.Id
# FROM Offer o
# LEFT JOIN Currency ppc
# ON ppc.Id = o.PromoterProfitCurrencyId
# WHERE o.Id IN (
# SELECT DISTINCT OfferId
# FROM vwEOSShow
# WHERE (ShowDate>=GetDate() OR PostponedDateTBC=1)
# AND CountryId = 1
# AND OfferStatusName IN ('Confirmed','On Sale','Settled','Draft')
# )
# AND o.RomeId IS NULL
# AND o.ArtistGuaranteeAmount > 0
# AND o.CopromoterId IS NOT NULL
# AND ppc.IsoCode = 'GBP'
# """)
# offer_ids = [item['Id'] for item in sample_eos_offers]
if sf.instance == 'lne' or skip_already_created_tours:
current_tours = set([
item.EOSId__c for item in sf.select(
"""SELECT EOSId__c FROM Tour__c WHERE EOSId__c <> NULL
AND IsHistoricalTour__c = False""")
])
tours_to_not_import = [
item for item in offer_ids if str(item) in current_tours
]
if len(tours_to_not_import) > 0:
print(
f'Skipping the following tours because they are already in Production: {tours_to_not_import}'
)
offer_ids = [
item for item in offer_ids if str(item) not in current_tours
]
# offer_ids = offer_ids[:50]
# offer_ids = [53943]
assert len(offer_ids) > 0
eos_data = uk.query_tours(sql, offer_ids, is_onsale=True)
pdh.to_excel(eos_data,
'Migrate EOS On-Sale Tours - Raw EOS Query Data.xlsx')
if len(eos_data.Tour__c) == 0: raise Exception('No Offers to migrate')
eos_data_with_remapped_eos_ids, remapped_eos_ids = uk.replace_duplicate_eos_ids(
eos_data)
eos_data_with_split_headliners, artist_ids_missing_in_rome_by_tour = uk.split_headliner_and_coheadliner(
sf, eos_data_with_remapped_eos_ids)
eos_data_with_missing_ids_removed, eos_ids_missing_in_rome, removed_eos_ids_by_tour = uk.remove_eos_ids_missing_in_rome(
sf, eos_data_with_split_headliners)
all_missing_eos_ids_by_tour = combine_missing_ids_dicts(
removed_eos_ids_by_tour, artist_ids_missing_in_rome_by_tour)
eos_ids_missing_in_rome.update(
itertools.chain.from_iterable(
artist_ids_missing_in_rome_by_tour.values()))
# print([int(k) for k,v in all_missing_eos_ids_by_tour.items() if '1299' in v])
assert len(
eos_ids_missing_in_rome
) == 0 or sf.instance != 'lne', f'Some EOS Ids are missing: {eos_ids_missing_in_rome}\nThe following tours have missing data: {[int(s) for s in all_missing_eos_ids_by_tour]}'
eos_data_dfs = ObjDict({
obj: pd.DataFrame(data)
for obj, data in eos_data_with_missing_ids_removed.items()
})
eos_data_with_file_data = uk.merge_eos_data_with_file_data(eos_data_dfs,
is_onsale=True)
eos_data_computed = uk.add_computed_fields(sf, eos_data_with_file_data)
validations(eos_data_computed, eos_ids_missing_in_rome,
sf.credentials['sandbox'] == 'False')
threading.new(pdh.to_excel, eos_data_computed.data2,
'Migrate EOS On-Sale Tours.xlsx')
# monitor_eos = uk.monitor_eos_tours(sql, [item['EOSId__c'] for item in eos_data.Tour__c])
# monitor_rome = uk.monitor_rome_tours(sf, [item['EOSId__c'] for item in eos_data.Tour__c])
# sf.dynamic_upsert(eos_data_computed.data2, mode='dynamic')
sf.bypass_prod_operation_approval()
rome_results = uk.upsert_eos_data_to_rome(
sf,
eos_data_computed.data2,
is_onsale=True,
delete_tours_first=delete_tours_first)
tour_results = itertools.chain.from_iterable(
[job.results for job in rome_results if job.object_name == 'Tour__c'])
event_results = itertools.chain.from_iterable(
[job.results for job in rome_results if job.object_name == 'Event__c'])
uk.update_romeids_in_eos(sql, tour_results, event_results)
if sf.instance == 'lne':
uk.add_default_tour_personnel(
sf, [item['EOSId__c'] for item in tour_results])
failed_deal_jobs = [
job.errors for job in rome_results
if job.object_name == 'Deal__c' and len(job.errors) > 0
]
if eos_ids_missing_in_rome:
missing_eos_id_info = uk.query_by_eos_ids(
sql, eos_ids_missing_in_rome,
['Name', 'FirstName', 'LastName', 'Email', 'EmailAddress'])
pdh.to_excel(missing_eos_id_info, 'Missing EOS Data.xlsx')
print(f'Missing EOS Ids in Rome: {eos_ids_missing_in_rome}')
# print(pd.concat(missing_eos_id_info.values()).to_string())
# tourlegs = sf.select("""
# SELECT Id
# FROM TourLeg__c
# WHERE Tour__r.AppScope__c = 'UK'
# AND CreatedBy.Name = 'DataMigration User'
# AND Id NOT IN (SELECT TourLeg__c FROM Event__c)
# """)
# sf.delete(tourlegs)
# OfferID Currency Exchange Rate
# 54297 USD 0.72314944
# 55166 USD 0.71428572
# 54326 USD 0.72825256
# 58875 USD 0.74074073
# 54134 USD 0.82644628
# 39597 USD 0.77363454
# 58821 USD 0.70611495
# 56998 USD 1
# 45648 USD 1
# 55139 USD 1
# print(f'EOS Monitor in progress')
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
