Как составить статистику на сайт

• Введение
• База данных
• Модуль логирования
• Поисковые роботы и реферралы
• Расширение модуля
• Модернизация программного кода

create procedure p_SessionStart 
     @UserID           int = null, 
     @IPAddress        varchar(15), 
     @BrowserString    varchar(1024), 
     @ReferralURL      varchar(4096) = null, 
     @Site                varchar(100) = null, 
     @PageUrl          varchar(255) 
  as 
  begin tran 
  declare @PageID int 
  select @PageID = PageID from Page where PageURL = @PageUrl and (@Site is null and Site = @Site or Site is null) 
  if @PageID is null 
  begin 
     insert into Page (PageURL, Site) values (@PageUrl, @Site) 
     select @PageID = @@IDENTITY 
  end 
  insert into Session (UserID, DateStart, FirstPageID, LastPageID, DateEnd, IPAddress, BrowserString, ReferralURL) 
     values (@UserID, getDate(), @PageID, @PageID, getDate(), @IPAddress, @BrowserString, @ReferralURL) 
  commit 
  return @@identity 
  go 
  create procedure p_DoRequest 
     @SessionID  int, 
     @UserID     varchar(100) = null, 
     @Site          varchar(100) = null, 
     @PageUrl    varchar(255), 
     @QueryString   varchar(1024), 
     @IsPostBack    bit, 
     @IsAuthenticated     bit 
  as 
  begin tran 
  declare @PageID int 
  select @PageID = PageID from Page where PageURL = @PageUrl and (@Site is null and Site = @Site or Site is null) 
  if @PageID is null 
  begin 
     insert into Page (PageURL, Site) values (@PageUrl, @Site) 
     select @PageID = @@IDENTITY 
  end 
  insert into Request (PageID, SessionID, RequestDate, QueryString, IsPostBack, IsAuthenticated) 
     values (@PageID, @SessionID, getDate(), @QueryString, @IsPostBack, @IsAuthenticated) 
  update  
     Session 
  set 
     LastPageID = @PageID,  
     DateEnd = getdate() 
  where  
     SessionID = @SessionID 
  if @UserID is not null 
     update  
        Session 
     set 
        UserID = @UserID 
     where  
        SessionID = @SessionID 
  commit 
  go

public enum PersistIDPlace 
  { 
        Session, 
        Cookie 
  } 
  public class SiteStatsSettings 
  { 
        public string ConnectionString; 
        public string Site; 
        public PersistIDPlace SessionIDPlace; 
  }

public class SiteStatsConfigHandler : IConfigurationSectionHandler 
  { 
        public SiteStatsConfigHandler(){} 
        public object Create(object parent, object configContext, System.Xml.XmlNode section) 
        { 
              SiteStatsSettings ret = new SiteStatsSettings(); 
              ret.ConnectionString = section.SelectSingleNode("ConnectionString").InnerText; 
              ret.Site = section.SelectSingleNode("Site") != null ? section.SelectSingleNode("Site").InnerText : ""; 
              if(section.SelectSingleNode("SessionIDPlace") != null) 
                    ret.SessionIDPlace = (PersistIDPlace) Enum.Parse(typeof(PersistIDPlace), section.SelectSingleNode("SessionIDPlace").InnerText); 
              else 
                    ret.SessionIDPlace = PersistIDPlace.Cookie; 
              return ret; 
        } 
  }

<configSections> 
  <section name="siteStats" type="SiteStats.SiteStatsConfigHandler, SiteStats"/> 
  </configSections> 
  И теперь можно использовать секцию <siteStats> для задания параметров модуля: 
  <siteStats> 
        <ConnectionString>server=localhost;uid=sa;pwd=;database=SiteStats</ConnectionString> 
        <Site>Mania</Site> 
        <SessionIDPlace>Cookie</ SessionIDPlace> 
  </siteStats>

