CLASS : TRACKING POUR VOTRE ECOMMERCE (MYSQL SQLSERVER)

Imports System.Data.SqlClient
Imports System.Data.Odbc
Imports System.Configuration
Imports System.Web.HttpServerUtility

Namespace ecommerce

    Public Class eComTracking
        Protected _IDTrack As String
        Public ReadOnly Property IDTrack() As String
            Get
                Return _IDTrack
            End Get
        End Property
        Protected _IDCook As String = ""
        Public Property IDCook() As String
            Get
                Return _IDCook
            End Get
            Set(ByVal Value As String)
                _IDCook = Value
            End Set
        End Property
        Protected _IsRegister As Boolean = True
        Public Property IsRegister() As Boolean
            Get
                Return _IsRegister
            End Get
            Set(ByVal Value As Boolean)
                _IsRegister = Value
            End Set
        End Property
        Protected _InscrNews As Integer = 0
        Public Property InscrNews() As Integer
            Get
                Return _InscrNews
            End Get
            Set(ByVal Value As Integer)
                _InscrNews = Value
            End Set
        End Property
        Protected _Referant As String = ""
        Public Property Referant() As String
            Get
                Return _Referant
            End Get
            Set(ByVal Value As String)
                _Referant = Value
            End Set
        End Property
        Protected _IP As String = ""
        Public Property IP() As String
            Get
                Return _IP
            End Get
            Set(ByVal Value As String)
                _IP = Value
            End Set
        End Property
        Protected _PartId As Integer = 0
        Public Property PartId() As Integer
            Get
                Return _PartId
            End Get
            Set(ByVal Value As Integer)
                _PartId = Value
            End Set
        End Property
        Protected _BannerId As Integer = 0
        Public Property BannerId() As Integer
            Get
                Return _BannerId
            End Get
            Set(ByVal Value As Integer)
                _BannerId = Value
            End Set
        End Property
        Protected _ActionId As Integer = 0
        Public Property ActionId() As Integer
            Get
                Return _ActionId
            End Get
            Set(ByVal Value As Integer)
                _ActionId = Value
            End Set
        End Property
        Protected _DateOpen As Date = Now
        Public Property DateOpen() As Date
            Get
                Return _DateOpen
            End Get
            Set(ByVal Value As Date)
                _DateOpen = Value
            End Set
        End Property
        Protected _AddPanier As Integer = 0
        Public Property AddPanier() As Integer
            Get
                Return _AddPanier
            End Get
            Set(ByVal Value As Integer)
                _AddPanier = Value
            End Set
        End Property
        Protected _IdCommande As Integer = 0
        Public Property IdCommande() As Integer
            Get
                Return _IdCommande
            End Get
            Set(ByVal Value As Integer)
                _IdCommande = Value
            End Set
        End Property
        Protected _IdClient As Integer = 0
        Public Property IdClient() As Integer
            Get
                Return _IdClient
            End Get
            Set(ByVal Value As Integer)
                _IdClient = Value
            End Set
        End Property
        Protected _Email As String = ""
        Public Property Email() As String
            Get
                Return _Email
            End Get
            Set(ByVal Value As String)
                _Email = Value
            End Set
        End Property
        Protected _UseMoteur As Integer = 0
        Public Property UseMoteur() As Integer
            Get
                Return _UseMoteur
            End Get
            Set(ByVal Value As Integer)
                _UseMoteur = Value
            End Set
        End Property
        Protected _UseAcProd As Integer = 0
        Public Property UseAcProd() As Integer
            Get
                Return _UseAcProd
            End Get
            Set(ByVal Value As Integer)
                _UseAcProd = Value
            End Set
        End Property
        Protected _Cross As Integer = 0
        Public Property Cross() As Integer
            Get
                Return _Cross
            End Get
            Set(ByVal Value As Integer)
                _Cross = Value
            End Set
        End Property
        Protected _URLSite As String = ""
        Public Property URLSite() As String
            Get
                Return _URLSite
            End Get
            Set(ByVal Value As String)
                _URLSite = Value
            End Set
        End Property
        Protected _promoref As String = ""
        Public Property promoref() As String
            Get
                Return _promoref
            End Get
            Set(ByVal Value As String)
                _promoref = Value
            End Set
        End Property
        Protected _domaine As String = ""
        Public Property domaine() As String
            Get
                Return _domaine
            End Get
            Set(ByVal Value As String)
                _domaine = Value
            End Set
        End Property
        Protected _NbPage As Integer = 0
        Public Property NbPage() As Integer
            Get
                Return _NbPage
            End Get
            Set(ByVal Value As Integer)
                _NbPage = Value
            End Set
        End Property

        Public Sub New(ByVal IDTracking As String, ByVal StrReferant As String, ByVal IPuser As String, Optional ByVal URLSite As String = "euros-laser.com", Optional ByVal IsRegister As Boolean = True)
            _IDTrack = IDTracking
            Referant = StrReferant
            _IP = IPuser
            _IsRegister = IsRegister
            _URLSite = URLSite
        End Sub

        Public Function SessionClose()
            If (IsRegister) And NbPage > 0 Then
                RegisterSQLServer()
            End If
        End Function

        Public Sub AddPageVue()
            _NbPage = _NbPage + 1
        End Sub

        Protected Sub RegisterSQLServer()
            Dim SqlStr As String = ""
            SqlStr = "INSERT INTO AA_TRACKING ("
            SqlStr = SqlStr & "T_IDTrack,"
            SqlStr = SqlStr & "T_IDCook,"
            SqlStr = SqlStr & "T_InscrNews,"
            SqlStr = SqlStr & "T_Referant,"
            SqlStr = SqlStr & "T_IP,"
            SqlStr = SqlStr & "T_PartId,"
            SqlStr = SqlStr & "T_NbPage,"
            SqlStr = SqlStr & "T_BannerId,"
            SqlStr = SqlStr & "T_ActionId,"
            SqlStr = SqlStr & "T_DateOpen,"
            SqlStr = SqlStr & "T_AddPanier,"
            SqlStr = SqlStr & "T_IdCommande,"
            SqlStr = SqlStr & "T_IdClient,"
            SqlStr = SqlStr & "T_Email,"
            SqlStr = SqlStr & "T_UseMoteur,"
            SqlStr = SqlStr & "T_UseAcProd,"
            SqlStr = SqlStr & "T_Cross,"
            SqlStr = SqlStr & "T_URLSite,"
            SqlStr = SqlStr & "T_DOMAINE,"
            SqlStr = SqlStr & "T_promoref) VALUES ("
            SqlStr = SqlStr & "'" & Replace(_IDTrack, "'", "''") & "',"
            SqlStr = SqlStr & "'" & Replace(_IDCook, "'", "''") & "',"
            SqlStr = SqlStr & _InscrNews.ToString & ","
            SqlStr = SqlStr & "'" & Replace(_Referant, "'", "''") & "',"
            SqlStr = SqlStr & "'" & Replace(_IP, "'", "''") & "',"
            SqlStr = SqlStr & _PartId.ToString & ","
            SqlStr = SqlStr & _NbPage.ToString & ","
            SqlStr = SqlStr & _BannerId.ToString & ","
            SqlStr = SqlStr & _ActionId.ToString & ","
            SqlStr = SqlStr & "'" & Replace(_DateOpen, "'", "''") & "',"
            SqlStr = SqlStr & _AddPanier.ToString & ","
            SqlStr = SqlStr & _IdCommande.ToString & ","
            SqlStr = SqlStr & _IdClient.ToString & ","
            SqlStr = SqlStr & "'" & Replace(_Email, "'", "''") & "',"
            SqlStr = SqlStr & _UseMoteur.ToString & ","
            SqlStr = SqlStr & _UseAcProd.ToString & ","
            SqlStr = SqlStr & _Cross.ToString & ","
            SqlStr = SqlStr & "'" & Replace(_URLSite, "'", "''") & "',"
            SqlStr = SqlStr & "'" & Replace(_domaine, "'", "''") & "',"
            SqlStr = SqlStr & "'" & Replace(_promoref, "'", "''") & "')"
            Dim NumCOM As String = ""
            'connectionMySQL
            Dim conn As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
            Dim com As New SqlCommand(SqlStr, conn)
            Try
                conn.Open()
                com.ExecuteNonQuery()
            Catch ex As Exception
            Finally
                com = Nothing
                conn.Close()
                conn = Nothing
            End Try
        End Sub

        Protected Sub RegisterMySQL()
            Dim SqlStr As String = ""
            SqlStr = "INSERT INTO AA_TRACKING ("
            SqlStr = SqlStr & "T_IDTrack,"
            SqlStr = SqlStr & "T_IDCook,"
            SqlStr = SqlStr & "T_InscrNews,"
            SqlStr = SqlStr & "T_Referant,"
            SqlStr = SqlStr & "T_IP,"
            SqlStr = SqlStr & "T_PartId,"
            SqlStr = SqlStr & "T_NbPage,"
            SqlStr = SqlStr & "T_BannerId,"
            SqlStr = SqlStr & "T_ActionId,"
            SqlStr = SqlStr & "T_DateOpen,"
            SqlStr = SqlStr & "T_AddPanier,"
            SqlStr = SqlStr & "T_IdCommande,"
            SqlStr = SqlStr & "T_IdClient,"
            SqlStr = SqlStr & "T_Email,"
            SqlStr = SqlStr & "T_UseMoteur,"
            SqlStr = SqlStr & "T_UseAcProd,"
            SqlStr = SqlStr & "T_Cross,"
            SqlStr = SqlStr & "T_URLSite,"
            SqlStr = SqlStr & "T_DOMAINE,"
            SqlStr = SqlStr & "T_promoref) VALUES ("
            SqlStr = SqlStr & "'" & Replace(_IDTrack, "'", "''") & "',"
            SqlStr = SqlStr & "'" & Replace(_IDCook, "'", "''") & "',"
            SqlStr = SqlStr & _InscrNews.ToString & ","
            SqlStr = SqlStr & "'" & Replace(_Referant, "'", "''") & "',"
            SqlStr = SqlStr & "'" & Replace(_IP, "'", "''") & "',"
            SqlStr = SqlStr & _PartId.ToString & ","
            SqlStr = SqlStr & _NbPage.ToString & ","
            SqlStr = SqlStr & _BannerId.ToString & ","
            SqlStr = SqlStr & _ActionId.ToString & ","
            SqlStr = SqlStr & "NOW(),"
            SqlStr = SqlStr & _AddPanier.ToString & ","
            SqlStr = SqlStr & _IdCommande.ToString & ","
            SqlStr = SqlStr & _IdClient.ToString & ","
            SqlStr = SqlStr & "'" & Replace(_Email, "'", "''") & "',"
            SqlStr = SqlStr & _UseMoteur.ToString & ","
            SqlStr = SqlStr & _UseAcProd.ToString & ","
            SqlStr = SqlStr & _Cross.ToString & ","
            SqlStr = SqlStr & "'" & Replace(_URLSite, "'", "''") & "',"
            SqlStr = SqlStr & "'" & Replace(_domaine, "'", "''") & "',"
            SqlStr = SqlStr & "'" & Replace(_promoref, "'", "''") & "')"
            Dim NumCOM As String = ""
            'connectionMySQL
            Dim conn As New OdbcConnection(ConfigurationSettings.AppSettings("connectionMySQL"))
            Dim com As New OdbcCommand(SqlStr, conn)
            Try
                conn.Open()
                com.ExecuteNonQuery()
            Catch ex As Exception
            Finally
                com = Nothing
                conn.Close()
                conn = Nothing
            End Try
        End Sub

        Public Function SessionMySQLClose()
            If (IsRegister) Then
                RegisterMySQL()
            End If
        End Function

        Public Shared Function GetPartCom(ByVal idcom As Integer) As String
            Dim SqlStr As String = "SELECT eBoutiks_PARTENAIRES.PART_NOM from AA_TRACKING,eBoutiks_PARTENAIRES WHERE AA_TRACKING.T_PartId = eBoutiks_PARTENAIRES.PART_ID AND AA_TRACKING.T_IdCommande=" & idcom.ToString
            Dim conn As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
            Dim com As New SqlCommand(SqlStr, conn)
            Dim result As String
            Try
                conn.Open()
                result = com.ExecuteScalar
            Catch ex As Exception
            Finally
                com = Nothing
                conn.Close()
                conn = Nothing
            End Try
            Return result
        End Function

        Public Shared Function UpdateCACom(ByVal TrackId As String, ByVal Montant As Decimal, ByVal IdCom As Integer)
            Dim SqlStr As String = "UPDATE AA_TRACKING SET T_IdCommande=" & IdCom.ToString & " WHERE T_IDTrack='" & Replace(TrackId, "'", "''") & "'"
            Dim conn As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
            Dim com As New SqlCommand(SqlStr, conn)
            Try
                conn.Open()
                com.ExecuteNonQuery()
            Catch ex As Exception
            Finally
                com = Nothing
                conn.Close()
                conn = Nothing
            End Try
        End Function

        Public Shared Function GetKeyWord(ByVal AbsoluteUri As String) As String
            ' retourne les mots clefs saisis sous GOOGLE ou MSNSEARCH à partir d'une URL
            Dim lachaine As String = AbsoluteUri
            Dim keyword As String = ""
            Dim Cpt As Integer = 0
            Dim Cpt2 As Integer = 0
            Cpt = InStr(lachaine, "q")
            If Cpt > 0 Then
                lachaine = Mid(lachaine, (Cpt + 2), Len(lachaine))
                Cpt2 = InStr(lachaine, "&")
                If Cpt2 > 0 Then
                    keyword = Mid(lachaine, 1, (Cpt2 - 1))
                    keyword = Replace(keyword, "+", " ")
                Else
                    keyword = lachaine
                End If
            End If
            Return keyword
        End Function

        Public Shared Function IsRegisteTrack(ByVal IP As String) As Boolean
            Dim StrSql As String = "SELECT count(*) FROM AA_BOT WHERE BOT_IP='" & IP & "'"
            Dim Retour As Integer = 0
            Dim conn As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
            Dim com As New SqlCommand(StrSql, conn)
            Try
                conn.Open()
                Retour = com.ExecuteScalar()
            Catch ex As Exception
            Finally
                com = Nothing
                conn.Close()
                conn = Nothing
            End Try
            If Retour > 0 Then
                Return False
            Else
                Return True
            End If
        End Function

    End Class
End Namespace