-
Notifications
You must be signed in to change notification settings - Fork 0
/
MiniSod
199 lines (160 loc) · 10.7 KB
/
MiniSod
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
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
Sub MiniSod()
Dim sodQuery As DAO.Recordset
Dim sodString As String
Dim usersQuery As DAO.Recordset
Set usersQuery = CurrentDb.OpenRecordset("select distinct user_id from pramjobs where user_id like 'bo*' or user_id like 'cl*' or user_id like 'pe*' or user_id like 'nn*' ")
'Set usersQuery = CurrentDb.OpenRecordset("select distinct user_id from pramjobs where user_id = 'clgaetee' ")
Dim candidateQuery As DAO.Recordset
Dim candidateString As String
Dim lastCheck As DAO.Recordset
Dim lastCheckInt As Integer
Dim lastCheckString As String
Dim sodNum As Integer
Dim sum As Integer
Dim sArray() As String
Dim i As Integer
Dim wbTarget As Workbook
Dim wsTarget As Worksheet
Dim MyInt As Integer
rowNumber = 1
'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")
Dim Cases As Integer
If usersQuery.RecordCount <> 0 Then
usersQuery.MoveFirst
While Not usersQuery.EOF
sodString = " SELECT DISTINCT ERD FROM (" & _
" SELECT distinct erd from pramjobs inner join sod on pramjobs.erd = sod.erd1 where ( user_id = '" & usersQuery("user_id") & "' and mid(erd,7,1) <> '3' and mid(erd,7,1) <> '2') and (cluster like '*Chile*' or cluster like '*Bolivia*' ) " & _
" UNION ALL " & _
" SELECT distinct erd from pramjobs inner join sod on pramjobs.erd = sod.erd2 where ( user_id = '" & usersQuery("user_id") & "' and mid(erd,7,1) <> '3' and mid(erd,7,1) <> '2') and (cluster like '*Chile*' or cluster like '*Bolivia*' ) " & _
" UNION ALL " & _
" SELECT distinct erd from pramjobs inner join sod on pramjobs.erd = sod.erd3 where ( user_id = '" & usersQuery("user_id") & "' and mid(erd,7,1) <> '3' and mid(erd,7,1) <> '2') and (cluster like '*Chile*' or cluster like '*Bolivia*' ) " & _
" UNION ALL " & _
" SELECT distinct erd from pramjobs inner join sod on pramjobs.erd = sod.erd4 where ( user_id = '" & usersQuery("user_id") & "' and mid(erd,7,1) <> '3' and mid(erd,7,1) <> '2') and (cluster like '*Chile*' or cluster like '*Bolivia*' ) " & _
" UNION ALL " & _
" SELECT distinct erd from pramjobs inner join sod on pramjobs.erd = sod.erd5 where ( user_id = '" & usersQuery("user_id") & "' and mid(erd,7,1) <> '3' and mid(erd,7,1) <> '2') and (cluster like '*Chile*' or cluster like '*Bolivia*' ) " & _
" UNION ALL " & _
" SELECT distinct erd from pramjobs inner join sod on pramjobs.erd = sod.erd6 where ( user_id = '" & usersQuery("user_id") & "' and mid(erd,7,1) <> '3' and mid(erd,7,1) <> '2') and (cluster like '*Chile*' or cluster like '*Bolivia*' ) " & _
" ) "
Debug.Print usersQuery("user_id")
Set sodQuery = CurrentDb.OpenRecordset(sodString)
If sodQuery.RecordCount <> 0 Then
sodQuery.MoveFirst
While Not sodQuery.EOF
candidateString = "select * from sod where erd1 = '" & sodQuery("ERD") & "'"
Set candidateQuery = CurrentDb.OpenRecordset(candidateString)
If candidateQuery.RecordCount <> 0 Then
candidateQuery.MoveFirst
While Not candidateQuery.EOF
sum = 0
lastCheckInt = 1
If candidateQuery("roleId1") <> "" Then
sum = sum + 1
End If
If candidateQuery("roleId2") <> "" Then
sum = sum + 1
End If
If candidateQuery("roleId3") <> "" Then
sum = sum + 1
End If
If candidateQuery("roleId4") <> "" Then
sum = sum + 1
End If
If candidateQuery("roleId5") <> "" Then
sum = sum + 1
End If
If candidateQuery("roleId6") <> "" Then
sum = sum + 1
End If
'Debug.Print usersQuery("user_id") & " " & sodQuery("ERD") & " " & candidateQuery("roleId1") & " " & candidateQuery("roleId2") & " " & candidateQuery("roleId3") & " " & candidateQuery("roleId4") & " " & candidateQuery("roleId5") & " " & candidateQuery("roleId6") & " " & sum
ReDim sArray(sum)
While lastCheckInt <= sum
sodNum = 1
lastCheckString = "select distinct erd from pramjobs where user_id = '" & usersQuery("user_id") & "' and erd = 'ERD000" & candidateQuery("roleId" & CStr(lastCheckInt)) & "'"
Set lastCheck = CurrentDb.OpenRecordset(lastCheckString)
'Debug.Print lastCheckString
If lastCheck.RecordCount <> 0 Then
lastCheck.MoveFirst
sArray(lastCheckInt - 1) = lastCheck("ERD")
sodNum = sodNum + 1
'Debug.Print lastCheck("erd")
End If
lastCheckInt = lastCheckInt + 1
Wend
If sodNum = sum Then
If sum = 2 Then
While i <= sum
wsTarget.Cells(rowNumber, 1).Value = usersQuery("user_id")
wsTarget.Cells(rowNumber, 2).Value = sArray(0)
wsTarget.Cells(rowNumber, 3).Value = sArray(1)
Debug.Print "SOD FOUND: " & sodQuery("ERD")
i = i + 1
Wend
End If
If sum = 3 Then
While i <= sum
wsTarget.Cells(rowNumber, 1).Value = usersQuery("user_id")
wsTarget.Cells(rowNumber, 2).Value = sArray(0)
wsTarget.Cells(rowNumber, 3).Value = sArray(1)
wsTarget.Cells(rowNumber, 4).Value = sArray(2)
Debug.Print "SOD FOUND: " & sodQuery("ERD")
i = i + 1
Wend
End If
If sum = 4 Then
While i <= sum
wsTarget.Cells(rowNumber, 1).Value = usersQuery("user_id")
wsTarget.Cells(rowNumber, 2).Value = sArray(0)
wsTarget.Cells(rowNumber, 3).Value = sArray(1)
wsTarget.Cells(rowNumber, 4).Value = sArray(2)
wsTarget.Cells(rowNumber, 5).Value = sArray(3)
Debug.Print "SOD FOUND: " & sodQuery("ERD")
i = i + 1
Wend
End If
If sum = 5 Then
While i <= sum
wsTarget.Cells(rowNumber, 1).Value = usersQuery("user_id")
wsTarget.Cells(rowNumber, 2).Value = sArray(0)
wsTarget.Cells(rowNumber, 3).Value = sArray(1)
wsTarget.Cells(rowNumber, 4).Value = sArray(2)
wsTarget.Cells(rowNumber, 5).Value = sArray(3)
wsTarget.Cells(rowNumber, 6).Value = sArray(4)
Debug.Print "SOD FOUND: " & sodQuery("ERD")
i = i + 1
Wend
End If
If sum = 6 Then
While i <= sum
wsTarget.Cells(rowNumber, 1).Value = usersQuery("user_id")
wsTarget.Cells(rowNumber, 2).Value = sArray(0)
wsTarget.Cells(rowNumber, 3).Value = sArray(1)
wsTarget.Cells(rowNumber, 4).Value = sArray(2)
wsTarget.Cells(rowNumber, 5).Value = sArray(3)
wsTarget.Cells(rowNumber, 6).Value = sArray(4)
wsTarget.Cells(rowNumber, 7).Value = sArray(5)
Debug.Print "SOD FOUND: " & sodQuery("ERD")
i = i + 1
Wend
End If
i = 0
rowNumber = rowNumber + 1
End If
candidateQuery.MoveNext
Wend
End If
candidateQuery.Close
Set candidateQuery = Nothing
sodQuery.MoveNext
Wend
End If
sodQuery.Close
Set sodQuery = Nothing
usersQuery.MoveNext
Wend
End If
usersQuery.Close
Set usersQuery = Nothing
Debug.Print "Programa terminado"
End Sub