public override UserWebsiteCollection GetUserWebsites(int userID, IEnumerable <int> websiteIDs)
{
using (SqlQuery query = new SqlQuery())
{
query.CommandText = "SELECT * FROM Chinaz_UserWebsitesView WHERE UserID = @UserID AND WebsiteID IN( @WebsiteIDs)";
query.CreateParameter <int>("@UserID", userID, SqlDbType.Int);
query.CreateInParameter <int>("@WebsiteIDs", websiteIDs);
using (XSqlDataReader reader = query.ExecuteReader())
{
UserWebsiteCollection result = new UserWebsiteCollection(reader);
return(result);
}
}
}
public List <UserWebsiteProxy> Website_GetUserWebsiteList(int userID, int pageSize, int pageNumber, out int rowCount)
{
rowCount = 0;
if (!CheckClient())
{
return(null);
}
List <UserWebsiteProxy> websites = new List <UserWebsiteProxy>();
UserWebsiteCollection items = WebsiteBO.Instance.GetUserWebsites(userID, pageSize, pageNumber);
foreach (UserWebsite item in items)
{
websites.Add(ProxyConverter.GetUserWebsiteProxy(item));
}
rowCount = items.TotalRecords;
return(websites);
}
public override UserWebsiteCollection AdminSearchUserWebsites(AdminWebsiteFilter filter, int pageNumber)
{
using (SqlQuery query = new SqlQuery())
{
string SordField = filter.Order.ToString();
StringBuilder condition = new StringBuilder();
if (filter != null)
{
if (!string.IsNullOrEmpty(filter.Username))
{
condition.Append(" AND UserID IN( SELECT UserID FROM bx_Users WHERE Username LIKE '%' + @Username +'%')");
query.CreateParameter <string>("@Username", filter.Username, SqlDbType.NVarChar, 50);
}
if (filter.IsHaveImage < 2)
{
condition.Append(" AND IsHaveImage = @IsHaveImage");
query.CreateParameter <bool>("@IsHaveImage", (filter.IsHaveImage == 1), SqlDbType.Bit);
}
if (!string.IsNullOrEmpty(filter.WebsiteName))
{
condition.Append(" AND WebsiteName LIKE '%' + @WebsiteName +'%'");
query.CreateParameter <string>("@WebsiteName", filter.WebsiteName, SqlDbType.NVarChar, 50);
}
if (!string.IsNullOrEmpty(filter.Url))
{
condition.Append(" AND Url LIKE '%' + @Url + '%'");
query.CreateParameter <string>("@Url", filter.Url, SqlDbType.NVarChar, 200);
}
if (filter.categoryID > 0)
{
condition.Append(" AND CategoryID = @CategoryID");
query.CreateParameter <int>("@CategoryID", filter.categoryID, SqlDbType.Int);
}
if (filter.IsVerified < 2)
{
condition.Append(" AND Verified = @Verified");
query.CreateParameter <bool>("@Verified", (filter.IsVerified == 1), SqlDbType.Bit);
}
if (filter.BeginDate != null)
{
condition.Append(" AND CreateDate >= @BeginDate");
query.CreateParameter <DateTime>("@BeginDate", filter.BeginDate.Value, SqlDbType.DateTime);
}
if (filter.EndDate != null)
{
condition.Append(" AND CreateDate <= @EndDate");
query.CreateParameter <DateTime>("@EndDate", filter.EndDate.Value, SqlDbType.DateTime);
}
}
if (condition.Length > 5)
{
condition.Remove(0, 5);
}
query.Pager.TableName = "Chinaz_UserWebsitesView";
query.Pager.PageSize = filter.Pagesize;
query.Pager.PageNumber = pageNumber;
query.Pager.PrimaryKey = "UserWebsiteID";
query.Pager.SortField = SordField;
query.Pager.Condition = condition.ToString();
query.Pager.SelectCount = true;
query.Pager.IsDesc = filter.IsDesc;
using (XSqlDataReader reader = query.ExecuteReader())
{
UserWebsiteCollection websitelist = new UserWebsiteCollection(reader);
if (reader.NextResult())
{
while (reader.Next)
{
websitelist.TotalRecords = reader.Get <int>(0);
}
}
return(websitelist);
}
}
}
/// <summary>
/// 获取用户网站列表信息
/// </summary>
/// <param name="userID"></param>
/// <param name="pageSize"></param>
/// <param name="isGetMessages">是否获取消息列表</param>
/// <param name="isGetServices">是否获取服务列表</param>
/// <param name="pageNumber"></param>
/// <returns></returns>
public override UserWebsiteCollection GetUserWebsites(int userID, int pageSize, int pageNumber, bool isGetMessages, bool isGetServices)
{
using (SqlQuery query = new SqlQuery())
{
query.Pager.TableName = "Chinaz_UserWebsitesView";
query.Pager.PageSize = pageSize;
query.Pager.PageNumber = pageNumber;
query.Pager.PrimaryKey = "UserWebsiteID";
query.Pager.SelectCount = true;
query.Pager.SortField = "UserWebsiteID";
query.Pager.IsDesc = true;
query.Pager.Condition = "UserID = @UserID";
query.CreateParameter <int>("@UserID", userID, SqlDbType.Int);
UserWebsiteCollection result = new UserWebsiteCollection();
using (XSqlDataReader reader = query.ExecuteReader())
{
result = new UserWebsiteCollection(reader);
if (reader.NextResult())
{
while (reader.Next)
{
result.TotalRecords = reader.Get <int>(0);
}
}
}
if (isGetMessages || isGetServices)
{
query.CommandText = "";
if (isGetMessages)
{
if (result.Count > 0)
{
WebsiteService service = null;
string url;
foreach (UserWebsite web in result)
{
for (int i = 0; i < WebsiteServiceList.ServiceList.Count; i++)
{
service = WebsiteServiceList.ServiceList[i];
url = RemoveUrlPrefix(web.Url);
web.Services.Add(service.ID, new WebsiteService(service.ID, service.Content, string.Format(service.Url, url), service.LightImgPath, service.UnLightImgPath, service.IsPreView, service.OnLight));
}
}
}
}
if (isGetMessages)
{
query.CommandText += "SELECT * FROM Chinaz_UserWebsiteMessages WHERE UserWebsiteID IN(@UserWebsiteIDs) Order BY UserWebsiteID;";
}
if (isGetServices)
{
query.CommandText += "SELECT * FROM Chinaz_UserWebsiteServices WHERE UserWebsiteID IN(@UserWebsiteIDs) Order BY UserWebsiteID;";
}
query.CommandType = CommandType.Text;
query.CreateInParameter <int>("@UserWebsiteIDs", result.GetKeys());
using (XSqlDataReader reader = query.ExecuteReader())
{
UserWebsite site = null;
if (isGetMessages)
{
while (reader.Next)
{
UserWebsiteMessage message = new UserWebsiteMessage(reader);
if (site != null && site.UserWebsiteID == message.UserWebsiteID)
{
site.Messages.Add(message);
}
else
{
site = result.GetValue(message.UserWebsiteID);
site.Messages.Add(message);
}
}
}
if (isGetServices)
{
if (isGetMessages == false)
{
reader.NextResult();
}
int websiteid, serviceid;
while (reader.Next)
{
serviceid = reader.Get <int>("ServiceID");
websiteid = reader.Get <int>("UserWebsiteID");
if (site != null && site.UserWebsiteID == websiteid)
{
site.Services[serviceid].OnLight = true;
}
else
{
site = result.GetValue(websiteid);
site.Services[serviceid].OnLight = true;
}
}
}
}
}
return(result);
}
}
public override UserWebsiteCollection AdminSearchUserWebsites(AdminWebsiteFilter filter,int pageNumber)
{
using (SqlQuery query = new SqlQuery())
{
string SordField = filter.Order.ToString();
StringBuilder condition = new StringBuilder();
if (filter != null)
{
if (!string.IsNullOrEmpty(filter.Username))
{
condition.Append(" AND UserID IN( SELECT UserID FROM bx_Users WHERE Username LIKE '%' + @Username +'%')");
query.CreateParameter<string>("@Username", filter.Username, SqlDbType.NVarChar, 50);
}
if (filter.IsHaveImage < 2)
{
condition.Append(" AND IsHaveImage = @IsHaveImage");
query.CreateParameter<bool>("@IsHaveImage", (filter.IsHaveImage == 1), SqlDbType.Bit);
}
if (!string.IsNullOrEmpty(filter.WebsiteName))
{
condition.Append(" AND WebsiteName LIKE '%' + @WebsiteName +'%'");
query.CreateParameter<string>("@WebsiteName", filter.WebsiteName, SqlDbType.NVarChar, 50);
}
if (!string.IsNullOrEmpty(filter.Url))
{
condition.Append(" AND Url LIKE '%' + @Url + '%'");
query.CreateParameter<string>("@Url", filter.Url, SqlDbType.NVarChar, 200);
}
if (filter.categoryID > 0)
{
condition.Append(" AND CategoryID = @CategoryID");
query.CreateParameter<int>("@CategoryID", filter.categoryID, SqlDbType.Int);
}
if (filter.IsVerified < 2)
{
condition.Append(" AND Verified = @Verified");
query.CreateParameter<bool>("@Verified", (filter.IsVerified == 1), SqlDbType.Bit);
}
if (filter.BeginDate != null)
{
condition.Append(" AND CreateDate >= @BeginDate");
query.CreateParameter<DateTime>("@BeginDate", filter.BeginDate.Value, SqlDbType.DateTime);
}
if (filter.EndDate != null)
{
condition.Append(" AND CreateDate <= @EndDate");
query.CreateParameter<DateTime>("@EndDate", filter.EndDate.Value, SqlDbType.DateTime);
}
}
if (condition.Length > 5)
condition.Remove(0, 5);
query.Pager.TableName = "Chinaz_UserWebsitesView";
query.Pager.PageSize = filter.Pagesize;
query.Pager.PageNumber = pageNumber;
query.Pager.PrimaryKey = "UserWebsiteID";
query.Pager.SortField = SordField;
query.Pager.Condition = condition.ToString();
query.Pager.SelectCount = true;
query.Pager.IsDesc = filter.IsDesc;
using (XSqlDataReader reader = query.ExecuteReader())
{
UserWebsiteCollection websitelist = new UserWebsiteCollection(reader);
if (reader.NextResult())
{
while (reader.Next)
websitelist.TotalRecords = reader.Get<int>(0);
}
return websitelist;
}
}
}
/// <summary>
/// 获取用户网站列表信息
/// </summary>
/// <param name="userID"></param>
/// <param name="pageSize"></param>
/// <param name="isGetMessages">是否获取消息列表</param>
/// <param name="isGetServices">是否获取服务列表</param>
/// <param name="pageNumber"></param>
/// <returns></returns>
public override UserWebsiteCollection GetUserWebsites(int userID, int pageSize, int pageNumber, bool isGetMessages, bool isGetServices)
{
using (SqlQuery query = new SqlQuery())
{
query.Pager.TableName = "Chinaz_UserWebsitesView";
query.Pager.PageSize = pageSize;
query.Pager.PageNumber = pageNumber;
query.Pager.PrimaryKey = "UserWebsiteID";
query.Pager.SelectCount = true;
query.Pager.SortField = "UserWebsiteID";
query.Pager.IsDesc = true;
query.Pager.Condition = "UserID = @UserID";
query.CreateParameter<int>("@UserID", userID, SqlDbType.Int);
UserWebsiteCollection result = new UserWebsiteCollection();
using (XSqlDataReader reader = query.ExecuteReader())
{
result = new UserWebsiteCollection(reader);
if (reader.NextResult())
{
while (reader.Next)
result.TotalRecords = reader.Get<int>(0);
}
}
if (isGetMessages || isGetServices)
{
query.CommandText = "";
if (isGetMessages)
{
if (result.Count > 0)
{
WebsiteService service = null;
string url;
foreach (UserWebsite web in result)
{
for (int i = 0; i < WebsiteServiceList.ServiceList.Count; i++)
{
service = WebsiteServiceList.ServiceList[i];
url = RemoveUrlPrefix(web.Url);
web.Services.Add(service.ID, new WebsiteService(service.ID, service.Content, string.Format(service.Url, url), service.LightImgPath, service.UnLightImgPath, service.IsPreView, service.OnLight));
}
}
}
}
if (isGetMessages)
{
query.CommandText += "SELECT * FROM Chinaz_UserWebsiteMessages WHERE UserWebsiteID IN(@UserWebsiteIDs) Order BY UserWebsiteID;";
}
if(isGetServices)
{
query.CommandText += "SELECT * FROM Chinaz_UserWebsiteServices WHERE UserWebsiteID IN(@UserWebsiteIDs) Order BY UserWebsiteID;";
}
query.CommandType = CommandType.Text;
query.CreateInParameter<int>("@UserWebsiteIDs", result.GetKeys());
using (XSqlDataReader reader = query.ExecuteReader())
{
UserWebsite site = null;
if (isGetMessages)
{
while (reader.Next)
{
UserWebsiteMessage message = new UserWebsiteMessage(reader);
if (site != null && site.UserWebsiteID == message.UserWebsiteID)
site.Messages.Add(message);
else
{
site = result.GetValue(message.UserWebsiteID);
site.Messages.Add(message);
}
}
}
if (isGetServices)
{
if (isGetMessages == false)
reader.NextResult();
int websiteid, serviceid;
while (reader.Next)
{
serviceid = reader.Get<int>("ServiceID");
websiteid = reader.Get<int>("UserWebsiteID");
if (site != null && site.UserWebsiteID == websiteid)
site.Services[serviceid].OnLight = true;
else
{
site = result.GetValue(websiteid);
site.Services[serviceid].OnLight = true;
}
}
}
}
}
return result;
}
}
public override UserWebsiteCollection GetUserWebsites(int userID, IEnumerable<int> websiteIDs)
{
using (SqlQuery query = new SqlQuery())
{
query.CommandText = "SELECT * FROM Chinaz_UserWebsitesView WHERE UserID = @UserID AND WebsiteID IN( @WebsiteIDs)";
query.CreateParameter<int>("@UserID", userID, SqlDbType.Int);
query.CreateInParameter<int>("@WebsiteIDs", websiteIDs);
using (XSqlDataReader reader = query.ExecuteReader())
{
UserWebsiteCollection result = new UserWebsiteCollection(reader);
return result;
}
}
}