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 |
Partager