--exec usp_getstatuscount '05/01/2001','05/01/2001','|100118|','|845A|','|629279|','','','','','','','','','','','bdm0440','frmstatus'
exec usp_getstatuscount '05/01/2001','05/07/2001','','','','','','','','','','','','','','bdm0440','frmstatus'
--select * from tmpipeline_intermediate
--drop table #tempstatus1
--drop table #tempstatus

alter Procedure dbo.usp_GetStatusCount
CREATE Procedure dbo.usp_GetStatusCount
(
@StartDate		char(10),
@EndDate		char(10),
@TMVendors		varchar(4096),		
@Matrices		varchar(4096),  		
@Efforts		varchar(4096),  		
@SeqNumbers		varchar(4096),		
@Clients		varchar(4096),  		
@Listowners		varchar(4096),  		
@Clubs			varchar(4096),  		--	Marketing Programs
@CreditOfferers		varchar(4096),  		
@ClearingCenters	varchar(4096),  		
@Mediums		varchar(4096),		
@ManifestNumbers	varchar(4096),		
@options		integer,			--	used for sql flow control (1 procedure for each form / report), corresponds to the index of the option button on the wizard
@MachineName	varchar(30),    		--	Machine Name calling Routine
@Screen		varchar(30)     		--	Screen Calling routine
)

AS

DECLARE @InsertParm CHAR(25)
DECLARE @IntParmStart INTEGER
DECLARE @IntListBoxLength INTEGER
DECLARE @GroupBY1 varchar(4096)
DECLARE @WhereClause1 varchar(4096)
DECLARE @WhereClause2 varchar(4096)
DECLARE @StartingDate  datetime
DECLARE @EndingDate datetime
SET NOCOUNT ON

/*******************************************************************/
/* Create the Base Where Clause                                    */
/*******************************************************************/
SELECT @WhereClause1 = " and TR.TransmitDate between '" + @StartDate + "' AND '" + @EndDate + "' "

/*******************************************************************/
/* Parse off the options for  TM Vendors                           */
/*******************************************************************/
IF LEN(RTRIM(@TMVendors))<>0
  BEGIN
    CREATE TABLE #tmp1(Parm varchar(255))

    --Setup the starting values.
    SELECT 	@IntParmStart = 1
    SELECT 	@IntListBoxLength = LEN(@TMVendors)

    --Loop while the variable '@IntListBoxLength' is no equal to zero.
    WHILE @IntListBoxLength <> 0
	BEGIN	--Get the first parm, strip off the "|" charater and insert parm into temp table.
		SELECT @InsertParm = SUBSTRING(@TMVendors, @IntParmStart, CHARINDEX('|', @TMVendors , 1)-1)		
		INSERT INTO #tmp1 (Parm) 
		SELECT Parm = convert(varchar(255),@InsertParm) 
		
		--Trim off the inserted parm and setup to capture the next parm for insertion.
		SELECT @IntParmStart = (CHARINDEX('|', @TMVendors , 1) + 1)
		SELECT @TMVendors = SUBSTRING(@TMVendors, CHARINDEX('|', @TMVendors , 1) + 1,  LEN(@TMVendors))
		SELECT @IntParmStart = 1
		SELECT @IntListBoxLength = LEN(@TMVendors)
	CONTINUE
    END
    SELECT @WhereClause2 = " #tempstatus.vendor not in (SELECT * FROM #tmp1)"
  END

/*******************************************************************/
/* Parse off the Matrices     	                                     */
/*******************************************************************/
IF LEN(RTRIM(@Matrices))<>0
  BEGIN
    CREATE TABLE #tmp2(Parm varchar(255))

    --Setup the starting values.
    SELECT 	@IntParmStart = 1
    SELECT 	@IntListBoxLength = LEN(@Matrices)

    --Loop while the variable '@IntListBoxLength' is no equal to zero.
    WHILE @IntListBoxLength <> 0
	BEGIN	--Get the first parm, strip off the "|" charater and insert parm into temp table.
		SELECT @InsertParm = SUBSTRING(@Matrices, @IntParmStart, CHARINDEX('|', @Matrices , 1)-1)		
		INSERT INTO #tmp2 (Parm) 
		   SELECT Parm = convert(varchar(255),@InsertParm) 
		
		--Trim off the inserted parm and setup to capture the next parm for insertion.
		SELECT @IntParmStart = (CHARINDEX('|', @Matrices , 1) + 1)
		SELECT @Matrices = SUBSTRING(@Matrices, CHARINDEX('|', @Matrices , 1) + 1,  LEN(@Matrices))
		SELECT @IntParmStart = 1
		SELECT @IntListBoxLength = LEN(@Matrices)
	CONTINUE
    END
    SELECT @WhereClause1 = @WhereClause1 + " AND me.matrixnumber in (SELECT * FROM #tmp2)"
  END

