{"cells":[{"cell_type":"code","source":["# Variables/Parameters\n","wkClientId = ''\n","wkClientSecret = ''\n","wkTableId = ''\n","reportYear =''\n","env = ''\n"],"outputs":[{"output_type":"display_data","data":{"application/vnd.livy.statement-meta+json":{"spark_pool":null,"statement_id":3,"statement_ids":[3],"state":"finished","livy_statement_state":"available","session_id":"82ac9f9f-dc19-46d5-ad0a-ff3b07eb830a","normalized_state":"finished","queued_time":"2025-01-23T18:33:43.8954139Z","session_start_time":"2025-01-23T18:33:44.2192663Z","execution_start_time":"2025-01-23T18:33:55.8591814Z","execution_finish_time":"2025-01-23T18:33:58.8063932Z","parent_msg_id":"98fadaa5-8ddd-4ce3-85c5-39034f8c231a"},"text/plain":"StatementMeta(, 82ac9f9f-dc19-46d5-ad0a-ff3b07eb830a, 3, Finished, Available, Finished)"},"metadata":{}}],"execution_count":1,"metadata":{"microsoft":{"language":"python","language_group":"synapse_pyspark"},"tags":["parameters"]},"id":"d325ac2f-8d19-40ba-93a3-caa224ee8a7f"},{"cell_type":"code","source":["#Get ESG Metrics Data from Fabric Tables\n","sql_statement = f\"\"\"select \n","cm.MetricId as metric_id,\n","cm.ReportingPeriod as reporting_period,\n","cm.UnitOfMeasureName as uom,\n","cm.Value as value,\n","cm.DimensionName as dimension_name,\n","cm.DimensionValue as dimension_value,\n","md.MetricName as metric_name,\n","md.MeasureName as measure_name,\n","md.SustainabilityArea as sustainability_area,\n","ml.LabelKey as label_key,\n","ml.LabelValue as label_value\n","from \n","(select * from ComputedESGMetrics\n","where ComputedESGMetrics.ReportingPeriod = '{reportYear}') cm\n","left join MetricsDefinitions md \n","on cm.MetricId = md.MetricId\n","left join MetricsLabels ml\n","on cm.MetricId = ml.MetricId\"\"\"\n","df = spark.sql(sql_statement)\n","file_blob= df.toPandas().to_csv(index=None)"],"outputs":[],"execution_count":null,"metadata":{"microsoft":{"language":"python","language_group":"synapse_pyspark"}},"id":"3dd34fdf-ad7b-4a10-9ad2-b1c62a7848c9"},{"cell_type":"code","source":["# Workiva Functions\n","\n","import requests\n","import json\n","\n","def wkOauth(client_id, client_secret, environment):\n","    iamUrl = f'https://api.{environment}.wdesk.com/iam/v1/oauth2/token'  # Defaulted to Prod IAM url\n","\n","    iamHeaders = {\n","        'Content-Type': 'application/x-www-form-urlencoded',\n","        'Accept': 'application/json'\n","    }\n","\n","    iamBody = {'client_id': f'{client_id}',\n","               'client_secret': f'{client_secret}', 'grant_type': 'client_credentials'}\n","\n","    request = requests.post(iamUrl, headers=iamHeaders, data=iamBody)\n","    json_data = request.json()\n","    token = json_data['access_token']\n","    bearer = token\n","    return bearer\n","\n","def uploadFile(bearer, table_id, data, filename, env):\n","    # stage the file\n","    header1 = {\n","        'Accept': 'application/json',\n","        'Authorization': f'Bearer {bearer}'\n","    }\n","    url1 = f'https://h.{env}.wdesk.com/s/wdata/prep/api/v1/file'\n","    file = {'file': (f'{filename}', f'{data}', 'text/csv')}\n","    pld = {'tableId': f'{table_id}'}\n","    r1 = requests.post(url=url1, headers=header1, files=file, data=pld)\n","    output1 = r1.json()\n","    file_id = output1['body']['id']\n","\n","    # import the file\n","    header2 = {\n","        'Content-Type': 'application/json',\n","        'Accept': 'application/json',\n","        'Authorization': f'Bearer {bearer}'\n","    }\n","    url2 = f'https://h.{env}.wdesk.com/s/wdata/prep/api/v1/table/{table_id}/import'\n","    pld2 = {'fileId': f'{file_id}'}\n","    r2 = requests.post(url=url2, headers=header2, json=pld2)\n","    return r2.status_code\n","\n","def listFiles(bearer, tableId, env):\n","    url = f'https://h.{env}.wdesk.com/s/wdata/prep/api/v1/file'\n","    header = {\n","        'Accept': 'application/json',\n","        'Authorization': f'Bearer {bearer}'\n","    }\n","    param = {\n","        'tableId': f'{tableId}'\n","    }\n","    r = requests.get(url, headers=header, params=param)\n","    return r.json()\n","\n","def deleteFiles(bearer,tableId,fileId,env):\n","    # Un-import the file from workiva table\n","    header : {\n","        'Authorization': f'Bearer {bearer}',\n","        'Accept':'application/json'\n","    } \n","    url1 = f'https://h.{env}.wdesk.com/s/wdata/prep/api/v1/table/{tableId}/import/{fileId}'\n","    r1 = requests.delete(url1,headers={\n","        'Authorization': f'Bearer {bearer}',\n","        'Accept':'application/json'\n","    })\n","\n","    if r1.status_code == 200 :\n","        url2 = f'https://h.{env}.wdesk.com/s/wdata/prep/api/v1/file/{fileId}'\n","        r2 = requests.delete(url2,headers={\n","        'Authorization': f'Bearer {bearer}',\n","        'Accept':'application/json'\n","            })\n","        return r2.status_code\n","    else:\n","        return r1.json()    "],"outputs":[],"execution_count":null,"metadata":{"microsoft":{"language":"python","language_group":"synapse_pyspark"}},"id":"b0ed96d5-8a32-4815-ad0f-d5a67a2ac66e"},{"cell_type":"code","source":["#Upload the files into Workiva\n","\n","# Get token from Workiva\n","get_token = wkOauth(wkClientId,wkClientSecret,env)\n","\n","# Get Files List from Workiva Table\n","files_list = listFiles(get_token,wkTableId,env)\n","print(\"file list has been retrieved\")\n","\n","# Remove old file from Workiva Table\n","for v in files_list['body']:\n","    if v['name'] == f'SDSF_{reportYear}.csv':\n","        file_id = v['id']\n","        delete_file = deleteFiles(get_token,wkTableId,file_id,env)\n","        print(\"old file has been deleted from workiva table\")\n","    else:\n","        # print(\"No previous version of the same file exists. Continuing with uploading the new file in workiva table\")\n","        pass\n","            \n","\n","# Upload the file\n","print(\"Starting File Upload into Workiva Table\")\n","file_name = f'SDSF_{reportYear}.csv'\n","upld = uploadFile(get_token,wkTableId,file_blob,file_name,env)\n","print(\"File upload to workiva is complete\")"],"outputs":[],"execution_count":null,"metadata":{"microsoft":{"language":"python","language_group":"synapse_pyspark"}},"id":"b793913c-6d80-429e-89d8-b1d08a8fb379"}],"metadata":{"kernel_info":{"name":"synapse_pyspark"},"kernelspec":{"name":"synapse_pyspark","language":"Python","display_name":"Synapse PySpark"},"language_info":{"name":"python"},"microsoft":{"language":"python","language_group":"synapse_pyspark","ms_spell_check":{"ms_spell_check_language":"en"}},"nteract":{"version":"nteract-front-end@1.0.0"},"spark_compute":{"compute_id":"/trident/default","session_options":{"conf":{"spark.synapse.nbs.session.timeout":"1200000"}}},"dependencies":{"warehouse":{},"lakehouse":{"default_lakehouse":"909f739c-a5b3-46b6-83d9-b3d7a9ea00c2","default_lakehouse_name":"SDS_ESGDE_test_data_ComputedESGMetrics_LH","default_lakehouse_workspace_id":"825bc98c-47a8-4dd0-a6c0-c9a0d8e34536"}}},"nbformat":4,"nbformat_minor":5}