USE [GIX]
GO
/****** Object:  StoredProcedure [dbo].[GetBulletinRecepientSummary]    Script Date: 23/02/2018 11:14:39 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Camille
-- Create date: 08/12/2011
-- Description:	Returns
-- =============================================
ALTER PROCEDURE [dbo].[GetBulletinRecepientSummary]
	@BulletinID int

AS
BEGIN
--DECLARE @BulletinID int = 18
DECLARE @Criteria  VARCHAR(10);
DECLARE @BulletinDate Date = NULL
DECLARE @GasGateIDs VARCHAR(MAX);
DECLARE @LoadSheddingCategoryIDS VARCHAR(MAX);
DECLARE @PoolIDs VARCHAR(MAX);
DECLARE @WeldedPointIDs VARCHAR(MAX);
DECLARE @PartyIDs VARCHAR(MAX);


SELECT @Criteria = Criteria, @BulletinDate = BulletinDate
FROM Bulletin

SELECT @GasGateIDs = COALESCE(@GasGateIDs + ', ' + convert(varchar(max),BulletinGasGateid), convert(varchar(max),BulletinGasGateid))
FROM BulletinGasGate
where bulletinid = @BulletinID


SELECT @LoadSheddingCategoryIDS = COALESCE(@LoadSheddingCategoryIDS + ', ' + convert(varchar(max),LoadSheddingCategoryid), convert(varchar(max),LoadSheddingCategoryid))
FROM BulletinLoadSheddingCategory
where bulletinid = @BulletinID

SELECT @PoolIDs = COALESCE(@PoolIDs + ', ' + convert(varchar(max),PoolId), convert(varchar(max),PoolId))
FROM BulletinPool
where bulletinid = @BulletinID



SELECT @WeldedPointIDs = COALESCE(@WeldedPointIDs + ', ' + convert(varchar(max),WeldedPointId), convert(varchar(max),WeldedPointId))
FROM BulletinWeldedPoint
where bulletinid = @BulletinID



SELECT @PartyIDs = COALESCE(@PartyIDs + ', ' + convert(varchar(max),PartyId), convert(varchar(max),PartyId))
FROM BulletinParty
where bulletinid = @BulletinID

--select ''+ @BulletinGasGateIds   BulletinGasGateIds , @BulletinLSCIds BulletinLSCIds, @BulletinPoolIds BulletinPoolIds, @BulletinWeldedPointIds BulletinWeldedPointIds, @BulletinPartyIds  BulletinPartyIds





DECLARE @SQL varchar(MAX);
DECLARE @CurrentDate DATE = ISNULL(@BulletinDate,GETDATE())
DECLARE @FirstCriteria TINYINT = 1
IF @Criteria IS NULL
SET @Criteria ='AND'

DECLARE @Temp as Table(
 	RowNumber Int,
	PartyName VARCHAR(100),
	WeldedPointName VARCHAR(100),
	LSCName VARCHAR(100),
	[PoolName] VARCHAR(100),
	GasGateCode VARCHAR(100)
)


SET @SQL = '  SELECT DISTINCT  ROW_NUMBER() OVER(ORDER BY Pool.Name asc) AS RowNumber, vwPartyActive.PartyName,    WeldedPointName, LOV.Name LSC, Pool.Name, GasGate.Code GasGateCode'
SET @SQL = @SQL +  ' FROM         vwWeldedPointActive '
SET @SQL = @SQL +  ' INNER JOIN ContactWeldedPoint ON vwWeldedPointActive.WeldedPointID = ContactWeldedPoint.WeldedPointID '
SET @SQL = @SQL +  ' INNER JOIN ContractWeldedPoint ON vwWeldedPointActive.WeldedPointID = ContractWeldedPoint.WeldedPointID '
SET @SQL = @SQL +  ' INNER JOIN vwContactActive ON  ContactWeldedPoint.ContactID = vwContactActive.ContactID '
SET @SQL = @SQL +  ' INNER JOIN GasGate ON vwWeldedPointActive.GasGateID = GasGate.GasGateID '
SET @SQL = @SQL +  ' INNER JOIN Pool ON GasGate.PoolID = Pool.PoolID '
SET @SQL = @SQL +  ' INNER JOIN LOV on vwWeldedPointActive.LOVLoadSheddingCategoryID = LOV.LOVID '
SET @SQL = @SQL +  ' INNER JOIN vwPartyActive on vwPartyActive.PartyID = vwContactActive.PartyID '
SET @SQL = @SQL +  ' WHERE IsEmergencyContact =  1 AND '
SET @SQL = @SQL +  '  WeldedPointEndDate >=  ''' + CONVERT(VARCHAR(50),@CurrentDate) +'''' +' AND '

IF  @PartyIDs IS NOT NULL AND LEN(@PartyIDs)>0
	BEGIN
		SET @SQL = @SQL + CASE WHEN @FirstCriteria = 0 THEN @Criteria ELSE ' (' END + '  vwContactActive.PartyID IN (' + CAST(@PartyIDs AS VARCHAR(max)) +') '
		SET @FirstCriteria = 0;
	END

IF  @WeldedPointIDs IS NOT NULL AND LEN(@WeldedPointIDs)>0
	BEGIN
		SET @SQL = @SQL + CASE WHEN @FirstCriteria = 0 THEN @Criteria ELSE ' (' END + ' vwWeldedPointActive.WeldedPointID IN (' + CAST(@WeldedPointIDs AS VARCHAR(max)) +') '
		SET @FirstCriteria = 0;
	END

IF  @GasGateIDs IS NOT NULL AND LEN(@GasGateIDs)>0
	BEGIN
		SET @SQL = @SQL + CASE WHEN @FirstCriteria = 0 THEN @Criteria ELSE ' (' END + ' GasGate.GasGateID IN (' + CAST(@GasGateIDs AS VARCHAR(max)) +') '
		SET @FirstCriteria = 0;
	END

IF  @PoolIDs IS NOT NULL AND LEN(@PoolIDs)>0
	BEGIN
		SET @SQL = @SQL + CASE WHEN @FirstCriteria = 0 THEN @Criteria ELSE ' (' END + ' Pool.PoolID IN (' + CAST(@PoolIDs AS VARCHAR(max)) +') '
		SET @FirstCriteria = 0;
	END

IF  @LoadSheddingCategoryIDS IS NOT NULL AND LEN(@LoadSheddingCategoryIDS)>0
	BEGIN
		SET @SQL = @SQL + CASE WHEN @FirstCriteria = 0 THEN @Criteria ELSE ' (' END + ' LOVLoadSheddingCategoryID IN (' + CAST(@LoadSheddingCategoryIDS AS VARCHAR(max))+') '
		SET @FirstCriteria = 0;
	END

SET @SQL = @SQL + CASE WHEN @FirstCriteria = 0 THEN ')' ELSE ' (1=0)' END


INSERT INTO @Temp
EXEC (@SQL)
PRINT (@SQL)



select  RowNumber,
	[PoolName]  ,
	PartyName  ,
	WeldedPointName  ,
	LSCName  , GasGateCode from @Temp




END