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 !
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.