Agicap/Agicap_Get_transactions_by_account.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "eb397bd6-e162-4022-b924-aaa788fc3077",
   "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": "3a556037-c0e4-4f60-b53e-9f20d76df238",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "# Agicap - Get transactions by account\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+-+Get+transactions+by+account:+Error+short+description\">Bug report</a>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "34b2748e-6b0a-4ce1-9b30-b90fd2c71079",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "**Tags:** #agicap #forecast #company #data #python"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e575bd90-d1cb-4081-8108-9490dc8447a0",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "**Author:** [Florent Ravenel](https://www.linkedin.com/in/florent-ravenel)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "37038a7d-407d-49e8-9ca2-358bcc850434",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "**Last update:** 2023-09-18 (Created: 2023-09-18)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7555cfe2-b035-4a3c-a79b-ee86dd41e15d",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "**Description:** This notebook is designed to retrieve all transactions for a specified company and account from Agicap. It will then organize this data into a structured DataFrame for easy analysis. \n",
    "The DataFrame returned contains the following columns:\n",
    "- 'ENTREPRISE_ID': This column represents the unique identifier of the company.\n",
    "- 'COMPTE_ID': This column indicates the specific account ID related to the transaction.\n",
    "- 'TRANSACTION_ID': This column holds the unique transaction ID.\n",
    "- 'TRANSACTION_NAME': This column contains the name or description of the transaction.\n",
    "- 'CATEGORY_ID': This column represents the unique identifier of the transaction category.\n",
    "- 'CATEGORY_NAME': This column contains the name of the transaction category.\n",
    "- 'PROJECTS': This column is intended for any project-related information linked with the transaction.\n",
    "- 'CURRENCY': This column indicates the currency in which the transaction was made.\n",
    "- 'DATE_ORDER': This column holds the order date of the transaction in Unix timestamp format.\n",
    "- 'DATE': This column contains the date of the transaction in 'DD/MM/YYYY' format.\n",
    "- 'VALUE': This column represents the monetary value of the transaction."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1b67c259-20ee-4772-9d45-f777ae0a13e4",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "**References:**\n",
    "- [Agicap - Transactions](https://app.agicap.com/fr/app/paid/list)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b6a14a76-b442-4e2c-a1aa-5ad1afeac4f3",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "## Input"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "22c375cc-0f73-4ccf-8343-c6ffae0f55c3",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "### Import libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8444fcb8-e1d2-46f0-ae48-d700f2f232a0",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "outputs": [],
   "source": [
    "import requests\n",
    "import naas\n",
    "import pandas as pd\n",
    "import json"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d5ab947d-7760-4bc0-87cf-bff6c1cb31b7",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "### Setup variables\n",
    "**Mandatory**\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",
    "- `account_id`: Agicap enterprise ID. Your Agicap account manager can provide you all your enterprises/accounts ids.\n",
    "\n",
    "**Optional**\n",
    "- `output_csv`: csv file path to be saved as output"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ac122764-ea31-4162-b38b-4e0d8cda2e6d",
   "metadata": {
    "papermill": {},
    "tags": [
     "parameters"
    ]
   },
   "outputs": [],
   "source": [
    "# Mandatory\n",
    "username = naas.secret.get('AGICAP_USERNAME') or \"<username>\"\n",
    "password = naas.secret.get('AGICAP_PASSWORD') or \"<password>\"\n",
    "enterprise_id = \"00001\"\n",
    "account_id = \"00001\"\n",
    "\n",
    "# Optional\n",
    "output_csv = f\"Transactions_{enterprise_id}_{account_id}.csv\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7dd3be39-682b-4ed3-a4e4-89f1289677fe",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "## Model"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "12ea1d10-c9d6-4f87-b9bf-db7edc6c0dc9",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "### Get token from Agicap\n",
    "Get token using user credentials"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "aacad552-faf3-4fdb-8416-ddb9bda275ce",
   "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": "f12a9609-21e7-4dbf-a7c9-6aa8297b245a",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "### Get transactions\n",
    "Get all transactions using API"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "eefb367e-8e56-4366-b474-edb607ddd047",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "outputs": [],
   "source": [
    "def get_export(token, company_id, url, payload):\n",
    "    # Headers\n",
    "    headers = {\n",
    "        \"Accept\": \"application/json, text/plain, */*\",\n",
    "        \"Accept-Language\": \"fr\",\n",
    "        \"Authorization\": f\"Bearer {token}\",\n",
    "        \"EntrepriseId\": str(company_id),\n",
    "        \"Content-Type\": \"application/json\"\n",
    "    }\n",
    "\n",
    "    # Request\n",
    "    res = requests.post(url, headers=headers, json=payload)\n",
    "    return res\n",
    "\n",
    "# Flatten the nested dict\n",
    "def flatten_dict(d, parent_key='', sep='_'):\n",
    "    \"\"\"\n",
    "    Flattens a nested dictionary into a single level dictionary.\n",
    "\n",
    "    Args:\n",
    "        d (dict): A nested dictionary.\n",
    "        parent_key (str): Optional string to prefix the keys with.\n",
    "        sep (str): Optional separator to use between parent_key and child_key.\n",
    "\n",
    "    Returns:\n",
    "        dict: A flattened dictionary.\n",
    "    \"\"\"\n",
    "    items = []\n",
    "    for k, v in d.items():\n",
    "        new_key = f\"{parent_key}{sep}{k}\" if parent_key else k\n",
    "        if isinstance(v, dict):\n",
    "            items.extend(flatten_dict(v, new_key, sep=sep).items())\n",
    "        else:\n",
    "            items.append((new_key, v))\n",
    "    return dict(items)\n",
    "\n",
    "\n",
    "def get_transactions(\n",
    "    company_id,\n",
    "    account_id,\n",
    "    token=None,\n",
    "    company_name=None,\n",
    "    account_name=None,\n",
    "):\n",
    "    # Init\n",
    "    data = []\n",
    "    skip = 0\n",
    "    take = 100\n",
    "    \n",
    "    # Get token\n",
    "    if not token:\n",
    "        token = naas.secret.get('AGICAP_TOKEN')\n",
    "    \n",
    "    # Payload\n",
    "    payload = {\n",
    "        \"filters\":\n",
    "        {\n",
    "            \"nature\": 1,\n",
    "            \"statusesToInclude\": 0,\n",
    "            \"statusesToExclude\": 0,\n",
    "            \"transactionWithProjectsDistributionStale\": False,\n",
    "            \"includeTransactionWithoutProject\": False,\n",
    "            \"transactionsType\": 0,\n",
    "            \"categorizationState\": 0,\n",
    "            \"bankAccountIds\": [str(account_id)]\n",
    "        },\n",
    "        \"sort\":{\"asc\": False, \"sortField\": \"0\"},\n",
    "        \"pagination\": {\"skip\": skip,\"take\": take}\n",
    "    }    \n",
    "    # URL\n",
    "    url = \"https://app.agicap.com/api/paidtransaction/GetByFilters\"\n",
    "    \n",
    "    # Loop to update token if needed\n",
    "    while True:\n",
    "        # Requests\n",
    "        result = []\n",
    "        res = get_export(token, str(company_id), url, payload)\n",
    "        \n",
    "        # Manage result\n",
    "        if res.status_code == 200:\n",
    "            message = f\"{res.status_code} - Export successfull\"\n",
    "            result = res.json().get(\"Result\")\n",
    "        else:\n",
    "            print(\"❌ Error while getting:\", url)\n",
    "            message = f\"{res.status_code} - {res.text}\"\n",
    "            print(message)\n",
    "                        \n",
    "        # Parse result to flaten df\n",
    "        for r in result:\n",
    "            data.append(flatten_dict(r))\n",
    "            \n",
    "        # Break if result empty\n",
    "        if len(result) == 0:\n",
    "            print(f\"No result found (skip={skip})\")\n",
    "            break\n",
    "        elif len(result) < take:\n",
    "            break\n",
    "        else:\n",
    "            skip += take \n",
    "            payload[\"pagination\"][\"skip\"] = skip\n",
    "            \n",
    "    # Create df\n",
    "    df = pd.DataFrame(data).reset_index(drop=True)\n",
    "    \n",
    "    # Enrich transactions\n",
    "    if len(df) > 0:\n",
    "        i = 0\n",
    "        df.columns = df.columns.str.upper()\n",
    "        # Insert company info\n",
    "        if company_name:\n",
    "            df.insert(loc=i, column=\"ENTREPRISE\", value=company_name)\n",
    "            i += 1\n",
    "        df.insert(loc=i, column=\"ENTREPRISE_ID\", value=company_id)\n",
    "        i += 1\n",
    "        \n",
    "        # Insert account info\n",
    "        if account_name:\n",
    "            df.insert(loc=i, column=\"COMPTE\", value=account_name)\n",
    "            i += 1\n",
    "        df.insert(loc=i, column=\"COMPTE_ID\", value=account_id)\n",
    "    return df, message\n",
    "\n",
    "df, message = get_transactions(\n",
    "    enterprise_id,\n",
    "    account_id,\n",
    "    token\n",
    ")\n",
    "print(\"Rows:\", len(df))\n",
    "df.head(1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f26a44d4-0072-4399-87ec-3a0afe5d110e",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "### Prep data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "084468ea-23d3-43d6-ab95-60c74302770e",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "outputs": [],
   "source": [
    "def get_projects_dict(df):\n",
    "    # Init\n",
    "    output = {}\n",
    "    projects = df[\"PROJECTS\"].astype(str).unique().tolist()\n",
    "    \n",
    "    # Loop on unique value\n",
    "    for p in projects:\n",
    "        if p != '[]':\n",
    "            p = p.replace(\"'\", '\"') # replace single quotes with double quotes to make it a valid JSON string\n",
    "            data = json.loads(p) # parse the string into a Python object\n",
    "            title = data[0]['Title']  # get the \"Title\"\n",
    "            output[p] = title\n",
    "        else:\n",
    "            output[p] = \"\"\n",
    "    return output\n",
    "\n",
    "def create_database(df_init):\n",
    "    # Init\n",
    "    df = df_init.copy()\n",
    "    \n",
    "    # Keep columns\n",
    "    to_keep = [\n",
    "        'ENTREPRISE_ID',\n",
    "        'COMPTE_ID',\n",
    "        'ID',\n",
    "        'NAME',\n",
    "        'CATEGORY_ID',\n",
    "        'CATEGORY_NAME',\n",
    "        'PAYMENTDATE',\n",
    "        'PROJECTS',\n",
    "        'ISCASHINFLOW',\n",
    "        'AMOUNT_VALUE',\n",
    "        'AMOUNT_CURRENCY',\n",
    "    ]\n",
    "    df = df[to_keep]\n",
    "    \n",
    "    # Rename columns\n",
    "    to_rename = {\n",
    "        \"ID\": \"TRANSACTION_ID\",\n",
    "        \"NAME\": \"TRANSACTION_NAME\",\n",
    "        \"AMOUNT_CURRENCY\": \"CURRENCY\"\n",
    "    }\n",
    "    df = df.rename(columns=to_rename)\n",
    "    \n",
    "    # Get Projet name\n",
    "    projects = get_projects_dict(df)\n",
    "    df[\"PROJECTS\"] = df[\"PROJECTS\"].astype(str).map(projects)\n",
    "    \n",
    "    # Transform payment date\n",
    "    df[\"DATE_ORDER\"] = df[\"PAYMENTDATE\"].astype(int) / 1000\n",
    "    df[\"PAYMENTDATE\"] = pd.to_datetime((df[\"PAYMENTDATE\"].astype(int) / 1000), unit=\"s\").dt.tz_localize('UTC').dt.tz_convert('Europe/Paris')\n",
    "    df[\"DATE\"] = df[\"PAYMENTDATE\"].dt.strftime(\"%d/%m/%Y\")\n",
    "    \n",
    "    # Create VALUE column with sign\n",
    "    df[\"VALUE\"] = df.apply(lambda row: row[\"AMOUNT_VALUE\"] * (-1) if not row[\"ISCASHINFLOW\"] else row[\"AMOUNT_VALUE\"], axis=1)\n",
    "    \n",
    "    # Drop useless columns\n",
    "    to_drop = [\n",
    "        \"PAYMENTDATE\",\n",
    "        \"ISCASHINFLOW\",\n",
    "        \"AMOUNT_VALUE\"\n",
    "    ]\n",
    "    df = df.drop(to_drop, axis=1)\n",
    "    return df.reset_index(drop=True)\n",
    "\n",
    "df_output = create_database(df)\n",
    "print(f\"DB: {len(df_output)} rows, {len(df_output.columns)} columns\")\n",
    "df_output.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6141397b-77fc-460e-bd8b-d205f99915b6",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-09-05T13:05:30.218110Z",
     "iopub.status.busy": "2023-09-05T13:05:30.217869Z",
     "iopub.status.idle": "2023-09-05T13:05:30.461970Z",
     "shell.execute_reply": "2023-09-05T13:05:30.461276Z",
     "shell.execute_reply.started": "2023-09-05T13:05:30.218079Z"
    },
    "papermill": {},
    "tags": []
   },
   "source": [
    "## Output"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "93ee88ca-0202-4142-b6d5-e9e12797df96",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "### Save export to CSV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4c85cdcd-997a-485e-9f6a-46ffed3513b3",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "outputs": [],
   "source": [
    "df_output.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": "3d1b2dd49f1d4066bac81a72f725227fdfce1e44d69e304ec9dfae39cbfdbe4d",
   "notebook_path": "Agicap/Agicap_Get_transactions_by_account.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
}