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

    SET @select_query = 'select yearmonth,sum(invoices_cnt) as invoices_cnt,
       sum(basic) as basic,
       sum(tax) as tax,
       sum(total) as total,
       sum(balance) as balance,sum(lost_amount) as lost_amount
from kpi_invoice_monthly_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 @service_type != ''
        BEGIN
            SET @select_query = @select_query + ' AND (service_type_id = '+@service_type+') ';
        end
    IF  @sales_id != '' and @sales_by = 'start'
        BEGIN
            SET @select_query = @select_query + ' AND ( sales_person_id_by_start IN (' + @sales_id + ') OR shared_with_by_start IN ('+ @sales_id +'))';
        END
    IF  @sales_id != '' and @sales_by = 'create'
        BEGIN
            SET @select_query = @select_query + ' AND ( sales_person_id_by_create IN (' + @sales_id + ') OR shared_with_by_create IN ('+ @sales_id + '))';
        END
--     IF  @tl_id != '' and @sales_by = 'start'
--         BEGIN
--             SET @select_query =@select_query + ' AND tl_id_by_start = ' + @tl_id;
--         END
--     IF  @tl_id != '' and @sales_by = 'create'
--         BEGIN
--             SET @select_query =@select_query + ' AND tl_id_by_create = ' + @tl_id;
--         END

    IF @client_grade != ''
        BEGIN
            SET @select_query = @select_query + ' AND client_grade_id IN ('+@client_grade+')'
        end
     IF @relationship_id != ''
        BEGIN
            SET @select_query = @select_query + ' AND relationship_id IN ('+@relationship_id+')'
        end
    IF @show_lost != ''
        BEGIN
            SET @select_query = @select_query + ' AND lost_amount > 0'
        end
    SET @select_query = @select_query + ' group by year, month, yearmonth
              order by year, month';

   EXEC sp_executesql    @select_query;
go

