{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Create a Word Document\n",
    "Let's imagine in this hypothetical situation, a client sends the billing group a note saying that the client is undergoing a review of their legal fees and needs the supplied document completed for each invoice of theirs for the next 12 months. You could handle that in a number of ways, but since you're getting so good at Python you wonder - can I do that? Let's take a look.\n",
    "\n",
    "See the file in this folder called `client_supplied_LegalFeesSheet`. This is what the client wants and it must be in that form. We'll take a copy and mark it up filling it in with data from 3E. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os \n",
    "import sys\n",
    "import pyodbc\n",
    "elite_db_server = os.environ['ELITE_PROD_HOST']\n",
    "elite_db = os.environ['ELITE_PROD_DB']\n",
    "elite_user = os.environ['ELITE_RO_NAME']\n",
    "elite_pass = os.environ['ELITE_RO_PASS']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn_str = (f'DRIVER={{FreeTDS}};SERVER={elite_db_server};'\n",
    "            f'PORT=1433;DATABASE={elite_db};UID={elite_user};'\n",
    "            f'PWD={elite_pass};TDS_Version=8.0;ClientCharset=UTF-8')\n",
    "conn_3e = pyodbc.connect(conn_str)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We look at the data they are requesting and build some sql statements to get the data. Depending on how you use 3E, these may be altered slightly for your situation.\n",
    "\n",
    "The first two sections `Matter` and `This Invoice` we can do with a simple query."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql_invoice = \"\"\"\n",
    "select \n",
    "InvMaster.InvNumber,\n",
    "InvMaster.InvDate,\n",
    "InvMaster.OrgFee,\n",
    "isnull(InvMaster.OrgSCo,0) + isnull(InvMaster.OrgHCo,0) 'OrgCosts',\n",
    "InvMaster.OrgTax,\n",
    "Matter.Number,\n",
    "Matter.DisplayName, \n",
    "Matter.OpenDate\n",
    "\n",
    "from ProfMaster\n",
    "join InvMaster on InvMaster.InvIndex = ProfMaster.InvMaster\n",
    "join Matter on Matter.MattIndex = ProfMaster.LeadMatter\n",
    "\n",
    "where InvMaster.InvNumber = ?\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "invoice_number = '2019123456'    # enter an invoice number\n",
    "cursor = conn_3e.cursor()\n",
    "invoice_results = cursor.execute(sql_invoice, (invoice_number,))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Before we go too far, in the [Interacting with SQL](Interacting%20with%20SQL.ipynb) notebook I mentioned converting the results to a dictionary. Let's use such a function here."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def results_to_dict(results):\n",
    "    columns = [column[0] for column in results.description]\n",
    "    records = []\n",
    "    for row in results.fetchall():\n",
    "        records.append(dict(zip(columns, row)))\n",
    "    return records"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "invoice_detail = results_to_dict(invoice_results)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "invoice_detail"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Great, now lets get that little summary of timekeepers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql_fee_summary = \"\"\"\n",
    "select\n",
    "Title.Description 'Title',\n",
    "timekeeper.BillName,\n",
    "sum(ProfDetail.PresHrs) 'SumPresHours',\n",
    "ProfDetail.PresRate\n",
    "\n",
    "from ProfMaster\n",
    "join ProfDetail on ProfDetail.ProfMaster = ProfMaster.ProfIndex\n",
    "join ProfDetailTime on ProfDetail.ProfDetailID = ProfDetailTime.ProfDetailTimeID\n",
    "join Timekeeper on ProfDetail.PresTimekeeper = Timekeeper.TkprIndex\n",
    "join TkprDate on TkprDate.TimekeeperLkUp = Timekeeper.TkprIndex\n",
    "    and ProfDetail.WorkDate between TkprDate.NxStartDate and TkprDate.NxEndDate\n",
    "join Title on Title.Code = TkprDate.Title\n",
    "join BaseAppSetup TitleBase on TitleBase.BaseAppSetupID = Title.TitleID\n",
    "where ProfDetail.IsDisplay = 1\n",
    "and ProfMaster.InvNumber = ?\n",
    "\n",
    "group by TitleBase.SortString, Title.Description, Timekeeper.BillName,  ProfDetail.PresRate\n",
    "order by TitleBase.SortString, sum(ProfDetail.PresHrs)*ProfDetail.PresRate desc \"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor = conn_3e.cursor()\n",
    "fee_summary_results = cursor.execute(sql_fee_summary, (invoice_number,))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fee_summary_details = results_to_dict(fee_summary_results)\n",
    "fee_summary_details"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Okay, so now, like in the earlier Notebooks we need to install a library to help us out. This time it is called `docxtmp`. Take a look at the [documentation](https://docxtpl.readthedocs.io/en/latest/) for an indepth look at this little libary. \n",
    "\n",
    "We're going to take the form given to us by the client and mark it up so it will work for us. See that marked up version as `LegalFeesSheet.docx`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "!{sys.executable} -m pip install docxtpl"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from docxtpl import DocxTemplate\n",
    "\n",
    "doc = DocxTemplate(\"LegalFeesSheet.docx\")\n",
    "context = {'invoice':invoice_detail[0],\n",
    "           'fees': fee_summary_details, }\n",
    "doc.render(context)\n",
    "doc.save(\"generated_LegalFeesSheet.docx\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, if you look in your folder where you are saving these notebooks, there should be a `generated_LegalFeesSheet.docx` file with results.\n",
    "\n",
    "## Conclusion\n",
    "In this Notebook, we've set up a connection to our Elite database server, run some SQL queries and output the results to a Word Document (docx). \n",
    "\n",
    "-30-"
   ]
  }
 ],
 "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.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}