CREATE PROCEDURE [dbo].[fetch_timesheets_expected_by_month]
    @monthDate nvarchar(max) = '',
    @whereCondn nvarchar(max) = '',
    @salesId nvarchar(max) = '',
    @blockShared nvarchar(max) = 0
AS
    declare @select_query nvarchar(max) = '';
    SET @select_query = 'SELECT ' +
                        'dt.timesheet_id,
                       dt.account_id, dt.customer_id,
                       dcp.package_id,cat.category_name as package_technology,
                       dt.start_date, dt.end_date,
                       expected_amount,
                       expected_amount*dt.inr_rate as expected_amount_inr,
                       amount_after_adjustment,
                       amount_after_adjustment*dt.inr_rate as amount_after_adjustment_inr,
                       per_day_amount,
                       dca.account_name,
                       dc.company_name,
                       fcsh.sales_person_id,
                       fcsh.shared_with,
                       salesperson.display_name as salesperson_name,
                       shared.display_name as shared_name,
                         (((iif(dt.end_date > dbo.convertDateToInt(EOMONTH('''+@monthDate+''')),dbo.convertDateToInt(EOMONTH('''+@monthDate+''')),
                           dt.end_date))-(iif(dt.start_date < dbo.convertDateToInt('''+@monthDate+'''),dbo.convertDateToInt('''+@monthDate+'''), dt.start_date))+1)
                               *per_day_amount) as actual_amount_after_adjustment ' +
                        'from dim_timesheets_expected dt join dim_customer_package dcp on dcp.crp_id = dt.crp_id
join dim_customer_accounts dca on dcp.account_id = dca.account_id
join dim_customer dc on dc.customer_id = dca.customer_id
 left join (select *,row_number() over ( partition by customer_id order by from_date desc) as last
 from fact_customer_sales_history
 where DateKey <= dbo.convertDateToInt(EOMONTH('''+@monthDate+'''))) as fcsh on fcsh.last = 1 and fcsh.customer_id = dcp.customer_id
left join dim_users as salesperson on salesperson.user_id = fcsh.sales_person_id
left join dim_users as shared on shared.user_id = fcsh.shared_with' +
                        ' left join dim_date as pkgStartDim on pkgStartDim.DateKey = dcp.start_date ' +
                        ' left join dim_date as actualEndDim on actualEndDim.DateKey = dcp.actual_end_date '+
                        ' left join mis_bu_technology as pkg_bu on pkg_bu.technology_id = dcp.technology_id '+
                        ' left join mis_bu_technology as res_bu on res_bu.technology_id = dcp.res_tech_id ' +
                        ' left join dim_resource_category as cat on cat.category_id = dcp.technology_id '
    ;
    SET @select_query = @select_query + @whereCondn
    IF  @salesId != '' and @blockShared = 0
        BEGIN
            SET @select_query = @select_query + ' AND ( fcsh.sales_person_id IN (' + @salesId + ') OR fcsh.shared_with IN ('+ @salesId+'))';
        END
    IF  @salesId != '' and @blockShared = 1
        BEGIN
            SET @select_query = @select_query + ' AND ( fcsh.sales_person_id IN (' + @salesId + '))';
        END
    SET @select_query = @select_query + ' ORDER BY dc.company_name, dca.account_name'
    EXEC sp_executesql @select_query;
go