/*******************************************************************/
/* Parse off the Efforts				          */	
/*******************************************************************/
if len(rtrim(@efforts)) <> 0		
    begin	----------		 
	CREATE TABLE #tmp3(Parm char(10))

    	SELECT 	@IntParmStart = 1
    	SELECT 	@IntListBoxLength = LEN(@efforts)

    	WHILE @IntListBoxLength <> 0
		BEGIN	--Get the first parm, strip off the "|" charater and insert into temp table.
			SELECT @InsertParm = 
				SUBSTRING(@efforts, 1, CHARINDEX('|', @efforts, 1) - 1 )		

			INSERT INTO #tmp3(Parm ) 		
--			SELECT Parm = convert(int,@InsertParm)
			SELECT Parm = left(@InsertParm, 10)
			SELECT @efforts = SUBSTRING(@efforts, CHARINDEX('|', @efforts, 1) + 1,  LEN(@efforts))
			SELECT @IntListBoxLength = LEN(@efforts)
			CONTINUE
   		end
	SELECT @WhereClause1 = @WhereClause1 + " and eff.effortnumber in (SELECT * FROM #tmp3) "
    end  	 

/*******************************************************************/
/* Parse off the options for  sequence numbers                */
/*******************************************************************/
IF LEN(RTRIM(@SeqNumbers))<>0
  BEGIN
    CREATE TABLE #tmp4(Parm int)

    --Setup the starting values.
    SELECT 	@IntParmStart = 1
    SELECT 	@IntListBoxLength = LEN(@SeqNumbers)

    --Loop while the variable '@IntListBoxLength' is no equal to zero.
    WHILE @IntListBoxLength <> 0
	BEGIN	--Get the first parm, strip off the "|" charater and insert parm into temp table.
		SELECT @InsertParm = SUBSTRING(@SeqNumbers, @IntParmStart, CHARINDEX('|', @SeqNumbers , 1)-1)		
		INSERT INTO #tmp4 (Parm) 
		SELECT Parm = convert(int,@InsertParm) 
		
		--Trim off the inserted parm and setup to capture the next parm for insertion.
		SELECT @IntParmStart = (CHARINDEX('|', @SeqNumbers , 1) + 1)
		SELECT @SeqNumbers = SUBSTRING(@SeqNumbers, CHARINDEX('|', @SeqNumbers , 1) + 1,  LEN(@SeqNumbers))
		SELECT @IntParmStart = 1
		SELECT @IntListBoxLength = LEN(@SeqNumbers)
	CONTINUE
    END
   SELECT @WhereClause1 = @WhereClause1 + " AND tr.sequencenumber in (SELECT * FROM #tmp4)"
  END


