CREATE PROCEDURE [dbo].[fetch_crp_for_sales_team_lead]
    @fromDate nvarchar(max) = '',
    @toDate nvarchar(max) = '',
    @salesId nvarchar(max) = '',
    @whereCondn nvarchar(max) = '',
    @relationship_id nvarchar(max) = '',
    @region_type_id nvarchar(max) = '',
    @resource_level nvarchar(max) = ''
AS
    declare @select_query nvarchar(max) = '';
    declare @select_stats nvarchar(max) = '';
    declare @currentDate date = CAST(@fromDate AS DATE);
    declare @endDate date = EOMONTH(CAST(@toDate AS DATE));
    declare @profitSelect nvarchar(max) = '';
    declare @profitQuery nvarchar(max) = '';
    declare @start nvarchar(max) = 0;
    declare @filterCondn nvarchar(max) = '';
    declare @filterCondnForProject nvarchar(max) = '';
     if @relationship_id != ''
        BEGIN
            SET @filterCondn = @filterCondn + ' and fc.relationship_id IN (' + @relationship_id+')';
            SET @filterCondnForProject = @filterCondnForProject + ' and pct.relationship_id IN ('+@relationship_id+')';
        end
    if @region_type_id != ''
        BEGIN
            SET @filterCondn = @filterCondn + ' and fc.region_type_id = '+@region_type_id;
            SET @filterCondnForProject = @filterCondnForProject + ' and pct.region_type_id = '+@region_type_id;
        end
    if @resource_level != ''
        BEGIN
            SET @filterCondn = @filterCondn + ' and fp.last_resource_employee_level = '+@resource_level;
        end
    WHILE @currentDate < @endDate
        BEGIN
            -- all employee salary for the month
            SET @select_stats = @select_stats +  'round(sum(case when fp.DateKey = '+CAST(dbo.convertDateToInt(@currentDate) AS nvarchar)+' then iif(res_salary is null,0,res_salary) else 0 end),2) as [resource_salary_'+@start+'],';
            SET @select_stats = @select_stats +  'ISNULL(SUM((case when fp.DateKey = '+CAST(dbo.convertDateToInt(@currentDate) AS nvarchar)+' and res_salary is not null then res_cnt end)),0) as [resource_salary_cnt_'+@start+'],';
            -- all project amount for the month
            SET @select_stats = @select_stats +  'ISNULL((select sum(contribution_amt) from kpi_crp_for_project_contribution_by_customer pct where ((pct.tl_id IS NULL AND du.user_id IS NULL) OR pct.tl_id = du.user_id) and (pct.yearmonth+''-01'') between '''+CAST(@currentDate AS nvarchar)+''' and '''+CAST(EOMONTH(@currentDate) AS nvarchar)+''''+@filterCondnForProject+'),0) as [project_amt_'+@start+'],';
            -- expected unbilled for the month
            SET @select_stats = @select_stats +  'round(sum(case when fp.DateKey = '+CAST(dbo.convertDateToInt(@currentDate) AS nvarchar)+' then (iif(timesheet.amount_after_adjustment is null, 0, timesheet.amount_after_adjustment*timesheet.inr_rate)) else 0 end),2) as [pkg_amount_'+@start+'],';
            SET @select_stats = @select_stats +  'count((case when fp.DateKey = '+CAST(dbo.convertDateToInt(@currentDate) AS nvarchar)+' and timesheet.amount_after_adjustment is not null then fp.parent_id end)) as [pkg_cnt_'+@start+'],';
             -- billed amount split across
            SET @select_stats = @select_stats +  'round(sum(case when fp.DateKey = '+CAST(dbo.convertDateToInt(@currentDate) AS nvarchar)+' then (iif(fp.actual_billed_amount is null, 0, fp.actual_billed_amount)) else 0 end),2) as [billed_amount_'+@start+'],';
            SET @select_stats = @select_stats +  'count(case when fp.DateKey = '+CAST(dbo.convertDateToInt(@currentDate) AS nvarchar)+' and fp.actual_billed_amount> 0 then fp.parent_id end ) as [billed_pkg_cnt_'+@start+'],';
            -- billed amount based on start date
            SET @select_stats = @select_stats +  'round(sum(case when fp.DateKey = '+CAST(dbo.convertDateToInt(@currentDate) AS nvarchar)+' then (iif(fp.billed_amount is null, 0, fp.billed_amount)) else 0 end),2) as [billed_amount_by_month_'+@start+'],';
            SET @select_stats = @select_stats +  'count((case when fp.DateKey = '+CAST(dbo.convertDateToInt(@currentDate) AS nvarchar)+' and fp.billed_amount > 0 then fp.parent_id  end)) as [billed_pkg_cnt_by_month_'+@start+'],';
            -- unbilled amount split across
            SET @select_stats = @select_stats +  'round(sum(case when fp.DateKey = '+CAST(dbo.convertDateToInt(@currentDate) AS nvarchar)+' then (iif(fp.actual_unbilled_amount is null, 0, fp.actual_unbilled_amount)) else 0 end),2) as [unbilled_amount_'+@start+'],';
            SET @select_stats = @select_stats +  'count((case when fp.DateKey = '+CAST(dbo.convertDateToInt(@currentDate) AS nvarchar)+' and fp.actual_unbilled_amount > 0 then fp.parent_id end)) as [unbilled_pkg_cnt_'+@start+'],';
            -- unbilled amount based on start date
            SET @select_stats = @select_stats +  'round(sum(case when fp.DateKey = '+CAST(dbo.convertDateToInt(@currentDate) AS nvarchar)+' then iif(fp.unbilled_amount is null, 0,fp.unbilled_amount) else 0 end),2) as [unbilled_amount_by_month_'+@start+'],';
            SET @select_stats = @select_stats +  'count((case when fp.DateKey = '+CAST(dbo.convertDateToInt(@currentDate) AS nvarchar)+' and fp.unbilled_amount > 0 then fp.parent_id end)) as [unbilled_pkg_cnt_by_month_'+@start+'],';


            SET @profitSelect = @profitSelect
                            + '([pkg_amount_'+@start+']  - [resource_salary_'+@start+']) as [profit_in_amt_by_pkg_'+@start+'],'
                            + '([billed_amount_'+@start+']  - [resource_salary_'+@start+']) as [profit_in_amt_by_billed_'+@start+'],'
                            + '([billed_amount_'+@start+'] + [unbilled_amount_'+@start+']  - [resource_salary_'+@start+']) as [profit_in_amt_by_billed_unbilled_'+@start+'],'
                            + '([billed_amount_by_month_'+@start+']  - [resource_salary_'+@start+']) as [profit_in_amt_by_billed_cd_'+@start+'],'
                            + '([billed_amount_by_month_'+@start+'] + [unbilled_amount_by_month_'+@start+']  - [resource_salary_'+@start+']) as [profit_in_amt_by_billed_unbilled_cd_'+@start+'],'
                            + '([billed_amount_'+@start+'] + [unbilled_amount_'+@start+']) as [billed_unbilled_'+@start+'],'
                            + '([billed_amount_by_month_'+@start+'] + [unbilled_amount_by_month_'+@start+']) as [billed_unbilled_cd_'+@start+'],'


            SET @currentDate = DATEADD(month,1,@currentDate)
            SET @start = @start+1
        END
        -- salary of employees for technologies for the duration
        SET @select_stats = @select_stats +  'round(sum(iif(res_salary is null,0,res_salary)),2) as [resource_salary_all],'
        SET @select_stats = @select_stats +  'count(distinct(case when res_salary is not null then last_resource_id end)) as [resource_salary_all_cnt],'
        -- all project amount for the duration
        SET @select_stats = @select_stats +  'ISNULL((select sum(contribution_amt) from kpi_crp_for_project_contribution_by_customer pct where ((pct.tl_id IS NULL AND du.user_id IS NULL) OR pct.tl_id = du.user_id) and (pct.yearmonth+''-01'') between '''+CAST(@fromDate AS nvarchar)+''' and '''+CAST(EOMONTH(@toDate) AS nvarchar)+''''+@filterCondnForProject+'),0) as [project_amt_all],';
        -- expected unbilled amount
        SET @select_stats = @select_stats +  'round(sum((iif(timesheet.amount_after_adjustment is null, 0, timesheet.amount_after_adjustment*timesheet.inr_rate))),2) as [pkg_amount_all],'
        SET @select_stats = @select_stats +  'count(distinct(case when timesheet.amount_after_adjustment is null then timesheet.crp_parent_id end)) as [pkg_cnt_all],'
        -- total billed amount split across
        SET @select_stats = @select_stats +  'round(sum((iif(fp.actual_billed_amount is null, 0, fp.actual_billed_amount))),2) as [billed_amount_all],';
        SET @select_stats = @select_stats +  'count(distinct(case when fp.actual_billed_amount is not null then fp.parent_id end)) as [billed_pkg_cnt_all],';
        -- total billed amount by start date
        SET @select_stats = @select_stats +  'round(sum((iif(fp.billed_amount is null, 0, fp.billed_amount))),2) as [billed_amount_by_month_all],';
        SET @select_stats = @select_stats +  'count(distinct(case when fp.billed_amount is not null then fp.parent_id end)) as [billed_pkg_cnt_by_month_all],';
        -- total unbilled amount across start date
        SET @select_stats = @select_stats +  'round(sum((iif(fp.actual_unbilled_amount is null, 0, fp.actual_unbilled_amount))),2) as [unbilled_amount_all],';
        SET @select_stats = @select_stats +  'count(distinct(case when fp.actual_unbilled_amount is not null then fp.parent_id end)) as [unbilled_pkg_cnt_all],';
        -- total unbilled amount based on start date
        SET @select_stats = @select_stats +  'round(sum(iif(fp.unbilled_amount is null, 0,fp.unbilled_amount)),2) as [unbilled_amount_by_month_all],';
        SET @select_stats = @select_stats +  'count(distinct(case when fp.unbilled_amount is not null then fp.parent_id end)) as [unbilled_pkg_cnt_by_month_all],';

    SET @profitSelect = @profitSelect
                            + '([pkg_amount_all] - [resource_salary_all]) as [profit_in_amt_by_pkg_all],'
                            + '([billed_amount_all]  - [resource_salary_all]) as [profit_in_amt_by_billed_all],'
                            + '([billed_amount_all]  + [unbilled_amount_all] - [resource_salary_all]) as [profit_in_amt_by_billed_unbilled_all],'
                            + '([billed_amount_by_month_all]  - [resource_salary_all]) as [profit_in_amt_by_billed_cd_all],'
                            + '([billed_amount_by_month_all] + [unbilled_amount_by_month_all]  - [resource_salary_all]) as [profit_in_amt_by_billed_unbilled_cd_all],'
                            + '([billed_amount_all] + [unbilled_amount_all]) as [billed_unbilled_all],'
                            + '([billed_amount_by_month_all] + [unbilled_amount_by_month_all]) as [billed_unbilled_cd_all],'

    SET @select_query = 'select '+@select_stats+' IIF(du.user_id IS NULL,0,du.user_id) as user_id,
IIF(du.display_name IS NULL,''NA'',du.display_name) as display_name ';
    SET @select_query = @select_query + 'from fact_customers as fc left join fact_package as fp on fp.customer_id = fc.customer_id and fc.DateKey = fp.DateKey
join dim_date as mapDate on fc.DateKey = mapDate.DateKey
left join dim_customer_package dcp on fp.crp_id = dcp.crp_id join dim_customer as dc on dc.customer_id = fc.customer_id
left join dim_timesheets_expected as timesheet on timesheet.crp_id = fp.crp_id and (fp.DateKey/100) = (timesheet.start_date/100)
left join dim_users as du on du.user_id = fc.tl_id '
    SET @select_query = @select_query + ' where fc.DateKey  >= ''' + CAST(dbo.convertDateToInt(@fromDate) AS nvarchar) +'''';
	SET @select_query = @select_query + ' and fc.DateKey  <=''' + CAST(dbo.convertDateToInt(EOMONTH(@toDate)) AS nvarchar) +'''';
    IF  @salesId != ''
        BEGIN
            SET @select_query = @select_query + ' AND ( fc.sales_person_id IN (' + @salesId + '))';
--             SET @select_query = @select_query + ' AND ( fp.actual_sales_person_id IN (' + @salesId + ') OR fp.actual_shared_with IN ('+ @salesId+'))';
        END
--     SET @select_query = @select_query+' and fp.res_salary > 0 and last_resource_id is not null
    SET @select_query = @select_query+@filterCondn+'
    group by du.user_id, du.display_name' ;
--                     'ORDER BY fp.customer_id OFFSET '+@offset+' ROWS FETCH NEXT '+@pagelimit+' ROWS ONLY';


--     SET @select_query = @select_query + ' ORDER BY dc.company_name, dca.account_name'
    SET @profitQuery = 'SELECT '+@profitSelect+' res.* FROM ( '+@select_query+' ) AS res ORDER BY res.display_name'
    EXEC sp_executesql @profitQuery;
go

