Agicap/Agicap_Export_treasury_plan.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "27ba4b1f-4cac-4732-89f4-746670a18eff",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "<img width=\"8%\" alt=\"Agicap.png\" src=\"https://raw.githubusercontent.com/jupyter-naas/awesome-notebooks/master/.github/assets/logos/Agicap.png\" style=\"border-radius: 15%\">"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "47871128-af8b-4aea-8f82-99761e2f07a9",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "# Agicap - Export treasury plan\n",
    "<a href=\"https://bit.ly/3JyWIk6\">Give Feedback</a> | <a href=\"https://github.com/jupyter-naas/awesome-notebooks/issues/new?assignees=&labels=bug&template=bug_report.md&title=Agicap+-+Export+treasury+plan:+Error+short+description\">Bug report</a>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "71de4649-bb2a-49d0-887d-e7432cea502c",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "**Tags:** #agicap #treasury #export #plan #finance #data #snippet"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "93b970bb-d728-49a7-a005-eaf0b166f02a",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "**Author:** [Florent Ravenel](https://www.linkedin.com/in/florent-ravenel)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1e2e3b7d-7bd2-4450-8104-52a279b3348e",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "**Last update:** 2023-04-27 (Created: 2023-04-26)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "25cd968d-2614-4232-b482-e4778a85cafb",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "**Description:** This notebook will export the Excel treasury plan consolidated by month from Agicap and return a dataframe."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "01841f84-cc0a-4a5e-bfb2-04b5d59819b2",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "**References:**\n",
    "- [Agicap Website](https://app.agicap.com/fr/)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9738d9da-dc20-4471-a173-39907c3b7414",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "## Input"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9e1d4e10-5293-43d4-8e2a-85db359a70d6",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "### Import libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "179ec8b5-d08e-4a22-b2af-2d30ad8c2653",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "outputs": [],
   "source": [
    "import requests\n",
    "import naas\n",
    "import pandas as pd\n",
    "from datetime import datetime\n",
    "from dateutil.relativedelta import relativedelta"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5d885e99-bf04-4ecd-97d7-4adf429f95d2",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "### Setup Variables\n",
    "- `username`: Agicap username\n",
    "- `password`: Agicap password\n",
    "- `enterprise_id`: Agicap enterprise ID. Your Agicap account manager can provide you all your enterprises/accounts ids.\n",
    "- `date_start`: First date of the extract in format: \"%Y-%m-%d\" (example: 2023-01-01). If not set it will return the first day of the current month\n",
    "- `date_end`: Last date of the extract in format: \"%Y-%m-%d\" (example: 2023-01-31). If not set it will return the last day of the current month\n",
    "- `output_csv`: csv file path to be saved as output"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "78c8d2d6-0f41-4513-9606-26432533c6c9",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Inputs\n",
    "username = naas.secret.get('AGICAP_USERNAME') or \"<username>\"\n",
    "password = naas.secret.get('AGICAP_PASSWORD') or \"<password>\"\n",
    "enterprise_id = \"00001\"\n",
    "date_start = None\n",
    "date_end = None\n",
    "\n",
    "# Outputs\n",
    "output_csv = f\"ExportExcelSimple_{enterprise_id}.csv\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e3fa61f7-7967-46e2-b998-b185823f5e91",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "## Model"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bc3b1b89-3b3e-4ada-b59b-9ce24ab20c3f",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "### Get token from Agicap\n",
    "Get token using user credentials"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a02b3f80-c763-43b6-963c-f5f0bb3b184a",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "outputs": [],
   "source": [
    "def get_token(\n",
    "    username=None,\n",
    "    password=None,\n",
    "    force_update=False,\n",
    "):\n",
    "    # Get credentials\n",
    "    if not username:\n",
    "        username = naas.secret.get('AGICAP_USERNAME')\n",
    "    if not password:\n",
    "        password = naas.secret.get('AGICAP_PASSWORD')\n",
    "        \n",
    "    # Check if token exists\n",
    "    token = naas.secret.get('AGICAP_TOKEN')\n",
    "    if token and not force_update:\n",
    "        return token\n",
    "    \n",
    "    # Sign in to get token\n",
    "    url = \"https://business-definition.agicap.com/signin\"\n",
    "    headers = {\n",
    "        \"Accept\": \"application/json, text/plain, */*\",\n",
    "        \"Content-Type\": \"application/json\"\n",
    "    }\n",
    "    payload = {\n",
    "        \"Username\": username,\n",
    "        \"Password\": password\n",
    "    }\n",
    "    res = requests.post(url, headers=headers, json=payload)\n",
    "    res.raise_for_status\n",
    "    \n",
    "    # Get agicap token\n",
    "    if len(res.json()) > 0:\n",
    "        token = res.json().get(\"token\")\n",
    "        if token != naas.secret.get('AGICAP_TOKEN'):\n",
    "            naas.secret.add('AGICAP_TOKEN', token)\n",
    "    else:\n",
    "        print('Error while connecting to AGICAP!')\n",
    "    return token\n",
    "\n",
    "token = get_token(username, password)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f4f944d3-0c0e-464d-962b-9c8cda313ca0",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "### Get date range\n",
    "Transform date to timestamp to pass it as parameters in request."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "147e1134-2df7-48af-8bae-9a07b6658ad5",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "outputs": [],
   "source": [
    "def get_date_range(\n",
    "    date_start,\n",
    "    date_end\n",
    "):\n",
    "    # Get first and last day of current month\n",
    "    first_day_month = datetime.now().replace(day=1, hour=0, minute=0, second=0, microsecond=0)\n",
    "    last_day_month = first_day_month + relativedelta(months=1) - relativedelta(seconds=1)\n",
    "    \n",
    "    # Setup range\n",
    "    if not date_start:\n",
    "        date_start = first_day_month\n",
    "    else:\n",
    "        date_start = datetime.strptime(date_start, \"%Y-%m-%d\")\n",
    "        \n",
    "    if not date_end:\n",
    "        date_end = last_day_month\n",
    "    else:\n",
    "        date_end = datetime.strptime(date_end, \"%Y-%m-%d\")\n",
    "        \n",
    "    # Timestamp for requests with milliseconds\n",
    "    ts_start = date_start.strftime(\"%s\") + \"000\"\n",
    "    ts_end = date_end.strftime(\"%s\") + \"000\"\n",
    "    return ts_start, ts_end\n",
    "\n",
    "date_start_t, date_end_t = get_date_range(date_start, date_end)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3b474b54-26f1-40b2-a857-067a17e8afcd",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "### Export treasury plan\n",
    "Get export with request"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b1b6de6b-ccd0-4c38-907b-5b2fbe51952f",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "outputs": [],
   "source": [
    "def clean_excel(\n",
    "    res,\n",
    "    header=2\n",
    "):\n",
    "    # Read excel\n",
    "    df = pd.read_excel(res.content, header=header)\n",
    "    \n",
    "    # Remove unnamed columns\n",
    "    for c in df.columns:\n",
    "        if c.startswith(\"Unnamed\"):\n",
    "            df = df.drop(c, axis=1)\n",
    "            \n",
    "    # Drop empty rows\n",
    "    df = df.dropna()\n",
    "    return df.reset_index(drop=True)\n",
    "\n",
    "def get_excel_simple(\n",
    "    enterprise_id,\n",
    "    token,\n",
    "    date_start=None,\n",
    "    date_end=None\n",
    "):\n",
    "    # Init\n",
    "    df = pd.DataFrame()\n",
    "    \n",
    "    # Payload\n",
    "    payload = {\n",
    "        \"Scenarios\":\n",
    "        [\n",
    "            {\n",
    "                \"Id\": None,\n",
    "                \"colorSelected\": False,\n",
    "                \"isEdit\": False,\n",
    "                \"isLoading\": False,\n",
    "                \"isSelected\": True,\n",
    "                \"tableauDeBordPrevisionel\": True,\n",
    "                \"forecastRawData\": True\n",
    "            }\n",
    "        ],\n",
    "        \"DateBegin\": date_start,\n",
    "        \"DateEnd\": date_end,\n",
    "        \"Periodicite\": {\"Day\": 0, \"Month\": 1}\n",
    "    }\n",
    "    # Headers\n",
    "    headers = {\n",
    "        \"Accept\": \"application/json, text/plain, */*\",\n",
    "        \"Accept-Language\": \"fr\",\n",
    "        \"Authorization\": f\"Bearer {token}\",\n",
    "        \"EntrepriseId\": enterprise_id,\n",
    "        \"Content-Type\": \"application/json\"\n",
    "    }\n",
    "    # Request\n",
    "    url = \"https://app.agicap.com/api/exportexcel/ExportExcelSimple\"\n",
    "    res = requests.post(url, headers=headers, json=payload)\n",
    "    if res.status_code == 200:\n",
    "        # Clean excel\n",
    "        df = clean_excel(res)\n",
    "    elif res.status_code == 401:\n",
    "        token = get_token()\n",
    "        headers[\"Authorization\"] = f\"Bearer {token}\"\n",
    "        res = requests.post(url, headers=headers, json=payload)\n",
    "        df = clean_excel(res)\n",
    "    else:\n",
    "        print(res.status_code, res.text)\n",
    "        print(\"❌ Error while getting:\", url)\n",
    "    return df.reset_index(drop=True)\n",
    "\n",
    "df = get_excel_simple(\n",
    "    enterprise_id,\n",
    "    token=token,\n",
    "    date_start=date_start_t,\n",
    "    date_end=date_end_t\n",
    ")\n",
    "print(\"Rows fetched:\", len(df))\n",
    "df.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a3488db5-423b-4a71-b99c-25ecded8f66c",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9ec1afc6-f1aa-460e-a51a-b29f909e0a90",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "## Output"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1260baba-fccb-40e5-9b4e-9b30609b1323",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "### Save export to CSV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6d5a4455-1169-4e82-a17d-65b5820af733",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "outputs": [],
   "source": [
    "df.to_csv(output_csv, index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.6"
  },
  "naas": {
   "notebook_id": "fde19b055488d7f318ec541fd38a5195fe025974bb22c1cb2fbf869fbb2ac7c6",
   "notebook_path": "Agicap/Agicap_Export_treasury_plan.ipynb"
  },
  "papermill": {
   "default_parameters": {},
   "environment_variables": {},
   "parameters": {},
   "version": "2.4.0"
  },
  "widgets": {
   "application/vnd.jupyter.widget-state+json": {
    "state": {},
    "version_major": 2,
    "version_minor": 0
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}