SiteStatsSettings settings = (SiteStatsSettings) ConfigurationSettings.GetConfig("siteStats");

public class SiteStatsBLL 
  { 
        private static int SessionStart(HttpContext context) 
        { 
              SiteStatsSettings settings = (SiteStatsSettings) ConfigurationSettings.GetConfig("siteStats"); 
              SqlConnection myConn = new SqlConnection(settings.ConnectionString); 
              SqlCommand myCmd = new SqlCommand("p_SessionStart", myConn); 
              myCmd.CommandType = CommandType.StoredProcedure; 
              if (context.User.Identity.IsAuthenticated) 
                    myCmd.Parameters.Add("@UserID", Int32.Parse(context.User.Identity.Name)); 
              myCmd.Parameters.Add("@IPAddress", context.Request.UserHostAddress); 
              myCmd.Parameters.Add("@BrowserString", context.Request.UserAgent == null ? "" : context.Request.UserAgent); 
              if (context.Request.UrlReferrer != null) 
                    myCmd.Parameters.Add("@ReferralURL", context.Server.UrlDecode(context.Request.UrlReferrer.ToString())); 
              if (settings.Site != "") 
                    myCmd.Parameters.Add("@Site", settings.Site); 
              myCmd.Parameters.Add("@PageURL", context.Request.FilePath); 
              myCmd.Parameters.Add("RETURN_VALUE", SqlDbType.Int); 
              myCmd.Parameters["RETURN_VALUE"].Direction = ParameterDirection.ReturnValue; 
              myConn.Open(); 
              myCmd.ExecuteNonQuery(); 
              myConn.Close(); 
              return (int) myCmd.Parameters["RETURN_VALUE"].Value; 
        } 
        public static void Request(HttpContext context) 
        { 
              SiteStatsSettings settings = (SiteStatsSettings) ConfigurationSettings.GetConfig("siteStats"); 
              int SessionID; 
              switch(settings.SessionIDPlace) 
              { 
                    case PersistIDPlace.Session: 
                    if (context.Session["SessionID"] != null) 
                          SessionID = (int) context.Session["SessionID"]; 
                    else 
                    { 
                          SessionID = SessionStart(context); 
                          context.Session["SessionID"] = SessionID; 
                    } 
                          break; 
                    default: 
                          if (context.Request.Cookies["SessionID"] != null) 
                               SessionID = Int32.Parse(context.Request.Cookies["SessionID"].Value); 
                          else 
                          { 
                               SessionID = SessionStart(context); 
                               context.Response.Cookies.Add(new HttpCookie("SessionID", SessionID.ToString())); 
                          } 
                          break; 
              } 
              SqlConnection myConn = new SqlConnection(settings.ConnectionString); 
              SqlCommand myCmd = new SqlCommand("p_DoRequest", myConn); 
              myCmd.CommandType = CommandType.StoredProcedure; 
              myCmd.Parameters.Add("@SessionID", SessionID); 
              if (context.User.Identity.IsAuthenticated) 
                    myCmd.Parameters.Add("@UserID", context.User.Identity.Name); 
              if (settings.Site != "") 
                    myCmd.Parameters.Add("@Site", settings.Site); 
              myCmd.Parameters.Add("@PageURL", context.Request.RawUrl.IndexOf("?") != -1 ? context.Request.RawUrl.Substring(0, context.Request.RawUrl.IndexOf("?")) : context.Request.RawUrl); 
              myCmd.Parameters.Add("@QueryString", context.Request.QueryString.ToString()); 
              myCmd.Parameters.Add("@IsAuthenticated", context.Request.IsAuthenticated); 
              myCmd.Parameters.Add("@IsPostBack", context.Request.HttpMethod == "POST"); 
              myConn.Open(); 
              myCmd.ExecuteNonQuery(); 
              myConn.Close(); 
        } 
  }

