Class : tracking pour votre ecommerce (mysql sqlserver)

Commenter

Contenu du snippet

Et hop ! encore une petite classe réutilisable à loisir !
C'est avec cette classe que je gère le Tracking sur mes sites de e-commerce (enfin, une autre plus complète mais sur la même base. Cette classe fonctionne sous SQLServer et MySQL, voici les deux scripts SQL :

Sous SQLServer :
CREATE TABLE [dbo].[AA_TRACKING] (
[T_IDTrack] [nvarchar] (100) COLLATE French_CI_AS NOT NULL ,
[T_IDCook] [nvarchar] (100) COLLATE French_CI_AS NULL ,
[T_InscrNews] [int] NOT NULL ,
[T_Referant] [nvarchar] (255) COLLATE French_CI_AS NULL ,
[T_IP] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[T_PartId] [int] NOT NULL ,
[T_BannerId] [int] NOT NULL ,
[T_ActionId] [int] NOT NULL ,
[T_DateOpen] [smalldatetime] NOT NULL ,
[T_DateClose] [smalldatetime] NOT NULL ,
[T_AddPanier] [int] NOT NULL ,
[T_IdCommande] [int] NOT NULL ,
[T_IdClient] [int] NOT NULL ,
[T_Email] [nvarchar] (255) COLLATE French_CI_AS NULL ,
[T_UseMoteur] [int] NOT NULL ,
[T_UseAcProd] [int] NOT NULL ,
[T_Cross] [int] NOT NULL ,
[T_URLSite] [nvarchar] (100) COLLATE French_CI_AS NULL ,
[T_promoref] [nvarchar] (50) COLLATE French_CI_AS NULL ,
[T_CACom] [decimal](18, 0) NULL ,
[T_DOMAINE] [nvarchar] (255) COLLATE French_CI_AS NULL ,
[T_NbPage] [int] NULL
) ON [PRIMARY]
GO

Sous MySQL :
CREATE TABLE `aa_tracking` (
`T_IDTrack` varchar(100) NOT NULL default '',
`T_IDCook` varchar(100) default NULL,
`T_InscrNews` int(11) NOT NULL default '0',
`T_Referant` varchar(255) default NULL,
`T_IP` varchar(50) default NULL,
`T_PartId` int(11) default '0',
`T_BannerId` int(11) default '0',
`T_ActionId` int(11) default '0',
`T_DateOpen` date default NULL,
`T_DateClose` date default NULL,
`T_AddPanier` int(11) default '0',
`T_IdCommande` int(11) default '0',
`T_IdClient` int(11) default '0',
`T_Email` varchar(255) default NULL,
`T_UseMoteur` int(11) default '0',
`T_UseAcProd` int(11) default '0',
`T_Cross` int(11) default '0',
`T_URLSite` varchar(100) default NULL,
`T_promoref` varchar(50) default NULL,
`T_CACom` decimal(10,0) default '0',
`T_DOMAINE` varchar(255) default NULL,
`T_NbPage` int(11) default '0',
PRIMARY KEY (`T_IDTrack`)
) TYPE=MyISAM;

Source / Exemple : 


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

Conclusion :


Bon Code !

A voir également

S'inscrire maintenant

Vous n'êtes pas encore membre ?

inscrivez-vous, c'est gratuit et ça prend moins d'une minute !

Les membres obtiennent plus de réponses que les utilisateurs anonymes.

Le fait d'être membre vous permet d'avoir un suivi détaillé de vos demandes et codes sources.

Le fait d'être membre vous permet d'avoir des options supplémentaires.

Du même auteur (cs_vladam)