jobs/notebook-report/monthly/reconciliation_summary.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false,
    "editable": true,
    "jupyter": {
     "outputs_hidden": false
    },
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "source": [
    "# Reconciliation Monthly Stats"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "source": [
    "We need to load in these libraries into our notebook in order to query, load, manipulate and view the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "editable": true,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "is_executing": false,
     "name": "#%%\n"
    },
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "import base64\n",
    "from config import Config\n",
    "from datetime import datetime, timezone, timedelta\n",
    "import os\n",
    "import requests\n",
    "from IPython import get_ipython\n",
    "from IPython.display import display, Markdown\n",
    "\n",
    "%load_ext sql\n",
    "%config SqlMagic.displaylimit = 5"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": [
     "parameters"
    ]
   },
   "source": [
    "# Parameters cell for external parameters via papermill (job running this notebook will insert a parameter cell below this). This cell has a tag of with the name \"parameters\" that is used by papermill\n",
    "\n",
    "e.g.\n",
    "param1 = \"some_value\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "source": [
    "This will create the connection to the database and prep the jupyter magic for SQL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "editable": true,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "is_executing": false,
     "name": "#%%\n"
    },
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "%sql $Config.SQLALCHEMY_DATABASE_URI"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "source": [
    "Simplest query to run to ensure our libraries are loaded and our DB connection is working"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "is_executing": false,
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "set time zone 'UTC';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Query ..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "%%sql monthly_reconciliation_summary  <<\n",
    "SELECT\n",
    "    id,\n",
    "    (created_on AT TIME ZONE 'UTC' AT TIME ZONE 'America/Vancouver')::date AS created_date,\n",
    "    total,\n",
    "    service_fees,\n",
    "    payment_method_code,\n",
    "    corp_type_code,\n",
    "    created_by,\n",
    "    payment_date\n",
    "FROM\n",
    "    invoices\n",
    "WHERE\n",
    "    corp_type_code = :partner_code\n",
    "    AND total > 0\n",
    "    AND invoice_status_code = 'PAID'\n",
    "    AND payment_method_code in ('PAD','EJV')\n",
    "    AND created_on AT TIME ZONE 'UTC' AT TIME ZONE 'America/Vancouver'  > (current_date - 1 - interval '1 months')::date\n",
    "    AND created_on AT TIME ZONE 'UTC' AT TIME ZONE 'America/Vancouver' <= (current_date - 1)::date\n",
    "ORDER BY\n",
    "    1;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Save to CSV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "filename_summary = os.path.join(os.getcwd(), r'data/')+partner_code+'_monthly_reconciliation_summary_' + datetime.strftime(datetime.now()-timedelta(1), '%Y-%m') +'.csv'\n",
    "df_summary = monthly_reconciliation_summary.DataFrame()\n",
    "with open(filename_summary, 'w') as f:\n",
    "    f.write('Monthly Reconciliation Summary:\\n\\n')\n",
    "    if df_summary.empty:\n",
    "        f.write('No Data Retrieved')\n",
    "    else:\n",
    "        df_summary.to_csv(f, sep=',', encoding='utf-8', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "disbursement summary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Define the partner codes for which the disbursement summary should be executed\n",
    "partners_for_disbursement_summary = ['CSO', 'VS']\n",
    "\n",
    "print(f\"Processing for partner_code: {partner_code}\")\n",
    "print(f\"Partners for disbursement summary: {partners_for_disbursement_summary}\")\n",
    "\n",
    "if partner_code in partners_for_disbursement_summary:\n",
    "    print(f\"Partner code {partner_code} found in the list, executing SQL query.\")\n",
    "    query = f\"\"\"\n",
    "    SELECT id, (disbursement_date AT TIME ZONE 'UTC' AT TIME ZONE 'America/Vancouver')::date, total, service_fees, payment_method_code, corp_type_code,created_by\n",
    "    FROM invoices\n",
    "    WHERE corp_type_code = :partner_code\n",
    "    AND invoice_status_code = 'PAID'\n",
    "    AND payment_method_code in ('PAD','EJV')\n",
    "    AND disbursement_status_code = 'COMPLETED'\n",
    "    AND disbursement_date AT TIME ZONE 'UTC' AT TIME ZONE 'America/Vancouver'  > (current_date - 1 - interval '1 months'- interval '5 days')::date\n",
    "    AND disbursement_date AT TIME ZONE 'UTC' AT TIME ZONE 'America/Vancouver'  <= (current_date - 1)::date\n",
    "    order by 1;\n",
    "    \"\"\"\n",
    "\n",
    "    display(Markdown(f\"## Running query for partner: {partner_code}\"))\n",
    "    results = get_ipython().run_cell_magic('sql', '', query)\n",
    "    monthly_reconciliation_disbursed = results.DataFrame()  # Convert the results to a DataFrame for later use\n",
    "else:\n",
    "    print(f\"Partner code {partner_code} not in the list, skipping SQL query.\")\n",
    "    monthly_reconciliation_disbursed = None"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Save to another CSV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "if monthly_reconciliation_disbursed is not None and not monthly_reconciliation_disbursed.empty:\n",
    "    filename_disbursed = os.path.join(os.getcwd(), 'data', f'{partner_code}_monthly_reconciliation_disbursed_' + datetime.strftime(datetime.now() - timedelta(1), '%Y-%m-%d') + '.csv')\n",
    "    print(f\"Saving CSV for partner_code: {partner_code} at {filename_disbursed}\")\n",
    "    with open(filename_disbursed, 'w') as f:\n",
    "        f.write('Monthly Reconciliation Disbursed:\\n\\n')\n",
    "        if monthly_reconciliation_disbursed.empty:\n",
    "            f.write('No Data Retrieved')\n",
    "        else:\n",
    "            monthly_reconciliation_disbursed.to_csv(f, sep=',', encoding='utf-8', index=False)\n",
    "else:\n",
    "    print(f\"Partner code {partner_code} not in the list or no data to save, skipping CSV save.\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Authenticate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "payload = \"grant_type=client_credentials\"\n",
    "basic_hash = base64.b64encode(f\"{os.getenv('NOTEBOOK_SERVICE_ACCOUNT_ID')}:{os.getenv('NOTEBOOK_SERVICE_ACCOUNT_SECRET')}\".encode())\n",
    "\n",
    "headers = {\n",
    "  'Content-Type': 'application/x-www-form-urlencoded',\n",
    "  'Authorization': f'Basic {basic_hash.decode()}'\n",
    "}\n",
    "response = requests.request(\"POST\", f\"{os.getenv('JWT_OIDC_ISSUER')}/protocol/openid-connect/token\", headers=headers, data=payload)\n",
    "\n",
    "assert response.status_code == 200\n",
    "notebook_service_account_token = response.json().get('access_token')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "partner_details = {\n",
    "    'CSO': {\n",
    "        \"companyName\": \"Ministry of Justice\",\n",
    "        \"addressLine1\": \"PO Box 9249, Stn Prov Govt\",\n",
    "        \"addressLine2\": \"6th Floor, 850 Burdett Avenue\",\n",
    "        \"city\": \"VICTORIA\",\n",
    "        \"province\": \"BC\",\n",
    "        \"areaCode\": \"V8W 9J2\"\n",
    "    },\n",
    "    'VS': {\n",
    "        \"companyName\": \"Vital Statistics Agency\",\n",
    "        \"addressLine1\": \"PO Box 9657, Stn Prov Govt\",\n",
    "        \"addressLine2\": \"\",\n",
    "        \"city\": \"VICTORIA\",\n",
    "        \"province\": \"BC\",\n",
    "        \"areaCode\": \"V8W 9P3\"\n",
    "    },\n",
    "    'RPT': {\n",
    "        \"companyName\": \"Property Taxation Branch\",\n",
    "        \"addressLine1\": \"Ministry of Provincial Revenue\",\n",
    "        \"addressLine2\": \"4th Floor, 1802 Douglas Street\",\n",
    "        \"city\": \"VICTORIA\",\n",
    "        \"province\": \"BC\",\n",
    "        \"areaCode\": \"V8T 4K6\"\n",
    "    },\n",
    "    'ESRA': {\n",
    "        \"companyName\": \"Ministry of Enviornment\",\n",
    "        \"addressLine1\": \"Environmental Emergencies and Land Remediation\",\n",
    "        \"addressLine2\": \"2nd Floor 10470-152nd Street\",\n",
    "        \"city\": \"SURREY\",\n",
    "        \"province\": \"BC\",\n",
    "        \"areaCode\": \"V3R 0Y3\"\n",
    "    }\n",
    "\n",
    "}\n",
    "\n",
    "def generate_report(partner_code):\n",
    "    API_BASE_URL = os.getenv('REPORT_API_URL', '')\n",
    "    if not API_BASE_URL:\n",
    "        raise ValueError(\"The REPORT_API_URL environment variable is not set or is empty\")\n",
    "\n",
    "    url = API_BASE_URL\n",
    "    headers = {\n",
    "        'Authorization': f'Bearer {notebook_service_account_token}',\n",
    "        'Content-Type': 'application/json',\n",
    "        'Accept': 'application/pdf'\n",
    "    }\n",
    "\n",
    "    query = \"\"\"\n",
    "    SELECT\n",
    "        COUNT(*) AS transaction_count,\n",
    "        SUM(total) AS total,\n",
    "        TO_CHAR(DATE_TRUNC('month',current_date) - INTERVAL '1 month','Month') as month,\n",
    "        corp_type_code\n",
    "    FROM\n",
    "        invoices\n",
    "    WHERE\n",
    "        corp_type_code = :partner_code\n",
    "        AND invoice_status_code = 'PAID'\n",
    "        AND payment_method_code IN ('PAD', 'EJV')\n",
    "        AND DATE_TRUNC('month', created_on AT TIME ZONE 'UTC' AT TIME ZONE 'America/Vancouver') = DATE_TRUNC('month', current_date - INTERVAL '1 month')\n",
    "    GROUP BY\n",
    "        corp_type_code\n",
    "    ORDER BY\n",
    "        month;\n",
    "    \"\"\"\n",
    "\n",
    "    result = %sql $query\n",
    "\n",
    "    print(result)\n",
    "\n",
    "    if result:\n",
    "        df = result.DataFrame()\n",
    "\n",
    "        # Rename columns to match the expected names in the template\n",
    "        df.rename(columns={\n",
    "            'corp_type_code': 'registry',\n",
    "            'transaction_count': 'transCounts',\n",
    "            'total': 'amount'\n",
    "        }, inplace=True)\n",
    "\n",
    "        df['symbol'] = '$'\n",
    "\n",
    "        df['amount'] = df['amount'].apply(lambda x: f\"{x:.2f}\")\n",
    "\n",
    "        # Convert DataFrame to JSON-compatible format\n",
    "        tableRows = df.to_dict(orient='records')\n",
    "\n",
    "        current_date = datetime.now(tz=timezone.utc).strftime(\"%B %d, %Y\")\n",
    "\n",
    "        details = partner_details.get(partner_code, {})\n",
    "        if not details:\n",
    "            raise ValueError(f\"No details found for partner code: {partner_code}\")\n",
    "\n",
    "        # Define the request body\n",
    "        data = {\n",
    "            \"templateVars\": {\n",
    "                \"date\": current_date,\n",
    "                \"companyName\": details[\"companyName\"],\n",
    "                \"addressLine1\": details[\"addressLine1\"],\n",
    "                \"addressLine2\": details[\"addressLine2\"],\n",
    "                \"city\": details[\"city\"],\n",
    "                \"province\": details[\"province\"],\n",
    "                \"areaCode\": details[\"areaCode\"],\n",
    "                \"firstName\": partner_code,\n",
    "                \"enterMonth\": df['month'][0] if not df.empty else \"N/A\",\n",
    "                \"tableRows\": tableRows\n",
    "            },\n",
    "            \"templateName\": \"revenue_letter\",\n",
    "            \"reportName\": \"revenue_letter\"\n",
    "        }\n",
    "\n",
    "        response = requests.post(url, headers=headers, json=data)\n",
    "\n",
    "        if response.status_code == 200:\n",
    "            pdf_content = response.content\n",
    "\n",
    "            pdf_filename = os.path.join(os.getcwd(), 'data', f'{partner_code}_revenue_letter.pdf')\n",
    "            with open(pdf_filename, 'wb') as pdf_file:\n",
    "                pdf_file.write(pdf_content)\n",
    "\n",
    "            print(\"PDF report saved successfully as 'payment_receipt.pdf'\")\n",
    "        else:\n",
    "            print('Failed to get the report:', response.text)\n",
    "    else:\n",
    "        print('No results returned from the SQL query')\n",
    "generate_report(partner_code)"
   ]
  }
 ],
 "metadata": {
  "celltoolbar": "Tags",
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.11.4"
  },
  "pycharm": {
   "stem_cell": {
    "cell_type": "raw",
    "metadata": {
     "collapsed": false
    },
    "source": []
   }
  },
  "vscode": {
   "interpreter": {
    "hash": "fcb35bce15c55b4cacb5112e543368f86c7f98ed17acd45e6841ee83ed1df6e3"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}