CREATE PROCEDURE [dbo].[fetch_crp_for_bu]
    @fromDate nvarchar(max) = '',
    @toDate nvarchar(max) = '',
    @salesId nvarchar(max) = '',
    @techBasedOn nvarchar(max) = 'package',
    @whereCondn nvarchar(max) = '',
    @relationship_id nvarchar(max) = '',
    @employee_level nvarchar(max) = ''
AS
    declare @select_query nvarchar(max) = '';
    declare @select_stats nvarchar(max) = '';
    declare @finalSelect nvarchar(max) = '';
    declare @profitSelect nvarchar(max) = '';
    declare @profitQuery nvarchar(max) = '';
    declare @finalQuery nvarchar(max) = '';
    declare @joinOn nvarchar(max) = 'fp.pkg_tech_id';
    declare @relationshipCondn nvarchar(max) = '';
    if @techBasedOn = 'resource'
        BEGIN
            SET @joinOn = 'fp.res_tech_id'
        end
    if @relationship_id != ''
        BEGIN
            SET @relationshipCondn = ' and relationship_id IN (' + @relationship_id + ')'
        end
    declare @start nvarchar(max) = 0;
    declare @currentDate date = CAST(@fromDate AS DATE);
    declare @endDate date = EOMONTH(CAST(@toDate AS DATE));
    WHILE @currentDate < @endDate
        BEGIN
            -- all employee salary for the month
            SET @select_stats = @select_stats +  '(select sum(no_of_calendar_days_for_user*per_day_salary_calendar) from fact_user_salary usal where mbt.technology_id = usal.technology_id and usal.DateKey='+CAST(dbo.convertDateToInt(@currentDate) AS nvarchar)+' and usal.DateKey <= dbo.convertDateToInt(EOMONTH(GETDATE()))) as [resource_salary_'+@start+'],';
            SET @finalSelect = @finalSelect + 'sum([resource_salary_'+@start+']) as [resource_salary_'+@start+'],'
            -- all employee count for the month
            SET @select_stats = @select_stats +  '(select COUNT(user_id) from fact_user_salary usal where mbt.technology_id = usal.technology_id and usal.DateKey='+CAST(dbo.convertDateToInt(@currentDate) AS nvarchar)+' and usal.DateKey <= dbo.convertDateToInt(EOMONTH(GETDATE()))) as [resource_salary_cnt_'+@start+'],';
            SET @finalSelect = @finalSelect + 'sum([resource_salary_cnt_'+@start+']) as [resource_salary_cnt_'+@start+'],'
            -- all project amount for the month
            SET @select_stats = @select_stats +  '(select sum(contribution_amt) from kpi_crp_for_project_contribution_by_technology pct where mbt.technology_id = pct.technology_id and (pct.yearmonth+''-01'') between '''+CAST(@currentDate AS nvarchar)+''' and '''+CAST(EOMONTH(@currentDate) AS nvarchar)+''''+@relationshipCondn+') as [project_amt_'+@start+'],';
            SET @finalSelect = @finalSelect + 'sum([project_amt_'+@start+']) 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)+' and timesheet.amount_after_adjustment is not null then (timesheet.amount_after_adjustment*timesheet.inr_rate) else 0 end),2) as [pkg_amount_'+@start+'],';
            SET @finalSelect = @finalSelect + 'sum([pkg_amount_'+@start+']) as [pkg_amount_'+@start+'],'
            -- expected unbilled package cnt for the month
            SET @select_stats = @select_stats +  'round(count(distinct (case when fp.DateKey = '+CAST(dbo.convertDateToInt(@currentDate) AS nvarchar)+' and timesheet.amount_after_adjustment is not null then timesheet.crp_parent_id end)),2) as [pkg_cnt_'+@start+'],';
            SET @finalSelect = @finalSelect + 'sum([pkg_cnt_'+@start+']) as [pkg_cnt_'+@start+'],'
            -- billed amount split across
            SET @select_stats = @select_stats +  '(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)) as [billed_amount_'+@start+'],';
            SET @finalSelect = @finalSelect + 'sum([billed_amount_'+@start+']) as [billed_amount_'+@start+'],'
            -- count of packages contributed to billed amt split across
            SET @select_stats = @select_stats +  '(count((case when fp.DateKey = '+CAST(dbo.convertDateToInt(@currentDate) AS nvarchar)+' then (case when fp.actual_billed_amount> 0 then fp.parent_id end)  end))) as [billed_pkg_cnt_'+@start+'],';
            SET @finalSelect = @finalSelect + 'sum([billed_pkg_cnt_'+@start+']) 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 @finalSelect = @finalSelect + 'sum([billed_amount_by_month_'+@start+']) as [billed_amount_by_month_'+@start+'],'
            -- count of packages for billed amount based on start date
            SET @select_stats = @select_stats +  'count((case when fp.DateKey = '+CAST(dbo.convertDateToInt(@currentDate) AS nvarchar)+' and fp.billed_amount is not null then fp.parent_id end)) as [billed_pkg_cnt_by_month_'+@start+'],';
            SET @finalSelect = @finalSelect + 'sum([billed_pkg_cnt_by_month_'+@start+']) 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 @finalSelect = @finalSelect + 'sum([unbilled_amount_'+@start+']) as [unbilled_amount_'+@start+'],'
            -- count of packages for unbilled amout split across
            SET @select_stats = @select_stats +  'count((case when fp.DateKey = '+CAST(dbo.convertDateToInt(@currentDate) AS nvarchar)+' and fp.actual_unbilled_amount is not null and fp.timesheet_count_in_month > 0 then fp.parent_id  end)) as [unbilled_pkg_cnt_'+@start+'],';
            SET @finalSelect = @finalSelect + 'sum([unbilled_pkg_cnt_'+@start+']) 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 @finalSelect = @finalSelect + 'sum([unbilled_amount_by_month_'+@start+']) as [unbilled_amount_by_month_'+@start+'],'
            -- count of packages for unbilled amount split across
            SET @select_stats = @select_stats +  'count((case when fp.DateKey = '+CAST(dbo.convertDateToInt(@currentDate) AS nvarchar)+' and fp.unbilled_amount is not null and fp.timesheet_count_in_month > 0 then fp.parent_id end)) as [unbilled_pkg_cnt_by_month_'+@start+'],';
            SET @finalSelect = @finalSelect + 'sum([unbilled_pkg_cnt_by_month_'+@start+']) as [unbilled_pkg_cnt_by_month_'+@start+'],'

            SET @profitSelect = @profitSelect
                            + '(iif([pkg_amount_'+@start+'] is null,0,[pkg_amount_'+@start+']) - iif([resource_salary_'+@start+'] is null, 0, [resource_salary_'+@start+'] )) as [profit_in_amt_by_pkg_'+@start+'],'
                            + '(iif([billed_amount_'+@start+'] is null, 0, [billed_amount_'+@start+'])  + iif([project_amt_'+@start+'] is null, 0 ,[project_amt_'+@start+'] ) - [resource_salary_'+@start+']) as [profit_in_amt_by_billed_'+@start+'],'
                            + '([billed_amount_'+@start+'] + [unbilled_amount_'+@start+']  + iif([project_amt_'+@start+'] is null, 0, [project_amt_'+@start+'] )- [resource_salary_'+@start+']) as [profit_in_amt_by_billed_unbilled_'+@start+'],'
                            + '([billed_amount_by_month_'+@start+']  + iif([project_amt_'+@start+'] is null, 0, [project_amt_'+@start+']) - [resource_salary_'+@start+']) as [profit_in_amt_by_billed_cd_'+@start+'],'
                            + '([billed_amount_by_month_'+@start+'] + [unbilled_amount_by_month_'+@start+']  + iif([project_amt_'+@start+'] is null, 0, [project_amt_'+@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 +  '(select sum(no_of_calendar_days_for_user*per_day_salary_calendar) from fact_user_salary usal where mbt.technology_id = usal.technology_id and usal.DateKey between '+CAST(dbo.convertDateToInt(CAST(@fromDate AS DATE)) AS nvarchar)+' and '+CAST(dbo.convertDateToInt(CAST(@toDate AS DATE)) AS nvarchar)+' and usal.DateKey <= dbo.convertDateToInt(EOMONTH(GETDATE()))) as [resource_salary_all],'
            SET @finalSelect = @finalSelect + 'sum([resource_salary_all]) as [resource_salary_all],'
            -- count of employees for the duration
            SET @select_stats = @select_stats +  '(select COUNT(distinct user_id) from fact_user_salary usal where mbt.technology_id = usal.technology_id and usal.DateKey between '+CAST(dbo.convertDateToInt(CAST(@fromDate AS DATE)) AS nvarchar)+' and '+CAST(dbo.convertDateToInt(CAST(@toDate AS DATE)) AS nvarchar)+' and usal.DateKey <= dbo.convertDateToInt(EOMONTH(GETDATE()))) as [resource_salary_all_cnt],'
            SET @finalSelect = @finalSelect + 'sum([resource_salary_all_cnt]) as [resource_salary_all_cnt],'
             -- all project amount for the duration
            SET @select_stats = @select_stats +  '(select sum(contribution_amt) from kpi_crp_for_project_contribution_by_technology pct where mbt.technology_id = pct.technology_id and (pct.yearmonth+''-01'') between '''+CAST(@fromDate AS nvarchar)+''' and '''+CAST(EOMONTH(@toDate) AS nvarchar)+''''+@relationshipCondn+') as [project_amt_all],';
            SET @finalSelect = @finalSelect + 'sum(iif([project_amt_all] is null, 0, [project_amt_all])) 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 @finalSelect = @finalSelect + 'sum([pkg_amount_all]) as [pkg_amount_all],'
            -- expected unbilled distinct package cnt
            SET @select_stats = @select_stats +  '(count(distinct(case when timesheet.amount_after_adjustment is not null then timesheet.crp_parent_id end))) as [pkg_cnt_all],'
            SET @finalSelect = @finalSelect + 'sum([pkg_cnt_all]) as [pkg_cnt_all],'
            -- total billed amount split across
            SET @select_stats = @select_stats +  '(sum((iif(fp.actual_billed_amount is null, 0, fp.actual_billed_amount)))) as [billed_amount_all],';
            SET @finalSelect = @finalSelect + 'sum([billed_amount_all]) as [billed_amount_all],'
            -- total billed package count for split across
            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],';
            SET @finalSelect = @finalSelect + 'sum([billed_pkg_cnt_all]) 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 @finalSelect = @finalSelect + 'sum([billed_amount_by_month_all]) as [billed_amount_by_month_all],'
            -- total billed package count by start date
            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],';
            SET @finalSelect = @finalSelect + 'sum([billed_pkg_cnt_by_month_all]) 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 @finalSelect = @finalSelect + 'sum([unbilled_amount_all]) as [unbilled_amount_all],'
            -- count of packages for unbilled amount across start date
            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],';
            SET @finalSelect = @finalSelect + 'sum([unbilled_pkg_cnt_all]) 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 @finalSelect = @finalSelect + 'sum([unbilled_amount_by_month_all]) as [unbilled_amount_by_month_all],'
            -- count of packages for total unbilled amount based on start date
            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 @finalSelect = @finalSelect + 'sum([unbilled_pkg_cnt_by_month_all]) 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] + [project_amt_all] - [resource_salary_all]) as [profit_in_amt_by_billed_all],'
                            + '([billed_amount_all] + [project_amt_all] + [unbilled_amount_all] - [resource_salary_all]) as [profit_in_amt_by_billed_unbilled_all],'
                            + '([billed_amount_by_month_all] + [project_amt_all] - [resource_salary_all]) as [profit_in_amt_by_billed_cd_all],'
                            + '([billed_amount_by_month_all] + [unbilled_amount_by_month_all] + [project_amt_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;
     SET @select_query = @select_query + ' IIF(bu.bu_id is null , 0,bu.bu_id) as bu_id,IIF(bu.bu_title is null,''Others'' ,bu.bu_title) as bu_title';

    SET @select_query = @select_query + ' from mis_bu_master bu
join mis_bu_technology as mbt on bu.bu_id = mbt.bu_id
left join fact_package as fp on '+@joinOn+' = mbt.technology_id'+
                        ' and fp.DateKey between '''+CAST(dbo.convertDateToInt(@fromDate) AS nvarchar)+''' and '''+CAST(dbo.convertDateToInt(EOMONTH(@toDate)) AS nvarchar)+'''  ' +@relationshipCondn+
                        ' left join dim_timesheets_expected as timesheet on timesheet.crp_id = fp.crp_id and (fp.DateKey/100) = (timesheet.start_date/100) where 1=1 '
--     SET @select_query = @select_query + ' where fp.DateKey  >= ''' + CAST(dbo.convertDateToInt(@fromDate) AS nvarchar) +'''';
-- 	SET @select_query = @select_query + ' and fp.DateKey  <=''' + CAST(dbo.convertDateToInt(EOMONTH(@toDate)) AS nvarchar) +'''';
    IF  @salesId != ''
        BEGIN
            SET @select_query = @select_query + ' AND ( fp.actual_sales_person_id IN (' + @salesId + ') OR fp.actual_shared_with IN ('+ @salesId+'))';
        END
--     IF @relationship_id != ''
--         BEGIN
--             SET @select_query = @select_query + ' '+@relationshipCondn
--         end
    SET @select_query = @select_query + @whereCondn
    SET @select_query = @select_query+'
    group by bu.bu_id, bu.bu_title,mbt.technology_id' ;
--                     '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 @finalQuery = 'SELECT '+@finalSelect+' IIF(t.bu_id is null , 0,t.bu_id) as bu_id,IIF(t.bu_title is null,''Others'' ,t.bu_title) as bu_title from ('+@select_query+') as t group by t.bu_id, t.bu_title';

    SET @profitQuery = 'SELECT '+@profitSelect+' res.* FROM ( '+@finalQuery+' ) AS res ORDER BY res.bu_id'
    EXEC sp_executesql @profitQuery;
go

