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.
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.
import os
import sys
import pyodbc
elite_db_server = os.environ['ELITE_PROD_HOST']
elite_db = os.environ['ELITE_PROD_DB']
elite_user = os.environ['ELITE_RO_NAME']
elite_pass = os.environ['ELITE_RO_PASS']
cursor.close()
conn_str = (f'DRIVER={{FreeTDS}};SERVER={elite_db_server};'
f'PORT=1433;DATABASE={elite_db};UID={elite_user};'
f'PWD={elite_pass};TDS_Version=8.0;ClientCharset=UTF-8')
conn_3e = pyodbc.connect(conn_str)
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.
The first two sections Matter
and This Invoice
we can do with a simple query.
sql_invoice = """
select
InvMaster.InvNumber,
InvMaster.InvDate,
InvMaster.OrgFee,
isnull(InvMaster.OrgSCo,0) + isnull(InvMaster.OrgHCo,0) 'OrgCosts',
InvMaster.OrgTax,
Matter.Number,
Matter.DisplayName,
Matter.OpenDate
from ProfMaster
join InvMaster on InvMaster.InvIndex = ProfMaster.InvMaster
join Matter on Matter.MattIndex = ProfMaster.LeadMatter
where InvMaster.InvNumber = ?
"""
invoice_number = '2019123456' # enter an invoice number
cursor = conn_3e.cursor()
invoice_results = cursor.execute(sql_invoice, (invoice_number,))
Before we go too far, in the Interacting with SQL notebook I mentioned converting the results to a dictionary. Let's use such a function here.
def results_to_dict(results):
columns = [column[0] for column in results.description]
records = []
for row in results.fetchall():
records.append(dict(zip(columns, row)))
return records
invoice_detail = results_to_dict(invoice_results)
invoice_detail
Great, now lets get that little summary of timekeepers.
sql_fee_summary = """
select
Title.Description 'Title',
timekeeper.BillName,
sum(ProfDetail.PresHrs) 'SumPresHours',
ProfDetail.PresRate
from ProfMaster
join ProfDetail on ProfDetail.ProfMaster = ProfMaster.ProfIndex
join ProfDetailTime on ProfDetail.ProfDetailID = ProfDetailTime.ProfDetailTimeID
join Timekeeper on ProfDetail.PresTimekeeper = Timekeeper.TkprIndex
join TkprDate on TkprDate.TimekeeperLkUp = Timekeeper.TkprIndex
and ProfDetail.WorkDate between TkprDate.NxStartDate and TkprDate.NxEndDate
join Title on Title.Code = TkprDate.Title
join BaseAppSetup TitleBase on TitleBase.BaseAppSetupID = Title.TitleID
where ProfDetail.IsDisplay = 1
and ProfMaster.InvNumber = ?
group by TitleBase.SortString, Title.Description, Timekeeper.BillName, ProfDetail.PresRate
order by TitleBase.SortString, sum(ProfDetail.PresHrs)*ProfDetail.PresRate desc """
cursor = conn_3e.cursor()
fee_summary_results = cursor.execute(sql_fee_summary, (invoice_number,))
fee_summary_details = results_to_dict(fee_summary_results)
fee_summary_details
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 for an indepth look at this little libary.
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
.
!{sys.executable} -m pip install docxtpl
from docxtpl import DocxTemplate
doc = DocxTemplate("LegalFeesSheet.docx")
context = {'invoice':invoice_detail[0],
'fees': fee_summary_details, }
doc.render(context)
doc.save("generated_LegalFeesSheet.docx")
Now, if you look in your folder where you are saving these notebooks, there should be a generated_LegalFeesSheet.docx
file with results.
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).
-30-