CREATE PROCEDURE [dbo].[fetch_invoices_by_month]
    @monthDate nvarchar(max) = '',
    @whereCondn nvarchar(max) = '',
    @blockShared nvarchar(max) = 0,
    @salesId nvarchar(max) = ''
AS
    declare @select_query nvarchar(max) = '';
    declare @final_query nvarchar(max) = '';
    SET @select_query = 'SELECT invoice_number,grp_inv_num, di.description, di.start_date,
       di.end_date, date_generated,grp_inv_date, total,
       amount,paidamt,tax,balance,lost_amount, is_proforma_invoice,
       (((iif(di.end_date > dbo.convertDateToInt(EOMONTH('''+@monthDate+''')),dbo.convertDateToInt(EOMONTH('''+@monthDate+''')),
           di.end_date))-(iif(di.start_date < dbo.convertDateToInt('''+@monthDate+'''),dbo.convertDateToInt('''+@monthDate+'''), di.start_date))+1)
               *per_day_amount_without_tax) as actual_amount_month,
       amount_inr,tax_inr,total_inr,paidamt_inr,balance_inr,
       dca.account_id,
       dca.account_name,
       dc.customer_id,
       dc.company_name,
       dcp.package_id, cat.category_name as package_technology,
       fcsh.sales_person_id,
       fcsh.shared_with,
       salesperson.display_name as salesperson_name,
       shared.display_name as shared_name,' +
                        'IIF(shared.user_id IS NOT NULL, amount_inr/2, amount_inr) as amount_inr_shared,' +
                        '
       ((iif(di.end_date > dbo.convertDateToInt(EOMONTH('''+@monthDate+''')),dbo.convertDateToInt(EOMONTH('''+@monthDate+''')),
           di.end_date))-(iif(di.start_date < dbo.convertDateToInt('''+@monthDate+'''),dbo.convertDateToInt('''+@monthDate+'''), di.start_date))+1) as duration_in_days_for_month
from dim_invoices as di
join dim_customer_accounts dca on di.account_id = dca.account_id
join dim_customer dc on dc.customer_id = dca.customer_id
left join dim_customer_package dcp on dcp.crp_id = (select crp_id from fact_package where DateKey = dbo.convertDateToInt('''+@monthDate+''') and di.package_id = fact_package.parent_id)
' +
                        ' left join dim_date as actualStartDim on actualStartDim.DateKey = dcp.actual_start_date'+
                        ' left join dim_date as actualEndDim on actualEndDim.DateKey = dcp.actual_end_date'+
                        ' left join dim_date as pkgStartDim on pkgStartDim.DateKey = dcp.start_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
 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 = di.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 ';
    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 @final_query = 'SELECT tmp.*, IIF(tmp.shared_name IS NOT NULL, actual_amount_month/2, actual_amount_month) as actual_amount_month_shared,' +
                       'IIF(tmp.shared_name is null, actual_amount_month, actual_amount_month/2) as sales_assigned_amount,IIF(tmp.shared_name is not null, actual_amount_month/2, 0) as sales_shared_amount  FROM ('+@select_query+') as tmp ORDER BY tmp.company_name, tmp.account_name'
    EXEC sp_executesql @final_query;
go

