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.CORP\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:	670
Committed:	Mon Jan 20 22:41:38 2020 UTC (6 years, 2 months ago) by nino.borges
Content type:	text/x-python
File size:	10976 byte(s)
Log Message:	Updates to reflect stuff moved from iad to XDD
#	User	Rev	Content
1	nino.borges	633	"""
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	nino.borges	670	self.daoDB = daoEngine.OpenDatabase(r'C:\Users\eborges.CORP\Desktop\TaskList.accdb')
20	nino.borges	633	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	nino.borges	666	#print "on %s"%daoRSObj.Fields('Jobnumber').Value
95	nino.borges	633	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	nino.borges	666	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