Active_prgs/Evidox/EvidoxTaskListLib.py

"""

EvidoxTaskListLib

Created by:
Emanuel Borges
09.07.2017

A class for interacting with the taskList.

"""

import win32com.client
from collections import namedtuple

class TaskListDBConnection:
    def __init__(self, accessDB):
        daoEngine = win32com.client.Dispatch('DAO.DBEngine.120')
        self.daoDB = daoEngine.OpenDatabase(r'C:\Users\eborges\Desktop\TaskList.accdb')
        self.empMatrix = self._UpdateEmployeeMatrix()

    def CloseTaskListConnection(self):
        """Closes the entire connection and not just the RS"""
        self.daoDB.Close()

    def _UpdateEmployeeMatrix(self):
        """Internal method that updates the empMatrix. Simple ID = Firstname Lastname"""
        empMatrix = {}
        daoRSObj = self.daoDB.OpenRecordset("SELECT * FROM [Employees] ")
        daoRSObj.MoveLast()
        fullCount = daoRSObj.RecordCount
        daoRSObj.MoveFirst()
        for i in range(fullCount):
            empMatrix[daoRSObj.Fields('ID').Value] = "%s %s"% (daoRSObj.Fields('Firstname').Value,daoRSObj.Fields('Lastname').Value)
            daoRSObj.MoveNext()
        daoRSObj.Close()
        return empMatrix

    def GetOpenAndClosedJobs(self, clmNumb):
        """Returns a named tuple of the open and closed jobs for that clmNumb"""
        jobsDict = {}
        ds = namedtuple('job_number',['task_type','closed_date','assigned_to','description'])
        daoRSObj = self.daoDB.OpenRecordset("SELECT * FROM [Task List] WHERE clientmatterID = %s "% clmNumb)
        ##  First check to see if records were returned.
        if daoRSObj.EOF:
            print "No records were returned!"
        else:
            daoRSObj.MoveLast()
            fullCount = daoRSObj.RecordCount
            daoRSObj.MoveFirst()
            for i in range(fullCount):
                jobsDict[daoRSObj.Fields('Jobnumber').Value] = ds(daoRSObj.Fields('TaskType').Value,
                                                                  daoRSObj.Fields('DateCompleted').Value,
                                                                  daoRSObj.Fields('AssignedTo').Value,
                                                                  daoRSObj.Fields('Task').Value)
                daoRSObj.MoveNext()
        daoRSObj.Close()
        return jobsDict

    def GetOpenJobs(self, clmNumb):
        """Returns a named tuple of the open jobs for that clmNumb"""
        jobsDict = {}
        ds = namedtuple('job_number',['task_type','closed_date','assigned_to','description'])
        daoRSObj = self.daoDB.OpenRecordset("SELECT * FROM [Task List] WHERE clientmatterID = %s and DateCompleted IS NULL "% clmNumb)
        ##  First check to see if records were returned.
        if daoRSObj.EOF:
            print "No records were returned!"
        else:
            daoRSObj.MoveLast()
            fullCount = daoRSObj.RecordCount
            daoRSObj.MoveFirst()
            for i in range(fullCount):
                jobsDict[daoRSObj.Fields('Jobnumber').Value] = ds(daoRSObj.Fields('TaskType').Value,
                                                                  daoRSObj.Fields('DateCompleted').Value,
                                                                  daoRSObj.Fields('AssignedTo').Value,
                                                                  daoRSObj.Fields('Task').Value)
                daoRSObj.MoveNext()
        daoRSObj.Close()
        return jobsDict

    def GetClosedJobs(self, clmNumb):
        """Returns a named tuple of the closed jobs for that clmNumb"""
        jobsDict = {}
        ds = namedtuple('job_number',['task_type','closed_date','assigned_to','description'])
        daoRSObj = self.daoDB.OpenRecordset("SELECT * FROM [Task List] WHERE clientmatterID = %s and DateCompleted IS NOT NULL "% clmNumb)
        ##  First check to see if records were returned.
        if daoRSObj.EOF:
            print "No records were returned!"
        else:
            daoRSObj.MoveLast()
            fullCount = daoRSObj.RecordCount
            daoRSObj.MoveFirst()
            for i in range(fullCount):
                #print "on %s"%daoRSObj.Fields('Jobnumber').Value
                jobsDict[daoRSObj.Fields('Jobnumber').Value] = ds(daoRSObj.Fields('TaskType').Value,
                                                                  daoRSObj.Fields('DateCompleted').Value,
                                                                  daoRSObj.Fields('AssignedTo').Value,
                                                                  daoRSObj.Fields('Task').Value)
                daoRSObj.MoveNext()
        daoRSObj.Close()
        return jobsDict

    def GetClientContacts(self, clientID):
        """Returns a named tuple of the employees at that client"""
        employeeDict = {}
        ds = namedtuple('full_name',['first_name','last_name', 'middle_initial','title','primary_phone_number', 'secondary_phone_number', 'primary_email'])
        daoRSObj = self.daoDB.OpenRecordset("SELECT * FROM Contacts WHERE [firm/company] = %s"% clientID)
        ##  First check to see if records were returned.
        if daoRSObj.EOF:
            print "No records were returned!"
        else:
            daoRSObj.MoveLast()
            fullCount = daoRSObj.RecordCount
            daoRSObj.MoveFirst()
            for i in range(fullCount):
                ## First, make sure you dont have an empty contact, which I've seen in Access
                if daoRSObj.Fields('Contact').Value:
                    ## First parse the contact name
                    contactName = daoRSObj.Fields('Contact').Value
                    contactNameParts = contactName.split(",")
                    lastName = contactNameParts[0]
                    firstName = contactNameParts[1]
                    firstName = firstName.lstrip()
                    ##  Had to solve for an issue where they are not consistent with the suffix in the first name.
                    if " " in firstName:
                        if len(firstName.split(" ")) > 2:
                            #print firstName.split(" ")
                            firstNameParts = firstName.split(" ")
                            firstName = firstNameParts[1]
                            middleInitial = firstNameParts[2]
                        else:
                            firstName, middleInitial = firstName.split(" ")
                    else:
                        middleInitial = 'None'
                    employeeDict["%s, %s"%(lastName, firstName)] = ds(firstName,
                                                                     lastName,
                                                                     middleInitial,
                                                                     daoRSObj.Fields('Title').Value,
                                                                     daoRSObj.Fields('OfficePhone').Value,
                                                                     daoRSObj.Fields('CellPhone').Value,
                                                                     daoRSObj.Fields('Email').Value)
                    daoRSObj.MoveNext()
        daoRSObj.Close()
        return employeeDict

    def GetActiveReviewDatabases(self, caseID):
        """Returns a named tuple of the active cases for this case"""
        reviewDBDict = {}
        ds = namedtuple('access_table_entryid',['name','client_abbreviation','db_status','tasklist_clmid','storage_location','platform'])
        daoRSObj = self.daoDB.OpenRecordset("SELECT * FROM Turndoxlibrary WHERE [clientmatterid] = %s"% caseID)
        ##  First check to see if records were returned.
        if daoRSObj.EOF:
            print "No records were returned!"
        else:
            daoRSObj.MoveLast()
            fullCount = daoRSObj.RecordCount
            daoRSObj.MoveFirst()
            for i in range(fullCount):
                accessDBStatusField = daoRSObj.Fields('Active').Value
                ##  Only grab the active ones.  one day you will udpate cases but for now there is no need to grab anything other than active.
                if accessDBStatusField.upper() == 'ACTIVE':
                    reviewDBDict[daoRSObj.Fields('ID').Value] = ds(daoRSObj.Fields('Library').Value,
                                                                   daoRSObj.Fields('ClientAbrv').Value,
                                                                   'active',
                                                                   caseID,
                                                                   daoRSObj.Fields('StorageLocation').Value,
                                                                   daoRSObj.Fields('Application').Value)
                daoRSObj.MoveNext()
        daoRSObj.Close()
        return reviewDBDict

    def GetHostingBillings(self, databaseName):
        """Gets all of the hosting billings, as a dict, for a database.  key is accessID"""
        hostingBillingsDict = {}
        ds = namedtuple('access_table_entryid',['billing_date','data_hosting_billed','user_licenses_billed'])
        daoRSObj = self.daoDB.OpenRecordset("SELECT * FROM MonthlyBilling WHERE [library] = '%s'"% databaseName)
        ##  First check to see if records were returned.
        if daoRSObj.EOF:
            print "No records were returned!"
        else:
            daoRSObj.MoveLast()
            fullCount = daoRSObj.RecordCount
            daoRSObj.MoveFirst()
            for i in range(fullCount):
                hostingBillingsDict[daoRSObj.Fields('ID').Value] = ds(daoRSObj.Fields('BillingMonth').Value,
                                                               daoRSObj.Fields('GbHosted').Value,
                                                               daoRSObj.Fields('NumUsers').Value)
                daoRSObj.MoveNext()
        daoRSObj.Close()
        return hostingBillingsDict


    def GetCaseInformation(self, caseID):
        """Returns a named tuple with information for this case for syncing the case info"""
        caseInfoDict = {}
        ds = namedtuple('client_matter_id',['client_id','client_matter_folder'])
        daoRSObj = self.daoDB.OpenRecordset("SELECT * FROM ClientMatterTbl WHERE [id] = %s"% caseID)
        ##  First check to see if records were returned.
        if daoRSObj.EOF:
            print "No records were returned!"
        else:
            daoRSObj.MoveLast()
            fullCount = daoRSObj.RecordCount
            daoRSObj.MoveFirst()
            for i in range(fullCount):
                caseInfoDict[daoRSObj.Fields('ID').Value] = ds(daoRSObj.Fields('Client').Value, daoRSObj.Fields('EvidoxCMFolder').Value)
                daoRSObj.MoveNext()
        daoRSObj.Close()
        return caseInfoDict
