void GetRiskModTestSiteAndForum(IDnaDataReader reader, out int siteid, out int forumid)
{
reader.ExecuteWithinATransaction(@" select top 1 g.forumid,g.siteid
from topics t
join guideentries g on g.h2g2id=t.h2g2id
order by topicid");
reader.Read();
forumid = reader.GetInt32("forumid");
siteid = reader.GetInt32("siteid");
reader.Close();
}
int CountEventQueueEntries(IDnaDataReader reader)
{
reader.ExecuteWithinATransaction("select count(*) as C from eventqueue");
reader.Read();
int c = reader.GetInt32("C");
reader.Close();
return c;
}
void ClearRiskModThreadEntryQueue(IDnaDataReader reader)
{
reader.ExecuteWithinATransaction("delete from RiskModThreadEntryQueue");
reader.Close();
}
void SetUpEmailEventQueue(IDnaDataReader reader)
{
string sql = @"
DECLARE @NodeType int, @ArticleType int, @ClubType int, @ForumType int, @ThreadType int, @PostType int, @UserType int, @VoteType int, @LinkType int, @TeamType int, @URLType int
EXEC SetItemTypeValInternal 'IT_NODE', @NodeType OUTPUT
EXEC SetItemTypeValInternal 'IT_H2G2', @ArticleType OUTPUT
EXEC SetItemTypeValInternal 'IT_CLUB', @ClubType OUTPUT
EXEC SetItemTypeValInternal 'IT_FORUM', @ForumType OUTPUT
EXEC SetItemTypeValInternal 'IT_THREAD', @ThreadType OUTPUT
EXEC SetItemTypeValInternal 'IT_POST', @PostType OUTPUT
EXEC SetItemTypeValInternal 'IT_USER', @UserType OUTPUT
EXEC SetItemTypeValInternal 'IT_VOTE', @VoteType OUTPUT
EXEC SetItemTypeValInternal 'IT_LINK', @LinkType OUTPUT
EXEC SetItemTypeValInternal 'IT_CLUB_MEMBERS', @TeamType OUTPUT
EXEC SetItemTypeValInternal 'IT_URL', @URLType OUTPUT
-- Now get all the values for the different events that can happen
DECLARE @ArticleEdit int, @ArticleTagged int, @TaggedArticleEdited int, @ForumEdit int, @NewTeamMember int, @PostRepliedTo int, @NewThread int, @ThreadTagged int
DECLARE @UserTagged int, @ClubTagged int, @LinkAdded int, @VoteAdded int, @VoteRemoved int, @OwnerTeamChange int, @MemberTeamChange int, @MemberApplication int, @ClubEdit int, @NodeHidden int
EXEC SetEventTypeValInternal 'ET_ARTICLEEDITED', @ArticleEdit OUTPUT
EXEC SetEventTypeValInternal 'ET_CATEGORYARTICLETAGGED', @ArticleTagged OUTPUT
EXEC SetEventTypeValInternal 'ET_CATEGORYARTICLEEDITED', @TaggedArticleEdited OUTPUT
EXEC SetEventTypeValInternal 'ET_FORUMEDITED', @ForumEdit OUTPUT
EXEC SetEventTypeValInternal 'ET_NEWTEAMMEMBER', @NewTeamMember OUTPUT
EXEC SetEventTypeValInternal 'ET_POSTREPLIEDTO', @PostRepliedTo OUTPUT
EXEC SetEventTypeValInternal 'ET_POSTNEWTHREAD', @NewThread OUTPUT
EXEC SetEventTypeValInternal 'ET_CATEGORYTHREADTAGGED', @ThreadTagged OUTPUT
EXEC SetEventTypeValInternal 'ET_CATEGORYUSERTAGGED', @UserTagged OUTPUT
EXEC SetEventTypeValInternal 'ET_CATEGORYCLUBTAGGED', @ClubTagged OUTPUT
EXEC SetEventTypeValInternal 'ET_NEWLINKADDED', @LinkAdded OUTPUT
EXEC SetEventTypeValInternal 'ET_VOTEADDED', @VoteAdded OUTPUT
EXEC SetEventTypeValInternal 'ET_VOTEREMOVED', @VoteRemoved OUTPUT
EXEC SetEventTypeValInternal 'ET_CLUBOWNERTEAMCHANGE', @OwnerTeamChange OUTPUT
EXEC SetEventTypeValInternal 'ET_CLUBMEMBERTEAMCHANGE', @MemberTeamChange OUTPUT
EXEC SetEventTypeValInternal 'ET_CLUBMEMBERAPPLICATIONCHANGE', @MemberApplication OUTPUT
EXEC SetEventTypeValInternal 'ET_CLUBEDITED', @ClubEdit OUTPUT
EXEC SetEventTypeValInternal 'ET_CATEGORYHIDDEN', @NodeHidden OUTPUT
declare @uid uniqueidentifier,@ItemID int,@ItemType int,@EventType int,@ItemID2 int,@ItemType2 int,@NotifyType int
set @uid=newid()
-- Set up the email alert lists
INSERT INTO [dbo].[EMailAlertList] ([EMailAlertListID],[UserID],[CreatedDate],[LastUpdated],[SiteID]) VALUES (@uid, 6, getdate(), getdate(),1)
INSERT INTO [dbo].InstantEMailAlertList (InstantEMailAlertListID,[UserID],[CreatedDate],[LastUpdated],[SiteID]) VALUES (@uid, 6, getdate(), getdate(),1)
-- Now insert a bunch of email event queue items to test the various branches
-- case 1 & 2
select top 1 @ItemID =h.nodeid,@ItemType =@NodeType,@EventType =@UserTagged,@ItemID2 =0,@ItemType2 =0,@NotifyType =0
from Hierarchy h
INSERT INTO [dbo].[EMailEventQueue] ([ListID],[SiteID],[ItemID],[ItemType],[EventType],[EventDate],[ItemID2],[ItemType2],[NotifyType],[EventUserID],[IsOwner])
VALUES (@uid,1,@ItemID,@ItemType,@EventType,getdate(),@ItemID2,@ItemType2,@NotifyType,6,1)
-- case 3 & 4
-- can't be done because inner joins on threads where threadid=0
--case 5 & 6
select top 1 @ItemID =f.forumid,@ItemType =@ForumType,@EventType =@ForumEdit,@ItemID2 =t.threadid,@ItemType2 =@ThreadType,@NotifyType =0
from forums f
join threads t on t.forumid=f.forumid
where t.VisibleTo IS NULL
INSERT INTO [dbo].[EMailEventQueue] ([ListID],[SiteID],[ItemID],[ItemType],[EventType],[EventDate],[ItemID2],[ItemType2],[NotifyType],[EventUserID],[IsOwner])
VALUES (@uid,1,@ItemID,@ItemType,@EventType,getdate(),@ItemID2,@ItemType2,@NotifyType,6,1)
-- case 7 & 8
select top 1 @ItemID =t.threadid,@ItemType =@ThreadType,@EventType =@PostRepliedTo,@ItemID2 =te.entryid,@ItemType2 =@PostType,@NotifyType =0
from forums f
join threads t on t.forumid=f.forumid
join threadentries te on te.threadid=t.threadid
where t.VisibleTo IS NULL and te.hidden is null
INSERT INTO [dbo].[EMailEventQueue] ([ListID],[SiteID],[ItemID],[ItemType],[EventType],[EventDate],[ItemID2],[ItemType2],[NotifyType],[EventUserID],[IsOwner])
VALUES (@uid,1,@ItemID,@ItemType,@EventType,getdate(),@ItemID2,@ItemType2,@NotifyType,6,1)
-- case 9 & 10
select top 1 @ItemID =h.nodeid,@ItemType =@NodeType,@EventType =@ThreadTagged,@ItemID2 =t.threadid,@ItemType2 =@ThreadType,@NotifyType =0
from forums f
join hierarchy h on nodeid=nodeid
join threads t on t.forumid=f.forumid
where t.VisibleTo IS NULL
INSERT INTO [dbo].[EMailEventQueue] ([ListID],[SiteID],[ItemID],[ItemType],[EventType],[EventDate],[ItemID2],[ItemType2],[NotifyType],[EventUserID],[IsOwner])
VALUES (@uid,1,@ItemID,@ItemType,@EventType,getdate(),@ItemID2,@ItemType2,@NotifyType,6,1)
-- case 11, 12, 13, 14, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28 - clubs, not in use so not tested
-- case 15 & 16
select top 1 @ItemID =h.nodeid,@ItemType =@NodeType,@EventType =@ArticleTagged,@ItemID2 =g.h2g2id,@ItemType2 =@ArticleType,@NotifyType =0
from hierarchy h, guideentries g
where g.hidden IS NULL
INSERT INTO [dbo].[EMailEventQueue] ([ListID],[SiteID],[ItemID],[ItemType],[EventType],[EventDate],[ItemID2],[ItemType2],[NotifyType],[EventUserID],[IsOwner])
VALUES (@uid,1,@ItemID,@ItemType,@EventType,getdate(),@ItemID2,@ItemType2,@NotifyType,6,1)
-- case 17 & 18
select top 1 @ItemID =g.h2g2id,@ItemType =@ArticleType,@EventType =@ArticleEdit,@ItemID2 =0,@ItemType2 =0,@NotifyType =0
from guideentries g
where g.hidden IS NULL
INSERT INTO [dbo].[EMailEventQueue] ([ListID],[SiteID],[ItemID],[ItemType],[EventType],[EventDate],[ItemID2],[ItemType2],[NotifyType],[EventUserID],[IsOwner])
VALUES (@uid,1,@ItemID,@ItemType,@EventType,getdate(),@ItemID2,@ItemType2,@NotifyType,6,1)
-- case 19 & 20
select top 1 @ItemID =g.h2g2id,@ItemType =@ArticleType,@EventType =@ArticleEdit,@ItemID2 =0,@ItemType2 =0,@NotifyType =0
from guideentries g
where g.hidden IS NULL
INSERT INTO [dbo].[EMailEventQueue] ([ListID],[SiteID],[ItemID],[ItemType],[EventType],[EventDate],[ItemID2],[ItemType2],[NotifyType],[EventUserID],[IsOwner])
VALUES (@uid,1,@ItemID,@ItemType,@EventType,getdate(),@ItemID2,@ItemType2,@NotifyType,6,1)
-- case 29 & 30
select top 1 @ItemID =h.nodeid,@ItemType =0,@EventType =@NodeHidden,@ItemID2 =h.nodeid,@ItemType2 =0,@NotifyType =0
from hierarchy h
INSERT INTO [dbo].[EMailEventQueue] ([ListID],[SiteID],[ItemID],[ItemType],[EventType],[EventDate],[ItemID2],[ItemType2],[NotifyType],[EventUserID],[IsOwner])
VALUES (@uid,1,@ItemID,@ItemType,@EventType,getdate(),@ItemID2,@ItemType2,@NotifyType,6,1)";
reader.ExecuteWithinATransaction(sql);
reader.Close();
}
void ClearEventQueue(IDnaDataReader reader)
{
reader.ExecuteWithinATransaction("delete from eventqueue");
reader.Close();
}
string GetNextIndentityId(IDnaDataReader reader)
{
string sql = @"select IdentityUserID from signinuseridmapping
where cast(identityuserid as bigint) = (select max(cast(identityuserid as bigint)) from signinuseridmapping)";
reader.ExecuteWithinATransaction(sql);
reader.Read();
string identityId = reader.GetString("IdentityUserID");
reader.Close();
Int64 id = Int64.Parse(identityId);
return (id+1).ToString();
}
void EmailEnc_SetUp_fetchrecommendationdetails(IDnaDataReader reader, out int recId, out int entryId)
{
// Find a Scout Recommendation that matches the SP's join requirements
string sql = @"select top 1 RecommendationID, SR.EntryId" + NL +
"from ScoutRecommendations SR" + NL +
"inner join GuideEntries G on G.EntryID = SR.EntryID" + NL +
"inner join Users U1 on U1.UserID = SR.ScoutID" + NL +
"inner join Users U2 on U2.UserID = G.Editor" + NL +
"INNER JOIN Journals J1 on J1.UserID = U1.UserID and J1.SiteID = G.SiteID" + NL +
"INNER JOIN Journals J2 on J2.UserID = U2.UserID and J2.SiteID = G.SiteID";
reader.ExecuteWithinATransaction(sql);
reader.Read();
recId = reader.GetInt32("RecommendationID");
entryId = reader.GetInt32("EntryId");
reader.Close();
// Set up the scout and editor user ids to something predictable
reader.ExecuteWithinATransaction("UPDATE ScoutRecommendations SET ScoutId=42 WHERE RecommendationID=" + recId);
reader.ExecuteWithinATransaction("UPDATE GuideEntries SET Editor=6 WHERE EntryId=" + entryId);
// Set up the email adresses in the user accounts
UpdateUserEmailAddress(reader, 42, "*****@*****.**");
UpdateUserEmailAddress(reader, 6, "*****@*****.**");
reader.Close();
}
SqlBinary HashEmailAddress(IDnaDataReader reader,string email)
{
reader.ExecuteWithOpenKey("select dbo.udf_hashemailaddress('" + email + "') AS HashedEmail");
reader.Read();
var hashedEmail = reader.GetSqlBinary("HashedEmail");
reader.Close();
return hashedEmail;
}
void AddEmailToBannedList(IDnaDataReader reader, string email, int siginBanned, int complaintBanned)
{
string sql = @"EXEC addemailtobannedlist @email='" + email + "', @signinbanned =" + siginBanned + ", @complaintbanned =" + complaintBanned + ", @editorid = 6";
reader.ExecuteWithinATransaction(sql);
reader.Close();
}
GuideEntryInfo GetGuideEntryInfo(IDnaDataReader reader,int entryId)
{
string sql = @"select * from guideentries where entryid="+entryId;
reader.ExecuteWithinATransaction(sql);
reader.Read();
var info = new GuideEntryInfo();
info.EntryId = reader.GetInt32("entryid");
info.Editor = reader.GetInt32("editor");
reader.Close();
return info;
}
void UpdateUserEmailAddress(IDnaDataReader reader, int userId, string email)
{
string sql;
if (email != null)
sql = string.Format(@"update users set encryptedemail=dbo.udf_encryptemailaddress('{0}',{1}) where userid={1}",email,userId);
else
sql = string.Format(@"update users set encryptedemail=dbo.udf_encryptemailaddress(NULL,{0}) where userid={0}",userId);
reader.ExecuteWithOpenKey(sql);
reader.Close();
}
int FindGuideEntry(IDnaDataReader reader)
{
string sql=@"select top 1 * from guideentries where text like '<GUIDE%' and siteid=1";
reader.ExecuteWithinATransaction(sql);
reader.Read();
int entryId = reader.GetInt32("entryid");
reader.Close();
return entryId;
}
int CreateNewUser(IDnaDataReader reader, string email)
{
string id = GetNextIndentityId(reader);
string sql = string.Format(@"EXEC [dbo].[createnewuserfromidentityid] @identityuserid ='{0}',"+NL+
"@legacyssoid = null,"+NL+
"@username = '******',"+NL+
"@email = '{1}'",id, email);
reader.ExecuteWithinATransaction(sql);
sql = "SELECT DnaUserId FROM SignInUserIdMapping WHERE IdentityUserId='" + id + "'";
reader.ExecuteWithinATransaction(sql);
reader.Read();
int userId = reader.GetInt32("DnaUserId");
reader.Close();
return userId;
}
int GetNextUserId(IDnaDataReader reader)
{
reader.ExecuteWithinATransaction(@"select max(userid) AS maxuserid from users");
reader.Read();
int maxuserId = reader.GetInt32("maxuserid");
reader.Close();
return maxuserId + 1;
}
void RiskModTestPostHelper(IDnaDataReader reader, int forumid, int? threadid, int? inreplyto, int userid, string content, out int? newthreadid, out int? newthreadentryid, bool ignoremoderation, bool forcepremodposting)
{
string sql = string.Format(@"
-- Pretend this user has never posted to this forum, to make sure the check in posttoforuminternal
-- that stops the same user from creating a new conversation within a minute of the last one, doesn't stop the post
update threadentries set forumid=-{0} where forumid={0} and userid={3}
declare @hash uniqueidentifier, @returnthread int, @returnpost int, @premodpostingmodid int, @ispremoderated int
set @hash=newid()
exec posttoforuminternal @userid= {3},
@forumid = {0},
@inreplyto = {2},
@threadid = {1},
@subject ='The Cabinet',
@content ='{4}',
@poststyle =1,
@hash =@hash,
@keywords ='',
@nickname ='the furry one',
@returnthread = @returnthread OUTPUT,
@returnpost = @returnpost OUTPUT,
@type = NULL,
@eventdate = NULL,
@forcemoderate = 0,
@forcepremoderation = 0,
@ignoremoderation = {6},
@allowevententries = 1,
@nodeid = 0,
@ipaddress = 'testip',
@queueid = null,
@clubid = 0,
@premodpostingmodid =@premodpostingmodid OUTPUT,
@ispremoderated =@ispremoderated OUTPUT,
@bbcuid = '{5}',
@isnotable = 0,
@iscomment = 0,
@modnotes = NULL,
@isthreadedcomment = 0,
@ignoreriskmoderation = 0,
@forcepremodposting = {7},
@forcepremodpostingdate = NULL,
@riskmodthreadentryqueueid = NULL;
-- put the userids back
update threadentries set forumid={0} where forumid =-{0} and userid={3}
select @returnthread AS returnthread, @returnpost as returnpost",
forumid,
threadid.HasValue ? threadid.ToString() : "NULL",
inreplyto.HasValue ? inreplyto.ToString() : "NULL",
userid,
content,
testGUID.ToString(),
ignoremoderation ? 1 : 0,
forcepremodposting ? 1 : 0);
reader.ExecuteWithinATransaction(sql);
reader.Read();
newthreadid = reader.GetNullableInt32("returnthread");
newthreadentryid = reader.GetNullableInt32("returnpost");
reader.Close();
}
void FixUpModActionTable(IDnaDataReader reader)
{
reader.ExecuteWithinATransaction("delete ModAction");
reader.ExecuteWithinATransaction("insert ModAction select 0,'None' UNION select 1,'Edited' UNION select 2,'Hidden' UNION select 3,'Unhidden'");
reader.Close();
}
void CheckRiskModThreadEntryQueue(IDnaDataReader reader, int? ThreadEntryId, char PublishMethod, bool? IsRisky, TestDate DateAssessed, int SiteId, int ForumId, int? ThreadId, int UserId, string UserName, int? InReplyTo, string Subject, string Text, byte PostStyle, string IPAddress, string BBCUID, DateTime EventDate, byte AllowEventEntries, int NodeId, int? QueueId, int ClubId, byte IsNotable, byte IsComment, string ModNotes, byte IsThreadedComment)
{
reader.ExecuteWithinATransaction(@"SELECT rm.*
FROM RiskModThreadEntryQueue rm
WHERE RiskModThreadEntryQueueId=" + GetLatestRiskModThreadEntryQueueId(reader));
reader.Read();
// Nasty tweak. The RiskModThreadEntryQueue table always stores undefined ThreadId and ThreadEntryId values
// as NULL. We treat zero values as NULL for comparision purposes
ThreadId = NullIf(ThreadId, 0);
ThreadEntryId = NullIf(ThreadEntryId, 0);
TestNullableIntField(reader, "ThreadEntryId", ThreadEntryId);
TestPublishMethod(reader, PublishMethod);
TestNullableBoolField(reader, "IsRisky", IsRisky);
TestNullableDateField(reader, "DateAssessed", DateAssessed);
Assert.AreEqual(SiteId,reader.GetInt32("SiteId"));
Assert.AreEqual(ForumId,reader.GetInt32("ForumId"));
TestNullableIntField(reader, "ThreadId", ThreadId);
Assert.AreEqual(UserId,reader.GetInt32("UserId"));
Assert.AreEqual(UserName, reader.GetString("UserName"));
TestNullableIntField(reader, "InReplyTo", InReplyTo);
Assert.AreEqual(Subject,reader.GetString("Subject"));
Assert.AreEqual(Text,reader.GetString("Text"));
Assert.AreEqual(PostStyle, reader.GetByte("PostStyle"));
Assert.AreEqual(IPAddress,reader.GetString("IPAddress"));
Assert.AreEqual(BBCUID,reader.GetGuidAsStringOrEmpty("BBCUID"));
//Assert.AreEqual(EventDate,reader.GetInt32("EventDate"));
Assert.AreEqual(AllowEventEntries,reader.GetByte("AllowEventEntries"));
//Assert.AreEqual(NodeId,reader.GetInt32("NodeId"));
TestNullableIntField(reader, "QueueId", QueueId);
Assert.AreEqual(ClubId,reader.GetInt32("ClubId"));
Assert.AreEqual(IsNotable, reader.GetByte("IsNotable"));
Assert.AreEqual(IsComment, reader.GetByte("IsComment"));
TestNullableStringField(reader, "ModNotes", ModNotes);
Assert.AreEqual(IsThreadedComment, reader.GetByte("IsThreadedComment"));
reader.Close();
}
// create procedure fetcharticlemoderationhistory @h2g2id int
/*
use smallguide
begin tran
select j1.forumid,u1.*
FROM Users u1
INNER JOIN Journals J1 ON J1.UserID = u1.UserID and J1.SiteID = 1
--INNER JOIN FaveForums f ON f.ForumID = J1.ForumID
--INNER JOIN Users u ON f.UserID = u.UserID
--INNER JOIN Journals J on J.UserID = u.UserID --and J.SiteID = 1
--INNER JOIN Forums fo ON fo.ForumID = J.ForumID
insert FaveForums values (5819,5)
exec watchingusers @userid =5, @siteid =1, @skip = 0, @show = 100000
rollback tran
use master
*/
#endregion
#region Helper methods
EmailValues GetUserEmailValues(IDnaDataReader reader, int userId)
{
string sql = string.Format(@"SELECT DecryptedEmail = dbo.udf_decryptemailaddress(EncryptedEmail,UserID), EncryptedEmail, HashedEmail FROM Users WHERE UserId=" + userId);
reader.ExecuteWithOpenKey(sql);
reader.Read();
EmailValues emailValues = new EmailValues();
emailValues.DecryptedEmail = reader.GetString("DecryptedEmail");
emailValues.EncryptedEmail = reader.GetSqlBinary("EncryptedEmail");
emailValues.HashedEmail = reader.GetSqlBinary("HashedEmail");
reader.Close();
return emailValues;
}
