public override DoingCollection GetEveryoneDoingsWithComments(int pageNumber, int pageSize, ref int?totalCount)
{
DoingCollection doings = null;
using (SqlSession db = new SqlSession())
{
using (SqlQuery query = new SqlQuery())
{
query.Pager.TableName = "[bx_Doings]";
query.Pager.SortField = "[DoingID]";
query.Pager.PageNumber = pageNumber;
query.Pager.PageSize = pageSize;
query.Pager.TotalRecords = totalCount;
query.Pager.IsDesc = true;
query.Pager.SelectCount = true;
using (XSqlDataReader reader = query.ExecuteReader())
{
doings = new DoingCollection(reader);
if (totalCount == null && reader.NextResult() && reader.Read())
{
totalCount = reader.Get <int>(0);
}
doings.TotalRecords = totalCount.GetValueOrDefault();
}
}
FillDoingComments(doings, db);
}
return(doings);
}
public override DenouncingCollection GetDenouncingWithReply(DenouncingFilter filter, int pageNumber)
{
using (SqlSession db = new SqlSession())
{
DenouncingCollection denouncings = null;
using (SqlQuery query = db.CreateQuery())
{
query.Pager.PageNumber = pageNumber;
query.Pager.PageSize = filter.PageSize;
query.Pager.TableName = "bx_Denouncings";
query.Pager.SortField = "DenouncingID";
query.Pager.IsDesc = filter.IsDesc;
query.Pager.SelectCount = true;
filter.Type = DenouncingType.Reply;
GetSearchDenouncingsCondition(query, filter);
using (XSqlDataReader reader = query.ExecuteReader())
{
denouncings = new DenouncingCollection(reader);
if (reader.NextResult())
{
while (reader.Read())
{
denouncings.TotalRecords = reader.Get <int>(0);
}
}
}
}
FillDenouncingContents(denouncings, db);
if (denouncings.Count > 0)
{
int[] targetIDs = GetTargetIDs(denouncings);
PostCollectionV5 posts = PostBOV5.Instance.GetPosts(targetIDs);
for (int i = 0; i < denouncings.Count; i++)
{
for (int j = 0; j < posts.Count; j++)
{
if (denouncings[i].TargetID == posts[j].ThreadID)
{
denouncings[i].TargetReply = posts[j];
break;
}
}
}
}
return(denouncings);
}
}
public override DoingCollection GetUserDoingsWithComments(int doingOwnerID, DataAccessLevel dataAccessLevel, int pageNumber, int pageSize, ref int?totalCount)
{
DoingCollection doings = null;
using (SqlSession db = new SqlSession())
{
using (SqlQuery query = db.CreateQuery())
{
query.Pager.TableName = "[bx_Doings]";
query.Pager.SortField = "[DoingID]";
query.Pager.SelectCount = true;
query.Pager.PageNumber = pageNumber;
query.Pager.PageSize = pageSize;
query.Pager.TotalRecords = totalCount;
query.Pager.Condition = "[UserID]=@UserID";
query.CreateParameter <int>("@UserID", doingOwnerID, SqlDbType.Int);
//if (dataAccessLevel == DataAccessLevel.Normal)
//{
// query.Pager.Condition += " AND [PrivacyType] IN (0, 3)";
//}
//else if (dataAccessLevel == DataAccessLevel.Friend)
//{
// query.Pager.Condition += " AND [PrivacyType] IN (0, 1, 3)";
//}
using (XSqlDataReader reader = query.ExecuteReader())
{
doings = new DoingCollection(reader);
if (totalCount == null && reader.NextResult() && reader.Read())
{
totalCount = reader.Get <int>(0);
}
doings.TotalRecords = totalCount.GetValueOrDefault();
}
}
FillDoingComments(doings, db);
}
return(doings);
}
public override DenouncingCollection GetDenouncingWithUser(DenouncingFilter filter, int pageNumber)
{
using (SqlSession db = new SqlSession())
{
DenouncingCollection denouncings = null;
using (SqlQuery query = db.CreateQuery())
{
query.Pager.PageNumber = pageNumber;
query.Pager.PageSize = filter.PageSize;
query.Pager.TableName = "bx_Denouncings";
query.Pager.SortField = "DenouncingID";
query.Pager.IsDesc = filter.IsDesc;
query.Pager.SelectCount = true;
filter.Type = DenouncingType.Space;
GetSearchDenouncingsCondition(query, filter);
using (XSqlDataReader reader = query.ExecuteReader())
{
denouncings = new DenouncingCollection(reader);
if (reader.NextResult())
{
while (reader.Read())
{
denouncings.TotalRecords = reader.Get <int>(0);
}
}
}
}
FillDenouncingContents(denouncings, db);
if (denouncings.Count > 0)
{
UserBO.Instance.WaitForFillSimpleUsers <Denouncing>(denouncings);
}
return(denouncings);
}
}
/// <summary>
/// 获取系统启动时数据库当前时间与程序时间的相差毫秒数
/// </summary>
public override double GetTimeIntervalFromDatabase()
{
using (SqlSession session = new SqlSession())
{
string connectionString = session.Connection.ConnectionString;
Regex regexConnString1 = new Regex(@"^.*\s?(?>Server|Data\s+Source)\s*=\s*(?>\.|\(local\)|127\.0\.0\.1|localhost|::1)\s*;.*?", RegexOptions.IgnoreCase);
if (regexConnString1.IsMatch(connectionString))
{
return(0.0f);
}
using (SqlQuery query = new SqlQuery())
{
query.CommandText = "SELECT GETDATE();";
return((query.ExecuteScalar <DateTime>() - DateTimeUtil.Now).TotalMilliseconds);
}
}
}
/// <summary>
/// 获取系统启动时数据库当前时间与程序时间的相差毫秒数
/// </summary>
public override double GetTimeIntervalFromDatabase()
{
using (SqlSession session = new SqlSession())
{
string connectionString = session.Connection.ConnectionString;
Regex regexConnString1 = new Regex(@"^.*\s?(?>Server|Data\s+Source)\s*=\s*(?>\.|\(local\)|127\.0\.0\.1|localhost|::1)\s*;.*?", RegexOptions.IgnoreCase);
if (regexConnString1.IsMatch(connectionString))
{
return 0.0f;
}
using (SqlQuery query = new SqlQuery())
{
query.CommandText = "SELECT GETDATE();";
return (query.ExecuteScalar<DateTime>() - DateTimeUtil.Now).TotalMilliseconds;
}
}
}
private void FillDenouncingContents(DenouncingCollection denouncings, SqlSession db)
{
if (denouncings.Count == 0)
{
return;
}
DenouncingContentCollection contents = null;
using (SqlQuery query = db.CreateQuery())
{
query.CommandText = "SELECT * FROM bx_DenouncingContents WHERE DenouncingID IN (@DenouncingIDs) ORDER BY [DenouncingID]";
query.CreateInParameter <int>("@DenouncingIDs", denouncings.GetDenouncingIDs());
using (XSqlDataReader reader = query.ExecuteReader())
{
contents = new DenouncingContentCollection(reader);
}
}
db.Connection.Close();
Denouncing denouncing = null;
int lastDenouncingID = -1;
for (int i = 0; i < contents.Count; i++)
{
int denouncingID = contents[i].DenouncingID;
if (denouncingID != lastDenouncingID)
{
denouncing = denouncings.GetValue(denouncingID);
lastDenouncingID = denouncingID;
}
denouncing.ContentList.Add(contents[i]);
}
}
public override DoingCollection GetUserCommentedDoingsWithComments(int userID, int pageNumber, int pageSize, ref int?totalCount)
{
DoingCollection doings = null;
using (SqlSession db = new SqlSession())
{
using (SqlQuery query = new SqlQuery())
{
query.Pager.TableName = "[bx_Doings]";
query.Pager.SortField = "DoingID";
query.Pager.PageNumber = pageNumber;
query.Pager.PageSize = pageSize;
query.Pager.TotalRecords = totalCount;
query.Pager.IsDesc = true;
query.Pager.SelectCount = true;
query.Pager.Condition = "[DoingID] IN (SELECT [TargetID] FROM [bx_Comments] WHERE [Type]=3 AND [UserID] = @UserID)";
query.CreateParameter <int>("@UserID", userID, SqlDbType.Int);
using (XSqlDataReader reader = query.ExecuteReader())
{
doings = new DoingCollection(reader);
if (totalCount == null && reader.NextResult() && reader.Read())
{
totalCount = reader.Get <int>(0);
}
doings.TotalRecords = totalCount.GetValueOrDefault();
}
}
FillDoingComments(doings, db);
}
return(doings);
}
public override UserCollection GetUserShows(int userID, int pageNumber, int pageSize, out int userTotalCount)
{
userTotalCount = 0;
UserCollection users = new UserCollection();
using (SqlSession db = new SqlSession())
{
using (SqlQuery query = db.CreateQuery())
{
query.Pager.TableName = "bx_PointShowUsers";
query.Pager.SortField = "Price";
query.Pager.IsDesc = true;
query.Pager.PageNumber = pageNumber;
query.Pager.PageSize = pageSize;
query.Pager.SelectCount = true;
query.Pager.PrimaryKey = "UserID";
query.CreateParameter <int>("@UserID", userID, SqlDbType.Int);
using (XSqlDataReader reader = query.ExecuteReader())
{
users = new UserCollection(reader);
if (reader.NextResult())
{
if (reader.Read())
{
users.TotalRecords = reader.Get <int>(0);
userTotalCount = users.TotalRecords;
}
}
}
}
return(users);
}
}
public override void DeleteExperisValidateCodeActionRecord(List <string> actions, List <int> limitedTimes)
{
using (SqlSession db = new SqlSession())
{
using (SqlQuery query = db.CreateQuery(QueryMode.Batch))
{
StringBuilder sql = new StringBuilder();
for (int i = 0; i < actions.Count; i++)
{
sql.AppendFormat(@"
DELETE [bx_ValidateCodeActionRecords] WHERE [Action] = @Action_{0} AND [CreateDate] < @LimitedTime_{0};
", i);
query.CreateParameter <string>("@Action_" + i, actions[i], SqlDbType.VarChar, 200);
query.CreateParameter <DateTime>("@LimitedTime_" + i, DateTimeUtil.Now.AddSeconds(0 - limitedTimes[i]), SqlDbType.DateTime);
//query.ExecuteNonQuery();
}
//query.Submit();
if (actions.Count > 0)
{
query.CommandText += "DELETE [bx_ValidateCodeActionRecords] WHERE [Action] NOT IN (@Actions);";
query.CreateInParameter <string>("@Actions", actions);
}
else
{
query.CommandText += "DELETE [bx_ValidateCodeActionRecords]";
}
query.ExecuteNonQuery();
}
}
}
private void FillDenouncingContents(DenouncingCollection denouncings, SqlSession db)
{
if (denouncings.Count == 0)
return;
DenouncingContentCollection contents = null;
using (SqlQuery query = db.CreateQuery())
{
query.CommandText = "SELECT * FROM bx_DenouncingContents WHERE DenouncingID IN (@DenouncingIDs) ORDER BY [DenouncingID]";
query.CreateInParameter<int>("@DenouncingIDs", denouncings.GetDenouncingIDs());
using (XSqlDataReader reader = query.ExecuteReader())
{
contents = new DenouncingContentCollection(reader);
}
}
db.Connection.Close();
Denouncing denouncing = null;
int lastDenouncingID = -1;
for (int i = 0; i < contents.Count; i++)
{
int denouncingID = contents[i].DenouncingID;
if (denouncingID != lastDenouncingID)
{
denouncing = denouncings.GetValue(denouncingID);
lastDenouncingID = denouncingID;
}
denouncing.ContentList.Add(contents[i]);
}
}
private void FillDoingComments(DoingCollection doings, SqlSession db)
{
if (doings.Count == 0)
{
return;
}
List <int> minIds = new List <int>();
List <int> maxIds = new List <int>();
for (int i = 0; i < doings.Count; i++)
{
if (doings[i].TotalComments == 0)
{
continue;
}
else if (doings[i].TotalComments == 1)
{
minIds.Add(doings[i].DoingID);
}
else
{
minIds.Add(doings[i].DoingID);
maxIds.Add(doings[i].DoingID);
}
}
if (minIds.Count == 0)
{
return;
}
using (SqlQuery query = db.CreateQuery())
{
query.CommandText = @"
SELECT * FROM bx_Comments WHERE CommentID IN(
SELECT Min(CommentID) FROM [bx_Comments] WHERE [Type]=3 AND IsApproved = 1 AND [TargetID] IN(@MinTargetIDs) GROUP BY TargetID
);
";
if (maxIds.Count > 0)
{
query.CommandText += @"
SELECT * FROM bx_Comments WHERE CommentID IN(
SELECT Max(CommentID) FROM [bx_Comments] WHERE [Type]=3 AND IsApproved = 1 AND [TargetID] IN(@MaxTargetIDs) GROUP BY TargetID
);
";
}
query.CreateInParameter <int>("@MinTargetIDs", minIds);
query.CreateInParameter <int>("@MaxTargetIDs", maxIds);
using (XSqlDataReader reader = query.ExecuteReader())
{
while (reader.Read())
{
Comment comment = new Comment(reader);
Doing doing = doings.GetValue(comment.TargetID);
if (doing != null)
{
if (doing.CommentList.ContainsKey(comment.CommentID) == false)
{
doing.CommentList.Add(comment);
}
}
}
if (reader.NextResult())
{
while (reader.Read())
{
Comment comment = new Comment(reader);
Doing doing = doings.GetValue(comment.TargetID);
if (doing != null)
{
if (doing.CommentList.ContainsKey(comment.CommentID) == false)
{
doing.CommentList.Add(comment);
}
}
}
}
}
}
db.Connection.Close();
//Doing doing = null;
//int lastDoingID = -1;
//for (int i = 0; i < comments.Count; i++)
//{
// int doingID = comments[i].TargetID;
// if (doingID != lastDoingID)
// {
// doing = doings.GetValue(doingID);
// lastDoingID = doingID;
// }
// doing.CommentList.Add(comments[i]);
//}
}
public override void SetUserNoAddFeedApps(int userID, UserNoAddFeedAppCollection userNoAddFeedApps)
{
using (SqlSession db = new SqlSession())
{
db.BeginTransaction();
//using (SqlQuery query = db.CreateQuery())
//{
// query.CommandText = "DELETE [bx_UserNoAddFeedApps] WHERE [UserID]=@UserID;";
// query.CreateParameter<int>("@UserID", userID, SqlDbType.Int);
// query.ExecuteNonQuery();
//}
using (SqlQuery query = db.CreateQuery(QueryMode.Prepare))
{
query.CommandText = @"
IF EXISTS(SELECT * FROM [bx_UserNoAddFeedApps] WHERE AppID=@AppID AND UserID=@UserID AND ActionType=@ActionType)
UPDATE [bx_UserNoAddFeedApps] SET [Send]=@Send WHERE AppID=@AppID AND UserID=@UserID AND ActionType=@ActionType;
ELSE
INSERT INTO [bx_UserNoAddFeedApps](
[AppID]
,[UserID]
,[ActionType]
,[Send]
) VALUES (
@AppID
,@UserID
,@ActionType
,@Send
);
";
foreach (UserNoAddFeedApp userNoAddFeedApp in userNoAddFeedApps)
{
query.CreateParameter<Guid>("@AppID", userNoAddFeedApp.AppID, SqlDbType.UniqueIdentifier);
query.CreateParameter<int>("@ActionType", userNoAddFeedApp.ActionType, SqlDbType.Int);
query.CreateParameter<int>("@UserID", userID, SqlDbType.Int);
query.CreateParameter<bool>("@Send", userNoAddFeedApp.Send, SqlDbType.Bit);
query.ExecuteNonQuery();
}
}
db.CommitTransaction();
}
}
public override DoingCollection GetUserDoingsWithComments(int doingOwnerID, DataAccessLevel dataAccessLevel, int pageNumber, int pageSize, ref int? totalCount)
{
DoingCollection doings = null;
using (SqlSession db = new SqlSession())
{
using (SqlQuery query = db.CreateQuery())
{
query.Pager.TableName = "[bx_Doings]";
query.Pager.SortField = "[DoingID]";
query.Pager.SelectCount = true;
query.Pager.PageNumber = pageNumber;
query.Pager.PageSize = pageSize;
query.Pager.TotalRecords = totalCount;
query.Pager.Condition = "[UserID]=@UserID";
query.CreateParameter<int>("@UserID", doingOwnerID, SqlDbType.Int);
//if (dataAccessLevel == DataAccessLevel.Normal)
//{
// query.Pager.Condition += " AND [PrivacyType] IN (0, 3)";
//}
//else if (dataAccessLevel == DataAccessLevel.Friend)
//{
// query.Pager.Condition += " AND [PrivacyType] IN (0, 1, 3)";
//}
using (XSqlDataReader reader = query.ExecuteReader())
{
doings = new DoingCollection(reader);
if (totalCount == null && reader.NextResult() && reader.Read())
{
totalCount = reader.Get<int>(0);
}
doings.TotalRecords = totalCount.GetValueOrDefault();
}
}
FillDoingComments(doings, db);
}
return doings;
}
public override DenouncingCollection GetDenouncingWithReply(DenouncingFilter filter, int pageNumber)
{
using (SqlSession db = new SqlSession())
{
DenouncingCollection denouncings = null;
using (SqlQuery query = db.CreateQuery())
{
query.Pager.PageNumber = pageNumber;
query.Pager.PageSize = filter.PageSize;
query.Pager.TableName = "bx_Denouncings";
query.Pager.SortField = "DenouncingID";
query.Pager.IsDesc = filter.IsDesc;
query.Pager.SelectCount = true;
filter.Type = DenouncingType.Reply;
GetSearchDenouncingsCondition(query, filter);
using (XSqlDataReader reader = query.ExecuteReader())
{
denouncings = new DenouncingCollection(reader);
if (reader.NextResult())
{
while (reader.Read())
denouncings.TotalRecords = reader.Get<int>(0);
}
}
}
FillDenouncingContents(denouncings, db);
if (denouncings.Count > 0)
{
int[] targetIDs = GetTargetIDs(denouncings);
PostCollectionV5 posts = PostBOV5.Instance.GetPosts(targetIDs);
for (int i = 0; i < denouncings.Count; i++)
{
for (int j = 0; j < posts.Count; j++)
{
if (denouncings[i].TargetID == posts[j].ThreadID)
{
denouncings[i].TargetReply = posts[j];
break;
}
}
}
}
return denouncings;
}
}
/// <summary>
/// 如果UserID为空，则完全删除这一组Feed；
/// 如果UserID不为空，则把用户从这些Feed移除。如果某个Feed只关联了这一个用户，则整条Feed被删除
/// </summary>
/// <param name="userID"></param>
/// <param name="feedIDs"></param>
public override void DeleteFeeds(int? userID, IEnumerable<int> feedIDs)
{
bool emptyFeeds = true;
foreach (int id in feedIDs)
{
emptyFeeds = false;
break;
}
if (emptyFeeds)
return;
if (userID == null)
{
using (SqlQuery query = new SqlQuery())
{
query.CommandText = @"
DELETE [bx_Feeds] WHERE [ID] IN(@FeedIDs);
";
query.CreateInParameter("@FeedIDs", feedIDs);
query.ExecuteNonQuery();
return;
}
}
using (SqlSession db = new SqlSession())
{
db.BeginTransaction();
using (SqlQuery query = db.CreateQuery())
{
query.CommandText = "DELETE [bx_UserFeeds] WHERE [UserID]=@UserID AND [FeedID] IN(@FeedIDs);";
query.CreateParameter<int>("@UserID", userID.Value, SqlDbType.Int);
query.CreateInParameter("@FeedIDs", feedIDs);
query.ExecuteNonQuery();
}
using (SqlQuery query = db.CreateQuery(QueryMode.Prepare))
{
query.CommandText = @"
IF NOT EXISTS(SELECT * FROM [bx_UserFeeds] WHERE [FeedID]=@FeedID)
DELETE [bx_Feeds] WHERE [ID] = @FeedID;
--以下是删除 类似加好友的动态
DECLARE @FeedCount int;
SELECT @FeedCount = COUNT(*) FROM bx_UserFeeds WHERE [FeedID]=@FeedID
IF @FeedCount = 1 AND EXISTS(SELECT * FROM bx_Feeds F INNER JOIN bx_UserFeeds UF ON F.[ID]=UF.[FeedID] WHERE F.[ID]=@FeedID AND UF.[UserID]=F.[TargetUserID]) BEGIN
DELETE [bx_Feeds] WHERE [ID] = @FeedID;
END
";
foreach (int feedID in feedIDs)
{
query.CreateParameter<int>("@FeedID", feedID, SqlDbType.Int);
query.ExecuteNonQuery();
}
}
db.CommitTransaction();
}
#region
/*
using (DbSession db = new DbSession())
{
if (userID == null)
{
db.ExecuteNonQuery(@"
DELETE [bx_Feeds] WHERE [ID] IN(@FeedIDs);
"
, db.ParamReplace("@FeedIDs", StringUtil.Join(feedIDs))
);
return;
}
StringBuilder sql = new StringBuilder();
sql.Append("DELETE [bx_UserFeeds] WHERE [UserID]=@UserID AND [FeedID] IN(@FeedIDs);");
int i = 0;
foreach (int feedID in feedIDs)
{
string s = db.FormatSql(@"
IF NOT EXISTS(SELECT * FROM [bx_UserFeeds] WHERE [FeedID]=@FeedID)
DELETE [bx_Feeds] WHERE [ID] = @FeedID;
--以下是删除 类似加好友的动态
DECLARE @Feed" + i +@"Count int;
SELECT @Feed" + i + @"Count = COUNT(*) FROM bx_UserFeeds WHERE [FeedID]=@FeedID
IF @Feed" + i + @"Count = 1 AND EXISTS(SELECT * FROM bx_Feeds F INNER JOIN bx_UserFeeds UF ON F.[ID]=UF.[FeedID] WHERE F.[ID]=@FeedID AND UF.[UserID]=F.[TargetUserID]) BEGIN
DELETE [bx_Feeds] WHERE [ID] = @FeedID;
END
"
, db.Param<int>("@FeedID",feedID)
);
sql.AppendLine(s);
i++;
}
db.ExecuteNonQuery(
sql.ToString()
, db.Param<int>("@UserID",userID.Value)
, db.ParamReplace("@FeedIDs", StringUtil.Join(feedIDs))
);
}
*/
#endregion
}
/// <summary>
/// 保存标签
/// </summary>
/// <param name="tags">标签集</param>
/// <param name="type">类型,如日志标签等</param>
/// <param name="targetID">标签的对象ID</param>
public override bool SaveTags(TagCollection tags, TagType type, int targetID)
{
using (SqlSession db = new SqlSession())
{
using (SqlQuery query = db.CreateQuery())
{
query.CommandText = @"DELETE FROM [bx_TagRelation] WHERE [Type]=@Type AND [TargetID] = @TargetID;";
query.CreateParameter<int>("@TargetID", targetID, SqlDbType.Int);
query.CreateParameter<TagType>("@Type", type, SqlDbType.TinyInt);
query.ExecuteNonQuery();
}
if (tags == null || tags.Count == 0)
return true;
using (SqlQuery query = db.CreateQuery(QueryMode.Prepare))
{
query.CommandText = @"
IF EXISTS (SELECT * FROM [bx_Tags] WHERE [Name] = @TagName) BEGIN
DECLARE @TagID int;
SET @TagID = (SELECT TOP 1 [ID] FROM [bx_Tags] WHERE [Name] = @TagName);
DECLARE @PrivacyType tinyint;
IF @Type = 1
SET @PrivacyType = (SELECT [PrivacyType] FROM [bx_BlogArticles] WHERE [ArticleID] = @TargetID);
ELSE
SET @PrivacyType = 0;
IF NOT EXISTS (SELECT * FROM [bx_TagRelation] WHERE [TagID] = @TagID) BEGIN
IF @PrivacyType = 0 OR @PrivacyType = 3 BEGIN
INSERT INTO [bx_TagRelation]([TagID], [Type], [TargetID]) VALUES(@TagID, @Type, @TargetID);
END
END
ELSE BEGIN
IF EXISTS (SELECT * FROM [bx_TagRelation] WHERE [TagID] = @TagID AND [Type] = @Type AND [TargetID] = @TargetID) BEGIN
IF @PrivacyType <> 0 AND @PrivacyType <> 3 BEGIN
DELETE FROM [bx_TagRelation] WHERE [TagID] = @TagID AND [Type] = @Type AND [TargetID] = @TargetID;
END
END
ELSE BEGIN
IF @PrivacyType = 0 OR @PrivacyType = 3 BEGIN
INSERT INTO [bx_TagRelation]([TagID], [Type], [TargetID]) VALUES(@TagID, @Type, @TargetID);
END
END
END
END
ELSE BEGIN
DECLARE @NewTagID int;
INSERT INTO [bx_Tags]([Name]) VALUES (@TagName);
SET @NewTagID = @@IDENTITY;
IF @Type = 1 AND EXISTS (SELECT * FROM [bx_BlogArticles] WHERE [ArticleID] = @TargetID AND [PrivacyType] IN (0,3)) BEGIN
INSERT INTO [bx_TagRelation]([TagID], [Type], [TargetID]) VALUES(@NewTagID, @Type, @TargetID);
END
END";
foreach (Tag tag in tags)
{
query.CreateParameter<string>("@TagName", tag.Name, SqlDbType.NVarChar, 50);
query.CreateParameter<int>("@TargetID", targetID, SqlDbType.Int);
query.CreateParameter<TagType>("@Type", type, SqlDbType.TinyInt);
query.ExecuteNonQuery();
}
}
}
return true;
}
private void FillDoingComments(DoingCollection doings, SqlSession db)
{
if (doings.Count == 0)
return;
List<int> minIds = new List<int>();
List<int> maxIds = new List<int>();
for (int i = 0; i < doings.Count; i++)
{
if (doings[i].TotalComments == 0)
continue;
else if (doings[i].TotalComments == 1)
minIds.Add(doings[i].DoingID);
else
{
minIds.Add(doings[i].DoingID);
maxIds.Add(doings[i].DoingID);
}
}
if (minIds.Count == 0)
return;
using (SqlQuery query = db.CreateQuery())
{
query.CommandText = @"
SELECT * FROM bx_Comments WHERE CommentID IN(
SELECT Min(CommentID) FROM [bx_Comments] WHERE [Type]=3 AND IsApproved = 1 AND [TargetID] IN(@MinTargetIDs) GROUP BY TargetID
);
";
if (maxIds.Count > 0)
{
query.CommandText += @"
SELECT * FROM bx_Comments WHERE CommentID IN(
SELECT Max(CommentID) FROM [bx_Comments] WHERE [Type]=3 AND IsApproved = 1 AND [TargetID] IN(@MaxTargetIDs) GROUP BY TargetID
);
";
}
query.CreateInParameter<int>("@MinTargetIDs", minIds);
query.CreateInParameter<int>("@MaxTargetIDs", maxIds);
using (XSqlDataReader reader = query.ExecuteReader())
{
while (reader.Read())
{
Comment comment = new Comment(reader);
Doing doing = doings.GetValue(comment.TargetID);
if (doing != null)
{
if (doing.CommentList.ContainsKey(comment.CommentID) == false)
doing.CommentList.Add(comment);
}
}
if (reader.NextResult())
{
while (reader.Read())
{
Comment comment = new Comment(reader);
Doing doing = doings.GetValue(comment.TargetID);
if (doing != null)
{
if (doing.CommentList.ContainsKey(comment.CommentID) == false)
doing.CommentList.Add(comment);
}
}
}
}
}
db.Connection.Close();
//Doing doing = null;
//int lastDoingID = -1;
//for (int i = 0; i < comments.Count; i++)
//{
// int doingID = comments[i].TargetID;
// if (doingID != lastDoingID)
// {
// doing = doings.GetValue(doingID);
// lastDoingID = doingID;
// }
// doing.CommentList.Add(comments[i]);
//}
}
public override DoingCollection GetUserCommentedDoingsWithComments(int userID, int pageNumber, int pageSize, ref int? totalCount)
{
DoingCollection doings = null;
using (SqlSession db = new SqlSession())
{
using (SqlQuery query = new SqlQuery())
{
query.Pager.TableName = "[bx_Doings]";
query.Pager.SortField = "DoingID";
query.Pager.PageNumber = pageNumber;
query.Pager.PageSize = pageSize;
query.Pager.TotalRecords = totalCount;
query.Pager.IsDesc = true;
query.Pager.SelectCount = true;
query.Pager.Condition = "[DoingID] IN (SELECT [TargetID] FROM [bx_Comments] WHERE [Type]=3 AND [UserID] = @UserID)";
query.CreateParameter<int>("@UserID", userID, SqlDbType.Int);
using (XSqlDataReader reader = query.ExecuteReader())
{
doings = new DoingCollection(reader);
if (totalCount == null && reader.NextResult() && reader.Read())
{
totalCount = reader.Get<int>(0);
}
doings.TotalRecords = totalCount.GetValueOrDefault();
}
}
FillDoingComments(doings, db);
}
return doings;
}
public override DoingCollection GetEveryoneDoingsWithComments(int pageNumber, int pageSize, ref int? totalCount)
{
DoingCollection doings = null;
using (SqlSession db = new SqlSession())
{
using (SqlQuery query = new SqlQuery())
{
query.Pager.TableName = "[bx_Doings]";
query.Pager.SortField = "[DoingID]";
query.Pager.PageNumber = pageNumber;
query.Pager.PageSize = pageSize;
query.Pager.TotalRecords = totalCount;
query.Pager.IsDesc = true;
query.Pager.SelectCount = true;
using (XSqlDataReader reader = query.ExecuteReader())
{
doings = new DoingCollection(reader);
if (totalCount == null && reader.NextResult() && reader.Read())
{
totalCount = reader.Get<int>(0);
}
doings.TotalRecords = totalCount.GetValueOrDefault();
}
}
FillDoingComments(doings, db);
}
return doings;
}
public override DenouncingCollection GetDenouncingWithShare(DenouncingFilter filter, int pageNumber)
{
using (SqlSession db = new SqlSession())
{
DenouncingCollection denouncings = null;
using (SqlQuery query = db.CreateQuery())
{
query.Pager.PageNumber = pageNumber;
query.Pager.PageSize = filter.PageSize;
query.Pager.TableName = "bx_Denouncings";
query.Pager.SortField = "DenouncingID";
query.Pager.IsDesc = filter.IsDesc;
query.Pager.SelectCount = true;
filter.Type = DenouncingType.Share;
GetSearchDenouncingsCondition(query, filter);
using (XSqlDataReader reader = query.ExecuteReader())
{
denouncings = new DenouncingCollection(reader);
if (reader.NextResult())
{
while (reader.Read())
denouncings.TotalRecords = reader.Get<int>(0);
}
}
}
FillDenouncingContents(denouncings, db);
if (denouncings.Count > 0)
{
int[] targetIDs = GetTargetIDs(denouncings);
ShareCollection shares = null;
using (SqlQuery query = db.CreateQuery())
{
query.CommandText = "SELECT * FROM bx_SharesView WHERE ShareID IN (@IDs)";
query.CreateInParameter<int>("@IDs", targetIDs);
using (XSqlDataReader reader = query.ExecuteReader())
{
shares = new ShareCollection(reader);
}
}
ShareBO.Instance.ProcessKeyword(shares, ProcessKeywordMode.FillOriginalText);
for (int i = 0; i < denouncings.Count; i++)
{
for (int j = 0; j < shares.Count; j++)
{
if (denouncings[i].TargetID == shares[j].ShareID)
{
denouncings[i].TargetShare = shares[j];
break;
}
}
}
}
return denouncings;
}
}
public override DenouncingCollection GetDenouncingWithUser(DenouncingFilter filter, int pageNumber)
{
using (SqlSession db = new SqlSession())
{
DenouncingCollection denouncings = null;
using (SqlQuery query = db.CreateQuery())
{
query.Pager.PageNumber = pageNumber;
query.Pager.PageSize = filter.PageSize;
query.Pager.TableName = "bx_Denouncings";
query.Pager.SortField = "DenouncingID";
query.Pager.IsDesc = filter.IsDesc;
query.Pager.SelectCount = true;
filter.Type = DenouncingType.Space;
GetSearchDenouncingsCondition(query, filter);
using (XSqlDataReader reader = query.ExecuteReader())
{
denouncings = new DenouncingCollection(reader);
if (reader.NextResult())
{
while (reader.Read())
denouncings.TotalRecords = reader.Get<int>(0);
}
}
}
FillDenouncingContents(denouncings, db);
if (denouncings.Count > 0)
{
UserBO.Instance.WaitForFillSimpleUsers<Denouncing>(denouncings);
}
return denouncings;
}
}
/// <summary>
/// 保存标签
/// </summary>
/// <param name="tags">标签集</param>
/// <param name="type">类型,如日志标签等</param>
/// <param name="targetID">标签的对象ID</param>
public override bool SaveTags(TagCollection tags, TagType type, int targetID)
{
using (SqlSession db = new SqlSession())
{
using (SqlQuery query = db.CreateQuery())
{
query.CommandText = @"DELETE FROM [bx_TagRelation] WHERE [Type]=@Type AND [TargetID] = @TargetID;";
query.CreateParameter <int>("@TargetID", targetID, SqlDbType.Int);
query.CreateParameter <TagType>("@Type", type, SqlDbType.TinyInt);
query.ExecuteNonQuery();
}
if (tags == null || tags.Count == 0)
{
return(true);
}
using (SqlQuery query = db.CreateQuery(QueryMode.Prepare))
{
query.CommandText = @"
IF EXISTS (SELECT * FROM [bx_Tags] WHERE [Name] = @TagName) BEGIN
DECLARE @TagID int;
SET @TagID = (SELECT TOP 1 [ID] FROM [bx_Tags] WHERE [Name] = @TagName);
DECLARE @PrivacyType tinyint;
IF @Type = 1
SET @PrivacyType = (SELECT [PrivacyType] FROM [bx_BlogArticles] WHERE [ArticleID] = @TargetID);
ELSE
SET @PrivacyType = 0;
IF NOT EXISTS (SELECT * FROM [bx_TagRelation] WHERE [TagID] = @TagID) BEGIN
IF @PrivacyType = 0 OR @PrivacyType = 3 BEGIN
INSERT INTO [bx_TagRelation]([TagID], [Type], [TargetID]) VALUES(@TagID, @Type, @TargetID);
END
END
ELSE BEGIN
IF EXISTS (SELECT * FROM [bx_TagRelation] WHERE [TagID] = @TagID AND [Type] = @Type AND [TargetID] = @TargetID) BEGIN
IF @PrivacyType <> 0 AND @PrivacyType <> 3 BEGIN
DELETE FROM [bx_TagRelation] WHERE [TagID] = @TagID AND [Type] = @Type AND [TargetID] = @TargetID;
END
END
ELSE BEGIN
IF @PrivacyType = 0 OR @PrivacyType = 3 BEGIN
INSERT INTO [bx_TagRelation]([TagID], [Type], [TargetID]) VALUES(@TagID, @Type, @TargetID);
END
END
END
END
ELSE BEGIN
DECLARE @NewTagID int;
INSERT INTO [bx_Tags]([Name]) VALUES (@TagName);
SET @NewTagID = @@IDENTITY;
IF @Type = 1 AND EXISTS (SELECT * FROM [bx_BlogArticles] WHERE [ArticleID] = @TargetID AND [PrivacyType] IN (0,3)) BEGIN
INSERT INTO [bx_TagRelation]([TagID], [Type], [TargetID]) VALUES(@NewTagID, @Type, @TargetID);
END
END";
foreach (Tag tag in tags)
{
query.CreateParameter <string>("@TagName", tag.Name, SqlDbType.NVarChar, 50);
query.CreateParameter <int>("@TargetID", targetID, SqlDbType.Int);
query.CreateParameter <TagType>("@Type", type, SqlDbType.TinyInt);
query.ExecuteNonQuery();
}
}
}
return(true);
}
public override void DeleteExperisValidateCodeActionRecord(List<string> actions, List<int> limitedTimes)
{
using (SqlSession db = new SqlSession())
{
using (SqlQuery query = db.CreateQuery(QueryMode.Batch))
{
StringBuilder sql = new StringBuilder();
for (int i = 0; i < actions.Count; i++)
{
sql.AppendFormat(@"
DELETE [bx_ValidateCodeActionRecords] WHERE [Action] = @Action_{0} AND [CreateDate] < @LimitedTime_{0};
", i);
query.CreateParameter<string>("@Action_" + i, actions[i], SqlDbType.VarChar, 200);
query.CreateParameter<DateTime>("@LimitedTime_" + i, DateTimeUtil.Now.AddSeconds(0 - limitedTimes[i]), SqlDbType.DateTime);
//query.ExecuteNonQuery();
}
//query.Submit();
if (actions.Count > 0)
{
query.CommandText += "DELETE [bx_ValidateCodeActionRecords] WHERE [Action] NOT IN (@Actions);";
query.CreateInParameter<string>("@Actions", actions);
}
else
query.CommandText += "DELETE [bx_ValidateCodeActionRecords]";
query.ExecuteNonQuery();
}
}
}
public override DenouncingCollection GetDenouncingWithShare(DenouncingFilter filter, int pageNumber)
{
using (SqlSession db = new SqlSession())
{
DenouncingCollection denouncings = null;
using (SqlQuery query = db.CreateQuery())
{
query.Pager.PageNumber = pageNumber;
query.Pager.PageSize = filter.PageSize;
query.Pager.TableName = "bx_Denouncings";
query.Pager.SortField = "DenouncingID";
query.Pager.IsDesc = filter.IsDesc;
query.Pager.SelectCount = true;
filter.Type = DenouncingType.Share;
GetSearchDenouncingsCondition(query, filter);
using (XSqlDataReader reader = query.ExecuteReader())
{
denouncings = new DenouncingCollection(reader);
if (reader.NextResult())
{
while (reader.Read())
{
denouncings.TotalRecords = reader.Get <int>(0);
}
}
}
}
FillDenouncingContents(denouncings, db);
if (denouncings.Count > 0)
{
int[] targetIDs = GetTargetIDs(denouncings);
ShareCollection shares = null;
using (SqlQuery query = db.CreateQuery())
{
query.CommandText = "SELECT * FROM bx_SharesView WHERE ShareID IN (@IDs)";
query.CreateInParameter <int>("@IDs", targetIDs);
using (XSqlDataReader reader = query.ExecuteReader())
{
shares = new ShareCollection(reader);
}
}
ShareBO.Instance.ProcessKeyword(shares, ProcessKeywordMode.FillOriginalText);
for (int i = 0; i < denouncings.Count; i++)
{
for (int j = 0; j < shares.Count; j++)
{
if (denouncings[i].TargetID == shares[j].ShareID)
{
denouncings[i].TargetShare = shares[j];
break;
}
}
}
}
return(denouncings);
}
}
public override UserCollection GetUserShows(int userID, int pageNumber, int pageSize, out int userTotalCount)
{
userTotalCount = 0;
UserCollection users = new UserCollection();
using (SqlSession db = new SqlSession())
{
using (SqlQuery query = db.CreateQuery())
{
query.Pager.TableName = "bx_PointShowUsers";
query.Pager.SortField = "Price";
query.Pager.IsDesc = true;
query.Pager.PageNumber = pageNumber;
query.Pager.PageSize = pageSize;
query.Pager.SelectCount = true;
query.Pager.PrimaryKey = "UserID";
query.CreateParameter<int>("@UserID", userID, SqlDbType.Int);
using (XSqlDataReader reader = query.ExecuteReader())
{
users = new UserCollection(reader);
if (reader.NextResult())
{
if (reader.Read())
{
users.TotalRecords = reader.Get<int>(0);
userTotalCount = users.TotalRecords;
}
}
}
}
return users;
}
}
public override FeedCollection SearchFeeds(int pageNumber, FeedSearchFilter filter, ref int totalCount)
{
using (SqlSession db = new SqlSession())
{
List<int> feedIDs = new List<int>();
using (SqlQuery query = db.CreateQuery())
{
query.Pager.IsDesc = filter.IsDesc;
if (filter.UserID != null)
{
query.Pager.ResultFields = "[FeedID]";
if (filter.Order == FeedSearchFilter.OrderBy.ID)
query.Pager.SortField = "[ID]";
else
{
query.Pager.SortField = "[CreateDate]";
query.Pager.PrimaryKey = "[ID]";
}
query.Pager.TableName = "[bx_UserFeeds]";
query.Pager.Condition = @" UserID = @UserID "
+ (filter.AppID == null ? "" : (@" AND [FeedID] IN(SELECT [ID] FROM [bx_Feeds] WHERE [AppID]=@AppID "
+ (filter.AppActionType == null ? "" : (" AND [ActionType] = @ActionType)"))))
+ (filter.BeginDate == null ? "" : (" AND [CreateDate] > @BeginDate"))
+ (filter.EndDate == null ? "" : (" AND [CreateDate] < @EndDate"));
}
else
{
query.Pager.ResultFields = "[ID]";
if (filter.Order == FeedSearchFilter.OrderBy.ID)
query.Pager.SortField = "[ID]";
else
{
query.Pager.SortField = "[CreateDate]";
query.Pager.PrimaryKey = "[ID]";
}
query.Pager.TableName = "[bx_Feeds]";
query.Pager.Condition = @" 1 = 1 "
+ (filter.AppID == null ? "" : (@" AND [AppID]=@AppID ")
+ (filter.AppActionType == null ? "" : (" AND [ActionType] = @ActionType")))
+ (filter.BeginDate == null ? "" : (" AND [CreateDate] > @BeginDate"))
+ (filter.EndDate == null ? "" : (" AND [CreateDate] < @EndDate"));
}
query.Pager.PageNumber = pageNumber;
query.Pager.PageSize = filter.PageSize;
query.Pager.TotalRecords = totalCount;
query.Pager.SelectCount = true;
query.CreateParameter<int?>("@UserID", filter.UserID, SqlDbType.Int);
query.CreateParameter<Guid?>("@AppID", filter.AppID, SqlDbType.UniqueIdentifier);
query.CreateParameter<int?>("@ActionType", filter.AppActionType, SqlDbType.Int);
query.CreateParameter<DateTime?>("@BeginDate", filter.BeginDate, SqlDbType.DateTime);
query.CreateParameter<DateTime?>("@EndDate", filter.EndDate, SqlDbType.DateTime);
using (XSqlDataReader reader = query.ExecuteReader())
{
while (reader.Read())
{
feedIDs.Add(reader.GetInt32(0));
}
if (reader.NextResult())
{
while (reader.Read())
{
totalCount = reader.GetInt32(0);
}
}
}
}
if (feedIDs.Count == 0)
return new FeedCollection();
string sql;
string sortField = (filter.Order == FeedSearchFilter.OrderBy.ID ? "[ID]" : "[CreateDate]");
string order = (filter.IsDesc ? " DESC " : " ASC ");
if (filter.UserID == null)
{
sql = @"
SELECT * FROM [bx_Feeds] WHERE [ID] IN(@FeedIDs) ORDER BY " + sortField + order + @";
SELECT * FROM [bx_UserFeeds] WHERE [FeedID] IN(@FeedIDs) ORDER BY [FeedID] DESC,[CreateDate] DESC;
";
}
else
{
sql = @"
SELECT * FROM [bx_Feeds] WHERE [ID] IN(@FeedIDs) ORDER BY " + sortField + order + @";
SELECT * FROM [bx_UserFeeds] WHERE [FeedID] IN(@FeedIDs) ORDER BY [FeedID] DESC,[CreateDate] DESC;
SELECT * FROM [bx_UserFeeds] WHERE [FeedID] IN(SELECT [FeedID] FROM [bx_UserFeeds] UF INNER JOIN [bx_Feeds] F ON UF.[FeedID]=F.[ID] WHERE F.[ID] IN(@FeedIDs) AND UF.[UserID]=F.[TargetUserID] AND F.[TargetUserID]=@UserID) ORDER BY [CreateDate] DESC;
";
}
using (SqlQuery query = db.CreateQuery())
{
query.CommandText = sql;
query.CreateParameter<int?>("@UserID", filter.UserID, SqlDbType.Int);
query.CreateInParameter<int>("@FeedIDs", feedIDs);
using (XSqlDataReader reader = query.ExecuteReader())
{
FeedCollection feeds = new FeedCollection(reader);
if (reader.NextResult())
{
Feed currentFeed = null;
while (reader.Read())
{
UserFeed userFeed = new UserFeed(reader);
currentFeed = ProcessFeed(feeds, currentFeed, userFeed, false);
}
}
if (reader.NextResult())// 这一组是特殊的 处理类似加好友的 如果TargetUserID 是好友 需要把UserFeeds 表里与该动态有关的用户都取出来
{
Feed currentFeed = null;
while (reader.Read())
{
UserFeed userFeed = new UserFeed(reader);
currentFeed = ProcessFeed(feeds, currentFeed, userFeed, true);
}
}
return feeds;
}
}
}
}