Revision:	666
Committed:	Thu Dec 12 21:18:56 2019 UTC (6 years, 3 months ago) by nino.borges
Content type:	text/x-python
File size:	10971 byte(s)
Log Message:	small changes
#	Content
1	"""
2
3	EvidoxTaskListLib
4
5	Created by:
6	Emanuel Borges
7	09.07.2017
8
9	A class for interacting with the taskList.
10
11	"""
12
13	import win32com.client
14	from collections import namedtuple
15
16	class TaskListDBConnection:
17	def __init__(self, accessDB):
18	daoEngine = win32com.client.Dispatch('DAO.DBEngine.120')
19	self.daoDB = daoEngine.OpenDatabase(r'C:\Users\eborges\Desktop\TaskList.accdb')
20	self.empMatrix = self._UpdateEmployeeMatrix()
21
22	def CloseTaskListConnection(self):
23	"""Closes the entire connection and not just the RS"""
24	self.daoDB.Close()
25
26	def _UpdateEmployeeMatrix(self):
27	"""Internal method that updates the empMatrix. Simple ID = Firstname Lastname"""
28	empMatrix = {}
29	daoRSObj = self.daoDB.OpenRecordset("SELECT * FROM [Employees] ")
30	daoRSObj.MoveLast()
31	fullCount = daoRSObj.RecordCount
32	daoRSObj.MoveFirst()
33	for i in range(fullCount):
34	empMatrix[daoRSObj.Fields('ID').Value] = "%s %s"% (daoRSObj.Fields('Firstname').Value,daoRSObj.Fields('Lastname').Value)
35	daoRSObj.MoveNext()
36	daoRSObj.Close()
37	return empMatrix
38
39	def GetOpenAndClosedJobs(self, clmNumb):
40	"""Returns a named tuple of the open and closed jobs for that clmNumb"""
41	jobsDict = {}
42	ds = namedtuple('job_number',['task_type','closed_date','assigned_to','description'])
43	daoRSObj = self.daoDB.OpenRecordset("SELECT * FROM [Task List] WHERE clientmatterID = %s "% clmNumb)
44	## First check to see if records were returned.
45	if daoRSObj.EOF:
46	print "No records were returned!"
47	else:
48	daoRSObj.MoveLast()
49	fullCount = daoRSObj.RecordCount
50	daoRSObj.MoveFirst()
51	for i in range(fullCount):
52	jobsDict[daoRSObj.Fields('Jobnumber').Value] = ds(daoRSObj.Fields('TaskType').Value,
53	daoRSObj.Fields('DateCompleted').Value,
54	daoRSObj.Fields('AssignedTo').Value,
55	daoRSObj.Fields('Task').Value)
56	daoRSObj.MoveNext()
57	daoRSObj.Close()
58	return jobsDict
59
60	def GetOpenJobs(self, clmNumb):
61	"""Returns a named tuple of the open jobs for that clmNumb"""
62	jobsDict = {}
63	ds = namedtuple('job_number',['task_type','closed_date','assigned_to','description'])
64	daoRSObj = self.daoDB.OpenRecordset("SELECT * FROM [Task List] WHERE clientmatterID = %s and DateCompleted IS NULL "% clmNumb)
65	## First check to see if records were returned.
66	if daoRSObj.EOF:
67	print "No records were returned!"
68	else:
69	daoRSObj.MoveLast()
70	fullCount = daoRSObj.RecordCount
71	daoRSObj.MoveFirst()
72	for i in range(fullCount):
73	jobsDict[daoRSObj.Fields('Jobnumber').Value] = ds(daoRSObj.Fields('TaskType').Value,
74	daoRSObj.Fields('DateCompleted').Value,
75	daoRSObj.Fields('AssignedTo').Value,
76	daoRSObj.Fields('Task').Value)
77	daoRSObj.MoveNext()
78	daoRSObj.Close()
79	return jobsDict
80
81	def GetClosedJobs(self, clmNumb):
82	"""Returns a named tuple of the closed jobs for that clmNumb"""
83	jobsDict = {}
84	ds = namedtuple('job_number',['task_type','closed_date','assigned_to','description'])
85	daoRSObj = self.daoDB.OpenRecordset("SELECT * FROM [Task List] WHERE clientmatterID = %s and DateCompleted IS NOT NULL "% clmNumb)
86	## First check to see if records were returned.
87	if daoRSObj.EOF:
88	print "No records were returned!"
89	else:
90	daoRSObj.MoveLast()
91	fullCount = daoRSObj.RecordCount
92	daoRSObj.MoveFirst()
93	for i in range(fullCount):
94	#print "on %s"%daoRSObj.Fields('Jobnumber').Value
95	jobsDict[daoRSObj.Fields('Jobnumber').Value] = ds(daoRSObj.Fields('TaskType').Value,
96	daoRSObj.Fields('DateCompleted').Value,
97	daoRSObj.Fields('AssignedTo').Value,
98	daoRSObj.Fields('Task').Value)
99	daoRSObj.MoveNext()
100	daoRSObj.Close()
101	return jobsDict
102
103	def GetClientContacts(self, clientID):
104	"""Returns a named tuple of the employees at that client"""
105	employeeDict = {}
106	ds = namedtuple('full_name',['first_name','last_name', 'middle_initial','title','primary_phone_number', 'secondary_phone_number', 'primary_email'])
107	daoRSObj = self.daoDB.OpenRecordset("SELECT * FROM Contacts WHERE [firm/company] = %s"% clientID)
108	## First check to see if records were returned.
109	if daoRSObj.EOF:
110	print "No records were returned!"
111	else:
112	daoRSObj.MoveLast()
113	fullCount = daoRSObj.RecordCount
114	daoRSObj.MoveFirst()
115	for i in range(fullCount):
116	## First, make sure you dont have an empty contact, which I've seen in Access
117	if daoRSObj.Fields('Contact').Value:
118	## First parse the contact name
119	contactName = daoRSObj.Fields('Contact').Value
120	contactNameParts = contactName.split(",")
121	lastName = contactNameParts[0]
122	firstName = contactNameParts[1]
123	firstName = firstName.lstrip()
124	## Had to solve for an issue where they are not consistent with the suffix in the first name.
125	if " " in firstName:
126	if len(firstName.split(" ")) > 2:
127	#print firstName.split(" ")
128	firstNameParts = firstName.split(" ")
129	firstName = firstNameParts[1]
130	middleInitial = firstNameParts[2]
131	else:
132	firstName, middleInitial = firstName.split(" ")
133	else:
134	middleInitial = 'None'
135	employeeDict["%s, %s"%(lastName, firstName)] = ds(firstName,
136	lastName,
137	middleInitial,
138	daoRSObj.Fields('Title').Value,
139	daoRSObj.Fields('OfficePhone').Value,
140	daoRSObj.Fields('CellPhone').Value,
141	daoRSObj.Fields('Email').Value)
142	daoRSObj.MoveNext()
143	daoRSObj.Close()
144	return employeeDict
145
146	def GetActiveReviewDatabases(self, caseID):
147	"""Returns a named tuple of the active cases for this case"""
148	reviewDBDict = {}
149	ds = namedtuple('access_table_entryid',['name','client_abbreviation','db_status','tasklist_clmid','storage_location','platform'])
150	daoRSObj = self.daoDB.OpenRecordset("SELECT * FROM Turndoxlibrary WHERE [clientmatterid] = %s"% caseID)
151	## First check to see if records were returned.
152	if daoRSObj.EOF:
153	print "No records were returned!"
154	else:
155	daoRSObj.MoveLast()
156	fullCount = daoRSObj.RecordCount
157	daoRSObj.MoveFirst()
158	for i in range(fullCount):
159	accessDBStatusField = daoRSObj.Fields('Active').Value
160	## Only grab the active ones. one day you will udpate cases but for now there is no need to grab anything other than active.
161	if accessDBStatusField.upper() == 'ACTIVE':
162	reviewDBDict[daoRSObj.Fields('ID').Value] = ds(daoRSObj.Fields('Library').Value,
163	daoRSObj.Fields('ClientAbrv').Value,
164	'active',
165	caseID,
166	daoRSObj.Fields('StorageLocation').Value,
167	daoRSObj.Fields('Application').Value)
168	daoRSObj.MoveNext()
169	daoRSObj.Close()
170	return reviewDBDict
171
172	def GetHostingBillings(self, databaseName):
173	"""Gets all of the hosting billings, as a dict, for a database. key is accessID"""
174	hostingBillingsDict = {}
175	ds = namedtuple('access_table_entryid',['billing_date','data_hosting_billed','user_licenses_billed'])
176	daoRSObj = self.daoDB.OpenRecordset("SELECT * FROM MonthlyBilling WHERE [library] = '%s'"% databaseName)
177	## First check to see if records were returned.
178	if daoRSObj.EOF:
179	print "No records were returned!"
180	else:
181	daoRSObj.MoveLast()
182	fullCount = daoRSObj.RecordCount
183	daoRSObj.MoveFirst()
184	for i in range(fullCount):
185	hostingBillingsDict[daoRSObj.Fields('ID').Value] = ds(daoRSObj.Fields('BillingMonth').Value,
186	daoRSObj.Fields('GbHosted').Value,
187	daoRSObj.Fields('NumUsers').Value)
188	daoRSObj.MoveNext()
189	daoRSObj.Close()
190	return hostingBillingsDict
191
192
193
194	def GetCaseInformation(self, caseID):
195	"""Returns a named tuple with information for this case for syncing the case info"""
196	caseInfoDict = {}
197	ds = namedtuple('client_matter_id',['client_id','client_matter_folder'])
198	daoRSObj = self.daoDB.OpenRecordset("SELECT * FROM ClientMatterTbl WHERE [id] = %s"% caseID)
199	## First check to see if records were returned.
200	if daoRSObj.EOF:
201	print "No records were returned!"
202	else:
203	daoRSObj.MoveLast()
204	fullCount = daoRSObj.RecordCount
205	daoRSObj.MoveFirst()
206	for i in range(fullCount):
207	caseInfoDict[daoRSObj.Fields('ID').Value] = ds(daoRSObj.Fields('Client').Value, daoRSObj.Fields('EvidoxCMFolder').Value)
208	daoRSObj.MoveNext()
209	daoRSObj.Close()
210	return caseInfoDict