-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathdeviations
169 lines (125 loc) · 8.45 KB
/
deviations
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
Sub Desviaciones()
Dim hsrQuery As DAO.Recordset
Dim hsrString1 As String
Dim hsrString2 As String
Dim hsrString As String
Set hsrQuery = CurrentDb.OpenRecordset("hsr")
Dim rsQuery As DAO.Recordset
Dim rsString As String
Dim tempString As String
Dim employeeQuery As DAO.Recordset
Dim employeeString As String
Dim testQuery As DAO.Recordset
Dim testString As String
Dim excepcionesQuery As DAO.Recordset
Dim excepcionesString As String
Dim myTempString As String
'workbook where the data is to be pasted
Dim wbTarget As Workbook
Dim wsTarget As Worksheet
Dim myInt As Integer
myInt = 2
'open a workbook that has same name as the sheet name
Set wbTarget = Workbooks.Open("C:\Users\clcardensi\Desktop\myExcelJob.xlsx")
Set wsTarget = wbTarget.Worksheets("Hoja1")
wsTarget.Cells(1, 1).Value = "userId"
wsTarget.Cells(1, 2).Value = "FirstName"
wsTarget.Cells(1, 3).Value = "LastName"
wsTarget.Cells(1, 4).Value = "JobID"
wsTarget.Cells(1, 5).Value = "JobName"
wsTarget.Cells(1, 6).Value = "Position"
wsTarget.Cells(1, 7).Value = "Location"
wsTarget.Cells(1, 8).Value = "ERD"
wsTarget.Cells(1, 9).Value = "Business_Role"
wsTarget.Cells(1, 10).Value = "LineManager"
wsTarget.Cells(1, 11).Value = "Justificacion"
'wsTarget.Cells(1, 12).Value = hsrString
Set rsQuery = CurrentDb.OpenRecordset("select distinct pramjobs.user_id from pramjobs inner join employee on pramjobs.user_id = employee.user_id")
If rsQuery.RecordCount <> 0 Then
rsQuery.MoveFirst
While Not rsQuery.EOF
employeeString = "select distinct employee.user_id,employee.location,employee.first_name,employee.last_name,line_manager,jobName,jobid,employee.position from employee where employee.user_id = '" & rsQuery("user_id") & "'"
Debug.Print employeeString
Set employeeQuery = CurrentDb.OpenRecordset(employeeString)
'SE ELIMINAN LOS ROLES 2,3,4,5,6,7
hsrString1 = " select algo2.erd,business_role,justificacion from " & _
" ( " & _
" select pramjobs.erd,pramjobs.business_role from " & _
" (select distinct pramjobs.erd,pramjobs.business_role from pramjobs inner join hsr on pramjobs.erd = hsr.erd where (mid(pramjobs.erd,7,1) <> '2' and mid(pramjobs.erd,7,1) <> '3' and mid(pramjobs.erd,7,1) <> '4' and mid(pramjobs.erd,7,1) <> '6') and pramjobs.user_id = '" & rsQuery("user_id") & "' and (cluster like '*Chile*' or cluster like '*Bolivia*' or cluster like '*NPDI*') ) as myResult1 " & _
" Left Join " & _
" (select erd from hsr where jobId = '" & employeeQuery("jobid") & "') as myResult2 " & _
" on myResult1.erd = myResult2.erd " & _
" where myResult2.erd Is Null " & _
" ) as algo " & _
" inner Join " & _
" (select justificacion,erd from excepciones where usuaria = '" & rsQuery("user_id") & "' ) as algo2 " & _
" on algo.erd = algo2.erd " & _
" Union ALL " & _
" select algo2.erd,business_role,justificacion from " & _
" ( " & _
" select pramjobs.erd,pramjobs.business_role from " & _
" (select distinct pramjobs.erd,pramjobs.business_role from pramjobs inner join hsr on pramjobs.erd = hsr.erd where (mid(pramjobs.erd,7,1) <> '2' and mid(pramjobs.erd,7,1) <> '3' and mid(pramjobs.erd,7,1) <> '4' and mid(pramjobs.erd,7,1) <> '6') and pramjobs.user_id = '" & rsQuery("user_id") & "' and (cluster like '*Chile*' or cluster like '*Bolivia*' or cluster like '*NPDI*') ) as myResult1 " & _
" Left Join " & _
" (select erd from hsr where jobId = '" & employeeQuery("jobid") & "') as myResult2 " & _
" on myResult1.erd = myResult2.erd " & _
" where myResult2.erd Is Null " & _
" ) as algo " & _
" Left Join " & _
" (select justificacion,erd from excepciones where usuaria = '" & rsQuery("user_id") & "' ) as algo2 "
hsrString2 = " on algo.erd = algo2.erd " & _
" where algo2.erd Is Null " & _
" Union ALL " & _
" select algo2.erd,business_role,justificacion from " & _
" ( " & _
" select pramjobs.erd,pramjobs.business_role from " & _
" (select distinct pramjobs.erd,pramjobs.business_role from pramjobs inner join hsr on pramjobs.erd = hsr.erd where (mid(pramjobs.erd,7,1) <> '2' and mid(pramjobs.erd,7,1) <> '3' and mid(pramjobs.erd,7,1) <> '4' and mid(pramjobs.erd,7,1) <> '6') and pramjobs.user_id = '" & rsQuery("user_id") & "' and (cluster like '*Chile*' or cluster like '*Bolivia*' or cluster like '*NPDI*')) as myResult1 " & _
" Left Join " & _
" (select erd from hsr where jobId = '" & employeeQuery("jobid") & "' ) as myResult2 " & _
" on myResult1.erd = myResult2.erd " & _
" where myResult2.erd Is Null " & _
" ) as algo " & _
" Right Join " & _
" (select justificacion,erd from excepciones where usuaria = '" & rsQuery("user_id") & "' ) as algo2 " & _
" on algo.erd = algo2.erd " & _
" where algo.erd Is Null "
hsrString = hsrString1 & hsrString2
'"select pramjobs.erd,pramjobs.business_role from " & _
' " (select distinct pramjobs.erd,pramjobs.business_role from pramjobs inner join hsr on pramjobs.erd = hsr.erd where (mid(pramjobs.erd,7,1) <> '2' and mid(pramjobs.erd,7,1) <> '3' and mid(pramjobs.erd,7,1) <> '4' and mid(pramjobs.erd,7,1) <> '5' and mid(pramjobs.erd,7,1) <> '6') and pramjobs.user_id = '" & rsQuery("user_id") & "') as myResult1 " & _
' " Left Join " & _
' " (select erd from hsr where jobId = '" & employeeQuery("jobid") & "'" & ") as myResult2 " & _
' " on myResult1.erd = myResult2.erd " & _
' " where myResult2.erd Is Null "
Debug.Print hsrString
Set hsrQuery = CurrentDb.OpenRecordset(hsrString)
'Debug.Print rsQuery("user_id")
'Debug.Print employeeQuery("jobName")
'employeeQuery ("Location")
If hsrQuery.RecordCount <> 0 Then
hsrQuery.MoveFirst
While Not hsrQuery.EOF
'myCell = "A" & myInt
wsTarget.Cells(myInt, 1).Value = employeeQuery("user_id")
wsTarget.Cells(myInt, 2).Value = employeeQuery("first_name")
wsTarget.Cells(myInt, 3).Value = employeeQuery("last_name")
wsTarget.Cells(myInt, 4).Value = employeeQuery("jobId")
wsTarget.Cells(myInt, 5).Value = employeeQuery("jobName")
wsTarget.Cells(myInt, 6).Value = employeeQuery("position")
wsTarget.Cells(myInt, 7).Value = employeeQuery("location")
wsTarget.Cells(myInt, 8).Value = hsrQuery("erd")
wsTarget.Cells(myInt, 9).Value = hsrQuery("business_role")
wsTarget.Cells(myInt, 10).Value = employeeQuery("line_manager")
wsTarget.Cells(myInt, 11).Value = hsrQuery("justificacion")
'wsTarget.Cells(myInt, 12).Value = hsrString
'Debug.Print myCell
myInt = myInt + 1
hsrQuery.MoveNext
Wend
End If
hsrQuery.Close
Set hsrQuery = Nothing
rsQuery.MoveNext
Wend
End If
rsQuery.Close
Set rsQuery = Nothing
End Sub