token = credentials.token return token['token_type'], token['access_token'] client = KeyVaultClient(KeyVaultAuthentication(auth_callack)) # key_bundle = client.get_key(vault_url, key_name, key_version) # json_key = key_bundle.key # COMMAND ---------- secrets = client.get_secrets(KEY_VAULT_URI) myversion = "0" for sec in secrets: if sec.id.rsplit('/')[4] == workingdir: print(sec.id.rsplit('/')[4] + ' Match') versions = client.get_secret_versions(KEY_VAULT_URI, workingdir) for version in versions: if version.attributes.enabled: print(version) myversion = version.id.rsplit('/')[5] print(myacversion) else: print(sec.id.rsplit('/')[4] + ' No Match') if myversion == "0": dbutils.notebook.exit("no secret found") # COMMAND ---------- #grab a secret from the vault mysecret = client.get_secret(KEY_VAULT_URI,workingdir,myversion)
user = cred["username"] password = cred["password"] #Setup settings for Azure Key-Vault ACR_ADMIN_PWD = 'ADMIN-PASSWORD' ACR_ADMIN_USR = '******' URL_KEY_VAULT = 'https://CHANGE_ME.vault.azure.net/' TENANT_ID_VAR = 'CHANGE_ME' URL_DOCKER_REGISTRY = 'https://CHANGE_ME.azurecr.io/v2/' # Get keys from Azure key-vault credentials = ServicePrincipalCredentials(user, password, tenant=TENANT_ID_VAR) client = KeyVaultClient(credentials) # Get version of key for PASSWORD admin_password_version = list( client.get_secret_versions(URL_KEY_VAULT, ACR_ADMIN_PWD)) path = admin_password_version[0].as_dict().get('id') admin_password_version = os.path.split(path)[1] admin_password = client.get_secret(URL_KEY_VAULT, ACR_ADMIN_PWD, admin_password_version).value # Get version of key for USERNAME admin_user_version = list( client.get_secret_versions(URL_KEY_VAULT, ACR_ADMIN_USR)) path = admin_user_version[0].as_dict().get('id') admin_user_version = os.path.split(path)[1] admin_user = client.get_secret(URL_KEY_VAULT, ACR_ADMIN_USR, admin_user_version).value ## Parse arguments. Will be used Azure Settings as default. parser = argparse.ArgumentParser( description='Prepare to cleanup Docker Container Registry by API requests')
logging.basicConfig(format='%(asctime)s - %(message)s', datefmt='%d-%b-%y %H:%M:%S', level=logging.INFO) logger = logging.getLogger(__name__) logger.info('START: Geting credentials') app_id = 'e744dc6d-3d62-4879-8f43-e2ca01a49720' tenant_id = '8e9a02ca-d0ea-4763-900c-ac6ee988b360' app_secret = 'Y38uym/cZuA]S4.W/Cnnzc9P0645dn4M' credentials = ServicePrincipalCredentials(client_id=app_id, secret=app_secret, tenant=tenant_id) secret_client = KeyVaultClient(credentials) vault_base_url = "https://kv-dse-scccth-prod01.vault.azure.net/" secret_name = "pctbag-db-host" secure_secrets_versions = secret_client.get_secret_versions(vault_base_url, secret_name) current_secret_version = sorted(list(secure_secrets_versions), key=lambda x: x.attributes.created, reverse=True)[0] current_secret_version_id = current_secret_version.id[-32:] secure_secret = secret_client.get_secret(vault_base_url, secret_name, current_secret_version_id) db_host = secure_secret.value #print("db_host = " + str(db_host)) secret_name = "pctbag-db-name" secure_secrets_versions = secret_client.get_secret_versions(vault_base_url, secret_name) current_secret_version = sorted(list(secure_secrets_versions), key=lambda x: x.attributes.created, reverse=True)[0] current_secret_version_id = current_secret_version.id[-32:] secure_secret = secret_client.get_secret(vault_base_url, secret_name, current_secret_version_id) db_name = secure_secret.value #print("db_name = " + str(db_name)) secret_name = "pctbag-db-username"
def __init__(): logging.basicConfig(format='%(asctime)s - %(message)s', datefmt='%d-%b-%y %H:%M:%S', level=logging.INFO) logger = logging.getLogger(__name__) logger.info('START: Geting credentials') app_id = 'e744dc6d-3d62-4879-8f43-e2ca01a49720' tenant_id = '8e9a02ca-d0ea-4763-900c-ac6ee988b360' app_secret = 'Y38uym/cZuA]S4.W/Cnnzc9P0645dn4M' credentials = ServicePrincipalCredentials(client_id=app_id, secret=app_secret, tenant=tenant_id) secret_client = KeyVaultClient(credentials) vault_base_url = "https://kv-dse-scccth-prod01.vault.azure.net/" secret_name = "pctbag-db-host" secure_secrets_versions = secret_client.get_secret_versions( vault_base_url, secret_name) current_secret_version = sorted(list(secure_secrets_versions), key=lambda x: x.attributes.created, reverse=True)[0] current_secret_version_id = current_secret_version.id[-32:] secure_secret = secret_client.get_secret(vault_base_url, secret_name, current_secret_version_id) db_host = secure_secret.value #print("db_host = " + str(db_host)) secret_name = "pctbag-db-name" secure_secrets_versions = secret_client.get_secret_versions( vault_base_url, secret_name) current_secret_version = sorted(list(secure_secrets_versions), key=lambda x: x.attributes.created, reverse=True)[0] current_secret_version_id = current_secret_version.id[-32:] secure_secret = secret_client.get_secret(vault_base_url, secret_name, current_secret_version_id) db_name = secure_secret.value #print("db_name = " + str(db_name)) secret_name = "pctbag-db-username" secure_secrets_versions = secret_client.get_secret_versions( vault_base_url, secret_name) current_secret_version = sorted(list(secure_secrets_versions), key=lambda x: x.attributes.created, reverse=True)[0] current_secret_version_id = current_secret_version.id[-32:] secure_secret = secret_client.get_secret(vault_base_url, secret_name, current_secret_version_id) db_user = secure_secret.value #print("db_username = "******"pctbag-db-password" secure_secrets_versions = secret_client.get_secret_versions( vault_base_url, secret_name) current_secret_version = sorted(list(secure_secrets_versions), key=lambda x: x.attributes.created, reverse=True)[0] current_secret_version_id = current_secret_version.id[-32:] secure_secret = secret_client.get_secret(vault_base_url, secret_name, current_secret_version_id) db_password = secure_secret.value logger.info('START: Establish database connection') driver_name = '{ODBC Driver 17 for SQL Server}' connection_string = 'DRIVER=' + driver_name + ';SERVER=' + db_host + ';PORT=1433;DATABASE=' + db_name + ';UID=' + db_user + ';PWD=' + db_password connection = pyodbc.connect(connection_string) logger.info('START: Query DB Table - pctbag_tlkpMonthToNum') monthmap = pd.read_sql("SELECT * FROM pctbag_tlkpMonthToNum", connection) logger.info('START: Query DB Table - pctbag_tlkpSKUMapping') SKU_map = pd.read_sql("SELECT * FROM pctbag_tlkpSKUMapping", connection) logger.info('START: Query DB Table - pctbag_tlkpSegmentMapping') seg_map = pd.read_sql("SELECT * FROM pctbag_tlkpSegmentMapping", connection) logger.info('START: Query DB Table - pctbag_tlkpBagProductCost') mrg_rp = pd.read_sql("SELECT * FROM pctbag_tlkpBagProductCost", connection) logger.info('START: Query DB Table - pctbag_tlkpLogParameters') log_para = pd.read_sql("SELECT * FROM pctbag_tlkpLogParameters", connection) logger.info('START: Query DB Table - pctbag_tlkpAreaProvinceMapping') areaMapping = pd.read_sql("SELECT * FROM pctbag_tlkpAreaProvinceMapping", connection) logger.info('START: Query DB Table - pctbag_tlkBagSalesRep') salesRep = pd.read_sql("SELECT * FROM pctbag_tlkBagSalesRep", connection) logger.info('START: Query DB Table - SAP BI') data = pd.read_sql("SELECT * FROM dbo.pct_bag_sapbi", connection) trans_as_of_date = data['Calendar Day'].max() updated_date = date.today() bkp_data = data bkp_monthmap = monthmap bkp_SKU_map = SKU_map bkp_seg_map = seg_map bkp_mrg_rp = mrg_rp bkp_log_para = log_para bkp_areaMapping = areaMapping bkp_salesRep = salesRep bkp_trans_as_of_date = trans_as_of_date bkp_updated_date = updated_date logger.info('START: Generating Power BI Data - PGT') data = pd.merge(data, monthmap, how='inner', left_on=['Calendar Year', 'Calendar month'], right_on=['CalendarYear', 'CalendarMonth']) CM = data['MonthNo'].max() LM = CM - 1 df = data \ >> mask(X['MonthNo'] >= LM) \ >> select( X['Sold-to Area (SCCC)'], \ X['Sold-to Province'], \ X['MonthNo'], \ X['Product Hierarchy Level 4'], \ X['Volume Sold'], \ X['Contribution Margin'] \ ) \ >> mutate(\ Volume_last_month = (X['MonthNo']==LM) * X['Volume Sold'], \ Volume_current_month = (X['MonthNo']==CM) * X['Volume Sold'], \ ) \ >> group_by(\ X['Sold-to Area (SCCC)'], \ X['Sold-to Province'], \ X['Product Hierarchy Level 4'], \ ) \ >> summarize(\ Contribution_Margin_last_month = (X['Contribution Margin'] * X['Volume_last_month']).sum()/X['Volume_last_month'].sum(),\ Contribution_Margin_current_month = (X['Contribution Margin'] * X['Volume_current_month']).sum()/ X['Volume_current_month'].sum(),\ Volume_last_month = X['Volume_last_month'].sum(),\ Volume_current_month = X['Volume_current_month'].sum(),\ ) SKU_map['Product Hierarchy Level 4'] = SKU_map['SKU'] data_final = pd.merge(df, SKU_map, how='left', on='Product Hierarchy Level 4') data_final = data_final[[ 'Sold-to Area (SCCC)', 'Sold-to Province', 'Product Hierarchy Level 4', 'SKU1', 'BagWeight', 'Contribution_Margin_last_month', 'Contribution_Margin_current_month', 'Volume_last_month', 'Volume_current_month' ]] data_final[['Contribution_Margin_last_month' ]] = data_final[['Contribution_Margin_last_month' ]].replace([np.inf, -np.inf], np.nan) data_final[['Contribution_Margin_current_month' ]] = data_final[['Contribution_Margin_current_month' ]].replace([np.inf, -np.inf], np.nan) data_final[['Volume_last_month' ]] = data_final[['Volume_last_month' ]].replace([np.inf, -np.inf], np.nan) data_final[['Volume_last_month' ]] = data_final[['Volume_current_month' ]].replace([np.inf, -np.inf], np.nan) data_final.rename( { 'Sold-to Area (SCCC)': 'Area', 'Sold-to Province': 'SoldToProvince', 'Product Hierarchy Level 4': 'ProductHierarchyLevel4', 'Contribution_Margin_last_month': 'ContributionMarginLM', 'Contribution_Margin_current_month': 'ContributionMarginCM', 'Volume_last_month': 'VolumeLM', 'Volume_current_month': 'VolumeCM' }, axis=1, inplace=True) data_final['DataAsOfDate'] = trans_as_of_date data_final['UpdatedDate'] = updated_date pgt_all = data_final logger.info('END: Generating Power BI data (Price Gap Tracker) /' + str(len(pgt_all)) + ' records') logger.info('START: Writing output csv file - PGT') secret_name = "pctbag-pgt-ofilename" secure_secrets_versions = secret_client.get_secret_versions( vault_base_url, secret_name) current_secret_version = sorted(list(secure_secrets_versions), key=lambda x: x.attributes.created, reverse=True)[0] current_secret_version_id = current_secret_version.id[-32:] secure_secret = secret_client.get_secret(vault_base_url, secret_name, current_secret_version_id) pgt_ofilename = secure_secret.value pgt_all.to_csv(pgt_ofilename, index=False) logger.info('START: Moving result to blob storage - PGT') secret_name = "pctbag-st-name" secure_secrets_versions = secret_client.get_secret_versions( vault_base_url, secret_name) current_secret_version = sorted(list(secure_secrets_versions), key=lambda x: x.attributes.created, reverse=True)[0] current_secret_version_id = current_secret_version.id[-32:] secure_secret = secret_client.get_secret(vault_base_url, secret_name, current_secret_version_id) account_name = secure_secret.value secret_name = "pctbag-st-accesskey" secure_secrets_versions = secret_client.get_secret_versions( vault_base_url, secret_name) current_secret_version = sorted(list(secure_secrets_versions), key=lambda x: x.attributes.created, reverse=True)[0] current_secret_version_id = current_secret_version.id[-32:] secure_secret = secret_client.get_secret(vault_base_url, secret_name, current_secret_version_id) account_key = secure_secret.value secret_name = "pctbag-st-pbiContainers" secure_secrets_versions = secret_client.get_secret_versions( vault_base_url, secret_name) current_secret_version = sorted(list(secure_secrets_versions), key=lambda x: x.attributes.created, reverse=True)[0] current_secret_version_id = current_secret_version.id[-32:] secure_secret = secret_client.get_secret(vault_base_url, secret_name, current_secret_version_id) container_name = secure_secret.value block_blob_service = BlockBlobService(account_name, account_key) with open(pgt_ofilename, 'r') as myfile: file_content = myfile.read() block_blob_service.create_blob_from_text(container_name, pgt_ofilename, file_content) logger.info('END: Write result to blob storage - PGT') logger.info('START: RESET CONFIGURATION - TAB6n7') sap_data = bkp_data month_map = bkp_monthmap seg_map = bkp_seg_map mrg_rp = bkp_mrg_rp log_para = bkp_log_para trans_as_of_date = bkp_trans_as_of_date updated_date = bkp_updated_date logger.info('END: RESET CONFIGURATION - TAB6n7') logger.info('START: Generating Power BI Data (Tab6)') # Map month number to SAP data sap_data['Calendar year'] = sap_data['Calendar Year'] sap_data['Quantity of Sale Unit'] = sap_data['Volume Sold'] sap_data1 = pd.merge(sap_data, month_map, how='left', left_on=['Calendar year', 'Calendar month'], right_on=['CalendarYear', 'CalendarMonth']) sap_data1 = sap_data1.drop(['CalendarYear', 'CalendarMonth'], axis=1) max_month = sap_data1['MonthNo'].max() min_month = sap_data1['MonthNo'].min() rolling_8months = sap_data1 \ >> mask((X['Order - Price Category'] == "Maintain Market") | (X['Order - Price Category']=="Normal Sales")) \ >> mask((X['Sold-to Sales Office'] == "NSD-1") | (X['Sold-to Sales Office'] == "NSD-2")) \ >> mask(X['MonthNo'] > max_month -8 ) \ >> group_by(X['Sold-to Customer']) \ >> summarize( Volume = X['Quantity of Sale Unit'].sum()) # Map segments sap_data2 = pd.merge( sap_data1, seg_map, how='inner', left_on=['Sold-to Area (SCCC)', 'Product Hierarchy Level 4'], right_on=['Area', 'ProductHierarchyLevel4']) sap_data2 = sap_data2.drop(['Area', 'ProductHierarchyLevel4'], axis=1) # Map var costs and packing costs from mrgin report sap_data2 = pd.merge( sap_data2, mrg_rp, how='inner', left_on=[ 'Calendar month', 'Calendar year', 'Product Hierarchy Level 4' ], right_on=['CalendarMonth', 'CalendarYear', 'ProductHierarchyLevel4']) sap_data2 = sap_data2.drop( ['CalendarYear', 'CalendarMonth', 'ProductHierarchyLevel4'], axis=1) ## Collate data for segment customer tab custCM = sap_data2 \ >> mask((X['Order - Price Category'] == "Maintain Market") | (X['Order - Price Category']=="Normal Sales")) \ >> mask((X['Sold-to Sales Office'] == "NSD-1") | (X['Sold-to Sales Office'] == "NSD-2")) \ >> mask(X['MonthNo'] > max_month - 8 ) \ >> mutate(\ Total_Discount_rebate = X['Discount'] + X['Rebate'], \ Contribution_Margin = X['Marketing Net Sales'] - X['VariableProductionCost']- X['PackingCost'] \ )\ >> group_by(\ X['Segment'], \ X['Sold-to Customer']\ )\ >> summarize(\ Segment_Volume = X['Quantity of Sale Unit'].sum(),\ CM = (X['Quantity of Sale Unit']*X['Contribution_Margin']).sum()\ ) \ >> mutate(Actual_CM = X['CM']/X['Segment_Volume']) \ >> select(\ X['Segment'], \ X['Sold-to Customer'], \ X['Actual_CM'])\ # Join CM with 8 months volume curveData = pd.merge(custCM, rolling_8months, how='inner', on='Sold-to Customer') # Join curveData with segment wise log curve parameters curveData["Segment"] = pd.to_numeric(curveData["Segment"]) log_para["Segment"] = pd.to_numeric(log_para["Segment"]) curveData = pd.merge(curveData, log_para, how='inner', left_on=['Segment'], right_on=['Segment']) curveData['Target_CM'] = curveData['Intercept'] + ( curveData['Coefficient'] * (np.log(curveData['Volume']) / np.log(curveData['LogBase']))) curveData[ 'Yellow_line'] = curveData['Target_CM'] + curveData['YellowDelta'] curveData['Black_line'] = curveData['BlackLine'] curveData['Current_Volume'] = curveData['Volume'] curveData = curveData[[ 'Segment', 'Sold-to Customer', 'Current_Volume', 'Actual_CM', 'Target_CM', 'Black_line', 'Yellow_line' ]] curveData.rename( { 'Sold-to Customer': 'SoldToCustomer', 'Current_Volume': 'CurrentVolume', 'Actual_CM': 'ActualContributionMargin', 'Target_CM': 'TargetContributionMargin', 'Black_line': 'BlackLine', 'Yellow_line': 'YellowLine' }, axis=1, inplace=True) curveData['DataAsOfDate'] = trans_as_of_date curveData['UpdatedDate'] = updated_date logger.info('END: Generating Power BI data (Tab6 Curve Data) /' + str(len(curveData)) + ' records') logger.info('START: Writing output csv file (Tab6 Curve Data)') secret_name = "pctbag-tab6-ofilename" secure_secrets_versions = secret_client.get_secret_versions( vault_base_url, secret_name) current_secret_version = sorted(list(secure_secrets_versions), key=lambda x: x.attributes.created, reverse=True)[0] current_secret_version_id = current_secret_version.id[-32:] secure_secret = secret_client.get_secret(vault_base_url, secret_name, current_secret_version_id) tab6_ofilename = secure_secret.value curveData.to_csv(tab6_ofilename, index=False) logger.info('START: Moving result to blob storage (Tab6 Curve Data)') secret_name = "pctbag-st-name" secure_secrets_versions = secret_client.get_secret_versions( vault_base_url, secret_name) current_secret_version = sorted(list(secure_secrets_versions), key=lambda x: x.attributes.created, reverse=True)[0] current_secret_version_id = current_secret_version.id[-32:] secure_secret = secret_client.get_secret(vault_base_url, secret_name, current_secret_version_id) account_name = secure_secret.value secret_name = "pctbag-st-accesskey" secure_secrets_versions = secret_client.get_secret_versions( vault_base_url, secret_name) current_secret_version = sorted(list(secure_secrets_versions), key=lambda x: x.attributes.created, reverse=True)[0] current_secret_version_id = current_secret_version.id[-32:] secure_secret = secret_client.get_secret(vault_base_url, secret_name, current_secret_version_id) account_key = secure_secret.value secret_name = "pctbag-st-pbiContainers" secure_secrets_versions = secret_client.get_secret_versions( vault_base_url, secret_name) current_secret_version = sorted(list(secure_secrets_versions), key=lambda x: x.attributes.created, reverse=True)[0] current_secret_version_id = current_secret_version.id[-32:] secure_secret = secret_client.get_secret(vault_base_url, secret_name, current_secret_version_id) container_name = secure_secret.value block_blob_service = BlockBlobService(account_name, account_key) with open(tab6_ofilename, 'r') as myfile: file_content = myfile.read() block_blob_service.create_blob_from_text(container_name, tab6_ofilename, file_content) logger.info('END: Write result to blob storage (Tab6 Curve Data)') ## 2. Data for current month and past month performance max_month = sap_data1['MonthNo'].max() rolling_8months_vol = sap_data1 \ >> mask((X['Order - Price Category'] == "Maintain Market") | (X['Order - Price Category']=="Normal Sales")) \ >> mask((X['Sold-to Sales Office'] == "NSD-1") | (X['Sold-to Sales Office'] == "NSD-2")) \ >> mutate(\ Volume_baseline = ((X['MonthNo'] > (max_month-8)) & (X['MonthNo'] <= (max_month-1))) * X['Quantity of Sale Unit'],\ Volume_current = ((X['MonthNo'] > (max_month-8)) & (X['MonthNo'] <= (max_month))) * X['Quantity of Sale Unit']\ )\ >> group_by(X['Sold-to Customer']) \ >> summarize(\ Volume_baseline = X['Volume_baseline'].sum(),\ Volume_current = X['Volume_current'].sum()\ ) segmentData = sap_data2 \ >> mask((X['Order - Price Category'] == "Maintain Market") | (X['Order - Price Category']=="Normal Sales")) \ >> mask((X['Sold-to Sales Office'] == "NSD-1") | (X['Sold-to Sales Office'] == "NSD-2")) \ >> mutate(\ cm = X['Marketing Net Sales'] - X['VariableProductionCost'] - X['PackingCost'],\ discount = X['Discount'] + X['Rebate'], vol_baseline = ((X['MonthNo'] > (max_month-8)) & (X['MonthNo'] <= (max_month-1))) * X['Quantity of Sale Unit'],\ vol_current = ((X['MonthNo'] > (max_month-8)) & (X['MonthNo'] <= (max_month))) * X['Quantity of Sale Unit'],\ vol_currentMonth = (X['MonthNo'] == (max_month)) * X['Quantity of Sale Unit'],\ vol_lastMonth = (X['MonthNo'] == (max_month-1)) * X['Quantity of Sale Unit']\ )\ >> mutate(\ cm_baseline = ((X['MonthNo'] > (max_month-8)) & (X['MonthNo'] <= (max_month-1))) * X['cm'] * X['Quantity of Sale Unit'],\ cm_current = ((X['MonthNo'] > (max_month-8)) & (X['MonthNo'] <= (max_month))) * X['cm'] * X['Quantity of Sale Unit'],\ cm_currentMonth = (X['MonthNo'] == (max_month)) * X['cm'] * X['Quantity of Sale Unit'],\ cm_lastMonth = (X['MonthNo'] == (max_month-1)) * X['cm'] * X['Quantity of Sale Unit'],\ discount_currentMonth = (X['MonthNo'] == (max_month)) * X['discount'] * X['Quantity of Sale Unit'],\ discount_lastMonth = (X['MonthNo'] == (max_month-1)) * X['discount'] * X['Quantity of Sale Unit']\ )\ >> group_by(\ X['Segment'],\ X['Sold-to Customer'] \ )\ >> summarize(\ Volume_baseline = X['vol_baseline'].sum(),\ Volume_current = X['vol_current'].sum(),\ discount_currentMonth = X['discount_currentMonth'].sum(),\ discount_lastMonth = X['discount_lastMonth'].sum(),\ cm_currentMonth = X['cm_currentMonth'].sum(),\ cm_lastMonth = X['cm_lastMonth'].sum(),\ Vol_currentMonth = X['vol_currentMonth'].sum(),\ Vol_lastMonth = X['vol_lastMonth'].sum(),\ cm_baseline = X['cm_baseline'].sum(),\ cm_current = X['cm_current'].sum()\ )\ >> mutate(\ cm_baseline = (X['cm_baseline']/X['Volume_baseline']),\ cm_current = (X['cm_current']/X['Volume_current'])\ )\ >> select(\ X['Segment'],\ X['Sold-to Customer'],\ X['cm_baseline'],\ X['cm_current'],\ X['cm_currentMonth'],\ X['cm_lastMonth'],\ X['Vol_currentMonth'],\ X['Vol_lastMonth'],\ X['discount_currentMonth'],\ X['discount_lastMonth']\ ) logger.info('DONE: POINT 3') segmentData = pd.merge(segmentData, rolling_8months_vol, how='inner', on="Sold-to Customer") segmentData["Segment"] = pd.to_numeric(segmentData["Segment"]) log_para["Segment"] = pd.to_numeric(log_para["Segment"]) segmentData = pd.merge(segmentData, log_para, how='inner', left_on=['Segment'], right_on=['Segment']) segmentData['Target_CM_baseline'] = segmentData['Intercept'] + ( segmentData['Coefficient'] * (np.log(segmentData['Volume_baseline']) / np.log(segmentData['LogBase']))) segmentData['Target_CM_current'] = segmentData['Intercept'] + ( segmentData['Coefficient'] * (np.log(segmentData['Volume_current']) / np.log(segmentData['LogBase']))) segmentData['Yellow_line_baseline'] = segmentData[ 'Target_CM_baseline'] + segmentData['YellowDelta'] segmentData['Yellow_line_current'] = segmentData[ 'Target_CM_current'] + segmentData['YellowDelta'] segmentData['Black_line'] = segmentData['BlackLine'] segmentData['Scenario_baseline'] = np.where( segmentData['cm_baseline'] >= segmentData['Target_CM_baseline'], 'A', np.where( segmentData['cm_baseline'] >= segmentData['Yellow_line_baseline'], 'B', np.where(segmentData['cm_baseline'] >= segmentData['Black_line'], 'C', 'D'))) segmentData['Scenario_current'] = np.where( segmentData['cm_current'] >= segmentData['Target_CM_current'], 'A', np.where( segmentData['cm_current'] >= segmentData['Yellow_line_current'], 'B', np.where(segmentData['cm_current'] >= segmentData['Black_line'], 'C', 'D'))) segmentData = segmentData[[ 'Segment', 'Scenario_baseline', 'Scenario_current', 'cm_currentMonth', 'cm_lastMonth', 'Vol_currentMonth', 'Vol_lastMonth', 'discount_currentMonth', 'discount_lastMonth' ]] segmentData.rename( { 'Scenario_baseline': 'ScenarioBaseline', 'Scenario_current': 'ScenarioCurrent', 'cm_currentMonth': 'ContributionMarginCM', 'cm_lastMonth': 'ContributionMarginLM', 'Contribution_Margin_current_month': 'ContributionMarginCM', 'Vol_lastMonth': 'VolumeLM', 'Vol_currentMonth': 'VolumeCM', 'discount_currentMonth': 'DiscountCM', 'discount_lastMonth': 'DiscountLM' }, axis=1, inplace=True) segmentData['DataAsOfDate'] = trans_as_of_date segmentData['UpdatedDate'] = updated_date logger.info('END: Generating Power BI data (Tab6 & 7) /' + str(len(segmentData)) + ' records') logger.info('START: Writing output csv file (Tab6 & 7)') secret_name = "pctbag-t67-ofilename" secure_secrets_versions = secret_client.get_secret_versions( vault_base_url, secret_name) current_secret_version = sorted(list(secure_secrets_versions), key=lambda x: x.attributes.created, reverse=True)[0] current_secret_version_id = current_secret_version.id[-32:] secure_secret = secret_client.get_secret(vault_base_url, secret_name, current_secret_version_id) t67_ofilename = secure_secret.value segmentData.to_csv(t67_ofilename, index=False) logger.info('START: Moving result to blob storage (Tab6 & 7)') secret_name = "pctbag-st-name" secure_secrets_versions = secret_client.get_secret_versions( vault_base_url, secret_name) current_secret_version = sorted(list(secure_secrets_versions), key=lambda x: x.attributes.created, reverse=True)[0] current_secret_version_id = current_secret_version.id[-32:] secure_secret = secret_client.get_secret(vault_base_url, secret_name, current_secret_version_id) account_name = secure_secret.value secret_name = "pctbag-st-accesskey" secure_secrets_versions = secret_client.get_secret_versions( vault_base_url, secret_name) current_secret_version = sorted(list(secure_secrets_versions), key=lambda x: x.attributes.created, reverse=True)[0] current_secret_version_id = current_secret_version.id[-32:] secure_secret = secret_client.get_secret(vault_base_url, secret_name, current_secret_version_id) account_key = secure_secret.value secret_name = "pctbag-st-pbiContainers" secure_secrets_versions = secret_client.get_secret_versions( vault_base_url, secret_name) current_secret_version = sorted(list(secure_secrets_versions), key=lambda x: x.attributes.created, reverse=True)[0] current_secret_version_id = current_secret_version.id[-32:] secure_secret = secret_client.get_secret(vault_base_url, secret_name, current_secret_version_id) container_name = secure_secret.value block_blob_service = BlockBlobService(account_name, account_key) with open(t67_ofilename, 'r') as myfile: file_content = myfile.read() block_blob_service.create_blob_from_text(container_name, t67_ofilename, file_content) logger.info('END: Write result to blob storage (Tab6 & 7)') connection.close() return 'SUCCESS'