//////////////////////////////////////////////////
/// @brief Change la variable pour le site actif
//////////////////////////////////////////////////
private void ChangeVariable(string _var, int _site)
{
oData = new DataServices(_dsn, DBQuery.QVariable);
oData.AddParameter("@VAR", SqlDbType.VarChar, 50, ParameterDirection.Input, _var, "var_nom");
oData.AddParameter("@SITE", SqlDbType.Int, 0, ParameterDirection.Input, _site, "var_site");
oData.Execute(DataServices.ExecutionMode.UPDATE);
oData.Dispose();
}
//////////////////////////////////////////////////
/// @brief Enregistre la visite
//////////////////////////////////////////////////
public void Visiteur_Enregistre_Visite(string _session)
{
string pays_nom;
double code_ip;
string req;
DataServices oData;
Visiteur_Configuration();
try {
string[] IP = VisiteurAddress.Split('.');
code_ip = (16777216 * Convert.ToDouble((IP[0]))) + (65536 * Convert.ToDouble((IP[1]))) + (256 * Convert.ToDouble((IP[2]))) + Convert.ToDouble((IP[3]));
req = "SELECT TOP 1 iplocation_pays FROM wmsIPLOCATION WHERE iplocation_code_debut <=@DEBUT AND iplocation_code_fin >= @FIN";
oData = new DataServices(VisiteurDsn, req);
oData.AddParameter("@DEBUT", SqlDbType.Float, 0, ParameterDirection.Input, code_ip, "iplocation_code_debut");
oData.AddParameter("@FIN", SqlDbType.Float, 0, ParameterDirection.Input, code_ip, "iplocation_code_fin");
oData.GetStructures();
if (oData.UtilityDataExiste)
{
pays_nom = oData.UtilityDataField.ToString();
}
else
{
pays_nom = "N/A";
code_ip = 0;
}
}
catch
{
pays_nom = "N/A";
code_ip = 0;
}
req = "INSERT INTO wmsVISITE (visite_date_debut,visite_session,visiteur_id,config_id,visite_host_ip,visite_site,visite_code,visite_pays,visite_debut,visite_fin,visite_duree,visite_pages,visite_pages_in) ";
req += "values (@DATE,@SESSION,@VISITEUR,@CONFIG,@IP,@SITE,@VISITE,@PAYS,@DEBUT,@FIN,@DUREE,@PAGES,@PAGESINT)";
oData = new DataServices(VisiteurDsn, req);
oData.AddParameter("@DATE", SqlDbType.DateTime, 0, ParameterDirection.Input, DateTime.Now, "visite_date_debut");
oData.AddParameter("@VISITEUR", SqlDbType.Int, 0, ParameterDirection.Input, VisiteurId, "visiteur_id");
oData.AddParameter("@CONFIG", SqlDbType.Int, 0, ParameterDirection.Input, _config_id, "config_id");
oData.AddParameter("@IP", SqlDbType.VarChar, 20, ParameterDirection.Input, VisiteurAddress, "visite_host_ip");
oData.AddParameter("@SITE", SqlDbType.Int, 0, ParameterDirection.Input, VisiteurSite, "visite_site");
oData.AddParameter("@VISITE", SqlDbType.Float, 0, ParameterDirection.Input, code_ip, "visite_code");
oData.AddParameter("@PAYS", SqlDbType.VarChar, 50, ParameterDirection.Input, pays_nom, "visite_pays");
oData.AddParameter("@SESSION", SqlDbType.VarChar, 50, ParameterDirection.Input, _session, "visite_session");
oData.AddParameter("@DEBUT", SqlDbType.DateTime, 0, ParameterDirection.Input, DateTime.Now, "visite_debut");
oData.AddParameter("@FIN", SqlDbType.DateTime, 0, ParameterDirection.Input, DateTime.Now, "visite_fin");
oData.AddParameter("@DUREE", SqlDbType.Int, 0, ParameterDirection.Input, 0, "visite_duree");
oData.AddParameter("@PAGES", SqlDbType.Int, 0, ParameterDirection.Input, 0, "visite_pages_in");
oData.AddParameter("@PAGESINT", SqlDbType.Int, 0, ParameterDirection.Input, 0, "visite_pages");
oData.Execute(DataServices.ExecutionMode.INSERT);
VisiteurVisiteId = (int)oData.UtilityDataId;
oData.Dispose();
}
//////////////////////////////////////////////////
/// @brief Récupère la configuration du visiteur
//////////////////////////////////////////////////
public void Visiteur_Configuration()
{
string req = "SELECT config_id FROM wmsUSERAGENT WHERE config_useragent=@USERAGENT";
DataServices oData = new DataServices(VisiteurDsn, req);
oData.AddParameter("@USERAGENT", SqlDbType.NVarChar, 1000, ParameterDirection.Input, VisiteurUserAgent, "config_useragent");
oData.GetStructures();
if (!oData.UtilityDataExiste)
{
req = "INSERT INTO wmsUSERAGENT (config_OS,config_navigateur,config_version,config_screen,config_cookies,config_crawler, config_useragent) VALUES (@PLATFORM,@NAVIGATEUR,@VERSION,@SCREENW,@COOKIE,@CRAWLER,@USERAGENT)";
oData.UtilitySqlCommand = new SqlCommand(req, oData.UtilitySqlConnection);
oData.AddParameter("@PLATFORM", SqlDbType.VarChar, 50, ParameterDirection.Input, VisiteurBrowser.Platform, "config_OS");
oData.AddParameter("@NAVIGATEUR", SqlDbType.VarChar, 50, ParameterDirection.Input, VisiteurBrowser.Type, "config_navigateur");
oData.AddParameter("@VERSION", SqlDbType.VarChar, 50, ParameterDirection.Input, VisiteurBrowser.Version, "config_version");
oData.AddParameter("@SCREENW", SqlDbType.Int, 0, ParameterDirection.Input, VisiteurBrowser.ScreenPixelsWidth, "config_screen");
oData.AddParameter("@COOKIE", SqlDbType.Int, 0, ParameterDirection.Input, VisiteurBrowser.Cookies, "config_cookies");
oData.AddParameter("@CRAWLER", SqlDbType.Int, 0, ParameterDirection.Input, VisiteurBrowser.Crawler, "config_crawler");
oData.AddParameter("@USERAGENT", SqlDbType.NVarChar, 1000, ParameterDirection.Input, VisiteurUserAgent, "config_useragent");
oData.Execute(DataServices.ExecutionMode.INSERT);
_config_id = (int)oData.UtilityDataId;
}
else
{
_config_id = (int)oData.UtilityDataField;
}
oData.Dispose();
}
//////////////////////////////////////////////////
/// @brief Charge le chemin de la page d'accueil pour le profil et pour le site sélectionné
//////////////////////////////////////////////////
private string Charge_Visiteur_Homepage()
{
string _accueil = "/";
string req = "select page_url FROM wmsPAGE where page_id = (select page_id from crmDROIT_SITE where site_id=@SITE AND profil_id=@PROFIL)";
DataServices oData = new DataServices(VisiteurDsn, req);
oData.AddParameter("@PROFIL", SqlDbType.Int, 0, ParameterDirection.Input, VisiteurProfil, "profil_id");
oData.AddParameter("@SITE", SqlDbType.Int, 0, ParameterDirection.Input, VisiteurSite, "site_id");
oData.GetStructures();
_accueil += oData.UtilityDataField;
oData.Dispose();
return(_accueil);
}
//////////////////////////////////////////////////
/// @brief Modifie la visite
//////////////////////////////////////////////////
public void Visiteur_Anonyme_To_Member(int memberid, int visiteid)
{
String req = "UPDATE wmsVISITE SET visiteur_id=@VISITEUR where visite_id=@SESSIONADN";
DataServices oData = new DataServices(VisiteurDsn, req);
oData.AddParameter("@SESSIONADN", SqlDbType.Int, 0, ParameterDirection.Input, visiteid, "visite_id");
oData.AddParameter("@VISITEUR", SqlDbType.Int, 0, ParameterDirection.Input, memberid, "visiteur_id");
oData.Execute(DataServices.ExecutionMode.UPDATE);
oData.Dispose();
req = "UPDATE crmPUBLIC SET public_connexions =COALESCE(public_connexions, 0)+1, public_dernierevisite=@DATE WHERE public_id =@VISITEUR";
oData = new DataServices(VisiteurDsn, req);
oData.AddParameter("@VISITEUR", SqlDbType.Int, 0, ParameterDirection.Input, memberid, "public_id");
oData.AddParameter("@DATE", SqlDbType.DateTime, 0, ParameterDirection.Input, DateTime.Now, "public_dernierevisite");
oData.Execute(DataServices.ExecutionMode.UPDATE);
oData.Dispose();
}
//////////////////////////////////////////////////
/// @brief Construit un Datatable contenant les rubriques accessibles par le profil pour le site sélectionné
//////////////////////////////////////////////////
private DataTable Charge_Profil_Visiteur()
{
DataTable oDroits = new DataTable();
Object[] newRow = new object[0];
oDroits.Columns.Add(new DataColumn("page_id", Type.GetType("System.Int32")));
String req = "SELECT * FROM crmDROIT_PAGE WHERE profil_id IN (SELECT profil_id FROM crmDROIT_SITE WHERE site_id=@SITE) AND profil_id=@PROFIL";
DataServices oData = new DataServices(VisiteurDsn, req);
oData.AddParameter("@SITE", SqlDbType.Int, 0, ParameterDirection.Input, VisiteurSite, "site_id");
oData.AddParameter("@PROFIL", SqlDbType.Int, 0, ParameterDirection.Input, VisiteurProfil, "profil_id");
oData.GetStructures();
foreach (DataRow oRub in oData.UtilityDataTable.Rows)
{
newRow[0] = oRub["page_id"];
oDroits.Rows.Add(newRow);
}
oData.Dispose();
VisiteurHomePage = Charge_Visiteur_Homepage();
return(oDroits);
}
//////////////////////////////////////////////////
/// @brief Charge la table avec les données attendues
//////////////////////////////////////////////////
private DataTable Charge(string _requete, int _site = -1, string _champ = null)
{
oData = new DataServices(_dsn, _requete);
oData.UtilitySqlCommand = new SqlCommand(_requete, oData.UtilitySqlConnection);
if (_champ != null)
{
oData.AddParameter("@SITE", SqlDbType.Int, 0, ParameterDirection.Input, _site, _champ);
}
oData.GetStructures();
oData.Dispose();
if (_requete.IndexOf("wmsCOMPOSANT_PAGE") > 1 && _site != -1)
{
ChangeVariable("composants", _site);
}
return(oData.UtilityDataTable);
}
//////////////////////////////////////////////////
/// @brief Voir la procedure stocker "Visiteur_Enregistre_Hit" pour savoir comment l'utiliser
//////////////////////////////////////////////////
public void Visiteur_Enregistre_Hit(string _referer, string _domaine)
{
DataServices ds = new DataServices(VisiteurDsn, "Visiteur_Enregistre_Hit");
ds.UtilitySqlCommand.CommandType = CommandType.StoredProcedure;
ds.AddParameter("@referer", SqlDbType.VarChar, 255, ParameterDirection.Input, _referer, "referer_url");
ds.AddParameter("@domaine", SqlDbType.VarChar, 255, ParameterDirection.Input, _domaine, "referer_domaine");
ds.AddParameter("@site", SqlDbType.Int, 255, ParameterDirection.Input, VisiteurSite, "referer_site");
ds.AddParameter("@date", SqlDbType.DateTime, 255, ParameterDirection.Input, DateTime.Now, "hit_date");
ds.AddParameter("@session", SqlDbType.Int, 255, ParameterDirection.Input, VisiteurVisiteId, "visite_id");
ds.AddParameter("@rubrique", SqlDbType.Int, 255, ParameterDirection.Input, VisiteurPage, "page_id");
ds.AddParameter("@url", SqlDbType.VarChar, 255, ParameterDirection.Input, VisiteurPageURL, "hit_url");
ds.UtilitySqlCommand.ExecuteReader();
ds.Dispose();
}