public class SiteStatsModule : IHttpModule 
  { 
        void IHttpModule.Init(HttpApplication context) 
        { 
              context.PostRequestHandlerExecute += new EventHandler(context_PostRequestHandlerExecute); 
        } 
        void IHttpModule.Dispose() 
        { 
        } 
        void context_PostRequestHandlerExecute(object sender, EventArgs e) 
        { 
              SiteStatsBLL.Request(HttpContext.Current); 
        } 
  }

<configSections>
          <section name="siteStats" type="SiteStats.SiteStatsConfigHandler, SiteStats"/>
  </configSections>

<siteStats> 
        <ConnectionString>строка подключения к БД</ConnectionString> 
        <Site>имя сайта</Site> 
        <SessionIDPlace>Место хранения идентификатора сессии (Cookie или Session)</SessionIDPlace> 
  </siteStats>

<httpModules> 
        <add name="SiteStatsModule" type="SiteStats.SiteStatsModule, SiteStats"/> 
  </httpModules>

ALTER procedure p_SessionStart 
     @UserID           int = null, 
     @IPAddress        varchar(15), 
     @BrowserString    varchar(1024), 
     @ReferralURL      varchar(4096) = null, 
     @Site                varchar(100) = null, 
     @PageUrl          varchar(255), 
     @BotID               int = null, 
     @SiteName            varchar(255) = null, 
     @Keyword             varchar(1000) = null, 
     @SearchEngineID      int = null 
  as 
  begin tran 
  declare @PageID int 
  select @PageID = PageID from Page where PageURL = @PageUrl and (@Site is null and Site = @Site or Site is null) 
  if @PageID is null 
  begin 
     insert into Page (PageURL, Site) values (@PageUrl, @Site) 
     select @PageID = @@IDENTITY 
  end 
  declare @SiteID   int 
  if @SiteName is not null 
  begin 
        select @SiteID = SiteID from Sites where Name = @SiteName 
        if @SiteID is null 
        begin 
              insert into Sites (Name) values (@SiteName) 
              select @SiteID = @@IDENTITY 
        end 
  end 
  declare @KeywordID      int 
  if @Keyword is not null 
  begin 
        select @KeywordID = KeywordID from Keywords where Keywords = @Keyword 
        if @KeywordID is null 
        begin 
              insert into Keywords (Keywords) values (@Keyword) 
              select @KeywordID = @@IDENTITY 
        end 
  end 
  insert into Session (UserID, DateStart, FirstPageID, LastPageID, DateEnd, IPAddress, BrowserString, ReferralURL, BotID, SiteID, SearchEngineID, KeywordID) 
     values (@UserID, getDate(), @PageID, @PageID, getDate(), @IPAddress, @BrowserString, @ReferralURL, @BotID, @SiteID, @SearchEngineID, @KeywordID) 
  commit 
  return @@identity

ALTER PROCEDURE p_Session_Fill 
        @DateStart  datetime = null, 
        @ClearBots  bit = 0 
  AS 
  if @DateStart is null 
        set @DateStart = dateadd(hh, -1, getDate())

if @ClearBots = 0 
  begin 
        update  
              Session  
        set  
              BotID = Bots.BotID  
        from 
              Bots 
        where  
              Session.BotID is null and DateStart > @DateStart and Mask <> '%bot%' and BrowserString like Mask 
        update  
              Session  
        set  
              BotID = Bots.BotID  
        from 
              Bots 
        where  
              Session.BotID is null and Mask = '%bot%' and DateStart > @DateStart and BrowserString like '%bot%' 
  end 
  else         
  begin 
        delete from Request where SessionID in (select SessionID from Session inner join Bots on BrowserString like Mask) 
        delete from Session where SessionID in (select SessionID from Session inner join Bots on BrowserString like Mask) 
  end

update  
        Session  
  set  
        SearchEngineID = SearchEngines.SearchEngineID  
  from 
        SearchEngines 
  where  
        ReferralURL is not null and DateStart > @DateStart and ReferralURL like SearchMask

