USE [FLConnect_Dbrf]
GO
/****** Object:  StoredProcedure [dbo].[Pp_CollectedDataByP]    Script Date: 10/16/2019 5:38:15 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


-- Updates:
--Moran 3.11.2011: Exec Pp_Validation added
--Shmulik 23.11.2011: remove non users from Padj_Factor calculation
--Shmulik:21.02.12: add EligibleHH<>6 to CouponFilterCode
--Roni:28.03.12: add AccountCode as 3rd factor in order by
--Shmulik:11.11.12: add Padj_FactorFromDeal
--Shmulik 31.12.12: add Filter on Free Coupons
--Sari 08.07.2013 -- Replace Tdbrf_DebriefParams with Tmng_DebriefParams
--Moran 2013:   Insert R&D mode and Change production mode to use Tnam_Function table instead of temp table
--              Use SP:Pnam_GetFilteredCoupons instead of internal calculation
--              Take BC redemptions instead of Coupon red for Direct Mail channels in production mode
--              Insert Update Tp_UpdatePadjFactorHistory table
--              Use PrintDayPurchase parameter in production mode
--              Update SP:Pp_ValidationByP
--Shmulik 20.08.2014 Add BCusgae treatment
--Shmulik 02.09.2014 Add Ranked values
--Shmulik 08.07.2015 fixed bug (missing sector) in line 449
--Moran 28.10.2015: filter out non-users where RedeemersOnly=1 (instead of in MailingChannels)
--Moran 29.10.2015: filter out accounts with only 1 coupon (in "RankedXs" calculation)
--Ella 18.04.2016: Replace Delete by truncate from Tnam_Functions for performance
--Moran 19.07.2017: valid deals completion in research mode also; insert dealcode=-1 to  Tp_Update_Padj_Factor_History table when there are no valid deals

BEGIN TRY
BEGIN TRAN

--ALTER  procedure [dbo].[Pp_CollectedDataByP] (
declare
@DealCode INT = 45,
                                            @RnDFunctionCode INT = 1005,
                                            @Ptype INT = 0,
                                            @Pparams INT = 1
--) AS

select count(*) as A
from FLConnect_Imp.dbo.zzzTnam_Functions_Backup
where FunctionCode=1005
    and CouponCode=2019021270 and Ptype=-1 and DealCode=45

SET NOCOUNT ON

--DECLARE @DealCode INT=209,@RnDFunctionCode INT = NULL,@Ptype INT = 0,@Pparams INT = NULL
exec Pcont_WriteToLog @DealCode,'Start P ','Pp_CollectedDataByP'
declare @Padj_FactorDealsBack INT,@Padj_FactorFromDeal int
declare @CouponFilterCode int
declare @ChannelScopeFilter INT
DECLARE @PrintDayPurchase bit
declare @FunctionCode SMALLINT
DECLARE @BCusage bit

declare @I smallint,  @S smallint ,@SearchDealCode int, @P smallint,@IsFinal smallint,@Sql varchar(500)

--Gather coupon Information
create table #Coupons(
CouponCode int,
Description varchar(250),
OfferType SMALLINT)

insert #Coupons
Select CouponCode,Description,OfferType
from Tcpn_MainHistory
where DealCode=@DealCode

--insert 2 rows for summary entries
Insert  #Coupons(CouponCode,Description) select 0,'All Coupons'
Insert  #Coupons(CouponCode,Description) select -1,'All Coupons-Weighted Avg'

------------------------Filtering-------------------------
create table #TempFilteredCoupons
(DealCode int,CouponCode int)

create table #FilteredCoupons
(FunctionCode SMALLINT ,CouponCode int)

declare @StrFilterCode varchar(200)
declare @TempFilterCode smallint
declare @CharPosition smallint
declare @FilterSQL varchar(1000)
DECLARE @SQL VARCHAR(1000)

SELECT @FunctionCode=1
WHILE @FunctionCode<=4
BEGIN
    SELECT @CouponFilterCode=CouponFilterCode,@ChannelScopeFilter=ChannelScopeFilter
    FROM dbo.Tp_researchParamsByP WHERE FunctionCode=@FunctionCode

    INSERT INTO #TempFilteredCoupons (DealCode,CouponCode)
    EXEC Pnam_GetFilteredCoupons @DealCode,@CouponFilterCode,@ChannelScopeFilter

    INSERT INTO #FilteredCoupons   ( FunctionCode, CouponCode )
    SELECT @FunctionCode,CouponCode
    FROM #TempFilteredCoupons

    --TRUNCATE TABLE #TempFilteredCoupons
    delete from #TempFilteredCoupons

SELECT @FunctionCode=@FunctionCode+1
END -- end  While @FunctionCode<=4


--Create cross functionXSector information
-- this table will inclurd 4 (functions)X2 (NotFinal/Final)X6(Sectors 0-5)
create table #FunctionSectorCross(
FunctionCode smallint,
Ptype bit,
Sector smallint)
Select @I=1
while @I<=8
begin
        select @IsFinal= (@I+1) % 2,
        @S =0
        while @S<=5
            begin
                    insert  #FunctionSectorCross Select CEILING(@I*1.0/2),@IsFinal,@S

                    select @S=@S+1
            end
    Select @I=@I+1
end



--find last @Padj_FactorDealsBack VALID deals
-- Search for deals that are representative and valid
Create table #ValidDeals
(FunctionCode SMALLINT,DealCode INT,OriginalFunc INT)


SELECT @FunctionCode=1
WHILE @FunctionCode<=4
Begin

        select @I=0,@SearchDealCode=@DealCode
        SELECT @Padj_FactorDealsBack=PAdjFactor_DealsBack,@Padj_FactorFromDeal=PAdjFactor_fromDeal FROM dbo.Tp_researchParamsByP WHERE FunctionCode=@FunctionCode

        While @I<@Padj_FactorDealsBack and @SearchDealCode>=@Padj_FactorFromDeal
        begin
                if (Select COUNT(*) from Tmng_DebriefParams inner join Tdeal_HeaderPPB on
                                Tmng_DebriefParams.DealCode=Tdeal_HeaderPPB.DealCode
                                    Where IsRepresentative=1 and ISNULL(DataStatus,0) IN(0,1,6) and Tmng_DebriefParams.DealCode=@SearchDealCode)>0
                                        begin
                                        insert #ValidDeals (FunctionCode,DealCode,OriginalFunc) select @FunctionCode, @SearchDealCode,@FunctionCode
                                        select @I=@I+1
                                        end
            Select @SearchDealCode=@SearchDealCode-1
        END


        SELECT @FunctionCode=@FunctionCode+1
END         --WHILE @FunctionCode<=4
--merge valid deals and current deals into required deals




Create table #RequiredDeals
(DealCode int)

insert #RequiredDeals
select DISTINCT DealCode from #ValidDeals

if (Select COUNT(*) from #RequiredDeals where DealCode=@DealCode )=0
begin
insert #RequiredDeals
Select @DealCode
end

CREATE TABLE #Tp_researchParamsByP(
    FunctionCode smallint,
    CouponFilterCode int,
    PadjFactor_ChannelScope smallint,
    PAdjFactor_DealsBack smallint,
    PAdjFactor_fromDeal int ,
    PAdj_ResearchFunc INT,
    PAdj_ResearchFuncFromDeal INT,
    Padj_RobustP float ,
    Padj_Max float ,
    Padj_MinIssue int ,
    Padj_MinRed int ,
    ChannelScopeFilter smallint ,
    ApplyWarnings bit,
    UpdatePAdjFactor BIT,
    PrintDayPurchase BIT,
    RedeemersOnly bit )


---------------------------PRODUCTION MODE-------------------------------
IF @RnDFunctionCode IS NULL AND @Ptype=0 AND @Pparams IS NULL
BEGIN


--get all relevalnt data from dbrf table

Select a.DealCode,AccountCode,CouponCode,a.ChannelCode,Sector,P1,FinalP1,P2,FinalP2,P3,FinalP3,P4,FinalP4,
                CASE WHEN CouponUsedTimes>0 then 1 ELSE 0 END CouponUsed,
                CASE WHEN CBarCodeUsed>0 then 1 ELSE 0 END BCUsed,
TotalPrintDay$Bruto
into #DbrfData
from Tdbrf_PersonalCoupons_Ver2 a inner join #RequiredDeals b
ON a.DealCode=b.DealCode
where  TotalValidPeriod$Bruto>0 and IsTargeted=1 and IsGiven=1

select count(*) as B
from FLConnect_Imp.dbo.zzzTnam_Functions_Backup
where FunctionCode=1005
    and CouponCode=2019021270 and Ptype=-1 and DealCode=45

exec Pcont_WriteToLog @DealCode,'Delete from Tnam_Functions ','Pp_CollectedDataByP'

UPDATE STATISTICS Tnam_Functions



select *
into #UserDifinedFunctions
from FLConnect_Imp.dbo.zzzTnam_Functions_Backup_2
where FunctionCode>4

--truncate table Tnam_Functions
delete from FLConnect_Imp.dbo.zzzTnam_Functions_Backup_2

insert into FLConnect_Imp.dbo.zzzTnam_Functions_Backup_2
select * from #UserDifinedFunctions

--UPDATE STATISTICS Tnam_Functions
--Delete FROM Tnam_Functions WHERE FunctionCode BETWEEN 1 AND 4

exec Pcont_WriteToLog @DealCode,'End Delete from Tnam_Functions ','Pp_CollectedDataByP'

select count(*) as C
from FLConnect_Imp.dbo.zzzTnam_Functions_Backup
where FunctionCode=1005
    and CouponCode=2019021270 and Ptype=-1 and DealCode=45

Select @I=1,@IsFinal=0

While @I<=8
begin
Select @P=CEILING(@I*1.0/2)
select @IsFinal= (@I+1) % 2
SELECT @ChannelScopeFilter=ChannelScopeFilter,@PrintDayPurchase=PrintDayPurchase,@BCusage=BCusage
FROM dbo.Tp_researchParamsByP WHERE FunctionCode=@P


select @Sql='Insert FLConnect_Imp.dbo.zzzTnam_Functions_Backup_2 (DealCode,AccountCode,CouponCode,FunctionCode,Ptype,P,ChannelCode,Sector,CouponUsed)
             Select DealCode,AccountCode,CouponCode,' + CONVERT(varchar,@P)+
                                                    ',' +CONVERT(varchar,@IsFinal) +
                                                        case when @IsFinal=1 then ',FinalP' else ',P' end+CONVERT(varchar,@P)+
                                                        ',ChannelCode,Sector,' + CASE WHEN @BCusage=1 THEN 'BCUsed' ELSE 'CouponUsed' END +' from #DbrfData where '
                                                        + case when @IsFinal=1 then 'FinalP' else 'P' end+CONVERT(varchar,@P)+ ' is not null'
                    IF @ChannelScopeFilter<>0
                    BEGIN
                    SELECT @Sql=@Sql +' AND ( ChannelCode & ' + CONVERT(VARCHAR,@ChannelScopeFilter) + '>0)'
                    END

                    IF @PrintDayPurchase=1
                    BEGIN
                    SELECT @Sql=@Sql + ' AND TotalPrintDay$Bruto>0'
                    END


EXEC (@Sql)

select @I=@I+1
END

select count(*) as D
from FLConnect_Imp.dbo.zzzTnam_Functions_Backup
where FunctionCode=1005
    and CouponCode=2019021270 and Ptype=-1 and DealCode=45

INSERT INTO #Tp_researchParamsByP
SELECT  FunctionCode, CouponFilterCode, PadjFactor_ChannelScope, PAdjFactor_DealsBack,
PAdjFactor_fromDeal, PAdj_ResearchFunc, PAdj_ResearchFuncFromDeal, Padj_RobustP, Padj_Max, Padj_MinIssue, Padj_MinRed,
ChannelScopeFilter, ApplyWarnings, UpdatePAdjFactor, PrintDayPurchase, RedeemersOnly
FROM dbo.Tp_researchParamsByP

END ELSE -----------------------------RND MODE-------------------------------
BEGIN
    IF NOT EXISTS(SELECT * FROM FLConnect_Imp.dbo.zzzTnam_Functions_Backup_2
        WHERE DealCode=@DealCode AND Ptype=@Ptype AND FunctionCode=@RnDFunctionCode)
        BEGIN
            RAISERROR ('No data for RnD mode in Tnam_Functions', 16,1)
            goto Finish
        END

    INSERT INTO #Tp_researchParamsByP
    SELECT  FunctionCode, CouponFilterCode, PadjFactor_ChannelScope, PAdjFactor_DealsBack,
PAdjFactor_fromDeal, PAdj_ResearchFunc, PAdj_ResearchFuncFromDeal, Padj_RobustP, Padj_Max, Padj_MinIssue, Padj_MinRed,
ChannelScopeFilter, ApplyWarnings, UpdatePAdjFactor, PrintDayPurchase, RedeemersOnly
 FROM Tp_researchParamsByP WHERE FunctionCode=@Pparams

    UPDATE #Tp_researchParamsByP
    SET FunctionCode=@RnDFunctionCode

    IF NOT EXISTS(SELECT * FROM #Tp_researchParamsByP)
    BEGIN
            RAISERROR ('No data for Pparams in Tp_researchParamsByP', 16,1)
            goto Finish
    END


    Update #FilteredCoupons
    set FunctionCode=@RnDFunctionCode where FunctionCode=@Pparams

    update #FunctionSectorCross
    set FunctionCode=@RnDFunctionCode where FunctionCode=@Pparams

    Update #ValidDeals
    set FunctionCode=@RnDFunctionCode,OriginalFunc=@RnDFunctionCode where FunctionCode=@Pparams

    DELETE FROM #ValidDeals WHERE FunctionCode<>@RnDFunctionCode
END



--Filter out non-users where RedeemersOnly=1

CREATE TABLE #NonUsers
(DealCode INT,AccountCode NUMERIC(18,0),ChannelCode smallint)

insert into #NonUsers
select a.DealCode,AccountCode,a.ChannelCode
from FLConnect_Imp.dbo.zzzTnam_Functions_Backup_2 a inner join  #Tp_researchParamsByP b
on a.FunctionCode=b.FunctionCode
where RedeemersOnly=1
GROUP BY a.DealCode,AccountCode,a.ChannelCode
HAVING MAX(CouponUsed)=0

DELETE a
FROM FLConnect_Imp.dbo.zzzTnam_Functions_Backup_2 a INNER JOIN #NonUsers b ON
a.AccountCode = b.AccountCode AND a.ChannelCode = b.ChannelCode AND a.DealCode = b.DealCode



--Add ResearchFunc data In case there are less than PAdjFactor_DealsBack deals
SELECT b.FunctionCode, c.DealCode,c.FunctionCode ResearchFunc
INTO #AddDeals
FROM #Tp_researchParamsByP b
INNER JOIN (SELECT FunctionCode,DealCode FROM dbo.FLConnect_Imp.dbo.zzzTnam_Functions_Backup_2 WHERE Ptype=0 GROUP BY FunctionCode,DealCode) c
ON b.PAdj_ResearchFunc=c.FunctionCode AND PAdj_ResearchFuncFromDeal<=c.DealCode
inner join Tdeal_HeaderPPB d on c.DealCode=d.DealCode
where IsRepresentative=1
ORDER BY b.FunctionCode,c.DealCode DESC


declare @PAdj_ResearchFuncFromDeal INT, @PAdj_ResearchFunc INT ,@FuncDeals INT

SELECT @FunctionCode=1
WHILE @FunctionCode<=4
Begin
        SELECT @Padj_FactorDealsBack=PAdjFactor_DealsBack,@PAdj_ResearchFuncFromDeal=PAdj_ResearchFuncFromDeal,@PAdj_ResearchFunc=PAdj_ResearchFunc
        FROM #Tp_researchParamsByP WHERE FunctionCode=isnull(@RnDFunctionCode, @FunctionCode)
        SELECT @FuncDeals=COUNT(*) from #ValidDeals WHERE OriginalFunc=isnull(@RnDFunctionCode, @FunctionCode)

        IF @FuncDeals<@Padj_FactorDealsBack
        BEGIN
            INSERT #ValidDeals
            SELECT TOP (@Padj_FactorDealsBack-@FuncDeals) ResearchFunc,DealCode,FunctionCode
            FROM #AddDeals WHERE FunctionCode=isnull(@RnDFunctionCode, @FunctionCode)
        END

        SELECT @FunctionCode=@FunctionCode+1
END         --WHILE @FunctionCode<=4



--Padj_Factor data : relevant only at coupon level
Create table #PAdj_CouponSector(
CouponCode int,
FunctionCode smallint,
Ptype SMALLINT,
Sector smallint,
CouponIssued int,
CouponRed int,
PEstRed float)

--create couponXsector1-5 Padj_Factor data
-- on mailing channels filter out non users
insert #PAdj_CouponSector
select
CouponCode,
OriginalFunc FunctionCode,
Ptype,
Sector,
COUNT(*) CouponIssued,
SUM(CouponUsed) CouponRed,
SUM(P) PEstRed
from FLConnect_Imp.dbo.zzzTnam_Functions_Backup_2 a inner join #ValidDeals b ON
a.DealCode=b.DealCode AND a.FunctionCode=b.FunctionCode
WHERE Ptype IN (@Ptype,1)
group by CouponCode,OriginalFunc,Ptype,Sector

--create couponXsector0 Padj_Factor data
insert #PAdj_CouponSector
select
CouponCode,
FunctionCode,
Ptype,
0,
sum(CouponIssued) CouponIssued,
SUM(CouponRed) CouponRed,
SUM(PEstRed) PEstRed
from #PAdj_CouponSector
where Sector<>5
group by CouponCode,FunctionCode,Ptype




--Calculate decile
Create table #CouponSectorDecile
(DealCode int,
AccountCode numeric(18,0),
CouponCode int,
FunctionCode smallint,
Ptype SMALLINT,
ChannelCode smallint,
Sector smallint,
P float,
Decile smallint,
CouponUsed SMALLINT,
Ranking smallint)

--create couponXsextor1-5 decile
insert #CouponSectorDecile
Select
a.DealCode,
AccountCode,
CouponCode,
a.FunctionCode,
Ptype,
ChannelCode,
Sector,
P,
ntile(10) over (Partition by CouponCode,a.FunctionCode,Ptype,Sector order by P desc,convert(bigint,AccountCode) % 999 ,AccountCode) Decile,
CouponUsed,
ROW_NUMBER() OVER (Partition by a.FunctionCode,AccountCode,Ptype,ChannelCode order by P DESC,CouponCode) Ranking
from FLConnect_Imp.dbo.zzzTnam_Functions_Backup_2 a
where a.DealCode=@DealCode AND Ptype IN (@Ptype,1)

--create couponXsextor0 decile
insert #CouponSectorDecile
Select
a.DealCode,
AccountCode,
CouponCode,
a.FunctionCode,
Ptype,
ChannelCode,
0,
P,
ntile(10) over (Partition by CouponCode,a.FunctionCode,Ptype order by P desc,convert(bigint,AccountCode) % 999,AccountCode) Decile,
CouponUsed,
ROW_NUMBER() OVER (Partition by a.FunctionCode,AccountCode,Ptype,ChannelCode order by P DESC,CouponCode) Ranking
from FLConnect_Imp.dbo.zzzTnam_Functions_Backup_2 a
where a.DealCode=@DealCode and Sector<>5 AND Ptype IN (@Ptype,1)


--create sextor1-4 decile
insert #CouponSectorDecile
Select
DealCode,
AccountCode,
0,
FLConnect_Imp.dbo.zzzTnam_Functions_Backup_2.FunctionCode,
Ptype,
ChannelCode,
Sector,
P,
ntile(10) over (Partition by FLConnect_Imp.dbo.zzzTnam_Functions_Backup_2.FunctionCode,Ptype,Sector order by P desc,convert(bigint,AccountCode) % 999,AccountCode) Decile,
CouponUsed,
ROW_NUMBER() OVER (Partition by FLConnect_Imp.dbo.zzzTnam_Functions_Backup_2.FunctionCode,AccountCode,Ptype,ChannelCode,Sector order by P DESC,FLConnect_Imp.dbo.zzzTnam_Functions_Backup_2.CouponCode) Ranking
from FLConnect_Imp.dbo.zzzTnam_Functions_Backup_2 INNER JOIN  #FilteredCoupons ON
FLConnect_Imp.dbo.zzzTnam_Functions_Backup_2.FunctionCode=#FilteredCoupons.FunctionCode AND
FLConnect_Imp.dbo.zzzTnam_Functions_Backup_2.CouponCode=#FilteredCoupons.CouponCode
 where DealCode=@DealCode  AND Ptype IN (@Ptype,1)



--create sextor0 decile
insert #CouponSectorDecile
Select
DealCode,
AccountCode,
0,
FLConnect_Imp.dbo.zzzTnam_Functions_Backup_2.FunctionCode,
Ptype,
ChannelCode,
0,
P,
ntile(10) over (Partition by FLConnect_Imp.dbo.zzzTnam_Functions_Backup_2.FunctionCode,Ptype order by P desc,convert(bigint,AccountCode) % 999,AccountCode) Decile,
CouponUsed,
ROW_NUMBER() OVER (Partition by FLConnect_Imp.dbo.zzzTnam_Functions_Backup_2.FunctionCode,AccountCode,Ptype,ChannelCode order by P DESC,FLConnect_Imp.dbo.zzzTnam_Functions_Backup_2.CouponCode) Ranking
from FLConnect_Imp.dbo.zzzTnam_Functions_Backup_2 INNER JOIN  #FilteredCoupons ON
FLConnect_Imp.dbo.zzzTnam_Functions_Backup_2.FunctionCode=#FilteredCoupons.FunctionCode AND
FLConnect_Imp.dbo.zzzTnam_Functions_Backup_2.CouponCode=#FilteredCoupons.CouponCode
where DealCode=@DealCode and Sector<>5 AND Ptype IN (@Ptype,1)


--summarize data

--calc num coupons per account in order to filter out accounts with only 1 coupon
select AccountCode, COUNT(Distinct CouponCode) NumCoupons
into #NumCoupons
from #CouponSectorDecile
group by AccountCode

Select
CouponCode,
FunctionCode,
Ptype,
Sector,
COUNT(*)  CouponIssued,
SUM(CouponUsed) CouponRed,
SUM(P) PEstRed,
SUM(case when Decile<=1 then 1 else 0 end) CouponIssuedDR10,
SUM(case when Decile<=1 then CouponUsed else 0 end) CouponRedDR10,
SUM(case when Decile<=2 then 1 else 0 end) CouponIssuedDR20,
SUM(case when Decile<=2 then CouponUsed else 0 end) CouponRedDR20,
SUM(case when Decile<=4 then 1 else 0 end) CouponIssuedDR40,
SUM(case when Decile<=4 then CouponUsed else 0 end) CouponRedDR40,
SUM(case when Decile<=8 then 1 else 0 end) CouponIssuedDR80,
SUM(case when Decile<=8 then CouponUsed else 0 end) CouponRedDR80,

SUM(CASE WHEN Ranking=1 and b.NumCoupons>1 THEN CouponUsed ELSE 0 END) CouponRedRanked1,
SUM(CASE WHEN Ranking=1 and b.NumCoupons>1 THEN 1 ELSE 0 END) CouponIssuedRanked1,
SUM(CASE WHEN Ranking<=2 and b.NumCoupons>1 THEN CouponUsed ELSE 0 END) CouponRedRanked2,
SUM(CASE WHEN Ranking<=2 and b.NumCoupons>1 THEN 1 ELSE 0 END) CouponIssuedRanked2,
SUM(CASE WHEN Ranking<=3 and b.NumCoupons>1 THEN CouponUsed ELSE 0 END) CouponRedRanked3,
SUM(CASE WHEN Ranking<=3 and b.NumCoupons>1 THEN 1 ELSE 0 END) CouponIssuedRanked3
into #CouponSectorLevel
from #CouponSectorDecile a inner join #NumCoupons b
on a.AccountCode=b.AccountCode
group by CouponCode,FunctionCode,Ptype,Sector




--calculate DR,Rank
Select
CouponCode,
FunctionCode,
Ptype,
Sector,
CouponIssued,
CouponRed,
PEstRed,
Case when CouponRed>0 and PEstRed>0 then
                    CouponRed/PEstRed else null end AccuracyRatio,
case    when CouponRed>0 and        CouponIssuedDR10>0  and CouponIssued>0 then
100*(1.0*CouponRedDR10/CouponIssuedDR10)/(1.0*CouponRed/CouponIssued) else null end DR10,
case    when CouponRed>0 and        CouponIssuedDR20>0  and CouponIssued>0 then
100*(1.0*CouponRedDR20/CouponIssuedDR20)/(1.0*CouponRed/CouponIssued) else null end DR20,
case    when CouponRed>0 and        CouponIssuedDR40>0  and CouponIssued>0 then
100*(1.0*CouponRedDR40/CouponIssuedDR40)/(1.0*CouponRed/CouponIssued) else null end DR40,
case    when CouponRed>0 and        CouponIssuedDR80>0 and CouponIssued>0 then
100*(1.0*CouponRedDR80/CouponIssuedDR80)/(1.0*CouponRed/CouponIssued) else null end DR80
                    into #DRAccuracy
 from #CouponSectorLevel


-- create weigthed avarage information
select
-1 CouponCode,
#DRAccuracy.FunctionCode,
Ptype,
Sector,
sum(DR10*CouponRed)/SUM(CouponRed) DR10,
sum(DR20*CouponRed)/SUM(CouponRed) DR20,
sum(DR40*CouponRed)/SUM(CouponRed) DR40,
sum(DR80*CouponRed)/SUM(CouponRed) DR80
into #TempWeightedAverage
from #DRAccuracy  INNER JOIN  #FilteredCoupons ON
#DRAccuracy.FunctionCode=#FilteredCoupons.FunctionCode AND
#DRAccuracy.CouponCode=#FilteredCoupons.CouponCode
where #DRAccuracy.CouponCode>0
group by #DRAccuracy.FunctionCode,
Ptype,
Sector


insert #DRAccuracy
Select b.CouponCode,
b.FunctionCode,
b.Ptype,
b.Sector,
a.CouponIssued,
a.CouponRed,
a.PEstRed,
a.AccuracyRatio,
b.DR10,
b.DR20,
b.DR40,
b.DR80
from #DRAccuracy a inner join #TempWeightedAverage b on
a.FunctionCode=b.FunctionCode and
a.Ptype=b.Ptype and
a.Sector=b.Sector
where a.CouponCode=0


--calculate DR,Rank  and accuracy data
Select
CouponCode,
FunctionCode,
Ptype,
Sector,
CouponIssuedRanked1,
CouponRedRanked1,
CouponIssuedRanked2,
CouponRedRanked2,
CouponIssuedRanked3,
CouponRedRanked3,
CASE WHEN CouponIssuedRanked1>0 THEN
100*(1.0*CouponRedRanked1/CouponIssuedRanked1) ELSE 0 END CouponRanked1,
CASE WHEN CouponIssuedRanked2>0 THEN
100*(1.0*CouponRedRanked2/CouponIssuedRanked2) ELSE 0 END CouponRanked2,
CASE WHEN CouponIssuedRanked3>0 THEN
100*(1.0*CouponRedRanked3/CouponIssuedRanked3) ELSE 0 END CouponRanked3
                    into #Ranking
 from #CouponSectorLevel




delete Tp_resultsSummary
FROM dbo.Tp_resultsSummary INNER JOIN dbo.#Tp_researchParamsByP ON
dbo.Tp_resultsSummary.FunctionCode = dbo.#Tp_researchParamsByP.FunctionCode AND
dbo.Tp_resultsSummary.CouponFilterCode = dbo.#Tp_researchParamsByP.CouponFilterCode AND
dbo.Tp_resultsSummary.PadjFactor_DealsBack = dbo.#Tp_researchParamsByP.PAdjFactor_DealsBack
where DealCode=@DealCode


insert Tp_resultsSummary
(DealCode, FunctionCode, IsFinal, CouponCode, CouponDescription,
OfferType, CouponFilterCode, PadjFactor_DealsBack, Sector,
CouponIssued, CouponRed, CouponIssuedForPadjFactor,CouponRedForPadjFactor,PEstRed, PEstRedForPadjFactor, AccuracyRatio,
PTempAdjFactor, DR10, DR20, DR40, DR80,Ranked1,Ranked2,Ranked3)
Select
@DealCode,
#Tp_researchParamsByP.FunctionCode,
#FunctionSectorCross.Ptype,
#Coupons.CouponCode,
#Coupons.Description,
#Coupons.OfferType,
CouponFilterCode,
PAdjFactor_DealsBack,
#FunctionSectorCross.Sector,
#DRAccuracy.CouponIssued,
#DRAccuracy.CouponRed,
#PAdj_CouponSector.CouponIssued,
#PAdj_CouponSector.CouponRed,
ROUND(#DRAccuracy.PEstRed,2),
ROUND(#PAdj_CouponSector.PEstRed,2),
ROUND(#DRAccuracy.AccuracyRatio,2),
ROUND(Case when  isnull(#PAdj_CouponSector.PEstRed,0)>0 then
                    1.0*(#PAdj_CouponSector.CouponRed+Padj_RobustP)/(   #PAdj_CouponSector.PEstRed+Padj_RobustP) else null END,2) PtempAdjFactor,
ROUND(#DRAccuracy.DR10,0),
ROUND(#DRAccuracy.DR20,0),
ROUND(#DRAccuracy.DR40,0),
ROUND(#DRAccuracy.DR80,0),
ROUND(#Ranking.CouponRanked1,2),
ROUND(#Ranking.CouponRanked2,2),
ROUND(#Ranking.CouponRanked3,2)
from (#Coupons cross join #FunctionSectorCross)
INNER JOIN #Tp_researchParamsByP ON
#FunctionSectorCross.FunctionCode=#Tp_researchParamsByP.FunctionCode
left join #PAdj_CouponSector on
#Coupons.CouponCode=#PAdj_CouponSector.CouponCode and
#FunctionSectorCross.FunctionCode =#PAdj_CouponSector.FunctionCode and
#FunctionSectorCross.Ptype =(CASE WHEN #PAdj_CouponSector.Ptype IN (-1,0) THEN 0 ELSE 1 END) and
#FunctionSectorCross.Sector=#PAdj_CouponSector.Sector
left join #DRAccuracy on
#Coupons.CouponCode=#DRAccuracy.CouponCode and
#FunctionSectorCross.FunctionCode =#DRAccuracy.FunctionCode and
#FunctionSectorCross.Ptype =(CASE WHEN #DRAccuracy.Ptype IN (-1,0) THEN 0 ELSE 1 END) and
#FunctionSectorCross.Sector=#DRAccuracy.Sector
left join #Ranking on
#Coupons.CouponCode=#Ranking.CouponCode and
#FunctionSectorCross.FunctionCode =#Ranking.FunctionCode and
#FunctionSectorCross.Ptype =(CASE WHEN #Ranking.Ptype IN (-1,0) THEN 0 ELSE 1 END) and
#FunctionSectorCross.Sector=#Ranking.Sector


--calculate PadjFactor
Update Tp_resultsSummary
set PadjFactor= ROUND(case when PTempAdjFactor>Padj_Max then Padj_Max
                                            when PTempAdjFactor<1/Padj_Max then 1/Padj_Max
                                            else PTempAdjFactor END,2)
from    Tp_resultsSummary  INNER JOIN dbo.#Tp_researchParamsByP ON
dbo.Tp_resultsSummary.FunctionCode = dbo.#Tp_researchParamsByP.FunctionCode AND
dbo.Tp_resultsSummary.CouponFilterCode = dbo.#Tp_researchParamsByP.CouponFilterCode AND
dbo.Tp_resultsSummary.PadjFactor_DealsBack = dbo.#Tp_researchParamsByP.PAdjFactor_DealsBack
where DealCode=@DealCode



--calculate ABSErrorAvg
select
#DRAccuracy.FunctionCode,
Ptype,
Sector,
sum((Case when CouponRed>0 and PEstRed>0 then
        case when CouponRed>PEstRed then
                    1.0*CouponRed/PEstRed -1
        else    1.0*PEstRed/CouponRed -1    end
else null end)  *CouponRed)/SUM(CouponRed)  ABSErrorAvg
into #ABSError
from #DRAccuracy  INNER JOIN  #FilteredCoupons ON
#DRAccuracy.FunctionCode=#FilteredCoupons.FunctionCode AND
#DRAccuracy.CouponCode=#FilteredCoupons.CouponCode
where #DRAccuracy.CouponCode>0
group by #DRAccuracy.FunctionCode,
Ptype,
Sector


Update Tp_resultsSummary
set ABSErrorAvg=ROUND(b.ABSErrorAvg,2)
from Tp_resultsSummary INNER JOIN dbo.#Tp_researchParamsByP ON
dbo.Tp_resultsSummary.FunctionCode = dbo.#Tp_researchParamsByP.FunctionCode AND
dbo.Tp_resultsSummary.CouponFilterCode = dbo.#Tp_researchParamsByP.CouponFilterCode AND
dbo.Tp_resultsSummary.PadjFactor_DealsBack = dbo.#Tp_researchParamsByP.PAdjFactor_DealsBack
 inner join #ABSError b on
Tp_resultsSummary.FunctionCode=b.FunctionCode and
Tp_resultsSummary.IsFinal=(CASE WHEN b.Ptype IN (-1,0) THEN 0 ELSE 1 END) and
Tp_resultsSummary.Sector=b.Sector
where DealCode=@DealCode
and Tp_resultsSummary.CouponCode in (-1,0)

--Insert to Tp_UpdatePadjFactorHistory
DECLARE @LastDeal INT,@FromDeal INT,@NumDeals INT
SELECT @LastDeal=MAX(DealCode),@FromDeal=MIN(DealCode),@NumDeals=COUNT(DISTINCT DealCode)
FROM #ValidDeals

DELETE FROM Tp_UpdatePadjFactorHistory WHERE UpdateTime IS NULL

INSERT INTO dbo.Tp_UpdatePadjFactorHistory
        ( FunctionCode ,DealCode ,FromDeal ,DealsNum,
          ResearchFunc ,ResearchFuncFromDeal ,CouponFilterCode ,ChannelScope )
SELECT FunctionCode,isnull(@LastDeal,-1),isnull(@FromDeal,-1),@NumDeals,PAdj_ResearchFunc,PAdj_ResearchFuncFromDeal,
CouponFilterCode,ChannelScopeFilter
FROM #Tp_researchParamsByP WHERE UpdatePAdjFactor=1

DROP TABLE #Coupons,#TempFilteredCoupons,#FilteredCoupons,#FunctionSectorCross,#ValidDeals,
#RequiredDeals,#Tp_researchParamsByP,#NonUsers,#AddDeals,#NumCoupons

select count(*) as E
from FLConnect_Imp.dbo.zzzTnam_Functions_Backup
where FunctionCode=1005
    and CouponCode=2019021270 and Ptype=-1 and DealCode=45

--EXEC Pp_ValidationByP @DealCode, @RnDFunctionCode,@Pparams

exec Pcont_WriteToLog @DealCode,'End P ','Pp_CollectedDataByP'


ROLLBACK TRAN
END TRY
BEGIN CATCH
    declare @sMsg_ varchar(255) = error_message()
    raiserror (@sMsg_, 16, 1);

    if @@TRANCOUNT > 0 ROLLBACK TRAN
END CATCH

Finish:
    if @@TRANCOUNT > 0 ROLLBACK TRAN