CREATE PROCEDURE [dbo].[fetch_customers]
    @whereCondn nvarchar(max) = '',
    @selectCondn nvarchar(max) = '',
    @salesId nvarchar(max) = '',
    @fromDate nvarchar(max) = '',
    @toDate nvarchar(max) = '',
    @showActualBilled nvarchar(max) = ''
AS
    declare @select_query nvarchar(max) = '';
    declare @select_cols nvarchar(max) = '';
    SET @select_cols = 'dc.company_name, dc.customer_id,dc.created_on,dc.first_start_pkg_on,' +
                       '(select count(account_id) from dim_customer_accounts where customer_id = dc.customer_id) as accounts,dc.currency_symbol,
       (select count(distinct dim_customer_package.parent_id) from dim_customer_package where dim_customer_package.customer_id = dc.customer_id ) as packages,
       (select count(distinct dim_customer_project.project_id) from dim_customer_project where dim_customer_project.customer_id = dc.customer_id and isDeleted=0 and project_type_id = 1) as projects,
       (select count(id) from dim_invoices where dim_invoices.customer_id = dc.customer_id ) as no_of_invoices,
       (select sum(amount_inr) from dim_invoices where dim_invoices.customer_id = dc.customer_id ) as billed_invoices,
        (select count(timesheet_id) from dim_timesheets where dim_timesheets.customer_id = dc.customer_id and timesheet_status_id IN (1,4,7)) as timesheets,
       (select sum(amount_after_adjustment*inr_rate) from dim_timesheets where dim_timesheets.customer_id = dc.customer_id and timesheet_status_id IN (1,4,7)) as unbilled,
        (select sum(total_amount * exchange_rate) from dim_transactions_receipt as t where t.customer_id = dc.customer_id) as collection,' +
                       'sales_person.display_name as salesperson_name,
       shared.display_name as shared_name,'+
                         '(iif(first_start_pkg_on is not null and first_project_started_on is not null,(iif(first_start_pkg_on > first_project_started_on, first_project_started_on, first_start_pkg_on)), iif(first_start_pkg_on is null and first_project_started_on is not null, first_project_started_on, first_start_pkg_on) )) as first_package_project_date,' +
                       '(iif(last_package_end_date is not null and last_project_delivered_on is not null, (iif(last_package_end_date > last_project_delivered_on, last_package_end_date, last_project_delivered_on)), (iif(last_package_end_date is null, last_project_delivered_on , last_package_end_date)))) as last_package_project_date'

    IF @fromDate != ''
        BEGIN
            SET @select_cols = @select_cols + ',(select sum(actual_billed_amount) from fact_package where fact_package.customer_id = dc.customer_id and DateKey between dbo.convertDateToInt('''+@fromDate+''') and dbo.convertDateToInt('''+@toDate+''') '+@selectCondn+') as actual_billed_invoices_amt_for_month,' +
                               '(select sum(actual_inv_count) from fact_package where fact_package.customer_id = dc.customer_id and DateKey between dbo.convertDateToInt('''+@fromDate+''') and dbo.convertDateToInt('''+@toDate+''') '+@selectCondn+') as actual_billed_invoices_cnt_for_month,'+
                               '(select sum(inv_count_in_month) from fact_package where fact_package.customer_id = dc.customer_id and DateKey between dbo.convertDateToInt('''+@fromDate+''') and dbo.convertDateToInt('''+@toDate+''') '+@selectCondn+') as billed_invoices_cnt_for_month,'+
                               '(select sum(billed_amount) from fact_package where fact_package.customer_id = dc.customer_id and DateKey between dbo.convertDateToInt('''+@fromDate+''') and dbo.convertDateToInt('''+@toDate+''') '+@selectCondn+') as billed_invoices_amt_for_month
       '
        end
    SET @select_query = 'SELECT '+@select_cols +' FROM dim_customer dc';
    IF @fromDate != ''
        BEGIN
            SET @select_query = @select_query + ' left join fact_customer_sales_history as fcsh on fcsh.history_id = (select top 1 fcsh1.history_id from fact_customer_sales_history fcsh1
    where fcsh1.DateKey <= dbo.convertDateToInt('''+@toDate+''') and fcsh1.customer_id = dc.customer_id order by fcsh1.DateKey desc) '
             SET @select_query = @select_query + ' left join dim_users as sales_person on sales_person.user_id = fcsh.sales_person_id
        left join dim_users as shared on shared.user_id = fcsh.shared_with ' ;
        end
    IF @fromDate = ''
        BEGIN
            SET @select_query = @select_query + ' left join dim_users as sales_person on sales_person.user_id = dc.sales_person_id
        left join dim_users as shared on shared.user_id = dc.shared_with ' ;
        end

    SET @select_query = @select_query + @whereCondn
    IF @salesId != '' and @fromDate = ''
         BEGIN
            SET @select_query = @select_query + ' AND ( dc.sales_person_id IN (' + @salesId + ') OR dc.shared_with IN ('+ @salesId+'))';
        END
    IF @salesId != '' and @fromDate != ''
         BEGIN
            SET @select_query = @select_query + ' AND ( fcsh.sales_person_id IN (' + @salesId + ') OR fcsh.shared_with IN ('+ @salesId+'))';
        END
    SET @select_query = @select_query + ' ORDER BY dc.company_name'
    EXEC sp_executesql @select_query;
go

