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;
}
void CheckLatestPremodPostings(IDnaDataReader reader, int modId, int userid, int forumid, int? threadid, int? inReplyTo, string body, int postStyle, int siteid, byte isComment, int? riskModThreadEntryQueueId)
{
reader.ExecuteWithinATransaction(@"SELECT * FROM PremodPostings where ModId="+modId);
reader.Read();
// Nasty tweak. With PremodPostings, when it creates a thread mod entry, it sets threadid=null and postid=0
// posttoforuminternal returns 0 for both thread id and threadentry id, so here we're treating 0 as null
threadid = NullIf(threadid, 0);
Assert.AreEqual(userid, reader.GetInt32("userid"));
Assert.AreEqual(forumid, reader.GetInt32("forumid"));
TestNullableIntField(reader, "threadid", threadid);
TestNullableIntField(reader, "inReplyTo", inReplyTo);
Assert.AreEqual(body, reader.GetString("body"));
Assert.AreEqual(postStyle, reader.GetInt32("postStyle"));
Assert.AreEqual(siteid, reader.GetInt32("siteid"));
Assert.AreEqual(isComment, reader.GetByte("isComment"));
TestNullableIntField(reader, "riskModThreadEntryQueueId", riskModThreadEntryQueueId);
}
int GetLatestRiskModThreadEntryQueueId(IDnaDataReader reader)
{
reader.ExecuteWithinATransaction(@"SELECT top 1 * FROM RiskModThreadEntryQueue order by RiskModThreadEntryQueueId desc");
reader.Read();
if (reader.HasRows)
return reader.GetInt32("RiskModThreadEntryQueueId");
return -1;
}
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();
}
void CheckLatestThreadEntry(IDnaDataReader reader, int threadid, int forumid, int userid, int? nextSibling, int? parent, int? prevSibling, int? firstChild, int entryID, int? hidden, int postIndex, byte postStyle, string text)
{
reader.ExecuteWithinATransaction(@"SELECT top 1 * FROM ThreadEntries order by EntryID desc");
reader.Read();
Assert.AreEqual(threadid, reader.GetInt32("threadid"));
Assert.AreEqual(forumid, reader.GetInt32("forumid"));
Assert.AreEqual(userid, reader.GetInt32("userid"));
TestNullableIntField(reader, "nextSibling", nextSibling);
TestNullableIntField(reader, "parent", parent);
TestNullableIntField(reader, "prevSibling", prevSibling);
TestNullableIntField(reader, "firstChild", firstChild);
Assert.AreEqual(entryID, reader.GetInt32("entryID"));
TestNullableIntField(reader, "hidden", hidden);
Assert.AreEqual(postIndex, reader.GetInt32("postIndex"));
Assert.AreEqual(postStyle, reader.GetByte("postStyle"));
Assert.AreEqual(text, reader.GetString("text"));
}
void SetSiteOptionInt(IDnaDataReader reader, int siteid, string section, string name, int val, int type)
{
string sql =string.Format(@"
delete siteoptions where siteid={0} and section='{1}' and name='{2}';
insert siteoptions(siteid,section,name,value,type) values ({0},'{1}','{2}','{3}',{4});", siteid,section,name,val,type);
reader.ExecuteWithinATransaction(sql);
}
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 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 RiskMod_ProcessRiskAssessmentForThreadEntry(IDnaDataReader reader, int riskModId, string isRisky, out int? newThreadId, out int? newThreadEntryId)
{
string sql = string.Format(@"
declare @ret int
exec @ret=riskmod_processriskassessmentforthreadentry @riskmodthreadentryqueueid={0}, @risky={1}
", riskModId, RiskyOrNotRisky(isRisky));
reader.ExecuteWithinATransaction(sql);
reader.Read();
if (reader.DoesFieldExist("ThreadId"))
{
newThreadId = reader.GetInt32("ThreadID");
newThreadEntryId = reader.GetInt32("PostID");
}
else
{
newThreadId = newThreadEntryId = null;
}
}
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 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();
}
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;
}
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;
}
void SetSiteRiskModState(IDnaDataReader reader, int siteid, bool ison, char state)
{
string sql = string.Format("EXEC riskmod_setsitestate @siteid = {0}, @ison = {1}, @publishmethod = '{2}'", siteid, ison?1:0, state);
reader.ExecuteWithinATransaction(sql);
}
void ProcessEventQueue(IDnaDataReader reader)
{
reader.ExecuteWithinATransaction("exec processeventqueue");
}
void SetSiteModStatus(IDnaDataReader reader, int siteid, string modStatus)
{
int premoderation=0, unmoderated=0;
switch (modStatus.ToLower())
{
case "unmoderated": premoderation = 0; unmoderated = 1; break;
case "postmoderated": premoderation = 0; unmoderated = 0; break;
case "premoderated": premoderation = 1; unmoderated = 0; break;
default: Assert.Fail("Unknown moderation status"); break;
}
string sql = string.Format("UPDATE Sites SET premoderation={1}, unmoderated={2} WHERE siteid={0}", siteid, premoderation, unmoderated);
reader.ExecuteWithinATransaction(sql);
}
void GenerateBIEvents(IDnaDataReader reader)
{
reader.ExecuteWithinATransaction(@"
DECLARE @TopEventID INT
SELECT @TopEventID = MAX(EventID) FROM dbo.EventQueue
EXEC dbo.generatebievents @TopEventID");
}
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();
}
void ClearBIEventQueue(IDnaDataReader reader)
{
reader.ExecuteWithinATransaction("delete from bieventqueue");
}
void ModeratePost(IDnaDataReader reader, int forumid, int threadid, int postid, int modid, int status, string notes, int? referto, int? referredby)
{
string sql = string.Format(@"
EXEC moderatepost @forumid={0}, @threadid={1}, @postid={2},
@modid={3}, @status={4}, @notes='{5}', @referto={6}, @referredby={7}, @moderationstatus=NULL,
@emailtype=''",
forumid,
threadid,
postid,
modid,
status,
notes,
referto.HasValue ? referto.ToString() : "NULL",
referredby.HasValue ? referredby.ToString() : "NULL");
reader.ExecuteWithinATransaction(sql);
}
bool CheckEventIsInQueue(IDnaDataReader reader, EventTypes EventType, int ItemID, EventItemTypes ItemType, int ItemID2, EventItemTypes ItemType2, int EventUserID)
{
string sql = string.Format(@"
select 1
from eventQueue with(nolock)
where EventType={0} and ItemID={1} and ItemType={2} and ItemID2={3} and ItemType2={4} and EventUserID={5}
", (int)EventType, ItemID, (int)ItemType, ItemID2, (int)ItemType2, EventUserID);
reader.ExecuteWithinATransaction(sql);
return reader.HasRows;
}
void CheckLatestThread(IDnaDataReader reader, int threadid, int forumid, byte canRead, byte canWrite, int siteid)
{
reader.ExecuteWithinATransaction(@"SELECT top 1 * FROM Threads order by ThreadID desc");
reader.Read();
Assert.AreEqual(threadid, reader.GetInt32("threadid"));
Assert.AreEqual(forumid, reader.GetInt32("forumid"));
Assert.AreEqual(canRead, reader.GetByte("canRead"));
Assert.AreEqual(canWrite, reader.GetByte("canWrite"));
Assert.AreEqual(siteid, reader.GetInt32("siteid"));
}
void ClearEventQueue(IDnaDataReader reader)
{
reader.ExecuteWithinATransaction("delete from eventqueue");
reader.Close();
}
void CheckLatestThreadMod(IDnaDataReader reader, int forumid, int? threadid, int? postid, int? lockedby, int status, string notes, int siteid, byte isPremodPosting)
{
reader.ExecuteWithinATransaction(@"SELECT top 1 * FROM ThreadMod order by ModId desc");
reader.Read();
// Nasty tweak. With PremodPostings, when it creates a thread mod entry, it sets threadid=null and postid=0
// posttoforuminternal returns 0 for both thread id and threadentry id, so here we're treating 0 as null
threadid = NullIf(threadid, 0);
Assert.AreEqual(forumid, reader.GetInt32("forumid"));
TestNullableIntField(reader, "threadid", threadid);
TestNullableIntField(reader, "postid", postid);
TestNullableIntField(reader, "lockedby", lockedby);
Assert.AreEqual(status, reader.GetInt32("status"));
TestNullableStringField(reader, "notes", notes);
Assert.AreEqual(siteid, reader.GetInt32("siteid"));
Assert.AreEqual(isPremodPosting,reader.GetByte("isPremodPosting"));
}
int CountEventQueueEntries(IDnaDataReader reader)
{
reader.ExecuteWithinATransaction("select count(*) as C from eventqueue");
reader.Read();
int c = reader.GetInt32("C");
reader.Close();
return c;
}
int GetLatestThreadEntryId(IDnaDataReader reader)
{
reader.ExecuteWithinATransaction(@"SELECT top 1 * FROM ThreadEntries order by EntryId desc");
reader.Read();
return reader.GetInt32("EntryId");
}
void ClearRiskModThreadEntryQueue(IDnaDataReader reader)
{
reader.ExecuteWithinATransaction("delete from RiskModThreadEntryQueue");
reader.Close();
}
int GetLatestPremodPostingsModId(IDnaDataReader reader)
{
reader.ExecuteWithinATransaction(@"SELECT top 1 * FROM PremodPostings order by modid desc");
reader.Read();
if (reader.HasRows)
return reader.GetInt32("ModId");
return -1;
}
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;
}
