CREATE PROCEDURE [dbo].[fetch_previous_outstanding_for_packages]
    @from nvarchar(max) = '2022-01-01',
    @sales_id nvarchar(max) = '',
    @region_type_id nvarchar(max) = '',
    @client_grade nvarchar(max) = '',
    @relationship_id nvarchar(max) = ''
AS
    declare @select_query1 nvarchar(max) = ''; -- unbilled
    declare @select_query2 nvarchar(max) = ''; --billed
    declare @select_query3 nvarchar(max) = ''; --billed
    declare @select_query nvarchar(max) = '';

    SET @select_query1 = 'select 0 as pending_billed,sum(unbilled_amount) as pending_unbilled, 0 as project_pending_billed '
    SET @select_query2 = 'select sum(balance)  as pending_billed,0 as pending_unbilled, 0 as project_pending_billed '
    SET @select_query3 = 'select 0 as pending_billed,0 as pending_unbilled, SUM(balance) as project_pending_billed '

    SET @select_query1 = @select_query1 + ' from fact_package join dim_customer on dim_customer.customer_id = fact_package.customer_id '
    SET @select_query1 = @select_query1 + ' where DateKey  < convert(varchar,cast(''' + @from +''' as date),112)';
    SET @select_query2 = @select_query2 + ' from kpi_invoice_monthly_summary'
    SET @select_query2 = @select_query2 + ' where balance > 0 and cast(yearmonth+''-01'' as date) < '''+@from+''' and service_type_id = 3';
      SET @select_query3 = @select_query3 + ' from kpi_invoice_monthly_summary'
    SET @select_query3 = @select_query3 + ' where balance > 0 and cast(yearmonth+''-01'' as date) < '''+@from+''' and service_type_id = 1';
-- 	SET @select_query = @select_query + ' and DateKey  <= convert(varchar,cast(''' + @to +''' as date),112) ';

    IF  @sales_id != ''
        BEGIN
            SET @select_query1 = @select_query1 + ' AND ( actual_sales_person_id IN (' + @sales_id + ') OR actual_shared_with IN ('+ @sales_id+'))';
            SET @select_query2 = @select_query2 + ' AND ( sales_person_id_by_create IN (' + @sales_id + ') OR shared_with_by_create IN ('+ @sales_id+'))';
            SET @select_query3 = @select_query3 + ' AND ( sales_person_id_by_create IN (' + @sales_id + ') OR shared_with_by_create IN ('+ @sales_id+'))';
        END
    IF @region_type_id != ''
        BEGIN
           SET @select_query1 = @select_query1 + ' AND ( fact_package.region_type_id = '+@region_type_id+' )'
           SET @select_query2 = @select_query2 + ' AND ( region_type_id = '+@region_type_id+' )'
           SET @select_query3 = @select_query3 + ' AND ( region_type_id = '+@region_type_id+' )'
        END
    IF @client_grade != ''
        BEGIN
            SET @select_query1 = @select_query1 + ' AND (dim_customer.client_grade_id IN ('+@client_grade+'))'
            SET @select_query2 = @select_query2 + ' AND (client_grade_id IN ('+@client_grade+'))'
            SET @select_query3 = @select_query3 + ' AND (client_grade_id IN ('+@client_grade+'))'
        end
    IF @relationship_id != ''
        BEGIN
            SET @select_query1 = @select_query1 + ' AND (dim_customer.relationship_id IN ('+@relationship_id+'))'
            SET @select_query2 = @select_query2 + ' AND (relationship_id IN ('+@relationship_id+'))'
            SET @select_query3 = @select_query3 + ' AND (relationship_id IN ('+@relationship_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 = 2310';
--     SET @select_query = @select_query + ' group by year, month, yearmonth
--               order by year, month';
    SET @select_query = 'SELECT SUM(pending_billed) as pending_billed, SUM(pending_unbilled) as pending_unbilled, SUM(project_pending_billed) as project_pending_billed FROM (' + @select_query1 + ' UNION ALL '+@select_query2 + ' UNION ALL '+@select_query3+') as t';
   EXEC sp_executesql    @select_query;
go

