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
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
| 'Declare objects and variables
Dim Arg, recentFile, flowchartUsername, strProvider, strProvider2, strPath, testString, concatCellInfo, flowchartName, cellCode, offerID, offerListID, offerCode
Dim sFilePathFTP, sFilePathEmail, objFSO, objTextFileFTP, objTextFileEmail
Dim myConnection, myCommand, adoRec, adoRecOfferCode, sftp, adoRecFullName, adoRecEmail, adoRecPhoneNumber, adoTargetCellInfo, adoCustomCellInfo
Dim objShellFTP, objShellEmail
Dim flowchartID, totalCount, campaignID, campaignName
Dim fullName, emailAddress, phoneNumber, optionalEmail
Dim oFTPScriptFSO, oFTPScriptShell, strProviderName, strProvider2Name, strEmailList, strBody
Dim sCmdFTP, sCmdEmail
'declare counter variables
Dim lenToCellCount, lenToOfferCode, creativeCodeLength, cellCountLength
'Declare date time variables
Dim mostRecentDate
'Declare booleans
Dim isMultipleEmailCamp, noCreative
'Declare cell variables
Dim cellID, cellName, creativeCode, cellCount
'initialize variables
totalCount = 0
offerCode = ""
creativeCode = ""
noCreative=False
'initialize constants for column spacing
lenToCellCount = 30
lenToOfferCode = 25
'Get the script arguments
Set Arg = WScript.Arguments
'Initialize the flowchart variables from the arguments passed to the script
recentFile = Replace(Arg(0),"'","")
flowchartUsername = Replace(Arg(1),"'","")
flowchartID = Replace(Arg(2),"'","")
flowchartName = Replace(Arg(3),"'","")
'get optional emails
optionalEmail = Replace(Arg(4),"'","")
'remove optional email spaces (This is necessary for blat to work correctly)
optionalEmail = Replace(optionalEmail," ","")
'define connections
Set myConnection = CreateObject("ADODB.Connection")
Set myCommand = CreateObject("ADODB.Command")
myConnection.Open "PROVIDER=SQLOLEDB;DATA SOURCE=AAPMSCCMSUC1;" & _
"UID=CCMSUnicaCampaign@app.cogeco.com;PWD=gp6D9EtkG9"
myCommand.ActiveConnection=myConnection
'Get the Full name of the user running the flowchart
myCommand.CommandText="SELECT FIRST_NAME + ' ' + LAST_NAME from platform.usm_user where NAME = '" & flowchartUsername & "'"
Set adoRecFullName = myCommand.Execute()
fullName = adoRecFullName(0)
'Get the email address of the user from the database
myCommand.CommandText="SELECT EMAIL from platform.usm_user where NAME = '" & flowchartUsername & "'"
Set adoRecEmail = myCommand.Execute()
emailAddress = adoRecEmail(0)
'Get the phone number of the user from the database
myCommand.CommandText="SELECT PHONE1 from platform.usm_user where NAME = '" & flowchartUsername & "'"
Set adoRecPhoneNumber = myCommand.Execute()
phoneNumber = adoRecPhoneNumber(0)
'Get the Campaign ID from the database and use it in the next query
myCommand.CommandText="SELECT CampaignID from platform.UA_Flowchart where FlowchartID = " & flowchartID
Set adoRec = myCommand.Execute()
campaignID = adoRec(0)
'Use the campaign ID to get the campaign name from the database
myCommand.CommandText="SELECT Name from platform.UA_Campaign where CampaignID = " & campaignID
Set adoRec = myCommand.Execute()
campaignName = adoRec(0)
'Check if this is a multiple-email-single-id flowchart by checking if the flowchart id is in the mutiple em history table
myCommand.CommandText="SELECT CAMPCODE from platform.EM_MULTIPLES where sessionid = " & flowchartID
Set adoRec = myCommand.Execute()
'verify if flowchart id was in that table
if adoRec.EOF then
isMultipleEmailCamp = FALSE
else
isMultipleEmailCamp = TRUE
end if
'Get the information needed (cell count, cell name)
myCommand.CommandText="select sum(cellcount) as cellSum, StringValue, MAX(platform.UA_TargetCells.offerListID) from platform.UA_TargetCells INNER JOIN platform.UA_OfferListMember ON platform.UA_TargetCells.OfferListID = platform.UA_OfferListMember.OfferListID" & _
" INNER JOIN platform.UA_CELLATTRIBUTE on platform.UA_TargetCells.cellid= platform.UA_CELLATTRIBUTE.cellid where " & _
"FlowchartID = " & flowchartID & " and StringValue!='' and IsControl = 0 and platform.UA_TargetCells.OfferListID is not null group by StringValue"
Set adoTargetCellInfo = myCommand.Execute()
'if there is no creative code entered just use cell information
if adoTargetCellInfo.EOF then
myCommand.CommandText="SELECT DISTINCT CellCount, CellID, platform.UA_TargetCells.offerListID from platform.UA_TargetCells INNER JOIN platform.UA_OfferListMember ON platform.UA_TargetCells.OfferListID = platform.UA_OfferListMember.OfferListID where " & _
"FlowchartID = " & flowchartID & " and IsControl = 0 and platform.UA_TargetCells.OfferListID is not null"
Set adoTargetCellInfo = myCommand.Execute()
noCreative=True
end if
'Initialize concatinated string
concatCellInfo = vbCrLf & "Here is the summary of the cells and the creative codes: " & vbCrLf & vbCrLf & _
"Creative Code " & "Cell Count " & "Offer Code" & vbCrLf
Do While Not adoTargetCellInfo.EOF
'we will need to loop through all the possible values and insert them into variables
'cellID = adoTargetCellInfo(0)
cellCount = adoTargetCellInfo(0)
if isMultipleEmailCamp = FALSE then
'Get the custom cell value (creative)
'myCommand.CommandText="SELECT StringValue from platform.UA_CellAttribute where CellID = " & cellID & " and AttributeID = 104"
'Set adoCustomCellInfo = myCommand.Execute()
'If nothing in creative simply output the cell count as there is only 1 cell existing and exit loop
if adoTargetCellInfo.EOF or noCreative = true then
creativeCode = "N/A"
'totalCount = cellCount
'concatCellInfo = vbCrLf & "Total Cell Count: " & cellCount
'exit do
Else 'Set the correct value from the database
creativeCode = adoTargetCellInfo(1)
End if
if creativeCode <> "" then
'do nothing
Else
creativeCode = "N/A"
End if
'To get the offer code several steps need to be taken as described below---------
'set the offer list ID
offerListID = adoTargetCellInfo(2)
'Get the offer ID if it exists using the offerListID
myCommand.CommandText="SELECT offerID from platform.UA_OfferListMember where OfferListID = " & offerListID
Set adoRec = myCommand.Execute()
'Reset offer code for next segment
offerCode = ""
'Loop through all offerIDs and get their offer codes
Do While not adoRec.EOF
'set the offer ID
offerID = adoRec(0)
'Finally get the offer code if it exists using the offer id
myCommand.CommandText="SELECT OfferCode1 from platform.UA_Offer where OfferID = " & offerID
Set adoRecOfferCode = myCommand.Execute()
if adoRecOfferCode.EOF then
'keep going until offercode is found
Else
'Finally Set the offer code
'Check if it is the first offer code or not and concatinate appropriately
if offerCode = "" then
offerCode = adoRecOfferCode(0)
else
offerCode = offerCode & ", &OfferCode=" & adoRecOfferCode(0)
end if
end if
adoRec.MoveNext
Loop
'End getting the offer code----------------------------------------------------------
'Get the total of all cells
totalCount = totalCount + cellCount
else
'get cellid
myCommand.CommandTest="select distinct platform.UA_TargetCells.cellid from platform.UA_TargetCells INNER JOIN platform.UA_OfferListMember ON platform.UA_TargetCells.OfferListID = platform.UA_OfferListMember.OfferListID" & _
" INNER JOIN platform.UA_CELLATTRIBUTE on platform.UA_TargetCells.cellid= platform.UA_CELLATTRIBUTE.cellid where StringValue='" & adoTargetCellInfo(1) & "'" & _
" AND FlowchartID = " & flowchartID & " and StringValue!='' and IsControl = 0 and platform.UA_TargetCells.OfferListID is not null"
Set adoCustomCellInfo = myCommand.Execute()
cellID = CStr(adoCustomCellInfo(0))
'Get the custom cell value (cell code)
myCommand.CommandText="SELECT cellCode from platform.UA_TargetCells where CellID = " & cellID
Set adoCustomCellInfo = myCommand.Execute()
cellCode = CStr(adoCustomCellInfo(0))
'Get the most recent date in the em multiple table and select based on it
myCommand.CommandText="select top 1 EVENT_DATETIME from platform.EM_MULTIPLES where sessionid = " & flowchartID & " order by EVENT_DATETIME desc"
Set adoCustomCellInfo = myCommand.Execute()
mostRecentDate = CStr(adoCustomCellInfo(0))
'Get the custom cell value (creative)
myCommand.CommandText="SELECT CREATE_VERSION, count(*) from platform.EM_MULTIPLES where Cell_CD = '" & cellCode & "' and sessionid = " & _
flowchartID & " and EVENT_DATETIME = '" & mostRecentDate & "' group by CREATE_VERSION"
Set adoCustomCellInfo = myCommand.Execute()
'test if empty then ouptut error log
if adoCustomCellInfo.EOF then
creativeCode = "N/A"
'totalCount = cellCount
'concatCellInfo = vbCrLf & "Total Cell Count: " & cellCount
'exit do
else
creativeCode = CStr(adoCustomCellInfo(0))
end if
'To get the offer code several steps need to be taken as described below---------
'set the offer list ID
offerListID = adoTargetCellInfo(2)
'Get the offer ID if it exists using the offerListID
myCommand.CommandText="SELECT offerID from platform.UA_OfferListMember where OfferListID = " & offerListID
Set adoRec = myCommand.Execute()
'Reset offer code for next segment
offerCode = ""
'Loop through all offersIDs and get the offer codes
Do While not adoRec.EOF
'set the offer ID
offerID = adoRec(0)
'Finally get the offer code if it exists using the offer id
myCommand.CommandText="SELECT OfferCode1 from platform.UA_Offer where OfferID = " & offerID
Set adoRecOfferCode = myCommand.Execute()
if adoRecOfferCode.EOF then
'keep going until offercode is found
Else
'Finally Set the offer code
'Check if it is the first offer code or not and concatinate appropriately
if offerCode = "" then
offerCode = adoRecOfferCode(0)
else
offerCode = offerCode & ", &OfferCode=" & adoRecOfferCode(0)
end if
end if
adoRec.MoveNext
Loop
'End getting the offer code----------------------------------------------------------
'replace count for cell
if adoCustomCellInfo.EOF then
testString = testString & vbCrLf & " ERROR ON QUERY SELECT CREATE_VERSION, count(*) from platform.EM_MULTIPLES where Cell_CD = '" & cellCode & "' and sessionid = " & _
flowchartID & " group by CREATE_VERSION"
else
cellCount = adoCustomCellInfo(1)
end if
totalCount = totalCount + cellCount
end if
'This is the mechanics that allow for proper indentation. It checks the total amount of spaces between the column
'titles (i.e. Creative Code_______x-length_______Cell Count____y-length_____) and adds the required
'amount of spaces (lenght of strings) in between values to keep correct indentation.
'get lenght of creative code
creativeCodeLength = Len(creativeCode)
'convert cell count to string and get lenght as well
cellCountLength = Len(Cstr(cellCount))
'testString = testString & creativeCodeLength & " " & cellCountLength & " " & lenToCellCount & " " & _
' lenToOfferCode & vbcrlf
'initialize first column
concatCellInfo = concatCellInfo & vbCrLf & creativeCode
'add correct amount of spaces between columns
for k = 0 to (lenToCellCount - creativeCodeLength - 1)
concatCellInfo = concatCellInfo & " "
next
'add second column info
concatCellInfo = concatCellInfo & cellCount
'add correct amount of spaces for second column
for k = 0 to (lenToOfferCode - cellCountLength - 1)
concatCellInfo = concatCellInfo & " "
next
'add third column info
concatCellInfo = concatCellInfo & "&OfferCd=" & offerCode
'get next value in array
adoTargetCellInfo.MoveNext
Loop
'initialize total count row
concatCellInfo = concatCellInfo & vbCrLf & "Total Cell Count"
'add correct amount of spaces for the total (take spaces until Cell Count)
for k = 0 to (lenToCellCount - 16 - 1) '16 is the lenght of the text for "Total Cell Count"
concatCellInfo = concatCellInfo & " "
next
'write the total cell count
concatCellInfo = concatCellInfo & totalCount
'Get the provider name from the flowchart name
strProvider=Mid(recentFile,19,3)
strProvider2=Mid(recentFile,19,3)
'Using the provider name Set the appropriate mail list
If strProvider2 = "Internal" then
strPath = "cd ""tsm/Providers list/Internal DNCL"""
strProvider2Name = "Cible_Recherche"
strEmailList = ",test2.test2@cogeco.com"
ElseIf strProvider2 = "D2D" then
strPath = "cd ""cim/Providers list"""
strProvider2Name = "D2D"
strEmailList = ",test2.test2@cogeco.com"
ElseIf strProvider2 = "INB" then
strPath = "cd ""cim/ToBedeleted"""
strProvider2Name = "Inbound"
strEmailList = ",test2.test2@cogeco.com"
End If
'Tests to be sent to consultants only
'strEmailList = ",armen.kitbalian@cogeco.com"
'strEmailList = ",test.test@cogeco.com"
'Add to email list the optional emails only if they exist
if optionalEmail <> "" then
strEmailList = strEmailList & "," & optionalEmail
else
'do nothing
end if
'strEmailList = ",armen.kitbalian@cogeco.com"
'Set the subject of the email using the appropriate information
strSubject = flowchartName
'Set the body of the email using all the required information
strBody="The campaign file " & recentFile & " has been produced by " & fullName & _
" on " & WeekDayName(Weekday(Date)) & " " & MonthName(Month(Date)) & " " & Day(Date) & " " & Year(Date) & " at " & _
TIME & " and sent to " & strProvider2Name & " under the Internal DNCL folder!" & vbCrLf & vbCrLf & concatCellInfo
'Set required object for both email and ftp
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Set ftp shell object
Set objShellFTP = CreateObject("WSCript.shell")
sFilePathFTP="D:\app\script.txt"
'Set the ftp commands
Set objTextFileFTP = objFSO.CreateTextFile(sFilePathFTP)
objTextFileFTP.Write ("open sftp.cogeco.com" & vbCRLF)
objTextFileFTP.Write ("unica" & vbCRLF)
objTextFileFTP.Write ("zxab9911" & vbCRLF)
objTextFileFTP.Write (strPath & vbCRLF)
objTextFileFTP.Write ("put " & """D:\Program Files\Unica\Campaign\partitions\partition1\Outputs\" & recentFile & """" & vbCRLF)
objTextFileFTP.Write ("bye")
objTextFileFTP.Close
'set the ftp launch command
sCmdFTP="ftp -s:" & sFilePathFTP
'Run ftp command
objShellFTP.Run sCmdFTP, 0, False
'Set email shell object
Set objShellEmail = CreateObject("WSCript.shell")
'Create Blat parameters and commands
sFilePathEmail="D:\Program Files\Unica\Campaign\partitions\partition1\Outputs\send_mailCmpFile.txt"
sCmdEmail = "D:\Tools\blat307_32.full\blat307\Full\Blat " & """" & sFilePathEmail & """" & " -to " & emailAddress & _
strEmailList & " -s " & """" & strSubject & """" & " -i " & """" & "Cogeco Campaign Notifications"
'Set the run command for the email
Set objTextFileEmail = objFSO.CreateTextFile(sFilePathEmail)
objTextFileEmail.Write (strBody)
'ran email command
objShellEmail.Run sCmdEmail, 0, False
'Clean all objects after use to free up memory
Set adoRecOfferCode = Nothing
Set adoTargetCellInfo = Nothing
Set adoCustomCellInfo = Nothing
Set adoRec = Nothing
Set Arg = Nothing
Set objShellFTP = Nothing
Set objShellEmail = Nothing
Set myConnection = Nothing
Set myCommand = Nothing
Set adoRecFullName = Nothing
Set adoRecEmail = Nothing
Set adoRecPhoneNumber = Nothing
Set oFTPScriptFSO = Nothing
Set oFTPScriptShell = Nothing
Set objFSO = Nothing
Set objTextFileFTP = Nothing
Set objTextFileEmail = Nothing |
