V1/features/workflow/Call Api On File Upload.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from lusidtools.jupyter_tools import toggle_code\n",
    "\n",
    "\"\"\"Call Api on File Upload\n",
    "\n",
    "Attributes\n",
    "----------\n",
    "subscriptions\n",
    "events\n",
    "notifications\n",
    "luminesce\n",
    "\"\"\"\n",
    "\n",
    "toggle_code(\"Toggle Docstring\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "# Call API on File Upload\n",
    "\n",
    "This notebook will detail how to create a process in which actions are triggered on the upload of a new file.\n",
    "\n",
    "For this example, we will manually drop an auto-load-transactions.xlsx file into [Drive](https://support.lusid.com/knowledgebase/article/KA-01672/en-us), and this will trigger an api call which will run a Luminesce query to create a portfolio, create instruments and upsert the transactions into that portfolio. This is a very simple example, but it could be extended further by triggering a job which could contain references to any number of apis.\n",
    "\n",
    "The steps are as follows:\n",
    "1. Authorise LUSID account\n",
    "2. Create subscription to FileCreated event\n",
    "3. Create webhook notification to be triggered by subscription\n",
    "    - This notification will call an API\n",
    "4. Trigger the event\n",
    "    - This will cause the notification to fire which will call the API and in turn cause data from the uploaded file to be loaded into LUSID"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "### Setup Lusid\n",
    "\n",
    "Import modules and define our api factories."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import os\n",
    "import json\n",
    "import pandas as pd\n",
    "\n",
    "# import lusid specific packages\n",
    "import lusid.models as models\n",
    "import lusid.utilities as utilities\n",
    "import lusid\n",
    "from fbnsdkutilities import ApiClientFactory\n",
    "from lusidjam import RefreshingToken\n",
    "import lusid_notifications as ln\n",
    "import lusid_drive\n",
    "\n",
    "# Set the secrets path\n",
    "secrets_path = os.getenv(\"FBN_SECRETS_PATH\")\n",
    "api_url = os.getenv(\"FBN_LUSID_API_URL\")\n",
    "\n",
    "# For running the notebook locally\n",
    "if secrets_path is None:\n",
    "    secrets_path = os.path.join(os.path.dirname(os.getcwd()), \"secrets.json\")\n",
    "\n",
    "notifications_factory = ApiClientFactory(ln, token=RefreshingToken())\n",
    "\n",
    "portfolios_factory = utilities.ApiClientFactory(\n",
    "    token=RefreshingToken(),\n",
    "    api_secrets_filename=secrets_path\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# notifications APIs\n",
    "subscriptions_api = notifications_factory.build(ln.api.SubscriptionsApi)\n",
    "notifications_api = notifications_factory.build(ln.api.NotificationsApi)\n",
    "events_api = notifications_factory.build(ln.api.EventsApi)\n",
    "event_types_api = notifications_factory.build(ln.api.EventTypesApi)\n",
    "\n",
    "# portfolios API\n",
    "portfolios_api = portfolios_factory.build(lusid.api.TransactionPortfoliosApi)\n",
    "\n",
    "# drive API\n",
    "configuration = lusid_drive.Configuration(\n",
    "    host=f'{portfolios_api.api_client.configuration.host[:-4]}/drive'\n",
    ")\n",
    "configuration.access_token = portfolios_api.api_client.configuration.access_token\n",
    "\n",
    "with lusid_drive.ApiClient(configuration) as api_client:\n",
    "    files_api = lusid_drive.FilesApi(api_client)\n",
    "    folders_api = lusid_drive.FoldersApi(api_client)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "### 1. Authorise LUSID account\n",
    "\n",
    "To authorise your LUSID account, follow the instruction at [KA-01735](https://support.lusid.com/knowledgebase/article/KA-01735/). This will allow the webhook to execute an API request on your behalf. You only need to do this once for each Lusid account.\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "### 2. Create a subscription\n",
    "\n",
    "Here we are creating a subscription to a `FileCreated` event, with a matching filter set to look for our `auto-load-transactions.xlsx` file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Subscription created for file_load.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "{'created_at': datetime.datetime(2023, 2, 7, 10, 12, 27, 756852, tzinfo=tzutc()),\n",
       " 'created_by': '00uieujafoYdmkDSx2p7',\n",
       " 'description': 'file_load',\n",
       " 'display_name': 'file_load',\n",
       " 'id': {'code': 'file_load', 'scope': 'file_load'},\n",
       " 'last_modified_at': datetime.datetime(2023, 2, 7, 10, 12, 27, 756852, tzinfo=tzutc()),\n",
       " 'last_modified_by': '00uieujafoYdmkDSx2p7',\n",
       " 'matching_pattern': {'event_type': 'FileCreated',\n",
       "                      'filter': \"FileName eq 'auto-load-transactions.xlsx'\"},\n",
       " 'status': 'Active'}"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# create subscription to file creation event\n",
    "def create_file_subscription(\n",
    "        scope: str,\n",
    "        code: str,\n",
    "        name: str,\n",
    "        matching_filter: str,\n",
    "        description: str = None\n",
    "):\n",
    "    try:\n",
    "        resp = subscriptions_api.create_subscription(\n",
    "            create_subscription=ln.models.CreateSubscription(\n",
    "                id=models.ResourceId(\n",
    "                    scope=scope,\n",
    "                    code=code\n",
    "                ),\n",
    "                display_name=name,\n",
    "                description=description if description else name,\n",
    "                status=\"Active\",\n",
    "                matching_pattern=ln.models.MatchingPattern(\n",
    "                    event_type=\"FileCreated\",\n",
    "                    filter=matching_filter\n",
    "                )\n",
    "            )\n",
    "        )\n",
    "        print(f\"Subscription created for {code}.\")\n",
    "        return resp\n",
    "    except ln.ApiException as e:\n",
    "        detail = json.loads(e.body)\n",
    "        if detail[\"code\"] != 711: # 'SubscriptionAlreadyExists'\n",
    "            raise e\n",
    "        else:\n",
    "            print(\"Subscription already exists.\")\n",
    "\n",
    "create_file_subscription(\n",
    "    scope=\"file_load\",\n",
    "    code=\"file_load\",\n",
    "    name=\"file_load\",\n",
    "    matching_filter=\"FileName eq 'auto-load-transactions.xlsx'\",\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "### 3. Create a webhook notification\n",
    "\n",
    "First, define the SQL Luminesce query to run. This query will unpack our data file from the drive and then create a portfolio, instruments and transactions.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "sql = \"\"\"\n",
    "-- Extract transaction data from LUSID Drive\n",
    "\n",
    "@txn_data =\n",
    "use Drive.Excel\n",
    "--file=/lusid-examples/auto-load-transactions.xlsx\n",
    "enduse;\n",
    "\n",
    "-- Set variables for the portfolio's scope and code\n",
    "\n",
    "@@portfolio_scope = select 'auto-load-demo';\n",
    "@@portfolio_code = select 'uk-equity';\n",
    "@@portfolio_name = select 'UK EQUITY';\n",
    "\n",
    "-- Define the portfolio data\n",
    "\n",
    "@create_portfolio =\n",
    "select 'Transaction' as PortfolioType,\n",
    "@@portfolio_scope as PortfolioScope,\n",
    "@@portfolio_code as PortfolioCode,\n",
    "@@portfolio_name as DisplayName,\n",
    "'' as Description,\n",
    "#2000-01-01# as Created,\n",
    "''as SubHoldingKeys,\n",
    "'GBP' as BaseCurrency;\n",
    "\n",
    "-- Upload the portfolio into LUSID\n",
    "\n",
    "@response_create_portfolio =\n",
    "select *\n",
    "from Lusid.Portfolio.Writer\n",
    "where ToWrite = @create_portfolio;\n",
    "\n",
    "-- Get instrument data\n",
    "\n",
    "@equity_instruments =\n",
    "select\n",
    "Name as DisplayName,\n",
    "ISIN as Isin,\n",
    "ClientInternal as ClientInternal,\n",
    "SEDOL as Sedol,\n",
    "'GBP' as DomCcy\n",
    "from @txn_data;\n",
    "\n",
    "-- Upload the transformed data into LUSID\n",
    "\n",
    "@response = select *\n",
    "from Lusid.Instrument.Equity.Writer\n",
    "where ToWrite = @equity_instruments;\n",
    "\n",
    "-- --Transform data using SQL\n",
    "\n",
    "@transactions =\n",
    "select\n",
    "@@portfolio_scope as PortfolioScope,\n",
    "@@portfolio_code as PortfolioCode,\n",
    "t.TransactionID as TxnId,\n",
    "t.Type as Type,\n",
    "t.TransactionDate as TransactionDate,\n",
    "t.SettlementDate as SettlementDate,\n",
    "t.Units as Units,\n",
    "t.Price as TradePrice,\n",
    "t.TotalConsideration as TotalConsideration,\n",
    "t.Currency as SettlementCurrency,\n",
    "t.ClientInternal as ClientInternal,\n",
    "r.LusidInstrumentId as LusidInstrumentId\n",
    "from @txn_data t\n",
    "inner join @response r\n",
    "where t.ClientInternal = r.ClientInternal;\n",
    "\n",
    "-- Upload the transformed data into LUSID\n",
    "\n",
    "select *\n",
    "from Lusid.Portfolio.Txn.Writer\n",
    "where ToWrite = @transactions;\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "Now, define the webhook notification to run our SQL query."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "outputs": [
    {
     "data": {
      "text/plain": "'Notification successfully created with ID : 7bf05c94-2d74-4da7-9f68-3930129c763e'"
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def create_webhook_notification(\n",
    "        scope: str,\n",
    "        code: str,\n",
    "        sql: str,\n",
    "):\n",
    "    # this can be changed to call any api required\n",
    "    create_webhook_notification = {\n",
    "        \"description\": \"Upsert transactions into new portfolio\",\n",
    "        \"httpMethod\": \"Put\",\n",
    "        \"url\": \"/honeycomb/api/Sql/csv\",\n",
    "        \"authenticationType\": \"Lusid\",\n",
    "        \"contentType\": \"PlainText\",\n",
    "        \"content\": sql\n",
    "    }\n",
    "\n",
    "    try:\n",
    "        response = notifications_api.create_webhook_notification(\n",
    "            scope=scope,\n",
    "            code=code,\n",
    "            create_webhook_notification=create_webhook_notification\n",
    "        )\n",
    "        return f\"Notification successfully created with ID : {response.id}\"\n",
    "    except ln.ApiException as e:\n",
    "        return e.body\n",
    "\n",
    "# delete notifications to allow updates\n",
    "notifications = notifications_api.list_notifications(scope=\"file_load\", code=\"file_load\").values\n",
    "for notification in notifications:\n",
    "    notifications_api.delete_notification(\n",
    "        scope=\"file_load\",\n",
    "        code=\"file_load\",\n",
    "        id=notification.id\n",
    "    )\n",
    "\n",
    "create_webhook_notification(\n",
    "    scope=\"file_load\",  # scope and code of subscription\n",
    "    code=\"file_load\",\n",
    "    sql=sql\n",
    ")"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "### 4. Trigger the event\n",
    "\n",
    "You can trigger the event by dropping the `auto-load-transactions.xlsx` file into drive. This file is located within the data folder. You can verify the process has been successful by checking your portfolios for `uk-equity` within the `auto-load-demo` scope.\n",
    "\n",
    "Alternatively you can run the following cells."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The following cell verifies that the portfolio doesn't exist"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Portfolio not found\n"
     ]
    }
   ],
   "source": [
    "try:\n",
    "    portfolios_api.get_transactions(scope='auto-load-demo', code='uk-equity').values\n",
    "except lusid.ApiException as e:\n",
    "    detail = json.loads(e.body)\n",
    "    if detail[\"code\"] != 109: # 'PortfolioNotFound'\n",
    "        raise e\n",
    "    else:\n",
    "        print(detail['title'])"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "Next, upload the file to drive. This will create a `FileCreated` event meaning that our event subscription will get triggered. This will kick off our webhook notification which will in turn call the Luminesce SQL API endpoint, with our SQL script as its payload. This SQL will run and begin the creation of our portfolio, instruments and transactions."
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "File created at: https://fbn-dan.lusid.com/app/data-management/drive/7d3c87a4-cbe3-47da-80e8-1b74626323d9?type=file\n"
     ]
    }
   ],
   "source": [
    "file_name = 'auto-load-transactions.xlsx'\n",
    "drive_path = 'lusid-examples'\n",
    "\n",
    "# create a directory\n",
    "try:\n",
    "    directory_response = folders_api.create_folder(\n",
    "        create_folder={\n",
    "            \"path\":'/',\n",
    "            \"name\":drive_path\n",
    "        }\n",
    "    )\n",
    "except lusid_drive.ApiException as e:\n",
    "    detail = json.loads(e.body)\n",
    "    if detail[\"code\"] != 664: # 'FolderAlreadyExists'\n",
    "        raise e\n",
    "\n",
    "with open('./data/auto-load-transactions.xlsx', 'rb') as data:\n",
    "    try:\n",
    "        response = files_api.create_file(\n",
    "            x_lusid_drive_filename=file_name,\n",
    "            x_lusid_drive_path=drive_path,\n",
    "            content_length=os.stat('./data/auto-load-transactions.xlsx').st_size,\n",
    "            body=data.read()\n",
    "        )\n",
    "        href = f\"https://{portfolios_api.api_client.configuration.host[:-4]}/app/data-management/drive/{response.id}?type=file\"\n",
    "        print(\"File created at:\", href)\n",
    "    except lusid_drive.ApiException as e:\n",
    "        detail = json.loads(e.body)\n",
    "        if detail[\"code\"] != 671: # 'FileAlreadyExists'\n",
    "            raise e"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now check that the portfolio exists and contains the expected transactions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "outputs": [
    {
     "data": {
      "text/plain": "  transaction_id  type  units    price    amount          transaction_date  \\\n0       txn-0006   Buy  100.0  2203.79  220379.0 2022-01-13 00:00:00+00:00   \n1       txn-0003   Buy  100.0    45.31    4531.0 2022-02-04 00:00:00+00:00   \n2       txn-0001   Buy  100.0   161.02   16102.0 2022-03-01 00:00:00+00:00   \n3       txn-0005  Sell  100.0  1136.50  113650.0 2022-03-03 00:00:00+00:00   \n4       txn-0002   Buy  100.0  1453.20  145320.0 2022-05-03 00:00:00+00:00   \n5       txn-0004   Buy  100.0   286.40   28640.0 2022-05-03 00:00:00+00:00   \n6       txn-0007  Sell  100.0   726.40   72640.0 2022-07-24 00:00:00+00:00   \n7       txn-0008  Sell  100.0  1214.00  121400.0 2022-07-31 00:00:00+00:00   \n8       txn-0009   Buy  100.0   263.70   26370.0 2022-08-09 00:00:00+00:00   \n\n            settlement_date transaction_currency  \n0 2022-01-16 00:00:00+00:00                  GBP  \n1 2022-02-06 00:00:00+00:00                  GBP  \n2 2022-03-04 00:00:00+00:00                  GBP  \n3 2022-03-05 00:00:00+00:00                  GBP  \n4 2022-05-07 00:00:00+00:00                  GBP  \n5 2022-05-06 00:00:00+00:00                  GBP  \n6 2022-07-27 00:00:00+00:00                  USD  \n7 2022-08-02 00:00:00+00:00                  USD  \n8 2022-08-12 00:00:00+00:00                  GBP  ",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>transaction_id</th>\n      <th>type</th>\n      <th>units</th>\n      <th>price</th>\n      <th>amount</th>\n      <th>transaction_date</th>\n      <th>settlement_date</th>\n      <th>transaction_currency</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>txn-0006</td>\n      <td>Buy</td>\n      <td>100.0</td>\n      <td>2203.79</td>\n      <td>220379.0</td>\n      <td>2022-01-13 00:00:00+00:00</td>\n      <td>2022-01-16 00:00:00+00:00</td>\n      <td>GBP</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>txn-0003</td>\n      <td>Buy</td>\n      <td>100.0</td>\n      <td>45.31</td>\n      <td>4531.0</td>\n      <td>2022-02-04 00:00:00+00:00</td>\n      <td>2022-02-06 00:00:00+00:00</td>\n      <td>GBP</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>txn-0001</td>\n      <td>Buy</td>\n      <td>100.0</td>\n      <td>161.02</td>\n      <td>16102.0</td>\n      <td>2022-03-01 00:00:00+00:00</td>\n      <td>2022-03-04 00:00:00+00:00</td>\n      <td>GBP</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>txn-0005</td>\n      <td>Sell</td>\n      <td>100.0</td>\n      <td>1136.50</td>\n      <td>113650.0</td>\n      <td>2022-03-03 00:00:00+00:00</td>\n      <td>2022-03-05 00:00:00+00:00</td>\n      <td>GBP</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>txn-0002</td>\n      <td>Buy</td>\n      <td>100.0</td>\n      <td>1453.20</td>\n      <td>145320.0</td>\n      <td>2022-05-03 00:00:00+00:00</td>\n      <td>2022-05-07 00:00:00+00:00</td>\n      <td>GBP</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>txn-0004</td>\n      <td>Buy</td>\n      <td>100.0</td>\n      <td>286.40</td>\n      <td>28640.0</td>\n      <td>2022-05-03 00:00:00+00:00</td>\n      <td>2022-05-06 00:00:00+00:00</td>\n      <td>GBP</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>txn-0007</td>\n      <td>Sell</td>\n      <td>100.0</td>\n      <td>726.40</td>\n      <td>72640.0</td>\n      <td>2022-07-24 00:00:00+00:00</td>\n      <td>2022-07-27 00:00:00+00:00</td>\n      <td>USD</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>txn-0008</td>\n      <td>Sell</td>\n      <td>100.0</td>\n      <td>1214.00</td>\n      <td>121400.0</td>\n      <td>2022-07-31 00:00:00+00:00</td>\n      <td>2022-08-02 00:00:00+00:00</td>\n      <td>USD</td>\n    </tr>\n    <tr>\n      <th>8</th>\n      <td>txn-0009</td>\n      <td>Buy</td>\n      <td>100.0</td>\n      <td>263.70</td>\n      <td>26370.0</td>\n      <td>2022-08-09 00:00:00+00:00</td>\n      <td>2022-08-12 00:00:00+00:00</td>\n      <td>GBP</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "try:\n",
    "    values = portfolios_api.get_transactions(scope='auto-load-demo', code='uk-equity').values\n",
    "    portfolio_txns = pd.DataFrame(\n",
    "        {\n",
    "            'transaction_id': [i.transaction_id for i in values],\n",
    "            'type': [i.type for i in values],\n",
    "            'units': [i.units for i in values],\n",
    "            'price': [i.transaction_price.price for i in values],\n",
    "            'amount': [i.total_consideration.amount for i in values],\n",
    "            'transaction_date': [i.transaction_date for i in values],\n",
    "            'settlement_date': [i.settlement_date for i in values],\n",
    "            'transaction_currency': [i.transaction_currency for i in values],\n",
    "        }\n",
    "    )\n",
    "    portfolio_txns.head()\n",
    "except lusid.ApiException as e:\n",
    "    detail = json.loads(e.body)\n",
    "    if detail[\"code\"] != 109: # 'PortfolioNotFound'\n",
    "        raise e"
   ],
   "metadata": {
    "collapsed": false
   }
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}