public override void DataBind()
{
if (_bound)
{
return;
}
_bound = true;
this.SelectCommand = string.Format("Select * from MemberView where 1=1");
if (NetworkBound)
{
NetworkRelations networkRelations = new NetworkRelations();
SelectCommand += " and " + networkRelations.GetRelationQueryByNetwork(cte.NetworkTable, cte.NetworkRelateToField, Int32.Parse(networkName));
}
if (Client)
{
SelectCommand += " and MemberId not in (select MemberId from OperatorProfile)";
}
else if (!StringUtils.IsNullOrWhiteSpace(groupName))
{
string sql = string.Format("Select GroupId from OperatorGroups Where GroupName = '{0}'", groupName.ToString());
DataSet ds = DBUtils.GetDataSet(sql, cte.lib);
SelectCommand += string.Format(" and MemberId in (select MemberId from OperatorProfile where GroupId = {0})", Int32.Parse(ds.Tables[0].Rows[0]["GroupId"].ToString()));
}
if (CMSMode != null && CMSMode.Value)
{
string q = WebContext.Request["q"];
string networkId = WebContext.Request["NetworkId"];
string groupId = WebContext.Request["GroupId"];
NetworkRelations networkRelations = new NetworkRelations();
if (!string.IsNullOrEmpty(networkId))
{
SelectCommand += " and " + networkRelations.GetRelationQueryByNetwork(cte.NetworkTable, cte.NetworkRelateToField, Int32.Parse(networkId));
}
if (!String.IsNullOrEmpty(groupId))
{
SelectCommand += String.Format(" and MemberId in (select MemberId from GroupsMembers where GroupId={0})", groupId);
}
if (!string.IsNullOrWhiteSpace(q))
{
this.SelectCommand += string.Format(" and (Username like '%{0}%' or FullName like '%{0}%' or Email like '%{0}%')", StringUtils.SQLEncode(q));
}
}
if (!EnablePaging)
{
this.SelectCommand += " Order By " + this.OrderBy;
}
base.DataBind();
}
public override void DataBind()
{
if (_bound)
{
return;
}
_bound = true;
this.SelectCommand = string.Format("Select * from operatorsview where 1=1");
if (NetworkBound)
{
NetworkRelations networkRelations = new NetworkRelations();
SelectCommand += " and " + networkRelations.GetRelationQueryByNetwork(cte.NetworkTable, cte.NetworkRelateToField, Int32.Parse(networkName));
}
if (CMSMode != null && CMSMode.Value)
{
string q = WebContext.Request["q"];
string _network = WebContext.Request["NetworkId"];
string _group = WebContext.Request["GroupId"];
if (!string.IsNullOrWhiteSpace(q))
{
this.SelectCommand += string.Format(" and (Username like '%{0}%' or Email like '%{0}%' or FirstName like '%{0}%' or LastName like '%{0}%')", StringUtils.SQLEncode(q));
}
if (!String.IsNullOrWhiteSpace(_network))
{
this.SelectCommand += string.Format(" and MemberId in (select MemberId from MemberNetworks where NetworkId={0})", Int32.Parse(_network));
}
if (!String.IsNullOrWhiteSpace(_group))
{
this.SelectCommand += string.Format(" and MemberId in (select MemberId from OperatorProfile where GroupId={0})", Int32.Parse(_group));
}
}
if (!EnablePaging)
{
this.SelectCommand += " Order By " + this.OrderBy;
}
base.DataBind();
}
public override void DataBind()
{
if (_bound)
{
return;
}
_bound = true;
if (String.IsNullOrWhiteSpace(Category))
{
if (Randomise)
{
this.SelectCommand = string.Format("Select top {0} *, newid() as ___Ran from AlbumsView", Top);
this.OrderBy = "___Ran";
}
else
{
this.SelectCommand = string.Format("Select top {0} * from AlbumsView", Top);
}
}
else
{
if (Randomise)
{
this.SelectCommand = string.Format("Select top {0} *, newid() as ___Ran from AlbumsFullView", Top);
this.OrderBy = "___Ran";
}
else
{
this.SelectCommand = string.Format("Select top {0} * from AlbumsFullView", Top);
}
}
if (!CMSMode.Value && !MyPage.Editable)
{
this.SelectCommand += string.Format(" Where Status={0}", Status == true ? "1" : "0");
}
else
{
this.SelectCommand += " Where 1=1";
}
//TODO: Display Disabled Albums in case an administrator is logged in
if (!String.IsNullOrWhiteSpace(Category))
{
this.SelectCommand += string.Format(" And (UniqueName='{0}' or CategoryName='{0}')", StringUtils.SQLEncode(Category));
}
else if (CategoryId > 0)
{
this.SelectCommand += string.Format(" And CategoryId&power(2, {0}) = power(2, {0})", CategoryId);
}
if (_language != Languages.Default)
{
this.SelectCommand += string.Format(" And (Language={0} or Language={1})", (int)_language, (int)Languages.Default);
}
if (!string.IsNullOrWhiteSpace(Condition))
{
this.SelectCommand += string.Format(" And " + Condition);
}
if (PagesCategoryId.HasValue && PagesCategoryId.Value > 0)
{
}
this.SelectCommand += string.Format(" And CategoryId <> {0}", PagesCategoryId);
if (NetworkBound)
{
NetworkRelations networkRelations = new NetworkRelations();
SelectCommand += " and " + networkRelations.GetRelationQueryByNetwork(cte.NetworkRelationTable, cte.NetworkRelateToField, Int32.Parse(networkName));
}
if (RelateToSection != null)
{
int relationValue = -1;
switch (RelateToSection)
{
case SiteSections.Page:
relationValue = (int)ControlUtils.GetBoundedDataField(this.NamingContainer, "PageId");
break;
default:
break;
}
this.SelectCommand += String.Format(" and Id in (select AlbumId from PhotoAlbumRelations where RelateToId={0} and Section={1})", relationValue, (int)RelateToSection);
}
if (CMSMode != null && CMSMode.Value)
{
string q = WebContext.Request["q"];
string cat = WebContext.Request["CategoryId"];
string net = WebContext.Request["NetworkId"];
if (!string.IsNullOrWhiteSpace(q))
{
this.SelectCommand += string.Format(" and (Name like '%{0}%' or DisplayName like '%{0}%')", StringUtils.SQLEncode(q));
}
if (!string.IsNullOrWhiteSpace(cat))
{
this.SelectCommand += string.Format(" and (categoryid & POWER(2, {0}) = POWER(2, {0}))", StringUtils.SQLEncode(cat));
}
if (!string.IsNullOrWhiteSpace(net))
{
if (net != "-1")
{
this.SelectCommand += string.Format(" and Id in (select Id from PhotoAlbumsNetwork where NetworkId={0})", Int32.Parse(net));
}
else
{
this.SelectCommand += string.Format(" and Id in (select Id from PhotoAlbumsNetwork)");
}
}
}
if (!String.IsNullOrWhiteSpace(Filter))
{
this.SelectCommand += " and " + Filter;
}
if (!EnablePaging)
{
this.SelectCommand += " Order By " + this.OrderBy;
}
base.DataBind();
}
public override void DataBind()
{
if (_bound)
{
return;
}
_bound = true;
this.SelectCommand = string.Format("Select top {0} * from CalendarView where 1=1", Top);
if (CMSMode == null || CMSMode == false)
{
/* Reserved for use UTC
* if (isPrevious == true)
* this.SelectCommand += string.Format(" Where DATEADD(minute, DATEPART(minute, Time), DATEADD(hour, -DATEPART(hour, DateFrom), DATEADD(hour, DATEPART(hour, Time), DateFrom))) < GETUTCDATE()");
* else
* this.SelectCommand += string.Format(" Where DATEADD(minute, DATEPART(minute, Time), DATEADD(hour, -DATEPART(hour, DateFrom), DATEADD(hour, DATEPART(hour, Time), DateFrom))) > GETUTCDATE()");
*
*/
if (NetworkBound)
{
NetworkRelations networkRelations = new NetworkRelations();
this.SelectCommand += string.Format(" and " + networkRelations.GetRelationQueryByNetwork(cte.NetworkRelationTable, cte.NetworkRelateToField, Int32.Parse(networkName)));
}
if (UpComing != null)
{
if (UpComing == false)
{
this.SelectCommand += string.Format(@" And
DateFrom <=
DATEADD(minute, -DATEPART(minute, GetDate()),
DATEADD(hour, -DATEPART(hour, GetDate()),
DATEADD(second, -DATEPART(second, GetDate()),
DATEADD(millisecond, -DATEPART(millisecond, GetDate()), GetDate())
)
)
)");
}
else
{
this.SelectCommand += string.Format(" And DateTo >= '{0}'", DateTime.Today);
}
}
if (IsPrevious != null)
{
if (_isPrevious == true)
{
this.SelectCommand += string.Format(@" And
DateFrom <
DATEADD(minute, -DATEPART(minute, GetDate()),
DATEADD(hour, -DATEPART(hour, GetDate()),
DATEADD(second, -DATEPART(second, GetDate()),
DATEADD(millisecond, -DATEPART(millisecond, GetDate()), GetDate())
)
)
)");
}
else
{
this.SelectCommand += string.Format(@" And
DateFrom >=
DATEADD(minute, -DATEPART(minute, GetDate()),
DATEADD(hour, -DATEPART(hour, GetDate()),
DATEADD(second, -DATEPART(second, GetDate()),
DATEADD(millisecond, -DATEPART(millisecond, GetDate()), GetDate())
)
)
)");
}
}
string st = "in(0, 1)";
if (Status == "true")
{
st = "= 1";
}
if (Status == "false")
{
st = "= 0";
}
this.SelectCommand += string.Format(" And Status {0}", st);
if (_language != Languages.Default)
{
this.SelectCommand += string.Format(" and Language={0}", (int)_language);
}
if (CategoryId > 0)
{
this.SelectCommand += string.Format(" And CategoryId&power(2, {0}) = power(2, {0})", CategoryId);
}
if (DateFrom != null && DateFrom != "")
{
if (DateFrom == "Now")
{
this.SelectCommand += string.Format(" And DateTo >= '{0}'", DateTime.Today);
}
else
{
this.SelectCommand += string.Format(" And DateTo >= '{0}'", DateTime.Parse(DateFrom));
}
}
if (DateTo != null)
{
this.SelectCommand += string.Format(" And DateFrom <= '{0}'", DateTime.Parse(DateTo));
}
if (!String.IsNullOrWhiteSpace(Filter))
{
this.SelectCommand += " and " + Filter;
}
if (!EnablePaging)
{
this.SelectCommand += " Order By " + this.OrderBy;
}
}
else
{
string str = "";
System.Web.HttpRequest Request = WebContext.Request;
if (Request["q"] != null && Request["q"] != "")
{
str += string.Format(" and (Description like '%{0}%' or Title like '%{0}%')", lw.Utils.StringUtils.SQLEncode(Request["q"]));
}
if (Request["Status"] != null && Request["Status"] != "")
{
str += String.Format(" And Status={0}", Int32.Parse(Request["Status"]));
}
if (Request["CalendarCategory"] != null && Request["CalendarCategory"] != "")
{
str += String.Format(" And CategoryId&{0}={0}", Math.Pow(2, Int32.Parse(Request["CalendarCategory"])));
}
if (Request["DateFrom"] != null && Request["DateFrom"] != "")
{
str += string.Format(" And DateFrom>= '{0}'", Request["DateFrom"]);
}
if (Request["DateTo"] != null && Request["DateTo"] != "")
{
str += string.Format(" And DateTo < DateAdd(day, 1, '{0}')", lw.Utils.StringUtils.SQLEncode(Request["DateTo"]));
}
string lan = Request["Language"];
if (!String.IsNullOrEmpty(lan) && lan != "0")
{
str += string.Format(" and Language={0}", Int32.Parse(Request["Language"]));
}
str = str == "" ? str : " where " + str.Substring(5);
this.SelectCommand += str;
}
base.DataBind();
}
/// <summary>
/// Builds the select command for the data source
/// </summary>
public void BuildQuery()
{
StringBuilder cond = new StringBuilder();
NewsManager nMgr = new NewsManager();
if (TypeId != null)
{
if (!Recursive)
{
cond.Append(string.Format(" And NewsType={0}", TypeId.Value));
}
else
{
string tempSql = string.Format("select * from dbo.NewsTypes_GetDescendants({0})", TypeId);
DataSet tempDs = DBUtils.GetDataSet(tempSql, cte.lib);
string sep = "";
StringBuilder tempSB = new StringBuilder();
foreach (DataRow newsType in tempDs.Tables[0].Rows)
{
tempSB.Append(sep);
tempSB.Append(newsType["TypeId"].ToString());
sep = ",";
}
string typeIds = tempSB.ToString();
if (!String.IsNullOrWhiteSpace(typeIds))
{
cond.Append(string.Format(" And NewsType in ({0})", typeIds));
}
}
}
if (month != null && month != "")
{
DateTime d = DateTime.Parse(month);
DateTime d1 = d.AddMonths(1);
cond.Append(string.Format(" And NewsDate Between '{0:M}' and '{1:M}'", d, d1));
}
if (!String.IsNullOrEmpty(Year))
{
cond.Append(string.Format(" And datepart(yyyy, NewsDate) = '{0}'", Year));
}
if (NetworkBound)
{
NetworkRelations networkRelations = new NetworkRelations();
cond.Append(" and " + networkRelations.GetRelationQueryByNetwork(cte.NetworkRelationTable, cte.NetworkRelateToField, Int32.Parse(networkName)));
}
//will show all enabled news if no status is specified
if (Status == null)
{
if (CMSMode == null || !CMSMode.Value)
{
cond.Append(string.Format(" And (Status={0} or Status={1})", (int)NewsTagStatus.Archive, (int)NewsTagStatus.MainPage));
}
}
else
{
if (status == NewsTagStatus.Inherit)
{
try
{
int _status = -1;
object obj = DataBinder.Eval(this.NamingContainer, "DataItem.Status");
if (obj != null)
{
_status = Int32.Parse(obj.ToString());
}
else
{
_status = (int)NewsStatus.Archive;
}
cond.Append(string.Format(" And Status={0}", _status));
}
catch
{
}
}
else
{
cond.Append(string.Format(" And Status={0}", (int)Status));
}
}
cond.Append(string.Format(" And (NewsLanguage={0} or NewsLanguage={1})", (int)Language, (int)Languages.Default));
if (!String.IsNullOrEmpty(WebContext.Request.QueryString["NewsCategory"]))
{
cond.Append(" And UniqueName='" + StringUtils.SQLEncode(WebContext.Request.QueryString["NewsCategory"]) + "'");
}
cond.Append(" And (PublishDate is Null Or PublishDate <= getDate())");
if (!String.IsNullOrWhiteSpace(_customCondition))
{
cond.Append(_customCondition);
}
string _q = WebContext.Request["q"];
string _network = WebContext.Request["NetworkId"];
if (!String.IsNullOrWhiteSpace(_q))
{
cond.Append(string.Format(" And (Title like '%{0}%' or UniqueName like '%{0}%' or Header like '%{0}%;')", StringUtils.SQLEncode(_q)));
}
if (!String.IsNullOrEmpty(_network))
{
cond.Append(string.Format(" and NewsId in (select NewsId from NewsNetwork where NetworkId={0})", Int32.Parse(_network)));
}
if (!String.IsNullOrWhiteSpace(Filter))
{
cond.Append(" and " + Filter);
}
string sql = "";
if (cond.Length > 0)
{
sql = cond.ToString().Substring(5);
}
string _max = "";
if (Max != null)
{
_max = string.Format(" Top {0}", Max);
}
this.SelectCommand = string.Format("select {1}* from NewsView where {0}", sql, _max);
//WebContext.Response.Write(this.SelectCommand);
//WebContext.Response.End();
if (!EnablePaging)
{
this.SelectCommand += " Order By " + this.OrderBy;
}
}
public override void DataBind()
{
if (_bound)
{
return;
}
_bound = true;
if (CMSMode == false && !CMSMode.Value && !this.MyPage.Editable)
{
StringBuilder cond = new StringBuilder();
if (!String.IsNullOrWhiteSpace(Type))
{
cond.Append(string.Format(" And (Type='{0}' or UniqueName='{0}')", StringUtils.SQLEncode(Type)));
}
if (status != DownloadStatus.All)
{
cond.Append(string.Format(" And Status={0}", (int)Status));
}
if (NetworkBound)
{
NetworkRelations networkRelations = new NetworkRelations();
cond.Append(" and " + networkRelations.GetRelationQueryByMember(cte.NetworkRelationTable, cte.NetworkRelateToField, WebContext.Profile.UserId));
}
if (NonMemberNetworkBound)
{
NetworkRelations networkRelations = new NetworkRelations();
cond.Append(" and " + networkRelations.GetRelationQueryByNetwork(cte.NetworkRelationTable, cte.NetworkRelateToField, Int32.Parse(networkName)));
}
if (!string.IsNullOrWhiteSpace(Condition))
{
cond.Append(string.Format(" And " + Condition));
}
if (DisplaySinceLastLogin)
{
System.Data.DataRow member = lw.Members.Security.User.LoggedInUser(this, false) as System.Data.DataRow;
MemberLoginActivity mlMgr = new MemberLoginActivity();
DataView dv = mlMgr.GetMemberActivity((int)member["MemberId"]);
dv.Sort = "Id Desc";
if (dv.Count >= 2)
{
cond.Append(string.Format(" and DateAdded>='{0}'", dv[1]["LoggedInDate"]));
}
}
this.SelectCommand = string.Format(" select Top {0} d.*, '{1}/' + d.UniqueName + '/' + d.FileName as DownloadLink, FileSize/1024 as KB from DownloadsView d where Status<>{2} {3}",
Top,
Downloads.DownloadsVR,
(int)DownloadStatus.Disabled,
cond.ToString()
);
if (!EnablePaging)
{
this.SelectCommand += " Order By " + OrderBy;
}
}
else
{
System.Web.HttpRequest Request = WebContext.Request;
string cond = "1=1 ";
if (!string.IsNullOrWhiteSpace(Condition))
{
cond += string.Format(" And " + Condition);
}
if (!String.IsNullOrEmpty(Request["q"]))
{
cond += string.Format(" and (Title like '%{0}%' or FileName like '%{0}%')", StringUtils.SQLEncode(Request["q"]));
}
if (!String.IsNullOrEmpty(Request["NetworkId"]))
{
cond += string.Format(" and DownloadId in (select DownloadId from DownloadsNetwork where NetworkId = {0})", Int32.Parse(Request["NetworkId"]));
}
if (!String.IsNullOrWhiteSpace(Type))
{
cond += (string.Format(" And (Type='{0}' or UniqueName='{0}')", StringUtils.SQLEncode(Type)));
}
if (!String.IsNullOrEmpty(Request["TypeId"]))
{
string _type = Request["TypeId"];
cond += string.Format(" and DownloadType={0}", Int32.Parse(_type));
}
if (!String.IsNullOrEmpty(Request["Status"]))
{
string _Status = Request["Status"];
cond += string.Format(" and Status={0}", Int32.Parse(_Status));
}
this.SelectCommand = string.Format(" select top {3} d.*, '{1}/' + d.UniqueName + '/' + d.FileName as DownloadLink, FileSize/1024 as KB from DownloadsView d where {2}",
"100%",
Downloads.DownloadsVR,
cond,
Top
);
if (!EnablePaging)
{
this.SelectCommand += " Order By " + OrderBy;
}
}
base.DataBind();
}
public override void DataBind()
{
if (_bound)
{
return;
}
_bound = true;
StringBuilder query = new StringBuilder();
query.Append("select ");
// select top ..
if (!String.IsNullOrWhiteSpace(Top))
{
query.Append("Top " + Top);
}
query.Append(" D.*, C.[Count] from DownloadTypes D ");
query.Append("left outer Join");
query.Append("(select count(*) as [Count],DownloadType as TypeId from Downloads ");
bool whereAdded = false;
if (status != DownloadStatus.All)
{
if (!whereAdded)
{
query.Append(" where ");
}
else
{
query.Append(" and ");
}
whereAdded = true;
query.Append(string.Format("Status={0}", (int)status));
}
else
{
if (!whereAdded)
{
query.Append(" where ");
}
else
{
query.Append(" and ");
}
whereAdded = true;
query.Append(string.Format("Status<>{0}", (int)DownloadStatus.Disabled));
}
if (NetworkBound)
{
NetworkRelations networkRelations = new NetworkRelations();
if (!whereAdded)
{
query.Append(" where ");
}
else
{
query.Append(" and ");
}
whereAdded = true;
query.Append(networkRelations.GetRelationQueryByMember(cte.NetworkRelationTable, cte.NetworkRelateToField, WebContext.Profile.UserId));
}
query.Append(" Group By DownloadType) C on D.TypeId = C.TypeId");
if (!string.IsNullOrWhiteSpace(Condition))
{
query.Append(string.Format(" where " + Condition));
}
if (CMSMode == true)
{
query.Clear();
query.Append("Select * from DownloadTypes");
}
this.SelectCommand = query.ToString();
base.DataBind();
}
