if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[InvestmentHistory]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[InvestmentHistory]
GO

CREATE TABLE [dbo].[InvestmentHistory] (
	[ParentInvestmentUID] [int] NOT NULL ,
	[DateReceived] [datetime] NOT NULL ,
	[ContributionTypeCatID] [int] NOT NULL ,
	[ContributionReasonCatID] [int] NOT NULL ,
	[TransactionTypeCatID] [int] NOT NULL ,
	[TransactionEventCatID] [int] NOT NULL ,
	[Amount] [money] NULL ,
	[AmountCurrency] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[DateInvested] [datetime] NULL ,
	[Units] [decimal](18, 6) NULL ,
	[IsTxUID] [int] NULL ,
	[RowIdentity] [int] IDENTITY (1, 1) NOT NULL ,
	[WFCreatorID] [int] NOT NULL ,
	[CreatorStepID] [int] NOT NULL ,
	[WFOwnerID] [int] NULL ,
	[OwnerStepID] [int] NULL ,
	[CreationStamp] [datetime] NULL ,
	[RecType] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[AccountTransactionUID] [int] NULL ,
	[TaxDate] [datetime] NULL 
) ON [PRIMARY]
GO

@TempPersonTable char (50),
@UserID char (20),
@FromDate char (50),
@ToDate char (50),
@TransType char (50),

AS

Begin

begin

/*Create a temporary table to hold the raw data extracted according to the criteria .*/
Create table [dbo]. [#TransactionSelection]
	(Personuid int,
	NINO char(9),
	EERef char(20),
	surname char(40),
	inits char (5),
	Fund char(40),
	RecDate datetime,
	InvDate datetime,
	TransType char(50),
	TransEvent char(50),
	ContSource char(50),
	ContType char(50),
	Conts decimal(10,2),
	PayCentre char(100),
	Location char(100),
	Scheme char(100),
	Benefit char(100),
	FundManager char(100)
	)

end

IF @Transtype <> 'All' and @TransEvent <> 'All' and @ContSrc <> 'All'
begin

end
IF @Transtype = 'All' and @TransEvent <> 'All' and @ContSrc <> 'All'
begin

end

Insert into #TransactionSelection 
(Personuid, 
NINO, 
EERef, 
surname, 
inits, 
Fund, 
RecDate,  
InvDate, 
TransType, 
TransEvent, 
ContSource, 
ContType, 
Conts, 
PayCentre, 
Location, 
Scheme, 
Benefit, 
FundManager)
	
select p.Personuid, 
p.NationalIDNumber, 
e.employeereference, 
p.Surname, 
p.Initials, 
i.name, 
ih.datereceived, 
ih.dateinvested, 
d.longdesc as TransactionType, 
d1.longdesc as TransactionEvent, 
d2.longdesc as ContributionSource, 
d3.longdesc as ContributionType, 
isnull(sum(ih.amount), 0.00) as Conts, 
pc.name, 
l.name, 
s.name, 
b.name, 
fm.name

from person p 
inner join employee e 
on p.personuid = e.personuid 
inner join schememember sm 
on e.employeeuid = sm.employeeuid 
left join location l 
on e.employeeuid = l.parentlocationuid 
left join paycentre pc 
on e.employeeuid = pc.parentpaycentreuid 
inner join scheme s 
on sm.schemeuid = s.schemeuid 
inner join memberbenefit mb 
on sm.memberuid = mb.memberuid 
inner join memberinvestment mi 
on mb.memberbenefituid = mi.memberbenefituid 
inner join benefit b 
on mb.benefituid = b.benefituid 
inner join investment i 
on mi.investmentuid = i.investmentuid 
inner join fundmanager fm 
on i.fundmanageruid = fm.fundmanageruid 
inner join investmenthistory ih 
on mi.memberinvestmentuid = ih.parentinvestmentuid 
inner join description d 
on ih.transactiontypecatid = d.parentuid 
inner join description d1 
on ih.transactioneventcatid = d1.parentuid 
inner join description d2 
on ih.contributionreasoncatid = d2.parentuid 
inner join description d3 
on ih.contributiontypecatid = d3.parentuid 

inner join ' + @TempPersonTable + ' on p.personuid = ' + @TempPersonTable + '.personuid

where 
(dbo.FuncRealDate(ih.datereceived) >= convert(datetime,  @fromdate, 103) 
and dbo.FuncRealDate(ih.datereceived) <= convert(datetime, @todate, 103)) 
and d.longdesc in (@transtype) 
and ltrim(rtrim(d1.longdesc)) in ( @transevent) 
and ltrim(rtrim(d2.longdesc)) in ( @contSrc)

group by 
P.personuid,
p.NationalIDNumber, 
e.employeereference, 
p.Surname, 
p.Initials, 
pc.name, 
l.name, 
s.name, 
b.name, 
i.name, 
fm.name, 
ih.datereceived, 
ih.dateinvested, 
d.longdesc, 
d1.longdesc, 
d2.longdesc, 
d3.longdesc

isnull(sum(ih.amount), 0.00) as Conts

from person p 
inner join employee e 
on p.personuid = e.personuid