/// <summary> /// Builds the select command for the data source /// </summary> public void BuildQuery() { if (String.IsNullOrWhiteSpace(SelectCommand)) { StringBuilder cond = new StringBuilder(); PagesManager pMgr = new PagesManager(); if (ParentId != null && !_getFromParent) { if (!Recursive) { cond.AppendFormat(" And ParentId={0}", _parentId); } else { string tempSql = string.Format("select * from dbo.Pages_GetDescendants({0})", _parentId); DataSet tempDs = DBUtils.GetDataSet(tempSql, cte.lib); string sep = ""; StringBuilder tempSB = new StringBuilder(); foreach (DataRow newsType in tempDs.Tables[0].Rows) { if (_recursiveLevel != null && _recursiveLevel != (int)newsType["Level"]) { continue; } tempSB.Append(sep); tempSB.Append(newsType["PageId"].ToString()); sep = ","; } string typeIds = tempSB.ToString(); if (!String.IsNullOrWhiteSpace(typeIds)) { cond.Append(string.Format(" And ParentId in ({0})", typeIds)); } } } else { if (!String.IsNullOrWhiteSpace(ParentURL)) { if (!Recursive) { cond.AppendFormat(" And ParentId in (select PageId from Pages where URL=N'{0}' or Title=N'{0}')", StringUtils.SQLEncode(ParentURL)); } else { //TODO: fix database connection _parentId = pMgr.GetPage(ParentURL).PageId; string tempSql = string.Format("select * from dbo.Pages_GetDescendants({0})", _parentId); DataSet tempDs = DBUtils.GetDataSet(tempSql, cte.lib); string sep = ""; StringBuilder tempSB = new StringBuilder(); foreach (DataRow newsType in tempDs.Tables[0].Rows) { if (_recursiveLevel != null && _recursiveLevel != (int)newsType["Level"]) { continue; } tempSB.Append(sep); tempSB.Append(newsType["PageId"].ToString()); sep = ","; } string typeIds = tempSB.ToString(); if (!String.IsNullOrWhiteSpace(typeIds)) { cond.Append(string.Format(" And ParentId in ({0})", typeIds)); } } } } if (month != null && month != "") { DateTime d = DateTime.Parse(month); DateTime d1 = d.AddMonths(1); cond.Append(string.Format(" And PublishDate Between '{0:M}' and '{1:M}'", d, d1)); } if (!String.IsNullOrEmpty(Year)) { cond.Append(string.Format(" And datepart(yyyy, PublishDate) = '{0}'", Year)); } if (!MyPage.Editable && !CMSMode.Value) { cond.AppendFormat(" and Status in ({0}, {1}, {2}, {3})", (byte)PageStatus.Published, (byte)PageStatus.Dynamic, (byte)PageStatus.Important, (byte)PageStatus.Menu); cond.Append(" And (PublishDate is Null Or PublishDate <= getDate())"); } else { cond.AppendFormat(" and Status not in ({0})", (byte)PageStatus.Deleted); } if (!String.IsNullOrWhiteSpace(_customCondition)) { cond.Append(_customCondition); } string _source = WebContext.Request["source"]; if (_source != null && _source.ToLower() == "categories") { string _typeId = WebContext.Request["TypeId"]; if (!String.IsNullOrWhiteSpace(_typeId)) { cond.Append(string.Format(" AND PageType={0}", StringUtils.SQLEncode(_typeId))); } } else if (_source != null && _source.ToLower() == "templates") { string templateId = WebContext.Request["TemplateId"]; if (!String.IsNullOrWhiteSpace(templateId)) { cond.Append(string.Format(" AND PageTemplate={0}", StringUtils.SQLEncode(templateId))); } } //string pId = WebContext.Request["ParentId"]; //if (!String.IsNullOrWhiteSpace(pId)) //{ // cond.Append(string.Format(" And ParentId={0}", StringUtils.SQLEncode(pId))); //} string _q = WebContext.Request["q"]; if (!String.IsNullOrWhiteSpace(_q)) { cond.Append(string.Format(" And (Title like N'%{0}%' or URL like N'%{0}%' or Header like N'%{0}%')", StringUtils.SQLEncode(_q))); } string _status = WebContext.Request["Status"]; if (!String.IsNullOrWhiteSpace(_status)) { cond.Append(string.Format(" And Status={0}", StringUtils.SQLEncode(_status))); } string sql = ""; if (cond.Length > 0) { sql = cond.ToString().Substring(5); } string _max = ""; if (Max != null) { _max = string.Format(" Top {0}", Max); } else if (_getPageProperties) { _max = " Top 100 PERCENT"; } this.SelectCommand = string.Format("select {1}* from " + SQLSource + " where {0}", sql, _max); } if (!EnablePaging) { this.SelectCommand += " Order By " + this.OrderBy; } if (_getPageProperties) { string tempSql = "select * from PageDataProrpertiesView where PageId in (select PageId from (" + this.SelectCommand + ") P)"; DataTable properties = DBUtils.GetDataSet(tempSql, cte.lib).Tables[0]; MyPage.AddContext(cte.PageProperties + "-" + this.ID, properties, true); } //WebContext.Response.Write(this.SelectCommand); //WebContext.Response.End(); }