PageRenderTime 81ms CodeModel.GetById 35ms RepoModel.GetById 1ms app.codeStats 1ms

/Other/N2F/Src/ExternalMessaging/trunk/ExternalMessaging/Comment.cs

https://github.com/AnthonyNystrom/GenXSource
C# | 292 lines | 204 code | 75 blank | 13 comment | 24 complexity | 6cf839fba637f38f652d41c2b60c5674 MD5 | raw file
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Text;
  4. using System.Data.SqlClient;
  5. using Microsoft.SqlServer.Server;
  6. using System.Net.Mail;
  7. using System.Data;
  8. namespace ExternalMessaging
  9. {
  10. public class Comment
  11. {
  12. [Microsoft.SqlServer.Server.SqlTrigger(Name = "NewComment", Target = "Comment", Event = "FOR INSERT")]
  13. public static void NewComment()
  14. {
  15. SqlCommand command = null;
  16. DataSet ds = null;
  17. SqlDataAdapter dataAdapter = null;
  18. string TargetEmailAddress = null;
  19. string OwnerEmailAddress = null;
  20. int OwnerMemberID = -1;
  21. string subject = null;
  22. string body = null;
  23. try
  24. {
  25. SqlTriggerContext triggContext = SqlContext.TriggerContext;
  26. switch (triggContext.TriggerAction)
  27. {
  28. case TriggerAction.Insert:
  29. // Retrieve the connection that the trigger is using
  30. using (SqlConnection connection
  31. = new SqlConnection(@"context connection=true"))
  32. {
  33. connection.Open();
  34. command = new SqlCommand(@"SELECT * FROM INSERTED;",
  35. connection);
  36. dataAdapter = new SqlDataAdapter(command);
  37. ds = new DataSet();
  38. dataAdapter.Fill(ds);
  39. DataRow insertedRow = ds.Tables[0].Rows[0];
  40. int ObjectID = (int)insertedRow["ObjectID"];
  41. int MemberIDFrom = (int)insertedRow["MemberIDFrom"];
  42. ContentType commentType = (ContentType)((int)insertedRow["CommentType"]);
  43. int ThreadNo = (int)insertedRow["ThreadNo"];
  44. string Path = (string)insertedRow["Path"];
  45. // The object on which the comment was made along with the owner information
  46. // Can be a Video, Photo, Nspot, Member etc...
  47. DataSet objectDS = Utility.GetObjectByObjectIDWithJoin(ObjectID, commentType, connection);
  48. OwnerMemberID = (int)objectDS.Tables[0].Rows[0]["MemberID"];
  49. if (commentType == ContentType.Wall)
  50. {
  51. OwnerEmailAddress = (string)objectDS.Tables[0].Rows[0]["Email"];
  52. }
  53. else
  54. {
  55. OwnerEmailAddress = (string)objectDS.Tables[0].Rows[0]["MemberEmail"];
  56. }
  57. // The target members to which the notification email should be sent
  58. // The owner of the object is not included in this list.
  59. DataSet targetMembers = GetCommentTargetMembers(ObjectID, commentType, ThreadNo, connection);
  60. Member sendingMember = Member.GetMemberByMemberID(MemberIDFrom, connection);
  61. string unMergedSubject = GetSubject(Path == "/", commentType);
  62. string unMergedBody = GetBody(Path == "/", commentType);
  63. unMergedBody = Utility.PopulateLink( unMergedBody, commentType, objectDS.Tables[0].Rows[0] );
  64. // Send notification email to all members participating in the thread
  65. foreach (DataRow row in targetMembers.Tables[0].Rows)
  66. {
  67. // if the posting member is also found in target member.skip
  68. if ( (int)row["MemberID"] == MemberIDFrom )
  69. continue;
  70. TargetEmailAddress = (string)row["Email"];
  71. subject = unMergedSubject;
  72. body = unMergedBody;
  73. if (OwnerEmailAddress == TargetEmailAddress)
  74. continue;
  75. if (!Notify(row, ContentType.ThreadReply))
  76. continue;
  77. subject = MergeHelper.GenericMerge(subject, insertedRow.Table.Columns, insertedRow);
  78. body = MergeHelper.GenericMerge(body, insertedRow.Table.Columns, insertedRow);
  79. subject = MergeHelper.GenericMerge(subject, row.Table.Columns, row);
  80. body = MergeHelper.GenericMerge(body, row.Table.Columns, row);
  81. subject = MergeHelper.MergeOtherMemberInfo(subject, sendingMember);
  82. body = MergeHelper.MergeOtherMemberInfo(body, sendingMember);
  83. //body = MergeHelper.MergeBanner(body, "New " + commentType.ToString() + " comment",connection);
  84. MailHelper.SendEmail(TargetEmailAddress, subject, body);
  85. }
  86. if (MemberIDFrom != OwnerMemberID
  87. &&
  88. Notify(
  89. GetMemberSettingsByMemberID(OwnerMemberID, connection).Tables[0].Rows[0], commentType)
  90. )
  91. {
  92. subject = GetSubject(true, commentType);
  93. body = GetBody(true, commentType);
  94. subject = MergeHelper.GenericMerge(subject, insertedRow.Table.Columns, insertedRow);
  95. body = MergeHelper.GenericMerge(body, insertedRow.Table.Columns, insertedRow);
  96. subject = MergeHelper.GenericMerge(subject, objectDS.Tables[0].Columns, objectDS.Tables[0].Rows[0]);
  97. body = MergeHelper.GenericMerge(body, objectDS.Tables[0].Columns, objectDS.Tables[0].Rows[0]);
  98. subject = MergeHelper.MergeOtherMemberInfo(subject, sendingMember);
  99. body = MergeHelper.MergeOtherMemberInfo(body, sendingMember);
  100. //body = MergeHelper.MergeBanner(body, "New " + commentType.ToString() + " comment", connection);
  101. MailHelper.SendEmail(OwnerEmailAddress, subject, body);
  102. }
  103. }
  104. break;
  105. }
  106. }
  107. catch { }
  108. finally
  109. {
  110. try
  111. {
  112. if (command != null)
  113. command.Dispose();
  114. if (ds != null)
  115. ds.Dispose();
  116. if (dataAdapter != null)
  117. dataAdapter.Dispose();
  118. }
  119. catch { }
  120. }
  121. }
  122. private static string GetBody(bool rootComment,ContentType commentType)
  123. {
  124. if (rootComment)
  125. {
  126. switch (commentType)
  127. {
  128. case ContentType.Wall:
  129. return Templates.NewMemberCommentBody;
  130. case ContentType.Video:
  131. return Templates.NewVideoCommentBody;
  132. case ContentType.Blog:
  133. return Templates.NewBlogCommentBody;
  134. case ContentType.Photo:
  135. return Templates.NewPhotoCommentBody;
  136. case ContentType.AskAFriend:
  137. return Templates.NewAAFCommentBody;
  138. }
  139. }
  140. return Templates.NewThreadCommentBody;
  141. }
  142. private static string GetSubject(bool rootComment, ContentType commentType)
  143. {
  144. if (rootComment)
  145. {
  146. switch (commentType)
  147. {
  148. case ContentType.Wall:
  149. return Templates.NewMemberCommentSubject;
  150. case ContentType.Video:
  151. return Templates.NewVideoCommentSubject;
  152. case ContentType.Blog:
  153. return Templates.NewBlogCommentSubject;
  154. case ContentType.Photo:
  155. return Templates.NewPhotoCommentSubject;
  156. case ContentType.AskAFriend:
  157. return Templates.NewAAFCommentSubject;
  158. }
  159. }
  160. return Templates.NewThreadCommentSubject;
  161. }
  162. private static bool Notify(DataRow row, ContentType type)
  163. {
  164. switch (type)
  165. {
  166. case ContentType.Wall:
  167. return (bool)row["NotifyNewProfileComment"];
  168. case ContentType.Video:
  169. return (bool)row["NotifyNewVideoComment"];
  170. case ContentType.ThreadReply:
  171. return (bool)row["NotifyOnThreadReply"];
  172. case ContentType.Blog:
  173. return true;
  174. case ContentType.Photo:
  175. return (bool)row["NotifyNewPhotoComment"];
  176. case ContentType.AskAFriend:
  177. return (bool)row["NotifyOnAAFComment"];
  178. }
  179. return false;
  180. }
  181. /// <summary>
  182. /// Get Information on the members that are to be notified
  183. /// </summary>
  184. /// <returns></returns>
  185. private static DataSet GetCommentTargetMembers(int ObjectID,ContentType type,int ThreadNo,SqlConnection conn)
  186. {
  187. SqlCommand command = new SqlCommand("HG_GetCommentTargetMembersForTrigger", conn);
  188. SqlParameter param = new SqlParameter("@ObjectID", ObjectID);
  189. param.DbType = DbType.Int32;
  190. command.Parameters.Add(param);
  191. param = new SqlParameter("@CommentType", (int)type);
  192. param.DbType = DbType.Int32;
  193. command.Parameters.Add(param);
  194. param = new SqlParameter("@ThreadNo", ThreadNo);
  195. param.DbType = DbType.Int32;
  196. command.Parameters.Add(param);
  197. command.CommandType = CommandType.StoredProcedure;
  198. SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
  199. DataSet ds = new DataSet();
  200. dataAdapter.Fill(ds);
  201. return ds;
  202. }
  203. private static DataSet GetMemberSettingsByMemberID(int MemberID, SqlConnection conn)
  204. {
  205. SqlCommand command = null;
  206. command = new SqlCommand("AG_GetMemberSettingsByMemberID", conn);
  207. SqlParameter param = new SqlParameter("@MemberID", MemberID);
  208. param.DbType = DbType.Int32;
  209. command.Parameters.Add(param);
  210. command.CommandType = CommandType.StoredProcedure;
  211. SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
  212. DataSet ds = new DataSet();
  213. dataAdapter.Fill(ds);
  214. return ds;
  215. }
  216. }
  217. }