CREATE PROCEDURE [dbo].[fetch_summary_sales_performance_for_packages]
    @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) = '',
    @bu_id nvarchar(max) = '',
    @tech_id nvarchar(max) = '',
    @tech_type nvarchar(max) = 'pkg',
    @relationship_id nvarchar(max) = ''
AS
    declare @select_query nvarchar(max) = '';
    declare @bu_tech_table_alias nvarchar(max) = 'bu_pkg_tech'
    declare @tech_id_col nvarchar(max) = 'pkg_tech_id'
    IF @tech_type = 'res'
        BEGIN
           SET  @bu_tech_table_alias = 'bu_res_tech'
           SET @tech_id_col = 'res_tech_id'
        end

    SET @select_query = 'select count(distinct parent_id) as total_packages,
       count(distinct (case when actual_inv_count > 0 then parent_id else null end)) as invoice_raised_in,
       count(distinct (case when actual_timesheet_count > 0 then parent_id else null end)) as unbilled_pkg_cnt,
       count(distinct (case when ignored_timesheets > 0 then parent_id else null end)) as ignored_timesheets_cnt,
       count(distinct (case when ignored_timesheets = 0 and actual_timesheet_count = 0 and actual_inv_count = 0 then parent_id else null end)) as ''0_billing_pkg_cnt'',
       count(distinct (case when billing_method_id = 1 then parent_id else null end)) as daily_pkg_cnt,
       count(distinct (case when billing_method_id = 2 then parent_id else null end)) as hourly_pkg_cnt,
       count(distinct (case when billing_method_id = 3 then parent_id else null end)) as monthly_pkg_cnt,
       count(distinct (case when fact_package.region_type_id = 1 then parent_id else null end)) as local_pkg_cnt,
       count(distinct (case when fact_package.region_type_id = 2 then parent_id else null end)) as international_pkg_cnt,
       count(distinct (case when location_id = 1 then parent_id else null end)) as onsite_pkg_cnt,
       count(distinct (case when location_id = 2 then parent_id else null end)) as offshore_pkg_cnt,
       count(distinct (case when fact_package.region_id = 1 then parent_id else null end)) as region_1,
       count(distinct (case when fact_package.region_id = 2 then parent_id else null end)) as region_2,
       count(distinct (case when fact_package.region_id = 3 then parent_id else null end)) as region_3,
       count(distinct (case when fact_package.region_id = 4 then parent_id else null end)) as region_4,
       count(distinct (case when fact_package.region_id = 5 then parent_id else null end)) as region_5,
       count(distinct (case when fact_package.region_id = 6 then parent_id else null end)) as region_6,
       count(distinct (case when fact_package.region_id = 7 then parent_id else null end)) as region_7,
       count(distinct (case when fact_package.region_id = 8 then parent_id else null end)) as region_8,
       count(distinct (case when fact_package.region_id = 9 then parent_id else null end)) as region_9,
       count(distinct (case when fact_package.region_id = 10 then parent_id else null end)) as region_10,
       count(distinct (case when fact_package.region_id = 11 then parent_id else null end)) as region_11,
       count(distinct (case when fact_package.region_id = 12 then parent_id else null end)) as region_12,
       count(distinct (case when fact_package.region_id = 13 then parent_id else null end)) as region_13,
       count(distinct (case when fact_package.region_id = 14 then parent_id else null end)) as region_14,
       count(distinct (case when fact_package.region_id = 15 then parent_id else null end)) as region_15,
       count(distinct (case when fact_package.region_id = 16 then parent_id else null end)) as region_16,
       count(distinct (case when fact_package.region_id = 16 then parent_id else null end)) as region_16,
       count(distinct (case when fact_package.region_id = 17 then parent_id else null end)) as region_17,
       count(distinct (case when fact_package.region_id = 18 then parent_id else null end)) as region_18,
       count(distinct (case when fact_package.region_id = 19 then parent_id else null end)) as region_19,
       count(distinct (case when fact_package.region_id = 20 then parent_id else null end)) as region_20,
       count(distinct (case when fact_package.region_id = 21 then parent_id else null end)) as region_21,
       count(distinct (case when fact_package.region_id = 22 then parent_id else null end)) as region_22,
       count(distinct (case when fact_package.region_id = 23 then parent_id else null end)) as region_23,
       count(distinct (case when fact_package.region_id = 24 then parent_id else null end)) as region_24,
       count(distinct (case when fact_package.region_id = 25 then parent_id else null end)) as region_25,
       count(distinct (case when fact_package.region_id = 26 then parent_id else null end)) as region_26,
       count(distinct (case when fact_package.region_id is null then parent_id else null end)) as region_na,
       count(distinct fact_package.customer_id) as totalCustomers,
        COUNT(distinct (case when duration_in_months_actual < 1 and (package_status_id = 1 or package_status_id = 2) then parent_id else null end)) AS OTC_cnt,' +
                        'COUNT(distinct (case when fact_package.client_grade_id = 1 then fact_package.customer_id else null end)) as clientgrade_1_customers,
                        COUNT(distinct (case when fact_package.client_grade_id = 2 then fact_package.customer_id else null end)) as clientgrade_2_customers,
                        COUNT(distinct (case when fact_package.client_grade_id = 3 then fact_package.customer_id else null end)) as clientgrade_3_customers,
                        COUNT(distinct (case when fact_package.client_grade_id = 4 then fact_package.customer_id else null end)) as clientgrade_4_customers,
                        COUNT(distinct (case when fact_package.client_grade_id = 5 then fact_package.customer_id else null end)) as clientgrade_5_customers,
                        COUNT(distinct (case when fact_package.client_grade_id = 6 then fact_package.customer_id else null end)) as clientgrade_6_customers,
                        COUNT(distinct (case when fact_package.client_grade_id = 7 then fact_package.customer_id else null end)) as clientgrade_7_customers,' +
                        ' COUNT(distinct (case when fact_package.client_grade_id IS NULL then fact_package.customer_id else null end)) as clientgrade_NA_customers,
                        COUNT(distinct (case when fact_package.client_grade_id = 1 then fact_package.parent_id else null end)) as clientgrade_1_pkg,
                        COUNT(distinct (case when fact_package.client_grade_id = 2 then fact_package.parent_id else null end)) as clientgrade_2_pkg,
                        COUNT(distinct (case when fact_package.client_grade_id = 3 then fact_package.parent_id else null end)) as clientgrade_3_pkg,
                        COUNT(distinct (case when fact_package.client_grade_id = 4 then fact_package.parent_id else null end)) as clientgrade_4_pkg,
                        COUNT(distinct (case when fact_package.client_grade_id = 5 then fact_package.parent_id else null end)) as clientgrade_5_pkg,
                        COUNT(distinct (case when fact_package.client_grade_id = 6 then fact_package.parent_id else null end)) as clientgrade_6_pkg,
                        COUNT(distinct (case when fact_package.client_grade_id = 7 then fact_package.parent_id else null end)) as clientgrade_7_pkg,' +
                        ' COUNT(distinct (case when fact_package.client_grade_id IS NULL then fact_package.parent_id else null end)) as clientgrade_NA_pkg,
                        COUNT(distinct (case when fact_package.client_grade_id is not null then fact_package.customer_id else null end)) as clientgrade_customers,
                        COUNT(distinct (case when fact_package.client_grade_id is not null then fact_package.parent_id else null end)) as clientgrade_pkg
       '

    SET @select_query = @select_query + ' from fact_package left join mis_bu_technology as '+@bu_tech_table_alias+' on '+@bu_tech_table_alias+'.technology_id = fact_package.'+@tech_id_col
    SET @select_query = @select_query + ' where DateKey  >= convert(varchar,cast(''' + @from +''' as date),112)';
	SET @select_query = @select_query + ' and DateKey  <= convert(varchar,cast(''' + @to +''' as date),112) ';

    IF  @sales_id != ''
        BEGIN
            SET @select_query = @select_query + ' AND ( fact_package.actual_sales_person_id IN (' + @sales_id + ') OR fact_package.actual_shared_with IN ('+ @sales_id+'))';
        END
    IF @region_type_id != ''
        BEGIN
           SET @select_query = @select_query + ' AND ( fact_package.region_type_id = '+@region_type_id+' )'
        END
    IF @client_grade != ''
        BEGIN
            SET @select_query = @select_query + ' AND (fact_package.client_grade_id IN ('+@client_grade+'))'
        end
     IF @bu_id != ''
        BEGIN
            SET @select_query = @select_query + ' AND '+@bu_tech_table_alias+'.bu_id IN ('+@bu_id+' )'
        end
    IF @tech_id != ''
        BEGIN
            SET @select_query = @select_query + ' AND '+@tech_id_col+' IN ('+@tech_id+' )'
        end
    IF @relationship_id != ''
        BEGIN
            SET @select_query = @select_query + ' AND fact_package.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';
   EXEC sp_executesql    @select_query;
go

