public static string MergeOtherMemberInfo(string inputString, Member memberInfo)
{
StringBuilder stringBuilder = new StringBuilder(inputString);
stringBuilder.Replace("<#OtherDOB#>", memberInfo.DOB.ToShortDateString());
stringBuilder.Replace("<#OtherEmail#>", memberInfo.Email);
stringBuilder.Replace("<#OtherFirstName#>", memberInfo.FirstName);
stringBuilder.Replace("<#OtherLastName#>", memberInfo.LastName);
stringBuilder.Replace("<#OtherNickName#>", memberInfo.NickName);
stringBuilder.Replace("<#OtherZipPostcode#>", memberInfo.ZipPostcode);
return stringBuilder.ToString();
}
public static string MergeMemberInfo(string inputString, Member memberInfo)
{
StringBuilder stringBuilder = new StringBuilder(inputString);
stringBuilder.Replace("<#DOB#>", memberInfo.DOB.ToShortDateString());
stringBuilder.Replace("<#Email#>", memberInfo.Email);
stringBuilder.Replace("<#FirstName#>", memberInfo.FirstName);
stringBuilder.Replace("<#LastName#>", memberInfo.LastName);
stringBuilder.Replace("<#NickName#>", memberInfo.NickName);
stringBuilder.Replace("<#ZipPostcode#>", memberInfo.ZipPostcode);
stringBuilder.Replace("<#Password#>", memberInfo.Password);
stringBuilder.Replace("<#WebMemberID#>", memberInfo.WebMemberID);
return stringBuilder.ToString();
}
private static string PopulateLink(string inputString, ContentType contentType, DataRow row,Member ownerMember)
{
string QueryString = string.Empty;
switch (contentType)
{
case ContentType.Video:
inputString = inputString.Replace("<#PageLink#>", "video/" + Utility.FormatStringForURL((string)row["Title"]) + "/" + (string)row["WebVideoID"]);
//QueryString = "v=" + (string)row["WebVideoID"];
break;
case ContentType.Blog:
inputString = inputString.Replace("<#PageLink#>", "Blog.aspx");
QueryString = "?m=" + ownerMember.WebMemberID + "&b=" + (string)row["WebBlogEntryID"];
break;
}
return inputString.Replace("<#QueryString#>", QueryString);
}
/// <summary>
/// Takes an prepopulated IDataReader and creates an array of Members
/// </summary>
public static List<Member> PopulateObject(IDataReader dr)
{
List<Member> arr = new List<Member>();
Member obj;
while (dr.Read())
{
obj = new Member();
obj._memberID = (int)dr["MemberID"];
obj._webMemberID = (string)dr["WebMemberID"];
obj._adminStatusID = (int)dr["AdminStatusID"];
obj._nickName = (string)dr["NickName"];
obj._channelID = (int)dr["ChannelID"];
obj._password = (string)dr["Password"];
obj._email = (string)dr["Email"];
obj._gender = (int)dr["Gender"];
obj._firstName = (string)dr["FirstName"];
obj._lastName = (string)dr["LastName"];
obj._profilePhotoResourceFileID = (int)dr["ProfilePhotoResourceFileID"];
obj._dOB = (DateTime)dr["DOB"];
obj._iSOCountry = (string)dr["ISOCountry"];
obj._zipPostcode = (string)dr["ZipPostcode"];
obj._createdDT = (DateTime)dr["CreatedDT"];
arr.Add(obj);
}
dr.Close();
return arr;
}
public static void NewMemberInvite()
{
SqlCommand command = null;
DataSet ds = null;
SqlDataAdapter dataAdapter = null;
try
{
SqlTriggerContext triggContext = SqlContext.TriggerContext;
switch (triggContext.TriggerAction)
{
case TriggerAction.Insert:
// Retrieve the connection that the trigger is using
using (SqlConnection connection
= new SqlConnection(@"context connection=true"))
{
connection.Open();
command = new SqlCommand(@"SELECT * FROM INSERTED;",
connection);
dataAdapter = new SqlDataAdapter(command);
ds = new DataSet();
dataAdapter.Fill(ds);
DataTable table = ds.Tables[0];
DataRow row = table.Rows[0];
DataColumnCollection columns = table.Columns;
int MemberID = (int)row["MemberID"];
string EmailAddress = (string)row["EmailAddress"];
Member sendingMember = null;
if (MemberID != 0)
{
sendingMember = Member.GetMemberByMemberID(MemberID, connection);
}
else
{
sendingMember = new Member();
sendingMember.FirstName = "Your friend";
}
string TargetEmailAddress = EmailAddress;
string subject = Templates.MemberInviteSubject;
string body = Templates.MemberInviteBody;
subject = MergeHelper.GenericMerge(subject, columns, row);
body = MergeHelper.GenericMerge(body, columns, row);
subject = MergeHelper.MergeMemberInfo(subject, sendingMember);
body = MergeHelper.MergeMemberInfo(body, sendingMember);
MailHelper.SendEmail(TargetEmailAddress, subject, body);
}
break;
}
}
catch { }
finally
{
try
{
if (command != null)
command.Dispose();
if (ds != null)
ds.Dispose();
if (dataAdapter != null)
dataAdapter.Dispose();
}
catch { }
}
}
private static string InsertInviteClick(DataRow updatedRow,Member sendingMember,string forwardURL,SqlConnection conn)
{
String insertSQL = @"INSERT INTO InviteClick
(
WebInviteClickID,
ForwardURL,
ContactImportID
)
VALUES
(
'{0}',
'{1}',
{2}
)";
StringBuilder sbinsertSQL = new StringBuilder();
object[] param = new object[3];
param[0] = Utility.NewWebID();
param[1] = forwardURL;
param[2] = (int)updatedRow["ContactImportID"];
sbinsertSQL.AppendFormat(insertSQL,param);
SqlCommand insertCommand = new SqlCommand(sbinsertSQL.ToString(),conn);
insertCommand.ExecuteNonQuery();
return (string)param[0];
}
public static void NewComment()
{
SqlCommand command = null;
DataSet ds = null;
SqlDataAdapter dataAdapter = null;
string TargetEmailAddress = null;
string OwnerEmailAddress = null;
int OwnerMemberID = -1;
string subject = null;
string body = null;
try
{
SqlTriggerContext triggContext = SqlContext.TriggerContext;
switch (triggContext.TriggerAction)
{
case TriggerAction.Insert:
// Retrieve the connection that the trigger is using
using (SqlConnection connection
= new SqlConnection(@"context connection=true"))
{
connection.Open();
command = new SqlCommand(@"SELECT * FROM INSERTED;",
connection);
dataAdapter = new SqlDataAdapter(command);
ds = new DataSet();
dataAdapter.Fill(ds);
DataRow insertedRow = ds.Tables[0].Rows[0];
int ObjectID = (int)insertedRow["ObjectID"];
int MemberIDFrom = (int)insertedRow["MemberIDFrom"];
ContentType commentType = (ContentType)((int)insertedRow["CommentType"]);
int ThreadNo = (int)insertedRow["ThreadNo"];
string Path = (string)insertedRow["Path"];
// The object on which the comment was made along with the owner information
// Can be a Video, Photo, Nspot, Member etc...
DataSet objectDS = Utility.GetObjectByObjectIDWithJoin(ObjectID, commentType, connection);
OwnerMemberID = (int)objectDS.Tables[0].Rows[0]["MemberID"];
if (commentType == ContentType.Wall)
{
OwnerEmailAddress = (string)objectDS.Tables[0].Rows[0]["Email"];
}
else
{
OwnerEmailAddress = (string)objectDS.Tables[0].Rows[0]["MemberEmail"];
}
// The target members to which the notification email should be sent
// The owner of the object is not included in this list.
DataSet targetMembers = GetCommentTargetMembers(ObjectID, commentType, ThreadNo, connection);
Member sendingMember = Member.GetMemberByMemberID(MemberIDFrom, connection);
string unMergedSubject = GetSubject(Path == "/", commentType);
string unMergedBody = GetBody(Path == "/", commentType);
unMergedBody = Utility.PopulateLink(unMergedBody, commentType, objectDS.Tables[0].Rows[0]);
// Send notification email to all members participating in the thread
foreach (DataRow row in targetMembers.Tables[0].Rows)
{
// if the posting member is also found in target member.skip
if ((int)row["MemberID"] == MemberIDFrom)
{
continue;
}
TargetEmailAddress = (string)row["Email"];
subject = unMergedSubject;
body = unMergedBody;
if (OwnerEmailAddress == TargetEmailAddress)
{
continue;
}
if (!Notify(row, ContentType.ThreadReply))
{
continue;
}
subject = MergeHelper.GenericMerge(subject, insertedRow.Table.Columns, insertedRow);
body = MergeHelper.GenericMerge(body, insertedRow.Table.Columns, insertedRow);
subject = MergeHelper.GenericMerge(subject, row.Table.Columns, row);
body = MergeHelper.GenericMerge(body, row.Table.Columns, row);
subject = MergeHelper.MergeOtherMemberInfo(subject, sendingMember);
body = MergeHelper.MergeOtherMemberInfo(body, sendingMember);
//body = MergeHelper.MergeBanner(body, "New " + commentType.ToString() + " comment",connection);
MailHelper.SendEmail(TargetEmailAddress, subject, body);
}
if (MemberIDFrom != OwnerMemberID
&&
Notify(
GetMemberSettingsByMemberID(OwnerMemberID, connection).Tables[0].Rows[0], commentType)
)
{
subject = GetSubject(true, commentType);
body = GetBody(true, commentType);
subject = MergeHelper.GenericMerge(subject, insertedRow.Table.Columns, insertedRow);
body = MergeHelper.GenericMerge(body, insertedRow.Table.Columns, insertedRow);
subject = MergeHelper.GenericMerge(subject, objectDS.Tables[0].Columns, objectDS.Tables[0].Rows[0]);
body = MergeHelper.GenericMerge(body, objectDS.Tables[0].Columns, objectDS.Tables[0].Rows[0]);
subject = MergeHelper.MergeOtherMemberInfo(subject, sendingMember);
body = MergeHelper.MergeOtherMemberInfo(body, sendingMember);
//body = MergeHelper.MergeBanner(body, "New " + commentType.ToString() + " comment", connection);
MailHelper.SendEmail(OwnerEmailAddress, subject, body);
}
}
break;
}
}
catch { }
finally
{
try
{
if (command != null)
{
command.Dispose();
}
if (ds != null)
{
ds.Dispose();
}
if (dataAdapter != null)
{
dataAdapter.Dispose();
}
}
catch { }
}
}
public static void NewMessage()
{
SqlCommand command = null;
DataSet ds = null;
SqlDataAdapter dataAdapter = null;
try
{
SqlTriggerContext triggContext = SqlContext.TriggerContext;
switch (triggContext.TriggerAction)
{
case TriggerAction.Insert:
// Retrieve the connection that the trigger is using
using (SqlConnection connection
= new SqlConnection(@"context connection=true"))
{
connection.Open();
command = new SqlCommand(@"SELECT * FROM INSERTED;",
connection);
dataAdapter = new SqlDataAdapter(command);
ds = new DataSet();
dataAdapter.Fill(ds);
//Populate data from the inserted row
DataRow insertedRow = ds.Tables[0].Rows[0];
DataColumnCollection insertedColumns = ds.Tables[0].Columns;
int MemberIDTo = (int)insertedRow["MemberIDTo"];
int MemberIDFrom = (int)insertedRow["MemberIDFrom"];
string ExternalEmailTo = (string)insertedRow["ExternalEmailTo"];
if (MemberIDTo != -1)
{
MemberSettings ms = Member.GetMemberSettingsByMemberID(MemberIDTo, connection);
// In case we don't find any settings
if (ms == null)
{
return;
}
// If the user doesn't want to be notified return
if (!ms.NotifyOnNewMessage)
{
return;
}
}
Member targetMember = null;
if (MemberIDTo != -1)
{
targetMember = Member.GetMemberByMemberID(MemberIDTo, connection);
}
else
{
targetMember = new Member();
targetMember.Email = ExternalEmailTo;
}
Member sendingMember = Member.GetMemberByMemberID(MemberIDFrom, connection);
string TargetEmailAddress = targetMember.Email;
string subject = string.Empty;
string body = string.Empty;
if (MemberIDTo == -1)
{
subject = Templates.NewExternalMessageSubject;
body = Templates.NewExternalMessageBody;
}
else
{
subject = Templates.NewMessageSubject;
body = Templates.NewMessageBody;
}
subject = MergeHelper.GenericMerge(subject, insertedColumns, insertedRow);
body = MergeHelper.GenericMerge(body, insertedColumns, insertedRow);
subject = MergeHelper.MergeMemberInfo(subject, targetMember);
body = MergeHelper.MergeMemberInfo(body, targetMember);
subject = MergeHelper.MergeOtherMemberInfo(subject, sendingMember);
body = MergeHelper.MergeOtherMemberInfo(body, sendingMember);
MailHelper.SendEmail(TargetEmailAddress, subject, body);
}
break;
}
}
catch { }
finally
{
try
{
if (command != null)
{
command.Dispose();
}
if (ds != null)
{
ds.Dispose();
}
if (dataAdapter != null)
{
dataAdapter.Dispose();
}
}
catch { }
}
}
public static void NewMessage()
{
SqlCommand command = null;
DataSet ds = null;
SqlDataAdapter dataAdapter = null;
try
{
SqlTriggerContext triggContext = SqlContext.TriggerContext;
switch (triggContext.TriggerAction)
{
case TriggerAction.Insert:
// Retrieve the connection that the trigger is using
using (SqlConnection connection
= new SqlConnection(@"context connection=true"))
{
connection.Open();
command = new SqlCommand(@"SELECT * FROM INSERTED;",
connection);
dataAdapter = new SqlDataAdapter(command);
ds = new DataSet();
dataAdapter.Fill(ds);
//Populate data from the inserted row
DataRow insertedRow = ds.Tables[0].Rows[0];
DataColumnCollection insertedColumns = ds.Tables[0].Columns;
int MemberIDTo = (int)insertedRow["MemberIDTo"];
int MemberIDFrom = (int)insertedRow["MemberIDFrom"];
string ExternalEmailTo = (string)insertedRow["ExternalEmailTo"];
if (MemberIDTo != -1)
{
MemberSettings ms = Member.GetMemberSettingsByMemberID(MemberIDTo, connection);
// In case we don't find any settings
if (ms == null)
return;
// If the user doesn't want to be notified return
if (!ms.NotifyOnNewMessage)
return;
}
Member targetMember = null;
if (MemberIDTo != -1)
{
targetMember = Member.GetMemberByMemberID(MemberIDTo, connection);
}
else
{
targetMember = new Member();
targetMember.Email = ExternalEmailTo;
}
Member sendingMember = Member.GetMemberByMemberID(MemberIDFrom, connection);
string TargetEmailAddress = targetMember.Email;
string subject = string.Empty;
string body = string.Empty;
if (MemberIDTo == -1)
{
subject = Templates.NewExternalMessageSubject;
body = Templates.NewExternalMessageBody;
}
else
{
subject = Templates.NewMessageSubject;
body = Templates.NewMessageBody;
}
subject = MergeHelper.GenericMerge(subject, insertedColumns, insertedRow);
body = MergeHelper.GenericMerge(body, insertedColumns, insertedRow);
subject = MergeHelper.MergeMemberInfo(subject, targetMember);
body = MergeHelper.MergeMemberInfo(body, targetMember);
subject = MergeHelper.MergeOtherMemberInfo(subject, sendingMember);
body = MergeHelper.MergeOtherMemberInfo(body, sendingMember);
MailHelper.SendEmail(TargetEmailAddress, subject, body);
}
break;
}
}
catch { }
finally
{
try
{
if (command != null)
command.Dispose();
if (ds != null)
ds.Dispose();
if (dataAdapter != null)
dataAdapter.Dispose();
}
catch { }
}
}
