-- Scheme Project Member query - Custom Query for PAA PI Scheme Project
-- Gets all Members with submitted proposals who selected the a DIFFERENT level of PI Cover in the proposal for this renewal to last year.
--NOTE: this excludes members who did not submit a proposal last year

CREATE OR REPLACE FUNCTION PAA_sp_GetSchemeMemberSubmittedProposals_DifferentRenewalLimit
(
	schemeprojid int
)
RETURNS TABLE(MemberID int,
	c__Previous_Option__Integer int,
	c__New_Option__Integer int) 
AS
$$
DECLARE RenewFromSchemeProjID int;

BEGIN
--First, get the SchemeProjecttID this one Renewed from

SELECT RenewFromSchemeProjectID 
INTO 
RenewFromSchemeProjID

FROM tblSchemeProject WHERE SchemeProjectID = $1;

IF RenewFromSchemeProjID IS NOT NULL

THEN

	--This is a renewal project, get members with different limits selected between these schemes

	--First get a temp table with the Renewed-From data (to join in the main SELECT query - simplifies the main query)




	CREATE TEMPORARY TABLE PreviousProject AS

	SELECT 
		MemberID,  
		tblProposal.ProposalID,
		fn_GetPropData_Integer('PILevelOfCover', tblProposal.ProposalID::int) AS PreviousOption
	
	FROM 
		tblSchemeProjectMember 
		INNER JOIN tblProposal ON tblSchemeProjectMember.ProposalID = tblProposal.ProposalID
	WHERE
		tblSchemeProjectMember.SchemeProjectID = RenewFromSchemeProjID AND 
	
		--Proposal is submitted
		tblProposal.ProposalSubmitted IS NOT NULL;

RETURN QUERY	 

	--Now get the Scheme Project Members from The current Scheme Project who:
	--   1. Submitted a Proposal in both the Renewed-From Project AND the current Project; and
	--   2. Selected a DIFFERENT cover level for the proposals in the Renewed-From Project and the current Project

	SELECT 
		-- The following fields are required in all Scheme Project Member queries:

		tblSchemeProjectMember.MemberID,
	
		-- Custom columns:

		PreviousProject.PreviousOption AS c__Previous_Option__Int,
		fn_GetPropData_Integer(''PILevelOfCover'', tblProposal.ProposalID::int) AS c__New_Option__In

	FROM	
		tblSchemeProjectMember 
		INNER JOIN tblProposal 
		ON tblProposal.ProposalID = tblSchemeProjectMember.ProposalID 
		INNER JOIN PreviousProject 
		ON tblSchemeProjectMember.MemberID = PreviousProject.MemberID

	WHERE 
		tblSchemeProjectMember.SchemeProjectID = $1 AND 
	
		--Proposal is submitted and 1million PI Cover Level option is selected

		tblProposal.ProposalSubmitted IS NOT NULL AND 

		PreviousOption <> fn_GetPropData_Integer(''PILevelOfCover'', tblProposal.ProposalID::int);

	DROP TABLE PreviousProject;
END IF;
END;
$$Language 'plpgsql';