/// <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();
}
