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.&#169MIND 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.&#169MIND 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.&#169MIND 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.&#169MIND 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.&#169MIND 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.&#169MIND 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.&#169MIND 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.&#169MIND All Rights Reserved. </td></tr></table></div></body></html>


there is a scheduler which is running and sending the messages


Comments

Post a Comment

Popular posts from this blog

Npgsql query and format with output parameters

  public string UploadExceldata(string json)         {             string connectionString = String.Format(new Connection().ConnectionString);             DataSet ds = new DataSet();             BaseResultData oBaseResult = new BaseResultData();             var connection = new Npgsql.NpgsqlConnection(connectionString);             try             {                                if (!(connection.State == ConnectionState.Open))                 {                     connection.Open();                 }                 //NpgsqlTransaction trans = connection.BeginTransaction();                 var command = new Npgsql.NpgsqlCommand("systemtracker_dbo.pr_create_user_from_excel_json", connection);                 command.CommandType = System.Data.CommandType.StoredProcedure;                 IDbDataAdapter dbDataAdapter = new NpgsqlDataAdapter();                 //Parameter Settings                 var parameter = command.CreateParameter();                 parameter.ParameterName =
Read more

Npgsql Helper class

 using System; using System.Collections.Generic; using System.Text; using System.Data; using Npgsql; using NpgsqlTypes; namespace AI_Portal_Cloud.DAL {     public class clsPSQLHelper : IDisposable     {         private NpgsqlConnection connection = null;         private String connectionString = "dbConnection";         private NpgsqlDataAdapter sqlData = new NpgsqlDataAdapter();         public NpgsqlCommand sqlCmd = new NpgsqlCommand();                  // {0} : IP, {1} : DB Name, {2} : userID, {3} : userPW         private String connectionFormat = "Server={0};Database={1};User Id={2};Password={3};Port={4}";         public String ConnectionString         {             set             {                 connectionString = value;             }         }         #region Constructors         public clsPSQLHelper()         {             //             // TODO: Add constructor logic here             //             string strConn = String.Format(new Connection().ConnectionS
Read more