declare @ReferralUrl varchar(4096) 
  declare @SessionID      int 
  declare @SiteName varchar(255) 
  declare @SiteID int 
  declare cur1 cursor for 
        select SessionID, ReferralURL from Session with (nolock) 
              where ReferralURL is not null and DateStart > @DateStart and SiteID is null 
  open cur1 
  fetch next from cur1 into @SessionID, @ReferralUrl 
  while @@fetch_status = 0 
  begin 
        set @SiteID = null 
        set @SiteName = substring(@ReferralUrl, 8, len(@ReferralUrl) - 7) 
        set @SiteName = substring(@SiteName, 1, charindex('/', @SiteName) - 1) 
        select @SiteID = SiteID from Sites with (nolock) where Name = @SiteName 
        if @SiteID is null 
        begin 
              insert into Sites(Name) 
                    values (@SiteName) 
              set @SiteID = @@identity 
        end 
        update 
              Session 
        set 
              SiteID = @SiteID 
        where 
              SessionID = @SessionID 
        fetch next from cur1 into @SessionID, @ReferralUrl 
  end 
  close cur1 
  deallocate cur1

declare @SearchEngineID int 
  declare @KeywordLabel varchar(10) 
  declare cur2 cursor for 
        select  
              SessionID,  
              Session.SearchEngineID,  
              ReferralURL,  
              KeywordsMask 
        from 
              Session with (nolock) inner join SearchEngines with (nolock) on Session.SearchEngineID = SearchEngines.SearchEngineID 
        where 
              KeywordID is null and DateStart > @DateStart 
  open cur2 
  fetch next from cur2 into @SessionID, @SearchEngineID, @ReferralUrl, @KeywordLabel 
  while @@fetch_status = 0 
  begin 
        declare @keywords varchar(1000) 
        declare @pos int 
        set @pos = null 
        set @keywords = null 
        set @pos = CHARINDEX(@KeywordLabel, @ReferralUrl) 
        if @pos > 0 
        begin 
              set @keywords = SUBSTRING(@ReferralUrl, @pos + len(@KeywordLabel), len(@ReferralUrl) - len(@KeywordLabel) - @pos + 1) 
              if @KeywordLabel <> '/' 
              begin 
                    set @pos = CHARINDEX('&', @keywords) 
                    if @pos > 0 
                          set @keywords = substring(@keywords, 1, @pos - 1) 
              end 
              else 
              begin 
                    while @pos <> 0 
                    begin 
                          set @keywords = SUBSTRING(@keywords, @pos + 1, len(@keywords) - @pos) 
                          set @pos = CHARINDEX('/', @keywords) 
                    end 
              end 
        end 
        if @keywords is not null and @keywords <> '' 
        begin 
              set @keywords = REPLACE(@keywords, '+', ' ') 
              set @keywords = REPLACE(@keywords, '%20', ' ') 
              set @keywords = ltrim(rtrim(@keywords)) 
              if @keywords <> '' 
              begin 
                    declare @KeywordID int 
                    set @KeywordID = null 
                    select @KeywordID = KeywordID from Keywords with (nolock) where Keywords = @keywords 
                    if @KeywordID is null 
                    begin 
                          insert into Keywords (Keywords) 
                               values (@keywords) 
                          set @KeywordID = @@identity 
                    end 
                    update 
                          Session 
                    set 
                          KeywordID = @KeywordID 
                    where 
                          SessionID = @SessionID 
              end 
        end 
        fetch next from cur2 into @SessionID, @SearchEngineID, @ReferralUrl, @KeywordLabel 
  end 
  close cur2 
  deallocate cur2

public bool ParseAddData;
  public bool PersistBots;

ret.ParseAddData = section.SelectSingleNode("ParseAddData") != null ? bool.Parse(section.SelectSingleNode("ParseAddData").InnerText) : false;
  ret.PersistBots = section.SelectSingleNode("PersistBots") != null ? bool.Parse(section.SelectSingleNode("PersistBots").InnerText) : true;

