def main():
sql = SQL_Server_API(eos_prod_creds)
sf = Salesforce_API('*****@*****.**')
file_data = dfconcerts_get_file_data(top=5)
# offer_ids = [37008,39524,40799,38880,40115]
offer_ids = pdh.int_to_str(file_data['All']['Tour__c.EOSId__c'].drop_duplicates().dropna()).tolist()
eos_data = uk.query_tours(sql, offer_ids)
dfconcerts_data_quality_issues(file_data, eos_data)
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)
all_data = dfconcerts_add_file_data_to_eos_data(eos_data_with_missing_ids_removed, file_data)
all_data_computed = uk.add_computed_fields(sf, all_data, 'EOSId__c', 'SourceSystemId__c', 'SourceSystemId__c', 'EOSId__c', 'SourceSystemId__c', 'SourceSystemId__c')
threading.new(pdh.to_excel, all_data, 'DF Data - Raw.xlsx')
threading.new(pdh.to_excel, all_data_computed.data2, 'DF Data - With Computed Fields.xlsx')
uk.upsert_eos_data_to_rome(sf, all_data_computed.data2, delete_tours_first=True)
print(f'Missing EOS Ids in Rome: {eos_ids_missing_in_rome}')
print(f'Missing Event Ids in File: {eos_ids_missing_in_file}')
return
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():
# 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():
shows = sql.query("""
SELECT s.*
FROM ItineraryShow s
LEFT JOIN Offer o
ON s.OfferId = o.Id
WHERE o.RomeId IS NOT NULL
-- OR s.RomeId IS NOT NULL
""")
rome_shows = sf.select("""
SELECT Id, EOSId__c
FROM Event__c
WHERE EOSId__c <> NULL
""")
rome_ids = {item['Id'] for item in rome_shows}
invalid = [show for show in shows if show['RomeId'] not in rome_ids]
offer_ids = list({show['OfferId'] for show in invalid})
show_ids = list({str(show['Id']) for show in invalid})
# show_ids = ['49959']
if len(show_ids) == 0:
return
sf_tourlegs = sf.select(
"SELECT Id, Tour__r.EOSId__c, Crossed__c FROM TourLeg__c WHERE Tour__r.EOSId__c IN @offer_ids ORDER BY Order__c ASC",
return_type='flat')
eos_data = uk.query_tours(sql, offer_ids=offer_ids, is_onsale=False)
for key, val in eos_data.items():
eos_data[key] = [
item for item in val
if 'EventEOSId' in item and item['EventEOSId'] in show_ids
]
for evt in eos_data.Event__c:
evt['TourLegOrder__c'] = 100
legs = [
item for item in sf_tourlegs
if item['Tour__r.EOSId__c'] == evt['TourEOSId']
]
crossedlegs = [item for item in legs if item['Crossed__c'] == True]
del evt['TourLeg__r.SourceSystemId__c']
if len(crossedlegs) > 0:
evt['TourLeg__c'] = crossedlegs[0].Id
elif len(legs) > 0:
evt['TourLeg__c'] = legs[0].Id
else:
raise Exception('No legs...')
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)
eos_data_computed = uk.add_computed_fields(
sf, eos_data_with_missing_ids_removed).data2
del eos_data_computed['Tour__c']
del eos_data_computed['TourDeal__c']
del eos_data_computed['TourLeg__c']
del eos_data_computed['TourArtistPayment__c']
del eos_data_computed['TourOnSale__c']
with sf.bypass_settings():
sf.bypass_prod_operation_approval()
batch_sizes = {
'Event__c': 50,
'Deal__c': 30,
'EventDateTime__c': 30,
}
jobs = sf.dynamic_upsert(
eos_data_computed,
mode='bulk',
batch_sizes=batch_sizes,
remove_if_blank={'OwnerId', 'Owner.EOSId__c', 'IsTouringApp__c'})
event_jobs = [job for job in jobs if job.object_name == 'Event__c']
update_eos_shows = [{
'Id': int(item['EOSId__c']),
'RomeId': item['Id']
} for item in itertools.chain.from_iterable(event_jobs)]
sql.update('ItineraryShow', 'Id', update_eos_shows)
return
def fix(sf=None, sql=None, vars=None, data=None):
import itertools
import functions.uk_eos as uk
offer_ids = vars.get('offer_ids')
show_ids = vars.get('show_ids')
offer_ids = vars.get('offer_ids')
sf_tourlegs = sf.select(
"SELECT Id, Tour__r.EOSId__c, Crossed__c FROM TourLeg__c WHERE Tour__r.EOSId__c IN @offer_ids ORDER BY Order__c ASC",
return_type='flat')
eos_data = uk.query_tours(sql, offer_ids=offer_ids, is_onsale=False)
for key, val in eos_data.items():
eos_data[key] = [
item for item in val
if 'EventEOSId' in item and item['EventEOSId'] in show_ids
]
for evt in eos_data.Event__c:
evt['TourLegOrder__c'] = 100
legs = [
item for item in sf_tourlegs
if item['Tour__r.EOSId__c'] == evt['TourEOSId']
]
crossedlegs = [item for item in legs if item['Crossed__c'] == True]
del evt['TourLeg__r.SourceSystemId__c']
if len(crossedlegs) > 0:
evt['TourLeg__c'] = crossedlegs[0].Id
elif len(legs) > 0:
evt['TourLeg__c'] = legs[0].Id
else:
raise Exception('No legs...')
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)
eos_data_computed = uk.add_computed_fields(
sf, eos_data_with_missing_ids_removed).data2
del eos_data_computed['Tour__c']
del eos_data_computed['TourDeal__c']
del eos_data_computed['TourLeg__c']
del eos_data_computed['TourArtistPayment__c']
del eos_data_computed['TourOnSale__c']
with sf.bypass_settings():
sf.bypass_prod_operation_approval()
batch_sizes = {
'Event__c': 50,
'Deal__c': 30,
'EventDateTime__c': 30,
}
jobs = sf.dynamic_upsert(
eos_data_computed,
mode='bulk',
batch_sizes=batch_sizes,
remove_if_blank={'OwnerId', 'Owner.EOSId__c', 'IsTouringApp__c'})
event_jobs = [job for job in jobs if job.object_name == 'Event__c']
update_eos_shows = [{
'Id': int(item['EOSId__c']),
'RomeId': item['Id']
} for item in itertools.chain.from_iterable(event_jobs)]
sql.update('ItineraryShow', 'Id', update_eos_shows)