/*******************************************************************/
/* Parse off the options for  clients                                    */
/*******************************************************************/
/*
IF LEN(RTRIM(@Clients))<>0
  BEGIN
    CREATE TABLE #tmp5(Parm int)

    --Setup the starting values.
    SELECT 	@IntParmStart = 1
    SELECT 	@IntListBoxLength = LEN(@Clients)

    --Loop while the variable '@IntListBoxLength' is no equal to zero.
    WHILE @IntListBoxLength <> 0
	BEGIN	--Get the first parm, strip off the "|" charater and insert parm into temp table.
		SELECT @InsertParm = SUBSTRING(@Clients, @IntParmStart, CHARINDEX('|', @Clients , 1)-1)		
		INSERT INTO #tmp5 (Parm) 
		SELECT Parm = convert(int,@InsertParm) 
		
		--Trim off the inserted parm and setup to capture the next parm for insertion.
		SELECT @IntParmStart = (CHARINDEX('|', @Clients , 1) + 1)
		SELECT @SeqNumbers = SUBSTRING(@Clients, CHARINDEX('|', @Clients , 1) + 1,  LEN(@Clients))
		SELECT @IntParmStart = 1
		SELECT @IntListBoxLength = LEN(@Clients)
	CONTINUE
    END
    SELECT @WhereClause1 = @WhereClause1 + " AND pc.parentid IN (SELECT * FROM #tmp5)"
  END
*/
/*******************************************************************/
/* Parse off the options for  listowners                               */
/*******************************************************************/
/*
IF LEN(RTRIM(@Listowners))<>0
  BEGIN
    CREATE TABLE #tmp6(Parm int)

    --Setup the starting values.
    SELECT 	@IntParmStart = 1
    SELECT 	@IntListBoxLength = LEN(@Listowners)

    --Loop while the variable '@IntListBoxLength' is no equal to zero.
    WHILE @IntListBoxLength <> 0
	BEGIN	--Get the first parm, strip off the "|" charater and insert parm into temp table.
		SELECT @InsertParm = SUBSTRING(@Listowners, @IntParmStart, CHARINDEX('|', @Listowners , 1)-1)		
		INSERT INTO #tmp6 (Parm) 
		SELECT Parm = convert(int,@InsertParm) 
		
		--Trim off the inserted parm and setup to capture the next parm for insertion.
		SELECT @IntParmStart = (CHARINDEX('|', @Listowners , 1) + 1)
		SELECT @Listowners = SUBSTRING(@Listowners, CHARINDEX('|', @Listowners , 1) + 1,  LEN(@Listowners))
		SELECT @IntParmStart = 1
		SELECT @IntListBoxLength = LEN(@Listowners)
	CONTINUE
    END
    SELECT @WhereClause1 = @WhereClause1 + " AND b.businessid IN (SELECT * FROM #tmp6)"
  END
*/

/*******************************************************************/
/* Parse off the options for  clubs                                      */
/*******************************************************************/
/*
IF LEN(RTRIM(@Clubs))<>0
  BEGIN
    CREATE TABLE #tmp7(Parm int)

    --Setup the starting values.
    SELECT 	@IntParmStart = 1
    SELECT 	@IntListBoxLength = LEN(@Clubs)

    --Loop while the variable '@IntListBoxLength' is no equal to zero.
    WHILE @IntListBoxLength <> 0
	BEGIN	--Get the first parm, strip off the "|" charater and insert parm into temp table.
		SELECT @InsertParm = SUBSTRING(@Clubs, @IntParmStart, CHARINDEX('|', @Clubs , 1)-1)		
		INSERT INTO #tmp7 (Parm) 
		SELECT Parm = convert(int,@InsertParm) 
		
		--Trim off the inserted parm and setup to capture the next parm for insertion.
		SELECT @IntParmStart = (CHARINDEX('|', @Clubs , 1) + 1)
		SELECT @Clubs = SUBSTRING(@Clubs, CHARINDEX('|', @Clubs , 1) + 1,  LEN(@Clubs))
		SELECT @IntParmStart = 1
		SELECT @IntListBoxLength = LEN(@Clubs)
	CONTINUE
    END
    SELECT @WhereClause1 = @WhereClause1 + " AND mp.marketingprogramid IN (SELECT * FROM #tmp7)"
  END
*/

/*******************************************************************/
/* Parse off the options for  clearing centers                      */
/*******************************************************************/
/*
IF LEN(RTRIM(@ClearingCenters))<>0
  BEGIN
    CREATE TABLE #tmp8(Parm int)
   -stop here
    --Setup the starting values.
    SELECT 	@IntParmStart = 1
    SELECT 	@IntListBoxLength = LEN(@SeqNumbers)

    --Loop while the variable '@IntListBoxLength' is no equal to zero.
    WHILE @IntListBoxLength <> 0
	BEGIN	--Get the first parm, strip off the "|" charater and insert parm into temp table.
		SELECT @InsertParm = SUBSTRING(@SeqNumbers, @IntParmStart, CHARINDEX('|', @SeqNumbers , 1)-1)		
		INSERT INTO #tmp4 (Parm) 
 		   SELECT Parm = convert(int,@InsertParm) 
		
		--Trim off the inserted parm and setup to capture the next parm for insertion.
		SELECT @IntParmStart = (CHARINDEX('|', @SeqNumbers , 1) + 1)
		SELECT @SeqNumbers = SUBSTRING(@SeqNumbers, CHARINDEX('|', @SeqNumbers , 1) + 1,  LEN(@SeqNumbers))
		SELECT @IntParmStart = 1
		SELECT @IntListBoxLength = LEN(@SeqNumbers)
	CONTINUE
    END
    SELECT @WhereClause1 = @WhereClause1 + " AND TR.SequenceNumber IN (SELECT * FROM #tmp4)"
  END
*/



