CREATE PROCEDURE proc1 (
	@stream_id int,
	@type varchar(20) = 'Stream'
)

AS

if @type = 'Batch' begin
	-- if a batch id is passed through the stream id
	SELECT @stream_id = StreamId
	FROM Batches
	WHERE BatchId = @stream_id
end

DELETE StreamTracking
WHERE StreamId = @stream_id

DECLARE @job_id int
DECLARE @is_printing int
DECLARE @is_mailing int
DECLARE @is_inkjetting int
DECLARE @national_job_id int

SELECT @job_id = j.JobId, @is_printing = IsPrinting, @is_mailing = IsMailing, @is_inkjetting = IsInkjet, @national_job_id = NationalJobId
FROM Streams s INNER JOIN
		Jobs j ON s.JobId = j.JobId
WHERE StreamId = @stream_id

DECLARE @laser_complete int
SELECT 	@laser_complete = StatusLevel
FROM 	JobControl.dbo.Status 
WHERE	Status = 'Laser Reconciled'

DECLARE @mail_complete int
SELECT 	@mail_complete = StatusLevel
FROM 	JobControl.dbo.Status 
WHERE	Status = 'Mail Reconciled'

DECLARE @inkjet_complete int
SELECT 	@inkjet_complete = StatusLevel
FROM 	JobControl.dbo.Status 
WHERE	Status = 'Inkjet Reconciled'

/* Sites */

CREATE TABLE #temp_data (
	MachineType varchar(50),
	Site varchar(30),
	Qty int,
	BatchStatus varchar(50)
)

/* PRINT INFORMATION */ 
if @is_printing = 1 begin
	INSERT INTO #temp_data
		SELECT 'Printer', MachineSite, SUM(BatchImages), BatchStatus
		FROM 	Streams st INNER JOIN
				Batches b ON b.StreamID = st.StreamID INNER JOIN
				BatchesPrintDetails bpd ON b.BatchId = bpd.BatchId INNER JOIN
				JobControl.dbo.MachineShifts ms ON ms.MachineShiftId = bpd.MachineShiftId INNER JOIN
				JobControl.dbo.Printers p ON p.PrinterID = ms.MachineID
		WHERE 	Remakes = 0 AND st.StreamID = @stream_id AND IsPrinting = 1 AND MachineType like 'Printer%'  AND BatchStatus NOT LIKE '%Cancelled'
		GROUP BY MachineSite, BatchStatus
end
		
/* MAIL INFORMATION */ 
if @is_mailing = 1 begin
	INSERT INTO #temp_data
		SELECT 'Mail Machine', MachineSite,  SUM(BatchMailpacks), BatchStatus
		FROM 	Streams st INNER JOIN
				Batches b ON b.StreamID = st.StreamID INNER JOIN
				BatchesMailDetails bmd ON b.BatchId = bmd.BatchId INNER JOIN
				JobControl.dbo.MachineShifts ms ON ms.MachineShiftId = bmd.MachineShiftId INNER JOIN
				JobControl.dbo.MailMachines m ON m.MailMachineId = ms.MachineID INNER JOIN
				JobControl.dbo.Status s ON b.BatchStatus = s.Status
		WHERE 	Remakes = 0 AND st.StreamID = @stream_id AND IsMailing = 1 AND MachineType like 'Mail Machine%' AND BatchStatus NOT LIKE '%Cancelled'
		GROUP BY MachineSite, BatchStatus
end
	
/* INKJET INFORMATION */

if @is_inkjetting = 1 begin
	INSERT INTO #temp_data
		SELECT 'Inkjet', MachineSite,  SUM(BatchMailpacks), BatchStatus
		FROM 	Streams st INNER JOIN
				Batches b ON b.StreamID = st.StreamID INNER JOIN
				BatchesInkjetDetails bmd ON b.BatchId = bmd.BatchId INNER JOIN
				JobControl.dbo.MachineShifts ms ON ms.MachineShiftId = bmd.MachineShiftId INNER JOIN
				JobControl.dbo.Inkjets i ON i.InkjetID = ms.MachineID INNER JOIN
				JobControl.dbo.Status s ON b.BatchStatus = s.Status
		WHERE 	Remakes = 0 AND st.StreamID = @stream_id AND IsInkjet = 1 AND MachineType like 'Inkjet%' AND BatchStatus NOT LIKE '%Cancelled'
		GROUP BY MachineSite, BatchStatus
end

DECLARE site CURSOR FOR
	SELECT DISTINCT Site 
	FROM #temp_data

OPEN site

DECLARE @site varchar(30)
DECLARE @print_qty int
DECLARE @print_complete int
DECLARE @mail_qty int
DECLARE @mail_qty_complete int
DECLARE @inkjet_qty int
DECLARE @inkjet_qty_complete int

FETCH NEXT FROM site INTO @site
WHILE @@fetch_status = 0
BEGIN

	SELECT @print_qty = isnull(SUM(Qty), 0)
	FROM #temp_data
	WHERE MachineType = 'Printer'
	
	SELECT @print_complete = isnull(SUM(Qty), 0)
	FROM #temp_data td INNER JOIN
			Status s ON s.Status = td.BatchStatus
	WHERE MachineType = 'Printer' AND StatusLevel >= @laser_complete
	
	SELECT @mail_qty = isnull(SUM(Qty), 0)
	FROM #temp_data
	WHERE MachineType LIKE 'Mail Machine%'
	
	SELECT @mail_qty_complete = isnull(SUM(Qty), 0)
	FROM #temp_data td INNER JOIN
			Status s ON s.Status = td.BatchStatus
	WHERE MachineType = 'Mail Machine' AND StatusLevel >= @mail_complete
	
	SELECT @inkjet_qty = isnull(SUM(Qty), 0)
	FROM #temp_data
	WHERE MachineType = 'Inkjet'
	
	SELECT @inkjet_qty_complete = isnull(SUM(Qty), 0)
	FROM #temp_data td INNER JOIN
			Status s ON s.Status = td.BatchStatus
	WHERE MachineType = 'Inkjet' AND StatusLevel >= @inkjet_complete
	
	INSERT INTO StreamTracking (
		JobId,
		StreamId,
		StreamTotalImages,
		StreamImagesComplete,
		StreamTotalMailpacks,
		StreamMailpacksComplete,
		StreamTotalInkjetMailpacks,
		StreamInkjetMailpacksComplete,
		Site,
		LastUpdated,
		NationalJobId
	)
	VALUES (
		@job_id,
		@stream_id,
		@print_qty,
		@print_complete,
		@mail_qty,
		@mail_qty_complete,
		@inkjet_qty,
		@inkjet_qty_complete,
		@site,
		getdate(),
		@national_job_id
	)


	FETCH NEXT FROM site INTO @site
END

CLOSE site
DEALLOCATE site

DROP TABLE #temp_data


GO