Private Shared Function GetExecCampaignList() As Data.DataTable
'This function returns a datatable of campaign shortcodes that meet criteria:
'1. Campaign is less than six months old, or has been distributed in the last six months- iExecLookBack (constant), local variable: dtCompareDate
'2. Have produced leads at any time
Dim objSQLCmd As Data.SqlClient.SqlCommand
Dim objSQLDR As Data.SqlClient.SqlDataReader
Dim objSQLDA As Data.SqlClient.SqlDataAdapter
Dim dtMaillist As Data.DataTable 'Maillist Table
Dim dtCampaigns As Data.DataTable 'Result DataTable
Dim drRow As Data.DataRow
Dim sSQL As String
Dim dtCompareDate As DateTime
Dim sShortCode As String 'Campaign Shortcode
Dim dtCampDate As DateTime 'Campaign Creation Date
Dim sMedia As String 'Camapign Media
Dim bLead As Boolean 'Campaign has produced a lead
Dim bFresh As Boolean 'Campaign created or distributed within last 6 months
Try
dtCompareDate = DateTime.Now.AddMonths(-iExecLookBack)
'Define a result datatable
dtCampaigns = New Data.DataTable
dtCampaigns.Columns.Add("ShortCode", GetType(String))
dtCampaigns.Columns.Add("Media", GetType(String))
dtCampaigns.Columns.Add("Desc", GetType(String))
'Pull the mailist sources from go live date on
sSQL = "SELECT DISTINCT [Source] FROM Maillist " & _
"WHERE ([DateStamp] >= '" & cMain.conGoLiveDate.ToString(cMain.conRptDate) & "') " & _
"AND ([SOURCE] IS NOT NULL)"
objSQLDA = New SqlClient.SqlDataAdapter(sSQL, cMain.gsLdrConn)
dtMaillist = New Data.DataTable
objSQLDA.Fill(dtMaillist)
dtMaillist.PrimaryKey = New System.Data.DataColumn() {dtMaillist.Columns(0)} 'Set the primary key to allow DefaultView.Find()
dtMaillist.DefaultView.Sort = "Source"
'Pull a list of all campaigns (Normal and Internet)
sSQL = "SELECT [Index],[ShortCode],[Media],[Desc],[Date] FROM tblCampaigns " & _
"UNION ALL " & _
"SELECT [Index],[ShortCode],'Internet' as [Media],[Desc],[StartDate] FROM tblInetCampaigns"
objSQLCmd = New SqlClient.SqlCommand(sSQL, New SqlClient.SqlConnection(cMain.gsMktConn))
objSQLCmd.Connection.Open()
objSQLDR = objSQLCmd.ExecuteReader(CommandBehavior.CloseConnection)
With objSQLDR
While .Read()
'Check all campaigns to see if they meet the selection criteria
sShortCode = .GetString(1)
sMedia = .GetString(2)
dtCampDate = .GetDateTime(4)
bLead = False
bFresh = False
'Test campaign to see if it meets criteria
If dtMaillist.DefaultView.Find(sShortCode) >= 0 Then 'Check if the campaign shortcode is found in maillist results
bLead = True
Else
Select Case sMedia
Case "Mailouts", "Flyers"
sSQL = "SELECT COUNT(*) FROM tblDistQty WHERE ([CampaignIndex]=" & .GetInt32(0) & ")"
If CInt(cMain.GetDBVal(sSQL)) > 0 Then
bFresh = True
End If
Case Else
If dtCampDate > dtCompareDate Then
bFresh = True
End If
End Select
End If 'Check the campaign creation or last distribution
'If lead found or date criteria met -> add result
If bLead Or bFresh Then
drRow = dtCampaigns.NewRow()
drRow.Item(0) = sShortCode
drRow.Item(1) = sMedia
drRow.Item(2) = .GetString(3)
dtCampaigns.Rows.Add(drRow)
End If
End While
End With
Catch ex As Exception
cMain.WriteErrorLog(conCName & "::GetExecCampaignList" & vbTab & ex.Message)
Return dtCampaigns
Finally
drRow = Nothing
dtMaillist.Dispose()
dtMaillist = Nothing
If Not objSQLDA Is Nothing Then
objSQLDA.Dispose()
objSQLDA = Nothing
End If
If Not objSQLCmd Is Nothing Then
objSQLCmd.Dispose()
objSQLCmd = Nothing
End If
If Not objSQLDR Is Nothing Then
If Not objSQLDR.IsClosed Then
objSQLDR.Close()
End If
objSQLDR = Nothing
End If
End Try
Return dtCampaigns
End Function
