CREATE PROCEDURE [dbo].[fetch_monthly_sales_performance_for_projects]
    @from nvarchar(max) = '2022-01-01',
    @to nvarchar(max) = '2022-12-31',
    @sales_id nvarchar(max) = '',
    @tl_id nvarchar(max) = '',
    @region_type_id nvarchar(max) = '',
    @client_grade nvarchar(max) = '',
    @relationship_id nvarchar(max) = ''
AS
    declare @select_query nvarchar(max) = '';

    SET @select_query = 'select year, month, yearmonth,
       count(customer_id) as customers, sum(closed_projects) as projects_cnt,
       sum(closed_project_amt) as project_amount,
       sum(invoice_amount) as invoice_amount,sum(invoice_pending_amount) as invoice_pending_amount, sum(unrealized_amt) as unrealized_amt,
       sum(pending_amount_till_date) as pending_amount_till_date,sum(total_lost_amount) as total_lost_amount
from kpi_monthly_customer_project_summary'

    SET @select_query = @select_query + ' where cast(yearmonth+''-''+''01'' as date)  >= ''' + @from +'''';
	SET @select_query = @select_query + ' and cast(yearmonth+''-''+''01'' as date)  <=''' + @to +'''';

    IF  @sales_id != ''
        BEGIN
            SET @select_query = @select_query + ' AND ( sales_person_id IN (' + @sales_id + ') OR shared_with IN ('+ @sales_id +'))';
        END
    IF @region_type_id != ''
        BEGIN
            SET @select_query = @select_query + ' AND (region_type_id = '+@region_type_id+')';
        end
--     IF  @tl_id != ''
--         BEGIN
--             SET @select_query =@select_query + ' AND tl_id = ' + @tl_id;
--         END
--     SET @select_query = @select_query + ' AND customer_id = 27'
    IF @client_grade != ''
        BEGIN
            SET @select_query = @select_query + ' AND (client_grade IN ('+@client_grade+'))'
        end
    IF @relationship_id != ''
        BEGIN
            SET @select_query = @select_query + ' AND (relationship_id IN ('+@relationship_id+'))'
        end
    SET @select_query = @select_query + ' group by year, month, yearmonth
              order by year, month';

   EXEC sp_executesql    @select_query;
go

