USE [SystemTracker]
GO
/****** Object: StoredProcedure [dbo].[PR_SEND_EMAIL] Script Date: 04-10-2021 21:13:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--exec [PR_Send_Email] 'Gauri.Shukla@mind-infotech.com','','','','','','Gauri.Shukla@mind-infotech.com','','','UserQueries','gauri'
ALTER proc [dbo].[PR_SEND_EMAIL](
@p_MailID_To varchar(1000) ,
@p_Attachment nvarchar(800)='' ,
@p_Bcc_ID varchar(500)='' , -- this parameter is not being used.
@p_CC_ID varchar(500)='' ,
@p_ReplyTo_ID varchar(1000)='' ,
@p_BodyText nvarchar(max)='' ,
@p_From_ID varchar(50)='' ,
@p_SourcePCName varchar(50)='' ,
@p_Subject nvarchar(200)='',
@p_EmailType varchar(500),
@p_UserName varchar(50) = 'User' ,
@p_OTP int=0 ,
@p_Domain varchar(200) = '' ,
@p_UserPassword varchar(1000) ='',
@P_QUERY NVARCHAR(MAX)=''
)
AS
BEGIN
Declare @BCC_EMAILID varchar(500) = ''
DECLARE @p_NewBodyText NVARCHAR(max)
SELECT @BCC_EMAILID = [VALUE] FROM ST_FUNCTIONAL_CTL where F_Class = 'BCC_EMAILS'
SELECT @p_From_ID = [VALUE] FROM ST_FUNCTIONAL_CTL WHERE F_Class = 'ADMIN_EMAIL'
IF(@p_EmailType='OTPSendToUser')
BEGIN
EXEC PR_Get_Email_Templete @p_EmailType, @p_NewBodyText output
set @p_NewBodyText = replace(@p_NewBodyText, '@@UserName@@', @p_UserName)
set @p_NewBodyText = replace(@p_NewBodyText, '@@OTP@@', @p_OTP)
select @p_Subject=MailSubject from ST_MailsHTML where MailType='OTPSendToUser'
INSERT INTO [ST_MMS_Mailer](MailID_To,Attachment,Bcc_ID,CC_ID,ReplyTo_ID,BodyText,Ent_Dt,From_ID,From_Name,
SourcePCName,BodyType,LastExecutionCode,Priority,RetryAttempts,LastTryDate,Subject,
SystemName,Error_Desc)
VALUES
(@p_MailID_To,@p_Attachment,@BCC_EMAILID,@p_CC_ID,@p_ReplyTo_ID,@p_NewBodyText,getdate(),@p_From_ID,'',@p_SourcePCName,'HTML',
1,2,2,getdate(),@p_Subject,'SystemTracker','N/A')
END
else IF(@p_EmailType='PasswordSendToUser')
BEGIN
EXEC PR_Get_Email_Templete @p_EmailType, @p_NewBodyText output
set @p_NewBodyText = replace(@p_NewBodyText, '@@UserName@@', @p_UserName)
set @p_NewBodyText = replace(@p_NewBodyText, '@@UserPassword@@', @p_Subject)
select @p_Subject=MailSubject from ST_MailsHTML where MailType='PasswordSendToUser'
INSERT INTO [ST_MMS_Mailer](MailID_To,Attachment,Bcc_ID,CC_ID,ReplyTo_ID,BodyText,Ent_Dt,From_ID,From_Name,
SourcePCName,BodyType,LastExecutionCode,Priority,RetryAttempts,LastTryDate,Subject,
SystemName,Error_Desc)
VALUES
(@p_MailID_To,@p_Attachment,@BCC_EMAILID,@p_CC_ID,@p_ReplyTo_ID,@p_NewBodyText,getdate(),@p_From_ID,'',@p_SourcePCName,'HTML',
1,2,2,getdate(),@p_Subject,'SystemTracker','N/A')
END
else IF(@p_EmailType='UserQueries')
BEGIN
set @p_SourcePCName = 'p_SourcePCName'
EXEC PR_Get_Email_Templete @p_EmailType, @p_NewBodyText output
set @p_NewBodyText = replace(@p_NewBodyText, '@@EmployeeDetail@@', @p_BodyText)
set @p_NewBodyText = replace(@p_NewBodyText, '@@ProcessName@@', @P_QUERY)
select @p_Subject=MailSubject from ST_MailsHTML where MailType='UserQueries'
INSERT INTO [ST_MMS_Mailer](MailID_To,Attachment,Bcc_ID,CC_ID,ReplyTo_ID,BodyText,Ent_Dt,From_ID,From_Name,
SourcePCName,BodyType,LastExecutionCode,Priority,RetryAttempts,LastTryDate,Subject,
SystemName,Error_Desc)
VALUES
(@p_MailID_To,@p_Attachment,@BCC_EMAILID,@p_CC_ID,@p_ReplyTo_ID,@p_NewBodyText,getdate(),@p_From_ID,'',@p_SourcePCName,'HTML',
1,2,2,getdate(),@p_Subject,'SystemTracker','N/A')
END
else IF(isnull(@p_BodyText, '') <> '')
BEGIN
INSERT INTO [ST_MMS_Mailer](MailID_To,Attachment,Bcc_ID,CC_ID,ReplyTo_ID,BodyText,Ent_Dt,From_ID,From_Name,
SourcePCName,BodyType,LastExecutionCode,Priority,RetryAttempts,LastTryDate,Subject,
SystemName,Error_Desc)
VALUES
(@p_MailID_To,@p_Attachment,@BCC_EMAILID,@p_CC_ID,@p_ReplyTo_ID,@p_BodyText,getdate(),@p_From_ID,'',@p_SourcePCName,'HTML',
1,2,2,getdate(),@p_Subject,'SystemTracker','N/A')
END
END
----------------------------------------------------------------------------------------------------------------------------
Table --ST_MMS_MAILER
Mail_ID MailID_To Attachment Bcc_ID CC_ID ReplyTo_ID BodyText Charset Ent_Dt From_ID From_Name SourcePCName BodyType LastExecutionCode Priority RetryAttempts LastTryDate Subject SystemName Error_Desc
130 VIJAY.KUMAR3@MIND-INFOTECH.COM Kiran.Bhatnagar@mind-infotech.com <!DOCTYPE html><html lang="en"><head><meta charset="utf-8"><title>Mailer</title></head><body style="margin:0 auto;background:#ffffff;"><div style="background:#ffffff;padding:25px 0"><table style="font-family:Calibri, arial;font-size:12px;color:#666;max-width:610px;color:#414042"bgcolor="#fff" border="0" align="center" width="100%" cellpadding="0" cellspacing="0"><tr> <td style="background:#db2128; font-family:Calibri, arial; font-weight:bold;color:#fff;font-size:28px;padding:12px 12px 0" valign="top">My Activity</td><td style="background:#db2128;color:#fff;padding:12px" align="right">13-Oct-2020<br></td></tr><tr><td colspan="2" style="padding:10px 12px;font-weight:300"> <strong>Dear Mr./Ms vijaykumar3</strong>,<br><br>One Time Password (OTP) for My Activity(Forget Password) is 250709.it is valid for 20 minute(s).<br>This is an auto generated email through <strong>My Activity</strong>. <br></td></tr><tr><td colspan="2" style="padding:0 0 12px 12px;font-weight:300"><strong>Best Regards,</strong><br>My Activity Team</td></tr><tr> <td colspan="2" style="padding:12px;font-weight:300;background:#e7e8e9;color:#6d6e71;font-size:11px"><strong>Disclaimer:</strong><br>This communication is System Generated. Please do not reply to this Email. If you are not the correct recipient for this notification, please contact the My Activity Administrator.©MIND All Rights Reserved. </td></tr></table></div></body></html> NULL 2020-10-13 16:32:38.850 ProductivityTracker@mind-infotech.com p_SourcePCName HTML 1 2 10 2020-10-13 16:32:38.850 My Activity OTP SystemTracker N/A
587 ProductivityTracker@mind-infotech.com Kiran.Bhatnagar@mind-infotech.com <!DOCTYPE html><html lang="en"><head><meta charset="utf-8"><title>Mailer</title></head><body style="margin:0 auto;background:#ffffff;"><div style="background:#ffffff;padding:25px 0"><table style="font-family:Calibri, arial;font-size:12px;color:#666;max-width:610px;color:#414042"bgcolor="#fff" border="0" align="center" width="100%" cellpadding="0" cellspacing="0"><tr> <td style="background:#db2128; font-family:Calibri, arial; font-weight:bold;color:#fff;font-size:28px;padding:12px 12px 0" valign="top">My Activity</td><td style="background:#db2128;color:#fff;padding:12px" align="right">04-Oct-2021<br></td></tr><tr><td colspan="2" style="padding:10px 12px;font-weight:300"> <strong>Dear Team</strong>,<br><br>
A new request is recevied to add below text in Productive List from Shivika Agarwal (SHIVIKA.AGARWAL@MIND-INFOTECH.COM) - Reporting Manager :Gargi Sharma .<br> span class= ellipsis title= ログイン - board - Google Chrome ログイン - board - Google Ch… span span class= ellipsis title= ダッシュボード - board - Google Chrome ダッシュボード - board - Google… span - Snipping Tool span class= ellipsis title= 案件一覧 - board - Google Chrome 案件一覧 - board - Google Ch… span span class= ellipsis title= List of projects --board - Google Chrome List of projects --board… span span class= ellipsis title= Lightning Experience - Google Chrome Lightning Experience - G… span <br>This is an auto generated email through <strong>My Activity</strong>. <br></td></tr><tr><td colspan="2" style="padding:0 0 12px 12px;font-weight:300"><strong>Best Regards,</strong><br>My Activity Team</td></tr><tr> <td colspan="2" style="padding:12px;font-weight:300;background:#e7e8e9;color:#6d6e71;font-size:11px"><strong>Disclaimer:</strong><br>This communication is System Generated. Please do not reply to this Email. If you are not the correct recipient for this notification, please contact the My Activity Administrator.©MIND All Rights Reserved. </td></tr></table></div></body></html> NULL 2021-10-04 15:59:59.860 ProductivityTracker@mind-infotech.com p_SourcePCName HTML 0 2 2 2021-10-04 16:19:28.620 User Request SystemTracker Successful
588 Gauri.Shukla@mind-infotech.com Kiran.Bhatnagar@mind-infotech.com <!DOCTYPE html><html lang="en"><head><meta charset="utf-8"><title>Mailer</title></head><body style="margin:0 auto;background:#ffffff;"><div style="background:#ffffff;padding:25px 0"><table style="font-family:Calibri, arial;font-size:12px;color:#666;max-width:610px;color:#414042"bgcolor="#fff" border="0" align="center" width="100%" cellpadding="0" cellspacing="0"><tr> <td style="background:#db2128; font-family:Calibri, arial; font-weight:bold;color:#fff;font-size:28px;padding:12px 12px 0" valign="top">My Activity</td><td style="background:#db2128;color:#fff;padding:12px" align="right">05-Oct-2021<br></td></tr><tr><td colspan="2" style="padding:10px 12px;font-weight:300"> <strong>Dear Team</strong>,<br><br>
A new request is recevied to add below text in Productive List from .<br> <br>This is an auto generated email through <strong>My Activity</strong>. <br></td></tr><tr><td colspan="2" style="padding:0 0 12px 12px;font-weight:300"><strong>Best Regards,</strong><br>My Activity Team</td></tr><tr> <td colspan="2" style="padding:12px;font-weight:300;background:#e7e8e9;color:#6d6e71;font-size:11px"><strong>Disclaimer:</strong><br>This communication is System Generated. Please do not reply to this Email. If you are not the correct recipient for this notification, please contact the My Activity Administrator.©MIND All Rights Reserved. </td></tr></table></div></body></html> NULL 2021-10-05 08:37:35.087 ProductivityTracker@mind-infotech.com p_SourcePCName HTML 1 2 2 2021-10-05 08:37:35.087 User Request SystemTracker N/A
584 ProductivityTracker@mind-infotech.com Kiran.Bhatnagar@mind-infotech.com <!DOCTYPE html><html lang="en"><head><meta charset="utf-8"><title>Mailer</title></head><body style="margin:0 auto;background:#ffffff;"><div style="background:#ffffff;padding:25px 0"><table style="font-family:Calibri, arial;font-size:12px;color:#666;max-width:610px;color:#414042"bgcolor="#fff" border="0" align="center" width="100%" cellpadding="0" cellspacing="0"><tr> <td style="background:#db2128; font-family:Calibri, arial; font-weight:bold;color:#fff;font-size:28px;padding:12px 12px 0" valign="top">My Activity</td><td style="background:#db2128;color:#fff;padding:12px" align="right">30-Sep-2021<br></td></tr><tr><td colspan="2" style="padding:10px 12px;font-weight:300"> <strong>Dear Team</strong>,<br><br>
A new request is recevied to add below text in Productive List from AnuragAgarwal (Anurag.A@mind-infotech.com) - Reporting Manager :DeepaliDhar .<br>Key User - Google Chrome span class= ellipsis title= Strategical Reports - Google Chrome Strategical Reports - Go… span span class= ellipsis title= Plant Overview - Google Chrome Plant Overview - Google … span Asset - Google Chrome Admin - Google Chrome Volumes - Google Chrome Plant - Google Chrome <br>This is an auto generated email through <strong>My Activity</strong>. <br></td></tr><tr><td colspan="2" style="padding:0 0 12px 12px;font-weight:300"><strong>Best Regards,</strong><br>My Activity Team</td></tr><tr> <td colspan="2" style="padding:12px;font-weight:300;background:#e7e8e9;color:#6d6e71;font-size:11px"><strong>Disclaimer:</strong><br>This communication is System Generated. Please do not reply to this Email. If you are not the correct recipient for this notification, please contact the My Activity Administrator.©MIND All Rights Reserved. </td></tr></table></div></body></html> NULL 2021-09-30 15:47:24.480 ProductivityTracker@mind-infotech.com p_SourcePCName HTML 0 2 2 2021-09-30 16:07:40.857 User Request SystemTracker Successful
131 SANDEEP.KUMAR@MIND-INFOTECH.COM Kiran.Bhatnagar@mind-infotech.com <!DOCTYPE html><html lang="en"><head><meta charset="utf-8"><title>Mailer</title></head><body style="margin:0 auto;background:#ffffff;"><div style="background:#ffffff;padding:25px 0"><table style="font-family:Calibri, arial;font-size:12px;color:#666;max-width:610px;color:#414042"bgcolor="#fff" border="0" align="center" width="100%" cellpadding="0" cellspacing="0"><tr> <td style="background:#db2128; font-family:Calibri, arial; font-weight:bold;color:#fff;font-size:28px;padding:12px 12px 0" valign="top">My Activity</td><td style="background:#db2128;color:#fff;padding:12px" align="right">14-Oct-2020<br></td></tr><tr><td colspan="2" style="padding:10px 12px;font-weight:300"> <strong>Dear Team</strong>,<br><br>
A new request is recevied to add below text in Productive List from SandeepKumar (SANDEEP.KUMAR@MIND-INFOTECH.COM) - Reporting Manager :KiranBhatnagar .<br>3171 <br>This is an auto generated email through <strong>My Activity</strong>. <br></td></tr><tr><td colspan="2" style="padding:0 0 12px 12px;font-weight:300"><strong>Best Regards,</strong><br>My Activity Team</td></tr><tr> <td colspan="2" style="padding:12px;font-weight:300;background:#e7e8e9;color:#6d6e71;font-size:11px"><strong>Disclaimer:</strong><br>This communication is System Generated. Please do not reply to this Email. If you are not the correct recipient for this notification, please contact the My Activity Administrator.©MIND All Rights Reserved. </td></tr></table></div></body></html> NULL 2020-10-14 14:45:14.010 ProductivityTracker@mind-infotech.com p_SourcePCName HTML 1 2 10 2020-10-14 14:45:14.010 User Request SystemTracker N/A
586 ProductivityTracker@mind-infotech.com Kiran.Bhatnagar@mind-infotech.com <!DOCTYPE html><html lang="en"><head><meta charset="utf-8"><title>Mailer</title></head><body style="margin:0 auto;background:#ffffff;"><div style="background:#ffffff;padding:25px 0"><table style="font-family:Calibri, arial;font-size:12px;color:#666;max-width:610px;color:#414042"bgcolor="#fff" border="0" align="center" width="100%" cellpadding="0" cellspacing="0"><tr> <td style="background:#db2128; font-family:Calibri, arial; font-weight:bold;color:#fff;font-size:28px;padding:12px 12px 0" valign="top">My Activity</td><td style="background:#db2128;color:#fff;padding:12px" align="right">01-Oct-2021<br></td></tr><tr><td colspan="2" style="padding:10px 12px;font-weight:300"> <strong>Dear Team</strong>,<br><br>
A new request is recevied to add below text in Productive List from AmiteshYadav (amitesh.yadav@mind-infotech.com) - Reporting Manager :VishalAgarwal .<br>Settings span class= ellipsis title= MRB Log in to the site - Google Chrome MRB Log in to the site … span span class= ellipsis title= MRB Administration Search - Google Chrome MRB Administration Sea… span <br>This is an auto generated email through <strong>My Activity</strong>. <br></td></tr><tr><td colspan="2" style="padding:0 0 12px 12px;font-weight:300"><strong>Best Regards,</strong><br>My Activity Team</td></tr><tr> <td colspan="2" style="padding:12px;font-weight:300;background:#e7e8e9;color:#6d6e71;font-size:11px"><strong>Disclaimer:</strong><br>This communication is System Generated. Please do not reply to this Email. If you are not the correct recipient for this notification, please contact the My Activity Administrator.©MIND All Rights Reserved. </td></tr></table></div></body></html> NULL 2021-10-01 14:07:27.553 ProductivityTracker@mind-infotech.com p_SourcePCName HTML 0 2 2 2021-10-01 14:28:00.403 User Request SystemTracker Successful
585 ProductivityTracker@mind-infotech.com Kiran.Bhatnagar@mind-infotech.com <!DOCTYPE html><html lang="en"><head><meta charset="utf-8"><title>Mailer</title></head><body style="margin:0 auto;background:#ffffff;"><div style="background:#ffffff;padding:25px 0"><table style="font-family:Calibri, arial;font-size:12px;color:#666;max-width:610px;color:#414042"bgcolor="#fff" border="0" align="center" width="100%" cellpadding="0" cellspacing="0"><tr> <td style="background:#db2128; font-family:Calibri, arial; font-weight:bold;color:#fff;font-size:28px;padding:12px 12px 0" valign="top">My Activity</td><td style="background:#db2128;color:#fff;padding:12px" align="right">01-Oct-2021<br></td></tr><tr><td colspan="2" style="padding:10px 12px;font-weight:300"> <strong>Dear Team</strong>,<br><br>
A new request is recevied to add below text in Productive List from Rashi Bhargava (RASHI.BHARGAVA@MIND-INFOTECH.COM) - Reporting Manager :KopalRajpoot .<br> span class= ellipsis title= Price Movement Sheet - Google Chrome Price Movement Sheet - G… span <br>This is an auto generated email through <strong>My Activity</strong>. <br></td></tr><tr><td colspan="2" style="padding:0 0 12px 12px;font-weight:300"><strong>Best Regards,</strong><br>My Activity Team</td></tr><tr> <td colspan="2" style="padding:12px;font-weight:300;background:#e7e8e9;color:#6d6e71;font-size:11px"><strong>Disclaimer:</strong><br>This communication is System Generated. Please do not reply to this Email. If you are not the correct recipient for this notification, please contact the My Activity Administrator.©MIND All Rights Reserved. </td></tr></table></div></body></html> NULL 2021-10-01 09:09:13.420 ProductivityTracker@mind-infotech.com p_SourcePCName HTML 0 2 2 2021-10-01 09:29:22.560 User Request SystemTracker Successful
----------------------------------------------------------------------------------------------------------------------------
Declare @p_EmailType varchar(200)=''
DECLARE @p_NewBodyText NVARCHAR(max)
set @p_EmailType = 'UserQueries'
EXEC PR_Get_Email_Templete @p_EmailType, @p_NewBodyText output
print '' +@p_NewBodyText
this is giving this html
<!DOCTYPE html><html lang="en"><head><meta charset="utf-8"><title>Mailer</title></head><body style="margin:0 auto;background:#ffffff;"><div style="background:#ffffff;padding:25px 0"><table style="font-family:Calibri, arial;font-size:12px;color:#666;max-width:610px;color:#414042"bgcolor="#fff" border="0" align="center" width="100%" cellpadding="0" cellspacing="0"><tr> <td style="background:#db2128; font-family:Calibri, arial; font-weight:bold;color:#fff;font-size:28px;padding:12px 12px 0" valign="top">My Activity</td><td style="background:#db2128;color:#fff;padding:12px" align="right">05-Oct-2021<br></td></tr><tr><td colspan="2" style="padding:10px 12px;font-weight:300"> <strong>Dear Team</strong>,<br><br>
A new request is recevied to add below text in Productive List from @@EmployeeDetail@@ .<br>@@ProcessName@@ <br>This is an auto generated email through <strong>My Activity</strong>. <br></td></tr><tr><td colspan="2" style="padding:0 0 12px 12px;font-weight:300"><strong>Best Regards,</strong><br>My Activity Team</td></tr><tr> <td colspan="2" style="padding:12px;font-weight:300;background:#e7e8e9;color:#6d6e71;font-size:11px"><strong>Disclaimer:</strong><br>This communication is System Generated. Please do not reply to this Email. If you are not the correct recipient for this notification, please contact the My Activity Administrator.©MIND All Rights Reserved. </td></tr></table></div></body></html>
there is a scheduler which is running and sending the messages
Comments
Post a Comment