<siteStats> 
              <ConnectionString>server=localhost;uid=sa;pwd=;database=SiteStats</ConnectionString> 
              <SessionIDPlace>Cookie</SessionIDPlace> 
              <ParseAddData>true</ParseAddData> 
              <PersistBots>true</PersistBots> 
  </siteStats>

private static SortedList bots; 
  private static SortedList searchEngines; 
  private class SearchEngine 
  { 
        public Regex Mask; 
        public string Key; 
        public SearchEngine(Regex mask, string key) 
        { 
              Mask = mask; 
              Key = key; 
        } 
  } 
  static SiteStatsBLL() 
  { 
        bots = new SortedList(); 
        SiteStatsSettings settings = (SiteStatsSettings) ConfigurationSettings.GetConfig("siteStats"); 
        SqlConnection myConn = new SqlConnection(settings.ConnectionString); 
        SqlCommand myCmd = new SqlCommand("select * from Bots", myConn); 
        myConn.Open(); 
        SqlDataReader rdr = myCmd.ExecuteReader(); 
        while(rdr.Read()) 
        { 
              Regex mask = new Regex(rdr["Mask"].ToString().Replace("%", "(.+?)"), RegexOptions.Compiled | RegexOptions.IgnoreCase); 
              bots.Add((int) rdr["BotID"], mask); 
        } 
        rdr.Close(); 
        searchEngines = new SortedList(); 
        myCmd.CommandText = "select * from SearchEngines"; 
        rdr = myCmd.ExecuteReader(); 
        while(rdr.Read()) 
        { 
              Regex mask = new Regex(rdr["SearchMask"].ToString().Replace("%", "(.+?)"), RegexOptions.Compiled | RegexOptions.IgnoreCase); 
              bots.Add((int) rdr["SearchEngineID"], new SearchEngine(mask, rdr["KeywordMask"].ToString())); 
        } 
        rdr.Close(); 
        myConn.Close(); 
  }

int BotID = 0; 
  int SearchEngineID = 0; 
  string SiteName = ""; 
  string Keyword = ""; 
  if(settings.ParseAddData) 
  { 
        foreach(object key in bots.Keys) 
              if(((Regex) bots[key]).IsMatch(context.Request.UserAgent == null ? "" : context.Request.UserAgent)) 
              { 
                    BotID = (int) key; 
                    break; 
              } 
        if(BotID != 0 && !settings.PersistBots) 
              return -1; 
        if(context.Request.UrlReferrer != null && context.Request.UrlReferrer.ToString() != "") 
        { 
              foreach(object key in searchEngines.Keys) 
                    if(((SearchEngine) searchEngines[key]).Mask.IsMatch(context.Request.UrlReferrer.ToString())) 
                    { 
                          SearchEngineID = (int) key; 
                          foreach(string param in context.Request.UrlReferrer.Query.Split('&')) 
                               if(param.StartsWith(((SearchEngine) searchEngines[key]).Key)) 
                               { 
                                     Keyword = param.Replace(((SearchEngine) searchEngines[key]).Key, ""); 
                                     break; 
                               } 
                          break; 
                    } 
              if(SearchEngineID == 0) 
                    SiteName = context.Request.UrlReferrer.Host; 
        } 
  }

if(SessionID == -1)
        return;

if(settings.ParseAddData) 
  { 
        if (BotID != 0) 
              myCmd.Parameters.Add("@BotID", BotID); 
        if (SearchEngineID != 0) 
              myCmd.Parameters.Add("@SearchEngineID", SearchEngineID); 
        if (Keyword != "") 
              myCmd.Parameters.Add("@Keyword", Keyword); 
        if (SiteName != "") 
              myCmd.Parameters.Add("@SiteName", SiteName); 
  }