-- =============================================
-- Author:		Charlene van Heerden
-- Create date: 2018-01-11
-- Description:	Campaign Log Export
-- =============================================
CREATE PROCEDURE sp_CampaignLogExport 
	@exportDate VARCHAR(50)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    SELECT c.campaignTitle as Title, CASE WHEN m.messageType = 2 THEN 'SMS' ELSE 'Email' END AS MessageType, m.dateSent AS DateSent, ISNULL(u.name + ' ' + u.surname, '') AS SentBy,
	m.message as MessageContent, ISNULL(r.recipientName , '') as Recipient
	FROM pal_CampaignMessage m 
	LEFT OUTER JOIN pal_Campaign c ON c.recId = m.campaignId 
	LEFT OUTER JOIN pal_vUserShared u ON u.recId = c.createdBy 
	LEFT OUTER JOIN pal_CampaignRecipient r ON r.campaignId = m.campaignId AND r.recipientNumber = (CASE WHEN m.messageType = 2 THEN m.mobile ELSE m.email END)
	WHERE m.messageType = 2
	AND (@exportDate = '' OR CONVERT(DATE, m.DateSent) = @exportDate)

END
