def main():
ts = sf.select("""
SELECT Id, Event__r.TourLeg__r.Tour__r.EOSId__c, Event__r.EOSId__c, Type__c, LastModifiedDate, LastModifiedBy.Name
FROM TicketScale__c
WHERE StageType__c IN ('Ticket Band','Ticket Hold','Ticket Upgrade')
AND IsTouringApp__c = True
AND Event__r.TourLeg__r.Tour__r.AppScope__c = 'UK'
AND LastModifiedDate = TODAY
ORDER BY LastModifiedDate DESC
""",
return_type='flat')
event_ids = [t['Event__r.EOSId__c'] for t in ts]
all_events = sf.select("""
SELECT Id, EOSId__c, TourLeg__r.Tour__r.EOSId__c
FROM Event__c
WHERE EOSId__c IN @event_ids
""",
return_type='flat')
last = None
groups = []
curr_group = []
groups.append(curr_group)
for t in ts:
s = t.LastModifiedDate[11:19]
time = int(s[0:2]) * 60 * 60 + int(s[3:5]) * 60 + int(s[6:8])
t.time = time
if last is None or last.time - 60 <= time:
curr_group.append(t)
else:
curr_group = []
curr_group.append(t)
groups.append(curr_group)
t.group_index = len(groups) - 1
last = t
out = []
for i, group in enumerate(groups):
events = {ts['Event__r.EOSId__c'] for ts in group}
tours = {ts['Event__r.TourLeg__r.Tour__r.EOSId__c'] for ts in group}
all_possible_events = {
e.EOSId__c
for e in all_events if e['TourLeg__r.Tour__r.EOSId__c'] in tours
}
missing_events = {e for e in all_possible_events if e not in events}
num_events = len({ts['Event__r.EOSId__c'] for ts in group})
num_tours = len(
{ts['Event__r.TourLeg__r.Tour__r.EOSId__c']
for ts in group})
out.append({
'index': i,
'events': num_events,
'tours': num_tours,
'missing_events': missing_events
})
pdh.to_html(ts, 'TS Data')
pdh.to_excel([ts, out], 'TS Data')
return
def main():
gb_settings = source_sf.select("""
SELECT Id, CurrencyIsoCode, GBLite__Custom_CSS_File__c, GBLite__Custom_JS_File__c, GBLite__Default_Definition__c, GBLite__Definition__c, GBLite__Grid_Name__c
FROM GBLite__GridBuddySettings__c""",
return_type='dataframe')
pdh.to_html(gb_settings, 'test html')
return
def run_event_2(eventdata):
event = eventdata.Event__c[0]
expenses = [
item for item in eventdata.LedgerEntryBreakout__c
if item.ParentRecordTypeName__c == 'Expenses'
]
ancillaries = [
item for item in eventdata.LedgerEntryBreakout__c
if item.ParentRecordTypeName__c != 'Expenses'
and item.SharedInNetPot__c == False
]
deals = eventdata.Deal__c
combined_copro_loss_percent = get_combined_copromoter_percent(
eventdata.Deal__c, 'EventPL__c')
combined_copro_expadj_percent = get_combined_copromoter_percent(
eventdata.Deal__c, 'ExpenseAdjustment__c')
combined_copro_ancillary_percent = get_combined_copromoter_percent(
eventdata.Deal__c, 'AncillaryRevenue__c')
avg_gross_ticket_price = event.GrossPotential__c / event.SelloutPaidTickets__c
avg_adjgross_ticket_price = event.AdjustedGrossPotential__c / event.SelloutPaidTickets__c
avg_netgross_ticket_price = event.NetGrossPotential__c / event.SelloutPaidTickets__c
s1 = get_steps_from_expenses(event, expenses, avg_gross_ticket_price,
avg_adjgross_ticket_price,
avg_netgross_ticket_price)
s2 = get_steps_from_venuedeal(event, expenses, avg_netgross_ticket_price)
s3 = get_steps_from_artist_deals(event, deals)
s4 = get_steps_from_ancillaries(event, ancillaries)
s5 = get_steps_from_expense_adjustments(event, expenses)
artist_breakeven_steps = combine_steps(s1 + s2 + s3)
internal_breakeven_steps = combine_steps(s1 + s2 + s3 + s4 + s5)
artist_result = calculate_breakeven(artist_breakeven_steps,
avg_netgross_ticket_price,
combined_copro_loss_percent,
combined_copro_expadj_percent,
combined_copro_ancillary_percent)
internal_result = calculate_breakeven(internal_breakeven_steps,
avg_netgross_ticket_price,
combined_copro_loss_percent,
combined_copro_expadj_percent,
combined_copro_ancillary_percent)
result = {
'Id': event['Id'],
'V2 Actual Breakeven':
f'Artist: {artist_result["breakeven_tickets"]:.0f}\n LN/Internal: {internal_result["breakeven_tickets"]:.0f}',
'original_steps': s1 + s2 + s3 + s4,
'artist_steps': internal_breakeven_steps,
'internal_steps': internal_breakeven_steps,
}
df = pd.DataFrame(internal_breakeven_steps)
del df['source_records']
pdh.to_html(df, f'{event.Id} BreakevenSteps V2')
return result
def main():
pass
events = sf.select("""
SELECT Id, Office__r.Name, Venue__r.Name, EventName__c, Status__c, Venue__r.EvenkoAACodePrefix__c, ShowCount__c, EventFirstDate__c, EvenkoAACode__c, InitialConfirmedDate__c
FROM Event__c
WHERE EvenkoAACode__c = NULL
AND Venue__r.EvenkoAACodePrefix__c != NULL
AND Status__c IN ('Confirmed','Flash Started','Flash Completed')
AND Division__c = 'Evenko'
""", return_type='flat')
for item in events:
s = item['Venue__r.EvenkoAACodePrefix__c'] + item['EventFirstDate__c'][2:].replace('-','')
item['EvenkoAACode__c'] = s
pdh.to_html(pd.DataFrame(events), 'New Evenko Codes')
pdh.to_excel(pd.DataFrame(events), 'New Evenko Codes')
with sf.bypass_settings():
sf.update(events)
return
def main():
sf = Salesforce_API('*****@*****.**')
# sf = Salesforce_API('*****@*****.**')
sf.default_mode = 'simple'
query = """
SELECT Id
, TourLeg__r.Tour__c AS TourId
, TourLeg__r.Tour__r.TourTitle__c AS Tour
, TourLeg__r.Tour__r.TourBooker__r.Name AS [Tour Booker]
, Venue__r.Name AS Venue
, EventFirstDate__c AS [Event Date]
, TO_INT(SelloutPaidTickets__c) AS [Sellout Tickets]
FROM Event__c
WHERE TourLeg__r.Tour__r.WorkingCopy__c = NULL
-- AND TourLeg__r.Tour__r.AppScope__c = 'UK'
-- AND VenueDealType__c != NULL
-- AND TourLeg__r.Tour__r.TourBooker__r.Name = 'Ben Hatton'
AND BusinessPlanOption__c = False
AND TourLeg__r.Tour__c = 'a1s79000000Gn44AAC'
ORDER BY TourLeg__r.Tour__c, TourLeg__r.Order__c ASC, TourLegOrder__c ASC
"""
# WHERE TourLeg__r.Tour__c = 'a1s01000000EuoUAAS'
# -- AND TourLegOrder__c = 9
# """SELECT Id FROM Event__c WHERE TourLeg__r.Tour__c = 'a1s01000000F5XGAA0' AND BusinessPlanOption__c = False"""
# """SELECT Id FROM Event__c
# WHERE VenueDealType__c != NULL
# AND TourLeg__r.Tour__r.AppScope__c = 'UK'
# AND Id IN (SELECT Event__c FROM LedgerEntryBreakout__c)
# LIMIT 10"""
events = sf.fselect(query)
assert len(events) > 0, 'need events'
eventdata = sf.select_events(
events,
select_fields={'Event__c': ['*.writable']},
objects={'Event__c', 'Deal__c', 'LedgerEntryBreakout__c'})
results = []
results2 = []
for evt in eventdata.Event__c:
chunk = ObjDict({
k: [
item for item in v if item['Id'] == evt['Id']
or item.get('Event__c', None) == evt['Id']
]
for k, v in eventdata.items()
})
results.append(run_event(chunk))
results2.append(run_event_2(chunk))
df = pd.DataFrame(events)
df = df.merge(pd.DataFrame(results2), on='Id')
df = df.merge(pd.DataFrame(results), on='Id')
df['url'] = 'https://lne.lightning.force.com/lightning/r/Tour__c/' + df[
'TourId'] + '/view'
del df['Id']
del df['TourId']
# df['url'] = '<a href="https://lne.lightning.force.com/lightning/r/Tour__c/' + df['Id'] + '/view">' + df['Tour'] + '</a>'
debug_details = df.to_dict('records')
df.drop(columns=['original_steps', 'artist_steps', 'internal_steps'],
inplace=True)
pdh.to_html(df, 'Breakeven Analysis', render_newlines=True)
print('HTML printed')
pdh.to_excel(df, 'Breakeven Analysis', word_wrap=True)
return
def run_event(eventdata):
event = eventdata.Event__c[0]
expenses = [
item for item in eventdata.LedgerEntryBreakout__c
if item.ParentRecordTypeName__c == 'Expenses'
]
ancillaries = [
item for item in eventdata.LedgerEntryBreakout__c
if item.ParentRecordTypeName__c != 'Expenses'
and item.SharedInNetPot__c == False
]
combined_copro_loss_percent = get_combined_copromoter_percent(
eventdata.Deal__c, 'EventPL__c')
combined_copro_expadj_percent = get_combined_copromoter_percent(
eventdata.Deal__c, 'ExpenseAdjustment__c')
combined_copro_ancillary_percent = get_combined_copromoter_percent(
eventdata.Deal__c, 'AncillaryRevenue__c')
total_flat_expense_adjustments = _sum(
[exp.ExpenseAdjustment__c for exp in expenses])
total_flat_ancillaries = _sum(
[an.BaseAmount__c for an in ancillaries if an.RateType__c == 'Flat'])
total_ancillaries_per_paid = _sum([
an.BaseAmount__c for an in ancillaries if an.RateType__c == 'Per Cap'
])
internal_flat_expense_adjustments = total_flat_expense_adjustments * (
1 - combined_copro_expadj_percent)
internal_flat_ancillaries = total_flat_ancillaries * (
1 - combined_copro_ancillary_percent)
internal_ancillaries_per_paid = total_ancillaries_per_paid * (
1 - combined_copro_ancillary_percent)
internal_copro_loss_adjustment = (
(internal_flat_expense_adjustments + internal_flat_ancillaries) /
(1 - combined_copro_loss_percent)) - (
internal_flat_expense_adjustments + internal_flat_ancillaries)
artist_breakeven = _sum([
_sum([
_sum(d.GuaranteeAmount__c, d.ArtistSuppliedSL__c) *
d.DealExchangeRate__c for d in eventdata.Deal__c
if d.RecordType.Name == 'Artist'
]),
_sum([
l.OfferMin__c for l in expenses
if l.ParentRecordTypeName__c == 'Expenses'
and l.OfferRateType__c not in {'Flat', 'Per Truck'}
]),
_sum([
l.OfferRate__c for l in expenses
if l.ParentRecordTypeName__c == 'Expenses'
if l.OfferRateType__c == 'Flat'
]),
_sum([
l.OfferRate__c * event.NumberofTrucks__c for l in expenses
if l.ParentRecordTypeName__c == 'Expenses'
if l.OfferRateType__c == 'Per Truck'
]),
])
internal_breakeven = (artist_breakeven -
internal_flat_expense_adjustments -
internal_flat_ancillaries -
internal_copro_loss_adjustment)
if event.SelloutPaidTickets__c in {
0, None
} or event.ProjectedNetGross__c in {0, None}:
return {}
artist_breakeven_tickets, steps1 = get_breakeven_tickets(
eventdata, artist_breakeven, 0, 0)
internal_breakeven_tickets, steps2 = get_breakeven_tickets(
eventdata, internal_breakeven, internal_ancillaries_per_paid,
combined_copro_loss_percent)
# print(f'\nArtist Breakeven: {math.ceil(artist_breakeven_tickets)} Live Nation Breakeven: {math.ceil(internal_breakeven_tickets)}')
a1, a2 = get_breakeven_tickets_approximation1(eventdata, locals())
b1, b2 = get_breakeven_tickets_approximation2(eventdata, locals())
c1, c2 = get_breakeven_tickets_approximation3(eventdata, locals())
# print(f'Approximation 1: Artist {a1}, Internal {a2}')
pdh.to_html(steps2, f'{event.Id} BreakevenSteps V1')
result = {
'Id':
event['Id'],
'Actual Breakeven':
f'Artist: {artist_breakeven_tickets:.0f}\n LN/Internal: {internal_breakeven_tickets:.0f}',
'Approx. 1 Using Forecasted Exp.':
f'Artist: {a1:.0f}\n LN/Internal: {a2:.0f}',
'Approx. 1 Diff':
f'{a1-artist_breakeven_tickets:.0f} tickets\n{a2-internal_breakeven_tickets:.0f} tickets',
'Approx. 1 Diff %':
f'{(a1-artist_breakeven_tickets)/event.SelloutPaidTickets__c*100:.1f}%\n{(a2-internal_breakeven_tickets)/event.SelloutPaidTickets__c*100:.1f}%',
'Approx. 2 Add PRS % and Venue %':
f'Artist: {b1:.0f}\n LN/Internal: {b2:.0f}',
'Approx. 2 Diff':
f'{b1-artist_breakeven_tickets:.0f} tickets\n{b2-internal_breakeven_tickets:.0f} tickets',
'Approx. 2 Diff %':
f'{(b1-artist_breakeven_tickets)/event.SelloutPaidTickets__c*100:.1f}%\n{(b2-internal_breakeven_tickets)/event.SelloutPaidTickets__c*100:.1f}%',
'Approx. 3 Remove PRS % only':
f'Artist: {c1:.0f}\n LN/Internal: {c2:.0f}',
'Approx. 3 Diff':
f'{c1-artist_breakeven_tickets:.0f} tickets\n{c2-internal_breakeven_tickets:.0f} tickets',
'Approx. 3 Diff %':
f'{(c1-artist_breakeven_tickets)/event.SelloutPaidTickets__c*100:.1f}%\n{(c2-internal_breakeven_tickets)/event.SelloutPaidTickets__c*100:.1f}%',
# 'Approx. #2\nFactor in var. Per Paids': f'Artist: {b1:.0f}\n LN/Internal: {b2:.0f}',
# 'Approx. #2 Diff': f'Artist: {b1-artist_breakeven_tickets:.0f}\n LN/Internal: {b2-internal_breakeven_tickets:.0f}',
'Venue Deal':
format_venue_deal(event),
'Flat Costs':
f'{artist_breakeven:.0f}',
'Exp. Adjustments':
f'{internal_flat_expense_adjustments:.0f}',
'Ancillaries':
f'{internal_flat_ancillaries:.0f}',
'Co-Promoter':
format_copromoter(combined_copro_loss_percent,
combined_copro_expadj_percent,
combined_copro_ancillary_percent),
}
result = {k: v for k, v in result.items() if v not in {0, '0', '', None}}
return result