context: using System;
  using System.Collections.Generic;
  using System.Data;
  using System.Data.SqlClient;
  using System.Security.Permissions;
  using System.Web.Caching;
  using MsSqlDBUtility;
  using System.Security;
  using One.Net.BLL.Model.Attributes;
  using System.Threading;
  using System.Linq;
  // for old stuff
  
  namespace One.Net.BLL.DAL
  {
  	public class DbWebsite
  	{
          public Dictionary<int, string> FindPages(string keyword)
          {
              Dictionary<int, string> pageIds = new Dictionary<int, string>();
  
              var paramsToPass = new SqlParameter[2];
              paramsToPass[0] = new SqlParameter("@titleSearch", SqlDbType.NVarChar, 255);
              paramsToPass[0].Value = "%" + keyword + "%";
              paramsToPass[1] = new SqlParameter("@languageId", Thread.CurrentThread.CurrentCulture.LCID);
  
              // search page content first
              var sql = @"SELECT DISTINCT * FROM (
                          SELECT DISTINCT TOP(10) p.id pageId, cds.title pageTitle
                          FROM pages p
                          INNER JOIN content_data_store cds ON cds.content_fk_id=p.content_fk_id
                          WHERE p.publish=0 AND cds.language_fk_id=@languageId";
              sql += " AND (cds.title LIKE @titleSearch OR cds.teaser LIKE @titleSearch OR cds.html LIKE @titleSearch) ";
  
              // now also search TextContent module instances within pages.
              // note that UNION operator removes duplicates
              sql += @"UNION
                      SELECT DISTINCT TOP(10) mi.pages_fk_id pageId, cds.title pageTitle
  	                FROM module_instance mi
  	                INNER JOIN module_settings ms ON ms.module_instance_fk_pages_fk_publish = 0 AND ms.module_instance_fk_id=mi.id AND ms.settings_list_fk_id = (SELECT id FROM settings_list WHERE subsystem='TextContent' AND name='ContentId')
  	                INNER JOIN content_data_store cds ON cds.content_fk_id=ms.value
                      WHERE mi.pages_fk_publish=0 ";
              sql += @" AND (cds.title LIKE @titleSearch OR cds.teaser LIKE @titleSearch OR cds.html LIKE @titleSearch)
                      ) t
                  ORDER BY pageTitle ASC ";
  
              using (var reader = SqlHelper.ExecuteReader(SqlHelper.ConnStringMain, CommandType.Text, sql, paramsToPass))
              {
                  while (reader.Read())
                  {
                      var pageId = (int)reader["pageId"];
                      var title = (string)reader["pageTitle"];
                      try
                      {
                          pageIds.Add(pageId, title);
                      }
                      catch { }
                  }
              }
  
              return pageIds;
          }
  
          public PagedList<BOPage> ListPages(int webSiteId, int languageId, bool publishFlag, bool filterChanged = false)
          {
              PagedList<BOPage> pages = new PagedList<BOPage>();
  
              var paramsToPass = new SqlParameter[3];
              paramsToPass[0] = new SqlParameter("@websiteID", webSiteId);
              paramsToPass[1] = new SqlParameter("@languageId", languageId);
              paramsToPass[2] = new SqlParameter("@publishFlag", publishFlag);
  
  
              string sql = @"SELECT cds.title, cds.subtitle, cds.teaser, cds.html, c.principal_created_by, c.date_created, 
              c.principal_modified_by, c.date_modified, c.votes, c.score, c.id ContentId, p.id PageId, p.pages_fk_id, p.menu_group, p.idx, 
  			t.id TemplateId, t.name, level, pending_delete, changed, 
              break_persistence, web_site_fk_id, redirectToUrl, [viewGroups], [editGroups], [requireSSL], sub_route_url
       FROM [dbo].[pages] p	
       INNER JOIN [dbo].[content_data_store] cds ON cds.content_fk_id = p.content_fk_id AND cds.language_fk_id = @languageId
       INNER JOIN [dbo].[content] c ON c.id = p.content_fk_id
       INNER JOIN [dbo].[template] t ON t.id = p.template_fk_id AND template_type = '3'
  	   WHERE p.publish = @publishFlag AND p.web_site_fk_id=@websiteID";
                  if (filterChanged)
                  {
                      sql += " AND p.changed = 1";
                  }
                      
             sql += " ORDER BY PageId ASC";
  
              using (var rdr = SqlHelper.ExecuteReader(SqlHelper.ConnStringMain, CommandType.Text,
                  sql, paramsToPass))
              {
                  while (rdr.Read())
                  {
                      BOPage page = new BOPage();
                      PopulatePage(rdr, page, languageId);
                      LoadPageLinks(page, publishFlag, languageId);
                      LoadPageSettings(page, publishFlag);
                      pages.Add(page);
                  }
  
                  if (rdr.NextResult())
                  {
                      if (rdr.Read())
                      {
                          pages.AllRecords = rdr.GetInt32(0);
                      }
                  }
              }
  
              return pages;
          }
  
          [SqlClientPermission(SecurityAction.Demand, Unrestricted = true)]
          public List<BOPage> GetSiteStructure(int webSiteId, int languageId, bool publishFlag)
          {
              List<BOPage> structure = new List<BOPage>();
              SqlParameter[] parms;
              parms = new SqlParameter[] {
  					new SqlParameter("@websiteID", webSiteId),
  					new SqlParameter("@LCID", languageId),
                      new SqlParameter("@publishFlag", publishFlag)
                      };
              using (var rdr = SqlHelper.ExecuteReader(SqlHelper.ConnStringMain, CommandType.Text,
                  @"SELECT p.id AS ID, p.pages_fk_id AS Parent, CAST(p.publish AS int) AS publish, il.par_link, sc2.title AS Title, sc2.subtitle AS SubTitle, sc2.teaser AS Teaser, sub_route_url,
                      t.name AS Template, t.id AS TemplateID, p.content_fk_id AS ContentID, menu_group, idx, changed, pending_delete, 
                      p.level, p.redirectToUrl,  [viewGroups], [editGroups], [requireSSL], date_modified, date_created
                       FROM [dbo].pages p
                       INNER JOIN [dbo].content  sc1 ON sc1.id = p.content_fk_id 
                       INNER JOIN [dbo].content_data_store sc2 ON sc2.content_fk_id = p.content_fk_id AND sc2.language_fk_id = @LCID
                       INNER JOIN [dbo].template t ON t.id = p.template_fk_id
                       INNER JOIN [dbo].int_link il ON il.pages_fk_id = p.id AND il.pages_fk_publish= @publishFlag AND il.language_fk_id = @LCID
                       WHERE p.publish = @publishFlag AND p.web_site_fk_id = @websiteID
                       ORDER BY p.pages_fk_id, p.idx", parms)) //out sqlCacheDep,
              // beware of the p.idx order: it should be OK as long as long as pages are added using a sequenced id order.
              // if order is incorrect, we'll get an exception later when building sitemap
              {
                  int _indexID = rdr.GetOrdinal("ID");
                  int _indexUrl = rdr.GetOrdinal("par_link");
                  int _indexTitle = rdr.GetOrdinal("Title");
                  int _indexSubTitle = rdr.GetOrdinal("SubTitle");
                  int _indexTeaser = rdr.GetOrdinal("Teaser");
                  int _indexParent = rdr.GetOrdinal("Parent");
                  int _indexPublish = rdr.GetOrdinal("publish");
                  int _indexTemplate = rdr.GetOrdinal("Template");
                  int _indexTemplateID = rdr.GetOrdinal("TemplateID");
                  int _indexContentID = rdr.GetOrdinal("ContentID");
                  int _indexMenuGroup = rdr.GetOrdinal("menu_group");
                  int _indexOrder = rdr.GetOrdinal("idx");
                  int _indexChanged = rdr.GetOrdinal("changed");
                  int _indexPendingDelete = rdr.GetOrdinal("pending_delete");
                  int _indexLevel = rdr.GetOrdinal("level");
                  int _indexRedirect = rdr.GetOrdinal("redirectToUrl");
  
                  int _indexViewGroups = rdr.GetOrdinal("viewGroups");
                  int _indexEditGroups = rdr.GetOrdinal("editGroups");
                  int _indexRequireSSL = rdr.GetOrdinal("requireSSL");
  
                  int _sub_route_url = rdr.GetOrdinal("sub_route_url");
  
                  
  
  
                  while (rdr.Read())
                  {
                      BOPage sitePage = new BOPage();
                      sitePage.Id = rdr.GetInt32(_indexID);
  
                      if (rdr[_indexParent] != DBNull.Value)
                      {
                          sitePage.ParentId = rdr.GetInt32(_indexParent);
                      }
  
                      sitePage.MenuGroup = rdr.GetInt32(_indexMenuGroup);
                      sitePage.Order = rdr.GetInt32(_indexOrder);
                      sitePage.LanguageId = languageId;
                      sitePage.ContentId = rdr.GetInt32(_indexContentID);
                      sitePage.Title = rdr[_indexTitle] != DBNull.Value ? rdr.GetString(_indexTitle) : "";
                      sitePage.SubTitle = rdr[_indexSubTitle] != DBNull.Value ? rdr.GetString(_indexSubTitle) : "";
                      sitePage.Teaser = rdr[_indexTeaser] != DBNull.Value ? rdr.GetString(_indexTeaser) : "";
  
  
                      sitePage.ParLink = rdr.GetString(_indexUrl);
                      sitePage.Template = new BOTemplate();
                      sitePage.Template.Type = "3";
                      sitePage.Template.Id = rdr.GetInt32(_indexTemplateID);
                      sitePage.Template.Name = rdr.GetString(_indexTemplate);
                      sitePage.IsChanged = rdr.GetBoolean(_indexChanged);
                      sitePage.MarkedForDeletion = rdr.GetBoolean(_indexPendingDelete);
                      sitePage.Level = rdr.GetInt32(_indexLevel);
                      sitePage.RedirectToUrl = rdr.GetString(_indexRedirect);
                      sitePage.FrontEndRequireGroupList = rdr.GetString(_indexViewGroups);
                      sitePage.EditRequireGroupList = rdr.GetString(_indexEditGroups);
                      sitePage.RequireSSL = rdr.GetBoolean(_indexRequireSSL);
                      if (rdr[_sub_route_url] != DBNull.Value)
                          sitePage.SubRouteUrl = rdr.GetString(_sub_route_url);
  
                      sitePage.DateCreated = (DateTime) rdr["date_created"];
                      sitePage.DateModified = rdr["date_modified"] == DBNull.Value ? new DateTime?() : (DateTime) rdr["date_modified"];
                      structure.Add(sitePage);
                  }
              }
  
              return structure;
          }
  
          public List<int> ListChildrenIds(int pageId, bool publishFlag)
          {
              List<int> pageIds = new List<int>();
              
              SqlParameter[] parms = 
                     new SqlParameter[] {
                             new SqlParameter("@pageId", pageId),
                             new SqlParameter("@PublishFlag", publishFlag)};
              using (var rdr = SqlHelper.ExecuteReader(SqlHelper.ConnStringMain, CommandType.Text,
                  @"SELECT p.id AS ID
                       FROM [dbo].pages p
                       WHERE p.pages_fk_id = @pageId AND p.publish = @PublishFlag
                       ORDER BY p.idx", parms))
              {
                  while (rdr.Read())
                  {
                      pageIds.Add(rdr.GetInt32(0));
                  }
              }
  
              return pageIds;
          }
  
  	    public List<BOWebSite> List()
  		{
  			List<BOWebSite> websiteList = new List<BOWebSite>();
              using (var rdr = SqlHelper.ExecuteReader(SqlHelper.ConnStringMain, CommandType.Text,
                  @"SELECT w.id, w.content_fk_id, 
  	                    (SELECT p.id FROM [dbo].[pages] p 
  	                     WHERE publish = 0 AND web_site_fk_id = w.id AND pages_fk_id IS NULL) AS rootPageId
                      FROM [dbo].[web_site] w"))
              {
                  while (rdr.Read())
                  {
                      BOWebSite website = new BOWebSite();
                      website.Id = rdr.GetInt32(0);
                      website.ContentId = rdr.GetInt32(1);
                      if (rdr[2] != DBNull.Value)
                          website.RootPageId = rdr.GetInt32(2);
                      websiteList.Add(website);
                  }
              }
  
              foreach (BOWebSite site in websiteList)
              {
                  SqlParameter paramsToPass = new SqlParameter("@webSiteID", site.Id);
                  using (var rdr = SqlHelper.ExecuteReader(SqlHelper.ConnStringMain, CommandType.Text,
                  @"SELECT sl.name, ISNULL( ws.value, sl.default_value), sl.type, sl.user_visibility
                      FROM [dbo].[settings_list] sl 
                      LEFT JOIN [dbo].[web_site_settings] ws ON  sl.id = ws.settings_list_fk_id AND web_site_fk_id = @webSiteID
                      WHERE sl.[subsystem] = 'WebSite'", paramsToPass))
                  {
                      while (rdr.Read())
                      {
                          BOSetting setting = new BOSetting(rdr.GetString(0), rdr.GetString(2).ParseToEnum<SettingTypeEnum>(), rdr.GetString(1), rdr.GetString(3).ParseToEnum<VisibilityEnum>());
                          site.Settings.Add(rdr.GetString(0), setting);
                      }
                  }
              }
  			return websiteList;
  		}
  
  		public void Change(BOWebSite website, string connString = "")
  		{
              SqlParameter[] parms;
              if (website.IsNew)
              {
                  if (!website.ContentId.HasValue)
                  {
                      throw new InvalidOperationException("add Content before calling add website");
                  }
                  parms = new SqlParameter[] { 
                      new SqlParameter("@ContentId", website.ContentId),
                      new SqlParameter("@id", SqlDbType.Int)
                  };
                  parms[1].Direction = ParameterDirection.Output;
                  parms[1].DbType = DbType.Int32;
  
                  SqlHelper.ExecuteNonQuery(string.IsNullOrWhiteSpace(connString) ? SqlHelper.ConnStringMain : connString, CommandType.Text,
                  @"INSERT INTO [dbo].[web_site] (content_fk_id) VALUES (@ContentId); SET @id=SCOPE_IDENTITY()", parms);
                  website.Id = (int)parms[1].Value;
  
                  parms = new SqlParameter[] {
                      new SqlParameter("@Id", website.Id),
                      new SqlParameter("@Name", "PrimaryLanguageId"),
                      new SqlParameter("@Value", website.LanguageId)
                  };
  
                  SqlHelper.ExecuteNonQuery(string.IsNullOrWhiteSpace(connString) ? SqlHelper.ConnStringMain : connString, CommandType.StoredProcedure, "[dbo].[ChangeWebSiteSetting]", parms);
              }
  
              parms = new SqlParameter[] {
                  new SqlParameter("@Id", website.Id),
                  new SqlParameter("@Name", SqlDbType.VarChar, 255),
                  new SqlParameter("@Value", SqlDbType.VarChar, 4000)
              };
  
              foreach (BOSetting setting in website.Settings.Values)
              {
                  parms[1].Value = setting.Name;
                  parms[2].Value = setting.Value;
                  SqlHelper.ExecuteNonQuery(string.IsNullOrWhiteSpace(connString) ? SqlHelper.ConnStringMain : connString, CommandType.StoredProcedure, "ChangeWebSiteSetting", parms);
              }
  		}
  
          /* ----------------------------------------------------------- */
  
          public void ChangePage(BOPage page)
          {
              if (page == null)
                  return;
  
              var p = new SqlParameter[3]  {
                  new SqlParameter("@WebSiteId", page.WebSiteId),
                  new SqlParameter("@PublishFlag", page.PublishFlag),
                  new SqlParameter("@Id", page.Id)
              };
              var exists = (int) SqlHelper.ExecuteScalar(SqlHelper.ConnStringMain, CommandType.Text, "SELECT COUNT(id) FROM pages WHERE id = @Id AND publish = @PublishFlag AND web_site_fk_id = @WebSiteId", p) > 0;
              var sql = "";
              if (exists)
              {
                  sql = @"UPDATE [dbo].[pages] SET break_persistence = @BreakPersistance, [level] = @Level, 
  		content_fk_id = @ContentId, template_fk_id = @TemplateId,
  		pages_fk_publish = @PublishFlag, pages_fk_id = @ParentId, changed = @Changed, 
  		menu_group = @MenuGroup, idx = @Order, web_site_fk_id = @WebSiteId, pending_delete = @PendingDelete,
  		redirectToUrl = @redirectToUrl, viewGroups = @viewGroups, editGroups = @editGroups, requireSSL = @requireSSL, sub_route_url = @SubRouteUrl
  		WHERE id = @Id AND publish = @PublishFlag";
  
              }
              else
              {
                  if (!page.PublishFlag)
                  {
                      p = new SqlParameter[2]  {
                          new SqlParameter("@sequence","pages"),
                          new SqlParameter("@sequence_id", 0)
                      };
                      p[1].Direction = ParameterDirection.Output;
                      SqlHelper.ExecuteNonQuery(SqlHelper.ConnStringMain, CommandType.StoredProcedure, "[nextval]", p);
                      var newId = (long) p[1].Value;
                      page.Id = Convert.ToInt32(newId);
                      if (page.Order == 0)
                          page.Order = Convert.ToInt32(newId);
                  }
                  sql = @"INSERT INTO pages 
  		(id,  break_persistence, level, publish, content_fk_id, template_fk_id,
  		pages_fk_publish, pages_fk_id, changed, menu_group, idx, web_site_fk_id, pending_delete,
  		redirectToUrl, viewGroups, editGroups, requireSSL, sub_route_url)
  		VALUES
  		(@Id, @BreakPersistance, @Level, @PublishFlag, @ContentId, @TemplateId,
  		@PublishFlag, @ParentId, @Changed, @MenuGroup, @Order, @WebSiteId, @PendingDelete,
  		@redirectToUrl, @viewGroups, @editGroups, @requireSSL, @SubRouteUrl )";
              }
  
              SqlParameter pageParentIdParam = new SqlParameter("@ParentId", SqlDbType.Int);
              pageParentIdParam.IsNullable = true;
              if (page.ParentId.HasValue)
              {
                  pageParentIdParam.Value = page.ParentId.Value;
              }
              else
              {
                  pageParentIdParam.Value = DBNull.Value;
              }
  
              var parms = new SqlParameter[] {
  				new SqlParameter("@Id", page.Id),
  				pageParentIdParam,
                  new SqlParameter("@PublishFlag", page.PublishFlag),
                  new SqlParameter("@Changed", page.IsChanged),
                  new SqlParameter("@Order", page.Order),
                  new SqlParameter("@PendingDelete", page.MarkedForDeletion),
                  new SqlParameter("@MenuGroup", page.MenuGroup),
                  new SqlParameter("@BreakPersistance", page.BreakPersistance),
                  new SqlParameter("@Level", page.Level),
                  new SqlParameter("@ContentId", page.ContentId),
                  new SqlParameter("@TemplateId", page.Template.Id),
                  new SqlParameter("@WebSiteId", page.WebSiteId),
                  new SqlParameter("@redirectToUrl", page.RedirectToUrl), 
                  new SqlParameter("@viewGroups", page.FrontEndRequireGroupList),
                  new SqlParameter("@editGroups", page.EditRequireGroupList),
                  new SqlParameter("@requireSSL", page.RequireSSL),
                  new SqlParameter("@SubRouteUrl", page.SubRouteUrl)
              };
  
              SqlHelper.ExecuteNonQuery(SqlHelper.ConnStringMain, CommandType.Text, sql, parms);
              //page.Id = (int) parms[0].Value;
  
              parms = new SqlParameter[] {
                  new SqlParameter("@Id", page.Id),
  				new SqlParameter("@PublishFlag", page.PublishFlag),
                  new SqlParameter("@Name", SqlDbType.VarChar, 255),
                  new SqlParameter("@Value", SqlDbType.VarChar, 4000)
              };
              foreach (BOSetting setting in page.Settings.Values)
              {
                  parms[2].Value = setting.Name;
                  parms[3].Value = setting.Value;
                  SqlHelper.ExecuteNonQuery(SqlHelper.ConnStringMain, CommandType.StoredProcedure, "ChangePageSetting", parms);
              }
  
              parms = new SqlParameter[] {
                  new SqlParameter("@Id", page.Id),
  				new SqlParameter("@PublishFlag", page.PublishFlag),
                  new SqlParameter("@languageID", page.LanguageId),
                  new SqlParameter("@par_link", page.ParLink),
                  new SqlParameter("@par_link_name", "[ " + page.ParLink + " ]")
              };
              SqlHelper.ExecuteNonQuery(SqlHelper.ConnStringMain, CommandType.StoredProcedure, "ChangeIntLink", parms);
              
          }
  
          public void DeletePage(int pageID, bool publishFlag)
          {
              SqlParameter[] parms = new SqlParameter[] {
                  new SqlParameter("@Id", pageID),
  				new SqlParameter("@PublishFlag", publishFlag)
              };
              SqlHelper.ExecuteNonQuery(SqlHelper.ConnStringMain, CommandType.StoredProcedure,
                  @"DeletePage", parms);
          }
  
          public int GetMaxModuleInstanceIdx(int pageId, bool publishFlag, int placeHolderId)
          {
              var sql = @"SELECT ISNULL(MAX(idx), -1) idx 
                          FROM [dbo].[module_instance] 
                          WHERE pages_fk_id=@pageId and pages_fk_publish=@publishFlag AND place_holder_fk_id=@placeHolderId";
  
              return Int32.Parse(SqlHelper.ExecuteScalar(SqlHelper.ConnStringMain, CommandType.Text, sql,
                  new SqlParameter("@pageId", pageId),
                  new SqlParameter("@publishFlag", publishFlag),
                  new SqlParameter("@placeHolderId", placeHolderId)
                  ).ToString());
          }
  
          public BOPage GetPage(int pageId, bool publishFlag, int languageId)
          {
              //cacheDep = null;
              //SqlCacheDependency sqlCacheDep = null;
              BOPage page = null;
              SqlParameter[] parms = new SqlParameter[] {
  					new SqlParameter("@pageID", pageId),
  					new SqlParameter("@LCID", languageId),
                      new SqlParameter("@publishFlag", publishFlag)
                      };
              using (var rdr = SqlHelper.ExecuteReader(SqlHelper.ConnStringMain, CommandType.Text,
                  @"SELECT  cds.title, cds.subtitle, cds.teaser, cds.html, c.principal_created_by, c.date_created, 
                            c.principal_modified_by, c.date_modified, c.votes, c.score, c.id ContentId, p.id PageId, p.pages_fk_id, p.menu_group, p.idx,
  			              t.id TemplateId, t.name, level, pending_delete, changed, 
                            break_persistence, web_site_fk_id, redirectToUrl, [viewGroups], [editGroups], [requireSSL], p.sub_route_url
                    FROM [dbo].[pages] p	
                    INNER JOIN [dbo].[content_data_store] cds ON cds.content_fk_id = p.content_fk_id AND cds.language_fk_id = @LCID
                    INNER JOIN [dbo].[content] c ON c.id = p.content_fk_id
                    INNER JOIN [dbo].[template] t ON t.id = p.template_fk_id AND template_type = '3'
  	              WHERE p.publish = @publishFlag AND p.id = @pageID", parms))
              {
                  //cacheDep = (CacheDependency)sqlCacheDep;
  
                  if (rdr.Read())
                  {
                      page = new BOPage();
                      PopulatePage(rdr, page, languageId);
                  }
              }
              if (page == null)
              {
                  return null;
              }
  
              if (page.IsChanged && !page.PublishFlag)  
              {
                  // under these two conditions it makes sense to check if the page has an online version at all
                  object result = SqlHelper.ExecuteScalar(SqlHelper.ConnStringMain, CommandType.Text,
                      @"SELECT id FROM [dbo].[pages] WHERE id = @pageID AND publish = 1", parms);
                  if (result == null)
                  {
                      page.IsNew = true;
                  }
              }
  
              // ex ListPageModInstances stored procedure
              parms = new SqlParameter[] {
  					new SqlParameter("@CurrentNodeID ", pageId),
  					new SqlParameter("@LanguageID ", languageId),
                      new SqlParameter("@CurrentPublishFlag", publishFlag)
                      };
  
              var pagesFromPath = new List<int>();
              using (var rdr = SqlHelper.ExecuteReader(SqlHelper.ConnStringMain,
                  CommandType.StoredProcedure, "[dbo].[GetPath]", parms))
              {
                  while (rdr.Read())
                  {
                      pagesFromPath.Add((int)rdr["NodeID"]);
                  }
              }
  
              var currentNodeDepth = 0;
              foreach (var nodeId in pagesFromPath)
              {
                  using (var rdr = SqlHelper.ExecuteReader(SqlHelper.ConnStringMain, CommandType.Text, 
        @"SELECT mi.id, mi.idx, mi.module_fk_id, mod.name, mi.date_created,
                      mi.place_holder_fk_id, ph.place_holder_id,
  		            mi.persistent_from, mi.persistent_to
          FROM module_instance mi
  	    LEFT JOIN pages p ON mi.pages_fk_id = p.id AND p.publish = mi.pages_fk_publish
  	    LEFT JOIN module mod ON mi.module_fk_id = mod.id
  		INNER JOIN place_holder ph ON ph.id = mi.place_holder_fk_id
      WHERE 
          mi.pages_fk_id = @nodeId AND 
          mi.pages_fk_publish = @nodePublishFlag
      ORDER BY mi.place_holder_fk_id, mi.idx ASC", new SqlParameter("@nodeId", nodeId), new SqlParameter("@nodePublishFlag", publishFlag)))
                  {
                      while (rdr.Read())
                      {
                          if (rdr[0] != DBNull.Value)
                          {
                              var from = (int)rdr["persistent_from"];
                              var to = (int)rdr["persistent_to"];
                              
                              var moduleInstance = new BOModuleInstance 
                              { 
                                  Id = (int) rdr["id"],
                                  Order = (int)rdr["idx"],
                                  ModuleId = (int)rdr["module_fk_id"],
                                  ModuleName = (string) rdr["name"],
                                  PlaceHolderId = (int)rdr["place_holder_fk_id"],
                                  PageId = nodeId,
                                  DateCreated = rdr["date_created"] == DBNull.Value ? DateTime.MinValue : (DateTime)rdr["date_created"]
                              };
                              moduleInstance.IsInherited = currentNodeDepth != page.Level;
  
                              if (moduleInstance.IsInherited && !(page.Level >= from && page.Level <= to))
                              {
                                  continue;
                              }
  
                              if (!page.PlaceHolders.ContainsKey(moduleInstance.PlaceHolderId))
                              {
                                  BOPlaceHolder placeHolder = new BOPlaceHolder();
                                  placeHolder.Id = moduleInstance.PlaceHolderId;
                                  placeHolder.Name = (string) rdr["place_holder_id"];
                                  page.PlaceHolders.Add(moduleInstance.PlaceHolderId, placeHolder);
                              }
                              if (!(page.BreakPersistance && moduleInstance.IsInherited))
                              {
                                  page.PlaceHolders[moduleInstance.PlaceHolderId].ModuleInstances.Add(moduleInstance);
                              } 
                          }
                      }
                  }
                  currentNodeDepth++;
              }
              LoadPageLinks(page, publishFlag, languageId);
              LoadPageSettings(page, publishFlag);
              return page;
          }
  
          protected void LoadPageLinks(BOPage page, bool publishFlag, int languageId)
          {
              var parms = new SqlParameter[] {
  					new SqlParameter("@CurrentNodeID", page.Id),
  					new SqlParameter("@CurrentPublishFlag", publishFlag),
                      new SqlParameter("@LanguageID", languageId)
                      };
              string par_link = "";
              using (var rdr = SqlHelper.ExecuteReader(SqlHelper.ConnStringMain, CommandType.StoredProcedure, "[dbo].[GetPath]", parms))
              {
                  while (rdr.Read())
                  {
                      par_link = rdr.GetString(1);
                      if (par_link.Length != 0)
                      {
                          page.URI += "/" + par_link;
                      }
                      int parPageId = rdr.GetInt32(0);
                      if (parPageId != page.Id)
                      {
                          page.parentPagesSimpleList += "pp" + parPageId + " ";
                      }
                  }
              }
              if (page.IsRoot)
              {
                  page.URI = "/";
              }
  
              page.ParLink = par_link;
          }
  
          protected void LoadPageSettings(BOPage page, bool publishFlag)
          {
              var parms = new SqlParameter[] {
  					new SqlParameter("@PageId", page.Id),
  					new SqlParameter("@PublishFlag", publishFlag)
                      };
  
              using (var rdr = SqlHelper.ExecuteReader(SqlHelper.ConnStringMain, CommandType.Text,
                  @"SELECT sl.name AS SettingName, ISNULL(ps.value, sl.default_value) as Value,
                           sl.type Type, sl.user_visibility UserVisibility
                    FROM [dbo].[settings_list] sl
                    LEFT JOIN [dbo].[pages_settings] ps ON  sl.id = ps.settings_list_fk_id AND ps.pages_fk_publish=@PublishFlag AND ps.pages_fk_id=@PageId
                    WHERE sl.subsystem='Page'", parms))
              {
                  while (rdr.Read())
                  {
                      page.Settings.Add(rdr["SettingName"].ToString(), new BOSetting(rdr["SettingName"].ToString(), rdr["Type"].ToString().ParseToEnum<SettingTypeEnum>(), rdr["Value"].ToString(), rdr["UserVisibility"].ToString().ParseToEnum<VisibilityEnum>()));
                  }
              }
          }
  
          private static void PopulatePage(IDataReader rdr, BOPage sitePage, int languageId)
          {
              sitePage.ContentId = rdr.GetInt32(10);
              DbHelper.PopulateContent(rdr, sitePage, languageId);
  
              sitePage.Id = rdr.GetInt32(11);
              if (rdr[12] != DBNull.Value)
              {
  
gt:                 sitePage.ParentId = rdr.GetInt32(12);