

 --alter table eml_log
 --add eml_log_doc_no varchar(30)


GO
/****** Object:  StoredProcedure [dbo].[sp_send_email_webwork]    Script Date: 12/08/2024 6:48:44 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE OR ALTER procedure [dbo].[sp_send_email_webwork]

 @environment	VARCHAR(200)
	
AS
BEGIN

DECLARE @as_mail_profile	VARCHAR(200) ; 
DECLARE @as_to				VARCHAR(500) ;
DECLARE @copy_to			VARCHAR(1000) ;
DECLARE @as_subject			VARCHAR(200) ;
DECLARE @as_body			VARCHAR(MAX);
DECLARE @paths				VARCHAR(MAX);
DECLARE @as_doc_no			VARCHAR(30);
DECLARE @an_rowid			NUMERIC(14,0);
DECLARE @count				NUMERIC(14,0);


DECLARE EMAIL_CR CURSOR FAST_FORWARD FOR

	SELECT	eml_log_from,
			eml_log_to_email,
			eml_log_cc_email,
			eml_log_subject,
			eml_log_content,	
			eml_log_doc_no,
			rowid
	FROM	eml_log (NOLOCK)
	WHERE	eml_log_status IN ('P', 'E') 
	ORDER BY RowID
	
	OPEN EMAIL_CR 
		FETCH NEXT FROM EMAIL_CR Into @as_mail_profile, @as_to,  @copy_to, @as_subject, @as_body, @as_doc_no, @an_rowid ;	
		WHILE @@FETCH_STATUS = 0 
		BEGIN
		
--		exec msdb.dbo.sysmail_configure_sp 'MaxFileSize','10000000'
		--exec msdb.dbo.sysmail_configure_sp 'ProhibitedExtensions','exe,dll,vbs,js,ps'
			SELECT	@count = count(*)
			FROM	wkr_mst, wkr_ref
			WHERE	wkr_mst.site_cd = wkr_ref.site_cd
			AND		wkr_mst.rowid= wkr_ref.mst_rowid
			AND		wkr_mst.wkr_mst_wr_no = @as_doc_no;
			
			If @count > 0 
				BEGIN
					SELECT	@paths = REPLACE(STRING_AGG(wkr_ref.column3, ';'), '{directory}', @environment)
					FROM	wkr_mst, wkr_ref
					WHERE	wkr_mst.site_cd = wkr_ref.site_cd
					AND		wkr_mst.rowid= wkr_ref.mst_rowid
					AND		wkr_mst.wkr_mst_wr_no = @as_doc_no;

					EXEC msdb.dbo.sp_send_dbmail  
					@profile_name = 'CMMS Admin',  
					@recipients = @as_to,  
					@copy_recipients = @copy_to, 
					@subject = @as_subject, 
					@body = @as_body,
					@file_attachments = @paths,
					@body_format = 'HTML'; 
				END
			ELSE
				IF @count = 0
					BEGIN
						EXEC msdb.dbo.sp_send_dbmail  
						@profile_name = 'CMMS Admin',  
						@recipients = @as_to,  
						@copy_recipients = @copy_to, 
						@subject = @as_subject, 
						@body = @as_body,
						@body_format = 'HTML'; ;
					END
				ELSE
					BEGIN 
					EXEC msdb.dbo.sp_send_dbmail  
						@profile_name = 'CMMS Admin',  
						@recipients = @as_to,  
						@copy_recipients = @copy_to, 
						@subject = @as_subject, 
						@body = @as_body; 
					END

		
			UPDATE	eml_log
			SET		eml_log_status = 'S',
					eml_log_msg = 'The mail had been successfully sent.'
			WHERE	RowID = @an_rowid ;	
			
		FETCH NEXT FROM EMAIL_CR Into @as_mail_profile, @as_to,  @copy_to, @as_subject, @as_body, @as_doc_no, @an_rowid ;
		END
	
	CLOSE EMAIL_CR 
	DEALLOCATE EMAIL_CR 
END

