CLASS : NEWSLETTER AVEC OPTIN

#Region "Les imports"
Imports System.Data.SqlClient
Imports System.Web
Imports System.Web.Mail
Imports System.Configuration
#End Region

#Region "CopyRight & Co"
'*********************************************************************************
'**  File: AA-ENGINE\newsletter\Newsletter.vb
'**  Name: AA-ENGINE.Newsletter
'**  Desc: Moteur de newsletter avec double Optin sur une base de données SQLServer
'**  
'**
'**  Auth: (c)PINNEAU
'**  Date: 07/03/2005
'*********************************************************************************
'**  Change History
'*********************************************************************************
'**  Date:       Author:                Description:
'**  ----------  ------------------     -------------------------------------------
'**  07/03/2005  Jérôme PINNEAU              Création de la classe
'**  
'*********************************************************************************
#End Region
Namespace Newsletter

    Public Class Newsletter

        Public Shared Function IsAboEmail(ByVal email As String) As Boolean
            Dim IDretour As Integer = 0
            Dim conn As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
            conn.Open()
            Dim com As New SqlCommand("SELECT news_id FROM AA_newsletter WHERE news_mail='" & email & "' ORDER BY news_id DESC", conn)
            IDretour = com.ExecuteScalar()
            com = Nothing
            conn.Close()
            conn = Nothing
            If IDretour < 0 Then
                Return True
            Else
                Return False
            End If
        End Function

        Public Shared Sub AddAbonnes(ByVal email As String)
            Dim conn As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
            conn.Open()
            Dim com As New SqlCommand("INSERT INTO AA_newsletter (news_mail,news_creation,news_validation) VALUES ('" & Replace(email, "'", "''") & "',getdate(),1)", conn)
            com.ExecuteNonQuery()
            com = Nothing
            conn.Close()
            conn = Nothing
        End Sub

        Public Function PreInscription(ByVal email As String) As Integer
            Dim IDretour As Integer = 0
            Dim conn As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
            conn.Open()
            Dim com As New SqlCommand("INSERT INTO AA_newsletter (news_mail,news_creation) VALUES ('" & email & "',getdate())", conn)
            com.ExecuteNonQuery()
            com.CommandText = "SELECT news_id FROM AA_newsletter WHERE news_mail='" & email & "' ORDER BY news_id DESC"
            IDretour = com.ExecuteScalar()
            com = Nothing
            conn.Close()
            conn = Nothing
            Return IDretour
        End Function

        Public Function Update_Civilite(ByVal Idemail As Integer, ByVal civ As String) As Boolean
            Dim IDretour As Integer = 0
            Dim conn As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
            Try
                conn.Open()
            Catch ex As Exception
                Return False
            End Try
            Try
                Dim com As New SqlCommand("UPDATE AA_newsletter SET news_civ='" & civ & "' WHERE news_id=" & Idemail.ToString, conn)
                com.ExecuteNonQuery()
                com = Nothing
                conn.Close()
                conn = Nothing
                Return True
            Catch ex As Exception
                conn.Close()
                conn = Nothing
                Return False
            End Try
        End Function

        Public Function Update_DateNaissance(ByVal Idemail As Integer, ByVal DateNaissance As String) As Boolean
            Dim IDretour As Integer = 0
            Dim conn As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
            Try
                conn.Open()
            Catch ex As Exception
                Return False
            End Try
            Try
                Dim com As New SqlCommand("UPDATE AA_newsletter SET news_naissance='" & DateNaissance & "' WHERE news_id=" & Idemail.ToString, conn)
                com.ExecuteNonQuery()
                com = Nothing
                conn.Close()
                conn = Nothing
                Return True
            Catch ex As Exception
                conn.Close()
                conn = Nothing
                Return False
            End Try
        End Function

        Public Function Update_CPostal(ByVal Idemail As Integer, ByVal CP As String) As Boolean
            Dim IDretour As Integer = 0
            Dim conn As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
            Try
                conn.Open()
            Catch ex As Exception
                Return False
            End Try
            Try
                Dim com As New SqlCommand("UPDATE AA_newsletter SET news_CCP='" & CP & "' WHERE news_id=" & Idemail.ToString, conn)
                com.ExecuteNonQuery()
                com = Nothing
                conn.Close()
                conn = Nothing
                Return True
            Catch ex As Exception
                conn.Close()
                conn = Nothing
                Return False
            End Try
        End Function

        Public Function Register_mail(ByVal email As String, ByVal Id As Integer) As Boolean
            Dim IDretour As Integer = 0
            Dim conn As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
            Try
                conn.Open()
            Catch ex As Exception
                Return False
            End Try
            Try
                Dim com As New SqlCommand("UPDATE AA_newsletter SET news_validation=1 WHERE news_id=" & Id.ToString & " AND news_mail='" & Replace(email, "'", "''") & "'", conn)
                com.ExecuteNonQuery()
                com = Nothing
                conn.Close()
                conn = Nothing
                AA_ENGINE.IO.Logs.AddEvent("nouvelle inscription à la newsletter", 4)
                Return True
            Catch ex As Exception
                conn.Close()
                conn = Nothing
                Return False
            End Try
        End Function

        Public Function desinscription_mail(ByVal email As String, ByVal Id As Integer) As Boolean
            Dim IDretour As Integer = 0
            Dim conn As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
            Try
                conn.Open()
            Catch ex As Exception
                Return False
            End Try
            Try
                Dim com As New SqlCommand("UPDATE AA_newsletter SET news_validation=0,News_desins=getdate() WHERE news_id=" & Id.ToString & " AND news_mail='" & email & "'", conn)
                com.ExecuteNonQuery()
                com = Nothing
                conn.Close()
                conn = Nothing
                Return True
            Catch ex As Exception
                conn.Close()
                conn = Nothing
                Return False
            End Try
        End Function

        Public Function new_mail_erreur(ByVal email As String, ByVal Id As Integer) As Boolean
            Dim IDretour As Integer = 0
            Dim conn As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
            Try
                conn.Open()
            Catch ex As Exception
                Return False
            End Try
            Try
                Dim com As New SqlCommand("UPDATE AA_newsletter SET news_validation=0,news_desins=getdate()  WHERE news_id=" & Id.ToString & " AND news_mail='" & email & "'", conn)
                com.ExecuteNonQuery()
                com = Nothing
                conn.Close()
                conn = Nothing
                Return True
            Catch ex As Exception
                conn.Close()
                conn = Nothing
                Return False
            End Try
        End Function

    End Class

End Namespace