/*---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/
/*---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/
/*---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

/* Get the Status records in reverse order,  ie:most recent status will be at the top of the list */
--select getdate()
CREATE TABLE #TempStatus
(
StatusID INT NULL,
StatusDate datetime NULL,
OrderHeaderID INT NULL,
StatusWeight int NULL,
vendor varchar(10),
matrix varchar (10),
effortnumber varchar(10),
sequencenumber int
)

--first cut is for date only
select @GroupBY1="order by odj.orderheaderid,convert(datetime,convert(char(10),odj.statusdate,101)) desc,SO.StatusWeight desc,odj.StatusID"
EXEC ("Insert into #TempStatus select odj.StatusID,odj.statusdate,ODJ.orderheaderid,SO.StatusWeight,
	'',me.matrixnumber,eff.effortnumber,tr.sequencenumber
	from Poets..Transmit tr with(nolock)
	join Poets..Batch bat with(nolock) on bat.transmitid=tr.transmitid
	join poets..orderheader oh with(nolock) on oh.batchid=bat.batchid
	join  Poets..OrderDetailJournal odj with(nolock) on oh.orderheaderid=odj.orderheaderid
	join poets..Effort eff with(nolock) on eff.Effortid=oh.EffortID
	join mastereffort me with(nolock) on eff.Effortid=me.EffortID
	join StatusWeight so with(nolock) on so.StatusID=odj.StatusID
	WHERE odj.orderdetailid=1 and eff.efforttypeid=2" + @WHEREclause1 + @GroupBy1)

--puting the vendor in the above join takes forever
update #tempstatus
	set vendor=
	(
	select distinct tmb.businesscode
	from transmittals tm with(nolock)
	join filehistory fh with(nolock) on tm.filehistoryid=fh.filehistoryid
	join poets..tmbatch tmb with(nolock) on fh.batchcode=tmb.batchnumber
	where #tempstatus.effortnumber=tm.keycode
	)


--kick out the vendors that dont apply
if len(@whereclause2) >0
	begin
	exec("delete from #tempstatus where" + @whereclause2)
	end	

--lets see what we can see
delete from tmpipeline_intermediate
insert into tmpipeline_intermediate select * from #tempstatus

CREATE TABLE #TempStatus1
(
StatusID INT NULL,
StatusDate datetime NULL,
OrderHeaderID INT NULL,
StatusWeight int NULL
)


DECLARE @StatusID INT
DECLARE @StatusDate datetime
DECLARE @OrderHeaderID INT
DECLARE @StatusWeight INT
DECLARE @LASTORDERHEADER INT

DECLARE Status_Cursor insensitive CURSOR FOR
	SELECT StatusID,statusdate,orderheaderid,StatusWeight
        	FROM #tempStatus with(nolock)
   	order by orderheaderid,convert(datetime,convert(char(10),statusdate,101)) desc,StatusWeight desc,StatusID

SELECT @LASTORDERHEADER=0

OPEN Status_Cursor
FETCH NEXT FROM Status_Cursor Into @StatusID, @StatusDate,@OrderHeaderID,@StatusWeight
WHILE @@FETCH_STATUS = 0 
	BEGIN
		  IF @LASTORDERHEADER <> @ORDERHEADERID
		  BEGIN
			   INSERT INTO #TempStatus1 values(@StatusID, @StatusDate,@OrderHeaderID,@StatusWeight)
			   SELECT  @LASTORDERHEADER = @ORDERHEADERID
		  END
		  FETCH NEXT FROM Status_Cursor Into @StatusID, @StatusDate,@OrderHeaderID,@StatusWeight
	END
CLOSE Status_Cursor
DEALLOCATE Status_Cursor

DELETE from TMPipeline_Status_Results where MachineName=@MachineName and Screen=@Screen 

INSERT INTO TMPipeline_Status_Results 
	select @MachineName, @Screen, StatusID,convert(char(10), StatusDate,101),OrderHeaderID,StatusWeight
	from #tempStatus1 

INSERT into TMPipeline_Status_Results	values (@MachineName, @Screen, 0,'01/01/1900',0,@@Rowcount)

SET NOCOUNT OFF

Select * from TMPipeline_Status_Results with(nolock)
where MachineName=@MachineName and Screen=@Screen 
order by statusid